View Code - 20260723

excel_report.php | 20260723_000646.php Initial Domain Snapshot

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

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

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

function column_exists(PDO $pdo, string $table, string $column): bool
{
    $stmt = $pdo->prepare("SHOW COLUMNS FROM {$table} LIKE :col");
    $stmt->execute([':col' => $column]);
    return (bool)$stmt->fetchColumn();
}

if (column_exists($pdo, 'company_bank_accounts', 'company_id')) {
    $accountStmt = $pdo->prepare(
        "SELECT DISTINCT bank_user_id FROM company_bank_accounts WHERE company_id = :cid"
    );
    $accountStmt->execute([':cid' => $company_id]);
} else {
    $accountStmt = $pdo->prepare(
        "SELECT DISTINCT bank_user_id FROM company_bank_accounts"
    );
    $accountStmt->execute();
}
$bankUserIds = array_column($accountStmt->fetchAll(PDO::FETCH_ASSOC), 'bank_user_id');
$bankUserIds = array_map('trim', $bankUserIds);
$bankUserIds = array_filter($bankUserIds, function ($value) {
    return $value !== '';
});

function normalize_account_number($value)
{
    $value = trim((string)$value);
    if ($value === '') {
        return null;
    }
    $digits = preg_replace('/\D+/', '', $value);
    return $digits === '' ? null : $digits;
}

function scope_column_for_table(PDO $pdo, string $table): ?string
{
    foreach (['company_id', 'cid'] as $candidate) {
        if (column_exists($pdo, $table, $candidate)) {
            return $candidate;
        }
    }
    return null;
}

$departmentNameMap = [];
if (table_exists($pdo, 'company_departments')) {
    $stmt = $pdo->prepare("SELECT id, name FROM company_departments WHERE company_id = :cid");
    $stmt->execute([':cid' => $company_id]);
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $departmentNameMap[(int)$row['id']] = trim((string)$row['name']);
    }
}

$employeeDeptById = [];
$accountTraceMap = [];
$accountDeptMap = [];
$accountSources = [];
$accountTables = [
    'company_employee_master' => 'account_number',
    'loom_karigar_master' => 'beneficiary_ac_number',
    'party_ac_detail' => 'account_no',
    'contractor_employee' => 'account_no',
    'employee_bank_data' => 'beneficiary_ac_number',
];

foreach ($accountTables as $table => $column) {
    $scopeColumn = scope_column_for_table($pdo, $table);
    $selectFields = "{$column} AS account_value";
    if (column_exists($pdo, $table, 'id')) {
        $selectFields .= ", id AS row_id";
    }
    if (column_exists($pdo, $table, 'employee_id')) {
        $selectFields .= ", employee_id";
    }
    if (column_exists($pdo, $table, 'department_id')) {
        $selectFields .= ", department_id";
    }
    if (column_exists($pdo, $table, 'department_name')) {
        $selectFields .= ", department_name";
    }

if ($scopeColumn !== null) {
        $stmt = $pdo->prepare("SELECT {$selectFields} FROM {$table} WHERE {$scopeColumn} = :cid AND {$column} IS NOT NULL AND {$column} <> ''");
        $stmt->execute([':cid' => $company_id]);
    } else {
        $stmt = $pdo->prepare("SELECT {$selectFields} FROM {$table} WHERE {$column} IS NOT NULL AND {$column} <> ''");
        $stmt->execute();
    }

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $acct = normalize_account_number($row['account_value'] ?? '');
        if ($acct === null) {
            continue;
        }

if (!isset($accountSources[$acct])) {
            $accountSources[$acct] = [];
        }
        if (!in_array($table, $accountSources[$acct], true)) {
            $accountSources[$acct][] = $table;
        }

$employeeId = $table === 'company_employee_master'
            ? (int)($row['row_id'] ?? 0)
            : (int)($row['employee_id'] ?? 0);
        $departmentId = (int)($row['department_id'] ?? 0);
        $departmentName = trim((string)($row['department_name'] ?? ''));
        if ($table === 'loom_karigar_master') {
            $departmentName = 'Loom Karigar';
            $departmentId = 19;
            $employeeId = 0;
        } elseif ($departmentName === '' && $departmentId > 0) {
            $departmentName = $departmentNameMap[$departmentId] ?? '';
        }

if ($employeeId > 0 && !isset($employeeDeptById[$employeeId])) {
            $employeeDeptById[$employeeId] = [
                'department_id' => $departmentId,
                'department_name' => $departmentName,
            ];
        }

if (!isset($accountTraceMap[$acct])) {
            $accountTraceMap[$acct] = [
                'employee_id' => $employeeId,
                'department_id' => $departmentId,
                'department_name' => $departmentName,
            ];
        }
    }
}

