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

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎Сетки и печатная форма)
 
(не показано 37 промежуточных версии 5 участников)
Строка 2: Строка 2:
 
Заменить '''ManagerXP2.exe''' на версию '''2.272.26''' от октября 2014 г. или более новую.
 
Заменить '''ManagerXP2.exe''' на версию '''2.272.26''' от октября 2014 г. или более новую.
  
−
==Создать таблицу ELNAKLS==
+
==Установить процедуру PR_ALTER_VIEW==
−
<nowiki> CREATE TABLE ELNAKLS(FILENAME DM_TEXT1024); </nowiki>
+
[[Изменения представления| '''PR_ALTER_VIEW''']]
  
−
==Создать таблицу EXT_DATA==
+
==Выполняем скрипт==
−
<nowiki>
+
−
CREATE TABLE EXT_DATA(ID DM_ID NOT NULL,
+
−
SNAME DM_TEXT1024,
+
−
ORIG_NAME DM_TEXT1024,
+
−
MGN_SOURCE DM_TEXT1024,
+
−
QUANT DM_DOUBLE,
+
−
PRICE DM_DOUBLE,
+
−
SUMMA DM_DOUBLE,
+
−
SDOC_TYPE DM_TEXT1024,
+
−
COMMITDATE DM_DATETIME,
+
−
DOC_TYPE DM_STATUS,
+
−
STATUS DM_STATUS,
+
−
MGN_ID DM_ID_NULL,
+
−
INSERTDT DM_DATETIME,
+
−
G$PROFILE_ID DM_ID_NULL);
+
  
−
CREATE SEQUENCE GEN_EXT_DATA_ID;
+
<pre>
−
ALTER SEQUENCE GEN_EXT_DATA_ID RESTART WITH 0;
+
  
−
CREATE INDEX EXT_DATA_IDX1 ON EXT_DATA (ID);
+
 
−
CREATE INDEX EXT_DATA_IDX2 ON EXT_DATA (SNAME);
+
SET TERM ^ ;
−
CREATE INDEX EXT_DATA_IDX3 ON EXT_DATA (MGN_ID);
+
 
 +
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');^
 +
 
 +
 
 +
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^
 +
 
 +
 
 +
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
 
  CREATE OR ALTER TRIGGER EXT_DATA_BI FOR EXT_DATA
Строка 36: Строка 57:
 
   if (new.id is null) then
 
   if (new.id is null) then
 
     new.id = gen_id(gen_ext_data_id,1);
 
     new.id = gen_id(gen_ext_data_id,1);
−
  end;
+
  end;^
−
</nowiki>
+
  
−
==Добавить в таблицу WARES поля и индексы==
 
−
<nowiki>
 
−
ALTER TABLE WARES ADD SNAME DM_TEXT1024;
 
−
ALTER TABLE WARES ADD MGN_NAME DM_TEXT1024;
 
−
ALTER TABLE WARES ADD MGN_ID DM_ID_NULL;
 
−
ALTER TABLE WARES ADD MGN_SOURCE DM_TEXT1024;
 
  
−
CREATE INDEX WARES_IDX3 ON WARES (MGN_NAME);
+
execute procedure PR_ALTER_VIEW('WARES','SNAME','DM_TEXT1024','TABLE');^
−
CREATE DESCENDING INDEX WARES_IDX4 ON WARES (MGN_NAME);
+
execute procedure PR_ALTER_VIEW('WARES','MGN_NAME','DM_TEXT1024','TABLE');^
−
CREATE INDEX WARES_IDX7 ON WARES (MGN_ID);
+
execute procedure PR_ALTER_VIEW('WARES','MGN_ID','DM_ID_NULL','TABLE'); ^
−
</nowiki>
+
execute procedure PR_ALTER_VIEW('WARES','MGN_SOURCE','DM_BLOBTEXT','TABLE');^
 +
execute procedure PR_ALTER_VIEW('WARES','MGN_SOURCE','DM_BLOBTEXT','TABLE');^
  
