<?php


	define('_PATH', dirname(__FILE__));
	
	header( 'content-type: text/html; charset=utf-8' );
		
	echo '<a href="blitz_admin.php">retour ...</a>  
	<br>
	<br>';

/*
	$jour = date('d-m-Y');
	$heure = date("H_i_s");
	$infos = $jour.'-'.$heure;
	$fichier = 'dump_blitz_db_backup_'.$infos.'.sql';
	
	$dir = dirname(__FILE__) . '/dump.sql';

	echo "<h3>Backing up database to `<code>{$dir}</code>`</h3>";
	
	$chemin_backup = "/homez.528/analogde/www/WORKAREA/WEBSITE/BLITZ/backup_blitz/" .$fichier;
	
	//system("mysqldump --host=analogdepat.mysql.db --user=analogdepat --password=Un92pac007 --default-character-set=utf8 analogdepat BLITZ_affectation BLITZ_classement BLITZ_evolution BLITZ_parties > $fichier");

	

		$host    = "analogdepat.mysql.db";
   	    $user   = "analogdepat";
 	    $pass   = "Un92pac007";
	    $database   = "analogdepat";

	exec("mysqldump --user={$user} --password={$pass} --host={$host} {$database} --result-file={$dir} 2>&1", $output);

var_dump($output);


 $backup_file = $dbname . date("Y-m-d-H-i-s") . '.gz';
   $command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass ". "test_db | gzip > $backup_file";
   
   system($command);
 */  
   
function bidule()
{
		$mysqlDatabaseName = "analogdepat";
		$mysqlUserName = "analogdepat";
		$mysqlPassword ="Un92pac007";;
		$mysqlHostName = "analogdepat.mysql.db";
		$mysqlExportPath ='nom-du-fichier-dexport.sql';

		//Veuillez ne pas modifier les points suivants
		//Exportation de la base de données et résultat
		$command='mysqldump --opt -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' > ' .$mysqlExportPath;
		exec($command,$output=array(),$worked);

		switch($worked)
		{
			case 0:
					echo 'La base de données <b>' .$mysqlDatabaseName .'</b> a été stockée avec succès dans le chemin suivant '.getcwd().'/' .$mysqlExportPath .'</b>';
					break;
			case 1:
					echo 'Une erreur s est produite lors de la exportation de <b>' .$mysqlDatabaseName .'</b> vers'.getcwd().'/' .$mysqlExportPath .'</b>';
					break;
			case 2:
					echo 'Une erreur d exportation s est produite, veuillez vérifier les informations suivantes : <br/><br/><table><tr><td>MySQL Database Name:</td><td><b>' .$mysqlDatabaseName .'</b></td></tr><tr><td>MySQL User Name:</td><td><b>' .$mysqlUserName .'</b></td></tr><tr><td>MySQL Password:</td><td><b>NOTSHOWN</b></td></tr><tr><td>MySQL Host Name:</td><td><b>' .$mysqlHostName .'</b></td></tr></table>';
					break;
		}

}
   
/*****************************************************************************/   
   
