Xiaomi Aqara Cube mit NodeRed + Google Sheets

Cube + NodeRed + Google Sheets = Zeiterfassung Pro

Im PanOffice (Pandemie Office trifft’s eher als Home-Office) möchte man seine Arbeitszeit im Auge behalten und muss auf verschiedene Situationen „schnell“ reagieren. So war es nun der Wunsch von mir die Arbeitszeit zu erfassen – ohne ständig auf die Uhr zu schauen und Zeiten zu schreiben. Vorhandene Lösungen waren mir dann aber zu teuer 😉

Ich will also die aktuelle Aufgabe auf dem Würfel oben sehen und wenn sich was ändert den Würfel einfach auf die Aufgabe drehen. Ich habe da jetzt z. B. Freizeit, Arbeit, Meeting und Pause aktuell. 

Was der Würfel leider nicht erkennt ist es, wenn man ihn aufnimmt und auf eine andere Fläche stellt. Der Lagesensor reagiert dann nicht – man muss ihn richtig von A nach B kippen. Man kann den Würfel aber auf den Tisch stellen und schieben oder 2x klopfen. 🙂

Den Cube von Xiaomi habe ich bei EBay für 12,08€ gekauft – dauert nur rund 4 Wochen.

Den Cube habe ich über DeConz (ZigBee USB Stik) als Schalter eingebunden:

Dazu muss man nur den Deckel mit dem mitgelieferten Metallteil öffnen – das geht etwas schwer aber durchhalten – es klappt 🙂 Dann einfach den Link Button drücken und etwas Geduld haben.

Ganz wichtig zu wissen ist jetzt, dass DeConz das Gerät 2x publiziert. Im Node Red kann man die leider nicht unterscheiden:

Hier kann man nun leider nur probieren was man für Daten bekommt. Es gibt immer das „buttonevent“ und die gesture. Ein Gerät liefert Daten zur Lage und was mit dem Würfel gemacht wird und das zweite Gerät liefert Daten wenn das Gerät gedreht wird.

Würfelfelder

Darstellung im Web Phoscon

zur Lage

Es gibt folgende Gesten:

  • 1 wedeln
  • 2 in die Luft geworfen ==> button event 7008
  • 3 kippen
  • unbekannt
  • 5 status stehen/ schieben
  • 6 klopfen 2x

Das Buttonevent ist immer eine 4-stellige Zahl – interessant ist aber nur die 1. und 4. Stelle. so kommt z.B. die Zahl 1002 mit geste 3 => der Würfel zeigt nun die Fläche 1 und vorher war die Fläche 2 aktiv. 

Ein Sonderfall ist die Fläche 7. Schüttelt man den Würfel kommt 7007. Wirft man den Würfel in die Luft kommt 7008. Klopft man den Würfel 2x auf den Tisch (nicht in der Hand halten!!) kommt die aktuelle Fläche 2x  also z.b.  3003 oder 4004.

Nach dem Anschalten kommt 7000 (Ruhemodus).

Schiebt man den Würfel horizontal kommt zur Geste 5 das buttonevent  5000 oder z.B. 1000. Hier wird also die aktuelle Fläche (1. Stelle) geliefert und die vorherige Fläche ist 0.

Zur Drehung

wenn der Würfel auf dem Tisch liegt kann man ihn links und rechts drehen (z.b. für Helligkeit oder Lautstärke). Als Buttonevent erhält man die Angabe wie stark der Würfel gedreht wurde in Grad aber mit Faktor 100. Wenn der Wert 4324 kommt, bedeutet dies der Würfel wurde um 43,24° nach rechts gedreht. Ein negativer Wert bedeutet nach links.

Zusätzlich zeigt die Geste 7 (rechts) und 8(links) an in welche Richtung gedreht wurde.

 

Node Red

Im Node Red habe ich nun also 4 DeConz Adapter die mir von jedem Zauberwürfel den buttonevent und gesture liefert. Die Drehung nutze ich aktuell noch nicht.

Aus der Lage extrahiere ich durch simple Mathematik die aktuelle Fläche und die vorherige Fläche (letztes nutze ich auch noch nicht). Der Fläche habe ich für mich Aufgaben zugeordnet und bis auf die 7 nutze ich alle.

