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

// Get the selected start and end dates from the form
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-t');

// 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

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

// Add a condition to the SQL query to filter by the selected project ID
$project_filter = ($project_id) ? "t.projectID = '$project_id'" : '1';

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

// Add a condition to the SQL query to filter by the selected client ID
$client_filter = ($client_id) ? "t.clientID = '$client_id'" : '1';

// Modify the SQL query to include the client filter condition
$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 AND $project_filter AND $client_filter
          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 Report</h1>
    <form class="mb-4">
        <div class="row">
            <div class="col-md-2">
                <label for="startDate" class="form-label">From:</label>
                <input type="date" class="form-control" id="startDate" name="start_date" value="<?php echo $start_date; ?>">
            </div>
            <div class="col-md-2">
                <label for="endDate" class="form-label">To:</label>
                <input type="date" class="form-control" id="endDate" name="end_date" value="<?php echo $end_date; ?>">
            </div>
            <div class="col-md-2">
                <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-3">
                <label for="clientSelect" class="form-label">Select Client:</label>
                <select class="form-select" id="clientSelect" name="client_id">
                    <option value="">All Clients</option>
                    <?php
                    // Fetch and display a list of clients from your database
                    $client_query = "SELECT clientID, clientName FROM clients ORDER BY clientName ASC";
                    $client_result = mysqli_query($conn, $client_query);
                    while ($client = mysqli_fetch_assoc($client_result)) {
                        $client_id = $client['clientID'];
                        $client_name = $client['clientName'];
                        $selected = ($client_id == $_GET['client_id']) ? 'selected' : '';
                        echo "<option value='$client_id' $selected>$client_name</option>";
                    }
                    ?>
                </select>
            </div>
            <div class="col-md-3">
                <label for="projectSelect" class="form-label">Select Project:</label>
                <select class="form-select" id="projectSelect" name="project_id">
                    <option value="">All Projects</option> <!-- Default option -->
                </select>
            </div>
            <div class="col-md-1 mt-2">
                <button type="submit" class="btn btn-success mt-4">Show</button>
            </div>
            <div class="col-md-2 mt-2">
                <button id="download-csv" class="btn btn-primary mt-4 float-end">Download CSV</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>
    <script>
document.addEventListener("DOMContentLoaded", function () {
    const clientSelect = document.getElementById('clientSelect');
    const projectSelect = document.getElementById('projectSelect');

    // Populate the project dropdown with all projects by default
    fetchAllProjects();

    clientSelect.addEventListener('change', function () {
        const selectedClientID = clientSelect.value;

        if (selectedClientID !== '') {
            // Fetch and populate projects based on the selected client
            fetchProjects(selectedClientID);
        } else {
            // Show all projects by default
            fetchAllProjects();
        }
    });

    function fetchAllProjects() {
        // Make an AJAX request to get all projects
        const xhr = new XMLHttpRequest();
        xhr.open('POST', 'get_all_projects.php', true); // Create a new PHP script to get all projects
        xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');

        xhr.onload = function () {
            if (xhr.status === 200) {
                projectSelect.innerHTML = xhr.responseText;
            }
        };

        xhr.send();
    }

    function fetchProjects(clientID) {
        // Make an AJAX request to your get_projects.php script
        const xhr = new XMLHttpRequest();
        xhr.open('POST', 'get_projects.php', true);
        xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');

        xhr.onload = function () {
            if (xhr.status === 200) {
                projectSelect.innerHTML = '<option value="">All Projects</option>' + xhr.responseText;
            }
        };

        xhr.send('clientID=' + clientID);
    }
});
</script>
<?php include('includes/footer.php'); ?>