View Code - 20260721

new_pissing_salary_report.php | 20260721_154033.php Initial Bulk Import

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

$pdo        = $ctx['pdo'];
$company_id = (int)$ctx['company_id'];
$user_id    = (int)$ctx['user']['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;
        }
    }
}

function table_exists(PDO $pdo, string $table): bool {
    $st = $pdo->prepare("SHOW TABLES LIKE :tbl");
    $st->execute([':tbl' => $table]);
    return (bool)$st->fetchColumn();
}

$hasBeamLoad2 = table_exists($pdo, 'beam_load_data_2');

$month  = $_REQUEST['month']  ?? date('Y-m');
$period = $_REQUEST['period'] ?? '1-15';

if ($period === '1-15') {
    $start_date = $month . '-01';
    $end_date   = $month . '-15';
} else {
    $start_date = $month . '-16';
    $end_date   = date('Y-m-t', strtotime($month . '-01'));
}

$rows = [];
$total_qty = 0;
$total_amount = 0;

$extra = 0;
$deduction = 0;
$final_total = 0;
$employee_breakdown = [];

if (isset($_GET['search']) || isset($_POST['save_salary']) || (($_GET['export'] ?? '') === 'pdf')) {

$map = [];

/* ================= MAIN DATA ================= */
    $sql1 = "
        SELECT q.name AS quality_name, COUNT(*) AS qty, r.rate,
        (COUNT(*) * IFNULL(r.rate,0)) AS amount
        FROM beam_load_data b
        JOIN beam_qualities q ON q.id=b.quality_id AND q.company_id=b.company_id
        LEFT JOIN pissing_rate r ON r.quality_id=b.quality_id AND r.company_id=b.company_id
        WHERE b.company_id=:cid AND b.load_type='pissing'
        AND b.event_type='loaded'
        AND b.load_date BETWEEN :sd AND :ed
        GROUP BY b.quality_id,q.name,r.rate
    ";

$st = $pdo->prepare($sql1);
    $st->execute([':cid'=>$company_id,':sd'=>$start_date,':ed'=>$end_date]);

foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r) {
        $k=$r['quality_name'];
        $map[$k]['quality_name']=$k;
        $map[$k]['rate']=(float)$r['rate'];
        $map[$k]['qty']=($map[$k]['qty']??0)+(int)$r['qty'];
        $map[$k]['amount']=($map[$k]['amount']??0)+(float)$r['amount'];
    }

if ($hasBeamLoad2) {
        $sql2 = "
            SELECT pq.quality AS quality_name, COUNT(*) AS qty, pq.rate,
            (COUNT(*) * IFNULL(pq.rate,0)) AS amount
            FROM beam_load_data_2 b2
            JOIN pissing_quality pq
              ON pq.id = b2.quality_id
             AND pq.company_id = b2.company_id
             AND pq.is_active = 1
            WHERE b2.company_id = :cid
              AND b2.load_type = 'pissing'
              AND b2.event_type = 'loaded'
              AND b2.load_date BETWEEN :sd AND :ed
            GROUP BY pq.id, pq.quality, pq.rate
        ";

$st2 = $pdo->prepare($sql2);
        $st2->execute([':cid'=>$company_id,':sd'=>$start_date,':ed'=>$end_date]);

foreach ($st2->fetchAll(PDO::FETCH_ASSOC) as $r) {
            $k=$r['quality_name'];
            $map[$k]['quality_name']=$k;
            $map[$k]['rate']=(float)$r['rate'];
            $map[$k]['qty']=($map[$k]['qty']??0)+(int)$r['qty'];
            $map[$k]['amount']=($map[$k]['amount']??0)+(float)$r['amount'];
        }
    }

foreach ($map as $r) {
        $rows[] = $r;
        $total_qty += $r['qty'];
        $total_amount += $r['amount'];
    }

/* ================= EMPLOYEE FROM BEAM ================= */
    $emps = [];

