def addMember(tid,email,type):
curs = session.cursor(session.connect())
if not existsUser(email):
return "<div class='alert alert-danger'> Account with this email does not exist</div>"
#TODO send email with invitation to application?
elif not existsTeam(tid):
return "<div class='alert alert-danger'> Team with that id does not exist</div>"
else:
curs.execute('SELECT UID from user where email=%s', (email,))
row = curs.fetchone()
pid = row['UID']
if (type == "player"):
#We should probably check to see whether this player's already on the team's roster
#insert player into player table
curs.execute('INSERT into player(PID,team) values(%s,%s)',
(pid,tid))
return "<div class='alert alert-success'>Added player to the team! <div>"
elif (type == "coach"):
#update team to have a different coach
curs.excute('INSERT into coach(CID,team) values(%s,%s)',(pid,tid))
return "<div class='alert alert-success'>Added coach to the team! <div>"
elif (type == "manager"):
curs.execute('UPDATE team set manager = %s',(pid))
return "<div class='alert alert-success'>Manager changed for the team! <div>"
def printTeamTable(id):
curs = session.cursor(session.connect())
#execute the SQL query that retrieves the events associated with the team
curs.execute('SELECT * FROM event, team WHERE host_id = %s and TID = %s', (id, id))
# HTML Formatting below
header = "<div class=\"panel panel-default\"><div class='panel-heading'> Events for Team no. " + str(id) + "</div>"
tableHead = "<table class=\"table table-striped\"> <thead> <tr> \n <th> Team Name </th> \n <th> Location </th> \n <th> Event Date </th> \n <th> </th> </tr> </thead>"
tableEnd = "</table></div>"
lines = []
while True:
row = curs.fetchone()
print "<p>curs.fetchone: " #debugging
print row #debugging
if row == None:
# print "<h2> Events </h2>" + "\n".join(lines) #debugging
return header + tableHead + "\n".join(lines) + tableEnd
lines.append("<tr>" + "<td>" + str(row.get('name')) + "</td>") #displays the hosting team
lines.append("<td>" + str(row.get('location')) + "</td>") #displays the event location
lines.append("<td>" + str(row.get('event_date')) + "</td>") #displays the event date
lines.append("<td><form method=\"post\" action=\"viewEvents.cgi\" class=\"form-inline\">")
lines.append("<input type=\"submit\" name=\"submit\" value=\"View Attendance\" class=\"btn\">")
lines.append("<input type=\"hidden\" name=\"event\" value=\"" + str(row.get('EID')) + "\">\n</form></td></tr>\n") #view attending button
def printUserTable(id):
curs = session.cursor(session.connect())
#execute the SQL query
curs.execute('SELECT * FROM (SELECT event.EID, host_id, location, event_date, event_name, status FROM event,(SELECT * FROM attend WHERE UID = %s) as userEv where userEv.EID = event.EID) as ev, team where ev.host_id = TID', (id,))
# HTML Formatting below
header = "<div class=\"panel panel-default\"><div class='panel-heading'> Events for User no. " + str(id) + "</div>"
tableHead = "<table class=\"table table-striped\"> <thead> <tr> \n <th> Team Name </th> \n <th> Location </th> \n <th> Event Date </th> \n <th> Attend?</th> </tr> </thead>"
tableEnd = "</table></div>"
lines = []
while True:
row = curs.fetchone()
'''Advanced functionality of this would include using JSON to
provide a sortable view of the events. We can implement this
in the future.'''
if row == None:
# print "<h2> Events </h2>" + "\n".join(lines) #debugging
return header + tableHead + "\n".join(lines) + tableEnd
lines.append("<tr>" + "<td>" + str(row.get('name')) + "</td>") #displays the hosting team
lines.append("<td>" + str(row.get('location')) + "</td>") #displays the event location
lines.append("<td>" + str(row.get('event_date')) + "</td>") #displays the event date
lines.append(printAttendRadio(id,str(row.get('EID')),str(row.get('status'))) + "</td></tr>\n")
def existsUser(email):
curs = session.cursor(session.connect())
curs.execute('Select UID from user where email=%s',(email,))
row = curs.fetchone()
if row == None:
return False
else: return True
def existsTeam(team):
curs = session.cursor(session.connect())
curs.execute('Select TID from team where TID=%s',(team,))
row = curs.fetchone()
if row == None:
return False
else: return True
def delete():
TID = session.getTeamFromSession()
curs = session.cursor(session.connect())
curs.execute('DELETE from team where TID=%s',(TID,))
curs.execute('DELETE from player where team=%s',(TID,))
curs.execute('DELETE from coach where team=%s',(TID,))
curs.execute('UPDATE session set TID=NULL where TID=%s',(TID))
line = "<div class='alert alert-danger'> Team deleted \t <a href='userDashboard.cgi'>Return to Dashboard</a></div>"
return line
def delete():
UID = session.getUserFromSession()
curs = session.cursor(session.connect())
curs.execute('DELETE from user where UID=%s',(UID,))
curs.execute('DELETE from userpass where id=%s',(UID,))
curs.execute('DELETE from player where PID=%s',(UID,))
curs.execute('DELETE from coach where CID=%s',(UID,))
curs.execute('UPDATE team set manager=NULL where manager=%s',(UID,))
line = "<div class='alert alert-danger'> Account deleted \t <a href='logout.cgi'>Return to Login</a> </div>"
return line
def printAttendance(form_data):
EID = form_data.getfirst("event")
curs = session.cursor(session.connect())
curs.execute('SELECT * from attend where EID = %s', (EID,)) #Make this so it generates the name
response = "<br>Event Name <br> <table class='table table-striped'> <tr><thead> <th> User ID </th> <th> Attend? </th> </thead> </tr>"
while True:
row = curs.fetchone()
if row == None:
return response
response += "<tr><td>UID: " + str(row.get("UID")) + "</td><td> status: " + str(row.get("status")) + "</td></tr>"
def getEvent(TID,UID,view):
curs = session.cursor(session.connect())
#print "<p> right outside of checking for view: " #debugging
#print view #debugging
#user event query
if (view == "team"):
return printTeamTable(TID)
elif (view == "user"):
return printUserTable(UID)
elif (view == "userteam"):
return printUserTeamTable(UID,TID)
else:
return ""
def createTeam(manager,name,loc):
curs = session.cursor(session.connect())
curs.execute('INSERT INTO team(manager,name, location) VALUES(%s,%s,%s)',(manager, name, loc))
curs.execute('SELECT TID from team where TID=LAST_INSERT_ID()')
TID = curs.fetchone().get("TID")
session.setTeam(TID)
curs.execute('INSERT INTO player(PID,team) values(%s,%S)',(manager,TID))
if (name != "None"):
return "<div class='alert alert-success'>Your team <em>" + name + "</em> has been created. Now you can <a href='./dashboard.cgi?TID="+str(TID)+"'>manage your team</a>!</div>"
else:
return "<div class='alert alert-danger'> Uh oh! Something went wrong when creating your team. Double check your inputs!</div>"
def createEvent(host,date,name,location):
curs = session.cursor(session.connect())
#doesn't check whether an event is duplicated since steams may sometimes hold simultaneous events.
curs.execute('INSERT INTO event(host_id, location, event_date, event_name) VALUES(%s,%s,%s,%s)',(host,location,date,name))
# retrieves the EID of the event
curs.execute('SELECT LAST_INSERT_ID()')
row = curs.fetchone()
EID = row.get('LAST_INSERT_ID()') #retrieves the newly created EID
# Adds the event to the team's players' events list by automatically marking them as "yes"
curs.execute('INSERT INTO attend(EID,UID,status) SELECT %s, PID, \'y\' FROM player WHERE TEAM = %s',(EID, host))
if (location != "None"):
response = "<div class='alert alert-success'>"
response += "<p>Your event <em>" + name + "</em> on " + date + " at <em>" + location + "</em> has been created"
response +="</div>"
else:
response = "<div class='alert alert-danger'> Uh oh! Something went wrong. Check your inputs again. </div>"
return response
def getRoster(id):
curs = session.cursor(session.connect())
name = session.getTeamName()
curs.execute('select name,PID from player inner join user where PID=UID and team=%s',(id,))
header = "<div class=\"panel panel-default\"><div class='panel-heading'> Roster for team " + str(name) + "</div>"
tableHead = "<table class=\"table table-striped\"> <thead><tr> \n <th> PID </th> \n <th> Player Name </th> \n </tr></thead>"
tableEnd = "</table></div>"
lines = []
while True:
row = curs.fetchone()
if row == None:
return header + tableHead + "\n".join(lines) + tableEnd
lines.append("<tr>" + "<td>" + str(row.get('PID')) + "</td>")
lines.append("<td>" + row.get('name') + "</td>" + "</tr>")
def retrieveUser(UID):
curs = session.cursor(session.connect())
curs.execute(('Select UID,email,name,dob,phnum,nickname '
+'from user where UID=%s'),(UID,))
row = curs.fetchone()
if row == None:
print("<p> The data was not inserted correctly")
else:
line = ("<p>Inserted into the database was this user: \n "+
"<li>UID: {UID} \n"+
"<li>email: {email} \n"+
"<li>name: {name} \n"+
"<li>dob: {dob} \n"+
"<li>phnum: {phnum} \n"+
"<li>nickname: {nickname} \n").format(**row)
print line
curs.execute(('SELECT password from userpass where id=%s'),(UID,))
row2 = curs.fetchone()
if row2 == None:
print("<p> The password was not added correctly")
else:
line2 = ("<p>The password for this account is: {password}").format(**row2)
print line2
def updateAttendance(form_data):
response = form_data.getfirst("attend")
UID = form_data.getfirst("user") #gets the userID, later might not be needed because of sessions
EID = form_data.getfirst("event") #gets the eventID for updating database
# print "EID" + str(EID) #debugging
# print "UID" + str(UID) #debugging
curs = session.cursor(session.connect())
msg ="<div class='alert alert-success'>Attendance successfuly changed. "
if (response == "yes"):
msg += " You are attending to event number " + EID
#insert into the attend table that the user is attending the event
curs.execute('UPDATE attend SET status = \'y\' where EID = %s and UID = %s', (EID,UID))
#execute a sql query
if (response == "no"):
msg += " You are not attending event number " + EID
curs.execute('UPDATE attend SET status = \'n\' where EID = %s and UID = %s', (EID,UID))
if (response == "maybe"):
msg += " You may be present."
curs.execute('UPDATE attend SET status = \'m\' where EID = %s and UID = %s', (EID,UID))
return msg + "</div>"
def connect():
return session.cursor(session.connect())
def updateLocation(location):
curs = session.cursor(session.connect())
TID = session.getTeamFromSession()
curs.execute('UPDATE team set location=%s where TID=%s',(location,TID))
return "<div class='alert alert-success'>Location successfully updated!</div>"
def updateName(name):
curs = session.cursor(session.connect())
TID = session.getTeamFromSession()
curs.execute('UPDATE team set name=%s where TID=%s',(name,TID))
return "<div class='alert alert-success'>Team name successfully updated!</div>"
def updatePass(password):
curs = session.cursor(session.connect())
UID = session.getUserFromSession()
curs.execute('UPDATE userpass set password=password(%s) where id=%s',(password,UID))
return "<div class='alert alert-success'>Pasword successfully updated!</div>"
