2022-11-22HEADmaster

6 files changed, 160 insertions, 73 deletions

diff --git a/config/logrotate/power-logger b/config/logrotate/power-logger
new file mode 100644
index 0000000..089db74
--- /dev/null
+++ b/config/logrotate/power-logger
@@ -0,0 +1,12 @@
+/var/log/power_logger/logger.log {
+	rotate 7
+	daily
+	compress
+	size 100k
+	nocreate
+	missingok
+	postrotate
+		kill -HUP `cat /run/power_logger/logger.pid`
+		rm -f /run/power_logger/receiver.pid
+	endscript
+}
diff --git a/config/syslog/51power-logger.conf b/config/syslog/51power-logger.conf
new file mode 100644
index 0000000..c734814
--- /dev/null
+++ b/config/syslog/51power-logger.conf
@@ -0,0 +1 @@
+:programname, isequal, "power-logger" /var/log/power_logger/logger.log
diff --git a/config/systemd/power-logger.service b/config/systemd/power-logger.service
new file mode 100644
index 0000000..8774f96
--- /dev/null
+++ b/config/systemd/power-logger.service
@@ -0,0 +1,25 @@
+[Unit]
+Description=power-logger
+After=network.target
+StartLimitIntervalSec=0
+
+[Service]
+Type=simple
+Restart=always
+RestartSec=1
+User=power-logger
+Group=power-logger
+Environment=GEM_HOME=/usr/local/lib/ruby/gems
+StandardOutput=syslog
+StandardError=syslog
+SyslogIdentifier=power-logger
+PIDFile=/run/power_logger/logger.pid
+PermissionsStartOnly=true
+ExecStartPre=mkdir -p /run/power_logger /var/log/power_logger /usr/local/share/power_logger
+ExecStartPre=rm -f /run/power_logger/receiver.pid
+ExecStartPre=chown -R power-logger:power-logger /run/power_logger /var/log/power_logger /usr/local/share/power_logger
+ExecStopPost=rm -f /run/power_logger/logger.pid
+ExecStart=/usr/local/src/power_logger/power-logger
+
+[Install]
+WantedBy=multi-user.target
diff --git a/power-logger b/power-logger
index 827451f..dd94bb3 100755
--- a/power-logger
+++ b/power-logger
@@ -1,52 +1,59 @@
 #!/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/"
+$LOAD_PATH << '/usr/local/lib/ruby/gems/mqtt-0.5.0/lib/'
+$LOAD_PATH << '/usr/local/lib/ruby/gems/optimist-3.0.1/lib/'
+
+$stdout.sync = true
+$stderr.sync = true
 
 require 'json'
 require 'mqtt'
 require 'optimist'
 require 'sqlite3'
 require 'time'
-require 'zipf'
+
+$handles = {
+  'baker'        => 'fridge',
+  'batum'        => 'microwave',
+  'bembry'       => 'modem+printer',
+  'crowder'      => 'desk-2',
+  'davis'        => 'hifi',
+  'doncic'       => 'desk-1',
+  'fournier'     => 'air_washer',
+  'gibson'       => 'dishwasher',
+  'gooden'       => 'couch',
+  'grant'        => 'washing_machine',
+  'kukoc'        => 'rack',
+  'nogueira'     => 'kitchen_tech',
+  'world-peace'  => 'kettle+toaster'
+}
 
 def shutdown
-  STDERR.write "Shutting down ...\n"
+  STDERR.write 'Shutting down ...'
   $db.close
-  $logfile.close
-  STDERR.write "Done!\n"
+  STDERR.write " done!\n"
   exit
 end
 
-Signal.trap("INT") { shutdown }
-Signal.trap("TERM") { shutdown }
+Signal.trap('INT') { shutdown }
+Signal.trap('TERM') { shutdown }
 
-def db_open_or_new filename
+def db_open_or_create filename
   if File.exists? filename
     $db = SQLite3::Database.open filename
   else
     $db = SQLite3::Database.new filename
     $db.execute <<-SQL
-            create table power_log(
+            create table power(
              id INTEGER PRIMARY KEY,
              timestamp DATETIME,
-             device_name TEXT,
- 	 				   device_location TEXT,
+             device TEXT,
+ 	 				   handle 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
 
@@ -57,45 +64,55 @@ def db_execute sql, data
       break
     rescue SQLite3::BusyException
       STDERR.write "DB busy, skipping data point '#{d.to_s}'\n"
-      sleep 1
+      sleep rand
     end
   }
 end
 
+def db_insert device, handle, data
+  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, handle, total, total_start_time) VALUES(?,?,?,?,?)", \
+    [timestamp, device, handle, data['ENERGY']['Total'], total_start_time]
+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
+  parts = topic.split '/'
+  _, device, _ = parts
+  handle = $handles[device]
+
+  return device, handle
 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]
+def check_and_maybe_create_pidfile filename
+  if File.exists? filename
+    File.open filename, 'r' do |f|
+      STDERR.write "Possibly already running as PID #{f.read}, exiting"
+    end
+  else
+    File.open filename, 'w' do |f|
+      puts "Running as PID #{Process.pid}"
+      f.write "#{Process.pid}\n"
+    end
   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'
