/********************************************************************************/
/* PROCEDURE NAME: procSelectUsers											*/
/*                                                                              */
/* -> Description:                                                              */
/* This procedure will select all NON-DELETED users within tblUsers for display */
/********************************************************************************/
ALTER PROCEDURE [dbo].[procSelectUsers] (
	@p_vchLetterFilter VARCHAR(1),@p_vchOrderBy VARCHAR(20), @p_vchSortDirection VARCHAR(20), @p_vchQuickSearch VARCHAR(50), @p_intPage int, @p_intPageSize int
)
	
AS
BEGIN
DECLARE @startRowIndex INT
	SET @startRowIndex = (@p_intPage * @p_intPageSize) + 1;


	WITH Users as (
		SELECT ROW_NUMBER() OVER (ORDER BY intUserID ASC) as Row, 
		intUserID, vchFirstName, vchLastName,vchUserName, vchPassword,
		vchEmail, IsNull(U.intSecurityRoleID,0) as intSecurityRoleID, 
		IsNull(U.intWorkgroupID,0) as intWorkgroupID, vchInitials, vchBadgeNumber, 
		SR.vchRoleTitle AS vchSecurityRole, W.vchDescription AS vchWorkgroupDesc
	FROM tblUsers U, tblSecurityRoles SR, tblWorkgroups W
		WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID
			AND W.intWorkgroupID = U.intWorkgroupID
			AND vchLastName LIKE @p_vchLetterFilter + '%'
			AND vchLastName LIKE '%' + @p_vchQuickSearch + '%'
		)
		
			

	SELECT
		intUserID, vchFirstName, vchLastName,vchUserName, vchPassword,
		vchEmail, intSecurityRoleID, intWorkgroupID, vchInitials, vchBadgeNumber, 
		vchSecurityRole, vchWorkgroupDesc
	FROM Users
		WHERE Row BETWEEN @startRowIndex AND @startRowIndex + @p_intPageSize - 1
	ORDER BY 
		CASE
			WHEN @p_vchOrderBy = 'last' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchLastName ASC, vchFirstName ASC))
			WHEN @p_vchOrderBy = 'last' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchLastName DESC, vchFirstName DESC))
			WHEN @p_vchOrderBy = 'first' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchFirstName ASC, vchLastName ASC))
			WHEN @p_vchOrderBy = 'first' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchFirstName DESC, vchLastName DESC))
			WHEN @p_vchOrderBy = 'username' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchUserName DESC))
			WHEN @p_vchOrderBy = 'username' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchUserName ASC))
			WHEN @p_vchOrderBy = 'security' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchSecurityRole DESC, vchLastName DESC, vchFirstName DESC))
			WHEN @p_vchOrderBy = 'security' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchSecurityRole ASC, vchLastName ASC, vchFirstName ASC))
			WHEN @p_vchOrderBy = 'workgroup' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchWorkgroupDesc DESC, vchLastName DESC, vchFirstName DESC))
			WHEN @p_vchOrderBy = 'workgroup' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchWorkgroupDesc ASC, vchLastName ASC, vchFirstName ASC))
		END
	
END /* CREATE PROCEDURE procSelectAllUsers */

DECLARE	@p_vchLetterFilter VARCHAR(1),@p_vchOrderBy VARCHAR(20), @p_vchSortDirection VARCHAR(20), @p_vchQuickSearch VARCHAR(50), @p_intPage int, @p_intPageSize int

SET @p_vchLetterFilter = ''
set @p_vchOrderBy = 'last'
set @p_vchSortDirection = 'DESC'
set @p_vchQuickSearch = ''
set @p_intPage = 3
set @p_intPageSize = 15

	

DECLARE @TempTable TABLE(RowID INT IDENTITY, UserID int)

INSERT INTO @TempTable(UserID)
SELECT intUserID 
	FROM tblUsers U, tblSecurityRoles SR, tblWorkgroups W
		WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID
			AND W.intWorkgroupID = U.intWorkgroupID
			AND vchLastName LIKE @p_vchLetterFilter + '%'
			AND vchLastName LIKE '%' + @p_vchQuickSearch + '%'
	ORDER BY 
		CASE
			WHEN @p_vchOrderBy = 'last' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchLastName ASC, vchFirstName ASC))
			WHEN @p_vchOrderBy = 'last' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchLastName DESC, vchFirstName DESC))
			WHEN @p_vchOrderBy = 'first' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchFirstName ASC, vchLastName ASC))
			WHEN @p_vchOrderBy = 'first' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchFirstName DESC, vchLastName DESC))
			WHEN @p_vchOrderBy = 'username' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchUserName DESC))
			WHEN @p_vchOrderBy = 'username' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchUserName ASC))
			WHEN @p_vchOrderBy = 'security' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchRoleTitle DESC, vchLastName DESC, vchFirstName DESC))
			WHEN @p_vchOrderBy = 'security' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchRoleTitle ASC, vchLastName ASC, vchFirstName ASC))
			WHEN @p_vchOrderBy = 'workgroup' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchDescription DESC, vchLastName DESC, vchFirstName DESC))
			WHEN @p_vchOrderBy = 'workgroup' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchDescription ASC, vchLastName ASC, vchFirstName ASC))
		END

SELECT	UserID, vchFirstName, vchLastName,vchUserName, vchPassword,
		vchEmail, IsNull(U.intSecurityRoleID,0) as intSecurityRoleID, 
		IsNull(U.intWorkgroupID,0) as intWorkgroupID, vchInitials, vchBadgeNumber, 
		SR.vchRoleTitle AS vchSecurityRole, W.vchDescription AS vchWorkgroupDesc
	FROM @TempTable T, tblUsers U, tblSecurityRoles SR, tblWorkgroups W
		WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID
			AND T.UserID = U.intUserID
			AND W.intWorkgroupID = U.intWorkgroupID
			AND vchLastName LIKE @p_vchLetterFilter + '%'
			AND vchLastName LIKE '%' + @p_vchQuickSearch + '%'
			AND t.RowID BETWEEN ((@p_intPage - 1) * @p_intPageSize + 1) AND (@p_intPage * @p_intPageSize)