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


CREATE PROCEDURE [dbo].sp_GetSetRequestCatalog	@Xml       xml              = NULL,
												@ID        uniqueidentifier = NULL,
												@IsNew     bit              = 1,
												@QueueName varchar(100)     = 'RDSSync',
												@dateTime  datetime         = NULL,
												@User      uniqueidentifier = NULL
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @OperationType varchar(255), @FTLOAD varchar(6), @UPDATE varchar(6);
		SELECT @FTLOAD = 'FTLoad';
		SELECT @UPDATE = 'Update';
		IF @Xml IS NOT NULL
			BEGIN
				--DECLARE @BeginDate datetime, @EndDate datetime;
				
				-- Получаем тип операции
				SELECT @OperationType = [c].value('./header[1]/TYPE[1]', 'varchar(36)')
				FROM   @Xml.nodes('DRUG_APPLICATION_LIST') AS T(c);
				
				-- Создаём временную таблицу #DRUG_APPLICATION_LIST_ITEM из xml
				SELECT [c].value('ID[1]', 'uniqueidentifier')								AS [ID]
					   ,CONVERT( datetime, [c].value('BeginDate[1]', 'varchar(10)'), 104)	AS [BeginDate]
					   ,CONVERT( datetime, [c].value('EndDate[1]', 'varchar(10)'), 104)		AS [EndDate]
					   ,[c].value('Price[1]', 'decimal(31,10)')								AS [Price]
					   ,[c].value('RequestCode[1]', 'int')									AS [RequestCode]
					   ,[c].value('CMnnID[1]', 'int')										AS [CMnnID]
					   ,[c].value('CTrnID[1]', 'int')										AS [CTrnID]
					   ,[c].value('GroupsOfDrugsClassification[1]', 'int')					AS [GroupsOfDrugsClassification]
					   ,[c].value('BenefitType[1]', 'int')									AS [BenefitType]
					   ,[c].value('DoseView[1]', 'varchar(255)')							AS [DoseView]
					   ,[c].value('MedicationForms[1]', 'int')								AS [MedicationForms]
					   ,[c].value('./PackQuantity[1]', 'int')								AS [PackQuantity]
					   ,[c].value('./IsActive[1]', 'bit')									AS [IsActive]
					   ,[c].value('./ElectronicCertificate[1]', 'bit')						AS [ElectronicCertificate]
					   ,[c].value('./SectionCode[1]', 'varchar(255)')						AS [SectionCode]
					   ,[c].value('./Comment[1]', 'varchar(255)')							AS [Comment]
					   ,[c].value('./DoseCount[1]', 'decimal(5, 2)')						AS [DoseCount]
					   ,[c].value('./ConsumerUnits[1]', 'decimal(31, 10)')					AS [ConsumerUnits]
					   ,[c].value('./PersRequest[1]', 'bit')								AS [PersRequest]
					   ,t.c.value('./EhrRus9a9g09e8kl[1]', 'varchar(52)')					AS [EhrRus9a9g09e8kl]
					   ,t.c.value('./EhrRus9a9g09e8klVer[1]', 'varchar(8)')					AS [EhrRus9a9g09e8klVer]
					   ,t.c.value('./EhrRus6kiqts8t99[1]', 'int')							AS [EhrRus6kiqts8t99]
					   ,t.c.value('./EhrRus6kiqts8t99Ver[1]', 'varchar(8)')					AS [EhrRus6kiqts8t99Ver]
					   ,t.c.value('./EhrRusH17F8UTS3N[1]', 'int')							AS [EhrRusH17F8UTS3N]
					   ,t.c.value('./EhrRusH17F8UTS3NVer[1]', 'varchar(8)')					AS [EhrRusH17F8UTS3NVer]
					   INTO [#DRUG_APPLICATION_LIST_ITEM]
				FROM   @Xml.nodes('DRUG_APPLICATION_LIST/body/DRUG_APPLICATION_LIST_ITEMS/DRUG_APPLICATION_LIST_ITEM') AS T(c)
				
						-- Загружаем новые DoseView
						INSERT INTO [AKUZ].S_DLO_DoseView(Value)
						SELECT RTRIM(LTRIM(dli.[DoseView]))
						FROM   #DRUG_APPLICATION_LIST_ITEM AS dli
						WHERE  NOT EXISTS
						(
							SELECT 1
							FROM   [AKUZ].[S_DLO_DoseView] AS [dv]
							WHERE  RTRIM(LTRIM([dv].[Value])) = RTRIM(LTRIM(dli.[DoseView]))
						)
						GROUP BY RTRIM(LTRIM(dli.[DoseView]));
				
						-- Обновляем имеющиеся BASE_DRUG_REQUEST_ITEM
						UPDATE bdri
						  SET
							   [bdri].[BeginDate] = dli.[BeginDate]
							  ,[bdri].[Price] = dli.[Price]
							  ,[bdri].[RequestCode] = dli.[RequestCode]
							  ,[bdri].[AppListBeginDate] = dli.[BeginDate]
							  ,[bdri].[AppListEndDate] = dli.[EndDate]
							  ,[bdri].[MedicationForms] = mf.[MedicationFormsID]
							  ,[bdri].[PersRequest] = ISNULL(dli.[PersRequest],0)
						FROM [AKUZ].[T_BASE_DRUG_REQUEST_ITEM] [bdri]
							 JOIN #DRUG_APPLICATION_LIST_ITEM dli ON dli.[ID] = [bdri].[BaseDrugRequestItemID]
							 left join PHARM.T_MEDICATION_FORMS mf on mf.Code = dli.MedicationForms and mf.Active = 1
						WHERE  [bdri].[type_] = 1233;
					
						-- Обновляем имеющиеся REQUEST_CATALOG 
						UPDATE rc
						  SET
							  [rc].[CreateDate] = dli.[BeginDate]
							 ,[rc].[DoseView] = [dv].[Id]
							 ,[rc].[GroupsOfDrugsClassification] = dli.[GroupsOfDrugsClassification]
							 ,[rc].[CMnn] = mnn.[CMnnID]
							 ,[rc].[CTrn] = trn.[CTrnID]
							 ,[rc].[BenefitType] = eBt.EnumID
							 ,[rc].PackQuantity = dli.PackQuantity
							 ,[rc].[IsActive] = ISNULL(dli.[IsActive], 1)
							 ,[rc].[ElectronicCertificate] = ISNULL(dli.[ElectronicCertificate], 0)
							 ,[rc].SectionCode=dli.SectionCode
							 ,[rc].Comment=dli.Comment
							 ,[rc].DoseCount=dli.DoseCount
							 ,[rc].EhrRus9a9g09e8kl = ehr.EhrRusID
							 ,[rc].EhrRus6kiqts8t99 = ehr1.EhrRusID
							 ,[rc].EhrRusH17F8UTS3N = ehr2.EhrRusID
							 ,[rc].ConsumerUnits = dli.ConsumerUnits
						FROM [AKUZ].[T_REQUEST_CATALOG] [rc]
							 JOIN #DRUG_APPLICATION_LIST_ITEM dli ON dli.[ID] = [rc].[BaseDrugRequestItemID]
							 left join VCLib.T_ENUMS eBt on eBt.Code = dli.[BenefitType] and eBt.EnumTypeID = 242
							 cross apply (
								select top 1 dv.Id
								from [AKUZ].[S_DLO_DoseView] [dv]
								where RTRIM(LTRIM([dv].[Value])) = RTRIM(LTRIM(dli.[DoseView]))
							 ) dv
							 outer apply (
								select top 1 m.CMnnID
								from PHARM.T_C_MNN m 
								where m.OtherID = dli.[CMnnID]  and m.Active = 1 
							 ) mnn
							  outer apply (
								select top 1 t.CTrnID
								from PHARM.T_C_TRN t 
								where t.OtherID = dli.[CTrnID] and t.Active = 1
							 ) trn
							  outer apply (
							  	select top 1 er.EhrRusID 
							  	from EHR.T_EHR_RUS_9A9G09E8KL er
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							  	where er.CodeEHR = dli.EhrRus9a9g09e8kl and rrb.CurrVersion = dli.EhrRus9a9g09e8klVer
							  ) ehr
							  outer apply (
							  	select top 1 er.EhrRusID 
							  	from EHR.T_EHR_RUS_6KIQTS8T99 er
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = dli.EhrRus6kiqts8t99 and rrb.CurrVersion = dli.EhrRus6kiqts8t99Ver
							  ) ehr1
							  outer apply (
							  	select top 1 er.EhrRusID 
							  	from EHR.T_EHR_RUS_H17F8UTS3N er
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = dli.EhrRusH17F8UTS3N and rrb.CurrVersion = dli.EhrRusH17F8UTS3NVer
							  ) ehr2;

						-- Загружаем новые BASE_DRUG_REQUEST_ITEM
						INSERT INTO [AKUZ].T_BASE_DRUG_REQUEST_ITEM
						([BaseDrugRequestItemID],
						 [BeginDate],
						 [Price],
						 [MedicationForms],
						 [RequestCode],
						 [AppListBeginDate],
						 [AppListEndDate],
						 [type_],
						 [PersRequest]
						)
						SELECT dli.[ID],
							   dli.[BeginDate],
							   dli.[Price],
							   mf.[MedicationFormsID],
							   dli.[RequestCode],
							   dli.[BeginDate],
							   dli.[EndDate],
							   1233,
							   ISNULL(dli.[PersRequest],0)
						FROM   #DRUG_APPLICATION_LIST_ITEM AS dli
							 left join PHARM.T_MEDICATION_FORMS mf on mf.Code = dli.MedicationForms and mf.Active = 1
						WHERE  NOT EXISTS
						(
							SELECT [bdri].[BaseDrugRequestItemID]
							FROM   [AKUZ].[T_BASE_DRUG_REQUEST_ITEM] AS [bdri]
							WHERE  [bdri].[BaseDrugRequestItemID] = dli.[ID]
						);
						
						-- Загружаем новые REQUEST_CATALOG
						INSERT INTO [AKUZ].T_REQUEST_CATALOG
						([BaseDrugRequestItemID],
						 [CreateDate],
						 [DoseView],
						 [GroupsOfDrugsClassification],
						 [CMnn],
						 [CTrn],
						 [BenefitType],
						 [PackQuantity],
						 [IsActive],
                         ElectronicCertificate,
						 SectionCode,
						 Comment,
						 DoseCount,
                         ConsumerUnits,
						 EhrRus9a9g09e8kl,
						 EhrRus6kiqts8t99,
						 EhrRusH17F8UTS3N
						)
						SELECT	
								dli.[ID],
								dli.[BeginDate],
								[dv].[Id],
								dli.[GroupsOfDrugsClassification],
								mnn.[CMnnID],
								trn.[CTrnID],
								eBt.EnumID,
								dli.PackQuantity,
								ISNULL(dli.[IsActive], 1),
								ISNULL(dli.[ElectronicCertificate], 0),
								SectionCode,
								Comment,
								dli.DoseCount,
								dli.ConsumerUnits,
								ehr.EhrRusID,
								ehr1.EhrRusID,
								ehr2.EhrRusID
						FROM   #DRUG_APPLICATION_LIST_ITEM AS dli
							   left join VCLib.T_ENUMS eBt on eBt.Code = dli.[BenefitType] and eBt.EnumTypeID = 242
							 cross apply (
								select top 1 dv.Id
								from [AKUZ].[S_DLO_DoseView] [dv]
								where RTRIM(LTRIM([dv].[Value])) = RTRIM(LTRIM(dli.[DoseView]))
							 ) dv
							 outer apply (
								select top 1 m.CMnnID
								from PHARM.T_C_MNN m 
								where m.OtherID = dli.[CMnnID] and m.Active = 1
							 ) mnn
							  outer apply (
								select top 1 t.CTrnID
								from PHARM.T_C_TRN t 
								where t.OtherID = dli.[CTrnID] and t.Active = 1
							 ) trn
							  outer apply (
							  	select top 1 er.EhrRusID 
							  	from EHR.T_EHR_RUS_9A9G09E8KL er
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							  	where er.CodeEHR = dli.EhrRus9a9g09e8kl AND RRB.CURRVERSION = dli.EhrRus9a9g09e8klVer
							  ) ehr
							  outer apply (
							  	select top 1 er.EhrRusID 
							  	from EHR.T_EHR_RUS_6KIQTS8T99 er 
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = dli.EhrRus6kiqts8t99 AND RRB.CURRVERSION = dli.EhrRus6kiqts8t99Ver
							  ) ehr1
							  outer apply (
							  	select top 1 er.EhrRusID 
							  	from EHR.T_EHR_RUS_H17F8UTS3N er
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = dli.EhrRusH17F8UTS3N AND RRB.CURRVERSION = dli.EhrRusH17F8UTS3NVer
							  ) ehr2
						WHERE  NOT EXISTS
						(
							SELECT [rc].[BaseDrugRequestItemID]
							FROM   [AKUZ].[T_REQUEST_CATALOG] AS [rc]
							WHERE  [rc].[BaseDrugRequestItemID] = dli.[ID]
						);

						IF @OperationType = @FTLOAD
						BEGIN
						-- Закрываем REQUEST_CATALOG которых нет
						update rc
						set rc.IsActive = 0
						FROM [AKUZ].[T_REQUEST_CATALOG] [rc]
						WHERE rc.IsActive = 1 and
							   NOT EXISTS
						(
							SELECT dli.[ID]
							FROM   [dbo].[#DRUG_APPLICATION_LIST_ITEM] AS dli
							WHERE  dli.[ID] = rc.[BaseDrugRequestItemID]
						);

						-- Закрываем BASE_DRUG_REQUEST_ITEM которых нет
						update bdri
						set bdri.AppListEndDate = Getdate()
						FROM [AKUZ].[T_BASE_DRUG_REQUEST_ITEM] [bdri]
						WHERE  bdri.AppListEndDate > Getdate() and
							   NOT EXISTS
						(
							SELECT dli.[ID]
							FROM   [dbo].[#DRUG_APPLICATION_LIST_ITEM] AS dli
							WHERE  dli.[ID] = [bdri].[BaseDrugRequestItemID]
						);
						end;
				
				-- Удаляем временные таблицы
				DROP TABLE #DRUG_APPLICATION_LIST_ITEM;
			END;
	END;
