query($query);
if (!$result) {
echo "$query
";
die('ERROR - Bad Select Statement (day) -' . $con->error);
}
$CumulusDate = $result->fetch_array();
$rollovertime = "0am"; // $rollovertime = "9am";
//--------------------------------------------------------------------------------------------------------
$statisticsLabel=[ // summary row labels
"- Lowest - ↓", // element 0
"- Highest - ↑", // element 1
"- Total - Σ", // element 2
"- Greatest - ↑", // element 3
"- Strongest - ↑", // element 4
"- Least - ↓" // element 5
];
// Configure some variables specific to my data - change these for your own implementation
$earliestCumulusYear=2012;
$earliestCumulusDay=10;
$earliestCumulusMonthName="March";
$earliestCumulusMonthNum=03;
$rollover = 1 * ("0".substr ($rollovertime, 0, 1));
###################################
#### Analyse Query-String ####
###################################
$columnNameLeft = null;
$columnNameRight = null;
$dayfileColumnsAndCaptions = array(
// column name // caption
['HighWindGust', 'Racha Máxima'], // 'Strongest Wind Gust of day'
['HWindGBear', 'Dirección de la Racha Máxima'], // 'Bearing of Strongest Wind Gust'
['THWindG', 'Hora de la Racha Máxima'], // 'Time-stamp for StrongestWindGust'
['MinTemp', 'Temperatura Mínima'], // 'Lowest temperature recorded for day'
['TMinTemp', 'Hora de la Temperatura Mínima'], // 'Time-stamp for lowest temperature'
['MaxTemp', 'Temperatura Máxima'], // 'Highest temperature recorded for day'
['TMaxTemp', 'Hora de la Temperatura Máxima'], // 'Time-stamp for highest temperature'
['MinPress', 'Presión Mínima'], // 'Lowest pressure recorded for day'
['TMinPress', 'Hora de la Presión Mínima'], // 'Time-stamp for lowest pressure'
['MaxPress', 'Highest pressure recorded for day'],
['TMaxPress', 'Time-stamp for highest pressure'],
['MaxRainRate', 'Highest rain rate for day'],
['TMaxRR', 'Time-stamp for highest rain rate'],
['TotRainFall', 'Total rainfall recorded against day'],
['AvgTemp', 'Average of all temperature measurements in day'],
['TotWindRun', 'Total wind run recorded against day'],
['HighAvgWSpeed', 'Highest Average Wind Speed of day'],
['THAvgWSpeed', 'Time-stamp for highest average wind speed'],
['LowHum', 'Lowest relative humidity recorded for day'],
['TLowHum', 'Time-stamp for lowest relative humidity'],
['HighHum', 'Highest relative humidity recorded for day'],
['THighHum', 'Time-stamp for highest relative humidity'],
['TotalEvap', 'Total evapotranspiration recorded against day'],
['HoursSun', 'Total sunshine hours recorded against day'],
['HighHeatInd', 'Highest heat index calculated for day'],
['THighHeatInd', 'Time-stamp for highest heat index'],
['HighAppTemp', 'Highest apparent temperature recorded for day'],
['THighAppTemp', 'Time-stamp for highest apparent temperature'],
['LowAppTemp', 'Lowest apparent temperature recorded for day'],
['TLowAppTemp', 'Time-stamp for lowest apparent temperature'],
['HighHourRain', 'Highest rain fall total over an hour in day'],
['THighHourRain', 'Time-stamp for end of hour of highest hourly rain'],
['LowWindChill', 'Greatest wind chill calculated for day'],
['TLowWindChill', 'Time-stamp for greatest wind chill'],
['HighDewPoint', 'Highest dew-point for day'],
['THighDewPoint', 'Time-stamp for highest dew-point'],
['LowDewPoint', 'Lowest dew-point for day'],
['TLowDewPoint', 'Time-stamp for lowest dew-point'],
['DomWindDir', 'Bearing of Dominant wind in degrees'],
['HeatDegDays', 'Heating Degree Days figure'],
['CoolDegDays', 'Cooling Degree Days figure'],
['HighSolarRad', 'Highest solar radiation for day'],
['THighSolarRad', 'Time-stamp for highest solar radiation'],
['HighUV', 'Highest UltraViolet report for day'],
['THighUV', 'Time of highest UV'],
['HWindGBearSym', 'Bearing of Strongest Wind Gust as Compass Symbol'],
['DomWindDirSym', 'Bearing of Dominant wind direction as Compass Symbol']
);
if ( $_SERVER[ 'REQUEST_METHOD' ] == 'GET' )
{
// process the selections made on form
// Examine query-string for action
$action = array_key_exists('action', $_GET) ? $_GET['action'] : 'both';
// Examine query-string for criterion
if(array_key_exists('left_field', $_GET))
{
$columnNameLeft = $_GET['left_field']; // changes column to that selected when form submitted
for($i=0; $i < count($dayfileColumnsAndCaptions); $i++)
{
if($dayfileColumnsAndCaptions[$i][0] == $columnNameLeft)
{
$caption = $dayfileColumnsAndCaptions[$i][1];
break;
}
}
}
if($action != 'single' and array_key_exists('right_field', $_GET))
{
$columnNameRight = $_GET['right_field']; // changes column to that selected when form submitted
for($i=0; $i < count($dayfileColumnsAndCaptions); $i++)
{
if($dayfileColumnsAndCaptions[$i][0] == $columnNameRight)
{
break;
}
}
if(substr($columnNameRight, 0, 1) == 'T' and substr($columnNameRight, 1, 2) != 'ot' and $columnNameRight != 'T' . $columnNameLeft)
{
$action = 'single';
$columnNameRight = null;
$caption .= ' (Selection of RH column rejected as nonsensical)';
}else
$caption .= ' and ' . $dayfileColumnsAndCaptions[$i][1];
}
# ----------------------------
$displayYearStart = null;
$displayYearEnd = null;
$displayMonthEnd = 11;
if(array_key_exists('end-year', $_GET))
// Examine query-string for end-year
{
$displayYearEnd = $_GET['end-year'];
if(array_key_exists('end-month', $_GET))
// Examine query-string for end-month
{
$displayMonthEnd = $_GET['end-month'];
$displayMonthStart = ($displayMonthEnd + 13)%12;
if($displayMonthStart <= $displayMonthEnd) $displayYearStart = $displayYearEnd;
else $displayYearStart = $displayYearEnd - 1;
if($displayYearEnd == $earliestCumulusYear)
{
$displayYearStart = $earliestCumulusYear;
$displayMonthStart = '0' . $earliestCumulusMonthNum;
}
}
}
}
global $data;
// Validate query string -->> require minimum of one column name, an end month and an end year (together those allow start year to be determined)
// if minimum validation passes, run one query to obtain all data required for output table
if(!is_null($columnNameLeft) and !is_null($displayYearStart)) $data = readDayData($displayMonthStart, $displayYearStart, $displayMonthEnd, $displayYearEnd);
# print_array($data,'Data Array'); // Uncomment this for diagnostic displaying of query results
// -----------------------------------------------------------------
?>
Daily Weather Summary Observations selected by criterion and 12-month period
Tabulated Daily Weather Observations
Processed ' . $queryRead. ' in ' . $totalSQLtime. '';
?>
Showing separate values for each Criterion in LH cells, and Range (Left Criterion➖Right Criterion) in RH cells // use utf-8 arithmetic symbols
Showing separate values for each Criterion in LH cells, and Average (Left Criterion➕Right Criterion)➗2 in RH cells
';
?>
| ";
$tableCellPrefix=" | Script v.'. $latest_version . ' | '; break;
case 32: echo '
';
if ($displayYearStart != $displayYearEnd) {
echo $displayYearStart;
echo $periodMessage[1];
}
echo $displayYearEnd;
echo ' | ';
break;
// statistical row label appear in final rows
case 35:
if($wantTotalRow) echo '
'.$statisticsLabel[2].' | ';break;// total
case 34: echo '
';
// wind chill is greater with combination of lower temperatures and higher wind speeds, so use 'greater' rather than 'higher' or 'lower'
echo $columnNameLeft == 'GreatWindChill' ? $statisticsLabel[3] : $statisticsLabel[0];// greatest or lowest - using column name from my schema to determine suitable label
echo ' | ';break;
case 33: echo '
';
// wind chill is least with combination of higher temperatures and lower wind speeds, so use 'least' rather than 'higher' or 'lower'
if ($columnNameLeft == 'GreatWindChill') {
echo $statisticsLabel[5];// least
}elseif ($columnNameLeft == 'HighWindGust' or $columnNameLeft == 'HighAvgWSpeed') // strongest or highest - using column names from my schema to determine suitable label
{
echo $statisticsLabel[4];// strongest
}else echo $statisticsLabel[1];// highest
echo ' | ';
break;
// spacer inserts dummy row
case 6: // fall through
case 11: // fall through
case 16: // fall through
case 21: // fall through
case 26:
echo '
'.$tableCellPrefix."'>";
for($m=1;$m<=12;$m++)
{ // 'm' used because columns represent months
echo $tableCellPrefix . "' ". $headAttrib . ">";
switch($m) { // actions by column of dummy row
case 3: // fall through
case 6: // fall through
case 9: echo $col_gap;
// fall through (no 'break;' required)
} // end switch for column
} // end m loop
echo '
';
echo "\r\n"; // add windows line terminator so browser source view looks better
// row label for table cell rows
default: echo '';
echo "\r\n";
echo '' . $ds . ' | ';
echo "\r\n"; // add windows line terminator so browser source view looks better
}// end switch for row
// For those rows not completed above, complete them with column specific content now
for($colNum=1;$colNum<=12;$colNum++)
{// columns
// build table cells
switch($ds)
{ // actions by row
case 0:
// build table cells
echo '";
$tMonth[$colNum] = 1 * ($colNum - 1 + $displayMonthStart) % 12 ;
echo $monthArray[$tMonth[$colNum]];
$tYear[$colNum] = $tYear[$colNum - 1];
if ($tMonth[$colNum] < $tMonth[$colNum - 1])$tYear[$colNum]++;
echo " " . $tYear[$colNum];
echo ' | ';
echo "\r\n"; // add windows line terminator so browser source view looks better
// work out, for this column (month) specifically, how many rows relate to days in this month
$endDate = $tYear[$colNum]."-";
$endDate .= $tMonth[$colNum]; // month and year needed to identify leap years
$daysInMonth = date ('t',strtotime($endDate)); // does month have 28, 29, 30 or 31 days?
break;
case 32:
// column label insert for this month
// Create second column element id for column labels
echo "". $monthArray[$tMonth[$colNum]] . ' | ';
break;
case 33:
// statistical highest line
// create first of two statistical cells by day
echo $tableCellPrefix . "' id='" . $ds . '-' . $colNum . "'>";
if($columnValid[$colNum]['LH']) echo $highValue[$colNum-1];
if($action == 'range' || $action = 'mean' and $columnValid[$colNum]['LH']) echo '; ' . $highValue1[$colNum-1];
echo "";
// Create column element id for column labels, relic of when JavaScript highlighting row and column for moused over cell was available
echo $tableCellPrefix."' tabindex='".(($colNum+$displayMonthEnd)%12) ."' id='".$ds.'-'.$colNum."-2'>";
// create second of two statistical cells by day
// different handling for a) time-stamp, b) unused second cell, and c) second cell contains a value
if ($columnValid[$colNum]['LH'] AND $columnNameRight == 'T' . $columnNameLeft)
{ // time-stamp
echo $highTimeUpdate[$colNum-1];
echo ' on '; // class .black is in my css for that colour
if (substr( $highTimeUpdate[$colNum-1], 0, 2) < $rollover):
$highDayUpdate[$colNum-1] ++;
if ($highDayUpdate[$colNum-1] > $daysInMonth) {
echo "1 " . $monthArray[$tMonth[($colNum + 1) % 12]];
}else{
echo $highDayUpdate[$colNum-1];
}
echo '';
else:
if ($highDayUpdate[$colNum-1] == 1)
{
echo $highDayUpdate[$colNum-1] . " " . $monthArray[$tMonth[$colNum]];
}else{
echo $highDayUpdate[$colNum-1];
}
echo '';
endif;
}elseif($highValue2[$colNum-1] > -9990)
{
echo $highValue2[$colNum-1];
if($action == 'range' or $action = 'mean') echo ' on ' . $highDayUpdate[$colNum-1] . ''; // class .black is in my css for that colour
}
echo "";
echo "\r\n";// use windows line break for end of create two statistical cells by day
break;
case 34:
// statistical lowest line
// create first of two statistical cells by day
echo $tableCellPrefix."' id='".$ds.'-'.$colNum."'>";
if($columnValid[$colNum]['LH']) echo $lowValue[$colNum-1];
if($action == 'range' || $action = 'mean' and $columnValid[$colNum]['LH']) echo '; ' . $lowValue1[$colNum-1];
echo "";
// Create column element id for column labels, relic of when JavaScript highlighting row and column for moused over cell was available
echo $tableCellPrefix."' tabindex='".(($colNum+$displayMonthEnd)%12) ."' id='".$ds.'-'.$colNum."-2'>";
// create second of two statistical cells by day
// different handling for a) time-stamp, b) unused second cell, and c) second cell contains a value
if ($columnValid[$colNum]['LH'] AND $columnNameRight == 'T' . $columnNameLeft)
{ // time-stamp
echo $lowTimeUpdate[$colNum-1];
echo ' on ';
if (substr( $lowTimeUpdate[$colNum-1], 0, 2) < $rollover): // $rollover was defined in initialisation
$lowDayUpdate[$colNum-1] ++;
if ($lowDayUpdate[$colNum-1] > $daysInMonth) {
echo "1 " . $monthArray[$tMonth[($colNum + 1) % 12]];
}else{
echo $lowDayUpdate[$colNum-1];
}
echo '';
else:
if ($lowDayUpdate[$colNum-1] == 1) {
echo $lowDayUpdate[$colNum-1] . " " . $monthArray[$tMonth[$colNum]];
}else{
echo $lowDayUpdate[$colNum-1];
}
echo '';
endif;
}elseif($lowValue2[$colNum-1] <9990)
{
echo $lowValue2[$colNum-1];
if($action == 'range' or $action = 'mean') echo ' on ' . $lowDayUpdate[$colNum-1] . ''; // class .black is in my css for that colour
}
echo "";
echo "\r\n";// use windows line break for end of create two statistical cells by day
break;
case 35:
// total line
// create one statistical cell by day
echo $tableCellPrefix."' id='".$ds.'-'.$colNum."'>";
if ($columnValid[$colNum]['LH'] AND $totalValue[$colNum-1] > 0) echo $totalValue[$colNum-1];
echo "";
// second cell per day is left empty
echo $tableCellPrefix."'>";
echo "\r\n";// use windows line break for end of create two statistical cells by day
break;
default:
// This is for rows that represent first to last day of month
// Create row/column element id for table cells, relic of when JavaScript highlighting row and column for moused over cell was available
// index to array consists of day and month, year break may occur in middle of array
$id = 'd' . $ds . '-m' . $tMonth[$colNum];
// rows for valid day of month
// ===================================
// Start of LEFT HAND OF data cell PAIR
echo "";
if ($ds > ($daysInMonth + 1) OR !isset($data[$id][0]) OR is_null ($data[$id][0]) )
{
// not a valid day set should be same as NULL but good practice to check both
// create table cell but leave empty
}else{
// Valid day, valid value to insert, create table cell with that value and set boolean to say at least one valid value in this month
echo populateCells(true, $ds, $tMonth[$colNum], $colNum);
$columnValid[$colNum]['LH'] = true;
}
echo " | ";
// END of LEFT of two data cells by day
// =====================================
// =====================================
// Start of RIGHT HAND OF data cell PAIR
echo "";
if ($ds > ($daysInMonth + 1) OR !isset($data[$id][1]) OR is_null($data[$id][1]))
{
// create table cell but leave empty
}else{
// Valid day, valid value to insert, create table cell with that value and set boolean to say at least one valid value in this month
echo populateCells(false, $ds, $tMonth[$colNum], $colNum);
$columnValid[$colNum]['RH'] = true;
}
echo " | ";
// END of RIGHT of two data cells by day
// =====================================
echo "\r\n";// use windows line break for end of create two value cells by day
} // end switch for row
/*
switch($colNum) { // actions by column, switch is meant to be more efficient than if in php
case 3:
case 6:
case 9: echo $col_gap;break;
} // end switch for column
// above code segment simplified to single line below 28 Apr 2017
*/
if($colNum % 3 == 0) echo $col_gap; // overhead of calculating if compensated by only being one instruction to parse
} // end loop through columns (months)
// complete table row
echo "
";
echo "\r\n"; // add windows line terminator so browser source view looks better
} // end loop through rows (days and statistics) for current column
echo '
';
}
?>
query($query);
if (!$result) {
echo "$query
";
die('ERROR - Bad Select Statement (day) -' . $con->error);
}
$CumulusDate = $result->fetch_array();
return $CumulusDate[0];
}
//========================================================================================================
function populateCells($populateLH, $day, $month, $colNum) // $populateLH is true if value for LH cell to be returned, and false for RH cell; other parameters specify which row and column
{
global $action, $data, $columnValid, $columnNameLeft, $lowValue, $lowValue1, $lowValue2, $highValue, $highValue1, $highValue2, $totalValue, $lowDayUpdate, $lowTimeUpdate, $highDayUpdate, $highTimeUpdate;
static $LHcellValid; // this variable retains its value between successive calls to function
$id = 'd' . $day . '-m' . $month; // index to array consists of day and month, year break may occur in middle of array
if($populateLH)
{ // LH cell
$contents = is_null($data[$id][0]) ? '': $data[$id][0];
switch($action)
{
case 'single':
case 'both':
if(!is_null($data[$id][0]) and $data[$id][0] < $lowValue[$colNum - 1])
{
$lowValue[$colNum - 1] = $data[$id][0];
$lowDayUpdate[$colNum - 1] = $day;
}
if(!is_null($data[$id][0]) and $data[$id][0] > $highValue[$colNum - 1])
{
$highValue[$colNum - 1] = $data[$id][0];
$highDayUpdate[$colNum - 1] = $day;
}
if(!is_null($data[$id][0])) $totalValue[$colNum - 1] += $data[$id][0];
break;
case 'mean':
case 'range':
if(!is_null($data[$id][0]))
{
$pair = explode(';', $data[$id][0]);
if(!is_null($pair[0]) and $pair[0] < $lowValue[$colNum - 1])
{
$lowValue[$colNum - 1] = $pair[0];
$lowDayUpdate[$colNum - 1] = $day;
}
if(!is_null($pair[1]) and $pair[1] < $lowValue1[$colNum - 1])
{
$lowValue1[$colNum - 1] = $pair[1];
$lowDayUpdate[$colNum - 1] = $day;
}
if(!is_null($pair[0]) and $pair[0] > $highValue[$colNum - 1])
{
$highValue[$colNum - 1] = $pair[0];
$highDayUpdate[$colNum - 1] = $day;
}
if(!is_null($pair[1]) and $pair[1] > $highValue1[$colNum - 1])
{
$highValue1[$colNum - 1] = $pair[1];
$highDayUpdate[$colNum - 1] = $day;
}
}
break;
}
return $contents;
}
// RH cell
$contents = ($action != 'single' and is_null($data[$id][1])) ? '': $data[$id][1];
if(strpos($contents, ':'))
{
#################################
# Code for handling time-stamps #
#################################
if($lowDayUpdate[$colNum - 1] == $day) $lowTimeUpdate[$colNum - 1] = $contents;
if($highDayUpdate[$colNum - 1] == $day) $highTimeUpdate[$colNum - 1] = $contents;
return $contents;
}else{
switch($action)
{
case 'mean':
case 'both':
if(!is_null($data[$id][1]) and $data[$id][1] < $lowValue2[$colNum - 1])
{
$lowValue2[$colNum - 1] = $data[$id][1];
}
if(!is_null($data[$id][1]) and $data[$id][1] > $highValue2[$colNum - 1])
{
$highValue2[$colNum - 1] = $data[$id][1];
}
# if(!is_null($data[$id][1])) $totalValue2[$colNum - 1] += $data[$id][1];
return $contents;
case 'range':
###############################
# Code for handling ranges #
###############################
if(!is_null($data[$id][1]))
{
// update difference value statistics
$lowValue2[$colNum-1] = min($contents, $lowValue2[($colNum-1)]);
$highValue2[$colNum-1] = max($contents, $highValue2[($colNum-1)]);
}
return $contents;
}
}
}
//========================================================================================================
# function readDayData($tableStartMonth, $tableStartYear, $tableEndMonth, $tableEndYear, $fld)
function readDayData($displayMonthStart, $displayYearStart, $displayMonthEnd, $displayYearEnd)
{
global $con, $dayFileTableName, $startSQLtime, $totalSQLtime, $columnNameLeft, $columnNameRight, $action, $queryRead, $totalSQLtime;
# ----------------------------
$daysInMonth = date ('t',strtotime($displayYearEnd . '-' . ($displayMonthEnd+1) . '-' . "01")); // Does month have 28, 29, 30 or 31 days?
$queryRead = 'SELECT MONTH(LogDate) AS m0, DAYOFMONTH(LogDate) AS d0, ';
switch($action)
{
case 'single':
$queryRead .= $columnNameLeft . ' AS v0';
break;
case 'both':
$queryRead .= $columnNameLeft . ' AS v0, ' . $columnNameRight . ' AS v1';
break;
case 'mean':
$queryRead .= "CONCAT_WS('; ', $columnNameLeft, $columnNameRight) AS v0, ROUND(($columnNameLeft + $columnNameRight) / 2.0, 1) AS av1 ";
break;
case 'range':
$queryRead .= "CONCAT_WS('; ', $columnNameLeft, $columnNameRight) AS v0, ($columnNameLeft - $columnNameRight) AS r1 ";
break;
}
$queryRead .= ' FROM ' . $dayFileTableName . ' WHERE LogDate BETWEEN "' . $displayYearStart . '-' . ($displayMonthStart+1) . '-01" AND "'
. $displayYearEnd . '-' . ($displayMonthEnd+1) . '-' . $daysInMonth . '"';
$result = $con->query($queryRead);
if (!$result) {
echo 'ERROR - Bad Select Statement ' . $con->error . '
';
echo $queryRead;
echo "";
exit();
}
// import the rows and put the data into array
while ($items = $result->fetch_array())
{
$id = "d". (1 * $items['d0']) . '-m' . ($items['m0'] - 1); // dd-mm e.g. 23-00 for 23 Jan 2014 as Script month number is an index from zero (the start of all arrays),
// (on db 01 is January), and table column number also starts with month 1 (as column zero is row label).
$dataArray[$id][0] = $items['v0'];
switch($action)
{
case 'single':
break;
case 'both':
$dataArray[$id][1] = $items['v1'];
break;
case 'mean':
$dataArray[$id][1] = $items['av1'];
break;
case 'range':
$dataArray[$id][1] = $items['r1'];
break;
}
}
mysqli_free_result($result); # Free result set
//End of SQL execution timer
$mtime = microtime();
$mtime = explode(" ",$mtime);
$mtime = $mtime[1] + $mtime[0];
$endtime = $mtime;
$totalSQLtime = ($endtime - $startSQLtime);
return $dataArray;
}?>