def get_user_by(id): conn = None cur = None try: conn = PgConfig.db() if(conn): cur = conn.cursor() query = "SELECT users.first_name, users.last_name, users.email, users.color_theme, users.image FROM users WHERE users.user_id = %s" cur.execute(query, (id,)) obj = cur.fetchone() user =User() if(obj): user.first_name = obj[0] user.last_name = obj[1] user.full_name = '' if(obj[0]): user.full_name = user.full_name + str(obj[0]) if(obj[1]): user.full_name = user.full_name+' '+str(obj[1]) user.email = obj[2] user.color_theme = obj[3] user.image = obj[4] user.user_id = id else: return False cur.close() conn.close() return user except Exception as e: return e
def login(data): try: conn = PgConfig.db() if(conn): cur = conn.cursor() login_query = "SELECT users.otp, users.user_id, users.first_name, users.last_name, users.color_theme, users.image\ FROM users WHERE users.email LIKE %s" cur.execute(login_query, (data['email'], )) user = cur.fetchone() response = User() if(user[0]==data['otp']): response.email= data['email'] response.user_id = user[1] response.first_name = user[2] response.last_name = user[3] response.color_theme = user[4] response.image = user[5] get_role_query = "SELECT user_role.role_id FROM user_role WHERE user_role.user_id = %s" cur.execute(get_role_query, (user[1],)) response.role_id = cur.fetchone()[0] response.token = (Jwt.encode_auth_token(user_id=user[1], role_id=response.role_id)).decode() cur.close() conn.close() return response else: return False else: return False except Exception as e: raise e
def check_fb_user_existence(email): conn = None cur = None try: conn = PgConfig.db() if(conn): cur = conn.cursor() select_query = "SELECT user_id, first_name, color_theme, image FROM users WHERE email LIKE %s AND type = %s" cur.execute(select_query, (email, 'fb', )); obj = cur.fetchone() response = User() if(obj): get_role = "SELECT role_id FROM user_role WHERE user_id = %s" cur.execute(get_role, (obj[0],)); role = cur.fetchone() response.email= email response.user_id = obj[0] response.role_id = role[0] response.first_name = obj[1] response.color_theme = obj[2] response.image = obj[3] response.token = (Jwt.encode_auth_token(user_id=obj[0], role_id=response.role_id)).decode() cur.close() conn.close() return response else: cur.close() conn.close() return False else: return False except Exception as e: return e
def get_profile_details(user_id, role_id): conn = None cur = None try: conn = PgConfig.db() if(conn): cur = conn.cursor() select_query = "SELECT first_name, last_name, email, finanical_aid, middle_name, dob, gender, permanent_address,\ present_address, alt_email, phone, course, image, color_theme FROM users WHERE user_id = %s" cur.execute(select_query,(user_id,)) result = cur.fetchone() user = User() if(result): user.first_name=result[0] user.last_name=result[1] user.email = result[2] user.finanical_aid = result[3] user.middle_name = result[4] user.dob = result[5] user.gender = result[6] user.permanent_address = result[7] user.present_address = result[8] user.alt_email = result[9] user.phone = result[10] user.course = result[11] user.image = result[12] user.color_theme = result[13] if(int(role_id) == 3): gpa_query = "SELECT avg(gpa) FROM enrolled_courses WHERE user_id = %s" cur.execute(gpa_query, (user_id,)) cgpa = cur.fetchone()[0] user.cgpa = 0 if(cgpa): user.cgpa = round(float(cgpa),2) cur.close() conn.close() return user else: return False else: return False except Exception as e: return e
def get_all(users, start, end): conn = None cur = None role_id = 3 #default to students if(users=="PROFESSORS"): role_id = 2 try: conn = PgConfig.db() if(conn): cur = conn.cursor() query = "SELECT users.first_name, users.last_name, users.email, users.user_id, users.color_theme,\ users.image, users.finanical_aid, users.cgpa, users.dob FROM users, (SELECT user_id FROM user_role WHERE role_id = %s)\ AS user_role WHERE users.user_id = user_role.user_id ORDER BY users.user_id LIMIT %s OFFSET %s" cur.execute(query, (role_id, end, start,)) users = cur.fetchall() user_list = [] if(len(users)): for response in users: user = User() user.first_name = response[0] user.last_name = response[1] user.full_name = '' if(response[0]): user.full_name = user.full_name + str(response[0]) if(response[1]): user.full_name = user.full_name+' '+str(response[1]) user.email = response[2] user.user_id = response[3] user.color_theme = response[4] user.image = response[5] user.finanical_aid = response[6] user.cgpa = response[7] user.dob = response[8] user_list.append(user) else: return False cur.close() conn.close() return user_list except Exception as e: return e
def get_comment_by(course_id): conn = None cur = None try: conn = PgConfig.db() if(conn): cur = conn.cursor() query = "SELECT comment, user_id, course_ratings,sem_id, comment_id FROM course_comments WHERE course_id = %s" cur.execute(query, (course_id, )) comments = cur.fetchall() comment_list =[] for comment in comments: user = User() query = "SELECT first_name, last_name, email, user_id, color_theme, image FROM users WHERE user_id = %s" cur.execute(query, (comment[1], )) response = cur.fetchone() user = User() user.comment_id = comment[4] user.first_name = response[0] user.last_name = response[1] user.email = response[2] user.user_id = response[3] user.comment= comment[0] user.rating = comment[2] user.color_theme = response[4] user.image = response[5] user.sem_id = comment[3] comment_list.append(user) cur.close() conn.close() return comment_list else: cur.close() conn.close() return [] except Exception as e: return e