DROP FUNCTION if exists sp_HospitalisationLoad(xml);
CREATE OR REPLACE FUNCTION sp_HospitalisationLoad
(
    x xml default null::xml
)
RETURNS TABLE (Res boolean, Err text, lexem text)
LANGUAGE plpgsql
AS $function$
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('/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 HOSP_ID
    ,    (xpath('//FORM_MEDICAL_CARE[1]/text()', z))[1]::text::int                                            as FORM_MEDICAL_CARE
    ,    (xpath('//REFERRAL_NUMBER[1]/text()', z))[1]::varchar(21)                                           as REFERRAL_NUMBER
    ,    to_timestamp((xpath('//REFERRAL_DATE[1]/text()', z))[1]::text, 'YYYY-MM-DD')                        as REFERRAL_DATE
    ,    (xpath('//REFERRAL_MO[1]/text()', z))[1]::varchar(6)                                                   as REFERRAL_MO
    ,    (xpath('//MO[1]/text()', z))[1]::varchar(6)                                                           as MO
    ,    (xpath('//BRANCH[1]/text()', z))[1]::varchar(9)                                                       as BRANCH
    ,    (xpath('//DIVISION[1]/text()', z))[1]::varchar(12)                                                   as DIVISION
    ,    to_timestamp((xpath('//HOSPITALISATION_DATE[1]/text()', z))[1]::text, 'YYYY-MM-DD')                    as HOSPITALISATION_DATE
    ,    to_timestamp((xpath('//HOSPITALISATION_TIME[1]/text()', z))[1]::text, 'YYYY-MM-DD HH24:MI:SS')    as HOSPITALISATION_TIME
    ,    (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(40)                                                   as LAST_NAME
    ,    (xpath('//FIRST_NAME[1]/text()', z))[1]::varchar(40)                                                   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('//USL_OK[1]/text()', z))[1]::text::int                                                        as USL_OK
    ,    (xpath('//V020_STRUCTURE_BED[1]/text()', z))[1]::text::int                                            as V020_STRUCTURE_BED
    ,    (xpath('//MED_CARD_NUMBER[1]/text()', z))[1]::varchar(50)                                           as MED_CARD_NUMBER
    ,    (xpath('//MKB[1]/text()', z))[1]::varchar(8)                                                           as MKB
    ,    to_timestamp((xpath('//VMP_DATE[1]/text()', z))[1]::text, 'YYYY-MM-DD')                                as VMP_DATE
    ,    (xpath('//VMP_NUMBER[1]/text()', z))[1]::text::varchar(50)                                                    as VMP_NUMBER
    ,    (xpath('//VID_VMP[1]/text()', z))[1]::text::varchar(12)                                                      as VID_VMP
    ,    (xpath('//METOD_VMP[1]/text()', z))[1]::text::int                                                  as METOD_VMP
    ,    null::uuid                                                 as HospReferralId --заглушка
    ,    null::uuid                                                as HospIdFromDB --заглушка
    ,    null::uuid                                                          as ErrorString --заглушка
    ,    null::uuid                                              as MkbId --заглушка
    from
    (
        select unnest(xpath('/HOSPITALISATION//body', x)) as z
    ) t1;
    
    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors(CODE int, COMMENT varchar(250));

    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)
    where _type <> 'Delete';

    insert into TmpErrors(CODE, COMMENT)
    select 0, 'Код мкб не найден в справочнике МКБ'
    from TmpZAP z
    where z.MkbId is null
    and _type <> 'Delete';
    
    insert into TmpErrors(CODE, COMMENT)
    select 14, 'Неверный тип операции.'
    where _type not in ('Insert', 'Delete', 'Update');
    
    update TmpZAP zap
    set HospIdFromDB = hosp.HospitalizationID
    from ISZL.T_HOSPITALIZATION hosp 
    where hosp.HOSPITALIZATION_DATE = zap.HOSPITALISATION_DATE
        and hosp.MED_CARD_NUMBER = zap.MED_CARD_NUMBER
        and hosp.MO = zap.MO;
    
    if _type = 'Insert'  or (_type = 'Update' and ((select count(*) from TmpZAP where HospIdFromDB is null)<>0))
    then
        Update TmpZAP zap
        set HospReferralId =
        (
            select ref.HospitalizationReferralID
            from ISZL.T_HOSPITALIZATION_REFERRAL ref
            where ref.REFERRAL_DATE = zap.REFERRAL_DATE
                and (ref.REFERRAL_NUMBER = zap.REFERRAL_NUMBER OR (ref.RegRefNumber is not null and ref.RegRefNumber = zap.REFERRAL_NUMBER))
                and ref.LpuMainFrom = zap.REFERRAL_MO
            limit 1
        )
        where  zap.FORM_MEDICAL_CARE = 3;

        insert into TmpErrors(CODE, COMMENT)
        select 2, 'Ошибка. Госпиализация уже существет'
        from TmpZAP zap
        where zap.HospIdFromDB is not null
        union all
        select 4, 'Не найдено направление на госпитализацию.'
        from TmpZAP zap
        where ( zap.FORM_MEDICAL_CARE = 3) and zap.HospReferralId is null
        union all
        select 5, 'Не указан номер направления.'
        from TmpZAP zap
        where ( zap.FORM_MEDICAL_CARE = 3) 
        and zap.REFERRAL_NUMBER is null
        union all
        select 6, 'Не указана дата направления.'
        from TmpZAP zap
        where ( zap.FORM_MEDICAL_CARE = 3) 
        and zap.REFERRAL_DATE is null
        union all
        select 7, 'Не указана МО, направившая на госпитализацию.'
        from TmpZAP zap
        where ( zap.FORM_MEDICAL_CARE = 3) 
        and zap.REFERRAL_MO is null
        union all
        select 8, 'Не указан тип документа, подтверждающего факт страхования.'
        from TmpZAP zap
        where ( zap.FORM_MEDICAL_CARE = 3) 
        and zap.POLICY_TYPE is null
        union all
        select 9, 'Не указан номер документа, подтверждающего факт страхования.'
        from TmpZAP zap
        where ( zap.FORM_MEDICAL_CARE = 3) 
        and zap.POLIS_NUMBER is null
        union all
        select 10, 'Не указан вид высокотехнологичной медицинской помощи.'
        from TmpZAP zap
        where zap.VMP_DATE is not null 
        and zap.VID_VMP is null
        union all
        select 11, 'Не указан метод высокотехнологичной медицинской помощи.'
        from TmpZAP zap
        where zap.VMP_DATE is not null 
        and zap.METOD_VMP is null
        union all
        select 12, 'Коды диагнозов по МКБ для данного метода ВМП (справочник V019_METOD_VMP колонка DIAG) не содержат код диагноза по МКБ, указанный в атрибуте MKB'
        from TmpZAP zap
        where zap.METOD_VMP is not null 
        and not exists (select met.V019MetodVmpID from ISZL.T_V019_METOD_VMP met where met.IDHM = zap.METOD_VMP and met.DIAG like '%'+zap.MKB+'%' limit 1);
    


        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 zap.HOSP_ID, zap.FIRST_NAME, zap.LAST_NAME, zap.HOSPITALISATION_DATE, zap.HOSPITALISATION_TIME
            , zap.MED_CARD_NUMBER, zap.SEX, zap.DIVISION, zap.FORM_MEDICAL_CARE, zap.POLIS_SERIAL
            , zap.POLIS_NUMBER, zap.FATHER_NAME, zap.BIRTHDAY, zap.MKB, zap.SMO, zap.USL_OK, zap.POLICY_TYPE
            , now()::timestamp, zap.V020_STRUCTURE_BED, zap.VMP_NUMBER, zap.VID_VMP, zap.METOD_VMP
            , zap.VMP_DATE, vmp.V018VidVmpID
            , (select met.V019MetodVmpID from ISZL.T_V019_METOD_VMP met where met.IDHM = zap.METOD_VMP and met.DIAG like '%'+zap.MKB+'%' limit 1)
            , zap.REFERRAL_NUMBER, zap.REFERRAL_DATE, zap.BRANCH
            , zap.MO, zap.REFERRAL_MO, mkb.Name
            from TmpZAP zap
            left join ISZL.T_MKB mkb on mkb.MkbId = zap.MkbId 
            left join
                ISZL.T_V018_VID_VMP vmp
                on vmp.IDHVID = zap.VID_VMP;

            update ISZL.T_HOSPITALIZATION_REFERRAL ref
            set HospitalisationDate = zap.HOSPITALISATION_DATE
            ,    Hospitalization = zap.HOSP_ID
            from TmpZAP zap 
            where zap.HospReferralId = ref.HospitalizationReferralID;
        end if;
    end if;

    if _type = 'Update'  and  ((select count(*) from TmpZAP where HospIdFromDB is not null) <> 0)
    then
        --insert into TmpErrors(CODE, COMMENT)
        --select 12, 'Госпитализация не найдена.'
        --from TmpZAP zap
        --where zap.HospIdFromDB is null;
        insert into TmpErrors(CODE, COMMENT)
        select 12, 'Коды диагнозов по МКБ для данного метода ВМП (справочник V019_METOD_VMP колонка DIAG) не содержат код диагноза по МКБ, указанный в атрибуте MKB'
        from TmpZAP zap
        where zap.METOD_VMP is not null 
        and not exists (select met.V019MetodVmpID from ISZL.T_V019_METOD_VMP met where met.IDHM = zap.METOD_VMP and met.DIAG like '%'+zap.MKB+'%' limit 1);
    

        if not exists(select* from TmpErrors)
        then
            update ISZL.T_HOSPITALIZATION hosp
            set FIRST_NAME = zap.FIRST_NAME
            ,    LAST_NAME = zap.LAST_NAME
            ,    HOSPITALIZATION_DATE = zap.HOSPITALISATION_DATE
            ,    HOSPITALIZATION_TIME = zap.HOSPITALISATION_TIME
            ,    MED_CARD_NUMBER = zap.MED_CARD_NUMBER
            ,    SEX = zap.SEX
            ,    DIVISION = zap.DIVISION
            ,    FORM_MEDICAL_CARE = zap.FORM_MEDICAL_CARE
            ,    POLIS_SERIAL = zap.POLIS_SERIAL
            ,    POLIS_NUMBER = zap.POLIS_NUMBER
            ,    FATHER_NAME = zap.FATHER_NAME
            ,    BIRTHDAY = zap.BIRTHDAY
            ,    MKB = zap.MKB
            ,    SMO = zap.SMO
            ,    USL_OK = zap.USL_OK
            ,    POLICY_TYPE = zap.POLICY_TYPE
            ,    V020_STRUCTURE_BED = zap.V020_STRUCTURE_BED
            ,    VMP_NUMBER = zap.VMP_NUMBER
            ,    VID_VMP = zap.VID_VMP
            ,    METOD_VMP = zap.METOD_VMP
            ,    VMP_DATE = zap.VMP_DATE
            ,    V018VidVmp = (select vmp.V018VidVmpID from ISZL.T_V018_VID_VMP vmp where vmp.IDHVID = zap.VID_VMP limit 1)
            ,    V019MetodVmp = (select met.V019MetodVmpID from ISZL.T_V019_METOD_VMP met where met.IDHM = zap.METOD_VMP and met.DIAG like '%'+zap.MKB+'%' limit 1)
            ,    REFERRAL_NUMBER = zap.REFERRAL_NUMBER
            ,    REFERRAL_DATE = zap.REFERRAL_DATE
            ,    BRANCH = zap.BRANCH
            ,    MO = zap.MO
            ,    REFERRAL_MO = zap.REFERRAL_MO
            ,   Diagnosis = mkb.Name
            from TmpZAP zap
                left join ISZL.T_MKB mkb on mkb.MkbId = zap.MkbId 
            where hosp.HospitalizationID = zap.HospIdFromDB;
    
            update ISZL.T_HOSPITALIZATION_REFERRAL ref
            set HospitalisationDate = zap.HOSPITALISATION_DATE
            from TmpZAP zap 
            where zap.HospIdFromDB = ref.Hospitalization 
            and zap.REFERRAL_DATE is not null 
            and zap.REFERRAL_MO is not null
            and zap.REFERRAL_NUMBER is not null;

            update ISZL.T_HOSPITALIZATION_REFERRAL hosp
            set Hospitalization = null
            from TmpZAP zap 
            where zap.HospIdFromDB = hosp.Hospitalization
            and (zap.REFERRAL_DATE is null or zap.REFERRAL_MO is null or zap.REFERRAL_NUMBER is null);
        
        end if;
    end if;

    if _type = 'Delete'
    then
        insert into TmpErrors(CODE, COMMENT)
        select 13, 'Госпитализация не найдена.'
        from TmpZAP zap
        where zap.HospIdFromDB is null;

        if not exists(select* from TmpErrors)
        then
            update ISZL.T_HOSPITALIZATION_REFERRAL ref
            set Hospitalization = null, HospitalisationDate = null
            from TmpZAP zap 
            where ref.Hospitalization = zap.HospIdFromDB;

            delete from ISZL.T_HOSPITALIZATION hosp
            where exists(select* from TmpZAP zap where zap.HospIdFromDB = hosp.HospitalizationID);
        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
    , 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);

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