ТНВЭД — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎Новая вспомогательная таблица TNVED)
(→‎У тех клиентов где код тнвэд в таблице wares)
 
(не показаны 2 промежуточные версии этого же участника)
Строка 71: Строка 71:
  
 
==Заполняем коды ==
 
==Заполняем коды ==
 +
долгая операция может уйти до получаса
 
  <nowiki>
 
  <nowiki>
 
EXECUTE BLOCK
 
EXECUTE BLOCK
Строка 85: Строка 86:
 
END;
 
END;
 
  </nowiki>
 
  </nowiki>
 +
 
==Обновить менеджер до версии не ниже 273.75==
 
==Обновить менеджер до версии не ниже 273.75==
−
==У тех клиентов где код тнвэд в таблице wares==
+
==У тех клиентов где код тнвэд в таблице wares нужно перенести на атрибуты==
 
<pre>
 
<pre>
 
EXECUTE BLOCK
 
EXECUTE BLOCK

Текущая версия на 16:19, 20 августа 2018

Новая вспомогательная таблица TNVED

/******************************************************************************/
/***               Generated by IBExpert 16.08.2018 12:21:47                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE TNVED (
    TNVEDCODE  DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
    BARCODE    DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
    SNAME      DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */
);




/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE TNVED ADD CONSTRAINT PK_TNVED PRIMARY KEY (TNVEDCODE, BARCODE, SNAME);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX TNVED_IDX1 ON TNVED (TNVEDCODE);
CREATE INDEX TNVED_IDX2 ON TNVED (BARCODE);
CREATE INDEX TNVED_IDX3 ON TNVED (SNAME);


/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/

SET SQL DIALECT 3;



SET TERM ^ ;



CREATE OR ALTER TRIGGER TNVED_BI0 FOR TNVED
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  new.tnvedcode=trim(new.tnvedcode);
end
^

SET TERM ; ^

Выполнить файл tnved.sql

Файл:Tnved.rar

Заполняем коды

долгая операция может уйти до получаса 

EXECUTE BLOCK
AS
declare variable b dm_text;
declare variable t dm_text;
declare variable ware_id dm_uuid;
BEGIN
  for select t.barcode,t.tnvedcode from tnved t into :b,:t do
  begin
    for select id from wares where barcode containing :b into :ware_id do
      update or insert into ATTRIBUTE_DETAIL(ATTR_SID,ATTR_LINKID,ATTR_SVALUE) values ('TNVEDCODE',:ware_id,:t) matching (ATTR_SID,ATTR_LINKID);
  end
END;

Обновить менеджер до версии не ниже 273.75

У тех клиентов где код тнвэд в таблице wares нужно перенести на атрибуты

EXECUTE BLOCK
AS
 declare variable t dm_text;
 declare variable w dm_text;
BEGIN
 for select edizm,ware_id from vw_parts where coalesce(trim(edizm),'') <>'' into :t,:w do
   update or insert into ATTRIBUTE_DETAIL(ATTR_SID,ATTR_LINKID,ATTR_SVALUE) values
   ('UNITNOMENCLATURE',:w,:t) matching (ATTR_SID,ATTR_LINKID);

END;


EXECUTE BLOCK
AS
 declare variable t dm_text;
 declare variable w dm_text;
BEGIN
 for select tnvedcode,id from wares where coalesce(trim(tnvedcode),'') <>'' into :t,:w do
   update or insert into ATTRIBUTE_DETAIL(ATTR_SID,ATTR_LINKID,ATTR_SVALUE) values
   ('TNVEDCODE',:w,:t) matching (ATTR_SID,ATTR_LINKID);

END;

Скрипт на перевод атрибутов на двуст.синхронизацию. Сервер

  • Не забыть проверить настройки в G$DISTRIBUTE_VECTORS, G$DISTRIBUTE_X_TABLES 
/******************************************************************************/
/***               Generated by IBExpert 14.08.2018 12:37:19                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE ATTRIBUTES (
    ATTR_SID            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    ATTR_LINKFIELDNAME  DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    ATTR_TYPE           DM_STATUS /* DM_STATUS = INTEGER */,
    STATUS              DM_STATUS /* DM_STATUS = INTEGER */,
    INSERTDT            DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    D$SRVUPDDT          DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    D$UUID              DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */
);




/******************************************************************************/
/***                           Unique constraints                           ***/
/******************************************************************************/

ALTER TABLE ATTRIBUTES ADD CONSTRAINT UNQ1_ATTRIBUTES UNIQUE (D$UUID);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX ATTRIBUTES_IDX1 ON ATTRIBUTES (ATTR_SID);

/******************************************************************************/
/***                          Fields descriptions                           ***/
/******************************************************************************/

COMMENT ON COLUMN ATTRIBUTES.ATTR_TYPE IS 
'0 - string
1 - int
2 - float
3 - date
4 - datetime';



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/



