if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_LoadPlanPm]') and xtype = N'P')
drop procedure [dbo].[sp_LoadPlanPm]
GO
create procedure dbo.sp_LoadPlanPm 
     @x xml
    ,@FileID uniqueidentifier = null
    ,@Worker uniqueidentifier = null
as
begin

    /*
     * Парсинг исходной xml
     */
    select
        T.c.value('./HEADER[1]/CODE_MO[1]', 'varchar(6)') as CODE_MO,
        T.c.value('./HEADER[1]/TYPE[1]', 'int') as [TYPE]
        into #ZGLV
    from @x.nodes('PLAN_PM') T(c)

    select 
            z.CODE_MO as CODE_MO,
            z.[TYPE] as [TYPE],
            T.c.value('./Year[1]', 'int') as [Year],
            T.c.value('./V016Dispt[1]', 'varchar(3)') as [V016Dispt],
            T.c.value('./Branch[1]', 'varchar(17)') as [BranchCode],
            T.c.value('./TUch[1]', 'varchar(10)') as [TUch],
            T.c.value('./TipUch[1]', 'varchar(1)') as [TipUch],
            T.c.value('./PlanYear[1]', 'int') as [PlanYear],
            T.c.value('./January[1]', 'int') as [January],
            T.c.value('./February[1]', 'int') as [February],
            T.c.value('./March[1]', 'int') as [March],
            T.c.value('./April[1]', 'int') as [April],
            T.c.value('./May[1]', 'int') as [May],
            T.c.value('./June[1]', 'int') as [June],
            T.c.value('./July[1]', 'int') as [July],
            T.c.value('./August[1]', 'int') as [August],
            T.c.value('./September[1]', 'int') as [September],
            T.c.value('./October[1]', 'int') as [October],
            T.c.value('./November[1]', 'int') as [November],
            T.c.value('./December[1]', 'int') as [December]
        into #PLAN_PM
    from @x.nodes('PLAN_PM/BODY') T(c)
    join #ZGLV z on z.CODE_MO is not null and z.[TYPE] is not null

    /*
     * Стыкуем справочники
     */
    ALTER TABLE #PLAN_PM ADD Branch uniqueidentifier
    update pp
    set pp.Branch = b.BranchID
    from #PLAN_PM pp
        join ISZL.T_BRANCH b on b.BR_CODE = pp.BranchCode
                and 
            b.F003Mo = pp.CODE_MO
    where pp.BranchCode is not null
            and 
        b.ACTIVE = 1

    ALTER TABLE #PLAN_PM ADD V016 uniqueidentifier
    update pp
    set pp.V016 = 
        (
            select top 1 v016.V016ID
            from ISZL.T_V016_DISPT v016
            where v016.IDDT = pp.V016Dispt
            order by v016.DATEBEG desc
        )
    from #PLAN_PM pp
    where V016Dispt is not null

    /*
     * Проверки на всякую лажу
     */
    select 1 as CODE, cast('Код МО "' + ISNULL(pp.CODE_MO, '<null>') + '" не найден в справочнике F003' as varchar(max)) as [COMMENT]
        into #errors
    from #PLAN_PM pp
        left join ISZL.T_F003 f003 on f003.MCOD = pp.CODE_MO
    where f003.MCOD is null

    INSERT INTO #errors(CODE, [COMMENT])
    select 1, 'Активное подразделение по коду "' + pp.BranchCode + '" не найдено в МО с кодом "' + ISNULL(pp.CODE_MO, '<null>') + '"'
    from #PLAN_PM pp
    where pp.BranchCode is not null 
            and 
        pp.Branch is null

    INSERT INTO #errors(CODE, [COMMENT])
    select 1, 'Код типа профмероприятия "' + pp.V016Dispt + '" не найден в справочнике V016'
    from #PLAN_PM pp
    where pp.V016Dispt is not null 
            and 
        pp.V016 is null

    INSERT INTO #errors(CODE, [COMMENT])
    select 1, 'Невозможно загрузить план для типа диспансерицации "' + pp.V016Dispt + '" за ' + cast(pp.[Year] as varchar(11)) + ' год по МО "' + pp.CODE_MO + '" для отделения "' + pp.BranchCode + '". В базе присутствует дубликат, либо общий план на данную МО.'
    from #PLAN_PM pp
    where pp.[TYPE] = 1
            and
        pp.[Year] is not null
            and
        pp.CODE_MO is not null 
            and
        pp.BranchCode is not null 
            and 
        pp.Branch is not null
            and 
        pp.V016Dispt is not null
            and 
        pp.V016 is not null
            and
        exists
        (
            select *
            from ISZL.T_DICT_PLAN_PM dpp
            where dpp.[Year] = pp.[Year]
                    and
                dpp.F003Mo = pp.CODE_MO
                    and
                (dpp.Branch = pp.Branch or dpp.Branch is null)
                    and
                dpp.V016Dispt = pp.V016
        )

    INSERT INTO #errors(CODE, [COMMENT])
    select 1, 'Невозможно загрузить план для типа диспансерицации "' + pp.V016Dispt + '" за ' + cast(pp.[Year] as varchar(11)) + ' год по МО "' + pp.CODE_MO + '". В базе присутствует дубликат, либо разбивка по отделениям данной МО.'
    from #PLAN_PM pp
    where pp.[TYPE] = 1
            and
        pp.[Year] is not null
            and
        pp.CODE_MO is not null 
            and
        pp.BranchCode is null 
            and 
        pp.Branch is null
            and 
        pp.V016Dispt is not null
            and 
        pp.V016 is not null
            and
        exists
        (
            select *
            from ISZL.T_DICT_PLAN_PM dpp
            where dpp.[Year] = pp.[Year]
                    and
                dpp.F003Mo = pp.CODE_MO
                    and
                dpp.V016Dispt = pp.V016
        )

    /*
     * Обработка создания новых записей TYPE = 1 (Insert)
     */
    INSERT INTO [ISZL].[T_DICT_PLAN_PM]
               ([DictPlanPmID]
               ,[January]
               ,[February]
               ,[March]
               ,[April]
               ,[May]
               ,[June]
               ,[July]
               ,[August]
               ,[September]
               ,[October]
               ,[November]
               ,[December]
               ,[PlanYear]
               ,[TUch]
               ,[TipUch]
               ,[F003Mo]
               ,[Branch]
               ,[V016Dispt]
               ,[Year])
    SELECT
               NEWID()--<DictPlanPmID, uniqueidentifier,>
              ,pp.January--<January, int,>
              ,pp.February--<February, int,>
              ,pp.March--<March, int,>
              ,pp.April--<April, int,>
              ,pp.May--<May, int,>
              ,pp.June--<June, int,>
              ,pp.July--<July, int,>
              ,pp.August--<August, int,>
              ,pp.September--<September, int,>
              ,pp.October--<October, int,>
              ,pp.November--<November, int,>
              ,pp.December--<December, int,>
              ,pp.PlanYear--<PlanYear, int,>
              ,pp.TUch--<TUch, varchar(255),>
              ,pp.TipUch--<TipUch, int,>
              ,pp.CODE_MO--<F003Mo, varchar(6),>
              ,pp.Branch--<Branch, uniqueidentifier,>
              ,pp.V016--<V016Dispt, uniqueidentifier,>
              ,pp.[Year]--<Year, int,>)
    from #PLAN_PM pp
    where pp.[TYPE] = 1
            and
        not exists (select * from #errors)

    /*
     * Обновление ранее залитых записей записей TYPE = 2 (Update)
     */
    update dpp
    set dpp.January = pp.January
        ,dpp.February = pp.February
        ,dpp.March = pp.March
        ,dpp.April = pp.April
        ,dpp.May = pp.May
        ,dpp.June = pp.June
        ,dpp.July = pp.July
        ,dpp.August = pp.August
        ,dpp.September = pp.September
        ,dpp.October = pp.October
        ,dpp.November = pp.November
        ,dpp.December = pp.December
        ,dpp.PlanYear = pp.PlanYear
        ,dpp.TUch = pp.TUch
        ,dpp.TipUch = pp.TipUch
    from #PLAN_PM pp
        join ISZL.T_DICT_PLAN_PM dpp on dpp.[Year] = pp.[Year]
                and
            dpp.F003Mo = pp.CODE_MO
                and
            (dpp.Branch = pp.Branch or dpp.Branch is null and pp.BranchCode is null and pp.Branch is null)
                and
            dpp.V016Dispt = pp.V016
    where pp.[TYPE] = 2
            and
        not exists (select * from #errors)

    /*
     * Удаление ранее залитых записей записей TYPE = 3 (Delete)
     */
    delete dpp
    from #PLAN_PM pp
        join ISZL.T_DICT_PLAN_PM dpp on dpp.[Year] = pp.[Year]
                and
            dpp.F003Mo = pp.CODE_MO
                and
            (dpp.Branch = pp.Branch or dpp.Branch is null and pp.BranchCode is null and pp.Branch is null)
                and
            dpp.V016Dispt = pp.V016
    where pp.[TYPE] = 3
            and
        not exists (select * from #errors)

    select 
        (case 
            when not exists (select * from #errors) then cast(1 as bit)
            else cast(0 as bit)
        end),
        (case 
            when not exists (select * from #errors) then null
            else
           '<?xml version="1.0" encoding="Windows-1251"?>' 
            + 
            (
                SELECT 
                    CODE as 'CODE'
                    ,[COMMENT] as 'COMMENT'
                FROM #errors
                FOR XML PATH('ERR'),ROOT('ROOT')
            )
        end),
        cast(null as varchar(max)) as lexem

    DROP TABLE #errors
    DROP TABLE #PLAN_PM

end

