#!/user/bin/python
# -*- coding: utf-8 -*-
import bottle
import bottle_mysql
import re
import requests
import urllib2
import webbrowser
from array import *
from datetime import datetime
from pprint import pprint
from bottle import static_file, route, run, request,response, template, static_file
from urllib2 import urlopen 

app = bottle.Bottle()
plugin = bottle_mysql.Plugin(dbuser='user', dbpass="", dbname='nordicFantasy')
app.install(plugin)

@route('/<filename>.css')
def stylesheets(filename):
	print("help me")
	return static_file('{}.css'.format(filename), root='static')

@route('/static/<filename>')
def server_static(filename):
	print("something might be working")
	return static_file(filename, root='/static/')

@app.route('/loginn')
def show(db):
	pprint("login")
	return bottle.template("loginPage")

@route('/static/:path#.+#', name='static')
def static(path):
	return static_file(path, root='static')

#@app.route('/myTeam', method="POST")
@app.route('/myTeam')
def show(db):
	cookie = getCookie()
	print("myTeam")
	#getting all of the teams
	mysqlCode = ("select userID, league1, team1 from myUser where userName = '%s';" % cookie[0])
	pprint(mysqlCode)
	db.execute(mysqlCode)
	data = db.fetchall()
		
#getting league name
	if data[0]["league1"]:
		code = str(data[0]["league1"])
		print(code)
		sqlCode = ("select leagueName from myLeague where leagueID = '%s';" % code)
		pprint(sqlCode)
		db.execute(sqlCode)
		leagueName = db.fetchall()
		leagueName = leagueName[0]["leagueName"]
	else:
		leagueName = "You are not apart of a team"

#if you don't have a team you need to create a team.
	if data[0]["team1"]:
		pass
	else:
		print("creating a new team")
		sqlCode = ("insert into myTeam(userID,type) values(%s, 'standard');" % data[0]["userID"])
		db.execute(sqlCode)
		sqlCode = ("select teamID from myTeam where userID = %s;" % data[0]["userID"])
		db.execute(sqlCode)
		teamID = db.fetchall()
		sqlCode = ("update myUser set team1 = %s where userName = '%s';" % (teamID[0]["teamID"], cookie[0]))
		db.execute(sqlCode)

#getting team values
	code = str(data[0]["userID"])
	mysql = ("select * from myTeam where userID = '%s';" % code)
	db.execute(mysql)
	myTeam = db.fetchall() 

	return bottle.template("myTeam", data = myTeam, leagueName = leagueName)

@app.route('/helpme')
def show(db):
	girls = getID('https://data.fis-ski.com/cross-country/cup-standings.html?suchen=true&export_standing=&suchcompetitorid=&suchseason=2017&suchgender=L&suchnation=&sector=CC&suchcup=WC&discipline=ALL&search=Search')
	guys = getID('https://data.fis-ski.com/cross-country/cup-standings.html?suchen=true&export_standing=&suchcompetitorid=&suchseason=2017&suchgender=M&suchnation=&sector=CC&suchcup=WC&discipline=ALL&search=Search')
	size = len(girls)
	i = 0
	while i < size:
		mySqlCode = ("insert into myGirlSkiers(id,athlete,country) values (%s,%s,%s);")
		data2 = (girls[i][0],girls[i][1],girls[i][2])
		db.execute(mySqlCode, data2)
		i = i +1
	size = len(guys)
	i = 0
	while i < size:
		mySqlCode = ("insert into myGuySkiers(id,athlete,country) values (%s,%s,%s);")
		data2 = (guys[i][0],guys[i][1],guys[i][2])
		db.execute(mySqlCode, data2)
		i = i +1

	return bottle.template("helpme", girls = girls, guys = guys)

@app.route('/editTeam', method ="POST")
def show(db):
	cookie = getCookie()
	print("myTeam")
	print("this is the date ", datetime.date)
	addGuys = request.POST.getlist('addMaleSkier')
	addGirls = request.POST.getlist('addFemaleSkier')
	editM1 = request.forms.getlist('editM1')
	editM2 = request.forms.getlist('editM2')
	editM3 = request.forms.getlist('editM3')
	editM4 = request.forms.getlist('editM4')
	editM5 = request.forms.getlist('editM5')
	editW1 = request.forms.getlist('editF1')
	editW2 = request.forms.getlist('editF2')
	editW3 = request.forms.getlist('editF3')
	editW4 = request.forms.getlist('editF4')
	editW5 = request.forms.getlist('editF5')
	print("this is add guys", addGuys)
	print("this is add girls", addGirls)
	#getting all of the teams
	mysqlCode = ("select userID, league1 from myUser where userName = '%s';" % cookie[0])
	pprint(mysqlCode)
	db.execute(mysqlCode)
	data = db.fetchall()
		
