View Code - 20260722

salary_summary_by_department.php | 20260722_120325.php Initial Domain Snapshot

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

if (!class_exists(\Dompdf\Dompdf::class)) {
    foreach ([
        __DIR__ . '/lib/dompdf/autoload.inc.php',
        __DIR__ . '/vendor/autoload.php',
    ] as $dompdfAutoload) {
        if (is_file($dompdfAutoload)) {
            require_once $dompdfAutoload;
            if (class_exists(\Dompdf\Dompdf::class)) {
                break;
            }
        }
    }
}

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

$width = 720;
    $height = 260;
    $leftPad = 52;
    $rightPad = 18;
    $topPad = 20;
    $bottomPad = 52;
    $plotWidth = $width - $leftPad - $rightPad;
    $plotHeight = $height - $topPad - $bottomPad;
    $count = count($history);
    $slotWidth = $count > 0 ? ($plotWidth / $count) : $plotWidth;
    $barWidth = max(18, min(48, $slotWidth * 0.55));
    $maxValue = max(array_map(static function ($row) {
        return (float)($row['value'] ?? 0);
    }, $history));
    $maxValue = $maxValue > 0 ? $maxValue : 1;

$svg = [];
    $svg[] = '<svg xmlns="http://www.w3.org/2000/svg" width="'.$width.'" height="'.$height.'" viewBox="0 0 '.$width.' '.$height.'">';
    $svg[] = '<rect width="100%" height="100%" fill="#ffffff"/>';
    $svg[] = '<line x1="'.$leftPad.'" y1="'.$topPad.'" x2="'.$leftPad.'" y2="'.($topPad + $plotHeight).'" stroke="#888" stroke-width="1"/>';
    $svg[] = '<line x1="'.$leftPad.'" y1="'.($topPad + $plotHeight).'" x2="'.($leftPad + $plotWidth).'" y2="'.($topPad + $plotHeight).'" stroke="#888" stroke-width="1"/>';

for ($step = 0; $step <= 4; $step++) {
        $y = $topPad + ($plotHeight - (($plotHeight / 4) * $step));
        $value = ($maxValue / 4) * $step;
        $svg[] = '<line x1="'.$leftPad.'" y1="'.$y.'" x2="'.($leftPad + $plotWidth).'" y2="'.$y.'" stroke="#e5e7eb" stroke-width="1"/>';
        $svg[] = '<text x="'.($leftPad - 6).'" y="'.($y + 4).'" font-size="10" text-anchor="end" fill="#555">'.htmlspecialchars(n0($value), ENT_QUOTES, 'UTF-8').'</text>';
    }

foreach ($history as $index => $row) {
        $value = (float)($row['value'] ?? 0);
        $barHeight = $maxValue > 0 ? ($value / $maxValue) * $plotHeight : 0;
        $x = $leftPad + ($slotWidth * $index) + (($slotWidth - $barWidth) / 2);
        $y = $topPad + $plotHeight - $barHeight;
        $labelX = $leftPad + ($slotWidth * $index) + ($slotWidth / 2);
        $safeLabel = htmlspecialchars((string)($row['label'] ?? ''), ENT_QUOTES, 'UTF-8');
        $safeValue = htmlspecialchars(n0($value), ENT_QUOTES, 'UTF-8');

$svg[] = '<rect x="'.round($x, 2).'" y="'.round($y, 2).'" width="'.round($barWidth, 2).'" height="'.round($barHeight, 2).'" fill="#4e73df"/>';
        $svg[] = '<text x="'.round($labelX, 2).'" y="'.round($y - 6, 2).'" font-size="10" text-anchor="middle" fill="#1f2937">'.$safeValue.'</text>';
        $svg[] = '<text x="'.round($labelX, 2).'" y="'.($topPad + $plotHeight + 18).'" font-size="10" text-anchor="middle" fill="#444">'.$safeLabel.'</text>';
    }

$svg[] = '</svg>';
    return implode('', $svg);
}
function table_exists(PDO $pdo, string $table): bool {
    $st = $pdo->prepare("SHOW TABLES LIKE :tbl");
    $st->execute([':tbl' => $table]);
    return (bool)$st->fetchColumn();
}
function get_pissing_extra_deduction_adjustment(PDO $pdo, int $company_id, string $from, string $to): float {
    $employeeIds = [];

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

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

$employeeIds = array_values(array_unique(array_filter($employeeIds)));
    if (!$employeeIds) {
        return 0.0;
    }

$extraTotal = 0.0;
    $deductionTotal = 0.0;

$extraStmt = $pdo->prepare("\n        SELECT IFNULL(SUM(amount), 0)\n        FROM employee_extra\n        WHERE company_id = :cid\n          AND employee_id = :emp\n          AND entry_date BETWEEN :sd AND :ed\n    ");
    $deductionStmt = $pdo->prepare("\n        SELECT IFNULL(SUM(amount), 0)\n        FROM employee_deduction\n        WHERE company_id = :cid\n          AND employee_id = :emp\n          AND entry_date BETWEEN :sd AND :ed\n    ");

foreach ($employeeIds as $employeeId) {
        $params = [
            ':cid' => $company_id,
            ':emp' => $employeeId,
            ':sd' => $from,
            ':ed' => $to,
        ];

$extraStmt->execute($params);
        $extraTotal += (float)$extraStmt->fetchColumn();

$deductionStmt->execute($params);
        $deductionTotal += (float)$deductionStmt->fetchColumn();
    }

return round($extraTotal - $deductionTotal, 2);
}

