Установка Единого Окна
Материал из wiki.standart-n.ru
Версия от 20:30, 30 ноября 2016; Aleksnick (обсуждение | вклад)
Содержание
- 1 Подготовка серверной базы и скриптов для Единого Окна
- 2 Заблокировать синхронизацию
- 3 Лехина процедура по сжатию варесов засчет пустых значений стран и изготовителей
- 4 Лехина процедура по удалению тильд из наименований
- 5 Лехин триггер, чтобы не пересвязывались партии - НЕ ТЕСТИРОВАЛСЯ!!!
- 6 Скрипт Андрея - НЕ ДЛЯ СЕТИ!!!
- 7 Лешины скрипты - ДЛЯ РАБОТЫ МЕНЕДЖЕРА С ЕДИНЫМ ОКНОМ
- 8 Славины скрипты - ДЛЯ СИНХРОНИЗАЦИИ И СЖАТИЯ ТОВАРОВ
Подготовка серверной базы и скриптов для Единого Окна
Подготовка серверной базы и скриптов для Единого Окна
Заблокировать синхронизацию
Заблокировать синхронизацию, но, чтоб задания (g$tasks) выполнялись
Лехина процедура по сжатию варесов засчет пустых значений стран и изготовителей
SET TERM ^ ; create or alter procedure PR_COMPRESS_WARES returns ( OUT DM_UUID_NULL) as declare variable WARE_ID DM_UUID; declare variable NAME_ID DM_UUID; declare variable IZG_ID DM_UUID; declare variable COUNTRY_ID DM_UUID; declare variable BARCODE DM_TEXT; declare variable NEW_VALUE DM_UUID_NULL; declare variable NULL_VALUE DM_UUID_NULL; begin /* заполняем страну */ null_value = null; select v.id from vals v where v.alttype=0 and V.VTYPE = 2 and v.svalue='' into :null_value; for select W.ID, W.NAME_ID, W.IZG_ID, W.COUNTRY_ID, W.BARCODE from WARES W where w.country_id=:null_value into :WARE_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :BARCODE do begin NEW_VALUE = null; select first 1 W.COUNTRY_ID from WARES W where 1 = 1 and W.NAME_ID = :NAME_ID and ((W.IZG_ID = :IZG_ID) or (W.BARCODE = :BARCODE)) and W.COUNTRY_ID <> :COUNTRY_ID order by W.INSERTDT desc into :NEW_VALUE; if (:NEW_VALUE is not null) then update WARES W set W.COUNTRY_ID = :NEW_VALUE where W.ID = :WARE_ID; end /* заполняем изготовителя */ null_value = null; select v.id from vals v where v.alttype=0 and V.VTYPE = 3 and v.svalue='' into :null_value; for select W.ID, W.NAME_ID, W.IZG_ID, W.COUNTRY_ID, W.BARCODE from WARES W where w.izg_id=:null_value into :WARE_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :BARCODE do begin NEW_VALUE = null; select first 1 W.IZG_ID from WARES W where 1 = 1 and W.NAME_ID = :NAME_ID and W.IZG_ID <> :IZG_ID and ((W.COUNTRY_ID = :COUNTRY_ID) or (W.BARCODE = :BARCODE)) order by W.INSERTDT desc into :NEW_VALUE; if (:NEW_VALUE is not null) then update WARES W set W.IZG_ID = :NEW_VALUE where W.ID = :WARE_ID; end /* заполняем штрихкод */ for select W.ID, W.NAME_ID, W.IZG_ID, W.COUNTRY_ID, W.BARCODE from WARES W where w.barcode = '' into :WARE_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :BARCODE do begin NEW_VALUE = null; select first 1 W.barcode from WARES W where 1 = 1 and W.NAME_ID = :NAME_ID and W.barcode <> :barcode and ((W.COUNTRY_ID = :COUNTRY_ID) or (W.izg_id = :izg_id)) order by W.INSERTDT desc into :NEW_VALUE; if (:NEW_VALUE is not null) then update WARES W set W.barcode = :NEW_VALUE where W.ID = :WARE_ID; end suspend; end^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT ON VALS TO PROCEDURE PR_COMPRESS_WARES; GRANT SELECT,UPDATE ON WARES TO PROCEDURE PR_COMPRESS_WARES; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE PR_COMPRESS_WARES TO SYSDBA;
Лехина процедура по удалению тильд из наименований
SET TERM ^ ; create or alter procedure PR_MAKE_AWESOME_SNAME returns ( S DM_TEXT) as declare variable I DM_ID; declare variable K DM_METADATANAME; declare variable AWESOME_SNAME DM_TEXT; declare variable OLD_ID DM_UUID; declare variable NEW_ID DM_ID_NULL; begin for select /*first 10000*/ distinct w.name_id, trim(v.svalue) from wares w left join vals v on v.ID = w.name_id and v.vtype=1 where (v.svalue starting with ' ') or (v.svalue starting with '@') or (v.svalue starting with '~') into :old_ID, :AWESOME_SNAME do begin new_id = null; I = 110; K = ''; while (I > 1) do begin K = '~' || :I; I = :I - 1; AWESOME_SNAME = replace(:AWESOME_SNAME, trim(:K || '_'), ''); AWESOME_SNAME = replace(:AWESOME_SNAME, trim(:K), ''); end AWESOME_SNAME = replace(:AWESOME_SNAME, '~', ''); AWESOME_SNAME = replace(:AWESOME_SNAME, '@', ''); AWESOME_SNAME = replace(:AWESOME_SNAME, '_', ''); AWESOME_SNAME = replace(:AWESOME_SNAME, '''', ''); AWESOME_SNAME = replace(:AWESOME_SNAME, '"', ''); AWESOME_SNAME = replace(:AWESOME_SNAME, ' ', ' '); select v.id from vals v where v.vtype=1 and v.svalue=:AWESOME_SNAME into :new_id; if (:new_id is null) then update vals v set v.svalue=:AWESOME_SNAME where v.id=:old_id; else update wares w set w.name_id=:new_id where w.name_id=:old_id; S = :AWESOME_SNAME; suspend; end end^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT,UPDATE ON WARES TO PROCEDURE PR_MAKE_AWESOME_SNAME; GRANT SELECT,UPDATE ON VALS TO PROCEDURE PR_MAKE_AWESOME_SNAME; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE PR_MAKE_AWESOME_SNAME TO SYSDBA;
Лехин триггер, чтобы не пересвязывались партии - НЕ ТЕСТИРОВАЛСЯ!!!
/******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; SET TERM ^ ; CREATE OR ALTER TRIGGER WARES_BIU_COWA FOR WARES ACTIVE BEFORE INSERT OR UPDATE POSITION 0 AS /* заносим все поля в таблице wares в переменные */ declare variable ID type of DM_UUID; declare variable NAME_ID type of DM_UUID; declare variable IZG_ID type of DM_UUID; declare variable COUNTRY_ID type of DM_UUID; declare variable ORIG_CODE type of DM_TEXT; declare variable ORIG_NAME_ID type of DM_UUID; declare variable ORIG_IZG_ID type of DM_UUID; declare variable ORIG_COUNTRY_ID type of DM_UUID; declare variable BARCODE type of DM_TEXT; declare variable Z_ID type of DM_ID; declare variable SKLAD_ID type of DM_TEXT; declare variable INSERTDT type of DM_DATETIME; declare variable PACKET type of DM_ID; declare variable L_ID type of DM_UUID; declare variable cur_id type of DM_UUID; declare variable D$SRVUPDDT type of DM_DATETIME; declare variable SNAME type of DM_TEXT1024; declare variable MGN_NAME type of DM_TEXT1024; declare variable MGN_ID type of DM_ID_NULL; declare variable MGN_SOURCE type of DM_TEXT1024; declare variable WHASH type of DM_ID; declare variable NEW_COUNTRY_ID type of DM_UUID_NULL; declare variable NEW_IZG_ID type of DM_UUID_NULL; begin /* находим тот варес, который уже есть в базе */ 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, D$UUID, D$SRVUPDDT, SNAME, MGN_NAME, MGN_ID, MGN_SOURCE, WHASH 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 :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, :cur_id, :D$SRVUPDDT, :SNAME, :MGN_NAME, :MGN_ID, :MGN_SOURCE, :WHASH; /* если ничего не нашли, то выходим */ if (:cur_id is null) then exit; /* это условие я тоже не понял */ --if (not exists(select id from wares_log where id=:new.id)) then --begin --end /* вот это я не понял что вообще значит */ update wares_log set ACTUAL_WARE_ID=new.d$uuid where ACTUAL_WARE_ID=:cur_id; /* делаем связку нового товара на текущий варес в базе */ insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,ACTUAL_WARE_ID) values (new.ID,new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.ORIG_CODE,new.ORIG_NAME_ID,new.ORIG_IZG_ID,new.ORIG_COUNTRY_ID,new.BARCODE,new.Z_ID,new.SKLAD_ID,new.INSERTDT,new.PACKET,new.L_ID,:cur_id); /* !!! */ /* подменяем новые значения на старые, */ /* чтобы после выхода из триггера у нас снова создался ба старый варес */ /* соответственно со старым d$uuid */ /* и нам не нужно пересвязывать партии и другие таблицы */ /* !!! */ new.id=:ID; new.name_id=:NAME_ID; new.izg_id=:IZG_ID; new.country_id=:COUNTRY_ID; new.orig_code=:ORIG_CODE; new.orig_name_id=:ORIG_NAME_ID; new.orig_izg_id=:ORIG_IZG_ID; new.orig_country_id=:ORIG_COUNTRY_ID; new.barcode=:BARCODE; new.z_id=:Z_ID; new.sklad_id=:SKLAD_ID; new.insertdt=:INSERTDT; new.packet=:PACKET; new.l_id=:L_ID; new.d$uuid=:cur_id; new.d$srvupddt=:D$SRVUPDDT; new.sname=:SNAME; new.mgn_name=:MGN_NAME; new.mgn_name=:MGN_ID; new.mgn_name=:MGN_SOURCE; new.whash=:WHASH; /* удаляем старый варес. */ /* по выходу из триггера он снова будет создан */ delete from wares where d$uuid=:cur_id; /* код старого триггера */ /* AS declare variable cur_id type of DM_UUID; begin select d$uuid from wares where NAME_ID=new.name_id and IZG_ID=new.izg_id and COUNTRY_ID=new.COUNTRY_ID and BARCODE=new.BARCODE and d$uuid<>new.d$uuid into :cur_id; if (cur_id is null) then exit; if (not exists(select id from wares_log where id=:cur_id and actual_ware_id=new.id)) then begin update wares_log set ACTUAL_WARE_ID=new.d$uuid where ACTUAL_WARE_ID=:cur_id; insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,ACTUAL_WARE_ID) select ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,new.d$uuid from wares where d$uuid=:cur_id; end update parts set ware_id=new.d$uuid where ware_id=:cur_id; update doc_detail_active set ware_id=new.d$uuid where ware_id=:cur_id; delete from wares where d$uuid=:cur_id; end */ end ^ SET TERM ; ^
Скрипт Андрея - НЕ ДЛЯ СЕТИ!!!
Сжатие товаров в одиночной аптеки перед внедрением единого окна
Лешины скрипты - ДЛЯ РАБОТЫ МЕНЕДЖЕРА С ЕДИНЫМ ОКНОМ
create or alter procedure PR_AUTO_WARESLINK ( DOC_ID DM_ID_NULL, ACTIVE_ID DM_ID_NULL) as declare variable DDA_BARCODE DM_TEXT1024; declare variable DDA_WARE_ID DM_UUID_NULL; declare variable DDA_ORIG_COUNTRYID DM_UUID_NULL; declare variable DDA_ORIG_IZGID DM_UUID_NULL; declare variable DDA_ORIG_NAMEID DM_UUID_NULL; declare variable DDA_COUNTRY_ID DM_UUID_NULL; declare variable DDA_IZG_ID DM_UUID_NULL; declare variable DDA_NAME_ID DM_UUID_NULL; declare variable DDA_Z_ID DM_UUID_NULL; declare variable DDA_SKLAD_ID DM_UUID_NULL; declare variable DDALOG_BARCODE DM_TEXT1024; declare variable DDALOG_WARE_ID DM_UUID_NULL; declare variable DDALOG_ORIG_COUNTRYID DM_UUID_NULL; declare variable DDALOG_ORIG_IZGID DM_UUID_NULL; declare variable DDALOG_ORIG_NAMEID DM_UUID_NULL; declare variable DDALOG_COUNTRY_ID DM_UUID_NULL; declare variable DDALOG_IZG_ID DM_UUID_NULL; declare variable DDALOG_NAME_ID DM_UUID_NULL; declare variable DDALOG_Z_ID DM_UUID_NULL; declare variable DDALOG_SKLAD_ID DM_UUID_NULL; declare variable DDALOG2_BARCODE DM_TEXT1024; declare variable DDALOG2_WARE_ID DM_UUID_NULL; declare variable DDALOG2_ORIG_COUNTRYID DM_UUID_NULL; declare variable DDALOG2_ORIG_IZGID DM_UUID_NULL; declare variable DDALOG2_ORIG_NAMEID DM_UUID_NULL; declare variable DDALOG2_COUNTRY_ID DM_UUID_NULL; declare variable DDALOG2_IZG_ID DM_UUID_NULL; declare variable DDALOG2_NAME_ID DM_UUID_NULL; declare variable DDALOG2_Z_ID DM_UUID_NULL; declare variable DDALOG2_SKLAD_ID DM_UUID_NULL; begin select BCODE_IZG, WARE_ID, (select VAL_ID from PR_GETVAL_ID(DDA.SNAME, 0, DDA.PART_TYPE)) as NAME_ID, (select VAL_ID from PR_GETVAL_ID(DDA.SIZG, 3, DDA.PART_TYPE)) as IZG_ID, (select VAL_ID from PR_GETVAL_ID(DDA.SCOUNTRY, 2, DDA.PART_TYPE)) as COUNTRY_ID, (select VAL_ID from PR_GETVAL_ID(DDA.SORIG_NAME, 1, DDA.PART_TYPE)) as ORIG_NAME_ID, (select VAL_ID from PR_GETVAL_ID(DDA.SORIG_IZG, 6, DDA.PART_TYPE)) as ORGI_IZG_ID, (select VAL_ID from PR_GETVAL_ID(DDA.SORIG_COUNTRY, 5, DDA.PART_TYPE)) as ORIG_COUNTRY_ID, Z_ID, SKLAD_ID from DOC_DETAIL_ACTIVE DDA where DOC_ID = :DOC_ID and ID = :ACTIVE_ID into DDA_BARCODE, DDA_WARE_ID, DDA_NAME_ID, DDA_IZG_ID, DDA_COUNTRY_ID, DDA_ORIG_NAMEID, DDA_ORIG_IZGID, DDA_ORIG_COUNTRYID, DDA_Z_ID, DDA_SKLAD_ID; select first 1 BCODE_IZG, WARE_ID, (select VAL_ID from PR_GETVAL_ID(DDAL.SNAME, 0, DDAL.PART_TYPE)) as NAME_ID, (select VAL_ID from PR_GETVAL_ID(DDAL.SIZG, 3, DDAL.PART_TYPE)) as IZG_ID, (select VAL_ID from PR_GETVAL_ID(DDAL.SCOUNTRY, 2, DDAL.PART_TYPE)) as COUNTRY_ID, (select VAL_ID from PR_GETVAL_ID(DDAL.SORIG_NAME, 1, DDAL.PART_TYPE)) as ORIG_NAME_ID, (select VAL_ID from PR_GETVAL_ID(DDAL.SORIG_IZG, 6, DDAL.PART_TYPE)) as ORGI_IZG_ID, (select VAL_ID from PR_GETVAL_ID(DDAL.SORIG_COUNTRY, 5, DDAL.PART_TYPE)) as ORIG_COUNTRY_ID, Z_ID, SKLAD_ID from DOC_DETAIL_ACTIVE_LOG DDAL where DOC_ID = :DOC_ID and ID = :ACTIVE_ID order by LOG_ID into DDALOG_BARCODE, DDALOG_WARE_ID, DDALOG_NAME_ID, DDALOG_IZG_ID, DDALOG_COUNTRY_ID, DDALOG_ORIG_NAMEID, DDALOG_ORIG_IZGID, DDALOG_ORIG_COUNTRYID, DDALOG_Z_ID, DDALOG_SKLAD_ID; select first 1 skip 1 BCODE_IZG, WARE_ID, (select VAL_ID from PR_GETVAL_ID(DDAL.SNAME, 0, DDAL.PART_TYPE)) as NAME_ID, (select VAL_ID from PR_GETVAL_ID(DDAL.SIZG, 3, DDAL.PART_TYPE)) as IZG_ID, (select VAL_ID from PR_GETVAL_ID(DDAL.SCOUNTRY, 2, DDAL.PART_TYPE)) as COUNTRY_ID, (select VAL_ID from PR_GETVAL_ID(DDAL.SORIG_NAME, 1, DDAL.PART_TYPE)) as ORIG_NAME_ID, (select VAL_ID from PR_GETVAL_ID(DDAL.SORIG_IZG, 6, DDAL.PART_TYPE)) as ORGI_IZG_ID, (select VAL_ID from PR_GETVAL_ID(DDAL.SORIG_COUNTRY, 5, DDAL.PART_TYPE)) as ORIG_COUNTRY_ID, Z_ID, SKLAD_ID from DOC_DETAIL_ACTIVE_LOG DDAL where DOC_ID = :DOC_ID and ID = :ACTIVE_ID order by LOG_ID into DDALOG2_BARCODE, DDALOG2_WARE_ID, DDALOG2_NAME_ID, DDALOG2_IZG_ID, DDALOG2_COUNTRY_ID, DDALOG2_ORIG_NAMEID, DDALOG2_ORIG_IZGID, DDALOG2_ORIG_COUNTRYID, DDALOG2_Z_ID, DDALOG2_SKLAD_ID; if (((DDA_WARE_ID <> '-1') and (DDA_WARE_ID <> '0') and (DDA_WARE_ID is not null)) and ((DDALOG2_WARE_ID <> '-1') and (DDALOG2_WARE_ID <> '0') and (DDALOG2_WARE_ID is not null)) and (DDA_BARCODE = DDALOG2_BARCODE) and (DDA_WARE_ID <> DDALOG2_WARE_ID)) then begin update WARES set NAME_ID = :DDA_NAME_ID, IZG_ID = :DDA_IZG_ID, COUNTRY_ID = :DDA_COUNTRY_ID, ORIG_NAME_ID = :DDA_ORIG_NAMEID, ORIG_IZG_ID = :DDA_ORIG_IZGID, ORIG_COUNTRY_ID = :DDA_ORIG_COUNTRYID, BARCODE = :DDA_BARCODE where ID = :DDALOG2_WARE_ID; exit; end if ((DDALOG_WARE_ID = '0') or (DDALOG_WARE_ID = '-1') or (DDALOG_WARE_ID is null)) then begin insert into WARES_LOG (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, BARCODE, Z_ID, SKLAD_ID) values (:DDA_WARE_ID, :DDALOG_NAME_ID, :DDALOG_IZG_ID, :DDALOG_COUNTRY_ID, :DDALOG_ORIG_NAMEID, :DDALOG_ORIG_IZGID, :DDALOG_ORIG_COUNTRYID, :DDALOG_BARCODE, :DDALOG_Z_ID, :DDALOG_SKLAD_ID); end else if (((DDA_WARE_ID <> '0') and (DDA_WARE_ID <> '-1') and (DDA_WARE_ID is not null)) and ((DDA_NAME_ID <> DDALOG_NAME_ID) or (DDA_IZG_ID <> DDALOG_IZG_ID) or (DDA_COUNTRY_ID <> DDALOG_COUNTRY_ID) or (DDA_ORIG_NAMEID <> DDALOG_ORIG_NAMEID) or (DDA_ORIG_IZGID <> DDALOG_ORIG_IZGID) or (DDA_ORIG_COUNTRYID <> DDALOG_ORIG_COUNTRYID)) and (DDA_BARCODE = DDALOG_BARCODE)) then begin update WARES set NAME_ID = :DDA_NAME_ID, IZG_ID = :DDA_IZG_ID, COUNTRY_ID = :DDA_COUNTRY_ID, ORIG_NAME_ID = :DDA_ORIG_NAMEID, ORIG_IZG_ID = :DDA_ORIG_IZGID, ORIG_COUNTRY_ID = :DDA_ORIG_COUNTRYID, BARCODE = :DDA_BARCODE where ID = :DDALOG_WARE_ID; end end; 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 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) as declare variable WARE_ID type of DM_UUID_NULL; declare variable NAME_ID type of DM_UUID_NULL; declare variable ORIG_NAME_ID type of DM_UUID_NULL; declare variable IZG_ID type of DM_UUID_NULL; declare variable ORIG_IZG_ID type of DM_UUID_NULL; declare variable COUNTRY_ID type of DM_UUID_NULL; declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL; begin /* select val_id from pr_getval_id(:sname,0,:alttype) into :name_id; select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id; select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id; select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id; select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id; select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id; */ select ID from VALS where VTYPE = 0 and ALTTYPE = :ALTTYPE and SVALUE = :SNAME into :NAME_ID; select ID from VALS where VTYPE = 1 and ALTTYPE = :ALTTYPE and SVALUE = :SORIG_NAME into :ORIG_NAME_ID; select ID from VALS where VTYPE = 3 and ALTTYPE = :ALTTYPE and SVALUE = :SIZG into :IZG_ID; select ID from VALS where VTYPE = 6 and ALTTYPE = :ALTTYPE and SVALUE = :SORIG_IZG into :ORIG_IZG_ID; select ID from VALS where VTYPE = 2 and ALTTYPE = :ALTTYPE and SVALUE = :SCOUNTRY into :COUNTRY_ID; select ID from VALS where VTYPE = 5 and ALTTYPE = :ALTTYPE and SVALUE = :SORIG_COUNTRY into :ORIG_COUNTRY_ID; select WARE_ID from PR_GETWARE_BY_IDS(:NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, 0, 1, :BARCODE) into :WARE_ID; if (WARE_ID is not null) then begin /* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */ select SNAME, SIZG, SCOUNTRY, BARCODE from VW_WARES where ID = :WARE_ID into :GOODNAME, :GOODIZG, :GOODCOUNTRY, :GOODBARCODE; /* if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then */ /* insert into doc_detail_active_log(id,sname,sizg,scountry,part_type) values (:active_id,:sname,:sizg,:scountry,:alttype); */ /* goodname='2';--sname; */ if (GOODNAME is null) then GOODNAME = SNAME; if (GOODIZG is null) then GOODIZG = SIZG; if (GOODCOUNTRY is null) then GOODCOUNTRY = SCOUNTRY; if (GOODNAME is null) then GOODNAME = SORIG_NAME; if (GOODIZG is null) then GOODIZG = SORIG_IZG; if (GOODCOUNTRY is null) then GOODCOUNTRY = SORIG_COUNTRY; if (GOODBARCODE is null) then GOODBARCODE = BARCODE; suspend; exit; end select WARE_ID from PR_GETWARE_BY_IDS(:NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, 0, null, :BARCODE) into :WARE_ID; if (WARE_ID is not null) then begin /* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */ /* goodname='1';--sname; */ GOODNAME = SNAME; GOODIZG = SIZG; GOODCOUNTRY = SCOUNTRY; GOODBARCODE = BARCODE; suspend; exit; end WARE_ID = null; if (ORIG_NAME_ID is not null) then begin select first 1 ID from WARES where ORIG_NAME_ID = :ORIG_NAME_ID order by 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; 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; 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_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 */ /* insert into doc_detail_active_log(id,sname,sizg,scountry, part_type) values (:active_id,:sname,:sizg,:scountry,:alttype); */ /* goodname='3'; */ GOODBARCODE = BARCODE; if (GOODNAME is null) then GOODNAME = SNAME; if (GOODIZG is null) then GOODIZG = SIZG; if (GOODCOUNTRY is null) then GOODCOUNTRY = SCOUNTRY; if (GOODNAME is null) then GOODNAME = SORIG_NAME; if (GOODIZG is null) then GOODIZG = SORIG_IZG; if (GOODCOUNTRY is null) then GOODCOUNTRY = SORIG_COUNTRY; suspend; end; create or alter procedure PR_GET_WARE ( SNAME type of DM_TEXT, SIZG type of DM_TEXT, SCOUNTRY type of DM_TEXT, ORIG_CODE type of DM_TEXT = '', SORIG_NAME type of DM_TEXT = '', SORIG_IZG type of DM_TEXT = '', SORIG_COUNTRY type of DM_TEXT = '', BARCODE type of DM_TEXT = '', Z_ID type of DM_ID = 0, SKLAD_ID DM_TEXT = '', ALTTYPE DM_STATUS = 0, MNN DM_TEXT = '') returns ( W_ID type of DM_UUID_NULL) as declare variable NAME_ID type of DM_UUID_NULL; declare variable IZG_ID type of DM_UUID_NULL; declare variable COUNTRY_ID type of DM_UUID_NULL; declare variable ORIG_NAME_ID type of DM_UUID_NULL; declare variable ORIG_IZG_ID type of DM_UUID_NULL; declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL; begin select VAL_ID from PR_GETVAL_ID(:SNAME, 0, :ALTTYPE, :MNN) into :NAME_ID; select VAL_ID from PR_GETVAL_ID(:SIZG, 3, :ALTTYPE) into :IZG_ID; select VAL_ID from PR_GETVAL_ID(:SCOUNTRY, 2, :ALTTYPE) into :COUNTRY_ID; select VAL_ID from PR_GETVAL_ID(:SORIG_NAME, 1, :ALTTYPE) into :ORIG_NAME_ID; select VAL_ID from PR_GETVAL_ID(:SORIG_IZG, 6, :ALTTYPE) into :ORIG_IZG_ID; select VAL_ID from PR_GETVAL_ID(:SORIG_COUNTRY, 5, :ALTTYPE) into :ORIG_COUNTRY_ID; /* select first 1 id from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id -- into :w_id; and barcode=:barcode into :w_id; */ select ID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and BARCODE = :BARCODE into :W_ID; if (W_ID is null) then begin /* exception EX_WRONG_OPER; */ /* w_id=gen_id(gen_wares_id,1); */ W_ID = uuid_to_char(gen_uuid()); insert into WARES (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, BARCODE, Z_ID, SKLAD_ID) values (:W_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, :BARCODE, :Z_ID, :SKLAD_ID); end suspend; end; create or alter procedure PR_GETWARE_BY_IDS ( NAME_ID type of DM_UUID_NULL, IZG_ID type of DM_UUID_NULL, COUNTRY_ID type of DM_UUID_NULL, ORIG_CODE type of DM_TEXT, ORIG_NAME_ID type of DM_UUID_NULL, ORIG_IZG_ID type of DM_UUID_NULL, ORIG_COUNTRY_ID type of DM_UUID_NULL, DOINSERT type of DM_STATUS, SEARCHINLOGS type of DM_STATUS, BARCODE DM_TEXT) returns ( WARE_ID type of DM_UUID_NULL) as begin if (ORIG_CODE is null) then ORIG_CODE = ''; if (SEARCHINLOGS is null) then SEARCHINLOGS = 0; if (SEARCHINLOGS = 1) then begin select first 1 iif(ACTUAL_WARE_ID is null, ID, ACTUAL_WARE_ID) from WARES_LOG where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and BARCODE = :BARCODE order by LOG_INSERTDT desc into :WARE_ID; 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 ( DOC_ID type of DM_ID, SESSION_ID type of DM_ID) as declare variable WRONG_WARES DM_STATUS; declare variable BASE_TYPE type of DM_STATUS; declare variable SUMMA type of DM_DOUBLE; declare variable SUMMA_O type of DM_DOUBLE; declare variable SUM_NDSO type of DM_DOUBLE; declare variable DSCSUMMA type of DM_DOUBLE; declare variable SUM_DSC type of DM_DOUBLE; declare variable PRICE_TYPE DM_STATUS; declare variable CONTRACT_ID DM_ID; declare variable W_PARAM DM_STATUS; declare variable WARES_MODE DM_STATUS; begin select DT.BASE_TYPE, D.PRICE_TYPE, D.CONTRACT_ID from DOCS D left join DOC_TYPES DT on D.DOC_TYPE = DT.ID where D.ID = :DOC_ID into :BASE_TYPE, :PRICE_TYPE, :CONTRACT_ID; if (BASE_TYPE in (1, 3, 6, 8)) then begin WRONG_WARES = 0; WARES_MODE = 1; select PARAM_VALUE from PR_GETPARAMVALUE('WARES_MODE', 0) into WARES_MODE; if (WARES_MODE = 0) then begin select count(ID) from DOC_DETAIL_ACTIVE where DOC_ID = :DOC_ID and ((WARE_ID = '0') or (WARE_ID = '-1') or (WARE_ID = '')) into WRONG_WARES; if (WRONG_WARES > 0) then exception EX_WRONG_OPER; end execute procedure PR_DOC_PRIHOD_COMMIT(:DOC_ID, 0); end else if (BASE_TYPE in (2, 7, 9)) then execute procedure PR_DOC_RASHOD_COMMIT(:DOC_ID, 0); else exception EX_WRONGDOC_BASE_TYPE; delete from DOC_DETAIL_ACTIVE where DOC_ID = :DOC_ID; if (BASE_TYPE = 3) then begin delete from WAREBASE where PART_ID in (select PART_ID from DOC_DETAIL where DOC_ID = :DOC_ID and QUANT < 0 and PART_TYPE = 1); end select sum(SUMMA), sum(SUMMA_O), sum(SUM_NDSO), sum(PRICE * QUANT / 10000), sum(SUM_DSC) from DOC_DETAIL where DOC_ID = :DOC_ID into :SUMMA, :SUMMA_O, :SUM_NDSO, :DSCSUMMA, :SUM_DSC; DSCSUMMA = DSCSUMMA * 10000; update DOCS set COMMITSESSION_ID = :SESSION_ID, STATUS = 1, POSTDT = 'now', CHECKDATA = (select CHECKDATA from PR_DOCCHECKDATA(:DOC_ID)), SUMMA = :SUMMA, SUMMA_O = :SUMMA_O, SUM_NDSO = :SUM_NDSO, CALCSUMMA = :DSCSUMMA, SUM_DSC = :SUM_DSC, CASHDATA = (iif(:BASE_TYPE = 1, -:SUMMA, null)) where ID = :DOC_ID; execute procedure PR_UPDBLOCKINFO_BY_DOC(:DOC_ID, 1); /* if ((base_type = 2) and (contract_id<>0)) then */ if (CONTRACT_ID <> 0) then begin if (BASE_TYPE = 2) then execute procedure PR_CREDITCOMMIT(:CONTRACT_ID); if (BASE_TYPE in (8, 9)) then execute procedure PR_CREDITREINITCOMMIT(:CONTRACT_ID, :SESSION_ID); /* execute procedure PR_CREDITCOMMIT(:contract_id); */ end execute procedure PR_DOEVENT('EV_DOCCOMMIT'); end; create or alter trigger DOC_DETAIL_ACTIVE_BI for DOC_DETAIL_ACTIVE active before insert position 0 as begin if (new.ID is null) then new.ID = gen_id(GEN_DOC_DETAIL_ACTIVE_ID, 1); new.INSERTDT = 'now'; if (new.BASE_AGENT_ID is null) then new.BASE_AGENT_ID = 0; if (new.STATUS is null) then new.STATUS = 0; if (new.PARENT_ID is null) then new.PARENT_ID = 0; if (new.PART_TYPE is null) then new.PART_TYPE = 0; /* первая строчка = до привидения, оригианльные значения накладной */ /* select first 1 ID from WARES where NAME_ID = new.NAME_ID and IZG_ID = new.IZG_ID and COUNTRY_ID = new.COUNTRY_ID and BARCODE = new.BCODE_IZG into new.WARE_ID; */ insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT, DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE, BARCODE1, BCODE_IZG, GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, Z_ID, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW, PART_TYPE, BASE_AGENT_ID) values (new.ID, new.PARENT_ID, new.DOC_ID, new.PART_ID, new.PART_PARENT_ID, new.DOC_DETAIL_ID, new.PRICE, new.NAC, new.QUANT, new.DISCOUNT, new.SUMMA, new.SUMMA_O, new.DCARD, new.WARE_ID, new.PRICE_O, new.PRICE_Z, new.PRICE_R, new.BARCODE, new.BARCODE1, new.BCODE_IZG, new.GODENDO, new.SERIA, new.NDS, new.SUM_NDSO, new.SERT, new.DATESERT, new.KEMVSERT, new.SDSERT, new.REGN, new.NGTD, new.EDIZM, new.NAME_ID, new.IZG_ID, new.COUNTRY_ID, new.ORIG_CODE, new.ORIG_NAME_ID, new.ORIG_IZG_ID, new.ORIG_COUNTRY_ID, new.Z_ID, new.SKLAD_ID, new.SNAME, new.SIZG, new.SCOUNTRY, new.SORIG_NAME, new.SORIG_IZG, new.SORIG_COUNTRY, new.INSERTDT, new.INFO, new.KOEF, new.MOTHERPART_ID, new.DEP, new.SUM_DSC, new.HUMAN_QUANT, new.CUSTOMDRAW, new.PART_TYPE, new.BASE_AGENT_ID); /* -- */ if ((new.PART_ID is null) or (new.PART_ID = 0)) then begin /* >cf20110923 */ /* if (new.ware_id<0) then */ if (new.WARE_ID = '-1') then begin select GOODNAME, GOODIZG, GOODCOUNTRY, GOODBARCODE from PR_MAKEGOODWAREVALUES(new.SNAME, new.SORIG_NAME, new.SIZG, new.SORIG_IZG, new.SCOUNTRY, new.SORIG_COUNTRY, new.ORIG_CODE, new.ID, new.PART_TYPE, new.BCODE_IZG) into new.SNAME, new.SIZG, new.SCOUNTRY, new.BCODE_IZG; end /* <cf20110923 */ new.PART_ID = 0; select ID from VALS where VTYPE = 0 and ALTTYPE = new.PART_TYPE and SVALUE = new.SNAME into new.NAME_ID; select ID from VALS where VTYPE = 3 and ALTTYPE = new.PART_TYPE and SVALUE = new.SIZG into new.IZG_ID; select ID from VALS where VTYPE = 2 and ALTTYPE = new.PART_TYPE and SVALUE = new.SCOUNTRY into new.COUNTRY_ID; select ID from VALS where VTYPE = 1 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_NAME into new.ORIG_NAME_ID; select ID from VALS where VTYPE = 6 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_IZG into new.ORIG_IZG_ID; select ID from VALS where VTYPE = 5 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_COUNTRY into new.ORIG_COUNTRY_ID; new.WARE_ID = 0; select first 1 ID from WARES where NAME_ID = new.NAME_ID and IZG_ID = new.IZG_ID and COUNTRY_ID = new.COUNTRY_ID and BARCODE = new.BCODE_IZG into new.WARE_ID; /* вторая строчка = после приведения */ insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT, DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE, BARCODE1, BCODE_IZG, GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, Z_ID, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW, PART_TYPE, BASE_AGENT_ID) values (new.ID, new.PARENT_ID, new.DOC_ID, new.PART_ID, new.PART_PARENT_ID, new.DOC_DETAIL_ID, new.PRICE, new.NAC, new.QUANT, new.DISCOUNT, new.SUMMA, new.SUMMA_O, new.DCARD, new.WARE_ID, new.PRICE_O, new.PRICE_Z, new.PRICE_R, new.BARCODE, new.BARCODE1, new.BCODE_IZG, new.GODENDO, new.SERIA, new.NDS, new.SUM_NDSO, new.SERT, new.DATESERT, new.KEMVSERT, new.SDSERT, new.REGN, new.NGTD, new.EDIZM, new.NAME_ID, new.IZG_ID, new.COUNTRY_ID, new.ORIG_CODE, new.ORIG_NAME_ID, new.ORIG_IZG_ID, new.ORIG_COUNTRY_ID, new.Z_ID, new.SKLAD_ID, new.SNAME, new.SIZG, new.SCOUNTRY, new.SORIG_NAME, new.SORIG_IZG, new.SORIG_COUNTRY, new.INSERTDT, new.INFO, new.KOEF, new.MOTHERPART_ID, new.DEP, new.SUM_DSC, new.HUMAN_QUANT, new.CUSTOMDRAW, new.PART_TYPE, new.BASE_AGENT_ID); /* -- */ end if (new.PART_PARENT_ID is null) then new.PART_PARENT_ID = 0; if (new.WARE_ID is null) then new.WARE_ID = 0; if (new.NAME_ID is null) then new.NAME_ID = 0; if (new.IZG_ID is null) then new.IZG_ID = 0; if (new.COUNTRY_ID is null) then new.COUNTRY_ID = 0; if (new.ORIG_NAME_ID is null) then new.ORIG_NAME_ID = 0; if (new.ORIG_IZG_ID is null) then new.ORIG_IZG_ID = 0; if (new.ORIG_COUNTRY_ID is null) then new.ORIG_COUNTRY_ID = 0; if (new.Z_ID is null) then new.Z_ID = 0; if (new.SKLAD_ID is null) then new.SKLAD_ID = ''; if (new.MOTHERPART_ID is null) then new.MOTHERPART_ID = 0; if (new.DOC_DETAIL_ID is null) then new.DOC_DETAIL_ID = 0; if (new.PART_ID > 0) then /* заполняем значения партии */ begin select WARE_ID, PRICE, PRICE_O, PRICE_Z, PRICE_R, /* QUANT, */ /* BARCODE, */ BARCODE1, /* KRITK, */ GODENDO, SERIA, NDS, /* SUM_NDSO, */ SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, NAC, DEP, PART_TYPE, BASE_AGENT_ID, GROUP_ID from PARTS where ID = new.PART_ID into new.WARE_ID, new.PRICE, new.PRICE_O, new.PRICE_Z, new.PRICE_R, /* new.QUANT, */ /* new.BARCODE, */ new.BARCODE1, /* new.KRITK,*/ new.GODENDO, new.SERIA, new.NDS, /* new.SUM_NDSO, */ new.SERT, new.DATESERT, new.KEMVSERT, new.SDSERT, new.REGN, new.NGTD, new.EDIZM, new.NAC, new.DEP, new.PART_TYPE, new.BASE_AGENT_ID, new.GROUP_ID; end if (new.GROUP_ID is null) then new.GROUP_ID = 0; /* if (new.ware_id>0) then -- заполняем значения позиции */ if (new.WARE_ID not in ('-1', '0')) then /* заполняем значения позиции */ begin select WW.NAME_ID, (select SVALUE from VALS where ID = WW.NAME_ID), WW.IZG_ID, (select SVALUE from VALS where ID = WW.IZG_ID), WW.COUNTRY_ID, (select SVALUE from VALS where ID = WW.COUNTRY_ID), WW.ORIG_CODE, WW.ORIG_NAME_ID, (select SVALUE from VALS where ID = WW.ORIG_NAME_ID), WW.ORIG_IZG_ID, (select SVALUE from VALS where ID = WW.ORIG_IZG_ID), WW.ORIG_COUNTRY_ID, (select SVALUE from VALS where ID = WW.ORIG_COUNTRY_ID), WW.BARCODE, WW.Z_ID, WW.SKLAD_ID from WARES WW where WW.ID = new.WARE_ID into new.NAME_ID, new.SNAME, new.IZG_ID, new.SIZG, new.COUNTRY_ID, new.SCOUNTRY, new.ORIG_CODE, new.ORIG_NAME_ID, new.SORIG_NAME, new.ORIG_IZG_ID, new.SORIG_IZG, new.ORIG_COUNTRY_ID, new.SORIG_COUNTRY, new.BCODE_IZG, new.Z_ID, new.SKLAD_ID; end else begin /* if (new.ware_id<0) then */ if (new.WARE_ID = '-1') then begin select GOODNAME, GOODIZG, GOODCOUNTRY from PR_MAKEGOODWAREVALUES(new.SNAME, new.SORIG_NAME, new.SIZG, new.SORIG_IZG, new.SCOUNTRY, new.SORIG_COUNTRY, new.ORIG_CODE, new.ID, new.PART_TYPE, new.BCODE_IZG) into new.SNAME, new.SIZG, new.SCOUNTRY; new.WARE_ID = 0; end end if (new.QUANT is null) then new.QUANT = 0; if (new.PRICE is null) then new.PRICE = 0; if (new.PRICE_O is null) then new.PRICE_O = 0; if (new.NDS is null) then new.NDS = 0; if (new.SUMMA is null) then new.SUMMA = new.QUANT * new.PRICE; if (new.SUM_DSC is null) then new.SUM_DSC = 0; if (new.SUMMA_O is null) then begin new.SUMMA_O = new.QUANT * new.PRICE_O; end else begin if (new.QUANT <> 0) then new.PRICE_O = new.SUMMA_O / new.QUANT; end 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 active before update position 0 as begin /*20160513 if ((new.SNAME <> old.SNAME) or (new.NAME_ID is null) or (new.NAME_ID = '0')) then select VAL_ID from PR_GETVAL_ID(new.SNAME, 0, new.PART_TYPE, new.MNN) into new.NAME_ID; */ if (new.NAME_ID is null) then new.NAME_ID = 0; if ((new.SUMMA is null) and ((new.QUANT <> 0) and (new.PRICE <> 0))) then begin new.SUMMA = new.QUANT * new.PRICE; if (new.SUM_DSC is not null) then new.SUMMA = new.SUMMA - new.SUM_DSC; end if (((new.SUMMA_O = 0) or (new.SUMMA_O is null)) and ((new.QUANT <> 0) and (new.PRICE_O <> 0))) then new.SUMMA_O = new.QUANT * new.PRICE_O; if (new.NAME_ID <> old.NAME_ID) then execute procedure PR_COPYGROUPS(old.NAME_ID, new.NAME_ID); if (new.SUM_DSC is null) then if (new.SUMMA <> old.SUMMA) then begin new.SUM_DSC = (1 + new.DISCOUNT / 100) * 100 * new.SUMMA / (100 + old.DISCOUNT) - 100 * new.SUMMA / (100 + old.DISCOUNT); --new.summa=(1+new.discount/100)*100*old.summa/(100+old.discount); end if (new.SUM_DSC is null) then if (new.DISCOUNT <> old.DISCOUNT) then begin new.SUM_DSC = (1 + new.DISCOUNT / 100) * 100 * old.SUMMA / (100 + old.DISCOUNT) - 100 * old.SUMMA / (100 + old.DISCOUNT); new.SUMMA = (1 + new.DISCOUNT / 100) * 100 * old.SUMMA / (100 + old.DISCOUNT); end select ID from VALS where VTYPE = 0 and ALTTYPE = new.PART_TYPE and SVALUE = new.SNAME into new.NAME_ID; if (new.NAME_ID is null) then new.NAME_ID = 0; select ID from VALS where VTYPE = 3 and ALTTYPE = new.PART_TYPE and SVALUE = new.SIZG into new.IZG_ID; if (new.IZG_ID is null) then new.IZG_ID = 0; select ID from VALS where VTYPE = 2 and ALTTYPE = new.PART_TYPE and SVALUE = new.SCOUNTRY into new.COUNTRY_ID; if (new.COUNTRY_ID is null) then new.COUNTRY_ID = 0; select ID from VALS where VTYPE = 1 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_NAME into new.ORIG_NAME_ID; if (new.ORIG_NAME_ID is null) then new.ORIG_NAME_ID = 0; select ID from VALS where VTYPE = 6 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_IZG into new.ORIG_IZG_ID; if (new.ORIG_IZG_ID is null) then new.ORIG_IZG_ID = 0; select ID from VALS where VTYPE = 5 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_COUNTRY into new.ORIG_COUNTRY_ID; if (new.ORIG_COUNTRY_ID is null) then new.ORIG_COUNTRY_ID = 0; insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT, DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE, BARCODE1, BCODE_IZG, --KRITK, GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, Z_ID, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW, PART_TYPE, BASE_AGENT_ID) values (old.ID, old.PARENT_ID, old.DOC_ID, old.PART_ID, old.PART_PARENT_ID, old.DOC_DETAIL_ID, old.PRICE, old.NAC, old.QUANT, old.DISCOUNT, old.SUMMA, old.SUMMA_O, old.DCARD, old.WARE_ID, old.PRICE_O, old.PRICE_Z, old.PRICE_R, old.BARCODE, old.BARCODE1, old.BCODE_IZG, --old.KRITK, old.GODENDO, old.SERIA, old.NDS, old.SUM_NDSO, old.SERT, old.DATESERT, old.KEMVSERT, old.SDSERT, old.REGN, old.NGTD, old.EDIZM, old.NAME_ID, old.IZG_ID, old.COUNTRY_ID, old.ORIG_CODE, old.ORIG_NAME_ID, old.ORIG_IZG_ID, old.ORIG_COUNTRY_ID, old.Z_ID, old.SKLAD_ID, old.SNAME, old.SIZG, old.SCOUNTRY, old.SORIG_NAME, old.SORIG_IZG, old.SORIG_COUNTRY, old.INSERTDT, old.INFO, old.KOEF, old.MOTHERPART_ID, old.DEP, old.SUM_DSC, old.HUMAN_QUANT, old.CUSTOMDRAW, old.PART_TYPE, old.BASE_AGENT_ID); end; create or alter view VW_WARES ( ID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, ORIG_CODE, ORIG_NAME_ID, SORIG_NAME, ORIG_IZG_ID, SORIG_IZG, ORIG_COUNTRY_ID, SORIG_COUNTRY, BARCODE, Z_ID, SKLAD_ID, INSERTDT, PACKET, MGN_NAME, MGN_ID, MGN_SOURCE) as select W.ID, W.NAME_ID, W.SNAME, --(select svalue from vals where id = w.name_id), W.IZG_ID, (select SVALUE from VALS where ID = W.IZG_ID), W.COUNTRY_ID, (select SVALUE from VALS where ID = W.COUNTRY_ID), W.ORIG_CODE, W.ORIG_NAME_ID, (select SVALUE from VALS where ID = W.ORIG_NAME_ID), W.ORIG_IZG_ID, (select SVALUE from VALS where ID = W.ORIG_IZG_ID), W.ORIG_COUNTRY_ID, (select SVALUE from VALS where ID = W.ORIG_COUNTRY_ID), W.BARCODE, W.Z_ID, W.SKLAD_ID, W.INSERTDT, W.PACKET, W.MGN_NAME, W.MGN_ID, W.MGN_SOURCE from WARES W; 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;
Славины скрипты - ДЛЯ СИНХРОНИЗАЦИИ И СЖАТИЯ ТОВАРОВ
1
set term ^; create trigger WARES_LOG_BI_COWATMP for WARES_LOG active before insert position 0 as begin if (new.SKLAD_ID is null) then new.SKLAD_ID = ''; end^ set term; ^ create table TMP_LOG (SKEY DM_TEXT, SVALUE DM_TEXT); alter table WARES_LOG add ACTUAL_WARE_ID DM_UUID_NULL; create index WARES_LOG_ACTUAL_WARE_ID on WARES_LOG (ACTUAL_WARE_ID); create generator GEN_VALS_CARANTINE_ID; create table VALS_CARANTINE (ID DM_ID not null, CUR_ID DM_UUID, NEW_ID DM_UUID, INSERTDT DM_DATETIME, FROM_PROFILE_ID DM_ID_NULL); set term ^; create or alter trigger VALS_CARANTINE_BI for VALS_CARANTINE active before insert position 0 as begin if (new.ID is null) then new.ID = gen_id(GEN_VALS_CARANTINE_ID, 1); new.INSERTDT = current_timestamp; if (new.FROM_PROFILE_ID is null) then select FROM_PROFILE_ID from G$DISTRIBUTE where UUID = new.NEW_ID into new.FROM_PROFILE_ID; end^ set term ; ^ set term ^ ; create or alter trigger VALS_BIU_DODOUBLES for VALS active before insert or update position 0 as declare variable ID type of DM_UUID; begin select ID from VALS where VTYPE = new.VTYPE and SVALUE = new.SVALUE and ALTTYPE = new.ALTTYPE and D$UUID <> new.D$UUID into :ID; if (ID is null) then exit; /* 0 - приведенное наименование 3 - приведенный производитель 2 - приведенная страна 1 - ориг. наименование 6 - ориг. производитель 5 - ориг. страна Запускает цепочку апдейтов. При изменении wares проверяются дубли, и, в случае нахождения, правятся партии */ if (new.VTYPE = 0) then update WARES set NAME_ID = new.ID where NAME_ID = :ID; else if (new.VTYPE = 3) then update WARES set IZG_ID = new.ID where IZG_ID = :ID; else if (new.VTYPE = 2) then update WARES set COUNTRY_ID = new.ID where COUNTRY_ID = :ID; else if (new.VTYPE = 1) then update WARES set ORIG_NAME_ID = new.ID where ORIG_NAME_ID = :ID; else if (new.VTYPE = 6) then update WARES set ORIG_IZG_ID = new.ID where ORIG_IZG_ID = :ID; else if (new.VTYPE = 5) then update WARES set ORIG_COUNTRY_ID = new.ID where ORIG_COUNTRY_ID = :ID; else exit; insert into VALS_CARANTINE (CUR_ID, NEW_ID) values (:ID, new.ID); delete from VALS where ID = :ID; end^ set term; ^ set term ^; create or alter trigger WARES_BIU_COWA for WARES active before insert or update position 0 as declare variable CUR_ID type of DM_UUID; begin select D$UUID from WARES where NAME_ID = new.NAME_ID and IZG_ID = new.IZG_ID and COUNTRY_ID = new.COUNTRY_ID and BARCODE = new.BARCODE and D$UUID <> new.D$UUID into :CUR_ID; if (CUR_ID is null) then exit; if (not exists(select ID from WARES_LOG where ID = :CUR_ID and ACTUAL_WARE_ID = new.ID)) then begin 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; ^ set term ^; create or alter trigger PARTS_BIU_COWA for PARTS active before insert or update position 0 as declare variable WARE_ID type of DM_UUID; begin if (exists(select ID from WARES where ID = new.WARE_ID)) then exit; /* select max(actual_ware_id) from wares_log where id=new.ware_id into :ware_id; */ select max(ACTUAL_WARE_ID) from WARES_LOG where ID = new.WARE_ID and (exists(select ID from WARES W where W.D$UUID = ACTUAL_WARE_ID)) into :WARE_ID; if (WARE_ID is null) then exit; /* if (exists(select id from wares where d$uuid=:ware_id)) then */ new.WARE_ID = :WARE_ID; end^ set term; ^ set term ^; create or alter trigger WARES_BD_COWA for WARES active before delete position 0 as declare variable WARE_ID type of DM_UUID; begin if (not exists(select ID from PARTS where WARE_ID = old.ID)) then exit; /* select max(actual_ware_id) from wares_log where id=old.id into ware_id; */ select max(ACTUAL_WARE_ID) from WARES_LOG WL where ID = old.ID and (exists(select ID from WARES W where W.ID = WL.ACTUAL_WARE_ID)) into WARE_ID; if (WARE_ID is null) then insert into TMP_LOG (SKEY, SVALUE) values ('Нет ware по acual_ware!', old.ID); else update PARTS set WARE_ID = :WARE_ID where WARE_ID = old.ID; end^ set term; ^ set term ^; create or alter procedure PR_TMP_CHANGEWARES_CONSTR as declare variable UUID type of DM_UUID; declare variable NAME_ID type of DM_UUID; declare variable IZG_ID type of DM_UUID; declare variable COUNTRY_ID type of DM_UUID; declare variable BARCODE type of DM_TEXT; declare variable ACTUAL_UUID type of DM_UUID; declare variable EXISTING_ACTUAL_WARE_ID type of DM_UUID; begin /* delete from wares_log wl where (ACTUAL_WARE_ID is null) and (not exists (select id from wares w where w.id=wl.id)); */ for select min(D$UUID), NAME_ID, IZG_ID, COUNTRY_ID, BARCODE from WARES group by NAME_ID, IZG_ID, COUNTRY_ID, BARCODE having count(1) > 1 into ACTUAL_UUID, NAME_ID, IZG_ID, COUNTRY_ID, BARCODE do begin EXISTING_ACTUAL_WARE_ID = null; select max(ACTUAL_WARE_ID) from WARES_LOG where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and BARCODE = :BARCODE into EXISTING_ACTUAL_WARE_ID; if (EXISTING_ACTUAL_WARE_ID is not null) then begin if (exists(select ID from WARES where D$UUID = :EXISTING_ACTUAL_WARE_ID)) then ACTUAL_UUID = EXISTING_ACTUAL_WARE_ID; end for select D$UUID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and BARCODE = :BARCODE and D$UUID <> :ACTUAL_UUID into UUID do begin insert into WARES_LOG (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, BARCODE, Z_ID, SKLAD_ID, INSERTDT, PACKET, L_ID, ACTUAL_WARE_ID) select ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, BARCODE, Z_ID, SKLAD_ID, INSERTDT, PACKET, L_ID, :ACTUAL_UUID from WARES where D$UUID = :UUID; --на триггере update parts set ware_id=:actual_uuid where ware_id=:uuid; delete from WARES where D$UUID = :UUID; end end end^ set term; ^ set term ^; create or alter trigger WARES_LOG_AI_COWATMP for WARES_LOG active after insert position 0 as begin if (new.ACTUAL_WARE_ID is null) then exit; if (exists(select ID from WARES where ID = new.ACTUAL_WARE_ID)) then delete from WARES where ID = new.ID; else insert into TMP_LOG (SKEY, SVALUE) values ('WARES_LOG_AI_COWATMP', new.ACTUAL_WARE_ID); end^ set term; ^ alter trigger WARES_AD_DISTR inactive; alter trigger WARES_BU_DISTR inactive; alter trigger WARES_LOG_BI_DISTR inactive; alter trigger WARES_LOG_AI0 inactive; alter trigger PARTS_AU0 inactive; alter trigger PARTS_BU_DISTR inactive; alter trigger PARTS_BU_G$SYNC inactive; alter trigger PARTS_BU0 inactive;
4
/* Insert into wares_log */
3
alter trigger PARTS_BU_DISTR active; alter trigger PARTS_BU_G$SYNC active; alter trigger PARTS_BU0 active; alter trigger WARES_LOG_BI_DISTR active; alter trigger WARES_AD_DISTR active; alter trigger WARES_BU_DISTR active; create index WARES_IDX_U on WARES(NAME_ID, IZG_ID, COUNTRY_ID, BARCODE); alter table WARES drop constraint UNQ1_WARES; alter table WARES drop constraint WARES_IDX1; create index WARES_IDX1 on WARES(NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID); drop trigger WARES_LOG_BI_COWATMP; alter trigger WARES_AU0 inactive; alter trigger WARES_BU0 inactive; alter trigger WARES_BIU_COWA inactive; alter trigger WARES_SNAME_BU0 inactive; alter trigger WARES_BU_DISTR inactive;
4
update WARES set BARCODE = '' where BARCODE is null; alter trigger WARES_BU0 active; alter trigger WARES_BIU_COWA active; alter trigger WARES_SNAME_BU0 active; alter trigger WARES_BU_DISTR active; create unique index WARES_IDX_UUID on WARES(D$UUID);
5
execute procedure PR_TMP_CHANGEWARES_CONSTR;
6
drop index WARES_IDX_U; create unique index WARES_IDX_U on WARES (NAME_ID, IZG_ID, COUNTRY_ID, BARCODE); drop trigger WARES_LOG_AI_COWATMP;
7
/* update or insert into wares */
8
ALTER TRIGGER WARES_AU0 ACTIVE; ALTER TRIGGER WARES_LOG_AI0 ACTIVE; ALTER TRIGGER PARTS_AU0 ACTIVE; ALTER TRIGGER PARTS_BU_DISTR INACTIVE;
9
execute procedure DBGPR_MAKEWAREBASEFROMDOCS(0);
10
ALTER TRIGGER PARTS_BU_DISTR ACTIVE;