- Joined
- Dec 18, 2009
- Messages
- 333
- Reaction score
- 142
BILLING Database Clean and Fixed
Table and Procedure
Example auto add Premium without edit/insert manual from register page
Table and Procedure
Code:
/*
BILLING DATABASE
[USER=793186]ROSLAW[/USER]
ALL VERSION 2.2.3 AND 2.2.3.2
Log Fix :
- Fix multiple same account (Lower and Upper Case) accumulation cash amount.
- Fix status premium by DateEnd
- Fix Automation BillingType Update by Remain Time
Example Insert From Registration Page :
INSERT INTO tbl_personal_billing (ID, EndDate) VALUES (CONVERT(binary, ".$username.", CONVERT(datetime, GETDATE()+3))) // +3 = 3 Day's premium service
INSERT INTO tbl_user (UserID, Cash) VALUES (CONVERT(binary, ".$username."), '100') // 100 = 100 Cashpoint
*/
-- Table tbl_personal_billing --
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_personal_billing]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_personal_billing](
[ID] [binary](13) NOT NULL,
[BillingType] [int] NOT NULL CONSTRAINT [DF2_tbl_personal_billing_accounttype] DEFAULT ((0)),
[EndDate] [datetime] NOT NULL CONSTRAINT [DF_tbl_personal_billing_EndDate] DEFAULT (getdate()),
[RemainTime] [int] NULL CONSTRAINT [DF2_tbl_personal_billing_RemainTime] DEFAULT ((0)),
CONSTRAINT [PK2_personal_billing] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
-- Table tbl_user --
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_user]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_user](
[ID] [int] IDENTITY(1,1),
[UserID] [binary](13) NOT NULL,
[Cash] [int] NOT NULL CONSTRAINT [DF_tbl_user_Cash] DEFAULT ((0)),
CONSTRAINT [PK_tbl_user] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
-- Table tbl_cashlogrf --
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_cashlogrf]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_cashlogrf](
[userid] [binary](13) NOT NULL,
[character] [varchar](50) NOT NULL,
[pscode] [varchar](20) NOT NULL,
[quantity] [int] NOT NULL,
[server] [varchar](50) NOT NULL,
[scode] [bigint] NOT NULL,
[price] [int] NOT NULL,
[dcrate] [int] NOT NULL,
[Date] [datetime] NOT NULL CONSTRAINT [DF1_tbl_cashlog_Date] DEFAULT (getdate()),
[status] [int] NULL
) ON [PRIMARY]
END
GO
-- Tabel tbl_cashList --
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_cashList]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_cashList](
[id] [varchar](20) NOT NULL,
[name] [varchar](50) NOT NULL,
[cost] [int] NOT NULL
) ON [PRIMARY]
END
GO
-- Procedure Check Account when LoginServer --
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RF_CheckAccountStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql [USER=2000137629]statement[/USER] = N'
CREATE PROCEDURE [dbo].[RF_CheckAccountStatus]
[USER=19862]id[/USER] varchar(20),
@p2 int OUTPUT
AS
DECLARE [USER=1333340202]End[/USER]_time datetime
DECLARE @cur_time datetime
DECLARE @rem_time int
SELECT [USER=1333340202]End[/USER]_time = EndDate FROM tbl_personal_billing WHERE ID = CONVERT(binary, [USER=19862]id[/USER])
SELECT @cur_time = CONVERT(datetime, GETDATE())
IF [USER=1333340202]End[/USER]_time > @cur_time
BEGIN
SET @p2 = 2
SET @rem_time = CONVERT(int, [USER=1333340202]End[/USER]_time-@cur_time)
UPDATE tbl_personal_billing SET BillingType = 2 WHERE ID = CONVERT(binary, [USER=19862]id[/USER])
UPDATE tbl_personal_billing SET RemainTime = @rem_time WHERE ID = CONVERT(binary, [USER=19862]id[/USER])
RETURN @p2
END
ELSE
BEGIN
SET @p2 = 1
UPDATE tbl_personal_billing SET BillingType = 1 WHERE ID = CONVERT(binary, [USER=19862]id[/USER])
UPDATE tbl_personal_billing SET RemainTime = 0 WHERE ID = CONVERT(binary, [USER=19862]id[/USER])
RETURN @p2
END
'
END
GO
-- Procedure Check Account by BillingServer --
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RF_LoginBillingStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql [USER=2000137629]statement[/USER] = N'
CREATE PROCEDURE [dbo].[RF_LoginBillingStatus]
[USER=19862]id[/USER] varchar(20)
AS
DECLARE [USER=1333340202]End[/USER]_time datetime
DECLARE @cur_time datetime
DECLARE @rem_time int
SELECT [USER=1333340202]End[/USER]_time = EndDate FROM tbl_personal_billing WHERE ID = CONVERT(binary, [USER=19862]id[/USER])
SELECT @cur_time = CONVERT(datetime, GETDATE())
IF [USER=1333340202]End[/USER]_time > @cur_time
BEGIN
SET @rem_time = CONVERT(int, [USER=1333340202]End[/USER]_time-@cur_time)
UPDATE tbl_personal_billing SET BillingType = 2 WHERE ID = CONVERT(binary, [USER=19862]id[/USER])
UPDATE tbl_personal_billing SET RemainTime = @rem_time WHERE ID = CONVERT(binary, [USER=19862]id[/USER])
RETURN
END
ELSE
BEGIN
UPDATE tbl_personal_billing SET BillingType = 1 WHERE ID = CONVERT(binary, [USER=19862]id[/USER])
UPDATE tbl_personal_billing SET RemainTime = 0 WHERE ID = CONVERT(binary, [USER=19862]id[/USER])
RETURN
END
'
END
GO
-- Procedure Check Ping Server --
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RF_PING]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql [USER=2000137629]statement[/USER] = N'
CREATE PROC [dbo].[RF_PING]
AS
DECLARE @strCmd VARCHAR(60)
DECLARE @strServerIP VARCHAR(50)
SET @strServerIP = ''127.0.0.1''
SELECT @strCmd = ''ping '' + @strServerIP
/* EXEC Master..xp_cmdShell @strCmd */
'
END
GO
-- Procedure Buy Item --
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RF_ChargeBalance]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql [USER=2000137629]statement[/USER] = N'CREATE PROCEDURE [dbo].[RF_ChargeBalance]
@s_userid varchar(20),
@s_character varchar(50),
@s_pscode varchar(20),
@s_quantity int,
[USER=273835]S_S[/USER]erver varchar (50),
[USER=273835]S_S[/USER]code bigint,
@s_price int,
@s_dcrate int,
@p9 int OUTPUT
AS
SET NOCOUNT ON
DECLARE
@totalAmount AS int,
[USER=471367]proDuct[/USER]Amount AS int,
[USER=315880]cas[/USER]hBalance AS int
SELECT [USER=315880]cas[/USER]hBalance = Cash FROM tbl_user WHERE UserID = CONVERT(binary, @s_userid)
SELECT [USER=471367]proDuct[/USER]Amount = cost FROM tbl_cashList WHERE id = @s_pscode
SET @totalAmount = [USER=471367]proDuct[/USER]Amount*@s_quantity
IF @@error <> 0
BEGIN
SET @p9 = 0
RETURN @p9
END
ELSE
IF @s_userid IS NULL OR RTRIM(@s_userid)=''''
BEGIN
SET @p9 = 0
RETURN @p9
END
ELSE IF @s_pscode IS NULL OR LEN(@s_pscode) <> ''7''
BEGIN
SET @p9 = 0
RETURN @p9
END
ELSE IF @s_price IS NULL OR RTRIM(@s_price) = '''' OR @s_price < ''0''
BEGIN
SET @p9 = 0
RETURN @p9
END
ELSE IF @s_quantity IS NULL OR @s_quantity <= ''0'' OR @s_quantity >= ''100''
BEGIN
SET @p9 = 0
RETURN @p9
END
ELSE IF [USER=273835]S_S[/USER]code IS NULL OR RTRIM [USER=273835]S_S[/USER]code) = ''''
BEGIN
SET @p9 = 0
RETURN @p9
END
ELSE IF [USER=273835]S_S[/USER]erver IS NULL OR RTRIM [USER=273835]S_S[/USER]erver)= ''''
BEGIN
SET @p9 = 0
RETURN @p9
END
ELSE IF @s_character IS NULL OR RTRIM(@s_character) = ''''
BEGIN
SET @p9 = 0
RETURN @p9
END
ELSE IF [USER=471367]proDuct[/USER]Amount != @s_price OR @s_price <= 0 OR [USER=471367]proDuct[/USER]Amount <= 0 OR [USER=471367]proDuct[/USER]Amount IS NULL
BEGIN
SET @p9 = 0
RETURN @p9
END
ELSE IF @totalAmount > [USER=315880]cas[/USER]hBalance
BEGIN
SET @p9 = 0
RETURN @p9
END
SET [USER=315880]cas[/USER]hBalance = [USER=315880]cas[/USER]hbalance-@totalAmount
IF @@error <> 0
BEGIN
SET @p9 = 0
RETURN @p9
END
ELSE
BEGIN
SET @p9 = 1
INSERT INTO tbl_cashlogrf (userid, character, pscode, quantity, server, scode, price, dcrate, status)
VALUES (CONVERT(binary, @s_userid), @s_character, @s_pscode, @s_quantity, [USER=273835]S_S[/USER]erver, [USER=273835]S_S[/USER]code, @s_price, @s_dcrate, @p9)
UPDATE tbl_user SET Cash = [USER=315880]cas[/USER]hbalance WHERE UserID = CONVERT(binary, @s_userid)
RETURN @p9
END
'
END
GO
-- Procedure Check Cash Account --
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RF_CheckBalance]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql [USER=2000137629]statement[/USER] = N'
CREATE PROCEDURE [dbo].[RF_CheckBalance]
@s_userid varchar(50),
@p2 int OUTPUT
AS
SET NOCOUNT ON
SELECT @p2 = Cash FROM tbl_user WITH (READUNCOMMITTED) WHERE UserID = CONVERT(binary, @s_userid)
RETURN @p2
'
END
GOExample auto add Premium without edit/insert manual from register page
Code:
USE [RF_USER]
GO
/****** Object: StoredProcedure [dbo].[pSelect_AccountPass] Script Date: 09/16/2015 00:48:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[pSelect_AccountPass]
[USER=19862]id[/USER] varchar(13)
AS
DECLARE @curdate DATETIME
DECLARE [USER=848971]Birth[/USER]date DATETIME
DECLARE @plusdate DATETIME
DECLARE @AgeResult TINYINT
DECLARE [USER=43741]prem[/USER]Day_Add INT
DECLARE [USER=315880]cas[/USER]h_Add INT
SET @curdate = GETDATE()
SET [USER=848971]Birth[/USER]date = (SELECT birthdate FROM tbl_rfaccount WHERE ID = CONVERT(BINARY, [USER=19862]id[/USER]))
IF(ISDATE [USER=848971]Birth[/USER]date) = 1)
BEGIN
SET @plusdate = DATEADD(yy, 18, CONVERT(DATETIME, [USER=848971]Birth[/USER]date))
END
IF(ISDATE(@curdate) = 1) AND (@curdate >= @plusdate)
BEGIN
SET @AgeResult = 0
END
ELSE
BEGIN
SET @AgeResult = 1
END
SELECT RTRIM( CONVERT( VARCHAR(24), password) ), accounttype, AgeResult= @AgeResult FROM tbl_rfaccount WHERE ID = CONVERT(BINARY, [USER=19862]id[/USER])
SET [USER=43741]prem[/USER]Day_Add = 3 -- Premium Day Add for new account
SET [USER=315880]cas[/USER]h_Add = 0 -- Cashpoint Add for new account
IF NOT EXISTS(SELECT ID FROM BILLING.dbo.tbl_personal_billing WHERE ID = CONVERT(binary, [USER=19862]id[/USER]))
BEGIN
INSERT INTO BILLING.dbo.tbl_personal_billing (ID, EndDate) VALUES (CONVERT(binary, [USER=19862]id[/USER]), CONVERT(datetime, GETDATE() [USER=43741]prem[/USER]Day_Add))
INSERT INTO BILLING.dbo.tbl_user (UserID, Cash) VALUES (CONVERT(binary, [USER=19862]id[/USER]), [USER=315880]cas[/USER]h_Add)
ENDView attachment RoslawBilling.zip
Last edited:
