<?php
include_once("settings.inc");

function getDBLink($func)
{
GLOBAL	$dbserver, $dbuser, $dbpwd;

	$link = mysql_connect ($dbserver, $dbuser, $dbpwd) or 
		die ("Could Not Connect In function [$func]");

	// print ("creating link for [$func] link resource is $link</b><br>");

	return $link;
}

function userIsLoggedIn()
{
global $HTTP_COOKIE_VARS, $loggedInCookie;

  if ($HTTP_COOKIE_VARS[$loggedInCookie] == null
	 || $HTTP_COOKIE_VARS[$loggedInCookie] == "" )
	
	return false;

  else
	return true;
}

function clearLoginInfo()
{
GLOBAL $HTTP_COOKIE_VARS, $loggedInCookie;

        // clear out log in cookie
    setcookie ($loggedInCookie, "", time() - 3600);

      // we don't have a current user anymore
    $CURRENT_USER = array();
}


/**************************************************************
 * these are functions that query the database only, i.e.  SELECTS only
 * when you need data from the database, you may want to modify these
 * functions instead of writing new ones, if there is one is close enough
 * the way these retrievals work is to poplulate an array with the data
 * elements by name (usually a different name from the db column name
 * that allows for changing the queries without breaking existing code.
 * performance arguments will come into play here, but we don't need to 
 * be performant.
 *
 * Note that the design philosophy is to put the result into the passed
 * in array. Let the application do whatever the it wants with the results
 * this way the app is not bound to the database design or query design.
 * we can chane queries to be more performant (or add additional info if
 * new requirements come up) without jeopardizing the application integrity
 *
 * the idea is to have a few functions as possible to reduce the 
 * maintenance of the site.
***********************************************************************/

/*****************************************************************
 * getGameInfo	returns game information for a specific gameId.
 *
 * params:
 *	$gameId		the game id in question
 *	$gameArray	the array to put results in
*******************************************************************/
function getGameInfo($gameId, $gameArray)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $database;

	$teamNames	= array();
	getTeamName(null, &$teamNames);

	$link = getDBLink("getTeamGames");

	$q = "SELECT g.id, g.game_day, g.home_team_id, g.away_team_id,"
		. "  g.field, g.home_score, g.away_score "
		. " from $database.games g "
		. " where id = $gameId";

	$result = mysql_query($q, $link)
		or die("Query Failed In getTeamGames:[" . $q . "]");


   while ($row = mysql_fetch_array ($result))
   {

		$gameArray["id"]		= $row[0];
		$gameArray["date"]		= $row[1];
		
		$homeTeam		=	$teamNames[$row[2]];
		$gameArray["homeTeamId"]	= $row[2];
		$gameArray["homeTeam"]	= $homeTeam;

		$awayTeam		= 	$teamNames[$row[3]];
		$gameArray["awayTeamId"]	= $row[3];
		$gameArray["awayTeam"]	= $awayTeam;

		$gameArray["field"]	   	= $row[4];
		$gameArray["homeScore"] 	= $row[5];
		$game["awayScore"] 	= $row[6];

	}
		
	mysql_free_result ($result);
	mysql_close ($link);
}
/*****************************************************************
 * getTeamGames	returns game information for a specific team.
 *
 * params:
 *	$team		the team id in question
 *	$played		a flag that says if we want games that were
 *			played or not (scheduled games)
 *	$gameArray	the array to put results in
*******************************************************************/
function getTeamGames($team, $played, $gameArray)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $database;

	$teamNames	= array();
	getTeamName(null, &$teamNames);

	$link = getDBLink("getTeamGames");

	$q = "SELECT g.id, g.game_day, g.home_team_id, g.away_team_id,"
		. "  g.field, g.home_score, g.away_score, g.resched_date "
		. " from $database.games g "
		. " where ";

	if ($team != null)
		$q .= " (g.home_team_id = $team or g.away_team_id = $team )";
	else
		$q .= " 1 = 1 "; // dirty hack to get proper sql syntax. see concats below

		// $played indicates if user wants list of played games
		// or unplayed games $played may be null which means get all games
	if ($played == false)
		$q .= " and (g.home_score = 0 and g.away_score = 0) ";
	else if ($played == true)
		$q .= " and (g.home_score != 0 or g.away_score != 0) ";

	$q .= " order by g.game_day";

	$result = mysql_query($q, $link)
		or die("Query Failed In getTeamGames:[" . $q . "]");

   $aCount		= 0;

   while ($row = mysql_fetch_array ($result))
   {
		$game	= array();

		$game["id"]		= $row[0];
		$game["date"]		= $row[1];
		
		$homeTeam		=	$teamNames[$row[2]];
		$game["homeTeamId"]	= $row[2];
		$game["homeTeam"]	= $homeTeam;

		$awayTeam		= 	$teamNames[$row[3]];
		$game["awayTeamId"]	= $row[3];
		$game["awayTeam"]	= $awayTeam;

		$game["field"]	   	= $row[4];
		$game["homeScore"] 	= $row[5];
		$game["awayScore"] 	= $row[6];

		$game["reschedDate"] 	= $row[7];

		$gameArray[$aCount]	= $game;

		$aCount	+= 1;
	}
		
	mysql_free_result ($result);
	mysql_close ($link);
}

