View Code - 20260722

« Back to History

cms_payment_report.php | 20260722_120325.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';

$ctx = page_require_access('cms_payment_report');

$pdo        = $ctx['pdo'];
$company_id = (int)$ctx['company_id'];

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

/* ========================
   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;
    }
}

/* ========================
   Date Filter
======================== */

$from = $_GET['from_date'] ?? '';
$to   = $_GET['to_date'] ?? '';

/* ========================
   Employee Bank Accounts
======================== */

$employeeAccounts = [];

// Step A: Load from company_employee_master
$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']
        ];
    }
}

// Step B: Load from employee_bank_data (image_cc6d8a.png)
$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 !== '') {
        // Agar account master me pehle se nahi hai tabhi isko add karenge priority validation ke liye
        if (!isset($employeeAccounts[$acc])) {
            $employeeAccounts[$acc] = [
                'name' => $r['employee_name'],
                'department' => $r['department_name']
            ];
        }
    }
}

/* ========================
   Contractor Employee Accounts (image_cc6984.png)
======================== */

$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']
        ];
    }
}

/* ========================
   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;
    }
}

/* ========================
   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'];
    }
}

/* ========================
   Department Names
======================== */

$deptNames = [];

$stmt = $pdo->prepare("
    SELECT id, name
    FROM company_departments
    WHERE company_id = :cid
");

$stmt->execute([
    ':cid' => $company_id
]);

while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $deptNames[(int)$r['id']] = $r['name'];
}

/* ========================
   CMS Payment Read
======================== */

$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);

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* ========================
   Department Summary & Filter Unknown Entries
======================== */

$summary = [];
$unknownEntries = []; 
$grandTotal = 0;

foreach ($rows as $r) {
    $account = preg_replace(
        '/\D/',
        '',
        (string)$r['beneficiary_account_no']
    );
    if (!$account) {
        continue;
    }
    
    $isUnknown = false;
    
    if (isset($deletedAccounts[$account])) {
        $department = 'Deleted Account';
        $displayName = $r['beneficiary_name'];
    } 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 = $r['beneficiary_name'];
    } elseif (isset($partyAccounts[$account])) {
        $department = 'Party - ' . $partyAccounts[$account];
        $displayName = $partyAccounts[$account];
    } else {
        $department = 'Unknown';
        $displayName = $r['beneficiary_name'];
        $isUnknown = true;
    }
    
    if ($isUnknown) {
        $unknownEntries[] = [
            'date' => $r['payment_date'],
            'beneficiary_name' => $r['beneficiary_name'],
            'account_no' => $r['beneficiary_account_no'],
            'ref_no' => $r['payment_ref_no'] ?? '-',
            'amount' => (float)$r['amount']
        ];
    }

$date = $r['payment_date'];
    $key = $date . '|' . $department;
    if (!isset($summary[$key])) {
        $summary[$key] = [
            'date' => $date,
            'department' => $department,
            'entries' => 0,
            'total' => 0
        ];
    }
    $summary[$key]['entries']++;
    $summary[$key]['total'] += (float)$r['amount'];
    $grandTotal += (float)$r['amount'];
}

<h3>CMS Payment Report</h3>

<div>

</div>

<div>

</div>

<button class="btn btn-primary">
                Filter
            </button>

<a
                href="cms_payment_report.php"
                class="btn btn-secondary"
            >
                Reset
            </a>

</div>

</form>

</div>

<h4>
        Department + Date Wise Payment Summary
    </h4>

<thead>

<tr>

<th>
                    Payment Date
                </th>

<th>
                    Department
                </th>

<th>
                    Total Entries
                </th>

<th>
                    Total Amount
                </th>

</tr>

</thead>

<tbody>

<?php if (!$summary): ?>

<tr>

<td colspan="5">
                        No data found.
                    </td>

</tr>

<?php else: ?>

<?php
                $i = 1;
                $totalEntries = 0;
                ?>

<?php foreach ($summary as $s): ?>

<?php
                    $totalEntries += (int)$s['entries'];
                    ?>

<tr>

</tr>

<?php endforeach; ?>

<?php endif; ?>

</tbody>

<tfoot>

<tr>

<th colspan="3">
                    Grand Total
                </th>

</tr>

</tfoot>

</table>

</div>

<div class="card" style="margin-top: 25px;">
    
    <h4 class="text-danger">
        Unknown Account Detailed Transactions (From File)
    </h4>

<table class="table table-striped table-bordered">
        
        <thead>
            <tr>
                <th>#</th>
                <th>Payment Date</th>
                <th>Beneficiary Name</th>
                <th>Account Number</th>
                <th>Reference Number</th>
                <th class="text-end">Amount</th>
            </tr>
        </thead>
        
        <tbody>
            <?php if (empty($unknownEntries)): ?>
                <tr>
                    <td colspan="6" class="text-muted text-center">
                        Is filter criteria me koi bhi Unknown Account entry nahi mili.
                    </td>
                </tr>
            <?php else: ?>
                <?php 
                $ukIdx = 1;
                $ukTotalAmount = 0;
                ?>
                <?php foreach ($unknownEntries as $uk): ?>
                    <?php $ukTotalAmount += $uk['amount']; ?>
                    <tr>
                        <td><?= $ukIdx++ ?></td>
                        <td><?= htmlspecialchars((string)$uk['date']) ?></td>
                        <td class="fw-bold"><?= htmlspecialchars((string)$uk['beneficiary_name']) ?></td>
                        <td><?= htmlspecialchars((string)$uk['account_no']) ?></td>
                        <td><?= htmlspecialchars((string)$uk['ref_no']) ?></td>
                        <td class="text-end fw-bold"><?= number_format($uk['amount'], 2) ?></td>
                    </tr>
                <?php endforeach; ?>
            <?php endif; ?>
        </tbody>
        
        <?php if (!empty($unknownEntries)): ?>
        <tfoot>
            <tr>
                <th colspan="5" class="text-end">Unknown Total Amount:</th>
                <th class="text-end text-danger fw-bold"><?= number_format($ukTotalAmount, 2) ?></th>
            </tr>
        </tfoot>
        <?php endif; ?>
        
    </table>
    
</div>

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