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