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

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎Выполняем скрипт)
Строка 6: Строка 6:
 
<pre>
 
<pre>
  
−
/* ELNAKLS */
+
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 TABLE ELNAKLS(FILENAME DM_TEXT1024);
+
--изменение вьюх
 +
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
  
−
/* EXT_DATA */
+
    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
  
−
CREATE TABLE EXT_DATA(ID DM_ID NOT NULL,
+
        L = 0;
−
SNAME DM_TEXT1024,
+
        while (position(')', STR, :L + 1) <> 0) do      --считаем количество закрывающих скобок
−
ORIG_NAME DM_TEXT1024,
+
        begin
−
MGN_SOURCE DM_TEXT1024,
+
          L = position(')', STR, :L + 1);
−
QUANT DM_DOUBLE,
+
          I = :I - 1;
−
PRICE DM_DOUBLE,
+
        end
−
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;
+
        if (:I = 0) then --если открывающих и закрывающих поровну - значит этот from основной, если конечно в комментах нет лишних скобок
−
ALTER SEQUENCE GEN_EXT_DATA_ID RESTART WITH 0;
+
              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;^
  
−
CREATE INDEX EXT_DATA_IDX1 ON EXT_DATA (ID);
+
--таблица ELNAKLS
−
CREATE INDEX EXT_DATA_IDX2 ON EXT_DATA (SNAME);
+
EXECUTE BLOCK AS BEGIN
−
CREATE INDEX EXT_DATA_IDX3 ON EXT_DATA (MGN_ID);
+
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
 
  CREATE OR ALTER TRIGGER EXT_DATA_BI FOR EXT_DATA
 
  ACTIVE BEFORE INSERT POSITION 0
 
  ACTIVE BEFORE INSERT POSITION 0
Строка 42: Строка 207:
 
   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;^
−
 
+
−
 
+
−
/* Добавить в таблицу WARES поля и индексы */
+
−
 
+
−
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_BLOBTEXT; /* A.K. DM_TEXT_BIG;  aleksnick 16.03.2015 DM_TEXT1024; */
+
−
 
+
−
CREATE INDEX WARES_IDX3 ON WARES (MGN_NAME);
+
−
CREATE DESCENDING INDEX WARES_IDX4 ON WARES (MGN_NAME);
+
−
CREATE INDEX WARES_IDX7 ON WARES (MGN_ID);
+
−
 
+
−
 
+
−
/* WARES_BU_MGN */
+
−
 
+
−
CREATE OR ALTER TRIGGER WARES_BU_MGN FOR WARES
+
−
ACTIVE BEFORE UPDATE POSITION 0
+
−
AS
+
−
begin
+
  
 +
--добавляем колонки в таблицу 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
 
   if (new.name_id <> old.name_id) then
 
   begin
 
   begin
Строка 69: Строка 251:
 
     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
−
 
+
ACTIVE BEFORE INSERT POSITION 0
−
 
+
AS
−
/* ORDER_DATA */
+
  begin
−
 
+
     new.sname = (select svalue from vals where id = new.name_id);
−
CREATE TABLE ORDER_DATA (
+
  end;^
−
    ID                      DM_ID NOT NULL /* DM_ID = BIGINT */,
+
−
    MGN_NAME                DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
+
−
    ORDER_QUANT              DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    SHORT_ORDER_QUANT        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    OPTIMAL_ORDER_QUANT      DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    ASSORT_ORDER_QUANT      DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    LAST_PRIHOD_QUANT        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    REALQUANT                DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    LAST_PRIHOD_DATE        DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
+
−
    LAST_PRIHOD_PRICE        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    LAST_WEEK_SUM            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    LAST_2WEEKS_SUM          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    LAST_PERIOD_SUM          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    LAST_MONTH_SUM          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    SEASON_SUM              DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    RASHOD_DOC_QUANT        DM_STATUS /* DM_STATUS = INTEGER */,
+
−
    PRIHOD_DOC_QUANT        DM_STATUS /* DM_STATUS = INTEGER */,
+
−
    LAST_OZ_DATE            DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
+
−
    ELNAKL_QUANT            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    QUANT                    DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    LAST_OZ_QUANT            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    ACTIVE_PRIHOD_DOC_QUANT DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
     LAST_OZ_PRICE            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
+
−
    LAST_RASHOD_DATE        DM_DATETIME,
+
−
    ACTIVE_ORDER_QUANT     DM_DOUBLE,
+
−
    MGN_ID                  DM_ID_NULL /* DM_ID_NULL = BIGINT */,
+
−
    BAD_FLAG                DM_STATUS /* DM_STATUS = INTEGER */,
+
−
    INSERTDT                DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
+
−
    G$PROFILE_ID     DM_ID_NULL
+
−
);
+
−
  CREATE GENERATOR GEN_ORDER_DATA_ID;
+
−
ALTER TABLE ORDER_DATA ADD CONSTRAINT PK_ORDER_DATA PRIMARY KEY (ID);
+
−
 
+
−
CREATE INDEX ORDER_DATA_IDX1 ON ORDER_DATA (MGN_NAME);
+
−
CREATE DESCENDING INDEX ORDER_DATA_IDX2 ON ORDER_DATA (MGN_NAME);
+
−
CREATE DESCENDING INDEX ORDER_DATA_IDX3 ON ORDER_DATA (ID);
+
  
 +
-- таблица 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
 
  CREATE OR ALTER TRIGGER ORDER_DATA_BI FOR ORDER_DATA
 
  ACTIVE BEFORE INSERT POSITION 0
 
  ACTIVE BEFORE INSERT POSITION 0
Строка 121: Строка 431:
 
   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;^
−
 
+
−
 
+
−
/* WARES_BI_SNAME */
+
−
 
+
−
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;
+
−
 
+
−
 
+
−
/* UPDPR_REPARWARES */
+
  
 +
--
 
create or alter procedure PR_INSTALL_UPDPR_REPARWARES
 
create or alter procedure PR_INSTALL_UPDPR_REPARWARES
 
as
 
as
Строка 178: Строка 476:
 
     update DOC_DETAIL_ACTIVE_NAMEID 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;
 
     update DOC_DETAIL_DELETED set ware_id=:id where ware_id=:t_id;
−
 
 
     if ((
 
     if ((
 
       select
 
       select
Строка 188: Строка 485:
 
     ) THEN
 
     ) THEN
 
       EXECUTE STATEMENT (''update DOC_DETAIL_ACTIVE_TREB set ware_id=''||:id||'' where ware_id=''||:t_id);
 
       EXECUTE STATEMENT (''update DOC_DETAIL_ACTIVE_TREB set ware_id=''||:id||'' where ware_id=''||:t_id);
−
 
 
     if ((
 
     if ((
 
       select
 
       select
Строка 215: Строка 511:
 
     end;
 
     end;
 
   ');
 
   ');
−
 
 
else
 
else
−
 
 
   EXECUTE STATEMENT ('
 
   EXECUTE STATEMENT ('
 
     create or alter procedure UPDPR_REPARWARES
 
     create or alter procedure UPDPR_REPARWARES
Строка 247: Строка 541:
 
     -- update DOC_DETAIL_ACTIVE_LOG 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_NAMEID set ware_id=:id where ware_id=:t_id;
−
 
 
     if ((
 
     if ((
 
       select
 
       select
Строка 257: Строка 550:
 
     ) THEN
 
     ) THEN
 
       EXECUTE STATEMENT (''update DOC_DETAIL_ACTIVE_TREB set ware_id=''||:id||'' where ware_id=''||:t_id);
 
       EXECUTE STATEMENT (''update DOC_DETAIL_ACTIVE_TREB set ware_id=''||:id||'' where ware_id=''||:t_id);
−
 
 
     if ((
 
     if ((
 
       select
 
       select
Строка 267: Строка 559:
 
     ) THEN
 
     ) THEN
 
       EXECUTE STATEMENT (''update OUT$ZAKAZ set ware_id=''||:id||'' where ware_id=''||:t_id);
 
       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 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 set ware_id=:id where ware_id=:t_id;
 
     update PARTS_LOG set ware_id=:id where ware_id=:t_id;
 
     update PARTS_LOG set ware_id=:id where ware_id=:t_id;
Строка 285: Строка 576:
 
     end;
 
     end;
 
   ');
 
   ');
−
 
+
end;^
−
end;
+
−
 
+
 
/* Existing privileges on this procedure */
 
/* Existing privileges on this procedure */
−
 
+
GRANT EXECUTE ON PROCEDURE PR_INSTALL_UPDPR_REPARWARES TO SYSDBA;^
−
GRANT EXECUTE ON PROCEDURE PR_INSTALL_UPDPR_REPARWARES TO SYSDBA;
+
execute procedure PR_INSTALL_UPDPR_REPARWARES;^
−
 
+
DROP PROCEDURE PR_INSTALL_UPDPR_REPARWARES;^
−
execute procedure PR_INSTALL_UPDPR_REPARWARES;
+
−
 
+
−
DROP PROCEDURE PR_INSTALL_UPDPR_REPARWARES;
+
−
 
+
  
 
/* PR_GET_WARE */
 
/* PR_GET_WARE */
−
 
 
create or alter procedure PR_INSTALL_PR_GET_WARE
 
create or alter procedure PR_INSTALL_PR_GET_WARE
 
as
 
as
 
begin
 
begin
−
 
 
   if ((
 
   if ((
 
     select
 
     select
Строка 360: Строка 643:
 
     end;
 
     end;
 
   ');
 
   ');
−
 
 
else
 
else
−
 
 
   EXECUTE STATEMENT ('
 
   EXECUTE STATEMENT ('
 
create or alter procedure PR_GET_WARE (
 
create or alter procedure PR_GET_WARE (
Строка 412: Строка 693:
 
     end;
 
     end;
 
   ');
 
   ');
−
 
+
end;^
−
end;
+
−
 
+
 
/* Existing privileges on this procedure */
 
/* 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;^
  
−
GRANT EXECUTE ON PROCEDURE PR_INSTALL_PR_GET_WARE TO SYSDBA;
+
EXECUTE BLOCK AS BEGIN
−
 
+
if(not exists(select id from DOC_TYPES where id = 36)) then
−
execute procedure PR_INSTALL_PR_GET_WARE;
+
execute statement 'INSERT INTO DOC_TYPES (ID, CAPTION, BASE_TYPE, STATINI, REPORTS, VTYPE) VALUES (36, ''Заказ оптовику'', 4, ''
−
 
+
−
DROP PROCEDURE PR_INSTALL_PR_GET_WARE;
+
−
 
+
−
 
+
−
/* Создаем тип документа "Заказ оптовику" */
+
−
 
+
−
INSERT INTO DOC_TYPES (ID, CAPTION, BASE_TYPE, STATINI, REPORTS, VTYPE) VALUES (36, 'Заказ оптовику', 4, '
+
 
[main]
 
[main]
 
koef=0
 
koef=0
Строка 437: Строка 712:
 
[dbf]
 
[dbf]
 
inipath=dbf.ini
 
inipath=dbf.ini
−
', NULL, 1);
+
'', NULL, 1);';
 +
END;^
  
−
</pre>
+
--колонки в вьюхи
 +
EXECUTE BLOCK AS BEGIN
 +
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_DOC_DETAIL' and rf.RDB$FIELD_NAME = 'MGN_NAME'))
 +
then
 +
execute statement 'execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL'',''MGN_NAME'',''w.mgn_name'')';
 +
END^
 +
EXECUTE BLOCK AS BEGIN
 +
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_DOC_DETAIL' and rf.RDB$FIELD_NAME = 'MGN_ID'))
 +
then
 +
execute statement 'execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL'',''MGN_ID'',''w.mgn_id'')';
 +
END^
 +
EXECUTE BLOCK AS BEGIN
 +
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_DOC_DETAIL' and rf.RDB$FIELD_NAME = 'MGN_SOURCE'))
 +
then
 +
execute statement 'execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL'',''MGN_SOURCE'',''cast(w.mgn_source as dm_text_big)'')';
 +
END^
  
−
==В представления VW_DOC_DETAIL и VW_WARES добавить поля==
+
EXECUTE BLOCK AS BEGIN
−
<nowiki>
+
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_WARES' and rf.RDB$FIELD_NAME = 'MGN_NAME'))
−
MGN_NAME,
+
then
−
MGN_ID,
+
execute statement 'execute procedure PR_ALTER_VIEW(''VW_WARES'',''MGN_NAME'',''w.mgn_name'')';
−
MGN_SOURCE
+
END^
−
</nowiki>
+
EXECUTE BLOCK AS BEGIN
−
как
+
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_WARES' and rf.RDB$FIELD_NAME = 'MGN_ID'))
−
<nowiki>
+
then
−
w.mgn_name,
+
execute statement 'execute procedure PR_ALTER_VIEW(''VW_WARES'',''MGN_ID'',''w.mgn_id'')';
−
w.mgn_id,
+
END^
−
cast(w.mgn_source as dm_text_big) /* aleksnick 16.03.2016 w.mgn_source */
+
EXECUTE BLOCK AS BEGIN
−
</nowiki>
+
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_WARES' and rf.RDB$FIELD_NAME = 'MGN_SOURCE'))
−
где w - таблица WARES
+
then
 +
execute statement 'execute procedure PR_ALTER_VIEW(''VW_WARES'',''MGN_SOURCE'',''cast(w.mgn_source as dm_text_big)'')';
 +
END^
  
−
==В представление VW_DOC_DETAIL_ACTIVE добавить поля==
+
EXECUTE BLOCK AS BEGIN
−
<nowiki>
+
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_DOC_DETAIL_ACTIVE' and rf.RDB$FIELD_NAME = 'MGN_NAME'))
−
MGN_NAME,
+
then
−
MGN_ID,
+
execute statement 'execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''MGN_NAME'',''(select mgn_name from wares where id = da.ware_id) as mgn_name'')';
−
MGN_SOURCE
+
END^
−
</nowiki>
+
EXECUTE BLOCK AS BEGIN
−
как
+
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_DOC_DETAIL_ACTIVE' and rf.RDB$FIELD_NAME = 'MGN_ID'))
−
<nowiki>
+
then
−
(select mgn_name from wares where id = da.ware_id) as mgn_name,
+
execute statement 'execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''MGN_ID'',''(select mgn_id from wares where id = da.ware_id) as mgn_id'')';
−
(select mgn_id from wares where id = da.ware_id) as mgn_id,
+
END^
−
(select mgn_source from wares where id = da.ware_id) as mgn_source
+
EXECUTE BLOCK AS BEGIN
−
</nowiki>
+
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_DOC_DETAIL_ACTIVE' and rf.RDB$FIELD_NAME = 'MGN_SOURCE'))
 +
then
 +
execute statement 'execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''MGN_SOURCE'',''(select mgn_source from wares where id = da.ware_id) as mgn_source'')';
 +
END^
  
−
==В представление VW_WAREBASE добавить поле==
+
EXECUTE BLOCK AS BEGIN
−
<nowiki>
+
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_WAREBASE' and rf.RDB$FIELD_NAME = 'MGN_NAME'))
−
MGN_NAME
+
then
−
</nowiki>
+
execute statement 'execute procedure PR_ALTER_VIEW(''VW_WAREBASE'',''MGN_NAME'',''(select mgn_name from wares where id = wb.ware_id)'')';
−
как
+
END^
−
<nowiki>
+
 
−
(select mgn_name from wares where id = wb.ware_id)
+
 
−
</nowiki>
+
SET TERM ; ^
 +
 
 +
</pre>
  
  

Версия 13:30, 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 BLOCK AS BEGIN
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_DOC_DETAIL' and rf.RDB$FIELD_NAME = 'MGN_NAME'))
then
execute statement 'execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL'',''MGN_NAME'',''w.mgn_name'')';
END^
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_DOC_DETAIL' and rf.RDB$FIELD_NAME = 'MGN_ID'))
then
execute statement 'execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL'',''MGN_ID'',''w.mgn_id'')';
END^
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_DOC_DETAIL' and rf.RDB$FIELD_NAME = 'MGN_SOURCE'))
then
execute statement 'execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL'',''MGN_SOURCE'',''cast(w.mgn_source as dm_text_big)'')';
END^

EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_WARES' and rf.RDB$FIELD_NAME = 'MGN_NAME'))
then
execute statement 'execute procedure PR_ALTER_VIEW(''VW_WARES'',''MGN_NAME'',''w.mgn_name'')';
END^
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_WARES' and rf.RDB$FIELD_NAME = 'MGN_ID'))
then
execute statement 'execute procedure PR_ALTER_VIEW(''VW_WARES'',''MGN_ID'',''w.mgn_id'')';
END^
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_WARES' and rf.RDB$FIELD_NAME = 'MGN_SOURCE'))
then
execute statement 'execute procedure PR_ALTER_VIEW(''VW_WARES'',''MGN_SOURCE'',''cast(w.mgn_source as dm_text_big)'')';
END^

EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_DOC_DETAIL_ACTIVE' and rf.RDB$FIELD_NAME = 'MGN_NAME'))
then
execute statement 'execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''MGN_NAME'',''(select mgn_name from wares where id = da.ware_id) as mgn_name'')';
END^
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_DOC_DETAIL_ACTIVE' and rf.RDB$FIELD_NAME = 'MGN_ID'))
then
execute statement 'execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''MGN_ID'',''(select mgn_id from wares where id = da.ware_id) as mgn_id'')';
END^
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_DOC_DETAIL_ACTIVE' and rf.RDB$FIELD_NAME = 'MGN_SOURCE'))
then
execute statement 'execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''MGN_SOURCE'',''(select mgn_source from wares where id = da.ware_id) as mgn_source'')';
END^

EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'VW_WAREBASE' and rf.RDB$FIELD_NAME = 'MGN_NAME'))
then
execute statement 'execute procedure PR_ALTER_VIEW(''VW_WAREBASE'',''MGN_NAME'',''(select mgn_name from wares where id = wb.ware_id)'')';
END^


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=4161»