if exists ( select * from sys.procedures p where p.name = 'spx_LoadItemPlanModification' ) DROP PROCEDURE [dbo].[spx_LoadItemPlanModification]
GO
CREATE PROCEDURE [dbo].[spx_LoadItemPlanModification]
    @TableName varchar(max)
AS 
BEGIN 

    declare @insertCommand nvarchar(max)
    declare @currentDate datetime = GETDATE()

    -- Очищаем ранее загруженные планы
    delete from IES.T_ITEM_PLAN_MODIFICATION;
    delete from IES.T_ITEM_PLAN;

    -- Болванка времянки с сырыми данными
    select top 0 NEWID() as PlanDictionaryID, cast(null as varchar(max)) as PlanMonth, cast(null as varchar(max)) as PeriodYear, cast(null as varchar(max)) as MO, cast(null as varchar(max)) as [PROFILE], cast(null as decimal(31,10)) as [PlanValue]
        into #plan

    -- Курсор на формирование таблицы с сырыми данными для загрузки
    DECLARE ValueColumnsCursor CURSOR FOR
    select N'insert into #plan select pd.PlanDictionaryID, cast(v.Column1 as int) as PlanMonth, cast(v.Column2 as int) as PeriodYear, v.Column3 as MO, cast(v.Column5 as int) as [PROFILE], cast(REPLACE(v.' + c.[name] + ', '','', ''.'') as decimal(31,10)) as [PlanValue]
    from IES.T_PLAN_DICTIONARY pd, (select * from dbo.ITEM_PLAN_MODIFICATION_0 where ISNUMERIC(' + c.[name] + ') <> 0) v
    where pd.Code = (select top 1 ' + c.[name] + ' from dbo.ITEM_PLAN_MODIFICATION_0 where ISNUMERIC(' + c.[name] + ') = 0)'
    from sys.columns c
    where c.object_id = object_id(N'[dbo].[ITEM_PLAN_MODIFICATION_0]')
                    and
            c.name not in ('Column0', 'Column1', 'Column2', 'Column3', 'Column4', 'Column5')

    -- Открываем курсор, фетчим первую команду
    OPEN ValueColumnsCursor
    FETCH NEXT FROM ValueColumnsCursor INTO @insertCommand

        -- Пока есть инфа, заливаем данные во времянку
        WHILE @@FETCH_STATUS = 0
        begin
            exec sp_executesql @insertCommand
            FETCH NEXT FROM ValueColumnsCursor INTO @insertCommand
        end

    -- Закрываем курсор
    CLOSE ValueColumnsCursor
    DEALLOCATE ValueColumnsCursor

    -- Собираем план и факт в одну сгруппированную таблицу
    SELECT
               NEWID() as ItemPlanModificationID
              ,ISNULL(SUM(p.[PlanValue]), 0) as PlanValue
              ,ISNULL(SUM(fact.FactValue), 0) as FactValue
              ,@currentDate as ModificationDate
              ,p.PlanMonth
              ,case when p.PlanMonth in (1, 2, 3) then 1
                  when p.PlanMonth in (4, 5, 6) then 2    
                  when p.PlanMonth in (7, 8, 9) then 3
                  when p.PlanMonth in (10, 11, 12) then 4
               end as PlanQuarter
              ,p.PeriodYear
              ,mo.MCOD as MO
              ,p.PlanDictionaryID as PlanParameter
              ,v002.IDPR as [PROFILE]
        into #plan_fact
    from #plan p
        join IES.T_F003_MO mo on mo.MCOD = p.MO
        left join IES.T_V002_PROFILE v002 on v002.IDPR = p.[PROFILE]
        left join
        (
            select sif.PeriodYear, sif.PlanMonth, sif.MO, sif.[PROFILE], sif.PlanParameter, SUM(sif.Value) as FactValue
            from IES.T_SCHET_ITEM_FAKT sif
            where sif.IsMek = 0
                and not exists (
                                select *
                                from IES.T_PLAN_CUT_ITEM pci
                                join IES.T_SCHET_ITEM_FAKT sif1 on sif1.SchetItemFaktID = pci.SchetItemFakt
                                where sif1.SchetSluch = sif.SchetSluch
                                and pci.CutReason in (0, 1)
                            )
            group by sif.PeriodYear, sif.PlanMonth, sif.MO, sif.[PROFILE], sif.PlanParameter
        ) as fact on fact.PeriodYear = p.PeriodYear 
                and 
            fact.PlanMonth < p.PlanMonth 
                and 
            fact.MO = p.MO 
                and 
            (fact.[PROFILE] = p.[PROFILE] or fact.[PROFILE] is null and p.[PROFILE] is null)
    group by v002.IDPR, p.PlanDictionaryID, mo.MCOD, p.PeriodYear, p.PlanMonth

    -- Генерим записи плана за предыдущий период с учётом факта
    INSERT INTO [IES].[T_ITEM_PLAN_MODIFICATION]
               ([ItemPlanModificationID]
               ,[Value]
               ,[ModificationDate]
               ,[PlanParameter]
               ,[PlanMonth]
               ,[PlanQuarter]
               ,[PROFILE]
               ,[PeriodYear]
               ,[MO])
    select 
                NEWID()
               ,case 
                    when ISNULL(pf.FactValue, 0) > ISNULL(pf.PlanValue, 0) then ISNULL(pf.PlanValue, 0)
                    else ISNULL(pf.FactValue, 0)
                end as [Value]
               ,pf.[ModificationDate]
               ,pf.[PlanParameter]
               ,pf.[PlanMonth] - 1
               ,case
                    when pf.[PlanMonth] - 1 in(1, 2, 3) then 1
                    when pf.[PlanMonth] - 1 in(4, 5, 6) then 2    
                    when pf.[PlanMonth] - 1 in(7, 8, 9) then 3
                    when pf.[PlanMonth] - 1 in(10, 11, 12) then 4    
                end
               ,pf.PROFILE
               ,pf.[PeriodYear]
               ,pf.[MO]
    from #plan_fact pf
    where pf.PlanMonth > 1

    -- Генерим записи плана за текущий период
    INSERT INTO [IES].[T_ITEM_PLAN_MODIFICATION]
               ([ItemPlanModificationID]
               ,[Value]
               ,[ModificationDate]
               ,[PlanParameter]
               ,[PlanMonth]
               ,[PlanQuarter]
               ,[PROFILE]
               ,[PeriodYear]
               ,[MO])
    select 
                pf.[ItemPlanModificationID]
               ,case 
                    when pf.PlanMonth = 1 then ISNULL(pf.PlanValue, 0)
                    when ISNULL(pf.FactValue, 0) > ISNULL(pf.PlanValue, 0) then 0
                    else ISNULL(pf.PlanValue, 0) - ISNULL(pf.FactValue, 0)
                end
               ,pf.[ModificationDate]
               ,pf.[PlanParameter]
               ,pf.[PlanMonth]
               ,pf.[PlanQuarter]
               ,pf.[PROFILE]
               ,pf.[PeriodYear]
               ,pf.[MO]
    from #plan_fact pf

    -- Дроп времянок
    drop table #plan_fact
    drop table #plan
END

