00001 <?php
00002
00015
00016
00017 define('REPORT_TYPE_CONFERENCE', 0x00001);
00018 define('REPORT_TYPE_SCHED_CONF', 0x00002);
00019 define('REPORT_TYPE_DIRECTOR', 0x00003);
00020 define('REPORT_TYPE_REVIEWER', 0x00004);
00021 define('REPORT_TYPE_TRACK', 0x00005);
00022
00023 class SchedConfStatisticsDAO extends DAO {
00033 function getPaperStatistics($schedConfId, $trackIds = null, $dateStart = null, $dateEnd = null) {
00034
00035 import('paper.Paper');
00036
00037 $params = array($schedConfId);
00038 if (!empty($trackIds)) {
00039 $trackSql = ' AND (a.track_id = ?';
00040 $params[] = array_shift($trackIds);
00041 foreach ($trackIds as $trackId) {
00042 $trackSql .= ' OR a.track_id = ?';
00043 $params[] = $trackId;
00044 }
00045 $trackSql .= ')';
00046 } else $trackSql = '';
00047
00048 $sql = 'SELECT a.paper_id,
00049 a.date_submitted,
00050 pa.date_published,
00051 pa.pub_id,
00052 d.decision,
00053 a.status
00054 FROM papers a
00055 LEFT JOIN published_papers pa ON (a.paper_id = pa.paper_id)
00056 LEFT JOIN edit_decisions d ON (d.paper_id = a.paper_id)
00057 WHERE a.sched_conf_id = ?' .
00058 ($dateStart !== null ? ' AND a.date_submitted >= ' . $this->datetimeToDB($dateStart) : '') .
00059 ($dateEnd !== null ? ' AND a.date_submitted <= ' . $this->datetimeToDB($dateEnd) : '') .
00060 $trackSql .
00061 ' ORDER BY a.paper_id, d.date_decided DESC';
00062
00063 $result =& $this->retrieve($sql, $params);
00064
00065 $returner = array(
00066 'numSubmissions' => 0,
00067 'numReviewedSubmissions' => 0,
00068 'numPublishedSubmissions' => 0,
00069 'submissionsAccept' => 0,
00070 'submissionsDecline' => 0,
00071 'submissionsRevise' => 0,
00072 'submissionsAcceptPercent' => 0,
00073 'submissionsDeclinePercent' => 0,
00074 'submissionsRevisePercent' => 0,
00075 'daysToPublication' => 0
00076 );
00077
00078
00079 $paperIds = array();
00080
00081 $totalTimeToPublication = 0;
00082 $timeToPublicationCount = 0;
00083
00084 while (!$result->EOF) {
00085 $row = $result->GetRowAssoc(false);
00086
00087
00088
00089
00090
00091 if (!in_array($row['paper_id'], $paperIds)) {
00092 $paperIds[] = $row['paper_id'];
00093 $returner['numSubmissions']++;
00094
00095 if (!empty($row['pub_id']) && $row['status'] == STATUS_PUBLISHED) {
00096 $returner['numPublishedSubmissions']++;
00097 }
00098
00099 if (!empty($row['date_submitted']) && !empty($row['date_published']) && $row['status'] == STATUS_PUBLISHED) {
00100 $timeSubmitted = strtotime($this->datetimeFromDB($row['date_submitted']));
00101 $timePublished = strtotime($this->datetimeFromDB($row['date_published']));
00102 if ($timePublished > $timeSubmitted) {
00103 $totalTimeToPublication += ($timePublished - $timeSubmitted);
00104 $timeToPublicationCount++;
00105 }
00106 }
00107
00108 import('submission.common.Action');
00109 switch ($row['decision']) {
00110 case SUBMISSION_DIRECTOR_DECISION_ACCEPT:
00111 $returner['submissionsAccept']++;
00112 $returner['numReviewedSubmissions']++;
00113 break;
00114 case SUBMISSION_DIRECTOR_DECISION_PENDING_REVISIONS:
00115 $returner['submissionsRevise']++;
00116 break;
00117 case SUBMISSION_DIRECTOR_DECISION_DECLINE:
00118 $returner['submissionsDecline']++;
00119 $returner['numReviewedSubmissions']++;
00120 break;
00121 }
00122 }
00123
00124 $result->moveNext();
00125 }
00126
00127 $result->Close();
00128 unset($result);
00129
00130
00131 if ($returner['numReviewedSubmissions'] != 0) {
00132 $returner['submissionsAcceptPercent'] = round($returner['submissionsAccept'] * 100 / $returner['numReviewedSubmissions']);
00133 $returner['submissionsDeclinePercent'] = round($returner['submissionsDecline'] * 100 / $returner['numReviewedSubmissions']);
00134 $returner['submissionsRevisePercent'] = round($returner['submissionsRevise'] * 100 / $returner['numReviewedSubmissions']);
00135 }
00136
00137 if ($timeToPublicationCount != 0) {
00138
00139 $returner['daysToPublication'] = round($totalTimeToPublication / $timeToPublicationCount / 60 / 60 / 24);
00140 }
00141
00142 return $returner;
00143 }
00144
00153 function getUserStatistics($schedConfId, $dateStart = null, $dateEnd = null) {
00154 $roleDao =& DAORegistry::getDAO('RoleDAO');
00155
00156
00157 $result =& $this->retrieve(
00158 'SELECT COUNT(DISTINCT r.user_id) FROM roles r, users u WHERE r.user_id = u.user_id AND r.sched_conf_id = ?' .
00159 ($dateStart !== null ? ' AND u.date_registered >= ' . $this->datetimeToDB($dateStart) : '') .
00160 ($dateEnd !== null ? ' AND u.date_registered <= ' . $this->datetimeToDB($dateEnd) : ''),
00161 $schedConfId
00162 );
00163
00164 $returner = array(
00165 'totalUsersCount' => $result->fields[0]
00166 );
00167
00168 $result->Close();
00169 unset($result);
00170
00171
00172 $result =& $this->retrieve(
00173 '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.sched_conf_id = ?' .
00174 ($dateStart !== null ? ' AND u.date_registered >= ' . $this->datetimeToDB($dateStart) : '') .
00175 ($dateEnd !== null ? ' AND u.date_registered <= ' . $this->datetimeToDB($dateEnd) : '') .
00176 'GROUP BY r.role_id',
00177 $schedConfId
00178 );
00179
00180 while (!$result->EOF) {
00181 $row = $result->GetRowAssoc(false);
00182 $returner[$roleDao->getRolePath($row['role_id'])] = $row['role_count'];
00183 $result->moveNext();
00184 }
00185
00186 $result->Close();
00187 unset($result);
00188
00189 return $returner;
00190 }
00191
00199 function getRegistrationStatistics($schedConfId, $dateStart = null, $dateEnd = null) {
00200 $result =& $this->retrieve(
00201 'SELECT st.type_id,
00202 rts.setting_value AS type_name,
00203 COUNT(s.registration_id) AS type_count
00204 FROM registration_types st
00205 LEFT JOIN sched_confs sc ON (st.sched_conf_id = sc.sched_conf_id)
00206 LEFT JOIN conferences c ON (sc.conference_id = c.conference_id)
00207 LEFT JOIN registration_type_settings rts ON (rts.type_id = st.type_id AND rts.setting_name = ? AND rts.locale = c.primary_locale),
00208 registrations s
00209 WHERE st.sched_conf_id = ? AND
00210 s.type_id = st.type_id' .
00211 ($dateStart !== null ? ' AND st.opening_date >= ' . $this->datetimeToDB($dateStart) : '') .
00212 ($dateEnd !== null ? ' AND st.closing_date <= ' . $this->datetimeToDB($dateEnd) : '') .
00213 ' GROUP BY st.type_id, rts.setting_value',
00214 array('name', $schedConfId)
00215 );
00216
00217 $returner = array();
00218
00219 while (!$result->EOF) {
00220 $row = $result->getRowAssoc(false);
00221 $returner[$row['type_id']] = array(
00222 'name' => $row['type_name'],
00223 'count' => $row['type_count']
00224 );
00225 $result->moveNext();
00226 }
00227 $result->Close();
00228 unset($result);
00229
00230 return $returner;
00231 }
00232
00241 function getIssueStatistics($schedConfId, $dateStart = null, $dateEnd = null) {
00242 $result =& $this->retrieve(
00243 'SELECT COUNT(*) AS count, published FROM issues WHERE sched_conf_id = ?' .
00244 ($dateStart !== null ? ' AND date_published >= ' . $this->datetimeToDB($dateStart) : '') .
00245 ($dateEnd !== null ? ' AND date_published <= ' . $this->datetimeToDB($dateEnd) : '') .
00246 ' GROUP BY published',
00247 $schedConfId
00248 );
00249
00250 $returner = array(
00251 'numPublishedIssues' => 0,
00252 'numUnpublishedIssues' => 0
00253 );
00254
00255 while (!$result->EOF) {
00256 $row = $result->GetRowAssoc(false);
00257
00258 if ($row['published']) {
00259 $returner['numPublishedIssues'] = $row['count'];
00260 } else {
00261 $returner['numUnpublishedIssues'] = $row['count'];
00262 }
00263 $result->moveNext();
00264 }
00265
00266 $result->Close();
00267 unset($result);
00268
00269 $returner['numIssues'] = $returner['numPublishedIssues'] + $returner['numUnpublishedIssues'];
00270
00271 return $returner;
00272 }
00273
00282 function getReviewerStatistics($schedConfId, $trackIds, $dateStart = null, $dateEnd = null) {
00283 $params = array($schedConfId);
00284 if (!empty($trackIds)) {
00285 $trackSql = ' AND (a.track_id = ?';
00286 $params[] = array_shift($trackIds);
00287 foreach ($trackIds as $trackId) {
00288 $trackSql .= ' OR a.track_id = ?';
00289 $params[] = $trackId;
00290 }
00291 $trackSql .= ')';
00292 } else $trackSql = '';
00293
00294 $sql = 'SELECT a.paper_id,
00295 af.date_uploaded AS date_rv_uploaded,
00296 r.review_id,
00297 u.date_registered,
00298 r.reviewer_id,
00299 r.quality AS quality,
00300 r.date_assigned,
00301 r.date_completed
00302 FROM papers a,
00303 paper_files af,
00304 review_assignments r
00305 LEFT JOIN users u ON (u.user_id = r.reviewer_id)
00306 WHERE a.sched_conf_id = ? AND
00307 r.paper_id = a.paper_id AND
00308 af.paper_id = a.paper_id AND
00309 af.file_id = a.review_file_id AND
00310 af.revision = 1' .
00311 ($dateStart !== null ? ' AND a.date_submitted >= ' . $this->datetimeToDB($dateStart) : '') .
00312 ($dateEnd !== null ? ' AND a.date_submitted <= ' . $this->datetimeToDB($dateEnd) : '') .
00313 $trackSql;
00314 $result =& $this->retrieve($sql, $params);
00315
00316 $returner = array(
00317 'reviewsCount' => 0,
00318 'reviewerScore' => 0,
00319 'daysPerReview' => 0,
00320 'reviewerAddedCount' => 0,
00321 'reviewerCount' => 0,
00322 'reviewedSubmissionsCount' => 0
00323 );
00324
00325 $scoredReviewsCount = 0;
00326 $totalScore = 0;
00327 $completedReviewsCount = 0;
00328 $totalElapsedTime = 0;
00329 $reviewerList = array();
00330 $paperIds = array();
00331
00332 while (!$result->EOF) {
00333 $row = $result->GetRowAssoc(false);
00334 $returner['reviewsCount']++;
00335 if (!empty($row['quality'])) {
00336 $scoredReviewsCount++;
00337 $totalScore += $row['quality'];
00338 }
00339
00340 $paperIds[] = $row['paper_id'];
00341
00342 if (!empty($row['reviewer_id']) && !in_array($row['reviewer_id'], $reviewerList)) {
00343 $returner['reviewerCount']++;
00344 $dateRegistered = strtotime($this->datetimeFromDB($row['date_registered']));
00345 if (($dateRegistered >= $dateStart || $dateStart === null) && ($dateRegistered <= $dateEnd || $dateEnd == null)) {
00346 $returner['reviewerAddedCount']++;
00347 }
00348 array_push($reviewerList, $row['reviewer_id']);
00349 }
00350
00351 if (!empty($row['date_assigned']) && !empty($row['date_completed'])) {
00352 $timeReviewVersionUploaded = strtotime($this->datetimeFromDB($row['date_rv_uploaded']));
00353 $timeCompleted = strtotime($this->datetimeFromDB($row['date_completed']));
00354 if ($timeCompleted > $timeReviewVersionUploaded) {
00355 $completedReviewsCount++;
00356 $totalElapsedTime += ($timeCompleted - $timeReviewVersionUploaded);
00357 }
00358 }
00359 $result->moveNext();
00360 }
00361
00362 $result->Close();
00363 unset($result);
00364
00365 if ($scoredReviewsCount > 0) {
00366
00367 $returner['reviewerScore'] = round($totalScore * 10 / $scoredReviewsCount) / 10;
00368 }
00369 if ($completedReviewsCount > 0) {
00370 $seconds = $totalElapsedTime / $completedReviewsCount;
00371 $returner['daysPerReview'] = $seconds / 60 / 60 / 24;
00372 }
00373
00374 $paperIds = array_unique($paperIds);
00375 $returner['reviewedSubmissionsCount'] = count($paperIds);
00376
00377 return $returner;
00378 }
00379 }
00380
00381 ?>