Подготовка серверной базы и скриптов для Единого Окна — различия между версиями

Текущая версия на 15:05, 6 сентября 2016

Содержание

1 Экспорт vals_link (для ЙО: boss:d:\обмен\JO\ZTRADE.FDB) как dbf или insert
2 Создаем и вставляем данные vals_link в нашу БД (скрипт создания и импорта из dbf: cowa_vals_links.sql)
3 Выпоняем cowa_all.sql (не оптимизирован для ЙО)
4 Для связывания по ШК выполняем cowa_bcode_links.sql (не оптимизирован для ЙО)
5 Проверяем работу, смотрим сколько записей добавилось в g$distribute - могут добавится немного записей и только по таблицам wares и wares_log
6 Экспорт как insert скрипт в файл запроса "select * from wares_log wl where wl.actual_ware_id is not null", все строки "commit work" - убираем
7 Экспрорт как update or insert скрипт в файл запроса "select distinct w.* from TMP_UPDATED_WARES t left join wares w on t.uuid=w.d$uuid where w.d$uuid is not null", все строки "commit work" - убираем
8 Делаем шаблоны G$TASKS_TMPL по образцу. Заменить data в записях cowa_02XX на файл, созданный в 6м пункте, cowa_08 - на файл 7го пункта

Экспорт vals_link (для ЙО: boss:d:\обмен\JO\ZTRADE.FDB) как dbf или insert

Создаем и вставляем данные vals_link в нашу БД (скрипт создания и импорта из dbf: cowa_vals_links.sql)

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

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


CREATE GENERATOR GEN_VALS_LINKS_ID;

CREATE TABLE VALS_LINKS (
    ID            DM_ID NOT NULL,
    RIGHT_VAL_ID  DM_UUID,
    WRONG_VAL_ID  DM_UUID,
    INSERTDT      DM_DATETIME,
    VTYPE         DM_STATUS NOT NULL,
    SRIGHT_VAL    DM_TEXT,
    SWRONG_VAL    DM_TEXT,
    DELETEDT      DM_DATETIME
);




/******************************************************************************/
/***                           Unique Constraints                           ***/
/******************************************************************************/

ALTER TABLE VALS_LINKS ADD CONSTRAINT UNQ1_VALS_LINKS UNIQUE (RIGHT_VAL_ID, WRONG_VAL_ID);


/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE VALS_LINKS ADD CONSTRAINT PK_VALS_LINKS PRIMARY KEY (ID);


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

CREATE INDEX VALS_LINKS_IDX1 ON VALS_LINKS (RIGHT_VAL_ID);
CREATE INDEX VALS_LINKS_IDX2 ON VALS_LINKS (WRONG_VAL_ID);
CREATE INDEX VALS_LINKS_IDX3 ON VALS_LINKS (SRIGHT_VAL, SWRONG_VAL);
CREATE INDEX VALS_LINKS_IDX4 ON VALS_LINKS (SRIGHT_VAL);
CREATE INDEX VALS_LINKS_IDX5 ON VALS_LINKS (SWRONG_VAL);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: VALS_LINKS_BI */
CREATE OR ALTER TRIGGER VALS_LINKS_BI FOR VALS_LINKS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_vals_links_id,1);
  new.insertdt=current_timestamp;
end
^


SET TERM ; ^



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



execute ibeblock
as
begin
  cbb = 'execute ibeblock (RecCount variant)
         as
         begin
           if (ibec_Mod(RecCount, 100) = 0) then
             ibec_Progress(RecCount || '' records imported.'');
         end;';
  
  DB = ibec_CreateConnection(__ctFirebird, 'DBName="standart-n:D:\STANDART-N\base_g\test\test_ztrade_g.fdb";
                                            ClientLib=gds32.dll;
                                            User=SYSDBA; Password=masterkey; Names=WIN1251; SqlDialect=3;');
  try
    ibec_UseConnection(DB);
    Res = ibec_ImportData(DB, 'VALS_LINKS', __impDBase, 'D:\STANDART-N\base_g\test\SQLs\vals_links.dbf', '', 
                          'RowFirst=1; RowLast=2147483647;
                           DateOrder=DMY; DateSeparator="."; TimeSeparator=":";', '', cbb);
  
    if (Res is not null) then
      ibec_ShowMessage(Res || ' records were imported successfully.');
  finally
    ibec_CloseConnection(DB);
  end;

end

Выпоняем cowa_all.sql (не оптимизирован для ЙО)

--копируем VALS_LINKS как объект
ALTER TRIGGER WARES_LOG_BI_DISTR INACTIVE;
ALTER TRIGGER WARES_LOG_BU_DISTR INACTIVE;
ALTER TRIGGER WARES_LOG_AD_DISTR INACTIVE;
ALTER TRIGGER WARES_AD_DISTR INACTIVE;

ALTER TRIGGER WARES_AU0 INACTIVE;
ALTER TRIGGER WARES_AIU0 INACTIVE;
ALTER TRIGGER VALS_AIU0 INACTIVE;

ALTER TRIGGER PARTS_AU0 INACTIVE;

ALTER TRIGGER VALS_AD_DISTR INACTIVE;
--ALTER TRIGGER VALS_AD_DODOUBLES INACTIVE;
--ALTER TRIGGER WARES_BIU_VALCHECK INACTIVE;

--ALTER TRIGGER WARES_AD_DISTR INACTIVE;
--ALTER TRIGGER WARES_AD0 INACTIVE;
ALTER TRIGGER WARES_BD0 INACTIVE;
ALTER TRIGGER WARES_BU_DISTR INACTIVE;
--ALTER TRIGGER WARES_BU0_MGN_NAME_ASNA INACTIVE;

--DROP INDEX WARES_IDX15;
--CREATE INDEX WARES_IDX15 ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID);

ALTER TABLE WARES_LOG
ADD ACTUAL_WARE_ID DM_UUID_NULL;
CREATE INDEX WARES_LOG_ACTUAL_WARE_ID ON WARES_LOG (ACTUAL_WARE_ID);

SET TERM ^ ;

CREATE OR ALTER trigger wares_log_bi_tmp for wares_log
active before insert position 0
AS
begin
  if (new.log_id is null) then
    new.log_id=UUID_TO_CHAR(GEN_UUID());
  new.d$uuid=new.log_id;
  if (new.d$srvupddt is null) then
  begin
    new.d$srvupddt='2000-01-01';
  end
  if (new.l_id is null) then
    new.l_id=GEN_ID(GEN_WARES_LOG_ID,1);
  new.log_insertdt=current_timestamp;
end^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER procedure PR_TMP_CHANGEWARES_CONSTR
as
declare variable UUID type of DM_UUID;
declare variable NAME_ID type of DM_UUID;
declare variable IZG_ID type of DM_UUID;
declare variable COUNTRY_ID type of DM_UUID;
declare variable BARCODE type of DM_TEXT;
declare variable ACTUAL_UUID type of DM_UUID;
begin
  delete from wares_log wl where (ACTUAL_WARE_ID is null) and (not exists (select id from wares w where w.id=wl.id));
  for
    select min(d$uuid), name_id, izg_id, country_id, barcode from wares group by name_id, izg_id, country_id, barcode having count(1)>1
       into actual_uuid, name_id, izg_id, country_id, barcode
  do
  begin
    for select d$uuid from wares where name_id=:name_id and izg_id=:izg_id and country_id=:country_id and barcode=:barcode and d$uuid<>:actual_uuid into uuid
    do begin
--      insert into tmp_todelete (TABLENAME,UUID,REASON,ACTUAL_UUID) values ('WARES',:uuid,'удаление',:actual_uuid);
      insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,ACTUAL_WARE_ID)
          select ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,:actual_uuid
        from wares where d$uuid=:uuid;
      update parts set ware_id=:actual_uuid where ware_id=:uuid;
      delete from wares where d$uuid=:uuid;
    end
  end
--delete from wares w where not exists (select id from parts p where p.ware_id=w.id) = всего 11414 , после сжатия - игнорим пока
delete from vals v where (vtype=0) and (not exists (select id from wares w where w.name_id=v.id)) and (not exists (select id from wares_log wl where wl.name_id=v.id));
delete from vals v where (vtype=3) and (not exists (select id from wares w where w.izg_id=v.id)) and (not exists (select id from wares_log wl where wl.izg_id=v.id));
delete from vals v where (vtype=2) and (not exists (select id from wares w where w.country_id=v.id)) and (not exists (select id from wares_log wl where wl.country_id=v.id));

delete from vals v where (vtype=1) and (not exists (select id from wares w where w.orig_name_id=v.id)) and (not exists (select id from wares_log wl where wl.orig_name_id=v.id));
delete from vals v where (vtype=6) and (not exists (select id from wares w where w.orig_izg_id=v.id)) and (not exists (select id from wares_log wl where wl.orig_izg_id=v.id));
delete from vals v where (vtype=5) and (not exists (select id from wares w where w.orig_country_id=v.id)) and (not exists (select id from wares_log wl where wl.orig_country_id=v.id));
end^

SET TERM ; ^

update wares set barcode='' where barcode is null;

execute procedure PR_TMP_CHANGEWARES_CONSTR;
commit work;

CREATE UNIQUE INDEX WARES_IDX_U ON WARES (NAME_ID, IZG_ID, COUNTRY_ID, BARCODE);

--DROP TRIGGER WARES_LOG_BI_TMP;

CREATE TABLE TMP_UPDATED_WARES (
    UUID  DM_UUID /* DM_UUID = CHAR(36) NOT NULL */
);
CREATE INDEX TMP_UPDATED_WARES_IDX1 ON TMP_UPDATED_WARES (UUID);


/*
CREATE GENERATOR GEN_VALS_CARANTINE_ID;
CREATE TABLE VALS_CARANTINE (
    ID               DM_ID NOT NULL,
    CUR_ID           DM_UUID,
    NEW_ID           DM_UUID,
    INSERTDT         DM_DATETIME,
    FROM_PROFILE_ID  DM_ID_NULL
);
SET TERM ^ ;

CREATE OR ALTER TRIGGER VALS_CARANTINE_BI FOR VALS_CARANTINE
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_vals_carantine_id,1);
  new.insertdt=current_timestamp;
  if (new.from_profile_id is null) then
    select from_profile_id from g$distribute where uuid=new.new_id into new.from_profile_id;
end
^

SET TERM ; ^
*/
SET TERM ^ ;

CREATE OR ALTER trigger vals_biu_dodoubles for vals
inactive before insert or update position 0
AS
  declare variable ID type of DM_UUID;
begin
  select id from vals where VTYPE=new.VTYPE and svalue=new.svalue and ALTTYPE=new.ALTTYPE and d$uuid<>new.d$uuid into :id;
  if (id is null) then exit;
/*
0 - приведенное наименование
3 - приведенный производитель
2 - приведенная страна
1 - ориг. наименование
6 - ориг. производитель
5 - ориг. страна
Запускает цепочку апдейтов. При изменении wares проверяются дубли, и, в случае нахождения, правятся партии
*/
  if (new.vtype=0) then
    update wares set name_id=new.id where name_id=:id;
  else if (new.vtype=3) then
    update wares set izg_id=new.id where izg_id=:id;
  else if (new.vtype=2) then
    update wares set country_id=new.id where country_id=:id;
  else if (new.vtype=1) then
    update wares set orig_name_id=new.id where orig_name_id=:id;
  else if (new.vtype=6) then
    update wares set orig_izg_id=new.id where orig_izg_id=:id;
  else if (new.vtype=5) then
    update wares set orig_country_id=new.id where orig_country_id=:id;
  else exit;
  insert into vals_carantine (cur_id, new_id) values (:id, new.id);
  delete from vals where id=:id;
end^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger wares_biu_cowa for wares
active before insert or update position 0
AS
declare variable cur_id type of DM_UUID;
begin
  cur_id=null;
  select d$uuid from wares where
    NAME_ID=new.name_id and IZG_ID=new.izg_id and COUNTRY_ID=new.COUNTRY_ID and BARCODE=new.BARCODE and d$uuid<>new.d$uuid into :cur_id;
  if (cur_id is null) then exit;
  if (not exists(select id from wares_log where id=:cur_id and actual_ware_id=new.id)) then
  begin
    update wares_log set ACTUAL_WARE_ID=new.d$uuid where ACTUAL_WARE_ID=:cur_id;
    insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,ACTUAL_WARE_ID)
      select ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,new.d$uuid from wares where d$uuid=:cur_id;
--      values (new.ID,new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.ORIG_CODE,new.ORIG_NAME_ID,new.ORIG_IZG_ID,new.ORIG_COUNTRY_ID,new.BARCODE,new.Z_ID,new.SKLAD_ID,new.INSERTDT,new.PACKET,new.L_ID,new.d$uuid);
  end
  update parts set ware_id=new.d$uuid where ware_id=:cur_id;
  delete from wares where d$uuid=:cur_id;
end^

SET TERM ; ^
SET TERM ^ ;

CREATE OR ALTER trigger parts_biu_cowa for parts
active before insert or update position 0
AS
declare variable ware_id type of DM_UUID;
begin
  if (exists(select id from wares where id=new.ware_id)) then exit;
  select max(actual_ware_id) from wares_log where id=new.ware_id and (exists(select id from wares w where w.d$uuid=actual_ware_id)) into :ware_id;
  if (ware_id is null) then exit;
--  if (exists(select id from wares where d$uuid=:ware_id)) then
    new.ware_id=:ware_id;
end^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER procedure UTPR_REMOVE_TILDA (
    SVALUE type of DM_TEXT)
returns (
    S type of DM_TEXT)
as
declare variable TILDA type of DM_TEXT;
declare variable C char(10);
begin
  s=:svalue;
  if (svalue not starting with '~') then
  begin
    suspend;
    exit;
  end
  tilda='';
  if (svalue starting with '~100') then
  begin
    s=substring(svalue from 5 for 245);
    tilda='100';
  end
  else
  begin
    c=substring(svalue from 3 for 1);
    if (c in ('0','1','2','3','4','5','6','7','8','9')) then
    begin
      s=substring(svalue from 4 for 245);
      tilda=substring(svalue from 2 for 2);
    end
    else
    begin
      s=substring(svalue from 3 for 245);
      tilda=substring(svalue from 2 for 1);
    end
    c=substring(s from 1 for 1);
    if (c='@') then
    begin
      s=substring(s from 2);
    end
  end
  suspend;
end^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure UTPR_BUBBLE_SORT (
    SOURCE DM_TEXT)
returns (
    S DM_TEXT)
as
declare variable C1 char(1);
declare variable L integer;
declare variable I integer;
declare variable SWAPPED integer;
declare variable J integer;
declare variable C2 char(1);
declare variable B char(1);
begin
  l=char_length(source);
  i=0;
  s=source;
  while (i<=l) do
  begin
    i=i+1; j=0;
    swapped=0;
    while (j<=l-i-1) do
    begin
      j=j+1;
      c1=substring(s from j for 1);
      c2=substring(s from j+1 for 1);
      if (c1>c2) then
      begin
        b=c1;
        s=substring(s from 1 for j - 1) || c2 || c1 || substring(s from j + 2);
        swapped=1;
      end 
    end 
    if (swapped=0) then break;
  end 
  suspend;
end^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER procedure UTPR_CHARSONLY (
    SOURCE type of DM_TEXT,
    SORTED integer = 0)
returns (
    S DM_TEXT)
as
declare variable C char(1);
declare variable L integer;
declare variable I integer;
declare variable V integer;
begin
  l=char_length(source);
  i=1;
  s='';
  while (i<=l) do
  begin
    c=substring(source from i for 1);
    v=ascii_val(c);
    if (((v>47) and (v<58)) or
        ((v>64) and (v<91)) or
        ((v>96) and (v<123)) or
        (v>191))   then
      s=s||c;
    i=i+1;
  end 
  if (sorted=1) then
    select s from UTPR_BUBBLE_SORT(:s) into :s;
  suspend;
end^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER procedure PR_TMP_COMPRESS_VALS
as
declare variable UVALUE type of DM_TEXT;
declare variable VTYPE type of DM_STATUS;
declare variable ID type of DM_UUID;
declare variable W_CNT type of DM_STATUS;
declare variable T_CNT type of DM_STATUS;
declare variable T_ID type of DM_UUID;
begin
--  update vals set uppervalue=upper(trim((select s from UTPR_REMOVE_TILDA(svalue)))), isactual=0;
  update vals set uppervalue=(select s from UTPR_CHARSONLY(upper(trim((select s from UTPR_REMOVE_TILDA(svalue)))),1)), isactual=0;
--  for select uppervalue, vtype from vals where vtype in (0,3,2) and svalue<>'' group by vtype, uppervalue having count(1)>1 into uvalue, vtype do
  for select uppervalue, vtype from vals where vtype in (0,3,2) group by vtype, uppervalue having count(1)>1 into uvalue, vtype do
  begin
    T_CNT=0; t_id=null;
    for select id from vals where vtype=:vtype and uppervalue=:uvalue into id do
    begin
      if (vtype=0) then
        select count(1) from wares where name_id=:id into :w_cnt;
      else if (vtype=3) then
        select count(1) from wares where izg_id=:id into :w_cnt;
      else if (vtype=2) then
        select count(1) from wares where country_id=:id into :w_cnt;
     if (t_cnt<w_cnt) then
     begin
       t_cnt=w_cnt;
       t_id=:id;
     end 
     update vals set isactual=:w_cnt where id=:id;
    end
    if (t_id is not null) then
    begin
        for select id from vals where vtype=:vtype and uppervalue=:uvalue and id<>:t_id into id do
        begin
          if (vtype=0) then
          begin
            insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where name_id=:id;
            update wares set name_id=:t_id where name_id=:id;
          end
          else if (vtype=3) then
            begin
              insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where izg_id=:id;
              update wares set izg_id=:t_id where izg_id=:id;
            end
          else if (vtype=2) then
            begin
              insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where country_id=:id;
              update wares set country_id=:t_id where country_id=:id;
            end
        end
    end
  end 
end^

SET TERM ; ^


CREATE INDEX VALS_IDX_COWA_TMP ON VALS (VTYPE, UPPERVALUE);

execute procedure PR_TMP_COMPRESS_VALS;
commit work;

SET TERM ^ ;

CREATE OR ALTER procedure PR_TMP_APPLY_VALS_LINKS
as
declare variable RIGHT_ID type of DM_UUID;
declare variable WRONG_ID type of DM_UUID;
begin
  for select vl.right_val_id, vl.wrong_val_id from vals_links vl where vtype=0 into right_id, wrong_id do
  begin
    insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where name_id=:wrong_id;
    update wares set name_id=:right_id where name_id=:wrong_id;
  end
  for select vl.right_val_id, vl.wrong_val_id from vals_links vl where vtype=3 into right_id, wrong_id do
  begin
    insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where izg_id=:wrong_id;
    update wares set izg_id=:right_id where izg_id=:wrong_id;
  end
  for select vl.right_val_id, vl.wrong_val_id from vals_links vl where vtype=2 into right_id, wrong_id do
  begin
    insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where country_id=:wrong_id;
    update wares set country_id=:right_id where country_id=:wrong_id;
  end
end^

SET TERM ; ^

execute procedure PR_TMP_APPLY_VALS_LINKS;
commit work;

DROP TRIGGER WARES_LOG_BI_TMP;


ALTER TRIGGER WARES_AU0 INACTIVE;
ALTER TRIGGER WARES_AIU0 INACTIVE;
ALTER TRIGGER VALS_AIU0 INACTIVE;

ALTER TRIGGER WARES_LOG_BI_DISTR ACTIVE;
ALTER TRIGGER WARES_LOG_BU_DISTR ACTIVE;
ALTER TRIGGER WARES_LOG_AD_DISTR ACTIVE;
ALTER TRIGGER WARES_AD_DISTR ACTIVE;

ALTER TRIGGER PARTS_AU0 ACTIVE;

Для связывания по ШК выполняем cowa_bcode_links.sql (не оптимизирован для ЙО)

/******************************************************************************/ /*** Generated by IBExpert 19.08.2016 13:46:31 ***/ /******************************************************************************/

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

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

CREATE GENERATOR GEN_BARCODE_WARE_ID;

CREATE TABLE BARCODE_WARE (

   WARE_ID     DM_UUID NOT NULL,
   BARCODE     DM_TEXT,
   ID          DM_ID NOT NULL,
   NAME_ID     DM_UUID,
   IZG_ID      DM_UUID,
   COUNTRY_ID  DM_UUID,
   SNAME       DM_TEXT,
   SIZG        DM_TEXT,
   SCOUNTRY    DM_TEXT

);

/******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/

ALTER TABLE BARCODE_WARE ADD CONSTRAINT PK_BARCODE_WARE PRIMARY KEY (ID);

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

CREATE INDEX BARCODE_WARE_IDX1 ON BARCODE_WARE (NAME_ID); CREATE INDEX BARCODE_WARE_IDX2 ON BARCODE_WARE (IZG_ID); CREATE INDEX BARCODE_WARE_IDX3 ON BARCODE_WARE (COUNTRY_ID);

/******************************************************************************/ /*** Triggers ***/ /******************************************************************************/

SET TERM ^ ;

/******************************************************************************/ /*** Triggers for tables ***/ /******************************************************************************/

/* Trigger: BARCODE_WARE_BI */ CREATE OR ALTER TRIGGER BARCODE_WARE_BI FOR BARCODE_WARE ACTIVE BEFORE INSERT POSITION 0 as begin

 if (new.id is null) then
   new.id = gen_id(gen_barcode_ware_id,1);
 select svalue from vals where id=new.name_id into new.sname;
 select svalue from vals where id=new.izg_id into new.sizg;
 select svalue from vals where id=new.country_id into new.scountry;

end ^

SET TERM ; ^

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

SET TERM ^ ;

CREATE OR ALTER procedure UTPR_EXPLODE (

   SOURCE type of DM_BLOBTEXT,
   SEPARATOR type of DM_TEXT)

returns (

   S type of DM_TEXT)

as declare variable I integer; declare variable L integer; declare variable O integer; begin

 o=1;
 l=char_length(separator);
 i=position(SEPARATOR,SOURCE);
 while (i>0) do
 begin
   s=substring(source from o for i-o);
   suspend;
   o=i+1;
   i=position(SEPARATOR,SOURCE,o);
 end
 s=substring(source from o for char_length(source));
 suspend;

end^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure PRCOWA_BARCODE_LINKS as declare variable NAME_ID type of DM_UUID; declare variable IZG_ID type of DM_UUID; declare variable COUNTRY_ID type of DM_UUID; declare variable BARCODE type of DM_TEXT; declare variable ID DM_UUID; declare variable SCOUNTRY DM_TEXT; declare variable SIZG DM_TEXT; begin --заполнение

 delete from barcode_ware;
 for select name_id, izg_id, country_id, barcode, id from wares where barcode<> and barcode is not null into name_id, izg_id, country_id, barcode, :id do
 begin
   for select trim(s) from UTPR_EXPLODE(:barcode,',') into :barcode do
   begin
     if (barcode<>) then
       insert into barcode_ware (ware_id, barcode, name_id, izg_id, country_id) values (:id,:barcode,:name_id, :izg_id, :country_id);
   end
 end

-- обработка

 for select barcode, name_id, --count(1) as total--, count(distinct izg_id) as izg, count(distinct country_id) as country
   max(sizg) as sizg, max(scountry) as scountry

-- from barcode_ware group by barcode, name_id having ((count(1)>count(distinct izg_id)) and count(distinct izg_id)>1) or ((count(1)>count(distinct country_id)) and count(distinct country_id)>1)

       from barcode_ware group by barcode, name_id having (count(distinct izg_id)>1) or (count(distinct country_id)>1)
   into barcode, name_id, sizg,  scountry do
 begin
   if (trim(sizg)<>) then
   begin
     izg_id=null;
     select id from vals where svalue=:sizg and vtype=3 into :izg_id;
     for select ware_id from barcode_ware where barcode=:barcode and name_id=:name_id and sizg= into :id do
     begin
       update wares set izg_id=:izg_id where id=:id;
       insert into TMP_UPDATED_WARES(uuid) values (:id);
     end
   end
   if (trim(scountry)<>) then
   begin
     country_id=null;
     select id from vals where svalue=:scountry and vtype=2 into :country_id;
     for select ware_id from barcode_ware where barcode=:barcode and name_id=:name_id and scountry= into :id do
     begin
       update wares set country_id=:country_id where id = :id;
       insert into TMP_UPDATED_WARES(uuid) values (:id);
     end
  end
 end

end^

SET TERM ; ^

ALTER TRIGGER WARES_LOG_BI_DISTR INACTIVE; ALTER TRIGGER WARES_LOG_BU_DISTR INACTIVE; ALTER TRIGGER WARES_BI_DODOUBLES INACTIVE; ALTER TRIGGER WARES_AD_DISTR INACTIVE; ALTER TRIGGER PARTS_BIU0_SAGENT INACTIVE; ALTER TRIGGER PARTS_BU_SRVSYNC INACTIVE; ALTER TRIGGER PARTS_AU0 INACTIVE;

SET TERM ^ ;

CREATE OR ALTER trigger wares_log_bi_tmp for wares_log active before insert position 0 AS begin

 if (new.log_id is null) then
   new.log_id=UUID_TO_CHAR(GEN_UUID());
 new.d$uuid=new.log_id;
 if (new.d$srvupddt is null) then
 begin
   new.d$srvupddt='2000-01-01';
 end
 if (new.l_id is null) then
   new.l_id=GEN_ID(GEN_WARES_LOG_ID,1);
 new.log_insertdt=current_timestamp;

end^

SET TERM ; ^

execute procedure PRCOWA_BARCODE_LINKS; commit work;

DROP TRIGGER WARES_LOG_BI_TMP; ALTER TRIGGER WARES_LOG_BI_DISTR ACTIVE; ALTER TRIGGER WARES_LOG_BU_DISTR ACTIVE; ALTER TRIGGER WARES_BI_DODOUBLES ACTIVE; ALTER TRIGGER WARES_AD_DISTR ACTIVE; ALTER TRIGGER PARTS_BIU0_SAGENT ACTIVE; ALTER TRIGGER PARTS_BU_SRVSYNC ACTIVE; ALTER TRIGGER PARTS_AU0 ACTIVE;

