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 URINow we know what the query string was, we can make the query and retrieve the data needed to generate our report.
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" )
# 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="/images/benscomputer_no-ip_org_Archive/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! exitSo 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
