DataStore.php
281 lines
| 1 | <?php |
| 2 | /** |
| 3 | * API\Reports\Products\Stats\DataStore class file. |
| 4 | */ |
| 5 | |
| 6 | namespace Automattic\WooCommerce\Admin\API\Reports\Variations\Stats; |
| 7 | |
| 8 | defined( 'ABSPATH' ) || exit; |
| 9 | |
| 10 | use Automattic\WooCommerce\Admin\API\Reports\Variations\DataStore as VariationsDataStore; |
| 11 | use Automattic\WooCommerce\Admin\API\Reports\DataStoreInterface; |
| 12 | use Automattic\WooCommerce\Admin\API\Reports\TimeInterval; |
| 13 | use Automattic\WooCommerce\Admin\API\Reports\StatsDataStoreTrait; |
| 14 | |
| 15 | /** |
| 16 | * API\Reports\Variations\Stats\DataStore. |
| 17 | */ |
| 18 | class DataStore extends VariationsDataStore implements DataStoreInterface { |
| 19 | use StatsDataStoreTrait; |
| 20 | |
| 21 | /** |
| 22 | * Mapping columns to data type to return correct response types. |
| 23 | * |
| 24 | * @override VariationsDataStore::$column_types |
| 25 | * |
| 26 | * @var array |
| 27 | */ |
| 28 | protected $column_types = array( |
| 29 | 'items_sold' => 'intval', |
| 30 | 'net_revenue' => 'floatval', |
| 31 | 'orders_count' => 'intval', |
| 32 | 'variations_count' => 'intval', |
| 33 | ); |
| 34 | |
| 35 | /** |
| 36 | * Cache identifier. |
| 37 | * |
| 38 | * @override VariationsDataStore::$cache_key |
| 39 | * |
| 40 | * @var string |
| 41 | */ |
| 42 | protected $cache_key = 'variations_stats'; |
| 43 | |
| 44 | /** |
| 45 | * Data store context used to pass to filters. |
| 46 | * |
| 47 | * @override VariationsDataStore::$context |
| 48 | * |
| 49 | * @var string |
| 50 | */ |
| 51 | protected $context = 'variations_stats'; |
| 52 | |
| 53 | /** |
| 54 | * Assign report columns once full table name has been assigned. |
| 55 | * |
| 56 | * @override VariationsDataStore::assign_report_columns() |
| 57 | */ |
| 58 | protected function assign_report_columns() { |
| 59 | $table_name = self::get_db_table_name(); |
| 60 | $this->report_columns = array( |
| 61 | 'items_sold' => 'SUM(product_qty) as items_sold', |
| 62 | 'net_revenue' => 'SUM(product_net_revenue) AS net_revenue', |
| 63 | 'orders_count' => "COUNT( DISTINCT ( CASE WHEN product_gross_revenue >= 0 THEN {$table_name}.order_id END ) ) as orders_count", |
| 64 | 'variations_count' => 'COUNT(DISTINCT variation_id) as variations_count', |
| 65 | ); |
| 66 | } |
| 67 | |
| 68 | /** |
| 69 | * Updates the database query with parameters used for Products Stats report: categories and order status. |
| 70 | * |
| 71 | * @param array $query_args Query arguments supplied by the user. |
| 72 | */ |
| 73 | protected function update_sql_query_params( $query_args ) { |
| 74 | global $wpdb; |
| 75 | |
| 76 | $products_where_clause = ''; |
| 77 | $products_from_clause = ''; |
| 78 | $where_subquery = array(); |
| 79 | $order_product_lookup_table = self::get_db_table_name(); |
| 80 | $order_item_meta_table = $wpdb->prefix . 'woocommerce_order_itemmeta'; |
| 81 | |
| 82 | $included_products = $this->get_included_products( $query_args ); |
| 83 | if ( $included_products ) { |
| 84 | $products_where_clause .= " AND {$order_product_lookup_table}.product_id IN ({$included_products})"; |
| 85 | } |
| 86 | |
| 87 | $excluded_products = $this->get_excluded_products( $query_args ); |
| 88 | if ( $excluded_products ) { |
| 89 | $products_where_clause .= "AND {$order_product_lookup_table}.product_id NOT IN ({$excluded_products})"; |
| 90 | } |
| 91 | |
| 92 | $included_variations = $this->get_included_variations( $query_args ); |
| 93 | if ( $included_variations ) { |
| 94 | $products_where_clause .= " AND {$order_product_lookup_table}.variation_id IN ({$included_variations})"; |
| 95 | } elseif ( $this->should_exclude_simple_products( $query_args ) ) { |
| 96 | $products_where_clause .= " AND {$order_product_lookup_table}.variation_id != 0"; |
| 97 | } |
| 98 | |
| 99 | $order_status_filter = $this->get_status_subquery( $query_args ); |
| 100 | if ( $order_status_filter ) { |
| 101 | $products_from_clause .= " JOIN {$wpdb->prefix}wc_order_stats ON {$order_product_lookup_table}.order_id = {$wpdb->prefix}wc_order_stats.order_id"; |
| 102 | $products_where_clause .= " AND ( {$order_status_filter} )"; |
| 103 | } |
| 104 | |
| 105 | $attribute_order_items_subquery = $this->get_order_item_by_attribute_subquery( $query_args ); |
| 106 | if ( $attribute_order_items_subquery ) { |
| 107 | // JOIN on product lookup if we haven't already. |
| 108 | if ( ! $order_status_filter ) { |
| 109 | $products_from_clause .= " JOIN {$wpdb->prefix}wc_order_stats ON {$order_product_lookup_table}.order_id = {$wpdb->prefix}wc_order_stats.order_id"; |
| 110 | } |
| 111 | |
| 112 | // Add subquery for matching attributes to WHERE. |
| 113 | $products_where_clause .= $attribute_order_items_subquery; |
| 114 | } |
| 115 | |
| 116 | if ( 0 < count( $where_subquery ) ) { |
| 117 | $operator = $this->get_match_operator( $query_args ); |
| 118 | $products_where_clause .= 'AND (' . implode( " {$operator} ", $where_subquery ) . ')'; |
| 119 | } |
| 120 | |
| 121 | $this->add_time_period_sql_params( $query_args, $order_product_lookup_table ); |
| 122 | $this->total_query->add_sql_clause( 'where', $products_where_clause ); |
| 123 | $this->total_query->add_sql_clause( 'join', $products_from_clause ); |
| 124 | |
| 125 | $this->add_intervals_sql_params( $query_args, $order_product_lookup_table ); |
| 126 | $this->interval_query->add_sql_clause( 'where', $products_where_clause ); |
| 127 | $this->interval_query->add_sql_clause( 'join', $products_from_clause ); |
| 128 | $this->interval_query->add_sql_clause( 'select', $this->get_sql_clause( 'select' ) . ' AS time_interval' ); |
| 129 | } |
| 130 | |
| 131 | /** |
| 132 | * Returns if simple products should be excluded from the report. |
| 133 | * |
| 134 | * @internal |
| 135 | * |
| 136 | * @param array $query_args Query parameters. |
| 137 | * |
| 138 | * @return boolean |
| 139 | */ |
| 140 | protected function should_exclude_simple_products( array $query_args ) { |
| 141 | return apply_filters( 'experimental_woocommerce_analytics_variations_stats_should_exclude_simple_products', true, $query_args ); |
| 142 | } |
| 143 | |
| 144 | /** |
| 145 | * Get the default query arguments to be used by get_data(). |
| 146 | * These defaults are only partially applied when used via REST API, as that has its own defaults. |
| 147 | * |
| 148 | * @override VariationsDataStore::get_default_query_vars() |
| 149 | * |
| 150 | * @return array Query parameters. |
| 151 | */ |
| 152 | public function get_default_query_vars() { |
| 153 | $defaults = parent::get_default_query_vars(); |
| 154 | $defaults['category_includes'] = array(); |
| 155 | $defaults['interval'] = 'week'; |
| 156 | unset( $defaults['extended_info'] ); |
| 157 | |
| 158 | return $defaults; |
| 159 | } |
| 160 | |
| 161 | /** |
| 162 | * Returns the report data based on normalized parameters. |
| 163 | * Will be called by `get_data` if there is no data in cache. |
| 164 | * |
| 165 | * @override VariationsDataStore::get_noncached_stats_data() |
| 166 | * |
| 167 | * @see get_data |
| 168 | * @see get_noncached_stats_data |
| 169 | * @param array $query_args Query parameters. |
| 170 | * @param array $params Query limit parameters. |
| 171 | * @param stdClass $data Reference to the data object to fill. |
| 172 | * @param int $expected_interval_count Number of expected intervals. |
| 173 | * @return stdClass|WP_Error Data object `{ totals: *, intervals: array, total: int, pages: int, page_no: int }`, or error. |
| 174 | */ |
| 175 | public function get_noncached_stats_data( $query_args, $params, &$data, $expected_interval_count ) { |
| 176 | global $wpdb; |
| 177 | |
| 178 | $table_name = self::get_db_table_name(); |
| 179 | |
| 180 | $this->initialize_queries(); |
| 181 | |
| 182 | $selections = $this->selected_columns( $query_args ); |
| 183 | |
| 184 | $this->update_sql_query_params( $query_args ); |
| 185 | $this->get_limit_sql_params( $query_args ); |
| 186 | $this->interval_query->add_sql_clause( 'where_time', $this->get_sql_clause( 'where_time' ) ); |
| 187 | |
| 188 | /* phpcs:disable WordPress.DB.PreparedSQL.NotPrepared */ |
| 189 | $db_intervals = $wpdb->get_col( |
| 190 | $this->interval_query->get_query_statement() |
| 191 | ); |
| 192 | /* phpcs:enable */ |
| 193 | |
| 194 | $db_interval_count = count( $db_intervals ); |
| 195 | |
| 196 | $intervals = array(); |
| 197 | $this->update_intervals_sql_params( $query_args, $db_interval_count, $expected_interval_count, $table_name ); |
| 198 | $this->total_query->add_sql_clause( 'select', $selections ); |
| 199 | $this->total_query->add_sql_clause( 'where_time', $this->get_sql_clause( 'where_time' ) ); |
| 200 | |
| 201 | /* phpcs:disable WordPress.DB.PreparedSQL.NotPrepared */ |
| 202 | $totals = $wpdb->get_results( |
| 203 | $this->total_query->get_query_statement(), |
| 204 | ARRAY_A |
| 205 | ); |
| 206 | /* phpcs:enable */ |
| 207 | |
| 208 | // phpcs:ignore Generic.Commenting.Todo.TaskFound |
| 209 | // @todo remove these assignments when refactoring segmenter classes to use query objects. |
| 210 | $totals_query = array( |
| 211 | 'from_clause' => $this->total_query->get_sql_clause( 'join' ), |
| 212 | 'where_time_clause' => $this->total_query->get_sql_clause( 'where_time' ), |
| 213 | 'where_clause' => $this->total_query->get_sql_clause( 'where' ), |
| 214 | ); |
| 215 | $intervals_query = array( |
| 216 | 'select_clause' => $this->get_sql_clause( 'select' ), |
| 217 | 'from_clause' => $this->interval_query->get_sql_clause( 'join' ), |
| 218 | 'where_time_clause' => $this->interval_query->get_sql_clause( 'where_time' ), |
| 219 | 'where_clause' => $this->interval_query->get_sql_clause( 'where' ), |
| 220 | 'order_by' => $this->get_sql_clause( 'order_by' ), |
| 221 | 'limit' => $this->get_sql_clause( 'limit' ), |
| 222 | ); |
| 223 | $segmenter = new Segmenter( $query_args, $this->report_columns ); |
| 224 | $totals[0]['segments'] = $segmenter->get_totals_segments( $totals_query, $table_name ); |
| 225 | |
| 226 | if ( null === $totals ) { |
| 227 | return new \WP_Error( 'woocommerce_analytics_variations_stats_result_failed', __( 'Sorry, fetching revenue data failed.', 'woocommerce' ) ); |
| 228 | } |
| 229 | |
| 230 | $this->interval_query->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) ); |
| 231 | $this->interval_query->add_sql_clause( 'limit', $this->get_sql_clause( 'limit' ) ); |
| 232 | $this->interval_query->add_sql_clause( 'select', ", MAX({$table_name}.date_created) AS datetime_anchor" ); |
| 233 | if ( '' !== $selections ) { |
| 234 | $this->interval_query->add_sql_clause( 'select', ', ' . $selections ); |
| 235 | } |
| 236 | |
| 237 | /* phpcs:disable WordPress.DB.PreparedSQL.NotPrepared */ |
| 238 | $intervals = $wpdb->get_results( |
| 239 | $this->interval_query->get_query_statement(), |
| 240 | ARRAY_A |
| 241 | ); |
| 242 | /* phpcs:enable */ |
| 243 | |
| 244 | if ( null === $intervals ) { |
| 245 | return new \WP_Error( 'woocommerce_analytics_variations_stats_result_failed', __( 'Sorry, fetching revenue data failed.', 'woocommerce' ) ); |
| 246 | } |
| 247 | |
| 248 | $totals = (object) $this->cast_numbers( $totals[0] ); |
| 249 | |
| 250 | $data->totals = $totals; |
| 251 | $data->intervals = $intervals; |
| 252 | |
| 253 | if ( TimeInterval::intervals_missing( $expected_interval_count, $db_interval_count, $params['per_page'], $query_args['page'], $query_args['order'], $query_args['orderby'], count( $intervals ) ) ) { |
| 254 | $this->fill_in_missing_intervals( $db_intervals, $query_args['adj_after'], $query_args['adj_before'], $query_args['interval'], $data ); |
| 255 | $this->sort_intervals( $data, $query_args['orderby'], $query_args['order'] ); |
| 256 | $this->remove_extra_records( $data, $query_args['page'], $params['per_page'], $db_interval_count, $expected_interval_count, $query_args['orderby'], $query_args['order'] ); |
| 257 | } else { |
| 258 | $this->update_interval_boundary_dates( $query_args['after'], $query_args['before'], $query_args['interval'], $data->intervals ); |
| 259 | } |
| 260 | $segmenter->add_intervals_segments( $data, $intervals_query, $table_name ); |
| 261 | |
| 262 | return $data; |
| 263 | } |
| 264 | |
| 265 | /** |
| 266 | * Normalizes order_by clause to match to SQL query. |
| 267 | * |
| 268 | * @override VariationsDataStore::normalize_order_by() |
| 269 | * |
| 270 | * @param string $order_by Order by option requeste by user. |
| 271 | * @return string |
| 272 | */ |
| 273 | protected function normalize_order_by( $order_by ) { |
| 274 | if ( 'date' === $order_by ) { |
| 275 | return 'time_interval'; |
| 276 | } |
| 277 | |
| 278 | return $order_by; |
| 279 | } |
| 280 | } |
| 281 |