<?php

include_once 'connection.php';

$currentteam = ( isset( $_GET['teamid'] ) && is_numeric( $_GET['teamid'] ) ) ? intval( $_GET['teamid'] ) : 0;
$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;
$currentgoalie =  isset( $_GET['goalie'] ) ? $_GET['goalie'] : "Skater";
$subgoalie = ( isset( $_GET['subgoalie'] ) && is_numeric( $_GET['subgoalie'] ) ) ? intval( $_GET['subgoalie'] ) : 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';

/*
Splits
Standard
Goals by Period
Shots by Period
Shot Efficeny by Period
Goal Types by Period
Penalties by Period
Special Teams Efficiency 

2nd Split - Changes Behavior on Context - One table answer for individual teams, 2nd Split Filter Selectors for Team-team comparison
Overall
Location
Time
Home/Away
Weekday/Weekend

*/

//$selectTeamGroup = 'SELECT "team';
//$groupBy = 'GROUP BY team';

//$selectLocation .= ', LocationName';
//$groupBy .= ', LocationName';


$selectDataSplit = ' GP, W, L, T, OTL, Pts, SF, ROUND(SF/GP,2) as SFPG, GF, CONCAT(ROUND((GF/SF)*100,2), "%") AS ShotEff, SA, ROUND(SA/GP, 2) as SAPG, GA, CONCAT(ROUND((1-(GA/SA))*100,2), "%") AS DefEff, (SF-SA) as SD, Pen, PIM, PP, PPG, CONCAT(ROUND((PPG/PP)*100, 2), "%") AS "PP%", PK, PPGA, CONCAT(ROUND((PPGA/PK)*100, 2), "%") AS "PK%" ';
$selectSplitOverall = '';
$selectSplit = '';
$selectResultsOverallSplit = '';
$selectResultsSplit = '';
$selectGameResultsSplit = '';
$selectGameListSplit = '';
$selectBaseSplit = '';
$joinByOverall = '';
$joinResultsBy = '';
$joinGoalEventsBy = '';
$joinPenaltyEventsBy = '';
$joinBy = '';
$selectEventsSplit = '';
$whereByOverallHome = '';
$whereByOverallAway = '';
$whereBy = '';
$groupByOverall = 'gameResults.team';
$groupBy = '';
$groupByGame = '';
$havingBy = '';
$orderBy = '';

$selectPeriod = 'SELECT gamePeriodName FROM period';

function fetchTeamID($currentteam) {
    $PDOconnect = createDBH();
    $fetchPastTeams = $PDOconnect->prepare('SELECT idTeamkey FROM teamassignment WHERE idTeamAssignment = :idTeamAssignment LIMIT 1');	
    $fetchPastTeams->bindParam(':idTeamAssignment', $currentteam, PDO::PARAM_INT);
    $fetchPastTeams->execute();
    $returnTeam = $fetchPastTeams->fetch(PDO::FETCH_ASSOC);
   return $returnTeam['idTeamkey'];
}

