View Code - 20260726

new_pasaria_salary.php | 20260726_140739.php advance added

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

require_once __DIR__ . '/modules/auth/page_acl.php';
$ctx = page_require_access('pasaria_salary');

$pdo        = $ctx['pdo'];
$company_id = (int)$ctx['company_id'];

function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }

/* ================= DOMPDF (SAFE) ================= */
$PDF_AVAILABLE = false;
$dompdf_autoloaders = [
    __DIR__ . '/lib/dompdf/autoload.inc.php',
    __DIR__ . '/vendor/autoload.php',
];
foreach ($dompdf_autoloaders as $autoload) {
    if (is_file($autoload)) {
        require_once $autoload;
        if (class_exists(\Dompdf\Dompdf::class)) {
            $PDF_AVAILABLE = true;
            break;
        }
    }
}

/* ================= FILTERS ================= */
$year   = isset($_GET['year'])  ? (int)$_GET['year']  : (int)date('Y');
$month  = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('m');
$period = isset($_GET['period']) ? $_GET['period'] : '1-15';

if ($period === '1-15') {
    $start_date = sprintf('%04d-%02d-01 00:00:00', $year, $month);
    $end_date   = sprintf('%04d-%02d-15 23:59:59', $year, $month);
} else {
    $start_date = sprintf('%04d-%02d-16 00:00:00', $year, $month);
    $end_date   = date('Y-m-t 23:59:59', strtotime(sprintf('%04d-%02d-01', $year, $month)));
}

/* ================= RATE MAP ================= */
$rateMap = [];
$qr = $pdo->prepare("
    SELECT pasaria_type, MAX(rate) as rate 
    FROM pasaria_rates 
    WHERE company_id = ? 
    GROUP BY pasaria_type
");
$qr->execute([$company_id]);

foreach($qr->fetchAll(PDO::FETCH_ASSOC) as $r) {
    $rateMap[trim($r['pasaria_type'])] = (float)$r['rate'];
}

/* ================= EXTRA, DEDUCTION & ADVANCE FETCH ================= */
$extraMap = [];
$qExtra = $pdo->prepare("SELECT employee_id, SUM(amount) as total FROM employee_extra WHERE company_id = ? AND entry_date BETWEEN ? AND ? GROUP BY employee_id");
$qExtra->execute([$company_id, substr($start_date, 0, 10), substr($end_date, 0, 10)]);
foreach($qExtra->fetchAll(PDO::FETCH_ASSOC) as $ex) {
    $extraMap[$ex['employee_id']] = (float)$ex['total'];
}

$deductMap = [];
$qDeduct = $pdo->prepare("SELECT employee_id, SUM(amount) as total FROM employee_deduction WHERE company_id = ? AND entry_date BETWEEN ? AND ? GROUP BY employee_id");
$qDeduct->execute([$company_id, substr($start_date, 0, 10), substr($end_date, 0, 10)]);
foreach($qDeduct->fetchAll(PDO::FETCH_ASSOC) as $de) {
    $deductMap[$de['employee_id']] = (float)$de['total'];
}

$advanceMap = [];
$qAdvance = $pdo->prepare("SELECT employee_id, SUM(amount) as total FROM employee_advance WHERE company_id = ? AND date BETWEEN ? AND ? GROUP BY employee_id");
$qAdvance->execute([$company_id, substr($start_date, 0, 10), substr($end_date, 0, 10)]);
foreach($qAdvance->fetchAll(PDO::FETCH_ASSOC) as $ad) {
    $advanceMap[$ad['employee_id']] = (float)$ad['total'];
}

/* ================= MAIN QUERY ================= */
$sql = "
SELECT 
    p.*,
    cem.name as emp_mapped_name,
    cem.beneficiary_name, 
    cem.account_number, 
    cem.ifsc
FROM pasaria_entry_new p
LEFT JOIN company_employee_master cem 
       ON p.employee_id = cem.id 
      AND p.company_id = cem.company_id
WHERE p.company_id = :cid 
  AND p.pasaria_date BETWEEN :s AND :e
ORDER BY p.pasaria_date ASC
";

$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':cid' => $company_id,
    ':s'   => $start_date,
    ':e'   => $end_date
]);

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* ================= SUMMARY ================= */
$summary = [];
$details = [];
$total_net = 0;

