Created
March 25, 2015 17:16
-
-
Save daigo75/2f88fa4d1b0907e080cd to your computer and use it in GitHub Desktop.
WC_Admin_Report - Improved support for INNER, LEFT and RIGHT JOIN (ref. https://github.com/woothemes/woocommerce/issues/7818)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| if ( ! defined( 'ABSPATH' ) ) { | |
| exit; // Exit if accessed directly | |
| } | |
| /** | |
| * Admin Report | |
| * | |
| * Extended by reports to show charts and stats in admin. | |
| * | |
| * @author WooThemes | |
| * @category Admin | |
| * @package WooCommerce/Admin/Reports | |
| * @version 2.1.0 | |
| */ | |
| class WC_Admin_Report { | |
| public $chart_interval; | |
| public $group_by_query; | |
| public $barwidth; | |
| public $chart_groupby; | |
| public $start_date; | |
| public $end_date; | |
| /** | |
| * Get report totals such as order totals and discount amounts. | |
| * | |
| * Data example: | |
| * | |
| * '_order_total' => array( | |
| * 'type' => 'meta', | |
| * 'function' => 'SUM', | |
| * 'name' => 'total_sales' | |
| * ) | |
| * | |
| * @param array $args | |
| * @return array|string depending on query_type | |
| */ | |
| 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 $key => $value ) { | |
| $distinct = ''; | |
| if ( isset( $value['distinct'] ) ) { | |
| $distinct = 'DISTINCT'; | |
| } | |
| if ( $value['type'] == 'meta' ) { | |
| $get_key = "meta_{$key}.meta_value"; | |
| } elseif( $value['type'] == 'post_data' ) { | |
| $get_key = "posts.{$key}"; | |
| } elseif( $value['type'] == 'order_item_meta' ) { | |
| $get_key = "order_item_meta_{$key}.meta_value"; | |
| } elseif( $value['type'] == 'order_item' ) { | |
| $get_key = "order_items.{$key}"; | |
| } else { | |
| 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 as $key => $value ) { | |
| $join_type = isset($value['join_type']) ? $value['join_type'] : 'INNER'; | |
| if ( $value['type'] == 'meta' ) { | |
| $joins["meta_{$key}"] = "{$join_type} JOIN {$wpdb->postmeta} AS meta_{$key} ON " . | |
| "(posts.ID = meta_{$key}.post_id) " . | |
| "AND (meta_{$key}.meta_key = '{$key}')"; | |
| } elseif ( $value['type'] == '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 ( $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 = '{$key}')"; | |
| } elseif ( $value['type'] == 'order_item' ) { | |
| $joins["order_items"] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id"; | |
| } | |
| } | |
| if ( ! empty( $where_meta ) ) { | |
| foreach ( $where_meta as $value ) { | |
| if ( ! is_array( $value ) ) { | |
| continue; | |
| } | |
| $join_type = isset($value['join_type']) ? $value['join_type'] : 'INNER'; | |
| $key = is_array( $value['meta_key'] ) ? $value['meta_key'][0] . '_array' : $value['meta_key']; | |
| if ( isset( $value['type'] ) && $value['type'] == 'order_item_meta' ) { | |
| $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 ) ) { | |
| $query['where'] .= " | |
| AND ( parent.post_status IN ( 'wc-" . implode( "','wc-", $parent_order_status ) . "') OR parent.ID IS NULL ) | |
| "; | |
| } | |
| if ( $filter_range ) { | |
| $query['where'] .= " | |
| AND posts.post_date >= '" . date('Y-m-d', $this->start_date ) . "' | |
| AND posts.post_date < '" . date('Y-m-d', 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 = is_array( $value['meta_key'] ) ? $value['meta_key'][0] . '_array' : $value['meta_key']; | |
| if ( strtolower( $value['operator'] ) == 'in' ) { | |
| if ( is_array( $value['meta_value'] ) ) { | |
| $value['meta_value'] = implode( "','", $value['meta_value'] ); | |
| } | |
| if ( ! empty( $value['meta_value'] ) ) { | |
| $where_value = "IN ('{$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'] ) && $value['type'] == 'order_item_meta' ) { | |
| 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' ) { | |
| if ( is_array( $value['value'] ) ) { | |
| $value['value'] = implode( "','", $value['value'] ); | |
| } | |
| if ( ! empty( $value['value'] ) ) { | |
| $where_value = "IN ('{$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 ); | |
| $query_hash = md5( $query_type . $query ); | |
| $cached_results = get_transient( strtolower( get_class( $this ) ) ); | |
| if ( $debug ) { | |
| echo '<pre>'; | |
| print_r( $query ); | |
| echo '</pre>'; | |
| } | |
| if ( $debug || $nocache || false === $cached_results || ! isset( $cached_results[ $query_hash ] ) ) { | |
| $cached_results[ $query_hash ] = apply_filters( 'woocommerce_reports_get_order_report_data', $wpdb->$query_type( $query ), $data ); | |
| set_transient( strtolower( get_class( $this ) ), $cached_results, DAY_IN_SECONDS ); | |
| } | |
| $result = $cached_results[ $query_hash ]; | |
| return $result; | |
| } | |
| /** | |
| * Put data with post_date's into an array of times | |
| * | |
| * @param array $data array of your data | |
| * @param string $date_key key for the 'date' field. e.g. 'post_date' | |
| * @param string $data_key key for the data you are charting | |
| * @param int $interval | |
| * @param string $start_date | |
| * @param string $group_by | |
| * @return string | |
| */ | |
| public function prepare_chart_data( $data, $date_key, $data_key, $interval, $start_date, $group_by ) { | |
| $prepared_data = array(); | |
| // Ensure all days (or months) have values first in this range | |
| for ( $i = 0; $i <= $interval; $i ++ ) { | |
| switch ( $group_by ) { | |
| case 'day' : | |
| $time = strtotime( date( 'Ymd', strtotime( "+{$i} DAY", $start_date ) ) ) . '000'; | |
| break; | |
| case 'month' : | |
| default : | |
| $time = strtotime( date( 'Ym', strtotime( "+{$i} MONTH", $start_date ) ) . '01' ) . '000'; | |
| break; | |
| } | |
| if ( ! isset( $prepared_data[ $time ] ) ) { | |
| $prepared_data[ $time ] = array( esc_js( $time ), 0 ); | |
| } | |
| } | |
| foreach ( $data as $d ) { | |
| switch ( $group_by ) { | |
| case 'day' : | |
| $time = strtotime( date( 'Ymd', strtotime( $d->$date_key ) ) ) . '000'; | |
| break; | |
| case 'month' : | |
| default : | |
| $time = strtotime( date( 'Ym', strtotime( $d->$date_key ) ) . '01' ) . '000'; | |
| break; | |
| } | |
| if ( ! isset( $prepared_data[ $time ] ) ) { | |
| continue; | |
| } | |
| if ( $data_key ) { | |
| $prepared_data[ $time ][1] += $d->$data_key; | |
| } else { | |
| $prepared_data[ $time ][1] ++; | |
| } | |
| } | |
| return $prepared_data; | |
| } | |
| /** | |
| * Prepares a sparkline to show sales in the last X days | |
| * | |
| * @param int $id ID of the product to show. Blank to get all orders. | |
| * @param int $days Days of stats to get. | |
| * @param string $type Type of sparkline to get. Ignored if ID is not set. | |
| * @return string | |
| */ | |
| public function sales_sparkline( $id = '', $days = 7, $type = 'sales' ) { | |
| if ( $id ) { | |
| $meta_key = $type == 'sales' ? '_line_total' : '_qty'; | |
| $data = $this->get_order_report_data( array( | |
| 'data' => array( | |
| '_product_id' => array( | |
| 'type' => 'order_item_meta', | |
| 'order_item_type' => 'line_item', | |
| 'function' => '', | |
| 'name' => 'product_id' | |
| ), | |
| $meta_key => array( | |
| 'type' => 'order_item_meta', | |
| 'order_item_type' => 'line_item', | |
| 'function' => 'SUM', | |
| 'name' => 'sparkline_value' | |
| ), | |
| 'post_date' => array( | |
| 'type' => 'post_data', | |
| 'function' => '', | |
| 'name' => 'post_date' | |
| ), | |
| ), | |
| 'where' => array( | |
| array( | |
| 'key' => 'post_date', | |
| 'value' => date( 'Y-m-d', strtotime( 'midnight -' . ( $days - 1 ) . ' days', current_time( 'timestamp' ) ) ), | |
| 'operator' => '>' | |
| ), | |
| array( | |
| 'key' => 'order_item_meta__product_id.meta_value', | |
| 'value' => $id, | |
| 'operator' => '=' | |
| ) | |
| ), | |
| 'group_by' => 'YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date)', | |
| 'query_type' => 'get_results', | |
| 'filter_range' => false | |
| ) ); | |
| } else { | |
| $data = $this->get_order_report_data( array( | |
| 'data' => array( | |
| '_order_total' => array( | |
| 'type' => 'meta', | |
| 'function' => 'SUM', | |
| 'name' => 'sparkline_value' | |
| ), | |
| 'post_date' => array( | |
| 'type' => 'post_data', | |
| 'function' => '', | |
| 'name' => 'post_date' | |
| ), | |
| ), | |
| 'where' => array( | |
| array( | |
| 'key' => 'post_date', | |
| 'value' => date( 'Y-m-d', strtotime( 'midnight -' . ( $days - 1 ) . ' days', current_time( 'timestamp' ) ) ), | |
| 'operator' => '>' | |
| ) | |
| ), | |
| 'group_by' => 'YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date)', | |
| 'query_type' => 'get_results', | |
| 'filter_range' => false | |
| ) ); | |
| } | |
| $total = 0; | |
| foreach ( $data as $d ) { | |
| $total += $d->sparkline_value; | |
| } | |
| if ( $type == 'sales' ) { | |
| $tooltip = sprintf( __( 'Sold %s worth in the last %d days', 'woocommerce' ), strip_tags( wc_price( $total ) ), $days ); | |
| } else { | |
| $tooltip = sprintf( _n( 'Sold 1 item in the last %d days', 'Sold %d items in the last %d days', $total, 'woocommerce' ), $total, $days ); | |
| } | |
| $sparkline_data = array_values( $this->prepare_chart_data( $data, 'post_date', 'sparkline_value', $days - 1, strtotime( 'midnight -' . ( $days - 1 ) . ' days', current_time( 'timestamp' ) ), 'day' ) ); | |
| 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>'; | |
| } | |
| /** | |
| * Get the current range and calculate the start and end dates | |
| * | |
| * @param string $current_range | |
| */ | |
| public function calculate_current_range( $current_range ) { | |
| switch ( $current_range ) { | |
| case 'custom' : | |
| $this->start_date = strtotime( sanitize_text_field( $_GET['start_date'] ) ); | |
| $this->end_date = strtotime( 'midnight', strtotime( sanitize_text_field( $_GET['end_date'] ) ) ); | |
| if ( ! $this->end_date ) { | |
| $this->end_date = current_time('timestamp'); | |
| } | |
| $interval = 0; | |
| $min_date = $this->start_date; | |
| while ( ( $min_date = strtotime( "+1 MONTH", $min_date ) ) <= $this->end_date ) { | |
| $interval ++; | |
| } | |
| // 3 months max for day view | |
| if ( $interval > 3 ) { | |
| $this->chart_groupby = 'month'; | |
| } else { | |
| $this->chart_groupby = 'day'; | |
| } | |
| break; | |
| case 'year' : | |
| $this->start_date = strtotime( date( 'Y-01-01', current_time('timestamp') ) ); | |
| $this->end_date = strtotime( 'midnight', current_time( 'timestamp' ) ); | |
| $this->chart_groupby = 'month'; | |
| break; | |
| case 'last_month' : | |
| $this->start_date = strtotime( date( 'Y-m-01', strtotime( '-1 MONTH', current_time('timestamp') ) ) ); | |
| $this->end_date = strtotime( date( 'Y-m-t', strtotime( '-1 MONTH', current_time('timestamp') ) ) ); | |
| $this->chart_groupby = 'day'; | |
| break; | |
| case 'month' : | |
| $this->start_date = strtotime( date( 'Y-m-01', current_time('timestamp') ) ); | |
| $this->end_date = strtotime( 'midnight', current_time( 'timestamp' ) ); | |
| $this->chart_groupby = 'day'; | |
| break; | |
| case '7day' : | |
| $this->start_date = strtotime( '-6 days', current_time( 'timestamp' ) ); | |
| $this->end_date = strtotime( 'midnight', current_time( 'timestamp' ) ); | |
| $this->chart_groupby = 'day'; | |
| break; | |
| } | |
| // Group by | |
| switch ( $this->chart_groupby ) { | |
| case 'day' : | |
| $this->group_by_query = 'YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date)'; | |
| $this->chart_interval = ceil( max( 0, ( $this->end_date - $this->start_date ) / ( 60 * 60 * 24 ) ) ); | |
| $this->barwidth = 60 * 60 * 24 * 1000; | |
| break; | |
| case 'month' : | |
| $this->group_by_query = 'YEAR(posts.post_date), MONTH(posts.post_date)'; | |
| $this->chart_interval = 0; | |
| $min_date = $this->start_date; | |
| while ( ( $min_date = strtotime( "+1 MONTH", $min_date ) ) <= $this->end_date ) { | |
| $this->chart_interval ++; | |
| } | |
| $this->barwidth = 60 * 60 * 24 * 7 * 4 * 1000; | |
| break; | |
| } | |
| } | |
| /** | |
| * Return currency tooltip JS based on WooCommerce currency position settings. | |
| * | |
| * @return string | |
| */ | |
| public function get_currency_tooltip() { | |
| switch( get_option( 'woocommerce_currency_pos' ) ) { | |
| case 'right': | |
| $currency_tooltip = 'append_tooltip: "' . get_woocommerce_currency_symbol() . '"'; break; | |
| case 'right_space': | |
| $currency_tooltip = 'append_tooltip: " ' . get_woocommerce_currency_symbol() . '"'; break; | |
| case 'left': | |
| $currency_tooltip = 'prepend_tooltip: "' . get_woocommerce_currency_symbol() . '"'; break; | |
| case 'left_space': | |
| default: | |
| $currency_tooltip = 'prepend_tooltip: "' . get_woocommerce_currency_symbol() . ' "'; break; | |
| } | |
| return $currency_tooltip; | |
| } | |
| /** | |
| * Get the main chart | |
| * | |
| * @return string | |
| */ | |
| public function get_main_chart() {} | |
| /** | |
| * Get the legend for the main chart sidebar | |
| * | |
| * @return array | |
| */ | |
| public function get_chart_legend() { | |
| return array(); | |
| } | |
| /** | |
| * [get_chart_widgets description] | |
| * | |
| * @return array | |
| */ | |
| public function get_chart_widgets() { | |
| return array(); | |
| } | |
| /** | |
| * Get an export link if needed | |
| */ | |
| public function get_export_button() {} | |
| /** | |
| * Output the report | |
| */ | |
| public function output_report() {} | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment