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

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎Выполняем скрипт)
Строка 4: Строка 4:
 
==Выполняем скрипт==
 
==Выполняем скрипт==
  
−
<pre>
+
<code>
  
 
SET TERM ^ ;
 
SET TERM ^ ;
Строка 730: Строка 730:
 
SET TERM ; ^
 
SET TERM ; ^
  
−
</pre>
+
</code>
  
 
==Дополнительно==
 
==Дополнительно==

Версия 17:39, 14 июня 2016

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

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

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

SET TERM ^ ; --создание доменов если их нет EXECUTE BLOCK AS BEGIN if (not exists(select f.rdb$field_name, t.rdb$type_name from rdb$fields f, rdb$types t where f.rdb$field_name='DM_ID_NULL' and f.rdb$field_type=t.rdb$type and t.rdb$field_name='RDB$FIELD_TYPE')) then execute statement 'CREATE DOMAIN DM_ID_NULL AS BIGINT;'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select f.rdb$field_name, t.rdb$type_name from rdb$fields f, rdb$types t where f.rdb$field_name='DM_ID' and f.rdb$field_type=t.rdb$type and t.rdb$field_name='RDB$FIELD_TYPE')) then execute statement 'CREATE DOMAIN DM_ID AS BIGINT NOT NULL;'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select f.rdb$field_name, t.rdb$type_name from rdb$fields f, rdb$types t where f.rdb$field_name='DM_STATUS' and f.rdb$field_type=t.rdb$type and t.rdb$field_name='RDB$FIELD_TYPE')) then execute statement 'CREATE DOMAIN DM_STATUS AS INTEGER;'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select f.rdb$field_name, t.rdb$type_name from rdb$fields f, rdb$types t where f.rdb$field_name='DM_DATETIME' and f.rdb$field_type=t.rdb$type and t.rdb$field_name='RDB$FIELD_TYPE')) then execute statement 'CREATE DOMAIN DM_DATETIME AS TIMESTAMP;'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select f.rdb$field_name, t.rdb$type_name from rdb$fields f, rdb$types t where f.rdb$field_name='DM_DOUBLE' and f.rdb$field_type=t.rdb$type and t.rdb$field_name='RDB$FIELD_TYPE')) then execute statement 'CREATE DOMAIN DM_TEXT1024 AS VARCHAR(1024) CHARACTER SET WIN1251 COLLATE WIN1251 ;'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select f.rdb$field_name, t.rdb$type_name from rdb$fields f, rdb$types t where f.rdb$field_name='DM_TEXT1024' and f.rdb$field_type=t.rdb$type and t.rdb$field_name='RDB$FIELD_TYPE')) then execute statement 'CREATE DOMAIN DM_DOUBLE AS DOUBLE PRECISION;'; END^

--изменение вьюх create or alter procedure PR_ALTER_VIEW ( 

   VW_NAME DM_TEXT,
   NEW_VW_FIELD DM_TEXT,
   NEW_VW_VALUE DM_TEXT,
   NEW_VW_JOIN DM_TEXT = null)

as declare variable I DM_ID; declare variable VW_BODY DM_TEXT_BIG; declare variable VW_FIELDS DM_TEXT_BIG; declare variable J DM_ID; declare variable STR DM_TEXT_BIG; declare variable L integer; begin 

 if (:NEW_VW_JOIN is null) then
   NEW_VW_JOIN = ;
 select list(trim(RDB$FIELD_NAME))
 from (select RC.RDB$FIELD_NAME
       from RDB$RELATION_FIELDS RC
       where RC.RDB$RELATION_NAME = upper(:VW_NAME)
       order by RC.RDB$FIELD_POSITION)
 into :VW_FIELDS;
 select R.RDB$VIEW_SOURCE
 from RDB$RELATIONS R
 where R.RDB$RELATION_NAME = upper(:VW_NAME)
 into :VW_BODY;

 I = 0;
 J = 0;
 if ((select count(*)
      from RDB$RELATION_FIELDS RF
      where RF.RDB$RELATION_NAME = upper(:VW_NAME) and
            RF.RDB$FIELD_NAME = upper(:NEW_VW_FIELD)) = 0) then
 begin
   while (position('FROM', UPPER(:VW_BODY), :J + 1) <> 0) do
   begin
     J = position('FROM', UPPER(:VW_BODY), :J + 1);
     I = :I + 1;
   end

   if (:I = 1) then --один основной from, т.е. скрипт без подзапросов
   begin
     execute statement 'CREATE OR ALTER VIEW ' || upper(:VW_NAME) || '( ' || :VW_FIELDS || ',' || upper(:NEW_VW_FIELD) || ' ) as ' || substring(:VW_BODY from 1 for :J - 1) || ' ,' || upper(:NEW_VW_VALUE) || ' ' || substring(:VW_BODY from :J for char_length(:VW_BODY)) || ;
   end
   else
   begin --есть подзапросы - нужно распарсить и найти основной from
     J = 0;
     while (position('FROM', UPPER(:VW_BODY), :J + 1) <> 0) do     --находим сколько всего from'ов в скрипте
     begin
       I = 0;
       J = position('FROM', UPPER(:VW_BODY), :J + 1);
       L = 0;
       STR = substring(:VW_BODY from L + 1 for :J - 1);
       while (position('(', STR, :L + 1) <> 0) do       --считаем количество открывающих скобок
       begin
         L = position('(', STR, :L + 1);
         I = :I + 1;
       end

       L = 0;
       while (position(')', STR, :L + 1) <> 0) do       --считаем количество закрывающих скобок
       begin
         L = position(')', STR, :L + 1);
         I = :I - 1;
       end

       if (:I = 0) then --если открывающих и закрывающих поровну - значит этот from основной, если конечно в комментах нет лишних скобок
             execute statement 'CREATE OR ALTER VIEW ' || upper(:VW_NAME) || '( ' || :VW_FIELDS || ',' || upper(:NEW_VW_FIELD) || ' ) as ' || substring(:VW_BODY from 1 for :J - 1) || ' ,' || upper(:NEW_VW_VALUE) || ' ' || substring(:VW_BODY from :J for char_length(:VW_BODY)) || ;
     end
   end
  end

end^ GRANT EXECUTE ON PROCEDURE PR_ALTER_VIEW TO SYSDBA;^

--таблица 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 BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'SNAME')) then execute statement 'ALTER TABLE EXT_DATA ADD SNAME DM_TEXT1024'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'ORIG_NAME')) then execute statement 'ALTER TABLE EXT_DATA ADD ORIG_NAME DM_TEXT1024'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'MGN_SOURCE')) then execute statement 'ALTER TABLE EXT_DATA ADD MGN_SOURCE DM_TEXT1024'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'QUANT')) then execute statement 'ALTER TABLE EXT_DATA ADD QUANT DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'PRICE')) then execute statement 'ALTER TABLE EXT_DATA ADD PRICE DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'SUMMA')) then execute statement 'ALTER TABLE EXT_DATA ADD SUMMA DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'SDOC_TYPE')) then execute statement 'ALTER TABLE EXT_DATA ADD SDOC_TYPE DM_TEXT1024'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'COMMITDATE')) then execute statement 'ALTER TABLE EXT_DATA ADD COMMITDATE DM_DATETIME'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'DOC_TYPE')) then execute statement 'ALTER TABLE EXT_DATA ADD DOC_TYPE DM_STATUS'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'STATUS')) then execute statement 'ALTER TABLE EXT_DATA ADD STATUS DM_STATUS'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'MGN_ID')) then execute statement 'ALTER TABLE EXT_DATA ADD MGN_ID DM_ID_NULL'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'INSERTDT')) then execute statement 'ALTER TABLE EXT_DATA ADD INSERTDT DM_DATETIME'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'EXT_DATA' and rf.RDB$FIELD_NAME = 'G$PROFILE_ID')) then execute statement 'ALTER TABLE EXT_DATA ADD G$PROFILE_ID DM_ID_NULL'; END^ EXECUTE BLOCK AS BEGIN if (not exists(SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG=0 and RDB$GENERATOR_NAME = 'GEN_EXT_DATA_ID')) then execute statement 'CREATE SEQUENCE GEN_EXT_DATA_ID; ALTER SEQUENCE GEN_EXT_DATA_ID RESTART WITH 0;'; END^ EXECUTE BLOCK AS BEGIN if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME='EXT_DATA_IDX1')) then execute statement 'CREATE INDEX EXT_DATA_IDX1 ON EXT_DATA (ID);'; END^ EXECUTE BLOCK AS BEGIN if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME='EXT_DATA_IDX2')) then execute statement 'CREATE INDEX EXT_DATA_IDX2 ON EXT_DATA (SNAME);'; END^ EXECUTE BLOCK AS BEGIN if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME='EXT_DATA_IDX3')) then execute statement 'CREATE INDEX EXT_DATA_IDX3 ON EXT_DATA (MGN_ID);'; END^ 

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 BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'WARES' and rf.RDB$FIELD_NAME = 'SNAME')) then execute statement 'ALTER TABLE WARES ADD SNAME DM_TEXT1024'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'WARES' and rf.RDB$FIELD_NAME = 'MGN_NAME')) then execute statement 'ALTER TABLE WARES ADD MGN_NAME DM_TEXT1024'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'WARES' and rf.RDB$FIELD_NAME = 'MGN_ID')) then execute statement 'ALTER TABLE WARES ADD MGN_ID DM_ID_NULL'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'WARES' and rf.RDB$FIELD_NAME = 'MGN_SOURCE')) then execute statement 'ALTER TABLE WARES ADD MGN_SOURCE DM_BLOBTEXT'; END^ EXECUTE BLOCK AS BEGIN if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME='WARES_IDX3')) then execute statement 'CREATE INDEX WARES_IDX3 ON WARES (MGN_NAME);'; END^ EXECUTE BLOCK AS BEGIN if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME='WARES_IDX4')) then execute statement 'CREATE DESCENDING INDEX WARES_IDX4 ON WARES (MGN_NAME);'; END^ EXECUTE BLOCK AS BEGIN if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME='WARES_IDX7')) then execute statement 'CREATE INDEX WARES_IDX7 ON WARES (MGN_ID);'; END^ 

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 BLOCK AS BEGIN if (not exists(SELECT s.rdb$field_name FROM rdb$index_segments AS s LEFT JOIN rdb$relation_constraints AS rc ON (rc.rdb$index_name = s.rdb$index_name) WHERE rc.rdb$constraint_name = 'PK_ORDER_DATA' AND rc.rdb$constraint_type = 'PRIMARY KEY')) then execute statement 'ALTER TABLE ORDER_DATA ADD CONSTRAINT PK_ORDER_DATA PRIMARY KEY (ID);'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'MGN_NAME')) then execute statement 'ALTER TABLE ORDER_DATA ADD MGN_NAME DM_TEXT1024'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'ORDER_QUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD ORDER_QUANT DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'SHORT_ORDER_QUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD SHORT_ORDER_QUANT DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'OPTIMAL_ORDER_QUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD OPTIMAL_ORDER_QUANT DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'ASSORT_ORDER_QUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD ASSORT_ORDER_QUANT DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'LAST_PRIHOD_QUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD LAST_PRIHOD_QUANT DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'REALQUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD REALQUANT DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'LAST_PRIHOD_DATE')) then execute statement 'ALTER TABLE ORDER_DATA ADD LAST_PRIHOD_DATE DM_DATETIME'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'LAST_PRIHOD_PRICE')) then execute statement 'ALTER TABLE ORDER_DATA ADD LAST_PRIHOD_PRICE DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'LAST_WEEK_SUM')) then execute statement 'ALTER TABLE ORDER_DATA ADD LAST_WEEK_SUM DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'LAST_2WEEKS_SUM')) then execute statement 'ALTER TABLE ORDER_DATA ADD LAST_2WEEKS_SUM DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'LAST_PERIOD_SUM')) then execute statement 'ALTER TABLE ORDER_DATA ADD LAST_PERIOD_SUM DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'LAST_MONTH_SUM')) then execute statement 'ALTER TABLE ORDER_DATA ADD LAST_MONTH_SUM DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'SEASON_SUM')) then execute statement 'ALTER TABLE ORDER_DATA ADD SEASON_SUM DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'RASHOD_DOC_QUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD RASHOD_DOC_QUANT DM_STATUS'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'PRIHOD_DOC_QUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD PRIHOD_DOC_QUANT DM_STATUS'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'LAST_OZ_DATE')) then execute statement 'ALTER TABLE ORDER_DATA ADD LAST_OZ_DATE DM_DATETIME'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'ELNAKL_QUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD ELNAKL_QUANT DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'QUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD QUANT DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'LAST_OZ_QUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD LAST_OZ_QUANT DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'ACTIVE_PRIHOD_DOC_QUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD ACTIVE_PRIHOD_DOC_QUANT DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'LAST_OZ_PRICE')) then execute statement 'ALTER TABLE ORDER_DATA ADD LAST_OZ_PRICE DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'LAST_RASHOD_DATE')) then execute statement 'ALTER TABLE ORDER_DATA ADD LAST_RASHOD_DATE DM_DATETIME'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'ACTIVE_ORDER_QUANT')) then execute statement 'ALTER TABLE ORDER_DATA ADD ACTIVE_ORDER_QUANT DM_DOUBLE'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'MGN_ID')) then execute statement 'ALTER TABLE ORDER_DATA ADD MGN_ID DM_ID_NULL'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'BAD_FLAG')) then execute statement 'ALTER TABLE ORDER_DATA ADD BAD_FLAG DM_STATUS'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'INSERTDT')) then execute statement 'ALTER TABLE ORDER_DATA ADD INSERTDT DM_DATETIME'; END^ EXECUTE BLOCK AS BEGIN if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'ORDER_DATA' and rf.RDB$FIELD_NAME = 'G$PROFILE_ID')) then execute statement 'ALTER TABLE ORDER_DATA ADD G$PROFILE_ID DM_ID_NULL'; END^ EXECUTE BLOCK AS BEGIN if (not exists(SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG=0 and RDB$GENERATOR_NAME = 'GEN_ORDER_DATA_ID')) then execute statement 'CREATE GENERATOR GEN_ORDER_DATA_ID;'; END^ EXECUTE BLOCK AS BEGIN if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME='ORDER_DATA_IDX1')) then execute statement 'CREATE INDEX ORDER_DATA_IDX1 ON ORDER_DATA (MGN_NAME);'; END^ EXECUTE BLOCK AS BEGIN if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME='ORDER_DATA_IDX2')) then execute statement 'CREATE DESCENDING INDEX ORDER_DATA_IDX2 ON ORDER_DATA (MGN_NAME);'; END^ EXECUTE BLOCK AS BEGIN if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME='ORDER_DATA_IDX3')) then execute statement 'CREATE DESCENDING INDEX ORDER_DATA_IDX3 ON ORDER_DATA (ID);'; END^ 

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

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

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


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

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

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

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

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

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

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

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