So after a slugfest getting OpenHAB to do my latest bidding, I find myself wondering (even though I have a working solution) if maybe I encountered so many problems because I was doing something (or somethings) the hard way. I present my solution for retrospective power reports, and am asking aloud if anyone has any better/cleaner ways to solve the problem. (Jump to PUTTING IT ALL TOGETHER to skip the backstory and jump straight to the code.)
THE MAIN PROBLEM: I need OpenHAB to calculate the amount of power I’ve used for an electric billing cycle, which will have ended anywhere from 1-15 days ago, and started between 29 to 39 days before that. Start date and run range must be configurable through the classic UI; manually editing a text file is not considered a solution.
Recently (this past year) my electric company switched my house to a smart meter. Using the “trust but verify” principle, I want to verify that this new meter (and their billing system) isn’t wildly mis-billing me. If they say I used 1040 KWH in a month and my meter says I used 1030 KWH in a month, whatever. But if they say I used 2590 KWH and my meter says I used 1030 KWH, then we got problems dude.
THE ROOT CAUSE: My electric company (which rhymes exactly with Commonwealth Edison) has a billing department run by sociopaths that still live in the mid-1970s. For some unfathomable reason (even though I have a smart meter that’s read remotely by computer!), my billing cycle varies between 29 and 34 days per month. This billing cycle varies month-to-month dictated by company holidays and neighborhood meter reading schedules which are not available to the customer. Oh, the amount of shade I threw at them on facebook for that!
“I can’t find any month on my calendar that has 34 days in it. You mean to tell me someone has to manually go and tell your computer to read my meter each month!?? No? Then why is the meter reading schedule dependent at all on company holidays? You expect me to believe that you give your computers time off to observe Christmas!??”
The criticism was not received enthusiastically.
Having exhausted my possibilities with their 1970s-era billing department, I realized I could already solve the problem. I have z-wave electric meters on my main feeds logging both power and KWH used every few minutes into mysql; it shouldn’t be too hard to whip up something to do what I need, right? I also have a bunch of other z-wave devices that report cumulative KWH usage, so I should be able to figure out how much those are using as well. Finally, since I know how much I roughly pay per KWH, after I figure out how much power each device used it shouldn’t be hard to figure out how much each cost to run per month, right? Finally, it’d be really great if the whole shebang was emailed so I could read it at my leisure.
…I don’t for ask much. 
PROBLEM #1: There appears to be no way to input dates to OpenHAB.
I didn’t need to input a date so much as I needed some way to indicate a date range. (“How much power did I use between Jan 1 and Feb 3rd?”)
Solution: Dimmers! Stop laughing; outside of switches dimmers are pretty much the only way I can see to get user input into OpenHAB. So I made two dimmers; one representing the number of days ago the billing period began, and one representing the number of days ago the billing period ended. I also made a textual representation of the date so the user has confirmation after moving the dimmer what date they’re picking. (Anytime the dimmer gets updated I recalculate the associated date string.)
Dimmer VIRTUAL_START_POWER_REPORT_DAYS_AGO "Number of days ago power report should start [%d]" (Group_PowerKWH)
String VIRTUAL_START_POWER_REPORT_DATE "Power report start date [%s]" (Group_PowerKWH)
Dimmer VIRTUAL_STOP_POWER_REPORT_DAYS_AGO "Number of days ago power report should stop [%d]" (Group_PowerKWH)
String VIRTUAL_STOP_POWER_REPORT_DATE "Power report stop date [%s]" (Group_PowerKWH)
PROBLEM #2: There appears to be no way to store information while iterating over members of a group.
For simplicity sake, I made any device that reads KWH that I wanted a report on a member of group Group_KWHReport . So now I needed to iterate over the members of that group, figure out the power usage at the end, figure out the power usage at the beginning, subtract, and I should have the number I need. Add the info for that device to the email and then go onto the next item.
…which doesn’t sound crazy, except OpenHAB says you can’t store things in variables between iterations of group members. If you make a string variable outside of your group iterator and try to change it within the group iterator, OpenHAB will throw a fit saying you “cannot refer to a non-final variable from within a closure”. (That’s designer’s phrase; runtime error should be something similar.) The closure in this case being the group iterator itself. Ugh. So we need a place to stick a string that doesn’t look like a string, that will persist even though we’re iterating over elements in the group.
Solution: Make the email body an openhab element rather than a variable. (If all you have is a hammer, everything looks like a nail.) So instead of writing something like this:
var String Email_Body = "First line of email.\r\n"
Group_PowerKWHReport.members.forEach[item|
Email_Body = Email_Body + "Another line of the email body. Item: " + item.name + "\r\n"
]
You have to do something like this… item definition:
String VIRTUAL_POWER_REPORT "Dummy string that holds the actual report"
usage:
sendCommand(VIRTUAL_POWER_REPORT,"First line of email.\r\n")
Group_PowerKWHReport.members.forEach[item|
sendCommand(VIRTUAL_POWER_REPORT, VIRTUAL_POWER_REPORT.state + "Another line of the email body. item: " + item.name + "\r\n")
]
This was the point where I really began to question if I was really doing things the right way. Nevertheless I had a working solution within my grasp, so I persevered on.
PROBLEM #3: Not every meter has complete data.
This was really a problem of my own making; if I was simply after my overall power usage then I could keep tweaking the dimmers until I found my usable date range, errors be damned. But since I was now iterating over a group of items, I needed a better solution.
Solution: If you get a null back from historicstate, go back one less day.
while (item.historicState(now.minusDays(DaysStart.intValue))) == null)
{
// if we don't have any historic data for the start date, go forward day by day until we have data
logInfo("openhab", "power report: no valid info " + DaysStart + " days ago. Going to next day.")
DaysStart = DaysStart - 1 // go back one fewer day
DaysStartOffset = DaysStartOffset + 1 // counter for how many days we skipped
}
PROBLEM #4: Negative overall KWH
Similar to problem #3 but different cause. In a nutshell, if you’ve reset one of your KWH meters at a different point than all the others, and your report’s date range spans that time frame, you can wind up with a negative KWH for amount of power used. (e.g. meter was previously at 800 KWH, you reset it on the tenth day of the billing cycle, and then you only use 70 KWH after that. 70-800 = -730KWH. Whoops!)
Solution is nearly identical to problem #3; if you subtract the start from the end and get a negative number, go back one fewer day for the start.
PUTTING IT ALL TOGETHER
To make it all happen, first you need to throw any item that’s a KWH meter into its own group. (Mine is called Group_PowerKWHReport .) Every item also must store persistence data in a queryable database, so mysql is fine, but storing in logfiles is a no-go. I think rrd4j should work as long as you’re not going back in time farther than you’re storing precise data, but I haven’t tried it as I’m a mysql kind of guy.
Finally, you need the following item definitions put somewhere on a sitemap so you can manipulate and see them through the UI:
Dimmer VIRTUAL_START_POWER_REPORT_DAYS_AGO "Number of days ago power report should start [%d]" (Group_PowerKWH)
String VIRTUAL_START_POWER_REPORT_DATE "Power report start date [%s]" (Group_PowerKWH)
Dimmer VIRTUAL_STOP_POWER_REPORT_DAYS_AGO "Number of days ago power report should stop [%d]" (Group_PowerKWH)
String VIRTUAL_STOP_POWER_REPORT_DATE "Power report stop date [%s]" (Group_PowerKWH)
Switch VIRTUAL_POWER_REPORT_GO "GO! Generate power usage report NOW! (...and mail it)" (Group_PowerKWH)
String VIRTUAL_POWER_REPORT "Dummy string that holds the actual report"
And now what you’ve all been waiting for, the meat & potatoes, the rules:
val String Mail_Destination = "test@test.com" // where to send mail
val Number Cost_Per_KWH = 0.135 // dollars per KWH
rule "recalculate power report start date"
when
Item VIRTUAL_START_POWER_REPORT_DAYS_AGO changed
then
{
var Number MyDays = 0
MyDays = VIRTUAL_START_POWER_REPORT_DAYS_AGO.state as DecimalType
logInfo("openhab","number of days ago for report to start:" + MyDays.intValue)
sendCommand(VIRTUAL_START_POWER_REPORT_DATE,now.minusDays(MyDays.intValue).minusHours(now.getHourOfDay).minusMinutes(now.getMinuteOfDay%60).toLocalDateTime.toString)
}
end
rule "recalculate power report end date"
when
Item VIRTUAL_STOP_POWER_REPORT_DAYS_AGO changed
then
{
var Number MyDays = 0
MyDays = VIRTUAL_STOP_POWER_REPORT_DAYS_AGO.state as DecimalType
logInfo("openhab","number of days ago for report to stop:" + MyDays.intValue)
sendCommand(VIRTUAL_STOP_POWER_REPORT_DATE,now.minusDays(MyDays.intValue).minusHours(now.getHourOfDay).minusMinutes(now.getMinuteOfDay%60).toLocalDateTime.toString)
}
end
rule "generate and mail power report"
when
Item VIRTUAL_POWER_REPORT_GO changed to ON
then
{
sendCommand(VIRTUAL_POWER_REPORT_GO, OFF)
logInfo("openhab","power report button pushed")
sendCommand(VIRTUAL_POWER_REPORT, "\r\nOpenHAB power usage report for " + ((VIRTUAL_START_POWER_REPORT_DAYS_AGO.state as DecimalType) - (VIRTUAL_STOP_POWER_REPORT_DAYS_AGO.state as DecimalType) ) + " day")
if (((VIRTUAL_START_POWER_REPORT_DAYS_AGO.state as DecimalType) - (VIRTUAL_STOP_POWER_REPORT_DAYS_AGO.state as DecimalType) ) > 1) sendCommand(VIRTUAL_POWER_REPORT, VIRTUAL_POWER_REPORT.state + "s")
sendCommand(VIRTUAL_POWER_REPORT, VIRTUAL_POWER_REPORT.state + ", ending with " + VIRTUAL_STOP_POWER_REPORT_DATE.state + "\r\n")
Group_PowerKWHReport.members.forEach[item|
var Number KWHStart = 0
var Number KWHStop = 0
var Number KWHDifference = 0
var Number DaysStart = 0
var Number DaysStartOffset = 0
var Number DaysStop = 0
var String FriendlyName = item.name
DaysStart = VIRTUAL_START_POWER_REPORT_DAYS_AGO.state as DecimalType
DaysStop = VIRTUAL_STOP_POWER_REPORT_DAYS_AGO.state as DecimalType
switch FriendlyName as String
{
case "BASEMENT_NETWORK_RACK_KWH": {
FriendlyName = "Cable modem" }
case "BEDROOM_FAN_KWH": {
FriendlyName = "Bedroom fan" }
// couldn't find a way to grab the item description text string while iterating, so change the raw name to something friendly here. lots of cases omitted for brevity.
}
if (DaysStop > DaysStart)
{
// logInfo("openhab","power report: end date is before start date. setting start date to end date - 1.")
DaysStart = DaysStop + 1 // but counterintuitively that's actually "one more day ago" than the stop date
}
// it seems as though if mysql reports an error (such as table doesn't exist), that the entire routine is aborted for that item on the first mysql historic data access.
// this fits our use case well, and it does not appear to abort the sequence for the rest of the members, so... GREAT!
// but if we get wonky behavior, that's where I'd start looking first. for now though it just works, so letting sleeping dogs lie.
if (item.historicState(now.minusDays(DaysStop.intValue).minusHours(now.getHourOfDay).minusMinutes(now.getMinuteOfDay%60)) == null)
{
logInfo("openhab", "power report: report end date for " + FriendlyName + " doesn't have valid data; days before that can't have valid data. nothing to do.")
}
else
{
logInfo("openhab","Power report for " + FriendlyName)
KWHStop = item.historicState(now.minusDays(DaysStop.intValue).minusHours(now.getHourOfDay).minusMinutes(now.getMinuteOfDay%60)).state as DecimalType
logInfo("openhab","power report: got end value")
sendCommand(VIRTUAL_POWER_REPORT,VIRTUAL_POWER_REPORT.state + "\r\n" + FriendlyName + ": ")
while (item.historicState(now.minusDays(DaysStart.intValue).minusHours(now.getHourOfDay).minusMinutes(now.getMinuteOfDay%60)) == null && DaysStart > DaysStop)
{
// if we don't have any historic data for the start date, go forward day by day until either we have data or we overrun the stop date
// logInfo("openhab", "power report: no valid info " + DaysStart + " days ago. Going to next day.")
DaysStart = DaysStart - 1 // go back one fewer day
DaysStartOffset = DaysStartOffset + 1 // increment counter for how many days we skipped
}
while (item.historicState(now.minusDays(DaysStart.intValue).minusHours(now.getHourOfDay).minusMinutes(now.getMinuteOfDay%60)).state as DecimalType > KWHStop && DaysStart > DaysStop)
{
// the meter was reset between the start and end points, resulting in a negative KWH.
// go forward until either that's not true, or we overrun the end date
// logInfo("openhab","power report: negative KWH (meter reset) for " + FriendlyName + ", going forward a day")
DaysStart = DaysStart - 1 // go back one fewer day
DaysStartOffset = DaysStartOffset + 1 // inc counter for how many days we skipped ahead
}
if (DaysStartOffset > 0)
{
logInfo("openhab", "power report: skipped forward " + DaysStartOffset + " to get to valid data for " + FriendlyName)
}
KWHStart = item.historicState(now.minusDays(DaysStart.intValue).minusHours(now.getHourOfDay).minusMinutes(now.getMinuteOfDay%60)).state as DecimalType
logInfo("openhab","power report: got start value for " + FriendlyName)
KWHDifference = KWHStop - KWHStart
// logInfo("openhab","power report, " + FriendlyName + " start: " + KWHStart + " stop:" + KWHStop + " difference:" + KWHDifference)
var String FormattedNumber = String::format("%1$.3f",KWHDifference)
sendCommand(VIRTUAL_POWER_REPORT,VIRTUAL_POWER_REPORT.state + FormattedNumber + " KWH")
if (DaysStartOffset > 0)
{
sendCommand(VIRTUAL_POWER_REPORT, VIRTUAL_POWER_REPORT.state + " (" + (DaysStart.intValue - DaysStop.intValue) + " day")
if (DaysStart.intValue - DaysStop.intValue > 1) sendCommand(VIRTUAL_POWER_REPORT, VIRTUAL_POWER_REPORT.state + "s")
sendCommand(VIRTUAL_POWER_REPORT, VIRTUAL_POWER_REPORT.state + ")")
}
FormattedNumber = String::format ("%1$.2f",(KWHDifference * Cost_Per_KWH))
sendCommand(VIRTUAL_POWER_REPORT, VIRTUAL_POWER_REPORT.state + " [$" + FormattedNumber + "]")
}
] // close forEach
sendCommand(VIRTUAL_POWER_REPORT, VIRTUAL_POWER_REPORT.state + "\r\n\r\nEnd of OpenHAB power report.\r\n")
sendMail(Mail_Destination,"OpenHAB Power Report",VIRTUAL_POWER_REPORT.state.toString) // mail it!
logInfo("openhab","End of power report")
}
end
To use it, adjust the dimmers (ignoring the % signs; can’t find a way to get rid of them) to adjust the start and end days for your report. The date picked will show up in the corresponding string so you know what you’ll be getting. (Might need to manually refresh if you’re on OH 1.8.3 to see it update.) Finally, kick the generate power usage report button to on, and it’ll email out a report like so:
OpenHAB power usage report for 30 days, ending with 2017-01-18T00:00:13.497
Cable modem: 1.571 KWH (11 days) [$0.21]
Bedroom fan: 0.000 KWH (11 days) [$0.00]
Bedroom TV: 3.836 KWH (12 days) [$0.52]
Front bush christmas lights: 2.436 KWH (11 days) [$0.33]
Amplifiers: 1.648 KWH (11 days) [$0.22]
Christmas tree: 1.247 KWH (11 days) [$0.17]
Utility room lights: 0.264 KWH (11 days) [$0.04]
Basement freezer: 16.610 KWH (11 days) [$2.24]
Basement fridge: 4.274 KWH (11 days) [$0.58]
Auxillary HVAC: 3.074 KWH (11 days) [$0.41]
Kitchen oven & dishwasher: 10.998 KWH (11 days) [$1.48]
Kitchen microwave & fridge: 42.116 KWH (11 days) [$5.69]
Bedrooms and attic: 45.569 KWH (11 days) [$6.15]
Washer, dryer & server: 50.818 KWH (11 days) [$6.86]
Primary air conditioning: 2.927 KWH (11 days) [$0.40]
Living & dining rooms: 34.067 KWH [$4.60]
Furnace: 71.181 KWH [$9.61]
House TOTAL: 319.890 KWH (11 days) [$43.19]
End of OpenHAB power report.
Simple, eh?
You made it all the way through – kudos to you!
I’m wondering, despite working perfectly, if there were any better ways to do some of this code. When the report is generating the CPU spikes pretty hard, and that’s bad in principle. There are other things I don’t like, for example, stuffing the email body into a string object while I’m iterating over group members. That just feels wrong, so looking to see if anyone has any better suggestions to avoid that. Or suggestions on how to improve anything else, for that matter!
(had similar experiences with my local electric company)
