TVF_StaffDetailsFromWindowsLogin

The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
User avatar
robinwilson16
Site Admin
Posts: 185
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile: https://www.linkedin.com/in/robinwilson16
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

TVF_StaffDetailsFromWindowsLogin

Post by robinwilson16 » Thu Mar 02, 2017 11:55 pm

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

Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Who is online

Users browsing this forum: No registered users and 1 guest