<?php 

include_once 'connection.php';

//include 'html.php';


$setDBH = createDBH();
$setNDBH = createNDBH();

$currentlevel = ( isset( $_GET['levelid'] ) && is_numeric( $_GET['levelid'] ) ) ? intval( $_GET['levelid'] ) : 0;
$currentdivision = ( isset( $_GET['divid'] ) )  ?  $_GET['divid']  : NULL;
$currentseason = ( isset( $_GET['seasonid'] ) && is_numeric( $_GET['seasonid'] ) ) ? intval( $_GET['seasonid'] ) : 0;
$currentteam = ( isset( $_GET['teamid'] ) && is_numeric( $_GET['teamid'] ) ) ? intval( $_GET['teamid'] ) : 0;
$SQLWhere = ' ';

if ($currentteam != 0) 
{ 
  $SQLWhere .= ' AND (HomeTeam = :idTeam or AwayTeam = :idTeam)'; 
} 
elseif($currentlevel != 0 AND !empty($currentdivision)) 
{ 
  $SQLWhere .= ' AND hometeamstatassignment.idLevelSeasonkey = :idLevel AND hometeamstatassignment.TeamDivision = :division'; 
} 
elseif ($currentlevel != 0) 
{ 
  $SQLWhere .= ' AND hometeamstatassignment.idLevelSeasonkey = :idLevel'; 
} 
if ($currentseason != 0) 
{ 
  $SQLWhere .= ' AND game.idSeasonkey = :idSeason'; 
}



