#!/bin/bash
#
# Protocol driver for reading data from the Claims Database
#
# Filename: read_records_claims.sh

# To see how to use this script, read the Protocol Documentation
# Available from http://benscomputer.no-ip.org/Claims_DB/
SCRIPT_VERSION="0.21"


while [ -e /tmp/DBCLEANUP.LOCK ]
do

sleep 2


done




report_version_function ()
{
# Report back the version of Claims_DB that we are running
#
# Added in V0.21
echo "In report_version_function" >> "$DEBUGPATH"
# We Don't need the Tempfile
rm -f "$DBFILE" >> "$DEBUGPATH"

 

# If Versioning is disabled in the config, don't report the info back

if [ "$VERSIONING_DISABLE" == "True" ]
then
echo "Version Reporting Disabled by VERSIONING_DISABLE" >> "$DEBUGPATH"
# This could be handy from a security point of view
# That said, an attacker could submit a variety of different requests 
# and establish which version we are running based on the available functions!

# Don't report the version back
echo "Function Disabled"


exit


fi
echo "Version Reporting Enabled, Reporting Back" >> "$DEBUGPATH"
# We are allowed to report the function back
echo "read_records_claims.sh V$SCRIPT_VERSION"
exit
}



generate_table_sig_function ()
{
# Function added in V0.21 Development


if [ "$DBROOT" == "" ]
then
# We were not passed the Database's name
echo "BADREQUEST"
exit
fi
if [ "$TABLE" == "" ]
then
# We were not passed the Table name
echo "BADREQUEST"
exit
fi

# We have table and DBName so generate a checksum

$SUMPROGRAM "$PROGROOT"/"$DBROOT"/"$TABLE".csv

# Simple function this one!
# One line and we're done, calooh calay!
exit
}



table_size_function ()
{

wc -l "$DBFILE" > /tmp/$$.TABLESIZE
AWKCMD=$( awk -F\  '{print $1}' /tmp/$$.TABLESIZE )
ROW_COUNT=$(( $AWKCMD - 1 ))
echo "$ROW_COUNT"
rm -f /tmp/$$.TABLESIZE
exit

}


