Writing a Front End for Claims_DB Part 5 - gen_report.sh

This content was originally published to Benscomputer.no-ip.org


Tutorial Part 4 - add_process.sh


So far in this tutorial we have created a landing page, and a means for users to add records to the system. What we havent done yet is created a way for them to retrieve data, worse we've teased them by putting an option on the landing page that currently does nothing.

Well now we are going to create the script that is supposed to sit on the end of that button. So without further ado, lets create our headers

nano gen_report.sh

#!/bin/bash
#
# gen_report.sh
#
# Part of the Claims_DB Sample Database Frontend
# Generates a report of all stock for a given location on the bike
#
# Copyright Ben Tasker 2009
#
# Released under the GNU GPL
# See http://benscomputer.no-ip.org/LICENSE


# Set yer variables here

CLAIMS_PROGS="/home/ben/programs/Claims_DB/"
DBNAME="Sample_DB"

# Tell the browser our MIME Type
echo Content-type:
text/html
echo ""

Now we want to grab the Request URI to find out which query criteria the user selected.

# Grab the Query Criteria from the Request URI
REQUEST_URI=$( /bin/env | grep "REQUEST_URI" )

# Seperate out the Criteria
QUERYSTRINGA=$( echo "$REQUEST_URI" | sed -n 's/^.*CRITERIA=\([^&]*\).*$/\1/p' )

# Remove URL Encoding
QUERYSTRING=$( echo "$QUERYSTRINGA" | sed "s/%0D%0A/<br>/g" | sed "s/+/ /g" | sed "s/%21/\!/g" | sed "s/%40/@/g" | sed "s/%20/ /g" | sed "s/%26/\&/g" | sed "s/%3B/[SEMICOLON]/g" | sed "s/%22/[DOUBLEQUOTES]/g" | sed "s/%2F/\//g" | sed "s/%5C/\\\/g" | sed "s/%24/$/g" | sed "s/%A3/?/g" | sed "s/%27/\'/g" | sed "s/%23/\#/g" | sed "s/%3A/\:/g" | sed "s/%5B/\[/g" | sed "s/%40/@/g" | sed "s/%5D/\]/g" | sed "s/%25/\%/g" | sed "s/%5E/\^/g" | sed "s/%28/(/g" | sed "s/%29/)/g" | sed "s/%2B/+/g" | sed "s/%2C/,/g" )
Now we know what the query string was, we can make the query and retrieve the data needed to generate our report.

# Get the Column Headers

DBROOT="$DBNAME" TABLE="Catalogue" "$CLAIMS_PROGS"/bin/read_records_claims.sh -headers > /tmp/REPHEAD.tmp

# Run the Query

DBROOT="$DBNAME" TABLE="Claims" "$CLAIMS_PROGS"/bin/read_records_claims.sh -query "$QUERYSTRING" 2 > 
/tmp/REBBOD.tmp

# Generate the Title Row

COLT1=$( awk -F\, '{print $1}' /tmp/REPHEAD.tmp | sed 's/\"//g' )
COLT2=$( awk -F\, '{print $2}' /tmp/REPHEAD.tmp | sed 's/\"//g' )
COLT3=$( awk -F\, '{print $3}' /tmp/REPHEAD.tmp | sed 's/\"//g' )
COLT4=$( awk -F\, '{print $4}' /tmp/REPHEAD.tmp | sed 's/\"//g' )
COLT5=$( awk -F\, '{print $5}' /tmp/REPHEAD.tmp | sed 's/\"//g' )
COLT6=$( awk -F\, '{print $6}' /tmp/REPHEAD.tmp | sed 's/\"//g' )
COLT7=$( awk -F\, '{print $7}' /tmp/REPHEAD.tmp | sed 's/\"//g' )
COLT8=$( awk -F\, '{print $8}' /tmp/REPHEAD.tmp | sed 's/\"//g' )
COLT9=$( awk -F\, '{print $9}' /tmp/REPHEAD.tmp | sed 's/\"//g' )
COLT10=$( awk -F\, '{print $10}' /tmp/REPHEAD.tmp | sed 's/\"//g' )
COLT11=$( awk -F\, '{print $11}' /tmp/REPHEAD.tmp | sed 's/\"//g' )
COLT12=$( awk -F\, '{print $12}' /tmp/REPHEAD.tmp | sed 's/\"//g' )

We can now start by creating the HTML, ready to begin inserting data. You'll notice a reference to a CSS file on this server, this contains formatting to try and ensure that if the report is printed it will not expand over more than one page wide. Click here to view the CSS file.

# Start Generating the HTML

DATESTAMP=$( date +'%A %d %B %Y' )

/bin/cat << EOM


<html><head>
<title>Parts for location $QUERYSTRING</title>
<link rel="StyleSheet" href="http://benscomputer.no-ip.org/stylesheets/print_report_land.css" type="text/css" 
media="print">
</head>
<body bgcolor="white">
<b><font size="+1"><font size="+12">Parts for location 
$QUERYSTRING</b></font></font><br>
<br><br>
<font size="+1">
$DATESTAMP</font>
<br>
<hr>

