#!/bin/bash
#
# CLI DBMS for the Claims_db Protocol Drivers
#
# Filename: Claims_DB_CLI.sh
#
# Copyright Ben Tasker 2009
#
# Released under the GNU GPL
# See http://benscomputer.no-ip.org/LICENSE for a copy of the License

source /home/ben/sandbox/Claims_DB/config/config_CLI


Reports ()
{

# Query the built in Database for Database Information

DBROOT="Databases" TABLE="Reports" "$CLAIMS_PROGS"/bin/read_records_claims.sh -query "$SELECTED_DATABASE_ID" 1 > /tmp/DBLISTING


echo "Report Selection"
echo "------------------"
echo ""
echo ""
echo "Please select the Report you wish to run"
echo ""
awk -F\, '{print $3}' /tmp/DBLISTING > /tmp/DBLISTING2
awk -F\, '{print $2}' /tmp/DBLISTING > /tmp/DBLISTING2_ID
# Grab Line by line
rm -f /tmp/DBLISTING3 2> /dev/null

# Find the Database Names
for a in $( cat /tmp/DBLISTING2 );
do

LINE_NOS=$( cat /tmp/DBLISTING2 | grep -nw "$a" )
echo -n "=" >> /tmp/DBLISTING3
echo "$LINE_NOS" >> /tmp/DBLISTING3


done


# Now match those to their ID's
for a in $( cat /tmp/DBLISTING3 );
do


LINE_NUMBER=$( echo "$a" | sed -n 's/^.*=\([^:]*\).*$/\1/p' )
sed -n $LINE_NUMBER'p' /tmp/DBLISTING2_ID > /tmp/DBLISTING4

# Grab the ID
ID_ID=$( cat /tmp/DBLISTING4 )
ID_NAME=$(sed -n "$LINE_NUMBER"p /tmp/DBLISTING2 )

echo "$ID_ID $ID_NAME" >> /tmp/DBLISTING5


done



sed s/\"//g /tmp/DBLISTING5 > /tmp/DBLISTING

cat /tmp/DBLISTING

rm -f /tmp/DBLISTING*
echo ""
echo "Enter the Report ID"
echo ""
read DBCHOICE
clear
# Convert back to the name
DBROOT="Databases" TABLE="Reports" "$CLAIMS_PROGS"/bin/read_records_claims.sh -query "$DBCHOICE" 2 > /tmp/DBLISTING
SELECTED_QUERY=$( awk -F\, '{print $3}' /tmp/DBLISTING | sed s/\"//g )
SELECTED_QUERY_ID="$DBCHOICE"

clear
# Run the Report

source "$CLAIMS_PROGS"/db/"$SELECTED_DATABASE"/"Reports"/"$SELECTED_QUERY"

echo "Press s to Save, p to Print, q to return to main Menu"
read REPORTCHOICE

if [ "$REPORTCHOICE" == "s" ]
then
#Output the Report to a file of the users choice
echo "Enter File to Save to (i.e. /home/$USER/ReportName.txt ), Leave blank to use default (/home/$USER/Report.txt)"
read SAVEPATH

if [ "$SAVEPATH" == "" ]
then
SAVEPATH="/home/$USER/Report.txt"

fi
AUTOMATED="1"
source "$CLAIMS_PROGS"/db/"$SELECTED_DATABASE"/"Reports"/"$SELECTED_QUERY" > "$SAVEPATH"
if [ "$?" == "0" ]
then
# Check for errors
echo "Save Succcessful, Report saved to $SAVEPATH"
sleep 2
AUTOMATED="0"
main_menu
else
echo "Save Failed, Do we have write access to the Destination? Or is your Disk Full?"
echo "Will return to Main Menu in 2 Seconds"
sleep 2
AUTOMATED="0"
main_menu


fi
fi


if [ "$REPORTCHOICE" == "q" ]
then
# Return to Main Menu
AUTOMATED="0"

main_menu
fi

if [ "$REPORTCHOICE" == "p" ]
then
# Print the file
AUTOMATED="1"
source "$CLAIMS_PROGS"/db/"$SELECTED_DATABASE"/"Reports"/"$SELECTED_QUERY" > /tmp/REPORTPRINT

if [ "$PAGE_ORIENTATION" == "Portrait" ]
then

lp /tmp/REPORTPRINT
else
lp -olandscape /tmp/REPORTPRINT

fi
AUTOMATED="0"
rm -f /tmp/REPORTPRINT
echo "Returning to Main Menu in 2 seconds"
sleep 2
main_menu

fi

echo "Response not Recognised, Returning to Main Menu"
sleep 2
main_menu

}

select_db ()
{
# Query the built in Database for Database Information

DBROOT="Databases" TABLE="Databases" "$CLAIMS_PROGS"/bin/read_records_claims.sh -read > /tmp/DBLISTING

echo "Database Selection"
echo "------------------"
echo ""
echo ""
echo "Please select the database you wish to use"
echo ""
awk -F\, '{print $2}' /tmp/DBLISTING > /tmp/DBLISTING2
awk -F\, '{print $1}' /tmp/DBLISTING > /tmp/DBLISTING2_ID
# Grab Line by line
rm -f /tmp/DBLISTING3 2> /dev/null

# Find the Database Names
for a in $( cat /tmp/DBLISTING2 );
do

LINE_NOS=$( cat /tmp/DBLISTING2 | grep -nw "$a" )
echo -n "=" >> /tmp/DBLISTING3
echo "$LINE_NOS" >> /tmp/DBLISTING3


done


# Now match those to their ID's
for a in $( cat /tmp/DBLISTING3 );
do


LINE_NUMBER=$( echo "$a" | sed -n 's/^.*=\([^:]*\).*$/\1/p' )
sed -n $LINE_NUMBER'p' /tmp/DBLISTING2_ID > /tmp/DBLISTING4

# Grab the ID
ID_ID=$( cat /tmp/DBLISTING4 )
ID_NAME=$(sed -n "$LINE_NUMBER"p /tmp/DBLISTING2 )

echo "$ID_ID $ID_NAME" >> /tmp/DBLISTING5


done



sed s/\"//g /tmp/DBLISTING5 > /tmp/DBLISTING

cat /tmp/DBLISTING

rm -f /tmp/DBLISTING*
echo ""
echo "Enter the Database ID"
echo ""
read DBCHOICE

# Convert back to the name
DBROOT="Databases" TABLE="Databases" "$CLAIMS_PROGS"/bin/read_records_claims.sh -query "$DBCHOICE" 1 > /tmp/DBLISTING
SELECTED_DATABASE=$( awk -F\, '{print $2}' /tmp/DBLISTING | sed s/\"//g )
SELECTED_DATABASE_ID="$DBCHOICE"

main_menu
}

select_Table ()
{
# Query the built in Database for Database Information

DBROOT="Databases" TABLE="Tables" "$CLAIMS_PROGS"/bin/read_records_claims.sh -query "$SELECTED_DATABASE_ID" 1 > /tmp/DBLISTING

echo "Table Selection"
echo "------------------"
echo ""
echo ""
echo "Please select the Table you wish to edit"
echo ""
awk -F\, '{print $4}' /tmp/DBLISTING > /tmp/DBLISTING2
awk -F\, '{print $3}' /tmp/DBLISTING > /tmp/DBLISTING2_ID
# Grab Line by line
rm -f /tmp/DBLISTING3 2> /dev/null

# Find the Database Names
for a in $( cat /tmp/DBLISTING2 );
do

LINE_NOS=$( cat /tmp/DBLISTING2 | grep -nw "$a" )
echo -n "=" >> /tmp/DBLISTING3
echo "$LINE_NOS" >> /tmp/DBLISTING3


done


# Now match those to their ID's
for a in $( cat /tmp/DBLISTING3 );
do


LINE_NUMBER=$( echo "$a" | sed -n 's/^.*=\([^:]*\).*$/\1/p' )
sed -n $LINE_NUMBER'p' /tmp/DBLISTING2_ID > /tmp/DBLISTING4

# Grab the ID
ID_ID=$( cat /tmp/DBLISTING4 )
ID_NAME=$(sed -n "$LINE_NUMBER"p /tmp/DBLISTING2 )

echo "$ID_ID $ID_NAME" >> /tmp/DBLISTING5


done



sed s/\"//g /tmp/DBLISTING5 > /tmp/DBLISTING

cat /tmp/DBLISTING

rm -f /tmp/DBLISTING*
echo ""
echo "Enter the Table ID"
echo ""
read DBCHOICE

# Convert back to the name
DBROOT="Databases" TABLE="Tables" "$CLAIMS_PROGS"/bin/read_records_claims.sh -query "$DBCHOICE" 3 > /tmp/DBLISTING
SELECTED_TABLE=$( awk -F\, '{print $4}' /tmp/DBLISTING | sed s/\"//g )
SELECTED_TABLE_ID="$DBCHOICE"

# Let the user insert a record next
add_record
}


add_record ()
{
# Adds a record to the selected Table

# Grab Column Headers
DBROOT="$SELECTED_DATABASE" TABLE="$SELECTED_TABLE" "$CLAIMS_PROGS"/bin/read_records_claims.sh -headers > /tmp/DBRECADD
# Print Header for Column 1
COLHEAD=$( awk -F\, '{print $1}' /tmp/DBRECADD | sed s/\"//g )

echo "Enter a value for $COLHEAD (leave blank to use Autonumber) "
read COL1

if [ "$COL1" == "" ]
then

if [ -e "$CLAIMS_PROGS"/db/"$SELECTED_DATABASE"/"$SELECTED_TABLE"_PK ]
then

PK_VALUE=$( cat "$CLAIMS_PROGS"/db/"$SELECTED_DATABASE"/"$SELECTED_TABLE"_PK )
PK_VALUE=$(( $PK_VALUE + 1 ))
else
PK_VALUE="1"

fi
COL1="$PK_VALUE"

fi


COLHEAD=$( awk -F\, '{print $2}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $2}' /tmp/DBRECADD | sed s/\"//g
read COL2

COLHEAD=$( awk -F\, '{print $3}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $3}' /tmp/DBRECADD | sed s/\"//g
read COL3

COLHEAD=$( awk -F\, '{print $4}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $4}' /tmp/DBRECADD | sed s/\"//g
read COL4

COLHEAD=$( awk -F\, '{print $5}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $5}' /tmp/DBRECADD | sed s/\"//g
read COL5

COLHEAD=$( awk -F\, '{print $6}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $6}' /tmp/DBRECADD | sed s/\"//g
read COL6

COLHEAD=$( awk -F\, '{print $7}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $7}' /tmp/DBRECADD | sed s/\"//g
read COL7


COLHEAD=$( awk -F\, '{print $8}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $8}' /tmp/DBRECADD | sed s/\"//g
read COL8

COLHEAD=$( awk -F\, '{print $9}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $9}' /tmp/DBRECADD | sed s/\"//g
read COL9

COLHEAD=$( awk -F\, '{print $10}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $10}' /tmp/DBRECADD | sed s/\"//g
read COL10

COLHEAD=$( awk -F\, '{print $11}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $11}' /tmp/DBRECADD | sed s/\"//g
read COL11

COLHEAD=$( awk -F\, '{print $12}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $12}' /tmp/DBRECADD | sed s/\"//g
read COL12

COLHEAD=$( awk -F\, '{print $13}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $13}' /tmp/DBRECADD | sed s/\"//g
read COL13
COLHEAD=$( awk -F\, '{print $14}' /tmp/DBRECADD | sed s/\"//g )
if [ "$COLHEAD" == "" ]
then
commit_record

fi
echo -n "Enter a value for  "
awk -F\, '{print $14}' /tmp/DBRECADD | sed s/\"//g
read COL14
#write the record to the table
commit_record
}


commit_record ()
{

# Write new record to the table

COL1="$COL1" COL2="$COL2" COL3="$COL3" COL4="$COL4" COL5="$COL5" COL6="$COL6" COL7="$COL7" COL8="$COL8" COL9="$COL9" COL10="$COL10" COL11="$COL11" COL12="$COL12" COL13="$COL13" COL14="$COL14" DBROOT="$SELECTED_DATABASE" TABLE="$SELECTED_TABLE" "$CLAIMS_PROGS"/bin/insert_record_claims.sh -insert > /tmp/DBRECADD

STATUS=$( cat /tmp/DBRECADD )

if [ "$STATUS" == "SUCCESS" ]
then
echo $PK_VALUE > "$CLAIMS_PROGS"/db/"$SELECTED_DATABASE"/"$SELECTED_TABLE"_PK
echo "Record Inserted Successfully"
else
echo "Record Insertion failed, response code was $STATUS"


fi




# Tidy up after the last function
rm /tmp/DBRECADD
main_menu
}

select_Query ()
{
# Query the built in Database for Database Information

DBROOT="Databases" TABLE="Queries" "$CLAIMS_PROGS"/bin/read_records_claims.sh -query "$SELECTED_DATABASE_ID" 1 > /tmp/DBLISTING


echo "Query Selection"
echo "------------------"
echo ""
echo ""
echo "Please select the Query you wish to run"
echo ""
awk -F\, '{print $3}' /tmp/DBLISTING > /tmp/DBLISTING2
awk -F\, '{print $2}' /tmp/DBLISTING > /tmp/DBLISTING2_ID
# Grab Line by line
rm -f /tmp/DBLISTING3 2> /dev/null

# Find the Database Names
for a in $( cat /tmp/DBLISTING2 );
do

LINE_NOS=$( cat /tmp/DBLISTING2 | grep -nw "$a" )
echo -n "=" >> /tmp/DBLISTING3
echo "$LINE_NOS" >> /tmp/DBLISTING3


done


# Now match those to their ID's
for a in $( cat /tmp/DBLISTING3 );
do


LINE_NUMBER=$( echo "$a" | sed -n 's/^.*=\([^:]*\).*$/\1/p' )
sed -n $LINE_NUMBER'p' /tmp/DBLISTING2_ID > /tmp/DBLISTING4

# Grab the ID
ID_ID=$( cat /tmp/DBLISTING4 )
ID_NAME=$(sed -n "$LINE_NUMBER"p /tmp/DBLISTING2 )

echo "$ID_ID $ID_NAME" >> /tmp/DBLISTING5


done



sed s/\"//g /tmp/DBLISTING5 > /tmp/DBLISTING

cat /tmp/DBLISTING

rm -f /tmp/DBLISTING*
echo ""
echo "Enter the Query ID"
echo ""
read DBCHOICE

# Convert back to the name
DBROOT="Databases" TABLE="Queries" "$CLAIMS_PROGS"/bin/read_records_claims.sh -query "$DBCHOICE" 2 > /tmp/DBLISTING
SELECTED_QUERY=$( awk -F\, '{print $3}' /tmp/DBLISTING | sed s/\"//g )
SELECTED_QUERY_ID="$DBCHOICE"

clear
# Run the Query
chmod +x "$CLAIMS_PROGS"/db/"$SELECTED_DATABASE"/"Queries"/"$SELECTED_QUERY"
CLAIMS_PROGS="$CLAIMS_PROGS" DBROOT="$SELECTED_DATABASE" "$CLAIMS_PROGS"/db/"$SELECTED_DATABASE"/"Queries"/"$SELECTED_QUERY"
echo ""
echo "Press enter to Continue"
read temp
main_menu
}

forms ()
{
 #Query the built in Database for Database Information

DBROOT="Databases" TABLE="Forms" "$CLAIMS_PROGS"/bin/read_records_claims.sh -query "$SELECTED_DATABASE_ID" 1 > /tmp/DBLISTING

echo "Form Selection"
echo "------------------"
echo ""
echo ""
echo "Please select the form you wish to open"
echo ""
awk -F\, '{print $3}' /tmp/DBLISTING > /tmp/DBLISTING2
awk -F\, '{print $2}' /tmp/DBLISTING > /tmp/DBLISTING2_ID
# Grab Line by line

rm -f /tmp/DBLISTING3 2> /dev/null

# Find the Database Names
for a in $( cat /tmp/DBLISTING2 );
do

LINE_NOS=$( cat /tmp/DBLISTING2 | grep -nw "$a" )
echo -n "=" >> /tmp/DBLISTING3
echo "$LINE_NOS" >> /tmp/DBLISTING3


done


# Now match those to their ID's
for a in $( cat /tmp/DBLISTING3 );
do


LINE_NUMBER=$( echo "$a" | sed -n 's/^.*=\([^:]*\).*$/\1/p' )
sed -n $LINE_NUMBER'p' /tmp/DBLISTING2_ID > /tmp/DBLISTING4

# Grab the ID
ID_ID=$( cat /tmp/DBLISTING4 )
ID_NAME=$(sed -n "$LINE_NUMBER"p /tmp/DBLISTING2 )

echo "$ID_ID $ID_NAME" >> /tmp/DBLISTING5


done



sed s/\"//g /tmp/DBLISTING5 > /tmp/DBLISTING

cat /tmp/DBLISTING

rm -f /tmp/DBLISTING*
echo ""
echo "Enter the Form ID"
echo ""
read DBCHOICE

# Convert back to the name
DBROOT="Databases" TABLE="Forms" "$CLAIMS_PROGS"/bin/read_records_claims.sh -query "$DBCHOICE" 2 > /tmp/DBLISTING
SELECTED_TABLE=$( awk -F\, '{print $3}' /tmp/DBLISTING | sed s/\"//g )
SELECTED_TABLE_ID="$DBCHOICE"



source "$CLAIMS_PROGS"/db/"$SELECTED_DATABASE"/Forms/"$SELECTED_TABLE"

echo "Press Enter to Continue"
read temp
main_menu



}

main_menu ()
{
clear
echo "                   Claims_DB CLI Front End     "
echo ""
echo ""
echo "Selected Database: $SELECTED_DATABASE"
echo ""
echo "Main Menu"
echo "---------"
echo ""
echo "1) Select Database"
echo "2) Tables "
echo "3) Queries"
echo "4) Forms "
echo "5) Reports "
# Insert rest of menu when implemented

echo "q) Quit"

read MAINMENUCHOICE


if [ "$MAINMENUCHOICE" == "q" ]
then
clear
exit
fi

if [ "$MAINMENUCHOICE" == "1" ]
then
clear
select_db
fi

if [ "$MAINMENUCHOICE" == "2" ]
then
clear
select_Table
fi

if [ "$MAINMENUCHOICE" == "3" ]
then
select_Query
fi

if [ "$MAINMENUCHOICE" == "4" ]
then
forms
fi

if [ "$MAINMENUCHOICE" == "5" ]
then
Reports
fi



echo "Invalid Choice"
sleep 2
main_menu
}








main_menu