…und hier kommt der gesamt-Code, der eine „stacked column“ chart herstellt (siehe Abbildung).
Alle Instanzen mit Variable „DigitalInput 2“ (also EnOcean-Bewegungsmelder) werden automatisch erfasst. Voraussetzung ist eigentlich nur das Logging mit PostgreSQL. Alles Weitere wird vom Script durchgeführt.
Achtung: das Script sollte als HTML-Element eingebunden werden (es hat zwar PHP drin, aber auch HTML und Javascript).
<?php
$RedirectEchoToFile = false;
//--------------------------------------------
// do not change anything below here
//redirects ECHO to file
/*
if ($RedirectEchoToFile == true)
{echo "output redirected to file";
unlink('PostgresCrosstabs.html');
$ob_file = fopen('PostgresCrosstabs.html','w');
ob_start('ob_file_callback');
echo "Start of PostgresCrosstabs file <br>";}
*/
$EnOceanArray = EnoceanCrossTab("DigitalInput 2", "boolean", "", "");
//echo "GoogleChartString: ". $EnOceanArray['GoogleChartString'] ;
$GoogleArray = PrepareGoogleChart($EnOceanArray);
EnoceanCrossTab("Analog Value 2", "integer", "", "");
EnoceanCrossTab("Analog Value 3", "integer", "", "");
//---------------------------------------------------
Function PrepareGoogleChart($EnOceanArray)
{
// var GoogleAddColumns adds columns to DataTable
$TimeUnit = "hours";
$GoogleAddColumns = "";
// var GoogleVisualizeAllColumns renders DataView1 view of DataTable
$VisualizeFirstColumn = ".setColumns([{calc: function(data, row) {return data.getFormattedValue(row, " . ColumnLabel($TimeUnit). "); }, type:'string'}";
$GoogleVisualizeAllColumns = "var dataView1 = new google.visualization.DataView(data);".chr(13)
."dataView1".$VisualizeFirstColumn;
$GoogleVisualizeCount = "var dataViewCount = new google.visualization.DataView(data);"
.chr(13)."dataViewCount".$VisualizeFirstColumn;
$GoogleVisualizeAvg = "var dataViewAvg = new google.visualization.DataView(data);"
.chr(13)."dataViewAvg".$VisualizeFirstColumn;
$GoogleVisualizeMinStdevStdevMax = "var dataViewMinStdevStdevMax = new google.visualization.DataView(data);"
.chr(13)."dataViewMinStdevStdevMax".$VisualizeFirstColumn;
$GoogleVisualizeScattergram = "var dataViewScattergram = new google.visualization.DataView(data);"
.chr(13)."dataViewScattergram.setColumns([";
//create strings for Google DataTable/DataView1 columns definition
for ($varIndex=0; $varIndex < ($EnOceanArray['NumberOfColumns']-1); $varIndex++)
{
Switch ($EnOceanArray['TableType'])
{
case "boolean":
$GoogleAddColumns .= "
dataMasterTable.addColumn('number', ".$EnOceanArray['CategoryArray'][$varIndex]. ", 'count$varIndex'); ";
break;
case "integer":
$GoogleAddColumns .= "
dataMasterTable.addColumn('number', ".$EnOceanArray['CategoryArray'][$varIndex]. " avg');
dataMasterTable.addColumn('number', ".$EnOceanArray['CategoryArray'][$varIndex]. " stddev+');
dataMasterTable.addColumn('number', ".$EnOceanArray['CategoryArray'][$varIndex]. " stddev-');
dataMasterTable.addColumn('number', ".$EnOceanArray['CategoryArray'][$varIndex]. " max');
dataMasterTable.addColumn('number', ".$EnOceanArray['CategoryArray'][$varIndex]. " min');
";
break;
case "float":
$GoogleAddColumns .= "
dataMasterTable.addColumn('number', ".$EnOceanArray['CategoryArray'][$varIndex]. " avg');
dataMasterTable.addColumn('number', ".$EnOceanArray['CategoryArray'][$varIndex]. " stddev+');
dataMasterTable.addColumn('number', ".$EnOceanArray['CategoryArray'][$varIndex]. " stddev-');
dataMasterTable.addColumn('number', ".$EnOceanArray['CategoryArray'][$varIndex]. " max');
dataMasterTable.addColumn('number', ".$EnOceanArray['CategoryArray'][$varIndex]. " min');
";
break;
case "string":
break;
}
$GoogleVisualizeAllColumns .= ", ". strval(($varIndex+1)*4+1).", ". strval(($varIndex+1)*4+2).", ". strval(($varIndex+1)*4+3).", ". strval(($varIndex+1)*4+4);
$GoogleVisualizeCount .= ", ". strval(($varIndex+1)*4+1);
$GoogleVisualizeAvg .= ", ". strval(($varIndex+1)*4+2);
$GoogleVisualizeMinStdevStdevMax .= ", ". strval(($varIndex+1)*4+4).", ". strval(($varIndex+1)*4+4).", ". strval(($varIndex+1)*4+3).", ". strval(($varIndex+1)*4+3);
$GoogleVisualizeScattergram .= strval(($varIndex+1)*4+2).", ";
}
//echo "Tabletype: ". $EnOceanArray['TableType'].chr(13)."<br>"."<br>";
//echo "GoogleAddColumns: ". $GoogleAddColumns.chr(13)."<br>"."<br>";
$GoogleVisualizeAllColumns .= "]);".chr(13);
$GoogleVisualizeCount .= "]);".chr(13);
$GoogleVisualizeAvg .= "]);".chr(13);
$GoogleVisualizeMinStdevStdevMax.= "]);".chr(13);
$GoogleVisualizeScattergram = substr($GoogleVisualizeScattergram, 0, -2) . "]);".chr(13);
VerboseEcho($GoogleAddColumns. "<br><br><br>".$GoogleVisualizeAllColumns);
//create dataset to be processed into google datatable
$DataForGoogleChart = $EnOceanArray['GoogleChartString'];
//echo $DataForGoogleChart;
$GoogleFormattedDataArray = array(
'GoogleAddColumns' => $GoogleAddColumns,
'GoogleAddRows' => $EnOceanArray['GoogleChartString']);
return $GoogleFormattedDataArray;
}
//-----------------------------------------------------
Function EnoceanCrossTab($SensorString, $TableType, $TimeUnit, $TimeStart) //sensorstring: e.g. DigitalInput 2
{
$dbconn = pg_connect("host=localhost
port=5432
dbname=IpsLogging
user=IPS
password=ips")
or die ('connection aborted: ' . pg_last_error().chr(13));
switch ($TableType)
{
case "boolean":
$IpsTable = "loggingdb_ips_boolean";
$AggregateFunctions = "COUNT(*) As value ";
$SensorColumn = 2;
break;
case "integer":
$IpsTable = "loggingdb_ips_integer";
$AggregateFunctions = "(COALESCE(AVG(ipsvalue),0)::NUMERIC(5,2)::text) ||'' '' ||
((AVG(ipsvalue)+STDDEV(ipsvalue))::NUMERIC(5,2))::text ||'' '' ||
((AVG(ipsvalue)-STDDEV(ipsvalue))::NUMERIC(5,2))::text ||'' '' ||
(MAX(ipsvalue)::NUMERIC(5,2))::text ||'' '' ||
(MIN(ipsvalue)::NUMERIC(5,2))::text ||'' ''
As value ";
$SensorColumn = 4;
break;
case "float":
$IpsTable = "loggingdb_ips_float";
$AggregateFunctions = "AVG(ipsvalue) As value ";
$SensorColumn = 3;
break;
case "string":
$IpsTable = "loggingdb_ips_string";
break;
}
// echo "type ".$IpsTable." <br>";
$SensorResult = GetSensorNames($SensorString, $TableType, "", "",0, 1);
$CategoryString = implode(", ", pg_fetch_all_columns($SensorResult,$SensorColumn));
$CategoryArray = pg_fetch_all_columns(GetSensorNames($SensorString, $TableType, "", "",0, 0),5); //array containing the name of each sensor
//Print_r ($CategoryArray);
$CrossTabEnoceanSensors =
"SELECT *
FROM crosstab
('SELECT
to_char(ipstimestamp, ''mon DD HH24h'') As row_name,
varid As category, "
.$AggregateFunctions
." FROM $IpsTable As log
JOIN ipsobjects_with_parent ips
ON log.varid = ips.objectid
WHERE (ips.objectname LIKE ''".$SensorString."'')
AND (log.ipsvalue != 0)
AND (ipstimestamp > (now()- ''2 days''::interval))
GROUP BY to_char(ipstimestamp, ''yyyy MM DD HH24h''), row_name, objectid, category
ORDER BY to_char(ipstimestamp, ''yyyy MM DD HH24h''), row_name, objectid, category',
'SELECT DISTINCT varid
FROM $IpsTable As log
JOIN ipsobjects_with_parent ips
ON log.varid = ips.objectid
WHERE (ips.objectname LIKE ''".$SensorString."'')
ORDER BY 1;'
)
As CountsPerHour(row_name text,"
.$CategoryString.")";
$QueryResult=pg_query ($dbconn, $CrossTabEnoceanSensors);
$CrosstabArray = pg_fetch_all($QueryResult);
//$table = good_query_table($CrosstabArray, $QueryResult, 0);
//BetterTable($QueryResult); //this functions outputs a nicely formatted crosstab!
if (!$QueryResult)
echo "PG Error: ". pg_last_error().chr(13);
// create bracketed Array of Arrays GoogleChartString
$GoogleChartStringOld ="["; //opening bracket inserted
$GoogleChartString ="["; //opening bracket inserted
while ($row = pg_fetch_row($QueryResult))
{
$GoogleChartStringOld = $GoogleChartStringOld . "[" .implode(", ", $row) . "], ";
$GoogleChartString .="[";
$index =0;
foreach ($row as $col_value) {
++$index;
if ($index==1)
{$GoogleChartString .= "'".$col_value."', ";}
else{
if($col_value == NULL) $col_value = 0;
$GoogleChartString .= $col_value.", ";
}
}
$GoogleChartString = substr($GoogleChartString,0,-2)."], ";
}
$GoogleChartStringOld = substr($GoogleChartStringOld,0,-2)."]";
$GoogleChartString = substr($GoogleChartString,0,-2)."]";
//echo $GoogleChartString ;
$EnOceanArray = array(
"GoogleChartString"=>$GoogleChartString,
"NumberOfColumns"=>pg_num_fields($QueryResult),
"CategoryArray"=>$CategoryArray,
"TableType"=>$TableType
);
return $EnOceanArray;
}
//---------------------------------------
function BetterTable($result)
{
$i = 0;
echo "<html>
<body>
<table>
<table class='BetterTable' border='1'>";
echo "<tr>";
echo '<td>Line #
</td>';
while ($i < pg_num_fields($result))
{
$fieldName = pg_field_name($result, $i);
echo '<td>' . $fieldName . '</td>';
$i = $i + 1;
}
echo '</tr>';
$i = 0;
while ($row = pg_fetch_row($result))
{
if ($i%2 == 0)
Echo "<tr bgcolor=\"#d0d0d0\" >";
else
Echo "<tr bgcolor=\"#eeeeee\">";
$fields = count($row);
$y = 0;
echo '<td>'.$i. '</td>';
while ($y < $fields)
{
$c_row = current($row);
echo '<td>'.$c_row . '</td>';
next($row);
$y = $y + 1;
}
echo '</tr>';
$i = $i + 1;
}
echo '</table><br><br></body></html>';
}
//----------------------------------------------------
Function GetSensorNames($SensorType, $TableType, $prefix, $postfix, $QueryType, $column)
{//TableType specifies the type of variable, and redirectes to the correct table.
//prefix and postfix are adapters returned for each row
//
//retrieve full names of EnOcean variables, including parent names
//QueryColumns specifies which query columns (see below)
$dbconn = pg_connect("host=localhost
port=5432
dbname=IpsLogging
user=IPS
password=ips")
or die ('connection aborted: ' . pg_last_error().chr(13));
switch ($TableType)
{
case "boolean":
$IpsTable = "loggingdb_ips_boolean";
break;
case "integer":
$IpsTable = "loggingdb_ips_integer";
break;
case "float":
$IpsTable = "loggingdb_ips_float";
break;
case "string":
$IpsTable = "loggingdb_ips_string";
break;
}
// echo "type ".$IpsTable." <br>";
if ($prefix!="") $prefix = $prefix." || ";
if ($postfix!="")$postfix = " || ". $postfix;
$GetSensorNamesString =
"SELECT
DISTINCT '\"' || varid ||'\"' || ' integer ' as integername,
varid as NakedVariable,
'\"' || ips.parentname || ' - ' || ips.objectname || ' (' || ips.objectid || ')\"' || ' integer ' AS fullname,
'\"' || ips.parentname || ' - ' || ips.objectname || ' (' || ips.objectid || ')\"' || ' numeric(5,2) ' AS fullnamenumeric,
'\"' || ips.parentname || ' - ' || ips.objectname || ' (' || ips.objectid || ')\"' || ' text ' AS fullnamestring,
'\"' || ips.parentname || ' - ' || ips.objectname || ' (' || ips.objectid || ')\"' AS fullnamestring,
$prefix varid $postfix as DecoratedVariable
FROM $IpsTable As log
JOIN ipsobjects_with_parent ips
ON log.varid = ips.objectid
WHERE (ips.objectname LIKE '$SensorType')
ORDER BY 1;
";
$QueryResult=pg_query ($dbconn, $GetSensorNamesString);
if (!$QueryResult)
echo "PG Error: ". pg_last_error().chr(13);
else
{return $QueryResult;}
}
//-----------------------------
Function ColumnLabel($TimeUnit)
{
switch ($TimeUnit)
{
case "minutes": $ColumnLabel= "0"; break;
case "hours": $ColumnLabel= "1"; break;
case "days": $ColumnLabel= "2"; break;
case "months": $ColumnLabel= "3"; break;
case "years": $ColumnLabel= "4"; break;}
return($ColumnLabel);
}
//--------------------------------------------
function VerboseEcho($Anything)
{global $Verbose;
if ($Verbose == true) Echo $Anything;}
?>
<html>
<link href="/CSS/Bellaria.css" rel="stylesheet" type="text/css" />
<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="/js/jquery-1.7.1.js"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.load('visualization', '1', {packages: ['gauge']});
google.load('visualization', '1', {packages:['table']});
google.setOnLoadCallback(drawAllCharts);
function drawAllCharts()
{
var dataMasterTable = new google.visualization.DataTable();
dataMasterTable.addColumn('string', 'hours');
<?php
echo $GoogleArray['GoogleAddColumns']
?>
//dataMasterTable.addRows([['jun 18 20h', 0, 0, 3, 0, 3, 0, 0, 0, 0, 7]]);
dataMasterTable.addRows(<?php echo $GoogleArray['GoogleAddRows'] ?>);
//create dynamic dataview for charts
//columns as specified in included php
// var DataviewsJSON = "Data" + data.toJSON() + <br>+"dataView1" + dataView1.toJSON() + <br> +"dataViewAvg" + dataViewAvg.toJSON() + <br> +"dataMax" + dataMax.toJSON() + <br> +"dataMin" + dataMin.toJSON() + <br> +"dataViewCount" + dataViewCount.toJSON() + <br>;
var dataToJson = dataMasterTable.toJSON();
//$('#debug_div').html("dataMasterTable.toJSON());
//$('#debug_div').html('test');
$('#ColumnChart_div' ).html('ColumnChart_div callback is working');
/*
$('#table_div' ).html('table_div callback is working');
$('#ipstable_div' ).html('ipstable_div callback is working');
$('#AreaChart' ).html('AreaChart callback is working');
$('#BarChart' ).html('BarChart callback is working');
$('#BubbleChart' ).html('BubbleChart callback is working');
$('#CandlestickChart' ).html('CandlestickChart callback is working');
$('#ComboChart' ).html('ComboChart callback is working');
$('#GageChart' ).html('GageChart callback is working');
$('#LineChart' ).html('LineChart callback is working');
$('#ScatterChart' ).html('ScatterChart callback is working');
$('#SteppedAreaChart' ).html('SteppedAreaChart callback is working');
$('#TableChart' ).html('TableChart callback is working');
*/
var ColumnChart = new google.visualization.ColumnChart(document.getElementById('ColumnChart_div'));
var options = {
chartArea: {width: '100%', height: '70%'},
//legend: {position: 'in'},
//titlePosition: 'in',
axisTitlesPosition: 'in',
hAxis: {textPosition: 'in'},
vAxis: {textPosition: 'in'},
width: 900,
height: 520,
isStacked: true,
pointSize: 7,
fontSize: 10,
backgroundColor: 'white',
bar:{groupWidth: '90%'},
hAxis:{slantedTextAngle: 90},
legend:{position: 'in', textStyle: {color: 'blue', fontSize: 12}}
};
ColumnChart.draw(dataMasterTable, options);
/*
var IpsBooleanTable = new google.visualization.Table(document.getElementById('ipstable_div'));
IpsBooleanTable.draw(dataMasterTable, {showRowNumber: true});
var Linechart = new google.visualization.LineChart(document.getElementById('LineChart_div'));
var options = {width: 800, height: 480, pointSize: 5};
Linechart.draw(dataViewAvg, options);
var AreaChart = new google.visualization.AreaChart (document.getElementById('AreaChart_div'));
var options = {width: 800, height: 480, pointSize: 5};
AreaChart.draw(dataViewAvg, options);
var BarChart = new google.visualization.BarChart (document.getElementById('BarChart_div'));
var options = {width: 800, height: 480, pointSize: 5};
BarChart.draw(dataViewAvg, options);
var BubbleChart = new google.visualization.BubbleChart(document.getElementById('BubbleChart_div'));
var options = {width: 800, height: 480, pointSize: 5};
BubbleChart.draw(dataViewAvg, options);
var CandlestickChart = new google.visualization.CandlestickChart (document.getElementById('CandlestickChart_div'));
var options = {width: 800, height: 480, pointSize: 5};
CandlestickChart.draw(dataViewMinStdevStdevMax, options);
var ComboChart = new google.visualization.ComboChart (document.getElementById('ComboChart_div'));
var options = {width: 800, height: 480, pointSize: 5};
ComboChart.draw(dataViewAvg, options);
var ScatterChart = new google.visualization.ScatterChart(document.getElementById('ScatterChart_div'));
var options = {width: 800, height: 480, pointSize: 5};
ScatterChart.draw(dataViewScattergram, options);
var SteppedAreaChart = new google.visualization.SteppedAreaChart(document.getElementById('SteppedAreaChart_div'));
var options = {width: 800, height: 480, pointSize: 5};
SteppedAreaChart.draw(dataViewAvg, options);
var GaugeChart = new google.visualization.Gauge(document.getElementById('GaugeChart_div'));
var options = {width: 800, height: 480, pointSize: 5};
GaugeChart.draw(dataViewAvg, options);
*/
}
</script>
</head>
<body>
<!--
<div id="debug_div" style="width:90%; text-align:left; min-height:180px; background-color: #CFC; border-color:red; border-style:solid border-width:1px"></div>
-->
<div id="ColumnChart_div" style="width:100%; min-height:250px; background-color:#CFC; border-color:green; border-style:solid; border-width:1px" >ColumnChart_div no data</div>
<!--
<div id="ipstable_div" style="width:90%; min-height:180px; background-color:#CFC; border-color:yellow; border-style:solid; border-width:1px">ipstable_div no data</div>
<div id="AreaChart_div" style="width:90%; min-height:180px; background-color:#CFC; border-color:red; border-style:solid; border-width:1px" >AreaChart_div no data</div>
<div id="BarChart_div" style="width:90%; min-height:180px; background-color:#CFC; border-color:red; border-style:solid; border-width:1px" >BarChart_div no data</div>
<div id="BubbleChart_div" style="width:90%; min-height:180px; background-color:#CFC; border-color:red; border-style:solid; border-width:1px" >BubbleChart_div no data</div>
<div id="CandlestickChart_div" style="width:90%; min-height:180px; background-color:#CFC; border-color:red; border-style:solid; border-width:1px" >CandlestickChart_div no data</div>
<div id="ComboChart_div" style="width:90%; min-height:180px; background-color:#CFC; border-color:red; border-style:solid; border-width:1px" >ComboChart_div no data</div>
<div id="ScatterChart_div" style="width:90%; min-height:180px; background-color:#CFC; border-color:red; border-style:solid; border-width:1px" >ScatterChart_div no data</div>
<div id="SteppedAreaChart_div" style="width:90%; min-height:180px; background-color:#CFC; border-color:red; border-style:solid; border-width:1px" >SteppedAreaChart_div no data</div>
<div id="GaugeChart_div" style="width:90%; min-height:180px; background-color:#CFC; border-color:red; border-style:solid; border-width:1px" >GaugeChart_div no data</div>
<div id="LineChart_div" style="width:90%; min-height:180px; background-color:#CFC; border-color:red; border-style:solid; border-width:1px" >LineChart_div no data</div>
-->
</body>
</html>
…und hier ist ein Beispiel für ein Candlestick-Chart. Die Enden der Striche zeigen Minima und Maxima für die jeweilige Zeitperiode, während die oberen und die unteren Kante der Boxen zeigen Mittel+StdDev bzw. Mittel-StdDev.
Die Tootip-Box wird von Javascript erzeugt während des Hovering mit der Maus auf einem beliebigen Element.
Alles mit GoogleCharts visualisiert anhand von postgres-geloggten IPS-Daten (hier: Helligkeitswerte von EnOcean-Sensoren während eines Tages)!
als demo: hier sieht man das langsame Sterben eines EnOcean-Sensors (#8620248). Von Tag zu Tag wird die Ladung des dunkelblauen sensors immer schwächer, bis er eines Tages abreisst und vom Radar verschwindet. Die Candlestick Graphik zeigt Tagesmaxima, -minima, und Standardabweichungen.
Die möglichen Differentialdiagnosen lauten: (1) zuwenig Licht oder (2) Photovoltaik-Panele kaputt oder (3) Kondensor hinüber. #1 scheint mir unwahrscheinlich weil in den letzten 3 Monaten immer genug Licht vorhanden war. Naja, der Sensor ist ja noch in Garantie…