<table style="width: 100%; text-align: left;" border="0" cellpadding="2"
cellspacing="2">
<tbody><tr>
<td style="vertical-align: top; text-align: center;">
<b>$COLT1</b>
</b>
</td>

<td style="vertical-align: top; text-align: center;">
<b>$COLT2</b>
</td>

<td style="vertical-align: top; text-align: center;">
<b>$COLT3</b>
</td>

<td style="vertical-align: top; text-align: center;">
<b>$COLT4</b>
</td>


<td style="vertical-align: top; text-align: center;">
<b>$COLT5</b>
</td>


<td style="vertical-align: top; text-align: center;">
<b>$COLT6</b>
</td>


<td style="vertical-align: top; text-align: center;">
<b>$COLT7</b>
</td>


<td style="vertical-align: top; text-align: center;">
<b>$COLT8</b>
</td>


<td style="vertical-align: top; text-align: center;">
<b>$COLT9</b>
</td>


<td style="vertical-align: top; text-align: center;">
<b>$COLT10</b>

</td></tr>
<tr><td colspan="13"><hr></td></tr>
EOM

What we have at this point looks a little something like this;


Parts for Location (whatever the user queried)

Saturday 20 June 2009

SN
Description
Part Location
Part Number
Number Type
Stockist
Qty in Pack
Qty on bike
Rating
Bike




The data will then be placed below this row by row. So in order to achieve this we need to do the following;

# Populate the Table with data, but sort it first

cat /tmp/REBBOD.tmp | awk -F"," '{print $1"," $2"," $3"," $4"," $5"," $6"," $7"," $8"," $9"," $10","}' | sort -n -k 1 > /tmp/REBBOD.sorted

# Remove the old unsorted temp file
rm -f /tmp/REBBOD.tmp


# Go through Row by Row
while read -r a
do
#Extract each column

COLT1=$( echo "$a" | awk -F\, '{print $1}' | sed 's/\"//g' )
COLT2=$( echo "$a" | awk -F\, '{print $2}' | sed 's/\"//g' )
COLT3=$( echo "$a" | awk -F\, '{print $3}' | sed 's/\"//g' )
COLT4=$( echo "$a" | awk -F\, '{print $4}' | sed 's/\"//g' )
COLT5=$( echo "$a" | awk -F\, '{print $5}' | sed 's/\"//g' )
COLT6=$( echo "$a" | awk -F\, '{print $6}' | sed 's/\"//g' )
COLT7=$( echo "$a" | awk -F\, '{print $7}' | sed 's/\"//g' )
COLT8=$( echo "$a" | awk -F\, '{print $8}' | sed 's/\"//g' )
COLT9=$( echo "$a" | awk -F\, '{print $9}' | sed 's/\"//g' )
COLT10=$( echo "$a" | awk -F\, '{print $10}' | sed 's/\"//g' )

# Generate the HTML

/bin/cat << EOM

<tr>
<td style="vertical-align: top; text-align: center;">
$COLT1
</b>
</td>

<td style="vertical-align: top; text-align: center;">
$COLT2
</td>

<td style="vertical-align: top; text-align: center;">
$COLT3
</td>

<td style="vertical-align: top; text-align: center;">
$COLT4
</td>


<td style="vertical-align: top; text-align: center;">
$COLT5
</td>


<td style="vertical-align: top; text-align: center;">
$COLT6
</td>


<td style="vertical-align: top; text-align: center;">
$COLT7
</td>


<td style="vertical-align: top; text-align: center;">
$COLT8
</td>


<td style="vertical-align: top; text-align: center;">
$COLT9
</td>


<td style="vertical-align: top; text-align: center;">
$COLT10

</td>

</tr>
EOM


done < /tmp/REBBOD.sorted

rm -f /tmp/REBBOD.sorted 2> /dev/null

And then finish off

# generate the closing HTML
/bin/cat << EOM
</tbody></table><br>
<hr>
</body>
</html>
EOM

# We're finished!
exit
So the finished result should look something like this;


Parts for Location (whatever the user queried)

Saturday 20 June 2009

SN
Description
Part Location
Part Number
Number Type
Stockist
Qty in Pack
Qty on bike
Rating
Bike


1
Somepart
Location
12345
OEM
N/A
1
2
N/A
SV650-S
5
Someotherpart
Location
632458
Stockist
FavShop
2
1
10W
SV650-S
14
Anotherpart
Location
98541652
OEM
N/A
1
4
N/A
SV650-S



And so long as the users browser supports CSS when they try to print the report, it should fit onto the width of a Landscape A4 sheet. They may have to set the pagination in Page Setup though as some browsers don't seem to support that setting.

The source for this section can be downloaded from here.

Tutorial Part 6 - Closing Notes



 
 Share