Open Journal Systems  2.4.4
 All Classes Namespaces Functions Variables Groups Pages
ProofreaderSubmissionDAO.inc.php
1 <?php
2 
17 import('classes.submission.proofreader.ProofreaderSubmission');
18 
22  var $articleCommentDao;
23  var $editAssignmentDao;
24  var $galleyDao;
25  var $suppFileDao;
26 
31  parent::DAO();
32 
33  $this->articleDao =& DAORegistry::getDAO('ArticleDAO');
34  $this->articleCommentDao =& DAORegistry::getDAO('ArticleCommentDAO');
35  $this->editAssignmentDao =& DAORegistry::getDAO('EditAssignmentDAO');
36  $this->galleyDao =& DAORegistry::getDAO('ArticleGalleyDAO');
37  $this->suppFileDao =& DAORegistry::getDAO('SuppFileDAO');
38  }
39 
45  function &getSubmission($articleId, $journalId = null) {
46  $primaryLocale = AppLocale::getPrimaryLocale();
47  $locale = AppLocale::getLocale();
48 
49  $params = array(
50  'title',
51  $primaryLocale,
52  'title',
53  $locale,
54  'abbrev',
55  $primaryLocale,
56  'abbrev',
57  $locale,
58  $articleId
59  );
60  if ($journalId) $params[] = $journalId;
61 
62  $result =& $this->retrieve(
63  'SELECT a.*,
64  COALESCE(stl.setting_value, stpl.setting_value) AS section_title,
65  COALESCE(sal.setting_value, sapl.setting_value) AS section_abbrev
66  FROM articles a
67  LEFT JOIN sections s ON s.section_id = a.section_id
68  LEFT JOIN section_settings stpl ON (s.section_id = stpl.section_id AND stpl.setting_name = ? AND stpl.locale = ?)
69  LEFT JOIN section_settings stl ON (s.section_id = stl.section_id AND stl.setting_name = ? AND stl.locale = ?)
70  LEFT JOIN section_settings sapl ON (s.section_id = sapl.section_id AND sapl.setting_name = ? AND sapl.locale = ?)
71  LEFT JOIN section_settings sal ON (s.section_id = sal.section_id AND sal.setting_name = ? AND sal.locale = ?)
72  WHERE article_id = ?' .
73  ($journalId?' AND a.journal_id = ?':''),
74  $params
75  );
76 
77  $returner = null;
78  if ($result->RecordCount() != 0) {
79  $returner =& $this->_returnSubmissionFromRow($result->GetRowAssoc(false));
80  }
81 
82  $result->Close();
83  unset($result);
84 
85  return $returner;
86  }
87 
93  function &_returnSubmissionFromRow(&$row) {
94  $submission = new ProofreaderSubmission();
95  $this->articleDao->_articleFromRow($submission, $row);
96  $submission->setMostRecentProofreadComment($this->articleCommentDao->getMostRecentArticleComment($row['article_id'], COMMENT_TYPE_PROOFREAD, $row['article_id']));
97 
98  // Editor Assignment
99  $editAssignments =& $this->editAssignmentDao->getEditAssignmentsByArticleId($row['article_id']);
100  $submission->setEditAssignments($editAssignments->toArray());
101 
102  // Layout reference information
103  $submission->setGalleys($this->galleyDao->getGalleysByArticle($row['article_id']));
104 
105  $submission->setSuppFiles($this->suppFileDao->getSuppFilesByArticle($row['article_id']));
106 
107  $submission->setMostRecentLayoutComment($this->articleCommentDao->getMostRecentArticleComment($row['article_id'], COMMENT_TYPE_LAYOUT, $row['article_id']));
108 
109  HookRegistry::call('ProofreaderSubmissionDAO::_returnProofreaderSubmissionFromRow', array(&$submission, &$row));
110 
111  return $submission;
112  }
113 
127  function &getSubmissions($proofreaderId, $journalId = null, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $active = true, $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
128  $primaryLocale = AppLocale::getPrimaryLocale();
129  $locale = AppLocale::getLocale();
130 
131  $params = array(
132  'title', // Section title
133  $primaryLocale,
134  'title',
135  $locale,
136  'abbrev', // Section abbrev.
137  $primaryLocale,
138  'abbrev',
139  $locale,
140  'cleanTitle', // Article title
141  'cleanTitle',
142  $locale,
143  ASSOC_TYPE_ARTICLE,
144  'SIGNOFF_COPYEDITING_FINAL',
145  ASSOC_TYPE_ARTICLE,
146  'SIGNOFF_LAYOUT',
147  ASSOC_TYPE_ARTICLE,
148  'SIGNOFF_PROOFREADING_PROOFREADER',
149  ASSOC_TYPE_ARTICLE,
150  'SIGNOFF_COPYEDITING_INITIAL',
151  $proofreaderId
152  );
153  if (isset($journalId)) $params[] = $journalId;
154 
155  $searchSql = '';
156 
157  if (!empty($search)) switch ($searchField) {
158  case SUBMISSION_FIELD_ID:
159  $params[] = (int) $search;
160  $searchSql = ' AND a.article_id = ?';
161  break;
162  case SUBMISSION_FIELD_TITLE:
163  if ($searchMatch === 'is') {
164  $searchSql = ' AND LOWER(atl.setting_value) = LOWER(?)';
165  } elseif ($searchMatch === 'contains') {
166  $searchSql = ' AND LOWER(atl.setting_value) LIKE LOWER(?)';
167  $search = '%' . $search . '%';
168  } else { // $searchMatch === 'startsWith'
169  $searchSql = ' AND LOWER(atl.setting_value) LIKE LOWER(?)';
170  $search = $search . '%';
171  }
172  $params[] = $search;
173  break;
174  case SUBMISSION_FIELD_AUTHOR:
175  $searchSql = $this->_generateUserNameSearchSQL($search, $searchMatch, 'aa.', $params);
176  break;
177  case SUBMISSION_FIELD_EDITOR:
178  $searchSql = $this->_generateUserNameSearchSQL($search, $searchMatch, 'ed.', $params);
179  break;
180  }
181 
182  if (!empty($dateFrom) || !empty($dateTo)) switch($dateField) {
183  case SUBMISSION_FIELD_DATE_SUBMITTED:
184  if (!empty($dateFrom)) {
185  $searchSql .= ' AND a.date_submitted >= ' . $this->datetimeToDB($dateFrom);
186  }
187  if (!empty($dateTo)) {
188  $searchSql .= ' AND a.date_submitted <= ' . $this->datetimeToDB($dateTo);
189  }
190  break;
191  case SUBMISSION_FIELD_DATE_COPYEDIT_COMPLETE:
192  if (!empty($dateFrom)) {
193  $searchSql .= ' AND scp.date_completed >= ' . $this->datetimeToDB($dateFrom);
194  }
195  if (!empty($dateTo)) {
196  $searchSql .= ' AND scp.date_completed <= ' . $this->datetimeToDB($dateTo);
197  }
198  break;
199  case SUBMISSION_FIELD_DATE_LAYOUT_COMPLETE:
200  if (!empty($dateFrom)) {
201  $searchSql .= ' AND sle.date_completed >= ' . $this->datetimeToDB($dateFrom);
202  }
203  if (!empty($dateTo)) {
204  $searchSql .= ' AND sle.date_completed <= ' . $this->datetimeToDB($dateTo);
205  }
206  break;
207  case SUBMISSION_FIELD_DATE_PROOFREADING_COMPLETE:
208  if (!empty($dateFrom)) {
209  $searchSql .= ' AND spr.date_completed >= ' . $this->datetimeToDB($dateFrom);
210  }
211  if (!empty($dateTo)) {
212  $searchSql .= 'AND spr.date_completed <= ' . $this->datetimeToDB($dateTo);
213  }
214  break;
215  }
216  $sql = 'SELECT DISTINCT
217  a.*,
218  spr.date_notified AS date_assigned,
219  spr.date_completed AS date_completed,
220  SUBSTRING(COALESCE(atl.setting_value, atpl.setting_value) FROM 1 FOR 255) AS submission_title,
221  aap.last_name AS author_name,
222  SUBSTRING(COALESCE(stl.setting_value, stpl.setting_value) FROM 1 FOR 255) AS section_title,
223  SUBSTRING(COALESCE(sal.setting_value, sapl.setting_value) FROM 1 FOR 255) AS section_abbrev
224  FROM articles a
225  LEFT JOIN authors aa ON (aa.submission_id = a.article_id)
226  LEFT JOIN authors aap ON (aap.submission_id = a.article_id AND aap.primary_contact = 1)
227  LEFT JOIN sections s ON s.section_id = a.section_id
228  LEFT JOIN edit_assignments e ON (e.article_id = a.article_id)
229  LEFT JOIN users ed ON (e.editor_id = ed.user_id)
230  LEFT JOIN section_settings stpl ON (s.section_id = stpl.section_id AND stpl.setting_name = ? AND stpl.locale = ?)
231  LEFT JOIN section_settings stl ON (s.section_id = stl.section_id AND stl.setting_name = ? AND stl.locale = ?)
232  LEFT JOIN section_settings sapl ON (s.section_id = sapl.section_id AND sapl.setting_name = ? AND sapl.locale = ?)
233  LEFT JOIN section_settings sal ON (s.section_id = sal.section_id AND sal.setting_name = ? AND sal.locale = ?)
234  LEFT JOIN article_settings atpl ON (atpl.article_id = a.article_id AND atpl.setting_name = ? AND atpl.locale = a.locale)
235  LEFT JOIN article_settings atl ON (a.article_id = atl.article_id AND atl.setting_name = ? and atl.locale = ?)
236  LEFT JOIN signoffs scpf ON (a.article_id = scpf.assoc_id AND scpf.assoc_type = ? AND scpf.symbolic = ?)
237  LEFT JOIN signoffs sle ON (a.article_id = sle.assoc_id AND sle.assoc_type = ? AND sle.symbolic = ?)
238  LEFT JOIN signoffs spr ON (a.article_id = spr.assoc_id AND spr.assoc_type = ? AND spr.symbolic = ?)
239  LEFT JOIN signoffs scpi ON (a.article_id = scpi.assoc_id AND scpi.assoc_type = ? AND scpi.symbolic = ?)
240  WHERE
241  spr.user_id = ? AND
242  ' . (isset($journalId)?'a.journal_id = ? AND':'') . '
243  spr.date_notified IS NOT NULL';
244 
245  if ($active) {
246  $sql .= ' AND a.status = ' . STATUS_QUEUED;
247  } else {
248  $sql .= ' AND a.status <> ' . STATUS_QUEUED;
249  }
250 
251  $result =& $this->retrieveRange($sql . ' ' . $searchSql . ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : ''), $params, $rangeInfo);
252 
253  $returner = new DAOResultFactory ($result, $this, '_returnSubmissionFromRow');
254  return $returner;
255  }
256 
260  function _generateUserNameSearchSQL($search, $searchMatch, $prefix, &$params) {
261  $first_last = $this->concat($prefix.'first_name', '\' \'', $prefix.'last_name');
262  $first_middle_last = $this->concat($prefix.'first_name', '\' \'', $prefix.'middle_name', '\' \'', $prefix.'last_name');
263  $last_comma_first = $this->concat($prefix.'last_name', '\', \'', $prefix.'first_name');
264  $last_comma_first_middle = $this->concat($prefix.'last_name', '\', \'', $prefix.'first_name', '\' \'', $prefix.'middle_name');
265  if ($searchMatch === 'is') {
266  $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(?))";
267  } elseif ($searchMatch === 'contains') {
268  $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(?))";
269  $search = '%' . $search . '%';
270  } else { // $searchMatch === 'startsWith'
271  $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(?))";
272  $search = $search . '%';
273  }
274  $params[] = $params[] = $params[] = $params[] = $params[] = $search;
275  return $searchSql;
276  }
277 
283  function getSubmissionsCount($proofreaderId, $journalId) {
284  $submissionsCount = array();
285  $submissionsCount[0] = 0;
286  $submissionsCount[1] = 0;
287 
288  $sql = 'SELECT
289  a.status
290  FROM
291  articles a
292  LEFT JOIN signoffs spp ON (a.article_id = spp.assoc_id AND spp.assoc_type = ? AND spp.symbolic = ?)
293  LEFT JOIN sections s ON s.section_id = a.section_id
294  WHERE
295  spp.user_id = ? AND a.journal_id = ? AND spp.date_notified IS NOT NULL';
296 
297  $result =& $this->retrieve($sql, array(ASSOC_TYPE_ARTICLE, 'SIGNOFF_PROOFREADING_PROOFREADER', $proofreaderId, $journalId));
298 
299  while (!$result->EOF) {
300  if ($result->fields['status'] == STATUS_QUEUED) {
301  $submissionsCount[0] += 1;
302  } else {
303  $submissionsCount[1] += 1;
304  }
305  $result->moveNext();
306  }
307 
308  return $submissionsCount;
309  }
310 
316  function getSortMapping($heading) {
317  switch ($heading) {
318  case 'id': return 'a.article_id';
319  case 'assignDate': return 'date_assigned';
320  case 'dateCompleted': return 'date_completed';
321  case 'section': return 'section_abbrev';
322  case 'authors': return 'author_name';
323  case 'title': return 'submission_title';
324  case 'status': return 'a.status';
325  default: return null;
326  }
327  }
328 }
329 
330 ?>
Describes a proofreader&#39;s view of a submission.
_generateUserNameSearchSQL($search, $searchMatch, $prefix, &$params)
Operations for retrieving and modifying objects from a database.
Definition: DAO.inc.php:29
& retrieve($sql, $params=false, $callHooks=true)
Definition: DAO.inc.php:83
& getSubmissions($proofreaderId, $journalId=null, $searchField=null, $searchMatch=null, $search=null, $dateField=null, $dateFrom=null, $dateTo=null, $active=true, $rangeInfo=null, $sortBy=null, $sortDirection=SORT_DIRECTION_ASC)
& retrieveRange($sql, $params=false, $dbResultRange=null, $callHooks=true)
Definition: DAO.inc.php:176
& getSubmission($articleId, $journalId=null)
concat()
Definition: DAO.inc.php:52
getSubmissionsCount($proofreaderId, $journalId)
Operations for retrieving and modifying ProofreaderSubmission objects.
Wrapper around ADORecordSet providing &quot;factory&quot; features for generating objects from DAOs...
& getDAO($name, $dbconn=null)
call($hookName, $args=null)
datetimeToDB($dt)
Definition: DAO.inc.php:296
getDirectionMapping($direction)
Definition: DAO.inc.php:588