WooCommerce event ticket sales report

December 14th, 2016 Leave a comment Go to comments

For several years we've been using WooCommerce for general admission event tickets.  For this we've been using an open source ticketing system.  It works, sort of.  The number of bugs we have to find and fix increases at every show.  The most critical one we found this year was a bug that displays show times incorrectly.  This is problematic for obvious reasons.  When we reported this to the company's developers, their suggestion was to stop selling tickets until after Daylight Saving's Time.  This plus many other annoyances led us to decide to reinvent the wheel.

We dispensed with the plugin, and decided to use only the built-in features of WooCommerce with a custom report that displays the order for each product (show).  This custom report proved to be somewhat of a challenge - WooCommerce's documentation is ridiculous at times - but we're more confident in our code than the barely-working plugin we were using before.



This is the short version of how we set up our products. The full version is available in the WooCommerce documentation.

1) Set up an attribute from within Products>>Attributes.  Ours was called Ticket Type (you can name the attribute as you please).  We added the names of our tickets, such as Adult, Senior, Student, etc. 

2) Create a Variable product.

3) Navigate to the Attributes tab, choose the Attribute you created in step 1), then click Add, Select all, Used for variations, Save attributes.  If you don't want to use all the attributes, you can remove those not relevant to this product.

4) Navigate to the Variations tab and Create variations from all attributes.

5) From the actions dropdown, choose Toggle "Virtual" and click Go.

6) Click the Expand link, then set your prices for each ticket type.

7) Save your changes.

8) Place this product in a category called Tickets and publish it.

9) Duplicate your product as necessary if your show runs on multiple days.

This is our procedure on show nights:

1) Edit the product, navigate to the Variations tab, and from the actions dropdown, choose Toggle "Enabled".  This prevents users from purchasing the product after your ticket lists are already printed.

2) Run our script below to generate the list of online orders for the night and print it.

We thought about publishing this as a plugin, but the odds of you needing to make the exact same report as us is rather slim.  So instead we'll post the code, which you can modify to fit your specifications.  If you need to make a report of all the customers who purchased a specific product, this is how you do.
<?php
/*
Plugin Name: WooCommerce Ticket Sales Report
Description: A plugin to create reports about sales of variable products, useful for general admission event tickets.
Author: Mango
Version: 0.2Alpha
*/
 
if ( ! defined( 'ABSPATH' ) ) exit; // Exit if accessed directly
 
$wctsr_category = 'Tickets';
 
if (in_array('woocommerce/woocommerce.php', apply_filters('active_plugins', get_option('active_plugins')))) {
 add_action('admin_menu', 'wctsr_menu');
 }
 
function wctsr_menu() {
 add_submenu_page('woocommerce','Ticket Sales', 'Ticket Sales', 'manage_options', 'wc-ticket-sales-report', 'wctsr_init' );
 }
 
function wctsr_init(){
 if (!isset($_GET['product_id']) or !is_numeric($_GET['product_id'])) wctsr_shows(); else wctsr_tickets();
 }
 
function wctsr_shows() {
 // At some point it would be worth it to paginate this but I CBF right now.
 // Might be easy to do it by year.
 echo "<h1>Ticket Sales</h1>";
 // Tried to use wc_get_products, couldn't make it work.
 $products = get_posts(['post_type'=>'product', 'product_cat'=>$GLOBALS['wctsr_category'], 'posts_per_page'=>-1, 'orderby'=>'date', 'order'=>'DESC']);
 foreach ($products as $product) {
  echo "<p>";
  echo "<a href='" . add_query_arg(['page'=>'wc-ticket-sales-report','product_id'=>$product->ID], admin_url('admin.php')) . "'>{$product->post_title}</a>";
  echo " - " . ((wc_variations_enabled($product->ID)) ? "Still on sale" : "sales over");
  echo "</p>\n";
  }
 }
 
