View Code - 20260920

cms_payment_importer.php | 20260920_164915.php Initial Domain Snapshot

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

// Auth & Context Loader
require_once __DIR__ . '/modules/auth/page_acl.php';
$ctx = page_require_access('va_pending_report'); 
$pdo = $ctx['pdo']; 
$company_id = (int)$ctx['company_id'];

// Make sure PhpSpreadsheet is available via Composer autoloager
require_once __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$message = '';
$error = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel_file'])) {
    $fileTmpPath = $_FILES['excel_file']['tmp_name'];
    
    if (is_uploaded_file($fileTmpPath)) {
        try {
            // Load Excel file directly using PhpSpreadsheet
            $spreadsheet = IOFactory::load($fileTmpPath);
            $worksheet = $spreadsheet->getActiveSheet();
            
            $records = [];
            $current_record = null;
            $header_map = null;

$get_col = function (array $rec, string $name, $default = null) use (&$header_map) {
                if ($header_map === null || !isset($header_map[$name]) || !isset($rec[$header_map[$name]])) {
                    return $default;
                }
                return $rec[$header_map[$name]];
            };

$normalize_payment_date = function (?string $raw): ?string {
                if ($raw === null || trim($raw) === '') {
                    return $raw;
                }
                $raw = trim($raw);
                $formats = ['d-M-Y', 'd-m-y', 'd-m-Y', 'd-M-y', 'd/m/Y', 'd/m/y', 'Y-m-d'];
                foreach ($formats as $fmt) {
                    $dt = DateTime::createFromFormat($fmt, $raw);
                    if ($dt !== false) {
                        $errors = DateTime::getLastErrors();
                        if (empty($errors['warning_count']) && empty($errors['error_count'])) {
                            return $dt->format('d-M-Y');
                        }
                    }
                }
                return $raw;
            };

// Read rows directly from Excel worksheet cells
            foreach ($worksheet->getRowIterator() as $row) {
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(false);
                
                $row_data = [];
                foreach ($cellIterator as $cell) {
                    // getFormattedValue ensures leading zeros and exact account digits are preserved
                    $row_data[] = trim((string)$cell->getFormattedValue());
                }

if (empty($row_data) || count($row_data) < 2) {
                    continue;
                }

$serial = trim($row_data[0]);

if ($serial === 'Serial No') {
                    if ($header_map === null) {
                        $header_map = [];
                        foreach ($row_data as $idx => $col_name) {
                            $col_name = trim($col_name);
                            if ($col_name !== '') {
                                $header_map[$col_name] = $idx;
                            }
                        }
                    }
                    continue;
                }

if (strpos($serial, 'MAA SHARDA') !== false || strpos($serial, 'Page No') !== false) {
                    continue;
                }

if (is_numeric($serial)) {
                    if ($current_record !== null) {
                        $records[] = $current_record;
                    }
                    $current_record = $row_data;
                } 
                elseif ($current_record !== null) {
                    for ($i = 0; $i < count($row_data); $i++) {
                        if (isset($row_data[$i]) && $row_data[$i] !== '') {
                            $val = $row_data[$i];
                            if (isset($current_record[$i]) && $current_record[$i] !== '') {
                                $current_record[$i] .= " " . $val;
                            } else {
                                $current_record[$i] = $val;
                            }
                        }
                    }
                }
            }

if ($current_record !== null) {
                $records[] = $current_record;
            }

$paid_records = [];
            $unique_files = [];

foreach ($records as $rec) {
                $status = strtolower(trim($get_col($rec, 'Status', '')));
                if ($status === 'paid') {
                    foreach ($rec as $key => $value) {
                        $rec[$key] = str_replace(["\r", "\n"], " ", $value);
                    }
                    
                    if ($header_map !== null && isset($header_map['IFSC Code'], $rec[$header_map['IFSC Code']])) {
                        $rec[$header_map['IFSC Code']] = str_replace(' ', '', $rec[$header_map['IFSC Code']]);
                    }
                    if ($header_map !== null && isset($header_map['File Name'], $rec[$header_map['File Name']])) {
                        $rec[$header_map['File Name']] = trim(str_replace(' ', '', $rec[$header_map['File Name']]));
                    }

$paid_records[] = $rec;

$file_name_val = $get_col($rec, 'File Name');
                    if (!empty($file_name_val)) {
                        $unique_files[$file_name_val] = true;
                    }
                }
            }

if (!empty($paid_records)) {
                $pdo->beginTransaction();

if (!empty($unique_files)) {
                    $files_array = array_keys($unique_files);
                    $placeholders = implode(',', array_fill(0, count($files_array), '?'));
                    
                    $delete_sql = "DELETE FROM cms_payment_data_filewise WHERE company_id = ? AND file_name IN ($placeholders)";
                    $delete_stmt = $pdo->prepare($delete_sql);
                    
                    $execute_params = array_merge([$company_id], $files_array);
                    $delete_stmt->execute($execute_params);
                }

$insert_sql = "INSERT INTO cms_payment_data_filewise (
                    company_id, serial_no, payment_product_code, file_name, payment_instruction_date, 
                    instrument_ref_no, customer_account_no, instrument_amount, pay_to, 
                    beneficiary_account_no, ifsc_code, beneficiary_mailing_address1, status, 
                    processing_remarks, old_product_code, common_ref_no
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                
                $insert_stmt = $pdo->prepare($insert_sql);

foreach ($paid_records as $rec) {
                    $amount_raw = $get_col($rec, 'Instrument Amount');
                    
                    // Exact string values for account numbers directly from Excel cells
                    $beneficiary_acc = trim((string)$get_col($rec, 'Beneficiary Account No', ''));
                    $customer_acc    = trim((string)$get_col($rec, 'Customer Account No', ''));

$insert_stmt->execute([
                        $company_id,
                        (int)$get_col($rec, 'Serial No', 0),
                        $get_col($rec, 'Payment Product Code'),
                        $get_col($rec, 'File Name'),
                        $normalize_payment_date($get_col($rec, 'Payment Instruction Date')),
                        $get_col($rec, 'Instrument Ref No'),
                        $customer_acc,
                        $amount_raw !== null ? (float)str_replace(',', '', $amount_raw) : 0.00,
                        $get_col($rec, 'Pay To'),
                        $beneficiary_acc,
                        $get_col($rec, 'IFSC Code'),
                        $get_col($rec, 'Beneficairy Mailing Address1'),
                        $get_col($rec, 'Status'),
                        $get_col($rec, 'Processing Remarks'),
                        $get_col($rec, 'Old Product Code'),
                        $get_col($rec, 'Common Ref No')
                    ]);
                }

$pdo->commit();
                $message = "Successfully uploaded and processed " . count($paid_records) . " records from Excel with 'Paid' status.";
            } else {
                $message = "No matching items with 'Paid' status flags discovered inside the Excel file.";
            }

} catch (Exception $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            $error = "Excel Processing / Data Integrity Error: " . $e->getMessage();
        }
    } else {
        $error = "Invalid file upload attempt.";
    }
}

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

