Getting WooCommerce Monthly Sales Archive in Dashboard Widget

PressTigers

Getting WooCommerce Monthly Sales Archive in Dashboard Widget

May 18, 2017
Leave a comment

In the previous article, getting WooCommerce current month sales summary in the Dashboard Widget was explained. Picking up from where we left off, we’ll begin this tutorial by developing a feature of order history in which every one of the orders from the earliest starting point till the present month will be listed.

You need to add the following code snippets in your Theme’s functions.php file. As an outcome, it will generate monthly sales archive on your Dashboard. You need to make sure to create JS (admin-script.js) and CSS (admin-style.css) files to include the CSS and JS in the admin area according to your Theme or plugin structure.

add_action( 'wp_ajax_nopriv_get_orders_archive', 'get_orders_archive' );
add_action( 'wp_ajax_get_orders_archive', 'get_orders_archive' );

function load_custom_wp_admin_style() {
    wp_register_style( 'custom_wp_admin_css', get_template_directory_uri() . '/css/admin-style.css', false, '1.0.0' );
    wp_enqueue_style( 'custom_wp_admin_css' );
    wp_enqueue_script( 'my_custom_script', get_template_directory_uri() . '/js/admin-script.js' );
    wp_localize_script( 'my_custom_script', 'ajax_var', array( 'url' => admin_url( 'admin-ajax.php' ), ) );
}
add_action( 'admin_enqueue_scripts', 'load_custom_wp_admin_style' );
function ptmp_montly_sales_dashboard_widget() {
    monthly_archive_dropdown();
    $start_date = strtotime( date( 'Y-m', current_time( 'timestamp' ) ) . '-01 midnight' );
    $sold_products = get_orders_list( $start_date );
    // List Sales Items
    if ( !empty( $sold_products ) ) {
   	 echo $html = get_output( $sold_products );
    } else {
   	 echo '<p>' . __( 'Currently, there is no sale for this month.', 'wp-dynamo-child' ) . '</p>';
    }
}

add_action( 'wp_ajax_nopriv_get_orders_archive', 'get_orders_archive' );

add_action( 'wp_ajax_get_orders_archive', 'get_orders_archive' );

function load_custom_wp_admin_style() {

wp_register_style( 'custom_wp_admin_css', get_template_directory_uri() . '/css/admin-style.css', false, '1.0.0' );

wp_enqueue_style( 'custom_wp_admin_css' );

wp_enqueue_script( 'my_custom_script', get_template_directory_uri() . '/js/admin-script.js' );

wp_localize_script( 'my_custom_script', 'ajax_var', array( 'url' => admin_url( 'admin-ajax.php' ), ) );

}

add_action( 'admin_enqueue_scripts', 'load_custom_wp_admin_style' );

