-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-11-03
-- Description: Загрузка льготников по 12 ВЗН
-- =============================================
DROP PROCEDURE IF EXISTS sp_GetSetRDSNosology;

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

            -- Generate table TMP_NOSOLOGYS
			Drop Table If Exists TMP_NOSOLOGYS;
            Create Temp Table TMP_NOSOLOGYS
            as
			select (xpath('NOSOLOGY_ITEM/ID[1]/text()', x))[1]::text::UUID								as ID
			,(xpath('NOSOLOGY_ITEM/ADRESS[1]/text()', x))[1]::varchar(256)									as Adress
			,(xpath('NOSOLOGY_ITEM/CARD_STATUS[1]/text()', x))[1]::varchar(256)								as CardStatus
			,(xpath('NOSOLOGY_ITEM/COMMENT[1]/text()', x))[1]::varchar(1024)									as Comment
			,to_timestamp((xpath('NOSOLOGY_ITEM/CHANGE_DATE[1]/text()', x))[1]::text, 'DD.MM.YYYY')		as DateOfChange
			,to_timestamp((xpath('NOSOLOGY_ITEM/DECLUSION_DATE[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as DateOfDeclusion
			,to_timestamp((xpath('NOSOLOGY_ITEM/INCLUSION_DATE[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as DateOfInclusion
			,(xpath('NOSOLOGY_ITEM/EXECUTIVE_AUTHORITY[1]/text()', x))[1]::varchar(256)						as ExecutiveAuthority
			,(xpath('NOSOLOGY_ITEM/HEAD[1]/text()', x))[1]::varchar(256)										as Head
			,(xpath('NOSOLOGY_ITEM/OMS_NUMBER[1]/text()', x))[1]::varchar(30)								as OmsNumber
			,(xpath('NOSOLOGY_ITEM/OMS_SERIAL[1]/text()', x))[1]::varchar(30)								as OmsSerial
			,(xpath('NOSOLOGY_ITEM/OPERATION_CODE[1]/text()', x))[1]::varchar(128)							as OperationCode
			,(xpath('NOSOLOGY_ITEM/UNIQUE_NUMBER[1]/text()', x))[1]::varchar(128)							as UniqueNumber
			,(xpath('NOSOLOGY_ITEM/MKB[1]/text()', x))[1]::varchar(10)										as Mkb
			,(xpath('NOSOLOGY_ITEM/DIAGNOSIS[1]/text()', x))[1]::varchar(4096)								as Diagnosis
			,(xpath('NOSOLOGY_ITEM/DOC_SERIES[1]/text()', x))[1]::varchar(30)								as DocSeries
			,(xpath('NOSOLOGY_ITEM/DOC_NUMBER[1]/text()', x))[1]::varchar(30)								as DocNumber
			,to_timestamp((xpath('NOSOLOGY_ITEM/BIRTHDAY[1]/text()', x))[1]::text, 'DD.MM.YYYY')			as Birthday
			,(xpath('NOSOLOGY_ITEM/FIRST_NAME[1]/text()', x))[1]::varchar(60)								as FirstName
			,(xpath('NOSOLOGY_ITEM/LAST_NAME[1]/text()', x))[1]::varchar(60)									as LastName
			,(xpath('NOSOLOGY_ITEM/FATHER_NAME[1]/text()', x))[1]::varchar(60)								as FatherName
			,to_timestamp((xpath('NOSOLOGY_ITEM/DOC_ISSUE_DATE[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as DocIssueDate
			,(xpath('NOSOLOGY_ITEM/DOC_ISSUE_ORG[1]/text()', x))[1]::varchar(256)							as DocIssueOrg
			,(xpath('NOSOLOGY_ITEM/DECLUSION_LPU[1]/text()', x))[1]::varchar(6)								as DeclusionLPU
			,(xpath('NOSOLOGY_ITEM/DOC_TYPE[1]/text()', x))[1]::text::int											as DocumentType
			,(xpath('NOSOLOGY_ITEM/EX178[1]/text()', x))[1]::text::int												as ExemptionBy178
			,(xpath('NOSOLOGY_ITEM/EX890[1]/text()', x))[1]::text::int												as ExemptionBy890
			,(xpath('NOSOLOGY_ITEM/INCLUSION_LPU[1]/text()', x))[1]::varchar(6)								as InclusionLPU
			,(xpath('NOSOLOGY_ITEM/REGION[1]/text()', x))[1]::varchar(13)									as Region
			,(xpath('NOSOLOGY_ITEM/SEX[1]/text()', x))[1]::text::int												as Sex
			,(xpath('NOSOLOGY_ITEM/RF_ACCOUNT[1]/text()', x))[1]::text::int										as SubjectOfRFAccounted
			,(xpath('NOSOLOGY_ITEM/SNILS[1]/text()', x))[1]::varchar(14)										as SNILS
			,(xpath('NOSOLOGY_ITEM/BENIFITS[1]/text()', x))[1]::varchar(500)									as Benefits
			,(xpath('NOSOLOGY_ITEM/BIRTHDAY_LAST_NAME[1]/text()', x))[1]::varchar(60)						as BirthdayLastName
			,(xpath('NOSOLOGY_ITEM/DIASBILITY[1]/text()', x))[1]::text::int										as Disability
			,(xpath('NOSOLOGY_ITEM/THERAPY_RESISTANCE[1]/text()', x))[1]::text::int								as TherapyResistance
			,to_timestamp((xpath('NOSOLOGY_ITEM/DEATH_DATE[1]/text()', x))[1]::text, 'DD.MM.YYYY')		as DeathDate
			,(xpath('NOSOLOGY_ITEM/OKATO[1]/text()', x))[1]::varchar(20)									as OKATO
			,(xpath('NOSOLOGY_ITEM/SMO[1]/text()', x))[1]::varchar(5)										as F002Smo
			from
				(
					select	unnest(xpath('/NOSOLOGY/body/NOSOLOGY_ITEMS/NOSOLOGY_ITEM', "Xml"::xml)) as x
				) NOSOLOGY_ITEM
			where _OperationType = 'FTLoad';

			-- Generate table TMP_NOSOLOGY
			Drop Table If Exists TMP_NOSOLOGY;
            Create Temp Table TMP_NOSOLOGY
            as
			select (xpath('NOSOLOGY/header[1]/ID[1]/text()', x))[1]::text::UUID							as ID
			,(xpath('NOSOLOGY/body[1]/ADRESS[1]/text()', x))[1]::varchar(256)									as Adress
			,(xpath('NOSOLOGY/body[1]/CARD_STATUS[1]/text()', x))[1]::varchar(256)								as CardStatus
			,(xpath('NOSOLOGY/body[1]/COMMENT[1]/text()', x))[1]::varchar(1024)									as Comment
			,to_timestamp((xpath('NOSOLOGY/body[1]/CHANGE_DATE[1]/text()', x))[1]::text, 'DD.MM.YYYY')		as DateOfChange
			,to_timestamp((xpath('NOSOLOGY/body[1]/DECLUSION_DATE[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as DateOfDeclusion
			,to_timestamp((xpath('NOSOLOGY/body[1]/INCLUSION_DATE[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as DateOfInclusion
			,(xpath('NOSOLOGY/body[1]/EXECUTIVE_AUTHORITY[1]/text()', x))[1]::varchar(256)						as ExecutiveAuthority
			,(xpath('NOSOLOGY/body[1]/HEAD[1]/text()', x))[1]::varchar(256)										as Head
			,(xpath('NOSOLOGY/body[1]/OMS_NUMBER[1]/text()', x))[1]::varchar(30)								as OmsNumber
			,(xpath('NOSOLOGY/body[1]/OMS_SERIAL[1]/text()', x))[1]::varchar(30)								as OmsSerial
			,(xpath('NOSOLOGY/body[1]/OPERATION_CODE[1]/text()', x))[1]::varchar(128)							as OperationCode
			,(xpath('NOSOLOGY/body[1]/UNIQUE_NUMBER[1]/text()', x))[1]::varchar(128)							as UniqueNumber
			,(xpath('NOSOLOGY/body[1]/MKB[1]/text()', x))[1]::varchar(10)										as Mkb
			,(xpath('NOSOLOGY/body[1]/DIAGNOSIS[1]/text()', x))[1]::varchar(4096)								as Diagnosis
			,(xpath('NOSOLOGY/body[1]/DOC_SERIES[1]/text()', x))[1]::varchar(30)								as DocSeries
			,(xpath('NOSOLOGY/body[1]/DOC_NUMBER[1]/text()', x))[1]::varchar(30)								as DocNumber
			,to_timestamp((xpath('NOSOLOGY/body[1]/BIRTHDAY[1]/text()', x))[1]::text, 'DD.MM.YYYY')			as Birthday
			,(xpath('NOSOLOGY/body[1]/FIRST_NAME[1]/text()', x))[1]::varchar(60)								as FirstName
			,(xpath('NOSOLOGY/body[1]/LAST_NAME[1]/text()', x))[1]::varchar(60)									as LastName
			,(xpath('NOSOLOGY/body[1]/FATHER_NAME[1]/text()', x))[1]::varchar(60)								as FatherName
			,to_timestamp((xpath('NOSOLOGY/body[1]/DOC_ISSUE_DATE[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as DocIssueDate
			,(xpath('NOSOLOGY/body[1]/DOC_ISSUE_ORG[1]/text()', x))[1]::varchar(256)							as DocIssueOrg
			,(xpath('NOSOLOGY/body[1]/DECLUSION_LPU[1]/text()', x))[1]::varchar(6)								as DeclusionLPU
			,(xpath('NOSOLOGY/body[1]/DOC_TYPE[1]/text()', x))[1]::text::int											as DocumentType
			,(xpath('NOSOLOGY/body[1]/EX178[1]/text()', x))[1]::text::int												as ExemptionBy178
			,(xpath('NOSOLOGY/body[1]/EX890[1]/text()', x))[1]::text::int												as ExemptionBy890
			,(xpath('NOSOLOGY/body[1]/INCLUSION_LPU[1]/text()', x))[1]::varchar(6)								as InclusionLPU
			,(xpath('NOSOLOGY/body[1]/REGION[1]/text()', x))[1]::varchar(13)									as Region
			,(xpath('NOSOLOGY/body[1]/SEX[1]/text()', x))[1]::text::int												as Sex
			,(xpath('NOSOLOGY/body[1]/RF_ACCOUNT[1]/text()', x))[1]::text::int										as SubjectOfRFAccounted
			,(xpath('NOSOLOGY/body[1]/SNILS[1]/text()', x))[1]::varchar(14)										as SNILS
			,(xpath('NOSOLOGY/body[1]/BENIFITS[1]/text()', x))[1]::varchar(500)									as Benefits
			,(xpath('NOSOLOGY/body[1]/BIRTHDAY_LAST_NAME[1]/text()', x))[1]::varchar(60)						as BirthdayLastName
			,(xpath('NOSOLOGY/body[1]/DIASBILITY[1]/text()', x))[1]::text::int										as Disability
			,(xpath('NOSOLOGY/body[1]/THERAPY_RESISTANCE[1]/text()', x))[1]::text::int								as TherapyResistance
			,to_timestamp((xpath('NOSOLOGY/body[1]/DEATH_DATE[1]/text()', x))[1]::text, 'DD.MM.YYYY')			as DeathDate
			,(xpath('NOSOLOGY/body[1]/OKATO[1]/text()', x))[1]::varchar(20)									as OKATO
			,(xpath('NOSOLOGY/body[1]/SMO[1]/text()', x))[1]::varchar(5)										as F002Smo
			from
				(
					select	unnest(xpath('/NOSOLOGY', "Xml"::xml)) as x
				) NOSOLOGY
			where _OperationType <> 'FTLoad';

			ALTER TABLE TMP_NOSOLOGY ADD Patient uuid;
			ALTER TABLE TMP_NOSOLOGYS ADD Patient uuid;

			-- Связываем регистр с пациентами
			 -- Поиск по СНИЛС + ДР
			 update TMP_NOSOLOGY 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_NOSOLOGY 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_NOSOLOGY 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_NOSOLOGYS 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_NOSOLOGYS 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_NOSOLOGYS 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 = 19006 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()
			           ,nos.Patient
			           ,(select b.BenefitsID from AKUZ.T_BENEFITS b 
					   where b.BenefitCode = nos.Benefits and b.BenefitViews = 19006 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_NOSOLOGYS 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_NOSOLOGY nos on nos.Patient = pd.Patient
			where b.BenefitsID = pd.Benefits and b.BenefitViews = 19006 and pd.CreateType = 2 and b.BenefitCode = nos.Benefits;
			
			ELSE
				delete from AKUZ.T_PATIENT_BENEFITS pd 
					using AKUZ.T_BENEFITS b
					join TMP_NOSOLOGY nos on nos.Patient = pd.Patient
				where b.BenefitsID = pd.Benefits and b.BenefitViews = 19006 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
			           uuid_generate_v4()
			           ,nos.Patient
			           ,(select b.BenefitsID from AKUZ.T_BENEFITS b 
					   where b.BenefitCode = nos.Benefits and b.BenefitViews = 19006 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_NOSOLOGY nos
					   where nos.Patient is not null;
			END if;

		DROP TABLE TMP_NOSOLOGY;
		DROP TABLE TMP_NOSOLOGYS;
        end if;
    End;
End;
$$;