/ojs/ojs-2.3.2-1/classes/submission/copyeditor/CopyeditorSubmissionDAO.inc.php
PHP | 343 lines | 255 code | 35 blank | 53 comment | 26 complexity | f6c9b700fb57b3b0a20162f3597d54d3 MD5 | raw file
Possible License(s): GPL-2.0, LGPL-2.1
- <?php
- /**
- * @file classes/submission/copyeditor/CopyeditorSubmissionDAO.inc.php
- *
- * Copyright (c) 2003-2009 John Willinsky
- * Distributed under the GNU GPL v2. For full terms see the file docs/COPYING.
- *
- * @class CopyeditorSubmissionDAO
- * @ingroup submission
- * @see CopyeditorSubmission
- *
- * @brief Operations for retrieving and modifying CopyeditorSubmission objects.
- */
- // $Id$
- import('submission.copyeditor.CopyeditorSubmission');
- class CopyeditorSubmissionDAO extends DAO {
- var $articleDao;
- var $authorDao;
- var $userDao;
- var $editAssignmentDao;
- var $articleFileDao;
- var $suppFileDao;
- var $galleyDao;
- var $articleCommentDao;
- /**
- * Constructor.
- */
- function CopyeditorSubmissionDAO() {
- parent::DAO();
- $this->articleDao =& DAORegistry::getDAO('ArticleDAO');
- $this->authorDao =& DAORegistry::getDAO('AuthorDAO');
- $this->userDao =& DAORegistry::getDAO('UserDAO');
- $this->editAssignmentDao =& DAORegistry::getDAO('EditAssignmentDAO');
- $this->articleDao =& DAORegistry::getDAO('ArticleDAO');
- $this->articleFileDao =& DAORegistry::getDAO('ArticleFileDAO');
- $this->articleCommentDao =& DAORegistry::getDAO('ArticleCommentDAO');
- $this->suppFileDao =& DAORegistry::getDAO('SuppFileDAO');
- $this->galleyDao =& DAORegistry::getDAO('ArticleGalleyDAO');
- }
- /**
- * Retrieve a copyeditor submission by article ID.
- * @param $articleId int
- * @return CopyeditorSubmission
- */
- function &getCopyeditorSubmission($articleId) {
- $primaryLocale = Locale::getPrimaryLocale();
- $locale = Locale::getLocale();
- $result =& $this->retrieve(
- 'SELECT a.*,
- e.editor_id,
- COALESCE(stl.setting_value, stpl.setting_value) AS section_title,
- COALESCE(sal.setting_value, sapl.setting_value) AS section_abbrev
- FROM articles a
- LEFT JOIN edit_assignments e ON (a.article_id = e.article_id)
- LEFT JOIN sections s ON (s.section_id = a.section_id)
- LEFT JOIN section_settings stpl ON (s.section_id = stpl.section_id AND stpl.setting_name = ? AND stpl.locale = ?)
- LEFT JOIN section_settings stl ON (s.section_id = stl.section_id AND stl.setting_name = ? AND stl.locale = ?)
- LEFT JOIN section_settings sapl ON (s.section_id = sapl.section_id AND sapl.setting_name = ? AND sapl.locale = ?)
- LEFT JOIN section_settings sal ON (s.section_id = sal.section_id AND sal.setting_name = ? AND sal.locale = ?)
- WHERE a.article_id = ?',
- array(
- 'title',
- $primaryLocale,
- 'title',
- $locale,
- 'abbrev',
- $primaryLocale,
- 'abbrev',
- $locale,
- $articleId
- )
- );
- $returner = null;
- if ($result->RecordCount() != 0) {
- $returner =& $this->_returnCopyeditorSubmissionFromRow($result->GetRowAssoc(false));
- }
- $result->Close();
- unset($result);
- return $returner;
- }
- /**
- * Internal function to return a CopyeditorSubmission object from a row.
- * @param $row array
- * @return CopyeditorSubmission
- */
- function &_returnCopyeditorSubmissionFromRow(&$row) {
- $copyeditorSubmission = new CopyeditorSubmission();
- // Article attributes
- $this->articleDao->_articleFromRow($copyeditorSubmission, $row);
- // Editor Assignment
- $editAssignments =& $this->editAssignmentDao->getEditAssignmentsByArticleId($row['article_id']);
- $copyeditorSubmission->setEditAssignments($editAssignments->toArray());
- // Comments
- $copyeditorSubmission->setMostRecentCopyeditComment($this->articleCommentDao->getMostRecentArticleComment($row['article_id'], COMMENT_TYPE_COPYEDIT, $row['article_id']));
- $copyeditorSubmission->setMostRecentLayoutComment($this->articleCommentDao->getMostRecentArticleComment($row['article_id'], COMMENT_TYPE_LAYOUT, $row['article_id']));
- // Files
-
- // Information for Layout table access
- $copyeditorSubmission->setSuppFiles($this->suppFileDao->getSuppFilesByArticle($row['article_id']));
- $copyeditorSubmission->setGalleys($this->galleyDao->getGalleysByArticle($row['article_id']));
- HookRegistry::call('CopyeditorSubmissionDAO::_returnCopyeditorSubmissionFromRow', array(&$copyeditorSubmission, &$row));
- return $copyeditorSubmission;
- }
- /**
- * Get all submissions for a copyeditor of a journal.
- * @param $copyeditorId int
- * @param $journalId int optional
- * @param $searchField int SUBMISSION_FIELD_... constant
- * @param $searchMatch String 'is' or 'contains' or 'startsWith'
- * @param $search String Search string
- * @param $dateField int SUBMISSION_FIELD_DATE_... constant
- * @param $dateFrom int Search from timestamp
- * @param $dateTo int Search to timestamp
- * @return array CopyeditorSubmissions
- */
- function &getCopyeditorSubmissionsByCopyeditorId($copyeditorId, $journalId = null, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $active = true, $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
- $locale = Locale::getLocale();
- $primaryLocale = Locale::getPrimaryLocale();
- $params = array(
- 'title', // Section title
- $primaryLocale,
- 'title',
- $locale,
- 'abbrev', // Section abbrev
- $primaryLocale,
- 'abbrev',
- $locale,
- 'cleanTitle', // Article title
- $primaryLocale,
- 'cleanTitle', // Article title
- $locale,
- ASSOC_TYPE_ARTICLE,
- 'SIGNOFF_COPYEDITING_FINAL',
- ASSOC_TYPE_ARTICLE,
- 'SIGNOFF_LAYOUT',
- ASSOC_TYPE_ARTICLE,
- 'SIGNOFF_PROOFREADING_PROOFREADER',
- ASSOC_TYPE_ARTICLE,
- 'SIGNOFF_COPYEDITING_INITIAL'
- );
- if (isset($journalId)) $params[] = $journalId;
- $params[] = $copyeditorId;
- $searchSql = '';
- if (!empty($search)) switch ($searchField) {
- case SUBMISSION_FIELD_TITLE:
- if ($searchMatch === 'is') {
- $searchSql = ' AND LOWER(atl.setting_value) = LOWER(?)';
- } elseif ($searchMatch === 'contains') {
- $searchSql = ' AND LOWER(atl.setting_value) LIKE LOWER(?)';
- $search = '%' . $search . '%';
- } else { // $searchMatch === 'startsWith'
- $searchSql = ' AND LOWER(atl.setting_value) LIKE LOWER(?)';
- $search = $search . '%';
- }
- $params[] = $search;
- break;
- case SUBMISSION_FIELD_AUTHOR:
- $first_last = $this->_dataSource->Concat('aa.first_name', '\' \'', 'aa.last_name');
- $first_middle_last = $this->_dataSource->Concat('aa.first_name', '\' \'', 'aa.middle_name', '\' \'', 'aa.last_name');
- $last_comma_first = $this->_dataSource->Concat('aa.last_name', '\', \'', 'aa.first_name');
- $last_comma_first_middle = $this->_dataSource->Concat('aa.last_name', '\', \'', 'aa.first_name', '\' \'', 'aa.middle_name');
- if ($searchMatch === 'is') {
- $searchSql = " AND (LOWER(aa.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(?))";
- } elseif ($searchMatch === 'contains') {
- $searchSql = " AND (LOWER(aa.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(?))";
- $search = '%' . $search . '%';
- } else { // $searchMatch === 'startsWith'
- $searchSql = " AND (LOWER(aa.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(?))";
- $search = $search . '%';
- }
- $params[] = $params[] = $params[] = $params[] = $params[] = $search;
- break;
- case SUBMISSION_FIELD_EDITOR:
- $first_last = $this->_dataSource->Concat('ed.first_name', '\' \'', 'ed.last_name');
- $first_middle_last = $this->_dataSource->Concat('ed.first_name', '\' \'', 'ed.middle_name', '\' \'', 'ed.last_name');
- $last_comma_first = $this->_dataSource->Concat('ed.last_name', '\', \'', 'ed.first_name');
- $last_comma_first_middle = $this->_dataSource->Concat('ed.last_name', '\', \'', 'ed.first_name', '\' \'', 'ed.middle_name');
- if ($searchMatch === 'is') {
- $searchSql = " AND (LOWER(ed.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(?))";
- } elseif ($searchMatch === 'contains') {
- $searchSql = " AND (LOWER(ed.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(?))";
- $search = '%' . $search . '%';
- } else { // $searchMatch === 'startsWith'
- $searchSql = " AND (LOWER(ed.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(?))";
- $search = $search . '%';
- }
- $params[] = $params[] = $params[] = $params[] = $params[] = $search;
- break;
- }
- if (!empty($dateFrom) || !empty($dateTo)) switch($dateField) {
- case SUBMISSION_FIELD_DATE_SUBMITTED:
- if (!empty($dateFrom)) {
- $searchSql .= ' AND a.date_submitted >= ' . $this->datetimeToDB($dateFrom);
- }
- if (!empty($dateTo)) {
- $searchSql .= ' AND a.date_submitted <= ' . $this->datetimeToDB($dateTo);
- }
- break;
- case SUBMISSION_FIELD_DATE_COPYEDIT_COMPLETE:
- if (!empty($dateFrom)) {
- $searchSql .= ' AND scp.date_completed >= ' . $this->datetimeToDB($dateFrom);
- }
- if (!empty($dateTo)) {
- $searchSql .= ' AND scp.date_completed <= ' . $this->datetimeToDB($dateTo);
- }
- break;
- case SUBMISSION_FIELD_DATE_LAYOUT_COMPLETE:
- if (!empty($dateFrom)) {
- $searchSql .= ' AND sle.date_completed >= ' . $this->datetimeToDB($dateFrom);
- }
- if (!empty($dateTo)) {
- $searchSql .= ' AND sle.date_completed <= ' . $this->datetimeToDB($dateTo);
- }
- break;
- case SUBMISSION_FIELD_DATE_PROOFREADING_COMPLETE:
- if (!empty($dateFrom)) {
- $searchSql .= ' AND spr.date_completed >= ' . $this->datetimeToDB($dateFrom);
- }
- if (!empty($dateTo)) {
- $searchSql .= 'AND spr.date_completed <= ' . $this->datetimeToDB($dateTo);
- }
- break;
- }
- $sql = 'SELECT DISTINCT
- a.*,
- scpi.date_notified AS date_assigned,
- scpf.date_completed AS date_completed,
- COALESCE(atl.setting_value, atpl.setting_value) AS submission_title,
- aap.last_name AS author_name,
- COALESCE(stl.setting_value, stpl.setting_value) AS section_title,
- COALESCE(sal.setting_value, sapl.setting_value) AS section_abbrev
- FROM
- articles a
- INNER JOIN article_authors aa ON (aa.article_id = a.article_id)
- LEFT JOIN article_authors aap ON (aap.article_id = a.article_id AND aap.primary_contact = 1)
- LEFT JOIN sections s ON (s.section_id = a.section_id)
- LEFT JOIN edit_assignments e ON (e.article_id = a.article_id)
- LEFT JOIN users ed ON (e.editor_id = ed.user_id)
- LEFT JOIN section_settings stpl ON (s.section_id = stpl.section_id AND stpl.setting_name = ? AND stpl.locale = ?)
- LEFT JOIN section_settings stl ON (s.section_id = stl.section_id AND stl.setting_name = ? AND stl.locale = ?)
- LEFT JOIN section_settings sapl ON (s.section_id = sapl.section_id AND sapl.setting_name = ? AND sapl.locale = ?)
- LEFT JOIN section_settings sal ON (s.section_id = sal.section_id AND sal.setting_name = ? AND sal.locale = ?)
- LEFT JOIN article_settings atpl ON (atpl.article_id = a.article_id AND atpl.setting_name = ? AND atpl.locale = ?)
- LEFT JOIN article_settings atl ON (a.article_id = atl.article_id AND atl.setting_name = ? AND atl.locale = ?)
- LEFT JOIN signoffs scpf ON (a.article_id = scpf.assoc_id AND scpf.assoc_type = ? AND scpf.symbolic = ?)
- LEFT JOIN signoffs sle ON (a.article_id = sle.assoc_id AND sle.assoc_type = ? AND sle.symbolic = ?)
- LEFT JOIN signoffs spr ON (a.article_id = spr.assoc_id AND spr.assoc_type = ? AND spr.symbolic = ?)
- LEFT JOIN signoffs scpi ON (a.article_id = scpi.assoc_id AND scpi.assoc_type = ? AND scpi.symbolic = ?)
- WHERE
- ' . (isset($journalId)?'a.journal_id = ? AND':'') . '
- scpi.user_id = ? AND
- (' . ($active?'':'NOT ') . ' ((scpi.date_notified IS NOT NULL AND scpi.date_completed IS NULL) OR (scpf.date_notified IS NOT NULL AND scpf.date_completed IS NULL))) ';
- $result =& $this->retrieveRange(
- $sql . ' ' . $searchSql . ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : ''),
- count($params)==1?array_shift($params):$params,
- $rangeInfo);
- $returner = new DAOResultFactory($result, $this, '_returnCopyeditorSubmissionFromRow');
- return $returner;
- }
- /**
- * Get count of active and complete assignments
- * @param copyeditorId int
- * @param journalId int
- */
- function getSubmissionsCount($copyeditorId, $journalId) {
- $submissionsCount = array();
- $submissionsCount[0] = 0;
- $submissionsCount[1] = 0;
- $sql = 'SELECT
- scf.date_completed
- FROM
- articles a
- LEFT JOIN sections s ON (s.section_id = a.section_id)
- LEFT JOIN signoffs scf ON (a.article_id = scf.assoc_id AND scf.assoc_type = ? AND scf.symbolic = ?)
- LEFT JOIN signoffs sci ON (a.article_id = sci.assoc_id AND sci.assoc_type = ? AND sci.symbolic = ?)
- WHERE
- a.journal_id = ? AND sci.user_id = ? AND sci.date_notified IS NOT NULL';
-
- $result =& $this->retrieve($sql, array(ASSOC_TYPE_ARTICLE, 'SIGNOFF_COPYEDITING_FINAL', ASSOC_TYPE_ARTICLE, 'SIGNOFF_COPYEDITING_INITIAL', $journalId, $copyeditorId));
- while (!$result->EOF) {
- if ($result->fields['date_completed'] == null) {
- $submissionsCount[0] += 1;
- } else {
- $submissionsCount[1] += 1;
- }
- $result->moveNext();
- }
- $result->Close();
- unset($result);
- return $submissionsCount;
- }
-
- /**
- * Map a column heading value to a database value for sorting
- * @param string
- * @return string
- */
- function getSortMapping($heading) {
- switch ($heading) {
- case 'id': return 'a.article_id';
- case 'assignDate': return 'date_assigned';
- case 'dateCompleted': return 'date_completed';
- case 'section': return 'section_abbrev';
- case 'authors': return 'author_name';
- case 'title': return 'submission_title';
- case 'status': return 'a.status';
- default: return null;
- }
- }
- }
- ?>