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; }