-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-21
-- Description: Загрузка льготников по орфанным заболеваниям
-- =============================================
DROP PROCEDURE IF EXISTS DBO.sp_getsetrdsorphregister;

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

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

			if (_OperationType = 'FTLoad')
            then

				delete from APS.T_ORPHAN_REGISTER;

				insert into  APS.T_ORPHAN_REGISTER
				(
					 OrphanRegisterID
				    ,Snils
				    ,Birthday
				    ,FirstName
				    ,LastName
				    ,FatherName
				    ,DocIssueDate
				    ,DocIssueOrg
				    ,Mkb
				    ,Diagnosis
				    ,DocSeries
				    ,DocNumber
				    ,DateBegin
				    ,DateEnd
				    ,Adress
				    ,Sex
				    ,DocType
				    ,MkbDop
				    ,DiagnosisDop
				    ,KodAdr
				    ,BirthLastName
				    ,Disability
				    ,OMSSerial
				    ,OMSNumber
				    ,F002Smo
				    ,LpuMainFirst
				    ,LpuMain
				    ,Federal
				    ,PostFio
				    ,RegisterId
					)
				select  
					ID
				   ,Snils
				   ,Birthday
				   ,FirstName
				   ,LastName
				   ,FatherName
				   ,DocIssueDate
				   ,DocIssueOrg
				   ,Mkb
				   ,Diagnosis
				   ,DocSeries
				   ,DocNumber
				   ,DateBegin
				   ,DateEnd
				   ,Adress
				   ,Sex
				   ,DocType
				   ,MkbDop
				   ,DiagnosisDop
				   ,OKATO
				   ,BirthLastName
				   ,Disability
				   ,OMSSerial
				   ,OMSNumber
				   ,F002Smo
				   ,LpuMainFirst
				   ,LpuMain
				   ,Federal
				   ,PostFio
				   ,RegisterId
				from TMP_ORPHAN_REGISTERS;
			elsif (_OperationType = 'Delete')
			then
				delete from APS.T_ORPHAN_REGISTER orph 
				using TMP_ORPHAN_REGISTER tmporph 
				where tmporph.ID = orph.OrphanRegisterID;
			else
				delete from APS.T_ORPHAN_REGISTER orph 
				using TMP_ORPHAN_REGISTER tmporph 
				where tmporph.ID = orph.OrphanRegisterID;

				INSERT INTO APS.T_ORPHAN_REGISTER
			     (
					OrphanRegisterID
					,Snils
					,Birthday
					,FirstName
					,LastName
					,FatherName
					,DocIssueDate
					,DocIssueOrg
					,Mkb
					,Diagnosis
					,DocSeries
					,DocNumber
					,DateBegin
					,DateEnd
					,Adress
					,Sex
					,DocType
					,MkbDop
					,DiagnosisDop
					,KodAdr
					,BirthLastName
					,Disability
					,OMSSerial
					,OMSNumber
					,F002Smo
					,LpuMainFirst
					,LpuMain
					,Federal
					,PostFio
					,RegisterId
				 )
				select  
					ID
					,Snils
					,Birthday
					,FirstName
					,LastName
					,FatherName
					,DocIssueDate
					,DocIssueOrg
					,Mkb
					,Diagnosis
					,DocSeries
					,DocNumber
					,DateBegin
					,DateEnd
					,Adress
					,Sex
					,DocType
					,MkbDop
					,DiagnosisDop
					,OKATO
					,BirthLastName
					,Disability
					,OMSSerial
					,OMSNumber
					,F002Smo
					,LpuMainFirst
					,LpuMain
					,Federal
					,PostFio
					,RegisterId
				from TMP_ORPHAN_REGISTER;
		end if;

		DROP TABLE TMP_ORPHAN_REGISTER;
		DROP TABLE TMP_ORPHAN_REGISTERS;

        end if;
    End;
End;
$$;