CREATE PROCEDURE p_fzero_champs

AS
BEGIN

-- This routine updates the F-Zero championship ladder (488) with points from the various F-Zero ladders

DECLARE @UserId int;

-- Step 1 - Ensure there is an entry for each player from all ladders in the champs ladder
DECLARE		List_Cursor CURSOR FORWARD_ONLY FOR
SELECT	 	DISTINCT L.UserId
FROM		t_list_data L 
WHERE		L.listid IN (489,490,491,492,493,494,499) AND L.UserId NOT IN (120435,125584,120199) AND L.UserId NOT IN (SELECT UserId FROM t_List_Data WHERE ListId=488)
OPEN List_Cursor
FETCH NEXT FROM List_Cursor INTO @UserId
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO t_List_Data (ListId,UserId) VALUES (488,@UserId)
	FETCH NEXT FROM List_Cursor INTO @UserId	
END
CLOSE List_Cursor
DEALLOCATE List_Cursor

-- Set all points to NULL to prepare for full update
UPDATE t_List_Data SET intvalue1=0,intvalue2=NULL,intvalue4=0,intvalue5=NULL,intvalue7=0,intvalue8=NULL,intvalue11=0,intvalue12=NULL,intvalue14=0,intvalue15=NULL,intvalue17=0,intvalue18=NULL,intvalue20=0,intvalue21=NULL,intvalue22=0,strvalue1='' WHERE ListId=488

-- Calc scores for all F-Zero ladders
EXEC p_fzero_ranking 489 -- BF Gold/Platinum/Champ Ladder 
EXEC p_fzero_ranking 490 -- BF Bronze/Silver Ladder 
EXEC p_fzero_ranking 491 -- X Ladder 
EXEC p_fzero_ranking 492 -- SNES Ladder 
EXEC p_fzero_ranking 493 -- MV Ladder 
EXEC p_fzero_ranking 494 -- GX Open Ladder 
EXEC p_fzero_ranking 499 -- GX Max Speed Ladder 

-- Set GX best score
UPDATE t_List_Data SET intvalue22=intvalue1 WHERE ListId=488 AND intvalue1<intvalue4
UPDATE t_List_Data SET intvalue22=intvalue4 WHERE ListId=488 AND intvalue22=0
-- Set total BF score
UPDATE t_List_Data SET intvalue23=intvalue17+intvalue20 WHERE ListId=488 
-- Set total points
UPDATE t_List_Data SET ordernum=intvalue22+intvalue7+intvalue11+intvalue14+intvalue23 WHERE ListId=488

END
GO