<div class="container-fluid mt-4 px-4">
    <div class="card shadow-sm border-0 mb-4">
        <div class="card-header bg-dark text-white py-3">
            <h5 class="mb-0 fw-bold"><i class="fa fa-file-excel-o text-success"></i> CMS Payment Interface (Direct Excel Engine)</h5>
        </div>
        <div class="card-body bg-light rounded-bottom">
            
            <?php if (!empty($message)): ?>
                <div class="alert alert-success border-0 shadow-sm alert-dismissible fade show" role="alert">
                    <i class="fa fa-check-circle me-2"></i> <?= htmlspecialchars($message) ?>
                    <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                </div>
            <?php endif; ?>
            
            <?php if (!empty($error)): ?>
                <div class="alert alert-danger border-0 shadow-sm alert-dismissible fade show" role="alert">
                    <i class="fa fa-exclamation-triangle me-2"></i> <?= htmlspecialchars($error) ?>
                    <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                </div>
            <?php endif; ?>

<form action="" method="POST" enctype="multipart/form-data" class="row g-3 align-items-end">
                <div class="col-md-8">
                    <label for="excel_file" class="small fw-bold text-muted text-uppercase d-block mb-2">Select Master CMS Excel File (.xlsx / .xls)</label>
                    <input type="file" name="excel_file" id="excel_file" class="form-control form-control-sm border-secondary shadow-inner" accept=".xlsx, .xls" required>
                    <div class="form-text text-dark-50 small mt-1">
                        <i class="fa fa-info-circle text-primary"></i> <strong>Direct Excel Reader:</strong> Leading zeros and account numbers are safely preserved without truncation. Only <strong>'Paid'</strong> status rows are processed.
                    </div>
                </div>
                <div class="col-md-4 text-end">
                    <button type="submit" class="btn btn-success btn-sm px-4 fw-bold shadow-sm w-100">
                        <i class="fa fa-gears me-1"></i> Parse & Commit Excel Data
                    </button>
                </div>
            </form>
        </div>
    </div>
</div>

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