P2wzh4kx

1
2IF EXISTS (SELECT * from dbo.sysobjects where id = object_id(N'[api].[prc_RinhVangNoHu_GetTopUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
3DROP PROCEDURE [api].[prc_RinhVangNoHu_GetTopUser]
4GO
5
6CREATE PROCEDURE api.prc_RinhVangNoHu_GetTopUser
7	@GameID INT,
8	@NumberTop int,
9	@UserId int
10AS
11BEGIN	
12	DECLARE @v_sql1 nvarchar(500);	
13	DECLARE @RatioCard INT;
14	SELECT @RatioCard = RatioCard FROM [dbo].[Event_RinhVangNoHu_EventConfig] WITH(NOLOCK) WHERE GameID = @GameID;
15	IF(@GameID > 0) BEGIN
16		Set @v_sql1 = 'SELECT TOP('+Convert(varchar,@NumberTop)+')
17				  b.UserID,(b.PointBetting+b.PointCard) AS TotalCoinWin
18			FROM Event_RinhVangNoHu_RankingTable b with(nolock) WHERE GameID ='+ Convert(varchar,@GameID) ;
19		
20		IF(@UserId > 0)
21			SET @v_sql1 = @v_sql1 + ' AND b.UserID ='+Convert(varchar,@UserId); 
22		SET @v_sql1 = @v_sql1 + ' ORDER BY TotalCoinWin DESC';		
23		
24		Exec (@v_sql1);
25	END
26	IF(@GameID < 0) BEGIN
27		Set @v_sql1 = 'SELECT TOP('+Convert(varchar,@NumberTop)+')
28				  b.UserID,SUM(b.PointBetting+b.PointCard) AS TotalCoinWin
29			FROM Event_RinhVangNoHu_RankingTable b with(nolock)  WHERE GameID NOT IN(45,209)';
30		
31		IF(@UserId > 0)
32			SET @v_sql1 = @v_sql1 + ' WHERE b.UserID ='+Convert(varchar,@UserId); 
33		SET @v_sql1 = @v_sql1 + ' GROUP BY b.UserID ORDER BY TotalCoinWin DESC';	
34		
35		Exec (@v_sql1);
36		
37	END
38	ELSE BEGIN
39		/*
40	
41		Set @v_sql1 = 'SELECT TOP('+Convert(varchar,@NumberTop)+')
42				  b.UserID,SUM(b.PointBetting+b.PointCard) AS TotalCoinWin
43			FROM Event_RinhVangNoHu_RankingTable b with(nolock) ';
44		
45		IF(@UserId > 0)
46			SET @v_sql1 = @v_sql1 + ' WHERE b.UserID ='+Convert(varchar,@UserId); 
47		SET @v_sql1 = @v_sql1 + ' GROUP BY b.UserID ORDER BY TotalCoinWin DESC';	*/
48		
49		DECLARE @temp_table Table (UserID INT, TotalCoinWin MONEY);
50		
51		INSERT INTO @temp_table
52					SELECT l.UserID, ((l.CoinWin*100/l.RatioFee)/0.05) AS TotalCoinWin
53					FROM Event_RinhVangNoHu_LogCoinWin l  with(nolock)
54					WHERE l.GameID NOT IN(45,209);
55		IF(@UserId > 0) BEGIN
56			SELECT  TOP(@NumberTop) UserID, floor(SUM(TotalCoinWin)/1000) AS TotalCoinWin FROM @temp_table WHERE UserID=@UserId
57			GROUP BY UserID ORDER BY TotalCoinWin DESC;
58		END
59		ELSE BEGIN
60			SELECT TOP(@NumberTop) UserID, floor(SUM(TotalCoinWin)/1000) AS TotalCoinWin FROM @temp_table 
61			GROUP BY UserID ORDER BY TotalCoinWin DESC;
62		END
63	
64	END
65	
66	--Exec (@v_sql1);
67		
68END
69GO