Overview of `api.php`

The api.php file handles API requests and determines which action to perform. The focus here is on generating reports using the report action. Below is a breakdown of how the api.php script works.

1. Headers

Cache Control: The script sets headers to prevent caching.
Error Reporting: Error reporting is enabled by using ini_set to display errors during development.

header("Cache-Control: no-cache, no-store, must-revalidate");
header("Pragma: no-cache");
header("Expires: 0");
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

2. Config and Database Includes

The configuration and database connection settings are included via config.php and db.php.

require_once 'config.php';
require_once 'db.php';

3. Parameter Retrieval

The parameters are retrieved using a helper function getParam() based on the request method (GET or POST). The action parameter is converted to lowercase for consistency.

$action = strtolower(getParam('action', $requestMethod) ?? '');

4. API Action Logic

The script uses a switch statement to check the action requested. In this case, it listens for the report action.
It retrieves the necessary parameters: term_id, student_id, date, absent, and format using the getParams() function.
If all required parameters are available, the script calls generateReport.php to process the request.

$result = getParams([], ['term_id', 'student_id', 'date', 'absent', 'format'], $requestMethod);
if (empty($result['missing'])) {
    include 'functions/generateReport.php';

5. Default JSON Response

If no format is specified, the API defaults to returning a JSON response.

$format = $result['params']['format'] ?? 'json';

6. HTML or JSON Output

If the requested format is html, the generated report is output directly as HTML.
Otherwise, the report is returned as a JSON response.

if ($format == 'html' && is_string($response)) {
    echo $response;
} else {
    echo json_encode($response);
}

7. Error Handling

If parameters are missing, the script returns a JSON error response listing the missing parameters.

} else {
    $response = 'Missing parameters: ' . implode(', ', $result['missing']);
    echo json_encode(['status' => 'error', 'message' => $response]);
}

8. Invalid Action

If an unrecognized action is sent, a 404 response is returned.

default:
    http_response_code(404);
    echo json_encode(['error' => 'Invalid action!']);
    break;

Overview of `generateReport.php`

Here's an overview of its functionality:

Key Features of `generateReport.php`

Report Generation Logic:
- The function generateReport connects to the database using $pdo to retrieve attendance data based on specific criteria:
  - term_id: The term to filter by.
  - student_id: The specific student to report on.
  - date: The date for the attendance report.
  - absent: If set to true, it will retrieve students who were absent; otherwise, it will get students who were present.
- The report can be outputted in either HTML or email format.
SQL Query Structure:
- The SQL query is dynamically adjusted based on whether the report is for present or absent students. The query filters students based on their attendance records for the provided term_id and date.
- The query joins the students, terms, and leaders tables to fetch student names, leader names, and term information.
HTML Report:
- The generateHtmlReport function builds a clean HTML table to display attendance data, including student ID, name, and group leader.
- It uses inline CSS for styling, ensuring a visually clean and formatted report.
Error Handling:
- If there is an error during the database operation, it is caught by a try-catch block, and an error message is returned.

Suggested Enhancements

Pagination: If you expect large datasets, you could add pagination to break the results into pages.
CSV Export: Consider adding an option to export the report as a CSV file, making it easier to share or analyze in spreadsheet software.
Advanced Filters: You could add filters like course_id or leader_id to narrow down the report further.

Here’s a step-by-step breakdown of how we build our query using logic to handle the attendance and absent students based on the enrollment in courses ending with "MLC-0000-1":

Steps to Implement

Identify the Correct Term: We’ll use the provided term_id to fetch the correct term from the terms table.
Filter Courses Ending with "MLC-0000-1": We’ll check the courses table for courses within the specified term_id that end with "MLC-0000-1".
Extract the List of Enrolled Students: Each course contains a JSON array of students in the students column. We'll extract this array of students for courses that meet the filtering criteria.
Compare Enrollment with Attendance Records: Using the provided date, we will compare the list of enrolled students with the records in the attendance table.
- If absent=true, we’ll return students who do not have an attendance record for the specified date.
- If absent=false, we’ll return students who do have an attendance record for the specified date.

Code to Handle the Logic

<?php