/*****************************************************************
 * getDivisionGames	returns game information for a specific division.
 *			See Also getTeamGames
 *
 * params:
 *	$division	indicates the division in question
 *	$played		a flag that says if we want games that were
 *			played or not (scheduled games)
 *	$gameArray	the array to put results in
*******************************************************************/

function getDivisionGames($division, $played, $gameArray)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $database;

	$teamNames	= array();
	getTeamName(null, &$teamNames);

	$q = "SELECT g.id, g.game_day, g.home_team_id, g.away_team_id,"
		. "  g.field, g.home_score, g.away_score "
		. " from $database.team t, $database.games g "
		. "where t.division = '$division' "
		. " and g.home_team_id = t.id";

		// $played indicates if user wants list of played games
		// or unplayed games
	if ($played == false)
		$q .= " and (g.home_score = 0 and g.away_score = 0) ";
	else
		$q .= " and (g.home_score != 0 or g.away_score != 0) ";

	$q .= " order by g.game_day";

	$link = getDBLink("getDivisionGames");
	
	$result = mysql_query($q, $link)
		or die("Query Failed In getDivisionGames:[" . $q . "]");

   $aCount		= 0;

   while ($row = mysql_fetch_array ($result))
   {
		$game	= array();

		$game["id"]		= $row[0];
		$game["date"]		= $row[1];
		
		$homeTeam	=  $teamNames[$row[2]];
		$game["homeTeamId"]	= $row[2];
		$game["homeTeam"]	= $homeTeam;

		$awayTeam	= $teamNames[$row[3]];
		$game["awayTeamId"]	= $row[3];
		$game["awayTeam"]	= $awayTeam;

		$game["field"]	   = $row[4];
		$game["homeScore"] = $row[5];
		$game["awayScore"] = $row[6];

		$gameArray[$aCount]	= $game;

		$aCount	+= 1;
	}
		
	mysql_free_result ($result);
	mysql_close ($link);
}

/*****************************************************************
 * getTeamDivision	returns the division that a team plays in
 *
 * $teamId	the team in question
 *
 * returns string holding the team's division
******************************************************************/
function getTeamDivision($teamId)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $database;

	$link = getDBLink("getTeamDivision");
	
	$q ="SELECT t.division
		from  $database.team t
		where t.id = \"" . $teamId . "\"";

	$result = mysql_query ($q, $link) or die("Team Query Failed [" . $q . "]");

	$division	= "";
		// there should be only be one result row
	while ($row = mysql_fetch_array ($result))
	{
		$division	= $row[0];
	}
		
	mysql_free_result ($result);
	mysql_close ($link);

	return $division;
}

/*****************************************************************
 * getTeamName	returns the name of a team based on id
 *
 * $teamId	the team id in question
 *
 * returns string indicating the team's name
******************************************************************/
function getTeamName($teamId, $teamNameArray = null)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $database;

	$link = getDBLink("getTeamName");
	
	$q ="SELECT t.name, t.id
		from  $database.team t ";

	if ($team != null)
		$q	.= " where t.id = \"" . $teamId . "\"";

	$result = mysql_query ($q, $link) or die("Team Query Failed [" . $q . "]");

		// there may be only be one result row
	while ($row = mysql_fetch_array ($result))
	{
		$name	= $row[0];

		$teamNameArray[$row[1]]	= $name;
	}
		
	mysql_free_result ($result);
	mysql_close ($link);

	return $name;
}
/*****************************************************************
 * getPlayerTeamInfo	returns the team info for specified player
 *
 * $playerId	the player id in question
 * $player	array to put the results into
******************************************************************/

