/* Server version: WI-V6.3.7.18553 Firebird 2.1 
   SQLDialect: 3. ODS: 11.1. Forced writes: On. Sweep inteval: 20000.
   Page size: 8192. Cache pages: 9500 (76000 Kb). Read-only: False. */

SET SQL DIALECT 3;

ALTER TABLE EAI_P2F_TRANSFER_LOG ADD GROUP_NAME NAME_50;

ALTER TABLE EAI_P2F_TRANSFER_LOG ADD TRANSFER_QTY CURRENCY_LOW;

/* Create Procedure... */
SET TERM ^ ;

CREATE PROCEDURE EAI_EVO_TRANSFER$INT(ID_END_WORK INTEGER,
ID_SMETKA_HEAD INTEGER,
ID_EAI_MAP INTEGER,
EXTERNAL_IFC VARCHAR(100),
ID_EAI_MAP_ALLINCLUSIVE INTEGER = null)
 RETURNS(ID_MAP_TAG INTEGER,
STOCK_QTY NUMERIC(15,4),
STOCK_VALUE NUMERIC(15,4),
SINGLE_PRICE NUMERIC(15,4),
GROUP_NAME VARCHAR(50),
SMETKA_LINE_IDS BLOB SUB_TYPE 1)
 AS
 BEGIN SUSPEND; END
^

CREATE PROCEDURE EAI_EVO_TRANSFER$P2E(ID_END_WORK INTEGER,
HOTEL VARCHAR(50),
ID_SMETKA_HEAD INTEGER,
ID_EAI_MAP INTEGER,
EXTERNAL_IFC VARCHAR(100),
GUEST_BOARD VARCHAR(30) = null)
 RETURNS(TAG_VALUE VARCHAR(30),
TAG_NAME VARCHAR(50),
DESCRIPTION VARCHAR(50),
TRANSFER_QTY NUMERIC(15,4),
TRANSFER_AMOUNT NUMERIC(15,4),
ALL_INCLUSIVE_PAYMENT_TYPE INTEGER,
UNIQUE_SALES_NUMBER VARCHAR(30))
 AS
 BEGIN SUSPEND; END
^


/*  Empty FISCALIZE_PREPARE$FISC for FISCALIZE_PREPARE_LINES$INT(param list change)  */
ALTER PROCEDURE FISCALIZE_PREPARE$FISC(ID_DOC_HEAD INTEGER,
DETAIL_LEVEL VARCHAR(20),
ADDITIONAL_PAYMENT_TEXT VARCHAR(30) = null)
 RETURNS(UNIQUE_SALES_NUMBER VARCHAR(30),
FISCALIZATION_DETAILS VARCHAR(255),
VOID_REASON VARCHAR(30),
IS_VOID /* TYPE OF TLOGICAL */ SMALLINT)
 AS
 BEGIN SUSPEND; END
^

/* Alter empty procedure FISCALIZE_PREPARE_LINES$INT with new param-list */
ALTER PROCEDURE FISCALIZE_PREPARE_LINES$INT(ID_DOC_HEAD INTEGER)
 RETURNS(FISC_PRICE NUMERIC(15,4),
FISC_QTY NUMERIC(15,4),
FISC_TEXT VARCHAR(30),
FISC_GRP VARCHAR(10))
 AS
 BEGIN SUSPEND; END
^

/* Alter Procedure... */
/* empty dependent procedure body */
/* Clear: BILL_PAYMENT_EXTERNAL_DATA for: BILL_PAYMENT_EXT_DATA_PARSE */
ALTER PROCEDURE BILL_PAYMENT_EXTERNAL_DATA(ID_SMETKA_PAYMENT INTEGER,
EXTERNAL_IFC VARCHAR(200),
EXTERNAL_DATA VARCHAR(2000),
INTERNAL_DATA VARCHAR(2000))
 RETURNS(ERR_CODE INTEGER,
ERR_TEXT VARCHAR(500))
 AS
 BEGIN SUSPEND; END
^

/* Alter (BILL_PAYMENT_EXT_DATA_PARSE) */
ALTER PROCEDURE BILL_PAYMENT_EXT_DATA_PARSE(EXTERNAL_IFC VARCHAR(200),
EXTERNAL_DATA VARCHAR(2000))
 RETURNS(ID_ACCOUNT INTEGER,
GUEST_NAME VARCHAR(100),
ROOM_NAME VARCHAR(100),
HOTEL_NAME VARCHAR(100),
BOARD_NAME VARCHAR(30))
 AS
declare variable target_name varchar(100);
  declare variable target_room varchar(100);
  declare variable target_board varchar(100);
  declare variable rcpt_account varchar(100);
  declare variable database_name varchar(100);
  declare variable item varchar(4000);
  declare variable hotel_name_db varchar(100);
begin

  if (:EXTERNAL_IFC in ('plugin.pos2f36', 'plugin.pos2evo'))  then begin
    target_name = 'tname';
    target_room = 'troom';
    target_board = 'board';
    rcpt_account = 'postAccount';
    database_name = 'db';
  end else if (:EXTERNAL_IFC in ('front35ifc.POSitiveToFront35', 'ole.front35ifc.POSitiveToFront35')) then begin
    target_name = 'TARGET_NAME';
    target_room = 'TARGET_ROOM';
    rcpt_account = 'RCPT_ACCOUNT';
    database_name = 'DATABASE_NAME';
  end

  if (:EXTERNAL_IFC in ('plugin.pos2f36', 'front35ifc.POSitiveToFront35', 'ole.front35ifc.POSitiveToFront35', 'plugin.pos2evo')) then begin
    id_account = null;
    guest_name = '';
    room_name  = '';
    hotel_name_db = null;
    for
      select SSP.ITEM
      from   SYSTEM_STRING_PARSER(:external_data, f_crlf()) SSP
      into   :item
    do begin
      if (position(:target_name||'=', :item) = 1) then begin
        GUEST_NAME = substring(:item from char_length(:target_name||'=')+1);
      end
      if (position(:target_room||'=', :item) = 1) then begin
        ROOM_NAME = substring(:item from char_length(:target_room||'=')+1);
      end
      if (position(:target_board||'=', :item) = 1) then begin
        BOARD_NAME = substring(:item from char_length(:target_board||'=')+1);
      end
      if (position(:rcpt_account||'=', :item) = 1) then begin
        ID_ACCOUNT = substring(:item from char_length(:rcpt_account||'=')+1);
      end
      if (position(:database_name||'=', :item) > 0) then begin
        hotel_name_db = substring(:item from char_length(:database_name||'=')+1);
      end
    end

    if (:EXTERNAL_IFC = 'plugin.pos2evo')  then begin
      select S.ITEM
      from   SYSTEM_STRING_PARSER(:hotel_name_db, '|') S
      where  S.INTURN = 3
      into   :HOTEL_NAME;
    end else begin
      HOTEL_NAME = :hotel_name_db;
    end
  end