database_size_function ()
{

wc -l "$PROGROOT"/"$DBROOT"/*.csv > /tmp/$$.TABLESIZE.1
cat /tmp/$$.TABLESIZE.1 | grep "total" > /tmp/$$.TABLESIZE
AWKCMD=$( awk -F\  '{print $1}' /tmp/$$.TABLESIZE )

ls -l "$PROGROOT"/"$DBROOT"/*.csv > /tmp/$$.TABLECOUNT
wc -l /tmp/$$.TABLECOUNT > /tmp/$$.TABLECOUNT.1


HEADER_ROWS=$( awk -F\  '{print $1}' /tmp/$$.TABLECOUNT.1 )
ROW_COUNT=$(( $AWKCMD - $HEADER_ROWS ))

echo "$ROW_COUNT"
rm -f /tmp/$$.TABLESIZE
rm -f /tmp/$$.TABLECOUNT.1
rm -f /tmp/$$.TABLECOUNT
exit


}



select_distinct_function ()
{
echo "select_distinct_funtion - COLREF = $COLREF" >> "$DEBUGPATH"
COLREF="$OTHERCOLREF"
if [ "$COLREF" == "1" ]
then
AWKCMD=$( awk -F\, '{print $1}' "$DBFILE" )
fi
if [ "$COLREF" == "2" ]
then
AWKCMD=$( awk -F\, '{print $2}' "$DBFILE" )
fi

if [ "$COLREF" == "3" ]
then
AWKCMD=$( awk -F\, '{print $3}' "$DBFILE" )
fi
if [ "$COLREF" == "4" ]
then
AWKCMD=$( awk -F\, '{print $4}' "$DBFILE" )
fi
if [ "$COLREF" == "5" ]
then
AWKCMD=$( awk -F\, '{print $5}' "$DBFILE" )
fi
if [ "$COLREF" == "6" ]
then
AWKCMD=$( awk -F\, '{print $6}' "$DBFILE" )
fi
if [ "$COLREF" == "7" ]
then
AWKCMD=$( awk -F\, '{print $7}' "$DBFILE" )
fi
if [ "$COLREF" == "8" ]
then
AWKCMD=$( awk -F\, '{print $8}' "$DBFILE" )
fi
if [ "$COLREF" == "9" ]
then
AWKCMD=$( awk -F\, '{print $9}' "$DBFILE" )
fi
if [ "$COLREF" == "10" ]
then
AWKCMD=$( awk -F\, '{print $10}' "$DBFILE" )
fi
if [ "$COLREF" == "11" ]
then
AWKCMD=$( awk -F\, '{print $11}' "$DBFILE" )
fi
if [ "$COLREF" == "12" ]
then
AWKCMD=$( awk -F\, '{print $12}' "$DBFILE" )
fi
if [ "$COLREF" == "13" ]
then
AWKCMD=$( awk -F\, '{print $13}' "$DBFILE" )
fi
if [ "$COLREF" == "14" ]
then
AWKCMD=$( awk -F\, '{print $14}' "$DBFILE" )
fi
if [ "$COLREF" == "15" ]
then
AWKCMD=$( awk -F\, '{print $15}' "$DBFILE" )
fi
if [ "$COLREF" == "16" ]
then
AWKCMD=$( awk -F\, '{print $16}' "$DBFILE" )
fi
if [ "$COLREF" == "17" ]
then
AWKCMD=$( awk -F\, '{print $17}' "$DBFILE" )
fi
if [ "$COLREF" == "18" ]
then
AWKCMD=$( awk -F\, '{print $18}' "$DBFILE" )
fi
if [ "$COLREF" == "19" ]
then
AWKCMD=$( awk -F\, '{print $19}' "$DBFILE" )
fi
if [ "$COLREF" == "20" ]
then
AWKCMD=$( awk -F\, '{print $20}' "$DBFILE" )
fi

echo "Parsing Information" >> "$DEBUGPATH"
# Now seperate out the column, and remove duplicates
/bin/cat << EOM > /$DBTEMPDIR/LOCATS2.tmp.$MYPID
$AWKCMD 
EOM


SKIPINFO=$( head -n 1 /$DBTEMPDIR/LOCATS2.tmp.$MYPID )
cat /$DBTEMPDIR/LOCATS2.tmp.$MYPID | grep -v "$SKIPINFO" > /$DBTEMPDIR/LOCATS2.tmp2.$MYPID
mv /$DBTEMPDIR/LOCATS2.tmp2.$MYPID /$DBTEMPDIR/LOCATS2.tmp.$MYPID >> "$DEBUGPATH"
echo "Removing Duplicates" >> "$DEBUGPATH"
awk '{
if ($0 in stored_lines)
x=1
else
print
stored_lines[$0]=1
}' /$DBTEMPDIR/LOCATS2.tmp.$MYPID | sed s/\"//g
echo "Exiting" >> "$DEBUGPATH"
exit

}

select_next_pk ()
{
echo "Primary Key Generation" >> "$DEBUGPATH"

COLREF="$OTHERCOLREF"
echo "Primary Key Generation - Primary Key Column = $COLREF" >> "$DEBUGPATH"
if [ "$COLREF" == "1" ]
then

AWKCMD=$( awk -F\, '{print $1}' "$DBFILE" )
fi
if [ "$COLREF" == "2" ]
then
AWKCMD=$( awk -F\, '{print $2}' "$DBFILE" )
fi

if [ "$COLREF" == "3" ]
then
AWKCMD=$( awk -F\, '{print $3}' "$DBFILE" )
fi
if [ "$COLREF" == "4" ]
then
AWKCMD=$( awk -F\, '{print $4}' "$DBFILE" )
fi
if [ "$COLREF" == "5" ]
then
AWKCMD=$( awk -F\, '{print $5}' "$DBFILE" )
fi
if [ "$COLREF" == "6" ]
then
AWKCMD=$( awk -F\, '{print $6}' "$DBFILE" )
fi
if [ "$COLREF" == "7" ]
then
AWKCMD=$( awk -F\, '{print $7}' "$DBFILE" )
fi
if [ "$COLREF" == "8" ]
then
AWKCMD=$( awk -F\, '{print $8}' "$DBFILE" )
fi
if [ "$COLREF" == "9" ]
then
AWKCMD=$( awk -F\, '{print $9}' "$DBFILE" )
fi
if [ "$COLREF" == "10" ]
then
AWKCMD=$( awk -F\, '{print $10}' "$DBFILE" )
fi
if [ "$COLREF" == "11" ]
then
AWKCMD=$( awk -F\, '{print $11}' "$DBFILE" )
fi
if [ "$COLREF" == "12" ]
then
AWKCMD=$( awk -F\, '{print $12}' "$DBFILE" )
fi
if [ "$COLREF" == "13" ]
then
AWKCMD=$( awk -F\, '{print $13}' "$DBFILE" )
fi
if [ "$COLREF" == "14" ]
then
AWKCMD=$( awk -F\, '{print $14}' "$DBFILE" )
fi
if [ "$COLREF" == "15" ]
then
AWKCMD=$( awk -F\, '{print $15}' "$DBFILE" )
fi
if [ "$COLREF" == "16" ]
then
AWKCMD=$( awk -F\, '{print $16}' "$DBFILE" )
fi
if [ "$COLREF" == "17" ]
then
AWKCMD=$( awk -F\, '{print $17}' "$DBFILE" )
fi
if [ "$COLREF" == "18" ]
then
AWKCMD=$( awk -F\, '{print $18}' "$DBFILE" )
fi
if [ "$COLREF" == "19" ]
then
AWKCMD=$( awk -F\, '{print $19}' "$DBFILE" )
fi
if [ "$COLREF" == "20" ]
then
AWKCMD=$( awk -F\, '{print $20}' "$DBFILE" )
fi

/bin/cat << EOM > /$DBTEMPDIR/LOCATS2.tmp.$MYPID
$AWKCMD
EOM

# Strip out the text Delimiter - "
sed s/\"//g /$DBTEMPDIR/LOCATS2.tmp.$MYPID > /$DBTEMPDIR/LOCATS.tmp.$MYPID
echo "Sorting Results" >> "$DEBUGPATH"
# Now lets sort it
cat /$DBTEMPDIR/LOCATS.tmp.$MYPID | sort -n -k 1 > /$DBTEMPDIR/LOCATS2.tmp.$MYPID

# What is the final entry, load it into a variable
echo "Loading Variable" >> "$DEBUGPATH"
LASTKEY=$( cat /$DBTEMPDIR/LOCATS2.tmp.$MYPID | tail -n 1 )

# Work out the next key with a simple bit of maths

NEWKEY=$(( $LASTKEY + 1 ))
rm -f /$DBTEMPDIR/LOCATS.tmp.$MYPID
rm -f /$DBTEMPDIR/LOCATS2.tmp.$MYPID
echo $NEWKEY
echo "Exiting" >> "$DEBUGPATH"
exit

}

read_data ()
{
# User wants Data from the file
echo "Reading table $TABLE" >> "$DEBUGPATH"

# Create the temporary file, without headers
cat "$DBFILE" | grep -v "$HEADERS" > "$DBTEMPFILE"



# Read line by line
while read -r a                    
do   
echo $a
done < "$DBTEMPFILE"

rm -f $DBTEMPFILE 2> /dev/null
rm -f $DBFILE 2> /dev/null
echo "Exiting" >> "$DEBUGPATH"
exit
}

query_database ()
{
echo "Function query_database" >> "$DEBUGPATH"
echo "Column Reference - $COLREF" >> "$DEBUGPATH"
# Queries the selected Dataset

if [ "$COLREF" == "1" ]
then
AWKCMD=$( awk -F\, '{print $1}' "$DBFILE" )
fi
if [ "$COLREF" == "2" ]
then
AWKCMD=$( awk -F\, '{print $2}' "$DBFILE" )
fi

if [ "$COLREF" == "3" ]
then
AWKCMD=$( awk -F\, '{print $3}' "$DBFILE" )
fi
if [ "$COLREF" == "4" ]
then
AWKCMD=$( awk -F\, '{print $4}' "$DBFILE" )
fi
if [ "$COLREF" == "5" ]
then
AWKCMD=$( awk -F\, '{print $5}' "$DBFILE" )
fi
if [ "$COLREF" == "6" ]
then
AWKCMD=$( awk -F\, '{print $6}' "$DBFILE" )
fi
if [ "$COLREF" == "7" ]
then
AWKCMD=$( awk -F\, '{print $7}' "$DBFILE" )
fi
if [ "$COLREF" == "8" ]
then
AWKCMD=$( awk -F\, '{print $8}' "$DBFILE" )
fi
if [ "$COLREF" == "9" ]
then
AWKCMD=$( awk -F\, '{print $9}' "$DBFILE" )
fi
if [ "$COLREF" == "10" ]
then
AWKCMD=$( awk -F\, '{print $10}' "$DBFILE" )
fi
if [ "$COLREF" == "11" ]
then
AWKCMD=$( awk -F\, '{print $11}' "$DBFILE" )
fi
if [ "$COLREF" == "12" ]
then
AWKCMD=$( awk -F\, '{print $12}' "$DBFILE" )
fi
if [ "$COLREF" == "13" ]
then
AWKCMD=$( awk -F\, '{print $13}' "$DBFILE" )
fi
if [ "$COLREF" == "14" ]
then
AWKCMD=$( awk -F\, '{print $14}' "$DBFILE" )
fi
if [ "$COLREF" == "15" ]
then
AWKCMD=$( awk -F\, '{print $15}' "$DBFILE" )
fi
if [ "$COLREF" == "16" ]
then
AWKCMD=$( awk -F\, '{print $16}' "$DBFILE" )
fi
if [ "$COLREF" == "17" ]
then
AWKCMD=$( awk -F\, '{print $17}' "$DBFILE" )
fi
if [ "$COLREF" == "18" ]
then
AWKCMD=$( awk -F\, '{print $18}' "$DBFILE" )
fi
if [ "$COLREF" == "19" ]
then
AWKCMD=$( awk -F\, '{print $19}' "$DBFILE" )
fi
if [ "$COLREF" == "20" ]
then
AWKCMD=$( awk -F\, '{print $20}' "$DBFILE" )
fi
echo "Query String - $QUERYSTRING" >> "$DEBUGPATH"
# Select Columns
/bin/cat << EOM | grep -nw "\"$QUERYSTRING\"" | sed s/\ //g | sed s/\=/EQUALS/g > /$DBTEMPDIR/DBRECS.$MYPID
$AWKCMD
EOM

chmod 777 /$DBTEMPDIR/DBRECS.$MYPID >> "$DEBUGPATH"

rm -f /$DBTEMPDIR/DBRECS2.$MYPID 2> /dev/null
for a in $( cat /$DBTEMPDIR/DBRECS.$MYPID );
do
echo -n "=" >> /$DBTEMPDIR/DBRECS2.$MYPID
echo "$a" >> /$DBTEMPDIR/DBRECS2.$MYPID
done

RESULTS_ENUMERATE=$( cat /$DBTEMPDIR/DBRECS2.$MYPID 2> /dev/null )
if [ "$RESULTS_ENUMERATE" == "" ]
then
no_results
fi 

for a in $( cat /$DBTEMPDIR/DBRECS2.$MYPID );
do
LINE_NUMBER=$( echo "$a" | sed -n 's/^.*=\([^:]*\).*$/\1/p' )

# There used to be a good reason for this line
#LINE_NUMBER2=$(( $LINE_NUMBER - 1 ))

sed -n $LINE_NUMBER'p' $DBFILE

done
# Output the retrieved line for processing by external scripts
echo "exiting" >> "$DEBUGPATH"
exit




}

query_database_line ()
{
echo "Function query_database_line" >> "$DEBUGPATH"
echo "Query Column $COLREF" >> "$DEBUGPATH"
# Queries the selected Dataset

if [ "$COLREF" == "1" ]
then
AWKCMD=$( awk -F\, '{print $1}' $DBFILE )
fi
if [ "$COLREF" == "2" ]
then
AWKCMD=$( awk -F\, '{print $2}' $DBFILE )
fi

if [ "$COLREF" == "3" ]
then
AWKCMD=$( awk -F\, '{print $3}' $DBFILE )
fi
if [ "$COLREF" == "4" ]
then
AWKCMD=$( awk -F\, '{print $4}' $DBFILE )
fi
if [ "$COLREF" == "5" ]
then
AWKCMD=$( awk -F\, '{print $5}' $DBFILE )
fi
if [ "$COLREF" == "6" ]
then
AWKCMD=$( awk -F\, '{print $6}' $DBFILE )
fi
if [ "$COLREF" == "7" ]
then
AWKCMD=$( awk -F\, '{print $7}' $DBFILE )
fi
if [ "$COLREF" == "8" ]
then
AWKCMD=$( awk -F\, '{print $8}' $DBFILE )
fi
if [ "$COLREF" == "9" ]
then
AWKCMD=$( awk -F\, '{print $9}' $DBFILE )
fi
if [ "$COLREF" == "10" ]
then
AWKCMD=$( awk -F\, '{print $10}' $DBFILE )
fi
if [ "$COLREF" == "11" ]
then
AWKCMD=$( awk -F\, '{print $11}' $DBFILE )
fi
if [ "$COLREF" == "12" ]
then
AWKCMD=$( awk -F\, '{print $12}' $DBFILE )
fi
if [ "$COLREF" == "13" ]
then
AWKCMD=$( awk -F\, '{print $13}' $DBFILE )
fi
if [ "$COLREF" == "14" ]
then
AWKCMD=$( awk -F\, '{print $14}' $DBFILE )
fi
if [ "$COLREF" == "15" ]
then
AWKCMD=$( awk -F\, '{print $15}' $DBFILE )
fi
if [ "$COLREF" == "16" ]
then
AWKCMD=$( awk -F\, '{print $16}' $DBFILE )
fi
if [ "$COLREF" == "17" ]
then
AWKCMD=$( awk -F\, '{print $17}' $DBFILE )
fi
if [ "$COLREF" == "18" ]
then
AWKCMD=$( awk -F\, '{print $18}' $DBFILE )
fi
if [ "$COLREF" == "19" ]
then
AWKCMD=$( awk -F\, '{print $19}' $DBFILE )
fi
if [ "$COLREF" == "20" ]
then
AWKCMD=$( awk -F\, '{print $20}' $DBFILE )
fi

echo "Query string -  $QUERYSTRING" >> "$DEBUGPATH"
# Select Columns
/bin/cat << EOM | grep -nw "$QUERYSTRING" | sed s/\ //g | sed s/\=/EQUALS/g > /$DBTEMPDIR/DBRECS.$MYPID
chmod 777 /$DBTEMPDIR/DBRECS.$MYPID
$AWKCMD
EOM



rm -f /$DBTEMPDIR/DBRECS2.$MYPID 2> /dev/null


for a in $( cat /$DBTEMPDIR/DBRECS.$MYPID );
do
echo -n "=" >> /$DBTEMPDIR/DBRECS2.$MYPID
echo "$a" >> /$DBTEMPDIR/DBRECS2.$MYPID
done

RESULTS_ENUMERATE=$( cat /$DBTEMPDIR/DBRECS2.$MYPID 2> /dev/null )
if [ "$RESULTS_ENUMERATE" == "" ]
then
no_results
fi 

for a in $( cat /$DBTEMPDIR/DBRECS2.$MYPID );
do
LINE_NUMBER=$( echo "$a" | sed -n 's/^.*=\([^:]*\).*$/\1/p' )
# There used to be a good reason for this line
# LINE_NUMBER=$(( $LINE_NUMBER - 1 ))
echo "$LINE_NUMBER"

done
# Output the retrieved line for processing by external scripts
rm -f $DBTEMPFILE 2> /dev/null
rm -f $DBFILE 2> /dev/null

echo "Exiting" >> "$DEBUGPATH"
exit




}

no_results ()
{
# No results found
echo "No results found" >> "$DEBUGPATH"
echo "NORESULTS"
# And........... leave
echo "Exiting" >> "$DEBUGPATH"
exit
}

################################################################
################################################################
##################### Function Main ############################
################################################################
################################################################



# We have a variety of variables that need setting

# grab the main config file
source /etc/claims_db.conf

if [ "$DEBUG" == "Y" ]
then
DEBUGPATH="$DEBUGFILE"
else
DEBUGPATH="/dev/null"
fi
echo "Called at $( date )" >> "$DEBUGPATH"

# Make temp files unique
MYPID="$$"

# Check we have the needed information
if [ "$DBROOT" == "" ]
then
echo "DBNAME specified in place of DBROOT" >> "$DEBUGPATH"
DBROOT="$DBNAME"
fi

if [ "$PROGROOT" == "" ]
then
# Essential Config Variable not set
echo "BADREQUEST"
exit

fi

echo "Using Database $DBROOT and Table $TABLE " >> "$DEBUGPATH"
# Where are our debug messages going?




# Before we make a shadow of the database, or do any groundwork parsing
# lets see if it's actually necessary




if [ "$1" == "--gen-tbl-sig" ]
then
# Function added in V0.21 Development
#
# Intended to be used to allow frontends to support caching for quicker query response
#
# Bit of a blunt tool, but running the query and then checksumming it doesn't really save any time!

generate_table_sig_function

fi



if [ "$1" == "--version" ]
then
report_version_function
fi

if [ "$1" == "--tmp-cleanup" ]
then
# Clean up temporary files.

echo "" > /tmp/DBCLEANUP.LOCK

rm -f /$DBTEMPDIR/TMPSIZE
rm -f /$DBTEMPDIR/DEBUG*
rm -f /$DBTEMPDIR/LOCATS*
rm -f /$DBTEMPDIR/*.TABLE*
rm -f /$DBTEMPDIR/DB*

# Remove the lock file
rm -f /$DBTEMPDIR/DBCLEANUP.LOCK
exit
fi

# Looks like we need to get down and dirty with the table!



# Create a copy of the database table
# At least it won't screw the database if we cock it up
#
#
echo "Creating Database Temporary file" >> "$DEBUGPATH"
echo "Setting Variable" >> "$DEBUGPATH"

DBFILE="/$DBTEMPDIR/DBCOPY.$MYPID"


echo "Creating Shadow Database" >> "$DEBUGPATH"

cp "$PROGROOT"/"$DBROOT"/"$TABLE".csv "$DBFILE" >> "$DEBUGPATH"
chmod 777 "$DBFILE" >> "$DEBUGPATH"

# Some functions also use the file DBTEMPFILE
DBTEMPFILE="/$DBTEMPDIR/DBFILE.csv.$MYPID"

# Find out what the headers of the file are, then we can parse it out
HEADERS=$( head -1 $DBFILE )

# If a query has been submitted, check it
QUERYSTRING="$2"
COLREF="$3"
OTHERCOLREF="$2"

echo "Parse request argument processing" >> "$DEBUGPATH"
if [ "$1" == "-headers" ]
then
echo "Reporting Headers" >> "$DEBUGPATH"
# User wants Column Headers from the Table
echo "$HEADERS"
exit
fi

if [ "$1" == "-read" ]
then

# User wants to Read Data from the table
read_data
fi

if [ "$1" == "-query" ]
then

# user wants to query the database
query_database


fi

if [ "$1" == "-query-line" ]
then

# user wants to query the database
query_database_line


fi

# Added in V0.2

if [ "$1" == "-gen-PK" ]
then

# user wants to know what the next Primary key is
select_next_pk


fi

if [ "$1" == "-select-distinct" ]
then
select_distinct_function
fi

if [ "$1" == "-Table-size" ]
then
table_size_function
fi




echo "Unrecognised request, argument was $1" >> "$DEBUGPATH"
rm $DBFILE >> "$DEBUGPATH"
echo "BADREQUEST"
exit




# Select Columns
#awk -F\, '{print $1":"$3}' DBFILE