function getPlayerTeamInfo($playerId, $player)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $database;

	$link = getDBLink("getPlayerTeamInfo");
	
	$q ="SELECT t.id, t.division,  r.isCaptain
		from  $database.player p, $database.team t, $database.roster r
		where p.id = r.player_id
		and r.team_id = t.id
		and p.id = \"" . $playerId . "\"";

	$result = mysql_query ($q, $link) or die("Player Query Failed [" . $q . "]");

		// there c$may be only be one result row
	while ($row = mysql_fetch_array ($result))
	{
		if($row[1] == "A")
		{
			$player["aLeagueTeamId"]		= $row[0];
			$player["isALeagueCaptain"]		= $row[2];
		}
		else if ($row[1] == "Draft")
		{
			$player["draftLeagueTeamId"]		= $row[0];
			$player["isDraftLeagueCaptain"]		= $row[2];
		}
	}
		
	mysql_free_result ($result);
	mysql_close ($link);
}
/*****************************************************************
 * getPlayerInfo	returns basic info for specified player
 *
 * $playerId	player id in question
 * $player	array to put the results into
 *****************************************************************/
function getPlayerInfo($playerId, $player)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $database;

	$link = getDBLink("getPlayerInfo");
	
	$q ="SELECT id, LastName, FirstName, Street, City, State, ZipCode, Birthdate,  EmailAddress,
	   HomePhone, WorkPhone, CurrentUPAMember, SkillLevel, FitnessLevel, YearsClub, YearsRecreation,
	   Baggage1, WillCaptainA, WillCaptainDraft, playingA, playingDraft, Sex, isAdmin, password, availability, playingOpen
		from  mcudlproto.player where id = \"" . $playerId . "\"";

	$result = mysql_query ($q, $link) or die("Player Query Failed [" . $q . "]");

		// there should only be one result row
	while ($row = mysql_fetch_array ($result))
	{
		$player["id"]					= $row["id"];
		$player["lastName"]				= $row["LastName"];
		$player["firstName"]		  	= $row["FirstName"];
		$player["street"]				= $row["Street"];
		$player["city"]					= $row["City"];
		$player["state"]				= $row["State"];
		$player["zipcode"]				= $row["ZipCode"];
		$player["birthdate"]			= $row["Birthdate"];
		$player["email"]				= $row["EmailAddress"];
		$player["homePhone"]			= $row["HomePhone"];
		$player["workPhone"]			= $row["WorkPhone"];
		$player["upaMember"]			= $row["CurrentUPAMember"];
		$player["skillLevel"]			= $row["SkillLevel"];
		$player["fitnessLevel"]			= $row["FitnessLevel"];
		$player["clubYears"]			= $row["YearsClub"];
		$player["recYears"]				= $row["YearsRecreation"];
		$player["baggage1"]				= $row["Baggage1"];
		$player["willCaptainA"]			= $row["WillCaptainA"];
		$player["willCaptainDraft"]		= $row["WillCaptainDraft"];
		$player["playingA"]				= $row["playingA"];
		$player["playingDraft"]			= $row["playingDraft"];
		$player["sex"]					= $row["Sex"];
		$player["isAdmin"]				= $row["isAdmin"];
		$player["password"]				= $row["password"];
		$player["availability"]			= $row["availability"];
                $player["playingOpen"]                  = $row["playingOpen"];
	        $player["UPAmembershipnumber"]          = $row["UPAmembershipnumber"];
}
		
	mysql_free_result ($result);
	mysql_close ($link);
}
/*****************************************************************
 * getAvailablePlayers	returns player's firstName, lastName and id
 *			for players that are not already assigned to
 *			a team for specified division
 *
 * $division		the division in question
 * $captainsOnly	flag to indicate if only captains are required
 * $playerSet		array to put results in. is an array of arrays
 *			that hold player info.
 *
******************************************************************/
function getAvailablePlayers($captainsOnly = false, $division, $playerSet)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $database;

	$link = getDBLink("getAvailablePlayers");

		/*********************************************************
		 * Need to determine which players are already assigned a team
		 * in the division in question. we don't want to make these
		 * available
		**********************************************************/

	$assignedDivisionPlayersQ	= "select r.player_id from $database.roster r, $database.team t
									where  r.team_id = t.id
									and t.division = '" . $division . "'";

	$assignedPlayersRS = mysql_query($assignedDivisionPlayersQ, $link)
							or die("Query Failed In getAvailablePlayers:[" . $assignedDivisionPlayersQ . "]");

	$assignedPlayers	= array();
	$count	= 0;

   while ($row = mysql_fetch_array ($assignedPlayersRS))
   {
		$assignedPlayers[$count]	= $row[0];
$assignedPlayers[$row[0]]	= $row;
		$count	+= 1;
	}

		/****************************************************************
		 * Now we need to get the set of players who said they want to pay
		 * in the division in question. some of them may have been assigned
		*****************************************************************/

	if ($division == "A")
		$filterCol	= "playingA";
	else
		$filterCol	= "playingDraft";

	if ($filterCol == null)
	{
		$allDivisionPlayersQ	= "select p.id, p.FirstName, p.LastName from $database.player p "
								. " p.willCaptainA = 'Y'";
	}
	else
	{
		$allDivisionPlayersQ	= "select p.id, p.FirstName, p.LastName from $database.player p where p.$filterCol = 'Y'";

		if ($captainsOnly == true )
			$allDivisionPlayersQ	.= " and p.willCaptainA = 'Y' ";
	}
		
	$allDivisionPlayersQ	.= " order by p.LastName";

	$allDivisionPlayersRS = mysql_query($allDivisionPlayersQ, $link)
							or die("Query Failed In getAvailablePlayers:[" . $allDivisionPlayersQ . "]");

	$count	= 0;
        $x=null;

	while ($row = mysql_fetch_array ($allDivisionPlayersRS))
	{
   		$player	= array();
                $x=$assignedPlayers[$row[0]];
			// if the result player is already assign skip it
                if ($x == null)
		{
			$player["playerId"]		= $row[0];
			$player["firstName"]	= $row[1];
			$player["lastName"]		= $row[2];

			$playerSet[$count]	= $player;
			$count	+= 1;
		}
	}

	mysql_free_result ($allDivisionPlayersRS);
	mysql_free_result ($assignedPlayersRS);

	mysql_close ($link);
}

function getAllRegisteredPlayers($playerSet)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix;

	$link = getDBLink("getAllRegisteredPlayers");

	$q = "SELECT p.FirstName, p.LastName, p.user, p.Street, p.City, p.State, "
			. "p.Zipcode, p.HomePhone, p.WorkPhone, p.EmailAddress, p.id "
			. " from " . $dbprefix . "phpws_mod_phatform_form_4 p " 
			. " order by p.LastName";
	
	$result = mysql_query($q, $link) or die("Query Failed In getAllRegisteredPlayers:[" . $q . "]");

   $count	= 0;
   while ($row = mysql_fetch_array ($result)) {
   		$player	= array();

		 $player["firstName"]		= $row[0];
		 $player["lastName"]		= $row[1];
		 $player["nickName"]		= $row[2];
		 $player["street"]			= $row[3];
		 $player["city"]			= $row[4];
		 $player["state"]			= $row[5]; 
		 $player["zipcode"]			= $row[6];
		 $player["homePhone"]		= $row[7];
		 $player["workPhone"]		= $row[8];
		 $player["emailAddress"]	= $row[9];
		 $player["playerId"]		= $row[10];

		 $playerSet[$count]	= $player;

		 $count	+= 1;
	}

	mysql_free_result ($result);
	mysql_close ($link);
}

function getSupplementalPlayers($playerSet)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix;

	$link = getDBLink("getSupplementalPlayers");

	$q = "SELECT p.FirstName, p.LastName, p.NickName, p.Street, p.City, p.State, "
			. "p.Zipcode, p.HomePhone, p.WorkPhone, p.EmailAddress, p.id "
                        . " ,p.availability, p.yearsClub, p.yearsRecreation, p.skillLevel,"
                        . "p.fitnessLevel,p.isPaid "
			. " from " . $dbprefix . "player p where p.supplemental = 'Y' "
                        . " and p.playingDraft = 'Y' and p.isPaid= 'Y'" 
			. " order by p.LastName";
	
	$result = mysql_query($q, $link) or die("Query Failed In getSupplementalRegisteredPlayers:[" . $q . "]");

   $count	= 0;
   while ($row = mysql_fetch_array ($result)) {
   		$player	= array();

		 $player["firstName"]		= $row[0];
		 $player["lastName"]		= $row[1];
		 $player["nickName"]		= $row[2];
		 $player["street"]			= $row[3];
		 $player["city"]			= $row[4];
		 $player["state"]			= $row[5]; 
		 $player["zipcode"]			= $row[6];
		 $player["homePhone"]		= $row[7];
		 $player["workPhone"]		= $row[8];
		 $player["emailAddress"]	= $row[9];
		 $player["playerId"]		= $row[10];
                 $player["availability"]		= $row[11];
                 $player["yearsClub"]		= $row[12];
                 $player["yearsRecreation"]		= $row[13];
                 $player["skillLevel"]		= $row[14];
                 $player["fitnessLevel"]		= $row[15];
                 $player["isPaid"]		= $row[16];

		 $playerSet[$count]	= $player;

		 $count	+= 1;
	}

	mysql_free_result ($result);
	mysql_close ($link);
}


