📄 File: payroll.php

Path: /home2/outerorb/emp.outerorbittech.in/admin/payroll.php

Size: 19.55 KB

Permissions: 0666

<?php
require __DIR__ . '/../includes/helpers.php';
require_admin();
ensure_payroll_tables();
ensure_employees_table();
ensure_attendance_tables();

$roleLabel = 'Admin';
$pdo   = db();
$flash = flash();
$admin = current_admin();

$year  = isset($_GET['year'])  ? (int)$_GET['year']  : (int)date('Y');
$month = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('m');

// ---------- POST: generate / save / finalize ----------
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (!verify_csrf($_POST['csrf_token'] ?? '')) {
        redirect_with_message('payroll.php?year='.$year.'&month='.$month, 'Session expired.', 'error');
    }
    $action = $_POST['action'] ?? '';

    // Generate / recalculate payroll run
    if ($action === 'generate') {
        // Get or create run
        $run = $pdo->prepare('SELECT * FROM payroll_runs WHERE year=? AND month=? LIMIT 1');
        $run->execute([$year, $month]);
        $run = $run->fetch();
        if (!$run) {
            $pdo->prepare('INSERT INTO payroll_runs (year,month,created_by) VALUES (?,?,?)')->execute([$year, $month, $admin['id'] ?? null]);
            $runId = (int)$pdo->lastInsertId();
        } elseif ($run['status'] === 'finalized') {
            redirect_with_message('payroll.php?year='.$year.'&month='.$month, 'Payroll already finalized.', 'error');
        } else {
            $runId = (int)$run['id'];
        }

        $daysInMonth = cal_days_in_month(CAL_GREGORIAN, $month, $year);

        // Active employees with salary structures
        $emps = $pdo->prepare(
            "SELECT e.*, ss.basic, ss.hra, ss.da, ss.conveyance, ss.special_allowance, ss.other_allowance,
                    ss.pf_applicable, ss.esi_applicable, ss.pt_applicable
             FROM employees e
             JOIN salary_structures ss ON ss.employee_id = e.id
             WHERE e.deleted_at IS NULL AND e.employee_status = 'active'"
        );
        $emps->execute();
        $emps = $emps->fetchAll();

        foreach ($emps as $emp) {
            // Working days from attendance
            $attStmt = $pdo->prepare(
                "SELECT SUM(CASE WHEN status='present' THEN 1 WHEN status='wfh' THEN 1 WHEN status='half_day' THEN 0.5 ELSE 0 END) AS worked
                 FROM attendance_logs WHERE employee_id=? AND YEAR(attend_date)=? AND MONTH(attend_date)=?"
            );
            $attStmt->execute([$emp['id'], $year, $month]);
            $daysWorked = (float)($attStmt->fetch()['worked'] ?? $daysInMonth);

            $entry = calculate_payroll_entry($emp, $daysWorked, (float)$daysInMonth);

            $pdo->prepare(
                'INSERT INTO payroll_entries
                 (payroll_run_id,employee_id,days_in_month,days_worked,basic,hra,da,conveyance,special_allowance,other_allowance,
                  gross,pf_deduction,esi_deduction,pt_deduction,tds_deduction,advance_deduction,other_deduction,total_deductions,net_pay)
                 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
                 ON DUPLICATE KEY UPDATE
                 days_worked=VALUES(days_worked),basic=VALUES(basic),hra=VALUES(hra),da=VALUES(da),
                 conveyance=VALUES(conveyance),special_allowance=VALUES(special_allowance),other_allowance=VALUES(other_allowance),
                 gross=VALUES(gross),pf_deduction=VALUES(pf_deduction),esi_deduction=VALUES(esi_deduction),
                 pt_deduction=VALUES(pt_deduction),total_deductions=VALUES(total_deductions),net_pay=VALUES(net_pay),updated_at=NOW()'
            )->execute([
                $runId, $emp['id'], $daysInMonth, $daysWorked,
                $entry['basic'], $entry['hra'], $entry['da'], $entry['conv'],
                $entry['special'], $entry['other'], $entry['gross'],
                $entry['pf'], $entry['esi'], $entry['pt'],
                0, 0, 0,
                $entry['totalD'], $entry['net'],
            ]);
        }
        redirect_with_message('payroll.php?year='.$year.'&month='.$month, 'Payroll calculated for '.date('F Y', mktime(0,0,0,$month,1,$year)).'.');
    }

    // Update individual deductions
    if ($action === 'update_entry') {
        $entryId = (int)($_POST['entry_id'] ?? 0);
        $tds     = max(0, (float)($_POST['tds_deduction']     ?? 0));
        $adv     = max(0, (float)($_POST['advance_deduction'] ?? 0));
        $otherD  = max(0, (float)($_POST['other_deduction']   ?? 0));
        $notes   = sanitize_text($_POST['notes'] ?? '');

        if ($entryId) {
            $entry = $pdo->prepare('SELECT * FROM payroll_entries WHERE id=? LIMIT 1');
            $entry->execute([$entryId]);
            $entry = $entry->fetch();
            if ($entry) {
                $total = $entry['pf_deduction'] + $entry['esi_deduction'] + $entry['pt_deduction'] + $tds + $adv + $otherD;
                $net   = round($entry['gross'] - $total, 2);
                $pdo->prepare(
                    'UPDATE payroll_entries SET tds_deduction=?,advance_deduction=?,other_deduction=?,
                     total_deductions=?,net_pay=?,notes=?,updated_at=NOW() WHERE id=?'
                )->execute([$tds, $adv, $otherD, $total, $net, $notes ?: null, $entryId]);
            }
        }
        redirect_with_message('payroll.php?year='.$year.'&month='.$month, 'Entry updated.');
    }

    // Finalize
    if ($action === 'finalize') {
        $runId = (int)($_POST['run_id'] ?? 0);
        if ($runId) {
            $pdo->prepare('UPDATE payroll_runs SET status=\'finalized\', finalized_at=NOW() WHERE id=? AND status=\'draft\'')->execute([$runId]);
            redirect_with_message('payroll.php?year='.$year.'&month='.$month, 'Payroll finalized.');
        }
    }
}

