#!/usr/bin/python

import sys
import MySQLdb
import Gnuplot
import string
import tempfile
import os
import time

print "plotsolar " + time.ctime()

# TODO: more graphs?

graphs = {
  'Power': ('SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(Timestamp))) as T, AVG(POUT)/1000.0 as V FROM Samples','kW','Sum'),
  'Inverter Efficiency': ('SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(Timestamp))) as T, IF(SUM(PIN)<300, 0, SUM(POUT)/SUM(PIN)) as V FROM Samples','','Avg'),
  'Maximum Inverter Temperature': ('SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(Timestamp))) as T, MAX(SUBSTRING(MEASTEMP, 3, INSTR(MEASTEMP, "F:") - 4) + 0.0) as V FROM Samples', 'degrees C', 'Max'),
}

date_ranges = {
  'Day': 'Timestamp >= now() - INTERVAL 1 DAY',
  'Week': 'Timestamp >= now() - INTERVAL 7 DAY',
  'Month': 'Timestamp >= now() - INTERVAL 1 MONTH',
  'Quarter': 'Timestamp >= now() - INTERVAL 3 MONTH',
  'Year': 'Timestamp >= now() - INTERVAL 1 YEAR',
  'All': '1=1',
}

groupings = {
  'Day': 'Timestamp',                             # no group (1440 samples)
  'Week': 'DATE_FORMAT(Timestamp, "%Y %m %d %H")',# group by hour(~168 samples)
  'Month': 'DATE_FORMAT(Timestamp, "%Y %m %d")',  # group by day (~30 samples)
  'Quarter': 'DATE_FORMAT(Timestamp, "%Y %m %d")',# group by day (~90 samples)
  'Year': 'DATE_FORMAT(Timestamp, "%Y %U")',      # group by week (52 samples)
  'All': 'DATE_FORMAT(Timestamp, "%Y %U")',       # group by week (52+ samples)
}

def main(cursor, scale, inverters, outputdir):

  range = date_ranges[scale]
  group = groupings[scale]

  for (name, (sql, ylabel, accum)) in graphs.items():
    data = []

    # get a line for each inverter
    n = 0
    for inverter in inverters:
      n+=1
      cursor.execute("%s WHERE %s AND IDN='%s' GROUP BY %s" %
                     (sql, range, inverter, group))
      series = cursor.fetchall()
      if len(series) == 0: continue
      (f, fname) = tempfile.mkstemp()
      realdata = 0
      for s in series:
	if s[1] != 0:
          os.write(f, "%s %s\n" % (s[0], s[1]))
          realdata = 1
        else:
          os.write(f, "\n")
      os.close(f)
      if not realdata: continue
      d = Gnuplot.File(fname, title="inverter%d" % n, using="1:3", with="lines lt %d" % (n+1))
      data.append(d)

    # add a total line
    if len(inverters) > 1:
      sql2 = sql
      if accum == "Sum":	
	cursor.execute("CREATE TEMPORARY TABLE tmp SELECT Timestamp, SUM(POUT) as POUT FROM Samples WHERE %s GROUP BY Timestamp" % range)
	sql2 = sql2[:-7] + "tmp"
      cursor.execute("%s WHERE %s GROUP BY %s" %
                     (sql2, range, group))
      series = cursor.fetchall()
      if len(series) == 0: continue
      (f, fname) = tempfile.mkstemp()
      realdata = 0
      for s in series:
	if s[1] != 0:
          os.write(f, "%s %s\n" % (s[0], s[1]))
	  realdata = 1
        else:
          os.write(f, "\n")
      os.close(f)
      if not realdata: continue
      d = Gnuplot.File(fname, title="system", using="1:3", with="lines lt 1")
      data.append(d)
      if accum == "Sum":
	cursor.execute("DROP TABLE tmp")

    g = Gnuplot.Gnuplot()
    g('set title "%s"' % name)
    g('set xdata time')
    g('set timefmt "%Y-%m-%d %H:%M:%S"')
    g('set xtics rotate')
    g('set bmargin 6')
    if ylabel: g('set ylabel "%s"' % ylabel)
    g('set style data lines')
    g('set yrange [0:]')
    g('set key right bottom')
    g('set terminal png')
    g('set output "%s/%s_%s.png"' % (outputdir, name.replace(" ", "_"), scale))

    g.plot(*data)

if __name__ == "__main__":
  # connect to database
  dbparts = sys.argv[1].split(":")
  connection = MySQLdb.connect(host=dbparts[0], user=dbparts[1],
    passwd=dbparts[2], db=dbparts[3])
  cursor = connection.cursor()

  # figure out which inverters are available
  cursor.execute("SELECT DISTINCT(IDN) as name, MIN(Timestamp) as first_timestamp FROM Samples WHERE IDN LIKE %s AND LENGTH(IDN)=35 AND Timestamp >= now() - INTERVAL 1 YEAR GROUP BY name ORDER BY first_timestamp" % "'M%'")
  inverters = map(lambda x: x[0], cursor.fetchall())

  main(cursor, 'Day', inverters, sys.argv[2])
  main(cursor, 'Week', inverters, sys.argv[2])
  main(cursor, 'Month', inverters, sys.argv[2])
  main(cursor, 'Year', inverters, sys.argv[2])

  print "done " + time.ctime()