from IPython.display import HTML
import requests
import pandas as pd
import MySQLdb
import pandas.io.sql as psql
mysql_conn=MySQLdb.connect(user='root',passwd='password',db='jplug');
import datetime
import time
import pytz
import time
import matplotlib.dates as mdates
import matplotlib.pyplot as plt


styles = requests.get("https://raw.github.com/CamDavidsonPilon/Probabilistic-Programming-and-Bayesian-Methods-for-Hackers/master/styles/custom.css")
HTML(styles.text)
import json
s = requests.get("https://raw.github.com/CamDavidsonPilon/Probabilistic-Programming-and-Bayesian-Methods-for-Hackers/master/styles/bmh_matplotlibrc.json").json()
matplotlib.rcParams.update(s)


figsize(10,7)

start_date=datetime.datetime(2013,5,28,0,0,0,tzinfo=pytz.timezone('Asia/Kolkata'))
jplug_ids=["001EC00CC4A0","001EC00CC4A1","001EC00CC4AD","001EC00CC49C","001EC00CC49F","001EC00D7A18","001EC00CC49D","001EC00D7A1D","001EC00D7A1C"]
#jplug_ids=["001EC00CC4A0"]
num_days=61
stats={}
for jplug in jplug_ids:
    stats[jplug]={}
    stats[jplug]['X']=[]
    stats[jplug]['count']=[]
    stats[jplug]['mean']=[]
    stats[jplug]['max']=[]
    stats[jplug]['min']=[]
    for i in range(num_days):
        start=start_date+datetime.timedelta(i)
        end=start_date+datetime.timedelta(i+1)
        start_timestamp=int(time.mktime(start.timetuple()))
        end_timestamp=int(time.mktime(end.timetuple()))
        query='select count(*) from jplug_data where mac="%s" and timestamp between %d and %d;' %(jplug,start_timestamp,end_timestamp)
        result=psql.frame_query(query,mysql_conn)
        stats[jplug]['count'].append(result.values[0][0])
        stats[jplug]['X'].append(start)
        query='select avg(active_power) from jplug_data where mac="%s" and timestamp between %d and %d;' %(jplug,start_timestamp,end_timestamp)
        result=psql.frame_query(query,mysql_conn)
        if result.values[0][0] is not None:
            stats[jplug]['mean'].append(result.values[0][0])
        else:
            stats[jplug]['mean'].append(0)        
                
    plt.figure()
    plt.subplot(2,1,1)
    plt.bar(stats[jplug]['X'],stats[jplug]['count'])
    plt.title("Number of points for %s" %jplug)
    plt.subplot(2,1,2)
    plt.bar(stats[jplug]['X'],stats[jplug]['mean'])
    plt.title("Average active power for %s" %jplug)
    plt.ylabel('Average Active Power (W)')
    plt.tight_layout()