<?php
/*
 * Display names/codes from Source table and upon selecting one
 * display associated attributes from Dynamic table.
 * Once an attribute from the dynamic table has been selected
 * then display a graph of timestamp and value from the associated DynamicValue
 * table.
 * Allow to be graphed no more than max entries as given in prefs
 *
 */


require '../Common/openDB.php';
require '../Common/preferences.php';
$con = mysql_connect($website,$userid,$pw);
if (!$con) {
	die('Could not connect: ' . mysql_error());
}
mysql_query("SET foreign_key_checks = 0",$con);

$curDB = "../CurrentlyOpenedDBName.tmp";
if(file_exists ($curDB)){
	$f = fopen($curDB, "r");
	$dbase   = trim(fgets($f)); // linked database name
	fclose($f);
	mysql_select_db($dbase, $con);
	//echo "Connected to " . $dbase . "<br/>";
}else{
	//No database currently open
	return;
}

$source_id = $_GET[source_id];
$dynamic_id = $_GET[dynamic_id];
$res = mysql_query("SELECT COUNT(fetcher_id),attribute,value FROM Dynamic WHERE dynamic_id = {$dynamic_id}",$con);
while($row = mysql_fetch_array($res)){
		$count1 = $row[0];
		$tagvalue = $row[1] . ' ' . $row[2];
}

$sec = "60"; // Default value is 1 minute
$data_fetching_time_period = 60;
if($count1 > 0){
	$res = mysql_query("SELECT fetcher_id FROM Dynamic WHERE dynamic_id = {$dynamic_id}",$con);
	while($row = mysql_fetch_array($res))$fetcher_id = $row[0];

	$res = mysql_query("SELECT COUNT(frequency_id) FROM Fetcher WHERE fetcher_id = {$fetcher_id}",$con);
	while($row = mysql_fetch_array($res))$count2 = $row[0];

	if($count2 > 0){
		$res = mysql_query("SELECT frequency_id FROM Fetcher WHERE fetcher_id = {$fetcher_id}",$con);
		while($row = mysql_fetch_array($res))$frequency_id = $row[0];

		$res = mysql_query("SELECT period,times_per_period FROM Frequency WHERE frequency_id = {$frequency_id}",$con);
		while($row = mysql_fetch_array($res)){
			$period = $row[0];
			$times_per_period = $row[1];
		}
		date_default_timezone_set($TimeZone);
		$current_mnth = date('F');
		$current_year = date('Y');
		$data_fetching_time_period = GetTimePeriodFromFrequency($period,$times_per_period,$current_mnth,$current_year);

		// Currently just refresh every minute
		// $sec = GetTimePeriod($period,$times_per_period);
		// if(!is_numeric($sec))$sec = "60";
	}
}

$page = $_SERVER['PHP_SELF'];