#removing atheltes
	if editM1:
		sql = ("update myTeam set mName1 = null where mName1 = '%s'" % editM1[0]) 
		sql += (" and userID = %s;" % data[0]["userID"])
		db.execute(sql)
	if editM2:
		sql = ("update myTeam set mName2 = null where mName2 = '%s'" % editM2[0]) 
		sql += (" and userID = %s;" % data[0]["userID"])
		db.execute(sql)
	if editM3:
		sql = ("update myTeam set mName3 = null where mName3 = '%s'" % editM3[0]) 
		sql += (" and userID = %s;" % data[0]["userID"])
		db.execute(sql)
	if editM4:
		sql = ("update myTeam set mName4 = null where mName4 = '%s'" % editM4[0]) 
		sql += (" and userID = %s;" % data[0]["userID"])
		db.execute(sql)
	if editM5:
		sql = ("update myTeam set mName5 = null where mName5 = '%s'" % editM5[0]) 
		sql += (" and userID = %s;" % data[0]["userID"])
		db.execute(sql)
	if editW1:
		sql = ("update myTeam set wName1 = null where wName1 = '%s'" % editW1[0]) 
		sql += (" and userID = %s;" % data[0]["userID"])
		db.execute(sql)
	if editW2:
		sql = ("update myTeam set wName2 = null where wName2 = '%s'" % editW2[0]) 
		sql += (" and userID = %s;" % data[0]["userID"])
		db.execute(sql)
	if editW3:
		sql = ("update myTeam set wName3 = null where wName3 = '%s'" % editW3[0]) 
		sql += (" and userID = %s;" % data[0]["userID"])
		print("dis da edit ", editW3[0])
		db.execute(sql)
	if editW4:
		sql = ("update myTeam set wName4 = null where wName4 = '%s'" % editW4[0]) 
		sql += (" and userID = %s;" % data[0]["userID"])
		db.execute(sql)
	if editW5:
		sql = ("update myTeam set wName5 = null where wName5 = '%s'" % editW5[0]) 
		sql += (" and userID = %s;" % data[0]["userID"])
		db.execute(sql)

#adding athletes
	code = str(data[0]["userID"])
	mysql = ("select * from myTeam where userID = '%s';" % code)
	db.execute(mysql)
	myTeam = db.fetchall() 
	size = len(addGuys)
	i = 0
	while i < size:
		print("add guys", addGuys[i])
		i = i +1

	if myTeam[0]["mName1"]: 
		pass
	else:
		if addGuys:
			sql = ("update myTeam set mName1 = '%s' where " % addGuys[0]) 
			sql +=("userID = %s;" % data[0]["userID"])
			db.execute(sql)
			addGuys.pop(0)
	if myTeam[0]["mName2"]:
		pass
	else:
		if addGuys:
			sql = ("update myTeam set mName2 = '%s' where " % addGuys[0]) 
			sql += ("userID = %s;" % data[0]["userID"])
			db.execute(sql)
			addGuys.pop(0)

	if myTeam[0]["mName3"]:
		pass
	else:
		if addGuys:
			sql = ("update myTeam set mName3 = '%s' where " % addGuys[0]) 
			sql += ("userID = %s;" % data[0]["userID"])
			db.execute(sql)
			addGuys.pop(0)

	if myTeam[0]["mName4"]:
		pass
	else:
		if addGuys:
			sql = ("update myTeam set mName4 = '%s' where " % addGuys[0]) 
			sql += ("userID = %s;" % data[0]["userID"])
			db.execute(sql)
			addGuys.pop(0)
	
	if myTeam[0]["mName5"]:
		pass
	else:
		if addGuys:
			sql = ("update myTeam set mName5 = '%s' where " % addGuys[0]) 
			sql += ("userID = %s;" % data[0]["userID"])
			db.execute(sql)
			addGuys.pop(0)