function JSONgetResults($SQLWhere) {
	$setDBH = createDBH();
	$setNDBH = createNDBH();
	
	$currentlevel = ( isset( $_GET['levelid'] ) && is_numeric( $_GET['levelid'] ) ) ? intval( $_GET['levelid'] ) : 0;
	$currentdivision = ( isset( $_GET['divid'] ) )  ?  $_GET['divid']  : NULL;
	$currentseason = ( isset( $_GET['seasonid'] ) && is_numeric( $_GET['seasonid'] ) ) ? intval( $_GET['seasonid'] ) : 0;
	$currentteam = ( isset( $_GET['teamid'] ) && is_numeric( $_GET['teamid'] ) ) ? intval( $_GET['teamid'] ) : 0;

	$PastGamesSQLStart = 'SELECT DISTINCT idGame, GameDate, GameStartTime, LocationName, Rink, SubSeasonName, HomeTeam, hometeam.TeamName as HomeTeamName, hometeam.externalLargeImage AS HomeLargeImage, hometeam.externalSmallImage AS HomeSmallImage, homeThemeTable.cssCustom AS HomeTheme, AwayTeam, awayteam.TeamName as AwayTeamName, awayteam.externalLargeImage AS AwayLargeImage, awayteam.externalSmallImage AS AwaySmallImage, awayThemeTable.cssCustom AS AwayTheme, HomeGoals, AwayGoals, HomeShots, AwayShots, HomeOTShots, AwayOTShots, Home1stGoals, Home2ndGoals, Home3rdGoals, HomeOTGoals, Away1stGoals, Away2ndGoals, Away3rdGoals, AwayOTGoals, hometeamstatassignment.TeamWins AS HomeWins, hometeamstatassignment.TeamLoss AS HomeLoss, hometeamstatassignment.TeamTie AS HomeTie, hometeamstatassignment.TeamOTLoss AS HomeOTLoss, hometeamstatassignment.TeamPts AS HomePts, awaystatteamassignment.TeamWins AS AwayWins, awaystatteamassignment.TeamLoss AS AwayLoss, awaystatteamassignment.TeamTie AS AwayTie, awaystatteamassignment.TeamOTLoss AS AwayOTLoss, awaystatteamassignment.TeamPts AS AwayPts, HomeForfeit, AwayForfeit, OTDetected 
	FROM game 
	JOIN teamassignment AS hometeamassignment ON game.HomeTeam = hometeamassignment.idTeamAssignment 
	JOIN teamstatassignment AS hometeamstatassignment ON hometeamassignment.idTeamAssignment = hometeamstatassignment.idTeamAssignmentkey AND game.idsubseasonkey = hometeamstatassignment.idsubseasonkey
	JOIN team as hometeam ON hometeamassignment.idTeamkey = hometeam.idTeam
	LEFT JOIN jjglover_GLSHockey_NewsCss.cssteamassignment AS homeThemeTable ON hometeamassignment.idTeamAssignment = homeThemeTable.TeamidRef AND homeThemeTable.TeamtableRef = "teamassignment"
	JOIN teamassignment AS awayteamassignment ON game.AwayTeam = awayteamassignment.idTeamAssignment 
	JOIN teamstatassignment AS awaystatteamassignment ON awayteamassignment.idTeamAssignment = awaystatteamassignment.idTeamAssignmentkey AND game.idsubseasonkey = awaystatteamassignment.idsubseasonkey
	JOIN team as awayteam ON awayteamassignment.idTeamkey = awayteam.idTeam
	LEFT JOIN jjglover_GLSHockey_NewsCss.cssteamassignment as awayThemeTable ON awayteamassignment.idTeamAssignment = awayThemeTable.TeamidRef AND awayThemeTable.TeamtableRef = "teamassignment"
	JOIN location ON game.idLocationkey = location.idLocation 
	JOIN subseason ON game.idsubseasonkey = subseason.idsubseason WHERE GameDate <= CURDATE() AND GameFinished = 1';
	$PastGamesSQLEnd = ' AND ((hometeamstatassignment.teamStartDate IS NULL OR game.GameDate > hometeamstatassignment.teamStartDate) AND (hometeamstatassignment.teamEndDate IS NULL OR game.GameDate < hometeamstatassignment.teamEndDate)) AND ((awaystatteamassignment.teamStartDate IS NULL OR game.GameDate > awaystatteamassignment.teamStartDate) AND (awaystatteamassignment.teamEndDate IS NULL OR game.GameDate < awaystatteamassignment.teamEndDate)) ORDER BY GameDate DESC, GameStartTime DESC LIMIT 7';

	$PastGames = $setDBH->prepare($PastGamesSQLStart.$SQLWhere.$PastGamesSQLEnd);
	#echo $PastGamesSQLStart.$SQLWhere.$PastGamesSQLEnd;
	
	if ($currentteam !=0)
	{
		$PastGames->bindParam(':idTeam', $currentteam, PDO::PARAM_INT);
	}
	elseif ($currentlevel != 0 AND !empty($currentdivision))
	{
		$PastGames->bindParam(':idLevel', $currentlevel, PDO::PARAM_INT);
		$PastGames->bindParam(':division', $currentdivision, PDO::PARAM_STR);
	}
	elseif ($currentlevel != 0)
	{
		$PastGames->bindParam(':idLevel', $currentlevel, PDO::PARAM_INT);
	}
	if ($currentseason != 0)
	{
		$PastGames->bindParam(':idSeason', $currentseason, PDO::PARAM_INT);
	}
	$PastGames->execute();
	$pastgameCount = $PastGames->rowCount();

	$pastResultsArray = array();


	while ($pastresults = $PastGames->fetch(PDO::FETCH_ASSOC)) {
		$FetchRecapSQL = 'SELECT idNews, NewsDate, NewsTitle, NewsItem, NewsAuthor, NewsAuthorLink, newsYoutube, TeamAssignmentidAuthor FROM news JOIN newsdeliver ON news.idNews = newsdeliver.NewsidRef WHERE newsApprove = 1 AND Target = "game" AND Targetid = :gameid';
			if ($currentteam != 0) {$FetchRecapSQL .= ' AND TeamAssignmentidAuthor = :currentteam';}
			$FetchRecap = $setNDBH->prepare($FetchRecapSQL);
			$FetchRecap->bindParam(':gameid', $pastresults['idGame'], PDO::PARAM_INT);
			if ($currentteam != 0) {$FetchRecap->bindParam(':currentteam', $currentteam, PDO::PARAM_INT);}
			$FetchRecap->execute();
			$CountRecaps = $FetchRecap->rowCount();

			$Recaps = array();
			if ($CountRecaps > 0)
			{
				$Recaps = array();
				while ($GameRecap = $FetchRecap->fetch(PDO::FETCH_ASSOC))
				{
					if ($pastresults['HomeTeam'] == $GameRecap['TeamAssignmentidAuthor']) {$GameRecap += ['side'=>'Home'];}
					elseif ($pastresults['AwayTeam'] == $GameRecap['TeamAssignmentidAuthor']) {$GameRecap += ['side'=> 'Away'];}
					else {$GameRecap += ['side'=> null];}
					$Recaps[] = $GameRecap;
				}
			}
		$pastresults += ['recaps'=>$Recaps];
		
		$gameWinnerSQL = 'SELECT gameevents.idTeamAssignmentkey, GoalScorer, goalPlayer.idPlayerAssignment, goalName.PlayerFirst, goalName.playerLast, goalCSS.imagelocation AS goalImage, PlayerAssist, assistPlayer.idPlayerAssignment AS assistPlayerID, assistName.PlayerFirst AS assistPlayerFirst, assistName.playerLast AS assistPlayerLast, assistCSS.imagelocation AS assistImage,PlayerAssist2, assist2Player.idPlayerAssignment AS assist2PlayerID, assist2Name.PlayerFirst AS assist2PlayerFirst, assist2Name.playerLast AS assist2PlayerLast, assist2CSS.imagelocation AS assist2Image, goalclasskey, Goaltype, GoalTypeShort FROM game 
		JOIN gameevents ON game.idGame = gameevents.idGamekey 
		JOIN goalevent ON gameevents.idGameEvents = goalevent.idGameEvents
		JOIN goalclassevent ON goalevent.idGameEvents = goalclassevent.goaleventkey
        JOIN goalclass ON goalclass.idGoalClass = goalclassevent.goalclasskey
        JOIN playerassignment AS goalPlayer ON goalPlayer.idPlayerAssignment = goalevent.GoalScorer
        JOIN players AS goalName ON goalName.idPlayers = goalPlayer.idPlayerskey
		LEFT JOIN jjglover_GLSHockey_NewsCss.cssplayerassignment AS goalCSS ON goalCSS.PlayeridRef = goalPlayer.idPlayerAssignment AND goalCSS.PlayertableRef = "playerassignment"
        LEFT JOIN playerassignment AS assistPlayer ON assistPlayer.idPlayerAssignment = goalevent.PlayerAssist
        LEFT JOIN players AS assistName ON assistName.idPlayers = assistPlayer.idPlayerskey
        LEFT JOIN jjglover_GLSHockey_NewsCss.cssplayerassignment AS assistCSS ON assistCSS.PlayeridRef = assistPlayer.idPlayerAssignment AND assistCSS.PlayertableRef = "playerassignment"
        LEFT JOIN playerassignment AS assist2Player ON assist2Player.idPlayerAssignment = goalevent.PlayerAssist2
        LEFT JOIN players AS assist2Name ON assist2Name.idPlayers = assist2Player.idPlayerskey
        LEFT JOIN jjglover_GLSHockey_NewsCss.cssplayerassignment AS assist2CSS ON assist2CSS.PlayeridRef = assist2Player.idPlayerAssignment AND assist2CSS.PlayertableRef = "playerassignment"
        WHERE idGame=:gameid AND (goalclasskey = 6 or goalclasskey = 7);';
		$FetchWinningGoal = $setDBH->prepare($gameWinnerSQL);
		$FetchWinningGoal->bindParam(':gameid', $pastresults['idGame'], PDO::PARAM_INT);
		$FetchWinningGoal->execute();
		$pastresults += ['GameWinner' => $FetchWinningGoal->fetchAll(PDO::FETCH_ASSOC)];

		$pastResultsArray[] = $pastresults;
	}
return $pastResultsArray;
}

