/* Server version: WI-V6.3.3.18185 Firebird 2.1 
   SQLDialect: 3. ODS: 11.0. Forced writes: On. Sweep inteval: 20000.
   Page size: 4096. Cache pages: 9500 (38000 Kb). Read-only: False. */
SET SQL DIALECT 3;

DROP TRIGGER REMAKE_REF_CONSTR;
DROP TRIGGER REMAKE_INDICES;

/* Drop Index... */
RECONNECT;

/* Drop: EAI_MAP_ALLINCLUSIVE_IDX1 (TIdxData) */
DROP INDEX EAI_MAP_ALLINCLUSIVE_IDX1;


/* Create Table... */
CREATE TABLE EAI_MAP_ALL_INCLUSIVE_PAYABLE(ID_EAI_MAP_ALL_INCLUSIVE ID_TYPE NOT NULL,
ID_STOCK_GROUP ID_TYPE NOT NULL);


CREATE TABLE EAI_P2F_TRANSFER_LOG(ID_SMETKA_HEAD ID_REF,
ID_SMETKA_PAYMENT ID_REF,
ID_END_WORK ID_REF,
TAG_NAME NAME_50,
TAG_VALUE NAME_30,
STOCK_GROUP NAME_50,
TRANSFER_AMOUNT CURRENCY_LOW);



ALTER TABLE EAI_MAP_ALLINCLUSIVE ADD ID_EAI_MAP_ALLINCLUSIVE ID_TYPE;

commit work;

update EAI_MAP_ALLINCLUSIVE
set    ID_EAI_MAP_ALLINCLUSIVE = GEN_ID(GEN_EAI_MAP_ALLINCLUSIVE_ID, 1);

commit work;

ALTER TABLE EAI_MAP_ALLINCLUSIVE ADD COMPATIBLE_GUEST_BOARD NAME_30;

ALTER TABLE SMETKA_FRONT ADD GUEST_BOARD NAME_30;

ALTER TABLE SMETKA_FRONT ADD TRANSFER_AMOUNT CURRENCY_LOW;

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_SOURCE='NAME_50' WHERE RDB$FIELD_NAME='EXPORT_TAG' AND RDB$RELATION_NAME='CONTRAGENT';

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

CREATE 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
 BEGIN SUSPEND; END
^

CREATE PROCEDURE EAI_EXPORT_ALOYALTY_INCOME(ID_OBJECT INTEGER,
FROM_DATE DATE)
 RETURNS(CLIENT_UUID VARCHAR(40),
INCOME_DATE DATE,
INCOME_VALUE NUMERIC(15,4))
 AS
 BEGIN SUSPEND; END
^

CREATE PROCEDURE EAI_IMPORT_ALOYALTY_CLIENT(CLIENT_UUID VARCHAR(40),
CLIENT_NAME VARCHAR(100),
CARD_CODE VARCHAR(50),
IS_ARHIVE SMALLINT,
DATE_OF_BIRTH DATE)
 AS
 BEGIN EXIT; END
^

CREATE PROCEDURE EAI_SELECT_ALOYALTY_OBJECTS RETURNS(ID_OBJECT INTEGER,
OBJECT_NAME VARCHAR(50))
 AS
 BEGIN SUSPEND; END
^


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

CREATE INDEX ARCHIVE_HEAD_NUM_IDX ON ARCHIVE_HEAD(SMETKA_NUM);

CREATE INDEX ARCHIVE_HEAD_OBJ_IDX ON ARCHIVE_HEAD(ID_OBJECT);

CREATE INDEX ARCHIVE_LINE_IDX3 ON ARCHIVE_LINE(ID_SMETKA_BON);

CREATE INDEX ARCHIVE_LINE_IDX4 ON ARCHIVE_LINE(ID_SMETKA_TRANS);

CREATE INDEX ARCHIVE_TRANS_START_DT_IDX ON ARCHIVE_TRANS COMPUTED BY(start_date + start_time);

CREATE INDEX EAI_P2F_TRANSFER_LOG_IDX1 ON EAI_P2F_TRANSFER_LOG(ID_SMETKA_HEAD);

CREATE INDEX EAI_P2F_TRANSFER_LOG_IDX2 ON EAI_P2F_TRANSFER_LOG(ID_END_WORK);


/* Create Primary Key... */
ALTER TABLE EAI_MAP_ALL_INCLUSIVE_PAYABLE ADD CONSTRAINT PK_EAI_MAP_ALL_INCL_PAYABLE PRIMARY KEY (ID_EAI_MAP_ALL_INCLUSIVE, ID_STOCK_GROUP);

ALTER TABLE EAI_MAP_ALLINCLUSIVE ADD CONSTRAINT PK_EAI_MAP_ALLINCLUSIVE PRIMARY KEY (ID_EAI_MAP_ALLINCLUSIVE);

/* Create Unique... */
ALTER TABLE EAI_MAP_ALLINCLUSIVE ADD CONSTRAINT UNQ1_EAI_MAP_ALLINCLUSIVE UNIQUE (HOTEL, ID_OBJECT, COMPATIBLE_GUEST_BOARD);

/* Create Foreign Key... */
RECONNECT;

