DECLARE
		@TrackID int,
		@TrackDBID varchar(50),
		@State varchar(4),
		@TrackName varchar(50),
		@PointsTitle varchar(50),
		@TrackPoints int,
		@RiderPoints int,
		@ARCPoints int,
		@AvgRiders int,
		@TotalRiders int,
		@TrackNumSingles int,
		@RenewalYear int,
		@SingleMultiplier int,
		@Section varchar(50),
		@SectionPoints int,
		@MemberDBID varchar(50),
		@MemberID varchar(50),
		@ProficiencyName varchar(75),
		@MemberType varchar(75),
		@FirstName varchar(50),
		@LastName varchar(50),
		@SinglesAtTrack int,
		@SinglesTotal int,
		@DateBegin datetime,
		@AppDate datetime,
		@MoveUpDate datetime;
		
		
SET NOCOUNT ON


-- Cursor to get the current tracks --
DECLARE cursorTracks CURSOR FAST_FORWARD FOR
	SELECT Track_DBID,Track_Number,Track_Nick_Name,State FROM Tracks Trk
	INNER JOIN Member_Address_Book Addr ON Trk.Track_SN=Addr.Contact_SN
	WHERE Trk.Active=1 AND Trk.Track_Number=1357
	ORDER BY State,Track_Number

	OPEN cursorTracks
	FETCH NEXT FROM cursorTracks INTO @TrackDBID,@TrackID,@TrackName,@State
	WHILE @@FETCH_STATUS=0
		BEGIN
			-- Set ARCPoints = 0 for tracks that haven't opened yet --
			SET @ARCPoints = 0
			-- Set TrackPoints = 0 for the next track --
			SET @TrackPoints = 0
			
			-- Cursor to get riders that credited membership to the track --
			DECLARE cursorTrackRiders CURSOR FAST_FORWARD FOR
			
				SELECT DISTINCT(Mbr.Member_DBID),Mbr.Member_SN,Addr.First_Name,Addr.Last_Name,Mbr.Date_Begin,Mbr.Date_Application,Lic.Date_Moveup FROM Members Mbr
				INNER JOIN Member_Address_Book Addr ON Mbr.Member_SN = Addr.Contact_SN
				INNER JOIN Tracks Trk ON Mbr.Credited_Track_ID=Trk.Track_DBID
				INNER JOIN Member_Licenses Lic ON Lic.Member_ID=Mbr.Member_DBID
				WHERE (Mbr.Date_Stamp > '12/21/2013' OR Mbr.Date_Application > '01/01/2014') AND Trk.Track_Number=@TrackID 
				AND Lic.License_Proficiency_Class_ID=1 AND Mbr.Membership_Type_ID IN (3,4,5,2,6)
				ORDER BY Mbr.Date_Application,Addr.Last_Name,Addr.First_Name

			OPEN cursorTrackRiders
			FETCH NEXT FROM cursorTrackRiders INTO @MemberDBID,@MemberID,@FirstName,@LastName,@DateBegin,@AppDate,@MoveUpDate
			WHILE @@FETCH_STATUS=0
				BEGIN
					SET @RiderPoints = 5
					
					-- Grab the proficiency of the rider for 20", as doing it in the cursor returned multiple rows for one rider --
					SELECT @ProficiencyName = P2.Proficiency_Name FROM Member_Licenses L2
					INNER JOIN Ref.Proficiency_Codes P2 ON L2.Proficiency_Code=P2.Proficiency_Code
					WHERE L2.Proficiency_Code IN ('N','I','E','G','Y') AND P2.Proficiency_Sanction_ID=1 AND L2.Member_ID = @MemberDBID 
					
					-- Only make the trip in here if they are not Strider or A Pro --
					IF @ProficiencyName NOT IN ('Strider','A Pro')
						BEGIN
							
							-- Figure out if this rider is going to give move-up points to track --
							IF @MoveUpDate BETWEEN '01/01/2014'  AND '12/16/2014'
								BEGIN
									IF @ProficiencyName IN ('Inter','Girls')
										SET @RiderPoints = @RiderPoints + 25
									IF @ProficiencyName = 'Expert'
										SET @RiderPoints = @RiderPoints + 50	
								END
							-- This will clear out move-up date for riders that didn't advance this year --
							IF @MoveUpDate < '01/01/2014'
								SET @MoveUpDate = ''
								
							-- Set the type of membership and the year of membership --
							IF @DateBegin = @AppDate
								BEGIN
									SET @MemberType = 'NEW - ' + @ProficiencyName
									SET @SingleMultiplier = 5
									
									-- Only execute this code for FULL members --
									IF @ProficiencyName <> 'Strider'
										BEGIN
											-- For new riders, the singles MUST be at the hometrack --
											SELECT @SinglesAtTrack = COUNT(DISTINCT(Schedule.Date_Race)) FROM Track_Result_Summaries Result
											INNER JOIN Track_Schedules Schedule ON Result.Track_Result_Summary_DBID = Schedule.Track_Schedule_DBID
											INNER JOIN Track_Result_Age_Classes Age ON Result.Track_Result_Summary_DBID = Age.Track_Result_Summary_ID
											INNER JOIN Track_Result_Motos Moto ON Age.Track_Result_Age_Class_DBID = Moto.Track_Result_Age_Class_ID
											INNER JOIN Track_Result_Finishes Finish ON Finish.Track_Result_Moto_ID = Moto.Track_Result_Moto_DBID
											INNER JOIN Tracks ON Schedule.Track_ID = Tracks.Track_DBID
											INNER JOIN Member_Licenses Lic ON Finish.License_ID=Lic.Member_License_DBID
											WHERE DATEPART(YYYY,Schedule.Date_Race) = '2014' AND Schedule.Point_Value_ID=1 AND Tracks.Track_Number=@TrackID
											AND Lic.Member_ID=@MemberDBID
										END
									
								END
							ELSE
								BEGIN
									-- If it is a Novice rider who renews in less than 180 days, consider them new --
									IF DATEDIFF(dd,@AppDate,@DateBegin)< 180 AND @ProficiencyName = 'Novice'
										BEGIN
											SET @MemberType = 'NEW - ' + @ProficiencyName
											SET @SingleMultiplier = 5
											
											-- Only execute this code for FULL members --
											IF @ProficiencyName <> 'Strider'
												BEGIN
													-- For new riders, the singles MUST be at the hometrack --
													SELECT @SinglesAtTrack = COUNT(DISTINCT(Schedule.Date_Race)) FROM Track_Result_Summaries Result
													INNER JOIN Track_Schedules Schedule ON Result.Track_Result_Summary_DBID = Schedule.Track_Schedule_DBID
													INNER JOIN Track_Result_Age_Classes Age ON Result.Track_Result_Summary_DBID = Age.Track_Result_Summary_ID
													INNER JOIN Track_Result_Motos Moto ON Age.Track_Result_Age_Class_DBID = Moto.Track_Result_Age_Class_ID
													INNER JOIN Track_Result_Finishes Finish ON Finish.Track_Result_Moto_ID = Moto.Track_Result_Moto_DBID
													INNER JOIN Tracks ON Schedule.Track_ID = Tracks.Track_DBID
													INNER JOIN Member_Licenses Lic ON Finish.License_ID=Lic.Member_License_DBID
													WHERE DATEPART(YYYY,Schedule.Date_Race) = '2014' AND Schedule.Point_Value_ID=1 AND Tracks.Track_Number=@TrackID
													AND Lic.Member_ID=@MemberDBID
												END
										
										END
									ELSE
										BEGIN
											SET @MemberType = 'RENEWAL - ' + @ProficiencyName
											SET @RenewalYear = DATEDIFF(yy,@DateBegin,@AppDate)
									
											-- Figure out the multiplier for renewal singles --
											IF @RenewalYear < 1
												SET @RenewalYear = 1
											IF @RenewalYear = 1
												SET @SingleMultiplier = 1
											IF @RenewalYear = 2
												SET @SingleMultiplier = 3
											IF @RenewalYear > 2
												SET @SingleMultiplier = 5
											-- Only execute for FULL Members
											IF @ProficiencyName <> 'Strider'
												BEGIN
													-- For renewals, the singles can be at ANY track --
													SELECT @SinglesAtTrack=COUNT(DISTINCT(Schedule.Date_Race)) FROM Track_Result_Summaries Result
													INNER JOIN Track_Schedules Schedule ON Result.Track_Result_Summary_DBID = Schedule.Track_Schedule_DBID
													INNER JOIN Track_Result_Age_Classes Age ON Result.Track_Result_Summary_DBID = Age.Track_Result_Summary_ID
													INNER JOIN Track_Result_Motos Moto ON Age.Track_Result_Age_Class_DBID = Moto.Track_Result_Age_Class_ID
													INNER JOIN Track_Result_Finishes Finish ON Finish.Track_Result_Moto_ID = Moto.Track_Result_Moto_DBID
													INNER JOIN Tracks ON Schedule.Track_ID = Tracks.Track_DBID
													INNER JOIN Member_Licenses Lic ON Finish.License_ID=Lic.Member_License_DBID
													WHERE DATEPART(YYYY,Schedule.Date_Race) = '2014' AND Schedule.Point_Value_ID=1 AND Lic.Member_ID=@MemberDBID
												END	
										END
				
								-- Calculate SinglesTotal, which is the points earned from singles --
								IF @SinglesAtTrack > 4
									SET @SinglesTotal = @SingleMultiplier * 5
								IF @SinglesAtTrack BETWEEN 1 AND 4
									SET @SinglesTotal = @SingleMultiplier * @SinglesAtTrack
								
								-- Add the SingleTotal into the RiderPoints before insertion into table --
								SET @RiderPoints = @RiderPoints + @SinglesTotal
								
							END
							
						-- And the RiderPoints to the TrackPoints --
						SET @TrackPoints = @TrackPoints + @RiderPoints
						
						-- Some DEBUG PRINT code to see what is happening --
						PRINT 'Working on ' + @TrackName + ' for ' + @LastName + ',' + @FirstName + ' -  Total Points=' + CONVERT(varchar(10),@TrackPoints)
						
						-- Insert rider values into the #temp table --
							INSERT INTO #tblTrackPoints(SortOrder,TrackState,TrackID,TrackName,SerialNum,LastName,FirstName,DateBegin,AppDate,MoveUpDate,Singles,Points)
							VALUES(4,'','',@MemberType,@MemberID,@LastName,@FirstName,@DateBegin,@AppDate,@MoveUpDate,@SinglesAtTrack,@RiderPoints)
						
						-- Reset the variables for the next rider --	
						SET @RiderPoints = 0
						SET @SinglesTotal = 0
						SET @ProficiencyName = ''
						
					FETCH NEXT FROM cursorTrackRiders INTO @MemberDBID,@MemberID,@FirstName,@LastName,@DateBegin,@AppDate,@MoveUpDate
				END

			CLOSE cursorTrackRiders
			DEALLOCATE cursorTrackRiders
			
			-- Compute the ARC score and add that into the track points total --
			SELECT @TotalRiders=SUM(Result.Finishes_Count_Riders_Total_Ama), @TrackNumSingles=COUNT(Schedule.Date_Race)
			FROM Track_Result_Summaries Result
			INNER JOIN Track_Schedules Schedule ON Result.Track_Result_Summary_DBID = Schedule.Track_Schedule_DBID
			INNER JOIN Tracks ON Schedule.Track_ID = Tracks.Track_DBID
			INNER JOIN Member_Address_Book Addr ON Tracks.Track_SN = Addr.Contact_SN
			WHERE DATEPART(YYYY,Schedule.Date_Race) = '2014' AND Schedule.Point_Value_ID=1 AND Tracks.Track_Number=@TrackID
			
			IF @TrackNumSingles > 0
				SET @AvgRiders = @TotalRiders/@TrackNumSingles
			ELSE
				SET @AvgRiders = 0
			
			IF @AvgRiders > 99
				SET @ARCPoints = (((@AvgRiders * .1) * @TrackNumSingles) * .1)
			ELSE
				SET @ARCPoints = (((@AvgRiders * (@AvgRiders * .01)) * @TrackNumSingles) * .1)
			
			-- Add the ARCPoints to the TrackPoints total --
			SET @TrackPoints = @TrackPoints + @ARCPoints
			
			-- Insert track values into the #temp table --
			INSERT INTO tmpTrackPoints(SortOrder,TrackState,TrackID,TrackName,SerialNum,LastName,FirstName,DateBegin,AppDate,Singles,Points)
			VALUES(1,@State,@TrackID,@TrackName,'','','','','','',@TrackPoints)
			
			-- Insert the ARC row into the #temp table --
			INSERT INTO tmpTrackPoints(SortOrder,TrackState,TrackID,TrackName,SerialNum,LastName,FirstName,DateBegin,AppDate,Singles,Points)
			VALUES(2,'','','SINGLES',@TrackNumSingles,'','ARC',@AvgRiders,'','ARC POINTS',@ARCPoints)
			
			-- Insert the header row for the track into the #temp table --
			INSERT INTO tmpTrackPoints(SortOrder,TrackState,TrackID,TrackName,SerialNum,LastName,FirstName,DateBegin,AppDate,Singles,Points)
			VALUES(3,'','','','Serial #','LastName','FirstName','Date Began','App Date','# of Singles','Points')

		FETCH NEXT FROM cursorTracks INTO @TrackDBID,@TrackID,@TrackName,@State
	END
CLOSE cursorTracks
DEALLOCATE cursorTracks