PKPEmailTemplateQueryBuilder.inc.php

<?php
namespace PKP\Services\QueryBuilders;
 
use Illuminate\Database\Capsule\Manager as Capsule;
use PKP\Services\QueryBuilders\Interfaces\EntityQueryBuilderInterface;
 
class PKPEmailTemplateQueryBuilder implements EntityQueryBuilderInterface {
 
   protected $contextId = null;
 
   protected $isEnabled = null;
 
   protected $isCustom = null;
 
   protected $fromRoleIds = [];
 
   protected $toRoleIds = [];
 
   protected $keys = [];
 
   protected $searchPhrase = null;
 
   protected $stageIds = [];
 
   public function filterByContext($contextId) {
      $this->contextId = $contextId;
      return $this;
   }
 
   public function filterByIsEnabled($isEnabled) {
      $this->isEnabled = $isEnabled;
      return $this;
   }
 
   public function filterByIsCustom($isCustom) {
      $this->isCustom = $isCustom;
      return $this;
   }
 
   public function filterByFromRoleIds($fromRoleIds) {
      $this->fromRoleIds = $fromRoleIds;
      return $this;
   }
 
   public function filterByToRoleIds($toRoleIds) {
      $this->toRoleIds = $toRoleIds;
      return $this;
   }
 
   public function filterByKeys($keys) {
      $this->keys = $keys;
      return $this;
   }
 
