private function getEmployees()
{
$dbc = DatabaseConnection::getConnection();
$statement = $dbc->prepare("SELECT * FROM users" . " WHERE role='employee'" . " ORDER BY name");
$statement->execute();
$employees = [];
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
$employees[] = [User::load($row['id'])->jsonSerialize()];
}
return $employees;
}
private function getManagerShifts($input)
{
$start_time = $input['start_time'];
$end_time = $input['end_time'];
$dbc = DatabaseConnection::getConnection();
$statement = $dbc->prepare("SELECT * FROM shifts" . " WHERE (start_time > :start_time AND start_time < :end_time)" . " OR (end_time > :start_time AND start_time < :end_time)" . " ORDER BY start_time");
$statement->execute(['start_time' => $start_time, 'end_time' => $end_time]);
$shifts = [];
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
$shifts[] = [Shift::load($row['id'])->jsonSerialize()];
}
return $shifts;
}
/**
* Returns true if this id is in use in the database
* @param type $id
* @return boolean
* @throws PDOException
*/
private static function existsInDb($id)
{
try {
$table = self::getTableName();
$dbc = DatabaseConnection::getConnection();
$statement = $dbc->prepare("SELECT * from :table where id = :id");
$statement->execute([':table' => $table, ':id' => $id]);
$row = $statement->fetch();
if ($row) {
return true;
}
return false;
} catch (Exception $e) {
throw new PDOException($e->getMessage());
}
}
private function getCoworkers($shift_id)
{
$shift = Shift::load($shift_id);
$start_time = $shift->start_time;
$end_time = $shift->end_time;
$dbc = DatabaseConnection::getConnection();
$statement = $dbc->prepare("SELECT * FROM shifts" . " WHERE (start_time >= :start_time AND start_time < :end_time)" . " OR (end_time >= :start_time AND end_time < :end_time)" . " ORDER BY start_time");
$statement->execute(['start_time' => $start_time, 'end_time' => $end_time]);
$coworker_ids = [];
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
if (strcmp($row['employee_id'], $shift->employee_id) !== 0 && strcmp($row['employee_id'], '') !== 0) {
$coworker_ids[] = $row['employee_id'];
}
}
$coworkers = [];
foreach ($coworker_ids as $coworker_id) {
$coworkers[] = User::load($coworker_id)->name;
}
return $coworkers;
}
/**
* Returns a summary of hours worked per week.
* If a shift spans midnight on a monday, it gets added to the previous
* week.
* @param string $user_id
*/
private function getSummary($user_id)
{
$dbc = DatabaseConnection::getConnection();
$statement = $dbc->prepare("SELECT * FROM shifts" . " WHERE end_time < NOW() AND employee_id=:user_id" . " ORDER BY start_time");
$statement->execute(['id' => $user_id]);
$weeklyTotals = [];
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
$date = new DateTime($row['start_time']);
$week = $date->format("W");
$seconds_worked = strtotime($row['end_time']) - strtotime($row['start_time']);
$minutes_worked = $seconds_worked / 60;
if (array_key_exists($week, $weeklyTotals)) {
$weeklyTotals[$week] += $minutes_worked;
} else {
$weeklyTotals[$week] = $minutes_worked;
}
}
$summary = [];
foreach ($weeklyTotals as $key => $value) {
$summary[] = ['week' => $key, 'minutes_worked' => $value];
}
return $summary;
}
