DROP PROCEDURE IF EXISTS public.sp_plancutcalculation;
DROP FUNCTION IF EXISTS public.sp_plancutcalculation;

CREATE PROCEDURE public.sp_plancutcalculation(IN "PlanCutDictionary" uuid)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_defaultErrorid int;
    v_plancutdictionary uuid := "PlanCutDictionary";
BEGIN
    
    --RAISE NOTICE 'Загружаем недостающие ошибки в справочник ошибок %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    INSERT INTO IES.T_EXPERTISE_ERROR
               (ExpertiseErrorID
               ,Code
               ,Name
               ,DateAdd
               ,ErrorType
               ,ChargeType
               ,ChargeSize
               ,Priority
               ,F014DenyReason)
    SELECT
               ROW_NUMBER() over (order by t.F014DenyReason) + (select max(ExpertiseErrorID) from ies.T_EXPERTISE_ERROR LIMIT 1)--<ExpertiseErrorID, int,>
              ,gen_random_uuid()--<Code, varchar(255),>
              ,coalesce(f014.Naim, '')--<Name, varchar(255),>
              ,now()--<DateAdd, datetime,>
              ,0--<ErrorType, int,>
              ,0--<ChargeType, int,>
              ,100--<ChargeSize, decimal(31,10),>
              ,10--<Priority, int,>
              ,t.F014DenyReason--<F014DenyReason, int,>
    from (
            select cs.F014DenyReason
            from IES.T_CLASSIFICATION_SETTING cs
            where cs.IsValueControlParameter = true
                    and
                cs.F014DenyReason is not null
            group by cs.F014DenyReason
            union
            select dr.F014DenyReasonID
            from IES.T_F014_DENY_REASON dr
            where dr.Kod = 273 and dr.Osn = '1.6.2.'
        ) t
        left join IES.T_F014_DENY_REASON f014 on f014.F014DenyReasonID = t.F014DenyReason
    where not exists
        (
            select *
            from IES.T_EXPERTISE_ERROR ee
            where ee.F014DenyReason = t.F014DenyReason
        );

    --RAISE NOTICE 'Идентификатор ошибки по умолчанию %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    
    SELECT ee.ExpertiseErrorID
    INTO v_defaultErrorid 
    from ies.T_EXPERTISE_ERROR ee
    join ies.T_F014_DENY_REASON f014 on (ee.F014DenyReason = f014.F014DenyReasonID)
    where f014.Kod = 273 and f014.Osn = '1.6.2.'
    order by ee.DateAdd desc, ee.ExpertiseErrorID
    LIMIT 1;
   
    --RAISE NOTICE 'v_defaultErrorid: % | %', coalesce(v_defaultErrorid::varchar, 'null'), to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    
    --RAISE NOTICE 'Генерируем недостающие акты %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    insert into ies.T_SCHET_ACT(SchetActID, Schet, Number, PayOut, TotalCost)
    select gen_random_uuid(), s.SchetID, '1', s.SUMMAP, s.SANK_MEK
    from ies.T_PLAN_CUT_ITEM t1
    join ies.T_SCHET_ITEM_FAKT saf on (t1.SchetItemFakt = saf.SchetItemFaktID)
    join ies.T_SCHET_SLUCH ss on (saf.ObjectID = ss.schetsluchid)
    join ies.T_SCHET_ZAP sz on (ss.schetzap = sz.schetzapid)
    join ies.T_SCHET s on (sz.schet = s.schetid)
    left join ies.T_SCHET_ACT sa on (sz.schet = sa.schet)
    where t1.PlanCutDictionary = v_plancutdictionary
        and sa.schetactid is null
        and t1.CutReason in (0,1)
    group by s.SchetID, s.SUMMAP, s.SANK_MEK;
       
    --RAISE NOTICE 'проставляем отказы у случаев %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    insert INTO ies.T_SCHET_ACT_ITEM(SchetActItemID, SchetAct, SchetSluchAccomplished, SchetSluch, IsAddToAct)
    select gen_random_uuid(), sa.schetactid, ss.SchetSluchAccomplished, ss.schetsluchid, true
    from ies.T_PLAN_CUT_ITEM t1
    join ies.T_SCHET_ITEM_FAKT saf on (t1.SchetItemFakt = saf.SchetItemFaktID)
    join ies.T_SCHET_SLUCH ss on (saf.ObjectID = ss.schetsluchid)
    join ies.T_SCHET_ZAP sz on (ss.schetzap = sz.schetzapid)
    join ies.T_SCHET_ACT sa on (sz.schet = sa.schet)
    where t1.PlanCutDictionary = v_plancutdictionary
        and not exists (select 1 from ies.T_SCHET_ACT_ITEM sai where sai.schetsluch = ss.schetsluchid)
        and t1.CutReason in (0,1)
    group by sa.schetactid, ss.SchetSluchAccomplished, ss.schetsluchid;

   --RAISE NOTICE 'проставляем отказы у случаев %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    drop table if exists temp_plan_cut_item;
    create temp table temp_plan_cut_item as
    select t1.plancutitemid, saf.ObjectID, ss.SchetSluchAccomplished, sz.schet, sai.schetactitemid, case when ss.OPLATA = 2 then 0 else 1 end as isCut, t1.F014DenyReason, saf.SchetItemFaktID    
    from ies.T_PLAN_CUT_ITEM t1
    join ies.T_SCHET_ITEM_FAKT saf on (t1.SchetItemFakt = saf.SchetItemFaktID)
    join ies.T_SCHET_SLUCH ss on (saf.ObjectID = ss.schetsluchid)
    join ies.T_SCHET_ZAP sz on (ss.schetzap = sz.schetzapid)
    join ies.T_SCHET_ACT_ITEM sai on (ss.schetsluchid = sai.schetsluch)
    where t1.PlanCutDictionary = v_plancutdictionary
        and t1.CutReason in (0,1);

   --RAISE NOTICE 'связываем plan_cut_item с записью акта МЭК %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update ies.T_PLAN_CUT_ITEM t1
    set SchetActItem = tt.SchetActItemID
    from temp_plan_cut_item tt
    WHERE (tt.PlanCutItemID = t1.PlanCutItemID);
    
    --RAISE NOTICE 'удаляем принятие вручную, если произошло отклонение по объёму %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    delete FROM ies.T_ITEM_ACCEPTED_BY_HAND iabh     
    USING temp_plan_cut_item tt
    join ies.T_SCHET_ACT_ITEM sai on (tt.SchetActItemID = sai.SchetActItemID)
    where sai.IsAddToAct = false
    AND sai.SchetActItemID = iabh.SchetActItem;
    
    --RAISE NOTICE 'еще раз создаём записи акта %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    insert INTO ies.T_SCHET_ACT_ITEM(SchetActItemID, SchetAct, SchetSluchAccomplished, SchetSluch, IsAddToAct)
    select gen_random_uuid(), sai.schetact, ss.SchetSluchAccomplished, ss.schetsluchid, true
    from temp_plan_cut_item tt
    join ies.T_SCHET_ACT_ITEM sai on (tt.SchetActItemID = sai.SchetActItemID)
    join ies.T_SCHET_SLUCH ss on (sai.SchetSluchAccomplished = ss.SchetSluchAccomplished)
    where not exists (select 1 from ies.T_SCHET_ACT_ITEM item where item.SchetSluch = ss.SchetSluchID limit 1)
        group by sai.schetact, ss.SchetSluchAccomplished, ss.schetsluchid;
    
    --RAISE NOTICE 'проставляем признак отказа по МЭК в запись акта МЭК по объёмам %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update ies.T_SCHET_ACT_ITEM sai
    set IsAddToAct = true
    from temp_plan_cut_item tt    
    where sai.IsAddToAct = false AND tt.SchetActItemID = sai.SchetActItemID;

    --RAISE NOTICE 'создаём причины отказа для записи акта МЭК %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    insert into ies.T_ACT_ITEM_ERROR(ActItemErrorID, SchetActItem, ExpertiseError)
    select gen_random_uuid(), tt.SchetActItemID, COALESCE(aa.ExpertiseErrorID, v_defaultErrorid)
    from temp_plan_cut_item tt
    join ies.T_SCHET_ACT_ITEM sai on (tt.SchetActItemID = sai.SchetActItemID)
    join ies.T_SCHET_ACT sa on (sai.schetact = sa.schetactid)
    join ies.T_SCHET s on (sa.schet = s.schetid)
    LEFT JOIN LATERAL (select ExpertiseErrorID from ies.T_EXPERTISE_ERROR ee
        where ee.F014DenyReason = tt.F014DenyReason
            and ee.DateAdd <= s.ReceivedDate and (ee.DateRemove is null or ee.DateRemove >= s.ReceivedDate)
    order by ee.DateAdd desc, ee.ExpertiseErrorID) aa ON true
    group by tt.SchetActItemID, aa.ExpertiseErrorID;

   
    --RAISE NOTICE 'удаляем случаи из временной таблицы, которые уже были отказаны, следовательно у них сумма не меняется %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    delete from temp_plan_cut_item
    where iscut = 0;
        
    --RAISE NOTICE 'отмечаем срезанные записи факта как не подлежащие расчёту %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update IES.T_SCHET_ITEM_FAKT sif
    set IsMek = true     
    from temp_plan_cut_item tt    
    WHERE sif.SchetItemFaktID = tt.SchetItemFaktID;

    --RAISE NOTICE 'Создание временной таблицы случаев подлежащих срезу %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    drop table if exists temp_calc_ss;
    create temp table temp_calc_ss as
    select tt.ObjectID    
    from temp_plan_cut_item tt
    group by tt.ObjectID;
    
   
    --RAISE NOTICE 'расчитываем суммы на случаях (типа рекальк)%', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update ies.T_SCHET_SLUCH ss
    set SANK_MEK = ss.SUMP
        ,SUMP = 0
        ,OPLATA = 2
    from temp_calc_ss tt
    WHERE (tt.ObjectID = ss.SchetSluchID);
     
    drop table temp_calc_ss;

    --RAISE NOTICE 'Создание временной таблицы зак случаев подлежащих срезу %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    drop table if exists temp_calc_ssa;
    create temp table temp_calc_ssa as
    select tt.SchetSluchAccomplished    
    from temp_plan_cut_item tt
    group by tt.SchetSluchAccomplished;
    
    --RAISE NOTICE 'расчитываем суммы на законченных случаях(рекальк) %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update ies.T_SCHET_SLUCH_ACCOMPLISHED ssa
    set SANK_MEK = ssa.SUMP
        ,SUMP = 0
        ,OPLATA = 2
    from temp_calc_ssa tt
    WHERE (tt.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID);

    drop table temp_calc_ssa;

    --RAISE NOTICE 'рекальк счёта %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    drop table if exists temp_calc_schets;
    create temp table temp_calc_schets as
    select tt.Schet    
    from temp_plan_cut_item tt
    group by tt.Schet;
    
    --расчитываем суммы на счетах
    update ies.T_SCHET s
    set SUMMAP = COALESCE(aa.S_SUMP,0)
        ,SANK_MEK = COALESCE(aa.S_SANK_MEK,0)
    from temp_calc_schets tt    
    CROSS JOIN LATERAL(select sum(ssa.SUMP) as S_SUMP, sum(ssa.SANK_MEK) as S_SANK_MEK
        from ies.T_SCHET_ZAP sz
        join ies.T_SCHET_SLUCH_ACCOMPLISHED ssa on (sz.SchetZapID = ssa.SchetZap)
        where sz.Schet = tt.Schet) aa
        where (tt.Schet = s.SchetID);

    --RAISE NOTICE 'рекальк акта %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update ies.T_SCHET_ACT sa
    set TotalCost = COALESCE(s.SANK_MEK, 0)
        , PayOut = COALESCE(s.SUMMAP, 0)
    from temp_calc_schets tt
    join ies.T_SCHET s on tt.Schet = s.SchetID
    WHERE sa.Schet = s.SchetID;
    
    drop table temp_calc_schets;
    drop table temp_plan_cut_item;
--*/
end;
$procedure$;
