drop procedure if exists sp_GetSetDrugApplicationList;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-09-29
-- Description: Выгрузка заявочного списка из RDS
-- =============================================
CREATE PROCEDURE sp_GetSetDrugApplicationList
(
	"Xml" xml DEFAULT NULL::xml
	,"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
)
as
$$
Begin
	declare
		_Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'DRUG_APPLICATION_LIST';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
	begin
		"dateTime" := coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

		if ("Xml" is null)
		then
			--выясняем тип операции
			_OperationType := case when "ID" is null then 'FTLoad' else 'Update' end;

			select 
			xmlelement
			(
				name "DRUG_APPLICATION_LIST",
				xmlelement
				(
					name header,
					xmlforest (_OperationType as "TYPE"
				),
				xmlelement
				(
					name body,
					xmlelement
					(
						name "DRUG_APPLICATION_LIST_ITEMS",
						xmlelement
						(
							name "DRUG_APPLICATION_LIST_ITEM",
							xmlforest 
							(
								dali.DrugApplicationListItemID									AS "ID"
								,to_char(dal.BeginDate, 'YYYY-MM-DD')							AS "BeginDate"
								,to_char(ISNULL(dali.EndDate, dal.EndDate), 'YYYY-MM-DD')		AS "EndDate"
								,dali.Price														AS "Price"
								,dali.RequestCode												AS "RequestCode"
								,mnn.OtherID													AS "CMnnID"
								,trn.OtherID													AS "CTrnID"
								,dali.GroupsOfDrugsClassification								AS "GroupsOfDrugsClassification"
								,eBt.Code														AS "BenefitType"
								,dv.Value														AS "DoseView"
								,mf.Code														AS "MedicationForms"
								,dali.PackQuantity												as "PackQuantity"
								,dal.IsActive													as "IsActive"
								,dali.ElectronicCertificate										as "ElectronicCertificate"
								,dali.SectionCode												as "SectionCode"
								,dali.Comment													as "Comment"
								,dali.PersRequest												as "PersRequest"
								,dali.DoseCount													as "DoseCount"
								,dali.ConsumerUnits												as "ConsumerUnits"
								,er.CodeEHR														as "EhrRus9a9g09e8kl"
								,rrb.CurrVersion												as "EhrRus9a9g09e8klVer"
								,er1.IdEHR														as "EhrRus6kiqts8t99"
								,rrb1.CurrVersion												as "EhrRus6kiqts8t99Ver"
								,er2.IdEHR														as "EhrRusH17F8UTS3N"
								,rrb2.CurrVersion												as "EhrRusH17F8UTS3NVer"
							)
						)
					)
				)
			)
			into _Result 
			from RDS.T_DRUG_APPLICATION_LIST_ITEM AS dali
				JOIN RDS.T_DRUG_APPLICATION_LIST AS dal ON dal.DrugApplicationListID = dali.DrugApplicationList
				JOIN RDS.S_DLO_DoseView AS dv ON dv.Id = dali.DoseView
				LEFT JOIN PHARM.T_C_MNN mnn on mnn.CMnnID = dali.CMnn
				LEFT JOIN PHARM.T_C_TRN trn on trn.CTrnID = dali.CTrn
				JOIN VCLib.T_ENUMS eBt on eBt.EnumID = dali.BenefitType
				LEFT JOIN EHR.T_EHR_RUS_9A9G09E8KL er on er.EhrRusID = dali.EhrRus9a9g09e8kl
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				LEFT JOIN EHR.T_EHR_RUS_6KIQTS8T99 er1 on er1.EhrRusID = dali.EhrRus6kiqts8t99
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
				LEFT JOIN EHR.T_EHR_RUS_H17F8UTS3N er2 on er2.EhrRusID = dali.EhrRusH17F8UTS3N
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = er2.EhrRusRefBooks
				LEFT JOIN PHARM.T_MEDICATION_FORMS mf on mf.MedicationFormsID = dali.MedicationForms
			WHERE ("ID" is null or dali.DrugApplicationList = "ID") and DATEPART(YEAR, dal.EndDate) >= DATEPART(YEAR, "dateTime") and dal.IsActive = '1';

			-- Отправка
			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));
				-- Рассылаем пакет всем подписанным серверам AKUZ-а, если не указана очередь
		end if; 
	End;
End;
$$;