Export/SQL statement for Managing Your Orders and Attendees

[mierlp]

Hi

We are developing a app for scanning by mobile phone. We would like to have a query statement which
we can use to get all th nessacary fields/data form the tables to create a new table called SCANNING.

Based on this article https://theeventscalendar.com/knowledgebase/managing-your-orders-and-attendees/

it’s possible to generate a csv file. We would like to build a separate tool which runs on the server and
looks every 15 minutes if a event is closed and after that it has to run the query en saves the data
in a separate table called ‘SCANNING’.

Can this be done and is this query statement available ?

Regards Peter

[Andras]

Hi Peter,

Thanks for reaching out to us.

That sounds like an awesome app to build. I’m quite sure it can be done.

Please note that we are limited in supporting customizations but I can definitely give you some pointers and guidance one this.

What data would you exactly need that you want to put in the SCANNING table? What would be in a row of the table?

Cheers,
Andras

[mierlp]

Hi Andras,

Thanks for the reply. Attachted 2 screenshots from the the app.
Green when valid and red when not valid or already scanned.

What we need is the following information :
– Event Name
– Event Number
– Event Date
– Event Time
– TicketNumber
– LastName
– FirstName
– MiddleName
– Order Date
– Order Time
– Order status or Payment status

Each order/payed ticket will be a record in the table

Would be great if you can help us out.

Greetz Peter

• This reply was modified 6 years, 1 month ago by mierlp.

[Andras]

Hi Peter,

As it happens, we’ve started work on a ticket scanning app ourselves. We don’t have a release date yet and in fact the work has been paused while we focus on other things but we do have a working prototype for this already.

To help you with your customization you could use the following functions / methods:

This can be used to get the event ID (and ultimately the event object from the attendee ID:

Tribe__Tickets__Tickets::get_event_id_from_attendee_id();

 

This Can be used to get the meta key that links attendees with orders (and so ultimately one can work their way back to the order itself using this:

Tribe__Tickets__Tickets::get_attendee_order_key();

 

Also there is a whole bunch of stuff in the code of Event Tickets and Event Tickets Plus, so you could look through that as well.

All that we do with tickets and attendees is stored in the wp_posts and wp_postmeta tables and in the case of WooCommerce also in their customer tables.

I hope this can get you started.

Cheers,
Andras

[mierlp]

Hi Andras,

Thanks for the respons. A scanning app would be great if you can sell it
together with the theme. This prototype i showed is also working but the
only thing we needs is the data.

Which tooling do you use to retrieve the data from the memo fields ?
Can i use an query builder because i don’t recognize the syntax how
data is stored. Maybe because we developed desktop/web applications
where data is stored in records/fields

Regards Peter

[Andras]

Hi Peter,

Which tooling do you use to retrieve the data from the memo fields ?

I’m sorry, I don’t quite understand the question. Which memo fields are you referring to?

As for tooling, in our plugins we use built-in functions / methods – also from WooCommerce – to retrieve the ticket data and all data that is connected to a specific event or ticket.

Every event is a line in the wp_posts table, and has several meta data in the wp_postmeta table. And that’s the same for the tickets.

The sold tickets are also pretty similar, every order is a line in wp_posts with the corresponding metadata, and I believe woo is also saving some stuff in their own tables.

A.

[mierlp]

Hi

Oke thanks…i will try to use a query builder to retrieve some data
and see where data is stored.

Regars Peter

[Andras]

Allright, good luck!

I’ll leave this open for now, in case you need some further info.

Note that if there are no new posts here for 3 weeks, then the thread will be closed automatically. In case you would need further help after that, then you can always open a new topic.

Cheers,
Andras

[mierlp]

Hi Address,

I’m back…but i think we have to give it up..with a SQL statement and not knowing
the relation between different tables it’s not to do.

I would like to use a tool like Navicat to create a query for the data
to retrieve all database. Only I do not see the connection between the tables.

When I create an event and I search for the event name, this occurs in 2 tables:
– wp_options
– wp_post * (here is the correct ID I think, eg 2167?)

When I search on this ID (2167), I find it in the tables:
– wp_options
– wp_postmeta *
– wp_post
– wp_term_relationships

When I filter in wp_postmeta on the post_id (2167) I get data
about the event and fields like:
– _EventVenueID?
– _EventOrganizerID?

I miss the connection and where to start in which order to retrieve the correct data
for a closed event.

I don’t see relationship on field level between :
Event -> Tickets -> Orders / Customer / TicketNumbers (_tribe_wooticket_security_code?)

• How do you export the CSV from the plugin?
• Is this based on JSON or something else … because in some
  there is all info in one line like first+lastname, address, zip, city

Regards Peter

[Andras]

Hi Peter,

When an order for a ticket is placed, then a whole bunch of data is created. And I agree, it is difficult to track them down. Hopefully this explanation will help:

Here’s a screenshot, part of the wp_postmeta table for a purchase.

I guess you need to start with the user ID and the event ID.

If you have the user ID, you can look up the purchases (ID of the purchased tickets) in wp_postmeta.

From there you can use the ID of the purchased tickets to run a query in wp_postmeta and you will get further IDs which you can see in the screenshot.

You can use those IDs to fetch further information about the purchase, like name and address of the buyer, event details and so on.

In terms of code the csv export of the attendee list is handled – partially – here:

wp-content/plugins/event-tickets/src/Tribe/Attendees.php

There is also a filter you can use to add extra columns, line 509:

tribe_events_tickets_attendees_csv_export_columns

Hope this helps you move ahead.

I’ll be here for further questions.

A.

[Support Droid]

Hey there! This thread has been pretty quiet for the last three weeks, so we’re going to go ahead and close it to avoid confusion with other topics. If you’re still looking for help with this, please do open a new thread, reference this one and we’d be more than happy to continue the conversation over there.

Thanks so much!
The Events Calendar Support Team

[Author]

Posts