DROP FUNCTION if exists sp_DnInsert;
drop procedure if exists sp_DnInsert;

CREATE procedure sp_DnInsert
(
    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$
declare
    _DnFileId uuid;
    _filexists uuid;
begin
    Drop Table If Exists TmpZGLV;
    Create Temp Table TmpZGLV
    As
    select
        coalesce("FileID", gen_random_uuid()) as FileId,
        (xpath('//VERSION[1]/text()', z))[1]::varchar(5) as VERSION,
        to_timestamp((xpath('//DATA[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') as DATA,
        (xpath('//FILENAME[1]/text()', z))[1]::varchar(50) as FILENAME,
        (xpath('//MCOD[1]/text()', z))[1]::text::varchar(6) as MCOD,
        (xpath('//SMO[1]/text()', z))[1]::text::varchar(5) as SMO,
        date_trunc('day', now()::timestamp) as ReceivedDate,
        now()::timestamp as ReceivedTime
    from
    (
        select unnest(xpath('/DN_LIST/ZGLV', x)) as z
    ) t1;
    
    Drop Table If Exists TmpZL;
    Create Temp Table TmpZL
    As
    select
        gen_random_uuid() as ItemID,
        (xpath('//ID_PAC[1]/text()', z))[1]::varchar(36) as ID_PAC,
        (xpath('//FAM[1]/text()', z))[1]::varchar(40) as FAM,
        (xpath('//IM[1]/text()', z))[1]::varchar(40) as IM,
        (xpath('//OT[1]/text()', z))[1]::varchar(40) as OT,
        to_timestamp((xpath('//DR[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') as DR,
        (xpath('//ENP[1]/text()', z))[1]::varchar(16) as ENP,
        (xpath('//VPOLIS[1]/text()', z))[1]::text::int as VPOLIS,
        (xpath('//SPOLIS[1]/text()', z))[1]::varchar(10) as SPOLIS,
        (xpath('//NPOLIS[1]/text()', z))[1]::varchar(20) as NPOLIS,
        (xpath('//SNILS[1]/text()', z))[1]::varchar(14) as SNILS,
        (xpath('//InsuredId[1]/text()', z))[1]::text::uuid as InsuredId
    from
    (
        select unnest(xpath('/DN_LIST/ZL', x)) as z
    ) t1;
    
    Drop Table If Exists TmpCONTACTS;
    Create Temp Table TmpCONTACTS
    As
    select
        (xpath('//ID_PAC[1]/text()', z))[1]::varchar(36) as ID_PAC,
        (xpath('//CONTACT[1]/text()', con))[1]::varchar(250) as CONTACT,
        (xpath('//TYPE[1]/text()', con))[1]::text::varchar(1) as TYPE
    from
    (
        select z, unnest(xpath('/ZL/CONTACTS', z)) as con
        from
        (
            select unnest(xpath('/DN_LIST/ZL', x)) as z
        ) t1
    ) t2;
    
    Drop Table If Exists TmpDN;
    Create Temp Table TmpDN
    As
    select
        gen_random_uuid() as DNID,
        (xpath('//ID_PAC[1]/text()', z))[1]::varchar(36) as ID_PAC,
        (xpath('//DN_ID[1]/text()', dn))[1]::varchar(36) as DN_ID,
        to_timestamp((xpath('//DATE_BEGIN[1]/text()', dn))[1]::varchar(10), 'YYYY-MM-DD') as DATE_BEGIN,
        to_timestamp((xpath('//DATE_OUT[1]/text()', dn))[1]::varchar(10), 'YYYY-MM-DD') as DATE_OUT,
        (xpath('//DN_PR[1]/text()', dn))[1]::text::int as DN_PR,
        to_timestamp((xpath('//DATE_GR[1]/text()', dn))[1]::varchar(10), 'YYYY-MM-DD') as DATE_GR,
        (xpath('//GROUP_DN[1]/text()', dn))[1]::text::int as GROUP_DN,
        (xpath('//DS[1]/text()', dn))[1]::varchar(10) as DS,
        (xpath('//MO[1]/text()', dn))[1]::varchar(6) as MO,
        (xpath('//CODE_MR[1]/text()', dn))[1]::varchar(14) as SNILS_MR,
        (xpath('//SPEC[1]/text()', dn))[1]::text::int as SPEC,
        to_timestamp((xpath('//DATE_P[1]/text()', dn))[1]::varchar(10), 'YYYY-MM-DD') as DATE_P
    from
    (
        select z, unnest(xpath('/ZL/DN', z)) as dn
        from
        (
            select unnest(xpath('/DN_LIST/ZL', x)) as z
        ) t1
    ) t2;

    alter table TmpDN add MkbId uuid;
    update TmpDN dn
    set MkbId = mkb.MkbID
    from ISZL.T_MKB mkb 
    where mkb.MCBCode = dn.DS
            and 
        mkb.EndCode is null;
    
    Drop Table If Exists TmpPRESENCES;
    Create Temp Table TmpPRESENCES
    As
    select
        gen_random_uuid() as PresenceIdGUID,
        (xpath('//ID_PAC[1]/text()', z))[1]::varchar(36) as ID_PAC,
        (xpath('//DN_ID[1]/text()', dn))[1]::varchar(36) as DN_ID,
        (xpath('//CODE_MR[1]/text()', dn))[1]::varchar(14) as SNILS_MR,
        (xpath('//PRESENCE_ID[1]/text()', pr))[1]::varchar(36) as PresenceId,
        to_timestamp((xpath('//DATE_PLAN[1]/text()', pr))[1]::varchar(10), 'YYYY-MM-DD') as DATE_PLAN,
        (xpath('//PLACE[1]/text()', pr))[1]::text::int as PLACE,
        (xpath('//MO_P[1]/text()', pr))[1]::varchar(6) as MO_P
    from
    (
        select z, dn, unnest(xpath('/DN/PRESENCES', dn)) as pr
        from
        (
            select z, unnest(xpath('/ZL/DN', z)) as dn
            from
            (
                select unnest(xpath('/DN_LIST/ZL', x)) as z
            ) t1
        ) t2
    ) t3;
    
    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors
    as
    select 0::int as Code, ('Код мкб (' || dn.DS || ') не найден в справочнике МКБ (DN_ID - ' || DN_ID || ')')::varchar(250) as Comment
    from TmpDN dn
    where dn.MkbId is null;

    insert into TmpErrors(Code, Comment)
    select 1, 'Код мкб (' || dn.DS || ') не принадлежит классификатору перечня заболеваний, при наличии которых проводится ДН'
    from TmpDN dn
    where dn.DS like 'U%';

    insert into TmpErrors(Code, Comment)
    select 7, 'Дата посещения  (' || pres.DATE_PLAN::text || ') не может быть раньше, чем дата постановки на ДН (' || dn.DATE_BEGIN::text || '); DN_ID = ' || dn.DN_ID
    from TmpPRESENCES pres
        join TmpDN dn on dn.DN_ID = pres.DN_ID
    where pres.DATE_PLAN < dn.DATE_BEGIN;

    -- Для Костромы
    if exists(select * from information_schema.columns WHERE column_name='xdn')
    then
    begin
        EXECUTE 'insert into TmpErrors(Code, Comment) select 0, ''Врач, указанный как лечащий, не может вести ДН по его специальности. ID_PAC('' || dn.ID_PAC || '')'' from TmpDN dn join ISZL.T_V021_SPECIALITY s on s.Id = dn.SPEC where s.xDn = 0;';
    end;
    end if;

    insert into TmpErrors(Code, Comment)
    select 3, 'Некорректная дата у явки с идентификатором (' || pres.PresenceId || '). ID_PAC('|| pres.ID_PAC ||')'
    from TmpPRESENCES pres
    where length(EXTRACT(year from pres.DATE_PLAN)::text) <> 4;

    if not exists(select * from TmpErrors)
    then 
    begin
        --идентификация
        update TmpZL t1
        set InsuredId = 
        (
              select t2.InsuredID 
            from ISZL.T_INSURED t2
              inner join
                ISZL.T_INSURED_POLICY_RECORD ipr
                on t2.InsuredID = ipr.Insured
                and ipr.DateEnd is null
              inner join
                ISZL.T_POLICY iszlpolicy 
                on iszlpolicy.PolicyID = ipr.Policy
              where t2.LastName = t1.FAM
            and t2.FirstName = t1.IM
            and t2.Birthday = t1.DR
            and iszlpolicy.Number = t1.NPOLIS   
            limit 1
         )
        where t1.InsuredId is null;
 
        --step 2 (ис ЗЛ фамилия, имя, отчество, др)
        update TmpZL t1
        set InsuredId = (select t2.InsuredID from ISZL.T_INSURED t2 where t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR limit 1)
        where t1.InsuredId is null;

        --step 3 (фамилия, имя, номер полиса) из коллекции полисов
        update TmpZL t1
        set InsuredId =
        (
              select t2.InsuredID 
            from ISZL.T_INSURED t2
              inner join
                ISZL.T_INSURED_POLICY_RECORD ipr 
                on t2.InsuredID = ipr.Insured and ipr.DateEnd is null
              inner join
                ISZL.T_POLICY iszlpolicy
                on iszlpolicy.PolicyID = ipr.Policy
              where t2.LastName = t1.FAM 
            and t2.FirstName = t1.IM 
            and iszlpolicy.Number = t1.NPOLIS
            limit 1
         )
        where t1.InsuredId is null;

        --step 4 (фамилия, др, номер полиса) из коллекции полисов
        update TmpZL t1
        set InsuredId =
        (
              select t2.InsuredID 
            from ISZL.T_INSURED t2
              inner join
                ISZL.T_INSURED_POLICY_RECORD ipr
                on t2.InsuredID = ipr.Insured
                and ipr.DateEnd is null
              inner join
                ISZL.T_POLICY iszlpolicy
                on iszlpolicy.PolicyID = ipr.Policy
              where t2.LastName = t1.FAM
            and t2.Birthday = t1.DR
            and iszlpolicy.Number = t1.NPOLIS
            limit 1
         )
        where t1.InsuredId is null;

        --step 5 (ENP) из ЗЛ
        update TmpZL t1
        set InsuredId = (select t2.InsuredID from ISZL.T_INSURED t2 where t2.ENP = t1.ENP limit 1)
        where t1.InsuredId is null;

        --step 6 (NPOLIS, ID_PAC) из коллекции полисов
        update TmpZL t1
        set InsuredId = 
        (
            select t2.InsuredID
            from ISZL.T_INSURED t2
            inner join
                ISZL.T_INSURED_POLICY_RECORD ipr
                on t2.InsuredID = ipr.Insured
                and ipr.DateEnd is null
            inner join
                ISZL.T_POLICY iszlpolicy 
                on iszlpolicy.PolicyID = ipr.Policy
            where iszlpolicy.Number = t1.NPOLIS
            and t2.ID_PAC = t1.ID_PAC
            limit 1
        )
        where t1.InsuredId is null;

        --step 7 (ID_PAC) из ЗЛ
        update TmpZL t1
        set InsuredId = (select t2.InsuredID from ISZL.T_INSURED t2 where t2.ID_PAC = t1.ID_PAC limit 1)
        where t1.InsuredId is null;

        insert into TmpErrors(Code, Comment)
        select 1, 'Застрахованный не найден в БД. NPOLIS(' || zl.NPOLIS || ')'
        from TmpZL zl
        where zl.InsuredId is null;        

        alter table TmpPRESENCES add PresenceDuplicateId uuid;
        alter table TmpDN add DuplicateId uuid;

        -- Ищем дубликаты PRESENCES (Застрахованый, Код МКБ-10, Дата явки)
        update TmpPRESENCES p
        set PresenceDuplicateId = (
            select pr.DnPresenceID
            from ISZL.T_DN_PRESENCE pr
                join ISZL.T_DN_ITEM di on di.DnItemId = pr.DnItem
                        and
                    di.Insured = zl.InsuredId
            limit 1
        )
        from TmpDN dn
            join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS
                    and
                mkb.EndCode is null
            join TmpZL zl on zl.ID_PAC = dn.ID_PAC
        where dn.DN_ID = p.DN_ID 
                and 
            dn.ID_PAC = p.ID_PAC;

        update TmpDN dn
        set DuplicateId = (
            select di.DnItemID
            from TmpZL z
                join TmpDN d on z.ID_PAC = dn.ID_PAC
                join ISZL.T_DN_ITEM di on z.InsuredId = di.Insured
                join ISZL.T_MKB mkb on mkb.MCBCode = d.DS
            where di.Insured is not null and z.InsuredId is not null
                    and
                z.ID_PAC = zl.ID_PAC
                    and
                d.DNID = dn.DNID
                    and
                d.MO = di.MO_IN
                    and 
                mkb.MkbID = di.DS_DISP
                    and
                (
                    di.DATE_OUT is null and d.DATE_OUT is null
                        or
                    (
                        di.DATE_OUT is not null and d.DATE_OUT is not null
                            and
                        di.DATE_OUT::date = d.DATE_OUT::date
                    )
                )
            limit 1
        )
        from TmpZL zl
        where zl.ID_PAC = dn.ID_PAC
                and
            zl.InsuredId is not null;

    end;
    end if;

    if not exists(select * from TmpErrors)
    then
    begin
        ---file delete
        _DnFileId := (select dd.FileID from ISZL.T_DN_FILE dd where dd.FILENAME = (select z.FILENAME from TmpZGLV z limit 1) limit 1);

        if _DnFileId is not null
        then
        begin
            delete from ISZL.T_DN_PRESENCE pr
            where exists(select * from ISZL.T_DN_ITEM di where di.DnItemId = pr.DnItem and di.DnFile = _DnFileId);

            delete from ISZL.T_DN_ITEM where DnFile = _DnFileId;

            delete from ISZL.T_DN_FILE where FileId = _DnFileId;

            delete from VClib.T_FILE where FileId = _DnFileId;
        end;
        end if;
    
        ---create file            
        insert into VCLib.T_FILE(FileID, Name, CreateDateTime, FileNameForm, SaveFileLocation, UseOle, type_)
        select z.FileId, z.FILENAME, now()::timestamp, 1, 1, '0'::boolean, (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DN_FILE') 
        from TmpZGLV z;

        insert into ISZL.T_DN_FILE(FileID, VERSION, CODE_MO, ID, ReceivedDate, ReceivedTime, DATA, FILENAME)
        select z.FileId, z.VERSION, z.MCOD, null::varchar(36), z.ReceivedDate, z.ReceivedTime, z.DATA, z.FILENAME
        from TmpZGLV z;

        insert into ISZL.T_DN_ITEM
        (
            DnItemID,
            DATE_IN,
            DATE_OUT,
            RESULT_OUT,
            DN_ID,
            DS_DISP,
            SNILS_VR,
            V021Speciality,
            Insured,
            ID_PAC,
            DnFile,
            MO_IN,
            DATE_GR,
            GROUP_DN,
            DATE_P
        )
        select 
            dn.DNID,
            dn.DATE_BEGIN,
            dn.DATE_OUT,
            case dn.DN_PR when 4 then 1 when 6 then 8 else null end,
            dn.DN_ID,
            mkb.MkbID,
            dn.SNILS_MR,
            dn.SPEC,
            zl.InsuredId,
            dn.ID_PAC,
            "FileID",
            mo.MCOD,
            dn.DATE_GR,
            dn.GROUP_DN,
            dn.DATE_P
        from TmpDN dn
        inner join 
            TmpZL zl
            on zl.ID_PAC = dn.ID_PAC
        inner join
            ISZL.T_MKB mkb
            on mkb.MCBCode = dn.DS
            and mkb.EndCode is null
        left join 
            ISZL.T_F003 mo 
            on mo.MCOD = dn.MO
        where dn.DuplicateId is null;

        update ISZL.T_DN_ITEM di
        set RESULT_OUT = case dn.DN_PR when 4 then 1 when 6 then 8 else null end,
            DN_ID = dn.DN_ID,
            SNILS_VR = dn.SNILS_MR,
            V021Speciality = dn.SPEC,
            ID_PAC = dn.ID_PAC,
            DnFile = "FileID",
            DATE_GR = dn.DATE_GR,
            GROUP_DN = dn.GROUP_DN,
            DATE_P = dn.DATE_P,
            DATE_OUT = dn.DATE_OUT
        from TmpDN dn
            join TmpZL zl on zl.ID_PAC = dn.ID_PAC
            join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null
            left join ISZL.T_F003 mo on mo.MCOD = dn.MO
        where di.DnItemID = dn.DuplicateId 
                and 
            dn.DuplicateId is not null;

        insert into ISZL.T_DN_PRESENCE
        (
            DnPresenceID,
            DATE_PLAN,
            PLACE,
            MO_P,
            DnItem,
            SNILS_VR1
        )
        select 
            pres.PresenceIdGUID,
            pres.DATE_PLAN,
            pres.PLACE,
            lpumain.MCOD,
            dn.DNID,
            pres.SNILS_MR
        from TmpPRESENCES pres
        inner join
            TmpDN dn
            on dn.DN_ID = pres.DN_ID
        left join
            ISZL.T_F003 lpumain 
            on lpumain.MCOD = pres.MO_P
        where pres.PresenceDuplicateId is null;
            
        update ISZL.T_DN_PRESENCE dp
        set PLACE = pres.PLACE,
            MO_P = lpumain.MCOD
        from TmpPRESENCES pres
            join TmpDN dn on dn.DN_ID = pres.DN_ID
            left join ISZL.T_F003 lpumain on lpumain.MCOD = pres.MO_P
        where dp.DnPresenceID = pres.PresenceDuplicateId
                and
            pres.PresenceDuplicateId is not null;

        --добавляем только таких, которые отличаются по типу и контакту в ЗЛ
        insert into ISZL.T_INSURED_CONTACT(InsuredContactID, Insured, Contact, ContactType)
        select gen_random_uuid(), zl.InsuredId, cont.CONTACT, t1.ContactTypeID
        from TmpCONTACTS cont
        inner join
            TmpZL zl
            on zl.ID_PAC = cont.ID_PAC
        inner join
            ISZL.T_CONTACT_TYPE t1 
            on t1.Name = cont.TYPE
        where not exists
        (
            select * from ISZL.T_INSURED_CONTACT insc where insc.Contact = cont.CONTACT and insc.Insured = zl.InsuredId and insc.ContactType = t1.ContactTypeID
        );
    end;
    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;

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

	drop table if exists temp_table;
    DROP TABLE if exists TmpZGLV;
    DROP TABLE if exists TmpZL;
    DROP TABLE if exists TmpCONTACTS;
    DROP TABLE if exists TmpDN;
    DROP TABLE if exists TmpPRESENCES;
    DROP TABLE if exists TmpErrors;
end;
$BODY$ LANGUAGE plpgsql;
