def team_parameters():
if request.method == 'POST':
if request.form['min_size'] == '' or request.form['min_size'] == '':
error = 'Invalid parameters - empty parameters'
return render_template("set-team-parameters.html", error=error)
else:
Team.default_min_size = int(request.form['min_size'])
Team.default_max_size = int(request.form['max_size'])
if Team.default_min_size > Team.default_max_size:
error = 'Invalid parameters - minimum team size can\'t be larger than maximum team size'
return render_template("set-team-parameters.html", error=error)
db = DatabaseConnection()
with db.get_connection().cursor() as cursor:
cursor.execute("UPDATE tms.team_parameters SET max_team_size =(%s), min_team_size=(%s), are_parameters_Set=(%s)",
(Team.default_max_size, Team.default_min_size, True))
db.get_connection().close()
flash('Parameters successfully set')
g.are_team_parameters_set = True
return redirect(url_for("dashboard"))
# GET Request
else:
if g.user_type == 'instructor':
return render_template("set-team-parameters.html")
else:
return redirect(url_for("dashboard"))
def copy_data_from_csv(file_path, sql):
db = DatabaseConnection()
connection = db.get_connection()
with connection.cursor() as cursor:
with open(file_path) as f:
cursor.copy_expert(file=f, sql=sql)
def _get_postgres_version():
db = DatabaseConnection()
with db.get_connection().cursor() as cursor:
cursor.execute('SELECT version()')
db_version = cursor.fetchone()
print('PostgreSQL database version: ' + db_version[0])
def copy_data_from_event_pre_stage():
db = DatabaseConnection()
sql = """INSERT INTO accidents_weather_data_mart.event_dimension(event_key, name)
SELECT event_key, name FROM dimension_pre_stage.event_dimension_pre_stage"""
with db.get_connection().cursor() as cursor:
cursor.execute(sql)
def create_team():
if request.method == 'POST':
try:
teamName = request.form['team_name']
db = DatabaseConnection()
with db.get_connection() as con:
cursor = con.cursor()
cursor.execute("SELECT * FROM tms.team_parameters")
team_parameters = cursor.fetchone()
areTeamParametersSet = team_parameters[3]
print("ARE PARAMETERS SET: " + str(areTeamParametersSet))
if (areTeamParametersSet):
# Create the team in DB and initialize team_size as 1
cursor.execute("INSERT INTO tms.team(team_name, team_size) VALUES (%s, %s)", (teamName, 1))
# Update the values of student's is_liaison and team name
cursor.execute("UPDATE tms.student SET is_liason=(%s), team_name=(%s) WHERE email=(%s)", (True, teamName, g.user))
# Update request status to accepted
cursor.execute("UPDATE tms.team_request SET request_status=(%s) WHERE student_email=(%s)", ("accepted", g.user))
session['isLiaison'] = True # Set the session variables so that changes take effect this session
session["teamName"] = teamName
flash("Successfully created team")
else:
flash("Instructor has not yet set the team parameters")
except:
con.rollback()
# return f"{err.__class__.__name__}: {err}"
flash("Error in creating team")
finally:
con.close()
return redirect(url_for("dashboard"))
# GET Request
if ((g.user_type == "student") and (g.student_team_name == None)):
return render_template("create-team.html") # Student with no team
elif ((g.user_type == "student") and (g.student_team_name != None)):
flash("You are already part of a team!")
return redirect(url_for("dashboard")) # Student already in team, redirect
else:
return redirect(url_for("dashboard")) # user is instructor or not logged in
def _create_schemas():
print("Initializing schemas...")
db = DatabaseConnection()
connection = db.get_connection()
with connection.cursor() as cursor:
with open("db/sql/schemas.sql", "r") as f:
sql = f.read()
cursor.execute(sql)
print("Schemas successfully created.")
def _initialize_team_parameters():
print("Initializing team parameters...")
db = DatabaseConnection()
with db.get_connection().cursor() as cursor:
cursor.execute("SELECT * FROM tms.team_parameters")
if cursor.rowcount == 0:
cursor.execute(
"INSERT INTO tms.team_parameters (max_team_size, min_team_size, are_parameters_set) "
"VALUES (%s, %s, %s)", (0, 0, False))
print("Parameters successfully initialized.")
def copy_data_from_hour_pre_stage():
db = DatabaseConnection()
sql = """INSERT INTO accidents_weather_data_mart.hour_dimension(
hour_key,
hour_start,
hour_end,
date,
day_of_week,
month,
year,
is_weekend,
is_holiday,
holiday_name)
SELECT * FROM dimension_pre_stage.hour_dimension_pre_stage"""
with db.get_connection().cursor() as cursor:
cursor.execute(sql)
def insert_many(entities):
"""
Insert many entities at once to the database.
:param entities: a list of tuples of the form ->
[(hour_start, hour_end, date,day_of_week, month,year, is_weekend, is_holiday, holiday_name)]
:return: None
"""
db = DatabaseConnection()
sql_insert = """INSERT INTO dimension_pre_stage.hour_dimension_pre_stage (
hour_start,
hour_end,
date,
day_of_week,
month,
year,
is_weekend,
is_holiday,
holiday_name)
VALUES %s"""
with db.get_connection().cursor() as cursor:
execute_values(cur=cursor, sql=sql_insert, argslist=entities)
def team_visualize():
if g.user_type == "instructor":
teams = []
teams_sql = []
db = DatabaseConnection()
with db.get_connection().cursor() as cursor:
cursor.execute("SELECT * FROM tms.team")
teams_sql = cursor.fetchall()
db.get_connection().close()
for team_sql in teams_sql:
team = Team(team_sql[1])
team.team_number = team_sql[0]
team.set_max_team_size(team_sql[2])
team.set_min_team_size(team_sql[3])
team.num_team_members = team_sql[4]
teams.append(team)
return render_template("visualize-teams.html", teams=teams)
return redirect(url_for("dashboard"))
def team_requests():
if request.method == 'POST':
try:
studentRequestEmail = request.form['student_request_email'] # Get the email of the student trying to join the team
studentRequestName = request.form['student_request_name'] # Get the email of the student trying to join the team
db = DatabaseConnection()
with db.get_connection() as con:
cursor = con.cursor()
cursor.execute("SELECT * FROM tms.team_parameters")
team_parameters = cursor.fetchone()
maxTeamSize = team_parameters[1] # Get Max team size
cursor.execute("SELECT * FROM tms.team where team_name=(%s)", (g.student_team_name,))
current_team = cursor.fetchone()
currentTeamSize = current_team[4] # Get current size of the team
if (currentTeamSize < maxTeamSize): # If team capacity has not been reached
incrementTeamSize = currentTeamSize + 1
# Update student's team name
cursor.execute("UPDATE tms.student SET team_name=(%s) WHERE email=(%s)", (g.student_team_name, studentRequestEmail))
# Increment team size
cursor.execute("UPDATE tms.team SET team_size=(%s) WHERE team_name=(%s)", (incrementTeamSize, g.student_team_name))
print("GOT HERE")
# Update request status to accepted
cursor.execute("UPDATE tms.team_request SET request_status=(%s) WHERE student_email=(%s)", ("accepted", studentRequestEmail))
flash("Added " + studentRequestName + " to the team!")
else:
flash("Team Capacity of " + str(maxTeamSize) + " has already been reached!")
except:
con.rollback()
#return f"{err.__class__.__name__}: {err}"
flash("Error in accepting team member")
finally:
con.close()
return redirect(url_for("team_requests"))
# GET Request
# If it's a student that is a liaison
if ((g.user_type == "student") and (g.student_is_liaison == True)):
student_requests_sql = []
array_student_request_data = []
db = DatabaseConnection()
with db.get_connection().cursor() as cursor:
cursor.execute("SELECT * FROM tms.team_request WHERE team_name=(%s) AND request_status=(%s)",(g.student_team_name, "pending"))
student_requests_sql = cursor.fetchall()
for student_request in student_requests_sql:
student_request_data = []
student_request_data.append(student_request[2]) # Append email of student
student_request_data.append(student_request[4]) # Append full name of student
array_student_request_data.append(student_request_data) # Append that list to the larger list
print("Student Requests: " + str(student_requests_sql))
print("-----------------")
print("Student Request Emails: " + str(array_student_request_data))
return render_template("accept-students.html", studentRequests=array_student_request_data)
else:
return redirect(url_for("dashboard")) # user is not signed in or is instructor
def sign_in():
if request.method == 'POST':
session.pop('user', None) # clear session
email = request.form['email']
password = request.form['password']
try:
db = DatabaseConnection()
with db.get_connection() as con:
cursor = con.cursor()
cursor.execute("SELECT * FROM tms.student WHERE tms.student.email = (%s)",(email,))
dbEmailResultStudent = cursor.fetchone()
if (dbEmailResultStudent != None): # Student with email found
dbHashedPass = dbEmailResultStudent[5]
userType = "student"
else: # Check if instructor
cursor = con.cursor()
cursor.execute("SELECT * FROM tms.instructor WHERE tms.instructor.email = (%s)",(email,))
dbEmailResultInstructor = cursor.fetchone()
if (dbEmailResultInstructor != None): # instructor with email found
dbHashedPass = dbEmailResultInstructor[4]
userType = "instructor"
else:
flash("No user with such email exists.")
return redirect(url_for("sign_in"))
if (check_password_hash(dbHashedPass, password)): # compare hash pass here instead
session['user'] = request.form['email']
session['userType'] = userType
if (userType == "student"): # Student
session['firstName'] = dbEmailResultStudent[1]
session['lastName'] = dbEmailResultStudent[2]
session['studentNumber'] = dbEmailResultStudent[3]
session['isLiaison'] = dbEmailResultStudent[6]
session["teamName"] = dbEmailResultStudent[7]
else: # Instructor
session['firstName'] = dbEmailResultInstructor[1]
session['lastName'] = dbEmailResultInstructor[2]
flash("Login Successful")
return redirect(url_for('dashboard'))
else:
flash("Invalid Password")
return redirect(url_for("sign_in"))
except:
con.rollback()
#return f"{err.__class__.__name__}: {err}"
flash("Error in registering user")
finally:
con.close()
if g.user: # if already signed in, then redirect user to dashboard
return redirect(url_for('dashboard'))
return render_template("sign-in.html")
def join_team():
if request.method == 'POST':
try:
db = DatabaseConnection()
with db.get_connection() as con:
cursor = con.cursor()
cursor.execute("SELECT * FROM tms.team") # Check if teams exist
teams_sql = cursor.fetchone()
if (teams_sql != None):
teamToJoin = request.form['team_to_join']
cursor.execute("SELECT * FROM tms.team_request WHERE team_name=(%s) AND student_email=(%s)",(teamToJoin, g.user))
joinRequestResult = cursor.fetchone()
if (joinRequestResult == None):
studentFullName = g.user_first_name + " " + g.user_last_name
cursor.execute("INSERT INTO tms.team_request (team_name, student_email, request_status, student_name) VALUES (%s, %s, %s, %s)", (teamToJoin, g.user, "pending", studentFullName))
flash("Successfully sent request to join team: " + teamToJoin)
else:
flash("Already sent a request to join team: " + teamToJoin)
else:
flash("No teams currently exist")
except Exception as err:
con.rollback()
return f"{err.__class__.__name__}: {err}"
#flash("Error in sending request to join team")
finally:
con.close()
# GET Request
# If it's a student that's not in a team
if ((g.user_type == "student") and (g.student_team_name == None)):
teams_sql = []
team_names = []
db = DatabaseConnection()
with db.get_connection().cursor() as cursor:
cursor.execute("SELECT * FROM tms.team")
teams_sql = cursor.fetchall()
for team_sql in teams_sql:
team_names.append(team_sql[1])
print("Teams: " + str(teams_sql))
return render_template("join-team.html", teams=team_names)
elif ((g.user_type == "student") and (g.student_team_name != None)):
flash("You are already part of a team!")
return redirect(url_for("dashboard")) # student is already in team, redirect
else:
return redirect(url_for("dashboard")) # user is not signed in or is instructor
def register():
if request.method == 'POST':
session.pop('user', None) # clear session
if (request.form['password'] != request.form['confirm_password']):
flash("Passwords don't match.")
return redirect(url_for("register"))
if (request.form['email'] != request.form['confirm_email']):
flash("Emails don't match.")
return redirect(url_for("register"))
try:
firstname = request.form['first_name']
lastname = request.form['last_name']
email = request.form['email']
password = request.form['password']
userType = request.form['user_type']
hashedPass = generate_password_hash(password)
db = DatabaseConnection()
with db.get_connection() as con:
cursor = con.cursor()
if (userType == "student"): #if it's a student
cursor.execute("SELECT * FROM tms.student WHERE tms.student.email = (%s)",(email,))
dbEmailResult = cursor.fetchone()
if (dbEmailResult == None):
studentNumber = request.form['student_number']
cursor = con.cursor()
cursor.execute("INSERT INTO tms.student (first_name, last_name, student_number, email, password, is_liason, team_name) VALUES (%s, %s, %s, %s, %s, %s, NULL)",(firstname, lastname, studentNumber, email, hashedPass, False))
flash("Successfully registered user")
else:
flash("User with that email already exists.")
else: #it's an instructor
cursor.execute("SELECT * FROM tms.instructor WHERE tms.instructor.email = (%s)",(email,))
dbEmailResult = cursor.fetchone()
if (dbEmailResult == None):
cursor = con.cursor()
cursor.execute("INSERT INTO tms.instructor (first_name, last_name, email, password) VALUES (%s, %s, %s, %s)",(firstname, lastname, email, hashedPass))
flash("Successfully registered user")
else:
flash("User with that email already exists.")
except:
con.rollback()
#return f"{err.__class__.__name__}: {err}"
flash("Error in registering user")
finally:
con.close()
if g.user: # if logged in, redirect to dashboard
return redirect(url_for("dashboard"))
return render_template("register.html") # otherwise, display register form
from db.DatabaseConnection import DatabaseConnection
from db.config import config
import os
from werkzeug.security import generate_password_hash, check_password_hash
from models.user import User
from models.team import Team
# app = Flask(__name__)
app = Flask(__name__, static_folder='./static')
app.secret_key = os.urandom(24)
user = User('test', 'er', '*****@*****.**')
user.user_type = 'Instructor'
with app.app_context():
db = DatabaseConnection()
with db.get_connection().cursor() as cursor:
cursor.execute("SELECT * FROM tms.team_parameters")
team_parameters = cursor.fetchone()
g.are_team_parameters_set = team_parameters[3]
db.get_connection().close()
print(g.are_team_parameters_set)
@app.route('/')
def index():
if g.user:
return redirect(url_for("dashboard"))
def create_filtered_stations_table(sql, stations):
db = DatabaseConnection()
connection = db.get_connection()
with connection.cursor() as cursor:
cursor.execute(sql, (stations, ))
