function loadDepartments($cid, $id)
{
// subdepartments
$departments = array();
if ($id == -1) {
$request = "SELECT * FROM department WHERE cid = {$cid} AND did IS NULL";
} else {
$request = "SELECT * FROM department WHERE cid = {$cid} and did = {$id}";
}
$result = mysql_query($request);
$count = mysql_num_rows($result);
while ($row = mysql_fetch_object($result)) {
$department = new Department();
$department->setId($row->id);
$department->setName($row->name);
$department->setDepartments(loadDepartments($cid, $row->id));
$employees = loadEmployees($cid, $row->id);
$department->setEmployees($employees);
$inconsistent = containsManager($employees);
if ($inconsistent == true) {
$department->setInconsistent(true);
$department->setMessage("No Manager!");
}
$departments[] = $department;
}
return $departments;
}
/**
* Start browsing the directory and register files
*
* @return Exception code = 400 if directory non-existent
* An array containing list of non-imported files if successful
*/
public function doImport()
{
if (!file_exists($this->_dir)) {
throw new Exception("directory non-existent", 400);
}
$errArr = array();
$handler = opendir($this->_dir);
// TODO: does not do recrusive listing, do we need that?
while (false !== ($file = readdir($handler))) {
if ($file != '.' && $file != '..') {
$err = false;
$pos = strrpos($file, '.');
$fileName = strtoupper(substr($file, 0, $pos));
$token = strtok($fileName, '_');
$counter = 0;
while (false !== $token) {
switch ($counter) {
case 0:
if (strlen($token) != 7) {
$err = true;
}
$rawCourseCode = $token;
break;
case 1:
if ($token != substr($this->_year, 0, 4)) {
$err = true;
}
break;
case 2:
if ($token != "EXAM") {
if (substr($token, 0, 5) == "EXAM(") {
// name could have the following syntax: AER205S_2009_EXAM(2).pdf
$count = strtok($token, '(');
$count = strtok('(');
$count = strtok($count, ')');
if ($count === false || !is_numeric($count)) {
$err = true;
}
} else {
$err = true;
}
}
break;
}
$token = strtok("_");
$counter++;
}
if ($counter != 3 || $err) {
$err = true;
} else {
// assume course code is 7 chars in length with the last char being either S, F or Y
$part1 = substr($rawCourseCode, 0, 6);
//e.g. AER205
$part2 = substr($rawCourseCode, 6, 1);
//e.g. F
switch ($part2) {
case "F":
case "S":
$courseCode = $part1 . "H1";
$descr = $part1 . " " . $this->_year . " Official Exam" . (isset($count) ? ' (' . $count . ')' : '');
break;
case "Y":
$courseCode = $part1 . "Y1";
$descr = $part1 . " " . $this->_year . " Official Exam" . (isset($count) ? ' (' . $count . ')' : '');
break;
default:
$err = true;
break;
}
if (!$err) {
$conn = Propel::getConnection();
// check if we have exam of this descr already
$examArr = ExamPeer::getExamsForYearAndCourseId($courseCode, $this->_year, $conn);
foreach ($examArr as $ex) {
if ($ex->getType() == EnumItemPeer::EXAM && $ex->getDescr() == $descr) {
$err = true;
break;
}
}
if (!$err) {
// first check if course exists
$course = CoursePeer::retrieveByPK($courseCode, $conn);
if (!isset($course)) {
$course = new Course();
//$course->setDeptId(substr($courseCode, 0, 3));
$course->setDescr($courseCode);
$course->setIsEng(1);
$course->setId($courseCode);
$dept = DepartmentPeer::retrieveByPK(substr($courseCode, 0, 3), $conn);
if (!isset($dept)) {
$dept = new Department();
$dept->setId(substr($courseCode, 0, 3));
$dept->setDescr(substr($courseCode, 0, 3));
$dept->save($conn);
}
$course->setDepartment($dept);
$course->save($conn);
}
// register exam
$exam = new Exam();
$exam->setType(EnumItemPeer::EXAM);
$exam->setDescr($descr);
$exam->setCourseId($courseCode);
$exam->setFilePath($this->_dir . $file);
$exam->setYear($this->_year);
$exam->save();
}
}
}
if ($err) {
$errArr[] = $file;
}
}
}
closedir($handler);
return $errArr;
}
public function saveToDatabase()
{
if (isset($this->_infoArr) && isset($this->_mappingArr)) {
$err = "";
$conn = Propel::getConnection();
$dt = date("Y-m-d H:i:s");
$len = count($this->_infoArr);
for ($i = 0; $i < $len; $i++) {
$courseCode = substr($this->_infoArr[$i]["courseCode"], 0, 8);
$deptId = substr($courseCode, 0, 3);
$deptObj = DepartmentPeer::retrieveByPK($deptId, $conn);
if (!isset($deptObj)) {
// new department found, save to db
$deptObj = new Department();
$deptObj->setId($deptId);
$deptObj->setDescr($deptId);
$deptObj->save($conn);
}
$course = CoursePeer::retrieveByPK($courseCode, $conn);
if (!isset($course)) {
// new course found, save to db
$course = new Course();
$course->setDescr($this->_infoArr[$i]["courseName"]);
$course->setIsEng(1);
$course->setDeptId($deptId);
$course->setId($courseCode);
$course->save($conn);
} elseif ($course->getDescr() == $course->getId()) {
// exam importer registers course description as course id
// if we encounter this situation, amend it with the proper description
$course->setDescr($this->_infoArr[$i]["courseName"]);
$course->save($conn);
}
try {
$instr = InstructorPeer::findInstructorByName($this->_infoArr[$i]["instrFirstName"], $this->_infoArr[$i]["instrLastName"], $conn);
} catch (Exception $e) {
if ($e->getCode() == 1) {
// no instructor found
$instr = new Instructor();
$instr->setFirstName($this->_infoArr[$i]["instrFirstName"]);
$instr->setLastName($this->_infoArr[$i]["instrLastName"]);
$instr->setDeptId($course->getDeptId());
$instr->save($conn);
} else {
// TODO: big problem, duplicate instructors found
// log error and move on
continue;
}
}
// create CourseInstructorAssociation if it doesn't exist
try {
$assoc = CourseInstructorAssociationPeer::findForYearAndInstructorIdAndCourseId($this->_year, $course->getId(), $instr->getId(), $conn);
} catch (Exception $e) {
if ($e->getCode() == 1) {
// create new object
$assoc = new CourseInstructorAssociation();
$assoc->setYear($this->_year);
$assoc->setCourseId($course->getId());
$assoc->setInstructorId($instr->getId());
$assoc->save($conn);
} else {
// TODO: big problem, duplicate assocs found
// log error and move on
continue;
}
}
// enrolled and responded
if (isset($this->_infoArr[$i]["enrolled"])) {
$ratingObj = new AutoCourseRating();
$ratingObj->setFieldId(RatingFieldPeer::NUMBER_ENROLLED);
$ratingObj->setRating(0);
$ratingObj->setImportDt($dt);
$ratingObj->setNumber($this->_infoArr[$i]["enrolled"]);
$ratingObj->setCourseInsId($assoc->getId());
$ratingObj->save($conn);
}
if (isset($this->_infoArr[$i]["response"])) {
$ratingObj = new AutoCourseRating();
$ratingObj->setFieldId(RatingFieldPeer::NUMBER_RESPONDED);
$ratingObj->setRating(0);
$ratingObj->setImportDt($dt);
$ratingObj->setNumber($this->_infoArr[$i]["response"]);
$ratingObj->setCourseInsId($assoc->getId());
$ratingObj->save($conn);
}
// we can now save the real rating data
$ratingArr = $this->_ratingArr[$i];
foreach ($ratingArr as $fieldId => $data) {
foreach ($data as $rating => $number) {
$ratingObj = new AutoCourseRating();
$ratingObj->setFieldId($fieldId);
$ratingObj->setRating($rating);
$ratingObj->setNumber($number);
$ratingObj->setImportDt($dt);
$ratingObj->setCourseInsId($assoc->getId());
$ratingObj->save($conn);
}
}
}
} else {
throw new Exception("readCsv method has not been called.");
}
}
function loadDepartment($id)
{
// name
$request = "SELECT * FROM department WHERE id = " . $id;
$result = mysql_query($request);
$row = mysql_fetch_object($result);
$name = $row->name;
$parent = $row->did;
// departments
$unselectable = unselectable($id);
$departments = array();
$request = "SELECT * FROM department WHERE id NOT IN (" . $unselectable . ")";
$result = mysql_query($request);
while ($row = mysql_fetch_object($result)) {
$department["id"] = $row->id;
$department["name"] = $row->name;
if ($row->id == $parent) {
$department["parent"] = true;
} else {
$department["parent"] = false;
}
$departments[] = $department;
}
// employees
$employees = array();
$request = "SELECT * FROM employee";
$result = mysql_query($request);
while ($row = mysql_fetch_object($result)) {
$employee = array();
$employee["id"] = $row->id;
$employee["name"] = $row->name;
if ($row->manager == true && $row->did == $id) {
$employee["manager"] = true;
} else {
$employee["manager"] = false;
}
$employees[] = $employee;
}
// total
$total = totalDepartment($id);
// create department object
$department = new Department();
$department->setId($id);
$department->setDepartments($departments);
$department->setEmployees($employees);
$department->setName($name);
$department->setTotal($total);
// return department object
return $department;
}
