DROP FUNCTION if exists sp_MotionInHospitalLoad(xml);
DROP FUNCTION if exists sp_MotionInHospitalLoad(xml, uuid, uuid);
CREATE OR REPLACE FUNCTION sp_MotionInHospitalLoad
(
    x xml default null::xml
)
RETURNS TABLE (Res boolean, Err text)
LANGUAGE plpgsql
AS $function$
declare 
    _MO varchar(6);
    _TYPE varchar(6);
    _exists uuid;
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('/MOTION_IN_HOSPITAL//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('//HOSPITALISATION_DATE[1]/text()', z))[1]::text, 'YYYY-MM-DD')    as HOSPITALISATION_DATE
    ,    (xpath('//MED_CARD_NUMBER[1]/text()', z))[1]::varchar(50)                           as MED_CARD_NUMBER
    ,    (xpath('//BRANCH[1]/text()', z))[1]::varchar(16)                                       as BRANCH
    ,    (xpath('//DIVISION[1]/text()', z))[1]::varchar(16)                                   as DIVISION
    ,    (xpath('//V020_STRUCTURE_BED[1]/text()', z))[1]::text::int                            as V020_STRUCTURE_BED
    ,    to_timestamp((xpath('//DATE_IN[1]/text()', z))[1]::text, 'YYYY-MM-DD')                as DATE_IN
    ,    to_timestamp((xpath('//DATE_OUT[1]/text()', z))[1]::text, 'YYYY-MM-DD')                as DATE_OUT
    ,    (xpath('//TYPE_MOT[1]/text()', z))[1]::text::int                                    as TYPE_MOT
    ,    (xpath('//USL_OK[1]/text()', z))[1]::text::int                                        as USL_OK
    ,    (xpath('//OUTCOME[1]/text()', z))[1]::text::int                                        as OUTCOME
    ,    _MO                                                                                    as MO
    ,    (xpath('//hospId[1]/text()', z))[1]::uuid                                            as hospId
    from
    (
        select unnest(xpath('/MOTION_IN_HOSPITAL//body', x)) as z
    ) t1;
    
    update TmpZAP t1
    set hospId = 
    (
        select hosp.HospitalizationID from ISZL.T_HOSPITALIZATION hosp
        where hosp.MED_CARD_NUMBER = t1.MED_CARD_NUMBER
        and hosp.HOSPITALIZATION_DATE = t1.HOSPITALISATION_DATE
        and hosp.MO = t1.MO
        limit 1
    );
    
    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors
    as
    select 1::int as CODE, 'Не найдена соответствующая госпитализация'::varchar(250) as COMMENT
    from TmpZAP zap
    where zap.hospId is null
    union all
    select 2, 'Поле DATE_OUT обязательно для заполнения(TYPE_MOT = 2)'
    from TmpZAP zap
    where zap.TYPE_MOT = 2
    and zap.DATE_OUT is null
    union all
    select 3, 'Поле OUTCOME обязательно для заполнения(TYPE_MOT = 2)'
    from TmpZAP zap
    where zap.TYPE_MOT = 2 and zap.OUTCOME is null
    union all
    select 6, 'Неверный тип операции.'
    where _type not in ('Insert', 'Update', 'Delete');
    
    if _type = 'Insert'
    then

    _exists := 
        (
            select hosp.MotionInHospitalID from ISZL.T_MOTION_IN_HOSPITAL hosp
            inner join 
                TmpZAP zap
                on zap.HOSPITALISATION_DATE = hosp.HOSPITALIZATION_DATE
                and zap.MED_CARD_NUMBER = hosp.MED_CARD_NUMBER
                and zap.hospId = hosp.Hospitalization 
                limit 1
        );
        
        if _exists is not null
        then
            insert into TmpErrors(Code, COMMENT)
            select 4, 'Запись уже была добавлена ранее';
        else
            --если нет ошибок, то вставка
            insert into ISZL.T_MOTION_IN_HOSPITAL(MotionInHospitalID, HOSPITALIZATION_DATE, MED_CARD_NUMBER, MO
            , DIVISION, V020_STRUCTURE_BED, DATE_IN, Hospitalization, BRANCH, DATE_OUT, USL_OK, OUTCOME)
            select gen_random_uuid(), zap.HOSPITALISATION_DATE, zap.MED_CARD_NUMBER, zap.MO, zap.DIVISION
            , zap.V020_STRUCTURE_BED, zap.DATE_IN, zap.hospId, zap.BRANCH, zap.DATE_OUT, zap.USL_OK, zap.OUTCOME
            from TmpZAP zap
            where not exists(select* from TmpErrors);
        end if;
    end if;

    if _type = 'Update'
    then    
        --проверка
        _exists := 
        (
            select hosp.MotionInHospitalID from ISZL.T_MOTION_IN_HOSPITAL hosp
            inner join 
                TmpZAP zap
                on zap.HOSPITALISATION_DATE = hosp.HOSPITALIZATION_DATE
                and zap.MED_CARD_NUMBER = hosp.MED_CARD_NUMBER
                and zap.hospId = hosp.Hospitalization 
        );
        
        if _exists is null
        then
            insert into TmpErrors(Code, COMMENT)
            select 4, 'Не найдены соответствующие сведения о выбытии пациента';
        else
            update ISZL.T_MOTION_IN_HOSPITAL mh
            set  HOSPITALIZATION_DATE = zap.HOSPITALISATION_DATE
            ,    MED_CARD_NUMBER = zap.MED_CARD_NUMBER
            ,    MO = zap.MO
            ,    DIVISION = zap.DIVISION
            ,    V020_STRUCTURE_BED = zap.V020_STRUCTURE_BED
            ,    DATE_IN = zap.DATE_IN
            ,    BRANCH = zap.BRANCH
            ,    DATE_OUT = zap.DATE_OUT
            ,    USL_OK = zap.USL_OK
            from TmpZAP zap
            where mh.MotionInHospitalID = _exists
            and not exists(select* from TmpErrors);
        end if;
    end if;

    if _type = 'Delete'
    then
        _exists :=
        (
            select hosp.MotionInHospitalID from ISZL.T_MOTION_IN_HOSPITAL hosp
            inner join 
                TmpZAP zap
                on zap.HOSPITALISATION_DATE = hosp.HOSPITALIZATION_DATE
                and zap.MED_CARD_NUMBER = hosp.MED_CARD_NUMBER
                and zap.hospId = hosp.Hospitalization
        );

        if _exists is null
        then
            insert into TmpErrors(Code, COMMENT)
            select 5, 'Не найдены соответствующие сведения о выбытии пациента';
        else
            delete from ISZL.T_MOTION_IN_HOSPITAL mh
            where mh.MotionInHospitalID = _exists
            and not exists(select* from TmpErrors);
        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;
    DROP TABLE if exists TmpErrors;
end;
$function$;
