Create your own sales report
Modify the sales report generated when clicking the export link in WPPizza -> Reports
The sales report can be manipulated using a variety of filters/actions according to the examples below. Depending on your requirements, you might need to use one, more or all of them.
wppizza_filter_csv_columns_detailed / wppizza_filter_csv_rows_detailed (v3.9+)
Removing columns from / adding columns to “Detailed” report export WPpizza->Reports
example:
/********************************************************** removing all "order verbose" details adding a custom column use print_r($columns) for all available column keys **********************************************************/ add_filter( 'wppizza_filter_csv_columns_detailed', 'myprefix_alter_report_columns' ); function myprefix_alter_report_columns($columns){ /* remove order_verbose column label */ unset($columns['order_verbose']); /* add custom column */ $columns['my_column'] = 'My Custom Column'; return $columns; } add_filter( 'wppizza_filter_csv_rows_detailed', 'myprefix_alter_report_rows', 10, 4 ); function myprefix_alter_report_rows($order, $values, $user_data, $oId){ /* remove order_verbose column data */ unset($order['order_verbose']); /* add custom column. adding some data based on $order or $values parameters for example */ $order['my_column'] = 'some data' ; return $order; }
wppizza_filter_csv_export_select / wppizza_filter_csv_export_{$key} (v3.9+)
Creating your own report and adding it to “WPPizza->Reports->Export” dropdown
example:
/********************************************************************* adding "My custom report" to report export dropdown options using 'myprefix_myreport' key *********************************************************************/ add_filter('wppizza_filter_csv_export_select', 'myprefix_export_name' ); function myprefix_export_name($array){ /* adding report with "myprefixmyreport" key to dropdown (key must be a-zA-Z0-9 only) */ $array['myprefixmyreport'] = 'My custom report'; return $array; }
/********************************************************** generating report for export when 'myprefixmyreport' was seleccted in dropdown **********************************************************/ add_filter('wppizza_filter_csv_export_myprefixmyreport', 'myprefix_report_export', 10 , 3 ); /********************************************************* * * [generating the csv] * * @param str * @param array * @param str * * @return str * * the below will generate exactly the same report as when using the "detailed" * option from the dropdown, so edit as required for your purposes *********************************************************/ function myprefix_report_export($csv, $data, $type){ /******************* # order data will always be grouped by blogs, even if only one blog and/or it's not a multisite setup # # (in a multisite setup orders from multiple blogs might be returned depending on settings # and additionally have different order form settings labels and/or counts) # # so we need to always loop through each blog (though there might only be one of course) *******************/ /***************** ini an empty array we implode later to the csv string *****************/ $csvData = array(); /***************** ini an empty array that will contain the csv rows of each blog *****************/ $csvBlogRows = array(); /***************** columns used per blog to add more empty separators if necessary when putting it all into a single csv *****************/ $columnCount = array(); /**************** # first of all, make sure there's actually some data returned ****************/ if(!empty($data['blogs'])){ foreach($data['blogs'] as $blogID => $blogInfo){ /* * * * * * * * # set top row - blog info * * * * * * * */ $blog_info = array(); $blog_info['blog_id'] = '"(Blog #'.$blogInfo['blog_id'].')"'; $blog_info['blogname'] = '"'.substr($blogInfo['blogname'], 0, 18 ).'"';//lets not have massive strings here //$blog_info['siteurl'] = substr($blogInfo['siteurl'], 0, 16 );// skip this for now. might mess up column widths and blogname should do really /* --------------------------- allow blog info filtering --------------------------- */ $blog_info = apply_filters('wppizza_filter_csv_bloginfo_'.$type.'', $blog_info, $blogInfo, $blogID); /***** max columns this blog *****/ $columnCountBlogInfo[$blogID] = count($blog_info); /***** convert bloginfo comma separated (to not mess up column widths too much) string *****/ $csvBlogRows[$blogID]['blog_info'] = implode(',', $blog_info); /* * * * * * * * * * * * * * * */ /* * * * * * * * # set second row - column labels * * * * * * * */ $columns = array(); /* order transaction details */ $columns['order_id'] = __('Order ID', 'wppizza_admin'); $columns['order_date_formatted'] = __('Date', 'wppizza_admin'); $columns['order_paid_by'] = __('Paid By', 'wppizza_admin'); $columns['payment_status'] = __('Status', 'wppizza_admin'); $columns['transaction_id'] = __('Transaction ID', 'wppizza_admin'); $columns['self_pickup'] = __('Pickup', 'wppizza_admin'); /* WP (un)-registered users */ $columns['user_registered'] = __('Registered User', 'wppizza_admin'); $columns['user_username'] = __('Username', 'wppizza_admin'); $columns['user_fullname'] = __('Full Name', 'wppizza_admin'); $columns['user_email'] = __('eMail', 'wppizza_admin'); /* customer info labels - entered in checkout page - related to each order looping through each formfield */ foreach($data['formfields'][$blogID] as $ffID => $ffValues){ $columns['customer_'.$ffID] = $ffValues['label']; } $columns['customer_ip_address'] = __('IP Address', 'wppizza_admin'); /* order items */ $columns['order_verbose'] = __('Order Verbose', 'wppizza_admin'); $columns['number_of_items'] = __('Products Count', 'wppizza_admin'); $columns['products_verbose'] = __('Products Verbose', 'wppizza_admin'); /* financials */ $columns['currencyiso'] = __('Currency', 'wppizza_admin'); $columns['total_price_items'] = __('Price Items', 'wppizza_admin'); $columns['total_discounts'] = __('Discounts', 'wppizza_admin'); $columns['delivery_charges'] = __('Delivery Charges', 'wppizza_admin'); $columns['handling_charges'] = __('Handling Charges', 'wppizza_admin'); $columns['tips'] = __('Tips', 'wppizza_admin'); $columns['tax_rates_verbose'] = __('Tax Rates', 'wppizza_admin'); $columns['taxes_included'] = __('Taxes Included', 'wppizza_admin'); $columns['taxes'] = __('Taxes', 'wppizza_admin'); $columns['total'] = __('Total', 'wppizza_admin'); /* --------------------------- allow column name filtering --------------------------- */ $columns = apply_filters('wppizza_filter_csv_columns_'.$type.'', $columns ); /***** max columns this blog *****/ $columnCount[$blogID] = count($columns); /***** convert column names to comma separated string and add linebreak *****/ $csvBlogRows[$blogID]['columns'] = implode(',', $columns); /* * * * * * * * * * * * * * * */ /* * * * * * * * loop through orders - matching values to column * * * * * * * */ $orders = array(); foreach($data['orders'][$blogID] as $oId => $values){ $order = array(); /* order transaction details */ $order['order_id'] = $oId ; $order['order_date_formatted'] = date('Y-m-d H:i:s', $values['order']['order_date_timestamp']); $order['order_paid_by'] = $values['order']['initiator']; $order['payment_status'] = $values['order']['payment_status']; $order['transaction_id'] = $values['order']['transaction_id']; $order['self_pickup'] = !empty($values['order']['self_pickup']) ? 'Y' : 'N'; /* WP (un)-registered users */ $user_id = $values['order']['wp_user_id'];//user id associated with this order $user_data = $data['users'][$user_id];//user date associated with this user id (if any) $order['user_registered'] = !empty($user_id) ? 'Y' : 'N'; $order['user_username'] = !empty($user_data) ? $user_data['user_login'] : ''; $order['user_fullname'] = !empty($user_data) ? $user_data['first_name'] . ' ' . $user_data['last_name'] : '' ; $order['user_email'] = !empty($user_data) ? $user_data['user_email'] : '' ; /* customer info labels - entered in checkout page - related to each order looping through each formfield make sure to wrap each line in quotes */ foreach($data['formfields'][$blogID] as $ffID => $ffValues){ $order['customer_'.$ffID] = $values['customer'][$ffID]; } $order['customer_ip_address'] = $values['customer']['ip_address']; /* order items */ $order['order_verbose'] = $values['order']['verbose']; $order['number_of_items'] = $values['order']['number_of_items']; $productsVerbose = array(); foreach($values['items'] as $iId => $item){ $productsVerbose[$iId] = $item['quantity'].'x '.$item['title'].' ['.$item['price_label'].'] - '.$item['pricetotal_formatted'] ; } $order['products_verbose'] = implode(PHP_EOL, $productsVerbose); /* financials */ $order['currencyiso'] = $values['order']['currencyiso']; $order['total_price_items'] = $values['order']['total_price_items']; $order['total_discounts'] = (0 - $values['order']['total_discounts']);//make negative $order['delivery_charges'] = $values['order']['delivery_charges']; $order['handling_charges'] = $values['order']['handling_charges']; $order['tips'] = $values['order']['tips']; $taxRatesVerbose = array(); if(!empty($values['order']['tax_by_rate'])){ foreach($values['order']['tax_by_rate'] as $taxType=>$rates){ $taxRatesVerbose[] = $taxType . ' @' . $rates['rate'].'%'; } } $order['tax_rates_verbose'] = implode(PHP_EOL, $taxRatesVerbose);//wrap in quotes to make sure linebreaks work $order['taxes_included'] = !empty($values['order']['tax_included']) ? 'Y' : 'N'; $order['taxes'] = $values['order']['taxes']; $order['total'] = $values['order']['total']; /* --------------------------- allow row data filtering --------------------------- */ $order = apply_filters('wppizza_filter_csv_rows_'.$type.'', $order, $values, $user_data, $oId ); /***** convert to comma separated string each value enclosed by quotes to make sure linebreaks are recognised using helper function to sanitise csv values *****/ $order = array_map('myhelper_sanitize_csv_values', $order); $orders[$oId] = '"'.implode('","', $order).'"'; } /***** join all orders adding linebreaks in between *****/ $csvBlogRows[$blogID]['orders'] = $orders; /* * * * * * * * * * * * * * * */ } } /************************************************************ create csv for each blog accounting for maximum columns per blog adding comma separators if necessary ************************************************************/ $maxColumns = max($columnCount); if(!empty($data['blogs'])){ foreach($data['blogs'] as $blogID => $blogInfo){ /* determine how many additional separators we need for each line of data of this blog */ $additionalSeparatorsBlogInfo = ($maxColumns - $columnCountBlogInfo[$blogID]); $additionalSeparatorsData = ($maxColumns - $columnCount[$blogID]); /* blog info */ $csvBlogRows[$blogID]['blog_info'] = $csvBlogRows[$blogID]['blog_info']. str_repeat(",", $additionalSeparatorsBlogInfo); /* columns */ $csvBlogRows[$blogID]['columns'] = $csvBlogRows[$blogID]['columns']. str_repeat(",", $additionalSeparatorsData); /* order data */ $orders = array(); foreach($csvBlogRows[$blogID]['orders'] as $oId => $order){ $orders[$oId] = $order . str_repeat(",", $additionalSeparatorsData); } $csvBlogRows[$blogID]['orders'] = implode(PHP_EOL, $orders); /******************************* implode data for this blogid *******************************/ $csvData[$blogID] = implode(PHP_EOL, $csvBlogRows[$blogID]); } } /* # * # * # * # * # * # * # * # * # * # * # * # * # * # # # implode data for all blogids to a single string, # for otput adding some linebreaks after each blog data # * # * # * # * # * # * # * # * # * # * # * # * # * # * # */ $lineBreaks = PHP_EOL . str_repeat(",", $maxColumns) . PHP_EOL . str_repeat(",", $maxColumns). PHP_EOL ; $csv = implode($lineBreaks, $csvData) . $lineBreaks; return $csv; }
/********************************************************* * * [sanitise csv values. just to make sure we have csv conform values * converting entities, removing commas and quotes * called before imploding order data to csv string ] * * @since 3.9 * @param str * @return str * *********************************************************/ function myhelper_sanitize_csv_values($value){ /* a - strip tags */ $value = wp_strip_all_tags($value); /* b - convert commas */ $value = str_replace(',',";", $value); /* c - convert quotes */ $value = str_replace('"',"`", $value); return $value; }
wppizza_filter_report_query
alter the query according to your requirements
@param: str (the whole query string)
@param: str (blog prefix)
@param: str (more or less the WHERE part of the query using the appropriate date ranges etc – just for convenience in some circumstances)
@return: str
example:
add_filter('wppizza_filter_report_query', 'my_prefix_my_query', 10, 3); function my_prefix_my_query($ordersQuery, $wpdbPrefix, $oQuery){ /* maybe not the most useful example but to keep it simple - look at the table structure of the wppizza_orders table to create a query appropriate to your scenario */ $ordersQuery = str_ireplace("WHERE", "WHERE wp_user_id = '5' AND ");/* keep the query as is, but only query sales from user with user_id 5 */ return $ordersQuery; }
wppizza_filter_report_datasets
modify the dataset array that will be passed on to the wppizza_report_export function and the wppizza_custom_report action below
@param: array (current dataset)
@param: array (order details returned for each order the query result includes)
@return: array
example:
add_filter('wppizza_filter_report_datasets', 'my_prefix_my_datasets', 10, 2); function my_prefix_my_datasets($datasets, $processOrder){ /* adding my_custom_dataset to already existing/default ones */ $datasets['my_custom_dataset'] = array(); $datasets['my_custom_dataset']['count'] = 0; /* a very basic example */ foreach($processOrder as $k=>$v){ /* use print_r($v) to get all array key/values associated with an order*/ $datasets['my_custom_dataset']['count'] ++; $datasets['my_custom_dataset'][$k]['value'] = 'some value for order with key '.$k.''; } return $datasets; }
wppizza_filter_reports_export_results
modify the results (rows/columns etc) to be used in the csv file to export/save
@param: array ($result – array of csv parts/results to be included in report)
@param: array ($report_data – array of report_data including datasets)
@return: array
example:
add_action('wppizza_filter_reports_export_results', 'my_custom_reports_export_results'); function my_custom_reports_export_results($result, $report_data){ /* Check / vardump $result parameter to get array keys/values of the report some examples below - modify as required */ /* do NOT include the "sales value by order status" in csv */ unset($result['order_status_summary']); /* do NOT include the "sales value by gateway" in csv */ unset($result['sales_by_gateway']); /* do NOT include "sales by item" in csv */ unset($result['sales_by_item']); /* add 'my customised dataset' created by using wppizza_filter_report_datasets - see example above - to the report output */ $result['my_custom_dataset'] = '';// initialise the string $result['my_custom_dataset'] .= PHP_EOL . '"some label"'. PHP_EOL ; /* add header row */ /* add results rows */ foreach($report_data['dataset']['my_custom_dataset'] as $key=>$array){ $result['my_custom_dataset'] .= $array['value']; // add data row $result['my_custom_dataset'] .=PHP_EOL;// make sure to start a new line for each new row } $result['my_custom_dataset'] .=PHP_EOL . PHP_EOL; // add another couple of empty lines for clarity after the end of it all return $result; }
wppizza_custom_report
creating your own export report, completely REPLACING the whole function with your own
@param: array ($report_data)
@return: void
example:
as i starting point i would suggest the following
– copy the whole ‘wppizza_report_export’ function (located in classes/subpages/subpage.reports.php) as your ‘my_custom_report_export’ function.
– you must remove the action hook (do_action(‘wppizza_custom_report’, $report_data);) in that copy or you will have an infinite loop
– edit as required
add_action('wppizza_custom_report', 'my_custom_report_export'); function my_custom_report_export($report_data){ /* run your own reports functions using a copy of wppizza_report_export (see notes above), so it's something like: */ if(empty($_GET['export'])){ return; } $currency = $report_data['currency']; $dataset = $report_data['dataset'];/* $dataset will also now contain your $datasets['my_custom_dataset'] from above - do print_r($report_data to get the whole array) */ /* ... a lot more code .... */ header("Content-Length: " . strlen($result)); echo $result; exit();// dont forget to exit }
Report functions
wppizza_reports_data() (v3.12+)
get csv report data as a string in a variable
@param: array (query arguments)
@return: string (as csv)
example:
$args = array( /* range of report data to retrieve. if 'from' or 'to' are omitted defaults to last 30 days */ 'range' => array( 'from' =>'2020-04-01', //from date in 'Y-m-d' format. Will always use time of 00:00:00 'to' =>'2020-04-15', //to date in 'Y-m-d' format. Will always use time of 23:59:59 ), /* defaults to 'detailed' if omitted. Should not be set to 'summary' (this will not work). Use above filters to filter the detailed report if you wish If you have created your own report using 'wppizza_filter_csv_export_select' and 'wppizza_filter_csv_export_{$key}' above use that type ($key) instead if you wish to retrieve the data of that report */ 'type' => 'detailed', ); /* return the csv data as a string in the $csv variable */ $csv = wppizza_reports_data($args); /* You can now use the data contained in '$csv' to - perhaps - save it to some file or save to file and email the report as attachment somewhere or do whatever it is you need to do ... If you wanted your code to run as a WP cronjob refer to the WordPress codex regarding scheduling wp_cron */