<?php
#include 'header.php';
#include 'html.php';
include_once 'connection.php';

$currentteam = ( isset( $_GET['teamid'] ) && is_numeric( $_GET['teamid'] ) ) ? intval( $_GET['teamid'] ) : 0;
$currentseason = ( isset( $_GET['seasonid'] ) && is_numeric( $_GET['seasonid'] ) ) ? intval( $_GET['seasonid'] ) : 0;
$currentlevel = ( isset( $_GET['levelid'] ) && is_numeric( $_GET['levelid'] ) ) ? intval( $_GET['levelid'] ) : 0;
$currentdivision = isset($_GET['divid']) ? $_GET['divid'] : NULL;
$currentsubseason = ( isset( $_GET['subseasonid'] ) && is_numeric( $_GET['subseasonid'] ) ) ? intval( $_GET['subseasonid'] ) : 0;
if ($currentteam != 0)
{$currentfilter = ( isset( $_GET['filter'] ) && is_numeric( $_GET['filter'] ) ) ? intval( $_GET['filter'] ) : 0;}
else
{$currentfilter = ( isset( $_GET['filter'] ) && is_numeric( $_GET['filter'] ) ) ? intval( $_GET['filter'] ) : 2;}
$currentdate = ( isset( $_GET['date'] ) ) ? $_GET['date'] : date("Y-m-d");

$schedtype = ( isset( $_GET['type'] ) ) ?  $_GET['type']  : null;

$pageselect = ( isset( $_GET['page'] ) && is_numeric( $_GET['page'] ) ) ? intval( $_GET['page'] ) : 1;

function setSeasonRange($season) {
	$pdoconnect = createDBH();
	
	$setSchedSQL = 'SELECT SeasonStart, SeasonEnd FROM season WHERE idSeason = :seasonid LIMIT 1';
	$setSchedDates = $pdoconnect->prepare($setSchedSQL);
	$setSchedDates->bindParam(':seasonid', $season, PDO::PARAM_INT);
	$setSchedDates->execute();
	$SchedDates = $setSchedDates->fetch(PDO::FETCH_ASSOC);
	
return $SchedDates;
}


