
<?php
//-----------------------------------------------------------------
// Query a Cumulus dayfile MySQL table and return the cumulative growing degree days
// by year as defined by the parameters in the URL. This sums the average daily temperature for each day 
// that is above the base temperature by year. Daily temperatures below the base temperature are set to zero.
// Author: FG Weather
// Version 1.0 - 18 Sep 2021


// Call this script as e.g. ...
//         historicGrowingDegreeDaysDaily.php?startMonth=7&baseTemp1=0&baseTemp2=5&tempCapAt30C=true
//  Where:
//    startMonth=XX
//		The starting month for the year - e.g 1 for Jan, 10 for Oct. For Feb or later the year is attributed to the starting month.
//		i.e. - setting 2 would show the year for example as Feb 2020 to Jan 2021.
//    baseTemp1=XX [value between 0 and 30]
//		Sets the first series base temperature
//    baseTemp2=XX [value between 0 and 30]
//		Sets the second series base temperature
//    tempCapAt30C = [true | false]
//      Enables or disables capping of daily maximum temperatures to 30 C as many plants stops growing above 30 C.
//    
//  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 like this: {"GDD1":{"2009":[[t0,v0],[t1,v1],...],"2010":[[t0,v0],[t1,v1],...],....,
//                   		"GDD2":{"2009":[[t0,v0],[t1,v1],...],"2010":[[t0,v0],[t1,v1],...],....,}
// Data arrays are also returned to report on the options selected in the URL.
//-----------------------------------------------------------------

// configuration settings - temp in C
//$baseTemp1 = 5; //The first series base temperature which is to be used to calculate the growing degree days 
//$baseTemp2 = 10; //The second series base temperature which is to be used to calculate the growing degree days 
//$startMonth = 7; //The month you want the growing season year to start 
//$TempcapAt30C = true; //

//Define the key names
$BaseTemp1key ="GDD1";
$BaseTemp2key ="GDD2";

// Acceptable validBoolean parameter values
$validBoolean = array('true', 'false');

//check parameter - startMonth
if (array_key_exists('startMonth', $_GET)) {
	$startMonth = strtolower($_GET['startMonth']);
	if (!in_array($startMonth, range(1, 12))) {
		die("invalid 'startMonth' supplied. Must be between 1 and 12. - '$startMonth'");
	}
} else {
		die('You must supply a "startMonth" parameter');
}

//check parameter - baseTemp1
if (array_key_exists('baseTemp1', $_GET)) {
	$baseTemp1 = strtolower($_GET['baseTemp1']);
	if (!in_array($baseTemp1, range(0, 30))) {
		die("invalid 'baseTemp1' supplied. Must be between 0 and 30.  - '$baseTemp1'");
	}
} else {
		die('You must supply a "baseTemp1" parameter');
}

//check parameter - baseTemp2
if (array_key_exists('baseTemp2', $_GET)) {
	$baseTemp2 = strtolower($_GET['baseTemp2']);
	if (!in_array($baseTemp2, range(0, 30))) {
		die("invalid 'baseTemp2' supplied. Must be between 0 and 30. - '$baseTemp2'");
	}
} else {
		die('You must supply a "baseTemp2" parameter');
}

//check parameter - cap temps at 30 C
if (array_key_exists('tempCapAt30C', $_GET)) {
	$tempCapAt30C = strtolower($_GET['tempCapAt30C']);
	if (!in_array($tempCapAt30C, $validBoolean)) {
		die("invalid 'tempCapAt30C' supplied. Can only be 'true' or 'false'. - '$tempCapAt30C'");
	}
} else {
		die('You must supply a "tempCapAt30C" parameter');
}

//MySQL database auth - pull the auth details from this file 
include(dirname(__DIR__) . "/../wd_db_auth_detail.php");

// Standard Source view option check
function check_sourceview () {
	if (isset($_GET['view']) && ($_GET['view'] == 'sce' || $_GET['view'] == 'src')) {
		$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;
	}
}

// Just list the PHP source?
check_sourceview();

// Connect to the database
$mysqli = new mysqli($dbhost, $dbuser, $dbpassword, $database);
if ($mysqli->connect_errno) {
    die('Failed to connect to the database server - ' . $mysqli->connect_error);
}

#
# The db querys
#

$result = $mysqli->query('SET time_zone="+00:00"');
if (!$result) {
	die('ERROR - TZ Statement');
}

$queryYears = 'SELECT DISTINCT YEAR(LogDate) AS year FROM Dayfile ORDER BY year'; 

// get the years for the database
$resultYears = $mysqli->query($queryYears);
if (!$resultYears) {
	die('ERROR - Bad Select Statement 1');
}

