<?php

  include("html_functions.php");



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

  {

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

  }

  

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

  # Scouting

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



  # Initialize variables

  $settings['scoutsOn']=0;



  # Get admin settings

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

  $query="SELECT setting,value FROM ootp_sql_admin WHERE scope='ALL' AND module='admin';";

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

  if (mysqli_num_rows($result))

  {

	  while ($row=mysqli_fetch_array($result))

	  {

		  $setting=$row['setting'];

		  $settings[$setting]=$row['value'];

	  }

  }







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

  # SQL Table Manipulation

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

  # Delete Old Ratings Tables

  $query="DROP TABLE IF EXISTS players_batting_old;";

  mysqli_query($db,$query);

  $query="DROP TABLE IF EXISTS players_pitching_old;";

  mysqli_query($db,$query);

  $query="DROP TABLE IF EXISTS players_fielding_old;";

  mysqli_query($db,$query);

  # Rename Existing Ratings Tables as Old

  $query="SHOW TABLES LIKE 'players_%_scouted';";

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

  if (mysqli_num_rows($result)==3)

  {

	  $query="ALTER TABLE players_batting_scouted RENAME players_batting_old;";

	  mysqli_query($db,$query);

	  $query="ALTER TABLE players_pitching_scouted RENAME players_pitching_old;";

	  mysqli_query($db,$query);

	  $query="ALTER TABLE players_fielding_scouted RENAME players_fielding_old;";

	  mysqli_query($db,$query);

  }







  $scoutsOn=$settings['scoutsOn'];

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

  # Scout Players

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



  # Scout Batting Ratings

  $scoutArray=Array('batting_ratings_overall_contact', 'batting_ratings_overall_gap', 'batting_ratings_overall_eye', 'batting_ratings_overall_strikeouts', 'batting_ratings_overall_hp', 'batting_ratings_overall_power', 'batting_ratings_overall_babip', 'batting_ratings_vsr_contact', 'batting_ratings_vsr_gap', 'batting_ratings_vsr_eye', 'batting_ratings_vsr_strikeouts', 'batting_ratings_vsr_hp', 'batting_ratings_vsr_power', 'batting_ratings_vsr_babip', 'batting_ratings_vsl_contact', 'batting_ratings_vsl_gap', 'batting_ratings_vsl_eye', 'batting_ratings_vsl_strikeouts', 'batting_ratings_vsl_hp', 'batting_ratings_vsl_power', 'batting_ratings_vsl_babip', 'batting_ratings_talent_contact', 'batting_ratings_talent_gap', 'batting_ratings_talent_eye', 'batting_ratings_talent_strikeouts', 'batting_ratings_talent_hp', 'batting_ratings_talent_power', 'batting_ratings_talent_babip', 'batting_ratings_misc_bunt', 'batting_ratings_misc_bunt_for_hit', 'running_ratings_speed','running_ratings_stealing','running_ratings_baserunning');

  

  $pquery="SELECT player_id,age,running_ratings_speed,running_ratings_stealing,running_ratings_baserunning FROM players WHERE retired=0 AND hidden=0";



  $query="SELECT pr.player_id";

  $cnt=0;

  foreach ($scoutArray as $key => $ratLbl)

  {

	  $scaleLbl=get_scale($ratLbl);

	  $scale=$settings[$scaleLbl];



	  if ($scale=='hidden')

	  {

		  $query.=",0 as $ratLbl";

		  continue;

	  }



	  # Get Scale Adjustment

	  switch ($scale)

	  {

	  case 'hidden': $ratAdj=0; break;

	  case '1-5'   : $ratAdj=200; break;

	  case '2-8'   : $ratAdj=200; break;

	  case '1-10'  : $ratAdj=200; break;

	  case '20-80' : $ratAdj=200; break;

	  case '1-100' : $ratAdj=150; break;

	  }

	  switch ($scaleLbl)

	  {

	  case 'Talents': $ratAdj=$ratAdj; break;

	  default       : $ratAdj=0.5*$ratAdj; break;

	  }



	  switch ($cnt%5)

	  {

	  case 0: $prime=113; break;

	  case 1: $prime=191; break;

	  case 2: $prime=229; break;

	  case 3: $prime=349; break;

	  case 4: $prime=463; break;

	  case 5: $prime=211; break;

	  case 6: $prime=313; break;

	  }



	  $half=($prime-1)/2;

	  $adj=$ratAdj/$half;



	  # Determine Scouted Rating

	  $calc="0.6*(p.player_id%$prime-$half)/$half";

	  $ageAdj="IF(age<19,.75,IF(age>35,0.1,0.0003*age*age - 0.0542*age + 1.6515))";



	  $calc="ROUND(IF(($ageAdj*$calc)>=0,(255-$ratLbl)*($ageAdj*$calc),($ratLbl-1)*($ageAdj*$calc)),0)";



	  $query.=",IF($ratLbl=0,0,$ratLbl+$calc) as $ratLbl";



	  $cnt++;

  }

  $query.=" FROM ($pquery) as p,players_batting as pr WHERE p.player_id=pr.player_id";



  # Make sure talent is always bigger than rating

  $talQuery="SELECT player_id";

  foreach ($scoutArray as $key => $ratLbl)

  {

	  $e=explode("_",$ratLbl);

	  switch ($e[2])

	  {

	  case 'overall':

		  $talLbl=str_replace('overall','talent',$ratLbl);

		  $talQuery.=",IF($talLbl>=$ratLbl,$ratLbl,IF($ratLbl>128,$talLbl,$ratLbl)) as $ratLbl";

		  break;

	  case 'vsl':

		  $talLbl=str_replace('vsl','talent',$ratLbl);

		  $ovrLbl=str_replace('vsl','overall',$ratLbl);

		  $talQuery.=",IF($ratLbl>(IF($ratLbl>=$ovrLbl,$ratLbl,IF($ovrLbl>128,$ratLbl,$ovrLbl))),IF($ratLbl>=$ovrLbl,$ratLbl,IF($ovrLbl>128,$ratLbl,$ovrLbl)),$ratLbl) as $ratLbl";

		  break;

	  case 'vsr':

		  $talLbl=str_replace('vsr','talent',$ratLbl);

		  $ovrLbl=str_replace('vsr','overall',$ratLbl);

		  $talQuery.=",IF($ratLbl>(IF($ratLbl>=$ovrLbl,$ratLbl,IF($ovrLbl>128,$ratLbl,$ovrLbl))),IF($ratLbl>=$ovrLbl,$ratLbl,IF($ovrLbl>128,$ratLbl,$ovrLbl)),$ratLbl) as $ratLbl";

		  break;

	  case 'talent':

		  $ovrLbl=str_replace('talent','overall',$ratLbl);

		  $talQuery.=",IF($ratLbl>=$ovrLbl,$ratLbl,IF($ovrLbl>128,$ratLbl,$ovrLbl)) as $ratLbl";

		  break;

	  default:

		  $talQuery.=",$ratLbl";

		  break;

	  }

  }

  $talQuery.=" FROM ($query) as t";

  $query="CREATE TABLE players_batting_scouted AS ($talQuery)";

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

  mysqli_query($db,$query);



  # Scout Pitching Ratings

  $scoutArray=Array('pitching_ratings_overall_stuff', 'pitching_ratings_overall_control', 'pitching_ratings_overall_movement', 'pitching_ratings_overall_balk', 'pitching_ratings_overall_hp', 'pitching_ratings_overall_wild_pitch', 'pitching_ratings_vsr_stuff', 'pitching_ratings_vsr_control', 'pitching_ratings_vsr_movement', 'pitching_ratings_vsr_balk', 'pitching_ratings_vsr_hp', 'pitching_ratings_vsr_wild_pitch', 'pitching_ratings_vsl_stuff', 'pitching_ratings_vsl_control', 'pitching_ratings_vsl_movement', 'pitching_ratings_vsl_balk', 'pitching_ratings_vsl_hp', 'pitching_ratings_vsl_wild_pitch', 'pitching_ratings_talent_stuff', 'pitching_ratings_talent_control', 'pitching_ratings_talent_movement', 'pitching_ratings_talent_balk', 'pitching_ratings_talent_hp', 'pitching_ratings_talent_wild_pitch', 'pitching_ratings_pitches_fastball', 'pitching_ratings_pitches_slider', 'pitching_ratings_pitches_curveball', 'pitching_ratings_pitches_screwball', 'pitching_ratings_pitches_forkball', 'pitching_ratings_pitches_changeup', 'pitching_ratings_pitches_sinker', 'pitching_ratings_pitches_splitter', 'pitching_ratings_pitches_knuckleball', 'pitching_ratings_pitches_cutter', 'pitching_ratings_pitches_circlechange', 'pitching_ratings_pitches_knucklecurve', 'pitching_ratings_pitches_talent_fastball', 'pitching_ratings_pitches_talent_slider', 'pitching_ratings_pitches_talent_curveball', 'pitching_ratings_pitches_talent_screwball', 'pitching_ratings_pitches_talent_forkball', 'pitching_ratings_pitches_talent_changeup', 'pitching_ratings_pitches_talent_sinker', 'pitching_ratings_pitches_talent_splitter', 'pitching_ratings_pitches_talent_knuckleball', 'pitching_ratings_pitches_talent_cutter', 'pitching_ratings_pitches_talent_circlechange', 'pitching_ratings_pitches_talent_knucklecurve', 'pitching_ratings_misc_velocity', 'pitching_ratings_misc_stamina', 'pitching_ratings_misc_ground_fly', 'pitching_ratings_misc_hold');



  $pquery="SELECT player_id,age FROM players WHERE retired=0 AND hidden=0";

  $query="SELECT pr.player_id";

  $cnt=0;

  foreach ($scoutArray as $key => $ratLbl)

  {

	  $scaleLbl=get_scale($ratLbl);

	  $scale=$settings[$scaleLbl];



	  if ($scale=='hidden')

	  {

		  $query.=",0 as $ratLbl";

		  continue;

	  }



	  # Get Scale Adjustment

	  switch ($scale)

	  {

	  case 'hidden': $ratAdj=0; break;

	  case '1-5'   : $ratAdj=200; break;

	  case '2-8'   : $ratAdj=200; break;

	  case '1-10'  : $ratAdj=200; break;

	  case '20-80' : $ratAdj=200; break;

	  case '1-100' : $ratAdj=150; break;

	  }

	  switch ($scaleLbl)

	  {

	  case 'Talents': $ratAdj=$ratAdj; break;

	  default       : $ratAdj=0.5*$ratAdj; break;

	  }



	  switch ($cnt%5)

	  {

	  case 0: $prime=113;  break;

	  case 1: $prime=191; break;

	  case 2: $prime=229; break;

	  case 3: $prime=349; break;

	  case 4: $prime=463; break;

	  case 5: $prime=211; break;

	  case 6: $prime=313; break;

	  }



	  $half=($prime-1)/2;

	  $adj=$ratAdj/$half;



	  # Determine Scouted Rating

	  $calc="0.6*(p.player_id%$prime-$half)/$half";

	  $ageAdj="IF(age<19,.75,IF(age>35,0.1,0.0003*age*age - 0.0542*age + 1.6515))";



	  $calc="ROUND(IF(($ageAdj*$calc)>=0,(255-$ratLbl)*($ageAdj*$calc),($ratLbl-1)*($ageAdj*$calc)),0)";



	  switch ($ratLbl)

	  {

	  case 'pitching_ratings_misc_velocity':

		  $calc="$ratLbl+((p.player_id%3)-1)";

		  break;

	  case 'pitching_ratings_misc_ground_fly':

		  $calc="$ratLbl+((p.player_id%5)-2)";

		  break;

	  default:

		  $calc="$ratLbl+$calc";

		  break;

	  }



	  $query.=",IF($ratLbl=0,0,$calc) as $ratLbl";



	  $cnt++;

  }

  $query.=" FROM ($pquery) as p,players_pitching as pr WHERE p.player_id=pr.player_id";



  # Make sure talent is always bigger than rating

  $talQuery="SELECT player_id";

  foreach ($scoutArray as $key => $ratLbl)

  {

	  $e=explode("_",$ratLbl);



	  if ($e[2]=='pitches')

	  {

		  switch ($e[3])

		  {

		  case 'talent':

			  $ovrLbl=str_replace('talent_','',$ratLbl);

			  $talQuery.=",IF($ratLbl>=$ovrLbl,$ratLbl,IF($ovrLbl>128,$ratLbl,$ovrLbl)) as $ratLbl";

			  break;

		  default:   //Overall for pitches

			  $talLbl=str_replace('pitches_','pitches_talent_',$ratLbl);

			  $talQuery.=",IF($talLbl>=$ratLbl,$ratLbl,IF($ratLbl>128,$talLbl,$ratLbl)) as $ratLbl";

			  break;

		  }

	  }

	  else   //Non pitches

	  {

		  switch ($e[2])

		  {

		  case 'overall':

			  $talLbl=str_replace('overall','talent',$ratLbl);

			  $talQuery.=",IF($talLbl>=$ratLbl,$ratLbl,IF($ratLbl>128,$talLbl,$ratLbl)) as $ratLbl";

			  break;

		  case 'vsl':

			  $talLbl=str_replace('vsl','talent',$ratLbl);

			  $ovrLbl=str_replace('vsl','overall',$ratLbl);

			  $talQuery.=",IF($ratLbl>(IF($ratLbl>=$ovrLbl,$ratLbl,IF($ovrLbl>128,$ratLbl,$ovrLbl))),IF($ratLbl>=$ovrLbl,$ratLbl,IF($ovrLbl>128,$ratLbl,$ovrLbl)),$ratLbl) as $ratLbl";

			  break;

		  case 'vsr':

			  $talLbl=str_replace('vsr','talent',$ratLbl);

			  $ovrLbl=str_replace('vsr','overall',$ratLbl);

			  $talQuery.=",IF($ratLbl>(IF($ratLbl>=$ovrLbl,$ratLbl,IF($ovrLbl>128,$ratLbl,$ovrLbl))),IF($ratLbl>=$ovrLbl,$ratLbl,IF($ovrLbl>128,$ratLbl,$ovrLbl)),$ratLbl) as $ratLbl";

			  break;

		  case 'talent':

			  $ovrLbl=str_replace('talent','overall',$ratLbl);

			  $talQuery.=",IF($ratLbl>=$ovrLbl,$ratLbl,IF($ovrLbl>128,$ratLbl,$ovrLbl)) as $ratLbl";

			  break;

		  default:

			  $talQuery.=",$ratLbl";

			  break;

		  }

	  }

  }

  $talQuery.=" FROM ($query) as t";

  $query="CREATE TABLE players_pitching_scouted AS ($talQuery)";

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

  mysqli_query($db,$query);





  # Scout Fielding Ratings

  $scoutArray=Array('fielding_ratings_infield_range', 'fielding_ratings_infield_arm', 'fielding_ratings_turn_doubleplay', 'fielding_ratings_outfield_range', 'fielding_ratings_outfield_arm', 'fielding_ratings_catcher_arm', 'fielding_ratings_catcher_ability', 'fielding_ratings_infield_error', 'fielding_ratings_outfield_error', 'fielding_experience0', 'fielding_experience1', 'fielding_experience2', 'fielding_experience3', 'fielding_experience4', 'fielding_experience5', 'fielding_experience6', 'fielding_experience7', 'fielding_experience8', 'fielding_experience9', 'fielding_rating_pos1', 'fielding_rating_pos2', 'fielding_rating_pos3', 'fielding_rating_pos4', 'fielding_rating_pos5', 'fielding_rating_pos6', 'fielding_rating_pos7', 'fielding_rating_pos8', 'fielding_rating_pos9');

  

  $pquery="SELECT player_id,age FROM players WHERE retired=0 AND hidden=0";

  $query="SELECT pr.player_id";

  $cnt=0;

  foreach ($scoutArray as $key => $ratLbl)

  {

	  $scaleLbl=get_scale($ratLbl);

	  $scale=$settings[$scaleLbl];



	  if ($scale=='hidden')

	  {

		  $query.=",0 as $ratLbl";

		  continue;

	  }



	  # Get Scale Adjustment

	  switch ($scale)

	  {

	  case 'hidden': $ratAdj=0; break;

	  case '1-5'   : $ratAdj=200; break;

	  case '2-8'   : $ratAdj=200; break;

	  case '1-10'  : $ratAdj=200; break;

	  case '20-80' : $ratAdj=200; break;

	  case '1-100' : $ratAdj=150; break;

	  }

	  switch ($scaleLbl)

	  {

	  case 'Talents': $ratAdj=$ratAdj; break;

	  default       : $ratAdj=0.5*$ratAdj; break;

	  }



	  switch ($cnt%5)

	  {

	  case 0: $prime=113;  break;

	  case 1: $prime=191; break;

	  case 2: $prime=229; break;

	  case 3: $prime=349; break;

	  case 4: $prime=463; break;

	  }



	  $half=($prime-1)/2;

	  $adj=$ratAdj/$half;



	  # Determine Scouted Rating

	  $calc="0.6*(p.player_id%$prime-$half)/$half";

	  $ageAdj="IF(age<19,.75,IF(age>35,0.1,0.0003*age*age - 0.0542*age + 1.6515))";



	  $calc="ROUND(IF(($ageAdj*$calc)>=0,(255-$ratLbl)*($ageAdj*$calc),($ratLbl-1)*($ageAdj*$calc)),0)";



	  $query.=",IF($ratLbl=0,0,$ratLbl+$calc) as $ratLbl";



	  $cnt++;

  }

  $query.=" FROM ($pquery) as p,players_fielding as pr WHERE p.player_id=pr.player_id";

  $query="CREATE TABLE players_fielding_scouted AS ($query)";

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

  mysqli_query($db,$query);



  # Save off date

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

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

  $row=mysqli_fetch_array($result);

  $value=$row['current_date'];



  $scope='ALL';

  $module='admin';

  $setting='sctrun';



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

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

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

  mysqli_query($db,$query);



  $_SESSION['sctrun']=1;

  

  mysqli_close($db);



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



?>

