IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_PlanCutUncalculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_PlanCutUncalculate]
GO
-- ======================================
-- Author:      Vitacore (с)
-- Create date: 2022-06-06
-- Description: Откат фактического среза (экспериментально, использовать с осторожностью)
-- ======================================
CREATE PROCEDURE [dbo].[sp_PlanCutUncalculate]
    @PlanCutDictionary uniqueidentifier
AS
BEGIN

    -- Список ошибок для удаления
    select t1.plancutitemid, saf.ObjectID, ss.schetsluchaccomplished, sz.schet, sai.schetactitemid, aie.ActItemErrorID
    into #temp_plan_cut_item_errors
    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 = @PlanCutDictionary
            and
        t1.F014DenyReason = f014.F014DenyReasonID
        and t1.CutReason in (0, 1)

    -- Удаление ошибок из актов
    delete aie
    from ies.T_ACT_ITEM_ERROR aie
    where aie.ActItemErrorID in 
        (
            select pcie.ActItemErrorID
            from #temp_plan_cut_item_errors pcie
        )

    -- Расчитываем суммы на случаях
    update 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
    join ies.T_SCHET_SLUCH ss on (pcie.ObjectID = ss.SchetSluchID)

    -- Расчитываем суммы на законченных случаях
    update 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
    join ies.T_SCHET_SLUCH_ACCOMPLISHED ssa on (pcie.schetsluchaccomplished = ssa.SchetSluchAccomplishedID)

    -- Отвязываем PLAN_CUT_ITEM
    update pci
    set SchetActItem = null
    from ies.T_PLAN_CUT_ITEM pci
    where pci.PlanCutDictionary = @PlanCutDictionary

    -- Возвращаем IsMek = 0 для IES.T_SCHET_ITEM_FAKT
    update sif
    set IsMek = 0
    from ies.T_PLAN_CUT_ITEM pci
    join IES.T_SCHET_ITEM_FAKT sif on sif.SchetItemFaktID = pci.SchetItemFakt
    where pci.PlanCutDictionary = @PlanCutDictionary
            and 
        pci.CutReason in (0, 1)

    -- Удаление пустых элементов актов
    delete sai
    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
        )

    -- Расчитываем суммы на счетах
    update s
    set SUMMAP = aa.S_SUMP
        ,SANK_MEK = aa.S_SANK_MEK
    from ies.T_SCHET s
    cross apply(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
    )

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

    -- Переводим срез в предварительный
    update pcd
    set [Status] = 1
    from ies.T_PLAN_CUT_DICTIONARY pcd
    where pcd.PlanCutDictionaryID = @PlanCutDictionary

    drop table #temp_plan_cut_item_errors
END
