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
                          else fcs.PeriodValue - 12
                      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]
               ,[Rest]
               ,[CalcRest]
               ,[CreationDateTime]
               ,[F014DenyReason])
    SELECT
               NEWID()--<PlanCutItemID, uniqueidentifier,>
              ,@planCutDictionaryId--<PlanCutDictionary, uniqueidentifier,>
              ,sif.SchetItemFaktID--<SchetItemFakt, uniqueidentifier,>
              ,null--<SchetActItem, uniqueidentifier,>
              ,case rpci.CutReason
               when 0 then 1
               when 2 then 2
               end--<CutReason, int,>
              ,rpci.Rest--<Rest, decimal>
              ,rpci.CalcRest--<CalcRest, decimal>
              ,rpci.CreationDateTime--<CreationDateTime, datetime>
              ,rpci.F014DenyReason--<F014DenyReason, uniqueidentifier,>
    from IES.T_SCHET_ITEM_FAKT sif
        join IES.T_SCHET_SLUCH ss with (nolock) on ss.SchetSluchID = sif.SchetSluch
        cross apply
        (
            select top 1 pci.F014DenyReason, pci.Rest, pci.CalcRest, pci.CreationDateTime, pci.CutReason
            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 with (nolock) on ss1.SchetSluchID = sif1.SchetSluch
            where pci.PlanCutDictionary = @planCutDictionaryId
                    and
                sif1.SchetItemFaktID <> sif.SchetItemFaktID
                    and
                ss1.SchetSluchAccomplished = ss.SchetSluchAccomplished
        ) rpci
    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
            )

END
