52 lines
1.5 KiB
PHP
52 lines
1.5 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Models;
|
|
|
|
use App\Core\Database\Database;
|
|
use PDO;
|
|
|
|
class Employee
|
|
{
|
|
public static function all(): array
|
|
{
|
|
$db = Database::getConnection();
|
|
$stmt = $db->query("
|
|
SELECT e.*,
|
|
(SELECT COUNT(*) FROM employee_orders WHERE employee_id = e.id) as total_orders,
|
|
(SELECT COALESCE(SUM(total_bill), 0) FROM employee_orders WHERE employee_id = e.id AND status = 'unpaid') as unpaid_balance
|
|
FROM employees e
|
|
ORDER BY e.name ASC
|
|
");
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
public static function find(int $id): ?array
|
|
{
|
|
$db = Database::getConnection();
|
|
$stmt = $db->prepare("SELECT * FROM employees WHERE id = ?");
|
|
$stmt->execute([$id]);
|
|
$res = $stmt->fetch();
|
|
return $res ?: null;
|
|
}
|
|
|
|
public static function findOrCreateByName(string $name, ?string $mattermostUsername = null): int
|
|
{
|
|
$db = Database::getConnection();
|
|
$name = trim($name);
|
|
|
|
$stmt = $db->prepare("SELECT id FROM employees WHERE LOWER(name) = LOWER(?)");
|
|
$stmt->execute([$name]);
|
|
$id = $stmt->fetchColumn();
|
|
|
|
if ($id) {
|
|
return (int)$id;
|
|
}
|
|
|
|
$stmt = $db->prepare("INSERT INTO employees (name, mattermost_username) VALUES (?, ?)");
|
|
$stmt->execute([$name, $mattermostUsername ?: strtolower(preg_replace('/[^a-zA-Z0-9]/', '', $name))]);
|
|
return (int)$db->lastInsertId();
|
|
}
|
|
}
|