DROP FUNCTION if exists sp_LoadPlanPm;
drop procedure if exists sp_LoadPlanPm;

CREATE procedure sp_LoadPlanPm
(
    x xml
    ,"FileID" uuid default null::uuid
    ,"Worker" uuid default null::uuid    
    ,inout "Res" boolean default null::boolean
    ,inout "Err" xml default null::xml
)
LANGUAGE plpgsql
AS $procedure$
declare 
begin

    /*
     * Парсинг исходной xml
     */
     create temp table ZGLV
     as
     select 
        CODE_MO::varchar(6),
        TYPE::int
     from xmltable('//HEADER' passing x
        columns 
            CODE_MO text path 'CODE_MO',
            TYPE text path 'TYPE'
     );

    create temp table PLAN_PM
    as
    select
         zg.CODE_MO as CODE_MO
        ,zg.TYPE as TYPE
        ,t.Year::int
        ,t.V016Dispt::varchar(3)
        ,t.Branch::varchar(17) as BranchCode
        ,t.TUch::varchar(10)
        ,t.TipUch::varchar(1)
        ,t.PlanYear::int
        ,t.January::int
        ,t.February::int
        ,t.March::int
        ,t.April::int
        ,t.May::int
        ,t.June::int
        ,t.July::int
        ,t.August::int
        ,t.September::int
        ,t.October::int
        ,t.November::int
        ,t.December::int
    from xmltable('//BODY' passing x
        columns 
            Year text path 'Year',
            V016Dispt text path 'V016Dispt',
            Branch text path 'Branch',
            TUch text path 'TUch',
            TipUch text path 'TipUch',
            PlanYear text path 'PlanYear',
            January text path 'January',
            February text path 'February',
            March text path 'March',
            April text path 'April',
            May text path 'May',
            June text path 'June',
            July text path 'July',
            August text path 'August',
            September text path 'September',
            October text path 'October',
            November text path 'November',
            December text path 'December'
    ) t
    join ZGLV zg on zg.CODE_MO is not null and zg.TYPE is not null;

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

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

    /*
     * Проверки на всякую лажу
     */
    create temp table errors
    as
    select 1 as CODE, cast('Код МО "' || COALESCE(pp.CODE_MO, '<null>') || '" не найден в справочнике F003' as varchar) as "comment"
    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 || '" не найдено в МО с кодом "' || COALESCE(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
               gen_random_uuid()--<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 ISZL.T_DICT_PLAN_PM dpp
    set January = pp.January
        ,February = pp.February
        ,March = pp.March
        ,April = pp.April
        ,May = pp.May
        ,June = pp.June
        ,July = pp.July
        ,August = pp.August
        ,September = pp.September
        ,October = pp.October
        ,November = pp.November
        ,December = pp.December
        ,PlanYear = pp.PlanYear
        ,TUch = pp.TUch
        ,TipUch = pp.TipUch
    from PLAN_PM pp
    where 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
            and
        pp.TYPE = 2
            and
        not exists (select * from errors);

    /*
     * Удаление ранее залитых записей записей TYPE = 3 (Delete)
     */
    delete from ISZL.T_DICT_PLAN_PM dpp
    using PLAN_PM pp
    where 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
            and
        pp.TYPE = 3
            and
        not exists (select * from errors);


    select 
        (case 
            when not exists (select * from errors) then true
            else false
        end)
        into "Res";

    select 
        (case 
            when not exists (select * from errors) then null
            else '<?xml version="1.0" encoding="Windows-1251"?>' 
            || 
            (
                xmlelement
                (
                    name "ROOT",
                    (
                        select xmlagg
                        (
                            xmlelement
                            (
                                name "ERR",
                                xmlforest
                                (
                                    e.CODE AS "CODE",
                                    e."comment" AS "COMMENT"
                                )
                            )
                        )
                        FROM errors e
                    )
                )
            )::text
        end)
        into "Err";

    DROP TABLE PLAN_PM;
    DROP TABLE errors;

end;
$procedure$;