/* ---------- INPUT ---------- */
$month  = $_GET['month']  ?? date('Y-m');
$period = $_GET['period'] ?? 'H1';

$month_key = $month.'-01';
$y = (int)date('Y',strtotime($month_key));
$m = (int)date('m',strtotime($month_key));

if ($period==='H1'){
    $from="$month-01"; 
    $to="$month-15";
} else {
    $from="$month-16"; 
    $to=date('Y-m-t',strtotime($month_key));
}

/* ---------- HELPERS ---------- */
require_once __DIR__.'/modules/salary/loom_salary_total.php';
require_once __DIR__.'/modules/salary/pissing_salary_total.php';
require_once __DIR__.'/modules/salary/pasaria_salary_gross_total.php';
require_once __DIR__.'/modules/salary/warper_salary_gross_total.php';

/* ---------- DATA ---------- */
$rows = [];

$st = $pdo->prepare("
SELECT department_name, SUM(gross_amount) total
FROM semi_monthly_salary_report
WHERE company_id=? AND month_key=? AND period_label=?
GROUP BY department_id
");
$st->execute([$company_id,$month_key,$period]);

foreach($st as $r){
    $rows[$r['department_name']] = ($rows[$r['department_name']] ?? 0) + (float)$r['total'];
}

if($period==='H2'){
    $st = $pdo->prepare("
    SELECT department_name, SUM(gross_amount) total
    FROM monthly_salary_report
    WHERE company_id=? AND month_key=?
    GROUP BY department_id
    ");
    $st->execute([$company_id,$month_key]);

foreach($st as $r){
        $rows[$r['department_name']] = ($rows[$r['department_name']] ?? 0) + (float)$r['total'];
    }
}

/* Modules */
$rows['Loom']    = get_loom_salary_total($pdo,$company_id,$from,$to);
$rows['Warping'] = get_warper_salary_gross_total($pdo,$company_id,$month_key,$period);
$rows['Pissing'] = get_pissing_salary_total($pdo,$company_id,$from,$to)
    + get_pissing_extra_deduction_adjustment($pdo,$company_id,$from,$to);
$rows['Pasaria'] = get_pasaria_salary_gross_total($pdo,$company_id,$y,$m,$period);

$rows = array_filter($rows);
$grand_total = array_sum($rows);

/* ---------- HISTORY ---------- */
$history = [];
$curr = strtotime($month_key);

for ($i=0;$i<6;$i++){

$h_month = date('Y-m',$curr);
    $h_key = $h_month.'-01';
    $hy = (int)date('Y',strtotime($h_key));
    $hm = (int)date('m',strtotime($h_key));

$h_period = $period;

if($h_period==='H1'){
        $h_from="$h_month-01";
        $h_to="$h_month-15";
    }else{
        $h_from="$h_month-16";
        $h_to=date('Y-m-t',strtotime($h_key));
    }

$total=0;

$st=$pdo->prepare("SELECT SUM(gross_amount) FROM semi_monthly_salary_report WHERE company_id=? AND month_key=? AND period_label=?");
    $st->execute([$company_id,$h_key,$h_period]);
    $total+=(float)$st->fetchColumn();

if($h_period==='H2'){
        $st=$pdo->prepare("SELECT SUM(gross_amount) FROM monthly_salary_report WHERE company_id=? AND month_key=?");
        $st->execute([$company_id,$h_key]);
        $total+=(float)$st->fetchColumn();
    }

$total+=get_loom_salary_total($pdo,$company_id,$h_from,$h_to);
    $total+=get_warper_salary_gross_total($pdo,$company_id,$h_key,$h_period);
    $total+=get_pissing_salary_total($pdo,$company_id,$h_from,$h_to)
        + get_pissing_extra_deduction_adjustment($pdo,$company_id,$h_from,$h_to);
    $total+=get_pasaria_salary_gross_total($pdo,$company_id,$hy,$hm,$h_period);

$history[]=[
        'label'=>date('M',strtotime($h_key)).' '.$h_period,
        'value'=>round($total,2)
    ];

$curr = strtotime('-1 month', $curr);
}

if (($_GET['export'] ?? '') === 'pdf') {
    if (!class_exists(\Dompdf\Dompdf::class)) {
        http_response_code(503);
        exit('PDF export is not available right now.');
    }

$period_label = ($period === 'H1') ? '1-15' : '16-End';
    ob_start();
    ?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Department Wise Salary Summary PDF</title>
<style>
    @page { margin: 16px; }
    body { margin: 0; font-family: DejaVu Sans, sans-serif; font-size: 10px; color: #111; }
    h1 { margin: 0 0 4px; text-align: center; font-size: 16px; }
    h2 { margin: 0 0 12px; text-align: center; font-size: 12px; font-weight: normal; color: #555; }
    .section-title { margin: 14px 0 8px; font-size: 12px; font-weight: 700; }
    table { width: 100%; border-collapse: collapse; table-layout: fixed; }
    th, td { border: 1px solid #d9d9d9; padding: 6px; }
    th { background: #f3f4f6; font-size: 9px; }
    td { font-size: 10px; }
    .text-end { text-align: right; }
    tfoot th { background: #eef3ff; font-weight: 700; }
    .chart-wrap { margin-top: 10px; border: 1px solid #d9d9d9; padding: 8px; }
    .chart-wrap svg { width: 100%; height: auto; display: block; }
</style>
</head>
<body>
    <h1>Department Wise Salary Summary</h1>
    <h2><?= h(date('F Y', strtotime($month_key))) ?> | <?= h($period_label) ?></h2>

<div class="section-title">Current Summary</div>
    <table>
        <thead>
            <tr><th>Department</th><th class="text-end">Total Salary</th></tr>
        </thead>
        <tbody>
            <?php foreach($rows as $k=>$v): ?>
            <tr>
                <td><?= h($k) ?></td>
                <td class="text-end">Rs. <?= n0($v) ?></td>
            </tr>
            <?php endforeach; ?>
        </tbody>
        <tfoot>
            <tr>
                <th>Grand Total</th>
                <th class="text-end">Rs. <?= n0($grand_total) ?></th>
            </tr>
        </tfoot>
    </table>

<div class="section-title">Salary History (Last 6 Periods)</div>
    <table>
        <thead>
            <tr><th>Period</th><th class="text-end">Total</th></tr>
        </thead>
        <tbody>
            <?php foreach($history as $h): ?>
            <tr>
                <td><?= h($h['label']) ?></td>
                <td class="text-end">Rs. <?= n0($h['value']) ?></td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>

<div class="chart-wrap">
        <?= build_salary_history_svg($history) ?>
    </div>
</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();
    $dompdf->stream('department_salary_summary_' . $month . '_' . $period . '.pdf', ['Attachment' => true]);
    exit;
}

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

@media print {
    .no-print { display:none !important; }
    body { font-size:12px; }
    .container { width:100% !important; }
    .card { border:1px solid #000; box-shadow:none; page-break-inside:avoid; }
    #chart { max-height:200px !important; }
}
</style>

<div class="card mb-4 no-print">
<div class="card-body">
<form method="get" class="row g-3 align-items-end">
<div class="col-md-4">
<label>Month</label>
<input type="month" name="month" class="form-control" value="<?=h($month)?>">
</div>
<div class="col-md-3">
<label>Period</label>
<select name="period" class="form-select">
<option value="H1" <?=$period==='H1'?'selected':''?>>1–15</option>
<option value="H2" <?=$period==='H2'?'selected':''?>>16–End</option>
</select>
</div>
<div class="col-md-5 d-flex gap-2">
<button class="btn btn-primary w-100">Apply</button>
<button type="button" onclick="window.print()" class="btn btn-dark">Print</button>
<button class="btn btn-danger" name="export" value="pdf">PDF</button>
</div>
</form>
</div>
</div>

<h4 class="text-center fw-bold mb-3">
Department Wise Salary Summary (Gross)
</h4>

<div class="card mb-4">
<table class="table table-bordered mb-0">
<thead>
<tr><th>Department</th><th class="text-end">Total Salary</th></tr>
</thead>
<tbody>
<?php foreach($rows as $k=>$v): ?>
<tr>
<td><?=h($k)?></td>
<td class="text-end fw-bold">₹<?=n0($v)?></td>
</tr>
<?php endforeach; ?>
</tbody>
<tfoot>
<tr>
<th>Grand Total</th>
<th class="text-end">₹<?=n0($grand_total)?></th>
</tr>
</tfoot>
</table>
</div>

<div class="card">
<div class="card-header text-center fw-bold">
Salary History (Last 6 Periods)
</div>

<table class="table mb-0">
<thead><tr><th>Period</th><th class="text-end">Total</th></tr></thead>
<tbody>
<?php foreach($history as $h): ?>
<tr>
<td><?=h($h['label'])?></td>
<td class="text-end">₹<?=n0($h['value'])?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>

</div>

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