Установка Единого Окна — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
 
(не показаны 43 промежуточные версии 2 участников)
Строка 1: Строка 1:
==Сжатие товаров в одиночной аптеки перед внедрением единого окна==
+
==Предварительная подготовка==
[[Сжатие товаров]]
+
1) Обновляем у всех клиентов Менеджер до версии не ниже ManagerXP2_273_5 от декабря 2016г.
 +
2) Обновляем клиента синхронизации до версии не ниже DistributeClient2.202 от декабря 2016г.
 +
3) Настраиваем двустороннюю синхронизацию таблиц WARES_TREB и G$PROFILES;
 +
4) Проверяем, что настроена двусторонняя синхронизация таблицы WARES_LOG;
 +
5) Проверяем, что у клиентов в PARAMS есть параметр CODE_PROFILE (Код профиля) и он корректный;
  
==Лешины скрипты==
+
==Подготовка серверной базы и скриптов для Единого Окна==
 +
1) Обновляем скрипты синхронизации на сервере;
 +
2) Блокируем синхронизацию (оставляем только g$tasks), для отправки обновлений клиентам;
 +
3) Готовим серверую БД [[Подготовка серверной БД для перевода на Единое Окно]];
 +
 
 +
==Останавливаем торговлю в точках==
 
<pre>
 
<pre>
 +
CREATE EXCEPTION EX_DONT_WORK 'Вход в программу заблокирован. Идет обновление...';
 +
SET TERM ^ ;
 +
CREATE OR ALTER TRIGGER SESSIONS_DONT_WORK_BI0 FOR SESSIONS
 +
ACTIVE BEFORE INSERT POSITION 0
 +
AS
 +
begin
 +
  exception EX_DONT_WORK; --Вход в программу заблокирован. Идет обновление...
 +
end
 +
^
 +
SET TERM ; ^
 +
INSERT INTO Z$SERVICE (ID, TASK, STATUS, INSERTDT, STARTDT, ENDDT, TXTLOG) VALUES (999, 'POLITE_RESTORE', 0, current_timestamp, current_timestamp, current_timestamp, NULL);
 +
update sessions s
 +
set s.enddt=current_timestamp, s.endflag=0, s.endsession_id=s.id
 +
where 1=1
 +
and s.prog containing 'manager'
 +
and s.endflag is null
 +
and cast(s.startdt as dm_date) >= dateadd(-3 day to current_date);
 +
</pre>
 +
 +
==Обновляем структуру БД клиентов==
 +
<pre>
 +
CREATE EXCEPTION EX_WARES_ID_NOT_FOUND 'Не найдена позиция (wares)';
 +
 +
/*******************************************************************/
 +
/*НОВЫЕ ПОЛЯ*/
 +
 +
ALTER TABLE WARES ADD G$PROFILE_ID DM_ID_NULL;
 +
ALTER TABLE WARES ADD WHASH DM_ID;
 +
ALTER TABLE WARES_LOG ADD ACTUAL_WARE_ID DM_UUID_NULL;
 +
ALTER TABLE PARTS ADD ORIG_SNAME DM_TEXT;
 +
ALTER TABLE PARTS ADD ORIG_SIZG DM_TEXT;
 +
ALTER TABLE PARTS ADD ORIG_SCOUNTRY DM_TEXT;
 +
ALTER TABLE PARTS ADD ORIG_BCODE_IZG DM_TEXT;
 +
ALTER TABLE DOC_DETAIL_ACTIVE ADD D$UUID DM_UUID_NULL;
 +
ALTER TABLE DOC_DETAIL_ACTIVE ADD D$SRVUPDDT DM_DATETIME;
 +
ALTER TABLE DOC_DETAIL_VIRTUAL ADD D$UUID DM_UUID;
 +
ALTER TABLE DOC_DETAIL_VIRTUAL ADD D$SRVUPDDT DM_DATETIME;
 +
 +
/*******************************************************************/
 +
/*ПРОЦЕДУРЫ*/
 +
 +
 +
 +
SET TERM ^ ;
 +
 
