class StatsHelper():
def __init__(self):
self.database = Database()
print("Stats Helping initialising!")
def select_all_employee(self):
result = self.database.fetch_all("SELECT * FROM employeedata")
return result
def select_all(self):
result = self.database.fetch_all("SELECT * FROM dayroutine")
return result
def join_all(self):
result = self.database.fetch_all(
"SELECT * FROM dayroutine as a left join employeedata b on a.employee_id = b.employee_id"
)
return result
# HINT: You can define more queries here, along with some python logic to calculate!
def calculate_avg(self):
result = self.database.fetch_all(
"SELECT AVG(work_time) AS workavg, AVG(sleep_time) AS sleepavg, AVG(exercise_time) AS exerciseavg, AVG(social_interaction_time) AS socialavg, AVG(a.employee_id) AS employeeid FROM dayroutine as a left join employeedata b on a.employee_id = b.employee_id GROUP BY a.employee_id"
)
return result
class StatsHelper():
def __init__(self):
self.database = Database()
print("Stats Helping initialising!")
def select_all_users(self):
result = self.database.fetch_all("SELECT * FROM user")
return result
def select_all_problems(self):
result = self.database.fetch_all("SELECT * FROM problem")
return result
# HINT: You can define more queries here, along with some python logic to calculate!
def calculate_another_stat(self):
# all_rows = self.database.fetch_all("")
return None
class StatsHelper():
def __init__(self):
self.database = Database()
print("Stats Helping initialising!")
def select_all_employee(self):
result = self.database.fetch_all("SELECT * FROM employeedata")
return result
def select_all(self):
result = self.database.fetch_all("SELECT * FROM dayroutine limit 0,7")
return result
def join_all(self):
result = self.database.fetch_all(
"SELECT * FROM dayroutine as a left join employeedata b on a.employee_id = b.employee_id"
)
return result
# return each employee with their joined dayroutines in order
def per_employee(self, employee_id):
result = self.database.fetch_all(f"""
SELECT * FROM dayroutine AS a WHERE EXISTS (
SELECT 1 FROM dayroutine
WHERE a.employee_id = {employee_id}
);
""")
return result
# return the median sleep, exercise and work times for all employees
def median_survey(self):
columns = [
"exercise_time", "social_interaction_time", "work_time",
"sleep_time"
]
medians = {}
for col in columns:
self.database.fetch_all("SET @rowindex_ex := -1")
med = self.database.fetch_all(f"""
SELECT
CAST(AVG(g.{col}) AS DOUBLE)
FROM
(SELECT @rowindex_ex:=@rowindex_ex + 1 AS rowindex_ex,
dayroutine.{col} AS {col}
FROM dayroutine
ORDER BY dayroutine.{col}) AS g
WHERE
g.rowindex_ex IN (FLOOR(@rowindex_ex / 2) , CEIL(@rowindex_ex / 2))
""")
median = med[0][f"CAST(AVG(g.{col}) AS DOUBLE)"]
medians.update({
col: median,
})
return medians
class StatsHelper():
def __init__(self):
self.database = Database()
print("Stats Helping initialising!")
#Used for content table
def select_all_employee(self):
result = self.database.fetch_all("SELECT * FROM employeedata")
return result
#Used for Total Exercise Hours
# def select_all_hours(self):
#Most Active Month!
def select_all(self):
result = self.database.fetch_all(
"SELECT b.employee_name, FORMAT(SUM(a.exercise_time)/60, 2) AS totalExerciseTime, FORMAT(SUM(a.sleep_time)/60, 2) AS totalSleep, FORMAT(SUM(a.social_interaction_time)/60, 2) AS totalSocialTime, FORMAT(SUM(a.work_time)/60, 2) AS totalWork FROM dayroutine as a join employeedata b on a.employee_id = b.employee_id GROUP BY b.employee_name"
)
return result
def join_all(self):
result = self.database.fetch_all(
"SELECT * FROM dayroutine as a left join employeedata b on a.employee_id = b.employee_id"
)
return result
def calculate_total_exercise(self):
result = self.database.fetch_all(
"SELECT FORMAT(SUM(exercise_time)/60,2) as totalExerciseTime FROM dayroutine"
)
return result
def highest_month(self):
result = self.database.fetch_all(
"SELECT month, SUM(exercise_time) as totalExerciseTime FROM dayroutine GROUP BY month ORDER BY totalExerciseTime LIMIT 0,1"
)
return result
def totalStaff(self):
result = self.database.fetch_all(
"SELECT COUNT(employee_name) FROM employeedata")
return result
def avgEmployeeAge(self):
result = self.database.fetch_all(
"SELECT FORMAT(SUM(age)/COUNT(employee_name),2) FROM employeedata")
return result
# HINT: You can define more queries here, along with some python logic to calculate!
def calculate_another_stat(self):
# all_rows = self.database.fetch_all("")
return None
class StatsHelper():
def __init__(self):
self.database = Database()
print("Stats Helping initialising!")
def select_all_employee(self):
result = self.database.fetch_all("SELECT * FROM employeedata")
return result
def select_all(self):
result = self.database.fetch_all("SELECT * FROM dayroutine limit 0,7")
return result
def join_all(self):
result = self.database.fetch_all(
"SELECT * FROM dayroutine as a left join employeedata b on a.employee_id = b.employee_id"
)
return result
# HINT: You can define more queries here, along with some python logic to calculate!
def calculate_another_stat(self):
# all_rows = self.database.fetch_all("")
return None
def __init__(self):
self.database = Database()
print("Stats Helping initialising!")
class StatsHelper():
def __init__(self):
self.database = Database()
print("Stats Helping initialising!")
def select_all_employee(self):
result = self.database.fetch_all("SELECT * FROM employeedata")
return result
def select_all_pag(self, amount, page):
result = self.database.fetch_all(f"SELECT * FROM dayroutine LIMIT {amount} OFFSET {amount * page}")
return result
def join_all(self):
result = self.database.fetch_all("SELECT * FROM dayroutine as a left join employeedata b on a.employee_id = b.employee_id")
return result
def asc_exercise(self):
result = self.database.fetch_all("SELECT * FROM dayroutine ORDER BY exercise_time ASC")
return result
# EXERCISE PAGINATION
def desc_exercise_pag(self, amount, page):
result = self.database.fetch_all(f"SELECT * FROM dayroutine ORDER BY exercise_time DESC LIMIT {amount} OFFSET {amount * page}")
return result
def asc_exercise_pag(self, amount, page):
result = self.database.fetch_all(f"SELECT * FROM dayroutine ORDER BY exercise_time ASC LIMIT {amount} OFFSET {amount * page}")
return result
# SLEEP PAGINATION
def desc_sleep_pag(self, amount, page):
result = self.database.fetch_all(f"SELECT * FROM dayroutine ORDER BY sleep_time DESC LIMIT {amount} OFFSET {amount * page}")
return result
def asc_sleep_pag(self, amount, page):
result = self.database.fetch_all(f"SELECT * FROM dayroutine ORDER BY sleep_time ASC LIMIT {amount} OFFSET {amount * page}")
return result
# SOCIAL PAGINATION
def desc_social_pag(self, amount, page):
result = self.database.fetch_all(f"SELECT * FROM dayroutine ORDER BY social_interaction_time DESC LIMIT {amount} OFFSET {amount * page}")
return result
def asc_social_pag(self, amount, page):
result = self.database.fetch_all(f"SELECT * FROM dayroutine ORDER BY social_interaction_time ASC LIMIT {amount} OFFSET {amount * page}")
return result
# WORK PAGINATION
def desc_work_pag(self, amount, page):
result = self.database.fetch_all(f"SELECT * FROM dayroutine ORDER BY work_time DESC LIMIT {amount} OFFSET {amount * page}")
return result
def asc_work_pag(self, amount, page):
result = self.database.fetch_all(f"SELECT * FROM dayroutine ORDER BY work_time ASC LIMIT {amount} OFFSET {amount * page}")
return result
def desc_exercise(self):
result = self.database.fetch_all(f"SELECT * FROM dayroutine ORDER BY exercise_time DESC")
return result
def total_records(self):
result = self.database.fetch_all("SELECT * FROM dayroutine")
return len(result)
# HINT: You can define more queries here, along with some python logic to calculate!
def calculate_another_stat(self):
# all_rows = self.database.fetch_all("")
return None