−
==В представления VW_DOC_DETAIL и VW_WARES добавить поля==
+
execute procedure PR_ALTER_VIEW('WARES','MGN_NAME','WARES_IDX3','INDEX');^
−
<nowiki>
+
execute procedure PR_ALTER_VIEW('WARES','MGN_NAME','WARES_IDX4','INDEX');^
−
MGN_NAME,
+
execute procedure PR_ALTER_VIEW('WARES','MGN_ID','WARES_IDX7','INDEX'); ^
−
MGN_ID,
+
−
MGN_SOURCE
+
−
</nowiki>
+
−
как
+
−
<nowiki>
+
−
w.mgn_name,
+
−
w.mgn_id,
+
−
w.mgn_source
+
−
</nowiki>
+
−
где w - таблица WARES
+
−
 
+
−
==В представление VW_DOC_DETAIL_ACTIVE добавить поля==
+
−
<nowiki>
+
−
MGN_NAME,
+
−
MGN_ID,
+
−
MGN_SOURCE
+
−
</nowiki>
+
−
как
+
−
<nowiki>
+
−
(select mgn_name from wares where id = da.ware_id) as mgn_name,
+
−
(select mgn_id from wares where id = da.ware_id) as mgn_id,
+
−
(select mgn_source from wares where id = da.ware_id) as mgn_source
+
−
</nowiki>
+
−
 
+
−
==В представление VW_WAREBASE добавить поле==
+
−
<nowiki>
+
−
MGN_NAME
+
−
</nowiki>
+
−
как
+
−
<nowiki>
+
−
(select mgn_name from wares where id = wb.ware_id)
+
−
</nowiki>
+
−
 
+
−
==Создать триггер WARES_BU_MGN==
+
−
<nowiki>
+
−
CREATE OR ALTER TRIGGER WARES_BU_MGN FOR WARES
+
−
ACTIVE BEFORE UPDATE POSITION 0
+
−
AS
+
−
begin
+
  
 +
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 (new.name_id <> old.name_id) then
 
   begin
 
   begin
Строка 99: Строка 79:
 
     new.mgn_id = 0;
 
     new.mgn_id = 0;
 
   end
 
   end
−
 
 
   new.sname = (select svalue from vals where id = new.name_id);
 
   new.sname = (select svalue from vals where id = new.name_id);
−
 
+
end;^
−
end;
+
  CREATE OR ALTER TRIGGER WARES_BI_SNAME FOR WARES
−
  </nowiki>
+
ACTIVE BEFORE INSERT POSITION 0
 +
AS
 +
begin
 +
    new.sname = (select svalue from vals where id = new.name_id);
 +
end;^
  
−
==В триггер WARES_BI_SNAME добавить строку==
 
−
<nowiki>
 
−
new.sname = (select svalue from vals where id = new.name_id);
 
−
</nowiki>
 
−
или создать его при отсутствии
 
−
<nowiki>
 
−
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;
 
