#!/usr/bin/env python
# -*- coding: utf-8 -*-

import plotly

#import plotly.plotly as py
import plotly.graph_objs as go
from plotly import tools

import pandas as pd
import MySQLdb
import datetime
now = datetime.datetime.now()

jahr=now.year

db_connection = MySQLdb.connect(host= "localhost",
                  user="root",
                  passwd="root",
                  db="mydb")

#fig = tools.make_subplots(rows=4, cols=1, subplot_titles=('Oeltank 1', 'Oeltank 2', 'Oeltank 3', 'Temperatur Hof'))
fig = tools.make_subplots(rows=7, cols=1, subplot_titles=('Oeltank 1', 'Oeltank 2', 'Oeltank 3', 'Temperatur Hof', 'Temperatur Hof '+str(jahr-1),'Temperatur Dach '+str(jahr),'Temperatur Dach'+str(jahr-1)))

df = pd.read_sql('SELECT zeit, OelVolumen, OelVolumen2, OelVolumen3, temperatur FROM oelstand where zeit > "'+str(jahr)+'-01-01"' , con=db_connection)
df_prev = pd.read_sql('SELECT zeit, temperatur FROM oelstand where zeit > "'+str(jahr-1)+'-01-01" and zeit < "'+str(jahr)+'-01-01"' , con=db_connection)

df2 = pd.read_sql('SELECT zeit, location, temperatur FROM temperature where location="wohnung" and zeit > "'+str(jahr)+'-01-01"' , con=db_connection)
df3 = pd.read_sql('SELECT zeit, location, temperatur FROM temperature where location="dach" and zeit > "'+str(jahr)+'-01-01"' , con=db_connection)
df4 = pd.read_sql('SELECT zeit, location, temperatur FROM temperature where location="dach" and zeit > "'+str(jahr-1)+'-01-01" AND zeit < "'+str(jahr) +'-01-01"' , con=db_connection)
#df = pd.read_csv("/home/pi/datafile2.csv")

trace_oel1 = go.Scatter(
                x=pd.to_datetime(df['zeit']),
                y=df['OelVolumen'],
                name = "oelltank1",
                line = dict(color = '#17BECF'),
                opacity = 0.8)

trace_oel2 = go.Scatter(
                #x=df.Date,
                x=pd.to_datetime(df['zeit']),
                y=df['OelVolumen2'],
                name = "oelltank2",
                line = dict(color = '#7F7F7F'),
                opacity = 0.8)

trace_oel3 = go.Scatter(
                #x=df.Date,
                x=pd.to_datetime(df['zeit']),
                y=df['OelVolumen3'],
                name = "oelltank3",
                line = dict(color = '#3333FF'),
                opacity = 0.8)

trace_temp_hof = go.Scatter(
                x=pd.to_datetime(df['zeit']),
                y=df['temperatur'],
                name = "Temp_hof_"+str(jahr),
                line = dict(color = '#FF3333'),
                opacity = 0.8)

trace_temp_hof_prev = go.Scatter(
                x=pd.to_datetime(df_prev['zeit']),
                y=df_prev['temperatur'],
                name = "Temp_Hof_"+str(jahr-1),
                line = dict(color = '#8b8b8b'),
                opacity = 0.8)


trace_temp_2og = go.Scatter(
                x=pd.to_datetime(df2['zeit']),
                y=df2['temperatur'],
                name = "Temp_2og",
                line = dict(color = '#FF3333'),
                opacity = 0.8)

trace_temp_dach = go.Scatter(
                x=pd.to_datetime(df3['zeit']),
                y=df3['temperatur'],
                name = "Temp_Dach",
                line = dict(color = '#17BECF'),
                opacity = 0.8)

trace_temp_dach_prev = go.Scatter(
                x=pd.to_datetime(df4['zeit']),
                y=df4['temperatur'],
                name = "Temp_Dach_"+str(jahr-1),
                line = dict(color = '#17BECF'),
                opacity = 0.8)


data = [trace_oel1,trace_oel2,trace_oel3]
data2 = [trace_temp_hof]
data3 = [trace_temp_2og, trace_temp_dach, trace_temp_dach_prev]

layout = dict(
    title = "Oel Staende",
    xaxis = dict( range = [str(jahr-1)+'-01-01_00:00',str(jahr)+'-07-25_23:59'])
)


#fig = dict(data=data, layout=layout)
fig.append_trace(trace_oel1, 1, 1)
fig.append_trace(trace_oel2, 2, 1)
fig.append_trace(trace_oel3, 3, 1)
fig.append_trace(trace_temp_hof, 4, 1)
fig.append_trace(trace_temp_hof_prev, 5, 1)
fig.append_trace(trace_temp_2og, 6, 1)
fig.append_trace(trace_temp_dach, 6, 1)
fig.append_trace(trace_temp_dach_prev, 7, 1)
fig['layout'].update(showlegend=True,height=1500,width=1000, title='Hausstatus', xaxis= {
    "autorange": True, 
    "range": [str(jahr)+"-01-01", str(jahr)+"-12-31"]
  }
)
#fig['data'] = data
plotly.offline.plot(fig, filename = "/var/www/html/index.html")

