Object type: Stored Procedure (MS-SQL)
Parent: CustomerDBSQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--spCCPValidate 'tech','clyde'
--spCCPValidate 'maryjo','test'
 
-- =============================================
-- Author:	www.allshore.us, Radu B.
-- Update date: 6/19/2012 - update also handles GROUP logins
--              6/12/2024 - cahnged GroupMembers from VARCHAR(255) to VARCHAR(MAX)
-- Description:	Validates the User for Customer Control Panel
-- =============================================
ALTER PROCEDURE [dbo].[spCCPValidate]
(
	-- Add the parameters for the stored procedure here
	@UserName nvarchar(255),
	@Password nvarchar(255)
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @t TABLE (
		LoginType VARCHAR(2),
		LoginName VARCHAR(50),
		LoginPassword VARCHAR(50),
		GroupMembers VARCHAR(MAX)
	)
 
 
	-- STEP 1 - Try to validate it as a regular user
	INSERT @t 
		SELECT 'ST' AS LoginType, [WMRptsLogin], [WMRptsPwd], ''
		FROM [Stations]
		WHERE [WMRptsLogin] = @UserName AND  [WMRptsPwd] = @Password
 
 
	-- STEP 2 - If the above query returns ZERO rows
	IF (SELECT @@ROWCOUNT FROM @t) IS NULL
	BEGIN
 
		DECLARE @temp TABLE (
			LoginType VARCHAR(2),
			LoginName VARCHAR(50),
			LoginPassword VARCHAR(50),
			LoginSourceID INT
		)
		INSERT @temp 
		SELECT	[GroupLoginType] AS LoginType,
				[GroupLogin],[GroupPassword],[GroupSourceID] 
			FROM CCPGroups
			WHERE [GroupLogin] = @UserName AND [GroupPassword] = @Password	
 
		IF (SELECT @@ROWCOUNT FROM @temp) IS NOT NULL
		BEGIN
 
			print 'not a station'
			DECLARE @TYPE AS VARCHAR(2)
			SET @TYPE = (SELECT TOP 1 LoginType FROM @temp)
 
			print 'type = ' + @TYPE
 
			DECLARE @StationList AS VARCHAR(MAX)
 
			-- get stations for the CLUSTER only
			IF (SELECT TOP 1 LoginType FROM @temp) = 'CL'
			BEGIN
				SELECT @StationList = ISNULL(@StationList + ', ', '')
					+ [CALL LTR] FROM Stations 
					WHERE LocationNo = (SELECT LoginSourceID FROM @temp) AND StationActive = 1
					ORDER BY [CALL LTR]	
			END
			-- get stations for the whole COMPANY
			ELSE IF (SELECT TOP 1 LoginType FROM @temp) = 'CO'
			BEGIN
 
				SELECT @StationList = ISNULL(@StationList + ',', '')
					+ [CALL LTR] FROM Stations 
					WHERE [Billling Account No] = (SELECT LoginSourceID FROM @temp) AND StationActive = 1
					ORDER BY [CALL LTR]		
			END				
 
			-- now insert these values into @t, PLUE the ALL stations as a tring
			INSERT @t
				SELECT TOP 1 LoginType, LoginName, LoginPassword, @StationList FROM @temp
 
 
		END
	END
 
	-- STEP 3
	SELECT 
		LoginType,
		LoginName,
		LoginPassword,
		Isnull(GroupMembers,'') AS GroupMembers
	FROM @t
END
GO