−
</nowiki>
 
  
−
==Создать таблицу ORDER_DATA==
+
EXECUTE BLOCK AS BEGIN
−
<nowiki>
+
if (not exists(select 1 from rdb$relations where rdb$relation_name = 'ORDER_DATA')) then
−
CREATE TABLE ORDER_DATA (
+
execute statement 'CREATE TABLE ORDER_DATA (ID DM_ID NOT NULL);';
−
    ID                       DM_ID NOT NULL /* DM_ID = BIGINT */,
+
END^
−
    MGN_NAME                 DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','ID','','PRIMARY KEY');^
−
    ORDER_QUANT             DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
 
−
    SHORT_ORDER_QUANT       DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_NAME','DM_TEXT1024','TABLE');^
−
    OPTIMAL_ORDER_QUANT     DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','ORDER_QUANT','DM_DOUBLE','TABLE');^
−
    ASSORT_ORDER_QUANT       DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','SHORT_ORDER_QUANT','DM_DOUBLE','TABLE');^
−
    LAST_PRIHOD_QUANT       DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','OPTIMAL_ORDER_QUANT','DM_DOUBLE','TABLE');^
−
    REALQUANT               DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','ASSORT_ORDER_QUANT','DM_DOUBLE','TABLE');^
−
    LAST_PRIHOD_DATE         DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PRIHOD_QUANT','DM_DOUBLE','TABLE');^
−
    LAST_PRIHOD_PRICE       DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','REALQUANT','DM_DOUBLE','TABLE');^
−
    LAST_WEEK_SUM           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PRIHOD_DATE','DM_DATETIME','TABLE');^
−
    LAST_2WEEKS_SUM         DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PRIHOD_PRICE','DM_DOUBLE','TABLE');^
−
    LAST_PERIOD_SUM         DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_WEEK_SUM','DM_DOUBLE','TABLE');^
−
    LAST_MONTH_SUM           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_2WEEKS_SUM','DM_DOUBLE','TABLE');^
−
    SEASON_SUM               DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PERIOD_SUM','DM_DOUBLE','TABLE');^
−
    RASHOD_DOC_QUANT         DM_STATUS /* DM_STATUS = INTEGER */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_MONTH_SUM','DM_DOUBLE','TABLE');^
−
    PRIHOD_DOC_QUANT         DM_STATUS /* DM_STATUS = INTEGER */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','SEASON_SUM','DM_DOUBLE','TABLE');^
−
    LAST_OZ_DATE             DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','RASHOD_DOC_QUANT','DM_STATUS','TABLE');^
−
    ELNAKL_QUANT             DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','PRIHOD_DOC_QUANT','DM_STATUS','TABLE');^
−
    QUANT                   DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_OZ_DATE','DM_DATETIME','TABLE');^
−
    LAST_OZ_QUANT           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','ELNAKL_QUANT','DM_DOUBLE','TABLE');^
−
    ACTIVE_PRIHOD_DOC_QUANT DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','QUANT','DM_DOUBLE','TABLE');^
−
    LAST_OZ_PRICE           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_OZ_QUANT','DM_DOUBLE','TABLE');^
−
    LAST_RASHOD_DATE         DM_DATETIME,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','ACTIVE_PRIHOD_DOC_QUANT','DM_DOUBLE','TABLE');^
−
    ACTIVE_ORDER_QUANT     DM_DOUBLE,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_OZ_PRICE','DM_DOUBLE','TABLE');^
−
    MGN_ID                   DM_ID_NULL /* DM_ID_NULL = BIGINT */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_RASHOD_DATE','DM_DATETIME','TABLE');^
−
    BAD_FLAG                 DM_STATUS /* DM_STATUS = INTEGER */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','ACTIVE_ORDER_QUANT','DM_DOUBLE','TABLE');^
−
    INSERTDT                 DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_ID','DM_ID_NULL','TABLE');^
−
    G$PROFILE_ID     DM_ID_NULL
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','BAD_FLAG','DM_STATUS','TABLE');^
−
);
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','INSERTDT','DM_DATETIME','TABLE');^
−
CREATE GENERATOR GEN_ORDER_DATA_ID;
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','G$PROFILE_ID','DM_ID_NULL','TABLE');^
−
ALTER TABLE ORDER_DATA ADD CONSTRAINT PK_ORDER_DATA PRIMARY KEY (ID);
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','G$PROFILE_ID','DM_ID_NULL','TABLE');^
 +
 
 +
execute procedure PR_ALTER_VIEW('','','GEN_ORDER_DATA_ID','generator');^
  
−
CREATE INDEX ORDER_DATA_IDX1 ON ORDER_DATA (MGN_NAME);
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_NAME','ORDER_DATA_IDX1','INDEX');^
−
CREATE DESCENDING INDEX ORDER_DATA_IDX2 ON ORDER_DATA (MGN_NAME);
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_NAME','ORDER_DATA_IDX2','INDEX');^
−
CREATE DESCENDING INDEX ORDER_DATA_IDX3 ON ORDER_DATA (ID);
+
execute procedure PR_ALTER_VIEW('ORDER_DATA','ID','ORDER_DATA_IDX3','INDEX');^
  
 
  CREATE OR ALTER TRIGGER ORDER_DATA_BI FOR ORDER_DATA
 
  CREATE OR ALTER TRIGGER ORDER_DATA_BI FOR ORDER_DATA
Строка 165: Строка 137:
 
   if (new.id is null) then
 
   if (new.id is null) then
 
     new.id = gen_id(gen_order_data_id,1);
 
     new.id = gen_id(gen_order_data_id,1);
−
  end;
+
  end;^
−
</nowiki>
+
  
−
==Дополнительно==
 
