/
widget_dbplot.php
157 lines (126 loc) · 5.49 KB
/
widget_dbplot.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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
<?php
// ----------------------------------------------------------
//
// SmartVISU widget for database plots with highcharts
// (c) Tobias Geier 2015
//
// Version: 0.3
// License: GNU General Public License v2.0
//
// Manual: https://github.com/ToGe3688/db_plot_widget
//
// ----------------------------------------------------------
require_once(dirname('__FILE__')."/widget_dbplot.config.php");
/* * ****************************************
It is not needed to change any settings below if you use this widget with FHEM DbLog
* **************************************** */
$timestampColumn = 'TIMESTAMP';
$valueColumn = 'VALUE';
$unitColumn = 'UNIT';
$readingColumn = 'READING';
$deviceColumn = 'DEVICE';
$logTable = 'history';
/* * ****************************************
* ***************ENDCONFIG******************
* **************************************** */
// Set the JSON header
header("content-type: application/json");
// If one of POST values is missing return Error
if (!isset($_POST['query']) || $_POST['query'] == "") {
returnError("Missing or empty query value in POST request");
} else {
$query = $_POST['query'];
}
if (!isset($_POST['timeRangeStart']) || $_POST['timeRangeStart'] == "" || !is_numeric($_POST['timeRangeStart'])) {
returnError("Missing, wrong or empty time range start value in POST request");
} else {
$timestampStart = $_POST['timeRangeStart'];
}
if (!isset($_POST['timeRangeEnd']) || $_POST['timeRangeEnd'] == "" || !is_numeric($_POST['timeRangeEnd'])) {
returnError("Missing, wrong or empty time range end value in POST request");
} else {
$timestampEnd = $_POST['timeRangeEnd'];
}
$maxCount = (isset($_POST['maxRows'])) ? $_POST['maxRows'] : 300;
// Decode JSON for query from POST Request and basic validation for request query
$requestedDeviceReadings = json_decode($query);
if (!is_array($requestedDeviceReadings) && count($requestedDeviceReadings) == 0) {
returnError("plotOptions is no array, wrong formed or empty");
}
// Create new PDO Object for DB Connection
if ($dbType == 'sqlite') {
$db = new PDO('sqlite:' . $dbPath);
} elseif ($dbType == 'mysql') {
$db = new PDO('mysql:host=' . $host . '; port='. $port .';dbname=' . $database, $mysql_username, $mysql_password);
$db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
}
// Get requested series for the plot
foreach ($requestedDeviceReadings as $i => $deviceReading) {
// Prepare plot array
$plotArray = array();
// Execute DB Query for device reading
$resultArray = getData($deviceReading->device, $deviceReading->reading, $timestampStart, $timestampEnd, $maxCount, $db);
$stmt = $resultArray;
// Loop through fetched data from db and push values to plot array
for ($a = 0; $row = $stmt->fetch(PDO::FETCH_ASSOC); $a++) {
// Convert datetime from timestamp column to unix timestamp or set to current time if update request
$timestamp = strtotime($row['TIMESTAMP']) * 1000;
$item = array($timestamp, floatval($row['VALUE']));
array_push($plotArray, $item);
}
// Fill return array with Options for plot
foreach ($deviceReading->config as $key => $value) {
$returnArray[$i][$key] = $value;
// Check if unit for readings is allready set
$unitSet = ($key == 'unit') ? true : false;
}
// If unit is not set use unit value from db
$returnArray[$i]['unit'] = (!$unitSet) ? $row['UNIT'] : $returnArray[$i]['unit'];
// Reverse and set plot array
$returnArray[$i]['data'] = array_reverse($plotArray);
$i++;
}
// Close DB Connection
unset($db);
// Return the array and encode to JSON
echo json_encode($returnArray);
/* * ****************************************
* ***************FUNCTIONS******************
* **************************************** */
// Query function to get data from FHEM dbLog Database
function getData($device, $reading, $timestampStart, $timestampEnd, $maxCount, $db) {
global $timestampColumn;
global $valueColumn;
global $unitColumn;
global $readingColumn;
global $deviceColumn;
global $logTable;
$datetimeStart = date('Y-m-d H:i:s', $timestampStart);
$datetimeEnd = date('Y-m-d H:i:s', $timestampEnd);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbQuery = 'SELECT ' . $timestampColumn . ', ' . $valueColumn . ', ' . $unitColumn . ' FROM ' . $logTable . ' WHERE ' . $deviceColumn . '=:device AND ' . $readingColumn . '=:reading AND ' . $timestampColumn . ' BETWEEN :timeRangeStart AND :timeRangeEnd ORDER BY ' . $timestampColumn . ' DESC LIMIT 0,:count';
// Execute query and return fetched rows
$fetchedRows = executeDbQuery($db, $dbQuery, $device, $reading, $datetimeStart, $datetimeEnd, $maxCount);
return $fetchedRows;
}
// Execute DB query
function executeDbQuery($db, $query, $device, $reading, $timestampStart, $timestampEnd, $maxCount) {
try {
$stmt = $db->prepare($query);
$stmt->bindValue(':device', $device, PDO::PARAM_STR);
$stmt->bindValue(':reading', $reading, PDO::PARAM_STR);
$stmt->bindValue(':timeRangeStart', $timestampStart);
$stmt->bindValue(':timeRangeEnd', $timestampEnd);
$stmt->bindValue(':count', $maxCount);
$stmt->execute();
} catch (PDOException $pe) {
returnError($pe->getMessage());
}
return $stmt;
}
// Return script errors as JSON Data to display them in SmartVISU
function returnError($error) {
$errorReturn = array('error' => '[dbPlot.widget]: ' . $error);
echo json_encode($errorReturn);
exit;
}