SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DropSchetItemFacts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DropSchetItemFacts]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2021-01-13
-- Description: Удаление записей таблицы IES.T_SCHET_ITEM_FAKT
-- =============================================
CREATE PROCEDURE [dbo].[sp_DropSchetItemFacts]
  @year int,
  @quarter int,
  @month int,
  @ids xml = ''
AS
BEGIN

    select T.c.value('.', 'uniqueidentifier') as id
        into #ids
    from @ids.nodes('/ids/id') T(c)

    -- Помечаем связанные группы факта, что они требуют пересчёта
    update f
    set f.NeedValuesUpdate = 1
    from IES.T_ITEM_FACT f
        join IES.T_SCHET_ITEM_FAKT sif on sif.ItemFact = f.ItemFactID
        left join IES.T_SCHET_SLUCH ss on ss.SchetSluchID = sif.SchetSluch
        left join IES.T_SCHET_ZAP sz on sz.SchetZapID = ss.SchetZap
    where sif.ItemFact is not null
            and
        sif.PeriodYear = @year
            and
        (@quarter = 0 or sif.PlanQuarter = @quarter)
            and
        (@month = 0 or sif.PlanMonth = @month)
            and
        (not exists(select * from #ids) or sz.Schet in (select id from #ids))

    -- Удаляем элементы среза принятых вручную случаев
    delete pci
    from IES.T_SCHET_ITEM_FAKT sif
        join IES.T_PLAN_CUT_ITEM pci on pci.SchetItemFakt = sif.SchetItemFaktID
        left join IES.T_SCHET_SLUCH ss on ss.SchetSluchID = sif.SchetSluch
        left join IES.T_SCHET_ZAP sz on sz.SchetZapID = ss.SchetZap
    where sif.PeriodYear = @year
            and
        (@quarter = 0 or sif.PlanQuarter = @quarter)
            and
        (@month = 0 or sif.PlanMonth = @month)
            and
        (not exists(select * from #ids) or sz.Schet in (select id from #ids))
            and
        sif.IsMek = 0

    -- Удаляем все записи факта без элементов среза
    delete sif
    from IES.T_SCHET_ITEM_FAKT sif
        left join IES.T_SCHET_SLUCH ss on ss.SchetSluchID = sif.SchetSluch
        left join IES.T_SCHET_ZAP sz on sz.SchetZapID = ss.SchetZap
    where sif.PeriodYear = @year
            and
        (@quarter = 0 or sif.PlanQuarter = @quarter)
            and
        (@month = 0 or sif.PlanMonth = @month)
            and
        (not exists(select * from #ids) or sz.Schet in (select id from #ids))
            and
        not exists
        (
            select *
            from IES.T_PLAN_CUT_ITEM pci
            where pci.SchetItemFakt = sif.SchetItemFaktID
        )

    drop table #ids;

END
GO
