« Back to History
attendance_dashboard_main.php
|
20260920_164915.php
Initial Domain Snapshot
Copy Code
<?php require_once __DIR__ . '/modules/auth/page_acl.php'; $ctx = page_require_access('attendance_dashboard'); $pdo = $ctx['pdo']; $company_id = (int)$ctx['company_id']; $u = $ctx['user'] ?? null; $company_ids = [$company_id]; $company_placeholders = '?'; $groupStmt = $pdo->prepare("SELECT id FROM company_groups WHERE company_id = :cid LIMIT 1"); $groupStmt->execute([':cid' => $company_id]); $group_id = $groupStmt->fetchColumn(); if ($group_id !== false && $group_id !== null) { $mapStmt = $pdo->prepare("SELECT company_id FROM company_group_map WHERE group_id = :gid ORDER BY company_id ASC"); $mapStmt->execute([':gid' => $group_id]); $company_ids = array_column($mapStmt->fetchAll(PDO::FETCH_ASSOC), 'company_id'); if (empty($company_ids)) { $company_ids = [$company_id]; } } $company_ids = array_values(array_unique($company_ids)); sort($company_ids, SORT_NUMERIC); $group_master_company = $company_ids[0]; $companyParams = []; foreach ($company_ids as $i => $cid) { $companyParams[':cid'.$i] = $cid; } $company_placeholders = implode(',', array_keys($companyParams)); require_once __DIR__ . '/partials/header.php'; /* ========================= INIT ========================= */ $totalEmp = 0; $present = 0; $late = 0; $absent = 0; $deptData = []; $deptNames = []; /* ========================= TOTAL EMPLOYEES ========================= */ $stmt = $pdo->prepare( "SELECT COUNT(*) FROM emp_master WHERE company_id IN ($company_placeholders)" ); $stmt->execute($companyParams); $totalEmp = (int)$stmt->fetchColumn(); /* ========================= TODAY ATTENDANCE ========================= */ $today = date('Y-m-d'); $stmt = $pdo->prepare(" SELECT em.Empcode AS employee_code, MIN(al.punch_time) AS first_punch FROM attendance_logs AS al JOIN emp_master AS em ON ( al.employee_code = em.Empcode OR ( al.employee_code REGEXP '^[0-9]+$' AND em.Empcode REGEXP '^[0-9]+$' AND CAST(al.employee_code AS UNSIGNED) = CAST(em.Empcode AS UNSIGNED) ) ) WHERE em.company_id IN ($company_placeholders) AND DATE(al.punch_time) = :dt GROUP BY em.Empcode "); $params = $companyParams; $params[':dt'] = $today; $stmt->execute($params); $attendance = $stmt->fetchAll(PDO::FETCH_ASSOC); /* ========================= PRESENT + LATE LOGIC ========================= */ $presentList = []; /* ========================= DEPARTMENT MASTER ========================= */ $deptStmt = $pdo->prepare( "SELECT company_id, departmentid AS departmentId, department_name FROM department_master WHERE company_id IN ($company_placeholders)" ); $deptStmt->execute($companyParams); $deptNamesById = []; while ($d = $deptStmt->fetch(PDO::FETCH_ASSOC)) { $company = (int)$d['company_id']; $deptId = (int)$d['departmentId']; $deptNames[$company][$deptId] = trim($d['department_name']); if ($company === $group_master_company || !isset($deptNamesById[$deptId])) { $deptNamesById[$deptId] = trim($d['department_name']); } } /* ========================= EMPLOYEE MASTER + POLICY ========================= */ $empStmt = $pdo->prepare( "SELECT Empcode, Name, DeptID, CatID, `Shift`, company_id FROM emp_master WHERE company_id IN ($company_placeholders)" ); $empStmt->execute($companyParams); $empDept = []; $empData = []; $empDataByKey = []; while ($e = $empStmt->fetch(PDO::FETCH_ASSOC)) { $code = trim($e['Empcode'] ?? ''); $company = (int)$e['company_id']; $emp = [ 'empcode' => $code, 'name' => trim($e['Name'] ?: 'Unknown'), 'dept_id' => $e['DeptID'], 'cat_id' => (int)($e['CatID'] ?? 0), 'shift' => trim($e['Shift'] ?? ''), 'company_id' => $company, ]; $empDept[$code] = $e['DeptID']; $empCompany[$code] = $company; $empData[$code] = $emp; $empDataByKey[$code] = &$empData[$code]; if (ctype_digit($code)) { $norm = (string)(int)$code; if ($norm !== $code) { $empDataByKey[$norm] = &$empData[$code]; } } } $shiftColumn = 'in_time'; $columnStmt = $pdo->prepare("SHOW COLUMNS FROM shift_master LIKE 'in_time'"); $columnStmt->execute(); if (!$columnStmt->fetch()) { $columnStmt = $pdo->prepare("SHOW COLUMNS FROM shift_master LIKE 'shift_in'"); $columnStmt->execute(); if ($columnStmt->fetch()) { $shiftColumn = 'shift_in'; } } $shiftStmt = $pdo->prepare( "SELECT id, shift_code, shift_name, {$shiftColumn} AS shift_in, company_id FROM shift_master WHERE company_id IN ($company_placeholders)" ); $shiftStmt->execute($companyParams); $shiftDataByCompany = []; $shiftDataGroupById = []; $shiftDataGroupByCode = []; while ($s = $shiftStmt->fetch(PDO::FETCH_ASSOC)) { $company = (int)$s['company_id']; $deptId = (int)$s['id']; $shiftDataByCompany[$company]['by_id'][$deptId] = $s; $code = trim($s['shift_code'] ?? ''); if ($code !== '') { $shiftDataByCompany[$company]['by_code'][$code] = $s; $shiftDataByCompany[$company]['by_code'][strtolower($code)] = $s; } if (!isset($shiftDataGroupById[$deptId])) { $shiftDataGroupById[$deptId] = $s; } if ($code !== '' && !isset($shiftDataGroupByCode[$code])) { $shiftDataGroupByCode[$code] = $s; $shiftDataGroupByCode[strtolower($code)] = $s; } } $policyStmt = $pdo->prepare( "SELECT id, late_arrival_allow, company_id FROM policy_master WHERE company_id IN ($company_placeholders)" ); $policyStmt->execute($companyParams); $policyAllow = []; $policyAllowGroup = []; while ($p = $policyStmt->fetch(PDO::FETCH_ASSOC)) { $company = (int)$p['company_id']; $policyId = (int)$p['id']; $policyAllow[$company][$policyId] = $p['late_arrival_allow']; if (!isset($policyAllowGroup[$policyId])) { $policyAllowGroup[$policyId] = $p['late_arrival_allow']; } } function timeToSeconds($time) { if (!$time) return 0; $parts = explode(':', $time); if (count($parts) === 2) { [$h, $m] = $parts; $s = 0; } elseif (count($parts) === 3) { [$h, $m, $s] = $parts; } else { return 0; } return ((int)$h * 3600) + ((int)$m * 60) + (int)$s; } $presentEmployees = []; $lateEmployees = []; $absentEmployees = []; $attendanceMap = []; foreach ($attendance as $row) { $code = trim($row['employee_code'] ?? ''); $employee = $empDataByKey[$code] ?? null; if (!$employee && ctype_digit($code)) { $employee = $empDataByKey[(string)(int)$code] ?? null; } if (!$employee) { $employee = [ 'empcode' => $code, 'name' => 'Unknown', 'dept_id' => null, 'cat_id' => 0, 'shift' => '', 'company_id' => 0, ]; } $matchedCode = $employee['empcode'] ?? $code; $attendanceMap[$matchedCode] = true; $presentList[] = $matchedCode; $firstPunch = date('H:i:s', strtotime($row['first_punch'])); $actualSeconds = timeToSeconds($firstPunch); $catId = (int)$employee['cat_id']; $company = (int)$employee['company_id']; $shiftKey = trim($employee['shift'] ?? ''); $shift = null; $companyShiftData = $shiftDataByCompany[$company] ?? ['by_id' => [], 'by_code' => []]; $masterShiftData = $shiftDataByCompany[$group_master_company] ?? ['by_id' => [], 'by_code' => []]; $companyPolicy = $policyAllow[$company] ?? []; $masterPolicy = $policyAllow[$group_master_company] ?? []; if ($company > 0 && $shiftKey !== '') { $shift = $companyShiftData['by_id'][(int)$shiftKey] ?? $companyShiftData['by_code'][$shiftKey] ?? $companyShiftData['by_code'][strtolower($shiftKey)] ?? null; } if (!$shift && $company > 0 && $catId > 0) { $shift = $companyShiftData['by_id'][$catId] ?? null; } $policyLate = $companyPolicy[$catId] ?? $masterPolicy[$catId] ?? $policyAllowGroup[$catId] ?? null; if (!$shift && $shiftKey !== '') { $shift = $companyShiftData['by_id'][(int)$shiftKey] ?? $masterShiftData['by_id'][(int)$shiftKey] ?? $shiftDataGroupById[(int)$shiftKey] ?? $companyShiftData['by_code'][$shiftKey] ?? $masterShiftData['by_code'][$shiftKey] ?? $shiftDataGroupByCode[$shiftKey] ?? $companyShiftData['by_code'][strtolower($shiftKey)] ?? $masterShiftData['by_code'][strtolower($shiftKey)] ?? $shiftDataGroupByCode[strtolower($shiftKey)] ?? null; } if (!$shift && $catId > 0) { $shift = $companyShiftData['by_id'][$catId] ?? $masterShiftData['by_id'][$catId] ?? $shiftDataGroupById[$catId] ?? null; } if ($shift && trim($shift['shift_in']) !== '' && trim((string)$policyLate) !== '') { $baseSeconds = timeToSeconds($shift['shift_in']); $allowedOffset = timeToSeconds($policyLate); $allowedAtSeconds = $baseSeconds + $allowedOffset; $allowed = trim($policyLate); $status = $actualSeconds > $allowedAtSeconds ? 'Late' : 'On time'; $lateByMinutes = $status === 'Late' ? round(($actualSeconds - $allowedAtSeconds) / 60, 1) : 0; $allowedAt = gmdate('H:i:s', $allowedAtSeconds); } else { $allowed = null; $allowedAtSeconds = null; $status = 'Invalid shift/policy'; $lateByMinutes = null; $allowedAt = ''; } $deptId = (int)$employee['dept_id']; $presentEmployees[] = [ 'code' => $matchedCode, 'name' => $employee['name'], 'department' => $deptNames[$company][$deptId] ?? $deptNames[$group_master_company][$deptId] ?? $deptNamesById[$deptId] ?? 'Dept '.$deptId, 'cat_id' => $employee['cat_id'], 'shift_value' => $shift['shift_code'] ?? $employee['shift'] ?? '', 'shift_in' => $shift['shift_in'] ?? '', 'first_punch' => $firstPunch, 'allowed_after' => $allowed, 'allowed_at' => $allowedAt, 'status' => $status, 'late_by' => $lateByMinutes, ]; if ($status === 'Late') { $lateEmployees[] = end($presentEmployees); } } foreach ($empData as $code => $employee) { if (isset($attendanceMap[$code])) continue; $company = (int)$employee['company_id']; $deptId = (int)$employee['dept_id']; $absentEmployees[] = [ 'code' => $code, 'name' => $employee['name'], 'department' => $deptNames[$company][$deptId] ?? $deptNames[$group_master_company][$deptId] ?? $deptNamesById[$deptId] ?? 'Dept '.$deptId, 'cat_id' => $employee['cat_id'], 'status' => 'Absent', ]; } $present = count($presentEmployees); $late = count($lateEmployees); $absent = $totalEmp - $present; /* ========================= DEPARTMENT CALCULATION ========================= */ $presentMap = array_flip($presentList); foreach($empDept as $emp => $dept){ $company = $empCompany[$emp] ?? 0; $deptKey = $company . '|' . $dept; if(!isset($deptData[$deptKey])){ $deptData[$deptKey] = [ 'company_id' => $company, 'dept_id' => $dept, 'name' => $deptNames[$company][$dept] ?? $deptNames[$group_master_company][$dept] ?? $deptNamesById[$dept] ?? 'Dept '.$dept, 'present' => 0, 'absent' => 0, ]; } if(isset($presentMap[$emp])){ $deptData[$deptKey]['present']++; } else { $deptData[$deptKey]['absent']++; } } $presentJSON = json_encode($presentEmployees, JSON_HEX_TAG|JSON_HEX_APOS|JSON_HEX_QUOT|JSON_HEX_AMP); $lateJSON = json_encode($lateEmployees, JSON_HEX_TAG|JSON_HEX_APOS|JSON_HEX_QUOT|JSON_HEX_AMP); $absentJSON = json_encode($absentEmployees, JSON_HEX_TAG|JSON_HEX_APOS|JSON_HEX_QUOT|JSON_HEX_AMP); ?> <div class="container-fluid mt-3"> <h3 class="mb-3">Attendance Dashboard</h3> <!-- CARDS --> <div class="row mb-4"> <div class="col-md-3"> <div class="card text-white bg-primary shadow"> <div class="card-body"> <h6>Total Employees</h6> <h2><?= $totalEmp ?></h2> </div> </div> </div> <div class="col-md-3"> <div id="presentCard" class="card text-white bg-success shadow cursor-pointer" style="cursor:pointer;"> <div class="card-body"> <h6>Present</h6> <h2><?= $present ?></h2> </div> </div> </div> <div class="col-md-3"> <div id="lateCard" class="card text-white bg-warning shadow cursor-pointer" style="cursor:pointer;"> <div class="card-body"> <h6>Late</h6> <h2><?= $late ?></h2> </div> </div> </div> <div class="col-md-3"> <div id="absentCard" class="card text-white bg-danger shadow cursor-pointer" style="cursor:pointer;"> <div class="card-body"> <h6>Absent</h6> <h2><?= $absent ?></h2> </div> </div> </div> </div> <div id="attendanceListSection" class="card shadow mb-4" style="display:none;"> <div class="card-header bg-secondary text-white d-flex justify-content-between align-items-center"> <div id="attendanceListTitle">Attendance List</div> <button id="closeAttendanceList" class="btn btn-sm btn-light">Close</button> </div> <div class="card-body p-0"> <div class="table-responsive"> <table class="table table-bordered table-hover mb-0"> <thead class="table-light"> <tr id="attendanceListHeader"> <th>Employee Code</th> <th>Name</th> <th>Department</th> <th>Category</th> <th>Shift</th> <th>Status</th> <th>First Punch</th> <th>Allowed At</th> <th>Late By (min)</th> </tr> </thead> <tbody id="attendanceListBody"></tbody> </table> </div> </div> </div> <!-- DEPARTMENT TABLE --> <div class="card shadow"> <div class="card-header bg-dark text-white"> <h5 class="mb-0">Department-wise Attendance</h5> </div> <div class="card-body p-0"> <table class="table table-bordered table-striped mb-0"> <thead class="table-light"> <tr> <th>Department</th> <th class="text-center">Present</th> <th class="text-center">Absent</th> <th class="text-center">%</th> </tr> </thead> <tbody> <?php foreach($deptData as $d): $total = $d['present'] + $d['absent']; $per = $total > 0 ? round(($d['present']/$total)*100,1) : 0; ?> <tr> <td><?= htmlspecialchars($d['name']) ?></td> <td class="text-center text-success fw-bold"> <?= $d['present'] ?> </td> <td class="text-center text-danger fw-bold"> <?= $d['absent'] ?> </td> <td class="text-center"> <?= $per ?>% </td> </tr> <?php endforeach; ?> </tbody> </table> </div> </div> </div> <script> (function(){ const presentRows = <?= $presentJSON ?>; const lateRows = <?= $lateJSON ?>; const absentRows = <?= $absentJSON ?>; const section = document.getElementById('attendanceListSection'); const title = document.getElementById('attendanceListTitle'); const body = document.getElementById('attendanceListBody'); const closeBtn = document.getElementById('closeAttendanceList'); const presentCard = document.getElementById('presentCard'); const lateCard = document.getElementById('lateCard'); const absentCard = document.getElementById('absentCard'); function formatValue(value) { return value === null || value === undefined ? '' : value; } function renderRows(rows) { body.innerHTML = ''; if (rows.length === 0) { body.innerHTML = '<tr><td colspan="8" class="text-center">No records found.</td></tr>'; return; } rows.forEach(r => { const tr = document.createElement('tr'); tr.innerHTML = ` <td>${formatValue(r.code)}</td> <td>${formatValue(r.name)}</td> <td>${formatValue(r.department)}</td> <td>${formatValue(r.cat_id)}</td> <td>${formatValue(r.shift_value)}</td> <td>${formatValue(r.status)}</td> <td>${formatValue(r.first_punch)}</td> <td>${formatValue(r.allowed_at)}</td> <td>${formatValue(r.late_by)}</td> `; body.appendChild(tr); }); } function showList(titleText, rows) { title.textContent = titleText; renderRows(rows); section.style.display = 'block'; section.scrollIntoView({behavior:'smooth', block:'start'}); } presentCard && presentCard.addEventListener('click', function(){ showList('Present Employees', presentRows); }); lateCard && lateCard.addEventListener('click', function(){ showList('Late Employees', lateRows); }); absentCard && absentCard.addEventListener('click', function(){ showList('Absent Employees', absentRows); }); closeBtn && closeBtn.addEventListener('click', function(){ section.style.display = 'none'; }); })(); </script> <?php require_once __DIR__ . '/partials/footer.php'; ?>