Проверяем работу, смотрим сколько записей добавилось в g$distribute - могут добавится немного записей и только по таблицам wares и wares_log

Экспорт как insert скрипт в файл запроса "select * from wares_log wl where wl.actual_ware_id is not null", все строки "commit work" - убираем

Экспрорт как update or insert скрипт в файл запроса "select distinct w.* from TMP_UPDATED_WARES t left join wares w on t.uuid=w.d$uuid where w.d$uuid is not null", все строки "commit work" - убираем

Делаем шаблоны G$TASKS_TMPL по образцу. Заменить data в записях cowa_02XX на файл, созданный в 6м пункте, cowa_08 - на файл 7го пункта

/******************************************************************************/
/***               Generated by IBExpert 25.08.2016 14:49:37                ***/
/******************************************************************************/

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



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



CREATE TABLE G$TASKS_TMPL (
    ID             DM_ID NOT NULL,
    PROFILE_ID     DM_ID,
    TASK_TYPE      DM_STATUS,
    CAPTION        DM_TEXT,
    DATA           DM_BLOBTEXT,
    INSERTDT       DM_DATETIME,
    SENDDT         DM_DATETIME,
    ENDDT          DM_DATETIME,
    ENDFLAG        DM_STATUS,
    ENDTEXT        DM_BLOBTEXT,
    ENDDATA        DM_BLOBTEXT,
    G$PROFILE_ID   DM_ID_NULL,
    D$UUID         DM_UUID_NULL,
    D$SRVUPDDT     DM_DATETIME,
    CHECKPREVTASK  DM_STATUS
);




/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348162, 0, 0, 'cowa_01', 'SET TERM ^ ;

CREATE trigger wares_log_bi_cowatmp for wares_log
active before insert position 0
AS
begin
  if (new.sklad_id is null) then
    new.sklad_id='''';
end^

SET TERM ; ^

CREATE TABLE TMP_LOG (
    SKEY DM_TEXT,
    SVALUE DM_TEXT);

ALTER TABLE WARES_LOG
ADD ACTUAL_WARE_ID DM_UUID_NULL;

CREATE INDEX WARES_LOG_ACTUAL_WARE_ID
ON WARES_LOG (ACTUAL_WARE_ID);


CREATE GENERATOR GEN_VALS_CARANTINE_ID;

CREATE TABLE VALS_CARANTINE (
    ID               DM_ID NOT NULL,
    CUR_ID           DM_UUID,
    NEW_ID           DM_UUID,
    INSERTDT         DM_DATETIME,
    FROM_PROFILE_ID  DM_ID_NULL
);
SET TERM ^ ;

CREATE OR ALTER TRIGGER VALS_CARANTINE_BI FOR VALS_CARANTINE
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_vals_carantine_id,1);
  new.insertdt=current_timestamp;
  if (new.from_profile_id is null) then
    select from_profile_id from g$distribute where uuid=new.new_id into new.from_profile_id;
end
^


SET TERM ; ^
SET TERM ^ ;

CREATE OR ALTER trigger vals_biu_dodoubles for vals
active before insert or update position 0
AS
  declare variable ID type of DM_UUID;
begin
  select id from vals where VTYPE=new.VTYPE and svalue=new.svalue and ALTTYPE=new.ALTTYPE and d$uuid<>new.d$uuid into :id;
  if (id is null) then exit;
/*
0 - приведенное наименование
3 - приведенный производитель
2 - приведенная страна
1 - ориг. наименование
6 - ориг. производитель
5 - ориг. страна
Запускает цепочку апдейтов. При изменении wares проверяются дубли, и, в случае нахождения, правятся партии
*/
  if (new.vtype=0) then
    update wares set name_id=new.id where name_id=:id;
  else if (new.vtype=3) then
    update wares set izg_id=new.id where izg_id=:id;
  else if (new.vtype=2) then
    update wares set country_id=new.id where country_id=:id;
  else if (new.vtype=1) then
    update wares set orig_name_id=new.id where orig_name_id=:id;
  else if (new.vtype=6) then
    update wares set orig_izg_id=new.id where orig_izg_id=:id;
  else if (new.vtype=5) then
    update wares set orig_country_id=new.id where orig_country_id=:id;
  else exit;
  insert into vals_carantine (cur_id, new_id) values (:id, new.id);
  delete from vals where id=:id;
end^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER TRIGGER WARES_BIU_COWA FOR WARES
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
declare variable cur_id type of DM_UUID;
begin
  select d$uuid from wares where
    NAME_ID=new.name_id and IZG_ID=new.izg_id and COUNTRY_ID=new.COUNTRY_ID and BARCODE=new.BARCODE and d$uuid<>new.d$uuid into :cur_id;
  if (cur_id is null) then exit;
  if (not exists(select id from wares_log where id=:cur_id and actual_ware_id=new.id)) then
  begin
    update wares_log set ACTUAL_WARE_ID=new.d$uuid where ACTUAL_WARE_ID=:cur_id;
    insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,ACTUAL_WARE_ID)
      select ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,new.d$uuid from wares where d$uuid=:cur_id;
  end
  update parts set ware_id=new.d$uuid where ware_id=:cur_id;
  update doc_detail_active set ware_id=new.d$uuid where ware_id=:cur_id;

  --20160819 А.К.
  update warebase_distr set ware_id=new.d$uuid where ware_id=:cur_id;
  update doc_detail_active_treb set ware_id=new.d$uuid where ware_id=:cur_id;
  update DOC_DETAIL_DELETED set ware_id=new.d$uuid where ware_id=:cur_id;
  update DOC_DETAIL_VIRTUAL set ware_id=new.d$uuid where ware_id=:cur_id;
  update WARES_TREB set ware_id=new.d$uuid where ware_id=:cur_id;

  delete from wares where d$uuid=:cur_id;
end^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger parts_biu_cowa for parts
active before insert or update position 0
AS
declare variable ware_id type of DM_UUID;
begin
  if (exists(select id from wares where id=new.ware_id)) then exit;
--  select max(actual_ware_id) from wares_log where id=new.ware_id into :ware_id;
  select max(actual_ware_id) from wares_log where id=new.ware_id and (exists(select id from wares w where w.d$uuid=actual_ware_id)) into :ware_id;
  if (ware_id is null) then exit;
--  if (exists(select id from wares where d$uuid=:ware_id)) then
    new.ware_id=:ware_id;
end^

SET TERM ; ^



SET TERM ^ ;

CREATE OR ALTER trigger wares_bd_cowa for wares
active before delete position 0
AS
declare variable ware_id type of DM_UUID;
begin
  if (not exists (select id from parts where ware_id=old.id)) then exit;
--  select max(actual_ware_id) from wares_log where id=old.id into ware_id;
  select max(actual_ware_id) from wares_log wl where id=old.id and (exists (select id from wares w where w.id=wl.actual_ware_id)) into ware_id;
  if (ware_id is null) then
    insert into tmp_log (skey, svalue) values (''Нет ware по acual_ware!'',old.id);
  else
   begin
    update parts set ware_id=:ware_id where ware_id=old.id;
    --20160819 А.К.
    update doc_detail_active set ware_id=:ware_id where ware_id=old.id;
    update warebase_distr set ware_id=:ware_id where ware_id=old.id;
    update doc_detail_active_treb set ware_id=:ware_id where ware_id=old.id;
    update DOC_DETAIL_DELETED set ware_id=:ware_id where ware_id=old.id;
    update DOC_DETAIL_VIRTUAL set ware_id=:ware_id where ware_id=old.id;
    update WARES_TREB set ware_id=:ware_id where ware_id=old.id;
   end
end^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure PR_TMP_CHANGEWARES_CONSTR
as
declare variable UUID type of DM_UUID;
declare variable NAME_ID type of DM_UUID;
declare variable IZG_ID type of DM_UUID;
declare variable COUNTRY_ID type of DM_UUID;
declare variable BARCODE type of DM_TEXT;
declare variable ACTUAL_UUID type of DM_UUID;
declare variable EXISTING_ACTUAL_WARE_ID type of DM_UUID;
begin
--  delete from wares_log wl where (ACTUAL_WARE_ID is null) and (not exists (select id from wares w where w.id=wl.id));
  for
    select min(d$uuid), name_id, izg_id, country_id, barcode from wares group by name_id, izg_id, country_id, barcode having count(1)>1
       into actual_uuid, name_id, izg_id, country_id, barcode
  do
  begin
    EXISTING_ACTUAL_WARE_ID=null;
    select max(actual_ware_id) from wares_log where  name_id=:name_id and izg_id=:izg_id and country_id=:country_id and barcode=:barcode into EXISTING_ACTUAL_WARE_ID;
    if (EXISTING_ACTUAL_WARE_ID is not null) then
    begin
      if (exists (select id from wares where d$uuid=:existing_actual_ware_id)) then
        actual_uuid=EXISTING_ACTUAL_WARE_ID;
    end
    for select d$uuid from wares where name_id=:name_id and izg_id=:izg_id and country_id=:country_id and barcode=:barcode and d$uuid<>:actual_uuid into uuid
    do begin
      insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,ACTUAL_WARE_ID,d$srvupddt)
          select ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,:actual_uuid, current_timestamp
        from wares where d$uuid=:uuid;
--на триггере      update parts set ware_id=:actual_uuid where ware_id=:uuid;
      delete from wares where d$uuid=:uuid;
    end
  end
end^

SET TERM ; ^



SET TERM ^ ;

CREATE OR ALTER trigger wares_log_ai_cowatmp for wares_log
active after insert position 0
AS
begin
  if (new.actual_ware_id is null) then exit;
  if (exists(select id from wares where id=new.actual_ware_id)) then
    delete from wares where id=new.id;
  else
    insert into tmp_log (skey,svalue) values (''WARES_LOG_AI_COWATMP'',new.actual_ware_id);
end^

SET TERM ; ^



ALTER TRIGGER WARES_AD_DISTR INACTIVE;
ALTER TRIGGER WARES_BU_DISTR INACTIVE;
ALTER TRIGGER WARES_LOG_BI_DISTR INACTIVE;
ALTER TRIGGER WARES_LOG_AI0 INACTIVE;
ALTER TRIGGER PARTS_AU0 INACTIVE;

ALTER TRIGGER PARTS_BU_DISTR INACTIVE;
ALTER TRIGGER PARTS_BU_G$SYNC INACTIVE;
ALTER TRIGGER PARTS_BU0 INACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348163, 0, 0, 'cowa_02', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348164, 0, 0, 'cowa_03', 'ALTER TRIGGER PARTS_BU_DISTR ACTIVE;
ALTER TRIGGER PARTS_BU_G$SYNC ACTIVE;
ALTER TRIGGER PARTS_BU0 ACTIVE;

ALTER TRIGGER WARES_LOG_BI_DISTR ACTIVE;
ALTER TRIGGER WARES_AD_DISTR ACTIVE;
ALTER TRIGGER WARES_BU_DISTR ACTIVE;

CREATE INDEX WARES_IDX_U ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,BARCODE);

ALTER TABLE WARES DROP CONSTRAINT UNQ1_WARES;
ALTER TABLE WARES DROP CONSTRAINT WARES_IDX1;
CREATE INDEX WARES_IDX1 ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID);

DROP TRIGGER wares_log_bi_cowatmp;

ALTER TRIGGER WARES_AU0 INACTIVE;

ALTER TRIGGER WARES_BU0 INACTIVE;
ALTER TRIGGER WARES_BIU_COWA INACTIVE;
ALTER TRIGGER WARES_SNAME_BU0 INACTIVE;
ALTER TRIGGER WARES_BU_DISTR INACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348165, 0, 0, 'cowa_04', 'update wares set barcode='''' where barcode is null;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348166, 0, 0, 'cowa_05', 'ALTER TRIGGER WARES_BU0 ACTIVE;
ALTER TRIGGER WARES_BIU_COWA ACTIVE;
ALTER TRIGGER WARES_SNAME_BU0 ACTIVE;
ALTER TRIGGER WARES_BU_DISTR ACTIVE;

CREATE UNIQUE INDEX WARES_IDX_UUID
ON WARES (D$UUID);

ALTER TRIGGER WARES_AD_DISTR INACTIVE;
ALTER TRIGGER PARTS_BU_DISTR INACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348167, 0, 0, 'cowa_06', 'execute procedure PR_TMP_CHANGEWARES_CONSTR;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348168, 0, 0, 'cowa_07', 'DROP INDEX WARES_IDX_U;
CREATE UNIQUE INDEX WARES_IDX_U ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,BARCODE);

DROP TRIGGER WARES_LOG_AI_COWATMP;

