bgghotness_stats.py

import sqlite3
import matplotlib.pyplot as plt
import numpy as np


db_loc = 'gencon_test.sqlite'
conn = sqlite3.connect(db_loc)
c = conn.cursor()





def top_avg(c,cat,begin):
    query = ""
    wherequery = ""


    if cat == 'game':
        query = "SELECT games.name as nm, avg(hotgame.rank) as rk, count(hotgame.rank) as hr FROM hotgame JOIN games ON games.bggid = hotgame.bggid "
    elif cat == "person":
        query = "select name as nm, avg(rank) as rk, count(rank) as hr from hotperson "
    elif cat == "company":
        query = "select publishers.name as nm, avg(hotcomp.rank) as rk, count(hotcomp.rank) as hr from hotcomp join publishers on publishers.pubid = hotcomp.pubid "
    else:
        print "Invalid category"
        return
    
    if begin is not None:
        print begin
        wherequery = "where date(date,'localtime') >= '{tm}'".format(tm = begin)
            
    query += wherequery + "group by nm order by rk asc limit 20"
    
    c.execute(query)
    result = c.fetchall()
    i = 1
    print "Overall Rank : Name - Average Rank - Hours"
    for item in result:
        print str(i) + ": " + str(item[0]) + " - " + str(item[1]) + " - " + str(item[2]) 
        i += 1
    return result


def rnk_inc(c, cat, begin):
    
    query = ""

    if cat == 'game':
        query = """ select games.name, min(a.rank - b.rank) as diff 
                    from hotgame a 
                    inner join hotgame b on a.date > b.date and a.bggid = b.bggid {begin}
                    inner join games on games.bggid = a.bggid 
                    group by a.bggid 
                    having diff <> 0 order by diff
                    limit 20

                    """
    elif cat == 'person':
        query = """ select a.name, min(a.rank - b.rank) as diff
                    from hotperson a
                    inner join hotperson b on a.date > b.date and a.name = b.name {begin}
                    group by a.name
                    having diff <> 0 order by diff
                    limit 20
                    """
    elif cat == 'company':
        query = """ select publishers.name, min(a.rank - b.rank) as diff
                    from hotcomp a
                    inner join hotcomp b on a.date > b.date and a.pubid = b.pubid {begin}
                    inner join publishers on publishers.pubid = a.pubid
                    group by a.pubid
                    having diff <> 0 order by diff
                    limit 20
                    """

    else:
        print "Invalid category"
        
    if begin is not None:
        query = query.format(begin = 'and b.date >= {dt}'.format(dt = begin))
        
    else:
        query = query.format(begin = ' ')
        
            
           
    c.execute(query)
    result = c.fetchall()
    i = 1
    print "Overall Rank: Name - Rank Increase"
    for item in result:
        print str(i) + ": " + str(item[0]) + " - " +str(item[1])
        i += 1
    
    return result


def history(c, cat, name):
    if cat == 'game':
        if name is None:
            query = """select games.name, rank
                        from hotgame
                        join games on games.bggid = hotgame.bggid
                        group by games.name
                        having hotgame.date = max(hotgame.date)
                        order by rank asc
                        limit 20
            """
        else:
            query = "SELECT datetime(date,'localtime'), rank FROM hotgame JOIN games ON games.bggid = hotgame.bggid WHERE games.name like '{nm}'".format(nm = name)
    elif cat == "person":
        if name is None:
            query = "SELECT name, rank FROM hotperson group by name HAVING date = max(hotperson.date) ORDER BY rank asc limit 20"
        else:
            query = "SELECT datetime(date,'localtime'), rank FROM hotperson WHERE hotperson.name like '{nm}'".format(nm = name)
    elif cat == "company":
        if name is None:
            query = """SELECT publishers.name, rank
                        FROM hotcomp
                        JOIN publishers ON publishers.pubid = hotcomp.pubid
                        GROUP BY publishers.name
                        HAVING hotcomp.date = max(hotcomp.date)
                        ORDER BY rank asc LIMIT 20"""
        else:      
            query = "SELECT datetime(date,'localtime'), rank FROM hotcomp JOIN publishers ON publishers.pubid = hotcomp.pubid WHERE publishers.name like '{nm}'".format(nm = name)
    else:
        print "Invalid Category"
        return
    

    c.execute(query)
    result = c.fetchall()
    for item in result:
        print str(item[0]) + " - " + str(item[1])

    return result


while True:
    command = raw_input('Enter Command: ')
    
    if command[0:6] == 'topavg':
        command = command.lstrip('topavg')
        command = command.lstrip(' ')
        cat = command.split(' ', 1)[0]

        
        try:
            begin = command.split(' ',1)[1]
            print begin
        except IndexError:
            begin = None
            
        top_avg(c, cat, begin)
        
    elif command[0:6] == 'rnkinc':
        command = command.lstrip('rnkinc')
        command = command.lstrip(' ')
        cat = command.split(' ',1)[0]
        
        try:
            begin = command.split(' ',1)[1]
            print begin
        except IndexError:
            begin = None
            
        rnk_inc(c, cat, begin)
        
    elif command[0:7] == 'history':
        command = command.lstrip('history')
        command = command.lstrip(' ')
        cat = command.split(' ', 1)[0]
        
        try:
            name = command.split(' ',1)[1]
        except IndexError:
            name = None
        
        
        history(c, cat, name)