gettemp.php

<?php
    // php script to output JSON data to be used client side
    // by jqPlot to produce a graph
    // by Andrew Pattison

    // set timezone
    $t = date_default_timezone_set("Europe/London");

    // get start and end date/time from parameters
    // passed on URL
    $start = filter_input(INPUT_GET, "start");
    $end = filter_input(INPUT_GET, "end");
    // range - number of minutes to average temperature over
    // a value of 1 means no averaging, unless temperatures are
    // being collected more than once a minute
    $range = filter_input(INPUT_GET, "range");
    // sensor - which sensor to get data from
    $sensor = filter_input(INPUT_GET, "sensor");

    // only proceed if all 3 parameters are present
    if ($start && $end && $range)
    {
        $db = new SQLite3("observation.db");

        $result = $db->query("SELECT
                                datetime,
                                " . $sensor . "
                                FROM
                                    temperature
                                WHERE
                                    datetime > " . $start . "
                                    AND
                                    datetime <= " . $end . "
                                ORDER BY
                                    datetime ASC");
        $string = "[[";
        $rowcount = 0;
        // initialise count of rows for use in creating averages
        $c = 0;
        // initialise array to hold values to be averaged
        $a = array();
        $startTS = 0;
        $rawrowcount = 0;
        while ($row = $result->fetchArray()) {
            $rawrowcount++;
            $current = new DateTime($row['datetime']);
            $currentTS = $current->getTimestamp();
            // first time round loop - set start of datetime range
            // we are using to compute averages
            if ($c == 0) {
                $start = new DateTime($row['datetime']);
                $startTS = $start->getTimestamp();
            }
            // if current datetime is within range, add value
            // of temperature to array for averaging
            if ($currentTS - $startTS < $range * 60) {
                array_push($a, $row[$sensor]);
                $c++;
            } else {
                // first deal with values already in array
                $avg = round(array_sum($a) / $c, 2);
                // compute average time as the start datetime
                // plus half the range
                if (count($a) == 1) {
                    $avgdatetime = date('Y-m-d H:i:s', $startTS);
                } else {
                    $avgdatetime = date('Y-m-d H:i:s', $startTS + $range * 30);
                }
                $s = json_encode([$avgdatetime, $avg]);
                $start = new DateTime($row['datetime']);
                $startTS = $start->getTimestamp();
                $a = array();
                $c = 0;
                $string .= $s . ",";
                $rowcount++;
                // next deal with the current row
                array_push($a, $row[$sensor]);
                $c = 1;
            }
        }
        // handle case where there are some items in the array
        // but no more rows left to return from the database
        if (count($a) != 0) {
            $rowcount++;
            $avg = round(array_sum($a) / count($a), 2);
            if (count($a) == 1) {
                $avgdatetime = date('Y-m-d H:i:s', $startTS);
            } else {
                $avgdatetime = date('Y-m-d H:i:s', $startTS + $range * 30);
            }
            $s = json_encode([$avgdatetime, $avg]);
            $string .= $s . ",";
        }

        // strip last comma
        $string = substr($string,0,-1);
        $string .= "]]";
        if ($rowcount == 0) {
            // special case - if no rows then output null
            // to keep jqPlot happy, otherwise it does not
            // output a graph
            $string = "[[null]]";
        }
        echo $string;
    }
?>