Youth Sports Sites

[imNotScott]

I've used Mambo (and will soon transition to Joomla!) to maintain a site for the local youth soccer club that I volunteer for.  I would really like to help develop a component that would allow players (or their parents) to register for our program through the website.  This could be of great value to youth clubs everywhere that currently pay for this service.  Most of the companies that offer this service charge between $2 and $4 per player added to their database!  For my club, that would translate to about $2400 to $4800 per season and we really can't afford that.

I'm not a PHP developer, most of my work has been in Borland Delphi (Object Pascal), but I'm a pretty quick study and I've browsed a few "PHP for Dummies" type books.  Anyone else out there have interest in a project of this type?

Thanks,
Scott(not)
http://www.damascussoccer.org

[jkrobin]

(not)Scott,

I think such a component would be a great thing. It may be worth it to look into using the Community Builder component, though all desired functionality may not be available in it.

I currently use Joomla as the platform for my daughter's travel soccer team (http://u12g.miscmeta.com), and have had some thoughts about using it for the local club as a whole.

jkrobin

[imNotScott]

I guess CB could be modified to fit this purpose, but I wonder if it would be easier just to build a component from the ground up?

Does anyone know if there is good documentation on component building out there?

[jkrobin]

For brainstorming purposes, maybe you could post a short list of the required (and desired) functionality that such a component would include. I'd be happy to stay involved in this (potential) project with you, although my PHP skills are very limited.

jkrobin

[imNotScott]

OK, off the top of my head…..

Needed functionality:
Front end.  The front end would consist of a registration form for players.:

• Information would include “stock” fields such as player name, date of birth, address, etc.. as well as custom fields particular to an individual organization.
• A parent should be able to register multiple players at once.  Many organizations offer a “family discount” on registration fees such as a $5 discount for each additional player in one family.
• Players would have to provide a valid email address and respond to a verification message before being allowed to register for a program.

Payments.  The ability to collect payments is necessary.  Credit card payment is an option, but I don’t know how to handle that.  Are there standard hooks that can be provided to send credit card information to a gateway?  Is Paypal easier?  At the very minimum, a registration could be put on “hold” until a check gets received and then the player’s registration would be reconciled.

Administration.  There is a lot you can do with this from the back end.  A page to reconcile payments would be an absolute necessity.  A “team builder” module that allowed you to take kids from a pool of unassigned players and place them on a team would be great.  From there you could add modules to help schedule games, record results, etc…

[jkrobin]

A couple of thoughts:

