DROP FUNCTION if exists sp_CancelHospitalisationLoad;
drop procedure if exists sp_CancelHospitalisationLoad;

CREATE procedure sp_CancelHospitalisationLoad
(
    x xml default null::xml
,	inout "Res" boolean default null::boolean
,	inout "Err" xml default null::xml
)
LANGUAGE plpgsql
AS $procedure$
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('/CANCEL_HOSPITALISATION//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 CANHOSP_ID
    ,    to_timestamp((xpath('//DATE[1]/text()', z))[1]::text, 'YYYY-MM-DD')            as DATE
    ,    (xpath('//MED_CARD_NUMBER[1]/text()', z))[1]::varchar(50)                   as MED_CARD_NUMBER
    ,    (xpath('//HOSPITALISATION_DIVISION[1]/text()', z))[1]::varchar(16)            as HOSPITALISATION_DIVISION
    ,    (xpath('//BRANCH[1]/text()', z))[1]::varchar(17)                            as BRANCH
    ,    (xpath('//REASON[1]/text()', z))[1]::text::int                                as REASON
    ,    (xpath('//CANCEL_SOURSE[1]/text()', z))[1]::text::int                        as CANCEL_SOURSE
    ,    to_timestamp((xpath('//DATE_CANCEL[1]/text()', z))[1]::text, 'YYYY-MM-DD')    as DATE_CANCEL
    ,    (xpath('//CODE_ORG[1]/text()', z))[1]::text::int                            as CODE_ORG
    ,    (xpath('//CANCEL_DESCRIPTION[1]/text()', z))[1]::varchar(4096)                as CANCEL_DESCRIPTION
    ,    (xpath('//HospIdFromDB[1]/text()', z))[1]::text::uuid                        as HospIdFromDB --заглушка
    from
    (
        select unnest(xpath('/CANCEL_HOSPITALISATION//body', x)) as z
    ) t1;
    
    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors(CODE int, COMMENT varchar(250));
    
    if _type = 'Insert'
    then
        update TmpZAP t1
        set HospIdFromDB =
        (
            select hosp.HospitalizationID
            from ISZL.T_HOSPITALIZATION hosp
            where hosp.HOSPITALIZATION_DATE = t1.DATE 
            and hosp.MED_CARD_NUMBER = t1.MED_CARD_NUMBER 
            and hosp.DIVISION = t1.HOSPITALISATION_DIVISION
        );

        insert into TmpErrors(CODE, COMMENT)
        select 1, 'Не найдена соответствующая госпитализация.'
        from TmpZAP z
        where z.HospIdFromDB is null;
        
        insert into TmpErrors(CODE, COMMENT)
        select 2, 'Отмена госпитализации уже существует.'
        from TmpZAP zap
        inner join ISZL.T_CANCEL_HOSPITALIZATION cancel on cancel.Hospitalization = zap.HospIdFromDB
        where zap.HospIdFromDB is not null;

        if not exists(select* from TmpErrors)
        then
            insert into ISZL.T_CANCEL_HOSPITALIZATION(CancelHospitalizationID, DATE, MED_CARD_NUMBER
            , HOSPITALIZATION_DIVISION, BRANCH, REASON, CANCEL_SOURCE, DATE_CANCEL, CODE_ORG, CANCEL_DESCRIPTION
            , DivisionObj, BranchObj, CreatePlace, Hospitalization)
            select zap.CANHOSP_ID, zap.DATE, zap.MED_CARD_NUMBER, zap.HOSPITALISATION_DIVISION, zap.BRANCH
            , zap.REASON, zap.CANCEL_SOURSE, zap.DATE_CANCEL, zap.CODE_ORG, zap.CANCEL_DESCRIPTION, div.DivisionID
            , br.BranchID, 2, zap.HospIdFromDB
            from TmpZAP zap
        left join
            ISZL.T_BRANCH br
            on br.BR_CODE = z.BRANCH and br.f003mo = _MO
        left join
            ISZL.T_DIVISION div
            on div.CODE = z.HOSPITALISATION_DIVISION and (div.lpumain = zap.MO or br.branchid  = div.branch);
        else
            insert into TmpErrors(CODE, COMMENT)
            select 3, 'Неверный тип операции.';
        end if;
    else
        insert into TmpErrors(CODE, COMMENT)
        select 3, 'Неверный тип операции.';
    end if;

   	create temp table temp_table(Res boolean,Err text);
   	insert into temp_table(Res,Err)
	    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;

   	select Res from temp_table into "Res";
	select Err from temp_table into "Err";

	drop table if exists temp_table;
    DROP TABLE if exists TmpZGLV;
    DROP TABLE if exists TmpZAP;        
end;
$procedure$;
