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