View Code - 20260723

cms_payment_detail.php | 20260723_000646.php Initial Domain Snapshot

<?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';
require_once __DIR__ . '/modules/activity/activity_logger.php';

$ctx = page_require_access('cms_payment_report');

$pdo        = $ctx['pdo'];
$company_id = (int)$ctx['company_id'];
$user_id    = (int)($ctx['user']['id'] ?? 0);

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

/* ==========================================
   1. BULK UPDATE & SAVE LOGIC
========================================== */
$toast = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_bulk'])) {
    $updated_rows = 0;

foreach ($_POST['rows'] ?? [] as $ref_no => $row) {
        $remark = trim($row['remark'] ?? '');
        $rapier = (float)($row['rapier_share'] ?? 0);
        $ref_no = trim((string)$ref_no);

if ($remark !== '') {
            $pdo->prepare("
                INSERT IGNORE INTO expense_remark_master (company_id, remark)
                VALUES (?, ?)
            ")->execute([$company_id, $remark]);
        }

try {
            $stmtUpdate = $pdo->prepare("
                UPDATE CMS_payment_detail
                SET remark = ?, rapier_share = ?
                WHERE payment_ref_no = ? AND company_id = ?
            ");
            $stmtUpdate->execute([$remark, $rapier, $ref_no, $company_id]);
            $updated_rows++;
        } catch (PDOException $e) {
            // Safe handling if columns missing
        }
    }

if ($updated_rows > 0) {
        activity_log([
            'company_id'  => $company_id,
            'user_id'     => $user_id,
            'module'      => 'cms_payment',
            'action_name' => 'edit',
            'entity_type' => 'cms_payment_detail',
            'entity_id'   => 0,
            'remarks'     => 'Updated CMS payment breakdown rows: ' . $updated_rows
        ]);
        $toast = 'All changes saved successfully!';
    }
}

/* ==========================================
   2. FILTERS (Date Setup)
========================================== */
$from = $_GET['from_date'] ?? '';
$to   = $_GET['to_date'] ?? '';

/* ==========================================
   3. MASTER LOOKUPS (For Identification)
========================================== */

// 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 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'], 'dept' => $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'], 'dept' => $r['department_name']];
    }
}

// C. Contractor Employees
$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'], 'dept' => $r['department']];
    }
}

// D. Loom Karigar
$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'];
}

// F. Remarks Master Datalist
$rq = $pdo->prepare("SELECT remark FROM expense_remark_master WHERE company_id = ? ORDER BY remark");
$rq->execute([$company_id]);
$remarkList = $rq->fetchAll(PDO::FETCH_COLUMN);

/* ==========================================
   4. DATA FETCHING & BREAKDOWN PROCESSING
========================================== */
$sql = "
    SELECT
        beneficiary_account_no,
        amount,
        payment_date,
        beneficiary_name,
        payment_ref_no
    FROM CMS_payment_detail
    WHERE company_id = :cid
";

$params = [':cid' => $company_id];
if ($from) { $sql .= " AND payment_date >= :from_date "; $params[':from_date'] = $from; }
if ($to) { $sql .= " AND payment_date <= :to_date "; $params[':to_date'] = $to; }
$sql .= " ORDER BY payment_date ASC ";

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

// Is array me hum account wise total club karenge (Breakup Report logic)
$accountBreakdown = [];
$grandTotalStatement = 0.0;

