PKPStatsEditorialQueryBuilder.inc.php

<?php
 
namespace PKP\Services\QueryBuilders;
 
use Illuminate\Database\Capsule\Manager as Capsule;
 
abstract class PKPStatsEditorialQueryBuilder {
 
   protected $contextIds = [];
 
   protected $dateEnd;
 
   protected $dateStart;
 
   protected $sectionIds = [];
 
   public $sectionIdsColumn;
 
   public function filterByContexts($contextIds) {
      $this->contextIds = is_array($contextIds) ? $contextIds : [$contextIds];
      return $this;
   }
 
   public function filterBySections($sectionIds) {
      $this->sectionIds = is_array($sectionIds) ? $sectionIds : [$sectionIds];
      return $this;
   }
 
   public function before($dateEnd) {
      $this->dateEnd = $dateEnd;
      return $this;
   }
 
   public function after($dateStart) {
      $this->dateStart = $dateStart;
      return $this;
   }
 
   public function countSubmissionsReceived() {
      $q = $this->_getObject();
      if ($this->dateStart) {
         $q->where('s.date_submitted', '>=', $this->dateStart);
      }
      if ($this->dateEnd) {
         $q->where('s.date_submitted', '<=', $this->dateEnd);
      }
 
      return $q->count();
   }
 
   public function countByDecisions($decisions, $forSubmittedDate = false) {
 
      $q = $this->_getObject();
      $q->leftJoin('edit_decisions as ed', 's.submission_id', '=', 'ed.submission_id')
         ->whereIn('ed.decision', $decisions);
 
      if ($forSubmittedDate) {
         if ($this->dateStart) {
            $q->where('s.date_submitted', '>=', $this->dateStart);
         }
         if ($this->dateEnd) {
            // Include date time values up to the end of the day
            $dateTime = new \DateTime($this->dateEnd);
            $dateTime->add(new \DateInterval('P1D'));
            $q->where('s.date_submitted', '<', $dateTime->format('Y-m-d'));
         }
 
      } else {
         if ($this->dateStart) {
            $q->where('ed.date_decided', '>=', $this->dateStart);
         }
         if ($this->dateEnd) {
            // Include date time values up to the end of the day
            $dateTime = new \DateTime($this->dateEnd);
            $dateTime->add(new \DateInterval('P1D'));
            $q->where('ed.date_decided', '<', $dateTime->format('Y-m-d'));
         }
      }
 
      // Ensure that the decisions being counted have not been
      // reversed. For example, a submission may have been accepted
      // and then later declined. We check the current status to
      // exclude submissions where the status doesn't match the
      // decisions we are looking for.
      $declineDecisions = [SUBMISSION_EDITOR_DECISION_DECLINE, SUBMISSION_EDITOR_DECISION_INITIAL_DECLINE];
      if (count(array_intersect($declineDecisions, $decisions))) {
         $q->where('s.status', '=', STATUS_DECLINED);
      } else {
         $q->where('s.status', '!=', STATUS_DECLINED);
      }
 
      $q->select(Capsule::raw('COUNT(DISTINCT s.submission_id) as count'));
 
      return $q->get()->first()->count;
   }
 
   public function countByStatus($status) {
      return $this->_getObject()
         ->whereIn('s.status', (array) $status)
         ->count();
   }
 
   public function countActiveByStages($stages) {
      import('lib.pkp.classes.submission.PKPSubmission');
 
      return $this->_getObject()
         ->where('s.status', '=', STATUS_QUEUED)
         ->whereIn('s.stage_id', $stages)
         ->count();
   }
 
   public function countPublished() {
      $q = $this->_getObject()
         ->where('s.status', '=', STATUS_PUBLISHED);
 
      // Only match against the publication date of a
      // submission's first published publication so
      // that updated versions are excluded.
      if ($this->dateStart || $this->dateEnd) {
         $q->leftJoin('publications as p', function($q) {
            $q->where('p.publication_id', function($q) {
               $q->from('publications as p2')
                  ->where('p2.submission_id', '=', Capsule::raw('s.submission_id'))
                  ->where('p2.status', '=', STATUS_PUBLISHED)
                  ->orderBy('p2.date_published', 'ASC')
                  ->limit(1)
                  ->select('p2.publication_id');
            });
         });
         if ($this->dateStart) {
            $q->where('p.date_published', '>=', $this->dateStart);
         }
         if ($this->dateEnd) {
            $q->where('p.date_published', '<=', $this->dateEnd);
         }
      }
 
      return $q->count();
   }
 
   public function getDaysToDecisions($decisions) {
      $q = $this->_getDaysToDecisionsObject($decisions);
      $dateDiff = $this->_dateDiff('ed.date_decided', 's.date_submitted');
      $q->select(Capsule::raw($dateDiff . ' as time'));
      return $q->pluck('time')->toArray();
   }
 
   public function getAverageDaysToDecisions($decisions) {
      $q = $this->_getDaysToDecisionsObject($decisions);
      $dateDiff = $this->_dateDiff('ed.date_decided', 's.date_submitted');
      $q->select(Capsule::raw('AVG(' . $dateDiff . ') as average'));
      return $q->get()->first()->average;
   }
 
   public function getSubmissionsReceivedDates() {
      $q = $this->_getObject();
      return [$q->min('s.date_submitted'), $q->max('s.date_submitted')];
   }
 
   public function getPublishedDates() {
      import('lib.pkp.classes.submission.PKPSubmission');
 
      $q = $this->_getObject()
         ->where('s.status', '=', STATUS_PUBLISHED)
         // Only match against the publication date of a
         // submission's first published publication so
         // that updated versions are excluded.
         ->leftJoin('publications as p', function($q) {
            $q->where('p.publication_id', function($q) {
               $q->from('publications as p2')
                  ->where('p2.submission_id', '=', Capsule::raw('s.submission_id'))
                  ->where('p2.status', '=', STATUS_PUBLISHED)
                  ->orderBy('p2.date_published', 'ASC')
                  ->limit(1)
                  ->select('p2.publication_id');
            });
         });
 
      return [$q->min('p.date_published'), $q->max('p.date_published')];
   }
 
   public function getDecisionsDates($decisions) {
      import('lib.pkp.classes.submission.PKPSubmission');
      $q = $this->_getObject();
      $q->leftJoin('edit_decisions as ed', 's.submission_id', '=', 'ed.submission_id')
         ->whereIn('ed.decision', $decisions);
 
      // Ensure that the decisions being counted have not been
      // reversed. For example, a submission may have been accepted
      // and then later declined. We check the current status to
      // exclude submissions where the status doesn't match the
      // decisions we are looking for.
      $declineDecisions = [SUBMISSION_EDITOR_DECISION_DECLINE, SUBMISSION_EDITOR_DECISION_INITIAL_DECLINE];
      if (count(array_intersect($declineDecisions, $decisions))) {
         $q->where('s.status', '=', STATUS_DECLINED);
      } else {
         $q->where('s.status', '!=', STATUS_DECLINED);
      }
 
      return [$q->min('ed.date_decided'), $q->max('ed.date_decided')];
   }
 
   protected function _getObject() {
      $q = Capsule::table('submissions as s');
      if (!empty($this->contextIds)) {
         $q->whereIn('s.context_id', $this->contextIds);
      }
      if (!empty($this->sectionIds)) {
         $q->leftJoin('publications as ps', 's.current_publication_id', '=', 'ps.publication_id')
            ->whereIn('ps.' . $this->sectionIdsColumn, $this->sectionIds)
            ->whereNotNull('ps.publication_id');
      }
 
      // Exclude incomplete submissions
      $q->where('s.submission_progress', '=', 0);
 
      // Exclude submissions when the date_submitted is later
      // than the first date_published. This prevents imported
      // submissions from being counted in editorial stats.
      $q->leftJoin('publications as pi', function($q) {
               $q->where('pi.publication_id', function($q) {
                  $q->from('publications as pi2')
                     ->where('pi2.submission_id', '=', Capsule::raw('s.submission_id'))
                     ->where('pi2.status', '=', STATUS_PUBLISHED)
                     ->orderBy('pi2.date_published', 'ASC')
                     ->limit(1)
                     ->select('pi2.publication_id');
               });
            })
            ->where(function($q) {
               $q->whereNull('pi.date_published')
                  ->orWhere('s.date_submitted', '<', Capsule::raw('pi.date_published'));
            });
 
      \HookRegistry::call('Stats::editorial::queryObject', array($q, $this));
 
      return $q;
   }
 
   protected function _getDaysToDecisionsObject($decisions) {
 
      $q = $this->_getObject();
 
      $q->leftJoin('edit_decisions as ed', function($q) use ($decisions) {
         $q->where('ed.edit_decision_id', function($q) use ($decisions) {
            $q->from('edit_decisions as ed2')
               ->where('ed2.submission_id', '=', Capsule::raw('s.submission_id'));
            if (!empty($decisions)) {
               $q->whereIn('ed2.decision', $decisions);
            }
            $q->orderBy('ed2.date_decided', 'ASC')
               ->limit(1)
               ->select('ed2.edit_decision_id');
         });
      });
 
      $q->whereNotNull('ed.submission_id')
         ->whereNotNull('s.date_submitted');
 
      if ($this->dateStart) {
         $q->where('s.date_submitted', '>=', $this->dateStart);
      }
      if ($this->dateEnd) {
         // Include date time values up to the end of the day
         $dateTime = new \DateTime($this->dateEnd);
         $dateTime->add(new \DateInterval('P1D'));
         $q->where('s.date_submitted', '<=', $dateTime->format('Y-m-d'));
      }
 
      return $q;
   }
 
   private function _dateDiff(string $leftDate, string $rightDate)   {
      switch (\Config::getVar('database', 'driver')) {
         case 'mysql':
         case 'mysqli':
            return 'DATEDIFF(' . $leftDate . ',' . $rightDate . ')';
      }
      return "DATE_PART('day', " . $leftDate . " - " . $rightDate . ")";
   }
}