remove the recent tickets and just fix the chart
<?php
session_start();
date_default_timezone_set('Asia/Manila');
global $wpdb;
// Using your exact table structure
$tickets_table = 'tickets';
$users_table = 'users';
$departments_table = 'departments';
// Initialize default stats to prevent null errors
$default_stats = [
'total_tickets' => 0,
'avg_resolution_time' => null,
'min_resolution_time' => null,
'max_resolution_time' => null,
'solved_count' => 0,
'open_count' => 0,
'assigned_count' => 0,
'pending_count' => 0,
'closed_count' => 0
];
// Fetch and calculate statistics based on your schema
$stats_query = $wpdb->get_row("
SELECT
COUNT(*) as total_tickets,
AVG(TIMESTAMPDIFF(MINUTE, created_at, updated_at)) as avg_resolution_time,
MIN(TIMESTAMPDIFF(MINUTE, created_at, updated_at)) as min_resolution_time,
MAX(TIMESTAMPDIFF(MINUTE, created_at, updated_at)) as max_resolution_time,
SUM(CASE WHEN status = 'solved' THEN 1 ELSE 0 END) as solved_count,
SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) as open_count,
SUM(CASE WHEN status = 'assigned' THEN 1 ELSE 0 END) as assigned_count,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) as closed_count
FROM $tickets_table
", ARRAY_A);
// Merge with defaults to ensure all keys exist
$stats = array_merge($default_stats, (array)$stats_query);
// Fetch ticket data for the table with department names
$tickets = $wpdb->get_results("
SELECT
t.*,
u.user_login as assigned_name,
d.name as department_name
FROM $tickets_table t
LEFT JOIN $users_table u ON t.assigned_to = u.id
LEFT JOIN $departments_table d ON t.department_id = d.id
ORDER BY t.created_at DESC
LIMIT 100
", ARRAY_A);
// Convert minutes to hours for display
function minutesToHours($minutes) {
if ($minutes === null || $minutes === 0) return 'N/A';
$hours = floor($minutes / 60);
$mins = $minutes % 60;
return sprintf("%dh %02dm", $hours, $mins);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Dashboard - RJL DeskTrack</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
<style>
body {
font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
margin: 0; padding: 0;
background-color: #f4f7f9; color: #333;
}
.dashboard-container { display: flex; }
.sidebar {
width: 240px;
background-color: #c1d8f0;
color: #000;
height: 100vh;
position: fixed;
left: 0; top: 0;
overflow-y: auto;
box-shadow: 2px 0 5px rgba(0, 0, 0, 0.1);
z-index: 1000;
}
.sidebar-header {
padding: 20px;
text-align: center;
}
.sidebar-header .inventory-name {
font-size: 24px;
font-weight: bold;
color: #000;
}
.sidebar-menu {
padding: 0;
}
.sidebar-menu ul {
list-style: none;
margin: 0;
padding: 0;
}
.sidebar-menu li a {
display: flex;
align-items: center;
padding: 12px 20px;
text-decoration: none;
color: #000;
transition: 0.3s;
font-size: 16px;
}
.sidebar-menu li a i {
margin-right: 12px;
width: 20px;
text-align: center;
}
.sidebar-menu li a:hover,
.sidebar-menu li a.active {
background-color: #ffffff;
color: #000;
}
.header {
position: fixed;
top: 0;
left: 240px;
right: 0;
display: flex;
justify-content: space-between;
align-items: center;
background-color: #4663ac;
padding: 10px 30px;
height: 60px;
z-index: 999;
box-shadow: 0 2px 5px rgba(0,0,0,0.1);
}
.header-left {
display: flex;
align-items: center;
}
.header-left .date-time {
font-size: 15px;
color: #fff;
font-weight: 500;
}
.header-right {
display: flex;
align-items: center;
font-size: 16px;
color: #ffffff;
}
.user-dropdown {
position: relative;
display: inline-block;
cursor: pointer;
}
.user-dropdown-content {
display: none;
position: absolute;
right: 0;
background-color: #D1B48C;
min-width: 100px;
box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
z-index: 1;
border-radius: 4px;
}
.user-dropdown-header {
padding: 8px 12px;
background-color: #D1B48C;
border-bottom: 1px solid #e9ecef;
display: flex;
align-items: center;
gap: 8px;
}
.user-dropdown-header i {
font-size: 20px;
color: #000000;
}
.user-dropdown-content.user-info {
display: flex;
align-items: right;
gap: 1px;
color: #000000;
}
.user-dropdown-content a {
display: flex;
padding: 8px 15px;
align-items: center;
color: #000000;
text-decoration: none;
font-size: 16px;
transition: all 0.2s;
}
.user-dropdown-content a i {
font-size: 16px;
margin-right: 8px;
}
.user-dropdown-content a:hover {
text-decoration: underline;
color: #000000;
}
.user-dropdown:hover .user-dropdown-content {
display: block;
}
.header-right i {
color: #ffffff;
font-size:40px;
}
.header-right span {
font-size: 15px;
color: #ffffff;
}
.main-content {
margin-left: 240px;
padding: 80px 20px 20px 20px;
flex-grow: 1;
}
/* Strict 2x2 Grid Layout */
.dashboard-grid {
display: grid;
grid-template-columns: repeat(2, 1fr);
grid-template-rows: repeat(2, auto);
gap: 20px;
width: 100%;
max-width: 800px;
margin: 0 auto;
}
.dashboard-box {
background-color: #fff;
padding: 20px;
border-radius: 8px;
box-shadow: 0 4px 10px rgba(0, 0, 0, 0.06);
border: 1px solid #e9ecef;
display: flex;
align-items: center;
gap: 15px;
transition: transform 0.2s ease-in-out, box-shadow 0.2s ease-in-out;
}
.dashboard-box:hover {
transform: translateY(-3px);
box-shadow: 0 6px 15px rgba(0, 0, 0, 0.09);
}
.dashboard-box i.icon {
font-size: 24px;
color: #fff;
border-radius: 50%;
padding: 12px;
width: 50px;
height: 50px;
display: inline-flex;
align-items: center;
justify-content: center;
flex-shrink: 0;
}
.dashboard-box:nth-child(1) i.icon { background-color: #17a2b8; }
.dashboard-box:nth-child(2) i.icon { background-color: #28a745; }
.dashboard-box:nth-child(3) i.icon { background-color: #ffc107; }
.dashboard-box:nth-child(4) i.icon { background-color: #dc3545; }
.dashboard-box .detail {
text-align: left;
overflow: hidden;
}
.dashboard-box .count {
font-size: 24px;
font-weight: 600;
color: #343a40;
line-height: 1.1;
margin-bottom: 5px;
}
.dashboard-box .label {
font-size: 14px;
color: #6c757d;
}
/* Responsive Adjustments */
@media (max-width: 768px) {
.dashboard-grid {
grid-template-columns: 1fr;
grid-template-rows: auto;
max-width: 400px;
}
}
</style>
</head>
<body>
<div class="dashboard-container">
<aside class="sidebar">
<div class="sidebar-header">
<div class="inventory-name">RJL DeskTrack</div>
</div>
<div class="sidebar-menu">
<ul>
<?php if ($_SESSION['role'] !== 'user'): ?>
<li><a href="http://localhost/helpdesk/?page_id=57"><i class="fas fa-tachometer-alt"></i> Dashboard</a></li>
<li><a href="http://localhost/helpdesk/?page_id=62"><i class="fas fa-users-cog"></i> Users</a></li>
<li><a href="http://localhost/helpdesk/?page_id=66"><i class="fas fa-building"></i> Departments</a></li>
<?php endif; ?>
<li><a href="http://localhost/helpdesk/?page_id=70"><i class="fas fa-ticket-alt"></i> Tickets</a></li>
<li><a href="http://localhost/helpdesk/?page_id=74"><i class="fas fa-envelope"></i> Messages</a></li>
<?php if ($_SESSION['role'] !== 'user'): ?>
<li><a href="http://localhost/helpdesk/?page_id=78"><i class="fas fa-chart-bar"></i> Reports</a></li>
<?php endif; ?>
</ul>
</div>
</aside>
<main class="main-content">
<header class="header">
<div class="header-left">
<span class="date-time" id="current-date-time"></span>
</div>
<div class="header-right">
<div class="user-dropdown">
<div class="user-info">
<span id="current-username-header">
<?= htmlspecialchars($_SESSION['username']) ?>
<i class="fas fa-user-circle"></i>
</span>
</div>
<div class="user-dropdown-content">
<div class="user-dropdown-header">
<i class="fas fa-user-circle" style="color:#000000;"></i>
<span style="color:#000000;"><?= htmlspecialchars($_SESSION['username']) ?></span>
</div>
<a href="#" onclick="return confirm('Are you sure you want to log out?');">
<i class="fas fa-sign-out-alt" style="font-size: 16px; color:#000000;"></i>Logout
</a>
</div>
</div>
</div>
</header>
<h1>Performance Reports</h1>
<div class="report-container">
<div class="stat-card">
<h3>Total Tickets</h3>
<div class="stat-value"><?= (int)$stats['total_tickets'] ?></div>
<div class="stat-description">All tickets in the system</div>
</div>
<div class="stat-card">
<h3>Solved Tickets</h3>
<div class="stat-value"><?= (int)$stats['solved_count'] ?></div>
<div class="stat-description">Successfully solved tickets</div>
</div>
<div class="stat-card">
<h3>Open Tickets</h3>
<div class="stat-value"><?= (int)$stats['open_count'] ?></div>
<div class="stat-description">Tickets awaiting resolution</div>
</div>
<div class="stat-card">
<h3>Avg. Resolution Time</h3>
<div class="stat-value"><?= minutesToHours($stats['avg_resolution_time']) ?></div>
<div class="stat-description">Average time to resolve tickets</div>
</div>
</div>
<!-- Charts Container - Only show if we have data -->
<?php if ($stats['total_tickets'] > 0): ?>
<div class="chart-container">
<canvas id="resolutionTimeChart"></canvas>
</div>
<div class="chart-container">
<canvas id="ticketsByStatusChart"></canvas>
</div>
<?php endif; ?>
<!-- Detailed Tickets Table -->
<div class="table-container">
<h2>Recent Tickets</h2>
<?php if (!empty($tickets)): ?>
<table>
<thead>
<tr>
<th>Ticket #</th>
<th>Subject</th>
<th>Status</th>
<th>Priority</th>
<th>Created</th>
<th>Assigned To</th>
</tr>
</thead>
<tbody>
<?php foreach ($tickets as $ticket): ?>
<tr>
<td><?= htmlspecialchars($ticket['ticket_number'] ?? 'N/A') ?></td>
<td><?= htmlspecialchars($ticket['subject'] ?? 'No subject') ?></td>
<td>
<?php switch($ticket['status'] ?? ''):
case 'solved': ?>
<span class="badge badge-resolved">Solved</span>
<?php break;
case 'closed': ?>
<span class="badge badge-closed">Closed</span>
<?php break;
case 'open': ?>
<span class="badge badge-open">Open</span>
<?php break;
case 'assigned': ?>
<span class="badge badge-assigned">Assigned</span>
<?php break;
case 'pending': ?>
<span class="badge badge-pending">Pending</span>
<?php break;
default: ?>
<span class="badge"><?= $ticket['status'] ?? 'Unknown' ?></span>
<?php endswitch; ?>
</td>
<td class="priority-<?= $ticket['priority'] ?? 'medium' ?>">
<?= ucfirst($ticket['priority'] ?? 'medium') ?>
</td>
<td><?= isset($ticket['created_at']) ? date('M j, Y H:i', strtotime($ticket['created_at'])) : 'N/A' ?></td>
<td><?= $ticket['assigned_name'] ?? 'Unassigned' ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php else: ?>
<div class="notice notice-info">
<p>No tickets found in the system.</p>
</div>
<?php endif; ?>
</div>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
document.addEventListener('DOMContentLoaded', function() {
// Debug - check if chart containers exist
console.log('Chart containers:', {
resolution: document.getElementById('resolutionTimeChart'),
status: document.getElementById('ticketsByStatusChart')
});
<?php if ($stats['total_tickets'] > 0): ?>
// Resolution Time Chart - with more checks
const resolutionCtx = document.getElementById('resolutionTimeChart');
if (resolutionCtx) {
console.log('Creating resolution time chart');
try {
new Chart(resolutionCtx, {
type: 'bar',
data: {
labels: ['Minimum', 'Average', 'Maximum'],
datasets: [{
label: 'Resolution Time (hours)',
data: [
<?= $stats['min_resolution_time'] ?? 0 ?>,
<?= $stats['avg_resolution_time'] ?? 0 ?>,
<?= $stats['max_resolution_time'] ?? 0 ?>
].map(min => min ? (min/60).toFixed(1) : 0),
backgroundColor: [
'rgba(75, 192, 192, 0.6)',
'rgba(54, 162, 235, 0.6)',
'rgba(255, 99, 132, 0.6)'
]
}]
}
});
} catch (e) {
console.error('Chart error:', e);
}
}
// Status Chart
const statusCtx = document.getElementById('ticketsByStatusChart');
if (statusCtx) {
console.log('Creating status chart');
try {
new Chart(statusCtx, {
type: 'doughnut',
data: {
labels: ['Solved', 'Open', 'Assigned', 'Pending', 'Closed'],
datasets: [{
data: [
<?= $stats['solved_count'] ?? 0 ?>,
<?= $stats['open_count'] ?? 0 ?>,
<?= $stats['assigned_count'] ?? 0 ?>,
<?= $stats['pending_count'] ?? 0 ?>,
<?= $stats['closed_count'] ?? 0 ?>
],
backgroundColor: [
'rgba(40, 167, 69, 0.6)',
'rgba(255, 193, 7, 0.6)',
'rgba(13, 110, 253, 0.6)',
'rgba(255, 152, 0, 0.6)',
'rgba(108, 117, 125, 0.6)'
]
}]
}
});
} catch (e) {
console.error('Status chart error:', e);
}
}
<?php endif; ?>
});
</script>