

ALTER PROCEDURE [sCommunication].[pRegisteredUsersGet]

		@rSourceTypeId smallint = 0,
		@rSourceId bigint = 0,
		@rOSTypeId tinyint = 0,
		@rUserSourceTypeId smallint = 0
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	--Add Distinct because in new FCM HTTP V1 API we Process through loop (because of that we get 2 msg if there is two duplicate toke)
	SELECT 		ISNULL(DeviceToken,'') AS DeviceToken
	FROM 
		sCommunication.tRegisteredUsers RU 
		INNER JOIN sStudent.tAcademic SA ON RU.UserSourceId = SA.StudentId AND RU.UserSourceTypeId=2003 
		INNER JOIN sSetup.tClassMaster CM ON CM.Id = SA.ClassId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id = SA.BatchId AND BM.IsActive=1		
		INNER JOIN sStudent.tStudentCommunication SC ON SC.StudentId = SA.StudentId AND ISNULL(SC.IsBlockAllAlert,0) = 0
		LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId = SA.StudentId -- For House
		LEFT JOIN sHostel.tHostelAdmission HA ON HA.SourceId = SA.StudentId AND HA.SourceTypeId=2003 AND BM.OrgGroupBatchId = HA.OrgGroupBatchId  -- For Hostel
	WHERE 
		(
		(SA.Id=@rSourceId) AND (@rSourceTypeId=2001)
		OR (SA.StudentId=@rSourceId) AND (@rSourceTypeId=2003)
		OR (SA.DivisionId=@rSourceId) AND (@rSourceTypeId=2005)
		OR (SA.ClassId=@rSourceId) AND (@rSourceTypeId=2016)
		OR (CM.DepartmentId=@rSourceId) AND (@rSourceTypeId=2017)
		OR (SA.OrgId=@rSourceId) AND (@rSourceTypeId=2021)	
		OR (HA.Hosteld=@rSourceId) AND (@rSourceTypeId=2014) -- For Hostel
		OR (SO.HouseId=@rSourceId) AND (@rSourceTypeId=2029) -- For House	
		) 
		AND RU.OSTypeId=@rOSTypeId AND RU.DeviceToken	IS NOT NULL AND RU.DeviceToken != '' 	
		AND ((RU.UserSourceTypeId=@rUserSourceTypeId) OR (@rUserSourceTypeId=0))
		AND CHARINDEX(':', DeviceToken) > 0 --(Add condition for remove old Device token for ios)
	UNION ALL		
		
	SELECT 			ISNULL(DeviceToken,'') AS DeviceToken
	FROM 
		sCommunication.tRegisteredUsers RU
		INNER JOIN sHrm.tEmployeeJobDetail EJD ON RU.UserSourceId = EJD.EmployeeId AND RU.UserSourceTypeId=95
	WHERE 
		(
		(EJD.EmployeeId=@rSourceId) AND (@rSourceTypeId=95)
		OR (EJD.OrganizationDepartmentId=@rSourceId) AND (@rSourceTypeId=2017)
		OR (EJD.OrgId=@rSourceId) AND (@rSourceTypeId=2021)	
		) AND RU.OSTypeId=@rOSTypeId AND RU.DeviceToken	IS NOT NULL AND RU.DeviceToken != '' 
		AND ((RU.UserSourceTypeId=@rUserSourceTypeId) OR (@rUserSourceTypeId=0))
		AND CHARINDEX(':', DeviceToken) > 0		
END
