[Help] Premium 0 Days

[lollipop07]

Hi, I need someone to fine tune something for me..

Basically what it is it when ever someone gets to 0 days premium and logs into the server with 0 days, the server will lock and allow no more connections untill aujard is reloaded..

Ive been trying my best to add a check into account_login stored procedure to basically check the premium service table if the users has 0 days and if they do to remove them from that table..

Im not too good with procs and sql grammering but I have a general idea of when the check needs to do..

here is my account_login proc.

USE [KN_online]
GO
/****** Object:  StoredProcedure [dbo].[ACCOUNT_LOGIN]    Script Date: 07/15/2008 19:23:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ACCOUNT_LOGIN]
@AccountID    varchar(21),
@Password    varchar(13),
@nRet        smallint    OUTPUT

AS

DECLARE @Nation tinyint, @CharNum smallint
SET @Nation = 0
SET @CharNum = 0
DECLARE @pwd varchar(13)
SET @pwd = null
SELECT @pwd = strPasswd FROM [dbo].[TB_USER] WHERE strAccountID = @AccountID  and  idays>0
IF @pwd IS null
BEGIN
    --SET @nRet = 0
             SET @nRet = 4
    RETURN
END
ELSE IF @pwd <> @Password
BEGIN
    --SET @nRet = 0
             SET @nRet = 3
    RETURN
END

exec rundupecheck @accountid
exec rundupecheckinn @accountid

SELECT @Nation = bNation, @CharNum = bCharNum FROM ACCOUNT_CHAR WHERE strAccountID = @AccountID
IF @@ROWCOUNT = 0
BEGIN
    SET @nRet = 1
    RETURN
END
IF @CharNum = 0
BEGIN
    SET @nRet = 1
    RETURN
END
ELSE 
BEGIN
SET @nRet = 1
    RETURN
END

heres what the grammer needs to be like (like I said I dont know sql grammer to good)

begin
check table PREMIUM_SERVICE where StrAccountID = @AccountID and nDays='0' 

if nDays='0'

delete from table PREMIUM_SERVICE where StrAccountID = @AccountID
end

Please if anyone can edit this to work, Would really make my day..

Thanks in advance.

[MentaL]

[Mausie]

Why do u try it the hard way?
What you can do is basically edit the Account Login procedure to give them 999 days of premium instead of 3...

Look at this part of the procedure:

begin
insert into PREMIUM_SERVICE (strAccountID, strType, nDays) VALUES (@AccountID, 1, 3)
end

The 1 is the type of premium.
The 3 is the amount of days (change it to 999).

Problem solved, nobody will ever reach 0 days of premium membership.

[lollipop07]

Why do u try it the hard way?
What you can do is basically edit the Account Login procedure to give them 999 days of premium instead of 3...

Look at this part of the procedure:

begin
insert into PREMIUM_SERVICE (strAccountID, strType, nDays) VALUES (@AccountID, 1, 3)
end

The 1 is the type of premium.
The 3 is the amount of days (change it to 999).

Problem solved, nobody will ever reach 0 days of premium membership.

The thing is that, I do want people to reach 0 days for my own reasons..

I have fixed this issue now by adding a check into my Account login Proc to basically Stop users with 0 Premium from Logging in...


Now my issue is this script:

ALTER PROCEDURE [dbo].[UPDATE_PREMIUM_SERVICE_USER_EXEC] 

AS 
 
DECLARE @d24 datetime

SET @d24 = DateAdd(hh,-24, GETDATE())
UPDATE PREMIUM_SERVICE SET nDays = nDays - 1 where Date > @d24

This is suppose to check the Date column in my premium_service table and if more than 24 hours has passed, it should -1 day from the users Premium days left..

It runs but nothing is changed..

[lollipop07]

Ok.

Can Anyone see why this is updating every 15 mins? I need it to Update every 1440 mins.

BTW its not the CONVERT(VARCHAR(15) bit of the script.

SET @d24 = CONVERT(VARCHAR(15) , GETDATE(),102)
SET @d24 = DATEADD(HH,DATEPART(HH,dateadd(MI, 10, GETDATE())),@d24)

[Si6]

LOL

Why do u try it the hard way?

could also be known as the proper way instead of the "easy way" as your referring to which is half assed like most of the other crap

[lollipop07]

LOL


could also be known as the proper way instead of the "easy way" as your referring to which is half assed like most of the other crap

Amen