-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2020-04-01
-- Description: Загрузка регистра паллиативных больных.
-- =============================================
DROP PROCEDURE IF EXISTS sp_GetPalliativeRegister;

CREATE PROCEDURE sp_GetPalliativeRegister
(
    "Xml" xml = null    
,	"ID" uuid DEFAULT NULL::uuid
,   "QueueName" varchar(100) = 'RDSSync'
,   "DateTime" TimeStamp = null
,	"User" uuid DEFAULT NULL::uuid
)
    language plpgsql
as
$$
 Begin
    declare 
    _OperationType varchar(36);
    Begin
        IF ("Xml" is not null)
        then
	    	"DateTime" := coalesce("DateTime", Current_TimeStamp::TIMESTAMP);
            
            -- Получаем тип операции
			select into
				_OperationType
				(xpath('/PALLIATIVE_REGISTER/header[1]/TYPE[1]/text()',"Xml"))[1]::varchar(36);

            -- Generate table Tmp_PALLIATIVE_REGISTER
			Drop Table If Exists Tmp_PALLIATIVE_REGISTER;
            Create Temp Table Tmp_PALLIATIVE_REGISTER
            as
            select (xpath('PATIENT/RegisterID[1]/text()', x))[1]::text::int                             as RegisterID
                  ,(xpath('PATIENT/LastName[1]/text()', x))[1]::varchar(40)                             as LastName
                  ,(xpath('PATIENT/FirstName[1]/text()', x))[1]::varchar(40)                            as FirstName
                  ,(xpath('PATIENT/FatherName[1]/text()', x))[1]::varchar(40)                           as FatherName
                  ,to_timestamp((xpath('PATIENT/Birthday[1]/text()', x))[1]::text, 'DD.MM.YYYY')        as Birthday
                  ,(xpath('PATIENT/Kod[1]/text()', x))[1]::varchar(32)		                            as Kod
                  ,(xpath('PATIENT/DocSeries[1]/text()', x))[1]::varchar(8)                             as DocSeries
                  ,(xpath('PATIENT/DocNumber[1]/text()', x))[1]::varchar(8)                             as DocNumber
                  ,(xpath('PATIENT/Sex[1]/text()', x))[1]::text::int                                    as Sex
                  ,(xpath('PATIENT/Snils[1]/text()', x))[1]::varchar(14)                                as Snils
                  ,to_timestamp((xpath('PATIENT/DateBegin[1]/text()', x))[1]::text, 'DD.MM.YYYY')       as DateBegin
                  ,to_timestamp((xpath('PATIENT/DateEnd[1]/text()', x))[1]::text, 'DD.MM.YYYY')         as DateEnd
                  ,to_timestamp((xpath('PATIENT/LastEditDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')    as LastEditDate
                  ,(xpath('PATIENT/Lpu[1]/text()', x))[1]::varchar(6)                                   as Lpu
                  ,(xpath('PATIENT/ExcludeReason[1]/text()', x))[1]::text::int                          as ExcludeReason
                  ,(xpath('PATIENT/DocType[1]/text()', x))[1]::text::int                                as DocType
                  ,(xpath('PATIENT/PatientId[1]/text()', x))[1]::text::UUID                             as PatientId
            from
    			(
    				select	unnest(xpath('PALLIATIVE_REGISTER/body[1]/PATIENT', x)) as x
    				from 
    				(
    					select "Xml" as x
    				) root
    			) PATIENT;	

	    	
            -- Generate table Tmp_PALLIATIVE_BENEFITS
			Drop Table If Exists Tmp_PALLIATIVE_BENEFITS;
            Create Temp Table Tmp_PALLIATIVE_BENEFITS
            as
            select (xpath('BENEFITS/RegisterID[1]/text()', x))[1]::text::int						as RegisterID
                  ,to_timestamp((xpath('BENEFITS/DiagDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as DiagDate
                  ,(xpath('BENEFITS/Document[1]/text()', x))[1]::varchar(16)						as Document
                  ,to_timestamp((xpath('./DocumentDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')	    as DocumentDate
                  ,(xpath('BENEFITS/Mkb[1]/text()', x))[1]::varchar(8)								as Mkb
                  ,(xpath('BENEFITS/BenefitCode[1]/text()', x))[1]::varchar(255)					as BenefitCode
                  ,(xpath('BENEFITS/BenefitViews[1]/text()', x))[1]::text::int						as BenefitViews
                  ,to_timestamp((xpath('BENEFITS/BeginDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as BeginDate
                  ,to_timestamp((xpath('BENEFITS/EndDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as EndDate
                  ,(xpath('BENEFITS/Lpu[1]/text()', x))[1]::varchar(6)								as Lpu
            from
    			(
    				select	unnest(xpath('PALLIATIVE_REGISTER/body[1]/PATIENT/BENEFITS', x)) as x
    				from 
    				(
    					select "Xml" as x
    				) root
    			) BENEFITS;	

            -- Обработка операции
            if (_OperationType = 'FTLoad')
            then
                -- Связываем регистр с пациентами
                -- Поиск по СНИЛС + ДР
                update Tmp_PALLIATIVE_REGISTER pr
                set PatientId = p.PatientID
                from AKUZ.T_PATIENT p
                where p.SNILS = pr.Snils and p.Birthday = pr.Birthday and 
	    			pr.Snils is not null 
	    			and pr.Birthday is not null;

                -- Поиск по серии и номеру документа
                update Tmp_PALLIATIVE_REGISTER pr
                set PatientId = p.PatientID
                from AKUZ.T_PATIENT p
                where LEFT(p.DocSeries, 8) = pr.DocSeries and LEFT(p.DocNumber, 8) = pr.DocNumber and
	    			pr.PatientId is null
	    			and pr.DocSeries is not null
	    			and pr.DocNumber is not null;

                -- Поиск по ФИО + ДР
                update Tmp_PALLIATIVE_REGISTER pr
                set PatientId = p.PatientID
                from AKUZ.T_PATIENT p
                where p.LastName = pr.LastName and p.FirstName = pr.FirstName and (p.FatherName = pr.FatherName or p.FatherName is null and pr.FatherName is null) and p.Birthday = pr.Birthday and pr.PatientId is null;

	    		delete from akuz.T_PATIENT_BENEFITS pb 
	    		    using AKUZ.T_BENEFITS b
	    		where b.BenefitsID = pb.Benefits and b.BenefitViews = 19008;

                -- Загружаем новые
                INSERT INTO AKUZ.T_PATIENT_BENEFITS
                           (PatientBenefitsID ,Patient ,Benefits ,BenefitDocumentNumber ,BeginDate ,EndDate ,Mkb ,Active
                           ,Deleted ,CreateType ,LPU_INF ,RegDate, PAC_ID)
                SELECT
                           uuid_generate_v4()--<PatientBenefitsID, uniqueidentifier,>
                           ,p.PatientID--<Patient, uniqueidentifier,>
                           ,b.BenefitsID--<Benefits, uniqueidentifier,>
                           ,pb.Document--<BenefitDocumentNumber, varchar(255),>
                           ,pb.BeginDate--<BeginDate, datetime,>
                           ,pb.EndDate--<EndDate, datetime,>
                           ,(select m.MkbID from AKUZ.T_MKB m where m.MCBCode = pb.Mkb limit 1)--<Mkb, uniqueidentifier,>
                           ,'1'--<Active, bit,>
                           ,'0'--<Deleted, bit,>
                           ,2--<CreateType, int,>
                           ,pb.Lpu--<LPU_INF, varchar(6),>
                           ,"DateTime"--<RegDate, datetime,>)
	    				   ,pb.RegisterID
                from Tmp_PALLIATIVE_BENEFITS pb
                    join Tmp_PALLIATIVE_REGISTER pr on pr.RegisterID = pb.RegisterID
                    join AKUZ.T_PATIENT p on p.PatientID = pr.PatientId
                    join VCLib.T_ENUMS eBv on eBv.Code = pb.BenefitViews and eBv.EnumTypeID = 190
                    join AKUZ.T_BENEFITS b on b.BenefitCode = pb.BenefitCode and b.BenefitViews = eBv.EnumID;
            END if;

            -- Drop tables
            DROP TABLE Tmp_PALLIATIVE_REGISTER;
            DROP TABLE Tmp_PALLIATIVE_BENEFITS;
         end if;
    End;
End;
$$;