Установка ЕГАИС

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

Настройка jacarta-ключа

Предварительно нужно установить Единый клиент джакарты Медиа:Получение_КЭП_для_ЕГАИС.docx


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

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

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

SET SQL DIALECT 3;

ALTER TABLE DOC_DETAIL ADD FIO_ID DM_ID_NULL;
ALTER TABLE DOC_DETAIL ADD FIO_BOLNOY_ID DM_ID_NULL;
ALTER TABLE DOC_DETAIL ADD TYPE_LGOTA DM_ID_NULL;
ALTER TABLE DOC_DETAIL ADD RECEPT DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD SUM_NDSR DM_DOUBLE;
ALTER TABLE DOC_DETAIL ADD SUM_NDSR DM_DOUBLE;
ALTER TABLE DOCS ADD SUM_NDSR DM_DOUBLE;
ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_UUID DM_UUID_NULL;
ALTER TABLE PARTS ADD MOTHERPART_UUID DM_UUID_NULL;
ALTER TABLE VALS ADD D$UUID DM_UUID_NULL;
ALTER TABLE VALS ADD L_ID DM_ID_NULL;

ALTER TABLE AGENTS ADD EGAIS_ID DM_UUID_NULL;
ALTER TABLE DOCS ADD EGAIS_NUM DM_TEXT;
ALTER TABLE DOCS ADD EGAIS_ID DM_TEXT;
ALTER TABLE DOCS ADD EGAIS_UNITTYPE DM_TEXT;
ALTER TABLE DOCS ADD EGAIS_STATUS DM_STATUS;
DESCRIBE FIELD EGAIS_STATUS TABLE DOCS
'1 - Accept акт принятия
2 - Reject акт принятия
3 - Accept акт отказа
4 - Reject акт отказа
5 - Accept акт расхождения
6 - Reject акт расхождения';

ALTER TABLE PARTS ADD EGAIS_ID DM_TEXT;
ALTER TABLE PARTS ADD EGAIS_REGID DM_TEXT;
ALTER TABLE PARTS ADD EGAIS_BREGID DM_TEXT;
ALTER TABLE PARTS ADD EGAIS_ALCCODE DM_TEXT1024;
ALTER TABLE PARTS ADD EGAIS_QUANT DM_DOUBLE;
ALTER TABLE PARTS ADD CAPACITY DM_DOUBLE;
ALTER TABLE PARTS ADD ALC_VOLUME DM_DOUBLE;
ALTER TABLE PARTS ADD EGAIS_PRODUCER_ID DM_TEXT;
ALTER TABLE PARTS ADD EGAIS_BARCODE DM_TEXT1024;
ALTER TABLE PARTS ADD EGAIS_TYPE DM_TEXT;
ALTER TABLE PARTS ADD PRODUCER_INN DM_TEXT1024;
ALTER TABLE PARTS ADD PRODUCER_KPP DM_TEXT1024;
ALTER TABLE PARTS ADD BOTTLINGDATE DM_DATETIME;
ALTER TABLE PARTS ADD EGAIS_PRODUCT_VCODE DM_TEXT;

ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_ID DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_BREGID DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_REGID DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_ALCCODE DM_TEXT1024;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_PRODUCT_VCODE DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD CAPACITY DM_DOUBLE;
ALTER TABLE DOC_DETAIL_ACTIVE ADD ALC_VOLUME DM_DOUBLE;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_PRODUCER_ID DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_QUANT DM_DOUBLE;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_BARCODE DM_TEXT1024;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_TYPE DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD PRODUCER_INN DM_TEXT1024;
ALTER TABLE DOC_DETAIL_ACTIVE ADD PRODUCER_KPP DM_TEXT1024;
ALTER TABLE DOC_DETAIL_ACTIVE ADD BOTTLINGDATE DM_DATETIME;


CREATE GENERATOR GEN_EGAIS_REQUESTS_ID;

CREATE TABLE EGAIS_REQUESTS (
    ID           DM_ID NOT NULL /* DM_ID = BIGINT */,
    REPLY_ID     DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    REPLY_URL    DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    QUERY_TYPE   DM_STATUS /* DM_STATUS = INTEGER */,
    QUERY_URL    DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    QUERY_DATA   DM_BLOBBIN /* DM_BLOBBIN = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    REPLY_DATA   DM_BLOBBIN /* DM_BLOBBIN = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    STATUS       DM_STATUS /* DM_STATUS = INTEGER */,
    INSERT_DATE  DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    REPLY_DATE   DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    EGAIS_ID     DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    QUERY_OUT    DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */
);

ALTER TABLE EGAIS_REQUESTS ADD CONSTRAINT PK_EGAIS_REQUESTS PRIMARY KEY (ID);
CREATE INDEX EGAIS_REQUESTS_IDX1 ON EGAIS_REQUESTS (REPLY_ID);

CREATE OR ALTER TRIGGER EGAIS_REQUESTS_BI FOR EGAIS_REQUESTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_egais_REQUESTS_id,1);
  if (new.insert_date is null) then
    new.insert_date = 'now';
end;

CREATE OR ALTER TRIGGER EGAIS_REQUESTS_BU0 FOR EGAIS_REQUESTS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (new.reply_url <> '') then
    new.reply_date = 'now';
