· 8 years ago · Feb 05, 2018, 03:48 PM
1IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PV_IRGetBadgeLeaderboardData]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
2DROP PROCEDURE [dbo].PV_IRGetBadgeLeaderboardData
3GO
4PRINT 'Creating procedure PV_IRGetBadgeLeaderboardData';
5GO
6
7CREATE PROCEDURE [dbo].[PV_IRGetBadgeLeaderboardData]
8 @BadgeDefinitionID BIGINT,
9 @PageSize INT = 20,
10 @UserGuid NVARCHAR(10) = NULL,
11 @PageIndex BIGINT = 1,
12 @RecordCount BIGINT OUTPUT
13AS
14BEGIN
15
16 DECLARE @ResultsTemp TABLE
17 (
18 FirstName NVARCHAR(200)
19 , LastName NVARCHAR(200)
20 , IsAchieved BIT
21 , Points BIGINT
22 , IsUserResult BIT
23 , RowId INT
24 )
25
26 DECLARE @MaxPoint BIGINT
27
28 SELECT @MaxPoint = MAX(bu.ProgressValue)
29 FROM IRBadgeDefinition bd
30 INNER JOIN IRBadgeCategory bc ON bc.ID = bd.IRBadgeCategoryID
31 INNER JOIN IRBadgeUser bu ON bu.IRBadgeDefinitionID = bd.ID
32 INNER JOIN IRUser u ON u.ID = bu.IRUserID AND u.IsTestUser = 0
33 WHERE @BadgeDefinitionID = bd.ID
34 AND bd.IsDeleted = 0
35 --AND bd.Status = 2 /* Active = 2 */
36 AND bc.Type = 13 /* Game = 13 */
37 --AND bu.ProgressValue > 0
38
39 INSERT INTO @ResultsTemp
40 SELECT
41 u.FirstName,
42 u.LastName,
43 CASE WHEN @MaxPoint = bu.ProgressValue THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS IsAchieved,
44 --CAST(bu.Quantity AS BIT)
45 ISNULL(bu.ProgressValue, 0) AS Points,
46 CASE WHEN u.Guid = @UserGuid THEN 1 ELSE 0 END AS IsUserResult,
47 ROW_NUMBER() OVER (ORDER BY
48 ISNULL(bu.ProgressValue, 0) DESC) as RowId
49 FROM IRBadgeDefinition bd
50 INNER JOIN IRBadgeCategory bc ON bc.ID = bd.IRBadgeCategoryID
51 INNER JOIN IRBadgeUser bu ON bu.IRBadgeDefinitionID = bd.ID
52 INNER JOIN IRUser u ON u.ID = bu.IRUserID AND u.IsTestUser = 0
53 WHERE @BadgeDefinitionID = bd.ID
54 AND bd.IsDeleted = 0
55 --AND bd.Status = 2 /* Active = 2 */
56 AND bc.Type = 13 /* Game = 13 */
57 --AND bu.ProgressValue > 0
58 ORDER BY Points DESC
59
60 SELECT @RecordCount = COUNT(1)
61 FROM @ResultsTemp
62
63 IF ((((@PageIndex - 1) * @PageSize) + 1) > @RecordCount)
64 BEGIN
65 SET @PageIndex = 1
66 END
67
68 DECLARE @Results TABLE
69 (
70 FirstName NVARCHAR(200)
71 , LastName NVARCHAR(200)
72 , IsAchieved BIT
73 , Points BIGINT
74 , IsUserResult BIT
75 , RowId INT
76 )
77
78 INSERT @Results
79 SELECT * FROM @ResultsTemp
80 WHERE RowId BETWEEN (((@PageIndex - 1) * @PageSize) + 1) AND (@PageIndex * @PageSize)
81
82 IF NOT EXISTS (SELECT 1 FROM @Results as r
83 WHERE IsUserResult = 1)
84 AND @UserGuid IS NOT NULL
85 AND LEN(@UserGuid) > 0
86 BEGIN
87 INSERT @Results
88 SELECT rt.FirstName,
89 rt.LastName,
90 rt.IsAchieved,
91 rt.Points,
92 rt.IsUserResult,
93 (SELECT COUNT(1) FROM @Results) +1
94 FROM @ResultsTemp as rt
95 WHERE rt.IsUserResult = 1
96 END
97
98 SELECT *
99 FROM @Results
100
101
102 IF @@ERROR<>0 GOTO ERROR_PROC;
103
104 -----------------------------------------------------
105 -- exit point of the store proc
106 RETURN(0);
107
108 ERROR_PROC:
109 RETURN(99);
110END;
111GO
112--- end PV_IRGetBadgeLeaderboardData