View Code - 20260721

cms_payment_filewise_report.php | 20260721_154032.php Initial Bulk Import

<?php
declare(strict_types=1);

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require_once __DIR__ . '/modules/auth/page_acl.php';

// Auth and Database extraction via $ctx pattern
$ctx = page_require_access('va_pending_report'); 
$pdo        = $ctx['pdo'];
$company_id = (int)$ctx['company_id'];

/* ==========================================================================
   1. LOAD MAPPING REFERENCE DATA (As per your account structure logic)
   ========================================================================== */

// A. Deleted Accounts
$deletedAccounts = [];
$stmt = $pdo->prepare("SELECT bank_ac_no FROM deleted_bank_ac WHERE company_id = :cid");
$stmt->execute([':cid' => $company_id]);
while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $acc = preg_replace('/\D/', '', (string)$r['bank_ac_no']);
    if ($acc !== '') { $deletedAccounts[$acc] = true; }
}

// B. Employee Bank Accounts
$employeeAccounts = [];
$stmt = $pdo->prepare("SELECT account_number, name, department_name FROM company_employee_master WHERE company_id = :cid");
$stmt->execute([':cid' => $company_id]);
while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $acc = preg_replace('/\D/', '', (string)$r['account_number']);
    if ($acc !== '') {
        $employeeAccounts[$acc] = ['name' => $r['name'], 'department' => $r['department_name']];
    }
}

$stmt = $pdo->prepare("SELECT beneficiary_ac_number, employee_name, department_name FROM employee_bank_data WHERE company_id = :cid");
$stmt->execute([':cid' => $company_id]);
while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $acc = preg_replace('/\D/', '', (string)$r['beneficiary_ac_number']);
    if ($acc !== '' && !isset($employeeAccounts[$acc])) {
        $employeeAccounts[$acc] = ['name' => $r['employee_name'], 'department' => $r['department_name']];
    }
}

// C. Contractor Employee Accounts
$contractorEmployees = [];
$stmt = $pdo->prepare("SELECT account_no, employee_name, department FROM contractor_employee WHERE cid = :cid");
$stmt->execute([':cid' => $company_id]);
while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $acc = preg_replace('/\D/', '', (string)$r['account_no']);
    if ($acc !== '') {
        $contractorEmployees[$acc] = ['name' => $r['employee_name'], 'department' => $r['department']];
    }
}

// D. Loom Karigar Accounts
$loomAccounts = [];
$stmt = $pdo->prepare("SELECT beneficiary_ac_number FROM loom_karigar_master WHERE company_id = :cid");
$stmt->execute([':cid' => $company_id]);
while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $acc = preg_replace('/\D/', '', (string)$r['beneficiary_ac_number']);
    if ($acc !== '') { $loomAccounts[$acc] = true; }
}

// E. Party Accounts
$partyAccounts = [];
$stmt = $pdo->prepare("SELECT account_no, party_name FROM party_ac_detail WHERE company_id = :cid");
$stmt->execute([':cid' => $company_id]);
while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $acc = preg_replace('/\D/', '', (string)$r['account_no']);
    if ($acc !== '') { $partyAccounts[$acc] = $r['party_name']; }
}

/* ==========================================================================
   2. DATE FILTERS & DATA FETCHING FROM FILEWISE TABLE
   ========================================================================== */
$from = $_GET['from_date'] ?? '';
$to   = $_GET['to_date'] ?? '';

$sql = "
    SELECT 
        id, file_name, serial_no, payment_instruction_date, instrument_ref_no,
        beneficiary_account_no, instrument_amount, pay_to, ifsc_code, status, processing_remarks
    FROM cms_payment_data_filewise
    WHERE company_id = :cid AND LOWER(status) = 'paid'
";

$params = [':cid' => $company_id];

