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

-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-04-20
-- Description: Бурятия. Первоначальная отправка  информации  по пациентам  в РИАС  для профилактики туберкулеза
-- =============================================
CREATE PROCEDURE [dbo].[sp_BurDirectionRIACInitialTbPat]	
    @LpuMainID uniqueidentifier = null
	,@QueueName varchar(100) = 'Bur.RIACSync'	  -- RoutigKey
AS	
BEGIN
	
	SET NOCOUNT ON;

declare	 @pLpuMain [uniqueidentifier] =  @LpuMainID,    
		 @StatusRegCansel [int] = 2, 
		 @dateTime [datetime] = GETDATE()

declare  @Result varchar(max)
		,@CURSOR CURSOR
		,@ContentEncoding varchar(255)
		,@Type varchar(255)
		,@TYPE_WEBSER varchar(16)
		,@User uniqueidentifier = NULL
		,@OwnerID uniqueidentifier
		,@TYPE_OPER varchar(16) = 'AddPatient'   
		,@IDPBDD uniqueidentifier = null	-- При первоначальной заливке туб.пациентов	
		,@IDAK  uniqueidentifier = null
		,@IDPatient uniqueidentifier = null	
		,@oid varchar(128)
		,@AttachDate varchar(10)
		,@Number varchar(64) -- полис
		,@Serial varchar(16)
		,@TypePolis int 
		,@FirstName varchar(64) 
		,@FatherName varchar(64) 
		,@LastName varchar(64) 
		,@Snils varchar(14) 
		,@Sex int
		,@BirthDate varchar(10) 
		,@LastFgDate  datetime 
		,@DeathDate varchar(10)
		,@Group varchar(16)
		,@dateStart varchar(10)

select  @ContentEncoding = 'utf-8'

select  @TYPE_WEBSER = 'tuberws'
		,@Type = 'BUR_DIRECTION_TUBER'
		,@QueueName  = 'Bur.RIACSync'
		
SET @CURSOR = CURSOR SCROLL
FOR 
	SELECT 	
 		ac.AmbulanceCardID,			-- @IDAK
		ac.Patient,					-- @IDPatient
		-- ac.CardLpu,
		IsNull(p.LastName, ''),		-- @LastName
		IsNull(p.FirstName, ''),	-- @FirstName
		IsNull(p.FatherName, ''),	-- @FatherName 
		IsNull(REPLACE(REPLACE(p.SNILS,'-',''),' ',''),'') SNILS ,	-- @Snils 
		convert(varchar(10),p.Birthday, 120),					-- @BirthDate 
		
		e.Code,						-- @Sex
		IsNull(pcy.Number,''),		-- @Number 
		IsNull(pcy.Serial,''),		-- @Serial
 
		case		-- перекодировка типа полиса для Бурятии							 
				when pcy3.PolicyForm = 1 then 2
				when pcy3.PolicyForm in (2, 3) then 1
				when pcy3.PolicyForm = 4 then 3
				else 0
		end TypePolis ,				-- @TypePolis				
		convert(varchar(10),di.DeathDate, 120),				-- @DeathDate    дата смерти   
		
		IsNull( erm.oid,''),         

		isnull(ctg.Code,''), 										-- @Group        код контенгента
		convert(varchar(10),ac.CreationDateTime, 120),				-- @DateStart   будем считать за дату включения в контингент (а группу контингента)
		convert(varchar(10),lastreg.RegistrationDate, 120)			-- @AttachDate	 0..1	Дата прикрепления пациента к МУ

		FROM  AKUZ.T_AMBULANCE_CARD ac
		LEFT JOIN AKUZ.T_PATIENT p ON p.PatientID = ac.Patient
		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_LPU_MAIN lm ON lm.LpuID = ac.CardLpu
		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_POLICY pcy3  on pcy3.PolicyID = [dbo].fn_GetOmsPolicy(ac.Patient, @dateTime)
		LEFT JOIN AKUZ.T_PRG_CONTINGENT ctg on ctg.PrgContingentID = p.PrgContingent
	    LEFT JOIN AKUZ.T_DEATH_INFO	di on di.AmbulanceCard = ac.AmbulanceCardID

			-- информация о прикреплении.  Текущее прикрепление
		JOIN (  Select * -- rg.Patient, rg.[Status], rg.RegistrationDate, rg.LpuMain ,st.StateNumber, dv.Code 
				From [AKUZ].T_REGISTRATION rg
				--left join [AKUZ].T_STATE st on st.StateID = rg.State 
				--left join [AKUZ].T_DIVISION dv on dv.DivisionID = st.Division
				where   
				     rg.LpuMain=@pLpuMain
				    and rg.Status = 1 
					and  rg.RegistrationID = (SELECT TOP 1 rg2.RegistrationID
													FROM AKUZ.T_REGISTRATION rg2
													LEFT JOIN AKUZ.T_REGISTRATION_CANCEL rgc ON rgc.Registration = rg2.RegistrationID
													WHERE  (
															rg2.Patient=rg.Patient 
															AND rg2.RegistrationDate <= @dateTime
															AND (rgc.RegistrationCancelID IS NULL OR NOT rgc.Status = 2) 
															AND rg2.AttachmentStatus > 0
															AND rg2.Profile in (1) 
															) -- Профиль прикрепления  Терапия
													ORDER BY rg2.RegistrationDate DESC
										      )
				) lastreg On  lastreg.Patient = ac.Patient 
		WHERE  ac.CardLpu=@pLpuMain