function JSONschedule ($type, $date, $minusdate, $seasoninactive) {
	function JSONgetRecaps($game, $hometeam, $awayteam) {
		$setNDBH = createNDBH();
	
		$FetchRecapSQL = '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';
		$FetchRecap = $setNDBH->prepare($FetchRecapSQL);
		$FetchRecap->bindParam(':gameid', $game, PDO::PARAM_INT);
		$FetchRecap->execute();
	
		$CountRecaps = $FetchRecap->rowCount();
		$Recaps = array();
		$recapFetched = '';
		$pastresults = array();

		if ($CountRecaps > 0)
		{
			$recapFetched = array();
			while ($GameRecap = $FetchRecap->fetch(PDO::FETCH_ASSOC))
			{
				if ($hometeam == $GameRecap['TeamAssignmentidAuthor']) {$GameRecap += ['side'=>'Home'];}
				elseif ($awayteam == $GameRecap['TeamAssignmentidAuthor']) {$GameRecap += ['side'=> 'Away'];}
				else {$GameRecap += ['side'=> null];}
				$Recaps[] = $GameRecap;
			}
		}
		$pastresults += ['recaps'=>$Recaps];
		$pastResultsArray[] = $pastresults;
	}

	function JSONgetUniqueGameDates($season) {
		$setDBH = createDBH();
	
		$GameDatesSQL = 'SELECT DISTINCT GameDate FROM game WHERE game.idSeasonkey = :idseason';
		$FetchGameDates = $setDBH->prepare($GameDatesSQL);
		$FetchGameDates->bindParam(':idseason', $season, PDO::PARAM_INT);
		$FetchGameDates->execute();

		$pastresults = array();

		while ($GameRecap = $FetchGameDates->fetch(PDO::FETCH_ASSOC))
			{
				$pastresults[] = $GameRecap['GameDate'];
			}

		$foundGameDates = array_values(array_unique($pastresults));
		return $foundGameDates;
	}
	
	$connectPDO = createDBH();
	
	$scheduleWhere = '';
	
	$currentteam = ( isset( $_GET['teamid'] ) && is_numeric( $_GET['teamid'] ) ) ? intval( $_GET['teamid'] ) : 0;
	$currentseason = ( isset( $_GET['seasonid'] ) && is_numeric( $_GET['seasonid'] ) ) ? intval( $_GET['seasonid'] ) : 0;
	$currentsubseason = ( isset( $_GET['subseasonid'] ) && is_numeric( $_GET['subseasonid'] ) ) ? intval( $_GET['subseasonid'] ) : 0;
	$currentlevel = ( isset( $_GET['levelid'] ) && is_numeric( $_GET['levelid'] ) ) ? intval( $_GET['levelid'] ) : 0;
	$currentdivision = isset($_GET['divid']) ? $_GET['divid'] : NULL;

	$pageselect = ( isset( $_GET['page'] ) && is_numeric( $_GET['page'] ) ) ? intval( $_GET['page'] ) : 1;
	$filter = ( isset( $_GET['filter'] ) && is_numeric( $_GET['filter'] ) ) ? intval( $_GET['filter'] ) : 2;
	
	$priority = isset($_GET['priority']) ? $_GET['priority'] : 'Level';

	$ScheduleSQLstart = 'SELECT SQL_CALC_FOUND_ROWS DISTINCT idGame, SeasonDisplay, SubSeasonName, HomeTeamFound.idLevelSeasonkey AS idLevel, HomeLevel.SortPriority, HomeTeamFound.TeamDivision as Division, HomeLevel.Level, GameDate, GameStartTime, LocationName, Rink, AwayTeamData.TeamName AS AwayTeamName, AwayTeamFound.teamPrefix AS AwayPrefix, AwayTeamData.externalLargeImage as AwayImage, awayThemeTable.cssCustom AS AwayTheme, HomeTeamData.TeamName AS HomeTeamName, HomeTeamFound.teamPrefix AS HomePrefix, HomeTeamData.externalLargeImage AS HomeImage, homeThemeTable.cssCustom AS HomeTheme, HomeShots, AwayShots, HomeOTShots, AwayOTShots, HomeGoals, AwayGoals, Home1stGoals, Home2ndGoals, Home3rdGoals, HomeOTGoals, Away1stGoals, Away2ndGoals, Away3rdGoals, AwayOTGoals, HomeTeam, AwayTeam,  GameFinished, HomeTeamFound.TeamWins AS HomeWins, HomeTeamFound.TeamLoss AS HomeLoss, HomeTeamFound.TeamTie AS HomeTie, HomeTeamFound.TeamOTLoss AS HomeOTL, HomeTeamFound.TeamPts AS HomePts, AwayTeamFound.TeamWins AS AwayWins, AwayTeamFound.TeamLoss AS AwayLoss, AwayTeamFound.TeamTie AS AwayTie, AwayTeamFound.TeamOTLoss AS AwayOTL, AwayTeamFound.TeamPts AS AwayPts FROM game 
	JOIN teamstatassignment AS HomeTeamFound ON game.HomeTeam = HomeTeamFound.idTeamAssignmentkey AND HomeTeamFound.idsubseasonkey = game.idsubseasonkey
	JOIN teamassignment AS HomeTeamFetch ON HomeTeamFound.idTeamAssignmentkey = HomeTeamFetch.idTeamAssignment
	LEFT JOIN jjglover_GLSHockey_NewsCss.cssteamassignment AS homeThemeTable ON HomeTeamFound.idTeamAssignmentkey = homeThemeTable.TeamidRef AND homeThemeTable.TeamtableRef = "teamassignment"
	JOIN team AS HomeTeamData ON HomeTeamData.idTeam = HomeTeamFetch.idTeamkey 
	JOIN level AS HomeLevel ON HomeTeamFound.idLevelSeasonkey = HomeLevel.idLevelSeason 
	JOIN teamstatassignment AS AwayTeamFound ON game.AwayTeam = AwayTeamFound.idTeamAssignmentkey AND AwayTeamFound.idsubseasonkey = game.idsubseasonkey 
	JOIN teamassignment AS AwayTeamFetch ON AwayTeamFound.idTeamAssignmentkey = AwayTeamFetch.idTeamAssignment
	LEFT JOIN jjglover_GLSHockey_NewsCss.cssteamassignment as awayThemeTable ON AwayTeamFound.idTeamAssignmentkey = awayThemeTable.TeamidRef AND awayThemeTable.TeamtableRef = "teamassignment"
	JOIN team AS AwayTeamData ON AwayTeamData.idTeam = AwayTeamFetch.idTeamkey
	JOIN location ON game.idLocationkey = location.idLocation 
	JOIN season ON game.idSeasonkey = season.idSeason 
	JOIN subseason ON game.idsubseasonkey = subseason.idsubseason';
	$ScheduleSQLLimit = '';

	if ($currentlevel != 0 AND !empty($currentdivision))
	{
		$scheduleWhere .= ' WHERE HomeTeamFound.idLevelSeasonkey = :idlevel AND HomeTeamFound.TeamDivision = :division AND game.idSeasonkey = :idseason';
	}
	elseif ($currentlevel != 0)
	{
		$scheduleWhere .= ' WHERE HomeTeamFound.idLevelSeasonkey = :idlevel AND game.idSeasonkey = :idseason';
	}
	elseif ($currentteam != 0)
	{
		$scheduleWhere .= ' WHERE (HomeTeam = :teamid OR AwayTeam = :teamid)';
	}
	elseif ($currentseason != 0)
	{
		$scheduleWhere .= ' WHERE game.idSeasonkey = :idseason';
	}
	if ($currentsubseason != 0)
	{
		$scheduleWhere .= ' AND game.idsubseasonkey = :idsubseason';
	}

	$ScheduleSQLend = ' AND (HomeTeamFound.teamStartDate IS NULL OR (game.GameDate BETWEEN HomeTeamFound.teamStartDate AND HomeTeamFound.teamEndDate)) AND (AwayTeamFound.teamStartDate IS NULL OR (game.GameDate BETWEEN AwayTeamFound.teamStartDate AND AwayTeamFound.teamEndDate)) ORDER BY GameDate';

	if ($type == 'Grid') {
		$CurrentDate = date("Y-m-d");
		$SearchDate = new DateTime($date);

		$SQLSearchDate = $SearchDate->format("Y-m-d");
		$SearchDateLimit = $SearchDate->modify($minusdate." days");
		$SQLSearchLimit = $SearchDateLimit->format("Y-m-d");

		if (($seasoninactive == 0) AND (strtotime($CurrentDate) < strtotime($SQLSearchDate))) {
			$SearchinverseDate = new DateTime($date);
			$minusdate = '+'.abs($minusdate);
			$SearchForwardDateLimit = $SearchinverseDate->modify($minusdate." days");
			$SQLSearchLimit = $SearchForwardDateLimit->format("Y-m-d");
		}

		if ($minusdate > 0) {
			$scheduleWhere .= ' AND GameDate BETWEEN :SQLDate AND :SQLLimitDate';
		} elseif ($minusdate == 0) {
			$scheduleWhere .= ' AND GameDate = :SQLDate';	
		} elseif ($minusdate < 0) {
			$scheduleWhere .= ' AND GameDate BETWEEN :SQLLimitDate AND :SQLDate';
		}

		#Determines sort order by checking current date to reference date (Schedule vs Results)
		
		#if (($seasoninactive == 0) AND (strtotime($CurrentDate) >= strtotime($SQLSearchDate)))
		#{$ScheduleSQLend .= ' ASC,';} else {$ScheduleSQLend .= ' DESC,';}
		$ScheduleSQLend .= ' ASC,';
		if ($priority == 'Level') {$ScheduleSQLend .= ' SortPriority, idLevel, HomeTeamFound.TeamDivision, GameStartTime';}
		elseif ($priority == 'Location') {$ScheduleSQLend .= ' LocationName, Rink, GameStartTime, SortPriority, idLevel, HomeTeamFound.TeamDivision';}
	} 
	elseif ($type == 'List') {
		if ($currentteam != 0) {
			$filter = 0;
		} else {
			$recordsOffset = ($pageselect-1)*25+1; 
			$ScheduleSQLLimit .= ' LIMIT 40 OFFSET :page';
		}
				
		if ($filter == 1) //Upcoming Games
		{
			$scheduleWhere .= ' AND GameDate >= CURDATE()';
			$ScheduleSQLend = ' ORDER BY GameDate ASC, GameStartTime';
		}
		if ($filter == 2) //Previous Games
		{
			$scheduleWhere .= ' AND GameDate <= CURDATE()';
			$ScheduleSQLend = ' ORDER BY GameDate DESC, GameStartTime DESC';
		}
		if ($filter == 0) //full schedule
		{
			$ScheduleSQLend = ' ORDER BY GameDate ASC, GameStartTime';
		}
	}

	#Set Query for Team
	$ScheduleQuery = $connectPDO->prepare($ScheduleSQLstart.$scheduleWhere.$ScheduleSQLend.$ScheduleSQLLimit);
	#echo $ScheduleSQLstart.$scheduleWhere.$ScheduleSQLend.$ScheduleSQLLimit;


	if ($currentlevel != 0 AND !empty($currentdivision))
	{
		$ScheduleQuery->bindParam(':idseason', $currentseason, PDO::PARAM_INT);
		$ScheduleQuery->bindParam(':idlevel', $currentlevel, PDO::PARAM_INT);
		$ScheduleQuery->bindParam(':division', $currentdivision, PDO::PARAM_STR);
	}
	elseif ($currentlevel != 0)
	{
		$ScheduleQuery->bindParam(':idseason', $currentseason, PDO::PARAM_INT);
		$ScheduleQuery->bindParam(':idlevel', $currentlevel, PDO::PARAM_INT);
	}
	elseif ($currentteam != 0)
	{
		$ScheduleQuery->bindParam(':teamid', $currentteam, PDO::PARAM_INT);
	}
	elseif ($currentseason != 0)
	{
		$ScheduleQuery->bindParam(':idseason', $currentseason, PDO::PARAM_INT);
	}
	if ($currentsubseason != 0)
	{
		$ScheduleQuery->bindParam(':idsubseason', $currentsubseason, PDO::PARAM_INT);
	}
	if ($type == 'Grid') {
		$ScheduleQuery->bindParam(':SQLDate', $SQLSearchDate, PDO::PARAM_STR);

		if ($minusdate != 0) {$ScheduleQuery->bindParam(':SQLLimitDate', $SQLSearchLimit, PDO::PARAM_STR);}
	} elseif($type == 'List') {
		$ScheduleQuery->bindParam(':page', $recordsOffset, PDO::PARAM_INT);
	}

	$ScheduleQuery->execute();

	$getTotalRowCount = $connectPDO->prepare('SELECT FOUND_ROWS()');
	$getTotalRowCount->execute();
	
	$TotalPossibleRecords = $getTotalRowCount->fetchColumn();
	$maxPage = round($TotalPossibleRecords/25);
	$ScheduleData = array();

	if ($type == 'List') {
		$win = 0;
		$loss = 0;
		$tie = 0;
		$otl = 0;
	}

	$schedGames = array();

	while($row = $ScheduleQuery->fetch(PDO::FETCH_ASSOC)) {
		if ($type== 'List') {
			if (!empty($row['HomePrefix'])) {$HomeTeamDisplay = '('.$row['HomePrefix'].') '.$row['HomeTeamName'];}
				else {$HomeTeamDisplay = $row['HomeTeamName'];}
				
			if (!empty($row['AwayPrefix'])) {$AwayTeamDisplay = '('.$row['AwayPrefix'].') '.$row['AwayTeamName'];}
				else {$AwayTeamDisplay = $row['AwayTeamName'];}

			$gameListArray = array(
				'idGame' => $row['idGame'],
				'Game Date' => date("D, M j", strtotime($row["GameDate"])).' at '.date("g:i a", strtotime($row["GameStartTime"])),
				'Rink' => $row['LocationName'].' '.$row['Rink'],
				'Home Team' => $HomeTeamDisplay, 
				'HomeImage' => $row['HomeImage'],
				'HomeLink' => $row['HomeTeam'],
				'Away Team' => $AwayTeamDisplay,
				'AwayImage' => $row['AwayImage'],
				'AwayLink' => $row['AwayTeam'],
				'Result' => $row['HomeGoals'].' - '.$row['AwayGoals']);
			
			if ($row['GameFinished'] == 1 && $currentteam != 0)
				{	
					if ($currentteam == $row['HomeTeam']) 
						{$testteam = 'HomeGoals';
						$testotherteam = 'AwayGoals';}
					else {$testteam = 'AwayGoals';
						$testotherteam = 'HomeGoals';}
					if ($row[$testteam] > $row[$testotherteam])
					{
						$win++;
					}
					elseif ($row[$testteam] < $row[$testotherteam])
					{
						if (empty($row['AwayOTShots']) AND empty($row['HomeOTShots'])) 
						{
							$loss++;
						} else {
							$otl++;
						} 
					}
					elseif ($row[$testteam] == $row[$testotherteam])
					{
						$tie++;
					}
				$gameListArray['Record'] = $win.'-'.$loss.'-'.$tie.'-'.$otl;
			}
			$ScheduleData[] = $gameListArray;

		} elseif ($type == 'Grid') {
			$schedGames[] = $row['GameDate'];
			$row['recaps']= JSONgetRecaps($row['idGame'], $row['HomeTeam'], $row['AwayTeam']);

			$ScheduleData[] = $row;
		}
	}

	$schedUniqueGames = array_values(array_unique($schedGames));

return array('maxPage' => $maxPage, 'data' => $ScheduleData, 'gameDates' => $schedUniqueGames);
}

