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

ERRORS IGNORE;

/*ALTER TABLE SYSTEM_CFG DROP CONSTRAINT PK_SYSTEM_CFG;*/

/* Drop Trigger... */
DROP TRIGGER SYSTEM_CFG_HASH;


/* Drop Index... */
RECONNECT;

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

RECONNECT;

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

RECONNECT;

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


RECONNECT;

/* Drop tables... */
/*DROP TABLE SYSTEM_CFG;*/

ERRORS NORMAL;

/* Create index... */
CREATE INDEX ARCHIVE_HEAD_EWOBJ_IDX ON ARCHIVE_HEAD(END_WORK_OBJECT);

CREATE INDEX ARCHIVE_HEAD_EWUSER_IDX ON ARCHIVE_HEAD(END_WORK_USER);


/* Alter Procedure... */
/* empty dependent procedure body */
/* Clear: ENDWORK_PROCESS for: ENDWORK_SUB_CLEAR */
SET TERM ^ ;

ALTER PROCEDURE ENDWORK_PROCESS(ID_CLIENT_POS INTEGER,
END_WORK_KIND VARCHAR(30),
NEXT_DATE DATE,
EW_USER_LIST VARCHAR(3000),
ID_CURRENT_EW INTEGER)
 RETURNS(ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255),
UPD_NEXT_DATE DATE,
UPD_NEXT_ID_EW INTEGER)
 AS
 BEGIN SUSPEND; END
^

/* empty dependent procedure body */
/* Clear: ENDWORK_REGENERATE for: ENDWORK_SUB_CLEAR */
ALTER PROCEDURE ENDWORK_REGENERATE(ID_DAY_EW INTEGER,
OPTIONS VARCHAR(255))
 RETURNS(ERR_CODE INTEGER)
 AS
 BEGIN SUSPEND; END
^

/* Alter (ENDWORK_SUB_CLEAR) */
ALTER PROCEDURE ENDWORK_SUB_CLEAR(ID_END_WORK INTEGER)
 RETURNS(ERR_CODE INTEGER)
 AS
declare variable cpcdb_key char(8);
begin
  err_code = 0;

  /* CLEAR POOL LOG */
  for
    select CPC.RDB$DB_KEY
    from   CONTRAGENT_POOL_CHRONOLOGY CPC
           join SMETKA_HEAD SH on SH.ID_SMETKA_HEAD = CPC.ID_SMETKA_HEAD
    where  CPC.ALREADY_IN_HISTORY = 1
           and SH.ID_END_WORK = :id_end_work

    union

    select CPC.RDB$DB_KEY
    from   CONTRAGENT_POOL_CHRONOLOGY CPC
    where  CPC.ALREADY_IN_HISTORY = 1
           and not exists(
             select SH.ID_SMETKA_HEAD
             from   SMETKA_HEAD SH
             where  SH.ID_SMETKA_HEAD = CPC.ID_SMETKA_HEAD
           )
    into   :cpcdb_key
  do begin
    delete from CONTRAGENT_POOL_CHRONOLOGY CPC
    where  CPC.RDB$DB_KEY = :cpcdb_key;
  end
   
  delete from CONTRAGENT_POOL_CHRONOLOGY CPC
  where  CPC.ID_SMETKA_HEAD is null
         and CPC.ALREADY_IN_HISTORY = 1;

  delete from SMETKA_LINE SHL
  where  SHL.ID_END_WORK = :id_end_work;

  delete from SMETKA_HEAD SHD
  where  SHD.ID_END_WORK = :id_end_work;

  delete from SMETKA_TRANS ST
  where  ST.ID_END_WORK = :id_end_work;

  delete from SMETKA_MANAGE SM
  where  SM.ID_END_WORK = :id_end_work;

  delete from SMETKA_LINE_TEXT SLT
  where  SLT.ID_END_WORK = :id_end_work;

  delete from SMETKA_MODIFFIER SM
  where  SM.ID_END_WORK = :id_end_work;

  delete from SMETKA_PRINT_QUEUE SPQ
  where  SPQ.ID_END_WORK = :id_end_work;
