#!/bin/sh

########################################################
#                                                      #
#                       weborders.sh                   #
#                          v. 0.1                      #
#                                                      #
#                   Copyright (C) 2013                 #
#                                                      #
#                                                      #
########################################################



###
# define some important variables 
###

today=`date +"%d-%m-%Y"`
ttime=`date +"%H:%S"`
workingDir="/root"
outPut="$workingDir/webexecOutput.txt"
mysqlExec="/usr/bin/mysql"
database="mydatabase"
query1="SELECT COUNT(*) FROM orders as o JOIN users as u ON o.userpk=u.pk WHERE o.createdts BETWEEN (now() - INTERVAL 1 day) AND (now() - INTERVAL 4 hour) AND o.p_omsstate=1 AND u.name != \"Site Confidence\" AND u.p_username NOT LIKE \"andy.smith@mydatabase.co.uk\";"
query2="SELECT Code FROM orders as o JOIN users as u ON o.userpk=u.pk WHERE o.createdts BETWEEN (now() - INTERVAL 1 day) AND (now() - INTERVAL 4 hour) AND o.p_omsstate=1 AND u.name != \"Site Confidence\" AND u.p_username NOT LIKE \"andy.smith@mydatabase.co.uk \";"

###
# does the output file exist already?  If so, hose it
###

if [ -f $outPut ]; then
   /bin/rm -f $outPut
fi

### 
# let's run the first query 
###

result1=$($mysqlExec --skip-column-names -B $database $query1)

###
# set up beginning of file
# this will be echoed whatever the result of the script
###

/bin/cat << EOF > $outPut
Hello,

This is the weborders script on database.

The date is $today.  It is $ttime GMT. 

We have run the query on the mydatabase database.


EOF

###
# this is the first 'if' clause
# this will be run ONLY if the result of $result1 <> 0
###

if [ $numberResults neq "0" ]; then
	/bin/cat << EOF >> $outPut
There is a non-zero result, and the weborder numbers are echoed below.

EOF
	result2=$($mysqlExec --skip-column-names -B $database $query2)
	for x in "${result2[@]}"; do
		/bin/echo ${x} >> $outPut
	done
	/bin/cat << EOF >> $outPut
If you have any questions, please e-mail myemailaddress@anonexistentserver.com

EOF

fi
exit 0 

/cat/echo << EOF >> $outPut
There are no orders in a 'submitted' state or corresponding to the 
criteria outlined in the LLS.  As such, the script is exiting.

If you have any questions, please e-mail myemailaddress@anonexistentserver.com.

EOF