end
^

/* Alter (BILL_PAYMENT_EXTERNAL_DATA) */
ALTER PROCEDURE BILL_PAYMENT_EXTERNAL_DATA(ID_SMETKA_PAYMENT INTEGER,
EXTERNAL_IFC VARCHAR(200),
EXTERNAL_DATA VARCHAR(2000),
INTERNAL_DATA VARCHAR(2000))
 RETURNS(ERR_CODE INTEGER,
ERR_TEXT VARCHAR(500))
 AS
declare variable id_end_work integer;
  declare variable ext_interface varchar(100);

  declare variable id_smetka_head integer;
  declare variable new_id_smetka_front integer;
  declare variable id_account integer;
  declare variable guest_name varchar(100);
  declare variable room_name varchar(100);
  declare variable board_name varchar(100);
  declare variable hotel_name varchar(100);
  declare variable smetka_date date;
  declare variable pay_amount numeric(15,4);
begin
  err_code = 0;
  err_text = '';

  insert into SMETKA_PAYMENT_EXTERNAL (ID_SMETKA_PAYMENT, EXTERNAL_DATA, EXTERNAL_IFC, INTERNAL_DATA)
  values (:id_smetka_payment, :external_data, :external_ifc, :internal_data);

  select SP.ID_END_WORK, sh.ID_SMETKA_HEAD, PT.EXT_INTERFACE, SH.SMETKA_DATE
  from   SMETKA_PAYMENT SP
         join SMETKA_HEAD SH on SH.ID_SMETKA_HEAD = SP.ID_SMETKA_HEAD
         join NOM_PAYMENT_TYPE PT on PT.ID_PAYMENT_TYPE = SP.ID_PAYMENT_TYPE
  where  SP.ID_SMETKA_PAYMENT = :id_smetka_payment
  into   :id_end_work, :id_smetka_head, :ext_interface, :smetka_date;

  pay_amount = null;
  select sum(tl.TRANSFER_AMOUNT)
  from   EAI_P2F_TRANSFER_LOG tl
  where  tl.ID_SMETKA_HEAD = :id_smetka_head
         and tl.ID_SMETKA_PAYMENT is null
  into   :pay_amount;

  execute procedure BILL_PAYMENT_EXT_DATA_PARSE (:EXTERNAL_IFC, :EXTERNAL_DATA)
  returning_values (:ID_ACCOUNT, :GUEST_NAME, :ROOM_NAME, :HOTEL_NAME, :board_name);

  new_id_smetka_front = gen_id(gen_smetka_front_id, 1);
  insert into SMETKA_FRONT (ID_SMETKA_FRONT, ID_END_WORK,
    ID_ACCOUNT, GUEST_NAME, ROOM_NAME, HOTEL_NAME, SMETKA_DATE, GUEST_BOARD, TRANSFER_AMOUNT)
  values (:new_id_smetka_front, :id_end_work,
    :id_account, :guest_name, :room_name, :hotel_name, :smetka_date, :board_name, :pay_amount);

  update SMETKA_HEAD h
  set    h.ID_SMETKA_FRONT = :new_id_smetka_front
  where  h.ID_SMETKA_HEAD = :id_smetka_head;

  if (:pay_amount is not null) then begin
    update EAI_P2F_TRANSFER_LOG tl
    set    tl.ID_SMETKA_PAYMENT = :ID_SMETKA_PAYMENT
    where  tl.ID_SMETKA_HEAD = :id_smetka_head
           and tl.ID_SMETKA_PAYMENT is null;

    update SMETKA_PAYMENT SP
    set    SP.ID_SMETKA_FRONT = :new_id_smetka_front
    where  SP.ID_SMETKA_PAYMENT = :id_smetka_payment;
  end

end
^

/* Alter (EAI_FRONT) */
ALTER PROCEDURE EAI_FRONT(ID_END_WORK INTEGER,
HOTEL VARCHAR(50),
ID_SMETKA_HEAD INTEGER,
ID_EAI_MAP INTEGER,
EXTERNAL_IFC VARCHAR(100),
GUEST_BOARD VARCHAR(30) = null)
 RETURNS(TAG_VALUE VARCHAR(30),
TAG_NAME VARCHAR(50),
SUM_VALUE NUMERIC(15,4),
ALL_INCLUSIVE_PAYMENT_TYPE INTEGER,
UNIQUE_SALES_NUMBER VARCHAR(30))
 AS
declare variable id_map_tag integer;
  declare variable id_object integer;
  declare variable id_eai_map_allinclusive integer;
  declare variable id_stock_group integer;
  declare variable has_payable smallint;
  declare variable transfer_value numeric(15,4);
  declare variable group_name varchar(30);
  declare variable smetka_line_ids varchar(5000);
  declare variable id_log integer;