end
^

/* Alter (NOM_MODIFY_STOCK_EFFECT) */
ALTER PROCEDURE NOM_MODIFY_STOCK_EFFECT(STOCKS_LIST VARCHAR(4000),
DELIM_STOCK CHAR,
DELIM_PRICE CHAR)
 RETURNS(ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255))
 AS
declare variable stock_item varchar(1000);
  declare variable item varchar(1000);
  declare variable cost_price numeric(15,4);
  declare variable id_stock integer;
  declare variable inturn integer;
begin
  /*  Description:
  ||
  */

  --remove me - no longer use
  err_code = 0;
  err_text = '';

  for
    select SSP.ITEM, SSP.INTURN
    from   SYSTEM_STRING_PARSER(:stocks_list, :delim_stock) SSP
    into   :stock_item, :inturn
  do begin
    cost_price = null;
    id_stock = null;
    for
      select SSP.ITEM
      from   SYSTEM_STRING_PARSER(:stock_item, :delim_price) SSP
      into   :item
    do begin
      if (:id_stock is null) then id_stock = :item;
      else cost_price = :item;
    end

    if ((:id_stock is null) or (:cost_price is null)) then begin
      err_code = -1;
      err_text = 'MISSING VALUES IN TURN '||:inturn;
      exit;
    end

    update STOCK S
    set    S.COST_PRICE = :cost_price
    where  S.ID_STOCK = :id_stock;

    execute procedure STOCK_INVALIDATE_PRICE_LIST (:ID_STOCK);
  end

  when any do begin
    err_code = -1;
    err_text = 'SQL ERROR FOR VALUES IN TURN '||:inturn;
    exit;
  end
end
^

/* Alter (SYSTEM_LOGON_USER) */


/* Restore proc. body: ENDWORK_PROCESS */
ALTER PROCEDURE ENDWORK_PROCESS(ID_CLIENT_POS INTEGER,
END_WORK_KIND VARCHAR(30),
NEXT_DATE DATE,
EW_USER_LIST VARCHAR(3000),
ID_CURRENT_EW INTEGER)
 RETURNS(ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255),
UPD_NEXT_DATE DATE,
UPD_NEXT_ID_EW INTEGER)
 AS
declare variable dbg varchar(3000);
  declare variable id_object integer;
  declare variable id_sales integer;
  declare variable id_ew_next integer;
  declare variable user_name varchar(30);
  declare variable prev_date date;
  declare variable replication integer;
  declare variable id_current_day_ew integer;
  declare variable ew_user_name varchar(30);
  declare variable no_use integer;
  declare variable suppress_object integer;