foreach ($rows as $r) {

$emp_id = $r['employee_id'] ?: 0;
    $type   = trim($r['pasaria_type']);
    $rate   = $rateMap[$type] ?? 0;

if (!isset($summary[$emp_id])) {
        $summary[$emp_id] = [
            'employee_id'      => $r['employee_id'],
            'name'             => $r['emp_mapped_name'] ?: 'Unknown',
            'beneficiary_name' => $r['beneficiary_name'] ?: '',
            'account_number'   => $r['account_number'] ?: '',
            'ifsc'             => $r['ifsc'] ?: '',
            'total_jobs'       => 0,
            'gross_pay'        => 0,
            'extra'            => $extraMap[$emp_id] ?? 0,
            'deduction'        => $deductMap[$emp_id] ?? 0,
            'advance'          => $advanceMap[$emp_id] ?? 0,
            'net_pay'          => 0
        ];
        $details[$emp_id] = [];
    }

$summary[$emp_id]['total_jobs'] += 1;
    $summary[$emp_id]['gross_pay']  += $rate;

$details[$emp_id][] = [
        'date'    => $r['pasaria_date'],
        'type'    => $type,
        'rate'    => $rate,
        'machine' => $r['machine_no'] ?? ''
    ];
}

// Net Pay calculation: gross + extra - deduction - advance
foreach ($summary as $emp_id => &$s) {
    $s['net_pay'] = $s['gross_pay'] + $s['extra'] - $s['deduction'] - $s['advance'];
    $total_net += $s['net_pay'];
}
unset($s);

/* ================= EXPORT DATA ================= */
$salary_export_data = [];
foreach ($summary as $s) {
    $salary_export_data[] = [
        'employee_id'      => $s['employee_id'],
        'employee_name'    => $s['name'],
        'beneficiary_name' => $s['beneficiary_name'],
        'account_number'   => $s['account_number'],
        'ifsc'             => $s['ifsc'],
        'amount'           => round($s['net_pay'], 2),
        'net_pay'          => round($s['net_pay'], 2),
        'jobs'             => $s['total_jobs'],
        'source'           => 'pasaria'
    ];
}
$salary_json = json_encode($salary_export_data);