if (table_exists($pdo, 'account_department_map') && column_exists($pdo, 'account_department_map', 'account_no')) {
    $scopeColumn = scope_column_for_table($pdo, 'account_department_map');
    $selectFields = "account_no, department_id";
    if (column_exists($pdo, 'account_department_map', 'department_name')) {
        $selectFields .= ", department_name";
    }
    $sql = "SELECT {$selectFields} FROM account_department_map WHERE account_no IS NOT NULL AND account_no <> ''";
    if ($scopeColumn !== null) {
        $sql .= " AND {$scopeColumn} = :cid";
        if (column_exists($pdo, 'account_department_map', 'is_active')) {
            $sql .= " AND COALESCE(is_active,1) = 1";
        }
        $stmt = $pdo->prepare($sql);
        $stmt->execute([':cid' => $company_id]);
    } else {
        $stmt = $pdo->prepare($sql);
        $stmt->execute();
    }

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $acct = normalize_account_number($row['account_no'] ?? '');
        if ($acct !== null && !isset($accountDeptMap[$acct])) {
            $departmentId = (int)($row['department_id'] ?? 0);
            $departmentName = trim((string)($row['department_name'] ?? ''));
            if ($departmentName === '' && $departmentId > 0) {
                $departmentName = $departmentNameMap[$departmentId] ?? '';
            }
            $accountDeptMap[$acct] = [
                'department_id' => $departmentId,
                'department_name' => $departmentName,
            ];
        }
    }
}

$deletedAccountMap = [];
if (table_exists($pdo, 'deleted_bank_ac') && column_exists($pdo, 'deleted_bank_ac', 'bank_ac_no')) {
    $deletedScope = scope_column_for_table($pdo, 'deleted_bank_ac');
    $deletedRefExpr = column_exists($pdo, 'deleted_bank_ac', 'ref_table') ? 'ref_table' : "'' AS ref_table";
    $deletedDeptExpr = column_exists($pdo, 'deleted_bank_ac', 'department_id') ? 'department_id' : '0 AS department_id';
    $deletedEmpExpr = column_exists($pdo, 'deleted_bank_ac', 'employee_id') ? 'employee_id' : '0 AS employee_id';

$deletedQueries = [];
    if ($deletedScope !== null) {
        $deletedQueries[] = [
            "SELECT bank_ac_no, {$deletedRefExpr}, {$deletedDeptExpr}, {$deletedEmpExpr} FROM deleted_bank_ac WHERE {$deletedScope} = :cid AND bank_ac_no IS NOT NULL AND bank_ac_no <> '' ORDER BY id DESC",
            [':cid' => $company_id]
        ];
    }
    $deletedQueries[] = [
        "SELECT bank_ac_no, {$deletedRefExpr}, {$deletedDeptExpr}, {$deletedEmpExpr} FROM deleted_bank_ac WHERE bank_ac_no IS NOT NULL AND bank_ac_no <> '' ORDER BY id DESC",
        []
    ];

foreach ($deletedQueries as [$sql, $bind]) {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($bind);
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $acct = normalize_account_number($row['bank_ac_no'] ?? '');
            if ($acct !== null && !isset($deletedAccountMap[$acct])) {
                $employeeId = (int)($row['employee_id'] ?? 0);
                $departmentId = (int)($row['department_id'] ?? 0);
                if ($departmentId <= 0 && $employeeId > 0 && isset($employeeDeptById[$employeeId])) {
                    $departmentId = (int)$employeeDeptById[$employeeId]['department_id'];
                }
                $deletedAccountMap[$acct] = [
                    'source' => trim((string)($row['ref_table'] ?? '')) ?: 'deleted_bank_ac',
                    'employee_id' => $employeeId,
                    'department_id' => $departmentId,
                ];
            }
        }
    }
}