begin
  err_code = 0;
  err_text = ''; replication = 0; /* MUST BE A SETTING */
  upd_next_id_ew = null;
  /* Description
  ||
  */
  if (:end_work_kind = 'SERVER_ALL') then end_work_kind = 'SERVER';
  if (:end_work_kind not in ('DAY', 'SERVER', 'OBJECT')) then begin
    err_code = -1;
    err_text = 'end_work_kind = '||:end_work_kind;
    exit;
  end
  /*  user expiration*/

  execute procedure SYSTEM_GET_VALUE(:id_client_pos, 'CURRENT_USER')
  returning_values  :user_name;

  execute procedure SYSTEM_GET_VALUE(:id_client_pos, 'CURRENT_EW')
  returning_values  :id_current_day_ew;

  execute procedure SYSTEM_GET_VALUE(:id_client_pos, 'CURRENT_OBJECT')
  returning_values  :id_object;

  select O.ID_EW_NEXT, O.SYSTEM_DATE
  from   NOM_OBJECT O
  where  O.ID_OBJECT = :id_object
  into   :id_ew_next, :prev_date;

  if (:end_work_kind = 'DAY') then begin
    execute procedure SYSTEM_GET_VALUE(:id_client_pos, 'SUPPRESS_OBJECT')
    returning_values  :suppress_object;
    if (:suppress_object = 1) then begin
      execute procedure NOM_MODIFY_OBJECT_SUPPRESS(:id_client_pos, :id_object, 'rise')
      returning_values  :err_code, :err_text;
      if (:err_code < 0) then exit;
    end
    insert into SYSTEM_END_WORK(ID_END_WORK, ID_OBJECT, PREV_DATE, NEXT_DATE,
      EW_USER, EW_KIND, ID_DAY_MASTER)
    values (:id_current_ew, :id_object, :prev_date, :next_date,
      :user_name, 2, :id_current_ew);

    update SYSTEM_END_WORK SEW
    set    SEW.ID_DAY_MASTER = :id_current_ew
    where  SEW.ID_OBJECT = :id_object
           and SEW.ID_DAY_MASTER is null;

    update SYSTEM_NOMERATOR N
    set    N.NUM_NEXT = 1
    where  N.NUM_CLASS = 'BILL'
           and N.NUM_OBJECT = :id_object;

    /* archive */
    execute procedure ENDWORK_SUB_ARCHIVE(:id_current_day_ew)
    returning_values  :err_code;
    if (:err_code < 0) then exit;

    if (:replication = 0) then begin
      /* summarize  */
      execute procedure ENDWORK_SUB_SUMMARY(:id_current_day_ew, :id_object, :prev_date)
      returning_values  :err_code;
      if (:err_code < 0) then exit;

      execute procedure ENDWORK_SUB_CLEAR(:id_current_day_ew)
      returning_values  :err_code;
      if (:err_code < 0) then exit;
    end
    upd_next_date = :next_date;
    execute procedure ENDWORK_GETID(:id_object, 'JUST_GEN', '')
    returning_values  :err_code, :err_text, :upd_next_id_ew, :no_use;
  end else if (:end_work_kind = 'OBJECT') then begin
    /* INTERFASE_TRADE */
    execute procedure EAI_EFFECT(:id_current_day_ew, :id_current_ew, :id_object, :prev_date)
    returning_values  :err_code, :id_sales;

    insert into SALES_HEAD (
      ID_SALES, ID_OBJECT, ID_END_WORK, SALE_DATE, IS_ACTIVE, IS_VALID, NOTE, SALE_NUM)
    select
      SH.ID_SALE_HEAD, SH.ID_OBJECT, SH.END_WORK_DAY, SH.SALE_DATE, SH.IS_ACTIVE, 1, SH.NOTE, SH.ID_SALE_HEAD
    from   EAI_EFFECT_SALE SH
    where  SH.ID_SALE_HEAD = :id_sales;
    
    insert into SALES_LINE (
      ID_SALE_LINE, ID_SALES, ID_STOCK, ID_INVENT, SALE_QTY, SALE_PRICE,
      BASE_PRICE, IS_DDS, DDS_STAVKA, DDS_GROUP, POS_CODE, IS_FISCAL, ID_MENU,
      ID_SMETKA, ID_TYPE_PAYMENT)
    select
      SL.ID_SALE_LINE, SL.ID_SALE_HEAD, SL.ID_POST_ITEM, SL.ID_INVENT_DELIVERY, SL.SALE_QTY, 0,
      0, 1, SL.VAT_VALUE, SL.VAT_NAME, SL.POS_CODE, 1, -1,
      -1, null
    from   EAI_EFFECT_LINE SL
    where  SL.ID_SALE_HEAD = :id_sales;

    update SMETKA_HEAD SH
    set    SH.END_WORK_OBJECT = :id_current_ew
    where  SH.ID_END_WORK = :id_current_day_ew
           and SH.END_WORK_OBJECT is null;

    insert into SYSTEM_END_WORK(ID_END_WORK, ID_OBJECT, PREV_DATE, NEXT_DATE,
      EW_USER, EW_KIND, ID_DAY_MASTER)
    values (:id_current_ew, :id_object, :prev_date, null,
      :user_name, 1, null);
  end else if (:end_work_kind = 'SERVER') then begin
    for
      select SSP.ITEM
      from   SYSTEM_STRING_PARSER(:ew_user_list, ',') SSP
      into   :ew_user_name
    do begin
      update SMETKA_HEAD SH
      set    SH.END_WORK_USER = :id_current_ew
      where  SH.ID_END_WORK = :id_current_day_ew
             and SH.END_WORK_USER is null
             and SH.USER_OWNER = :ew_user_name;
    end

    insert into SYSTEM_END_WORK(ID_END_WORK, ID_OBJECT, PREV_DATE, NEXT_DATE,
      EW_USER, EW_KIND, ID_DAY_MASTER)
    values (:id_current_ew, :id_object, :prev_date, null,
      :user_name, 0, null);
  end