ALTER TABLE EAI_MAP_ALL_INCLUSIVE_PAYABLE ADD CONSTRAINT FK_EAI_MAIP_MAP_ALL_INCL FOREIGN KEY (ID_EAI_MAP_ALL_INCLUSIVE) REFERENCES EAI_MAP_ALLINCLUSIVE (ID_EAI_MAP_ALLINCLUSIVE) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE EAI_MAP_ALL_INCLUSIVE_PAYABLE ADD CONSTRAINT FK_EAI_MAIP_STOCK_GRP FOREIGN KEY (ID_STOCK_GROUP) REFERENCES STOCK_GROUP (ID_STOCK_GROUP) ON UPDATE CASCADE ON DELETE CASCADE;

/*  Empty EAI_FRONT for EAI_FRONT_SUB(param list change)  */
SET TERM ^ ;

ALTER PROCEDURE EAI_FRONT(ID_END_WORK INTEGER,
HOTEL VARCHAR(50),
ID_SMETKA_HEAD INTEGER,
ID_EAI_MAP INTEGER,
EXTERNAL_IFC VARCHAR(100))
 RETURNS(TAG_VALUE VARCHAR(30),
TAG_NAME VARCHAR(50),
SUM_VALUE NUMERIC(15,4),
ALL_INCLUSIVE_PAYMENT_TYPE INTEGER)
 AS
 BEGIN SUSPEND; END
^

/* Alter empty procedure EAI_FRONT_SUB with new param-list */
ALTER PROCEDURE EAI_FRONT_SUB(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,
SUM_VALUE NUMERIC(15,4),
ID_STOCK_GROUP INTEGER)
 AS
 BEGIN SUSPEND; END
^

/* Alter empty procedure NOM_MODIFY_CONTRAGENT with new param-list */
ALTER PROCEDURE NOM_MODIFY_CONTRAGENT(ID_CONTRAGENT INTEGER,
ID_CONTRAGENT_GROUP INTEGER,
CONTRAGENT_NAME VARCHAR(50),
D_NUM VARCHAR(30),
BULSTAT VARCHAR(30),
ID_SECURE_LEVEL INTEGER,
IS_USE SMALLINT,
EXPORT_TAG VARCHAR(50),
ID_FOOTER_TYPE INTEGER,
ID_DISCOUNT INTEGER,
IS_ONLY_FAKTURA SMALLINT,
MOL VARCHAR(30),
CITY VARCHAR(30),
ADDRESS VARCHAR(50),
PHONE VARCHAR(30),
ZIP_CODE VARCHAR(10),
POST_ADDRESS VARCHAR(50),
DESCRIPTION BLOB,
OPERATION VARCHAR(20))
 RETURNS(ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255),
NEW_ID_CONTRAGENT INTEGER)
 AS
 BEGIN SUSPEND; END
^

/* Alter Procedure... */
/* 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;

/*$$IBEC$$   if (:ext_interface in ('plugin.pos2f36', 'plugin.pos2evo'))  then begin
    TARGET_NAME = 'tname';
    TARGET_ROOM = 'troom';
    RCPT_ACCOUNT = 'postAccount';
    DATABASE_NAME = 'db';
  end else if (:ext_interface 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

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

  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;

    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_PAYMENT SP
    set    SP.ID_SMETKA_FRONT = :new_id_smetka_front
    where  SP.ID_SMETKA_PAYMENT = :id_smetka_payment;
  end

/*$$IBEC$$ 
  if (:ext_interface 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 (StrPos(:TARGET_NAME, :item) <> 0) then begin
        for
          select SSP.ITEM, SSP.INTURN
          from   SYSTEM_STRING_PARSER(:item, '=') SSP
          into   :item2, :in_turn
        do if (:in_turn = 2) then guest_name = :item2;
      end
      if (StrPos(:TARGET_ROOM, :item) <> 0) then begin
        for
          select SSP.ITEM, SSP.INTURN
          from   SYSTEM_STRING_PARSER(:item, '=') SSP
          into   :item2, :in_turn
        do if (:in_turn = 2) then room_name = :item2;
      end
      if (StrPos(:RCPT_ACCOUNT, :item) <> 0) then begin
        for
          select SSP.ITEM, SSP.INTURN
          from   SYSTEM_STRING_PARSER(:item, '=') SSP
          into   :item2, :in_turn
        do if (:in_turn = 2) then id_account = :item2;
      end
      if (StrPos(:DATABASE_NAME, :item) <> 0) then begin
        for
          select SSP.ITEM, SSP.INTURN
          from   SYSTEM_STRING_PARSER(:item, '=') SSP
          into   :item2, :in_turn
        do if (:in_turn = 2) then hotel_name_db = :item2;
      end
    end

    if (:ext_interface = '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

    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)
    values (:new_id_smetka_front, :id_end_work,
      :id_account, :guest_name, :room_name, :hotel_name, :smetka_date);
  
    update SMETKA_PAYMENT SP
    set    SP.ID_SMETKA_FRONT = :new_id_smetka_front
    where  SP.ID_SMETKA_PAYMENT = :id_smetka_payment;
  end $$IBEC$$*/
end
^

