init

9 files changed, 222 insertions, 0 deletions

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..2a8933f
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,4 @@
+*.db
+*.gz
+env/
+log/
diff --git a/db-create.rb b/db-create.rb
new file mode 100755
index 0000000..a9bf771
--- /dev/null
+++ b/db-create.rb
@@ -0,0 +1,24 @@
+#!/usr/bin/env ruby
+
+require 'sqlite3'
+
+def main
+  db = SQLite3::Database.new ARGV[0]
+
+  db.execute <<-SQL
+          create table power(
+           id INTEGER PRIMARY KEY,
+           timestamp DATETIME,
+           device_name TEXT,
+					 device_location_primary TEXT,
+					 device_location_secondary TEXT,
+           total FLOAT,
+					 total_start_time DATETIME
+         );
+        SQL
+
+  db.close
+end
+
+
+main
diff --git a/db-create.sh b/db-create.sh
new file mode 100755
index 0000000..cb11626
--- /dev/null
+++ b/db-create.sh
@@ -0,0 +1,5 @@
+#!/usr/bin/env zsh
+
+export GEM_HOME=/home/pks/.local/lib/ruby
+export GEM_PATH=/home/pks/.local/lib/ruby
+$(which ruby) $(dirname $0:A)/db-create.rb $1
diff --git a/db-from-log.rb b/db-from-log.rb
new file mode 100755
index 0000000..dd4d379
--- /dev/null
+++ b/db-from-log.rb
@@ -0,0 +1,64 @@
+#!/usr/bin/env ruby
+
+require 'json'
+require 'sqlite3'
+require 'time'
+
+def db_execute db, s, d
+  begin
+    db.execute(s, d)
+    rescue SQLite3::BusyException
+      sleep 3
+      begin
+        db.execute(s, d)
+      rescue SQLite3::BusyException
+        STDERR.write "DB busy, skipping data point '#{d.to_s}'\n"
+      end
+  end
+end
+
+def parse_log_line line
+  _, topic, data = line.split "\t"
+  topic_parts = topic.split "/"
+  if topic_parts.size == 5 # old format, no hostname in topic
+    _, _, device_location_1, device_location_2, _ = topic_parts
+    device_name = ""
+  else
+    _, device_name, _, device_location_1, device_location_2, _ = topic_parts
+  end
+  device_location = [device_location_1, device_location_2]
+  data.gsub!("'", '"')
+  data = JSON.parse data
+
+  return device_name, device_location, data
+end
+
+def insert_power db, device_name, device_location, data
+  if data.has_key? "ENERGY"
+    timestamp = Time.parse(data["Time"]).utc.to_i
+    total_start_time = Time.parse(data["ENERGY"]["TotalStartTime"]).utc.to_i
+    db_execute db, \
+      "INSERT INTO power(timestamp, device_name, device_location_primary, device_location_secondary, total, total_start_time) VALUES(?,?,?,?,?,?)", \
+      [timestamp, device_name, device_location[0], device_location[1], data["ENERGY"]["Total"], total_start_time]
+  end
+end
+
+def main
+  db = SQLite3::Database.open ARGV[0]
+  while line = STDIN.gets
+    begin
+      device_name, device_location, data = parse_log_line line
+      insert_power db, device_name, device_location, data
+    rescue
+      STDERR.write "Cannot insert log entry '#{line.strip}'\n"
+    end
+    if File.exist? "#{File.expand_path(File.dirname(__FILE__))}/stop"
+      STDERR.write "Stopping loop\n"
+      break
+    end
+  end
+  db.close
+end
+
+
+main
diff --git a/db-from-log.sh b/db-from-log.sh
new file mode 100755
index 0000000..75a316a
--- /dev/null
+++ b/db-from-log.sh
@@ -0,0 +1,5 @@
+#!/usr/bin/env zsh
+
+export GEM_HOME=/home/pks/.local/lib/ruby
+export GEM_PATH=/home/pks/.local/lib/ruby
+$(which ruby) $(dirname $0:A)/db-from-log.rb $1
diff --git a/db-query.rb b/db-query.rb
new file mode 100755
index 0000000..3a3fa70
--- /dev/null
+++ b/db-query.rb
@@ -0,0 +1,14 @@
+#!/usr/bin/env ruby
+
+require 'sqlite3'
+
+def main
+  db = SQLite3::Database.new ARGV[0]
+  db.execute("select * from power") do |result|
+    puts result.to_s
+  end
+  db.close
+end
+
+
+main
diff --git a/kwh.rb b/kwh.rb
new file mode 100755
index 0000000..9ffd7c6
--- /dev/null
+++ b/kwh.rb
@@ -0,0 +1,35 @@
+#!/usr/bin/env ruby
+
+require 'sqlite3'
+
+def main
+  db = SQLite3::Database.new ARGV[0]
+  devices =  db.execute "select distinct device_location_primary, device_location_secondary FROM power"
+  devices.reject! { |i| not ["office", "living_room", "guest_restroom", "kitchen", "bedroom_2"].include? i[0] }
+  start_date = Date.new(2020,9,01).to_time.to_i
+  end_date = Date.new(2020,9,30).to_time.to_i
+  totals = {}
+  totals.default = 0.0
+  devices.sort_by{|i| i[0] }.each { |device|
+    first = db.execute "select TOTAL, TIMESTAMP from power WHERE TIMESTAMP >= #{start_date} and TIMESTAMP <= #{end_date} AND DEVICE_LOCATION_PRIMARY = '#{device[0]}' AND DEVICE_LOCATION_SECONDARY = '#{device[1]}' ORDER BY TIMESTAMP ASC LIMIT 1"
+    last = db.execute "select TOTAL, TIMESTAMP from power WHERE TIMESTAMP >= #{start_date} and TIMESTAMP <= #{end_date} AND DEVICE_LOCATION_PRIMARY = '#{device[0]}' AND DEVICE_LOCATION_SECONDARY = '#{device[1]}' ORDER BY TIMESTAMP DESC LIMIT 1"
+    if first.size > 0 and last.size > 0
+      puts "#{Time.at(first[0][1])} --- #{Time.at(last[0][1])}"
+      puts "#{first[0][0]} ::: #{last[0][0]}"
+      kwh = last[0][0] - first[0][0]
+      puts "#{device.join '/'}: #{kwh.round 0} kW/h"
+      puts
+      totals[device[0]] += kwh
+    end
+  }
+  puts "=== TOTAL: #{totals.values.inject(:+).round 0} kW/h ==="
+  puts "\nBy Room"
+  puts "-------"
+  totals.each_key { |k|
+    puts "  #{k}: #{totals[k].round 0} kW/h"
+  }
+  db.close
+end
+
+
+main
diff --git a/mqtt-receiver.py b/mqtt-receiver.py
new file mode 100644
index 0000000..3d6cd8a
--- /dev/null
+++ b/mqtt-receiver.py
@@ -0,0 +1,27 @@
+import json
+import paho.mqtt.client as mqtt
+import sys
+
+
+def on_connect(client, userdata, flags, rc):
+    client.subscribe("#")  # Subscribe to all topics
+    sys.stderr.write("Connected with result code {}\n".format(rc))
+    sys.stderr.flush()
+
+
+def on_message(client, userdata, msg):
+    msg_as_str = msg.payload.decode('utf-8')
+    try:
+        print("{}\t{}\t{}".format(msg.timestamp, msg.topic, json.loads(msg_as_str)))
+        sys.stdout.flush()
+    except Exception as e:
+        sys.stderr.write("Cannot parse message: '{}'\n".format(msg_as_str))
+        sys.stderr.flush()
+
+
+if __name__ == "__main__":
+    client = mqtt.Client()
+    client.on_connect = on_connect
+    client.on_message = on_message
+    client.connect("localhost", 1883, 60)
+    client.loop_forever()
diff --git a/power-logger b/power-logger
new file mode 100755
index 0000000..f1ac7a7
--- /dev/null
+++ b/power-logger
@@ -0,0 +1,44 @@
+#!/bin/sh
+### BEGIN INIT INFO
+# Provides:          power-logger
+# Required-Start:    $all
+# Required-Stop:
+# Default-Start:     2 3 4 5
+# Default-Stop:
+# Short-Description: power-logger
+### END INIT INFO
+
+export SUFFIX=$(date +'%Y-%m-%d-%H:%M:%S')
+export USER=pks
+export GROUP=pks
+export DIR=/home/$USER/mqtt_client/
+
+case "$1" in
+  start)
+    echo "Starting power-logger"
+    rm -f $DIR/stop
+    if [ ! -f $DIR/power.db ]; then
+      runuser -l $USER -c "$DIR/db-create.sh $DIR/power.db"
+    fi
+    mkdir -p $DIR/log
+    chown -R $USER:$GROUP $DIR/log
+    runuser -l $USER -c "source $DIR/env/bin/activate && python $DIR/mqtt-receiver.py 2> >(cat > $DIR/log/power.$SUFFIX.err) | tee >(cat > $DIR/log/power.$SUFFIX.out) | $DIR/db-from-log.sh $DIR/power.db 2> >(cat > $DIR/log/power.db-from-log.$SUFFIX.err)" &
+    echo $! > /run/power-logger.pid2
+    ;;
+  stop)
+    echo "Stopping power-logger"
+    runuser -l $USER -c "touch $DIR/stop"
+    sleep 3
+    if [ -f /run/power-logger.pid2 ]; then
+      kill $(cat /run/power-logger.pid2) &>/dev/null
+      rm -f /run/power-logger.pid2
+    fi
+    rm -f $DIR/stop
+    ;;
+  *)
+    echo "Usage: /etc/init.d/power-logger {start|stop}"
+    exit 1
+    ;;
+esac
+
+exit 0