<?
require_once("../classes/dbconnection.php");

$db_old = "wavewatcher";
$db_new = "wavewatcher3";
$filename = "ww2_to_ww3.sql";

$dbcn = new DBConnection();
$cn = $dbcn->Connect();

$table_names = "";
$ping_count = 0;
$interface_count = 0;
$signal_count = 0;

// node_types
$sql_queries = "INSERT INTO $db_new.node_types (node_type_id, node_type_name) \nSELECT node_type_id, node_type_name \nFROM $db_old.node_types;\n\n";

// networks
$sql_queries .= "INSERT INTO $db_new.networks (network_id, network_name) \nSELECT network_id, network_name \nFROM $db_old.networks;\n\n";

// nodes
$sql_queries .= "INSERT INTO $db_new.nodes (node_id, node_type_id, network_id, parent_node_id, node_name, node_pass, node_ip, location, monitor, log, signal, alarm) \nSELECT node_id, node_type_id, network_id, parent_node_id, node_name, node_pass, node_ip, location, monitor, log, signal, alarm \nFROM $db_old.nodes;\n\n";

// users
$sql_queries .= "INSERT INTO $db_new.users (user_id, name, password, company, email, location, phone, type, lastlogin) \nSELECT user_id, name, password, company, email, location, phone, type, lastlogin \nFROM $db_old.users;\n\n";

// nodes_users
$sql_queries .= "INSERT INTO $db_new.nodes_users (user_id, node_id) \nSELECT user_id, node_id \nFROM $db_old.nodes_users;\n\n";

// current_ping_times
$sql_queries .= "INSERT INTO $db_new.current_ping_times (node_id, status, ping_info, time, utime) \nSELECT node_id, status, ping_time, time, utime \nFROM $db_old.current_ping_times;\n\n";

// status_changes
$sql_queries .= "INSERT INTO $db_new.status_changes (node_id, service, status_change, info, time, utime) \nSELECT node_id, service, status_change, info, time, utime \nFROM $db_old.status_changes;\n\n";

// all log tables
$result = $dbcn->Query("USE $db_old");
$tables_result = $dbcn->Query("SHOW TABLES");

while (List($table) = $dbcn->FetchRow($tables_result))
{
	if (ereg("^lognode_[0-9]{1,}_ping$", $table)) // ping log
	{
		list ($lognode, $node_id, $ping) = split ('_', $table);

		$sql_queries .= "INSERT INTO $db_new.ping_times_log (node_id, utime, ping_time) \nSELECT '$node_id', time, ping_time \nFROM $db_old.$table;\n\n";

		$table_names .= $table." ping<br>";
		$ping_count++;
	}
	else if (ereg("^lognode_[0-9]{1,}_int_[0-9]{1,}$", $table)) // node interface log
	{
		list ($lognode, $node_id, $int, $interface) = split ('_', $table);

		$sql_queries .= "INSERT INTO $db_new.node_interface_log (node_id, interface, utime, txbytes, rxbytes) \nSELECT '$node_id', '$interface', time, outbytes, inbytes \nFROM $db_old.$table;\n\n";

		$table_names .= $table." interface<br>";
		$interface_count++;
	}
	else if (ereg("^lognode_[0-9]{1,}_([a-fA-F0-9]{2}_){6}.{1,}$", $table)) // signal log
	{
		list ($lognode, $node_id, $mac1, $mac2, $mac3, $mac4, $mac5, $mac6) = split('_', $table);
		$mac = $mac1."-".$mac2."-".$mac3."-".$mac4."-".$mac5."-".$mac6;
		$mac = strtr($mac, "abcdef", "ABCDEF");	
		$id_length = strlen($node_id);
		$if_name = substr($table, 9 + $id_length + 18);

		$sql_queries .= "INSERT INTO $db_new.connections (node_id, mac, if_name) \nVALUES ('$node_id', '$mac', '$if_name');\n\n";

		$sql_queries .= "INSERT INTO $db_new.signal_levels_log (connection_id, utime, signal, noise) \nSELECT c.connection_id, l.time, l.sig, l.noi \nFROM $db_old.$table AS l, $db_new.connections AS c\nWHERE c.node_id = '$node_id' AND c.mac = '$mac' AND c.if_name = '$if_name';\n\n";

		$table_names .= $table." signal<br>";
		$signal_count++;
	}
}

// current_signal_levels
$sql_queries .= "INSERT INTO $db_new.current_signal_levels (connection_id, locsig, locnoi, remsig, remnoi, time, utime, status) \nSELECT c.connection_id, csl.locsig, csl.locnoi, csl.remsig, csl.remnoi, csl.time, csl.utime, csl.status \nFROM $db_old.current_signal_levels AS csl, $db_new.connections AS c \nWHERE csl.node_id = c.node_id AND RIGHT(csl.int_name, LENGTH(csl.int_name)-18) = c.if_name AND REPLACE(csl.mac, '_', '-') = c.mac;\n\n";

// signal_alarm_levels 
$result = $dbcn->Query("SELECT node_id, REPLACE(LEFT(int_name, 17), '_', '-'), RIGHT(int_name, LENGTH(int_name)-18), sig_alarm_level, noise_alarm_level, monitor \nFROM $db_old.signal_alarm_levels");

while (List($node_id, $mac, $if_name, $sig_alarm_level, $noise_alarm_level, $monitor) = $dbcn->FetchRow($result))
{
	$mac = strtr($mac, "abcdef", "ABCDEF"); 

	if ($monitor == 'Y')
	{
		$sql_queries .= "UPDATE $db_new.connections \nSET sig_alarm_level = '$sig_alarm_level', noise_alarm_level = '$noise_alarm_level', sig_alarm_active = 'Y', noise_alarm_active = 'Y' \nWHERE node_id = '$node_id' AND mac = '$mac' AND if_name = '$if_name';\n\n";
	}
	else
	{
		$sql_queries .= "UPDATE $db_new.connections \nSET sig_alarm_level = '$sig_alarm_level', noise_alarm_level = '$noise_alarm_level', sig_alarm_active = 'N', noise_alarm_active = 'N' \nWHERE node_id = '$node_id' AND mac = '$mac' AND if_name = '$if_name';\n\n";
	}
}

$dbcn->Disconnect($cn);

// write queries to file
$myfile = fopen($filename,"w");
if ($myfile)
{
	$fp = fwrite($myfile, $sql_queries);
	fclose($myfile);
}

?>

<html>
	<head>
	</head>
	<body>
		<?=$ping_count?> ping<br>
		<?=$interface_count?> interface<br>
		<?=$signal_count?> signal<br>
		<br>
		<?=$table_names?><br>
	</body>
</html>