---------------------------------Profile in (1) 				
UNION
---------------------------------Profile in (2) 
	SELECT 	
 		ac.AmbulanceCardID,			-- @IDAK
		ac.Patient,					-- @IDPatient
		-- ac.CardLpu,
		IsNull(p.LastName, ''),		-- @LastName
		IsNull(p.FirstName, ''),	-- @FirstName
		IsNull(p.FatherName, ''),	-- @FatherName 
		IsNull(REPLACE(REPLACE(p.SNILS,'-',''),' ',''),'') SNILS ,	-- @Snils 
		convert(varchar(10),p.Birthday, 120), 					-- @BirthDate 
		
		e.Code,						-- @Sex
		IsNull(pcy.Number,''),		-- @Number 
		IsNull(pcy.Serial,''),		-- @Serial
 
		case		-- перекодировка типа полиса для Бурятии							 
				when pcy3.PolicyForm = 1 then 2
				when pcy3.PolicyForm in (2, 3) then 1
				when pcy3.PolicyForm = 4 then 3
				else 0
		end TypePolis ,				-- @TypePolis				
		convert(varchar(10),di.DeathDate, 120), 				-- @DeathDate    дата смерти   
		
		IsNull(erm.oid,''),    

		isnull(ctg.Code,''), 										-- @Group        код контенгента
		convert(varchar(10),ac.CreationDateTime, 120),				-- @DateStart   будем считать за дату включения в контингент (а группу контингента)
		convert(varchar(10),lastreg.RegistrationDate, 120)			-- @AttachDate	 0..1	Дата прикрепления пациента к МУ
		
		FROM  AKUZ.T_AMBULANCE_CARD ac
		LEFT JOIN AKUZ.T_PATIENT p ON p.PatientID = ac.Patient
		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_LPU_MAIN lm ON lm.LpuID = ac.CardLpu
		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_POLICY pcy3  on pcy3.PolicyID = [dbo].fn_GetOmsPolicy(ac.Patient, @dateTime)
		LEFT JOIN AKUZ.T_PRG_CONTINGENT ctg on ctg.PrgContingentID = p.PrgContingent
	    LEFT JOIN AKUZ.T_DEATH_INFO	di on di.AmbulanceCard = ac.AmbulanceCardID

			-- информация о прикреплении.  Текущее прикрепление
		JOIN (  Select * -- rg.Patient, rg.[Status], rg.RegistrationDate, rg.LpuMain ,st.StateNumber, dv.Code 
				From [AKUZ].T_REGISTRATION rg
				--left join [AKUZ].T_STATE st on st.StateID = rg.State 
				--left join [AKUZ].T_DIVISION dv on dv.DivisionID = st.Division
				where   
				     rg.LpuMain=@pLpuMain
				    and rg.Status = 1 
					and  rg.RegistrationID = (SELECT TOP 1 rg2.RegistrationID
													FROM AKUZ.T_REGISTRATION rg2
													LEFT JOIN AKUZ.T_REGISTRATION_CANCEL rgc ON rgc.Registration = rg2.RegistrationID
													WHERE  (
															rg2.Patient=rg.Patient 
															AND rg2.RegistrationDate <= @dateTime
															AND (rgc.RegistrationCancelID IS NULL OR NOT rgc.Status = 2) 
															AND rg2.AttachmentStatus > 0
															AND rg2.Profile in (2) 
															) -- Профиль прикрепления  Терапия
													ORDER BY rg2.RegistrationDate DESC
										      )
				) lastreg On  lastreg.Patient = ac.Patient 
		WHERE  ac.CardLpu=@pLpuMain
				
OPEN @CURSOR
FETCH NEXT FROM @CURSOR INTO 
     @IDAK,@IDPatient,	@LastName,	@FirstName,	@FatherName, @Snils, @BirthDate,  @Sex,  @Number, @Serial, @TypePolis,@DeathDate , @oid,  @Group, @DateStart,@AttachDate

WHILE @@FETCH_STATUS = 0
	BEGIN
		if not exists (select * from  [dbo].[T_IDPATIENT_SEND_RIAS] where PatientID = @IDPatient)
		BEGIN

		    Set @OwnerID = @IDAK
			select @Result = 
			(
				select 
						@TYPE_OPER			as  'TYPE_OPER'
						,@TYPE_WEBSER		as  'TYPE_WEBSER'				
						,@IDPBDD			as  'IDOwner'
						,@IDAK				as  'IDOwnerForm'
						,@oid				as  'idOrg'
						,@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				as  'Patient/SNILS'	
						,@Sex				as  'Patient/Sex'	
						,@BirthDate			as  'Patient/BirthDate' 
						,@oid				as  'Patient/idOrg'
						,@AttachDate		as  'Patient/attachDate'
						,@DeathDate			as  'Patient/deathDate'
						,@IDPatient			as  'Patient/IdPatientMIS'
						,@Group				as  'Patient/TuberGroups/Group'
						,@LastFgDate		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

		    FETCH NEXT FROM @CURSOR INTO  
				 @IDAK,@IDPatient,	@LastName,	@FirstName,	@FatherName, @Snils, @BirthDate,  @Sex,  @Number, @Serial, @TypePolis,@DeathDate , @oid,  @Group, @LastFgDate,	@AttachDate
	END
CLOSE @CURSOR
DEALLOCATE @CURSOR
END
