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 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 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 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 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 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 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 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 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 change_status_app(appliedUserId,teamId):
cursor= db.cursor(dictionary=True)
user_apps = get_user_apps(appliedUserId)
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,(every['content'],date.today(), teamId,appliedUserId))
db.commit()
cursor.close()
return
cursor.close()
return
def add_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']):
return
sql = 'INSERT INTO application(teamId,userId,content,applyDate,modified,status) values(%s,%s,%s,%s,%s,%s)'
values = (teamId,user.id,form.content.data,date.today(),date.today(),'In Process')
cursor.execute(sql,values)
db.commit()
cursor.close()
def insert_user(user):
cursor = db.cursor(dictionary=True)
sql = 'INSERT INTO user(username,email,password) VALUES(%s,%s,%s)'
values = (user.username, user.email, user.password)
cursor.execute(sql, values)
db.commit()
user.id = cursor.lastrowid
sql = 'INSERT INTO userdetail(userId,linkPhoto,fullName) VALUES(%s,%s,%s)'
values = (user.id, 'img/profile_img/dummy.jpg', user.username)
cursor.execute(sql, values)
db.commit()
cursor.close()
def create_team(user,form):
cursor = db.cursor(dictionary=True)
sql = 'INSERT INTO team(adminUserId,teamName,courseId,description,openSpots,createDate,isAccepting,linkPhoto) values(%s,%s,%s,%s,%s,%s,%s,%s)'
path = ''
if(form.linkPhoto.data):
path = 'img/team_img/' + save_picture(form.linkPhoto.data)
else:
path = 'img/team_img/dummy.jpeg'
values = (user.id,form.teamName.data,form.course.data,form.description.data,form.openSpots.data,date.today(),form.isAccepting.data,path)
cursor.execute(sql,values)
db.commit()
res = cursor.lastrowid
cursor.close()
return res
def change_open_spots(number,teamId):
cursor = db.cursor(dictionary=True)
sql = 'SELECT openSpots FROM team where teamId = %s'
cursor.execute(sql,(teamId,))
res = cursor.fetchone()
spots = 0
if(res):
spots = res['openSpots']
spots += number
sql='UPDATE team SET openSpots = %s WHERE teamId = %s'
cursor.execute(sql,(spots,teamId))
if(spots<=0):
set_is_accepting(teamId,False)
db.commit()
cursor.close()
def update_team_data(teamId,form):
cursor = db.cursor(dictionary=True)
team = get_team(teamId = teamId)
sql = '''UPDATE team
SET teamName = %s, courseId = %s, description = %s, openSpots = %s, isAccepting = %s, linkPhoto = %s
WHERE teamId = %s'''
path = ''
if(form.linkPhoto.data):
path = 'img/team_img/' + save_picture(form.linkPhoto.data)
else:
path = team['linkPhoto']
values = (form.teamName.data,form.course.data,form.description.data,form.openSpots.data,form.isAccepting.data,path,teamId)
cursor.execute(sql,values)
db.commit()
cursor.close()
def delete_user_app(appId):
cursor = db.cursor(dictionary=True)
sql = 'DELETE FROM application where applicationId = %s'
cursor.execute(sql,(appId,))
db.commit()
cursor.close()
def delet_profile(userId):
cursor = db.cursor(dictionary=True)
sql = 'DELETE FROM user where userId = %s'
cursor.execute(sql, (userId, ))
db.commit()
cursor.close()
def deny_user_team(teamId,userId):
cursor = db.cursor(dictionary=True)
sql = 'UPDATE application SET status=%s WHERE teamId = %s and userId = %s'
cursor.execute(sql,('Denied',teamId,userId))
db.commit()
cursor.close()
def add_user_team(teamId,userId):
cursor = db.cursor(dictionary=True)
sql = 'INSERT INTO teamlist(teamId,userId) values(%s,%s)'
cursor.execute(sql,(teamId,userId))
db.commit()
cursor.close()