Quantcast

Replacing a Spreadsheet

Get the WebProNews Newsletter:


[ Business]

Spreadsheets can be powerful tools, and particularly so in the hands of an expert user. A spreadsheet can be used to reorganize data and to extract information not otherwise available. For example, at a client site, an application report generates a listing of hourly billing, but can’t give the cross-reference totals desired.

The raw output looks something like this.

The customer needed several breakdowns of this data, including hours by shift, and by status. The application could produce this, but only by purchasing a very expensive add-on report module. As the customer saw no other benefit to that expense, they instead loaded the report into a spreadsheet and created the needed output from that.

This was a complex procedure, but the responsible person understood how to do it, and provided the data for several years in that manner. Earlier this year, however, she left on maternity leave, and the job had to be taken over by someone else. The generation of the report itself also involved some complexity, and of course the spreadsheet manipulations were not only difficult but were ad-hoc: the owners might need one representation today (“What is the breakdown of status C for the WEST office in February”) and another tomorrow. The new employee just wasn’t up to the challenge.

I offered to provide an HTML page that could generate desired reports on demand. I did this with a Perl cgi script that is reproduced with comments here. You can get an idea of it at Billing Demo. The advantage of this is that anyone can run it – even the owners and managers who need the ad hoc queries. If new requirements come up, I can easily add them to the output.

#!/usr/bin/perl
$SPREAD_DIR="/usr/home/pcunix/www/data/";
$TITLE="Billing Breakdown";
$UNPACK="A6x1A8x1A5x1A8x1A9x2A2x7A1x6A1";
# The "A" strips ending blanks, "x" skips characters
#
$GOODLINE=60;
# Length of a "good" line
#
$MYNAME=$ENV{REQUEST_URI};
# So here, will be "/cgi-bin/billing.pl"
#

use CGI qw(:standard);
foreach $i (param) {
&nbsp # set each parameter to a matching variable
&nbsp foreach $j (param($i) ) {
&nbsp&nbsp $$i=$j;
&nbsp }
}
#
print "Content-TYPE: text/html\n\n";
chdir("$SPREAD_DIR");
# output beginning of html page
#
myhead();
# if we have all data, do the selection and produce output
#
runit() if defined $havedata;
# or if we only have the files to read, get rest of selection criteria
#
selecting() if defined $havefiles;
#
# Otherwise, here we are at the beginning
#
$size=0;
$size++ while (<*SP>);
$size %=5;
print <<EOF;
<p>
<form method=post action="$MYNAME">
<table style="margin-left: auto; margin-right: auto;" width="75%">
<tr><td><select name="file" size="$size">
EOF
$s="selected"; # just to force first selection
foreach (<*SP>) {
&nbsp print qq(<option $s value="$_">$_</option>);
&nbsp $s="";
}
#
print <<EOF;
</select></table>
<input value="Choose File" type=submit name=havefiles>
</form>
<hr></body></html>
EOF

sub myhead() {
#
print <<EOF;
<html><head><title>$TITLE</title>
</head><body>
<h2>$TITLE</h2>
EOF
#
}

