--- /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()