DROP PROCEDURE IF EXISTS sp_GetSetAdditionalFactorsRequestCatalog;

GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- ==================================================
-- Author:      Vitacore (с)
-- Create date: 2019-04-23
-- Description: Синхронизация дополнительных факторов заявочного каталога
-- ==================================================

CREATE PROCEDURE sp_GetSetAdditionalFactorsRequestCatalog
(
	"Xml" text DEFAULT NULL
,	"IsNew" boolean DEFAULT '1'
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" character varying DEFAULT null::character varying
,	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
,	"User" uuid DEFAULT NULL::uuid
)
language plpgsql
as
$$
begin
	DECLARE _Result text; 
			_ContentEncoding varchar(255);
			_Type varchar(255);
			_OperationType varchar(255);
			_OwnerID varchar(255);
			,_FTLOAD varchar(6);

		begin
			"dateTime" := coalesce("dateTime", Current_TimeStamp::TIMESTAMP); 
			_FLOAD = 'FTLoad';

			IF("Xml" is null)
			then
				-- Инициализация
				_ContentEncoding := 'utf-8';
				_Type := 'ADDITIONAL_FACTORS_REQUEST_CATALOG';
				_OwnerID := "ID";
				-- Получаем тип операции
				_OperationType := _FLOAD;

				select 
				xmlelement
				(
					name "ADDITIONAL_FACTORS_REQUEST_CATALOG",
					xmlelement
					(
						name header,
						xmlforest(_OperationType as "TYPE"),
					),
					xmlelement
					(
						name body,
						xmlelement
						(
							name "ADDITIONAL_FACTORS_REQUEST_CATALOGS",
							xmlelement
							(
								name "ADDITIONAL_FACTORS_REQUEST_CATALOG",
								xmlforest
								(
									afda.AdditionalFactorsDrugApplicationItemID		AS "ID"
									,afda.DrugApplicationListItem					AS "DrugApplicationListItem"
									,afda.AdditionalFinanc							AS "AdditionalFinanc"
									,afda.Palliative								AS "Palliative"
									,b.BenefitViews									AS "BenefitViews"
									,b.BenefitCode									AS "BenefitCode"
								)
							)
									
						)
					)
				) 
				from RDS.T_ADDITIONAL_FACTORS_DRUG_APPLICATION_ITEM afda
					JOIN RDS.T_DRUG_APPLICATION_LIST_ITEM AS dali on dali.DrugApplicationListItemID = afda.DrugApplicationListItem
					JOIN RDS.T_DRUG_APPLICATION_LIST AS dal ON dal.DrugApplicationListID = dali.DrugApplicationList
					left join rds.T_BENEFITS b on b.BenefitsID = afda.Benefits
					where date_part(year, dal.BeginDate) >= date_part(year, "dateTime")
				into _Result;


				-- Отправка
				INSERT INTO RDS.T_RABBIT_SEND_OBJECT
				(
					RabbitSendObjectID,
					QueueName,
					Content,
					MessageID,
					Headers,
					ContentEncoding,
					Type,
					UserID,
					State,
					CreateDateTime,
					OwnerID
				)
				SELECT uuid_generate_v4(), --(<RabbitSendObjectID, uniqueidentifier,>
					   s.ServerName, --,<QueueName, varchar(255),>
					   ConvertTextToBinaryZip(_Result, ''), --,<Content, text,>
					   NULL, --,<MessageID, varchar(255),>
					   NULL, --,<Headers, varchar(255),>
					   _ContentEncoding, --,<ContentEncoding, varchar(255),>
					   _Type, --,<Type, varchar(255),>
					   "User", --,<UserID, varchar(255),>
					   0, --,<State, int,>)
					   "dateTime", --,<CreateDateTime, datetime,>)
					   _OwnerID				--,<OwnerID, varchar(255),>
				FROM   RDS.T_SYNC_SERVERS AS s
				WHERE  
					("QueueName" IS NOT NULL AND s.ServerName = "QueueName") 
					OR ("QueueName" IS NULL 
					AND s.ServerType IN(0, 1)); -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь
			end if;
	END;
GO