create or alter procedure PR_AUTO_WARESLINK (
 
create or alter procedure PR_AUTO_WARESLINK (
 
     DOC_ID DM_ID_NULL,
 
     DOC_ID DM_ID_NULL,
Строка 17: Строка 71:
 
declare variable DDA_NAME_ID DM_UUID_NULL;
 
declare variable DDA_NAME_ID DM_UUID_NULL;
 
declare variable DDA_Z_ID DM_UUID_NULL;
 
declare variable DDA_Z_ID DM_UUID_NULL;
declare variable DDA_SKLAD_ID DM_UUID_NULL;
+
declare variable DDA_SKLAD_ID DM_TEXT;
 
declare variable DDALOG_BARCODE DM_TEXT1024;
 
declare variable DDALOG_BARCODE DM_TEXT1024;
 
declare variable DDALOG_WARE_ID DM_UUID_NULL;
 
declare variable DDALOG_WARE_ID DM_UUID_NULL;
Строка 27: Строка 81:
 
declare variable DDALOG_NAME_ID DM_UUID_NULL;
 
declare variable DDALOG_NAME_ID DM_UUID_NULL;
 
declare variable DDALOG_Z_ID DM_UUID_NULL;
 
declare variable DDALOG_Z_ID DM_UUID_NULL;
declare variable DDALOG_SKLAD_ID DM_UUID_NULL;
+
declare variable DDALOG_SKLAD_ID DM_TEXT;
 
declare variable DDALOG2_BARCODE DM_TEXT1024;
 
declare variable DDALOG2_BARCODE DM_TEXT1024;
 
declare variable DDALOG2_WARE_ID DM_UUID_NULL;
 
declare variable DDALOG2_WARE_ID DM_UUID_NULL;
Строка 37: Строка 91:
 
declare variable DDALOG2_NAME_ID DM_UUID_NULL;
 
declare variable DDALOG2_NAME_ID DM_UUID_NULL;
 
declare variable DDALOG2_Z_ID DM_UUID_NULL;
 
declare variable DDALOG2_Z_ID DM_UUID_NULL;
declare variable DDALOG2_SKLAD_ID DM_UUID_NULL;
+
declare variable DDALOG2_SKLAD_ID DM_TEXT;
 +
declare variable DDALOG_SNAME DM_TEXT;
 +
declare variable DDALOG_SIZG DM_TEXT;
 +
declare variable DDALOG_SCOUNTRY DM_TEXT;
 +
declare variable DDA_SNAME DM_TEXT;
 +
declare variable DDA_SIZG DM_TEXT;
 +
declare variable DDA_SCOUNTRY DM_TEXT;
 
begin
 
begin
  
  select BCODE_IZG,
+
  select bcode_izg, ware_id, sname, sizg, scountry,
        WARE_ID,
+
  (select val_id from PR_GETVAL_ID(dda.sname, 0, dda.part_type)) as name_id,
        (select VAL_ID
+
  (select val_id from PR_GETVAL_ID(dda.sizg, 3, dda.part_type)) as izg_id,
        from PR_GETVAL_ID(DDA.SNAME, 0, DDA.PART_TYPE)) as NAME_ID,
+
  (select val_id from PR_GETVAL_ID(dda.scountry, 2, dda.part_type)) as country_id,
        (select VAL_ID
+
  (select val_id from PR_GETVAL_ID(dda.sorig_name, 1, dda.part_type)) as orig_name_id,
        from PR_GETVAL_ID(DDA.SIZG, 3, DDA.PART_TYPE)) as IZG_ID,
+
  (select val_id from PR_GETVAL_ID(dda.sorig_izg, 6, dda.part_type)) as orgi_izg_id,
        (select VAL_ID
+
  (select val_id from PR_GETVAL_ID(dda.sorig_country, 5, dda.part_type)) as orig_country_id,
        from PR_GETVAL_ID(DDA.SCOUNTRY, 2, DDA.PART_TYPE)) as COUNTRY_ID,
+
  Z_ID, SKLAD_ID
        (select VAL_ID
+
  from doc_detail_active dda where doc_id = :doc_id and id = :active_id
        from PR_GETVAL_ID(DDA.SORIG_NAME, 1, DDA.PART_TYPE)) as ORIG_NAME_ID,
+
  into dda_barcode, dda_ware_id, dda_sname, dda_sizg, dda_scountry, dda_name_id, dda_izg_id, dda_country_id, dda_orig_nameid,
        (select VAL_ID
+
       dda_orig_izgid, dda_orig_countryid, dda_z_id, dda_sklad_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,
+
  select first 1 bcode_izg, ware_id, sname, sizg, scountry,
                WARE_ID,
+
  (select val_id from PR_GETVAL_ID(ddal.sname, 0, ddal.part_type)) as name_id,
                (select VAL_ID
+
  (select val_id from PR_GETVAL_ID(ddal.sizg, 3, ddal.part_type)) as izg_id,
                from PR_GETVAL_ID(DDAL.SNAME, 0, DDAL.PART_TYPE)) as NAME_ID,
+
  (select val_id from PR_GETVAL_ID(ddal.scountry, 2, ddal.part_type)) as country_id,
                (select VAL_ID
+
  (select val_id from PR_GETVAL_ID(ddal.sorig_name, 1, ddal.part_type)) as orig_name_id,
                from PR_GETVAL_ID(DDAL.SIZG, 3, DDAL.PART_TYPE)) as IZG_ID,
+
  (select val_id from PR_GETVAL_ID(ddal.sorig_izg, 6, ddal.part_type)) as orgi_izg_id,
                (select VAL_ID
+
  (select val_id from PR_GETVAL_ID(ddal.sorig_country, 5, ddal.part_type)) as orig_country_id,
                from PR_GETVAL_ID(DDAL.SCOUNTRY, 2, DDAL.PART_TYPE)) as COUNTRY_ID,
+
  Z_ID, SKLAD_ID
                (select VAL_ID
+
  from doc_detail_active_log ddal where doc_id = :doc_id and id = :active_id order by log_id
                from PR_GETVAL_ID(DDAL.SORIG_NAME, 1, DDAL.PART_TYPE)) as ORIG_NAME_ID,
+
  into ddalog_barcode, ddalog_ware_id, ddalog_sname, ddalog_sizg, ddalog_scountry, ddalog_name_id, ddalog_izg_id, ddalog_country_id,
                (select VAL_ID
+
      ddalog_orig_nameid, ddalog_orig_izgid, ddalog_orig_countryid, ddalog_z_id, ddalog_sklad_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,
+
  if ( (ddalog_ware_id is null) or (ddalog_sname is null) or
                      WARE_ID,
+
      ((dda_sname = ddalog_sname) and (dda_sizg = ddalog_sizg) and (dda_scountry = ddalog_scountry) and (dda_barcode = ddalog_barcode)) )  then exit;
                      (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
+
  if ( (ddalog_ware_id = '0') or (ddalog_ware_id = '-1') ) then
    (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
 
  begin
  update WARES
+
  if (not exists(select id from wares_log where id = :dda_ware_id and name_id = :ddalog_name_id and izg_id = :ddalog_izg_id
  set NAME_ID = :DDA_NAME_ID,
+
      and country_id = :ddalog_country_id and barcode = :ddalog_barcode)) then
      IZG_ID = :DDA_IZG_ID,
+
      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)
      COUNTRY_ID = :DDA_COUNTRY_ID,
+
      values (:dda_ware_id, :ddalog_name_id, :ddalog_izg_id, :ddalog_country_id, :ddalog_orig_nameid, :ddalog_orig_izgid,
      ORIG_NAME_ID = :DDA_ORIG_NAMEID,
+
              :ddalog_orig_countryid, :ddalog_barcode, :ddalog_z_id, :ddalog_sklad_id);
      ORIG_IZG_ID = :DDA_ORIG_IZGID,
+
      ORIG_COUNTRY_ID = :DDA_ORIG_COUNTRYID,
+
      BARCODE = :DDA_BARCODE
+
  where ID = :DDALOG2_WARE_ID;
+
  exit;
+
 
  end
 
  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
+
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;
+
SET TERM ; ^
  
grant execute
+
/* Following GRANT statetements are generated automatically */
    on procedure PR_GETVAL_ID to procedure PR_AUTO_WARESLINK;
+
grant select on DOC_DETAIL_ACTIVE to procedure PR_AUTO_WARESLINK;
+
grant select on DOC_DETAIL_ACTIVE_LOG to procedure PR_AUTO_WARESLINK;
+
grant insert on WARES_LOG to procedure PR_AUTO_WARESLINK;
+
grant select,
+
            update on WARES to procedure PR_AUTO_WARESLINK;
+
grant execute
+
    on procedure PR_AUTO_WARESLINK to SYSDBA;
+
  
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)
+
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_AUTO_WARESLINK;
 +
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_AUTO_WARESLINK;
 +
GRANT SELECT ON DOC_DETAIL_ACTIVE_LOG TO PROCEDURE PR_AUTO_WARESLINK;
 +
GRANT SELECT,INSERT ON WARES_LOG TO PROCEDURE PR_AUTO_WARESLINK;
 +
 
 +
/* Existing privileges on this procedure */
 +
 
 +
GRANT EXECUTE ON PROCEDURE PR_AUTO_WARESLINK TO SYSDBA;
 +
 
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
create or alter procedure PR_NEW_PART (
 +
    DOC_ID type of DM_ID,
 +
    PARENT_ID type of DM_ID,
 +
    WARE_ID type of DM_UUID_NULL,
 +
    PRICE type of DM_DOUBLE,
 +
    PRICE_O type of DM_DOUBLE,
 +
    PRICE_Z type of DM_DOUBLE,
 +
    PRICE_R type of DM_DOUBLE,
 +
    QUANT type of DM_DOUBLE,
 +
    BARCODE type of DM_TEXT,
 +
    BARCODE1 type of DM_TEXT,
 +
    DEP type of DM_ID,
 +
    GODENDO type of DM_DATETIME,
 +
    SERIA type of DM_TEXT,
 +
    NDS type of DM_DOUBLE,
 +
    SUM_NDSO type of DM_DOUBLE,
 +
    SERT type of DM_TEXT,
 +
    DATESERT type of DM_DATETIME,
 +
    KEMVSERT type of DM_TEXT,
 +
    SDSERT type of DM_DATETIME,
 +
    REGN type of DM_TEXT,
 +
    NGTD type of DM_TEXT,
 +
    EDIZM type of DM_TEXT,
 +
    NAC type of DM_DOUBLE,
 +
    MOTHERPART_ID type of DM_ID,
 +
    PART_TYPE DM_STATUS,
 +
    BASE_AGENT_ID DM_ID,
 +
    SKLAD_ID type of DM_TEXT,
 +
    CONTRACT_ID DM_ID,
 +
    DOC_DETAIL_ACTIVE_ID DM_ID,
 +
    GROUP_ID DM_ID,
 +
    MOTHERPART_UUID DM_UUID_NULL = 0,
 +
    EGAIS_ID DM_TEXT1024 = null,
 +
    EGAIS_REGID DM_TEXT1024 = null,
 +
    EGAIS_BREGID DM_TEXT1024 = null,
 +
    EGAIS_ALCCODE DM_TEXT1024 = null,
 +
    EGAIS_QUANT DM_DOUBLE = 0,
 +
    CAPACITY DM_DOUBLE = 0,
 +
    ALC_VOLUME DM_DOUBLE = 0,
 +
    EGAIS_PRODUCER_ID DM_TEXT1024 = null,
 +
    EGAIS_BARCODE DM_TEXT1024 = null,
 +
    EGAIS_TYPE DM_TEXT = null,
 +
    PRODUCER_INN DM_TEXT1024 = null,
 +
    PRODUCER_KPP DM_TEXT1024 = null,
 +
    BOTTLINGDATE DM_DATETIME = null,
 +
    EGAIS_PRODUCT_VCODE DM_TEXT = null)
 +
returns (
 +
    P_ID type of DM_ID)
 
as
 
as
declare variable WARE_ID type of DM_UUID_NULL;
+
declare variable ORIG_SNAME DM_TEXT = 0;
declare variable NAME_ID type of DM_UUID_NULL;
+
declare variable ORIG_SIZG DM_TEXT = 0;
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
+
declare variable ORIG_SCOUNTRY DM_TEXT = 0;
declare variable IZG_ID type of DM_UUID_NULL;
+
declare variable ORIG_BCODE_IZG DM_TEXT;
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
 
begin
  
/*
+
   select iif(trim(coalesce(sorig_name, '')) = '', sname, sorig_name),
   select val_id from pr_getval_id(:sname,0,:alttype) into :name_id;
+
   iif(trim(coalesce(sorig_izg, '')) = '', sizg, sorig_izg),
  select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
+
   iif(trim(coalesce(sorig_country, '')) = '', scountry, sorig_country),
  select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id;
+
   bcode_izg
   select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
+
  from doc_detail_active where id = :doc_detail_active_id into :orig_sname, :orig_sizg, :orig_scountry, :orig_bcode_izg;
   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
+
  p_id=gen_id(gen_parts_id,1);
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)
+
  insert into parts
into :WARE_ID;
+
   
if (WARE_ID is not null) then
+
 
begin
+
(ID,parent_id,doc_id,WARE_ID,PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE,BARCODE1,DEP,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,
  /* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */
+
    DATESERT,KEMVSERT,SDSERT,REGN,NGTD,
  select SNAME,
+
    EDIZM,NAC,motherpart_id,sklad_id,part_type,BASE_AGENT_ID,contract_id, doc_detail_active_id, group_id, motherpart_uuid,
        SIZG,
+
    EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE, EGAIS_QUANT, CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID, EGAIS_BARCODE,
        SCOUNTRY,
+
    EGAIS_TYPE, PRODUCER_INN, PRODUCER_KPP, BOTTLINGDATE, EGAIS_PRODUCT_VCODE, ORIG_SNAME, ORIG_SIZG, ORIG_SCOUNTRY,
        BARCODE
+
 
  from VW_WARES
+
ORIG_BCODE_IZG)
   where ID = :WARE_ID
+
   values
  into :GOODNAME,
+
   
      :GOODIZG,
+
 
      :GOODCOUNTRY,
+
(:p_id,:parent_id,:doc_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,
      :GOODBARCODE;
+
  :SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,
  /* if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then */
+
    :SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id,:sklad_id,:part_type,:BASE_AGENT_ID,:contract_id,:doc_detail_active_id, :group_id, :motherpart_uuid,
  /* insert into doc_detail_active_log(id,sname,sizg,scountry,part_type) values (:active_id,:sname,:sizg,:scountry,:alttype); */
+
    :EGAIS_ID, :EGAIS_BREGID, :EGAIS_REGID, :EGAIS_ALCCODE, :EGAIS_QUANT, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID,
  /* goodname='2';--sname; */
+
 
  if (GOODNAME is null) then
+
:EGAIS_BARCODE,
  GOODNAME = SNAME;
+
    :EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE, :ORIG_SNAME, :ORIG_SIZG, :ORIG_SCOUNTRY,
  if (GOODIZG is null) then
+
 
  GOODIZG = SIZG;
+
:ORIG_BCODE_IZG);
  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;
 
   suspend;
  exit;
+
end^
end
+
  
select WARE_ID
+
SET TERM ; ^
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;
+
  
 +
/* Following GRANT statetements are generated automatically */
 +
 +
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_NEW_PART;
 +
GRANT INSERT ON PARTS TO PROCEDURE PR_NEW_PART;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
 +
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO "PUBLIC";
 +
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO STANDART;
 +
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO SYSDBA;
 +
 +
 +
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
 +
begin
 +
  if (ORIG_CODE is null) then
 +
    ORIG_CODE = '';
 +
  if (SEARCHINLOGS is null) then
 +
    SEARCHINLOGS = 0;
 +
  if (SEARCHINLOGS = 1) then
 +
  begin
 +
--    select iif(max(actual_ware_id) is null, max(id), 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 :WARE_ID;
 +
    select first 1 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;
 +
    if (WARE_ID is not null) then
 +
      WARE_ID=coalesce((select first 1 actual_ware_id from WARES_LOG where id=:WARE_ID and actual_ware_id is not null order by log_insertdt desc),ware_id);
 +
    if (WARE_ID is not null) then
 +
        select id from wares where id = :WARE_ID 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;
 
   suspend;
  exit;
+
end^
end
+
  
WARE_ID = null;
+
SET TERM ; ^
if (ORIG_NAME_ID is not null) then
+
begin
+
  select first 1 ID
+
  from WARES
+
  where ORIG_NAME_ID = :ORIG_NAME_ID
+
  order by ID 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_ID 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_ID 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;
+
/* Following GRANT statetements are generated automatically */
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_ID 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_ID 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;
+
GRANT SELECT ON WARES_LOG TO PROCEDURE PR_GETWARE_BY_IDS;
if (ORIG_COUNTRY_ID is not null) then
+
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GETWARE_BY_IDS;
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_ID 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_ID 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 */
+
/* Existing privileges on this procedure */
/*  insert into doc_detail_active_log(id,sname,sizg,scountry, part_type) values (:active_id,:sname,:sizg,:scountry,:alttype); */
+
  
/* goodname='3'; */
+
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MAKEGOODWAREVALUES;
GOODBARCODE = BARCODE;
+
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_UPDATEWARE;
 +
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_UPDATEWARE4PART;
 +
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO STANDART;
 +
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO SYSDBA;
  
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;
+
SET TERM ^ ;
  
 
create or alter procedure PR_GET_WARE (
 
create or alter procedure PR_GET_WARE (
Строка 477: Строка 355:
 
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
 
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
 
begin
 
begin
 +
  if (Z_ID is null) then Z_ID = 0;
 +
  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 VAL_ID
+
  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
from PR_GETVAL_ID(:SNAME, 0, :ALTTYPE, :MNN)
+
    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 :W_ID;
into :NAME_ID;
+
  else
select VAL_ID
+
    select first 1 ID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID into :W_ID;
from PR_GETVAL_ID(:SIZG, 3, :ALTTYPE)
+
-- select id from wares where whash = (select whash from PR_GET_WARES_HASH(:NAME_ID,:IZG_ID,:COUNTRY_ID,:BARCODE,:orig_code)) into :W_ID;
into :IZG_ID;
+
  if (W_ID is null) then
  select VAL_ID
+
  begin
from PR_GETVAL_ID(:SCOUNTRY, 2, :ALTTYPE)
+
    --    exception EX_WRONG_OPER;
into :COUNTRY_ID;
+
    --w_id=gen_id(gen_wares_id,1);
select VAL_ID
+
    W_ID = uuid_to_char(gen_uuid());
from PR_GETVAL_ID(:SORIG_NAME, 1, :ALTTYPE)
+
    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)
into :ORIG_NAME_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);
select VAL_ID
+
   end
from PR_GETVAL_ID(:SORIG_IZG, 6, :ALTTYPE)
+
   suspend;
into :ORIG_IZG_ID;
+
end^
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
+
SET TERM ; ^
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 (
+
/* Following GRANT statetements are generated automatically */
 +
 
 +
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_GET_WARE;
 +
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GET_WARE;
 +
 
 +
/* Existing privileges on this procedure */
 +
 
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO "PUBLIC";
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO STANDART;
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO SYSDBA;
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
create or alter procedure PR_MGNLINK (
 +
    VTYPE type of DM_STATUS,
 +
    RIGHT_ID type of DM_UUID_NULL,
 +
    WRONG_ID type of DM_UUID_NULL,
 +
    WITHBLOCKS type of DM_STATUS)
 +
as
 +
declare variable NAME_ID type of DM_UUID_NULL;
 +
declare variable COUNTRY_ID type of DM_UUID_NULL;
 +
declare variable IZG_ID type of DM_UUID_NULL;
 +
declare variable W_NAME_ID type of DM_UUID_NULL;
 +
declare variable W_COUNTRY_ID type of DM_UUID_NULL;
 +
declare variable W_IZG_ID type of DM_UUID_NULL;
 +
declare variable W_ORIG_CODE DM_TEXT;
 +
declare variable W_ORIG_NAME_ID type of DM_UUID_NULL;
 +
declare variable W_ORIG_COUNTRY_ID type of DM_UUID_NULL;
 +
declare variable W_ORIG_IZG_ID type of DM_UUID_NULL;
 +
declare variable ID DM_UUID_NULL;
 +
declare variable TNAME_ID type of DM_UUID_NULL;
 +
declare variable CHECK_ID DM_UUID_NULL;
 +
declare variable BARCODE DM_TEXT;
 +
begin
 +
select NAME_ID, COUNTRY_ID, IZG_ID from WARES where ID = :RIGHT_ID into :NAME_ID, :COUNTRY_ID, :IZG_ID;
 +
select NAME_ID, COUNTRY_ID, IZG_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_COUNTRY_ID, ORIG_IZG_ID, BARCODE from WARES where ID = :WRONG_ID
 +
into :W_NAME_ID, :W_COUNTRY_ID, :W_IZG_ID, :W_ORIG_CODE, :W_ORIG_NAME_ID, :W_ORIG_COUNTRY_ID, :W_ORIG_IZG_ID, :BARCODE;
 +
if ((NAME_ID is null) or (COUNTRY_ID is null) or (IZG_ID is null)) then
 +
exception EX_WARES_ID_NOT_FOUND;
 +
--  execute procedure pr_debug_wrongvals(:dbgsname,:name_id);
 +
--  execute procedure pr_debug_wrongvals(:dbgsizg,:izg_id);
 +
--  execute procedure pr_debug_wrongvals(:dbgscountry,:country_id);
 +
CHECK_ID = '0';
 +
if (vtype = 0) then
 +
begin
 +
select ware_id from PR_GETWARE_BY_IDS(:name_id, :w_izg_id, :w_country_id, :w_orig_code, :w_orig_name_id, :w_orig_izg_id, :w_orig_country_id, 0, 0,
 +
 
 +
:BARCODE)
 +
into :CHECK_ID;
 +
end
 +
else if (vtype = 2) then
 +
begin
 +
select ware_id from PR_GETWARE_BY_IDS(:w_name_id, :w_izg_id, :country_id, :w_orig_code, :w_orig_name_id, :w_orig_izg_id, :w_orig_country_id, 0, 0,
 +
 
 +
:BARCODE)
 +
into :CHECK_ID;
 +
end
 +
else if (vtype = 3) then
 +
begin
 +
select ware_id from PR_GETWARE_BY_IDS(:w_name_id, :izg_id, :w_country_id, :w_orig_code, :w_orig_name_id, :w_orig_izg_id, :w_orig_country_id, 0, 0,
 +
 
 +
:BARCODE)
 +
into :CHECK_ID;
 +
end
 +
if (CHECK_ID <> '0') then
 +
begin
 +
update WARES set ORIG_CODE = substring(id || '@' || orig_code from 1 for 250)  where ID = :WRONG_ID;
 +
--update PARTS set WARE_ID = :right_id where WARE_ID = :WRONG_ID; --version 2: updating parts, but no actual link is created
 +
--exit;
 +
end
 +
if (VTYPE = 0) then
 +
begin
 +
select ID, NAME_ID from WARES where ID = :WRONG_ID into :ID, :TNAME_ID;
 +
if (WITHBLOCKS = 1) then
 +
begin
 +
update WARES set NAME_ID = :NAME_ID where ID = :ID or NAME_ID = :TNAME_ID;
 +
end
 +
else
 +
begin
 +
update WARES set NAME_ID = :NAME_ID where ID = :WRONG_ID;
 +
end
 +
end
 +
else
 +
if (VTYPE = 2) then
 +
begin
 +
--    select svalue from vals where id=:country_id into :tmp;
 +
--    if (tmp is null) then exception ex_svalue_not_found;
 +
update WARES set COUNTRY_ID = :COUNTRY_ID where ID = :WRONG_ID;
 +
--    update totalpricelist set country_id=:country_id, scountry=:tmp where system_id=:wrong_id;
 +
end
 +
else
 +
if (VTYPE = 3) then
 +
begin
 +
--    select svalue from vals where id=:izg_id into :tmp;
 +
--    if (tmp is null) then exception ex_svalue_not_found;
 +
update WARES set IZG_ID = :IZG_ID where ID = :WRONG_ID;
 +
--    update totalpricelist set izg_id=:izg_id, sizg=:tmp where system_id=:wrong_id;
 +
end
 +
else
 +
exception EX_UNKNOWN_MGNTYPE;
 +
for select PART_ID from WAREBASE where WARE_ID in (:WRONG_ID, :RIGHT_ID) into :ID
 +
do
 +
execute procedure PR_UPDBLOCKINFO(:ID);
 +
end^
 +
 
 +
SET TERM ; ^
 +
 
 +
/* Following GRANT statetements are generated automatically */
 +
 
 +
GRANT SELECT,UPDATE ON WARES TO PROCEDURE PR_MGNLINK;
 +
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MGNLINK;
 +
GRANT SELECT ON WAREBASE TO PROCEDURE PR_MGNLINK;
 +
GRANT EXECUTE ON PROCEDURE PR_UPDBLOCKINFO TO PROCEDURE PR_MGNLINK;
 +
 
 +
/* Existing privileges on this procedure */
 +
 
 +
GRANT EXECUTE ON PROCEDURE PR_MGNLINK TO STANDART;
 +
GRANT EXECUTE ON PROCEDURE PR_MGNLINK TO SYSDBA;
 +
 
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
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 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^
 +
 
 +
SET TERM ; ^
 +
 
 +
/* Following GRANT statetements are generated automatically */
 +
 
 +
GRANT SELECT ON VALS TO PROCEDURE PR_MAKEGOODWAREVALUES;
 +
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MAKEGOODWAREVALUES;
 +
GRANT SELECT ON VW_WARES TO PROCEDURE PR_MAKEGOODWAREVALUES;
 +
GRANT SELECT ON WARES TO PROCEDURE PR_MAKEGOODWAREVALUES;
 +
GRANT SELECT ON WARES_LOG TO PROCEDURE PR_MAKEGOODWAREVALUES;
 +
 
 +
/* Existing privileges on this procedure */
 +
 
 +
GRANT EXECUTE ON PROCEDURE PR_MAKEGOODWAREVALUES TO STANDART;
 +
GRANT EXECUTE ON PROCEDURE PR_MAKEGOODWAREVALUES TO SYSDBA;
 +
 
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
create or alter procedure PR_GET_WARES_HASH (
 
     NAME_ID type of DM_UUID_NULL,
 
     NAME_ID type of DM_UUID_NULL,
 
     IZG_ID type of DM_UUID_NULL,
 
     IZG_ID type of DM_UUID_NULL,
 
     COUNTRY_ID type of DM_UUID_NULL,
 
     COUNTRY_ID type of DM_UUID_NULL,
     ORIG_CODE type of DM_TEXT,
+
     BARCODE type of DM_TEXT,
     ORIG_NAME_ID type of DM_UUID_NULL,
+
     ORIG_CODE type of DM_TEXT)
    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 (
 
returns (
     WARE_ID type of DM_UUID_NULL)
+
     WHASH DM_ID)
 
as
 
as
 
begin
 
begin
if (ORIG_CODE is null) then
+
   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
   ORIG_CODE = '';
+
  whash = hash(coalesce(:NAME_ID,'NAME_ID')||coalesce(:IZG_ID,'IZG_ID')||coalesce(:COUNTRY_ID,'COUNTRY_ID')||coalesce(:BARCODE, '')||coalesce
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;
+
  select ID
+
  from WARES
+
  where ID = :WARE_ID
+
  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 (
+
(:ORIG_CODE, ''));
 +
  else
 +
  whash = hash(coalesce(:NAME_ID,'NAME_ID')||coalesce(:IZG_ID,'IZG_ID')||coalesce(:COUNTRY_ID,'COUNTRY_ID')||coalesce(:ORIG_CODE, ''));
 +
  suspend;
 +
end^
 +
 
 +
SET TERM ; ^
 +
 
 +
/* Existing privileges on this procedure */
 +
 
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARES_HASH TO PROCEDURE PR_GETWARE_BY_IDS;
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARES_HASH TO PROCEDURE PR_GET_WARE;
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARES_HASH TO SYSDBA;
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
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,
 
     DOC_ID type of DM_ID,
     SESSION_ID type of DM_ID)
+
     COMMITDATE type of DM_DATETIME)
 
as
 
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
 
begin
select DT.BASE_TYPE,
+
/*
        D.PRICE_TYPE,
+
  эта процедура выполняется для каждой записи, добавляемой в DOC_DETAIL в процедурах проведения документов
        D.CONTRACT_ID
+
*/
from DOCS D
+
  /* Для вречей*/
left join DOC_TYPES DT on D.DOC_TYPE = DT.ID
+
  update doc_detail_doctor set doc_detail_id=:commit_id, doc_id=:doc_id, commitdate=:commitdate where DOC_DETAIL_ACTIVE_ID=:active_id;
where D.ID = :DOC_ID
+
  /* Старые рецепты*/
into :BASE_TYPE,
+
  update DOC_DETAIL_MAKE set doc_detail_id=:commit_id, doc_id=:doc_id, commitdate=:commitdate where DOC_DETAIL_ACTIVE_ID=:active_id;
      :PRICE_TYPE,
+
  /* Новые рецепты*/
      :CONTRACT_ID;
+
  update recepts set table_id=:commit_id, table_name='DOC_DETAIL', status=1 where doc_detail_active_id=:active_id; --для рецептов
  if (BASE_TYPE in (1, 3, 6, 8)) then
+
begin
+
  
   WRONG_WARES = 0;
+
execute procedure PR_AUTO_WARESLINK(:DOC_ID, :ACTIVE_ID);
   WARES_MODE = 1;
+
 
   select PARAM_VALUE
+
end^
   from PR_GETPARAMVALUE('WARES_MODE', 0)
+
 
   into WARES_MODE;
+
SET TERM ; ^
   if (WARES_MODE = 0) then
+
 
 +
 
 +
 
 +
 
 +
 
 +
/*******************************************************************/
 +
/*ТРИГГЕРЫ*/
 +
 
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
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
 +
   if (new.orig_code is null) then
 +
    new.orig_code='';
 +
   if (new.barcode is null) then
 +
    new.barcode='';
 +
   select first 1 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 orig_code=new.orig_code
 +
    and  d$uuid<>new.d$uuid into :cur_id;
 +
   if (cur_id is null) then exit;
 +
  new.orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
 +
end
 +
^
 +
 
 +
 
 +
SET TERM ; ^
 +
 
 +
SET SQL DIALECT 3;
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER WARES_BI_DISTR FOR WARES
 +
ACTIVE BEFORE INSERT POSITION 0
 +
AS
 +
begin
 +
   new.insertdt=current_timestamp;
 +
  new.PACKET = gen_id(gen_WARES_PACKET,1);
 +
   if (new.orig_code is null) then
 +
    new.orig_code='';
 +
  if (new.id is null) then
 
   begin
 
   begin
  select count(ID)
+
    new.id=UUID_TO_CHAR(GEN_UUID());
  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
 
   end
 +
  new.d$uuid=new.id;
 +
  if (new.d$srvupddt is null) then
 +
  begin
  
  execute procedure PR_DOC_PRIHOD_COMMIT(:DOC_ID, 0);
+
    if ((select param_value from params where param_id = 'WARES_ALLOW_ADD') = '1') then --Режим создания нового товара (1-запрещено)
end
+
       exception EX_WARES_ID_NOT_FOUND (select svalue from vals where id = new.name_id);
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
+
    new.g$profile_id = cast((select param_value from params where param_id = 'CODE_PROFILE') as dm_id);
active before insert position 0
+
    new.d$srvupddt='2000-01-01';
as
+
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('WARES',new.d$uuid,0,null) matching (TABLENAME,UUID);
 +
  end
 +
  if (new.l_id is null) then
 +
    new.l_id=GEN_ID(GEN_WARES_ID,1);
 +
end
 +
^
 +
 
 +
 
 +
SET TERM ; ^
 +
 
 +
 
 +
 
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER WARES_SNAME_BI0 FOR WARES
 +
ACTIVE BEFORE INSERT POSITION 0
 +
AS
 
begin
 
begin
if (new.ID is null) then
+
new.sname = (select svalue from vals where id = new.name_id);
  new.ID = gen_id(GEN_DOC_DETAIL_ACTIVE_ID, 1);
+
end
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,
+
SET TERM ; ^
                                    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
+
SET SQL DIALECT 3;
   /* >cf20110923 */
+
 
   /* if (new.ware_id<0) then */
+
 
   if (new.WARE_ID = '-1') then
+
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
 +
  if (new.orig_code is null) then
 +
    new.orig_code='';
 +
  if (new.barcode is null) then
 +
    new.barcode='';
 +
   select first 1 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 orig_code=new.orig_code and d
 +
 
 +
$uuid<>new.d$uuid into :cur_id;
 +
   if (cur_id is null) then exit;
 +
  new.orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
 +
end
 +
^
 +
 
 +
 
 +
SET TERM ; ^
 +
 
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER WARES_BU0 FOR WARES
 +
ACTIVE BEFORE UPDATE POSITION 0
 +
AS
 +
begin
 +
   if (new.orig_code is null) then
 +
    new.orig_code='';
 +
  if (new.name_id <> old.name_id) then
 
   begin
 
   begin
  select GOODNAME,
+
    new.mgn_name = null;
          GOODIZG,
+
    new.mgn_id = 0;
          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
 
   end
  /* <cf20110923 */
+
end
  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
+
SET TERM ; ^
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
 
  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,
+
SET SQL DIALECT 3;
      new.DATESERT,
+
 
      new.KEMVSERT,
+
 
      new.SDSERT,
+
SET TERM ^ ;
      new.REGN,
+
 
      new.NGTD,
+
 
      new.EDIZM,
+
 
      new.NAC,
+
CREATE OR ALTER TRIGGER WARES_BU_MGN FOR WARES
      new.DEP,
+
ACTIVE BEFORE UPDATE POSITION 0
      new.PART_TYPE,
+
AS
      new.BASE_AGENT_ID,
+
      new.GROUP_ID;
+
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
 
  begin
   select WW.NAME_ID,
+
   if (new.name_id <> old.name_id) then
        (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
 
   begin
  select GOODNAME,
+
    new.mgn_name = null;
          GOODIZG,
+
    new.mgn_id = 0;
          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
 +
  new.sname = (select svalue from vals where id = new.name_id);
 
  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
+
if (new.SUM_NDSO is null) then
+
  new.SUM_NDSO = new.SUMMA_O * new.NDS / 100;
+
if (((new.NAC is null) or (new.NAC = 0)) and
+
    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;
+
/* -> 20130301-ann gen_barcode_by_name */
+
if ((new.BARCODE is null) or (trim(new.BARCODE) = '')) 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(new.name_id,'88') 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
 
  where ID = new.PART_PARENT_ID;
 
  execute procedure PR_COPY_MEMBERSHIP('DOC_DETAIL_ACTIVE', new.PART_PARENT_ID, new.ID);
 
end
 
end;
 
  
create or alter trigger DOC_DETAIL_ACTIVE_BU0 for DOC_DETAIL_ACTIVE
+
SET TERM ; ^
active before update position 0
+
 
as
+
 
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER WARES_AU_DDA FOR WARES
 +
ACTIVE AFTER UPDATE POSITION 0
 +
AS
 +
declare variable w_sname DM_TEXT;
 +
declare variable w_sizg DM_TEXT;
 +
declare variable w_scountry DM_TEXT;
 
begin
 
begin
/*20160513
+
  if ( (new.id = old.id) and
    if ((new.SNAME <> old.SNAME) or (new.NAME_ID is null) or (new.NAME_ID = '0')) then
+
  ((new.name_id <> old.name_id) or (new.izg_id <> old.izg_id)
      select VAL_ID from PR_GETVAL_ID(new.SNAME, 0, new.PART_TYPE, new.MNN) into new.NAME_ID;
+
  or (new.country_id <> old.country_id) or (new.barcode <> old.barcode)) ) then
    */
+
  begin
  
if (new.NAME_ID is null) then
+
    select svalue from vals where id = new.name_id into :w_sname;
  new.NAME_ID = 0;
+
    select svalue from vals where id = new.izg_id into :w_sizg;
if ((new.SUMMA is null) and
+
    select svalue from vals where id = new.country_id into :w_scountry;
    ((new.QUANT <> 0) and
+
 
    (new.PRICE <> 0))) then
+
    update doc_detail_active set sname = :w_sname, sizg = :w_sizg, scountry = :w_scountry,
begin
+
    bcode_izg = new.barcode, name_id = new.name_id, izg_id = new.izg_id, country_id = new.country_id
  new.SUMMA = new.QUANT * new.PRICE;
+
    where ware_id=new.id;
  if (new.SUM_DSC is not null) then
+
 
  new.SUMMA = new.SUMMA - new.SUM_DSC;
+
    update doc_detail_virtual set sname = :w_sname, sizg = :w_sizg, scountry = :w_scountry,
end
+
    bcode_izg = new.barcode, name_id = new.name_id, izg_id = new.izg_id, country_id = new.country_id
if (((new.SUMMA_O = 0) or (new.SUMMA_O is null)) and
+
    where ware_id=new.id;
    ((new.QUANT <> 0) and
+
 
    (new.PRICE_O <> 0))) then
+
    update doc_detail_active_treb set sname = :w_sname, sizg = :w_sizg, scountry = :w_scountry,
  new.SUMMA_O = new.QUANT * new.PRICE_O;
+
    bcode_izg = new.barcode, name_id = new.name_id, izg_id = new.izg_id, country_id = new.country_id
if (new.NAME_ID <> old.NAME_ID) then
+
    where ware_id=new.id;
   execute procedure PR_COPYGROUPS(old.NAME_ID, new.NAME_ID);
+
 
  if (new.SUM_DSC is null) then
+
   end
   if (new.SUMMA <> old.SUMMA) then
+
end
 +
^
 +
 
 +
 
 +
SET TERM ; ^
 +
 
 +
 
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER VALS_BD0 FOR VALS
 +
ACTIVE BEFORE DELETE POSITION 0
 +
AS
 +
begin
 +
  exception ex_wrong_oper 'попытка удаления наименования! ' || old.id;
 +
end
 +
^
 +
 
 +
 
 +
SET TERM ; ^
 +
 
 +
 
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BI_DISTR FOR DOC_DETAIL_ACTIVE
 +
ACTIVE BEFORE INSERT POSITION 0
 +
AS
 +
begin
 +
   if (new.d$uuid is null) then
 
   begin
 
   begin
  new.SUM_DSC = (1 + new.DISCOUNT / 100) * 100 * new.SUMMA / (100 + old.DISCOUNT) - 100 * new.SUMMA / (100 + old.DISCOUNT);
+
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
  --new.summa=(1+new.discount/100)*100*old.summa/(100+old.discount);
+
    new.d$srvupddt='2000-01-01';
 
   end
 
   end
  if (new.SUM_DSC is null) then
+
end
   if (new.DISCOUNT <> old.DISCOUNT) then
+
^
 +
 
 +
 
 +
SET TERM ; ^
 +
 
 +
 
 +
 
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER WARES_HASH_BUI0 FOR WARES
 +
ACTIVE BEFORE INSERT OR UPDATE POSITION 1900
 +
AS
 +
begin
 +
  select whash from PR_GET_WARES_HASH(new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.BARCODE,new.orig_code) into new.whash;
 +
end
 +
^
 +
 
 +
 
 +
SET TERM ; ^
 +
 
 +
 
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BU_XL FOR DOC_DETAIL_ACTIVE
 +
ACTIVE BEFORE UPDATE POSITION 999
 +
AS
 +
begin
 +
   if ( (new.part_id = 0) and ((new.sname <> old.sname) or (new.sizg <> old.sizg) or (new.scountry <> old.scountry) or (new.bcode_izg <> old.bcode_izg)) )  then
 
   begin
 
   begin
  new.SUM_DSC = (1 + new.DISCOUNT / 100) * 100 * old.SUMMA / (100 + old.DISCOUNT) - 100 * old.SUMMA / (100 + old.DISCOUNT);
+
      new.WARE_ID = '0';
  new.SUMMA = (1 + new.DISCOUNT / 100) * 100 * old.SUMMA / (100 + old.DISCOUNT);
+
 
 +
      select first 1 ID from WARES where
 +
      NAME_ID = (select ID from VALS where VTYPE = 0 and ALTTYPE = new.PART_TYPE and SVALUE = new.SNAME) and
 +
      IZG_ID = (select ID from VALS where VTYPE = 3 and ALTTYPE = new.PART_TYPE and SVALUE = new.SIZG) and
 +
      COUNTRY_ID = (select ID from VALS where VTYPE = 2 and ALTTYPE = new.PART_TYPE and SVALUE = new.SCOUNTRY) and
 +
      BARCODE = new.BCODE_IZG
 +
      into new.WARE_ID;
 
   end
 
   end
 +
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;
 
  
insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
+
SET TERM ; ^
                                    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 view VW_WARES (
+
 
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER PARTS_BU0 FOR PARTS
 +
ACTIVE BEFORE UPDATE POSITION 0
 +
AS
 +
begin
 +
 
 +
  if (new.ware_id <> old.ware_id) then
 +
  begin
 +
    insert into parts_log (
 +
ID,
 +
PARENT_ID,
 +
DOC_ID,
 +
WARE_ID,
 +
PRICE,
 +
PRICE_O,
 +
PRICE_Z,
 +
PRICE_R,
 +
QUANT,
 +
BARCODE,
 +
BARCODE1,
 +
DEP,
 +
GODENDO,
 +
SERIA,
 +
NDS,
 +
SUM_NDSO,
 +
SERT,
 +
DATESERT,
 +
KEMVSERT,
 +
SDSERT,
 +
REGN,
 +
NGTD,
 +
EDIZM,
 +
INSERTDT,
 +
NAC,
 +
MOTHERPART_ID,
 +
ENDDT,
 +
SESSION_ID,
 +
PACKET,
 +
sklad_id,
 +
part_type,
 +
base_agent_id,
 +
contract_id,
 +
DOC_DETAIL_ACTIVE_ID,
 +
vpart_id
 +
)
 +
values(
 +
old.ID,
 +
old.PARENT_ID,
 +
old.DOC_ID,
 +
old.WARE_ID,
 +
old.PRICE,
 +
old.PRICE_O,
 +
old.PRICE_Z,
 +
old.PRICE_R,
 +
old.QUANT,
 +
old.BARCODE,
 +
old.BARCODE1,
 +
old.DEP,
 +
old.GODENDO,
 +
old.SERIA,
 +
old.NDS,
 +
old.SUM_NDSO,
 +
old.SERT,
 +
old.DATESERT,
 +
old.KEMVSERT,
 +
old.SDSERT,
 +
old.REGN,
 +
old.NGTD,
 +
old.EDIZM,
 +
old.INSERTDT,
 +
old.NAC,
 +
old.MOTHERPART_ID,
 +
old.ENDDT,
 +
old.SESSION_ID,
 +
old.PACKET,
 +
old.sklad_id,
 +
old.part_type,
 +
old.base_agent_id,
 +
old.contract_id,
 +
old.doc_detail_active_id,
 +
old.vpart_id
 +
);
 +
  end
 +
end
 +
^
 +
 
 +
 
 +
SET TERM ; ^
 +
 
 +
ALTER TRIGGER WARES_LOG_AI0 INACTIVE;
 +
 
 +
 
 +
 
 +
/*******************************************************************/
 +
/*ПРЕДСТАВЛЕНИЯ*/
 +
 
 +
 
 +
 
 +
/******************************************************************************/
 +
 
 +
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                                Views                                  ***/
 +
/******************************************************************************/
 +
 
 +
 
 +
/* View: VW_WARES */
 +
CREATE OR ALTER VIEW VW_WARES(
 
     ID,
 
     ID,
 
     NAME_ID,
 
     NAME_ID,
Строка 1147: Строка 1187:
 
     MGN_ID,
 
     MGN_ID,
 
     MGN_SOURCE)
 
     MGN_SOURCE)
as
+
AS
select W.ID,
+
select
      W.NAME_ID,
+
w.ID,
      W.SNAME, --(select svalue from vals where id = w.name_id),
+
w.NAME_ID,
      W.IZG_ID,
+
w.sname,
      (select SVALUE
+
w.IZG_ID,
        from VALS
+
(select svalue from vals where id = w.izg_id),
        where ID = W.IZG_ID),
+
w.COUNTRY_ID,
      W.COUNTRY_ID,
+
(select svalue from vals where id = w.country_id),
      (select SVALUE
+
w.ORIG_CODE,
        from VALS
+
w.ORIG_NAME_ID,
        where ID = W.COUNTRY_ID),
+
(select svalue from vals where id = w.orig_name_id),
      W.ORIG_CODE,
+
w.ORIG_IZG_ID,
      W.ORIG_NAME_ID,
+
(select svalue from vals where id = w.orig_izg_id),
      (select SVALUE
+
w.ORIG_COUNTRY_ID,
        from VALS
+
(select svalue from vals where id = w.orig_country_id),
        where ID = W.ORIG_NAME_ID),
+
w.BARCODE,
      W.ORIG_IZG_ID,
+
w.Z_ID,
      (select SVALUE
+
w.SKLAD_ID,
        from VALS
+
w.INSERTDT,
        where ID = W.ORIG_IZG_ID),
+
w.PACKET,
      W.ORIG_COUNTRY_ID,
+
w.mgn_name,
      (select SVALUE
+
w.mgn_id,
        from VALS
+
w.mgn_source
        where ID = W.ORIG_COUNTRY_ID),
+
from wares w order by sname, BARCODE
      W.BARCODE,
+
;
      W.Z_ID,
+
      W.SKLAD_ID,
+
      W.INSERTDT,
+
      W.PACKET,
+
      W.MGN_NAME,
+
      W.MGN_ID,
+
      W.MGN_SOURCE
+
from WARES W;
+
  
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;
 
</pre>
 
  
==Славины скрипты==
+
/******************************************************************************/
<pre>
+
/***                              Privileges                              ***/
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; ^
+
/* Privileges of users */
 +
GRANT ALL ON VW_WARES TO STANDART WITH GRANT OPTION;
  
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;
+
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 +
SET SQL DIALECT 3;
  
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 ^ ;
+
/***                                Views                                  ***/
 +
/******************************************************************************/
  
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;
+
/* 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,
 +
    RQ_SPROFILE_ID)
 +
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,
 +
(select caption from g$profiles where id = wt.RQ_PROFILE_ID)
 +
from wares_treb wt
 +
left join wares w on wt.ware_id = w.id
 +
;
  
set term ^;
+
</pre>
  
create or alter trigger WARES_BIU_COWA for WARES
+
==Триггер DOC_DETAIL_ACTIVE_BI==
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
+
  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;
+
delete from WARES
+
where D$UUID = :CUR_ID;
+
end^
+
  
set term;
+
после блока
^
+
<pre>
 +
if (new.parent_id is null) then new.parent_id=0;
 +
if (new.part_type is null) then new.part_type=0;
 +
</pre>
  
set term ^;
+
заменяем код, на следующий:
 +
<pre>
 +
  --первая строчка =  до привидения, оригианльные значения накладной
 +
  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;
  
create or alter trigger PARTS_BIU_COWA for PARTS
+
  insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
active before insert or update position 0
+
                                    DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE,
as
+
                                    BARCODE1, BCODE_IZG,
declare variable WARE_ID type of DM_UUID;
+
                                    GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM,
begin
+
                                    NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID,
if (exists(select ID
+
                                    Z_ID, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY,
            from WARES
+
                                    INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW,
            where ID = new.WARE_ID)) then
+
                                    PART_TYPE, BASE_AGENT_ID)
   exit;
+
   values (new.ID, new.PARENT_ID, new.DOC_ID, new.PART_ID, new.PART_PARENT_ID, new.DOC_DETAIL_ID, new.PRICE, new.NAC,
/* select max(actual_ware_id) from wares_log where id=new.ware_id into :ware_id; */
+
          new.QUANT, new.DISCOUNT, new.SUMMA, new.SUMMA_O, new.DCARD, new.WARE_ID, new.PRICE_O, new.PRICE_Z,
select max(ACTUAL_WARE_ID)
+
          new.PRICE_R, new.BARCODE, new.BARCODE1, new.BCODE_IZG,
from WARES_LOG
+
          new.GODENDO, new.SERIA, new.NDS, new.SUM_NDSO, new.SERT, new.DATESERT, new.KEMVSERT, new.SDSERT, new.REGN,
where ID = new.WARE_ID
+
          new.NGTD, new.EDIZM, new.NAME_ID, new.IZG_ID, new.COUNTRY_ID, new.ORIG_CODE, new.ORIG_NAME_ID,
      and (exists(select ID
+
          new.ORIG_IZG_ID, new.ORIG_COUNTRY_ID, new.Z_ID, new.SKLAD_ID, new.SNAME, new.SIZG, new.SCOUNTRY,
                  from WARES W
+
          new.SORIG_NAME, new.SORIG_IZG, new.SORIG_COUNTRY, new.INSERTDT, new.INFO, new.KOEF, new.MOTHERPART_ID,
                  where W.D$UUID = ACTUAL_WARE_ID))
+
          new.DEP, new.SUM_DSC, new.HUMAN_QUANT, new.CUSTOMDRAW, new.PART_TYPE, new.BASE_AGENT_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;
+
  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;
  
set term ^;
+
    --вторая строчка = после приведения
 +
    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);
 +
    --
  
create or alter trigger WARES_BD_COWA for WARES
+
   end
active before delete position 0
+
</pre>
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
+
  update PARTS
+
  set WARE_ID = :WARE_ID
+
  where WARE_ID = old.ID;
+
end^
+
  
set term;
 
^
 
  
set term ^;
+
после блока
 +
<pre>
 +
select ... from parts .....
 +
if (new.group_id is null) then new.group_id=0;
 +
</pre>
  
create or alter procedure PR_TMP_CHANGEWARES_CONSTR
+
заменяем код, на следующий:
as
+
<pre>
declare variable UUID type of DM_UUID;
+
  if (new.WARE_ID not in ('-1', '0')) then -- заполняем значения позиции
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
 
   begin
  if (exists(select ID
+
    select WW.NAME_ID,
              from WARES
+
          (select SVALUE from VALS where ID = WW.NAME_ID), WW.IZG_ID,
              where D$UUID = :EXISTING_ACTUAL_WARE_ID)) then
+
          (select SVALUE from VALS where ID = WW.IZG_ID), WW.COUNTRY_ID,
    ACTUAL_UUID = EXISTING_ACTUAL_WARE_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
 
   end
   for select D$UUID
+
   else
      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
 
   begin
  insert into WARES_LOG (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID,
+
    --if (new.ware_id<0) then
                          BARCODE, Z_ID, SKLAD_ID, INSERTDT, PACKET, L_ID, ACTUAL_WARE_ID)
+
    if (new.WARE_ID = '-1') then
  select ID,
+
    begin
          NAME_ID,
+
      select GOODNAME, GOODIZG, GOODCOUNTRY from PR_MAKEGOODWAREVALUES(new.SNAME, new.SORIG_NAME, new.SIZG, new.SORIG_IZG,  
          IZG_ID,
+
new.SCOUNTRY, new.SORIG_COUNTRY, new.ORIG_CODE, new.ID, new.PART_TYPE, new.bcode_izg) into new.SNAME,
          COUNTRY_ID,
+
          new.SIZG, new.SCOUNTRY;
          ORIG_CODE,
+
      new.WARE_ID = 0;
          ORIG_NAME_ID,
+
    end
          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
end
+
</pre>
end^
+
  
set term;
+
==Триггер DOC_DETAIL_ACTIVE_BU0==
^
+
<pre>
 +
Присутствует строка
 +
if (new.z_id is null) then new.z_id=0;
 +
</pre>
  
set term ^;
+
==Представление VW_DOC_DETAIL_ACTIVE==
 +
Добавить поле DOC_DETAIL_ACTIVE.D$UUID
  
create or alter trigger WARES_LOG_AI_COWATMP for WARES_LOG
+
==Подготавливаем товары к сжатию==
active after insert position 0
+
<pre>
as
+
CREATE INDEX WARES_IDX_U ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,BARCODE,ORIG_CODE);
 +
ALTER TRIGGER WARES_BU_DISTR INACTIVE;
 +
ALTER TRIGGER WARES_AU0 INACTIVE;
 +
ALTER TRIGGER WARES_SNAME_BI0 INACTIVE;
 +
update wares set orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
 +
ALTER TRIGGER WARES_BU_DISTR ACTIVE;
 +
ALTER TRIGGER WARES_AU0 ACTIVE;
 +
ALTER TRIGGER WARES_SNAME_BI0 ACTIVE;
 +
</pre>
 +
 
 +
==Создаем индексы==
 +
<pre>
 +
CREATE INDEX WARES_LOG_IDX_AWID ON WARES_LOG (ACTUAL_WARE_ID);
 +
DROP INDEX WARES_IDX_U;
 +
CREATE UNIQUE INDEX WARES_IDX_U ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,BARCODE,ORIG_CODE);
 +
CREATE DESCENDING INDEX WARES_IDX_ID_DESC ON WARES (ID);
 +
CREATE INDEX WARES_IDX_NID ON WARES (NAME_ID);
 +
CREATE INDEX WARES_IDX_IID ON WARES (IZG_ID);
 +
CREATE INDEX WARES_IDX_CID ON WARES (COUNTRY_ID);
 +
CREATE UNIQUE INDEX WARES_IDX100 ON WARES (WHASH);
 +
</pre>
 +
 
 +
==Устанавливаем параметры работы Единого Окна==
 +
<pre>
 +
update params p set p.param_value = '0' where p.param_id = 'WARES_MODE'; /*Режим редактирования товаров - НОВЫЙ*/
 +
update params p set p.param_value = '1' where p.param_id = 'BCODE_IZG_EDIT_MODE'; /*Метод редактирования штрхкода изготовителя - НОВЫЙ*/
 +
update params p set p.param_value = '1' where p.param_id = 'WARES_ALLOW_EDIT'; /*Редактирование существующего товара - Разрешить*/
 +
update params p set p.param_value = '0' where p.param_id = 'WARES_ALLOW_ADD'; /*Режим создания нового товара - Разрешить*/
 +
</pre>
 +
 
 +
==Разрешаем торговлю в точках==
 +
<pre>
 +
ALTER TRIGGER SESSIONS_DONT_WORK_BI0 inactive;
 +
delete from Z$SERVICE where ID=999;
 +
</pre>
 +
 
 +
==Окончание==
 +
1) Желательно сделать бекап-ресторе на всех обновляемых базах;
 +
2) После перевода последней точки, включаем синхронизацию на сервере;
 +
3) Для сжатия товаров запускам в базе исправлений ТМС "Сжать товары после приведения";
 +
 
 +
