<?php

include_once 'connection.php';

$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;
$currentsubseason = ( isset( $_GET['subseasonid'] ) && is_numeric( $_GET['subseasonid'] ) ) ? intval( $_GET['subseasonid'] ) : 0;
$pageselect = ( isset( $_GET['page'] ) && is_numeric( $_GET['page'] ) ) ? intval( $_GET['page'] ) : 1;
$sortcondition = isset($_GET['sort']) ? $_GET['sort'] : NULL;
$split = isset($_GET['split']) ? $_GET['split'] : NULL;
$currentgroup = 'seasonsubseason';
$sethistory = isset($_GET['request']) ? $_GET['request'] : 'current';


$idplayer = ( isset( $_GET['playerid'] ) && is_numeric( $_GET['playerid'] ) ) ? intval( $_GET['playerid'] ) : 0;
$idplayerassignment = ( isset( $_GET['playerteamid'] ) && is_numeric( $_GET['playerteamid'] ) ) ? intval( $_GET['playerteamid'] ) : 0;
$subgoalie = ( isset( $_GET['subgoalie'] ) && is_numeric( $_GET['subgoalie'] ) ) ? intval( $_GET['subgoalie'] ) : 2;


$playerDisplay = ''; 
$selectSplit = '';
$selectData = 'COUNT(idGame) AS GP, SUM(CASE WHEN GoalieResult = "Win" THEN 1 ELSE 0 END) AS "W", 
SUM(CASE WHEN GoalieResult = "Loss" THEN 1 ELSE 0 END) AS "L",
SUM(CASE WHEN GoalieResult = "Tie" THEN 1 ELSE 0 END) AS "T",
SUM(CASE WHEN GoalieResult = "OTL" THEN 1 ELSE 0 END) AS "OTL",
SUM(GoalieShots) AS SA, 
SUM(GoalieShots) - SUM(GoalieGoalsAllowed) AS SV,
SUM(GoalieGoalsAllowed) AS GA,
ROUND(1-(SUM(GoalieGoalsAllowed)/SUM(GoalieShots)), 3)  AS "Save%",
ROUND(SUM(GoalieGoalsAllowed)/COUNT(idGame), 2) AS "GAA",
ROUND((SUM(GoalieGoalsAllowed)-SUM(GoaliePPAllowed)-SUM(GoalieSHAllowed))/COUNT(idGame), 2) AS "GAA Even",
SUM(GoaliePPAllowed) AS "GA PP",
SUM(GoalieSHAllowed) AS "GA SH",
SUM(Shutout) AS "Shutout"';
$selectBaseOverall = '';
$joinBy = '';
$joinPeriodData = '';
$whereBy = '';
$groupBy = '';
$havingBy = '';
$orderBy = '';

if ($idplayerassignment > 0) {
    $PDOconnect = createDBH();
    $fetchPlayerDetail = $PDOconnect->prepare('SELECT idPlayersKey, idTeamkey FROM playerassignment JOIN teamassignment ON playerassignment.idTeamAssignmentkey = teamassignment.idTeamAssignment WHERE idPlayerAssignment = :idPlayerAssignment LIMIT 1');	
    $fetchPlayerDetail->bindParam(':idPlayerAssignment', $idplayerassignment, PDO::PARAM_INT);
    $fetchPlayerDetail->execute();
    $returnPlayer = $fetchPlayerDetail->fetch(PDO::FETCH_ASSOC);
    $idplayer = $returnPlayer['idPlayersKey'];
    $idTeam = $returnPlayer['idTeamkey'];

    if($sethistory == 'current') {
        $playerDisplay = 'PlayerNumber, " ", PlayerFirst, " ", PlayerLast';
        $targetPlayer = $idplayerassignment;
        $whereBy .= 'idPlayers = '.$idplayer.' AND idTeamkey = '.$idTeam;
        $groupBy .= '';
    } else if($sethistory == 'history') {
        $selectSplit .= "SeasonDisplay AS Season, ";
        $playerDisplay = 'PlayerNumber, " ", PlayerFirst, " ", PlayerLast';
        $targetPlayer = $idplayerassignment;
        $whereBy .= 'idPlayers = '.$idplayer.' AND idTeamkey = '.$idTeam;
        $groupBy .= '';
    }
        
} else if ($idplayer > 0) {
    $selectSplit .= "SeasonDisplay, ";
    $playerDisplay = 'PlayerFirst, " ", PlayerLast';
    $targetPlayer = $idplayer;
    $whereBy = 'idPlayers = :idPlayer';
    $groupBy = '';
} else {
    if ($currentseason != 0) {$SQLWhere .= ' WHERE idSeasonkey = :idseason';}
    if ($currentsubseason != 0)
    {
        if ($currentsubseason == 99) {
            $SQLWhere .= ' AND subseason.Playoff = 1';
        } else {
        $SQLWhere .= ' AND idsubseasonkey = :idsubseason';
        }
    }
    if ($currentlevel != 0) {$SQLWhere .= ' AND idLevelSeasonkey = :idlevel';}
    if (!empty($currentdivision)) {$SQLWhere .= ' AND TeamDivision = :division';}
    if ($currentteam != 0) {$SQLWhere .= ' WHERE idTeamAssignment = :teamid';}
    if (!empty($sort)) {$SQLOrderBy = ' ORDER BY '.$sort;}
}