if (!empty($from)) {
    $sql .= " AND STR_TO_DATE(payment_instruction_date, '%d-%b-%Y') >= STR_TO_DATE(:from_date, '%Y-%m-%d') ";
    $params[':from_date'] = $from;
}
if (!empty($to)) {
    $sql .= " AND STR_TO_DATE(payment_instruction_date, '%d-%b-%Y') <= STR_TO_DATE(:to_date, '%Y-%m-%d') ";
    $params[':to_date'] = $to;
}

$sql .= " ORDER BY file_name ASC, serial_no ASC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$raw_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* ==========================================================================
   3. DATA STRUCTURING & PRODUCTION DIVISION MATHS (Loom vs Rapier)
   ========================================================================= */
$filewise_groups = [];
$grand_total_amount = 0;
$grand_total_records = 0;

// Segregation totals for the separate custom block
$total_rapier_share = 0.00;
$total_loom_share   = 0.00;

foreach ($raw_rows as $row) {
    $filename = !empty($row['file_name']) ? $row['file_name'] : 'Unknown_File';
    $account  = preg_replace('/\D/', '', (string)$row['beneficiary_account_no']);
    
    if (isset($deletedAccounts[$account])) {
        $department  = 'Deleted Account';
        $displayName = !empty($row['pay_to']) ? $row['pay_to'] : 'N/A';
    } elseif (isset($employeeAccounts[$account])) {
        $department  = $employeeAccounts[$account]['department'];
        $displayName = $employeeAccounts[$account]['name'];
    } elseif (isset($contractorEmployees[$account])) {
        $department  = $contractorEmployees[$account]['department'];
        $displayName = $contractorEmployees[$account]['name'];
    } elseif (isset($loomAccounts[$account])) {
        $department  = 'Loom Karigar';
        $displayName = !empty($row['pay_to']) ? $row['pay_to'] : 'N/A';
    } elseif (isset($partyAccounts[$account])) {
        $department  = 'Party - ' . $partyAccounts[$account];
        $displayName = $partyAccounts[$account];
    } else {
        $department  = 'Unknown';
        $displayName = !empty($row['pay_to']) ? $row['pay_to'] : 'N/A';
    }

$amount = (float)$row['instrument_amount'];
    $dept_lower = strtolower(trim($department));

// Rule Logic Execution: Rapier and Folding 30% / 70% rule logic
    if ($dept_lower === 'rapier') {
        $total_rapier_share += $amount;
    } elseif ($dept_lower === 'folding') {
        $total_rapier_share += ($amount * 0.30);
        $total_loom_share   += ($amount * 0.70);
    } else {
        // All remaining structural departments are cataloged into the Loom category
        $total_loom_share += $amount;
    }

if (!isset($filewise_groups[$filename])) {
        $filewise_groups[$filename] = [
            'file_name'     => $filename,
            'total_amount'  => 0.00,
            'total_records' => 0,
            'items'         => []
        ];
    }

$filewise_groups[$filename]['items'][] = [
        'serial_no'        => $row['serial_no'],
        'date'             => $row['payment_instruction_date'],
        'ref_no'           => $row['instrument_ref_no'],
        'account_no'       => $row['beneficiary_account_no'],
        'ifsc'             => $row['ifsc_code'],
        'mapped_name'      => $displayName,
        'department_name'  => $department,
        'amount'           => $amount,
        'remarks'          => $row['processing_remarks']
    ];

$filewise_groups[$filename]['total_amount']  += $amount;
    $filewise_groups[$filename]['total_records'] ++;
    
    $grand_total_amount  += $amount;
    $grand_total_records ++;
}

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

<div class="container-fluid mt-4 px-4">
    <div class="d-flex justify-content-between align-items-center mb-3">
        <h4 class="text-dark fw-bold mb-0">
            <i class="fa fa-file-text-o me-2 text-primary"></i>CMS Filewise Payment & Department Report
        </h4>
        <button type="button" onclick="window.print()" class="btn btn-outline-dark btn-sm px-3 no-print shadow-sm">
            <i class="fa fa-print me-1"></i> Print Report
        </button>
    </div>

