Open Journal Systems  2.4.8
 All Classes Namespaces Functions Variables Groups Pages
SectionEditorSubmissionDAO.inc.php
1 <?php
2 
20 import('classes.submission.sectionEditor.SectionEditorSubmission');
21 
22 // Bring in editor decision constants
23 import('classes.submission.author.AuthorSubmission');
24 import('classes.submission.common.Action');
25 import('classes.submission.reviewer.ReviewerSubmission');
26 
28  var $articleDao;
29  var $authorDao;
30  var $userDao;
31  var $editAssignmentDao;
32  var $reviewAssignmentDao;
33  var $copyeditorSubmissionDao;
34  var $articleFileDao;
35  var $suppFileDao;
36  var $signoffDao;
37  var $galleyDao;
38  var $articleEmailLogDao;
39  var $articleCommentDao;
40 
45  parent::DAO();
46  $this->articleDao =& DAORegistry::getDAO('ArticleDAO');
47  $this->authorDao =& DAORegistry::getDAO('AuthorDAO');
48  $this->userDao =& DAORegistry::getDAO('UserDAO');
49  $this->editAssignmentDao =& DAORegistry::getDAO('EditAssignmentDAO');
50  $this->reviewAssignmentDao =& DAORegistry::getDAO('ReviewAssignmentDAO');
51  $this->copyeditorSubmissionDao =& DAORegistry::getDAO('CopyeditorSubmissionDAO');
52  $this->articleFileDao =& DAORegistry::getDAO('ArticleFileDAO');
53  $this->suppFileDao =& DAORegistry::getDAO('SuppFileDAO');
54  $this->signoffDao =& DAORegistry::getDAO('SignoffDAO');
55  $this->galleyDao =& DAORegistry::getDAO('ArticleGalleyDAO');
56  $this->articleEmailLogDao =& DAORegistry::getDAO('ArticleEmailLogDAO');
57  $this->articleCommentDao =& DAORegistry::getDAO('ArticleCommentDAO');
58  }
59 
65  function &getSectionEditorSubmission($articleId) {
66  $primaryLocale = AppLocale::getPrimaryLocale();
67  $locale = AppLocale::getLocale();
68  $result =& $this->retrieve(
69  'SELECT a.*,
70  COALESCE(stl.setting_value, stpl.setting_value) AS section_title,
71  COALESCE(sal.setting_value, sapl.setting_value) AS section_abbrev,
72  r2.review_revision
73  FROM articles a
74  LEFT JOIN sections s ON (s.section_id = a.section_id)
75  LEFT JOIN review_rounds r2 ON (a.article_id = r2.submission_id AND a.current_round = r2.round)
76  LEFT JOIN section_settings stpl ON (s.section_id = stpl.section_id AND stpl.setting_name = ? AND stpl.locale = ?)
77  LEFT JOIN section_settings stl ON (s.section_id = stl.section_id AND stl.setting_name = ? AND stl.locale = ?)
78  LEFT JOIN section_settings sapl ON (s.section_id = sapl.section_id AND sapl.setting_name = ? AND sapl.locale = ?)
79  LEFT JOIN section_settings sal ON (s.section_id = sal.section_id AND sal.setting_name = ? AND sal.locale = ?)
80  WHERE a.article_id = ?',
81  array(
82  'title',
83  $primaryLocale,
84  'title',
85  $locale,
86  'abbrev',
87  $primaryLocale,
88  'abbrev',
89  $locale,
90  $articleId
91  )
92  );
93 
94  $returner = null;
95  if ($result->RecordCount() != 0) {
96  $returner =& $this->_returnSectionEditorSubmissionFromRow($result->GetRowAssoc(false));
97  }
98 
99  $result->Close();
100  unset($result);
101 
102  return $returner;
103  }
104 
111  $sectionEditorSubmission = new SectionEditorSubmission();
112 
113  // Article attributes
114  $this->articleDao->_articleFromRow($sectionEditorSubmission, $row);
115 
116  // Editor Assignment
117  $editAssignments =& $this->editAssignmentDao->getEditAssignmentsByArticleId($row['article_id']);
118  $sectionEditorSubmission->setEditAssignments($editAssignments->toArray());
119 
120  // Editor Decisions
121  for ($i = 1; $i <= $row['current_round']; $i++) {
122  $sectionEditorSubmission->setDecisions($this->getEditorDecisions($row['article_id'], $i), $i);
123  }
124 
125  // Comments
126  $sectionEditorSubmission->setMostRecentEditorDecisionComment($this->articleCommentDao->getMostRecentArticleComment($row['article_id'], COMMENT_TYPE_EDITOR_DECISION, $row['article_id']));
127  $sectionEditorSubmission->setMostRecentCopyeditComment($this->articleCommentDao->getMostRecentArticleComment($row['article_id'], COMMENT_TYPE_COPYEDIT, $row['article_id']));
128  $sectionEditorSubmission->setMostRecentLayoutComment($this->articleCommentDao->getMostRecentArticleComment($row['article_id'], COMMENT_TYPE_LAYOUT, $row['article_id']));
129  $sectionEditorSubmission->setMostRecentProofreadComment($this->articleCommentDao->getMostRecentArticleComment($row['article_id'], COMMENT_TYPE_PROOFREAD, $row['article_id']));
130 
131  // Files
132  $sectionEditorSubmission->setSubmissionFile($this->articleFileDao->getArticleFile($row['submission_file_id']));
133  $sectionEditorSubmission->setRevisedFile($this->articleFileDao->getArticleFile($row['revised_file_id']));
134  $sectionEditorSubmission->setReviewFile($this->articleFileDao->getArticleFile($row['review_file_id']));
135  $sectionEditorSubmission->setSuppFiles($this->suppFileDao->getSuppFilesByArticle($row['article_id']));
136  $sectionEditorSubmission->setEditorFile($this->articleFileDao->getArticleFile($row['editor_file_id']));
137 
138 
139  for ($i = 1; $i <= $row['current_round']; $i++) {
140  $sectionEditorSubmission->setEditorFileRevisions($this->articleFileDao->getArticleFileRevisions($row['editor_file_id'], $i), $i);
141  $sectionEditorSubmission->setAuthorFileRevisions($this->articleFileDao->getArticleFileRevisions($row['revised_file_id'], $i), $i);
142  }
143 
144  // Review Rounds
145  $sectionEditorSubmission->setReviewRevision($row['review_revision']);
146 
147  // Review Assignments
148  for ($i = 1; $i <= $row['current_round']; $i++) {
149  $sectionEditorSubmission->setReviewAssignments($this->reviewAssignmentDao->getBySubmissionId($row['article_id'], $i), $i);
150  }
151 
152  // Layout Editing
153  $sectionEditorSubmission->setGalleys($this->galleyDao->getGalleysByArticle($row['article_id']));
154 
155  // Proof Assignment
156  HookRegistry::call('SectionEditorSubmissionDAO::_returnSectionEditorSubmissionFromRow', array(&$sectionEditorSubmission, &$row));
157 
158  return $sectionEditorSubmission;
159  }
160 
165  function updateSectionEditorSubmission(&$sectionEditorSubmission) {
166  // update edit assignment
167  $editAssignments =& $sectionEditorSubmission->getEditAssignments();
168  foreach ($editAssignments as $editAssignment) {
169  if ($editAssignment->getEditId() > 0) {
170  $this->editAssignmentDao->updateEditAssignment($editAssignment);
171  } else {
172  $this->editAssignmentDao->insertEditAssignment($editAssignment);
173  }
174  }
175 
176  // Update editor decisions
177  for ($i = 1; $i <= $sectionEditorSubmission->getCurrentRound(); $i++) {
178  $editorDecisions =& $sectionEditorSubmission->getDecisions($i);
179  if (is_array($editorDecisions)) {
180  foreach ($editorDecisions as $key => $editorDecision) {
181  if ($editorDecision['editDecisionId'] == null) {
182  $this->update(
183  sprintf('INSERT INTO edit_decisions
184  (article_id, round, editor_id, decision, date_decided)
185  VALUES (?, ?, ?, ?, %s)',
186  $this->datetimeToDB($editorDecision['dateDecided'])),
187  array($sectionEditorSubmission->getId(), $sectionEditorSubmission->getCurrentRound(), $editorDecision['editorId'], $editorDecision['decision'])
188  );
189  $editorDecisions[$key]['editDecisionId'] = $this->getInsertId('edit_decisions', 'edit_decision_id');
190  }
191  }
192  }
193  unset($editorDecisions);
194  }
195  if ($this->reviewRoundExists($sectionEditorSubmission->getId(), $sectionEditorSubmission->getCurrentRound())) {
196  $this->update(
197  'UPDATE review_rounds
198  SET
199  review_revision = ?
200  WHERE submission_id = ? AND round = ?',
201  array(
202  $sectionEditorSubmission->getReviewRevision(),
203  $sectionEditorSubmission->getId(),
204  $sectionEditorSubmission->getCurrentRound()
205  )
206  );
207  } elseif ($sectionEditorSubmission->getReviewRevision()!=null) {
208  $this->createReviewRound(
209  $sectionEditorSubmission->getId(),
210  $sectionEditorSubmission->getCurrentRound() === null ? 1 : $sectionEditorSubmission->getCurrentRound(),
211  $sectionEditorSubmission->getReviewRevision()
212  );
213  }
214 
215  // Update copyeditor assignment
216  $copyeditSignoff = $this->signoffDao->getBySymbolic('SIGNOFF_COPYEDITING_INITIAL', ASSOC_TYPE_ARTICLE, $sectionEditorSubmission->getId());
217  if ($copyeditSignoff) {
218  $copyeditorSubmission =& $this->copyeditorSubmissionDao->getCopyeditorSubmission($sectionEditorSubmission->getId());
219  } else {
220  $copyeditorSubmission = new CopyeditorSubmission();
221  }
222 
223  // Only update the fields that an editor can modify.
224  $copyeditorSubmission->setId($sectionEditorSubmission->getId());
225  $copyeditorSubmission->setDateStatusModified($sectionEditorSubmission->getDateStatusModified());
226  $copyeditorSubmission->setLastModified($sectionEditorSubmission->getLastModified());
227 
228  // update review assignments
229  foreach ($sectionEditorSubmission->getReviewAssignments() as $roundReviewAssignments) {
230  foreach ($roundReviewAssignments as $reviewAssignment) {
231  if ($reviewAssignment->getId() > 0) {
232  $this->reviewAssignmentDao->updateReviewAssignment($reviewAssignment);
233  } else {
234  $this->reviewAssignmentDao->insertObject($reviewAssignment);
235  }
236  }
237  }
238 
239  // Remove deleted review assignments
240  $removedReviewAssignments = $sectionEditorSubmission->getRemovedReviewAssignments();
241  for ($i=0, $count=count($removedReviewAssignments); $i < $count; $i++) {
242  $this->reviewAssignmentDao->deleteReviewAssignmentById($removedReviewAssignments[$i]);
243  }
244 
245  // Update article
246  if ($sectionEditorSubmission->getId()) {
247 
248  $article =& $this->articleDao->getArticle($sectionEditorSubmission->getId());
249 
250  // Only update fields that can actually be edited.
251  $article->setSectionId($sectionEditorSubmission->getSectionId());
252  $article->setCurrentRound($sectionEditorSubmission->getCurrentRound());
253  $article->setReviewFileId($sectionEditorSubmission->getReviewFileId());
254  $article->setEditorFileId($sectionEditorSubmission->getEditorFileId());
255  $article->setStatus($sectionEditorSubmission->getStatus());
256  $article->setDateStatusModified($sectionEditorSubmission->getDateStatusModified());
257  $article->setLastModified($sectionEditorSubmission->getLastModified());
258  $article->setCommentsStatus($sectionEditorSubmission->getCommentsStatus());
259 
260  $this->articleDao->updateArticle($article);
261  }
262 
263  }
264 
265  function createReviewRound($articleId, $round, $reviewRevision) {
266  $this->update(
267  'INSERT INTO review_rounds
268  (submission_id, round, review_revision)
269  VALUES
270  (?, ?, ?)',
271  array($articleId, $round, $reviewRevision)
272  );
273  }
274 
281  function &getSectionEditorSubmissions($sectionEditorId, $journalId, $status = true) {
282  $primaryLocale = AppLocale::getPrimaryLocale();
283  $locale = AppLocale::getLocale();
284 
285  $sectionEditorSubmissions = array();
286 
287  $result =& $this->retrieve(
288  'SELECT a.*,
289  COALESCE(stl.setting_value, stpl.setting_value) AS section_title,
290  COALESCE(sal.setting_value, sapl.setting_value) AS section_abbrev,
291  r2.review_revision
292  FROM articles a
293  LEFT JOIN edit_assignments e ON (e.article_id = a.article_id)
294  LEFT JOIN sections s ON (s.section_id = a.section_id)
295  LEFT JOIN review_rounds r2 ON (a.article_id = r2.submission_id AND a.current_round = r2.round)
296  LEFT JOIN section_settings stpl ON (s.section_id = stpl.section_id AND stpl.setting_name = ? AND stpl.locale = ?)
297  LEFT JOIN section_settings stl ON (s.section_id = stl.section_id AND stl.setting_name = ? AND stl.locale = ?)
298  LEFT JOIN section_settings sapl ON (s.section_id = sapl.section_id AND sapl.setting_name = ? AND sapl.locale = ?)
299  LEFT JOIN section_settings sal ON (s.section_id = sal.section_id AND sal.setting_name = ? AND sal.locale = ?)
300  WHERE a.journal_id = ?
301  AND e.editor_id = ?
302  AND a.status = ?',
303  array(
304  'title',
305  $primaryLocale,
306  'title',
307  $locale,
308  'abbrev',
309  $primaryLocale,
310  'abbrev',
311  $locale,
312  $journalId,
313  $sectionEditorId,
314  $status
315  )
316  );
317 
318  while (!$result->EOF) {
319  $sectionEditorSubmissions[] =& $this->_returnSectionEditorSubmissionFromRow($result->GetRowAssoc(false));
320  $result->MoveNext();
321  }
322 
323  $result->Close();
324  unset($result);
325 
326  return $sectionEditorSubmissions;
327  }
328 
332  function &_getUnfilteredSectionEditorSubmissions($sectionEditorId, $journalId, $sectionId = 0, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $additionalWhereSql = '', $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
333  $primaryLocale = AppLocale::getPrimaryLocale();
334  $locale = AppLocale::getLocale();
335 
336  $params = array(
337  ASSOC_TYPE_ARTICLE,
338  'SIGNOFF_COPYEDITING_FINAL',
339  ASSOC_TYPE_ARTICLE,
340  'SIGNOFF_PROOFREADING_PROOFREADER',
341  ASSOC_TYPE_ARTICLE,
342  'SIGNOFF_LAYOUT',
343  'title', // Section title
344  $primaryLocale,
345  'title',
346  $locale,
347  'abbrev', // Section abbrev
348  $primaryLocale,
349  'abbrev',
350  $locale,
351  'cleanTitle', // Article title
352  'cleanTitle',
353  $locale,
354  'title', // Article title
355  'title',
356  $locale,
357  $journalId,
358  $sectionEditorId
359  );
360 
361  $searchSql = '';
362 
363  if (!empty($search)) switch ($searchField) {
364  case SUBMISSION_FIELD_ID:
365  switch ($searchMatch) {
366  case 'is':
367  $params[] = (int) $search;
368  $searchSql = ' AND a.article_id = ?';
369  break;
370  case 'contains':
371  $search = '%' . $search . '%';
372  $params[] = $search;
373  $searchSql = ' AND CONCAT(a.article_id) LIKE ?';
374  break;
375  case 'startsWith':
376  $search = $search . '%';
377  $params[] = $search;
378  $searchSql = 'AND CONCAT(a.article_id) LIKE ?';
379  break;
380  }
381  break;
382  case SUBMISSION_FIELD_TITLE:
383  if ($searchMatch === 'is') {
384  $searchSql = ' AND LOWER(atl.setting_value) = LOWER(?)';
385  } elseif ($searchMatch === 'contains') {
386  $searchSql = ' AND LOWER(atl.setting_value) LIKE LOWER(?)';
387  $search = '%' . $search . '%';
388  } else { // $searchMatch === 'startsWith'
389  $searchSql = ' AND LOWER(atl.setting_value) LIKE LOWER(?)';
390  $search = '%' . $search . '%';
391  }
392  $params[] = $search;
393  break;
394  case SUBMISSION_FIELD_AUTHOR:
395  $searchSql = $this->_generateUserNameSearchSQL($search, $searchMatch, 'aa.', $params);
396  break;
397  case SUBMISSION_FIELD_EDITOR:
398  $searchSql = $this->_generateUserNameSearchSQL($search, $searchMatch, 'ed.', $params);
399  break;
400  }
401 
402  if (!empty($dateFrom) || !empty($dateTo)) switch($dateField) {
403  case SUBMISSION_FIELD_DATE_SUBMITTED:
404  if (!empty($dateFrom)) {
405  $searchSql .= ' AND a.date_submitted >= ' . $this->datetimeToDB($dateFrom);
406  }
407  if (!empty($dateTo)) {
408  $searchSql .= ' AND a.date_submitted <= ' . $this->datetimeToDB($dateTo);
409  }
410  break;
411  case SUBMISSION_FIELD_DATE_COPYEDIT_COMPLETE:
412  if (!empty($dateFrom)) {
413  $searchSql .= ' AND c.date_final_completed >= ' . $this->datetimeToDB($dateFrom);
414  }
415  if (!empty($dateTo)) {
416  $searchSql .= ' AND c.date_final_completed <= ' . $this->datetimeToDB($dateTo);
417  }
418  break;
419  case SUBMISSION_FIELD_DATE_LAYOUT_COMPLETE:
420  if (!empty($dateFrom)) {
421  $searchSql .= ' AND l.date_completed >= ' . $this->datetimeToDB($dateFrom);
422  }
423  if (!empty($dateTo)) {
424  $searchSql .= ' AND l.date_completed <= ' . $this->datetimeToDB($dateTo);
425  }
426  break;
427  case SUBMISSION_FIELD_DATE_PROOFREADING_COMPLETE:
428  if (!empty($dateFrom)) {
429  $searchSql .= ' AND p.date_proofreader_completed >= ' . $this->datetimeToDB($dateFrom);
430  }
431  if (!empty($dateTo)) {
432  $searchSql .= ' AND p.date_proofreader_completed <= ' . $this->datetimeToDB($dateTo);
433  }
434  break;
435  }
436 
437  $sql = 'SELECT DISTINCT
438  a.*,
439  scf.date_completed as copyedit_completed,
440  spr.date_completed as proofread_completed,
441  sle.date_completed as layout_completed,
442  SUBSTRING(COALESCE(actl.setting_value, actpl.setting_value) FROM 1 FOR 255) AS submission_clean_title,
443  aap.last_name AS author_name,
444  e.can_review AS can_review,
445  e.can_edit AS can_edit,
446  SUBSTRING(COALESCE(stl.setting_value, stpl.setting_value) FROM 1 FOR 255) AS section_title,
447  SUBSTRING(COALESCE(sal.setting_value, sapl.setting_value) FROM 1 FOR 255) AS section_abbrev,
448  r2.review_revision
449  FROM articles a
450  LEFT JOIN authors aa ON (aa.submission_id = a.article_id)
451  LEFT JOIN authors aap ON (aap.submission_id = a.article_id AND aap.primary_contact = 1)
452  LEFT JOIN edit_assignments e ON (e.article_id = a.article_id)
453  LEFT JOIN users ed ON (e.editor_id = ed.user_id)
454  LEFT JOIN sections s ON (s.section_id = a.section_id)
455  LEFT JOIN signoffs scf ON (a.article_id = scf.assoc_id AND scf.assoc_type = ? AND scf.symbolic = ?)
456  LEFT JOIN users ce ON (scf.user_id = ce.user_id)
457  LEFT JOIN signoffs spr ON (a.article_id = spr.assoc_id AND spr.assoc_type = ? AND spr.symbolic = ?)
458  LEFT JOIN users pe ON (pe.user_id = spr.user_id)
459  LEFT JOIN review_rounds r2 ON (a.article_id = r2.submission_id and a.current_round = r2.round)
460  LEFT JOIN signoffs sle ON (a.article_id = sle.assoc_id AND sle.assoc_type = ? AND sle.symbolic = ?) LEFT JOIN users le ON (le.user_id = sle.user_id)
461  LEFT JOIN section_settings stpl ON (s.section_id = stpl.section_id AND stpl.setting_name = ? AND stpl.locale = ?)
462  LEFT JOIN section_settings stl ON (s.section_id = stl.section_id AND stl.setting_name = ? AND stl.locale = ?)
463  LEFT JOIN section_settings sapl ON (s.section_id = sapl.section_id AND sapl.setting_name = ? AND sapl.locale = ?)
464  LEFT JOIN section_settings sal ON (s.section_id = sal.section_id AND sal.setting_name = ? AND sal.locale = ?)
465  LEFT JOIN article_settings actpl ON (actpl.article_id = a.article_id AND actpl.setting_name = ? AND actpl.locale = a.locale)
466  LEFT JOIN article_settings actl ON (a.article_id = actl.article_id AND actl.setting_name = ? AND actl.locale = ?)
467  LEFT JOIN article_settings atpl ON (atpl.article_id = a.article_id AND atpl.setting_name = ? AND atpl.locale = a.locale)
468  LEFT JOIN article_settings atl ON (a.article_id = atl.article_id AND atl.setting_name = ? AND atl.locale = ?)
469  LEFT JOIN edit_decisions edec ON (a.article_id = edec.article_id)
470  LEFT JOIN edit_decisions edec2 ON (a.article_id = edec2.article_id AND edec.edit_decision_id < edec2.edit_decision_id)
471  WHERE a.journal_id = ?
472  AND e.editor_id = ?
473  AND a.submission_progress = 0' . (!empty($additionalWhereSql)?" AND ($additionalWhereSql)":'') . '
474  AND edec2.edit_decision_id IS NULL';
475 
476  if ($sectionId) {
477  $params[] = $sectionId;
478  $searchSql .= ' AND a.section_id = ?';
479  }
480 
481  $result =& $this->retrieveRange($sql . ' ' . $searchSql . ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : ''),
482  $params,
483  $rangeInfo
484  );
485 
486  return $result;
487  }
488 
492  function _generateUserNameSearchSQL($search, $searchMatch, $prefix, &$params) {
493  $first_last = $this->concat($prefix.'first_name', '\' \'', $prefix.'last_name');
494  $first_middle_last = $this->concat($prefix.'first_name', '\' \'', $prefix.'middle_name', '\' \'', $prefix.'last_name');
495  $last_comma_first = $this->concat($prefix.'last_name', '\', \'', $prefix.'first_name');
496  $last_comma_first_middle = $this->concat($prefix.'last_name', '\', \'', $prefix.'first_name', '\' \'', $prefix.'middle_name');
497  if ($searchMatch === 'is') {
498  $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(?))";
499  } elseif ($searchMatch === 'contains') {
500  $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(?))";
501  $search = '%' . $search . '%';
502  } else { // $searchMatch === 'startsWith'
503  $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(?))";
504  $search = $search . '%';
505  }
506  $params[] = $params[] = $params[] = $params[] = $params[] = $search;
507  return $searchSql;
508  }
509 
523  function &getSectionEditorSubmissionsInReview($sectionEditorId, $journalId, $sectionId, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
524  $result =& $this->_getUnfilteredSectionEditorSubmissions(
525  $sectionEditorId, $journalId, $sectionId,
526  $searchField, $searchMatch, $search,
527  $dateField, $dateFrom, $dateTo,
528  'a.status = ' . STATUS_QUEUED . ' AND e.can_review = 1 AND (edec.decision IS NULL OR edec.decision <> ' . SUBMISSION_EDITOR_DECISION_ACCEPT . ')',
529  $rangeInfo, $sortBy, $sortDirection
530  );
531  $returner = new DAOResultFactory($result, $this, '_returnSectionEditorSubmissionFromRow');
532  return $returner;
533  }
534 
548  function &getSectionEditorSubmissionsInEditing($sectionEditorId, $journalId, $sectionId, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
549  $result =& $this->_getUnfilteredSectionEditorSubmissions(
550  $sectionEditorId, $journalId, $sectionId,
551  $searchField, $searchMatch, $search,
552  $dateField, $dateFrom, $dateTo,
553  'a.status = ' . STATUS_QUEUED . ' AND e.can_edit = 1 AND edec.decision = ' . SUBMISSION_EDITOR_DECISION_ACCEPT,
554  $rangeInfo, $sortBy, $sortDirection
555  );
556  $returner = new DAOResultFactory($result, $this, '_returnSectionEditorSubmissionFromRow');
557  return $returner;
558  }
559 
573  function &getSectionEditorSubmissionsArchives($sectionEditorId, $journalId, $sectionId, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
575  $sectionEditorId, $journalId, $sectionId,
576  $searchField, $searchMatch, $search,
577  $dateField, $dateFrom, $dateTo,
578  'a.status <> ' . STATUS_QUEUED,
579  $rangeInfo, $sortBy, $sortDirection
580  );
581  $returner = new DAOResultFactory($result, $this, '_returnSectionEditorSubmissionFromRow');
582  return $returner;
583  }
584 
588  function &getSectionEditorSubmissionsCount($sectionEditorId, $journalId) {
589  $submissionsCount = array();
590  for($i = 0; $i < 2; $i++) {
591  $submissionsCount[$i] = 0;
592  }
593 
594  // Fetch a count of submissions in review.
595  // "d2" and "d" are used to fetch the single most recent
596  // editor decision.
597  $result =& $this->retrieve(
598  'SELECT COUNT(*) AS review_count
599  FROM articles a
600  LEFT JOIN edit_assignments e ON (a.article_id = e.article_id)
601  LEFT JOIN edit_assignments e2 ON (a.article_id = e2.article_id AND e.edit_id < e2.edit_id AND e2.editor_id=?)
602  LEFT JOIN edit_decisions d ON (a.article_id = d.article_id)
603  LEFT JOIN edit_decisions d2 ON (a.article_id = d2.article_id AND d.edit_decision_id < d2.edit_decision_id)
604  WHERE a.journal_id = ?
605  AND e.editor_id = ?
606  AND e2.edit_id IS NULL
607  AND a.submission_progress = 0
608  AND a.status = ' . STATUS_QUEUED . '
609  AND d2.edit_decision_id IS NULL
610  AND (d.decision IS NULL OR d.decision <> ' . SUBMISSION_EDITOR_DECISION_ACCEPT . ')',
611  array((int) $sectionEditorId, (int) $journalId, (int) $sectionEditorId)
612  );
613  $submissionsCount[0] = $result->Fields('review_count');
614  $result->Close();
615 
616  // Fetch a count of submissions in editing.
617  // "d2" and "d" are used to fetch the single most recent
618  // editor decision.
619  $result =& $this->retrieve(
620  'SELECT COUNT(*) AS editing_count
621  FROM articles a
622  LEFT JOIN edit_assignments e ON (a.article_id = e.article_id)
623  LEFT JOIN edit_assignments e2 ON (a.article_id = e2.article_id AND e.edit_id < e2.edit_id AND e2.editor_id=?)
624  LEFT JOIN edit_decisions d ON (a.article_id = d.article_id)
625  LEFT JOIN edit_decisions d2 ON (a.article_id = d2.article_id AND d.edit_decision_id < d2.edit_decision_id)
626  WHERE a.journal_id = ?
627  AND e.editor_id = ?
628  AND e2.edit_id IS NULL
629  AND a.submission_progress = 0
630  AND a.status = ' . STATUS_QUEUED . '
631  AND d2.edit_decision_id IS NULL
632  AND d.decision = ' . SUBMISSION_EDITOR_DECISION_ACCEPT,
633  array((int) $sectionEditorId, (int) $journalId, (int) $sectionEditorId)
634  );
635  $submissionsCount[1] = $result->Fields('editing_count');
636  $result->Close();
637  return $submissionsCount;
638  }
639 
640  //
641  // Miscellaneous
642  //
643 
648  function deleteDecisionsByArticle($articleId) {
649  return $this->update(
650  'DELETE FROM edit_decisions WHERE article_id = ?',
651  $articleId
652  );
653  }
654 
659  function deleteReviewRoundsByArticle($articleId) {
660  return $this->update(
661  'DELETE FROM review_rounds WHERE submission_id = ?',
662  $articleId
663  );
664  }
665 
671  function getEditorDecisions($articleId, $round = null) {
672  $decisions = array();
673 
674  if ($round == null) {
675  $result =& $this->retrieve(
676  'SELECT edit_decision_id, editor_id, decision, date_decided FROM edit_decisions WHERE article_id = ? ORDER BY edit_decision_id ASC', $articleId
677  );
678  } else {
679  $result =& $this->retrieve(
680  'SELECT edit_decision_id, editor_id, decision, date_decided FROM edit_decisions WHERE article_id = ? AND round = ? ORDER BY edit_decision_id ASC',
681  array($articleId, $round)
682  );
683  }
684 
685  while (!$result->EOF) {
686  $decisions[] = array(
687  'editDecisionId' => $result->fields['edit_decision_id'],
688  'editorId' => $result->fields['editor_id'],
689  'decision' => $result->fields['decision'],
690  'dateDecided' => $this->datetimeFromDB($result->fields['date_decided'])
691  );
692  $result->moveNext();
693  }
694  $result->Close();
695  unset($result);
696 
697  return $decisions;
698  }
699 
705  function getMaxReviewRound($articleId) {
706  $result =& $this->retrieve(
707  'SELECT MAX(round) FROM review_rounds WHERE submission_id = ?', $articleId
708  );
709  $returner = isset($result->fields[0]) ? $result->fields[0] : 0;
710 
711  $result->Close();
712  unset($result);
713 
714  return $returner;
715  }
716 
723  function reviewRoundExists($articleId, $round) {
724  $result =& $this->retrieve(
725  'SELECT COUNT(*) FROM review_rounds WHERE submission_id = ? AND round = ?', array($articleId, $round)
726  );
727  $returner = isset($result->fields[0]) && $result->fields[0] == 1 ? true : false;
728 
729  $result->Close();
730  unset($result);
731 
732  return $returner;
733  }
734 
741  function reviewerExists($articleId, $reviewerId, $round) {
742  $result =& $this->retrieve(
743  'SELECT COUNT(*) FROM review_assignments WHERE submission_id = ? AND reviewer_id = ? AND round = ? AND cancelled = 0', array((int) $articleId, (int) $reviewerId, (int) $round)
744  );
745  $returner = isset($result->fields[0]) && $result->fields[0] == 1 ? true : false;
746 
747  $result->Close();
748  unset($result);
749 
750  return $returner;
751  }
752 
764  function &getReviewersForArticle($journalId, $articleId, $round, $searchType = null, $search = null, $searchMatch = null, $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
765  // Convert the field being searched for to a DB element to select on
766  $searchTypeMap = array(
767  USER_FIELD_FIRSTNAME => 'u.first_name',
768  USER_FIELD_LASTNAME => 'u.last_name',
769  USER_FIELD_USERNAME => 'u.username',
770  USER_FIELD_EMAIL => 'u.email',
771  USER_FIELD_INTERESTS => 'cves.setting_value'
772  );
773 
774  // Generate the SQL used to filter the results based on what the user is searching for
775  $paramArray = array((int) $articleId, (int) $round);
776  $joinInterests = false;
777  if($searchType == USER_FIELD_INTERESTS) {
778  $joinInterests = true;
779  }
780 
781  // Push some extra default parameters to the SQL parameter array
782  $paramArray[] = (int) $journalId;
783  $paramArray[] = ROLE_ID_REVIEWER;
784 
785  $searchSql = '';
786  if (isset($search) && isset($searchTypeMap[$searchType])) {
787  $fieldName = $searchTypeMap[$searchType];
788  switch ($searchMatch) {
789  case 'is':
790  $searchSql = "AND LOWER($fieldName) = LOWER(?)";
791  $paramArray[] = $search;
792  break;
793  case 'contains':
794  $searchSql = "AND LOWER($fieldName) LIKE LOWER(?)";
795  $paramArray[] = '%' . $search . '%';
796  break;
797  case 'startsWith':
798  $searchSql = "AND LOWER($fieldName) LIKE LOWER(?)";
799  $paramArray[] = $search . '%';
800  break;
801  }
802  } elseif (isset($search)) switch ($searchType) {
803  case USER_FIELD_USERID:
804  $searchSql = 'AND user_id=?';
805  $paramArray[] = $search;
806  break;
807  case USER_FIELD_INITIAL:
808  $searchSql = 'AND (LOWER(last_name) LIKE LOWER(?) OR LOWER(username) LIKE LOWER(?))';
809  $paramArray[] = $search . '%';
810  $paramArray[] = $search . '%';
811  break;
812  }
813 
814  $interestJoinSql = ($joinInterests ? '
815  LEFT JOIN user_interests ui ON (ui.user_id = u.user_id)
816  LEFT JOIN controlled_vocab_entry_settings cves ON (cves.controlled_vocab_entry_id = ui.controlled_vocab_entry_id) ':'');
817 
818  $result =& $this->retrieveRange(
819  'SELECT DISTINCT
820  u.user_id,
821  u.last_name,
822  ar.review_id,
823  (SELECT AVG(ra.quality) FROM review_assignments ra WHERE ra.reviewer_id = u.user_id) AS average_quality,
824  (SELECT COUNT(ac.review_id) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL AND ac.cancelled = 0) AS completed,
825  (SELECT COUNT(ac.review_id) FROM review_assignments ac, articles a WHERE
826  ac.reviewer_id = u.user_id AND
827  ac.submission_id = a.article_id AND
828  ac.date_notified IS NOT NULL AND
829  ac.date_completed IS NULL AND
830  ac.cancelled = 0 AND
831  ac.declined = 0 AND
832  a.status <> '.STATUS_QUEUED.') AS incomplete,
833  (SELECT MAX(ac.date_notified) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL) AS latest,
834  (SELECT AVG(ac.date_completed-ac.date_notified) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL AND ac.cancelled = 0) AS average
835  FROM users u
836  LEFT JOIN review_assignments ra ON (ra.reviewer_id = u.user_id)
837  LEFT JOIN review_assignments ar ON (ar.reviewer_id = u.user_id AND ar.cancelled = 0 AND ar.submission_id = ? AND ar.round = ?)
838  LEFT JOIN roles r ON (r.user_id = u.user_id)
839  LEFT JOIN articles a ON (ra.submission_id = a.article_id)
840  '.$interestJoinSql.'
841  WHERE u.user_id = r.user_id AND
842  r.journal_id = ? AND
843  r.role_id = ? ' . $searchSql . 'GROUP BY u.user_id, u.last_name, ar.review_id' .
844  ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : ''),
845  $paramArray, $rangeInfo
846  );
847 
848  $returner = new DAOResultFactory($result, $this, '_returnReviewerUserFromRow');
849  return $returner;
850  }
851 
852  function &_returnReviewerUserFromRow(&$row) { // FIXME
853  $user =& $this->userDao->getById($row['user_id']);
854  $user->review_id = $row['review_id'];
855  $user->declined = $row['declined'];
856 
857  HookRegistry::call('SectionEditorSubmissionDAO::_returnReviewerUserFromRow', array(&$user, &$row));
858 
859  return $user;
860  }
861 
868  function &getReviewersNotAssignedToArticle($journalId, $articleId) {
869  $users = array();
870 
871  $result =& $this->retrieve(
872  'SELECT u.*
873  FROM users u
874  LEFT JOIN roles r ON (r.user_id = u.user_id)
875  LEFT JOIN review_assignments a ON (a.reviewer_id = u.user_id AND a.article_id = ?)
876  WHERE r.journal_id = ? AND
877  r.role_id = ? AND
878  a.article_id IS NULL
879  ORDER BY last_name, first_name',
880  array($articleId, $journalId, RoleDAO::getRoleIdFromPath('reviewer'))
881  );
882 
883  while (!$result->EOF) {
884  $users[] =& $this->userDao->_returnUserFromRowWithData($result->GetRowAssoc(false));
885  $result->moveNext();
886  }
887 
888  $result->Close();
889  unset($result);
890 
891  return $users;
892  }
893 
900  function copyeditorExists($articleId, $copyeditorId) {
901  $result =& $this->retrieve(
902  'SELECT COUNT(*) FROM signoffs WHERE assoc_id = ? AND assoc_type = ? AND user_id = ? AND symbolic = ?', array($articleId, ASSOC_TYPE_ARTICLE, $copyeditorId, 'SIGNOFF_COPYEDITING_INITIAL')
903  );
904  return isset($result->fields[0]) && $result->fields[0] == 1 ? true : false;
905  }
906 
913  function getLayoutEditorStatistics($journalId, $layoutEditorId = null) {
914  $statistics = array();
915 
916  // WARNING: This is reused for the next two queries
917  $params = array(
918  (int) $journalId,
919  'SIGNOFF_LAYOUT',
920  'SIGNOFF_PROOFREADING_LAYOUT',
921  ASSOC_TYPE_ARTICLE,
922  ASSOC_TYPE_ARTICLE
923  );
924  if ($layoutEditorId) $params[] = (int) $layoutEditorId;
925 
926  // Get counts of completed submissions
927  $result =& $this->retrieve(
928  'SELECT sl.user_id AS editor_id,
929  COUNT(sl.assoc_id) AS complete
930  FROM signoffs sl,
931  articles a
932  INNER JOIN signoffs sp ON (sp.assoc_id = a.article_id)
933  WHERE sl.assoc_id = a.article_id AND
934  a.status <> ' . STATUS_QUEUED . ' AND
935  sl.date_notified IS NOT NULL AND
936  a.journal_id = ? AND
937  sl.symbolic = ? AND
938  sp.symbolic = ? AND
939  sl.assoc_type = ? AND
940  sp.assoc_type = ?
941  ' . ($layoutEditorId?' AND sl.user_id = ?':'') . '
942  GROUP BY sl.user_id',
943  $params
944  );
945  while (!$result->EOF) {
946  $row = $result->GetRowAssoc(false);
947  if (!isset($statistics[$row['editor_id']])) $statistics[$row['editor_id']] = array();
948  $statistics[$row['editor_id']]['complete'] = $row['complete'];
949  $result->MoveNext();
950  }
951 
952  $result->Close();
953  unset($result);
954 
955  // Get counts of incomplete submissions
956  $result =& $this->retrieve(
957  'SELECT sl.user_id AS editor_id,
958  COUNT(sl.assoc_id) AS incomplete
959  FROM signoffs sl,
960  articles a
961  INNER JOIN signoffs sp ON (sp.assoc_id = a.article_id)
962  WHERE sl.assoc_id = a.article_id AND
963  a.status = ' . STATUS_QUEUED . ' AND
964  sl.date_notified IS NOT NULL AND
965  a.journal_id = ? AND
966  sl.symbolic = ? AND
967  sp.symbolic = ? AND
968  sl.assoc_type = ? AND
969  sp.assoc_type = ?
970  ' . ($layoutEditorId?' AND sl.user_id = ?':'') . '
971  GROUP BY sl.user_id',
972  $params
973  );
974  while (!$result->EOF) {
975  $row = $result->GetRowAssoc(false);
976  if (!isset($statistics[$row['editor_id']])) $statistics[$row['editor_id']] = array();
977  $statistics[$row['editor_id']]['incomplete'] = $row['incomplete'];
978  $result->MoveNext();
979  }
980 
981  $result->Close();
982  unset($result);
983 
984  // Get last assignment date
985  $params = array(
986  (int) $journalId,
987  'SIGNOFF_LAYOUT',
988  ASSOC_TYPE_ARTICLE
989  );
990  if ($layoutEditorId) $params[] = (int) $layoutEditorId;
991 
992  $result =& $this->retrieve(
993  'SELECT sl.user_id AS editor_id,
994  MAX(sl.date_notified) AS last_assigned
995  FROM signoffs sl,
996  articles a
997  WHERE sl.assoc_id = a.article_id AND
998  a.journal_id = ? AND
999  sl.symbolic = ? AND
1000  sl.assoc_type = ?
1001  ' . ($layoutEditorId?' AND sl.user_id = ?':'') . '
1002  GROUP BY sl.user_id',
1003  $params
1004  );
1005  while (!$result->EOF) {
1006  $row = $result->GetRowAssoc(false);
1007  if (!isset($statistics[$row['editor_id']])) $statistics[$row['editor_id']] = array();
1008  $statistics[$row['editor_id']]['last_assigned'] = $this->datetimeFromDB($row['last_assigned']);
1009  $result->MoveNext();
1010  }
1011 
1012  $result->Close();
1013  unset($result);
1014 
1015  return $statistics;
1016  }
1017 
1023  function getReviewerStatistics($journalId) {
1024  $statistics = array();
1025 
1026  // Get latest review request date
1027  $result =& $this->retrieve(
1028  'SELECT r.reviewer_id, MAX(r.date_notified) AS last_notified
1029  FROM review_assignments r,
1030  articles a
1031  WHERE r.submission_id = a.article_id AND
1032  a.journal_id = ?
1033  GROUP BY r.reviewer_id',
1034  (int) $journalId
1035  );
1036  while (!$result->EOF) {
1037  $row = $result->GetRowAssoc(false);
1038  if (!isset($statistics[$row['reviewer_id']])) $statistics[$row['reviewer_id']] = array();
1039  $statistics[$row['reviewer_id']]['last_notified'] = $this->datetimeFromDB($row['last_notified']);
1040  $result->MoveNext();
1041  }
1042 
1043  $result->Close();
1044  unset($result);
1045 
1046  // Get incomplete submission count
1047  $result =& $this->retrieve(
1048  'SELECT r.reviewer_id, COUNT(*) AS incomplete
1049  FROM review_assignments r,
1050  articles a
1051  WHERE r.submission_id = a.article_id AND
1052  r.date_notified IS NOT NULL AND
1053  r.date_completed IS NULL AND
1054  r.cancelled = 0 AND
1055  r.declined = 0 AND
1056  r.date_completed IS NULL AND r.declined <> 1 AND (r.cancelled = 0 OR r.cancelled IS NULL) AND a.status = ' . STATUS_QUEUED . ' AND
1057  a.journal_id = ?
1058  GROUP BY r.reviewer_id',
1059  (int) $journalId
1060  );
1061  while (!$result->EOF) {
1062  $row = $result->GetRowAssoc(false);
1063  if (!isset($statistics[$row['reviewer_id']])) $statistics[$row['reviewer_id']] = array();
1064  $statistics[$row['reviewer_id']]['incomplete'] = $row['incomplete'];
1065  $result->MoveNext();
1066  }
1067 
1068  $result->Close();
1069  unset($result);
1070 
1071  // Calculate time taken for completed reviews
1072  $result =& $this->retrieve(
1073  'SELECT r.reviewer_id, r.date_notified, r.date_completed
1074  FROM review_assignments r,
1075  articles a
1076  WHERE r.submission_id = a.article_id AND
1077  r.date_notified IS NOT NULL AND
1078  r.date_completed IS NOT NULL AND
1079  r.declined = 0 AND
1080  a.journal_id = ?',
1081  (int) $journalId
1082  );
1083  while (!$result->EOF) {
1084  $row = $result->GetRowAssoc(false);
1085  if (!isset($statistics[$row['reviewer_id']])) $statistics[$row['reviewer_id']] = array();
1086 
1087  $completed = strtotime($this->datetimeFromDB($row['date_completed']));
1088  $notified = strtotime($this->datetimeFromDB($row['date_notified']));
1089  if (isset($statistics[$row['reviewer_id']]['total_span'])) {
1090  $statistics[$row['reviewer_id']]['total_span'] += $completed - $notified;
1091  $statistics[$row['reviewer_id']]['completed_review_count'] += 1;
1092  } else {
1093  $statistics[$row['reviewer_id']]['total_span'] = $completed - $notified;
1094  $statistics[$row['reviewer_id']]['completed_review_count'] = 1;
1095  }
1096 
1097  // Calculate the average length of review in weeks.
1098  $statistics[$row['reviewer_id']]['average_span'] = (($statistics[$row['reviewer_id']]['total_span'] / $statistics[$row['reviewer_id']]['completed_review_count']) / 60 / 60 / 24 / 7);
1099  $result->MoveNext();
1100  }
1101 
1102  $result->Close();
1103  unset($result);
1104 
1105  return $statistics;
1106  }
1107 
1114  function getCopyeditorStatistics($journalId, $copyeditorId = null) {
1115  $statistics = array();
1116 
1117  // WARNING: This is reused for the next two queries.
1118  $params = array(
1119  (int) $journalId,
1120  'SIGNOFF_COPYEDITING_INITIAL',
1121  ASSOC_TYPE_ARTICLE
1122  );
1123  if ($copyeditorId) $params[] = (int) $copyeditorId;
1124 
1125  // Get counts of completed submissions
1126  $result =& $this->retrieve(
1127  'SELECT sc.user_id AS editor_id,
1128  COUNT(sc.assoc_id) AS complete
1129  FROM signoffs sc,
1130  articles a
1131  LEFT JOIN published_articles pa ON (pa.article_id = a.article_id)
1132  WHERE sc.assoc_id = a.article_id AND
1133  (pa.date_published IS NOT NULL AND a.status <> ' . STATUS_QUEUED . ') AND
1134  a.journal_id = ? AND
1135  sc.symbolic = ? AND
1136  sc.assoc_type = ?
1137  ' . ($copyeditorId?' AND sc.user_id = ?':'') . '
1138  GROUP BY sc.user_id',
1139  $params
1140  );
1141  while (!$result->EOF) {
1142  $row = $result->GetRowAssoc(false);
1143  if (!isset($statistics[$row['editor_id']])) $statistics[$row['editor_id']] = array();
1144  $statistics[$row['editor_id']]['complete'] = $row['complete'];
1145  $result->MoveNext();
1146  }
1147 
1148  $result->Close();
1149  unset($result);
1150 
1151  // Get counts of incomplete submissions
1152  $result =& $this->retrieve(
1153  'SELECT sc.user_id AS editor_id,
1154  COUNT(sc.assoc_id) AS incomplete
1155  FROM signoffs sc,
1156  articles a
1157  LEFT JOIN published_articles pa ON (pa.article_id = a.article_id)
1158  LEFT JOIN issues i ON (i.issue_id = pa.issue_id)
1159  WHERE sc.assoc_id = a.article_id AND
1160  NOT (pa.date_published IS NOT NULL AND a.status <> ' . STATUS_QUEUED . ') AND
1161  i.date_published IS NULL AND a.status = ' . STATUS_QUEUED . ' AND
1162  a.journal_id = ? AND
1163  sc.symbolic = ? AND
1164  sc.assoc_type = ?
1165  ' . ($copyeditorId?' AND sc.user_id = ?':'') . '
1166  GROUP BY sc.user_id',
1167  $params
1168  );
1169  while (!$result->EOF) {
1170  $row = $result->GetRowAssoc(false);
1171  if (!isset($statistics[$row['editor_id']])) $statistics[$row['editor_id']] = array();
1172  $statistics[$row['editor_id']]['incomplete'] = $row['incomplete'];
1173  $result->MoveNext();
1174  }
1175 
1176  $result->Close();
1177  unset($result);
1178 
1179  // Get last assignment date
1180  $params = array(
1181  (int) $journalId,
1182  'SIGNOFF_COPYEDITING_INITIAL',
1183  ASSOC_TYPE_ARTICLE
1184  );
1185  if ($copyeditorId) $params[] = (int) $copyeditorId;
1186 
1187  $result =& $this->retrieve(
1188  'SELECT sc.user_id AS editor_id,
1189  MAX(sc.date_notified) AS last_assigned
1190  FROM signoffs sc,
1191  articles a
1192  WHERE sc.assoc_id = a.article_id AND
1193  a.journal_id = ? AND
1194  sc.symbolic = ? AND
1195  sc.assoc_type = ?
1196  ' . ($copyeditorId?' AND sc.user_id = ?':'') . '
1197  GROUP BY sc.user_id',
1198  $params
1199  );
1200  while (!$result->EOF) {
1201  $row = $result->GetRowAssoc(false);
1202  if (!isset($statistics[$row['editor_id']])) $statistics[$row['editor_id']] = array();
1203  $statistics[$row['editor_id']]['last_assigned'] = $this->datetimeFromDB($row['last_assigned']);
1204  $result->MoveNext();
1205  }
1206 
1207  $result->Close();
1208  unset($result);
1209 
1210  return $statistics;
1211  }
1212 
1219  function getProofreaderStatistics($journalId, $proofreaderId = null) {
1220  $statistics = array();
1221 
1222  // WARNING: This is used in the next three queries
1223  $params = array(
1224  (int) $journalId,
1225  'SIGNOFF_PROOFREADING_PROOFREADER',
1226  ASSOC_TYPE_ARTICLE
1227  );
1228  if ($proofreaderId) $params[] = (int) $proofreaderId;
1229 
1230  // Get counts of completed submissions
1231  $result =& $this->retrieve(
1232  'SELECT sp.user_id AS editor_id,
1233  COUNT(sp.assoc_id) AS complete
1234  FROM signoffs sp,
1235  articles a
1236  WHERE sp.assoc_id = a.article_id AND
1237  sp.date_completed IS NOT NULL AND
1238  a.journal_id = ? AND
1239  sp.symbolic = ? AND
1240  sp.assoc_type = ? AND
1241  a.status <> ' . STATUS_QUEUED . '
1242  ' . ($proofreaderId?' AND sp.user_id = ?':'') . '
1243  GROUP BY sp.user_id',
1244  $params
1245  );
1246  while (!$result->EOF) {
1247  $row = $result->GetRowAssoc(false);
1248  if (!isset($statistics[$row['editor_id']])) $statistics[$row['editor_id']] = array();
1249  $statistics[$row['editor_id']]['complete'] = $row['complete'];
1250  $result->MoveNext();
1251  }
1252 
1253  $result->Close();
1254  unset($result);
1255 
1256  // Get counts of incomplete submissions
1257  $result =& $this->retrieve(
1258  'SELECT sp.user_id AS editor_id,
1259  COUNT(sp.assoc_id) AS incomplete
1260  FROM signoffs sp,
1261  articles a
1262  WHERE sp.assoc_id = a.article_id AND
1263  sp.date_completed IS NULL AND
1264  sp.date_notified IS NOT NULL AND
1265  a.status = ' . STATUS_QUEUED . ' AND
1266  a.journal_id = ? AND
1267  sp.symbolic = ? AND
1268  sp.assoc_type = ?
1269  ' . ($proofreaderId?' AND sp.user_id = ?':'') . '
1270  GROUP BY sp.user_id',
1271  $params
1272  );
1273  while (!$result->EOF) {
1274  $row = $result->GetRowAssoc(false);
1275  if (!isset($statistics[$row['editor_id']])) $statistics[$row['editor_id']] = array();
1276  $statistics[$row['editor_id']]['incomplete'] = $row['incomplete'];
1277  $result->MoveNext();
1278  }
1279 
1280  $result->Close();
1281  unset($result);
1282 
1283  // Get last assignment date
1284  $result =& $this->retrieve(
1285  'SELECT sp.user_id AS editor_id,
1286  MAX(sp.date_notified) AS last_assigned
1287  FROM signoffs sp,
1288  articles a
1289  WHERE sp.assoc_id = a.article_id AND
1290  a.journal_id = ? AND
1291  sp.symbolic = ? AND
1292  sp.assoc_type = ?
1293  ' . ($proofreaderId?' AND sp.user_id = ?':'') . '
1294  GROUP BY sp.user_id',
1295  $params
1296  );
1297  while (!$result->EOF) {
1298  $row = $result->GetRowAssoc(false);
1299  if (!isset($statistics[$row['editor_id']])) $statistics[$row['editor_id']] = array();
1300  $statistics[$row['editor_id']]['last_assigned'] = $this->datetimeFromDB($row['last_assigned']);
1301  $result->MoveNext();
1302  }
1303  $result->Close();
1304  unset($result);
1305 
1306  return $statistics;
1307  }
1308 
1314  function getSortMapping($heading) {
1315  switch ($heading) {
1316  case 'id': return 'a.article_id';
1317  case 'submitDate': return 'a.date_submitted';
1318  case 'section': return 'section_abbrev';
1319  case 'authors': return 'author_name';
1320  case 'title': return 'submission_clean_title';
1321  case 'active': return 'incomplete';
1322  case 'subCopyedit': return 'copyedit_completed';
1323  case 'subLayout': return 'layout_completed';
1324  case 'subProof': return 'proofread_completed';
1325  case 'reviewerName': return 'u.last_name';
1326  case 'quality': return 'average_quality';
1327  case 'done': return 'completed';
1328  case 'latest': return 'latest';
1329  case 'active': return 'active';
1330  case 'average': return 'average';
1331  case 'name': return 'u.last_name';
1332  case 'status': return 'a.status';
1333  default: return null;
1334  }
1335  }
1336 }
1337 
1338 ?>
Operations for retrieving and modifying objects from a database.
Definition: DAO.inc.php:29
& getReviewersNotAssignedToArticle($journalId, $articleId)
copyeditorExists($articleId, $copyeditorId)
& retrieve($sql, $params=false, $callHooks=true)
Definition: DAO.inc.php:83
getLayoutEditorStatistics($journalId, $layoutEditorId=null)
& getSectionEditorSubmissionsCount($sectionEditorId, $journalId)
getInsertId($table= '', $id= '', $callHooks=true)
Definition: DAO.inc.php:252
CopyeditorSubmission class.
getRoleIdFromPath($rolePath)
& getSectionEditorSubmissionsInEditing($sectionEditorId, $journalId, $sectionId, $searchField=null, $searchMatch=null, $search=null, $dateField=null, $dateFrom=null, $dateTo=null, $rangeInfo=null, $sortBy=null, $sortDirection=SORT_DIRECTION_ASC)
reviewerExists($articleId, $reviewerId, $round)
getCopyeditorStatistics($journalId, $copyeditorId=null)
& retrieveRange($sql, $params=false, $dbResultRange=null, $callHooks=true)
Definition: DAO.inc.php:176
updateSectionEditorSubmission(&$sectionEditorSubmission)
& getReviewersForArticle($journalId, $articleId, $round, $searchType=null, $search=null, $searchMatch=null, $rangeInfo=null, $sortBy=null, $sortDirection=SORT_DIRECTION_ASC)
concat()
Definition: DAO.inc.php:52
datetimeFromDB($dt)
Definition: DAO.inc.php:316
& _getUnfilteredSectionEditorSubmissions($sectionEditorId, $journalId, $sectionId=0, $searchField=null, $searchMatch=null, $search=null, $dateField=null, $dateFrom=null, $dateTo=null, $additionalWhereSql= '', $rangeInfo=null, $sortBy=null, $sortDirection=SORT_DIRECTION_ASC)
Wrapper around ADORecordSet providing &quot;factory&quot; features for generating objects from DAOs...
& getSectionEditorSubmissionsInReview($sectionEditorId, $journalId, $sectionId, $searchField=null, $searchMatch=null, $search=null, $dateField=null, $dateFrom=null, $dateTo=null, $rangeInfo=null, $sortBy=null, $sortDirection=SORT_DIRECTION_ASC)
Operations for retrieving and modifying SectionEditorSubmission objects.
& getDAO($name, $dbconn=null)
call($hookName, $args=null)
_generateUserNameSearchSQL($search, $searchMatch, $prefix, &$params)
SectionEditorSubmission class.
datetimeToDB($dt)
Definition: DAO.inc.php:296
update($sql, $params=false, $callHooks=true, $dieOnError=true)
Definition: DAO.inc.php:211
getDirectionMapping($direction)
Definition: DAO.inc.php:588
& getSectionEditorSubmissionsArchives($sectionEditorId, $journalId, $sectionId, $searchField=null, $searchMatch=null, $search=null, $dateField=null, $dateFrom=null, $dateTo=null, $rangeInfo=null, $sortBy=null, $sortDirection=SORT_DIRECTION_ASC)
getProofreaderStatistics($journalId, $proofreaderId=null)
& getSectionEditorSubmissions($sectionEditorId, $journalId, $status=true)