Node Red selbst schreibt über den node-red-contrib-google-sheets Adapter die Daten zu Google Tabellen. Das Einrichten ist etwas „tricky“ aber in der README erklärt. Was nicht drinnen steht, ist dass man in dem Projekt noch die API für Sheets (Tabellen) aktivieren muss. Das kann man aber nachträglich noch machen und die Debug Console zeigt hilfreiche Fehlermeldungen.

Die Payload Merge Funktion hier sammelt die Payload Objekte und gibt sie als vereintes Objekt nach einer Zeit X weiter. Also 1. Objekt lautet { aktuell: 1, vorheriges: 2} und das 2. Objekt {geste: 3} dann kommt am raus: { aktuell: 1, vorheriges: 2, geste: 3}. Node Red Code hier: payload merge.

 

Ich verwende in dem Adapter dann die „Append Row“ Funktion um eine neue Zeile anzufügen. Für mich reicht hier die aktuelle Fläche und die Uhrzeit mit sekunden. Folgt ein weiteres Event, weiß ich ja wie lange die andere Fläche aktiv war. 

Da beim einfügen der Zeile wird nicht einfach die letzte Zeile beschrieben, sondern wirklich eine Zeile eingefügt. Ich kann somit in der Tabelle keine Formeln in die z.B. 3. oder 4. Spalte setzen. Node Red muss die mit einfügen. Einerseits möchte ich aber das Ende in einer Zeile stehen haben und 2. die Dauer daraus berechnen. Für weitere Berechnungen benötige ich noch das Datum ohne Uhrzeit:

Dafür benötigt man noch 3 Formeln. Da wir nicht wissen in welcher Zeile wir sind benötigt man die Funktion INDIREKT mit RC-Notation.

Bevor man die Daten nun also an Excel senden kann muss man ein 2-Dimensionales Array erschaffen. Eine Liste von Zeilen also. Jedes Element ist eine Spalte. Formeln können direkt übergeben werden.

Hier wird nun jeweils das Start-Datum des Nächsten Events als Ende genommen. Wenn das da ist wird die Differenz berechnet und das Datum gebildet. 

In meinem Beispiel lass ich das Tablett noch die aktuelle Tätigkeit sprechen mittels Text-To-Speech was der Fully Kiosk Browser super per API kann.

Noch als Tipp: Nach dem Append to Row liefert der GSheet Adapter noch Informationen wo er die Zeile eingefügt hat. Damit kann man dann noch mehr machen 🙂

Den Node Red Code gibt es hier Zeiterfassung Node Red

Google Skills

Jetzt muss man das ganze ja noch in Tabelle / Excel auswerten was man da an Daten hat.

Zuerst erstellen wir eine Liste von den Tagen die erfasst wurden. Mit der Funktion UNIQUE bekomme ich alle Elemente und SORT zeigt mir diese in der Spalte noch an

