<?php
include('includes/header.php'); // Include your header file

// Get the selected month and year from the query parameters (e.g., ?month=8&year=2023)
$selected_month = isset($_GET['month']) ? intval($_GET['month']) : date('m'); // Default to the current month
$selected_year = isset($_GET['year']) ? intval($_GET['year']) : date('Y');    // Default to the current year

// Construct a date range for the selected month and year
$start_date = "$selected_year-$selected_month-01";
$end_date = date('Y-m-t', strtotime($start_date));

// Get the selected employee ID from the form
$employee_id = isset($_GET['employee_id']) ? $_GET['employee_id'] : '';

// Add a condition to the SQL query to filter by the selected employee ID
$filter_condition = ($employee_id) ? "t.userID = '$employee_id'" : '1'; // '1' to include all employees

$query = "SELECT t.*, u.fname, u.lname, u.email, c.clientName, p.projectName, m.milestoneName
          FROM timesheet t
          INNER JOIN users u ON t.userID = u.userID
          LEFT JOIN clients c ON t.clientID = c.clientID
          LEFT JOIN projects p ON t.projectID = p.projectID
          LEFT JOIN milestones m ON t.milestoneID = m.milestoneID
          WHERE t.tentryDate BETWEEN '$start_date' AND '$end_date' AND $filter_condition
          ORDER BY u.fname ASC";

// Execute the query
$result = mysqli_query($conn, $query);

if (!$result) {
    // Handle database query error
    die("Query failed: " . mysqli_error($conn));
}

$previous_user = null; // Variable to track the previous user
$total_hours = 0; // Variable to calculate the total hours for a user

?>
<div class="container mt-4">
    <h1>Timesheet Entries for <?php echo date('F Y', strtotime($start_date)); ?></h1>
    <button id="download-csv" class="btn btn-primary mt-4 float-end">Download CSV</button>
    <form class="mb-4">
        <div class="row">
            <div class="col-md-3">
                <label for="monthSelect" class="form-label">Select Month:</label>
                <select class="form-select" id="monthSelect" name="month">
                    <?php
                    // Generate options for selecting months
                    for ($i = 1; $i <= 12; $i++) {
                        $month_name = date('F', mktime(0, 0, 0, $i, 1));
                        $selected = ($selected_month == $i) ? 'selected' : '';
                        echo "<option value='$i' $selected>$month_name</option>";
                    }
                    ?>
                </select>
            </div>
            <div class="col-md-3">
                <label for="yearSelect" class="form-label">Select Year:</label>
                <select class="form-select" id="yearSelect" name="year">
                    <?php
                    // Generate options for selecting years (you can adjust the range as needed)
                    for ($i = date('Y') - 10; $i <= date('Y') + 10; $i++) {
                        $selected = ($selected_year == $i) ? 'selected' : '';
                        echo "<option value='$i' $selected>$i</option>";
                    }
                    ?>
                </select>
            </div>
            <div class="col-md-3">
                <label for="employeeSelect" class="form-label">Select Employee:</label>
                <select class="form-select" id="employeeSelect" name="employee_id">
                    <option value="">All Employees</option>
                    <?php
                    // Fetch and display a list of employees from your database
                    $employee_query = "SELECT userID, fname, lname FROM users ORDER BY fname ASC";
                    $employee_result = mysqli_query($conn, $employee_query);
                    while ($employee = mysqli_fetch_assoc($employee_result)) {
                        $employee_id = $employee['userID'];
                        $employee_name = $employee['fname'] . ' ' . $employee['lname'];
                        $selected = ($employee_id == $_GET['employee_id']) ? 'selected' : '';
                        echo "<option value='$employee_id' $selected>$employee_name</option>";
                    }
                    ?>
                </select>
            </div>

            <div class="col-md-2">
                <button type="submit" class="btn btn-success mt-4">Show Data</button>
            </div>
        </div>
    </form>

    <table class="table table-bordered table-striped">
        <thead>
            <tr>
                <th>Employee Name</th>
                <th>Date</th>
                <th>Client</th>
                <th>Project</th>
                <th>Milestone</th>
                <th>Hours</th>
                <th>Description</th>
            </tr>
        </thead>
        <tbody>
            <?php
            while ($row = mysqli_fetch_assoc($result)) {
                // Output each timesheet entry row here
                if ($row['userID'] !== $previous_user) {
                    // If a new user is encountered, display the regular rows for the user
                    if ($previous_user !== null) {
                        echo "<tr class='bg-info text-white'>";
                        echo "<td colspan='4'><strong>Total Hours:</strong></td>";
                        echo "<td><strong>$total_hours</strong></td>";
                        echo "<td colspan='3'></td>";
                        echo "</tr>";
                    }
                    $total_hours = 0; // Reset the total hours for the new user
                } 

                // Add the hours to the total for the current user
                $total_hours += $row['hrs'];

                // Display the user's data
                echo "<tr>";
                echo "<td>{$row['fname']} {$row['lname']}</td>";
                echo "<td>{$row['tentryDate']}</td>";
                echo "<td>{$row['clientName']}</td>"; // Display clientName
                echo "<td>{$row['projectName']}</td>"; // Display projectName
                echo "<td>{$row['milestoneName']}</td>";
                echo "<td>{$row['hrs']}</td>";
                echo "<td>{$row['descp']}</td>";
                echo "</tr>";

                $previous_user = $row['userID']; // Update the previous user
            }
            // Display the total hours row for the last user
            if ($previous_user !== null) {
                echo "<tr class='bg-info text-white'>";
                echo "<td colspan='4'><strong>Total Hours:</strong></td>";
                echo "<td><strong>$total_hours</strong></td>";
                echo "<td colspan='3'></td>";
                echo "</tr>";
            }
            ?>
        </tbody>
    </table>
</div>

    <script>
        // Function to trigger CSV download
        function downloadCSV() {
            const table = document.querySelector('.table');
            const rows = table.querySelectorAll('tr');
            const csv = [];

            // Generate CSV content
            rows.forEach((row, rowIndex) => {
                const rowData = [];
                row.querySelectorAll('td, th').forEach((cell, cellIndex) => {
                    // Wrap cell data in double quotes and escape existing double quotes
                    const cellData = cell.textContent.replace(/"/g, '""');
                    rowData.push(`"${cellData}"`);
                });

                // Include the header row if it's the first row
                if (rowIndex === 0) {
                    csv.push(rowData.join(','));
                } else {
                    csv.push(rowData.join(','));
                }
            });

            // Create a blob containing the CSV data
            const blob = new Blob([csv.join('\n')], { type: 'text/csv' });

            // Create a download link and trigger the download
            const link = document.createElement('a');
            link.href = URL.createObjectURL(blob);
            link.download = 'allTimesheet_Report.csv';
            link.style.display = 'none';
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
        }

        // Add click event listener to the download button
        document.getElementById('download-csv').addEventListener('click', downloadCSV);
    </script>
<?php include('includes/footer.php'); ?>