-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-21
-- Description: Загрузка льготников по специальной льготе из RDS
-- =============================================
DROP PROCEDURE IF EXISTS DBO.sp_getsetspecialbenefitslist;

CREATE OR REPLACE PROCEDURE APS.sp_getsetspecialbenefitslist
(
    "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('/SPECIAL_BENEFITS_REGISTR/header[1]/TYPE[1]/text()',"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', "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', "Xml"::xml)) as x
				) t
			where _OperationType <> 'FTLoad';

			if (_OperationType = 'FTLoad')
            then
				delete from APS.T_SPECIAL_BENEFITS_REGISTR;

				INSERT INTO APS.T_SPECIAL_BENEFITS_REGISTR
					(
					   SpecialBenefitsRegistrID
				       ,Adress
				       ,Comment
				       ,DateOfChange
				       ,DateOfDeclusion
				       ,DateOfInclusion
				       ,OmsNumber
				       ,OmsSerial
				       ,UniqueNumber
				       ,Mkb
				       ,Diagnosis
				       ,DocSeries
				       ,DocNumber
				       ,Birthday
				       ,FirstName
				       ,LastName
				       ,FatherName
				       ,DocIssueDate
				       ,DocIssueOrg
				       ,DocumentType
				       ,InclusionLPU
				       ,Sex
				       ,SNILS
				       ,Benefits
					   ,BirthdayLastName
					   ,F002Smo
					)
				SELECT
				        sbr.ID
				       ,sbr.Adress
				       ,sbr.Comment
				       ,sbr.DateOfChange
				       ,sbr.DateOfDeclusion
				       ,sbr.DateOfInclusion
				       ,sbr.OmsNumber
				       ,sbr.OmsSerial
				       ,sbr.UniqueNumber
				       ,sbr.Mkb
				       ,sbr.Diagnosis
				       ,sbr.DocSeries
				       ,sbr.DocNumber
				       ,sbr.Birthday
				       ,sbr.FirstName
				       ,sbr.LastName
				       ,sbr.FatherName
				       ,sbr.DocIssueDate
				       ,sbr.DocIssueOrg
				       ,(select e.EnumID from VCLIB.T_ENUMS e WHERE e.EnumTypeID = 105 and e.Code = sbr.DocumentType limit 1)
				       ,sbr.InclusionLPU
				       ,(select e.EnumID from VCLIB.T_ENUMS e WHERE e.EnumTypeID = 103 and e.Code = sbr.Sex limit 1)
				       ,sbr.SNILS
				       ,(select b.BenefitsID from APS.T_BENEFITS b where b.BenefitCode = sbr.Benefits and b.BenefitViews = sbr.BenefitsViews limit 1)
					   ,sbr.BirthdayLastName
					   ,sbr.F002Smo
				from TMP_SPECIAL_BENEFITS_REGISTR sbr;
			end if;
			elsif (_OperationType = 'Delete')
			then
				delete from APS.T_SPECIAL_BENEFITS_REGISTR sbr
				using TMP_SPECIAL_BENEFITS_REG sb 
				where sb.ID = sbr.SpecialBenefitsRegistrID
			end if;
			else 
			then
				update APS.T_SPECIAL_BENEFITS_REGISTR sbr
				set 
					sbr.Adress				= n.Adress
					,sbr.Comment			= n.Comment
					,sbr.DateOfChange		= n.DateOfChange
					,sbr.DateOfDeclusion	= n.DateOfDeclusion
					,sbr.DateOfInclusion	= n.DateOfInclusion
					,sbr.OmsNumber			= n.OmsNumber
					,sbr.OmsSerial			= n.OmsSerial
					,sbr.UniqueNumber		= n.UniqueNumber
					,sbr.Mkb				= n.Mkb
					,sbr.Diagnosis			= n.Diagnosis
					,sbr.DocSeries			= n.DocSeries
					,sbr.DocNumber			= n.DocNumber
					,sbr.Birthday			= n.Birthday
					,sbr.FirstName			= n.FirstName
					,sbr.LastName			= n.LastName
					,sbr.FatherName			= n.FatherName
					,sbr.DocIssueDate		= n.DocIssueDate
					,sbr.DocIssueOrg		= n.DocIssueOrg
					,sbr.DocumentType		= (select e.EnumID from VCLIB.T_ENUMS e WHERE e.EnumTypeID = 105 and e.Code = n.DocumentType limit 1)
					,sbr.InclusionLPU		= n.InclusionLPU
					,sbr.Sex				= (select e.EnumID from VCLIB.T_ENUMS e WHERE e.EnumTypeID = 103 and e.Code = n.Sex limit 1)
					,sbr.SNILS				= n.SNILS
					,sbr.Benefits			= (select b.BenefitsID from APS.T_BENEFITS b where b.BenefitCode = n.Benefits and b.BenefitViews = n.BenefitsViews limit 1)
					,sbr.BirthdayLastName	= n.BirthdayLastName
					,sbr.F002Smo			= n.F002Smo
					from TMP_SPECIAL_BENEFITS_REG n 
					where n.ID = sbr.SpecialBenefitsRegistrID;

					INSERT INTO APS.T_SPECIAL_BENEFITS_REGISTR
				    (
					   SpecialBenefitsRegistrID
				       ,Adress
				       ,Comment
				       ,DateOfChange
				       ,DateOfDeclusion
				       ,DateOfInclusion
				       ,OmsNumber
				       ,OmsSerial
				       ,UniqueNumber
				       ,Mkb
				       ,Diagnosis
				       ,DocSeries
				       ,DocNumber
				       ,Birthday
				       ,FirstName
				       ,LastName
				       ,FatherName
				       ,DocIssueDate
				       ,DocIssueOrg
				       ,DocumentType
				       ,InclusionLPU
				       ,Sex
				       ,SNILS
				       ,Benefits
					   ,BirthdayLastName
					   ,F002Smo
					)
				SELECT
				        sbr.ID
				       ,sbr.Adress
				       ,sbr.Comment
				       ,sbr.DateOfChange
				       ,sbr.DateOfDeclusion
				       ,sbr.DateOfInclusion
				       ,sbr.OmsNumber
				       ,sbr.OmsSerial
				       ,sbr.UniqueNumber
				       ,sbr.Mkb
				       ,sbr.Diagnosis
				       ,sbr.DocSeries
				       ,sbr.DocNumber
				       ,sbr.Birthday
				       ,sbr.FirstName
				       ,sbr.LastName
				       ,sbr.FatherName
				       ,sbr.DocIssueDate
				       ,sbr.DocIssueOrg
				       ,(select e.EnumID from VCLIB.T_ENUMS e WHERE e.EnumTypeID = 105 and e.Code = sbr.DocumentType limit 1)
				       ,sbr.InclusionLPU
				       ,(select TOP 1 e.EnumID from VCLIB.T_ENUMS e WHERE e.EnumTypeID = 103 and e.Code = sbr.Sex limit 1)
				       ,sbr.SNILS
				       ,(select b.BenefitsID from APS.T_BENEFITS b where b.BenefitCode = sbr.Benefits and b.BenefitViews = sbr.BenefitsViews limit 1)
					   ,sbr.BirthdayLastName
					   ,sbr.F002Smo
				from TMP_SPECIAL_BENEFITS_REG sbr
				where not exists
				(
				    select *
				    from APS.T_SPECIAL_BENEFITS_REGISTR s
				    where s.SpecialBenefitsRegistrID = sbr.ID
				);
			end if;

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