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]
            ,T.c.value('./BODY[1]/Year[1]', 'int') as [Year]
            ,T.c.value('./BODY[1]/V016Dispt[1]', 'varchar(3)') as [V016Dispt]
            ,T.c.value('./BODY[1]/Branch[1]', 'varchar(17)') as [BranchCode]
            ,T.c.value('./BODY[1]/TUch[1]', 'varchar(10)') as [TUch]
            ,T.c.value('./BODY[1]/TipUch[1]', 'varchar(1)') as [TipUch]
            ,T.c.value('./BODY[1]/PlanYear[1]', 'int') as [PlanYear]
            ,T.c.value('./BODY[1]/January[1]', 'int') as [January]
            ,T.c.value('./BODY[1]/February[1]', 'int') as [February]
            ,T.c.value('./BODY[1]/March[1]', 'int') as [March]
            ,T.c.value('./BODY[1]/April[1]', 'int') as [April]
            ,T.c.value('./BODY[1]/May[1]', 'int') as [May]
            ,T.c.value('./BODY[1]/June[1]', 'int') as [June]
            ,T.c.value('./BODY[1]/July[1]', 'int') as [July]
            ,T.c.value('./BODY[1]/August[1]', 'int') as [August]
            ,T.c.value('./BODY[1]/September[1]', 'int') as [September]
            ,T.c.value('./BODY[1]/October[1]', 'int') as [October]
            ,T.c.value('./BODY[1]/November[1]', 'int') as [November]
            ,T.c.value('./BODY[1]/December[1]', 'int') as [December]
        into #PLAN_PM
    from @x.nodes('PLAN_PM') T(c)

    /*
     * Стыкуем справочники
     */
    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