end
^

/* Restore proc. body: ENDWORK_REGENERATE */
ALTER PROCEDURE ENDWORK_REGENERATE(ID_DAY_EW INTEGER,
OPTIONS VARCHAR(255))
 RETURNS(ERR_CODE INTEGER)
 AS
declare variable dbg varchar(2000);
  declare variable id_object integer;
  declare variable fiscal_date date;
  declare variable id_invent_ew integer;
  declare variable no_use varchar(20);
begin
  /*  Description:
  ||
  */
  select EW.ID_OBJECT, EW.PREV_DATE
  from   SYSTEM_END_WORK EW
  where  EW.ID_END_WORK = :id_day_ew
  into   :id_object, :fiscal_date;

  execute procedure ENDWORK_UNDO(:id_day_ew, 'REVERT');
  
  execute procedure ENDWORK_SUB_ARCHIVE(:id_day_ew)
  returning_values  :err_code;
  
  execute procedure ENDWORK_SUB_SUMMARY(:id_day_ew, :id_object, :fiscal_date)
  returning_values  :err_code;

  if (:options = 'with_effect_export') then begin
    for
      select EW.ID_END_WORK
      from   SYSTEM_END_WORK EW
      where  EW.ID_DAY_MASTER = :id_day_ew
             and EW.EW_KIND = 1
      into   :id_invent_ew
    do begin
      delete from EAI_EFFECT_SALE S
      where  S.END_WORK_DAY = :id_invent_ew;
      update SMETKA_HEAD SH
      set    SH.END_WORK_OBJECT = null
      where  SH.END_WORK_OBJECT = :id_invent_ew;
      execute procedure EAI_EFFECT(:id_day_ew, :id_invent_ew, :id_object, :fiscal_date)
      returning_values  :err_code, :no_use;
    end
  end
  execute procedure ENDWORK_SUB_CLEAR(:id_day_ew)
  returning_values  :err_code;
end
^

/* Alter Procedure... */
/* Alter (ENDWORK_PROCESS) */
ALTER PROCEDURE ENDWORK_PROCESS(ID_CLIENT_POS INTEGER,
END_WORK_KIND VARCHAR(30),
NEXT_DATE DATE,
EW_USER_LIST VARCHAR(3000),
ID_CURRENT_EW INTEGER)
 RETURNS(ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255),
UPD_NEXT_DATE DATE,
UPD_NEXT_ID_EW INTEGER)
 AS
declare variable dbg varchar(3000);
  declare variable id_object integer;
  declare variable id_sales integer;
  declare variable id_ew_next integer;
  declare variable user_name varchar(30);
  declare variable prev_date date;
  declare variable replication integer;
  declare variable id_current_day_ew integer;
  declare variable ew_user_name varchar(30);
  declare variable no_use integer;
  declare variable suppress_object integer;
