Results 1 to 2 of 2
  1. #1
    CC Grandmaster Spiny Norman's Avatar
    Join Date
    Sep 2004
    Posts
    4,437

    Perl code: ACF Ratings update

    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.

    Code:
    #!/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;
    }
    “As you perhaps know, I haven't always been a Christian. I didn't go to religion to make me happy. I always knew a bottle of port would do that. If you want a religion to make you feel really comfortable, I certainly don't recommend Christianity.” -- C.S.Lewis

  2. #2
    CC Grandmaster Spiny Norman's Avatar
    Join Date
    Sep 2004
    Posts
    4,437
    Some examples of the output this program can produce:

    Code:
    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

    Code:
       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
    “As you perhaps know, I haven't always been a Christian. I didn't go to religion to make me happy. I always knew a bottle of port would do that. If you want a religion to make you feel really comfortable, I certainly don't recommend Christianity.” -- C.S.Lewis

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •