Open Journal Systems  2.4.4
 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)
36  WHERE a2.article_id IS NULL AND
37  a.date_submitted IS NOT NULL AND
38  a.journal_id = ?',
39  (int) $journalId
40  );
41 
42  $row = $result->GetRowAssoc(false);
43  $firstActivityDate = $this->datetimeFromDB($row['first_date']);
44  $result->Close();
45  if (!$firstActivityDate) return null;
46  return strtotime($firstActivityDate);
47 
48  }
49 
59  function getArticleStatistics($journalId, $sectionIds = null, $dateStart = null, $dateEnd = null) {
60  // Bring in status constants
61  import('classes.article.Article');
62 
63  $params = array($journalId);
64  if (!empty($sectionIds)) {
65  $sectionSql = ' AND (a.section_id = ?';
66  $params[] = array_shift($sectionIds);
67  foreach ($sectionIds as $sectionId) {
68  $sectionSql .= ' OR a.section_id = ?';
69  $params[] = $sectionId;
70  }
71  $sectionSql .= ')';
72  } else $sectionSql = '';
73 
74  $sql = 'SELECT a.article_id,
75  a.date_submitted,
76  pa.date_published,
77  pa.published_article_id,
78  d.decision,
79  a.status
80  FROM articles a
81  LEFT JOIN published_articles pa ON (a.article_id = pa.article_id)
82  LEFT JOIN edit_decisions d ON (d.article_id = a.article_id)
83  WHERE a.journal_id = ?' .
84  ($dateStart !== null ? ' AND a.date_submitted >= ' . $this->datetimeToDB($dateStart) : '') .
85  ($dateEnd !== null ? ' AND a.date_submitted <= ' . $this->datetimeToDB($dateEnd) : '') .
86  $sectionSql .
87  ' ORDER BY a.article_id, d.date_decided DESC';
88 
89  $result =& $this->retrieve($sql, $params);
90 
91  $returner = array(
92  'numSubmissions' => 0,
93  'numReviewedSubmissions' => 0,
94  'numPublishedSubmissions' => 0,
95  'submissionsAccept' => 0,
96  'submissionsDecline' => 0,
97  'submissionsAcceptPercent' => 0,
98  'submissionsDeclinePercent' => 0,
99  'daysToPublication' => 0
100  );
101 
102  // Track which articles we're including
103  $articleIds = array();
104 
105  $totalTimeToPublication = 0;
106  $timeToPublicationCount = 0;
107 
108  while (!$result->EOF) {
109  $row = $result->GetRowAssoc(false);
110 
111  // For each article, pick the most recent editor
112  // decision only and ignore the rest. Depends on sort
113  // order. FIXME -- there must be a better way of doing
114  // this that's database independent.
115  if (!in_array($row['article_id'], $articleIds)) {
116  $articleIds[] = $row['article_id'];
117  $returner['numSubmissions']++;
118 
119  if (!empty($row['published_article_id']) && $row['status'] == STATUS_PUBLISHED) {
120  $returner['numPublishedSubmissions']++;
121  }
122 
123  if (!empty($row['date_submitted']) && !empty($row['date_published']) && $row['status'] == STATUS_PUBLISHED) {
124  $timeSubmitted = strtotime($this->datetimeFromDB($row['date_submitted']));
125  $timePublished = strtotime($this->datetimeFromDB($row['date_published']));
126  if ($timePublished > $timeSubmitted) {
127  $totalTimeToPublication += ($timePublished - $timeSubmitted);
128  $timeToPublicationCount++;
129  }
130  }
131 
132  import('classes.submission.common.Action');
133  switch ($row['decision']) {
134  case SUBMISSION_EDITOR_DECISION_ACCEPT:
135  $returner['submissionsAccept']++;
136  $returner['numReviewedSubmissions']++;
137  break;
138  case SUBMISSION_EDITOR_DECISION_DECLINE:
139  $returner['submissionsDecline']++;
140  $returner['numReviewedSubmissions']++;
141  break;
142  }
143  }
144 
145  $result->moveNext();
146  }
147 
148  $result->Close();
149  unset($result);
150 
151  // Calculate percentages where necessary
152  if ($returner['numReviewedSubmissions'] != 0) {
153  $returner['submissionsAcceptPercent'] = round($returner['submissionsAccept'] * 100 / $returner['numReviewedSubmissions']);
154  $returner['submissionsDeclinePercent'] = round($returner['submissionsDecline'] * 100 / $returner['numReviewedSubmissions']);
155  }
156 
157  if ($timeToPublicationCount != 0) {
158  // Keep one sig fig
159  $returner['daysToPublication'] = round($totalTimeToPublication / $timeToPublicationCount / 60 / 60 / 24);
160  }
161 
162  return $returner;
163  }
164 
173  function getUserStatistics($journalId, $dateStart = null, $dateEnd = null) {
174  $roleDao =& DAORegistry::getDAO('RoleDAO');
175 
176  // Get count of total users for this journal
177  $result =& $this->retrieve(
178  'SELECT COUNT(DISTINCT r.user_id) FROM roles r, users u WHERE r.user_id = u.user_id AND r.journal_id = ?' .
179  ($dateStart !== null ? ' AND u.date_registered >= ' . $this->datetimeToDB($dateStart) : '') .
180  ($dateEnd !== null ? ' AND u.date_registered <= ' . $this->datetimeToDB($dateEnd) : ''),
181  $journalId
182  );
183 
184  $returner = array(
185  'totalUsersCount' => $result->fields[0]
186  );
187 
188  $result->Close();
189  unset($result);
190 
191  // Get user counts for each role.
192  $result =& $this->retrieve(
193  '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 = ?' .
194  ($dateStart !== null ? ' AND u.date_registered >= ' . $this->datetimeToDB($dateStart) : '') .
195  ($dateEnd !== null ? ' AND u.date_registered <= ' . $this->datetimeToDB($dateEnd) : '') .
196  ' GROUP BY r.role_id',
197  $journalId
198  );
199 
200  while (!$result->EOF) {
201  $row = $result->GetRowAssoc(false);
202  $returner[$roleDao->getRolePath($row['role_id'])] = $row['role_count'];
203  $result->moveNext();
204  }
205 
206  $result->Close();
207  unset($result);
208 
209  return $returner;
210  }
211 
219  function getSubscriptionStatistics($journalId, $dateStart = null, $dateEnd = null) {
220  $result =& $this->retrieve(
221  'SELECT st.type_id,
222  sts.setting_value AS type_name,
223  count(s.subscription_id) AS type_count
224  FROM subscription_types st
225  LEFT JOIN journals j ON (j.journal_id = st.journal_id)
226  LEFT JOIN subscription_type_settings sts ON (st.type_id = sts.type_id AND sts.setting_name = ? AND sts.locale = j.primary_locale),
227  subscriptions s
228  WHERE st.journal_id = ?
229  AND s.type_id = st.type_id' .
230  ($dateStart !== null ? ' AND s.date_start >= ' . $this->datetimeToDB($dateStart) : '') .
231  ($dateEnd !== null ? ' AND s.date_start <= ' . $this->datetimeToDB($dateEnd) : '') .
232  ' GROUP BY st.type_id, sts.setting_value',
233  array('name', $journalId)
234  );
235 
236  $returner = array();
237 
238  while (!$result->EOF) {
239  $row = $result->getRowAssoc(false);
240  $returner[$row['type_id']] = array(
241  'name' => $row['type_name'],
242  'count' => $row['type_count']
243  );
244  $result->moveNext();
245  }
246  $result->Close();
247  unset($result);
248 
249  return $returner;
250  }
251 
260  function getIssueStatistics($journalId, $dateStart = null, $dateEnd = null) {
261  $result =& $this->retrieve(
262  'SELECT COUNT(*) AS count, published FROM issues WHERE journal_id = ?' .
263  ($dateStart !== null ? ' AND date_published >= ' . $this->datetimeToDB($dateStart) : '') .
264  ($dateEnd !== null ? ' AND date_published <= ' . $this->datetimeToDB($dateEnd) : '') .
265  ' GROUP BY published',
266  $journalId
267  );
268 
269  $returner = array(
270  'numPublishedIssues' => 0,
271  'numUnpublishedIssues' => 0
272  );
273 
274  while (!$result->EOF) {
275  $row = $result->GetRowAssoc(false);
276 
277  if ($row['published']) {
278  $returner['numPublishedIssues'] = $row['count'];
279  } else {
280  $returner['numUnpublishedIssues'] = $row['count'];
281  }
282  $result->moveNext();
283  }
284 
285  $result->Close();
286  unset($result);
287 
288  $returner['numIssues'] = $returner['numPublishedIssues'] + $returner['numUnpublishedIssues'];
289 
290  return $returner;
291  }
292 
301  function getReviewerStatistics($journalId, $sectionIds, $dateStart = null, $dateEnd = null) {
302  $params = array($journalId);
303  if (!empty($sectionIds)) {
304  $sectionSql = ' AND (a.section_id = ?';
305  $params[] = array_shift($sectionIds);
306  foreach ($sectionIds as $sectionId) {
307  $sectionSql .= ' OR a.section_id = ?';
308  $params[] = $sectionId;
309  }
310  $sectionSql .= ')';
311  } else $sectionSql = '';
312 
313  $sql = 'SELECT a.article_id,
314  af.date_uploaded AS date_rv_uploaded,
315  r.review_id,
316  u.date_registered,
317  r.reviewer_id,
318  r.quality,
319  r.date_assigned,
320  r.date_completed
321  FROM articles a,
322  article_files af,
323  review_assignments r
324  LEFT JOIN users u ON (u.user_id = r.reviewer_id)
325  WHERE a.journal_id = ?
326  AND r.submission_id = a.article_id
327  AND af.article_id = a.article_id
328  AND af.file_id = a.review_file_id
329  AND af.revision = 1' .
330  ($dateStart !== null ? ' AND a.date_submitted >= ' . $this->datetimeToDB($dateStart) : '') .
331  ($dateEnd !== null ? ' AND a.date_submitted <= ' . $this->datetimeToDB($dateEnd) : '') .
332  $sectionSql;
333  $result =& $this->retrieve($sql, $params);
334 
335  $returner = array(
336  'reviewsCount' => 0,
337  'reviewerScore' => 0,
338  'daysPerReview' => 0,
339  'reviewerAddedCount' => 0,
340  'reviewerCount' => 0,
341  'reviewedSubmissionsCount' => 0
342  );
343 
344  $scoredReviewsCount = 0;
345  $totalScore = 0;
346  $completedReviewsCount = 0;
347  $totalElapsedTime = 0;
348  $reviewerList = array();
349  $articleIds = array();
350 
351  while (!$result->EOF) {
352  $row = $result->GetRowAssoc(false);
353  $returner['reviewsCount']++;
354  if (!empty($row['quality'])) {
355  $scoredReviewsCount++;
356  $totalScore += $row['quality'];
357  }
358 
359  $articleIds[] = $row['article_id'];
360 
361  if (!empty($row['reviewer_id']) && !in_array($row['reviewer_id'], $reviewerList)) {
362  $returner['reviewerCount']++;
363  $dateRegistered = strtotime($this->datetimeFromDB($row['date_registered']));
364  if (($dateRegistered >= $dateStart || $dateStart === null) && ($dateRegistered <= $dateEnd || $dateEnd == null)) {
365  $returner['reviewerAddedCount']++;
366  }
367  array_push($reviewerList, $row['reviewer_id']);
368  }
369 
370  if (!empty($row['date_assigned']) && !empty($row['date_completed'])) {
371  $timeReviewVersionUploaded = strtotime($this->datetimeFromDB($row['date_rv_uploaded']));
372  $timeCompleted = strtotime($this->datetimeFromDB($row['date_completed']));
373  if ($timeCompleted > $timeReviewVersionUploaded) {
374  $completedReviewsCount++;
375  $totalElapsedTime += ($timeCompleted - $timeReviewVersionUploaded);
376  }
377  }
378  $result->moveNext();
379  }
380 
381  $result->Close();
382  unset($result);
383 
384  if ($scoredReviewsCount > 0) {
385  // To one decimal place
386  $returner['reviewerScore'] = round($totalScore * 10 / $scoredReviewsCount) / 10;
387  }
388  if ($completedReviewsCount > 0) {
389  $seconds = $totalElapsedTime / $completedReviewsCount;
390  $returner['daysPerReview'] = $seconds / 60 / 60 / 24;
391  }
392 
393  $articleIds = array_unique($articleIds);
394  $returner['reviewedSubmissionsCount'] = count($articleIds);
395 
396  return $returner;
397  }
398 }
399 
400 ?>
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)