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

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎проблемы при продаже алкоголя)
(→‎Выполнить скрипт)
Строка 13: Строка 13:
 
==Выполнить скрипт==
 
==Выполнить скрипт==
 
<pre>
 
<pre>
−
SET SQL DIALECT 3;
+
SET TERM ^ ;
−
 
+
--создание доменов если их нет
−
ALTER TABLE DOC_DETAIL ADD FIO_ID DM_ID_NULL;
+
execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^
−
ALTER TABLE DOC_DETAIL ADD FIO_BOLNOY_ID DM_ID_NULL;
+
execute procedure PR_ALTER_VIEW('DM_ID','','BIGINT NOT NULL','DOMAIN');^
−
ALTER TABLE DOC_DETAIL ADD TYPE_LGOTA DM_ID_NULL;
+
execute procedure PR_ALTER_VIEW('DM_STATUS','','INTEGER','DOMAIN');^
−
ALTER TABLE DOC_DETAIL ADD RECEPT DM_TEXT;
+
execute procedure PR_ALTER_VIEW('DM_DATETIME','','TIMESTAMP','DOMAIN');^
−
ALTER TABLE DOC_DETAIL_ACTIVE ADD SUM_NDSR DM_DOUBLE;
+
execute procedure PR_ALTER_VIEW('DM_TEXT1024','','VARCHAR(1024) CHARACTER SET WIN1251 COLLATE WIN1251','DOMAIN');^
−
ALTER TABLE DOC_DETAIL ADD SUM_NDSR DM_DOUBLE;
+
execute procedure PR_ALTER_VIEW('DM_DOUBLE','','DOUBLE PRECISION','DOMAIN');^
−
ALTER TABLE DOCS ADD SUM_NDSR DM_DOUBLE;
+
execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^
−
ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_UUID DM_UUID_NULL;
+
execute procedure PR_ALTER_VIEW('DM_TEXT','','VARCHAR(250) CHARACTER SET WIN1251 COLLATE PXW_CYRL ','DOMAIN');^
−
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 (
+
execute procedure PR_ALTER_VIEW('','','GEN_AGENTS_CONTRACTS_STATUS_ID','generator');^
−
    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);
+
-- таблица AGENTS_CONTRACTS_STATUS
−
CREATE INDEX EGAIS_REQUESTS_IDX1 ON EGAIS_REQUESTS (REPLY_ID);
+
EXECUTE BLOCK AS BEGIN
 +
if (not exists(select 1 from rdb$relations where rdb$relation_name = 'AGENTS_CONTRACTS_STATUS')) then
 +
execute statement '
 +
CREATE TABLE AGENTS_CONTRACTS_STATUS (
 +
    ID      DM_STATUS NOT NULL /* DM_STATUS = INTEGER */,
 +
    CAPTION  DM_TEXT /* DM_TEXT = VARCHAR(250) */
 +
);';
 +
END^
  
−
CREATE OR ALTER TRIGGER EGAIS_REQUESTS_BI FOR EGAIS_REQUESTS
+
/* Trigger: AGENTS_CONTRACTS_STATUS_BI */
 +
CREATE OR ALTER TRIGGER AGENTS_CONTRACTS_STATUS_BI FOR AGENTS_CONTRACTS_STATUS
 
ACTIVE BEFORE INSERT POSITION 0
 
ACTIVE BEFORE INSERT POSITION 0
 
as
 
as
 
begin
 
begin
 
   if (new.id is null) then
 
   if (new.id is null) then
−
     new.id = gen_id(gen_egais_REQUESTS_id,1);
+
     new.id = gen_id(gen_agents_contracts_status_id,1);
−
  if (new.insert_date is null) then
+
end
−
    new.insert_date = 'now';
+
^
−
end;
+
  
−
CREATE OR ALTER TRIGGER EGAIS_REQUESTS_BU0 FOR EGAIS_REQUESTS
+
delete from AGENTS_CONTRACTS_STATUS;^
−
ACTIVE BEFORE UPDATE POSITION 0
+
INSERT INTO AGENTS_CONTRACTS_STATUS (ID, CAPTION)
−
AS
+
                            VALUES (0, 'Активный');^
−
begin
+
INSERT INTO AGENTS_CONTRACTS_STATUS (ID, CAPTION)
−
  if (new.reply_url <> '') then
+
                            VALUES (-1, 'Удален');^
−
    new.reply_date = 'now';
+
INSERT INTO AGENTS_CONTRACTS_STATUS (ID, CAPTION)
−
end;
+
                            VALUES (1, 'Основной');^
  
−
COMMENT ON COLUMN EGAIS_REQUESTS.QUERY_TYPE IS
+
execute procedure PR_ALTER_VIEW('','','GEN_AGENTS_CONTRACTS_ID','generator');^
−
'1 - синхронизация контрагентов
+
execute procedure PR_ALTER_VIEW('','','GEN_AGENTS_CONTRACTS_VNUM','generator');^
−
2 - акт принятия ТТН
+
−
3 - акт отказа от ТТН
+
−
4 - акт расхождения по ТТН';
+
  