function JSONgetNews() {
	$setDBH = createDBH();
	$setNDBH = createNDBH();

	function getLevelID($seasonID, $levelID) {
		$setDBH = createDBH();

		$levelFetchSQL = 'SELECT SeasonLevels.idSeasonLevel FROM SeasonLevels WHERE idSeason=:seasonid AND idLevel=:levelid LIMIT 1;';
		$fetchLevelID = $setDBH->prepare($levelFetchSQL);
		$fetchLevelID->bindParam(':seasonid', $seasonID, PDO::PARAM_INT);
		$fetchLevelID->bindParam(':levelid', $levelID, PDO::PARAM_INT);
		$fetchLevelID->execute();
		$getLevelID = $fetchLevelID->fetch(PDO::FETCH_ASSOC);

		return $getLevelID['idSeasonLevel'];
	}

	$currentlevel = ( isset( $_GET['levelid'] ) && is_numeric( $_GET['levelid'] ) ) ? intval( $_GET['levelid'] ) : 0;
	$currentdivision = ( isset( $_GET['divid'] ) )  ?  $_GET['divid']  : NULL;
	$currentseason = ( isset( $_GET['seasonid'] ) && is_numeric( $_GET['seasonid'] ) ) ? intval( $_GET['seasonid'] ) : 0;
	$currentteam = ( isset( $_GET['teamid'] ) && is_numeric( $_GET['teamid'] ) ) ? intval( $_GET['teamid'] ) : 0;
	
	$NewsSQLStart = 'SELECT idNews, NewsDate, NewsTitle, NewsItem, NewsAuthor, NewsAuthorLink, newsYoutube, Target, Targetid, TargetDivision, blogName, TeamAssignmentidAuthor ';
	$NewsSQLFrom = 'FROM news JOIN newsdeliver ON news.idNews = newsdeliver.NewsidRef LEFT JOIN blog ON newsdeliver.Targetid = blog.idblog AND Target = "blog" ';
	$NewsSQLWhere = 'WHERE newsApprove = 1 AND NewsDate <= CURDATE() AND NewsDate > CURDATE()-INTERVAL 30 DAY ';
	if ($currentteam != 0)
	{
		$NewsSQLWhere .= ' AND ((Target = "team" AND Targetid = :idTeam) OR (Target = "playerteam" AND NoPropogate = 1 AND Targetid IN (SELECT idPlayerAssignment FROM jjglover_GLSHockey.playerassignment WHERE idTeamAssignmentkey = :idTeam)))' ;
	}
	elseif($currentlevel != 0 AND !empty($currentdivision))
	{
		$levelTarget = getLevelID($currentseason, $currentlevel);
		
		$NewsSQLWhere .= ' AND ((Target = "level" AND Targetid = :idLevel AND TargetDivision = :Division)
		 OR (Target = "level" AND Targetid = :idLevel AND TargetDivision IS NULL) OR (Target = "team" AND Targetid IN (SELECT idTeamAssignment FROM jjglover_GLSHockey.teamstatview WHERE idSeason = :idSeason AND idLevelSeason = :teamLevel AND Division = :Division) AND NoPropogate = 0) OR (Target = "game" AND TeamAssignmentidAuthor IN (SELECT idTeamAssignment FROM jjglover_GLSHockey.teamstatview WHERE idSeason = :idSeason AND idLevelSeason = :idLevel AND Division = :Division) AND NoPropogate = 0) OR (Target = "season" AND Targetid = :idSeason))';
	}
	elseif ($currentlevel != 0)
	{
		$levelTarget = getLevelID($currentseason, $currentlevel);
		
		$NewsSQLWhere .= ' AND ((Target = "level" AND Targetid = :idLevel)
		 OR (Target = "team" AND targetid IN (SELECT idTeamAssignment FROM jjglover_GLSHockey.teamstatview WHERE idSeason = :idSeason and idLevelSeason = :teamLevel) AND NoPropogate = 0) OR (Target = "game" AND TeamAssignmentidAuthor IN (SELECT idTeamAssignment FROM jjglover_GLSHockey.teamstatview WHERE idSeason = :idSeason AND idLevelSeason = :idLevel) AND NoPropogate = 0) OR (Target = "season" AND Targetid = :idSeason))';
	}
	elseif ($currentseason != 0)
	{
		$NewsSQLWhere .= ' AND ((Target = "season" AND Targetid = :idSeason)
		 OR (Target = "blog")
		 OR (Target = "level" AND Targetid IN (SELECT DISTINCT idLevelSeason FROM jjglover_GLSHockey.teamstatview WHERE idSeason = :idSeason) AND NoPropogate = 0)
		 OR (Target = "team" AND Targetid IN (SELECT idTeamAssignment FROM jjglover_GLSHockey.teamstatview WHERE idSeason = :idSeason) AND NoPropogate = 0)
		 OR (Target = "game" AND TeamAssignmentidAuthor IN (SELECT idTeamAssignment FROM jjglover_GLSHockey.teamstatview WHERE idSeason = :idSeason) AND NoPropogate = 0))';
	}
	
	#$NewsSQLStart = 'SELECT idNews, NewsDate, NewsTitle, NewsItem, NewsAuthor, NewsAuthorLink, Target, Targetid, TargetDivision FROM news JOIN newsdeliver ON news.idNews = newsdeliver.NewsidRef WHERE newsApprove = 1 AND NewsDate <= CURDATE() AND NewsDate > CURDATE()-INTERVAL 31 DAY ';
	$NewsSQLOrder = ' ORDER BY NewsDate DESC LIMIT 2';
	#echo $NewsSQLStart.$NewsSQLWhere.$NewsSQLOrder;
	
	
	$NewsFetch = $setNDBH->prepare($NewsSQLStart.$NewsSQLFrom.$NewsSQLWhere.$NewsSQLOrder);
	if ($currentteam != 0)
	{
		$NewsFetch->bindParam(':idTeam', $currentteam, PDO::PARAM_INT);
	}
	elseif ($currentlevel != 0 AND !empty($currentdivision))
	{
		$NewsFetch->bindParam(':idLevel', $levelTarget, PDO::PARAM_INT);
		$NewsFetch->bindParam(':teamLevel', $currentlevel, PDO::PARAM_INT);
		$NewsFetch->bindParam(':Division', $currentdivision, PDO::PARAM_STR);
		$NewsFetch->bindParam(':idSeason', $currentseason, PDO::PARAM_INT);
	}
	elseif ($currentlevel != 0)
	{
		$NewsFetch->bindParam(':idLevel', $levelTarget, PDO::PARAM_INT);
		$NewsFetch->bindParam(':teamLevel', $currentlevel, PDO::PARAM_INT);
		$NewsFetch->bindParam(':idSeason', $currentseason, PDO::PARAM_INT);
	}
	elseif ($currentseason != 0)
	{
		$NewsFetch->bindParam(':idSeason', $currentseason, PDO::PARAM_INT);
	}
	$NewsFetch->execute();
	$NewsCount = $NewsFetch->rowCount();
	
	#$NewsSection = $NewsFetch->fetchAll(PDO::FETCH_ASSOC);
	$outputNews = [];
	while ($row = $NewsFetch->fetch(PDO::FETCH_ASSOC)){
		$row['DisplayDate'] = date("M d, Y", strtotime($row['NewsDate']));

		if($row['Target'] == "season") {
			$SeasonFetch = $setDBH->prepare('SELECT SeasonDisplay FROM season WHERE idSeason = :idSeason');
			$SeasonFetch->bindParam(':idSeason', $row['Targetid'], PDO::PARAM_INT);
			$SeasonFetch->execute();
			$SeasonName = $SeasonFetch->fetch(PDO::FETCH_ASSOC);
			$row['DisplayTitle'] = $SeasonName['SeasonDisplay'].' News';
		} elseif ($row['Target'] == "level") {
			$LevelFetch = $setDBH->prepare('SELECT Level FROM level JOIN SeasonLevels ON SeasonLevels.idLevel = level.idLevelSeason WHERE idSeasonLevel = :idLevel');
			$LevelFetch->bindParam(':idLevel', $row['Targetid'], PDO::PARAM_INT);
			$LevelFetch->execute();
			$LevelName = $LevelFetch->fetch(PDO::FETCH_ASSOC);
			$row['DisplayTitle'] = $LevelName['Level'].' News';
		} elseif ($row['Target'] == "team") {
			$TeamFetch = $setDBH->prepare('SELECT TeamName FROM jjglover_GLSHockey.teamassignment JOIN jjglover_GLSHockey.team ON teamassignment.idTeamkey = team.idTeam WHERE idTeamAssignment = :idTeam');
			$TeamFetch->bindParam(':idTeam', $row['Targetid'], PDO::PARAM_INT);
			$TeamFetch->execute();
			$TeamName = $TeamFetch->fetch(PDO::FETCH_ASSOC);
			$row['DisplayTitle'] = $TeamName['TeamName'].' News';
		} elseif ($row['Target'] == "blog") {
			$row['DisplayTitle'] = $row['blogName'];
		}
		$outputNews[] = $row;
	}


