Open Journal Systems  2.4.8
 All Classes Namespaces Functions Variables Groups Pages
JournalStatisticsDAO.inc.php
1 <?php
2 
16 define('REPORT_TYPE_JOURNAL', 0x00001);
17 define('REPORT_TYPE_EDITOR', 0x00002);
18 define('REPORT_TYPE_REVIEWER', 0x00003);
19 define('REPORT_TYPE_SECTION', 0x00004);
20 
21 class JournalStatisticsDAO extends DAO {
29  function getFirstActivityDate($journalId) {
30  $result =& $this->retrieve(
31  'SELECT LEAST(a.date_submitted, COALESCE(pa.date_published, NOW()), COALESCE(i.date_published, NOW())) AS first_date
32  FROM articles a
33  LEFT JOIN published_articles pa ON (a.article_id = pa.article_id)
34  LEFT JOIN issues i ON (pa.issue_id = i.issue_id)
35  LEFT JOIN articles a2 ON (a2.article_id < a.article_id AND a2.date_submitted IS NOT NULL AND a2.journal_id = ?)
36  WHERE a2.article_id IS NULL AND
37  a.date_submitted IS NOT NULL AND
38  a.journal_id = ?',
39  array(
40  (int) $journalId,
41  (int) $journalId
42  )
43  );
44 
45  $row = $result->GetRowAssoc(false);
46  $firstActivityDate = $this->datetimeFromDB($row['first_date']);
47  $result->Close();
48  // An earlier user registration can override the earliest article activity date
49  $result =& $this->retrieve(
50  'SELECT MIN(u.date_registered) AS first_date FROM users u JOIN roles r ON (u.user_id = r.user_id) WHERE r.journal_id = ?',
51  array(
52  (int) $journalId
53  )
54  );
55  $row = $result->GetRowAssoc(false);
56  $firstUserDate = $this->datetimeFromDB($row['first_date']);
57  if (!$firstActivityDate || ($firstUserDate && $firstActivityDate && strtotime($firstUserDate) < strtotime($firstActivityDate))) {
58  $firstActivityDate = $firstUserDate;
59  }
60  if (!$firstActivityDate) return null;
61  return strtotime($firstActivityDate);
62 
63  }
64 
74  function getArticleStatistics($journalId, $sectionIds = null, $dateStart = null, $dateEnd = null) {
75  // Bring in status constants
76  import('classes.article.Article');
77 
78  $params = array($journalId);
79  if (!empty($sectionIds)) {
80  $sectionSql = ' AND (a.section_id = ?';
81  $params[] = array_shift($sectionIds);
82  foreach ($sectionIds as $sectionId) {
83  $sectionSql .= ' OR a.section_id = ?';
84  $params[] = $sectionId;
85  }
86  $sectionSql .= ')';
87  } else $sectionSql = '';
88 
89  $sql = 'SELECT a.article_id,
90  a.date_submitted,
91  pa.date_published,
92  pa.published_article_id,
93  d.decision,
94  a.status
95  FROM articles a
96  LEFT JOIN published_articles pa ON (a.article_id = pa.article_id)
97  LEFT JOIN edit_decisions d ON (d.article_id = a.article_id)
98  WHERE a.journal_id = ?' .
99  ($dateStart !== null ? ' AND a.date_submitted >= ' . $this->datetimeToDB($dateStart) : '') .
100  ($dateEnd !== null ? ' AND a.date_submitted <= ' . $this->datetimeToDB($dateEnd) : '') .
101  $sectionSql .
102  ' ORDER BY a.article_id, d.date_decided DESC';
103 
104  $result =& $this->retrieve($sql, $params);
105 
106  $returner = array(
107  'numSubmissions' => 0,
108  'numReviewedSubmissions' => 0,
109  'numPublishedSubmissions' => 0,
110  'submissionsAccept' => 0,
111  'submissionsDecline' => 0,
112  'submissionsAcceptPercent' => 0,
113  'submissionsDeclinePercent' => 0,
114  'daysToPublication' => 0
115  );
116 
117  // Track which articles we're including
118  $articleIds = array();
119 
120  $totalTimeToPublication = 0;
121  $timeToPublicationCount = 0;
122 
123  while (!$result->EOF) {
124  $row = $result->GetRowAssoc(false);
125 
126  // For each article, pick the most recent editor
127  // decision only and ignore the rest. Depends on sort
128  // order. FIXME -- there must be a better way of doing
129  // this that's database independent.
130  if (!in_array($row['article_id'], $articleIds)) {
131  $articleIds[] = $row['article_id'];
132  $returner['numSubmissions']++;
133 
134  if (!empty($row['published_article_id']) && $row['status'] == STATUS_PUBLISHED) {
135  $returner['numPublishedSubmissions']++;
136  }
137 
138  if (!empty($row['date_submitted']) && !empty($row['date_published']) && $row['status'] == STATUS_PUBLISHED) {
139  $timeSubmitted = strtotime($this->datetimeFromDB($row['date_submitted']));
140  $timePublished = strtotime($this->datetimeFromDB($row['date_published']));
141  if ($timePublished > $timeSubmitted) {
142  $totalTimeToPublication += ($timePublished - $timeSubmitted);
143  $timeToPublicationCount++;
144  }
145  }
146 
147  import('classes.submission.common.Action');
148  switch ($row['decision']) {
149  case SUBMISSION_EDITOR_DECISION_ACCEPT:
150  $returner['submissionsAccept']++;
151  $returner['numReviewedSubmissions']++;
152  break;
153  case SUBMISSION_EDITOR_DECISION_DECLINE:
154  $returner['submissionsDecline']++;
155  $returner['numReviewedSubmissions']++;
156  break;
157  }
158  }
159 
160  $result->moveNext();
161  }
162 
163  $result->Close();
164  unset($result);
165 
166  // Calculate percentages where necessary
167  if ($returner['numReviewedSubmissions'] != 0) {
168  $returner['submissionsAcceptPercent'] = round($returner['submissionsAccept'] * 100 / $returner['numReviewedSubmissions']);
169  $returner['submissionsDeclinePercent'] = round($returner['submissionsDecline'] * 100 / $returner['numReviewedSubmissions']);
170  }
171 
172  if ($timeToPublicationCount != 0) {
173  // Keep one sig fig
174  $returner['daysToPublication'] = round($totalTimeToPublication / $timeToPublicationCount / 60 / 60 / 24);
175  }
176 
177  return $returner;
178  }
179 
188  function getUserStatistics($journalId, $dateStart = null, $dateEnd = null) {
189  $roleDao =& DAORegistry::getDAO('RoleDAO');
190 
191  // Get count of total users for this journal
192  $result =& $this->retrieve(
193  'SELECT COUNT(DISTINCT r.user_id) FROM roles r, users u WHERE r.user_id = u.user_id AND r.journal_id = ?' .
194  ($dateStart !== null ? ' AND u.date_registered >= ' . $this->datetimeToDB($dateStart) : '') .
195  ($dateEnd !== null ? ' AND u.date_registered <= ' . $this->datetimeToDB($dateEnd) : ''),
196  $journalId
197  );
198 
199  $returner = array(
200  'totalUsersCount' => $result->fields[0]
201  );
202 
203  $result->Close();
204  unset($result);
205 
206  // Get user counts for each role.
207  $result =& $this->retrieve(
208  'SELECT r.role_id, COUNT(r.user_id) AS role_count FROM roles r LEFT JOIN users u ON (r.user_id = u.user_id) WHERE r.journal_id = ?' .
209  ($dateStart !== null ? ' AND u.date_registered >= ' . $this->datetimeToDB($dateStart) : '') .
210  ($dateEnd !== null ? ' AND u.date_registered <= ' . $this->datetimeToDB($dateEnd) : '') .
211  ' GROUP BY r.role_id',
212  $journalId
213  );
214 
215  while (!$result->EOF) {
216  $row = $result->GetRowAssoc(false);
217  $returner[$roleDao->getRolePath($row['role_id'])] = $row['role_count'];
218  $result->moveNext();
219  }
220 
221  $result->Close();
222  unset($result);
223 
224  return $returner;
225  }
226 
234  function getSubscriptionStatistics($journalId, $dateStart = null, $dateEnd = null) {
235  $result =& $this->retrieve(
236  'SELECT st.type_id,
237  sts.setting_value AS type_name,
238  count(s.subscription_id) AS type_count
239  FROM subscription_types st
240  LEFT JOIN journals j ON (j.journal_id = st.journal_id)
241  LEFT JOIN subscription_type_settings sts ON (st.type_id = sts.type_id AND sts.setting_name = ? AND sts.locale = j.primary_locale),
242  subscriptions s
243  WHERE st.journal_id = ?
244  AND s.type_id = st.type_id' .
245  ($dateStart !== null ? ' AND s.date_start >= ' . $this->datetimeToDB($dateStart) : '') .
246  ($dateEnd !== null ? ' AND s.date_start <= ' . $this->datetimeToDB($dateEnd) : '') .
247  ' GROUP BY st.type_id, sts.setting_value',
248  array('name', $journalId)
249  );
250 
251  $returner = array();
252 
253  while (!$result->EOF) {
254  $row = $result->getRowAssoc(false);
255  $returner[$row['type_id']] = array(
256  'name' => $row['type_name'],
257  'count' => $row['type_count']
258  );
259  $result->moveNext();
260  }
261  $result->Close();
262  unset($result);
263 
264  return $returner;
265  }
266 
275  function getIssueStatistics($journalId, $dateStart = null, $dateEnd = null) {
276  $result =& $this->retrieve(
277  'SELECT COUNT(*) AS count, published FROM issues WHERE journal_id = ?' .
278  ($dateStart !== null ? ' AND date_published >= ' . $this->datetimeToDB($dateStart) : '') .
279  ($dateEnd !== null ? ' AND date_published <= ' . $this->datetimeToDB($dateEnd) : '') .
280  ' GROUP BY published',
281  $journalId
282  );
283 
284  $returner = array(
285  'numPublishedIssues' => 0,
286  'numUnpublishedIssues' => 0
287  );
288 
289  while (!$result->EOF) {
290  $row = $result->GetRowAssoc(false);
291 
292  if ($row['published']) {
293  $returner['numPublishedIssues'] = $row['count'];
294  } else {
295  $returner['numUnpublishedIssues'] = $row['count'];
296  }
297  $result->moveNext();
298  }
299 
300  $result->Close();
301  unset($result);
302 
303  $returner['numIssues'] = $returner['numPublishedIssues'] + $returner['numUnpublishedIssues'];
304 
305  return $returner;
306  }
307 
316  function getReviewerStatistics($journalId, $sectionIds, $dateStart = null, $dateEnd = null) {
317  $params = array($journalId);
318  if (!empty($sectionIds)) {
319  $sectionSql = ' AND (a.section_id = ?';
320  $params[] = array_shift($sectionIds);
321  foreach ($sectionIds as $sectionId) {
322  $sectionSql .= ' OR a.section_id = ?';
323  $params[] = $sectionId;
324  }
325  $sectionSql .= ')';
326  } else $sectionSql = '';
327 
328  $sql = 'SELECT a.article_id,
329  af.date_uploaded AS date_rv_uploaded,
330  r.review_id,
331  u.date_registered,
332  r.reviewer_id,
333  r.quality,
334  r.date_assigned,
335  r.date_completed
336  FROM articles a,
337  article_files af,
338  review_assignments r
339  LEFT JOIN users u ON (u.user_id = r.reviewer_id)
340  WHERE a.journal_id = ?
341  AND r.submission_id = a.article_id
342  AND af.article_id = a.article_id
343  AND af.file_id = a.review_file_id
344  AND af.revision = 1' .
345  ($dateStart !== null ? ' AND a.date_submitted >= ' . $this->datetimeToDB($dateStart) : '') .
346  ($dateEnd !== null ? ' AND a.date_submitted <= ' . $this->datetimeToDB($dateEnd) : '') .
347  $sectionSql;
348  $result =& $this->retrieve($sql, $params);
349 
350  $returner = array(
351  'reviewsCount' => 0,
352  'reviewerScore' => 0,
353  'daysPerReview' => 0,
354  'reviewerAddedCount' => 0,
355  'reviewerCount' => 0,
356  'reviewedSubmissionsCount' => 0
357  );
358 
359  $scoredReviewsCount = 0;
360  $totalScore = 0;
361  $completedReviewsCount = 0;
362  $totalElapsedTime = 0;
363  $reviewerList = array();
364  $articleIds = array();
365 
366  while (!$result->EOF) {
367  $row = $result->GetRowAssoc(false);
368  $returner['reviewsCount']++;
369  if (!empty($row['quality'])) {
370  $scoredReviewsCount++;
371  $totalScore += $row['quality'];
372  }
373 
374  $articleIds[] = $row['article_id'];
375 
376  if (!empty($row['reviewer_id']) && !in_array($row['reviewer_id'], $reviewerList)) {
377  $returner['reviewerCount']++;
378  $dateRegistered = strtotime($this->datetimeFromDB($row['date_registered']));
379  if (($dateRegistered >= $dateStart || $dateStart === null) && ($dateRegistered <= $dateEnd || $dateEnd == null)) {
380  $returner['reviewerAddedCount']++;
381  }
382  array_push($reviewerList, $row['reviewer_id']);
383  }
384 
385  if (!empty($row['date_assigned']) && !empty($row['date_completed'])) {
386  $timeReviewVersionUploaded = strtotime($this->datetimeFromDB($row['date_rv_uploaded']));
387  $timeCompleted = strtotime($this->datetimeFromDB($row['date_completed']));
388  if ($timeCompleted > $timeReviewVersionUploaded) {
389  $completedReviewsCount++;
390  $totalElapsedTime += ($timeCompleted - $timeReviewVersionUploaded);
391  }
392  }
393  $result->moveNext();
394  }
395 
396  $result->Close();
397  unset($result);
398 
399  if ($scoredReviewsCount > 0) {
400  // To one decimal place
401  $returner['reviewerScore'] = round($totalScore * 10 / $scoredReviewsCount) / 10;
402  }
403  if ($completedReviewsCount > 0) {
404  $seconds = $totalElapsedTime / $completedReviewsCount;
405  $returner['daysPerReview'] = $seconds / 60 / 60 / 24;
406  }
407 
408  $articleIds = array_unique($articleIds);
409  $returner['reviewedSubmissionsCount'] = count($articleIds);
410 
411  return $returner;
412  }
413 }
414 
415 ?>
getUserStatistics($journalId, $dateStart=null, $dateEnd=null)
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
getReviewerStatistics($journalId, $sectionIds, $dateStart=null, $dateEnd=null)
getIssueStatistics($journalId, $dateStart=null, $dateEnd=null)
datetimeFromDB($dt)
Definition: DAO.inc.php:316
Operations for retrieving journal statistics.
getSubscriptionStatistics($journalId, $dateStart=null, $dateEnd=null)
& getDAO($name, $dbconn=null)
datetimeToDB($dt)
Definition: DAO.inc.php:296
getArticleStatistics($journalId, $sectionIds=null, $dateStart=null, $dateEnd=null)