First time install

Config file

The config files have most non-code related configs... the simple, get-it-up-and-running type of things. At a minimum you need to set: (frankly you should go thru each file in cgi-bin/rt/config/)

• DB_OWNER - mySQL username
• DB_PASS - mySQL password
• DB_HOST - DNS name of mySQL host (may be localhost)
• DB_NAME - name of the database (many isp's may name your database for you (this is not the table name)
• LOG_PATH - where the log files are stored (needs to be chmod 777)
• ROS_PATH - path to the roster support files (eg. javascript check code, user FAQ, etc)
• DOMAIN - domain name for your server (used for email from addr, links, etc)
• WM_EMAIL - addr for your webmaster
• MEM_EMAIL - addr for club Membership officer
• ROS_EMAIL - addr for roster maintainer (if that role is separate from Membership officer)
• ERR_EMAIL - addr for error reports (optional)
• email_lists - A list of the names of Yahoo groups associated with the club that you want the roster to assist with.
• $CGI_script - if you change the name of the script, change it here too so it can find itself
• Set the variables in the UPLOAD section (if you plan to allow that)

Other useful code variables

In the main script...

• $DEBUG - set to 1 for lots of debug info printed out within the normal code execution
• $OFFLINE - leaves an "offline" message up, but allows an SU person in
• $BACK_UP - to tell users when the tool will be back online
• $READ_ONLY - tool is online but will not allow users to save any edits
• $SEND_ENABLE - in case you want to disable sending of letters for debug

Importing from an external DB

Nothing is automated yet, but I will make available a script I used that takes a CSV-formatted file (export from Excel), and re-arranges the data into the proper format, and does a series of mySQL INSERTS to add the data to the database (requires shell access).

Usernames, passwords

For large clubs (or even unlucky small ones), there is no way to guarantee that names will be unique, especially as the Alumni database side of things grows. Email addrs are also sometimes shared between spouses, and are not sure to be unique. So to make things simple, RosterTool is set up to use the mysql_insertid as a guaranteed unique identifier. When a user is added, a random password is generated for them. At this point, any user can enter their email addr into the "lost password" box and their password will be mailed to them at that addr. A user may change their password in the "edit my info" section where they can edit their other info. No checking is done on passwords for digits, upper/lower case, etc. Only that the password is non-null.

Permissions

Permissions are done in a bit-mapped fashion, with each bit in the value representing a different permission type. There is no ordering of the permissions, per se, no inheritance that the next highest permission can do everything that a lower permission can do. Each bit/permission is either on or off, and you get the abilities associated with that permission.

Permissions are retrieved from the DB with each form submission, so no spoofing should be possible.

Only the SuperUser acct can change permissions for anyone. By default all new members are given no special permissions.

Anyone in the roster will be give OWNER permissions when looking at their own info.

The "status", "ltm", and "jci" fields are used to ADJUST permissions on the fly. If a member has ACTIVE status (or the special JCI/LTM fields are a Y) then they are given an ACTIVE level of permissions, which allows them to do full searches of other similar people. Without this, they are only able to see their own info.

cron jobs

Several cron jobs are available to systems that support such things (Linux, Mac OS X, ie. essentially any flavor of unix).

• roster backup - a mySQL dump is executed, gzipped, stored locally, and then mailed to the WEB_EMAIL and ROS_EMAIL for "off-site" backup. These files are typically pretty small. Suggested interval once/week. No automated restore is in place yet, but can be done with shell access by uploading the file, un-gzipping it, and executing the commands

  mysqladmin -u mySQL_username -p mySQL_password drop roster
  mysql mySQL_username < backup-file.sql

• info_check - the roster is set up to periodically email a subset of the members with a form letter giving them a dump #ALL_INFO# of their info, and asking them to correct things that are out of date. One such usage would be to send out this info once/month, sending it to the subset that will renew 4 months in the future, so that their information will be more likely to be up to date for renewal purposes. Over 12 months all members should thus be contacted.
• Integrity check - this can be run command-line, or by cron, and is the same as the "integrity check" button on the Admin Panel. If not run from the web interface, the report will be mailed to the addr specified in the $INTEGRITY_REPORT_EMAIL field in the config file. As a cron job, it may be useful to run this 1-2 weeks into every month, as a way to catch when renewals/drops were forgotten at the end of the previous month.

Limitations

There are some features that certainly would be nice, but I have not done yet, and may/not have any firm plans to do so

• A template for the user info form. The layout is an admitted hack, but is a first attempt to get away from a fully hard-coded table of html code. Work on this issue is probable.
• Web-based/GUI mySQL field editing - for right now the way to do this is with the mySQL query box on the admin panel. As SU you can issue commands such as

  ALTER TABLE roster CHANGE ejaycall enewsletter enum('Y','N') 

  to rename a field, or

  ALTER TABLE roster ADD COLUMN info_after_quit  VARCHAR(1) AFTER board
  UPDATE roster SET info_after_quit ='N'

  to add a new field and set a value for everyone. Remember to set the LIMIT box to infinity.
• A better way to associate UIDs and names... ie for the Buddy person to be able to type a UID in to the buddy field, and not a name. Its not that they could not enter a number, but there is not yet a slick way to look up names/uid associations (ideas anyone?). I certainly don't want the name/uid database floating around in the page source as javascript.
• Have form errors set focus to the offending form field... yes this should be simple, yet every attempt has eluded success so far (I am NOT a fan of javascript)

Admin panel

The Admin Panel will be visible in some subset only to members with special privileges.

Newsletter editor

The newsletter editor is presented with a box listing the various State level officers. They may edit the contact info for these people, the intention being to be able to send them our newsletter, either electronically, or by US mail. Any user that has only the State status type will see things as an inActive member would, ie. they will only be able to access their own information.

If a STATE officer is also a local member, the easiest way to deal with this is to have duplicate records for them. IMO, its best to leave the ~6 state level records as placeholders... just changing the contact info, but thinking of the "member" as "State President", who _this_ year happens to be so and so. To avoid sending the local person 2 newsletters, let them control their main acct as they'd like, and set the placeholder STATE acct addr to be 'DO NOT MAIL', zip code 99999. When the lables are printed, it will be easy to toss that one out.

The newsletter editor, by default, is able to download a list of birthday information for publishing a "birthdays this month" column in the newsletter.

As of version 1.67, the Newsletter editor will also get an "edit by name" ability, and will be able to set the eNewsletter option for any member, but for non-State officers, that is the only piece of info they can edit, and tehy will not be able to view other private info about that member.

Buddy menu

To help acclimate new members, we have a "buddy" program where a veteran member is matched to a new member to help introduce them around, and get them out to some events.

The buddy chairperson is able to view/edit the "buddy" field for all members, and may search for newly added members to know who needs a buddy.

Adding users

This requires ADMIN or SU permissions.

Click on the "Create new member" button. A (mostly) blank user info form is presented where you can fill in the pertinent user information. Note that some fields are required (this is configurable in the code) and will cause an error until they are saved.

Form info validation

A fair amount of form validation is done with javascript, followed up by a bit in the perl script itself. ie. zip codes are checked="checked" to be 5 numbers, phone #'s are reformatted to ###-###-####x##### (x##### is an optional 5 digit extension), etc. Checks are only performed on items that the editting person has the ability to change... ie. members (by default) cannot change some identifying information such as birthdate... so when they are doing the edit no check will be performed on that piece of information. But when an Admin (or SU) does the edit (or is doing the create), a check is performed.

Action after creating

The default for this option is configurable in the config file.

• create: uses this if you will be adding members in batches... as each form is submitted you will automatically go to a new blank form.
• group_welcome: change this as you add the last person... the script will then do a search for all members that joined this month and were updated today... not a perfect match, but keeping the creation date seemed a waste.
• letter: takes you to the welcome letter for just this last person... if you want to send personalized letters
• nothing: takes none of these actions.

Note: depending upon the flow you intend to use, these options overlap with the checkboxes for sending a welcome letter automatically. As our process evolved, we established a generic welcome letter we liked (including a few replacement fields, ie. #FIRST# #LAST#, set the flow to go to a new create form every time, and checked="checked" the box to send a welcome letter, and send Yahoo group invites as each new person was added.

Renewals

Our renewals are based on 12 months from when you join, so we have renewals every month (except December... don't ask). You can select renewals for any month/year and you will get a list of who will be up for renewal. If you do this several months in advance you can send early emails (using the "letters"), or generate mailing labels, etc.

As renewal info comes in, pull up the month in question and use the pull-down menus for each person.

For "drops", change their status to some flavor of Inactive.

For renewals, change the "renewal action" to "renew". Their "ry" (renewal year) will then be incremented by one year.

Action will only be taken on the people for which one of the 2 above things is done. Anyone left as "Active, No Action", will stay on the list as needing to renew in that month. So you can do "partials" as you get the information, instead of waiting til all the information is in.

You can also send an "exit" letter to those who "drop". The idea here is that someone people may misplace their renewal info, forget about it, or sometimes their US Mail addr is outdated, and this email will catch them instead.

You can also send any other letters (such an early "hey your renewal is coming up", with the "Preview Email to Group" button.

Generic mySQL query

Any string in this box is passed as a generic query to mySQL. Only someone with SU privs can do something beyond a select.

For non-select operations (by an SU), remember to adjust the LIMIT pull-down, or your operation may only affect a subset, or cause an error. As an example, when I add want to add a new field to the table, I will type in something like

ALTER TABLE roster ADD COLUMN anncpref VARCHAR(1) AFTER im

A more general use of this query box might be to do a large-scale data manipulation... ie something like

UPDATE roster SET ry=2004 WHERE rm=10

Letters

RosterTool makes use of several "letters" for mail-merge types of emails, ie. a welcoming letter to let a new member know that their info is in the roster, and thus they can start to access it. Other already-envisioned uses

• "Exit" letter when a member quits.. may catch someone that did not mean to quit
• "Renewal" letter... an admin could search for renewals that are due several months out, and send a letter to that group.
• "Regional" letter... for a group spread out over a large area, you could send letters to all people in a regional sub-search.

Validate addrs on Yahoo groups (or any mailing list)

We have several Yahoo groups with our club. We would like to restrict some of those to people that are active members only. Since there is not a force-add (that I know of) by email (for remote operation) for Yahoo we cannot automatically keep our Yahoo list membership in sync with who is/not a member (would happily take suggestions here).

To use this feature, log in to your yahoo list. Click on "Members". Click on 'Download List'. Copy and paste that list into the textarea box. Hit the "Check these addrs" button. You will get a report of the addrs that you pasted in that do not match those of the list of Active members. At your option you can then remove those members at Yahoo. This function prints a summary list of unknown addrs at the bottom, which can be pasted into the Yahoo form to remove a whole group of members at one time (vs. setting each person to "unsubscribe").

In 1.70, added the reverse feature which will allow you to paste in the dump from Yahoo of who is subscribed and tell you who in the club is NOT on the list.

Roster integrity check

This function attempts a "sanity check" of all roster information. It looks for

• Members out of the age range (the Jaycees is for people 21-40)
• Members that appear to be past due for renewal
• Other missing info (bday info, 4 digit zip codes, etc)

No actions are taken directly. It is up to the administrator to act on the information in the report.

Edit by name

Enter partial first and last names (or use a % as the mySQL wildcard). If more than one match is found, they will be printed in a table with an "Edit" button next to the name. If only one match is found, you will be taken directly to the Edit Info form for that person.

Edit by UID

Enter a user ID #, you will be taken directly to the Edit Info form for that person.

Form letter fields

You can use any field as a mail-merge type field, but entering it in the letter as #FIELD_name#.

Eg. if the letter text contains "Hello #FIRST#", then when it is sent, it will be sent as "Hello Bob," "Hello Sue,", etc.

Note, the pwd, and perm field are removed for security reasons.

Club announcement

We have established a "direct" email list (of sorts) for what are considered "critical" communications, such as a meeting being canceled at the last minute, etc. Only members with the Annc permission can send such a message. These messages will be sent directly (vs over a Yahoo group which might have delays, and does not reach as much of our membership) to each members using their primary or alternate addr.

Members may exclude themselves from receiving these by checking the box in their info to not receive such anncs.

A list of the Active members that are not reached by this (because they checked="checked" the box, or do not have an email on file) is generated so that phone calls, or mailing labels can be generated (as needed).

File uploads

Several upload options exist

• Newsletter - for the newsletter editor
• CPGs - this allows VPs to upload CPGs in to the CPG library (world accessible)
• File Archives - will allow any member to upload a file into these archives which are accessible only by other members
• Roster Photo - allows members to upload a photo of themselves for display

Post processing can be specified for each type of upload. Included types are

• zip - will zip the file and discard the unzipped version
• image - will use Imagemagick convert (path must be specified in config) to adjust picture to small size for display and discard original.

Roster downloads

We offer several types of downloads from the roster

• Roster - a general listing of all Active members (plus jci/ltm), giving name, addr, employer, and contact info. Available only to Active members (and jci/ltm).
• Board roster - a list of the current Board members, with contact info. So that the Board does not need to maintain a separate roster.
• Bday - a list of month/day birthday info that is available only to the Newsletter Editor so that we can publish "happy birthday this month" info in our newsletter.
• Newsletter labels - again, available to the Newsletter Editor. We encourage our members to receive the newsletter electronically. For those that do not, this generates mailing labels the subset of members that want a copy mailed to them (US Mail). Labels are sorted by zip to help speed up bulk mailing sorting.
• VCF - Vcard format - right now under test and only available to SU privs. A v-card export of the roster data. Should be import-able to Palm devices and many Address Book applications.

Permissions for who can access each type of file are available in the config file.

The send_me subroutine sends out the files. Adding/removing files currently will require modifying the code, altho you could change the permissions in the config file to effectively remove any of these files.

File archives

Files uploaded to the Archives can be downloaded by logged in members. The download.cgi script is used to hide the actual URL. HTTP_REFERER is checked to be sure the download request is generated by the local site, implying that the user has logged in and been authenticated.

Photos

Members may upload a photo, which will be scaled (default is 150x150) to save size and be uniform in display. An option will be added to allow members to turn off display of the picture, and/or delete it.

Data structure

The fields are defined in the code using a hash of predefined prefixes with the field names added on as shown

name_	String you want the public to see describing this field
edit_perms_	Which privileges will be able to edit this field
view_perms_	Which privileges will be able to view this field
type_	HTML form input type (defined so far; TEXT, select_A, PASSWORD, PERM, textarea
onchange_	javascript call (if defined) when field is changed in the form
chkstr_	javascript call when form is submitted
init_	initial value for new member creation
required_	flag to denote required fields (gets a * in the form)
default_checked="checked"_	flag to determine which fields have their boxes checked="checked" by default in the search form
dispsize_	used for size field in HTML form
max_	used for MAXLENGth field in HTML form. Calculated on-the-fly from TABLE column definitions.
options_	Points to an array of OPTIONS for a select list
form_fmt_	Various formatting options for the edit info form. Defined in comments in the code
form_colspan	Used for formatting the table
form_tr	Used to start a new table row in the form
form_text	Used as the Field name when a MULTIPLE type is specified in form_fmt instead of the name_

Ikonboard integration

Ikonboard is a well-known "forum" package.

A loose integration with Ikonboard is now in place. When a new user is created in RosterTool, that same user is created in Ikonboard (optionally.. see the config file) with the same username, password, location, and real name. These values are also updated to the IKB member_profiles DB when they are updated in RT.

Ikonboard has several minimum length checks for username, password that are not in RT. As such you can either add them to RT, or remove them from RT. They are easy to remove in IKB, in the files Register.pm, and Session.pm, search for the word "short". There are 3 places that need need to be modified. I also removed the IP checks in Register.pm, and modified it so that the ONLY place it would accept registrations was from the IP of our webhost.

During the IKB update function, Active status will be checked="checked", and the member dropped from IKB if their status in the RT is dropped.

And while it could be argued this will remove some the "extras" from Ikonboard, I plan to remove most of the User Control Panel, with the intention being that users will not try to modify their info anywhere except in the RosterTool. This will allow them to keep the same username/password for both.

Command line access... use with other scripts

As an experiment to integrate with Gallery, I added a command-line access that would return an approximation of a php "serialized" data-stream of a a user record.

format:  ./ros2.cgi serialize 23 

Error reports

This is not sophisticated (yet), but when an error occurs (mostly for now a mySQL error, or an unknown action), it will be logged to the error_log, and optional an email can be sent to ERR_EMAIL. Also put in a provision to EXIT_ON_ERROR, which I recommend for now, until I'm sure it will be graceful about continuing (which I think is true, but).

Navigation menu (user area)

Send me...

See the "File downloads" section above.

User query form

Members can construct their own "custom" roster, using a somewhat "busy" form to select what columns they want to see about each person, and determining a search criteria (such as only people that live in a certain city).

Quick find directions, phone #, etc

Mostly a "social" feature.. a quick way to find a phone #, or email for someone else, rather than downloading the full roster.

Interest search

As a social organization (as well as civic), our members tend to meet lots of friends. We also tend to get a lot of new members that are also new to the area, so this helps them find some people that may have similar hobbies and interests.

Email list maintenance

Its not a direct interface with Yahoo, but this will generate mails to the Yahoo email-interface, allowing members to subscribe, unsubscribe, and change their delivery-mode options. This allows a lot of members to belong to our lists, without having to get a real Yahoo ID, etc, as well as making things easy for those not very technically-inclined.

CPG question #3 info

Not sure this will have any application outside the Jaycees, but when you run a project in our Chapter, you must generate a "plan" detailing what you want the Board to approve (it sounds a lot more formal than it is). Part of this plan is to include the contact info for the Chapter officers that will be involved in the project. This "button" saves you from having to type that in yourself. (and as a side note, these documents are great b/c when they are done, they can serve as a guide to anyone in the future that wants to know how to run a similar project).

Awards

The Awards section is new as of 1.70, and is less than mature in development. There was a desire to archive the awards given to club members on a month/tri-mester/year basis. Also to track awards given to the local organization by the District/Region/State/National level. These awards could be on an individual basis, or for a Project that was run in the chapter.

Currently the awards entry is fairly robust, tho certainly tailored to Jaycee awards (which comprises a rather complicated variety of categories). The search/display of awards is less developed, currently only allowing the display of awards for a certain year, with rudimentary sorting on the "period" column.

I believe the backend database should be stable in its structure, so the entry of awards should be safe. The querying of this stored data is likely to see further development, but that would not cause any loss of effort for award data already entered.