Perl code: ACF Ratings update

[Spiny Norman]

Its a while since I posted this bit of Perl code on the board. I recently had cause to make some enhancements. This code:

-- extracts member ratings from the ACF master file;
-- updates an Excel spreadsheet (our club's membership database);
-- writes out a text file of the club member ratings; and
-- writes out a text file of the changes to member ratings (since last update)

I hope it may be useful for someone. I use ActivePerl (runs under Windows) to run it.

#!/usr/bin/perl
#
# Name        : ExtractClubRatings.pl
# Author      : Stephen Frost
# Date Written: March 2006
# Description : Reads ACF Ratings Master List and extracts club ratings
#

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$| = 1;

%ClubMembersList = ();

&SetUserDefinedVariables();
&DoMainProcessing();
&EndProcessing();

exit;


sub SetUserDefinedVariables {
   #
   #  FEEL FREE TO CHANGE THE VALUE OF ANY VARIABLES NOMINATED
   #  IN THIS SUBROUTINE, BUT DON'T TOUCH ANYTHING ELSE IN THE CODE!
   #

   #
   #  Set the pathname of the ACF Master File you wish to process
   #

   $ACFMasterFile   = 'C:\Temp\Chess Club\ACF Master File.txt';

   #
   #  Set the pathname of the text file you wish to produce
   #

   $OutputFile      = 'C:\Temp\Chess Club\Croydon Chess - Classic Ratings List.txt';

   #
   #  Set the pathname of the rating changes file you wish to produce
   #

   $ChangeFile      = 'C:\Temp\Chess Club\Croydon Chess - Changed Ratings.txt';

   #
   #  Decide which form of INPUT you wish to take.  Currently your choices are:
   #
   #  InputOption = 'HardCoded' ... means we use values from ClubMembersList defined below
   #  InputOption = 'ExcelList' ... means we use values from MembershipSpreadsheet defined below
   #

   $InputOption = 'ExcelList';

   #
   #  If you selected: InputOption = 'HardCoded' set up the next variable
   #
   #  You wish to work from a hard-coded list of ACF IDs, so you can
   #  add as many entries as you like to the following associative array.
   #
   #  The format of this array is as follows:
   #    - key   = ACF numeric ID, 7 digits long
   #    - value = Player name (for your reference only, not actually used!)
   #

   if ($InputOption eq 'HardCoded') {

      $ClubMembersList{'1000001'} = 'LASTNAME, Firstname';
      $ClubMembersList{'9999999'} = 'PLAYER, Another';
   }

   #
   #  If you selected: InputOption = 'ExcelList' set up the next few variables
   #
   #  You wish to work from a list of ACF IDs sourced from an Excel spreadsheet.
   #  Set the pathname of the Excel spreadsheet, the name of the worksheet, the
   #  number of the column containing the ACF IDs (A=1, B=2, etc), plus both the
   #  starting and ending row numbers to search through
   #

   if ($InputOption eq 'ExcelList') {

      $ExcelPathname     = "Z:/General/Croydon Chess/Databases/Membership/Membership Database - Croydon Chess.xls";
      $ExcelWorksheet    = 'Members Register';
      $ExcelACFIDColumn  = 7;
      $ExcelACFIDColumn  = 7;
      $ExcelStartRow     = 2;
      $ExcelEndRow       = 100;
      $ExcelRatingColumn = "H";
   }

   #
   #  Decide whether you want the rating "confidence level" codes
   #  displayed in your list (i.e. ?, ??, ! and !! codes)
   #  - set this variable to either "Yes" or "No"
   #  

   $ConfidenceCodes = "No"

}


sub DoMainProcessing {
   print "\n";
   print "Extracting latest ratings for club members ...\n";
   print "\n";

   if ($InputOption eq 'ExcelList') {

      # "Die" if errors encountered
      $Win32::OLE::Warn = 3;

      #  Create an Excel object (use an existing one if possible)
      $ExcelObject = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');

      #  Open the spreadsheet  
      $ExcelBook  = $ExcelObject->Workbooks->Open($ExcelPathname);

      #  Select the desired worksheet
      $ExcelSheet = $ExcelBook->Worksheets($ExcelWorksheet);

      #  Loop through the desired number of rows in the spreadsheet
      foreach $ExcelRow ($ExcelStartRow..$ExcelEndRow) {

         #  Skip empty cells
         next unless defined $ExcelSheet->Cells($ExcelRow,$ExcelACFIDColumn)->{'Value'};

         #  Get the ACD ID from the desired column
         $ACFID = $ExcelSheet->Cells($ExcelRow,$ExcelACFIDColumn)->{'Value'};

         #  Store the row number in the ClubMemberList array
         $ClubMembersList{$ACFID} = "$ExcelRow";
      }
   }

   #
   #  OK, at this point $ClubMembersList should loaded with all the ACF IDs
   #  relevant to the club (either hard-coded, or loaded from a spreadsheet)
   #

   open(MASTERFILE, "$ACFMasterFile") || &SystemError('Cannot open ACF Master File');
   open(OUTPUTFILE, ">$OutputFile")   || &SystemError('Cannot open Output File');
   open(CHANGEFILE, ">$ChangeFile")   || &SystemError('Cannot open Change File');

   while (<MASTERFILE>) {

      ($id, $rating, $state, $lastname, $firstname1, $firstname2) = split;

      #  Check to see if the ID matches to our list of club members
      if (length($ClubMembersList{$id}) > 0) {

         #  Got one ... check whether "confidence codes" are needed
         if ($ConfidenceCodes eq 'No') {
            $rating =~ s/\?//g;
            $rating =~ s/\!//g;
         }

         if ($InputOption eq 'ExcelList') {

            #  Update the spreadsheet with the new rating
            my $cell = "$ExcelRatingColumn$ClubMembersList{$id}";
            my $oldrating = $ExcelSheet->Range("$cell:$cell")->{Value};
            my $newrating = $rating;
            if ($newrating eq $oldrating) {
               print "No update for $id in cell $cell (identical rating of $newrating)\n";
            } else {
               print "---> updating $id in cell $cell with rating $newrating\n";
               $ExcelSheet->Range("$cell:$cell")->{Value} = $newrating;
               while (length($oldrating) < 6) {
                  $oldrating = " $oldrating";
               }
               while (length($newrating) < 6) {
                  $newrating = " $newrating";
               }
               print CHANGEFILE "$oldrating --> $newrating  $lastname $firstname1 $firstname2\n";
            }
         }

         #  Make the rating field a fixed-width for display purposes
         while (length($rating) < 6) {
            $rating = " $rating";
         }

         #  Format the output the way you want it
         $formatted = "$rating  $lastname $firstname1 $firstname2";

         while (length($formatted) < 33) {
            $formatted = "$formatted ";
         }

         $formatted = "$formatted  $id";

         #  Update the array with the desired values
         $ClubMembersList{$id} = $formatted;
      }
   }

   print OUTPUTFILE "Rating  Member...................  ACF....\n";

   foreach $id (sort SortByDescendingValue keys(%ClubMembersList)) {
      print OUTPUTFILE "$ClubMembersList{$id}\n";
   }

   if ($InputOption eq 'ExcelList') {
      #  Close the spreadsheet object
      $ExcelBook->Close;
   }

   close(MASTERFILE);
   close(OUTPUTFILE);
   close(CHANGEFILE);
}


sub EndProcessing {
}


sub SortByDescendingValue {
   $ClubMembersList{$b} <=> $ClubMembersList{$a};
}


sub SystemError {
   local($errormessage) = $_[0];
   print "Processing error: $errormessage ($!)\n";
   exit;
}

[Spiny Norman]

Some examples of the output this program can produce:

Rating  Member...................  ACF....
  2310  West, Guy                  3087441
  2192  Teo, Kok-Siong             3132213
  2149  Szuveges, Grant J          3095071
  2051  Wong, Ngiam Yee            3096795
  1815  Stones, Douglas            3121654
  1808  Birchall, Ian              3071315
  1619  Chong, Reginald            3133941
  1561  Frost, Stephen             3074874
  1454  Cross, Jamie               3131756
  1366  Yung, Jamie                3124001
  1336  Goldsmith, Richard         3125832
  1335  Simpson, Bruce             3103442
  1286  Watson, Jean               2409494
  1219  Kloprogge, Ion             3099131
  1131  Wills, Steven L            4151565
  1116  Lyons, Linden              3122600
  1114  Matthews, Rodney           3135761
  1107  Gibson, Kyle               3122423
  1100  Jaske, Jan                 3124955
  1085  Young, Jack                2409046
   818  Fleming, John M            3135750
   768  Yung, Pearl                3133613
   735  Stuyt, George              3125670
   325  Yung, Cameron              3125445
    2g  Xu, Chelsea                3132434
    1g  Jackson, Pearce            3125250
   New  Wu, Alan                   3132423
   Unr  Fink, Egon                 3135831
   Unr  Boyd, Halen                3133134
   Unr  Jayasekera, Arun           3132364
   New  Knur, Dylan                3127825
   Unr  McLean, Bryce              3136122
   New  Clare, Benjamin            3132305
   New  Liu, Richard               3133683

   Old        New
Rating     Rating  Name................
  1838 -->   1808  Birchall, Ian 
  1605 -->   1619  Chong, Reginald 
   793 -->    818  Fleming, John M
  1222 -->   1107  Gibson, Kyle 
  1311 -->   1336  Goldsmith, Richard 
    6g -->     1g  Jackson, Pearce 
  1286 -->   1219  Kloprogge, Ion 
  1202 -->   1116  Lyons, Linden 
  1116 -->   1114  Matthews, Rodney 
  1246 -->   1335  Simpson, Bruce 
   485 -->    735  Stuyt, George 
  1311 -->   1286  Watson, Jean 
  2292 -->   2310  West, Guy 
   New -->     2g  Xu, Chelsea 
    5g -->    325  Yung, Cameron