classes/submission/proofreader/ProofreaderSubmissionDAO.inc.php

<?php

// $Id: ProofreaderSubmissionDAO.inc.php,v 1.29 2008/07/01 01:16:11 asmecher Exp $


import('submission.proofreader.ProofreaderSubmission');

class ProofreaderSubmissionDAO extends DAO {
   var $articleDao;
   var $articleCommentDao;
   var $editAssignmentDao;
   var $proofAssignmentDao;
   var $layoutAssignmentDao;
   var $galleyDao;
   var $suppFileDao;

   function ProofreaderSubmissionDAO() {
      parent::DAO();

      $this->articleDao = &DAORegistry::getDAO('ArticleDAO');
      $this->articleCommentDao = &DAORegistry::getDAO('ArticleCommentDAO');
      $this->proofAssignmentDao = &DAORegistry::getDAO('ProofAssignmentDAO');
      $this->editAssignmentDao = &DAORegistry::getDAO('EditAssignmentDAO');
      $this->layoutAssignmentDao = &DAORegistry::getDAO('LayoutAssignmentDAO');
      $this->galleyDao = &DAORegistry::getDAO('ArticleGalleyDAO');
      $this->suppFileDao = &DAORegistry::getDAO('SuppFileDAO');
   }

   function &getSubmission($articleId, $journalId = null) {
      $primaryLocale = Locale::getPrimaryLocale();
      $locale = Locale::getLocale();

      $params = array(
         'title',
         $primaryLocale,
         'title',
         $locale,
         'abbrev',
         $primaryLocale,
         'abbrev',
         $locale,
         $articleId
      );
      if ($journalId) $params[] = $journalId;

      $result = &$this->retrieve(
         'SELECT  a.*,
            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 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 article_id = ?' .
            ($journalId?' AND a.journal_id = ?':''),
         $params
      );

      $returner = null;
      if ($result->RecordCount() != 0) {
         $returner = &$this->_returnSubmissionFromRow($result->GetRowAssoc(false));
      }

      $result->Close();
      unset($result);

      return $returner;
   }

   function &_returnSubmissionFromRow(&$row) {
      $submission = &new ProofreaderSubmission();
      $this->articleDao->_articleFromRow($submission, $row);
      $submission->setMostRecentProofreadComment($this->articleCommentDao->getMostRecentArticleComment($row['article_id'], COMMENT_TYPE_PROOFREAD, $row['article_id']));
      $submission->setProofAssignment($this->proofAssignmentDao->getProofAssignmentByArticleId($row['article_id']));

      // Editor Assignment
      $editAssignments =& $this->editAssignmentDao->getEditAssignmentsByArticleId($row['article_id']);
      $submission->setEditAssignments($editAssignments->toArray());

      // Layout reference information
      $submission->setLayoutAssignment($this->layoutAssignmentDao->getLayoutAssignmentByArticleId($row['article_id']));

      $submission->setGalleys($this->galleyDao->getGalleysByArticle($row['article_id']));

      $submission->setSuppFiles($this->suppFileDao->getSuppFilesByArticle($row['article_id']));

      $submission->setMostRecentLayoutComment($this->articleCommentDao->getMostRecentArticleComment($row['article_id'], COMMENT_TYPE_LAYOUT, $row['article_id']));

      HookRegistry::call('ProofreaderSubmissionDAO::_returnProofreaderSubmissionFromRow', array(&$submission, &$row));

      return $submission;
   }

   function updateSubmission(&$submission) {
      // Only update proofread-specific data
      $proofreadAssignment =& $submission->getProofAssignment();
      $this->proofAssignmentDao->updateProofAssignment($proofAssignment);
   }

   function &getSubmissions($proofreaderId, $journalId = null, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $active = true, $rangeInfo = null) {
      $primaryLocale = Locale::getPrimaryLocale();
      $locale = Locale::getLocale();

      $params = array(
         'title',
         $primaryLocale,
         'title',
         $locale,
         'abbrev',
         $primaryLocale,
         'abbrev',
         $locale,
         'title',
         $proofreaderId
      );
      if (isset($journalId)) $params[] = $journalId;

      $searchSql = '';

      if (!empty($search)) switch ($searchField) {
         case SUBMISSION_FIELD_TITLE:
            if ($searchMatch === 'is') {
               $searchSql = ' AND LOWER(atl.setting_value) = LOWER(?)';
            } else {
               $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(?))";
            } else {
               $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(?))";
            } else {
               $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 c.date_final_completed >= ' . $this->datetimeToDB($dateFrom);
            }
            if (!empty($dateTo)) {
               $searchSql .= ' AND c.date_final_completed <= ' . $this->datetimeToDB($dateTo);
            }
            break;
         case SUBMISSION_FIELD_DATE_LAYOUT_COMPLETE:
            if (!empty($dateFrom)) {
               $searchSql .= ' AND l.date_completed >= ' . $this->datetimeToDB($dateFrom);
            }
            if (!empty($dateTo)) {
               $searchSql .= ' AND l.date_completed <= ' . $this->datetimeToDB($dateTo);
            }
            break;
         case SUBMISSION_FIELD_DATE_PROOFREADING_COMPLETE:
            if (!empty($dateFrom)) {
               $searchSql .= ' AND p.date_proofreader_completed >= ' . $this->datetimeToDB($dateFrom);
            }
            if (!empty($dateTo)) {
               $searchSql .= 'AND p.date_proofreader_completed <= ' . $this->datetimeToDB($dateTo);
            }
            break;
      }
      $sql = 'SELECT DISTINCT
            a.*,
            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)
            INNER JOIN proof_assignments p ON (p.article_id = a.article_id)
            LEFT JOIN sections s ON s.section_id = a.section_id
            LEFT JOIN copyed_assignments c ON (c.article_id = a.article_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 layouted_assignments l ON (l.article_id = a.article_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 atl ON (a.article_id = atl.article_id AND atl.setting_name = ?)
         WHERE
            p.proofreader_id = ? AND
            ' . (isset($journalId)?'a.journal_id = ? AND':'') . '
            p.date_proofreader_notified IS NOT NULL';

      if ($active) {
         $sql .= ' AND p.date_proofreader_completed IS NULL';
      } else {
         $sql .= ' AND p.date_proofreader_completed IS NOT NULL';    
      }

      $result = &$this->retrieveRange($sql . ' ' . $searchSql, $params, $rangeInfo);

      $returner = &new DAOResultFactory ($result, $this, '_returnSubmissionFromRow');
      return $returner;
   }

   function getSubmissionsCount($proofreaderId, $journalId) {
      $submissionsCount = array();
      $submissionsCount[0] = 0;
      $submissionsCount[1] = 0;

      $sql = 'SELECT p.date_proofreader_completed FROM articles a INNER JOIN proof_assignments p ON (p.article_id = a.article_id) LEFT JOIN sections s ON s.section_id = a.section_id WHERE p.proofreader_id = ? AND a.journal_id = ? AND p.date_proofreader_notified IS NOT NULL';

      $result = &$this->retrieve($sql, array($proofreaderId, $journalId));

      while (!$result->EOF) {
         if ($result->fields['date_proofreader_completed'] == null) {
            $submissionsCount[0] += 1;
         } else {
            $submissionsCount[1] += 1;
         }
         $result->moveNext();
      }

      return $submissionsCount;
   }
}

?>

---