Event Reporting

Hey @bcurtis65nj,

First, sorry for the delayed response. Lots of new clients are demanding my attention and I am finding myself spread pretty thin, LOL.

Next, thanks for the amazing complement. It really pleases me to hear that you like our work, and that it is helping you and your client to sell tickets

Finally, to your questions. We are working on a Reporting Extension, which will provide a more robust system for adding reports. It will also include several new reports, mainly dealing with financial breakdowns of events, but we are always open to suggestions for stuff we have not thought of, so let us know what you had in mind, and if it is not in the list, and makes sense to be in the list, it will get added.

You also had a question about how to aggregate the different bits of info together, so that you can create a (or a series of) custom reports. I can help you there. Below is a detailed explanation of how it all links together. For any readers no interested in that, you can bail now :), cause I tend to be long winded.

The focal point of almost all of the data is the table called wp_qsot_event_zone_to_order. As long as you have some bit of information (like the order number, or the event_id, or product_id of the ticket), you can query that table to get most of the linkage information you need to produce a meaningful report.

Once you have rows of information from that table, you can link each row to an order/order_item (or both depending on what you are after). You ask specifically for several different pieces of information. I will address each below, telling you how to fetch the information, based on what I have said so far. A word of caution, Almost no data in the WordPress database is in normalized form, especially when dealing with meta_data. WooCommerce orders are no different. All order meta, is actually ‘postmeta’ found in wp_postmeta. Some of the data you are asking about lives in that table.

* order#: the order number is one of the columns in wp_qsot_event_zone_to_order. once you have a row from that table, you have the order number

* customer address: this one is a bit trickier. once you have the order (above) you can use the order_id to query the wp_postmeta table, find all the ‘billing_%’ meta fields for the order, and use the WC_Countries::get_formatted_address() method (found in woocommerce/includes/class-wc-countries.php) to construct a well formatted address, despite country.

* event ordered: the event_id is part of the wp_qsot_event_zone_to_order table. once you have a row from there, you can throw event_id into get_post() and that will return you a post object

* qty: this is a value stored in the wp_qsot_event_zone_to_order table. the column is called quantity. just tally them up

* type: I am not exactly sure what you mean when you say ‘type’ this could be any number of things. I can certainly tell you how to link to it if I know what ‘type’ specifically you are talking about

* tax: the total tax for an order is stored in a meta_data value for the order. this means once you have the order_id from wp_qsot_event_zone_to_order, you can do a look up in the wp_postmeta table, for keys called _order_tax and/or _order_shipping_tax if you need to include some sort of tax for shipping also (if that is even relevant)

* total is similar to tax. you need to use the order_id to look up the value from wp_postmeta. the key for total is called _order_total

Now, with that, I must say, do not use joins for this. The entire reason we offloaded all of this data to it’s own table is because performance on the wp_postmeta table is very slow, in almost all cases, especially when you start looking things up by meta_value (like event_id). I recommend a different approach, which will take a little longer to run, but will not require GB of ram or GHz of processor.

The gist is to simply process the list of results for the report a small group of results at a time, in multiple passes. This may not be relevant to you, because I do not know how many orders you are talking about here, but I would say if there are anything more than 1000 orders, you definitely want to throttle your reporting, like I describe next. I recommend doing them in groups of 100-500, and storing the results into a table or file until you have completed processing. Then, after the main work is done, push raw data out to the end user.

I also recommend, as I said earlier, not doing a big join. The main reason for this is because of another comment from earlier, which is that the wp_postmeta table has major performance problems as it is. Doing a join will only compound the problem, because now you are doing a potentially multi-million row join on a table that has performance problems. Bad Idea.

Instead, do one query to grab a group of records from wp_qsot_event_zone_to_order. You may be able to do one join here to the wp_posts table, to get the post_title of the event (since the wp_posts table does not have any performance issues, especially when using the primary key, wp_posts.id). Then, cycle through your list of rows in PHP, and aggregate a list of order_ids that you need meta for. Use that order_id list, and perform a single second query to the wp_postmeta table, asking for all _order_total, _order_tax, _order_shipping_tax, and _billing% records for all matching order_ids. Offload sorting and grouping of that data into an array of associative arrays, to PHP. Then, once you have done those two querys (for this group of rows), start piecing it all together for a finalized report row. Store that report row somewhere (a csv file, or db table, or whatever, just not an array in PHP). Then finally move to the next group of 100 matches (or however big your groups are).

In between each iteration, where you grab the next group, you may also want to burn the internal WP caches, by doing a $GLOBALS['wp_object_cache']->cache = array() (to clear out wp_cache’s associative array of internal wp cache) and a $GLOBALS['wpdb']->flush() (to clear out db qurey results cache). Otherwise, you may run into a memory limit breach, causing your script to fatal error.

I want to say that the goal of all of the above is not to force you to do it my way. Do it your own way. Find your own path, by all means I did. I am merely trying to impart some wisdom here, from years of experience writing reports in PHP, and doing data migrations which have very similar problems to writing reports. If you find any of the above useful, awesome, and I am glad to help. If not, it is all the same to me. I just really like writing about things I know, as does anyone I suppose.

Hope this helps,
Loushou