−
1) Проверить, что индекс WARES_IDX1 уникален, если нет, то выполнить процедуру UPDPR_REPARWARES и сделать индекс уникальным (возможно потребуется монопольный доступ к БД).
 
−
Если таблицы OUT$ZAKAZ или DOC_DETAIL_ACTIVE_TREB в БД нет, создавать их не обязательно, можно закомментировать обращение к ним в UPDPR_REPARWARES.
 
−
 
−
ДЛЯ БД С ГЛОБАЛЬНЫМИ НАИМЕНОВАНИЯМИ:
 
−
 
−
<nowiki>
 
−
SET TERM ^ ;
 
  
−
create or alter procedure UPDPR_REPARWARES
+
create or alter procedure PR_INSTALL_UPDPR_REPARWARES
−
returns (
+
−
    ID DM_TEXT)
+
 
as
 
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
 
begin
−
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_ACTIVE_TREB set ware_id=:id where ware_id=:t_id;
 
−
update DOC_DETAIL_DELETED set ware_id=:id where ware_id=:t_id;
 
−
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^
 
  
−
SET TERM ; ^
+
  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;^
  
−
/* Following GRANT statetements are generated automatically */
+
GRANT EXECUTE ON PROCEDURE PR_INSTALL_UPDPR_REPARWARES TO SYSDBA;^
 +
execute procedure PR_INSTALL_UPDPR_REPARWARES;^
 +
DROP PROCEDURE PR_INSTALL_UPDPR_REPARWARES;^
  
−
GRANT SELECT,DELETE ON DOC_DETAIL_ACTIVE_LOG TO PROCEDURE UPDPR_REPARWARES;
+
create or alter procedure PR_INSTALL_PR_GET_WARE
−
GRANT SELECT,DELETE ON WARES TO PROCEDURE UPDPR_REPARWARES;
+
−
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE TO PROCEDURE UPDPR_REPARWARES;
+
−
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE_CANCELED TO PROCEDURE UPDPR_REPARWARES;
+
−
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE_NAMEID TO PROCEDURE UPDPR_REPARWARES;
+
−
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE_TREB TO PROCEDURE UPDPR_REPARWARES;
+
−
GRANT SELECT,UPDATE ON DOC_DETAIL_DELETED TO PROCEDURE UPDPR_REPARWARES;
+
−
GRANT SELECT,UPDATE ON OUT$ZAKAZ TO PROCEDURE UPDPR_REPARWARES;
+
−
GRANT SELECT,UPDATE ON PARTS TO PROCEDURE UPDPR_REPARWARES;
+
−
GRANT SELECT,UPDATE ON PARTS_LOG TO PROCEDURE UPDPR_REPARWARES;
+
−
GRANT SELECT,UPDATE ON WAREBASE TO PROCEDURE UPDPR_REPARWARES;
+
−
GRANT SELECT,UPDATE ON WAREBASEFOLDERS TO PROCEDURE UPDPR_REPARWARES;
+
−
GRANT SELECT,UPDATE ON WAREBASENAMEID TO PROCEDURE UPDPR_REPARWARES;
+
−
 
+
−
/* Existing privileges on this procedure */
+
−
 
+
−
GRANT EXECUTE ON PROCEDURE UPDPR_REPARWARES TO SYSDBA;
+
−
</nowiki>
+
−
 
+
−
 
+
−
ДЛЯ БД БЕЗ ГЛОБАЛЬНЫХ НАИМЕНОВАНИЙ:
+
−
 
+
−
<nowiki>
+
−
SET TERM ^ ;
+
−
 
+
−
create or alter procedure UPDPR_REPARWARES
+
−
returns (
+
−
    ID DM_TEXT)
+
 
as
 
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
 
begin
−
delete from DOC_DETAIL_ACTIVE_LOG;
+
  if ((
−
for select min(id), NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID from wares
+
    select
−
group by NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID
+
    upper(rf.rdb$field_source)
−
having count(1)>1
+
    from RDB$RELATION_FIELDS rf
−
into id, NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID do
+
    where 1=1
−
begin
+
    and rf.rdb$field_name='ID'
−
for
+
    and rf.rdb$relation_name='WARES')='DM_UUID'
−
select id from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE
+
  ) then