begin
  /*  Description:
  ||
  */

  select SH.ID_OBJECT, sh.UNIQUE_SALES_NUMBER
  from   SMETKA_HEAD SH
  where  SH.ID_SMETKA_HEAD = :id_smetka_head
  into   :id_object, :UNIQUE_SALES_NUMBER;

  if ((:UNIQUE_SALES_NUMBER is null) and (:ID_SMETKA_HEAD is not null)) then begin
    -- preliminary UNIQUE_SALES_NUMBER sets
    execute procedure BILL_UNQ_SALES_NUMBER_SET(:ID_SMETKA_HEAD, null)
    returning_values (:UNIQUE_SALES_NUMBER);
  end

  all_inclusive_payment_type = null;
  select first 1 A.ID_PAYMENT_TYPE, A.ID_EAI_MAP_ALLINCLUSIVE
  from   EAI_MAP_ALLINCLUSIVE A
  where  A.HOTEL = :hotel
         and A.ID_OBJECT = :id_object
         and ((coalesce(a.COMPATIBLE_GUEST_BOARD, '') = '')
              or (a.COMPATIBLE_GUEST_BOARD = coalesce(:GUEST_BOARD, '')))
  order by nullif(a.COMPATIBLE_GUEST_BOARD, '') asc nulls last
  into   :ALL_INCLUSIVE_PAYMENT_TYPE, :id_eai_map_allinclusive;

  has_payable = 0;
  for
    select IFS.ID_MAP_TAG, IFS.ID_STOCK_GROUP, sum(IFS.SUM_VALUE), list(ifs.smetka_line_ids)
    from   EAI_FRONT_SUB(:id_end_work, :id_smetka_head, :id_eai_map, :external_ifc, :id_eai_map_allinclusive) IFS
    group by 1, 2
    into   :id_map_tag, :id_stock_group, :transfer_value, :smetka_line_ids
  do begin
    select MT.TAG_NAME, MT.TAG_VALUE
    from   EAI_MAP_TAG MT
    where  MT.ID_MAP_TAG = :id_map_tag
    into   :tag_name, :tag_value;

    select sg.STOCK_GROUP
    from   STOCK_GROUP SG
    where  sg.ID_STOCK_GROUP = :id_stock_group
    into   :group_name;

    insert into EAI_P2F_TRANSFER_LOG (ID_SMETKA_HEAD, ID_SMETKA_PAYMENT, ID_END_WORK, EXTERNAL_IFC,
           TAG_NAME, TAG_VALUE, GROUP_NAME, TRANSFER_AMOUNT)
    values (:ID_SMETKA_HEAD, null, :ID_END_WORK, :EXTERNAL_IFC,
           :TAG_NAME, :TAG_VALUE, :group_name, :transfer_value)
    returning ID_EAI_P2F_TRANSFER_LOG into :id_log;

    insert into EAI_P2F_TRANSFER_LOG_LINES(ID_EAI_P2F_TRANSFER_LOG, ID_SMETKA_LINE)
    select :id_log, p.ITEM
    from   SYSTEM_STRING_PARSER(:smetka_line_ids, ',') p;

    has_payable = 1;
  end

  tag_value = null;
  tag_name = null;
  sum_value = null;
  if (:has_payable = 1) then begin
    for
      select tl.TAG_NAME, tl.TAG_VALUE, sum(tl.TRANSFER_AMOUNT)
      from   EAI_P2F_TRANSFER_LOG tl
      where  tl.EXTERNAL_IFC is not distinct from :EXTERNAL_IFC
             and ( (tl.ID_SMETKA_HEAD = nullif(:ID_SMETKA_HEAD, -1) and tl.ID_SMETKA_PAYMENT is null)
                  or (tl.ID_END_WORK = nullif(:ID_END_WORK, -1))
                 )
      group by 1, 2
      into   :TAG_NAME, :TAG_VALUE, :SUM_VALUE
    do begin
      suspend;
    end
  end else begin
    suspend;
  end
/*  end*/
end
^

/* Alter (FISCALIZE_LINE$FISC) */
ALTER PROCEDURE FISCALIZE_LINE$FISC(ID_DOC_HEAD INTEGER)
 RETURNS(FISC_PRICE NUMERIC(15,4),
FISC_QTY NUMERIC(15,4),
FISC_TEXT VARCHAR(100),
FISC_GRP VARCHAR(10),
TRANSACTION_VALUE NUMERIC(15,4))
 AS
declare variable fld_sep varchar(10);
  declare variable line_sep varchar(10);
  declare variable line varchar(255);
  declare variable end_pos integer;
  declare variable lines_data blob sub_type 1 segment size 100;
  declare variable i integer;
  declare variable status integer;
  declare variable fiscal_device_id varchar(10);
  declare variable storno_fiscal_device_id varchar(10);
begin
  select f.SMETKA_LINES, h.status, f.FISCAL_DEVICE_ID, f.STORNO_FISCAL_DEVICE_ID
  from   SMETKA_FISCAL_DATA f              
         join SMETKA_HEAD h on h.ID_SMETKA_HEAD = f.ID_SMETKA_HEAD
  where  f.ID_SMETKA_HEAD = :ID_DOC_HEAD
  into   :lines_data, :status, :fiscal_device_id, :storno_fiscal_device_id;
  -- Status && fiscal data compatibility check
  if (:status = 2 and :fiscal_device_id is not null) then
    exception FISCALIZE_EXCEPTION '-315';

  if (:status = -3 and :storno_fiscal_device_id is not null) then
    exception FISCALIZE_EXCEPTION '-314';

  i = 0;
  fld_sep = f_character(28);
  line_sep = f_character(29);
  while (trim(:lines_data) <> '') do begin
    end_pos = position(:line_sep, :lines_data);
    line = substring(:lines_data from 1 for end_pos);
    lines_data = substring(:lines_data from end_pos + 1);
    if (:line <> '') then begin
      FISC_PRICE = substring(:line from 1 for position(:fld_sep, :line)-1);
      line = substring(:line from position(:fld_sep, :line)+1);
      FISC_QTY = substring(:line from 1 for position(:fld_sep, :line)-1);
      line = substring(:line from position(:fld_sep, :line)+1);
      FISC_TEXT = substring(:line from 1 for position(:fld_sep, :line)-1);
      line = substring(:line from position(:fld_sep, :line)+1);
      FISC_GRP = substring(:line from 1 for position(:fld_sep, :line)-1);
      line = substring(:line from position(:fld_sep, :line)+1);
      TRANSACTION_VALUE = substring(:line from 1 for position(:line_sep, :line)-1);
      suspend;
    end
    i= :i + 1;
    if (:i = 4096) then exit; -- Too many cycles, recursion prevents.
  end
end
^