#adding girls
	if myTeam[0]["wName1"]: 
		pass
	else:
		if addGirls:
			sql = ("update myTeam set wName1 = '%s' where " % addGirls[0]) 
			sql +=("userID = %s;" % data[0]["userID"])
			db.execute(sql)
			addGirls.pop(0)
	if myTeam[0]["wName2"]:
		pass
	else:
		if addGirls:
			sql = ("update myTeam set wName2 = '%s' where " % addGirls[0]) 
			sql += ("userID = %s;" % data[0]["userID"])
			db.execute(sql)
			addGirls.pop(0)

	if myTeam[0]["wName3"]:
		pass
	else:
		if addGirls:
			sql = ("update myTeam set wName3 = '%s' where " % addGirls[0]) 
			sql += ("userID = %s;" % data[0]["userID"])
			db.execute(sql)
			addGirls.pop(0)

	if myTeam[0]["wName4"]:
		pass
	else:
		if addGirls:
			sql = ("update myTeam set wName4 = '%s' where " % addGirls[0]) 
			sql += ("userID = %s;" % data[0]["userID"])
			db.execute(sql)
			addGirls.pop(0)
	
	if myTeam[0]["wName5"]:
		pass
	else:
		if addGirls:
			sql = ("update myTeam set wName5 = '%s' where " % addGirls[0]) 
			sql += ("userID = %s;" % data[0]["userID"])
			db.execute(sql)
			addGirls.pop(0)

#		mySqlCode = ("insert into myTeam(id,athlete,country) values (%s,%s,%s);")
#		data2 = (guys[i][0],guys[i][1],guys[i][2])
#		db.execute(mySqlCode, data2)


#getting league name
	code = str(data[0]["league1"])
	print(code)
	sqlCode = ("select leagueName from myLeague where leagueID = '%s';" % code)
	pprint(sqlCode)
	db.execute(sqlCode)
	leagueName = db.fetchall()
	if leagueName:
		leagueName = leagueName[0]["leagueName"]
	else:
		leagueName = "you are not part of a league"

#getting team values
	code = str(data[0]["userID"])
	mysql = ("select * from myTeam where userID = '%s';" % code)
	db.execute(mysql)
	myTeam = db.fetchall() 

#getting atheletes 
	db.execute("select * from myGirlSkiers;")
	dataGirl = db.fetchall()
	db.execute("select * from myGuySkiers;")
	dataGuy = db.fetchall()
	
	return bottle.template("editTeam", data = myTeam, leagueName = leagueName, guySkiers = dataGuy, girlSkiers = dataGirl)

@app.route('/editUser')
def show(db):
	userName = request.get_cookie("userName", secret="batman")
	data = userName
	return bottle.template("editUser", data = data)

@app.route('/edit', method="POST")
def show(db):
	userName = request.get_cookie("userName", secret="batman")
	newName = request.forms.get('newName')
	oldPass1 = request.forms.get('oldPass1')
	oldPass2 = request.forms.get('oldPass2')
	newPass = request.forms.get('newPass')

	cookie = getCookie()
# getting the password
	getPass = ("select password from myUser where userName = '%s';" %userName)
	print(getPass , userName)
	db.execute(getPass)
	results = db.fetchall()
	userN = " "

	passW = str(results[0]["password"])	
	print("oldPass ",passW)

	if oldPass1 == passW:
		if oldPass2 == passW:
			mysql = ("update myUser set password = '%s' where" % newPass) 
			mysql += (" userName = '%s';" % userName)
			print(mysql)
			db.execute(mysql)
# getting the newUserName
	print("getting newUserName")
	getName = ("select * from myUser where userName = '%s';" %newName)
	db.execute(getName)
	name = db.fetchall()
	print("this is name ", name)
	if newName:
		if name:
			print("the username is taken")
		else:
			print("the username is not taken")
			mysql = ("update myUser set userName = '%s' " % newName)
			mysql += ("where userName = '%s';" % userName)
			print(mysql)
			db.execute(mysql)
			userName = newName
			response.set_cookie("userName", userName, secret='batman')

	return bottle.template("editUser", data = userName)

@app.route('/deleteUser', method= "POST")
def show(db):
	cookie = getCookie()
	print("cookie ", cookie[0])

	return bottle.template("loginPage")

