<?php

  include("html_functions.php");



  if ($_SESSION['user_is_commish']!=1)

  {

	  header('Location: '.$_SERVER['HTTP_REFERER']);

  }



  ##### Initialize variables

  include($_SESSION['adminpath']."/dbopen.php");



  # Get current date

  if (!isset($_SESSION['lgdate']))

  {

	  $query="SELECT leagues.current_date as lgdate FROM leagues LIMIT 1";

	  if ($_GET['showq']==1) {echo "$query<br/>\n";}

	  $result=mysqli_query($db,$query);

	  $row=mysqli_fetch_array($result);

	  $_SESSION['lgdate']=$row['lgdate'];

  }

  $curLgDate=$_SESSION['lgdate'];

  $date=new DateTime($_SESSION['lgdate']);

  $lgyear=$date->format("Y");



  # Find Leagues

  $query="SELECT league_id,abbr,if(start_date<'$curLgDate',YEAR(start_date),YEAR(start_date)-1) as start_year FROM leagues WHERE 1";

  if ($_GET['showq']==1) {echo "$query<br/>\n";}

  $result=mysqli_query($db,$query);

  if (!mysqli_num_rows($result)) {echo "ERROR: leagues table not loaded"; exit;}

  else

  {

       	  while ($row=mysqli_fetch_array($result))

	  {

	    	  $league_id=$row['league_id'];

		  $leagues[$league_id]['exists']=1;

		  $leagues[$league_id]['abbr']=$row['abbr'];

		  $leagues[$league_id]['start_year']=$row['start_year'];

	  }

  }



  ##### Check if park_factors table exists #####

  $query="SHOW TABLES LIKE 'park_factors';";

  if ($_GET['showq']==1) {echo "$query<br/>\n";}

  $result=mysqli_query($db,$query);

  if (!mysqli_num_rows($result))

  {

    	  ##Initialize Park Factors Table

	  $query="CREATE TABLE IF NOT EXISTS park_factors (team_id INT,league_id INT,park_id INT,year INT,rpf DOUBLE DEFAULT 1,hrpf DOUBLE DEFAULT 1,spf DOUBLE DEFAULT 1,zl DOUBLE DEFAULT 1,PRIMARY KEY (team_id,year));";

    	  $result=mysqli_query($db,$query);

  }





  ##### Loop Through Leagues #####

  $updText="";

  foreach ($leagues as $league_id => $val)

  {

     	##### Unset Arrays #####

	unset($teams);

     	unset($subleagues);

	unset($parks);



	$lgyear=$leagues[$league_id]['start_year'];



	##### Check if regular season games have been played #####

	$lgAbbr=$leagues[$league_id]['abbr'];

	$query="SELECT game_id FROM games WHERE league_id=$league_id AND played=1 AND game_type=0 LIMIT 1;";

	if ($_GET['showq']==1) {echo "$query<br/>\n";}

	$result=mysqli_query($db,$query);

	if (!mysqli_num_rows($result)) {$updText.="# $lgAbbr: No games played.  Skipping.\n";continue;}

    	else {$updText.="# $lgAbbr: Calculating park factors\n";}



	##Clear out current year's entries

	$query="DELETE FROM park_factors WHERE year=$lgyear AND league_id=$league_id;";

	mysqli_query($db,$query);

     

	###############################################################################################

	# Get League Structure

	# 

	###############################################################################################

	$query="SELECT team_id,sub_league_id,park_id FROM teams WHERE league_id=$league_id AND allstar_team=0 ORDER BY team_id;";

	if ($_GET['showq']==1) {echo "$query<br/>\n";}

     	$result=mysqli_query($db,$query);

	while ($row=mysqli_fetch_array($result))

     	{

		$parkID=$row['park_id'];

	   	$tid=$row['team_id'];

		$slID=$row['sub_league_id'];

	   	$teams[$tid]['park_id']=$parkID;

		$teams[$tid]['slID']=$slID;

	   	$subleagues[$slID]['nTeams']=$subleagues[$slID]['nTeams']+1;

	}



	###############################################################################################

	# Get Game Stats

	#

	###############################################################################################

	$query="SELECT game_id,home_team,away_team,runs0,runs1 FROM games WHERE league_id=$league_id AND game_type=0 AND played=1;";

	if ($_GET['showq']==1) {echo "$query<br/>\n";}

     	$result=mysqli_query($db,$query);

	while ($row=mysqli_fetch_array($result))

    	{

		$gid=$row['game_id'];   

	  	$hid=$row['home_team'];

		$aid=$row['away_team'];

	  	$aruns=$row['runs0'];

		$hruns=$row['runs1'];



		$parks['home'][$hid]['runs']=$parks['home'][$hid]['runs']+$hruns+$aruns;

	  	$parks['home'][$hid]['games']=$parks['home'][$hid]['games']+1;

		$parks['away'][$aid]['runs']=$parks['away'][$aid]['runs']+$hruns+$aruns;

	  	$parks['away'][$aid]['games']=$parks['away'][$aid]['games']+1;

		$parks['tot']['runs']=$parks['tot']['runs']+$hruns+$aruns;

	  	$parks['tot']['games']=$parks['tot']['games']+1;



		$teams[$hid]['rf']=$teams[$hid]['rf']+$hruns;

	  	$teams[$hid]['ra']=$teams[$hid]['ra']+$aruns;



		$teams[$aid]['rf']=$teams[$aid]['rf']+$aruns;

	  	$teams[$aid]['ra']=$teams[$aid]['ra']+$hruns;

	}



	$query="SELECT g.game_id,pgb.team_id,home_team,away_team,player_id,hr FROM players_game_batting as pgb,games as g WHERE g.league_id=$league_id AND pgb.game_id=g.game_id AND pgb.league_id=g.league_id AND game_type=0 AND played=1;";

	if ($_GET['showq']==1) {echo "$query<br/>\n";}

     	$result=mysqli_query($db,$query);

	while ($row=mysqli_fetch_array($result))

    	{

		$gid=$row['game_id'];

	  	$tid=$row['team_id'];

		$hid=$row['home_team'];

	  	$aid=$row['away_team'];

		$hr=$row['hr'];



		$parks['home'][$hid]['hrs']=$parks['home'][$hid]['hrs']+$hr;

	  	$parks['away'][$aid]['hrs']=$parks['away'][$aid]['hrs']+$hr;

		$parks['tot']['hrs']=$parks['tot']['hrs']+$hr;

    	}



	###############################################################################################

	# Get League Batting Stats

	#

	###############################################################################################

	$query="SELECT t.team_id,tr.sub_league_id,hr,r FROM team_batting_stats as t,team_relations as tr WHERE tr.league_id=$league_id AND t.league_id=tr.league_id AND t.team_id=tr.team_id;";

	if ($_GET['showq']==1) {echo "$query<br/>\n";}

     	$result=mysqli_query($db,$query);

	while ($row=mysqli_fetch_array($result))

    	{

		$slID=$row['sub_league_id'];

	  	$hr=$row['hr'];

		$r=$row['r'];



		$subleagues[$slID]['hr']=$subleagues[$slID]['hr']+$hr;

	  	$subleagues[$slID]['r']=$subleagues[$slID]['r']+$r;

	}



	###############################################################################################

	# Get Team and League Pitching Stats

	#

	################################################################################################

	$query="SELECT t.team_id,tr.sub_league_id,ip,r,ipf FROM team_pitching_stats as t,team_relations as tr WHERE tr.league_id=$league_id AND t.league_id=tr.league_id AND t.team_id=tr.team_id;";

	if ($_GET['showq']==1) {echo "$query<br/>\n";}

     	$result=mysqli_query($db,$query);

	while ($row=mysqli_fetch_array($result))

    	{

		$slID=$row['sub_league_id'];



		$actIP=($row['ip']*3+$row['ipf'])/3;

	  	$subleagues[$slID]['actIP']=$subleagues[$slID]['actIP']+$actIP;

		$subleagues[$slID]['pr']=$subleagues[$slID]['pr']+$row['r'];

    	}



	###############################################################################################

	# Calculate League Rate Stats

	#

	###############################################################################################

	foreach ($subleagues as $slID => $val)

    	{

		#Home Run Hit Rate

		$hr=$subleagues[$slID]['hr'];

		$r=$subleagues[$slID]['r'];

	  	if ($r==0) {$hrRate=0;} else {$hrRate=($hr/$r)*1.50;}

		$subleagues[$slID]['hrRate']=$hrRate;

    	}

     

	###############################################################################################

	# Calculate Park Factors

	# 

	###############################################################################################

	foreach ($teams as $tid => $val)

	{

	  	if ($tid=='tot') {continue;}

		if ($parks['home'][$tid]['games']==0) {$rpgHome=0;$hrpgHome=0;}

		else 

	 	{

			$rpgHome=$parks['home'][$tid]['runs']/$parks['home'][$tid]['games'];

	       		$hrpgHome=$parks['home'][$tid]['hrs']/$parks['home'][$tid]['games'];

		}



		if ($parks['away'][$tid]['games']==0) {$rpgAway=0;$hrpgAway=0;}

	 	else

		{

	       		$rpgAway=$parks['away'][$tid]['runs']/$parks['away'][$tid]['games'];

			$hrpgAway=$parks['away'][$tid]['hrs']/$parks['away'][$tid]['games'];

	 	}

	  

		if ($rpgAway==0) {$iPFr=1;} else {$iPFr=$rpgHome/$rpgAway;}

		if ($hrpgAway==0) {$iPFhr=1;} else {$iPFhr=$hrpgHome/$hrpgAway;}



		$slID=$teams[$tid]['slID'];

	  	$nTeams=$subleagues[$slID]['nTeams'];

		$slHRrate=$subleagues[$slID]['hrRate'];



		$rPF=(($nTeams-1)*$iPFr + $nTeams - $iPFr) / (2*($nTeams-1));

	  	$hrPF=(($nTeams-1)*$iPFhr + $nTeams - $iPFhr) / (2*($nTeams-1));



		$sPF=($rPF-$slHRrate*$hrPF) / (1-$slHRrate);

	  	$sPF=sqrt($sPF);



		$parks[$tid]['rPF']=$rPF;

	  	$parks[$tid]['hrPF']=$hrPF;

		$parks[$tid]['sPF']=$sPF;



		#Calculate Zero Level

		$ZL = ($subleagues[$slID]['pr']*9/$subleagues[$slID]['actIP']) * $rPF*1.52;

		$parks[$tid]['ZL']=$ZL;

    	}



	###############################################################################################

	# Generate SQL Queries

	#

	###############################################################################################

	foreach ($teams as $tid => $val)

    	{

		$rPF=$parks[$tid]['rPF'];

	  	$hrPF=$parks[$tid]['hrPF'];

		$sPF=$parks[$tid]['sPF'];

	  	$zl=$parks[$tid]['ZL'];

		$parkID=$teams[$tid]['park_id'];

	  	$updText.="INSERT INTO park_factors (team_id,league_id,park_id,year,rpf,hrpf,spf,zl)";

		$updText.=" VALUES ($tid,$league_id,$parkID,$lgyear,$rPF,$hrPF,$sPF,$zl)";

	  	$updText.=" ON DUPLICATE KEY UPDATE park_id=$parkID,rpf=$rPF,hrpf=$hrPF,spf=$sPF,zl=$zl";

		$updText.="\n";

	}

  }



  ###############################################################################################

  # Save Queries to File and Load into Database

  #

  ###############################################################################################

  if ($updText!="")

  {

	  $updText=trim($updText);

  	  $e=explode("\n",$updText);

	  foreach ($e as $key => $query)

  	  {

		  mysqli_query($db,$query);

  		  $err=mysqli_error($db);

		  if ($err!="") {echo $query."<br/>".$err."<br />\n";}

  	  }

  }



  # Save off date

  $scope='ALL';

  $module='admin';

  $setting='pfrun';



  $query="INSERT INTO ootp_sql_admin (scope, module, setting, value) VALUES (";

  $query.="'$scope', '$module', '$setting', '$curLgDate'";

  $query.=") ON DUPLICATE KEY UPDATE value='$curLgDate';";

  if ($_GET['showq']==1) {echo "$query<br/>\n";}

  mysqli_query($db,$query);



  $_SESSION[$setting]=1;



  mysqli_close($db);



  header('Location: '.$_SERVER['HTTP_REFERER']);



?>