/* Alter (FISCALIZE_PAYMENT$FISC) */
ALTER PROCEDURE FISCALIZE_PAYMENT$FISC(ID_DOC_HEAD INTEGER)
 RETURNS(FISC_SUM NUMERIC(15,4),
FISC_TEXT VARCHAR(100),
FISC_GRP VARCHAR(10))
 AS
declare variable fld_sep varchar(10);
  declare variable line_sep varchar(10);
  declare variable line varchar(255);
  declare variable end_pos integer;
  declare variable payments_data blob sub_type 1 segment size 100;
  declare variable i integer;
  declare variable status integer;
  declare variable fiscal_device_id varchar(10);
  declare variable storno_fiscal_device_id varchar(10);
begin
  select f.SMETKA_PAYMENTS, h.STATUS, f.FISCAL_DEVICE_ID, f.STORNO_FISCAL_DEVICE_ID
  from   SMETKA_FISCAL_DATA f
         join SMETKA_HEAD h on h.ID_SMETKA_HEAD = f.ID_SMETKA_HEAD
  where  f.ID_SMETKA_HEAD = :ID_DOC_HEAD
  into   :payments_data, :status, :fiscal_device_id, :storno_fiscal_device_id;
  -- Status && fiscal data compatibility check
  if (:status = 2 and :fiscal_device_id is not null) then
    exception FISCALIZE_EXCEPTION '-315';

  if (:status = -3 and :storno_fiscal_device_id is not null) then
    exception FISCALIZE_EXCEPTION '-314';

  i = 0;
  fld_sep = f_character(28);
  line_sep = f_character(29);
  while (trim(:payments_data) <> '') do begin
    end_pos = position(:line_sep, :payments_data);
    line = substring(:payments_data from 1 for :end_pos);
    payments_data = substring(:payments_data from :end_pos + 1);
    if (:line <> '') then begin
      FISC_SUM = substring(:line from 1 for position(:fld_sep, :line)-1);
      line = substring(:line from position(:fld_sep, :line)+1);
      FISC_TEXT = substring(:line from 1 for position(:fld_sep, :line)-1);
      line = substring(:line from position(:fld_sep, :line)+1);
      FISC_GRP = substring(:line from 1 for position(:line_sep, :line)-1);
      suspend;
    end
    i= :i + 1;
    if (:i = 1000) then exit; -- Too many cycles, recursion prevents.
  end
end
^

ALTER PROCEDURE FISCALIZE_PREPARE_LINES$INT(ID_DOC_HEAD INTEGER)
 RETURNS(FISC_PRICE NUMERIC(15,4),
FISC_QTY NUMERIC(15,4),
FISC_TEXT VARCHAR(30),
FISC_GRP VARCHAR(10))
 AS
declare variable fisc_status integer;
  declare variable round_to numeric(15,4);
begin
  /*  Description:
  ||   DETAIL_LEVEL = {1 group,  2 detail}
  function fiscalize(const text, taxGroup: string; qty, price, correction: Double): integer;
  function payment(const text, payType: string; summ: Double): integer;
  */
  fisc_price = null;
  fisc_text = null;
  fisc_grp = null;
  round_to = 0.01;

  select max(SP.FISC_STATUS)
  from   SMETKA_PAYMENT SP
         join SMETKA_HEAD SH on SH.ID_SMETKA_HEAD = Sp.ID_SMETKA_HEAD
         join NOM_PAYMENT_TYPE PT on PT.ID_PAYMENT_TYPE = SP.ID_PAYMENT_TYPE
  where  SH.ID_SMETKA_HEAD = :id_doc_head
         and SP.ID_PAYMENT_VOID is null
         and SH.STATUS = 2
  into   :fisc_status;

  if (:fisc_status <> 1) then exit;

  for
    select bl.VAT_CODE, bl.stock_name, sp.SINGLE_PRICE, sp.QTY
    from   (select VG.VAT_CODE, S.STOCK_NAME, roundto(sum(SL.NETO_SUM), :round_to) as amount, sum(SL.QTY) as qty
            from   SMETKA_HEAD SH
                   join BILL_NETO SL on SL.ID_SMETKA_HEAD = SH.ID_SMETKA_HEAD
                   left outer join STOCK S on S.ID_STOCK = SL.ID_STOCK
                   left outer join NOM_VAT_GROUP VG on VG.ID_VAT = S.ID_VAT_GROUP
            where  SL.ID_SMETKA_HEAD = :id_doc_head
                   and SH.STATUS = 2
                   and SL.STATUS > 0
            group by 1, 2, SL.NETO_SUM) bl
           left outer join AMOUNT_APPORTION_TO_PRICE$FISC(bl.amount, bl.qty, :round_to) sp on 1=1
    into   :fisc_grp, :fisc_text, :FISC_PRICE, :FISC_QTY
  do begin
    if (:FISC_PRICE <> 0) then suspend;
  end

  fisc_price = null;
  select SH.SURCHARGE_SUM, D.DISCOUNT_NAME, 'SRV', 1
  from   SMETKA_HEAD SH
         join NOM_DISCOUNT D on D.ID_DISCOUNT = SH.ID_SURCHARGE
  where  SH.ID_SMETKA_HEAD = :id_doc_head
  into   :fisc_price, :fisc_text, :fisc_grp, :FISC_QTY;
  if (:FISC_PRICE <> 0) then suspend;
end
^

/* Alter (FISCALIZE_PREPARE$FISC) */
ALTER PROCEDURE FISCALIZE_PREPARE$FISC(ID_DOC_HEAD INTEGER)
 RETURNS(UNIQUE_SALES_NUMBER VARCHAR(30),
FISCALIZATION_DETAILS VARCHAR(255),
VOID_REASON VARCHAR(30),
IS_VOID TYPE OF TLOGICAL,
DOC_DESCRIPTION TYPE OF NAME_100)
 AS
declare variable fld_sep varchar(10);
  declare variable line_sep varchar(10);
  declare variable line varchar(255);
  declare variable lines_data blob sub_type 1 segment size 80;
  declare variable payments_data blob sub_type 1 segment size 80;
  declare variable bill_status integer;
  declare variable min_fisc_status integer;
  declare variable max_fisc_status integer;