function JSONsetSubseasonFilter($season) {
	$pdoconnect = createDBH();
	
	$setSubSchedSQL = 'SELECT idsubseason, SubSeasonName FROM game JOIN subseason ON game.idsubseasonkey = subseason.idsubseason WHERE idSeasonkey = :seasonid AND GameDate <= CURDATE() GROUP BY idSeasonkey, idsubseasonkey ORDER BY SortOrder';
	$setSubSchedSQL = $pdoconnect->prepare($setSubSchedSQL);
	$setSubSchedSQL->bindParam(':seasonid', $season, PDO::PARAM_INT);
	$setSubSchedSQL->execute();
	$subeasonNavoutput = array();
	while($row = $setSubSchedSQL->fetch(PDO::FETCH_ASSOC)) {
		$subeasonNavoutput[] = array('value'=>$row['idsubseason'], 'text'=>$row['SubSeasonName']);
	}
	
return $subeasonNavoutput;
}

function JSONsetSubseasonDates() {
	$pdoconnect = createDBH();
	$currentseason = ( isset( $_GET['seasonid'] ) && is_numeric( $_GET['seasonid'] ) ) ? intval( $_GET['seasonid'] ) : 0;
	$currentlevel = ( isset( $_GET['levelid'] ) && is_numeric( $_GET['levelid'] ) ) ? intval( $_GET['levelid'] ) : 0;
	$currentdivision = isset($_GET['divid']) ? $_GET['divid'] : NULL;
	
	$fetchDateSelect = 'SELECT Min(GameDate) as subseasonDate FROM game JOIN subseason ON game.idsubseasonkey = subseason.idsubseason JOIN teamassignment ON game.HomeTeam = teamassignment.idteamassignment JOIN teamstatassignment ON teamassignment.idteamassignment = teamstatassignment.idTeamAssignmentkey AND game.idsubseasonkey = teamstatassignment.idsubseasonkey';
	$fetchDateWhere = ' WHERE game.idSeasonkey = :seasonid AND GameDate <= CURDATE()';
	$fetchDateGroup = ' GROUP BY game.idSeasonkey ORDER BY SortOrder LIMIT 1';

	if ($currentlevel != 0 )
	{
		$fetchDateWhere .= ' AND idLevelSeasonkey = :idlevel';
	}
	if (!empty($currentdivision))
	{
		$fetchDateWhere .= ' AND TeamDivision = :division';
	}
	

	$setSubSchedSQL = $pdoconnect->prepare($fetchDateSelect.$fetchDateWhere.$fetchDateGroup);

	$setSubSchedSQL->bindParam(':seasonid', $currentseason, PDO::PARAM_INT);

	if ($currentlevel != 0)
	{
		$setSubSchedSQL->bindParam(':idlevel', $currentlevel, PDO::PARAM_INT);
	}
	if (!empty($currentdivision))
	{
		$setSubSchedSQL->bindParam(':division', $currentdivision, PDO::PARAM_STR);
	}

	$setSubSchedSQL->execute();

	$setLastDate = $setSubSchedSQL->fetch(PDO::FETCH_ASSOC);
	
return $setLastDate['subseasonDate'];
}

