$currentteam, "levelid"=>$currentlevel, "divid"=>$currentdivision, "seasonid"=>$currentseason,"subseasonid"=>$currentsubseason, "page"=>$pageselect, "sort"=>$sortcondition); $setlink = ''; $uniqueelement = 0; foreach($startcondition as $var => $var_value) { if(!empty($var_value) && $var != $omit) { $uniqueelement ++; if($uniqueelement >1) { $setlink .= '&'; } $setlink .= $var.'='.$var_value; } } return $setlink; } */ function datapointaverage($datapoint, $average, $type) { $output = $datapoint/$average; if ($type == 'percent') {$output = round(($datapoint/$average)*100,2);} elseif ($type == 'number') {$output = round($datapoint/$average,2);} return $output; } /* function assemblesortmenu($link) { $PDOconnect = createDBH(); $Sortmenustart = $PDOconnect->prepare('SELECT TargetSQL, StatShort FROM SortingStatTable WHERE Goalie = 2 ORDER BY SortPriority ASC'); $Sortmenustart->execute(); $Sortingmenu = ''; while($rowsort = $Sortmenustart->fetch(PDO::FETCH_ASSOC)) { $Sortingmenu .= ' '.$rowsort['StatShort'].' | '; } return $Sortingmenu; } */ function sortinghandle($target) { $PDOconnect = createDBH(); $sortcondition = isset($_GET['sort']) ? $_GET['sort'] : NULL; $Sortmenufetch = $PDOconnect->prepare('SELECT TargetSQL FROM SortingStatTable WHERE Goalie = 2 AND StatShort = :selectsort'); $Sortmenufetch->bindParam(':selectsort', $target, PDO::PARAM_STR); $Sortmenufetch->execute(); $SortTargetfetch = $Sortmenufetch->fetch(PDO::FETCH_ASSOC); return $SortTargetfetch['TargetSQL'] ?? null; } function JSONSetMenuSubseason() { $pdoConnect = createDBH(); $currentseason = ( isset( $_GET['seasonid'] ) && is_numeric( $_GET['seasonid'] ) ) ? intval( $_GET['seasonid'] ) : 0; $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; $Subseasonfetchstart = 'SELECT DISTINCT idSubseason, SubSeasonName, Playoff FROM teamassignment JOIN teamstatassignment ON teamassignment.idTeamAssignment = teamstatassignment.idTeamAssignmentkey JOIN subseason ON teamstatassignment.idsubseasonkey = subseason.idsubseason'; $SetSQLWhere = ''; if ($currentseason != 0) { $SetSQLWhere .= ' WHERE idSeasonkey = :idseason'; } if ($currentlevel != 0) { $SetSQLWhere .= ' AND idLevelSeasonkey = :idlevel'; } elseif ($currentteam != 0) { $SetSQLWhere .= ' WHERE teamassignment.idTeamAssignment = :teamid'; } if (!empty($currentdivision)) { $SetSQLWhere .= ' AND TeamDivision = :division'; } $SetSQLEnd = ' ORDER BY SortOrder'; $Subseasonfetch = $pdoConnect->prepare($Subseasonfetchstart.$SetSQLWhere.$SetSQLEnd); if ($currentseason != 0) { $Subseasonfetch->bindParam(':idseason', $currentseason, PDO::PARAM_INT); } if ($currentlevel != 0) { $Subseasonfetch->bindParam(':idlevel', $currentlevel, PDO::PARAM_INT); } elseif ($currentteam != 0) { $Subseasonfetch->bindParam(':teamid', $currentteam, PDO::PARAM_INT); } if (!empty($currentdivision)) { $Subseasonfetch->bindParam(':division', $currentdivision, PDO::PARAM_STR); } $Subseasonfetch->execute(); $CountSubseason = $Subseasonfetch->rowCount(); $optionsList = array(); if ($CountSubseason > 1) { $RegularSeason = 0; $Playoffs = 0; while ($subseason = $Subseasonfetch->fetch(PDO::FETCH_ASSOC)) { if ($RegularSeason == 0 && $subseason['idSubseason'] == 1) { $RegularSeason = 1; } if ($Playoffs == 0 && $subseason['Playoff'] == 1) { $Playoffs = 1; $optionsList[] = array('value' => '99', 'text' => 'Playoffs'); if ($RegularSeason == 1) { array_unshift($optionsList, array('value' => '0', 'text' => 'Overall')); } } $optionsList[] = array('value'=> $subseason['idSubseason'], 'text' => $subseason['SubSeasonName']); } } return $optionsList; } function JSONteamstatfetch ($sort, $stathighlight, $teamhistory) { $PDOconnect = createDBH(); ##Gets necessary starting steps $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; $currentgroup = (isset( $_GET['group'] ) ? $_GET['group'] : $_GET['group'] = 'Season'); $splitcondition = isset($_GET['split']) ? $_GET['split'] : NULL; #Gets past team stats $TeamstatfetchSelect = 'SELECT idTeamAssignment, TeamName, externalLargeImage AS teamimage, cssCustom, SUM(TeamGamesPlayed) AS GP, SUM(TeamWins) AS W, SUM(TeamLoss) AS L, SUM(TeamTie) AS T, SUM(TeamOTLoss) AS OTL, SUM(TeamPts) AS Pts, SUM(TeamGoals) AS GF, SUM(TeamGoalsAgainst) AS GA, SUM(TeamGoals)-SUM(TeamGoalsAgainst) AS GD, SUM(TeamShots) AS SF, IF(SUM(TeamShots) > 0, (ROUND(SUM(TeamShots)/SUM(TeamGamesPlayed),2)), 0) As SPG, SUM(TeamShotsAgainst) AS SA, IF(SUM(TeamShotsAgainst) > 0, (ROUND(SUM(TeamShotsAgainst)/SUM(TeamGamesPlayed),2)), 0) As SAPG, SUM(TeamShots)-SUM(TeamShotsAgainst) AS SD, SUM(TeamPIM) AS PIM, SUM(TeamPN) AS PN, SUM(TeamPIMA) AS PIMA, SUM(TeamPNA) AS PNA, SUM(TeamPPChance) AS PP, SUM(TeamPPGoal) AS PPG, SUM(TeamPKChance) AS PK, SUM(TeamPPGAllowed) AS PPGA, SUM(SHG) AS SHG, SUM(SHGAllowed) AS SHGA, SUM(EmptyNetGoal) AS ENG, SUM(EmptyNetGoalAllowed) AS ENGA, (SUM(TeamPPGoal) + SUM(SHG) - SUM(TeamPPGAllowed) - SUM(SHGAllowed)) AS SpTEff, IF(SUM(TeamShots) > 0, (SUM(TeamGoals)-SUM(EmptyNetGoal))/(SUM(TeamShots)-SUM(EmptyNetGoal)), 0) AS ShotEff, IF(SUM(TeamShots) > 0, SUM(TeamShots)/SUM(TeamGamesPlayed), 0) AS SPG, IF(SUM(TeamShotsAgainst) > 0, ((SUM(TeamGoalsAgainst)-SUM(EmptyNetGoalAllowed))/(SUM(TeamShotsAgainst)-SUM(EmptyNetGoalAllowed))), 0) As DefEff, IF(SUM(TeamShotsAgainst) > 0, SUM(TeamShotsAgainst)/SUM(TeamGamesPlayed), 0) As SAPG, IF(SUM(TeamPPChance) > 0, SUM(TeamPPGoal)/SUM(TeamPPChance), 0) AS PPConvert, IF(SUM(TeamPKChance) > 0, 1-(SUM(TeamPPGAllowed)/SUM(TeamPKChance)), 0) AS PKConvert'; $TeamstatfetchFrom = ' FROM teamstatassignment JOIN teamassignment ON teamstatassignment.idTeamAssignmentkey = teamassignment.idTeamAssignment LEFT JOIN jjglover_GLSHockey_NewsCss.cssteamassignment ON idTeamAssignment = cssteamassignment.TeamidRef AND jjglover_GLSHockey_NewsCss.cssteamassignment.TeamtableRef = "teamassignment" JOIN team ON teamassignment.idTeamkey = team.idTeam JOIN subseason ON teamstatassignment.idsubseasonkey = subseason.idsubseason'; $SQLWhere = ''; $SQLJoin = ''; $SQLGroup = ''; $SQLOrderBy = ''; if ($teamhistory == 1) { #Find Team ID for history section $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); $idTeamHistory = $returnTeam['idTeamkey']; $SQLWhere .= ' WHERE idTeamkey = :teamid'; $TeamstatfetchSelect .= ', SeasonDisplay, SubSeasonName, idTeamAssignment'; $TeamstatfetchFrom .= ' JOIN season ON teamassignment.idSeasonkey = season.idSeason'; if (empty($sort)) { if($currentgroup == 'Subseason') {$SQLOrderBy = ' ORDER BY subseason.SortOrder DESC';} else {$SQLOrderBy = ' ORDER BY SeasonStart DESC, subseason.SortOrder DESC';} } else {$SQLOrderBy = ' ORDER BY '.$sort;} } 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 ($currentteam > 0 AND $teamhistory == 0) { $TeamstatfetchSelect .= ', SeasonDisplay, SubSeasonName, idTeamAssignment'; $TeamstatfetchFrom .= ' JOIN season ON teamassignment.idSeasonkey = season.idSeason'; $SQLOrderBy = ' ORDER BY SeasonStart DESC'; } if ($currentgroup == 'Season') { $SQLGroup = ' GROUP BY idTeamAssignment, idSeasonkey'; } elseif ($currentgroup == 'Subseason') { $SQLGroup = ' GROUP BY idsubseasonkey'; } elseif ($currentgroup == 'Subseason Breakdown') { $SQLGroup = ' GROUP BY idTeamAssignment, idSeasonkey, idsubseasonkey, idLevelSeasonkey'; } #Set Query for Team $Teamstatquery = $PDOconnect->prepare($TeamstatfetchSelect.$TeamstatfetchFrom.$SQLWhere.$SQLGroup.$SQLOrderBy); #echo 'test'; #echo $TeamstatfetchSelect.$TeamstatfetchFrom.$SQLWhere.$SQLGroup.$SQLOrderBy; if ($currentseason != 0) {$Teamstatquery->bindParam(':idseason', $currentseason, PDO::PARAM_INT);} if ($teamhistory == 1) {$Teamstatquery->bindParam(':teamid', $idTeamHistory, PDO::PARAM_INT);} if ($currentteam != 0 && $teamhistory == 0) {$Teamstatquery->bindParam(':teamid', $currentteam, PDO::PARAM_INT);} if ($currentsubseason != 0 && $currentsubseason != 99) {$Teamstatquery->bindParam(':idsubseason', $currentsubseason, PDO::PARAM_INT);} if ($currentlevel != 0) {$Teamstatquery->bindParam(':idlevel', $currentlevel, PDO::PARAM_INT);} if (!empty($currentdivision)) {$Teamstatquery->bindParam(':division', $currentdivision, PDO::PARAM_STR);} $Teamstatquery->execute(); $teamstatArray = array(); while ($teamstat = $Teamstatquery->fetch(PDO::FETCH_ASSOC)) { if ($currentteam > 0) { if ($currentgroup == "Season") {$teamRow = array('Season' => $teamstat['SeasonDisplay']);} elseif ($currentgroup == "Subseason") {$teamRow = array('Subseason' => $teamstat['SubSeasonName']);} else {$teamRow = array('Season' => $teamstat['SeasonDisplay'], 'Subseason' => $teamstat['SubSeasonName']);} } else {$teamRow = array();} //if (!empty($teamstat['cssCustom'])) {$cssTheme = $teamstat['cssCustom'];} else {$cssTheme = '';} $cssTheme = ""; $teamRow += ['idTeam' => $teamstat['idTeamAssignment'], 'Team' => $teamstat['TeamName'], 'idTeam' => $teamstat['idTeamAssignment'], 'teamimage' => $teamstat['teamimage'], 'TeamTheme' => $cssTheme, 'GP' => $teamstat['GP'], 'W' => $teamstat['W'], 'L' => $teamstat['L'], 'T' => $teamstat['T'], 'OTL' => $teamstat['OTL'], 'Pts' => $teamstat['Pts'], 'GF' => $teamstat['GF'], 'GA' => $teamstat['GA'], 'GD' => $teamstat['GD'], 'SF' => $teamstat['SF'], 'SPG' => round($teamstat['SPG'], 2), 'SA' => $teamstat['SA'], 'SAPG' => round($teamstat['SAPG'], 2),'SD' => $teamstat['SD'], 'ShotEff' => round($teamstat['ShotEff']*100, 2).'%', 'DefEff' => round(100-$teamstat['DefEff']*100, 2).'%', 'PIM' => $teamstat['PIM'], 'PN' => $teamstat['PN'], 'PIMA' => $teamstat['PIMA'], 'PNA' => $teamstat['PNA'], 'PP' => $teamstat['PP'], 'PPG' => $teamstat['PPG'], 'PP%' => round($teamstat['PPConvert']*100, 2).'%', 'PK' => $teamstat['PK'], 'PPGA' => $teamstat['PPGA'], 'PK%' => round($teamstat['PKConvert']*100,2).'%', 'SHG' => $teamstat['SHG'], 'SHGA' => $teamstat['SHGA'], 'SpTEff' => $teamstat['SpTEff'], 'ENG' => $teamstat['ENG'], 'ENGA' => $teamstat['ENGA']]; $teamstatArray[] = $teamRow; } return $teamstatArray; } $sortcondition = isset($_GET['sort']) ? $_GET['sort'] : NULL; /* $ResetLink = Createlink('sort'); if ($currentteam > 0) { $groupSelect = divBuilder('GroupSelector','id','Season Totals | Show Subseason Breakdown | Subseason Totals'); } else {$groupSelect ='';} */ #$FilterSet = assemblesortmenu($ResetLink); #$FilterMenuDiv = divBuilder('SortSelector', 'id', $FilterSet); function JSONassemblesortmenu() { $PDOconnect = createDBH(); $Sortmenustart = $PDOconnect->prepare('SELECT StatShort, StatFullName FROM SortingStatTable WHERE Goalie = 2 ORDER BY SortPriority ASC'); $Sortmenustart->execute(); $rowsort = array(); while ($statMenu = $Sortmenustart->fetch(PDO::FETCH_ASSOC)) { $rowsort[] = array('value' => $statMenu['StatShort'], 'text' => $statMenu['StatFullName']); } return $rowsort; } $Targetsort = sortinghandle($sortcondition); $currenthistory = isset($_GET['request']) ? $_GET['request'] : 'current'; if($currentteam > 0) { if($currenthistory == 'current'){ $filterSplitMenu = array( array('value' => 'standard', 'text'=>"Standard"), array('value' =>'gameType', 'text'=>"Subseason"), array('value' =>'level', 'text'=>"Level"), array('value'=>'location', 'text'=>"Location"), array('value'=>'homeaway', 'text'=>"Home vs Away"), array('value'=>'weekend', 'text'=>"Weekday vs Weekend"), array('value'=>'timestart', 'text'=>"Start Time Split"), array('value'=>'shotsPeriod', 'text'=>"Shots by Period"), array('value'=>'goalsPeriod', 'text'=>"Goals by Period"), array('value'=>'penaltyperiod', 'text'=>"Penalties by Period"), ); } else { $filterSplitMenu = array( array('value' => 'standard', 'text'=>"Standard"), array('value' => 'alltime', 'text'=>"Alltime"), array('value' =>'gameType', 'text'=>"Subseason"), array('value' =>'level', 'text'=>"Level"), array('value'=>'location', 'text'=>"Location"), array('value'=>'homeaway', 'text'=>"Home vs Away"), array('value'=>'weekend', 'text'=>"Weekday vs Weekend"), array('value'=>'timestart', 'text'=>"Start Time Split"), array('value'=>'shotsPeriod', 'text'=>"Shots by Period"), array('value'=>'goalsPeriod', 'text'=>"Goals by Period"), array('value'=>'penaltyperiod', 'text'=>"Penalties by Period"), ); } } else { $filterSplitMenu = null; } if ($currentteam > 0 AND $currenthistory == 'current') { $filterGroupMenu = array(array('value' => 'Season', 'text'=>"Season"), array('value'=>'Subseason Breakdown', 'text'=>"Subseason Breakdown")); $filterStatMenu = ''; } else if ($currentteam > 0 AND $_GET['request'] == 'history'){ $filterGroupMenu = array(array('value' => 'Season', 'text'=>"Season"), array('value'=>'Subseason Breakdown', 'text'=>"Subseason Breakdown"), array('value'=>'Subseason', 'text'=>"Subseason")); $filterStatMenu = ''; } else { $filterGroupMenu = ''; $filterStatMenu = JSONassemblesortmenu(); } if ($splitcondition != 'standard') { include_once 'teamStatSplitJSON.php'; } else { switch ($currenthistory) { case 'current': if (empty($sortcondition)) {$sortcondition = 'Pts';} $outputJSONArray = JSONteamstatfetch($Targetsort, $sortcondition, 0); break; case 'history': $outputJSONArray = JSONteamstatfetch($Targetsort, $sortcondition, 1); break; } $prepJSONteamstatArray = array( 'dataarray' => $outputJSONArray, '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 $Header; echo JSON_encode($prepJSONteamstatArray); } ?>