关于报表开发代码示例 

  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
View Code

 

posted @ 2016-06-23 15:53  C-华仔  阅读(323)  评论(0)    收藏  举报