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