<?PHP
include "../inc/var.php";
include "../inc/connect.php";
include $server_inc."/whm_functions.php";

$message="";
if (!is_writable($server_backup)) { $stop=true; $message.="Please chmod ".$server_backup." to 0777 to continue."; }
if (file_exists($below_public."/timestamp_complete.log")) { $stop=true; $message.="Please contact support. You have already ran this update. Running it again will destroy your database."; }
unset($stop);
if (isset($stop))
	{
	echo $message;
	}
else
	{
	# backup & email out good copy of the database
	dbbackup($email_admin, $server_backup, $dbhost, $dbuser, $dbpass, $db, $http_admin, $http_images, $which);


	# START: Alter Table Functions
	# ---------------------------------------------------------------
	# alter table to fit the new format
	function exec_alterTable($table, $base_column, $new_column, $details)
		{
		$query="ALTER TABLE ";
		$query.="`";
		$query.=$table;
		$query.="` ";
		$query.="CHANGE ";
		$query.="`";
		$query.=$base_column;
		$query.="` ";
		$query.="`";
		$query.=$new_column;
		$query.="` ";
		$query.=$details;
		
		mysql_query($query);
		}
	# ---------------------------------------------------------------
	# END: Alter Table Functions


	# START: Conversion Functions
	# ---------------------------------------------------------------
	# converts a MySQL timestamp to unix
	function convert2unix($mysql_timestamp)
		{
		$m=substr($mysql_timestamp, 4, 2);
		$d=substr($mysql_timestamp, 6, 2);
		$y=substr($mysql_timestamp, 0, 4);

		return mktime(0, 0, 0, $m, $d, $y);
		}
	# ---------------------------------------------------------------
	# END: Conversion Functions
		

	# START: Collect Out-Dated Data
	# ---------------------------------------------------------------
	# grab mysql timestamp from the database
	function get_mysql_time($base_column, $table, $autoIncrementName, $autoIncrementID)
		{
		$query="select ";
		$query.=$base_column." ";	// 0
		$query.="from ";
		$query.=$table." ";
		$query.="where ".$autoIncrementName."='".addslashes(trim($autoIncrementID))."' ";
		$query.="limit 0, 1";

		$rs=mysql_fetch_row(mysql_query($query));

		return $rs[0];
		}
	# ---------------------------------------------------------------
	# END: Collect Out-Dated Data


	# tables needing conversions
	# --------------------------
	# - affiliate_details - ogcreate
	# - affiliate_history - period_of
	# - affiliate_user - ogcreate
	# - server_config - ogcreate
	# - ticket - ogcreate
	# - ticket_reply - ogcreate
	# - user - ogcreate
	# hosting_order - ogcreate, resolved_time, ur_date, cancel_date
	# default table type is int 40 to allow room for growth

	# convert all except hosting order
	for ($i=0; $i<=6; $i++)
		{
		if ($i==0) 
			{ 
			$base_column="ogcreate"; 
			$autoIncrementName="afdid";
			$table="affiliate_details"; 
			}
		else if ($i==1) 
			{ 
			$base_column="period_of";  
			$autoIncrementName="afhid";
			$table="affiliate_history";
			}
		else if ($i==2) 
			{ 
			$base_column="ogcreate"; 
			$autoIncrementName="afuid";
			$table="affiliate_user"; 
			}
		else if ($i==3)
			{ 
			$base_column="ogcreate"; 
			$autoIncrementName="whm_id";
			$table="server_config"; 
			}
		else if ($i==4)
			{ 
			$base_column="ogcreate"; 
			$autoIncrementName="id";
			$table="ticket"; 
			}
		else if ($i==5)
			{ 
			$base_column="ogcreate";
			$autoIncrementName="rid"; 
			$table="ticket_reply"; 
			}
		else if ($i==6)
			{ 
			$base_column="ogcreate"; 
			$autoIncrementName="uid";
			$table="user"; 
			}
		
		$k=0;
		$query="select "; 
		$query.=$base_column.", ";		// 0
		$query.=$autoIncrementName." "; // 1
		$query.="from ";
		$query.=$table; 
		$row=mysql_query($query);
		while ($rs=mysql_fetch_row($row))
			{
			$mysql_timestamp=convert2unix($rs[0]);
			$autoIncrementID=$rs[1];
			$new_column=$base_column;
			$details="INT( 40 )";

			exec_alterTable($table, $base_column, $new_column, $details);

			$update="update ";
			$update.=$table." ";
			$update.="set ";
			$update.=$base_column."='".$mysql_timestamp."' ";
			$update.="where ";
			$update.=$autoIncrementName."='".$autoIncrementID."'";
			mysql_query($update);
			$k++;
			}

		if ($k==0) 
			{ 
			$new_column=$base_column; 
			$details="INT( 40 )";
			exec_alterTable($table, $base_column, $new_column, $details); 
			}

		unset($k);

		echo "updated table ".$table."....<br>";
		}

	# convert hosting order
	$base_column="ogcreate";
	$base_column0="resolved_time";
	$base_column1="ur_date";
	$base_column2="cancel_date";
	$autoIncrementName="oid"; 
	$table="hosting_order";
	$details="INT( 40 )";
	$buffer="";

	$query="select "; 
	$query.=$base_column.", ";			// 0
	$query.=$autoIncrementName.", ";	// 1
	$query.=$base_column0.", ";			// 2
	$query.=$base_column1.", ";			// 3
	$query.=$base_column2." ";			// 4
	$query.="from ";
	$query.=$table; 
	$row=mysql_query($query);
	while ($rs=mysql_fetch_row($row))
		{
		$buffer.=convert2unix($rs[0])."|"; # ogcreate
		$buffer.=convert2unix($rs[2])."|"; # resolved_time
		$buffer.=convert2unix($rs[3])."|"; # ur_date
		$buffer.=convert2unix($rs[4])."|"; # cancel_date
		$buffer.=$rs[1]."|\n";# auto increment ID
		}

	exec_alterTable($table, $base_column, $base_column, $details);
	exec_alterTable($table, $base_column0, $base_column0, $details);
	exec_alterTable($table, $base_column1, $base_column1, $details);
	exec_alterTable($table, $base_column2, $base_column2, $details);

	$buffer_ct=(substr_count($buffer, "\n")-1);
	$explode=explode("\n", $buffer);
	for ($j=0; $j<=$buffer_ct; $j++)
		{
		list($ogcreate, $resolved_time, $ur_date, $cancel_date, $inc_id)=explode("|", $explode[$j]);
		
		$hquery="update ";
		$hquery.="hosting_order ";
		$hquery.="set ";
		$hquery.="ogcreate='".$ogcreate."', ";
		$hquery.="resolved_time='".$resolved_time."', ";
		$hquery.="ur_date='".$ur_date."', ";
		$hquery.="cancel_date='".$cancel_date."' ";
		$hquery.="where ";
		$hquery.="oid='".$inc_id."'";
		
		mysql_query($hquery);

		unset($ogcreate);
		unset($resolved_time);
		unset($ur_date);
		unset($cancel_date);
		unset($inc_id);
		}

	echo "updated table ".$table."....<br>";
	mysql_query("ALTER TABLE `session_history` CHANGE `ogcreate` `ogcreate` INT( 40 ) DEFAULT NULL");
	echo "updated table session_history....<br>";

	echo "done!";

	$read=fopen($below_public."/timestamp_complete.log", "w");
	$result=fwrite($read, date("m/d/Y h:i:s a"));
	fclose($read);

	chmod($below_public."/timestamp_complete.log", 0777);
	}
?>