<div class="card border border-secondary-subtle shadow-sm mb-4 no-print">
        <div class="card-body bg-white rounded">
            <form method="GET" action="" class="row g-3 align-items-end">
                <div class="col-md-4">
                    <label class="small fw-bold text-muted text-uppercase mb-1 d-block">From Payment Instruction Date</label>
                    <input type="date" name="from_date" class="form-control form-control-sm border-secondary shadow-inner" value="<?= htmlspecialchars($from) ?>">
                </div>
                <div class="col-md-4">
                    <label class="small fw-bold text-muted text-uppercase mb-1 d-block">To Payment Instruction Date</label>
                    <input type="date" name="to_date" class="form-control form-control-sm border-secondary shadow-inner" value="<?= htmlspecialchars($to) ?>">
                </div>
                <div class="col-md-4 d-flex gap-2">
                    <button type="submit" class="btn btn-primary btn-sm px-4 fw-bold shadow-sm w-50">
                        <i class="fa fa-filter me-1"></i> Filter Matrix
                    </button>
                    <a href="cms_payment_filewise_report.php" class="btn btn-outline-secondary btn-sm px-4 fw-bold shadow-sm w-50 text-center">
                        <i class="fa fa-refresh me-1"></i> Reset View
                    </a>
                </div>
            </form>
        </div>
    </div>

<?php if (!empty($filewise_groups)): ?>
    <div class="card border border-secondary shadow-sm mb-4">
        <div class="card-header bg-light border-bottom border-secondary text-dark py-3">
            <h5 class="mb-0 fw-bold"><i class="fa fa-pie-chart text-primary me-2"></i>Production Share Distribution Metrics (Total Analysis)</h5>
        </div>
        <div class="table-responsive">
            <table class="table table-bordered align-middle mb-0 text-center">
                <thead class="table-light small text-uppercase fw-bold border-bottom">
                    <tr>
                        <th style="width: 33.33%;">Grand Total Amount</th>
                        <th style="width: 33.33%;" class="text-primary">Rapier Share Total <span class="text-muted lowercase font-monospace small d-block">(Rapier + 30% Folding)</span></th>
                        <th style="width: 33.33%;" class="text-success">Loom Share Total <span class="text-muted lowercase font-monospace small d-block">(Loom + 70% Folding)</span></th>
                    </tr>
                </thead>
                <tbody class="fw-bold fs-5">
                    <tr>
                        <td>&#8377; <?= number_format($grand_total_amount, 2) ?></td>
                        <td class="text-primary">&#8377; <?= number_format($total_rapier_share, 2) ?></td>
                        <td class="text-success">&#8377; <?= number_format($total_loom_share, 2) ?></td>
                    </tr>
                </tbody>
            </table>
        </div>
    </div>
    <?php endif; ?>

<div class="row g-3 mb-4 text-uppercase fw-bold text-center">
        <div class="col-md-6">
            <div class="card border border-success-subtle bg-white border-start border-success border-3 shadow-sm py-2">
                <div class="text-success small mb-0">Grand Aggregated Disbursed Volume</div>
                <div class="fs-4 text-dark">&#8377; <?= number_format($grand_total_amount, 2) ?></div>
            </div>
        </div>
        <div class="col-md-6">
            <div class="card border border-primary-subtle bg-white border-start border-primary border-3 shadow-sm py-2">
                <div class="text-primary small mb-0">Grand Total Processed Records</div>
                <div class="fs-4 text-dark"><?= number_format($grand_total_records) ?> Rows</div>
            </div>
        </div>
    </div>