end;

COMMENT ON COLUMN EGAIS_REQUESTS.QUERY_TYPE IS 
'1 - синхронизация контрагентов
2 - акт принятия ТТН
3 - акт отказа от ТТН
4 - акт расхождения по ТТН';

create or alter procedure PR_NEW_PART (
    DOC_ID type of DM_ID,
    PARENT_ID type of DM_ID,
    WARE_ID type of DM_UUID_NULL,
    PRICE type of DM_DOUBLE,
    PRICE_O type of DM_DOUBLE,
    PRICE_Z type of DM_DOUBLE,
    PRICE_R type of DM_DOUBLE,
    QUANT type of DM_DOUBLE,
    BARCODE type of DM_TEXT,
    BARCODE1 type of DM_TEXT,
    DEP type of DM_ID,
    GODENDO type of DM_DATETIME,
    SERIA type of DM_TEXT,
    NDS type of DM_DOUBLE,
    SUM_NDSO type of DM_DOUBLE,
    SERT type of DM_TEXT,
    DATESERT type of DM_DATETIME,
    KEMVSERT type of DM_TEXT,
    SDSERT type of DM_DATETIME,
    REGN type of DM_TEXT,
    NGTD type of DM_TEXT,
    EDIZM type of DM_TEXT,
    NAC type of DM_DOUBLE,
    MOTHERPART_ID type of DM_ID,
    PART_TYPE DM_STATUS,
    BASE_AGENT_ID DM_ID,
    SKLAD_ID type of DM_TEXT,
    CONTRACT_ID DM_ID,
    DOC_DETAIL_ACTIVE_ID DM_ID,
    GROUP_ID DM_ID,
    MOTHERPART_UUID DM_UUID_NULL = 0,
    EGAIS_ID DM_ID_NULL = null,
    EGAIS_REGID DM_TEXT1024 = null,
    EGAIS_BREGID DM_TEXT1024 = null,
    EGAIS_ALCCODE DM_TEXT1024 = null,
    EGAIS_QUANT DM_DOUBLE = 0,
    CAPACITY DM_DOUBLE = 0,
    ALC_VOLUME DM_DOUBLE = 0,
    EGAIS_PRODUCER_ID DM_TEXT1024 = null,
    EGAIS_BARCODE DM_TEXT1024 = null,
    EGAIS_TYPE DM_TEXT = null,
    PRODUCER_INN DM_TEXT1024 = null,
    PRODUCER_KPP DM_TEXT1024 = null,
    BOTTLINGDATE DM_DATETIME = null,
    EGAIS_PRODUCT_VCODE DM_TEXT = null)
returns (
    P_ID type of DM_ID)
as
begin
  p_id=gen_id(gen_parts_id,1);
  insert into parts
    (ID,parent_id,doc_id,WARE_ID,PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE,BARCODE1,DEP,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,DATESERT,KEMVSERT,SDSERT,REGN,NGTD,
    EDIZM,NAC,motherpart_id,sklad_id,part_type,BASE_AGENT_ID,contract_id, doc_detail_active_id, group_id, motherpart_uuid,
    EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE, EGAIS_QUANT, CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID, EGAIS_BARCODE,
    EGAIS_TYPE, PRODUCER_INN, PRODUCER_KPP, BOTTLINGDATE, EGAIS_PRODUCT_VCODE)
  values
    (:p_id,:parent_id,:doc_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,
    :SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id,:sklad_id,:part_type,:BASE_AGENT_ID,:contract_id,:doc_detail_active_id, :group_id, :motherpart_uuid,
    :EGAIS_ID, :EGAIS_BREGID, :EGAIS_REGID, :EGAIS_ALCCODE, :EGAIS_QUANT, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID, :EGAIS_BARCODE,
    :EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE);
  suspend;
end;


/* Following GRANT statetements are generated automatically */

GRANT INSERT ON PARTS TO PROCEDURE PR_NEW_PART;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO SYSDBA;


create or alter procedure PR_GET_EGAIS_STATUS (
    ID DM_STATUS)
returns (
    OUT_TEXT DM_TEXT1024)
as
begin

  if ( (id is null) or (id = 0) )  then out_text = 'Не установлен';
  else if (id = 1) then out_text = 'Акт принятия приянт';
  else if (id = 2) then out_text = 'Акт принятия отклонен';
  else if (id = 3) then out_text = 'Акт отказа принят';
  else if (id = 4) then out_text = 'Акт отказа отклонен';
  else if (id = 5) then out_text = 'Акт расхождений принят';
  else if (id = 6) then out_text = 'Акт расхождений отклонен';
  else out_text = 'Неизвестный статус';

  suspend;
end;

GRANT EXECUTE ON PROCEDURE PR_GET_EGAIS_STATUS TO SYSDBA;

create or alter procedure PR_GET_ADDRESS_ID (
    COUNTRY_ID DM_ID_NULL,
    REGION_ID DM_ID_NULL,
    DESCRIPTION DM_TEXT_BIG)
returns (
    ID DM_ID_NULL)
