Automattic\WooCommerce\Admin\API\Reports\Customers

DataStore::add_sql_query_params() protected WC 1.0

Updates the database query with parameters used for Customers report: categories and order status.

{} It's a method of the class: DataStore{}

No Hooks.

Return

null. Nothing.

Usage

// protected - for code of main (parent) or child class
$result = $this->add_sql_query_params( $query_args );

$query_args(array) (required)

Query arguments supplied by the user.

Code of DataStore::add_sql_query_params() DataStore::add sql query params ^{WC 6.1.0}

protected function add_sql_query_params( $query_args ) {
	global $wpdb;
	$customer_lookup_table  = self::get_db_table_name();
	$order_stats_table_name = $wpdb->prefix . 'wc_order_stats';

	$this->add_time_period_sql_params( $query_args, $customer_lookup_table );
	$this->get_limit_sql_params( $query_args );
	$this->add_order_by_sql_params( $query_args );
	$this->subquery->add_sql_clause( 'left_join', "LEFT JOIN {$order_stats_table_name} ON {$customer_lookup_table}.customer_id = {$order_stats_table_name}.customer_id" );

	$match_operator = $this->get_match_operator( $query_args );
	$where_clauses  = array();
	$having_clauses = array();

	$exact_match_params = array(
		'name',
		'username',
		'email',
		'country',
	);

	foreach ( $exact_match_params as $exact_match_param ) {
		if ( ! empty( $query_args[ $exact_match_param . '_includes' ] ) ) {
			$exact_match_arguments         = $query_args[ $exact_match_param . '_includes' ];
			$exact_match_arguments_escaped = array_map( 'esc_sql', explode( ',', $exact_match_arguments ) );
			$included                      = implode( "','", $exact_match_arguments_escaped );
			// 'country_includes' is a list of country codes, the others will be a list of customer ids.
			$table_column    = 'country' === $exact_match_param ? $exact_match_param : 'customer_id';
			$where_clauses[] = "{$customer_lookup_table}.{$table_column} IN ('{$included}')";
		}

		if ( ! empty( $query_args[ $exact_match_param . '_excludes' ] ) ) {
			$exact_match_arguments         = $query_args[ $exact_match_param . '_excludes' ];
			$exact_match_arguments_escaped = array_map( 'esc_sql', explode( ',', $exact_match_arguments ) );
			$excluded                      = implode( "','", $exact_match_arguments_escaped );
			// 'country_includes' is a list of country codes, the others will be a list of customer ids.
			$table_column    = 'country' === $exact_match_param ? $exact_match_param : 'customer_id';
			$where_clauses[] = "{$customer_lookup_table}.{$table_column} NOT IN ('{$excluded}')";
		}
	}

	$search_params = array(
		'name',
		'username',
		'email',
	);

	if ( ! empty( $query_args['search'] ) ) {
		$name_like = '%' . $wpdb->esc_like( $query_args['search'] ) . '%';

		if ( empty( $query_args['searchby'] ) || 'name' === $query_args['searchby'] || ! in_array( $query_args['searchby'], $search_params, true ) ) {
			$searchby = "CONCAT_WS( ' ', first_name, last_name )";
		} else {
			$searchby = $query_args['searchby'];
		}

		$where_clauses[] = $wpdb->prepare( "{$searchby} LIKE %s", $name_like ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	// Allow a list of customer IDs to be specified.
	if ( ! empty( $query_args['customers'] ) ) {
		$included_customers = $this->get_filtered_ids( $query_args, 'customers' );
		$where_clauses[]    = "{$customer_lookup_table}.customer_id IN ({$included_customers})";
	}

	$numeric_params = array(
		'orders_count'    => array(
			'column' => 'COUNT( order_id )',
			'format' => '%d',
		),
		'total_spend'     => array(
			'column' => 'SUM( total_sales )',
			'format' => '%f',
		),
		'avg_order_value' => array(
			'column' => '( SUM( total_sales ) / COUNT( order_id ) )',
			'format' => '%f',
		),
	);

	foreach ( $numeric_params as $numeric_param => $param_info ) {
		$subclauses = array();
		$min_param  = $numeric_param . '_min';
		$max_param  = $numeric_param . '_max';
		$or_equal   = isset( $query_args[ $min_param ] ) && isset( $query_args[ $max_param ] ) ? '=' : '';

		if ( isset( $query_args[ $min_param ] ) ) {
			$subclauses[] = $wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
				"{$param_info['column']} >{$or_equal} {$param_info['format']}",
				$query_args[ $min_param ]
			);
		}

		if ( isset( $query_args[ $max_param ] ) ) {
			$subclauses[] = $wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
				"{$param_info['column']} <{$or_equal} {$param_info['format']}",
				$query_args[ $max_param ]
			);
		}

		if ( $subclauses ) {
			$having_clauses[] = '(' . implode( ' AND ', $subclauses ) . ')';
		}
	}

	if ( $where_clauses ) {
		$preceding_match = empty( $this->get_sql_clause( 'where_time' ) ) ? ' AND ' : " {$match_operator} ";
		$this->subquery->add_sql_clause( 'where', $preceding_match . implode( " {$match_operator} ", $where_clauses ) );
	}

	$order_status_filter = $this->get_status_subquery( $query_args );
	if ( $order_status_filter ) {
		$this->subquery->add_sql_clause( 'left_join', "AND ( {$order_status_filter} )" );
	}

	if ( $having_clauses ) {
		$preceding_match = empty( $this->get_sql_clause( 'having' ) ) ? ' AND ' : " {$match_operator} ";
		$this->subquery->add_sql_clause( 'having', $preceding_match . implode( " {$match_operator} ", $having_clauses ) );
	}
}