<?php
include('includes/header.php');
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
?>

<div class="container mt-5">
    <div class="row">
        <div class="col-md-12">
            <div class="card">
                <div class="card-header">
                    <h3>Receivables Report</h3>
                    <button id="download-excel" class="btn btn-primary float-end" style="margin-bottom: 10px;">Download Excel</button>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-bordered table-striped">
                            <thead>
                                <tr>
                                    <th>S.No.</th>
                                    <th>Client Name</th>
                                    <th>Contract Name</th>
                                    <th>Total Billed Amount</th>
                                    <th>Total Received Amount</th>
                                    <th>Total Receivable Value</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                // Initialize total variables
                                $totalBilledAmount = 0;
                                $totalReceivedAmount = 0;
                                $totalReceivableValue = 0;

                                // Query to fetch all contracts along with their total billed amount and total received amount
                                $query = "SELECT c.contractID, clients.clientName, c.contractName, c.contractAmt, 
                                          SUM(bd.billAmount) AS totalBilledAmount,
                                          SUM(bd.receivedAmt) AS totalReceivedAmount,
                                          (SUM(bd.billAmount) - COALESCE(SUM(bd.receivedAmt), 0)) AS totalReceivableValue
                                          FROM contracts c
                                          INNER JOIN projects p ON c.contractID = p.contractID
                                          INNER JOIN clients ON p.clientID = clients.clientID
                                          LEFT JOIN ncmilestones m ON c.contractID = m.contractID
                                          LEFT JOIN ncbilldetails bd ON m.ncmilestoneID = bd.ncmilestoneID
                                          GROUP BY c.contractID
                                          HAVING c.contractAmt >= 100
                                          ORDER BY totalReceivableValue DESC"; // Exclude contracts with contractAmt less than 100";
                                $result = mysqli_query($conn, $query);

                                if ($result && mysqli_num_rows($result) > 0) {
                                    $count = 1;
                                    while ($row = mysqli_fetch_assoc($result)) {
                                        // Output rows for each contract
                                        echo "<tr>";
                                        echo "<td>" . $count . "</td>";
                                        echo "<td>" . $row['clientName'] . "</td>";
                                        echo "<td>" . $row['contractName'] . "</td>";
                                        echo "<td>" . ($row['totalBilledAmount'] !== null ? number_format($row['totalBilledAmount'], 0) : '0') . "</td>"; // Display total billed amount without decimals
                                        echo "<td>" . ($row['totalReceivedAmount'] !== null ? number_format($row['totalReceivedAmount'], 0) : '0') . "</td>"; // Display total received amount without decimals
                                        echo "<td>" . ($row['totalReceivableValue'] !== null ? number_format($row['totalReceivableValue'], 0) :'0') . "</td>"; // Display total receivable value without decimals
                                        echo "</tr>";

                                        // Update total variables
                                        $totalBilledAmount += $row['totalBilledAmount'];
                                        $totalReceivedAmount += $row['totalReceivedAmount'];
                                        $totalReceivableValue += $row['totalReceivableValue'];
                                        $count++;
                                    }
                                } else {
                                    echo "<tr><td colspan='6'>No contracts found.</td></tr>";
                                }
                                ?>
                            </tbody>
                            <tfoot>
                                <tr>
                                    <td class="text-center" colspan="3"><strong>Totals</strong></td>
                                    <td><strong><?php echo number_format($totalBilledAmount, 0); ?></strong></td>
                                    <td><strong><?php echo number_format($totalReceivedAmount, 0); ?></strong></td>
                                    <td><strong><?php echo number_format($totalReceivableValue, 0); ?></strong></td>
                                </tr>
                            </tfoot>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>
<script>
    function downloadExcel() {
        const table = document.querySelector('.table'); // Select the table
        const wb = XLSX.utils.table_to_book(table, { sheet: "Receivables Report" }); // Convert table to Excel workbook
        
        // Generate the Excel file and trigger the download
        XLSX.writeFile(wb, 'Receivables Report.xlsx');
    }
    
    // Add event listener to the "Download Excel" button
    document.getElementById('download-excel').addEventListener('click', downloadExcel);
</script>

<?php include('includes/footer.php'); ?>