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

CREATE PROCEDURE sp_GetSetRDSOrphRegister
(
    "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('/ORPHAN_REGISTER/header[1]/TYPE[1]/text()',"Xml"))[1]::varchar(36);

			-- Generate table TMP_ORPHAN_REGISTERS
			Drop Table If Exists TMP_ORPHAN_REGISTERS;
            Create Temp Table TMP_ORPHAN_REGISTERS
            as
			select (xpath('./ID[1]/text()', x))[1]::text::UUID								as ID
			,to_timestamp((xpath('./DATE_BEGIN[1]/text()', x))[1]::text, 'DD.MM.YYYY')		as DateBegin
			,to_timestamp((xpath('./DATE_END[1]/text()', x))[1]::text, 'DD.MM.YYYY')		as DateEnd
			,(xpath('./FIRST_NAME[1]/text()', x))[1]::varchar(60)							as FirstName
			,(xpath('./LAST_MANE[1]/text()', x))[1]::varchar(60)							as LastName
			,(xpath('./BIRTH_LAST_MANE[1]/text()', x))[1]::varchar(60)						as BirthLastName
			,(xpath('./FATHER_NAME[1]/text()', x))[1]::varchar(60)							as FatherName
			,to_timestamp((xpath('./BIRTHDAY[1]/text()', x))[1]::text, 'DD.MM.YYYY')		as Birthday
			,(xpath('./SEX[1]/text()', x))[1]::text::int									as Sex
			,(xpath('./SNILS[1]/text()', x))[1]::varchar(14)								as SNILS
			,(xpath('./DIASBILITY[1]/text()', x))[1]::text::int								as Disability
			,(xpath('./ADRESS[1]/text()', x))[1]::varchar(1000)								as Adress
			,(xpath('./OKATO[1]/text()', x))[1]::varchar(256)								as OKATO
			,(xpath('./FEDERAL[1]/text()', x))[1]::text::boolean							as Federal
			,to_timestamp((xpath('./DOC_ISSUE_DATE[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as DocIssueDate
			,(xpath('./DOC_ISSUE_ORG[1]/text()', x))[1]::varchar(256)						as DocIssueOrg
			,(xpath('./DOC_TYPE[1]/text()', x))[1]::text::int								as DocType
			,(xpath('./DOC_SERIES[1]/text()', x))[1]::varchar(30)							as DocSeries
			,(xpath('./DOC_NUMBER[1]/text()', x))[1]::varchar(30)							as DocNumber
			,(xpath('./SMO[1]/text()', x))[1]::varchar(5)									as F002Smo
			,(xpath('./OMS_SERIAL[1]/text()', x))[1]::varchar(12)							as OmsSerial
			,(xpath('./OMS_NUMBER[1]/text()', x))[1]::varchar(32)							as OmsNumber
			,(xpath('./MKB[1]/text()', x))[1]::varchar(10)									as Mkb
			,(xpath('./DIAGNOSIS[1]/text()', x))[1]::varchar(4096)							as Diagnosis
			,(xpath('./MKB_DOP[1]/text()', x))[1]::varchar(10)								as MkbDop
			,(xpath('./DIAGNOSIS_DOP[1]/text()', x))[1]::varchar(4096)						as DiagnosisDop
			,(xpath('./LPU_MAIN_FIRST[1]/text()', x))[1]::varchar(6)						as LpuMainFirst
			,(xpath('./LPU_MAIN[1]/text()', x))[1]::varchar(6)								as LpuMain
			,(xpath('./POST_FIO[1]/text()', x))[1]::varchar(255)							as PostFio
			,(xpath('./REGISTER_ID[1]/text()', x))[1]::text::int							as RegisterId
			from
				(
					select	unnest(xpath('/ORPHAN_REGISTER/body/ORPHAN_REGISTERS/ORPHAN_REGISTER_ITEM', "Xml"::xml)) as x
				) t
			where _OperationType = 'FTLoad';

			-- Generate table TMP_ORPHAN_REGISTER
			Drop Table If Exists TMP_ORPHAN_REGISTER;
            Create Temp Table TMP_ORPHAN_REGISTER
            as
			select (xpath('./body[1]/ID[1]/text()', x))[1]::text::UUID								as ID
			,to_timestamp((xpath('./body[1]/DATE_BEGIN[1]/text()', x))[1]::text, 'DD.MM.YYYY')		as DateBegin
			,to_timestamp((xpath('./body[1]/DATE_END[1]/text()', x))[1]::text, 'DD.MM.YYYY')		as DateEnd
			,(xpath('./body[1]/FIRST_NAME[1]/text()', x))[1]::varchar(60)							as FirstName
			,(xpath('./body[1]/LAST_MANE[1]/text()', x))[1]::varchar(60)							as LastName
			,(xpath('./body[1]/BIRTH_LAST_MANE[1]/text()', x))[1]::varchar(60)						as BirthLastName
			,(xpath('./body[1]/FATHER_NAME[1]/text()', x))[1]::varchar(60)							as FatherName
			,to_timestamp( (xpath('./body[1]/BIRTHDAY[1]/text()', x))[1]::text, 'DD.MM.YYYY')		as Birthday
			,(xpath('./body[1]/SEX[1]/text()', x))[1]::text::int									as Sex
			,(xpath('./body[1]/SNILS[1]/text()', x))[1]::varchar(14)								as SNILS
			,(xpath('./body[1]/DIASBILITY[1]/text()', x))[1]::text::int								as Disability
			,(xpath('./body[1]/ADRESS[1]/text()', x))[1]::varchar(1000)								as Adress
			,(xpath('./body[1]/OKATO[1]/text()', x))[1]::varchar(256)								as OKATO
			,(xpath('./body[1]/FEDERAL[1]/text()', x))[1]::text::boolean							as Federal
			,to_timestamp((xpath('./body[1]/DOC_ISSUE_DATE[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as DocIssueDate
			,(xpath('./body[1]/DOC_ISSUE_ORG[1]/text()', x))[1]::varchar(256)						as DocIssueOrg
			,(xpath('./body[1]/DOC_TYPE[1]/text()', x))[1]::text::int								as DocType
			,(xpath('./body[1]/DOC_SERIES[1]/text()', x))[1]::varchar(30)							as DocSeries
			,(xpath('./body[1]/DOC_NUMBER[1]/text()', x))[1]::varchar(30)							as DocNumber
			,(xpath('./body[1]/SMO[1]/text()', x))[1]::varchar(5)									as F002Smo
			,(xpath('./body[1]/OMS_SERIAL[1]/text()', x))[1]::varchar(12)							as OmsSerial
			,(xpath('./body[1]/OMS_NUMBER[1]/text()', x))[1]::varchar(32)							as OmsNumber
			,(xpath('./body[1]/MKB[1]/text()', x))[1]::varchar(10)									as Mkb
			,(xpath('./body[1]/DIAGNOSIS[1]/text()', x))[1]::varchar(4096)							as Diagnosis
			,(xpath('./body[1]/MKB_DOP[1]/text()', x))[1]::varchar(10)								as MkbDop
			,(xpath('./body[1]/DIAGNOSIS_DOP[1]/text()', x))[1]::varchar(4096)						as DiagnosisDop
			,(xpath('./body[1]/LPU_MAIN_FIRST[1]/text()', x))[1]::varchar(6)						as LpuMainFirst
			,(xpath('./body[1]/LPU_MAIN[1]/text()', x))[1]::varchar(6)								as LpuMain
			,(xpath('./body[1]/POST_FIO[1]/text()', x))[1]::varchar(255)							as PostFio
			,(xpath('./body[1]/REGISTER_ID[1]/text()', x))[1]::text::int							as RegisterId
			from
				(
					select	unnest(xpath('/ORPHAN_REGISTER', "Xml"::xml)) as x
				) t
			where _OperationType <> 'FTLoad';

			ALTER TABLE TMP_ORPHAN_REGISTER ADD Patient uuid;
			ALTER TABLE TMP_ORPHAN_REGISTERS ADD Patient uuid;

			-- Связываем регистр с пациентами
			-- Поиск по СНИЛС + ДР
			update TMP_ORPHAN_REGISTER orph
			set Patient = p.PatientID
			from AKUZ.T_PATIENT p
			where p.SNILS = orph.SNILS and p.Birthday = orph.Birthday and
				orph.Patient is null
				and orph.SNILS is not null
				and orph.Birthday is not null;

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

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

			-- Поиск по СНИЛС + ДР
			update TMP_ORPHAN_REGISTERS orph
			set Patient = p.PatientID
			from AKUZ.T_PATIENT p 
			where p.SNILS = orph.SNILS and p.Birthday = orph.Birthday and
				orph.Patient is null
				and orph.SNILS is not null
			    and orph.Birthday is not null;

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

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

			if (_OperationType = 'FTLoad')
			then

				delete from AKUZ.T_PATIENT_BENEFITS pd 
					using AKUZ.T_BENEFITS b
				where b.BenefitsID = pd.Benefits and b.BenefitViews = 19011 and CreateType = 2;

				INSERT INTO AKUZ.T_PATIENT_BENEFITS
			           (PatientBenefitsID
			           ,Patient
			           ,Benefits
			           ,BenefitDocument
			           ,BenefitDocumentNumber
			           ,BeginDate
			           ,EndDate
			           ,Mkb
			           ,Active
			           ,Deleted
			           ,CreateType
			           ,PAC_ID
			           ,LPU_INF
			           ,SOST_R
			           ,ReasonRegistration
			           ,ReasonDeregistration
			           ,BenefitDocumentSeries
			           ,BenefitDocType
			           ,RegDate)
			     SELECT
			           uuid_generate_v4()
			           ,orph.Patient
			           ,(select b.BenefitsID from AKUZ.T_BENEFITS b where b.BenefitViews = 19011 limit 1)
			           ,null
			           ,null
			           ,orph.DateBegin
			           ,orph.DateEnd
			           ,(select m.MkbID from AKUZ.T_MKB m where orph.Mkb = m.MCBCode limit 1)
			           ,'1'
			           ,'0'
			           ,2
			           ,orph.RegisterId
			           ,orph.LpuMain
			           ,null
			           ,null
			           ,null
			           ,null
			           ,null
			           ,orph.DateBegin
					   FROM TMP_ORPHAN_REGISTERS orph
					   where orph.Patient is not null;

			elsif(_OperationType = 'Delete')
			then

				delete from AKUZ.T_PATIENT_BENEFITS pd 
					using AKUZ.T_BENEFITS b 
					join TMP_ORPHAN_REGISTER orph on orph.RegisterId = pd.PAC_ID
				where b.BenefitsID = pd.Benefits and b.BenefitViews = 19011 and pd.CreateType = 2;

			ELSE
				delete from AKUZ.T_PATIENT_BENEFITS pd 
					using AKUZ.T_BENEFITS b
					join TMP_ORPHAN_REGISTER orph on orph.RegisterId = pd.PAC_ID
				where b.BenefitsID = pd.Benefits and b.BenefitViews = 19011 and pd.CreateType = 2;

				INSERT INTO AKUZ.T_PATIENT_BENEFITS
			           (PatientBenefitsID
			           ,Patient
			           ,Benefits
			           ,BenefitDocument
			           ,BenefitDocumentNumber
			           ,BeginDate
			           ,EndDate
			           ,Mkb
			           ,Active
			           ,Deleted
			           ,CreateType
			           ,PAC_ID
			           ,LPU_INF
			           ,SOST_R
			           ,ReasonRegistration
			           ,ReasonDeregistration
			           ,BenefitDocumentSeries
			           ,BenefitDocType
			           ,RegDate)
			     SELECT
			           uuid_generate_v4()
			           ,orph.Patient
			           ,(select b.BenefitsID from AKUZ.T_BENEFITS b where b.BenefitViews = 19011 limit 1)
			           ,null
			           ,null
			           ,orph.DateBegin
			           ,orph.DateEnd
			           ,(select m.MkbID from AKUZ.T_MKB m where orph.Mkb = m.MCBCode limit 1)
			           ,'1'
			           ,'0'
			           ,2
			           ,orph.RegisterId
			           ,orph.LpuMain
			           ,null
			           ,null
			           ,null
			           ,null
			           ,null
			           ,orph.DateBegin
					   FROM TMP_ORPHAN_REGISTER orph
					   where orph.Patient is not null;

			END if;

		DROP TABLE TMP_ORPHAN_REGISTER;
		DROP TABLE TMP_ORPHAN_REGISTERS;
        end if;
    End;
End;
$$;