Установка Единого Окна — различия между версиями
Материал из wiki.standart-n.ru
Agk (обсуждение | вклад) |
Agk (обсуждение | вклад) (→Обновляем структуру БД клиентов) |
||
| Строка 1018: | Строка 1018: | ||
SET TERM ; ^ | 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 ; ^ | ||
| + | |||
Версия 13:40, 14 января 2017
Содержание
Предварительная подготовка
1) Обновляем у всех клиентов Менеджер до версии не ниже ManagerXP2_273_5 от декабря 2016г. 2) Обновляем клиента синхронизации до версии не ниже DistributeClient2.202 от декабря 2016г. 3) Настраиваем двустороннюю синхронизацию таблиц WARES_TREB и G$PROFILES; 4) Проверяем, что настроена двусторонняя синхронизация таблицы WARES_LOG; 5) Проверяем, что у клиентов в PARAMS есть параметр CODE_PROFILE (Код профиля) и он корректный;
Подготовка серверной базы и скриптов для Единого Окна
1) Обновляем скрипты синхронизации на сервере; 2) Блокируем синхронизацию (оставляем только g$tasks); 3) Готовим серверую БД Подготовка серверной БД для перевода на Единое Окно;
Останавливаем торговлю в точках
CREATE EXCEPTION EX_DONT_WORK 'Вход в программу заблокирован. Идет обновление...'; SET TERM ^ ; CREATE OR ALTER TRIGGER SESSIONS_DONT_WORK_BI0 FOR SESSIONS ACTIVE BEFORE INSERT POSITION 0 AS begin exception EX_DONT_WORK; --Вход в программу заблокирован. Идет обновление... end ^ SET TERM ; ^ INSERT INTO Z$SERVICE (ID, TASK, STATUS, INSERTDT, STARTDT, ENDDT, TXTLOG) VALUES (999, 'POLITE_RESTORE', 0, current_timestamp, current_timestamp, current_timestamp, NULL); update sessions s set s.enddt=current_timestamp, s.endflag=0, s.endsession_id=s.id where 1=1 and s.prog containing 'manager' and s.endflag is null and cast(s.startdt as dm_date) >= dateadd(-3 day to current_date);
Обновляем структуру БД клиентов
CREATE EXCEPTION EX_WARES_ID_NOT_FOUND 'Не найдена позиция (wares)';
/*******************************************************************/
/*НОВЫЕ ПОЛЯ*/
ALTER TABLE WARES ADD G$PROFILE_ID DM_ID_NULL;
ALTER TABLE WARES ADD WHASH DM_ID;
ALTER TABLE WARES_LOG ADD ACTUAL_WARE_ID DM_UUID_NULL;
ALTER TABLE PARTS ADD ORIG_SNAME DM_TEXT;
ALTER TABLE PARTS ADD ORIG_SIZG DM_TEXT;
ALTER TABLE PARTS ADD ORIG_SCOUNTRY DM_TEXT;
ALTER TABLE PARTS ADD ORIG_BCODE_IZG DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD D$UUID DM_UUID_NULL;
ALTER TABLE DOC_DETAIL_ACTIVE ADD D$SRVUPDDT DM_DATETIME;
/*******************************************************************/
/*ПРОЦЕДУРЫ*/
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,D
ATESERT,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_N
DSO,: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 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 ; ^
/*******************************************************************/
/*ПРЕДСТАВЛЕНИЯ*/
/******************************************************************************/
/******************************************************************************/
/*** 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
Подготавливаем товары к сжатию
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 = '0' where p.param_id = 'WARES_ALLOW_EDIT'; update params p set p.param_value = '0' where p.param_id = 'WARES_ALLOW_ADD';