   public function filterByStageIds($stageIds) {
      $this->stageIds = $stageIds;
      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 getIds() {
      throw new \Exception('PKPEmailTemplateQueryBuilder::getIds() is not supported. Email templates should be referenced by key instead of id.');
   }
 
   public function getCount() {
      $compiledQuery = $this->getCompiledQuery();
      return Capsule::table(Capsule::raw('(' . $compiledQuery[0] . ') as email_template_count'))
         ->setBindings($compiledQuery[1])
         ->count();
   }
 
   public function getQuery() {
      throw new \Exception('PKPEmailTemplateQueryBuilder::getQuery() is not supported. Use PKPEmailTemplateQueryBuilder::getCompiledQuery() instead.');
   }
 
   public function getCompiledQuery() {
      $this->setCommonColumns();
 
      $defaultQueryObject = $this->getDefault();
 
      // Use a UNION to ensure the query will match rows in email_templates and
      // email_templates_default. This ensures that custom templates which have
      // no default in email_templates_default are still returned. These templates
      // should not be returned when a role filter is used.
      if (empty($this->fromRoleIds) && empty($this->toRoleIds)) {
         $customQueryObject = $this->getCustom();
         return [
            '(' . $defaultQueryObject->toSql() . ') union (' . $customQueryObject->toSql() . ')',
            $defaultQueryObject->mergeBindings($customQueryObject)->getBindings(),
         ];
      }
 
      return [
         $defaultQueryObject->toSql(),
         $defaultQueryObject->getBindings(),
      ];
   }
 
   public function getModified() {
      $this->setCommonColumns();
      $q = $this->getCustom();
      $q->whereNotNull('et.email_id');
      return $q;
   }
 
   protected function setCommonColumns() {
      $this->columns = [
         'etd.can_disable',
         'etd.can_edit',
         Capsule::raw('COALESCE(etd.email_key, et.email_key) as email_key'),
         'etd.from_role_id',
         'etd.to_role_id',
         'etd.stage_id',
         'et.email_id',
         'et.context_id',
         Capsule::raw('COALESCE(et.enabled, 1) as enabled'),
      ];
   }
 
   protected function getDefault() {
      $q = Capsule::table('email_templates_default as etd')
         ->orderBy('email_key', 'asc')
         ->groupBy('etd.email_key')
         ->groupBy('etd.can_disable')
         ->groupBy('etd.can_edit')
         ->groupBy('etd.from_role_id')
         ->groupBy('etd.to_role_id')
         ->groupBy('etd.stage_id')
         ->groupBy('et.email_id');
 
      if (!is_null($this->contextId)) {
         $contextId = $this->contextId;
         $q->leftJoin('email_templates as et', function ($table) use ($contextId) {
            $table->on('etd.email_key', '=', 'et.email_key')
               ->on('et.context_id', '=', Capsule::raw((int) $contextId));
         });
      } else {
         $q->leftJoin('email_templates as et', 'etd.email_key', '=', 'et.email_key');
      }
 
      if (!is_null($this->contextId)) {
         $contextId = $this->contextId;
         $q->where(function($q) use ($contextId) {
            $q->whereNull('et.context_id')
               ->orWhere('et.context_id', '=', $this->contextId);
         });
      }
 
      if (!empty($this->isEnabled)) {
         $q->where(function($q) {
            // Unmodified default templates are considered enabled
            $q->whereNull('et.enabled')
               ->orWhere('et.enabled', '=', 1);
         });
      } elseif ($this->isEnabled === false) {
         $q->where('et.enabled', '!=', 1);
      }
 
      if (!empty($this->isCustom)) {
         $q->whereNull('etd.can_disable');
      } elseif ($this->isCustom === false) {
         $q->whereNotNull('etd.can_disable');
      }
 
      if (!empty($this->fromRoleIds)) {
         $q->whereIn('etd.from_role_id', $this->fromRoleIds);
      }
 
      if (!empty($this->toRoleIds)) {
         $q->whereIn('etd.to_role_id', $this->toRoleIds);
      }
 
      if (!empty($this->stageIds)) {
         if (in_array(EMAIL_TEMPLATE_STAGE_DEFAULT, $this->stageIds)) {
            $q->whereNull('etd.stage_id')
               ->orWhereIn('etd.stage_id', $this->stageIds);
         } else {
            $q->whereIn('etd.stage_id', $this->stageIds);
         }
      }
 
      // search phrase
      if (!empty($this->searchPhrase)) {
         $words = explode(' ', $this->searchPhrase);
         if (count($words)) {
            $q->leftJoin('email_templates_settings as ets', 'et.email_id', '=', 'ets.email_id');
            $q->leftJoin('email_templates_default_data as etddata', 'etd.email_key', '=', 'etddata.email_key');
            foreach ($words as $word) {
               $word = strtolower(addcslashes($word, '%_'));
               $q->where(function($q) use ($word) {
                  $q->where(Capsule::raw('lower(et.email_key)'), 'LIKE', "%{$word}%")
                     ->orWhere(function($q) use ($word) {
                        $q->where('ets.setting_name', 'subject');
                        $q->where(Capsule::raw('lower(ets.setting_value)'), 'LIKE', "%{$word}%");
                     })
                     ->orWhere(function($q) use ($word) {
                        $q->where('ets.setting_name', 'body');
                        $q->where(Capsule::raw('lower(ets.setting_value)'), 'LIKE', "%{$word}%");
                     })
                     ->orWhere(Capsule::raw('lower(etd.email_key)'), 'LIKE', "%{$word}%")
                     ->orWhere(Capsule::raw('lower(etddata.subject)'), 'LIKE', "%{$word}%")
                     ->orWhere(Capsule::raw('lower(etddata.body)'), 'LIKE', "%{$word}%")
                     ->orWhere(Capsule::raw('lower(etddata.description)'), 'LIKE', "%{$word}%");
               });
            }
         }
      }
 
      if (!empty($this->keys)) {
         $keys = $this->keys;
         $q->where(function($q) use ($keys) {
            $q->whereIn('etd.email_key', $this->keys)
               ->orWhereIn('et.email_key', $this->keys);
         });
      }
 
      if (!empty($this->toRoleIds)) {
         $q->whereIn('etd.to_role_id', $this->toRoleIds);
      }
 
      // Add app-specific query statements
      \HookRegistry::call('EmailTemplate::getMany::queryObject::default', array($q, $this));
 
      $q->select($this->columns);
 
      return $q;
   }
 
   protected function getCustom() {
      $q = Capsule::table('email_templates as et')
         ->leftJoin('email_templates_default as etd', 'etd.email_key', '=', 'et.email_key');
 
      if (!is_null($this->contextId)) {
         $q->where(function($q) {
            $q->whereNull('et.context_id')
               ->orWhere('et.context_id', '=', $this->contextId);
         });
      }
 
      if (!empty($this->isEnabled)) {
         $q->where('et.enabled', '=', 1);
      } elseif ($this->isEnabled === false) {
         $q->where('et.enabled', '!=', 1);
      }
 
      if (!empty($this->isCustom)) {
         $q->whereNull('etd.can_disable');
      } elseif ($this->isCustom === false) {
         $q->whereNotNull('etd.can_disable');
      }
 
      if (!empty($this->searchPhrase)) {
         $words = explode(' ', $this->searchPhrase);
         if (count($words)) {
            $q->leftJoin('email_templates_settings as ets', 'et.email_id', '=', 'ets.email_id');
            foreach ($words as $word) {
               $word = strtolower(addcslashes($word, '%_'));
               $q->where(function ($q) use ($word) {
                  $q->where(Capsule::raw('lower(et.email_key)'), 'LIKE', "%{$word}%")
                     ->orWhere(function($q) use ($word) {
                        $q->where('ets.setting_name', 'subject');
                        $q->where(Capsule::raw('lower(ets.setting_value)'), 'LIKE', "%{$word}%");
                     })
                     ->orWhere(function($q) use ($word) {
                        $q->where('ets.setting_name', 'body');
                        $q->where(Capsule::raw('lower(ets.setting_value)'), 'LIKE', "%{$word}%");
                     });
               });
            }
         }
      }
 
      if (!empty($this->keys)) {
         $q->where(function($q) {
            $q->whereIn('etd.email_key', $this->keys)
               ->orWhereIn('et.email_key', $this->keys);
         });
      }
 
      // Add app-specific query statements
      \HookRegistry::call('EmailTemplate::getMany::queryObject::custom', array($q, $this));
 
      $q->select($this->columns);
 
      return $q;
   }
}