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_MDR308 erm ON erm.EhrRusID = lm.EhrRusMdr308
--		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_MDR308 erm ON erm.EhrRusID = lm.EhrRusMdr308
--		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
