/******************************************************************************/
/***               Generated by IBExpert 25.02.2021 10:22:16                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/


CREATE GENERATOR GEN_LOG_PR_SET_WAREBASE_DATE_ID;

CREATE TABLE LOG_PR_SET_WAREBASE_DATE (
    ID        DM_ID NOT NULL /* DM_ID = BIGINT NOT NULL */,
    S         DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
    INSERTDT  DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    MSECS     DM_ID_NULL /* DM_ID_NULL = BIGINT */
);




/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE LOG_PR_SET_WAREBASE_DATE ADD CONSTRAINT PK_LOG_PR_SET_WAREBASE_DATE PRIMARY KEY (ID);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: LOG_PR_SET_WAREBASE_DATE_BI */
CREATE OR ALTER TRIGGER LOG_PR_SET_WAREBASE_DATE_BI FOR LOG_PR_SET_WAREBASE_DATE
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_log_pr_set_warebase_date_id,1);
  new.insertdt='now';
  new.msecs=datediff(millisecond, (select insertdt from LOG_PR_SET_WAREBASE_DATE where id=new.id-1), new.insertdt);
end
^

SET TERM ; ^



SET TERM ^ ;

create or alter procedure AA_PRDBG_SETWBDATE (
    ADOC_DATE type of DM_DATE,
    APROFILE_ID type of DM_ID)
returns (
    S DM_TEXT)
as
declare variable DOC_DATE type of DM_DATE;
declare variable PROFILE_ID type of DM_ID;
declare variable PART_ID type of DM_ID;
declare variable QUANT type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable SUM_NDSR DM_DOUBLE;
declare variable QUANT_PREV type of DM_DOUBLE;
declare variable SUM_NDSO_PREV type of DM_DOUBLE;
declare variable SUM_NDSR_PREV DM_DOUBLE;
declare variable DOC_DATE_PREV type of DM_DATE;
declare variable I integer;
begin
--APROFILE_ID=2;
--ADOC_DATE='01.01.2000';
  if (APROFILE_ID is null) then
    APROFILE_ID=0;
  for select id from g$profiles where (id =:APROFILE_ID or (:APROFILE_ID=0 and id>0 and status=0 and relationtype=1) ) into profile_id do
  begin
    if (ADOC_DATE is null) then -- заполняем автоматически с последних остатков
    begin
      doc_date=null;
      select max(DOC_COMMITDATE) from WAREBASE_D where g$profile_id=:profile_id into doc_date;
      if (doc_date is null) then
      begin
        select min(doc_commitdate) from doc_detail where g$profile_id=:profile_id into doc_date;
        if (doc_date is not null) then
          DOC_DATE=cast('01.'||(extract (month from DOC_DATE))||'.'||(extract (year from DOC_DATE)) as dm_date);
        else
          doc_date='01.01.2020';
      end
      else
        doc_date=dateadd(month,-1,doc_date);
    end
    else
    begin
      DOC_DATE=cast('01.'||(extract (month from ADOC_DATE))||'.'||(extract (year from ADOC_DATE)) as dm_date);
    end 
    i=0;
    while (DOC_DATE<current_date) do
    begin
      i=i+1;
--      if (i>2) then break;
      DOC_DATE_PREV=doc_date;
      doc_date=dateadd(month,1,doc_date);
      delete from WAREBASE_D where g$profile_id=:profile_id and doc_commitdate=:doc_date;
      insert into WAREBASE_D (DOC_COMMITDATE,PART_ID,QUANT,SUM_NDSO,SUM_NDSR,G$PROFILE_ID,sum_dsc)
        select :doc_date, part_id, sum(quant), sum(SUM_NDSO),sum(SUM_NDSR),:PROFILE_ID,0 from
          (select part_id, quant, sum_ndso, SUM_NDSR from WAREBASE_D wb where wb.g$profile_id=:profile_id and DOC_COMMITDATE=:doc_date_prev
--and part_id=275394
            union all
           select part_id,sum(quant),sum(sum_ndso), sum(SUM_NDSR) from doc_detail dd
            where g$profile_id=:profile_id and doc_commitdate<:doc_date and doc_commitdate>=:DOC_DATE_PREV and DD.PART_TYPE = 0
--and part_id=275394
             group by part_id
             having abs(sum(quant))>0.001/* or abs(sum(sum_ndso))>0.001*10 or abs(sum(sum_ndsr))>0.001*10*/) u
          group by part_id having abs(sum(quant))>0.001/* or abs(sum(sum_ndso))>0.001*10 or abs(sum(sum_ndsr))>0.001*10*/;
    end
  end
  s='all done';
  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON G$PROFILES TO PROCEDURE AA_PRDBG_SETWBDATE;
