IssueQueryBuilder.inc.php

<?php
 
namespace APP\Services\QueryBuilders;
 
use Illuminate\Database\Capsule\Manager as Capsule;
use PKP\Services\QueryBuilders\Interfaces\EntityQueryBuilderInterface;
 
class IssueQueryBuilder implements EntityQueryBuilderInterface {
 
   protected $contextId = null;
 
   protected $columns = array();
 
   protected $orderColumn = 'i.date_published';
 
   protected $orderDirection = 'DESC';
 
   protected $isPublished = null;
 
   protected $issueIds = [];
 
   protected $volumes = null;
 
   protected $numbers = null;
 
   protected $years = null;
 
   protected $countOnly = null;
 
   protected $limit = null;
 
   protected $offset = 0;
 
   protected $searchPhrase = '';
 
   public function filterByContext($contextId) {
      $this->contextId = $contextId;
      return $this;
   }
 
   public function orderBy($column, $direction = 'DESC') {
      if ($column === 'lastModified') {
         $this->orderColumn = 'i.last_modified';
      } elseif ($column === 'seq') {
         $this->orderColumn = 'o.seq';
      } else {
         $this->orderColumn = 'i.date_published';
      }
      $this->orderDirection = $direction;
      return $this;
   }
 
   public function filterByPublished($isPublished) {
      $this->isPublished = $isPublished;
      return $this;
   }
 
   public function filterByVolumes($volumes) {
      if (!is_null($volumes) && !is_array($volumes)) {
         $volumes = array($volumes);
      }
      $this->volumes = $volumes;
      return $this;
   }
 
   public function filterByNumbers($numbers) {
      if (!is_null($numbers) && !is_array($numbers)) {
         $numbers = array($numbers);
      }
      $this->numbers = $numbers;
      return $this;
   }
 
   public function filterByYears($years) {
      if (!is_null($years) && !is_array($years)) {
         $years = array($years);
      }
      $this->years = $years;
      return $this;
   }
 
   public function filterByIds($issueIds) {
      $this->issueIds = $issueIds;
      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('i.issue_id')
         ->get()
         ->count();
   }
 
   public function getIds() {
      return $this
         ->getQuery()
         ->select('i.issue_id')
         ->pluck('i.issue_id')
         ->toArray();
   }
 
   public function getQuery() {
      $this->columns[] = 'i.*';
      $q = Capsule::table('issues as i')
               ->leftJoin('issue_settings as is', 'i.issue_id', '=', 'is.issue_id')
               ->leftJoin('custom_issue_orders as o', 'o.issue_id', '=', 'i.issue_id')
               ->orderBy($this->orderColumn, $this->orderDirection)
               ->groupBy('i.issue_id', $this->orderColumn);
 
      // context
      // Never permit a query without a context_id clause unless the '*' wildcard
      // has been set explicitely.
      if (is_null($this->contextId)) {
         $q->where('i.journal_id', '=', CONTEXT_ID_NONE);
      } elseif ($this->contextId !== '*') {
         $q->where('i.journal_id', '=' , $this->contextId);
      }
 
      // published
      if (!is_null($this->isPublished)) {
         $q->where('i.published', $this->isPublished ? 1 : 0);
      }
 
      // volumes
      if (!is_null($this->volumes)) {
         $q->whereIn('i.volume', $this->volumes);
      }
 
      // numbers
      if (!is_null($this->numbers)) {
         $q->whereIn('i.number', $this->numbers);
      }
 
      // years
      if (!is_null($this->years)) {
         $q->whereIn('i.year', $this->years);
      }
 
      // issue ids
      if (!empty($this->issueIds)) {
         $q->whereIn('i.issue_id', $this->issueIds);
      }
 
      // search phrase
      if (!empty($this->searchPhrase)) {
         $searchPhrase = $this->searchPhrase;
 
         // Add support for searching for the volume, number and year
         // using the localized issue identification formats. In
         // en_US this will match Vol. 1. No. 1 (2018) against:
         // i.volume = 1 AND i.number = 1 AND i.year = 2018
         $volume = '';
         $number = '';
         $year = '';
         $volumeRegex = '/' . preg_quote(__('issue.vol')) . '\s\S/';
         preg_match($volumeRegex, $searchPhrase, $matches);
         if (count($matches)) {
            $volume = trim(str_replace(__('issue.vol'), '', $matches[0]));
            $searchPhrase = str_replace($matches[0], '', $searchPhrase);
         }
         $numberRegex = '/' . preg_quote(__('issue.no')) . '\s\S/';
         preg_match($numberRegex, $searchPhrase, $matches);
         if (count($matches)) {
            $number = trim(str_replace(__('issue.no'), '', $matches[0]));
            $searchPhrase = str_replace($matches[0], '', $searchPhrase);
         }
         preg_match('/\(\d{4}\)\:?/', $searchPhrase, $matches);
         if (count($matches)) {
            $year = substr($matches[0], 1, 4);
            $searchPhrase = str_replace($matches[0], '', $searchPhrase);
         }
         if ($volume !== '' || $number !== '' || $year !== '') {
            $q->where(function($q) use ($volume, $number, $year) {
               if ($volume) {
                  $q->where('i.volume', '=', $volume);
               }
               if ($number) {
                  $q->where('i.number', '=', $number);
               }
               if ($year) {
                  $q->where('i.year', '=', $year);
               }
            });
         }
 
         $words = array_unique(explode(' ', $searchPhrase));
         if (count($words)) {
            foreach ($words as $word) {
               $word = strtolower(addcslashes($word, '%_'));
               $q->where(function($q) use ($word)  {
                  $q->where(function($q) use ($word) {
                     $q->where('is.setting_name', 'title');
                     $q->where(Capsule::raw('lower(is.setting_value)'), 'LIKE', "%{$word}%");
                  })
                  ->orWhere(function($q) use ($word) {
                     $q->where('is.setting_name', 'description');
                     $q->where(Capsule::raw('lower(is.setting_value)'), 'LIKE', "%{$word}%");
                  });
 
                  // Match any four-digit number to the year
                  if (ctype_digit($word) && strlen($word) === 4) {
                     $q->orWhere('i.year', '=', $word);
                  }
               });
            }
         }
      }
 
      // Allow third-party query statements
      \HookRegistry::call('Issue::getMany::queryObject', array(&$q, $this));
 
      $q->select($this->columns);
 
      return $q;
   }
}