wp-SwimTeam

It is somewhat duplication of the Report Generator functionality but I went ahead and enhanced the CSV Roster Export to include the age, adjusted age, and the age group assignment.  With the clean up of the age group calculation, the information is now readily available any time a swimmer record is queried.  As such, the age and age group now appear on the detail profiled as well as a couple other places.

Over the weekend I was speaking with one of the swim team parents who was asking me about our web site and I showed her what she could see about her daughter.  As soon as I popped her daugher’s information she noticed that she was placed in the wrong age group.  Oops.

I had seen this situation previously but couldn’t pin it down but now that I had a swimmer I could test against, I dug into it.  It turns out that I had made the age calculation much, much, harder than it really needed to be.  And it was wrong.

I was trying to use MySQL’s PERIOD_DIFF() function so the age and cutoff date adjusted age would be returned as part of the query.  It turns out that PERIOD_DIFF() only accounts for months (which I knew when I used it) but not taking into account the days causes the calculation to be wrong and limits the age cutoff date to be the first  of a month which may or may not be acceptable.

My first search yielded this example which turns out to be wrong as it doesn’t always return the correct age.

Here is the correct SQL to calculate age:

SELECT YEAR(birthdate) - (MONTH(CURRENT_DATE()) < MONTH(birthdate)) -
    ((MONTH(CURRENT_DATE()) = MONTH(birthdate)) & (DAY(CURRENT_DATE()) <
    DAY(birthdate))) AS age 

Here is the correct SQL to calcualte age based on a specific cutoff date, in this case the cutoff date is June 1st:

SELECT YEAR(CURRENT_DATE()) - YEAR(birthdate) - (MONTH('2008-06-01') <
    MONTH(birthdate)) - ((MONTH('2008-06-01') = MONTH(birthdate)) &
    (DAY('2008-06-01') <= DAY(birthdate))) AS agegroupage

By using the calculations, the swimmer’s age and age group age are always returned with the query as opposed to computed on the fly.

I have started working on a report generator for the plugin.  The report generator will allow an administrator to run reports for the swim team with control over what is included in the report as well as the ability to apply filters.  The report can be generated as either a web page (table) or as a CSV download which could then be loaded into Excel.  For the most part I have completed the GUI, now I need to actually generate the report.

It has been a pretty hectic week for Swim Team and as such, I have found some bugs and some minor annoyances and tried to fix most of them.  The most serious bug was in the SDIF export which I was working with one of our coaches on so he could started working with WinSwim with our roster.  If the parent profile wasn’t entered, the last last profile found was being used to construct the D1 and D2 records.  Now when a parent profile record isn’t found, it reverts to the site admin’s record to populate the data.

I also switched a bunch of the swimmer lists (roster, all swimmers) back to sorting by last name because that is how the data is usually looked at.  I also fixed a bug in the age computation which was rather elusive – it only appeared once in a while.  It turns out the logic was wrong in an age computation function I had grabbed off a PHP site a while back.  Lastly, I made progress on the swimmer id/number/label but am not yet computing them.  I think all of the infrastructure for them is down now.

Over the weekend I have spent a lot of time thinking about Swimmer Ids.  I need a fairly flexible solution because it seems that there are a number of schemes used by teams and each is slightly different.  At least, that is the case here in TSA country.

1. Simple numeric sequence – each swimmer is assigned a number starting at some predetermined number and incrementing from their.  This is very simple and easy to implement.
2. Gender numeric sequence – similar to the simple numeric sequence but there is two groups of numbers, each each with a gender prefix (B, G) to ensure they are unique.
3. Alpha numeric sequence – similar to the gender numeric sequence except each age group and gender is assigned a letter as a prefix and the swimmers within that age group are sequenced.
4. Numeric range (this is what our local team uses) – each age group and gender is assgined a range (e.g. 7-8 girls are numbered 800-899, 7-8 boys are numbered 700-799) and the swimmers within that range are numbered starting at the lower end of the range.
5. USA Swimming Id – this is very simple as the Swimmer Id is constructed using a format defined by USA Swimming.

If there are other scenarios, I have not observed them in the 5 years which I have been going to swim meets.  To start with I will implement the simple id (which is already done since it can use the unique database id for each swimmer) and the numeric range (since that is what we use).  The alphanumeric range should work as well since I believe I can implement both using a “alpha-numeric” prefix for each age group.

I also committed changes which add some additional checking for the swimmers age.  I had found that some parents had missed their child’s birthday resulting in it being set to the current date and making their child 0 years old.  I added a check to compare the swimmer’s age against the minimum and maximum age (ours is 3-18) to ensure a swimmer isn’t too young or too old at registration time.

Today I committed new functionality which implements a new option called Auto-Register.  Setting Auto-Register to Yes on the Swim Team Options panel causes new swimmers added to the system to be automatically registered for the current season.  This saves a step which I found many people missed anyway.  By default, this option is enabled.

It has been about a month now the MacDolphins have been using this plugin in conjunction with WordPress and everything is going pretty well.  Next thing to implement is the Swimmer ID – we need those next week for the Swimmer Open House kickoff party!

This morning I committed a bunch of changes which allow the roster to be exported in CSV format.  The CSV format can be opened in Excel or other tools which can deal with comma separated data.  We’re using the data to order t-shirts and verify that all of the families of the swimmers on the roster have been billed correctly.  Now that the CSV export is working correctly, I will go back and modify the SDIF export so it sends a file download to the browser as opposed to having to copy and paste it like it currently is.