function getUnpaidPlayers($playerSet)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix;

	$link = getDBLink("getAllRegisteredPlayers");

	$q = "SELECT p.FirstName, p.LastName, p.user, p.Street, p.City, p.State, "
			. "p.Zipcode, p.HomePhone, p.WorkPhone, p.EmailAddress, p.id, "
                        . "p.paid, p.waiver "
			. " from " . $dbprefix . "phpws_mod_phatform_form_4 p "
			. " order by p.LastName";
	
	$result = mysql_query($q, $link) or die("Query Failed In getAllRegisteredPlayers:[" . $q . "]");

   $count	= 0;
   while ($row = mysql_fetch_array ($result)) {
   		$player	= array();

		 $player["firstName"]		= $row[0];
		 $player["lastName"]		= $row[1];
		 $player["nickName"]		= $row[2];
		 $player["street"]			= $row[3];
		 $player["city"]			= $row[4];
		 $player["state"]			= $row[5]; 
		 $player["zipcode"]			= $row[6];
		 $player["homePhone"]		= $row[7];
		 $player["workPhone"]		= $row[8];
		 $player["emailAddress"]	= $row[9];
		 $player["playerId"]		= $row[10];
                 $player["isPaid"] = $row[11];
                 $player["providedWaiver"] = $row[12];

		 $playerSet[$count]	= $player;

		 $count	+= 1;
	}

	mysql_free_result ($result);
	mysql_close ($link);
}

function getTeamRoster($teamId, $playerSet)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix;

	$link = getDBLink("getTeamRoster");

	$q = "SELECT p.FirstName, p.LastName, p.NickName, r.isCaptain, p.Street, p.City, p.State, "
			. "p.Zipcode, p.HomePhone, p.WorkPhone, p.EmailAddress, p.id, p.isPaid, t.name from "
			. $dbprefix . "player p, " . $dbprefix . "roster r," . $dbprefix . "team t "
			. " where p.id = r.player_id  and r.team_id = t.id ";
			
		// if no team specified, get all players
	if ($teamId != null)
		$q .= " and r.team_id = $teamId ";
		
	$q	.= " order by p.LastName";
	
	$result = mysql_query($q, $link) or die("Query Failed In getTeamRoster:[" . $q . "]");

   $count	= 0;
   while ($row = mysql_fetch_array ($result)) {
   		$player	= array();

		 $player["firstName"]		= $row[0];
		 $player["lastName"]		= $row[1];
		 $player["nickName"]		= $row[2];
		 $player["isCaptain"]		= $row[3];
		 $player["street"]			= $row[4];
		 $player["city"]			= $row[5];
		 $player["state"]			= $row[6]; 
		 $player["zipcode"]			= $row[7];
		 $player["homePhone"]		= $row[8];
		 $player["workPhone"]		= $row[9];
		 $player["emailAddress"]	= $row[10];
		 $player["playerId"]		= $row[11];
		 $player["isPaid"]			= $row[12];
		 $player["teamName"]		= $row[13];

		 $playerSet[$count]	= $player;

		 $count	+= 1;
	}

	mysql_free_result ($result);
	mysql_close ($link);
}

function getTeamSet($division, $teamArray)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $database;

	$link = getDBLink("getTeamSet");

	$q = "SELECT id, name, division from $database.team "
		. "where division = '$division' order by id";
	
	$result = mysql_query($q, $link)
		or die("Query Failed In genTeamSet:[" . $q . "]");

   $aCount		= count($teamArray);

   while ($row = mysql_fetch_array ($result)) {
		$teamArray[$aCount]				= array();

		$teamArray[$aCount]["id"]		= $row[0];
		$teamArray[$aCount]["name"]		= $row[1];
		$teamArray[$aCount]["division"]	= $row[2];

		$aCount	+= 1;
	}
		
	mysql_free_result ($result);
	mysql_close ($link);
}

