SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[yaf_user_upgrade](@UserID INT)
AS
BEGIN
    DECLARE @RankID       INT
    DECLARE @Flags        INT
    DECLARE @MinPosts     INT
    DECLARE @NumPosts     INT
    DECLARE @BoardId      INT
    DECLARE @RankBoardId  INT
    -- Get user and rank information
    SELECT @RankID = b.RankID,
           @Flags = b.Flags,
           @MinPosts = b.MinPosts,
           @NumPosts = a.NumPosts,
           @BoardId = a.BoardId
    FROM   [dbo].[yaf_User] a
           INNER JOIN [dbo].[yaf_Rank] b
                ON  b.RankID = a.RankID
    WHERE  a.UserID = @UserID
    
    -- If user isn't member of a ladder rank, exit
    IF (@Flags & 2) = 0
        RETURN
    
    SELECT @RankBoardId = BoardID
    FROM   [dbo].[yaf_Rank]
    WHERE  RankID = @RankID
    
    IF @RankBoardId <> @BoardId
        SELECT TOP 1
               @RankID = RankID
        FROM   [dbo].[yaf_Rank]
        WHERE  BoardId = @BoardId
               AND (Flags & 2) = 2
               AND MinPosts <= @NumPosts
        ORDER BY
               MinPosts DESC
    ELSE
        -- See if user got enough posts for next ladder group
        SELECT TOP 1
               @RankID = RankID
        FROM   [dbo].[yaf_Rank]
        WHERE  BoardId = @BoardId
               AND (Flags & 2) = 2
               AND MinPosts <= @NumPosts
               AND MinPosts > @MinPosts
        ORDER BY
               MinPosts
    
    IF @@ROWCOUNT = 1
        UPDATE [dbo].[yaf_User]
        SET    RankID = @RankID
        WHERE  UserID = @UserID
END

DECLARE @user_id      INT 
DECLARE users_cursor  CURSOR 
FOR
    SELECT [UserID]
    FROM   [dbo].[yaf_User]

OPEN users_cursor
FETCH NEXT FROM users_cursor INTO @user_id
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC [dbo].[yaf_user_upgrade]
         @UserID = @user_id
   
    FETCH NEXT FROM users_cursor INTO @user_id
END
CLOSE users_cursor
DEALLOCATE users_cursor