if ($split == 'goalperiod') {
    $selectSplit = "";
    $joinPeriodData = 	'JOIN gameperiod
    LEFT JOIN ( 
		SELECT gamegoaliesave.idPlayerAssignmentkey, game.idGame AS game, gameperiod.gamePeriodName AS Period, SUM(CASE WHEN GoalType = "GA" THEN 1 ELSE 0 END) AS "GA", 
		SUM(CASE WHEN GoalType = "PPG" THEN 1 ELSE 0 END) AS "PPGA",
		SUM(CASE WHEN GoalType = "SHG" THEN 1 ELSE 0 END) AS "SHGA"
		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 gameperiod
        LEFT JOIN (
            SELECT idGame, idPlayerAssignmentkey, GamePeriod, gameevents.idGameEvents, "GA" AS GoalType
            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 gameevents ON game.idGame = gameevents.idGamekey
            JOIN goalevent ON gameevents.idGameEvents = goalevent.idGameEvents
            WHERE idplayerassignment = GoalieinNet AND '.$whereBy.'
            UNION ALL 
            SELECT idGame, idPlayerAssignmentkey, GamePeriod, gameevents.idGameEvents, goalclass.GoalTypeShort AS GoalType
            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 gameevents ON game.idGame = gameevents.idGamekey
            JOIN goalevent ON gameevents.idGameEvents = goalevent.idGameEvents
            JOIN goalclassevent ON goalevent.idGameEvents = goalclassevent.goaleventkey
            JOIN goalclass ON goalclassevent.goalclasskey = goalclass.idGoalClass
            WHERE (goalclassevent.goalclasskey = 2 OR goalclassevent.goalclasskey = 3) AND idplayerassignment = GoalieinNet AND '.$whereBy.'
            ) AS goalPeriodData ON goalPeriodData.idGame = game.idGame AND gameperiod.gamePeriodValue = goalPeriodData.GamePeriod
        WHERE '.$whereBy.'
        GROUP BY idPlayerAssignmentkey, game, GamePeriod
    ) AS PeriodDetail ON game.idGame = PeriodDetail.game AND (gameperiod.gamePeriodName = PeriodDetail.Period OR PeriodDetail.Period is null)';
    $selectBaseOverall .= ' gameperiod.gamePeriodName AS Period, GA, PPGA, SHGA, CASE WHEN gameperiod.gamePeriodValue = 1 AND idTeamAssignment = HomeTeam THEN Away1stShots WHEN gameperiod.gamePeriodValue = 2 AND idTeamAssignment = HomeTeam THEN Away2ndShots WHEN gameperiod.gamePeriodValue = 3 AND idTeamAssignment = HomeTeam THEN Away3rdShots WHEN gameperiod.gamePeriodValue = 4 AND idTeamAssignment = HomeTeam THEN AwayOTShots WHEN gameperiod.gamePeriodValue = 1 AND idTeamAssignment = AwayTeam THEN Home1stShots WHEN gameperiod.gamePeriodValue = 2 AND idTeamAssignment = AwayTeam THEN Home2ndShots WHEN gameperiod.gamePeriodValue = 3 AND idTeamAssignment = AwayTeam THEN Home3rdShots WHEN gameperiod.gamePeriodValue = 4 AND idTeamAssignment = AwayTeam THEN HomeOTShots END AS SA, IF (game.OTDetected = 1 AND gameperiod.gamePeriodName = "OT" OR gameperiod.gamePeriodName <> "OT", 1, 0) AS GP, ';
    $selectSplit .= 'Period, ';
    $selectData = 'SUM(GP) AS GP, SUM(SA) AS SA, 
    SUM(SA) - SUM(GA) AS SV,
    SUM(GA) AS GA,
    ROUND(1-(SUM(GA)/SUM(SA)), 3)  AS "Save%",
    ROUND(SUM(GA)/SUM(GP), 2) AS "GAA",
    ROUND((SUM(GA)-SUM(PPGA)-SUM(SHGA))/SUM(GP), 2) AS "GAA Even",
    SUM(PPGA) AS "GA PP",
    SUM(SHGA) AS "GA SH"';
    $groupBy .= 'Period';
} else if ($split == 'alltime') {
    $selectSplit .= '';
    $groupBy .= '';
} else if ($split == 'standard') {
    $selectSplit .= "SeasonDisplay, ";
    $groupBy .= 'SeasonDisplay';
    $orderBy .= 'ORDER BY SeasonStart DESC';
} else if ($split == 'subseason') {
    $selectSplit = 'SubSeasonName AS "Game Type", ';
    $groupBy .= 'SubSeasonName';
} else if ($split == 'level') {
    $selectSplit = 'Level, ';
    $selectBaseOverall = 'SortPriority, ';
    $groupBy .= 'Level ';
    $orderBy .= 'ORDER BY SortPriority';
} else if ($split == 'location') {
    $selectSplit = 'LocationName AS Arena, ';
    $groupBy .= 'LocationName';
    $orderBy .= 'ORDER BY GP DESC';
} else if ($split == 'homeaway') {
    $selectSplit = 'homeAway as "Home/Away", ';
    $groupBy .= 'homeAway';
} else if ($split == 'weekend') {
    $selectSplit = 'dayType as "Weekday/Weekend", ';
    $selectBaseOverall = 'CASE WHEN WEEKDAY(game.GameDate) = 5 OR WEEKDAY(game.GameDate) = 6 THEN "Weekend" ELSE "Weekday" END AS "dayType", ';
    $groupBy .= 'dayType';
} else if ($split == 'timestart') {
    $selectSplit = 'timeType as "Time of Day", ';
    $selectBaseOverall = 'CASE WHEN game.GameStartTime < "18:00:00" THEN "Before 6pm Start" WHEN game.GameStartTime BETWEEN "18:00:00" AND "18:59:00" THEN "6pm Start"  WHEN game.GameStartTime BETWEEN "19:00:00" AND "19:59:00" THEN "7pm Start" WHEN game.GameStartTime BETWEEN "20:00:00" AND "20:59:00" THEN "8pm Start" WHEN game.GameStartTime BETWEEN "21:00:00" AND "21:59:00" THEN "9pm Start" WHEN game.GameStartTime >= "22:00:00" THEN "10pm or Later"  END AS "timeType", ';
    $groupBy .= 'timeType';
}

