#!/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 ""
# 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/ /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' )
# Start Generating the HTML
DATESTAMP=$( date +'%A %d %B %Y' )
/bin/cat << EOM
Parts for location $QUERYSTRINGParts for location $QUERYSTRING
$DATESTAMP
$COLT1
$COLT2
$COLT3
$COLT4
$COLT5
$COLT6
$COLT7
$COLT8
$COLT9
$COLT10
EOM
# 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
$COLT1
$COLT2
$COLT3
$COLT4
$COLT5
$COLT6
$COLT7
$COLT8
$COLT9
$COLT10
EOM
done < /tmp/REBBOD.sorted
rm -f /tmp/REBBOD.sorted 2> /dev/null
# generate the closing HTML
/bin/cat << EOM