235 lines
8.4 KiB
PHP
235 lines
8.4 KiB
PHP
<?php
|
|
|
|
require_once 'models/TicketModel.php';
|
|
require_once 'models/UserPreferencesModel.php';
|
|
require_once 'models/StatsModel.php';
|
|
|
|
class DashboardController
|
|
{
|
|
private $ticketModel;
|
|
private $prefsModel;
|
|
private $statsModel;
|
|
private $conn;
|
|
|
|
/** Valid sort columns (whitelist) */
|
|
private const VALID_SORT_COLUMNS = [
|
|
'ticket_id', 'title', 'status', 'priority', 'category', 'type',
|
|
'created_at', 'updated_at', 'assigned_to', 'created_by'
|
|
];
|
|
|
|
/** Valid statuses */
|
|
private const VALID_STATUSES = ['Open', 'Pending', 'In Progress', 'Closed'];
|
|
|
|
public function __construct($conn)
|
|
{
|
|
$this->conn = $conn;
|
|
$this->ticketModel = new TicketModel($conn);
|
|
$this->prefsModel = new UserPreferencesModel($conn);
|
|
$this->statsModel = new StatsModel($conn);
|
|
}
|
|
|
|
/**
|
|
* Validate and sanitize a date string
|
|
*/
|
|
private function validateDate(?string $date): ?string
|
|
{
|
|
if (empty($date)) {
|
|
return null;
|
|
}
|
|
// Check if it's a valid date format (YYYY-MM-DD)
|
|
if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $date) && strtotime($date) !== false) {
|
|
return $date;
|
|
}
|
|
return null;
|
|
}
|
|
|
|
/**
|
|
* Validate priority value (1-5)
|
|
*/
|
|
private function validatePriority($priority): ?int
|
|
{
|
|
if ($priority === null || $priority === '') {
|
|
return null;
|
|
}
|
|
$val = (int)$priority;
|
|
return ($val >= 1 && $val <= 5) ? $val : null;
|
|
}
|
|
|
|
/**
|
|
* Validate user ID
|
|
*/
|
|
private function validateUserId($userId): ?int
|
|
{
|
|
if ($userId === null || $userId === '') {
|
|
return null;
|
|
}
|
|
$val = (int)$userId;
|
|
return ($val > 0) ? $val : null;
|
|
}
|
|
|
|
public function index()
|
|
{
|
|
// Get user ID for preferences
|
|
$userId = isset($_SESSION['user']['user_id']) ? $_SESSION['user']['user_id'] : null;
|
|
|
|
// Validate and sanitize page parameter
|
|
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
|
|
|
|
// Get rows per page from user preferences, fallback to cookie, then default
|
|
// Clamp to reasonable range (1-100)
|
|
$limit = 15;
|
|
if ($userId) {
|
|
$limit = (int)$this->prefsModel->getPreference($userId, 'rows_per_page', 15);
|
|
} elseif (isset($_COOKIE['ticketsPerPage'])) {
|
|
$limit = (int)$_COOKIE['ticketsPerPage'];
|
|
}
|
|
$limit = max(1, min(100, $limit));
|
|
|
|
// Validate sort column against whitelist
|
|
$sortColumn = isset($_GET['sort']) && in_array($_GET['sort'], self::VALID_SORT_COLUMNS, true)
|
|
? $_GET['sort']
|
|
: 'ticket_id';
|
|
|
|
// Validate sort direction
|
|
$sortDirection = isset($_GET['dir']) && strtolower($_GET['dir']) === 'asc' ? 'asc' : 'desc';
|
|
|
|
// Category and type are validated by the model (uses prepared statements)
|
|
$category = isset($_GET['category']) ? trim($_GET['category']) : null;
|
|
$type = isset($_GET['type']) ? trim($_GET['type']) : null;
|
|
|
|
// Sanitize search - limit length to prevent abuse
|
|
$search = isset($_GET['search']) ? substr(trim($_GET['search']), 0, 255) : null;
|
|
|
|
// Handle status filtering with user preferences
|
|
$status = null;
|
|
if (isset($_GET['status']) && !empty($_GET['status'])) {
|
|
// Validate each status in the comma-separated list
|
|
$requestedStatuses = array_map('trim', explode(',', $_GET['status']));
|
|
$validStatuses = array_filter($requestedStatuses, function ($s) {
|
|
return in_array($s, self::VALID_STATUSES, true);
|
|
});
|
|
$status = !empty($validStatuses) ? implode(',', $validStatuses) : null;
|
|
} elseif (!isset($_GET['show_all'])) {
|
|
// Get default status filters from user preferences
|
|
if ($userId) {
|
|
$status = $this->prefsModel->getPreference($userId, 'default_status_filters', 'Open,Pending,In Progress');
|
|
} else {
|
|
// Default: show Open, Pending, and In Progress (exclude Closed)
|
|
$status = 'Open,Pending,In Progress';
|
|
}
|
|
}
|
|
// If $_GET['show_all'] exists or no status param with show_all, show all tickets (status = null)
|
|
|
|
// Build and validate advanced search filters
|
|
$filters = [];
|
|
|
|
// Validate date filters
|
|
$createdFrom = $this->validateDate($_GET['created_from'] ?? null);
|
|
$createdTo = $this->validateDate($_GET['created_to'] ?? null);
|
|
$updatedFrom = $this->validateDate($_GET['updated_from'] ?? null);
|
|
$updatedTo = $this->validateDate($_GET['updated_to'] ?? null);
|
|
$closedFrom = $this->validateDate($_GET['closed_from'] ?? null);
|
|
$closedTo = $this->validateDate($_GET['closed_to'] ?? null);
|
|
|
|
if ($createdFrom) {
|
|
$filters['created_from'] = $createdFrom;
|
|
}
|
|
if ($createdTo) {
|
|
$filters['created_to'] = $createdTo;
|
|
}
|
|
if ($updatedFrom) {
|
|
$filters['updated_from'] = $updatedFrom;
|
|
}
|
|
if ($updatedTo) {
|
|
$filters['updated_to'] = $updatedTo;
|
|
}
|
|
if ($closedFrom) {
|
|
$filters['closed_from'] = $closedFrom;
|
|
}
|
|
if ($closedTo) {
|
|
$filters['closed_to'] = $closedTo;
|
|
}
|
|
|
|
// Validate priority filters; ?priority=N sets exact match (min=max=N)
|
|
$prioritySingle = $this->validatePriority($_GET['priority'] ?? null);
|
|
$priorityMin = $prioritySingle ?? $this->validatePriority($_GET['priority_min'] ?? null);
|
|
$priorityMax = $prioritySingle ?? $this->validatePriority($_GET['priority_max'] ?? null);
|
|
|
|
if ($priorityMin !== null) {
|
|
$filters['priority_min'] = $priorityMin;
|
|
}
|
|
if ($priorityMax !== null) {
|
|
$filters['priority_max'] = $priorityMax;
|
|
}
|
|
|
|
// Validate user ID filters
|
|
$createdBy = $this->validateUserId($_GET['created_by'] ?? null);
|
|
if ($createdBy !== null) {
|
|
$filters['created_by'] = $createdBy;
|
|
}
|
|
|
|
// assigned_to accepts a numeric user ID, 'unassigned', or the special string 'me'
|
|
$assignedToRaw = $_GET['assigned_to'] ?? null;
|
|
if ($assignedToRaw === 'unassigned') {
|
|
$filters['assigned_to'] = 'unassigned';
|
|
} elseif ($assignedToRaw === 'me' && $userId) {
|
|
$filters['assigned_to'] = (int)$userId;
|
|
} else {
|
|
$assignedTo = $this->validateUserId($assignedToRaw);
|
|
if ($assignedTo !== null) {
|
|
$filters['assigned_to'] = $assignedTo;
|
|
}
|
|
}
|
|
|
|
// Get tickets with pagination, sorting, search, and advanced filters
|
|
$result = $this->ticketModel->getAllTickets($page, $limit, $status, $sortColumn, $sortDirection, $category, $type, $search, $filters, $GLOBALS['currentUser'] ?? []);
|
|
|
|
// Get categories and types for filters (single query)
|
|
$filterOptions = $this->getCategoriesAndTypes();
|
|
$categories = $filterOptions['categories'];
|
|
$types = $filterOptions['types'];
|
|
|
|
// Extract data for the view
|
|
$tickets = $result['tickets'];
|
|
$totalTickets = $result['total'];
|
|
$totalPages = $result['pages'];
|
|
|
|
// Load dashboard statistics
|
|
$stats = $this->statsModel->getAllStats($GLOBALS['currentUser'] ?? []);
|
|
|
|
// Load the dashboard view
|
|
include 'views/DashboardView.php';
|
|
}
|
|
|
|
/**
|
|
* Get categories and types in a single query
|
|
*
|
|
* @return array ['categories' => [...], 'types' => [...]]
|
|
*/
|
|
private function getCategoriesAndTypes(): array
|
|
{
|
|
$sql = "SELECT 'category' as field, category as value FROM tickets WHERE category IS NOT NULL
|
|
UNION
|
|
SELECT 'type' as field, type as value FROM tickets WHERE type IS NOT NULL
|
|
ORDER BY field, value";
|
|
|
|
$result = $this->conn->query($sql);
|
|
$categories = [];
|
|
$types = [];
|
|
|
|
if (!$result) {
|
|
return ['categories' => $categories, 'types' => $types];
|
|
}
|
|
|
|
while ($row = $result->fetch_assoc()) {
|
|
if ($row['field'] === 'category' && !in_array($row['value'], $categories, true)) {
|
|
$categories[] = $row['value'];
|
|
} elseif ($row['field'] === 'type' && !in_array($row['value'], $types, true)) {
|
|
$types[] = $row['value'];
|
|
}
|
|
}
|
|
|
|
return ['categories' => $categories, 'types' => $types];
|
|
}
|
|
}
|