Margin Pool Stats

Post here to discuss topics relate to the 4.x version for the ASP Football Pool software program. All support topics should go here.
Post Reply
User avatar
fbonani
Posts: 50
Joined: Thu Aug 15, 2019 12:07 pm

Margin Pool Stats

Post by fbonani »

I thought I would start a new topic regarding the Margin Pool Stats. As I stated earlier (Survivor Pool Stats), I want to add the username and score of the margin pool leader and I am almost there. It is actually working, but I have to go in and change the highest score manually. Hopefully some access db guru can tell me what needs to be changed to make this an automatic data pull. Please look at the function GetPlayerMarginScore() portion of the functions to see what I mean about manually entering the data. Here is the code I have in the userLogin.asp file at the moment.
My site is at the following link .

Code: Select all

<%@ LANGUAGE="VBScript" %>
<!-- #include file="includes/config.asp" --><!-- #include file="includes/common.asp" --><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!-- #include file="includes/form.asp" -->
<!-- #include file="includes/passwords.asp" -->
<!-- #include file="includes/side.asp" -->
<%	'Open the database.
	call OpenDb() %>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<%	PageSubTitle = "User Login"
	call BuildPageHeader("", "")

	'If this is not a postback, add client-side script to set form field focus.
	if Request.ServerVariables("Content_Length") = 0 then %>
	<script type="text/javascript">//<![CDATA[
	//=========================================================================
	// If a username is stored in a cookie, preselect it on the form.
	//=========================================================================
	domUtils.onready(function () {

		try {

			// Check for the username cookie.
			var username = domUtils.getCookie("<% = SESSION_USERNAME_KEY %>");
			if (username.length > 0) {

				// Look for a match in the drop down list.
				var selectEl = document.getElementById("username");
				if (selectEl != null) {
					var found = false;
					var i = 0;
					do {
						if (selectEl.options[i].value == username) {
							selectEl.options[i].selected = true;
							found = true;
						}
						else
							i++;
					} while (i < selectEl.options.length && !found);

					// If found, set focus on the password field and exit.
					if (found) {
						document.getElementById("password").focus();
						return;
					}
				}
			}

			// Otherwise, set focus on the drop down list.
			document.getElementById("username").focus();
		}
		catch (ex) {}
	});
	//]]></script>
<%	end if %>
<!-- #include file="includes/custom.asp" -->
</head>
<body>
<!-- #include file="includes/header.asp" -->
<!-- #include file="includes/menu.asp" -->
	<div id="contentSection">
	<table id="mainWrapper" border="0" cellpadding="0" cellspacing="0"><tr><td>