function getGameStats($gameId, $teamId, $playerId, $statsArray)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $database;

	$roster	= array();
	getTeamRoster($teamId, &$roster);

	$link = getDBLink("getGameStats");

	$q = "SELECT  ps.id, ps.player_id, ps.goals, ps.assists, ps.blocks "
		. " from $database.player_stats ps "
		. " where ps.game_id = $gameId "
		. " and ps.team_id = $teamId ";

	$result = mysql_query($q, $link)
		or die("Query Failed In getGameStats:[" . $q . "]");

   $playerStats	= array();

   while ($row = mysql_fetch_array ($result)) {
		$playerStat			= array();
		$playerStat["statId"]	= $row[0];
		$playerStat["playerId"]	= $row[1];
		$playerStat["goals"]	= $row[2];
		$playerStat["assists"]	= $row[3];
		$playerStat["blocks"]	= $row[4];

			// index this record on the player
		$playerStats[$row[1]]	= $playerStat;
	}
		
	mysql_free_result ($result);
	mysql_close ($link);

	$count	= 0;
	foreach ($roster AS $player)
	{
		$foundPlayer	= $playerStats[$player["playerId"]];

			// if we didn't retrieve a stat, make it up
		if ($foundPlayer == null)
		{
			$playerStat			= array();

			$playerStat["playerId"]		= $player["playerId"];
			$playerStat["firstName"]	= $player["firstName"];
			$playerStat["lastName"]		= $player["lastName"];
			$playerStat["goals"]		= "0";
			$playerStat["assists"]		= "0";
			$playerStat["blocks"]		= "0";
		}
		else
		{
			$playerStat	= $playerStats[$player["playerId"]];

				// add stuff not in the stat query
			$playerStat["firstName"]	= $player["firstName"];
			$playerStat["lastName"]		= $player["lastName"];
		}

		$statsArray[$count]	= $playerStat;

		$count	+= 1;
	}
}

function getRowId($tableName, $dbname='mcudlproto.', $fldName='id')
{
GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix;
	
	$link = getDBLink("getRowId");

	$q = "SELECT MAX($fldName) from " . $dbname . $tableName;

	$result = mysql_query($q, $link)
		or die("Query Failed In getRowId:[" . $q . "]");

	while ($row = mysql_fetch_array ($result)) {
		$id = $row[0];
	}
	if ($id == null)$id = 0;  

	mysql_free_result ($result);
	mysql_close ($link);

	return $id + 1;
}

function createPlayerId($lastName)
{
GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix;

	$link = getDBLink("createPlayerId");
	
	$q = "SELECT id from " . $dbprefix . "player where LastName = \"" . $lastName . "\"";

	$result = mysql_query($q, $link)
		or die("Query Failed In createPlayerId:[" . $q . "]");

	$numRows	= mysql_num_rows($result);

	if ($numRows  == 0)
		$playerId	= $lastName;
	else
	{
		$numRows	+= 1;
		$playerId	= $lastName . "$numRows";
	}
		
	mysql_free_result ($result);
	mysql_close ($link);

	return $playerId;
}


function getUserPassword($user)
{
GLOBAL $dbserver, $dbuser, $dbpwd, $bbdbprefix;

	$link = getDBLink("getUserPassword");
	
	$q = "SELECT user_password from " . $bbdbprefix . "phpbb_users where username = \"" . $user . "\"";

	$result = mysql_query($q, $link)
		or die("Query Failed In getUserPassword:[" . $q . "]");

	$pwd = "BadPassword";
	while ($row = mysql_fetch_array ($result)) {
		$pwd = $row[0];
	}
		
	mysql_free_result ($result);
	mysql_close ($link);
	return $pwd;
}

/*********************************************************************
 * DB update functions. these functions do inserts or updates against
 * the database. these may want to be put into a separate file
**********************************************************************/

function reschedGame($gameId, $reschedDate)
{
GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $database;

  $q = "UPDATE $database.games set resched_date = '$reschedDate' where id = $gameId";

	$link = getDBLink("reschedGame");
  $result = mysql_query($q, $link)
		or die("Query Failed In reschedGame [" . $q . "]");

  mysql_close ($link);

}

