From 1123b0db81b6057e113f16a1ddea15c36b7b3d79 Mon Sep 17 00:00:00 2001
From: pks <pks@pks.rocks>
Date: Sun, 27 Sep 2020 20:55:18 +0200
Subject: init

---
 kwh.rb | 35 +++++++++++++++++++++++++++++++++++
 1 file changed, 35 insertions(+)
 create mode 100755 kwh.rb

(limited to 'kwh.rb')

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
-- 
cgit v1.2.3