// set the URL parameter inputs
$result = $mysqli->query('SET @baseTemp1 = ' . $baseTemp1 . ', @baseTemp2 = ' . $baseTemp2 . ',@startMonth =' . $startMonth . ',@tempCapAt30C =' . $tempCapAt30C . ';');

if (!$result) {
	die('ERROR - Config Statement');
}

// for each year get the data
while($rowYear = $resultYears->fetch_array()) {
	$currYear = $rowYear[0];
	$currYear1 = $currYear + 1;

// construct a new query for the data 
$query = 
    'SELECT '.
    '   UNIX_TIMESTAMP(LogDateCommon), '.
    '   Year,'.
    '   ROUND(annual_growing_degree_day_base_1,1) AS annual_growing_degree_day_base_1, '.
    '   ROUND(annual_growing_degree_day_base_2,1) AS annual_growing_degree_day_base_2 '.
    '   FROM ( '.
    '        WITH gdd AS ('.
    '            /* determine the GDD as the delta of avg temp - base temp */'.
    '            SELECT '. 
    '                LogDate,'.
    '                CASE WHEN @startmonth = 1 THEN DATE_FORMAT(LogDate, "2000-%m-%d") '.
    '                     WHEN MONTH(LogDate) >= @startmonth  AND @startMonth = 2 THEN DATE_FORMAT(LogDate, "2000-%m-%d") '.
    '                     WHEN MONTH(LogDate) < @startmonth  AND @startMonth = 2 THEN DATE_FORMAT(LogDate, "2001-%m-%d") '.
    '                     WHEN MONTH(LogDate) >= @startmonth THEN DATE_FORMAT(LogDate, "1999-%m-%d") '.
    '                     ELSE DATE_FORMAT(LogDate, "2000-%m-%d") END AS LogDateCommon,' .
    '                CASE WHEN MONTH(LogDate) <= @startMonth - 1 THEN YEAR(LogDate) - 1 ELSE YEAR(LogDate) END AS year, '.
    '                CASE WHEN AvgTemp > @baseTemp1 THEN AvgTemp - @baseTemp1 ELSE 0 END AS growing_degree_day_base_1, '.
    '                CASE WHEN AvgTemp > @baseTemp1 THEN AvgTemp - @baseTemp2 ELSE 0 END AS growing_degree_day_base_2 '.
    '            FROM ( '.
    '                /* determine the avg temp for each day */'.
    '                SELECT LogDate, CASE WHEN MinTemp IS NOT NULL AND MaxTemp IS NOT NULL THEN (MinTemp + MaxTemp) / 2 ELSE 0 END AS avgTemp '.
    '                FROM ( '.
    '                    /* cap max temps at 30 C? */'.
    '                    SELECT LogDate, MinTemp, CASE WHEN MaxTemp > 30 AND @tempCapAt30C  = TRUE THEN 30 ELSE MaxTemp END AS MaxTemp '.
    '                    FROM Dayfile '.
    '                    WHERE LogDate >= "2010-02-14" '.
    '                ) AS groupedData '.
    '            ) AS AvgTemp '.
    '        ) '.
    '        SELECT '.
    '        /* determine the annual cumulative GDD for each year */'.
    '            LogDate, '.
    '            LogDateCommon,'.
    '            year, '.
    '            SUM(growing_degree_day_base_1) OVER (PARTITION by year ORDER BY LogDateCommon) AS annual_growing_degree_day_base_1, '.
    '           SUM(growing_degree_day_base_2) OVER (PARTITION by year ORDER BY LogDateCommon) AS annual_growing_degree_day_base_2 '.
    '        FROM gdd '.
    ') AS annualGDD '.
    'WHERE LogDate >= "' . $currYear . '-01-01" '.
		'AND LogDate < "' . $currYear1 . '-01-01" ';
        


$result = $mysqli->query($query);
	if (!$result) {
		die('ERROR - Bad Select Statement query ' . $currYear . ' ' . $mysqli->error . '<br><br>' . $query);
	}

    // import the rows and put the data into arrays
    while($row = $result->fetch_array()) {
        $GDD1[$row[1]][] = array(($row[0] + 6000) *1000, (float)$row[2]);
        $GDD2[$row[1]][] = array(($row[0] + 6000) *1000, (float)$row[3]);

	}
	
} 

// import the rows and put the data into arrays by key name, along with selected options
$return[$BaseTemp1key] = $GDD1;
$return[$BaseTemp2key] = $GDD2;
$return["options"] = array("gddBase1" => $baseTemp1,"gddBase2"=> $baseTemp2,"startMon"=> $startMonth);

// close connection
$mysqli->close();

header('Content-type: text/json');
header('Cache-Control: private');

echo json_encode($return);
?>