Exemplo n.º 1
0
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>"
Exemplo n.º 2
0
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
Exemplo n.º 3
0
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") 
Exemplo n.º 4
0
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
Exemplo n.º 5
0
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
Exemplo n.º 6
0
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
Exemplo n.º 7
0
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
Exemplo n.º 8
0
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>"
Exemplo n.º 9
0
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 ""
Exemplo n.º 10
0
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>"
Exemplo n.º 11
0
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
Exemplo n.º 12
0
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>")
Exemplo n.º 13
0
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
Exemplo n.º 14
0
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>"
Exemplo n.º 15
0
def connect():
    return session.cursor(session.connect())
Exemplo n.º 16
0
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>"
Exemplo n.º 17
0
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>"
Exemplo n.º 18
0
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>"