More useful registrant exports

Here's the code I use on the CSV export (after importing into MySQL) to calculate age and which Optimist fleet that represents. You need to use the FLOOR function on the date calculation, otherwise you run the risk of rounding up on ages (been there... big mistake).
FLOOR( ( DATEDIFF( '2016-02-24', STR_TO_DATE( `Date of Birth (dd/mm/yyyy)`, '%Y-%m-%d' ) ) ) / 365.25 ) AS `AGE`, IF( FLOOR( ( DATEDIFF( '2016-02-24', STR_TO_DATE( `Date of Birth (dd/mm/yyyy)`, '%Y-%m-%d' ) ) ) / 365.25 ) < 11, 'White', IF( FLOOR( ( DATEDIFF( '2016-02-24', STR_TO_DATE( `Date of Birth (dd/mm/yyyy)`, '%Y-%m-%d' ) ) ) / 365.25 ) > 12, 'Red', 'Blue' ) ) AS `Fleet`

Dividing by 365.25 is technically flawed. It can result in someone changing age a day early occasionally.

I wondered how long till fleet/flight allocation was going to crop up! Am I correctly understanding that if Under 11 they are White, 11 or 12 they are Blue and 13 or more they are Red?

Flight allocation rules would presumably be event specific? (i.e. if you run Oppi Opens and GP-14 opens the GP-14s may be flighted on something else other than age, and certainly not on age 11-13)

So there would need to be a Registrant Extract with general mapping and then the ability to map specfically for a specific event?

Registrant extracts from EB can be done on a per-event basis or for the entire registrant list. I do it for the entire list then use the SQL queries to sort out what classes I want, so I only have to do one download and import.

For Optimist dinghies, 10 and under is White Fleet, 11&12 are Blue and 13 and over are Red. That's per class association rules. I suppose events might decide to change that, I know we certainly do from time to time, but even if the program did the appropriate calculations, division names can always be ignored, or scoring can be done using class or fleet. Occasional one-day errors in birthdays don't worry me - if it by (slim) chance it happens they can issue a Scoring Query during the event and it'll get fixed. Trust me, they pay a lot of attention to these things, and there's an expectation that the scorer isn't perfect. Doing this per class rules for each class is probably a good place to start. I'm also doing Laser Masters in this event, which has age breaks at 35, 45, 55 and 65. The same logic will be used for that.

I like the way Sailwave does mapping when you import a CSV - they detect the column names then give you the option to map to established fields in Sailwave. If Sailwave sees a field name that looks familiar, it maps it, but does give you the option of changing that. You can also ignore fields, which would be important in an Events Booking CSV export where things like payment and transaction ID aren't important to the program.

So, I think program flow might go something like this:
- The extension imports the entire CSV file into another set of tables (an "Import" button - you don't need to do it until you are ready to set up the scoring).
- You get some options for filtering on fields. In my case, the class names are the same as the event names, so when I want a Sailwave extract for "Optimist" I just use WHERE 'event' = 'Optimist'.
- You take that query and map it to Sailwave fields, with options not to map some fields.
- You download a Sailwave-ready CSV file. If the field names are close enough, you might not have to do much in the way of telling Sailwave what fields in the CSV map to Sailwave fields.

I'm thinking this conversation is probably not much interest to other EB users - perhaps we should take it somewhere else?

abarrow wrote: I'm thinking this conversation is probably not much interest to other EB users - perhaps we should take it somewhere else?

Not out fault that they aren't into the best sport there is

But absolutely it needs to move off this thread. I'd prefer to keep the discussion public if I can in-case anyone else comes along with an interest or who wants to chip in. So should we try putting it on the bitbucket for where I'm going to develop the code...? It doesn't have a proper discussion board but we can use the issue tracker. I've opened issues for workflow and data-manipulation (i.e. calculations, flight allocation, concatination etc)
bitbucket.org/shinysolutions/plg_sw_eb_extract/issues
You'll need a free bitbucket account