=SORT(UNIQUE(Rohdaten!E2:E40035);1;TRUE

Zu dem Datum kann ich nun die Arbeitszeiten raussuchen mit einer SUMIFS Funktion. Da die nur UND Verknüpfungen kennen muss man jedes Event einzeln zählen. Daher hier mehrere SUMIFS addiert.

=IF(B2<>""; SUMIFS(Rohdaten!$D$2:$D$10033;Rohdaten!$E$2:$E$10033;B2;Rohdaten!$B$2:$B$10033;5)+SUMIFS(Rohdaten!$D$2:$D$10033;Rohdaten!$E$2:$E$10033;B2;Rohdaten!$B$2:$B$10033;6);"")

Alternativ kann man hier auch die Pausen berechnen – also das jeweilige Event.

Ich berechne aus dem Datum dann noch die Kalenderwoche und beginne das Spiel dann noch einmal und zähle zu der Woche (vorher ja zum Datum) die Stunden und kann so die Wochenarbeitszeit berechnen.

Die Funktion INDIREKT kennt Excel nun nicht. Ich stelle aber trotzdem mal hier den Export der Sheets Datei bereit. Da kann man die Formeln mal sehen und vielleicht auch wieder reimportieren nach Excel.

Zeiterfassung.xlsx

Veröffentlicht unter Heimautomatisierung | Verschlagwortet mit , , , , , , , , , , , | Schreib einen Kommentar

Proxmox LXC HDD vergrößern

Irgendwann kann es dann doch passieren, dass der LXC Container von Proxmox zu klein wird. Dann muss man die Festplatte vergrößern. Das geht aber etwas anders als bei einer VM. Proxmox bietet hier für LXC-Container den Befehl pct an.

Wie ihr seht, könnt ihr mit pct alles mögliche steuern – wäre sicher später mal noch interessant . 🙂

Ausgehend von einer Festplatte (rootfs) und der Container ID 101 würde unser Befehl also lauten:


1
pct resize 101 rootfs 20G

Wichtig: Vorher Backup machen

Veröffentlicht unter Linux-Welt | Verschlagwortet mit , , , , , | Schreib einen Kommentar

PiHole mit eigenem Hostnamen/ Subdomain

Für meine Dienste nutze ich traefik als Reverse-Proxy. Hier ist Let´s Encrypt von Haus aus mit dabei und ich muss mich um sowas nicht auch noch kümmern.

PiHole habe ich jetzt nicht mehr im Docker laufen, sondern auf dem ProxMox in einer VM. Beim Dockercontainer kann man den Virtuell Host einfach angeben – aber wenn man PiHole lokal installiert gibt es dafür leider keine Option. Hier muss man nun etwas Code anpassen

/var/www/html/pihole/index.php

Hier muss man nur den Text „pi.hole“ in Zeile 53 anpassen auf die Subdomain die man nutzen möchte. Die Hochkommas müssen bleiben. Und dann müsste das so aussehen:

Veröffentlicht unter Blogroll | Verschlagwortet mit , | Schreib einen Kommentar

IKEA TRÅDFRI Signalverstärker

Signalverstärker + DeConz (Conbee II von Dresden Elektronik)

Sollte die Reichweite der ZigBee Geräte, welche man an einem ConBee II von Dresden Elektronik nicht ausreicht, ist die erste Alternative natürlich ein USB Pigtail um den USB-Stik von dem Computer / Elektronik weg zu bekommen. Fast genauso teuer ist aber auch der Signalverstärker von Ikea. Mit gerademal 10€ erhält man ein USB Ladegerät und ein Aufsteck-Signalverstärker.

DeConz selbst kennt den Verstärker bereits – das UI hat dafür aber noch keine „Kategorie“. Deswegen wirkt ist die Einrichtung etwas merkwürdig. Zum Einrichten selbst brauch man aber einen passenden Gegenstand um den Signalverstärker resetten zu können.

Vorgehensweise:

  • Repeater in die Steckdose stecken (Anschalten)
  • im Phoscon unter Geräte –> Lichter –> „Neue Lichter verbinden“ anklicken.
  • Repeater resetten wie links gezeigt. Für den Reset muss man den Taster im Inneren ca. 5 Sekunden drücken und dann los lassen. Anschließend blinkt die LED.

Wenige Sekunden danach sollte der Repeater unter „Verbundene Lichter“ erscheinen.

Das ZigBee Netzwerk organisiert sich selbst, womit die Einrichtung damit erledigt ist. Im Deconz selbst kann man nun noch den VNC Mode aktivieren und sich das Netzwerk anschauen.

Ich muss jetzt noch die passende Steckdose finden, damit mein Multisensor 5 in der Garage funken kann 😊.

Veröffentlicht unter Heimautomatisierung, ZigBee | Verschlagwortet mit , , , , , , | Schreib einen Kommentar

YouTube Werbung blocken / DNS

Etwas Werbung ist ja ok, aber YouTube übertreibt es in letzter Zeit immer mehr. Also Grund genug Gegenmaßnahmen zu ergreifen 😈 

Bildergebnis für youtube ad blocker

In meinem Netzwerk läuft sowieso schon der pi-hole und der blockt auch schon gut was weg. In Apps und Webseiten vermisst man jedenfalls keine Werbung. Und neben schnelleren Ladezeiten fängt man sich eher weniger oft irgendwas ein 😉

Ich habe hier mal 3 Listen von GitHub Projekten die man einbinden kann und damit einen ganz guten Mix hat. Stand heute werden diese auch gepflegt 🙂

Wer noch weitere gute Projekte / Listen kennt –> Bitte in die Kommentare

Veröffentlicht unter Nützliches | Verschlagwortet mit , , , , , , | Schreib einen Kommentar