Изменения представления — различия между версиями
Материал из wiki.standart-n.ru
BeTePoK (обсуждение | вклад) |
BeTePoK (обсуждение | вклад) |
||
Строка 25: | Строка 25: | ||
Установка: | Установка: | ||
<PRE> | <PRE> | ||
+ | |||
SET TERM ^ ; | SET TERM ^ ; | ||
+ | --создание доменов если их нет | ||
+ | execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^ | ||
+ | execute procedure PR_ALTER_VIEW('DM_ID','','BIGINT NOT NULL','DOMAIN');^ | ||
+ | execute procedure PR_ALTER_VIEW('DM_STATUS','','INTEGER','DOMAIN');^ | ||
+ | execute procedure PR_ALTER_VIEW('DM_DATETIME','','TIMESTAMP','DOMAIN');^ | ||
+ | execute procedure PR_ALTER_VIEW('DM_TEXT1024','','VARCHAR(1024) CHARACTER SET WIN1251 COLLATE WIN1251','DOMAIN');^ | ||
+ | execute procedure PR_ALTER_VIEW('DM_DOUBLE','','DOUBLE PRECISION','DOMAIN');^ | ||
+ | execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^ | ||
+ | execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^ | ||
− | + | --таблица ELNAKLS | |
− | + | EXECUTE BLOCK AS BEGIN | |
− | + | if (not exists(select 1 from rdb$relations where rdb$relation_name = 'ELNAKLS')) then | |
− | + | execute statement 'CREATE TABLE ELNAKLS(FILENAME DM_TEXT1024);'; | |
− | + | END^ | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | --таблица EXT_DATA | |
− | + | EXECUTE BLOCK AS BEGIN | |
− | + | if (not exists(select 1 from rdb$relations where rdb$relation_name = 'EXT_DATA')) then | |
− | + | execute statement 'CREATE TABLE EXT_DATA(ID DM_ID NOT NULL);'; | |
− | + | END^ | |
− | + | execute procedure PR_ALTER_VIEW('EXT_DATA','SNAME','DM_TEXT1024','TABLE');^ | |
− | + | execute procedure PR_ALTER_VIEW('EXT_DATA','ORIG_NAME','DM_TEXT1024','TABLE');^ | |
− | + | execute procedure PR_ALTER_VIEW('EXT_DATA','MGN_SOURCE','DM_TEXT1024','TABLE'); ^ | |
− | + | execute procedure PR_ALTER_VIEW('EXT_DATA','QUANT','DM_DOUBLE','TABLE'); ^ | |
− | + | execute procedure PR_ALTER_VIEW('EXT_DATA','PRICE','DM_DOUBLE','TABLE');^ | |
− | + | execute procedure PR_ALTER_VIEW('EXT_DATA','SUMMA','DM_DOUBLE','TABLE');^ | |
− | + | execute procedure PR_ALTER_VIEW('EXT_DATA','SDOC_TYPE','DM_TEXT1024','TABLE');^ | |
+ | execute procedure PR_ALTER_VIEW('EXT_DATA','COMMITDATE','DM_DATETIME','TABLE'); ^ | ||
+ | execute procedure PR_ALTER_VIEW('EXT_DATA','DOC_TYPE','DM_STATUS','TABLE'); ^ | ||
+ | execute procedure PR_ALTER_VIEW('EXT_DATA','STATUS','DM_STATUS','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('EXT_DATA','MGN_ID','DM_ID_NULL','TABLE'); ^ | ||
+ | execute procedure PR_ALTER_VIEW('EXT_DATA','INSERTDT','DM_DATETIME','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('EXT_DATA','G$PROFILE_ID','DM_ID_NULL','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('','','GEN_EXT_DATA_ID','generator');^ | ||
+ | execute procedure PR_ALTER_VIEW('EXT_DATA','ID','EXT_DATA_IDX1','INDEX');^ | ||
+ | execute procedure PR_ALTER_VIEW('EXT_DATA','SNAME','EXT_DATA_IDX2','INDEX');^ | ||
+ | execute procedure PR_ALTER_VIEW('EXT_DATA','MGN_ID','EXT_DATA_IDX3','INDEX'); ^ | ||
− | + | CREATE OR ALTER TRIGGER EXT_DATA_BI FOR EXT_DATA | |
− | + | ACTIVE BEFORE INSERT POSITION 0 | |
− | + | as | |
− | + | begin | |
− | + | new.insertdt = 'now'; | |
− | + | if (new.id is null) then | |
− | + | new.id = gen_id(gen_ext_data_id,1); | |
− | + | end;^ | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | --добавляем колонки в таблицу WARES | |
− | + | execute procedure PR_ALTER_VIEW('WARES','SNAME','DM_TEXT1024','TABLE');^ | |
− | + | execute procedure PR_ALTER_VIEW('WARES','MGN_NAME','DM_TEXT1024','TABLE');^ | |
− | + | execute procedure PR_ALTER_VIEW('WARES','MGN_ID','DM_ID_NULL','TABLE'); ^ | |
− | + | execute procedure PR_ALTER_VIEW('WARES','MGN_SOURCE','DM_BLOBTEXT','TABLE');^ | |
− | + | execute procedure PR_ALTER_VIEW('WARES','MGN_SOURCE','DM_BLOBTEXT','TABLE');^ | |
+ | --индексы в таблицу wares | ||
+ | execute procedure PR_ALTER_VIEW('WARES','MGN_NAME','WARES_IDX3','INDEX');^ | ||
+ | execute procedure PR_ALTER_VIEW('WARES','MGN_NAME','WARES_IDX4','INDEX');^ | ||
+ | execute procedure PR_ALTER_VIEW('WARES','MGN_ID','WARES_IDX7','INDEX'); ^ | ||
− | + | CREATE OR ALTER TRIGGER WARES_BU_MGN FOR WARES | |
− | + | ACTIVE BEFORE UPDATE POSITION 0 | |
− | + | AS | |
− | + | begin | |
− | + | if (new.name_id <> old.name_id) then | |
− | + | ||
− | if ( | + | |
begin | begin | ||
− | + | new.mgn_name = null; | |
− | + | new.mgn_id = 0; | |
end | end | ||
− | + | new.sname = (select svalue from vals where id = new.name_id); | |
− | + | end;^ | |
− | + | CREATE OR ALTER TRIGGER WARES_BI_SNAME FOR WARES | |
− | + | ACTIVE BEFORE INSERT POSITION 0 | |
− | + | AS | |
− | + | begin | |
− | + | new.sname = (select svalue from vals where id = new.name_id); | |
− | + | end;^ | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | end^ | + | |
− | + | -- таблица ORDER_DATA | |
+ | EXECUTE BLOCK AS BEGIN | ||
+ | if (not exists(select 1 from rdb$relations where rdb$relation_name = 'ORDER_DATA')) then | ||
+ | execute statement 'CREATE TABLE ORDER_DATA (ID DM_ID NOT NULL);'; | ||
+ | END^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','ID','','PRIMARY KEY');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_NAME','DM_TEXT1024','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','ORDER_QUANT','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','SHORT_ORDER_QUANT','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','OPTIMAL_ORDER_QUANT','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','ASSORT_ORDER_QUANT','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PRIHOD_QUANT','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','REALQUANT','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PRIHOD_DATE','DM_DATETIME','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PRIHOD_PRICE','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_WEEK_SUM','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_2WEEKS_SUM','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PERIOD_SUM','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_MONTH_SUM','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','SEASON_SUM','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','RASHOD_DOC_QUANT','DM_STATUS','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','PRIHOD_DOC_QUANT','DM_STATUS','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_OZ_DATE','DM_DATETIME','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','ELNAKL_QUANT','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','QUANT','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_OZ_QUANT','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','ACTIVE_PRIHOD_DOC_QUANT','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_OZ_PRICE','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_RASHOD_DATE','DM_DATETIME','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','ACTIVE_ORDER_QUANT','DM_DOUBLE','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_ID','DM_ID_NULL','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','BAD_FLAG','DM_STATUS','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','INSERTDT','DM_DATETIME','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','G$PROFILE_ID','DM_ID_NULL','TABLE');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','G$PROFILE_ID','DM_ID_NULL','TABLE');^ | ||
+ | |||
+ | execute procedure PR_ALTER_VIEW('','','GEN_ORDER_DATA_ID','generator');^ | ||
+ | |||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_NAME','ORDER_DATA_IDX1','INDEX');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_NAME','ORDER_DATA_IDX2','INDEX');^ | ||
+ | execute procedure PR_ALTER_VIEW('ORDER_DATA','ID','ORDER_DATA_IDX3','INDEX');^ | ||
+ | |||
+ | CREATE OR ALTER TRIGGER ORDER_DATA_BI FOR ORDER_DATA | ||
+ | ACTIVE BEFORE INSERT POSITION 0 | ||
+ | as | ||
+ | begin | ||
+ | if (new.id is null) then | ||
+ | new.id = gen_id(gen_order_data_id,1); | ||
+ | end;^ | ||
+ | |||
+ | -- | ||
+ | create or alter procedure PR_INSTALL_UPDPR_REPARWARES | ||
+ | as | ||
+ | begin | ||
+ | |||
+ | if (( | ||
+ | select | ||
+ | upper(rf.rdb$field_source) | ||
+ | from RDB$RELATION_FIELDS rf | ||
+ | where 1=1 | ||
+ | and rf.rdb$field_name='ID' | ||
+ | and rf.rdb$relation_name='WARES')='DM_UUID' | ||
+ | ) then | ||
+ | EXECUTE STATEMENT (' | ||
+ | create or alter procedure UPDPR_REPARWARES | ||
+ | returns ( | ||
+ | ID DM_TEXT) | ||
+ | as | ||
+ | declare variable NAME_ID DM_UUID; | ||
+ | declare variable IZG_ID DM_TEXT; | ||
+ | declare variable COUNTRY_ID DM_TEXT; | ||
+ | declare variable ORIG_CODE DM_TEXT; | ||
+ | declare variable ORIG_NAME_ID DM_TEXT; | ||
+ | declare variable ORIG_IZG_ID DM_TEXT; | ||
+ | declare variable ORIG_COUNTRY_ID DM_TEXT; | ||
+ | declare variable T_ID DM_UUID; | ||
+ | begin | ||
+ | --BASE WITH GLOBAL ID | ||
+ | delete from DOC_DETAIL_ACTIVE_LOG; | ||
+ | for select min(id), NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID from wares | ||
+ | group by NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID | ||
+ | having count(1)>1 | ||
+ | into id, NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID do | ||
+ | begin | ||
+ | for | ||
+ | select id from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE | ||
+ | and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id into :t_id do | ||
+ | begin | ||
+ | update DOC_DETAIL_ACTIVE set ware_id=:id where ware_id=:t_id; | ||
+ | update DOC_DETAIL_ACTIVE_CANCELED set ware_id=:id where ware_id=:t_id; | ||
+ | -- update DOC_DETAIL_ACTIVE_LOG set ware_id=:id where ware_id=:t_id; | ||
+ | update DOC_DETAIL_ACTIVE_NAMEID set ware_id=:id where ware_id=:t_id; | ||
+ | update DOC_DETAIL_DELETED set ware_id=:id where ware_id=:t_id; | ||
+ | if (( | ||
+ | select | ||
+ | count(1) | ||
+ | from RDB$RELATIONS r | ||
+ | where 1=1 | ||
+ | and upper(r.rdb$relation_name)=''DOC_DETAIL_ACTIVE_TREB'' | ||
+ | )>0 | ||
+ | ) THEN | ||
+ | EXECUTE STATEMENT (''update DOC_DETAIL_ACTIVE_TREB set ware_id=''||:id||'' where ware_id=''||:t_id); | ||
+ | if (( | ||
+ | select | ||
+ | count(1) | ||
+ | from RDB$RELATIONS r | ||
+ | where 1=1 | ||
+ | and upper(r.rdb$relation_name)=''OUT$ZAKAZ'' | ||
+ | )>0 | ||
+ | ) THEN | ||
+ | EXECUTE STATEMENT (''update OUT$ZAKAZ set ware_id=''||:id||'' where ware_id=''||:t_id); | ||
+ | |||
+ | update PARTS set ware_id=:id where ware_id=:t_id; | ||
+ | update PARTS_LOG set ware_id=:id where ware_id=:t_id; | ||
+ | update WAREBASE set ware_id=:id where ware_id=:t_id; | ||
+ | update WAREBASEFOLDERS set ware_id=:id where ware_id=:t_id; | ||
+ | update WAREBASENAMEID set ware_id=:id where ware_id=:t_id; | ||
+ | delete from wares where id=:t_id; | ||
+ | end | ||
+ | -- suspend; | ||
+ | /* delete from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE | ||
+ | and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id; | ||
+ | suspend; | ||
+ | */ | ||
+ | end | ||
+ | -- suspend; | ||
+ | end; | ||
+ | '); | ||
+ | else | ||
+ | EXECUTE STATEMENT (' | ||
+ | create or alter procedure UPDPR_REPARWARES | ||
+ | returns ( | ||
+ | ID DM_TEXT) | ||
+ | as | ||
+ | declare variable NAME_ID DM_ID; | ||
+ | declare variable IZG_ID DM_TEXT; | ||
+ | declare variable COUNTRY_ID DM_TEXT; | ||
+ | declare variable ORIG_CODE DM_TEXT; | ||
+ | declare variable ORIG_NAME_ID DM_TEXT; | ||
+ | declare variable ORIG_IZG_ID DM_TEXT; | ||
+ | declare variable ORIG_COUNTRY_ID DM_TEXT; | ||
+ | declare variable T_ID DM_ID_NULL; | ||
+ | begin | ||
+ | --BASE WITHOUT GLOBAL ID | ||
+ | delete from DOC_DETAIL_ACTIVE_LOG; | ||
+ | for select min(id), NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID from wares | ||
+ | group by NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID | ||
+ | having count(1)>1 | ||
+ | into id, NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID do | ||
+ | begin | ||
+ | for | ||
+ | select id from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE | ||
+ | and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id into :t_id do | ||
+ | begin | ||
+ | update DOC_DETAIL_ACTIVE set ware_id=:id where ware_id=:t_id; | ||
+ | update DOC_DETAIL_ACTIVE_CANCELED set ware_id=:id where ware_id=:t_id; | ||
+ | -- update DOC_DETAIL_ACTIVE_LOG set ware_id=:id where ware_id=:t_id; | ||
+ | update DOC_DETAIL_ACTIVE_NAMEID set ware_id=:id where ware_id=:t_id; | ||
+ | if (( | ||
+ | select | ||
+ | count(1) | ||
+ | from RDB$RELATIONS r | ||
+ | where 1=1 | ||
+ | and upper(r.rdb$relation_name)=''DOC_DETAIL_ACTIVE_TREB'' | ||
+ | )>0 | ||
+ | ) THEN | ||
+ | EXECUTE STATEMENT (''update DOC_DETAIL_ACTIVE_TREB set ware_id=''||:id||'' where ware_id=''||:t_id); | ||
+ | if (( | ||
+ | select | ||
+ | count(1) | ||
+ | from RDB$RELATIONS r | ||
+ | where 1=1 | ||
+ | and upper(r.rdb$relation_name)=''OUT$ZAKAZ'' | ||
+ | )>0 | ||
+ | ) THEN | ||
+ | EXECUTE STATEMENT (''update OUT$ZAKAZ set ware_id=''||:id||'' where ware_id=''||:t_id); | ||
+ | update DOC_DETAIL_DELETED set ware_id=:id where ware_id=:t_id; | ||
+ | update PARTS set ware_id=:id where ware_id=:t_id; | ||
+ | update PARTS_LOG set ware_id=:id where ware_id=:t_id; | ||
+ | update WAREBASE set ware_id=:id where ware_id=:t_id; | ||
+ | update WAREBASEFOLDERS set ware_id=:id where ware_id=:t_id; | ||
+ | update WAREBASENAMEID set ware_id=:id where ware_id=:t_id; | ||
+ | delete from wares where id=:t_id; | ||
+ | end | ||
+ | -- suspend; | ||
+ | /* delete from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE | ||
+ | and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id; | ||
+ | suspend; | ||
+ | */ | ||
+ | end | ||
+ | -- suspend; | ||
+ | end; | ||
+ | '); | ||
+ | end;^ | ||
/* Existing privileges on this procedure */ | /* Existing privileges on this procedure */ | ||
+ | GRANT EXECUTE ON PROCEDURE PR_INSTALL_UPDPR_REPARWARES TO SYSDBA;^ | ||
+ | execute procedure PR_INSTALL_UPDPR_REPARWARES;^ | ||
+ | DROP PROCEDURE PR_INSTALL_UPDPR_REPARWARES;^ | ||
+ | |||
+ | /* PR_GET_WARE */ | ||
+ | create or alter procedure PR_INSTALL_PR_GET_WARE | ||
+ | as | ||
+ | begin | ||
+ | if (( | ||
+ | select | ||
+ | upper(rf.rdb$field_source) | ||
+ | from RDB$RELATION_FIELDS rf | ||
+ | where 1=1 | ||
+ | and rf.rdb$field_name='ID' | ||
+ | and rf.rdb$relation_name='WARES')='DM_UUID' | ||
+ | ) then | ||
+ | EXECUTE STATEMENT (' | ||
+ | create or alter procedure PR_GET_WARE ( | ||
+ | SNAME type of DM_TEXT, | ||
+ | SIZG type of DM_TEXT, | ||
+ | SCOUNTRY type of DM_TEXT, | ||
+ | ORIG_CODE type of DM_TEXT, | ||
+ | SORIG_NAME type of DM_TEXT, | ||
+ | SORIG_IZG type of DM_TEXT, | ||
+ | SORIG_COUNTRY type of DM_TEXT, | ||
+ | BARCODE type of DM_TEXT, | ||
+ | Z_ID type of DM_ID, | ||
+ | SKLAD_ID DM_TEXT, | ||
+ | ALTTYPE DM_STATUS, | ||
+ | MNN DM_TEXT = '''') | ||
+ | returns ( | ||
+ | W_ID type of DM_UUID_NULL) | ||
+ | as | ||
+ | declare variable OLD_BARCODE DM_TEXT1024; | ||
+ | declare variable NAME_ID type of DM_UUID_NULL; | ||
+ | declare variable IZG_ID type of DM_UUID_NULL; | ||
+ | declare variable COUNTRY_ID type of DM_UUID_NULL; | ||
+ | declare variable ORIG_NAME_ID type of DM_UUID_NULL; | ||
+ | declare variable ORIG_IZG_ID type of DM_UUID_NULL; | ||
+ | declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL; | ||
+ | begin | ||
+ | --BASE WITH GLOBAL ID | ||
+ | if (orig_code is null) then orig_code=''''; | ||
+ | select val_id from pr_getval_id(:sname,0,:alttype,:mnn) into :name_id; | ||
+ | select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id; | ||
+ | select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id; | ||
+ | select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id; | ||
+ | select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id; | ||
+ | select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id; | ||
+ | select id, barcode 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, :old_barcode; | ||
+ | if (w_id is null) then | ||
+ | begin | ||
+ | -- exception EX_WRONG_OPER; | ||
+ | --w_id=gen_id(gen_wares_id,1); | ||
+ | w_id=UUID_TO_CHAR(GEN_UUID()); | ||
+ | insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID) | ||
+ | values | ||
+ | (:W_ID,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,:BARCODE,:Z_ID,:SKLAD_ID); | ||
+ | end | ||
+ | else if (old_barcode <> barcode) then update wares set barcode = :barcode where id = :w_id; | ||
+ | suspend; | ||
+ | end; | ||
+ | '); | ||
+ | else | ||
+ | EXECUTE STATEMENT (' | ||
+ | create or alter procedure PR_GET_WARE ( | ||
+ | SNAME type of DM_TEXT, | ||
+ | SIZG type of DM_TEXT, | ||
+ | SCOUNTRY type of DM_TEXT, | ||
+ | ORIG_CODE type of DM_TEXT, | ||
+ | SORIG_NAME type of DM_TEXT, | ||
+ | SORIG_IZG type of DM_TEXT, | ||
+ | SORIG_COUNTRY type of DM_TEXT, | ||
+ | BARCODE type of DM_TEXT, | ||
+ | Z_ID type of DM_ID, | ||
+ | SKLAD_ID DM_TEXT, | ||
+ | ALTTYPE DM_STATUS, | ||
+ | MNN DM_TEXT = '''') | ||
+ | returns ( | ||
+ | W_ID type of DM_ID) | ||
+ | as | ||
+ | declare variable OLD_BARCODE DM_TEXT1024; | ||
+ | declare variable NAME_ID type of DM_ID; | ||
+ | declare variable IZG_ID type of DM_ID; | ||
+ | declare variable COUNTRY_ID type of DM_ID; | ||
+ | declare variable ORIG_NAME_ID type of DM_ID; | ||
+ | declare variable ORIG_IZG_ID type of DM_ID; | ||
+ | declare variable ORIG_COUNTRY_ID type of DM_ID; | ||
+ | begin | ||
+ | --BASE WITHOUT GLOBAL ID | ||
+ | if (orig_code is null) then orig_code=''''; | ||
+ | select val_id from pr_getval_id(:sname,0,:alttype,:mnn) into :name_id; | ||
+ | select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id; | ||
+ | select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id; | ||
+ | select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id; | ||
+ | select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id; | ||
+ | select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id; | ||
+ | select id, barcode 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, :old_barcode; | ||
+ | if (w_id is null) then | ||
+ | begin | ||
+ | -- exception EX_WRONG_OPER; | ||
+ | w_id=gen_id(gen_wares_id,1); | ||
+ | --w_id=UUID_TO_CHAR(GEN_UUID()); | ||
+ | insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID) | ||
+ | values | ||
+ | (:W_ID,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,:BARCODE,:Z_ID,:SKLAD_ID); | ||
+ | end | ||
+ | else if (old_barcode <> barcode) then update wares set barcode = :barcode where id = :w_id; | ||
+ | suspend; | ||
+ | end; | ||
+ | '); | ||
+ | end;^ | ||
+ | /* Existing privileges on this procedure */ | ||
+ | GRANT EXECUTE ON PROCEDURE PR_INSTALL_PR_GET_WARE TO SYSDBA;^ | ||
+ | execute procedure PR_INSTALL_PR_GET_WARE;^ | ||
+ | DROP PROCEDURE PR_INSTALL_PR_GET_WARE;^ | ||
+ | |||
+ | EXECUTE BLOCK AS BEGIN | ||
+ | if(not exists(select id from DOC_TYPES where id = 36)) then | ||
+ | execute statement 'INSERT INTO DOC_TYPES (ID, CAPTION, BASE_TYPE, STATINI, REPORTS, VTYPE) VALUES (36, ''Заказ оптовику'', 4, '' | ||
+ | [main] | ||
+ | koef=0 | ||
+ | |||
+ | [freereport] | ||
+ | enabled=1 | ||
+ | caption=Печать пакета документов | ||
+ | tmplpath=zakaz_opt.cffr3 | ||
+ | |||
+ | [dbf] | ||
+ | inipath=dbf.ini | ||
+ | '', NULL, 1);'; | ||
+ | END;^ | ||
+ | |||
+ | --колонки в вьюхи | ||
+ | execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MGN_NAME','w.mgn_name');^ | ||
+ | execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MGN_ID','w.mgn_id');^ | ||
+ | execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MGN_SOURCE','cast(w.mgn_source as dm_text_big)');^ | ||
+ | execute procedure PR_ALTER_VIEW('VW_WARES','MGN_NAME','w.mgn_name');^ | ||
+ | execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id');^ | ||
+ | execute procedure PR_ALTER_VIEW('VW_WARES','MGN_SOURCE','cast(w.mgn_source as dm_text_big)');^ | ||
+ | execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','MGN_NAME','(select mgn_name from wares where id = da.ware_id) as mgn_name');^ | ||
+ | execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','MGN_ID','(select mgn_id from wares where id = da.ware_id) as mgn_id');^ | ||
+ | execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','MGN_SOURCE','(select mgn_source from wares where id = da.ware_id) as mgn_source');^ | ||
+ | execute procedure PR_ALTER_VIEW('VW_WAREBASE','MGN_NAME','(select mgn_name from wares where id = wb.ware_id)');^ | ||
+ | |||
+ | SET TERM ; ^ | ||
− | |||
</PRE> | </PRE> |
Версия 10:04, 15 июня 2016
Изменение VIEW
- изменяет view,таблицы
пока что без добавления join'ов во view - там всё не так однозначно
Пример:
--создание генератора execute procedure PR_ALTER_VIEW('','','gen1111','generator'); --создание индекса execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100500','index'); execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100501','DESCENDING INDEX'); execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100502','ASCENDING INDEX'); --первичный ключ execute procedure PR_ALTER_VIEW('TEMP_MNN','ID','','PRIMARY KEY'); --изменение таблицы execute procedure PR_ALTER_VIEW('DOC_DETAIL','MGN_ID1','DM_DATETIME','TABLE'); --изменение вьюхи execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id','VIEW'); execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id'); execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id',''); --создание домена execute procedure PR_ALTER_VIEW('DM_BIGINT1','','BIGINT','DOMAIN');
Установка:
SET TERM ^ ; --создание доменов если их нет execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^ execute procedure PR_ALTER_VIEW('DM_ID','','BIGINT NOT NULL','DOMAIN');^ execute procedure PR_ALTER_VIEW('DM_STATUS','','INTEGER','DOMAIN');^ execute procedure PR_ALTER_VIEW('DM_DATETIME','','TIMESTAMP','DOMAIN');^ execute procedure PR_ALTER_VIEW('DM_TEXT1024','','VARCHAR(1024) CHARACTER SET WIN1251 COLLATE WIN1251','DOMAIN');^ execute procedure PR_ALTER_VIEW('DM_DOUBLE','','DOUBLE PRECISION','DOMAIN');^ execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^ execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^ --таблица ELNAKLS EXECUTE BLOCK AS BEGIN if (not exists(select 1 from rdb$relations where rdb$relation_name = 'ELNAKLS')) then execute statement 'CREATE TABLE ELNAKLS(FILENAME DM_TEXT1024);'; END^ --таблица EXT_DATA EXECUTE BLOCK AS BEGIN if (not exists(select 1 from rdb$relations where rdb$relation_name = 'EXT_DATA')) then execute statement 'CREATE TABLE EXT_DATA(ID DM_ID NOT NULL);'; END^ execute procedure PR_ALTER_VIEW('EXT_DATA','SNAME','DM_TEXT1024','TABLE');^ execute procedure PR_ALTER_VIEW('EXT_DATA','ORIG_NAME','DM_TEXT1024','TABLE');^ execute procedure PR_ALTER_VIEW('EXT_DATA','MGN_SOURCE','DM_TEXT1024','TABLE'); ^ execute procedure PR_ALTER_VIEW('EXT_DATA','QUANT','DM_DOUBLE','TABLE'); ^ execute procedure PR_ALTER_VIEW('EXT_DATA','PRICE','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('EXT_DATA','SUMMA','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('EXT_DATA','SDOC_TYPE','DM_TEXT1024','TABLE');^ execute procedure PR_ALTER_VIEW('EXT_DATA','COMMITDATE','DM_DATETIME','TABLE'); ^ execute procedure PR_ALTER_VIEW('EXT_DATA','DOC_TYPE','DM_STATUS','TABLE'); ^ execute procedure PR_ALTER_VIEW('EXT_DATA','STATUS','DM_STATUS','TABLE');^ execute procedure PR_ALTER_VIEW('EXT_DATA','MGN_ID','DM_ID_NULL','TABLE'); ^ execute procedure PR_ALTER_VIEW('EXT_DATA','INSERTDT','DM_DATETIME','TABLE');^ execute procedure PR_ALTER_VIEW('EXT_DATA','G$PROFILE_ID','DM_ID_NULL','TABLE');^ execute procedure PR_ALTER_VIEW('','','GEN_EXT_DATA_ID','generator');^ execute procedure PR_ALTER_VIEW('EXT_DATA','ID','EXT_DATA_IDX1','INDEX');^ execute procedure PR_ALTER_VIEW('EXT_DATA','SNAME','EXT_DATA_IDX2','INDEX');^ execute procedure PR_ALTER_VIEW('EXT_DATA','MGN_ID','EXT_DATA_IDX3','INDEX'); ^ CREATE OR ALTER TRIGGER EXT_DATA_BI FOR EXT_DATA ACTIVE BEFORE INSERT POSITION 0 as begin new.insertdt = 'now'; if (new.id is null) then new.id = gen_id(gen_ext_data_id,1); end;^ --добавляем колонки в таблицу WARES execute procedure PR_ALTER_VIEW('WARES','SNAME','DM_TEXT1024','TABLE');^ execute procedure PR_ALTER_VIEW('WARES','MGN_NAME','DM_TEXT1024','TABLE');^ execute procedure PR_ALTER_VIEW('WARES','MGN_ID','DM_ID_NULL','TABLE'); ^ execute procedure PR_ALTER_VIEW('WARES','MGN_SOURCE','DM_BLOBTEXT','TABLE');^ execute procedure PR_ALTER_VIEW('WARES','MGN_SOURCE','DM_BLOBTEXT','TABLE');^ --индексы в таблицу wares execute procedure PR_ALTER_VIEW('WARES','MGN_NAME','WARES_IDX3','INDEX');^ execute procedure PR_ALTER_VIEW('WARES','MGN_NAME','WARES_IDX4','INDEX');^ execute procedure PR_ALTER_VIEW('WARES','MGN_ID','WARES_IDX7','INDEX'); ^ 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;^ CREATE OR ALTER TRIGGER WARES_BI_SNAME FOR WARES ACTIVE BEFORE INSERT POSITION 0 AS begin new.sname = (select svalue from vals where id = new.name_id); end;^ -- таблица ORDER_DATA EXECUTE BLOCK AS BEGIN if (not exists(select 1 from rdb$relations where rdb$relation_name = 'ORDER_DATA')) then execute statement 'CREATE TABLE ORDER_DATA (ID DM_ID NOT NULL);'; END^ execute procedure PR_ALTER_VIEW('ORDER_DATA','ID','','PRIMARY KEY');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_NAME','DM_TEXT1024','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','ORDER_QUANT','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','SHORT_ORDER_QUANT','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','OPTIMAL_ORDER_QUANT','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','ASSORT_ORDER_QUANT','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PRIHOD_QUANT','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','REALQUANT','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PRIHOD_DATE','DM_DATETIME','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PRIHOD_PRICE','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_WEEK_SUM','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_2WEEKS_SUM','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PERIOD_SUM','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_MONTH_SUM','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','SEASON_SUM','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','RASHOD_DOC_QUANT','DM_STATUS','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','PRIHOD_DOC_QUANT','DM_STATUS','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_OZ_DATE','DM_DATETIME','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','ELNAKL_QUANT','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','QUANT','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_OZ_QUANT','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','ACTIVE_PRIHOD_DOC_QUANT','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_OZ_PRICE','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_RASHOD_DATE','DM_DATETIME','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','ACTIVE_ORDER_QUANT','DM_DOUBLE','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_ID','DM_ID_NULL','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','BAD_FLAG','DM_STATUS','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','INSERTDT','DM_DATETIME','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','G$PROFILE_ID','DM_ID_NULL','TABLE');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','G$PROFILE_ID','DM_ID_NULL','TABLE');^ execute procedure PR_ALTER_VIEW('','','GEN_ORDER_DATA_ID','generator');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_NAME','ORDER_DATA_IDX1','INDEX');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_NAME','ORDER_DATA_IDX2','INDEX');^ execute procedure PR_ALTER_VIEW('ORDER_DATA','ID','ORDER_DATA_IDX3','INDEX');^ CREATE OR ALTER TRIGGER ORDER_DATA_BI FOR ORDER_DATA ACTIVE BEFORE INSERT POSITION 0 as begin if (new.id is null) then new.id = gen_id(gen_order_data_id,1); end;^ -- create or alter procedure PR_INSTALL_UPDPR_REPARWARES as begin if (( select upper(rf.rdb$field_source) from RDB$RELATION_FIELDS rf where 1=1 and rf.rdb$field_name='ID' and rf.rdb$relation_name='WARES')='DM_UUID' ) then EXECUTE STATEMENT (' create or alter procedure UPDPR_REPARWARES returns ( ID DM_TEXT) as declare variable NAME_ID DM_UUID; declare variable IZG_ID DM_TEXT; declare variable COUNTRY_ID DM_TEXT; declare variable ORIG_CODE DM_TEXT; declare variable ORIG_NAME_ID DM_TEXT; declare variable ORIG_IZG_ID DM_TEXT; declare variable ORIG_COUNTRY_ID DM_TEXT; declare variable T_ID DM_UUID; begin --BASE WITH GLOBAL ID delete from DOC_DETAIL_ACTIVE_LOG; for select min(id), NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID from wares group by NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID having count(1)>1 into id, NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID do begin for select id from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id into :t_id do begin update DOC_DETAIL_ACTIVE set ware_id=:id where ware_id=:t_id; update DOC_DETAIL_ACTIVE_CANCELED set ware_id=:id where ware_id=:t_id; -- update DOC_DETAIL_ACTIVE_LOG set ware_id=:id where ware_id=:t_id; update DOC_DETAIL_ACTIVE_NAMEID set ware_id=:id where ware_id=:t_id; update DOC_DETAIL_DELETED set ware_id=:id where ware_id=:t_id; if (( select count(1) from RDB$RELATIONS r where 1=1 and upper(r.rdb$relation_name)=''DOC_DETAIL_ACTIVE_TREB'' )>0 ) THEN EXECUTE STATEMENT (''update DOC_DETAIL_ACTIVE_TREB set ware_id=''||:id||'' where ware_id=''||:t_id); if (( select count(1) from RDB$RELATIONS r where 1=1 and upper(r.rdb$relation_name)=''OUT$ZAKAZ'' )>0 ) THEN EXECUTE STATEMENT (''update OUT$ZAKAZ set ware_id=''||:id||'' where ware_id=''||:t_id); update PARTS set ware_id=:id where ware_id=:t_id; update PARTS_LOG set ware_id=:id where ware_id=:t_id; update WAREBASE set ware_id=:id where ware_id=:t_id; update WAREBASEFOLDERS set ware_id=:id where ware_id=:t_id; update WAREBASENAMEID set ware_id=:id where ware_id=:t_id; delete from wares where id=:t_id; end -- suspend; /* delete from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id; suspend; */ end -- suspend; end; '); else EXECUTE STATEMENT (' create or alter procedure UPDPR_REPARWARES returns ( ID DM_TEXT) as declare variable NAME_ID DM_ID; declare variable IZG_ID DM_TEXT; declare variable COUNTRY_ID DM_TEXT; declare variable ORIG_CODE DM_TEXT; declare variable ORIG_NAME_ID DM_TEXT; declare variable ORIG_IZG_ID DM_TEXT; declare variable ORIG_COUNTRY_ID DM_TEXT; declare variable T_ID DM_ID_NULL; begin --BASE WITHOUT GLOBAL ID delete from DOC_DETAIL_ACTIVE_LOG; for select min(id), NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID from wares group by NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID having count(1)>1 into id, NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID do begin for select id from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id into :t_id do begin update DOC_DETAIL_ACTIVE set ware_id=:id where ware_id=:t_id; update DOC_DETAIL_ACTIVE_CANCELED set ware_id=:id where ware_id=:t_id; -- update DOC_DETAIL_ACTIVE_LOG set ware_id=:id where ware_id=:t_id; update DOC_DETAIL_ACTIVE_NAMEID set ware_id=:id where ware_id=:t_id; if (( select count(1) from RDB$RELATIONS r where 1=1 and upper(r.rdb$relation_name)=''DOC_DETAIL_ACTIVE_TREB'' )>0 ) THEN EXECUTE STATEMENT (''update DOC_DETAIL_ACTIVE_TREB set ware_id=''||:id||'' where ware_id=''||:t_id); if (( select count(1) from RDB$RELATIONS r where 1=1 and upper(r.rdb$relation_name)=''OUT$ZAKAZ'' )>0 ) THEN EXECUTE STATEMENT (''update OUT$ZAKAZ set ware_id=''||:id||'' where ware_id=''||:t_id); update DOC_DETAIL_DELETED set ware_id=:id where ware_id=:t_id; update PARTS set ware_id=:id where ware_id=:t_id; update PARTS_LOG set ware_id=:id where ware_id=:t_id; update WAREBASE set ware_id=:id where ware_id=:t_id; update WAREBASEFOLDERS set ware_id=:id where ware_id=:t_id; update WAREBASENAMEID set ware_id=:id where ware_id=:t_id; delete from wares where id=:t_id; end -- suspend; /* delete from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id; suspend; */ end -- suspend; end; '); end;^ /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE PR_INSTALL_UPDPR_REPARWARES TO SYSDBA;^ execute procedure PR_INSTALL_UPDPR_REPARWARES;^ DROP PROCEDURE PR_INSTALL_UPDPR_REPARWARES;^ /* PR_GET_WARE */ create or alter procedure PR_INSTALL_PR_GET_WARE as begin if (( select upper(rf.rdb$field_source) from RDB$RELATION_FIELDS rf where 1=1 and rf.rdb$field_name='ID' and rf.rdb$relation_name='WARES')='DM_UUID' ) then EXECUTE STATEMENT (' create or alter procedure PR_GET_WARE ( SNAME type of DM_TEXT, SIZG type of DM_TEXT, SCOUNTRY type of DM_TEXT, ORIG_CODE type of DM_TEXT, SORIG_NAME type of DM_TEXT, SORIG_IZG type of DM_TEXT, SORIG_COUNTRY type of DM_TEXT, BARCODE type of DM_TEXT, Z_ID type of DM_ID, SKLAD_ID DM_TEXT, ALTTYPE DM_STATUS, MNN DM_TEXT = '''') returns ( W_ID type of DM_UUID_NULL) as declare variable OLD_BARCODE DM_TEXT1024; declare variable NAME_ID type of DM_UUID_NULL; declare variable IZG_ID type of DM_UUID_NULL; declare variable COUNTRY_ID type of DM_UUID_NULL; declare variable ORIG_NAME_ID type of DM_UUID_NULL; declare variable ORIG_IZG_ID type of DM_UUID_NULL; declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL; begin --BASE WITH GLOBAL ID if (orig_code is null) then orig_code=''''; select val_id from pr_getval_id(:sname,0,:alttype,:mnn) into :name_id; select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id; select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id; select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id; select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id; select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id; select id, barcode 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, :old_barcode; if (w_id is null) then begin -- exception EX_WRONG_OPER; --w_id=gen_id(gen_wares_id,1); w_id=UUID_TO_CHAR(GEN_UUID()); insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID) values (:W_ID,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,:BARCODE,:Z_ID,:SKLAD_ID); end else if (old_barcode <> barcode) then update wares set barcode = :barcode where id = :w_id; suspend; end; '); else EXECUTE STATEMENT (' create or alter procedure PR_GET_WARE ( SNAME type of DM_TEXT, SIZG type of DM_TEXT, SCOUNTRY type of DM_TEXT, ORIG_CODE type of DM_TEXT, SORIG_NAME type of DM_TEXT, SORIG_IZG type of DM_TEXT, SORIG_COUNTRY type of DM_TEXT, BARCODE type of DM_TEXT, Z_ID type of DM_ID, SKLAD_ID DM_TEXT, ALTTYPE DM_STATUS, MNN DM_TEXT = '''') returns ( W_ID type of DM_ID) as declare variable OLD_BARCODE DM_TEXT1024; declare variable NAME_ID type of DM_ID; declare variable IZG_ID type of DM_ID; declare variable COUNTRY_ID type of DM_ID; declare variable ORIG_NAME_ID type of DM_ID; declare variable ORIG_IZG_ID type of DM_ID; declare variable ORIG_COUNTRY_ID type of DM_ID; begin --BASE WITHOUT GLOBAL ID if (orig_code is null) then orig_code=''''; select val_id from pr_getval_id(:sname,0,:alttype,:mnn) into :name_id; select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id; select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id; select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id; select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id; select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id; select id, barcode 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, :old_barcode; if (w_id is null) then begin -- exception EX_WRONG_OPER; w_id=gen_id(gen_wares_id,1); --w_id=UUID_TO_CHAR(GEN_UUID()); insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID) values (:W_ID,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,:BARCODE,:Z_ID,:SKLAD_ID); end else if (old_barcode <> barcode) then update wares set barcode = :barcode where id = :w_id; suspend; end; '); end;^ /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE PR_INSTALL_PR_GET_WARE TO SYSDBA;^ execute procedure PR_INSTALL_PR_GET_WARE;^ DROP PROCEDURE PR_INSTALL_PR_GET_WARE;^ EXECUTE BLOCK AS BEGIN if(not exists(select id from DOC_TYPES where id = 36)) then execute statement 'INSERT INTO DOC_TYPES (ID, CAPTION, BASE_TYPE, STATINI, REPORTS, VTYPE) VALUES (36, ''Заказ оптовику'', 4, '' [main] koef=0 [freereport] enabled=1 caption=Печать пакета документов tmplpath=zakaz_opt.cffr3 [dbf] inipath=dbf.ini '', NULL, 1);'; END;^ --колонки в вьюхи execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MGN_NAME','w.mgn_name');^ execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MGN_ID','w.mgn_id');^ execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MGN_SOURCE','cast(w.mgn_source as dm_text_big)');^ execute procedure PR_ALTER_VIEW('VW_WARES','MGN_NAME','w.mgn_name');^ execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id');^ execute procedure PR_ALTER_VIEW('VW_WARES','MGN_SOURCE','cast(w.mgn_source as dm_text_big)');^ execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','MGN_NAME','(select mgn_name from wares where id = da.ware_id) as mgn_name');^ execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','MGN_ID','(select mgn_id from wares where id = da.ware_id) as mgn_id');^ execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','MGN_SOURCE','(select mgn_source from wares where id = da.ware_id) as mgn_source');^ execute procedure PR_ALTER_VIEW('VW_WAREBASE','MGN_NAME','(select mgn_name from wares where id = wb.ware_id)');^ SET TERM ; ^