IF EXISTS
(
	SELECT *
	FROM   sys.objects
	WHERE  object_id = OBJECT_ID(N'[dbo].[sp_GetSetDrugApplicationList]')
		   AND [type] IN(N'P', N'PC')
)
	BEGIN
		DROP PROCEDURE [dbo].[sp_GetSetDrugApplicationList];
	END;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-09-29
-- Description: Выгрузка заявочного списка из RDS
-- =============================================


CREATE PROCEDURE [dbo].[sp_GetSetDrugApplicationList] 
			@Xml       xml              = NULL,
			@IsNew     bit              = 1,
			@ID        uniqueidentifier = NULL,
			@QueueName varchar(100)     = NULL,
			@dateTime  datetime         = NULL,
			@User      uniqueidentifier = NULL
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @Result varchar(max), @ContentEncoding varchar(255), @Type varchar(255), @OperationType varchar(255), @OwnerID uniqueidentifier, @FTLOAD varchar(6)= 'FTLoad', @UPDATE varchar(6) = 'Update';
		SELECT @dateTime = ISNULL(@dateTime, GETDATE())
		IF(@Xml IS NULL)
			BEGIN

				-- Инициализация
				SELECT @ContentEncoding = 'utf-8',
					   @Type = 'DRUG_APPLICATION_LIST',
					   @OwnerID = @ID;

				--выясняем тип операции
				SELECT @OperationType = case when @ID is null then @FTLOAD else @UPDATE end;

				-- собираем DRUG_APPLICATION_LIST
				SELECT        @Result =
				(
					SELECT    @OperationType AS 'header/TYPE',
					(
						SELECT [dali].[DrugApplicationListItemID]									AS 'ID'
							  ,CONVERT( varchar(10), [dal].[BeginDate], 104)						AS 'BeginDate'
							  ,CONVERT( varchar(10), ISNULL(dali.EndDate, [dal].[EndDate]), 104)	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'
                              ,[dali].Preparation													as 'Preparation'
							  ,er.CodeEHR															as 'EhrRus9a9g09e8kl'
							  ,rrb.CurrVersion														as 'EhrRus9a9g09e8klVer'
							  ,er1.IdEHR															as 'EhrRus6kiqts8t99'
							  ,rrb1.CurrVersion														as 'EhrRus6kiqts8t99Ver'
							  ,er2.IdEHR															as 'EhrRusH17F8UTS3N'
							  ,rrb2.CurrVersion														as 'EhrRusH17F8UTS3NVer'
						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
						FOR XML PATH('DRUG_APPLICATION_LIST_ITEM'), TYPE ) AS  'body/DRUG_APPLICATION_LIST_ITEMS'
					FOR XML PATH(''), ROOT('DRUG_APPLICATION_LIST')
				); 

				-- Отправка
				INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT]
				([RabbitSendObjectID],
				 [QueueName],
				 [Content],
				 [MessageID],
				 [Headers],
				 [ContentEncoding],
				 [Type],
				 [UserID],
				 [State],
				 [CreateDateTime],
				 [OwnerID]
				)
				SELECT NEWID(), --(<RabbitSendObjectID, uniqueidentifier,>
					   [s].[ServerName], --,<QueueName, varchar(255),>
					   dbo.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;
	END;
GO