@app.route('/loginPage', method= "POST")
def show(db):
	pprint("checkUser")
	userName = request.forms.get('userName')
	password = request.forms.get('password')
	pprint(userName)
	pprint(password)	
	check = ("select userName, league1, team1 from myUser where password = %s and userName = %s;")
	data = (password, userName)
	db.execute(check, data)
	results = db.fetchall()
	if results:
		color = "blue"
		info = color
#		for row in results:
		print("this is results" , results)
		info = str(results[0]["userName"])
		response.set_cookie("userName", info, secret='batman')
		info = str(results[0]["league1"])
		response.set_cookie("userLeague", info, secret='batman')
		info = str(results[0]["team1"])
		print(info)
		response.set_cookie("userTeam", info, secret='batman')
#		db.execute("select * from myTeam where teamID = '%s';" % info)
#		data = db.fetchall()
		cookie = getCookie()
		mysqlCode = ("select team1, teamW1, teamL1, points1, league1, team2, teamW2, teamL2, points2, league2 from myUser where userName = '%s';" % results[0]["userName"])
		pprint(mysqlCode)
		db.execute(mysqlCode)
		data = db.fetchall()
		leagueID1 = str(data[0]["league1"])
		mySql = ("select userName, teamW1, teamL1 from myUser where userID in (select userID from myTeam where leagueID = '%s');" % leagueID1)
		db.execute(mySql)
		userIDs = db.fetchall()
		
#getting league name
		code = str(data[0]["league1"])
		print(code)
		sqlCode = ("select leagueName from myLeague where leagueID = '%s';" % code)
		pprint(sqlCode)
		db.execute(sqlCode)
		data = db.fetchall()
		print(data)
		if data:
			data = data[0]["leagueName"]
		else:
			data = "Not apart of a league"
		pattern = '(\/article=[a-z]+(-[a-z]+)*.html">[A-Za-z]+( ([a-zA-Z]|!|:|-)*)*)'
		athletePattern = '(\/athlete=[a-z]+(-[a-z]+)*.html">[A-Za-z]+( ([a-zA-Z]|!|:|-)*)*)'
		news = getNews('http://www.fis-ski.com/cross-country//news-multimedia/news/', pattern)
		womenPastResults = getResults('http://www.fis-ski.com/cross-country/events-and-places/results/?season=2017&discipline=&gender=all&race_id=29539&sector=CC', True)
		menPastResults = getResults('http://www.fis-ski.com/cross-country/events-and-places/results/?season=2017&discipline=&gender=all&race_id=29542&sector=CC', False)
		return bottle.template("homePage", data = data, league = userIDs, news = news, resultsM = menPastResults , resultsF = womenPastResults)
#		return bottle.template("homePage", data = data)
	else:
		color = "red"
		pprint("should go to loginPage")
	pprint("yes it did work")
	return bottle.template("loginPage", color = color)


@app.route('/newUser', method="POST")
def show(db):
	pprint("newUser")
	return bottle.template("newUser")	

@app.route('/checkNewUser', method="POST")
def show(db):
	pprint("checkNewUser")
	userName2 = request.forms.get('userName')
	password2 = request.forms.get('password')
	pprint(userName2)
	mysqlCode = ("select userName from myUser where userName = '%s';" % userName2)
	pprint(mysqlCode)
	db.execute(mysqlCode)
	results = db.fetchall()
	if results:
		color = "red"
		return bottle.template("newUser")
	else:
		pattern = "^(?=.*[A-Za-z])(?=.*\d)(?=.*[$@$!%*#?&])[A-Za-z\d$@$!%*#?&]{8,16}"
		passwordValidated = re.match(pattern, password2)
		color = "red"
		if passwordValidated:
			color = "green"
			check =("insert into myUser(userName, password) values('%s', '%s');" % (userName2, password2))
			pprint(check)
			db.execute(check)
			response.set_cookie("userName", userName2, secret='batman')
			response.set_cookie("userLeague", "", secret='batman')
			response.set_cookie("userTeam", "", secret='batman')
		#	leagueID1 = str(data[0]["league1"])
		