−
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
+
  EXECUTE STATEMENT ('
−
begin
+
    create or alter procedure PR_GET_WARE (
−
update DOC_DETAIL_ACTIVE set ware_id=:id where ware_id=:t_id;
+
        SNAME type of DM_TEXT,
−
update DOC_DETAIL_ACTIVE_CANCELED set ware_id=:id where ware_id=:t_id;
+
        SIZG type of DM_TEXT,
−
-- update DOC_DETAIL_ACTIVE_LOG set ware_id=:id where ware_id=:t_id;
+
        SCOUNTRY type of DM_TEXT,
−
update DOC_DETAIL_ACTIVE_NAMEID set ware_id=:id where ware_id=:t_id;
+
        ORIG_CODE type of DM_TEXT,
−
update DOC_DETAIL_ACTIVE_TREB set ware_id=:id where ware_id=:t_id;
+
        SORIG_NAME type of DM_TEXT,
−
update DOC_DETAIL_DELETED set ware_id=:id where ware_id=:t_id;
+
        SORIG_IZG type of DM_TEXT,
−
update OUT$ZAKAZ set ware_id=:id where ware_id=:t_id;
+
        SORIG_COUNTRY type of DM_TEXT,
−
update PARTS set ware_id=:id where ware_id=:t_id;
+
        BARCODE type of DM_TEXT,
−
update PARTS_LOG set ware_id=:id where ware_id=:t_id;
+
        Z_ID type of DM_ID,
−
update WAREBASE set ware_id=:id where ware_id=:t_id;
+
        SKLAD_ID DM_TEXT,
−
update WAREBASEFOLDERS set ware_id=:id where ware_id=:t_id;
+
        ALTTYPE DM_STATUS,
−
update WAREBASENAMEID set ware_id=:id where ware_id=:t_id;
+
        MNN DM_TEXT = '''')
−
delete from wares where id=:t_id;
+
    returns (
−
end
+
        W_ID type of DM_UUID_NULL)
−
--    suspend;
+
    as
−
/*    delete from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE
+
    declare variable OLD_BARCODE DM_TEXT1024;
−
and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id;
+
    declare variable NAME_ID type of DM_UUID_NULL;
−
suspend;
+
    declare variable IZG_ID type of DM_UUID_NULL;
−
*/
+
    declare variable COUNTRY_ID type of DM_UUID_NULL;
−
end
+
    declare variable ORIG_NAME_ID type of DM_UUID_NULL;
−
-- suspend;
+
    declare variable ORIG_IZG_ID type of DM_UUID_NULL;
−
end^
+
    declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
−
 
+
    begin
−
SET TERM ; ^
+
    --BASE WITH GLOBAL ID
−
 
+
    if (orig_code is null) then orig_code='''';
−
/* Following GRANT statetements are generated automatically */
+
    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;
−
GRANT SELECT,DELETE ON DOC_DETAIL_ACTIVE_LOG TO PROCEDURE UPDPR_REPARWARES;
+
    select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id;
−
GRANT SELECT,DELETE ON WARES TO PROCEDURE UPDPR_REPARWARES;
+
    select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
−
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE TO PROCEDURE UPDPR_REPARWARES;
+
    select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
−
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE_CANCELED TO PROCEDURE UPDPR_REPARWARES;
+
    select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
−
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE_NAMEID TO PROCEDURE UPDPR_REPARWARES;
+
    select id, barcode from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and
−
GRANT SELECT,UPDATE ON DOC_DETAIL_DELETED TO PROCEDURE UPDPR_REPARWARES;
+
    ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and
−
GRANT SELECT,UPDATE ON OUT$ZAKAZ TO PROCEDURE UPDPR_REPARWARES;
+
    ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id into :w_id, :old_barcode;
−
GRANT SELECT,UPDATE ON PARTS TO PROCEDURE UPDPR_REPARWARES;
+
    if (w_id is null) then
−
GRANT SELECT,UPDATE ON PARTS_LOG TO PROCEDURE UPDPR_REPARWARES;
+
    begin
−
GRANT SELECT,UPDATE ON WAREBASE TO PROCEDURE UPDPR_REPARWARES;
+
    --   exception EX_WRONG_OPER;
−
GRANT SELECT,UPDATE ON WAREBASEFOLDERS TO PROCEDURE UPDPR_REPARWARES;
+
    --w_id=gen_id(gen_wares_id,1);
−
GRANT SELECT,UPDATE ON WAREBASENAMEID TO PROCEDURE UPDPR_REPARWARES;
+
    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)
−
/* Existing privileges on this procedure */
+
    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);
