1 -- =============================================
2 -- Author: <华仔>
3 -- Create date: <2016,6,22>
4 -- Description: <玩家游戏财富日志>
5 -- EXEC pr_GameScoreInfoChangeDetail '3','2016-06-22','2016-06-23','-999','CatchFish','96648' -- 捕鱼
6 -- EXEC pr_GameScoreInfoChangeDetail '3','2016-06-22','2016-06-23','-999','CowCow','全部' -- 牛牛
7 -- =============================================
8 ALTER PROCEDURE [dbo].[pr_GameScoreInfoChangeDetail]
9 @时间类型 CHAR(1),
10 @开始时间 CHAR(10),
11 @结束时间 CHAR(10),
12 @平台名称 VARCHAR(20),
13 @游戏名称 VARCHAR(20),
14 @UserID VARCHAR(500)
15 AS
16 BEGIN
17
18
19 SET NOCOUNT ON;
20
21 DECLARE @SQL VARCHAR(MAX),@Joi VARCHAR(500)='',@Par VARCHAR(250);
22
23 SET @Par='
24 WHERE '+
25 CASE @时间类型
26 WHEN 1 THEN 'AAI.RegisterDate BETWEEN '''+@开始时间+''' AND '''+@结束时间+'''' -- 注册时间
27 WHEN 2 THEN 'AAI.LastLogonDate BETWEEN '''+@开始时间+''' AND '''+@结束时间+'''' -- 登陆时间
28 WHEN 3 THEN 'TGSIC.ChangeTime BETWEEN '''+@开始时间+''' AND '''+@结束时间+'''' -- 改变时间
29
30 END
31
32 IF(@平台名称 <> '-999')
33 SET @Joi+='
34 JOIN [dbo].Fn_ParsingMorePar('''+@平台名称+''') AS PLAT ON PLAT.Code <> ''-999'' AND PLAT.Code = AAI.GamePlatId'
35
36 IF(@UserID <> '全部')
37 SET @Joi+='
38 JOIN [dbo].Fn_ParsingMorePar('''+@UserID+''') AS UserID ON UserID.Code <> ''全部'' AND UserID.Code = AAI.UserID'
39
40 IF(@游戏名称 = 'CatchFish')
41 BEGIN
42 SET @SQL='
43 SELECT ''新四海龙王'' AS 游戏名称
44 ,AAI.UserID AS 用户ID
45 ,AAI.GameID AS 游戏ID
46 ,AAI.[NickName] AS 用户昵称
47 ,TGSIC.[ChangeColumn] AS 改变字段
48 ,TGSIC.[ChangeTime] AS 改变时间
49 ,TGSIC.[OldColumn] AS 原来值
50 ,TGSIC.[NewColumn] AS 后来值
51 ,CASE
52 WHEN TGSIC.[OldColumn] > TGSIC.[NewColumn]
53 THEN ''减少''
54 ELSE ''增加''
55 END AS 类型
56 ,ABS(TGSIC.[OldColumn] - TGSIC.[NewColumn]) AS 变动值
57 ,ISNULL(tE.[EventName],TGSIC.[EventInfo]) AS 活动内容
58 ,ISNULL(GRI.ServerName,'''') AS 游戏场合
59 FROM [CatchFish].[dbo].[QT_GameScoreInfoChange] TGSIC WITH(NOLOCK)
60 JOIN [CatchFish].[dbo].[QA_AccountsInfo] AAI WITH(NOLOCK)ON AAI.UserID = TGSIC.ChangeUserID
61 LEFT JOIN [tbEvent] tE WITH(NOLOCK)ON tE.EventInfo = TGSIC.EventInfo AND tE.Project = ''CatchFish''
62 OUTER APPLY
63 (
64 SELECT TOP 1 TGSLL.ServerID,TGSLL.KindID
65 FROM [CatchFish].[dbo].QT_GameScoreLockerLog TGSLL
66 WHERE TGSLL.UserID = TGSIC.ChangeUserID AND TGSLL.CollectDate <= TGSIC.ChangeTime
67 AND tE.EventName = ''游戏写分''
68 ORDER BY TGSLL.CollectDate DESC
69 )ApDraw
70 LEFT JOIN [tbGameRoomInfo] GRI WITH(NOLOCK)ON GRI.ServerID = ApDraw.ServerID AND GRI.KindID = ApDraw.KindID
71
72 '+@Joi+@Par+'
73 ORDER BY 游戏名称,改变时间'
74 END
75
76 IF(@游戏名称 = 'CowCow')
77 BEGIN
78 SET @SQL='
79 SELECT ''疯狂牛牛'' AS 游戏名称
80 ,AAI.UserID AS 用户ID
81 ,AAI.GameID AS 游戏ID
82 ,AAI.[NickName] AS 用户昵称
83 ,TGSIC.[ChangeColumn] AS 改变字段
84 ,TGSIC.[ChangeTime] AS 改变时间
85 ,TGSIC.[OldColumn] AS 原来值
86 ,TGSIC.[NewColumn] AS 后来值
87 ,CASE
88 WHEN TGSIC.[OldColumn] > TGSIC.[NewColumn]
89 THEN ''减少''
90 ELSE ''增加''
91 END AS 类型
92 ,ABS(TGSIC.[OldColumn] - TGSIC.[NewColumn]) AS 变动值
93 ,ISNULL(tE.[EventName],TGSIC.[EventInfo]) AS 活动内容
94 ,ISNULL(GRI.ServerName,'''') AS 游戏场合
95 FROM [CowCow].[dbo].[QT_GameScoreInfoChange] TGSIC WITH(NOLOCK)
96 JOIN [CowCow].[dbo].[QA_AccountsInfo] AAI WITH(NOLOCK)ON AAI.UserID = TGSIC.ChangeUserID
97 LEFT JOIN [tbEvent] tE WITH(NOLOCK)ON tE.EventInfo = TGSIC.EventInfo AND tE.Project = ''CowCow''
98 OUTER APPLY
99 (
100 SELECT TOP 1 TGSLL.ServerID,TGSLL.KindID
101 FROM [CowCow].[dbo].QT_GameScoreLockerLog TGSLL
102 WHERE TGSLL.UserID = TGSIC.ChangeUserID AND TGSLL.CollectDate <= TGSIC.ChangeTime
103 AND tE.EventName = ''游戏写分''
104 ORDER BY TGSLL.CollectDate DESC
105 )ApDraw
106 LEFT JOIN [tbGameRoomInfo] GRI WITH(NOLOCK)ON GRI.ServerID = ApDraw.ServerID AND GRI.GameID = ApDraw.KindID
107
108 '+@Joi+@Par+'
109 ORDER BY 游戏名称,改变时间'
110 END
111
112 --PRINT @SQL
113 EXEC(@SQL)
114
115 END
