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)