<?php
	include('database.php');
	DB_connexion(); 
	
	header( 'content-type: text/html; charset=utf-8' );

			
	define('_PATH', dirname(__FILE__));
			
	
/*********************************************************************************************************/
	
	function generation_classement_table()		
	{		
			$joueur = array();
			
			$sql = "SELECT * FROM BLITZ_affectation ORDER by joueur asc"; 
			$results = mysql_query($sql);
			while($row = mysql_fetch_assoc($results))
			{
					$joueur[] = $row['joueur'];
			}
			
			
			echo " ---> Listes des joueurs:";
			echo '<br/>'; 
			
			echo "<pre>";
			print_r($joueur);
			echo "</pre>";

					
			$sql = "CREATE TABLE BLITZ_copy(
					id int(11) NOT NULL AUTO_INCREMENT,
					joueur varchar(30) NOT NULL DEFAULT '',
					elo int(11) NOT NULL DEFAULT '0',
					initial int(11) NOT NULL DEFAULT '0',
					parties int(11) NOT NULL DEFAULT '0',
					prior int(11) NOT NULL DEFAULT '0',
					bonus int(11) NOT NULL DEFAULT '0',
					oldrating int(11) NOT NULL DEFAULT '0',
					gcount int(11) NOT NULL DEFAULT '0',
					wins int(11) NOT NULL DEFAULT '0',
					losses int(11) NOT NULL DEFAULT '0',
					draws int(11) NOT NULL DEFAULT '0',
					active int(11) NOT NULL DEFAULT '1',
					PRIMARY KEY (id)
			)";					
					
					
			mysql_query($sql);
			
			echo " ---> Initialisation de la table classement:";
			echo '<br/>'; 
			
			for ($i = 0; $i < count( $joueur ); $i++) 
			{
				echo "The number is: $i <br>";
				
				$sql ="INSERT INTO BLITZ_copy (id, joueur, elo, initial, parties, prior, bonus, oldrating, gcount, wins, losses, draws, active) VALUES
					  ($i, '$joueur[$i]', 1100, 1100, 0, 0, 0, 1100, 0, 0, 0, 0, 1)";
				echo $sql;
				
				echo '<br/>'; 
				
				$results = mysql_query($sql);

				
			}
			
			
			$rename= mysql_query("ALTER TABLE BLITZ_copy RENAME TO BLITZ_NEW");

			if($rename !== FALSE)
			{
				echo("The table has been renamed.");
			}
			else
			{
				echo("The table has not been renamed.");
			}


	}

/*********************************************************************************************************/
	
	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);
					}

					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";
						  
						}
					}
  
			mysql_close( $link );
			
			// save to file
			
			$backup_file = 'xxxx_db-backup-'.date("d.m.Y_H:i").'.sql';
			
			$handle = fopen($backup_file,'w+');
			fwrite($handle,$data);
			fclose($handle);
			
			
			zip_file($backup_file);
			
			unzip_file();
			//return $data;
			return $backup_file;
 
  
}
	
/*********************************************************************************************************/

	function zip_file($backup_file)
	{
		$zip = new ZipArchive;
		if ($zip->open('xxxx_yyyy.zip', ZipArchive::CREATE | ZipArchive::OVERWRITE ) === TRUE) // | ZipArchive::OVERWRITE
		{
			$zip->addFile($backup_file);
			$zip->close();
		}
	}

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

	function unzip_file()
	{
	
		$zip = new ZipArchive;
		if ($zip->open('xxxx_yyyy.zip') === TRUE) 
		{
			$path = _PATH."/unzip/";
			$zip->extractTo($path);
			$zip->close();
			echo 'Unzip file OK.';
		} 
		else 
		{
			echo 'Unzip fail !.';
		}
}

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

	function restore_sql_file($backup_file)
	{

		vider_table($tables);
	
		$db_host   = "analogdepat.mysql.db";
		$db_user   = "analogdepat";
		$db_pass   = "Un92pac007";
		$db_name   = "analogdepat";

		$cmd = "mysql -h {$db_host} -u {$db_user} -p{$db_pass} {$db_name} < $backup_file";
		//exec($cmd);
	
		exec($cmd,$output=array(),$worked);
		switch($worked)
		{
			case 0:
				echo 'Les données du fichier <b>' .$backup_file .'</b>ont été importées avec succès dans la base de données. <b>' .$db_name .'</b>';
				break;
			case 1:
				echo 'Une erreur s\'est produite lors de l\'importation ?.';
				break;
		}

	}

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

	function vider_table($table)
	{

		$db_host    = "analogdepat.mysql.db";
   	    $db_user   = "analogdepat";
 	    $db_pass   = "Un92pac007";
	    $db_name   = "analogdepat";
        
		$tables= "BLITZ_affectation,BLITZ_classement,BLITZ_evolution,BLITZ_parties";
		$tables= "BLITZ_classement,BLITZ_evolution,BLITZ_parties";
		
		
		$connexion = mysql_connect($db_host,$db_user,$db_pass); 
		if(!$connexion)
		{
			die('Could not connect: ' . mysql_error());
		}
 
		mysql_select_db($db_name, $connexion); 
		
		$tables_array = explode(',',$tables); 
  
		foreach($tables_array as $table)
		{
			//$sql = "DROP TABLE " .$table;
			$sql = "TRUNCATE TABLE " .$table;
			echo $sql;
			mysql_query( $sql );
			echo '<br/>'; 
		}
		
		mysql_close($connexion);
		
       
	}

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


	generation_classement_table();		


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


