PDA

View Full Version : Club rating list application



Spiny Norman
01-03-2006, 08:14 AM
I am interested in acquiring or developing an application which would automatically extract from the ACF Ratings Master File a list of our club members and their ratings that could be published in real time on our website. Fairly simple really ... a config file with the IDs of the club members, then read through the master file and get their ratings. To update the list, just update the latest master file to the website and voila.

Have anyone built this before? If so, would you be prepared to provide me with a copy? If not, I will build one ... but I would be interested to know whether people have preferences with regard to the build tools. I would probably prefer to develop it in ASP or ASP.NET, but can do Perl at a pinch. Would anyone else be interested in it?

Observation: Maybe PAX could add this as a feature to SP2HTML ... so that a config file would have the list of club members, then these could be flagged somehow in the HTML output for the website?

ursogr8
01-03-2006, 12:03 PM
I am interested in acquiring or developing an application which would automatically extract from the ACF Ratings Master File a list of our club members and their ratings that could be published in real time on our website. Fairly simple really ... a config file with the IDs of the club members, then read through the master file and get their ratings. To update the list, just update the latest master file to the website and voila.

Have anyone built this before? If so, would you be prepared to provide me with a copy? If not, I will build one ... but I would be interested to know whether people have preferences with regard to the build tools. I would probably prefer to develop it in ASP or ASP.NET, but can do Perl at a pinch. Would anyone else be interested in it?

Observation: Maybe PAX could add this as a feature to SP2HTML ... so that a config file would have the list of club members, then these could be flagged somehow in the HTML output for the website?


hi Steve

Count me as a customer if you build one. (Although, your post is full of dirty techo. talk; so I am taking it on face value ;) ).

regards
T

Spiny Norman
01-03-2006, 03:01 PM
OK, here's the first beta. It reads data from the ACF Master File to produce a pre-determined list of players, sorted in reverse rating order that looks like this:


Rating Member..................
2306!! (IM) SANDLER, Leonid
2260? (FM) SARFATI, Jonathan
2142 WONG, Ngiam Yee
1710! WALLER, Tony
1469! SMITH, Mike
1437! FROST, Jared
1434! FROST, Stephen
1307!! KLOPROGGE, Ion


Its quick-n-dirty and written in Perl. For those on Windows, Google for "ActivePerl" and you can download the Windows version of Perl. Then just do the following from the command prompt:

perl My_Script_Name.pl

and it will produce "OutputFile.txt" in your C:\Temp directory. You will see, below, where this (and the name of the ACF Master File) has been crudely hardcoded into the script. If anyone wants to bother cleaning it up so it uses variable names, please go ahead ... just post the revised version here.

You will also see below that the ACF IDs of your club members need to be put into the script along with their name. I expect it would be trivial to mod the script so it gets their name from the ACF file, and it would also be trivial to read the IDs in from another file I suppose ... but Croydon doesn't have that many members so we can maintain it manually.

Right, here's the code for your My_Script_Name.pl (which I called ExtractClubRatings.pl):


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

&InitialiseVariables();
&MainProcessing();
&EndProcessing();
exit;


sub InitialiseVariables {
$| = 1;
$ACFMasterFile = 'C:\Temp\ACFMasterFile.txt';
$OutputFile = 'C:\Temp\OutputFile.txt';

%ClubMembersList = (
3121982 => 'FROST, Jared',
3074874 => 'FROST, Stephen',
3099131 => 'KLOPROGGE, Ion',
3001890 => '(IM) SANDLER, Leonid',
4000932 => '(FM) SARFATI, Jonathan',
3106511 => 'SMITH, Mike',
3087065 => 'WALLER, Tony',
3096795 => 'WONG, Ngiam Yee'
);

}


sub MainProcessing {
print "\n";
print "ACF Master File : $ACFMasterFile\n";
print "Output File : $OutputFile\n";
print "\n";
print "Extracting latest ratings for club members ...\n";
print "\n";

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

while (<MASTERFILE>) {
($id, $rating) = split;
while (length($rating) < 6) {
$rating = "$rating ";
}
if (length($ClubMembersList{$id}) > 0) {
$ClubMembersList{$id} = "$rating $ClubMembersList{$id}";
}
}

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

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

close(MASTERFILE);
close(OUTPUTFILE);
}


sub EndProcessing {
}


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


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

pballard
01-03-2006, 03:22 PM
Then executed as a CGI, I assume?

p.s.
Q: How do you obfuscate Python code?
A: rewrite it in Perl :)

