
/****** Object:  StoredProcedure [dbo].[sp_TFOMS_FreeBedsXml]    Script Date: 9/28/2020 3:15:30 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_TFOMS_FreeBedsXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_TFOMS_FreeBedsXml]
GO
--актуальное количество свободных мест

-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2020-02-03
-- Description:	Выгрузка информации в ТФОМС о количестве свободных мест в отделении
-- =============================================
CREATE PROCEDURE [dbo].[sp_TFOMS_FreeBedsXml]
	@QueueName varchar(255) = null
AS
BEGIN

	declare @Result varchar(max)
			,@ContentEncoding varchar(255)
			,@previous_day_8_hours datetime
			,@previous_day datetime
			,@current_day datetime
			,@tomorrow_day datetime
SET NOCOUNT ON;

select   
    @current_day = DATEADD(d,0,DateAdd(hour, 0, cast(floor(cast(getdate() as float))as datetime)))
   ,@previous_day_8_hours = DATEADD(d,-1,DateAdd(hour, 8, cast(floor(cast(getdate() as float))as datetime)))
   ,@previous_day = DATEADD(d,-1,DateAdd(hour, 0, cast(floor(cast(getdate() as float))as datetime)))
   ,@tomorrow_day = DATEADD(d,1,DateAdd(hour, 0, cast(floor(cast(getdate() as float))as datetime)))
   
   create table #lpu_main(lpuCode varchar(10));
   insert into #lpu_main values 
			 ('166601')
			--,('166602')
			--,('166507')
			--,('166601')
			--,('060013')
			--,('166733')
			--,('032013')
			--,('730029')
			--,('800001')
			--,('166101')
			--,('730022')
			--,('166507')
			--,('440010')
			--,('166602')
--_____________________________________________________________________________________________
;WITH xmlnamespaces ('https://services.rosminzdrav.ru/' as ser)
	SELECT @Result = 
	(
		select 

			 '1.0'																												as      'ser:HEAD/ser:VERSION'
			,'2CE0D39F'																											as      'ser:HEAD/ser:SENDER'
			,convert(varchar(10),CURRENT_TIMESTAMP, 20)																			as      'ser:HEAD/ser:D_EVENT'
			,			
			(
				select
				l.lpuCode																										as      'ser:CODE_MO'
				,(
					select 
					freebed.FreeBedsInformationID																				as         'ser:GUID'
					,l.lpuCode																									as         'ser:LPU'
					,CASE division.DivisionType
						WHEN 12808 THEN 2
						WHEN 12809 THEN 2
						WHEN 12810 THEN 2
					ELSE 1
					END																											as		   'ser:USL_OK'
					,division.Code																								as         'ser:PODR'
					,sb.Code																									as         'ser:PROFK'
					,(
						select SUM(ab.BedsCount) 
						from  [AKUZ].T_DIVISION_LINK_STRUCTURE_BED dlsb  
						join [AKUZ].T_AMOUNT_BED ab on ab.DivisionLinkStructureBed = dlsb.DivisionLinkStructureBedID
						join  [AKUZ].T_STRUCTURE_BED sb   on sb.StructureBedID = dlsb.StructureBed
						join [AKUZ].T_DIVISION dv on dlsb.Division = dv.DivisionID
						join [AKUZ].T_BRANCH br on dv.Branch = br.LpuID 
						join [AKUZ].T_LPU_MAIN lm on br.LpuMain = lm.LpuID
						where  dv.DivisionID = division.DivisionID 
						and (ab.DateEnd is null or ab.DateEnd >= @current_day)
					)																											as		  'ser:CNT_BED'	 --Всего коек в отделении

					,(select count(*) from [AKUZ].T_MOVEMENTS_DIVISION_CARD modc
									  join [AKUZ].T_MEDICAL_EPISODE me on me.MedicalEpisodeID = modc.MedicalEpisodeID
					where 
					 modc.Division =  division.DivisionID
					and modc.MovementTypeOut is null
					and me.DateOut is null
					and (	
						modc.MovementTypeIn = 0
						or modc.MovementTypeIn = 2
						or modc.MovementTypeIn = 9
						or modc.MovementTypeIn = 11
						or modc.MovementTypeIn = 13
						)
						and me.DateIn<=@previous_day_8_hours and (me.DateOut is null or me.DateOut >@previous_day_8_hours )
					)																											 as				  'ser:BUSY_BED'	 --Всего занятых коек на 8 часов утра прошедшего дня

					,(
						select SUM(ab.BedsCount) 
						from  [AKUZ].T_DIVISION_LINK_STRUCTURE_BED dlsb  
						join [AKUZ].T_AMOUNT_BED ab on ab.DivisionLinkStructureBed = dlsb.DivisionLinkStructureBedID
						join  [AKUZ].T_STRUCTURE_BED sb   on sb.StructureBedID = dlsb.StructureBed
						join [AKUZ].T_DIVISION dv on dlsb.Division = dv.DivisionID
						join [AKUZ].T_BRANCH br on dv.Branch = br.LpuID 
						join [AKUZ].T_LPU_MAIN lm on br.LpuMain = lm.LpuID
						where  (ab.DateEnd is null or ab.DateEnd >= @current_day)
						and dv.DivisionID = division.DivisionID
					)																											 as					  'ser:CNT_BED'	 --Всего коек в отделении

					,(select count(*) from [AKUZ].T_MOVEMENTS_DIVISION_CARD modc
									  join [AKUZ].T_MEDICAL_EPISODE me on me.MedicalEpisodeID = modc.MedicalEpisodeID
					where 
					 modc.Division =  division.DivisionID
					and modc.MovementTypeOut is null
					and me.DateOut is null
					and (	
						modc.MovementTypeIn = 0
						or modc.MovementTypeIn = 2
						or modc.MovementTypeIn = 9
						or modc.MovementTypeIn = 11
						or modc.MovementTypeIn = 13
						)
					and me.DateIn>=@previous_day and me.DateIn < @current_day
					)																											 as					'ser:ALL_HOSP'	 --Количество госпитализированных больных (Всего) за прошедшие сутки
					

					,(select count(*) 
					from [AKUZ].T_MOVEMENTS_DIVISION_CARD modc
					join [AKUZ].T_MEDICAL_EPISODE me on me.MedicalEpisodeID = modc.MedicalEpisodeID
					join [AKUZ].T_MOVEMENT mov on modc.Movement = mov.MovementID
					where 
					 modc.Division =  division.DivisionID
					and modc.MovementTypeOut is null
					and me.DateOut is null
					and (	
						modc.MovementTypeIn = 0
						or modc.MovementTypeIn = 2
						or modc.MovementTypeIn = 9
						or modc.MovementTypeIn = 11
						or modc.MovementTypeIn = 13
						)
					and mov.PaymentType = 12601
					and me.DateIn>=@previous_day and me.DateIn< @current_day
					)																											 as         'ser:OMS_HOSP'	 --
					,(select count(*) 
					from [AKUZ].T_MOVEMENTS_DIVISION_CARD modc
					join [AKUZ].T_MEDICAL_EPISODE me on me.MedicalEpisodeID = modc.MedicalEpisodeID
					join [AKUZ].T_MOVEMENT mov on modc.Movement = mov.MovementID
					where 
					modc.Division =  division.DivisionID
					and modc.MovementTypeOut is null
					and me.DateOut is null
					and (	
						modc.MovementTypeIn = 0
						or modc.MovementTypeIn = 2
						or modc.MovementTypeIn = 9
						or modc.MovementTypeIn = 11
						or modc.MovementTypeIn = 13
						)
					and mov.PaymentType = 12611
					and me.DateIn>=@previous_day and me.DateIn< @current_day
					)																											 as         'ser:HMP_HOSP'	 --
					,
					
					(select count(*) from [AKUZ].T_MOVEMENTS_DIVISION_CARD modc
					where 
					modc.Division =  division.DivisionID
					and modc.MovementTypeOut is null
					and me.DateOut is null
					and (	
						modc.MovementTypeOut = 1
						or modc.MovementTypeOut = 3
						or modc.MovementTypeOut = 7
						)
					and me.DateOut >=@previous_day and me.DateOut <@current_day
					)																											 as         'ser:ALL_OUT'	 --
					,
					(
					select count(*) from [AKUZ].T_MOVEMENTS_DIVISION_CARD modc
					join [AKUZ].T_MEDICAL_EPISODE me on me.MedicalEpisodeID = modc.MedicalEpisodeID
					join [AKUZ].T_MOVEMENT mov on modc.Movement = mov.MovementID
					where 
					modc.Division =  division.DivisionID
					and modc.MovementTypeOut is null
					and me.DateOut is null
					and (	
						modc.MovementTypeOut = 1
						or modc.MovementTypeOut = 3
						or modc.MovementTypeOut = 7
						)
					and mov.PaymentType = 12601
					and me.DateOut >=@previous_day and me.DateOut <@current_day
					)																											 as         'ser:OMS_OUT'	 --
					,
					(
					select count(*) from [AKUZ].T_MOVEMENTS_DIVISION_CARD modc
					join [AKUZ].T_MEDICAL_EPISODE me on me.MedicalEpisodeID = modc.MedicalEpisodeID
					join [AKUZ].T_MOVEMENT mov on modc.Movement = mov.MovementID
					where 
					modc.Division =  division.DivisionID
					and modc.MovementTypeOut is null
					and me.DateOut is null
					and (	
						modc.MovementTypeOut = 1
						or modc.MovementTypeOut = 3
						or modc.MovementTypeOut = 7
						)
					and mov.PaymentType = 12611
					and me.DateOut >=@previous_day and me.DateOut <@current_day
					)																											 as         'ser:HMP_OUT'	 --

					,(
						select count(*) from [AKUZ].T_HOSPITALIZATION_REFERRAL hsrf		
						join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hsrf.MedicalRecordID							
						join [AKUZ].T_BRANCH brT on brT.LpuID = hsrf.Branch
						join [AKUZ].T_LPU_MAIN lmT on brT.LpuMain = lmT.LpuID
						join [AKUZ].T_DIVISION dT on dT.DivisionID = ref.Division
						where 
						dT.DivisionID = division.DivisionID
						and hsrf.HospitalisationDate >=@current_day and  hsrf.HospitalisationDate<@tomorrow_day
					)																											 as         'ser:ALL_PLAN'	 --
					,(
						select count(*) from [AKUZ].T_HOSPITALIZATION_REFERRAL hsrf		
						join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hsrf.MedicalRecordID							
						join [AKUZ].T_BRANCH brT on brT.LpuID = hsrf.Branch
						join [AKUZ].T_LPU_MAIN lmT on brT.LpuMain = lmT.LpuID
						join [AKUZ].T_DIVISION dT on dT.DivisionID = ref.Division
						where 
						(dT.RevenueType=0 or dT.RevenueType=1)
						and dT.DivisionID = division.DivisionID
						and hsrf.HospitalisationDate >=@current_day and  hsrf.HospitalisationDate<@tomorrow_day
					)																											 as         'ser:OMS_PLAN'	 --Количество запланированных госпитализаций на текущий день в рамках ОМС
					,(
						select count(*) from [AKUZ].T_HOSPITALIZATION_REFERRAL hsrf		
						join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hsrf.MedicalRecordID							
						join [AKUZ].T_BRANCH brT on brT.LpuID = hsrf.Branch
						join [AKUZ].T_LPU_MAIN lmT on brT.LpuMain = lmT.LpuID
						join [AKUZ].T_DIVISION dT on dT.DivisionID = ref.Division
						where 
						dT.RevenueType is null
						and dT.DivisionID = division.DivisionID
						and hsrf.HospitalisationDate >=@current_day and  hsrf.HospitalisationDate<@tomorrow_day
					)																											 as         'ser:HMP_PLAN'	 --Количество запланированных госпитализаций на текущий день в рамках ВМП
					,
					(
						select 
						fb.Amount																								 as         'ser:CNT_FREE'	 --
						,isnull(fb.MaleBed,0)																					 as         'ser:CNT_FREE_M'	 --
						,isnull(fb.FemaleBed,0)																					 as         'ser:CNT_FREE_W'	 --
						,isnull(fb.ChildBed,0)																					 as			'ser:CNT_FREE_CH'	 --
						from [AKUZ].T_FREE_BEDS_INFORMATION fb
						join akuz.T_DIVISION_LINK_STRUCTURE_BED dlsb on fb.DivisionLinkStructureBed = dlsb.DivisionLinkStructureBedID
						join akuz.T_DIVISION d on dlsb.Division = d.DivisionID
						where d.DivisionID = division.DivisionID
						and fb.ActualDate >= @current_day 
						and fb.ActualDate <= DATEADD(d,10,@current_day)
						FOR XML PATH('ser:FREE_BED'),
						TYPE
					)
					from [AKUZ].T_DIVISION division 
					join [AKUZ].T_BRANCH branch on division.Branch = branch.LpuID
					join [AKUZ].T_LPU_MAIN lpumain on branch.LpuMain = lpumain.LpuID
					join  [AKUZ].T_DIVISION_LINK_STRUCTURE_BED dlinksb on division.DivisionID = dlinksb.Division
					join  [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = dlinksb.StructureBed
					join  [AKUZ].T_FREE_BEDS_INFORMATION freebed on freebed.DivisionLinkStructureBed = dlinksb.DivisionLinkStructureBedID and freebed.ActualDate = @current_day
					where lpumain.LpuCode = l.lpuCode  
					FOR XML PATH('ser:REC'),
					TYPE
				)
				from #lpu_main l
				FOR XML PATH('ser:DATA'),
				TYPE
			)
		FOR XML PATH(''),
		ROOT('ser:SendTrafficRqst')
	) 

		INSERT INTO [AKUZ].[T_RABBIT_SEND_OBJECT]
		   ([RabbitSendObjectID]
		   ,[QueueName]
		   ,[Content]
		   ,[MessageID]
		   ,[Headers]
		   ,[ContentEncoding]
		   ,[Type]
		   ,[UserID]
		   ,[State]
		   ,[CreateDateTime]
		   ,[OwnerID])
		select
		   NewID()--(RabbitSendObjectID, uniqueidentifier,
		   ,@QueueName--,QueueName, varchar(255),
		   ,dbo.ConvertTextToBinaryZip(@Result, '')--,Content, text,
		   ,null--,MessageID, varchar(255),
		   ,null--,Headers, varchar(255),
		   ,'utf-8'--,ContentEncoding, varchar(255),
		   ,'SendTrafficRqst'--,Type, varchar(255),
		   ,null--,UserID, varchar(255),
		   ,0--,State, int,)
		   ,GETDATE()
		   ,null

		drop table #lpu_main
END
GO
