<?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 = ', IFNULL(SUM(GP), 0) AS GP, IFNULL(SUM(Goals), 0) AS G, IFNULL(SUM(Assists),0) AS A, IFNULL(SUM(PN), 0) AS PN, IFNULL(SUM(PIM),0) AS PIM, IFNULL(SUM(PPG), 0) AS PPG, IFNULL(SUM(SHG), 0) AS SHG, IFNULL(SUM(ENG), 0) AS ENG, IFNULL(SUM(GWG), 0) AS GWG, IFNULL(SUM(PSG), 0) AS PSG, IFNULL(SUM(PPP), 0) AS PPP, IFNULL(SUM(SHP), 0) AS SHP, IFNULL(SUM(ENP), 0) AS ENP, IFNULL(SUM(GWP), 0) AS GWP, IFNULL(SUM(GTP), 0) AS GTP';
$selectBaseOverall = '';
$joinBy = '';
$joinPeriod = '';
$goaljoinperiod = '';
$assistjoinperiod = '';
$specialjoinperiod = '';
$penaltyjoinperiod = '';
$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 .= 'Skater';
    } else if($sethistory == 'history') {
        $selectSplit .= "SeasonDisplay AS Season, ";
        $playerDisplay = 'PlayerNumber, " ", PlayerFirst, " ", PlayerLast';
        $targetPlayer = $idplayerassignment;
        $whereBy .= 'idPlayers = '.$idplayer.' AND idTeamkey = '.$idTeam;
        $groupBy .= 'Skater';
    }
} else if ($idplayer > 0) {
    $selectSplit .= "SeasonDisplay AS Season, ";
    $playerDisplay = 'PlayerFirst, " ", PlayerLast';
    $targetPlayer = $idplayer;
    $whereBy = 'idPlayers = :idPlayer';
    $groupBy = 'Skater';
} 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') {
    //$selectData = ', SUM(GP) AS GP';
    $selectSplit = 'Period, '; 
    $selectBaseOverall = 'gameperiod.gamePeriodName AS Period, ';
    $joinPeriod .= "JOIN gameperiod ";
    $goaljoinperiod = 'AND gameperiod.gamePeriodValue = goalList.GamePeriod';
    $assistjoinperiod = 'AND gameperiod.gamePeriodValue = assistCombineList.GamePeriod';
    $specialjoinperiod = 'AND gameperiod.gamePeriodValue = specialGoal.GamePeriod';
    $penaltyjoinperiod = 'AND gameperiod.gamePeriodValue = penaltyEvents.GamePeriod';
    $groupBy = 'Period';
} else if ($split == 'alltime') {
    $selectSplit = 'TeamName, ';
    $groupBy .= ', TeamName';
} else if ($split == 'standard') {
    $selectSplit .= "SeasonDisplay AS Season, ";
    $groupBy .= ', SeasonDisplay ';
    $orderBy = ' ORDER BY SeasonStart DESC';
} else if ($split == 'subseason') {
    $selectSplit = 'SubSeasonName AS "Game Type", ';
    $groupBy .= ', SubSeasonName ';
} else if ($split == 'level') {
    $selectSplit = 'Level, ';
    $groupBy .= ', Level ';
    $orderBy = ' ORDER BY Level ';
} 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.' GamePosition, GameLine '.$selectData.' FROM (
    SELECT CONCAT('.$playerDisplay.') AS Skater, SeasonDisplay, SeasonStart, SubSeasonName, LocationName, if(playerassignment.idTeamAssignmentkey = HomeTeam, "Home", "Away") as homeAway, '.$selectBaseOverall.' idPlayers, idGame, PlayerAssignmentKey, Level, idTeamAssignment, idTeamkey, TeamName, GamePosition, GameLine, 1 AS GP, Goals, Assists, PN, PIM, PPG, specialGoal.SHG AS SHG, ENG, GWG, PSG, PPP, SHP, ENP, GWP, GTP
    FROM gameattendance
    JOIN game ON gameattendance.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 gameattendance.PlayerAssignmentkey = 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
    '.$joinPeriod.'
    LEFT JOIN (
        SELECT idGamekey, GamePeriod, COUNT(goalevent.idGameEvents) AS Goals from goalevent
        JOIN playerassignment ON goalevent.GoalScorer = playerassignment.idPlayerAssignment
        JOIN players ON playerassignment.idPlayerskey = players.idPlayers
        JOIN gameevents ON goalevent.idGameEvents = gameevents.idGameEvents
        JOIN teamassignment ON playerassignment.idTeamAssignmentkey = teamassignment.idTeamAssignment
        WHERE '.$whereBy.'
        GROUP BY idGamekey
        ) AS goalList ON idGame = goalList.idGamekey '.$goaljoinperiod.' 
    LEFT JOIN (
        SELECT idGamekey, GamePeriod, SUM(Assists) as Assists from (
            SELECT idGamekey, GamePeriod, COUNT(goalevent.idGameEvents) AS Assists FROM goalevent
            JOIN playerassignment ON goalevent.PlayerAssist = playerassignment.idPlayerAssignment
            JOIN players ON playerassignment.idPlayerskey = players.idPlayers
            JOIN gameevents ON goalevent.idGameEvents = gameevents.idGameEvents
            JOIN teamassignment ON playerassignment.idTeamAssignmentkey = teamassignment.idTeamAssignment
            WHERE '.$whereBy.'
            GROUP BY idGamekey
            UNION ALL
            SELECT idGamekey, GamePeriod, COUNT(goalevent.idGameEvents) AS Assists FROM goalevent
            JOIN playerassignment ON goalevent.PlayerAssist2 = playerassignment.idPlayerAssignment
            JOIN players ON playerassignment.idPlayerskey = players.idPlayers
            JOIN gameevents ON goalevent.idGameEvents = gameevents.idGameEvents
            JOIN teamassignment ON playerassignment.idTeamAssignmentkey = teamassignment.idTeamAssignment
            WHERE '.$whereBy.'
            GROUP BY idGamekey
            ) AS AssistList
        GROUP BY idGamekey
        ) AS assistCombineList ON idGame = assistCombineList.idGamekey '.$assistjoinperiod.' 
    LEFT JOIN (SELECT idGamekey, GamePeriod, SUM(CASE WHEN GoalType = "PPG" THEN 1 ELSE 0 END) AS PPG, 
            SUM(CASE WHEN GoalType = "SHG" THEN 1 ELSE 0 END) AS SHG, 
            SUM(CASE WHEN GoalType = "ENG" THEN 1 ELSE 0 END) AS ENG,
            SUM(CASE WHEN GoalType = "GWG" THEN 1 ELSE 0 END) AS GWG, 
            SUM(CASE WHEN GoalType = "GTG" THEN 1 ELSE 0 END) AS GTG, 
            SUM(CASE WHEN GoalType = "PSG" THEN 1 ELSE 0 END) AS PSG, 
            SUM(CASE WHEN GoalType = "PPP" THEN 1 ELSE 0 END) AS PPP,
            SUM(CASE WHEN GoalType = "SHP" THEN 1 ELSE 0 END) AS SHP,
            SUM(CASE WHEN GoalType = "ENP" THEN 1 ELSE 0 END) AS ENP,
            SUM(CASE WHEN GoalType = "GWP" THEN 1 ELSE 0 END) AS GWP,
            SUM(CASE WHEN GoalType = "GTP" THEN 1 ELSE 0 END) AS GTP
            FROM (SELECT idGamekey, GamePeriod, goalevent.idGameEvents, If(GoalScorer = idPlayerAssignment, GoalTypeShort, 
            CASE WHEN GoalTypeShort = "PPG" THEN "PPP" 
                WHEN GoalTypeShort = "SHG" THEN "SHP" 
                WHEN GoalTypeShort = "ENG" THEN "ENP" 
                WHEN GoalTypeShort = "GWG" THEN "GWP" 
                WHEN GoalTypeShort = "GTG" THEN "GTP" END) AS GoalType 
                FROM goalevent
                JOIN playerassignment ON goalevent.GoalScorer = playerassignment.idPlayerAssignment OR goalevent.PlayerAssist = playerassignment.idPlayerAssignment OR goalevent.PlayerAssist2 = playerassignment.idPlayerAssignment
                JOIN players ON playerassignment.idPlayerskey = players.idPlayers
                JOIN gameevents ON goalevent.idGameEvents = gameevents.idGameEvents
                JOIN goalclassevent ON goalevent.idGameEvents = goalclassevent.goaleventkey
                JOIN goalclass ON goalclassevent.goalclasskey = goalclass.idGoalClass
                JOIN teamassignment ON playerassignment.idTeamAssignmentkey = teamassignment.idTeamAssignment
                WHERE '.$whereBy.') AS goalType
            GROUP BY idGamekey
        ) AS specialGoal ON idGame = specialGoal.idGamekey '.$specialjoinperiod.' 
    LEFT JOIN 
        (SELECT idGamekey, GamePeriod, COUNT(penaltyevent.idGameEvents) as PN, SUM(PenaltyLength) AS PIM
            FROM penaltyevent
            JOIN playerassignment ON penaltyevent.idPlayerAssignmentkey = playerassignment.idPlayerAssignment
            JOIN players ON playerassignment.idPlayerskey = players.idPlayers
            JOIN gameevents ON penaltyevent.idGameEvents = gameevents.idGameEvents
            JOIN teamassignment ON playerassignment.idTeamAssignmentkey = teamassignment.idTeamAssignment
            WHERE '.$whereBy.'
            GROUP BY idGamekey
        ) AS penaltyEvents ON idGame = penaltyEvents.idGamekey '.$penaltyjoinperiod.' 
    WHERE Attend = 1 AND '.$whereBy.') 
AS DataPull
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' => null,
	'subseasonMenu' => null,
    'splitMenu' => $filterSplitMenu
);

echo JSON_encode($prepJSONteamstatArray);

?>