ALTER PROCEDURE EAI_FRONT_SUB(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,
SUM_VALUE NUMERIC(15,4),
ID_STOCK_GROUP INTEGER)
 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 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, S.ID_STOCK_GROUP, 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
      where  SH.ID_END_WORK = :id_end_work
             and BN.STATUS > 0
             and SH.STATUS = 2
             and not exists(
               select 1
               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)
                        or exists(
                           select *
                           from   EAI_P2F_SUPPRESS_TP STP
                           where  SP.ID_PAYMENT_TYPE = STP.ID_PAYMENT_TYPE))
             )
      group by SH.ID_OBJECT, BN.ID_INVENT, S.ID_STOCK_GROUP
      into   :id_object, :id_invent, :id_stock_group, :sum_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, 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
             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 SH.ID_OBJECT, BN.ID_INVENT, S.ID_STOCK_GROUP
      into   :id_object, :id_invent, :id_stock_group, :sum_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
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))
 RETURNS(TAG_VALUE VARCHAR(30),
TAG_NAME VARCHAR(50),
SUM_VALUE NUMERIC(15,4),
ALL_INCLUSIVE_PAYMENT_TYPE INTEGER)
 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);
begin
  /*  Description:
  ||
  */
  select SH.ID_OBJECT
  from   SMETKA_HEAD SH
  where  SH.ID_SMETKA_HEAD = :id_smetka_head
  into   :id_object;

  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;

/*  if (:all_inclusive_payment_type is not null) then begin
    tag_value = null;
    tag_name = null;
    sum_value = null;
    suspend;
  end else begin*/
  has_payable = 0;
  for
    select IFS.ID_MAP_TAG, IFS.ID_STOCK_GROUP, sum(IFS.SUM_VALUE)
    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
  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,
           TAG_NAME, TAG_VALUE, STOCK_GROUP, TRANSFER_AMOUNT)
    values (:ID_SMETKA_HEAD, null, :ID_END_WORK,
           :TAG_NAME, :TAG_VALUE, :group_name, :transfer_value);
    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.ID_SMETKA_HEAD = :ID_SMETKA_HEAD 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 PROCEDURE NOM_MODIFY_CONTRAGENT(ID_CONTRAGENT INTEGER,
ID_CONTRAGENT_GROUP INTEGER,
CONTRAGENT_NAME VARCHAR(50),
D_NUM VARCHAR(30),
BULSTAT VARCHAR(30),
ID_SECURE_LEVEL INTEGER,
IS_USE SMALLINT,
EXPORT_TAG VARCHAR(50),
ID_FOOTER_TYPE INTEGER,
ID_DISCOUNT INTEGER,
IS_ONLY_FAKTURA SMALLINT,
MOL VARCHAR(30),
CITY VARCHAR(30),
ADDRESS VARCHAR(50),
PHONE VARCHAR(30),
ZIP_CODE VARCHAR(10),
POST_ADDRESS VARCHAR(50),
DESCRIPTION BLOB,
OPERATION VARCHAR(20))
 RETURNS(ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255),
NEW_ID_CONTRAGENT INTEGER)
 AS
declare variable id_contragent_data integer;
  declare variable contragent_name_search varchar(50);
  declare variable dbg varchar(200);
begin 
  if (:operation is null) then operation = 'null';
  if (:operation not in ('add', 'mod', 'del')) then begin
    err_code = -1;
    err_text = 'Unknown operation for NOM_MODIFY_CONTRAGENT, value='||:operation;
    exit;
  end

  if (:id_contragent = 0) then begin
    err_code = -3;
    exit;
  end

  contragent_name_search = f_uppercase(CONTRAGENT_NAME);
  if (:operation = 'add') then begin
    new_id_contragent = gen_id(gen_contragent_id, 1);
    insert into CONTRAGENT (ID_CONTRAGENT, ID_CONTRAGENT_GROUP, CONTRAGENT_NAME, D_NUM, BULSTAT,
           ID_SECURE_LEVEL, IS_USE, EXPORT_TAG, ID_FOOTER_TYPE, ID_DISCOUNT, CONTRAGENT_NAME_SEARCH,
           IS_ONLY_FAKTURA, ID_PROGRAM_LEVEL, CL_JOIN_DATE)
    values (:new_id_contragent, :id_contragent_group, :contragent_name, :d_num, :bulstat,
           :id_secure_level, :is_use, :export_tag, :id_footer_type, :id_discount, :contragent_name_search,
           :is_only_faktura, null, null);
    id_contragent_data = gen_id(gen_contragent_data_id, 1);
    insert into CONTRAGENT_DATA (ID_CONTRAGENT_DATA, ID_CONTRAGENT, MOL, CITY, ADDRESS, PHONE,
           ZIP_CODE, POST_ADDRESS, DESCRIPTION)
    values (:id_contragent_data, :new_id_contragent, :mol, :city, :address, :phone,
           :zip_code, :post_address, :description);
  end else if (:operation = 'mod') then begin
    update CONTRAGENT_DATA
    set    MOL = :mol,
           CITY = :city,
           ADDRESS = :address,
           PHONE = :phone,
           ZIP_CODE = :zip_code,
           POST_ADDRESS = :post_address,
           DESCRIPTION = :description
    where  ID_CONTRAGENT = :id_contragent;
    update CONTRAGENT
    set    ID_CONTRAGENT_GROUP = :id_contragent_group,
           CONTRAGENT_NAME = :contragent_name,
           D_NUM = :d_num,
           BULSTAT = :bulstat,
           ID_SECURE_LEVEL = :id_secure_level,
           IS_USE = :is_use,
           EXPORT_TAG = :export_tag,
           ID_FOOTER_TYPE = :id_footer_type,
           ID_DISCOUNT = :id_discount,
           CONTRAGENT_NAME_SEARCH = :contragent_name_search,
           IS_ONLY_FAKTURA = :is_only_faktura
    where  ID_CONTRAGENT = :id_contragent;
    new_id_contragent = :ID_CONTRAGENT;
  end else if (:operation = 'del') then begin
    delete from CONTRAGENT C
    where  C.ID_CONTRAGENT = :id_contragent;

    delete from CONTRAGENT_DATA CD
    where  CD.ID_CONTRAGENT = :id_contragent;
  end else begin
    err_code = -1;
    err_text = 'Unknown value for operation='||:operation;
  end