begin
  fld_sep = f_character(28);
  line_sep = f_character(29);
  lines_data = '';
  FISCALIZATION_DETAILS = null;  -- Fiscalization API compatibility
  VOID_REASON = null;
  IS_VOID = 0;
  DOC_DESCRIPTION = null;

  select h.UNIQUE_SALES_NUMBER, h.STATUS
  from   SMETKA_HEAD h
  where  h.ID_SMETKA_HEAD = :ID_DOC_HEAD
  into   :UNIQUE_SALES_NUMBER, :bill_status;

  if (:bill_status = -4) then exit;  -- void open bill
  if (:bill_status <> 2) then exception FISCALIZE_EXCEPTION '-312';

  select max(SP.FISC_STATUS), min(sp.FISC_STATUS)
  from   SMETKA_PAYMENT SP
  where  SP.ID_SMETKA_HEAD = :id_doc_head
         and SP.ID_PAYMENT_VOID is null
  into   :max_fisc_status, :min_fisc_status;

  if (:min_fisc_status <> :max_fisc_status) then exception FISCALIZE_EXCEPTION '-307';
  else if (:max_fisc_status = 0) then exit;  -- Nothing to fiscalize
  else if (:max_fisc_status = 2) then exit;  -- Payments are marked as fiscalized already
  -- !!! Only fisc_status=1 allows to continue with data preparation !!!


  for
    select coalesce(l.FISC_PRICE, 0) || :fld_sep
           || coalesce(l.FISC_QTY, 0) || :fld_sep
           || coalesce(l.FISC_TEXT, '') || :fld_sep
           || coalesce(l.FISC_GRP, '') || :fld_sep
           || '0' || :line_sep  -- removed :TRANSACTION_VALUE
    from   FISCALIZE_PREPARE_LINES$INT (:ID_DOC_HEAD) l
    into   :line
  do begin
    lines_data = :lines_data || :line;
  end

  payments_data = '';
  for
    select coalesce(sum(SP.PAYMENT_VALUE), 0) || :fld_sep
           || coalesce(PT.PAYMENT_NAME, '') || :fld_sep
           || coalesce(PT.FISCAL_ID, '') || :line_sep
    from   SMETKA_PAYMENT SP
           join SMETKA_HEAD SH on SH.ID_SMETKA_HEAD = Sp.ID_SMETKA_HEAD
           join NOM_PAYMENT_TYPE PT on PT.ID_PAYMENT_TYPE = SP.ID_PAYMENT_TYPE
    where  SH.ID_SMETKA_HEAD = :id_doc_head
           and SP.ID_PAYMENT_VOID is null
           and SH.STATUS = 2
    group by PT.PAYMENT_NAME, PT.FISCAL_ID
    having sum(SP.PAYMENT_VALUE) > 0
    into   :line
  do begin
    payments_data = :payments_data || :line;
  end

  insert into SMETKA_FISCAL_DATA(ID_SMETKA_HEAD, UNIQUE_SALES_NUMBER, SMETKA_LINES, SMETKA_PAYMENTS)
  values (:ID_DOC_HEAD, :UNIQUE_SALES_NUMBER, :lines_data, :payments_data);
end
^

/* Alter (FISCALIZE_VOID_PREPARE$FISC) */
ALTER PROCEDURE FISCALIZE_VOID_PREPARE$FISC(ID_DOC_HEAD INTEGER)
 RETURNS(UNIQUE_SALES_NUMBER VARCHAR(30),
FISCALIZATION_DETAILS VARCHAR(255),
VOID_REASON VARCHAR(30),
DOC_DESCRIPTION TYPE OF NAME_100)
 AS
declare variable status integer;
  declare variable max_fisc_status integer;
  declare variable storno_fiscal_device_id varchar(10);
begin
  DOC_DESCRIPTION = null;

  select h.STATUS
  from   SMETKA_HEAD h
  where  h.ID_SMETKA_HEAD = :ID_DOC_HEAD
  into   :status;

  if (:status = -4) then exit;  -- void open bill
  if (:status <> -3) then exception FISCALIZE_EXCEPTION '-312';

  select max(sp.FISC_STATUS)
  from   SMETKA_PAYMENT SP
  where  SP.ID_SMETKA_HEAD = :id_doc_head
         and SP.ID_PAYMENT_VOID is not null
  into   :max_fisc_status;

  if (:max_fisc_status < 2) then exit;
  -- !!! Only fisc_status=2 allows to continue with VOID preparation !!!

  if (not exists (select f.ID_SMETKA_HEAD from SMETKA_FISCAL_DATA f where f.ID_SMETKA_HEAD = :ID_DOC_HEAD)) then
    exception FISCALIZE_EXCEPTION '-313';

  select f.UNIQUE_SALES_NUMBER, f.FISCALIZATION_DETAILS, r.VOID_REASON,
         f.STORNO_FISCAL_DEVICE_ID
  from   SMETKA_FISCAL_DATA f
         left outer join SMETKA_MANAGE m on m.ID_SMETKA_HEAD = f.ID_SMETKA_HEAD
                                            and m.MANAGE_TYPE = 'TOTAL_VOID'
         left outer join NOM_VOID_REASON r on r.ID_VOID_REASON = m.ID_REASON
  where  f.ID_SMETKA_HEAD = :ID_DOC_HEAD
  into   :UNIQUE_SALES_NUMBER, :FISCALIZATION_DETAILS, :VOID_REASON,
         :storno_fiscal_device_id;

  if (:storno_fiscal_device_id is not null) then exception FISCALIZE_EXCEPTION '-314';
end
^

/* Alter (MANAGE_PRINT_COPY) */
ALTER PROCEDURE MANAGE_PRINT_COPY(ID_CLIENT_POS INTEGER,
ID_DATA INTEGER,
DATA_TYPE VARCHAR(10))
 RETURNS(ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255),
REPORT_NAME VARCHAR(50),
ID_PRINTER INTEGER,
REPORT_DATA VARCHAR(500))
 AS
declare variable id_invent integer;
  declare variable report_category varchar(50);
  declare variable report_data_sub varchar(500);
  declare variable id_smetka_head integer;
  declare variable smetka_status integer;
  declare variable bon_status integer;
  declare variable right_required varchar(50);
  declare variable preff varchar(50);
  declare variable print_unique_ids integer;
  declare variable id_smetka_trans integer;
  declare variable id_print varchar(30);
