272 lines
9.1 KiB
PHP
272 lines
9.1 KiB
PHP
<?php
|
|
// includes/functions.php
|
|
|
|
function get_work_orders_by_status($status) {
|
|
$pdo = db();
|
|
$stmt = $pdo->prepare("SELECT wo.*, u.username as assigned_technician FROM work_orders wo LEFT JOIN users u ON wo.assigned_technician_id = u.id WHERE status = ? ORDER BY created_at DESC");
|
|
$stmt->execute([$status]);
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
function get_all_work_orders() {
|
|
$pdo = db();
|
|
$stmt = $pdo->query("SELECT wo.*, u.username as assigned_technician FROM work_orders wo LEFT JOIN users u ON wo.assigned_technician_id = u.id ORDER BY created_at DESC");
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
function get_work_order_by_id($id) {
|
|
$pdo = db();
|
|
$stmt = $pdo->prepare("SELECT wo.*, u.username as assigned_technician_name FROM work_orders wo LEFT JOIN users u ON wo.assigned_technician_id = u.id WHERE wo.id = ?");
|
|
$stmt->execute([$id]);
|
|
return $stmt->fetch();
|
|
}
|
|
|
|
function get_kpis($work_orders) {
|
|
$total_jobs = count($work_orders);
|
|
$completed_jobs = 0;
|
|
foreach ($work_orders as $order) {
|
|
if ($order['status'] === 'Completed') {
|
|
$completed_jobs++;
|
|
}
|
|
}
|
|
$completion_rate = $total_jobs > 0 ? ($completed_jobs / $total_jobs) * 100 : 0;
|
|
|
|
return [
|
|
'total_jobs' => $total_jobs,
|
|
'completion_rate' => round($completion_rate),
|
|
'revenue' => '12,345' // Static for now
|
|
];
|
|
}
|
|
|
|
function create_work_order($customer_name, $customer_email, $job_type, $status, $address, $technician_id) {
|
|
$pdo = db();
|
|
$sql = "INSERT INTO work_orders (customer_name, customer_email, job_type, status, address, assigned_technician_id, uuid) VALUES (?, ?, ?, ?, ?, ?, UUID())";
|
|
$stmt = $pdo->prepare($sql);
|
|
return $stmt->execute([$customer_name, $customer_email, $job_type, $status, $address, $technician_id]);
|
|
}
|
|
|
|
function update_work_order($id, $customer_name, $customer_email, $job_type, $status, $address, $technician_id) {
|
|
$pdo = db();
|
|
$completed_at_sql = ($status === 'Completed') ? ", completed_at = CURRENT_TIMESTAMP" : "";
|
|
$sql = "UPDATE work_orders SET customer_name = ?, customer_email = ?, job_type = ?, status = ?, address = ?, assigned_technician_id = ? {$completed_at_sql} WHERE id = ?";
|
|
$stmt = $pdo->prepare($sql);
|
|
return $stmt->execute([$customer_name, $customer_email, $job_type, $status, $address, $technician_id, $id]);
|
|
}
|
|
|
|
function dispatch_work_order($id) {
|
|
$pdo = db();
|
|
$sql = "UPDATE work_orders SET status = 'In Progress', dispatched_at = CURRENT_TIMESTAMP WHERE id = ?";
|
|
$stmt = $pdo->prepare($sql);
|
|
return $stmt->execute([$id]);
|
|
}
|
|
|
|
function get_work_order_by_uuid($uuid) {
|
|
$pdo = db();
|
|
$stmt = $pdo->prepare("SELECT wo.*, u.username as assigned_technician_name FROM work_orders wo LEFT JOIN users u ON wo.assigned_technician_id = u.id WHERE wo.uuid = ?");
|
|
$stmt->execute([$uuid]);
|
|
return $stmt->fetch();
|
|
}
|
|
|
|
function search_work_orders($search_term = '', $status = '') {
|
|
$pdo = db();
|
|
$sql = "SELECT wo.*, u.username as assigned_technician FROM work_orders wo LEFT JOIN users u ON wo.assigned_technician_id = u.id";
|
|
$params = [];
|
|
$where_clauses = [];
|
|
|
|
if (!empty($search_term)) {
|
|
$where_clauses[] = "(wo.customer_name LIKE ? OR wo.job_type LIKE ? OR wo.address LIKE ? OR u.username LIKE ?)";
|
|
$search_param = "%{$search_term}%";
|
|
array_push($params, $search_param, $search_param, $search_param, $search_param);
|
|
}
|
|
|
|
if (!empty($status)) {
|
|
$where_clauses[] = "wo.status = ?";
|
|
$params[] = $status;
|
|
}
|
|
|
|
if (!empty($where_clauses)) {
|
|
$sql .= " WHERE " . implode(' AND ', $where_clauses);
|
|
}
|
|
|
|
$sql .= " ORDER BY wo.created_at DESC";
|
|
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute($params);
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
// --- User & Auth Functions ---
|
|
|
|
function create_user($username, $email, $password, $role) {
|
|
$pdo = db();
|
|
$hash = password_hash($password, PASSWORD_DEFAULT);
|
|
$sql = "INSERT INTO users (username, email, password, role) VALUES (?, ?, ?, ?)";
|
|
$stmt = $pdo->prepare($sql);
|
|
return $stmt->execute([$username, $email, $hash, $role]);
|
|
}
|
|
|
|
function get_user_by_username($username) {
|
|
$pdo = db();
|
|
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
|
|
$stmt->execute([$username]);
|
|
return $stmt->fetch();
|
|
}
|
|
|
|
function get_user_by_id($id) {
|
|
$pdo = db();
|
|
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
|
|
$stmt->execute([$id]);
|
|
return $stmt->fetch();
|
|
}
|
|
|
|
function get_users_by_role($role) {
|
|
$pdo = db();
|
|
$stmt = $pdo->prepare("SELECT id, username FROM users WHERE role = ? ORDER BY username ASC");
|
|
$stmt->execute([$role]);
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
function start_secure_session() {
|
|
$session_name = 'sec_session_id'; // Set a custom session name
|
|
$secure = true; // Set to true if using https.
|
|
$httponly = true; // This stops javascript being able to access the session id.
|
|
|
|
ini_set('session.use_only_cookies', 1); // Forces sessions to only use cookies.
|
|
$cookieParams = session_get_cookie_params();
|
|
session_set_cookie_params($cookieParams["lifetime"], $cookieParams["path"], $cookieParams["domain"], $secure, $httponly);
|
|
|
|
session_name($session_name);
|
|
session_start();
|
|
session_regenerate_id();
|
|
}
|
|
|
|
function is_logged_in() {
|
|
return isset($_SESSION['user_id']);
|
|
}
|
|
|
|
function require_login() {
|
|
if (!is_logged_in()) {
|
|
header('Location: /login.php');
|
|
exit();
|
|
}
|
|
}
|
|
|
|
function get_user_role() {
|
|
return isset($_SESSION['role']) ? $_SESSION['role'] : null;
|
|
}
|
|
|
|
// --- Reporting Functions ---
|
|
|
|
function get_work_order_counts_by_status() {
|
|
$pdo = db();
|
|
$sql = "SELECT status, COUNT(*) as count FROM work_orders GROUP BY status";
|
|
$stmt = $pdo->query($sql);
|
|
return $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
|
|
}
|
|
|
|
function get_work_order_counts_by_technician() {
|
|
$pdo = db();
|
|
$sql = "SELECT u.username, COUNT(wo.id) as count FROM work_orders wo JOIN users u ON wo.assigned_technician_id = u.id GROUP BY u.username";
|
|
$stmt = $pdo->query($sql);
|
|
return $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
|
|
}
|
|
|
|
function get_average_completion_time() {
|
|
$pdo = db();
|
|
$sql = "SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, completed_at)) as avg_hours FROM work_orders WHERE completed_at IS NOT NULL";
|
|
$stmt = $pdo->query($sql);
|
|
$result = $stmt->fetch();
|
|
return $result ? round($result['avg_hours'], 1) : 0;
|
|
}
|
|
|
|
// --- Inventory Functions ---
|
|
|
|
function get_inventory_items() {
|
|
$pdo = db();
|
|
$stmt = $pdo->query("SELECT * FROM inventory ORDER BY item_name ASC");
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
function get_inventory_item_by_id($id) {
|
|
$pdo = db();
|
|
$stmt = $pdo->prepare("SELECT * FROM inventory WHERE id = ?");
|
|
$stmt->execute([$id]);
|
|
return $stmt->fetch();
|
|
}
|
|
|
|
function add_inventory_item($item_name, $quantity, $price) {
|
|
$pdo = db();
|
|
$sql = "INSERT INTO inventory (item_name, quantity, price) VALUES (?, ?, ?)";
|
|
$stmt = $pdo->prepare($sql);
|
|
return $stmt->execute([$item_name, $quantity, $price]);
|
|
}
|
|
|
|
function update_inventory_item($id, $item_name, $quantity, $price) {
|
|
$pdo = db();
|
|
$sql = "UPDATE inventory SET item_name = ?, quantity = ?, price = ? WHERE id = ?";
|
|
$stmt = $pdo->prepare($sql);
|
|
return $stmt->execute([$item_name, $quantity, $price, $id]);
|
|
}
|
|
|
|
function delete_inventory_item($id) {
|
|
$pdo = db();
|
|
$sql = "DELETE FROM inventory WHERE id = ?";
|
|
$stmt = $pdo->prepare($sql);
|
|
return $stmt->execute([$id]);
|
|
}
|
|
|
|
function get_work_order_parts($work_order_id) {
|
|
$pdo = db();
|
|
$sql = "SELECT i.item_name, wp.quantity_used, i.price, wp.id as part_id FROM work_order_parts wp JOIN inventory i ON wp.inventory_id = i.id WHERE wp.work_order_id = ?";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute([$work_order_id]);
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
function add_part_to_work_order($work_order_id, $inventory_id, $quantity_used) {
|
|
$pdo = db();
|
|
$pdo->beginTransaction();
|
|
try {
|
|
$sql = "INSERT INTO work_order_parts (work_order_id, inventory_id, quantity_used) VALUES (?, ?, ?)";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute([$work_order_id, $inventory_id, $quantity_used]);
|
|
|
|
$sql = "UPDATE inventory SET quantity = quantity - ? WHERE id = ?";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute([$quantity_used, $inventory_id]);
|
|
|
|
$pdo->commit();
|
|
return true;
|
|
} catch (Exception $e) {
|
|
$pdo->rollBack();
|
|
return false;
|
|
}
|
|
}
|
|
|
|
function remove_part_from_work_order($part_id) {
|
|
$pdo = db();
|
|
$pdo->beginTransaction();
|
|
try {
|
|
$sql = "SELECT inventory_id, quantity_used FROM work_order_parts WHERE id = ?";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute([$part_id]);
|
|
$part = $stmt->fetch();
|
|
|
|
if ($part) {
|
|
$sql = "UPDATE inventory SET quantity = quantity + ? WHERE id = ?";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute([$part['quantity_used'], $part['inventory_id']]);
|
|
|
|
$sql = "DELETE FROM work_order_parts WHERE id = ?";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute([$part_id]);
|
|
}
|
|
|
|
$pdo->commit();
|
|
return true;
|
|
} catch (Exception $e) {
|
|
$pdo->rollBack();
|
|
return false;
|
|
}
|
|
}
|