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