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

/* Alter Procedure... */
/* Alter (EAI_FRONT) */
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),
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 = 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
^

/* empty dependent procedure body */
/* Clear: EAI_FRONT for: EAI_FRONT_SUB */
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
 BEGIN SUSPEND; END
^

/* Alter (EAI_FRONT_SUB) */
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 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 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
^

/* Restore proc. body: 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 = 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 Procedure... */
/* 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 = 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
^

SET TERM ; ^

