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 PLAN_PM
    as
    select
         (xpath('/PLAN_PM/HEADER[1]/CODE_MO/text()', z))[1]::varchar(6) as CODE_MO
        ,(xpath('/PLAN_PM/HEADER[1]/TYPE/text()', z))[1]::varchar(11)::int as TYPE
        ,(xpath('/PLAN_PM/BODY[1]/Year/text()', z))[1]::varchar(11)::int as Year
        ,(xpath('/PLAN_PM/BODY[1]/V016Dispt/text()', z))[1]::varchar(3) as V016Dispt
        ,(xpath('/PLAN_PM/BODY[1]/Branch/text()', z))[1]::varchar(17) as BranchCode
        ,(xpath('/PLAN_PM/BODY[1]/TUch/text()', z))[1]::varchar(10) as TUch
        ,(xpath('/PLAN_PM/BODY[1]/TipUch/text()', z))[1]::varchar(1) as TipUch
        ,(xpath('/PLAN_PM/BODY[1]/PlanYear/text()', z))[1]::varchar(11)::int as PlanYear
        ,(xpath('/PLAN_PM/BODY[1]/January/text()', z))[1]::varchar(11)::int as January
        ,(xpath('/PLAN_PM/BODY[1]/February/text()', z))[1]::varchar(11)::int as February
        ,(xpath('/PLAN_PM/BODY[1]/March/text()', z))[1]::varchar(11)::int as March
        ,(xpath('/PLAN_PM/BODY[1]/April/text()', z))[1]::varchar(11)::int as April
        ,(xpath('/PLAN_PM/BODY[1]/May/text()', z))[1]::varchar(11)::int as May
        ,(xpath('/PLAN_PM/BODY[1]/June/text()', z))[1]::varchar(11)::int as June
        ,(xpath('/PLAN_PM/BODY[1]/July/text()', z))[1]::varchar(11)::int as July
        ,(xpath('/PLAN_PM/BODY[1]/August/text()', z))[1]::varchar(11)::int as August
        ,(xpath('/PLAN_PM/BODY[1]/September/text()', z))[1]::varchar(11)::int as September
        ,(xpath('/PLAN_PM/BODY[1]/October/text()', z))[1]::varchar(11)::int as October
        ,(xpath('/PLAN_PM/BODY[1]/November/text()', z))[1]::varchar(11)::int as November
        ,(xpath('/PLAN_PM/BODY[1]/December/text()', z))[1]::varchar(11)::int as December
    from
    (
        select unnest(xpath('*', x)) as z
    ) t;

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