−
GRANT EXECUTE ON PROCEDURE UPDPR_REPARWARES TO SYSDBA;
+
    end
−
</nowiki>
+
    else if (old_barcode <> barcode) then update wares set barcode = :barcode where id = :w_id;
−
 
+
    suspend;
−
 
+
    end;
−
<b> 2) обновить PR_GET_WARE (при необходимости) </b>
+
  ');
−
 
+
else
−
ДЛЯ БД С ГЛОБАЛЬНЫМИ НАИМЕНОВАНИЯМИ:
+
  EXECUTE STATEMENT ('
−
 
+
−
<nowiki>
+
−
SET TERM ^ ;
+
−
 
+
 
create or alter procedure PR_GET_WARE (
 
create or alter procedure PR_GET_WARE (
−
    SNAME type of DM_TEXT,
+
        SNAME type of DM_TEXT,
−
    SIZG type of DM_TEXT,
+
        SIZG type of DM_TEXT,
−
    SCOUNTRY type of DM_TEXT,
+
        SCOUNTRY type of DM_TEXT,
−
    ORIG_CODE type of DM_TEXT,
+
        ORIG_CODE type of DM_TEXT,
−
    SORIG_NAME type of DM_TEXT,
+
        SORIG_NAME type of DM_TEXT,
−
    SORIG_IZG type of DM_TEXT,
+
        SORIG_IZG type of DM_TEXT,
−
    SORIG_COUNTRY type of DM_TEXT,
+
        SORIG_COUNTRY type of DM_TEXT,
−
    BARCODE type of DM_TEXT,
+
        BARCODE type of DM_TEXT,
−
    Z_ID type of DM_ID,
+
        Z_ID type of DM_ID,
−
    SKLAD_ID DM_TEXT,
+
        SKLAD_ID DM_TEXT,
−
    ALTTYPE DM_STATUS,
+
        ALTTYPE DM_STATUS,
−
    MNN DM_TEXT = '')
+
        MNN DM_TEXT = '''')
−
returns (
+
    returns (
−
    W_ID type of DM_UUID_NULL)
+
        W_ID type of DM_ID)
−
as
+
    as
−
declare variable OLD_BARCODE DM_TEXT1024;
+
    declare variable OLD_BARCODE DM_TEXT1024;
−
declare variable NAME_ID type of DM_UUID_NULL;
+
    declare variable NAME_ID type of DM_ID;
−
declare variable IZG_ID type of DM_UUID_NULL;
+
    declare variable IZG_ID type of DM_ID;
−
declare variable COUNTRY_ID type of DM_UUID_NULL;
+
    declare variable COUNTRY_ID type of DM_ID;
−
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
+
    declare variable ORIG_NAME_ID type of DM_ID;
−
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
+
    declare variable ORIG_IZG_ID type of DM_ID;
−
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
+
    declare variable ORIG_COUNTRY_ID type of DM_ID;
−
begin
+
    begin
−
if (orig_code is null) then orig_code='';
+
    --BASE WITHOUT GLOBAL ID
−
select val_id from pr_getval_id(:sname,0,:alttype,:mnn) into :name_id;
+
    if (orig_code is null) then orig_code='''';
−
select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id;
+
    select val_id from pr_getval_id(:sname,0,:alttype,:mnn) into :name_id;
−
select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id;
+
    select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id;
−
select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
+
    select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id;
−
select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
+
    select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
−
select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
+
    select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
−
select id, barcode from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and
+
    select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
−
ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and
+
    select id, barcode from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and
−
ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id into :w_id, :old_barcode;
+
    ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and
−
if (w_id is null) then
+
    ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id into :w_id, :old_barcode;
−
begin
+
    if (w_id is null) then
−
--    exception EX_WRONG_OPER;
+
    begin
−
--w_id=gen_id(gen_wares_id,1);
+
    --    exception EX_WRONG_OPER;
−
w_id=UUID_TO_CHAR(GEN_UUID());
+
    w_id=gen_id(gen_wares_id,1);
−
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)
+
    --w_id=UUID_TO_CHAR(GEN_UUID());
−
values
+
    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)