/*

	$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 "*"

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

	//echo "---> " .$file;
	// save to file
	//$handle = fopen($backup_file,'w+');
	//fwrite($handle,$mybackup);
	//fclose($handle);
	
	
	//zip_file();
	//unzip_file();
	
	$backup_file = "xxxx_db-backup-03.02.2020_22:06.sql";	
	restore_sql_file($backup_file);

*/


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


 //echo 'Connected successfully<br />';
        //$sql = "DROP TABLE " .$table;
        //echo $sql;
		//echo '<br/>'; 
        //$retval = mysql_query( $sql );
         
        /*if(! $retval ) 
		{
            die('Could not delete table: ' . mysql_error());
        }
        
		echo "Table deleted successfully\n";*/
        //mysql_close($connexion);


//vider_table($tables);
/*
function table_exists($this_table)
{
	$checktable = mysql_query("SHOW TABLES LIKE '$this_table'");
	$table_exists = mysql_num_rows($checktable) ;
	
	if( table_exists > 0)
	{
		echo "Table exists in DB";
	}
	else
	{	
		echo "Table not found !!!."
	}
}
*/
// https://www.virendrachandak.com/techtalk/how-to-create-a-zip-file-using-php/

//https://stackoverflow.com/questions/13659534/backup-mysql-tables-with-php
	
	//https://phppot.com/php/restore-mysql-database-using-php/
	
	
	//$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';
	
// get backup
//$mybackup = backup_tables("myhost","mydbuser","mydbpasswd","mydatabase","*");

//$mybackup = backup_tables( $db_host, $db_user, $db_pass , $db_name,"*");

//	$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 "*"

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

	//echo "---> " .$file;
	// save to file
	//$handle = fopen($backup_file,'w+');
	//fwrite($handle,$mybackup);
	//fclose($handle);
	
	
	//zip_file();
	//unzip_file();
	
//	$backup_file = "xxxx_db-backup-03.02.2020_22:06.sql";	
//	restore_sql_file($backup_file);

	


		
			//$sql = "ALTER TABLE BLITZ_copy RENAME TO BLITZ_xxx";
			//$results = mysql_query($sql);
			
			/*while($row = mysql_fetch_assoc($results))
			{
					$joueur[] = $row['joueur'];
					
					$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')";
					
			}*/
			
			

			//$str1 = "TRUNCATE TABLE " .$_SESSION['classement'];
			//mysql_query($str1);
			
			//$str2 = "TRUNCATE TABLE " .$_SESSION['evolution'];
			//$str3 = "TRUNCATE TABLE " .$_SESSION['parties'];
			//$str4 = "TRUNCATE TABLE " .$_SESSION['chessmaster'];
				
			//mysql_query($str1);
			//mysql_query($str2);
			//mysql_query($str3);
			//mysql_query($str4);
			
			
			//$db_host    = "analogdepat.mysql.db";
   	        //$db_user   = "analogdepat";
 	        //$db_pass   = "Un92pac007";
	        //$db_name   = "analogdepat";
			
			//$database = 'db';
			//$user = 'user';
			//$pass = 'pass';
			//$host = 'localhost';
			//$dir = dirname(__FILE__) . '/dump.sql';

			//echo "<h3>Backing up database to `<code>{$dir}</code>`</h3>";

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

			//var_dump($output);
			
			
	/*		
			<?php
$username = "abc";
$password = "xyz";
$hostname = "domain.com";
$database = "test_db";
$username =escapeshellcmd($username);
$password =escapeshellcmd($password);
$hostname =escapeshellcmd($hostname);
$database =escapeshellcmd($database);
$backupFile='url'.date("Y-m-d-H-i-s").$database.'.sql.tgz';

 $command = "mysqldump -u$username -p$password -h$hostname $database wp_posts > $backupFile";
system($command, $result);
echo $result;
?>
	*/
	
	// https://phpsources.net/code/php/mysql/612_dump-sauvegarde-avec-php-d-une-base-de-donnee-mysql
	
	// https://tellibus.com/blog/3-Tutoriel-PHP--Programmer-des-backups-automatiques-d-une-base-de-donnees-MySQL-et-les-envoyer-via-FTP
	
	// https://stackoverflow.com/questions/13659534/backup-mysql-tables-with-php
	
	// https://stackoverflow.com/questions/13659534/backup-mysql-tables-with-php

// https://dev.to/erhankilic/how-to-import-sql-file-with-php--1jbc
/*function restore_mysql_query()
{
	// Name of the file
	$filename = 'sql.sql';
	// MySQL host
	$mysql_host = 'localhost';
	// MySQL username
	$mysql_username = 'username';
	// MySQL password
	$mysql_password = 'password';
	// Database name
	$mysql_database = 'database';

// Connect to MySQL server
mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error());
// Select database
mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_error());

// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// Loop through each line
foreach ($lines as $line) {
// Skip it if it's a comment
    if (substr($line, 0, 2) == '--' || $line == '')
        continue;

// Add this line to the current segment
    $templine .= $line;
// If it has a semicolon at the end, it's the end of the query
    if (substr(trim($line), -1, 1) == ';') {
        // Perform the query
        mysql_query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
        // Reset temp variable to empty
        $templine = '';
    }
}
echo "Tables imported successfully";
}

*/


?>