From 899ba88b76a2f01b1bab87fbb960c2277435c521 Mon Sep 17 00:00:00 2001 From: pks Date: Sun, 16 May 2021 18:22:52 +0200 Subject: restart --- config/init.d/power-logger | 44 ++++++++++++++++ kwh.rb | 35 ------------- power-logger | 44 ---------------- power-logger.rb | 122 +++++++++++++++++++++++++++++++++++++++++++++ power-meter | 35 +++++++++++++ 5 files changed, 201 insertions(+), 79 deletions(-) create mode 100755 config/init.d/power-logger delete mode 100755 kwh.rb delete mode 100755 power-logger create mode 100755 power-logger.rb create mode 100755 power-meter diff --git a/config/init.d/power-logger b/config/init.d/power-logger new file mode 100755 index 0000000..f1ac7a7 --- /dev/null +++ b/config/init.d/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 diff --git a/kwh.rb b/kwh.rb deleted file mode 100755 index 9ffd7c6..0000000 --- a/kwh.rb +++ /dev/null @@ -1,35 +0,0 @@ -#!/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/power-logger b/power-logger deleted file mode 100755 index f1ac7a7..0000000 --- a/power-logger +++ /dev/null @@ -1,44 +0,0 @@ -#!/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 diff --git a/power-logger.rb b/power-logger.rb new file mode 100755 index 0000000..827451f --- /dev/null +++ b/power-logger.rb @@ -0,0 +1,122 @@ +#!/usr/bin/env ruby + +# FIXME +$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/json-2.3.1/lib/" +$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/mqtt-0.5.0/lib/" +$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/optimist-3.0.1/lib/" +$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/sqlite3-1.4.2/lib/" +$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/zipf-1.2.6/lib/" + +require 'json' +require 'mqtt' +require 'optimist' +require 'sqlite3' +require 'time' +require 'zipf' + +def shutdown + STDERR.write "Shutting down ...\n" + $db.close + $logfile.close + STDERR.write "Done!\n" + exit +end + +Signal.trap("INT") { shutdown } +Signal.trap("TERM") { shutdown } + +def db_open_or_new filename + if File.exists? filename + $db = SQLite3::Database.open filename + else + $db = SQLite3::Database.new filename + $db.execute <<-SQL + create table power_log( + id INTEGER PRIMARY KEY, + timestamp DATETIME, + device_name TEXT, + device_location TEXT, + total FLOAT, + total_start_time DATETIME + ); + SQL + $db.execute <<-SQL + create table power_meter( + id INTEGER PRIMARY KEY, + timestamp DATETIME, + total FLOAT + ); + SQL + end +end + +def db_execute sql, data + 3.times { + begin + $db.execute sql, data + break + rescue SQLite3::BusyException + STDERR.write "DB busy, skipping data point '#{d.to_s}'\n" + sleep 1 + end + } +end + +def parse_topic topic + parts = topic.split "/" + else + _, device_name, _, device_location_1, device_location_2, _ = parts + end + device_location = [device_location_1, device_location_2] + return device_name, device_location +end + +def parse_message message + return JSON.parse message +end + +def insert_power 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 \ + "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 + options = Optimist::options do + opt :host, "MQTT hostname", :type => :string, :default => 'localhost', :short => '-h' + opt :port, "MQTT port", :type => :int, :default => 1883, :short => '-p' + opt :db, "SQLite3 database file", :type => :string, :required => true, :short => '-d' + opt :log, "Logfile", :type => :string, :required => true, :short => '-l' + end + + db_open_or_new options[:db] + + $logfile = WriteFile.new options[:log] + + $mqtt_client = MQTT::Client.connect( + :host => options[:host], + :port => options[:port], + :ssl => false) + $mqtt_client.subscribe '#' + + $mqtt_client.get do |topic,message| + begin + puts "#{topic}\t#{message} + #device_name, device_location = parse_topic topic + #data = parse_message message + #insert_power device_name, device_location, data + #$logfile.write "#{topic}\t#{message}\n" + rescue => exception + $logfile.write "[Ignoring]\t#{topic}\t#{message}\n" + end + end + + shutdown +end + + +main diff --git a/power-meter b/power-meter new file mode 100755 index 0000000..9ffd7c6 --- /dev/null +++ b/power-meter @@ -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 -- cgit v1.2.3