| <?php | |
| /** | |
| * Dewdrop | |
| * | |
| * @link https://github.com/DeltaSystems/dewdrop | |
| * @copyright Delta Systems (http://deltasys.com) | |
| * @license https://github.com/DeltaSystems/dewdrop/LICENSE | |
| */ | |
| namespace Dewdrop\Db\Select\Filter; | |
| use DateTime; | |
| use Dewdrop\Db\Select; | |
| use Dewdrop\Db\Select\Filter\Exception\InvalidOperator; | |
| use Dewdrop\Db\Select\Filter\Exception\MissingQueryVar; | |
| use Dewdrop\Db\Select\SelectException; | |
| use Dewdrop\Inflector; | |
| use Dewdrop\Pimple; | |
| use Exception; | |
| /** | |
| * A filter implementation for date (and related datatypes such as timestamps) | |
| * fields. This is the most complex filter. It provides many different operators | |
| * to help select common date ranges (e.g. "This Month"). | |
| * | |
| * Internally, this filter divides a lot of its code between operators that require | |
| * a single input (e.g. OP_IS) and those that require two inputs to check a range | |
| * of dates (e.g. OP_BETWEEN). | |
| */ | |
| class Date | |
| { | |
| /** | |
| * Operator constant for dates in a range (inclusive). | |
| * | |
| * @const | |
| */ | |
| const OP_ON_OR_BETWEEN = 'on-or-between'; | |
| /** | |
| * Operator constant for dates in a range (exclusive). | |
| * | |
| * @const | |
| */ | |
| const OP_BETWEEN = 'between'; | |
| /** | |
| * Operator constant for dates before value (inclusive). | |
| * | |
| * @const | |
| */ | |
| const OP_ON_OR_BEFORE = 'on-or-before'; | |
| /** | |
| * Operator constant for dates before value (exclusive). | |
| * | |
| * @const | |
| */ | |
| const OP_BEFORE = 'before'; | |
| /** | |
| * Operator constant for dates after value (inclusive). | |
| * | |
| * @const | |
| */ | |
| const OP_ON_OR_AFTER = 'on-or-after'; | |
| /** | |
| * Operator constant for dates after value (exclusive). | |
| * | |
| * @const | |
| */ | |
| const OP_AFTER = 'after'; | |
| /** | |
| * Operator constant for exact date match. | |
| * | |
| * @const | |
| */ | |
| const OP_IS = 'is'; | |
| /** | |
| * Operator constant for today. | |
| * | |
| * @const | |
| */ | |
| const OP_TODAY = 'today'; | |
| /** | |
| * Operator constant for yesterday. | |
| * | |
| * @const | |
| */ | |
| const OP_YESTERDAY = 'yesterday'; | |
| /** | |
| * Operator constant for this week. | |
| * | |
| * @const | |
| */ | |
| const OP_THIS_WEEK = 'this-week'; | |
| /** | |
| * Operator constant for this month. | |
| * | |
| * @const | |
| */ | |
| const OP_THIS_MONTH = 'this-month'; | |
| /** | |
| * Operator constant for this year. | |
| * | |
| * @const | |
| */ | |
| const OP_THIS_YEAR = 'this-year'; | |
| /** | |
| * The name of the table in which the filtered column is present. | |
| * | |
| * @var string | |
| */ | |
| private $tableName; | |
| /** | |
| * The name of the DB column you need to filter. | |
| * | |
| * @var string | |
| */ | |
| private $columnName; | |
| /** | |
| * Inflector object used when converting from operator to filter method name. | |
| * | |
| * @var Inflector | |
| */ | |
| private $inflector; | |
| /** | |
| * Whether to cut off time values on timestamps when filtering input. | |
| * | |
| * @var bool | |
| */ | |
| private $truncateTimestamps = true; | |
| /** | |
| * Provide the table and column names that will be filtered by this object. | |
| * | |
| * @param string $tableName | |
| * @param string $columnName | |
| * @param Inflector $inflector | |
| */ | |
| public function __construct($tableName, $columnName, Inflector $inflector = null) | |
| { | |
| $this->tableName = $tableName; | |
| $this->columnName = $columnName; | |
| $this->inflector = ($inflector ?: Pimple::getResource('inflector')); | |
| } | |
| /** | |
| * Set whether time values should be truncated from timestamps when filtering | |
| * input. | |
| * | |
| * @param bool $truncateTimestamps | |
| * @return $this | |
| */ | |
| public function setTruncateTimestamps($truncateTimestamps) | |
| { | |
| $this->truncateTimestamps = $truncateTimestamps; | |
| return $this; | |
| } | |
| /** | |
| * Apply the filter to the supplied Select object. | |
| * | |
| * @param Select $select | |
| * @param string $conditionSetName | |
| * @param array $queryVars | |
| * @return Select | |
| * @throws InvalidOperator | |
| * @throws MissingQueryVar | |
| */ | |
| public function apply(Select $select, $conditionSetName, array $queryVars) | |
| { | |
| $this->validate($queryVars); | |
| $operator = $queryVars['comp']; | |
| $start = null; | |
| $end = null; | |
| $startObj = null; | |
| $startIso = null; | |
| $endObj = null; | |
| $endIso = null; | |
| if ($this->truncateTimestamps) { | |
| $format = 'Y-m-d'; | |
| } else { | |
| $format = 'Y-m-d G:i:s'; | |
| } | |
| if ($queryVars['start']) { | |
| try { | |
| $startObj = new DateTime($queryVars['start']); | |
| $startIso = $startObj->format($format); | |
| } catch (Exception $e) { | |
| // If we get input we can't parse, we just throw it out | |
| } | |
| } | |
| if ($queryVars['end']) { | |
| try { | |
| $endObj = new DateTime($queryVars['end']); | |
| $endIso = $endObj->format($format); | |
| } catch (Exception $e) { | |
| // If we get input we can't parse, we just throw it out | |
| } | |
| } | |
| // If the second input is greater than the first, swap them for the user | |
| if ($startObj && $endObj && $endObj < $startObj) { | |
| $swapEndIso = $endIso; | |
| $endIso = $startIso; | |
| $startIso = $swapEndIso; | |
| } | |
| $methodName = 'filter' . $this->inflector->camelize($operator); | |
| return $this->$methodName($select, $conditionSetName, $startIso, $endIso); | |
| } | |
| /** | |
| * Filter the Select to find records on or between the start and end dates. | |
| * | |
| * @param Select $select | |
| * @param string $conditionSetName | |
| * @param string $start | |
| * @param string $end | |
| * @return Select | |
| */ | |
| public function filterOnOrBetween(Select $select, $conditionSetName, $start, $end) | |
| { | |
| return $this->filterBetweenInternal(true, $select, $conditionSetName, $start, $end); | |
| } | |
| /** | |
| * Filter the Select to find records between the start and end dates. | |
| * | |
| * @param Select $select | |
| * @param string $conditionSetName | |
| * @param string $start | |
| * @param string $end | |
| * @return Select | |
| */ | |
| public function filterBetween(Select $select, $conditionSetName, $start, $end) | |
| { | |
| return $this->filterBetweenInternal(false, $select, $conditionSetName, $start, $end); | |
| } | |
| /** | |
| * This method does the heavy-lifting for the two "between" operators (both | |
| * inclusive and exclusive). This method will attempt to handle situations | |
| * where we don't actually have a start or end date by delegating to other | |
| * methods. | |
| * | |
| * @param bool $inclusive | |
| * @param Select $select | |
| * @param string $conditionSetName | |
| * @param string $start | |
| * @param string $end | |
| * @return Select | |
| */ | |
| private function filterBetweenInternal($inclusive, Select $select, $conditionSetName, $start, $end) | |
| { | |
| if (!$start && !$end) { | |
| return $select; | |
| } elseif (!$start && $inclusive) { | |
| return $this->filterOnOrBefore($select, $conditionSetName, $end, $start); | |
| } elseif (!$start && !$inclusive) { | |
| return $this->filterBefore($select, $conditionSetName, $end, $start); | |
| } elseif (!$end && $inclusive) { | |
| return $this->filterOnOrAfter($select, $conditionSetName, $start, $end); | |
| } elseif (!$end) { | |
| return $this->filterAfter($select, $conditionSetName, $start, $end); | |
| } | |
| $quotedAlias = $this->getAliasForComparison($select); | |
| $inclusive = ($inclusive ? '=' : ''); | |
| $select->whereConditionSet( | |
| $conditionSetName, | |
| sprintf( | |
| '%s AND %s', | |
| $select->getAdapter()->quoteInto("{$quotedAlias} >{$inclusive} ?", $start), | |
| $select->getAdapter()->quoteInto("{$quotedAlias} <{$inclusive} ?", $end) | |
| ) | |
| ); | |
| return $select; | |
| } | |
| public function filterBefore(Select $select, $conditionSetName, $start, $end) | |
| { | |
| return $this->filterSingleInputInternal(false, $select, $conditionSetName, 'before', $start); | |
| } | |
| public function filterOnOrBefore(Select $select, $conditionSetName, $start, $end) | |
| { | |
| return $this->filterSingleInputInternal(true, $select, $conditionSetName, 'before', $start); | |
| } | |
| public function filterAfter(Select $select, $conditionSetName, $start, $end) | |
| { | |
| return $this->filterSingleInputInternal(false, $select, $conditionSetName, 'after', $start); | |
| } | |
| public function filterOnOrAfter(Select $select, $conditionSetName, $start, $end) | |
| { | |
| return $this->filterSingleInputInternal(true, $select, $conditionSetName, 'after', $start); | |
| } | |
| public function filterIs(Select $select, $conditionSetName, $start, $end) | |
| { | |
| return $this->filterSingleInputInternal(false, $select, $conditionSetName, 'equal', $start); | |
| } | |
| /** | |
| * This method does the heavy lifting for all the single-input operators | |
| * this filter supports. | |
| * | |
| * @param bool $inclusive | |
| * @param Select $select | |
| * @param string $conditionSetName | |
| * @param string $type | |
| * @param string $value | |
| * @return $this | |
| * @throws Exception | |
| * @throws SelectException | |
| */ | |
| private function filterSingleInputInternal($inclusive, Select $select, $conditionSetName, $type, $value) | |
| { | |
| if ('after' !== $type && 'before' !== $type && 'equal' !== $type) { | |
| throw new SelectException('Expected either "before" or "after" or "equal" as type.'); | |
| } | |
| $quotedAlias = $this->getAliasForComparison($select); | |
| $inclusive = ($inclusive ? '=' : ''); | |
| switch ($type) { | |
| case 'before': | |
| $operator = '<'; | |
| break; | |
| case 'after': | |
| $operator = '>'; | |
| break; | |
| case 'equal': | |
| $operator = '='; | |
| break; | |
| default: | |
| throw new Exception("filterSingleInputInternal expects before, after or equal operators only."); | |
| break; | |
| } | |
| return $select->whereConditionSet( | |
| $conditionSetName, | |
| "{$quotedAlias} {$operator}{$inclusive} ?", | |
| $value | |
| ); | |
| } | |
| private function getAliasForComparison(Select $select) | |
| { | |
| $quotedAlias = $select->quoteWithAlias($this->tableName, $this->columnName); | |
| $dbAdapter = $select->getAdapter(); | |
| $metadata = $dbAdapter->getTableMetadata($this->tableName); | |
| if ($this->truncateTimestamps && 'timestamp' === $metadata['columns'][$this->columnName]['GENERIC_TYPE']) { | |
| $quotedAlias = $dbAdapter->getDriver()->truncateTimestampToDate($quotedAlias); | |
| } | |
| return $quotedAlias; | |
| } | |
| /** | |
| * Ensure the expected input vars were supplied before we attempt to apply | |
| * a filter. We need the "comp" variable, which gives us the operator, | |
| * along with a start and end date. | |
| * | |
| * @param array $vars | |
| * @throws InvalidOperator | |
| * @throws MissingQueryVar | |
| */ | |
| private function validate(array $vars) | |
| { | |
| if (!isset($vars['comp'])) { | |
| throw new MissingQueryVar('"comp" variable expected.'); | |
| } | |
| if (!isset($vars['start'])) { | |
| throw new MissingQueryVar('"start" variable expected.'); | |
| } | |
| if (!isset($vars['end'])) { | |
| throw new MissingQueryVar('"end" variable expected.'); | |
| } | |
| if (!$this->isValidOperator($vars['comp'])) { | |
| throw new InvalidOperator("{$vars['comp']} is not a valid operator for date filters."); | |
| } | |
| } | |
| /** | |
| * Check to see if the supplied operator is valid. | |
| * | |
| * @param string $operator | |
| * @return bool | |
| */ | |
| private function isValidOperator($operator) | |
| { | |
| switch ($operator) { | |
| case self::OP_ON_OR_BETWEEN: | |
| case self::OP_BETWEEN: | |
| case self::OP_ON_OR_BEFORE: | |
| case self::OP_BEFORE: | |
| case self::OP_ON_OR_AFTER: | |
| case self::OP_AFTER: | |
| case self::OP_IS: | |
| case self::OP_TODAY: | |
| case self::OP_YESTERDAY: | |
| case self::OP_THIS_WEEK: | |
| case self::OP_THIS_MONTH: | |
| case self::OP_THIS_YEAR: | |
| return true; | |
| default: | |
| return false; | |
| } | |
| } | |
| } |