9 files changed, 20 insertions, 163 deletions
diff --git a/config/init.d/power-logger b/config/init.d/power-logger
index f1ac7a7..e50d080 100755
--- a/config/init.d/power-logger
+++ b/config/init.d/power-logger
@@ -8,32 +8,24 @@
 # Short-Description: power-logger
 ### END INIT INFO
 
-export SUFFIX=$(date +'%Y-%m-%d-%H:%M:%S')
+export SUFFIX=$(date +'%Y-%m-%dT%H:%M:%S')
 export USER=pks
 export GROUP=pks
-export DIR=/home/$USER/mqtt_client/
+export DIR=/usr/local/src/power_logger
+export GEM_HOME=/home/$USER/.local/lib/ruby
+export GEM_PATH=/home/$USER/.local/lib/ruby
 
 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
+    su - $USER -c "mkdir -p $DIR/log $DIR/db"
+    su - $USER -c "$DIR/power-logger.rb -d $DIR/db/power.db -l $DIR/log/power.$SUFFIX.gz" &
+    echo $! > /run/power-logger.pid
     ;;
   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
+    kill $(cat /run/power-logger.pid)
+    rm -f /run/power-logger.pid
     ;;
   *)
     echo "Usage: /etc/init.d/power-logger {start|stop}"
diff --git a/db-create.rb b/db-create.rb
deleted file mode 100755
index a9bf771..0000000
--- a/db-create.rb
+++ /dev/null
@@ -1,24 +0,0 @@
-#!/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
deleted file mode 100755
index cb11626..0000000
--- a/db-create.sh
+++ /dev/null
@@ -1,5 +0,0 @@
-#!/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
deleted file mode 100755
index dd4d379..0000000
--- a/db-from-log.rb
+++ /dev/null
@@ -1,64 +0,0 @@
-#!/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
deleted file mode 100755
index 75a316a..0000000
--- a/db-from-log.sh
+++ /dev/null
@@ -1,5 +0,0 @@
-#!/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
deleted file mode 100755
index 3a3fa70..0000000
--- a/db-query.rb
+++ /dev/null
@@ -1,14 +0,0 @@
-#!/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/mqtt-receiver.py b/mqtt-receiver.py
deleted file mode 100644
index 3d6cd8a..0000000
--- a/mqtt-receiver.py
+++ /dev/null
@@ -1,27 +0,0 @@
-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.rb b/power-logger
index 827451f..827451f 100755
--- a/power-logger.rb
+++ b/power-logger
diff --git a/power-meter b/power-meter
index 9ffd7c6..4e05af5 100755
--- a/power-meter
+++ b/power-meter
@@ -5,24 +5,28 @@ require 'sqlite3'
 def main
   db = SQLite3::Database.new ARGV[0]
   devices =  db.execute "select distinct device_location_primary, device_location_secondary FROM power"
+  puts devices.to_s
+  exit
   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
+  start_date = Date.new(2021,01,01).to_time.to_i
+  end_date = Date.new(2021,01,30).to_time.to_i
   totals = {}
   totals.default = 0.0
   devices.sort_by{|i| i[0] }.each { |device|
+    puts "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"
+    exit
     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]}"
+      #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
+      #puts "#{device.join '/'}: #{kwh.round 0} kW/h"
+      #puts
       totals[device[0]] += kwh
     end
   }
-  puts "=== TOTAL: #{totals.values.inject(:+).round 0} kW/h ==="
+  puts "TOTAL for #{start_date} - #{end_date}: #{totals.values.inject(:+).round 0} kW/h"
   puts "\nBy Room"
   puts "-------"
   totals.each_key { |k|