$bankStatementMap = [];
if (table_exists($pdo, 'bank_icici_import') && column_exists($pdo, 'bank_icici_import', 'beneficiary_ac_no')) {
    $bankScope = scope_column_for_table($pdo, 'bank_icici_import');
    if ($bankScope !== null) {
        $stmt = $pdo->prepare("SELECT beneficiary_ac_no, amount, txn_date, status FROM bank_icici_import WHERE {$bankScope} = :cid AND beneficiary_ac_no IS NOT NULL AND beneficiary_ac_no <> ''");
        $stmt->execute([':cid' => $company_id]);
    } else {
        $stmt = $pdo->prepare("SELECT beneficiary_ac_no, amount, txn_date, status FROM bank_icici_import WHERE beneficiary_ac_no IS NOT NULL AND beneficiary_ac_no <> ''");
        $stmt->execute();
    }

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $acct = normalize_account_number($row['beneficiary_ac_no'] ?? '');
        $amt = normalize_amount($row['amount'] ?? null);
        if ($acct === null || $amt === null) {
            continue;
        }
        $key = $acct . '|' . number_format($amt, 2, '.', '');
        $bankStatementMap[$key][] = [
            'txn_date' => normalize_date($row['txn_date'] ?? null),
            'status' => trim((string)($row['status'] ?? '')),
        ];
    }
}

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

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;

$folder = __DIR__ . '/excel';
$files = glob($folder . '/*.xlsx') ?: [];
$rows = [];
$seenEntries = [];

$from_date = !empty($_GET['from_date']) ? trim((string)$_GET['from_date']) : '';
$to_date = !empty($_GET['to_date']) ? trim((string)$_GET['to_date']) : '';
$remark_query = !empty($_GET['remark_query']) ? trim((string)$_GET['remark_query']) : '';
$remark_mode = !empty($_GET['remark_mode']) ? trim((string)$_GET['remark_mode']) : 'include';
$min_amount = !empty($_GET['min_amount']) ? trim((string)$_GET['min_amount']) : '';
$max_amount = !empty($_GET['max_amount']) ? trim((string)$_GET['max_amount']) : '';

function normalize_date($value)
{
    if ($value === null || $value === '') {
        return null;
    }

if (is_numeric($value)) {
        try {
            $dateTime = Date::excelToDateTimeObject($value);
            return $dateTime->format('Y-m-d');
        } catch (Exception $e) {
        }
    }

$timestamp = strtotime((string)$value);
    if ($timestamp !== false) {
        return date('Y-m-d', $timestamp);
    }

if (preg_match('#^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$#', (string)$value, $matches)) {
        return sprintf('%04d-%02d-%02d', $matches[3], $matches[2], $matches[1]);
    }

return null;
}

function normalize_amount($value)
{
    if ($value === null || $value === '') {
        return null;
    }

$normalized = str_replace([',', ' ', '₹', '$', '€'], '', (string)$value);
    if (is_numeric($normalized)) {
        return (float)$normalized;
    }

return null;
}

$normalized_from = normalize_date($from_date);
$normalized_to = normalize_date($to_date);

