Automattic\WooCommerce\Internal\Admin\Reports

HposLegacyOrderReportQueryBuilder::build_transition_fallback_expr │ private │ WC 1.0

Build the fallback used when CONVERT_TZ() can't resolve the named timezone (MySQL timezone tables not loaded).

A single shift by the current gmt_offset would be wrong for rows created in a different DST period, so the site timezone's DST transitions inside the report window are baked into a CASE expression and each row is shifted by the offset in effect when it was created. Without a caller-supplied range (e.g. the sparklines) the window covers the last year, which spans any window such callers use.

Method of the class: HposLegacyOrderReportQueryBuilder{}

No Hooks.

Returns

string. SQL fragment that produces a local DATETIME.

Usage

// private - for code of main (parent) class only
$result = $this->build_transition_fallback_expr(): string;

HposLegacyOrderReportQueryBuilder::build_transition_fallback_expr() HposLegacyOrderReportQueryBuilder::build transition fallback expr code ^{WC 11.1.2}

private function build_transition_fallback_expr(): string {
	list( $window_start, $window_end ) = $this->report_range ?? array( time() - YEAR_IN_SECONDS, time() + DAY_IN_SECONDS );

	$transitions = wp_timezone()->getTransitions( $window_start, $window_end );
	if ( ! is_array( $transitions ) || array() === $transitions ) {
		$schema = $this->get_report_schema();
		return "DATE_ADD(orders.date_created_gmt, INTERVAL {$schema['gmt_offset']} SECOND)";
	}

	// The first entry describes the offset already in effect at the window start;
	// each later entry is a transition inside the window.
	$case  = '';
	$count = count( $transitions );
	for ( $i = 0; $i < $count - 1; $i++ ) {
		$boundary = gmdate( 'Y-m-d H:i:s', $transitions[ $i + 1 ]['ts'] );
		$offset   = (int) $transitions[ $i ]['offset'];
		$case    .= "WHEN orders.date_created_gmt < '{$boundary}' THEN DATE_ADD(orders.date_created_gmt, INTERVAL {$offset} SECOND) ";
	}

	$last_offset = (int) $transitions[ $count - 1 ]['offset'];
	$last_shift  = "DATE_ADD(orders.date_created_gmt, INTERVAL {$last_offset} SECOND)";

	if ( '' === $case ) {
		return $last_shift;
	}

	return "CASE {$case}ELSE {$last_shift} END";
}