ent intervals' borders, not times when data in db were recorded. * * E.g. if there are db records for only Tuesday and Thursday this week, the actual week interval is [Mon, Sun], not [Tue, Thu]. * * @param DateTime $start_datetime Start date. * @param DateTime $end_datetime End date. * @param string $time_interval Time interval, e.g. day, week, month. * @param array $intervals Array of intervals extracted from SQL db. */ protected function update_interval_boundary_dates( $start_datetime, $end_datetime, $time_interval, &$intervals ) { $local_tz = new \DateTimeZone( wc_timezone_string() ); foreach ( $intervals as $key => $interval ) { $datetime = new \DateTime( $interval['datetime_anchor'], $local_tz ); $prev_start = TimeInterval::iterate( $datetime, $time_interval, true ); // @todo Not sure if the +1/-1 here are correct, especially as they are applied before the ?: below. $prev_start_timestamp = (int) $prev_start->format( 'U' ) + 1; $prev_start->setTimestamp( $prev_start_timestamp ); if ( $start_datetime ) { $date_start = $prev_start < $start_datetime ? $start_datetime : $prev_start; $intervals[ $key ]['date_start'] = $date_start->format( 'Y-m-d H:i:s' ); } else { $intervals[ $key ]['date_start'] = $prev_start->format( 'Y-m-d H:i:s' ); } $next_end = TimeInterval::iterate( $datetime, $time_interval ); $next_end_timestamp = (int) $next_end->format( 'U' ) - 1; $next_end->setTimestamp( $next_end_timestamp ); if ( $end_datetime ) { $date_end = $next_end > $end_datetime ? $end_datetime : $next_end; $intervals[ $key ]['date_end'] = $date_end->format( 'Y-m-d H:i:s' ); } else { $intervals[ $key ]['date_end'] = $next_end->format( 'Y-m-d H:i:s' ); } $intervals[ $key ]['interval'] = $time_interval; } } /** * Change structure of intervals to form a correct response. * * Also converts local datetimes to GMT and adds them to the intervals. * * @param array $intervals Time interval, e.g. day, week, month. */ protected function create_interval_subtotals( &$intervals ) { foreach ( $intervals as $key => $interval ) { $start_gmt = TimeInterval::convert_local_datetime_to_gmt( $interval['date_start'] ); $end_gmt = TimeInterval::convert_local_datetime_to_gmt( $interval['date_end'] ); // Move intervals result to subtotals object. $intervals[ $key ] = array( 'interval' => $interval['time_interval'], 'date_start' => $interval['date_start'], 'date_start_gmt' => $start_gmt->format( TimeInterval::$sql_datetime_format ), 'date_end' => $interval['date_end'], 'date_end_gmt' => $end_gmt->format( TimeInterval::$sql_datetime_format ), ); unset( $interval['interval'] ); unset( $interval['date_start'] ); unset( $interval['date_end'] ); unset( $interval['datetime_anchor'] ); unset( $interval['time_interval'] ); $intervals[ $key ]['subtotals'] = (object) $this->cast_numbers( $interval ); } } /** * Fills WHERE clause of SQL request with date-related constraints. * * @param array $query_args Parameters supplied by the user. * @param string $table_name Name of the db table relevant for the date constraint. */ protected function add_time_period_sql_params( $query_args, $table_name ) { $this->clear_sql_clause( array( 'from', 'where_time', 'where' ) ); if ( isset( $this->subquery ) ) { $this->subquery->clear_sql_clause( 'where_time' ); } if ( isset( $query_args['before'] ) && '' !== $query_args['before'] ) { if ( is_a( $query_args['before'], 'WC_DateTime' ) ) { $datetime_str = $query_args['before']->date( TimeInterval::$sql_datetime_format ); } else { $datetime_str = $query_args['before']->format( TimeInterval::$sql_datetime_format ); } if ( isset( $this->subquery ) ) { $this->subquery->add_sql_clause( 'where_time', "AND {$table_name}.date_created <= '$datetime_str'" ); } else { $this->add_sql_clause( 'where_time', "AND {$table_name}.date_created <= '$datetime_str'" ); } } if ( isset( $query_args['after'] ) && '' !== $query_args['after'] ) { if ( is_a( $query_args['after'], 'WC_DateTime' ) ) { $datetime_str = $query_args['after']->date( TimeInterval::$sql_datetime_format ); } else { $datetime_str = $query_args['after']->format( TimeInterval::$sql_datetime_format ); } if ( isset( $this->subquery ) ) { $this->subquery->add_sql_clause( 'where_time', "AND {$table_name}.date_created >= '$datetime_str'" ); } else { $this->add_sql_clause( 'where_time', "AND {$table_name}.date_created >= '$datetime_str'" ); } } } /** * Fills LIMIT clause of SQL request based on user supplied parameters. * * @param array $query_args Parameters supplied by the user. * @return array */ protected function get_limit_sql_params( $query_args ) { $params = $this->get_limit_params( $query_args ); $this->clear_sql_clause( 'limit' ); $this->add_sql_clause( 'limit', "LIMIT {$params['offset']}, {$params['per_page']}" ); return $params; } /** * Fills LIMIT parameters of SQL request based on user supplied parameters. * * @param array $query_args Parameters supplied by the user. * @return array */ protected function get_limit_params( $query_args = array() ) { if ( isset( $query_args['per_page'] ) && is_numeric( $query_args['per_page'] ) ) { $this->limit_parameters['per_page'] = (int) $query_args['per_page']; } else { $this->limit_parameters['per_page'] = get_option( 'posts_per_page' ); } $this->limit_parameters['offset'] = 0; if ( isset( $query_args['page'] ) ) { $this->limit_parameters['offset'] = ( (int) $query_args['page'] - 1 ) * $this->limit_parameters['per_page']; } return $this->limit_parameters; } /** * Generates a virtual table given a list of IDs. * * @param array $ids Array of IDs. * @param array $id_field Name of the ID field. * @param array $other_values Other values that must be contained in the virtual table. * @return array */ protected function get_ids_table( $ids, $id_field, $other_values = array() ) { $selects = array(); foreach ( $ids as $id ) { $new_select = "SELECT {$id} AS {$id_field}"; foreach ( $other_values as $key => $value ) { $new_select .= ", {$value} AS {$key}"; } array_push( $selects, $new_select ); } return join( ' UNION ', $selects ); } /** * Returns a comma separated list of the fields in the `query_args`, if there aren't, returns `report_columns` keys. * * @param array $query_args Parameters supplied by the user. * @return array */ protected function get_fields( $query_args ) { if ( isset( $query_args['fields'] ) && is_array( $query_args['fields'] ) ) { return $query_args['fields']; } return array_keys( $this->report_columns ); } /** * Returns a comma separated list of the field names prepared to be used for a selection after a join with `default_results`. * * @param array $fields Array of fields name. * @param array $default_results_fields Fields to load from `default_results` table. * @param array $outer_selections Array of fields that are not selected in the inner query. * @return string */ protected function format_join_selections( $fields, $default_results_fields, $outer_selections = array() ) { foreach ( $fields as $i => $field ) { foreach ( $default_results_fields as $default_results_field ) { if ( $field === $default_results_field ) { $fields[ $i ] = "default_results.{$field} AS {$field}"; } } if ( in_array( $field, $outer_selections, true ) && array_key_exists( $field, $this->report_columns ) ) { $fields[ $i ] = $this->report_columns[ $field ]; } } return implode( ', ', $fields ); } /** * Fills ORDER BY clause of SQL request based on user supplied parameters. * * @param array $query_args Parameters supplied by the user. */ protected function add_order_by_sql_params( $query_args ) { if ( isset( $query_args['orderby'] ) ) { $order_by_clause = $this->normalize_order_by( $query_args['orderby'] ); } else { $order_by_clause = ''; } $this->clear_sql_clause( 'order_by' ); $this->add_sql_clause( 'order_by', $order_by_clause ); $this->add_orderby_order_clause( $query_args, $this ); } /** * Fills FROM and WHERE clauses of SQL request for 'Intervals' section of data response based on user supplied parameters. * * @param array $query_args Parameters supplied by the user. * @param string $table_name Name of the db table relevant for the date constraint. */ protected function add_intervals_sql_params( $query_args, $table_name ) { $this->clear_sql_clause( array( 'from', 'where_time', 'where' ) ); $this->add_time_period_sql_params( $query_args, $table_name ); if ( isset( $query_args['interval'] ) && '' !== $query_args['interval'] ) { $interval = $query_args['interval']; $this->clear_sql_clause( 'select' ); $this->add_sql_clause( 'select', TimeInterval::db_datetime_format( $interval, $table_name ) ); } } /** * Get join and where clauses for refunds based on user supplied parameters. * * @param array $query_args Parameters supplied by the user. * @return array */ protected function get_refund_subquery( $query_args ) { global $wpdb; $table_name = $wpdb->prefix . 'wc_order_stats'; $sql_query = array( 'where_clause' => '', 'from_clause' => '', ); if ( ! isset( $query_args['refunds'] ) ) { return $sql_query; } if ( 'all' === $query_args['refunds'] ) { $sql_query['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; return " {$select_table}.{$select_field} {$compare} ( SELECT DISTINCT {$filter_table}.{$select_field} FROM {$filter_table} WHERE {$filter_table}.{$filter_field} IN ({$id_list}) )"; } /** * 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' ), $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( $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', $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. * @return array */ protected function get_attribute_subqueries( $query_args ) { global $wpdb; $sql_clauses = array( 'join' => array(), 'where' => array(), ); $match_operator = $this->get_match_operator( $query_args ); $join_table = $wpdb->prefix . 'wc_order_product_lookup'; $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 = wc_variation_attribute_name( $attr_taxonomy ); $meta_value = $attr_term->slug; } else { // Assume these are a custom attribute slug/value pair. $meta_key = 'attribute_' . esc_sql( $attribute_term[0] ); $meta_value = esc_sql( $attribute_term[1] ); } $join_alias = 'wpm1'; // If we're matching all filters (AND), we'll need multiple JOINs on postmeta. // If not, just one. if ( 'AND' === $match_operator || empty( $sql_clauses['join'] ) ) { $join_idx = count( $sql_clauses['join'] ) + 1; $join_alias = 'wpm' . $join_idx; $sql_clauses['join'][] = "JOIN {$wpdb->postmeta} as {$join_alias} ON {$join_alias}.post_id = {$join_table}.variation_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 ); } } 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 = ',' ) { $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 ) ) { $ids_str = implode( $separator, $ids ); } return $ids_str; } /** * Assign report columns once full table name has been assigned. */ protected function assign_report_columns() {} }