Title: Event Reporting
Last modified: August 30, 2016

---

# Event Reporting

 *  [bcurtis65nj](https://wordpress.org/support/users/bcurtis65nj/)
 * (@bcurtis65nj)
 * [10 years, 9 months ago](https://wordpress.org/support/topic/event-reporting/)
 * I recently moved one of my clients to OpenTickets and I am never going back. 
   Well done, thanks.
 * One of the featurs my clients like is the ability to run order detail reports,
   will-call/check in reports etc. I did this with a custom portal I wrote and want
   to adapt to this table structure.
 * question: Where do I find and how do I join together the data on openticket events.
   I need order#, consumer addr, event ordered, qty for each ticket and type and
   tax, order total.
 * I do find Wooo to be deficient in this area. Am I missing a cool plugin or are
   you working on a more robust reporting add-on?
 * [https://wordpress.org/plugins/opentickets-community-edition/](https://wordpress.org/plugins/opentickets-community-edition/)

Viewing 1 replies (of 1 total)

 *  Plugin Author [loushou](https://wordpress.org/support/users/loushou/)
 * (@loushou)
 * [10 years, 8 months ago](https://wordpress.org/support/topic/event-reporting/#post-6422561)
 * Hey [@bcurtis65nj](https://wordpress.org/support/users/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

Viewing 1 replies (of 1 total)

The topic ‘Event Reporting’ is closed to new replies.

 * ![](https://s.w.org/plugins/geopattern-icon/opentickets-community-edition_bcbcb3.
   svg)
 * [OpenTickets Community Edition](https://wordpress.org/plugins/opentickets-community-edition/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/opentickets-community-edition/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/opentickets-community-edition/)
 * [Active Topics](https://wordpress.org/support/plugin/opentickets-community-edition/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/opentickets-community-edition/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/opentickets-community-edition/reviews/)

 * 1 reply
 * 2 participants
 * Last reply from: [loushou](https://wordpress.org/support/users/loushou/)
 * Last activity: [10 years, 8 months ago](https://wordpress.org/support/topic/event-reporting/#post-6422561)
 * Status: not resolved