PDA

View Full Version : Perl code: ACF Ratings update



Spiny Norman
17-10-2009, 03:56 PM
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
17-10-2009, 03:59 PM
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