GRANT SELECT,INSERT,DELETE ON WAREBASE_D TO PROCEDURE AA_PRDBG_SETWBDATE;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE AA_PRDBG_SETWBDATE;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE AA_PRDBG_SETWBDATE TO SYSDBA;



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/


SET TERM ^ ;

create or alter procedure PR_SET_WAREBASE_DATE (
    BEG_DATE DM_DATE = '31.12.1899',
    END_DATE DM_DATE = current_date,
    MIN_QUANT DM_DOUBLE = 0,
    PR_ID DM_ID_NULL = 0,
    FROM_DATE DM_DATE = '01.01.2000')
as
declare variable G$PROFILE_ID DM_ID;
declare variable PART_ID DM_ID;
declare variable TEK_DATE DM_DATE;
declare variable QUANT DM_DOUBLE;
declare variable DOC_COMMITDATE DM_DATE;
declare variable LAST_DATE DM_DATE;
declare variable LAST_QUANT DM_DOUBLE;
declare variable SUM_NDSO DM_DOUBLE;
declare variable SUM_NDSR DM_DOUBLE;
declare variable NDSO DM_DOUBLE;
declare variable NDSR DM_DOUBLE;
declare variable SUM_DSC DM_DOUBLE;
declare variable DSC DM_DOUBLE;
declare variable S type of DM_TEXT;
begin
insert into LOG_PR_SET_WAREBASE_DATE (s) values ('start(BEG_DATE='||:BEG_DATE||'; END_DATE='||:END_DATE||'; MIN_QUANT='||:MIN_QUANT||'; PR_ID='||:PR_ID||'; FROM_DATE='||:FROM_DATE);
  if (BEG_DATE<>END_DATE) then   EXIT;

