中财网中秋公司活动策划:商城积分数据库设计
来源：百度文库 编辑：中财网 时间：2024/04/30 17:14:36
商城积分数据库设计
分类： 数据库2011-10-09 14:15 52人阅读 评论(0) 收藏 举报
需求：①积分累计：获取积分的过期时间为半年，消耗积分遵循先进先出的原则（当然要在总积分够消耗的情况下）
②积分月结：每月一号汇总上月获取和消耗积分并且扣除过期积分。
思路：主要考虑到积分结算的压力问题，若活动用户产生的数据明细成千上百万结算时就会很麻烦，考虑把一些压力转移到明细操作中：
①获取积分时同时记录在当月的月记录（稍后把数据库脚本放上）里，每月一条记录。当然会在总积分表中增加
②消耗积分时会同时在某个字段中记录当月总消耗的数量，这样就可以减少月结时的操作步骤，
③月结时要根据先进先出的原则，先从月表中扣除上月消耗的积分，然后从积分总表中扣除过期积分。
数据库简介：
辅助配置表
明细表：AppID,ModleID为辅助配置表的内容，AccountID为用户ID
月表：GetNum当月获取积分，LeaveNum当月剩余积分若过期则设置为0。
总积分表：CostNum记录消耗的积分总和，每一次结算后则重置为0；
其它：这个存储过程实现了批量更新数据，因为一个用户有多个月的积分记录，所以要使用一对多的批量更新模式，
view plain
ALTER PROC [dbo].[gs_MonthBalance2]
AS
BEGIN
Declare @ExpireDate varchar(10)
SET @ExpireDate=convert(char(7),DATEADD(MONTH,DATEDIFF(MONTH,0,getdate())-1,0),120)--过期积分月份(当前月的上月)
;WITH t AS(
SELECT rn=ROW_NUMBER()over(Order by BalanceDate),* FROM CodeMonth),
t1 AS(
SELECT *,tmpsum=(SELECT SUM(LeaveNum) FROM t WHERE AccountID=a.AccountID and rn<=a.rn) FROM t a)
---批量更新数据
UPDATE a SET
a.LeaveNum=a.LeaveNum-case
WHEN a.LeaveNum-(a.tmpsum-b.CostNum) <=0 THEN 0
WHEN a.tmpsum-b.CostNum <=0 THEN a.LeaveNum
ELSE a.LeaveNum-(a.tmpsum-b.CostNum)
end
FROM t1 a JOIN CodeTotle b on a.AccountID=b.AccountID
WHERE b.CostNum>0--排除未消耗数量
---清除消耗记录
UPDATE dbo.CodeTotle SET CostNum=0 WHERE CostNum>0
---从总数中删除过期数据
UPDATE ct SET ct.CodeNum=ct.CodeNum-cm.LeaveNum
FROM dbo.CodeTotle ct,
(SELECT * From CodeMonth WHERE convert(char(7),[ExpireDate],120)=@ExpireDate) AS cm
--(SELECT * From CodeMonth) AS cm
WHERE ct.AccountID=cm.AccountID
---从月表清除过期数据
UPDATE CodeMonth SET LeaveNum=0 WHERE convert(char(7),[ExpireDate],120)=@ExpireDate
--UPDATE CodeMonth SET LeaveNum=0
END
整个数据库脚本：
view plain
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CodeDetail](
[SeqID] [int] IDENTITY(1,1) NOT NULL,
[AccountID] [int] NOT NULL,
[AppID] [int] NOT NULL,
[ModleID] [int] NOT NULL,
[CodeNum] [int] NOT NULL,
[Note] [varchar](200) NULL,
[Des] [varchar](200) NULL,
[CreateDate] [datetime] NOT NULL,
[ExpireDate] [datetime] NOT NULL,
[Creater] [varchar](50) NOT NULL,
[CreateIP] [varchar](15) NOT NULL,
CONSTRAINT [PK_CODEDETAIL] PRIMARY KEY CLUSTERED
(
[SeqID] 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
/****** 对象: Table [dbo].[CodeConfig] 脚本日期: 10/09/2011 14:15:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CodeConfig](
[SeqID] [int] IDENTITY(1,1) NOT NULL,
[APPID] [int] NOT NULL,
[AppName] [varchar](50) NOT NULL,
[ModleID] [int] NOT NULL,
[ModleName] [varchar](50) NOT NULL,
[Note] [varchar](200) NULL CONSTRAINT [DF_CodeConfig_Note] DEFAULT (''),
[Status] [tinyint] NOT NULL CONSTRAINT [DF_CodeConfig_Status] DEFAULT ((1)),
CONSTRAINT [PK_CODECONFIG] PRIMARY KEY CLUSTERED
(
[SeqID] ASC,
[APPID] ASC,
[ModleID] 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
/****** 对象: Table [dbo].[CodeAccount] 脚本日期: 10/09/2011 14:15:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CodeAccount](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Password] [varchar](50) NOT NULL,
[Status] [int] NOT NULL CONSTRAINT [DF_gs_Accounts_Status] DEFAULT ((0)),
[AllowIP] [varchar](100) NOT NULL CONSTRAINT [DF_gs_Accounts_AllowIP] DEFAULT ('*'),
[Created] [datetime] NOT NULL CONSTRAINT [DF__gs_Accounts_Created] DEFAULT (getdate()),
[LastLogin] [datetime] NOT NULL CONSTRAINT [DF_gs_Accounts_LastLogin] DEFAULT (getdate()),
[LastLoginIP] [varchar](50) NOT NULL CONSTRAINT [DF_gs_Accounts_LastLoginIP] DEFAULT ('127.0.0.1'),
CONSTRAINT [PK_GS_ACCOUNTS] PRIMARY KEY CLUSTERED
(
[UserName] 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
/****** 对象: Table [dbo].[CodeMonth] 脚本日期: 10/09/2011 14:15:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CodeMonth](
[SeqID] [int] IDENTITY(1,1) NOT NULL,
[AccountID] [int] NOT NULL,
[GetNum] [int] NOT NULL,
[CostNum] [int] NOT NULL CONSTRAINT [DF_CodeMonth_CostNum] DEFAULT ((0)),
[LeaveNum] [int] NOT NULL,
[BalanceDate] [datetime] NOT NULL,
[ExpireDate] [datetime] NOT NULL,
[Note] [varchar](200) NULL,
[Exp1] [varchar](100) NULL,
CONSTRAINT [PK_CODEMONTH] PRIMARY KEY CLUSTERED
(
[SeqID] 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
/****** 对象: Table [dbo].[CodeTotle] 脚本日期: 10/09/2011 14:15:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CodeTotle](
[SeqID] [int] IDENTITY(1,1) NOT NULL,
[AccountID] [int] NOT NULL,
[CodeNum] [int] NOT NULL,
[CostNum] [int] NOT NULL CONSTRAINT [DF_CodeTotle_CostNum] DEFAULT ((0)),
[Status] [tinyint] NOT NULL CONSTRAINT [DF_CodeTotle_Status] DEFAULT ((1)),
[Note] [varchar](200) NULL CONSTRAINT [DF_CodeTotle_Note] DEFAULT (''),
[UpdateDate] [datetime] NOT NULL,
[Exp1] [varchar](100) NULL CONSTRAINT [DF_CodeTotle_Exp1] DEFAULT (''),
CONSTRAINT [PK_CODETOTLE] PRIMARY KEY CLUSTERED
(
[SeqID] ASC,
[AccountID] 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
/****** 对象: StoredProcedure [dbo].[gs_CodeDetail_Add] 脚本日期: 10/09/2011 14:15:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[gs_CodeDetail_Add]
@AccountID int,
@AppID int,
@ModleID int,
@CodeNum int,
@Note varchar(200),
@Des varchar(200),
@CreateDate datetime,
@Creater varchar(50),
@CreateIP varchar(15)
AS
Declare @ExpireDate DateTime
Set @ExpireDate=DATEADD(MONTH,7+DATEDIFF(MONTH,0,@CreateDate),0)-1
--明细添加
INSERT INTO CodeDetail ([AccountID],[AppID],[ModleID],[CodeNum],[Note],[Des],[CreateDate],[ExpireDate],[Creater],[CreateIP])
VALUES (@AccountID,@AppID,@ModleID,@CodeNum,@Note,@Des,@CreateDate,@ExpireDate,@Creater,@CreateIP)
--总积分
IF exists(SELECT * FROM CodeTotle WHERE AccountID= @AccountID)
BEGIN
UPDATE CodeTotle SET
[CodeNum] = [CodeNum]+@CodeNum,
[UpdateDate] = @CreateDate
WHERE [AccountID] = @AccountID
END
ELSE
BEGIN
INSERT INTO CodeTotle (
[AccountID],
[CodeNum],
[UpdateDate]
) VALUES (
@AccountID,
@CodeNum,
@CreateDate
)
END
--月结表
--已存在当月数据（Update）
IF exists(select * from dbo.CodeMonth WHERE convert(char(7),BalanceDate,120)=convert(char(7),@CreateDate,120) AND AccountID=@AccountID)--已经存在当月数据
BEGIN
IF(@CodeNum>0)
BEGIN
UPDATE dbo.CodeMonth SET GetNum=GetNum+@CodeNum,LeaveNum=LeaveNum+@CodeNum
WHERE convert(char(7),BalanceDate,120)=convert(char(7),@CreateDate,120) AND AccountID=@AccountID
END
ELSE
BEGIN
UPDATE dbo.CodeMonth SET CostNum=CostNum-@CodeNum WHERE AccountID=@AccountID AND convert(char(7),BalanceDate,120)=convert(char(7),@CreateDate,120)
UPDATE dbo.CodeTotle SET CostNum=CostNum-@CodeNum WHERE AccountID=@AccountID
END
END
--不存在当月数据（Insert)
ELSE
BEGIN
IF(@CodeNum>0)
BEGIN
INSERT INTO [dbo].[CodeMonth]([AccountID],[GetNum],[CostNum],[LeaveNum],[BalanceDate],[ExpireDate])
VALUES(@AccountID,@CodeNum,0,@CodeNum,@CreateDate,@ExpireDate)
END
ELSE
BEGIN
INSERT INTO [dbo].[CodeMonth]([AccountID],[GetNum],[CostNum],[LeaveNum],[BalanceDate],[ExpireDate])
VALUES(@AccountID,0,@CodeNum,0,@CreateDate,@ExpireDate)
UPDATE dbo.CodeTotle SET CostNum=CostNum-@CodeNum WHERE AccountID=@AccountID
END
END
select SCOPE_IDENTITY()
GO
/****** 对象: StoredProcedure [dbo].[gs_Month_Count] 脚本日期: 10/09/2011 14:15:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[gs_Month_Count]
AS
BEGIN
--先把上月获得数量放入数据库
INSERT INTO CodeMonth(AccountID,GetNum,LeaveNum,BalanceDate,[ExpireDate])
SELECT AccountID, SUM(CodeNum) as GetNum,SUM(CodeNum) as LeaveNum,getdate() as BalanceDate,
CONVERT(char(10),[ExpireDate],120) as [ExpireDate]
FROM CodeDetail WHERE CodeNum>0 AND DATEDIFF(month,CreateDate,getdate())=1--当前结算月的上月
GROUP BY AccountID, CONVERT(char(10),[ExpireDate],120)
--convert(char(7),CreateDate,120)=convert(char(7),DATEADD(MONTH,DATEDIFF(MONTH,0,getdate())-1,0),120)
--计算上月消耗数量和未消耗的人
INSERT INTO dbo.CodeMonth_Temp(AccountID,ExpendCode)
SELECT AccountID, -sum(CodeNum) as ExpendCode
FROM CodeDetail
WHERE CodeNum<0 AND datediff(month,CreateDate,getdate())=1
GROUP BY AccountID
Union
SELECT distinct AccountID ,0 as ExpendCode
FROM CodeDetail
where AccountID not in(SELECT distinct AccountID
FROM CodeDetail
WHERE CodeNum<0 AND datediff(month,CreateDate,getdate())=1 )
END
GO
/****** 对象: StoredProcedure [dbo].[gs_CodeConfig_Edit] 脚本日期: 10/09/2011 14:15:44 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[gs_CodeConfig_Edit]
@SeqID int,
@APPID int,
@AppName varchar(50),
@ModleID int,
@ModleName varchar(50),
@Note varchar(200),
@Status tinyint
AS
-- THIS STORED PROCEDURE NEEDS TO BE MANUALLY COMPLETED
-- MULITPLE PRIMARY KEY MEMBERS OR NON-GUID/INT PRIMARY KEY
DECLARE @Return int
SET @Return =1
IF(@SeqID=0)
BEGIN
IF exists(SELECT * FROM CodeConfig WHERE APPID= @APPID AND ModleID=@ModleID)
BEGIN
Set @Return=-1
END
ELSE
BEGIN
INSERT INTO CodeConfig (
[APPID],
[AppName],
[ModleID],
[ModleName],
[Note],
[Status]
) VALUES (
@APPID,
@AppName,
@ModleID,
@ModleName,
@Note,
@Status
)
END
END
ELSE
BEGIN
UPDATE CodeConfig SET
[AppName] = @AppName,
[ModleName] = @ModleName,
[Note] = @Note,
[Status] = @Status
WHERE
[SeqID] = @SeqID
AND [APPID] = @APPID
AND [ModleID] = @ModleID
END
Select @Return as [Return]
GO
/****** 对象: StoredProcedure [dbo].[gs_MonthBalance2] 脚本日期: 10/09/2011 14:15:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[gs_MonthBalance2]
AS
BEGIN
Declare @ExpireDate varchar(10)
SET @ExpireDate=convert(char(7),DATEADD(MONTH,DATEDIFF(MONTH,0,getdate())-1,0),120)--过期积分月份(当当前月的上月)
;WITH t AS(
SELECT rn=ROW_NUMBER()over(Order by BalanceDate),* FROM CodeMonth),
t1 AS(
SELECT *,tmpsum=(SELECT SUM(LeaveNum) FROM t WHERE AccountID=a.AccountID and rn<=a.rn) FROM t a)
---批量更新数据
UPDATE a SET
a.LeaveNum=a.LeaveNum-case
WHEN a.LeaveNum-(a.tmpsum-b.CostNum) <=0 THEN 0
WHEN a.tmpsum-b.CostNum <=0 THEN a.LeaveNum
ELSE a.LeaveNum-(a.tmpsum-b.CostNum)
end
FROM t1 a JOIN CodeTotle b on a.AccountID=b.AccountID
WHERE b.CostNum>0--排除未消耗数量
---清除消耗记录
UPDATE dbo.CodeTotle SET CostNum=0 WHERE CostNum>0
---从总数中删除过期数据
UPDATE ct SET ct.CodeNum=ct.CodeNum-cm.LeaveNum
FROM dbo.CodeTotle ct,
(SELECT * From CodeMonth WHERE convert(char(7),[ExpireDate],120)=@ExpireDate) AS cm
--(SELECT * From CodeMonth) AS cm
WHERE ct.AccountID=cm.AccountID
---从月表清除过期数据
UPDATE CodeMonth SET LeaveNum=0 WHERE convert(char(7),[ExpireDate],120)=@ExpireDate
--UPDATE CodeMonth SET LeaveNum=0
END
GO
模拟测试数据库脚本：
view plain
DECLARE @MyCounter INT
declare @the_date datetime
declare @AID INT
SET @AID=1000144
SET @MyCounter = 0
SET @the_date ='2011-08-01'
WHILE (@MyCounter < 2000)
BEGIN
WAITFOR DELAY '000:00:00'
EXECUTE [CodeDBV1_2].[dbo].[gs_CodeDetail_Add]
@AccountID=@AID
,@AppID=1
,@ModleID=11
,@CodeNum=-150
,@Note='Test'
,@Des='Test'
,@CreateDate=@the_date
,@Creater='System'
,@CreateIP='127.0.0.1'
SET @AID=@AID+1
--SET @the_date =dateadd(d,1,@the_date)
SET @MyCounter = @MyCounter + 1
END
目前做到这种程度，还没具体使用不知道还有什么漏洞！
分类： 数据库2011-10-09 14:15 52人阅读 评论(0) 收藏 举报
需求：①积分累计：获取积分的过期时间为半年，消耗积分遵循先进先出的原则（当然要在总积分够消耗的情况下）
②积分月结：每月一号汇总上月获取和消耗积分并且扣除过期积分。
思路：主要考虑到积分结算的压力问题，若活动用户产生的数据明细成千上百万结算时就会很麻烦，考虑把一些压力转移到明细操作中：
①获取积分时同时记录在当月的月记录（稍后把数据库脚本放上）里，每月一条记录。当然会在总积分表中增加
②消耗积分时会同时在某个字段中记录当月总消耗的数量，这样就可以减少月结时的操作步骤，
③月结时要根据先进先出的原则，先从月表中扣除上月消耗的积分，然后从积分总表中扣除过期积分。
数据库简介：
辅助配置表
明细表：AppID,ModleID为辅助配置表的内容，AccountID为用户ID
月表：GetNum当月获取积分，LeaveNum当月剩余积分若过期则设置为0。
总积分表：CostNum记录消耗的积分总和，每一次结算后则重置为0；
其它：这个存储过程实现了批量更新数据，因为一个用户有多个月的积分记录，所以要使用一对多的批量更新模式，
view plain
ALTER PROC [dbo].[gs_MonthBalance2]
AS
BEGIN
Declare @ExpireDate varchar(10)
SET @ExpireDate=convert(char(7),DATEADD(MONTH,DATEDIFF(MONTH,0,getdate())-1,0),120)--过期积分月份(当前月的上月)
;WITH t AS(
SELECT rn=ROW_NUMBER()over(Order by BalanceDate),* FROM CodeMonth),
t1 AS(
SELECT *,tmpsum=(SELECT SUM(LeaveNum) FROM t WHERE AccountID=a.AccountID and rn<=a.rn) FROM t a)
---
UPDATE a SET
a.LeaveNum=a.LeaveNum-case
WHEN a.LeaveNum-(a.tmpsum-b.CostNum) <=0 THEN 0
WHEN a.tmpsum-b.CostNum <=0 THEN a.LeaveNum
ELSE a.LeaveNum-(a.tmpsum-b.CostNum)
end
FROM t1 a JOIN CodeTotle b on a.AccountID=b.AccountID
WHERE b.CostNum>0--排除未消耗数量
---清除消耗记录
UPDATE dbo.CodeTotle SET CostNum=0 WHERE CostNum>0
---从总数中删除过期数据
UPDATE ct SET ct.CodeNum=ct.CodeNum-cm.LeaveNum
FROM dbo.CodeTotle ct,
(SELECT * From CodeMonth WHERE convert(char(7),[ExpireDate],120)=@ExpireDate) AS cm
--(SELECT * From CodeMonth) AS cm
WHERE ct.AccountID=cm.AccountID
---从月表清除过期数据
UPDATE CodeMonth SET LeaveNum=0 WHERE convert(char(7),[ExpireDate],120)=@ExpireDate
--UPDATE CodeMonth SET LeaveNum=0
END
整个数据库脚本：
view plain
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CodeDetail](
[SeqID] [int] IDENTITY(1,1) NOT NULL,
[AccountID] [int] NOT NULL,
[AppID] [int] NOT NULL,
[ModleID] [int] NOT NULL,
[CodeNum] [int] NOT NULL,
[Note] [varchar](200) NULL,
[Des] [varchar](200) NULL,
[CreateDate] [datetime] NOT NULL,
[ExpireDate] [datetime] NOT NULL,
[Creater] [varchar](50) NOT NULL,
[CreateIP] [varchar](15) NOT NULL,
CONSTRAINT [PK_CODEDETAIL] PRIMARY KEY CLUSTERED
(
[SeqID] 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
/****** 对象: Table [dbo].[CodeConfig] 脚本日期: 10/09/2011 14:15:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CodeConfig](
[SeqID] [int] IDENTITY(1,1) NOT NULL,
[APPID] [int] NOT NULL,
[AppName] [varchar](50) NOT NULL,
[ModleID] [int] NOT NULL,
[ModleName] [varchar](50) NOT NULL,
[Note] [varchar](200) NULL CONSTRAINT [DF_CodeConfig_Note] DEFAULT (''),
[Status] [tinyint] NOT NULL CONSTRAINT [DF_CodeConfig_Status] DEFAULT ((1)),
CONSTRAINT [PK_CODECONFIG] PRIMARY KEY CLUSTERED
(
[SeqID] ASC,
[APPID] ASC,
[ModleID] 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
/****** 对象: Table [dbo].[CodeAccount] 脚本日期: 10/09/2011 14:15:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CodeAccount](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Password] [varchar](50) NOT NULL,
[Status] [int] NOT NULL CONSTRAINT [DF_gs_Accounts_Status] DEFAULT ((0)),
[AllowIP] [varchar](100) NOT NULL CONSTRAINT [DF_gs_Accounts_AllowIP] DEFAULT ('*'),
[Created] [datetime] NOT NULL CONSTRAINT [DF__gs_Accounts_Created] DEFAULT (getdate()),
[LastLogin] [datetime] NOT NULL CONSTRAINT [DF_gs_Accounts_LastLogin] DEFAULT (getdate()),
[LastLoginIP] [varchar](50) NOT NULL CONSTRAINT [DF_gs_Accounts_LastLoginIP] DEFAULT ('127.0.0.1'),
CONSTRAINT [PK_GS_ACCOUNTS] PRIMARY KEY CLUSTERED
(
[UserName] 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
/****** 对象: Table [dbo].[CodeMonth] 脚本日期: 10/09/2011 14:15:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CodeMonth](
[SeqID] [int] IDENTITY(1,1) NOT NULL,
[AccountID] [int] NOT NULL,
[GetNum] [int] NOT NULL,
[CostNum] [int] NOT NULL CONSTRAINT [DF_CodeMonth_CostNum] DEFAULT ((0)),
[LeaveNum] [int] NOT NULL,
[BalanceDate] [datetime] NOT NULL,
[ExpireDate] [datetime] NOT NULL,
[Note] [varchar](200) NULL,
[Exp1] [varchar](100) NULL,
CONSTRAINT [PK_CODEMONTH] PRIMARY KEY CLUSTERED
(
[SeqID] 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
/****** 对象: Table [dbo].[CodeTotle] 脚本日期: 10/09/2011 14:15:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CodeTotle](
[SeqID] [int] IDENTITY(1,1) NOT NULL,
[AccountID] [int] NOT NULL,
[CodeNum] [int] NOT NULL,
[CostNum] [int] NOT NULL CONSTRAINT [DF_CodeTotle_CostNum] DEFAULT ((0)),
[Status] [tinyint] NOT NULL CONSTRAINT [DF_CodeTotle_Status] DEFAULT ((1)),
[Note] [varchar](200) NULL CONSTRAINT [DF_CodeTotle_Note] DEFAULT (''),
[UpdateDate] [datetime] NOT NULL,
[Exp1] [varchar](100) NULL CONSTRAINT [DF_CodeTotle_Exp1] DEFAULT (''),
CONSTRAINT [PK_CODETOTLE] PRIMARY KEY CLUSTERED
(
[SeqID] ASC,
[AccountID] 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
/****** 对象: StoredProcedure [dbo].[gs_CodeDetail_Add] 脚本日期: 10/09/2011 14:15:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[gs_CodeDetail_Add]
@AccountID int,
@AppID int,
@ModleID int,
@CodeNum int,
@Note varchar(200),
@Des varchar(200),
@CreateDate datetime,
@Creater varchar(50),
@CreateIP varchar(15)
AS
Declare @ExpireDate DateTime
Set @ExpireDate=DATEADD(MONTH,7+DATEDIFF(MONTH,0,@CreateDate),0)-1
--明细添加
INSERT INTO CodeDetail ([AccountID],[AppID],[ModleID],[CodeNum],[Note],[Des],[CreateDate],[ExpireDate],[Creater],[CreateIP])
VALUES (@AccountID,@AppID,@ModleID,@CodeNum,@Note,@Des,@CreateDate,@ExpireDate,@Creater,@CreateIP)
--总积分
IF exists(SELECT * FROM CodeTotle WHERE AccountID= @AccountID)
BEGIN
UPDATE CodeTotle SET
[CodeNum] = [CodeNum]+@CodeNum,
[UpdateDate] = @CreateDate
WHERE [AccountID] = @AccountID
END
ELSE
BEGIN
INSERT INTO CodeTotle (
[AccountID],
[CodeNum],
[UpdateDate]
) VALUES (
@AccountID,
@CodeNum,
@CreateDate
)
END
--月结表
--已存在当月数据（Update）
IF exists(select * from dbo.CodeMonth WHERE convert(char(7),BalanceDate,120)=convert(char(7),@CreateDate,120) AND AccountID=@AccountID)--已经存在当月数据
BEGIN
IF(@CodeNum>0)
BEGIN
UPDATE dbo.CodeMonth SET GetNum=GetNum+@CodeNum,LeaveNum=LeaveNum+@CodeNum
WHERE convert(char(7),BalanceDate,120)=convert(char(7),@CreateDate,120) AND AccountID=@AccountID
END
ELSE
BEGIN
UPDATE dbo.CodeMonth SET CostNum=CostNum-@CodeNum WHERE AccountID=@AccountID AND convert(char(7),BalanceDate,120)=convert(char(7),@CreateDate,120)
UPDATE dbo.CodeTotle SET CostNum=CostNum-@CodeNum WHERE AccountID=@AccountID
END
END
--不存在当月数据（Insert)
ELSE
BEGIN
IF(@CodeNum>0)
BEGIN
INSERT INTO [dbo].[CodeMonth]([AccountID],[GetNum],[CostNum],[LeaveNum],[BalanceDate],[ExpireDate])
VALUES(@AccountID,@CodeNum,0,@CodeNum,@CreateDate,@ExpireDate)
END
ELSE
BEGIN
INSERT INTO [dbo].[CodeMonth]([AccountID],[GetNum],[CostNum],[LeaveNum],[BalanceDate],[ExpireDate])
VALUES(@AccountID,0,@CodeNum,0,@CreateDate,@ExpireDate)
UPDATE dbo.CodeTotle SET CostNum=CostNum-@CodeNum WHERE AccountID=@AccountID
END
END
select SCOPE_IDENTITY()
GO
/****** 对象: StoredProcedure [dbo].[gs_Month_Count] 脚本日期: 10/09/2011 14:15:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[gs_Month_Count]
AS
BEGIN
--先把上月获得数量放入数据库
INSERT INTO CodeMonth(AccountID,GetNum,LeaveNum,BalanceDate,[ExpireDate])
SELECT AccountID, SUM(CodeNum) as GetNum,SUM(CodeNum) as LeaveNum,getdate() as BalanceDate,
CONVERT(char(10),[ExpireDate],120) as [ExpireDate]
FROM CodeDetail WHERE CodeNum>0 AND DATEDIFF(month,CreateDate,getdate())=1--当前结算月的上月
GROUP BY AccountID, CONVERT(char(10),[ExpireDate],120)
--convert(char(7),CreateDate,120)=convert(char(7),DATEADD(MONTH,DATEDIFF(MONTH,0,getdate())-1,0),120)
--计算上月消耗数量和未消耗的人
INSERT INTO dbo.CodeMonth_Temp(AccountID,ExpendCode)
SELECT AccountID, -sum(CodeNum) as ExpendCode
FROM CodeDetail
WHERE CodeNum<0 AND datediff(month,CreateDate,getdate())=1
GROUP BY AccountID
Union
SELECT distinct AccountID ,0 as ExpendCode
FROM CodeDetail
where AccountID not in(SELECT distinct AccountID
FROM CodeDetail
WHERE CodeNum<0 AND datediff(month,CreateDate,getdate())=1 )
END
GO
/****** 对象: StoredProcedure [dbo].[gs_CodeConfig_Edit] 脚本日期: 10/09/2011 14:15:44 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[gs_CodeConfig_Edit]
@SeqID int,
@APPID int,
@AppName varchar(50),
@ModleID int,
@ModleName varchar(50),
@Note varchar(200),
@Status tinyint
AS
-- THIS STORED PROCEDURE NEEDS TO BE MANUALLY COMPLETED
-- MULITPLE PRIMARY KEY MEMBERS OR NON-GUID/INT PRIMARY KEY
DECLARE @Return int
SET @Return =1
IF(@SeqID=0)
BEGIN
IF exists(SELECT * FROM CodeConfig WHERE APPID= @APPID AND ModleID=@ModleID)
BEGIN
Set @Return=-1
END
ELSE
BEGIN
INSERT INTO CodeConfig (
[APPID],
[AppName],
[ModleID],
[ModleName],
[Note],
[Status]
) VALUES (
@APPID,
@AppName,
@ModleID,
@ModleName,
@Note,
@Status
)
END
END
ELSE
BEGIN
UPDATE CodeConfig SET
[AppName] = @AppName,
[ModleName] = @ModleName,
[Note] = @Note,
[Status] = @Status
WHERE
[SeqID] = @SeqID
AND [APPID] = @APPID
AND [ModleID] = @ModleID
END
Select @Return as [Return]
GO
/****** 对象: StoredProcedure [dbo].[gs_MonthBalance2] 脚本日期: 10/09/2011 14:15:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[gs_MonthBalance2]
AS
BEGIN
Declare @ExpireDate varchar(10)
SET @ExpireDate=convert(char(7),DATEADD(MONTH,DATEDIFF(MONTH,0,getdate())-1,0),120)--过期积分月份(当当前月的上月)
;WITH t AS(
SELECT rn=ROW_NUMBER()over(Order by BalanceDate),* FROM CodeMonth),
t1 AS(
SELECT *,tmpsum=(SELECT SUM(LeaveNum) FROM t WHERE AccountID=a.AccountID and rn<=a.rn) FROM t a)
---批量更新数据
UPDATE a SET
a.LeaveNum=a.LeaveNum-case
WHEN a.LeaveNum-(a.tmpsum-b.CostNum) <=0 THEN 0
WHEN a.tmpsum-b.CostNum <=0 THEN a.LeaveNum
ELSE a.LeaveNum-(a.tmpsum-b.CostNum)
end
FROM t1 a JOIN CodeTotle b on a.AccountID=b.AccountID
WHERE b.CostNum>0--排除未消耗数量
---清除消耗记录
UPDATE dbo.CodeTotle SET CostNum=0 WHERE CostNum>0
---从总数中删除过期数据
UPDATE ct SET ct.CodeNum=ct.CodeNum-cm.LeaveNum
FROM dbo.CodeTotle ct,
(SELECT * From CodeMonth WHERE convert(char(7),[ExpireDate],120)=@ExpireDate) AS cm
--(SELECT * From CodeMonth) AS cm
WHERE ct.AccountID=cm.AccountID
---从月表清除过期数据
UPDATE CodeMonth SET LeaveNum=0 WHERE convert(char(7),[ExpireDate],120)=@ExpireDate
--UPDATE CodeMonth SET LeaveNum=0
END
GO
模拟测试数据库脚本：
view plain
declare @the_date datetime
declare @AID INT
SET @AID=1000144
SET @MyCounter = 0
SET @the_date ='2011-08-01'
WHILE (@MyCounter < 2000)
BEGIN
WAITFOR DELAY '000:00:00'
EXECUTE [CodeDBV1_2].[dbo].[gs_CodeDetail_Add]
@AccountID=@AID
,@AppID=1
,@ModleID=11
,@CodeNum=-150
,@Note='Test'
,@Des='Test'
,@CreateDate=@the_date
,@Creater='System'
,@CreateIP='127.0.0.1'
SET @AID=@AID+1
--SET @the_date =dateadd(d,1,@the_date)
SET @MyCounter = @MyCounter + 1
END
目前做到这种程度，还没具体使用不知道还有什么漏洞！