function generateReport($pdo, $term_id, $student_id, $date, $absent, $format) {
    // Ensure absent is a boolean; default to false if not explicitly true
    $absent = isset($absent) ? filter_var($absent, FILTER_VALIDATE_BOOLEAN) : false;

    // Fetch courses for the specified term that end with "MLC-0000-1"
    $query = "SELECT c.students
              FROM courses c
              JOIN terms t ON c.term = t.id
              WHERE c.term = ? AND c.courseID LIKE '%MLC-0000-1'";
              
    $stmt = $pdo->prepare($query);
    $stmt->execute([$term_id]);
    $courses = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($courses)) {
        return json_encode(['status' => 'error', 'message' => 'No courses found for the specified term.']);
    }

    // Collect all students from the selected courses
    $enrolledStudents = [];
    foreach ($courses as $course) {
        $students = json_decode($course['students'], true); // Decode the JSON student list
        if ($students) {
            $enrolledStudents = array_merge($enrolledStudents, $students);
        }
    }

    // If no students are found, return an error
    if (empty($enrolledStudents)) {
        return json_encode(['status' => 'error', 'message' => 'No students found for the selected courses.']);
    }

    // Remove duplicate student IDs
    $enrolledStudents = array_unique($enrolledStudents);

    // Fetch attendance records for the specified date and term
    $attendanceQuery = "SELECT a.student_id
                        FROM attendance a
                        WHERE a.date = ? AND a.term_id = ?";
                        
    $attendanceStmt = $pdo->prepare($attendanceQuery);
    $attendanceStmt->execute([$date, $term_id]);
    $attendanceRecords = $attendanceStmt->fetchAll(PDO::FETCH_COLUMN);

    // Compare enrolled students with attendance records
    if ($absent) {
        // Return students who are enrolled but do not have an attendance record for the specified date
        $absentStudents = array_diff($enrolledStudents, $attendanceRecords);
        $attendances = array_values($absentStudents);
    } else {
        // Return students who are enrolled and have an attendance record for the specified date
        $presentStudents = array_intersect($enrolledStudents, $attendanceRecords);
        $attendances = array_values($presentStudents);
    }

    try {
        // Fetch term short_name if a term_id is provided
        $termShortName = '';
        if ($term_id) {
            $termQuery = "SELECT short_name FROM terms WHERE id = ?";
            $termStmt = $pdo->prepare($termQuery);
            $termStmt->execute([$term_id]);
            $term = $termStmt->fetch(PDO::FETCH_ASSOC);
            $termShortName = $term['short_name'] ?? '';
        }

        // Format the report date
        $formattedDate = ($date) ? (new DateTime($date))->format('m-d-Y') : 'Multiple Dates';

        // Return the report in the requested format (default is JSON)
        if ($format == 'html') {
            $htmlReport = generateHtmlReport($pdo, $attendances, $formattedDate, $termShortName, $absent);
            return $htmlReport; // Output HTML directly
        } else if ($format == 'email') {
            require_once 'sendEmail.php';
            return sendEmailReport($pdo, generateHtmlReport($pdo, $attendances, $formattedDate, $termShortName, $absent)); // Pass $pdo here
        } else {
            return ['status' => 'success', 'data' => $attendances];
        }

    } catch (PDOException $e) {
        return ['status' => 'error', 'message' => $e->getMessage()];
    }
}

?>

Explanation - How it Works:

Fetch Courses with "MLC-0000-1": The query filters courses based on the term_id and course names ending with "MLC-0000-1" using the LIKE clause.
Decode the Student JSON List: For each course that matches the query, we decode the students column, which contains a JSON list of enrolled students.
Compare Enrollment and Attendance: We compare the list of students enrolled in the courses with the records in the attendance table for the specified date:
- For absent=true, we return students who are enrolled but do not have a matching attendance record.
- For absent=false, we return students who are enrolled and do have a matching attendance record.
Output Formats: The report is returned in either JSON (default), HTML, or email format depending on the format parameter.

Test Cases

Absent Students Report:
```
https://localhost/attendance/api.php?action=report&date=2024-08-21&absent=true&format=json&term_id=2
```
This will return all students who do not have an attendance record on 2024-08-21 but are enrolled in courses ending in "MLC-0000-1" for the specified term_id.
Present Students Report (Attendance Report):
```
https://localhost/attendance/api.php?action=report&date=2024-08-21&absent=false&format=json&term_id=2
```
This will return all students who have an attendance record on 2024-08-21 and are enrolled in courses ending in "MLC-0000-1" for the specified term_id.

Next Steps

Test with Sample Data: Verify that the API returns the correct list of students (either absent or present) based on the attendance records and course enrollments.
Troubleshoot Edge Cases: If some students are missing, check for potential issues such as:
- Incorrect student data in the JSON-encoded students column.
- Attendance records that are not properly linked to the correct term_id or date.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Overview of `api.php`

1. Headers

2. Config and Database Includes

3. Parameter Retrieval

4. API Action Logic

5. Default JSON Response

6. HTML or JSON Output

7. Error Handling

8. Invalid Action

Overview of `generateReport.php`

Key Features of `generateReport.php`

Suggested Enhancements

Steps to Implement

Code to Handle the Logic

Explanation - How it Works:

Test Cases

Next Steps

FilesExpand file tree

README.md

Latest commit

History

README.md

File metadata and controls

Overview of api.php

1. Headers

2. Config and Database Includes

3. Parameter Retrieval

4. API Action Logic

5. Default JSON Response

6. HTML or JSON Output

7. Error Handling

8. Invalid Action

Overview of generateReport.php

Key Features of generateReport.php

Suggested Enhancements

Steps to Implement

Code to Handle the Logic

Explanation - How it Works:

Test Cases

Next Steps

Overview of `api.php`

Overview of `generateReport.php`

Key Features of `generateReport.php`