-- временный тест cf20210204
select s from AA_PRDBG_SETWBDATE(null, null) into s;
insert into LOG_PR_SET_WAREBASE_DATE (s) values ('end: '||:s);
exit;
  --Если расчет остатков идет на текущий месяц, и сегодня последний день месяца, то рассчитываем остатки на следующий месяц
  --чтобы с утра уже работали отчеты А.К. 2019-02-12
  if ((extract(month from current_date)<>extract(month from current_date+1)) and
      (extract(month from BEG_DATE)=extract(month from current_date)) and
      (extract(YEAR from BEG_DATE)=extract(YEAR from current_date)) )
   Then
  begin
    BEG_DATE=addmonth(cast('01.'||(extract (month from current_date))||'.'||(extract (year from current_date)) as dm_date),1);
    END_DATE=BEG_DATE;
  end


  if (BEG_DATE<>cast('01.'||(extract (month from BEG_DATE))||'.'||(extract (year from BEG_DATE)) as dm_date)) then EXIT;
  --execute procedure PR_SET_SUM_ACCOUNT_GL(BEG_DATE,END_DATE);
  BEG_DATE=cast('01.'||(extract (month from BEG_DATE))||'.'||(extract (year from BEG_DATE)) as dm_date);
  END_DATE=cast('01.'||(extract (month from END_DATE))||'.'||(extract (year from END_DATE)) as dm_date);
  for select id from g$profiles where  (id =:pr_id or (:pr_id=0 and id>0 and status=0 and relationtype=1) )
   into :g$profile_id do
  begin
    delete from warebase_g where (quant <= 0.001 and quant >= 0) --and (sum_ndso <= 0.001 and sum_ndso >= 0)
    and realquant <= 0.001 and realquant >= 0 and enddt < dateadd(-1 month to current_date) and g$profile_id=:g$profile_id;
    delete from warebase_d where g$profile_id=:g$profile_id and DOC_COMMITDATE between :beg_date and :end_date;
    for  select  part_id,sum(quant),sum(sum_ndso),sum(SUM_NDSR),0 from
            (select PART_ID, QUANT,dd.g$profile_id ,dd.sum_ndso,dd.sum_ndsr
                      from WAREBASE_D dd
                      where DOC_COMMITDATE = :FROM_DATE and
                      g$profile_id=:g$profile_id
                             union all
                            select DD.PART_ID, DD.QUANT,dd.g$profile_id,dd.sum_ndso ,dd.sum_ndsr
                      from DOC_DETAIL DD
                      where DD.DOC_COMMITDATE between :FROM_DATE and :BEG_DATE-1 and
                     -- where DD.DOC_COMMITDATE <=:BEG_DATE-1 and
                           dd.g$profile_id=:g$profile_id and DD.PART_TYPE = 0) u
                           group by u.part_id,u.g$profile_id
                            having
         --abs(sum(quant))>:min_quant or abs(sum(sum_ndso))>:min_quant*10
         abs(sum(quant))>:min_quant or abs(sum(sum_ndso))>:min_quant*10 or abs(sum(sum_ndsr))>:min_quant*10 --938632 A.K.2019-09-20

         into :part_id,:last_quant,:sum_ndso,:sum_ndsr,:sum_dsc do
      insert into warebase_d(DOC_COMMITDATE,PART_ID,QUANT,SUM_NDSO,SUM_NDSR,G$PROFILE_ID,sum_dsc) values
            (:BEG_DATE,:part_id,:last_quant,:sum_ndso,:sum_ndsr,:g$profile_id,:SUM_dsc);
  end
  --20160209 А.К.
  /*
  BEG_DATE=cast('01.'||(extract (month from BEG_DATE))||'.'||(extract (year from BEG_DATE)) as dm_date);
  END_DATE=cast('01.'||(extract (month from END_DATE))||'.'||(extract (year from END_DATE)) as dm_date);
  --for select id from g$profiles where id>0 and status=0 into :g$profile_id do
  for select id from g$profiles where status=0 into :g$profile_id do
  begin
    delete from warebase_d where g$profile_id=:g$profile_id
   -- and part_id=24568
     and DOC_COMMITDATE between :beg_date and :end_date;
    for select part_id, sum(quant), sum(sum_ndso), sum(sum_ndsr), sum(sum_dsc) from

      (select part_id, sum(quant) as quant, sum(sum_ndso) as sum_ndso, sum(sum_ndsr) as sum_ndsr, sum(sum_dsc) as sum_dsc from doc_detail where
      g$profile_id=:g$profile_id
     -- and part_id=106426
       and doc_commitdate<:beg_date and part_type=0 group by part_id
      having abs(sum(quant))>:min_quant or (abs(sum(sum_ndso))>:min_quant)
        union all
      select distinct part_id,0 as quant,0 as sum_ndso, 0 as sum_ndsr, 0 as sum_dsc from doc_detail where g$profile_id=:g$profile_id
     -- and part_id=106426
      and doc_commitdate between :beg_date and :end_date and part_type=0)
      group by part_id into :part_id,:last_quant,:sum_ndso,:sum_ndsr, :sum_dsc do
  begin
    last_date=beg_date;
    for select sum(quant), sum(sum_ndso), sum(sum_ndsr), sum(sum_dsc),
    addmonth(cast('01.'||(extract (month from doc_commitdate))||'.'||(extract (year from doc_commitdate)) as dm_date),1)
     from doc_detail where part_id=:part_id and g$profile_id=:g$profile_id and
     cast(doc_commitdate as dm_date) between :last_date and :end_date group by
      addmonth(cast('01.'||(extract (month from doc_commitdate))||'.'||(extract (year from doc_commitdate)) as dm_date),1)
      into :quant, :ndso, :ndsr, :dsc,:doc_commitdate do
    begin
      if (last_date<doc_commitdate and doc_commitdate>:beg_date) then
      while (last_date<doc_commitdate) do
      begin
        if ((abs(last_quant)>:min_quant or (abs(sum_ndso)>:min_quant) ) and (last_date between :beg_date and :end_date)) then
          insert into warebase_d(DOC_COMMITDATE,PART_ID,QUANT,SUM_NDSO,SUM_NDSR,sum_dsc,G$PROFILE_ID) values
            (:last_date,:part_id,:last_quant,:sum_ndso,:sum_ndsr, :sum_dsc,:g$profile_id);
        last_date=addmonth(last_date,1);
      end
      last_quant=last_quant+quant;
      sum_ndso = sum_ndso+ndso;
      sum_ndsr = sum_ndsr+ndsr;
      sum_dsc = sum_dsc+dsc;
      if ((abs(last_quant)>:min_quant or (abs(sum_ndso)>:min_quant) ) and (last_date between :beg_date and :end_date)) then
          insert into warebase_d(DOC_COMMITDATE,PART_ID,QUANT,SUM_NDSO,SUM_NDSR, sum_dsc,G$PROFILE_ID) values
            (:last_date,:part_id,:last_quant,:sum_ndso,:sum_ndsr, :sum_dsc,:g$profile_id);
      last_date=addmonth(last_date,1);
    end
    if (last_date<beg_date) then last_date=beg_date;
    if ((last_date<>end_date) and (abs(last_quant)>:min_quant)) then
    while (last_date<end_date) do
    begin
      if ((last_date between :beg_date and :end_date)) then
        insert into warebase_d(DOC_COMMITDATE,PART_ID,QUANT,SUM_NDSO,SUM_NDSR, sum_dsc,G$PROFILE_ID) values
          (:last_date,:part_id,:last_quant,:sum_ndso,:sum_ndsr, :sum_dsc,:g$profile_id);
      last_date=addmonth(last_date,1);
    end
    if ((last_date=end_date) and (abs(last_quant)>:min_quant or (abs(sum_ndso)>:min_quant) )) then
      insert into warebase_d(DOC_COMMITDATE,PART_ID,QUANT,SUM_NDSO,SUM_NDSR, sum_dsc,G$PROFILE_ID) values
          (:last_date,:part_id,:last_quant,:sum_ndso,:sum_ndsr, :sum_dsc,:g$profile_id);
  end
  end    */

