Установка Единого Окна — различия между версиями
Материал из wiki.standart-n.ru
Agk (обсуждение | вклад) (→Триггер DOC_DETAIL_ACTIVE_BU0) |
Agk (обсуждение | вклад) |
||
Строка 1492: | Строка 1492: | ||
ALTER TRIGGER WARES_SNAME_BI0 ACTIVE; | ALTER TRIGGER WARES_SNAME_BI0 ACTIVE; | ||
</pre> | </pre> | ||
− | |||
==Создаем индексы== | ==Создаем индексы== |
Версия 15:24, 16 января 2017
Содержание
- 1 Предварительная подготовка
- 2 Подготовка серверной базы и скриптов для Единого Окна
- 3 Останавливаем торговлю в точках
- 4 Обновляем структуру БД клиентов
- 5 Триггер DOC_DETAIL_ACTIVE_BI
- 6 Триггер DOC_DETAIL_ACTIVE_BU0
- 7 Представление VW_DOC_DETAIL_ACTIVE
- 8 Подготавливаем товары к сжатию
- 9 Создаем индексы
- 10 Устанавливаем параметры работы Единого Окна
- 11 Разрешаем торговлю в точках
- 12 Окончание
- 13 ТМС "Сжать товары после приведения" для базы исправлений
- 14 Сетка для обработки запросов товаров
Предварительная подготовка
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