==ТМС "Сжать товары после приведения" для базы исправлений==
 +
<pre>
 +
uses unMain, need, cfUtils,
 +
  db, ibdatabase, IBQuery,
 +
  sysutils, windows, cffileutils,
 +
  Classes, Graphics, Controls, Forms, Dialogs;
 +
 
 +
const
 +
  SERVERDBNAME = '127.0.0.1:C:\Standart-N\base\base_g\ZTRADE_G.FDB';
 +
  SYSTEM_PHP = '\\DEVECP\sinhro\engine\system.php';
 +
  PHP_TEXT = '<?php die("error Запущен процесс сжатия товаров. Попробуйте повторить попытку через несколько минут."); ?>';
 +
 
 +
 
 +
function CreateIBQuery(db: TIBDatabase): TIBQuery;
 
begin
 
begin
if (new.ACTUAL_WARE_ID is null) then
+
  result:=TIBQuery.Create(nil);
  exit;
+
  result.Transaction:=TIBTransaction.Create(result);
if (exists(select ID
+
   result.Transaction.Params.Text:='read_committed'#13#10'rec_version'#13#10'nowait';
            from WARES
+
   result.Database:=DB;
            where ID = new.ACTUAL_WARE_ID)) then
+
  result.Transaction.DefaultDatabase:=result.Database;
   delete from WARES
