'where_clause'] .= 'parent_id != 0'; } if ( 'none' === $query_args['refunds'] ) { $sql_query['where_clause'] .= 'parent_id = 0'; } if ( 'full' === $query_args['refunds'] || 'partial' === $query_args['refunds'] ) { $operator = 'full' === $query_args['refunds'] ? '=' : '!='; $sql_query['from_clause'] .= " JOIN {$table_name} parent_order_stats ON {$table_name}.parent_id = parent_order_stats.order_id"; $sql_query['where_clause'] .= "parent_order_stats.status {$operator} '{$this->normalize_order_status( 'refunded' )}'"; } return $sql_query; } /** * Returns an array of products belonging to given categories. * * @param array $categories List of categories IDs. * @return array|stdClass */ protected function get_products_by_cat_ids( $categories ) { $terms = get_terms( array( 'taxonomy' => 'product_cat', 'include' => $categories, ) ); if ( is_wp_error( $terms ) || empty( $terms ) ) { return array(); } $args = array( 'category' => wc_list_pluck( $terms, 'slug' ), 'limit' => -1, 'return' => 'ids', ); return wc_get_products( $args ); } /** * Get WHERE filter by object ids subquery. * * @param string $select_table Select table name. * @param string $select_field Select table object ID field name. * @param string $filter_table Lookup table name. * @param string $filter_field Lookup table object ID field name. * @param string $compare Comparison string (IN|NOT IN). * @param string $id_list Comma separated ID list. * * @return string */ protected function get_object_where_filter( $select_table, $select_field, $filter_table, $filter_field, $compare, $id_list ) { global $wpdb; if ( empty( $id_list ) ) { return ''; } $lookup_name = isset( $wpdb->$filter_table ) ? $wpdb->$filter_table : $wpdb->prefix . $filter_table; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared return " {$select_table}.{$select_field} {$compare} ( SELECT DISTINCT {$filter_table}.{$select_field} FROM {$filter_table} WHERE {$filter_table}.{$filter_field} IN ({$id_list}) )"; // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared } /** * Returns an array of ids of allowed products, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return array */ protected function get_included_products_array( $query_args ) { $included_products = array(); $operator = $this->get_match_operator( $query_args ); if ( isset( $query_args['category_includes'] ) && is_array( $query_args['category_includes'] ) && count( $query_args['category_includes'] ) > 0 ) { $included_products = $this->get_products_by_cat_ids( $query_args['category_includes'] ); // If no products were found in the specified categories, we will force an empty set // by matching a product ID of -1, unless the filters are OR/any and products are specified. if ( empty( $included_products ) ) { $included_products = array( '-1' ); } } if ( isset( $query_args['product_includes'] ) && is_array( $query_args['product_includes'] ) && count( $query_args['product_includes'] ) > 0 ) { if ( count( $included_products ) > 0 ) { if ( 'AND' === $operator ) { // AND results in an intersection between products from selected categories and manually included products. $included_products = array_intersect( $included_products, $query_args['product_includes'] ); } elseif ( 'OR' === $operator ) { // OR results in a union of products from selected categories and manually included products. $included_products = array_merge( $included_products, $query_args['product_includes'] ); } } else { $included_products = $query_args['product_includes']; } } return $included_products; } /** * Returns comma separated ids of allowed products, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return string */ protected function get_included_products( $query_args ) { $included_products = $this->get_included_products_array( $query_args ); return implode( ',', $included_products ); } /** * Returns comma separated ids of allowed variations, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return string */ protected function get_included_variations( $query_args ) { return $this->get_filtered_ids( $query_args, 'variation_includes' ); } /** * Returns comma separated ids of excluded variations, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return string */ protected function get_excluded_variations( $query_args ) { return $this->get_filtered_ids( $query_args, 'variation_excludes' ); } /** * Returns an array of ids of disallowed products, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return array */ protected function get_excluded_products_array( $query_args ) { $excluded_products = array(); $operator = $this->get_match_operator( $query_args ); if ( isset( $query_args['category_excludes'] ) && is_array( $query_args['category_excludes'] ) && count( $query_args['category_excludes'] ) > 0 ) { $excluded_products = $this->get_products_by_cat_ids( $query_args['category_excludes'] ); } if ( isset( $query_args['product_excludes'] ) && is_array( $query_args['product_excludes'] ) && count( $query_args['product_excludes'] ) > 0 ) { $excluded_products = array_merge( $excluded_products, $query_args['product_excludes'] ); } return $excluded_products; } /** * Returns comma separated ids of excluded products, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return string */ protected function get_excluded_products( $query_args ) { $excluded_products = $this->get_excluded_products_array( $query_args ); return implode( ',', $excluded_products ); } /** * Returns comma separated ids of included categories, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return string */ protected function get_included_categories( $query_args ) { return $this->get_filtered_ids( $query_args, 'category_includes' ); } /** * Returns comma separated ids of included coupons, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @param string $field Field name in the parameter list. * @return string */ protected function get_included_coupons( $query_args, $field = 'coupon_includes' ) { return $this->get_filtered_ids( $query_args, $field ); } /** * Returns comma separated ids of excluded coupons, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return string */ protected function get_excluded_coupons( $query_args ) { return $this->get_filtered_ids( $query_args, 'coupon_excludes' ); } /** * Returns comma separated ids of included orders, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return string */ protected function get_included_orders( $query_args ) { return $this->get_filtered_ids( $query_args, 'order_includes' ); } /** * Returns comma separated ids of excluded orders, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return string */ protected function get_excluded_orders( $query_args ) { return $this->get_filtered_ids( $query_args, 'order_excludes' ); } /** * Returns comma separated ids of included users, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return string */ protected function get_included_users( $query_args ) { return $this->get_filtered_ids( $query_args, 'user_includes' ); } /** * Returns comma separated ids of excluded users, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return string */ protected function get_excluded_users( $query_args ) { return $this->get_filtered_ids( $query_args, 'user_excludes' ); } /** * Returns order status subquery to be used in WHERE SQL query, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @param string $operator AND or OR, based on match query argument. * @return string */ protected function get_status_subquery( $query_args, $operator = 'AND' ) { global $wpdb; $subqueries = array(); $excluded_statuses = array(); if ( isset( $query_args['status_is'] ) && is_array( $query_args['status_is'] ) && count( $query_args['status_is'] ) > 0 ) { $allowed_statuses = array_map( array( $this, 'normalize_order_status' ), esc_sql( $query_args['status_is'] ) ); if ( $allowed_statuses ) { $subqueries[] = "{$wpdb->prefix}wc_order_stats.status IN ( '" . implode( "','", $allowed_statuses ) . "' )"; } } if ( isset( $query_args['status_is_not'] ) && is_array( $query_args['status_is_not'] ) && count( $query_args['status_is_not'] ) > 0 ) { $excluded_statuses = array_map( array( $this, 'normalize_order_status' ), $query_args['status_is_not'] ); } if ( ( ! isset( $query_args['status_is'] ) || empty( $query_args['status_is'] ) ) && ( ! isset( $query_args['status_is_not'] ) || empty( $query_args['status_is_not'] ) ) ) { $excluded_statuses = array_map( array( $this, 'normalize_order_status' ), $this->get_excluded_report_order_statuses() ); } if ( $excluded_statuses ) { $subqueries[] = "{$wpdb->prefix}wc_order_stats.status NOT IN ( '" . implode( "','", $excluded_statuses ) . "' )"; } return implode( " $operator ", $subqueries ); } /** * Add order status SQL clauses if included in query. * * @param array $query_args Parameters supplied by the user. * @param string $table_name Database table name. * @param SqlQuery $sql_query Query object. */ protected function add_order_status_clause( $query_args, $table_name, &$sql_query ) { global $wpdb; $order_status_filter = $this->get_status_subquery( $query_args ); if ( $order_status_filter ) { $sql_query->add_sql_clause( 'join', "JOIN {$wpdb->prefix}wc_order_stats ON {$table_name}.order_id = {$wpdb->prefix}wc_order_stats.order_id" ); $sql_query->add_sql_clause( 'where', "AND ( {$order_status_filter} )" ); } } /** * Add order by SQL clause if included in query. * * @param array $query_args Parameters supplied by the user. * @param SqlQuery $sql_query Query object. * @return string Order by clause. */ protected function add_order_by_clause( $query_args, &$sql_query ) { $order_by_clause = ''; $sql_query->clear_sql_clause( array( 'order_by' ) ); if ( isset( $query_args['orderby'] ) ) { $order_by_clause = $this->normalize_order_by( esc_sql( $query_args['orderby'] ) ); $sql_query->add_sql_clause( 'order_by', $order_by_clause ); } // Return ORDER BY clause to allow adding the sort field(s) to query via a JOIN. return $order_by_clause; } /** * Add order by order SQL clause. * * @param array $query_args Parameters supplied by the user. * @param SqlQuery $sql_query Query object. */ protected function add_orderby_order_clause( $query_args, &$sql_query ) { if ( isset( $query_args['order'] ) ) { $sql_query->add_sql_clause( 'order_by', esc_sql( $query_args['order'] ) ); } else { $sql_query->add_sql_clause( 'order_by', 'DESC' ); } } /** * Returns customer subquery to be used in WHERE SQL query, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @return string */ protected function get_customer_subquery( $query_args ) { global $wpdb; $customer_filter = ''; if ( isset( $query_args['customer_type'] ) ) { if ( 'new' === strtolower( $query_args['customer_type'] ) ) { $customer_filter = " {$wpdb->prefix}wc_order_stats.returning_customer = 0"; } elseif ( 'returning' === strtolower( $query_args['customer_type'] ) ) { $customer_filter = " {$wpdb->prefix}wc_order_stats.returning_customer = 1"; } } return $customer_filter; } /** * Returns product attribute subquery elements used in JOIN and WHERE clauses, * based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @param string $table_name Database table name. * @return array */ protected function get_attribute_subqueries( $query_args, $table_name ) { global $wpdb; $sql_clauses = array( 'join' => array(), 'where' => array(), ); $match_operator = $this->get_match_operator( $query_args ); $post_meta_comparators = array( '=' => 'attribute_is', '!=' => 'attribute_is_not', ); foreach ( $post_meta_comparators as $comparator => $arg ) { if ( ! isset( $query_args[ $arg ] ) || ! is_array( $query_args[ $arg ] ) ) { continue; } foreach ( $query_args[ $arg ] as $attribute_term ) { // We expect tuples. if ( ! is_array( $attribute_term ) || 2 !== count( $attribute_term ) ) { continue; } // If the tuple is numeric, assume these are IDs. if ( is_numeric( $attribute_term[0] ) && is_numeric( $attribute_term[1] ) ) { $attribute_id = intval( $attribute_term[0] ); $term_id = intval( $attribute_term[1] ); // Invalid IDs. if ( 0 === $attribute_id || 0 === $term_id ) { continue; } // @todo: Use wc_get_attribute () instead ? $attr_taxonomy = wc_attribute_taxonomy_name_by_id( $attribute_id ); // Invalid attribute ID. if ( empty( $attr_taxonomy ) ) { continue; } $attr_term = get_term_by( 'id', $term_id, $attr_taxonomy ); // Invalid term ID. if ( false === $attr_term ) { continue; } $meta_key = sanitize_title( $attr_taxonomy ); $meta_value = $attr_term->slug; } else { // Assume these are a custom attribute slug/value pair. $meta_key = esc_sql( $attribute_term[0] ); $meta_value = esc_sql( $attribute_term[1] ); } $join_alias = 'orderitemmeta1'; if ( empty( $sql_clauses['join'] ) ) { $table_name = esc_sql( $table_name ); $sql_clauses['join'][] = "JOIN {$wpdb->prefix}woocommerce_order_items orderitems ON orderitems.order_id = {$table_name}.order_id"; } // If we're matching all filters (AND), we'll need multiple JOINs on postmeta. // If not, just one. if ( 'AND' === $match_operator || 1 === count( $sql_clauses['join'] ) ) { $join_idx = count( $sql_clauses['join'] ); $join_alias = 'orderitemmeta' . $join_idx; $sql_clauses['join'][] = "JOIN {$wpdb->prefix}woocommerce_order_itemmeta as {$join_alias} ON {$join_alias}.order_item_id = orderitems.order_item_id"; } // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $sql_clauses['where'][] = $wpdb->prepare( "( {$join_alias}.meta_key = %s AND {$join_alias}.meta_value {$comparator} %s )", $meta_key, $meta_value ); } } // If we're matching multiple attributes and all filters (AND), make sure // we're matching attributes on the same product. $num_attribute_filters = count( $sql_clauses['join'] ); for ( $i = 2; $i < $num_attribute_filters; $i++ ) { $join_alias = 'orderitemmeta' . $i; $sql_clauses['join'][] = "AND orderitemmeta1.order_item_id = {$join_alias}.order_item_id"; } return $sql_clauses; } /** * Returns logic operator for WHERE subclause based on 'match' query argument. * * @param array $query_args Parameters supplied by the user. * @return string */ protected function get_match_operator( $query_args ) { $operator = 'AND'; if ( ! isset( $query_args['match'] ) ) { return $operator; } if ( 'all' === strtolower( $query_args['match'] ) ) { $operator = 'AND'; } elseif ( 'any' === strtolower( $query_args['match'] ) ) { $operator = 'OR'; } return $operator; } /** * Returns filtered comma separated ids, based on query arguments from the user. * * @param array $query_args Parameters supplied by the user. * @param string $field Query field to filter. * @param string $separator Field separator. * @return string */ protected function get_filtered_ids( $query_args, $field, $separator = ',' ) { global $wpdb; $ids_str = ''; $ids = isset( $query_args[ $field ] ) && is_array( $query_args[ $field ] ) ? $query_args[ $field ] : array(); /** * Filter the IDs before retrieving report data. * * Allows filtering of the objects included or excluded from reports. * * @param array $ids List of object Ids. * @param array $query_args The original arguments for the request. * @param string $field The object type. * @param string $context The data store context. */ $ids = apply_filters( 'woocommerce_analytics_' . $field, $ids, $query_args, $field, $this->context ); if ( ! empty( $ids ) ) { $placeholders = implode( $separator, array_fill( 0, count( $ids ), '%d' ) ); /* phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared */ $ids_str = $wpdb->prepare( "{$placeholders}", $ids ); /* phpcs:enable */ } return $ids_str; } /** * Assign report columns once full table name has been assigned. */ protected function assign_report_columns() {} }