ЕГАИС постановка на баланс

Скрипты для постановки на баланс

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

PR_ALTER_VIEW

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

Для скриптов ниже актуальная версия Менеджера ManagerXP2_272_78 и выше

Выполнить скрипт

SET TERM ^ ;

execute procedure PR_ALTER_VIEW(''PARTS'',''EGAIS_REGID, EGAIS_QUANT, EGAIS_ALCCODE'',''PARTS_IDX_EGAIS_1'',''index'');^
execute procedure PR_ALTER_VIEW(''PARTS'',''EGAIS_REGID, EGAIS_BREGID, EGAIS_ALCCODE, EGAIS_QUANT'',''PARTS_IDX_EGAIS_2'',''index'');^
execute procedure PR_ALTER_VIEW(''EGAIS_DETAIL'',''DOC_DETAIL_ID'',''EGAIS_DETAIL_IDX1'',''index'');^


--Зкомментированные ниже строки - наброски для гурманов для тогочтобы внести в базы ТМСки по списанию пива и созданию документа перемещения в торговый зал
--EXECUTE BLOCK AS BEGIN
--if ((select 1 from groups where caption = ''Списание пива'') is null) then
--execute statement ''INSERT INTO GROUPS (PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID, SID) VALUES (-220, ''''Списание пива'''', --''''TMS'''', 0, ''''27-SEP-2016 16:24:48'''', 0, NULL, -1, NULL, 16777215, NULL, 0, NULL);'';
--END;^

--EXECUTE BLOCK AS BEGIN
--if ((select 1 from groups where caption = ''Списание пива'') is null) then
--execute statement ''update groups gp set gp.data =
--                    (select g.tmp_blob1 from G$PROFILES g where g.id = 1)
--                   where gp.caption = ''''Списание пива'''' and (select g.tmp_blob1 from G$PROFILES g where g.id = 1) is not null;^'';
--END;^

--EXECUTE BLOCK AS BEGIN
--if ((select 1 from groups where caption = ''Переместить в торговый зал'') is null) then
--execute statement ''
--INSERT INTO GROUPS (PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID, SID) VALUES ( -221, 'Переместить в торговый зал', '', 0, '8-OCT-2016 12:04:29.200', 0, NULL, -1, NULL, 16777215, NULL, 0, NULL);'';
--END;^

--EXECUTE BLOCK AS BEGIN
--if ((select 1 from groups where caption = ''Переместить в торговый зал'') is null) then
--execute statement ''update groups gp set gp.data =
--                    (select g.tmp_blob1 from G$PROFILES g where g.id = 3)
--                    where gp.caption = ''''Переместить в торговый зал'''' and (select g.tmp_blob1 from G$PROFILES g where g.id = 3) is not null;^'';
--END;^

--поначалу не совсем понятно создавались наименования документов, тут правка на более понятные
update doc_types set caption=''ЕГАИС Списание со склада'' where id = 42;^
update doc_types set caption=''ЕГАИС Списание из торгового зала'' where id = 43;^
update doc_types set caption=''ЕГАИС Перемещение со склада в торговый зал'' where id = 44;^
update doc_types set caption=''ЕГАИС Перемещение из торгового зала на склад'' where id = 45;^
update doc_types set caption=''ЕГАИС Акт постановки на баланс'' where id = 46;^

execute procedure PR_ALTER_VIEW(''VW_WAREBASE'',''EGAIS_SKLAD_QUANT'',''(select esk.quantity from egais_sklad esk where esk.informbregid = p.egais_bregid)'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_WAREBASE'',''EGAIS_SHOP_QUANT'',''(select esh.egais_quant from egais_shop esh where esh.alccode = p.egais_alccode and esh.producer_id = p.egais_producer_id)'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_WAREBASE'',''EGAIS_REGID'',''p.egais_regid'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_WAREBASE'',''EGAIS_BREGID'',''p.egais_bregid'',''VIEW'');^

execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''BOTTLINGDATE'',''BOTTLINGDATE'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''EGAIS_BARCODE'',''EGAIS_BARCODE'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''EGAIS_FIXNUMBER'',''EGAIS_FIXNUMBER'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''EGAIS_FIXDATE'',''EGAIS_FIXDATE'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''EGAIS_REGID'',''p.egais_regid'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''EGAIS_BREGID'',''p.egais_bregid'',''VIEW'');^


--в начальных вариантах был неправилный домен для акцизы, после применения этого скрипта есть вероятность что нужно будет ждатьбэкап-ресторе, чтобы изменения применились
update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = ''DM_BLOBTEXT''
where (RDB$FIELD_NAME = ''EGAIS_BARCODE'') and
(RDB$RELATION_NAME = ''PARTS'');^

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = ''DM_BLOBTEXT''
where (RDB$FIELD_NAME = ''EGAIS_BARCODE'') and
(RDB$RELATION_NAME = ''DOC_DETAIL_ACTIVE'');^

execute procedure PR_ALTER_VIEW('''','''',''GEN_EGAIS_SKLAD_ID'',''generator'');^

EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from rdb$relations where rdb$relation_name = ''EGAIS_SKLAD'')) then
execute statement ''CREATE TABLE EGAIS_SKLAD(ID DM_ID NOT NULL);'';
END^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''QUANTITY'',''DM_DOUBLE'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''INFORMAREGID'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''INFORMBREGID'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''FULLNAME'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''ALCCODE'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''CAPACITY'',''DM_DOUBLE'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''ALCVOLUME'',''DM_DOUBLE'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCTVCODE'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_CLIENTREGID'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_INN'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_KPP'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_FULLNAME'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_SHORTNAME'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_COUNTRY'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_REGIONCODE'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_DESCRIPTION'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''INSERTDT'','' DM_DATETIME'',''TABLE'');^

execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''INFORMAREGID'',''EGAIS_SKLAD_IDX1'',''index'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''INFORMBREGID'',''EGAIS_SKLAD_IDX2'',''index'');^

CREATE OR ALTER TRIGGER EGAIS_SKLAD_BI FOR EGAIS_SKLAD
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_egais_sklad_id,1);
  if (new.insertdt is null) then
    new.insertdt = ''now'';
end
^

--изменения типа редактирования штрихкода на тип memo, в большинстве случаев есть в настройках менеджера, иногда просто скрыто
update params set param_value = ''            1'' where PARAM_CAPTION = ''BCODE_EDITOR'';^


--триггер слежения за актуальностью егаис остатков, в любом случае надо время от времени запрашивать их с егаиса 
CREATE OR ALTER TRIGGER DOC_DETAIL_AI1 FOR DOC_DETAIL
ACTIVE AFTER INSERT POSITION 0
AS
DECLARE VARIABLE PRODUCER_ID DM_TEXT;
DECLARE VARIABLE ALCCODE DM_TEXT1024;
DECLARE VARIABLE QUANT DM_DOUBLE;
DECLARE VARIABLE MMBSH DM_TEXT1024;
DECLARE VARIABLE SNAME DM_TEXT;
DECLARE VARIABLE UNITTYPE DM_TEXT;
DECLARE VARIABLE ALCVOLUME DM_TEXT;
DECLARE VARIABLE PRODUCTVCODE DM_TEXT;
DECLARE VARIABLE DOC_TYPE DM_ID;

