Untitled

<?php
require_once('db_connect.php');
error_reporting(0);
session_start();

function get_user_name($user_id)
{
    global $conn;

    $query = "SELECT firstname, lastname FROM users WHERE id = ?";
    $stmt = $conn->prepare($query);
    $stmt->bind_param('s', $user_id);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($row = $result->fetch_assoc()) {
        $ssfullname = ucfirst($row['firstname']) . " " . ucfirst($row['lastname']);
    } else {
        $ssfullname = 'NA';
    }

    return $ssfullname;
}

if (isset($_POST['action']) && $_POST['action'] == 'monthly_report') {
    extract($_POST);

    ?>

    <table class="table m-0 table-bordered">
        <thead>
            <th>#</th>
            <th>User Name</th>
            <?php
            // Generate headers for each month
            for ($month = 1; $month <= 12; $month++) {
                $monthDate = date("M Y", strtotime("2024-$month-01"));
                echo "<th colspan='2'>$monthDate</th>";
            }
            ?>
        </thead>
        <tbody>
            <tr>
                <td></td>
                <td></td>
                <?php
                // Display month names by ashishp
                for ($month = 1; $month <= 12; $month++) {
                    $monthName = date("M Y", strtotime("2024-$month-01"));
                    echo "<td>Total Hours</td>";
                    echo "<td>Average Hours</td>";
                }
                ?>
            </tr>
            <?php
            $query_users = "SELECT DISTINCT user_ids FROM task_list";
            $stmt_users = $conn->prepare($query_users);
            $stmt_users->execute();
            $result_users = $stmt_users->get_result();

            if ($result_users->num_rows > 0) {
                $cnt = 0;
                while ($row_user = $result_users->fetch_assoc()) {
                    $user_id = $row_user['user_ids'];

                    // Initialize arrays to store total and average hours for each month by ashishp
                    $total_hours_by_month = array();
                    $average_hours_by_month = array();

                    for ($month = 1; $month <= 12; $month++) {
                        $total_hours_by_month[$month] = 0;
                        $average_hours_by_month[$month] = 0;
                    }

                    for ($month = 1; $month <= 12; $month++) {
                        $startDate = date('Y-m-01', strtotime("2024-$month-01"));
                        $endDate = date('Y-m-t', strtotime("2024-$month-01"));
                        $querySql = "SELECT time_taken, start_date
                                     FROM task_list
                                     WHERE user_ids = ?
                                     AND start_date >= ?
                                     AND start_date < LAST_DAY(?) + INTERVAL 1 DAY";

                        $stmt_rep = $conn->prepare($querySql);
                        $stmt_rep->bind_param('sss', $user_id, $startDate, $startDate);
                        $stmt_rep->execute();
                        $result_rep = $stmt_rep->get_result();
                        while ($row_rep = $result_rep->fetch_assoc()) {
                            $time_taken_array = unserialize($row_rep['time_taken']);
                            if ($time_taken_array !== false && is_array($time_taken_array)) {
                                foreach ($time_taken_array as $task_hours) {
                                    $hours = explode(":", $task_hours);
                                    $hours = array_pad($hours, 3, '00');
                                    if (count($hours) >= 3) {
                                        $total_hours_by_month[$month] += intval($hours[0]); 
                                    } else {
                                        echo "Invalid array structure: " . print_r($hours, true);
                                    }
                                }
                            } else {
                                echo "Invalid time_taken_array: " . $row_rep['time_taken'];
                            }
                        }

                        $stmt_rep->close(); // Close the statement

                        // Calculate average hours by ashish
                        $average_hours_by_month[$month] = $total_hours_by_month[$month] / 20; // Assuming 20 working days per month by ashishpandey
                    }
                    ?>
                    <tr>
                        <td><?= ++$cnt ?></td>
                        <td><a><?= get_user_name($user_id) ?></a></td>
                        <?php
                        foreach ($total_hours_by_month as $month => $total_hours) {
                            $average_hours = round($average_hours_by_month[$month], 2);
                            echo "<td>$total_hours</td>";
                            echo "<td>$average_hours</td>";
                        }
                        ?>
                    </tr>
                    <?php
                }
            }
            ?>
        </tbody>
    </table>
<?php
}
?>