foreach ($allRows as $row) {
    $rawAccount = preg_replace('/\D/', '', (string)$row['beneficiary_account_no']);
    $amount = (float)$row['amount'];
    $grandTotalStatement += $amount;

// Identify Name & Category
    $category = 'Unknown Account';
    $realName = $row['beneficiary_name'];

if (!$rawAccount) {
        $category = 'Invalid Account';
    } elseif (isset($deletedAccounts[$rawAccount])) {
        $category = 'Deleted Account';
    } elseif (isset($employeeAccounts[$rawAccount])) {
        $category = 'Employee (' . $employeeAccounts[$rawAccount]['dept'] . ')';
        $realName = $employeeAccounts[$rawAccount]['name'];
    } elseif (isset($contractorEmployees[$rawAccount])) {
        $category = 'Contractor Emp (' . $contractorEmployees[$rawAccount]['dept'] . ')';
        $realName = $contractorEmployees[$rawAccount]['name'];
    } elseif (isset($loomAccounts[$rawAccount])) {
        $category = 'Loom Karigar';
    } elseif (isset($partyAccounts[$rawAccount])) {
        $category = 'Party';
        $realName = $partyAccounts[$rawAccount];
    }

// Unique key combination for breakdown matching
    $breakdownKey = $rawAccount . '_' . $realName;

if (!isset($accountBreakdown[$breakdownKey])) {
        $accountBreakdown[$breakdownKey] = [
            'account_no' => $row['beneficiary_account_no'],
            'name'       => $realName,
            'category'   => $category,
            'total_amt'  => 0.0,
            'tx_count'   => 0
        ];
    }

$accountBreakdown[$breakdownKey]['total_amt'] += $amount;
    $accountBreakdown[$breakdownKey]['tx_count']++;
}

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

<div class="d-flex justify-content-between align-items-center mb-3">
        <div>
            <h3 class="text-dark mb-0">Bank Statement Breakup & Identification Report</h3>
            <p class="text-muted">Bank se kate total amount ka account-wise aur person-wise partition.</p>
        </div>
        <div>
            <button type="button" onclick="window.print()" class="btn btn-dark shadow-sm">
                Print Report
            </button>
        </div>
    </div>

<?php if ($toast): ?>
        <div class="alert alert-success"><?= h($toast) ?></div>
    <?php endif; ?>

<div class="card shadow-sm mb-4">
        <div class="card-body">
            <form method="GET" class="row g-3 align-items-end">
                <div class="col-md-4">
                    <label class="form-label fw-bold">From Date</label>
                    <input type="date" name="from_date" class="form-control" value="<?= h($from) ?>">
                </div>
                <div class="col-md-4">
                    <label class="form-label fw-bold">To Date</label>
                    <input type="date" name="to_date" class="form-control" value="<?= h($to) ?>">
                </div>
                <div class="col-md-4">
                    <button class="btn btn-primary px-4 me-2">Get Breakup</button>
                    <a href="cms_payment_detail.php" class="btn btn-secondary">Reset</a>
                </div>
            </form>
        </div>
    </div>

<div class="alert alert-info d-flex justify-content-between align-items-center shadow-sm py-3 mb-4">
        <h5 class="mb-0 text-uppercase fw-bold text-dark-emphasis">
            Total Bank Statement Deduction:
        </h5>
        <h3 class="mb-0 fw-bold text-primary">
            ₹ <?= number_format($grandTotalStatement, 2) ?>
        </h3>
    </div>

<div class="card shadow-sm mb-5">
        <div class="card-header bg-dark text-white fw-bold">
            1. Identified Accounts & Distributed Breakup List
        </div>
        <div class="table-responsive">
            <table class="table table-bordered table-striped table-hover align-middle mb-0">
                <thead class="table-light text-uppercase small">
                    <tr>
                        <th style="width: 60px;">#</th>
                        <th>Identified Person/Party Name</th>
                        <th>Account Number</th>
                        <th>Category Type</th>
                        <th class="text-center">Total Entries</th>
                        <th class="text-end text-danger" style="width: 220px;">Distributed Amount</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($accountBreakdown)): ?>
                        <tr>
                            <td colspan="6" class="text-center text-muted py-4">Koi data ya breakdown available nahi hai.</td>
                        </tr>
                    <?php else: ?>
                        <?php $bIdx = 1; foreach ($accountBreakdown as $bKey => $bData): ?>
                            <tr>
                                <td><?= $bIdx++ ?></td>
                                <td class="fw-bold text-dark"><?= h($bData['name']) ?></td>
                                <td><code><?= h($bData['account_no']) ?></code></td>
                                <td>
                                    <span class="badge bg-secondary-subtle text-secondary-emphasis border px-2 py-1">
                                        <?= h($bData['category']) ?>
                                    </span>
                                </td>
                                <td class="text-center"><?= $bData['tx_count'] ?></td>
                                <td class="text-end fw-bold text-danger fs-5">
                                    ₹ <?= number_format($bData['total_amt'], 2) ?>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </tbody>
                <tfoot>
                    <tr class="table-dark fw-bold text-end">
                        <td colspan="5">Total Breakdown Distributed:</td>
                        <td class="fs-5">₹ <?= number_format($grandTotalStatement, 2) ?></td>
                    </tr>
                </tfoot>
            </table>
        </div>
    </div>