/* ================= PDF EXPORT ================= */
if (($_GET['export'] ?? '') === 'pdf') {
    if (!$PDF_AVAILABLE) {
        http_response_code(503);
        header('Content-Type: text/html; charset=UTF-8');
        echo '<h3>PDF export unavailable</h3><p>Dompdf is not installed on this server.</p>';
        exit;
    }

ob_start();
    ?>
    <!DOCTYPE html>
    <html>
    <head>
    <meta charset="UTF-8">
    <title>Pasaria Salary Report PDF</title>
    <style>
        @page { margin: 16px 16px; }
        body { margin: 0; font-family: DejaVu Sans, sans-serif; font-size: 9px; color: #111; }
        h1 { margin: 0 0 4px; font-size: 15px; }
        .subhead { margin: 0 0 12px; font-size: 10px; color: #444; }
        .summary-cards { width: 100%; border-collapse: collapse; margin-bottom: 14px; }
        .summary-cards td { width: 50%; border: 1px solid #d9d9d9; padding: 8px; }
        .summary-label { font-size: 8px; text-transform: uppercase; color: #666; }
        .summary-value { font-size: 14px; font-weight: 700; margin-top: 2px; }
        table.report { width: 100%; border-collapse: collapse; table-layout: fixed; }
        table.report th, table.report td { border: 1px solid #dddddd; padding: 6px 5px; vertical-align: middle; }
        table.report th { background: #f3f4f6; font-size: 8px; }
        table.report td { font-size: 9px; }
        .text-center { text-align: center; }
        .text-end { text-align: right; }
        .text-muted { color: #666; }
        .emp-name { font-weight: 700; }
        .sub-line { font-size: 8px; color: #666; margin-top: 2px; }
        .details-wrap { padding: 8px; background: #fafafa; }
        table.details { width: 100%; border-collapse: collapse; table-layout: fixed; }
        table.details th, table.details td { border: 1px solid #e1e1e1; padding: 4px; }
        table.details th { background: #f8f9fa; font-size: 7.8px; }
        table.details td { font-size: 8px; }
        .row-extra td { background: #eaf7ee; font-weight: 700; }
        .row-deduct td { background: #fdecec; font-weight: 700; }
        .row-advance td { background: #fff3bf; font-weight: 700; }
        .row-net td { background: #212529; color: #fff; font-weight: 700; }
        .employee-row td { page-break-inside: avoid; }
    </style>
    </head>
    <body>
        <h1>Pasaria Salary Report</h1>
        <div class="subhead">
            <?= h(date('F Y', mktime(0,0,0,$month,1,$year))) ?> |
            <?= h($period=='1-15' ? '1-15' : '16-End') ?>
        </div>

<table class="summary-cards">
            <tr>
                <td>
                    <div class="summary-label">Total Employees</div>
                    <div class="summary-value"><?= count($summary) ?></div>
                </td>
                <td>
                    <div class="summary-label">Total Amount</div>
                    <div class="summary-value">Rs. <?= number_format($total_net, 2) ?></div>
                </td>
            </tr>
        </table>

<table class="report">
            <thead>
                <tr>
                    <th>Employee</th>
                    <th class="text-center">Jobs</th>
                    <th class="text-end">Net Pay</th>
                </tr>
            </thead>
            <tbody>
                <?php if (empty($summary)): ?>
                    <tr>
                        <td colspan="3" class="text-center text-muted">No records found.</td>
                    </tr>
                <?php else: ?>
                    <?php foreach ($summary as $emp_id => $s): ?>
                        <tr class="employee-row">
                            <td>
                                <div class="emp-name"><?= h($s['name']) ?></div>
                                <div class="sub-line">EMP ID: <?= h($s['employee_id']) ?></div>
                            </td>
                            <td class="text-center"><?= (int)$s['total_jobs'] ?></td>
                            <td class="text-end"><strong>Rs. <?= number_format($s['net_pay'], 2) ?></strong></td>
                        </tr>
                        <tr>
                            <td colspan="3" class="details-wrap">
                                <table class="details">
                                    <thead>
                                        <tr>
                                            <th>Date</th>
                                            <th>Machine</th>
                                            <th>Pasaria Type</th>
                                            <th class="text-end">Rate</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php foreach ($details[$emp_id] as $d): ?>
                                            <tr>
                                                <td><?= h(date('d-m-Y', strtotime($d['date']))) ?></td>
                                                <td><?= h($d['machine']) ?></td>
                                                <td><?= h($d['type']) ?></td>
                                                <td class="text-end">Rs. <?= number_format($d['rate'], 2) ?></td>
                                            </tr>
                                        <?php endforeach; ?>
                                        <?php if($s['extra'] > 0): ?>
                                            <tr class="row-extra">
                                                <td colspan="3" class="text-end">Extra Amount (+)</td>
                                                <td class="text-end">Rs. <?= number_format($s['extra'], 2) ?></td>
                                            </tr>
                                        <?php endif; ?>
                                        <?php if($s['deduction'] > 0): ?>
                                            <tr class="row-deduct">
                                                <td colspan="3" class="text-end">Deduction (-)</td>
                                                <td class="text-end">Rs. <?= number_format($s['deduction'], 2) ?></td>
                                            </tr>
                                        <?php endif; ?>
                                        <?php if($s['advance'] > 0): ?>
                                            <tr class="row-advance">
                                                <td colspan="3" class="text-end">Advance (-)</td>
                                                <td class="text-end">Rs. <?= number_format($s['advance'], 2) ?></td>
                                            </tr>
                                        <?php endif; ?>
                                        <tr class="row-net">
                                            <td colspan="3" class="text-end">Final Net Pay</td>
                                            <td class="text-end">Rs. <?= number_format($s['net_pay'], 2) ?></td>
                                        </tr>
                                    </tbody>
                                </table>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>
        </table>
    </body>
    </html>
    <?php
    $pdfHtml = ob_get_clean();

$dompdf = new \Dompdf\Dompdf([
        'isRemoteEnabled' => false,
        'defaultFont' => 'DejaVu Sans',
    ]);
    $dompdf->loadHtml($pdfHtml, 'UTF-8');
    $dompdf->setPaper('A4', 'portrait');
    $dompdf->render();
    $filename = 'pasaria_salary_' . $year . '_' . str_pad((string)$month, 2, '0', STR_PAD_LEFT) . '_' . ($period === '1-15' ? 'h1' : 'h2') . '.pdf';
    $dompdf->stream($filename, ['Attachment' => true]);
    exit;
}

$page_title = 'Pasaria Salary Report';
require_once __DIR__ . '/partials/header.php';
?>

<div class="d-flex justify-content-between align-items-center mb-4">
    <div>
        <h4 class="fw-bold mb-1">Pasaria Salary Report</h4>
        <small class="text-muted">
            <?= date('F Y', mktime(0,0,0,$month,1,$year)) ?> | 
            <?= $period=='1-15' ? '1–15' : '16–End' ?>
        </small>
    </div>

<?php $pdf_qs = http_build_query(['year' => $year, 'month' => $month, 'period' => $period, 'export' => 'pdf']); ?>

<form method="POST" action="/erp/employee_salary_export.php" target="_blank">
            <input type="hidden" name="salary_rows" value='<?= h($salary_json) ?>'>
            <button class="btn btn-success" <?= empty($summary)?'disabled':'' ?>>
                Export Payments
            </button>
        </form>

<a href="?<?= h($pdf_qs) ?>" class="btn btn-danger <?= empty($summary)?'disabled':'' ?>">
            Export PDF
        </a>

<button onclick="window.print()" class="btn btn-outline-dark">
            Print
        </button>
    </div>
</div>

<div class="col-auto">
        <input type="number" name="year" value="<?= $year ?>" class="form-control" style="width:100px;">
    </div>

<div class="col-auto">
        <button class="btn btn-primary">Apply</button>
    </div>
</form>

<div class="row mb-3">
    <div class="col-md-3">
        <div class="card text-center shadow-sm">
            <div class="card-body">
                <small class="text-muted">Total Employees</small>
                <h5 class="fw-bold"><?= count($summary) ?></h5>
            </div>
        </div>
    </div>

<div class="col-md-3">
        <div class="card text-center shadow-sm">
            <div class="card-body">
                <small class="text-muted">Total Amount</small>
                <h5 class="fw-bold text-primary">₹ <?= number_format($total_net,2) ?></h5>
            </div>
        </div>
    </div>
</div>

<thead class="table-light small text-uppercase">
<tr>
    <th>Employee</th>
    <th class="text-center">Jobs</th>
    <th class="text-end">Net Pay</th>
</tr>
</thead>

<tbody>

<?php if(empty($summary)): ?>
<tr>
    <td colspan="3" class="text-center p-4 text-muted">
        No records found.
    </td>
</tr>
<?php else: ?>

<?php foreach($summary as $emp_id => $s): ?>

<tr data-bs-toggle="collapse" 
    data-bs-target="#detail_<?= $emp_id ?>" 
    style="cursor:pointer;">
    <td>
        <strong><?= h($s['name']) ?></strong><br>
        <small class="text-muted">EMP ID: <?= h($s['employee_id']) ?></small>
    </td>
    <td class="text-center">
        <span class="badge bg-secondary"><?= $s['total_jobs'] ?></span>
    </td>
    <td class="text-end fw-bold">
        ₹ <?= number_format($s['net_pay'],2) ?>
    </td>
</tr>

<tr class="collapse bg-white" id="detail_<?= $emp_id ?>">
<td colspan="3">
<div class="p-3">
<table class="table table-sm table-bordered">
<thead class="table-light small">
<tr>
    <th>Date</th>
    <th>Machine</th>
    <th>Pasaria Type</th>
    <th class="text-end">Rate</th>
</tr>
</thead>
<tbody>
<?php foreach($details[$emp_id] as $d): ?>
<tr>
    <td><?= date('d-m-Y', strtotime($d['date'])) ?></td>
    <td><?= h($d['machine']) ?></td>
    <td><?= h($d['type']) ?></td>
    <td class="text-end">₹ <?= number_format($d['rate'],2) ?></td>
</tr>
<?php endforeach; ?>

<?php if($s['extra'] > 0): ?>
<tr class="table-success fw-bold">
    <td colspan="3" class="text-end">Extra Amount (+)</td>
    <td class="text-end">₹ <?= number_format($s['extra'],2) ?></td>
</tr>
<?php endif; ?>

<?php if($s['deduction'] > 0): ?>
<tr class="table-danger fw-bold">
    <td colspan="3" class="text-end">Deduction (-)</td>
    <td class="text-end">₹ <?= number_format($s['deduction'],2) ?></td>
</tr>
<?php endif; ?>

<?php if($s['advance'] > 0): ?>
<tr class="table-warning fw-bold">
    <td colspan="3" class="text-end">Advance (-)</td>
    <td class="text-end">₹ <?= number_format($s['advance'],2) ?></td>
</tr>
<?php endif; ?>

<tr class="table-dark text-white fw-bold">
    <td colspan="3" class="text-end">Final Net Pay</td>
    <td class="text-end">₹ <?= number_format($s['net_pay'],2) ?></td>
</tr>

</tbody>
</table>
</div>
</td>
</tr>

<?php endforeach; ?>
<?php endif; ?>

</tbody>
</table>
</div>
</div>

</div>

<?php require_once __DIR__ . '/partials/footer.php'; ?>