fix viewcalls
[scannr.git] / calls.json.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
<?php
include('common.inc.php');
function getSensorValuesByHour($sensorID, $timeFrom, $timeTo) {
    global $conn;
    $sth = $conn->prepare( 'select min(call_timestamp) as time, count(*), min(length), max(length), avg(length), stddev(length) from recordings
            group by tgid, date_trunc(\'hour\', call_timestamp) order by time');
 
    $sth->execute( Array($sensorID, $timeFrom, $timeTo));
    return  $sth->fetchAll();
}
 
function getSensorValuesByDay($sensorID, $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($sensorID));
    return  $sth->fetchAll();
}
function getSensorDataYears($sensorID, $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($sensorID));
    return  $sth->fetchAll();
}
 
function getSensorDataMonths($sensorID, $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($sensorID));
    return  $sth->fetchAll();
}
 
function getSensorDataDays($sensorID, $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($sensorID));
    return  $sth->fetchAll();
}
$action = $_REQUEST['action'];
    $sensorID = $_REQUEST['tgid'];
$timefrom = $_REQUEST['from'];
$timeto = $_REQUEST['to'];
if ($action = "data_description") {
    $timefrom = strtotime($timefrom);
    $timeto = strtotime($timeto);
    $years = getSensorDataYears($sensorID, $timefrom, $timeto);
 
    $months = getSensorDataMonths($sensorID, $timefrom, $timeto);
    $days = getSensorDataDays($sensorID, $timefrom, $timeto);
 
    echo json_encode(Array("years" => $years, "months" => $months, "days" => $days
                    ));
}
 
 
if ($action = "graph") {
    $values = getSensorValuesByHour($sensorID, $timefrom, $timeto);
    $label = $sensorID;
    $data = Array();
    $tzoffset = get_timezone_offset("UTC");
    foreach ($values as $value) {
        $data[] = Array((strtotime($value['time']) + $tzoffset) * 1000, intval($value['avg']));
    }
   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)
        )
    );
}
 
?>