end
^

/* Alter (EAI_MAP_ALLINCLUSIVE_MODIFY) */
ALTER PROCEDURE EAI_MAP_ALLINCLUSIVE_MODIFY(ID_OBJECT INTEGER,
HOTEL VARCHAR(50),
ID_PAYMENT_TYPE INTEGER,
OPERATION VARCHAR(10),
COMPATIBLE_GUEST_BOARD VARCHAR(30),
PAYABLE_STOCK_GROUP_LIST VARCHAR(1000))
 AS
declare variable id_stock_group integer;
  declare variable id_eai_map_allinclusive integer;
begin
  if (:operation = 'add') then begin
    insert into EAI_MAP_ALLINCLUSIVE (ID_OBJECT, HOTEL, ID_PAYMENT_TYPE, COMPATIBLE_GUEST_BOARD)
    values (:id_object, :hotel, :id_payment_type, :compatible_guest_board)
    returning ID_EAI_MAP_ALLINCLUSIVE into :id_eai_map_allinclusive;

    for
      select distinct trim(sp.ITEM)
      from   SYSTEM_STRING_PARSER (:PAYABLE_STOCK_GROUP_LIST, ',') sp
      into   :id_stock_group
    do begin
      insert into EAI_MAP_ALL_INCLUSIVE_PAYABLE (ID_EAI_MAP_ALL_INCLUSIVE, ID_STOCK_GROUP)
      values (:id_eai_map_allinclusive, :id_stock_group);
    end 
  
  end else if (:operation = 'del') then begin
    delete from  EAI_MAP_ALLINCLUSIVE A
    where  A.ID_OBJECT = :id_object
           and A.HOTEL = :hotel
           and a.COMPATIBLE_GUEST_BOARD = :COMPATIBLE_GUEST_BOARD;
  end

  insert into EAI_MAP_ALLINCLUSIVE_LOG (ID_OBJECT, HOTEL, ID_PAYMENT_TYPE, OPERATION)
  values (:id_object, :hotel, :id_payment_type, :operation);
end
^

/* Alter (SYSTEM_LOGON_SYSTEM) */
ALTER PROCEDURE SYSTEM_LOGON_SYSTEM(MACHINE_NAME VARCHAR(50),
EXE_VERSION VARCHAR(50),
DB_VERSION_REQ INTEGER,
CLIENT_KIND VARCHAR(10))
 RETURNS(ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255),
SERVER_DATETIME TIMESTAMP,
ADJUST_TIME VARCHAR(50),
ID_CLIENT_POS INTEGER)
 AS
declare variable exe_req_version varchar(50);
  declare variable db_version integer;
/*  declare variable expires_on date;*/
  declare variable exe_item varchar(30);
  declare variable db_req_item varchar(30);
  declare variable inturn_db integer;
  declare variable inturn_exe integer;
  declare variable enterprise_mode varchar(50);
  declare variable id_info_update integer;
begin
  err_code = 0;
  err_text = '';

  if (:client_kind = 'POS') then begin
    execute procedure SYSTEM_INFO_GET('ENTERPRISE_MODE')
    returning_values  :enterprise_mode;
    if (:enterprise_mode = 'CENTRAL') then begin
      /* In central db only ADMIN can be a client and can connect to the db */
      err_code = -161;
      exit;
    end
  end

  select max(SIU.ID_INFO_UPDATE)
  from   SYSTEM_INFO_UPDATE SIU
  into   :id_info_update;

  select SIM.APP_MODULE_VERSION
  from   SYSTEM_INFO_MODULE SIM
  where  SIM.APP_MODULE_NAME = :client_kind
         and SIM.ID_INFO_UPDATE = :id_info_update
  into   :exe_req_version;

  select SIM.APP_MODULE_VERSION
  from   SYSTEM_INFO_MODULE SIM
  where  SIM.APP_MODULE_NAME = 'DBMAIN'
         and SIM.ID_INFO_UPDATE = :id_info_update
  into   :db_version;

  for
    select SSP.ITEM, SSP.INTURN
    from   SYSTEM_STRING_PARSER(:exe_req_version, '.') SSP
    into   :db_req_item, :inturn_db
  do begin
    for
      select SSP2.ITEM, SSP2.INTURN
      from   SYSTEM_STRING_PARSER(:exe_version, '.') SSP2
      into   :exe_item, :inturn_exe
    do if (:inturn_exe = :inturn_db) then break;

    if (cast(:db_req_item as integer) < cast(:exe_item as integer)) then err_code = 1;
    if ((:err_code = 0) and (cast(:db_req_item as integer) > cast(:exe_item as integer))) then begin
      err_code = -4;
      exit;
    end
  end
  if (:err_code > 0) then err_code = 0;

  if ((:db_version is null) or (:db_version < :db_version_req)) then err_code = -5;
  else if (:client_kind not in ('POS', 'ADMIN', 'EFFECT_IFC')) then begin
    err_code = -1;
    err_text = 'client_kind has unknown value!';
  end
  if (:err_code < 0) then exit;

  select CP.ID_CLIENT_POS
  from   SYSTEM_CLIENT CP
  where  CP.MACHINE = :machine_name
         and CP.CLIENT_KIND = :client_kind
  into   :id_client_pos;

  if (:id_client_pos is null) then begin
    if (:client_kind = 'POS') then begin
      err_code = -6;
      err_text = 'MACHINE = '||:machine_name;
      exit;
    end else begin
      execute procedure SYSTEM_CLIENT_ADD(:machine_name, null, :client_kind, 0, null)
      returning_values  :err_code, :err_text, :id_client_pos;
      if (:err_code < 0) then exit;
    end
  end

  execute procedure SYSTEM_GET_VALUE(:id_client_pos, 'TIME_ADJUST_MIN')
  returning_values  :adjust_time;
  server_datetime = current_timestamp;

  RDB$SET_CONTEXT('USER_SESSION', 'ID_CLIENT_POS', :id_client_pos);
