# -*- coding: utf-8 -*-
#
#*********************************************************************
# Name:        DelaysByHourORD.py
# Created:     5/18/2016
# Modified:    5/18/2016
# Created By:  Peter McCutcheon
#
#---------------------------------------------------------------------
# Description:
#
#   This Python script reads the flight data database to obtain the
#   flight delay times by hour of the day for Chicago O'Hare airport.
#   The program uses SQL to read the database and generates a line
#   graph of the total number of minutes of delay per hour of the
#   day.  It also generates a line graph of the number of flights
#   per hour of the day.
#
#**********************************************************************

import sqlite3
from matplotlib import pyplot as plt

#
#   Connect to the FlightData database and create a cursor for SQL operations.
#
conn = sqlite3.connect('../data/flightdata.db')
curs = conn.cursor()          

#
#   Set up a couple of lists to be passed to matplotlib for the line graph.
#
hour = []
totalDelay = []
delayCount = []
#
#   Set up the SQL select statement to get the flight data from the database.
#   We are going select the data for a specific airport then group by hour 
#   and then sum on the departure delay for each hour of the day and display
#   a line graph. 
#
selStr = """select origin, substr(departtime,1,2) as h, sum(departdelay), count(departdelay) from flightdelay where origin like "ORD" group by h;"""

#
#   Fire off the actual SQL select.
#
try:
    curs.execute(selStr)
except Exception as er:
    print("Sqlite Error " + er.message)
#
#   Read through the rows that were returned from the DB and set up
#   the lists to pass to matplotlib.
#

result = curs.fetchall()
for row in result:
    #record = list(row)
    hour.append(int(row[1]))
    totalDelay.append(int(row[2]))
    delayCount.append(row[3])
    print(hour, totalDelay, delayCount)        
     
#
#   Set up the objects for the line graph.
#   We need the x and y values which are setup above as
#   we read data from the SQL select.  Hour will be
#   the x-axis and totalDelay will be the y-axix.
#   Everything else is just format, labels, title, line size etc.
#
lines = plt.plot(hour,totalDelay,'r-', hour, delayCount, 'b-')
plt.title("Hourly Delays for ORD During 01/2016")
plt.ylabel('Total Minutes')
plt.xlabel('Hour of Day')
plt.grid(True)
plt.setp(lines, linewidth=2.0)
#
#   Time to show the actual line graph. We'll also create a png of it.
#   Then clean everything up.
#
plt.savefig('../img/HourlyDelayORD.png')
plt.show() 
curs.close()
conn.close()