// Load run
$run = $pdo->prepare('SELECT * FROM payroll_runs WHERE year=? AND month=? LIMIT 1');
$run->execute([$year, $month]);
$run = $run->fetch() ?: null;

$entries = [];
$totals  = ['gross'=>0,'pf'=>0,'esi'=>0,'pt'=>0,'tds'=>0,'adv'=>0,'other'=>0,'total_d'=>0,'net'=>0,'count'=>0];
if ($run) {
    $stmt = $pdo->prepare(
        'SELECT pe.*, e.first_name, e.last_name, e.department
         FROM payroll_entries pe JOIN employees e ON e.id = pe.employee_id
         WHERE pe.payroll_run_id = ?
         ORDER BY e.department, e.first_name'
    );
    $stmt->execute([$run['id']]);
    $entries = $stmt->fetchAll();
    foreach ($entries as $en) {
        $totals['gross']   += $en['gross'];
        $totals['pf']      += $en['pf_deduction'];
        $totals['esi']     += $en['esi_deduction'];
        $totals['pt']      += $en['pt_deduction'];
        $totals['tds']     += $en['tds_deduction'];
        $totals['adv']     += $en['advance_deduction'];
        $totals['other']   += $en['other_deduction'];
        $totals['total_d'] += $en['total_deductions'];
        $totals['net']     += $en['net_pay'];
        $totals['count']++;
    }
}

// months nav
$prevM = $month-1; $prevY=$year; if ($prevM<1){$prevM=12; $prevY--;}
$nextM = $month+1; $nextY=$year; if ($nextM>12){$nextM=1; $nextY++;}