function backup_tables($host, $user, $pass, $name, $tables )  // $tables = '*'
{
			$data = "\n/*---------------------------------------------------------------".
					"\n  SQL DB BACKUP ".date("d.m.Y H:i")." ".
					"\n  HOST: {$host}".
					"\n  DATABASE: {$name}".
					"\n  TABLES: {$tables}".
					"\n  ---------------------------------------------------------------*/\n";

					$link = mysql_connect($host,$user,$pass);
					mysql_select_db($name,$link);
					mysql_query( "SET NAMES `utf8` COLLATE `utf8_general_ci`" , $link ); // Unicode

					if($tables == '*')
					{ 	//get all of the tables
						$tables = array();
						$result = mysql_query("SHOW TABLES");
						while($row = mysql_fetch_row($result))
						{
							$tables[] = $row[0];
						}
					}
					else
					{
						$tables = is_array($tables) ? $tables : explode(',',$tables);
					}

					echo " Nombres de table incluses dans le backup " .count($tables);
					
					echo "<br/>";
					echo "<br/>";

					print_r($tables);
  
					foreach($tables as $table)
					{
						$data.= "\n/*---------------------------------------------------------------".
								"\n  TABLE: `{$table}`".
								"\n  ---------------------------------------------------------------*/\n";  
		
						$data.= "DROP TABLE IF EXISTS `{$table}`;\n";
						
						//$res = mysql_query("SHOW CREATE TABLE `{$table}`", $link);
						//$row = mysql_fetch_row($res);
						//$data.= $row[1].";\n";

						$result = mysql_query("SELECT * FROM `{$table}`", $link);
						$num_rows = mysql_num_rows($result);    

						if($num_rows>0)
						{
		
							$vals = Array(); $z=0;
		  
							for($i=0; $i<$num_rows; $i++)
							{
								$items = mysql_fetch_row($result);
								$vals[$z]="(";
								
								for($j=0; $j<count($items); $j++)
								{
									if (isset($items[$j])) 
									{ 
										$vals[$z].= "'".mysql_real_escape_string( $items[$j], $link )."'"; 
									} 
									else 
									{ 
										$vals[$z].= "NULL";
										}
									if ($j<(count($items)-1))
									{ 
										$vals[$z].= ",";
									}
								}
								
								$vals[$z].= ")"; $z++;
							}
		  
							$data.= "INSERT INTO `{$table}` VALUES ";      
							$data .= "  ".implode(";\nINSERT INTO `{$table}` VALUES ", $vals).";\n";
						}
					}
					
					
					echo "<br/>";
					echo "<br/>";
					
					echo "******** Data extraites ********";
					
					echo "<br/>";
					echo "<br/>";
					
					echo "<pre>";
					print_r($data);
					echo "<pre/>";

					echo "<br/>";
					echo "<br/>";
  
					mysql_close( $link );
					//return $data;
 
					echo "******** Nom du fichier ********";
					echo "<br/>";
					//$backup_file = 'blitz_db-backup-'.date("d.m.Y_H:i").'.sql';
					$backup_file = = 'blitz_manual_db_tables_backup_'.date("d.m.Y_H:i").'.sql';
					
					echo $backup_file;
					
					echo "<br/>";
					echo "<br/>";

					echo "******** Nom du l'archive aprés compression ********";
					echo "<br/>";
					//$backup_file_zipped = 'blitz_db-backup-'.date("d.m.Y_H:i").'.zip';
					$backup_file_zipped = 'blitz_manual_db_tables_backup_'.date("d.m.Y_H:i").'.zip';
					
					echo $backup_file_zipped;
			
			
					$handle = fopen($backup_file,'w+');
					fwrite($handle,$data);
					fclose($handle);
			
					zip_file($backup_file, $backup_file_zipped);
  
}

/*****************************************************************************/   

function zip_file($backup_file, $backup_file_zipped)
{
	$zip = new ZipArchive;
	if ($zip->open($backup_file_zipped , ZipArchive::CREATE | ZipArchive::OVERWRITE ) === TRUE)
	{
		// Add file to the zip file
		
		$chemin_zip = _PATH."/backup_blitz/";
		
		//echo ">>>>> " $chemin_zip;
		
		$zip->addFile($backup_file);
		// All files are added, so close the zip file.
		$zip->close();
	}
	
	copy($backup_file_zipped, 'backup_blitz/'.$backup_file_zipped);
	
	// prévoir
	unlink($backup_file);
	unlink($backup_file_zipped);
	
	
}

/*****************************************************************************/   

function unzip_file()
{

	$file_selected =	"blitz_db-backup-09.02.2020_18:25.zip";
	
	$chemin_source = _PATH. "/backup_blitz/" .$file_selected;
	$chemin_destination = _PATH. "/unzip/" .$file_selected;

	echo $chemin_source;
	
	echo "<br/>";
	echo "<br/>";
	
	echo $chemin_destination;
	
	
	//$src = "/home/www/example.com/source/folders/123456";  // source folder or file
	//$dest = "/home/www/example.com/test/123456";   // destination folder or file        

//shell_exec("cp -r $chemin_source $chemin_destination");

echo "<H2>Copy files completed!</H2>"; //output when done
	
	copy($chemin_source, $chemin_destination);
	
	/*if (!copy($chemin_source, $chemin_destination)) 
	{
		echo "La copie $file du fichier a échoué...\n";
	}*/
	

	$path = _PATH ."/unzip/" .$file_selected;	
	
	echo " +++++++ " .$path;
	echo "<br/>";
	echo "<br/>";
	
	chdir("unzip");
	
	echo "<br/>";
	echo "<br/>";
	
	echo getcwd() . "\n";
	
	echo "<br/>";
	echo "<br/>";
	
	
	$zip = new ZipArchive;
	if ($zip->open($file_selected) === TRUE) 
	{
		$zip->extractTo(_PATH ."/unzip/");
		$zip->close();
		echo 'Unzip ok';
	} 
	else 
	{
		echo 'Unzip fail !.';
	}
	
	
	
}

