public function insertNews() {
$db = new DataBase();
//$db->setClassName(get_called_class());
$news = [];
foreach ($this->data['news'] as $key => $value) {
$news[] = '(' . $key . ', ' . $value . ')';
}
$sql = 'INSERT INTO ' . $this->table . '(Owner, Post) VALUES ' . implode(', ', array_values($news)) . ' ON DUPLICATE KEY UPDATE Owner = Owner';
return $db->execute($sql);
}
static function getPatternByProgram($program)
{
$pattern = new Pattern();
$db = new DataBase("SchedulerDatabase");
$getProgramPattern = "SELECT * FROM Patterns \n\t\t\t\t\t\t\t\t\tWHERE ProgramID = '{$program}'\n\t\t\t\t\t\t\t\t\tORDER BY YearRequired, TermRequired, SubjectID;";
$rows = $db->execute($getProgramPattern);
while ($row = $rows->fetch_object()) {
$newItem = new PatternItem($row->ProgramID, $row->CourseType, $row->YearRequired, $row->TermRequired, $row->SubjectID, $row->CourseNumber);
$pattern->addItem($newItem);
}
return $pattern;
}
public function updateRow()
{
$cols = [];
$data = [];
$db = new DataBase();
foreach ($this->data as $k => $v) {
$data[':' . $k] = $v;
if ('id' == $k) {
continue;
}
$cols[] = $k . '=:' . $k;
}
$sql = ' UPDATE ' . static::$table . ' SET ' . implode(', ', $cols) . ' WHERE id=:id';
return $db->execute($sql, $data);
}
function getLabs()
{
$db = new DataBase("SchedulerDatabase");
$labs = new SectionList();
$subID = $this->subjectID;
$CN = $this->courseNum;
$term = $this->term;
$sectionLetter = trim($this->sectionCode);
$sqlquery = "SELECT * FROM Section\n\t\t\t\t\t\t\tWHERE SubjectID LIKE '%{$subID}%'\n\t\t\t\t\t\t\t\t AND CourseNumber LIKE '%{$CN}%'\n\t\t\t\t\t\t\t\t AND Term LIKE '%{$term}%'\n\t\t\t\t\t\t\t\t AND (SectionCode LIKE '%" . $sectionLetter . "_%' OR SectionCode LIKE '%L_%')\n\t\t\t\t\t\t\t\t AND (NumberOfStudents < Capacity);";
$rows = $db->execute($sqlquery);
while ($row = $rows->fetch_object()) {
$newItem = new Section($row->SubjectID, $row->CourseNumber, $row->Year, $row->Term, $row->Title, $row->Credits, $row->ScheduleCode, $row->SectionCode, $row->Time, $row->Days, $row->Capacity, $row->NumberOfStudents);
$labs->addItem($newItem);
}
return $labs;
}
public function dropDb()
{
$db = new DataBase($this->_dbname);
$sql = "DROP DATABASE IF EXISTS `" . $this->_dbname . "`;";
if ($db->setQuery($sql)) {
$output = $db->execute();
$db->freeResults();
if ($output == null) {
return false;
} else {
return true;
}
} else {
return false;
}
}
function batchUpdate($maxRows = 500)
{
$db = new DataBase();
$db->connect();
$query = new Query($this->__table__, "update");
$query->batchMode = TRUE;
$firstElement = $this->__elements__[0];
$query->keys = array_keys($firstElement->__buildVars__());
$e = 0;
$query->extendedtype = "IGNORE";
$query->values = array();
foreach ($this->__elements__ as $elmnt) {
//$elmnt->__buildVars__();
$executed = false;
$query->values[] = $elmnt;
//echo join(",",$elmnt->__class_vars__)."<br/>";
if ($e > 0 && $e % $maxRows == 0) {
echo $query->build();
// $res = $db->execute($query);
$query->values = array();
$executed = true;
}
$e++;
}
//echo $query->build();
if (!$executed) {
$res = $db->execute($query);
}
//echo $elmnt->save();
}
} else {
header("content-type: text/plain");
echo "success-onsched=";
echo $filename;
}
exit;
}
exit;
case "GetPattern":
//returns pattern of program specified in XML format
$program = $_POST['program'];
header("content-type: text/xml");
$pat = new Pattern();
//Select all courses within the pattern table whose program is equal to the one specified by user
$getProgramPattern = "SELECT * FROM Patterns \n\t\t\t\t\t\t\t\t\tWHERE ProgramID = '{$program}';";
$rows = $db->execute($getProgramPattern);
while ($row = $rows->fetch_object()) {
//Add all courses found in query to list
$newItem = new PatternItem($row->ProgramID, $row->CourseType, $row->YearRequired, $row->TermRequired, $row->SubjectID, $row->CourseNumber);
$pat->addItem($newItem);
}
//Return XML of pattern to client-side
echo $pat->exportXML();
exit;
case "GetPrograms":
//Retrieves all academic programs in database and returns them to client-side as XML string
$getPrograms = "SELECT * FROM AcademicPrograms;";
$rows = $db->execute($getPrograms);
$returnval = "<programs>";
while ($row = $rows->fetch_object()) {
$returnval .= "<program><ProgramID>" . $row->ProgramID . "</ProgramID>\n\t\t\t\t\t\t\t <ProgramCode>" . $row->ProgramCode . "</ProgramCode></program>";
$coursesObject = simplexml_load_string($courseList);
//load xml string of courses into XML object
$Sections = new SectionList();
//List of sections that have already been updated, will be used in case one section fails to update, will revert
$SectionsUpdated = new SectionList();
//Create section list out of courses return by client
foreach ($coursesObject->children() as $course) {
$Sections->addItem(new Section($course->children()[0]->__toString(), $course->children()[1]->__toString(), $course->children()[2]->__toString(), $course->children()[3]->__toString(), $course->children()[4]->__toString(), $course->children()[5]->__toString(), $course->children()[6]->__toString(), $course->children()[7]->__toString(), $course->children()[8]->__toString(), $course->children()[9]->__toString(), $course->children()[10]->__toString(), $course->children()[11]->__toString()));
}
//Query to lock table during transaction
$lockSectionTable = "LOCK TABLE Section WRITE;";
//Query to unlock table once completed transaction
$unlockSectionTable = "UNLOCK TABLES;";
$revert = false;
//boolean to flag if revert required
$db->execute($lockSectionTable);
//lock DB
for ($i = 0; $i < count($Sections->SectionItems) && $revert == false; $i = $i + 1) {
$subID = $Sections->itemAt($i)->subjectID;
$CN = $Sections->itemAt($i)->courseNum;
$SC = $Sections->itemAt($i)->sectionCode;
$year = $Sections->itemAt($i)->year;
$term = $Sections->itemAt($i)->term;
//Query to retrieve given course, used to check that course is not already full
$getSection = "SELECT * FROM Section \n\t\t\t\t\t\tWHERE SubjectID LIKE '%{$subID}%' AND \n\t\t\t\t\t\tCourseNumber LIKE '%{$CN}%' AND \n\t\t\t\t\t\tSectionCode = '{$SC}' AND\n\t\t\t\t\t\tYear = {$year} AND\n\t\t\t\t\t\tTerm LIKE '%{$term}%';";
$row = $db->execute($getSection);
//get section to be updated
if ($row->num_rows != 1) {
$revert = true;
} else {
$sec = $row->fetch_object();
//Create Database
require_once "classes/database.php";
//Connect to database file
$db = new DataBase("");
//Connect to server
if ($db->execute($createDB)) {
//Create database in non exist
//echo "Successfully Created Database<br/>";
} else {
echo "Error Creating Database: " . $db->getError() . "<br/>";
exit;
}
$db = new DataBase("{$dbName}");
//Connect to database created
/*-- Create tables --*/
if ($db->execute($CreateSectionTable)) {
//echo "Successfully Created Section Table<br/>";
} else {
echo "Error Creating Section Table: " . $db->getError() . "<br/>";
exit;
}
if ($db->execute($CreateProgramsTable)) {
//echo "Successfully Created Academic Programs Table<br/>";
} else {
echo "Error Creating Academic Programs Table: " . $db->getError() . "<br/>";
exit;
}
if ($db->execute($CreatePatternsTable)) {
//echo "Successfully Created Pattern Table<br/>";
} else {
echo "Error Creating Pattern Table: " . $db->getError() . "<br/>";
function calculateCourses()
{
$db = new DataBase("SchedulerDatabase");
//Arrays to keep track of courses not taken year 1-4
$coursesNotTaken1 = $this->coursesNotTaken(1);
$coursesNotTaken2 = $this->coursesNotTaken(2);
$coursesNotTaken3 = $this->coursesNotTaken(3);
$coursesNotTaken4 = $this->coursesNotTaken(4);
$yearsCompleted = 0;
for ($i = 1; $i < 5; $i++) {
if ($this->completedYear($i)) {
$yearsCompleted++;
} else {
$i = 5;
//if does not complete previous year, break out of loop because impossible to complete years after
}
}
if (count($coursesNotTaken1) > 0) {
//If the student hasn't completed year 1
for ($i = 0; $i < count($coursesNotTaken1); $i++) {
//For every course not yet taken in that year
if ($this->numCourses == 5) {
//if reached max amount of courses to be taken exit funtion
return;
}
//If you have the prerequistes and the course if for the proper term then add it to list
if ($this->hasPrereq($yearsCompleted, $coursesNotTaken1[$i]->subjectID, $coursesNotTaken1[$i]->courseNumber)) {
$this->numCourses++;
//increment number of courses added
$subID = $coursesNotTaken1[$i]->subjectID;
$CN = $coursesNotTaken1[$i]->courseNumber;
$term = $this->term;
$getSections = "SELECT * FROM Section WHERE\n\t\t\t\t\t\t\t\t\t\tTerm LIKE '%{$term}%'\n\t\t\t\t\t\t\t\t\t\tAND SubjectID LIKE '%{$subID}%'\n\t\t\t\t\t\t\t\t\t\tAND CourseNumber LIKE '%{$CN}%'\n\t\t\t\t\t\t\t\t\t\tAND ScheduleCode LIKE '%LEC%'\n\t\t\t\t\t\t\t\t\t\tAND NumberOfStudents < Capacity;";
$rows = $db->execute($getSections);
while ($row = $rows->fetch_object()) {
$newItem = new Section($row->SubjectID, $row->CourseNumber, $row->Year, $row->Term, $row->Title, $row->Credits, $row->ScheduleCode, $row->SectionCode, $row->Time, $row->Days, $row->Capacity, $row->NumberOfStudents);
$this->courses->addItem($newItem);
}
//end while
}
//end if
}
//end for
}
//end if
if ($this->numCourses == 5) {
//if reached max amount of courses to be taken exit funtion
return;
}
if (count($coursesNotTaken2) > 0) {
//If the student hasn't completed year 2
for ($i = 0; $i < count($coursesNotTaken2); $i++) {
//For every course not yet taken in that year
if ($this->numCourses == 5) {
//if reached max amount of courses to be taken exit funtion
return;
}
//If you have the prerequistes and the course if for the proper term then add it to list
if ($this->hasPrereq($yearsCompleted, $coursesNotTaken2[$i]->subjectID, $coursesNotTaken2[$i]->courseNumber)) {
$this->numCourses++;
//increment number of courses added
$subID = $coursesNotTaken2[$i]->subjectID;
$CN = $coursesNotTaken2[$i]->courseNumber;
$term = $this->term;
$getSections = "SELECT * FROM Section WHERE\n\t\t\t\t\t\t\t\t\t\tTerm LIKE '%{$term}%'\n\t\t\t\t\t\t\t\t\t\tAND SubjectID LIKE '%{$subID}%'\n\t\t\t\t\t\t\t\t\t\tAND CourseNumber LIKE '%{$CN}%'\n\t\t\t\t\t\t\t\t\t\tAND ScheduleCode LIKE '%LEC%'\n\t\t\t\t\t\t\t\t\t\tAND NumberOfStudents < Capacity;";
$rows = $db->execute($getSections);
while ($row = $rows->fetch_object()) {
$newItem = new Section($row->SubjectID, $row->CourseNumber, $row->Year, $row->Term, $row->Title, $row->Credits, $row->ScheduleCode, $row->SectionCode, $row->Time, $row->Days, $row->Capacity, $row->NumberOfStudents);
$this->courses->addItem($newItem);
}
//end while
}
//end if
}
//end for
}
//end if
if ($this->numCourses == 5) {
//if reached max amount of courses to be taken exit funtion
return;
}
if (count($coursesNotTaken3) > 0) {
//If the student hasn't completed year 3
for ($i = 0; $i < count($coursesNotTaken3); $i++) {
//For every course not yet taken in that year
if ($this->numCourses == 5) {
//if reached max amount of courses to be taken exit funtion
return;
}
//If you have the prerequistes and the course if for the proper term then add it to list
if ($this->hasPrereq($yearsCompleted, $coursesNotTaken3[$i]->subjectID, $coursesNotTaken3[$i]->courseNumber)) {
$this->numCourses++;
//increment number of courses added
$subID = $coursesNotTaken3[$i]->subjectID;
$CN = $coursesNotTaken3[$i]->courseNumber;
$term = $this->term;
$getSections = "SELECT * FROM Section WHERE\n\t\t\t\t\t\t\t\t\t\tTerm LIKE '%{$term}%'\n\t\t\t\t\t\t\t\t\t\tAND SubjectID LIKE '%{$subID}%'\n\t\t\t\t\t\t\t\t\t\tAND CourseNumber LIKE '%{$CN}%'\n\t\t\t\t\t\t\t\t\t\tAND ScheduleCode LIKE '%LEC%'\n\t\t\t\t\t\t\t\t\t\tAND NumberOfStudents < Capacity;";
$rows = $db->execute($getSections);
while ($row = $rows->fetch_object()) {
$newItem = new Section($row->SubjectID, $row->CourseNumber, $row->Year, $row->Term, $row->Title, $row->Credits, $row->ScheduleCode, $row->SectionCode, $row->Time, $row->Days, $row->Capacity, $row->NumberOfStudents);
$this->courses->addItem($newItem);
}
//end while
}
//end if
}
//end for
}
//end if
if ($this->numCourses == 5) {
//if reached max amount of courses to be taken exit funtion
return;
}
if (count($coursesNotTaken4) > 0) {
//If the student hasn't completed year 4
for ($i = 0; $i < count($coursesNotTaken4); $i++) {
//For every course not yet taken in that year
if ($this->numCourses == 5) {
//if reached max amount of courses to be taken exit funtion
return;
}
//If you have the prerequistes and the course if for the proper term then add it to list
if ($this->hasPrereq($yearsCompleted, $coursesNotTaken4[$i]->subjectID, $coursesNotTaken4[$i]->courseNumber)) {
$subID = $coursesNotTaken4[$i]->subjectID;
$CN = $coursesNotTaken4[$i]->courseNumber;
$term = $this->term;
$getSections = "SELECT * FROM Section WHERE\n\t\t\t\t\t\t\t\t\t\tTerm LIKE '%{$term}%'\n\t\t\t\t\t\t\t\t\t\tAND SubjectID LIKE '%{$subID}%'\n\t\t\t\t\t\t\t\t\t\tAND CourseNumber LIKE '%{$CN}%'\n\t\t\t\t\t\t\t\t\t\tAND ScheduleCode LIKE '%LEC%'\n\t\t\t\t\t\t\t\t\t\tAND NumberOfStudents < Capacity;";
$rows = $db->execute($getSections);
if (!is_null($rows)) {
$this->numCourses++;
//increment number of courses added
while ($row = $rows->fetch_object()) {
$newItem = new Section($row->SubjectID, $row->CourseNumber, $row->Year, $row->Term, $row->Title, $row->Credits, $row->ScheduleCode, $row->SectionCode, $row->Time, $row->Days, $row->Capacity, $row->NumberOfStudents);
$this->courses->addItem($newItem);
}
//end while
}
//end if
}
//end if
}
//end for
}
//end if
}
