<?php
include('common.inc.php');
function getTGIDValuesByHour($TGID, $timeFrom, $timeTo)
{
    global $conn;
    $sth = $conn->prepare('select tgid, min(call_timestamp) as time, count(*), min(length), max(length), avg(length), stddev(length) from recordings
where call_timestamp between to_timestamp(?) and to_timestamp(?)
            group by tgid, date_trunc(\'hour\', call_timestamp) order by time');
 
    $sth->execute(Array($timeFrom, $timeTo));
    return $sth->fetchAll(PDO::FETCH_ASSOC);
 
 
}
 
function getTGIDValuesByDay($TGID, $dayFrom, $dayTo)
{
    global $conn;
    $sth = $conn->prepare('select min(time) as time, min(value), max(value), avg(value), stddev(value) from sensor_values where sensor_id = ?
            group by sensor_id, date_trunc(\'day\', time) order by time');
 
    $sth->execute(Array($TGID));
    return $sth->fetchAll(PDO::FETCH_ASSOC);
}
function getTGIDDataYears($TGID, $timeFrom, $timeTo)
{
    global $conn;
    $sth = $conn->prepare("select distinct extract('year' from call_timestamp) as year from recordings where tgid = ? order by year");
 
    $sth->execute(Array($TGID));
    return $sth->fetchAll(PDO::FETCH_ASSOC);
}
 
function getTGIDDataMonths($TGID, $timeFrom, $timeTo)
{
    global $conn;
    $sth = $conn->prepare("select distinct extract('month' from call_timestamp) as month, extract('year' from call_timestamp) as year from recordings where tgid = ?  order by year, month");
 
    $sth->execute(Array($TGID));
    return $sth->fetchAll(PDO::FETCH_ASSOC);
}
 
function getTGIDDataDays($TGID, $timeFrom, $timeTo)
{
    global $conn;
    $sth = $conn->prepare("select distinct extract('day' from call_timestamp) as day, extract('month' from call_timestamp) as month, extract('year' from call_timestamp) as year from recordings where tgid = ?  order by year,month,day");
 
 
    $sth->execute(Array($TGID));
    return $sth->fetchAll(PDO::FETCH_ASSOC);
}
$action = (isset($_REQUEST['action']) ? $_REQUEST['action'] : '');
$TGID = (isset($_REQUEST['tgid']) ? $_REQUEST['tgid'] : '');
$timefrom = (isset($_REQUEST['from']) ? $_REQUEST['from'] : '');
$timeto = (isset($_REQUEST['to']) ? $_REQUEST['to'] : '');
 
if ($action == "data") {
$sth = $conn->prepare('select * from recordings
            order by call_timestamp desc limit 100');
 
$sth->execute(Array());
 
echo json_encode ($sth->fetchAll(PDO::FETCH_ASSOC));
}
if ($action == "data_description") {
    $timefrom = strtotime($timefrom);
    $timeto = strtotime($timeto);
    $years = getTGIDDataYears($TGID, $timefrom, $timeto);
 
    $months = getTGIDDataMonths($TGID, $timefrom, $timeto);
    $days = getTGIDDataDays($TGID, $timefrom, $timeto);
 
    echo json_encode(Array("years" => $years, "months" => $months, "days" => $days
    ));
}
 
 
if (strpos($action, "graph") !== false) {
    $values = getTGIDValuesByHour($TGID, $timefrom, $timeto);
    $label = $TGID;
    $data = Array();
    $tzoffset = get_timezone_offset("UTC");
    foreach ($values as $value) {
        if ($action == "graphlength") {
            $data[$value['tgid']][] = Array((strtotime($value['time']) + $tzoffset) * 1000, intval($value['avg']));
        } else if ($action == "graphcount") {
            $data[$value['tgid']][] = Array((strtotime($value['time']) + $tzoffset) * 1000, intval($value['count']));
        }
    }
    echo json_encode(Array("label" => $label, "data" => $data,
            "previous" => Array(
                "from" => $timefrom - (24 * 60 * 60),
                "to" => $timefrom)
        ,
            "next" => Array(
                "to" => $timeto + (24 * 60 * 60),
                "from" => $timeto)
        )
    );
}
 
 
 
?>