echo ("
<html>
<head>
    <script type='text/javascript' src='http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js'></script>
    <meta http-equiv='refresh' content='{$sec}' URL='{$page}'>
</head>
<body>
");


if($source_id == NULL || $dynamic_id == NULL)header('Location: ../Common/BlankPage.html');

// DRAW GRAPH
// If user has selected dynamic attribute to view then draw graph
	$result = mysql_query("SELECT name FROM Source WHERE source_id=".$source_id.";",$con);
	while($row = mysql_fetch_array($result)){
		$source = $row[0];
	}

	$result = mysql_query("SELECT attribute FROM Dynamic WHERE dynamic_id=".$dynamic_id.";",$con);
	while($row = mysql_fetch_array($result)){
		$DynAttrib = $row[0];
	}

	// Get maximum and minimum value of data being plotted to set y-axis
	$result = mysql_query("SELECT MIN(timestamp) FROM DynamicValue WHERE dynamic_id=".$dynamic_id.";",$con);
	while($row = mysql_fetch_array($result))$absMinx = $row[0];

	date_default_timezone_set($TimeZone);
	$result = mysql_query("SELECT MAX(value),MAX(timestamp),MIN(value),MIN(timestamp) FROM (SELECT value,timestamp FROM DynamicValue WHERE dynamic_id=".$dynamic_id." ORDER BY timestamp DESC LIMIT {$entries_to_grph}) AS temptable;",$con);
	while($row = mysql_fetch_array($result)){
		$yMax = $row[0];
		$xMax = $row[1];
		$xMax = date('Y-m-d H:i:s');
		$yMin = $row[2];
		$xMin = $row[3];
	}

	// JBI Overload xMin to plot data from when user pressed start data fetch button
	$result = mysql_query("SELECT count(timestamp) FROM DynamicValue WHERE dynamic_id = {$dynamic_id}",$con);
	while($row = mysql_fetch_array($result))$items_present = $row[0];

	$result = mysql_query("SELECT status FROM Dynamic WHERE dynamic_id = {$dynamic_id}",$con);
	while($row = mysql_fetch_array($result))$status = $row[0];
	if($status != "stopped" && $items_present > 0){
		// if fetch frequency time period is less than 10 minutes then set the x axis range to at least 20 minutes
		$axis_range_modifier = $data_fetching_time_period;
		if($data_fetching_time_period < 600)$axis_range_modifier = 1200;

		$mod_now = strtotime($status) - $global_data_fetch_timestamp_delay - $axis_range_modifier;
		$xMin = date('Y-m-d H:i:s',$mod_now);
		$xMin_mysql = $xMin;
		$xMax = strtotime($xMax) + $axis_range_modifier;
		$xMax = date('Y-m-d H:i:s',$xMax);
	}

	// Convert MYSQL timestamp to seconds from epoch
	$absMinx = strtotime($absMinx);
	$xMinSec = strtotime($xMin);
	$xMaxSec = strtotime($xMax);
	$plottedTime = strtotime($xMax) - strtotime($xMin);

	// Get limits to plot in seconds from epoch
	$whitewidth = 1000; // this is the width of the white left and right edges of the plot area
	$xMinToPlot = $xMinSec - $plottedTime / $whitewidth; // 
	$xMaxToPlot = $xMaxSec + $plottedTime / $whitewidth;

	// Get times for past, plotted and future section
	$absMinx  = date('r',$absMinx);
	$xNow 	  = date('Y-m-d H:i:s');
	$xMax 	  = date('Y-m-d H:i:s',$xMaxToPlot);
	$xMax 	  = date('Y-m-d H:i:s',$xMaxToPlot);
	$xMin 	  = date('Y-m-d H:i:s',$xMinToPlot);
	$xMaxData = date('Y-m-d H:i:s',$xMaxSec);
	$xMinData = date('Y-m-d H:i:s',$xMinSec);

	// Convert to UTC format
	$xNow 		= GetUTCDate($xNow);
	$xMax 		= GetUTCDate($xMax);
	$xMin 		= GetUTCDate($xMin);
	$xMaxData 	= GetUTCDate($xMaxData);
	$xMinData 	= GetUTCDate($xMinData);

	// Write first part of the graph
	echo ("
	<script src='../JS/highcharts.js'></script>

    <script type='text/javascript'>
        <!-- Begin High charts code-->
        $(function() {
            $('#container').highcharts({
                chart: {
                    type: 'scatter', // original type is spline (that is line chart)
                    //type: 'spline', // original type is spline (that is line chart)
                    zoomType: 'x',
                },

				plotOptions: {
					scatter: {
						marker: {
							radius: 2,
						},
					},
				},

				credits: {
					enabled: false
				},
				title: {text: ''},
                xAxis: [{
                    type: 'datetime',
                    title: {text: 'Time'},
                    plotLines: [
						{   value: Date.UTC({$xMin}),
							width: 1,
							color: '#808080'
						},
						{   value: Date.UTC({$xMax}),
							width: 1,
							color: '#808080'
						},
						{   value: Date.UTC({$xNow}),
							width: 2,
							color: '#808080'
						},
						{   value: Date.UTC({$xMinData}),
							width: 0,
							color: '#808080'
						},
						{   value: Date.UTC({$xMaxData}),
							width: 0,
							color: '#808080'
						},
					],
					min:Date.UTC({$xMin}),
					max:Date.UTC({$xMax}),

					plotBands:[	{color: 'rgba(167,169,172,0.20)',from: Date.UTC({$xMinData}),to: Date.UTC({$xMaxData}),label: {text: '',align: 'center',},},]
                }],
				tooltip: {
					formatter: function() {
						return  '<b>' + this.series.name +'</b><br/>' + Highcharts.dateFormat('%e %b%y %H:%M:%S', new Date(this.x)) + ', ' + this.y;
					}
				},
                yAxis: [{ // Primary yAxis
                    title: {
                        text: '".$tagvalue."' ////'Temperature (°C)' //degrees in superscript (°C)
                    },
                    min: ".$yMin.",
					max: ".$yMax.",
                    plotLines: [{
                        value: 0,
                        width: 1,
                        color: '#808080'
                    }]
                },],
                legend: {
					enabled: false,
                    verticalAlign: 'top',
                    //y: 30,
                },
                series: [{
			name: '".$DynAttrib."',
		data: [
");

// Write data part of the graph
// List in ascending order do that Highcharts can zoom in

	$result = mysql_query("SELECT * FROM (SELECT timestamp,value FROM DynamicValue WHERE dynamic_id=".$dynamic_id." AND timestamp > '{$xMin_mysql}' ORDER BY timestamp DESC LIMIT {$entries_to_grph}) tmp ORDER BY timestamp ASC;",$con); // Order by timestamp in case table does not hold timestamps in order
	while($row = mysql_fetch_array($result)){
		$yValue = $row[1];
		$timeValue = GetUTCDate($row[0]);					//											[Date.UTC(YYYY , MM ,DD,HH,MM,SS),value],
		echo("[Date.UTC(".$timeValue."),".$yValue."],"); 	//(YYYY,MM,DD,hh,mm,ss),value formally as 	[Date.UTC(2015 , 03 ,18,15,44,30),16.15],
	}

echo("
],
color: '#0072BD',
tooltip: {
	valueSuffix: ' (y value)'
}
},
]
            });
        });
   </script>

   <div id='container' style='min-width: 310px; height: 400px; margin: 0 auto'></div>

");
if($status == "stopped" && $items_present > 0)echo ("Data available from {$absMinx}. ");

// Get next run time
$command = "schtasks.exe /QUERY /FO list /tn {$dynamic_id}{$dbase}"; 	// Get next run time from DOS
$output = shell_exec($command);
if($output == NULL || $output == ""){
    echo " ";
}else{
	$output = get_string_between($output, "Time:","Status");		// Get run time from DOS result
	//$outputX = strtotime($output);						// Get seconds from UNIX Epoch
	//$outputX = date('r',$output);						// Write to 'r' PHP format (RFC2822)
	echo ("Next data fetch at {$output}<br/>");
	//echo "outputX = {$outputX} <br/>";
}
echo ("
</body>
</html>
");

// Return UTC date format (as used in Highcharts (YYYY,MM,DD,HH,MM,SS)) from MySQL datetime format
function GetUTCDate($str){

	$tmp1 = str_replace("-",",",$str);
	$tmp2 = str_replace(" ",",",$tmp1);
	$timeValue = str_replace(":",",",$tmp2);

	// Cater to stupidity in highcharts whereby month0 = January
	$tmpArray = explode(',', $timeValue);
	$tmpArray[1] = $tmpArray[1] - 1;
	$timeValue = implode(',',$tmpArray);				//	[Date.UTC(YYYY , MM ,DD,HH,MM,SS),value],

	//echo ("SQLTIme = {$str} changed to {$timeValue}");
	return $timeValue;
}

?>
