Hi,
I'm having problems with a script which some of you helped me with last week. It's a script to check the status of orders in an SQL database, and if the count(*) is zero, then the script stops. If it's non-zero, the script echos the result to a file and then in cron, I cat the file and mail it.
First thing first, this script is pig-ugly with all of the echos in there, so I need to tidy that up, but more important is the fact that the second part - from where I've inserted ## second part -- just constantly returns the same order code.
Oh, and it loops.
What a mess.
Can someone help me ?
Thanks.
#!/bin/sh
## get date
today=`date +"%d-%m-%Y-%H-%S"`
if [ -f /root/extrac.txt ]; then
/bin/rm /root/extrac.txt
fi
#### check orders
orderStatus=$(/usr/bin/mysql -N -B mybase -e '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 != "wotsit" AND u.p_username NOT LIKE "e-mail_address@domain.co.uk";')
/bin/echo "" > /root/extrac.txt
/bin/echo "The date is $today" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "We have verified the number of orders still in 'state' state" >> /root/extrac.txt
/bin/echo "after four hours." >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
if [ $orderStatus != "0" ]; then
/bin/echo "" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "The number of orders in this state is not zero." >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "As such, the web order numbers are echoed below : " >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
## second part
orderStatus=$(/usr/bin/mysql -N -B mybase -e '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 != "wotsit" AND u.p_username NOT LIKE "e-mail_address@domain.co.uk";')
while [ $result ]
do
/bin/echo "" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "$result" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
done
exit 0
fi
/bin/echo "" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "The number of orders is zero, and as such, no web order numbers are available" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "Any questions, please e-mail me@myaddress.com" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "-------------------------------------------------------------" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
Yeah, it's a complete mess, isn't it? The problem stems from the fact that I could do this in PhP, but it's a production server and they don't want PhP running on it, and so I have to do it in bash, and have never created arrays (which I think is what's required) in bash.
So here is what I hope is a tidier version - especially devoid of those ugly echo statements.
I think it starts to come unstuck at the first line which executes the first SQL query (the red line). If it comes back with 25 results, then what I want in the evaluation (the magenta line) is basically to say ..
if the first query returns anything other than zero lines, then do this. But I can't figure out which variable to use. ?#, ## and so on .. none works.
#!/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
Okay, you first need to assess whether or not the mysql query went OK or not. Either "set -e" at the start of the script or deal with it when you run the query.
My MySQL is rusty but I checked and you can't run queries by just appending the query string - I get errors (which end up in my variable) when I do that. In stead, do something like:
Then instead of checking for the number of results, check if the string is empty.
You might also want to do a MySQL count statement (as opposed to a query) which may also be much better if the database is large. Additionally, when I do queries like that all my results end up on one line - you might want to append "limit 1" to your query to get just one result.