USE [RF_RusBill]
GO
/****** Object: StoredProcedure [dbo].[RF_PING] Script Date: 07/25/2009 00:04:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <_sharky_>
-- Create date: <22.05.2009>
-- Description: <RF_PING ???>
-- =============================================
CREATE PROCEDURE [dbo].[RF_PING]
--@param1 varchar(16)
AS
BEGIN
SET NOCOUNT ON;
--insert into tbl_a (name,value) values ('param1',@param1)
END
GO
/****** Object: Table [dbo].[tbl_UserStatus] Script Date: 07/25/2009 00:04:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_UserStatus](
[serial] [int] IDENTITY(1,1) NOT NULL,
[id] [varchar](16) NOT NULL,
[Status] [int] NOT NULL,
[DTStartPrem] [datetime] NULL,
[DTEndPrem] [datetime] NULL,
[Cash] [int] NOT NULL CONSTRAINT [DF_tbl_UserStatus_Cash] DEFAULT ((0)),
[actmonths] [int] NOT NULL CONSTRAINT [DF_tbl_UserStatus_actmonths] DEFAULT ((0)),
CONSTRAINT [PK_tbl_UserStatus] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_paylog] Script Date: 07/25/2009 00:04:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_paylog](
[PayID] [bigint] NOT NULL,
[UserID] [varchar](24) NULL,
[sum] [float] NOT NULL,
[accept] [bit] NOT NULL CONSTRAINT [DF_tbl_paylog_accept] DEFAULT ((0)),
[DT] [datetime] NOT NULL CONSTRAINT [DF_tbl_paylog_DT] DEFAULT (getdate()),
[CheckSum] [binary](4) NOT NULL CONSTRAINT [DF_tbl_paylog_CheckSum] DEFAULT (0xFFFFFFFF),
CONSTRAINT [PK_tbl_paylog] PRIMARY KEY CLUSTERED
(
[PayID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_CashLog] Script Date: 07/25/2009 00:04:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_CashLog](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[Account] [varchar](20) NOT NULL,
[Char] [varchar](30) NOT NULL,
[ItemID] [varchar](7) NOT NULL,
[Amount] [int] NOT NULL,
[Server] [varchar](32) NOT NULL,
[UID] [bigint] NOT NULL,
[Cost] [int] NOT NULL,
[Discount] [float] NOT NULL,
[Return] [tinyint] NOT NULL,
CONSTRAINT [PK_tbl_CashLog] PRIMARY KEY CLUSTERED
(
[Serial] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[RF_CheckBalance] Script Date: 07/25/2009 00:04:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RF_CheckBalance]
@s_userid varchar(50)
, @s_amount int OUTPUT
AS
SET NOCOUNT ON
SELECT @s_amount = Cash
FROM tbl_UserStatus WITH (READUNCOMMITTED)
WHERE ID = @s_userid
IF @s_amount IS NULL
SET @s_amount = 0
GO
/****** Object: StoredProcedure [dbo].[RF_CheckAccountStatus] Script Date: 07/25/2009 00:04:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <_sharky_>
-- Create date: <22.05.2009>
-- Description: <User Account Status Check Proc>
-- =============================================
CREATE PROCEDURE [dbo].[RF_CheckAccountStatus]
@id as varchar(16),
@Status int output
AS
BEGIN
SET NOCOUNT ON;
declare @EndDt datetime
select @EndDt=DTEndPrem from tbl_UserStatus where id=@id
if @EndDt>GETDATE() begin
SELECT @status=[Status] from tbl_UserStatus where id=@id
end else begin
Set @Status=1
update tbl_UserStatus set [Status]=@status where id=@id
end
END
GO
/****** Object: StoredProcedure [dbo].[RF_ActivatePremium] Script Date: 07/25/2009 00:04:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <_sharky_>
-- Create date: <22.05.2009>
-- Description: <Activation Premium Prc>
-- =============================================
CREATE PROCEDURE [dbo].[RF_ActivatePremium]
@UserID varchar(16),
@months tinyint,
@ret char(1) output
AS
BEGIN
SET NOCOUNT ON;
declare @serial int
declare @plusdate datetime
declare @curdate datetime
set @serial=0
select @serial=serial from tbl_UserStatus where id=@UserID
if @serial<1 begin
set @ret='F'
return
end
select @curdate=DTEndPrem from tbl_userstatus where serial=@serial
update tbl_userstatus set actmonths=@months where serial=@serial
if @curdate is null
begin
SET @plusdate = DATEADD(mm, @months, GETDATE())
update tbl_UserStatus set [Status]=2, DTStartPrem=GETDATE(), DTEndPrem=@plusdate where serial=@serial
end else begin
if @curdate>getdate() begin
update tbl_UserStatus set [Status]=2, DTEndPrem=DATEADD(mm,@months,DTEndPrem) where serial=@serial
end else begin
SET @plusdate = DATEADD(mm, @months, GETDATE())
update tbl_UserStatus set [Status]=2, DTStartPrem=GETDATE(), DTEndPrem=@plusdate where serial=@serial
end
end
if(@@ERROR=0) begin
set @ret='T'
return
end
END
GO
/****** Object: StoredProcedure [dbo].[commit_pay] Script Date: 07/25/2009 00:04:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[commit_pay]
@inv_id bigint,
@mon float,
@summ int,
@rw char(1) output
as
declare @a as bit
declare @p as bigint
declare @accid varchar(24)
select @p=PayID, @a=accept from dbo.tbl_paylog where PayID=@inv_id
--update dbo.tbl_paylog set UserID=@accid where PayID=@inv_id
select @accid=UserID from tbl_paylog where PayID=@inv_id
set @rw='A'
if @a=convert(bit,0) and @p <> 0
begin
update dbo.tbl_userStatus set Cash=Cash+@mon*200 where ID=@accid
update dbo.tbl_paylog set accept=convert(bit,1),[sum]=@mon where PayID=@inv_id
set @rw='T'
return
end
if @p is null
begin
set @rw='F'
return
end
if @a is null and @p<>null
begin
set @rw='C'
return
end
GO
/****** Object: StoredProcedure [dbo].[RF_ChargeBalance] Script Date: 07/25/2009 00:04:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RF_ChargeBalance]
@Login varchar(32)
, @Char varchar(20)
, @ItemID varchar(20)
, @Amount int
, @Server varchar(20)
, @UID bigint
, @Cost varchar(20)
, @Discount int
, @ret int output
AS
set nocount on
DECLARE
@totalAmount as int,
@ProductAmount as int,
@cashBalance as int
INSERT INTO [dbo].[tbl_CashLog] ([Account],[Char],[ItemID],[Amount],[Server],[UID],[Cost],[Discount],[Return])
VALUES (@login,@char,@ItemID,@Amount,@Server,@UID,@cost,@Discount,0)
if @@error <> 0
begin
Set @ret=0
return
end
else
if @Login is null or RTrim(@Login)=''
begin
Set @ret=0
return
end
else if @Cost is null or @Cost<='0'
begin
Set @ret=0
return
end
else if @Amount is null or @Amount<='0'
begin
Set @ret=0
return
end
else if @Discount is null or @Discount < '0' or @Discount >= '100'
begin
Set @ret=0
return
end
else if @ItemID is null or rtrim(@ItemID) =''
begin
Set @ret=0
return
end
else if @Server is null or rtrim(@Server)=''
begin
Set @ret=0
return
end
else if @Char is null or rtrim(@Char)=''
begin
Set @ret=0
return
end
select @cashBalance=Cash from tbl_UserStatus where ID=@Login
--select @productAmount=cost from tbl_cashList where id=@ItemID
/*
if @productAmount != @cost or @Cost<=0 or @productAmount <=0 or @productAmount is null
begin
set @ret=0
return
end
*/
if @Discount=0 begin
set @totalAmount=@Cost*@amount--@productAmount*@Amount
end else begin
set @totalAmount=convert(int,(@Cost*@amount)/@discount)
end
if @totalAmount > @CashBalance
begin
set @ret=0
return
end
set @cashBalance=@cashbalance-@totalAmount
update tbl_UserStatus set Cash=@cashbalance where ID=@Login
if @@error<>0
begin
set @ret=0
return
end
else
begin
set @ret=1
end
update [dbo].[tbl_CashLog] set [Return]=@ret where Account=@Login
GO
