View Code - 20260721

cms_payment_importer.php | 20260721_154032.php Initial Bulk Import

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

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

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['csv_file'])) {
    $fileTmpPath = $_FILES['csv_file']['tmp_name'];
    
    if (is_uploaded_file($fileTmpPath)) {
        if (($handle = fopen($fileTmpPath, "r")) !== FALSE) {
            $records = [];
            $current_record = null;

// Step 1: Multi-line wrapped cell blocks ko track karke single data row mein merge karna
            while (($row = fgetcsv($handle, 10000, ",")) !== FALSE) {
                if (empty($row) || count($row) < 2) {
                    continue;
                }

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

// Metadata aur Layout Headers bypass karein
                if ($serial === 'Serial No' || strpos($serial, 'MAA SHARDA') !== false || strpos($serial, 'Page No') !== false) {
                    continue;
                }

// Naya valid entry pattern check
                if (is_numeric($serial)) {
                    if ($current_record !== null) {
                        $records[] = $current_record;
                    }
                    $current_record = array_map('trim', $row);
                } 
                // Agar main cell multi-line string contents ke sath nested row break karta hai
                elseif ($current_record !== null) {
                    for ($i = 0; $i < count($row); $i++) {
                        if (isset($row[$i]) && trim($row[$i]) !== '') {
                            $val = trim($row[$i]);
                            if (isset($current_record[$i]) && $current_record[$i] !== '') {
                                $current_record[$i] .= " " . $val;
                            } else {
                                $current_record[$i] = $val;
                            }
                        }
                    }
                }
            }

// Target loop block ke bache hue balance rows collect karein
            if ($current_record !== null) {
                $records[] = $current_record;
            }
            fclose($handle);

// Step 2: Extract unique tracking elements aur status evaluation filter ('Paid' only)
            $paid_records = [];
            $unique_files = [];

foreach ($records as $rec) {
                $status = isset($rec[13]) ? strtolower(trim($rec[13])) : '';
                if ($status === 'paid') {
                    // Raw string lines se return carriages clean karein
                    foreach ($rec as $key => $value) {
                        $rec[$key] = str_replace(["\r", "\n"], " ", $value);
                    }
                    
                    // Core banking formatting structures clean karein
                    if (isset($rec[11])) {
                        $rec[11] = str_replace(' ', '', $rec[11]);
                    }
                    if (isset($rec[2])) {
                        $rec[2] = trim(str_replace(' ', '', $rec[2]));
                    }

$paid_records[] = $rec;
                    
                    if (!empty($rec[2])) {
                        $unique_files[$rec[2]] = true;
                    }
                }
            }

// Step 3: Delete-then-Insert transaction pipeline framework execute karna
            if (!empty($paid_records)) {
                try {
                    $pdo->beginTransaction();

// --- Delete-then-Insert Strategy with Company Segregation Constraint ---
                    if (!empty($unique_files)) {
                        $files_array = array_keys($unique_files);
                        $placeholders = implode(',', array_fill(0, count($files_array), '?'));
                        
                        // Delete query matching target company context parameters
                        $delete_sql = "DELETE FROM cms_payment_data_filewise WHERE company_id = ? AND file_name IN ($placeholders)";
                        $delete_stmt = $pdo->prepare($delete_sql);
                        
                        // Bind Company ID at first element slot position dynamically
                        $execute_params = array_merge([$company_id], $files_array);
                        $delete_stmt->execute($execute_params);
                    }

// Fresh insert processing framework
                    $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) {
                        $insert_stmt->execute([
                            $company_id,
                            isset($rec[0]) ? (int)$rec[0] : 0,
                            isset($rec[1]) ? $rec[1] : null,
                            isset($rec[2]) ? $rec[2] : null,
                            isset($rec[3]) ? $rec[3] : null,
                            isset($rec[4]) ? $rec[4] : null,
                            isset($rec[5]) ? $rec[5] : null,
                            isset($rec[7]) ? (float)str_replace(',', '', $rec[7]) : 0.00,
                            isset($rec[8]) ? $rec[8] : null,
                            isset($rec[9]) ? $rec[9] : null,
                            isset($rec[11]) ? $rec[11] : null,
                            isset($rec[12]) ? $rec[12] : null,
                            isset($rec[13]) ? $rec[13] : null,
                            isset($rec[14]) ? $rec[14] : null,
                            isset($rec[16]) ? $rec[16] : null,
                            isset($rec[18]) ? $rec[18] : null
                        ]);
                    }

$pdo->commit();
                    $message = "Successfully uploaded and processed " . count($paid_records) . " records with 'Paid' status.";
                } catch (Exception $e) {
                    $pdo->rollBack();
                    $error = "Data Integrity Error / Rollback Activated: " . $e->getMessage();
                }
            } else {
                $message = "No matching items with 'Paid' status flags discovered inside CSV.";
            }
        } else {
            $error = "System pipeline failed opening target file buffer stream.";
        }
    } else {
        $error = "Invalid transaction attempt. Please check uploaded dataset.";
    }
}

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-cloud-upload text-success"></i> CMS Payment Interface (Filewise 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="csv_file" class="small fw-bold text-muted text-uppercase d-block mb-2">Select Master CMS CSV File Matrix</label>
                    <input type="file" name="csv_file" id="csv_file" class="form-control form-control-sm border-secondary shadow-inner" accept=".csv" required>
                    <div class="form-text text-dark-50 small mt-1">
                        <i class="fa fa-info-circle text-primary"></i> <strong>Rule Enforced:</strong> Only rows containing standard <strong>'Paid'</strong> verification values are processed. Same file updates will safely execute full overwrite blocks.
                    </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 Transactions
                    </button>
                </div>
            </form>
        </div>
    </div>
</div>

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