as
begin

  if ((select id from addr_vals where id = :country_id) is null) then
    insert into addr_vals (id, vtype, svalue) values (:country_id, 0, :country_id);
  if ((select id from addr_vals where id = :region_id) is null) then
    insert into addr_vals (id, vtype, svalue) values (:region_id, 1, :region_id);

  select id from addrs where country_id = :country_id and region_id = :region_id and addresstext = :description into :id;
  if (id is null) then
    insert into addrs (country_id, region_id, addresstext) values (:country_id, :region_id, :description) returning id into :id;

  suspend;
end;

GRANT SELECT,INSERT ON ADDR_VALS TO PROCEDURE PR_GET_ADDRESS_ID;
GRANT SELECT,INSERT ON ADDRS TO PROCEDURE PR_GET_ADDRESS_ID;
GRANT EXECUTE ON PROCEDURE PR_GET_ADDRESS_ID TO SYSDBA;

INSERT INTO DOC_TYPES (ID, CAPTION, INSERTDT, BASE_TYPE, STATINI, REPORTS, VTYPE)
               VALUES (40, 'ТТН ЕГАИС', null, 1, '[XML]
enabled=1
caption=Печать пакета документов прихода
tmplpath=prihod.xml
askbefore=1

[freereport]
enabled=1
caption=Печать пакета документов прихода
tmplpath=prihod.cffr3

[dbf]
inipath=dbf.ini

[CSV]
inipath=dbf.ini
quotestrings=1
separator=#9
headers=1', ' [XML]
enabled=1
caption=Печать пакета документов прихода
tmplpath=prihod.xml
askbefore=1

[freereport]
enabled=1
caption=Печать пакета документов прихода
tmplpath=prihod.cffr


;''doc_id=:doc_id:'';''doc_detail=:doc_detail:''', 1);

INSERT INTO GROUPS (ID, PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID)
            VALUES (-10, -4, 'ЕГАИС оптовики', 'AGENTS', 0, null, 1, NULL, -1, NULL, NULL, NULL, 0);


SET TERM ^ ;

create or alter procedure PR_SPLIT (
    SOURCE_STRING DM_BLOBTEXT,
    SPLIT_STRING DM_TEXT)
returns (
    RESULT_STRING DM_TEXT1024)
as
declare variable CHAR2 integer;
declare variable CHAR1 integer;
begin
  char1 = 1;
  char2 = 1;
  while (char2 > 0) do
    begin
      char2 = position(split_string, source_string, char1);
      if (char2 <> 0) then
        begin
          result_string = substring(source_string from char1 for (char2 - char1));
          char1 = char2 + char_length(split_string);
          suspend;
        end
      else
        if (char1 <= char_length(source_string)) then
          begin
            result_string = substring(source_string from char1);
            suspend;
          end
    end
end^

SET TERM ; ^

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_SPLIT TO SYSDBA;

/******************************************************************************/
/***                Generated by IBExpert 11.01.2016 8:54:24                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE EGAIS_DETAIL (
    D$SRVUPDDT     DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    D$UUID         DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    DOC_DETAIL_ID  DM_ID NOT NULL /* DM_ID = BIGINT */,
    EGAIS_BARCODE  DM_BLOBTEXT NOT NULL /* DM_BLOBTEXT = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */,
    PRODUCER_INN   DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    PRODUCER_KPP   DM_TEXT /* DM_TEXT = VARCHAR(250) */
);




/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: EGAIS_DETAIL_BI0 */
CREATE OR ALTER TRIGGER EGAIS_DETAIL_BI0 FOR EGAIS_DETAIL
ACTIVE BEFORE INSERT POSITION 0
AS
declare variable barcode dm_text;
declare variable counter integer;
begin
  counter = 1;
  for select result_string from pr_split(new.egais_barcode, ascii_char(13)||ascii_char(10)) into :barcode do
    begin
      if (counter = 1) then
        new.egais_barcode = barcode;
      else
        insert into egais_detail(doc_detail_id, egais_barcode, producer_inn, producer_kpp)
          values (new.doc_detail_id, :barcode, new.producer_inn, new.producer_kpp);
      counter = 2;
    end
end
^


/* Trigger: EGAIS_DETAIL_BI_DISTR */
CREATE OR ALTER TRIGGER EGAIS_DETAIL_BI_DISTR FOR EGAIS_DETAIL
ACTIVE BEFORE INSERT POSITION 9999
AS
begin
  if (new.d$uuid is null) then
    begin
      new.d$uuid = UUID_TO_CHAR(GEN_UUID());
      new.d$srvupddt = '2000-01-01';
    end
end
^


SET TERM ; ^



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/


Обновить PR_DOC_PRIHOD_COMMIT_RECURSE

SET TERM ^ ;

create or alter procedure PR_CHECK_SUM_NDSR
as
begin

  if ((select
    count(1)
    from rdb$procedure_parameters pp
    where 1=1
    and pp.rdb$procedure_name='PR_DOC_DETAIL_INSERT'
    and pp.rdb$parameter_name='SUM_NDSR')=0 ) then
  EXECUTE STATEMENT ('
   create or alter procedure PR_DOC_PRIHOD_COMMIT_RECURSE (
        DOC_ID type of DM_ID,
        DOC_PARENT_ID type of DM_ID,
        PART_PARENT_ID type of DM_ID,
        DOC_COMMITDATE type of DM_DATE,
        CONTRACT_ID DM_ID)
    as
    declare variable PART_ID type of DM_ID;
    declare variable WARE_ID type of DM_UUID_NULL;
    declare variable SNAME type of DM_TEXT;
    declare variable SIZG type of DM_TEXT;
    declare variable SCOUNTRY type of DM_TEXT;
    declare variable ORIG_CODE type of DM_TEXT;
    declare variable SORIG_NAME type of DM_TEXT;
    declare variable SORIG_IZG type of DM_TEXT;
    declare variable SORIG_COUNTRY type of DM_TEXT;
    declare variable BARCODE type of DM_TEXT;
    declare variable Z_ID type of DM_ID;
    declare variable SKLAD_ID DM_TEXT;
    declare variable PRICE type of DM_DOUBLE;
    declare variable PRICE_O type of DM_DOUBLE;
    declare variable PRICE_Z type of DM_DOUBLE;
    declare variable PRICE_R type of DM_DOUBLE;
    declare variable QUANT type of DM_DOUBLE;
    declare variable BARCODE1 type of DM_TEXT;
    declare variable DEP type of DM_ID;
    declare variable KRITK type of DM_DOUBLE;
    declare variable GODENDO type of DM_DATETIME;
    declare variable SERIA type of DM_TEXT;
    declare variable NDS type of DM_DOUBLE;
    declare variable SUM_NDSO type of DM_DOUBLE;
    declare variable SERT type of DM_TEXT;
    declare variable DATESERT type of DM_DATETIME;
    declare variable KEMVSERT type of DM_TEXT;
    declare variable SDSERT type of DM_DATETIME;
    declare variable REGN type of DM_TEXT;
    declare variable NGTD type of DM_TEXT;
    declare variable EDIZM type of DM_TEXT;
    declare variable PARENT_ID type of DM_ID;
    declare variable SUMMA type of DM_DOUBLE;
    declare variable SUMMA_O type of DM_DOUBLE;
    declare variable DISCOUNT type of DM_DOUBLE;
    declare variable ACTIVE_ID type of DM_ID;
    declare variable NAC type of DM_DOUBLE;
    declare variable DOC_DETAIL_ID type of DM_ID;
    declare variable MOTHERPART_ID type of DM_ID;
    declare variable BCODE_IZG type of DM_TEXT;
    declare variable HUMAN_QUANT type of DM_TEXT;
    declare variable SUM_DSC type of DM_DOUBLE;
    declare variable DCARD type of DM_TEXT;
    declare variable PART_TYPE DM_STATUS;
    declare variable BASE_AGENT_ID DM_ID;
    declare variable GROUP_ID DM_ID;
    declare variable MAKE_ID DM_ID_NULL;
    declare variable MNN DM_TEXT;
    declare variable MOTHERPART_UUID DM_UUID_NULL;
    declare variable EGAIS_ID DM_TEXT1024;
    declare variable EGAIS_BREGID DM_TEXT1024;
    declare variable EGAIS_REGID DM_TEXT1024;
    declare variable EGAIS_ALCCODE DM_TEXT1024;
    declare variable CAPACITY DM_DOUBLE;
    declare variable ALC_VOLUME DM_DOUBLE;
    declare variable EGAIS_PRODUCER_ID DM_TEXT1024;
    declare variable EGAIS_QUANT DM_DOUBLE;
    declare variable EGAIS_BARCODE DM_TEXT1024;
    declare variable EGAIS_TYPE DM_TEXT;
    declare variable PRODUCER_INN DM_TEXT1024;
    declare variable PRODUCER_KPP DM_TEXT1024;
    declare variable BOTTLINGDATE DM_DATETIME;
    declare variable EGAIS_PRODUCT_VCODE DM_TEXT;
    begin
    --whithout SUM_NDSR
    for
    select
    /*ware_id*/  sname,sizg,scountry,orig_code,sorig_name,sorig_izg,sorig_country,barcode,z_id,sklad_id,
    /*part_id*/  PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE1,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,DATESERT,KEMVSERT,SDSERT,REGN,NGTD,EDIZM,
    PARENT_ID,DISCOUNT,SUMMA,summa_o, id, part_id, nac, doc_detail_id, motherpart_id, dep, bcode_izg, human_quant, SUM_DSC, dcard,
    part_type, BASE_AGENT_ID, group_id, make_id, mnn, motherpart_uuid,
    EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE, CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID, EGAIS_QUANT, EGAIS_BARCODE,
    EGAIS_TYPE, PRODUCER_INN, PRODUCER_KPP, BOTTLINGDATE, EGAIS_PRODUCT_VCODE
    from doc_detail_active where doc_id=:doc_id and part_parent_id=:doc_parent_id
    into
    :sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:barcode,:z_id,:sklad_id,
    :PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE1,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,
    :PARENT_ID,:DISCOUNT,:SUMMA,:summa_o, :active_id, :part_id, :nac, :doc_detail_id, :motherpart_id, :dep, :bcode_izg, :human_quant, :SUM_DSC, :dcard,
    :part_type, :BASE_AGENT_ID, :group_id,:make_id, :mnn, :motherpart_uuid,
    :EGAIS_ID, :EGAIS_BREGID, :EGAIS_REGID, :EGAIS_ALCCODE, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID, :EGAIS_QUANT, :EGAIS_BARCODE,
    :EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE
    do
    begin
    --    if ((price<0.001) or (price is null)) then exception EX_PRICE;
    if ((price<0) or (price is null)) then exception EX_PRICE;
    if (/*(price_o<0.001) or */(price_o is null)) then exception EX_PRICE_O;
    if (/*(quant<0.00001) or */(quant is null)) then exception EX_QUANT;
    if (/*(summa<0.00001) or */(summa is null)) then exception EX_SUMMA;
    if (/*(summa_o<0.00001) or */(summa_o is null)) then exception EX_SUMMA_O;
    --    if ((NDS<0.00001) or (NDS is null)) then exception EX_NDS;
    if (/*(SUM_NDSO<0) or */(SUM_NDSO is null)) then exception EX_SUM_NDSO;
    select w_id from pr_get_ware(:sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:bcode_izg,:z_id,:sklad_id,:part_type,:mnn)
    into :ware_id;
    if ((part_id=0) or (part_id is NULL)) then
    select p_id from pr_new_part(:DOC_ID,:part_parent_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,
    :SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id, :part_type,:BASE_AGENT_ID, :sklad_id, :contract_id, :active_id, :group_id,
    :motherpart_uuid, :EGAIS_ID, :EGAIS_REGID, :EGAIS_BREGID, :EGAIS_ALCCODE, :EGAIS_QUANT, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID, :EGAIS_BARCODE,
    :EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE) into :part_id;
    execute procedure PR_DOC_DETAIL_INSERT(:doc_detail_id,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o,:price,:sum_ndso,:nac,:doc_commitdate,:human_quant,:SUM_DSC,:dcard,:active_id,:part_type,:make_id);
    -- обновляем членство в группах
    execute procedure PR_UPDGROUPDETAIL_ACTIVE2PARTS(:active_id,:part_id);
    -- присваиваем ценам партии
    update prices set part_id=:part_id where dda_id=:active_id;
    update or insert into prices (part_id,price_type,currency_id, price,dda_id) values (:part_id,0,0,:price,:active_id) matching (PART_ID,PRICE_TYPE);
    execute procedure PR_DOC_PRIHOD_COMMIT_RECURSE(:doc_id,:active_id,:part_id,:doc_commitdate,:contract_id);
    end
    end;

  ');

else

  EXECUTE STATEMENT ('
    create or alter procedure PR_DOC_PRIHOD_COMMIT_RECURSE (
        DOC_ID type of DM_ID,
        DOC_PARENT_ID type of DM_ID,
        PART_PARENT_ID type of DM_ID,
        DOC_COMMITDATE type of DM_DATE,
        CONTRACT_ID DM_ID)
    as
    declare variable PART_ID type of DM_ID;
    declare variable WARE_ID type of DM_UUID_NULL;
    declare variable SNAME type of DM_TEXT;
    declare variable SIZG type of DM_TEXT;
    declare variable SCOUNTRY type of DM_TEXT;
    declare variable ORIG_CODE type of DM_TEXT;
    declare variable SORIG_NAME type of DM_TEXT;
    declare variable SORIG_IZG type of DM_TEXT;
    declare variable SORIG_COUNTRY type of DM_TEXT;
    declare variable BARCODE type of DM_TEXT;
    declare variable Z_ID type of DM_ID;
    declare variable SKLAD_ID DM_TEXT;
    declare variable PRICE type of DM_DOUBLE;
    declare variable PRICE_O type of DM_DOUBLE;
    declare variable PRICE_Z type of DM_DOUBLE;
    declare variable PRICE_R type of DM_DOUBLE;
    declare variable QUANT type of DM_DOUBLE;
    declare variable BARCODE1 type of DM_TEXT;
    declare variable DEP type of DM_ID;
    declare variable KRITK type of DM_DOUBLE;
    declare variable GODENDO type of DM_DATETIME;
    declare variable SERIA type of DM_TEXT;
    declare variable NDS type of DM_DOUBLE;
    declare variable SUM_NDSO type of DM_DOUBLE;
    declare variable SUM_NDSR DM_DOUBLE;
    declare variable SERT type of DM_TEXT;
    declare variable DATESERT type of DM_DATETIME;
    declare variable KEMVSERT type of DM_TEXT;
    declare variable SDSERT type of DM_DATETIME;
    declare variable REGN type of DM_TEXT;
    declare variable NGTD type of DM_TEXT;
    declare variable EDIZM type of DM_TEXT;
    declare variable PARENT_ID type of DM_ID;
    declare variable SUMMA type of DM_DOUBLE;
    declare variable SUMMA_O type of DM_DOUBLE;
    declare variable DISCOUNT type of DM_DOUBLE;
    declare variable ACTIVE_ID type of DM_ID;
    declare variable NAC type of DM_DOUBLE;
    declare variable DOC_DETAIL_ID type of DM_ID;
    declare variable MOTHERPART_ID type of DM_ID;
    declare variable BCODE_IZG type of DM_TEXT;
    declare variable HUMAN_QUANT type of DM_TEXT;
    declare variable SUM_DSC type of DM_DOUBLE;
    declare variable DCARD type of DM_TEXT;
    declare variable PART_TYPE DM_STATUS;
    declare variable BASE_AGENT_ID DM_ID;
    declare variable GROUP_ID DM_ID;
    declare variable MAKE_ID DM_ID_NULL;
    declare variable MNN DM_TEXT;
    declare variable MOTHERPART_UUID DM_UUID_NULL;
    declare variable EGAIS_ID DM_TEXT1024;
    declare variable EGAIS_BREGID DM_TEXT1024;
    declare variable EGAIS_REGID DM_TEXT1024;
    declare variable EGAIS_ALCCODE DM_TEXT1024;
    declare variable CAPACITY DM_DOUBLE;
    declare variable ALC_VOLUME DM_DOUBLE;
    declare variable EGAIS_PRODUCER_ID DM_TEXT1024;
    declare variable EGAIS_QUANT DM_DOUBLE;
    declare variable EGAIS_BARCODE DM_TEXT1024;
    declare variable EGAIS_TYPE DM_TEXT;
    declare variable PRODUCER_INN DM_TEXT1024;
    declare variable PRODUCER_KPP DM_TEXT1024;
    declare variable BOTTLINGDATE DM_DATETIME;
    declare variable EGAIS_PRODUCT_VCODE DM_TEXT;
    begin
    --whith SUM_NDSR
    for
    select
    /*ware_id*/  sname,sizg,scountry,orig_code,sorig_name,sorig_izg,sorig_country,barcode,z_id,sklad_id,
    /*part_id*/  PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE1,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,DATESERT,KEMVSERT,SDSERT,REGN,NGTD,EDIZM,
    PARENT_ID,DISCOUNT,SUMMA,summa_o, id, part_id, nac, doc_detail_id, motherpart_id, dep, bcode_izg, human_quant, SUM_DSC, dcard,
    part_type, BASE_AGENT_ID, group_id, make_id, mnn, motherpart_uuid,
    EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE, CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID, EGAIS_QUANT, EGAIS_BARCODE,
    EGAIS_TYPE, PRODUCER_INN, PRODUCER_KPP, BOTTLINGDATE, EGAIS_PRODUCT_VCODE
    from doc_detail_active where doc_id=:doc_id and part_parent_id=:doc_parent_id
    into
    :sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:barcode,:z_id,:sklad_id,
    :PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE1,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,
    :PARENT_ID,:DISCOUNT,:SUMMA,:summa_o, :active_id, :part_id, :nac, :doc_detail_id, :motherpart_id, :dep, :bcode_izg, :human_quant, :SUM_DSC, :dcard,
    :part_type, :BASE_AGENT_ID, :group_id,:make_id, :mnn, :motherpart_uuid,
    :EGAIS_ID, :EGAIS_BREGID, :EGAIS_REGID, :EGAIS_ALCCODE, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID, :EGAIS_QUANT, :EGAIS_BARCODE,
    :EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE
    do
    begin
    --    if ((price<0.001) or (price is null)) then exception EX_PRICE;
    if ((price<0) or (price is null)) then exception EX_PRICE;
    if (/*(price_o<0.001) or */(price_o is null)) then exception EX_PRICE_O;
    if (/*(quant<0.00001) or */(quant is null)) then exception EX_QUANT;
    if (/*(summa<0.00001) or */(summa is null)) then exception EX_SUMMA;
    if (/*(summa_o<0.00001) or */(summa_o is null)) then exception EX_SUMMA_O;
    --    if ((NDS<0.00001) or (NDS is null)) then exception EX_NDS;
    if (/*(SUM_NDSO<0) or */(SUM_NDSO is null or SUM_NDSR is null )) then exception EX_SUM_NDSO;
    select w_id from pr_get_ware(:sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:bcode_izg,:z_id,:sklad_id,:part_type,:mnn)
    into :ware_id;
    if ((part_id=0) or (part_id is NULL)) then
    select p_id from pr_new_part(:DOC_ID,:part_parent_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,
    :SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id, :part_type,:BASE_AGENT_ID, :sklad_id, :contract_id, :active_id, :group_id,
    :motherpart_uuid, :EGAIS_ID, :EGAIS_REGID, :EGAIS_BREGID, :EGAIS_ALCCODE, :EGAIS_QUANT, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID, :EGAIS_BARCODE,
    :EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE) into :part_id;
    execute procedure PR_DOC_DETAIL_INSERT(:doc_detail_id,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o,:price,:sum_ndso,:sum_ndsr,:nac,:doc_commitdate,:human_quant,:SUM_DSC,:dcard,:active_id,:part_type,:make_id);
    -- обновляем членство в группах
    execute procedure PR_UPDGROUPDETAIL_ACTIVE2PARTS(:active_id,:part_id);
    -- присваиваем ценам партии
    update prices set part_id=:part_id where dda_id=:active_id;
    update or insert into prices (part_id,price_type,currency_id, price,dda_id) values (:part_id,0,0,:price,:active_id) matching (PART_ID,PRICE_TYPE);
    execute procedure PR_DOC_PRIHOD_COMMIT_RECURSE(:doc_id,:active_id,:part_id,:doc_commitdate,:contract_id);
    end
    end;
  ');

end^

SET TERM ; ^

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_CHECK_SUM_NDSR TO SYSDBA;

execute procedure PR_CHECK_SUM_NDSR;

DROP PROCEDURE PR_CHECK_SUM_NDSR;


Исправить триггер DOC_DETAIL_ACTIVE_BI

После условия  if (new.part_id>0) then -- заполняем значения партии , 
дополняем выборку из таблицы PARTS полями

.....
motherpart_uuid,
EGAIS_ID,
EGAIS_REGID,
EGAIS_BREGID,
EGAIS_ALCCODE,
EGAIS_QUANT,
CAPACITY,
ALC_VOLUME,
EGAIS_BARCODE,
EGAIS_TYPE,
PRODUCER_INN,
PRODUCER_KPP,
BOTTLINGDATE,
EGAIS_PRODUCT_VCODE
from parts where id=new.part_id into
......
new.motherpart_uuid,
new.EGAIS_ID,
new.EGAIS_REGID,
new.EGAIS_BREGID,
new.EGAIS_ALCCODE,
new.EGAIS_QUANT,
new.CAPACITY,
new.ALC_VOLUME,
new.EGAIS_BARCODE,
new.EGAIS_TYPE,
new.PRODUCER_INN,
new.PRODUCER_KPP,
new.BOTTLINGDATE,
new.EGAIS_PRODUCT_VCODE;

Дополнить структуру VW_DOC_DETAIL_ACTIVE полями

    MOTHERPART_UUID,
    EGAIS_ID,
    EGAIS_BREGID,
    EGAIS_REGID,
    EGAIS_ALCCODE,
    EGAIS_PRODUCT_VCODE,
    CAPACITY,
    ALC_VOLUME,
    EGAIS_PRODUCER_ID,
    EGAIS_QUANT

как 

da.MOTHERPART_UUID,
EGAIS_ID,
EGAIS_BREGID,
EGAIS_REGID,
EGAIS_ALCCODE,
EGAIS_PRODUCT_VCODE,
CAPACITY,
ALC_VOLUME,
EGAIS_PRODUCER_ID,
EGAIS_QUANT

Дополнить структуру VW_DOC_DETAIL полями

    MOTHERPART_UUID,
    EGAIS_ID,
    EGAIS_BREGID,
    EGAIS_REGID,
    EGAIS_ALCCODE,
    CAPACITY,
    ALC_VOLUME,
    EGAIS_TTNID,
    EGAIS_QUANT

как 

p.motherpart_uuid,
p.EGAIS_ID,
p.EGAIS_BREGID,
p.EGAIS_REGID,
p.EGAIS_ALCCODE,
p.CAPACITY,
p.ALC_VOLUME,
d.EGAIS_ID,
p.EGAIS_QUANT

Дополнить структуру VW_PARTS полями

    EGAIS_ID,
    EGAIS_BREGID,
    EGAIS_REGID,
    EGAIS_ALCCODE

как 

p.EGAIS_ID,
p.EGAIS_BREGID,
p.EGAIS_REGID,
p.EGAIS_ALCCODE

Дополнить структуру VW_DOCS полями

    EGAIS_NUM,
    EGAIS_ID,
    EGAIS_UNITTYPE,
    EGAIS_STATUS,
    EGAIS_STATUS_TEXT

как 

docs.EGAIS_NUM,
docs.EGAIS_ID,
docs.EGAIS_UNITTYPE,
docs.EGAIS_STATUS,
(select out_text from PR_GET_EGAIS_STATUS(docs.EGAIS_STATUS))

Дополнить структуру VW_AGENTS полем

    EGAIS_ID

как 

    EGAIS_ID


ТМС проверки новых накладных от ЕГАИС

ТМС - вспомогательные - добавить новую ТМС "Проверить накладные" 

uses                                                                                                            
  Classes, Graphics, Controls, Forms, Dialogs, ComCtrls, Messages, SysUtils,
  ToolWin, ImgList, dxExEdtr, dxCntner, dxTL, dxDBCtrl, dxDBGrid, StdCtrls,
  unMain,DB, IBQuery, IBDatabase, unDM, DBTables, cfdxUtils, Menus, System,
  Buttons, ExtCtrls, StdCtrls, cfSelectEdit,need,
  gb_table, Grids, ClipBrd, DBGrids, unFrameCustomDict, cfWindows;

var qWork: TIBQuery;
    tmRefresh: TTimer;
    m: TMemo;                                       
                                         
procedure ShowData;                                     
var docnum: string;
begin
  try
  qWork:=dm.TempQuery(nil);                    
  try
    //m.Clear;
    if qWork.Transaction.Active then qWork.Transaction.Rollback;
    qWork.Transaction.StartTransaction;
    qWork.Active:=false;
    qWork.SQL.Text:='select list(docnum) as docnum from docs where status=2 and doc_type = 40';
    qWork.Active:=true;
    docnum:=qWork.FieldByName('docnum').AsString;
    if trim(docnum)<>'' then
    begin
      m.Color:=$008080FF;
      frmManagerXP2.LogIt('Внимание, новые накладные ЕГАИС № '+trim(docnum)+#13#10+'Откройте отложенные документы из журнала.');
    end
    else
      m.Color:=clWhite;
  except                                                                                                        
  //  ShowMessage(GLEM);
  end;
  finally
    qWork.Free;
  end;
end;


procedure tmRefreshTimer;
begin
   ShowData;
end;

begin
 m:=frmManagerXP2.FindComponent('mmLog');
 //m.Font.Name:='Verdana';
 //m.Font.Color:=clBlack;
 //m.Color:=$008080FF;
 ShowData;
 tmRefresh:=TTimer.Create(application);
 tmRefresh.Interval:=5000;
 tmRefresh.OnTimer:='tmRefreshTimer';
end;

Вызов ТМС проверки накладных

ТМС - События - После авторизации пользователя. В коде ХХХ это номер ТМС, созданной в предыдущем пункте 

uses
  Graphics, Controls, Forms, Dialogs, StdCtrls, undm, unMain,
  ComCtrls, ExtCtrls, StrUtils, Windows, Classes, Unit1;  

var
  mainForm: TForm1;
begin
  frmManagerXp2.RunTms(ХХХ,application);
  if dm.IsReg = false then
   begin
    MainForm := TForm1.Create(Application);
    MainForm.ShowModal;
   end;
end;

Настройка Менеджера

Перезапустить Менеджер и в сервис - параметры системы - Настройка ЕГАИС указать адрес сервера ЕГАИС и идентификатор

Сетки

Прикрепленный файл Файл:Сетки ЕГАИС.zip

Проверить УТМ (универсальный транспортный модуль)

Переходим по ссылке на web страницу УТМ, например http://egais:8080/, смотрим версию модуля, версия должна быть не ниже 2.0.1, если ниже, это значит, что УТМ тестовый.

Версия УТМ

Проверка

Запустить Менеджер - Инструменты - ЕГАИС Менеджер - Настройки - Перепроверить идентификаторы всех ТТН. После этого нажать "Проверить ТТН". Ошибок быть не должно

Проверка существующих ТТН 

Теги: ЕГАИС, УТМ, универсальный транспортный модуль

проблемы при продаже алкоголя

если попросят добавить дату розлива в алкогольных магазинах, используем колонку DATESERT

проблемы при продаже алкоголя

  • если ошибка при продаже алкоголя - чтото там про D$UUID и параметр null

выполнить на базе 

CREATE OR ALTER TRIGGER EGAIS_DETAIL_BI_DISTR FOR EGAIS_DETAIL
ACTIVE BEFORE INSERT POSITION 9999
AS
begin
  if (new.d$uuid is null) then
    begin
      new.d$uuid = UUID_TO_CHAR(GEN_UUID());
      new.d$srvupddt = '2000-01-01';
    end
end
  • если ошибка при добавлении в чек алкоголя - чтото про P.PRODUCER_INN или P.PRODUCER_KPP

в таблице PARTS Добавить два поля с доменом DM_TEXT 

PRODUCER_INN
PRODUCER_KPP
  • если в отчёт продажи алкоголя попадают товары не алкоголя 
в процедуре PR_DOC_COMMIT
заменить строки после exception EX_WRONGDOC_BASE_TYPE на 
/* nicky edit */
if (exists (select * from docs d where d.id = :doc_id and d.doc_type in (3, 9))) then
  begin
    insert into egais_detail (doc_detail_id, egais_barcode, producer_inn, producer_kpp) select dd.id, dda.egais_barcode, dda.producer_inn, dda.producer_kpp
      from doc_detail dd join doc_detail_active dda on dd.doc_id = dda.doc_id and dd.part_id = dda.part_id where dd.doc_id = :doc_id and char_length(dda.egais_barcode) > 0;
  end
/* nicky edit end*/

и выполнить скрипт очистки уже попавших товаров 

delete from egais_detail ed1
where ed1.doc_detail_id in
(select dd.id
from egais_detail ed
left join vw_doc_detail dd on dd.id = ed.doc_detail_id
left join vw_docs d on d.id = dd.doc_id
left join vw_warebase wb on wb.part_id = dd.part_id
      where not(char_Length(ed.egais_barcode) > 1)
      and not ((wb.mmbsh like '%-21={%') or (wb.mmbsh like '%-20={%'))
      )
  • в остальном, если срочно - ставим группу пиво - при этом двухмерный штрих код при продаже запрашиваться не будет

или вообще убираем группы - но тогда продаваемый алкоголь не попадёт в журнал розничной продажи

  • если ничего не помогаем - можно попробоват пройтись по пунктам установки егаиса 
1. readme2.txt - изменения в базе по ЕГАИСу
2.alko.txt - изменения по журналу продажи
после п.2 в таблице reports внести в blob поле журнала продажи алкоголя otch_alko.TXT
после этого в таблице GROUPS в  строке с названием алкоголь в поле DATA вносим groups_alko.TXT
а в строке с названием Пиво groups_pivo.TXT

Медиа:Desktop.7z

Источник — «http://wiki.standart-n.ru/index.php?title=Установка_ЕГАИС&oldid=3488»