<%	'If there is form data, process it.
	dim username, password
	dim sql, rs
	if Request.ServerVariables("Content_Length") > 0 then
		username = Trim(Request.Form("username"))
		password = Trim(Request.Form("password"))
		if username = "" then
	        FormFieldErrors.Add "username", "Please select your user name and enter your password."
	        FormFieldErrors.Add "password", ""
		elseif password = "" then
			FormFieldErrors.Add "password", "Please enter your password."
		end if

		'If the data is good, check the username and password.
		if FormFieldErrors.Count = 0 then
			sql = "SELECT * FROM Users WHERE Username = '" & SqlEscape(username) & "'"
			set rs = DbConn.Execute(sql)
			if rs.EOF and rs.BOF then
		        FormFieldErrors.Add "username", "Username '" & username & "' not found."
			elseif Hash(rs.Fields("Salt").Value & password) <> rs.Fields("Password").Value then
		        FormFieldErrors.Add "password", "Password is incorrect."
			else
		   		'Save the username and team theme ID in the Session and
				'redirect to the home page.
				Session(SESSION_USERNAME_KEY) = rs.Fields("Username").Value
				Session(SESSION_THEME_KEY)    = rs.Fields("TeamThemeID").Value
				Response.Redirect("./")
			end if
		end if

		'Show error messages.
		call DisplayFormFieldErrorsMessage("Login failed, see below:")

	end if %>
	<form action="<% = Request.ServerVariables("SCRIPT_NAME") %>" method="post">
		<table class="main fixed" border="0" cellpadding="0" cellspacing="0">
			<tr class="header bottomEdge">
				<th align="left">User Login</th>
			</tr>
			<tr>
				<td class="freeForm">
					<p>To login, select your name from the list, enter your password and hit the <code>Login</code> button.
					If you do not already have an account, you can <a href="userSignUp.asp">sign up</a> for one (you can review the <a href="helpRules.asp">pool rules</a> before joining).</p>
					<p>If you forgot your password, you can <a href="requestPassword.asp">Reset it Here</a>.</p>				<table border="0" cellpadding="0" cellspacing="0">
						<tr valign="middle">
							<td><strong>Username:</strong></td>
							<td>
								<select id="username" name="username" class="<% = StyleFormField("", "username") %>">
									<% call DisplayUserSelectList(GetUserList(false), username) %>
								</select>
							</td>
						</tr>
						<tr valign="middle">
							<td><strong>Password:</strong></td>
							<td><input type="password" id="password" name="password" value="" class="<% = StyleFormField("", "password") %>" /></td>
						</tr>
					</table>
					<p><em>Passwords are case-sensitive.</em>
					If you have forgotten your password or cannot log in for whatever reason, contact the <a href="mailto:<% = ADMIN_EMAIL %>">Administrator</a> for help.</p><hr width=100%>
					<b><center>Survivor | Margin Pool Statistics</b></center><table border="1" border color="red" cellpadding="0" cellspacing="0" align="center">
						<tr valign="center" bgcolor="#000">
							<td><span style="color: limegreen"><b>Active: <% = GetActiveCount(GetCurrentWeek()) %></span>  &nbsp;&nbsp;<span style="color: red">Eliminated: <% 
					
					Dim week,numWeeks,lastWeek,totalElim
					lastWeek = GetSurvivorFinalWeek()
					numWeeks = GetWeekCount()
					
					for week = SIDE_START_WEEK to numWeeks						
						if week <= lastWeek then 
							totalElim = totalElim + GetEliminatedCount(week) - GetBuybackCount(week)
						End If
					Next					
					
					Response.Write(totalElim)
				 %></span>
</td><td><span style="color: limegreen"><b>Player: <% = GetPlayerMarginScore() %></span>  &nbsp;&nbsp;<span style="color: limegreen">Score: <% = GetMarginScore() %></tr></table>				 
				 </td>
			</tr>
		</table>
		<p><input type="submit" name="submit" value="Login" class="button" /></p>
	</form>
	</td></tr></table>
	</div>
