WC_Admin_Report::get_order_report_data( array $args = array() )
Get report totals such as order totals and discount amounts.
Description Description
Data example:
‘_order_total’ => array( ‘type’ => ‘meta’, ‘function’ => ‘SUM’, ‘name’ => ‘total_sales’ )
Parameters Parameters
- $args
-
(Optional)
Default value: array()
Return Return
(mixed) depending on query_type
Source Source
File: includes/admin/reports/class-wc-admin-report.php
public function get_order_report_data( $args = array() ) {
global $wpdb;
$default_args = array(
'data' => array(),
'where' => array(),
'where_meta' => array(),
'query_type' => 'get_row',
'group_by' => '',
'order_by' => '',
'limit' => '',
'filter_range' => false,
'nocache' => false,
'debug' => false,
'order_types' => wc_get_order_types( 'reports' ),
'order_status' => array( 'completed', 'processing', 'on-hold' ),
'parent_order_status' => false,
);
$args = apply_filters( 'woocommerce_reports_get_order_report_data_args', $args );
$args = wp_parse_args( $args, $default_args );
extract( $args );
if ( empty( $data ) ) {
return '';
}
$order_status = apply_filters( 'woocommerce_reports_order_statuses', $order_status );
$query = array();
$select = array();
foreach ( $data as $raw_key => $value ) {
$key = sanitize_key( $raw_key );
$distinct = '';
if ( isset( $value['distinct'] ) ) {
$distinct = 'DISTINCT';
}
switch ( $value['type'] ) {
case 'meta':
$get_key = "meta_{$key}.meta_value";
break;
case 'parent_meta':
$get_key = "parent_meta_{$key}.meta_value";
break;
case 'post_data':
$get_key = "posts.{$key}";
break;
case 'order_item_meta':
$get_key = "order_item_meta_{$key}.meta_value";
break;
case 'order_item':
$get_key = "order_items.{$key}";
break;
}
if ( empty( $get_key ) ) {
// Skip to the next foreach iteration else the query will be invalid.
continue;
}
if ( $value['function'] ) {
$get = "{$value['function']}({$distinct} {$get_key})";
} else {
$get = "{$distinct} {$get_key}";
}
$select[] = "{$get} as {$value['name']}";
}
$query['select'] = 'SELECT ' . implode( ',', $select );
$query['from'] = "FROM {$wpdb->posts} AS posts";
// Joins
$joins = array();
foreach ( ( $data + $where ) as $raw_key => $value ) {
$join_type = isset( $value['join_type'] ) ? $value['join_type'] : 'INNER';
$type = isset( $value['type'] ) ? $value['type'] : false;
$key = sanitize_key( $raw_key );
switch ( $type ) {
case 'meta':
$joins[ "meta_{$key}" ] = "{$join_type} JOIN {$wpdb->postmeta} AS meta_{$key} ON ( posts.ID = meta_{$key}.post_id AND meta_{$key}.meta_key = '{$raw_key}' )";
break;
case 'parent_meta':
$joins[ "parent_meta_{$key}" ] = "{$join_type} JOIN {$wpdb->postmeta} AS parent_meta_{$key} ON (posts.post_parent = parent_meta_{$key}.post_id) AND (parent_meta_{$key}.meta_key = '{$raw_key}')";
break;
case 'order_item_meta':
$joins['order_items'] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON (posts.ID = order_items.order_id)";
if ( ! empty( $value['order_item_type'] ) ) {
$joins['order_items'] .= " AND (order_items.order_item_type = '{$value['order_item_type']}')";
}
$joins[ "order_item_meta_{$key}" ] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta_{$key} ON " .
"(order_items.order_item_id = order_item_meta_{$key}.order_item_id) " .
" AND (order_item_meta_{$key}.meta_key = '{$raw_key}')";
break;
case 'order_item':
$joins['order_items'] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id";
break;
}
}
if ( ! empty( $where_meta ) ) {
foreach ( $where_meta as $value ) {
if ( ! is_array( $value ) ) {
continue;
}
$join_type = isset( $value['join_type'] ) ? $value['join_type'] : 'INNER';
$type = isset( $value['type'] ) ? $value['type'] : false;
$key = sanitize_key( is_array( $value['meta_key'] ) ? $value['meta_key'][0] . '_array' : $value['meta_key'] );
if ( 'order_item_meta' === $type ) {
$joins['order_items'] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id";
$joins[ "order_item_meta_{$key}" ] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta_{$key} ON order_items.order_item_id = order_item_meta_{$key}.order_item_id";
} else {
// If we have a where clause for meta, join the postmeta table
$joins[ "meta_{$key}" ] = "{$join_type} JOIN {$wpdb->postmeta} AS meta_{$key} ON posts.ID = meta_{$key}.post_id";
}
}
}
if ( ! empty( $parent_order_status ) ) {
$joins['parent'] = "LEFT JOIN {$wpdb->posts} AS parent ON posts.post_parent = parent.ID";
}
$query['join'] = implode( ' ', $joins );
$query['where'] = "
WHERE posts.post_type IN ( '" . implode( "','", $order_types ) . "' )
";
if ( ! empty( $order_status ) ) {
$query['where'] .= "
AND posts.post_status IN ( 'wc-" . implode( "','wc-", $order_status ) . "')
";
}
if ( ! empty( $parent_order_status ) ) {
if ( ! empty( $order_status ) ) {
$query['where'] .= " AND ( parent.post_status IN ( 'wc-" . implode( "','wc-", $parent_order_status ) . "') OR parent.ID IS NULL ) ";
} else {
$query['where'] .= " AND parent.post_status IN ( 'wc-" . implode( "','wc-", $parent_order_status ) . "') ";
}
}
if ( $filter_range ) {
$query['where'] .= "
AND posts.post_date >= '" . date( 'Y-m-d H:i:s', $this->start_date ) . "'
AND posts.post_date < '" . date( 'Y-m-d H:i:s', strtotime( '+1 DAY', $this->end_date ) ) . "'
";
}
if ( ! empty( $where_meta ) ) {
$relation = isset( $where_meta['relation'] ) ? $where_meta['relation'] : 'AND';
$query['where'] .= ' AND (';
foreach ( $where_meta as $index => $value ) {
if ( ! is_array( $value ) ) {
continue;
}
$key = sanitize_key( is_array( $value['meta_key'] ) ? $value['meta_key'][0] . '_array' : $value['meta_key'] );
if ( strtolower( $value['operator'] ) == 'in' || strtolower( $value['operator'] ) == 'not in' ) {
if ( is_array( $value['meta_value'] ) ) {
$value['meta_value'] = implode( "','", $value['meta_value'] );
}
if ( ! empty( $value['meta_value'] ) ) {
$where_value = "{$value['operator']} ('{$value['meta_value']}')";
}
} else {
$where_value = "{$value['operator']} '{$value['meta_value']}'";
}
if ( ! empty( $where_value ) ) {
if ( $index > 0 ) {
$query['where'] .= ' ' . $relation;
}
if ( isset( $value['type'] ) && 'order_item_meta' === $value['type'] ) {
if ( is_array( $value['meta_key'] ) ) {
$query['where'] .= " ( order_item_meta_{$key}.meta_key IN ('" . implode( "','", $value['meta_key'] ) . "')";
} else {
$query['where'] .= " ( order_item_meta_{$key}.meta_key = '{$value['meta_key']}'";
}
$query['where'] .= " AND order_item_meta_{$key}.meta_value {$where_value} )";
} else {
if ( is_array( $value['meta_key'] ) ) {
$query['where'] .= " ( meta_{$key}.meta_key IN ('" . implode( "','", $value['meta_key'] ) . "')";
} else {
$query['where'] .= " ( meta_{$key}.meta_key = '{$value['meta_key']}'";
}
$query['where'] .= " AND meta_{$key}.meta_value {$where_value} )";
}
}
}
$query['where'] .= ')';
}
if ( ! empty( $where ) ) {
foreach ( $where as $value ) {
if ( strtolower( $value['operator'] ) == 'in' || strtolower( $value['operator'] ) == 'not in' ) {
if ( is_array( $value['value'] ) ) {
$value['value'] = implode( "','", $value['value'] );
}
if ( ! empty( $value['value'] ) ) {
$where_value = "{$value['operator']} ('{$value['value']}')";
}
} else {
$where_value = "{$value['operator']} '{$value['value']}'";
}
if ( ! empty( $where_value ) ) {
$query['where'] .= " AND {$value['key']} {$where_value}";
}
}
}
if ( $group_by ) {
$query['group_by'] = "GROUP BY {$group_by}";
}
if ( $order_by ) {
$query['order_by'] = "ORDER BY {$order_by}";
}
if ( $limit ) {
$query['limit'] = "LIMIT {$limit}";
}
$query = apply_filters( 'woocommerce_reports_get_order_report_query', $query );
$query = implode( ' ', $query );
if ( $debug ) {
echo '<pre>';
wc_print_r( $query );
echo '</pre>';
}
if ( $debug || $nocache ) {
self::enable_big_selects();
$result = apply_filters( 'woocommerce_reports_get_order_report_data', $wpdb->$query_type( $query ), $data );
} else {
$query_hash = md5( $query_type . $query );
$result = $this->get_cached_query( $query_hash );
if ( $result === null ) {
self::enable_big_selects();
$result = apply_filters( 'woocommerce_reports_get_order_report_data', $wpdb->$query_type( $query ), $data );
}
$this->set_cached_query( $query_hash, $result );
}
return $result;
}