foreach ($files as $file) {
    $basename = basename($file);
    if (!preg_match('/^REQ_(\d+)/i', $basename, $matches)) {
        continue;
    }

$fileBankUserId = $matches[1];
    if (!empty($bankUserIds) && !in_array($fileBankUserId, $bankUserIds, true)) {
        continue;
    }

try {
        $spreadsheet = IOFactory::load($file);
        $sheet = $spreadsheet->getActiveSheet();
        $data = $sheet->toArray();

for ($i = 1; $i < count($data); $i++) {
            $r = $data[$i];
            if (empty($r[0])) {
                continue;
            }

$row_date = normalize_date($r[5] ?? null);
            if ($row_date !== null && $normalized_from !== null && $row_date < $normalized_from) {
                continue;
            }
            if ($row_date !== null && $normalized_to !== null && $row_date > $normalized_to) {
                continue;
            }

$row_amount = isset($r[3]) ? normalize_amount($r[3]) : null;
            if ($row_amount !== null && $min_amount !== '' && is_numeric($min_amount) && $row_amount < (float)$min_amount) {
                continue;
            }
            if ($row_amount !== null && $max_amount !== '' && is_numeric($max_amount) && $row_amount > (float)$max_amount) {
                continue;
            }

$row_remarks = isset($r[18]) ? (string)$r[18] : '';
            if ($remark_query !== '') {
                $hasRemarkMatch = stripos($row_remarks, $remark_query) !== false;
                if ($remark_mode === 'exclude' && $hasRemarkMatch) {
                    continue;
                }
                if ($remark_mode === 'include' && !$hasRemarkMatch) {
                    continue;
                }
            }

$candidates = [];
            $primaryAccount = normalize_account_number($r[1] ?? '');
            if ($primaryAccount !== null) {
                $candidates[$primaryAccount] = true;
            }

foreach ($r as $cell) {
                $cellValue = trim((string)$cell);
                if ($cellValue === '') {
                    continue;
                }
                preg_match_all('/\d{9,}/', $cellValue, $foundMatches);
                foreach ($foundMatches[0] as $match) {
                    $normalizedMatch = normalize_account_number($match);
                    if ($normalizedMatch !== null) {
                        $candidates[$normalizedMatch] = true;
                    }
                }
            }

$accountCandidate = $primaryAccount;
            $sourceTable = 'unknown';
            $employeeId = 0;
            $departmentId = 0;
            $departmentName = '';

foreach (array_keys($candidates) as $candidate) {
                $hasMapping = isset($accountSources[$candidate]) || isset($deletedAccountMap[$candidate]) || isset($accountDeptMap[$candidate]);
                if (!$hasMapping) {
                    continue;
                }

$accountCandidate = $candidate;
                if (isset($accountSources[$candidate])) {
                    $sourceTable = implode(', ', $accountSources[$candidate]);
                }
                if (isset($accountTraceMap[$candidate])) {
                    $employeeId = (int)($accountTraceMap[$candidate]['employee_id'] ?? 0);
                    $departmentId = (int)($accountTraceMap[$candidate]['department_id'] ?? 0);
                    $departmentName = trim((string)($accountTraceMap[$candidate]['department_name'] ?? ''));
                }
                $isLoomKarigar = strpos($sourceTable, 'loom_karigar_master') !== false || $departmentName === 'Loom Karigar';
                if (!$isLoomKarigar && $employeeId > 0 && isset($employeeDeptById[$employeeId])) {
                    if ($departmentId <= 0) {
                        $departmentId = (int)$employeeDeptById[$employeeId]['department_id'];
                    }
                    if ($departmentName === '') {
                        $departmentName = trim((string)$employeeDeptById[$employeeId]['department_name']);
                    }
                }
                if (isset($deletedAccountMap[$candidate])) {
                    if ($sourceTable === 'unknown') {
                        $sourceTable = $deletedAccountMap[$candidate]['source'];
                    }
                    if ($employeeId <= 0) {
                        $employeeId = (int)($deletedAccountMap[$candidate]['employee_id'] ?? 0);
                    }
                    if ($departmentId <= 0) {
                        $departmentId = (int)($deletedAccountMap[$candidate]['department_id'] ?? 0);
                    }
                }
                if (!$isLoomKarigar && $employeeId > 0 && isset($employeeDeptById[$employeeId])) {
                    if ($departmentId <= 0) {
                        $departmentId = (int)$employeeDeptById[$employeeId]['department_id'];
                    }
                    if ($departmentName === '') {
                        $departmentName = trim((string)$employeeDeptById[$employeeId]['department_name']);
                    }
                }
                if (($departmentId <= 0 || $departmentName === '') && isset($accountDeptMap[$candidate])) {
                    if ($departmentId <= 0) {
                        $departmentId = (int)$accountDeptMap[$candidate]['department_id'];
                    }
                    if ($departmentName === '') {
                        $departmentName = trim((string)$accountDeptMap[$candidate]['department_name']);
                    }
                }
                if ($isLoomKarigar) {
                    $employeeId = 0;
                    $departmentId = 19;
                    $departmentName = 'Loom Karigar';
                } elseif ($departmentName === '' && $departmentId > 0) {
                    $departmentName = $departmentNameMap[$departmentId] ?? '';
                }
                break;
            }

if (empty($accountCandidate)) {
                continue;
            }

$periodKey = $row_date !== null ? date('Y-m', strtotime($row_date)) : 'no-period';
            $amountKey = $row_amount !== null ? number_format($row_amount, 2, '.', '') : '0.00';
            $dedupeKey = $accountCandidate . '|' . $amountKey . '|' . $periodKey;
            if (isset($seenEntries[$dedupeKey])) {
                continue;
            }
            $seenEntries[$dedupeKey] = true;

$verifyStatus = 'Unverified';
            $bankTxnDate = '';
            $bankKey = $accountCandidate . '|' . $amountKey;
            if (isset($bankStatementMap[$bankKey])) {
                $verifyStatus = 'Verified';
                $bankMatches = $bankStatementMap[$bankKey];
                $chosenMatch = $bankMatches[0];
                if ($row_date !== null) {
                    foreach ($bankMatches as $match) {
                        if (!empty($match['txn_date']) && $match['txn_date'] >= $row_date) {
                            $chosenMatch = $match;
                            break;
                        }
                    }
                }
                $bankTxnDate = $chosenMatch['txn_date'] ?? '';
            }

if ($verifyStatus !== 'Verified') {
                continue;
            }

$rows[] = [
                'file' => $basename,
                'beneficiary' => $r[2] ?? '',
                'amount' => $r[3] ?? '',
                'date' => $r[5] ?? '',
                'remarks' => $row_remarks,
                'account' => $accountCandidate ?? '',
                'source' => $sourceTable,
                'employee_id' => $employeeId,
                'department_id' => $departmentId,
                'department_name' => $departmentName,
                'verify_status' => $verifyStatus,
                'bank_txn_date' => $bankTxnDate,
            ];
        }
    } catch (Exception $e) {
        continue;
    }
}

