CREATE PROCEDURE [dbo].[usp_SearchResults_ByNINumber]
(
	@NINo varchar(8),
	@UserId int
)
AS


-- Employees
SELECT DISTINCT   empe.Surname + ', ' + empe.Forename AS [Name], 
                  ISNULL(empe.Known, '') As KnownAs,
                  empe.NhssNo As NhssNo,
                  empe.DOB As DoB

FROM         Main.dbo.t_Empmt empt 
INNER JOIN   Main.dbo.t_Empee empe ON empt.EmpeeId = empe.[Id] 
INNER JOIN	 Work.dbo.t_ESW esw ON esw.EmpmtId = empt.Id

WHERE     (SUBSTRING(empe.NINo,0,9) = @NINo) AND (dbo.UserHasCoveringRole(empt.Id,@UserId, 6) = 1 or
			dbo.UserHasCoveringRole(empt.Id,@UserId, 7) = 1)


BEGIN
	Declare @CompareDate datetime
	SET @CompareDate = GETDATE()
	 
	-- Related Employments
	SELECT DISTINCT   ou.[Name] AS Employer, 
					  emp.PayNo AS PayNo, 
					  esp.FullScale As StaffCategory,
					  emp.StartDate As Status,
					  ee.NhssNo As EmpNhssNo,
					  esw.EmperId, esw.DivId, esw.DirId, esw.DepId, esw.SubId, esp.Full, CONVERT(VARCHAR(10),emp.StartDate,103) As StartDate,emp.StartDate As 'td'

	FROM			Main.dbo.t_Empmt emp 
	INNER JOIN		Main.dbo.t_Empee ee ON emp.EmpeeId = ee.[Id] 
	INNER JOIN		Main.dbo.t_OrgUnit ou ON emp.EmperId = ou.[Id] 
	INNER JOIN		Work.dbo.t_ESW esw ON esw.EmpmtId = emp.Id
	INNER JOIN		Main.dbo.t_ESP esp ON emp.[Id] = esp.EmpmtId

	WHERE     (SUBSTRING(ee.NINo,0,9) = @NINo)  AND (dbo.UserHasCoveringRole(emp.Id,@UserId, 6) = 1 or
			dbo.UserHasCoveringRole(emp.Id,@UserId, 7) = 1)
	ORDER BY emp.StartDate ASC
END



Create FUNCTION [dbo].[UserHasCoveringRole]
(@EmpmtId int, @UserId int, @RId int)
RETURNS int
AS
BEGIN
	IF EXISTS(SELECT esw.ID
	FROM dbo.t_ESW esw, dbo.t_UR ur
	WHERE 
		ur.UId = @UId AND 
		ur.RId = @RId AND
		esw.EmpmtId = @EmpmtId AND
		(
			(esw.EmperId = ur.EmployerId AND ur.DivisionChpId IS NULL) OR
			(esw.DivId = ur.DivId AND ur.DirId IS NULL)OR
			(esw.DirId = ur.DirId AND ur.DepId IS NULL)OR
			(esw.DepId = ur.DepId AND ur.SubId IS NULL)OR
			esw.SubId = ur.SubId
		))
	BEGIN
		RETURN 1
	END
		RETURN 0 
END