View Code - 20260722

bank_icici_import.php | 20260722_120325.php Initial Domain Snapshot

<?php
require_once __DIR__ . '/modules/auth/page_acl.php';
$ctx = page_require_access('bank_icici_import');
$pdo = $ctx['pdo'];
$company_id = (int)$ctx['company_id'];
$u = $ctx['user'] ?? null;
require_once __DIR__ . '/helpers/activity_helper.php';

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$msg = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['file'])) {

$file = $_FILES['file']['tmp_name'];

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

if (count($rows) < 2) {
            throw new Exception("Empty or invalid file.");
        }

// Header detect (row 6 or 7 usually)
        $header = [];
        foreach ($rows as $i => $row) {
            if (in_array('Beneficiary Name', $row)) {
                $header = $row;
                $startRow = $i + 1;
                break;
            }
        }

if (empty($header)) {
            throw new Exception("Header not found.");
        }

// Map columns
        $map = [];
        foreach ($header as $k => $col) {
            $col = trim((string)$col);

if (in_array($col, ['Debit A/c', 'Debit Ac No', 'Debit Account No'], true)) $map['debit_ac_no'] = $k;
            if ($col === 'Beneficiary A/c No') $map['beneficiary_ac_no'] = $k;
            if ($col === 'Beneficiary Name') $map['beneficiary_name'] = $k;
            if ($col === 'Amount') $map['amount'] = $k;
            if ($col === 'Payment Mode') $map['payment_mode'] = $k;
            if ($col === 'Date') $map['txn_date'] = $k;
            if ($col === 'IFSC Code') $map['ifsc_code'] = $k;
            if ($col === 'Payable Location Name') $map['payable_location'] = $k;
            if ($col === 'Remarks') $map['remarks'] = $k;
            if (in_array($col, ['Payment Ref', 'Payment Reference', 'Payment Ref No'], true)) $map['payment_ref'] = $k;
            if ($col === 'Status') $map['status'] = $k;
            if (in_array($col, ['Liquidation', 'Liquidation Date'], true)) $map['liquidation_date'] = $k;
            if ($col === 'Customer Ref No') $map['customer_ref_no'] = $k;
            if ($col === 'Instrument Ref No') $map['instrument_ref_no'] = $k;
            if ($col === 'UTR NO') $map['utr_no'] = $k;
        }

$cell = function(array $row, array $map, string $key) {
            if (!isset($map[$key])) return null;
            $idx = $map[$key];
            return array_key_exists($idx, $row) ? $row[$idx] : null;
        };

$stmt = $pdo->prepare("
            INSERT INTO bank_icici_import (
                company_id,
                debit_ac_no,
                beneficiary_ac_no,
                beneficiary_name,
                amount,
                payment_mode,
                txn_date,
                ifsc_code,
                payable_location,
                remarks,
                payment_ref,
                status,
                liquidation_date,
                customer_ref_no,
                instrument_ref_no,
                utr_no
            ) VALUES (
                :company_id,
                :debit_ac_no,
                :beneficiary_ac_no,
                :beneficiary_name,
                :amount,
                :payment_mode,
                :txn_date,
                :ifsc_code,
                :payable_location,
                :remarks,
                :payment_ref,
                :status,
                :liquidation_date,
                :customer_ref_no,
                :instrument_ref_no,
                :utr_no
            )
        ");

$inserted = 0;

for ($i = $startRow; $i < count($rows); $i++) {
            $r = $rows[$i];

$beneficiaryName = trim((string)($cell($r, $map, 'beneficiary_name') ?? ''));
            if ($beneficiaryName === '') continue;

// Date convert
            $txnRaw = $cell($r, $map, 'txn_date');
            $liqRaw = $cell($r, $map, 'liquidation_date');

$date = !empty($txnRaw)
                ? date('Y-m-d', strtotime((string)$txnRaw))
                : null;

$liq = !empty($liqRaw)
                ? date('Y-m-d', strtotime((string)$liqRaw))
                : null;

$stmt->execute([
                ':company_id' => $company_id,
                ':debit_ac_no' => $cell($r, $map, 'debit_ac_no'),
                ':beneficiary_ac_no' => $cell($r, $map, 'beneficiary_ac_no'),
                ':beneficiary_name' => $beneficiaryName,
                ':amount' => $cell($r, $map, 'amount') ?? 0,
                ':payment_mode' => $cell($r, $map, 'payment_mode'),
                ':txn_date' => $date,
                ':ifsc_code' => $cell($r, $map, 'ifsc_code'),
                ':payable_location' => $cell($r, $map, 'payable_location'),
                ':remarks' => $cell($r, $map, 'remarks'),
                ':payment_ref' => $cell($r, $map, 'payment_ref'),
                ':status' => $cell($r, $map, 'status'),
                ':liquidation_date' => $liq,
                ':customer_ref_no' => $cell($r, $map, 'customer_ref_no'),
                ':instrument_ref_no' => $cell($r, $map, 'instrument_ref_no'),
                ':utr_no' => $cell($r, $map, 'utr_no'),
            ]);

$inserted++;
        }

$u = $ctx['user']; // Required by activity helper
        log_activity([
            'company_id'  => $company_id,
            'user_id'     => $u['id'] ?? 0,
            'module'      => 'bank',
            'page'        => basename(__FILE__),
            'action'      => 'CREATE',
            'entity_type' => 'import',
            'entity_id'   => 0,
            'description' => "Imported {$inserted} rows from ICICI Bank Excel"
        ]);
        $msg = "Imported: $inserted rows";

} catch (Exception $e) {
        $msg = "Error: " . $e->getMessage();
    }
}
?>

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

<div class="card p-3">
    <h3>ICICI Bank Excel Import</h3>

<?php if ($msg): ?>
        <div class="alert alert-info"><?= htmlspecialchars($msg) ?></div>
    <?php endif; ?>

<form method="post" enctype="multipart/form-data">
        <input type="file" name="file" required class="form-control mb-2">
        <button type="submit" class="btn btn-primary">Import</button>
    </form>
</div>

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