- back-end administration of the league could be in a different component. Limiting this one to just registration might make it easier to get done. (I've seen a few of these, including LeagueManager and LigaManager....or something like that)

- adaptation of a currently available GPL'd  component might be the easiest path.

Some research:

- here's a site that uses the phpShop component for resgistration:
http://bsll.org/component/option,com_ph ... temid,188/

- I came across a site earlier that used a custom-built setup using the Facile Forms component for league registration... don't have the link right now.

jkrobin

[jkrobin]

Here's another example of partial functionality to possibly build upon:

AKReservations v1.40beta

The demo site for the new version (http://akr.tuntange.net/) shows several examples, one of which is "Soccer Registration." By no means does it have all the desired functionality, but it may be a starting place.

Of all the possible solutions other than a custom compenent, I like the possibilities of the PHPshop component the most. I've not played with it much, though, so I don't know to what extent it can be customized/extended, etc.

jkrobin

[gjj]

Hi,

I run the web site for my rugby club www.redingensians.info, and am also trying to do some of the things you're trying to accomplish. However, it's not easy.

My grand plan is to use CB as the front end for registration. This will hook into (if it all works when the latest CB come out) civiCRM, which is designed for management of large communities. It's not yet completely integrated, but moves uite fast, and is certainly very comprehensive. It still lacks a front end for Joomla! though. I think this could be built with CB, or using some other tool. Users would be able to view their own details, and also change some of them.

The problem with Membership databases, esp of the type we are talking about is ensuring that there is an entity called Family, or has that concept. It must encompass many members living at the same address, but also differnet addresses (students). And amongst the family, the members may have identical or differnet contact details and preferences (email/cell phone etc.) I delved into using a Church database - as it seemed the closest match for a time - had most of the right concepts - but was too far removed from Joomla!

If you hunt round both the old *ambo forums and this one you'll see variousposts about amanaging memeberships and subscriptions which may be valuable to look at before starting off on your own component

Anyway, I'll watch this thread with interest....

Gareth

[imNotScott]

My grand plan is to use CB as the front end for registration......

What is CB?

[gjj]

Community Builder

[burner]

I've put several youth sites together & would like to see a registration mod/com done for Joomla!

There's a huge need for one in the volunteer community of soccer where registration costs to 3rd party paid handlers take money away from the club programs & the kids.

3rd party registration is a huge business that I'm sure fits into the mission statement of what Joomla! is all about.


Here's some I've done -

http://www.richmondgirlssoccer.com
http://www.richmondaviators.com
http://www.richmondsoccer.com
http://www.worldofsoccer.com

[tcp]

The access/registration for fee issue is a notable issue and I'm not sure if it has been sufficiently addressed.  I certainly haven't found a good answer yet.  It seems like the best (and most complicated) solution out there at the moment is Access Granted (http://www.mambobrothers.com/mambo/ ).  This is a commercial component and requires a fair amount of modifications to core system files, I understand.  I'll ask someone who is using it.

The problem with paid registrations, I understand, is related to the current Joomla API.  J! 1.5 is suppose to address some of these issues but I'm not an authoritative source to explain how.

Anyways, just some info.

tcp

[tcp]

http://akr.tuntange.net/
I case you're interested, check out the footballsoccer registration.

[imNotScott]

I have built my own registration system for the Fall 2006 season.  It's almost complete, but not yet ready to share with the world.  Since I'm a PHP newbie, I didn't build it as a Joomla module, but just straight PHP forms.

I've also done a lot of work on a back end league management application, but built that as a Java-Struts application.

I imagine the registration forms part could be wrapped inside a Joomla page but it is customized for our Club so I doubt it would be of much use to anyone else but me.

[burner]

I gave some Andre' some input on that soccer reg component when he was 1st starting out. It has a lot of promise & it's probably the one I will use.

Basically w/ soccer (sports) reg programs the collecting of money is not a big requirement for online reg. Most clubs need signed reg, checks, checks for uniform deposits & etc in the end. Online reg companies who collect a piece of the action when someone regs have made a niche for themselves that really isn't required.

The most important part of online reg is the collection of the data so you can verify players who when to tryouts did pay & those wanting to play non tryout teams get placed on teams.

Bottomline is the process is over thought & not focused on the basics.  :(

[tcp]

Maybe community builder (CB) is a good choice for you.  This would allow you to have profiles, a forum to post questions, and the like. 

tcp

[cgarst]

I am using Community Builder, that is integrated with Virtuemart and Letterman.  Latest versions of all with security fixes. The integration with virtuemart allows the billing fields to be displayed underneath the community builder fields.  I setup all the cb fields differently based on the sport currently in season, and just unpublish / publish them.

We use regular mod_login on the other side of the website for staff to use, there is a 'coaches corner' they use to post articles on.

I also use letterman to import all the users email, and use it to send out notifications for registration, news, etc...

Last fall we went live with it and successfully registered around 150 basketball players.  They had the option to either pay via paypal or mail in a check/money order with the confirmation email after they register.  Paypal charged around 80 cents for each registration.

For the reporting, I use JamboReport, it generates an .xls file of all the registration data that I can import into an access database.  The only thing I use access for is further manipulation of the data, ie. printing reports with a player on each sheet, to give out to the coaches during the draft, and generate reports for the athletic directors.

For modifications, most of them were done to virtuemart to customize the product towards the player, for example a 'product' called "Baseball Registration" in the shopping pathway also displays the username from the database, ie. "Baseball Registration For John Doe" and I had to hide the quantity box, since that is not needed.

We require a separate registration (username / password) for each registered player.

All in all, it works really good, and we are not having to pay any 3rd party fees.

I guess the only downside is that parents cannot register multiple kids at the same time, but wait....they couldn't do that before, they had to fill out a separate piece of paper!

Anyone interested in particulars about mods, integration, cb, etc, let me know.  The website is gsasports dot org.

Chuck Garst

[augustynr]

Chuck,
Can I get more info please.
robert

[nickolai]

I'm trying to do the same for my local soccer league http://www.ashevillesoccer.net. (asheville, nc)

My first attempt was to use a subscription program (AEC) and I'm somewhat satisfied with the results.  It would work if only I needed to keep track of who's paid/unpaid but 30 other people need to know as well (team captains).  If only I could have the paid subscription attached to the CB user profile and of course viewable in CB user lists.  (FYI: CB is coming out with a subscription component that probably will do this... not soon enough though registration is next week!)

So my question is: Is there anyway to attach a virtuemart purchase to a CB user profile?

Thanks for your help, Nick


Written 2-10-07
(ACE could work in conjunction with JamboReports but i decided to use virtuemart because the path had already been blazed by "cgarst ".)

[cgarst]

Nickolai and augustnyr,

I think that would be a rather extensive hack, to try to pull in all the purchase info into the CB user profile, I actually thought about it before I did what is in place now.  I think the ultimate solution is if someone could build a report module on to Virtuemart and have reports that could do this.

If all you need is to keep track of who's paid/unpaid, this will do all you need, and is very portable because anyone who has proper access to the site can run the report themselves, and view them if they have capability to open excel spreadsheets.  I have coaches that log in and do it.

Here is basically what needs to be done:

1.  Configured CB registration fields on my site to have the fields I need to capture for the particular sport in season.  These are basically the same fields that are on our manual registration form that is distributed to schools and the parks and rec. dept.

2. Installed Virtuemart (VM), and the VM CB plugin, which puts the VM payment fields underneath the CB fields during registration.

3. Installed JamboReport Component, and built a custom report that MERGES the CB fields with the Payment fields from VM, and outputs them to MS Excel Spreadsheet.  I would rather do it this way in my situation because I need to be able to take the spreadsheet and reformat for reports out of Access database.  I published the component on the site as a link from the main menu for registered users only and called it 'Baseball Payments'

First, go to this topic and it shows how to install jamboreport and the virtuemart report.

http://forum.joomla.org/index.php?topic=46426.msg249905

Replace the report.php in components/com_jamboreport/reports/vm_csv3 directory with this code below, and then modify the database fields (cb_xxxxx) to match yours from CB registration fields.  Pay careful attention to the join clauses, they are what actually do the merge, you probably should not need to change this. 

The payment status field shows me as pending or completed on the report.  We take paypal or check/money order, and when the paypal IPN hits, it works well, and when we receive the check in the mail, we just go in and update the order as complete and it is reflected on this report.

The names after each 'AS' is what is displayed on the first line of the excel spreadsheet for each column, you can call them whatever you want, just don't use spaces.

Under the line
// Set Conditions for Report
you can put in whatever you want to be able to pull only certain registrations.  I have to use this because we take registrations for different sports.

Another file in the same directory, metadata.xml, which needs to be changed so the screen has the correct report name and description, in the 'name' and 'description' tags:


Name of Report
Description of Report


Here's the report.php code (and attached in a text file):


/**
* @version $Id $
* @package JamboWorks
* @subpackage JamboReport
* @copyright 2005 JamboWorks LLC.  All rights reserved.
* @license GNU/GPL
*/

/**
* Reporting class
* @package JamboWorks
* @subpackage JamboReport
*/
class vm_csv3_Report extends JamboReport {
/** @var string The report id (folder name); */
var $id = 'vm_csv3';

/**
* Runs the report
* @param array An array of system variables
*/
function run( &$vars ) {
// set the default ordering
if (empty( $vars['cb_playerlastname'] )) {
$vars['cb_playerlastname'] = 'search_term';
}

$database = &$this->getDBO();

$qb = jwDatabaseHelper::getQuery();



// SELECT FIELDS FROM COMPROFILER TABLE

$qb->select( 'r.cb_ParentFirstName AS ParentFirstName' );
$qb->select( 'r.cb_ParentLastName AS ParentLastName' );

// GET EMAIL ADDRESS FROM USER TABLE
$qb->select( 'u.email AS Email' );

// GET ORDER TYPE FROM VM ORDER PAYMENT TABLE
$qb->select( 'p.payment_method_id AS PmtMethod' );

// SELECT FIELDS FROM VM ORDERS TABLE

$qb->select( 'o.order_id AS OrderNo' );
$qb->select( "FROM_UNIXTIME(o.cdate,'%c-%d-%y') AS Orderdate" );
$qb->select( 'o.order_total AS Amount' );
$qb->select( 'o.order_status AS Status' );
$qb->select( 'o.customer_note AS OrderNote' );
$qb->from( '#__vm_orders AS o' );


$qb->select( 'r.cb_address AS Address' );
$qb->select( 'r.cb_city AS City' );
$qb->select( 'r.cb_state AS State' );
$qb->select( 'r.cb_zip AS Zip' );
$qb->select( 'r.cb_homephone AS HomePhone' );
$qb->select( 'r.cb_workphone AS WorkPhone' );
$qb->select( 'r.cb_emergencyphone AS EmerPhone' );
$qb->select( 'r.cb_playerfirstname AS PlayerFirstName' );
$qb->select( 'r.cb_playerlastname AS PlayerLastName' );
$qb->select( 'r.cb_playergender AS Gender' );
$qb->select( 'r.cb_homeschool AS HomeSchool' );
$qb->select( 'r.cb_playerschool AS School' );
$qb->select( 'r.cb_grade AS Grade_Spring_07' );
$qb->select( 'r.cb_age AS Baseball_Teeball_Age_as_of_05_01_07' );
$qb->select( 'r.cb_softball AS Softball_Age_as_of_01_01_07' );
$qb->select( 'r.cb_basebracket AS Baseball_Teeball_League' );
$qb->select( 'r.cb_leaguesoftball AS Softball_League' );
$qb->select( 'r.cb_year AS Year' );
$qb->select( 'r.cb_sport AS Sport' );
$qb->select( 'r.cb_jerseybaseball AS JerseySize' );
$qb->select( 'r.cb_pantsbaseball AS PantsShortsSize' );
$qb->select( 'r.cb_disability AS Disability' );
$qb->select( 'r.cb_birthcert AS BirthCertOnFile' );
$qb->select( 'r.cb_volunteer AS Volunteer' );
$qb->select( 'r.cb_prevouscoachteam AS PreviousTeam' );
$qb->select( 'r.cb_requestedcoach AS Coach' );
$qb->select( 'r.cb_playerbirthdate AS Birthdate' );
$qb->select( 'r.cb_sameteam AS SameTeam' );


// MERGE DATA

$qb->join( 'LEFT', '#__comprofiler AS r ON r.user_id = o.user_id' );
$qb->join( 'LEFT', '#__users AS u ON r.user_id = u.id' );
$qb->join( 'LEFT', '#__vm_order_payment AS p ON o.order_id = p.order_id' );

// Set Conditions for Report

$qb->where( "r.cb_sport = 'Baseball'" );
$qb->where( "r.cb_year = '2007'" );
$qb->where( "o.order_status != 'R'" );



// SET DEFAULT ORDER FOR REPORT

$qb->order( 'cdate' );


// DO QUERY ON DATABASE and GENERATE EXCEL CSV FILE


$query = $qb->toString();
$database->setQuery( $query, 0, 1 );
$heading = $database->loadAssocList();
//echo $query;
echo $database->getErrorMsg();

$database->setQuery( $query );
$rows = $database->loadRowList();

array_unshift( $rows, array_keys( $heading[0] ) );

$buffer = trim( arrayToCSV( $rows ) );

header( "Content-type: application/octet-stream" );
header( "Content-Length: ".strlen( $buffer ) );
header( "Content-disposition: attachment; filename=GSA_Baseball_Registrations-".date("Y-m-d").".csv" );
header( "Pragma: no-cache" );
header( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );
header( "Expires: 0" );
echo $buffer;
exit( 0 );
}
}
?>

[cgarst]

Nickolai,
I checked out your site, real nice. I noticed your registration is using Acctexp - how does that work with CB ?  I know it is a component that locks accounts after they expire, is that all it does, and do the fields get stored in CB as normal?

I thought about using it also, but all I do is after the season is done, I go in and remove all the old accounts and save my excel reports if I ever need the data, we require re-registration each sport so I don't have old data.  So far, the only time I have used old data was to import the names and email addresses into Letterman to send out a notice that baseball signups were open online.

Another good thing I saw but dont use, it is a component someone wrote called 'League Manager'.  It allows you to enter all the teams, games and scores, not sure if it is fully developed.

Something else I forgot to mention, I had a few users last fall when registering for basketball put in 2 registrations for 2 kids, then pay for 2 kids under the same registration, which really messes up the reports, and I had to go into the database and change the jos vm order table to point to the correct userid.

I had to hack quite a few files to prohibit this, and this is the solution:

The big one is to 'echo' out the player name into the product name field, so they are purchasing a registration for the correct person on the correct account.

You have to go into the flypage, all I did was use the 'manufacturer link' field to point to $my->name which replaces it. 
$product_cell = str_replace( "{manufacturer_link}", $my->name, $product_cell );

And also you have to edit browse_3.txt under the browse directory and put in the following so it puts the name on the screen:
{product_name} for {manufacturer_link}  (outputs for example: "Basketball Registration for John Doe"

Removed the 'quantity' box from browse and checkout, where you can change the amount of an item.  (only need one registration per player)

To make sure the field gets the correct 'Player' name, the first two name fields on the CB registration screen, First Name and Last Name, I changed the screen to display "Player First Name" and Player Last Name" because these are the ones that are combined into $my->name.  I had to do this because I also have the parent first and last name just below it.  To change this all you have to do is edit the english.php file under the language directory in CB component.

Chuck

[nickolai]

To answer your question (I think): The fields get stored in CB as normal regardless of active/expired status. 

I thought about using it also, but all I do is after the season is done, I go in and remove all the old accounts and save my excel reports if I ever need the data,

Hmm, for me it's a bummer I wouldn't want to remove the old accounts because that would delete their photo gallery and blog content.  Could you make it so you wouldn't have to delete there registration at the end of the season? Is it neccessary to delete it?  Could they purchase another season?

I appreciate your prompt and lengthly reply. 

-Nick

BTW: I think JamboReport solves my problem.

[cgarst]

If I could somehow make the CB trigger a process that takes you straight to the account information screen and so that any required fields be filled out, then I would not have to delete the accounts, I could just delete some of the fields that should be updated. 

I am afraid that they wouldn't go in and update their information for the next sport.  Give any opportunity for failure and it will happen. 

I thought of adding new tabs to CB so there would be a tab for each sport, with the required fields, so they could keep all the sports on one registration forever.  But when you log in, the only current way to update is to actually click on account information, and it won't let you finish until you have completed all the fields that are required.

I wonder how you could make the system trigger this process and make it streamlined, like a survey.  Another approach would be to have Virtuemart trigger it to make them enter all the info and not be able to get past the screen when they do the purchase.  There is a field that you can fill in to put the link the first screen to display after logging in, but then again they can get past the screen if they want by clicking on something else.

Chuck

[nickolai]

Chuck,

I'm trying your method and so far so good, installed virtuemart, modified the reports, and now I just need to tweak virtuemart Products a little. 

I think I will be able to get by with out clearing out registrants since it's only one sport and people usually stay on the same team.  In the reports pages I will maybe set a condition will the payment date.  Or maybe by then I will be using community builder subscription component (Late Feb.). 

Jamboreports is really cool!  Without it I don't think my league could do it!  Oh, and I couldn't have done it without you either, Chuck I appreciate you sharing your code, ideas, and insight.

I guess I have to tweak view settings the default is backend users only...  I would like to have all registered users see it.  Was that tricky to do?
Thanks again,

Nick

[nickolai]

On the report I noticed that the names are only those that made a payment (check or paypal).  What about those people that do step 1, register but never do the 2nd step, paypal or mail in a check?  Am I missing something or is there a way to have all registered users pulled up on the report (step 1 & step 2 people)?

-Nick

[dolphieb]

I am using FacileForms to allow players to register and make online payments via Paypal. I have found a module that ties into facileforms and have customized it to meet my needs.

For those of you that have tried Facileforms it can be a little time consuming building the forms but once it is in place it works well and all the data is tracked in the database.

If you would like to see how it works you can visit our soccer site at http://www.ofallonunited.com

I am currently working on trying to customize leaguemanger if anyone has some experience with this component please let me know.

Joe

[cgarst]

Nick-
Glad to hear it is working out so far.

First question you had, do you mean getting Jamboreport so registered users can run it?  I just published it by doing a new menu entry -mainmenu, new, selecting component, jamboreport, publish it to registered users only.....  Let me know if you were talking about something else.

For your second question, only way I can think of doing it easily would be to to make a copy of the report (and directory, etc. in jamboreport component) and modify it so that the VM fields are not trying to be pulled and merged into the CB fields.  Since it has a join clause if there is no payment information, then it will not show up on the report. 

Just take out or comment out these lines :

// GET ORDER TYPE FROM VM ORDER PAYMENT TABLE
$qb->select( 'p.payment_method_id AS PmtMethod' );

// SELECT FIELDS FROM ORDERS TABLE

$qb->select( 'o.order_id AS OrderNo' );
$qb->select( "FROM_UNIXTIME(o.cdate,'%c-%d-%y') AS Orderdate" );
$qb->select( 'o.order_total AS Amount' );
$qb->select( 'o.order_status AS Status' );
$qb->select( 'o.customer_note AS OrderNote' );
$qb->from( '#__vm_orders AS o' );

Remove this join :
$qb->join( 'LEFT', '#__vm_order_payment AS p ON o.order_id = p.order_id' );

This probably will do the trick....

You could probably just use both reports separately, and if you know microsoft access, import both files as 2 tables that can be used to show the difference between the 2 reports, based on the userid.  Basically it would be a query that shows only the records from the 2 tables that would be lacking the payment information...lots of ways to skin this cat.

I am not an expert at sql syntax, but if you post this report code and ask, someone might answer the question, because it may be possible to change this report to include the CB data if the order and payment fields referenced were allowed to be null, and force it to display in the report.

Thanks,
Chuck

[cgarst]

Nick -
For CB subscriptions, do you think it would be functional enough for me to do away with VM?

All I basically need anyway is a payment required for each registration.

Chuck

[nickolai]

I'm not a 100% sure about CB subscriptions but if I were you I would definitely take a good look at it.  I think you have a great solution with your current setup (except for deleting users) so if it ain't broke then don't fix it!  :laugh:


Ok, I had to change the security in  ...html/components/com_jamboreport/jamboreport.php added author, editor, and registered.

Code: Select all

// set up security
$acl->_mos_add_acl( 'com_jamboreport', 'run', 'users', 'super administrator' );
$acl->_mos_add_acl( 'com_jamboreport', 'run', 'users', 'administrator' );
$acl->_mos_add_acl( 'com_jamboreport', 'run', 'users', 'manager' );
$acl->_mos_add_acl( 'com_jamboreport', 'run', 'users', 'author' );
$acl->_mos_add_acl( 'com_jamboreport', 'run', 'users', 'editor' );
$acl->_mos_add_acl( 'com_jamboreport', 'run', 'users', 'registered' );

Random question- Email activation...  I disabled it.  Do you think that's bad?

For the second question, I'm gong to comment that section out and see what happens...

Tomorrow, I propose to the soccer board using this registration system instead of youth leagues dot com.  Last year we paid them close to $5 per registration $5,102 in total!  Normally, I think it would be a no brainer but there is polictics involved with the asheville youth soccer program.  So we will see.

Thanks for your help, Nick

[cgarst]

Hoping that maybe CB subscription will make the flow better.....the only thing I really hate with CB / VM is when they register, sometimes they don't log back in and do the checkout.  I have to nag them.  How can we get it to not let them complete the registration unless they checkout?  I tried their demo on CB subscription, but it didn't provide a workflow, since I was already registered, so I hope that when you register it takes you straight to a payment screen instead of making you log back in....any suggestions with current CB / VM flow?

I did not do email verification because I have it on another moderated site that I run and about a 3rd of them never do it.  It seemed to me this would be another thing to nag about for the slackers who don't do the checkout...if the site starts getting unwanted registrations from spambots some day I may need to use it.

Good luck with this model saving you money, it saved us money in basketball, we only had 150 registrations but it was the first time ever.  Basketball is going to be around 200 registrations I expect, and then in June we have soccer, which is our monster and will likely be around 750.

Thanks,
Chuck