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

// Initialize variables for the selected date range
$fromDate = isset($_GET['fromDate']) ? $_GET['fromDate'] : '';
$toDate = isset($_GET['toDate']) ? $_GET['toDate'] : '';

?>

<div class="container mt-5">
    <div class="row">
        <div class="col-md-12">
            <div class="card">
                <div class="card-header">
                    <h3>Cumulative Billing Report</h3>
                </div>
                <div class="card-body">
                    <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="GET">
                        <div class="row mb-3">
                            <div class="col-md-6">
                                <label for="fromDate" class="form-label">From Date:</label>
                                <input type="date" class="form-control" id="fromDate" name="fromDate" value="<?php echo $fromDate; ?>">
                            </div>
                            <div class="col-md-6">
                                <label for="toDate" class="form-label">To Date:</label>
                                <input type="date" class="form-control" id="toDate" name="toDate" value="<?php echo $toDate; ?>">
                            </div>
                        </div>
                        <button type="submit" class="btn btn-primary">Filter</button>
                        <button id="download-excel" class="btn btn-primary float-end" style="margin-bottom: 10px;">Download Excel</button>
                    </form>
                    <!-- Show the selected date range as a subheading -->
                    <h5>Report Dates: <?php echo $fromDate . ' to ' . $toDate; ?></h5>
                    <div class="table-responsive mt-3">
                        <table class="table table-bordered table-striped">
                            <thead>
                                <tr>
                                    <th>S.No.</th>
                                    <th>Client Name</th>
                                    <th>Contract Name</th>
                                    <th>Milestone Name</th>
                                    <th>Bill Number</th>
                                    <th>Bill Date</th>
                                    <th>Bill Amount</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                // Prepare the SQL query based on the selected period
                                $sql = "SELECT clients.clientName, contracts.contractName, ncmilestones.milestone, 
                                        ncbilldetails.billNo, ncbilldetails.billDate, ncbilldetails.billAmount
                                        FROM ncbilldetails
                                        INNER JOIN ncmilestones ON ncbilldetails.ncmilestoneID = ncmilestones.ncmilestoneID
                                        INNER JOIN contracts ON ncmilestones.contractID = contracts.contractID
                                        INNER JOIN projects ON contracts.contractID = projects.contractID
                                        INNER JOIN clients ON projects.clientID = clients.clientID";
                                
                                if ($fromDate && $toDate) {
                                    // Add conditions for filtering by date range
                                    $sql .= " WHERE ncbilldetails.billDate BETWEEN '$fromDate' AND '$toDate'";
                                }

                                $sql .= " ORDER BY clients.clientName,contracts.contractName";

                                $result = mysqli_query($conn, $sql);

                                $totalAmount = 0;

                                if ($result && mysqli_num_rows($result) > 0) {
                                    $count = 1;
                                    while ($row = mysqli_fetch_assoc($result)) {
                                        echo "<tr>";
                                        echo "<td>" . $count . "</td>";
                                        echo "<td>" . $row['clientName'] . "</td>";
                                        echo "<td>" . $row['contractName'] . "</td>";
                                        echo "<td>" . $row['milestone'] . "</td>";
                                        echo "<td>" . $row['billNo'] . "</td>";
                                        echo "<td>" . $row['billDate'] . "</td>";
                                        echo "<td>" . $row['billAmount'] . "</td>";
                                        echo "</tr>";
                                        $totalAmount += $row['billAmount'];
                                        $count++;
                                    }
                                } else {
                                    echo "<tr><td colspan='7'>No bill details found.</td></tr>";
                                }
                                ?>
                                <!-- Row to show totals -->
                                <tr>
                                    <td colspan="6" class="text-end"><strong>Total:</strong></td>
                                    <td><?php echo $totalAmount; ?></td>
                                </tr>
                            </tbody>
                        </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: "Billing Report" }); // Convert table to Excel workbook
        
        // Generate the Excel file and trigger the download
        XLSX.writeFile(wb, 'Billing Report.xlsx');
    }
    
    // Add event listener to the "Download Excel" button
    document.getElementById('download-excel').addEventListener('click', downloadExcel);
</script>
<?php include('includes/footer.php'); ?>