function e(string $v): string { return htmlspecialchars($v, ENT_QUOTES, 'UTF-8'); }
function fmt(float $v): string { return '₹'.number_format($v, 2); }
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Payroll &mdash; <?php echo date('F Y', mktime(0,0,0,$month,1,$year)); ?></title>
    <link rel="stylesheet" href="../assets/css/style.css?v=<?php echo filemtime(__DIR__ . '/../assets/css/style.css'); ?>" />
    <link rel="stylesheet" href="../assets/css/polish.css?v=<?php echo filemtime(__DIR__ . '/../assets/css/polish.css'); ?>" />
    <style>
        .pay-table { width:100%; border-collapse:collapse; font-size:.8rem; white-space:nowrap; }
        .pay-table th, .pay-table td { padding:8px 10px; border-bottom:1px solid #f3f4f6; text-align:right; }
        .pay-table td:first-child, .pay-table th:first-child { text-align:left; }
        .pay-table th { background:#f9fafb; font-weight:700; color:#374151; border-bottom:1px solid #e5e7eb; }
        .pay-table tfoot td { font-weight:700; border-top:2px solid #e5e7eb; background:#f9fafb; }
        .status-badge { display:inline-block; padding:2px 10px; border-radius:999px; font-size:.75rem; font-weight:700; }
    </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">Payroll</h1>
                    <div class="breadcrumb-nav">
                        <span>Run and manage payroll</span>
                    </div>
                </div>
            </div>
            <div class="top-bar-right">
                <a href="dashboard.php" style="color: #cbd5e1; text-decoration: none; padding: 8px 12px; border-radius: 6px; transition: background 0.15s; font-size: 13px; font-weight: 600;">← Dashboard</a>
            </div>
        </div>
        <div class="dashboard-container" style="max-width: 100%;">
<div class="container">
    <div class="header">
        <div class="brand">
            <div class="brand-title">
                <h1 style="margin:0;">Payroll</h1>
                <p class="helper" style="margin:2px 0 0;"><?php echo date('F Y', mktime(0,0,0,$month,1,$year)); ?></p>
            </div>
        </div>
        <div class="actions">
            <a class="badge" href="salary.php">Salary Structures</a>
            <a class="badge" href="dashboard.php">Dashboard</a>
            <a class="badge" href="logout.php">Logout</a>
        </div>
    </div>

    <?php if ($flash): ?>
        <div class="alert <?php echo $flash['type'] === 'error' ? 'alert-error' : 'alert-success'; ?>">
            <?php echo e($flash['message']); ?>
        </div>
    <?php endif; ?>

    <!-- Nav + controls -->
    <div class="card" style="margin-bottom:16px; padding:12px 16px;">
        <div style="display:flex; gap:12px; align-items:center; flex-wrap:wrap;">
            <a class="badge" href="payroll.php?year=<?php echo $prevY; ?>&month=<?php echo $prevM; ?>">&larr; <?php echo date('M Y', mktime(0,0,0,$prevM,1,$prevY)); ?></a>
            <strong><?php echo date('F Y', mktime(0,0,0,$month,1,$year)); ?></strong>
            <a class="badge" href="payroll.php?year=<?php echo $nextY; ?>&month=<?php echo $nextM; ?>"><?php echo date('M Y', mktime(0,0,0,$nextM,1,$nextY)); ?> &rarr;</a>
            &nbsp;|&nbsp;
            <?php if (!$run || $run['status'] === 'draft'): ?>
            <form method="POST" style="display:inline;">
                <input type="hidden" name="csrf_token" value="<?php echo e(csrf_token()); ?>" />
                <input type="hidden" name="action" value="generate" />
                <button type="submit" class="btn btn-primary" style="padding:4px 14px; font-size:.85rem;">
                    <?php echo $run ? '↻ Recalculate' : 'Generate Payroll'; ?>
                </button>
            </form>
            <?php endif; ?>
            <?php if ($run): ?>
                <span class="status-badge" style="<?php echo $run['status']==='finalized' ? 'background:#d1fae5;color:#065f46;' : 'background:#fef3c7;color:#92400e;'; ?>">
                    <?php echo ucfirst($run['status']); ?>
                </span>
                <?php if ($run['status'] === 'draft' && !empty($entries)): ?>
                <form method="POST" style="display:inline;" onsubmit="return confirm('Finalize payroll? This cannot be undone.');">
                    <input type="hidden" name="csrf_token" value="<?php echo e(csrf_token()); ?>" />
                    <input type="hidden" name="action" value="finalize" />
                    <input type="hidden" name="run_id" value="<?php echo (int)$run['id']; ?>" />
                    <button type="submit" style="background:#065f46; color:#fff; border:none; padding:4px 14px; border-radius:6px; cursor:pointer; font-size:.85rem;">Finalize</button>
                </form>
                <?php endif; ?>
            <?php endif; ?>
        </div>
    </div>

    <?php if (empty($entries)): ?>
        <div class="card" style="text-align:center; padding:40px; color:#6b7280;">
            <?php echo $run ? 'No entries in this payroll run.' : 'No payroll generated yet. Click "Generate Payroll" to begin.'; ?>
        </div>
    <?php else: ?>
    <div class="card" style="overflow-x:auto;">
        <table class="pay-table">
            <thead>
                <tr>
                    <th>Employee</th>
                    <th>Dept</th>
                    <th>Days</th>
                    <th>Basic</th>
                    <th>HRA</th>
                    <th>DA+Conv</th>
                    <th>Special</th>
                    <th>Gross</th>
                    <th>PF</th>
                    <th>ESI</th>
                    <th>PT</th>
                    <th>TDS</th>
                    <th>Adv</th>
                    <th>Total Ded.</th>
                    <th>Net Pay</th>
                    <th></th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($entries as $en): ?>
                <tr>
                    <td><?php echo e($en['first_name'].' '.$en['last_name']); ?></td>
                    <td style="color:#6b7280;"><?php echo e($en['department']); ?></td>
                    <td><?php echo $en['days_worked']; ?>/<?php echo $en['days_in_month']; ?></td>
                    <td><?php echo fmt($en['basic']); ?></td>
                    <td><?php echo fmt($en['hra']); ?></td>
                    <td><?php echo fmt($en['da'] + $en['conveyance']); ?></td>
                    <td><?php echo fmt($en['special_allowance']); ?></td>
                    <td style="font-weight:700;"><?php echo fmt($en['gross']); ?></td>
                    <td style="color:#6b7280;"><?php echo fmt($en['pf_deduction']); ?></td>
                    <td style="color:#6b7280;"><?php echo fmt($en['esi_deduction']); ?></td>
                    <td style="color:#6b7280;"><?php echo fmt($en['pt_deduction']); ?></td>
                    <td style="color:#6b7280;"><?php echo fmt($en['tds_deduction']); ?></td>
                    <td style="color:#6b7280;"><?php echo fmt($en['advance_deduction']); ?></td>
                    <td style="color:#ef4444;"><?php echo fmt($en['total_deductions']); ?></td>
                    <td style="font-weight:800; color:#16a34a;"><?php echo fmt($en['net_pay']); ?></td>
                    <td>
                        <?php if ($run['status'] === 'draft'): ?>
                        <button type="button" onclick="openEditModal(<?php echo htmlspecialchars(json_encode($en), ENT_QUOTES); ?>)"
                            style="background:none; border:none; color:#3b82f6; cursor:pointer; font-size:.8rem;">Edit</button>
                        <?php else: ?>
                        <a href="payslip.php?emp=<?php echo (int)$en['employee_id']; ?>&run=<?php echo (int)$run['id']; ?>"
                           style="font-size:.8rem;" target="_blank">Payslip</a>
                        <?php endif; ?>
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
            <tfoot>
                <tr>
                    <td colspan="7">Total (<?php echo $totals['count']; ?> employees)</td>
                    <td><?php echo fmt($totals['gross']); ?></td>
                    <td><?php echo fmt($totals['pf']); ?></td>
                    <td><?php echo fmt($totals['esi']); ?></td>
                    <td><?php echo fmt($totals['pt']); ?></td>
                    <td><?php echo fmt($totals['tds']); ?></td>
                    <td><?php echo fmt($totals['adv']); ?></td>
                    <td><?php echo fmt($totals['total_d']); ?></td>
                    <td><?php echo fmt($totals['net']); ?></td>
                    <td></td>
                </tr>
            </tfoot>
        </table>
    </div>
    <?php endif; ?>
</div>

<!-- Edit deductions modal -->
<div id="edit-modal" style="display:none; position:fixed; inset:0; background:rgba(0,0,0,.45); z-index:1000; align-items:center; justify-content:center;">
    <div style="background:#fff; border-radius:12px; padding:28px; width:380px; max-width:95vw;">
        <h3 id="edit-modal-title" style="margin:0 0 16px; font-size:1rem;"></h3>
        <form method="POST" action="payroll.php?year=<?php echo $year; ?>&month=<?php echo $month; ?>">
            <input type="hidden" name="csrf_token" value="<?php echo e(csrf_token()); ?>" />
            <input type="hidden" name="action" value="update_entry" />
            <input type="hidden" name="entry_id" id="edit-entry-id" />
            <div style="display:grid; grid-template-columns:1fr 1fr; gap:12px;">
                <div class="form-group">
                    <label>TDS (₹)</label>
                    <input type="number" name="tds_deduction" id="edit-tds" class="form-control" min="0" step="0.01" />
                </div>
                <div class="form-group">
                    <label>Advance (₹)</label>
                    <input type="number" name="advance_deduction" id="edit-adv" class="form-control" min="0" step="0.01" />
                </div>
                <div class="form-group">
                    <label>Other Deduction (₹)</label>
                    <input type="number" name="other_deduction" id="edit-other" class="form-control" min="0" step="0.01" />
                </div>
                <div class="form-group">
                    <label>Notes</label>
                    <input type="text" name="notes" id="edit-notes" class="form-control" />
                </div>
            </div>
            <div style="display:flex; gap:10px; margin-top:4px;">
                <button type="submit" class="btn btn-primary" style="flex:1;">Save</button>
                <button type="button" onclick="document.getElementById('edit-modal').style.display='none'" style="flex:1; background:#f3f4f6; border:1px solid #e5e7eb; border-radius:6px; cursor:pointer;">Cancel</button>
            </div>
        </form>
    </div>
</div>
        </div>
        </div>
    </div>
</div>

<div class="sidebar-overlay" id="sidebarOverlay"></div>

<script>
    const sidebar = document.getElementById('sidebar');
    const sidebarToggle = document.getElementById('sidebarToggle');
    const sidebarOverlay = document.getElementById('sidebarOverlay');

    sidebarToggle?.addEventListener('click', () => {
        sidebar.classList.toggle('open');
        sidebarOverlay.classList.toggle('open');
    });

    sidebarOverlay?.addEventListener('click', () => {
        sidebar.classList.remove('open');
        sidebarOverlay.classList.remove('open');
    });
</script>

<script>
function openEditModal(en) {
    document.getElementById('edit-modal-title').textContent = en.first_name + ' ' + en.last_name;
    document.getElementById('edit-entry-id').value = en.id;
    document.getElementById('edit-tds').value   = en.tds_deduction;
    document.getElementById('edit-adv').value   = en.advance_deduction;
    document.getElementById('edit-other').value = en.other_deduction;
    document.getElementById('edit-notes').value = en.notes || '';
    document.getElementById('edit-modal').style.display = 'flex';
}
document.getElementById('edit-modal').addEventListener('click', function(e) {
    if (e.target === this) this.style.display = 'none';
});
</script>
</body>
</html>

← Back to Directory Edit File 🔒 Chmod