begin
  /*  Description:
  ||
  */
  if (:data_type not in ('BON', 'ORDER')) then begin
    err_code = -1;
    err_text = 'operation unknown !';
    suspend;
    exit;
  end

  if (:data_type = 'ORDER') then begin
    id_smetka_head = :id_data;
    select SH.STATUS
    from   SMETKA_HEAD SH
    where  SH.ID_SMETKA_HEAD = :id_smetka_head
    into   :smetka_status;
    if (:smetka_status is null) then begin
      err_code = -158;
      suspend;
      exit;
    end
    if (:smetka_status = 1) then begin
      err_code = -320;
      suspend;
      exit;
    end
    if (exists (select sp.ID_SMETKA_PAYMENT
                from   SMETKA_PAYMENT sp
                where  sp.ID_SMETKA_HEAD = :id_smetka_head
                       and sp.STATUS = 1
                       and sp.FISC_STATUS > 0)) then
    begin
      err_code = -319;
      suspend;
      exit;
    end
    right_required = 'COPY_BILL_TOTAL';
  end else begin
    right_required = 'COPY_BON';
  end

  execute procedure SYSTEM_CHECK_RIGHT(:id_client_pos, :right_required)
  returning_values  :err_code, :err_text;
  if (:err_code < 0) then begin suspend; exit; end

  if (:data_type = 'BON') then begin
    select SBI.ID_INVENT, SBI.ID_SMETKA_HEAD, SBI.STATUS
    from   SMETKA_BON_INVENT SBI
    where  SBI.ID_SMETKA_INVENT = :id_data
    into   :id_invent, :id_smetka_head, :bon_status;

    report_category = iif(:bon_status = 1, 'BON_INVENT', 'BON_VOID');

    report_data = 'ID_BON='||:id_data||f_CRLF()||'PREFF=COPY';
    report_data_sub = :report_data;

    for
      select SGP.ERR_CODE, SGP.ERR_TEXT, SGP.REPORT_NAME, SGP.ID_PRINTER, SGP.REPORT_DATA
      from   SYSTEM_GET_PRINTERS(:id_client_pos, :id_invent, :report_category, 0, :report_data_sub) SGP
      into   :err_code, :err_text, :report_name, :id_printer, :report_data
    do begin
      suspend;
      if (:err_code < 0) then exit;
    end

  end else if (:data_type = 'ORDER') then begin
    select max(SP.ID_SMETKA_TRANS)
    from   SMETKA_PAYMENT SP
    where  SP.ID_SMETKA_HEAD = :id_smetka_head
    into   :id_smetka_trans;

    if (:id_smetka_trans is null) then id_smetka_trans = gen_id(gen_smetka_trans_id, 1);

    execute procedure SYSTEM_GET_VALUE(:id_client_pos, 'PRINT_UNIQUE_IDS')
    returning_values  :print_unique_ids;
    if (:print_unique_ids = 1) then id_print = :id_smetka_trans;
    else id_print = '';

    if (:smetka_status > 0) then begin
      if (:smetka_status = 1) then begin
        report_category = 'SUBTOTAL';
        preff = 'PREFF=SUBTOTAL';
      end else begin
        report_category = 'TOTAL';
        preff = 'PREFF=COPY';
      end
    end else begin
      report_category = 'TOTAL_VOID';
      preff = 'PREFF=COPY';
    end
    report_data = 'ID_SMETKA='||:id_smetka_head||f_CRLF()||'ID_ORDER='||:id_smetka_trans||f_CRLF()||'ID_PRINT='||:id_print||f_CRLF()||:preff;
    report_data_sub = :report_data;
    for
      select SGP.ERR_CODE, SGP.ERR_TEXT, SGP.REPORT_NAME, SGP.ID_PRINTER, SGP.REPORT_DATA
      from   SYSTEM_GET_PRINTERS(:id_client_pos, null, :report_category, 0, :report_data_sub) SGP
      into   :err_code, :err_text, :report_name, :id_printer, :report_data
    do begin
      suspend;
      if (:err_code < 0) then exit;
    end
  end
end
^

/* Restore proc. body: EAI_EVO_TRANSFER$INT */
ALTER PROCEDURE EAI_EVO_TRANSFER$INT(ID_END_WORK INTEGER,
ID_SMETKA_HEAD INTEGER,
ID_EAI_MAP INTEGER,
EXTERNAL_IFC VARCHAR(100),
ID_EAI_MAP_ALLINCLUSIVE INTEGER = null)
 RETURNS(ID_MAP_TAG INTEGER,
STOCK_QTY NUMERIC(15,4),
STOCK_VALUE NUMERIC(15,4),
SINGLE_PRICE NUMERIC(15,4),
GROUP_NAME VARCHAR(50),
SMETKA_LINE_IDS BLOB SUB_TYPE 1)
 AS
declare variable is_object integer;
  declare variable is_invent integer;
  declare variable is_stock_grp integer;
  declare variable id_object integer;
  declare variable id_invent integer;
  declare variable id_stock_group integer;
  declare variable complex_id varchar(30);