−
(:W_ID,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,:BARCODE,:Z_ID,:SKLAD_ID);
+
    values
−
end
+
    (:W_ID,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,:BARCODE,:Z_ID,:SKLAD_ID);
−
else if (old_barcode <> barcode) then update wares set barcode = :barcode where id = :w_id;
+
    end
−
suspend;
+
    else if (old_barcode <> barcode) then update wares set barcode = :barcode where id = :w_id;
−
end^
+
    suspend;
 +
    end;
 +
  ');
 +
end;^
  
−
SET TERM ; ^
+
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;^
  
−
/* Following GRANT statetements are generated automatically */
+
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
  
−
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_GET_WARE;
+
[freereport]
−
GRANT SELECT,INSERT,UPDATE ON WARES TO PROCEDURE PR_GET_WARE;
+
enabled=1
 +
caption=Печать пакета документов
 +
tmplpath=zakaz_opt.cffr3
  
−
/* Existing privileges on this procedure */
+
[dbf]
 +
inipath=dbf.ini
 +
'', NULL, 1);';
 +
END;^
  
−
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
+
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MGN_NAME','w.mgn_name');^
−
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO "PUBLIC";
+
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MGN_ID','w.mgn_id');^
−
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO STANDART;
+
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MGN_SOURCE','cast(w.mgn_source as dm_text_big)');^
−
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO SYSDBA;
+
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_NAME','w.mgn_name');^
−
</nowiki>
+
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)');^
  
−
 
−
 
−
ДЛЯ БД БЕЗ ГЛОБАЛЬНЫХ НАИМЕНОВАНИЙ:
 
−
 
−
<nowiki>
 
−
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,
 
−
    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
 
−
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^
 
  
 
SET TERM ; ^
 
SET TERM ; ^
  
−
/* Following GRANT statetements are generated automatically */
 
  
−
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_GET_WARE;
 
−
GRANT SELECT,INSERT,UPDATE ON WARES TO PROCEDURE PR_GET_WARE;
 
  
−
/* Existing privileges on this procedure */
+
</pre>
  
−
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
+
==Дополнительно==
−
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO "PUBLIC";
+
Проверить, что индекс WARES_IDX1 уникален, если нет, то выполнить процедуру UPDPR_REPARWARES и сделать индекс уникальным (возможно потребуется монопольный доступ к БД).
−
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO STANDART;
+
Если таблицы OUT$ZAKAZ или DOC_DETAIL_ACTIVE_TREB в БД нет, создавать их не обязательно, можно закомментировать обращение к ним в UPDPR_REPARWARES.
−
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO SYSDBA;
+
−
</nowiki>
+
−
 
+
−
==Создаем тип документа "Заказ оптовику" (при необходимости)==
+
−
 
+
−
Если полей D$UUID, D$SRVUPDDT нет в целевой базе - удалите их из запроса.
+
−
<nowiki>
+
−
INSERT INTO DOC_TYPES (ID, CAPTION, INSERTDT, BASE_TYPE, STATINI, REPORTS, VTYPE, PACKET, D$UUID, D$SRVUPDDT) VALUES (36, 'Заказ оптовику', '6-DEC-2014 16:38:29.709', 4, NULL, NULL, 1, 1, '6BE0D5F3-FF8C-43CA-B5A2-751621FF02B3', '17-JAN-2000 00:00:00');
+
−
 
+
−
для поля statini пишем содержимое:
+
−
[main]
+
−
koef=0
+
−
 
+
−
[freereport]
+
−
enabled=1
+
−
caption=Печать пакета документов
+
−
tmplpath=zakaz_opt.cffr3
+
−
 
+
−
[dbf]
+
−
inipath=dbf.ini
+
−
</nowiki>
+
  
 
==Сетки и печатная форма ==
 
==Сетки и печатная форма ==
Строка 489: Строка 453:
  
 
==Проверка себя==
 
