<?php
include_once 'connection.php';


include 'goallinefunctionsJSON.php';


function getCSSImage($teamID) {
	$pdoConnect = createNDBH();
	
	$TeamImageQuery = $pdoConnect->prepare('SELECT imagelocation FROM CSSteamassignment WHERE idTeam = :teamid LIMIT 1');
	$TeamImageQuery->bindParam(':teamid', $teamID, PDO::PARAM_INT );
	$TeamImageQuery->execute();
	$teamImage=$TeamImageQuery->fetch(PDO::FETCH_ASSOC);
	
return $teamImage;	
}


function JSONgameData($gameid) {
	$pdoConnect = createDBH();

	## Initial query to fetch basic data
	$GameFetchQuery = $pdoConnect->prepare('SELECT GameDate, GameStartTime, LocationName, Rink, game.idSeasonkey, game.idsubseasonkey, SubSeasonName, HomeTeam, hometeamname.idTeam as HomeTeamid, hometeamname.TeamName as HomeTeamName, hometeamname.externalLargeImage AS HomeTeamAHALarge, hometeamname.externalSmallImage AS HomeTeamAHASmall, hometeamstat.teamPrefix AS HomePrefix, AwayTeam, awayteamname.idTeam as AwayTeamid, awayteamname.TeamName as AwayTeamName, awayteamname.externalLargeImage AS AwayTeamAHALarge, awayteamname.externalSmallImage AS AwayTeamAHASmall, awayteamstat.teamPrefix AS AwayPrefix, HomeGoals, AwayGoals, Home1stGoals, Home2ndGoals, Home3rdGoals, HomeOTGoals, Home1stShots, Home2ndShots, Home3rdShots, HomeOTShots, Away1stGoals, Away2ndGoals, Away3rdGoals, AwayOTGoals, Away1stShots, Away2ndShots, Away3rdShots, AwayOTShots, HomeShots, AwayShots, GameFinished FROM game JOIN teamassignment AS hometeamassignment ON game.HomeTeam = hometeamassignment.idTeamAssignment JOIN teamstatassignment AS hometeamstat ON hometeamassignment.idTeamAssignment = hometeamstat.idTeamAssignmentkey AND game.idsubseasonkey = hometeamstat.idsubseasonkey JOIN team AS hometeamname ON hometeamassignment.idTeamkey = hometeamname.idTeam JOIN teamassignment AS awayteamassignment ON game.AwayTeam = awayteamassignment.idTeamAssignment JOIN teamstatassignment AS awayteamstat ON awayteamassignment.idTeamAssignment = awayteamstat.idTeamAssignmentkey AND game.idsubseasonkey = awayteamstat.idsubseasonkey JOIN team AS awayteamname ON awayteamassignment.idTeamkey = awayteamname.idTeam JOIN location ON game.idLocationkey = location.idLocation JOIN subseason ON game.idsubseasonkey = subseason.idsubseason WHERE idGame = :gameid LIMIT 1');
	$GameFetchQuery->bindParam(':gameid', $gameid, PDO::PARAM_INT );
	$GameFetchQuery->execute();
	$gamedetails=$GameFetchQuery->fetch(PDO::FETCH_ASSOC);
return $gamedetails;
}

function JSONgetGameScore($gameid) {
	$pdoConnect = createDBH();

	$GameFetchQuery = $pdoConnect->prepare('SELECT HomeTeam, hometeamname.idTeam as HomeTeamid, hometeamname.TeamName as HomeTeamName, hometeamname.externalSmallImage AS HomeTeamAHASmall, hometeamstat.teamPrefix AS HomePrefix, AwayTeam, awayteamname.idTeam as AwayTeamid, awayteamname.TeamName as AwayTeamName, awayteamname.externalSmallImage AS AwayTeamAHASmall, awayteamstat.teamPrefix AS AwayPrefix, HomeGoals, AwayGoals, Home1stGoals, Home2ndGoals, Home3rdGoals, HomeOTGoals, Away1stGoals, Away2ndGoals, Away3rdGoals, AwayOTGoals, HomeOTShots, AwayOTShots FROM game JOIN teamassignment AS hometeamassignment ON game.HomeTeam = hometeamassignment.idTeamAssignment JOIN teamstatassignment AS hometeamstat ON hometeamassignment.idTeamAssignment = hometeamstat.idTeamAssignmentkey AND game.idsubseasonkey = hometeamstat.idsubseasonkey JOIN team AS hometeamname ON hometeamassignment.idTeamkey = hometeamname.idTeam JOIN teamassignment AS awayteamassignment ON game.AwayTeam = awayteamassignment.idTeamAssignment JOIN teamstatassignment AS awayteamstat ON awayteamassignment.idTeamAssignment = awayteamstat.idTeamAssignmentkey AND game.idsubseasonkey = awayteamstat.idsubseasonkey JOIN team AS awayteamname ON awayteamassignment.idTeamkey = awayteamname.idTeam JOIN location ON game.idLocationkey = location.idLocation JOIN subseason ON game.idsubseasonkey = subseason.idsubseason WHERE idGame = :gameid LIMIT 1');
	$GameFetchQuery->bindParam(':gameid', $gameid, PDO::PARAM_INT );
	$GameFetchQuery->execute();
	$gamedetails=$GameFetchQuery->fetch(PDO::FETCH_ASSOC);


	$HomeScoreArray = array('TeamID' => $gamedetails['HomeTeam'], 'Team Name' =>$gamedetails['HomeTeamName'], 'TeamImage' => $gamedetails['HomeTeamAHASmall'], '1st' => $gamedetails['Home1stGoals'], '2nd' => $gamedetails['Home2ndGoals'], '3rd' => $gamedetails['Home3rdGoals']);

	$AwayScoreArray = array('TeamID' => $gamedetails['AwayTeam'], 'Team Name' =>$gamedetails['AwayTeamName'], 'TeamImage' => $gamedetails['AwayTeamAHASmall'], '1st' => $gamedetails['Away1stGoals'], '2nd' => $gamedetails['Away2ndGoals'], '3rd' => $gamedetails['Away3rdGoals']);

	if ($gamedetails['HomeOTShots'] == 0 || $gamedetails['AwayOTShots'] == 0) {
		$HomeScoreArray += ['Final' => $gamedetails['HomeGoals']];
		$AwayScoreArray += ['Final' => $gamedetails['AwayGoals']];
	} else {
		$HomeScoreArray += ['OT' => $gamedetails['HomeOTGoals'], 'Final/OT' => $gamedetails['HomeGoals']];
		$AwayScoreArray += ['OT' => $gamedetails['AwayOTGoals'], 'Final/OT' => $gamedetails['AwayGoals']];
	}

	$outputScoreArray = array($HomeScoreArray, $AwayScoreArray);

return $outputScoreArray;
}

