#!/usr/bin/python
# import MySQL module

import MySQLdb
import sys
import getopt
import socket


def main(argv):
    grandtotal=0
    grandtotal_current=0
    db = MySQLdb.connect(host="localhost", user="mythtv", passwd="mythtv", db="mythconverg")
    # create a cursor
    cursor = db.cursor()
   # execute SQL statement
    cursor.execute("SELECT unix_timestamp(starttime),unix_timestamp(endtime),title FROM oldrecorded ORDER by starttime;")
    result = cursor.fetchall()

    for row in result:
      #  print row[0], row[1] ,row [2]
        start=row[0]
        end=row[1]
        if ( end  > start ):
            total = (end - start) /60
            grandtotal+=total
    hours = grandtotal / 60.0
    days = hours / 24.0

    cursor.execute("SELECT unix_timestamp(starttime),unix_timestamp(endtime),title FROM recorded where not (recgroup=(%s))  ORDER by starttime",("LiveTV"))
    result_current = cursor.fetchall()
    for row in result_current:
        start=row[0]
        end=row[1]
        if ( end  > start ):
            total = (end - start) /60
            grandtotal_current+=total
    hours_current = grandtotal_current / 60.0
    days_current = hours_current / 24.0

    print '<div id="header">'

    print "<table>"
    print "    <tr>"
    print "	  <td> Currently there are " ,  len(result_current) , " recorded programs.  With a viewing time of  %.2f days or  %.2f  hours. </td>" %(days_current,hours_current)
    print "    </tr>"
    print "</table>"
    print "</br> "

    print "<table>"
    print "	<tr>"
    print "  		<td> Overall there have been " ,  len(result) , "shows recorded. With viewing time of %.2f days or %.2f hours </td>"%(days,hours)
    print "	</tr>"
    print "</table>"
    print '</div>'
    print "</br> </br> "
#Top shows
    print '<div id="content"> <p></p>'
    print '<table class="sample">'
    print "Top 20 Shows"
    cursor.execute("select title,count(title),category as numtitle from oldrecorded group by title having (COUNT(title) > 1) order by (COUNT(title)) DESC  limit 20;" )
    result = cursor.fetchall()
    for row in result:
        print "	<tr>"
        print "		<td>",row[0],"</td>"
        print "		<td>",row[2],"</td>"
        print "		<td>",row[1],"</td>"
        print "	</tr>"
       # print "%40s %26s %10s" %(row[0],row[2],row[1])
    print "</table>"
    print "</div>"
#Top categorys

    print '<div id="column"></div>'
    print '<table class="sample">'
    print "Top 20 Categories"
    cursor.execute("select category,count(category) as numtitle from oldrecorded group by category having (COUNT(category) > 1) order by (COUNT(category)) DESC  limit 20; " )
    result = cursor.fetchall()
    for row in result:
        print "	<tr>"
        print "		<td>",row[0],"</td>"
        print "		<td>",row[1],"</td>"
        print "	</tr>"
        #print "%16s %16s" %(row[0],row[1])
    print "</table>"
    print "</div>"

if __name__ == "__main__":
    main(sys.argv[1:])