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 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 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 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 subscribers_count_event(eventId):
sql = 'SELECT count(userId) as c from enrollment WHERE eventId = %s'
cursor.execute(sql, (eventId, ))
return cursor.fetchone()
def load_user(user_id):
cursor.execute('SELECT * FROM user where userId = %s', (user_id, ))
res = cursor.fetchone()
return User(res)