View Code - 20260722

va_receive_report.php | 20260722_120325.php Initial Domain Snapshot

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

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

// Financial Year Filter Logic
$current_fy = "2025-26";
$selected_fy = $_GET['fy'] ?? $current_fy;
$fy_list = ["2024-25", "2025-26", "2026-27"];

// SQL Logic: Added financial_year filter and grouping
$sql = "
    SELECT 
        s.challan_no, 
        MAX(s.party_name) as party, 
        GROUP_CONCAT(DISTINCT s.grey_id SEPARATOR ', ') as grey_ids,
        SUM(s.pcs) as total_sent,
        IFNULL(r.total_received, 0) as total_received,
        (SUM(s.pcs) - IFNULL(r.total_received, 0)) as pending
    FROM va_send_entry s
    LEFT JOIN (
        SELECT challan_no, company_id, SUM(total_pcs) as total_received 
        FROM va_receive_entry 
        WHERE company_id = :cid1
        GROUP BY challan_no, company_id
    ) r ON s.challan_no = r.challan_no AND s.company_id = r.company_id
    WHERE s.company_id = :cid2 
      AND s.financial_year = :fy
    GROUP BY s.challan_no
    HAVING pending <= 0 
    ORDER BY s.challan_no DESC
";

try {
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':cid1' => $company_id,
        ':cid2' => $company_id,
        ':fy'   => $selected_fy
    ]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
    die("Query Error: " . $e->getMessage());
}

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

<div class="container-fluid mt-4 px-4">
    <div class="card border-0 shadow-sm mb-4 no-print">
        <div class="card-body bg-light rounded">
            <form method="GET" class="row g-3 align-items-end">
                <div class="col-md-3">
                    <label class="small fw-bold text-muted text-uppercase">Financial Year</label>
                    <select name="fy" class="form-select form-select-sm" onchange="this.form.submit()">
                        <?php foreach($fy_list as $fy): ?>
                            <option value="<?= $fy ?>" <?= $selected_fy == $fy ? 'selected' : '' ?>><?= $fy ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-2 ms-auto text-end">
                    <button type="button" onclick="window.print()" class="btn btn-dark btn-sm px-4">Print Report</button>
                </div>
            </form>
        </div>
    </div>

<div class="card shadow-sm border-0">
        <div class="card-header bg-white py-3">
            <h5 class="text-success fw-bold mb-0">
                VA Completed / Extra Received <span class="text-muted small">| FY: <?= $selected_fy ?></span>
            </h5>
        </div>
        <div class="table-responsive">
            <table class="table table-bordered table-hover align-middle mb-0">
                <thead class="table-dark small text-uppercase">
                    <tr>
                        <th>Challan No</th>
                        <th>Party Name</th>
                        <th>Grey IDs</th>
                        <th class="text-center">Sent Pcs</th>
                        <th class="text-center">Received Pcs</th>
                        <th class="text-center">Status</th>
                    </tr>
                </thead>
                <tbody class="small">
                    <?php 
                    $grand_sent = 0; 
                    $grand_rcvd = 0;

if (!empty($rows)):
                        foreach($rows as $r): 
                            $grand_sent += $r['total_sent'];
                            $grand_rcvd += $r['total_received'];
                    ?>
                    <tr>
                        <td class="fw-bold text-dark">#<?= htmlspecialchars($r['challan_no']) ?></td>
                        <td><?= htmlspecialchars($r['party']) ?></td>
                        <td class="text-muted" style="font-size: 0.8rem;"><?= htmlspecialchars($r['grey_ids'] ?? '-') ?></td>
                        <td class="text-center"><?= number_format($r['total_sent']) ?></td>
                        <td class="text-center fw-bold text-success"><?= number_format($r['total_received']) ?></td>
                        <td class="text-center">
                            <?php if($r['pending'] == 0): ?>
                                <span class="badge bg-success-subtle text-success border border-success-subtle px-3">COMPLETED</span>
                            <?php else: ?>
                                <span class="badge bg-primary-subtle text-primary border border-primary-subtle px-3">EXTRA: <?= abs($r['pending']) ?></span>
                            <?php endif; ?>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
                <tfoot class="table-light fw-bold shadow-sm">
                    <tr>
                        <td colspan="3" class="text-end text-uppercase">Grand Total (<?= $selected_fy ?>):</td>
                        <td class="text-center"><?= number_format($grand_sent) ?></td>
                        <td class="text-center text-success fs-6"><?= number_format($grand_rcvd) ?></td>
                        <td class="bg-white"></td>
                    </tr>
                    <?php else: ?>
                    <tr>
                        <td colspan="6" class="text-center py-5 text-muted">No completed records found for FY <?= $selected_fy ?>.</td>
                    </tr>
                    <?php endif; ?>
                </tfoot>
            </table>
        </div>
    </div>
</div>

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