PKPSubmissionQueryBuilder.inc.php

<?php
 
namespace PKP\Services\QueryBuilders;
 
use Illuminate\Database\Capsule\Manager as Capsule;
use PKP\Services\QueryBuilders\Interfaces\EntityQueryBuilderInterface;
 
abstract class PKPSubmissionQueryBuilder implements EntityQueryBuilderInterface {
 
   protected $categoryIds = null;
 
   protected $contextId = null;
 
   protected $columns = array();
 
   protected $orderColumn = 's.date_submitted';
 
   protected $orderDirection = 'DESC';
 
   protected $statuses = null;
 
   protected $stageIds = null;
 
   protected $assignedTo = [];
 
   protected $searchPhrase = null;
 
   protected $isIncomplete = false;
 
   protected $isOverdue = false;
 
   protected $daysInactive = null;
 
   protected $limit = null;
 
   protected $offset = 0;
 
   public function filterByContext($contextId) {
      $this->contextId = $contextId;
      return $this;
   }
 
   public function orderBy($column, $direction = 'DESC') {
      // Bring in orderby constants
      import('classes.submission.SubmissionDAO');
      if ($column === 'lastModified') {
         $this->orderColumn = 's.last_modified';
      } elseif ($column === 'dateLastActivity') {
         $this->orderColumn = 's.date_last_activity';
      } elseif ($column === 'title') {
         $this->orderColumn = Capsule::raw('COALESCE(publication_tlps.setting_value, publication_tlpsl.setting_value)');
      } elseif ($column === 'seq') {
         $this->orderColumn = 'po.seq';
      } elseif ($column === ORDERBY_DATE_PUBLISHED) {
         $this->orderColumn = 'po.date_published';
      } else {
         $this->orderColumn = 's.date_submitted';
      }
      $this->orderDirection = $direction;
      return $this;
   }
 
   public function filterByCategories($categoryIds) {
      if (!is_null($categoryIds) && !is_array($categoryIds)) {
         $categoryIds = array($categoryIds);
      }
      $this->categoryIds = $categoryIds;
      return $this;
   }
 
   public function filterByStatus($statuses) {
      if (!is_null($statuses) && !is_array($statuses)) {
         $statuses = array($statuses);
      }
      $this->statuses = $statuses;
      return $this;
   }
 
   public function filterByStageIds($stageIds) {
      if (!is_null($stageIds) && !is_array($stageIds)) {
         $stageIds = array($stageIds);
      }
      $this->stageIds = $stageIds;
      return $this;
   }
 
   public function filterByIncomplete($isIncomplete) {
      $this->isIncomplete = $isIncomplete;
      return $this;
   }
 
   public function filterByOverdue($isOverdue) {
      $this->isOverdue = $isOverdue;
      return $this;
   }
 
   public function filterByDaysInactive($daysInactive) {
      $this->daysInactive = $daysInactive;
      return $this;
   }
 
   public function assignedTo($assignedTo) {
      $this->assignedTo = $assignedTo;
      return $this;
   }
 
   public function searchPhrase($phrase) {
      $this->searchPhrase = $phrase;
      return $this;
   }
 
   public function limitTo($count) {
      $this->limit = $count;
      return $this;
   }
 
   public function offsetBy($offset) {
      $this->offset = $offset;
      return $this;
   }
 
   public function getCount() {
      return $this
         ->getQuery()
         ->select('s.submission_id')
         ->get()
         ->count();
   }
 
   public function getIds() {
      return $this
         ->getQuery()
         ->select('s.submission_id')
         ->pluck('s.submission_id')
         ->toArray();
   }
 
   public function getQuery() {
      $this->columns[] = 's.*';
      $q = Capsule::table('submissions as s')
               ->orderBy($this->orderColumn, $this->orderDirection)
               ->groupBy('s.submission_id');
 
      // context
      // Never permit a query without a context_id clause unless the '*' wildcard
      // has been set explicitely.
      if (is_null($this->contextId)) {
         $q->where('s.context_id', '=', CONTEXT_ID_NONE);
      } elseif ($this->contextId !== '*') {
         $q->where('s.context_id', '=' , $this->contextId);
      }
 
      // order by title
      if (is_object($this->orderColumn) && $this->orderColumn->getValue() === 'COALESCE(publication_tlps.setting_value, publication_tlpsl.setting_value)') {
         $locale = \AppLocale::getLocale();
         $this->columns[] = Capsule::raw('COALESCE(publication_tlps.setting_value, publication_tlpsl.setting_value)');
         $q->leftJoin('publications as publication_tlp', 's.current_publication_id', '=', 'publication_tlp.publication_id')
            ->leftJoin('publication_settings as publication_tlps', 'publication_tlp.publication_id', '=', 'publication_tlps.publication_id')
            ->where('publication_tlps.setting_name', '=', 'title')
            ->where('publication_tlps.locale', '=', $locale);
         $q->leftJoin('publications as publication_tlpl', 's.current_publication_id', '=', 'publication_tlpl.publication_id')
            ->leftJoin('publication_settings as publication_tlpsl', 'publication_tlp.publication_id', '=', 'publication_tlpsl.publication_id')
            ->where('publication_tlpsl.setting_name', '=', 'title')
            ->where('publication_tlpsl.locale', '=', Capsule::raw('publication_tlpl.locale'));
         $q->groupBy(Capsule::raw('COALESCE(publication_tlps.setting_value, publication_tlpsl.setting_value)'));
      }
 
      // order by publication sequence
      if ($this->orderColumn === 'po.seq') {
         $this->columns[] = 'po.seq';
         $q->leftJoin('publications as po', 's.current_publication_id', '=', 'po.publication_id');
         $q->groupBy('po.seq');
 
      // order by date of current version's publication
      } else if ($this->orderColumn === 'po.date_published') {
         $this->columns[] = 'po.date_published';
         $q->leftJoin('publications as po', 's.current_publication_id', '=', 'po.publication_id');
      }
 
      // statuses
      if (!is_null($this->statuses)) {
         $q->whereIn('s.status', $this->statuses);
      }
 
      // stage ids
      if (!is_null($this->stageIds)) {
         $q->whereIn('s.stage_id', $this->stageIds);
      }
 
      // incomplete submissions
      if ($this->isIncomplete) {
         $q->where('s.submission_progress', '>', 0);
      }
 
      //inactive for X days
      if ($this->daysInactive) {
         $q->where('s.date_last_activity', '<', \Core::getCurrentDate(strtotime('-'.$this->daysInactive.' days')));
      }
 
      // overdue submissions
      if ($this->isOverdue) {
         $q->leftJoin('review_assignments as raod', 'raod.submission_id', '=', 's.submission_id')
            ->leftJoin('review_rounds as rr', function($table) {
               $table->on('rr.submission_id', '=', 's.submission_id');
               $table->on('raod.review_round_id', '=', 'rr.review_round_id');
            });
         // Only get overdue assignments on active review rounds
         import('lib.pkp.classes.submission.reviewRound.ReviewRound');
         $q->where('rr.status', '!=', REVIEW_ROUND_STATUS_RESUBMIT_FOR_REVIEW);
         $q->where('rr.status', '!=', REVIEW_ROUND_STATUS_SENT_TO_EXTERNAL);
         $q->where('rr.status', '!=', REVIEW_ROUND_STATUS_ACCEPTED);
         $q->where('rr.status', '!=', REVIEW_ROUND_STATUS_DECLINED);
         $q->where(function ($q) {
            $q->where('raod.declined', '<>', 1);
            $q->where('raod.cancelled', '<>', 1);
            $q->where(function ($q) {
               $q->where('raod.date_due', '<', \Core::getCurrentDate(strtotime('tomorrow')));
               $q->whereNull('raod.date_completed');
            });
            $q->orWhere(function ($q) {
               $q->where('raod.date_response_due', '<', \Core::getCurrentDate(strtotime('tomorrow')));
               $q->whereNull('raod.date_confirmed');
            });
         });
      }
 
      // Assigned to
      $isAssignedOnly = !empty($this->assignedTo) && $this->assignedTo !== -1;
      if ($isAssignedOnly) {
         $assignedTo = $this->assignedTo;
 
         // Stage assignments
         $q->leftJoin('stage_assignments as sa', function($table) use ($assignedTo) {
            $table->on('s.submission_id', '=', 'sa.submission_id');
            $table->whereIn('sa.user_id', $assignedTo);
         });
 
         // Review assignments
         $q->leftJoin('review_assignments as ra', function($table) use ($assignedTo) {
            $table->on('s.submission_id', '=', 'ra.submission_id');
            $table->on('ra.declined', '=', Capsule::raw((int) 0));
            $table->whereIn('ra.reviewer_id', $assignedTo);
         });
 
         $q->where(function($q) {
            $q->whereNotNull('sa.stage_assignment_id');
            $q->orWhereNotNull('ra.review_id');
         });
      } elseif ($this->assignedTo === -1) {
         $sub = Capsule::table('stage_assignments')
                  ->select(Capsule::raw('count(stage_assignments.stage_assignment_id)'))
                  ->leftJoin('user_groups','stage_assignments.user_group_id','=','user_groups.user_group_id')
                  ->where('stage_assignments.submission_id', '=', Capsule::raw('s.submission_id'))
                  ->whereIn('user_groups.role_id', array(ROLE_ID_MANAGER, ROLE_ID_SUB_EDITOR));
 
         $q->whereNotNull('s.date_submitted')
            ->mergeBindings($sub)
            ->where(Capsule::raw('(' . $sub->toSql() . ')'),'=','0')
            ->groupBy('s.date_submitted'); // postgres compatibility
      }
 
      // search phrase
      if (!empty($this->searchPhrase)) {
         $words = explode(' ', $this->searchPhrase);
         if (count($words)) {
            $q->leftJoin('publications as p', 'p.submission_id', '=', 's.submission_id')
               ->leftJoin('publication_settings as ps','p.publication_id','=','ps.publication_id')
               ->leftJoin('authors as au','p.publication_id','=','au.publication_id')
               ->leftJoin('author_settings as aus', 'aus.author_id', '=', 'au.author_id');
 
            foreach ($words as $word) {
               $word = strtolower(addcslashes($word, '%_'));
               $q->where(function($q) use ($word, $isAssignedOnly)  {
                  $q->where(function($q) use ($word) {
                     $q->where('ps.setting_name', 'title');
                     $q->where(Capsule::raw('lower(ps.setting_value)'), 'LIKE', "%{$word}%");
                  })
                  ->orWhere(function($q) use ($word) {
                     $q->where('aus.setting_name', IDENTITY_SETTING_GIVENNAME);
                     $q->where(Capsule::raw('lower(aus.setting_value)'), 'LIKE', "%{$word}%");
                  })
                  ->orWhere(function($q) use ($word, $isAssignedOnly) {
                     $q->where('aus.setting_name', IDENTITY_SETTING_FAMILYNAME);
                     $q->where(Capsule::raw('lower(aus.setting_value)'), 'LIKE', "%{$word}%");
                  })
                  ->orWhere(function($q) use ($word, $isAssignedOnly) {
                     $q->where('aus.setting_name', 'orcid');
                     $q->where(Capsule::raw('lower(aus.setting_value)'), '=', "{$word}");
                  });
                  // Prevent reviewers from matching searches by author name
                  if ($isAssignedOnly) {
                     $q->whereNull('ra.reviewer_id');
                  }
                  if (ctype_digit((string) $word)) {
                     $q->orWhere('s.submission_id', '=', $word);
                  }
               });
            }
 
         }
      }
 
      // Category IDs
      if (!empty($this->categoryIds)) {
         $q->leftJoin('publication_categories as pc', 's.current_publication_id', '=', 'pc.publication_id')
            ->whereIn('pc.category_id', $this->categoryIds);
      }
 
      // Limit and offset results for pagination
      if (!is_null($this->limit)) {
         $q->limit($this->limit);
      }
      if (!empty($this->offset)) {
         $q->offset($this->offset);
      }
 
      // Add app-specific query statements
      \HookRegistry::call('Submission::getMany::queryObject', array(&$q, $this));
 
      $q->select($this->columns);
 
      return $q;
   }
 
   abstract function appGet($q);
}