1 <?php

     2 /**

     3  * WP_Date_Query will generate a MySQL WHERE clause for the specified date-based parameters.

     4  *

     5  * Initialize the class by passing an array of arrays of parameters.

     6  *

     7  * @link http://codex.wordpress.org/Function_Reference/WP_Query Codex page.

     8  *

     9  * @since 3.7.0

    10  */

    11 class WP_Date_Query {

    12 	/**

    13 	 * List of date queries.

    14 	 *

    15 	 * @since 3.7.0

    16 	 * @access public

    17 	 * @var array

    18 	 */

    19 	public $queries = array();

    20 

    21 	/**

    22 	 * The relation between the queries. Can be either 'AND' or 'OR' and can be changed via the query arguments.

    23 	 *

    24 	 * @since 3.7.0

    25 	 * @access public

    26 	 * @var string

    27 	 */

    28 	public $relation = 'AND';

    29 

    30 	/**

    31 	 * The column to query against. Can be changed via the query arguments.

    32 	 *

    33 	 * @since 3.7.0

    34 	 * @access public

    35 	 * @var string

    36 	 */

    37 	public $column = 'post_date';

    38 

    39 	/**

    40 	 * The value comparison operator. Can be changed via the query arguments.

    41 	 *

    42 	 * @since 3.7.0

    43 	 * @access public

    44 	 * @var array

    45 	 */

    46 	public $compare = '=';

    47 

    48 	/**

    49 	 * Constructor.

    50 	 *

    51 	 * @param array $date_query {

    52 	 *     One or more associative arrays of date query parameters.

    53 	 *

    54 	 *     @type array {

    55 	 *         @type string $column   Optional. The column to query against. If undefined, inherits the value of

    56 	 *                                the $default_column parameter. Default 'post_date'. Accepts 'post_date',

    57 	 *                                'post_date_gmt', 'post_modified','post_modified_gmt', 'comment_date',

    58 	 *                                'comment_date_gmt'.

    59 	 *         @type string $compare  Optional. The comparison operator.

    60 	 *                                Default '='. Accepts '=', '!=', '>', '>=', '<', '<=', 'IN', 'NOT IN',

    61 	 *                                'BETWEEN', 'NOT BETWEEN'.

    62 	 *         @type string $relation Optional. The boolean relationship between the date queryies.

    63 	 *                                Default 'OR'. Accepts 'OR', 'AND'.

    64 	 *         @type array {

    65 	 *             @type string|array $before Optional. Date to retrieve posts before. Accepts strtotime()-compatible

    66 	 *                                        string, or array of 'year', 'month', 'day' values. {

    67 	 *

    68 	 *                 @type string $year  The four-digit year. Default empty. Accepts any four-digit year.

    69 	 *                 @type string $month Optional when passing array.The month of the year.

    70 	 *                                     Default (string:empty)|(array:1). Accepts numbers 1-12.

    71 	 *                 @type string $day   Optional when passing array.The day of the month.

    72 	 *                                     Default (string:empty)|(array:1). Accepts numbers 1-31.

    73 	 *             }

    74 	 *             @type string|array $after Optional. Date to retrieve posts before. Accepts strtotime()-compatible

    75 	 *                                       string, or array of 'year', 'month', 'day' values. {

    76 	 *

    77 	 *                 @type string $year  The four-digit year. Default empty. Accepts any four-digit year.

    78 	 *                 @type string $month Optional when passing array.The month of the year.

    79 	 *                                     Default (string:empty)|(array:12). Accepts numbers 1-12.

    80 	 *                 @type string $day   Optional when passing array.The day of the month.

    81 	 *                                     Default (string:empty)|(array:last day of month). Accepts numbers 1-31.

    82 	 *             }

    83 	 *             @type string       $column    Optional. Used to add a clause comparing a column other than the column

    84 	 *                                           specified in the top-level $column paramater.  Default is the value

    85 	 *                                           of top-level $column. Accepts 'post_date', 'post_date_gmt',

    86 	 *                                           'post_modified', 'post_modified_gmt', 'comment_date', 'comment_date_gmt'.

    87 	 *             @type string       $compare   Optional. The comparison operator. Default '='. Accepts '=', '!=',

    88 	 *                                           '>', '>=', '<', '<=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'.

    89 	 *             @type bool         $inclusive Optional. Include results from dates specified in 'before' or 'after'.

    90 	 *                                           Default. Accepts.

    91 	 *             @type int          $year      Optional. The four-digit near number. Default empty. Accepts any

    92 	 *                                           four-digit year.

    93 	 *             @type int          $month     Optional. The two-digit month number. Default empty. Accepts numbers 1-12.

    94 	 *             @type int          $week      Optional. The week number of the year. Default empty. Accepts numbers 0-53.

    95 	 *             @type int          $day       Optional. The day of the month. Default empty. Accepts numbers 1-31.

    96 	 *             @type int          $hour      Optional. The hour of the day. Default empty. Accepts numbers 0-23.

    97 	 *             @type int          $minute    Optional. The minute of the hour. Default empty. Accepts numbers 0-60.

    98 	 *             @type int          $second    Optional. The second of the minute. Default empty. Accepts numbers 0-60.

    99 	 *         }

   100 	 *     }

   101 	 * }

   102 	 * @param array $default_column Optional. Default column to query against. Default 'post_date'.

   103 	 *                              Accepts 'post_date', 'post_date_gmt', 'post_modified', 'post_modified_gmt',

   104 	 *                              'comment_date', 'comment_date_gmt'.

   105 	 */

   106 	function __construct( $date_query, $default_column = 'post_date' ) {

   107 		if ( empty( $date_query ) || ! is_array( $date_query ) )

   108 			return;

   109 

   110 		if ( isset( $date_query['relation'] ) && strtoupper( $date_query['relation'] ) == 'OR' )

   111 			$this->relation = 'OR';

   112 		else

   113 			$this->relation = 'AND';

   114 

   115 		if ( ! empty( $date_query['column'] ) )

   116 			$this->column = esc_sql( $date_query['column'] );

   117 		else

   118 			$this->column = esc_sql( $default_column );

   119 

   120 		$this->column = $this->validate_column( $this->column );

   121 

   122 		$this->compare = $this->get_compare( $date_query );

   123 

   124 		// If an array of arrays wasn't passed, fix it

   125 		if ( ! isset( $date_query[0] ) )

   126 			$date_query = array( $date_query );

   127 

   128 		$this->queries = array();

   129 		foreach ( $date_query as $key => $query ) {

   130 			if ( ! is_array( $query ) )

   131 				continue;

   132 

   133 			$this->queries[$key] = $query;

   134 		}

   135 	}

   136 

   137 	/**

   138 	 * Determines and validates what comparison operator to use.

   139 	 *

   140 	 * @since 3.7.0

   141 	 * @access public

   142 	 *

   143 	 * @param array $query A date query or a date subquery

   144 	 * @return string The comparison operator

   145 	 */

   146 	public function get_compare( $query ) {

   147 		if ( ! empty( $query['compare'] ) && in_array( $query['compare'], array( '=', '!=', '>', '>=', '<', '<=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) )

   148 			return strtoupper( $query['compare'] );

   149 

   150 		return $this->compare;

   151 	}

   152 

   153 	/**

   154 	 * Validates a column name parameter.

   155 	 *

   156 	 * @since 3.7.0

   157 	 * @access public

   158 	 *

   159 	 * @param string $column The user-supplied column name.

   160 	 * @return string A validated column name value.

   161 	 */

   162 	public function validate_column( $column ) {

   163 		$valid_columns = array(

   164 			'post_date', 'post_date_gmt', 'post_modified',

   165 			'post_modified_gmt', 'comment_date', 'comment_date_gmt'

   166 		);

   167 		/**

   168 		 * Filter the list of valid date query columns.

   169 		 *

   170 		 * @since 3.7.0

   171 		 *

   172 		 * @param array $valid_columns An array of valid date query columns. Defaults are 'post_date', 'post_date_gmt',

   173 		 *                             'post_modified', 'post_modified_gmt', 'comment_date', 'comment_date_gmt'

   174 		 */

   175 		if ( ! in_array( $column, apply_filters( 'date_query_valid_columns', $valid_columns ) ) )

   176 			$column = 'post_date';

   177 

   178 		return $column;

   179 	}

   180 

   181 	/**

   182 	 * Turns an array of date query parameters into a MySQL string.

   183 	 *

   184 	 * @since 3.7.0

   185 	 * @access public

   186 	 *

   187 	 * @return string MySQL WHERE parameters

   188 	 */

   189 	public function get_sql() {

   190 		// The parts of the final query

   191 		$where = array();

   192 

   193 		foreach ( $this->queries as $key => $query ) {

   194 			$where_parts = $this->get_sql_for_subquery( $query );

   195 			if ( $where_parts ) {

   196 				// Combine the parts of this subquery into a single string

   197 				$where[ $key ] = '( ' . implode( ' AND ', $where_parts ) . ' )';

   198 			}

   199 		}

   200 

   201 		// Combine the subquery strings into a single string

   202 		if ( $where )

   203 			$where = ' AND ( ' . implode( " {$this->relation} ", $where ) . ' )';

   204 		else

   205 			$where = '';

   206 

   207 		/**

   208 		 * Filter the date query WHERE clause.

   209 		 *

   210 		 * @since 3.7.0

   211 		 *

   212 		 * @param string        $where WHERE clause of the date query.

   213 		 * @param WP_Date_Query $this  The WP_Date_Query instance.

   214 		 */

   215 		return apply_filters( 'get_date_sql', $where, $this );

   216 	}

   217 

   218 	/**

   219 	 * Turns a single date subquery into pieces for a WHERE clause.

   220 	 *

   221 	 * @since 3.7.0

   222 	 * return array

   223 	 */

   224 	protected function get_sql_for_subquery( $query ) {

   225 		global $wpdb;

   226 

   227 		// The sub-parts of a $where part

   228 		$where_parts = array();

   229 

   230 		$column = ( ! empty( $query['column'] ) ) ? esc_sql( $query['column'] ) : $this->column;

   231 

   232 		$column = $this->validate_column( $column );

   233 

   234 		$compare = $this->get_compare( $query );

   235 

   236 		$lt = '<';

   237 		$gt = '>';

   238 		if ( ! empty( $query['inclusive'] ) ) {

   239 			$lt .= '=';

   240 			$gt .= '=';

   241 		}

   242 

   243 		// Range queries

   244 		if ( ! empty( $query['after'] ) )

   245 			$where_parts[] = $wpdb->prepare( "$column $gt %s", $this->build_mysql_datetime( $query['after'], true ) );

   246 

   247 		if ( ! empty( $query['before'] ) )

   248 			$where_parts[] = $wpdb->prepare( "$column $lt %s", $this->build_mysql_datetime( $query['before'], false ) );

   249 

   250 		// Specific value queries

   251 

   252 		if ( isset( $query['year'] ) && $value = $this->build_value( $compare, $query['year'] ) )

   253 			$where_parts[] = "YEAR( $column ) $compare $value";

   254 

   255 		if ( isset( $query['month'] ) && $value = $this->build_value( $compare, $query['month'] ) )

   256 			$where_parts[] = "MONTH( $column ) $compare $value";

   257 

   258 		// Legacy

   259 		if ( isset( $query['monthnum'] ) && $value = $this->build_value( $compare, $query['monthnum'] ) )

   260 			$where_parts[] = "MONTH( $column ) $compare $value";

   261 

   262 		if ( isset( $query['week'] ) && false !== ( $value = $this->build_value( $compare, $query['week'] ) ) )

   263 			$where_parts[] = _wp_mysql_week( $column ) . " $compare $value";

   264 

   265 		// Legacy

   266 		if ( isset( $query['w'] ) && false !== ( $value = $this->build_value( $compare, $query['w'] ) ) )

   267 			$where_parts[] = _wp_mysql_week( $column ) . " $compare $value";

   268 

   269 		if ( isset( $query['dayofyear'] ) && $value = $this->build_value( $compare, $query['dayofyear'] ) )

   270 			$where_parts[] = "DAYOFYEAR( $column ) $compare $value";

   271 

   272 		if ( isset( $query['day'] ) && $value = $this->build_value( $compare, $query['day'] ) )

   273 			$where_parts[] = "DAYOFMONTH( $column ) $compare $value";

   274 

   275 		if ( isset( $query['dayofweek'] ) && $value = $this->build_value( $compare, $query['dayofweek'] ) )

   276 			$where_parts[] = "DAYOFWEEK( $column ) $compare $value";

   277 

   278 		if ( isset( $query['hour'] ) || isset( $query['minute'] ) || isset( $query['second'] ) ) {

   279 			// Avoid notices

   280 			foreach ( array( 'hour', 'minute', 'second' ) as $unit ) {

   281 				if ( ! isset( $query[$unit] ) ) {

   282 					$query[$unit] = null;

   283 				}

   284 			}

   285 

   286 			if ( $time_query = $this->build_time_query( $column, $compare, $query['hour'], $query['minute'], $query['second'] ) ) {

   287 				$where_parts[] = $time_query;

   288 			}

   289 		}

   290 

   291 		return $where_parts;

   292 	}

   293 

   294 	/**

   295 	 * Builds and validates a value string based on the comparison operator.

   296 	 *

   297 	 * @since 3.7.0

   298 	 * @access public

   299 	 *

   300 	 * @param string $compare The compare operator to use

   301 	 * @param string|array $value The value

   302 	 * @return string|int|false The value to be used in SQL or false on error.

   303 	 */

   304 	public function build_value( $compare, $value ) {

   305 		if ( ! isset( $value ) )

   306 			return false;

   307 

   308 		switch ( $compare ) {

   309 			case 'IN':

   310 			case 'NOT IN':

   311 				return '(' . implode( ',', array_map( 'intval', (array) $value ) ) . ')';

   312 

   313 			case 'BETWEEN':

   314 			case 'NOT BETWEEN':

   315 				if ( ! is_array( $value ) || 2 != count( $value ) || ! isset( $value[0] ) || ! isset( $value[1] ) )

   316 					$value = array( $value, $value );

   317 

   318 				$value = array_map( 'intval', $value );

   319 

   320 				return $value[0] . ' AND ' . $value[1];

   321 

   322 			default;

   323 				return (int) $value;

   324 		}

   325 	}

   326 

   327 	/**

   328 	 * Builds a MySQL format date/time based on some query parameters.

   329 	 *

   330 	 * You can pass an array of values (year, month, etc.) with missing parameter values being defaulted to

   331 	 * either the maximum or minimum values (controlled by the $default_to parameter). Alternatively you can

   332 	 * pass a string that that will be run through strtotime().

   333 	 *

   334 	 * @since 3.7.0

   335 	 * @access public

   336 	 *

   337 	 * @param string|array $datetime An array of parameters or a strotime() string

   338 	 * @param string $default_to Controls what values default to if they are missing from $datetime. Pass "min" or "max".

   339 	 * @return string|false A MySQL format date/time or false on failure

   340 	 */

   341 	public function build_mysql_datetime( $datetime, $default_to_max = false ) {

   342 		$now = current_time( 'timestamp' );

   343 

   344 		if ( ! is_array( $datetime ) ) {

   345 			// @todo Timezone issues here possibly

   346 			return gmdate( 'Y-m-d H:i:s', strtotime( $datetime, $now ) );

   347 		}

   348 

   349 		$datetime = array_map( 'absint', $datetime );

   350 

   351 		if ( ! isset( $datetime['year'] ) )

   352 			$datetime['year'] = gmdate( 'Y', $now );

   353 

   354 		if ( ! isset( $datetime['month'] ) )

   355 			$datetime['month'] = ( $default_to_max ) ? 12 : 1;

   356 

   357 		if ( ! isset( $datetime['day'] ) )

   358 			$datetime['day'] = ( $default_to_max ) ? (int) date( 't', mktime( 0, 0, 0, $datetime['month'], 1, $datetime['year'] ) ) : 1;

   359 

   360 		if ( ! isset( $datetime['hour'] ) )

   361 			$datetime['hour'] = ( $default_to_max ) ? 23 : 0;

   362 

   363 		if ( ! isset( $datetime['minute'] ) )

   364 			$datetime['minute'] = ( $default_to_max ) ? 59 : 0;

   365 

   366 		if ( ! isset( $datetime['second'] ) )

   367 			$datetime['second'] = ( $default_to_max ) ? 59 : 0;

   368 

   369 		return sprintf( '%04d-%02d-%02d %02d:%02d:%02d', $datetime['year'], $datetime['month'], $datetime['day'], $datetime['hour'], $datetime['minute'], $datetime['second'] );

   370 	}

   371 

   372 	/**

   373 	 * Builds a query string for comparing time values (hour, minute, second).

   374 	 *

   375 	 * If just hour, minute, or second is set than a normal comparison will be done.

   376 	 * However if multiple values are passed, a pseudo-decimal time will be created

   377 	 * in order to be able to accurately compare against.

   378 	 *

   379 	 * @since 3.7.0

   380 	 * @access public

   381 	 *

   382 	 * @param string $column The column to query against. Needs to be pre-validated!

   383 	 * @param string $compare The comparison operator. Needs to be pre-validated!

   384 	 * @param int|null $hour Optional. An hour value (0-23).

   385 	 * @param int|null $minute Optional. A minute value (0-59).

   386 	 * @param int|null $second Optional. A second value (0-59).

   387 	 * @return string|false A query part or false on failure.

   388 	 */

   389 	public function build_time_query( $column, $compare, $hour = null, $minute = null, $second = null ) {

   390 		global $wpdb;

   391 

   392 		// Have to have at least one

   393 		if ( ! isset( $hour ) && ! isset( $minute ) && ! isset( $second ) )

   394 			return false;

   395 

   396 		// Complex combined queries aren't supported for multi-value queries

   397 		if ( in_array( $compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) {

   398 			$return = array();

   399 

   400 			if ( isset( $hour ) && false !== ( $value = $this->build_value( $compare, $hour ) ) )

   401 				$return[] = "HOUR( $column ) $compare $value";

   402 

   403 			if ( isset( $minute ) && false !== ( $value = $this->build_value( $compare, $minute ) ) )

   404 				$return[] = "MINUTE( $column ) $compare $value";

   405 

   406 			if ( isset( $second ) && false !== ( $value = $this->build_value( $compare, $second ) ) )

   407 				$return[] = "SECOND( $column ) $compare $value";

   408 

   409 			return implode( ' AND ', $return );

   410 		}

   411 

   412 		// Cases where just one unit is set

   413 		if ( isset( $hour ) && ! isset( $minute ) && ! isset( $second ) && false !== ( $value = $this->build_value( $compare, $hour ) ) ) {

   414 			return "HOUR( $column ) $compare $value";

   415 		} elseif ( ! isset( $hour ) && isset( $minute ) && ! isset( $second ) && false !== ( $value = $this->build_value( $compare, $minute ) ) ) {

   416 			return "MINUTE( $column ) $compare $value";

   417 		} elseif ( ! isset( $hour ) && ! isset( $minute ) && isset( $second ) && false !== ( $value = $this->build_value( $compare, $second ) ) ) {

   418 			return "SECOND( $column ) $compare $value";

   419 		}

   420 

   421 		// Single units were already handled. Since hour & second isn't allowed, minute must to be set.

   422 		if ( ! isset( $minute ) )

   423 			return false;

   424 

   425 		$format = $time = '';

   426 

   427 		// Hour

   428 		if ( $hour ) {

   429 			$format .= '%H.';

   430 			$time   .= sprintf( '%02d', $hour ) . '.';

   431 		} else {

   432 			$format .= '0.';

   433 			$time   .= '0.';

   434 		}

   435 

   436 		// Minute

   437 		$format .= '%i';

   438 		$time   .= sprintf( '%02d', $minute );

   439 

   440 		if ( isset( $second ) ) {

   441 			$format .= '%s';

   442 			$time   .= sprintf( '%02d', $second );

   443 		}

   444 

   445 		return $wpdb->prepare( "DATE_FORMAT( $column, %s ) $compare %f", $format, $time );

   446 	}

   447 }