+
   result.BufferChunks:=100;
   where ID = new.ID;
+
   result.UniDirectional:=true;
else
+
end;
   insert into TMP_LOG (SKEY, SVALUE)
+
   values ('WARES_LOG_AI_COWATMP', new.ACTUAL_WARE_ID);
+
end^
+
  
set term;
+
procedure ServerCompressWares(bsingle: boolean);
^
+
var db: TIBDatabase;
 +
    q: TIBQuery;
 +
    fs: TFileStream;
 +
    s, task_caption: string;
 +
begin
 +
  frmManagerXP2.LogIt('Сжатие товаров (на сервере)...');
 +
  db:=TIBDatabase.Create(nil);
 +
  q:=CreateIBQuery(db);
 +
  fs:=TFileStream.Create(extractfilepath(application.exename)+'cowa.script',fmCreate);
 +
  screen.cursor:=crhourGlass;
 +
try
 +
  db.defaultTransaction:=TIBTransaction.Create(db);
 +
  db.defaultTransaction.Params.Text:='read_committed'#13#10'rec_version'#13#10'nowait';
 +
  db.LoginPrompt:=false;
 +
//  db.databasename:='standart-n:D:\STANDART-N\base_g\ztrade_g.fdb';
 +
  db.databasename:=SERVERDBNAME;
 +
  db.params.text:='user_name=sysdba'#13#10'password=masterkey'#13#10'lc_ctype=WIN1251';
 +
  db.connected:=true;
 +
  frmManagerXP2.LogIt('  подключение выполнено ('+db.databasename+')');
 +
  q.Transaction.StartTransaction;
 +
