Установка автозаказа

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск

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

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

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


/* ELNAKLS */

CREATE TABLE ELNAKLS(FILENAME DM_TEXT1024);


/* EXT_DATA */

CREATE TABLE EXT_DATA(ID DM_ID NOT NULL,
SNAME DM_TEXT1024,
ORIG_NAME DM_TEXT1024,
MGN_SOURCE DM_TEXT1024,
QUANT DM_DOUBLE,
PRICE DM_DOUBLE,
SUMMA DM_DOUBLE,
SDOC_TYPE DM_TEXT1024,
COMMITDATE DM_DATETIME,
DOC_TYPE DM_STATUS,
STATUS DM_STATUS,
MGN_ID DM_ID_NULL,
INSERTDT DM_DATETIME,
G$PROFILE_ID DM_ID_NULL);

 CREATE SEQUENCE GEN_EXT_DATA_ID;
 ALTER SEQUENCE GEN_EXT_DATA_ID RESTART WITH 0;

 CREATE INDEX EXT_DATA_IDX1 ON EXT_DATA (ID);
 CREATE INDEX EXT_DATA_IDX2 ON EXT_DATA (SNAME);
 CREATE INDEX EXT_DATA_IDX3 ON EXT_DATA (MGN_ID);

 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 поля и индексы */

 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_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

  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;


/* ORDER_DATA */

CREATE TABLE ORDER_DATA (
    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);

 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;


/* 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
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;


/* Создаем тип документа "Заказ оптовику" */

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


В представления VW_DOC_DETAIL и VW_WARES добавить поля

MGN_NAME,
MGN_ID,
MGN_SOURCE

как 

w.mgn_name,
w.mgn_id,
cast(w.mgn_source as dm_text_big) /* aleksnick 16.03.2016 w.mgn_source */

где w - таблица WARES

В представление VW_DOC_DETAIL_ACTIVE добавить поля

MGN_NAME,
MGN_ID,
MGN_SOURCE

как 

(select mgn_name from wares where id = da.ware_id) as mgn_name,
(select mgn_id from wares where id = da.ware_id) as mgn_id,
(select mgn_source from wares where id = da.ware_id) as mgn_source

В представление VW_WAREBASE добавить поле

MGN_NAME

как 

(select mgn_name from wares where id = wb.ware_id)


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

Проверить, что индекс 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=3634»