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

import MySQLdb
import sys
import getopt
import socket


def main(argv):
    #get LinHES version
    verfile = open('/etc/LinHES-release', 'r')
    verLH = verfile.read()
    verfile.close()

    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 where oldrecorded.recstatus = -3  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 "<h3>Welcome to</h3>"
    print "<h1>" , verLH , "</h1>"
    print "<h3>on " , socket.gethostname() , "</h3>"
    print "</br>"
    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 a 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 where oldrecorded.recstatus = -3 group by title having (COUNT(title) > 0 ) 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 categories

    print '<div id="column">'
    print '<table class="sample">'
    print "Top 20 Categories"
    cursor.execute("select category,count(category) as numtitle from oldrecorded where oldrecorded.recstatus = -3 group by category having (COUNT(category) > 0 ) order by (COUNT(category)) DESC  limit 20; " )
    result = cursor.fetchall()
    for row in result:
        category = row[0]
        if category == "":
            category = "Unknown"
        print "	<tr>"
        print "		<td>",category,"</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:])