$st = $pdo->prepare("
        SELECT DISTINCT employee_id
        FROM beam_load_data
        WHERE company_id = :cid
        AND load_type='pissing'
        AND event_type='loaded'
        AND load_date BETWEEN :sd AND :ed
    ");
    $st->execute([':cid'=>$company_id,':sd'=>$start_date,':ed'=>$end_date]);
    $emps = array_merge($emps, $st->fetchAll(PDO::FETCH_COLUMN));

if ($hasBeamLoad2) {
        $st = $pdo->prepare("
            SELECT DISTINCT employee_id
            FROM beam_load_data_2
            WHERE company_id = :cid
            AND load_type='pissing'
            AND event_type='loaded'
            AND load_date BETWEEN :sd AND :ed
        ");
        $st->execute([':cid'=>$company_id,':sd'=>$start_date,':ed'=>$end_date]);
        $emps = array_merge($emps, $st->fetchAll(PDO::FETCH_COLUMN));
    }

$emps = array_values(array_unique(array_filter($emps)));

foreach ($emps as $emp_id) {

// EXTRA
        $st = $pdo->prepare("
            SELECT IFNULL(SUM(amount),0)
            FROM employee_extra
            WHERE company_id = :cid
            AND employee_id = :emp
            AND entry_date BETWEEN :sd AND :ed
        ");
        $st->execute([
            ':cid'=>$company_id,
            ':emp'=>$emp_id,
            ':sd'=>$start_date,
            ':ed'=>$end_date
        ]);
        $ex = (float)$st->fetchColumn();

// DEDUCTION
        $st = $pdo->prepare("
            SELECT IFNULL(SUM(amount),0)
            FROM employee_deduction
            WHERE company_id = :cid
            AND employee_id = :emp
            AND entry_date BETWEEN :sd AND :ed
        ");
        $st->execute([
            ':cid'=>$company_id,
            ':emp'=>$emp_id,
            ':sd'=>$start_date,
            ':ed'=>$end_date
        ]);
        $ded = (float)$st->fetchColumn();

$employee_breakdown[] = [
            'emp_id'=>$emp_id,
            'extra'=>$ex,
            'deduction'=>$ded
        ];

$extra += $ex;
        $deduction += $ded;
    }

$final_total = $total_amount + $extra - $deduction;
}

/* ================= 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>Pissing Salary Report PDF</title>
    <style>
        @page { margin: 16px 16px; }
        body { margin: 0; font-family: DejaVu Sans, sans-serif; font-size: 10px; color: #111; }
        h1 { margin: 0 0 4px; font-size: 16px; }
        .subhead { margin: 0 0 12px; font-size: 11px; color: #444; }
        table.report, table.details { width: 100%; border-collapse: collapse; table-layout: fixed; }
        table.report th, table.report td, table.details th, table.details td { border: 1px solid #d9d9d9; padding: 6px; }
        table.report th, table.details th { background: #f3f4f6; font-size: 9px; }
        table.report td, table.details td { font-size: 10px; }
        .text-end { text-align: right; }
        .summary-label { font-weight: 700; }
        .section-title { margin: 14px 0 8px; font-size: 12px; font-weight: 700; }
        .final-total td { font-weight: 700; background: #eef3ff; }
    </style>
    </head>
    <body>
        <h1>Pissing Salary Report</h1>
        <div class="subhead">
            <?= h(date('F Y', strtotime($month . '-01'))) ?> |
            <?= h($period === '1-15' ? '1-15' : '16-31') ?>
        </div>

<table class="report">
            <thead>
                <tr>
                    <th>Quality</th>
                    <th class="text-end">Rate</th>
                    <th class="text-end">Qty</th>
                    <th class="text-end">Amount</th>
                </tr>
            </thead>
            <tbody>
                <?php if (empty($rows)): ?>
                    <tr>
                        <td colspan="4">No records found.</td>
                    </tr>
                <?php else: ?>
                    <?php foreach ($rows as $r): ?>
                        <tr>
                            <td><?= h($r['quality_name']) ?></td>
                            <td class="text-end"><?= number_format((float)($r['rate'] ?? 0), 2) ?></td>
                            <td class="text-end"><?= (int)$r['qty'] ?></td>
                            <td class="text-end"><?= number_format($r['amount'], 2) ?></td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>
            <tfoot>
                <tr>
                    <td class="summary-label">Total</td>
                    <td class="text-end"><?= (int)$total_qty ?></td>
                    <td class="text-end"><?= number_format($total_amount, 2) ?></td>
                </tr>
                <tr>
                    <td class="summary-label">Extra (+)</td>
                    <td></td>
                    <td class="text-end"><?= number_format($extra, 2) ?></td>
                </tr>
                <tr>
                    <td class="summary-label">Deduction (-)</td>
                    <td></td>
                    <td class="text-end"><?= number_format($deduction, 2) ?></td>
                </tr>
                <tr class="final-total">
                    <td class="summary-label">Final Total</td>
                    <td></td>
                    <td class="text-end"><?= number_format($final_total, 2) ?></td>
                </tr>
            </tfoot>
        </table>

<?php if (count($employee_breakdown) > 1): ?>
            <div class="section-title">Employee Breakdown</div>
            <table class="details">
                <thead>
                    <tr>
                        <th>Emp ID</th>
                        <th class="text-end">Extra</th>
                        <th class="text-end">Deduction</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($employee_breakdown as $e): ?>
                        <tr>
                            <td><?= h($e['emp_id']) ?></td>
                            <td class="text-end"><?= number_format($e['extra'], 2) ?></td>
                            <td class="text-end"><?= number_format($e['deduction'], 2) ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        <?php endif; ?>
    </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 = 'pissing_salary_' . str_replace('-', '_', $month) . '_' . ($period === '1-15' ? 'h1' : 'h2') . '.pdf';
    $dompdf->stream($filename, ['Attachment' => true]);
    exit;
}
?>

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

<form method="get" class="row mb-3">
<input type="month" name="month" value="<?=h($month)?>" class="form-control col">
<select name="period" class="form-control col">
<option value="1-15" <?=$period=='1-15'?'selected':''?>>1-15</option>
<option value="16-31" <?=$period=='16-31'?'selected':''?>>16-31</option>
</select>
<button class="btn btn-primary col" name="search">Search</button>
<?php if($rows): ?>
<a href="?<?= h(http_build_query(['month' => $month, 'period' => $period, 'export' => 'pdf'])) ?>" class="btn btn-danger col">Export PDF</a>
<?php endif; ?>
</form>

<?php if($rows): ?>

<table class="table table-bordered">
<thead>
<tr>
<th>Quality</th>
<th class="text-end">Rate</th>
<th class="text-end">Qty</th>
<th class="text-end">Amount</th>
</tr>
</thead>

<tfoot>
<tr>
<td><b>Total</b></td>
<td></td>
<td class="text-end"><?=$total_qty?></td>
<td class="text-end"><?=number_format($total_amount,2)?></td>
</tr>

<tr>
<td><b>Extra (+)</b></td>
<td></td>
<td></td>
<td class="text-end"><?=number_format($extra,2)?></td>
</tr>

<tr>
<td><b>Deduction (-)</b></td>
<td></td>
<td></td>
<td class="text-end"><?=number_format($deduction,2)?></td>
</tr>

<tr>
<td><b>Final Total</b></td>
<td></td>
<td></td>
<td class="text-end"><b><?=number_format($final_total,2)?></b></td>
</tr>
</tfoot>
</table>

<?php if(count($employee_breakdown) > 1): ?>
<h5>Employee Breakdown</h5>
<table class="table table-sm table-bordered">
<tr><th>Emp ID</th><th>Extra</th><th>Deduction</th></tr>
<?php foreach($employee_breakdown as $e): ?>
<tr>
<td><?=$e['emp_id']?></td>
<td><?=number_format($e['extra'],2)?></td>
<td><?=number_format($e['deduction'],2)?></td>
</tr>
<?php endforeach; ?>
</table>
<?php endif; ?>

<form method="post">
<input type="hidden" name="month" value="<?=h($month)?>">
<input type="hidden" name="period" value="<?=h($period)?>">
<button class="btn btn-success">Save Salary</button>
</form>

<?php endif; ?>

</div>

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