IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_PlanCutCalculation]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_PlanCutCalculation]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2021-02-09
-- Description: Обновление IES.T_PLAN_CUT_ITEM и сумм на случаях и счетах
-- =============================================
CREATE PROCEDURE [dbo].[sp_PlanCutCalculation]
  @PlanCutDictionary uniqueidentifier
AS
BEGIN

    -- Загружаем недостающие ошибки в справочник ошибок
    INSERT INTO [IES].[T_EXPERTISE_ERROR]
               ([ExpertiseErrorID]
               ,[Code]
               ,[Name]
               ,[DateAdd]
               ,[ErrorType]
               ,[ChargeType]
               ,[ChargeSize]
               ,[Priority]
               ,[F014DenyReason])
    SELECT
               ROW_NUMBER() over (order by t.F014DenyReason) + (select top 1 max([ExpertiseErrorID]) from ies.T_EXPERTISE_ERROR)--<ExpertiseErrorID, int,>
              ,NEWID()--<Code, varchar(255),>
              ,ISNULL(f014.Naim, '')--<Name, varchar(255),>
              ,GETDATE()--<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 = 1
                    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
        )

    -- Идентификатор ошибки по умолчанию
    declare @defaultErrorid int
    select top 1 @defaultErrorid = ee.ExpertiseErrorID
    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

    -- Генерируем недостающие акты
    insert into ies.T_SCHET_ACT(SchetActID, Schet, Number, PayOut, TotalCost)
    select newid(), 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 = @PlanCutDictionary
        and sa.schetactid is null
        and t1.CutReason in (0,1)
    group by s.SchetID, s.SUMMAP, s.SANK_MEK

    --проставляем отказы у случаев
    insert ies.T_SCHET_ACT_ITEM(SchetActItemID, SchetAct, SchetSluchAccomplished, SchetSluch, IsAddToAct)
    select newid(), sa.schetactid, ss.SchetSluchAccomplished, ss.schetsluchid, 1
    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 = @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

    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
    into #temp_plan_cut_item
    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 = @PlanCutDictionary
        and t1.CutReason in (0,1)

    update t1
    set SchetActItem = tt.SchetActItemID
    from #temp_plan_cut_item tt
    join ies.T_PLAN_CUT_ITEM t1 on (tt.PlanCutItemID = t1.PlanCutItemID)
    
    delete iabh
    from #temp_plan_cut_item tt
    join ies.T_SCHET_ACT_ITEM sai on (tt.SchetActItemID = sai.SchetActItemID)
    join ies.T_ITEM_ACCEPTED_BY_HAND iabh on (sai.SchetActItemID = iabh.SchetActItem)
    where sai.IsAddToAct = 0
        
    insert ies.T_SCHET_ACT_ITEM(SchetActItemID, SchetAct, SchetSluchAccomplished, SchetSluch, IsAddToAct)
    select newid(), sai.schetact, ss.SchetSluchAccomplished, ss.schetsluchid, 1
    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 top 1 1 from ies.T_SCHET_ACT_ITEM item where item.SchetSluch = ss.SchetSluchID)
        group by sai.schetact, ss.SchetSluchAccomplished, ss.schetsluchid
    
    update sai
    set IsAddToAct = 1
    from #temp_plan_cut_item tt
    join ies.T_SCHET_ACT_ITEM sai on (tt.SchetActItemID = sai.SchetActItemID)
    where sai.IsAddToAct = 0

    insert into ies.T_ACT_ITEM_ERROR(ActItemErrorID, SchetActItem, ExpertiseError)
    select newid(), tt.SchetActItemID, isnull(aa.ExpertiseErrorID, @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)
    outer apply(select top 1 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
    group by tt.SchetActItemID, aa.ExpertiseErrorID

    --удаляем случаи, которые уже были отказаны, следовательно у них сумма не меняется
    delete from #temp_plan_cut_item
    where iscut = 0
    
    --отмечаем срезанные записи факта как не подлежащие расчёту
    update sif
    set IsMek = 1
    from #temp_plan_cut_item tt
    join IES.T_SCHET_ITEM_FAKT sif on sif.SchetItemFaktID = tt.SchetItemFaktID

    select tt.ObjectID
    into #temp_calc_ss
    from #temp_plan_cut_item tt
    group by tt.ObjectID

    --расчитываем суммы на случаях
    update ss
    set SANK_MEK = ss.SUMP
        ,SUMP = 0
        ,OPLATA = 2
    from #temp_calc_ss tt
    join ies.T_SCHET_SLUCH ss on (tt.ObjectID = ss.SchetSluchID)
    
    drop table #temp_calc_ss

    select tt.SchetSluchAccomplished
    into #temp_calc_ssa
    from #temp_plan_cut_item tt
    group by tt.SchetSluchAccomplished

    --расчитываем суммы на законченных случаях
    update ssa
    set SANK_MEK = ssa.SUMP
        ,SUMP = 0
        ,OPLATA = 2
    from #temp_calc_ssa tt
    join ies.T_SCHET_SLUCH_ACCOMPLISHED ssa on (tt.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID)

    drop table #temp_calc_ssa

    select tt.Schet
    into #temp_calc_schets
    from #temp_plan_cut_item tt
    group by tt.Schet
    
    --расчитываем суммы на счетах
    update s
    set SUMMAP = aa.S_SUMP
        ,SANK_MEK = aa.S_SANK_MEK
    from #temp_calc_schets tt
    join ies.T_SCHET s on (tt.Schet = s.SchetID)
    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

    --расчитываем суммы на актах
    update sa 
    set TotalCost = isnull(s.SANK_MEK, 0)
        , PayOut = isnull(s.SUMMAP, 0)
    from #temp_calc_schets tt
    join ies.T_SCHET s on tt.Schet = s.SchetID
    join ies.T_SCHET_ACT sa on sa.Schet = s.SchetID
    
    drop table #temp_calc_schets
    drop table #temp_plan_cut_item
END
