Automattic\WooCommerce\Admin\API\Reports

DataStore::update_intervals_sql_params() │ protected │ WC 1.0

Updates the LIMIT query part for Intervals query of the report.

If there are less records in the database than time intervals, then we need to remap offset in SQL query to fetch correct records.

Method of the class: DataStore{}

No Hooks.

Return

null. Nothing (null).

Usage

// protected - for code of main (parent) or child class
$result = $this->update_intervals_sql_params( $query_args, $db_interval_count, $expected_interval_count, $table_name );

$query_args(array) (required) (passed by reference — &)

Query arguments.

$db_interval_count(int) (required)

Database interval count.

$expected_interval_count(int) (required)

Expected interval count on the output.

$table_name(string) (required)

Name of the db table relevant for the date constraint.

DataStore::update_intervals_sql_params() DataStore::update intervals sql params code ^{WC 8.7.0}

protected function update_intervals_sql_params( &$query_args, $db_interval_count, $expected_interval_count, $table_name ) {
	if ( $db_interval_count === $expected_interval_count ) {
		return;
	}

	$params   = $this->get_limit_params( $query_args );
	$local_tz = new \DateTimeZone( wc_timezone_string() );
	if ( 'date' === strtolower( $query_args['orderby'] ) ) {
		// page X in request translates to slightly different dates in the db, in case some
		// records are missing from the db.
		$start_iteration = 0;
		$end_iteration   = 0;
		if ( 'asc' === strtolower( $query_args['order'] ) ) {
			// ORDER BY date ASC.
			$new_start_date    = $query_args['after'];
			$intervals_to_skip = ( $query_args['page'] - 1 ) * $params['per_page'];
			$latest_end_date   = $query_args['before'];
			for ( $i = 0; $i < $intervals_to_skip; $i++ ) {
				if ( $new_start_date > $latest_end_date ) {
					$new_start_date  = $latest_end_date;
					$start_iteration = 0;
					break;
				}
				$new_start_date = TimeInterval::iterate( $new_start_date, $query_args['interval'] );
				$start_iteration ++;
			}

			$new_end_date = clone $new_start_date;
			for ( $i = 0; $i < $params['per_page']; $i++ ) {
				if ( $new_end_date > $latest_end_date ) {
					break;
				}
				$new_end_date = TimeInterval::iterate( $new_end_date, $query_args['interval'] );
				$end_iteration ++;
			}
			if ( $new_end_date > $latest_end_date ) {
				$new_end_date  = $latest_end_date;
				$end_iteration = 0;
			}
			if ( $end_iteration ) {
				$new_end_date_timestamp = (int) $new_end_date->format( 'U' ) - 1;
				$new_end_date->setTimestamp( $new_end_date_timestamp );
			}
		} else {
			// ORDER BY date DESC.
			$new_end_date        = $query_args['before'];
			$intervals_to_skip   = ( $query_args['page'] - 1 ) * $params['per_page'];
			$earliest_start_date = $query_args['after'];
			for ( $i = 0; $i < $intervals_to_skip; $i++ ) {
				if ( $new_end_date < $earliest_start_date ) {
					$new_end_date  = $earliest_start_date;
					$end_iteration = 0;
					break;
				}
				$new_end_date = TimeInterval::iterate( $new_end_date, $query_args['interval'], true );
				$end_iteration ++;
			}

			$new_start_date = clone $new_end_date;
			for ( $i = 0; $i < $params['per_page']; $i++ ) {
				if ( $new_start_date < $earliest_start_date ) {
					break;
				}
				$new_start_date = TimeInterval::iterate( $new_start_date, $query_args['interval'], true );
				$start_iteration ++;
			}
			if ( $new_start_date < $earliest_start_date ) {
				$new_start_date  = $earliest_start_date;
				$start_iteration = 0;
			}
			if ( $start_iteration ) {
				// @todo Is this correct? should it only be added if iterate runs? other two iterate instances, too?
				$new_start_date_timestamp = (int) $new_start_date->format( 'U' ) + 1;
				$new_start_date->setTimestamp( $new_start_date_timestamp );
			}
		}
		// @todo - Do this without modifying $query_args?
		$query_args['adj_after']  = $new_start_date;
		$query_args['adj_before'] = $new_end_date;
		$adj_after                = $new_start_date->format( TimeInterval::$sql_datetime_format );
		$adj_before               = $new_end_date->format( TimeInterval::$sql_datetime_format );
		$this->interval_query->clear_sql_clause( array( 'where_time', 'limit' ) );
		$this->interval_query->add_sql_clause( 'where_time', "AND {$table_name}.`{$this->date_column_name}` <= '$adj_before'" );
		$this->interval_query->add_sql_clause( 'where_time', "AND {$table_name}.`{$this->date_column_name}` >= '$adj_after'" );
		$this->clear_sql_clause( 'limit' );
		$this->add_sql_clause( 'limit', 'LIMIT 0,' . $params['per_page'] );
	} else {
		if ( 'asc' === $query_args['order'] ) {
			$offset = ( ( $query_args['page'] - 1 ) * $params['per_page'] ) - ( $expected_interval_count - $db_interval_count );
			$offset = $offset < 0 ? 0 : $offset;
			$count  = $query_args['page'] * $params['per_page'] - ( $expected_interval_count - $db_interval_count );
			if ( $count < 0 ) {
				$count = 0;
			} elseif ( $count > $params['per_page'] ) {
				$count = $params['per_page'];
			}

			$this->clear_sql_clause( 'limit' );
			$this->add_sql_clause( 'limit', 'LIMIT ' . $offset . ',' . $count );
		}
		// Otherwise no change in limit clause.
		// @todo - Do this without modifying $query_args?
		$query_args['adj_after']  = $query_args['after'];
		$query_args['adj_before'] = $query_args['before'];
	}
}