Scheduling-Data

Introduction

The data for the scheduling processing is comprised of two text files, one describing events and one describing teams. Originally, these were tab delimited reports generated by the CMI admin tool. CMI Admin was replaced somewhere around 2007 with Rainier. The fields changed significantly. At this time we started migrating to comma separated values (CSV) input files. Rainier also introduced issues with 16 bit unicode support, which affects the processing of input files which were still expected in UTF-8.

Generating Data files from Rainier

There are two reports that are used to export the required input data for the scheduling systems:

• USYVL Program Custom Reports:Event List - All by Program
• USYVL Program Custom Reports:Team List - All by program

General instruction for producing reports and exporting from Rainier. (this information updated as of Spring 2010 Rainier)

• Go to "Reporting" area of Rainier. (Click on Reporting Tab in lower left pane of the Application.
• Select appropriate custom report (listed in each section below) from Reporting Pane (upper left pane).
• Enter Selection Criteria appropriate to each report (listed in each section below).
• Click "View Report" Button
• Right click in the report where its highlighted, select "Export"
• Select "CSV (comma delimited)" from Export type selection ("Select a Format" displayed in window initially)
• Click "Export" button just to the right of that field.
• When Download window appears, select "Open" option. This will open file in notepad.
• Choose "Save As ..." from "File" menu.
• Set Encoding to ANSI
• Set Desired filename
• Click "Save" button.
• Go to this page and upload the files.

Data Format

Data format specs:

• 8bit ascii
• comma separated values (CSV)
• single header line describing the fields
• Event data fields:
  • Required Fields:
    • Program Name
    • Event Name
    • Event Time
    • Event Location
  • Optional Fields:
    • ??

• Teams Data fields:
  • Required Fields:
    • Program Name
    • Team Name
    • Team Number
  • Optional Fields:
    • Coach

Automated Scheduling Data

While the generation of the reports is not overly complicated, and is documented pretty well on the upload page, automating the generation and transfer of the data has been a long running goal and would save a significant amount of time over the course of a season.

In Fall of 2011 USYVL requested that the reports used for the scheduling be automatically generated and digitally transferred. Rainier suggested using email. To that end a script was set up on the development site to receive, parse and decode the emails.

The processing of this has gone through a couple of phases. Initially an email server was setup that would accept the email and process it directly into a data file. Relatively convenient and simple, but the MTA had to accept mail only from rainier systems. Standard network security practices limit where such systems could be deployed (ie: cant set up a home server without additional steps).

Though this worked and is documented below, ultimately a different solution was settled on. A gmail account was established that emails are sent to. The client systems can then be scripted to check the emails. This is done when the IO directory is reset, the age of the file can then be established.

Client Side Email check of Google gmail account

This is accomplished with a python script that is called from the scheduling system. Need to elaborate more on that at some point.

Processing Script

The following is the script as of 2011-12-16. It will likely change a bit in the near future.

#!/bin/sh
notification=/tmp/gameschedules/notification.$$
tmpd=/tmp/gameschedules
gs=$tmpd/gs.$$
data=/usr/local/data/gameschedules
datestamp=`date +'%Y%m%d-%H%M%S'`
ds="gs-$datestamp-$$"
sampledata_dirs="/Users/aaron/Sites/sites/usyvl/scheduling/io-data/sampleInputData /var/lib/usyvl/scheduling"

do_notify(){
    # bulid a notification email
    echo "From: aaron@eri.ucsb.edu" > $notification
    echo "To: sbbeachvball@gmail.com" >> $notification
    echo "Cc: randy@usyvl.org" >> $notification
    echo "Subject: Recieved an email at gameschedules" >> $notification
    echo "" >> $notification
    echo "" >> $notification
    echo "Recieved a message at the game schedules address with the subject line:" >> $notification

    grep "^Subject" $gs >> $notification
    echo "" >> $notification
    echo "To view do the following:" >> $notification
    echo "  sudo more $gs" >> $notification
    echo "" >> $notification
    echo "mv $gs $data/$ds" >> $notification

    # send notification email
    /usr/sbin/sendmail -t < $notification
}
#################################################################
# Begin Main
#################################################################

if [ ! -d $tmpd ]; then
  mkdir -p $tmpd
fi

# collect the email contents into a file
cat > $gs

# build and send an email notification
do_notify

# do some parsing of the subject line to build a more correct datestamp
subject=`grep "^Subject:" $gs | sed -e 's/Subject: //' -e 's/was executed at //'` 
subjwhich=`echo $subject | cut -d ' ' -f1`
date=`echo $subject | cut -d ' ' -f2 | awk -F/ '{ print $3$1$2 }'`
time=`echo $subject | cut -d ' ' -f3 | tr -d ':'`
m=`echo $subject | cut -d ' ' -f4`

case $subjwhich in
   AllEventsByProgramName)   which=events;    wh=ev    ;;
   TeamListAllByProgram)     which=teams;     wh=tm    ;;  
   *)                        which=$subjwhich;wh=unk    ;;
esac

# if its PM, add 12 hours to the string
case $m in 
  PM) time=`expr $time + 120000`  ;;
esac

# format as a string of numbers 6 long
time=`printf "%06d" $time`

#echo "date: $date, time: $time, wh: $wh"

fds=$date-$time-$which
rawemail="$data/$fds.email"
if [ -f "$rawemail" ]; then
    fds=$date-$time-$which-$$
fi
rawemail="$data/$fds.email"

# copy the email contents to the data file
# this was the old school naming during development
#cp -p $gs $data/$ds
#chmod 666 $data/$ds

cp -p $gs $rawemail
chmod 666 $rawemail

base64file="$data/$fds.base64"

# try to pull out the base64 string
base64found=
blankline=0
while read line; do 
  if [ "$base64found" ]; then
      # we are only looking for a blank line
      if [ "$line" = "" ]; then
          blankline=`expr $blankline + 1`
      fi
      
      if [ $blankline -gt 1 ]; then
          base64found=
      fi
      
      echo "$line" >> $base64file
      continue
  fi
  
  # initial check to see if we should start dumping to file
  echo $line | egrep '^Content-Transfer-Encoding: base64' > /dev/null
  if [ $? -eq 0 ]; then
      base64found=yes
  fi
done < $gs

if [ -f "$base64file" ]; then
    csv="$data/$fds.csv"
    perl -MMIME::Base64 -ne 'print decode_base64($_)' < $base64file | tr -d '\0\376\377' > $csv
fi

if [ -f "$csv" ]; then
    seasonstr=`cut -d, -f1 $csv | egrep -v 'textbox' | sort | uniq | cut -d'%' -f2`
    # should really verify that there is only one season found....
    yr=`echo $seasonstr | awk '{ print $1}'`
    se=`echo $seasonstr | awk '{ print $2}'`
    case $se in 
      Spring)   sc="_A_Spring"  ;;
      Summer)   sc="_B_Summer"  ;;
      Fall)     sc="_C_Fall"    ;;
      *)        sc="_X_Unknown" ;;
    esac
    seasoncode="$yr$sc"
    seasonfile="$seasoncode-$which.csv"
    lastauto="lastauto-$wh.csv"
    for d in $sampledata_dirs; do
      if [ -d $d -a -w $d ]; then
          #cp $csv $d/$seasonfile
          cp $csv $d/$lastauto
          chmod 666 $d/$lastauto
      fi
    done
    
fi