$fullSQL = 'SELECT '.$selectSplit.' '.$selectData.'
FROM (
    SELECT idPlayers, '.$selectBaseOverall.' playerassignment.PlayerNumber, PlayerFirst, PlayerLast, playerassignment.idPlayerAssignment AS playerid, idGame, SeasonStart, SeasonDisplay, LocationName, SubSeasonName, Level, idTeamAssignment, idTeamkey, TeamName, if(playerassignment.idTeamAssignmentkey = HomeTeam, "Home", "Away") as homeAway, GameDate, GameStartTime, GoalieResult, GoalieShots, GoalieGoalsAllowed, GoalieEvenAllowed, GoaliePPAllowed, GoalieSHAllowed, if(GoalieGoalsAllowed = 0, 1, 0) AS Shutout FROM gamegoaliesave 
    JOIN game ON gamegoaliesave.idGamekey = game.idGame
    JOIN location ON game.idLocationkey = location.idLocation
    JOIN subseason ON game.idsubseasonkey = subseason.idsubseason
    JOIN season ON game.idSeasonkey = season.idSeason
    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
    JOIN teamstatassignment ON teamassignment.idTeamassignment = teamstatassignment.idTeamAssignmentkey AND game.idsubseasonkey = teamstatassignment.idsubseasonkey
    JOIN level ON teamstatassignment.idLevelSeasonkey = level.idLevelSeason
    '.$joinPeriodData.'
    WHERE '.$whereBy.'
    ORDER BY SeasonStart DESC
    ) AS gameData 
WHERE '.$whereBy.'
GROUP BY '.$groupBy.' '.
$orderBy;

//print_r($fullSQL);
$PDOconnect = createDBH();
$fetchAllData = $PDOconnect->prepare($fullSQL);
$fetchAllData->bindParam(':idPlayer', $targetPlayer, PDO::PARAM_INT);
$fetchAllData->execute();
$fetchedAllData = $fetchAllData->fetchAll(PDO::FETCH_ASSOC);

$prepJSONteamstatArray = array(
	'dataarray' => $fetchedAllData,
	'tableid' => 'teamstat',
	'tableclass' => 'tablesorter',
	'tablesort' => $sortcondition,
	'linkslist' => array(array('link'=>'team?teamid=','linktarget'=>'idTeam','linkname'=>'Team')),
	'imagelist' => null,
	'filterMenu' => null,
	'groupMenu' => $starterFilterArray,
	'subseasonMenu' => null,
    'splitMenu' => $filterSplitMenu
);

echo JSON_encode($prepJSONteamstatArray);

?>