-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-02-07
-- Description: Загрузка льготников по специальной льготе из RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_GetSetSpecialBenefitsList;

CREATE PROCEDURE sp_GetSetSpecialBenefitsList
(
	"Xml" text DEFAULT null
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" character varying DEFAULT 'RDSSync'::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(36);
    p_xml           xml          := "Xml" :: xml;
    Begin
        IF (p_xml is not null)
        then
	    	"dateTime" := coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
            
            -- Получаем тип операции		
			select into
				_OperationType
				(xpath('/SPECIAL_BENEFITS_REGISTR/header[1]/TYPE[1]/text()',p_xml))[1]::varchar(36);

			-- Generate table TMP_SPECIAL_BENEFITS_REGISTR
			Drop Table If Exists TMP_SPECIAL_BENEFITS_REGISTR;
            Create Temp Table TMP_SPECIAL_BENEFITS_REGISTR
            as
			select (xpath('SPECIAL_BENEFITS_REGISTR_ITEM/ID[1]/text()', x))[1]::text::UUID								as ID
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/ADRESS[1/text()', x))[1]::varchar(256)								as Adress
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/COMMENT[1/text()', x))[1]::varchar(1024)								as Comment
			,to_timestamp((xpath('SPECIAL_BENEFITS_REGISTR_ITEM/CHANGE_DATE[1/text()', x))[1]::text, 'DD.MM.YYYY')		as DateOfChange
			,to_timestamp((xpath('SPECIAL_BENEFITS_REGISTR_ITEM/DECLUSION_DATE[1/text()', x))[1]::text, 'DD.MM.YYYY')	as DateOfDeclusion
			,to_timestamp((xpath('SPECIAL_BENEFITS_REGISTR_ITEM/INCLUSION_DATE[1/text()', x))[1]::text, 'DD.MM.YYYY')	as DateOfInclusion
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/OMS_NUMBER[1/text()', x))[1]::varchar(30)							as OmsNumber
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/OMS_SERIAL[1/text()', x))[1]::varchar(30)							as OmsSerial
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/UNIQUE_NUMBER[1/text()', x))[1]::varchar(128)						as UniqueNumber
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/MKB[1/text()', x))[1]::varchar(10)									as Mkb
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/DIAGNOSIS[1/text()', x))[1]::varchar(4096)							as Diagnosis
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/DOC_SERIES[1/text()', x))[1]::varchar(30)							as DocSeries
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/DOC_NUMBER[1/text()', x))[1]::varchar(30)							as DocNumber
			,to_timestamp((xpath('SPECIAL_BENEFITS_REGISTR_ITEM/BIRTHDAY[1/text()', x))[1]::text, 'DD.MM.YYYY')			as Birthday
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/FIRST_NAME[1/text()', x))[1]::varchar(60)							as FirstName
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/LAST_MANE[1/text()', x))[1]::varchar(60)								as LastName
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/FATHER_NAME[1/text()', x))[1]::varchar(60)							as FatherName
			,to_timestamp((xpath('SPECIAL_BENEFITS_REGISTR_ITEM/DOC_ISSUE_DATE[1/text()', x))[1]::text, 'DD.MM.YYYY')	as DocIssueDate
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/DOC_ISSUE_ORG[1/text()', x))[1]::varchar(256)						as DocIssueOrg
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/DOC_TYPE[1]/text()', x))[1]::text::int								as DocumentType
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/INCLUSION_LPU[1/text()', x))[1]::varchar(6)							as InclusionLPU
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/SEX[1]/text()', x))[1]::text::int									as Sex
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/BIRTHDAY_LAST_NAME[1/text()', x))[1]::varchar(60)					as BirthdayLastName
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/SMO[1/text()', x))[1]::varchar(5)									as F002Smo
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/SNILS[1/text()', x))[1]::varchar(14)									as SNILS
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/BENIFITS[1/text()', x))[1]::varchar(255)								as Benefits
			,(xpath('SPECIAL_BENEFITS_REGISTR_ITEM/BENIFITS_VIEWS[1]/text()', x))[1]::text::int							as BenefitViews
			from
				(
					select	unnest(xpath('/SPECIAL_BENEFITS_REGISTR/body/SPECIAL_BENEFITS_REGISTR_ITEMS/SPECIAL_BENEFITS_REGISTR_ITEM', p_xml::xml)) as x
				) SPECIAL_BENEFITS_REGISTR_ITEM
			where _OperationType = 'FTLoad';

			-- Generate table TMP_SPECIAL_BENEFITS_REGISTR
			Drop Table If Exists TMP_SPECIAL_BENEFITS_REGISTR;
            Create Temp Table TMP_SPECIAL_BENEFITS_REGISTR
            as
			select (xpath('SPECIAL_BENEFITS_REGISTR/header[1]/ID[1]/text()', x))[1]::text::UUID							as ID
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/ADRESS[1/text()', x))[1]::varchar(256)								as Adress
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/COMMENT[1/text()', x))[1]::varchar(1024)								as Comment
			,to_timestamp((xpath('SPECIAL_BENEFITS_REGISTR/body[1]/CHANGE_DATE[1/text()', x))[1]::text, 'DD.MM.YYYY')		as DateOfChange
			,to_timestamp((xpath('SPECIAL_BENEFITS_REGISTR/body[1]/DECLUSION_DATE[1/text()', x))[1]::text, 'DD.MM.YYYY')	as DateOfDeclusion
			,to_timestamp((xpath('SPECIAL_BENEFITS_REGISTR/body[1]/INCLUSION_DATE[1/text()', x))[1]::text, 'DD.MM.YYYY')	as DateOfInclusion
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/OMS_NUMBER[1/text()', x))[1]::varchar(30)							as OmsNumber
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/OMS_SERIAL[1/text()', x))[1]::varchar(30)							as OmsSerial
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/UNIQUE_NUMBER[1/text()', x))[1]::varchar(128)						as UniqueNumber
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/MKB[1/text()', x))[1]::varchar(10)									as Mkb
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/DIAGNOSIS[1/text()', x))[1]::varchar(4096)							as Diagnosis
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/DOC_SERIES[1/text()', x))[1]::varchar(30)							as DocSeries
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/DOC_NUMBER[1/text()', x))[1]::varchar(30)							as DocNumber
			,to_timestamp((xpath('SPECIAL_BENEFITS_REGISTR/body[1]/BIRTHDAY[1/text()', x))[1]::text, 'DD.MM.YYYY')			as Birthday
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/FIRST_NAME[1/text()', x))[1]::varchar(60)							as FirstName
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/LAST_MANE[1/text()', x))[1]::varchar(60)								as LastName
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/FATHER_NAME[1/text()', x))[1]::varchar(60)							as FatherName
			,to_timestamp((xpath('SPECIAL_BENEFITS_REGISTR/body[1]/DOC_ISSUE_DATE[1/text()', x))[1]::text, 'DD.MM.YYYY')	as DocIssueDate
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/DOC_ISSUE_ORG[1/text()', x))[1]::varchar(256)						as DocIssueOrg
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/DOC_TYPE[1]/text()', x))[1]::text::int								as DocumentType
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/INCLUSION_LPU[1/text()', x))[1]::varchar(6)							as InclusionLPU
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/SEX[1]/text()', x))[1]::text::int									as Sex
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/BIRTHDAY_LAST_NAME[1/text()', x))[1]::varchar(60)					as BirthdayLastName
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/SMO[1/text()', x))[1]::varchar(5)									as F002Smo
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/SNILS[1/text()', x))[1]::varchar(14)									as SNILS
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/BENIFITS[1/text()', x))[1]::varchar(255)								as Benefits
			,(xpath('SPECIAL_BENEFITS_REGISTR/body[1]/BENIFITS_VIEWS[1]/text()', x))[1]::text::int							as BenefitViews
			from
				(
					select	unnest(xpath('/SPECIAL_BENEFITS_REGISTR', p_xml::xml)) as x
				) t
			where _OperationType <> 'FTLoad';


			ALTER TABLE TMP_SPECIAL_BENEFITS_REGISTR ADD Patient uuid;
			ALTER TABLE TMP_SPECIAL_BENEFITS_REG ADD Patient uuid;

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

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

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

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

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

			    -- Поиск по ФИО + ДР
			    update TMP_SPECIAL_BENEFITS_REG nos
			    set Patient = p.PatientID
			    from AKUZ.T_PATIENT p 
			    where p.LastName = nos.LastName and  p.FirstName = nos.FirstName and (p.FatherName = nos.FatherName or p.FatherName is null and nos.FatherName is null) and p.Birthday = nos.Birthday and nos.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 = 19009 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
			           newid()
			           ,nos.Patient
			           ,(select b.BenefitsID from AKUZ.T_BENEFITS b 
					   where b.BenefitCode = nos.Benefits and b.BenefitViews = 19009 limit 1)
			           ,null
			           ,null
			           ,nos.DateOfInclusion
			           ,nos.DateOfDeclusion
			           ,(select m.MkbID from AKUZ.T_MKB m where nos.Mkb = m.MCBCode limit 1)
			           ,'1'
			           ,'0'
			           ,2
			           ,nos.UniqueNumber
			           ,nos.InclusionLPU
			           ,null
			           ,null
			           ,null
			           ,null
			           ,null
			           ,nos.DateOfInclusion
				FROM TMP_SPECIAL_BENEFITS_REGISTR nos
				where nos.Patient is not null;

			elsif(_OperationType = 'Delete')
			then
			delete from AKUZ.T_PATIENT_BENEFITS pd  
				using AKUZ.T_BENEFITS b 
				join TMP_SPECIAL_BENEFITS_REG nos on nos.Patient = pd.Patient
			where b.BenefitsID = pd.Benefits and b.BenefitViews = 19009 and pd.CreateType = 2 and b.BenefitCode = nos.Benefits;

			ELSE
			
			delete from AKUZ.T_PATIENT_BENEFITS pd 
				using AKUZ.T_BENEFITS b 
				join TMP_SPECIAL_BENEFITS_REG nos on nos.Patient = pd.Patient
			where b.BenefitsID = pd.Benefits and b.BenefitViews = 19009 and pd.CreateType = 2 and b.BenefitCode = nos.Benefits;

			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
			           newid()
			           ,nos.Patient
			           ,(select b.BenefitsID from AKUZ.T_BENEFITS b 
					   where b.BenefitCode = nos.Benefits and b.BenefitViews = 19009 limit 1)
			           ,null
			           ,null
			           ,nos.DateOfInclusion
			           ,nos.DateOfDeclusion
			           ,(select m.MkbID from AKUZ.T_MKB m where nos.Mkb = m.MCBCode limit 1)
			           ,'1'
			           ,'0'
			           ,2
			           ,nos.UniqueNumber
			           ,nos.InclusionLPU
			           ,null
			           ,null
			           ,null
			           ,null
			           ,null
			           ,nos.DateOfInclusion
					   FROM TMP_SPECIAL_BENEFITS_REG nos
					   where nos.Patient is not null;

				END if;

			DROP TABLE TMP_SPECIAL_BENEFITS_REGISTR;
			DROP TABLE TMP_SPECIAL_BENEFITS_REG;
        end if;
    End;
End;
$$;