DataStore.php
132 lines
| 1 | <?php |
| 2 | /** |
| 3 | * API\Reports\Customers\Stats\DataStore class file. |
| 4 | */ |
| 5 | |
| 6 | namespace Automattic\WooCommerce\Admin\API\Reports\Customers\Stats; |
| 7 | |
| 8 | defined( 'ABSPATH' ) || exit; |
| 9 | |
| 10 | use Automattic\WooCommerce\Admin\API\Reports\Customers\DataStore as CustomersDataStore; |
| 11 | use Automattic\WooCommerce\Admin\API\Reports\DataStore as ReportsDataStore; |
| 12 | use Automattic\WooCommerce\Admin\API\Reports\DataStoreInterface; |
| 13 | |
| 14 | /** |
| 15 | * API\Reports\Customers\Stats\DataStore. |
| 16 | */ |
| 17 | class DataStore extends CustomersDataStore implements DataStoreInterface { |
| 18 | /** |
| 19 | * Mapping columns to data type to return correct response types. |
| 20 | * |
| 21 | * @override CustomersDataStore::$column_types |
| 22 | * |
| 23 | * @var array |
| 24 | */ |
| 25 | protected $column_types = array( |
| 26 | 'customers_count' => 'intval', |
| 27 | 'avg_orders_count' => 'floatval', |
| 28 | 'avg_total_spend' => 'floatval', |
| 29 | 'avg_avg_order_value' => 'floatval', |
| 30 | ); |
| 31 | |
| 32 | /** |
| 33 | * Cache identifier. |
| 34 | * |
| 35 | * @override CustomersDataStore::$cache_key |
| 36 | * |
| 37 | * @var string |
| 38 | */ |
| 39 | protected $cache_key = 'customers_stats'; |
| 40 | |
| 41 | /** |
| 42 | * Data store context used to pass to filters. |
| 43 | * |
| 44 | * @override CustomersDataStore::$context |
| 45 | * |
| 46 | * @var string |
| 47 | */ |
| 48 | protected $context = 'customers_stats'; |
| 49 | |
| 50 | /** |
| 51 | * Assign report columns once full table name has been assigned. |
| 52 | * |
| 53 | * @override CustomersDataStore::assign_report_columns() |
| 54 | */ |
| 55 | protected function assign_report_columns() { |
| 56 | $this->report_columns = array( |
| 57 | 'customers_count' => 'COUNT( * ) as customers_count', |
| 58 | 'avg_orders_count' => 'AVG( orders_count ) as avg_orders_count', |
| 59 | 'avg_total_spend' => 'AVG( total_spend ) as avg_total_spend', |
| 60 | 'avg_avg_order_value' => 'AVG( avg_order_value ) as avg_avg_order_value', |
| 61 | ); |
| 62 | } |
| 63 | |
| 64 | /** |
| 65 | * Get the default query arguments to be used by get_data(). |
| 66 | * These defaults are only partially applied when used via REST API, as that has its own defaults. |
| 67 | * |
| 68 | * @override CustomersDataStore::get_default_query_vars() |
| 69 | * |
| 70 | * @return array Query parameters. |
| 71 | */ |
| 72 | public function get_default_query_vars() { |
| 73 | $defaults = ReportsDataStore::get_default_query_vars(); |
| 74 | $defaults['orderby'] = 'date_registered'; |
| 75 | // Do not set `order_before` and `order_after` here, like in the parent class. |
| 76 | return $defaults; |
| 77 | } |
| 78 | |
| 79 | /** |
| 80 | * Returns the report data based on normalized parameters. |
| 81 | * Will be called by `get_data` if there is no data in cache. |
| 82 | * |
| 83 | * @override CustomersDataStore::get_noncached_data() |
| 84 | * |
| 85 | * @see get_data |
| 86 | * @param array $query_args Query parameters. |
| 87 | * @return stdClass|WP_Error Data object `{ totals: *, intervals: array, total: int, pages: int, page_no: int }`, or error. |
| 88 | */ |
| 89 | public function get_noncached_data( $query_args ) { |
| 90 | global $wpdb; |
| 91 | $this->initialize_queries(); |
| 92 | |
| 93 | $data = (object) array( |
| 94 | 'customers_count' => 0, |
| 95 | 'avg_orders_count' => 0, |
| 96 | 'avg_total_spend' => 0.0, |
| 97 | 'avg_avg_order_value' => 0.0, |
| 98 | ); |
| 99 | |
| 100 | $selections = $this->selected_columns( $query_args ); |
| 101 | $this->add_sql_query_params( $query_args ); |
| 102 | // Clear SQL clauses set for parent class queries that are different here. |
| 103 | $this->subquery->clear_sql_clause( 'select' ); |
| 104 | $this->subquery->add_sql_clause( 'select', 'SUM( total_sales ) AS total_spend,' ); |
| 105 | $this->subquery->add_sql_clause( |
| 106 | 'select', |
| 107 | 'SUM( CASE WHEN parent_id = 0 THEN 1 END ) as orders_count,' |
| 108 | ); |
| 109 | $this->subquery->add_sql_clause( |
| 110 | 'select', |
| 111 | 'CASE WHEN SUM( CASE WHEN parent_id = 0 THEN 1 ELSE 0 END ) = 0 THEN NULL ELSE SUM( total_sales ) / SUM( CASE WHEN parent_id = 0 THEN 1 ELSE 0 END ) END AS avg_order_value' |
| 112 | ); |
| 113 | |
| 114 | $this->clear_sql_clause( array( 'order_by', 'limit' ) ); |
| 115 | $this->add_sql_clause( 'select', $selections ); |
| 116 | $this->add_sql_clause( 'from', "({$this->subquery->get_query_statement()}) AS tt" ); |
| 117 | |
| 118 | $report_data = $wpdb->get_results( |
| 119 | $this->get_query_statement(), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 120 | ARRAY_A |
| 121 | ); |
| 122 | |
| 123 | if ( null === $report_data ) { |
| 124 | return $data; |
| 125 | } |
| 126 | |
| 127 | $data = (object) $this->cast_numbers( $report_data[0] ); |
| 128 | |
| 129 | return $data; |
| 130 | } |
| 131 | } |
| 132 |