if ($currentteam > 0) {
    if ($sethistory == 'history') {
        $currentteam = fetchTeamID($currentteam);
        $selectSubseasonSplit = '';
        $joinGoalEventsBy = ' gameList.idTeamkey = goalStat.idTeamkey ';
        $joinPenaltyEventsBy = ' gameList.idTeamkey = penaltyStat.idTeamkey ';
        $whereBy = 'idTeamkey = :idTeam';
        $whereByOverallHome = 'idTeamkey = :idTeam';
        $whereByOverallAway = 'idTeamkey = :idTeam';
        $groupBy = 'idTeamkey';
    } else if ($sethistory == 'current') {
        $selectSubseasonSplit = '';
        $joinGoalEventsBy = ' gameList.team = goalStat.team ';
        $joinPenaltyEventsBy = ' gameList.idTeamkey = penaltyStat.idTeamkey ';
        $whereBy = 'HomeTeam = :idTeam or AwayTeam = :idTeam';
        $whereByOverallHome = 'HomeTeam = :idTeam';
        $whereByOverallAway = 'AwayTeam = :idTeam';
        $groupBy = 'team';
    }
} 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 == 'lineGoalsPeriod') {
    /*$selectGoalData = ', SUM(CASE WHEN GoalType = "GF" AND GamePeriod = 1 THEN 1 ELSE 0 END) AS "1stGF", 
    SUM(CASE WHEN GoalType = "PPG" AND GamePeriod = 1 THEN 1 ELSE 0 END) AS "1stPPG",
    SUM(CASE WHEN GoalType = "SHG" AND GamePeriod = 1 THEN 1 ELSE 0 END) AS "1stSHG",
    SUM(CASE WHEN GoalType = "ENG" AND GamePeriod = 1 THEN 1 ELSE 0 END) AS "1stENG",
    SUM(CASE WHEN GoalType = "GA" AND GamePeriod = 1 THEN 1 ELSE 0 END) AS "1stGA",
    SUM(CASE WHEN GoalType = "PPGA" AND GamePeriod = 1 THEN 1 ELSE 0 END) AS "1stPPGA",
    SUM(CASE WHEN GoalType = "SHGA" AND GamePeriod = 1 THEN 1 ELSE 0 END) AS "1stSHGA",
    SUM(CASE WHEN GoalType = "ENGA" AND GamePeriod = 1 THEN 1 ELSE 0 END) AS "1stENGA",
    SUM(CASE WHEN GoalType = "GF" AND GamePeriod = 2 THEN 1 ELSE 0 END) AS "2ndGF", 
    SUM(CASE WHEN GoalType = "PPG" AND GamePeriod = 2 THEN 1 ELSE 0 END) AS "2ndPPG",
    SUM(CASE WHEN GoalType = "SHG" AND GamePeriod = 2 THEN 1 ELSE 0 END) AS "2ndSHG",
    SUM(CASE WHEN GoalType = "ENG" AND GamePeriod = 2 THEN 1 ELSE 0 END) AS "2ndENG",
    SUM(CASE WHEN GoalType = "GA" AND GamePeriod = 2 THEN 1 ELSE 0 END) AS "2ndGA",
    SUM(CASE WHEN GoalType = "PPGA" AND GamePeriod = 2 THEN 1 ELSE 0 END) AS "2ndPPGA",
    SUM(CASE WHEN GoalType = "SHGA" AND GamePeriod = 2 THEN 1 ELSE 0 END) AS "2ndSHGA",
    SUM(CASE WHEN GoalType = "ENGA" AND GamePeriod = 2 THEN 1 ELSE 0 END) AS "2ndENGA",
    SUM(CASE WHEN GoalType = "GF" AND GamePeriod = 3 THEN 1 ELSE 0 END) AS "3rdGF", 
    SUM(CASE WHEN GoalType = "PPG" AND GamePeriod = 3 THEN 1 ELSE 0 END) AS "3rdPPG",
    SUM(CASE WHEN GoalType = "SHG" AND GamePeriod = 3 THEN 1 ELSE 0 END) AS "3rdSHG",
    SUM(CASE WHEN GoalType = "ENG" AND GamePeriod = 3 THEN 1 ELSE 0 END) AS "3rdENG",
    SUM(CASE WHEN GoalType = "GA" AND GamePeriod = 3 THEN 1 ELSE 0 END) AS "3rdGA",
    SUM(CASE WHEN GoalType = "PPGA" AND GamePeriod = 3 THEN 1 ELSE 0 END) AS "3rdPPGA",
    SUM(CASE WHEN GoalType = "SHGA" AND GamePeriod = 3 THEN 1 ELSE 0 END) AS "3rdSHGA",
    SUM(CASE WHEN GoalType = "ENGA" AND GamePeriod = 3 THEN 1 ELSE 0 END) AS "3rdENGA",
    SUM(CASE WHEN GoalType = "GF" AND GamePeriod = 4 THEN 1 ELSE 0 END) AS "OTGF", 
    SUM(CASE WHEN GoalType = "PPG" AND GamePeriod = 4 THEN 1 ELSE 0 END) AS "OTPPG",
    SUM(CASE WHEN GoalType = "SHG" AND GamePeriod = 4 THEN 1 ELSE 0 END) AS "OTSHG",
    SUM(CASE WHEN GoalType = "ENG" AND GamePeriod = 4 THEN 1 ELSE 0 END) AS "OTENG",
    SUM(CASE WHEN GoalType = "GA" AND GamePeriod = 4 THEN 1 ELSE 0 END) AS "OTGA",
    SUM(CASE WHEN GoalType = "PPGA" AND GamePeriod = 4 THEN 1 ELSE 0 END) AS "OTPPGA",
    SUM(CASE WHEN GoalType = "SHGA" AND GamePeriod = 4 THEN 1 ELSE 0 END) AS "OTSHGA",
    SUM(CASE WHEN GoalType = "ENGA" AND GamePeriod = 4 THEN 1 ELSE 0 END) AS "OTENGA"';*/
    $selectDataSplit = ' COUNT(idGame)/4 as GP, SUM(W)/4 AS W, SUM(OTL)/4 AS OTL, SUM(L)/4 AS L, SUM(T)/4 AS T, (SUM(W)*2+SUM(OTL)+SUM(T)/4) AS Pts, GF, GA, PPG, SHG, ENG, PPGA, SHGA, ENGA ';
} else if ($split == 'shotsPeriod') {
    $selectDataSplit = ' GP, W, OTL, L, T, Pts, ShF, ShA, (ShF-ShA) AS SD, ROUND(ShF/GP,2) as ShFPG, ROUND(ShA/GP, 2) as ShAPG, CONCAT(ROUND((GF/ShF)*100,2), "%") AS "Shot%", CONCAT(ROUND((1-(GA/ShA))*100,2), "%") AS "Def%"
    ';
    $selectSplitOverall .= 'gameEventStat.Period, ';
    $selectGameResultsSplit = 'gameList.Period AS Period, 
    CASE WHEN gameList.Period = "1" THEN SUM(1stSF) WHEN gameList.Period = "2" THEN SUM(2ndSF) WHEN gameList.Period = "3" THEN SUM(3rdSF) WHEN gameList.Period = "OT" THEN SUM(OTSF) ELSE null END AS "ShF", 
    CASE WHEN gameList.Period = "1" THEN SUM(1stSA) WHEN gameList.Period = "2" THEN SUM(2ndSA) WHEN gameList.Period = "3" THEN SUM(3rdSA) WHEN gameList.Period = "OT" THEN SUM(OTSA) ELSE null END AS "ShA",';
    $selectGameListSplit = 'gamePeriodName AS Period, ';
    $joinGoalEventsBy .= ' AND gameList.Period = goalStat.Period ';
    $joinPenaltyEventsBy .= ' AND gameList.Period = penaltyStat.Period ';
    $selectSplit = 'Period, ';
    $joinResultsBy = 'JOIN gameperiod';
    $groupBy .= ', Period';
    $groupByOverall .= ', gameEventStat.Period';  
    $orderBy = 'ORDER BY Period';
} else if ($split == 'goalsPeriod') {
    $selectDataSplit = ' GF, GA, GF-GA AS GD, CONCAT(ROUND((GF/ShF)*100,2), "%") AS "Shot%", CONCAT(ROUND((1-(GA/ShA))*100,2), "%") AS "Def%", PPG, SHG, PPGA, SHGA, (PPG+SHG)-(PPGA-SHGA) AS "SP+-", ENG, ENGA ';
    $selectSplitOverall .= 'gameEventStat.Period, ';
    $selectGameResultsSplit = 'gameList.Period AS Period, 
    CASE WHEN gameList.Period = "1" THEN SUM(1stSF) WHEN gameList.Period = "2" THEN SUM(2ndSF) WHEN gameList.Period = "3" THEN SUM(3rdSF) WHEN gameList.Period = "OT" THEN SUM(OTSF) ELSE null END AS "ShF", 
    CASE WHEN gameList.Period = "1" THEN SUM(1stSA) WHEN gameList.Period = "2" THEN SUM(2ndSA) WHEN gameList.Period = "3" THEN SUM(3rdSA) WHEN gameList.Period = "OT" THEN SUM(OTSA) ELSE null END AS "ShA",';
    $selectGameListSplit = 'gamePeriodName AS Period, ';
    $joinGoalEventsBy .= ' AND gameList.Period = goalStat.Period ';
    $joinPenaltyEventsBy .= ' AND gameList.Period = penaltyStat.Period ';
    $selectSplit = 'Period, ';
    $joinResultsBy = 'JOIN gameperiod';
    $groupBy .= ', Period';
    $groupByOverall .= ', gameEventStat.Period';  
    $orderBy = 'ORDER BY Period';
} else if ($split == 'penaltyperiod') {
    $selectDataSplit = ' Pen, PIM, PP, PPG, CONCAT(ROUND((PPG/PP)*100, 2), "%") AS "PP%", PK, PPGA, CONCAT(ROUND((PPGA/PK)*100, 2), "%") AS "PK%" ';
    $selectSplitOverall .= 'gameEventStat.Period, ';
    $selectGameResultsSplit = 'gameList.Period AS Period, ';
    $selectGameListSplit = 'gamePeriodName AS Period, ';
    $joinGoalEventsBy .= ' AND gameList.Period = goalStat.Period ';
    $joinPenaltyEventsBy .= ' AND gameList.Period = penaltyStat.Period ';
    $selectSplit = 'Period, ';
    $joinResultsBy = 'JOIN gameperiod';
    $groupBy .= ', Period';
    $groupByOverall .= ', gameEventStat.Period'; 
    $orderBy = 'ORDER BY Period';
} else if ($split == 'location') {
    $selectSplitOverall.= 'gameResults.LocationName, ';
    $selectResultsOverallSplit = 'LocationName, '; 
    $selectResultsSplit = 'LocationName, ';
    $selectSplit = 'LocationName, ';
    $selectGameResultsSplit = 'gameList.LocationName, '; 
    $selectGameListSplit = 'LocationName, ';
    $selectBaseSplit .= 'LocationName, ';
    $joinByOverall .= ' AND gameEventStat.LocationName = gameResults.LocationName';
    $joinResultsBy = 'JOIN location ON game.idLocationkey = location.idLocation';
    $joinBy .= 'JOIN location ON game.idLocationkey = location.idLocation';
    $groupByOverall .= ', gameResults.LocationName';
    $groupBy .= ', LocationName';
    $groupByGame .= 'GROUP BY LocationName';
    $orderBy = 'ORDER BY GP DESC';
} else if ($split == 'level') {
    $selectSplitOverall.= 'gameResults.Level, ';
    $selectResultsOverallSplit = 'Level, '; 
    $selectResultsSplit = 'Level, ';
    $selectSplit = 'Level, ';
    $selectGameResultsSplit = 'gameList.Level, '; 
    $selectGameListSplit = 'Level, ';
    $selectBaseSplit .= 'Level, ';
    $joinByOverall .= ' AND gameEventStat.Level = gameResults.Level';
    $joinBy .= ' JOIN teamstatassignment ON teamassignment.idTeamassignment = teamstatassignment.idTeamAssignmentkey AND game.idsubseasonkey = teamstatassignment.idsubseasonkey
    JOIN level ON teamstatassignment.idLevelSeasonkey = level.idLevelSeason';
    $joinResultsBy = ' JOIN teamstatassignment ON teamassignment.idTeamassignment = teamstatassignment.idTeamAssignmentkey AND game.idsubseasonkey = teamstatassignment.idsubseasonkey
    JOIN level ON teamstatassignment.idLevelSeasonkey = level.idLevelSeason';
    $groupByOverall .= ', gameResults.Level';
    $groupBy .= ', Level';
    $groupByGame .= 'GROUP BY Level';
    $orderBy = 'ORDER BY SortPriority';
} else if ($split == 'alltime') {

} else if ($split == 'gameType') {
    $selectSplitOverall.= 'gameResults.SubseasonName, ';
    $selectResultsOverallSplit = 'SubseasonName, '; 
    $selectResultsSplit = 'SubseasonName, ';
    $selectSplit = 'SubseasonName, ';
    $selectGameResultsSplit = 'gameList.SubseasonName, '; 
    $selectBaseSplit .= 'SubseasonName, ';
    $selectGameListSplit = 'SubseasonName, ';
    $joinByOverall .= ' AND gameEventStat.SubseasonName = gameResults.SubseasonName';
    $joinBy .= 'JOIN subseason ON game.idsubseasonkey = subseason.idsubseason';
    $joinResultsBy = ' JOIN subseason ON game.idsubseasonkey = subseason.idsubseason';
    $groupByOverall .= ', gameResults.SubseasonName';
    $groupBy .= ', SubseasonName';
    $groupByGame .= 'GROUP BY SubseasonName';
} else if ($split == 'homeaway') {
    $selectSplitOverall.= 'gameResults.homeAway, ';
    $selectResultsOverallSplit = 'homeAway, '; 
    $selectSplit = 'homeAway, ';
    $selectGameResultsSplit = 'gameList.homeAway, '; 
    $selectBaseSplit .= 'IF(idTeamAssignmentkey = game.HomeTeam, "Home", "Away") AS "homeAway", ';
    $joinByOverall .= ' AND gameEventStat.homeAway = gameResults.homeAway';
    $groupByOverall .= ', gameResults.homeAway';
    $groupBy .= ', homeAway';
    $groupByGame .= 'GROUP BY homeAway';
} else if ($split == 'weekend') {
    $selectSplitOverall.= 'gameResults.dayType, ';
    $selectResultsOverallSplit = 'dayType, '; 
    $selectResultsSplit = 'CASE WHEN WEEKDAY(game.GameDate) = 5 OR WEEKDAY(game.GameDate) = 6 THEN "Weekend" ELSE "Weekday" END AS "dayType", ';
    $selectSplit = 'dayType, ';
    $selectGameResultsSplit = 'gameList.dayType, '; 
    $selectGameListSplit = 'CASE WHEN WEEKDAY(game.GameDate) = 5 OR WEEKDAY(game.GameDate) = 6 THEN "Weekend" ELSE "Weekday" END AS "dayType", ';
    $selectBaseSplit .= 'CASE WHEN WEEKDAY(game.GameDate) = 5 OR WEEKDAY(game.GameDate) = 6 THEN "Weekend" ELSE "Weekday" END AS "dayType", ';
    $joinByOverall .= ' AND gameEventStat.dayType = gameResults.dayType';
    $groupByOverall .= ', gameResults.dayType';
    $groupBy .= ', dayType';
    $groupByGame .= 'GROUP BY dayType';
} else if ($split == 'timestart') {
    $selectSplitOverall.= 'gameResults.timeType, ';
    $selectResultsOverallSplit = 'timeType, '; 
    $selectResultsSplit = 'CASE WHEN game.GameStartTime >= "22:00:00" THEN "Late" WHEN game.GameStartTime < "18:00:00" THEN "Early" ELSE "Normal" END AS "timeType", ';
    $selectSplit = 'timeType, ';
    $selectGameResultsSplit = 'gameList.timeType, '; 
    $selectGameListSplit = 'CASE WHEN game.GameStartTime >= "22:00:00" THEN "Late" WHEN game.GameStartTime < "18:00:00" THEN "Early" ELSE "Normal" END AS "timeType", '; 
    $selectBaseSplit .= 'CASE WHEN game.GameStartTime >= "22:00:00" THEN "Late" WHEN game.GameStartTime < "18:00:00" THEN "Early" ELSE "Normal" END AS "timeType", ';
    $joinByOverall .= ' AND gameEventStat.timeType = gameResults.timeType';
    $groupByOverall .= ', gameResults.timeType';
    $groupBy .= ', timeType';
    $groupByGame .= 'GROUP BY timeType';
}