#getting league name
			league = ""
			data = "Not Apart of a League"
			pattern = '(\/article=[a-z]+(-[a-z]+)*.html">[A-Za-z]+( ([a-zA-Z]|!|:|-)*)*)'
			athletePattern = '(\/athlete=[a-z]+(-[a-z]+)*.html">[A-Za-z]+( ([a-zA-Z]|!|:|-)*)*)'
			news = getNews('http://www.fis-ski.com/cross-country//news-multimedia/news/', pattern)
			womenPastResults = getResults('http://www.fis-ski.com/cross-country/events-and-places/results/?season=2017&discipline=&gender=all&race_id=29539&sector=CC', True)
			menPastResults = getResults('http://www.fis-ski.com/cross-country/events-and-places/results/?season=2017&discipline=&gender=all&race_id=29542&sector=CC', False)
			return bottle.template("homePage", data = data, league = league,  news = news, resultsM = menPastResults , resultsF = womenPastResults)

		else:
			color = "didn't pass password validation"
			return bottle.template("newUser")
	
	return bottle.template("homePage")
	
@app.route('/checkNewLeague', method ="POST")
def show(db):
	pprint("checkNewLeague")
	name = request.forms.get('name')
	password = request.forms.get('password')
	type = request.forms.get('type')
	pprint(name)
	pprint(password)
	pprint(type)
	mysqlCode = ("select leagueName from myLeague where leagueName = '%s';" % name)
	db.execute(mysqlCode)
	results = db.fetchall()
	if results:
		color = "red"
		return bottle.template("createLeague")
	else:
		check = ("insert into myLeague(leagueName,password,type) values(%s,%s,%s);")
		data =(name, password, type)
		db.execute(check, data)
	return bottle.template("homePage")

@app.route('/homePage', method = "POST")
def show(db):
	pprint("homePage")
	cookie = getCookie()
	userName = cookie[0]
	userLeague = cookie[1]
	userTeam= cookie[2]
	print("this is userName: ",userName)
	print("this is userLeague: ",userLeague)
	print("this is userTeam: ",userTeam)
	return bottle.template("homePage")

@app.route('/homePage2')
def show(db):
	pprint("homePage")
	cookie = getCookie()
	mysqlCode = ("select team1, teamW1, teamL1, points1, league1, team2, teamW2, teamL2, points2, league2 from myUser where userName = '%s';" % cookie[0])
	pprint(mysqlCode)
	db.execute(mysqlCode)
	data = db.fetchall()
	leagueID1 = str(data[0]["league1"])
	mySql = ("select userName, teamW1, teamL1 from myUser where userID in (select userID from myTeam where leagueID = '%s');" % leagueID1)
	db.execute(mySql)
	userIDs = db.fetchall()
		
#getting league name
	code = str(data[0]["league1"])
	print(code)
	sqlCode = ("select leagueName from myLeague where leagueID = '%s';" % code)
	pprint(sqlCode)
	db.execute(sqlCode)
	data = db.fetchall()
	print(data)
	if data:
		data = data[0]["leagueName"]
	else:
		data = "Not apart of a league"
	pattern = '(\/article=[a-z]+(-[a-z]+)*.html">[A-Za-z]+( ([a-zA-Z]|!|:|-)*)*)'
	athletePattern = '(\/athlete=[a-z]+(-[a-z]+)*.html">[A-Za-z]+( ([a-zA-Z]|!|:|-)*)*)'
	news = getNews('http://www.fis-ski.com/cross-country//news-multimedia/news/', pattern)
	womenPastResults = getResults('http://www.fis-ski.com/cross-country/events-and-places/results/?season=2017&discipline=&gender=all&race_id=29539&sector=CC', True)
	menPastResults = getResults('http://www.fis-ski.com/cross-country/events-and-places/results/?season=2017&discipline=&gender=all&race_id=29542&sector=CC', False)
	return bottle.template("homePage", data = data, league = userIDs, news = news, resultsM = menPastResults , resultsF = womenPastResults)

@app.route('/updateWorldCupPoints')
def show(db):
#update the worldcup athelete page
	nation = nations()	
	point = points()
	name = names()
	namesID = nameID()
	size = len(name)
	i = 0
	while i < size:
		mySqlCode = ("insert into mySkiers(id,athlete,country,points) values (%s,%s,%s,%s);")
		data2 = (namesID[i],name[i],nation[i],point[i])
		db.execute(mySqlCode, data2)
		i = i +1
	return bottle.template("updateWorldCupPoints")

@app.route('/leaguePage')
def show(db):
	pprint("leaguePage")
	db.execute("select leagueName, password  from myLeague order by leagueName;")
	data = db.fetchall()
	userName = request.get_cookie("userName", secret="batman")
	userLeague = request.get_cookie("userLeague", secret="batman")
	userTeam= request.get_cookie("userTeam", secret="batman")
	print("this is userName: ",userName)
	print("this is userLeague: ",userLeague)
	print("this is userTeam: ",userTeam)
	print("this is data", data)
	return bottle.template("leaguePage", data = data)