if ($schedtype == 'List') {
	$ReturnJSONdata = JSONschedule($schedtype, null, null, null);
	
	$JSONdataArray = array(
		'dataarray' => $ReturnJSONdata['data'],
		'tableid' => 'schedule',
		'tableclass' => 'tablesorter',
		'tablesort' => '',
		'PlayerLinkIDs' => null,
		'linkslist' => array(array('link'=>'team?teamid=','linktarget'=>'HomeLink','linkname'=>'Home Team'), array('link'=>'team?teamid=','linktarget'=>'AwayLink','linkname'=>'Away Team'), array('link'=>'gamerecap?gameID=','linktarget'=>'idGame','linkname'=>'Game Date')),
		'imagelist' => array(array('image'=>'AwayImage', 'imagetarget'=>'Away Team'),array('image'=>'HomeImage', 'imagetarget'=>'Home Team')),
		'filterMenu' => array(array('value'=>1, 'text'=>'Upcoming Games'), array('value'=>2, 'text'=>'Previous Games'), array('value'=>3, 'text'=>'All Games')),
		'subseasonFilter' => JSONsetSubseasonFilter($currentseason),
		'maxPage' => $ReturnJSONdata['maxPage'],
		'currentPage' => $pageselect
	);	
} elseif ($schedtype == 'Grid') {
	$seasonDates = setSeasonRange($currentseason);
	$seasonStart = strtotime($seasonDates['SeasonStart']);
	$seasonEnd = strtotime($seasonDates['SeasonEnd']);
	$currentdatecompare = strtotime($currentdate);
		
	$todayDate = date("Y-m-d");
	$yesterdayDate = date("Y-m-d", strtotime($todayDate."-1 days"));
	

	#Checks if season is currently active
	if (strtotime($todayDate) > $seasonStart && strtotime($todayDate) < $seasonEnd) {

		if ($currentdatecompare < $seasonStart) {
			$currentdate = $seasonDates['SeasonStart'];
		} elseif ($currentdatecompare > $seasonEnd) {
			$currentdate = $seasonDates['SeasonEnd'];
		}
		$ReturnJSONdata = JSONschedule($schedtype, $currentdate, 3, 0);
		$setDateFilter = 1;
	} else {

		$currentdate = ( isset( $_GET['date'] ) ) ? $_GET['date'] : JSONsetSubseasonDates();
		
		$ReturnJSONdata = JSONschedule($schedtype, $currentdate, 6, 1);
		$setDateFilter = 0;
	}
	
	$JSONdataArray = array(
		'dataarray' => $ReturnJSONdata['data'],
		'linkslist' => array(array('link'=>'team?teamid=','linktarget'=>'HomeLink','linkname'=>'Home Team'), array('link'=>'team?teamid=','linktarget'=>'AwayLink','linkname'=>'Away Team'), array('link'=>'gamerecap?gameid=','linktarget'=>'idGame','linkname'=>'Game Date')),
		'imagelist' => array(array('image'=>'AwayImage', 'imagetarget'=>'Away Team'),array('image'=>'HomeImage', 'imagetarget'=>'Home Team')),
		'dateFilter' => $setDateFilter,
		'subseasonFilter' => JSONsetSubseasonFilter($currentseason),
		'scheduleType' => $schedtype,
		'setAllGameDates' => JSONgetUniqueGameDates($currentseason),
		'startDate' => $seasonDates['SeasonStart'],
		'endDate' => $seasonDates['SeasonEnd'],
		'initialDate' => $currentdate
	);
} else {
	$JSONdataArray = 'Failure of view Switch in PHP';
}
#print_r($JSONdataArray);
$JSONecho = json_encode($JSONdataArray);
echo $JSONecho;

?>