More useful registrant exports

Yes, it's Sailwave. My process now is to use Google Spreadsheets. My sailing event has 5 classes, so I just export the entire registrants list to the first sheet of a spreadsheet, then have several other sheets that draw data from the first sheet. When registrations change, I just overwrite the first sheet and the others change. Google spreadsheets has a nice process for importing CSV files and overwriting and existing sheet with it. Makes it fairly quick to update - just an Event Booking export, then an import to Google Sheets that overwrites the previous CSV file.

I've been playing the past couple of days with doing this using SQL queries on an imported CSV registrants file. For the life of me, I can't see how to do a direct query on the Events Booking database that would extract custom fields into database columns, so I need an additional step to export the CSV file then import it to a MySQL table that I can do queries on. The "gotcha" here is that MySQL CSV exports don't include the header row, with adds an additional layer of complexity. Also, the name has to be concatenated from first and last name, proper case (why can't people find their shift key when they register?) and various other things.

I want to do it this way because I can create a number of output files using queries, for example a single page per sailor they can sign, a smaller page that can be printed to an ID card, export for Sailwave, etc.

While I agree that there's probably a limited audience for this, I think a good suite of Joomla components that support sailing events would probably attract many clubs to the platform. There is already a small plugin to import results files to Joomla articles (I think you wrote it!). To my knowledge, there certainly isn't anything available for Wordpress, and the only other option is to go for one of the commercial pay-as-you-go platforms, which can get pretty expensive for small clubs.

Perhaps we should chip in to get Tuan to write the code!

If you want to discuss this offline, send me a PM.

abarrow wrote: Yes, it's Sailwave. My process now is to use Google Spreadsheets. My sailing event has 5 classes, so I just export the entire registrants list to the first sheet of a spreadsheet, then have several other sheets that draw data from the first sheet. When registrations change, I just overwrite the first sheet and the others change. Google spreadsheets has a nice process for importing CSV files and overwriting and existing sheet with it. Makes it fairly quick to update - just an Event Booking export, then an import to Google Sheets that overwrites the previous CSV file.

Yip thats what I would do for myself. But I will have about 10-15 event managers each year who should be able to download their entrants the night before the event depending when they set the cutoff date.I can't always be there to download it for them, and I have my doubts they would all cope. I have my doubts some of them will cope with clicking a button labelled "Download"

I've been playing the past couple of days with doing this using SQL queries on an imported CSV registrants file. For the life of me, I can't see how to do a direct query on the Events Booking database that would extract custom fields into database columns, so I need an additional step to export the CSV file then import it to a MySQL table that I can do queries on. The "gotcha" here is that MySQL CSV exports don't include the header row, with adds an additional layer of complexity. Also, the name has to be concatenated from first and last name, proper case (why can't people find their shift key when they register?) and various other things.

I haven't looked at the DB structure at all. But that sounds an awfully complex approach!

I want to do it this way because I can create a number of output files using queries, for example a single page per sailor they can sign, a smaller page that can be printed to an ID card, export for Sailwave, etc.

I suspect the SW guys might say those pages should actually be in SW! So import the competitors to SW and print whatever.
We've never gone down the ID card route.

While I agree that there's probably a limited audience for this, I think a good suite of Joomla components that support sailing events would probably attract many clubs to the platform. There is already a small plugin to import results files to Joomla articles (I think you wrote it!). To my knowledge, there certainly isn't anything available for Wordpress, and the only other option is to go for one of the commercial pay-as-you-go platforms, which can get pretty expensive for small clubs.

Yip. So the SW results solution for CMS's is to export and paste into an article.
The xtd-editor button is mine. I got it working and then lost the will when I discovered Sailwave4Joomla and similar names are blocked by Joomla Extension Directory. I still have plans to progress that to have some form of results menu that picks up the files etc from the FTP directory

Perhaps we should chip in to get Tuan to write the code!

I think the snag with that approach is lets say it costs £500 for Tuan to develop in developer time. If we split it thats quite pricey for I guess two small clubs to simplify something we can already do in a spreadsheet. If we said to Tuan we will chip in £50 each he needs to find 8 others to chip in and either doesn't develop until he has the money in the bank (we could be waiting a while!) or develops and carries the risk...
...So my suggestion would be that I develop the basic code and share either through this site if they are happy to have it, or as an extension on the extension directory. But let me have a look at the DB structure first...

I **think** this might be along the lines of what you are looking for. Attached as file as forum thought it was a S-Q-L injection


drive.google.com/file/d/0B8JydPjJg0oEM3Z...bDA/view?usp=sharing

Hummm, did you get a result from this? When I run it in phpmyadmin is get a null result. I've got 220 registrants, and I think I've adjusted the database table names correctly.

Never mind. I didn't notice the Event_ID number requirement. Works! Very cool. Thanks Calum!

OK - I've created a BitBucket Repository as a place holder for this when I get to it unless Tuan has already created something before then: bitbucket.org/shinysolutions/plg_sw_eb_extract/

Have you tried this with more than one value from the join? I seem to get nulls. Sailno works, but when I try to show another column, it's just null values.

Clearly I'm not even close to a SQL expert - I can usually figure out what others have done, but that's about it!

Ah - you are right. I'm able to get 1 row per field but not compress them down so I've re-written it completely to be a series of nested Selects. Updated SQL is here:

bitbucket.org/shinysolutions/plg_sw_eb_extract/wiki/SQL

Wow. A lot of code!

I think this is going to be tough to scale - in my case I have 41 custom fields spread across 6 different EB events, some of which I need to do calculations on (for example, with Optimist, I need to calculate age at the start of the event). Sailwave has a LOT of data fields that people could be using.

I think exporting the fields to another database once, then updating it as necessary, would reduce the complexity of the coding and greatly simplify troubleshooting. The code used in components/com_eventbooking/view/registrantlist/tmpl/default.php might be a good place to start. Just a thought.

I wasn't expecting you to try and manually code all 41 fields over. I'm thinking I should be able to create a ?plugin that would provide a mapping screen which lists all the SW Fields and provides a simple means to attach the EB fields to them. I'm expecting this is a configure once - run many times scenario.

Interesting point on the maths. I've just been working on an event which had a requirement for parental consent for <18's at the start of an event, other events have requirements for child to be under a specified age at the end of an event. I'd actually prefer to populate that age into a field during registration, with validation applying to that during the registration process either to ensure entry requirements met, or to make a different price apply. But I can see that there will always be a requirement to do something in the SQL.

Its a lot of code (a) because I've laid it out for ease of reading (b) because it is! But adding an extra field would just add something like:

JOIN (
S-E-L-E-C-T
fv.registrant_id AS 'id',
fv.field_value AS 'DateOfBirth'
FROM jos_eb_field_values fv
JOIN jos_eb_fields f ON fv.field_id = f.id
WHERE f.title = 'Date of Birth'
) AS dob ON dob.id = core.id

And then in the very first selection statement you'd add either just dob.DateOfBirth to the list or if you were calculating you might do something like: (I'd obviously add a further join to pull in the event date rather than an arbitrary date.)
To me the advantage of not creating a temporary table is that you may still need to run of and create joins from the temp table to other information in you database such as start and end dates etc. Unless your entrant database is humongous I don't think the performance hit is likely to be noticeable.