function addGame($date, $home, $away, $field)
{
GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix;

	$gameId = getRowId("games");

	$q = "INSERT into " . $dbprefix . "games"
	  . " (id, game_day, home_team_id, away_team_id, field) VALUES ("
	  . "$gameId" . ", "
	  . "\"$date\", "
	  . "\"$home\", "
	  . "\"$away\", "
	  . "$field)";

	$link = getDBLink("addGame");

	$result = mysql_query($q, $link)
		or die("Query Failed In addGame:[" . $q . "]");

	mysql_close ($link);
}

function clearGameResults($gameId, $homeTeam, $homeScore, $awayTeam, $awayScore)
{
  GLOBAL $dbserver, $dbuser, $dbpwd, $database;

		// don't update records if scores are zero
	if ($homeScore == 0 && $awayScore == 0)
		return;

  $link = getDBLink("clearGameResults");
	
  $q = "UPDATE $database.games"
		  . " set home_score = 0, "
		  . " away_score = 0 "
		  . "where id = $gameId";

  $result = mysql_query($q, $link)
		or die("Query Failed In clearGameResult:[" . $q . "]");

	if ($homeScore > $awayScore)
	{
		$winner = "UPDATE $database.team set wins = wins - 1 where id = $homeTeam";
		$loser = "UPDATE $database.team set losses = losses - 1 where id = $awayTeam";
	}
	else if ($awayScore > $homeScore)
	{
		$winner = "UPDATE $database.team set wins = wins - 1 where id = $awayTeam";
		$loser = "UPDATE $database.team set losses = losses - 1 where id = $homeTeam";
	}
		// if both scores are 0, we are clearing. so don't update record
	else
	{
		$winner = "UPDATE $database.team set draws = draws - 1 where id = $awayTeam";
		$loser = "UPDATE $database.team set draws = draws - 1 where id = $homeTeam";
	}

  $result = mysql_query($winner, $link)
		or die("Query Failed In clearGameResult winner:[" . $winner . "]");

  $result = mysql_query($loser, $link)
		or die("Query Failed In clearGameResult loser:[" . $loser . "]");

	// this is unfortunate for the user, but if the game is gone
	// the stats must be gone to.
	// need to be able to offer updating the game score
  $q = "DELETE FROM $database.player_stats where game_id = $gameId";

  $result = mysql_query($q, $link)
		or die("Query Failed In clearGameResult loser:[" . $q . "]");

  mysql_close ($link);
}

function addGameResult($gameId, $homeTeam, $homeScore, $awayTeam, $awayScore)
{
  GLOBAL $dbserver, $dbuser, $dbpwd, $database;

		// don't update records if scores are zero
	if ($homeScore == 0 && $awayScore == 0)
		return;

  $link = getDBLink("addGameResult");
	
  $q = "UPDATE $database.games"
		  . " set home_score = $homeScore, "
		  . " away_score = $awayScore "
		  . "where id = $gameId";

  $result = mysql_query($q, $link)
		or die("Query Failed In addGameResult:[" . $q . "]");

	if ($homeScore > $awayScore)
	{
		$winner = "UPDATE $database.team set wins = wins + 1 where id = $homeTeam";
		$loser = "UPDATE $database.team set losses = losses + 1 where id = $awayTeam";
	}
	else if ($awayScore > $homeScore)
	{
		$winner = "UPDATE $database.team set wins = wins + 1 where id = $awayTeam";
		$loser = "UPDATE $database.team set losses = losses + 1 where id = $homeTeam";
	}
		// if both scores are 0, we are clearing. so don't update record
	else
	{
		$winner = "UPDATE $database.team set draws = draws + 1 where id = $awayTeam";
		$loser = "UPDATE $database.team set draws = draws + 1 where id = $homeTeam";
	}

  $result = mysql_query($winner, $link)
		or die("Query Failed In addGameResult winner:[" . $winner . "]");

  $result = mysql_query($loser, $link)
		or die("Query Failed In addGameResult loser:[" . $loser . "]");

  mysql_close ($link);
}

