DROP FUNCTION if exists sp_HospitalisationLoad_folder;
drop procedure if exists sp_HospitalisationLoad_folder;

create procedure sp_HospitalisationLoad_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 HOSP_ID
    ,    (xpath('//N_ZAP[1]/text()', b))[1]::text::int                                                as N_ZAP
    ,    (xpath('//FILENAME[1]/text()', h))[1]::varchar(26)                                             as FILENAME
    ,    (xpath('//MO[1]/text()', h))[1]::varchar(6)                                                 as HMO
    ,    (xpath('//MO[1]/text()', b))[1]::varchar(6)                                                 as MO        
    ,    (xpath('//TYPE[1]/text()', b))[1]::varchar(6)                                               as TYPE
    ,    (xpath('//FORM_MEDICAL_CARE[1]/text()', b))[1]::text::int                                      as FORM_MEDICAL_CARE
    ,    (xpath('//REFERRAL_NUMBER[1]/text()', b))[1]::varchar(21)                                   as REFERRAL_NUMBER
    ,    to_timestamp((xpath('//REFERRAL_DATE[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')            as REFERRAL_DATE
    ,    (xpath('//REFERRAL_MO[1]/text()', b))[1]::varchar(6)                                         as REFERRAL_MO        
    ,    (xpath('//BRANCH[1]/text()', b))[1]::varchar(9)                                               as BRANCH
    ,    (xpath('//DIVISION[1]/text()', b))[1]::varchar(12)                                           as DIVISION
    ,    to_timestamp((xpath('//HOSPITALISATION_DATE[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')    as HOSPITALISATION_DATE
    ,    to_timestamp((xpath('//HOSPITALISATION_TIME[1]/text()', b))[1]::varchar(10), 'DD.MM.YYYY HH:MM:SS.MS')    as HOSPITALISATION_TIME
    ,    (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(40)                                   as LAST_NAME
    ,    (xpath('//FIRST_NAME[1]/text()', b))[1]::varchar(40)                                   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('//USL_OK[1]/text()', b))[1]::text::int                                        as USL_OK
    ,    (xpath('//V020_STRUCTURE_BED[1]/text()', b))[1]::text::int                          as V020_STRUCTURE_BED
    ,    (xpath('//MED_CARD_NUMBER[1]/text()', b))[1]::varchar(50)                           as MED_CARD_NUMBER
    ,    (xpath('//MKB[1]/text()', b))[1]::varchar(8)                                           as MKB
    ,    to_timestamp((xpath('//VMP_DATE[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')         as VMP_DATE
    ,    (xpath('//VMP_NUMBER[1]/text()', b))[1]::text                                       as VMP_NUMBER
    ,    (xpath('//VID_VMP[1]/text()', b))[1]::varchar(12)                                   as VID_VMP
    ,    (xpath('//METOD_VMP[1]/text()', b))[1]::text::int                                    as METOD_VMP    
    ,    (xpath('//HospReferralId[1]/text()', b))[1]::varchar(36)                              as HospReferralId --заглушка
    ,    (xpath('//HospIdFromDB[1]/text()', b))[1]::varchar(36)                              as HospIdFromDB --заглушка
    ,    (xpath('//ErrorString[1]/text()', b))[1]::text                                      as ErrorString --заглушка
    ,    (xpath('//MkbId[1]/text()', b))[1]::varchar(36)                                  as MkbId --заглушка    
    from
    (
        select unnest(xpath('/HOSPITALISATION/HEADER', x)) as h, unnest(xpath('/HOSPITALISATION/BODY', x)) as b
    ) t1;
    
    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors
    as
    select 14::int as Code, ('Неверный тип операции. N_ZAP(' || z.N_ZAP::text || ')')::varchar(250) as Comment
    from TmpZAP z
    where z.type <> 'Insert';


    update TmpZAP
    set MkbId = (select mkb.MkbId from ISZL.T_MKB mkb where  mkb.MCBCode = MKB and mkb.EndCode is null order by TreeLevel desc limit 1);

    insert into TmpErrors(Code, Comment)
    select 0, 'Код мкб ('|| z.MKB::text ||') не найден в справочнике МКБ'
    from TmpZAP z
    where z.MkbId is null and z.type <> 'Delete'; 

    update TmpZAP z
    set HospIdFromDB = hosp.HospitalizationID
    from ISZL.T_HOSPITALIZATION hosp 
    where hosp.FORM_MEDICAL_CARE = z.FORM_MEDICAL_CARE
        and hosp.HOSPITALIZATION_DATE = z.HOSPITALISATION_DATE
        and hosp.MED_CARD_NUMBER = z.MED_CARD_NUMBER
        and hosp.MO = z.MO;
    
    --Insert
    Update TmpZAP z
    set HospReferralId = 
    (
        select ref.HospitalizationReferralID
        from ISZL.T_HOSPITALIZATION_REFERRAL ref 
        where ref.REFERRAL_DATE = z.REFERRAL_DATE
            and (ref.REFERRAL_NUMBER = z.REFERRAL_NUMBER OR (ref.RegRefNumber is not null and ref.RegRefNumber = z.REFERRAL_NUMBER))
            and ref.LpuMainFrom = z.REFERRAL_MO
        limit 1
    )
    where z.FORM_MEDICAL_CARE = 3 and z.TYPE = 'Insert';
                  
    insert into TmpErrors(Code, Comment)
    select 2, 'Ошибка. Госпиализация уже существет. N_ZAP(' || z.N_ZAP::text || ')'
    from TmpZAP z
    where z.HospIdFromDB is not null and (z.TYPE = 'Insert' or (z.TYPE = 'Update' and z.HospIdFromDB is null ))
    union all
    select 4, 'Не найдено направление на госпитализацию. N_ZAP(' || z.N_ZAP::text || ')'
    from TmpZAP z
    where z.FORM_MEDICAL_CARE = 3 and z.HospReferralId is null AND (z.TYPE = 'Insert'  or (z.TYPE = 'Update' and z.HospIdFromDB is null ))
    union all
    select 5, 'Не указан номер направления. N_ZAP(' || z.N_ZAP::text || ')'
    from TmpZAP z
    where z.FORM_MEDICAL_CARE = 3 and z.REFERRAL_NUMBER is null AND (z.TYPE = 'Insert'  or (z.TYPE = 'Update' and z.HospIdFromDB is null ))
    union all
    select 6, 'Не указана дата направления. N_ZAP(' || z.N_ZAP::text || ')'
    from TmpZAP z
    where z.FORM_MEDICAL_CARE = 3 and z.REFERRAL_DATE is null AND (z.TYPE = 'Insert'  or (z.TYPE = 'Update' and z.HospIdFromDB is null ))
    union all
    select 7, 'Не указана МО, направившая на госпитализацию. N_ZAP(' || z.N_ZAP::text || ')'
    from TmpZAP z
    where z.FORM_MEDICAL_CARE = 3 and z.REFERRAL_MO is null AND (z.TYPE = 'Insert'  or (z.TYPE = 'Update' and z.HospIdFromDB is null ))
    union all
    select 8, 'Не указан тип документа, подтверждающего факт страхования. N_ZAP(' || z.N_ZAP::text || ')'
    from TmpZAP z
    where z.FORM_MEDICAL_CARE = 3 and z.POLICY_TYPE is null AND (z.TYPE = 'Insert'  or (z.TYPE = 'Update' and z.HospIdFromDB is null ))
    union all
    select 9, 'Не указан номер документа, подтверждающего факт страхования. N_ZAP(' || z.N_ZAP::text || ')'
    from TmpZAP z
    where z.FORM_MEDICAL_CARE = 3 and z.POLIS_NUMBER is null AND (z.TYPE = 'Insert'  or (z.TYPE = 'Update' and z.HospIdFromDB is null ))
    union all
    select 10, 'Не указан вид высокотехнологичной медицинской помощи. N_ZAP(' || z.N_ZAP::text || ')'
    from TmpZAP z
    where z.VMP_DATE is not null and z.VID_VMP is null AND (z.TYPE = 'Insert'  or (z.TYPE = 'Update' and z.HospIdFromDB is null ))
    union all
    select 11, 'Не указан метод высокотехнологичной медицинской помощи. N_ZAP(' || z.N_ZAP::text || ')'
    from TmpZAP z
    where z.VMP_DATE is not null and z.METOD_VMP is null AND (z.TYPE = 'Insert'  or (z.TYPE = 'Update' and z.HospIdFromDB is null ))
    union all
    select 12, 'Коды диагнозов по МКБ для данного метода ВМП (справочник V019_METOD_VMP колонка DIAG) не содержат код диагноза по МКБ, указанный в атрибуте MKB. N_ZAP(' || z.N_ZAP::text || ')'
    from TmpZAP z
    where z.METOD_VMP is not null 
    and not exists (select met.V019MetodVmpID from ISZL.T_V019_METOD_VMP met where met.IDHM = z.METOD_VMP and met.DIAG like '%'+z.MKB+'%' limit 1)
    AND (z.TYPE = 'Insert' or z.TYPE = 'Update');
    
    -- Insert
    if not exists(select* from TmpErrors)
    then
        insert into ISZL.T_HOSPITALIZATION(HospitalizationID, FIRST_NAME, LAST_NAME, HOSPITALIZATION_DATE, HOSPITALIZATION_TIME, MED_CARD_NUMBER, SEX, DIVISION
        , FORM_MEDICAL_CARE, POLIS_SERIAL, POLIS_NUMBER, FATHER_NAME, BIRTHDAY, MKB, SMO, USL_OK, POLICY_TYPE, CreateDateTime, V020_STRUCTURE_BED, VMP_NUMBER, VID_VMP
        , METOD_VMP, VMP_DATE, V018VidVmp, V019MetodVmp, REFERRAL_NUMBER, REFERRAL_DATE, BRANCH, MO, REFERRAL_MO
        ,Diagnosis)
        select z.HOSP_ID, z.FIRST_NAME, z.LAST_NAME, z.HOSPITALISATION_DATE, z.HOSPITALISATION_TIME, z.MED_CARD_NUMBER, z.SEX, z.DIVISION
        , z.FORM_MEDICAL_CARE, z.POLIS_SERIAL, z.POLIS_NUMBER, z.FATHER_NAME, z.BIRTHDAY, z.MKB, z.SMO, z.USL_OK, z.POLICY_TYPE, now()::timestamp
        , z.V020_STRUCTURE_BED, z.VMP_NUMBER, z.VID_VMP, z.METOD_VMP, z.VMP_DATE, vmp.V018VidVmpID
        , (select met.V019MetodVmpID from ISZL.T_V019_METOD_VMP met where met.IDHM = z.METOD_VMP and met.DIAG like '%'+z.MKB+'%' limit 1)
        , z.REFERRAL_NUMBER
        , z.REFERRAL_DATE, z.BRANCH, z.MO, z.REFERRAL_MO
        , mkb.Name
        from TmpZAP z
        left join
            ISZL.T_V018_VID_VMP vmp
            on vmp.IDHVID = z.VID_VMP
        left join ISZL.T_MKB mkb on mkb.MCBCode = z.MKB 
        where (z.TYPE = 'Insert' or (z.TYPE = 'Update' and z.HospIdFromDB is null ));
        

        update ISZL.T_HOSPITALIZATION_REFERRAL ref
        set HospitalisationDate = z.HOSPITALISATION_DATE
        ,    Hospitalization = z.HOSP_ID
        from TmpZAP z
        where z.HospReferralId::uuid = ref.HospitalizationReferralID
        and (z.TYPE = 'Insert' or (z.TYPE = 'Update' and z.HospIdFromDB is null ));

        if exists(select* from information_schema.columns c where c.table_schema = 'iszl' and c.table_name = 't_hospitalization' and c.column_name='filename')
        then
            update ISZL.T_HOSPITALIZATION hr
            set FILENAME = z.FILENAME
            from TmpZAP z 
            where z.HOSP_ID = hr.HospitalizationID
            and (z.TYPE = 'Insert' or (z.TYPE = 'Update' and z.HospIdFromDB is null ));
        end if;
    end if;
    
    --Update
    if    not exists(select* from TmpErrors)
    then
        update ISZL.T_HOSPITALIZATION hosp
        set FIRST_NAME = z.FIRST_NAME
        ,    LAST_NAME = z.LAST_NAME
        ,    HOSPITALIZATION_DATE = z.HOSPITALISATION_DATE
        ,    HOSPITALIZATION_TIME = z.HOSPITALISATION_TIME
        ,    MED_CARD_NUMBER = z.MED_CARD_NUMBER
        ,    SEX = z.SEX
        ,    DIVISION = z.DIVISION
        ,    FORM_MEDICAL_CARE = z.FORM_MEDICAL_CARE
        ,    POLIS_SERIAL = z.POLIS_SERIAL
        ,    POLIS_NUMBER = z.POLIS_NUMBER
        ,    FATHER_NAME = z.FATHER_NAME
        ,    BIRTHDAY = z.BIRTHDAY
        ,    MKB = z.MKB
        ,    SMO = z.SMO
        ,    USL_OK = z.USL_OK
        ,    POLICY_TYPE = z.POLICY_TYPE
        ,    V020_STRUCTURE_BED = z.V020_STRUCTURE_BED
        ,    VMP_NUMBER = z.VMP_NUMBER
        ,    VID_VMP = z.VID_VMP
        ,    METOD_VMP = z.METOD_VMP
        ,    VMP_DATE = z.VMP_DATE
        ,    V018VidVmp = (select vmp.V018VidVmpID from ISZL.T_V018_VID_VMP vmp where vmp.IDHVID = z.VID_VMP limit 1)
        ,    V019MetodVmp = (select met.V019MetodVmpID from ISZL.T_V019_METOD_VMP met where met.IDHM = z.METOD_VMP and met.DIAG like '%'+z.MKB+'%' limit 1)
        ,    REFERRAL_NUMBER = z.REFERRAL_NUMBER
        ,    REFERRAL_DATE = z.REFERRAL_DATE
        ,    BRANCH = z.BRANCH
        ,    MO = z.MO
        ,    REFERRAL_MO = z.REFERRAL_MO
        ,    Diagnosis = mkb.Name
        from TmpZAP z 
            left join ISZL.T_MKB mkb on mkb.MCBCode = z.MKB 
        where hosp.HospitalizationID = z.HospIdFromDB::uuid                        
        and z.TYPE = 'Update' and z.HospIdFromDB is not null;

        update ISZL.T_HOSPITALIZATION_REFERRAL ref
        set HospitalisationDate = z.HOSPITALISATION_DATE
        from TmpZAP z
        where z.HospIdFromDB::uuid = ref.Hospitalization 
        and z.REFERRAL_DATE is not null 
        and z.REFERRAL_MO is not null
        and z.REFERRAL_NUMBER is not null
        and z.TYPE = 'Update' and z.HospIdFromDB is not null;

        update ISZL.T_HOSPITALIZATION_REFERRAL hosp
        set Hospitalization = null
        from TmpZAP z
        where z.HospIdFromDB::uuid = hosp.Hospitalization
        and (z.REFERRAL_DATE is null or z.REFERRAL_MO is null or z.REFERRAL_NUMBER is null)
        and z.TYPE = 'Update' and z.HospIdFromDB is not null;

        if exists(select* from information_schema.columns c where c.table_schema = 'iszl' and c.table_name = 't_hospitalization' and c.column_name='filename')
        then
            update ISZL.T_HOSPITALIZATION hr
            set FILENAME = z.FILENAME
            from TmpZAP z 
            where z.HospIdFromDB = hr.HospitalizationID
            and z.TYPE = 'Update' and z.HospIdFromDB is not null;
        end if;
    end if;

    --Delete
    insert into TmpErrors(Code, Comment)
    select 13, 'Госпитализация не найдена. N_ZAP(' || z.N_ZAP::text || ')'
    from TmpZAP z
    where z.HospIdFromDB is null AND z.TYPE = 'Delete';

    if not exists(select* from TmpErrors)
    then
        update ISZL.T_HOSPITALIZATION_REFERRAL ref
        set Hospitalization = null
        from TmpZAP z 
        where ref.Hospitalization = z.HospIdFromDB::uuid
        and z.TYPE = 'Delete';

        delete from ISZL.T_HOSPITALIZATION hosp
        where exists(select* from TmpZAP z where z.HospIdFromDB::uuid = hosp.HospitalizationID and z.TYPE = 'Delete');
    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", Cast("' || r.HOSP_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;
