IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_BurDirectionRIACmantu]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_BurDirectionRIACmantu]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-07-14
-- Description: Передать информацию о услуге пробы Манту  в буряцкий РИАЦ
-- =============================================
CREATE PROCEDURE [dbo].[sp_BurDirectionRIACmantu] 
        @MedicalRecordID  uniqueidentifier	
	   ,@IDPatient  uniqueidentifier	
	   ,@QueueName varchar(100) = 'Bur.RIACSync' 
	   ,@User uniqueidentifier = null	
AS
BEGIN
	SET NOCOUNT ON;

  
declare  
		 @Result varchar(max)
		,@Type varchar(255)
		,@OwnerID uniqueidentifier
		,@dateTime datetime = null
		,@ContentEncoding varchar(255)
		
		,@TYPE_WEBSER varchar(16) 
		,@TYPE_OPER varchar(16) 
		,@TYPE_OPER_DB varchar(16)
		,@I int 
		,@Region  varchar(32)
		,@LpuMain uniqueidentifier
		,@EventID uniqueidentifier
		,@PostingDate  varchar(10)  
		,@EventDate   varchar(10) 
		,@oid varchar(128)
		,@AttachDate varchar(10)
		,@Snils varchar(32)
		,@ResultT varchar(16)  

		,@StatusRegCansel int = 2 

		,@IDAK  uniqueidentifier = null
		,@oid_Pat varchar(128)
		,@Number varchar(64) -- полис
		,@Serial varchar(16)
		,@TypePolis int 
		,@FirstName varchar(64) 
		,@FatherName varchar(64) 
		,@LastName varchar(64) 
		,@Snils_Pat varchar(14) 
		,@Sex int
		,@BirthDate varchar(10) 

		,@DeathDate varchar(10)
		,@Group varchar(16)
		,@dateStart varchar(10)
		,@dateStartWithoutReg  varchar(10)


		select @dateTime = GETDATE()
			,@TYPE_WEBSER = 'tuberws'
			,@TYPE_OPER = 'AddMantouxTest'   
			,@Type = 'mantouxTest'
			,@ContentEncoding = 'utf-8'		


		--  собираем данные  о услуге пробы Манту
		select 
			@EventID = mnr.MedicalRecordID-- Идентификатор случая в МИС
			,@PostingDate =  convert(varchar(10),mr1.RecordDate, 120)	-- Дата открытия случая
			,@EventDate  = convert(varchar(10),mnr.ResultDate, 120) -- Дата оказания услуги

			,@oid =  isnull(erm.oid,'')	 --	 Идентификатор МУ. ЛПУ проводившее исследование -- idOrg	

			,@Snils  = IsNull(t13.Snils,'')
			,@ResultT = case 
				when mnr.Result  = 0 then '1'   -- Отрицательная реакция
				when mnr.Result  = 1 then '2'   -- Сомнительная реакция	
				when mnr.Result  = 2 then '3'   -- Положительный	
				when mnr.Result  = 3 then '3'   -- Гиперергический									
				else '1'
			end    -- Код справочника «Характер реакции на пробу Манту». Приложение 6  ТЗ
		 ,@Group = ctg.Code
 
		FROM AKUZ.T_MANTU_RESULT mnr 
			LEFT JOIN AKUZ.T_MEDICAL_RECORD mr1 ON mr1.MedicalRecordID = mnr.MedicalRecordID
			LEFT JOIN AKUZ.T_MANTU_ACCEPT ma ON ma.MedicalRecordID = mnr.MantuAccept
			LEFT JOIN AKUZ.T_VACCINATION_ACCEPT va ON va.MedicalRecordID = mnr.MantuAccept
			LEFT JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = ma.MedicalRecordID
			JOIN AKUZ.T_PRG_CONTINGENT ctg ON ctg.PrgContingentID = va.PrgContingent

			LEFT JOIN AKUZ.T_AMBULANCE_CARD ac ON ac.AmbulanceCardID = acr.AmbulanceCard
			JOIN AKUZ.T_LPU_MAIN lm ON lm.LpuID = ac.CardLpu   
			JOIN EHR.T_EHR_RUS er ON er.EhrRusID = lm.EhrRusMdr308
			JOIN EHR.T_EHR_RUS_MDR308 erm ON erm.EhrRusID = er.EhrRusID 

			LEFT JOIN AKUZ.T_POST t10 ON t10.ResourceID = mr1.Post
			LEFT JOIN AKUZ.T_RESOURCE t11 ON t11.ResourceID = t10.ResourceID
			LEFT JOIN AKUZ.T_WORKER t12 ON t12.WorkerID = t10.Worker
			LEFT JOIN AKUZ.T_SNILS t13 ON t13.Snils = t12.Snils
			WHERE ma.MedicalRecordID=@MedicalRecordID
			

		-- Для случая, когда пациент нигде не зарегистрирован, но диагностику проходит
	    select top 1 @dateStartWithoutReg = convert(varchar(10),ak3.CreationDateTime, 120) from AKUZ.T_AMBULANCE_CARD ak3 where ak3.Patient = @IDPatient  order by ak3.CreationDateTime desc

		--  собираем данные  о  пациенте (чтобы его добавить/изменить, если он отсутствует в базе РИАС)
		SELECT 	top 1
			@IDAK = ac.AmbulanceCardID, 
			@LastName = IsNull(p.LastName,''),
			@FirstName = IsNull(p.FirstName,''), 
			@FatherName = IsNull(p.FatherName, ''), 
			@Snils_Pat = IsNull(REPLACE(REPLACE(p.SNILS,'-',''),' ',''),''),
			@BirthDate = convert(varchar(10), p.Birthday, 120),
			@Sex = e.Code,
			@Number = IsNull(pcy.Number, ''),
			@Serial = IsNull(pcy.Serial, ''),
 
			@TypePolis = case		-- перекодировка типа полиса для Бурятии							 
					when pcy3.PolicyForm = 1 then 2
					when pcy3.PolicyForm in (2, 3) then 1
					when pcy3.PolicyForm = 4 then 3
					else null
			end ,			
			@DeathDate =  convert(varchar(10),di.DeathDate, 120),   --     дата смерти   
			@oid_Pat =  isnull(erm.oid,''),	  
			@Group = isnull(ctg.Code,''), 					--	код контенгента
			@dateStart =  isnull(convert(varchar(10), r.RegistrationDate, 120), @dateStartWithoutReg),	--  будем считать за дату включения в контингент
			@AttachDate = convert(varchar(10),r.RegistrationDate, 120)				--	0..1	Дата прикрепления пациента к 

			from AKUZ.T_PATIENT p
 			LEFT JOIN AKUZ.T_SOCIAL_STATUS ss on ss.SocialStatusID = p.SocialStatus 
			LEFT JOIN AKUZ.T_SNILS s ON s.Snils = p.SNILS
			LEFT JOIN VCLib.T_ENUMS e ON e.EnumID = p.Sex
			LEFT JOIN AKUZ.T_POLICY pcy ON pcy.PolicyID = 
				(  
					(SELECT TOP 1 case when pcy2.ValidThrough IS NULL OR IsNull(@dateTime,@dateTime)<=pcy2.ValidThrough then pcy2.PolicyID else  NULL end
					FROM AKUZ.T_POLICY pcy2 
					LEFT JOIN AKUZ.T_PATIENT t21 ON t21.PatientID = pcy2.Patient
					WHERE (pcy2.type_ IN (536) AND t21.PatientID=p.PatientID AND IsNull(@dateTime,@dateTime)>=pcy2.IssueDate)
					ORDER BY pcy2.IssueDate DESC,IsNull(pcy2.ValidThrough,dateadd(Year,100,pcy2.IssueDate)) DESC
					)
				)
			LEFT JOIN AKUZ.T_POLICY pcy3  on pcy3.PolicyID = [dbo].fn_GetOmsPolicy(p.PatientID, @dateTime)
			left join AKUZ.T_REGISTRATION r ON p.PatientID = r.Patient   AND r.Status  = 1 
																	AND r.RegistrationDate<=@dateTime
																	AND r.AttachmentStatus > 0 
																	AND r.Profile  in (1,2)
			left JOIN AKUZ.T_LPU_MAIN lm ON lm.LpuID = r.LpuMain
			LEFT JOIN EHR.T_EHR_RUS er ON er.EhrRusID = lm.EhrRusMdr308
			LEFT JOIN EHR.T_EHR_RUS_MDR308 erm ON erm.EhrRusID = er.EhrRusID
			left JOIN AKUZ.T_AMBULANCE_CARD ac  ON ac.CardLpu = r.LpuMain and p.PatientID = ac.Patient
			LEFT JOIN AKUZ.T_PRG_CONTINGENT ctg on ctg.PrgContingentID = p.PrgContingent
			LEFT JOIN AKUZ.T_DEATH_INFO	di on di.AmbulanceCard = ac.AmbulanceCardID
			where p.PatientID = @IDPatient
			ORDER BY r.RegistrationDate DESC			
		Select  @Result =  		 
		( 
		    select  
				@TYPE_OPER			as 'TYPE_OPER'					-- операция 
				,@TYPE_WEBSER		as 'TYPE_WEBSER'				-- тип веб-сервиса (diabetws или  tuberws)
				,@TYPE_OPER_DB		as 'TYPE_OPER_DB'				-- операцмя  БД (Add  Update  Delete)
				,@MedicalRecordID	as 'IDOwner'
				,@MedicalRecordID	as 'IDOwnerForm'
				,@IDPatient         as 'MantouxTest/idPatientMIS'	-- Идентификатор пациента в МИС 
				,@EventID			as 'MantouxTest/idCaseMIS'		-- Идентификатор случая в МИС 
				,@PostingDate       as 'MantouxTest/dateOpen'		-- Дата открытия случая
				,@EventDate			as 'MantouxTest/dateStart'		-- Дата оказания услуги
				,@oid				as 'MantouxTest/idOrg'			-- Идентификатор МУ. ЛПУ проводившее исследование
				,@Snils				as 'MantouxTest/idDoctorMIS'	-- Идентификатор врача. В качестве идентификатора указывается СНИЛС в формате: ЧЧЧ-ЧЧЧ-ЧЧЧ ЧЧ
				,@ResultT			as 'MantouxTest/reactionNature'	-- Код справочника «Характер реакции на пробу Манту». Приложение 6


				--  Данные о пациенте
				,@Number			as  'Patient/InsurancePolicy/Number'
				,@Serial			as  'Patient/InsurancePolicy/Serial'	
				,@TypePolis 		as  'Patient/InsurancePolicy/Type'	
				,@FirstName			as  'Patient/Firstname'		
				,@FatherName		as  'Patient/Middlename'    
				,@LastName			as  'Patient/Familyname'
				,@Snils_Pat			as  'Patient/SNILS'	
				,@Sex				as  'Patient/Sex'	
				,@BirthDate			as  'Patient/BirthDate' 
				,@oid_pat			as  'Patient/idOrg'
				,@AttachDate		as  'Patient/attachDate'
				,@DeathDate			as  'Patient/deathDate'
				,@IDPatient			as  'Patient/IdPatientMIS'
				,@Group				as  'Patient/TuberGroups/Group'
				,@DateStart			as  'Patient/TuberGroups/DateStart'					

			for xml path(''),
			root('RIAC')
		)
	   
        -- Отправка
        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),>
              ,@ContentEncoding--,<ContentEncoding, varchar(255),>
              ,@Type--,<Type, varchar(255),>
              ,@User--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,@dateTime--,<CreateDateTime, datetime,>)
              ,@OwnerID--,<OwnerID, varchar(255),>

END
