Segmenter.php
332 lines
| 1 | <?php |
| 2 | /** |
| 3 | * Class for adding segmenting support to coupons/stats without cluttering the data store. |
| 4 | */ |
| 5 | |
| 6 | namespace Automattic\WooCommerce\Admin\API\Reports\Coupons\Stats; |
| 7 | |
| 8 | defined( 'ABSPATH' ) || exit; |
| 9 | |
| 10 | use Automattic\WooCommerce\Admin\API\Reports\Segmenter as ReportsSegmenter; |
| 11 | use Automattic\WooCommerce\Admin\API\Reports\ParameterException; |
| 12 | |
| 13 | /** |
| 14 | * Date & time interval and numeric range handling class for Reporting API. |
| 15 | */ |
| 16 | class Segmenter extends ReportsSegmenter { |
| 17 | |
| 18 | /** |
| 19 | * Returns column => query mapping to be used for product-related product-level segmenting query |
| 20 | * (e.g. coupon discount amount for product X when segmenting by product id or category). |
| 21 | * |
| 22 | * @param string $products_table Name of SQL table containing the product-level segmenting info. |
| 23 | * |
| 24 | * @return array Column => SELECT query mapping. |
| 25 | */ |
| 26 | protected function get_segment_selections_product_level( $products_table ) { |
| 27 | $columns_mapping = array( |
| 28 | 'amount' => "SUM($products_table.coupon_amount) as amount", |
| 29 | ); |
| 30 | |
| 31 | return $columns_mapping; |
| 32 | } |
| 33 | |
| 34 | /** |
| 35 | * Returns column => query mapping to be used for order-related product-level segmenting query |
| 36 | * (e.g. orders_count when segmented by category). |
| 37 | * |
| 38 | * @param string $coupons_lookup_table Name of SQL table containing the order-level segmenting info. |
| 39 | * |
| 40 | * @return array Column => SELECT query mapping. |
| 41 | */ |
| 42 | protected function get_segment_selections_order_level( $coupons_lookup_table ) { |
| 43 | $columns_mapping = array( |
| 44 | 'coupons_count' => "COUNT(DISTINCT $coupons_lookup_table.coupon_id) as coupons_count", |
| 45 | 'orders_count' => "COUNT(DISTINCT $coupons_lookup_table.order_id) as orders_count", |
| 46 | ); |
| 47 | |
| 48 | return $columns_mapping; |
| 49 | } |
| 50 | |
| 51 | /** |
| 52 | * Returns column => query mapping to be used for order-level segmenting query |
| 53 | * (e.g. discount amount when segmented by coupons). |
| 54 | * |
| 55 | * @param string $coupons_lookup_table Name of SQL table containing the order-level info. |
| 56 | * @param array $overrides Array of overrides for default column calculations. |
| 57 | * |
| 58 | * @return array Column => SELECT query mapping. |
| 59 | */ |
| 60 | protected function segment_selections_orders( $coupons_lookup_table, $overrides = array() ) { |
| 61 | $columns_mapping = array( |
| 62 | 'amount' => "SUM($coupons_lookup_table.discount_amount) as amount", |
| 63 | 'coupons_count' => "COUNT(DISTINCT $coupons_lookup_table.coupon_id) as coupons_count", |
| 64 | 'orders_count' => "COUNT(DISTINCT $coupons_lookup_table.order_id) as orders_count", |
| 65 | ); |
| 66 | |
| 67 | if ( $overrides ) { |
| 68 | $columns_mapping = array_merge( $columns_mapping, $overrides ); |
| 69 | } |
| 70 | |
| 71 | return $columns_mapping; |
| 72 | } |
| 73 | |
| 74 | /** |
| 75 | * Calculate segments for totals where the segmenting property is bound to product (e.g. category, product_id, variation_id). |
| 76 | * |
| 77 | * @param array $segmenting_selections SELECT part of segmenting SQL query--one for 'product_level' and one for 'order_level'. |
| 78 | * @param string $segmenting_from FROM part of segmenting SQL query. |
| 79 | * @param string $segmenting_where WHERE part of segmenting SQL query. |
| 80 | * @param string $segmenting_groupby GROUP BY part of segmenting SQL query. |
| 81 | * @param string $segmenting_dimension_name Name of the segmenting dimension. |
| 82 | * @param string $table_name Name of SQL table which is the stats table for orders. |
| 83 | * @param array $totals_query Array of SQL clauses for totals query. |
| 84 | * @param string $unique_orders_table Name of temporary SQL table that holds unique orders. |
| 85 | * |
| 86 | * @return array |
| 87 | */ |
| 88 | protected function get_product_related_totals_segments( $segmenting_selections, $segmenting_from, $segmenting_where, $segmenting_groupby, $segmenting_dimension_name, $table_name, $totals_query, $unique_orders_table ) { |
| 89 | global $wpdb; |
| 90 | |
| 91 | // Product-level numbers and order-level numbers can be fetched by the same query. |
| 92 | $segments_products = $wpdb->get_results( |
| 93 | "SELECT |
| 94 | $segmenting_groupby AS $segmenting_dimension_name |
| 95 | {$segmenting_selections['product_level']} |
| 96 | {$segmenting_selections['order_level']} |
| 97 | FROM |
| 98 | $table_name |
| 99 | $segmenting_from |
| 100 | {$totals_query['from_clause']} |
| 101 | WHERE |
| 102 | 1=1 |
| 103 | {$totals_query['where_time_clause']} |
| 104 | {$totals_query['where_clause']} |
| 105 | $segmenting_where |
| 106 | GROUP BY |
| 107 | $segmenting_groupby", |
| 108 | ARRAY_A |
| 109 | ); // WPCS: cache ok, DB call ok, unprepared SQL ok. |
| 110 | |
| 111 | $totals_segments = $this->merge_segment_totals_results( $segmenting_dimension_name, $segments_products, array() ); |
| 112 | return $totals_segments; |
| 113 | } |
| 114 | |
| 115 | /** |
| 116 | * Calculate segments for intervals where the segmenting property is bound to product (e.g. category, product_id, variation_id). |
| 117 | * |
| 118 | * @param array $segmenting_selections SELECT part of segmenting SQL query--one for 'product_level' and one for 'order_level'. |
| 119 | * @param string $segmenting_from FROM part of segmenting SQL query. |
| 120 | * @param string $segmenting_where WHERE part of segmenting SQL query. |
| 121 | * @param string $segmenting_groupby GROUP BY part of segmenting SQL query. |
| 122 | * @param string $segmenting_dimension_name Name of the segmenting dimension. |
| 123 | * @param string $table_name Name of SQL table which is the stats table for orders. |
| 124 | * @param array $intervals_query Array of SQL clauses for intervals query. |
| 125 | * @param string $unique_orders_table Name of temporary SQL table that holds unique orders. |
| 126 | * |
| 127 | * @return array |
| 128 | */ |
| 129 | protected function get_product_related_intervals_segments( $segmenting_selections, $segmenting_from, $segmenting_where, $segmenting_groupby, $segmenting_dimension_name, $table_name, $intervals_query, $unique_orders_table ) { |
| 130 | global $wpdb; |
| 131 | |
| 132 | // LIMIT offset, rowcount needs to be updated to LIMIT offset, rowcount * max number of segments. |
| 133 | $limit_parts = explode( ',', $intervals_query['limit'] ); |
| 134 | $orig_rowcount = intval( $limit_parts[1] ); |
| 135 | $segmenting_limit = $limit_parts[0] . ',' . $orig_rowcount * count( $this->get_all_segments() ); |
| 136 | |
| 137 | // Product-level numbers and order-level numbers can be fetched by the same query. |
| 138 | $segments_products = $wpdb->get_results( |
| 139 | "SELECT |
| 140 | {$intervals_query['select_clause']} AS time_interval, |
| 141 | $segmenting_groupby AS $segmenting_dimension_name |
| 142 | {$segmenting_selections['product_level']} |
| 143 | {$segmenting_selections['order_level']} |
| 144 | FROM |
| 145 | $table_name |
| 146 | $segmenting_from |
| 147 | {$intervals_query['from_clause']} |
| 148 | WHERE |
| 149 | 1=1 |
| 150 | {$intervals_query['where_time_clause']} |
| 151 | {$intervals_query['where_clause']} |
| 152 | $segmenting_where |
| 153 | GROUP BY |
| 154 | time_interval, $segmenting_groupby |
| 155 | $segmenting_limit", |
| 156 | ARRAY_A |
| 157 | ); // WPCS: cache ok, DB call ok, unprepared SQL ok. |
| 158 | |
| 159 | $intervals_segments = $this->merge_segment_intervals_results( $segmenting_dimension_name, $segments_products, array() ); |
| 160 | return $intervals_segments; |
| 161 | } |
| 162 | |
| 163 | /** |
| 164 | * Calculate segments for totals query where the segmenting property is bound to order (e.g. coupon or customer type). |
| 165 | * |
| 166 | * @param string $segmenting_select SELECT part of segmenting SQL query. |
| 167 | * @param string $segmenting_from FROM part of segmenting SQL query. |
| 168 | * @param string $segmenting_where WHERE part of segmenting SQL query. |
| 169 | * @param string $segmenting_groupby GROUP BY part of segmenting SQL query. |
| 170 | * @param string $table_name Name of SQL table which is the stats table for orders. |
| 171 | * @param array $totals_query Array of SQL clauses for intervals query. |
| 172 | * |
| 173 | * @return array |
| 174 | */ |
| 175 | protected function get_order_related_totals_segments( $segmenting_select, $segmenting_from, $segmenting_where, $segmenting_groupby, $table_name, $totals_query ) { |
| 176 | global $wpdb; |
| 177 | |
| 178 | $totals_segments = $wpdb->get_results( |
| 179 | "SELECT |
| 180 | $segmenting_groupby |
| 181 | $segmenting_select |
| 182 | FROM |
| 183 | $table_name |
| 184 | $segmenting_from |
| 185 | {$totals_query['from_clause']} |
| 186 | WHERE |
| 187 | 1=1 |
| 188 | {$totals_query['where_time_clause']} |
| 189 | {$totals_query['where_clause']} |
| 190 | $segmenting_where |
| 191 | GROUP BY |
| 192 | $segmenting_groupby", |
| 193 | ARRAY_A |
| 194 | ); // WPCS: cache ok, DB call ok, unprepared SQL ok. |
| 195 | |
| 196 | // Reformat result. |
| 197 | $totals_segments = $this->reformat_totals_segments( $totals_segments, $segmenting_groupby ); |
| 198 | return $totals_segments; |
| 199 | } |
| 200 | |
| 201 | /** |
| 202 | * Calculate segments for intervals query where the segmenting property is bound to order (e.g. coupon or customer type). |
| 203 | * |
| 204 | * @param string $segmenting_select SELECT part of segmenting SQL query. |
| 205 | * @param string $segmenting_from FROM part of segmenting SQL query. |
| 206 | * @param string $segmenting_where WHERE part of segmenting SQL query. |
| 207 | * @param string $segmenting_groupby GROUP BY part of segmenting SQL query. |
| 208 | * @param string $table_name Name of SQL table which is the stats table for orders. |
| 209 | * @param array $intervals_query Array of SQL clauses for intervals query. |
| 210 | * |
| 211 | * @return array |
| 212 | */ |
| 213 | protected function get_order_related_intervals_segments( $segmenting_select, $segmenting_from, $segmenting_where, $segmenting_groupby, $table_name, $intervals_query ) { |
| 214 | global $wpdb; |
| 215 | $limit_parts = explode( ',', $intervals_query['limit'] ); |
| 216 | $orig_rowcount = intval( $limit_parts[1] ); |
| 217 | $segmenting_limit = $limit_parts[0] . ',' . $orig_rowcount * count( $this->get_all_segments() ); |
| 218 | |
| 219 | $intervals_segments = $wpdb->get_results( |
| 220 | "SELECT |
| 221 | MAX($table_name.date_created) AS datetime_anchor, |
| 222 | {$intervals_query['select_clause']} AS time_interval, |
| 223 | $segmenting_groupby |
| 224 | $segmenting_select |
| 225 | FROM |
| 226 | $table_name |
| 227 | $segmenting_from |
| 228 | {$intervals_query['from_clause']} |
| 229 | WHERE |
| 230 | 1=1 |
| 231 | {$intervals_query['where_time_clause']} |
| 232 | {$intervals_query['where_clause']} |
| 233 | $segmenting_where |
| 234 | GROUP BY |
| 235 | time_interval, $segmenting_groupby |
| 236 | $segmenting_limit", |
| 237 | ARRAY_A |
| 238 | ); // WPCS: cache ok, DB call ok, unprepared SQL ok. |
| 239 | |
| 240 | // Reformat result. |
| 241 | $intervals_segments = $this->reformat_intervals_segments( $intervals_segments, $segmenting_groupby ); |
| 242 | return $intervals_segments; |
| 243 | } |
| 244 | |
| 245 | /** |
| 246 | * Return array of segments formatted for REST response. |
| 247 | * |
| 248 | * @param string $type Type of segments to return--'totals' or 'intervals'. |
| 249 | * @param array $query_params SQL query parameter array. |
| 250 | * @param string $table_name Name of main SQL table for the data store (used as basis for JOINS). |
| 251 | * |
| 252 | * @return array |
| 253 | * @throws \Automattic\WooCommerce\Admin\API\Reports\ParameterException In case of segmenting by variations, when no parent product is specified. |
| 254 | */ |
| 255 | protected function get_segments( $type, $query_params, $table_name ) { |
| 256 | global $wpdb; |
| 257 | if ( ! isset( $this->query_args['segmentby'] ) || '' === $this->query_args['segmentby'] ) { |
| 258 | return array(); |
| 259 | } |
| 260 | |
| 261 | $segments = null; |
| 262 | $product_segmenting_table = $wpdb->prefix . 'wc_order_product_lookup'; |
| 263 | $unique_orders_table = ''; |
| 264 | $segmenting_where = ''; |
| 265 | |
| 266 | // Product, variation, and category are bound to product, so here product segmenting table is required, |
| 267 | // while coupon and customer are bound to order, so we don't need the extra JOIN for those. |
| 268 | // This also means that segment selections need to be calculated differently. |
| 269 | if ( 'product' === $this->query_args['segmentby'] ) { |
| 270 | $product_level_columns = $this->get_segment_selections_product_level( $product_segmenting_table ); |
| 271 | $order_level_columns = $this->get_segment_selections_order_level( $table_name ); |
| 272 | $segmenting_selections = array( |
| 273 | 'product_level' => $this->prepare_selections( $product_level_columns ), |
| 274 | 'order_level' => $this->prepare_selections( $order_level_columns ), |
| 275 | ); |
| 276 | $this->report_columns = array_merge( $product_level_columns, $order_level_columns ); |
| 277 | $segmenting_from = "INNER JOIN $product_segmenting_table ON ($table_name.order_id = $product_segmenting_table.order_id)"; |
| 278 | $segmenting_groupby = $product_segmenting_table . '.product_id'; |
| 279 | $segmenting_dimension_name = 'product_id'; |
| 280 | |
| 281 | $segments = $this->get_product_related_segments( $type, $segmenting_selections, $segmenting_from, $segmenting_where, $segmenting_groupby, $segmenting_dimension_name, $table_name, $query_params, $unique_orders_table ); |
| 282 | } elseif ( 'variation' === $this->query_args['segmentby'] ) { |
| 283 | if ( ! isset( $this->query_args['product_includes'] ) || ! is_array( $this->query_args['product_includes'] ) || count( $this->query_args['product_includes'] ) !== 1 ) { |
| 284 | throw new ParameterException( 'wc_admin_reports_invalid_segmenting_variation', __( 'product_includes parameter need to specify exactly one product when segmenting by variation.', 'woocommerce' ) ); |
| 285 | } |
| 286 | |
| 287 | $product_level_columns = $this->get_segment_selections_product_level( $product_segmenting_table ); |
| 288 | $order_level_columns = $this->get_segment_selections_order_level( $table_name ); |
| 289 | $segmenting_selections = array( |
| 290 | 'product_level' => $this->prepare_selections( $product_level_columns ), |
| 291 | 'order_level' => $this->prepare_selections( $order_level_columns ), |
| 292 | ); |
| 293 | $this->report_columns = array_merge( $product_level_columns, $order_level_columns ); |
| 294 | $segmenting_from = "INNER JOIN $product_segmenting_table ON ($table_name.order_id = $product_segmenting_table.order_id)"; |
| 295 | $segmenting_where = "AND $product_segmenting_table.product_id = {$this->query_args['product_includes'][0]}"; |
| 296 | $segmenting_groupby = $product_segmenting_table . '.variation_id'; |
| 297 | $segmenting_dimension_name = 'variation_id'; |
| 298 | |
| 299 | $segments = $this->get_product_related_segments( $type, $segmenting_selections, $segmenting_from, $segmenting_where, $segmenting_groupby, $segmenting_dimension_name, $table_name, $query_params, $unique_orders_table ); |
| 300 | } elseif ( 'category' === $this->query_args['segmentby'] ) { |
| 301 | $product_level_columns = $this->get_segment_selections_product_level( $product_segmenting_table ); |
| 302 | $order_level_columns = $this->get_segment_selections_order_level( $table_name ); |
| 303 | $segmenting_selections = array( |
| 304 | 'product_level' => $this->prepare_selections( $product_level_columns ), |
| 305 | 'order_level' => $this->prepare_selections( $order_level_columns ), |
| 306 | ); |
| 307 | $this->report_columns = array_merge( $product_level_columns, $order_level_columns ); |
| 308 | $segmenting_from = " |
| 309 | INNER JOIN $product_segmenting_table ON ($table_name.order_id = $product_segmenting_table.order_id) |
| 310 | LEFT JOIN {$wpdb->term_relationships} ON {$product_segmenting_table}.product_id = {$wpdb->term_relationships}.object_id |
| 311 | JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_relationships}.term_taxonomy_id |
| 312 | LEFT JOIN {$wpdb->wc_category_lookup} ON {$wpdb->term_taxonomy}.term_id = {$wpdb->wc_category_lookup}.category_id |
| 313 | "; |
| 314 | $segmenting_where = " AND {$wpdb->wc_category_lookup}.category_tree_id IS NOT NULL"; |
| 315 | $segmenting_groupby = "{$wpdb->wc_category_lookup}.category_tree_id"; |
| 316 | $segmenting_dimension_name = 'category_id'; |
| 317 | |
| 318 | $segments = $this->get_product_related_segments( $type, $segmenting_selections, $segmenting_from, $segmenting_where, $segmenting_groupby, $segmenting_dimension_name, $table_name, $query_params, $unique_orders_table ); |
| 319 | } elseif ( 'coupon' === $this->query_args['segmentby'] ) { |
| 320 | $coupon_level_columns = $this->segment_selections_orders( $table_name ); |
| 321 | $segmenting_selections = $this->prepare_selections( $coupon_level_columns ); |
| 322 | $this->report_columns = $coupon_level_columns; |
| 323 | $segmenting_from = ''; |
| 324 | $segmenting_groupby = "$table_name.coupon_id"; |
| 325 | |
| 326 | $segments = $this->get_order_related_segments( $type, $segmenting_selections, $segmenting_from, $segmenting_where, $segmenting_groupby, $table_name, $query_params ); |
| 327 | } |
| 328 | |
| 329 | return $segments; |
| 330 | } |
| 331 | } |
| 332 |