/****** Object: Stored Procedure dbo.cabal_sp_auth Script Date: 2008-4-14 21:40:21 ******/
CREATE PROCEDURE cabal_sp_auth (@id varchar(32), @password varchar(32), @login int, @ip varchar(16))
AS
SET NOCOUNT ON
-- Return Value Type
DECLARE @AR_SUCCESS int, @AR_FAILED0 int, @AR_ALREADY int, @AR_OUTFSVC int, @AR_TIMEEXP int, @AR_BLOCKIP int, @AR_BLOCKID int, @AR_FREEID int
SET @AR_SUCCESS = 0x20
SET @AR_FAILED0 = 0x21
SET @AR_ALREADY = 0x22
SET @AR_OUTFSVC = 0x23
SET @AR_TIMEEXP = 0x24
SET @AR_BLOCKIP = 0x25
SET @AR_BLOCKID = 0x26
SET @AR_FREEID = 0x27
BEGIN TRAN
DECLARE @rusernum as int
DECLARE @rauthtype as int
DECLARE @ridentityno as char(13)
DECLARE @rlogin as int
SELECT @rusernum=usernum, @rauthtype=authtype, @ridentityno=identityno, @rlogin=login
FROM cabal_auth_table
WHERE ID = @id and password = dbo.fn_md5(@password)
IF @@ROWCOUNT = 0
BEGIN
COMMIT TRAN
SELECT @AR_FAILED0
RETURN
END
DECLARE @ReleaseDate DATETIME
IF @rauthtype = 2
BEGIN -- ?? ??
DECLARE @isBlocked BIT
SET @isBlocked = 1
SELECT @ReleaseDate = ReleaseDate FROM cabal_blockuser_table WHERE UserNum = @rusernum
IF (ISNULL(@ReleaseDate, 0) <> 0)
BEGIN
IF (@ReleaseDate < GETDATE() )
BEGIN -- expired
EXEC cabal_tool_ReleaseBlockUser @rusernum
SET @isBlocked = 0
END
END
IF @isBlocked = 1
BEGIN
COMMIT TRAN
SELECT @AR_BLOCKID, @rusernum, 0
RETURN
END
END
ELSE IF @rauthtype = 3
BEGIN -- ???? ??
SELECT @ReleaseDate = ISNULL(ReleaseDate, 0) FROM cabal_blockuser_table WHERE UserNum = @rusernum
IF (@ReleaseDate <> 0) AND (@ReleaseDate < GETDATE() )
BEGIN -- expired
EXEC cabal_tool_ReleaseBlockUser @rusernum
END
ELSE
BEGIN -- blocked
SET @ridentityno = SUBSTRING(@ridentityno, 1, 6) + '0000001'
END
END
-- Check blockip
IF EXISTS (SELECT id FROM cabal_blockip_list WHERE dbo.inet_addr(@ip) between fromip AND toip)
BEGIN
COMMIT TRAN
SELECT @AR_BLOCKIP, @rusernum, 0
RETURN
END
IF @rlogin <> 0
BEGIN
-- ?? ??? ?
COMMIT TRAN
SELECT @AR_ALREADY, @rusernum, @rlogin
RETURN
END
-- Check Charging
DECLARE @rType as int -- 0: free, 1: charged
DECLARE @rServiceKind as int -- 0: free, 1: charged
DECLARE @rExpireDate as DATETIME
DECLARE @rPayMinutes as int
SELECT @rType=Type,
@rExpireDate=ExpireDate,
@rPayMinutes=PayMinutes,
@rServiceKind=ServiceKind
FROM cabal_charge_auth WHERE usernum = @rusernum
IF @@ROWCOUNT = 0
BEGIN
SET @rType = 0
SET @rExpireDate = '2005-12-18 16:00'
SET @rPayMinutes = 0
SET @rServiceKind = 0
END
-- Check free ip
DECLARE @rIsFreeIp as int
SET @rIsFreeIp = 0
IF EXISTS (SELECT * FROM cabal_freeip_list WHERE IP=@ip)
BEGIN
SET @rIsFreeIp = 1
END
-- Free time (everyday PM 21~22)
DECLARE @StartFreeTime as DATETIME, @EndFreeTime as DATETIME
SET @StartFreeTime = CONVERT(VARCHAR(11), GETDATE(), 20) + '21:00:00'
SET @EndFreeTime = CONVERT(VARCHAR(11), GETDATE(), 20) + '21:00:00'
IF @rExpireDate < GETDATE() and @StartFreeTime < GETDATE() and GETDATE() < @EndFreeTime
BEGIN
SET @rType = 2
SET @rExpireDate = @EndFreeTime
END
--IF @rExpireDate < GETDATE() and @rPayMinutes <= 0 and @rIsFreeIp <> 1
--BEGIN
-- ?? ?? ??
--COMMIT TRAN
--SELECT @AR_TIMEEXP, @rusernum, @rlogin
--RETURN
--END
DECLARE @AuthKey VARCHAR(32)
SET @AuthKey = REPLACE(NEWID(), '-', '')
UPDATE cabal_auth_table SET login = @login, logintime=GETDATE(), LastIp=@ip, AuthKey=@AuthKey WHERE usernum = @rusernum
SELECT
@AR_SUCCESS,
@rusernum,
@rlogin,
@ridentityno,
@rType,
DATEDIFF(second, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE()), '1970-01-01'), @rExpireDate), -- UTC Time
@rPayMinutes,
@rIsFreeIp,
@rServiceKind,
@AuthKey
COMMIT TRAN
SET NOCOUNT OFF
GO
Reply With Quote
Originally Posted by felipeluzentti