end
^

/* Restore proc. body: 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 item2 varchar(500);
  declare variable in_turn integer;
  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 (strpos(:target_name, :item) > 0) then begin
        for
          select SSP.ITEM, SSP.INTURN
          from   SYSTEM_STRING_PARSER(:item, '=') SSP
          into   :item2, :in_turn
        do if (:in_turn = 2) then GUEST_NAME = :item2;
      end
      if (strpos(:target_room, :item) > 0) then begin
        for
          select SSP.ITEM, SSP.INTURN
          from   SYSTEM_STRING_PARSER(:item, '=') SSP
          into   :item2, :in_turn
        do if (:in_turn = 2) then ROOM_NAME = :item2;
      end
      if (strpos(:target_board, :item) > 0) then begin
        for
          select SSP.ITEM, SSP.INTURN
          from   SYSTEM_STRING_PARSER(:item, '=') SSP
          into   :item2, :in_turn
        do if (:in_turn = 2) then BOARD_NAME = :item2;
      end
      if (strpos(:rcpt_account, :item) > 0) then begin
        for
          select SSP.ITEM, SSP.INTURN
          from   SYSTEM_STRING_PARSER(:item, '=') SSP
          into   :item2, :in_turn
        do if (:in_turn = 2) then ID_ACCOUNT = :item2;
      end
      if (strpos(:database_name, :item) > 0) then begin
        for
          select SSP.ITEM, SSP.INTURN
          from   SYSTEM_STRING_PARSER(:item, '=') SSP
          into   :item2, :in_turn
        do if (:in_turn = 2) then HOTEL_NAME_DB = :item2;
      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
^

/* Restore proc. body: EAI_EXPORT_ALOYALTY_INCOME */
ALTER PROCEDURE EAI_EXPORT_ALOYALTY_INCOME(ID_OBJECT INTEGER,
FROM_DATE DATE)
 RETURNS(CLIENT_UUID VARCHAR(40),
INCOME_DATE DATE,
INCOME_VALUE NUMERIC(15,4))
 AS
begin
  for
    select substring(cc.EXPORT_TAG from 1 for 40),
           c.DATE_FISCAL,
           sum(c.NETO_SUM)
    from   SUM_CONTRAGENT c
           join CONTRAGENT cc on cc.ID_CONTRAGENT = c.ID_CONTRAGENT
    where  ((:from_date is null) or (c.DATE_FISCAL > :from_date))
           and c.ID_OBJECT = :id_object
           and upper(cc.EXPORT_TAG) starting with 'AL-'
    group by 1, 2
    into   :client_uuid, :income_date, :income_value
  do begin
    suspend;
  end
end
^

/* Restore proc. body: EAI_IMPORT_ALOYALTY_CLIENT */
ALTER PROCEDURE EAI_IMPORT_ALOYALTY_CLIENT(CLIENT_UUID VARCHAR(40),
CLIENT_NAME VARCHAR(100),
CARD_CODE VARCHAR(50),
IS_ARHIVE SMALLINT,
DATE_OF_BIRTH DATE)
 AS
declare variable id_contragent integer;
  declare variable id_contragent_group integer;
  declare variable id_secure_level integer;
  declare variable err_code integer;
  declare variable err_text varchar(255);
  declare variable operation varchar(3);
begin
  select first 1 cg.ID_CONTRAGENT_GROUP
  from   CONTRAGENT_GROUP cg
  order by cg.GROUP_NAME asc
  into   :id_contragent_group;

  select first 1 sl.ID_SECURE_LEVEL
  from   RIGHT_SECURE_LEVEL sl
  where  sl.SECURE_RIGHT_CODE = 'USE_CONTRAGENT'
  order by sl.REQUIRED_LEVEL asc
  into   :id_secure_level;

  select c.ID_CONTRAGENT
  from   CONTRAGENT c
  where  c.EXPORT_TAG = :CLIENT_UUID
  into   :id_contragent;

  operation = iif(:id_contragent is not null, 'mod', 'add');

  execute procedure NOM_MODIFY_CONTRAGENT (:id_contragent, :id_contragent_group, substring(:CLIENT_NAME from 1 for 50),
            '-', '-', :id_secure_level, 1 - coalesce(:IS_ARHIVE, 0), :CLIENT_UUID, null, null, 0, null, null, null, null, null, null, null, :operation)
  returning_values (:err_code, :err_text, :id_contragent);

  if (:err_code <> 0) then exception WRONG_VALUE :err_code;

  -- reset all contragent cards
  delete from CONTRAGENT_CARD CC
    where  CC.ID_CONTRAGENT = :id_contragent;

  if (coalesce(trim(:CARD_CODE), '') <> '') then begin
    execute procedure NOM_MODIFY_CONTRAGENT_CARD (null, :id_contragent, :CARD_CODE, '', 'add')
    returning_values (:err_code, :err_text);
    if (:err_code <> 0) then exception WRONG_VALUE :err_code;
  end