@app.route('/createLeague', method="POST")
def show(db):
	print("createLeague")
	return bottle.template("createLeague")
@app.route('/leaveLeague', method="POST")
def show(db):
	print("leave league")
	cookie = getCookie()
#	userID = getuserID(cookie[0], db)

	mySql = ("select league1, userID, team1 from myUser where userName = '%s';" % cookie[0])
	db.execute(mySql)
	data = db.fetchall()
	league = (data[0]["league1"])
	user = (data[0]["userID"])
	team = (data[0]["team1"])

	print("dis d0 data[0]", data)
# from myLeague remove userID#  where userID matchs from myUser
#	sql =("select * from myLeague where leagueID = %s;" % league)
#	db.execute(sql)
#	data = db.fetchall()
#	if data[0]["userID1"] == user:
#		sql = ("update myLeague userID1 = null where userID1 = '%s' and leagueID = 's';" % (user, league))
#	if data[0]["userID2"] == user:
#	if data[0]["userID3"] == user:
#	if data[0]["userID4"] == user:
#	if data[0]["userID5"] == user:
#	if data[0]["userID6"] == user:
#	if data[0]["userID7"] == user:
#	if data[0]["userID8"] == user:
#	if data[0]["userID9"] == user:
#	if data[0]["userID10"] == user:
#	if data[0]["userID11"] == user:
#	if data[0]["userID12"] == user:
#	sql = ("update myLeague league1 = null where userName = '%s';" % cookie[0])
# leagueID from myTeam    getting leagueID from myUser
	mySql =("update myTeam set leagueID = null where teamID = '%s';" % team)
	db.execute(mySql)
# leage1 from myUser  from userName
	mySql =("update myUser set league1 = null where userName = '%s';" % cookie[0])
	db.execute(mySql)


@app.route('/joinLeague', method="POST")
def show(db):
	cookie = getCookie()
	userID = getuserID(cookie[0], db)
	password = request.forms.get('password')
	name = request.forms.get('name')
	mysql = ("select league1, team1, league2, team2, leagueID from myUser, myLeague where userName ='%s' and leagueName ='%s';" % (cookie[0], name))
	print("join league mysql ", mysql)
	db.execute(mysql)
	data = db.fetchall()
	
	if data[0]["league1"]:
		if data[0]["league2"]:
			pass
		else:
			pass
		#	mysql = ("update myUser set league2 = %s where userName = '%s';" % (data[0]["leagueID"], cookie[0])) 
		#	db.execute(mysql)
		#	if data[0]["team2"]:
			#in myteam set leagueID to leagueID
		#		mysql = ("update myTeam set leagueID= %s where teamID= %s;" % (data[0]["leagueID"], data[0]["team2"]))
		#		db.execute(mysql)
	else:
	#in myUser set league1 to leagueID,
		mysql = ("update myUser set league1 = %s where userName = '%s';" % (data[0]["leagueID"], cookie[0])) 
		db.execute(mysql)
		if data[0]["team1"]:
			#in myteam set leagueID to leagueID
			mysql = ("update myTeam set leagueID= %s where teamID= %s;" % (data[0]["leagueID"], data[0]["team1"]))
			db.execute(mysql)
# getting the leagueID
		mysqlCode = ("select team1, teamW1, teamL1, points1, league1, team2, teamW2, teamL2, points2, league2 from myUser where userName = '%s';" % cookie[0])
		pprint(mysqlCode)
		db.execute(mysqlCode)
		data = db.fetchall()

# getting the other members of the league and standings
		leagueID1 = str(data[0]["league1"])
		mySql = ("select userName, teamW1, teamL1 from myUser where userID in (select userID from myTeam where leagueID = '%s');" % leagueID1)
		db.execute(mySql)
		userIDs = db.fetchall()

# getting the League Name
		sqlCode = ("select leagueName from myLeague where leagueID = '%s';" % leagueID1)
		pprint(sqlCode)
		db.execute(sqlCode)
		data = db.fetchall()

		return bottle.template("myLeague", data = leagueID1, league = userIDs)

	db.execute("select leagueName, password  from myLeague order by leagueName;")
	data = db.fetchall()
	return bottle.template("leaguePage", data = data)

