

import datetime
import sqlite3 as lite
import sys
import web, random

# APPLICATION-SPECIFIC




urls = ( '/(.*)', 'index')

# relates after the url /xxx to the server that should be called.

# What does this template.render thing. 
render = web.template.render('templates')


class Constants:
	x = 10
	users = {}
	colors = ['red','blue','green']
	bet_messages = []
	winnertake = 0.99 # amount the winner gets of nominal bet
	houseaccount = -1 # id of house account

count = 0
lastx = ""

# call with /?name=Joe
class index:
    def GET(self,name):
	# split name on equal
	namefields = name.split("=")
	# print "namefields are ", self
	# print "self is", self
	# print "name is", name
	postquestion = web.input(name=None)
	print "postquestion is", postquestion
	# print "postquestion[function]: ", postquestion['function']
	# print "postquestion[argument]: ", postquestion['argument']
	if 2 > len(namefields):
	  namefields = [postquestion['function'], postquestion['argument']]
	print "namefields now are", namefields
	# if the first string is payout  then call payout (still need to 
	#  remove bet in order to prevent two calls to payout for the same bet)
	#  e.g. http://0.0.0.0:8080/payout=13
	# if first string is findbet then take conditions as a string
	#   and search each string for any of the comma separated entities
	#   return them ordered by number of conditions that match, most first
	#  e.g. http://0.0.0.0:8080/findbet=team1='Cavaliers',moneyline>210
	if namefields[0] == "findbet":
        	return render.index(findbet('='.join(namefields[1:])))
	else:
		return 'Unrecognized command'
	

# APPLICATION-SPECIFIC

# Split on comma and then search each condition
def findbet(myfields):
 print "myfields is: ", myfields
 myfieldsarr = myfields.split(",")
 print "myfieldsarr is: ", myfieldsarr
 con = lite.connect('bet.db')
 with con:    
   cur = con.cursor()    
   cur.execute("DROP TABLE IF EXISTS tmp")
   cur.execute("CREATE TABLE tmp(startdate INT, starttime INT, location TEXT, team1 TEXT, team2 TEXT, betid INT,  predicatestring TEXT, maximumamount INT, committed INT, moneyline TEXT)")
   if len(myfieldsarr) == 1:
     x = "SELECT game.startdate, game.starttime, game.location, game.team1, game.team2, betdescription.betid, betdescription.predicatestring, betdescription.maximumamount, betdescription.committed, betdescription.moneyline FROM betdescription, game WHERE game.gameid = betdescription.gameid and " + myfieldsarr[0]
     print "len 1 x", x
     cur.execute(x)
     rows = cur.fetchall()
     print "One field. Rows are: ", rows
   else:
     x = "INSERT INTO tmp SELECT game.startdate, game.starttime, game.location, game.team1, game.team2, betdescription.betid, betdescription.predicatestring, betdescription.maximumamount, betdescription.committed, betdescription.moneyline FROM betdescription, game WHERE game.gameid = betdescription.gameid and " + myfieldsarr[0]
     print "within else, creating tmp, here is x: ", x
     cur.execute(x)
     cur.execute("DROP TABLE IF EXISTS tmp2")
     cur.execute("CREATE TABLE tmp2(betid INT)")
     for cond in myfieldsarr[1:]:
   	x = "INSERT INTO tmp2 SELECT tmp.betid FROM tmp where " + cond
   	# Would like a WHERE clause that searches for strings
	print "within myfieldsarr[1:], here is x: ", x
   	cur.execute(x)
     cur.execute("DROP TABLE IF EXISTS tmp3")
     cur.execute("CREATE TABLE tmp3(betid INT, mycount INT)")
     x = "INSERT INTO tmp3 SELECT betid, count(*) FROM tmp2 group by betid "
     cur.execute(x)
     x = "SELECT tmp.startdate, tmp.starttime, tmp.location, tmp.team1, tmp.team2, tmp.betid, tmp.predicatestring, tmp.maximumamount, tmp.committed, tmp.moneyline, tmp3.mycount FROM tmp, tmp3 WHERE tmp.betid = tmp3.betid ORDER BY mycount"
     # x = "SELECT tmp.startdate, tmp.location, tmp.team1, tmp.team2, tmp.betid, tmp.predicatestring, tmp.maximumamount, tmp.committed, tmp.moneyline FROM tmp"
     cur.execute(x)
     rows = cur.fetchall()
     print "rows after join: ", rows
   out = ''
   for row in rows:
          out+= ' startdate: '+ str(row[0])+ ' starttime: ' + str(row[1]) + ' location: '+ str(row[2]) + ' team1 : '+ str(row[3]) + ' Team 2: ' + str(row[4]) + ' betid: ' + str(row[5])  + '\n'
   return out
         

def generatefromdb(name):
   global count
   out = 'generating an output for ' + name + ' with count: ' + str(count)
   count+= 1
   con = lite.connect('bet.db')
   with con:    
       cur = con.cursor()    
       x = "SELECT * FROM game WHERE team1 = '" + name + "' OR team2 = '" + name + "'"
       print x
       cur.execute(x)
   
       rows = cur.fetchall()
   
       for row in rows[:3]:
          out+= ' Game: '+ str(row[0])+ ' start date: '+ str(row[1]) + ' start time: '+ str(row[2]) + ' location: ' + row[3]  + ' team: ' + row[4] + ' other team: ' + row[5] + ' game type: ' + row[7] + '\n'
   return out


if __name__ == '__main__':
    app = web.application(urls, globals())
    app.run()