$fullSQL = 'SELECT '.$selectSplitOverall.' gameResults.idTeamkey, team.TeamName AS TeamName, externalLargeImage AS teamimage, '.$selectDataSplit.'
    FROM (
        SELECT '.$selectResultsOverallSplit.' team, idTeamkey, COUNT(idGame) AS GP, SUM(W) AS W, SUM(OTL) AS OTL, SUM(L) AS L, SUM(T) AS T, SUM(W)*2+SUM(OTL)+SUM(T) AS Pts
        FROM (
            SELECT '.$selectResultsSplit.' "Home" as homeAway, HomeTeam AS team, idTeamkey, idGame, if (HomeGoals > AwayGoals, 1, 0) AS "W", if (HomeGoals < AwayGoals AND OTDetected = 0, 1, 0) AS "L", if (HomeGoals = AwayGoals, 1, 0) AS "T", if (HomeGoals < AwayGoals and OTDetected = 1, 1, 0) AS "OTL" FROM game JOIN teamassignment ON game.HomeTeam = teamassignment.idTeamAssignment 
            '.$joinBy.'
            WHERE '.$whereByOverallHome.'
            UNION ALL 
            SELECT '.$selectResultsSplit.' "Away" as homeAway, AwayTeam as team, idTeamkey, idGame, if (AwayGoals > HomeGoals, 1, 0) AS "W", if (AwayGoals < HomeGoals AND OTDetected = 0, 1, 0) AS "L", if (AwayGoals = HomeGoals, 1, 0) AS "T", if(AwayGoals < HomeGoals and OTDetected = 1, 1, 0) AS "OTL" FROM game JOIN teamassignment ON game.AwayTeam = teamassignment.idTeamAssignment 
            '.$joinBy.'
            WHERE '.$whereByOverallAway.'
        ) as resultsList
        '.$groupByGame.' '.$havingBy.' 
    ) AS gameResults
    JOIN team ON gameResults.idTeamkey = team.idTeam
    LEFT JOIN (
        SELECT '.$selectGameResultsSplit.' gameList.team, gameList.idTeamkey, SUM(SF) AS SF, SUM(SA) as SA, SUM(1stSF) AS 1stSF, SUM(1stSA) as 1stSA, SUM(2ndSF) AS 2ndSF, SUM(2ndSA) as 2ndSA, SUM(3rdSF) AS 3rdSF, SUM(3rdSA) as 3rdSA, SUM(OTSF) AS OTSF, SUM(OTSA) as OTSA, SUM(Pen) AS Pen, SUM(PK) AS PK, SUM(PIM) AS PIM, SUM(PenA) AS PenA, SUM(PP) AS PP, SUM(PIMA) AS PIMA, SUM(GF) AS GF, SUM(PPG) AS PPG, SUM(SHG) AS SHG, SUM(ENG) AS ENG, SUM(GA) AS GA, SUM(PPGA) AS PPGA, SUM(SHGA) AS SHGA, SUM(ENGA) AS ENGA
        FROM (
            SELECT '.$selectGameListSplit.' idGame, "Home" as homeAway, HomeTeam as team, teamassignment.idTeamkey, HomeShots as "SF", Home1stShots as "1stSF", Home2ndShots as "2ndSF", Home3rdShots AS "3rdSF", HomeOTShots as "OTSF", AwayShots as "SA", Away1stShots as "1stSA", Away2ndShots as "2ndSA", Away3rdShots as "3rdSA", AwayOTShots as "OTSA"
            FROM game 
            JOIN teamassignment ON game.HomeTeam = teamassignment.idTeamAssignment 
            '.$joinResultsBy.'
            WHERE '.$whereByOverallHome.'
            UNION ALL 
            SELECT '.$selectGameListSplit.' idGame, "Away" as homeAway, AwayTeam as team, teamassignment.idTeamkey, AwayShots as "SF", Away1stShots as "1stSF", Away2ndShots as "2ndSF", Away3rdShots as "3rdSF", AwayOTShots as "OTSF", HomeShots as "SA", Home1stShots as "1stSA", Home2ndShots as "2ndSA", Home3rdShots AS "3rdSA", HomeOTShots as "OTSA"
            FROM game
            JOIN teamassignment ON game.AwayTeam = teamassignment.idTeamAssignment 
            '.$joinResultsBy.'
            WHERE '.$whereByOverallAway.'
        ) AS gameList
        LEFT JOIN (
            SELECT '.$selectSplit.' idGame, team, idTeamkey, 
            SUM(CASE WHEN penaltyAgainst = "penaltyAgainst" THEN 1 ELSE 0 END) AS Pen,
            SUM(CASE WHEN penaltyAgainst = "penaltyAgainst" AND PowerPlay = 1 THEN 1 ELSE 0 END) AS PK,
            SUM(CASE WHEN penaltyAgainst = "penaltyAgainst" THEN PenaltyLength ELSE 0 END) AS PIM,
            SUM(CASE WHEN penaltyAgainst = "penaltyFor" THEN 1 ELSE 0 END) AS PenA,
            SUM(CASE WHEN penaltyAgainst = "penaltyFor" AND PowerPlay = 1 THEN 1 ELSE 0 END) AS PP,
            SUM(CASE WHEN penaltyAgainst = "penaltyFor" THEN PenaltyLength ELSE 0 END) AS PIMA
            FROM (
                SELECT '.$selectBaseSplit.' idGame, gameevents.idTeamAssignmentkey as "team", idTeamkey, "penaltyAgainst", if(gameevents.GamePeriod = 4, "OT", gameevents.GamePeriod) AS Period, gameevents.GameTime, penaltyevent.idPenaltykey, penaltyevent.idGameEvents AS penalty, PowerPlay, PenaltyLength
                FROM gameevents 
                JOIN penaltyevent ON gameevents.idGameEvents = penaltyevent.idGameEvents 
                JOIN game ON game.idGame = gameevents.idGamekey
                JOIN teamassignment ON gameevents.idTeamAssignmentkey = teamassignment.idTeamassignment
                '.$joinBy.'
                WHERE '.$whereBy.' 
                UNION ALL 
                SELECT '.$selectBaseSplit.' idGame, IF(gameevents.idTeamAssignmentkey = game.HomeTeam, game.AwayTeam, game.HomeTeam) AS "team", idTeamkey, "penaltyFor", if(gameevents.GamePeriod = 4, "OT", gameevents.GamePeriod) AS Period, gameevents.GameTime, penaltyevent.idPenaltykey, penaltyevent.idGameEvents AS penalty, PowerPlay, PenaltyLength
                FROM gameevents 
                JOIN penaltyevent ON gameevents.idGameEvents = penaltyevent.idGameEvents 
                JOIN game ON game.idGame = gameevents.idGamekey
                JOIN teamassignment ON if(gameevents.idTeamAssignmentkey = game.HomeTeam, game.AwayTeam, game.HomeTeam) = teamassignment.idTeamassignment
                '.$joinBy.'
                WHERE '.$whereBy.'
            ) AS PenaltyEventList
            GROUP BY idGame, '.$groupBy.'
        ) AS penaltyStat ON gameList.idGame = penaltyStat.idGame AND '.$joinPenaltyEventsBy.'
        LEFT JOIN (
            SELECT '.$selectSplit.' idGame, team, idTeamkey,  
            SUM(CASE WHEN GoalType = "GF" THEN 1 ELSE 0 END) AS GF, 
            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 = "GA" THEN 1 ELSE 0 END) AS GA, 
            SUM(CASE WHEN GoalType = "PPGA" THEN 1 ELSE 0 END) AS PPGA, 
            SUM(CASE WHEN GoalType = "SHGA" THEN 1 ELSE 0 END) AS SHGA,
            SUM(CASE WHEN GoalType = "ENGA" THEN 1 ELSE 0 END) AS ENGA
            FROM (
                SELECT '.$selectBaseSplit.' idGame, gameevents.idTeamAssignmentkey AS team, idTeamkey, if(gameevents.GamePeriod = 4, "OT", gameevents.GamePeriod) AS Period, gameevents.GameTime, gameevents.idGameEvents, "GF" AS GoalType
                FROM game
                JOIN gameevents ON gameevents.idGamekey = game.idGame 
                JOIN goalevent ON gameevents.idGameEvents = goalevent.idGameEvents 
                JOIN teamassignment ON gameevents.idTeamAssignmentkey = teamassignment.idTeamassignment
                '.$joinBy.'
                WHERE '.$whereBy.'
                UNION ALL
                SELECT '.$selectBaseSplit.' idGame, if(gameevents.idTeamAssignmentkey = game.HomeTeam, game.AwayTeam, game.HomeTeam) as team, idTeamkey, if(gameevents.GamePeriod = 4, "OT", gameevents.GamePeriod) AS Period, gameevents.GameTime, gameevents.idGameEvents, "GA" as GoalType
                FROM game
                JOIN gameevents ON gameevents.idGamekey = game.idGame 
                JOIN goalevent ON gameevents.idGameEvents = goalevent.idGameEvents 
                JOIN teamassignment ON if(gameevents.idTeamAssignmentkey = game.HomeTeam, game.AwayTeam, game.HomeTeam) = teamassignment.idTeamassignment
                '.$joinBy.'
                WHERE '.$whereBy.'
                UNION ALL
                SELECT '.$selectBaseSplit.' idGame, gameevents.idTeamAssignmentkey AS team, idTeamkey, if(gameevents.GamePeriod = 4, "OT", gameevents.GamePeriod) AS Period, gameevents.GameTime, gameevents.idGameEvents, 
                CASE
                WHEN GoalTypeShort = "PPG" THEN "PPG"
                WHEN GoalTypeShort = "SHG" THEN "SHG"
                WHEN GoalTypeShort = "ENG" THEN "ENG"
                WHEN GoalTypeShort = "PSG" THEN "PSG" END as GoalType
                FROM goalevent 
                JOIN goalclassevent ON goalevent.idGameEvents = goalclassevent.goaleventkey 
                JOIN (SELECT idgoalClass, GoalTypeShort FROM goalclass WHERE idGoalClass <> 6 AND idGoalClass <> 7) AS goaltypeLimited ON goalclassevent.goalclasskey = goaltypeLimited.idGoalClass 
                JOIN gameevents ON gameevents.idGameEvents = goalevent.idGameEvents 
                JOIN game ON gameevents.idGamekey = game.idGame
                JOIN teamassignment ON gameevents.idTeamAssignmentkey = teamassignment.idTeamassignment
                '.$joinBy.'
                WHERE '.$whereBy.'
                UNION ALL 
                SELECT '.$selectBaseSplit.' idGame, if(gameevents.idTeamAssignmentkey = game.HomeTeam, game.AwayTeam, game.HomeTeam) AS team, idTeamkey, if(gameevents.GamePeriod = 4, "OT", gameevents.GamePeriod) AS Period, gameevents.GameTime, gameevents.idGameEvents, 
                CASE
                    WHEN GoalTypeShort = "PPG" THEN "PPGA"
                    WHEN GoalTypeShort = "SHG" THEN "SHGA"
                    WHEN GoalTypeShort = "ENG" THEN "ENGA"
                    WHEN GoalTypeShort = "PSG" THEN "PSGA" END as GoalType
                FROM goalevent 
                JOIN goalclassevent ON goalevent.idGameEvents = goalclassevent.goaleventkey 
                JOIN (
                    SELECT idgoalClass, GoalTypeShort FROM goalclass WHERE idGoalClass <> 6 AND idGoalClass <> 7
                    ) AS goaltypeLimited ON goalclassevent.goalclasskey = goaltypeLimited.idGoalClass 
                JOIN gameevents ON gameevents.idGameEvents = goalevent.idGameEvents 
                JOIN game ON gameevents.idGamekey = game.idGame
                JOIN teamassignment ON if(gameevents.idTeamAssignmentkey = game.HomeTeam, game.AwayTeam, game.HomeTeam) = teamassignment.idTeamassignment
                '.$joinBy.'
                WHERE '.$whereBy.'
            ) AS GoalEventList 
            GROUP BY idGame, '.$groupBy.'
        ) AS goalStat ON gameList.idGame = goalStat.idGame AND '.$joinGoalEventsBy.' 
        GROUP BY '.$groupBy.'
    ) AS gameEventStat ON gameEventStat.team = gameResults.team '.$joinByOverall.'
