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

CREATE OR REPLACE PROCEDURE public.sp_plancutuncalculate(IN "PlanCutDictionary" uuid)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_plancutdictionary uuid := "PlanCutDictionary";
BEGIN    
    --RAISE NOTICE 'Список ошибок для удаления %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    drop table if exists temp_plan_cut_item_errors;
    create temp table temp_plan_cut_item_errors as
    select t1.plancutitemid, saf.ObjectID, ss.schetsluchaccomplished, sz.schet, sai.schetactitemid, aie.ActItemErrorID
    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)
    join ies.T_ACT_ITEM_ERROR aie on aie.SchetActItem = sai.SchetActItemID
    join ies.T_EXPERTISE_ERROR ee on ee.ExpertiseErrorID = aie.ExpertiseError
    join ies.T_F014_DENY_REASON f014 on f014.F014DenyReasonID = ee.F014DenyReason
    where t1.PlanCutDictionary = v_PlanCutDictionary
            and
        t1.F014DenyReason = f014.F014DenyReasonID
        and t1.CutReason in (0, 1);

    --RAISE NOTICE 'Удаление ошибок из актов %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    delete FROM ies.T_ACT_ITEM_ERROR aie
    where aie.ActItemErrorID in 
        (
            select pcie.ActItemErrorID
            from temp_plan_cut_item_errors pcie
        );
    
    --RAISE NOTICE 'Расчитываем суммы на случаях %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update ies.T_SCHET_SLUCH ss
    set SUMP = ss.SANK_MEK
        ,SANK_MEK = 0
        ,OPLATA = case
                        when ss.SANK_MEK = ss.SUMV then 1
                        else 3
                  end
    from temp_plan_cut_item_errors pcie
    WHERE (pcie.ObjectID = ss.SchetSluchID);
    
    --RAISE NOTICE 'Расчитываем суммы на законченных случаях %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update ies.T_SCHET_SLUCH_ACCOMPLISHED ssa
    set SUMP = ssa.SANK_MEK
        ,SANK_MEK = 0
        ,OPLATA = case
                        when ssa.SANK_MEK = ssa.SUMV then 1
                        else 3
                  end
    from temp_plan_cut_item_errors pcie
    WHERE (pcie.schetsluchaccomplished = ssa.SchetSluchAccomplishedID);
    
    --RAISE NOTICE 'Отвязываем PLAN_CUT_ITEM %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update ies.T_PLAN_CUT_ITEM pci
    set SchetActItem = null    
    where pci.PlanCutDictionary = v_PlanCutDictionary;
    
    --RAISE NOTICE 'Возвращаем IsMek = 0 для IES.T_SCHET_ITEM_FAKT %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update IES.T_SCHET_ITEM_FAKT sif
    set IsMek = false
    from ies.T_PLAN_CUT_ITEM pci    
    where pci.PlanCutDictionary = v_PlanCutDictionary
            and 
        pci.CutReason in (0, 1)
            and 
        sif.SchetItemFaktID = pci.SchetItemFakt;

    
    --RAISE NOTICE 'Удаление пустых элементов актов %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    delete FROM ies.T_SCHET_ACT_ITEM sai    
    where sai.SchetActItemID in
        (
            select pcie.schetactitemid
            from temp_plan_cut_item_errors pcie
        )
            and
        not exists
        (
            select *
            from ies.T_ACT_ITEM_ERROR aie
            where aie.SchetActItem = sai.SchetActItemID
        );

    --RAISE NOTICE 'Расчитываем суммы на счетах %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update ies.T_SCHET t0
    set SUMMAP = COALESCE(aa.S_SUMP,0)
        ,SANK_MEK = COALESCE(aa.S_SANK_MEK,0)
    from ies.T_SCHET s
    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 = s.SchetID) aa
    where s.SchetID in
    (
        select pcie.schet
        from temp_plan_cut_item_errors pcie
    )
        and 
    t0.Schetid = s.schetid;

    -- Расчитываем суммы на актах
    update ies.T_SCHET_ACT sa
    set TotalCost = coalesce(s.SANK_MEK, 0)
        , PayOut = coalesce(s.SUMMAP, 0)
    from ies.T_SCHET s     
    where sa.Schet in
    (
        select pcie.schet
        from temp_plan_cut_item_errors pcie
    ) 
        and 
    sa.Schet = s.SchetID;

    
    --RAISE NOTICE 'Переводим срез в предварительный %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update ies.T_PLAN_CUT_DICTIONARY pcd
    set Status = 1    
    where pcd.PlanCutDictionaryID = v_PlanCutDictionary;

    drop table temp_plan_cut_item_errors;
--*/
end;
$procedure$;