−
create or alter procedure PR_NEW_PART (
+
-- таблица AGENTS_CONTRACTS
−
    DOC_ID type of DM_ID,
+
EXECUTE BLOCK AS BEGIN
−
    PARENT_ID type of DM_ID,
+
if (not exists(select 1 from rdb$relations where rdb$relation_name = 'AGENTS_CONTRACTS')) then
−
    WARE_ID type of DM_UUID_NULL,
+
execute statement '
−
    PRICE type of DM_DOUBLE,
+
CREATE TABLE AGENTS_CONTRACTS (
−
    PRICE_O type of DM_DOUBLE,
+
     ID             DM_ID NOT NULL /* DM_ID = BIGINT */
−
    PRICE_Z type of DM_DOUBLE,
+
     );';
−
    PRICE_R type of DM_DOUBLE,
+
END^
−
    QUANT type of DM_DOUBLE,
+
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','ID','','PRIMARY KEY');^
−
    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_TEXT = 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;
+
  
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','PARENT_ID','DM_ID_NULL','TABLE');^
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','AGENT_ID','DM_ID','TABLE');^
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','VNUM','DM_ID_NULL','TABLE');^
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','CONTRACT_DATE','DM_DATETIME','TABLE');^
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','CAPTION','DM_TEXT1024','TABLE');^
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','STATUS','DM_STATUS','TABLE');^
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','START_DATE','DM_DATETIME','TABLE');^
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','END_DATE','DM_DATETIME','TABLE');^
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','BASE_TYPE','DM_STATUS','TABLE');^
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','SUMMA','DM_DOUBLE','TABLE');^
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','BASE_AGENT_ID','DM_ID_NULL','TABLE');^
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','BARCODE','DM_TEXT1024','TABLE');^
 +
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','DELETEDT','DM_DATETIME','TABLE');^
  
−
/* Following GRANT statetements are generated automatically */
+
/* Trigger: AGENTS_CONTRACTS_BI */
−
 
+
CREATE OR ALTER TRIGGER AGENTS_CONTRACTS_BI FOR AGENTS_CONTRACTS
−
GRANT INSERT ON PARTS TO PROCEDURE PR_NEW_PART;
+
ACTIVE BEFORE INSERT POSITION 0
−
 
+
−
/* 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
 
as
 
begin
 
begin
 +
  if (new.id is null) then
 +
    new.id = gen_id(gen_agents_contracts_id,1);
  
−
   if ( (id is null) or (id = 0) )  then out_text = 'Не установлен';
+
   if (new.vnum is null) then
−
  else if (id = 1) then out_text = 'Акт принят';
+
    new.vnum = gen_id(gen_agents_contracts_vnum,1);
−
  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;
+
   if (new.caption is null) then
−
end;
+
    new.caption = 'Договор №'||new.vnum;
  
−
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^
 
end^
  
−
SET TERM ; ^
+
/* Trigger: AGENTS_CONTRACTS_BU0 */
−
 
+
CREATE OR ALTER TRIGGER AGENTS_CONTRACTS_BU0 FOR AGENTS_CONTRACTS
−
/* Existing privileges on this procedure */
+
ACTIVE BEFORE UPDATE POSITION 0
−
 
+
−
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
 
AS
−
declare variable barcode dm_text;
 
−
declare variable counter integer;
 
 
begin
 
begin
−
   counter = 1;
+
   if (new.status = 1) then
−
  for select result_string from pr_split(new.egais_barcode, ascii_char(13)||ascii_char(10)) into :barcode do
+
    update agents_contracts set status = 0 where status = 1;
−
    begin
+
end^
−
      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
+
−
^
+
  
 +
COMMENT ON COLUMN AGENTS_CONTRACTS.STATUS IS
 +
'0 активный
 +
1 основной
 +
-1 удаленный';^
  
−
/* Trigger: EGAIS_DETAIL_BI_DISTR */
+
CREATE OR ALTER VIEW VW_AGENTS_CONTRACTS(
−
CREATE OR ALTER TRIGGER EGAIS_DETAIL_BI_DISTR FOR EGAIS_DETAIL
+
    ID,
−
ACTIVE BEFORE INSERT POSITION 9999
+
    PARENT_ID,
 +
    AGENT_ID,
 +
    AGENT_CAPTION,
 +
    VNUM,
 +
    CONTRACT_DATE,
 +
    CAPTION,
 +
    STATUS,
 +
    STATUS_CAPTION,
 +
    START_DATE,
 +
    END_DATE,
 +
    BASE_TYPE,
 +
    SUMMA,
 +
    BASE_AGENT_ID,
 +
    BARCODE,
 +
    DELETEDT)
 
AS
 
AS
−
begin
+
select
−
  if (new.d$uuid is null) then
+
    ac.id,
−
     begin
+
     ac.parent_id,
−
      new.d$uuid = UUID_TO_CHAR(GEN_UUID());
+
    ac.agent_id,
−
      new.d$srvupddt = '2000-01-01';
+
    a.caption,
−
     end
+
     ac.vnum,
−
end
+
    ac.contract_date,
−
^
+
    ac.caption,
 +
    ac.status,
 +
    acs.caption,
 +
    ac.start_date,
 +
    ac.end_date,
 +
    ac.base_type,
 +
    ac.summa,
 +
    ac.base_agent_id,
 +
    ac.barcode,
 +
    ac.deletedt
 +
from agents_contracts ac
 +
left join agents a on ac.agent_id = a.id
 +
left join agents_contracts_status acs on ac.status = acs.id ;^
  
 +
execute procedure PR_ALTER_VIEW('DOCS','AGENTS_CONTRACT_ID','DM_ID_NULL','TABLE');^
  
−
SET TERM ; ^
+
/* Добавить Справочник "Контракты" */
 +
EXECUTE BLOCK AS BEGIN
 +
if ((select caption from SP$WDICTS where caption = 'Контракты') is null) then
 +
execute statement 'INSERT INTO SP$WDICTS (ID, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INI, BMP, SORTING, FRAMECLASS) VALUES ((select max(id)+1 from SP$WDICTS), 0, ''Контракты'', NULL, ''AGENTS_CONTRACTS'', -1, ''
 +
[insertsql]
 +
insert into agents_contracts (agent_id, status) values (:agent_id:, 0)
  
 +
[deletesql_selected]
  
 +
[deletesql]
 +
update agents_contracts set deletedt=current_date, status = iif(status = -1, 0, -1) where id=:id
  
−
/******************************************************************************/
+
[refreshsql]
−
/***                  PR_DOC_PRIHOD_COMMIT_RECURSE                          ***/
+
select * from vw_agents_contracts where id=:id
−
/******************************************************************************/
+
  
 +
[selectsqlwithdeleted]
 +
select * from vw_agents_contracts where agent_id=:agent_id: order by id, caption
  
 +
[selectsql]
 +
select * from vw_agents_contracts where status <> -1 and agent_id=:agent_id: order by id, caption
  
−
SET TERM ^ ;
+
[main]
 +
sourcetablename=AGENTS_CONTRACTS
 +
returnfieldname=id
 +
captionfieldname=caption
 +
keyfieldname=id
 +
ViewID=agents_contracts
 +
RootGroupTableName=
 +
ShowCaption=Договора
 +
GetCaption=Договора
 +
GroupSelect=0
 +
foldergroup=
 +
initfolder_id=
 +
dataset=0
 +
InitTMSGroup_id=829
 +
folders_visible=0
 +
Canfloating=0
 +
hidetoppanel=0
 +
ActivateDictAction=0
  
−
create or alter procedure PR_INSTALL_COMMIT_RECURSE
+
[cfSelect]
−
as
+
selectfieldexpression=caption
−
begin
+
AllwaysPartial=1
−
 
+
−
  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_TEXT;
+
−
    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_TEXT;
+
−
    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,SUM_NDSR,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,:SUM_NDSR,: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_INSTALL_COMMIT_RECURSE TO SYSDBA;
+
−
 
+
−
execute procedure PR_INSTALL_COMMIT_RECURSE;
+
−
 
+
−
DROP PROCEDURE PR_INSTALL_COMMIT_RECURSE;
+
−
 
+
−
create or alter procedure PR_EDITCUSTOMDOC (
+
−
    DOC_ID type of DM_ID,
+
−
    SESSION_ID type of DM_ID,
+
−
    RGUID type of DM_RGUID)
+
−
returns (
+
−
    CUR_DOC_ID type of DM_ID)
+
−
as
+
−
declare variable VNUM type of DM_ID_NULL;
+
−
declare variable DOC_TYPE type of DM_ID;
+
−
declare variable DOC_BASE_TYPE type of DM_ID;
+
−
declare variable DOC_STATUS type of DM_STATUS;
+
−
declare variable DOC_AGENT_ID type of DM_ID;
+
−
declare variable DOCNUM type of DM_TEXT;
+
−
declare variable DOC_CAPTION type of DM_TEXT;
+
−
declare variable BASE_AGENT_ID integer;
+
−
declare variable DOCDATE type of DM_DATETIME;
+
−
declare variable EGAIS_NUM DM_TEXT;
+
−
declare variable EGAIS_ID DM_TEXT;
+
−
declare variable EGAIS_UNITTYPE DM_TEXT;
+
−
declare variable EGAIS_STATUS DM_STATUS;
+
−
begin
+
−
  select d.doc_type, d.status, dt.base_type, dt.caption, d.docnum, d.docdate, d.agent_id, d.vnum, d.base_agent_id, EGAIS_NUM, EGAIS_ID, EGAIS_UNITTYPE, EGAIS_STATUS from docs d left join doc_types dt on d.doc_type=dt.id where d.id=:doc_id
+
−
    into :doc_type, :doc_status, :doc_base_type, :doc_caption, :docnum, :docdate, :doc_AGENT_ID,:vnum, :BASE_AGENT_ID, :EGAIS_NUM, :EGAIS_ID, :EGAIS_UNITTYPE, :EGAIS_STATUS;
+
  
−
  if (doc_type is null) then exception EX_CANTFINDDOC_ID;
+
[form_show]
−
  if (doc_status in (0,-2,2)) then exception EX_WRONGDOCSTATUS;
+
position=2
−
  if (doc_base_type=3) then exception EX_WRONGDOC_BASE_TYPE;
+
left=0
−
--  if (doc_agent_id <= 0) then exception EX_WRONG_AGENT;
+
Top=0
−
  cur_doc_id=gen_id(gen_docs_id,1);
+
Width=900
−
  insert into docs (ID,PARENT_ID,DOC_TYPE,STATUS,AGENT_ID,RGUID,AUDIT_ID,DOCNUM,DOCDATE,DELETED_DOC_ID, BASE_AGENT_ID, EGAIS_NUM, EGAIS_ID, EGAIS_UNITTYPE, EGAIS_STATUS)
+
Height=600
−
    values (:cur_doc_id,0,:DOC_TYPE,0,:doc_AGENT_ID,:RGUID,:session_ID,:DOCNUM,:DOCDATE,:DOC_ID, :BASE_AGENT_ID, :EGAIS_NUM, :EGAIS_ID, :EGAIS_UNITTYPE, :EGAIS_STATUS);
+
MaxWidth=0
−
  -- Обновление номера документа на старое значение
+
MaxHeight=0
−
  UPDATE docs set vnum = :vnum, caption=:doc_caption || ' №' || :vnum  where id = :cur_doc_id;
+
MinWidth=500
 +
MinHeight=400
  
−
  if (doc_base_type=1) then --приход
+
[form_get]
−
  begin
+
position=2
−
  if (doc_status=1) then --
+
left=0
−
  begin
+
Top=0
−
    insert into DOC_DETAIL_ACTIVE (doc_id,part_id,quant,summa,discount,koef,sum_dsc,sum_ndso,price)
+
Width=900
−
      select :cur_doc_id,part_id,quant,summa,discount,0,sum_dsc,sum_ndso,price from doc_detail where doc_id=:doc_id;
+
Height=600
−
    update DOC_DETAIL_ACTIVE dda set dda.part_id=iif((select count(*) from doc_detail where part_id = dda.part_id) > 1,dda.part_id,0) where dda.doc_id=:cur_doc_id;
+
MaxWidth=0
−
  end
+
MaxHeight=0
−
  else
+
MinWidth=500
−
  begin
+
MinHeight=400
−
    if (doc_status=-1) then
+
−
    begin
+
−
      insert into DOC_DETAIL_ACTIVE (doc_id,part_id,quant,summa,discount,koef,sum_dsc,price)
+
−
        select :cur_doc_id,part_id,quant,summa,discount,0,sum_dsc,price from doc_detail_deleted where doc_id=:doc_id;
+
−
      --update DOC_DETAIL_ACTIVE set part_id=0 where doc_id=:cur_doc_id;
+
−
      update DOC_DETAIL_ACTIVE dda set dda.part_id=iif((select count(*) from doc_detail where part_id = dda.part_id) > 1,dda.part_id,0) where dda.doc_id=:cur_doc_id;
+
−
    end
+
−
  end
+
−
  end
+
−
  else if ((doc_base_type=2) or (doc_base_type=7)) then  --расход
+
−
  begin
+
−
    if (doc_status=1) then
+
−
    begin
+
−
      insert into doc_detail_active (doc_id,part_id,quant,dcard,summa,summa_o,nac,discount,sum_ndso,koef,sum_dsc,price)
+
−
        select :cur_doc_id,part_id,quant,dcard,summa,summa_o,nac,discount,sum_ndso,1,sum_dsc,price from doc_detail where doc_id=:doc_id;
+
−
    end
+
−
    else
+
−
    begin
+
−
      if (doc_status=-1) then
+
−
      begin
+
−
        insert into doc_detail_active (doc_id,part_id,quant,dcard,summa,summa_o,nac,discount,sum_ndso,koef,sum_dsc,price)
+
−
          select :cur_doc_id,part_id,quant,dcard,summa,summa_o,nac,discount,sum_ndso,1,sum_dsc,price from doc_detail_deleted where doc_id=:doc_id;
+
−
      end
+
−
    end
+
−
  end
+
  
−
  suspend;
+
[childs]
−
end;
+
bottomdock_units=0
 +
bottomdock_size=0
 +
rightdock_units=0
 +
rightdock_size=0
  
 +
[editfields]
 +
VNUM=default
 +
CONTRACT_DATE=default
 +
CAPTION=default
 +
START_DATE=default
 +
END_DATE=default
 +
BASE_TYPE=default
 +
SUMMA=default
 +
BASE_AGENT_ID=default
 +
BARCODE=default
 +
STATUS=WDICTS.AGENTS_CONTRACTS_STATUS(initvalue=status)
 +
status_caption=status
 +
'', NULL, NULL, NULL);';
 +
