def verify_user_by_email(email, password): with get_mysql_connection().cursor() as cursor: sql = "SELECT * FROM User WHERE email = %s AND password = '******'" cursor.execute(sql, email) result = cursor.fetchone() if result: return User(**result)
def get_user_by_id(user_id): with get_mysql_connection().cursor() as cursor: sql = "SELECT * FROM User WHERE id = " + str(user_id) cursor.execute(sql) result = cursor.fetchone() if result: return User(**result)
def get_user_by_email(email): with get_mysql_connection().cursor() as cursor: sql = "SELECT * FROM User WHERE email = %s" cursor.execute(sql, email) result = cursor.fetchone() if result: return User(**result)
def get_user_count(): with get_mysql_connection().cursor() as cursor: sql = "SELECT COUNT(*) FROM User" cursor.execute(sql) result = cursor.fetchone() if result: return result["COUNT(*)"]
def get_projects_for_user(user_id, mysql=None): sql = """SELECT Project.id, Project.name FROM Project LEFT OUTER JOIN User_Project on Project.id = User_Project.project_id WHERE User_Project.user_id = """ + str(user_id) with (mysql or get_mysql_connection()).cursor() as cursor: cursor.execute(sql) result = cursor.fetchall() return [Project(**r) for r in result] if result else []
def get_available_calendar_weeks(user_id): sql = "SELECT DISTINCT WorkingHour.calendar_week FROM WorkingHour WHERE WorkingHour.user_id = " + str( user_id) with get_mysql_connection().cursor() as cursor: cursor.execute(sql) result = cursor.fetchall() if result: return result return []
def get_project_hours_per_day(user_id, calendar_week, day): sql = """ SELECT Project.name, WorkingHour.""" + day + """ FROM WorkingHour INNER JOIN Project on WorkingHour.project_id = Project.id WHERE WorkingHour.calendar_week = """ + str( calendar_week) + """ AND WorkingHour.user_id = """ + str(user_id) with get_mysql_connection().cursor() as cursor: cursor.execute(sql) result = cursor.fetchall() return result if result else []
def put_working_hour(working_hours, mysql=None): values = [ working_hours.user_id, working_hours.project_id, working_hours.calendar_week, working_hours.monday, working_hours.tuesday, working_hours.wednesday, working_hours.thursday, working_hours.friday ] values_sql = "'" + "', '".join(map(lambda x: str(x), values)) + "'" sql = """ REPLACE INTO WorkingHour (user_id, project_id, calendar_week, monday, tuesday, wednesday, thursday, friday) VALUES(""" + values_sql + ")" with (mysql or get_mysql_connection()).cursor() as cursor: cursor.execute(sql)
def get_working_hour_for_user(user_id, calendar_week=get_current_week_number()): sql = """ SELECT WorkingHour.user_id, Project.id AS project_id, Project.name as project_name, WorkingHour.calendar_week, WorkingHour.monday, WorkingHour.tuesday, WorkingHour.wednesday, WorkingHour.thursday, WorkingHour.friday FROM WorkingHour, Project WHERE WorkingHour.project_id = Project.id AND WorkingHour.user_id = """ + str(user_id) + """ AND WorkingHour.calendar_week = """ + str(calendar_week) with get_mysql_connection().cursor() as cursor: cursor.execute(sql) result = cursor.fetchall() if result: return [WorkingHour(**r) for r in result] or []
def change_user_password(user_id, new_password): sql = "UPDATE User SET password = '******' WHERE id = " + str( user_id) with get_mysql_connection().cursor() as cursor: cursor.execute(sql) return True
def clear_working_hours(): with get_mysql_connection().cursor() as cursor: cursor.execute("TRUNCATE TABLE WorkingHour")