return $outputNews;
}

function JSONgetfuture($SQLWhere) {
	$setDBH = createDBH();

	$currentlevel = ( isset( $_GET['levelid'] ) && is_numeric( $_GET['levelid'] ) ) ? intval( $_GET['levelid'] ) : 0;
	$currentdivision = ( isset( $_GET['divid'] ) )  ?  $_GET['divid']  : NULL;
	$currentseason = ( isset( $_GET['seasonid'] ) && is_numeric( $_GET['seasonid'] ) ) ? intval( $_GET['seasonid'] ) : 0;
	$currentteam = ( isset( $_GET['teamid'] ) && is_numeric( $_GET['teamid'] ) ) ? intval( $_GET['teamid'] ) : 0;

	$FutureGamesSQLStart = 'SELECT DISTINCT idGame, GameDate, GameStartTime, LocationName, Rink, SubSeasonName, HomeTeam, hometeam.TeamName as HomeTeamName, hometeam.externalSmallImage as HomeSmallImage, AwayTeam, awayteam.TeamName as AwayTeamName, awayteam.externalSmallImage AS AwaySmallImage FROM game 
	JOIN teamassignment AS hometeamassignment ON game.HomeTeam = hometeamassignment.idTeamAssignment 
	JOIN teamstatassignment AS hometeamstatassignment ON hometeamassignment.idTeamAssignment = hometeamstatassignment.idTeamAssignmentkey AND game.idsubseasonkey = hometeamstatassignment.idsubseasonkey
	JOIN team as hometeam ON hometeamassignment.idTeamkey = hometeam.idTeam
	JOIN teamassignment AS awayteamassignment ON game.AwayTeam = awayteamassignment.idTeamAssignment 
	JOIN teamstatassignment AS awaystatteamassignment ON awayteamassignment.idTeamAssignment = awaystatteamassignment.idTeamAssignmentkey AND game.idsubseasonkey = awaystatteamassignment.idsubseasonkey
	JOIN team as awayteam ON awayteamassignment.idTeamkey = awayteam.idTeam
	JOIN location ON game.idLocationkey = location.idLocation 
	JOIN subseason ON game.idsubseasonkey = subseason.idsubseason 
	WHERE GameDate >= CURDATE() AND GameFinished = 0';

	$FutureGamesSQLEnd = ' ORDER BY GameDate ASC, GameStartTime ASC LIMIT 6';

	$FutureGames = $setDBH->prepare($FutureGamesSQLStart.$SQLWhere.$FutureGamesSQLEnd);

	$FutureGameDiv = '';
	if ($currentteam != 0)
	{
		$FutureGames->bindParam(':idTeam', $currentteam, PDO::PARAM_INT);
	}
	elseif ($currentlevel != 0 AND !empty($currentdivision))
	{
		$FutureGames->bindParam(':idLevel', $currentlevel, PDO::PARAM_INT);
		$FutureGames->bindParam(':division', $currentdivision, PDO::PARAM_STR);
	}
	elseif ($currentlevel != 0)
	{
		$FutureGames->bindParam(':idLevel', $currentlevel, PDO::PARAM_INT);
	}
	if ($currentseason != 0)
	{
		$FutureGames->bindParam(':idSeason', $currentseason, PDO::PARAM_INT);
	}
	$FutureGames->execute();
	$futuregameCount = $FutureGames->rowCount();
	$FutureGameList = '';
	$futureresults = $FutureGames->fetchAll(PDO::FETCH_ASSOC);
return $futureresults;
}


switch ($_GET['step']) {
	case 0: 
		$futureGamesList = JSONgetfuture($SQLWhere);
		$JSONfutureList = array();
		
		foreach ($futureGamesList as $futureGames) {$JSONfutureList[] = array($futureGames);}

		echo json_encode($JSONfutureList);
		#echo 'test';
	break;
	case 1:
		$newsList = JSONgetNews();
		$JSONNewsList = array();
		foreach ($newsList as $newsArticle) {
			$newsArticle['NewsItem'] = array_filter(explode("\n", $newsArticle['NewsItem']));
			$JSONNewsList[] = array($newsArticle);
		}
		#print_r($newsList);
		echo json_encode($JSONNewsList);
		#echo 'test';
	break;
	case 2:
		$resultsList = JSONgetResults($SQLWhere);
		$JSONresultsList = array();
		foreach ($resultsList as $gameResult) {
			$JSONresultsList[] = array($gameResult);
		}

		echo json_encode($JSONresultsList);
		#echo 'test';
	break;
	default: echo 'failed Index error';
}

?>