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")