DROP INDEX WARES_IDX100;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348169, 0, 0, 'cowa_08', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348170, 0, 0, 'cowa_09', 'execute procedure DBGPR_MAKEWAREBASEFROMDOCS(0);', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348171, 0, 0, 'cowa_10', 'ALTER TRIGGER PARTS_BU_DISTR ACTIVE;
ALTER TRIGGER WARES_AD_DISTR ACTIVE;
ALTER TRIGGER WARES_AU0 ACTIVE;
ALTER TRIGGER WARES_LOG_AI0 ACTIVE;
ALTER TRIGGER PARTS_AU0 ACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348172, 0, 0, 'cowa_1_whash', 'SET TERM ^ ;

CREATE OR ALTER procedure PR_GET_WARES_HASH (
    NAME_ID DM_UUID_NULL,
    IZG_ID DM_UUID_NULL,
    COUNTRY_ID DM_UUID_NULL,
    BARCODE DM_TEXT)
returns (
    WHASH DM_ID)
as
begin
  if (exists(select RDB$INDEX_NAME from RDB$INDEX_SEGMENTS ris where ris.rdb$index_name=''WARES_IDX_U'' and ris.rdb$field_name = ''BARCODE'')) then
   whash = hash(coalesce(:NAME_ID,''NAME_ID'')||coalesce(:IZG_ID,''IZG_ID'')||coalesce(:COUNTRY_ID,''COUNTRY_ID'')||coalesce(:BARCODE, ''''));
  else
   whash = hash(coalesce(:NAME_ID,''NAME_ID'')||coalesce(:IZG_ID,''IZG_ID'')||coalesce(:COUNTRY_ID,''COUNTRY_ID''));
  suspend;
end^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER procedure PR_GETWARE_BY_IDS (
    NAME_ID type of DM_UUID_NULL,
    IZG_ID type of DM_UUID_NULL,
    COUNTRY_ID type of DM_UUID_NULL,
    ORIG_CODE type of DM_TEXT,
    ORIG_NAME_ID type of DM_UUID_NULL,
    ORIG_IZG_ID type of DM_UUID_NULL,
    ORIG_COUNTRY_ID type of DM_UUID_NULL,
    DOINSERT type of DM_STATUS,
    SEARCHINLOGS type of DM_STATUS,
    BARCODE DM_TEXT)
returns (
    WARE_ID type of DM_UUID_NULL)
as
declare variable WHASH DM_ID;
begin
  if (orig_code is null) then orig_code='''';
  if (searchinlogs is null) then searchinlogs=0;
  if (searchinlogs=1) then
  begin 
    select first 1 id from wares_log where name_id=:name_id and izg_id=:izg_id and country_id=:country_id and
      orig_code=:orig_code and orig_name_id=:orig_name_id and orig_izg_id=:orig_izg_id and orig_country_id=:orig_country_id
      and barcode=:barcode
      into :ware_id;
      if ((select id from wares where id = :ware_id) is null) then ware_id = null;
  end
  else
  begin
--20160805 A.K.
    select whash from PR_GET_WARES_HASH(:NAME_ID,:IZG_ID,:COUNTRY_ID,:BARCODE) into :whash;
    select id from wares where whash = :whash into :ware_id;
/*    select first 1 id from wares where name_id=:name_id and izg_id=:izg_id and country_id=:country_id and
      orig_code=:orig_code and orig_name_id=:orig_name_id and orig_izg_id=:orig_izg_id and orig_country_id=:orig_country_id
      and barcode=:barcode
      into :ware_id;
*/
  end
  if ((ware_id is null) and (DOINSERT=1) and (searchinlogs<>1)) then
  begin
--    ware_id=gen_id(gen_wares_id,1);
    ware_id=UUID_TO_CHAR(GEN_UUID());
    insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,Z_ID,SKLAD_ID, barcode)
     values
       (:ware_id,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,0,'''',:barcode);
  end

  suspend;
end^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure PR_GET_WARE (
    SNAME type of DM_TEXT,
    SIZG type of DM_TEXT,
    SCOUNTRY type of DM_TEXT,
    ORIG_CODE type of DM_TEXT,
    SORIG_NAME type of DM_TEXT,
    SORIG_IZG type of DM_TEXT,
    SORIG_COUNTRY type of DM_TEXT,
    BARCODE type of DM_TEXT,
    Z_ID type of DM_ID,
    SKLAD_ID DM_TEXT,
    ALTTYPE DM_STATUS,
    MNN DM_TEXT = '''')
returns (
    W_ID type of DM_UUID_NULL)
as
declare variable OLD_BARCODE DM_TEXT1024;
declare variable NAME_ID type of DM_UUID_NULL;
declare variable IZG_ID type of DM_UUID_NULL;
declare variable COUNTRY_ID type of DM_UUID_NULL;
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
declare variable WHASH DM_ID;
begin
if (orig_code is null) then orig_code='''';
select val_id from pr_getval_id(:sname,0,:alttype,:mnn) into :name_id;
select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id;
select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id;
select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
--20160805 A.K.
select whash from PR_GET_WARES_HASH(:NAME_ID,:IZG_ID,:COUNTRY_ID,:BARCODE) into :whash;
select id from wares where whash = :whash into :w_id;
/*select id from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and
ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and
ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id and barcode=:barcode into :w_id;
*/

if (w_id is null) then
begin
--    exception EX_WRONG_OPER;
--w_id=gen_id(gen_wares_id,1);
w_id=UUID_TO_CHAR(GEN_UUID());
insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID)
values
(:W_ID,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,:BARCODE,:Z_ID,:SKLAD_ID);
end
--else if (old_barcode <> barcode) then update wares set barcode = :barcode where id = :w_id;
suspend;
end^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger wares_hash_bui0 for wares
active before insert or update position 0
AS
begin
 select whash from PR_GET_WARES_HASH(new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.BARCODE) into new.whash;
end^

SET TERM ; ^


ALTER TRIGGER WARES_BU_DISTR INACTIVE;
ALTER TRIGGER WARES_SNAME_BU0 INACTIVE;
ALTER TRIGGER WARES_BU0 INACTIVE;
ALTER TRIGGER WARES_AU0 INACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348173, 0, 0, 'cowa_2_whash', 'update wares set id=id;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348174, 0, 0, 'cowa_3_whash', 'ALTER TRIGGER WARES_BU_DISTR ACTIVE;
ALTER TRIGGER WARES_SNAME_BU0 ACTIVE;
ALTER TRIGGER WARES_BU0 ACTIVE;
ALTER TRIGGER WARES_AU0 ACTIVE;

CREATE UNIQUE INDEX WARES_IDX100 ON WARES (WHASH);', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348175, 0, 0, 'cowa_1_recur', 'SET TERM ^ ;

CREATE OR ALTER procedure PR_COWA_01
as
declare variable I integer;
declare variable ACTUAL_WARE_ID type of DM_UUID;
declare variable ACTUAL_WARE_ID1 type of DM_UUID;
begin
  i=1;
  while (i>0) do
  begin
    i=0;
    for select distinct actual_ware_id from wares_log wl where actual_ware_id is not null and not exists (select id from wares w where w.id=wl.actual_ware_id)
      into :actual_ware_id do
    begin
      for select distinct actual_ware_id from wares_log wl where id = :actual_ware_id and actual_ware_id is not null
        into ACTUAL_WARE_ID1 do
      begin
        i=i+1;
        update wares_log set actual_ware_id=:ACTUAL_WARE_ID1 where actual_ware_id=:ACTUAL_WARE_ID;
      end
    end
  end
end^

SET TERM ; ^

ALTER TRIGGER WARES_LOG_BU_DISTR INACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348200, 0, 0, 'cowa_bizon', 'SET TERM ^ ;

create or alter procedure PR_AUTO_WARESLINK (
    DOC_ID DM_ID_NULL,
    ACTIVE_ID DM_ID_NULL)
as
declare variable DDA_BARCODE DM_TEXT1024;
declare variable DDA_WARE_ID DM_UUID_NULL;
declare variable DDA_ORIG_COUNTRYID DM_UUID_NULL;
declare variable DDA_ORIG_IZGID DM_UUID_NULL;
declare variable DDA_ORIG_NAMEID DM_UUID_NULL;
declare variable DDA_COUNTRY_ID DM_UUID_NULL;
declare variable DDA_IZG_ID DM_UUID_NULL;
declare variable DDA_NAME_ID DM_UUID_NULL;
declare variable DDA_Z_ID DM_UUID_NULL;
declare variable DDA_SKLAD_ID DM_UUID_NULL;
declare variable DDALOG_BARCODE DM_TEXT1024;
declare variable DDALOG_WARE_ID DM_UUID_NULL;
declare variable DDALOG_ORIG_COUNTRYID DM_UUID_NULL;
declare variable DDALOG_ORIG_IZGID DM_UUID_NULL;
declare variable DDALOG_ORIG_NAMEID DM_UUID_NULL;
declare variable DDALOG_COUNTRY_ID DM_UUID_NULL;
declare variable DDALOG_IZG_ID DM_UUID_NULL;
declare variable DDALOG_NAME_ID DM_UUID_NULL;
declare variable DDALOG_Z_ID DM_UUID_NULL;
declare variable DDALOG_SKLAD_ID DM_UUID_NULL;
declare variable DDALOG2_BARCODE DM_TEXT1024;
declare variable DDALOG2_WARE_ID DM_UUID_NULL;
declare variable DDALOG2_ORIG_COUNTRYID DM_UUID_NULL;
declare variable DDALOG2_ORIG_IZGID DM_UUID_NULL;
declare variable DDALOG2_ORIG_NAMEID DM_UUID_NULL;
declare variable DDALOG2_COUNTRY_ID DM_UUID_NULL;
declare variable DDALOG2_IZG_ID DM_UUID_NULL;
declare variable DDALOG2_NAME_ID DM_UUID_NULL;
declare variable DDALOG2_Z_ID DM_UUID_NULL;
declare variable DDALOG2_SKLAD_ID DM_UUID_NULL;
begin

 select bcode_izg, ware_id,
   (select val_id from PR_GETVAL_ID(dda.sname, 0, dda.part_type)) as name_id,
   (select val_id from PR_GETVAL_ID(dda.sizg, 3, dda.part_type)) as izg_id,
   (select val_id from PR_GETVAL_ID(dda.scountry, 2, dda.part_type)) as country_id,
   (select val_id from PR_GETVAL_ID(dda.sorig_name, 1, dda.part_type)) as orig_name_id,
   (select val_id from PR_GETVAL_ID(dda.sorig_izg, 6, dda.part_type)) as orgi_izg_id,
   (select val_id from PR_GETVAL_ID(dda.sorig_country, 5, dda.part_type)) as orig_country_id,
   Z_ID, SKLAD_ID
 from doc_detail_active dda where doc_id = :doc_id and id = :active_id
 into dda_barcode, dda_ware_id, dda_name_id, dda_izg_id, dda_country_id, dda_orig_nameid, dda_orig_izgid, dda_orig_countryid, dda_z_id, dda_sklad_id;

 select first 1 bcode_izg, ware_id,
   (select val_id from PR_GETVAL_ID(ddal.sname, 0, ddal.part_type)) as name_id,
   (select val_id from PR_GETVAL_ID(ddal.sizg, 3, ddal.part_type)) as izg_id,
   (select val_id from PR_GETVAL_ID(ddal.scountry, 2, ddal.part_type)) as country_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_name, 1, ddal.part_type)) as orig_name_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_izg, 6, ddal.part_type)) as orgi_izg_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_country, 5, ddal.part_type)) as orig_country_id,
   Z_ID, SKLAD_ID
  from doc_detail_active_log ddal where doc_id = :doc_id and id = :active_id order by log_id
  into ddalog_barcode, ddalog_ware_id, ddalog_name_id, ddalog_izg_id, ddalog_country_id, ddalog_orig_nameid, ddalog_orig_izgid, ddalog_orig_countryid, ddalog_z_id, ddalog_sklad_id;

  select first 1 skip 1 bcode_izg, ware_id,
   (select val_id from PR_GETVAL_ID(ddal.sname, 0, ddal.part_type)) as name_id,
   (select val_id from PR_GETVAL_ID(ddal.sizg, 3, ddal.part_type)) as izg_id,
   (select val_id from PR_GETVAL_ID(ddal.scountry, 2, ddal.part_type)) as country_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_name, 1, ddal.part_type)) as orig_name_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_izg, 6, ddal.part_type)) as orgi_izg_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_country, 5, ddal.part_type)) as orig_country_id,
   Z_ID, SKLAD_ID
  from doc_detail_active_log ddal where doc_id = :doc_id and id = :active_id order by log_id
  into ddalog2_barcode, ddalog2_ware_id, ddalog2_name_id, ddalog2_izg_id, ddalog2_country_id, ddalog2_orig_nameid, ddalog2_orig_izgid, ddalog2_orig_countryid, ddalog2_z_id, ddalog2_sklad_id;

 if ( ((dda_ware_id <> ''-1'') and (dda_ware_id <> ''0'') and (dda_ware_id is not null)) and
    ((ddalog2_ware_id <> ''-1'') and (ddalog2_ware_id <> ''0'') and (ddalog2_ware_id is not null))
    and (dda_barcode = ddalog2_barcode)
    and (dda_ware_id <> ddalog2_ware_id) )
 then
 begin
   update wares set name_id = :dda_name_id, izg_id = :dda_izg_id, country_id = :dda_country_id, orig_name_id = :dda_orig_nameid,
    orig_izg_id = :dda_orig_izgid, orig_country_id = :dda_orig_countryid, barcode = :dda_barcode where id = :ddalog2_ware_id;
   exit;
 end

 if ( (ddalog_ware_id = ''0'') or (ddalog_ware_id = ''-1'') or (ddalog_ware_id is null) ) then
 begin
   insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE, Z_ID, SKLAD_ID)
   values (:dda_ware_id, :ddalog_name_id, :ddalog_izg_id, :ddalog_country_id, :ddalog_orig_nameid, :ddalog_orig_izgid,
    :ddalog_orig_countryid, :ddalog_barcode, :ddalog_z_id, :ddalog_sklad_id);

 end
 else if ( ((dda_ware_id <> ''0'') and (dda_ware_id <> ''-1'') and (dda_ware_id is not null)) and
  ((dda_name_id <> ddalog_name_id) or (dda_izg_id <> ddalog_izg_id) or (dda_country_id <> ddalog_country_id) or (dda_orig_nameid <> ddalog_orig_nameid)
  or (dda_orig_izgid <> ddalog_orig_izgid) or (dda_orig_countryid <> ddalog_orig_countryid))
  and (dda_barcode = ddalog_barcode)
  )  then
 begin
   update wares set name_id = :dda_name_id, izg_id = :dda_izg_id, country_id = :dda_country_id, orig_name_id = :dda_orig_nameid,
    orig_izg_id = :dda_orig_izgid, orig_country_id = :dda_orig_countryid, barcode = :dda_barcode where id = :ddalog_ware_id;
 end

end;^


create or alter procedure PR_MAKEGOODWAREVALUES (
    SNAME type of DM_TEXT,
    SORIG_NAME type of DM_TEXT,
    SIZG type of DM_TEXT,
    SORIG_IZG type of DM_TEXT,
    SCOUNTRY type of DM_TEXT,
    SORIG_COUNTRY type of DM_TEXT,
    ORIG_CODE type of DM_TEXT,
    ACTIVE_ID type of DM_ID,
    ALTTYPE DM_STATUS,
    BARCODE DM_TEXT1024 = '''')
returns (
    GOODNAME type of DM_TEXT,
    GOODIZG type of DM_TEXT,
    GOODCOUNTRY type of DM_TEXT,
    GOODBARCODE DM_TEXT)
as
declare variable WARE_ID type of DM_UUID_NULL;
declare variable NAME_ID type of DM_UUID_NULL;
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
declare variable IZG_ID type of DM_UUID_NULL;
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
declare variable COUNTRY_ID type of DM_UUID_NULL;
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
begin

 /*
  select val_id from pr_getval_id(:sname,0,:alttype) into :name_id;
  select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
  select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id;
  select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
  select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id;
  select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
 */

  select id from vals where vtype=0 and alttype=:alttype and svalue=:sname into :name_id;
  select id from vals where vtype=1 and alttype=:alttype and svalue=:sorig_name into :orig_name_id;
  select id from vals where vtype=3 and alttype=:alttype and svalue=:sizg into :izg_id;
  select id from vals where vtype=6 and alttype=:alttype and svalue=:sorig_izg into :orig_izg_id;
  select id from vals where vtype=2 and alttype=:alttype and svalue=:scountry into :country_id;
  select id from vals where vtype=5 and alttype=:alttype and svalue=:sorig_country into :orig_country_id;



  select ware_id from PR_GETWARE_BY_IDS(:name_id,:izg_id,:country_id,:orig_code,:orig_name_id,:orig_izg_id,:orig_country_id,0,1, :barcode) into :ware_id;
  if (ware_id is not null) then
  begin
    --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    select sname, sizg, scountry, barcode from VW_WARES where id=:ware_id into :goodname, :goodizg, :goodcountry, :goodbarcode;
--    if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then
--      insert into doc_detail_active_log(id,sname,sizg,scountry,part_type) values (:active_id,:sname,:sizg,:scountry,:alttype);
    --goodname=''2'';--sname;
  if (goodname is null)   then goodname = sname;
  if (goodizg is null)  then goodizg = sizg;
  if (goodcountry is null)  then goodcountry = scountry;
  if (goodname is null)   then goodname = sorig_name;
  if (goodizg is null)  then goodizg = sorig_izg;
  if (goodcountry is null)  then goodcountry = sorig_country;
  if (goodbarcode is null)  then goodbarcode = barcode;
    suspend;
    exit;
  end 

  select ware_id from PR_GETWARE_BY_IDS(:name_id,:izg_id,:country_id,:orig_code,:orig_name_id,:orig_izg_id,:orig_country_id,0,null, :barcode) into :ware_id;
  if (ware_id is not null) then
  begin
    --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    --goodname=''1'';--sname;
    goodname=sname;
    goodizg=sizg;
    goodcountry=scountry;
    goodbarcode=barcode;

    suspend;
    exit;
  end 


  ware_id=null;
  if (orig_name_id is not null) then
  begin
    select first 1 id from wares where orig_name_id=:orig_name_id order by INSERTDT desc into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where orig_name_id=:orig_name_id order by LOG_INSERTDT desc into :ware_id;
  end
  if ((ware_id is null) and (name_id is not null)) then
  begin
    select first 1 id from wares where name_id=:name_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where name_id=:name_id order by LOG_INSERTDT desc into :ware_id;
  end
  if (ware_id is null) then
  begin
    --goodname=''3'';--sname;
    goodname=sname;
    name_id=null;
  end
  else
  begin
    --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    select w.name_id, v.svalue from WARES w left join vals v on w.name_id=v.id where w.id=:ware_id into :name_id, :goodname;
  end


  ware_id=null;
  if (orig_izg_id is not null) then
  begin
    select first 1 id from wares where orig_izg_id=:orig_izg_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where orig_izg_id=:orig_izg_id order by LOG_INSERTDT desc into :ware_id;
  end
  if ((ware_id is null) and (izg_id is not null)) then
  begin
    select first 1 id from wares where izg_id=:izg_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where izg_id=:izg_id order by LOG_INSERTDT desc into :ware_id;
  end
  if (ware_id is null) then
    goodizg=sizg;
  else
  begin
   --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    select w.izg_id, v.svalue from WARES w left join vals v on w.izg_id=v.id where w.id=:ware_id into :izg_id, :goodizg;
  end


  ware_id=null;
  if (orig_country_id is not null) then
  begin
    select first 1 id from wares where orig_country_id=:orig_country_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where orig_country_id=:orig_country_id order by LOG_INSERTDT desc into :ware_id;
  end
  if ((ware_id is null) and (country_id is not null)) then
  begin
    select first 1 id from wares where country_id=:country_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where country_id=:country_id order by LOG_INSERTDT desc into :ware_id;
  end
  if (ware_id is null) then
    goodcountry=scountry;
  else
  begin
    --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    select w.country_id, v.svalue from WARES w left join vals v on w.country_id=v.id where w.id=:ware_id into :country_id, :goodcountry;
  end

 -- if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then
 --   insert into doc_detail_active_log(id,sname,sizg,scountry, part_type) values (:active_id,:sname,:sizg,:scountry,:alttype);

  --goodname=''3'';
  goodbarcode=barcode;

  if (goodname is null)   then goodname = sname;
  if (goodizg is null)  then goodizg = sizg;
  if (goodcountry is null)  then goodcountry = scountry;
  if (goodname is null)   then goodname = sorig_name;
  if (goodizg is null)  then goodizg = sorig_izg;
  if (goodcountry is null)  then goodcountry = sorig_country;


  suspend;
end^

create or alter procedure PR_GET_WARE (
    SNAME type of DM_TEXT,
    SIZG type of DM_TEXT,
    SCOUNTRY type of DM_TEXT,
    ORIG_CODE type of DM_TEXT = '''',
    SORIG_NAME type of DM_TEXT = '''',
    SORIG_IZG type of DM_TEXT = '''',
    SORIG_COUNTRY type of DM_TEXT = '''',
    BARCODE type of DM_TEXT = '''',
    Z_ID type of DM_ID = 0,
    SKLAD_ID DM_TEXT = '''',
    ALTTYPE DM_STATUS = 0,
    MNN DM_TEXT = '''')
returns (
    W_ID type of DM_UUID_NULL)
as
declare variable NAME_ID type of DM_UUID_NULL;
declare variable IZG_ID type of DM_UUID_NULL;
declare variable COUNTRY_ID type of DM_UUID_NULL;
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
begin

  select VAL_ID from PR_GETVAL_ID(:SNAME, 0, :ALTTYPE, :MNN) into :NAME_ID;
  select VAL_ID from PR_GETVAL_ID(:SIZG, 3, :ALTTYPE) into :IZG_ID;
  select VAL_ID from PR_GETVAL_ID(:SCOUNTRY, 2, :ALTTYPE) into :COUNTRY_ID;
  select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
  select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
  select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
  /*
select first 1 id from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and
ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and
ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id -- into :w_id;
and barcode=:barcode into :w_id;
*/
  select ID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and BARCODE = :BARCODE into :W_ID;

  if (W_ID is null) then
  begin
    --    exception EX_WRONG_OPER;
    --w_id=gen_id(gen_wares_id,1);
    W_ID = uuid_to_char(gen_uuid());
    insert into WARES (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, BARCODE, Z_ID, SKLAD_ID)
    values (:W_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, :BARCODE, :Z_ID, :SKLAD_ID);
  end
  suspend;
end;^

create or alter procedure PR_GETWARE_BY_IDS (
    NAME_ID type of DM_UUID_NULL,
    IZG_ID type of DM_UUID_NULL,
    COUNTRY_ID type of DM_UUID_NULL,
    ORIG_CODE type of DM_TEXT,
    ORIG_NAME_ID type of DM_UUID_NULL,
    ORIG_IZG_ID type of DM_UUID_NULL,
    ORIG_COUNTRY_ID type of DM_UUID_NULL,
    DOINSERT type of DM_STATUS,
    SEARCHINLOGS type of DM_STATUS,
    BARCODE DM_TEXT)
returns (
    WARE_ID type of DM_UUID_NULL)
as
begin
  if (orig_code is null) then orig_code='''';
  if (searchinlogs is null) then searchinlogs=0;
  if (searchinlogs=1) then
  begin 
    select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where name_id=:name_id and izg_id=:izg_id and country_id=:country_id and barcode=:barcode
    order by log_insertdt desc
    into :ware_id;
  end
  else
  begin
    select first 1 id from wares where name_id=:name_id and izg_id=:izg_id and country_id=:country_id and barcode=:barcode

      into :ware_id;
  end
  if ((ware_id is null) and (DOINSERT=1) and (searchinlogs<>1)) then
  begin
--    ware_id=gen_id(gen_wares_id,1);
    ware_id=UUID_TO_CHAR(GEN_UUID());
    insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,Z_ID,SKLAD_ID, barcode)
     values
       (:ware_id,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,0,'''',:barcode);
  end
  suspend;
end;^

create or alter procedure PR_DOC_COMMIT (
    DOC_ID type of DM_ID,
    SESSION_ID type of DM_ID)
as
declare variable WRONG_WARES DM_STATUS;
declare variable BASE_TYPE type of DM_STATUS;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable DSCSUMMA type of DM_DOUBLE;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable PRICE_TYPE DM_STATUS;
declare variable CONTRACT_ID DM_ID;
declare variable W_PARAM DM_STATUS;
declare variable WARES_MODE DM_STATUS;
begin
  select DT.BASE_TYPE, D.PRICE_TYPE, D.CONTRACT_ID from DOCS D
  left join DOC_TYPES DT on D.DOC_TYPE = DT.ID where D.ID = :DOC_ID into :BASE_TYPE, :PRICE_TYPE, :CONTRACT_ID;
  if (BASE_TYPE in (1, 3, 6, 8)) then
  begin

    wrong_wares = 0; wares_mode = 1;
    select PARAM_VALUE from PR_GETPARAMVALUE(''WARES_MODE'', 0) into wares_mode;
    if (wares_mode = 0) then
    begin
      select count(id) from doc_detail_active where doc_id = :doc_id and ((ware_id = ''0'') or (ware_id = ''-1'') or (ware_id = '''')) into wrong_wares;
      if (wrong_wares > 0) then exception EX_WRONG_OPER;
    end

    execute procedure PR_DOC_PRIHOD_COMMIT(:DOC_ID, 0);
  end
  else
  if (BASE_TYPE in (2, 7, 9)) then
    execute procedure PR_DOC_RASHOD_COMMIT(:DOC_ID, 0);
  else
    exception EX_WRONGDOC_BASE_TYPE;
  delete from DOC_DETAIL_ACTIVE where DOC_ID = :DOC_ID;
  if (BASE_TYPE = 3) then
  begin
    delete from WAREBASE where PART_ID in (select PART_ID from DOC_DETAIL where DOC_ID = :DOC_ID and QUANT < 0 and PART_TYPE = 1);
  end
  select sum(SUMMA), sum(SUMMA_O), sum(SUM_NDSO), sum(PRICE * QUANT / 10000), sum(SUM_DSC) from DOC_DETAIL where DOC_ID = :DOC_ID into :SUMMA,
       :SUMMA_O, :SUM_NDSO, :DSCSUMMA, :SUM_DSC;
  DSCSUMMA = DSCSUMMA * 10000;
  update DOCS set COMMITSESSION_ID = :SESSION_ID, STATUS = 1, POSTDT = ''now'',
                  CHECKDATA = (select CHECKDATA from PR_DOCCHECKDATA(:DOC_ID)), SUMMA = :SUMMA, SUMMA_O = :SUMMA_O,
                  SUM_NDSO = :SUM_NDSO, CALCSUMMA = :DSCSUMMA, SUM_DSC = :SUM_DSC, CASHDATA = (iif(:BASE_TYPE = 1, -:SUMMA, null)) where ID = :DOC_ID;
  execute procedure PR_UPDBLOCKINFO_BY_DOC(:DOC_ID, 1);
  --if ((base_type = 2) and (contract_id<>0)) then
  if (CONTRACT_ID <> 0) then
  begin
    if (BASE_TYPE = 2) then
      execute procedure PR_CREDITCOMMIT(:CONTRACT_ID);
    if (BASE_TYPE in (8, 9)) then
      execute procedure PR_CREDITREINITCOMMIT(:CONTRACT_ID, :SESSION_ID);
    --    execute procedure PR_CREDITCOMMIT(:contract_id);
  end
  execute procedure PR_DOEVENT(''EV_DOCCOMMIT'');
end;^


CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BI FOR DOC_DETAIL_ACTIVE
ACTIVE BEFORE INSERT POSITION 0
as
begin

    --Если в документ ввод услуг вставлять через буфер обмена, то тип строк - товар, а не услуга
    if ((select dt.base_type from docs d left join doc_types dt on dt.id=d.doc_type where d.id=new.doc_id)=6) then
      new.part_type=1;


  if (new.ID is null) then
    new.ID = gen_id(GEN_DOC_DETAIL_ACTIVE_ID, 1);
  new.INSERTDT = ''now'';
  if (new.BASE_AGENT_ID is null) then
    new.BASE_AGENT_ID = 0;
  if (new.STATUS is null) then
    new.STATUS = 0;
  if (new.PARENT_ID is null) then
    new.PARENT_ID = 0;
  if (new.PART_TYPE is null) then
    new.PART_TYPE = 0;


  --первая строчка =  до привидения, оригианльные значения накладной
  select first 1 ID from WARES where NAME_ID = new.NAME_ID and IZG_ID = new.IZG_ID and COUNTRY_ID = new.COUNTRY_ID and BARCODE = new.BCODE_IZG into new.WARE_ID;

  insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
                                     DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE,
                                     BARCODE1, BCODE_IZG,
                                     GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM,
                                     NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID,
                                     Z_ID, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY,
                                     INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW,
                                     PART_TYPE, BASE_AGENT_ID)
  values (new.ID, new.PARENT_ID, new.DOC_ID, new.PART_ID, new.PART_PARENT_ID, new.DOC_DETAIL_ID, new.PRICE, new.NAC,
          new.QUANT, new.DISCOUNT, new.SUMMA, new.SUMMA_O, new.DCARD, new.WARE_ID, new.PRICE_O, new.PRICE_Z,
          new.PRICE_R, new.BARCODE, new.BARCODE1, new.BCODE_IZG,
          new.GODENDO, new.SERIA, new.NDS, new.SUM_NDSO, new.SERT, new.DATESERT, new.KEMVSERT, new.SDSERT, new.REGN,
          new.NGTD, new.EDIZM, new.NAME_ID, new.IZG_ID, new.COUNTRY_ID, new.ORIG_CODE, new.ORIG_NAME_ID,
          new.ORIG_IZG_ID, new.ORIG_COUNTRY_ID, new.Z_ID, new.SKLAD_ID, new.SNAME, new.SIZG, new.SCOUNTRY,
          new.SORIG_NAME, new.SORIG_IZG, new.SORIG_COUNTRY, new.INSERTDT, new.INFO, new.KOEF, new.MOTHERPART_ID,
          new.DEP, new.SUM_DSC, new.HUMAN_QUANT, new.CUSTOMDRAW, new.PART_TYPE, new.BASE_AGENT_ID);
  --

  if ((new.PART_ID is null) or (new.PART_ID = 0)) then
  begin
    -->cf20110923
    --if (new.ware_id<0) then
    if (new.WARE_ID = ''-1'') then
    begin
      select GOODNAME, GOODIZG, GOODCOUNTRY, GOODBARCODE from PR_MAKEGOODWAREVALUES(new.SNAME, new.SORIG_NAME, new.SIZG, new.SORIG_IZG, new.SCOUNTRY, new.SORIG_COUNTRY, new.ORIG_CODE, new.ID, new.PART_TYPE, new.bcode_izg)
      into new.SNAME, new.SIZG, new.SCOUNTRY, new.BCODE_IZG;
    end
    --<cf20110923
    new.PART_ID = 0;
    select ID from VALS where VTYPE = 0 and ALTTYPE = new.PART_TYPE and SVALUE = new.SNAME into new.NAME_ID;
    select ID from VALS where VTYPE = 3 and ALTTYPE = new.PART_TYPE and SVALUE = new.SIZG into new.IZG_ID;
    select ID from VALS where VTYPE = 2 and ALTTYPE = new.PART_TYPE and SVALUE = new.SCOUNTRY into new.COUNTRY_ID;
    select ID from VALS where VTYPE = 1 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_NAME into new.ORIG_NAME_ID;
    select ID from VALS where VTYPE = 6 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_IZG into new.ORIG_IZG_ID;
    select ID from VALS where VTYPE = 5 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_COUNTRY into new.ORIG_COUNTRY_ID;
    new.WARE_ID = 0;
    select first 1 ID from WARES where NAME_ID = new.NAME_ID and IZG_ID = new.IZG_ID and COUNTRY_ID = new.COUNTRY_ID and BARCODE = new.BCODE_IZG into new.WARE_ID;

    --вторая строчка = после приведения
    insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
                                     DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE,
                                     BARCODE1, BCODE_IZG,
                                     GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM,
                                     NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID,
                                     Z_ID, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY,
                                     INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW,
                                     PART_TYPE, BASE_AGENT_ID)
    values (new.ID, new.PARENT_ID, new.DOC_ID, new.PART_ID, new.PART_PARENT_ID, new.DOC_DETAIL_ID, new.PRICE, new.NAC,
          new.QUANT, new.DISCOUNT, new.SUMMA, new.SUMMA_O, new.DCARD, new.WARE_ID, new.PRICE_O, new.PRICE_Z,
          new.PRICE_R, new.BARCODE, new.BARCODE1, new.BCODE_IZG,
          new.GODENDO, new.SERIA, new.NDS, new.SUM_NDSO, new.SERT, new.DATESERT, new.KEMVSERT, new.SDSERT, new.REGN,
          new.NGTD, new.EDIZM, new.NAME_ID, new.IZG_ID, new.COUNTRY_ID, new.ORIG_CODE, new.ORIG_NAME_ID,
          new.ORIG_IZG_ID, new.ORIG_COUNTRY_ID, new.Z_ID, new.SKLAD_ID, new.SNAME, new.SIZG, new.SCOUNTRY,
          new.SORIG_NAME, new.SORIG_IZG, new.SORIG_COUNTRY, new.INSERTDT, new.INFO, new.KOEF, new.MOTHERPART_ID,
          new.DEP, new.SUM_DSC, new.HUMAN_QUANT, new.CUSTOMDRAW, new.PART_TYPE, new.BASE_AGENT_ID);
    --

  end
  if (new.PART_PARENT_ID is null) then
    new.PART_PARENT_ID = 0;
  if (new.WARE_ID is null) then
    new.WARE_ID = 0;
  if (new.NAME_ID is null) then
    new.NAME_ID = 0;
  if (new.IZG_ID is null) then
    new.IZG_ID = 0;
  if (new.COUNTRY_ID is null) then
    new.COUNTRY_ID = 0;
  if (new.ORIG_NAME_ID is null) then
    new.ORIG_NAME_ID = 0;
  if (new.ORIG_IZG_ID is null) then
    new.ORIG_IZG_ID = 0;
  if (new.ORIG_COUNTRY_ID is null) then
    new.ORIG_COUNTRY_ID = 0;
  if (new.Z_ID is null) then
    new.Z_ID = 0;
  if (new.SKLAD_ID is null) then
    new.SKLAD_ID = '''';
  if (new.MOTHERPART_ID is null) then
    new.MOTHERPART_ID = 0;
  if (new.DOC_DETAIL_ID is null) then
    new.DOC_DETAIL_ID = 0;

  if (new.PART_ID > 0) then -- заполняем значения партии
  begin
select
WARE_ID,
PRICE,
PRICE_O,
PRICE_Z,
PRICE_R,
--QUANT,
BARCODE,
BARCODE1,
--KRITK,
GODENDO,
SERIA,
NDS,
--SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
nac,
dep,
part_type,
base_agent_id,
group_id,
MOTHERPART_UUID
from parts where id=new.part_id into
new.WARE_ID,
new.PRICE,
new.PRICE_O,
new.PRICE_Z,
new.PRICE_R,
--new.QUANT,
new.BARCODE,
new.BARCODE1,
--new.KRITK,
new.GODENDO,
new.SERIA,
new.NDS,
--new.SUM_NDSO,
new.SERT,
new.DATESERT,
new.KEMVSERT,
new.SDSERT,
new.REGN,
new.NGTD,
new.EDIZM,
new.nac,
new.dep,
new.part_type,
new.base_agent_id,
new.group_id,
new.MOTHERPART_UUID;
  end
  if (new.GROUP_ID is null) then
    new.GROUP_ID = 0;
  --if (new.ware_id>0) then -- заполняем значения позиции
  if (new.WARE_ID not in (''-1'', ''0'')) then -- заполняем значения позиции
  begin
    select WW.NAME_ID,
           (select SVALUE from VALS where ID = WW.NAME_ID), WW.IZG_ID,
           (select SVALUE from VALS where ID = WW.IZG_ID), WW.COUNTRY_ID,
           (select SVALUE from VALS where ID = WW.COUNTRY_ID), WW.ORIG_CODE, WW.ORIG_NAME_ID,
           (select SVALUE from VALS where ID = WW.ORIG_NAME_ID), WW.ORIG_IZG_ID,
           (select SVALUE from VALS where ID = WW.ORIG_IZG_ID), WW.ORIG_COUNTRY_ID,
           (select SVALUE from VALS where ID = WW.ORIG_COUNTRY_ID), WW.BARCODE, WW.Z_ID, WW.SKLAD_ID from WARES WW where WW.ID = new.WARE_ID into new.NAME_ID,
         new.SNAME, new.IZG_ID, new.SIZG, new.COUNTRY_ID, new.SCOUNTRY, new.ORIG_CODE, new.ORIG_NAME_ID, new.SORIG_NAME,
         new.ORIG_IZG_ID, new.SORIG_IZG, new.ORIG_COUNTRY_ID, new.SORIG_COUNTRY, new.BCODE_IZG, new.Z_ID, new.SKLAD_ID;
  end
  else
  begin
    --if (new.ware_id<0) then
    if (new.WARE_ID = ''-1'') then
    begin
      select GOODNAME, GOODIZG, GOODCOUNTRY from PR_MAKEGOODWAREVALUES(new.SNAME, new.SORIG_NAME, new.SIZG, new.SORIG_IZG, new.SCOUNTRY, new.SORIG_COUNTRY, new.ORIG_CODE, new.ID, new.PART_TYPE, new.bcode_izg) into new.SNAME,
           new.SIZG, new.SCOUNTRY;
      new.WARE_ID = 0;
    end
  end

if (new.quant is null) then new.quant=0;
if (new.price is null) then new.price=0;
if (new.price_o is null) then new.price_o=0;
if (new.nds is null) then new.nds=0;
if (new.summa is null) then new.summa=new.quant*new.price;
if (new.sum_dsc is null) then new.sum_dsc=0;
if (new.summa_o is null) then
begin
new.summa_o=new.quant*new.price_o;
end
else
begin
if (new.quant<>0) then
new.price_o=new.summa_o/new.quant;
end

--В любом случае перерасчитываем наценку, т.к. в автопереоценках идет вставка старой наценки А.К. 20151020
--if (((new.nac is null) or (new.nac=0)) and new.price_o<>0)  then
if (new.price_o<>0)  then
new.nac=(new.price-new.price_o)*100/new.price_o;

if (new.dep is null) then
begin
select first 1 id from deps where nds=new.nds order by id into new.dep;
end
if (new.dep is null) then
new.dep=0;

-- В любом случае перерасчитываем НДСы, т.к. при возврате могут отличаться на копейки А.К. 20151113
--if ( (new.sum_ndso is null) or (new.sum_ndso=0) ) then
 new.sum_ndso=round(new.summa_o*new.nds/(100+new.nds),2);
--if ( (new.sum_ndsr is null) or (new.sum_ndso=0) ) then
 new.sum_ndsr=new.summa*(select ndsr from deps where id=new.dep)/(100+(select ndsr from deps where id=new.dep));

--if ((new.barcode is null) or (trim(new.barcode)='''') or (new.part_id=0) ) then ---> 20130301-ann gen_barcode_by_name
if ((new.barcode is null) or (trim(new.barcode)='''')) then ---> 20150320 A.K.
begin
if ((select PARAM_VALUE from PR_GETPARAMVALUE(''GEN_BARCODE'',0))=0) then
select res_bcode from utpr_getchecksum_ean13(new.id) into new.barcode;
else select res_bcode from utpr_getchecksum_ean13((select l_id from vals where d$uuid=new.name_id),''88'') into new.barcode;
end
---> 20130301-ann gen_barcode_by_name
if (new.part_parent_id>0) then
begin
update doc_detail_active set
quant=quant-new.quant,
summa=summa-new.summa,
summa_o=summa_o-new.summa_o,
sum_ndso=sum_ndso-new.sum_ndso,
sum_ndsr=sum_ndsr-new.sum_ndsr
where id=new.part_parent_id;
execute procedure PR_COPY_MEMBERSHIP(''DOC_DETAIL_ACTIVE'',new.part_parent_id,new.id);
end

--Если указано наименование, но не указан ШК изготовителя - ищем его в партиях и подставляем по умолчанию 20.03.2015 A.K.
if (new.name_id is not null and (new.bcode_izg is null or Trim(new.bcode_izg) = '''' )
     and exists(select first 1 id from docs where doc_type in (1,2,20) and id = new.doc_id)) then
--20151113 A.K.
 new.bcode_izg = (select list(barcode,'','') from PR_GET_ALL_BARCODE(new.name_id));
-- select first 1 w.barcode from wares w where w.name_id = new.name_id into new.bcode_izg;
-- select first 1 w.barcode from wares w inner join vals v on w.name_id = v.id or w.orig_name_id = v.id
-- where w.barcode is not null and Trim(w.barcode) <> '''' and ( v.svalue =new.sname or v.svalue = new.sorig_name )
-- into new.bcode_izg;



--Для "Заказ оптовику" при отрицательном или нулевом кол-ве товара - берем кол-во из последнего прихода 02.03.2015  A.K.
if (((Select Doc_type From docs where id = new.doc_id) = 36) and new.quant <= 0 )
  then
  begin
    new.quant = (
      select first 1 dd.quant from doc_detail dd
        inner join docs d on dd.doc_id = d.id
        inner join parts p on dd.part_id=p.id
        inner join WARES w on p.ware_id=w.id
      where d.doc_type in (1,2) and w.name_id = new.name_id order by dd.insertdt desc );
    if (new.quant is null or new.quant = 0) then new.quant = 1;
  end

--Для "Заказ оптовику" цену и цену опт берем из остатков 19.10.2015  A.K.
if ((Select Doc_type From docs where id = new.doc_id) = 36) Then
Begin
 select first 1 price, price_o, part_id,ware_id from warebase where name_id = new.name_id order by docdate desc
 into new.price, new.price_o, new.part_id,new.ware_id;
end

--Заявка 656799 от 14.07.2016 A.K.
--Ткаченко: При добавлении позиции из остатков в активный документ прихода от поставщика розничную цену ставить 0, чтобы не забывали наценивать накладные
if ((Select Doc_type From docs where id = new.doc_id) = 1) Then
begin
 new.part_id=0;
 new.price=0;
 new.nac=-100;
 new.summa=0;
end

end
^

CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BU0 FOR DOC_DETAIL_ACTIVE
ACTIVE BEFORE UPDATE POSITION 0
as
begin
if (new.nds<>old.nds) then  --Изменение НДС только через отдел   OlgaV 20141114
begin
  select first 1 id from deps where status=0 and nds=new.nds into new.dep;
  new.nds=old.nds;
end
if (new.dep<>old.dep) then
  select nds from deps where id=new.dep into new.nds;
if ((new.nds<>old.nds) or (new.sum_ndso is null) or (new.sum_ndsr is null) ) then
begin
  new.sum_ndso=round(new.summa_o*new.nds/(100+new.nds),2);
  new.sum_ndsr=new.summa*(select ndsr from deps where id=new.dep)/(100+(select ndsr from deps where id=new.dep));
end       --Пересчет сумм НДС Olgav20141114

if (abs(new.sum_ndso-round((new.quant*new.price_o)*new.nds/(100+new.nds),2))>0.05) then
  new.sum_ndso=round((new.quant*new.price_o)*new.nds/(100+new.nds),2);
if (abs(new.sum_ndsr-(new.summa)*(100+(select d.ndsr from deps d where d.id=new.dep))/(100+(select d.ndsr from deps d where d.id=new.dep)))>0.05) Then
  new.sum_ndsr=(new.summa)*(select d.ndsr from deps d where d.id=new.dep)/(100+(select d.ndsr from deps d where d.id=new.dep));


if ( (new.sname <> old.sname) or (new.name_id is null) or (new.name_id = ''0'') )   then
select val_id from pr_getval_id(new.sname,0,new.part_type,new.mnn) into new.name_id;
--select id from vals where vtype=0 and alttype=new.part_type and svalue=new.sname into new.name_id;
if (new.name_id is null) then new.name_id=0;
if ( (new.summa is null) and ((new.quant<>0) and (new.price<>0)) ) then
begin
new.summa=new.quant*new.price;
if (new.sum_dsc is not null) then new.summa = new.summa - new.sum_dsc;
end
if ( ((new.summa_o = 0) or (new.summa_o is null) ) and ((new.quant<>0) and (new.price_o<>0)) ) then
new.summa_o=new.quant*new.price_o;
--if (new.name_id <> old.name_id) then execute procedure PR_COPYGROUPS(old.name_id, new.name_id); 23072015 A.K.
if (new.sum_dsc is null) then
if (new.summa<>old.summa) then
begin
new.sum_dsc=(1+new.discount/100)*100*new.summa/(100+old.discount)-100*new.summa/(100+old.discount);
--new.summa=(1+new.discount/100)*100*old.summa/(100+old.discount);
end
if (new.sum_dsc is null) then
if (new.discount<>old.discount) then
begin
new.sum_dsc=(1+new.discount/100)*100*old.summa/(100+old.discount)-100*old.summa/(100+old.discount);
new.summa=(1+new.discount/100)*100*old.summa/(100+old.discount);
end

select id from vals where vtype=0 and alttype=new.part_type and svalue=new.sname into new.name_id;
if (new.name_id is null) then new.name_id=0;
select id from vals where vtype=3 and alttype=new.part_type and svalue=new.sizg into new.izg_id;
if (new.izg_id is null) then new.izg_id=0;
select id from vals where vtype=2 and alttype=new.part_type and svalue=new.scountry into new.country_id;
if (new.country_id is null) then new.country_id=0;
select id from vals where vtype=1 and alttype=new.part_type and svalue=new.sorig_name into new.orig_name_id;
if (new.orig_name_id is null) then new.orig_name_id=0;
select id from vals where vtype=6 and alttype=new.part_type and svalue=new.sorig_izg into new.orig_izg_id;
if (new.orig_izg_id is null) then new.orig_izg_id=0;
select id from vals where vtype=5 and alttype=new.part_type and svalue=new.sorig_country into new.orig_country_id;
if (new.orig_country_id is null) then new.orig_country_id=0;

if ((old.part_id<>0) and (new.part_id=0) and (select deleted_doc_id from docs where id=new.doc_id) is null ) then
begin
if ((select PARAM_VALUE from PR_GETPARAMVALUE(''GEN_BARCODE'',0))=0) then
select res_bcode from utpr_getchecksum_ean13(new.id) into new.barcode;
else select res_bcode from utpr_getchecksum_ean13((select l_id from vals where d$uuid=new.name_id),''88'') into new.barcode;
end

if ((select PARAM_VALUE from pr_getparamvalue(''CHANGE_BRC_FOR_CORRECT'',''0'')) = ''0'') Then --Менять ШК в док-тах корректировки
  if ((select d.doc_type from docs d where d.id=new.doc_id) in (7,8,22) and new.quant>0 and new.barcode<>'''' and new.barcode is not null) then
    new.barcode=(select barcode from doc_detail_active where id=new.parent_id);

--Если указано наименование, но не указан ШК изготовителя - ищем его в партиях и подставляем по умолчанию 20.03.2015 A.K.
if (new.name_id is not null and (new.bcode_izg is null or Trim(new.bcode_izg) = '''' )
     and exists(select first 1 id from docs where doc_type in (1,2,20) and id = new.doc_id)) then
 select first 1 w.barcode from wares w where w.name_id = new.name_id into new.bcode_izg;

--Менять оптовую цену можно только в документах прихода от поставщика и оприходования (Заявка 574482 от 26.04.2016 А.К.)
if ( (coalesce(new.price_o,0) <> coalesce(old.price_o,0)) and ((select doc_type from docs where id = new.doc_id) not in (1,20)) ) then
      exception EX_ZAPRET_PRICE_O;

exit; -- 20160424 A.K.


  insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
                                     DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE,
                                     BARCODE1, BCODE_IZG,
                                     --KRITK,
                                     GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM,
                                     NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID,
                                     Z_ID, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY,
                                     INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW,
                                     PART_TYPE, BASE_AGENT_ID)
  values (old.ID, old.PARENT_ID, old.DOC_ID, old.PART_ID, old.PART_PARENT_ID, old.DOC_DETAIL_ID, old.PRICE, old.NAC,
          old.QUANT, old.DISCOUNT, old.SUMMA, old.SUMMA_O, old.DCARD, old.WARE_ID, old.PRICE_O, old.PRICE_Z,
          old.PRICE_R, old.BARCODE, old.BARCODE1, old.BCODE_IZG,
          --old.KRITK,
          old.GODENDO, old.SERIA, old.NDS, old.SUM_NDSO, old.SERT, old.DATESERT, old.KEMVSERT, old.SDSERT, old.REGN,
          old.NGTD, old.EDIZM, old.NAME_ID, old.IZG_ID, old.COUNTRY_ID, old.ORIG_CODE, old.ORIG_NAME_ID,
          old.ORIG_IZG_ID, old.ORIG_COUNTRY_ID, old.Z_ID, old.SKLAD_ID, old.SNAME, old.SIZG, old.SCOUNTRY,
          old.SORIG_NAME, old.SORIG_IZG, old.SORIG_COUNTRY, old.INSERTDT, old.INFO, old.KOEF, old.MOTHERPART_ID,
          old.DEP, old.SUM_DSC, old.HUMAN_QUANT, old.CUSTOMDRAW, old.PART_TYPE, old.BASE_AGENT_ID);
end
^



create or alter procedure PR_DOC_DETAIL_ACTIVE_ONCOMMIT (
    ACTIVE_ID type of DM_ID,
    COMMIT_ID type of DM_ID,
    DOC_ID type of DM_ID,
    COMMITDATE type of DM_DATETIME)
as
begin
/*
  эта процедура выполняется для каждой записи, добавляемой в DOC_DETAIL в процедурах проведения документов
*/
  /* Для вречей*/
  update doc_detail_doctor set doc_detail_id=:commit_id, doc_id=:doc_id, commitdate=:commitdate where DOC_DETAIL_ACTIVE_ID=:active_id;
  /* Старые рецепты*/
  update DOC_DETAIL_MAKE set doc_detail_id=:commit_id, doc_id=:doc_id, commitdate=:commitdate where DOC_DETAIL_ACTIVE_ID=:active_id;
  /* Новые рецепты*/
 update recepts set table_id=:commit_id, table_name=''DOC_DETAIL'', status=1 where doc_detail_active_id=:active_id; --для рецептов

 execute procedure PR_AUTO_WARESLINK(:DOC_ID, :ACTIVE_ID);

end;^

SET TERM ; ^


CREATE OR ALTER VIEW VW_WARES(
    ID,
    NAME_ID,
    SNAME,
    IZG_ID,
    SIZG,
    COUNTRY_ID,
    SCOUNTRY,
    ORIG_CODE,
    ORIG_NAME_ID,
    SORIG_NAME,
    ORIG_IZG_ID,
    SORIG_IZG,
    ORIG_COUNTRY_ID,
    SORIG_COUNTRY,
    BARCODE,
    Z_ID,
    SKLAD_ID,
    INSERTDT,
    PACKET,
    MGN_NAME,
    MGN_ID,
    MGN_SOURCE)
AS
select
w.ID,
w.NAME_ID,
w.sname,--(select svalue from vals where id = w.name_id),
w.IZG_ID,
(select svalue from vals where id = w.izg_id),
w.COUNTRY_ID,
(select svalue from vals where id = w.country_id),
w.ORIG_CODE,
w.ORIG_NAME_ID,
(select svalue from vals where id = w.orig_name_id),
w.ORIG_IZG_ID,
(select svalue from vals where id = w.orig_izg_id),
w.ORIG_COUNTRY_ID,
(select svalue from vals where id = w.orig_country_id),
w.BARCODE,
w.Z_ID,
w.SKLAD_ID,
w.INSERTDT,
w.PACKET,
w.mgn_name,
w.mgn_id,
w.mgn_source
from wares w
;

/******************************************************************************/
/***               Generated by IBExpert 12.08.2016 17:39:43                ***/
/******************************************************************************/

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



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


CREATE GENERATOR GEN_WARES_TREB_ID;

CREATE TABLE WARES_TREB (
    ID               DM_ID NOT NULL /* DM_ID = BIGINT */,
    WARE_ID          DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
    NAME_ID          DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
    IZG_ID           DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
    COUNTRY_ID       DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
    ORIG_CODE        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    ORIG_NAME_ID     DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
    ORIG_IZG_ID      DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
    ORIG_COUNTRY_ID  DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
    Z_ID             DM_ID NOT NULL /* DM_ID = BIGINT */,
    SNAME            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SIZG             DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SCOUNTRY         DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SORIG_NAME       DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SORIG_IZG        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SORIG_COUNTRY    DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SKLAD_ID         DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
    PRICE            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    QUANT            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SUMMA            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SUMMA_O          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    PRICE_O          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    PRICE_Z          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    PRICE_R          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    BARCODE          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    BARCODE1         DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    GODENDO          DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    SERIA            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    NDS              DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SUM_NDSO         DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SERT             DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    DATESERT         DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    KEMVSERT         DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SDSERT           DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    REGN             DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    NGTD             DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    EDIZM            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    INSERTDT         DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    BCODE_IZG        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    DDA_UUID         DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
    CMT_STATUS       DM_STATUS /* DM_STATUS = INTEGER */,
    CMT_DT           DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    CMT_USER         DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    CMT_PROFILE_ID   DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    PART_TYPE        DM_STATUS /* DM_STATUS = INTEGER */,
    RQ_USER          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    RQ_PROFILE_ID    DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    D$SRVUPDDT       DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    D$UUID           DM_UUID /* DM_UUID = CHAR(36) NOT NULL */
);




/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE WARES_TREB ADD CONSTRAINT PK_WARES_TREB PRIMARY KEY (D$UUID);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



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


/* Trigger: WARES_TREB_AU0 */
CREATE OR ALTER TRIGGER WARES_TREB_AU0 FOR WARES_TREB
ACTIVE AFTER UPDATE POSITION 0
AS
declare variable name_id type of DM_UUID;
declare variable sname type of DM_TEXT;
declare variable izg_id type of DM_UUID;
declare variable sizg type of DM_TEXT;
declare variable country_id type of DM_UUID;
declare variable scountry type of DM_TEXT;
declare variable orig_code type of DM_TEXT;
declare variable orig_name_id type of DM_UUID;
declare variable sorig_name type of DM_TEXT;
declare variable orig_izg_id type of DM_UUID;
declare variable sorig_izg type of DM_TEXT;
declare variable orig_country_id type of DM_UUID;
declare variable sorig_country  type of DM_TEXT;
declare variable barcode  type of DM_TEXT;
begin
  if (not exists (select d$uuid from doc_detail_active where d$uuid=new.dda_uuid)) then exit;
  if (not exists (select d$uuid from wares where d$uuid=new.ware_id)) then exit;
  select
    w.name_id, (select svalue from vals v1 where v1.id=w.name_id),
    w.izg_id, (select svalue from vals v2 where v2.id=w.izg_id),
    w.country_id, (select svalue from vals v3 where v3.id=w.country_id),
    w.orig_code,
    w.orig_name_id, (select svalue from vals v4 where v4.id=w.orig_name_id),
    w.orig_izg_id, (select svalue from vals v5 where v5.id=w.orig_izg_id),
    w.orig_country_id, (select svalue from vals v6 where v6.id=w.orig_country_id),
    w.barcode
  from wares w where d$uuid=new.ware_id into name_id,sname,  izg_id,sizg,  country_id,scountry,  orig_code,
                                             orig_name_id,sorig_name,  orig_izg_id,sorig_izg,  orig_country_id,sorig_country,
                                             barcode;
  update doc_detail_active set ware_id=new.ware_id,
    name_id=:name_id,sname=:sname,
    izg_id=:izg_id,sizg=:sizg,
    country_id=:country_id,scountry=:scountry,
    orig_code=:orig_code,
    orig_name_id=:orig_name_id,sorig_name=:sorig_name,
    orig_izg_id=:orig_izg_id,sorig_izg=:sorig_izg,
    orig_country_id=:orig_country_id,sorig_country=:sorig_country,
    bcode_izg=:barcode
   where d$uuid=new.dda_uuid;
  execute procedure PR_DOEVENT(''EV_WARES_TREB'');
end
^


/* Trigger: WARES_TREB_BI */
CREATE OR ALTER TRIGGER WARES_TREB_BI FOR WARES_TREB
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_wares_treb_id,1);
  new.insertdt=current_timestamp;
  if (new.CMT_STATUS is null) then
    new.CMT_STATUS=0;
  if (new.d$uuid is null) then --создание требования
  begin
    select null,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,Z_ID,
           SORIG_NAME,SORIG_IZG,SORIG_COUNTRY,SKLAD_ID,PRICE,QUANT,SUMMA,SUMMA_O,PRICE_O,PRICE_Z,PRICE_R,BARCODE,BARCODE1,
           GODENDO,SERIA,NDS,SUM_NDSO,SERT,DATESERT,KEMVSERT,SDSERT,REGN,NGTD,EDIZM,INSERTDT
     from doc_detail_active where d$uuid=new.dda_uuid into
       new.WARE_ID,new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.ORIG_CODE,new.ORIG_NAME_ID,new.ORIG_IZG_ID,new.ORIG_COUNTRY_ID,
       new.Z_ID,new.SORIG_NAME,new.SORIG_IZG,new.SORIG_COUNTRY,new.SKLAD_ID,new.PRICE,new.QUANT,
       new.SUMMA,new.SUMMA_O,new.PRICE_O,new.PRICE_Z,new.PRICE_R,new.BARCODE,new.BARCODE1,new.GODENDO,new.SERIA,new.NDS,new.SUM_NDSO,
       new.SERT,new.DATESERT,new.KEMVSERT,new.SDSERT,new.REGN,new.NGTD,new.EDIZM,new.INSERTDT;
  end
end
^


/* Trigger: WARES_TREB_BI_DISTR */
CREATE OR ALTER TRIGGER WARES_TREB_BI_DISTR FOR WARES_TREB
ACTIVE BEFORE INSERT POSITION 1000
AS
begin
  if (new.d$uuid is null) then
  begin
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
    new.d$srvupddt=''2000-01-01'';
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values (''WARES_TREB'',new.d$uuid,0,null) matching 
(TABLENAME,UUID);
  end
end
^


/* Trigger: WARES_TREB_BU_DISTR */
CREATE OR ALTER TRIGGER WARES_TREB_BU_DISTR FOR WARES_TREB
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 (''WARES_TREB'',new.d$uuid,1,null)
    matching (TABLENAME,UUID);
end
^


SET TERM ; ^



/******************************************************************************/

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



/******************************************************************************/
/***                                 Views                                  ***/
/******************************************************************************/


/* View: VW_WARES_TREB */
CREATE OR ALTER VIEW VW_WARES_TREB(
    ID,
    WARE_ID,
    NAME_ID,
    IZG_ID,
    COUNTRY_ID,
    ORIG_CODE,
    ORIG_NAME_ID,
    ORIG_IZG_ID,
    ORIG_COUNTRY_ID,
    Z_ID,
    SNAME,
    SIZG,
    SCOUNTRY,
    SORIG_NAME,
    SORIG_IZG,
    SORIG_COUNTRY,
    SKLAD_ID,
    PRICE,
    QUANT,
    SUMMA,
    SUMMA_O,
    PRICE_O,
    PRICE_Z,
    PRICE_R,
    BARCODE,
    BARCODE1,
    GODENDO,
    SERIA,
    NDS,
    SUM_NDSO,
    SERT,
    DATESERT,
    KEMVSERT,
    SDSERT,
    REGN,
    NGTD,
    EDIZM,
    INSERTDT,
    BCODE_IZG,
    DDA_UUID,
    D$UUID,
    D$SRVUPDDT,
    CMT_STATUS,
    CMT_DT,
    CMT_USER,
    CMT_PROFILE_ID,
    PART_TYPE,
    RQ_USER,
    RQ_PROFILE_ID,
    CMT_SNAME,
    CMT_SIZG,
    CMT_SCOUNTRY,
    CMT_BARCODE)
AS
select
wt.ID,
wt.WARE_ID,
wt.NAME_ID,
wt.IZG_ID,
wt.COUNTRY_ID,
wt.ORIG_CODE,
wt.ORIG_NAME_ID,
wt.ORIG_IZG_ID,
wt.ORIG_COUNTRY_ID,
wt.Z_ID,
wt.SNAME,
wt.SIZG,
wt.SCOUNTRY,
wt.SORIG_NAME,
wt.SORIG_IZG,
wt.SORIG_COUNTRY,
wt.SKLAD_ID,
wt.PRICE,
wt.QUANT,
wt.SUMMA,
wt.SUMMA_O,
wt.PRICE_O,
wt.PRICE_Z,
wt.PRICE_R,
wt.BARCODE,
wt.BARCODE1,
wt.GODENDO,
wt.SERIA,
wt.NDS,
wt.SUM_NDSO,
wt.SERT,
wt.DATESERT,
wt.KEMVSERT,
wt.SDSERT,
wt.REGN,
wt.NGTD,
wt.EDIZM,
wt.INSERTDT,
wt.BCODE_IZG,
wt.DDA_UUID,
wt.D$UUID,
wt.D$SRVUPDDT,
wt.CMT_STATUS,
wt.CMT_DT,
wt.CMT_USER,
wt.CMT_PROFILE_ID,
wt.PART_TYPE,
wt.RQ_USER,
wt.RQ_PROFILE_ID,
(select svalue from vals where id = 
w.name_id),
(select svalue from vals where id = w.izg_id),
(select svalue from vals where id = w.country_id),
w.barcode
from wares_treb 
wt
left join wares w on wt.ware_id = w.id
;


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

SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_AU0 FOR WARES
ACTIVE AFTER UPDATE POSITION 0
AS
begin
update warebase set updatedt=current_timestamp where ware_id=new.id;
  if (not(new.D$SRVUPDDT=old.D$SRVUPDDT)) then
    exit;
  if ((old.name_id<>new.name_id) or
     (old.izg_id<>new.izg_id) or
     (old.country_id<>new.country_id) or
--     (old.orig_code<>new.orig_code) or
     (old.orig_name_id<>new.orig_name_id) or
     (old.orig_izg_id<>new.orig_izg_id) or
     (old.orig_country_id<>new.orig_country_id) or
     (old.barcode<>new.barcode) or
--     (old.z_id<>new.z_id) or
     (old.sklad_id<>new.sklad_id)) then
  begin
    insert into wares_log
(
ID,
NAME_ID,
IZG_ID,
COUNTRY_ID,
ORIG_CODE,
ORIG_NAME_ID,
ORIG_IZG_ID,
ORIG_COUNTRY_ID,
BARCODE,
Z_ID,
SKLAD_ID,
INSERTDT,
PACKET
)
values
(
old.ID,
old.NAME_ID,
old.IZG_ID,
old.COUNTRY_ID,
old.ORIG_CODE,
old.ORIG_NAME_ID,
old.ORIG_IZG_ID,
old.ORIG_COUNTRY_ID,
old.BARCODE,
old.Z_ID,
old.SKLAD_ID,
old.INSERTDT,
old.PACKET
);
  end
end
^


SET TERM ; ^', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348176, 0, 0, 'cowa_2_recur', 'execute procedure PR_COWA_01;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
                  VALUES (348177, 0, 0, 'cowa_3_recur', 'ALTER TRIGGER WARES_LOG_BU_DISTR ACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);

COMMIT WORK;

Подготовка серверной базы и скриптов для Единого Окна — различия между версиями

Текущая версия на 15:05, 6 сентября 2016

Содержание

Экспорт vals_link (для ЙО: boss:d:\обмен\JO\ZTRADE.FDB) как dbf или insert

Создаем и вставляем данные vals_link в нашу БД (скрипт создания и импорта из dbf: cowa_vals_links.sql)

Выпоняем cowa_all.sql (не оптимизирован для ЙО)

Для связывания по ШК выполняем cowa_bcode_links.sql (не оптимизирован для ЙО)

Проверяем работу, смотрим сколько записей добавилось в g$distribute - могут добавится немного записей и только по таблицам wares и wares_log

Экспорт как insert скрипт в файл запроса "select * from wares_log wl where wl.actual_ware_id is not null", все строки "commit work" - убираем

Экспрорт как update or insert скрипт в файл запроса "select distinct w.* from TMP_UPDATED_WARES t left join wares w on t.uuid=w.d$uuid where w.d$uuid is not null", все строки "commit work" - убираем

Делаем шаблоны G$TASKS_TMPL по образцу. Заменить data в записях cowa_02XX на файл, созданный в 6м пункте, cowa_08 - на файл 7го пункта

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Действия

Поиск

Навигация

Инструкции

Отдел Внедрения

Отдел Менеджеров

Отдел Разработчиков

Отдел Системных Администраторов

Версии программ

Отчеты

Обновление функций и программ

Инструменты