View Code - 20260721

salary_history.php | 20260721_154033.php Initial Bulk Import

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

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

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

if (!function_exists('h')) {
    function h($v) {
        return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('is_valid_month_input')) {
    function is_valid_month_input($value) {
        return is_string($value) && preg_match('/^\d{4}-\d{2}$/', $value);
    }
}

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

if (!function_exists('get_pissing_extra_deduction_adjustment')) {
    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);
    }
}

if (!function_exists('get_live_salary_history_month_total')) {
    function get_live_salary_history_month_total(PDO $pdo, int $company_id, string $month): float {
        $month_key = $month . '-01';
        $year = (int)date('Y', strtotime($month_key));
        $monthNum = (int)date('m', strtotime($month_key));
        $from = $month . '-01';
        $to = date('Y-m-t', strtotime($month_key));

$semiStmt = $pdo->prepare("\n            SELECT IFNULL(SUM(gross_amount), 0)\n            FROM semi_monthly_salary_report\n            WHERE company_id = ? AND month_key = ?\n        ");
        $semiStmt->execute([$company_id, $month_key]);
        $semiTotal = (float)$semiStmt->fetchColumn();

$monthlyStmt = $pdo->prepare("\n            SELECT IFNULL(SUM(gross_amount), 0)\n            FROM monthly_salary_report\n            WHERE company_id = ? AND month_key = ?\n        ");
        $monthlyStmt->execute([$company_id, $month_key]);
        $monthlyTotal = (float)$monthlyStmt->fetchColumn();

$loomTotal = get_loom_salary_total($pdo, $company_id, $from, $to);
        $pissingTotal = get_pissing_salary_total($pdo, $company_id, $from, $to)
            + get_pissing_extra_deduction_adjustment($pdo, $company_id, $from, $to);
        $pasariaTotal = get_pasaria_salary_gross_total($pdo, $company_id, $year, $monthNum, 'H1')
            + get_pasaria_salary_gross_total($pdo, $company_id, $year, $monthNum, 'H2');
        $warperTotal = get_warper_salary_gross_total($pdo, $company_id, $month_key, 'H1')
            + get_warper_salary_gross_total($pdo, $company_id, $month_key, 'H2');

return round($semiTotal + $monthlyTotal + $loomTotal + $pissingTotal + $pasariaTotal + $warperTotal, 2);
    }
}

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';

// ===== INPUT =====
$defaultMonth = date('Y-m');
$from_month = is_valid_month_input($_GET['from_month'] ?? null) ? $_GET['from_month'] : ($defaultMonth);
$to_month = is_valid_month_input($_GET['to_month'] ?? null) ? $_GET['to_month'] : ($from_month);

if (strtotime($from_month . '-01') > strtotime($to_month . '-01')) {
    [$from_month, $to_month] = [$to_month, $from_month];
}

$from_key = $from_month . '-01';
$to_key = $to_month . '-01';

// ===== AGGREGATE =====
$monthly_totals = [];

$cursor = strtotime($from_key);
$endCursor = strtotime($to_key);
while ($cursor <= $endCursor) {
    $monthIndex = date('Y-m', $cursor);
    $monthly_totals[$monthIndex] = [
        'label' => date('F Y', $cursor),
        'total' => get_live_salary_history_month_total($pdo, $company_id, $monthIndex),
    ];
    $cursor = strtotime('+1 month', $cursor);
}

ksort($monthly_totals);

$grand_total = array_sum(array_column($monthly_totals, 'total'));
$chart_labels = array_values(array_column($monthly_totals, 'label'));
$chart_values = array_values(array_map(static function ($item) {
    return (float)$item['total'];
}, $monthly_totals));
?>

.print-page {
    max-width: 190mm;
    margin: 0 auto;
}

.salary-history-chart-card {
    margin-top: 20px;
    padding: 16px;
    overflow: hidden;
}

.salary-history-chart-box {
    position: relative;
    width: 100%;
    max-width: 100%;
    height: 280px;
}

#salaryHistoryChart {
    display: block;
    width: 100% !important;
    max-width: 100% !important;
    height: 100% !important;
}

@media print {
    .no-print { display: none !important; }
    body { font-size: 12px; margin: 0; }
    .print-page {
        width: 100% !important;
        max-width: 190mm;
    }
    .table th,
    .table td { padding: 6px 8px; }
    .salary-history-chart-card {
        margin-top: 12px;
        padding: 10px;
        page-break-inside: avoid;
        break-inside: avoid;
    }
    .salary-history-chart-box {
        width: 100%;
        max-width: 100%;
        height: 165px;
    }
    #salaryHistoryChart {
        max-height: 165px !important;
    }
}
</style>

<div>
            <label>From Month</label>
            <input type="month" name="from_month" value="<?=h($from_month)?>">
        </div>

<div>
            <label>To Month</label>
            <input type="month" name="to_month" value="<?=h($to_month)?>">
        </div>

<div>
            <button class="btn btn-primary">Load</button>
            <button type="button" onclick="window.print()" class="btn btn-success">
                🖨️ Print / PDF
            </button>
        </div>

</form>
</div>

<h2 style="text-align:center;">Salary History</h2>
    <p style="text-align:center;">
        <?=h(date('F Y', strtotime($from_key)))?> to <?=h(date('F Y', strtotime($to_key)))?>
    </p>

<table class="table">
        <thead>
            <tr>
                <th>Month</th>
                <th>Total</th>
            </tr>
        </thead>
        <tbody>
            <?php if (!$monthly_totals): ?>
            <tr>
                <td colspan="2" style="text-align:center;">No data found.</td>
            </tr>
            <?php else: ?>
            <?php foreach ($monthly_totals as $item): ?>
            <tr>
                <td><?=h($item['label'])?></td>
                <td><?=number_format($item['total'])?></td>
            </tr>
            <?php endforeach; ?>
            <tr>
                <td>GRAND TOTAL</td>
                <td style="font-weight:bold; border-top:2px solid #000;"><?=number_format($grand_total)?></td>
            </tr>
            <?php endif; ?>
        </tbody>
    </table>

<?php if ($monthly_totals): ?>
    <div class="card salary-history-chart-card">
        <h3 style="text-align:center; margin:0 0 16px;">Monthly Total Graph</h3>
        <div class="salary-history-chart-box">
            <canvas id="salaryHistoryChart"></canvas>
        </div>
    </div>
    <?php endif; ?>

</div>

<?php if ($monthly_totals): ?>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
new Chart(document.getElementById('salaryHistoryChart'), {
    type: 'bar',
    data: {
        labels: <?= json_encode($chart_labels) ?>,
        datasets: [{
            label: 'Monthly Total',
            data: <?= json_encode($chart_values) ?>,
            backgroundColor: '#198754',
            borderColor: '#146c43',
            borderWidth: 1,
            categoryPercentage: 0.72,
            barPercentage: 0.9,
            maxBarThickness: 36
        }]
    },
    options: {
        responsive: true,
        maintainAspectRatio: false,
        animation: false,
        layout: {
            padding: { left: 4, right: 4, top: 0, bottom: 0 }
        },
        plugins: {
            legend: { display: false }
        },
        scales: {
            x: {
                offset: false,
                grid: {
                    display: false
                },
                ticks: {
                    autoSkip: true,
                    maxTicksLimit: 8,
                    maxRotation: 45,
                    minRotation: 45,
                    font: { size: 9 }
                }
            },
            y: {
                beginAtZero: true,
                ticks: {
                    font: { size: 9 }
                }
            }
        }
    }
});
</script>
<?php endif; ?>

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