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], NEWID() as RAZDEL
        into #plan

    -- Курсор на формирование таблицы с сырыми данными для загрузки
    DECLARE ValueColumnsCursor CURSOR FOR
    select N'insert into #plan select pd.PlanDictionaryID, cast(v.Column2 as int) as PlanMonth, cast(v.Column1 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], r.DictionaryBaseID as RAZDEL
    from IES.T_PLAN_DICTIONARY pd, IES.R_NSI_RAZDELS r, (select * from dbo.ITEM_PLAN_MODIFICATION_0 where ISNUMERIC(' + c.[name] + ') <> 0) v
    where pd.Code + ''_'' + r.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,
            p.PlanValue,
            ISNULL(SUM(fact.FactValue), 0) as FactValue,
            @currentDate as ModificationDate,
            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.PlanMonth,
            p.PeriodYear,
            p.MO,
            p.PlanParameter,
            p.PROFILE,
            p.RAZDEL
        into #plan_fact
    from (
            select
                      ISNULL(SUM(p.[PlanValue]), 0) as PlanValue
                      ,p.PlanMonth
                      ,p.PeriodYear
                      ,mo.MCOD as MO
                      ,p.PlanDictionaryID as PlanParameter
                      ,v002.IDPR as [PROFILE]
                      ,p.RAZDEL
            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]
            group by v002.IDPR, p.RAZDEL, p.PlanDictionaryID, mo.MCOD, p.PeriodYear, p.PlanMonth
        ) p
        left join
        (
            select sif.PeriodYear, sif.PlanMonth, sif.MO, sif.[PROFILE], sif.RAZDEL, 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.ObjectID = sif.ObjectID
                                and pci.CutReason in (0, 1)
                            )
            group by sif.PeriodYear, sif.PlanMonth, sif.MO, sif.[PROFILE], sif.RAZDEL, 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)
                and
            fact.RAZDEL = p.RAZDEL 
                and 
            fact.PlanParameter = p.PlanParameter
    group by p.[PROFILE], p.RAZDEL, p.PlanParameter, p.MO, p.PeriodYear, p.PlanMonth, p.PlanValue

    -- Генерим записи плана за предыдущий период с учётом факта
    INSERT INTO [IES].[T_ITEM_PLAN_MODIFICATION]
               ([ItemPlanModificationID]
               ,[Value]
               ,[ModificationDate]
               ,[PlanParameter]
               ,[PlanMonth]
               ,[PlanQuarter]
               ,[PROFILE]
               ,[RAZDEL]
               ,[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.RAZDEL
               ,pf.[PeriodYear]
               ,pf.[MO]
    from #plan_fact pf
    where pf.PlanMonth > 1

    -- Генерация групп для остатка за предыдущий период
    INSERT INTO [IES].[T_PLAN_CLASSIFICATION]
               ([PlanClassificationID]
               ,[MO]
               ,[PROFILE]
               ,[RAZDEL])
    SELECT
               NEWID()--<PlanClassificationID, uniqueidentifier,>
              ,p.MO--<MO, varchar(6),>
              ,ISNULL(p.PROFILE, 2147483646)--<PROFILE, int,>
              ,p.RAZDEL--<RAZDEL, uniqueidentifier,>
    from IES.T_ITEM_PLAN_MODIFICATION p
    where p.PlanMonth > 1
            and
        not exists
        (
            select *
            from IES.T_PLAN_CLASSIFICATION pc
            where 
                pc.MO = p.MO 
                    and 
                pc.RAZDEL = p.RAZDEL 
                    and 
                pc.PROFILE = ISNULL(p.PROFILE, 2147483646)
        )
    group by p.MO, RAZDEL, p.PROFILE

    -- Генерация записей плана для групп остатка за предыдущий период
    INSERT INTO [IES].[T_ITEM_PLAN]
               ([ItemPlanID]
               ,[PlanClassificationNoFk]
               ,[NeedValuesUpdate])
    SELECT
               NEWID()--(<ItemPlanID, uniqueidentifier,>
              ,pc.PlanClassificationID--,<PlanClassificationNoFk, uniqueidentifier,>
              ,1--,<NeedValuesUpdate, bit,>
    from IES.T_ITEM_PLAN_MODIFICATION p
        join IES.T_PLAN_CLASSIFICATION pc on
            pc.MO = p.MO 
                and 
            pc.RAZDEL = p.RAZDEL 
                and 
            pc.PROFILE = ISNULL(p.PROFILE, 2147483646)
    group by pc.PlanClassificationID

    -- Проставляем ссылки на группы для записей прошлого периода
    update ipm
    set ipm.ItemPlan = p.ItemPlanID
    from IES.T_ITEM_PLAN_MODIFICATION ipm
        join IES.T_PLAN_CLASSIFICATION pc on
            pc.MO = ipm.MO 
                and 
            pc.RAZDEL = ipm.RAZDEL 
                and 
            pc.PROFILE = ISNULL(ipm.PROFILE, 2147483646)
        join IES.T_ITEM_PLAN p on p.PlanClassificationNoFk = pc.PlanClassificationID

    -- Генерим записи плана за текущий период
    INSERT INTO [IES].[T_ITEM_PLAN_MODIFICATION]
               ([ItemPlanModificationID]
               ,[Value]
               ,[ModificationDate]
               ,[PlanParameter]
               ,[PlanMonth]
               ,[PlanQuarter]
               ,[PROFILE]
               ,[RAZDEL]
               ,[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.[RAZDEL]
               ,pf.[PeriodYear]
               ,pf.[MO]
    from #plan_fact pf

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