|
Server : LiteSpeed System : Linux terra.hostitbro.com 5.14.0-611.54.3.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Thu May 7 16:31:24 EDT 2026 x86_64 User : outerorb ( 1091) PHP Version : 8.1.34 Disable Function : mail Directory : /home2/outerorb/emp.outerorbittech.in/admin/ | |
|
Path: /home2/outerorb/emp.outerorbittech.in/admin/reports.php
Size: 13.61 KB
Permissions: 0666
<?php
require __DIR__ . '/../includes/helpers.php';
require_admin();
ensure_employees_table();
ensure_attendance_tables();
ensure_leave_tables();
$pdo = db();
$flash = flash();
$year = isset($_GET['year']) ? (int)$_GET['year'] : (int)date('Y');
$month = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('m');
// ---- Headcount ----
$total = (int)$pdo->query("SELECT COUNT(*) FROM employees WHERE deleted_at IS NULL AND employee_status='active'")->fetchColumn();
// By department
$byDept = $pdo->query(
"SELECT department, COUNT(*) AS cnt FROM employees WHERE deleted_at IS NULL AND employee_status='active'
GROUP BY department ORDER BY cnt DESC"
)->fetchAll();
// By gender
$byGender = $pdo->query(
"SELECT gender, COUNT(*) AS cnt FROM employees WHERE deleted_at IS NULL AND employee_status='active'
GROUP BY gender ORDER BY cnt DESC"
)->fetchAll();
// By designation
$byDesig = $pdo->query(
"SELECT d.name AS designation, COUNT(*) AS cnt FROM employees e
LEFT JOIN designations d ON d.id = e.designation_id
WHERE e.deleted_at IS NULL AND e.employee_status='active'
GROUP BY e.designation_id, d.name ORDER BY cnt DESC LIMIT 10"
)->fetchAll();
// ---- Attendance (selected month) ----
$daysInMonth = cal_days_in_month(CAL_GREGORIAN, $month, $year);
$presentCount = (int)$pdo->prepare(
"SELECT COUNT(*) FROM attendance_logs WHERE status='present' AND YEAR(attend_date)=? AND MONTH(attend_date)=?"
)->execute([$year, $month]) && ($r = $pdo->prepare(
"SELECT COUNT(*) FROM attendance_logs WHERE status IN ('present','wfh') AND YEAR(attend_date)=? AND MONTH(attend_date)=?"
)) && $r->execute([$year, $month]) ? (int)$r->fetchColumn() : 0;
// Simpler approach:
$attStmt = $pdo->prepare(
"SELECT e.id,
SUM(CASE WHEN al.status IN ('present','wfh') THEN 1 WHEN al.status='half_day' THEN 0.5 ELSE 0 END) AS days_present
FROM employees e
LEFT JOIN attendance_logs al ON al.employee_id = e.id AND YEAR(al.attend_date)=? AND MONTH(al.attend_date)=?
WHERE e.deleted_at IS NULL AND e.employee_status='active'
GROUP BY e.id"
);
$attStmt->execute([$year, $month]);
$attRows = $attStmt->fetchAll();
$empCount = count($attRows);
$totalPresent = array_sum(array_column($attRows, 'days_present'));
$avgAttPct = $empCount > 0 ? round($totalPresent / ($empCount * $daysInMonth) * 100, 1) : 0;
// ---- Leaves current month ----
$leaveStmt = $pdo->prepare(
"SELECT status, COUNT(*) AS cnt FROM leave_applications
WHERE YEAR(from_date)=? AND MONTH(from_date)=?
GROUP BY status"
);
$leaveStmt->execute([$year, $month]);
$leaveByStatus = [];
foreach ($leaveStmt->fetchAll() as $r) { $leaveByStatus[$r['status']] = (int)$r['cnt']; }
$leaveTotal = array_sum($leaveByStatus);
$leaveApproved= $leaveByStatus['approved'] ?? 0;
// ---- Payroll trend (last 6 months) ----
$payrollTrend = [];
$ensurePayroll = function() use ($pdo) {
try {
$pdo->query('SELECT 1 FROM payroll_runs LIMIT 1');
return true;
} catch (Exception $e) { return false; }
};
if ($ensurePayroll()) {
$trendStmt = $pdo->prepare(
"SELECT pr.year, pr.month, SUM(pe.net_pay) AS total_net, SUM(pe.gross) AS total_gross
FROM payroll_runs pr
JOIN payroll_entries pe ON pe.payroll_run_id = pr.id
WHERE pr.status='finalized'
GROUP BY pr.year, pr.month
ORDER BY pr.year DESC, pr.month DESC
LIMIT 6"
);
$trendStmt->execute();
$payrollTrend = array_reverse($trendStmt->fetchAll());
}
function e(string $v): string { return htmlspecialchars($v, ENT_QUOTES, 'UTF-8'); }
$monthLabel = date('F Y', mktime(0,0,0,$month,1,$year));
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Reports — HRMS</title>
<link rel="stylesheet" href="../assets/css/style.css?v=<?php echo filemtime(__DIR__.'/../assets/css/style.css'); ?>" />
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js"></script>
<style>
.dashboard-container { padding: 20px 24px; max-width: 1200px; margin: 0 auto; }
.stat-grid { display:grid; grid-template-columns:repeat(4,1fr); gap:14px; margin-bottom:20px; }
.stat-card { background:var(--card); border-radius:10px; padding:16px; box-shadow:var(--shadow); }
.stat-num { font-size:1.8rem; font-weight:800; color:var(--text); }
.stat-label { color:var(--muted); font-weight:600; font-size:0.9rem; }
.chart-grid { display:grid; grid-template-columns:repeat(2,1fr); gap:14px; margin-bottom:18px; }
.chart-card, .table-card { background:var(--card); border-radius:10px; padding:14px; box-shadow:var(--shadow); }
.table-std th { padding:10px 12px; font-size:0.78rem; color:var(--muted); text-transform:uppercase; }
.table-std td { padding:10px 12px; }
@media(max-width:900px){ .stat-grid{grid-template-columns:repeat(2,1fr);} .chart-grid{grid-template-columns:1fr;} }
</style>
</head>
<body class="dashboard-layout">
<div class="app-wrapper">
<?php require '_sidebar.php'; ?>
<div class="main-content">
<div class="top-bar">
<div class="top-bar-left">
<button class="sidebar-toggle" id="sidebarToggle" aria-label="Toggle sidebar">☰</button>
<div>
<h1 class="page-title">Reports</h1>
<div class="breadcrumb-nav"><span>Employee statistics and operational trends</span></div>
</div>
</div>
<div class="top-bar-right">
<form method="GET" style="display:flex; gap:8px; align-items:center;">
<select name="month" onchange="this.form.submit()">
<?php for ($m=1;$m<=12;$m++): ?>
<option value="<?php echo $m; ?>" <?php echo $m===$month?'selected':''; ?>><?php echo date('F', mktime(0,0,0,$m)); ?></option>
<?php endfor; ?>
</select>
<select name="year" onchange="this.form.submit()">
<?php for ($y=(int)date('Y'); $y>=(int)date('Y')-3; $y--): ?>
<option value="<?php echo $y; ?>" <?php echo $y===$year?'selected':''; ?>><?php echo $y; ?></option>
<?php endfor; ?>
</select>
</form>
<a class="badge" href="dashboard.php">Dashboard</a>
<a class="badge" href="logout.php">Logout</a>
</div>
</div>
<div class="dashboard-container">
<?php if ($flash): ?><div class="alert <?php echo $flash['type'] === 'error' ? 'alert-error' : 'alert-success'; ?>"><?php echo htmlspecialchars($flash['message'], ENT_QUOTES, 'UTF-8'); ?></div><?php endif; ?>
<div class="stat-grid">
<div class="stat-card">
<div class="stat-num"><?php echo (int)$total; ?></div>
<div class="stat-label">Active Employees</div>
</div>
<div class="stat-card">
<div class="stat-num"><?php echo count($byDept); ?></div>
<div class="stat-label">Departments</div>
</div>
<div class="stat-card">
<div class="stat-num"><?php echo $avgAttPct; ?>%</div>
<div class="stat-label">Avg Attendance (<?php echo $monthLabel; ?>)</div>
</div>
<div class="stat-card">
<div class="stat-num"><?php echo $leaveTotal; ?></div>
<div class="stat-label">Leave Requests (<?php echo $monthLabel; ?>)</div>
</div>
</div>
<div class="chart-grid">
<div class="chart-card">
<h4>Headcount by Department</h4>
<canvas id="deptChart" height="160"></canvas>
</div>
<div class="chart-card">
<h4>Gender Distribution</h4>
<canvas id="genderChart" height="160"></canvas>
</div>
</div>
<div class="chart-grid">
<div class="chart-card">
<h4>Leave Requests by Status (<?php echo $monthLabel; ?>)</h4>
<canvas id="leaveChart" height="140"></canvas>
</div>
<div class="chart-card">
<h4>Payroll Trend (Net Pay)</h4>
<?php if (!empty($payrollTrend)): ?>
<canvas id="payrollChart" height="140"></canvas>
<?php else: ?>
<div style="color:var(--muted);">No finalized payroll runs yet.</div>
<?php endif; ?>
</div>
</div>
<div class="chart-grid">
<div class="table-card">
<h4>Top Designations</h4>
<table class="table-std">
<thead><tr><th>Designation</th><th style="text-align:right;">Count</th></tr></thead>
<tbody>
<?php foreach ($byDesig as $d): ?>
<tr>
<td><?php echo e($d['designation']); ?></td>
<td style="text-align:right; font-weight:700;"><?php echo (int)$d['cnt']; ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<div class="table-card">
<h4>Attendance Summary (<?php echo $monthLabel; ?>)</h4>
<?php
$attGrouped = $pdo->prepare(
"SELECT e.department, COUNT(DISTINCT e.id) AS emp_count, SUM(CASE WHEN al.status IN ('present','wfh') THEN 1 WHEN al.status='half_day' THEN 0.5 ELSE 0 END) AS days_present
FROM employees e
LEFT JOIN attendance_logs al ON al.employee_id = e.id AND YEAR(al.attend_date)=? AND MONTH(al.attend_date)=?
WHERE e.deleted_at IS NULL AND e.employee_status='active'
GROUP BY e.department ORDER BY e.department"
);
$attGrouped->execute([$year, $month]);
$attGrouped = $attGrouped->fetchAll();
?>
<table class="table-std">
<thead><tr><th>Department</th><th style="text-align:right;">Employees</th><th style="text-align:right;">Avg Days</th><th style="text-align:right;">Att%</th></tr></thead>
<tbody>
<?php foreach ($attGrouped as $ag):
$possible = $ag['emp_count'] * $daysInMonth;
$pct = $possible > 0 ? round($ag['days_present'] / $possible * 100, 1) : 0;
?>
<tr>
<td><?php echo e($ag['department']); ?></td>
<td style="text-align:right;"><?php echo (int)$ag['emp_count']; ?></td>
<td style="text-align:right;"><?php echo $ag['emp_count'] > 0 ? round($ag['days_present'] / $ag['emp_count'], 1) : 0; ?></td>
<td style="text-align:right; font-weight:700; color:<?php echo $pct >= 80 ? '#16a34a' : '#ef4444'; ?>;"><?php echo $pct; ?>%</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
<script>
// Dept chart
new Chart(document.getElementById('deptChart'), {
type: 'bar',
data: {
labels: <?php echo json_encode(array_column($byDept, 'department')); ?>,
datasets: [{ label: 'Employees', data: <?php echo json_encode(array_column($byDept, 'cnt')); ?>, backgroundColor: '#3b82f6', borderRadius: 6 }]
}, options: { responsive: true, plugins: { legend: { display: false } }, scales: { y: { beginAtZero: true, ticks: { precision:0 } } } }
});
// Gender
new Chart(document.getElementById('genderChart'), {
type: 'doughnut', data: { labels: <?php echo json_encode(array_column($byGender,'gender')); ?>, datasets: [{ data: <?php echo json_encode(array_column($byGender,'cnt')); ?>, backgroundColor: ['#3b82f6','#ec4899','#8b5cf6','#6b7280'] }] }, options: { responsive:true, plugins:{ legend:{ position:'bottom' } } }
});
// Leave
new Chart(document.getElementById('leaveChart'), { type:'pie', data:{ labels: <?php echo json_encode(array_keys($leaveByStatus)); ?>, datasets:[{ data: <?php echo json_encode(array_values($leaveByStatus)); ?>, backgroundColor:['#34d399','#f87171','#fbbf24','#60a5fa','#a78bfa'] }] }, options:{ responsive:true, plugins:{ legend:{ position:'bottom' } } } });
<?php if (!empty($payrollTrend)): ?>
new Chart(document.getElementById('payrollChart'), { type:'line', data:{ labels: <?php echo json_encode(array_map(fn($r)=>date('M Y', mktime(0,0,0,(int)$r['month'],1,(int)$r['year'])), $payrollTrend)); ?>, datasets:[{ label:'Net Pay (₹)', data: <?php echo json_encode(array_column($payrollTrend,'total_net')); ?>, borderColor:'#2563eb', backgroundColor:'rgba(37,99,235,.08)', tension:.3, fill:true }] }, options:{ responsive:true, plugins:{ legend:{ display:false } }, scales:{ y:{ beginAtZero:true } } } });
<?php endif; ?>
</script>
</body>
</html>