DROP FUNCTION if exists sp_AmountbedLoad(xml);
DROP FUNCTION if exists sp_AmountbedLoad(xml, uuid, uuid);
CREATE OR REPLACE FUNCTION sp_AmountbedLoad
(
    x xml default null::xml
)
RETURNS TABLE (Res boolean, Err text)
LANGUAGE plpgsql
AS $function$
declare 
    _MO varchar(6);
    _TYPE varchar(6);    
begin
    Drop Table If Exists TmpZGLV;
    Create Temp Table TmpZGLV
    As
    select
        (xpath('//MO[1]/text()', z))[1]::varchar(6)     as MO
    ,    (xpath('//TYPE[1]/text()', z))[1]::varchar(6)    as TYPE
    from
    (
        select unnest(xpath('/AMOUNT_BED//header', x)) as z
    ) t1;
    
    select 
        into _MO, _TYPE
        t.MO, t.TYPE
    from TmpZGLV t
    limit 1;
    
    Drop Table If Exists TmpZAP;
    Create Temp Table TmpZAP
    As
    select
        to_timestamp((xpath('//DATE_BEGIN[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')    as DATE_BEGIN
    ,    to_timestamp((xpath('//DATE_END[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')        as DATE_END
    ,    (xpath('//BR_CODE[1]/text()', z))[1]::varchar(16)                                     as BR_CODE
    ,    (xpath('//DIVISION[1]/text()', z))[1]::varchar(16)                                   as DIVISION
    ,    (xpath('//V020_STRUCTURE_BED[1]/text()', z))[1]::text::int                            as V020_STRUCTURE_BED
    ,    _MO                                                                                    as MO
    ,    null::uuid                                                                            as AmountBedID
    ,    null::uuid                                                                            as DivisionID
    ,    null::uuid                                                                            as BranchID
    from
    (
        select unnest(xpath('/AMOUNT_BED//body', x)) as z
    ) t1;
    
    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors
    As
    select 1::int as CODE, 'МО не найдена'::varchar(250) as COMMENT
    from TmpZGLV zglv
    where not exists(select* from iszl.t_f003 lm where lm.mcod = zglv.MO);
    
    -- поиск отделения и филиала
    update TmpZAP zap
    set AmountBedID = 
    (
        select ab.AmountBedID 
        from iszl.T_AMOUNT_BED ab
        inner join
            iszl.T_DIVISION div
            on div.DivisionID = ab.DivisionObj
        left join
            iszl.T_BRANCH br
            on div.Branch = br.BranchID 
        where ab.V020_STRUCTURE_BED = zap.V020_STRUCTURE_BED 
        and div.CODE = zap.DIVISION 
        and (div.LpuMain = _MO OR br.F003Mo = _MO)
        limit 1
    )
    ,    DivisionID = 
    (
        select div.DivisionID 
        from iszl.T_DIVISION div 
        left join
            iszl.T_BRANCH br
            on div.Branch = br.BranchID 
        where div.CODE = zap.DIVISION
        and (div.LpuMain = _MO OR br.F003Mo = _MO) 
        limit 1
    )
    ,    BranchID = 
    (
        select br.BranchID 
        from iszl.T_BRANCH br
        where br.BR_CODE = zap.BR_CODE 
        and br.F003Mo = _MO
        limit 1
    );

    insert into TmpErrors(Code, COMMENT)
    select 2, 'Не найдено подразделение (' || z.BR_CODE || ') Для МО (' || z.MO || ')'
    from TmpZAP z
    where z.BranchID is null
    and z.BR_CODE is not null
    union all
    select 3, 'Не найдено отделение (' || z.DIVISION || '). Для подразделение (' || BR_CODE || '). Для МО (' || MO || ')'
    from TmpZAP z
    where z.DivisionID is null
    and z.BranchID is not null;

    if _type not in ('Insert', 'Update', 'Delete')
    then
        insert into TmpErrors(CODE, COMMENT)
        select 6, 'Неверный тип операции.';
    end if;

    if _type = 'Insert'
    then
        -- удаляем, если есть
        delete from iszl.T_AMOUNT_BED t
        where exists(select* from TmpZAP zap where t.AmountBedID = zap.AmountBedID);

        -- вставка, если нет ошибок
        INSERT INTO ISZL.T_AMOUNT_BED(AmountBedID, DATE_BEGIN, DATE_END, BR_CODE, DIVISION
        , V020_STRUCTURE_BED, DivisionObj)
        select gen_random_uuid(), zap.DATE_BEGIN, zap.DATE_END, zap.BR_CODE, zap.DIVISION
        , zap.V020_STRUCTURE_BED, zap.DivisionID
        from TmpZAP zap
        where not exists(select* from TmpErrors);
    end if;

    if _type = 'Update'
    then
        insert into TmpErrors(Code, COMMENT)
        select 4, 'Запись не найдена в ранее загруженных списках'
        from TmpZAP zap
        where zap.AmountBedID is null;

        UPDATE ISZL.T_AMOUNT_BED t
           SET    DATE_BEGIN = zap.DATE_BEGIN
        ,    DATE_END = zap.DATE_END
        ,    BR_CODE = zap.BR_CODE
        ,    DIVISION = zap.DIVISION
        ,    V020_STRUCTURE_BED = zap.V020_STRUCTURE_BED
        ,    DivisionObj = zap.DivisionID
        from TmpZAP zap
        where t.AmountBedID = zap.AmountBedID
        and not exists(select* from TmpErrors);
    end if;

    if _type = 'Delete'
    then
        insert into TmpErrors(Code, COMMENT)
        select 5, 'Запись не найдена в ранее загруженных списках'
        from TmpZAP zap
        where zap.AmountBedID is null;

        delete from ISZL.T_AMOUNT_BED t
        WHERE exists(select* from TmpZAP zap where t.AmountBedID = zap.AmountBedID)
        and not exists(select* from TmpErrors);
    end if;
    
    return query
    select case when exists(select* from TmpErrors) then '0'::boolean else '1'::boolean end
    , case when exists(select* from TmpErrors) then '<?xml version="1.0" encoding="Windows-1251"?>
    ' || 
    (
        xmlelement
        (
            name "ROOT"
        ,    (
                select
                xmlagg
                (
                    xmlelement
                    (
                        name "ERR"
                    ,    xmlforest
                        (
                            te.CODE AS "CODE"
                        ,    te.COMMENT AS "COMMENT"
                        )
                    )
                )
                FROM TmpErrors te
            )
        )
    )::text end;


    DROP TABLE IF EXISTS TmpZGLV;
    DROP TABLE IF EXISTS TmpZAP;
    DROP TABLE IF EXISTS TmpErrors;

end;
$function$;