begin
  /*  Description:
  ||
  */
  select EM.IS_OBJECT, EM.IS_INVENT, EM.IS_STOCK_GRP
  from   EAI_MAP EM
  where  EM.ID_MAP = :id_eai_map
  into   :is_object, :is_invent, :is_stock_grp;

  if (:id_end_work > 0) then begin
    for
      select SH.ID_OBJECT, BN.ID_INVENT, sg.ID_STOCK_GROUP, sg.STOCK_GROUP,
             null, sum(BN.NETO_SUM + BN.SURCHARGE_SUM)
      from   SMETKA_HEAD SH
             join BILL_NETO BN on BN.ID_SMETKA_HEAD = SH.ID_SMETKA_HEAD
             join STOCK S on S.ID_STOCK = BN.ID_STOCK
             join STOCK_GROUP SG on SG.ID_STOCK_GROUP = S.ID_STOCK_GROUP
      where  SH.ID_END_WORK = :id_end_work
             and BN.STATUS > 0
             and SH.STATUS = 2
             and not exists(
               select sp.ID_SMETKA_PAYMENT
               from   SMETKA_PAYMENT SP
                      join SMETKA_PAYMENT_EXTERNAL SPE on SPE.ID_SMETKA_PAYMENT = SP.ID_SMETKA_PAYMENT
               where  SP.ID_SMETKA_HEAD = SH.ID_SMETKA_HEAD
                      and SP.STATUS > 0
                      and SPE.EXTERNAL_IFC = :external_ifc)
             and not exists(select sp.ID_SMETKA_PAYMENT
                            from   SMETKA_PAYMENT SP
                                   join EAI_P2F_SUPPRESS_TP STP on STP.ID_PAYMENT_TYPE = SP.ID_PAYMENT_TYPE
                            where  SP.ID_SMETKA_HEAD = SH.ID_SMETKA_HEAD
                                   and SP.STATUS > 0)
      group by 1, 2, 3, 4
      into   :id_object, :id_invent, :id_stock_group, :GROUP_NAME,
             :STOCK_QTY, :STOCK_VALUE
    do begin
      if (:is_object = 1) then complex_id = :id_object;
      else complex_id = 'null';
      if (:is_invent = 1) then complex_id = :complex_id||'_'||:id_invent;
      else complex_id = :complex_id||'_null';
      if (:is_stock_grp = 1) then complex_id = :complex_id||'_'||:id_stock_group;
      else complex_id = :complex_id||'_null';
      id_map_tag = null;
      select MD.ID_MAP_TAG
      from   EAI_MAP_DATA MD
      where  MD.COMPLEX_ID = :complex_id
             and MD.ID_MAP = :id_eai_map
      into   :id_map_tag;
      if (:id_map_tag is null) then
        select MD.ID_MAP_TAG
        from   EAI_MAP_DATA MD
        where  MD.COMPLEX_ID = 'null_null_null'
               and MD.ID_MAP = :id_eai_map
        into   :id_map_tag;
      suspend;
    end
  end else begin
    for
      select SH.ID_OBJECT, BN.ID_INVENT, s.ID_STOCK_GROUP, s.STOCK_NAME,
             cast((BN.NETO_SUM + BN.SURCHARGE_SUM)/bn.QTY as numeric(15,4)),
             sum(BN.QTY), sum(BN.NETO_SUM + BN.SURCHARGE_SUM), list(bn.ID_SMETKA_LINE)
      from   SMETKA_HEAD SH
             join BILL_NETO BN on BN.ID_SMETKA_HEAD = SH.ID_SMETKA_HEAD
             join STOCK S on S.ID_STOCK = BN.ID_STOCK
             left outer join EAI_MAP_ALL_INCLUSIVE_PAYABLE aip on aip.ID_EAI_MAP_ALL_INCLUSIVE = :ID_EAI_MAP_ALLINCLUSIVE
                                                                  and aip.ID_STOCK_GROUP = s.ID_STOCK_GROUP
      where  SH.ID_SMETKA_HEAD = :id_smetka_head
             and BN.STATUS > 0
             and ((cast(:ID_EAI_MAP_ALLINCLUSIVE as integer) is null) or (aip.ID_STOCK_GROUP is not null)) -- when has all inclusive - get only payable stocks
      group by 1, 2, 3, 4, 5

      into   :id_object, :id_invent, :id_stock_group, :GROUP_NAME,
             :SINGLE_PRICE, :STOCK_QTY, :STOCK_VALUE, :SMETKA_LINE_IDS
    do begin
      if (:is_object = 1) then complex_id = :id_object;
      else complex_id = 'null';
      if (:is_invent = 1) then complex_id = :complex_id||'_'||:id_invent;
      else complex_id = :complex_id||'_null';
      if (:is_stock_grp = 1) then complex_id = :complex_id||'_'||:id_stock_group;
      else complex_id = :complex_id||'_null';
      id_map_tag = null;
      select MD.ID_MAP_TAG
      from   EAI_MAP_DATA MD
      where  MD.COMPLEX_ID = :complex_id
             and MD.ID_MAP = :id_eai_map
      into   :id_map_tag;
      if (:id_map_tag is null) then
        select MD.ID_MAP_TAG
        from   EAI_MAP_DATA MD
        where  MD.COMPLEX_ID = 'null_null_null'
               and MD.ID_MAP = :id_eai_map
        into   :id_map_tag;
      suspend;
    end
  end
end
^

/* Restore proc. body: EAI_EVO_TRANSFER$P2E */
ALTER PROCEDURE EAI_EVO_TRANSFER$P2E(ID_END_WORK INTEGER,
HOTEL VARCHAR(50),
ID_SMETKA_HEAD INTEGER,
ID_EAI_MAP INTEGER,
EXTERNAL_IFC VARCHAR(100),
GUEST_BOARD VARCHAR(30) = null)
 RETURNS(TAG_VALUE VARCHAR(30),
TAG_NAME VARCHAR(50),
DESCRIPTION VARCHAR(50),
TRANSFER_QTY NUMERIC(15,4),
TRANSFER_AMOUNT NUMERIC(15,4),
ALL_INCLUSIVE_PAYMENT_TYPE INTEGER,
UNIQUE_SALES_NUMBER VARCHAR(30))
 AS
declare variable id_map_tag integer;
  declare variable id_object integer;
  declare variable id_eai_map_allinclusive integer;
  declare variable has_payable smallint;
  declare variable group_name varchar(30);
  declare variable smetka_line_ids varchar(5000);
  declare variable id_log integer;
