DataStore.php
254 lines
| 1 | <?php |
| 2 | /** |
| 3 | * API\Reports\Taxes\Stats\DataStore class file. |
| 4 | */ |
| 5 | |
| 6 | namespace Automattic\WooCommerce\Admin\API\Reports\Taxes\Stats; |
| 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\StatsDataStoreTrait; |
| 14 | |
| 15 | /** |
| 16 | * API\Reports\Taxes\Stats\DataStore. |
| 17 | */ |
| 18 | class DataStore extends ReportsDataStore implements DataStoreInterface { |
| 19 | use StatsDataStoreTrait; |
| 20 | |
| 21 | /** |
| 22 | * Table used to get the data. |
| 23 | * |
| 24 | * @override ReportsDataStore::$table_name |
| 25 | * |
| 26 | * @var string |
| 27 | */ |
| 28 | protected static $table_name = 'wc_order_tax_lookup'; |
| 29 | |
| 30 | /** |
| 31 | * Cache identifier. |
| 32 | * |
| 33 | * @override ReportsDataStore::$cache_key |
| 34 | * |
| 35 | * @var string |
| 36 | */ |
| 37 | protected $cache_key = 'taxes_stats'; |
| 38 | |
| 39 | /** |
| 40 | * Mapping columns to data type to return correct response types. |
| 41 | * |
| 42 | * @override ReportsDataStore::$column_types |
| 43 | * |
| 44 | * @var array |
| 45 | */ |
| 46 | protected $column_types = array( |
| 47 | 'tax_codes' => 'intval', |
| 48 | 'total_tax' => 'floatval', |
| 49 | 'order_tax' => 'floatval', |
| 50 | 'shipping_tax' => 'floatval', |
| 51 | 'orders_count' => 'intval', |
| 52 | ); |
| 53 | |
| 54 | /** |
| 55 | * Data store context used to pass to filters. |
| 56 | * |
| 57 | * @override ReportsDataStore::$context |
| 58 | * |
| 59 | * @var string |
| 60 | */ |
| 61 | protected $context = 'taxes_stats'; |
| 62 | |
| 63 | /** |
| 64 | * Assign report columns once full table name has been assigned. |
| 65 | * |
| 66 | * @override ReportsDataStore::assign_report_columns() |
| 67 | */ |
| 68 | protected function assign_report_columns() { |
| 69 | $table_name = self::get_db_table_name(); |
| 70 | $this->report_columns = array( |
| 71 | 'tax_codes' => 'COUNT(DISTINCT tax_rate_id) as tax_codes', |
| 72 | 'total_tax' => 'SUM(total_tax) AS total_tax', |
| 73 | 'order_tax' => 'SUM(order_tax) as order_tax', |
| 74 | 'shipping_tax' => 'SUM(shipping_tax) as shipping_tax', |
| 75 | 'orders_count' => "COUNT( DISTINCT ( CASE WHEN parent_id = 0 THEN {$table_name}.order_id END ) ) as orders_count", |
| 76 | ); |
| 77 | } |
| 78 | |
| 79 | /** |
| 80 | * Updates the database query with parameters used for Taxes Stats report |
| 81 | * |
| 82 | * @see Automattic\WooCommerce\Admin\API\Reports\Taxes\DataStore::add_sql_query_params() |
| 83 | * @param array $query_args Query arguments supplied by the user. |
| 84 | */ |
| 85 | protected function update_sql_query_params( $query_args ) { |
| 86 | global $wpdb; |
| 87 | |
| 88 | $order_tax_lookup_table = self::get_db_table_name(); |
| 89 | |
| 90 | $this->add_time_period_sql_params( $query_args, $order_tax_lookup_table ); |
| 91 | $taxes_where_clause = ''; |
| 92 | $order_status_filter = $this->get_status_subquery( $query_args ); |
| 93 | |
| 94 | if ( isset( $query_args['taxes'] ) && ! empty( $query_args['taxes'] ) ) { |
| 95 | $allowed_taxes = self::get_filtered_ids( $query_args, 'taxes' ); |
| 96 | /* phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- `$allowed_taxes` was prepared by get_filtered_ids above. */ |
| 97 | $taxes_where_clause .= " AND {$order_tax_lookup_table}.tax_rate_id IN ({$allowed_taxes})"; |
| 98 | /* phpcs:enable */ |
| 99 | } |
| 100 | |
| 101 | if ( $order_status_filter ) { |
| 102 | $taxes_where_clause .= " AND ( {$order_status_filter} )"; |
| 103 | } |
| 104 | |
| 105 | $this->total_query->add_sql_clause( 'where', $taxes_where_clause ); |
| 106 | |
| 107 | $this->add_intervals_sql_params( $query_args, $order_tax_lookup_table ); |
| 108 | $this->interval_query->add_sql_clause( 'where', $taxes_where_clause ); |
| 109 | $this->interval_query->add_sql_clause( 'select', $this->get_sql_clause( 'select' ) . ' AS time_interval' ); |
| 110 | $this->interval_query->add_sql_clause( 'where_time', $this->get_sql_clause( 'where_time' ) ); |
| 111 | } |
| 112 | |
| 113 | /** |
| 114 | * Get taxes associated with a store. |
| 115 | * |
| 116 | * @param array $args Array of args to filter the query by. Supports `include`. |
| 117 | * @return array An array of all taxes. |
| 118 | */ |
| 119 | public static function get_taxes( $args ) { |
| 120 | global $wpdb; |
| 121 | $query = " |
| 122 | SELECT |
| 123 | tax_rate_id, |
| 124 | tax_rate_country, |
| 125 | tax_rate_state, |
| 126 | tax_rate_name, |
| 127 | tax_rate_priority |
| 128 | FROM {$wpdb->prefix}woocommerce_tax_rates |
| 129 | "; |
| 130 | if ( ! empty( $args['include'] ) ) { |
| 131 | $args['include'] = (array) $args['include']; |
| 132 | /* phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared */ |
| 133 | $tax_placeholders = implode( ',', array_fill( 0, count( $args['include'] ), '%d' ) ); |
| 134 | $query .= $wpdb->prepare( " WHERE tax_rate_id IN ({$tax_placeholders})", $args['include'] ); |
| 135 | /* phpcs:enable */ |
| 136 | } |
| 137 | return $wpdb->get_results( $query, ARRAY_A ); // WPCS: cache ok, DB call ok, unprepared SQL ok. |
| 138 | } |
| 139 | |
| 140 | /** |
| 141 | * Get the default query arguments to be used by get_data(). |
| 142 | * These defaults are only partially applied when used via REST API, as that has its own defaults. |
| 143 | * |
| 144 | * @override ReportsDataStore::get_default_query_vars() |
| 145 | * |
| 146 | * @return array Query parameters. |
| 147 | */ |
| 148 | public function get_default_query_vars() { |
| 149 | $defaults = parent::get_default_query_vars(); |
| 150 | $defaults['orderby'] = 'tax_rate_id'; |
| 151 | $defaults['taxes'] = array(); |
| 152 | |
| 153 | return $defaults; |
| 154 | } |
| 155 | |
| 156 | /** |
| 157 | * Returns the report data based on normalized parameters. |
| 158 | * Will be called by `get_data` if there is no data in cache. |
| 159 | * |
| 160 | * @override ReportsDataStore::get_noncached_data() |
| 161 | * |
| 162 | * @see get_data |
| 163 | * @see get_noncached_stats_data |
| 164 | * @param array $query_args Query parameters. |
| 165 | * @param array $params Query limit parameters. |
| 166 | * @param stdClass $data Reference to the data object to fill. |
| 167 | * @param int $expected_interval_count Number of expected intervals. |
| 168 | * @return stdClass|WP_Error Data object `{ totals: *, intervals: array, total: int, pages: int, page_no: int }`, or error. |
| 169 | */ |
| 170 | public function get_noncached_stats_data( $query_args, $params, &$data, $expected_interval_count ) { |
| 171 | global $wpdb; |
| 172 | |
| 173 | $table_name = self::get_db_table_name(); |
| 174 | |
| 175 | $this->initialize_queries(); |
| 176 | |
| 177 | $selections = $this->selected_columns( $query_args ); |
| 178 | $order_stats_join = "JOIN {$wpdb->prefix}wc_order_stats ON {$table_name}.order_id = {$wpdb->prefix}wc_order_stats.order_id"; |
| 179 | $this->update_sql_query_params( $query_args ); |
| 180 | $this->interval_query->add_sql_clause( 'join', $order_stats_join ); |
| 181 | |
| 182 | $db_intervals = $wpdb->get_col( |
| 183 | // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- cache ok, DB call ok, unprepared SQL ok. |
| 184 | $this->interval_query->get_query_statement() |
| 185 | ); |
| 186 | $db_interval_count = count( $db_intervals ); |
| 187 | |
| 188 | $this->total_query->add_sql_clause( 'select', $selections ); |
| 189 | $this->total_query->add_sql_clause( 'join', $order_stats_join ); |
| 190 | $this->total_query->add_sql_clause( 'where_time', $this->get_sql_clause( 'where_time' ) ); |
| 191 | |
| 192 | $totals = $wpdb->get_results( |
| 193 | // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- cache ok, DB call ok, unprepared SQL ok. |
| 194 | $this->total_query->get_query_statement(), |
| 195 | ARRAY_A |
| 196 | ); |
| 197 | |
| 198 | if ( null === $totals ) { |
| 199 | return new \WP_Error( 'woocommerce_analytics_taxes_stats_result_failed', __( 'Sorry, fetching revenue data failed.', 'woocommerce' ) ); |
| 200 | } |
| 201 | |
| 202 | // phpcs:ignore Generic.Commenting.Todo.TaskFound |
| 203 | // @todo remove these assignments when refactoring segmenter classes to use query objects. |
| 204 | $totals_query = array( |
| 205 | 'from_clause' => $this->total_query->get_sql_clause( 'join' ), |
| 206 | 'where_time_clause' => $this->total_query->get_sql_clause( 'where_time' ), |
| 207 | 'where_clause' => $this->total_query->get_sql_clause( 'where' ), |
| 208 | ); |
| 209 | $intervals_query = array( |
| 210 | 'select_clause' => $this->get_sql_clause( 'select' ), |
| 211 | 'from_clause' => $this->interval_query->get_sql_clause( 'join' ), |
| 212 | 'where_time_clause' => $this->interval_query->get_sql_clause( 'where_time' ), |
| 213 | 'where_clause' => $this->interval_query->get_sql_clause( 'where' ), |
| 214 | ); |
| 215 | $segmenter = new Segmenter( $query_args, $this->report_columns ); |
| 216 | $totals[0]['segments'] = $segmenter->get_totals_segments( $totals_query, $table_name ); |
| 217 | |
| 218 | $this->update_intervals_sql_params( $query_args, $db_interval_count, $expected_interval_count, $table_name ); |
| 219 | |
| 220 | if ( '' !== $selections ) { |
| 221 | $this->interval_query->add_sql_clause( 'select', ', ' . $selections ); |
| 222 | } |
| 223 | |
| 224 | $this->interval_query->add_sql_clause( 'select', ", MAX({$table_name}.date_created) AS datetime_anchor" ); |
| 225 | $this->interval_query->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) ); |
| 226 | $this->interval_query->add_sql_clause( 'limit', $this->get_sql_clause( 'limit' ) ); |
| 227 | |
| 228 | $intervals = $wpdb->get_results( |
| 229 | // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- cache ok, DB call ok, unprepared SQL ok. |
| 230 | $this->interval_query->get_query_statement(), |
| 231 | ARRAY_A |
| 232 | ); |
| 233 | |
| 234 | if ( null === $intervals ) { |
| 235 | return new \WP_Error( 'woocommerce_analytics_taxes_stats_result_failed', __( 'Sorry, fetching tax data failed.', 'woocommerce' ) ); |
| 236 | } |
| 237 | |
| 238 | $totals = (object) $this->cast_numbers( $totals[0] ); |
| 239 | |
| 240 | $data->totals = $totals; |
| 241 | $data->intervals = $intervals; |
| 242 | |
| 243 | if ( TimeInterval::intervals_missing( $expected_interval_count, $db_interval_count, $params['per_page'], $query_args['page'], $query_args['order'], $query_args['orderby'], count( $intervals ) ) ) { |
| 244 | $this->fill_in_missing_intervals( $db_intervals, $query_args['adj_after'], $query_args['adj_before'], $query_args['interval'], $data ); |
| 245 | $this->sort_intervals( $data, $query_args['orderby'], $query_args['order'] ); |
| 246 | $this->remove_extra_records( $data, $query_args['page'], $params['per_page'], $db_interval_count, $expected_interval_count, $query_args['orderby'], $query_args['order'] ); |
| 247 | } else { |
| 248 | $this->update_interval_boundary_dates( $query_args['after'], $query_args['before'], $query_args['interval'], $data->intervals ); |
| 249 | } |
| 250 | $segmenter->add_intervals_segments( $data, $intervals_query, $table_name ); |
| 251 | return $data; |
| 252 | } |
| 253 | } |
| 254 |