|
TKT
|
June 5, 2017 at 10:23 am
We’d like to have the counts for all approved registration per each event. So, we’re trying to come up with a query that pulls all event registrations records from the db and group them by the statuses.
We are able to get numbers and counts but they’re not correct (The counts do not match the stats shown on the interface).
This is the query we use
SELECT EVT_ID, STS_ID, REG_final_price ,REG_paid ,TKT_ID , count(TKT_ID) ,sum(REG_count) ,sum(REG_group_size) ,sum(REG_att_is_going) , sum(REG_deleted)
FROM wp_esp_registration
WHERE EVT_ID =24036
GROUP BY TKT_ID , STS_ID
ORDER BY wp_esp_registration.TKT_ID ASC
Did we make a mistake somewhere?
|
|
TKT
|
June 6, 2017 at 10:15 am
We tried counting all the related approved registrations for a particula revent below
Count
The query returns 48 for ticket_id 78 while the correct number should be 77 . All the other ones also miscounted.
List all
The total approved records returned from the query for is 227 while it’s supposed to be about 325.
Seems like some records are missing. Don’t all approved registrations reside in this table Wp_esp_registration?
|
|
Josh
|
June 6, 2017 at 1:11 pm
Hi there,
The model system can be used to get an accurate count. Here’s a link to some documentation:
https://github.com/eventespresso/event-espresso-core/blob/master/docs/G–Model-System/model-querying.md
An example:
$attendee_count = EEM_Registration::instance()->count(array(
array(
'EVT_ID' => 24036,
'STS_ID' => EEM_Registration::status_id_approved,
),
), 'REG_ID', true);
echo $attendee_count;
|
|
TKT
|
June 7, 2017 at 11:45 am
Thanks for your guidance, we are able to pull the record counts as needed. There are, however, some discrepancies in the counts.
As you see in the screen here, this is the number of tickets sold as shown on the interface
count
Total is 327 and the 4 types of tickets have sold for 78, 184, 17, 48, respectively. However, the records pulled directly from the database have them as Total sold 328 and 77,186,17,48. Which one is supposed to be more accurate? What might have caused the discrepancy?
On a related note, I’m not sure which records are considered “reserved” and aren’t the sum of all 4 reserved tickets supposed to equal the total listed at the top line of the captured screen (on the event date/time)?
|
|
Josh
|
June 7, 2017 at 1:13 pm
The numbers you cite alone aren’t enough to determine which of the two are “more accurate”, and not enough information for what would cause the discrepancy. I can tell you though that the interface grabs and displays the TKT_sold field directly from the _esp_ticket table.
With regards to the reserved tickets, there usually are not records attached to those because they are registrations in progress. You can reserve count by going to the Event Espresso > Maintenance > Reset page. There’s a scheduled task that reduces those reserved ticket counts in the event of an abandoned registration. If the task scheduler (also known as the wp_cron() function) fails, then it’s a good time to use that reset button.
|
|
TKT
|
June 7, 2017 at 5:29 pm
Thanks for your reply.
For investigation purpose, I exported the sales records file in excel and sort through it. It appears that the numbers pulled directly from the db (Total sold 328 and 77,186,17,48) are the correct one.
Could you tell me how the interface pulls the information, i.e. what criteria does it use (such as status= “approved”)?
|
|
Josh
|
June 7, 2017 at 8:05 pm
The interface grabs and displays the TKT_sold field directly from the _esp_ticket database table. It does not actually count numbers of registrations or consider registration statuses. If you go into your database application and look at the esp_ticket table for the tickets in question, you’ll see that it matches what’s shown in the interface.
|
|
TKT
|
June 8, 2017 at 11:24 am
I see. I’m curious to know what affects these numbers in the esp_ticket table, like how they got incremented each time. Knowing this might help finding out how the discrepancy occured.
Perhaps cleaning up the data might help?
|
|
Josh
|
June 8, 2017 at 12:12 pm
By cleaning up do you mean changing the values in the TKT_sold field so they match your approved registrations count? That’d be fine.
|
|
TKT
|
June 8, 2017 at 9:16 pm
Yes, but ideally I’d like to change it through the interface as I’d like to keep direct database changes to the minimum.
Incidentally, I hope to understand how the TKT_sold field get changed so that our admin team can handle the data more properly which will benefit us in the long run.
|
|
Giant Dwarf
|
June 8, 2017 at 11:28 pm
Just spotted this, and thought I’d chip in that we previously encountered and reported the same issue last year.
I ended up having to cobble together a script that updates the TKT_sold/DTT_sold fields in the database based on the actual sales numbers because it was causing all kinds of problems with sales and book-keeping.
|
|
Josh
|
June 9, 2017 at 8:59 am
Thanks for chiming in. So in that other thread it was noted that you had some Pending Payment and Cancelled registrations there, which do not count as sales.
|
|
TKT
|
June 11, 2017 at 7:12 pm
Thanks Giant Dwarf for sharing your experience. That was very helpful.
Josh, as always, thanks a lot for your support. Anyway, I think I will just ignore the TKT_sold field for now, at least for this event. Will strictly use the ones we pull from the DB.
|