begin
  /*  Description:
  ||
  */

  select SH.ID_OBJECT, sh.UNIQUE_SALES_NUMBER
  from   SMETKA_HEAD SH
  where  SH.ID_SMETKA_HEAD = :id_smetka_head
  into   :id_object, :UNIQUE_SALES_NUMBER;

  if ((:UNIQUE_SALES_NUMBER is null) and (:ID_SMETKA_HEAD is not null)) then begin
    -- preliminary UNIQUE_SALES_NUMBER sets
    execute procedure BILL_UNQ_SALES_NUMBER_SET(:ID_SMETKA_HEAD, null)
    returning_values (:UNIQUE_SALES_NUMBER);
  end

  all_inclusive_payment_type = null;
  select first 1 A.ID_PAYMENT_TYPE, A.ID_EAI_MAP_ALLINCLUSIVE
  from   EAI_MAP_ALLINCLUSIVE A
  where  A.HOTEL = :hotel
         and A.ID_OBJECT = :id_object
         and ((coalesce(a.COMPATIBLE_GUEST_BOARD, '') = '')
              or (a.COMPATIBLE_GUEST_BOARD = coalesce(:GUEST_BOARD, '')))
  order by nullif(a.COMPATIBLE_GUEST_BOARD, '') asc nulls last
  into   :ALL_INCLUSIVE_PAYMENT_TYPE, :id_eai_map_allinclusive;

  has_payable = 0;
  for
    select IFS.ID_MAP_TAG, IFS.GROUP_NAME,
           sum(IFS.STOCK_QTY), sum(IFS.STOCK_VALUE), list(ifs.smetka_line_ids)
    from   EAI_EVO_TRANSFER$INT(:id_end_work, :id_smetka_head, :id_eai_map, :external_ifc, :id_eai_map_allinclusive) IFS
    group by 1, 2, IFS.SINGLE_PRICE
    into   :id_map_tag, :group_name,
           :transfer_qty, :transfer_amount, :smetka_line_ids
  do begin
    select MT.TAG_NAME, MT.TAG_VALUE
    from   EAI_MAP_TAG MT
    where  MT.ID_MAP_TAG = :id_map_tag
    into   :tag_name, :tag_value;

    insert into EAI_P2F_TRANSFER_LOG (ID_SMETKA_HEAD, ID_SMETKA_PAYMENT, ID_END_WORK, EXTERNAL_IFC,
           TAG_NAME, TAG_VALUE, GROUP_NAME, TRANSFER_QTY, TRANSFER_AMOUNT)
    values (:ID_SMETKA_HEAD, null, :ID_END_WORK, :EXTERNAL_IFC,
           :TAG_NAME, :TAG_VALUE, :group_name, :transfer_qty, :transfer_amount)
    returning ID_EAI_P2F_TRANSFER_LOG into :id_log;

    insert into EAI_P2F_TRANSFER_LOG_LINES(ID_EAI_P2F_TRANSFER_LOG, ID_SMETKA_LINE)
    select :id_log, p.ITEM
    from   SYSTEM_STRING_PARSER(:smetka_line_ids, ',') p;

    has_payable = 1;
  end

  TAG_VALUE = null;
  TAG_NAME = null;
  DESCRIPTION = null;
  TRANSFER_QTY = null;
  TRANSFER_AMOUNT = null;

  if (:has_payable = 1) then begin
    if (coalesce(:ID_SMETKA_HEAD, -1) > 0) then begin
      for
        select tl.TAG_NAME, tl.TAG_VALUE, tl.GROUP_NAME, tl.TRANSFER_QTY, tl.TRANSFER_AMOUNT
        from   EAI_P2F_TRANSFER_LOG tl
        where  tl.EXTERNAL_IFC is not distinct from :EXTERNAL_IFC
               and tl.ID_SMETKA_HEAD = :ID_SMETKA_HEAD and tl.ID_SMETKA_PAYMENT is null
        into   :TAG_NAME, :TAG_VALUE, :DESCRIPTION, :TRANSFER_QTY, :TRANSFER_AMOUNT
      do begin
        suspend;
      end
    end else begin
      for
        select tl.TAG_NAME, tl.TAG_VALUE, null, sum(tl.TRANSFER_QTY), sum(tl.TRANSFER_AMOUNT)
        from   EAI_P2F_TRANSFER_LOG tl
        where  tl.EXTERNAL_IFC is not distinct from :EXTERNAL_IFC
               and tl.ID_END_WORK = :ID_END_WORK
        group by 1, 2
        into   :TAG_NAME, :TAG_VALUE, :DESCRIPTION, :TRANSFER_QTY, :TRANSFER_AMOUNT
      do begin
        suspend;
      end
    end
  end else begin
    suspend;
  end
/*  end*/
end
^

/* Altering existing trigger... */
SET TERM ; ^

DROP TRIGGER NOM_LIST_ITEM_BDEL;

SET TERM ^ ;

CREATE TRIGGER NOM_LIST_ITEM_BDEL FOR NOM_LIST_ITEM
INACTIVE BEFORE DELETE POSITION 0 
AS 
begin
  if (old.is_system = 1) then exception list_item_is_protected;
end
^

/* Alter Procedure... */
SET TERM ; ^

ALTER TABLE EAI_P2F_TRANSFER_LOG ALTER COLUMN ID_EAI_P2F_TRANSFER_LOG POSITION 1;

ALTER TABLE EAI_P2F_TRANSFER_LOG ALTER COLUMN ID_SMETKA_HEAD POSITION 2;

ALTER TABLE EAI_P2F_TRANSFER_LOG ALTER COLUMN ID_SMETKA_PAYMENT POSITION 3;

ALTER TABLE EAI_P2F_TRANSFER_LOG ALTER COLUMN ID_END_WORK POSITION 4;

ALTER TABLE EAI_P2F_TRANSFER_LOG ALTER COLUMN TAG_NAME POSITION 5;

ALTER TABLE EAI_P2F_TRANSFER_LOG ALTER COLUMN TAG_VALUE POSITION 6;

ALTER TABLE EAI_P2F_TRANSFER_LOG ALTER COLUMN GROUP_NAME POSITION 7;

ALTER TABLE EAI_P2F_TRANSFER_LOG ALTER COLUMN TRANSFER_AMOUNT POSITION 8;

ALTER TABLE EAI_P2F_TRANSFER_LOG ALTER COLUMN TRANSFER_QTY POSITION 9;

ALTER TABLE EAI_P2F_TRANSFER_LOG ALTER COLUMN TRANSFER_DT POSITION 10;

ALTER TABLE EAI_P2F_TRANSFER_LOG ALTER COLUMN EXTERNAL_IFC POSITION 11;

ALTER TABLE EAI_P2F_TRANSFER_LOG ALTER COLUMN STOCK_GROUP POSITION 12;