end
^

/* Restore proc. body: EAI_SELECT_ALOYALTY_OBJECTS */
ALTER PROCEDURE EAI_SELECT_ALOYALTY_OBJECTS RETURNS(ID_OBJECT INTEGER,
OBJECT_NAME VARCHAR(50))
 AS
begin
  for
    select o.ID_OBJECT, o.OBJECT_NAME
    from   NOM_OBJECT o
    where  o.IS_USE = 1
    into   :ID_OBJECT, :OBJECT_NAME
  do begin
    suspend;
  end
end
^

/* Create trigger... */
CREATE TRIGGER EAI_MAP_ALLINCLUSIVE_BI FOR EAI_MAP_ALLINCLUSIVE
ACTIVE BEFORE INSERT POSITION 0 
AS
BEGIN
  IF (NEW.ID_EAI_MAP_ALLINCLUSIVE IS NULL) THEN
    NEW.ID_EAI_MAP_ALLINCLUSIVE = GEN_ID(GEN_EAI_MAP_ALLINCLUSIVE_ID,1);
END
^

alter procedure RIGHTS_REVOKE
AS
declare variable enterprise_mode varchar(50);
begin
  /* enterprise_mode = 'CENTRAL', 'REMOTE', 'STANDALONE'
  || RDB$PRIVILEGE = 'S' Select, 'R' Referance, 'X' Execute, 'I' Insert, 'U' Update, 'D' Delete
  || It is not a bad idea to forbid the user REPL to log on with SYSTEM_LOG_ON !
  || It is ABSOLUTELY necessary for POS and ADMIN to log on with dbuser different from SYSDBA
  */
  execute procedure SYSTEM_GET_VALUE(null, 'DB_TYPE')
  returning_values  :enterprise_mode;
  if (:enterprise_mode is null) then enterprise_mode = '';
  if (:enterprise_mode not in ('CENTRAL', 'REMOTE', 'STANDALONE')) then exception WRONG_VALUE;

  /* ONLY REMOTE DB NEEDS THIS PRIVILEGE, SO BY DEFAULT WE MUST CLEAR IT */
  delete from RDB$USER_PRIVILEGES UP where UP.RDB$USER = 'SYSTEM_POOL_TOTAL';


  if (:enterprise_mode = 'STANDALONE') then begin
    /* CLEAR ALL GRANTS TO USERS OTHER THAN "SYSDBA" AND "PUBLIC" */
    delete from RDB$USER_PRIVILEGES
    where  RDB$USER <> 'SYSDBA'
           and RDB$USER <> 'PUBLIC';

    /* GIVE ALL RIGHTS THAT "SYSDBA" HAS TO "PUBLIC" */
    insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
    select 'PUBLIC' , 'SYSDBA' , U.RDB$PRIVILEGE, 0 , U.RDB$RELATION_NAME, U.RDB$FIELD_NAME, U.RDB$USER_TYPE, U.RDB$OBJECT_TYPE
    from   RDB$USER_PRIVILEGES U
    where  U.RDB$USER = 'SYSDBA'
           and not exists
             (select *
              from RDB$USER_PRIVILEGES U2
              where U2.RDB$USER = 'PUBLIC'
                    and U2.RDB$PRIVILEGE = U.RDB$PRIVILEGE 
                    and U2.RDB$RELATION_NAME = U.RDB$RELATION_NAME);
  end else begin
    /* CLEAR ALL GRANTS TO USERS OTHER THAN "SYSDBA" */
    delete
    from   RDB$USER_PRIVILEGES UP
    where  UP.RDB$USER <> 'SYSDBA';

    /* GRANT 'S', 'R', 'X' TO "PUBLIC", THEESE ARE RIGHTS EVERYONE MUST HAVE
    || FOR EVERY TABLE IN THE DATABASE
    */
    insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
    select 'PUBLIC', 'SYSDBA', RDB$PRIVILEGE, 0, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE
    from   RDB$USER_PRIVILEGES U
    where  U.RDB$USER = 'SYSDBA'
           and U.RDB$PRIVILEGE in ('S', 'R', 'X');

    /* GRANT 'I', 'U', 'D' TO "REPL", THIS WAY "REPL" GAINS ALL RIGHTS TO ALL TABLES.
    || ONLY REPLICATION SCHEMA DEFINES WHICH TABLE WILL BE ACTUALLY REPLICATED.
    */
    insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
    select 'REPL', 'SYSDBA', RDB$PRIVILEGE, 0, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE
    from   RDB$USER_PRIVILEGES U
    where  U.RDB$USER = 'SYSDBA'
           and U.RDB$PRIVILEGE in ('I', 'U', 'D');

    /* GRANT 'I', 'U', 'D' TO "PUBLIC", DEPENDING ON THE ENTERPRISE MODE -> DB_TYPE
    || THESE TABLE GRANTS MUST MATCH REPLICATION SCHEMA
    */
    if (:enterprise_mode = 'REMOTE') then begin
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
      select 'PUBLIC', 'SYSDBA', RDB$PRIVILEGE, 0, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE
      from   RDB$USER_PRIVILEGES U
      where  U.RDB$USER = 'SYSDBA'
             and U.RDB$PRIVILEGE in ('I', 'U', 'D')
             and U.RDB$RELATION_NAME in (
             /* REPLICATED TABLES FROM REMOTE TO CENTRAL DATABASE */
             'SALES_HEAD', 'SALES_LINE',
             'EAI_EFFECT_SALE', 'EAI_EFFECT_LINE', 'SUM_DAY', 'SUM_PAY',
             'SUM_VOID', 'SUM_STOCK', 'SUM_DISCOUNT', 'SUM_CONTRAGENT', 'SUM_CONTRAGENT_PAY',
             'SYSTEM_END_WORK',
             /* COMMON, LOCAL ONLY TABLES */
             'DUMMY', 'KEYBOARD_LAYOUT', 'KEYBOARD_SHORTCUT', 'RIGHT_DEFINE',
             'RIGHT_GROUP', 'RIGHT_GROUP_LINE', 'RIGHT_GROUP_REPORT', 'SYSTEM_CLIENT_SECURE',
             'RIGHT_LOGON_LOG', 'RIGHT_OVERRULE', 'RIGHT_USER', 'RIGHT_USER_GROUP',
             'SETTING_PROFILE_FH', 'SETTING_PROFILE_PRINTER', 'SETTING_VALUE', 'SYSTEM_CFG',
             'SYSTEM_CLIENT', 'SETTING_PROFILE', 'NOM_FIRM_DATA', 'CONTRAGENT_POOL_CHRONOLOGY',
             /* OPERATIONAL, LOCAL ONLY TABLES */
             'SYSTEM_END_WORK', 'SYSTEM_LOCK', 'SYSTEM_NOMERATOR',
             'ARCHIVE_LINE','ARCHIVE_HEAD','ARCHIVE_BON_INVENT', 'ARCHIVE_DISCOUNT',
             'ARCHIVE_FOOTER', 'ARCHIVE_PAYMENT', 'ARCHIVE_TRANS', 'ARCHIVE_FRONT',
             'ARCHIVE_MANAGE', 'ARCHIVE_PAYMENT_EXTERNAL', 'ARCHIVE_LINE_TEXT', 'ARCHIVE_MODIFFIER',
             'SMETKA_LINE', 'SMETKA_HEAD', 'SMETKA_BON_INVENT', 'SMETKA_DISCOUNT',
             'SMETKA_FOOTER', 'SMETKA_PAYMENT', 'SMETKA_TRANS', 'SMETKA_FRONT', 'SMETKA_STOCKOUT',
             'SMETKA_MANAGE', 'SMETKA_PAYMENT_EXTERNAL', 'SMETKA_LINE_TEXT', 'SMETKA_MODIFFIER',
             'NOM_PRINTER', 'INVENT_PRINTER', 'NOM_PRINTER_TYPE', 'NOM_PRINTER_COMMAND', 'SETTING_PROFILE_PRINTER_CMD',
             'REPL_LOG', 'EAI_CONTRAGENT', 'EAI_MAP_TAG', 'EAI_MAP_DATA', 'EAI_P2F_SUPPRESS_TP', 'EAI_P2F_TRANSFER_LOG',
             'EAI_MAP', 'EAI_MAP_ALL_INCLUSIVE_PAYABLE', 'EAI_MAP_ALLINCLUSIVE', 'EAI_MAP_ALLINCLUSIVE_LOG',
             'NOM_OBJECT_SUPPRESS', 'SMETKA_PRINT_QUEUE', 'ARCHIVE_PRINT_QUEUE', 'PDA_BILL_POST', 'SMETKA_TAX', 'ARCHIVE_TAX'
             );
      /* POS2FRONT SETTINGS MUST BE REMOTELY MODIFIED */
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
      values ('PUBLIC', 'SYSDBA', 'U', 0, 'NOM_PAYMENT_TYPE', 'EXT_INTERFACE', 8, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
      values ('PUBLIC', 'SYSDBA', 'U', 0, 'NOM_PAYMENT_TYPE', 'EXT_INTERFACE_DATA', 8, 0);

      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
      select 'PUBLIC', 'SYSDBA', RDB$PRIVILEGE, 0, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE
      from   RDB$USER_PRIVILEGES U
      where  U.RDB$USER = 'SYSDBA'
             and U.RDB$PRIVILEGE in ('U')
             and U.RDB$RELATION_NAME in (
             'NOM_OBJECT'
             );
      /* BILL_FINALIZE_NUM CAN NOT BE PREPARED WITHOUT WRITE ACCES TO "CONTRAGENT_POOL_HISTORY".
      || WE MUST GRANT U,D,I,R FOR 'CONTRAGENT_POOL_HISTORY'&'CONTRAGENT_POOL' TO THE
      || STORE PROCEDURE 'SYSTEM_POOL_TOTAL'.
      */
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', 'SYSDBA', 'U', 0, 'CONTRAGENT_POOL', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', 'SYSDBA', 'D', 0, 'CONTRAGENT_POOL', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', 'SYSDBA', 'I', 0, 'CONTRAGENT_POOL', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', 'SYSDBA', 'R', 0, 'CONTRAGENT_POOL', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', 'SYSDBA', 'U', 0, 'CONTRAGENT_POOL_HISTORY', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', 'SYSDBA', 'D', 0, 'CONTRAGENT_POOL_HISTORY', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', 'SYSDBA', 'I', 0, 'CONTRAGENT_POOL_HISTORY', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', 'SYSDBA', 'R', 0, 'CONTRAGENT_POOL_HISTORY', null, 5, 0);
    end else if (:enterprise_mode = 'CENTRAL') then begin
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
      select 'PUBLIC', 'SYSDBA', RDB$PRIVILEGE, 0, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE
      from   RDB$USER_PRIVILEGES U
      where  U.RDB$USER = 'SYSDBA'
             and U.RDB$PRIVILEGE in ('I', 'U', 'D')
             and U.RDB$RELATION_NAME in (
             /* REPLICATED TABLES FROM CENTRAL TO REMOTE DATABASE */
             'MENU_HEAD', 'MENU_LINE', 'MENU_GROUP', 'MENU_LINE_PRICE',
             'MENU_PRICE_LIST', 'RIGHT_DEFINE', 'INVENT', 'CONTRAGENT',
             'CONTRAGENT_DATA', 'CONTRAGENT_GROUP', 'CONTRAGENT_PAYMENT',
             'CONTRAGENT_CARD', 'NOM_DISCOUNT', 'NOM_VAT_GROUP', 'NOM_VOID_REASON',
             'NOM_PAYMENT_TYPE', 'NOM_FIRM_DATA', 'STOCK', 'STOCK_GROUP',
             'CL_DISCOUNT_SCHEME', 'CL_DISCOUNT_SCHEME_DATA', 'CL_PROGRAM',
             'CL_PROGRAM_LEVEL', 'CONTRAGENT_POOL', 'CONTRAGENT_POOL_HISTORY',
             'NOM_POOL', 'NOM_POOL_RATE', 'NOM_TABLE_MAP', 'SCHEDULER_HEAD',
             'SCHEDULER_PERIOD', 'SCHEDULER_RULE',
             /* COMMON, LOCAL ONLY TABLES */
             'CONTRAGENT_POOL_CHRONOLOGY',
             'DUMMY', 'KEYBOARD_LAYOUT', 'KEYBOARD_SHORTCUT', 'RIGHT_DEFINE',
             'RIGHT_GROUP', 'RIGHT_GROUP_LINE', 'RIGHT_GROUP_REPORT', 'SYSTEM_CLIENT_SECURE',
             'RIGHT_LOGON_LOG', 'RIGHT_OVERRULE', 'RIGHT_USER', 'RIGHT_USER_GROUP',
              'SETTING_VALUE', 'SYSTEM_CFG',
             'SYSTEM_CLIENT', 'SETTING_PROFILE', 'SETTING_PROFILE_PRINTER_CMD',
             'RIGHT_SECURE_LEVEL', 'SYSTEM_NOMERATOR', 'REPL_LOG',
             'SETTING_PROFILE_FH', 'SETTING_PROFILE_PRINTER', 'NOM_TAXES'
--             , 'NOM_PRINTER_TYPE', 'NOM_PRINTER_COMMAND'
             );
    end
  end
