diff options
| -rw-r--r-- | .gitignore | 4 | ||||
| -rwxr-xr-x | db-create.rb | 24 | ||||
| -rwxr-xr-x | db-create.sh | 5 | ||||
| -rwxr-xr-x | db-from-log.rb | 64 | ||||
| -rwxr-xr-x | db-from-log.sh | 5 | ||||
| -rwxr-xr-x | db-query.rb | 14 | ||||
| -rwxr-xr-x | kwh.rb | 35 | ||||
| -rw-r--r-- | mqtt-receiver.py | 27 | ||||
| -rwxr-xr-x | power-logger | 44 |
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 @@ -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 |