END^
  
 +
/* Добавить Справочник "Контракты статусы" */
 +
EXECUTE BLOCK AS BEGIN
 +
if ((select caption from SP$WDICTS where caption = 'Контракты статусы') is null) then
 +
execute statement 'INSERT INTO SP$WDICTS (id, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INI, BMP, SORTING, FRAMECLASS) VALUES ((select max(id)+1 from SP$WDICTS),0, ''Контракты статусы'', NULL, ''AGENTS_CONTRACTS_STATUS'', 0, ''
 +
[insertsql]
  
−
/* VW_DOC_DETAIL_ACTIVE */
+
[deletesql_selected]
  
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','MOTHERPART_UUID','da.motherpart_uuid');
+
[deletesql]
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','EGAIS_ID','EGAIS_ID');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','EGAIS_BREGID','EGAIS_BREGID');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','EGAIS_REGID','EGAIS_REGID');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','EGAIS_ALCCODE','EGAIS_ALCCODE');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','CAPACITY','CAPACITY');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','ALC_VOLUME','ALC_VOLUME');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','EGAIS_PRODUCER_ID','EGAIS_PRODUCER_ID');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','EGAIS_QUANT','EGAIS_QUANT');
+
  
 +
[refreshsql]
 +
select * from agents_contracts_status where id=:id
  
