DROP FUNCTION if exists sp_CancelHospitalisationReferralLoad(xml);
DROP FUNCTION if exists sp_CancelHospitalisationReferralLoad(xml, uuid, uuid);
CREATE OR REPLACE FUNCTION sp_CancelHospitalisationReferralLoad
(
    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
    ,    (xpath('//MO_TO[1]/text()', z))[1]::varchar(6)    as MO_TO
    from
    (
        select unnest(xpath('/CANCEL_HOSPITALISATION_REFERRAL//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
        gen_random_uuid()                                                            as CANCELHOSP_ID
    ,    (xpath('//REFERRAL_NUMBER[1]/text()', z))[1]::varchar(21)                   as REFERRAL_NUMBER
    ,    to_timestamp((xpath('//DATE[1]/text()', z))[1]::text, 'YYYY-MM-DD')            as DATE
    ,    (xpath('//REFERRAL_MO[1]/text()', z))[1]::varchar(6)                           as REFERRAL_MO
    ,    (xpath('//BRANCH[1]/text()', z))[1]::varchar(16)                            as BRANCH
    ,    (xpath('//REASON[1]/text()', z))[1]::text::int                                as REASON
    ,    (xpath('//CANCEL_SOURSE[1]/text()', z))[1]::text::int                        as CANCEL_SOURSE
    ,    (xpath('//CODE_ORG[1]/text()', z))[1]::text::int                            as CODE_ORG    
    ,    to_timestamp((xpath('//DATE_CANCEL[1]/text()', z))[1]::text, 'YYYY-MM-DD')    as DATE_CANCEL
    ,    (xpath('//CANCEL_DESCRIPTION[1]/text()', z))[1]::varchar(4096)                as CANCEL_DESCRIPTION
    ,    (xpath('//HospRefIdFromDB[1]/text()', z))[1]::text::uuid                    as HospRefIdFromDB --заглушка
    from
    (
        select unnest(xpath('/CANCEL_HOSPITALISATION_REFERRAL//body', x)) as z
    ) t1;
    
    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors(CODE int, COMMENT varchar(250));
    
    update TmpZAP t1
    set HospRefIdFromDB =
    (
        select hosp.HospitalizationReferralID
        from ISZL.T_HOSPITALIZATION_REFERRAL hosp
        where hosp.REFERRAL_DATE = t1.DATE 
        and hosp.REFERRAL_NUMBER = t1.REFERRAL_NUMBER 
        and hosp.LpuMainFrom = t1.REFERRAL_MO
    );
    
    if _type not in ('Insert', 'Delete')
    then
        insert into TmpErrors(CODE, COMMENT)
        select 4, 'Неверный тип операции.';
    end if;
    
    if _type = 'Insert'
    then
        insert into TmpErrors(CODE, COMMENT)
        select 1, 'Не найдено соответствующее направление на госпитализацию.'
        from TmpZAP
        where HospRefIdFromDB is null;
        
        insert into TmpErrors(CODE, COMMENT)
        select 2, 'Отмена госпитализации уже существует.'
        from TmpZAP zap
        inner join ISZL.T_CANCEL_HOSPITALIZATION_REFERRAL cancel on cancel.HospitalizationReferral = zap.HospRefIdFromDB
        where zap.HospRefIdFromDB is not null;

        if not exists(select* from TmpErrors)
        then
            insert into ISZL.T_CANCEL_HOSPITALIZATION_REFERRAL(CancelHospitalizationOrReferralID, REFERRAL_NUMBER
            , DATE, REFERRAL_MO, REASON, CANCEL_SOURCE, CODE_ORG, DATE_CANCEL, CreatePlace, BRANCH
            , CANCEL_DESCRIPTION, BranchObj, HospitalizationReferral)
            select zap.CANCELHOSP_ID, zap.REFERRAL_NUMBER, zap.DATE, zap.REFERRAL_MO, zap.REASON
            , zap.CANCEL_SOURSE, zap.CODE_ORG, zap.DATE_CANCEL, 2, zap.BRANCH
            , zap.CANCEL_DESCRIPTION, branch.BranchID, zap.HospRefIdFromDB
            from TmpZAP zap
            left join
                ISZL.T_BRANCH branch
                on branch.BR_CODE = zap.BRANCH;
        end if;
    end if;

    if _type = 'Delete'
    then
        insert into TmpErrors(CODE, COMMENT)
        select 3, 'Не найдено соответствующее направление на госпитализацию.'
        from TmpZAP zap
        where zap.HospRefIdFromDB is null;

        if not exists(select* from TmpErrors)
        then
            delete from ISZL.T_CANCEL_HOSPITALIZATION_REFERRAL t1
            where exists
            (
                select* from TmpZAP zap 
                where t1.HospitalizationReferral = zap.HospRefIdFromDB
                and t1.REFERRAL_NUMBER = zap.REFERRAL_NUMBER 
                and t1.DATE = zap.DATE
                and t1.REFERRAL_MO = zap.REFERRAL_MO
                and t1.DATE_CANCEL = zap.DATE_CANCEL
            );
        end if;
    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;        
end;
$function$;