==Проверка себя==
−
  В Менеджере, перед проверкой убедитесь, что в '''"Сервис"-"Параметры системы"-"Глобальный справочник"''' указаны корректные параметры '''"Строка подключения Общего заказа"''' и '''"Путь к Общему заказу"''' (при наличии программы Общий заказ).
+
В Менеджере, перед проверкой убедитесь, что в '''"Сервис"-"Параметры системы"-"Глобальный справочник"''' указаны корректные параметры '''"Строка подключения Общего заказа"''' (пример: localhost:C:\Program Files\ZClientXP4\ZBASE.FDB) и '''"Путь к Общему заказу"''' (пример: C:\Program Files\ZClientXP4\) (при наличии программы Общий заказ).
 
   После чего в меню '''"Сервис-Заказ"''' открываем окно Автозаказа и нажимаем кнопку '''"Рассчитать заказ"''', после расчета мы должны увидеть позиции в кратком, оптимальном и ассортиментном заказе. Пример внешнего вида см. ниже.
 
   После чего в меню '''"Сервис-Заказ"''' открываем окно Автозаказа и нажимаем кнопку '''"Рассчитать заказ"''', после расчета мы должны увидеть позиции в кратком, оптимальном и ассортиментном заказе. Пример внешнего вида см. ниже.
 
   Проведите любой документ, убедитесь, что исправления в базе прошли корректно.
 
   Проведите любой документ, убедитесь, что исправления в базе прошли корректно.
 +
 +
  *Cмотрим таблицу vals, если поле id имеет тип bigint (dm_id) - это обычные наименования, если DM_UUID - это глобальные
  
 
  Теги: автозаказ, автозаявка, заказ, заявка
 
  Теги: автозаказ, автозаявка, заказ, заявка
  
 
[[Файл:Автозаказ_внешний_вид.png|left|Окно программы]]
 
[[Файл:Автозаказ_внешний_вид.png|left|Окно программы]]

Текущая версия на 19:23, 24 августа 2017

Заменить версию Менеджера

Заменить ManagerXP2.exe на версию 2.272.26 от октября 2014 г. или более новую.

Установить процедуру PR_ALTER_VIEW

PR_ALTER_VIEW

Выполняем скрипт



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


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^


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


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

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


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

GRANT EXECUTE ON PROCEDURE PR_INSTALL_UPDPR_REPARWARES TO SYSDBA;^
execute procedure PR_INSTALL_UPDPR_REPARWARES;^
DROP PROCEDURE PR_INSTALL_UPDPR_REPARWARES;^

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

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

Дополнительно

Проверить, что индекс WARES_IDX1 уникален, если нет, то выполнить процедуру UPDPR_REPARWARES и сделать индекс уникальным (возможно потребуется монопольный доступ к БД). Если таблицы OUT$ZAKAZ или DOC_DETAIL_ACTIVE_TREB в БД нет, создавать их не обязательно, можно закомментировать обращение к ним в UPDPR_REPARWARES.

Сетки и печатная форма

1) Сетка автозаказа: Прикрепленный файл Файл:Сетка автозаказа.zip

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

3) Печатная форма для заказа оптовику: Прикрепленный файл Файл:Zakaz opt.zip

Проверка себя

В Менеджере, перед проверкой убедитесь, что в "Сервис"-"Параметры системы"-"Глобальный справочник" указаны корректные параметры "Строка подключения Общего заказа" (пример: localhost:C:\Program Files\ZClientXP4\ZBASE.FDB) и "Путь к Общему заказу" (пример: C:\Program Files\ZClientXP4\) (при наличии программы Общий заказ). 

 После чего в меню "Сервис-Заказ" открываем окно Автозаказа и нажимаем кнопку "Рассчитать заказ", после расчета мы должны увидеть позиции в кратком, оптимальном и ассортиментном заказе. Пример внешнего вида см. ниже.
 Проведите любой документ, убедитесь, что исправления в базе прошли корректно.

 *Cмотрим таблицу vals, если поле id имеет тип bigint (dm_id) - это обычные наименования, если DM_UUID - это глобальные

Теги: автозаказ, автозаявка, заказ, заявка
Окно программы
Источник — «http://wiki.standart-n.ru/index.php?title=Установка_автозаказа&oldid=6071»