
/****** Object:  StoredProcedure [dbo].[sp_TFOMS_HospitalisationXml]    Script Date: 10/2/2020 10:55:33 AM ******/
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_HospitalisationXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_TFOMS_HospitalisationXml]
GO
-- =============================================
-- Author:	Ilya Zhuykov	Vitacore (с)
-- Create date: 2020-10-02
-- Description:	Выгрузка информации о госпитализации в ТФОМС
-- =============================================
CREATE PROCEDURE [dbo].[sp_TFOMS_HospitalisationXml]
	@ID uniqueidentifier = null,
	@QueueName varchar(255) = null,	
	@Sender varchar(255) = null,
	@LpuCode varchar(255) = null,	
	@mainID uniqueidentifier = null
AS
BEGIN

declare @Result varchar(max)
		,@refNumber varchar(225)
SET NOCOUNT ON;

--МО-исполнители где проверка включена
   create table #lpu_main(lpuCode varchar(10));
   insert into #lpu_main values  
  ('010050')
  --,('166601')


--проверка для принимающих МО
	if exists(select * from #lpu_main lm where lm.lpuCode = @LpuCode )
	begin
	declare  @dir_Number  varchar(225)
			,@dir_lpu  varchar(225)
			
		select @dir_Number = db.DirectionNumber,@dir_lpu =lpDir.LpuCode from [AKUZ].T_MOVEMENT mov 
			join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
			left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
			left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = dir.DirectionID
			left join [AKUZ].T_LPU_MAIN lpDir on lpDir.LpuID = db.DirectorLpu
		where mov.MovementID = @ID

		if @dir_Number is null
		begin
		declare @ent_id int = (select top 1 EntityID from vclib.t_Entities where EntName = 'HOSPITALIZATION_REFERRAL')
		declare  @NameForGenNumber varchar(32) = @dir_lpu+ YEAR(GETDATE())		

		exec @dir_Number = [dbo].[sp_GenerateNumber] @ent_id, @NameForGenNumber, 1
		
			update db set db.DirectionNumber = @dir_Number
			from [AKUZ].T_DIRECTION_BASE db
			where db.DirectionID = (select dir.DirectionID 
			from [AKUZ].T_MOVEMENT mov 
			left join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
			left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
			where mov.MovementID = @ID)		
		end
	end
	
	--select @refNumber = Concat(lpDir.LpuCode,YEAR(GETDATE()),case 
	--when db.DirectionNumber<10 then concat('0000',db.DirectionNumber)
	--when db.DirectionNumber<100 then concat('000',db.DirectionNumber)
	--when db.DirectionNumber<1000 then concat('00',db.DirectionNumber)
	--when db.DirectionNumber<10000 then concat('0',db.DirectionNumber)
	--else db.DirectionNumber
	--end)
	select @refNumber = lpDir.LpuCode  +'-'
		+ cast(YEAR(GETDATE()) as varchar(4))  +'-'
		+ case when len(db.DirectionNumber) <= 5 then right('0000' + db.DirectionNumber, 5) else db.DirectionNumber end
	from [AKUZ].T_MOVEMENT mov 
		join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
		left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
		left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = dir.DirectionID
		left join [AKUZ].T_LPU_MAIN lpDir on lpDir.LpuID = db.DirectorLpu
	where mov.MovementID = @ID and db.DirectionNumber is not null
	
--__________xml______________________________________________________________________________________________________________________________--

;WITH xmlnamespaces ('https://services.rosminzdrav.ru/' as ser)
	SELECT @Result = 
	(
			SELECT 
			 '1.0'				                                   		    as 'ser:HEAD/ser:VERSION'				 --версия
			,@Sender	                                             		as 'ser:HEAD/ser:SENDER'			     --МО-отправитель сведений
			,convert(varchar(10),CURRENT_TIMESTAMP, 20)            		    as 'ser:HEAD/ser:D_EVENT'				 --Дата отправления сведений
			--,''												   		    as 'ser:HEAD/ser:GUID_QUERY'			 --Идентификатор для №4

			,lm.LpuCode                                                		as 'ser:DATA/ser:CODE_MO'				 --Принимающая направление МО
			,IsNull(tt.MedicalRecordID,@ID )                                as 'ser:DATA/ser:REC/ser:GUID'			 --Уникальный код записи 
			

			,CASE WHEN  mdc.HospitalizationType = 13001 THEN
			lpDir.LpuCode	
			else null
			end																as 'ser:DATA/ser:REC/ser:NPR_MO'		 --Реестровый номер МО, направившей пациента на госпитализацию

			,CASE 
				WHEN mdc.HospitalizationType = 13001 THEN null
				WHEN brDir.TFomsCode like '%.1' THEN null 
				ELSE brDir.TFomsCode
			 END 													   		as 'ser:DATA/ser:REC/ser:NPR_LPU'		 --Региональный код подразделения МО, направившего пациента на госпитализацию			                                              		    
			,lm.LpuCode 										   			as 'ser:DATA/ser:REC/ser:DEST_MO'		 --Реестровый номер МО, в которую направлен/госпитализирован пациент
			,CASE 
				WHEN br.TFomsCode like '%.1' THEN null 
				ELSE br.TFomsCode
			 END												   		    as 'ser:DATA/ser:REC/ser:DEST_LPU'		 --Региональный код подразделения МО, в которое направлен/госпитализирован пациент
			,CASE mdc.HospitalizationType
				WHEN 13001 THEN 3
				WHEN 13002 THEN 1
				WHEN 13003 THEN 2
			 END															as 'ser:DATA/ser:REC/ser:FOR_POM'	         --Форма оказания медицинской помощи F003
			,pat.LastName													as 'ser:DATA/ser:REC/ser:PACIENT/ser:FAM'	 --Фамилия пациента
			,pat.FirstName													as 'ser:DATA/ser:REC/ser:PACIENT/ser:IM'	 --Имя пациента
			,pat.FatherName													as 'ser:DATA/ser:REC/ser:PACIENT/ser:OT'	 --Отчество пациента
			,(case when pat.Sex = 10301 then 1 else 2 end)					as 'ser:DATA/ser:REC/ser:PACIENT/ser:W'	     --Пол
			,convert(varchar(10), pat.Birthday, 20)							as 'ser:DATA/ser:REC/ser:PACIENT/ser:DR'	 --День рождения пациента

			--,''															as 'ser:DATA/ser:REC/ser:PACIENT/ser:FAM_P'	 --Фамилия представителя пациента
			--,''															as 'ser:DATA/ser:REC/ser:PACIENT/ser:IM_P'	 --Имя представителя пациента
			--,''															as 'ser:DATA/ser:REC/ser:PACIENT/ser:OT_P'	 --Отчество представителя пациента
			--,''															as 'ser:DATA/ser:REC/ser:PACIENT/ser:DR_P'	 --День рождения представителя пациента

			,CASE pol.PolicyForm
				WHEN 4 THEN 2
				WHEN 1 THEN 1
				else 3
			END														as 'ser:DATA/ser:REC/ser:PACIENT/ser:VPOLIS'	 --Тип ДПФС
			,pol.Serial     												as 'ser:DATA/ser:REC/ser:PACIENT/ser:SPOLIS'	 --Серия ДПФС
			,pol.Number    									    			as 'ser:DATA/ser:REC/ser:PACIENT/ser:NPOLIS'	 --Номер ДПФС
			,ISNULL(i1.SmoCod, i2.SmoCod)					    			as 'ser:DATA/ser:REC/ser:PACIENT/ser:SMO'		 --Реестровый номер СМО
			--,''															as 'ser:DATA/ser:REC/ser:PACIENT/ser:SMO_NAM'	 --Наименование СМО - не заполнять если номер есть
			,substring (ISNULL(ISNULL(r1.Okato, r2.Okato),''),1,5)			as 'ser:DATA/ser:REC/ser:PACIENT/ser:ST_OKATO'	 --Регион страхования
			--,''															as 'ser:DATA/ser:REC/ser:PACIENT/ser:NOVOR_NUM'	 --Номер ДПФС
			,isnull(tt.Phone ,
			(select top 1 Number  
			from akuz.t_phone_number ph_n
			where ph_n.Patient = pat.PatientID))							as 'ser:DATA/ser:REC/ser:PACIENT/ser:CONTACT'	 --Контактный номер
			
			,
CASE WHEN  mdc.HospitalizationType = 13001 THEN
			IsNull(convert(varchar(10), tt.ReferralDate, 20),
			convert(varchar(10), db.DirectionDate, 20))
ELSE null
			END																as 'ser:DATA/ser:REC/ser:NPR/ser:NPR_DATE'	 --Номер ДПФС
			,
CASE WHEN  mdc.HospitalizationType = 13001 THEN
			@refNumber			
ELSE null
			END																as 'ser:DATA/ser:REC/ser:NPR/ser:NPR_NUM'	 --Номер ДПФС
			,
CASE WHEN  mdc.HospitalizationType = 13001 THEN
			IsNull(tt.MkbCode,db.Mkb)
ELSE null
			END																as 'ser:DATA/ser:REC/ser:NPR/ser:DS'
			,CASE 
			-- исходного направления нет, но номер направления установлен
				when mdc.HospitalizationType != 13001 THEN NULL
				when tt.DivisionType is null and @refnumber is not null then
					CASE d.DivisionType
						WHEN 12808 THEN 2
						WHEN 12809 THEN 2
						WHEN 12810 THEN 2
					ELSE 1
					end
				when tt.DivisionType is null then null
				WHEN tt.DivisionType  = 12808 THEN 2
				WHEN tt.DivisionType  =  12809 THEN 2
				WHEN tt.DivisionType  = 12810 THEN 2				
				ELSE 1
				END															as 'ser:DATA/ser:REC/ser:NPR/ser:USL_OK'     --Условия оказания МП V006 			
			,
			CASE
				when mdc.HospitalizationType != 13001 THEN NULL
				WHEN @refnumber is null THEN null 
				ELSE IsNull(tt.sbCode, sb.CodeMz)
			end																as 'ser:DATA/ser:REC/ser:NPR/ser:PROFK'
			
			,
CASE WHEN  mdc.HospitalizationType = 13001 THEN
			isnull(tt.divCode,d.CodeMz )
ELSE null
			END																as 'ser:DATA/ser:REC/ser:NPR/ser:PODR'
			,
CASE WHEN  mdc.HospitalizationType = 13001 THEN
			tt.Snils
ELSE null
			END																as 'ser:DATA/ser:REC/ser:NPR/ser:CODE_MD'  --СНИЛС медработника, направившего больного
			,
CASE WHEN  mdc.HospitalizationType = 13001 THEN
			isNull(
				isNull(convert(varchar(10), tt.HospitalisationDate, 20)
				,convert(varchar(10), db.PreHospitalDate, 20)),
			convert(varchar(10), mov.MovementTime, 20))
ELSE null
			END																as 'ser:DATA/ser:REC/ser:NPR/ser:DATE_P'
				
-----------------------------------------------------------------------------------------------------------------------------------------------------
			,isnull(isnull(rdrd.Mkb ,mov.Mkb) ,db.Mkb)						as 'ser:DATA/ser:REC/ser:HOSP/ser:DS_P'		--Диагноз приемного отделения/диагноз при поступлении в отделение
			
,CASE WHEN  mdc.HospitalizationType = 13001 THEN
			convert(varchar(10),db.PreHospitalDate	, 20)	
			ELSE null
			END																as 'ser:DATA/ser:REC/ser:HOSP/ser:DATE_P'	--Плановая дата госпитализации,
			,convert(varchar(10), mov.ExpectedDateOfDischarge, 20)			as 'ser:DATA/ser:REC/ser:HOSP/ser:DATE_V'	--Плановая дата выбытия
			,CASE d.DivisionType
				WHEN 12808 THEN 2
				WHEN 12809 THEN 2
				WHEN 12810 THEN 2
			ELSE 1
			END																as 'ser:DATA/ser:REC/ser:HOSP/ser:USL_OK'	--Условия оказания МП
			,sb.CodeMz														as 'ser:DATA/ser:REC/ser:HOSP/ser:PROFK'	--Код профиля койки
			,d.CodeMz															as 'ser:DATA/ser:REC/ser:HOSP/ser:PODR'		--Код отделения
			,mov.FullMedCardNum												as 'ser:DATA/ser:REC/ser:HOSP/ser:N_MKSB'	--Номер карты стационарного больного
			,replace(convert(varchar(20), mov.MovementTime, 20),' ','T')	as 'ser:DATA/ser:REC/ser:HOSP/ser:DATE_1'	--Дата и время фактической госпитализации пациента
			--,''															as 'ser:DATA/ser:REC/ser:HOSP/ser:DATE_2'	--Предполагаемая дата выбытия
			--,''															as 'ser:DATA/ser:REC/ser:HOSP/ser:DS'		--Диагноз при выбытии

		from [AKUZ].T_MOVEMENT mov 
		left join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = mov.StructureBed
		left join [AKUZ].T_V002_PROFILE v0 on v0.V002ProfileID = sb.V002Profile
		left join [AKUZ].T_MEDICALCARD_REGISTRATION mdc on mdc.MovementID = mov.MovementID
		--LPU
		join [AKUZ].T_DIVISION d on d.DivisionID = mov.Division
		join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
		join [AKUZ].T_LPU_MAIN lm on br.LpuMain = lm.LpuID
		--patient
		join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
		join [AKUZ].T_EMERGENCY_EVENT ee on ee.EventID = eb.EventID
		join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
		join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
		
		--diagnosis
		left join [AKUZ].T_RECEPTION_DIAGNOSIS rd on rd.EmergencyEvent = eb.EventID 
		left join [AKUZ].T_DIAGNOSIS rdrd on rdrd.MedicalRecordID =  rd.MedicalRecordID 
		--polis
		left join [AKUZ].T_POLICY pol on pol.PolicyID = [dbo].[fn_GetOmsPolicy](pat.PatientID,mov.MovementDate)
		left join [AKUZ].T_INSURANCE_COMPANY ic on ic.OrganizationID = pol.InsuranceCompany
		left join [AKUZ].T_INSURER i1 on i1.InsurerID = ic.Insurer
		left join [AKUZ].T_INSURER i2 on i2.InsurerID = pol.Insurer
		left join [AKUZ].V_INSURER_REGION ir1 on ir1.InsurerRegionID = i1.OKATO		
		left join [AKUZ].T_REGION r1 on r1.RegionID = ir1.Region
		left join [AKUZ].V_INSURER_REGION ir2 on ir2.InsurerRegionID = i2.OKATO		
		left join [AKUZ].T_REGION r2 on r2.RegionID = ir2.Region
		--referral
		left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
		left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = dir.DirectionID
		left join [AKUZ].T_LPU_MAIN lpDir on lpDir.LpuID = db.DirectorLpu
		left join [AKUZ].T_DIVISION dDir on  dDir.DivisionID= dir.DirectorDivision
		left join [AKUZ].T_BRANCH brDir on brDir.LpuID = dDir.Adress
		
		--original referral
		outer apply
		(
			select hr.MedicalRecordID, hr.Phone,hrr.ReferralDate,hrr.MkbCode, dT.DivisionType, hrsb.CodeMz as sbCode,hr.HospitalisationDate,dT.CodeMz as divCode,hrw.Snils
			from [AKUZ].T_HOSPITALIZATION_REFERRAL hr
			join [AKUZ].T_REFERRAL hrr on hrr.MedicalRecordID = hr.MedicalRecordID
			join [AKUZ].T_MEDICAL_RECORD hrmr on hrmr.MedicalRecordID = hrr.MedicalRecordID
			join [AKUZ].T_EVENT_BASE hrae on  hrae.EventID = hrmr.AmbulanceEvent
			join [AKUZ].T_AMBULANCE_CARD hrac on  hrac.AmbulanceCardID = hrae.AmbulanceCard
			join [AKUZ].T_PATIENT hrp on  hrp.PatientID = hrac.Patient
			join [AKUZ].T_STRUCTURE_BED hrsb on hrsb.StructureBedID = hr.StructureBed
			join [AKUZ].T_DIVISION dT on dT.DivisionID = hrr.Division			
			join [AKUZ].T_POST hrpst on hrpst.ResourceID = hrmr.Post
			join [AKUZ].T_RESOURCE hrrs on hrrs.ResourceID = hrpst.ResourceID
			join [AKUZ].T_WORKER hrw on hrpst.Worker = hrw.WorkerID
			where hr.ReferralNumber = db.DirectionNumber
			and hrr.Lpu = db.Lpu
			and hrp.PatientID = pat.PatientID
			and hrr.ReferralDate <= eb.EventDate
			and DATEADD(M,2,hrr.ReferralDate)>= eb.EventDate
		) tt
		where mov.MovementID = @ID

		FOR XML PATH(''),
		ROOT('ser:SendRouteRqst')
	) 	
		select @mainID = IsNull(tt.MedicalRecordID,mov.MovementID )
		from [AKUZ].T_MOVEMENT mov
			join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
			join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
			join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
			left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = eb.Direction
			outer apply
			(
				select hr.MedicalRecordID
				from [AKUZ].T_HOSPITALIZATION_REFERRAL hr
				join [AKUZ].T_REFERRAL hrr on hrr.MedicalRecordID = hr.MedicalRecordID
				join [AKUZ].T_MEDICAL_RECORD hrar on  hrar.MedicalRecordID = hr.MedicalRecordID
				join [AKUZ].T_EVENT_BASE hrae on  hrae.EventID = hrar.AmbulanceEvent
				join [AKUZ].T_AMBULANCE_CARD hrac on  hrac.AmbulanceCardID = hrae.AmbulanceCard
				join [AKUZ].T_PATIENT hrp on  hrp.PatientID = hrac.Patient

				where hr.ReferralNumber = db.DirectionNumber
				and hrr.Lpu = db.Lpu
				and hrp.PatientID = pat.PatientID
				and hrr.ReferralDate <= eb.EventDate
				and DATEADD(M,2,hrr.ReferralDate)>= eb.EventDate
			) tt
		where mov.MovementID = @ID

		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),>
		   ,'SendRouteRqst'--,<Type, varchar(255),>
		   ,null--,<UserID, varchar(255),>
		   ,0--,<State, int,>)
		   ,GETDATE()
		   ,@mainID	

		   
		drop table #lpu_main
END

GO