Spiny Norman
01-03-2006, 03:52 PM
Yes, it could be modified to run as a CGI app "on demand" on a website ... if that was wanted then we could have a switch which determines whether it outputs to the screen (i.e. HTML) or to a text file (as at present).

I had a bit more of a play with this and came up with the following code example which reads from Croydon's membership database (an Excel spreadsheet). In that spreadsheet we keep track of people's names, their ACF IDs, and so on ... so its probably the ideal data source for producing this rating list.


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

$Win32::OLE::Warn = 3; # die on errors...

# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');

# open Excel file
my $Book = $Excel->Workbooks->Open("Z:/General/Croydon Chess/Databases/Membership/Membership Database - Croydon Chess.xls");

# select which worksheet we want
my $Sheet = $Book->Worksheets('Members Register');

foreach my $row (2..100) {
# skip empty cells
next unless defined $Sheet->Cells($row,7)->{'Value'};
# print out the contents of the cell
$id = $Sheet->Cells($row,7)->{'Value'};
printf "The member's ACF ID is $id\n";
}

# clean up after ourselves
$Book->Close;

So my next question is this: What do you all use to track memberships for your chess clubs? Excel spreadsheets, or something else? If Excel, do you all store the member's ACF ID in one of the columns?

ursogr8
01-03-2006, 03:59 PM
<snip>

So my next question is this: What do you all use to track memberships for your chess clubs? Excel spreadsheets, or something else? If Excel, do you all store the member's ACF ID in one of the columns?


Excel.....yes
Store ACF IDs...SP file...not EXCEL.

Spiny Norman
01-03-2006, 04:02 PM
Excel.....yes
Store ACF IDs...SP file...not EXCEL.
I doubt I would be able to get it to read ACF IDs from Swiss Perfect files without some assistance. Pax, are you out there ... can you advise anything re: format of SP files? If they are a DBF or similar then maybe I can find a perl module that will let me read them ...

Spiny Norman
01-03-2006, 04:04 PM
I doubt I would be able to get it to read ACF IDs from Swiss Perfect files without some assistance. Pax, are you out there ... can you advise anything re: format of SP files? If they are a DBF or similar then maybe I can find a perl module that will let me read them ...
The corollary to this: If we can read the data, we can probably write it back again ... e.g. if the Excel membership database contains a column for ratings, then we can write the new rating back into the spreadsheet. Ditto for SP if we can get access to the SP datafile, we could use the script to auto-update the SP file with the latest ACF ratings instead of doing it by hand.

Spiny Norman
01-03-2006, 06:32 PM
Here's beta version 0.2 ... I have put in proper code commenting ... have also modified the application so that it can either:

1) use a hard-coded list of ACF IDs; or
2) read in a list from an Excel spreadsheet (you have to nominate pathname, worksheet name, starting/ending row and a column number that contains the ACF ID codes).

It also now gets the player name information from the ACF Master File rather than from the hard-coded list. I have tested it out with Croydon's membership database and it takes about 5 seconds to process/produce the output file for us (this manual process used to take me around 1/2 an hour every quarter).

If someone can show me how to read Swiss Perfect files I would be happy to modify further and enable it to read codes from there too.


#!/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\ACFMasterFile.txt';

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

$OutputFile = 'C:\Temp\OutputFile.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{'1000002'} = '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';
$ExcelColumn = 7;
$ExcelStartRow = 2;
$ExcelEndRow = 100;
}
}


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,$ExcelColumn)->{'Value'};

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

# Store in the ClubMemberList array
$ClubMembersList{$ACFID} = ' ';
}

# Close the spreadsheet object
$ExcelBook->Close;
}

#
# 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');

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) {

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

# Update the array with the desired values
$ClubMembersList{$id} = "$rating $lastname $firstname1 $firstname2";
}
}

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

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

close(MASTERFILE);
close(OUTPUTFILE);
}


sub EndProcessing {
}


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


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

pax
03-03-2006, 07:16 AM
I doubt I would be able to get it to read ACF IDs from Swiss Perfect files without some assistance. Pax, are you out there ... can you advise anything re: format of SP files? If they are a DBF or similar then maybe I can find a perl module that will let me read them ...

SP data files are DBaseIII files. I use a C++ library called CDBFile to read them.

auriga
11-03-2006, 11:57 AM
SP data files are DBaseIII files. I use a C++ library called CDBFile to read them.

another approach is to import/link into another database
(eg. access, mysql, etc.)

or you could use odbc/jdbc driver for the dbase III file direct
or c++ library as pax does above.

dbf is relatively old database format though
(think it has 8.3 DOS limitation and doesn't like long filenames) :(