DROP FUNCTION if exists sp_HospReferralLoad(xml);
DROP FUNCTION if exists sp_HospReferralLoad(xml, uuid, uuid);
CREATE OR REPLACE FUNCTION sp_HospReferralLoad
(
    x xml default null::xml
)
RETURNS TABLE (Res boolean, Err text, lexem text)
LANGUAGE plpgsql
AS $function$
declare 
    _MO varchar(6);
    _TYPE varchar(6);
    _MO_TO 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('/HOSPITALISATION_REFERRAL/header', x)) as z
    ) t1;
    
    select 
        into _MO, _TYPE, _MO_TO
        t.MO, t.TYPE, t.MO_TO
    from TmpZGLV t
    limit 1;
    
    Drop Table If Exists TmpZAP;
    Create Temp Table TmpZAP
    As
    select
         gen_random_uuid()                                                                  as ID
    ,    _MO                                                                                as LpuFrom
    ,    (xpath('//REFERRAL_NUMBER[1]/text()', z))[1]::varchar(16)                       as REFERRAL_NUMBER
    ,    to_timestamp((xpath('//REFERRAL_DATE[1]/text()', z))[1]::text, 'YYYY-MM-DD')    as REFERRAL_DATE
    ,    (xpath('//HOSPITALISATION_TYPE[1]/text()', z))[1]::text::int                    as HOSPITALISATION_TYPE
    ,    (xpath('//BRANCH_FROM[1]/text()', z))[1]::varchar(16)                           as BRANCH_FROM
    ,    to_timestamp((xpath('//VMP_DATE[1]/text()', z))[1]::text, 'YYYY-MM-DD')            as VMP_DATE
    ,    (xpath('//VMP_NUMBER[1]/text()', z))[1]::text::int                                as VMP_NUMBER
    ,    (xpath('//BRANCH_TO[1]/text()', z))[1]::varchar(16)                               as BRANCH_TO
    ,    (xpath('//DIVISION_TO[1]/text()', z))[1]::varchar(16)                           as DIVISION_TO
    ,    (xpath('//V020_STRUCTURE_BED[1]/text()', z))[1]::text::int                        as V020_STRUCTURE_BED
    ,    (xpath('//MKB[1]/text()', z))[1]::varchar(8)                                       as MKB
    ,    to_timestamp((xpath('//PLANNED_DATE[1]/text()', z))[1]::text, 'YYYY-MM-DD')        as PLANNED_DATE
    ,    (xpath('//USL_OK[1]/text()', z))[1]::text::int                                    as USL_OK
    ,    (xpath('//DOC_CODE[1]/text()', z))[1]::text::int                                as DOC_CODE
    ,    (xpath('//POLICY_TYPE[1]/text()', z))[1]::text::int                                as POLICY_TYPE
    ,    (xpath('//POLIS_SERIAL[1]/text()', z))[1]::varchar(16)                           as POLIS_SERIAL
    ,    (xpath('//POLIS_NUMBER[1]/text()', z))[1]::varchar(16)                           as POLIS_NUMBER
    ,    (xpath('//SMO[1]/text()', z))[1]::varchar(5)                                       as SMO
    ,    (xpath('//LAST_NAME[1]/text()', z))[1]::varchar(32)                               as LAST_NAME
    ,    (xpath('//FIRST_NAME[1]/text()', z))[1]::varchar(32)                               as FIRST_NAME
    ,    (xpath('//FATHER_NAME[1]/text()', z))[1]::varchar(32)                           as FATHER_NAME    
    ,    (xpath('//SEX[1]/text()', z))[1]::text::int                                        as SEX
    ,    to_timestamp((xpath('//BIRTHDAY[1]/text()', z))[1]::text, 'YYYY-MM-DD')            as BIRTHDAY
    ,    (xpath('//PHONE[1]/text()', z))[1]::varchar(128)                                   as PHONE    
    ,    (xpath('//ANOTHER_REGION[1]/text()', z))[1]::text::int                            as ANOTHER_REGION
    from
    (
        select unnest(xpath('/HOSPITALISATION_REFERRAL/body', x)) as z
    ) t1;
    
    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors
    as
    select 7::int as CODE, 'Неверный тип операции.'::varchar(250) as COMMENT
    where _type <> 'Insert'
    union all
    select 1, 'Найден дубль направления на госпитализацию'
    from TmpZAP t1
    where exists 
    (
        select* from ISZL.T_HOSPITALIZATION_REFERRAL hr
        where hr.LpuMainFrom = t1.LpuFrom
        and hr.REFERRAL_NUMBER = t1.REFERRAL_NUMBER
        and hr.REFERRAL_DATE = t1.REFERRAL_DATE
    );

    --если нет ошибок, то вставка
    insert into ISZL.T_HOSPITALIZATION_REFERRAL(HospitalizationReferralID, REFERRAL_NUMBER, REFERRAL_DATE
    , BRANCH_FROM, HOSPITALIZATION_TYPE, VMP_DATE, VMP_NUMBER, BRANCH_TO, DIVISION_TO, V020_STRUCTURE_BED
    , MKB, PLANNED_DATE, USL_OK, DOC_CODE, POLICY_TYPE, POLIS_SERIAL, POLIS_NUMBER, SMO, LAST_NAME, FIRST_NAME
    , FATHER_NAME, SEX, BIRTHDAY, PHONE, ANOTHER_REGION, CreateDate
    , Hospitalization, Users, CreatePlace, HospitalisationDate, Diagnosis, HospTransDate
    , TransferReasonDescr, TransferUsers, LpuMainFrom, TransferReason,  Post, LpuMainTo)
    select ID, zap.REFERRAL_NUMBER, zap.REFERRAL_DATE, zap.BRANCH_FROM, zap.HOSPITALISATION_TYPE
    , zap.VMP_DATE, zap.VMP_NUMBER, zap.BRANCH_TO, zap.DIVISION_TO, zap.V020_STRUCTURE_BED, zap.MKB
    , zap.PLANNED_DATE, zap.USL_OK, zap.DOC_CODE, zap.POLICY_TYPE, zap.POLIS_SERIAL, zap.POLIS_NUMBER, zap.SMO
    , zap.LAST_NAME, zap.FIRST_NAME, zap.FATHER_NAME, zap.SEX, zap.BIRTHDAY, zap.PHONE, zap.ANOTHER_REGION
    , now()::timestamp, null, null, 2, null, null
    , null, null, null, _MO, null
    , null, _MO_TO
    from TmpZAP zap
    where not exists(select* from TmpErrors);

    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
    , null::text as lexem
    union all
    select null, null, 'new Process("ProvideInsuredProcess", new Apartment().GetInstance("ISZL.HOSPITALIZATION_REFERRAL", Cast("' || r.ID::text || '", "Guid"))).$Run()'
    from TmpZAP r
    where not exists(select* from TmpErrors);

    DROP TABLE if exists TmpZGLV;
    DROP TABLE if exists TmpZAP;
    DROP TABLE if exists TmpErrors;
end;
$function$;