function addPlayerStat($gameId, $teamId, $playerId, $goals, $assists, $blocks, $statId)
{
  GLOBAL $dbserver, $dbuser, $dbpwd, $database;
	
  if ($statId == "null")
  {
	  $rowId = getRowId("player_stats");

	  $q = "INSERT into $database.player_stats"
			  . " (id, game_id, player_id, team_id, goals, blocks, assists) VALUES ("
			  . "$rowId" . ", "
			  . "$gameId, "
			  . "\"$playerId\", "
			  . "$teamId, "
			  . "$goals, "
			  . "$blocks, "
			  . "$assists)";
	}
	else
	{
		$q	= "UPDATE $database.player_stats "
				. "set goals = $goals, "
				. " assists = $assists, "
				. " blocks = $blocks "
				. "where id = $statId";

	}

  $link = getDBLink("addPlayerStat");

  $result1 = mysql_query($q, $link)
		or die("Query Failed In addTeam:[" . $q . "]");

  mysql_close ($link);
}

function addTeam($teamName, $division)
{
  GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix;

  $teamId = getRowId("team");

  $link = getDBLink("addTeam");
	

  $q = "INSERT into " . $dbprefix . "team"
		  . " (id, division, name) VALUES ("
		  . "$teamId" . ", "
		  . "\"$division\", "
		  . "\"$teamName\")";

  $result1 = mysql_query($q, $link)
		or die("Query Failed In addTeam:[" . $q . "]");

  mysql_close ($link);
}

function addPlayer($teamId, $playerId, $isCaptain, $rosterPosition)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $database;

	if ($rosterPostion == null) $rosterPosition = 0;

	$rosterId = getRowId("roster");

	$q = "INSERT into " . $dbprefix . "roster "
		 . "(id, team_id, player_id, isCaptain, player_list_pos) VALUES ("
		 . "$rosterId , "
		 . "$teamId , "
		 . "\"$playerId\" , "
		 . "'$isCaptain' , "
		 . "$rosterPosition"
		 . ")";

	$xlink = getDBLink("addPlayer");

	mysql_query($q, $xlink)
		or die("Query Failed In addPlayer:[" . $q . "]");

        $q = "UPDATE $dbprefix" . "player set supplemental = 'N' WHERE id = '". $playerId. "'";
        mysql_query($q, $xlink)
		or die("Query Failed In addPlayer:[" . $q . "]");

	mysql_close ($xlink);

}


function rmPlayer($teamId, $playerId)
{
 GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix;

	$link = getDBLink("rmPlayer");

	$q = "DELETE from  " . $dbprefix . "roster "
		 . "where team_id = $teamId and player_id = '" . $playerId. "'";

	$result2 = mysql_query($q, $link)
		or die("Query Failed In rmPlayer:[" . $q . "]");

	mysql_close ($link);

}

/*
	insert a minimal record into the phpBB user table for this user
	if it does not exist.
	password is already md5 encrypted
*/

function ensurePHPBBRecord($username, $password, $email)
{
GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $bbdbprefix;

	$l1	= getDBLink("ensurePHPBBRecord");

	$q	= "SELECT user_id , user_password from "
		. $bbdbprefix . "phpbb_users where username='" . $username . "'";

	$result = mysql_query($q, $l1)
		or die("Query Failed In ensurePHPBBRecord:[" . $q . "]");

		// this return only one row. if no row then create a new one.
		// if there is one, make sure the common fields are updated
	if ($row = mysql_fetch_array ($result))
	{
		$q	= "UPDATE $bbdbprefix " . " phpbb_users "
			. " SET user_password = '$password' "
			. " , user_email = '$email' "
			. " WHERE user_id = $row[0]";

		mysql_query($q, $l1)
			or die ("UPDATE failed into phpbb_users table [$q]");
		mysql_close($l1);
	} else {
		$userId = getRowId("phpbb_users", $bbdbprefix, "user_id");
		$l	= getDBLink("wtf");

		$q	= "INSERT into $bbdbprefix" . "phpbb_users "
			. " (user_id, username, user_password, user_email) "
			. " values ($userId,'$username','$password', '$email')";

		mysql_query($q, $l)
			or die ("INSERT failed into phpbb_users table[$q]");
		mysql_close($l);
	}

}

function md5passwords() {
GLOBAL $dbserver, $dbuser, $dbpwd, $dbprefix, $bbdbprefix;

	$link	= getDBLink("md5passwords");

	$q	= "SELECT id, password, emailAddress from $dbprefix" . "player";
	$result = mysql_query($q, $link)
		or die ("md5 select failed [$q]");

	mysql_close($link);
	while ($row = mysql_fetch_array ($result))
	{
		ensurePHPBBRecord($row[0], $row[1], $row[2]);
	}

}

?>