function wctsr_tickets() {
 global $wpdb;
 // Find out what orders include the given product_id
 // Will this query survive an upgrade?  I have no idea; the docs are crap.
 $order_ids = $wpdb->get_results(
  $wpdb->prepare("
   SELECT i.order_id FROM {$wpdb->prefix}woocommerce_order_items AS i 
   INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS im
   ON i.order_item_id=im.order_item_id
   WHERE im.meta_key='_product_id' AND im.meta_value='%d'
   GROUP BY order_id
   ",
   $_GET['product_id']
   )
  );
 // Loop through the orders that contain this product_id
 $orders = [];
 foreach ($order_ids as $order_id) {
  $WC_Order = new WC_Order($order_id->order_id);
  if ($WC_Order->post->post_status == "trash") continue;
  $items = $WC_Order->get_items();
  // Initialize the $order array and start to populate it.
  $order = [];
  $order['name'] = "{$WC_Order->billing_last_name}, {$WC_Order->billing_first_name}";
  $order['phone'] = $WC_Order->billing_phone;
  $order['customer_note'] = $WC_Order->customer_note;
  // Loop through the items.
  $order['tickets'] = [];
  foreach ($items as $item) {
   // Ignore this if it isn't the item we're looking for - if that's the case, the customer bought tickets for multiple shows in a single transaction.
   if ($item['item_meta']['_product_id'][0] != $_GET['product_id']) continue;
   // Find the title of the event.  This is slightly hackish, it will be set for every item, but it's also the same each time.
   $title = $item['name'];
   // Add tickets to this order.
   $order['tickets'][] = ['type'=>$item['pa_t'], 'qty'=>$item['qty']];
   }
  // Add the order to the others orders.
  $orders[] = $order;
  }
 // Sort the orders by name.
 usort($orders, create_function('$a, $b', 'if ($a["name"] == $b["name"]) return 0; return (strtoupper($a["name"]) < strtoupper($b["name"])) ? -1 : 1;'));
 // Finally, display everything.
 echo "\n<h1>$title</h1>\n";
 // Warn the user if they forgot to disable sales for this show.
 if (wc_variations_enabled($_GET['product_id'])) echo "<h2 style='color:red;font-size:2em;'>This show is still on sale, therefore, this may NOT be the final version of this list.  You can suspend sales of this show via Products >> Edit >> Variations >> Toggle \"Enabled\".</h2>\n";
  // Loop through and display the orders.
  echo "<div class='tickets_list'>\n";
  foreach ($orders as $order) {
   echo "<div><div>{$order['name']}</div>\n<div>";
   foreach ($order['tickets'] as $ticket) echo "{$ticket['qty']}x {$ticket['type']}<br />";
   echo "</div>\n<div>{$order['phone']}</div>\n";
   echo "<div>{$order['customer_note']}</div>\n";
   echo "</div>\n";
   }
  echo "</div>\n";
 // CSS: Hide admin stuff, but only when printing.
 echo "<style type='text/css'>
 @media print {
  html.wp-toolbar, #wpcontent { margin:0; padding:0; }
  body { background:none; }
  #wpadminbar, #adminmenumain, #wpfooter { display:none; }
  }
 .tickets_list { font-size:11pt; }
 div#wpbody { font-family:Arial; line-height:normal; }
 .tickets_list > div { clear:both; border-top:solid 1px #ccc; page-break-inside:avoid !important; margin:1em 0 4px 0; }
 .tickets_list > div div:nth-child(1) { display:inline; float:left; width:36.5%; margin-right:1%; }
 .tickets_list > div div:nth-child(2) { display:inline; float:left; width:36.5%; margin-right:1%; }
 .tickets_list > div div:nth-child(3) { display:inline; float:right; width:25%; }
 .tickets_list > div div:nth-child(4) { clear:both; font-style:italic; }
</style>
 ";
 }
 
function wc_variations_enabled($id) {
 // Given a product ID, return the number of enabled variations.
 $product = new WC_Product_Variable($id);
 return count($product->get_available_variations());
 }
 
?>

 

If you would like a reply to your comment, you must leave your email address! We receive dozens of questions every month from people who don't leave us with any way to contact them, so we have no choice but to ignore the question. We try to reply to as many questions as we can, if we know the email address of the person who asked the question. Thanks in advance for writing in :)

Allowed HTML: <b>, <i>, <em>, <strong>. All other < and > will be replaced with &lt; and &gt;.