def remove_user_like(likedUserId, userId):
cursor = db.cursor(dictionary=True)
if (check_like(likedUserId, userId)):
sql = '''DELETE FROM likes WHERE likedUserId = %s and userId = %s'''
cursor.execute(sql, (likedUserId, userId))
db.commit()
cursor.close()
def load_user(user_id):
db.reconnect()
cursor = db.cursor(dictionary=True)
cursor.execute('SELECT * FROM user where userId = %s',(user_id,))
res = cursor.fetchone()
cursor.close()
return User(res)
def get_unis():
cursor = db.cursor(dictionary=True)
sql = 'SELECT * FROM university'
cursor.execute(sql)
res = cursor.fetchall()
cursor.close()
return res
def update_profile(user, form):
cursor = db.cursor(dictionary=True)
path_CV = ''
path_p = 'img/profile_img/'
if (form.linkPhoto.data):
path_p += save_picture(form.linkPhoto.data)
else:
path_p += 'dummy.jpg'
if (form.linkCV.data):
path_CV = 'files/CV/' + save_CV(form.linkCV.data)
values = (form.fullName.data, form.bio.data, form.university.data,
form.faculty.data, form.department.data, form.yearOfStudy.data,
form.gpa.data, path_p, path_CV, form.linkGithub.data, user)
sql = '''update userdetail
set fullName = %s,
bio = %s,
uniId = %s,
facId = %s,
depId = %s,
yearOfStudy = %s,
gpa = %s,
linkPhoto = %s,
linkCV = %s,
linkGithub = %s
where userId = %s'''
cursor.execute(sql, values)
db.commit()
cursor.close()
def get_user(username=None, email=None, userId=None):
cursor = db.cursor(dictionary=True)
values = ()
sql = 'SELECT * FROM user '
if (username or email or userId):
sql = sql + 'WHERE '
if username:
sql = sql + 'username = %s '
values = values + (username, )
if email:
if username:
sql = sql + 'OR '
sql = sql + 'email = %s '
values = values + (email, )
if userId:
if username or email:
sql = sql + 'OR '
sql = sql + 'userId = %s '
values = values + (userId, )
cursor.execute(sql, values)
res = cursor.fetchall()
cursor.close()
print(res)
return res
def get_courses():
cursor = db.cursor(dictionary=True)
sql = 'SELECT * FROM course'
cursor.execute(sql)
res = cursor.fetchall()
cursor.close()
return res
def accept_user_team(teamId,userId):
cursor = db.cursor(dictionary=True)
sql = 'UPDATE application SET status=%s WHERE teamId = %s and userId = %s'
cursor.execute(sql,('Accepted',teamId,userId))
db.commit()
change_open_spots(-1,teamId)
cursor.close()
def get_users():
cursor = db.cursor(dictionary=True)
sql = 'SELECT userId, username FROM user'
cursor.execute(sql)
res = cursor.fetchall()
cursor.close()
return res
def get_deps():
cursor = db.cursor(dictionary=True)
sql = 'SELECT * FROM department'
cursor.execute(sql)
res = cursor.fetchall()
cursor.close()
return res
def add_user_like(likedUserId, userId):
cursor = db.cursor(dictionary=True)
if (not check_like(likedUserId, userId)):
sql = '''INSERT INTO likes(likedUserId,userId) values(%s,%s)'''
cursor.execute(sql, (likedUserId, userId))
db.commit()
cursor.close()
def get_total_open_spots():
cursor = db.cursor(dictionary=True)
sql='''select sum(openSpots) as sum from team where team.isAccepting = 1'''
cursor.execute(sql)
res = cursor.fetchone()
cursor.close()
return res['sum']
def remove_like(likedUser, user):
cursor = db.cursor(dictionary=True)
res = get_likes(like_user, user)
if (res):
sql = 'DELETE FROM likes where likedUserId = %s and userId = %s'
cursor.execute(sql, (likedUser.id, user.id))
db.commit()
cursor.close()
def like_user(likedUser, user):
cursor = db.cursor(dictionary=True)
res = get_likes(likedUser, user)
if (not res):
sql = 'INSERT INTO likes(likedUserId,userId) values(%s,%s)'
cursor.execute(sql, (likedUser.id, user.id))
db.commit()
cursor.close()
def add_course(name, code, crn):
cursor = db.cursor(dictionary=True)
sql = 'INSERT INTO course(courseName,courseCode,courseCRN) values(%s,%s,%s)'
values = (name, code, crn)
cursor.execute(sql, values)
db.commit()
cursor.close()
def delete_team_info(teamId):
cursor= db.cursor(dictionary=True)
team = get_team(teamId)
if(team):
sql = 'DELETE FROM team WHERE teamId = %s'
cursor.execute(sql,(teamId,))
db.commit()
cursor.close()
def get_admin_teams(userId):
cursor = db.cursor(dictionary=True)
sql = '''SELECT team.*,course.* FROM team
LEFT JOIN course on course.courseId = team.courseId
WHERE team.adminUserId = %s ORDER BY isAccepting desc
'''
values = (userId,)
cursor.execute(sql,values)
return cursor.fetchall()
def set_is_accepting(teamId,value):
cursor = db.cursor(dictionary=True)
sql = 'UPDATE team SET isAccepting = %s WHERE teamId = %s'
values = (value,teamId)
cursor.execute(sql,values)
db.commit()
cursor.close()
def get_user_apps(userId):
cursor = db.cursor(dictionary=True)
sql = ''' SELECT user.*,team.*,application.* FROM application
INNER JOIN user ON user.userId = application.userId
INNER JOIN team on team.teamId = application.teamId
WHERE user.userId = %s'''
values = (userId,)
cursor.execute(sql,values)
return cursor.fetchall()
def add_uni(name):
cursor = db.cursor(dictionary=True)
name = name.upper()
sql = 'INSERT INTO university(uniName) values(%s)'
cursor.execute(sql, (name, ))
db.commit()
cursor.close()
def get_teams():
cursor = db.cursor(dictionary = True)
sql = '''SELECT team.*,course.* FROM team
LEFT JOIN course on course.courseId = team.courseId ORDER BY isAccepting desc, openSpots desc
'''
cursor.execute(sql)
res = cursor.fetchall()
cursor.close()
return res
def add_dep(name):
cursor = db.cursor(dictionary=True)
name = name.upper()
sql = 'INSERT INTO department(depName) values(%s)'
cursor.execute(sql, (name, ))
db.commit()
cursor.close()
def add_fac(name):
cursor = db.cursor(dictionary=True)
name = name.upper()
sql = 'INSERT INTO faculty(facName) values(%s)'
cursor.execute(sql, (name, ))
db.commit()
cursor.close()
def get_course_team_count():
cursor = db.cursor(dictionary = True)
sql = '''
SELECT course.courseName, count(team.courseId) as c FROM team
INNER JOIN course ON course.courseId = team.courseId
GROUP BY team.courseId
'''
cursor.execute(sql)
res = cursor.fetchall()
cursor.close()
return res
def get_accepting_team_count():
cursor = db.cursor(dictionary=True)
sql='''select team.isAccepting, count(*) as count from team
group by team.isAccepting'''
cursor.execute(sql)
res = cursor.fetchall()
for each in res:
if each['isAccepting']==1:
return each['count']
cursor.close()
return 0
def get_team_members(teamId):
cursor = db.cursor(dictionary=True)
sql = '''SELECT user.* FROM teamlist
INNER JOIN user on user.userId = teamlist.userId
WHERE teamlist.teamId = %s
'''
values = (teamId,)
cursor.execute(sql,values)
res = cursor.fetchall()
cursor.close()
return res
def get_user_teams(userId):
cursor = db.cursor(dictionary=True)
sql = '''SELECT team.*,course.* FROM teamlist
INNER JOIN user ON user.userId = teamlist.userId
INNER JOIN team on team.teamId = teamlist.teamId
LEFT JOIN course on course.courseId = team.courseId
WHERE user.userId = %s ORDER BY isAccepting desc
'''
values = (userId,)
cursor.execute(sql,values)
return cursor.fetchall()
def get_team(teamId):
cursor = db.cursor(dictionary=True)
sql = '''SELECT team.*,course.*,user.username FROM team
LEFT JOIN user ON user.userId = team.adminUserId
LEFT JOIN course on course.courseId = team.courseId
WHERE team.teamId = %s
'''
cursor.execute(sql,(teamId,))
res = cursor.fetchone()
cursor.close()
return res
def update_user_app(user,teamId,form):
cursor= db.cursor(dictionary=True)
user_apps = get_user_apps(user.id)
for every in user_apps:
if(teamId==every['teamId']):
sql = 'UPDATE application set content = %s, modified = %s where teamId = %s and userId = %s'
cursor.execute(sql,(form.content.data,date.today(), teamId,user.id))
db.commit()
cursor.close()
return
cursor.close()
return
def get_like_count(userId):
cursor = db.cursor(dictionary=True)
sql = '''SELECT COUNT(likeId) as c
FROM likes
WHERE likedUserId = %s;'''
cursor.execute(sql, (userId, ))
res = cursor.fetchone()
cursor.close()
if (res):
return res['c']
else:
return 0
def get_team_applications(teamId):
cursor = db.cursor(dictionary=True)
sql = '''SELECT user.*,userdetail.fullName,userdetail.linkPhoto,application.* FROM application
INNER JOIN user on user.userId = application.userId
INNER JOIN userdetail on userdetail.userId = application.userId
WHERE application.teamId = %s
'''
values = (teamId,)
cursor.execute(sql,values)
res = cursor.fetchall()
cursor.close()
return res
