00001 <?php
00002
00016
00017
00018
00019 import('submission.reviewAssignment.ReviewAssignment');
00020
00021 class ReviewAssignmentDAO extends DAO {
00022 var $userDao;
00023 var $articleFileDao;
00024 var $suppFileDao;
00025 var $articleCommentsDao;
00026
00030 function ReviewAssignmentDAO() {
00031 parent::DAO();
00032 $this->userDao = &DAORegistry::getDAO('UserDAO');
00033 $this->articleFileDao = &DAORegistry::getDAO('ArticleFileDAO');
00034 $this->suppFileDao = &DAORegistry::getDAO('SuppFileDAO');
00035 $this->articleCommentDao = &DAORegistry::getDAO('ArticleCommentDAO');
00036 }
00037
00044 function &getReviewAssignment($articleId, $reviewerId, $round) {
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_rounds r2 ON (r.article_id = r2.article_id AND r.round = r2.round) LEFT JOIN articles a ON (r.article_id = a.article_id) WHERE r.article_id = ? AND r.reviewer_id = ? AND r.cancelled <> 1 AND r.round = ?',
00047 array((int) $articleId, (int) $reviewerId, (int) $round)
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_rounds r2 ON (r.article_id = r2.article_id AND r.round = r2.round) LEFT JOIN articles a ON (r.article_id = a.article_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 &getReviewIndexesForRound($articleId, $round) {
00090 $returner = array();
00091 $index = 0;
00092 $result = &$this->retrieve(
00093 'SELECT review_id FROM review_assignments WHERE article_id = ? and round = ? AND (cancelled = 0 OR cancelled IS NULL) ORDER BY review_id',
00094 array((int) $articleId, (int) $round)
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_rounds r2 ON (r.article_id = r2.article_id AND r.round = r2.round) LEFT JOIN articles a ON (r.article_id = a.article_id) WHERE (r.cancelled IS NULL OR r.cancelled = 0) AND r.date_notified IS NOT NULL AND r.date_completed IS NULL AND r.declined <> 1 ORDER BY r.article_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 &getReviewAssignmentsByArticleId($articleId, $round = null) {
00139 $reviewAssignments = array();
00140
00141 if ($round == null) {
00142 $result = &$this->retrieve(
00143 '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_rounds r2 ON (r.article_id = r2.article_id AND r.round = r2.round) LEFT JOIN articles a ON (r.article_id = a.article_id) WHERE r.article_id = ? ORDER BY round, review_id',
00144 (int) $articleId
00145 );
00146 } else {
00147 $result = &$this->retrieve(
00148 '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_rounds r2 ON (r.article_id = r2.article_id AND r.round = r2.round) LEFT JOIN articles a ON (r.article_id = a.article_id) WHERE r.article_id = ? AND r.round = ? ORDER BY review_id',
00149 array((int) $articleId, (int) $round)
00150 );
00151 }
00152
00153 while (!$result->EOF) {
00154 $reviewAssignments[] = &$this->_returnReviewAssignmentFromRow($result->GetRowAssoc(false));
00155 $result->MoveNext();
00156 }
00157
00158 $result->Close();
00159 unset($result);
00160
00161 return $reviewAssignments;
00162 }
00163
00169 function &getReviewAssignmentsByUserId($userId) {
00170 $reviewAssignments = array();
00171
00172 $result = &$this->retrieve(
00173 '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_rounds r2 ON (r.article_id = r2.article_id AND r.round = r2.round) LEFT JOIN articles a ON (r.article_id = a.article_id) WHERE r.reviewer_id = ? ORDER BY round, review_id',
00174 (int) $userId
00175 );
00176
00177 while (!$result->EOF) {
00178 $reviewAssignments[] = &$this->_returnReviewAssignmentFromRow($result->GetRowAssoc(false));
00179 $result->MoveNext();
00180 }
00181
00182 $result->Close();
00183 unset($result);
00184
00185 return $reviewAssignments;
00186 }
00187
00193 function &getReviewAssignmentsByReviewFormId($reviewFormId) {
00194 $reviewAssignments = array();
00195
00196 $result = &$this->retrieve(
00197 '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_rounds r2 ON (r.article_id = r2.article_id AND r.round = r2.round) LEFT JOIN articles a ON (r.article_id = a.article_id) WHERE r.review_form_id = ? ORDER BY round, review_id',
00198 (int) $reviewFormId
00199 );
00200
00201 while (!$result->EOF) {
00202 $reviewAssignments[] =& $this->_returnReviewAssignmentFromRow($result->GetRowAssoc(false));
00203 $result->MoveNext();
00204 }
00205
00206 $result->Close();
00207 unset($result);
00208
00209 return $reviewAssignments;
00210 }
00211
00217 function &getReviewFilesByRound($articleId) {
00218 $returner = array();
00219
00220 $result = &$this->retrieve(
00221 'SELECT a.*, r.round as round from review_rounds r, article_files a, articles art where art.article_id=r.article_id and r.article_id=? and r.article_id=a.article_id and a.file_id=art.review_file_id and a.revision=r.review_revision and a.article_id=r.article_id',
00222 (int) $articleId
00223 );
00224
00225 while (!$result->EOF) {
00226 $row = $result->GetRowAssoc(false);
00227 $returner[$row['round']] = &$this->articleFileDao->_returnArticleFileFromRow($row);
00228 $result->MoveNext();
00229 }
00230
00231 $result->Close();
00232 unset($result);
00233
00234 return $returner;
00235 }
00236
00242 function &getAuthorViewableFilesByRound($articleId) {
00243 $files = array();
00244
00245 $result = &$this->retrieve(
00246 'select f.*, a.reviewer_id as reviewer_id, a.review_id from review_assignments a, article_files f where reviewer_file_id = file_id and viewable=1 and a.article_id=? order by a.round, a.reviewer_id, a.review_id',
00247 (int) $articleId
00248 );
00249
00250 while (!$result->EOF) {
00251 $row = $result->GetRowAssoc(false);
00252 if (!isset($files[$row['round']]) || !is_array($files[$row['round']])) {
00253 $files[$row['round']] = array();
00254 $this_reviewer_id = $row['reviewer_id'];
00255 $reviewer_index = 0;
00256 }
00257 else if ($this_reviewer_id != $row['reviewer_id']) {
00258 $this_reviewer_id = $row['reviewer_id'];
00259 $reviewer_index++;
00260 }
00261
00262 if (!isset($files[$row['round']][$reviewer_index]) || !is_array($files[$row['round']][$reviewer_index])) {
00263 $files[$row['round']][$reviewer_index] = array();
00264 }
00265 $thisArticleFile = &$this->articleFileDao->_returnArticleFileFromRow($row);
00266 $files[$row['round']][$reviewer_index][$row['review_id']] = $thisArticleFile;
00267 $result->MoveNext();
00268 }
00269
00270 $result->Close();
00271 unset($result);
00272
00273 return $files;
00274 }
00275
00282 function &getLastModifiedByRound($articleId) {
00283 $returner = array();
00284
00285 $result = &$this->retrieve(
00286 'SELECT round, MAX(last_modified) as last_modified FROM review_assignments WHERE article_id=? GROUP BY round',
00287 (int) $articleId
00288 );
00289
00290 while (!$result->EOF) {
00291 $row = $result->GetRowAssoc(false);
00292 $returner[$row['round']] = $this->datetimeFromDB($row['last_modified']);
00293 $result->MoveNext();
00294 }
00295
00296 $result->Close();
00297 unset($result);
00298
00299 return $returner;
00300 }
00301
00308 function &getEarliestNotificationByRound($articleId) {
00309 $returner = array();
00310
00311 $result = &$this->retrieve(
00312 'SELECT round, MIN(date_notified) as earliest_date FROM review_assignments WHERE article_id=? GROUP BY round',
00313 (int) $articleId
00314 );
00315
00316 while (!$result->EOF) {
00317 $row = $result->GetRowAssoc(false);
00318 $returner[$row['round']] = $this->datetimeFromDB($row['earliest_date']);
00319 $result->MoveNext();
00320 }
00321
00322 $result->Close();
00323 unset($result);
00324
00325 return $returner;
00326 }
00327
00333 function &getCancelsAndRegrets($articleId) {
00334 $reviewAssignments = array();
00335
00336 $result = &$this->retrieve(
00337 '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_rounds r2 ON (r.article_id = r2.article_id AND r.round = r2.round) LEFT JOIN articles a ON (r.article_id = a.article_id) WHERE r.article_id = ? AND (r.cancelled = 1 OR r.declined = 1) ORDER BY round, review_id',
00338 (int) $articleId
00339 );
00340
00341 while (!$result->EOF) {
00342 $reviewAssignments[] = &$this->_returnReviewAssignmentFromRow($result->GetRowAssoc(false));
00343 $result->MoveNext();
00344 }
00345
00346 $result->Close();
00347 unset($result);
00348
00349 return $reviewAssignments;
00350 }
00351
00357 function &_returnReviewAssignmentFromRow(&$row) {
00358 $reviewAssignment = &new ReviewAssignment();
00359 $reviewAssignment->setReviewId($row['review_id']);
00360 $reviewAssignment->setArticleId($row['article_id']);
00361 $reviewAssignment->setReviewerId($row['reviewer_id']);
00362 $reviewAssignment->setReviewerFullName($row['first_name'].' '.$row['last_name']);
00363 $reviewAssignment->setCompetingInterests($row['competing_interests']);
00364 $reviewAssignment->setRecommendation($row['recommendation']);
00365 $reviewAssignment->setDateAssigned($this->datetimeFromDB($row['date_assigned']));
00366 $reviewAssignment->setDateNotified($this->datetimeFromDB($row['date_notified']));
00367 $reviewAssignment->setDateConfirmed($this->datetimeFromDB($row['date_confirmed']));
00368 $reviewAssignment->setDateCompleted($this->datetimeFromDB($row['date_completed']));
00369 $reviewAssignment->setDateAcknowledged($this->datetimeFromDB($row['date_acknowledged']));
00370 $reviewAssignment->setDateDue($this->datetimeFromDB($row['date_due']));
00371 $reviewAssignment->setLastModified($this->datetimeFromDB($row['last_modified']));
00372 $reviewAssignment->setDeclined($row['declined']);
00373 $reviewAssignment->setReplaced($row['replaced']);
00374 $reviewAssignment->setCancelled($row['cancelled']);
00375 $reviewAssignment->setReviewerFileId($row['reviewer_file_id']);
00376 $reviewAssignment->setQuality($row['quality']);
00377 $reviewAssignment->setDateRated($this->datetimeFromDB($row['date_rated']));
00378 $reviewAssignment->setDateReminded($this->datetimeFromDB($row['date_reminded']));
00379 $reviewAssignment->setReminderWasAutomatic($row['reminder_was_automatic']);
00380 $reviewAssignment->setRound($row['round']);
00381 $reviewAssignment->setReviewFileId($row['review_file_id']);
00382 $reviewAssignment->setReviewRevision($row['review_revision']);
00383 $reviewAssignment->setReviewFormId($row['review_form_id']);
00384
00385
00386 $reviewAssignment->setReviewFile($this->articleFileDao->getArticleFile($row['review_file_id'], $row['review_revision']));
00387 $reviewAssignment->setReviewerFile($this->articleFileDao->getArticleFile($row['reviewer_file_id']));
00388 $reviewAssignment->setReviewerFileRevisions($this->articleFileDao->getArticleFileRevisions($row['reviewer_file_id']));
00389 $reviewAssignment->setSuppFiles($this->suppFileDao->getSuppFilesByArticle($row['article_id']));
00390
00391
00392
00393 $reviewAssignment->setMostRecentPeerReviewComment($this->articleCommentDao->getMostRecentArticleComment($row['article_id'], COMMENT_TYPE_PEER_REVIEW, $row['review_id']));
00394
00395 HookRegistry::call('ReviewAssignmentDAO::_returnReviewAssignmentFromRow', array(&$reviewAssignment, &$row));
00396
00397 return $reviewAssignment;
00398 }
00399
00404 function insertReviewAssignment(&$reviewAssignment) {
00405 $this->update(
00406 sprintf('INSERT INTO review_assignments
00407 (article_id, reviewer_id, round, competing_interests, 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)
00408 VALUES
00409 (?, ?, ?, ?, ?, ?, ?, ?, %s, %s, %s, %s, %s, %s, ?, ?, %s, %s, %s, ?, ?)',
00410 $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())),
00411 array(
00412 (int) $reviewAssignment->getArticleId(),
00413 (int) $reviewAssignment->getReviewerId(),
00414 max((int) $reviewAssignment->getRound(), 1),
00415 $reviewAssignment->getCompetingInterests(),
00416 $reviewAssignment->getRecommendation(),
00417 (int) $reviewAssignment->getDeclined(),
00418 (int) $reviewAssignment->getReplaced(),
00419 (int) $reviewAssignment->getCancelled(),
00420 $reviewAssignment->getReviewerFileId(),
00421 $reviewAssignment->getQuality(),
00422 $reviewAssignment->getReminderWasAutomatic(),
00423 $reviewAssignment->getReviewFormId()
00424 )
00425 );
00426
00427 $reviewAssignment->setReviewId($this->getInsertReviewId());
00428 return $reviewAssignment->getReviewId();
00429 }
00430
00435 function updateReviewAssignment(&$reviewAssignment) {
00436 return $this->update(
00437 sprintf('UPDATE review_assignments
00438 SET article_id = ?,
00439 reviewer_id = ?,
00440 round = ?,
00441 competing_interests = ?,
00442 recommendation = ?,
00443 declined = ?,
00444 replaced = ?,
00445 cancelled = ?,
00446 date_assigned = %s,
00447 date_notified = %s,
00448 date_confirmed = %s,
00449 date_completed = %s,
00450 date_acknowledged = %s,
00451 date_due = %s,
00452 reviewer_file_id = ?,
00453 quality = ?,
00454 date_rated = %s,
00455 last_modified = %s,
00456 date_reminded = %s,
00457 reminder_was_automatic = ?,
00458 review_form_id = ?
00459 WHERE review_id = ?',
00460 $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())),
00461 array(
00462 (int) $reviewAssignment->getArticleId(),
00463 (int) $reviewAssignment->getReviewerId(),
00464 (int) $reviewAssignment->getRound(),
00465 $reviewAssignment->getCompetingInterests(),
00466 $reviewAssignment->getRecommendation(),
00467 (int) $reviewAssignment->getDeclined(),
00468 (int) $reviewAssignment->getReplaced(),
00469 (int) $reviewAssignment->getCancelled(),
00470 $reviewAssignment->getReviewerFileId(),
00471 $reviewAssignment->getQuality(),
00472 $reviewAssignment->getReminderWasAutomatic(),
00473 $reviewAssignment->getReviewFormId(),
00474 (int) $reviewAssignment->getReviewId()
00475 )
00476 );
00477 }
00478
00483 function deleteReviewAssignmentById($reviewId) {
00484 $reviewFormResponseDao =& DAORegistry::getDAO('ReviewFormResponseDAO');
00485 $reviewFormResponseDao->deleteReviewFormResponseByReviewId($reviewId);
00486
00487 return $this->update(
00488 'DELETE FROM review_assignments WHERE review_id = ?',
00489 (int) $reviewId
00490 );
00491 }
00492
00498 function deleteReviewAssignmentsByArticle($articleId) {
00499 $returner = false;
00500 $result =& $this->retrieve(
00501 'SELECT review_id FROM review_assignments WHERE article_id = ?',
00502 (int) $articleId
00503 );
00504
00505 while (!$result->EOF) {
00506 $row = $result->GetRowAssoc(false);
00507 $reviewId = $row['review_id'];
00508
00509 $this->update('DELETE FROM review_form_responses WHERE review_id = ?', $reviewId);
00510 $this->update('DELETE FROM review_assignments WHERE review_id = ?', $reviewId);
00511
00512 $result->MoveNext();
00513 $returner = true;
00514 }
00515 $result->Close();
00516 return $returner;
00517 }
00518
00523 function getInsertReviewId() {
00524 return $this->getInsertId('review_assignments', 'review_id');
00525 }
00526
00531 function getAverageQualityRatings($journalId) {
00532 $averageQualityRatings = Array();
00533 $result = &$this->retrieve(
00534 'SELECT R.reviewer_id, AVG(R.quality) AS average, COUNT(R.quality) AS count FROM review_assignments R, articles A WHERE R.article_id = A.article_id AND A.journal_id = ? GROUP BY R.reviewer_id',
00535 (int) $journalId
00536 );
00537
00538 while (!$result->EOF) {
00539 $row = $result->GetRowAssoc(false);
00540 $averageQualityRatings[$row['reviewer_id']] = array('average' => $row['average'], 'count' => $row['count']);
00541 $result->MoveNext();
00542 }
00543
00544 $result->Close();
00545 unset($result);
00546
00547 return $averageQualityRatings;
00548 }
00549
00554 function getCompletedReviewCounts($journalId) {
00555 $returner = Array();
00556 $result = &$this->retrieve(
00557 'SELECT r.reviewer_id, COUNT(r.review_id) AS count FROM review_assignments r, articles a WHERE r.article_id = a.article_id AND a.journal_id = ? AND r.date_completed IS NOT NULL GROUP BY r.reviewer_id',
00558 (int) $journalId
00559 );
00560
00561 while (!$result->EOF) {
00562 $row = $result->GetRowAssoc(false);
00563 $returner[$row['reviewer_id']] = $row['count'];
00564 $result->MoveNext();
00565 }
00566
00567 $result->Close();
00568 unset($result);
00569
00570 return $returner;
00571 }
00572
00577 function getCompletedReviewCountsForReviewForms($journalId) {
00578 $returner = array();
00579 $result =& $this->retrieve(
00580 'SELECT r.review_form_id, COUNT(r.review_id) AS count
00581 FROM review_assignments r,
00582 articles a,
00583 review_forms rf
00584 WHERE r.article_id = a.article_id AND
00585 a.journal_id = ? AND
00586 r.review_form_id = rf.review_form_id AND
00587 rf.published = 1 AND
00588 r.date_completed IS NOT NULL
00589 GROUP BY r.review_form_id',
00590 (int) $journalId
00591 );
00592
00593 while (!$result->EOF) {
00594 $row = $result->GetRowAssoc(false);
00595 $returner[$row['review_form_id']] = $row['count'];
00596 $result->MoveNext();
00597 }
00598
00599 $result->Close();
00600 unset($result);
00601
00602 return $returner;
00603 }
00604
00609 function getActiveReviewCountsForReviewForms($journalId) {
00610 $returner = array();
00611 $result =& $this->retrieve(
00612 'SELECT r.review_form_id, COUNT(r.review_id) AS count
00613 FROM review_assignments r,
00614 articles a,
00615 review_forms rf
00616 WHERE r.article_id = a.article_id AND
00617 a.journal_id = ? AND
00618 r.review_form_id = rf.review_form_id AND
00619 rf.published = 1 AND
00620 r.date_confirmed IS NOT NULL AND
00621 r.date_completed IS NULL
00622 GROUP BY r.review_form_id',
00623 $journalId
00624 );
00625
00626 while (!$result->EOF) {
00627 $row = $result->GetRowAssoc(false);
00628 $returner[$row['review_form_id']] = $row['count'];
00629 $result->MoveNext();
00630 }
00631
00632 $result->Close();
00633 unset($result);
00634
00635 return $returner;
00636 }
00637 }
00638
00639 ?>
