View Code - 20260722

expense_import.php | 20260722_120325.php Initial Domain Snapshot

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

/* ================= AUTH / CONTEXT ================= */
require_once __DIR__ . '/modules/auth/page_acl.php';
$ctx = page_require_access('expense_import');

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

$msg = '';
$msg_type = 'info';
$preview = $_SESSION['expense_preview'] ?? [];

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

/* ================= DATE PARSER ================= */
function parse_excel_date($value) {
    $value = trim((string)$value);
    if ($value === '') return null;

if (is_numeric($value)) {
        $unix = ((int)$value - 25569) * 86400;
        return date('Y-m-d', $unix);
    }

$value = str_replace('/', '-', $value);
    $ts = strtotime($value);
    return ($ts !== false) ? date('Y-m-d', $ts) : null;
}

/* ================= STEP 1: PREVIEW ================= */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['preview'])) {

unset($_SESSION['expense_preview']);

if (!isset($_FILES['file']) || $_FILES['file']['error'] == 4) {
        $msg = 'Please select a CSV file first.';
        $msg_type = 'danger';
    } else {

$tmp = $_FILES['file']['tmp_name'];
        $ext = strtolower(pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION));

if ($ext !== 'csv') {
            $msg = 'Invalid format. Only CSV files are allowed.';
            $msg_type = 'danger';
        } elseif (($fh = fopen($tmp, 'r')) !== false) {

fgetcsv($fh); // skip header
            $rows = [];

while (($row = fgetcsv($fh)) !== false) {

$sn  = trim($row[0] ?? '');
                $tid = trim($row[1] ?? '');
                $dt  = parse_excel_date($row[2] ?? '');

$rem_raw = $row[6] ?? '';
                $rem = trim(preg_replace('/\s+/', ' ', $rem_raw));

$amt = trim($row[7] ?? 0);

if ($rem === '' || stripos($rem, 'CMS/') === false) {
                    continue;
                }

$amt = (float)str_replace(',', '', $amt);

$rows[] = [
                    'sn'  => $sn,
                    'tid' => $tid,
                    'dt'  => $dt,
                    'rem' => $rem,
                    'amt' => $amt
                ];
            }

fclose($fh);

if ($rows) {
                $_SESSION['expense_preview'] = $rows;
                $preview = $rows;
                $msg = count($rows) . ' CMS transactions found. Please review below.';
                $msg_type = 'primary';
            } else {
                $msg = 'No CMS records found in the uploaded file.';
                $msg_type = 'warning';
            }
        }
    }
}

/* ================= STEP 2: IMPORT ================= */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['import'])) {

$data = $_SESSION['expense_preview'] ?? [];

if (!$data) {
        $msg = 'Preview data expired. Please upload again.';
        $msg_type = 'danger';
    } else {

$ins = $pdo->prepare("
            INSERT IGNORE INTO expense_report 
            (company_id, sn, tran_id, transaction_date, transaction_remarks, withdrawal_amt) 
            VALUES (:cid, :sn, :tid, :dt, :rem, :amt)
        ");

$inserted = 0;
        $skipped  = 0;

$pdo->beginTransaction();

try {

foreach ($data as $r) {

$ins->execute([
                    ':cid' => $company_id,
                    ':sn'  => $r['sn'] ?: null,
                    ':tid' => $r['tid'],
                    ':dt'  => $r['dt'],
                    ':rem' => trim($r['rem']),
                    ':amt' => $r['amt']
                ]);

if ($ins->rowCount() > 0) {
                    $inserted++;
                } else {
                    $skipped++;
                }
            }

$pdo->commit();

unset($_SESSION['expense_preview']);
            $preview = [];

$msg = "Import Completed: $inserted inserted, $skipped duplicates skipped.";
            $msg_type = 'success';

} catch (Exception $e) {

$pdo->rollBack();
            $msg = "Database Error: " . $e->getMessage();
            $msg_type = 'danger';
        }
    }
}

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

<?php if ($msg): ?>
<div class="alert alert-<?= $msg_type ?> alert-dismissible fade show shadow-sm mb-4">
    <?= h($msg) ?>
    <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
</div>
<?php endif; ?>

<div class="card border-0 shadow-sm mb-4">
<div class="card-header bg-primary text-white py-3">
    <h5 class="mb-0">Import Expense Report</h5>
</div>

<div class="col-md-7">
<input type="file" name="file" class="form-control form-control-lg" accept=".csv" required>
<div class="form-text mt-2 text-start">Upload bank statement CSV containing CMS transactions.</div>
</div>

<div class="col-md-3">
<button type="submit" name="preview" class="btn btn-primary btn-lg w-100">
Preview Data
</button>
</div>

</form>
</div>
</div>

<?php if ($preview): ?>
<div class="card border-0 shadow-sm">
<div class="card-header bg-dark text-white d-flex justify-content-between align-items-center py-3">
    <h5 class="mb-0 small text-uppercase fw-bold">CMS Transaction Preview</h5>
    <span class="badge bg-primary fs-6"><?= count($preview) ?> Rows</span>
</div>

<div class="table-responsive" style="max-height: 500px; overflow-y: auto;">
<table class="table table-hover align-middle mb-0">
<thead class="table-light sticky-top">
<tr>
<th>Date</th>
<th>Transaction ID</th>
<th>Remarks / CMS Reference</th>
<th class="text-end">Withdrawal Amount</th>
</tr>
</thead>
<tbody>

<?php foreach ($preview as $r): ?>
<tr>
<td><?= h($r['dt']) ?></td>
<td><?= h($r['tid']) ?></td>
<td class="text-break" style="max-width: 300px;"><?= h($r['rem']) ?></td>
<td class="text-end text-danger fw-bold">₹ <?= number_format($r['amt'], 2) ?></td>
</tr>
<?php endforeach; ?>

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

<div class="card-footer bg-light p-3 text-end">
<form method="post">
<button type="submit" name="import" class="btn btn-success btn-lg px-5">
Confirm & Import to DB
</button>
</form>
</div>
</div>
<?php endif; ?>

</div>
</div>
</div>

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