# HG changeset patch # User Daniel O'Connor # Date 1513944186 -3600 # Node ID a7e9775b33f6a07407fb973d6aac23587effd918 # Parent e29a8fcdbd573110e46f039777f9467ccf46cacd Add graph script based on AGL code (but better) diff -r e29a8fcdbd57 -r a7e9775b33f6 graph.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/graph.py Fri Dec 22 13:03:06 2017 +0100 @@ -0,0 +1,287 @@ +#!/usr/bin/env python + +import argparse +import datetime +import dateutil +import exceptions +import matplotlib +import matplotlib.dates +import numpy +import os +import requests +import sqlite3 +import tzlocal + +class Column(object): + def __init__(self, rowname, title, table, units, limits = (None, None), conv = None): + self.rowname = rowname + self.title = title + self.table = table + self.units = units + self.limits = limits + self.conv = None + +columns = [ + Column('main_voltage', 'Battery Voltage', 'eprolog', 'Vdc'), + Column('aux_voltage', 'Aux Voltage', 'eprolog', 'Vdc'), + Column('battery_curr', 'Battery Current', 'eprolog', 'A'), + Column('amp_hours', 'Battery Amp Hours', 'eprolog', 'Ah'), + Column('state_of_charge', 'State of Charge', 'eprolog', '%', (0, 100)), + Column('time_remaining', 'Time Remaining', 'eprolog', 'min'), + Column('battery_temp', 'Battery Temperature', 'eprolog', 'C'), + + Column('ac_act_power', 'Active Power', 'giantlog', 'W'), + Column('ac_app_power', 'Apparent Power', 'giantlog', 'W'), + Column('ac_frequency', 'AC Frequency', 'giantlog', 'Hz'), + Column('ac_volts', 'AC Voltage', 'giantlog', 'Vac'), + Column('batt_chr_curr', 'Discharge Current', 'giantlog', 'A'), + Column('batt_dis_curr', 'Charge Current', 'giantlog', 'A'), + Column('battery_cap', 'Battery Capacity', 'giantlog', '%', (0, 100)), + Column('battery_volts', 'Battery Voltage', 'giantlog', 'Vdc'), + Column('grid_frequency', 'Grid Frequency', 'giantlog', 'Hz'), + Column('grid_volts', 'Grid Voltage', 'giantlog', 'Vac'), + Column('hs_temperature', 'HS Temperature', 'giantlog', 'C'), + Column('load_pct', 'Load', 'giantlog', '%', (0, 100)), +] + +def valid_date(s): + try: + return datetime.datetime.strptime(s, "%Y-%m-%d") + except ValueError: + raise argparse.ArgumentTypeError("Not a valid date: '{0}'.".format(s)) + +def main(): + parser = argparse.ArgumentParser() + parser.add_argument('-f', '--filename', help = 'Path to database', type = str, required = True) + parser.add_argument('-g', '--graphfn', help = 'File to write graph to', type = str) + parser.add_argument('-d', '--days', help = 'Days ago to graph', type = int) + parser.add_argument('-s', '--start', help = 'Start date for graph (YYYY-MM-DD)', type = valid_date) + parser.add_argument('-e', '--end', help = 'End date for graph (YYYY-MM-DD)', type = valid_date) + parser.add_argument('-c', '--column', help = 'Column to plot (can be specified multiple times)', type = str, action = 'append') + + args = parser.parse_args() + + if args.days is not None and args.days < 0: + parser.error('days must be non-negative') + + # Can specify.. + # Start and end + # Start and days + # End and days + # Nothing + # Want to end up with a start & end + if args.start is not None and args.end is not None: + pass + elif args.start is not None and args.days is not None: + args.end = args.start + datetime.timedelta(days = args.days) + elif args.end is not None and args.days is not None: + args.start = args.end - datetime.timedelta(days = args.days) + elif args.start is None and args.end is None and args.days is None: + end = datetime.date.today() + end = datetime.datetime(start.year, start.month, start.day) + args.start = args.end - datetime.timedelta(days = args.days) + else: + parser.error('can\'t specify days, start and end simultaneously') + + if args.start >= args.end: + parser.error('Start must be before end') + + cols = args.column + if cols == None: + cols = ['main_voltage', 'aux_voltage', 'ac_app_power'] + + dbh = sqlite3.connect(args.filename, detect_types = sqlite3.PARSE_DECLTYPES) + cur = dbh.cursor() + + # Get local timezone name and convert start/end to it + # Why is this so hard... + ltname = tzlocal.get_localzone().zone + ltname = 'Australia/Adelaide' + lt = dateutil.tz.gettz(ltname) + utc = dateutil.tz.gettz('UTC') + matplotlib.rcParams['timezone'] = ltname + + if args.start.tzinfo == None: + args.start = args.start.replace(tzinfo = lt) + if args.end.tzinfo == None: + args.end = args.end.replace(tzinfo = lt) + startlt = args.start + endlt = args.end + args.start = args.start.astimezone(utc) + args.end = args.end.astimezone(utc) + graph(args.graphfn, cur, cols, int(args.start.strftime('%s')), int(args.end.strftime('%s')), lt, utc) + +def graph(fname, cur, _cols, start, end, lt, utc): + import numpy + import matplotlib + import matplotlib.dates + + startdt = datetime.datetime.fromtimestamp(start).replace(tzinfo = utc).astimezone(lt) + enddt = datetime.datetime.fromtimestamp(end).replace(tzinfo = utc).astimezone(lt) + + colourlist = ['b','g','r','c','m','y','k'] + + cols = [] + + yaxisunits1 = None + yaxisunits2 = None + ax1lines = [] + ax2lines = [] + colouridx = 0 + for col in _cols: + # Check the column exists + for c in columns: + if col == c.rowname: + cols.append(c) + break + else: + raise exceptions.Exception('Unknown column name ' + c) + + # Work out what axes we are using + if yaxisunits1 == None: + yaxisunits1 = c.units + if yaxisunits2 == None: + if c.units != yaxisunits1: + yaxisunits2 = c.units + else: + if c.units != yaxisunits1 and c.units != yaxisunits2: + raise exceptions.Exception('Asked to graph >2 different units') + + for c in cols: + # Get the data + cur.execute('SELECT tstamp, ' + c.rowname + ' FROM ' + c.table + ' WHERE tstamp > ? AND tstamp < ? ORDER BY tstamp', + (start, end)) + ary = numpy.array(cur.fetchall()) + if ary.shape[0] == 0: + print('No data for ' + c.rowname) + return + + # Create TZ naive from POSIX stamp, then convert to TZ aware UTC then adjust to local time + c.xdata = map(lambda f: datetime.datetime.fromtimestamp(f).replace(tzinfo = utc).astimezone(lt), ary[:,0]) + c.ydata = ary[:,1] + if c.conv != None: + c.ydata = map(c.conv, c.ydata) + + scale_min, scale_max = c.limits + + # DoD? + c.annotation = None + + # Work out which axis to plot on + if c.units == yaxisunits1: + ax = ax1lines + else: + ax = ax2lines + c.colour = colourlist[colouridx] + colouridx += 1 + ax.append(c) + + # Load the right backend for display or save + if fname == None: + import matplotlib.pylab + fig = matplotlib.pylab.figure() + else: + import matplotlib.backends.backend_agg + fig = matplotlib.figure.Figure(figsize = (12, 6), dpi = 75) + + # Do the plot + ax1 = fig.add_subplot(111) + ax1.set_ylabel(yaxisunits1) + + annotations = [] + for line in ax1lines: + ax1.plot(line.xdata, line.ydata, label = line.title, color = line.colour) + if line.limits[0] != None or line.limits[1] != None: + ax1.set_ylim(line.limits[0], line.limits[1]) + if line.annotation != None: + annotations.append(line.annotation) + ax1.legend(loc = 'upper left') + + if len(ax2lines) > 0: + ax2 = ax1.twinx() + ax2.set_ylabel(yaxisunits2) + + for line in ax2lines: + ax2.plot(line.xdata, line.ydata, label = line.title, color = line.colour) + if line.limits[0] != None or line.limits[1] != None: + ax2.set_ylim(line.limits[0], line.limits[1]) + if line.annotation != None: + annotations.append(line.annotation) + + ax2.legend(loc = 'upper right') + + if len(annotations) > 0: + ax1.text(0.02, 0.9, reduce(lambda a, b: a + '\n' + b, annotations), + transform = ax1.transAxes, bbox = dict(facecolor = 'red', alpha = 0.5), + ha = 'left', va = 'top') + ndays = int(max(1, round((end - start) / 86400))) + for ax in fig.get_axes(): + if (enddt - startdt).total_seconds() > 86400: + ax.set_title('%s to %s' % (startdt.strftime('%Y-%m-%d'), enddt.strftime('%Y-%m-%d'))) + else: + ax.set_title('%s' % (startdt.strftime('%Y-%m-%d'))) + ax.set_xlim([startdt, enddt]) + ax.format_xdata = lambda d: matplotlib.dates.num2date(d).strftime('%d %b %H:%M') + ax.xaxis.grid(True) + ax.xaxis.set_major_formatter(matplotlib.dates.DateFormatter('%d %b\n%H:%M')) + ax.xaxis.set_major_locator(matplotlib.dates.HourLocator(interval = 2 * ndays)) + ax.xaxis.set_minor_locator(matplotlib.dates.MinuteLocator(interval = 5 * ndays)) + for label in ax.get_xticklabels(): + label.set_ha('center') + label.set_rotation(90) + + # Fudge margins to give more graph and less space + fig.subplots_adjust(left = 0.10, right = 0.88, top = 0.95, bottom = 0.15) + if fname == None: + matplotlib.pyplot.show() + else: + canvas = matplotlib.backends.backend_agg.FigureCanvasAgg(fig) # Sets canvas in fig too + fig.savefig(startlt.strftime(fname)) + +def updatedb(cur, data): + mkdb(cur) + for d in data['reads']['data']: + ts = datetime.datetime.strptime(d['t_stamp'], '%Y-%m-%dT%H:%M:%SZ') + # Note we rename *energy* to *power* here to match what it actually means + vals = [ts, d['battery_charge'], d['battery_energy'], d['energy_consumed'], d['energy_expected'], d['energy_exported'], d['energy_generated'], + d['energy_imported'], d['estimated_savings'], d['pv_forecast'], d['pv_generation']['battery_energy'], + d['pv_generation']['grid_energy'], d['pv_generation']['site_energy'], d['site_consumption']['battery_energy'], + d['site_consumption']['grid_energy'], d['site_consumption']['pv_energy']] + skip = True + for v in vals[1:]: + if v != None: + skip = False + break + if skip: + print('Skipping empty record at ' + str(ts)) + continue + cur.execute('INSERT OR IGNORE INTO agl(t_stamp, battery_charge, battery_power, power_consumed, power_expected, power_exported, power_generated, power_imported, estimated_savings, pv_forecast, pv_gen_battery, pv_gen_grid, pv_gen_site, site_cons_battery, site_cons_grid, site_cons_pv) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', vals) + +def gettoken(username, password): + authblob = json.encoder.JSONEncoder().encode({'email' : username, 'password' : password}) + reply = requests.request('POST', loginurl, data = authblob, headers = {'Content-Type' : 'application/json'}) + if reply.status_code != 200: + return None + return json.decoder.JSONDecoder().decode(reply.content)['access_token'] + +def getdata(token, startdate, enddate): + #print('getting ' + startdate.strftime('%Y-%m-%d')) + reply = requests.request('GET', dataurl, params = { + 'startDate' : startdate.strftime('%Y-%m-%d'), + 'endDate' : enddate.strftime('%Y-%m-%d'), + 'granularity' : 'Minute', + 'metrics' : 'read', + 'units' : 'W', + }, headers = { 'Authorization' : 'Bearer ' + token}) + + if reply.status_code != 200: + return None + + return json.decoder.JSONDecoder().decode(reply.content) + +def logout(token): + reply = requests.request('GET', logouturl, headers = { 'Authorization' : 'Bearer ' + token}) + return reply.status_code == 200 + +if __name__ == '__main__': + main()