def test_staff_index(self):
with self.client:
# check that login is required
response = self.client.get("/staff/")
self.assertEqual(response.status_code, 302)
# login
response = self.login("test_user", "test_password")
self.assertEqual(response.status_code, 200)
self.assertIn(b"What is Achieve?", response.data)
# check that page renders correctly
response = self.client.get("/staff/")
self.assertEqual(response.status_code, 200)
self.assertIn(b"<h1>All Staff</h1>", response.data)
self.assertIn(b"<td>Mark Hammel</td>", response.data)
# remove all staff from database
db = database.connect()
db.execute("DELETE FROM staff")
db.execute("DELETE FROM staff_client_teams")
db.execute("DELETE FROM staff_hours")
# db.execute("DELETE FROM supervisor_staff_teams")
db.commit()
response = self.client.get("/staff/")
self.assertEqual(response.status_code, 200)
self.assertIn(b"No Staff Found", response.data)
def test_init_db(self):
with self.app.app_context():
# init populates db in setUp()
# verify database contents
db = database.connect()
result = db.execute("SELECT * FROM users").fetchall()
self.assertEqual(len(result), 3)
result = db.execute("SELECT * FROM supervisors").fetchall()
self.assertEqual(len(result), 2)
result = db.execute("SELECT * FROM clients").fetchall()
self.assertEqual(len(result), 2)
result = db.execute("SELECT * FROM client_hours").fetchall()
self.assertEqual(len(result), 2)
result = db.execute("SELECT * FROM staff").fetchall()
self.assertEqual(len(result), 1)
result = db.execute("SELECT * FROM staff_hours").fetchall()
self.assertEqual(len(result), 1)
result = db.execute("SELECT * FROM staff_client_teams").fetchall()
self.assertEqual(len(result), 1)
def test_connect(self):
with self.app.app_context():
# test successful connection
db = database.connect()
self.assertTrue(db)
# test for connection 'failure'
database.close()
os.close(self.db_file_handle)
os.unlink(self.db_path)
with self.assertRaises(wkzg.InternalServerError):
db = database.connect()
# set up database again so tearDown() does not fail
self.setUp()
def check_name(names: list) -> list:
"""
Ensures a user's first or last name is at least one character and
less then 65 chars.
"""
errors = []
if type(names) != list:
raise TypeError(
"Function check_name() expected a list with two elements.")
if len(names[0] + " " + names[1]) > 64:
errors.append("Full name must be less then 64 characters")
for name in names:
if type(name) != str or len(names) != 2:
raise ValueError(
"Function check_name() expected a list containing two strings."
)
if not name:
errors.append("First and last name are required")
break
db = database.connect()
result = db.execute(
"SELECT staff_id FROM staff WHERE first_name = ? AND last_name = ?",
(names[0], names[1])).fetchone()
if result:
errors.append(
f"Staff member '{names[0]} {names[1]}' is already in the database."
)
return errors
def test_db_open_close(self):
with self.app.app_context():
db = database.connect()
# after context db should be closed automatically
with self.assertRaises(sqlite3.ProgrammingError):
db.execute("SELECT 1")
def get_one(id: int) -> dict:
""" Fetches a user's info the with provided id. """
db = database.connect()
result = db.execute(
"SELECT * FROM users WHERE user_id = ?",
(id,)
).fetchone()
return result
def get_id(initials: str) -> bool:
""" Fetches a client's ID by their initials """
if type(initials) != str:
raise TypeError
db = database.connect()
client_id = db.execute("SELECT client_id FROM clients WHERE initials = ?",
(initials, )).fetchone()
return client_id
def is_found(staff_id: int) -> bool:
""" Checks to see if a staff member exists. """
# validate staff id
util.require_int([staff_id])
db = database.connect()
staff = db.execute("SELECT first_name FROM staff WHERE staff_id = ?",
(staff_id, )).fetchone()
return True if staff else False
def get_by_username(username: str) -> dict:
""" Fetches a user's info with the provided username. """
db = database.connect()
result = db.execute(
"SELECT * FROM users WHERE username = ?",
(username,)
).fetchone()
return result
def get_all(page: int) -> list:
""" Fetches all user info. """
offset = page * 10
db = database.connect()
users = db.execute(
"SELECT * FROM users WHERE is_admin = 0 "
"ORDER BY first_name ASC "
"LIMIT 11 OFFSET ?", (offset, )
).fetchall()
return users
def is_found(supervisor_id: int) -> bool:
""" Indicates whether a provided supervisor id exits in the database """
util.require_int([supervisor_id])
db = database.connect()
result = db.execute(
"SELECT first_name FROM supervisors "
"WHERE supervisor_id = ?", (supervisor_id,)
).fetchone()
return True if result else False
def del_team(team_id: int) -> bool:
""" Removes a client from a staff member's team """
# validate args
util.require_int([team_id])
db = database.connect()
db.execute("DELETE FROM staff_client_teams WHERE team_id = ? ",
(team_id, ))
db.commit()
return True
def get_team_id(staff_id: int, client_id: int) -> bool:
""" Gets the id number of a staff-client team in the database """
# validates args
util.require_int([staff_id, client_id])
db = database.connect()
team_id = db.execute(
"SELECT team_id FROM staff_client_teams "
"WHERE client_id = ? AND staff_id = ?",
(client_id, staff_id)).fetchone()
return team_id
def create_team(staff_id: int, client_id: int):
""" Adds a client to a staff member's team """
# validate ids: assumes both ids exist in the db
util.require_int([staff_id, client_id])
db = database.connect()
cursor = db.execute(
"INSERT INTO staff_client_teams (staff_id, client_id) "
"VALUES(?, ?)", (staff_id, client_id))
db.commit()
return cursor.lastrowid
def team_exists(staff_id: int, client_id: int) -> bool:
""" Indicates is a staff member is on the same team as a client """
# validate ids
util.require_int([staff_id, client_id])
db = database.connect()
team_exists = db.execute(
"SELECT team_id FROM staff_client_teams "
"WHERE staff_id = ? AND client_id = ?",
(staff_id, client_id)).fetchone()
return True if team_exists else False
def is_found(client_id: int) -> bool:
""" Indicates whether or not a client_id is in the database. """
util.require_int([client_id])
# if type(client_id) != int or client_id < 1:
# raise TypeError(
# "Function is_found expects an integer larger then 0"
# )
db = database.connect()
result = db.execute("SELECT initials FROM clients "
"WHERE client_id = ?", (client_id, )).fetchone()
return True if result else False
def update_password(new_pw: str, id: int) -> bool:
""" Updates a user's password. """
try:
db = database.connect()
db.execute(
"UPDATE users SET password = ? WHERE user_id = ?",
(secure.generate_password_hash(new_pw), id)
)
except:
return False
db.commit()
return True
def delete(user_id: int) -> bool:
""" Deletes a user's account. """
try:
db = database.connect()
db.execute(
"DELETE FROM users WHERE user_id = ?",
(user_id,)
)
except:
return False
db.commit()
return True
def create(staff: dict) -> int:
""" Creates a staff member, along with their client and supervisor teams """
db = database.connect()
attendance = staff["attendance"]
hours = staff["hours"]
# insert staff, assumes validation has already occured
cursor = db.execute(
"INSERT INTO staff "
"(first_name, last_name, certification, tier, color, supervisor,"
" mon, tue, wed, thu, fri) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (
staff["first_name"],
staff["last_name"],
staff["cert"],
staff["tier"],
staff["color"],
staff["supervisor"],
attendance["mon"],
attendance["tue"],
attendance["wed"],
attendance["thu"],
attendance["fri"],
))
staff_id = cursor.lastrowid
# add staff member's hours
hours["staff_id"] = staff_id
db.execute(
"INSERT INTO staff_hours "
"(monday, tuesday, wednesday, thursday, friday, staff_id) "
"VALUES (?,?,?,?,?,?)", list(hours.values()))
# convert client initials to client ids
client_ids = client_q.get_ids(staff["clients"])
# add clients to staff_team
for client_id in client_ids:
# skip teams that already exist
if team_exists(staff_id, client_id):
continue
db.execute(
"INSERT INTO staff_client_teams "
"(staff_id, client_id) "
"VALUES (?, ?)", (staff_id, client_id))
db.commit()
return staff_id
def get_all(page_num: int) -> list:
""" Fetches basic information from all the staff. """
offset = page_num * 10
try:
db = database.connect()
staff = db.execute(
"SELECT staff_id, first_name, last_name, certification, tier, color "
"FROM staff ORDER BY first_name ASC LIMIT 11 OFFSET ?",
(offset, )).fetchall()
except Exception as e:
# print(str(e))
return False
return staff
def get_details(supervisor_id: int) -> dict:
"""
Fetches a staff member's supervisor information.
"""
# validate id
util.require_int([supervisor_id])
db = database.connect()
supervisor = db.execute(
"SELECT supervisor_id, first_name, last_name, certification "
"FROM supervisors "
"WHERE supervisor_id = ?",(supervisor_id,)
).fetchone()
return supervisor
def delete_staff():
staff_id = util.safe_int(request.form.get("staff_id"))
# validate staff_id
error = staff_val.check_staff_id(staff_id)
if error:
flash(error)
return redirect(url_for("staff.staff_index"))
# delete all related entries from the database
db = database.connect()
db.execute("DELETE FROM staff WHERE staff_id = ?", (staff_id, ))
db.commit()
flash("Staff Member Deleted.")
return redirect(url_for("staff.staff_index"))
def get_clients(staff_id: int) -> list:
"""
Fetches the client initials and team_id
of specific staff member's team.
"""
# validate id arg
util.require_int([staff_id])
db = database.connect()
clients = db.execute(
"SELECT sct.team_id, c.initials "
"FROM staff_Client_teams AS sct "
"INNER JOIN clients AS c ON sct.client_id=c.client_id "
"WHERE sct.staff_id = ?", (staff_id, )).fetchall()
return clients
def update(staff_data: dict, staff_id: int) -> bool:
""" Updates a staff members information (Excludes their clients) """
try:
db = database.connect()
# update basic info
db.execute(
"UPDATE staff "
"SET "
"first_name = ?, last_name = ?, certification = ?, "
"tier = ?, color = ?, supervisor = ?, "
"mon = ?, tue = ?, wed = ?, thu = ?, fri = ? "
"WHERE staff_id = ?", (
staff_data["first_name"],
staff_data["last_name"],
staff_data["cert"],
staff_data["tier"],
staff_data["color"],
staff_data["supervisor"],
staff_data["attendance"]["mon"],
staff_data["attendance"]["tue"],
staff_data["attendance"]["wed"],
staff_data["attendance"]["thu"],
staff_data["attendance"]["fri"],
staff_id,
))
# update hours
db.execute(
"UPDATE staff_hours "
"SET monday = ?, tuesday = ?, wednesday = ?, "
"thursday = ?, friday = ? "
"WHERE staff_id = ?", (
staff_data["hours"]["mon"],
staff_data["hours"]["tue"],
staff_data["hours"]["wed"],
staff_data["hours"]["thu"],
staff_data["hours"]["fri"],
staff_id,
))
except Exception as e:
# print("SQL ERROR: " + str(e))
return ["Staff Information update failed."]
db.commit()
return []
def test_db_insert_select(self):
with self.app.app_context():
# insert data
db = database.connect()
db.execute(
"INSERT INTO users "
"(username, password, first_name, last_name, email)"
" VALUES (?, ?, ?, ?, ?)",
("test_username", "test_user_password", "test_firstname",
"test_lastname", "*****@*****.**"))
db.commit()
# fetch retrieved data
result = db.execute(
"SELECT password FROM users WHERE username = ?",
("test_username", )).fetchone()
# validate query result
self.assertEqual(result["password"], "test_user_password")
def update(user: dict) -> bool:
""" Updates a user's information. """
try:
db = database.connect()
db.execute(
"UPDATE users SET "
"username=?, first_name=?, last_name=?, email=? "
"WHERE user_id=?",
(user["username"],
user["first_name"],
user["last_name"],
user["email"],
user["user_id"])
)
except:
return False
db.commit()
return True
def get_details(staff_id: int) -> dict:
"""
Fetches detailed staff information, includes a staff
members's schedule for the week.
"""
# validate staff_id
util.require_int([staff_id])
db = database.connect()
staff_details = db.execute(
"SELECT "
"s.staff_id, s.first_name, s.last_name, "
"s.certification, s.tier, s.color, s.supervisor, "
"s.mon, s.tue, s.wed, s.thu, s.fri, "
"sh.monday, sh.tuesday, sh.wednesday, "
"sh.thursday, sh.friday "
"FROM staff AS s "
"INNER JOIN staff_hours AS sh ON sh.staff_id = s.staff_id "
"WHERE s.staff_id=?", (staff_id, )).fetchone()
return staff_details
def create(new_user: dict) -> bool:
""" Inserts a new user's info into the database. """
try:
db = database.connect()
cursor = db.execute(
"INSERT INTO users "
"(username, first_name, last_name, email, password) "
"VALUES (?, ?, ?, ?, ?)",
(
new_user["username"],
new_user["first_name"],
new_user["last_name"],
new_user["email"],
secure.generate_password_hash(new_user["password"])
)
)
user_id = cursor.lastrowid
except:
return False
db.commit()
return user_id
def setUpClass(cls):
cls.db_file_handle, cls.db_path = tempfile.mkstemp()
cls.app = achiever.create_app({
'TESTING': True,
'DATABASE': cls.db_path,
})
with cls.app.app_context():
# populate database
database.init()
# insert second client
db = database.connect()
db.execute(
"INSERT INTO clients "
"(initials, total_hours, supervisor, color, mon, tue, wed, thu, fri) "
"VALUES ('DV', '15', '1', '2', '1', '1', '1', '0', '1')"
)
db.commit()
database.close()
def set_password(self, password):
db = database.connect()
db.execute(
"UPDATE users SET password = ? WHERE user_id = ?",
(secure.generate_password_hash(password), g.user["user_id"]))
db.commit()