/******************************************************************************/
/***               Generated by IBExpert 14.08.2018 12:38:29                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/


CREATE GENERATOR GEN_ATTRIBUTE_DETAIL_ID;

CREATE TABLE ATTRIBUTE_DETAIL (
    ID           DM_ID NOT NULL /* DM_ID = BIGINT */,
    ATTR_SID     DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    ATTR_LINKID  DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
    ATTR_SVALUE  DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    INSERTDT     DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    D$SRVUPDDT   DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    D$UUID       DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */
);




/******************************************************************************/
/***                           Unique constraints                           ***/
/******************************************************************************/

ALTER TABLE ATTRIBUTE_DETAIL ADD CONSTRAINT UNQ1_ATTRIBUTE_DETAIL UNIQUE (D$UUID);


/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE ATTRIBUTE_DETAIL ADD CONSTRAINT PK_ATTRIBUTE_DETAIL PRIMARY KEY (ID);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE UNIQUE INDEX ATTRIBUTE_DETAIL_IDX1 ON ATTRIBUTE_DETAIL (ATTR_SID, ATTR_LINKID);
CREATE INDEX ATTRIBUTE_DETAIL_IDX2 ON ATTRIBUTE_DETAIL (ATTR_SID, ATTR_LINKID, ATTR_SVALUE);


/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/


INSERT INTO G$DISTRIBUTE_VECTORS ( FROM_PROFILE_ID, TO_PROFILE_ID, TABLENAME, PERMISSION) VALUES ( 0, 0, 'ATTRIBUTE_DETAIL', 0);
INSERT INTO G$DISTRIBUTE_VECTORS ( FROM_PROFILE_ID, TO_PROFILE_ID, TABLENAME, PERMISSION) VALUES ( 0, 0, 'ATTRIBUTES', 0);

Скрипт на перевод атрибутов на двуст.синхронизацию. Клиент

ALTER TABLE ATTRIBUTE_DETAIL
ADD D$SRVUPDDT DM_DATETIME;

ALTER TABLE ATTRIBUTE_DETAIL
ADD D$UUID DM_UUID
NOT NULL ;

ALTER TABLE ATTRIBUTE_DETAIL
ADD CONSTRAINT UNQ1_ATTRIBUTE_DETAIL
UNIQUE (D$UUID);

SET TERM ^ ;

CREATE trigger attribute_detail_bi_distr for attribute_detail
active before insert position 0
AS
begin
  if (new.d$uuid is null) then
  begin
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
  end
  if (new.d$srvupddt is null) then
  begin
    new.d$srvupddt='2000-01-01';
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTE_DETAIL',new.d$uuid,0,null) matching (TABLENAME,UUID);
  end
end^

SET TERM ; ^

SET TERM ^ ;

CREATE trigger attribute_detail_bu_distr for attribute_detail
active before update position 0
AS
begin
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTE_DETAIL',new.d$uuid,1,null)
    matching (TABLENAME,UUID);
end^

SET TERM ; ^

SET TERM ^ ;



CREATE OR ALTER TRIGGER ATTRIBUTE_DETAIL_AD_DISTR FOR ATTRIBUTE_DETAIL
ACTIVE AFTER DELETE POSITION 0
AS
begin
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTE_DETAIL',old.d$uuid,2,null)
  matching (TABLENAME,UUID);
end
^

SET TERM ; ^

update ATTRIBUTE_DETAIL set d$srvupddt='2000-01-01',d$uuid=UUID_TO_CHAR(GEN_UUID());
update ATTRIBUTE_DETAIL set id=id;




ALTER TABLE ATTRIBUTES
ADD D$SRVUPDDT DM_DATETIME;

ALTER TABLE ATTRIBUTES
ADD D$UUID DM_UUID
NOT NULL ;

ALTER TABLE ATTRIBUTES
ADD CONSTRAINT UNQ1_ATTRIBUTES
UNIQUE (D$UUID);

SET TERM ^ ;

CREATE trigger ATTRIBUTES_bi_distr for ATTRIBUTES
active before insert position 0
AS
begin
  if (new.d$uuid is null) then
  begin
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
  end
  if (new.d$srvupddt is null) then
  begin
    new.d$srvupddt='2000-01-01';
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTES',new.d$uuid,0,null) matching (TABLENAME,UUID);
  end
end^

SET TERM ; ^

SET TERM ^ ;

CREATE trigger ATTRIBUTES_bu_distr for ATTRIBUTES
active before update position 0
AS
begin
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTES',new.d$uuid,1,null)
    matching (TABLENAME,UUID);
end^

SET TERM ; ^

SET TERM ^ ;



CREATE OR ALTER TRIGGER ATTRIBUTES_AD_DISTR FOR ATTRIBUTES
ACTIVE AFTER DELETE POSITION 0
AS
begin
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTES',old.d$uuid,2,null)
  matching (TABLENAME,UUID);
end
^

SET TERM ; ^

update ATTRIBUTES set d$srvupddt='2000-01-01',d$uuid=UUID_TO_CHAR(GEN_UUID());
update ATTRIBUTES set STATUS=STATUS;
Источник — «http://wiki.standart-n.ru/index.php?title=ТНВЭД&oldid=6752»