function fetchPreviousGoals($playerID, $gameDate, $elapsedTime, $subseason) {
	$pdoConnect = createDBH();

	$previousGoalFetch = $pdoConnect->prepare("SELECT COUNT(*) as GoalsScored
	FROM game
	JOIN gameevents ON game.idGame = gameevents.idGamekey
	JOIN goalevent ON gameevents.idGameEvents = goalevent.idGameEvents 
	WHERE GoalScorer = :playerid
	AND idsubseasonkey = :idsubseason 
	AND (GameDate < :gameDate
		OR (GameDate = :gameDate AND ROUND(((GamePeriod-1)*17)+(TIME_TO_SEC(GameTime)/60),2) <= :elapsedTime))");
	$previousGoalFetch->bindParam(':playerid', $playerID, PDO::PARAM_INT );
	$previousGoalFetch->bindParam(':idsubseason', $subseason, PDO::PARAM_INT );
	$previousGoalFetch->bindParam(':gameDate', $gameDate, PDO::PARAM_STR );
	$previousGoalFetch->bindParam(':elapsedTime', $elapsedTime, PDO::PARAM_STR );

	$previousGoalFetch->execute();
	$goalsFound = $previousGoalFetch->fetch(PDO::FETCH_ASSOC);

	return $goalsFound['GoalsScored'];
}

function fetchPreviousAssists($playerID, $gameDate, $elapsedTime, $subseason) {
	$pdoConnect = createDBH();

	$previousAssistFetch = $pdoConnect->prepare("SELECT COUNT(*) as AssistsScored
	FROM game
	JOIN gameevents ON game.idGame = gameevents.idGamekey
	JOIN goalevent ON gameevents.idGameEvents = goalevent.idGameEvents 
	WHERE (PlayerAssist = :playerid OR PlayerAssist2 = :playerid)
	AND idsubseasonkey = :idsubseason
	AND (GameDate < :gameDate
		OR (GameDate = :gameDate AND ROUND(((GamePeriod-1)*17)+(TIME_TO_SEC(GameTime)/60),2) <= :elapsedTime))");
	$previousAssistFetch->bindParam(':playerid', $playerID, PDO::PARAM_INT );
	$previousAssistFetch->bindParam(':idsubseason', $subseason, PDO::PARAM_INT );
	$previousAssistFetch->bindParam(':gameDate', $gameDate, PDO::PARAM_STR );
	$previousAssistFetch->bindParam(':elapsedTime', $elapsedTime, PDO::PARAM_STR );

	$previousAssistFetch->execute();
	$assistFound = $previousAssistFetch->fetch(PDO::FETCH_ASSOC);

	return $assistFound['AssistsScored'];
}

function JSONgetGoals($gameid) {	
	$pdoConnect = createDBH();
	
	$GoalEventsFetch = $pdoConnect->prepare("SELECT game.GameDate, game.idsubseasonkey, gameevents.idGameEvents, gameevents.idTeamAssignmentkey as TeamID, GamePeriod, GameTime, ROUND(((GamePeriod-1)*17)+(TIME_TO_SEC(GameTime)/60),2) AS 'ElapsedTime', TeamName, GoalScorerNum.idPlayerAssignment AS GoalScorerID, 
	CONCAT(IF(GoalScorerNum.PlayerNumber Is Null, '', CONCAT('\#', GoalScorerNum.PlayerNumber, ' ')), COALESCE(GoalName.PlayerFirst, ''), ' ', COALESCE(GoalName.PlayerLast, '')) AS GoalPlayerName, 
	Assist1Num.idPlayerAssignment as Assist1ID, 
	CONCAT(IF(Assist1Num.PlayerNumber Is Null, '', CONCAT('\#', Assist1Num.PlayerNumber, ' ')), COALESCE(Assist1Name.PlayerFirst, ''), ' ', COALESCE(Assist1Name.PlayerLast, '')) AS Assist1PlayerName, 
	Assist2Num.idPlayerAssignment as Assist2ID, 
	CONCAT(IF(Assist2Num.PlayerNumber Is Null, '', CONCAT('\#', Assist2Num.PlayerNumber, ' ')), COALESCE(Assist2Name.PlayerFirst, ''), ' ', COALESCE(Assist2Name.PlayerLast, '')) AS Assist2PlayerName FROM gameevents JOIN goalevent ON gameevents.idGameEvents=goalevent.idGameEvents JOIN game ON gameevents.idGamekey = game.idGame JOIN teamassignment ON gameevents.idTeamAssignmentkey = teamassignment.idTeamAssignment JOIN team ON teamassignment.idTeamkey = team.idTeam
	JOIN playerassignment AS GoalScorerNum ON goalevent.GoalScorer = GoalScorerNum.idPlayerAssignment JOIN players AS GoalName ON GoalScorerNum.idPlayerskey = GoalName.idPlayers 
	LEFT JOIN playerassignment AS Assist1Num ON goalevent.PlayerAssist = Assist1Num.idPlayerAssignment LEFT JOIN players AS Assist1Name ON Assist1Num.idPlayerskey = Assist1Name.idPlayers 
	LEFT JOIN playerassignment AS Assist2Num ON goalevent.PlayerAssist2 = Assist2Num.idPlayerAssignment LEFT JOIN players AS Assist2Name ON Assist2Num.idPlayerskey = Assist2Name.idPlayers WHERE idGamekey = :gameid AND idGameClassEventkey = 1 ORDER BY GamePeriod ASC, GameTime DESC");
	$GoalEventsFetch->bindParam(':gameid', $gameid, PDO::PARAM_INT );
	$GoalEventsFetch->execute();

	$JSONGoalsEvents = array();
	while($GoalEvents=$GoalEventsFetch->fetch(PDO::FETCH_ASSOC))
	{
		$createGoalTime = date('i:s', strtotime($GoalEvents['GameTime']));

		$checkGoalTime = substr($createGoalTime, 0, 1);
		if ($checkGoalTime == 0)  {$setGoalTime = substr($createGoalTime, 1);} else {$setGoalTime = $createGoalTime;}
		
		$previousGoalsScored = "(".fetchPreviousGoals($GoalEvents['GoalScorerID'], $GoalEvents['GameDate'], $GoalEvents['ElapsedTime'], $GoalEvents['idsubseasonkey']).")";
		if ($GoalEvents['Assist1ID']) {$previousAssists1 = "(".fetchPreviousAssists($GoalEvents['Assist1ID'], $GoalEvents['GameDate'], $GoalEvents['ElapsedTime'], $GoalEvents['idsubseasonkey']).")";} else {$previousAssists1 = '';}
		if ($GoalEvents['Assist2ID']) {$previousAssists2 = "(".fetchPreviousAssists($GoalEvents['Assist1ID'], $GoalEvents['GameDate'], $GoalEvents['ElapsedTime'], $GoalEvents['idsubseasonkey']).")";} else {$previousAssists2 = '';}

		$OutputEvents = array();
		$OutputEvents += ['Period' => $GoalEvents['GamePeriod'], 'Time' => $setGoalTime, 'Team' => $GoalEvents['TeamName'], 'teamid' => $GoalEvents['TeamID']];
		$OutputEvents += ['Name' => $GoalEvents['GoalPlayerName'].' '.$previousGoalsScored, 'goalid' => $GoalEvents['GoalScorerID']];
		$OutputEvents += ['Assist1' => $GoalEvents['Assist1PlayerName'].' '.$previousAssists1, 'assist1ID' => $GoalEvents['Assist1ID']];
		$OutputEvents += ['Assist2' => $GoalEvents['Assist2PlayerName'].' '.$previousAssists2, 'assist2ID' => $GoalEvents['Assist2ID']];
		$GoalClassFetch = $pdoConnect->prepare('SELECT GoalTypeShort, Goaltype, goalclasskey FROM goalclassevent JOIN goalclass ON goalclassevent.goalclasskey = goalclass.idGoalClass WHERE goaleventkey = :goalevent');
		$GoalClassFetch->bindParam(':goalevent', $GoalEvents['idGameEvents'], PDO::PARAM_INT );
		$GoalClassFetch->execute();
		$NumberGoalClass = $GoalClassFetch->rowCount();
		if ($NumberGoalClass > 0) {
			$GoalEventClasses = $GoalClassFetch->fetchAll(PDO::FETCH_ASSOC);
			$OutputEvents += ['Special' => $GoalEventClasses];
		} else {
			$OutputEvents += ['Special' => null];
		}
	$JSONGoalsEvents[] = $OutputEvents;
	}

return $JSONGoalsEvents;
}

function JSONgetPenalties($gameid) {
	$pdoConnect = createDBH();
	
	$PenaltyEventsFetch = $pdoConnect->prepare("SELECT GamePeriod, GameTime, TeamName, gameevents.idTeamAssignmentkey as TeamID, 
	CONCAT(IF(PlayerNumber Is Null, '', CONCAT('\#', PlayerNumber, ' ')), COALESCE(PlayerFirst, ''), ' ', COALESCE(PlayerLast, '')) AS PenaltyPlayer, penaltyevent.idPlayerAssignmentkey as PlayerID, PenaltyName, Powerplay, PenaltyLength FROM gameevents JOIN penaltyevent ON gameevents.idGameEvents=penaltyevent.idGameEvents JOIN penalty ON penaltyevent.idPenaltykey = penalty.idPenalty JOIN teamassignment ON gameevents.idTeamAssignmentkey = teamassignment.idTeamAssignment JOIN team ON teamassignment.idTeamkey = team.idTeam LEFT JOIN playerassignment ON penaltyevent.idPlayerAssignmentkey = playerassignment.idPlayerAssignment LEFT JOIN players ON playerassignment.idPlayerskey = players.idPlayers WHERE idGamekey = :gameid AND idGameClassEventkey = 2 ORDER BY GamePeriod ASC, GameTime DESC");
	$PenaltyEventsFetch->bindParam(':gameid', $gameid, PDO::PARAM_INT );
	$PenaltyEventsFetch->execute();

	$PenaltyEvents = $PenaltyEventsFetch->fetchAll(PDO::FETCH_ASSOC);
return $PenaltyEvents;
}

function JSONPenaltyOutput($penaltyarray) {

	$JSONPenalty = array();
	foreach ($penaltyarray as $PenaltyEvents) {
		$createPenaltyTime = date('i:s', strtotime($PenaltyEvents['GameTime']));

		$checkPenaltyTime = substr($createPenaltyTime, 0, 1);
		if ($checkPenaltyTime == 0)  {$setPenaltyTime = substr($createPenaltyTime, 1);} else {$setPenaltyTime = $createPenaltyTime;}
		
		$JSONPenalty[] = array('Period' => $PenaltyEvents['GamePeriod'], 'Time' => $setPenaltyTime, 'Team' => $PenaltyEvents['TeamName'], 'teamid' => $PenaltyEvents['TeamID'], 'Player' => $PenaltyEvents['PenaltyPlayer'], 'playerid' => $PenaltyEvents['PlayerID'], 'Penalty' => $PenaltyEvents['PenaltyName'], 'PIM' => $PenaltyEvents['PenaltyLength']);
	}
return $JSONPenalty;
}

function JSONgetPPboxScore ($goalarray, $penaltyarray, $teamid, $teamname, $teamimage) {
	#print_r($goalarray);
	$teampp = 0; 
	$teamppg = 0;
	$teamshg = 0;
	foreach ($penaltyarray as $penalty) {
		if ($penalty['Powerplay'] == 1 && $teamid != $penalty['TeamID']) {
			$teampp++;
		}
	}

	foreach ($goalarray as $goal) {
		if (isset($goal['Special']) > 0 && $goal['teamid'] == $teamid) {
			foreach($goal['Special'] as $goalclass) {
				if ($goalclass['GoalTypeShort'] == 'PPG') {$teamppg++;}
				elseif($goalclass['GoalTypeShort'] == 'SHG') {$teamshg++;}
			}
		}
	}

	if ($teampp == 0) {$powerplayConvert = '0%';}
	else {
		$powerplayConvert = number_format($teamppg/$teampp*100, 2) . "%";
	}

	$outputarray = array('teamid' => $teamid, 'Team' => $teamname, 'teamimage' => $teamimage, 'Powerplay' => $teampp, 'PPG' => $teamppg, 'PP%' => $powerplayConvert, 'SHG' => $teamshg);
	#print_r($outputarray);
return $outputarray;
}

function JSONboxscore($gameidbox, $teamidbox) {
	$pdoConnect = createDBH();
	#print_r($teamidbox);
	$TeamArray = array();
	#GamePosition is not used yet, Table position set to NULL
	$Skaters = $pdoConnect->prepare('SELECT GamePosition, PlayerNumber, PlayerLast, PlayerFirst, idPlayerAssignment FROM gameattendance JOIN playerassignment ON gameattendance.Playerassignmentkey = playerassignment.idPlayerassignment JOIN players ON playerassignment.idPlayerskey = players.idPlayers WHERE idGamekey = :idgame AND idTeamAssignmentkey = :idTeam AND Attend = 1');
	$Skaters->bindParam(':idgame', $gameidbox, PDO::PARAM_INT );
	$Skaters->bindParam(':idTeam', $teamidbox, PDO::PARAM_INT );
	$Skaters->execute();
	while($Skatersline=$Skaters->fetch(PDO::FETCH_ASSOC))
	{
		if(empty($Skatersline['PlayerNumber'])) {$name = $Skatersline['PlayerFirst'].' '.$Skatersline['PlayerLast'];} else {$name = '#'.$Skatersline['PlayerNumber'].' '.$Skatersline['PlayerFirst'].' '.$Skatersline['PlayerLast'];}
		array_push($TeamArray, array('playerid' => $Skatersline['idPlayerAssignment'], 'Name' => $name, 'Position' => '', 'G' => 0, 'A' => 0, 'Pts'=>0, 'PPG' => 0, 'SHG' => 0, 'PN' => 0, 'PIM' => 0));
	}
	$Assists = $pdoConnect->prepare('SELECT PlayerAssist FROM gameevents JOIN goalevent ON gameevents.idGameEvents = goalevent.idGameEvents WHERE idGamekey = :idgame AND idTeamAssignmentkey = :idTeam AND PlayerAssist IS NOT NULL UNION ALL SELECT PlayerAssist2 FROM gameevents JOIN goalevent ON gameevents.idGameEvents = goalevent.idGameEvents WHERE idGamekey = :idgame AND idTeamAssignmentkey = :idTeam AND PlayerAssist2 IS NOT NULL ');
	$Assists->bindParam(':idgame', $gameidbox, PDO::PARAM_INT );
	$Assists->bindParam(':idTeam', $teamidbox, PDO::PARAM_INT );
	$Assists->execute();
	if ($Assists->rowCount() != 0)
	{while ($Assistrow = $Assists->fetch(PDO::FETCH_ASSOC))
		{foreach ($TeamArray as &$array)
			{if ($array['playerid'] == $Assistrow['PlayerAssist'])
				{$array['A'] ++;}
			}
		} unset($array);
	}
	$Goals = $pdoConnect->prepare('SELECT GoalScorer, goalclasskey FROM gameevents JOIN goalevent ON gameevents.idGameEvents = goalevent.idGameEvents LEFT JOIN (SELECT idgoalclassevent, goaleventkey, goalclasskey FROM goalclassevent WHERE goalclasskey = 2 OR goalclasskey = 3) AS goalclasspossible ON goalevent.idGameEvents = goalclasspossible.goaleventkey WHERE idGamekey = :idgame AND idTeamAssignmentkey = :idTeam ');
	$Goals->bindParam(':idgame', $gameidbox, PDO::PARAM_INT );
	$Goals->bindParam(':idTeam', $teamidbox, PDO::PARAM_INT );
	$Goals->execute();
	if ($Goals->rowCount() != 0)
	{while ($Goalsrow = $Goals->fetch(PDO::FETCH_ASSOC))
		{foreach ($TeamArray as &$array)
			{if ($array['playerid'] == $Goalsrow['GoalScorer'])
				{$array['G'] ++;}
			if ($Goalsrow['goalclasskey'] == 2 && $array['playerid'] == $Goalsrow['GoalScorer'])
				{$array['PPG'] ++;}
			if ($Goalsrow['goalclasskey'] == 3 && $array['playerid'] == $Goalsrow['GoalScorer'])
				{$array['SHG'] ++;}
			}
		} unset($array);
	}
	$Penalties = $pdoConnect->prepare('SELECT idPlayerAssignmentkey, PenaltyLength FROM gameevents JOIN penaltyevent ON gameevents.idGameEvents = penaltyevent.idGameEvents WHERE idGamekey = :idgame AND idTeamAssignmentkey = :idTeam');
	$Penalties->bindParam(':idgame', $gameidbox, PDO::PARAM_INT );
	$Penalties->bindParam(':idTeam', $teamidbox, PDO::PARAM_INT );
	$Penalties->execute();
	{while ($Penaltyrow = $Penalties->fetch(PDO::FETCH_ASSOC))
		{foreach ($TeamArray as &$array)
			{if ($array['playerid'] == $Penaltyrow['idPlayerAssignmentkey'])
				{$array['PN'] ++;
				$array['PIM'] = $array['PIM'] + $Penaltyrow['PenaltyLength'];}
			}
		} unset($array);
	}

	foreach ($TeamArray as &$array) {
		$array['Pts'] = $array['G'] + $array['A'];
	} unset ($array);
	#print_r($TeamArray);
return $TeamArray;
}

function JSONGoalieGameStats($gameselect) {
	$pdoConnect = createDBH();

	$prepareSQL = "SELECT idTeamAssignmentkey, TeamName AS Team, idPlayerAssignment, CONCAT(IF(PlayerNumber Is Null, '', CONCAT('\#', PlayerNumber, ' ')), COALESCE(PlayerFirst, ''), ' ', COALESCE(PlayerLast, '')) AS 'Player', GoalieShots AS Shots, GoalieGoalsAllowed AS 'GA', FORMAT(1-(GoalieGoalsAllowed/GoalieShots),3) AS 'Save%' FROM gamegoaliesave JOIN game ON gamegoaliesave.idGamekey = game.idGame JOIN playerassignment ON gamegoaliesave.idPlayerAssignmentkey = playerassignment.idPlayerAssignment JOIN players ON playerassignment.idPlayerskey = players.idPlayers JOIN teamassignment ON playerassignment.idTeamAssignmentkey = teamassignment.idTeamAssignment JOIN team ON teamassignment.idTeamkey = team.idTeam WHERE idgame = :gameid ORDER BY GoalieShots DESC";
	
	$GoalieSQLSaveStat = $pdoConnect->prepare($prepareSQL);
	$GoalieSQLSaveStat->bindParam(':gameid', $gameselect, PDO::PARAM_INT );
	$GoalieSQLSaveStat->execute();
	$GoalieBoxScore = $GoalieSQLSaveStat->fetchAll(PDO::FETCH_ASSOC);
	
return $GoalieBoxScore;
}

function GetTeamStats($idteamset, $subseasonset) {
	$pdoConnect = createDBH();
	
	$SummaryTeamFetch = $pdoConnect->prepare('SELECT idTeamAssignmentkey, SUM(TeamGamesPlayed), SUM(TeamWins), SUM(TeamLoss), SUM(TeamTie), SUM(TeamOTLoss), SUM(TeamPts), SUM(TeamGoals), SUM(TeamGoalsAgainst), (SUM(TeamGoals)-SUM(TeamGoalsAgainst)) AS GD, SUM(TeamShots), IF(SUM(TeamShots) > 0, ROUND(SUM(TeamShots)/SUM(TeamGamesPlayed),2), 0) AS SPG, SUM(TeamShotsAgainst), IF(SUM(TeamShotsAgainst) > 0, ROUND(SUM(TeamShotsAgainst)/SUM(TeamGamesPlayed), 2), 0) As SAPG, (SUM(TeamShots)-SUM(TeamShotsAgainst)) AS SD, 
	IF(SUM(TeamShots) > 0, ROUND((SUM(TeamGoals)-SUM(EmptyNetGoal))/(SUM(TeamShots)-SUM(EmptyNetGoal))*100,
	1), 0) 
	AS ShotEff, 
	IF(SUM(TeamShotsAgainst) > 0, ROUND((1-(SUM(TeamGoalsAgainst)-SUM(EmptyNetGoalAllowed))/(SUM(TeamShotsAgainst)-SUM(EmptyNetGoalAllowed)))*100,1), 0)
	AS DefEff, SUM(TeamPIM), SUM(TeamPN), SUM(TeamPIMA), SUM(TeamPNA), SUM(TeamPPChance), SUM(TeamPPGoal), ROUND((SUM(TeamPPGoal)/SUM(TeamPPChance)*100),1) AS PowerplayConversion, SUM(TeamPKChance), SUM(TeamPPGAllowed), ROUND(((1-(SUM(TeamPPGAllowed)/SUM(TeamPKChance)))*100),1) AS PKilling, SUM(SHG), SUM(SHGAllowed) FROM teamstatassignment WHERE idTeamAssignmentkey = :idTeam GROUP BY idTeamAssignmentkey');
	#$SummaryTeamFetch->bindParam(':idsubseason', $subseasonset, PDO::PARAM_INT );
	$SummaryTeamFetch->bindParam(':idTeam', $idteamset, PDO::PARAM_INT );
	$SummaryTeamFetch->execute();
	
	$SummaryStats = $SummaryTeamFetch->fetch(PDO::FETCH_ASSOC);
	return $SummaryStats;
}

function JSONRankingRecap($dataarray, $subseason, $team) {
	$pdoConnect = createDBH();

	#levelid is from header data
	#division is from header data
	$GetDivisionandLevel = $pdoConnect->prepare('SELECT idLevelSeasonkey, TeamDivision, TeamGamesPlayed FROM teamstatassignment WHERE idTeamAssignmentkey = :teamid LIMIT 1');
	#$GetDivisionandLevel = $pdoConnect->prepare('SELECT idLevelSeasonkey, TeamDivision, TeamGamesPlayed FROM teamstatassignment WHERE idTeamAssignmentkey = :teamid AND idsubseasonkey = :subseasonid LIMIT 1');
	$GetDivisionandLevel->bindParam(':teamid', $team, PDO::PARAM_INT);
	#$GetDivisionandLevel->bindParam(':subseasonid', $subseason, PDO::PARAM_INT);
	$GetDivisionandLevel->execute();
	$fetchedDivisionandLevel = $GetDivisionandLevel->fetch(PDO::FETCH_ASSOC);

if ($fetchedDivisionandLevel['TeamGamesPlayed'] > 0 )
    {
    $rankLevelid = $fetchedDivisionandLevel['idLevelSeasonkey'];
    if ($subseason == 1) {$rankDivisionid = $fetchedDivisionandLevel['TeamDivision'];} else {$rankDivisonid = NULL;}
    
    $teamArrayRankings = array();
    #Need to extract division or level rank
    $ShotEffArray = makeStatArray ($dataarray, 'ShotEff', 1, 'Percent');
    $ShotEffRank = getRanking($ShotEffArray, $team, $rankLevelid, $rankDivisionid);
    if (!empty($ShotEffRank['DivisionRank'])) {$teamArrayRankings['ShotEff'] = $ShotEffRank['DivisionRank'];}
    else {$teamArrayRankings['ShotEff'] = $ShotEffRank['LevelRank'];}
    
    $DefEffArray = makeStatArray ($dataarray, 'DefEff', 1, 'Percent');
    $DefEffRank = getRanking($DefEffArray, $team, $rankLevelid, $rankDivisionid);
    if (!empty($DefEffRank['DivisionRank'])) {$teamArrayRankings['DefEff'] = $DefEffRank['DivisionRank'];}
    else {$teamArrayRankings['DefEff'] = $DefEffRank['LevelRank'];}

	$ShotPGArray = makeStatArray ($dataarray, 'SPG', 1, NULL);
    $ShotPGRank = getRanking($ShotPGArray, $team, $rankLevelid, $rankDivisionid);
    if (!empty($ShotDiffRank['DivisionRank'])) {$teamArrayRankings['SPG'] = $ShotPGRank['DivisionRank'];}
    else {$teamArrayRankings['SPG'] = $ShotPGRank['LevelRank'];}

	$ShotAPGArray = makeStatArray ($dataarray, 'SAPG', 1, NULL);
    $ShotAPGRank = getRanking($ShotAPGArray, $team, $rankLevelid, $rankDivisionid);
    if (!empty($ShotDiffRank['DivisionRank'])) {$teamArrayRankings['SAPG'] = $ShotAPGRank['DivisionRank'];}
    else {$teamArrayRankings['SAPG'] = $ShotAPGRank['LevelRank'];}
    
    $ShotDiffArray = makeStatArray ($dataarray, 'SD', 1, 'PlusMinus');
    $ShotDiffRank = getRanking($ShotDiffArray, $team, $rankLevelid, $rankDivisionid);
    if (!empty($ShotDiffRank['DivisionRank'])) {$teamArrayRankings['SD'] = $ShotDiffRank['DivisionRank'];}
    else {$teamArrayRankings['SD'] = $ShotDiffRank['LevelRank'];}
    
    $GoalsForArray = makeStatArray ($dataarray, 'SUM(TeamGoals)', 1, NULL);
    $GoalsForRank = getRanking($GoalsForArray, $team, $rankLevelid, $rankDivisionid);
    if (!empty($GoalsForRank['DivisionRank'])) {$teamArrayRankings['SUM(TeamGoals)'] = $GoalsForRank['DivisionRank'];}
    else {$teamArrayRankings['SUM(TeamGoals)'] = $GoalsForRank['LevelRank'];}
    
    $GoalsAgainstArray = makeStatArray ($dataarray, 'SUM(TeamGoalsAgainst)', 0, NULL);
    $GoalsAgainstRank = getRanking($GoalsAgainstArray, $team, $rankLevelid, $rankDivisionid);
    if (!empty($GoalsAgainstRank['DivisionRank'])) {$teamArrayRankings['SUM(TeamGoalsAgainst)'] = $GoalsAgainstRank['DivisionRank'];}
    else {$teamArrayRankings['SUM(TeamGoalsAgainst)'] = $GoalsAgainstRank['LevelRank'];}
    
    $GoalsDiffArray = makeStatArray ($dataarray, 'GD', 1, 'PlusMinus');
    $GoalsDiffRank = getRanking($GoalsDiffArray, $team, $rankLevelid, $rankDivisionid);
    if (!empty($GoalsDiffRank['DivisionRank'])) {$teamArrayRankings['GD'] = $GoalsDiffRank['DivisionRank'];}
    else {$teamArrayRankings['GD'] = $GoalsDiffRank['LevelRank'];}
    
    $PPArray = makeStatArray ($dataarray, 'PPConvert', 1, 'Percent');
    $PPRank = getRanking($PPArray, $team, $rankLevelid, $rankDivisionid);
    if (!empty($PPRank['DivisionRank'])) {$teamArrayRankings['PPConvert'] = $PPRank['DivisionRank'];}
    else {$teamArrayRankings['PPConvert'] = $PPRank['LevelRank'];}
    
    $PKArray = makeStatArray ($dataarray, 'PKConvert', 1, 'Percent');
    $PKRank = getRanking($PKArray, $team, $rankLevelid, $rankDivisionid);
    if (!empty($PKRank['DivisionRank'])) {$teamArrayRankings['PKConvert'] = $PKRank['DivisionRank'];}
	    else {$teamArrayRankings['PKConvert'] = $PKRank['LevelRank'];}
	} else $teamArrayRankings=array('ShotEff' => '--', 'DefEff' => '--', 'SD' => '--', 'SUM(TeamGoals)' => '--', 'SUM(TeamGoalsAgainst)' => '--', 'GD' => '--', 'PPConvert' => '--','PKConvert' => '--');
return $teamArrayRankings;
}

function JSONsetrecapmenu($idgame, $gamedate, $hometeam, $hometeamname, $awayteam, $awayteamname) {
	$pdoConnect = createNDBH();
	$setTime = strtotime($gamedate);
	if ($gamedate >= strtotime('now')) {$setMenuText = 'Game Preview';}
	elseif ($gamedate < strtotime('now')) {$setMenuText = 'Box Score';}

	$TestRecap = $pdoConnect->prepare('SELECT idNews, NewsDate, NewsTitle, NewsItem, NewsAuthor, NewsAuthorLink, TeamAssignmentidAuthor FROM news JOIN newsdeliver ON news.idNews = newsdeliver.NewsidRef WHERE newsApprove = 1 AND Target = "game" AND Targetid = :gameid');
	$TestRecap->bindParam(':gameid', $idgame, PDO::PARAM_INT);
	$TestRecap->execute();
	
	$JSONMenuarray = array();
	$CountTestRecap = $TestRecap->rowCount();
	if ($CountTestRecap > 0) {
		
		$JSONMenuarray[] = array('link' => 'gamerecap?gameID='.$idgame, 'text' => $setMenuText);
		while ($Recapitem = $TestRecap->fetch(PDO::FETCH_ASSOC)) {
			if ($Recapitem['TeamAssignmentidAuthor'] == $hometeam)	{
				$JSONMenuarray[] = array('link' => 'gamerecap?gameID='.$idgame.'&recap='.$hometeam, 'text' => $hometeamname.' Game Recap');
			} elseif ($Recapitem['TeamAssignmentidAuthor'] == $awayteam) {
				$JSONMenuarray[] = array('link' => 'gamerecap?gameID='.$idgame.'&recap='.$awayteam, 'text' => $awayteamname.' Game Recap');
			} else {
				$JSONMenuarray[] = array('link' => 'gamerecap?gameID='.$idgame.'&recap=0', 'text' => 'Game Recap');
			}
		}
	}
return $JSONMenuarray;
}

function JSONgameArticle($targetclass, $recapid, $idgame) {
	$pdoConnect = createNDBH();

	$FetchRecap = $pdoConnect->prepare('SELECT idNews, NewsDate, NewsTitle, NewsItem, NewsAuthor, NewsAuthorLink, TeamAssignmentidAuthor, newsImageLibrary, newsYouTube FROM news JOIN newsdeliver ON news.idNews = newsdeliver.NewsidRef WHERE Target = :targetclass AND Targetid = :gameid AND TeamAssignmentidAuthor = :idAuthor');
	$FetchRecap->bindParam(':gameid', $idgame, PDO::PARAM_INT);
	$FetchRecap->bindParam(':idAuthor', $recapid, PDO::PARAM_INT);
	$FetchRecap->bindParam(':targetclass', $targetclass, PDO::PARAM_STR);
	$FetchRecap->execute();
	
	#$RecapFetched = $FetchRecap->fetchAll(PDO::FETCH_ASSOC);

	$RecapOutput = array();

	while ($recapArticle = $FetchRecap->fetch(PDO::FETCH_ASSOC)) {
		$recapArticle['NewsArticle'] = array_filter(explode("\n", $recapArticle['NewsItem']));
		$RecapOutput[] = $recapArticle;
	}

return $RecapOutput;
}

$pdoConnect = createDBH();
$recapid = isset($_GET['recap']) ? $_GET['recap'] : NULL;
$gameid = $_GET['gameID'];

$setcondition = isset($_GET['recap']) ? 'recap' : NULL;

$gameData = JSONgameData($gameid);
#echo $gameData['Googleidmap'];
$CombineDateTime = $gameData['GameDate'].' '.$gameData['GameStartTime'];
$GameDateTime = strtotime($CombineDateTime);

if (($GameDateTime >= strtotime('now')) && (!isset($setcondition))) {$setcondition = 'preview';}
elseif (($GameDateTime < strtotime('now')) && (!isset($setcondition))) {
	if ($gameData['GameFinished'] == 1) {
		$setcondition = 'boxscore';
	} elseif ($gameData['GameFinished'] == 0) {$setcondition = 'waiting';}
}

$JSONRecapMenu = JSONsetrecapmenu($gameid, $GameDateTime, $gameData['HomeTeam'], $gameData['HomeTeamName'], $gameData['AwayTeam'], $gameData['AwayTeamName']);

$subseasongame = $gameData['idsubseasonkey'];
$Homestats = GetTeamStats($gameData['HomeTeam'], $subseasongame);
$Awaystats = GetTeamStats($gameData['AwayTeam'], $subseasongame);


$gameScoretable = JSONgetGameScore($gameid);
$JSONscore = buildJSONdataTable ($gameScoretable, 'tablesorter', null, array(array('link' => 'team?teamid=', 'linktarget' => 'TeamID', 'linkname' => 'Team Name')), array(array('image'=>'TeamImage', 'imagetarget'=>'Team Name')), null);


$JSONOutput = array(
	'gamedata' => $gameData,
	'gamePhase' => $setcondition,
	'gamerecaplinks' => $JSONRecapMenu,
	'homeStats' => $Homestats,
	'awayStats' => $Awaystats,
	'gamescore' => $JSONscore
);

switch ($setcondition) 
{
	case 'recap':
		$recapID = $_GET['recap'];
		$recapArticle = JSONgameArticle('game', $recapID, $gameid);

		$JSONOutput += ['recapArticle' => $recapArticle];

	echo JSON_encode($JSONOutput);
	break;
	case 'preview':

		$GetAllTeamData = teamstatdataset($gameData['idSeasonkey']);
		$homeRanking = JSONRankingRecap($GetAllTeamData, $subseasongame, $gameData['HomeTeam']);
		$awayRanking = JSONRankingRecap($GetAllTeamData, $subseasongame, $gameData['AwayTeam']);

		$JSONOutput += [
			'homeRankings' => $homeRanking,
			'awayRankings' => $awayRanking
		];
	echo JSON_encode($JSONOutput);
	break;
	case 'boxscore': 

		$fetchgoals = JSONgetGoals($gameid);
		#print_r($fetchgoals);
		$JSONgoals = buildJSONdataTable ($fetchgoals, 'tablesorter', null, array(array('link' => 'playercard?playerteamid=', 'linktarget' => 'goalid', 'linkname' => 'Name'), array('link' => 'playercard?playerteamid=', 'linktarget' => 'assist1ID', 'linkname' => 'Assist1'), array('link' => 'playercard?playerteamid=', 'linktarget' => 'assist2ID', 'linkname' => 'Assist2'), array('link' => 'team?teamid=', 'linktarget' => 'teamid', 'linkname' => 'Team')), null, null);

		$fetchpenalties = JSONgetPenalties($gameid);
		$outputPenalties = JSONPenaltyOutput($fetchpenalties);

		$JSONpenalties = buildJSONdataTable ($outputPenalties, 'tablesorter', null, array(array('link' => 'team?teamid=', 'linktarget' => 'teamid', 'linkname' => 'Team'), array('link' => 'playercard?playerteamid=', 'linktarget' => 'playerid', 'linkname' => 'Player')), null, null);

		$homePPArray = JSONgetPPboxScore($fetchgoals, $fetchpenalties, $gameData['HomeTeam'], $gameData['HomeTeamName'], $gameData['HomeTeamAHASmall']);
		$awayPPArray = JSONgetPPboxScore($fetchgoals, $fetchpenalties, $gameData['AwayTeam'], $gameData['AwayTeamName'], $gameData['AwayTeamAHASmall']);
		$PPBox = array($homePPArray, $awayPPArray);
		$JSONPPBox = buildJSONdataTable ($PPBox, 'tablesorter', null, array(array('link' => 'team?teamid=', 'linktarget' => 'teamid', 'linkname' => 'Team')), array(array('image'=>'teamimage', 'imagetarget'=>'Team')), null, null);
		$GoalieGameStats = JSONGoalieGameStats($gameid);
		$JSONgoaliestats = buildJSONdataTable ($GoalieGameStats, 'tablesorter', null, array(array('link' => 'playercard?playerteamid=', 'linktarget' => 'idPlayerAssignment', 'linkname' => 'Player'), array('link' => 'team?teamid=', 'linktarget' => 'idTeamAssignmentkey', 'linkname' => 'Team')), null, null);
	
		$homeShotsTable = array('Team' => $gameData['HomeTeamName'], 'teamid' => $gameData['HomeTeam'], '1st' => $gameData['Home1stShots'], '2nd' => $gameData['Home2ndShots'], '3rd' => $gameData['Home3rdShots']);
		$awayShotsTable = array('Team' => $gameData['AwayTeamName'], 'teamid' => $gameData['AwayTeam'], '1st' => $gameData['Away1stShots'], '2nd' => $gameData['Away2ndShots'], '3rd' => $gameData['Away3rdShots']);

		if ($gameData['AwayOTShots'] == 0 && $gameData['HomeOTShots'] == 0) {
			$homeShotsTable += ['Total' => $gameData['HomeShots']];
			$awayShotsTable += ['Total' => $gameData['AwayShots']];
		} elseif ($gameData['AwayOTShots'] > 0 || $gameData['HomeOTShots'] > 0) {
			$homeShotsTable += ['OT' => $gameData['HomeOTShots'], 'Total' => $gameData['HomeShots']];
			$awayShotsTable += ['OT' => $gameData['AwayOTShots'], 'Total' => $gameData['AwayShots']];
		}
		$boxShots = array($homeShotsTable, $awayShotsTable);
		$JSONShots = buildJSONdataTable ($boxShots, 'tablesorter', null, array(array('link' => 'team?teamid=', 'linktarget' => 'teamid', 'linkname' => 'Team')), null, null);

		$homeboxscore = JSONboxscore($gameid, $gameData['HomeTeam']);
		$awayboxscore = JSONboxscore($gameid, $gameData['AwayTeam']);
		
		$JSONhomeBox = buildJSONdataTable ($homeboxscore, 'tablesorter', null, array(array('link' => 'playercard?playerteamid=', 'linktarget' => 'playerid', 'linkname' => 'Name')), null, null);
		$JSONawayBox = buildJSONdataTable ($awayboxscore, 'tablesorter', null, array(array('link' => 'playercard?playerteamid=', 'linktarget' => 'playerid', 'linkname' => 'Name')), null, null);
				
		$JSONOutput += [
			'gameevents' => null,
			'goalevents' => $JSONgoals,
			'penaltyevents' => $JSONpenalties,
			'specialteamsbox' => $JSONPPBox, 
			'gameshots' => $JSONShots,
			'goalieboxscore' => $JSONgoaliestats,
			'hometeamboxscore' => $JSONhomeBox,
			'awayteamboxscore' => $JSONawayBox
		];
		
	echo JSON_encode($JSONOutput);
	break;
	case 'waiting': 
		$JSONOutput += [
			'message' => 'Waiting for official results from AHA Offices'
		];
		echo JSON_encode($JSONOutput);
	break;
}

?>