end
^

create or alter procedure RIGHTS_REGRANT
AS
begin
  execute procedure RIGHTS_REVOKE;
end
^


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

ALTER TABLE EAI_MAP_ALLINCLUSIVE ALTER COLUMN ID_EAI_MAP_ALLINCLUSIVE POSITION 1;

ALTER TABLE EAI_MAP_ALLINCLUSIVE ALTER COLUMN ID_OBJECT POSITION 2;

ALTER TABLE EAI_MAP_ALLINCLUSIVE ALTER COLUMN HOTEL POSITION 3;

ALTER TABLE EAI_MAP_ALLINCLUSIVE ALTER COLUMN COMPATIBLE_GUEST_BOARD POSITION 4;

ALTER TABLE EAI_MAP_ALLINCLUSIVE ALTER COLUMN ID_PAYMENT_TYPE POSITION 5;

ALTER TABLE SMETKA_FRONT ALTER COLUMN ID_SMETKA_FRONT POSITION 1;

ALTER TABLE SMETKA_FRONT ALTER COLUMN ID_END_WORK POSITION 2;

ALTER TABLE SMETKA_FRONT ALTER COLUMN SMETKA_DATE POSITION 3;

ALTER TABLE SMETKA_FRONT ALTER COLUMN ID_ACCOUNT POSITION 4;

ALTER TABLE SMETKA_FRONT ALTER COLUMN GUEST_NAME POSITION 5;

ALTER TABLE SMETKA_FRONT ALTER COLUMN ROOM_NAME POSITION 6;

ALTER TABLE SMETKA_FRONT ALTER COLUMN HOTEL_NAME POSITION 7;

ALTER TABLE SMETKA_FRONT ALTER COLUMN GUEST_BOARD POSITION 8;

ALTER TABLE SMETKA_FRONT ALTER COLUMN TRANSFER_AMOUNT POSITION 9;

