dbo.spCCPValidate

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 -- Description: Validates the User for Customer Control Panel -- Latest update also handles GROUP logins -- ============================================= CREATE 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(255) ) -- 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