Example #1
0
<?php

//require_once("db.php");
require_once "sensor_names.php";
require_once "sensor_log_db.php";
// header
$csv_export = 'Sensor ID;Sensor Name;Datetime;Value';
$csv_export .= '
';
// newline (seems to work both on Linux & Windows servers)
// flush all TMP data to storage.
flush_sensor_data_to_permanent_storage();
$sensor_log_db = open_sensor_DB_in_STORAGE(true);
//isset($_GET['period']) ? $period = $_GET['period'] : $period = "hour";
isset($_GET['period']) ? $period = $_GET['period'] : ($period = "hour");
//error_log("XXZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ" . $period);
//isset($_GET['date_from']) ? $date_from= $_GET['date_from'] : $date_from= "";
isset($_GET['date_from']) ? $date_from = $_GET['date_from'] : ($date_from = "");
isset($_GET['date_to']) ? $date_to = $_GET['date_to'] : ($date_to = "");
isset($_GET['date_to']) ? $date_to = $_GET['date_to'] : ($date_to = "");
//isset($_GET['single_sensor_selected']) ? $single_sensor_selected = $_GET['single_sensor_selected'] : $single_sensor_selected= "";
isset($_GET['single_sensor_selected']) ? $single_sensor_selected = $_GET['single_sensor_selected'] : ($single_sensor_selected = "");
if ($period == "hour") {
    $query_datetime_filter = " AND datetime > datetime('now','localtime','-1 hour')";
}
if ($period == "3hrs") {
    $query_datetime_filter = " AND datetime > datetime('now','localtime','-3 hours')";
}
if ($period == "6hrs") {
    $query_datetime_filter = " AND datetime > datetime('now','localtime','-6 hours')";
}
function sensor_historic_data($request_data)
{
    //error_log ($request_data['selected_sensor_ids']);
    $all_sensor_data = '';
    //place this before any script you want to calculate time
    //$time_start = microtime(true);
    // flush all TMP data to storage.
    //flush_sensor_data_to_permanent_storage();
    $sensor_log_db = open_sensor_DB_in_STORAGE(true);
    /*
    	 $time_end = microtime(true);
    	 $execution_time = ($time_end - $time_start);
    	 $time_start = $time_end;
    	 //execution time of the script
    	 error_log ( '<b>++++++++++++++++++++++++++++++++++++++++++++++++++++++++Open DB  Time:</b> '.$execution_time.' sec<br />');
    */
    //handle parameters
    //isset($_GET['json']) ? $json_result = $_GET['json'] : $json_result = false;
    $json_result = true;
    // data period and other parameters.
    //isset($_GET['period']) ? $period = $_GET['period'] : $period = "hour";
    isset($request_data['period']) ? $period = $request_data['period'] : ($period = "hour");
    //isset($_GET['date_from']) ? $date_from= $_GET['date_from'] : $date_from= "";
    isset($request_data['date_from']) ? $date_from = $request_data['date_from'] : ($date_from = "");
    isset($request_data['date_to']) ? $date_to = $request_data['date_to'] : ($date_to = "");
    isset($request_data['date_to']) ? $date_to = $request_data['date_to'] : ($date_to = "");
    if ($period == "hour") {
        $query_datetime_filter = " AND datetime > datetime('now','localtime','-1 hour')";
    }
    if ($period == "3hrs") {
        $query_datetime_filter = " AND datetime > datetime('now','localtime','-3 hours')";
    }
    if ($period == "6hrs") {
        $query_datetime_filter = " AND datetime > datetime('now','localtime','-6 hours')";
    }
    if ($period == "day") {
        $query_datetime_filter = " AND datetime > datetime('now','localtime','-1 day') and strftime ('%M', datetime) like '_1'";
    }
    // every 10 minutes
    if ($period == "3days") {
        $query_datetime_filter = " AND datetime > datetime('now','localtime','-3 days') and strftime ('%M', datetime) = '01'";
    }
    // every hour
    if ($period == "week") {
        $query_datetime_filter = " AND datetime > datetime('now','localtime','-7 days') and strftime ('%M', datetime) = '01'";
    }
    // every hour
    if ($period == "month") {
        $query_datetime_filter = " AND datetime > datetime('now','localtime','-1 month') and strftime ('%M', datetime) = '01'";
    }
    // every hour
    if ($period == "date_range") {
        if ($date_from != "" and $date_to != "") {
            $query_datetime_filter = sprintf(" AND datetime >= datetime('%s') AND datetime <= datetime('%s')  and strftime ('%%M', datetime) = '01'", $date_from, $date_to);
        }
    }
    //error_log(print_r($request_data,TRUE));
    $available_sensors = array();
    //isset($_GET['single_sensor_selected']) ? $single_sensor_selected = $_GET['single_sensor_selected'] : $single_sensor_selected= "";
    if (isset($request_data['single_sensor_selected'])) {
        if ($request_data['single_sensor_selected'] != "") {
            $available_sensors[] = $request_data['single_sensor_selected'];
        }
    }
    //error_log(print_r($request_data,TRUE));
    if (isset($request_data['selcected_sensors'])) {
        // prepare data selection only for the selected sensors
        $selected_sensors_array = json_decode($request_data['selcected_sensors']);
        foreach ($selected_sensors_array as $sensor) {
            $available_sensors[] = $sensor;
        }
    }
    if (empty($available_sensors)) {
        $results = $sensor_log_db->query("select distinct sensor_id from sensor_log ;");
        while ($row = $results->fetchArray()) {
            $available_sensors[] = $row['sensor_id'];
        }
    }
    //error_log(print_r($available_sensors,TRUE));
    // get all data from tempfs
    $sensor_log_db_tempfs = open_sensor_log_db_in_TEMPFS_();
    $query_sensor_id_filter = "";
    // get data for each sensor.
    foreach ($available_sensors as $sensor) {
        $query_sensor_id_filter = " AND sensor_id = '{$sensor}'";
        //error_log (  '++++++++++++++++++++++++++++++++++++++++++++'.$sensor.' ');
        $results = $sensor_log_db->query('SELECT * FROM sensor_log where 1 ' . $query_sensor_id_filter . $query_datetime_filter);
        while ($row = $results->fetchArray()) {
            $sensor_id = $row['sensor_id'];
            $datetime = strtotime($row['datetime']);
            $datetime *= 1000;
            // convert from Unix timestamp to JavaScript time
            $sensor_data = (double) $row["value"];
            //var_dump($row);
            //print ("<br / > " . $row['sensor_id'] . $row['value'] . $row['datetime'] . "<br / > " );
            if ($json_result) {
                $all_sensor_data["{$sensor_id}"][] = array($datetime, $sensor_data);
            } else {
                $all_sensor_data["{$sensor_id}"][] = " [{$datetime}, {$sensor_data}] ";
            }
        }
        $results2 = $sensor_log_db_tempfs->query('SELECT * FROM sensor_log where 1 ' . $query_sensor_id_filter . $query_datetime_filter);
        while ($row2 = $results2->fetchArray()) {
            $sensor_id = $row2['sensor_id'];
            $datetime = strtotime($row2['datetime']);
            $datetime *= 1000;
            // convert from Unix timestamp to JavaScript time
            $sensor_data = (double) $row2["value"];
            //var_dump($row);
            //print ("<br / > " . $row['sensor_id'] . $row['value'] . $row['datetime'] . "<br / > " );
            if ($json_result) {
                $all_sensor_data["{$sensor_id}"][] = array($datetime, $sensor_data);
            } else {
                $all_sensor_data["{$sensor_id}"][] = " [{$datetime}, {$sensor_data}] ";
            }
        }
    }
    return $all_sensor_data;
}