00001 <?php
00002
00015
00016
00017
00018 import('db.DAO');
00019
00020
00021 define('THESIS_FIELD_FIRSTNAME', 'student_first_name');
00022 define('THESIS_FIELD_LASTNAME', 'student_last_name');
00023 define('THESIS_FIELD_EMAIL', 'student_email');
00024 define('THESIS_FIELD_DEPARTMENT', 'department');
00025 define('THESIS_FIELD_UNIVERSITY', 'university');
00026 define('THESIS_FIELD_SUBJECT', 'subject');
00027 define('THESIS_FIELD_TITLE', 'title');
00028 define('THESIS_FIELD_ABSTRACT', 'abstract');
00029 define('THESIS_FIELD_NONE', null);
00030
00031
00032 define('THESIS_ORDER_SUBMISSION_DATE_ASC', 1);
00033 define('THESIS_ORDER_SUBMISSION_DATE_DESC', 2);
00034 define('THESIS_ORDER_APPROVAL_DATE_ASC', 3);
00035 define('THESIS_ORDER_APPROVAL_DATE_DESC', 4);
00036 define('THESIS_ORDER_LASTNAME_ASC', 5);
00037 define('THESIS_ORDER_LASTNAME_DESC', 6);
00038 define('THESIS_ORDER_TITLE_ASC', 7);
00039 define('THESIS_ORDER_TITLE_DESC', 8);
00040
00041 class ThesisDAO extends DAO {
00047 function &getThesis($thesisId) {
00048 $result = &$this->retrieve(
00049 'SELECT * FROM theses WHERE thesis_id = ?', $thesisId
00050 );
00051
00052 $returner = null;
00053 if ($result->RecordCount() != 0) {
00054 $returner = &$this->_returnThesisFromRow($result->GetRowAssoc(false));
00055 }
00056 $result->Close();
00057 return $returner;
00058 }
00059
00065 function &getMostRecentActiveThesisByJournalId($journalId) {
00066 $result = &$this->retrieve(
00067 'SELECT * FROM theses WHERE status = ? AND journal_id = ? ORDER BY date_submitted DESC, thesis_id DESC LIMIT 1', array(THESIS_STATUS_ACTIVE, $journalId)
00068 );
00069
00070 $returner = null;
00071 if ($result->RecordCount() != 0) {
00072 $returner = &$this->_returnThesisFromRow($result->GetRowAssoc(false));
00073 }
00074 $result->Close();
00075 return $returner;
00076 }
00077
00083 function getThesisJournalId($thesisId) {
00084 $result = &$this->retrieve(
00085 'SELECT journal_id FROM theses WHERE thesis_id = ?', $thesisId
00086 );
00087
00088 return isset($result->fields[0]) ? $result->fields[0] : 0;
00089 }
00090
00096 function isThesisActive($thesisId) {
00097 $thesisPlugin = &PluginRegistry::getPlugin('generic', 'ThesisPlugin');
00098 $thesisPlugin->import('Thesis');
00099
00100 $result = &$this->retrieve(
00101 'SELECT thesis_id FROM theses WHERE status = ? AND thesis_id = ?', array(THESIS_STATUS_ACTIVE, $thesisId)
00102 );
00103
00104 return isset($result->fields[0]) ? true : false;
00105 }
00106
00112 function &_returnThesisFromRow(&$row) {
00113 $thesisPlugin = &PluginRegistry::getPlugin('generic', 'ThesisPlugin');
00114 $thesisPlugin->import('Thesis');
00115
00116 $thesis = &new Thesis();
00117 $thesis->setThesisId($row['thesis_id']);
00118 $thesis->setJournalId($row['journal_id']);
00119 $thesis->setStatus($row['status']);
00120 $thesis->setDegree($row['degree']);
00121 $thesis->setDegreeName($row['degree_name']);
00122 $thesis->setDepartment($row['department']);
00123 $thesis->setUniversity($row['university']);
00124 $thesis->setDateApproved($this->dateFromDB($row['date_approved']));
00125 $thesis->setTitle($row['title']);
00126 $thesis->setAbstract($row['abstract']);
00127 $thesis->setUrl($row['url']);
00128 $thesis->setComment($row['comment']);
00129 $thesis->setStudentFirstName($row['student_first_name']);
00130 $thesis->setStudentMiddleName($row['student_middle_name']);
00131 $thesis->setStudentLastName($row['student_last_name']);
00132 $thesis->setStudentEmail($row['student_email']);
00133 $thesis->setStudentEmailPublish($row['student_email_publish']);
00134 $thesis->setStudentBio($row['student_bio']);
00135 $thesis->setSupervisorFirstName($row['supervisor_first_name']);
00136 $thesis->setSupervisorMiddleName($row['supervisor_middle_name']);
00137 $thesis->setSupervisorLastName($row['supervisor_last_name']);
00138 $thesis->setSupervisorEmail($row['supervisor_email']);
00139 $thesis->setDiscipline($row['discipline']);
00140 $thesis->setSubjectClass($row['subject_class']);
00141 $thesis->setSubject($row['subject']);
00142 $thesis->setCoverageGeo($row['coverage_geo']);
00143 $thesis->setCoverageChron($row['coverage_chron']);
00144 $thesis->setCoverageSample($row['coverage_sample']);
00145 $thesis->setMethod($row['method']);
00146 $thesis->setLanguage($row['language']);
00147 $thesis->setDateSubmitted($row['date_submitted']);
00148
00149 return $thesis;
00150 }
00151
00157 function insertThesis(&$thesis) {
00158 $ret = $this->update(
00159 sprintf('INSERT INTO theses
00160 (journal_id, status, degree, degree_name, department, university, date_approved, title, abstract, url, comment, student_first_name, student_middle_name, student_last_name, student_email, student_email_publish, student_bio, supervisor_first_name, supervisor_middle_name, supervisor_last_name, supervisor_email, discipline, subject_class, subject, coverage_geo, coverage_chron, coverage_sample, method, language, date_submitted)
00161 VALUES
00162 (?, ?, ?, ?, ?, ?, %s, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, %s)',
00163 $this->dateToDB($thesis->getDateApproved()), $this->datetimeToDB($thesis->getDateSubmitted())),
00164 array(
00165 $thesis->getJournalId(),
00166 $thesis->getStatus(),
00167 $thesis->getDegree(),
00168 $thesis->getDegreeName(),
00169 $thesis->getDepartment(),
00170 $thesis->getUniversity(),
00171 $thesis->getTitle(),
00172 $thesis->getAbstract(),
00173 $thesis->getUrl(),
00174 $thesis->getComment(),
00175 $thesis->getStudentFirstName(),
00176 $thesis->getStudentMiddleName(),
00177 $thesis->getStudentLastName(),
00178 $thesis->getStudentEmail(),
00179 $thesis->getStudentEmailPublish(),
00180 $thesis->getStudentBio(),
00181 $thesis->getSupervisorFirstName(),
00182 $thesis->getSupervisorMiddleName(),
00183 $thesis->getSupervisorLastName(),
00184 $thesis->getSupervisorEmail(),
00185 $thesis->getDiscipline(),
00186 $thesis->getSubjectClass(),
00187 $thesis->getSubject(),
00188 $thesis->getCoverageGeo(),
00189 $thesis->getCoverageChron(),
00190 $thesis->getCoverageSample(),
00191 $thesis->getMethod(),
00192 $thesis->getLanguage()
00193 )
00194 );
00195 $thesis->setThesisId($this->getInsertThesisId());
00196 return $thesis->getThesisId();
00197 }
00198
00204 function updateThesis(&$thesis) {
00205 return $this->update(
00206 sprintf('UPDATE theses
00207 SET
00208 journal_id = ?,
00209 status = ?,
00210 degree = ?,
00211 degree_name = ?,
00212 department = ?,
00213 university = ?,
00214 date_approved = %s,
00215 title = ?,
00216 abstract = ?,
00217 url = ?,
00218 comment = ?,
00219 student_first_name = ?,
00220 student_middle_name = ?,
00221 student_last_name = ?,
00222 student_email = ?,
00223 student_email_publish = ?,
00224 student_bio = ?,
00225 supervisor_first_name = ?,
00226 supervisor_middle_name = ?,
00227 supervisor_last_name = ?,
00228 supervisor_email = ?,
00229 discipline = ?,
00230 subject_class = ?,
00231 subject = ?,
00232 coverage_geo = ?,
00233 coverage_chron = ?,
00234 coverage_sample = ?,
00235 method = ?,
00236 language = ?,
00237 date_submitted = %s
00238 WHERE thesis_id = ?',
00239 $this->dateToDB($thesis->getDateApproved()), $this->datetimeToDB($thesis->getDateSubmitted())),
00240 array(
00241 $thesis->getJournalId(),
00242 $thesis->getStatus(),
00243 $thesis->getDegree(),
00244 $thesis->getDegreeName(),
00245 $thesis->getDepartment(),
00246 $thesis->getUniversity(),
00247 $thesis->getTitle(),
00248 $thesis->getAbstract(),
00249 $thesis->getUrl(),
00250 $thesis->getComment(),
00251 $thesis->getStudentFirstName(),
00252 $thesis->getStudentMiddleName(),
00253 $thesis->getStudentLastName(),
00254 $thesis->getStudentEmail(),
00255 $thesis->getStudentEmailPublish(),
00256 $thesis->getStudentBio(),
00257 $thesis->getSupervisorFirstName(),
00258 $thesis->getSupervisorMiddleName(),
00259 $thesis->getSupervisorLastName(),
00260 $thesis->getSupervisorEmail(),
00261 $thesis->getDiscipline(),
00262 $thesis->getSubjectClass(),
00263 $thesis->getSubject(),
00264 $thesis->getCoverageGeo(),
00265 $thesis->getCoverageChron(),
00266 $thesis->getCoverageSample(),
00267 $thesis->getMethod(),
00268 $thesis->getLanguage(),
00269 $thesis->getThesisId()
00270 )
00271 );
00272 }
00273
00279 function deleteThesis($thesis) {
00280 return $this->deleteThesisById($thesis->getThesisId());
00281 }
00282
00288 function deleteThesisById($thesisId) {
00289 return $this->update(
00290 'DELETE FROM theses WHERE thesis_id = ?', $thesisId
00291 );
00292 }
00293
00298 function deleteThesesByJournal($journalId) {
00299 return $this->update(
00300 'DELETE FROM theses WHERE journal_id = ?', $journalId
00301 );
00302 }
00303
00316 function &getThesesByJournalId($journalId, $searchType = null, $search = null, $searchMatch = null, $dateFrom = null, $dateTo = null, $resultOrder = null, $rangeInfo = null) {
00317 $paramArray = array((int) $journalId);
00318 $searchSql = '';
00319
00320 if (!empty($search)) switch ($searchType) {
00321 case THESIS_FIELD_FIRSTNAME:
00322 $searchSql = 'AND LOWER(student_first_name) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00323 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00324 break;
00325 case THESIS_FIELD_LASTNAME:
00326 $searchSql = 'AND LOWER(student_last_name) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00327 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00328 break;
00329 case THESIS_FIELD_EMAIL:
00330 $searchSql = 'AND LOWER(student_email) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00331 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00332 break;
00333 case THESIS_FIELD_DEPARTMENT:
00334 $searchSql = 'AND LOWER(department) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00335 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00336 break;
00337 case THESIS_FIELD_UNIVERSITY:
00338 $searchSql = 'AND LOWER(university) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00339 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00340 break;
00341 case THESIS_FIELD_TITLE:
00342 $searchSql = 'AND LOWER(title) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00343 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00344 break;
00345 case THESIS_FIELD_SUBJECT:
00346 $searchSql = 'AND LOWER(subject) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00347 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00348 break;
00349 case THESIS_FIELD_ABSTRACT:
00350 $searchSql = 'AND LOWER(abstract) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00351 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00352 break;
00353 }
00354
00355 if (!empty($dateFrom) || !empty($dateTo)) {
00356 if (!empty($dateFrom)) {
00357 $searchSql .= ' AND date_approved >= ' . $this->datetimeToDB($dateFrom);
00358 }
00359 if (!empty($dateTo)) {
00360 $searchSql .= ' AND date_approved <= ' . $this->datetimeToDB($dateTo);
00361 }
00362 }
00363
00364 switch ($resultOrder) {
00365 case THESIS_ORDER_SUBMISSION_DATE_ASC:
00366 $searchSql .= ' ORDER BY date_submitted ASC, thesis_id ASC';
00367 break;
00368 case THESIS_ORDER_SUBMISSION_DATE_DESC:
00369 $searchSql .= ' ORDER BY date_submitted DESC, thesis_id DESC';
00370 break;
00371 case THESIS_ORDER_APPROVAL_DATE_ASC:
00372 $searchSql .= ' ORDER BY date_approved ASC, student_last_name ASC, title ASC';
00373 break;
00374 case THESIS_ORDER_APPROVAL_DATE_DESC:
00375 $searchSql .= ' ORDER BY date_approved DESC, student_last_name ASC, title ASC';
00376 break;
00377 case THESIS_ORDER_LASTNAME_ASC:
00378 $searchSql .= ' ORDER BY student_last_name ASC, title ASC';
00379 break;
00380 case THESIS_ORDER_LASTNAME_DESC:
00381 $searchSql .= ' ORDER BY student_last_name DESC, title ASC';
00382 break;
00383 case THESIS_ORDER_TITLE_ASC:
00384 $searchSql .= ' ORDER BY title ASC, student_last_name ASC';
00385 break;
00386 case THESIS_ORDER_TITLE_DESC:
00387 $searchSql .= ' ORDER BY title DESC, student_last_name ASC';
00388 break;
00389 default:
00390 $searchSql .= ' ORDER BY date_submitted DESC, thesis_id DESC';
00391 }
00392
00393 $result = &$this->retrieveRange(
00394 'SELECT * FROM theses WHERE journal_id = ? ' . $searchSql,
00395 $paramArray,
00396 $rangeInfo
00397 );
00398
00399 $returner = &new DAOResultFactory($result, $this, '_returnThesisFromRow');
00400 return $returner;
00401 }
00402
00415 function &getActiveThesesByJournalId($journalId, $searchType = null, $search = null, $searchMatch = null, $dateFrom = null, $dateTo = null, $resultOrder = null, $rangeInfo = null) {
00416 $thesisPlugin = &PluginRegistry::getPlugin('generic', 'ThesisPlugin');
00417 $thesisPlugin->import('Thesis');
00418
00419 $paramArray = array(THESIS_STATUS_ACTIVE, (int) $journalId);
00420 $searchSql = '';
00421
00422 if (!empty($search)) switch ($searchType) {
00423 case THESIS_FIELD_FIRSTNAME:
00424 $searchSql = 'AND LOWER(student_first_name) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00425 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00426 break;
00427 case THESIS_FIELD_LASTNAME:
00428 $searchSql = 'AND LOWER(student_last_name) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00429 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00430 break;
00431 case THESIS_FIELD_EMAIL:
00432 $searchSql = 'AND LOWER(student_email) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00433 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00434 break;
00435 case THESIS_FIELD_DEPARTMENT:
00436 $searchSql = 'AND LOWER(department) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00437 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00438 break;
00439 case THESIS_FIELD_UNIVERSITY:
00440 $searchSql = 'AND LOWER(university) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00441 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00442 break;
00443 case THESIS_FIELD_TITLE:
00444 $searchSql = 'AND LOWER(title) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00445 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00446 break;
00447 case THESIS_FIELD_SUBJECT:
00448 $searchSql = 'AND LOWER(subject) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00449 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00450 break;
00451 case THESIS_FIELD_ABSTRACT:
00452 $searchSql = 'AND LOWER(abstract) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00453 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00454 break;
00455 }
00456
00457 if (!empty($dateFrom) || !empty($dateTo)) {
00458 if (!empty($dateFrom)) {
00459 $searchSql .= ' AND date_approved >= ' . $this->datetimeToDB($dateFrom);
00460 }
00461 if (!empty($dateTo)) {
00462 $searchSql .= ' AND date_approved <= ' . $this->datetimeToDB($dateTo);
00463 }
00464 }
00465
00466 switch ($resultOrder) {
00467 case THESIS_ORDER_SUBMISSION_DATE_ASC:
00468 $searchSql .= ' ORDER BY date_submitted ASC, thesis_id ASC';
00469 break;
00470 case THESIS_ORDER_SUBMISSION_DATE_DESC:
00471 $searchSql .= ' ORDER BY date_submitted DESC, thesis_id DESC';
00472 break;
00473 case THESIS_ORDER_APPROVAL_DATE_ASC:
00474 $searchSql .= ' ORDER BY date_approved ASC, student_last_name ASC, title ASC';
00475 break;
00476 case THESIS_ORDER_APPROVAL_DATE_DESC:
00477 $searchSql .= ' ORDER BY date_approved DESC, student_last_name ASC, title ASC';
00478 break;
00479 case THESIS_ORDER_LASTNAME_ASC:
00480 $searchSql .= ' ORDER BY student_last_name ASC, title ASC';
00481 break;
00482 case THESIS_ORDER_LASTNAME_DESC:
00483 $searchSql .= ' ORDER BY student_last_name DESC, title ASC';
00484 break;
00485 case THESIS_ORDER_TITLE_ASC:
00486 $searchSql .= ' ORDER BY title ASC, student_last_name ASC';
00487 break;
00488 case THESIS_ORDER_TITLE_DESC:
00489 $searchSql .= ' ORDER BY title DESC, student_last_name ASC';
00490 break;
00491 default:
00492 $searchSql .= ' ORDER BY date_submitted DESC, thesis_id DESC';
00493 }
00494
00495 $result = &$this->retrieveRange(
00496 'SELECT * FROM theses WHERE status = ? AND journal_id = ? ' . $searchSql,
00497 $paramArray,
00498 $rangeInfo
00499 );
00500
00501 $returner = &new DAOResultFactory($result, $this, '_returnThesisFromRow');
00502 return $returner;
00503 }
00504
00509 function getInsertThesisId() {
00510 return $this->getInsertId('theses', 'thesis_id');
00511 }
00512 }
00513
00514 ?>