sub selecting {
# This lets the user select the criteria for the report
# We build the selections from data actually available in the
# file, which makes this self-maintaining.
#
print "<h2>$file</h2>";
open(I,"<$file");
@all=<I>;close I;
$clines=@all;
print "<br>$clines total lines, ";
$lowdate="999999";$highdate="000000";
$lowdisp="";$highdisp="";
%allclients=();
%allbranches=();
%allstatus=();
$clines=0;
foreach(@all) {
&nbsp next if /^Branch/;
&nbsp next if length($_) < $GOODLINE;
&nbsp chomp;
&nbsp next if not $_;
&nbsp $clines++;
&nbsp $line=$_;
&nbsp $line =~ s/^\f\r//;
&nbsp chomp $line;
&nbsp @foo=unpack ($UNPACK,$line);
&nbsp @dt=split /\//, $foo[1];
&nbsp $t=sprintf("%6d",$foo[4]);
&nbsp $t="(BLANK)" if $t == 0;
&nbsp $allclients{$t}=$t;
&nbsp $t=uc($foo[0]);
&nbsp $t=~ s/ //g;
&nbsp $t="(BLANK)" if not $t;
&nbsp $allbranches{$t}=$t ;
&nbsp $t=uc($foo[5]);
&nbsp $t=~ s/ //g;
&nbsp $t="(BLANK)" if not $t;
&nbsp $allstatus{$t}=$t ;
&nbsp $td=sprintf("%0.2d%0.2d%0.2d",$dt[2],$dt[0],$dt[1]);
&nbsp if ($td < $lowdate) {
&nbsp&nbsp $lowdate=$td;
&nbsp&nbsp $lowdisp=$foo[1];
&nbsp }
&nbsp if ($td > $highdate) {
&nbsp&nbsp $highdate=$td;
&nbsp&nbsp $highdisp=$foo[1];
&nbsp }
}
#
$size=%allclients;$size %=5;$size++;
print <<EOF;
$clines lines read
<p>
<form method=post action="$MYNAME">
<table style="margin-left: auto; margin-right: auto;" width="75%">
<tr><td>Low date:</td><td><input type=text size=8 maxlength=8 name="slowdate" value=$lowdisp></td>
<tr><td>High date:</td><td><input type=text size=8 maxlength=8 name="shighdate" value=$highdisp></td>
<tr><td>Client(s)</td><td><select name="clients" size="$size" multiple="multiple">
EOF
#
&nbsp print qq(<option selected value="(ALL)">(ALL)</option>);
&nbsp print qq(<option value="(BLANK)">(BLANK)</option>);
foreach (sort keys %allclients) {
&nbsp next if /BLANK/;
&nbsp print qq(\n<option value="$_">$_</option>);
}

#
$size=%allbranches;$size %=5;$size++;
print <<EOF;
</select>
<tr><td>Branch</td><td><select name="branches" size="$size" multiple="multiple">
EOF
#
&nbsp print qq(<option selected value="(ALL)">(ALL)</option>);
foreach (sort keys %allbranches) {
&nbsp print qq(\n<option value="$_">$_</option>);
}
#
$size=%allstatus;$size %=5;$size++;
print <<EOF;
<tr><td>Service Type</td><td><select name="servtype" size="$size" multiple="multiple">
EOF
#
&nbsp print qq(<option selected value="(ALL)">(ALL)</option>);
foreach (sort keys %allstatus) {
&nbsp print qq(\n<option value="$_">$_</option>);
}
#
print <<EOF;
</table>
<input type=hidden name="lowdisp" value="$lowdisp">
<input type=hidden name="highdisp" value="$highdisp">
<input type=hidden name="lowdate" value="$lowdate">
<input type=hidden name="highdate" value="$highdate">
<input type=hidden name="file" value="$file">
<input type=submit name=havedata>
</form>
</body></html>
EOF
exit 0;
#
}

