classes/schedConf/SchedConfStatisticsDAO.inc.php

Go to the documentation of this file.
00001 <?php
00002 
00015 //$Id$
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       // Bring in status constants
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       // Track which papers we're including
00079       $paperIds = array();
00080 
00081       $totalTimeToPublication = 0;
00082       $timeToPublicationCount = 0;
00083 
00084       while (!$result->EOF) {
00085          $row = $result->GetRowAssoc(false);
00086 
00087          // For each paper, pick the most recent director
00088          // decision only and ignore the rest. Depends on sort
00089          // order. FIXME -- there must be a better way of doing
00090          // this that's database independent.
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       // Calculate percentages where necessary
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          // Keep one sig fig
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       // Get count of total users for this scheduled conference
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       // Get user counts for each role.
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          // To one decimal place
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 ?>

Generated on 25 Jul 2013 for Open Conference Systems by  doxygen 1.4.7