ctucx.git: nixfiles

machines/lollo/smarthome/mqtt-webui: new powermeter-archive

diff --git a/machines/lollo/smarthome/influxdb2.nix b/machines/lollo/smarthome/influxdb2.nix
@@ -32,4 +32,77 @@
     };
   };
 
+  systemd.services.power-history = {
+    startAt = "0/2:00:00";
+    path = with pkgs; [ curl jq mosquitto ];
+    script = ''
+      QUERY=$(cat <<-END
+      import "timezone"
+      import "date"
+      import "math"
+
+      option location = {zone: "Europe/Berlin", offset: 0h}
+      option header = false
+
+      date_start = date.truncate(t: date.sub(from: now(), d: 3y), unit: 1d)
+      date_stop = date.truncate(t: now(), unit: 1d)
+
+      data =
+          from(bucket: "mqttData")
+              |> range(start: date_start, stop: date_stop)
+              |> filter(fn: (r) => r.topic == "sdm2mqtt/leah" and r._field == "import")
+              |> drop(columns: ["_field", "_measurement", "topic", "host"])
+
+      //daily aggregation
+      data
+        // Windows and aggregates the data
+        |> range(start: -30d, stop: now())
+        |> aggregateWindow(every: duration(v: 1d), fn: max, createEmpty: false, timeSrc: "_start")
+        |> difference()
+        |> drop(columns: ["_start", "_stop"])
+        |> map(fn: (r) => ({r with _value: (math.round(x: r._value * 100.0) / 100.0)}))
+        |> map(fn: (r) => ({r with _time: string(v: date.year(t: r._time))+"-"+string(v: date.month(t: r._time))+"-"+string(v: date.monthDay(t: r._time))}))
+        |> yield(name: "d")
+
+      //weekly aggregation
+      data
+        // Windows and aggregates the data
+        |> aggregateWindow(every: duration(v: 1w), fn: max, createEmpty: false, offset: -3d, timeSrc: "_start")
+        |> difference()
+        |> drop(columns: ["_start", "_stop"])
+        |> map(fn: (r) => ({r with _value: (math.round(x: r._value * 100.0) / 100.0)}))
+        |> map(fn: (r) => ({r with _time: string(v: date.year(t: r._time))+"-"+string(v: date.week(t: r._time))}))
+        |> yield(name: "w")
+
+      //monthly aggregation
+      data
+        // Windows and aggregates the data
+        |> aggregateWindow(every: duration(v: 1mo), fn: max, createEmpty: false, timeSrc: "_start")
+        |> difference()
+        |> drop(columns: ["_start", "_stop"])
+        |> map(fn: (r) => ({r with _value: (math.round(x: r._value * 100.0) / 100.0)}))
+        |> map(fn: (r) => ({r with _time: string(v: date.year(t: r._time))+"-"+string(v: date.month(t: r._time))}))
+        |> yield(name: "m")
+
+      //yearly aggregation
+      data
+        // Windows and aggregates the data
+        |> aggregateWindow(every: duration(v: 1y), fn: max, createEmpty: false, timeSrc: "_start")
+        |> difference()
+        |> drop(columns: ["_start", "_stop"])
+        |> map(fn: (r) => ({r with _value: (math.round(x: r._value * 100.0) / 100.0)}))
+        |> map(fn: (r) => ({r with _time: string(v: date.year(t: r._time))}))
+        |> yield(name: "y")
+
+      END
+      )
+
+      REQUEST_BODY=`echo '{"dialect": { "header": false}}' | jq --arg q "''${QUERY}" '.query=$q'`
+      RESPONSE=`curl -X POST 'https://influx.home.ctu.cx/api/v2/query?org=leah' -sS -H 'Accept:application/csv' -H 'Authorization: Token ZrTCG9n8ow-KC_x_HhwJD3VwWS208051WczuIa-3i2M3qmZETdth_XIi5FUTEVKmMXlQ015ujWLRZLjBNbINxQ==' -H 'Content-type:application/json' -d "''${REQUEST_BODY}"`
+      MESSAGE=`echo "''${RESPONSE}" | tr -s '\r' '\n' | jq --slurp --raw-input -c 'split("\n") | map(select(length > 0)) | .[1:] | map(split(",")) | map([.[1],.[3],.[4]])'`
+
+      mosquitto_pub -h '127.0.0.1' -r -t 'sdm2mqtt/leah/history' -m "''${MESSAGE}"
+    '';
+  };
+
 }

diff --git a/machines/lollo/smarthome/mqtt-webui/config.nix b/machines/lollo/smarthome/mqtt-webui/config.nix
@@ -281,53 +281,6 @@ in {
     }
 
     {
-      id       = "powermeterarchive";
-      title    = "Archive";
-      sections = [
-        {
-          items = [
-            {
-              type  = "html";
-              topic = "grafana";
-              html  = ''<iframe src="https://grafana.ctu.cx/d-solo/FRDYqjEGz/smarthome-influx?orgId=1&from=now-24h&refresh=5m&panelId=30" frameborder="0"></iframe>'';
-            }
-          ];
-        }
-
-        {
-          items = [
-            {
-              type  = "html";
-              topic = "grafana";
-              html  = ''<iframe src="https://grafana.ctu.cx/d-solo/FRDYqjEGz/smarthome-influx?orgId=1&from=now-24h&refresh=5m&panelId=34" frameborder="0"></iframe>'';
-            }
-          ];
-        }
-
-        {
-          items = [
-            {
-              type  = "html";
-              topic = "grafana";
-              html  = ''<iframe src="https://grafana.ctu.cx/d-solo/FRDYqjEGz/smarthome-influx?orgId=1&from=now-24h&refresh=5m&panelId=32" frameborder="0"></iframe>'';
-            }
-          ];
-        }
-
-        {
-          items = [
-            {
-              type  = "html";
-              topic = "grafana";
-              html  = ''<iframe src="https://grafana.ctu.cx/d-solo/FRDYqjEGz/smarthome-influx?orgId=1&from=now-24h&refresh=5m&panelId=33" frameborder="0"></iframe>'';
-            }
-          ];
-
-        }
-      ];
-    }
-
-    {
       id       = "departures";
       title    = "Departures";
       sections = [

@@ -374,6 +327,46 @@ in {
       ];
     }
 
+   {
+      id       = "powermeterarchive";
+      title    = "Archive";
+      sections = [
+        {
+          items = [
+            {
+              type      = "html";
+              topic     = "sdm2mqtt/leah/history";
+              html      = "<div class=\"loader\"></div>";
+              transform = ''
+                let output = "";
+
+                output += '<table><tr><th>Date</th><th>Import</th></tr>';
+                message.forEach((data) => {
+                  if ( data[0] == "w" ) { output += '<tr><td>'+data[1]+'</td><td>'+data[2]+' kWh<td></tr>'; }
+                });
+                output += '</table>';
+
+                output += '<table><tr><th>Date</th><th>Import</th></tr>';
+                message.forEach((data) => {
+                  if ( data[0] == "m" ) { output += '<tr><td>'+data[1]+'</td><td>'+data[2]+' kWh<td></tr>'; }
+                });
+                output += '</table>';
+
+                output += '<table><tr><th>Date</th><th>Import</th></tr>';
+                message.forEach((data) => {
+                  if ( data[0] == "y" ) { output += '<tr><td>'+data[1]+'</td><td>'+data[2]+' kWh<td></tr>'; }
+                });
+                output += '</table>';
+
+
+                return output;
+            '';
+            }
+          ];
+        }
+      ];
+    }
+
   ];
 
 } 
\ No newline at end of file