begin
  err_code = 0;
  err_text = ''; replication = 0; /* MUST BE A SETTING */
  upd_next_id_ew = null;
  /* Description
  ||
  */
  if (:end_work_kind = 'SERVER_ALL') then end_work_kind = 'SERVER';
  if (:end_work_kind not in ('DAY', 'SERVER', 'OBJECT')) then begin
    err_code = -1;
    err_text = 'end_work_kind = '||:end_work_kind;
    exit;
  end
  /*  user expiration*/

  execute procedure SYSTEM_GET_VALUE(:id_client_pos, 'CURRENT_USER')
  returning_values  :user_name;

  execute procedure SYSTEM_GET_VALUE(:id_client_pos, 'CURRENT_EW')
  returning_values  :id_current_day_ew;

  execute procedure SYSTEM_GET_VALUE(:id_client_pos, 'CURRENT_OBJECT')
  returning_values  :id_object;

  select O.ID_EW_NEXT, O.SYSTEM_DATE
  from   NOM_OBJECT O
  where  O.ID_OBJECT = :id_object
  into   :id_ew_next, :prev_date;

  if (:end_work_kind = 'DAY') then begin
    execute procedure SYSTEM_GET_VALUE(:id_client_pos, 'SUPPRESS_OBJECT')
    returning_values  :suppress_object;
    if (:suppress_object = 1) then begin
      execute procedure NOM_MODIFY_OBJECT_SUPPRESS(:id_client_pos, :id_object, 'rise')
      returning_values  :err_code, :err_text;
      if (:err_code < 0) then exit;
    end
    insert into SYSTEM_END_WORK(ID_END_WORK, ID_OBJECT, PREV_DATE, NEXT_DATE,
      EW_USER, EW_KIND, ID_DAY_MASTER)
    values (:id_current_ew, :id_object, :prev_date, :next_date,
      :user_name, 2, :id_current_ew);

    update SYSTEM_END_WORK SEW
    set    SEW.ID_DAY_MASTER = :id_current_ew
    where  SEW.ID_OBJECT = :id_object
           and SEW.ID_DAY_MASTER is null;

    update SYSTEM_NOMERATOR N
    set    N.NUM_NEXT = 1
    where  N.NUM_CLASS = 'BILL'
           and N.NUM_OBJECT = :id_object;

    /* archive */
    execute procedure ENDWORK_SUB_ARCHIVE(:id_current_day_ew)
    returning_values  :err_code;
    if (:err_code < 0) then exit;

    if (:replication = 0) then begin
      /* summarize  */
      execute procedure ENDWORK_SUB_SUMMARY(:id_current_day_ew, :id_object, :prev_date)
      returning_values  :err_code;
      if (:err_code < 0) then exit;

      execute procedure ENDWORK_SUB_CLEAR(:id_current_day_ew)
      returning_values  :err_code;
      if (:err_code < 0) then exit;
    end
    upd_next_date = :next_date;
    execute procedure ENDWORK_GETID(:id_object, 'JUST_GEN', '')
    returning_values  :err_code, :err_text, :upd_next_id_ew, :no_use;
  end else if (:end_work_kind = 'OBJECT') then begin
    /* INTERFASE_TRADE */
    execute procedure EAI_EFFECT(:id_current_day_ew, :id_current_ew, :id_object, :prev_date)
    returning_values  :err_code, :id_sales;

    insert into SALES_HEAD (
      ID_SALES, ID_OBJECT, ID_END_WORK, SALE_DATE, IS_ACTIVE, IS_VALID, NOTE, SALE_NUM)
    select
      SH.ID_SALE_HEAD, SH.ID_OBJECT, SH.END_WORK_DAY, SH.SALE_DATE, SH.IS_ACTIVE, 1, SH.NOTE, SH.ID_SALE_HEAD
    from   EAI_EFFECT_SALE SH
    where  SH.ID_SALE_HEAD = :id_sales;
    
    insert into SALES_LINE (
      ID_SALE_LINE, ID_SALES, ID_STOCK, ID_INVENT, SALE_QTY, SALE_PRICE,
      BASE_PRICE, IS_DDS, DDS_STAVKA, DDS_GROUP, POS_CODE, IS_FISCAL, ID_MENU,
      ID_SMETKA, ID_TYPE_PAYMENT)
    select
      SL.ID_SALE_LINE, SL.ID_SALE_HEAD, SL.ID_POST_ITEM, SL.ID_INVENT_DELIVERY, SL.SALE_QTY, 0,
      0, 1, SL.VAT_VALUE, SL.VAT_NAME, SL.POS_CODE, 1, -1,
      -1, null
    from   EAI_EFFECT_LINE SL
    where  SL.ID_SALE_HEAD = :id_sales;

    update SMETKA_HEAD SH
    set    SH.END_WORK_OBJECT = :id_current_ew
    where  SH.ID_END_WORK = :id_current_day_ew
           and SH.END_WORK_OBJECT is null;

    insert into SYSTEM_END_WORK(ID_END_WORK, ID_OBJECT, PREV_DATE, NEXT_DATE,
      EW_USER, EW_KIND, ID_DAY_MASTER)
    values (:id_current_ew, :id_object, :prev_date, null,
      :user_name, 1, null);
  end else if (:end_work_kind = 'SERVER') then begin
    for
      select SSP.ITEM
      from   SYSTEM_STRING_PARSER(:ew_user_list, ',') SSP
      into   :ew_user_name
    do begin
      update SMETKA_HEAD SH
      set    SH.END_WORK_USER = :id_current_ew
      where  SH.ID_END_WORK = :id_current_day_ew
             and SH.END_WORK_USER is null
             and SH.USER_OWNER = :ew_user_name;
    end

    insert into SYSTEM_END_WORK(ID_END_WORK, ID_OBJECT, PREV_DATE, NEXT_DATE,
      EW_USER, EW_KIND, ID_DAY_MASTER)
    values (:id_current_ew, :id_object, :prev_date, null,
      :user_name, 0, null);
  end
