getTable.php

<?php
/*
This file is passed variable 'type' which identifies which table it returns. Given the 'type',
it queries the database and formats the results into a html table (enclosed in a string). This string is then sent back.
This file was written by Peter Smith.
*/
$type = $_REQUEST['type'];
$servername = "localhost";
include "team11-mysql-connect.php"; //to provide $username,$password
$dbname = "team11project";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
if($type == "problem"){
    $sql = "SELECT * FROM ProblemInfo";
}else if($type == "specialist"){
    //for type=specialist, I query the database twice, in order to get some extra information.
    $sql = $sql = "SELECT Specialists.SpecialistID, Specialists.Specialism, Personnel.Name, Personnel.TelNo, COUNT( e.ProblemID ) AS  'Current Problems', Specialists.PersonnelID
FROM Specialists
INNER JOIN Personnel ON Specialists.PersonnelID = Personnel.PersonnelID
INNER JOIN ProblemInfo e ON e.SpecialistID = Specialists.SpecialistID
WHERE STATUS =  'Pending'
GROUP BY Specialists.SpecialistID";
//the second query gets the number of completed problems within the last month, per specialist.
    $sql2 = "SELECT COUNT( f.ProblemID ) AS  'Completed Problems'
FROM ProblemInfo f
WHERE
CallDateTime
BETWEEN SUBDATE( NOW( ) , INTERVAL 1 
MONTH ) 
AND NOW( )
AND 
STATUS =  'Solved'
GROUP BY SpecialistID";
}else if($type == "hardware"){
    $sql = "SELECT * FROM Hardware WHERE HardwareID >=1";
}else if($type == "software"){
    $sql = "SELECT * FROM Software WHERE SoftwareID >=1";
}else{
    echo "this will work later";
}
//this stores the result from the query in $result
$result = $conn->query($sql);
//For type=specialist, we send the second query and store the result in $res2
$rows2 = [];
if($type == 'specialist'){
    $res2 = $conn->query($sql2);
    $i = 0;
    //We then go throgh $res2 and store the result in the array $rows2
    while($row = $res2->fetch_assoc()) {
        $rows2[$i] = $row["Completed Problems"];
        $i++;
    }
}