GROUP BY '.$groupByOverall.'
'.$orderBy;

//print_r($fullSQL);
$PDOconnect = createDBH();
$fetchAllData = $PDOconnect->prepare($fullSQL);
$fetchAllData->bindParam(':idTeam', $currentteam, PDO::PARAM_INT);
$fetchAllData->execute();
$fetchedAllData = $fetchAllData->fetchAll(PDO::FETCH_ASSOC);

foreach ($fetchedAllData AS &$dataSet) {if (isset($dataSet['idTeamkey'])) {unset($dataSet['idTeamkey']); }}


//print_r($fetchedAllData);
$prepJSONteamstatArray = array(
	'dataarray' => $fetchedAllData,
	'tableid' => 'teamstat',
	'tableclass' => 'tablesorter',
	'tablesort' => $sortcondition,
	'linkslist' => array(array('link'=>'team?teamid=','linktarget'=>'idTeam','linkname'=>'Team')),
	'imagelist' => array(array('image'=>'teamimage', 'imagetarget'=>'Team')),
	'filterMenu' => $filterStatMenu,
	'groupMenu' => $filterGroupMenu,
	'subseasonMenu' => JSONSetMenuSubseason(),
    'splitMenu' => $filterSplitMenu
);

echo JSON_encode($prepJSONteamstatArray);

?>