Öffnungszeiten der Fenster mit Hilfe von DbRep ermitteln

[peter_w]

Hallo zusammen,

ich wollte mal ermitteln wie lange denn hier die Fenster am Tag auf sind. Die Aufgabe hat dann doch etwas mehr Zeit benötigt als gedacht. Hier mein Lösungsweg:

Voraussetzung:

-Alle Fensterkontakte heißen: "?_Fensterkontakt"
-Bei mir sind die von Homematic mit dem Subtyp: "threeStateSensor" https://wiki.fhem.de/wiki/HM-Sec-SCo_T%C3%BCr-Fensterkontakt,_optisch
-Der Status der Sensoren "open" und "close" landet in der Datenbank, man muß den "nur" auswerten  .

1. Schritt:

Mit einem Notify device kann ich darauf reagieren wenn ein Fenster geschlossen wird (alle Definitionen von Devices mache ich hier mit  Raw definition https://wiki.fhem.de/wiki/Import_von_Code_Snippets

Code Auswählen Erweitern

defmod WindowClosedNty notify .*_Fensterkontakt:closed { if($NAME =~ /Fensterkontakt/ && $EVENT =~ /closed/ ) {SwitchEvent($NAME,"dummy",$EVENT)}}
attr WindowClosedNty alias Starte Ermittlung der Fenster Öffnungszeiten
attr WindowClosedNty comment Wird verwendet um die aktuelle Öffnungszeit der Fenster aus der Datenbank zu ziehen\
die im Reading "OpenTime" abgelegt wird.
attr WindowClosedNty room 40_Server

Die if Abfrage im Notify behebt ein Problem bei den Fensterkontakten die mit einem Thermostat gepeert sind. Der Thermostat wird auch Informiert und so kam es zu mehrfach Events weil die Einfache Abfrage auch in so einem Fall feuerte.

Im Notify wird die Funktion SwitchEvent($NAME,"dummy",$EVENT) aufgerufen (eigentlich wird nur $NAME benötigt, "dummy" und $EVENT kann man sich sparen)
Die habe ich in eine Datei ausgelagert. Die Datei schaut wie folgt aus:

Code Auswählen Erweitern

############################################################################
# $Id: myUtilsTemplate.pm 7570 2015-01-14 18:31:44Z rudolfkoenig $
#

package main;

use strict;
use warnings;
use POSIX;
use Time::Local;

sub myOpenTimeUtils_Initialize($$)
{
  my ($hash) = @_;
}

##########################################################################################################
#  This function is used to start an SQL query for the given device "$name" and check
#  how much time the device was in the state  defined in $usedVal1 today.
#  The query is done by a DbRep device.
# 
#  In this case the name of the DbRep device is "Rep.powerOnTime"
#  The following attributes are needed:
#     attr Rep.powerOnTime sqlResultFormat sline
#     attr Rep.powerOnTime userExitFn SwitchQueryResult
#  SwitchQueryResult is the function below which gets the result of the query
##########################################################################################################
sub SwitchEvent($$$)
{
my ($name,$reading,$value) = @_;

Log3 $name , 5,"SwitchEvent called with: $name $reading $value";

# the following values ha to be enclosed by ""
# to do this the escape symbol \ is used
my $usedReading = "\"state\"";    # name of the reading which holds the state which shall be checked
my $usedVal1 = "\"open\"";        #    text in the reading which shall start the time measuerement
my $usedVal2 = "\"closed\"";      #    text in the reading which stops the time measurement
my $device = "\"$name\"";         # name of the device which stzate shall be checked

my $dbRepDevice = "Rep.powerOnTime";  # name of the DbRep device

# The query will check for the current day the cumulated time difference between $usedVal1 and $usedVal2
# the example here is a homemetic threeStateSensor (window contact) and the result of the query is
# the cumulated time which the window was open (in seconds) and the name of the device whic is
# used by the function SwitchQueryResult()
# Example:  AZ_Fensterkontakt|433.2
my $sql = "SET \@topen = NULL,\@closed = NULL, \@popen = NULL;;".
              " SELECT  DEVICE ,SUM(TIMEDIFF(tclosed, topen)) AS duration FROM (".
  " SELECT TIMESTAMP, VALUE, DEVICE,".
      " \@topen   := \@popen AS topen,".
      " \@closed  := IF(VALUE = ${usedVal2},  TIMESTAMP, NULL) AS tclosed,".
  " \@popen   := IF(VALUE = ${usedVal1}, TIMESTAMP, NULL) AS prev_open".
  " FROM history WHERE".
              " DATE(TIMESTAMP) = CURDATE() AND".
  " DEVICE = ${device} AND".
  " READING = ${usedReading} AND ".
          " (VALUE = ${usedVal1} OR VALUE = ${usedVal2})".
  " ORDER BY  TIMESTAMP".
  " ) AS tmp";
Log3 $name , 5,"SwitchEvent start query: ${sql}";

fhem("set ${dbRepDevice} sqlCmd ${sql}"); # start the query now. Result will be processed in the function SwitchQueryResult() below

return;
}

##########################################################################################################
# This function will be called from the DbRep device if the attribute
# attr Name_of_your_DbRep_device userExitFn SwitchQueryResult is set.
# This function create a new reading "OpenTime" at the device for which the query is done and
# set the cumulated time difference in seconds to the reading.
##########################################################################################################
sub SwitchQueryResult($$$)
{
my ($name,$reading,$value) = @_;  # $name is the name of the DbRep device which is calling this function
my $hash = $defs{$name};

Log3 $name , 5, "SwitchQueryResult has been called with: $name,$reading,$value.";

# DbRep calls this function several times with different informations.
# if $reading is SqlResult, $value will hold the result of the query
if ($reading eq "SqlResult")
{
Log3 $name , 5, "SwitchQueryResult SqlResult detected.";
my $MyResult = ReadingsVal($name,"SqlResult","-");        # get the result from DbRep
my ($mydevname,$myvalue) = split('\|',$MyResult);         # split the result in two parts
if (!defined $myvalue || $myvalue eq '' )
{
   $myvalue = 0;
}
Log3 $name , 5, "SwitchQueryResult splitted result: $mydevname $myvalue";

fhem("setreading $mydevname OpenTime $myvalue");    # set the reading "OpenTime" with the time in seconds ($myvalue)

}

return;
}


1;

Die Funktion

Code Auswählen Erweitern

SwitchEvent() bekommt u.A. den Namen des Gerätes das den Event ausgelöst hat übergeben. Damit startet sie eine SQL Abfrage für das Gerät in der geprüft wird wie viel Zeit (in Sekunden) für das übergebene Gerät zwischen dem Zustand "open" und "closed" verbracht wurde. Die Abfrage wird über DbRep gemacht. Das  dafür nötige Gerät schaut so bei mir aus:

Code Auswählen Erweitern

defmod Rep.powerOnTime DbRep logdb
attr Rep.powerOnTime aggregation no
attr Rep.powerOnTime comment Öffnungszeit der Fenster ermitteln
attr Rep.powerOnTime devStateIcon connected:10px-kreis-gelb .*disconnect:10px-kreis-rot .*done:10px-kreis-gruen
attr Rep.powerOnTime reading state
attr Rep.powerOnTime room 40_Server
attr Rep.powerOnTime sqlResultFormat sline
attr Rep.powerOnTime userExitFn SwitchQueryResult

Ganz wichtig ist die Zeile attr Rep.powerOnTime userExitFn SwitchQueryResult. Hier ist die Funktion SwitchQueryResult() angegben, die nach erfolgreicher Bearbeitung der query aufgerufen wird.
Diese Funktion steckt ebenfalls in der oben gezeigten Datei.

Die Funktion SwitchQueryResult() zieht das Ergebnis der query aus dem DbRep Device und trägt die Gefundene Zeit in Sekunden in ein neues Reading des jeweiligen Fensterkontaktes ein.
So kann mna dann z.B. Am Fensterkontakt mit dem Namen "AZ_Fensterkontakt" das Reading OpenTime 1892.000000 finden.
Bei mir liefern die Kontakte auch ohne Änderung ca. 1x pro Stunde ihren Status ab, so werden die Zeiten also zyklisch aktualisiert und auch über den Tageswechsel dann zurückgesetzt.

Vielen Dank nochmal an Heiko der fix DbRep erweitert hat damit das mit der SQL query so funktionierte (siehe https://forum.fhem.de/index.php?topic=96082.0)

Anregungen und Ideen werden gerne entgegen genommen.

Gruß
   Peter

[DS_Starter]

Hallo Peter,

danke, ich bereite es mal für das Wiki auf.
Kleiner Hinweis ... ändere bitte RepDB mal in DbRep.

Grüße
Heiko

[DS_Starter]

Hallo Peter,

habe deine Beschreibung etwas verändert in das Wiki übernommen.
Insbesondere ist eine  Veränderung des Notify berücksichtigt falls die DB im asynchronen Modus betrieben wird.

Schau mal hier:
https://wiki.fhem.de/wiki/DbRep_-_Reporting_und_Management_von_DbLog-Datenbankinhalten#.C3.96ffnungszeiten_von_Fenster.2FT.C3.BCren_mit_aus_der_Datenbank_ermitteln

Grüße und schönes WE,
Heiko

[peter_w]

Hallo Heiko,

da sind ja noch ein paar schöne Sachen bei die ich auch übernehmen werde. Super.

Was mir noch aufgefallen ist:
Du nennst das Reading "LastOpenTime" aber im Kommentar zu SwitchQueryResult() steht in der Überschrift über der Funktion, neben dem Aufruf der das Reading setzt und in der Beschreibung unter der Funktion : "OpenTime"

Also nur Schönheit  .

Sonst ist mir nichts aufgefallen.

Gruß
     Peter

[DS_Starter]

Ah ja, danke 
Habs geändert.

Grüße,
Heiko