begin
  /* учёт егаис остатков в реальном времени */
  select doc_type from docs where id = new.doc_id into DOC_TYPE;

  select first 1 MMBSH,SNAME from warebase where part_id = new.part_id into :MMBSH,:SNAME;

  select EGAIS_PRODUCER_ID,EGAIS_ALCCODE,ALC_VOLUME,EGAIS_PRODUCT_VCODE from parts p where p.id = new.part_id into :PRODUCER_ID,:ALCCODE,:ALCVOLUME,:PRODUCTVCODE;

  if (((MMBSH containing '-21={') or (MMBSH containing '-20={')) and ((PRODUCER_ID is not null) and (ALCCODE is not null))) then
  begin
      --работа только со складом
      if (:DOC_TYPE in (40,41,42,46)) then
      begin

        select QUANTITY from EGAIS_SKLAD es where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE into :QUANT;

        if (QUANT is null) then
        begin
            insert into EGAIS_SKLAD (QUANTITY,FULLNAME,ALCCODE,PRODUCER_CLIENTREGID,PRODUCTVCODE)
                values (new.quant,:SNAME,:ALCCODE,:PRODUCER_ID,:PRODUCTVCODE);
        end
        else
          update EGAIS_SKLAD es set es.QUANTITY = :QUANT+new.quant where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE;
      end

      --работа только со торговым залом
      if (:DOC_TYPE in (43,47)) then
      begin

      select EGAIS_QUANT from EGAIS_SHOP es where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE into :QUANT;

      if (QUANT is null) then
      begin
            insert into EGAIS_SHOP (PART_ID,SNAME,EGAIS_QUANT,ALCCODE,UNITTYPE,ALCVOLUME,PRODUCTVCODE,PRODUCER_ID)
                values (new.part_id, :SNAME,  new.quant,:ALCCODE,:UNITTYPE,:ALCVOLUME,:PRODUCTVCODE,:PRODUCER_ID);
      end
      else
          update EGAIS_SHOP es set es.EGAIS_QUANT = :QUANT+new.quant where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE;

      end

      --перемещение со склада в торговый зал
      if (:DOC_TYPE in (44)) then
      begin
        select QUANTITY from EGAIS_SKLAD es where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE into :QUANT;

        if (QUANT is null) then
        begin
            insert into EGAIS_SKLAD (QUANTITY,FULLNAME,ALCCODE,PRODUCER_CLIENTREGID,PRODUCTVCODE)
                values (-new.quant,:SNAME,:ALCCODE,:PRODUCER_ID,:PRODUCTVCODE);
        end
        else
          update EGAIS_SKLAD es set es.QUANTITY = :QUANT-new.quant where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE;

      select EGAIS_QUANT from EGAIS_SHOP es where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE into :QUANT;

      if (QUANT is null) then
      begin
            insert into EGAIS_SHOP (PART_ID,SNAME,EGAIS_QUANT,ALCCODE,UNITTYPE,ALCVOLUME,PRODUCTVCODE,PRODUCER_ID)
                values (new.part_id, :SNAME,  new.quant,:ALCCODE,:UNITTYPE,:ALCVOLUME,:PRODUCTVCODE,:PRODUCER_ID);
      end
      else
          update EGAIS_SHOP es set es.EGAIS_QUANT = :QUANT+new.quant where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE;

      end

     --перемещение из торгового зала на склад
      if (:DOC_TYPE in (45)) then
      begin

        select QUANTITY from EGAIS_SKLAD es where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE into :QUANT;

        if (QUANT is null) then
        begin
            insert into EGAIS_SKLAD (QUANTITY,FULLNAME,ALCCODE,PRODUCER_CLIENTREGID,PRODUCTVCODE)
                values (new.quant,:SNAME,:ALCCODE,:PRODUCER_ID,:PRODUCTVCODE);
        end
        else
          update EGAIS_SKLAD es set es.QUANTITY = :QUANT+new.quant where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE;

      select EGAIS_QUANT from EGAIS_SHOP es where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE into :QUANT;

      if (QUANT is null) then
      begin
            insert into EGAIS_SHOP (PART_ID,SNAME,EGAIS_QUANT,ALCCODE,UNITTYPE,ALCVOLUME,PRODUCTVCODE,PRODUCER_ID)
                values (new.part_id, :SNAME,  -new.quant,:ALCCODE,:UNITTYPE,:ALCVOLUME,:PRODUCTVCODE,:PRODUCER_ID);
      end
      else
          update EGAIS_SHOP es set es.EGAIS_QUANT = :QUANT-new.quant where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE;
      end
  end

end
^

SET TERM ; ^

изменить триггер DOC_DETAIL_ACTIVE_BI

где берутся данные из таблицы parts, добавить чтобы заполнялось поле EGAIS_PRODUCER_ID