classes/submission/director/DirectorSubmissionDAO.inc.php

Go to the documentation of this file.
00001 <?php
00002 
00018 import('submission.director.DirectorSubmission');
00019 import('submission.author.AuthorSubmission'); // Bring in director decision constants
00020 
00021 define('DIRECTOR_SUBMISSION_SORT_ORDER_NATURAL',   0x00000001);
00022 define('DIRECTOR_SUBMISSION_SORT_ORDER_PUBLISHED', 0x00000002);
00023 
00024 class DirectorSubmissionDAO extends DAO {
00025 
00026    var $paperDao;
00027    var $authorDao;
00028    var $userDao;
00029    var $editAssignmentDao;
00030 
00034    function DirectorSubmissionDAO() {
00035       parent::DAO();
00036       $this->paperDao =& DAORegistry::getDAO('PaperDAO');
00037       $this->authorDao =& DAORegistry::getDAO('AuthorDAO');
00038       $this->userDao =& DAORegistry::getDAO('UserDAO');
00039       $this->editAssignmentDao =& DAORegistry::getDAO('EditAssignmentDAO');
00040    }
00041 
00047    function &getDirectorSubmission($paperId) {
00048       $primaryLocale = AppLocale::getPrimaryLocale();
00049       $locale = AppLocale::getLocale();
00050       $result =& $this->retrieve(
00051          'SELECT  p.*,
00052             COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title,
00053             COALESCE(tal.setting_value, tapl.setting_value) AS track_abbrev
00054          FROM  papers p
00055             LEFT JOIN tracks t ON t.track_id = p.track_id
00056             LEFT JOIN track_settings ttpl ON (t.track_id = ttpl.track_id AND ttpl.setting_name = ? AND ttpl.locale = ?)
00057             LEFT JOIN track_settings ttl ON (t.track_id = ttl.track_id AND ttl.setting_name = ? AND ttl.locale = ?)
00058             LEFT JOIN track_settings tapl ON (t.track_id = tapl.track_id AND tapl.setting_name = ? AND tapl.locale = ?)
00059             LEFT JOIN track_settings tal ON (t.track_id = tal.track_id AND tal.setting_name = ? AND tal.locale = ?)
00060          WHERE p.paper_id = ?',
00061          array(
00062             'title',
00063             $primaryLocale,
00064             'title',
00065             $locale,
00066             'abbrev',
00067             $primaryLocale,
00068             'abbrev',
00069             $locale,
00070             $paperId
00071          )
00072       );
00073 
00074       $returner = null;
00075       if ($result->RecordCount() != 0) {
00076          $returner =& $this->_returnDirectorSubmissionFromRow($result->GetRowAssoc(false));
00077       }
00078 
00079       $result->Close();
00080       unset($result);
00081 
00082       return $returner;
00083    }
00084 
00090    function &_returnDirectorSubmissionFromRow(&$row) {
00091       $directorSubmission = new DirectorSubmission();
00092 
00093       // Paper attributes
00094       $this->paperDao->_paperFromRow($directorSubmission, $row);
00095 
00096       // Director Assignment
00097       $editAssignments =& $this->editAssignmentDao->getEditAssignmentsByPaperId($row['paper_id']);
00098       $directorSubmission->setEditAssignments($editAssignments->toArray());
00099 
00100       // Director Decisions
00101       for ($i = 1; $i <= $row['current_stage']; $i++) {
00102          $directorSubmission->setDecisions($this->getDirectorDecisions($row['paper_id'], $i), $i);
00103       }
00104 
00105       // Review Rounds
00106       $reviewAssignmentDao =& DAORegistry::getDAO('ReviewAssignmentDAO');
00107       for ($i = REVIEW_STAGE_ABSTRACT; $i <= $directorSubmission->getCurrentStage(); $i++) {
00108          $reviewAssignments =& $reviewAssignmentDao->getReviewAssignmentsByPaperId($directorSubmission->getId(), $i);
00109          if (!empty($reviewAssignments)) {
00110             $directorSubmission->setReviewAssignments($reviewAssignments, $i);
00111          }
00112          unset($reviewAssignments);
00113       }
00114 
00115       HookRegistry::call('DirectorSubmissionDAO::_returnDirectorSubmissionFromRow', array(&$directorSubmission, &$row));
00116 
00117       return $directorSubmission;
00118    }
00119 
00124    function insertDirectorSubmission(&$directorSubmission) {
00125       $this->update(
00126          sprintf('INSERT INTO edit_assignments
00127             (paper_id, director_id, date_notified, date_completed, date_acknowledged)
00128             VALUES
00129             (?, ?, %s, %s, %s)',
00130             $this->datetimeToDB($directorSubmission->getDateNotified()), $this->datetimeToDB($directorSubmission->getDateCompleted()), $this->datetimeToDB($directorSubmission->getDateAcknowledged())),
00131          array(
00132             $directorSubmission->getPaperId(),
00133             $directorSubmission->getDirectorId()
00134          )
00135       );
00136 
00137       $directorSubmission->setEditId($this->getInsertEditId());
00138 
00139       // Insert review assignments.
00140       $reviewAssignments =& $directorSubmission->getReviewAssignments();
00141       for ($i=0, $count=count($reviewAssignments); $i < $count; $i++) {
00142          $reviewAssignments[$i]->setPaperId($directorSubmission->getPaperId());
00143          $this->reviewAssignmentDao->insertReviewAssignment($reviewAssignments[$i]);
00144       }
00145 
00146       return $directorSubmission->getEditId();
00147    }
00148 
00153    function updateDirectorSubmission(&$directorSubmission) {
00154       // update edit assignments
00155       $editAssignments = $directorSubmission->getEditAssignments();
00156       foreach ($editAssignments as $editAssignment) {
00157          if ($editAssignment->getEditId() > 0) {
00158             $this->editAssignmentDao->updateEditAssignment($editAssignment);
00159          } else {
00160             $this->editAssignmentDao->insertEditAssignment($editAssignment);
00161          }
00162       }
00163    }
00164 
00180    function &_getUnfilteredDirectorSubmissions($schedConfId, $trackId = 0, $directorId = 0, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $additionalWhereSql = '', $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
00181       $primaryLocale = AppLocale::getPrimaryLocale();
00182       $locale = AppLocale::getLocale();
00183       $params = array(
00184          'title', // Track title
00185          $primaryLocale,
00186          'title',
00187          $locale,
00188          'abbrev', // Track abbrev
00189          $primaryLocale,
00190          'abbrev',
00191          $locale,
00192          'cleanTitle', // Paper title
00193          $primaryLocale,
00194          'cleanTitle', // Paper title
00195          $locale,
00196          $schedConfId
00197       );
00198       $searchSql = '';
00199 
00200       if (!empty($search)) switch ($searchField) {
00201          case SUBMISSION_FIELD_TITLE:
00202             if ($searchMatch === 'is') {
00203                $searchSql = ' AND LOWER(ptl.setting_value) = LOWER(?)';
00204             } elseif ($searchMatch === 'contains') {
00205                $searchSql = ' AND LOWER(ptl.setting_value) LIKE LOWER(?)';
00206                $search = '%' . $search . '%';
00207             } else { // $searchMatch === 'startsWith'
00208                $searchSql = ' AND LOWER(ptl.setting_value) LIKE LOWER(?)';
00209                $search = $search . '%';
00210             }
00211             $params[] = $search;
00212             break;
00213          case SUBMISSION_FIELD_AUTHOR:
00214             $searchSql = $this->_generateUserNameSearchSQL($search, $searchMatch, 'pa.', $params);
00215             break;
00216          case SUBMISSION_FIELD_DIRECTOR:
00217             $searchSql = $this->_generateUserNameSearchSQL($search, $searchMatch, 'ed.', $params);
00218             break;
00219          case SUBMISSION_FIELD_REVIEWER:
00220             $searchSql = $this->_generateUserNameSearchSQL($search, $searchMatch, 're.', $params);
00221             break;
00222       }
00223       if (!empty($dateFrom) || !empty($dateTo)) switch($dateField) {
00224          case SUBMISSION_FIELD_DATE_SUBMITTED:
00225             if (!empty($dateFrom)) {
00226                $searchSql .= ' AND p.date_submitted >= ' . $this->datetimeToDB($dateFrom);
00227             }
00228             if (!empty($dateTo)) {
00229                $searchSql .= ' AND p.date_submitted <= ' . $this->datetimeToDB($dateTo);
00230             }
00231             break;
00232       }
00233 
00234       $sql = 'SELECT DISTINCT
00235             p.*,
00236             COALESCE(ptl.setting_value, pptl.setting_value) AS submission_title,
00237             pap.last_name AS author_name,
00238             t.seq, pp.seq,
00239             COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title,
00240             COALESCE(tal.setting_value, tapl.setting_value) AS track_abbrev
00241          FROM  papers p
00242             INNER JOIN paper_authors pa ON (pa.paper_id = p.paper_id)
00243             LEFT JOIN paper_authors pap ON (pap.paper_id = p.paper_id AND pap.primary_contact = 1)
00244             LEFT JOIN published_papers pp ON (pp.paper_id = p.paper_id)
00245             LEFT JOIN tracks t ON (t.track_id = p.track_id)
00246             LEFT JOIN edit_assignments e ON (e.paper_id = p.paper_id)
00247             LEFT JOIN users ed ON (e.director_id = ed.user_id)
00248             LEFT JOIN review_assignments ra ON (ra.paper_id = p.paper_id)
00249             LEFT JOIN users re ON (re.user_id = ra.reviewer_id AND cancelled = 0)
00250             LEFT JOIN track_settings ttpl ON (t.track_id = ttpl.track_id AND ttpl.setting_name = ? AND ttpl.locale = ?)
00251             LEFT JOIN track_settings ttl ON (t.track_id = ttl.track_id AND ttl.setting_name = ? AND ttl.locale = ?)
00252             LEFT JOIN track_settings tapl ON (t.track_id = tapl.track_id AND tapl.setting_name = ? AND tapl.locale = ?)
00253             LEFT JOIN track_settings tal ON (t.track_id = tal.track_id AND tal.setting_name = ? AND tal.locale = ?)
00254             LEFT JOIN paper_settings pptl ON (p.paper_id = pptl.paper_id AND pptl.setting_name = ? AND pptl.locale = ?)
00255             LEFT JOIN paper_settings ptl ON (p.paper_id = ptl.paper_id AND ptl.setting_name = ? AND ptl.locale = ?)
00256             LEFT JOIN edit_assignments ea ON (p.paper_id = ea.paper_id)
00257             LEFT JOIN edit_assignments ea2 ON (p.paper_id = ea2.paper_id AND ea.edit_id < ea2.edit_id)
00258          WHERE p.sched_conf_id = ?
00259             AND ea2.edit_id IS NULL' .
00260             (!empty($additionalWhereSql)?" AND ($additionalWhereSql)":'') . '
00261             AND (p.submission_progress = 0 OR (p.review_mode = ' . REVIEW_MODE_BOTH_SEQUENTIAL . ' AND p.submission_progress <> 1))';
00262 
00263       if ($trackId) {
00264          $searchSql .= ' AND p.track_id = ?';
00265          $params[] = $trackId;
00266       }
00267 
00268       if ($directorId) {
00269          $searchSql .= ' AND ed.user_id = ?';
00270          $params[] = $directorId;
00271       }
00272 
00273       $sql .= ' ' . $searchSql . ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : '');
00274 
00275       $result =& $this->retrieveRange(
00276          $sql,
00277          $params,
00278          $rangeInfo
00279       );
00280       return $result;
00281    }
00282 
00286    function _generateUserNameSearchSQL($search, $searchMatch, $prefix, &$params) {
00287       $first_last = $this->_dataSource->Concat($prefix.'first_name', '\' \'', $prefix.'last_name');
00288       $first_middle_last = $this->_dataSource->Concat($prefix.'first_name', '\' \'', $prefix.'middle_name', '\' \'', $prefix.'last_name');
00289       $last_comma_first = $this->_dataSource->Concat($prefix.'last_name', '\', \'', $prefix.'first_name');
00290       $last_comma_first_middle = $this->_dataSource->Concat($prefix.'last_name', '\', \'', $prefix.'first_name', '\' \'', $prefix.'middle_name');
00291       if ($searchMatch === 'is') {
00292          $searchSql = " AND (LOWER({$prefix}last_name) = LOWER(?) OR LOWER($first_last) = LOWER(?) OR LOWER($first_middle_last) = LOWER(?) OR LOWER($last_comma_first) = LOWER(?) OR LOWER($last_comma_first_middle) = LOWER(?))";
00293          $params[] = $params[] = $params[] = $params[] = $params[] = $search;
00294       } elseif ($searchMatch === 'initial') {
00295          $searchSql = " AND (LOWER({$prefix}last_name) LIKE LOWER(?))";
00296          $params[] = $search . '%';
00297       } elseif ($searchMatch === 'contains') {
00298          $searchSql = " AND (LOWER({$prefix}last_name) LIKE LOWER(?) OR LOWER($first_last) LIKE LOWER(?) OR LOWER($first_middle_last) LIKE LOWER(?) OR LOWER($last_comma_first) LIKE LOWER(?) OR LOWER($last_comma_first_middle) LIKE LOWER(?))";
00299          $params[] = $params[] = $params[] = $params[] = $params[] = '%' . $search . '%';
00300       } else { // $searchMatch === 'startsWith'
00301          $searchSql = " AND (LOWER({$prefix}last_name) LIKE LOWER(?) OR LOWER($first_last) LIKE LOWER(?) OR LOWER($first_middle_last) LIKE LOWER(?) OR LOWER($last_comma_first) LIKE LOWER(?) OR LOWER($last_comma_first_middle) LIKE LOWER(?))";
00302          $params[] = $params[] = $params[] = $params[] = $params[] = $search . '%';
00303       }
00304       return $searchSql;
00305    }
00306 
00321    function &getDirectorSubmissionsUnassigned($schedConfId, $trackId, $directorId, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
00322       $result =& $this->_getUnfilteredDirectorSubmissions(
00323          $schedConfId, $trackId, $directorId,
00324          $searchField, $searchMatch, $search,
00325          $dateField, $dateFrom, $dateTo,
00326          'p.status = ' . STATUS_QUEUED . ' AND ea.edit_id IS NULL',
00327          $rangeInfo, $sortBy, $sortDirection
00328       );
00329       $returner = new DAOResultFactory($result, $this, '_returnDirectorSubmissionFromRow');
00330       return $returner;
00331    }
00332 
00347    function &getDirectorSubmissionsInReview($schedConfId, $trackId, $directorId, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
00348       $result =& $this->_getUnfilteredDirectorSubmissions(
00349          $schedConfId, $trackId, $directorId,
00350          $searchField, $searchMatch, $search,
00351          $dateField, $dateFrom, $dateTo,
00352          'p.status = ' . STATUS_QUEUED . ' AND ea.edit_id IS NOT NULL',
00353          $rangeInfo, $sortBy, $sortDirection
00354       );
00355       $returner = new DAOResultFactory($result, $this, '_returnDirectorSubmissionFromRow');
00356       return $returner;
00357    }
00358 
00373    function &getDirectorSubmissionsAccepted($schedConfId, $trackId, $directorId, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $rangeInfo = null, $sortBy = null, $sortDirection = "ASC") {
00374       $result =& $this->_getUnfilteredDirectorSubmissions(
00375          $schedConfId, $trackId, $directorId,
00376          $searchField, $searchMatch, $search,
00377          $dateField, $dateFrom, $dateTo,
00378          'p.status = ' . STATUS_PUBLISHED,
00379          $rangeInfo, $sortBy, $sortDirection
00380       );
00381       $returner = new DAOResultFactory($result, $this, '_returnDirectorSubmissionFromRow');
00382       return $returner;
00383    }
00384 
00399    function &getDirectorSubmissionsArchives($schedConfId, $trackId, $directorId, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $rangeInfo = null, $sortBy = null, $sortDirection = "ASC") {
00400       $result =& $this->_getUnfilteredDirectorSubmissions(
00401          $schedConfId, $trackId, $directorId,
00402          $searchField, $searchMatch, $search,
00403          $dateField, $dateFrom, $dateTo,
00404          'p.status <> ' . STATUS_QUEUED . ' AND p.status <> ' . STATUS_PUBLISHED,
00405          $rangeInfo, $sortBy, $sortDirection
00406       );
00407       $returner = new DAOResultFactory($result, $this, '_returnDirectorSubmissionFromRow');
00408       return $returner;
00409    }
00410 
00414    function &getDirectorSubmissionsCount($schedConfId) {
00415       $submissionsCount = array();
00416 
00417       // Fetch a count of unassigned submissions.
00418       // "e2" and "e" are used to fetch only a single assignment
00419       // if several exist.
00420       $result =& $this->retrieve(
00421          'SELECT  COUNT(*) AS unassigned_count
00422          FROM  papers p
00423             LEFT JOIN edit_assignments e ON (p.paper_id = e.paper_id)
00424             LEFT JOIN edit_assignments e2 ON (p.paper_id = e2.paper_id AND e.edit_id < e2.edit_id)
00425          WHERE p.sched_conf_id = ?
00426             AND p.status = ' . STATUS_QUEUED . '
00427             AND e2.edit_id IS NULL
00428             AND e.edit_id IS NULL
00429             AND (p.submission_progress = 0 OR (p.review_mode = ' . REVIEW_MODE_BOTH_SEQUENTIAL . ' AND p.submission_progress <> 1))',
00430          array((int) $schedConfId)
00431       );
00432       $submissionsCount[0] = $result->Fields('unassigned_count');
00433       $result->Close();
00434 
00435       // Fetch a count of submissions in review.
00436       // "e2" and "e" are used to fetch only a single assignment
00437       // if several exist.
00438       $result =& $this->retrieve(
00439          'SELECT  COUNT(*) AS review_count
00440          FROM  papers p
00441             LEFT JOIN edit_assignments e ON (p.paper_id = e.paper_id)
00442             LEFT JOIN edit_assignments e2 ON (p.paper_id = e2.paper_id AND e.edit_id < e2.edit_id)
00443          WHERE p.sched_conf_id = ?
00444             AND p.status = ' . STATUS_QUEUED . '
00445             AND e2.edit_id IS NULL
00446             AND e.edit_id IS NOT NULL
00447             AND (p.submission_progress = 0 OR (p.review_mode = ' . REVIEW_MODE_BOTH_SEQUENTIAL . ' AND p.submission_progress <> 1))',
00448          array((int) $schedConfId)
00449       );
00450       $submissionsCount[1] = $result->Fields('review_count');
00451       $result->Close();
00452 
00453       return $submissionsCount;
00454    }
00455 
00456    //
00457    // Miscellaneous
00458    //
00459 
00465    function getDirectorDecisions($paperId, $stage = null) {
00466       $decisions = array();
00467       $args = array($paperId);
00468       if($stage) {
00469          $args[] = $stage;
00470       }
00471 
00472       $result =& $this->retrieve(
00473          'SELECT  edit_decision_id,
00474             director_id,
00475             decision,
00476             date_decided
00477          FROM  edit_decisions
00478          WHERE paper_id = ? ' .
00479          ($stage?' AND stage = ?':'') .
00480          ' ORDER BY date_decided ASC',
00481          (count($args)==1?shift($args):$args)
00482       );
00483 
00484       while (!$result->EOF) {
00485          $decisions[] = array(
00486             'editDecisionId' => $result->fields['edit_decision_id'],
00487             'directorId' => $result->fields['director_id'],
00488             'decision' => $result->fields['decision'],
00489             'dateDecided' => $this->datetimeFromDB($result->fields['date_decided'])
00490          );
00491          $result->moveNext();
00492       }
00493 
00494       $result->Close();
00495       unset($result);
00496 
00497       return $decisions;
00498    }
00499 
00504    function transferDirectorDecisions($oldUserId, $newUserId) {
00505       $this->update(
00506          'UPDATE edit_decisions SET director_id = ? WHERE director_id = ?',
00507          array($newUserId, $oldUserId)
00508       );
00509    }
00510 
00518    function &getUsersNotAssignedToPaper($schedConfId, $paperId, $roleId, $searchType=null, $search=null, $searchMatch=null, $rangeInfo = null) {
00519       $users = array();
00520 
00521       $paramArray = array(
00522          'interests',
00523          $paperId,
00524          $schedConfId,
00525          $roleId
00526       );
00527 
00528       $searchSql = '';
00529 
00530       $searchTypeMap = array(
00531          USER_FIELD_FIRSTNAME => 'u.first_name',
00532          USER_FIELD_LASTNAME => 'u.last_name',
00533          USER_FIELD_USERNAME => 'u.username',
00534          USER_FIELD_EMAIL => 'u.email',
00535          USER_FIELD_INTERESTS => 's.setting_value'
00536       );
00537 
00538       if (!empty($search) && isset($searchTypeMap[$searchType])) {
00539          $fieldName = $searchTypeMap[$searchType];
00540          switch ($searchMatch) {
00541             case 'is':
00542                $searchSql = "AND LOWER($fieldName) = LOWER(?)";
00543                $paramArray[] = $search;
00544                break;
00545             case 'contains':
00546                $searchSql = "AND LOWER($fieldName) LIKE LOWER(?)";
00547                $paramArray[] = '%' . $search . '%';
00548                break;
00549             case 'startsWith':
00550                $searchSql = "AND LOWER($fieldName) LIKE LOWER(?)";
00551                $paramArray[] = $search . '%';
00552                break;
00553          }
00554       } elseif (!empty($search)) switch ($searchType) {
00555          case USER_FIELD_USERID:
00556             $searchSql = 'AND u.user_id=?';
00557             $paramArray[] = $search;
00558             break;
00559          case USER_FIELD_INITIAL:
00560             $searchSql = 'AND (LOWER(u.last_name) LIKE LOWER(?) OR LOWER(u.username) LIKE LOWER(?))';
00561             $paramArray[] = $search . '%';
00562             $paramArray[] = $search . '%';
00563             break;
00564       }
00565 
00566       $result =& $this->retrieveRange(
00567          'SELECT DISTINCT
00568             u.*
00569          FROM  users u
00570             LEFT JOIN roles r ON (r.user_id = u.user_id)
00571             LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?)
00572             LEFT JOIN edit_assignments e ON (e.director_id = u.user_id AND e.paper_id = ?)
00573          WHERE r.sched_conf_id = ? AND
00574             r.role_id = ? AND
00575             e.paper_id IS NULL ' .
00576             $searchSql . '
00577          ORDER BY last_name, first_name',
00578          $paramArray, $rangeInfo
00579       );
00580 
00581       $returner = new DAOResultFactory($result, $this->userDao, '_returnUserFromRow');
00582       return $returner;
00583    }
00584 
00589    function getInsertEditId() {
00590       return $this->getInsertId('edit_assignments', 'edit_id');
00591    }
00592    
00598    function getSortMapping($heading) {
00599       switch ($heading) {
00600          case 'id': return 'p.paper_id';
00601          case 'submitDate': return 'p.date_submitted';
00602          case 'section': return 'section_abbrev';
00603          case 'authors': return 'author_name';
00604          case 'title': return 'submission_title';
00605          case 'active': return 'p.submission_progress';     
00606          case 'subLayout': return 'layout_completed';
00607          case 'status': return 'p.status';
00608          case 'seq': return 'pp.seq';
00609          default: return null;
00610       }
00611    }
00612 }
00613 
00614 ?>

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