DataStore.php
518 lines
| 1 | <?php |
| 2 | /** |
| 3 | * API\Reports\Coupons\DataStore class file. |
| 4 | */ |
| 5 | |
| 6 | namespace Automattic\WooCommerce\Admin\API\Reports\Coupons; |
| 7 | |
| 8 | defined( 'ABSPATH' ) || exit; |
| 9 | |
| 10 | use Automattic\WooCommerce\Admin\API\Reports\DataStore as ReportsDataStore; |
| 11 | use Automattic\WooCommerce\Admin\API\Reports\DataStoreInterface; |
| 12 | use Automattic\WooCommerce\Admin\API\Reports\TimeInterval; |
| 13 | use Automattic\WooCommerce\Admin\API\Reports\SqlQuery; |
| 14 | use Automattic\WooCommerce\Admin\API\Reports\Cache as ReportsCache; |
| 15 | use Automattic\WooCommerce\Enums\OrderItemType; |
| 16 | |
| 17 | /** |
| 18 | * API\Reports\Coupons\DataStore. |
| 19 | */ |
| 20 | class DataStore extends ReportsDataStore implements DataStoreInterface { |
| 21 | |
| 22 | /** |
| 23 | * Table used to get the data. |
| 24 | * |
| 25 | * @override ReportsDataStore::$table_name |
| 26 | * |
| 27 | * @var string |
| 28 | */ |
| 29 | protected static $table_name = 'wc_order_coupon_lookup'; |
| 30 | |
| 31 | /** |
| 32 | * Cache identifier. |
| 33 | * |
| 34 | * @override ReportsDataStore::$cache_key |
| 35 | * |
| 36 | * @var string |
| 37 | */ |
| 38 | protected $cache_key = 'coupons'; |
| 39 | |
| 40 | /** |
| 41 | * Mapping columns to data type to return correct response types. |
| 42 | * |
| 43 | * @override ReportsDataStore::$column_types |
| 44 | * |
| 45 | * @var array |
| 46 | */ |
| 47 | protected $column_types = array( |
| 48 | 'coupon_id' => 'intval', |
| 49 | 'amount' => 'floatval', |
| 50 | 'orders_count' => 'intval', |
| 51 | ); |
| 52 | |
| 53 | /** |
| 54 | * Data store context used to pass to filters. |
| 55 | * |
| 56 | * @override ReportsDataStore::$context |
| 57 | * |
| 58 | * @var string |
| 59 | */ |
| 60 | protected $context = 'coupons'; |
| 61 | |
| 62 | /** |
| 63 | * Assign report columns once full table name has been assigned. |
| 64 | * |
| 65 | * @override ReportsDataStore::assign_report_columns() |
| 66 | */ |
| 67 | protected function assign_report_columns() { |
| 68 | $table_name = self::get_db_table_name(); |
| 69 | $this->report_columns = array( |
| 70 | 'coupon_id' => 'coupon_id', |
| 71 | 'amount' => 'SUM(discount_amount) as amount', |
| 72 | 'orders_count' => "COUNT(DISTINCT {$table_name}.order_id) as orders_count", |
| 73 | ); |
| 74 | } |
| 75 | |
| 76 | // This method was already available as non-final, marking it as final now would make it backwards-incompatible. |
| 77 | // phpcs:disable WooCommerce.Functions.InternalInjectionMethod.MissingFinal |
| 78 | |
| 79 | /** |
| 80 | * Set up all the hooks for maintaining and populating table data. |
| 81 | * |
| 82 | * @internal |
| 83 | */ |
| 84 | public static function init() { |
| 85 | add_action( 'woocommerce_analytics_delete_order_stats', array( __CLASS__, 'sync_on_order_delete' ), 5 ); |
| 86 | } |
| 87 | |
| 88 | // phpcs:enable WooCommerce.Functions.InternalInjectionMethod.MissingFinal |
| 89 | |
| 90 | /** |
| 91 | * Returns an array of ids of included coupons, based on query arguments from the user. |
| 92 | * |
| 93 | * @param array $query_args Parameters supplied by the user. |
| 94 | * @return array |
| 95 | */ |
| 96 | protected function get_included_coupons_array( $query_args ) { |
| 97 | if ( isset( $query_args['coupons'] ) && is_array( $query_args['coupons'] ) && count( $query_args['coupons'] ) > 0 ) { |
| 98 | return $query_args['coupons']; |
| 99 | } |
| 100 | return array(); |
| 101 | } |
| 102 | |
| 103 | /** |
| 104 | * Updates the database query with parameters used for Products report: categories and order status. |
| 105 | * |
| 106 | * @param array $query_args Query arguments supplied by the user. |
| 107 | */ |
| 108 | protected function add_sql_query_params( $query_args ) { |
| 109 | global $wpdb; |
| 110 | $order_coupon_lookup_table = self::get_db_table_name(); |
| 111 | |
| 112 | $this->add_time_period_sql_params( $query_args, $order_coupon_lookup_table ); |
| 113 | $this->get_limit_sql_params( $query_args ); |
| 114 | |
| 115 | $included_coupons = $this->get_included_coupons( $query_args, 'coupons' ); |
| 116 | if ( $included_coupons ) { |
| 117 | $this->subquery->add_sql_clause( 'where', "AND {$order_coupon_lookup_table}.coupon_id IN ({$included_coupons})" ); |
| 118 | |
| 119 | $this->add_order_by_params( $query_args, 'outer', 'default_results.coupon_id' ); |
| 120 | } else { |
| 121 | $this->add_order_by_params( $query_args, 'inner', "{$order_coupon_lookup_table}.coupon_id" ); |
| 122 | } |
| 123 | |
| 124 | $this->add_order_status_clause( $query_args, $order_coupon_lookup_table, $this->subquery ); |
| 125 | } |
| 126 | |
| 127 | /** |
| 128 | * Fills ORDER BY clause of SQL request based on user supplied parameters. |
| 129 | * |
| 130 | * @param array $query_args Parameters supplied by the user. |
| 131 | * @param string $from_arg Target of the JOIN sql param. |
| 132 | * @param string $id_cell ID cell identifier, like `table_name.id_column_name`. |
| 133 | */ |
| 134 | protected function add_order_by_params( $query_args, $from_arg, $id_cell ) { |
| 135 | global $wpdb; |
| 136 | |
| 137 | // Sanitize input: guarantee that the id cell in the join is quoted with backticks. |
| 138 | $id_cell_segments = explode( '.', str_replace( '`', '', $id_cell ) ); |
| 139 | $id_cell_identifier = '`' . implode( '`.`', $id_cell_segments ) . '`'; |
| 140 | |
| 141 | $lookup_table = self::get_db_table_name(); |
| 142 | $order_by_clause = $this->add_order_by_clause( $query_args, $this ); |
| 143 | $join = "JOIN {$wpdb->posts} AS _coupons ON {$id_cell_identifier} = _coupons.ID"; |
| 144 | $this->add_orderby_order_clause( $query_args, $this ); |
| 145 | |
| 146 | if ( 'inner' === $from_arg ) { |
| 147 | $this->subquery->clear_sql_clause( 'join' ); |
| 148 | if ( false !== strpos( $order_by_clause, '_coupons' ) ) { |
| 149 | $this->subquery->add_sql_clause( 'join', $join ); |
| 150 | } |
| 151 | } else { |
| 152 | $this->clear_sql_clause( 'join' ); |
| 153 | if ( false !== strpos( $order_by_clause, '_coupons' ) ) { |
| 154 | $this->add_sql_clause( 'join', $join ); |
| 155 | } |
| 156 | } |
| 157 | } |
| 158 | |
| 159 | /** |
| 160 | * Maps ordering specified by the user to columns in the database/fields in the data. |
| 161 | * |
| 162 | * @override ReportsDataStore::normalize_order_by() |
| 163 | * |
| 164 | * @param string $order_by Sorting criterion. |
| 165 | * @return string |
| 166 | */ |
| 167 | protected function normalize_order_by( $order_by ) { |
| 168 | if ( 'date' === $order_by ) { |
| 169 | return 'time_interval'; |
| 170 | } |
| 171 | if ( 'code' === $order_by ) { |
| 172 | return '_coupons.post_title'; |
| 173 | } |
| 174 | return $order_by; |
| 175 | } |
| 176 | |
| 177 | /** |
| 178 | * Enriches the coupon data with extra attributes. |
| 179 | * |
| 180 | * @param array $coupon_data Coupon data. |
| 181 | * @param array $query_args Query parameters. |
| 182 | */ |
| 183 | protected function include_extended_info( &$coupon_data, $query_args ) { |
| 184 | foreach ( $coupon_data as $idx => $coupon_datum ) { |
| 185 | $extended_info = new \ArrayObject(); |
| 186 | if ( $query_args['extended_info'] ) { |
| 187 | $coupon_id = $coupon_datum['coupon_id']; |
| 188 | $coupon = new \WC_Coupon( $coupon_id ); |
| 189 | |
| 190 | if ( 0 === $coupon->get_id() ) { |
| 191 | // Deleted or otherwise invalid coupon. |
| 192 | $extended_info = array( |
| 193 | 'code' => __( '(Deleted)', 'woocommerce' ), |
| 194 | 'date_created' => '', |
| 195 | 'date_created_gmt' => '', |
| 196 | 'date_expires' => '', |
| 197 | 'date_expires_gmt' => '', |
| 198 | 'discount_type' => __( 'N/A', 'woocommerce' ), |
| 199 | ); |
| 200 | } else { |
| 201 | $gmt_timzone = new \DateTimeZone( 'UTC' ); |
| 202 | |
| 203 | $date_expires = $coupon->get_date_expires(); |
| 204 | if ( is_a( $date_expires, 'DateTime' ) ) { |
| 205 | $date_expires = $date_expires->format( TimeInterval::$iso_datetime_format ); |
| 206 | $date_expires_gmt = new \DateTime( $date_expires ); |
| 207 | $date_expires_gmt->setTimezone( $gmt_timzone ); |
| 208 | $date_expires_gmt = $date_expires_gmt->format( TimeInterval::$iso_datetime_format ); |
| 209 | } else { |
| 210 | $date_expires = ''; |
| 211 | $date_expires_gmt = ''; |
| 212 | } |
| 213 | |
| 214 | $date_created = $coupon->get_date_created(); |
| 215 | if ( is_a( $date_created, 'DateTime' ) ) { |
| 216 | $date_created = $date_created->format( TimeInterval::$iso_datetime_format ); |
| 217 | $date_created_gmt = new \DateTime( $date_created ); |
| 218 | $date_created_gmt->setTimezone( $gmt_timzone ); |
| 219 | $date_created_gmt = $date_created_gmt->format( TimeInterval::$iso_datetime_format ); |
| 220 | } else { |
| 221 | $date_created = ''; |
| 222 | $date_created_gmt = ''; |
| 223 | } |
| 224 | |
| 225 | $extended_info = array( |
| 226 | 'code' => $coupon->get_code(), |
| 227 | 'date_created' => $date_created, |
| 228 | 'date_created_gmt' => $date_created_gmt, |
| 229 | 'date_expires' => $date_expires, |
| 230 | 'date_expires_gmt' => $date_expires_gmt, |
| 231 | 'discount_type' => $coupon->get_discount_type(), |
| 232 | ); |
| 233 | } |
| 234 | } |
| 235 | $coupon_data[ $idx ]['extended_info'] = $extended_info; |
| 236 | } |
| 237 | } |
| 238 | |
| 239 | /** |
| 240 | * Get coupon ID for an order. |
| 241 | * |
| 242 | * Tries to get the ID from order item meta, then falls back to a query of published coupons. |
| 243 | * |
| 244 | * @param \WC_Order_Item_Coupon $coupon_item The coupon order item object. |
| 245 | * @return int Coupon ID on success, 0 on failure. |
| 246 | */ |
| 247 | public static function get_coupon_id( \WC_Order_Item_Coupon $coupon_item ) { |
| 248 | // First attempt to get coupon ID from order item data. |
| 249 | $coupon_info = $coupon_item->get_meta( 'coupon_info', true ); |
| 250 | if ( $coupon_info ) { |
| 251 | return json_decode( $coupon_info, true )[0]; |
| 252 | } |
| 253 | |
| 254 | $coupon_data = $coupon_item->get_meta( 'coupon_data', true ); |
| 255 | |
| 256 | // Normal checkout orders should have this data. |
| 257 | // See: https://github.com/woocommerce/woocommerce/blob/3dc7df7af9f7ca0c0aa34ede74493e856f276abe/includes/abstracts/abstract-wc-order.php#L1206. |
| 258 | if ( isset( $coupon_data['id'] ) ) { |
| 259 | return $coupon_data['id']; |
| 260 | } |
| 261 | |
| 262 | // Try to get the coupon ID using the code. |
| 263 | return wc_get_coupon_id_by_code( $coupon_item->get_code() ); |
| 264 | } |
| 265 | |
| 266 | /** |
| 267 | * Get the default query arguments to be used by get_data(). |
| 268 | * These defaults are only partially applied when used via REST API, as that has its own defaults. |
| 269 | * |
| 270 | * @override ReportsDataStore::get_default_query_vars() |
| 271 | * |
| 272 | * @return array Query parameters. |
| 273 | */ |
| 274 | public function get_default_query_vars() { |
| 275 | $defaults = parent::get_default_query_vars(); |
| 276 | $defaults['orderby'] = 'coupon_id'; |
| 277 | $defaults['coupons'] = array(); |
| 278 | $defaults['extended_info'] = false; |
| 279 | |
| 280 | return $defaults; |
| 281 | } |
| 282 | |
| 283 | |
| 284 | /** |
| 285 | * Returns the report data based on normalized parameters. |
| 286 | * Will be called by `get_data` if there is no data in cache. |
| 287 | * |
| 288 | * @override ReportsDataStore::get_noncached_data() |
| 289 | * |
| 290 | * @see get_data |
| 291 | * @param array $query_args Query parameters. |
| 292 | * @return stdClass|WP_Error Data object `{ totals: *, intervals: array, total: int, pages: int, page_no: int }`, or error. |
| 293 | */ |
| 294 | public function get_noncached_data( $query_args ) { |
| 295 | global $wpdb; |
| 296 | |
| 297 | $table_name = self::get_db_table_name(); |
| 298 | |
| 299 | $this->initialize_queries(); |
| 300 | |
| 301 | $data = (object) array( |
| 302 | 'data' => array(), |
| 303 | 'total' => 0, |
| 304 | 'pages' => 0, |
| 305 | 'page_no' => 0, |
| 306 | ); |
| 307 | |
| 308 | $selections = $this->selected_columns( $query_args ); |
| 309 | $included_coupons = $this->get_included_coupons_array( $query_args ); |
| 310 | $limit_params = $this->get_limit_params( $query_args ); |
| 311 | $this->subquery->add_sql_clause( 'select', $selections ); |
| 312 | $this->add_sql_query_params( $query_args ); |
| 313 | |
| 314 | if ( count( $included_coupons ) > 0 ) { |
| 315 | $total_results = count( $included_coupons ); |
| 316 | $total_pages = (int) ceil( $total_results / $limit_params['per_page'] ); |
| 317 | |
| 318 | $fields = $this->get_fields( $query_args ); |
| 319 | $ids_table = $this->get_ids_table( $included_coupons, 'coupon_id' ); |
| 320 | |
| 321 | $this->add_sql_clause( 'select', $this->format_join_selections( $fields, array( 'coupon_id' ) ) ); |
| 322 | $this->add_sql_clause( 'from', '(' ); |
| 323 | $this->add_sql_clause( 'from', $this->subquery->get_query_statement() ); |
| 324 | $this->add_sql_clause( 'from', ") AS {$table_name}" ); |
| 325 | $this->add_sql_clause( |
| 326 | 'right_join', |
| 327 | "RIGHT JOIN ( {$ids_table} ) AS default_results |
| 328 | ON default_results.coupon_id = {$table_name}.coupon_id" |
| 329 | ); |
| 330 | |
| 331 | $coupons_query = $this->get_query_statement(); |
| 332 | } else { |
| 333 | if ( in_array( $query_args['orderby'], array( 'amount', 'orders_count' ), true ) ) { |
| 334 | $this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) . ', coupon_id' ); |
| 335 | } else { |
| 336 | $this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) ); |
| 337 | } |
| 338 | $this->subquery->add_sql_clause( 'limit', $this->get_sql_clause( 'limit' ) ); |
| 339 | $coupons_query = $this->subquery->get_query_statement(); |
| 340 | |
| 341 | $this->subquery->clear_sql_clause( array( 'select', 'order_by', 'limit' ) ); |
| 342 | $this->subquery->add_sql_clause( 'select', 'coupon_id' ); |
| 343 | $coupon_subquery = "SELECT COUNT(*) FROM ( |
| 344 | {$this->subquery->get_query_statement()} |
| 345 | ) AS tt"; |
| 346 | |
| 347 | $db_records_count = (int) $wpdb->get_var( |
| 348 | $coupon_subquery // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 349 | ); |
| 350 | |
| 351 | $total_results = $db_records_count; |
| 352 | $total_pages = (int) ceil( $db_records_count / $limit_params['per_page'] ); |
| 353 | if ( $query_args['page'] < 1 || $query_args['page'] > $total_pages ) { |
| 354 | return $data; |
| 355 | } |
| 356 | } |
| 357 | |
| 358 | $coupon_data = $wpdb->get_results( |
| 359 | $coupons_query, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 360 | ARRAY_A |
| 361 | ); |
| 362 | if ( null === $coupon_data ) { |
| 363 | return $data; |
| 364 | } |
| 365 | |
| 366 | $this->include_extended_info( $coupon_data, $query_args ); |
| 367 | |
| 368 | $coupon_data = array_map( array( $this, 'cast_numbers' ), $coupon_data ); |
| 369 | $data = (object) array( |
| 370 | 'data' => $coupon_data, |
| 371 | 'total' => $total_results, |
| 372 | 'pages' => $total_pages, |
| 373 | 'page_no' => (int) $query_args['page'], |
| 374 | ); |
| 375 | |
| 376 | return $data; |
| 377 | } |
| 378 | |
| 379 | /** |
| 380 | * Create or update an an entry in the wc_order_coupon_lookup table for an order. |
| 381 | * |
| 382 | * @since 3.5.0 |
| 383 | * @param int $order_id Order ID. |
| 384 | * @return int|bool Returns -1 if order won't be processed, or a boolean indicating processing success. |
| 385 | */ |
| 386 | public static function sync_order_coupons( $order_id ) { |
| 387 | global $wpdb; |
| 388 | |
| 389 | $order = wc_get_order( $order_id ); |
| 390 | |
| 391 | if ( ! $order ) { |
| 392 | return -1; |
| 393 | } |
| 394 | |
| 395 | // Refunds don't affect coupon stats so return successfully if one is called here. |
| 396 | if ( 'shop_order_refund' === $order->get_type() ) { |
| 397 | return true; |
| 398 | } |
| 399 | |
| 400 | $table_name = self::get_db_table_name(); |
| 401 | $existing_items = $wpdb->get_col( |
| 402 | $wpdb->prepare( |
| 403 | // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 404 | "SELECT coupon_id FROM {$table_name} WHERE order_id = %d", |
| 405 | $order_id |
| 406 | ) |
| 407 | ); |
| 408 | $existing_items = array_flip( $existing_items ); |
| 409 | $coupon_items = $order->get_items( OrderItemType::COUPON ); |
| 410 | $coupon_items_count = count( $coupon_items ); |
| 411 | $num_updated = 0; |
| 412 | $num_deleted = 0; |
| 413 | |
| 414 | foreach ( $coupon_items as $coupon_item ) { |
| 415 | $coupon_id = self::get_coupon_id( $coupon_item ); |
| 416 | unset( $existing_items[ $coupon_id ] ); |
| 417 | |
| 418 | if ( ! $coupon_id ) { |
| 419 | // Insert a unique, but obviously invalid ID for this deleted coupon. |
| 420 | ++$num_deleted; |
| 421 | $coupon_id = -1 * $num_deleted; |
| 422 | } |
| 423 | |
| 424 | $result = $wpdb->replace( |
| 425 | self::get_db_table_name(), |
| 426 | array( |
| 427 | 'order_id' => $order_id, |
| 428 | 'coupon_id' => $coupon_id, |
| 429 | 'discount_amount' => $coupon_item->get_discount(), |
| 430 | 'date_created' => $order->get_date_created( 'edit' )->date( TimeInterval::$sql_datetime_format ), |
| 431 | ), |
| 432 | array( |
| 433 | '%d', |
| 434 | '%d', |
| 435 | '%f', |
| 436 | '%s', |
| 437 | ) |
| 438 | ); |
| 439 | |
| 440 | /** |
| 441 | * Fires when coupon's reports are updated. |
| 442 | * |
| 443 | * @param int $coupon_id Coupon ID. |
| 444 | * @param int $order_id Order ID. |
| 445 | */ |
| 446 | do_action( 'woocommerce_analytics_update_coupon', $coupon_id, $order_id ); |
| 447 | |
| 448 | // Sum the rows affected. Using REPLACE can affect 2 rows if the row already exists. |
| 449 | $num_updated += 2 === intval( $result ) ? 1 : intval( $result ); |
| 450 | } |
| 451 | |
| 452 | if ( ! empty( $existing_items ) ) { |
| 453 | $existing_items = array_flip( $existing_items ); |
| 454 | $format = array_fill( 0, count( $existing_items ), '%d' ); |
| 455 | $format = implode( ',', $format ); |
| 456 | array_unshift( $existing_items, $order_id ); |
| 457 | $wpdb->query( |
| 458 | $wpdb->prepare( |
| 459 | // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 460 | "DELETE FROM {$table_name} WHERE order_id = %d AND coupon_id in ({$format})", |
| 461 | $existing_items |
| 462 | ) |
| 463 | ); |
| 464 | } |
| 465 | |
| 466 | return ( $coupon_items_count === $num_updated ); |
| 467 | } |
| 468 | |
| 469 | /** |
| 470 | * Clean coupons data when an order is deleted. |
| 471 | * |
| 472 | * @param int $order_id Order ID. |
| 473 | */ |
| 474 | public static function sync_on_order_delete( $order_id ) { |
| 475 | global $wpdb; |
| 476 | |
| 477 | $wpdb->delete( self::get_db_table_name(), array( 'order_id' => $order_id ) ); |
| 478 | /** |
| 479 | * Fires when coupon's reports are removed from database. |
| 480 | * |
| 481 | * @param int $coupon_id Coupon ID. |
| 482 | * @param int $order_id Order ID. |
| 483 | */ |
| 484 | do_action( 'woocommerce_analytics_delete_coupon', 0, $order_id ); |
| 485 | |
| 486 | ReportsCache::invalidate(); |
| 487 | } |
| 488 | |
| 489 | /** |
| 490 | * Gets coupons based on the provided arguments. |
| 491 | * |
| 492 | * @todo Upon core merge, including this in core's `class-wc-coupon-data-store-cpt.php` might make more sense. |
| 493 | * @param array $args Array of args to filter the query by. Supports `include`. |
| 494 | * @return array Array of results. |
| 495 | */ |
| 496 | public function get_coupons( $args ) { |
| 497 | global $wpdb; |
| 498 | $query = "SELECT ID, post_title FROM {$wpdb->posts} WHERE post_type='shop_coupon'"; |
| 499 | |
| 500 | $included_coupons = $this->get_included_coupons( $args, 'include' ); |
| 501 | if ( ! empty( $included_coupons ) ) { |
| 502 | $query .= " AND ID IN ({$included_coupons})"; |
| 503 | } |
| 504 | |
| 505 | return $wpdb->get_results( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 506 | } |
| 507 | |
| 508 | /** |
| 509 | * Initialize query objects. |
| 510 | */ |
| 511 | protected function initialize_queries() { |
| 512 | $this->clear_all_clauses(); |
| 513 | $this->subquery = new SqlQuery( $this->context . '_subquery' ); |
| 514 | $this->subquery->add_sql_clause( 'from', self::get_db_table_name() ); |
| 515 | $this->subquery->add_sql_clause( 'group_by', 'coupon_id' ); |
| 516 | } |
| 517 | } |
| 518 |