1 <?php
2 if ( ! defined( 'ABSPATH' ) ) exit;
3
4 5 6 7 8 9 10 11 12 13
14 class WC_Admin_Report {
15
16 public $chart_interval;
17 public $group_by_query;
18 public $barwidth;
19 public $chart_groupby;
20 public $start_date;
21 public $end_date;
22
23 24 25 26 27 28 29 30 31 32 33 34 35 36
37 public function get_order_report_data( $args = array() ) {
38 global $wpdb;
39
40 $defaults = array(
41 'data' => array(),
42 'where' => array(),
43 'where_meta' => array(),
44 'query_type' => 'get_row',
45 'group_by' => '',
46 'order_by' => '',
47 'limit' => '',
48 'filter_range' => false,
49 'nocache' => false,
50 'debug' => false
51 );
52
53 $args = wp_parse_args( $args, $defaults );
54
55 extract( $args );
56
57 if ( empty( $data ) )
58 return false;
59
60 $select = array();
61
62 foreach ( $data as $key => $value ) {
63 $distinct = '';
64
65 if ( isset( $value['distinct'] ) )
66 $distinct = 'DISTINCT';
67
68 if ( $value['type'] == 'meta' )
69 $get_key = "meta_{$key}.meta_value";
70 elseif( $value['type'] == 'post_data' )
71 $get_key = "posts.{$key}";
72 elseif( $value['type'] == 'order_item_meta' )
73 $get_key = "order_item_meta_{$key}.meta_value";
74 elseif( $value['type'] == 'order_item' )
75 $get_key = "order_items.{$key}";
76
77 if ( $value['function'] )
78 $get = "{$value['function']}({$distinct} {$get_key})";
79 else
80 $get = "{$distinct} {$get_key}";
81
82 $select[] = "{$get} as {$value['name']}";
83 }
84
85 $query['select'] = "SELECT " . implode( ',', $select );
86 $query['from'] = "FROM {$wpdb->posts} AS posts";
87
88
89 $joins = array();
90 $joins['rel'] = "LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID";
91 $joins['tax'] = "LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )";
92 $joins['term'] = "LEFT JOIN {$wpdb->terms} AS term USING( term_id )";
93
94 foreach ( $data as $key => $value ) {
95 if ( $value['type'] == 'meta' ) {
96
97 $joins["meta_{$key}"] = "LEFT JOIN {$wpdb->postmeta} AS meta_{$key} ON posts.ID = meta_{$key}.post_id";
98
99 } elseif ( $value['type'] == 'order_item_meta' ) {
100
101 $joins["order_items"] = "LEFT JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id";
102 $joins["order_item_meta_{$key}"] = "LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta_{$key} ON order_items.order_item_id = order_item_meta_{$key}.order_item_id";
103
104 } elseif ( $value['type'] == 'order_item' ) {
105
106 $joins["order_items"] = "LEFT JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id";
107
108 }
109 }
110
111 if ( ! empty( $where_meta ) ) {
112 foreach ( $where_meta as $value ) {
113 if ( ! is_array( $value ) )
114 continue;
115
116 $key = is_array( $value['meta_key'] ) ? $value['meta_key'][0] . '_array' : $value['meta_key'];
117
118 if ( isset( $value['type'] ) && $value['type'] == 'order_item_meta' ) {
119
120 $joins["order_items"] = "LEFT JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id";
121 $joins["order_item_meta_{$key}"] = "LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta_{$key} ON order_items.order_item_id = order_item_meta_{$key}.order_item_id";
122
123 } else {
124
125 $joins["meta_{$key}"] = "LEFT JOIN {$wpdb->postmeta} AS meta_{$key} ON posts.ID = meta_{$key}.post_id";
126 }
127 }
128 }
129
130 $query['join'] = implode( ' ', $joins );
131
132 $query['where'] = "
133 WHERE posts.post_type = 'shop_order'
134 AND posts.post_status = 'publish'
135 AND tax.taxonomy = 'shop_order_status'
136 AND term.slug IN ('" . implode( "','", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "')
137 ";
138
139 if ( $filter_range ) {
140 $query['where'] .= "
141 AND post_date >= '" . date('Y-m-d', $this->start_date ) . "'
142 AND post_date < '" . date('Y-m-d', strtotime( '+1 DAY', $this->end_date ) ) . "'
143 ";
144 }
145
146 foreach ( $data as $key => $value ) {
147 if ( $value['type'] == 'meta' ) {
148
149 $query['where'] .= " AND meta_{$key}.meta_key = '{$key}'";
150
151 } elseif ( $value['type'] == 'order_item_meta' ) {
152
153 $query['where'] .= " AND order_items.order_item_type = '{$value['order_item_type']}'";
154 $query['where'] .= " AND order_item_meta_{$key}.meta_key = '{$key}'";
155
156 }
157 }
158
159 if ( ! empty( $where_meta ) ) {
160 $relation = isset( $where_meta['relation'] ) ? $where_meta['relation'] : 'AND';
161
162 $query['where'] .= " AND (";
163
164 foreach ( $where_meta as $index => $value ) {
165 if ( ! is_array( $value ) )
166 continue;
167
168 $key = is_array( $value['meta_key'] ) ? $value['meta_key'][0] . '_array' : $value['meta_key'];
169
170 if ( strtolower( $value['operator'] ) == 'in' ) {
171 if ( is_array( $value['meta_value'] ) )
172 $value['meta_value'] = implode( "','", $value['meta_value'] );
173 if ( ! empty( $value['meta_value'] ) )
174 $where_value = "IN ('{$value['meta_value']}')";
175 } else {
176 $where_value = "{$value['operator']} '{$value['meta_value']}'";
177 }
178
179 if ( ! empty( $where_value ) ) {
180 if ( $index > 0 )
181 $query['where'] .= ' ' . $relation;
182
183 if ( isset( $value['type'] ) && $value['type'] == 'order_item_meta' ) {
184 if ( is_array( $value['meta_key'] ) )
185 $query['where'] .= " ( order_item_meta_{$key}.meta_key IN ('" . implode( "','", $value['meta_key'] ) . "')";
186 else
187 $query['where'] .= " ( order_item_meta_{$key}.meta_key = '{$value['meta_key']}'";
188
189 $query['where'] .= " AND order_item_meta_{$key}.meta_value {$where_value} )";
190 } else {
191 if ( is_array( $value['meta_key'] ) )
192 $query['where'] .= " ( meta_{$key}.meta_key IN ('" . implode( "','", $value['meta_key'] ) . "')";
193 else
194 $query['where'] .= " ( meta_{$key}.meta_key = '{$value['meta_key']}'";
195
196 $query['where'] .= " AND meta_{$key}.meta_value {$where_value} )";
197 }
198 }
199 }
200
201 $query['where'] .= ")";
202 }
203
204 if ( ! empty( $where ) ) {
205 foreach ( $where as $value ) {
206 if ( strtolower( $value['operator'] ) == 'in' ) {
207 if ( is_array( $value['value'] ) )
208 $value['value'] = implode( "','", $value['value'] );
209 if ( ! empty( $value['value'] ) )
210 $where_value = "IN ('{$value['value']}')";
211 } else {
212 $where_value = "{$value['operator']} '{$value['value']}'";
213 }
214
215 if ( ! empty( $where_value ) )
216 $query['where'] .= " AND {$value['key']} {$where_value}";
217 }
218 }
219
220 if ( $group_by ) {
221 $query['group_by'] = "GROUP BY {$group_by}";
222 }
223
224 if ( $order_by ) {
225 $query['order_by'] = "ORDER BY {$order_by}";
226 }
227
228 if ( $limit ) {
229 $query['limit'] = "LIMIT {$limit}";
230 }
231
232 $query = apply_filters( 'woocommerce_reports_get_order_report_query', $query );
233 $query = implode( ' ', $query );
234 $query_hash = md5( $query_type . $query );
235 $cached_results = get_transient( strtolower( get_class( $this ) ) );
236
237 if ( $debug ) {
238 var_dump( $query );
239 }
240
241 if ( $debug || $nocache || false === $cached_results || ! isset( $cached_results[ $query_hash ] ) ) {
242 $cached_results[ $query_hash ] = apply_filters( 'woocommerce_reports_get_order_report_data', $wpdb->$query_type( $query ), $data );
243 set_transient( strtolower( get_class( $this ) ), $cached_results, DAY_IN_SECONDS );
244 }
245
246 $result = $cached_results[ $query_hash ];
247
248 return $result;
249 }
250
251 252 253 254 255 256 257 258 259 260 261
262 public function prepare_chart_data( $data, $date_key, $data_key, $interval, $start_date, $group_by ) {
263 $prepared_data = array();
264 $time = '';
265
266
267 for ( $i = 0; $i <= $interval; $i ++ ) {
268 switch ( $group_by ) {
269 case 'day' :
270 $time = strtotime( date( 'Ymd', strtotime( "+{$i} DAY", $start_date ) ) ) . '000';
271 break;
272 case 'month' :
273 $time = strtotime( date( 'Ym', strtotime( "+{$i} MONTH", $start_date ) ) . '01' ) . '000';
274 break;
275 }
276
277 if ( ! isset( $prepared_data[ $time ] ) )
278 $prepared_data[ $time ] = array( esc_js( $time ), 0 );
279 }
280
281 foreach ( $data as $d ) {
282 switch ( $group_by ) {
283 case 'day' :
284 $time = strtotime( date( 'Ymd', strtotime( $d->$date_key ) ) ) . '000';
285 break;
286 case 'month' :
287 $time = strtotime( date( 'Ym', strtotime( $d->$date_key ) ) . '01' ) . '000';
288 break;
289 }
290
291 if ( ! isset( $prepared_data[ $time ] ) )
292 continue;
293
294 if ( $data_key )
295 $prepared_data[ $time ][1] += $d->$data_key;
296 else
297 $prepared_data[ $time ][1] ++;
298 }
299
300 return $prepared_data;
301 }
302
303 304 305 306 307 308 309 310
311 public function sales_sparkline( $id = '', $days = 7, $type = 'sales' ) {
312
313 if ( $id ) {
314 $meta_key = $type == 'sales' ? '_line_total' : '_qty';
315
316 $data = $this->get_order_report_data( array(
317 'data' => array(
318 '_product_id' => array(
319 'type' => 'order_item_meta',
320 'order_item_type' => 'line_item',
321 'function' => '',
322 'name' => 'product_id'
323 ),
324 $meta_key => array(
325 'type' => 'order_item_meta',
326 'order_item_type' => 'line_item',
327 'function' => 'SUM',
328 'name' => 'sparkline_value'
329 ),
330 'post_date' => array(
331 'type' => 'post_data',
332 'function' => '',
333 'name' => 'post_date'
334 ),
335 ),
336 'where' => array(
337 array(
338 'key' => 'post_date',
339 'value' => date( 'Y-m-d', strtotime( 'midnight -' . ( $days - 1 ) . ' days', current_time( 'timestamp' ) ) ),
340 'operator' => '>'
341 ),
342 array(
343 'key' => 'order_item_meta__product_id.meta_value',
344 'value' => $id,
345 'operator' => '='
346 )
347 ),
348 'group_by' => 'YEAR(post_date), MONTH(post_date), DAY(post_date)',
349 'query_type' => 'get_results',
350 'filter_range' => false
351 ) );
352 } else {
353 $data = $this->get_order_report_data( array(
354 'data' => array(
355 '_order_total' => array(
356 'type' => 'meta',
357 'function' => 'SUM',
358 'name' => 'sparkline_value'
359 ),
360 'post_date' => array(
361 'type' => 'post_data',
362 'function' => '',
363 'name' => 'post_date'
364 ),
365 ),
366 'where' => array(
367 array(
368 'key' => 'post_date',
369 'value' => date( 'Y-m-d', strtotime( 'midnight -' . ( $days - 1 ) . ' days', current_time( 'timestamp' ) ) ),
370 'operator' => '>'
371 )
372 ),
373 'group_by' => 'YEAR(post_date), MONTH(post_date), DAY(post_date)',
374 'query_type' => 'get_results',
375 'filter_range' => false
376 ) );
377 }
378
379 $total = 0;
380 foreach ( $data as $d )
381 $total += $d->sparkline_value;
382
383 if ( $type == 'sales' ) {
384 $tooltip = sprintf( __( 'Sold %s worth in the last %d days', 'woocommerce' ), strip_tags( wc_price( $total ) ), $days );
385 } else {
386 $tooltip = sprintf( _n( 'Sold 1 item in the last %d days', 'Sold %d items in the last %d days', $total, 'woocommerce' ), $total, $days );
387 }
388
389 $sparkline_data = array_values( $this->prepare_chart_data( $data, 'post_date', 'sparkline_value', $days - 1, strtotime( 'midnight -' . ( $days - 1 ) . ' days', current_time( 'timestamp' ) ), 'day' ) );
390
391 return '<span class="wc_sparkline ' . ( $type == 'sales' ? 'lines' : 'bars' ) . ' tips" data-color="#777" data-tip="' . esc_attr( $tooltip ) . '" data-barwidth="' . 60*60*16*1000 . '" data-sparkline="' . esc_attr( json_encode( $sparkline_data ) ) . '"></span>';
392 }
393
394 395 396 397 398
399 public function calculate_current_range( $current_range ) {
400 switch ( $current_range ) {
401 case 'custom' :
402 $this->start_date = strtotime( sanitize_text_field( $_GET['start_date'] ) );
403 $this->end_date = strtotime( 'midnight', strtotime( sanitize_text_field( $_GET['end_date'] ) ) );
404
405 if ( ! $this->end_date )
406 $this->end_date = current_time('timestamp');
407
408 $interval = 0;
409 $min_date = $this->start_date;
410 while ( ( $min_date = strtotime( "+1 MONTH", $min_date ) ) <= $this->end_date ) {
411 $interval ++;
412 }
413
414
415 if ( $interval > 3 )
416 $this->chart_groupby = 'month';
417 else
418 $this->chart_groupby = 'day';
419 break;
420 case 'year' :
421 $this->start_date = strtotime( date( 'Y-01-01', current_time('timestamp') ) );
422 $this->end_date = strtotime( 'midnight', current_time( 'timestamp' ) );
423 $this->chart_groupby = 'month';
424 break;
425 case 'last_month' :
426 $this->start_date = strtotime( date( 'Y-m-01', strtotime( '-1 MONTH', current_time('timestamp') ) ) );
427 $this->end_date = strtotime( date( 'Y-m-t', strtotime( '-1 MONTH', current_time('timestamp') ) ) );
428 $this->chart_groupby = 'day';
429 break;
430 case 'month' :
431 $this->start_date = strtotime( date( 'Y-m-01', current_time('timestamp') ) );
432 $this->end_date = strtotime( 'midnight', current_time( 'timestamp' ) );
433 $this->chart_groupby = 'day';
434 break;
435 case '7day' :
436 $this->start_date = strtotime( '-6 days', current_time( 'timestamp' ) );
437 $this->end_date = strtotime( 'midnight', current_time( 'timestamp' ) );
438 $this->chart_groupby = 'day';
439 break;
440 }
441
442
443 switch ( $this->chart_groupby ) {
444 case 'day' :
445 $this->group_by_query = 'YEAR(post_date), MONTH(post_date), DAY(post_date)';
446 $this->chart_interval = ceil( max( 0, ( $this->end_date - $this->start_date ) / ( 60 * 60 * 24 ) ) );
447 $this->barwidth = 60 * 60 * 24 * 1000;
448 break;
449 case 'month' :
450 $this->group_by_query = 'YEAR(post_date), MONTH(post_date)';
451 $this->chart_interval = 0;
452 $min_date = $this->start_date;
453 while ( ( $min_date = strtotime( "+1 MONTH", $min_date ) ) <= $this->end_date ) {
454 $this->chart_interval ++;
455 }
456 $this->barwidth = 60 * 60 * 24 * 7 * 4 * 1000;
457 break;
458 }
459 }
460
461 462 463 464
465 public function get_main_chart() {}
466
467 468 469 470
471 public function get_chart_legend() {
472 return array();
473 }
474
475 476 477 478
479 public function get_chart_widgets() {
480 return array();
481 }
482
483 484 485
486 public function get_export_button() {}
487
488 489 490
491 public function output_report() {}
492 }
493