--cf 2015-11-27 -- пенесено в PR_FILL_4_REP_DB
--  execute statement 'execute procedure SRV$PR_TOTALFILLDATA(''192.168.26.10:Z:\Standart-N\base\ZTRADE_S.FDB'',''SYSDBA'',''masterkey'',0)'
--       ON EXTERNAL '192.168.26.10:Z:\Standart-N\ZTRADE4REP.FDB'
--       AS USER 'SYSDBA' PASSWORD 'masterkey';
--  execute statement 'execute procedure SRV$PR_FILLMARKETINGSDATA(''192.168.26.10:Z:\Standart-N\base\ZTRADE_S.FDB'',''SYSDBA'',''masterkey'');'
--       ON EXTERNAL '192.168.26.10:Z:\Standart-N\ZTRADE4REP.FDB'
--       AS USER 'SYSDBA' PASSWORD 'masterkey';
  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT INSERT ON LOG_PR_SET_WAREBASE_DATE TO PROCEDURE PR_SET_WAREBASE_DATE;
GRANT EXECUTE ON PROCEDURE AA_PRDBG_SETWBDATE TO PROCEDURE PR_SET_WAREBASE_DATE;
GRANT SELECT ON G$PROFILES TO PROCEDURE PR_SET_WAREBASE_DATE;
GRANT SELECT,DELETE ON WAREBASE_G TO PROCEDURE PR_SET_WAREBASE_DATE;
GRANT SELECT,INSERT,DELETE ON WAREBASE_D TO PROCEDURE PR_SET_WAREBASE_DATE;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_SET_WAREBASE_DATE;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_SET_WAREBASE_DATE TO SYSDBA;