if ($result->num_rows > 0) {
    //We start the string with a text box that, when changed, will trigger filterTable();
    $str = '<input type="text" id="'.$type.'Search" style="width: 100%; overflow-x: hidden; padding: 0.5%; font-size: 105%;" onkeyup="filterTable(\''.$type.'\')" placeholder="Search..." style="width: 20%;">';
    //The string is then added to with each header - which, when clicked, trigger a specific sortTable() function.
    if($type == "problem"){
        $str .= "<table id='problemTable'><thead><tr>";
        $str .= "<th onclick='sortTable(0, \"problemTable\")'>Problem ID</th>";
        $str .= "<th onclick='sortTable(1, \"problemTable\")'>Date/Time Opened</th>";
        $str .= "<th onclick='sortTable(2, \"problemTable\")'>ID of Caller</th>";
        $str .= "<th onclick='sortTable(3, \"problemTable\")'>ID of Operator</th>";
        $str .= "<th onclick='sortTable(4, \"problemTable\")'>Hardware/Software</th>";
        $str .= "<th onclick='sortTable(5, \"problemTable\")'>Problem Type</th>";
        $str .= "<th onclick='sortTable(6, \"problemTable\")'>Specialist ID</th>";
        $str .= "<th onclick='sortTable(7, \"problemTable\")'>Date/Time Solved</th>";
        $str .= "<th onclick='sortTable(8, \"problemTable\")'>Status</th>";
        $str .= "</tr></thead><tbody>";
        //We then loop through the results of our query and write a row for each. The <tr> tag has an onclick attribute which will take it to viewProblem(), allowing the use to see more detail about the problem.
        while($row = $result->fetch_assoc()) {
            $str .= "<tr onclick='viewProblem(".$row["ProblemID"].")'><td>".$row["ProblemID"]."</td><td>".$row["CallDateTime"]."</td><td>".$row["CallerID"]."</td><td>".$row["OperatorID"]."</td><td>";
            //As our database doesn't have a hardware/software value, and yet our table will (to simplify the information), this if statement will find out if the problem is Hardware or Software. If either HardwareID or SoftwareID is 0 in the db, it is the other.
            if($row["HardwareID"] != 0){
                $str .= "Hardware";
            }else if($row["SoftwareID"] != 0){
                $str .= "Software";
            }else{
                $str .= "Error";
            }
            $str .= "</td><td>".$row["ProblemType"]."</td><td>".$row["SpecialistID"]."</td><td>".$row["DateTimeSolved"]."</td><td>".$row["Status"]."</td></tr>";
        }
        $str .= '</tbody></table>';
        echo $str;
    } else if($type == "specialist"){
        //As above, we have a text box with filterTable and sortTable in the headers.
        $str .= "<table id='specialistTable'><thead><tr>";
        $str .= "<th onclick='sortTable(0, \"specialistTable\")'>Specialist ID</th>";
        $str .= "<th onclick='sortTable(1, \"specialistTable\")'>Name</th>";
        $str .= "<th onclick='sortTable(2, \"specialistTable\")'>Specialism</th>";
        $str .= "<th onclick='sortTable(3, \"specialistTable\")'>Tel. No.</th>";
        $str .= "<th onclick='sortTable(4, \"specialistTable\")'>No. Current Problems</th>";
        $str .= "<th onclick='sortTable(5, \"specialistTable\")'>No. Completed Problems </br> (over last month)</th>";
        $str .= "<th onclick='sortTable(6, \"specialistTable\")'>Personnel ID</th>";
        $str .= "</tr></thead><tbody>";
        $j = 0;
        while($row = $result->fetch_assoc()) {
            //This goes through the results and prints the row, and it accesses the second query by running the variable $j around as an index.
            $str .= "<tr><td>".$row["SpecialistID"]."</td><td>".$row["Name"]."</td><td>".$row["Specialism"]."</td><td>".$row["TelNo"]."</td><td>"."</td><td>".$row["Current Problems"]."</td><td>";
            $str .= $rows2[$j]."</td><td>".$row["PersonnelID"]."</td></tr>";
            $j++;
        }
        $str .= '</tbody></table>';
        echo $str;
    } else if($type == "hardware"){
        $str .= "<table id='hardwareTable'><thead><tr>";
        $str .= "<th onclick='sortTable(0, \"hardwareTable\")'>Hardware ID</th>";
        $str .= "<th onclick='sortTable(1, \"hardwareTable\")'>Hardware Type</th>";
        $str .= "<th onclick='sortTable(2, \"hardwareTable\")'>Hardware Make</th>";
        $str .= "<th onclick='sortTable(3, \"hardwareTable\")'>Serial No.</th>";
        $str .= "</tr></thead><tbody>";
        while($row = $result->fetch_assoc()) {
            $str .= "<tr><td>".$row["HardwareID"]."</td><td>".$row["HardwareType"]."</td><td>".$row["Make"]."</td><td>".$row["SerialNo"]."</td><td>"."</td></tr>";
        }
        $str .= '</tbody></table>';
        echo $str;
    } else if($type == "software"){
        $str .= "<table id='softwareTable'><thead><tr>";
        $str .= "<th onclick='sortTable(0, \"softwareTable\")'>Software ID</th>";
        $str .= "<th onclick='sortTable(1, \"softwareTable\")'>Software Name</th>";
        $str .= "<th onclick='sortTable(2, \"softwareTable\")'>Licenced?</th>";
        $str .= "<th onclick='sortTable(3, \"softwareTable\")'>Supported?</th>";
        $str .= "</tr></thead><tbody>";
        while($row = $result->fetch_assoc()) {
            $str .= "<tr><td>".$row["SoftwareID"]."</td><td>".$row["SoftwareName"]."</td><td>".$row["SoftwareLicenced"]."</td><td>".$row["SoftwareSupported"]."</td></tr>";
        }
        $str .= '</tbody></table>';
        echo $str;
    }
} else {
    echo "0 results";
}
$conn->close();
?>