<!-- #include file="includes/footer.asp" -->
</body>
</html>
<%	'**************************************************************************
	'* Local functions and subroutines.                                       *
	'**************************************************************************

	'--------------------------------------------------------------------------
	' Used to alternate row styles.
	'--------------------------------------------------------------------------
	function SetRowClass()

		SetRowClass = ""
		if alt then
			SetRowClass = "class=""alt"""
		end if
		alt = not alt

	end function

	'--------------------------------------------------------------------------
	' Returns the number of players that were active in the given week.
	'--------------------------------------------------------------------------
	function GetActiveCount(week)

		dim rs, sql

		GetActiveCount = 0

		sql = "SELECT COUNT(*) AS Total FROM SurvivorStatus" _
		   & " WHERE Week = " & week _
		   & " AND WasAlive"
		set rs = DbConn.Execute(sql)
		if not (rs.EOF and rs.BOF) then
			GetActiveCount = rs.Fields("Total").Value
		end if

	end function

	'--------------------------------------------------------------------------
	' Returns the number of players that used a buyback in the given week.
	'--------------------------------------------------------------------------
	function GetBuybackCount(week)

		dim rs, sql

		GetBuybackCount = 0

		sql = "SELECT COUNT(*) AS Total FROM SidePicks" _
		   & " WHERE Week = " & week _
		   & " AND SurvivorBuyback"
		set rs = DbConn.Execute(sql)
		if not (rs.EOF and rs.BOF) then
			GetBuybackCount = rs.Fields("Total").Value
		end if

	end function

	'--------------------------------------------------------------------------
	' Returns the number of players eliminated in the given week.
	'--------------------------------------------------------------------------
	function GetEliminatedCount(week)

		dim rs, sql

		GetEliminatedCount = 0

		sql = "SELECT COUNT(*) AS Total FROM SurvivorStatus" _
		   & " WHERE Week = " & week _
		   & " AND WasAlive AND NOT IsAlive AND NOT IsPending"
		set rs = DbConn.Execute(sql)
		if not (rs.EOF and rs.BOF) then
			GetEliminatedCount = rs.Fields("Total").Value
		end if

	end function

	'--------------------------------------------------------------------------
	' Returns the number of players that are pending in the given week.
	'--------------------------------------------------------------------------
	function GetPendingCount(week)

		dim rs, sql

		GetPendingCount = 0

		sql = "SELECT COUNT(*) AS Total FROM SurvivorStatus" _
		   & " WHERE Week = " & week _
		   & " AND IsPending"
		set rs = DbConn.Execute(sql)
		if not (rs.EOF and rs.BOF) then
			GetPendingCount = rs.Fields("Total").Value
		end if

	end function
	
	'--------------------------------------------------------------------------
	' Returns the highest score of margin players.
	'--------------------------------------------------------------------------
	function GetMarginScore()

		dim rs, sql

		GetMarginScore = 0

		sql = "SELECT Max(MarginScore) AS HighestScore FROM SidePicks" 
			
		set rs = DbConn.Execute(sql)
		if not (rs.EOF and rs.BOF) then
			GetMarginScore = rs.Fields("HighestScore").Value
		end if

	end function

'--------------------------------------------------------------------------
	' Returns player with the highest score in margin pool.
	'--------------------------------------------------------------------------
	function GetPlayerMarginScore()

		dim rs, sql

		GetPlayerMarginScore = 0

		sql = "SELECT Username AS HighestPlayer FROM SidePicks group by Username having max(MarginScore) > 17"  
	
		set rs = DbConn.Execute(sql)
		if not (rs.EOF and rs.BOF) then
			GetPlayerMarginScore = rs.Fields("HighestPlayer").Value
		end if

	end function
	'**************************************************************************
	'* Local class definitions.                                               *
	'**************************************************************************

	'--------------------------------------------------------------------------
	' TeamObj: Holds information for a single team.
	'--------------------------------------------------------------------------
	class TeamObj

		public id
		public name
		public pickCounts, results

		private sub Class_Initialize()

			name  = ""
			redim pickCounts(numWeeks)
			redim results(numWeeks)

		end sub

		private sub Class_Terminate()
		end sub

		public sub setData(teamID)

			dim sql, rs

			'Save the team ID.
			id = teamID

			'Get the result of this team's game for each week and find how
			'many times it was picked.
			dim i
			for i = 0 to numWeeks - 1
				pickCounts(i) = ""
				results(i)    = ""
				if i >= SIDE_START_WEEK - 1 then
					sql = "SELECT Result FROM Schedule" _
					   & " WHERE Week = " & (i + 1) _
					   & " AND (Schedule.VisitorID = '" & teamID & "' OR Schedule.HomeID = '" & teamID & "')"
					set rs = DbConn.Execute(sql)
					if not (rs.EOF and rs.BOF) then
						results(i) = rs.Fields("Result").Value
						sql = "SELECT COUNT(*) AS Total FROM SidePicks, SurvivorStatus" _
						   & " WHERE SidePicks.Week = " & (i + 1) _
						   & " AND SurvivorStatus.Week = " & (i + 1) _
						   & " AND SidePicks.Pick = '" & teamID & "'" _
						   & " AND SidePicks.Username = SurvivorStatus.Username" _
						   & " AND SurvivorStatus.WasAlive"
						set rs = DbConn.Execute(sql)
						if not (rs.EOF and rs.BOF) then
							pickCounts(i) = rs.Fields("Total").Value
						end if
					end if
				end if
			next

		end sub

	end class %>
Post Reply