TVF_StaffDetailsFromWindowsLogin

[robinwilson16]

Used to determine the role of a member of staff depending on if they are a director of faculty, a head of an area, a lecturer or non-teaching.
Used in some C# web applications that customise the view depending on the role of the logged on user using Windows Auth

Code: Select all

CREATE FUNCTION [dbo].[TVF_StaffDetailsFromWindowsLogin] (@Session Varchar(10), @WindowsUsername Varchar(50))
RETURNS TABLE
AS
RETURN

SELECT
	StaffID = U.user_staff,
	Username = U.user_ref,
	WindowsUsername = U.user_dbuser,
	Surname = P.p_surname,
	Forename = P.p_forenames,
	PhotoPath = RIGHT ( HL.h_command, CHARINDEX ( '\', REVERSE ( HL.h_command ) ) - 1 ),
	StaffRole = 
		CASE
			WHEN DIR.PersonID IS NOT NULL THEN 'Director of Faculty'
			WHEN TEAM.PersonID IS NOT NULL THEN 'Head of Learning and Standards'
			WHEN T.PersonID IS NOT NULL THEN 'Lecturer'
			ELSE 'College Staff'
		END
FROM ulive.dbo.caps_users U
INNER JOIN ulive.dbo.capd_staff STF
	ON STF.s_staffreference = U.user_staff
INNER JOIN ulive.dbo.capd_person P
	ON P.p_id = STF.s_id
LEFT JOIN ulive.dbo.caps_hotlinks HL
	ON HL.h_object_id = P.p_id
	AND HL.h_type = 'photograph'
LEFT JOIN (
	SELECT DISTINCT
		PersonID = DEPT.d_supervisor
	FROM ulive.dbo.capd_department DEPT	
) DIR ON DIR.PersonID = P.p_id
LEFT JOIN (
	SELECT DISTINCT
		PersonID = TEAM.s_supervisor
	FROM ulive.dbo.capd_section TEAM	
) TEAM ON TEAM.PersonID = P.p_id
LEFT JOIN (
	SELECT DISTINCT
		SA.sa_activitystaff PersonID
	FROM ulive.dbo.capd_staffactivity SA
	INNER JOIN ulive.dbo.capd_activity A
		ON A.a_id = SA.sa_activity
	INNER JOIN ulive.dbo.capd_register R
		ON R.r_id = A.a_register
	WHERE
		A.a_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
		AND A.a_end >= LEFT ( @Session, 4 ) + '-08-01'​​
) T ON T.PersonID = P.p_id
WHERE
	U.user_dbuser = @WindowsUsername