−
/* Дополнить структуру VW_DOC_DETAIL */
+
[selectsqlwithdeleted]
  
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MOTHERPART_UUID','p.motherpart_uuid');
+
[selectsql]
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','EGAIS_ID','p.EGAIS_ID');
+
select * from agents_contracts_status where id <> -1 order by id
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','EGAIS_BREGID','p.EGAIS_BREGID');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','EGAIS_REGID','p.EGAIS_REGID');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','EGAIS_ALCCODE','p.EGAIS_ALCCODE');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','CAPACITY','p.CAPACITY');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','ALC_VOLUME','p.ALC_VOLUME');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','EGAIS_TTNID','d.EGAIS_ID');
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','EGAIS_QUANT','p.EGAIS_QUANT');
+
  
 +
[main]
 +
sourcetablename=AGENTS_CONTRACTS_STATUS
 +
returnfieldname=id
 +
captionfieldname=caption
 +
keyfieldname=id
 +
ViewID=agents_contracts_status
 +
RootGroupTableName=
 +
ShowCaption=Статус
 +
GetCaption=Статус
 +
GroupSelect=0
 +
foldergroup=
 +
initfolder_id=
 +
dataset=0
 +
InitTMSGroup_id=0
 +
folders_visible=0
 +
Canfloating=0
 +
hidetoppanel=0
 +
ActivateDictAction=0
  
−
/* VW_PARTS */
+
[cfSelect]
 +
selectfieldexpression=caption
 +
AllwaysPartial=1
  
