#!/usr/bin/python
# 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
    try:
        cursor.execute("SELECT unix_timestamp(starttime),unix_timestamp(endtime),title FROM recorded where not (recgroup=(%s))  ORDER by starttime",("LiveTV"))
    except:
        pass
    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:])