$expenseGroupMeta = [];
if (table_exists($pdo, 'expense_group') && table_exists($pdo, 'expense_group_mapping')) {
    $groupStmt = $pdo->prepare("SELECT id, group_name FROM expense_group");
    $groupStmt->execute();
    while ($g = $groupStmt->fetch(PDO::FETCH_ASSOC)) {
        $expenseGroupMeta[(int)$g['id']] = trim((string)$g['group_name']);
    }
}

$groupedRows = [];
$sourceTotals = [];
$departmentTotals = [];
$namedDepartmentTotals = [];
$verificationTotals = [];
$finalExpenseReport = [];
$finalExpenseDepartments = [];
foreach ($rows as $row) {
    $amount = normalize_amount($row['amount']);
    $groupedRows[$row['file']]['total'] = ($groupedRows[$row['file']]['total'] ?? 0) + ($amount !== null ? $amount : 0);
    $groupedRows[$row['file']]['rows'][] = $row;

$source = $row['source'] ?? 'unknown';
    $sourceTotals[$source]['total'] = ($sourceTotals[$source]['total'] ?? 0) + ($amount !== null ? $amount : 0);
    $sourceTotals[$source]['count'] = ($sourceTotals[$source]['count'] ?? 0) + 1;

$departmentId = (int)($row['department_id'] ?? 0);
    $departmentName = trim((string)($row['department_name'] ?? ''));
    if ($departmentId > 0) {
        $departmentTotals[$departmentId]['total'] = ($departmentTotals[$departmentId]['total'] ?? 0) + ($amount !== null ? $amount : 0);
        $departmentTotals[$departmentId]['count'] = ($departmentTotals[$departmentId]['count'] ?? 0) + 1;

$deptKey = $departmentId . '|' . $departmentName;
        if (!isset($namedDepartmentTotals[$deptKey])) {
            $namedDepartmentTotals[$deptKey] = [
                'department_id' => $departmentId,
                'department_name' => $departmentName !== '' ? $departmentName : ('Dept ' . $departmentId),
                'count' => 0,
                'total' => 0,
            ];
        }
        $namedDepartmentTotals[$deptKey]['count']++;
        $namedDepartmentTotals[$deptKey]['total'] += ($amount !== null ? $amount : 0);

if (table_exists($pdo, 'expense_group_mapping')) {
            $mapStmt = $pdo->prepare("SELECT group_id, allocation_percent FROM expense_group_mapping WHERE company_id = :cid AND department_id = :did");
            $mapStmt->execute([':cid' => $company_id, ':did' => $departmentId]);
            while ($gm = $mapStmt->fetch(PDO::FETCH_ASSOC)) {
                $groupId = (int)($gm['group_id'] ?? 0);
                $percent = (float)($gm['allocation_percent'] ?? 0);
                if ($groupId <= 0) {
                    continue;
                }
                $allocatedAmount = ($amount !== null ? $amount : 0) * ($percent / 100);
                if (!isset($finalExpenseReport[$groupId])) {
                    $finalExpenseReport[$groupId] = [
                        'group_name' => $expenseGroupMeta[$groupId] ?? ('Group ' . $groupId),
                        'count' => 0,
                        'total' => 0,
                    ];
                }
                $finalExpenseReport[$groupId]['count']++;
                $finalExpenseReport[$groupId]['total'] += $allocatedAmount;

$deptLabel = $departmentName !== '' ? $departmentName : ('Dept ' . $departmentId);
                if (!isset($finalExpenseDepartments[$groupId])) {
                    $finalExpenseDepartments[$groupId] = [];
                }
                if (!isset($finalExpenseDepartments[$groupId][$departmentId])) {
                    $finalExpenseDepartments[$groupId][$departmentId] = [
                        'department_name' => $deptLabel,
                        'count' => 0,
                        'total' => 0,
                    ];
                }
                $finalExpenseDepartments[$groupId][$departmentId]['count']++;
                $finalExpenseDepartments[$groupId][$departmentId]['total'] += $allocatedAmount;
            }
        }
    }

$verifyStatus = $row['verify_status'] ?? 'Verified';
    $verificationTotals[$verifyStatus]['total'] = ($verificationTotals[$verifyStatus]['total'] ?? 0) + ($amount !== null ? $amount : 0);
    $verificationTotals[$verifyStatus]['count'] = ($verificationTotals[$verifyStatus]['count'] ?? 0) + 1;
}

