
/****** Object:  StoredProcedure [dbo].[sp_TFOMS_CancelReferralXml]    Script Date: 10/5/2020 4:13:04 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_CancelReferralXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_TFOMS_CancelReferralXml]
GO
-- =============================================
-- Author:		Vitacore ()
-- Create date: 2020-02-03
-- Description:	Выгрузка информации об отмене направления на госпитализацию в ТФОМС
-- =============================================
CREATE PROCEDURE [dbo].[sp_TFOMS_CancelReferralXml]
	@ID uniqueidentifier = null,
	@QueueName varchar(255) = null,
	@Sender varchar(255) = null
AS
BEGIN	
	declare @Result varchar(max)
		   ,@refNumber varchar(225)
		   ,@mainID uniqueidentifier
	SET NOCOUNT ON;
	select @refNumber =  lm.LpuCode + '-' +Cast(YEAR(GETDATE()) as varchar(4))+ '-' +right('0000'+ hr.ReferralNumber, 5)
	from  [AKUZ].T_CANCEL_HOSPITALIZATION_REFERRAL chr
		join akuz.T_HOSPITALIZATION_REFERRAL hr on chr.HospitalizationReferral = hr.MedicalRecordID
		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 chr.CancelHospitalizationReferralID = @ID

	select @mainID = hr.MedicalRecordID
	from  [AKUZ].T_CANCEL_HOSPITALIZATION_REFERRAL chr
		join akuz.T_HOSPITALIZATION_REFERRAL hr on chr.HospitalizationReferral = hr.MedicalRecordID
	where chr.CancelHospitalizationReferralID = @ID

	if( @refNumber is not null)
	begin
--__________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'	

			,lmT.LpuCode                                              		as 'ser:DATA/ser:CODE_MO'		
			,hr.MedicalRecordID					                           	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(i1.OKATO, i2.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.PreHospitalDate, 20)					as 'ser:DATA/ser:REC/ser:NPR/ser:DATE_P'
				
			,chr.[CancellationSource] + 1  									as 'ser:DATA/ser:REC/ser:ANN/ser:SRC'			 -- Источник аннулирования				
			,lpu_main.LpuCode									 			as 'ser:DATA/ser:REC/ser:ANN/ser:ORG'			 -- Реестровый номер источника аннулирования 		
			,CASE 
				WHEN branch.TFomsCode  like '%.1' THEN null 
				ELSE branch.TFomsCode 
			 END															as 'ser:DATA/ser:REC/ser:ANN/ser:LPU_1'			 --	Региональный код подразделения МО
			,chr.[ReasonCancel]+1											as 'ser:DATA/ser:REC/ser:ANN/ser:REASON'		 --	Причина аннулирования	   
			,convert(varchar(10), chr.DateCancel, 20)						as 'ser:DATA/ser:REC/ser:ANN/ser:D_ANN'			 --	Дата аннулирования
			--,replace(convert(varchar(10), chr.DateCancel, 20),' ','T')		as 'ser:DATA/ser:REC/ser:ANN/ser:D_ANN'			 --	Дата аннулирования
			,CASE chr.[CancellationSource]
				WHEN  4 THEN chr.Descr 
				ELSE null 
			END																as 'ser:DATA/ser:REC/ser:ANN/ser:COMMENTA'		 --	Комментарий к причине аннулирования если [CancellationSource] - Прочие


			from [AKUZ].T_CANCEL_HOSPITALIZATION_REFERRAL chr
			--from
			left join [AKUZ].T_HOSPITALIZATION_REFERRAL hr on hr.MedicalRecordID = chr.HospitalizationReferral
			left join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = hr.StructureBed
			left join [AKUZ].T_MEDICAL_RECORD mr on mr.MedicalRecordID = hr.MedicalRecordID
			join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hr.MedicalRecordID
			left join [AKUZ].T_POST p on p.ResourceID = mr.Post
			left join [AKUZ].T_RESOURCE r on r.ResourceID = p.ResourceID
			left join [AKUZ].T_WORKER w on p.Worker = w.WorkerID
			left 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		
			--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
			--patient
			join [AKUZ].T_EVENT_BASE eb on eb.EventID = mr.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
			
			join [AKUZ].T_USERS u on u.UserID = chr.Users
			join [AKUZ].T_BRANCH branch on branch.LpuID = u.Branch
			join [AKUZ].T_LPU_MAIN lpu_main on branch.LpuMain = lpu_main.LpuID
			where chr.CancelHospitalizationReferralID = @ID
			FOR XML PATH(''),
			ROOT('ser:SendRouteRqst')
		) 
	end
	else
	begin

	select @refNumber =  lm.LpuCode + Cast(YEAR(GETDATE()) as varchar(4))+right('0000'+ rhr.ReferralNumber, 5)
	from  [AKUZ].T_CANCEL_HOSPITALIZATION_REFERRAL chr
		join akuz.T_RDS_HOSPITALIZATION_REFERRAL rhr on chr.RdsHospitalizationReferral = rhr.RdsHospitalizationReferralID
		left join [AKUZ].T_BRANCH br on br.LpuID =  rhr.BranchFrom
		left join [AKUZ].T_LPU_MAIN lm on br.LpuMain = lm.LpuID
	where chr.CancelHospitalizationReferralID = @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'	

			,lmTo.LpuCode                                              		as 'ser:DATA/ser:CODE_MO'		
			,tt.MedicalRecordID					                           	as 'ser:DATA/ser:REC/ser:GUID'			 
			,lmFrom.LpuCode											   		    as 'ser:DATA/ser:REC/ser:NPR_MO'		
			,CASE 
				WHEN brFrom.TFomsCode  like '%.1' THEN null 
				ELSE brFrom.TFomsCode 
			 END															as 'ser:DATA/ser:REC/ser:NPR_LPU'		 
			,lmTo.LpuCode											   		as 'ser:DATA/ser:REC/ser:DEST_MO'		 
			,CASE 
				WHEN brTo.TFomsCode  like '%.1' THEN null 
				ELSE brTo.TFomsCode 
			 END												   		    as 'ser:DATA/ser:REC/ser:DEST_LPU'		
			,CASE rhr.HospitalizationType
				WHEN 13001 THEN 3
				WHEN 13002 THEN 1
				WHEN 13003 THEN 2
			 END															as 'ser:DATA/ser:REC/ser:FOR_POM'	         --Форма оказания медицинской помощи F003
			,rhr.LastName													as 'ser:DATA/ser:REC/ser:PACIENT/ser:FAM'	 --Фамилия пациента
			,rhr.FirstName													as 'ser:DATA/ser:REC/ser:PACIENT/ser:IM'	 --Имя пациента
			,rhr.FatherName													as 'ser:DATA/ser:REC/ser:PACIENT/ser:OT'	 --Отчество пациента
			,(case when rhr.Sex = 10301 then 1 else 2 end)					as 'ser:DATA/ser:REC/ser:PACIENT/ser:W'	     --Пол
			,convert(varchar(10), rhr.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 rhr.PolicyForm
					WHEN 4 THEN 2
					WHEN 1 THEN 1
					else 3
				END															as 'ser:DATA/ser:REC/ser:PACIENT/ser:VPOLIS'	 --Тип ДПФС
			,rhr.PolisSerial   												as 'ser:DATA/ser:REC/ser:PACIENT/ser:SPOLIS'	 --Серия ДПФС
			,rhr.PolisNumber    							    			as 'ser:DATA/ser:REC/ser:PACIENT/ser:NPOLIS'	 --Номер ДПФС
			,i1.SmoCod 			    										as 'ser:DATA/ser:REC/ser:PACIENT/ser:SMO'		 --Реестровый номер СМО
			--,''															as 'ser:DATA/ser:REC/ser:PACIENT/ser:SMO_NAM'	 --Наименование СМО - не заполнять если номер есть
			,substring (ISNULL(i1.OKATO,''),1,5)							as 'ser:DATA/ser:REC/ser:PACIENT/ser:ST_OKATO'	 --Регион страхования
			--,''															as 'ser:DATA/ser:REC/ser:PACIENT/ser:NOVOR_NUM'	 --Номер ДПФС
			,rhr.Phone 														as 'ser:DATA/ser:REC/ser:PACIENT/ser:CONTACT'	 --Контактный номер
		
			,convert(varchar(10), rhr.ReferralDate, 20) 					as 'ser:DATA/ser:REC/ser:NPR/ser:NPR_DATE'	 --Номер ДПФС
			,@refNumber														as 'ser:DATA/ser:REC/ser:NPR/ser:NPR_NUM'	 --Номер ДПФС
			,rhr.MkbCode													as 'ser:DATA/ser:REC/ser:NPR/ser:DS'
			,CASE d.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( d.CodeMz,  d.Code)										as 'ser:DATA/ser:REC/ser:NPR/ser:PODR'
			,tt.Snils														as 'ser:DATA/ser:REC/ser:NPR/ser:CODE_MD'  --СНИЛС медработника, направившего больного
			,convert(varchar(10), rhr.PlannedDate, 20)						as 'ser:DATA/ser:REC/ser:NPR/ser:DATE_P'
				
			,chr.[CancellationSource] + 1  									as 'ser:DATA/ser:REC/ser:ANN/ser:SRC'			 -- Источник аннулирования				
			,lpuCancel.LpuCode									 			as 'ser:DATA/ser:REC/ser:ANN/ser:ORG'			 -- Реестровый номер источника аннулирования 		
			,CASE 
				WHEN branchCancel.TFomsCode  like '%.1' THEN null 
				ELSE branchCancel.TFomsCode 
			 END															as 'ser:DATA/ser:REC/ser:ANN/ser:LPU_1'			 --	Региональный код подразделения МО
			,CASE chr.[ReasonCancel] 
				WHEN 7 THEN 6
				ELSE chr.[ReasonCancel]+1
			 END															as 'ser:DATA/ser:REC/ser:ANN/ser:REASON'		 --	Причина аннулирования	   
			,convert(varchar(10), chr.DateCancel, 20)						as 'ser:DATA/ser:REC/ser:ANN/ser:D_ANN'			 --	Дата аннулирования
			,CASE chr.[CancellationSource]
				WHEN  4 THEN chr.Descr 
				ELSE null 
			END																as 'ser:DATA/ser:REC/ser:ANN/ser:COMMENTA'		 --	Комментарий к причине аннулирования если [CancellationSource] - Прочие


			from [AKUZ].T_CANCEL_HOSPITALIZATION_REFERRAL chr
			--from
			join akuz.T_RDS_HOSPITALIZATION_REFERRAL rhr on chr.RdsHospitalizationReferral = rhr.RdsHospitalizationReferralID
			left join [AKUZ].T_BRANCH brFrom on brFrom.LpuID =  rhr.BranchFrom
			left join [AKUZ].T_LPU_MAIN lmFrom on brFrom.LpuMain = lmFrom.LpuID
			--to
			left join [AKUZ].T_DIVISION d on d.DivisionID = rhr.Division
			left join [AKUZ].T_BRANCH brTo on brTo.LpuID = d.Branch
			left join [AKUZ].T_LPU_MAIN lmTo on brTo.LpuMain = lmTo.LpuID		
			left join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = rhr.StructureBed

			left join [AKUZ].T_INSURER i1 on i1.InsurerID = rhr.Insurer
			left join [AKUZ].V_INSURER_REGION ir1 on ir1.InsurerRegionID = i1.OKATO		
			left join [AKUZ].T_REGION r1 on r1.RegionID = ir1.Region
			
			join [AKUZ].T_USERS u on u.UserID = chr.Users
			join [AKUZ].T_BRANCH branchCancel on branchCancel.LpuID = u.Branch
			join [AKUZ].T_LPU_MAIN lpuCancel on branchCancel.LpuMain = lpuCancel.LpuID
			outer apply
				(
					select hr.MedicalRecordID, hr.Phone,hrr.ReferralDate,hrr.MkbCode, dT.DivisionType, hrsb.CodeMz as sbCodeMz,hr.PreHospitalDate,dT.CodeMz as divCodeMz,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 =  rhr.ReferralNumber 
					and hrr.Lpu = rhr.LpuMainFrom
					and hrp.FirstName = rhr.FirstName
					and hrp.LastName = rhr.LastName
					and hrp.FatherName = rhr.FatherName
					and hrp.Birthday = rhr.Birthday
					and hrr.ReferralDate = rhr.ReferralDate
				) tt
				 
			where chr.CancelHospitalizationReferralID = @ID
			FOR XML PATH(''),
			ROOT('ser:SendRouteRqst')
		) 

		
	select @mainID = tt.MedicalRecordID
	from [AKUZ].T_CANCEL_HOSPITALIZATION_REFERRAL chr
			--from
			join akuz.T_RDS_HOSPITALIZATION_REFERRAL rhr on chr.RdsHospitalizationReferral = rhr.RdsHospitalizationReferralID
			left join [AKUZ].T_BRANCH brFrom on brFrom.LpuID =  rhr.BranchFrom
			left join [AKUZ].T_LPU_MAIN lmFrom on brFrom.LpuMain = lmFrom.LpuID
			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 =  rhr.ReferralNumber 
					and hrr.Lpu = rhr.LpuMainFrom
					and hrp.FirstName = rhr.FirstName
					and hrp.LastName = rhr.LastName
					and hrp.FatherName = rhr.FatherName
					and hrp.Birthday = rhr.Birthday
					and hrr.ReferralDate = rhr.ReferralDate
				) tt
	where chr.CancelHospitalizationReferralID = @ID
	end


		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
	END
GO