try
 +
  frmManagerXP2.LogIt('  Процесс обработки...');
 +
  if bsingle then
 +
    q.sql.text:='select s from AA_COWA_COMPRESS_WARES_SINGLE(1)'
 +
  else
 +
    q.sql.text:='select s from AA_COWA_COMPRESS_WAREs(1)';
 +
  q.active:=true;
 +
  if q.eof then
 +
  begin
 +
    frmManagerXP2.LogIt('  Нет записей для сжатия.');
 +
    q.transaction.Commit;
 +
    exit;
 +
  end;
 +
  while not q.eof do
 +
  begin
 +
    s:=q.fieldbyname('s').asstring+#13#10;
 +
    cfStreamWrite(fs,s);
 +
    if q.RecNo mod 500 = 0 then
 +
      frmManagerXP2.LogIt('    обработано записей: '+inttostr(q.RecNo));
 +
    q.Next;
 +
  end;
 +
  frmManagerXP2.LogIt('  Всего обработано записей: '+inttostr(q.RecNo));
 +
  if bSingle then
 +
    task_caption:='cowa auto single '+formatdatetime('yyyymmdd',now)
 +
  else
 +
    task_caption:='cowa auto '+formatdatetime('yyyymmdd',now);
 +
  frmManagerXP2.LogIt('  Создание задач на централизованное обновление ("'+task_caption+'")...');
 +
  q.active:=false;
 +
  q.sql.text:='insert into g$tasks (PROFILE_ID,TASK_TYPE,CAPTION,data) select id, 0, '''+task_caption+''',:data from g$profiles';
 +
  q.prepare;
 +
  fs.position:=0;
 +
  q.paramByName('data').LoadFromStream(fs,ftBlob);
 +
  q.execsql;
 +
  frmManagerXP2.LogIt('  Создание задач на централизованное обновление ("'+task_caption+'") - выполнено');
 +
  frmManagerXP2.LogIt('  подтверждение транзакции...');
 +
  q.transaction.Commit;
 +
  frmManagerXP2.LogIt('  подтверждение транзакции - ОК');
 +
except
 +
  q.transaction.rollback;
 +
  frmManagerXP2.LogIt('  Ошибка: '+GLEM);
 +
end;
 +
finally
 +
  screen.cursor:=crDefault;
 +
  fs.free;
 +
  q.free;
 +
  db.Free;
 +
end;
 +
end;
  
alter trigger WARES_AD_DISTR inactive;
+
procedure SyncOff;
alter trigger WARES_BU_DISTR inactive;
+
begin
alter trigger WARES_LOG_BI_DISTR inactive;
+
  frmManagerXP2.LogIt('Отключение синхронизации...');
alter trigger WARES_LOG_AI0 inactive;
+
  if fileexists(SYSTEM_PHP+'.cowa') then raise('Ошибка отключения синхронизации(найден файл "'+SYSTEM_PHP+'.cowa").'#13#10'Возможно процесс сжатия товаров уже запущен.');
alter trigger PARTS_AU0 inactive;
+
  if not fileexists(SYSTEM_PHP) then raise('Отключение невозможно. Файл "'+SYSTEM_PHP+'" - не найден');
 +
  movefile(SYSTEM_PHP,SYSTEM_PHP+'.cowa');
 +
  if not fileexists(SYSTEM_PHP+'.cowa') then raise('Ошибка переименования "'+SYSTEM_PHP+'" в '+SYSTEM_PHP+'.cowa');
 +
  stringtofile(PHP_TEXT,SYSTEM_PHP);
 +
  if not fileexists(SYSTEM_PHP) then raise('Ошибка создания файла "'+SYSTEM_PHP+'"!');
 +
  frmManagerXP2.LogIt('Отключение синхронизации - ОК');
 +
end;
  
alter trigger PARTS_BU_DISTR inactive;
+
 
alter trigger PARTS_BU_G$SYNC inactive;
+
procedure SyncOn;
alter trigger PARTS_BU0 inactive;
+
begin
 +
  frmManagerXP2.LogIt('Включение синхронизации...');
 +
  if not fileexists(SYSTEM_PHP+'.cowa') then raise('Ошибка включения синхронизации(не найден файл "'+SYSTEM_PHP+'.cowa").');
 +
  if not fileexists(SYSTEM_PHP) then raise('Не найден файл "'+SYSTEM_PHP+'!');
 +
  if GetFileSize_(SYSTEM_PHP)<>length(PHP_TEXT) then raise('Содержимое файла "'+SYSTEM_PHP+'" - не соответстувет операции.');
 +
  deletefile(SYSTEM_PHP);
 +
  if fileexists(SYSTEM_PHP) then raise('Ошибка удаления файла "'+SYSTEM_PHP+'!');
 +
  movefile(SYSTEM_PHP+'.cowa',SYSTEM_PHP);
 +
  if not fileexists(SYSTEM_PHP) then raise('Ошибка переименования "'+SYSTEM_PHP+'.cowa" в '+SYSTEM_PHP+'!');
 +
  if fileexists(SYSTEM_PHP+'.cowa') then raise('Ошибка переименования "'+SYSTEM_PHP+'.cowa" в '+SYSTEM_PHP+'(найден бэкап)!');
 +
  frmManagerXP2.LogIt('Включение синхронизации - ОК');
 +
end;
 +
 
 +
procedure CompressWares(bSingle: boolean);
 +
begin
 +
try
 +
  SyncOff;
 +
//  showmessage('sddfsd');
 +
  ServerCompressWares(bSingle);
 +
except
 +
  frmManagerXP2.LogIt(GLEM);
 +
  application.MessageBox(GLEM,'Ошибка',MB_ICONSTOP);
 +
end;
 +
try
 +
  SyncOn;
 +
except
 +
  frmManagerXP2.LogIt(GLEM);
 +
  application.MessageBox('Внимание!!!'#13#10'Ошибка запуска синхронизации: '#13#10+GLEM+#13#10'Необходимо восстановить файл system.php вручную!!!','Ошибка',MB_ICONSTOP);
 +
end;
 +
end;
 +
 
 +
begin
 +
  if application.MessageBox('Запустить процедуру централизованного сжатия товаров?'#13#10#13#10'Удаление неуникальных товаров с указанием актуального товара, рассылка скриптов всем профилям.','Подтверждение',MB_YESNO+MB_ICONQUESTION)=idYes then
 +
  begin
 +
    CompressWares(false);
 +
    application.MessageBox('Процедура централизованного сжатия товаров выполнена.','Отчет',MB_ICONINFORMATION);
 +
  end;
 +
end;                          
 
</pre>
 
</pre>
 +
 +
==Сетка для обработки запросов товаров==
 +
Прикрепленный файл  [[Файл: Запросы_товаров.zip]]

Текущая версия на 09:38, 28 июля 2017

Предварительная подготовка

1) Обновляем у всех клиентов Менеджер до версии не ниже ManagerXP2_273_5 от декабря 2016г.
2) Обновляем клиента синхронизации до версии не ниже DistributeClient2.202 от декабря 2016г.
3) Настраиваем двустороннюю синхронизацию таблиц WARES_TREB и G$PROFILES;
4) Проверяем, что настроена двусторонняя синхронизация таблицы WARES_LOG; 
5) Проверяем, что у клиентов в PARAMS есть параметр CODE_PROFILE (Код профиля) и он корректный; 

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

1) Обновляем скрипты синхронизации на сервере;
2) Блокируем синхронизацию (оставляем только g$tasks), для отправки обновлений клиентам;
3) Готовим серверую БД Подготовка серверной БД для перевода на Единое Окно;

Останавливаем торговлю в точках

CREATE EXCEPTION EX_DONT_WORK 'Вход в программу заблокирован. Идет обновление...';
SET TERM ^ ;
CREATE OR ALTER TRIGGER SESSIONS_DONT_WORK_BI0 FOR SESSIONS
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  exception EX_DONT_WORK; --Вход в программу заблокирован. Идет обновление...
end
^
SET TERM ; ^
INSERT INTO Z$SERVICE (ID, TASK, STATUS, INSERTDT, STARTDT, ENDDT, TXTLOG) VALUES (999, 'POLITE_RESTORE', 0, current_timestamp, current_timestamp, current_timestamp, NULL);
update sessions s
set s.enddt=current_timestamp, s.endflag=0, s.endsession_id=s.id
where 1=1
and s.prog containing 'manager'
and s.endflag is null
and cast(s.startdt as dm_date) >= dateadd(-3 day to current_date);

Обновляем структуру БД клиентов

CREATE EXCEPTION EX_WARES_ID_NOT_FOUND 'Не найдена позиция (wares)';

/*******************************************************************/
/*НОВЫЕ ПОЛЯ*/

ALTER TABLE WARES ADD G$PROFILE_ID DM_ID_NULL;
ALTER TABLE WARES ADD WHASH DM_ID;
ALTER TABLE WARES_LOG ADD ACTUAL_WARE_ID DM_UUID_NULL;
ALTER TABLE PARTS ADD ORIG_SNAME DM_TEXT;
ALTER TABLE PARTS ADD ORIG_SIZG DM_TEXT;
ALTER TABLE PARTS ADD ORIG_SCOUNTRY DM_TEXT;
ALTER TABLE PARTS ADD ORIG_BCODE_IZG DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD D$UUID DM_UUID_NULL;
ALTER TABLE DOC_DETAIL_ACTIVE ADD D$SRVUPDDT DM_DATETIME;
ALTER TABLE DOC_DETAIL_VIRTUAL ADD D$UUID DM_UUID;
ALTER TABLE DOC_DETAIL_VIRTUAL ADD D$SRVUPDDT DM_DATETIME;

/*******************************************************************/
/*ПРОЦЕДУРЫ*/



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_TEXT;
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_TEXT;
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_TEXT;
declare variable DDALOG_SNAME DM_TEXT;
declare variable DDALOG_SIZG DM_TEXT;
declare variable DDALOG_SCOUNTRY DM_TEXT;
declare variable DDA_SNAME DM_TEXT;
declare variable DDA_SIZG DM_TEXT;
declare variable DDA_SCOUNTRY DM_TEXT;
begin

 select bcode_izg, ware_id, sname, sizg, scountry,
   (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_sname, dda_sizg, dda_scountry, 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, sname, sizg, scountry,
   (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_sname, ddalog_sizg, ddalog_scountry, ddalog_name_id, ddalog_izg_id, ddalog_country_id,
       ddalog_orig_nameid, ddalog_orig_izgid, ddalog_orig_countryid, ddalog_z_id, ddalog_sklad_id;

 if ( (ddalog_ware_id is null) or (ddalog_sname is null) or
      ((dda_sname = ddalog_sname) and (dda_sizg = ddalog_sizg) and (dda_scountry = ddalog_scountry) and (dda_barcode = ddalog_barcode)) )  then exit;


 if ( (ddalog_ware_id = '0') or (ddalog_ware_id = '-1') ) then
 begin
   if (not exists(select id from wares_log where id = :dda_ware_id and name_id = :ddalog_name_id and izg_id = :ddalog_izg_id
       and country_id = :ddalog_country_id and barcode = :ddalog_barcode)) then
       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


end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_AUTO_WARESLINK;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_AUTO_WARESLINK;
GRANT SELECT ON DOC_DETAIL_ACTIVE_LOG TO PROCEDURE PR_AUTO_WARESLINK;
GRANT SELECT,INSERT ON WARES_LOG TO PROCEDURE PR_AUTO_WARESLINK;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_AUTO_WARESLINK TO SYSDBA;



SET TERM ^ ;

create or alter procedure PR_NEW_PART (
    DOC_ID type of DM_ID,
    PARENT_ID type of DM_ID,
    WARE_ID type of DM_UUID_NULL,
    PRICE type of DM_DOUBLE,
    PRICE_O type of DM_DOUBLE,
    PRICE_Z type of DM_DOUBLE,
    PRICE_R type of DM_DOUBLE,
    QUANT type of DM_DOUBLE,
    BARCODE type of DM_TEXT,
    BARCODE1 type of DM_TEXT,
    DEP type of DM_ID,
    GODENDO type of DM_DATETIME,
    SERIA type of DM_TEXT,
    NDS type of DM_DOUBLE,
    SUM_NDSO type of DM_DOUBLE,
    SERT type of DM_TEXT,
    DATESERT type of DM_DATETIME,
    KEMVSERT type of DM_TEXT,
    SDSERT type of DM_DATETIME,
    REGN type of DM_TEXT,
    NGTD type of DM_TEXT,
    EDIZM type of DM_TEXT,
    NAC type of DM_DOUBLE,
    MOTHERPART_ID type of DM_ID,
    PART_TYPE DM_STATUS,
    BASE_AGENT_ID DM_ID,
    SKLAD_ID type of DM_TEXT,
    CONTRACT_ID DM_ID,
    DOC_DETAIL_ACTIVE_ID DM_ID,
    GROUP_ID DM_ID,
    MOTHERPART_UUID DM_UUID_NULL = 0,
    EGAIS_ID DM_TEXT1024 = null,
    EGAIS_REGID DM_TEXT1024 = null,
    EGAIS_BREGID DM_TEXT1024 = null,
    EGAIS_ALCCODE DM_TEXT1024 = null,
    EGAIS_QUANT DM_DOUBLE = 0,
    CAPACITY DM_DOUBLE = 0,
    ALC_VOLUME DM_DOUBLE = 0,
    EGAIS_PRODUCER_ID DM_TEXT1024 = null,
    EGAIS_BARCODE DM_TEXT1024 = null,
    EGAIS_TYPE DM_TEXT = null,
    PRODUCER_INN DM_TEXT1024 = null,
    PRODUCER_KPP DM_TEXT1024 = null,
    BOTTLINGDATE DM_DATETIME = null,
    EGAIS_PRODUCT_VCODE DM_TEXT = null)
returns (
    P_ID type of DM_ID)
as
declare variable ORIG_SNAME DM_TEXT = 0;
declare variable ORIG_SIZG DM_TEXT = 0;
declare variable ORIG_SCOUNTRY DM_TEXT = 0;
declare variable ORIG_BCODE_IZG DM_TEXT;
begin

  select iif(trim(coalesce(sorig_name, '')) = '', sname, sorig_name),
  iif(trim(coalesce(sorig_izg, '')) = '', sizg, sorig_izg),
  iif(trim(coalesce(sorig_country, '')) = '', scountry, sorig_country),
  bcode_izg
  from doc_detail_active where id = :doc_detail_active_id into :orig_sname, :orig_sizg, :orig_scountry, :orig_bcode_izg;


  p_id=gen_id(gen_parts_id,1);
  insert into parts
    

(ID,parent_id,doc_id,WARE_ID,PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE,BARCODE1,DEP,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,
    DATESERT,KEMVSERT,SDSERT,REGN,NGTD,
    EDIZM,NAC,motherpart_id,sklad_id,part_type,BASE_AGENT_ID,contract_id, doc_detail_active_id, group_id, motherpart_uuid,
    EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE, EGAIS_QUANT, CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID, EGAIS_BARCODE,
    EGAIS_TYPE, PRODUCER_INN, PRODUCER_KPP, BOTTLINGDATE, EGAIS_PRODUCT_VCODE, ORIG_SNAME, ORIG_SIZG, ORIG_SCOUNTRY, 

ORIG_BCODE_IZG)
  values
    

(:p_id,:parent_id,:doc_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,
   :SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,
    :SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id,:sklad_id,:part_type,:BASE_AGENT_ID,:contract_id,:doc_detail_active_id, :group_id, :motherpart_uuid,
    :EGAIS_ID, :EGAIS_BREGID, :EGAIS_REGID, :EGAIS_ALCCODE, :EGAIS_QUANT, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID, 

:EGAIS_BARCODE,
    :EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE, :ORIG_SNAME, :ORIG_SIZG, :ORIG_SCOUNTRY, 

:ORIG_BCODE_IZG);
  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_NEW_PART;
GRANT INSERT ON PARTS TO PROCEDURE PR_NEW_PART;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO SYSDBA;


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
begin
  if (ORIG_CODE is null) then
    ORIG_CODE = '';
  if (SEARCHINLOGS is null) then
    SEARCHINLOGS = 0;
  if (SEARCHINLOGS = 1) then
  begin
--    select iif(max(actual_ware_id) is null, max(id), 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 :WARE_ID;
    select first 1 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;
    if (WARE_ID is not null) then
      WARE_ID=coalesce((select first 1 actual_ware_id from WARES_LOG where id=:WARE_ID and actual_ware_id is not null order by log_insertdt desc),ware_id);
    if (WARE_ID is not null) then
        select id from wares where id = :WARE_ID 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^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON WARES_LOG TO PROCEDURE PR_GETWARE_BY_IDS;
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GETWARE_BY_IDS;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_UPDATEWARE;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_UPDATEWARE4PART;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO SYSDBA;



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 = 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
  if (Z_ID is null) then Z_ID = 0;
  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;
*/

  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
    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 :W_ID;
  else
    select first 1 ID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID into :W_ID;
--  select id from wares where whash = (select whash from PR_GET_WARES_HASH(:NAME_ID,:IZG_ID,:COUNTRY_ID,:BARCODE,:orig_code)) 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^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_GET_WARE;
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GET_WARE;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO SYSDBA;


SET TERM ^ ;

create or alter procedure PR_MGNLINK (
    VTYPE type of DM_STATUS,
    RIGHT_ID type of DM_UUID_NULL,
    WRONG_ID type of DM_UUID_NULL,
    WITHBLOCKS type of DM_STATUS)
as
declare variable NAME_ID type of DM_UUID_NULL;
declare variable COUNTRY_ID type of DM_UUID_NULL;
declare variable IZG_ID type of DM_UUID_NULL;
declare variable W_NAME_ID type of DM_UUID_NULL;
declare variable W_COUNTRY_ID type of DM_UUID_NULL;
declare variable W_IZG_ID type of DM_UUID_NULL;
declare variable W_ORIG_CODE DM_TEXT;
declare variable W_ORIG_NAME_ID type of DM_UUID_NULL;
declare variable W_ORIG_COUNTRY_ID type of DM_UUID_NULL;
declare variable W_ORIG_IZG_ID type of DM_UUID_NULL;
declare variable ID DM_UUID_NULL;
declare variable TNAME_ID type of DM_UUID_NULL;
declare variable CHECK_ID DM_UUID_NULL;
declare variable BARCODE DM_TEXT;
begin
select NAME_ID, COUNTRY_ID, IZG_ID from WARES where ID = :RIGHT_ID into :NAME_ID, :COUNTRY_ID, :IZG_ID;
select NAME_ID, COUNTRY_ID, IZG_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_COUNTRY_ID, ORIG_IZG_ID, BARCODE from WARES where ID = :WRONG_ID
into :W_NAME_ID, :W_COUNTRY_ID, :W_IZG_ID, :W_ORIG_CODE, :W_ORIG_NAME_ID, :W_ORIG_COUNTRY_ID, :W_ORIG_IZG_ID, :BARCODE;
if ((NAME_ID is null) or (COUNTRY_ID is null) or (IZG_ID is null)) then
exception EX_WARES_ID_NOT_FOUND;
--  execute procedure pr_debug_wrongvals(:dbgsname,:name_id);
--  execute procedure pr_debug_wrongvals(:dbgsizg,:izg_id);
--  execute procedure pr_debug_wrongvals(:dbgscountry,:country_id);
CHECK_ID = '0';
if (vtype = 0) then
begin
select ware_id from PR_GETWARE_BY_IDS(:name_id, :w_izg_id, :w_country_id, :w_orig_code, :w_orig_name_id, :w_orig_izg_id, :w_orig_country_id, 0, 0, 

:BARCODE)
into :CHECK_ID;
end
else if (vtype = 2) then
begin
select ware_id from PR_GETWARE_BY_IDS(:w_name_id, :w_izg_id, :country_id, :w_orig_code, :w_orig_name_id, :w_orig_izg_id, :w_orig_country_id, 0, 0, 

:BARCODE)
into :CHECK_ID;
end
else if (vtype = 3) then
begin
select ware_id from PR_GETWARE_BY_IDS(:w_name_id, :izg_id, :w_country_id, :w_orig_code, :w_orig_name_id, :w_orig_izg_id, :w_orig_country_id, 0, 0, 

:BARCODE)
into :CHECK_ID;
end
if (CHECK_ID <> '0') then
begin
update WARES set ORIG_CODE = substring(id || '@' || orig_code from 1 for 250)  where ID = :WRONG_ID;
--update PARTS set WARE_ID = :right_id where WARE_ID = :WRONG_ID; --version 2: updating parts, but no actual link is created
--exit;
end
if (VTYPE = 0) then
begin
select ID, NAME_ID from WARES where ID = :WRONG_ID into :ID, :TNAME_ID;
if (WITHBLOCKS = 1) then
begin
update WARES set NAME_ID = :NAME_ID where ID = :ID or NAME_ID = :TNAME_ID;
end
else
begin
update WARES set NAME_ID = :NAME_ID where ID = :WRONG_ID;
end
end
else
if (VTYPE = 2) then
begin
--    select svalue from vals where id=:country_id into :tmp;
--    if (tmp is null) then exception ex_svalue_not_found;
update WARES set COUNTRY_ID = :COUNTRY_ID where ID = :WRONG_ID;
--    update totalpricelist set country_id=:country_id, scountry=:tmp where system_id=:wrong_id;
end
else
if (VTYPE = 3) then
begin
--    select svalue from vals where id=:izg_id into :tmp;
--    if (tmp is null) then exception ex_svalue_not_found;
update WARES set IZG_ID = :IZG_ID where ID = :WRONG_ID;
--    update totalpricelist set izg_id=:izg_id, sizg=:tmp where system_id=:wrong_id;
end
else
exception EX_UNKNOWN_MGNTYPE;
for select PART_ID from WAREBASE where WARE_ID in (:WRONG_ID, :RIGHT_ID) into :ID
do
execute procedure PR_UPDBLOCKINFO(:ID);
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT,UPDATE ON WARES TO PROCEDURE PR_MGNLINK;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MGNLINK;
GRANT SELECT ON WAREBASE TO PROCEDURE PR_MGNLINK;
GRANT EXECUTE ON PROCEDURE PR_UPDBLOCKINFO TO PROCEDURE PR_MGNLINK;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_MGNLINK TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_MGNLINK TO SYSDBA;



SET TERM ^ ;

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 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^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON VALS TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT SELECT ON VW_WARES TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT SELECT ON WARES TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT SELECT ON WARES_LOG TO PROCEDURE PR_MAKEGOODWAREVALUES;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_MAKEGOODWAREVALUES TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_MAKEGOODWAREVALUES TO SYSDBA;



SET TERM ^ ;

create or alter procedure PR_GET_WARES_HASH (
    NAME_ID type of DM_UUID_NULL,
    IZG_ID type of DM_UUID_NULL,
    COUNTRY_ID type of DM_UUID_NULL,
    BARCODE type of DM_TEXT,
    ORIG_CODE type of 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, '')||coalesce

(:ORIG_CODE, ''));
  else
   whash = hash(coalesce(:NAME_ID,'NAME_ID')||coalesce(:IZG_ID,'IZG_ID')||coalesce(:COUNTRY_ID,'COUNTRY_ID')||coalesce(:ORIG_CODE, ''));
  suspend;
end^

SET TERM ; ^

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GET_WARES_HASH TO PROCEDURE PR_GETWARE_BY_IDS;
GRANT EXECUTE ON PROCEDURE PR_GET_WARES_HASH TO PROCEDURE PR_GET_WARE;
GRANT EXECUTE ON PROCEDURE PR_GET_WARES_HASH TO SYSDBA;


SET TERM ^ ;

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 ; ^





/*******************************************************************/
/*ТРИГГЕРЫ*/

SET SQL DIALECT 3;


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
  if (new.orig_code is null) then
    new.orig_code='';
  if (new.barcode is null) then
    new.barcode='';
  select first 1 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 orig_code=new.orig_code 
     and  d$uuid<>new.d$uuid into :cur_id;
  if (cur_id is null) then exit;
  new.orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
end
^


SET TERM ; ^

SET SQL DIALECT 3;

SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_BI_DISTR FOR WARES
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  new.insertdt=current_timestamp;
  new.PACKET = gen_id(gen_WARES_PACKET,1);
  if (new.orig_code is null) then
    new.orig_code='';
  if (new.id is null) then
  begin
    new.id=UUID_TO_CHAR(GEN_UUID());
  end
  new.d$uuid=new.id;
  if (new.d$srvupddt is null) then
  begin

    if ((select param_value from params where param_id = 'WARES_ALLOW_ADD') = '1') then --Режим создания нового товара (1-запрещено)
      exception EX_WARES_ID_NOT_FOUND (select svalue from vals where id = new.name_id);

    new.g$profile_id = cast((select param_value from params where param_id = 'CODE_PROFILE') as dm_id);
    new.d$srvupddt='2000-01-01';
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('WARES',new.d$uuid,0,null) matching (TABLENAME,UUID);
  end
  if (new.l_id is null) then
    new.l_id=GEN_ID(GEN_WARES_ID,1);
end
^


SET TERM ; ^



SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_SNAME_BI0 FOR WARES
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.sname = (select svalue from vals where id = new.name_id);
end
^


SET TERM ; ^


SET SQL DIALECT 3;


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
  if (new.orig_code is null) then
    new.orig_code='';
  if (new.barcode is null) then
    new.barcode='';
  select first 1 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 orig_code=new.orig_code and d

$uuid<>new.d$uuid into :cur_id;
  if (cur_id is null) then exit;
  new.orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
end
^


SET TERM ; ^

SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_BU0 FOR WARES
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (new.orig_code is null) then
    new.orig_code='';
  if (new.name_id <> old.name_id) then
  begin
    new.mgn_name = null;
    new.mgn_id = 0;
  end
end
^


SET TERM ; ^





SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_BU_MGN FOR WARES
ACTIVE BEFORE UPDATE POSITION 0
AS
 begin
  if (new.name_id <> old.name_id) then
  begin
    new.mgn_name = null;
    new.mgn_id = 0;
  end
  new.sname = (select svalue from vals where id = new.name_id);
 end
^


SET TERM ; ^


SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_AU_DDA FOR WARES
ACTIVE AFTER UPDATE POSITION 0
AS
declare variable w_sname DM_TEXT;
declare variable w_sizg DM_TEXT;
declare variable w_scountry DM_TEXT;
begin
  if ( (new.id = old.id) and
   ((new.name_id <> old.name_id) or (new.izg_id <> old.izg_id)
   or (new.country_id <> old.country_id) or (new.barcode <> old.barcode)) ) then
  begin

    select svalue from vals where id = new.name_id into :w_sname;
    select svalue from vals where id = new.izg_id into :w_sizg;
    select svalue from vals where id = new.country_id into :w_scountry;

    update doc_detail_active set sname = :w_sname, sizg = :w_sizg, scountry = :w_scountry,
    bcode_izg = new.barcode, name_id = new.name_id, izg_id = new.izg_id, country_id = new.country_id
    where ware_id=new.id;

    update doc_detail_virtual set sname = :w_sname, sizg = :w_sizg, scountry = :w_scountry,
    bcode_izg = new.barcode, name_id = new.name_id, izg_id = new.izg_id, country_id = new.country_id
    where ware_id=new.id;

    update doc_detail_active_treb set sname = :w_sname, sizg = :w_sizg, scountry = :w_scountry,
    bcode_izg = new.barcode, name_id = new.name_id, izg_id = new.izg_id, country_id = new.country_id
    where ware_id=new.id;

  end
end
^


SET TERM ; ^


SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER VALS_BD0 FOR VALS
ACTIVE BEFORE DELETE POSITION 0
AS
begin
  exception ex_wrong_oper  'попытка удаления наименования! ' || old.id;
end
^


SET TERM ; ^


SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BI_DISTR FOR DOC_DETAIL_ACTIVE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.d$uuid is null) then
  begin
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
    new.d$srvupddt='2000-01-01';
  end
end
^


SET TERM ; ^



SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_HASH_BUI0 FOR WARES
ACTIVE BEFORE INSERT OR UPDATE POSITION 1900
AS
begin
 select whash from PR_GET_WARES_HASH(new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.BARCODE,new.orig_code) into new.whash;
end
^


SET TERM ; ^


SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BU_XL FOR DOC_DETAIL_ACTIVE
ACTIVE BEFORE UPDATE POSITION 999
AS
begin
  if ( (new.part_id = 0) and ((new.sname <> old.sname) or (new.sizg <> old.sizg) or (new.scountry <> old.scountry) or (new.bcode_izg <> old.bcode_izg)) )   then
  begin
      new.WARE_ID = '0';

      select first 1 ID from WARES where
      NAME_ID = (select ID from VALS where VTYPE = 0 and ALTTYPE = new.PART_TYPE and SVALUE = new.SNAME) and
      IZG_ID = (select ID from VALS where VTYPE = 3 and ALTTYPE = new.PART_TYPE and SVALUE = new.SIZG) and
      COUNTRY_ID = (select ID from VALS where VTYPE = 2 and ALTTYPE = new.PART_TYPE and SVALUE = new.SCOUNTRY) and
      BARCODE = new.BCODE_IZG
      into new.WARE_ID;
  end
end
^


SET TERM ; ^


SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER PARTS_BU0 FOR PARTS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin

  if (new.ware_id <> old.ware_id) then
  begin
    insert into parts_log (
ID,
PARENT_ID,
DOC_ID,
WARE_ID,
PRICE,
PRICE_O,
PRICE_Z,
PRICE_R,
QUANT,
BARCODE,
BARCODE1,
DEP,
GODENDO,
SERIA,
NDS,
SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
INSERTDT,
NAC,
MOTHERPART_ID,
ENDDT,
SESSION_ID,
PACKET,
sklad_id,
part_type,
base_agent_id,
contract_id,
DOC_DETAIL_ACTIVE_ID,
vpart_id
)
values(
old.ID,
old.PARENT_ID,
old.DOC_ID,
old.WARE_ID,
old.PRICE,
old.PRICE_O,
old.PRICE_Z,
old.PRICE_R,
old.QUANT,
old.BARCODE,
old.BARCODE1,
old.DEP,
old.GODENDO,
old.SERIA,
old.NDS,
old.SUM_NDSO,
old.SERT,
old.DATESERT,
old.KEMVSERT,
old.SDSERT,
old.REGN,
old.NGTD,
old.EDIZM,
old.INSERTDT,
old.NAC,
old.MOTHERPART_ID,
old.ENDDT,
old.SESSION_ID,
old.PACKET,
old.sklad_id,
old.part_type,
old.base_agent_id,
old.contract_id,
old.doc_detail_active_id,
old.vpart_id
);
  end
end
^


SET TERM ; ^

ALTER TRIGGER WARES_LOG_AI0 INACTIVE;



/*******************************************************************/
/*ПРЕДСТАВЛЕНИЯ*/



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

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



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


/* View: VW_WARES */
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,
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 order by sname, BARCODE
;




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


/* Privileges of users */
GRANT ALL ON VW_WARES TO STANDART WITH GRANT OPTION;



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

/******************************************************************************/
/***      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,
    RQ_SPROFILE_ID)
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,
(select caption from g$profiles where id = wt.RQ_PROFILE_ID)
from wares_treb wt
left join wares w on wt.ware_id = w.id
;

Триггер DOC_DETAIL_ACTIVE_BI

Правим руками, т.к. у каждого клиента тут есть особенности

после блока

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


после блока

select ... from parts .....
if (new.group_id is null) then new.group_id=0;

заменяем код, на следующий:

  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

Триггер DOC_DETAIL_ACTIVE_BU0

 Присутствует строка
if (new.z_id is null) then new.z_id=0;

Представление VW_DOC_DETAIL_ACTIVE

Добавить поле DOC_DETAIL_ACTIVE.D$UUID

Подготавливаем товары к сжатию

CREATE INDEX WARES_IDX_U ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,BARCODE,ORIG_CODE);
ALTER TRIGGER WARES_BU_DISTR INACTIVE;
ALTER TRIGGER WARES_AU0 INACTIVE;
ALTER TRIGGER WARES_SNAME_BI0 INACTIVE;
update wares set orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
ALTER TRIGGER WARES_BU_DISTR ACTIVE;
ALTER TRIGGER WARES_AU0 ACTIVE;
ALTER TRIGGER WARES_SNAME_BI0 ACTIVE;

Создаем индексы

CREATE INDEX WARES_LOG_IDX_AWID ON WARES_LOG (ACTUAL_WARE_ID);
DROP INDEX WARES_IDX_U;
CREATE UNIQUE INDEX WARES_IDX_U ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,BARCODE,ORIG_CODE);
CREATE DESCENDING INDEX WARES_IDX_ID_DESC ON WARES (ID);
CREATE INDEX WARES_IDX_NID ON WARES (NAME_ID);
CREATE INDEX WARES_IDX_IID ON WARES (IZG_ID);
CREATE INDEX WARES_IDX_CID ON WARES (COUNTRY_ID);
CREATE UNIQUE INDEX WARES_IDX100 ON WARES (WHASH);

Устанавливаем параметры работы Единого Окна

update params p set p.param_value = '0' where p.param_id = 'WARES_MODE'; /*Режим редактирования товаров - НОВЫЙ*/
update params p set p.param_value = '1' where p.param_id = 'BCODE_IZG_EDIT_MODE'; /*Метод редактирования штрхкода изготовителя - НОВЫЙ*/ 
update params p set p.param_value = '1' where p.param_id = 'WARES_ALLOW_EDIT'; /*Редактирование существующего товара - Разрешить*/
update params p set p.param_value = '0' where p.param_id = 'WARES_ALLOW_ADD'; /*Режим создания нового товара - Разрешить*/

Разрешаем торговлю в точках

ALTER TRIGGER SESSIONS_DONT_WORK_BI0 inactive;
delete from Z$SERVICE where ID=999;

Окончание

1) Желательно сделать бекап-ресторе на всех обновляемых базах;
2) После перевода последней точки, включаем синхронизацию на сервере;
3) Для сжатия товаров запускам в базе исправлений ТМС "Сжать товары после приведения";

ТМС "Сжать товары после приведения" для базы исправлений

uses unMain, need, cfUtils,
  db, ibdatabase, IBQuery,
  sysutils, windows, cffileutils,
  Classes, Graphics, Controls, Forms, Dialogs;

const
  SERVERDBNAME = '127.0.0.1:C:\Standart-N\base\base_g\ZTRADE_G.FDB';
  SYSTEM_PHP = '\\DEVECP\sinhro\engine\system.php';
  PHP_TEXT = '<?php die("error Запущен процесс сжатия товаров. Попробуйте повторить попытку через несколько минут."); ?>';


function CreateIBQuery(db: TIBDatabase): TIBQuery;
begin
  result:=TIBQuery.Create(nil);
  result.Transaction:=TIBTransaction.Create(result);
  result.Transaction.Params.Text:='read_committed'#13#10'rec_version'#13#10'nowait';
  result.Database:=DB;
  result.Transaction.DefaultDatabase:=result.Database;
  result.BufferChunks:=100;
  result.UniDirectional:=true;
end;

procedure ServerCompressWares(bsingle: boolean);
var db: TIBDatabase;
    q: TIBQuery;
    fs: TFileStream;
    s, task_caption: string;
begin
  frmManagerXP2.LogIt('Сжатие товаров (на сервере)...');
  db:=TIBDatabase.Create(nil);
  q:=CreateIBQuery(db);
  fs:=TFileStream.Create(extractfilepath(application.exename)+'cowa.script',fmCreate);
  screen.cursor:=crhourGlass;
 try
  db.defaultTransaction:=TIBTransaction.Create(db);
  db.defaultTransaction.Params.Text:='read_committed'#13#10'rec_version'#13#10'nowait';
  db.LoginPrompt:=false;
//  db.databasename:='standart-n:D:\STANDART-N\base_g\ztrade_g.fdb';
  db.databasename:=SERVERDBNAME;
  db.params.text:='user_name=sysdba'#13#10'password=masterkey'#13#10'lc_ctype=WIN1251';
  db.connected:=true;
  frmManagerXP2.LogIt('  подключение выполнено ('+db.databasename+')');
  q.Transaction.StartTransaction;
 try
  frmManagerXP2.LogIt('  Процесс обработки...');
  if bsingle then
    q.sql.text:='select s from AA_COWA_COMPRESS_WARES_SINGLE(1)'
  else
    q.sql.text:='select s from AA_COWA_COMPRESS_WAREs(1)';
  q.active:=true;
  if q.eof then
  begin
    frmManagerXP2.LogIt('  Нет записей для сжатия.');
    q.transaction.Commit;
    exit;
  end;
  while not q.eof do
  begin
    s:=q.fieldbyname('s').asstring+#13#10;
    cfStreamWrite(fs,s);
    if q.RecNo mod 500 = 0 then
      frmManagerXP2.LogIt('    обработано записей: '+inttostr(q.RecNo));
    q.Next;
  end;
  frmManagerXP2.LogIt('  Всего обработано записей: '+inttostr(q.RecNo));
  if bSingle then
    task_caption:='cowa auto single '+formatdatetime('yyyymmdd',now)
  else
    task_caption:='cowa auto '+formatdatetime('yyyymmdd',now);
  frmManagerXP2.LogIt('  Создание задач на централизованное обновление ("'+task_caption+'")...');
  q.active:=false;
  q.sql.text:='insert into g$tasks (PROFILE_ID,TASK_TYPE,CAPTION,data) select id, 0, '''+task_caption+''',:data from g$profiles';
  q.prepare;
  fs.position:=0;
  q.paramByName('data').LoadFromStream(fs,ftBlob);
  q.execsql;
  frmManagerXP2.LogIt('  Создание задач на централизованное обновление ("'+task_caption+'") - выполнено');
  frmManagerXP2.LogIt('  подтверждение транзакции...');
  q.transaction.Commit;
  frmManagerXP2.LogIt('  подтверждение транзакции - ОК');
 except
  q.transaction.rollback;
  frmManagerXP2.LogIt('  Ошибка: '+GLEM);
 end;
 finally
  screen.cursor:=crDefault;
  fs.free;
  q.free;
  db.Free;
 end;
end;

procedure SyncOff;
begin
  frmManagerXP2.LogIt('Отключение синхронизации...');
  if fileexists(SYSTEM_PHP+'.cowa') then raise('Ошибка отключения синхронизации(найден файл "'+SYSTEM_PHP+'.cowa").'#13#10'Возможно процесс сжатия товаров уже запущен.');
  if not fileexists(SYSTEM_PHP) then raise('Отключение невозможно. Файл "'+SYSTEM_PHP+'" - не найден');
  movefile(SYSTEM_PHP,SYSTEM_PHP+'.cowa');
  if not fileexists(SYSTEM_PHP+'.cowa') then raise('Ошибка переименования "'+SYSTEM_PHP+'" в '+SYSTEM_PHP+'.cowa');
  stringtofile(PHP_TEXT,SYSTEM_PHP);
  if not fileexists(SYSTEM_PHP) then raise('Ошибка создания файла "'+SYSTEM_PHP+'"!');
  frmManagerXP2.LogIt('Отключение синхронизации - ОК');
end;


procedure SyncOn;
begin
  frmManagerXP2.LogIt('Включение синхронизации...');
  if not fileexists(SYSTEM_PHP+'.cowa') then raise('Ошибка включения синхронизации(не найден файл "'+SYSTEM_PHP+'.cowa").');
  if not fileexists(SYSTEM_PHP) then raise('Не найден файл "'+SYSTEM_PHP+'!');
  if GetFileSize_(SYSTEM_PHP)<>length(PHP_TEXT) then raise('Содержимое файла "'+SYSTEM_PHP+'" - не соответстувет операции.');
  deletefile(SYSTEM_PHP);
  if fileexists(SYSTEM_PHP) then raise('Ошибка удаления файла "'+SYSTEM_PHP+'!');
  movefile(SYSTEM_PHP+'.cowa',SYSTEM_PHP);
  if not fileexists(SYSTEM_PHP) then raise('Ошибка переименования "'+SYSTEM_PHP+'.cowa" в '+SYSTEM_PHP+'!');
  if fileexists(SYSTEM_PHP+'.cowa') then raise('Ошибка переименования "'+SYSTEM_PHP+'.cowa" в '+SYSTEM_PHP+'(найден бэкап)!');
  frmManagerXP2.LogIt('Включение синхронизации - ОК');
end;

procedure CompressWares(bSingle: boolean);
begin
 try
  SyncOff;
//  showmessage('sddfsd');
  ServerCompressWares(bSingle);
 except
  frmManagerXP2.LogIt(GLEM);
  application.MessageBox(GLEM,'Ошибка',MB_ICONSTOP);
 end;
 try
  SyncOn;
 except
  frmManagerXP2.LogIt(GLEM);
  application.MessageBox('Внимание!!!'#13#10'Ошибка запуска синхронизации: '#13#10+GLEM+#13#10'Необходимо восстановить файл system.php вручную!!!','Ошибка',MB_ICONSTOP);
 end;
end;

begin
  if application.MessageBox('Запустить процедуру централизованного сжатия товаров?'#13#10#13#10'Удаление неуникальных товаров с указанием актуального товара, рассылка скриптов всем профилям.','Подтверждение',MB_YESNO+MB_ICONQUESTION)=idYes then
  begin
    CompressWares(false);
    application.MessageBox('Процедура централизованного сжатия товаров выполнена.','Отчет',MB_ICONINFORMATION);
  end;
end;                           

Сетка для обработки запросов товаров

Прикрепленный файл Файл:Запросы товаров.zip