ksort($groupedRows, SORT_NATURAL | SORT_FLAG_CASE);
ksort($sourceTotals);
ksort($departmentTotals, SORT_NUMERIC);
ksort($namedDepartmentTotals);
ksort($verificationTotals);
ksort($finalExpenseReport);
foreach ($finalExpenseDepartments as &$deptRows) {
    uasort($deptRows, function ($a, $b) {
        return ($b['total'] <=> $a['total']);
    });
}
unset($deptRows);
?>

<div class="card">
    <h3>Excel Combined Report</h3>
    <form method="get" class="form-inline" style="margin-bottom: 1rem; gap: 0.5rem; display: flex; flex-wrap: wrap; align-items: center;">
        <label for="from_date">From:</label>
        <input type="date" id="from_date" name="from_date" value="<?= htmlspecialchars($from_date) ?>" class="form-control">

<label for="to_date">To:</label>
        <input type="date" id="to_date" name="to_date" value="<?= htmlspecialchars($to_date) ?>" class="form-control">

<label for="min_amount">Min Amount:</label>
        <input type="number" step="0.01" id="min_amount" name="min_amount" value="<?= htmlspecialchars($min_amount) ?>" class="form-control" placeholder="0">

<label for="max_amount">Max Amount:</label>
        <input type="number" step="0.01" id="max_amount" name="max_amount" value="<?= htmlspecialchars($max_amount) ?>" class="form-control" placeholder="0">