−
execute procedure PR_ALTER_VIEW('VW_PARTS','EGAIS_ID','p.EGAIS_ID');
+
[form_show]
−
execute procedure PR_ALTER_VIEW('VW_PARTS','EGAIS_BREGID','p.EGAIS_BREGID');
+
position=8
−
execute procedure PR_ALTER_VIEW('VW_PARTS','EGAIS_REGID','p.EGAIS_REGID');
+
left=0
−
execute procedure PR_ALTER_VIEW('VW_PARTS','EGAIS_ALCCODE','p.EGAIS_ALCCODE');
+
Top=0
 +
Width=300
 +
Height=200
 +
MaxWidth=0
 +
MaxHeight=0
 +
MinWidth=300
 +
MinHeight=200
  
 +
[form_get]
 +
position=8
 +
left=0
 +
Top=0
 +
Width=300
 +
Height=200
 +
MaxWidth=0
 +
MaxHeight=0
 +
MinWidth=300
 +
MinHeight=200
  
−
/* Дополнить структуру VW_DOCS полями */
+
[childs]
 +
bottomdock_units=0
 +
bottomdock_size=0
 +
rightdock_units=0
 +
rightdock_size=0
  
−
execute procedure PR_ALTER_VIEW('VW_DOCS','EGAIS_NUM','docs.EGAIS_NUM');
+
[editfields]
−
execute procedure PR_ALTER_VIEW('VW_DOCS','EGAIS_ID','docs.EGAIS_ID');
+
CAPTION=default
−
execute procedure PR_ALTER_VIEW('VW_DOCS','EGAIS_UNITTYPE','docs.EGAIS_UNITTYPE');
+
−
execute procedure PR_ALTER_VIEW('VW_DOCS','EGAIS_STATUS','docs.EGAIS_STATUS');
+
−
execute procedure PR_ALTER_VIEW('VW_DOCS','EGAIS_STATUS_TEXT','(select out_text from PR_GET_EGAIS_STATUS(docs.EGAIS_STATUS))');
+
  
 +
[addfields]
 +
CAPTION=default
 +
'', NULL, NULL, NULL);';
 +
END^
  
−
/* Дополнить структуру VW_AGENTS полем */
+
execute procedure pr_alter_view('DOCS','DOCS_BI','if (new.agents_contract_id is null) then
 +
    new.agents_contract_id = (select first 1 id from agents_contracts where agent_id = new.agent_id and status = 1);
 +
  if (new.agents_contract_id is null) then new.agents_contract_id = 0;','TRIGGER','BEFORE INSERT')
  
−
execute procedure PR_ALTER_VIEW('VW_AGENTS','EGAIS_ID','EGAIS_ID');
+
--execute procedure PR_ALTER_VIEW('VW_DOCS','AGENT_CONTRACT_CAPTION','w.mgn_id','VIEW'); join пока нет возможности в автоматическом режиме добавлять
  
 
</pre>
 
</pre>

Версия 14:31, 6 июля 2016

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

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


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

PR_ALTER_VIEW

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

Для скриптов ниже актуальная версия Менеджера ManagerXP2_272_62(20160321_155233) кассира zkassa 2.2.1.22.exe

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

SET TERM ^ ;
--создание доменов если их нет
execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_ID','','BIGINT NOT NULL','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_STATUS','','INTEGER','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_DATETIME','','TIMESTAMP','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_TEXT1024','','VARCHAR(1024) CHARACTER SET WIN1251 COLLATE WIN1251','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_DOUBLE','','DOUBLE PRECISION','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_TEXT','','VARCHAR(250) CHARACTER SET WIN1251 COLLATE PXW_CYRL ','DOMAIN');^

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

-- таблица AGENTS_CONTRACTS_STATUS
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from rdb$relations where rdb$relation_name = 'AGENTS_CONTRACTS_STATUS')) then
execute statement '
CREATE TABLE AGENTS_CONTRACTS_STATUS (
    ID       DM_STATUS NOT NULL /* DM_STATUS = INTEGER */,
    CAPTION  DM_TEXT /* DM_TEXT = VARCHAR(250) */
);';
END^

/* Trigger: AGENTS_CONTRACTS_STATUS_BI */
CREATE OR ALTER TRIGGER AGENTS_CONTRACTS_STATUS_BI FOR AGENTS_CONTRACTS_STATUS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_agents_contracts_status_id,1);
end
^

delete from AGENTS_CONTRACTS_STATUS;^
INSERT INTO AGENTS_CONTRACTS_STATUS (ID, CAPTION)
                             VALUES (0, 'Активный');^
INSERT INTO AGENTS_CONTRACTS_STATUS (ID, CAPTION)
                             VALUES (-1, 'Удален');^
INSERT INTO AGENTS_CONTRACTS_STATUS (ID, CAPTION)
                             VALUES (1, 'Основной');^

execute procedure PR_ALTER_VIEW('','','GEN_AGENTS_CONTRACTS_ID','generator');^
execute procedure PR_ALTER_VIEW('','','GEN_AGENTS_CONTRACTS_VNUM','generator');^

-- таблица AGENTS_CONTRACTS
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from rdb$relations where rdb$relation_name = 'AGENTS_CONTRACTS')) then
execute statement '
CREATE TABLE AGENTS_CONTRACTS (
    ID             DM_ID NOT NULL /* DM_ID = BIGINT */
    );';
END^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','ID','','PRIMARY KEY');^

execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','PARENT_ID','DM_ID_NULL','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','AGENT_ID','DM_ID','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','VNUM','DM_ID_NULL','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','CONTRACT_DATE','DM_DATETIME','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','CAPTION','DM_TEXT1024','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','STATUS','DM_STATUS','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','START_DATE','DM_DATETIME','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','END_DATE','DM_DATETIME','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','BASE_TYPE','DM_STATUS','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','SUMMA','DM_DOUBLE','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','BASE_AGENT_ID','DM_ID_NULL','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','BARCODE','DM_TEXT1024','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','DELETEDT','DM_DATETIME','TABLE');^

