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

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
Строка 88: Строка 88:
  
 
END;
 
END;
 +
</pre>
 +
 +
==Скрипт на перевод атрибутов на двуст.синхронизацию. Сервер==
 +
* Не забыть проверить настройки в G$DISTRIBUTE_VECTORS, G$DISTRIBUTE_X_TABLES
 +
<pre>
 +
/******************************************************************************/
 +
/***              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);
 +
</pre>
 +
==Скрипт на перевод атрибутов на двуст.синхронизацию. Клиент==
 +
<pre>
 +
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;
 
</pre>
 
</pre>

Версия 17:38, 17 августа 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                               ***/
/******************************************************************************/

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

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

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=6745»