Replacing a Spreadsheet

February 9, 2005

95

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

Related

Related Articles

Apple Unveils iPhone 16 Series and New AirPods at ‘Glowtime’ Event

The Surge of Online Ad Spending in 2024

What Are The Best Wines At Kroger?

LEAVE A REPLY Cancel reply

Latest Articles

Apple Unveils iPhone 16 Series and New AirPods at ‘Glowtime’ Event

The Surge of Online Ad Spending in 2024

What Are The Best Wines At Kroger?

Is The Carnivore Diet Heart Healthy?

What to Plant in August in Zone 7 in Rural Appalachia