actions.php (14477B)
1 <?php 2 # TODO: Remove code duplication if time permits 3 # This is difficult to do because prepared 4 # statements have different inputs 5 session_start(); 6 7 $root = realpath($_SERVER['DOCUMENT_ROOT']); 8 include "$root/php/db.php"; 9 10 require('fpdf.php'); 11 12 # Form input passed in as POST variables 13 if ($_SERVER['REQUEST_METHOD'] == 'POST') { 14 $db = Database::get_connection(); 15 16 # Special variable passed in that tells what action to do 17 $action = $_POST['action']; 18 19 switch ($action) { 20 # Summaries for Students and Instructors 21 case 'my_summary': my_summary($db); 22 break; 23 case 'my_courses_form': my_courses($db); 24 break; 25 # Forms for Registrars/Admins 26 case 'add_faculty_form': add_faculty($db); 27 break; 28 case 'register_student_form': register_student($db); 29 break; 30 case 'assign_faculty_form': assign_faculty($db); 31 break; 32 case 'enroll_form': enroll($db); 33 break; 34 case 'drop_form': drop($db); 35 break; 36 case 'change_grade_form': change_grade($db); 37 break; 38 39 # Reports for Registars/Admins 40 case 'student_transcript_form': student_transcript($db); 41 break; 42 case 'class_list_form': class_list($db); 43 break; 44 case 'students_in_degree_form': students_in_degree($db); 45 break; 46 case 'students_instructors_form': students_instructors($db); 47 break; 48 case 'courses_taught_form': courses_taught($db); 49 break; 50 51 # User Management for Admins 52 case 'add_user_form': add_user($db); 53 break; 54 case 'delete_user_form': delete_user($db); 55 break; 56 case 'modify_user_form': modify_user($db); 57 break; 58 case 'view_users': view_users($db); 59 break; 60 } 61 } 62 63 function my_summary($db) { 64 $stmt = $db -> prepare("SELECT ID FROM Users WHERE UserName=?;"); 65 66 $stmt->bind_param('s', $_SESSION['user']); 67 68 $stmt->execute(); 69 70 $result = $stmt->get_result(); 71 72 while ($rows = mysqli_fetch_assoc($result)) 73 $uid = $rows['ID']; 74 75 $stmt = $db -> prepare("SELECT * FROM Enrollments WHERE StudentID=?;"); 76 77 $stmt->bind_param('i', $uid); 78 79 $stmt->execute(); 80 81 report($stmt, "Summary for $_SESSION[user]"); 82 } 83 84 function my_courses($db) { 85 $stmt = $db -> prepare("SELECT Enrollments.StudentID, Students.GivenName, Students.Surname, Enrollments.CourseCode, Enrollments.Section, Enrollments.Term, Enrollments.Year, Students.InCOOP FROM Enrollments, Students WHERE Enrollments.CourseCode = ? AND Enrollments.Section = ? AND Enrollments.Term = ? AND Enrollments.Year = ? AND Enrollments.StudentID = Students.StudentID ORDER BY Students.Surname;"); 86 87 # Convert course data into variables 88 $full_code = $_POST['course']; 89 $tokens = explode(' ', $full_code); 90 $array = array(); 91 $i = 0; 92 foreach($tokens as $token){ 93 $array[$i] = $token; 94 $i++; 95 } 96 $code = $array[0]; 97 $section = $array[1]; 98 $term = $array[2]; 99 $year = $array[3]; 100 101 $stmt->bind_param('sssi', $code, $section, $term, $year); 102 103 report($stmt, "Class List for $code$section-$term"); 104 } 105 106 # Forms 107 function add_faculty($db) { 108 $stmt = $db -> prepare("INSERT INTO Faculty (FacultyID, GivenName, Surname, HomePhoneNum) VALUES (?, ?, ?, ?);"); 109 110 $faculty_id = $_POST['faculty_id']; 111 $name = $_POST['name']; 112 $surname = $_POST['surname']; 113 $home_phone_num = $_POST['home_phone_num']; 114 115 $stmt->bind_param('isss', $faculty_id, $name, $surname, $home_phone_num); 116 117 $stmt->execute(); 118 119 $full_name = "$name $surname"; 120 121 $msg = ''; 122 # If table was actually modified, it means that the SQL query ran successfully 123 if ($db->affected_rows > 0) 124 $msg=$full_name.' added to faculty'; 125 else 126 $msg='Failed to add to faculty'; 127 128 echo $msg; 129 } 130 function register_student($db) { 131 $stmt = $db -> prepare("INSERT INTO Students (StudentID, GivenName, Surname, PhoneNumber, InCOOP, Degree) VALUES (?, ?, ?, ?, ?, ?);"); 132 133 $student_id = $_POST['student_id']; 134 $name = $_POST['name']; 135 $surname = $_POST['surname']; 136 $phone_num = $_POST['phone_num']; 137 $coop = $_POST['coop']; 138 $degree = $_POST['degree']; 139 140 $stmt->bind_param('isssis', $student_id, $name, $surname, $phone_num, $coop, $degree); 141 142 $stmt->execute(); 143 144 $full_name = "$name $surname"; 145 146 $msg = ''; 147 if ($db->affected_rows > 0) 148 $msg=$full_name.' registered into system'; 149 else 150 $msg='Failed to register into system'; 151 152 echo $msg; 153 } 154 function assign_faculty($db) { 155 $stmt = $db -> prepare("INSERT INTO Instructors (FacultyID, CourseCode, Section, Term, Year) VALUES (?, ?, ?, ?, ?);"); 156 157 $faculty_id = $_POST['faculty_id']; 158 $code = $_POST['code']; 159 $section = $_POST['section']; 160 $term = $_POST['term']; 161 $year = $_POST['year']; 162 163 $stmt->bind_param('isssi', $faculty_id, $code, $section, $term, $year); 164 165 $stmt->execute(); 166 167 $msg = ''; 168 if ($db->affected_rows > 0) { 169 $msg=$faculty_id." is now instructor for $code$section-$term"; 170 171 # Update Users table to reflect this change 172 $stmt = "UPDATE Users SET Role = 'Instructor' WHERE ID=$faculty_id;"; 173 $query = $db->query($stmt); 174 } 175 else 176 $msg="Failed to make instructor"; 177 178 echo $msg; 179 } 180 function enroll($db) { 181 $stmt = $db -> prepare("INSERT INTO Enrollments (StudentID, CourseCode, Section, Term, Year, Mark, Status) VALUES (?, ?, ?, ?, ?, 0, 'In Progress');"); 182 183 $student_id = $_POST['student_id']; 184 $code = $_POST['code']; 185 $section = $_POST['section']; 186 $term = $_POST['term']; 187 $year = $_POST['year']; 188 189 $stmt->bind_param('isssi', $student_id, $code, $section, $term, $year); 190 191 $stmt->execute(); 192 193 $msg = ''; 194 if ($db->affected_rows > 0) 195 $msg=$student_id." enrolled in $code$section-$term"; 196 else 197 $msg='Failed to enroll'; 198 199 echo $msg; 200 } 201 function drop($db) { 202 $stmt = $db -> prepare("DELETE FROM Enrollments WHERE StudentID = ? AND CourseCode = ? AND Section = ? AND Term = ? AND Year = ?;"); 203 204 $student_id = $_POST['student_id']; 205 $code = $_POST['code']; 206 $section = $_POST['section']; 207 $term = $_POST['term']; 208 $year = $_POST['year']; 209 210 $stmt->bind_param('isssi', $student_id, $code, $section, $term, $year); 211 212 $stmt->execute(); 213 214 $msg = ''; 215 if ($db->affected_rows > 0) 216 $msg=$student_id." dropped from $code$section-$term"; 217 else 218 $msg='Failed to drop'; 219 220 echo $msg; 221 } 222 function change_grade($db) { 223 $stmt = $db -> prepare("UPDATE Enrollments SET Mark = ?, STATUS = ? WHERE StudentID = ? AND CourseCode = ? AND Section = ? AND Term = ? AND Year = ?;"); 224 225 $grade = $_POST['grade']; 226 227 if ($grade < 50) 228 $status = 'Failed'; 229 else 230 $status = 'Passed'; 231 232 $student_id = $_POST['student_id']; 233 $code = $_POST['code']; 234 $section = $_POST['section']; 235 $term = $_POST['term']; 236 $year = $_POST['year']; 237 238 $stmt->bind_param('isisssi', $grade, $status, $student_id, $code, $section, $term, $year); 239 240 $stmt->execute(); 241 242 $msg = ''; 243 if ($db->affected_rows > 0) 244 $msg='Grade changed for '.$student_id; 245 else 246 $msg='Grade failed to be changed'; 247 248 echo $msg; 249 } 250 251 # Reports 252 function class_list($db) { 253 $stmt = $db -> prepare("SELECT Enrollments.StudentID, Students.GivenName, Students.Surname, Enrollments.CourseCode, Enrollments.Section, Enrollments.Term, Enrollments.Year, Students.InCOOP FROM Enrollments, Students WHERE Enrollments.CourseCode = ? AND Enrollments.Section = ? AND Enrollments.Term = ? AND Enrollments.Year = ? AND Enrollments.StudentID = Students.StudentID ORDER BY Students.Surname;"); 254 255 $code = $_POST['code']; 256 $section = $_POST['section']; 257 $term = $_POST['term']; 258 $year = $_POST['year']; 259 260 $stmt->bind_param('sssi', $code, $section, $term, $year); 261 262 report($stmt, "Class List for $code$section-$term"); 263 } 264 function student_transcript($db) { 265 $stmt = $db -> prepare("SELECT * FROM Enrollments WHERE StudentID=?;"); 266 267 $id = $_POST['id']; 268 269 $stmt->bind_param('i', $id); 270 271 report($stmt, "Summary for Student $id"); 272 } 273 function students_in_degree($db) { 274 $stmt = $db -> prepare("SELECT * FROM Students WHERE Students.Degree = ?;"); 275 276 $degree = $_POST['degree']; 277 278 $stmt->bind_param('s', $degree); 279 280 report($stmt, "Students in $degree"); 281 } 282 function students_instructors($db) { 283 $stmt = $db -> prepare("SELECT Instructors.FacultyID, Instructors.CourseCode, Faculty.GivenName, Faculty.Surname, Faculty.HomePhoneNum FROM Instructors, Faculty, Enrollments WHERE Instructors.FacultyID = Faculty.FacultyID AND Instructors.Term = ? AND Enrollments.StudentID = ? AND Instructors.CourseCode = Enrollments.CourseCode;"); 284 $student_id = $_POST['student_id']; 285 $term = $_POST['term']; 286 287 $stmt->bind_param('si', $term, $student_id); 288 289 report($stmt, "Instructors for Student $student_id in $term"); 290 } 291 function courses_taught($db) { 292 $stmt = $db -> prepare("SELECT Instructors.*, Faculty.GivenName, Faculty.Surname FROM Instructors, Faculty WHERE Instructors.FacultyID = Faculty.FacultyID AND Instructors.FacultyID = ? AND Instructors.Term = ? ORDER BY Instructors.CourseCode;"); 293 294 $faculty_id = $_POST['faculty_id']; 295 $term = $_POST['term']; 296 297 $stmt->bind_param('is', $faculty_id, $term); 298 299 report($stmt, "Courses Taught by Faculty Member $faculty_id in $term"); 300 } 301 302 function report($stmt, $title) { 303 # Execute SQL and get result 304 $stmt->execute(); 305 $result = $stmt->get_result(); 306 307 # Create PDF with one page (just one should suffice for our purposes) 308 $pdf = new FPDF(); 309 $pdf->AddPage(); 310 311 # Convert results into columnar array 312 $columns=array(); 313 while ($rows = mysqli_fetch_assoc($result)) { 314 $attrs = array_keys($rows); 315 for ($i = 0; $i < count($attrs); $i++) { 316 $temp = $rows[$attrs[$i]]; 317 $columns[$i]=$columns[$i].$temp."\n"; 318 } 319 } 320 321 # Display title 322 $pdf->SetFont('Arial','B',16); 323 $pdf->Cell(80); 324 $pdf->Cell(30, 10, $title, 0 , 0, 'C'); 325 $pdf->Ln(20); 326 327 $pdf->SetFont('Arial', 'B', 12); 328 329 # Print column attributes 330 for ($i = 0; $i < count($attrs); $i++) { 331 $pdf->SetY(20); 332 $pdf->SetX($i * 31); 333 $pdf->MultiCell(31, 10, $attrs[$i], 1); 334 } 335 336 # Print actual column values 337 for ($i = 0; $i < count($columns); $i++) { 338 $pdf->SetY(30); 339 $pdf->SetX($i * 31); 340 $pdf->MultiCell(31, 10, $columns[$i], 1); 341 } 342 343 # Save pdf to php server and echo URL 344 # URL is then opened in new tab by JavaScript 345 $pdf->Output('../report.pdf', 'F'); 346 $url = "http://".$_SERVER["HTTP_HOST"]."/report.pdf"; 347 echo $url; 348 } 349 350 # Manage Users 351 function add_user($db) { 352 $stmt = $db -> prepare("INSERT INTO Users (ID, UserName, Password, Role) VALUES (?, ?, ?, ?);"); 353 354 $uid = $_POST['uid']; 355 $user = $_POST['user']; 356 357 $pwd = $_POST['pwd']; 358 $pwd = password_hash($pwd, PASSWORD_BCRYPT); 359 360 $role = $_POST['role']; 361 362 $stmt->bind_param('isss', $uid, $user, $pwd, $role); 363 364 $stmt->execute(); 365 366 $msg = ''; 367 if ($db->affected_rows > 0) 368 $msg=$user.' added to the system'; 369 else 370 $msg='User failed to be added to the system'; 371 372 echo $msg; 373 } 374 function delete_user($db) { 375 $stmt = $db -> prepare("DELETE FROM Users WHERE ID = ?;"); 376 $uid = $_POST['uid']; 377 378 $stmt->bind_param('i', $uid); 379 380 $stmt->execute(); 381 382 $msg = ''; 383 if ($db->affected_rows > 0) 384 $msg="ID $uid deleted from the system"; 385 else 386 $msg='User failed to be removed from the system'; 387 388 echo $msg; 389 } 390 function modify_user($db) { 391 $stmt = $db -> prepare("UPDATE Users SET ID = ?, UserName = ?, Password = ?, Role = ? WHERE ID = ?"); 392 393 $uid = $_POST['uid']; 394 $user = $_POST['user']; 395 396 $pwd = $_POST['pwd']; 397 # Hash password using BCRYPT 398 # Admin does not actually know user's password --- this is good for security 399 $pwd = password_hash($pwd, PASSWORD_BCRYPT); 400 401 $role = $_POST['role']; 402 403 $stmt->bind_param('isssi', $uid, $user, $pwd, $role, $uid); 404 405 $stmt->execute(); 406 407 $msg = ''; 408 if ($db->affected_rows > 0) 409 $msg='Information changed for '.$uid; 410 else 411 $msg='Information failed to be changed'; 412 413 echo $msg; 414 } 415 function view_users($db) { 416 $stmt = $db -> prepare("SELECT ID, UserName, Role FROM Users;"); 417 418 report($stmt, 'Users'); 419 } 420 ?>