function ptmp_montly_sales_dashboard_widget() {

monthly_archive_dropdown();

$start_date = strtotime( date( 'Y-m', current_time( 'timestamp' ) ) . '-01 midnight' );

$sold_products = get_orders_list( $start_date );

// List Sales Items

if ( !empty( $sold_products ) ) {

echo $html = get_output( $sold_products );

} else {

echo '<p>' . __( 'Currently, there is no sale for this month.', 'wp-dynamo-child' ) . '</p>';

}

New addition in the widget is the “dropdown” in which months are displayed on the basis of orders. Additionally, you need to use a functional approach so that the code can easily be understood and reused.

function monthly_archive_dropdown() {
    global $wp_locale, $wpdb;
    $extra_checks = "AND post_status ='wc-completed' ";
    $filter_post_status = filter_input( INPUT_GET, "post_status" );
    if ( !isset( $filter_post_status ) || 'trash' !== $filter_post_status ) {
   	 $extra_checks .= " AND post_status != 'trash'";
    } elseif ( isset( $filter_post_status ) ) {
   	 $extra_checks = $wpdb->prepare( ' AND post_status = %s', $filter_post_status );
    }
    $months = $wpdb->get_results( $wpdb->prepare( " SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month FROM $wpdb->posts WHERE post_type = %s $extra_checks ORDER BY post_date DESC ", 'shop_order' ) );
    $month_count = count( $months );
    if ( $month_count < 1 ) {
   	 echo 'There is no sale yet!';
    };
    ?>
    <label for="filter-by-date" class="screen-reader-text"><?php _e( 'Filter by date' ); ?></label>
    <select name="m" id="pt-filter-by-date">
   	 <?php
   	 foreach ( $months as $arc_row ) {
   		 if ( 0 == $arc_row->year )
   			 continue;
   		 $month = zeroise( $arc_row->month, 2 );
   		 $year = $arc_row->year;
   		 printf( "<option %s value='%s'>%s</option>\n", selected( $m, $year . $month, false ), esc_attr( $arc_row->year . '-' . $month . '-1' ),
   				 /* translators: 1: month name, 2: 4-digit year */ sprintf( __( '%1$s %2$d' ), $wp_locale->get_month( $month ), $year )
   		 ); } ?>
    </select>
<?php }

function get_orders_list( $start_date ) {
    global $woocommerce, $wpdb, $product;
    include_once($woocommerce->plugin_path() . '/includes/admin/reports/class-wc-admin-report.php');
    // WooCommerce Admin Report
    $wc_report = new WC_Admin_Report();
    $end_date = strtotime( '+1month', $start_date ) - 86400;
    $wc_report->start_date = $start_date;
    $wc_report->end_date = $end_date;
   // Avoid max join size error
    $wpdb->query( 'SET SQL_BIG_SELECTS=1' );
 $sold_products = $wc_report->get_order_report_data( array(
   	 'data' => array(
   		 '_product_id' => array(
   			 'type' => 'order_item_meta',
   			 'order_item_type' => 'line_item',
   			 'function' => '',
   			 'name' => 'product_id'
   		 ),
   		 '_qty' => array(
   			 'type' => 'order_item_meta',
   			 'order_item_type' => 'line_item',
   			 'function' => 'SUM',
   			 'name' => 'quantity'
   		 ),
   		 '_line_subtotal' => array(
   			 'type' => 'order_item_meta',
   			 'order_item_type' => 'line_item',
   			 'function' => 'SUM',
   			 'name' => 'gross'
   		 ),
   		 '_line_total' => array(
   			 'type' => 'order_item_meta',
   			 'order_item_type' => 'line_item',
   			 'function' => 'SUM',
   			 'name' => 'gross_after_discount'
   		 )
   	 ),
   	 'query_type' => 'get_results',
   	 'order_by' => 'quantity DESC',
   	 'group_by' => 'product_id',
   	 'where_meta' => '',
   	 'order_types' => wc_get_order_types( 'order_count' ),
   	 'limit' => 12,
   	 'filter_range' => 1,
   	 'order_status' => array( 'completed' ),
   		 ) );
    return $sold_products;
}
function get_output( $sold_products ) {
    $html .= '<table class="ptmp-products-list widefat striped">
   		<thead><tr><th style="" class="manage-column column-name" scope="col">' . __( 'Name', 'woocommerce-monthly-sales-summary' ) . '</th>
   			<th style="" class="manage-column column-count" scope="col">' . __( 'Items', 'woocommerce-monthly-sales-summary' ) . '</th>
   			<th style="" class="manage-column column-earnings num" scope="col">' . __( 'Sales', 'woocommerce-monthly-sales-summary' ) . '</th>
   		</tr></thead>
   	<tbody id="ptmp-wcds-products-table-body">';
    foreach ( $sold_products as $product ) {
   	 $html .= '<tr>';
   	 $html .='<td><a href="' . esc_url( get_edit_post_link( intval( $product->product_id ) ) ) . '"><strong>' . html_entity_decode( get_the_title( $product->product_id ) ) . '</strong></a></td>';
   	 $html .='<td>' . intval( $product->quantity ) . '</td>';
   	 $price = $product->gross;
   	 $product_price = wc_price( $price );
   	 $html .='<td>' . $product_price . '</td>';
   	 $html .='</tr>';
    }
    $html .='</tbody>';
    $html .='</table>';
    return $html;
}
function get_orders_archive() {
    $start_date = strtotime( $_POST['current_date'] );
    $sold_products = get_orders_list( $start_date );
    // List Sales Items
    if ( !empty( $sold_products ) ) {
   	 $html = get_output( $sold_products );
   	 $json['html'] = $html;
   	 $json['type'] = "success";
    } else {
   	 $json['html'] = '<p>' . __( 'There is no sale for this month.', 'woocommerce-monthly-sales-summary' ) . '</p>';
   	 $json['type'] = "error";
    }
    wp_send_json( $json );
}

100

101

102

103

104

105

106

107

108

109

110

111

function monthly_archive_dropdown() {

global $wp_locale, $wpdb;

$extra_checks = "AND post_status ='wc-completed' ";

$filter_post_status = filter_input( INPUT_GET, "post_status" );

if ( !isset( $filter_post_status ) || 'trash' !== $filter_post_status ) {

$extra_checks .= " AND post_status != 'trash'";

} elseif ( isset( $filter_post_status ) ) {

$extra_checks = $wpdb->prepare( ' AND post_status = %s', $filter_post_status );

}

$months = $wpdb->get_results( $wpdb->prepare( " SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month FROM $wpdb->posts WHERE post_type = %s $extra_checks ORDER BY post_date DESC ", 'shop_order' ) );

$month_count = count( $months );

if ( $month_count < 1 ) {

echo 'There is no sale yet!';

};

<?php

foreach ( $months as $arc_row ) {

if ( 0 == $arc_row->year )

continue;

$month = zeroise( $arc_row->month, 2 );

$year = $arc_row->year;

printf( "<option %s value='%s'>%s</option>\n", selected( $m, $year . $month, false ), esc_attr( $arc_row->year . '-' . $month . '-1' ),

/* translators: 1: month name, 2: 4-digit year */ sprintf( __( '%1$s %2$d' ), $wp_locale->get_month( $month ), $year )

); } ?>

</select>

<?php }

function get_orders_list( $start_date ) {

global $woocommerce, $wpdb, $product;

include_once($woocommerce->plugin_path() . '/includes/admin/reports/class-wc-admin-report.php');

// WooCommerce Admin Report

$wc_report = new WC_Admin_Report();

$end_date = strtotime( '+1month', $start_date ) - 86400;

$wc_report->start_date = $start_date;

$wc_report->end_date = $end_date;

// Avoid max join size error

$wpdb->query( 'SET SQL_BIG_SELECTS=1' );

$sold_products = $wc_report->get_order_report_data( array(

'data' => array(

'_product_id' => array(

'type' => 'order_item_meta',

'order_item_type' => 'line_item',

'function' => '',

'name' => 'product_id'

'_qty' => array(

'type' => 'order_item_meta',

'order_item_type' => 'line_item',

'function' => 'SUM',

'name' => 'quantity'

'_line_subtotal' => array(

'type' => 'order_item_meta',

'order_item_type' => 'line_item',

'function' => 'SUM',

'name' => 'gross'

'_line_total' => array(

'type' => 'order_item_meta',

'order_item_type' => 'line_item',

'function' => 'SUM',

'name' => 'gross_after_discount'

)

'query_type' => 'get_results',

'order_by' => 'quantity DESC',

'group_by' => 'product_id',

'where_meta' => '',

'order_types' => wc_get_order_types( 'order_count' ),

'limit' => 12,

'filter_range' => 1,

'order_status' => array( 'completed' ),

) );

return $sold_products;

}

function get_output( $sold_products ) {

$html .= '<table class="ptmp-products-list widefat striped">

<thead><tr><th style="" class="manage-column column-name" scope="col">' . __( 'Name', 'woocommerce-monthly-sales-summary' ) . '</th>

<th style="" class="manage-column column-count" scope="col">' . __( 'Items', 'woocommerce-monthly-sales-summary' ) . '</th>

<th style="" class="manage-column column-earnings num" scope="col">' . __( 'Sales', 'woocommerce-monthly-sales-summary' ) . '</th>

</tr></thead>

<tbody id="ptmp-wcds-products-table-body">';

foreach ( $sold_products as $product ) {

$html .= '<tr>';

$html .='<td><a href="' . esc_url( get_edit_post_link( intval( $product->product_id ) ) ) . '"><strong>' . html_entity_decode( get_the_title( $product->product_id ) ) . '</strong></a></td>';

$html .='<td>' . intval( $product->quantity ) . '</td>';

$price = $product->gross;

$product_price = wc_price( $price );

$html .='<td>' . $product_price . '</td>';

$html .='</tr>';

}

$html .='</tbody>';

$html .='</table>';

return $html;

}

function get_orders_archive() {

$start_date = strtotime( $_POST['current_date'] );

$sold_products = get_orders_list( $start_date );

// List Sales Items

if ( !empty( $sold_products ) ) {

$html = get_output( $sold_products );

$json['html'] = $html;

$json['type'] = "success";

} else {

$json['html'] = '<p>' . __( 'There is no sale for this month.', 'woocommerce-monthly-sales-summary' ) . '</p>';

$json['type'] = "error";

}

wp_send_json( $json );

}

This code has been provided to enqueue the JS and CSS files in the admin area accordingly. Now create a JS file as mentioned earlier and add the following code in it:

(function ($) {
	'use strict';
	$(function () {
    	$("#pt-filter-by-date").on('change', function () {
        	var current_date = $(this).val();
        	var datastring = 'current_date=' + current_date + '&action=get_orders_archive';
$.ajax({
            	type: 'POST',
            	url: ajax_var.url,
            	data: datastring,
            	dataType: "json",
            	success: function (response) {

                	if (response.type == 'success') {
                    	$('#ptmp-monthly-sales-summary > p').remove();
                    	$('.ptmp-products-list').remove();
                    	$('#ptmp-monthly-sales-summary').append(response.html);
                	}
                	else if (response.type == 'error') {
                    	$('#ptmp-monthly-sales-summary').append(response.html);
                    	$('#ptmp-monthly-sales-summary > p').remove();
                	} },
});   });   });
})(jQuery);

(function ($) {

'use strict';

$(function () {

$("#pt-filter-by-date").on('change', function () {

var current_date = $(this).val();

var datastring = 'current_date=' + current_date + '&action=get_orders_archive';

$.ajax({

type: 'POST',

url: ajax_var.url,

data: datastring,

dataType: "json",

success: function (response) {

if (response.type == 'success') {

$('#ptmp-monthly-sales-summary > p').remove();

$('.ptmp-products-list').remove();

$('#ptmp-monthly-sales-summary').append(response.html);

}

else if (response.type == 'error') {

$('#ptmp-monthly-sales-summary').append(response.html);

$('#ptmp-monthly-sales-summary > p').remove();

} },

}); }); });

})(jQuery);

The CSS code has been provided in the previous article, so here you just need to add the following CSS rules in that previous CSS file:

#pt-filter-by-date {
	margin-bottom: 10px;
}

#pt-filter-by-date {

margin-bottom: 10px;

}

Upon the successful execution of the code mentioned above, you will be able to see the following final output of monthly sales archive on your Dashboard: