
/****** Object:  StoredProcedure [dbo].[sp_TFOMS_ReferralXml]    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_ReferralXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_TFOMS_ReferralXml]
GO
-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2020-09-28
-- Description:	Выгрузка информации направлении на госпитализацию в ТФОМС
-- =============================================
CREATE PROCEDURE [dbo].[sp_TFOMS_ReferralXml]	
	@ID uniqueidentifier = null,
	@QueueName varchar(255) = null,
	@Sender varchar(255) = null
AS
BEGIN

declare @Result varchar(max) 
	   ,@refNumber varchar(225)
	SET NOCOUNT ON;
	 
if(not exists(select * from [AKUZ].T_CANCEL_HOSPITALIZATION_REFERRAL chr where chr.HospitalizationReferral = @ID))
begin
	select @refNumber = Concat(lm.LpuCode,YEAR(GETDATE()),case 
		when hr.ReferralNumber<10 then concat('0000',hr.ReferralNumber)
		when hr.ReferralNumber<100 then concat('000',hr.ReferralNumber)
		when hr.ReferralNumber<1000 then concat('00',hr.ReferralNumber)
		when hr.ReferralNumber<10000 then concat('0',hr.ReferralNumber)
		else hr.ReferralNumber
		end)
		from akuz.T_HOSPITALIZATION_REFERRAL hr 
			join [AKUZ].T_MEDICAL_RECORD mr on mr.MedicalRecordID = hr.MedicalRecordID
			join [AKUZ].T_POST p on p.ResourceID = mr.Post
			join [AKUZ].T_RESOURCE r on r.ResourceID = p.ResourceID
			join [AKUZ].T_DIVISION d on d.DivisionID = r.Division
			left join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
			left join [AKUZ].T_LPU_MAIN lm on br.LpuMain = lm.LpuID
		where hr.MedicalRecordID =@ID

	--_________сформировать_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
				,lmT.LpuCode                                              		as 'ser:DATA/ser:CODE_MO'			  --Принимающая направление МО
				,@ID				                                 		    as 'ser:DATA/ser:REC/ser:GUID'			  --Уникальный код записи 
				,lm.LpuCode											   		    as 'ser:DATA/ser:REC/ser:NPR_MO'		 --Реестровый номер МО, направившей пациента на госпитализацию
				,CASE 
					WHEN br.TFomsCode  like '%.1' THEN null 
					ELSE br.TFomsCode 
				 END		 										   		    as 'ser:DATA/ser:REC/ser:NPR_LPU'		 --Региональный код подразделения МО, направившего пациента на госпитализацию
				,lmT.LpuCode										   		    as 'ser:DATA/ser:REC/ser:DEST_MO'		 --Реестровый номер МО, в которую направлен/госпитализирован пациент
				,CASE 
					WHEN brT.TFomsCode  like '%.1' THEN null 
					ELSE brT.TFomsCode 
				 END												   		    as 'ser:DATA/ser:REC/ser:DEST_LPU'		 --Региональный код подразделения МО, в которое направлен/госпитализирован пациент
				,CASE hr.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'	 --Номер ДПФС
				,hr.Phone 														as 'ser:DATA/ser:REC/ser:PACIENT/ser:CONTACT'	 --Номер ДПФС
		
				,convert(varchar(10), ref.ReferralDate, 20) 					as 'ser:DATA/ser:REC/ser:NPR/ser:NPR_DATE'	 --Номер ДПФС
				,@refNumber														as 'ser:DATA/ser:REC/ser:NPR/ser:NPR_NUM'	 --Номер ДПФС
				,ref.MkbCode													as 'ser:DATA/ser:REC/ser:NPR/ser:DS'
				,CASE dT.DivisionType
					WHEN 12808 THEN 2
					WHEN 12809 THEN 2
					WHEN 12810 THEN 2
					ELSE 1
				 END															as 'ser:DATA/ser:REC/ser:NPR/ser:USL_OK'     --Условия оказания МП V006 
				,sb.CodeMz														as 'ser:DATA/ser:REC/ser:NPR/ser:PROFK'
				,isnull( dT.CodeMz,  dT.Code)														as 'ser:DATA/ser:REC/ser:NPR/ser:PODR'
				,w.Snils														as 'ser:DATA/ser:REC/ser:NPR/ser:CODE_MD'  --СНИЛС медработника, направившего больного
				,convert(varchar(10), hr.HospitalisationDate, 20)				as 'ser:DATA/ser:REC/ser:NPR/ser:DATE_P'

				from [AKUZ].T_HOSPITALIZATION_REFERRAL hr 
				left join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = hr.StructureBed
				left join [AKUZ].T_V002_PROFILE v0 on v0.Id = sb.V002Profile
				left join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hr.MedicalRecordID
				--to
				left join [AKUZ].T_BRANCH brT on brT.LpuID = hr.Branch
				left join [AKUZ].T_LPU_MAIN lmT on brT.LpuMain = lmT.LpuID
				left join [AKUZ].T_DIVISION dT on dT.DivisionID = ref.Division
				--from
				join [AKUZ].T_MEDICAL_RECORD mr on mr.MedicalRecordID = ref.MedicalRecordID
				join [AKUZ].T_POST p on p.ResourceID = mr.Post
				join [AKUZ].T_RESOURCE r on r.ResourceID = p.ResourceID
				join [AKUZ].T_WORKER w on p.Worker = w.WorkerID
				join [AKUZ].T_DIVISION d on d.DivisionID = r.Division
				left join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
				left join [AKUZ].T_LPU_MAIN lm on br.LpuMain = lm.LpuID
				--patient
				join [AKUZ].T_AMBULANCE_CARD_RECORD ar on ar.MedicalRecordID = hr.MedicalRecordID
				join [AKUZ].T_EVENT_BASE eb on eb.EventID = ar.AmbulanceEvent
				join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
				join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
				--polis
				join [AKUZ].T_POLICY pol on pol.PolicyID = [dbo].[fn_GetOmsPolicy](pat.PatientID, ref.ReferralDate)
				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
			
				where hr.MedicalRecordID = @ID

				FOR XML PATH(''),
				ROOT('ser:SendRouteRqst')
			)
		 

	--_____положить в раббит________________________________________________________________________________________________________________________________--
		IF @Result is not null
		BEGIN
			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()
				,@ID
			end
		END

END
GO