sub runit {
# This generates the actual output
#
@values = param( 'clients' );
@bvalues = param( 'branches' );
@svalues = param( 'servtype' );
$now=time();
print "<p>From $file, for $slowdate to $shighdate, with <p>Clients: ";
foreach(@values) {
&nbsp print "<br>$_";
}
print "<p>Branches";
foreach(@bvalues) {
&nbsp print "<br>$_";
}
print "<p>Service Type";
foreach(@svalues) {
&nbsp print "<br>$_";
}
#
#
open(I,"$file");@all=<I>;close I;
@dt=split /\//,$slowdate;
$lowdate=sprintf("%d%0.2d%0.2d",$dt[2],$dt[0],$dt[1]);
@dt=split /\//,$shighdate;
$highdate=sprintf("%d%0.2d%0.2d",$dt[2],$dt[0],$dt[1]);

$gt=0;$tct=0;
foreach (@all) {
&nbsp next if /^Branch/;
&nbsp next if length($_) < $GOODLINE;
&nbsp $line=$_;
&nbsp $line =~ s/^\f\r//;
&nbsp chomp $line;
&nbsp @foo=unpack ($UNPACK,$line);
&nbsp @dt=split /\//, $foo[1];
&nbsp $thisdate=sprintf("%d%0.2d%0.2d",$dt[2],$dt[0],$dt[1]);
&nbsp next if $thisdate < $lowdate;
&nbsp next if $thisdate > $highdate;
&nbsp foreach (@foo) {
&nbsp s/ //g;
&nbsp }
&nbsp $t=sprintf("%0.5d",$foo[4]);
&nbsp $t="(BLANK)" if not $t;
&nbsp $cmatch=0;
foreach (@values) {
&nbsp $cmatch=1 if /ALL/;
&nbsp last if $cmatch;
&nbsp $tt=$_;
&nbsp $tt="(BLANK)" if not $tt;
&nbsp $cmatch=1 if ($tt == $t);
}
next if not $cmatch;
&nbsp $cmatch=0;
foreach (@bvalues) {
&nbsp $cmatch=1 if /ALL/;
&nbsp last if $cmatch;
&nbsp $t=uc($foo[0]);
&nbsp $tt=uc($_);
&nbsp $t=~ s/ //g;
&nbsp $tt=~ s/ //g;
&nbsp $t="(BLANK)" if not $t;
&nbsp $tt="(BLANK)" if not $tt;
&nbsp&nbsp $cmatch=1 if ($tt eq $t);
}
next if not $cmatch;
&nbsp $cmatch=0;
foreach (@svalues) {
&nbsp $tt=uc($_);
&nbsp $cmatch=1 if /ALL/;
&nbsp last if $cmatch;
&nbsp $t=uc($foo[5]);
&nbsp $t=~ s/ //g;
&nbsp $tt=~ s/ //g;
&nbsp $t="(BLANK)" if not $t;
&nbsp $tt="(BLANK)" if not $tt;
&nbsp&nbsp $cmatch=1 if ($tt eq $t);
}
next if not $cmatch;
push @detail, "<tr>";
foreach (@foo) {
push @detail, "<td>$_</td>\n";
}
$tct++;
$gt+=$foo[2];
$pline=join "\|",@foo;
$cbystatus="$foo[4]|$foo[6]";
$shiftbystatus="$foo[4]|$foo[6]|$foo[7]";
$bystatus{$cbystatus}+=$foo[2];
$countbystatus{$cbystatus}++;
$countbyshift{$shiftbystatus}++;
}
&nbsp printf("<hr><p>Total records %d Hours %.2f </td>\n", $tct,$gt);
#
print <<EOF;
<hr>
<table>
EOF
#
print "<tr><td><b>Sum of Hours</b></td>";
print "<tr><td>Client</td><td>Status</td><td>Sum</td>\n";
$gt=0;
foreach (sort keys %bystatus) {
&nbsp @t=split /\|/,$_;
&nbsp printf("<tr><td>$t[0]</td><td>$t[1]</td><td align=right>%.2f</td>\n",$bystatus{$_});
&nbsp $gt+=$bystatus{$_};
}
&nbsp print "<tr><td></td><td></td><td align=right>-------</td>";
&nbsp printf("<tr><td>Total</td><td></td><td align=right>%.2f</td>\n",$gt);

print "</table><hr><table><tr><td><b>Count</b></td>\n";
&nbsp print "<tr><td>Client</td><td>Status</td><td>Count</td>\n";
$tct=0;
foreach (sort keys %countbystatus) {
&nbsp @t=split /\|/,$_;
&nbsp $tct+=$countbystatus{$_};
&nbsp print "<tr><td>$t[0]</td><td>$t[1]</td><td align=right>$countbystatus{$_}</td>\n";
}
&nbsp print "<tr><td></td><td></td><td align=right>-----</td>\n";
&nbsp printf("<tr><td>Total</td><td></td><td align=right>%d</td>\n",$tct);

print "</table><hr><table><tr><td><b>By Shift</b></td>\n";
&nbsp print "<tr><td>Shift</td><td>Client</td><td>Status</td><td>Count</td>\n";
$tct=0;
foreach (sort keys %countbyshift) {
&nbsp @t=split /\|/,$_;
&nbsp $tct+=$countbyshift{$_};
&nbsp print "<tr><td>$t[2]</td><td> $t[0]</td><td> $t[1]</td><td align=right> $countbyshift{$_}</td>\n";
}
&nbsp print "<tr><td></td><td></td><td></td><td align=right>-----</td>\n";
&nbsp printf("<tr><td>Total</td><td></td><td></td><td align=right>%d</td>\n",$tct);
#
print <<EOF;
</table>
<hr>
<p>Detail Lines
<table style="margin-left: auto; margin-right: auto;" width="75%">
EOF
foreach (@detail) {
&nbsp print;
}
print <<EOF;
</table>
<hr>
<hr>
</body></html>
EOF
exit 0;
#
}

Download Billing.pl source

*Originally published at APLawrence.com

A.P. Lawrence provides SCO Unix and Linux consulting services http://www.pcunix.com

Replacing a Spreadsheet
Comments Off
Top Rated White Papers and Resources

Comments are closed.

  • Join for Access to Our Exclusive Web Tools
  • Sidebar Top
  • Sidebar Middle
  • Sign Up For The Free Newsletter
  • Sidebar Bottom