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_PlanCutPrepare]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_PlanCutPrepare]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2021-08-09
-- Description: Генерация записей предварительного среза для случаев, срезанных по причине присутствия срезанной записи в законченном случае
-- =============================================
CREATE PROCEDURE [dbo].[sp_PlanCutPrepare]
  @planCutDictionaryId uniqueidentifier
AS
BEGIN

    declare @year int,
            @quarter int,
            @month int

    select @year = fcs.PeriodYear
          ,@month = case
                        when fcs.PeriodValue > 12 then null
                        else fcs.PeriodValue
                    end
          ,@quarter = case
                          when fcs.PeriodValue in (1, 2, 3) then 1
                          when fcs.PeriodValue in (4, 5, 6) then 2
                          when fcs.PeriodValue in (7, 8, 9) then 3
                          when fcs.PeriodValue in (10, 11, 12) then 4
                      end
    from IES.T_PLAN_CUT_DICTIONARY pcd
        join IES.T_FACT_CALC_SETTING fcs on fcs.FactCalcSettingID = pcd.FactCalcSetting
    where pcd.PlanCutDictionaryID = @planCutDictionaryId


    INSERT INTO [IES].[T_PLAN_CUT_ITEM]
               ([PlanCutItemID]
               ,[PlanCutDictionary]
               ,[SchetItemFakt]
               ,[SchetActItem]
               ,[CutReason])
    SELECT
               NEWID()--<PlanCutItemID, uniqueidentifier,>
              ,@planCutDictionaryId--<PlanCutDictionary, uniqueidentifier,>
              ,sif.SchetItemFaktID--<SchetItemFakt, uniqueidentifier,>
              ,null--<SchetActItem, uniqueidentifier,>
              ,1--<CutReason, int,>
    from IES.T_SCHET_ITEM_FAKT sif
        join IES.T_SCHET_SLUCH ss on ss.SchetSluchID = sif.SchetSluch
    where sif.PeriodYear = @year
                and
            sif.PlanQuarter = @quarter
                and
            (@month is null or sif.PlanMonth = @month)
                and
            sif.IsMek = 0
                and
            not exists
            (
                select *
                from IES.T_PLAN_CUT_ITEM pci
                where pci.PlanCutDictionary = @planCutDictionaryId
                        and
                    pci.SchetItemFakt = sif.SchetItemFaktID
            )
                and
            exists
            (
                select *
                from IES.T_PLAN_CUT_ITEM pci
                    join IES.T_SCHET_ITEM_FAKT sif1 on sif1.SchetItemFaktID = pci.SchetItemFakt
                    join IES.T_SCHET_SLUCH ss1 on ss1.SchetSluchID = sif1.SchetSluch
                where pci.PlanCutDictionary = @planCutDictionaryId
                        and
                    sif1.SchetItemFaktID <> sif.SchetItemFaktID
                        and
                    ss1.SchetSluchAccomplished = ss.SchetSluchAccomplished
            )

END