<label for="remark_query">Remarks:</label>
        <input type="text" id="remark_query" name="remark_query" value="<?= htmlspecialchars($remark_query) ?>" class="form-control" placeholder="Search remarks">

<label for="remark_mode">Remark mode:</label>
        <select id="remark_mode" name="remark_mode" class="form-control">
            <option value="include" <?= $remark_mode === 'include' ? 'selected' : '' ?>>Include</option>
            <option value="exclude" <?= $remark_mode === 'exclude' ? 'selected' : '' ?>>Exclude</option>
        </select>

<button type="submit" class="btn btn-primary">Filter</button>
        <a href="<?= basename(__FILE__) ?>" class="btn btn-secondary">Reset</a>
    </form>

<?php if (!empty($sourceTotals)): ?>
        <div class="mb-3">
            <h5>Amount by source table</h5>
            <table class="table table-sm table-bordered" style="max-width: 480px;">
                <thead>
                    <tr>
                        <th>Source</th>
                        <th>Count</th>
                        <th>Total Amount</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($sourceTotals as $source => $stats): ?>
                        <tr>
                            <td><?= htmlspecialchars($source) ?></td>
                            <td><?= htmlspecialchars($stats['count']) ?></td>
                            <td><?= number_format($stats['total'], 2) ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    <?php endif; ?>

<?php if (!empty($departmentTotals)): ?>
        <div class="mb-3">
            <h5>Department wise amount</h5>
            <table class="table table-sm table-bordered" style="max-width: 480px;">
                <thead>
                    <tr>
                        <th>Department ID</th>
                        <th>Count</th>
                        <th>Total Amount</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($departmentTotals as $deptId => $stats): ?>
                        <tr>
                            <td><?= htmlspecialchars((string)$deptId) ?></td>
                            <td><?= htmlspecialchars($stats['count']) ?></td>
                            <td><?= number_format($stats['total'], 2) ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    <?php endif; ?>

<?php if (!empty($namedDepartmentTotals)): ?>
        <div class="mb-3">
            <h5>Backtracked department summary</h5>
            <table class="table table-sm table-bordered" style="max-width: 680px;">
                <thead>
                    <tr>
                        <th>Department ID</th>
                        <th>Department Name</th>
                        <th>Count</th>
                        <th>Total Amount</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($namedDepartmentTotals as $stats): ?>
                        <tr>
                            <td><?= htmlspecialchars((string)$stats['department_id']) ?></td>
                            <td><?= htmlspecialchars($stats['department_name']) ?></td>
                            <td><?= htmlspecialchars($stats['count']) ?></td>
                            <td><?= number_format($stats['total'], 2) ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    <?php endif; ?>

