Automattic\WooCommerce\Internal\Admin\Orders\MetaBoxes

CustomerHistory::query_hpos │ private │ WC 1.0

Query customer order stats from HPOS tables.

Method of the class: CustomerHistory{}

No Hooks.

Returns

Object. Object with orders_count and total_spend properties.

Usage

// private - for code of main (parent) class only
$result = $this->query_hpos( $customer_id, $billing_email ): object;

$customer_id(int) (required)

The customer user ID.

$billing_email(string) (required)

The billing email address.

CustomerHistory::query_hpos() CustomerHistory::query hpos code ^{WC 10.9.4}

private function query_hpos( int $customer_id, string $billing_email ): object {
	global $wpdb;

	$default = (object) array(
		'orders_count' => 0,
		'total_spend'  => 0,
	);

	$excluded_statuses_sql = $this->get_excluded_statuses_sql();
	$orders_table          = OrdersTableDataStore::get_orders_table_name();

	$sql = null;

	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	if ( $customer_id > 0 ) {
		$status_filter    = $excluded_statuses_sql ? "AND status NOT IN $excluded_statuses_sql" : '';
		$co_status_filter = $excluded_statuses_sql ? "AND co.status NOT IN $excluded_statuses_sql" : '';

		$sql = $wpdb->prepare(
			"SELECT COUNT(*) AS orders_count,
				COALESCE( SUM( filtered.total_amount ), 0 ) + COALESCE( SUM( r.refund_total ), 0 ) AS total_spend
			FROM (
				SELECT id, total_amount
				FROM %i
				WHERE customer_id = %d AND type = 'shop_order' $status_filter
			) AS filtered
			LEFT JOIN (
				SELECT rp.parent_order_id, SUM( rp.total_amount ) AS refund_total
				FROM %i AS rp
				INNER JOIN %i AS co ON rp.parent_order_id = co.id
				WHERE rp.type = 'shop_order_refund'
					AND co.customer_id = %d AND co.type = 'shop_order' $co_status_filter
				GROUP BY rp.parent_order_id
			) AS r ON filtered.id = r.parent_order_id",
			$orders_table,
			$customer_id,
			$orders_table,
			$orders_table,
			$customer_id
		);
	} elseif ( '' !== $billing_email ) {
		$addresses_table  = OrdersTableDataStore::get_addresses_table_name();
		$o_status_filter  = $excluded_statuses_sql ? "AND o.status NOT IN $excluded_statuses_sql" : '';
		$co_status_filter = $excluded_statuses_sql ? "AND co.status NOT IN $excluded_statuses_sql" : '';

		$sql = $wpdb->prepare(
			"SELECT COUNT(*) AS orders_count,
				COALESCE( SUM( filtered.total_amount ), 0 ) + COALESCE( SUM( r.refund_total ), 0 ) AS total_spend
			FROM (
				SELECT o.id, o.total_amount
				FROM %i AS o
				INNER JOIN %i AS a ON o.id = a.order_id AND a.address_type = 'billing'
				WHERE o.customer_id = 0 AND a.email = %s AND o.type = 'shop_order' $o_status_filter
			) AS filtered
			LEFT JOIN (
				SELECT rp.parent_order_id, SUM( rp.total_amount ) AS refund_total
				FROM %i AS rp
				INNER JOIN %i AS co ON rp.parent_order_id = co.id
				INNER JOIN %i AS ca ON co.id = ca.order_id AND ca.address_type = 'billing'
				WHERE rp.type = 'shop_order_refund'
					AND co.customer_id = 0 AND ca.email = %s AND co.type = 'shop_order' $co_status_filter
				GROUP BY rp.parent_order_id
			) AS r ON filtered.id = r.parent_order_id",
			$orders_table,
			$addresses_table,
			$billing_email,
			$orders_table,
			$orders_table,
			$addresses_table,
			$billing_email
		);
	}
	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

	if ( null === $sql ) {
		return $default;
	}

	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is prepared above.
	$row = $wpdb->get_row( $sql );

	if ( $wpdb->last_error ) {
		wc_get_logger()->error(
			sprintf( 'CustomerHistory: Failed to query HPOS order stats for customer_id=%d. DB error: %s', $customer_id, $wpdb->last_error ),
			array( 'source' => 'customer-history' )
		);
	}

	return $row ?? $default;
}