<?php if (empty($filewise_groups)): ?>
        <div class="alert alert-warning border-0 shadow-sm text-center py-4">
            <i class="fa fa-info-circle fa-2x d-block mb-2"></i> No transaction logs or uploaded files discovered within specified filter parameters.
        </div>
    <?php else: ?>
        <?php $file_idx = 1; ?>
        <?php foreach ($filewise_groups as $group): ?>
            <div class="card shadow-sm border border-secondary-subtle mb-4 card-file-block">
                <div class="card-header bg-light border-bottom border-secondary-subtle text-dark py-3 d-flex flex-wrap justify-content-between align-items-center gap-2">
                    <div>
                        <span class="badge bg-white text-dark border border-secondary me-2 px-2 py-1">FILE #<?= $file_idx++ ?></span>
                        <strong class="fs-6 word-break-all"><i class="fa fa-database text-primary me-1"></i> <?= htmlspecialchars($group['file_name']) ?></strong>
                    </div>
                    <div class="d-flex gap-3 text-nowrap align-items-center">
                        <span class="bg-white border text-dark rounded px-3 py-1 small fw-bold">
                            Total Records: <span class="text-primary"><?= $group['total_records'] ?></span>
                        </span>
                        <span class="bg-white border text-dark rounded px-3 py-1 small fw-bold">
                            Total Amount: <span class="text-success">&#8377; <?= number_format($group['total_amount'], 2) ?></span>
                        </span>
                    </div>
                </div>

<div class="table-responsive">
                    <table class="table table-sm table-bordered table-hover align-middle mb-0">
                        <thead class="table-light text-secondary small text-uppercase fw-bold">
                            <tr>
                                <th style="width: 5%;" class="text-center">Sr. No.</th>
                                <th style="width: 10%;">Value Date</th>
                                <th style="width: 15%;">Reference No.</th>
                                <th style="width: 25%;">Beneficiary Details / Party</th>
                                <th style="width: 15%;">Department Category</th>
                                <th style="width: 12%;" class="text-end">Amount Paid</th>
                                <th style="width: 18%;">Processing Remarks</th>
                            </tr>
                        </thead>
                        <tbody class="small">
                            <?php foreach ($group['items'] as $item): ?>
                                <?php 
                                    $bg_class = '';
                                    if ($item['department_name'] === 'Unknown') { $bg_class = 'table-danger-subtle'; }
                                    elseif ($item['department_name'] === 'Deleted Account') { $bg_class = 'table-warning-subtle'; }
                                ?>
                                <tr class="<?= $bg_class ?>">
                                    <td class="text-center fw-bold text-muted"><?= htmlspecialchars((string)$item['serial_no']) ?></td>
                                    <td><?= htmlspecialchars((string)$item['date']) ?></td>
                                    <td class="text-nowrap text-secondary"><?= htmlspecialchars((string)$item['ref_no']) ?></td>
                                    <td>
                                        <div class="fw-bold text-dark"><?= htmlspecialchars((string)$item['mapped_name']) ?></div>
                                        <div class="text-muted extra-small font-monospace">A/C: <?= htmlspecialchars((string)$item['account_no']) ?> | IFSC: <?= htmlspecialchars((string)$item['ifsc']) ?></div>
                                    </td>
                                    <td>
                                        <?php if ($item['department_name'] === 'Unknown'): ?>
                                            <span class="text-danger fw-bold border border-danger rounded px-1" style="font-size:0.75rem;">Unknown</span>
                                        <?php elseif ($item['department_name'] === 'Deleted Account'): ?>
                                            <span class="text-warning fw-bold border border-warning rounded px-1" style="font-size:0.75rem;">Deleted</span>
                                        <?php else: ?>
                                            <span class="text-dark fw-semibold"><?= htmlspecialchars((string)$item['department_name']) ?></span>
                                        <?php endif; ?>
                                    </td>
                                    <td class="text-end fw-bold text-success">&#8377;<?= number_format($item['amount'], 2) ?></td>
                                    <td class="text-muted cell-remarks"><?= htmlspecialchars((string)$item['remarks']) ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        <?php endforeach; ?>
    <?php endif; ?>
</div>

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