DROP function if exists sp_HospReferralLoad_folder;
drop procedure if exists sp_HospReferralLoad_folder;

CREATE procedure sp_HospReferralLoad_folder
(
    x xml
,    "FileID" uuid default null::uuid
,    "Worker" uuid default null::uuid    
,	inout "Res" boolean default null::boolean
,	inout "Err" xml default null::xml
)
as $BODY$
begin
    Drop Table If Exists TmpZAP;
    Create Temp Table TmpZAP
    As
    select
        gen_random_uuid()                                                                     as ID
    ,    (xpath('//N_ZAP[1]/text()', b))[1]::text::int                                        as N_ZAP
    ,    (xpath('//TYPE[1]/text()', b))[1]::varchar(6)                                       as TYPE
    ,    (xpath('//MO_TO[1]/text()', b))[1]::varchar(6)                                         as MO_TO    
    ,    (xpath('//FILENAME[1]/text()', h))[1]::varchar(26)                                     as FILENAME
    ,    (xpath('//MO[1]/text()', h))[1]::varchar(6)                                         as LpuFrom
    ,    (xpath('//REFERRAL_NUMBER[1]/text()', b))[1]::varchar(16)                           as REFERRAL_NUMBER
    ,    to_timestamp((xpath('//REFERRAL_DATE[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')    as REFERRAL_DATE
    ,    (xpath('//HOSPITALISATION_TYPE[1]/text()', b))[1]::text::int                        as HOSPITALISATION_TYPE
    ,    (xpath('//BRANCH_FROM[1]/text()', b))[1]::varchar(16)                               as BRANCH_FROM
    ,    to_timestamp((xpath('//VMP_DATE[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')         as VMP_DATE
    ,    (xpath('//VMP_NUMBER[1]/text()', b))[1]::varchar(17)                                   as VMP_NUMBER
    ,    (xpath('//BRANCH_TO[1]/text()', b))[1]::varchar(16)                                   as BRANCH_TO
    ,    (xpath('//DIVISION_TO[1]/text()', b))[1]::varchar(16)                               as DIVISION_TO
    ,    (xpath('//V020_STRUCTURE_BED[1]/text()', b))[1]::text::int                          as V020_STRUCTURE_BED
    ,    (xpath('//MKB[1]/text()', b))[1]::varchar(8)                                           as MKB
    ,    to_timestamp((xpath('//PLANNED_DATE[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')     as PLANNED_DATE
    ,    (xpath('//USL_OK[1]/text()', b))[1]::text::int                                        as USL_OK
    ,    (xpath('//DOC_CODE[1]/text()', b))[1]::varchar(50)                                   as DOC_CODE
    ,    (xpath('//POLICY_TYPE[1]/text()', b))[1]::text::int                                   as POLICY_TYPE
    ,    (xpath('//POLIS_SERIAL[1]/text()', b))[1]::varchar(16)                               as POLIS_SERIAL
    ,    (xpath('//POLIS_NUMBER[1]/text()', b))[1]::varchar(16)                               as POLIS_NUMBER
    ,    (xpath('//SMO[1]/text()', b))[1]::varchar(5)                                           as SMO
    ,    (xpath('//LAST_NAME[1]/text()', b))[1]::varchar(32)                                   as LAST_NAME
    ,    (xpath('//FIRST_NAME[1]/text()', b))[1]::varchar(32)                                   as FIRST_NAME
    ,    (xpath('//FATHER_NAME[1]/text()', b))[1]::varchar(32)                               as FATHER_NAME
    ,    (xpath('//SEX[1]/text()', b))[1]::text::int                                            as SEX
    ,    to_timestamp((xpath('//BIRTHDAY[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')         as BIRTHDAY
    ,    (xpath('//PHONE[1]/text()', b))[1]::varchar(128)                                       as PHONE
    ,    (xpath('//ANOTHER_REGION[1]/text()', b))[1]::text::int                               as ANOTHER_REGION
    ,    (xpath('//HospitalizationReferralID[1]/text()', b))[1]::text::uuid                  as HospitalizationReferralID
    from
    (
        select unnest(xpath('/HOSPITALISATION_REFERRAL/HEADER', x)) as h, unnest(xpath('/HOSPITALISATION_REFERRAL/BODY', x)) as b
    ) t1;
    
    update TmpZAP t1
    set HospitalizationReferralID = 
    (
        select hr.HospitalizationReferralID 
        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
        limit 1
    );

    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors
    as
    select 7::int as Code, ('Неверный тип операции. N_ZAP(' || z.N_ZAP::text || ')')::varchar(250) as Comment
    from TmpZAP z
    where z.type <> 'Insert'
    union all
    select 1, 'Найден дубль направления на госпитализацию HospitalizationReferralID (' || z.HospitalizationReferralID::text || '). N_ZAP(' || z.N_ZAP::text || ')'
    from TmpZAP z
    where z.HospitalizationReferralID is not null;
    
    -- Insert
    if not exists(select* from TmpErrors)
    then
        --если нет ошибок, то вставка
        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 z.ID, z.REFERRAL_NUMBER, z.REFERRAL_DATE, z.BRANCH_FROM, z.HOSPITALISATION_TYPE, z.VMP_DATE, z.VMP_NUMBER, z.BRANCH_TO, z.DIVISION_TO, z.V020_STRUCTURE_BED
        , z.MKB, z.PLANNED_DATE, z.USL_OK, z.DOC_CODE, z.POLICY_TYPE, z.POLIS_SERIAL, z.POLIS_NUMBER, z.SMO, z.LAST_NAME, z.FIRST_NAME, z.FATHER_NAME, z.SEX, z.BIRTHDAY
        , z.PHONE, z.ANOTHER_REGION, now()::timestamp, null, null, 1, null, null, null, null, null, z.LpuFrom, null
        , null, MO_TO
        from TmpZAP z;

        if exists(select* from information_schema.columns c where c.table_schema = 'iszl' and c.table_name = 't_hospitalization_referral' and c.column_name='filename')
        then
            update ISZL.T_HOSPITALIZATION_REFERRAL hr
            set FILENAME = z.FILENAME
            from TmpZAP z
            where z.ID = hr.HospitalizationReferralID;
        end if;
    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
	    , 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);

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

	drop table if exists temp_table;
    DROP TABLE if exists TmpZAP;
    DROP TABLE if exists TmpErrors;

end;
$BODY$ LANGUAGE plpgsql;