<?php if (!empty($verificationTotals)): ?>
        <div class="mb-3">
            <h5>Bank verification summary</h5>
            <table class="table table-sm table-bordered" style="max-width: 480px;">
                <thead>
                    <tr>
                        <th>Status</th>
                        <th>Count</th>
                        <th>Total Amount</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($verificationTotals as $status => $stats): ?>
                        <tr>
                            <td><?= htmlspecialchars($status) ?></td>
                            <td><?= htmlspecialchars($stats['count']) ?></td>
                            <td><?= number_format($stats['total'], 2) ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    <?php endif; ?>

<?php if (!empty($finalExpenseReport)): ?>
        <div class="mb-3">
            <h5>Final expense report</h5>
            <table class="table table-sm table-bordered" style="max-width: 820px;">
                <thead>
                    <tr>
                        <th>Expense Group</th>
                        <th>Count</th>
                        <th>Total Amount</th>
                        <th>Department Contribution</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($finalExpenseReport as $groupId => $stats): ?>
                        <tr>
                            <td><?= htmlspecialchars($stats['group_name']) ?></td>
                            <td><?= htmlspecialchars($stats['count']) ?></td>
                            <td><?= number_format($stats['total'], 2) ?></td>
                            <td>
                                <?php if (!empty($finalExpenseDepartments[$groupId])): ?>
                                    <?php foreach ($finalExpenseDepartments[$groupId] as $deptId => $deptStats): ?>
                                        <div>
                                            <strong><?= htmlspecialchars($deptStats['department_name']) ?></strong>
                                            (<?= htmlspecialchars((string)$deptId) ?>)
                                            : <?= number_format($deptStats['total'], 2) ?>
                                        </div>
                                    <?php endforeach; ?>
                                <?php endif; ?>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    <?php endif; ?>

<table class="table">
        <thead>
            <tr>
                <th>File</th>
                <th>Beneficiary</th>
                <th>Account</th>
                <th>Source Table</th>
                <th>Employee ID</th>
                <th>Department ID</th>
                <th>Department Name</th>
                <th>Amount</th>
                <th>Excel Date</th>
                <th>Bank Date</th>
                <th>Status</th>
                <th>Remarks</th>
            </tr>
        </thead>
        <tbody>
            <?php if (empty($groupedRows)): ?>
                <tr>
                    <td colspan="12" class="text-center">No records found.</td>
                </tr>
            <?php endif; ?>

<?php foreach ($groupedRows as $file => $group): ?>
                <tr class="table-secondary">
                    <td><strong><?= htmlspecialchars($file) ?></strong></td>
                    <td colspan="7"><strong>Total Records: <?= count($group['rows']) ?></strong></td>
                    <td><strong><?= number_format($group['total'], 2) ?></strong></td>
                    <td></td>
                    <td></td>
                    <td></td>
                </tr>
                <?php foreach ($group['rows'] as $r): ?>
                    <tr>
                        <td></td>
                        <td><?= htmlspecialchars($r['beneficiary']) ?></td>
                        <td><?= htmlspecialchars($r['account'] ?? '') ?></td>
                        <td><?= htmlspecialchars($r['source'] ?? 'unknown') ?></td>
                        <td><?= htmlspecialchars((string)($r['employee_id'] ?? 0)) ?></td>
                        <td><?= htmlspecialchars((string)($r['department_id'] ?? 0)) ?></td>
                        <td><?= htmlspecialchars($r['department_name'] ?? '') ?></td>
                        <td><?= htmlspecialchars($r['amount']) ?></td>
                        <td><?= htmlspecialchars($r['date']) ?></td>
                        <td><?= htmlspecialchars($r['bank_txn_date'] ?? '') ?></td>
                        <td><?= htmlspecialchars($r['verify_status'] ?? 'Verified') ?></td>
                        <td><?= htmlspecialchars($r['remarks']) ?></td>
                    </tr>
                <?php endforeach; ?>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>

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