classes/submission/reviewAssignment/ReviewAssignmentDAO.inc.php

Go to the documentation of this file.
00001 <?php
00002 
00016 //$Id$
00017 
00018 import('submission.reviewAssignment.ReviewAssignment');
00019 
00020 class ReviewAssignmentDAO extends DAO {
00021    var $userDao;
00022    var $paperFileDao;
00023    var $suppFileDao;
00024    var $paperCommentsDao;
00025 
00029    function ReviewAssignmentDAO() {
00030       parent::DAO();
00031       $this->userDao =& DAORegistry::getDAO('UserDAO');
00032       $this->paperFileDao =& DAORegistry::getDAO('PaperFileDAO');
00033       $this->suppFileDao =& DAORegistry::getDAO('SuppFileDAO');
00034       $this->paperCommentDao =& DAORegistry::getDAO('PaperCommentDAO');
00035    }
00036 
00044    function &getReviewAssignment($paperId, $reviewerId, $stage) {
00045       $result =& $this->retrieve(
00046          'SELECT r.*, r2.review_revision, a.review_file_id, u.first_name, u.last_name FROM review_assignments r LEFT JOIN users u ON (r.reviewer_id = u.user_id) LEFT JOIN review_stages r2 ON (r.paper_id = r2.paper_id AND r.stage = r2.stage) LEFT JOIN papers a ON (r.paper_id = a.paper_id) WHERE r.paper_id = ? AND r.reviewer_id = ? AND r.cancelled <> 1 AND r.stage = ?',
00047          array((int) $paperId, (int) $reviewerId, (int) $stage)
00048          );
00049 
00050       $returner = null;
00051       if ($result->RecordCount() != 0) {
00052          $returner =& $this->_returnReviewAssignmentFromRow($result->GetRowAssoc(false));
00053       }
00054 
00055       $result->Close();
00056       unset($result);
00057 
00058       return $returner;
00059    }
00060 
00066    function &getReviewAssignmentById($reviewId) {
00067       $result =& $this->retrieve(
00068          'SELECT r.*, r2.review_revision, a.review_file_id, u.first_name, u.last_name FROM review_assignments r LEFT JOIN users u ON (r.reviewer_id = u.user_id) LEFT JOIN review_stages r2 ON (r.paper_id = r2.paper_id AND r.stage = r2.stage) LEFT JOIN papers a ON (r.paper_id = a.paper_id) WHERE r.review_id = ?',
00069          (int) $reviewId
00070          );
00071 
00072       $returner = null;
00073       if ($result->RecordCount() != 0) {
00074          $returner =& $this->_returnReviewAssignmentFromRow($result->GetRowAssoc(false));
00075       }
00076 
00077       $result->Close();
00078       unset($result);
00079 
00080       return $returner;
00081    }
00082 
00089    function &getReviewIndexesForStage($paperId, $stage) {
00090       $returner = array();
00091       $index = 0;
00092       $result =& $this->retrieve(
00093          'SELECT review_id FROM review_assignments WHERE paper_id = ? AND stage = ? AND (cancelled = 0 OR cancelled IS NULL) ORDER BY review_id',
00094          array((int) $paperId, (int) $stage)
00095          );
00096 
00097       while (!$result->EOF) {
00098          $row = $result->GetRowAssoc(false);
00099          $returner[$row['review_id']] = $index++;
00100          $result->MoveNext();
00101       }
00102 
00103       $result->Close();
00104       unset($result);
00105 
00106       return $returner;
00107    }
00108 
00109 
00115    function &getIncompleteReviewAssignments() {
00116       $reviewAssignments = array();
00117 
00118       $result =& $this->retrieve(
00119          'SELECT r.*, r2.review_revision, a.review_file_id, u.first_name, u.last_name FROM review_assignments r LEFT JOIN users u ON (r.reviewer_id = u.user_id) LEFT JOIN review_stages r2 ON (r.paper_id = r2.paper_id AND r.stage = r2.stage) LEFT JOIN papers a ON (r.paper_id = a.paper_id) WHERE (r.cancelled IS NULL OR r.cancelled = 0) AND r.date_notified IS NOT NULL AND r.date_completed IS NULL ORDER BY r.paper_id'
00120       );
00121 
00122       while (!$result->EOF) {
00123          $reviewAssignments[] =& $this->_returnReviewAssignmentFromRow($result->GetRowAssoc(false));
00124          $result->MoveNext();
00125       }
00126 
00127       $result->Close();
00128       unset($result);
00129 
00130       return $reviewAssignments;
00131    }
00132 
00138    function &getReviewAssignmentsByPaperId($paperId, $stage = null) {
00139       $reviewAssignments = array();
00140 
00141       $args = array((int) $paperId);
00142       if ($stage) $args[] = (int) $stage;
00143 
00144       $result =& $this->retrieve('
00145          SELECT   r.*,
00146             r2.review_revision,
00147             a.review_file_id,
00148             u.first_name,
00149             u.last_name
00150          FROM review_assignments r
00151             LEFT JOIN users u ON (r.reviewer_id = u.user_id)
00152             LEFT JOIN review_stages r2 ON (r.paper_id = r2.paper_id AND r.stage = r2.stage)
00153             LEFT JOIN papers a ON (r.paper_id = a.paper_id)
00154          WHERE r.paper_id = ? '
00155             . ($stage ? ' AND r.stage = ?':'')
00156          . ' ORDER BY ' . ($stage?'':' stage,') . 'review_id',
00157          $args
00158       );
00159 
00160       while (!$result->EOF) {
00161          $reviewAssignments[] =& $this->_returnReviewAssignmentFromRow($result->GetRowAssoc(false));
00162          $result->MoveNext();
00163       }
00164 
00165       $result->Close();
00166       unset($result);
00167 
00168       return $reviewAssignments;
00169    }
00170 
00176    function &getReviewAssignmentsByUserId($userId) {
00177       $reviewAssignments = array();
00178 
00179       $result =& $this->retrieve(
00180          'SELECT r.*,
00181             r2.review_revision,
00182             a.review_file_id,
00183             u.first_name,
00184             u.last_name
00185          FROM review_assignments r
00186             LEFT JOIN users u ON (r.reviewer_id = u.user_id)
00187             LEFT JOIN review_stages r2 ON (r.paper_id = r2.paper_id AND r.stage = r2.stage)
00188             LEFT JOIN papers a ON (r.paper_id = a.paper_id) WHERE r.reviewer_id = ?
00189          ORDER BY stage, review_id',
00190          (int) $userId
00191       );
00192 
00193       while (!$result->EOF) {
00194          $reviewAssignments[] =& $this->_returnReviewAssignmentFromRow($result->GetRowAssoc(false));
00195          $result->MoveNext();
00196       }
00197 
00198       $result->Close();
00199       unset($result);
00200 
00201       return $reviewAssignments;
00202    }
00203    
00209    function &getReviewAssignmentsByReviewFormId($reviewFormId) {
00210       $reviewAssignments = array();
00211 
00212       $result =& $this->retrieve(
00213          'SELECT r.*, r2.review_revision, a.review_file_id, u.first_name, u.last_name FROM review_assignments r LEFT JOIN users u ON (r.reviewer_id = u.user_id) LEFT JOIN review_stages r2 ON (r.paper_id = r2.paper_id AND r.stage = r2.stage) LEFT JOIN papers a ON (r.paper_id = a.paper_id) WHERE r.review_form_id = ? ORDER BY stage, review_id',
00214          (int) $reviewFormId
00215       );
00216 
00217       while (!$result->EOF) {
00218          $reviewAssignments[] =& $this->_returnReviewAssignmentFromRow($result->GetRowAssoc(false));
00219          $result->MoveNext();
00220       }
00221 
00222       $result->Close();
00223       unset($result);
00224 
00225       return $reviewAssignments;
00226    }
00227    
00233    function &getReviewFilesByStage($paperId) {
00234       $returner = array();
00235 
00236       $result =& $this->retrieve('
00237          SELECT a.*,
00238             r.stage as stage
00239          FROM review_stages r,
00240             paper_files a,
00241             papers art
00242          WHERE art.paper_id=r.paper_id
00243             AND r.paper_id=?
00244             AND r.paper_id=a.paper_id
00245             AND a.file_id=art.review_file_id
00246             AND a.revision=r.review_revision
00247             AND a.paper_id=r.paper_id', 
00248          (int) $paperId
00249       );
00250 
00251       while (!$result->EOF) {
00252          $row = $result->GetRowAssoc(false);
00253          $returner[$row['stage']] =& $this->paperFileDao->_returnPaperFileFromRow($row);
00254          $result->MoveNext();
00255       }
00256 
00257       $result->Close();
00258       unset($result);
00259 
00260       return $returner;
00261    }
00262 
00268    function &getAuthorViewableFilesByStage($paperId) {
00269       $files = array();
00270 
00271       $result =& $this->retrieve(
00272          'SELECT  f.*, a.reviewer_id AS reviewer_id
00273          FROM  review_assignments a,
00274             paper_files f
00275          WHERE reviewer_file_id = file_id
00276             AND viewable = 1
00277             AND a.paper_id = ?
00278          ORDER BY a.stage, a.reviewer_id, a.review_id', 
00279          array((int) $paperId)
00280       );
00281 
00282       while (!$result->EOF) {
00283          $row = $result->GetRowAssoc(false);
00284          if (!isset($files[$row['stage']]) || !is_array($files[$row['stage']])) {
00285             $files[$row['stage']] = array();
00286             $thisReviewerId = $row['reviewer_id'];
00287             $reviewerIndex = 0;
00288          }
00289          else if ($thisReviewerId != $row['reviewer_id']) {
00290             $thisReviewerId = $row['reviewer_id'];
00291             $reviewerIndex++;
00292          }
00293 
00294          $thisPaperFile =& $this->paperFileDao->_returnPaperFileFromRow($row);
00295          $files[$row['stage']][$reviewerIndex][] = $thisPaperFile;
00296          $result->MoveNext();
00297       }
00298 
00299       $result->Close();
00300       unset($result);
00301 
00302       return $files;
00303    }
00304 
00311    function &getLastModifiedByStage($paperId) {
00312       $returner = array();
00313 
00314       $result =& $this->retrieve(
00315          'SELECT stage, MAX(last_modified) as last_modified FROM review_assignments WHERE paper_id=? GROUP BY stage', 
00316          array((int) $paperId)
00317       );
00318 
00319       while (!$result->EOF) {
00320          $row = $result->GetRowAssoc(false);
00321          $returner[$row['stage']] = $this->datetimeFromDB($row['last_modified']);
00322          $result->MoveNext();
00323       }
00324 
00325       $result->Close();
00326       unset($result);
00327 
00328       return $returner;
00329    }
00330 
00337    function &getEarliestNotificationByStage($paperId) {
00338       $returner = array();
00339 
00340       $result =& $this->retrieve(
00341          'SELECT stage, MIN(date_notified) as earliest_date FROM review_assignments WHERE paper_id=? GROUP BY stage', 
00342          array((int) $paperId)
00343       );
00344 
00345       while (!$result->EOF) {
00346          $row = $result->GetRowAssoc(false);
00347          $returner[$row['stage']] = $this->datetimeFromDB($row['earliest_date']);
00348          $result->MoveNext();
00349       }
00350 
00351       $result->Close();
00352       unset($result);
00353 
00354       return $returner;
00355    }
00356 
00362    function &getCancelsAndRegrets($paperId) {
00363       $reviewAssignments = array();
00364 
00365       $result =& $this->retrieve(
00366          'SELECT r.*, r2.review_revision, a.review_file_id, u.first_name, u.last_name FROM review_assignments r LEFT JOIN users u ON (r.reviewer_id = u.user_id) LEFT JOIN review_stages r2 ON (r.paper_id = r2.paper_id AND r.stage = r2.stage) LEFT JOIN papers a ON (r.paper_id = a.paper_id) WHERE r.paper_id = ? AND (r.cancelled = 1 OR r.declined = 1) ORDER BY stage, review_id',
00367          (int) $paperId
00368       );
00369 
00370       while (!$result->EOF) {
00371          $reviewAssignments[] =& $this->_returnReviewAssignmentFromRow($result->GetRowAssoc(false));
00372          $result->MoveNext();
00373       }
00374 
00375       $result->Close();
00376       unset($result);
00377 
00378       return $reviewAssignments;
00379    }
00380 
00386    function &_returnReviewAssignmentFromRow(&$row) {
00387       $reviewAssignment = new ReviewAssignment();
00388       $reviewAssignment->setId($row['review_id']);
00389       $reviewAssignment->setPaperId($row['paper_id']);
00390       $reviewAssignment->setReviewerId($row['reviewer_id']);
00391       $reviewAssignment->setReviewerFullName($row['first_name'].' '.$row['last_name']);
00392       $reviewAssignment->setRecommendation($row['recommendation']);
00393       $reviewAssignment->setDateAssigned($this->datetimeFromDB($row['date_assigned']));
00394       $reviewAssignment->setDateNotified($this->datetimeFromDB($row['date_notified']));
00395       $reviewAssignment->setDateConfirmed($this->datetimeFromDB($row['date_confirmed']));
00396       $reviewAssignment->setDateCompleted($this->datetimeFromDB($row['date_completed']));
00397       $reviewAssignment->setDateAcknowledged($this->datetimeFromDB($row['date_acknowledged']));
00398       $reviewAssignment->setDateDue($this->datetimeFromDB($row['date_due']));
00399       $reviewAssignment->setLastModified($this->datetimeFromDB($row['last_modified']));
00400       $reviewAssignment->setDeclined($row['declined']);
00401       $reviewAssignment->setReplaced($row['replaced']);
00402       $reviewAssignment->setCancelled($row['cancelled']);
00403       $reviewAssignment->setReviewerFileId($row['reviewer_file_id']);
00404       $reviewAssignment->setQuality($row['quality']);
00405       $reviewAssignment->setDateRated($this->datetimeFromDB($row['date_rated']));
00406       $reviewAssignment->setDateReminded($this->datetimeFromDB($row['date_reminded']));
00407       $reviewAssignment->setReminderWasAutomatic($row['reminder_was_automatic']);
00408       $reviewAssignment->setStage($row['stage']);
00409       $reviewAssignment->setReviewFileId($row['review_file_id']);
00410       $reviewAssignment->setReviewRevision($row['review_revision']);
00411       $reviewAssignment->setReviewFormId($row['review_form_id']);
00412 
00413       // Files
00414       $reviewAssignment->setReviewFile($this->paperFileDao->getPaperFile($row['review_file_id'], $row['review_revision']));
00415       $reviewAssignment->setReviewerFile($this->paperFileDao->getPaperFile($row['reviewer_file_id']));
00416       $reviewAssignment->setReviewerFileRevisions($this->paperFileDao->getPaperFileRevisions($row['reviewer_file_id']));
00417       $reviewAssignment->setSuppFiles($this->suppFileDao->getSuppFilesByPaper($row['paper_id']));
00418 
00419 
00420       // Comments
00421       $reviewAssignment->setMostRecentPeerReviewComment($this->paperCommentDao->getMostRecentPaperComment($row['paper_id'], COMMENT_TYPE_PEER_REVIEW, $row['review_id']));
00422 
00423       HookRegistry::call('ReviewAssignmentDAO::_returnReviewAssignmentFromRow', array(&$reviewAssignment, &$row));
00424 
00425       return $reviewAssignment;
00426    }
00427 
00432    function insertReviewAssignment(&$reviewAssignment) {
00433       $this->update(
00434          sprintf('INSERT INTO review_assignments
00435             (paper_id, reviewer_id, stage, recommendation, declined, replaced, cancelled, date_assigned, date_notified, date_confirmed, date_completed, date_acknowledged, date_due, reviewer_file_id, quality, date_rated, last_modified, date_reminded, reminder_was_automatic, review_form_id)
00436             VALUES
00437             (?, ?, ?, ?, ?, ?, ?, %s, %s, %s, %s, %s, %s, ?, ?, %s, %s, %s, ?, ?)',
00438             $this->datetimeToDB($reviewAssignment->getDateAssigned()), $this->datetimeToDB($reviewAssignment->getDateNotified()), $this->datetimeToDB($reviewAssignment->getDateConfirmed()), $this->datetimeToDB($reviewAssignment->getDateCompleted()), $this->datetimeToDB($reviewAssignment->getDateAcknowledged()), $this->datetimeToDB($reviewAssignment->getDateDue()), $this->datetimeToDB($reviewAssignment->getDateRated()), $this->datetimeToDB($reviewAssignment->getLastModified()), $this->datetimeToDB($reviewAssignment->getDateReminded())),
00439          array(
00440             (int) $reviewAssignment->getPaperId(),
00441             (int) $reviewAssignment->getReviewerId(),
00442             max((int) $reviewAssignment->getStage(), 1),
00443             $reviewAssignment->getRecommendation(),
00444             (int) $reviewAssignment->getDeclined(),
00445             (int) $reviewAssignment->getReplaced(),
00446             (int) $reviewAssignment->getCancelled(),
00447             $reviewAssignment->getReviewerFileId(),
00448             $reviewAssignment->getQuality(),
00449             $reviewAssignment->getReminderWasAutomatic(),
00450             $reviewAssignment->getReviewFormId()
00451          )
00452       );
00453 
00454       $reviewAssignment->setId($this->getInsertReviewId());
00455       return $reviewAssignment->getId();
00456    }
00457 
00462    function updateReviewAssignment(&$reviewAssignment) {
00463       return $this->update(
00464          sprintf('UPDATE review_assignments
00465             SET   paper_id = ?,
00466                reviewer_id = ?,
00467                stage = ?,
00468                recommendation = ?,
00469                declined = ?,
00470                replaced = ?,
00471                cancelled = ?,
00472                date_assigned = %s,
00473                date_notified = %s,
00474                date_confirmed = %s,
00475                date_completed = %s,
00476                date_acknowledged = %s,
00477                date_due = %s,
00478                reviewer_file_id = ?,
00479                quality = ?,
00480                date_rated = %s,
00481                last_modified = %s,
00482                date_reminded = %s,
00483                reminder_was_automatic = ?,
00484                review_form_id = ?
00485             WHERE review_id = ?',
00486             $this->datetimeToDB($reviewAssignment->getDateAssigned()), $this->datetimeToDB($reviewAssignment->getDateNotified()), $this->datetimeToDB($reviewAssignment->getDateConfirmed()), $this->datetimeToDB($reviewAssignment->getDateCompleted()), $this->datetimeToDB($reviewAssignment->getDateAcknowledged()), $this->datetimeToDB($reviewAssignment->getDateDue()), $this->datetimeToDB($reviewAssignment->getDateRated()), $this->datetimeToDB($reviewAssignment->getLastModified()), $this->datetimeToDB($reviewAssignment->getDateReminded())),
00487          array(
00488             (int) $reviewAssignment->getPaperId(),
00489             (int) $reviewAssignment->getReviewerId(),
00490             (int) $reviewAssignment->getStage(),
00491             $reviewAssignment->getRecommendation(),
00492             (int) $reviewAssignment->getDeclined(),
00493             (int) $reviewAssignment->getReplaced(),
00494             (int) $reviewAssignment->getCancelled(),
00495             $reviewAssignment->getReviewerFileId(),
00496             $reviewAssignment->getQuality(),
00497             $reviewAssignment->getReminderWasAutomatic(),
00498             $reviewAssignment->getReviewFormId(),
00499             (int) $reviewAssignment->getId()
00500          )
00501       );
00502    }
00503 
00508    function deleteReviewAssignmentById($reviewId) {
00509       $reviewFormResponseDao =& DAORegistry::getDAO('ReviewFormResponseDAO');
00510       $reviewFormResponseDao->deleteByReviewId($reviewId);
00511 
00512       return $this->update(
00513          'DELETE FROM review_assignments WHERE review_id = ?',
00514          (int) $reviewId
00515       );
00516    }
00517 
00523    function deleteReviewAssignmentsByPaper($paperId) {
00524       $returner = false;
00525       $result =& $this->retrieve(
00526          'SELECT review_id FROM review_assignments WHERE paper_id = ?',
00527          (int) $paperId
00528       );
00529 
00530       while (!$result->EOF) {
00531          $row = $result->GetRowAssoc(false);
00532          $reviewId = $row['review_id'];
00533 
00534          $this->update('DELETE FROM review_form_responses WHERE review_id = ?', $reviewId);
00535          $this->update('DELETE FROM review_assignments WHERE review_id = ?', $reviewId);
00536 
00537          $result->MoveNext();
00538          $returner = true;
00539       }
00540       $result->Close();
00541       return $returner;
00542    }
00543 
00548    function getInsertReviewId() {
00549       return $this->getInsertId('review_assignments', 'review_id');
00550    }
00551 
00556    function getAverageQualityRatings($schedConfId) {
00557       $averageQualityRatings = Array();
00558       $result =& $this->retrieve(
00559          'SELECT R.reviewer_id, AVG(R.quality) AS average, COUNT(R.quality) AS count FROM review_assignments R, papers A WHERE R.paper_id = A.paper_id AND A.sched_conf_id = ? GROUP BY R.reviewer_id',
00560          (int) $schedConfId
00561          );
00562 
00563       while (!$result->EOF) {
00564          $row = $result->GetRowAssoc(false);
00565          $averageQualityRatings[$row['reviewer_id']] = array('average' => $row['average'], 'count' => $row['count']);
00566          $result->MoveNext();
00567       }
00568 
00569       $result->Close();
00570       unset($result);
00571 
00572       return $averageQualityRatings;
00573    }
00574 
00579    function getCompletedReviewCounts($schedConfId) {
00580       $returner = Array();
00581       $result =& $this->retrieve(
00582          'SELECT r.reviewer_id, COUNT(r.review_id) AS count FROM review_assignments r, papers a WHERE r.paper_id = a.paper_id AND a.sched_conf_id = ? AND r.date_completed IS NOT NULL AND r.cancelled = 0 GROUP BY r.reviewer_id',
00583          (int) $schedConfId
00584       );
00585 
00586       while (!$result->EOF) {
00587          $row = $result->GetRowAssoc(false);
00588          $returner[$row['reviewer_id']] = $row['count'];
00589          $result->MoveNext();
00590       }
00591 
00592       $result->Close();
00593       unset($result);
00594 
00595       return $returner;
00596    }
00597 }
00598 
00599 ?>

Generated on 25 Jul 2013 for Open Conference Systems by  doxygen 1.4.7