-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2016-03-14
-- Description: Выгрузка/загрузка заявок по ДЛО в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_RDS_DrugRequest;

CREATE PROCEDURE sp_RDS_DrugRequest
(
	"Xml" text DEFAULT NULL
,	"IsNew" boolean DEFAULT '1'
,	"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
,	"PATH"  character varying DEFAULT null::character varying
)
    language plpgsql
as
$$
Begin

	Declare	_Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'DRUG_REQUEST';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
		-- Operation types
		_INSERT varchar(6) := 'Insert';
		_UPDATE varchar(6) := 'Update';
		_DELETE varchar(6) := 'Delete';
		_FTLOAD varchar(6) := 'FTLoad';
		_FTSEND varchar(6) := 'FTSend';
	Begin
		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

		if "Xml" is null
		then
			--выясняем тип операции
			IF "ID" is null
			then
			    _OperationType := _FTSEND;
			elsif "IsNew" = '1'
			then
			    _OperationType := _INSERT;
			elsif "IsNew" = '0' and exists(select* from AKUZ.T_DRUG_REQUEST dr where dr.DrugRequestID = "ID")
			then
			    _OperationType := _UPDATE;
			elsif "IsNew" = '0' and not exists(select* from AKUZ.T_DRUG_REQUEST dr where dr.DrugRequestID = "ID")
			then
			    _OperationType := _DELETE;
			    _Result := XMLELEMENT(name "DRUG_REQUEST", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "DrugRequestID")));
			end if;

			-- Обработка операции
			if _OperationType = _FTSEND
			then
				-- собираем все DRUG_REQUEST
				_Result := 
				(
					XMLELEMENT
					(
						name "DRUG_REQUEST"
					,	XMLELEMENT
						(
							name header
						,	XMLFOREST(_FTSEND as "TYPE")
						)
					,	XMLELEMENT
						(
							name body
						,	xmlelement
							(
								name "DRUG_REQUEST_LIST"
							,	(
									select
									xmlagg
									(
										xmlelement
										(
											name "DRUG_REQUEST"
										,	xmlforest
											(
												dr.DrugRequestID						as "DrugRequestID"
											,	to_char(bdr.RequestDate, 'YYYY-MM-DD')	as "RequestDate"
											,	bdr.Number								as "Number"
											,	bdr.Active								as "Active"
											,	bdr.Additional							as "Additional"
											,	to_char(bdr.BeginPeriod, 'YYYY-MM-DD')	as "BeginPeriod"
											,	to_char(bdr.EndPeriod, 'YYYY-MM-DD')	as "EndPeriod"
											,	bdr.Name								as "Name"
											,	dr.ImportFileName						as "ImportFileName"
											,	bdr.Comment								as "Comment"
											,	lp.LpuCode								as "LpuMain"
											,	br.BranchCode							as "BranchCode"
											,	drM.DrugRequestID						as "DrugRequestID"
											,	dr.RequestType							as "RequestType"
											,	bdr.Status								as "Status"
											,	bdr.Limit								as "Limit"
											,	er.CodeEHR								as "EhrRusEj36pmtjqk"
											,	rb.CurrVersion							as "EhrRusEj36pmtjqkVer"
											,	erVzn.IdEHR								as "EhrRusVzn17"
											,	rbVzn.CurrVersion						as "EhrRusVzn17Ver"
											)
										,	(
												select
												xmlagg
												(
													xmlelement
													(
														name "DRUG_DEMAND"
													,	xmlforest
														(
															dd.DrugDemandID		as "DrugDemandID"
														,	dd.DemandOnPeriod	as "DemandOnPeriod"
														,	dd.RequestQuantity  as "RequestQuantity"
														,	dd.RequestCode		as "RequestCode"
														,	dd.LSFO				as "LSFO"
														,	dd.Comment			as "Comment"
														,	dd.DrugRequest		as "DrugRequest"
														,	dd.SectionCode		as "SectionCode"
														,	dd.RequestCatalog	as "RequestCatalog"
														)
													,	(
															select
															xmlagg
															(
																xmlelement
																(
																	name "PHARMACY_DRUG_DEMAND"
																,	xmlforest
																	(
																		pdd.PharmacyDrugDemandID 	as "PharmacyDrugDemandID"
																	,	pdd.DemandOnPeriod			as "DemandOnPeriod"
																	,	pdd.RequestQuantity			as "RequestQuantity"
																	,	pdd.Mont1					as "Mont1"
																	,	pdd.Mont2					as "Mont2"
																	,	pdd.Mont3					as "Mont3"
																	,	pdd.Mont4					as "Mont4"
																	,	pdd.Mont5					as "Mont5"
																	,	pdd.Mont6					as "Mont6"
																	,	pdd.Mont7					as "Mont7"
																	,	pdd.Mont8					as "Mont8"
																	,	pdd.Mont9					as "Mont9"
																	,	pdd.Mont10					as "Mont10"
																	,	pdd.Mont11					as "Mont11"
																	,	pdd.Mont12					as "Mont12"
																	,	pdd.DrugDemand				as "DrugDemand"
																	,	pdd.Pharmacy				as "Pharmacy"
																	)
																,	(
																		select
																		xmlagg
																		(
																			xmlelement
																			(
																				name "PATIENT_DRUG_DEMAND"
																			,	xmlforest
																				(
																					pat.PatientDrugDemandID				as "PatientDrugDemandID"
																				,	pat.PharmacyDrugDemand				as "PharmacyDrugDemand"
																				,	pat.DemandOnPeriod					as "DemandOnPeriod"
																				,	pat.RequestQuantity					as "RequestQuantity"
																				,	pat.Mont1							as "Mont1"
																				,	pat.Mont2							as "Mont2"
																				,	pat.Mont3							as "Mont3"
																				,	pat.Mont4							as "Mont4"
																				,	pat.Mont5							as "Mont5"
																				,	pat.Mont6							as "Mont6"
																				,	pat.Mont7							as "Mont7"
																				,	pat.Mont8							as "Mont8"
																				,	pat.Mont9							as "Mont9"
																				,	pat.Mont10							as "Mont10"
																				,	pat.Mont11							as "Mont11"
																				,	pat.Mont12							as "Mont12"
																				,	p.Birthday							as "Birthday"
																				,	p.FirstName							as "FirstName"
																				,	p.FatherName						as "FatherName"
																				,	p.LastName							as "LastName"
																				,	eSex.Code							as "Sex"
																				,	b.BenefitCode						as "BenefitCode"
																				,	pat.Signa							as "Signa"
																				,	worker.FIO							as "Post"
																  				,	b.BenefitViews						as "BenefitsType"
																				,	pat.MKB								as "MKB"
																				,	pat.Comment							as "Comment"
																				,	p.SNILS								as "Snils"
																				,case when p.DocSeries = '0' then null
																  					else p.DocSeries end	
																														as "DocSeries"
																				,case when p.DocNumber = '0' then null
																						else p.DocNumber end						
																														as "DocNumber"
																				 ,to_char(p.DocIssueDate, 'YYYY-MM-DD') as "DocIssueDate"
																				,p.DocIssueOrg							as "DocIssueOrg"
																				,eDt.Code								as "DocType"
																				,polis.Number							as "PolicyNumber"
																				,polis.Serial							as "PolicySerial"
																				)
																			)
																		)
																		from AKUZ.T_PATIENT_DRUG_DEMAND pat
																		join AKUZ.T_PATIENT p on p.PatientID = pat.Patient
																		join VCLib.T_ENUMS eDt on eDt.EnumID = p.DocType
																		join VCLib.T_ENUMS eSex on eSex.EnumID = p.Sex
																		left join AKUZ.T_BENEFITS b on b.BenefitsID = pat.Benefits
																		left join AKUZ.T_POST post on post.ResourceID = pat.Post
																		left join AKUZ.T_WORKER worker on post.Worker = worker.WorkerID
																		left join AKUZ.T_POLICY polis on polis.PolicyID = dbo.fn_GetOmsPolicy(pat.Patient, "dateTime")
																		where pat.PharmacyDrugDemand = pdd.PharmacyDrugDemandID
																)
															)
														)
															from AKUZ.T_PHARMACY_DRUG_DEMAND pdd
															where dd.DrugDemandID = pdd.DrugDemand
													)
											)
												)
												from AKUZ.T_DRUG_DEMAND dd
												where dd.DrugRequest = dr.DrugRequestID
										)
								)							
									)
									from AKUZ.T_DRUG_REQUEST dr
									join AKUZ.T_BASE_DRUG_REQUEST bdr on bdr.DrugRequestID = dr.DrugRequestID
									join AKUZ.T_LPU_MAIN lp on lp.LpuID = bdr.LpuMain
									left join AKUZ.T_BRANCH br on br.LpuID = dr.Branch
									left join AKUZ.T_DRUG_REQUEST drM on drM.DrugRequestID = bdr.DrugRequestMain
									left join EHR.T_EHR_RUS_EJ36PMTJQK er on er.EhrRusID = bdr.EhrRusEj36pmtjqk
									left join EHR.T_EHR_RUS_REF_BOOKS rb on rb.EhrRusRefBooksID = er.EhrRusRefBooks
									left join EHR.T_EHR_RUS_VZN17 erVzn on erVzn.EhrRusID = bdr.EhrRusVzn17
									left join EHR.T_EHR_RUS_REF_BOOKS rbVzn on rbVzn.EhrRusRefBooksID = erVzn.EhrRusRefBooks
									where bdr.Status = 3
							)						
						)
					)			
				));
			elsif _OperationType <> _DELETE
			then
				-- собираем DRUG_REQUEST
				select 
				XMLELEMENT
				(
					name "DRUG_REQUEST"
				,	XMLELEMENT
					(
						name header
					,	XMLFOREST
						(
							_OperationType as "TYPE"
						)
					)
				,	XMLFOREST
					(
						"ID"                  	as "DrugRequestID"
					,	bdr.RequestDate			as "RequestDate"
					,	bdr.Number				as "Number"
					,	bdr.Active				as "Active"
					,	bdr.Additional			as "Additional"
					,	bdr.BeginPeriod			as "BeginPeriod"
					,	bdr.EndPeriod			as "EndPeriod"
					,	bdr.Name				as "Name"
					,	dr.ImportFileName		as "ImportFileName"
					,	bdr.Comment				as "Comment"
					,	lp.LpuCode				as "LpuMain"
					,	drM.DrugRequestID		as "DrugRequestID"
					,	dr.RequestType			as "RequestType"
					,	br.BranchCode			as "BranchCode"
					,	bdr.Status				as "Status"
					,	bdr.Limit				as "Limit"
					,	er.CodeEHR				as "EhrRusEj36pmtjqk"
					,	rb.CurrVersion			as "EhrRusEj36pmtjqkVer"
					,	erVzn.IdEHR				as "EhrRusVzn17"
					,	rbVzn.CurrVersion		as "EhrRusVzn17Ver"
					)
				,	--собираем DRUG_DEMAND
					(
					select
					xmlagg
					(
						xmlelement
						(
							name "DRUG_DEMAND"
						,	xmlforest
							(
								dd.DrugDemandID		as "DrugDemandID"
							,	dd.DemandOnPeriod	as "DemandOnPeriod"
							,	dd.RequestQuantity	as "RequestQuantity"
							,	dd.RequestCode		as "RequestCode"
							,	dd.LSFO				as "LSFO"
							,	dd.Comment			as "Comment"
							,	dd.DrugRequest		as "DrugRequest"
							,	dd.SectionCode		as "SectionCode"
							,	dd.RequestCatalog	as "RequestCatalog"
							)
						,	--собираем PHARMACY_DRUG_DEMAND
							(
							select
							xmlagg
							(
								xmlelement
								(
									name "PHARMACY_DRUG_DEMAND"
								,	xmlforest
									(
										pdd.PharmacyDrugDemandID	as "PharmacyDrugDemandID"
									,	pdd.DemandOnPeriod			as "DemandOnPeriod"
									,	pdd.RequestQuantity			as "RequestQuantity"
									,	pdd.Mont1					as "Mont1"
									,	pdd.Mont2					as "Mont2"
									,	pdd.Mont3					as "Mont3"
									,	pdd.Mont4					as "Mont4"
									,	pdd.Mont5					as "Mont5"
									,	pdd.Mont6					as "Mont6"
									,	pdd.Mont7					as "Mont7"
									,	pdd.Mont8					as "Mont8"
									,	pdd.Mont9					as "Mont9"
									,	pdd.Mont10					as "Mont10"
									,	pdd.Mont11					as "Mont11"
									,	pdd.Mont12					as "Mont12"
									,	pdd.DrugDemand				as "DrugDemand"
									,	pdd.Pharmacy				as "Pharmacy"
									)
								,	--собираем PATIENT_DRUG_DEMAND
									(
										select
										xmlagg
										(
											xmlelement
											(
												name "PATIENT_DRUG_DEMAND"
											,	xmlforest
												(
													pat.PatientDrugDemandID				as "PatientDrugDemandID"
												,	pat.PharmacyDrugDemand				as "PharmacyDrugDemand"
												,	pat.DemandOnPeriod					as "DemandOnPeriod"
												,	pat.RequestQuantity					as "RequestQuantity"
												,	pat.Mont1							as "Mont1"
												,	pat.Mont2							as "Mont2"
												,	pat.Mont3							as "Mont3"
												,	pat.Mont4							as "Mont4"
												,	pat.Mont5							as "Mont5"
												,	pat.Mont6							as "Mont6"
												,	pat.Mont7							as "Mont7"
												,	pat.Mont8							as "Mont8"
												,	pat.Mont9							as "Mont9"
												,	pat.Mont10							as "Mont10"
												,	pat.Mont11							as "Mont11"
												,	pat.Mont12							as "Mont12"
												,	p.Birthday							as "Birthday"
												,	p.FirstName							as "FirstName"
												,	p.FatherName						as "FatherName"
												,	p.LastName							as "LastName"
												,	eSex.Code							as "Sex"
												,	b.BenefitCode						as "BenefitCode"
												,	pat.Signa							as "Signa"
												,	worker.FIO							as "Post"
												,	b.BenefitViews						as "BenefitsType"
												,	pat.MKB								as "MKB"
												,	pat.Comment							as "Comment"
												,	p.SNILS								as "Snils"
												,case 
													when p.DocSeries = '0' then null
													else p.DocSeries end				as "DocSeries"
												,case 
													when p.DocNumber = '0' then null
													else p.DocNumber end				as "DocNumber"
												,to_char(p.DocIssueDate, 'YYYY-MM-DD')  as "DocIssueDate"
												,p.DocIssueOrg							as "DocIssueOrg"
												,eDt.Code								as "DocType"
												,polis.Number							as "PolicyNumber"
												,polis.Serial							as "PolicySerial"
												)
											)
										)
										from AKUZ.T_PATIENT_DRUG_DEMAND pat
										join AKUZ.T_PATIENT p on p.PatientID = pat.Patient
										join VClib.T_ENUMS eDt on eDt.EnumID = p.DocType
										join VCLib.T_ENUMS eSex on eSex.EnumID = p.Sex
										left join AKUZ.T_BENEFITS b on b.BenefitsID = pat.Benefits
										left join AKUZ.T_POST post on post.ResourceID = pat.Post
										left join AKUZ.T_WORKER worker on post.Worker = worker.WorkerID
										left join AKUZ.T_POLICY polis on polis.PolicyID = dbo.fn_GetOmsPolicy(pat.Patient, "dateTime")
										where pat.PharmacyDrugDemand = pdd.PharmacyDrugDemandID
									)
								)
							)
							from AKUZ.T_PHARMACY_DRUG_DEMAND pdd
							where dd.DrugDemandID = pdd.DrugDemand
							)
						)
					)
					from AKUZ.T_DRUG_DEMAND dd
					where dd.DrugRequest = dr.DrugRequestID
					)
				)			
				from AKUZ.T_DRUG_REQUEST dr
				join AKUZ.T_BASE_DRUG_REQUEST bdr on bdr.DrugRequestID = dr.DrugRequestID
				join AKUZ.T_LPU_MAIN lp on lp.LpuID = bdr.LpuMain
				left join AKUZ.T_BRANCH br on br.LpuID = dr.Branch
				left join AKUZ.T_DRUG_REQUEST drM on drM.DrugRequestID = bdr.DrugRequestMain
				left join EHR.T_EHR_RUS_EJ36PMTJQK er on er.EhrRusID = bdr.EhrRusEj36pmtjqk
				left join EHR.T_EHR_RUS_REF_BOOKS rb on rb.EhrRusRefBooksID = er.EhrRusRefBooks
				left join EHR.T_EHR_RUS_VZN17 erVzn on erVzn.EhrRusID = bdr.EhrRusVzn17
				left join EHR.T_EHR_RUS_REF_BOOKS rbVzn ON rbVzn.EhrRusRefBooksID = erVzn.EhrRusRefBooks
				into _Result
				where dr.DrugRequestID = "ID";
			end if;
			
			-- Отправка
			INSERT INTO akuz.T_RABBIT_SEND_OBJECT
				(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
			select	gen_random_uuid(), 
					"QueueName",
					ConvertTextToBinaryZip(_Result, ''), 
					null, 
					null, 
					_ContentEncoding, 
					_Type, 
					"User", 
					0, 
					"dateTime", 
					_OwnerID;
		else
			if "Xml" is not null
			then      
				-- Получаем тип операции
				_OperationType := (xpath('/DRUG_REQUEST/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);

				-- Generate table #DRUG_REQUEST
				Drop Table If Exists Tmp_DRUG_REQUEST;
				Create Temp Table Tmp_DRUG_REQUEST
				as
				SELECT	(xpath('/DRUG_REQUEST[1]/DrugRequestID[1]/text()', x))[1]::text::UUID as DrugRequestID
				,	to_timestamp((xpath('/DRUG_REQUEST[1]/RequestDate[1]/text()', x))[1]::text, 'YYYY-MM-DD') as RequestDate
				,	(xpath('/DRUG_REQUEST[1]/Active[1]/text()', x))[1]::text::boolean as Active
				,	(xpath('/DRUG_REQUEST[1]/Additional[1]/text()', x))[1]::text::boolean as Additional
				,	(xpath('/DRUG_REQUEST[1]/Number[1]/text()', x))[1]::text::int as Number
				,	to_timestamp((xpath('/DRUG_REQUEST[1]/BeginPeriod[1]/text()', x))[1]::text, 'YYYY-MM-DD') as BeginPeriod
				,	to_timestamp((xpath('/DRUG_REQUEST[1]/EndPeriod[1]/text()', x))[1]::text, 'YYYY-MM-DD') as EndPeriod
				,	(xpath('/DRUG_REQUEST[1]/Name[1]/text()', x))[1]::varchar(255) as Name
				,	(xpath('/DRUG_REQUEST[1]/ImportFileName[1]/text()', x))[1]::varchar(255) as ImportFileName
				,	(xpath('/DRUG_REQUEST[1]/Comment[1]/text()', x))[1]::varchar(8000) as Comment
				,	(xpath('/DRUG_REQUEST[1]/LpuMain[1]/text()', x))[1]::varchar(6) as LpuMain
				,	(xpath('/DRUG_REQUEST[1]/DrugRequestMain[1]/text()', x))[1]::text::UUID as DrugRequestMain
				,	(xpath('/DRUG_REQUEST[1]/RequestType[1]/text()', x))[1]::text::int as RequestType
				,	(xpath('/DRUG_REQUEST[1]/Status[1]/text()', x))[1]::text::int as Status
				,	(xpath('/DRUG_REQUEST[1]/Limit[1]/text()', x))[1]::text::decimal(31,10) as Limit
				,	(xpath('/DRUG_REQUEST[1]/BranchCode[1]/text()', x))[1]::varchar(16) as BranchCode
				from unnest(xpath('/DRUG_REQUEST', "Xml"::xml)) x
				where _OperationType <> _FTLOAD;

				-- Generate table #DRUG_DEMAND
				Drop Table If Exists Tmp_DRUG_DEMAND;
				Create Temp Table Tmp_DRUG_DEMAND
				as
				SELECT	(xpath('/DRUG_DEMAND[1]/DrugDemandID[1]/text()', x))[1]::text::UUID as DrugDemandID
				,	(xpath('/DRUG_DEMAND[1]/DemandOnPeriod[1]/text()', x))[1]::text::decimal(12,2) as DemandOnPeriod
				,	(xpath('/DRUG_DEMAND[1]/RequestQuantity[1]/text()', x))[1]::text::decimal(12,2) as RequestQuantity
				,	(xpath('/DRUG_DEMAND[1]/RequestCode[1]/text()', x))[1]::text::int as RequestCode
				,	(xpath('/DRUG_DEMAND[1]/LSFO[1]/text()', x))[1]::text::int as LSFO
				,	(xpath('/DRUG_DEMAND[1]/Comment[1]/text()', x))[1]::varchar(8000) as Comment
				,	(xpath('/DRUG_DEMAND[1]/DrugRequest[1]/text()', x))[1]::text::UUID as DrugRequest
				,	(xpath('/DRUG_DEMAND[1]/SectionCode[1]/text()', x))[1]::varchar(255) as SectionCode			
				,	(xpath('/DRUG_DEMAND[1]/RequestCatalog[1]/text()', x))[1]::text::UUID as RequestCatalog
				from unnest(xpath('/DRUG_REQUEST/DRUG_DEMAND', "Xml"::xml)) x
				where _OperationType <> _FTLOAD;

				-- Generate table #PATIENT_DRUG_DEMAND
				Drop Table If Exists Tmp_PATIENT_DRUG_DEMAND;
				Create Temp Table Tmp_PATIENT_DRUG_DEMAND
				as
				SELECT	(xpath('/PATIENT_DRUG_DEMAND[1]/PatientDrugDemandID[1]/text()', x))[1]::text::UUID as PatientDrugDemandID
				,	(xpath('/PATIENT_DRUG_DEMAND[1]/PharmacyDrugDemand[1]/text()', x))[1]::text::UUID as PharmacyDrugDemand
				,	(xpath('/PATIENT_DRUG_DEMAND[1]/DemandOnPeriod[1]/text()', x))[1]::text::decimal(12,2) as DemandOnPeriod
				,	to_timestamp((xpath('/PATIENT_DRUG_DEMAND[1]/Birthday[1]/text()', x))[1]::text, 'YYYY-MM-DD') as Birthday
				,	(xpath('/PATIENT_DRUG_DEMAND[1]/FirstName[1]/text()', x))[1]::varchar(40) as FirstName
				,	(xpath('/PATIENT_DRUG_DEMAND[1]/FatherName[1]/text()', x))[1]::varchar(40) as FatherName
				,	(xpath('/PATIENT_DRUG_DEMAND[1]/LastName[1]/text()', x))[1]::varchar(40) as LastName
				,	(xpath('/PATIENT_DRUG_DEMAND[1]/BenefitCode[1]/text()', x))[1]::text::int as BenefitCode							
				,	(xpath('/PATIENT_DRUG_DEMAND[1]/BenefitType[1]/text()', x))[1]::text::int as BenefitType
				,	(xpath('/PATIENT_DRUG_DEMAND[1]/MKB[1]/text()', x))[1]::varchar(10) as MKB
				,	(xpath('/PATIENT_DRUG_DEMAND[1]/Comment[1]/text()', x))[1]::varchar(8000) as Comment
				,	(xpath('/PATIENT_DRUG_DEMAND[1]/Snils[1]/text()', x))[1]::varchar(14) as Snils
				from unnest(xpath('/DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND/PATIENT_DRUG_DEMAND', "Xml"::xml)) x
				where _OperationType <> _FTLOAD;

				-- Generate table #DRUG_REQUEST_COMMENTS
				Drop Table If Exists Tmp_DRUG_REQUEST_COMMENTS;
				Create Temp Table Tmp_DRUG_REQUEST_COMMENTS
				as
				SELECT	(xpath('/DRUG_REQUEST_COMMENTS[1]/DrugRequestCommentsID[1]/text()', x))[1]::text::UUID as DrugRequestCommentsID
				,	(xpath('/DRUG_REQUEST_COMMENTS[1]/Comment[1]/text()', x))[1]::varchar(8000) as Comment
				,	(xpath('/DRUG_REQUEST_COMMENTS[1]/RequestCode[1]/text()', x))[1]::text::int as RequestCode
				,	(xpath('/DRUG_REQUEST_COMMENTS[1]/Processed[1]/text()', x))[1]::text::int as Processed
				,	(xpath('/DRUG_REQUEST_COMMENTS[1]/UserProcessed[1]/text()', x))[1]::text::int as UserProcessed			
				,	(xpath('/DRUG_REQUEST_COMMENTS[1]/Worker[1]/text()', x))[1]::varchar(500) as Worker
				,	(xpath('/DRUG_REQUEST_COMMENTS[1]/DrugRequest[1]/text()', x))[1]::text::uuid as DrugRequest
				from unnest(xpath('/DRUG_REQUEST/DRUG_REQUEST_COMMENTS', "Xml"::xml)) x
				where _OperationType <> _FTLOAD;

				-- Обработка операции
				if _OperationType = _DELETE
				then
					Raise Exception Using Message = 'Операция не поддерживается';
					Return;
				else
					update AKUZ.T_BASE_DRUG_REQUEST dr
					set	Comment = dre.Comment
					,	Status = dre.Status
					,	"limit" = dre.Limit
					from Tmp_DRUG_REQUEST dre 
					where dre.DrugRequestID = dr.DrugRequestID;

					update AKUZ.T_DRUG_DEMAND dd
					set	Comment = ddl.Comment
					from Tmp_DRUG_DEMAND ddl 
					where ddl.DrugDemandID = dd.DrugDemandID;
					
					update AKUZ.T_PATIENT_DRUG_DEMAND pdd
					set	Comment = pddl.Comment
					from Tmp_PATIENT_DRUG_DEMAND pddl 
					where pddl.PatientDrugDemandID = pdd.PatientDrugDemandID;

					update AKUZ.T_DRUG_REQUEST_COMMENTS drc
					set	Comment = rc.Comment
					,	Processed = rc.Processed
					from Tmp_DRUG_REQUEST_COMMENTS rc 
					where rc.DrugRequestCommentsID = rc.DrugRequestCommentsID;

					delete from AKUZ.T_DRUG_REQUEST_COMMENTS drc
					where not exists(select* from Tmp_DRUG_REQUEST_COMMENTS rc where drc.DrugRequestCommentsID = rc.DrugRequestCommentsID);

					INSERT INTO AKUZ.T_DRUG_REQUEST_COMMENTS(DrugRequestCommentsID, Comment, RequestCode, Processed, UserProcessed, Worker, DrugRequest)
					select DrugRequestCommentsID, Comment, RequestCode, Processed, UserProcessed, Worker, DrugRequest 
					from Tmp_DRUG_REQUEST_COMMENTS drc
					where not exists(select* from AKUZ.T_DRUG_REQUEST_COMMENTS rc where drc.DrugRequestCommentsID = rc.DrugRequestCommentsID);
				END if;
				DROP TABLE if exists Tmp_DRUG_REQUEST;
				DROP TABLE if exists Tmp_DRUG_DEMAND;
				DROP TABLE if exists Tmp_PATIENT_DRUG_DEMAND;
				DROP TABLE if exists Tmp_DRUG_REQUEST_COMMENTS;
			END if;
		end if;
	End;
End;
$$;