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