def update_user_detail(userId, form):
temp = get_user_detail(userId)
sql = '''
UPDATE userdetail
SET universityId = %s,
photoUrl = %s,
birthDate = %s,
bio =%s,
urlInstagram = %s,
urlTwitter = %s,
urlFacebook = %s,
occupation = %s,
gender = %s
WHERE userId = %s
'''
if (form.photoUrl.data):
photoUrl = 'img/profile/' + photo_save_profile(form.photoUrl.data)
else:
photoUrl = temp['photoUrl']
gender = ''
if (form.gender.data):
if (form.gender.data == 1):
gender = 'Male'
elif (form.gender.data == 2):
gender = 'Female'
elif (form.gender.data == 3):
gender = 'Other'
values = (form.university.data, photoUrl, form.birthDate.data,
form.bio.data, form.urlInstagram.data, form.urlTwitter.data,
form.urlFacebook.data, form.occupation.data, gender, userId)
cursor.execute(sql, values)
sql_connection.commit()
def get_event(eventId):
sql = '''SELECT date_format(event.eventdate,'%d/%m/%y') as date,event.*,category.* from event
LEFT JOIN category ON category.categoryId = event.categoryId
WHERE event.eventId = %s ORDER BY event.eventdate desc'''
cursor.execute(sql, (eventId, ))
return cursor.fetchone()
def category_event_count():
sql = '''
SELECT category.categoryName,count(event.categoryId) as c from event
INNER JOIN category on category.categoryId = event.categoryId
group by event.categoryId
'''
cursor.execute(sql)
return cursor.fetchall()
def get_all_event():
sql = '''SELECT date_format(event.eventdate,'%d/%m/%y') as date,user.*,userdetail.*,category.*,event.*
FROM event
INNER JOIN user on user.userId = event.adminId
INNER JOIN userdetail on userdetail.userId = event.adminId
LEFT JOIN category on category.categoryId = event.categoryId
ORDER BY event.eventdate desc
'''
cursor.execute(sql)
return cursor.fetchall()
def create_user(username, email, password):
photo = 'img/profile/dummy.png'
sql = 'INSERT INTO user(username,email,password) VALUES(%s,%s,%s)'
cursor.execute(sql, (username, email, password))
id_user = cursor.lastrowid
sql = 'INSERT INTO userdetail(userId,urlInstagram,urlTwitter,urlFacebook,photoUrl) VALUES(%s,%s,%s,%s,%s)'
cursor.execute(sql, (id_user, 'instagram', 'twitter', 'facebook', photo))
sql_connection.commit()
user_info = {"userId": id_user, "username": username, "email": email}
return User(user_info)
def create_event(userId, form):
sql = 'INSERT INTO event(adminId,categoryId,eventName,description,eventPhotoUrl,eventLink,eventDate) VALUES(%s,%s,%s,%s,%s,%s,%s)'
if (form.eventPhotoUrl.data):
photoUrl = 'img/event_photo/' + photo_save_event(
form.eventPhotoUrl.data)
else:
photoUrl = 'img/event_photo/dummy.jpg'
values = (userId, form.category.data, form.eventName.data,
form.description.data, photoUrl, form.eventLink.data,
form.eventdate.data)
cursor.execute(sql, values)
id_event = cursor.lastrowid
sql_connection.commit()
return id_event
def get_user(userId=None, username=None, email=None):
if (userId):
sql = 'SELECT * FROM user where userId = %s'
values = (userId, )
elif (username):
sql = 'SELECT * FROM user WHERE username = %s'
values = (username, )
elif (email):
sql = 'SELECT * FROM user WHERE email = %s'
values = (email, )
else:
return None
cursor.execute(sql, values)
return cursor.fetchone()
def get_user_detail(userId=None, username=None):
if (userId):
sql = '''SELECT date_format(userdetail.birthDate,'%d/%m/%y') as birthdate,user.*, userdetail.*,university.*
FROM userdetail
INNER JOIN user ON user.userId = userdetail.userId
LEFT JOIN university ON university.universityId = userdetail.universityId
WHERE userdetail.userId = %s'''
cursor.execute(sql, (userId, ))
return cursor.fetchone()
elif (username):
sql = '''SELECT date_format(userdetail.birthDate,'%d/%m/%y') as birthdate,user.*, userdetail.*,university.*
FROM userdetail
INNER JOIN user ON user.userId = userdetail.userId
LEFT JOIN university ON university.universityId = userdetail.universityId
WHERE user.username = %s
'''
cursor.execute(sql, (username, ))
return cursor.fetchone()
else:
return None
def update_event(eventId, form):
event = get_event(eventId=eventId)
sql = '''UPDATE event
SET categoryId = %s,
eventName = %s,
description = %s,
eventPhotoUrl = %s,
eventLink = %s,
eventdate = %s
WHERE eventId = %s
'''
if (form.eventPhotoUrl.data):
photoUrl = 'img/event_photo/' + photo_save_event(
form.eventPhotoUrl.data)
else:
photoUrl = event['eventPhotoUrl']
values = (form.category.data, form.eventName.data, form.description.data,
photoUrl, form.eventLink.data, form.eventdate.data, eventId)
cursor.execute(sql, values)
sql_connection.commit()
def get_enrollment(userId=None, eventId=None, enrollmentId=None):
if (enrollmentId):
sql = '''SELECT date_format(event.eventdate,'%d/%m/%y') as date,userdetail.*,event.*,user.*,enrollment.enrollmentId,enrollment.reason from enrollment
INNER JOIN event ON event.eventId = enrollment.eventId
INNER JOIN user ON user.userId = enrollment.userId
INNER JOIN userdetail ON userdetail.userId = enrollment.userId
WHERE enrollment.enrollmentId = %s
'''
cursor.execute(sql, (enrollmentId, ))
return cursor.fetchone()
if (userId and eventId):
sql = '''SELECT date_format(event.eventdate,'%d/%m/%y') as date,userdetail.*,event.*,user.*,enrollment.enrollmentId,enrollment.reason from enrollment
INNER JOIN event ON event.eventId = enrollment.eventId
INNER JOIN user ON user.userId = enrollment.userId
INNER JOIN userdetail ON userdetail.userId = enrollment.userId
WHERE enrollment.userId = %s and enrollment.eventId = %s
ORDER BY event.eventdate desc'''
cursor.execute(sql, (userId, eventId))
return cursor.fetchone()
elif (userId):
sql = '''SELECT date_format(event.eventdate,'%d/%m/%y') as date,userdetail.*,event.*,user.*,enrollment.enrollmentId,enrollment.reason from enrollment
INNER JOIN event ON event.eventId = enrollment.eventId
INNER JOIN user ON user.userId = enrollment.userId
INNER JOIN userdetail ON userdetail.userId = enrollment.userId
WHERE enrollment.userId = %s
ORDER BY event.eventdate desc
'''
cursor.execute(sql, (userId, ))
return cursor.fetchall()
elif (eventId):
sql = '''SELECT date_format(event.eventdate,'%d/%m/%y') as date,userdetail.* ,event.*,user.*,enrollment.reason,enrollment.enrollmentId from enrollment
INNER JOIN event ON event.eventId = enrollment.eventId
INNER JOIN user ON user.userId = enrollment.userId
INNER JOIN userdetail ON userdetail.userId = enrollment.userId
WHERE enrollment.eventId = %s
ORDER BY event.eventdate desc
'''
cursor.execute(sql, (eventId, ))
return cursor.fetchall()
else:
return None
def load_user(user_id):
cursor.execute('SELECT * FROM user where userId = %s', (user_id, ))
res = cursor.fetchone()
return User(res)
def delete_user(userId):
sql = 'DELETE FROM user WHERE userId = %s'
cursor.execute(sql, (userId, ))
sql_connection.commit()
def get_all_category():
sql = 'SELECT * FROM category'
cursor.execute(sql)
return cursor.fetchall()
def update_enrollment(enrollmentId, reson):
sql = 'UPDATE enrollment SET reason = %s WHERE enrollmentId = %s'
cursor.execute(sql, (reson, enrollmentId))
sql_connection.commit()
def add_enrollment(userId, eventId, reason):
sql = 'INSERT INTO enrollment(userId,eventId,reason) VALUES(%s,%s,%s)'
cursor.execute(sql, (userId, eventId, reason))
sql_connection.commit()
def delete_enrollment(enrollmentId):
sql = 'DELETE FROM enrollment WHERE enrollmentId = %s'
cursor.execute(sql, (enrollmentId, ))
sql_connection.commit()
def add_university(universityName):
sql = 'INSERT INTO university(universityName) values(%s)'
cursor.execute(sql, (universityName, ))
sql_connection.commit()
def get_all_university():
sql = 'SELECT * FROM university'
cursor.execute(sql)
return cursor.fetchall()
def create_category(categoryName):
sql = 'INSERT INTO category(categoryName) VALUES(%s)'
cursor.execute(sql, (categoryName, ))
sql_connection.commit()
def subscribers_count_event(eventId):
sql = 'SELECT count(userId) as c from enrollment WHERE eventId = %s'
cursor.execute(sql, (eventId, ))
return cursor.fetchone()