用户信息记录脚本

用户积分变操作记录脚本UserChangeInfo.sql，作用于WHJHTreasureDB表创建用户积分变化表NET_PM_UserChangeInfo。

脚本内容：

USE WHJHTreasureDB
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO

--用/户/金/币/变/化/表
CREATE proc NET_PM_UserChangeInfo

 @dwGameID INT,      --GameID
 @dwRowNumber INT    --行号

WITH ENCRYPTION AS

-- 属性设置
SET NOCOUNT ON
begin
--声明临时表
declare @t table(GameID int,TransDate datetime,type int,ScoreBalance decimal(18,2),***Balance decimal(18,2),BalanceChange decimal(18,2),Balance decimal(18,2),KindID int,GameName nvarchar(50),ip nvarchar(50),EnterTime datetime,LeaveTime datetime)
--插/入/线/上/充/值/表 type=1
 insert into @t(GameID,TransDate,type,BalanceChange)
 select GameID,PayDate,1,Diamond from WHJHTreasureDB.dbo.OnlinePayOrder   where Gameid=@dwGameID and OrderStatus=1
 --插/入/线/下/充/值/扣/除/表 type=2
 insert into @t(GameID,TransDate,type,BalanceChange)
 select GameID,createDate,2,CONVERT(decimal(18,2),amount)/100 from WHJHTreasureDB.dbo.OfflineRecord where GameID=@dwGameID
 --插/入/提/现/记/录 type=3
 insert into @t(GameID,TransDate,type,BalanceChange)
 select b.GameID,a.ApplyTime,3,CONVERT(decimal(18,2),a.Money)/100*-1 from WHJHTreasureDB.dbo.GameScoreDraw a inner join WHJHAccountsDB.dbo.Accountsinfo b on a.UserId=b.UserID where b.GameID=@dwGameID 
  --插/入/提/现/退/回/记/录 type=9
 insert into @t(GameID,TransDate,type,BalanceChange)
 select b.GameID,a.UpdateTime,9,CONVERT(decimal(18,2),a.Money)/100 from WHJHTreasureDB.dbo.GameScoreDraw a inner join WHJHAccountsDB.dbo.Accountsinfo b on a.UserId=b.UserID where b.GameID=@dwGameID and a.Status =-1
 --插/入/银/行/存/款/记/录 type=4
 insert into @t(GameID,TransDate,type,ScoreBalance,***Balance,BalanceChange,Balance)
 Select a.GameID,b.CollectDate,4,CONVERT(decimal(18,2),b.SourceGold)/100,CONVERT(decimal(18,2),b.Source***)/100,CONVERT(decimal(18,2),b.SwapScore)/100*-1,(CONVERT(decimal(18,2),b.SourceGold)-CONVERT(decimal(18,2),b.SwapScore))/100 from WHJHTreasureDB.dbo.RecordInsure b inner join WHJHAccountsDB.dbo.AccountsInfo a on b.SourceUserID=a.UserID where a.GameID=@dwGameID and TradeType=1
 --插/入/银/行/取/款/记/录 type=5
 insert into @t(GameID,TransDate,type,ScoreBalance,***Balance,BalanceChange,Balance)
 Select a.GameID,b.CollectDate,5,CONVERT(decimal(18,2),b.SourceGold)/100,CONVERT(decimal(18,2),b.Source***)/100,CONVERT(decimal(18,2),b.SwapScore)/100,(CONVERT(decimal(18,2),b.SourceGold)+CONVERT(decimal(18,2),b.SwapScore))/100 from WHJHTreasureDB.dbo.RecordInsure b inner join WHJHAccountsDB.dbo.AccountsInfo a on b.SourceUserID=a.UserID where a.GameID=@dwGameID and TradeType=2
 --插/入/注/册/送/金/币/表 type=6
 insert into @t(GameID,TransDate,type,ScoreBalance,***Balance,BalanceChange,Balance)
 Select b.GameID,a.CollectDate,6,CONVERT(decimal(18,2),a.PreScore)/100,CONVERT(decimal(18,2),a.PreInsureScore)/100,CONVERT(decimal(18,2),a.PresentScore)/100,(CONVERT(decimal(18,2),a.PreScore)+CONVERT(decimal(18,2),a.PresentScore))/100 from WHJHTreasureDB.dbo.RecordPresentInfo a inner join WHJHAccountsDB.dbo.Accountsinfo b on a.UserID=b.UserID where b.GameID=@dwGameID
 --插/入/绑/手/机送//金/币/表 type=7
 insert into @t(GameID,TransDate,type,BalanceChange)
 Select b.GameID,a.CollectDate,7,CONVERT(decimal(18,2),a.PresentScore)/100 from WHJHTreasureDB.dbo.ScorePresentInfo a inner join WHJHAccountsDB.dbo.AccountsInfo b on a.UserID=b.UserID where b.GameID=@dwGameID
 --插/入/游/戏/输/赢/表 type=8
 insert into @t(GameID,TransDate,type,ScoreBalance,***Balance,BalanceChange,KindID,GameName,ip,EnterTime,LeaveTime)
 Select b.GameID,a.EnterTime,8,CONVERT(decimal(18,2),a.EnterScore)/100,CONVERT(decimal(18,2),a.EnterInsure)/100,CONVERT(decimal(18,2),a.Score)/100,c.KindID,c.KindName,a.EnterClientIP,a.EnterTime,a.LeaveTime from WHJHTreasureDB.dbo.RecordUserInout a inner join WHJHAccountsDB.dbo.AccountsInfo b on a.UserID=b.UserID inner join WHJHPlatformDB.dbo.GameKindItem c on a.KindID=c.KindID where b.GameID=@dwGameID

select top 20 o.*,(Select COUNT(*) from @t) as DataAmount from 
(select row_number() over(order by TransDate desc) as rownumber,* from(Select * from @t) as oo ) as o where rownumber>@dwRowNumber;

return 0

END
GO

END！