dbo.spCCPValidate []

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== dbo.spCCPValidate ======

Object type: Stored Procedure (MS-SQL)\\
Parent: [[internal:dbsql:customerdbsql:start|CustomerDBSQL]]
\\ \\

<code sql>
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
</code>
User Tools

Site Tools

Page Tools
