<?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>Order Book 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 Contract Value</th>
                                    <th>Total Bill Amount</th>
                                    <th>Total Order On Hand Value</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                // Initialize total variables
                                $totalContractAmt = 0;
                                $totalBillAmt = 0;
                                $totalOrderOnHand = 0;

                                // Query to fetch all contracts along with their total bill amount
                                $query = "SELECT c.contractID, clients.clientName, c.contractName, c.contractAmt, 
                                          SUM(bd.billAmount) AS totalBillAmount,
                                          (c.contractAmt - COALESCE(SUM(bd.billAmount), 0)) AS orderOnHand 
                                          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
                                          WHERE c.contractAmt >= 100
                                          GROUP BY c.contractID
                                          ORDER BY orderOnHand DESC";
                                $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>" . number_format($row['contractAmt'], 0) . "</td>";
                                        echo "<td>" . ($row['totalBillAmount'] !== null ? number_format($row['totalBillAmount'], 0) : '0') . "</td>"; // Display total bill amount without decimals
                                        echo "<td>" . ($row['orderOnHand'] !== null ? number_format($row['orderOnHand'], 0) : '0') . "</td>"; // Display order on hand without decimals
                                        echo "</tr>";
                                        // Increment total variables
                                        $totalContractAmt += $row['contractAmt'];
                                        $totalBillAmt += ($row['totalBillAmount'] !== null ? $row['totalBillAmount'] : 0);
                                        $totalOrderOnHand += ($row['orderOnHand'] !== null ? $row['orderOnHand'] : 0);
                                        $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($totalContractAmt, 0); ?></strong></td>
                                    <td><strong><?php echo number_format($totalBillAmt, 0); ?></strong></td>
                                    <td><strong><?php echo number_format($totalOrderOnHand, 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: "Order Book Report" }); // Convert table to Excel workbook
        
        // Generate the Excel file and trigger the download
        XLSX.writeFile(wb, 'Order Book Report.xlsx');
    }
    
    // Add event listener to the "Download Excel" button
    document.getElementById('download-excel').addEventListener('click', downloadExcel);
</script>

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