/* Trigger: AGENTS_CONTRACTS_BI */
CREATE OR ALTER TRIGGER AGENTS_CONTRACTS_BI FOR AGENTS_CONTRACTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_agents_contracts_id,1);

  if (new.vnum is null) then
    new.vnum = gen_id(gen_agents_contracts_vnum,1);

  if (new.caption is null) then
    new.caption = 'Договор №'||new.vnum;

end^

/* Trigger: AGENTS_CONTRACTS_BU0 */
CREATE OR ALTER TRIGGER AGENTS_CONTRACTS_BU0 FOR AGENTS_CONTRACTS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (new.status = 1) then
    update agents_contracts set status = 0 where status = 1;
end^

COMMENT ON COLUMN AGENTS_CONTRACTS.STATUS IS
'0 активный
1 основной
-1 удаленный';^

CREATE OR ALTER VIEW VW_AGENTS_CONTRACTS(
    ID,
    PARENT_ID,
    AGENT_ID,
    AGENT_CAPTION,
    VNUM,
    CONTRACT_DATE,
    CAPTION,
    STATUS,
    STATUS_CAPTION,
    START_DATE,
    END_DATE,
    BASE_TYPE,
    SUMMA,
    BASE_AGENT_ID,
    BARCODE,
    DELETEDT)
AS
select
    ac.id,
    ac.parent_id,
    ac.agent_id,
    a.caption,
    ac.vnum,
    ac.contract_date,
    ac.caption,
    ac.status,
    acs.caption,
    ac.start_date,
    ac.end_date,
    ac.base_type,
    ac.summa,
    ac.base_agent_id,
    ac.barcode,
    ac.deletedt
from agents_contracts ac
left join agents a on ac.agent_id = a.id
left join agents_contracts_status acs on ac.status = acs.id ;^

execute procedure PR_ALTER_VIEW('DOCS','AGENTS_CONTRACT_ID','DM_ID_NULL','TABLE');^

/* Добавить Справочник "Контракты" */
EXECUTE BLOCK AS BEGIN
if ((select caption from SP$WDICTS where caption = 'Контракты') is null) then
execute statement 'INSERT INTO SP$WDICTS (ID, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INI, BMP, SORTING, FRAMECLASS) VALUES ((select max(id)+1 from SP$WDICTS), 0, ''Контракты'', NULL, ''AGENTS_CONTRACTS'', -1, ''
[insertsql]
insert into agents_contracts (agent_id, status) values (:agent_id:, 0)

[deletesql_selected]

[deletesql]
update agents_contracts set deletedt=current_date, status = iif(status = -1, 0, -1) where id=:id

[refreshsql]
select * from vw_agents_contracts where id=:id

[selectsqlwithdeleted]
select * from vw_agents_contracts where agent_id=:agent_id: order by id, caption

[selectsql]
select * from vw_agents_contracts where status <> -1 and agent_id=:agent_id: order by id, caption

[main]
sourcetablename=AGENTS_CONTRACTS
returnfieldname=id
captionfieldname=caption
keyfieldname=id
ViewID=agents_contracts
RootGroupTableName=
ShowCaption=Договора
GetCaption=Договора
GroupSelect=0
foldergroup=
initfolder_id=
dataset=0
InitTMSGroup_id=829
folders_visible=0
Canfloating=0
hidetoppanel=0
ActivateDictAction=0

[cfSelect]
selectfieldexpression=caption
AllwaysPartial=1

[form_show]
position=2
left=0
Top=0
Width=900
Height=600
MaxWidth=0
MaxHeight=0
MinWidth=500
MinHeight=400

[form_get]
position=2
left=0
Top=0
Width=900
Height=600
MaxWidth=0
MaxHeight=0
MinWidth=500
MinHeight=400

[childs]
bottomdock_units=0
bottomdock_size=0
rightdock_units=0
rightdock_size=0

[editfields]
VNUM=default
CONTRACT_DATE=default
CAPTION=default
START_DATE=default
END_DATE=default
BASE_TYPE=default
SUMMA=default
BASE_AGENT_ID=default
BARCODE=default
STATUS=WDICTS.AGENTS_CONTRACTS_STATUS(initvalue=status)
status_caption=status
'', NULL, NULL, NULL);';
END^

/* Добавить Справочник "Контракты статусы" */
EXECUTE BLOCK AS BEGIN
if ((select caption from SP$WDICTS where caption = 'Контракты статусы') is null) then
execute statement 'INSERT INTO SP$WDICTS (id, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INI, BMP, SORTING, FRAMECLASS) VALUES ((select max(id)+1 from SP$WDICTS),0, ''Контракты статусы'', NULL, ''AGENTS_CONTRACTS_STATUS'', 0, ''
[insertsql]

[deletesql_selected]

[deletesql]

[refreshsql]
select * from agents_contracts_status where id=:id

[selectsqlwithdeleted]

[selectsql]
select * from agents_contracts_status where id <> -1 order by id

[main]
sourcetablename=AGENTS_CONTRACTS_STATUS
returnfieldname=id
captionfieldname=caption
keyfieldname=id
ViewID=agents_contracts_status
RootGroupTableName=
ShowCaption=Статус
GetCaption=Статус
GroupSelect=0
foldergroup=
initfolder_id=
dataset=0
InitTMSGroup_id=0
folders_visible=0
Canfloating=0
hidetoppanel=0
ActivateDictAction=0

