TaskProject/handlers/data.cfc

// data.cfc
component {
    function index(event, rc, prc) {
        // Render the index form view
        event.setView("data");
    }

    // Function to create a new student
    function createStudent(sname, major, level, byear) {
        // Prepare the SQL statement with placeholders
        var insertQuery = new query(datasource="task");
        var sql = "INSERT INTO student (sname, major, level, byear) VALUES (?, ?, ?, ?)";
        insertQuery.setSQL(sql);
        var result = insertQuery.execute([sname, major, level, byear]);
        
        // Check for errors
        if (result.status != "SUCCESS") {
            throw new Error("Error inserting student: " & result.getError());
        }
        
        // Close the query
        insertQuery.close();
    }

    // Function to read all students
    function getAllStudents() {
        var students = [];
        var allQuery = new query(datasource="task");
        allQuery.setSQL("SELECT * FROM Student");
        var result = allQuery.execute();
        while (result.next()) {
            var student = {
                "sid": result.sid,
                "sname": result.sname,
                "major": result.major,
                "level": result.level,
                "byear": result.byear
            };
            arrayAppend(students, student);
        }
        result.close();
        return students;
    }

    // Query 1: Print all the projects in which a specified student is involved
    function getProjectsForStudent(studentID) {
        var projects = [];
        var projectQuery = new query(datasource="task");
        var sql = "SELECT pname FROM Project WHERE pid IN (SELECT pid FROM Assigned WHERE sid = ?)";
        projectQuery.setSQL(sql);
        var result = projectQuery.execute([studentID]);
        while (result.next()) {
            arrayAppend(projects, result.pname);
        }
        result.close();
        return projects;
    }

    // Query 2: Print all the people working on a specified project
    function getPeopleForProject(projectID) {
        var people = [];
        var projectAllQuery = new query(datasource="task");
        var sql = "SELECT sname FROM Student WHERE sid IN (SELECT sid FROM Assigned WHERE pid = ?)";
        projectAllQuery.setSQL(sql);
        var result = projectAllQuery.execute([projectID]);
        while (result.next()) {
            arrayAppend(people, result.sname);
        }
        result.close();
        return people;
    }

    // Query 3: Print all the projects supervised by a specified faculty within a specified time range
    function getProjectsForFacultyWithinTimeRange(facultyID, startDate, endDate) {
        var projects = [];
        var facultyQuery = new query(datasource="task");
        var sql = "SELECT pname FROM Project WHERE pi = ? AND sdate >= ? AND edate <= ?";
        facultyQuery.setSQL(sql);
        var result = facultyQuery.execute([facultyID, startDate, endDate]);
        while (result.next()) {
            arrayAppend(projects, result.pname);
        }
        result.close();
        return projects;
    }

}