-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-11-18
-- Description: Загрузка регистра региональных льготников.
-- =============================================
DROP PROCEDURE IF EXISTS sp_RR_Patients;

CREATE PROCEDURE sp_RR_Patients
(
	"Xml" text DEFAULT NULL
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" character varying DEFAULT 'rds.registry'::character varying
,	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
,	"User" uuid DEFAULT NULL::uuid
)
    language plpgsql
as
$$
Begin

	Declare
	    _OperationType varchar(255);
		_versionId uuid := gen_random_uuid();
	begin
		"dateTime" := coalesce("dateTime", current_timestamp);
		
		if "Xml" is not null
		then
			-- Получаем тип операции
			_OperationType := (xpath('/RR_PATIENT/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
			
			-- Generate table #RR_PATIENTS
			Drop Table If Exists TmpRR_PATIENTS;
			Create Temp Table TmpRR_PATIENTS
			As
			select
				(xpath('RR_PATIENT/RrPatientID[1]/text()', x))[1]::text::int                          		as RrPatientID
			,	(xpath('RR_PATIENT/Snils[1]/text()', x))[1]::varchar(14)                             		as Snils
			,	(xpath('RR_PATIENT/DocSeries[1]/text()', x))[1]::varchar(16)                             	as DocSeries
			,	(xpath('RR_PATIENT/DocNumber[1]/text()', x))[1]::varchar(16)                             	as DocNumber
			,	(xpath('RR_PATIENT/LastName[1]/text()', x))[1]::varchar(40)                             		as LastName
			,	(xpath('RR_PATIENT/FirstName[1]/text()', x))[1]::varchar(40)                             	as FirstName
			,	(xpath('RR_PATIENT/FatherName[1]/text()', x))[1]::varchar(40)                             	as FatherName		
			,	to_timestamp((xpath('RR_PATIENT/Birthday[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')     	as Birthday
			,	to_timestamp((xpath('RR_PATIENT/DateBegin[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')     as DateBegin		
			,	to_timestamp((xpath('RR_PATIENT/DateEnd[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')     	as DateEnd
			,	to_timestamp((xpath('RR_PATIENT/LastEditDate[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')	as LastEditDate
			,	(xpath('RR_PATIENT/Sex[1]/text()', x))[1]::text::int                                  		as Sex
			,	(xpath('RR_PATIENT/Lpu[1]/text()', x))[1]::varchar(6)                             			as Lpu
			,	(xpath('RR_PATIENT/ExcludeReason[1]/text()', x))[1]::text::int                               as ExcludeReason
			,	(xpath('RR_PATIENT/DocType[1]/text()', x))[1]::text::int                                  	as TIPD
			,	(xpath('RR_PATIENT/Kod[1]/text()', x))[1]::varchar(32)                             			as Kod
			,	(xpath('RR_PATIENT/KodAdr[1]/text()', x))[1]::varchar(11)                             		as KodAdr
			,	to_timestamp((xpath('RR_PATIENT/DAT_PROV[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')     	as DAT_PROV
			,	(xpath('RR_PATIENT/BirthSurname[1]/text()', x))[1]::varchar(40)                             	as BirthSurname
			,	(xpath('RR_PATIENT/LpuFirst[1]/text()', x))[1]::varchar(6)                             		as LpuFirst
			,	(xpath('RR_PATIENT/SOST_R[1]/text()', x))[1]::text::int                             			as SOST_R
			from
			(
				select unnest(xpath('/RR_PATIENT/body/RR_PATIENTS/RR_PATIENT', "Xml"::xml)) as x
			) RR_PATIENT
			where _OperationType = 'FTLoad';
			
			-- Обработка операции
	        if _OperationType = 'FTLoad'
	        then
	        	-- Версия регистра
	            INSERT INTO AKUZ.T_RR_LOAD(Version, UpdateDate, RrLoadID, FileName, LoadType)
	            SELECT (select COUNT(*) from AKUZ.T_RR_LOAD), "dateTime", _versionId, 'RR_PATIENT', 1; -- Полная загрузка
	
	            --удаляем все
	            delete from AKUZ.T_RR_PATIENT;
	
	            -- Загружаем новые
				INSERT INTO AKUZ.T_RR_PATIENT
				           (PAC_ID ,FAM ,IM ,OT ,W ,DROD ,SS ,SDOK ,NDOK
				           ,DATE_RB ,DATE_RE ,DAT_REG ,LPU_INF ,RrLoad ,PRICHINA
				           ,RrPatientID,Kod, KodAdr, DAT_PROV, BirthSurname, LpuMainFirst, SOST_R, TIPD)
				SELECT
				           nrp.RrPatientID--<PAC_ID, int,>
				          ,nrp.LastName--<FAM, varchar(40),>
				          ,nrp.FirstName--<IM, varchar(40),>
				          ,nrp.FatherName--<OT, varchar(40),>
				          ,nrp.Sex--<W, varchar(1),>
				          ,nrp.Birthday--<DROD, datetime,>
				          ,nrp.Snils--<SS, varchar(14),>
				          ,nrp.DocSeries--<SDOK, varchar(8),>
				          ,nrp.DocNumber--<NDOK, varchar(8),>
				          ,nrp.DateBegin--<DATE_RB, datetime,>
				          ,nrp.DateEnd--<DATE_RE, datetime,>
				          ,nrp.LastEditDate--<DAT_REG, datetime,>
				          ,nrp.Lpu--<LPU_INF, varchar(4),>
				          ,_versionId--<RrLoad, uniqueidentifier,>
				          ,coalesce(nrp.ExcludeReason, '0')--<PRICHINA, varchar(1),>
				          ,gen_random_uuid()--<RrPatientID, uniqueidentifier,>)
				          ,nrp.Kod
				          ,nrp.KodAdr
				          ,nrp.DAT_PROV
				          ,nrp.BirthSurname
				          ,nrp.LpuFirst
				          ,nrp.SOST_R
				          ,nrp.TIPD
				from TmpRR_PATIENTS nrp;
	
	            -- Связываем регистр с пациентами
	            -- Поиск по СНИЛС + ДР
	            update AKUZ.T_RR_PATIENT rp
	            set Patient = p.PatientID
	            from AKUZ.T_PATIENT p 
				where p.SNILS = rp.SS and p.Birthday = rp.DROD
	            and rp.SS is not null 
				and rp.SS <> '' 
				and rp.SS <> '              ';
	
	            -- Поиск по серии и номеру документа
	            update AKUZ.T_RR_PATIENT rp
	            set Patient = p.PatientID
	            from AKUZ.T_PATIENT p 
	            where LEFT(p.DocSeries, 8) = rp.SDOK
				and LEFT(p.DocNumber, 8) = rp.NDOK
				and rp.Patient is null 
				and rp.SDOK is not null 
				and rp.SDOK <> '' 
				and rp.NDOK is not null 
				and rp.NDOK  <> '';
	
	            -- Поиск по ФИО + ДР
	            update AKUZ.T_RR_PATIENT rp
	            set Patient = p.PatientID
	            from AKUZ.T_PATIENT p 
	            where p.LastName = rp.FAM 
				and p.FirstName = rp.IM 
				and (p.FatherName = rp.OT or p.FatherName is null and rp.OT is null) 
				and p.Birthday = rp.DROD
				and rp.Patient is null;
	
	        end if;
	
	        -- Drop tables
	        drop table if exists TmpRR_PATIENTS;
	
		end if;
	end;
End;
$$;