+    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, :default => '/usr/local/share/power_logger/power.db', :short => '-d'
+    opt :pidfile, 'PID file',              :type => :string, :default => '/run/power_logger/logger.pid', :short => '-P'
   end
 
-  db_open_or_new options[:db]
+  check_and_maybe_create_pidfile options[:pidfile]
 
-  $logfile = WriteFile.new options[:log]
+  db_open_or_create options[:db]
 
   $mqtt_client = MQTT::Client.connect(
     :host => options[:host],
@@ -105,13 +122,19 @@ def main
 
   $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"
+      logged = false
+      if topic.end_with? '/SENSOR'
+        data = parse_message message
+        if data.keys.include? 'ENERGY'
+          puts "[logged] topic=#{topic} message=#{message}"
+          device, handle = parse_topic topic
+          db_insert device, handle, data
+          logged = true
+        end
+      end
+      if not logged then STDERR.write "[ignored] topic=#{topic} message=#{message}\n" end
     rescue => exception
-      $logfile.write "[Ignoring]\t#{topic}\t#{message}\n"
+      STDERR.write "Exception: #{exception}\n"
     end
   end
 
diff --git a/power-meter b/power-meter
index 4e05af5..013da25 100755
--- a/power-meter
+++ b/power-meter
@@ -1,37 +1,54 @@
 #!/usr/bin/env ruby
 
+# FIXME
+$LOAD_PATH << '/usr/local/lib/ruby/2.5.0/gems/mqtt-0.5.0/lib/'
+$LOAD_PATH << '/usr/local/lib/ruby/2.5.0/gems/optimist-3.0.1/lib/'
+
+require 'optimist'
 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(2021,01,01).to_time.to_i
-  end_date = Date.new(2021,01,30).to_time.to_i
+  options = Optimist::options do
+    opt :db,   "SQLite3 database file",                      :type => :string, :required => true,  :short => '-d'
+    opt :from, "Calculate power consumption from date/time", :type => :string, :required => false, :short => '-f'
+    opt :to,   "Calculate power consumption to date/time",   :type => :string, :required => false, :short => '-t'
+  end
+
+  db = SQLite3::Database.new options[:db]
+
+  today = Date.today
+  if ! options[:from]
+    from = Date.new(today.year, today.month, 1).to_time
+  else
+    from = Time.parse(options[:from])
+  end
+  if ! options[:to]
+    to = Time.parse "#{today.year}-#{today.month}-#{today.day}T23:59:59"
+  else
+    to = Time.parse(options[:to])
+  end
+
+  handles =  db.execute "SELECT DISTINCT handle FROM power"
+  handles.map! { |i| i[0] }.sort!
+  max_handle_size = handles.map { |i| i.size }.max
+
   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]}"
-      kwh = last[0][0] - first[0][0]
-      #puts "#{device.join '/'}: #{kwh.round 0} kW/h"
-      #puts
-      totals[device[0]] += kwh
+
+  handles.each { |handle|
+    first = db.execute "SELECT total FROM power WHERE timestamp >= #{from.to_i} AND timestamp <= #{to.to_i} AND handle='#{handle}' ORDER BY timestamp ASC LIMIT 1"
+    last = db.execute "SELECT total FROM power WHERE timestamp >= #{from.to_i} AND timestamp <= #{to.to_i} AND handle='#{handle}' ORDER BY timestamp DESC LIMIT 1"
+    if first.size == 1 and last.size == 1
+      consumption = last.first.first - first.first.first
+      totals[handle] += consumption
     end
   }
-  puts "TOTAL for #{start_date} - #{end_date}: #{totals.values.inject(:+).round 0} kW/h"
-  puts "\nBy Room"
-  puts "-------"
-  totals.each_key { |k|
-    puts "  #{k}: #{totals[k].round 0} kW/h"
-  }
+
+  puts "Power consumption from #{from} to #{to} = #{totals.values.inject(:+).round 1} kW/h"
+  puts "\nBy handle"
+  puts "---------"
+  totals.each_key { |handle| puts " - #{handle.ljust(max_handle_size)} = #{totals[handle].round(2).to_s.ljust(4)} kW/h" }
+
   db.close
 end
 
diff --git a/scripts/install.sh b/scripts/install.sh
new file mode 100755
index 0000000..1f28629
--- /dev/null
+++ b/scripts/install.sh
@@ -0,0 +1,9 @@
+#!/usr/bin/env zsh
+
+sudo useradd -M -s /bin/bash power-logger
+sudo mkdir -p /usr/local/share/power_logger
+sudo mkdir -p /var/log/power_logger
+sudo mkdir -p /run/power_logger
+sudo chown -R power-logger:power-logger /usr/local/share/power_logger /var/log/power_logger /run/power_logger
+sudo chmod 775 /usr/local/share/power_logger /var/log/power_logger /run/power_logger
+gem install --install-dir=/usr/local/lib/ruby/2.5.0/ --bindir=/usr/local/bin/ process sqlite3