function restaure_mysql_table_dump()
{

	$db_host    = "analogdepat.mysql.db";
   	$db_user   = "analogdepat";
 	$db_pass   = "Un92pac007";
	$db_name   = "analogdepat";

	$file_selected = "blitz_db-backup-09.02.2020_18:25.zip";
	$restore_file = _PATH ."/unzip/" .$file_selected;	
	
	// purger les tables avant la restauration
	$str0 = "TRUNCATE TABLE " .$_SESSION['elo_affectation'];
	$str1 = "TRUNCATE TABLE " .$_SESSION['classement'];
	$str2 = "TRUNCATE TABLE " .$_SESSION['evolution'];
	$str3 = "TRUNCATE TABLE " .$_SESSION['parties'];

	mysql_query($str0);
	mysql_query($str1);
	mysql_query($str2);
	mysql_query($str3);
	
	//$cmd = "mysql -h {$analogdepat.mysql.db} -u {$db_user} -p{$db_pass} {$analogdepat} < $restore_file";
	//exec($cmd);

}

function lire_csv($nom_fichier )
{
    $donnee = array();   
	$joueur = array();
	$table_csv = array();
	
	$file = fopen ($nom_fichier,"r");
		
	while(! feof($file))
	{
			$tmp = fgets($file);
			$tmp = strtolower($tmp);
			//echo $tmp ."<BR>";
			$tmp = str_to_noaccent($tmp);
			$pieces = explode(";", $tmp);
			if($pieces[1] != "")
			{
				$table_csv[] = $pieces;
				$joueur[] = $pieces[1];
				$joueur[] = $pieces[2];
			}
	}
	
	fclose($file);
	
	$nb_ligne = count($table_csv);
	
	for ($i=0; $i < $nb_ligne; $i++)
	{
			$val = $table_csv[$i][4];
			$position = strpos($val, "5");
			if($position !== false)
			{ 
				//echo " Match nul";
				//echo "<br>";
				$table_csv[$i][3] = "0.5";
				$table_csv[$i][4] = "0.5";
			}
	
			$date_partie = $table_csv[$i][0];
			if($date_partie != "")
			{	$date = mise_en_forme_date($date_partie);
				$sauve = $date;
				$table_csv[$i][0] = $date;
			}
			else
			{	$table_csv[$i][0] = $sauve; 
			}
			
	}	
	
	
	$sans_doublon = array_unique($joueur);
	$sans_doublon = array_values($sans_doublon);
	// ordonner par ordre alphabetique
	usort($sans_doublon, "strcasecmp");
	$merge = array($table_csv, $sans_doublon );
	//echo "<pre>";
	//print_r($merge);
	
	return $merge;
	
}
/******************************************/
function str_to_noaccent($str)
{
    $tmp = $str;
    $tmp = preg_replace('#Ç#', 'C', $tmp);
    $tmp = preg_replace('#ç#', 'c', $tmp);
    $tmp = preg_replace('#è|é|ê|ë#', 'e', $tmp);
    $tmp = preg_replace('#È|É|Ê|Ë#', 'E', $tmp);
    $tmp = preg_replace('#à|á|â|ã|ä|å#', 'a', $tmp);
    $tmp = preg_replace('#@|À|Á|Â|Ã|Ä|Å#', 'A', $tmp);
    $tmp = preg_replace('#ì|í|î|ï#', 'i', $tmp);
    $tmp = preg_replace('#Ì|Í|Î|Ï#', 'I', $tmp);
    $tmp = preg_replace('#ð|ò|ó|ô|õ|ö#', 'o', $tmp);
    $tmp = preg_replace('#Ò|Ó|Ô|Õ|Ö#', 'O', $tmp);
    $tmp = preg_replace('#ù|ú|û|ü#', 'u', $tmp);
    $tmp = preg_replace('#Ù|Ú|Û|Ü#', 'U', $tmp);
    $tmp = preg_replace('#ý|ÿ#', 'y', $tmp);
    $tmp = preg_replace('#Ý#', 'Y', $tmp);
     
    return ($tmp);
}
/******************************************/
function mise_en_forme_date($date)
{
		list($jour, $mois, $annee) = explode('/', $date);
		$timestamp = mktime (0, 0, 0, $mois, $jour, $annee);
		return($timestamp);
}