[cfSelect]
selectfieldexpression=caption
AllwaysPartial=1

[form_show]
position=8
left=0
Top=0
Width=300
Height=200
MaxWidth=0
MaxHeight=0
MinWidth=300
MinHeight=200

[form_get]
position=8
left=0
Top=0
Width=300
Height=200
MaxWidth=0
MaxHeight=0
MinWidth=300
MinHeight=200

[childs]
bottomdock_units=0
bottomdock_size=0
rightdock_units=0
rightdock_size=0

[editfields]
CAPTION=default

[addfields]
CAPTION=default
'', NULL, NULL, NULL);';
END^

execute procedure pr_alter_view('DOCS','DOCS_BI','if (new.agents_contract_id is null) then
    new.agents_contract_id = (select first 1 id from agents_contracts where agent_id = new.agent_id and status = 1);
  if (new.agents_contract_id is null) then new.agents_contract_id = 0;','TRIGGER','BEFORE INSERT')

--execute procedure PR_ALTER_VIEW('VW_DOCS','AGENT_CONTRACT_CAPTION','w.mgn_id','VIEW'); join пока нет возможности в автоматическом режиме добавлять

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

1) Комментируем строки стандартного приведения (!!!ТОЛЬКО ДЛЯ ПРОДУКТОВ!!!) 

/*if (new.ware_id<0) then
begin
select goodname, goodizg, goodcountry from PR_MAKEGOODWAREVALUES(new.sname,new.sorig_name,new.sizg,new.sorig_izg,new.scountry,new.sorig_country,new.orig_code,new.id,new.part_type)
into new.sname, new.sizg,new.scountry;
end
*/

2) После условия 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;

3) В конце триггера пишем (!!!ТОЛЬКО ДЛЯ ПРОДУКТОВ!!!) 

--20160212 Приведение наименований для ЕГАИС, стандартное д.б. отключено
if ((select doc_type from docs where id = new.doc_id)=40) then
if ( (new.capacity is not null) and
     (new.bcode_izg is null or trim(new.bcode_izg) = '') and
     (select count(*) from warebase w left join parts p on p.id = w.part_id
          where (sname = new.sname or sorig_name = new.sname or sname = new.sorig_name or sorig_name = new.sorig_name) and
                (bcode_izg is not null and trim(bcode_izg) <> '') and
                (coalesce(p.capacity,0) = new.capacity) and
                (coalesce(p.egais_alccode,0) = coalesce(new.egais_alccode,0)) ) > 0
   )
  then
  begin
    select first 1 sname, name_id, bcode_izg, w.price from warebase w left join parts p on p.id = w.part_id
           where ( (sname = new.sname or sorig_name = new.sname or sname = new.sorig_name or sorig_name = new.sorig_name) and
                   (bcode_izg is not null and trim(bcode_izg) <> '') and
                   (coalesce(p.capacity,0) = new.capacity) and
                   (coalesce(p.egais_alccode,0) = coalesce(new.egais_alccode,0)) )
     into new.sname, new.name_id, new.bcode_izg, new.price;
     new.summa = new.price*new.quant;
     if (new.price_o<>0) then new.nac=(new.price-new.price_o)*100/new.price_o;
  End

Новый метод редактирования штрихкодов (!!!ТОЛЬКО ДЛЯ ПРОДУКТОВ!!!)

CREATE UNIQUE INDEX WARES_IDX1 ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE);

SET TERM ^ ;

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
if (orig_code is null) then orig_code='';
if (barcode is null) then barcode='';
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 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 barcode=:barcode into :w_id;
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^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_GET_WARE;
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GET_WARE;

/* Existing privileges on this procedure */

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

SET TERM ^ ;

create or alter procedure PR_GETWARE_BY_IDS (
    NAME_ID type of DM_ID,
    IZG_ID type of DM_ID,
    COUNTRY_ID type of DM_ID,
    ORIG_CODE type of DM_TEXT,
    ORIG_NAME_ID type of DM_ID,
    ORIG_IZG_ID type of DM_ID,
    ORIG_COUNTRY_ID type of DM_ID,
    DOINSERT type of DM_STATUS,
    SEARCHINLOGS type of DM_STATUS,
    BARCODE DM_TEXT)
returns (
    WARE_ID type of DM_ID)
as
begin
  if (orig_code is null) then orig_code='';
  if (searchinlogs is null) then searchinlogs=0;
  if (searchinlogs=1) then
  begin 
    select first 1 id from wares_log 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 barcode=:barcode
      into :ware_id;
      if ((select id from wares where id = :ware_id) is null) then ware_id = null;
  end
  else
  begin
    select first 1 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 barcode=:barcode
      into :ware_id;
  end
  if ((ware_id is null) and (DOINSERT=1) and (searchinlogs<>1)) then
  begin
    ware_id=gen_id(gen_wares_id,1);
--    ware_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,Z_ID,SKLAD_ID, barcode)
     values
       (:ware_id,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,0,'',:barcode);
  end

  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON WARES_LOG TO PROCEDURE PR_GETWARE_BY_IDS;
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GETWARE_BY_IDS;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_UPDATEWARE;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_UPDATEWARE4PART;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO SYSDBA;

Update params p set p.param_value = '1' where p.param_id ='BCODE_IZG_EDIT_MODE';

Update params p set p.param_value = 'Нет' where p.param_id ='ELNAKL_MAKE_LOCAL_GN';
Update params p set p.param_value = 'Нет' where p.param_id ='ELNAKL_MAKE_GLOBAL_GN';

ТМС проверки новых накладных от ЕГАИС (!!!ТОЛЬКО ДЛЯ ПРОДУКТОВ!!!)

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

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;

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

Перезапустить Менеджер: 

