<?php

  include("html_functions.php");

  $timeout=120;

  ################## PAGE INITIALIZATION #########################

  $lgid=$_SESSION['lgid'];

  $oldLgDate=$_SESSION['lgdate'];

  

  # Title Bar

  $title="Individual Table Load";

  

  page_head($title,$_SESSION['lgpath'],"","","statslab.js");

  echo " <div id='topbar'>\n";

  main_titlebar($_SESSION['lgid'],$_SESSION['lgpath'],$title);



  # Check User Table

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



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

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

  if (!mysqli_num_rows($result)) 

  {

	  # Create Admin Table

	  $query="CREATE TABLE IF NOT EXISTS ootp_sql_admin (scope VARCHAR(20) DEFAULT 'ALL', module VARCHAR(20), setting VARCHAR(60), identifier VARCHAR(100) DEFAULT -1, value VARCHAR(255), PRIMARY KEY (scope, module, setting, identifier))";

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

  }

  

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

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

  if (!mysqli_num_rows($result)) 

  {

	  echo "StatsLab Configuration Status: INSECUREbr/>\n";

	  echo " No user table defined.  Please load the human_managers table and change the commish password.<br/>\n";

  }

  else

  {

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

	  {

		  $query="SELECT human_manager_id FROM human_managers WHERE (human_manager_id=".$_SESSION['user_id'];

		  if (isset($_SESSION['user_was_commish'])) {$query.=" OR human_manager_id=".$_SESSION['user_was_commish'];}

		  $query.=") AND is_commish=1";

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

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

		  if (!mysqli_num_rows($result))

		  {

			  $query="SELECT value FROM ootp_sql_admin WHERE module='users' AND setting='commish' AND (identifier=".$_SESSION['user_id'];

			  if (isset($_SESSION['user_was_commish'])) {$query.=" OR identifier=".$_SESSION['user_was_commish'];}

			  $query.=")";

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

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

			  if (!mysqli_num_rows($result))

			  {

				  echo " </div>   <!-- END topbar DIV -->\n";

				  echo " <div id='contentpane'>\n";

				  echo "You must be a commisioner to access this page";

			

				  ################## END OF PAGE #########################

				  echo " </div>   <!-- END contentpane DIV -->\n";

				  page_foot($_SESSION['lgpath']);

				  exit;

			  }

		  }

	  }

	  else

	  {

		  echo " </div>   <!-- END topbar DIV -->\n";

		  echo " <div id='contentpane'>\n";

		  echo "You must be a commisioner to access this page";

	  

		  ################## END OF PAGE #########################

		  echo " </div>   <!-- END contentpane DIV -->\n";

		  page_foot($_SESSION['lgpath']);

		  exit;

	  }

  }

  mysqli_close($db);



  # Begin Page

  nav_toolbar('admin','Admin Pages');

  echo " </div>   <!-- END topbar DIV -->\n";

  echo " <div id='contentpane'>\n";



  ################## START OF PAGE CONTENT #########################

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

  $adminpath=$_SESSION['adminpath'];

  

  ##### Get File List #####

  $loadFileList[0]=(isset($_GET['filename'])?$_GET['filename']:"");

  if ($loadFileList[0]!="") {$cnt=1;} else {$cnt=0;}

  if (isset($_POST['loadList']))

  {

	  $cnt=count($_POST['loadList']);

       	  for ($i=0;$i<$cnt;$i++)

	  {

		  $loadFileList[$i]=$_POST['loadList'][$i];

	    	  #echo $_POST['loadList'][$i]."<br/>\n";

	  }

  }

  if (isset($loadFileList)) {asort($loadFileList);}

	

  ##### Load Selected Files #####

  for ($i=0;$i<$cnt;$i++)

  {

	  $loadFile=$loadFileList[$i];

	  if (($loadFile!="")&&(file_exists($adminpath."/".$loadFile)))

      	  {

		  echo " <div class='textbox' style='width:935px;'>\n";

		  set_time_limit($timeout);

	    	  $fnow=time();

		  $ex = explode(".",$loadFile);

	    	  echo " Loading $loadFile<br/>\n";

		  $tableName=$ex[0];

		  ## Import data

		  $file=$adminpath."/".$loadFile;

	    	  echo " Full path is $file<br/>\n";

		  $fr = fopen($file,"r");

		  $errCnt=0;

		  if (isset($errors))

		  { 

			  unset($errors);

			  unset($queries);

		  }

		  $qcnt=0;

		  $lrd=0;

		  unset($types);

		  while (!feof($fr))

		  {

			  $query=fgets($fr);

			  $lrd++;

			  $query=trim($query);

			  if (($query=="")||($query[0]=="#")) {continue;}

			  if (!ctype_alnum($query[0])) {continue;}

			  //$query=preg_replace("/([\xC2\xC3])([\x80-\xBF])/e","chr(ord('\\1')<<6&0xC0|ord('\\2')&0x3F)",$query);
			  $query=preg_replace_callback("/([\xC2\xC3])([\x80-\xBF])/", 
											function ($m) { return chr(ord($m[1]) << 6 & 0xC0 | ord($m[2]) & 0x3F); },
											$query) ;
				
			  /*

			  $query=str_replace(", , );",",1,1);",$query);

			  
			  $query=str_replace(", ,",",'',",$query);

			  $query=str_replace("#IND",0,$query);

			  $query=str_replace("#INF",0,$query);

			  if (($tableName=='players_career_batting_stats')||($tableName=='players_career_pitching_stats'))

			  {

				  $query=str_replace("insert into","insert ignore into",$query);

			  }

			  */

			  unset($qex);

			  $qex=explode(" ",$query);

			  $sqlcmd=$qex[0];

			  if (isset($types[$sqlcmd])) {$types[$sqlcmd]=$types[$sqlcmd]+1;} else {$types[$sqlcmd]=1;}

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

			  $qcnt++;

			  if ((($qcnt%1000)==0)&&($qcnt!=0)) {echo "... $qcnt queries executed<br/>\n";}

			  $err=mysqli_error($db);

			  if (($err!="") && ($query!=""))

			  {

				  $errors[$errCnt]=$err;

				  $queries[$errCnt]=$query;

				  $errCnt++;

			  }

			  if ((substr_count($query,"CREATE ")>0)&&(($tableName=='players_career_batting_stats')||($tableName=='players_career_pitching_stats')))

			  {

				  $query="ALTER TABLE $tableName ADD PRIMARY KEY (player_id,year,team_id,league_id,split_id);";

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

			  }

		  }

		  fclose($fr);



		  ## Table specific processing

		  switch ($tableName)

	   	  {

		  case 'leagues':

	       		  ## Get New League Date

			  echo "Checking Date<br/>\n";

			  $query="SELECT leagues.current_date,'$oldLgDate' as last_date,DATEDIFF(leagues.current_date,'$oldLgDate') as days FROM leagues WHERE league_id=".$_SESSION['lgid'].";";

			  if ($debug==1) {echo "$query<br/>\n";}

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

			  $row=mysqli_fetch_array($result);

			  $newLgDate=$row[0];

			  $simLength=$row['days'];

			  

			  if ($simLength>0)    ## Update of existing tables ##

			  {

			  	  $_SESSION['lgdate']=$newLgDate;

			       

				  ## Save off Settings

				  $query="INSERT INTO ootp_sql_admin (module, setting, value) VALUES ('admin','LastDate','$oldLgDate') ON DUPLICATE KEY UPDATE value='$oldLgDate';";

			  	  if ($debug==1) {echo "$query<br/>\n";}

				  mysqli_query($db,$query);

			  	  $query="INSERT INTO ootp_sql_admin (module, setting, value) VALUES ('admin','calcLength','$simLength') ON DUPLICATE KEY UPDATE value='$simLength';";

				  if ($debug==1) {echo "$query<br/>\n";}

			  	  mysqli_query($db,$query);

			  }

			  break;	

		  case 'human_managers':

	       		  ## Update Human Managers

			  echo "Processing Users<br/>\n";

			  ## Check if user table exists

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

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

			  if (!mysqli_num_rows($result))

			  {

			  	  ##### Initialize Table #####

				  $query="CREATE TABLE IF NOT EXISTS ootp_sql_users (human_manager_id INT, ip_address VARCHAR(100), last_date DATE, token VARCHAR(256) DEFAULT NULL, PRIMARY KEY (human_manager_id, ip_address));";

			  	  mysqli_query($db,$query);

			  }

 			  ## Get OOTP Managers

			  $query="SELECT human_manager_id,first_name,last_name FROM human_managers WHERE human_manager_id NOT IN (SELECT DISTINCT human_manager_id FROM ootp_sql_users);";

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

			  $updText="";

			  if (mysqli_num_rows($result))

			  {

			  	  while ($row=mysqli_fetch_array($result))

				  {

			  		  $hmid=$row['human_manager_id'];

			  

					  $escapedPW=addslashes("baseball");

					  $salt=bin2hex(random_bytes(32));

			  		  $saltedPW=$escapedPW . $salt;

			  

					  $hashedPW = hash('sha256', $saltedPW);

			  

					  $updText.="INSERT INTO ootp_sql_admin (scope,module,setting,identifier,value) VALUES ('ALL','admin','salt','".$hmid."','".$salt."') ON DUPLICATE KEY UPDATE value='$salt';\n";

			  		  $updText.="INSERT INTO ootp_sql_admin (scope,module,setting,identifier,value) VALUES ('ALL','admin','password','".$hmid."','".$hashedPW."') ON DUPLICATE KEY UPDATE value='$hashedPW'\n";

				  }

			  }

			  

			  ## Load Managers

			  if ($updText!="")

			  {

				  $updText=trim($updText,",");

			  	  $updText=trim($updText);

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

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

				  {

			  		  mysqli_query($db,$query);

					  $err=mysqli_error($db);

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

				  }

			  }



			  ##### Clear old user tokens #####

			  $query="DELETE FROM ootp_sql_users WHERE DATEDIFF(last_date,$newLgDate)<-30";

			  mysqli_query($db,$query);

			  break;

		  case 'games':

			  ##### Clear old user tokens #####

			  $query="DELETE FROM ootp_sql_admin WHERE setting='showScores'";

			  mysqli_query($db,$query);

			  break;

		  }

		  

		  $fend=time();

		  if ($errCnt==0)

	   	  {

			  $filesLoaded[$loadFile]=1;

		 	  echo "SUCCESSFUL! Processing took ".($fend-$fnow)." seconds<br/>\n";

			  $ltime=$fend-$fnow;

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

			  $query.="'File Load','Load Time','$loadFile'";

			  $query.=",'$fend|$ltime'";

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

			  mysqli_query($db,$query);

		  }

	   	  else

		  {

		 	  echo "ERRORS REPORTED!<br/>\n Processing took ".($fend-$fnow)." seconds<br/>\n";

		 	  for ($i=0;$i<$errCnt;$i++)

			  {

		      		  echo "   Query: ".$queries[$i]." -> Error: ".$errors[$i]."<br/>\n";

			  }

	   	  }

		  echo "... $qcnt queries executed from $lrd lines<br/>\n";

		  foreach ($types as $sqlcmd => $cmdcnt) {echo "... ... $cmdcnt $sqlcmd's<br/>\n";}

	    	  echo " </div>\n";

	  }

  }



  ##### Display SQL Files #####

  $loadCnt=0;

  if ($dir = opendir($adminpath))

  {

       	  ## Get loaded files

	  $query="SELECT * FROM ootp_sql_admin WHERE module='File Load' AND setting='Load Time';";

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

	  if (mysqli_num_rows($result))

      	  {

		  while ($row=mysqli_fetch_array($result))

	   	  {

			  $file=$row['identifier'];

		 	  $loadVal=$row['value'];

			  $e=explode("|",$loadVal);

		 	  $tables[$file]['loadTime']=$e[0];

			  $tables[$file]['loadSec']=$e[1];

	   	  }

	  }

       

	  ## Display Actions

	  echo " <div style='border:0;padding:0;margin:0;width:935px;text-align:right;'>\n";

       	  echo "  <a href='#' onclick=\"if (toggleAll('loadList[]',true)) return false;\">Check All</a> / <a href='#' onclick=\"if (toggleAll('loadList[]',false)) return false;\">Uncheck All</a>\n";

	  echo " </div>\n";

       

	  ## Display List

	  echo " <form action='./sql_file_load.php' method='post'>\n";    

       	  echo " <div class='textbox'>\n";

	  echo "  <table cellpadding=0 cellspacing=0 border=0><tr class='title'><td style='padding:3px;text-align:center;'>Uploaded SQL Files</td></tr><tr><td>\n";

       	  echo "   <table cellpadding=2 cellspacing=0 border=0 width='935px' class='sortable'>\n";

	  echo "    <thead><tr class='headline'><td class='hsc2_l'>Filename</td><td class='hsc2'>Timestamp</td><td class='hsc2'>Size</td><td class='hsc2'>Last Loaded</td><td class='hsc2' colspan=2>Actions</td></tr></thead>\n";

	  $cnt=0;

      while (false !== ($file = readdir($dir)))
	  {
          $ex = explode(".",$file);
		  $last = count($ex)-1;
          
          if (($ex[$last])!="sql") {continue;}

          $fileTime=filemtime($adminpath."/".$file);
          $loadTime=$tables[$file]['loadTime'];

          $fileList[$loadCnt]=$file;
          $loadCnt++;
      }

	  if ($loadCnt>0) {asort($fileList);}
      
      $isSplit=0;

	  foreach ($fileList as $key => $file)
      {
          $ex = explode(".",$file);
          $fileTime=filemtime($adminpath."/".$file);
          
          if (($isSplit==0)&&(substr_count($file,".mysql_")>0)) {$isSplit=1;}

		  $cls='s'.($cnt%2+1);
          echo "    <tr class='$cls'><td class='".$cls."_l'>$file";

		  if (isset($filesLoaded[$adminpath."/".$file])) {echo " - <b>LOADED</b>";}
          
          $fsize=filesize($adminpath."/".$file);

		  echo "</td><td sorttable_customkey='$fileTime'>".date("D M j, Y H:i",$fileTime)."</td>";
          
          echo "<td sorttable_customkey='$fsize'>".formatBytes($fsize)."</td>";

		  if (isset($tables[$file]['loadTime']))
	   	  {
              $loadTime=$tables[$file]['loadTime'];
              $loadSec=$tables[$file]['loadSec'];

			  echo "<td sorttable_customkey='$loadTime' title='Load Time: $loadSec Seconds'>".date("D M j, Y H:i",$loadTime)."</td>";
	   	  }
		  else {echo "<td>&nbsp;</td>";}
          
          echo "<td sorttable_customkey=1><a href='./sql_file_load.php?filename=$file'>Load</a>/<a href='./file_split.php?filename=$file'>Split</a>";

		  if (strpos($file,".mysql_")>0) {echo "/<a href='./file_split.php?delete=1&filename=$file'>Delete</a>";}

		  echo "</td>";
          
          echo "<td sorttable_customkey=1><input type='checkbox' name='loadList[]' value='$file' /></td>";
          
          echo "</tr>\n";

		  $cnt++;

      	  }

	  if ($isSplit==1)

      	  {

		  echo "    <tfoot><tr class='headline'><td class='hsc2' colspan=4>&nbsp;</td>";

	    	  echo "<td class='hsc2' colspan=2><a href='./file_split.php?filename=DELSPLITS'>Delete All Splits</a></td></tr></tfoot>\n";

	  }

       	  echo "    <tfoot><tr><td colspan=6><input type='submit' name='Load Checked' value='Load Checked' /></td></tr></tfoot>\n";

	  echo "   </table>\n";

       	  echo "  </td></tr></table>\n";

	  echo " </div>\n";

       	  echo " </form>\n";

       

	  ## Display Actions

	  echo " <div style='border:0;padding-top:10px;width:935px;text-align:right;'>\n";

       	  echo "  <a href='#' onclick=\"if (toggleAll('loadList[]',true)) return false;\">Check All</a> / <a href='#' onclick=\"if (toggleAll('loadList[]',false)) return false;\">Uncheck All</a>\n";

	  echo " </div>\n";

  }

  else

  {

       	  echo "ERROR: Unable to read directory ".$adminpath;

  }



  mysqli_close($db);



  ################## END OF PAGE #########################

  echo " </div>   <!-- END contentpane DIV -->\n";

  page_foot($_SESSION['lgpath']);





?>