/*****************************************************************************/   

	$db_host    = "analogdepat.mysql.db";
   	$db_user   = "analogdepat";
 	$db_pass   = "Un92pac007";
	$db_name   = "analogdepat";

	$backup_file = 'db-backup-'.time().'.sql';
	$backup_file = 'db-backup-'.date("d.m.Y_H:i").'.sql';

	$tables= "BLITZ_affectation,BLITZ_classement,BLITZ_evolution,BLITZ_parties";

	//$mybackup = backup_tables( $db_host, $db_user, $db_pass , $db_name, $tables); // si on veut toutes les table "*"
	backup_tables( $db_host, $db_user, $db_pass , $db_name, $tables); // si on veut toutes les table "*"


	//unzip_file();
	
	
/*	
	echo "<br/>";
	echo "<br/>";
	echo "<br/>";
	
	$donnees = lire_csv($chemin_backup);
	
	// fragmentation
	$liste_parties = array();
	$liste_joueurs = array();
	$liste_parties = $donnees[0];
	$liste_joueurs = $donnees[1]; 
	
	echo " ------ Extraction des parties ------------------------" ."<br/>";
	echo "<pre>";
	print_r($liste_parties);
	echo "<pre>";
	
	echo "<br/>";
	
	echo "Parties détectées : " .count($liste_parties) ;
	
	echo "<br/>";
	echo "<br/>";
	
	echo " ------ Purger les tables ------------------------" ."<br/>";
	echo "<br/>";
	
	$str1 = "TRUNCATE TABLE " .$_SESSION['classement'];
	$str2 = "TRUNCATE TABLE " .$_SESSION['evolution'];
	$str3 = "TRUNCATE TABLE " .$_SESSION['parties'];
				
	mysql_query($str1);
	mysql_query($str2);
	mysql_query($str3);

	$joueur_chess = array();

	$sql = "SELECT * FROM BLITZ_affectation ORDER BY joueur ASC";
	//echo $sql;
	
	$results = mysql_query($sql);
	while($data = mysql_fetch_assoc($results))
    {
		$joueur_chess[] = $data['joueur'];
    }

	echo " ------ Extraction des joueurs ------------------------" ."<br/>";
	
	echo '<pre>';
	var_dump($joueur_chess);
	echo '<pre/>';
	
	
	echo " ------ Initialisation du classement ------------------------" ."<br/>";
	echo "<br/>";		
		
	for ($i = 0; $i < count($joueur_chess); $i++)
	{
				$sql = "INSERT INTO BLITZ_classement (joueur, elo, initial, parties, prior, bonus, oldrating, gcount, wins, losses, draws, active) VALUES
				( '$joueur_chess[$i]', 1100, 1100, 0, 0, 0, 1100, 0, 0, 0, 0, 1)";
				
				echo $sql ."\n";
				
				mysql_query($sql);
	}
	
	echo " ------ ELO generation  ------------------------" ."<br>";
	echo "<br/>";

	$nb_parties = count($liste_parties);
	
	for ($item=0; $item < $nb_parties ; $item++)
	{
			$donnees[0] = $liste_parties[$item][0];
			$donnees[1] = $liste_parties[$item][1];
			$donnees[2] = $liste_parties[$item][2];
			$donnees[3] = $liste_parties[$item][3];
			$donnees[4] = $liste_parties[$item][4];
			
			$indice = $item + 1;
			algo_elo($donnees, "statistique", "sql", $indice );
			
			
	}

	echo "<br/>";
	echo "------ Toutes les tables de la database ont été mises à jour ------" ."<br/>";
	echo "<br/>";
	echo "Script executé avec succés";	
	echo "<br/>";


	echo "Done";
	
	
	
	// save to file
	//$handle = fopen($backup_file,'w+');
	//fwrite($handle,$mybackup);
	//fclose($handle);

	
	// https://www.ionos.fr/assistance/hebergement/utiliser-la-base-de-donnees-mysql-pour-un-projet-web/sauvegarder-et-restaurer-une-base-de-donnees-mysql-a-laide-de-php/
*/
	?>