1. в сервис - параметры системы - Настройка ЕГАИС указать адрес сервера ЕГАИС и идентификатор;
2. в сервис - параметры системы - автоматизация отключить "Приводить наименования к существующим" и "Приводить наименования через глобальный справочник" (!!!ТОЛЬКО ДЛЯ ПРОДУКТОВ!!!).

Сетки

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

Склейка партий(!!!только для продуктов)

в менеджере ТМС перед авторизвацией пользователя изменить запрос ищущий партии для склейки на подобный(или добавить нужные поля вручную - объём, содержание спирта...) 

Select  count(part_id),sname,base_agent_id,price, docagent,capacity, sum(quant) as quant
from VW_WAREBASE
      where quant <> 0
      group by sname,base_agent_id,price, docagent,capacity
      having count(part_id)>1

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

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

Версия УТМ

Проверка

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

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

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

установка журнала продажи алкоголя

после выполнения скрипта расположенного ниже - поместить в таблице REPORTS в отчёт - журнал продажи алкоголя в аоле data содержимое файла otch_alko.txt из архива Медиа:Alko.zip также в таблице groups поместить в поле data в строку Пиво содержимое файла groups_pivo.TXT из того же архива и в строку Алкоголь файл groups_alko.TXT 

CREATE OR ALTER VIEW VW_ALKO(
    ID,
    DOC_TYPE,
    CAPTION,
    DOCDATE,
    EGAIS_BARCODE,
    SNAME,
    ORIG_CODE,
    CAPACITY,
    QUANT)
AS
select d.id, d.doc_type, d.caption, d.docdate, ed.egais_barcode as egais_barcode, dd.sname, p.EGAIS_PRODUCT_VCODE, dd.capacity, iif(ed.egais_barcode='-',dd.quant,-1)
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 parts p on dd.part_id = p.id
where d.doc_type = 3
;


INSERT INTO REPORTS ( PARENT_ID, STATUS, REPORTTYPE, SORTING, CAPTION, PARAMS, WDICT_ID, DATA) VALUES ( 0, 0, 0, 0, 'Журнал продажи алкоголя', '', 25, NULL);


INSERT INTO GROUPS (ID, PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID, PACKET, SID) VALUES (-20, -8, 'Алкоголь', 'PARTS.NAME_ID', 0, '30-DEC-2015 14:41:25.011', 1, NULL, -1, NULL, 16777088, 8, 0, 6379, NULL);


INSERT INTO GROUPS (ID, PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID, PACKET, SID) VALUES (-21, -20, 'Пиво', 'PARTS.NAME_ID', 0, '30-DEC-2015 14:41:43.135', 1, NULL, -1, NULL, 516088, 8, 0, 6393, NULL);

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

  • достаточно часто стала вылезать при продаже ошибка - не удалось отправить данные в ЕГАИС - скорее всего подвис модуль УТМ - лучший вариант перезагрузить компьютер с УТМ, либо попробовать службу рестартануть, но както не всегда она поднимается
  • для заполнения кодов вида продукции удобно воспользоватся фильтром в менеджере 
((wb.mmbsh like ('%-21=%')) or (wb.mmbsh like ('%-20=%')) ) and (trim(wb.egais_product_vcode) = '' or (wb.egais_product_vcode is null))
  • если при продаже выскакивает сообщение 
java.lang.IllegalArgumentException: org.xml.sax.SAXParseException; cvc-pattern-valid: Value '133-103995646074040516326020775185' is not facet-valid with respect to pattern '\d\d[a-zA-Z0-9]{21}\d[0-1]\d[0-3]\d{10}[a-zA-Z0-9]{31}' for type 'BK'

и значение Value состоит только из цифр - не тот штрихкод отсканировали, на акцизе два штрихкода, один маленький, другой большой, пусть закроют пальцем маленький и сканируют большой, в нём должны быть цифры и латинские буквы!!!

  • если выскакивает ошибка что то связанное с CODE(нет под рукой примера, ошибка похожа на ту что выше но несколько отличается)... скорее всего не заполнено поле EGAIS_PRODUCT_VCODE - код вида продукции... значит товар был оприходован вручную, а не через ЕГАИС, надо заполнить соответсвенно справочника - в интернете легко найти - искать код вида продукции алкоголь 
update
parts p
set p.egais_product_vcode=200
where 1=1
and (select w.sname from wares w where w.id=p.ware_id) containing 'водка'
and p.egais_product_vcode is null

update
parts p
set p.egais_product_vcode=500
where 1=1
and (select w.sname from wares w where w.id=p.ware_id) containing 'пиво'
and p.egais_product_vcode is null
  • если выскакивает ошибка что то связанное с KP - в параметрах системы неправильно записан КПП
  • если не отправляются запросы в егаис, и в ЕГАИС менеджере есть только отправка запроса, но нет ответов - надо перезапустить службу УТМ, подвисла, или перезагрузить компьютер с УТМ либо если не помогает можно посмотретьнастройки брэндмауэра на компьютере с УТМ
  • если попросят добавить дату розлива в алкогольных магазинах, используем колонку DATESERT
  • если при проведении документа прихода алкоголя выходит ошибка "Conversion error from string "несколько цифр через запятую" At procedure 'PR_DOC_PRIHOD_COMMIT_RECURSE'..."

изменяем в процедурах PR_NEW_PART и PR_DOC_PRIHOD_COMMIT_RECURSE тип переменной egais_id на DM_TEXT

  • если в отчёт продажи алкоголя попадают товары не алкоголя 
в процедуре 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 ((dd.mmbsh like '%-21={%') or (dd.mmbsh like '%-20={%'))
      )
  • в остальном, если срочно - ставим группу пиво - при этом двухмерный штрих код при продаже запрашиваться не будет

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

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