<?php
//-----------------------------------------------------------------
// Query a Cumulus realtime MySQL table and return the requested fields
// along with the time stamps in a JSON format suitable for use with
// the Highcharts graphing package.
// Author: Mark Crossley
//
// Version 0.2 - 18 Dec 2012
//
// Call this script as e.g. ...
//         realtimeLogSql.php?recordAge=NNN&recordUnit=XXX&nth=NNN&temp&press
//         monthLogSql.php?recordAge=6&recordUnit=day&nth=5&Temp
//  Where:
//    recordAge=NNN
//		Returns records newer than NNN recordUnits, 6 hours in this example
//    recordUnit=XXX
//		The units to use for recordAge (second,minute,hour,day,week,month,quarter,year)
//    nth=NNN [OPTIONAL parameter]
//		If supplied the script will only return every n'th row from the realtime table, otherwise all
//      rows within the time range are returned. Useful for reducing large data sets for presentation.
//      This example will return every 5th row from the table
//    &temp&press
//		Returns the temperature and pressure data - concatenate as many record types as you wish.
//
//  The data arrays are returned in an object with the key name equal to
//  the param name supplied in the URL. In the example above the returned
//  object will be: {"temp": [[t0,v0],[t1,v1],...],
//                   "press": [[t0,v0],[t1,v1],...]}
//-----------------------------------------------------------------

// EDIT THIS NEXT SECTION CAREFULLY
// ----------------------------------------------------------------
// The server host name or number running your MySQL database
// usually 127.0.0.1 or localhost will suffice
$dbhost = ' ';
//
// The username used to log-in to your database server
$dbuser = ' ';
//
// The password used to log-in to your database server
$dbpassword = ' ';
//
// The name of the MySQL database we will store the tables in
$database = ' ';
//
// A better way of entering your login details is to put them in a separate script
// and include this here. This script should be placed in an area accessible to PHP
// but not web users. Your login details will not then beexposed by crashing this
// script.
// e.g. ...
include ('../forbidden/db_ro_details.php');

// The name of your realtime log table
$logtable = 'AllCdata';

//-----------------------------------------------------------------

// Acceptable recordUnit parameter values
$validunits = array('second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year');

// Columns of realtime log table
// Use the same names as the corresponding web tags

$rfields = array(
    'LogDateTime','Temp','Humidity','Dewpoint','Windspeed','Windgust','Windbearing','RainRate','TodayRainSoFar','Pressure',
	'Raincounter','InsideTemp','InsideHumidity','LatestWindGust','WindChill','HeatIndex','UVindex','SolarRad','Evapotrans',
	'AnnualEvapTran','ApparentTemp','MaxSolarRad','HrsSunShine','CurrWindBearing','RG11rain');

// Standard Source view option check
function check_sourceview () {
    global $SITE;
    if (isset($_GET['view']) && $_GET['view'] == 'sce') {
        $filenameReal = __FILE__;
        $download_size = filesize($filenameReal);
        header('Pragma: public');
        header('Cache-Control: private');
        header('Cache-Control: no-cache, must-revalidate');
        header('Content-type: text/plain');
        header('Accept-Ranges: bytes');
        header("Content-Length: $download_size");
        header('Connection: close');
        readfile($filenameReal);
        exit;
    }
}

// ============= Start it off... ============

// Just list the PHP source?
check_sourceview();


// check parameters (just presence, no value checking)
if (array_key_exists('recordAge', $_GET)) {
	$recordAge = $_GET['recordAge'];
} else {
	die('You must supply a "recordAge" parameter');
}

if (array_key_exists('recordUnit', $_GET)) {
	$recordUnit = strtolower($_GET['recordUnit']);
	if (!in_array($recordUnit, $validunits)) {
		die("invalid 'recordUnit' supplied - '$recordUnit'");
	}
} else {
		die('You must supply a "recordVal" parameter');
}

if (array_key_exists('nth', $_GET)) {
	$nth = $_GET['nth'];
} else {
	$nth = '';
}

if (array_key_exists('strt', $_GET)) {
	//$strtd = date('Y-m-d',strtotime($_GET['strt']));
	$strtd = $_GET['strt'];
	//$strtd = date('Y-m-d',strtotime("2017-10-11"));
	//echo $strtd;
}


// Process the script value arguments
$dataTypes = array();
foreach($_GET as $key => $value) {
    if (in_array($key, $rfields)) {
        $dataTypes[] = $key;
    }
}

// Did we get any valid parameters?
if (count($dataTypes) == 0) {
    die('No valid return data types supplied');
}

// Connect to the datbase
$con = mysql_connect($dbhost, $dbuser, $dbpassword);
if (!$con) {
	die('Failed to connect to the database server');
}

if (!mysql_select_db($database, $con)) {
	die('Failed to connect to the database on the server');
}

$result = mysql_query('SET time_zone="+0:00"');
if (!$result) {
	die('ERROR - TZ Statement');
}

// Was the 'nth' parameter supplied? If not, do a standard query, else, only return every nth row from the table
if ($nth === '') {

	// Start of query string, then...
	$query = 'SELECT UNIX_TIMESTAMP(LogDateTime)';
	//$query = 'SELECT LogDateTime';
	// add query data columns, then..
	foreach($dataTypes as $key) {
		$query .= ', ' . $key;
	}

	// end of query string.
	$query .= " FROM $logtable WHERE LogDateTime > DATE_SUB($strtd, INTERVAL $recordAge $recordUnit) and LogDateTime < DATE_ADD($strtd, INTERVAL 1 day) ORDER BY LogDateTime ASC";

} else {  // Only return every nth row

	// Start of query string, then...
	$query = 'SELECT ts';

	// add query data columns, then..
	foreach($dataTypes as $key) {
		$query .= ', ' . $key;
	}
	$query .= ' FROM ( SELECT @row := @row +1 AS rownum, UNIX_TIMESTAMP(LogDateTime) AS ts';

	// add query data columns, then..
	foreach($dataTypes as $key) {
		$query .= ', ' . $key;
	}
	// end of query string.
	$query .= " FROM $logtable WHERE LogDateTime > DATE_SUB($strtd, INTERVAL $recordAge $recordUnit) ORDER BY LogDateTime ASC) as DT WHERE MOD(rownum,$nth)=0";

	// initialise the row counter
	$result = mysql_query('SET @row := 0;');
	if (!$result) {
		die("ERROR - Bad SQL Statement: $query");
	}
}

// Perform query
$result = mysql_query($query);
if (!$result) {
	die("ERROR - Bad Select Statement: $query");
}

// for each of the returned rows, put the data into a series of arrays...
while($row = mysql_fetch_row($result)) {
	$tim = $row[0] * 1000;
	$i = 1;
	foreach($dataTypes as $key) {
        ${"arr_$key"}[]  = array($tim, (float)$row[$i++]);
    }
}

// build the return array
foreach($dataTypes as $key) {
    $return[$key] = ${'arr_' . $key};
}

header('Cache-Control: private');
header('Cache-Control: no-cache, must-revalidate');
header('Content-type: text/json  charset=UTF-8');

// encode the result and echo it back to the client
echo json_encode($return);

// ========== End of Module ===========
?>