end
^

/* Alter (ENDWORK_REGENERATE) */
ALTER PROCEDURE ENDWORK_REGENERATE(ID_DAY_EW INTEGER,
OPTIONS VARCHAR(255))
 RETURNS(ERR_CODE INTEGER)
 AS
declare variable dbg varchar(2000);
  declare variable id_object integer;
  declare variable fiscal_date date;
  declare variable id_invent_ew integer;
  declare variable no_use varchar(20);
begin
  /*  Description:
  ||
  */
  select EW.ID_OBJECT, EW.PREV_DATE
  from   SYSTEM_END_WORK EW
  where  EW.ID_END_WORK = :id_day_ew
  into   :id_object, :fiscal_date;

  execute procedure ENDWORK_UNDO(:id_day_ew, 'REVERT');
  
  execute procedure ENDWORK_SUB_ARCHIVE(:id_day_ew)
  returning_values  :err_code;
  
  execute procedure ENDWORK_SUB_SUMMARY(:id_day_ew, :id_object, :fiscal_date)
  returning_values  :err_code;

  if (:options = 'with_effect_export') then begin
    for
      select EW.ID_END_WORK
      from   SYSTEM_END_WORK EW
      where  EW.ID_DAY_MASTER = :id_day_ew
             and EW.EW_KIND = 1
      into   :id_invent_ew
    do begin
      delete from EAI_EFFECT_SALE S
      where  S.END_WORK_DAY = :id_invent_ew;
      update SMETKA_HEAD SH
      set    SH.END_WORK_OBJECT = null
      where  SH.END_WORK_OBJECT = :id_invent_ew;
      execute procedure EAI_EFFECT(:id_day_ew, :id_invent_ew, :id_object, :fiscal_date)
      returning_values  :err_code, :no_use;
    end
  end
  execute procedure ENDWORK_SUB_CLEAR(:id_day_ew)
  returning_values  :err_code;
end
^

SET TERM ; ^