<form method="POST">
        <div class="card shadow-sm">
            <div class="card-header bg-secondary text-white fw-bold">
                2. Statement Detailed Transaction Logs (With Remark/Rapier Share Input)
            </div>
            <div class="table-responsive">
                <table class="table table-bordered table-hover align-middle mb-0">
                    <thead class="table-light text-uppercase small">
                        <tr>
                            <th>#</th>
                            <th>Date</th>
                            <th>Ref Number</th>
                            <th>Statement Beneficiary Name</th>
                            <th>Account No.</th>
                            <th class="text-end">Amount</th>
                            <th>Custom Remark</th>
                            <th class="text-end">Rapier Share</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($allRows)): ?>
                            <tr>
                                <td colspan="8" class="text-center text-muted py-3">Detailed transactions khali hain.</td>
                            </tr>
                        <?php else: ?>
                            <?php 
                            $idx = 1; 
                            $totalRapier = 0.0;
                            foreach ($allRows as $r): 
                                $rowRemark = $r['remark'] ?? '';
                                $rowRapier = (float)($r['rapier_share'] ?? 0);
                                $totalRapier += $rowRapier;
                                $rowRef = !empty($r['payment_ref_no']) ? $r['payment_ref_no'] : 'EMPTY_REF_' . $idx;
                            ?>
                            <tr>
                                <td><?= $idx++ ?></td>
                                <td><?= h($r['payment_date']) ?></td>
                                <td class="small text-muted"><?= h($r['payment_ref_no'] ?? '-') ?></td>
                                <td class="fw-semibold"><?= h($r['beneficiary_name']) ?></td>
                                <td><code><?= h($r['beneficiary_account_no']) ?></code></td>
                                <td class="text-end fw-semibold">₹ <?= number_format((float)$r['amount'], 2) ?></td>
                                <td>
                                    <input list="remark_list" 
                                           name="rows[<?= h($rowRef) ?>][remark]" 
                                           value="<?= h($rowRemark) ?>" 
                                           class="form-control form-control-sm">
                                </td>
                                <td>
                                    <input type="number" 
                                           step="0.01" 
                                           name="rows[<?= h($rowRef) ?>][rapier_share]" 
                                           value="<?= $rowRapier > 0 ? h($rowRapier) : '0.00' ?>" 
                                           class="form-control form-control-sm text-end">
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                    <?php if (!empty($allRows)): ?>
                    <tfoot class="table-light fw-bold text-end">
                        <tr>
                            <td colspan="5">Grand Totals:</td>
                            <td class="text-success">₹ <?= number_format($grandTotalStatement, 2) ?></td>
                            <td></td>
                            <td class="text-primary">₹ <?= number_format($totalRapier, 2) ?></td>
                        </tr>
                    </tfoot>
                    <?php endif; ?>
                </table>
            </div>
        </div>

<?php if (!empty($allRows)): ?>
            <div class="mt-3 mb-5">
                <button class="btn btn-success px-4 py-2" name="save_bulk">
                    Save Remarks & Rapier Share
                </button>
            </div>
        <?php endif; ?>
    </form>
</div>

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