#leagueID from myTeam
def getuserID(a, db):
	mysql = ("select userID from myUser where userName = '%s';" % a)
	print("get userID", mysql)
	db.execute(mysql)
	data = db.fetchall()
	print(data)
	return data
		

@app.route('/myLeague')
def show(db):
	print("myLeague")
	cookie = getCookie()

# getting the leagueID
	mysqlCode = ("select team1, teamW1, teamL1, points1, league1, team2, teamW2, teamL2, points2, league2 from myUser where userName = '%s';" % cookie[0])
	pprint(mysqlCode)
	db.execute(mysqlCode)
	data = db.fetchall()

# getting the other members of the league and standings
	leagueID1 = str(data[0]["league1"])
	mySql = ("select userName, teamW1, teamL1 from myUser where userID in (select userID from myTeam where leagueID = '%s');" % leagueID1)
	db.execute(mySql)
	userIDs = db.fetchall()

# getting the League Name
	sqlCode = ("select leagueName from myLeague where leagueID = '%s';" % leagueID1)
	pprint(sqlCode)
	db.execute(sqlCode)
	data = db.fetchall()

	return bottle.template("myLeague", data = leagueID1, league = userIDs)

@app.route('/createLeague')
def show(db):
	pprint("createLeague")
	webbrowser.open('http://euclid.nmu.edu/~selfstro/seniorProject/createLeague.html')
	return bottle.template("createLeague")

@app.route('/skierPage')
def show(db):
	pprint("skierPage")
	db.execute("select * from mySkiers;")
	data = db.fetchall()
	db.execute("select distinct country from mySkiers order by country;")
	nation = db.fetchall()
	return bottle.template("skierPage", data = data, nation = nation)

@app.route('/filterSkiers', method="POST")
def show(db):
#	country = request.forms.get('country')
	country = request.POST.getlist('country')
	print(country)
	size = len(country)
	print(size)
	tmp = country[0]
	sql = ("select * from mySkiers where country = '%s'" %tmp)
	i = 1
	while i < size:
		print(country[i])
		tmp = country[i]
		sql += (" or country = '%s'" %tmp)
		i = i + 1
#		db.execute(check, data)
	sql += ";"
	print(sql)
#	db.execute("select * from mySkiers where country = '%s';" % country[0])
	db.execute(sql)
	data = db.fetchall()
	db.execute("select distinct country from mySkiers order by country;")
	nation = db.fetchall()
	return bottle.template("skierPage", data = data, nation = nation)

def getID(a):
	url = a
	response = urlopen(url)
	data = str(response.read())	
	pattern = "(competitorid=[0-9]{4,6})"
	secondP = "([0-9]{4,9})"
	id = re.findall(pattern,data)
#	ids = createList(id)
	round2 = ' '.join(id)
	finalID = re.findall(secondP, round2)
	print ("this is finalID", finalID)
	
	pat = '([A-Z]+( [A-Za-z-.]+)+</a>&nbsp)'
	r2 = re.findall(pat,data)
	round2 = createList(r2)
	round2= ' '.join(round2)
	newPat = '([A-Z]+( [A-Za-z-.]+)+)'
	names = re.findall(newPat, round2)
	finalNames = createList(names)
	print("dis name ", finalNames)
	
	#get nation
	pattern = '(flag-[A-Z]{3})'
	r3 = re.findall(pattern, data)
	round3 = ' '.join(r3)
	lastPat = '[A-Z]{3}'
	nation = re.findall(lastPat, round3)
	print("this is nations ", nation)
	final = zip(finalID,finalNames,nation)
	return final

def altElement(a):
	newList = a[::2]
	return newList
	
def createList(a):
	i = 0
	newlist=[]
	temp = ""
	size = len(a)
	size = size
	while i < size:
		print(a[i][0])
		temp = a[i][0]
		newlist.insert(i,temp)
		i= i+1
	return newlist

def create2List(a,b):
	i = 0
	newlist=[]
	temp = ""
	size = len(a)
	size = size
	while i < size:
		print(a[i][0])
		temp = a[i][0]
		temp2 = b[i][0]
		newlist.insert(temp,temp2)
		i= i+1
	return newlist

def alt(a):
	newList = a[::16]
	return newList

def getURL():
	url = "http://www.fis-ski.com/cross-country/leader-board/"
	response = urlopen(url)
	data = str(response.read())	
	return data

def getResults(url, bibs):

	response = urlopen(url)
	data = str(response.read())
	firstGet = '(=[a-z]+-([a-z]+( |-))+[0-9]{4,6}\/" style="display: inline;line-height: 20px; margin-left: 5px;">[A-Z]+( [A-Za-z]+)+)'
	pattern2 = '([a-z]+-([a-z]+( |-))+[0-9]{4,6})'
	pattern = '([A-Z]+( [A-Za-z]+)+)'
	placePat = '(<td>[0-9]+<)'
	placeFinal = '([0-9]+)'
#	first filter
	id = re.findall(firstGet,data)
	ids = createList(id)
	round2 = ' '.join(ids)
	print("this is round2! ", round2)

	place = re.findall(placePat, data)
	print("this one works", place)
	
	lastPlace = ' '.join(place)
	lastReg = re.findall(placeFinal, lastPlace)
	place = lastReg
	if bibs:
		#do have bibs take out everyother
		place = place[::2]

	print("this is places ",place)
#	regex the filter string
	nams = re.findall(pattern, round2)
	ids = re.findall(pattern2, round2)

	#create lists
	id = createList(ids)
	name = createList(nams)
	print("this is id ", id)
	print("this is name ", name)
	print("this is palce ", place)
	value = zip(place,id,name)		
	return value

def getNews(url,pattern):
#	url = 'http://www.fis-ski.com/cross-country//news-multimedia/news/'
	response = urlopen(url)
	data = str(response.read())
#	pattern = '(\/article=[a-z]+(-[a-z]+)*.html">[A-Za-z]+( ([a-zA-Z]|!|:|-)*)*)'
	firstFind = re.findall(pattern, data)
	print(firstFind)
	list = createList(firstFind)
	print(list)
	string = ' '.join(list)
	print(string)

	htmlPattern = '([a-z]+(-[a-z]+)*.html)'
	htmlList = re.findall(htmlPattern, string)
#	htmlList = createList(htmlList)
	print("html list ", htmlList)

	titlePattern = '([A-Z]([A-Za-z])*( ([A-Za-z\såäö]|:|!)+)*)'
	titleList = re.findall(titlePattern, string)
	htmlL = createList(htmlList)
	titleL = createList(titleList)
	news = zip(htmlL, titleL)
	print(news)
	return news
def nations():
#nations
	data = getURL()
	patternNation = '(title="[A-Z][a-z]+)'
	reFactorNation = '([A-Z][a-z]+)'
	wcp = re.findall(patternNation, data)	
	newthing = ' '.join(wcp)
	nations = re.findall(reFactorNation, newthing)
	nations.pop(0)
	nations.pop(0)
	nations.pop(0)
	nations.pop(0)
	return nations

def points():
#world cup points
	patternWCP = '(class="dcm-tcr">[0-9]{0,4})'
	reFactorWCP = '([0-9]{1,4})'
	data = getURL()
	points = re.findall(patternWCP , data)
	tmp = ' '.join(points)
	cupPoints = re.findall(reFactorWCP, tmp)
	return cupPoints

def names():
#name
	data = getURL()
	pattern = '([A-Z]+(  [A-Za-z|-]+)+)'
	names = re.findall(pattern, data)
	justNames = createList(names)
	jNames = altElement(justNames)
	return jNames

def nameID():
#names+ID
	data = getURL()
	pattern2 = '([a-z]+(-[a-z|-]+)+-[0-9]{4,6})'
	id = re.findall(pattern2,data)
	ids = createList(id)
	ids.pop(0)
	ids.pop(0)
	ids.pop(0)
	ids.pop(0)
	ids.pop(0)
	ids.pop(0)
	ids.pop(-1)
	return ids

def getCookie():
	userName = request.get_cookie("userName", secret="batman")
	userLeague = request.get_cookie("userLeague", secret="batman")
	userTeam= request.get_cookie("userTeam", secret="batman")
	print("this is userName: ",userName)
	print("this is userLeague: ",userLeague)
	print("this is userTeam: ",userTeam)
	cookie = [ userName, userLeague, userTeam]
	print(cookie[0])
	print(cookie[1])
	print(cookie[2])
	return cookie


app.run(host='euclid.nmu.edu',port= 5678)