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

CREATE OR REPLACE PROCEDURE APS.sp_getsetnosology
(
    "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/header1/TYPE1/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/ID1/text()', x))[1]::text::UUID								as ID
			,(xpath('NOSOLOGY_ITEM/ADRESS1/text()', x))[1]::varchar(256)									as Adress
			,to_timestamp((xpath('NOSOLOGY_ITEM/AUTO_DATE_CHANGE1/text()', x))[1]::text, 'DD.MM.YYYY')	as AutoDateOfChange
			,(xpath('NOSOLOGY_ITEM/CARD_STATUS1/text()', x))[1]::varchar(256)								as CardStatus
			,(xpath('NOSOLOGY_ITEM/COMMENT1/text()', x))[1]::varchar(1024)									as Comment
			,to_timestamp((xpath('NOSOLOGY_ITEM/CHANGE_DATE1/text()', x))[1]::text, 'DD.MM.YYYY')		as DateOfChange
			,to_timestamp((xpath('NOSOLOGY_ITEM/DECLUSION_DATE1/text()', x))[1]::text, 'DD.MM.YYYY')	as DateOfDeclusion
			,to_timestamp((xpath('NOSOLOGY_ITEM/INCLUSION_DATE1/text()', x))[1]::text, 'DD.MM.YYYY')	as DateOfInclusion
			,(xpath('NOSOLOGY_ITEM/EXECUTIVE_AUTHORITY1/text()', x))[1]::varchar(256)						as ExecutiveAuthority
			,(xpath('NOSOLOGY_ITEM/HEAD1/text()', x))[1]::varchar(256)										as Head
			,(xpath('NOSOLOGY_ITEM/OMS_NUMBER1/text()', x))[1]::varchar(30)								as OmsNumber
			,(xpath('NOSOLOGY_ITEM/OMS_SERIAL1/text()', x))[1]::varchar(30)								as OmsSerial
			,(xpath('NOSOLOGY_ITEM/OPERATION_CODE1/text()', x))[1]::varchar(128)							as OperationCode
			,(xpath('NOSOLOGY_ITEM/UNIQUE_NUMBER1/text()', x))[1]::varchar(128)							as UniqueNumber
			,(xpath('NOSOLOGY_ITEM/MKB1/text()', x))[1]::varchar(10)										as Mkb
			,(xpath('NOSOLOGY_ITEM/DIAGNOSIS1/text()', x))[1]::varchar(4096)								as Diagnosis
			,(xpath('NOSOLOGY_ITEM/DOC_SERIES1/text()', x))[1]::varchar(30)								as DocSeries
			,(xpath('NOSOLOGY_ITEM/DOC_NUMBER1/text()', x))[1]::varchar(30)								as DocNumber
			,to_timestamp((xpath('NOSOLOGY_ITEM/BIRTHDAY1/text()', x))[1]::text, 'DD.MM.YYYY')			as Birthday
			,(xpath('NOSOLOGY_ITEM/FIRST_NAME1/text()', x))[1]::varchar(60)								as FirstName
			,(xpath('NOSOLOGY_ITEM/LAST_NAME1/text()', x))[1]::varchar(60)									as LastName
			,(xpath('NOSOLOGY_ITEM/FATHER_NAME1/text()', x))[1]::varchar(60)								as FatherName
			,to_timestamp((xpath('NOSOLOGY_ITEM/DOC_ISSUE_DATE1/text()', x))[1]::text, 'DD.MM.YYYY')	as DocIssueDate
			,(xpath('NOSOLOGY_ITEM/DOC_ISSUE_ORG1/text()', x))[1]::varchar(256)							as DocIssueOrg
			,(xpath('NOSOLOGY_ITEM/DECLUSION_LPU1/text()', x))[1]::varchar(6)								as DeclusionLPU
			,(xpath('NOSOLOGY_ITEM/DOC_TYPE1/text()', x))[1]::text::int											as DocumentType
			,(xpath('NOSOLOGY_ITEM/EX1781/text()', x))[1]::text::int												as ExemptionBy178
			,(xpath('NOSOLOGY_ITEM/EX8901/text()', x))[1]::text::int												as ExemptionBy890
			,(xpath('NOSOLOGY_ITEM/INCLUSION_LPU1/text()', x))[1]::varchar(6)								as InclusionLPU
			,(xpath('NOSOLOGY_ITEM/REGION1/text()', x))[1]::varchar(13)									as Region
			,(xpath('NOSOLOGY_ITEM/SEX1/text()', x))[1]::text::int												as Sex
			,(xpath('NOSOLOGY_ITEM/RF_ACCOUNT1/text()', x))[1]::text::int										as SubjectOfRFAccounted
			,(xpath('NOSOLOGY_ITEM/SNILS1/text()', x))[1]::varchar(14)										as SNILS
			,(xpath('NOSOLOGY_ITEM/BENIFITS1/text()', x))[1]::varchar(500)									as Benefits
			,(xpath('NOSOLOGY_ITEM/BIRTHDAY_LAST_NAME1/text()', x))[1]::varchar(60)						as BirthdayLastName
			,(xpath('NOSOLOGY_ITEM/DIASBILITY1/text()', x))[1]::text::int										as Disability
			,(xpath('NOSOLOGY_ITEM/THERAPY_RESISTANCE1/text()', x))[1]::text::int								as TherapyResistance
			,to_timestamp((xpath('NOSOLOGY_ITEM/DEATH_DATE1/text()', x))[1]::text, 'DD.MM.YYYY')		as DeathDate
			,(xpath('NOSOLOGY_ITEM/OKATO1/text()', x))[1]::varchar(20)									as OKATO
			,(xpath('NOSOLOGY_ITEM/SMO1/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/header1/ID1/text()', x))[1]::text::UUID							as ID
			,(xpath('NOSOLOGY/body1/ADRESS1/text()', x))[1]::varchar(256)									as Adress
			,to_timestamp((xpath('NOSOLOGY/body1/AUTO_DATE_CHANGE1/text()', x))[1]::text, 'DD.MM.YYYY')	as AutoDateOfChange
			,(xpath('NOSOLOGY/body1/CARD_STATUS1/text()', x))[1]::varchar(256)								as CardStatus
			,(xpath('NOSOLOGY/body1/COMMENT1/text()', x))[1]::varchar(1024)									as Comment
			,to_timestamp((xpath('NOSOLOGY/body1/CHANGE_DATE1/text()', x))[1]::text, 'DD.MM.YYYY')		as DateOfChange
			,to_timestamp((xpath('NOSOLOGY/body1/DECLUSION_DATE1/text()', x))[1]::text, 'DD.MM.YYYY')	as DateOfDeclusion
			,to_timestamp((xpath('NOSOLOGY/body1/INCLUSION_DATE1/text()', x))[1]::text, 'DD.MM.YYYY')	as DateOfInclusion
			,(xpath('NOSOLOGY/body1/EXECUTIVE_AUTHORITY1/text()', x))[1]::varchar(256)						as ExecutiveAuthority
			,(xpath('NOSOLOGY/body1/HEAD1/text()', x))[1]::varchar(256)										as Head
			,(xpath('NOSOLOGY/body1/OMS_NUMBER1/text()', x))[1]::varchar(30)								as OmsNumber
			,(xpath('NOSOLOGY/body1/OMS_SERIAL1/text()', x))[1]::varchar(30)								as OmsSerial
			,(xpath('NOSOLOGY/body1/OPERATION_CODE1/text()', x))[1]::varchar(128)							as OperationCode
			,(xpath('NOSOLOGY/body1/UNIQUE_NUMBER1/text()', x))[1]::varchar(128)							as UniqueNumber
			,(xpath('NOSOLOGY/body1/MKB1/text()', x))[1]::varchar(10)										as Mkb
			,(xpath('NOSOLOGY/body1/DIAGNOSIS1/text()', x))[1]::varchar(4096)								as Diagnosis
			,(xpath('NOSOLOGY/body1/DOC_SERIES1/text()', x))[1]::varchar(30)								as DocSeries
			,(xpath('NOSOLOGY/body1/DOC_NUMBER1/text()', x))[1]::varchar(30)								as DocNumber
			,to_timestamp((xpath('NOSOLOGY/body1/BIRTHDAY1/text()', x))[1]::text, 'DD.MM.YYYY')			as Birthday
			,(xpath('NOSOLOGY/body1/FIRST_NAME1/text()', x))[1]::varchar(60)								as FirstName
			,(xpath('NOSOLOGY/body1/LAST_NAME1/text()', x))[1]::varchar(60)									as LastName
			,(xpath('NOSOLOGY/body1/FATHER_NAME1/text()', x))[1]::varchar(60)								as FatherName
			,to_timestamp((xpath('NOSOLOGY/body1/DOC_ISSUE_DATE1/text()', x))[1]::text, 'DD.MM.YYYY')	as DocIssueDate
			,(xpath('NOSOLOGY/body1/DOC_ISSUE_ORG1/text()', x))[1]::varchar(256)							as DocIssueOrg
			,(xpath('NOSOLOGY/body1/DECLUSION_LPU1/text()', x))[1]::varchar(6)								as DeclusionLPU
			,(xpath('NOSOLOGY/body1/DOC_TYPE1/text()', x))[1]::text::int											as DocumentType
			,(xpath('NOSOLOGY/body1/EX1781/text()', x))[1]::text::int												as ExemptionBy178
			,(xpath('NOSOLOGY/body1/EX8901/text()', x))[1]::text::int												as ExemptionBy890
			,(xpath('NOSOLOGY/body1/INCLUSION_LPU1/text()', x))[1]::varchar(6)								as InclusionLPU
			,(xpath('NOSOLOGY/body1/REGION1/text()', x))[1]::varchar(13)									as Region
			,(xpath('NOSOLOGY/body1/SEX1/text()', x))[1]::text::int												as Sex
			,(xpath('NOSOLOGY/body1/RF_ACCOUNT1/text()', x))[1]::text::int										as SubjectOfRFAccounted
			,(xpath('NOSOLOGY/body1/SNILS1/text()', x))[1]::varchar(14)										as SNILS
			,(xpath('NOSOLOGY/body1/BENIFITS1/text()', x))[1]::varchar(500)									as Benefits
			,(xpath('NOSOLOGY/body1/BIRTHDAY_LAST_NAME1/text()', x))[1]::varchar(60)						as BirthdayLastName
			,(xpath('NOSOLOGY/body1/DIASBILITY1/text()', x))[1]::text::int										as Disability
			,(xpath('NOSOLOGY/body1/THERAPY_RESISTANCE1/text()', x))[1]::text::int								as TherapyResistance
			,to_timestamp((xpath('NOSOLOGY/body1/DEATH_DATE1/text()', x))[1]::text, 'DD.MM.YYYY')			as DeathDate
			,(xpath('NOSOLOGY/body1/OKATO1/text()', x))[1]::varchar(20)									as OKATO
			,(xpath('NOSOLOGY/body1/SMO1/text()', x))[1]::varchar(5)										as F002Smo
			from
				(
					select	unnest(xpath('/NOSOLOGY', "Xml"::xml)) as x
				) NOSOLOGY
			where _OperationType <> 'FTLoad';

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

			INSERT INTO APS.T_NOSOLOGY
				       (NosologyID
				       ,Adress
				       ,AutoDateOfChange
				       ,CardStatus
				       ,Comment
				       ,DateOfChange
				       ,DateOfDeclusion
				       ,DateOfInclusion
				       ,ExecutiveAuthority
				       ,Head
				       ,OmsNumber
				       ,OmsSerial
				       ,OperationCode
				       ,UniqueNumber
				       ,Mkb
				       ,Diagnosis
				       ,DocSeries
				       ,DocNumber
				       ,Birthday
				       ,FirstName
				       ,LastName
				       ,FatherName
				       ,DocIssueDate
				       ,DocIssueOrg
				       ,DeclusionLPU
				       ,DocumentType
				       ,ExemptionBy178
				       ,ExemptionBy890
				       ,InclusionLPU
				       ,Region
				       ,Sex
				       ,SubjectOfRFAccounted
				       ,SNILS
				       ,Benefits
					   ,BirthdayLastName
					   ,Disability
					   ,TherapyResistance
					   ,DeathDate
					   ,OKATO
					   ,F002Smo)
				SELECT
				        gen_random_uuid()
				       ,nos.Adress
				       ,nos.AutoDateOfChange
				       ,nos.CardStatus
				       ,nos.Comment
				       ,nos.DateOfChange
				       ,nos.DateOfDeclusion
				       ,nos.DateOfInclusion
				       ,nos.ExecutiveAuthority
				       ,nos.Head
				       ,nos.OmsNumber
				       ,nos.OmsSerial
				       ,nos.OperationCode
				       ,nos.UniqueNumber
				       ,nos.Mkb
				       ,nos.Diagnosis
				       ,nos.DocSeries
				       ,nos.DocNumber
				       ,nos.Birthday
				       ,nos.FirstName
				       ,nos.LastName
				       ,nos.FatherName
				       ,nos.DocIssueDate
				       ,nos.DocIssueOrg
				       ,nos.DeclusionLPU
				       ,nos.DocumentType
				       ,nos.ExemptionBy178
				       ,nos.ExemptionBy890
				       ,nos.InclusionLPU
				       ,(select r.RegionID from APS.T_REGION r where r.Code = nos.Region limit 1)
				       ,nos.Sex
				       ,nos.SubjectOfRFAccounted
				       ,nos.SNILS
				       ,(select b.BenefitsID from APS.T_BENEFITS b where b.BenefitCode = nos.Benefits and b.BenefitViews = 19006 limit 1)
					   ,nos.BirthdayLastName
					   ,coalesce(nos.Disability, 0)
					   ,nos.TherapyResistance
					   ,nos.DeathDate
					   ,nos.OKATO
					   ,nos.F002Smo
				from TMP_NOSOLOGYS nos;

			elsif(_OperationType = 'Delete')
			then

				delete from APS.T_NOSOLOGY n 
					using TMP_NOSOLOGY nos
				where nos.SNILS = n.SNILS;
			
			ELSE
			
				delete from APS.T_NOSOLOGY n 
					using TMP_NOSOLOGY nos
				where nos.SNILS = n.SNILS;

				INSERT INTO APS.T_NOSOLOGY
				       (NosologyID
				       ,Adress
				       ,AutoDateOfChange
				       ,CardStatus
				       ,Comment
				       ,DateOfChange
				       ,DateOfDeclusion
				       ,DateOfInclusion
				       ,ExecutiveAuthority
				       ,Head
				       ,OmsNumber
				       ,OmsSerial
				       ,OperationCode
				       ,UniqueNumber
				       ,Mkb
				       ,Diagnosis
				       ,DocSeries
				       ,DocNumber
				       ,Birthday
				       ,FirstName
				       ,LastName
				       ,FatherName
				       ,DocIssueDate
				       ,DocIssueOrg
				       ,DeclusionLPU
				       ,DocumentType
				       ,ExemptionBy178
				       ,ExemptionBy890
				       ,InclusionLPU
				       ,Region
				       ,Sex
				       ,SubjectOfRFAccounted
				       ,SNILS
				       ,Benefits
					   ,BirthdayLastName
					   ,Disability
					   ,TherapyResistance
					   ,DeathDate
					   ,OKATO
					   ,F002Smo)
				SELECT
				        gen_random_uuid()
				       ,nos.Adress
				       ,nos.AutoDateOfChange
				       ,nos.CardStatus
				       ,nos.Comment
				       ,nos.DateOfChange
				       ,nos.DateOfDeclusion
				       ,nos.DateOfInclusion
				       ,nos.ExecutiveAuthority
				       ,nos.Head
				       ,nos.OmsNumber
				       ,nos.OmsSerial
				       ,nos.OperationCode
				       ,nos.UniqueNumber
				       ,nos.Mkb
				       ,nos.Diagnosis
				       ,nos.DocSeries
				       ,nos.DocNumber
				       ,nos.Birthday
				       ,nos.FirstName
				       ,nos.LastName
				       ,nos.FatherName
				       ,nos.DocIssueDate
				       ,nos.DocIssueOrg
				       ,nos.DeclusionLPU
				       ,nos.DocumentType
				       ,nos.ExemptionBy178
				       ,nos.ExemptionBy890
				       ,nos.InclusionLPU
				       ,(select r.RegionID from APS.T_REGION r where r.Code = nos.Region limit 1)
				       ,nos.Sex
				       ,nos.SubjectOfRFAccounted
				       ,nos.SNILS
				       ,(select b.BenefitsID from APS.T_BENEFITS b where b.BenefitCode = nos.Benefits and b.BenefitViews = 19006 limit 1)
					   ,nos.BirthdayLastName
					   ,coalesce(nos.Disability, 0)
					   ,nos.TherapyResistance
					   ,nos.DeathDate
					   ,nos.OKATO
					   ,nos.F002Smo
				from TMP_NOSOLOGY nos
				where not exists
				    (
				        select *
				        from APS.T_NOSOLOGY n
				        where n.SNILS = nos.SNILS
				    );
			END if;

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