TaskProject/views/data.cfm

<cfscript>

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

   
    
    </cfscript>
    
<!--- Output section for messages --->
<div id="output">
    <cfoutput>
        <!--- Check if a message is set and display it --->
        <cfif structKeyExists(url, "message")>
            <p>#url.message#</p>
        </cfif>
    </cfoutput>
</div>

<!--- Form for executing different queries and creating a new student --->
<form id="studentServiceForm" action='/create' method="post">
    <!-- Form for creating a new student -->
    <!-- Input fields for student information -->
    <label for="sname">Name:</label>
    <input type="text" name="sname" id="sname"><br>
    
    <label for="major">Major:</label>
    <input type="text" name="major" id="major"><br>
    
    <label for="level">Level:</label>
    <input type="text" name="level" id="level"><br>
    
    <label for="byear">Birth Year:</label>
    <input type="text" name="byear" id="byear"><br>
    
    <!-- Button to create a new student -->
    <button type="submit" name="method" value="createStudent">Create New Student</button><br>
</form>

<form action="" method="post">
    <!-- Input field for student name -->
    <label for="sname">Student Name:</label>
    <input type="text" id="sname" name="SNAME"><br>

</form>


<form method="post" action="/projects">
    <!-- Query 2: People for Project -->
    <input type="text" name="projectID" placeholder="Enter project ID">
    <button type="submit" name="query" value="query2">Query 2: People for Project</button><br>
</form>

<form method="post" action="/faculty">
    <!-- Query 4: Get all students -->
    <button type="submit" name="query" value="getAllStudents">Query 4: Get all students</button><br>
</form>