Requirement: A script that will run every morning which will connect to Mysql database and run the query to inform us about the holidays (it will also check if there were any holidays during last 2 business days). So the three queries are required to run.
for now, I have already written the script which sends the output over email in tabular format, however, I am unable to provide newline in between the two tabular outputs. Following is the script so far,
#!/bin/bash
mysql test -e "select c.CenterID,a.MIC,c.ExchangeName,a.CountryCode,a.EventDate,a.EventDayOfWeek,b.eventName from exch_calendar_event a left join exch_calendar_eventName b on a.EventID=b.ID left join exch_ca lendar_mic c on a.MIC=c.MIC where EventDate=DATE_FORMAT(20170811,'%Y%m%d') order by a.MIC;" | tr "\t" "~" > file.tmp
if [ -s file.tmp ]
then
echo "<table border=1>" > mail.txt
while read -r LINE
do
LINE=$(echo $LINE|sed 's/~/<\/td><td>/g')
echo "<tr><td>$LINE</td></tr>" >> mail.txt
done < file.tmp
echo "</table>" >> mail.txt
fi
mysql test -e "select c.CenterID,a.MIC,c.ExchangeName,a.CountryCode,a.EventDate,a.EventDayOfWeek,b.eventName from exch_calendar_event a left join exch_calendar_eventName b on a.EventID=b.ID left join exch_ca lendar_mic c on a.MIC=c.MIC where EventDate=DATE_FORMAT(20170831,'%Y%m%d') order by a.MIC;" | tr "\t" "~" >> file1.tmp
if [ -s file1.tmp ]
then
echo "<table border=1>" >> mail1.txt
while read -r LINE
do
LINE=$(echo $LINE|sed 's/~/<\/td><td>/g')
echo "<tr><td>$LINE</td></tr>" >> mail1.txt
done < file1.tmp
echo "</table>" >> mail1.txt
fi
cat header1 mail.txt mail1.txt footer | sendmail -t
rm -f file.tmp mail.txt file1.tmp mail1.txt
I am receiving the desired output, however, I need some blanklines to be separated from 2 query outputs.
Thanks
---------- Post updated at 05:11 AM ---------- Previous update was at 04:57 AM ----------
Ok, now it is sorted out. I have used the <br> tag.
You might consider using "plain text" as your mail format instead of HTML. This will make your mails smaller and less dangerous to handle (all the fancy Java-, Javascript- and whatnot-stuff used to install things you might not want to have installed will not work on plain text).
Increase efficiency.
Consider redirection in larger portions.
if [ -s file.tmp ]
then
echo "<table border=1>" > mail.txt
while read -r LINE
do
LINE=$(echo "$LINE"|sed 's/~/<\/td><td>/g')
echo "<tr><td>$LINE</td></tr>"
done < file.tmp >> mail.txt
echo "</table>" >> mail.txt
fi
Or even better
if [ -s file.tmp ]
then
{
echo "<table border=1>"
while read -r LINE
do
LINE=$(echo "$LINE"|sed 's/~/<\/td><td>/g')
echo "<tr><td>$LINE</td></tr>"
done < file.tmp
echo "</table>"
} > mail.txt
fi
Maybe you can save the loop and do everything in sed using its built-in loop?
if [ -s file.tmp ]
then
{
echo "<table border=1>"
sed '
s#~#</td><td>#g
s#^#<tr><td>#; s#$#</td></tr>#
'
echo "</table>"
} < file.tmp > mail.txt
fi
Once we start optimizing / improving the script, why not put everything together into one single loop:
DT=(20170811 20170831)
for i in 0 1
do echo "<table border=1>"
mysql test -e "select c.CenterID,a.MIC,c.ExchangeName,a.CountryCode,a.EventDate,a.EventDayOfWeek,b.eventName from exch_calendar_event a left join exch_calendar_eventName b on a.EventID=b.ID left join exch_calendar_mic c on a.MIC=c.MIC where EventDate=DATE_FORMAT(${DT[$i]},'%Y%m%d') order by a.MIC;" |
while read LINE
do echo "<tr><td>${LINE// /<\/td><td>}</td></tr>"
done
echo "</table><br>"
done | sendmail -t
Yes, looping this will be good, however, in the output I am getting one extra <\/td> after every entry. O/p in the email attached: Below is the code:
#!/bin/bash
DT=(20170811 20170831)
for i in 0 1
do echo "<table border=1>"
mysql test -e "select c.CenterID,a.MIC,c.ExchangeName,a.CountryCode,a.EventDate,a.EventDayOfWeek,b.eventName from exch_calendar_event a left join exch_calendar_eventName b on a.EventID=b.ID left join exch_calendar_mic c on a.MIC=c.MIC where EventDate=DATE_FORMAT(${DT[$i]},'%Y%m%d') order by a.MIC;" |
while read LINE
do echo "<tr><td>${LINE// /<\/td><td>}</td></tr>"
done
echo "</table><br>"
done >> mail.txt
cat header1 mail.txt footer | sendmail -t
Also, I could not understand the ${LINE// /<\/td><td>} part. Is it replacing the tab with </td><td>.
Please let me know. Once the output is sorted out, I need to optimize the script to run everyday which will fetch the data for today, t-1 and t-2 (provided all are business days).
For eg: If today is Tuesday, then the query will be executed 3 times for today, Monday, friday and send the output (if any) over email. No blank emails required.
Currently, I am just checking the output format with hardcoding the date, however, above is the overall requirement of the script.
---------- Post updated 09-04-17 at 02:51 AM ---------- Previous update was 09-03-17 at 12:34 PM ----------
Hi,
So now Ia m getting the output as desired in the email. Below is the test script to check the format of the output over email:
#!/bin/bash
DT=(20170811 20170831)
for i in 0 1
do
#echo "<table border=1>"
mysql test -e "select c.CenterID,a.MIC,c.ExchangeName,a.CountryCode,a.EventDate,a.EventDayOfWeek,b.eventName from exch_calendar_event a left join exch_calendar_eventName b on a.EventID=b.ID left join exch_calendar_mic c on a.MIC=c.MIC where EventDate=DATE_FORMAT(${DT[$i]},'%Y%m%d') order by a.MIC;" | tr "\t" "~" > file.tmp
if [ -s file.tmp ]
then
{
echo "<table border=1>"
while read -r LINE
do
LINE=$(echo $LINE|sed 's/~/<\/td><td>/g')
echo "<tr><td>$LINE</td></tr>"
done < file.tmp
echo "</table><br><br>"
} >> mail.txt
fi
done
cat header1 mail.txt footer | sendmail -t
And, following is the content in the mail.txt after execution of the script:
I tried t search, however, I am unable to find any relevant information about how to consider the business days only (mon-fri). I just need to run the query for last 3 business days (T, T-1, T-2). So we can this format in variables and use this in loop.
following is the code till now, where the hard coded dates are used to test the script, however, the script will be running everyday which will check, if today or T-1 or T-2 is a holiday. Note here that, this need to be checked for only business days.
#!/bin/bash
DT=(20170811 20170831)
for i in 0 1
do
#echo "<table border=1>"
mysql test -e "select c.CenterID,a.MIC,c.ExchangeName,a.CountryCode,a.EventDate,a.EventDayOfWeek,b.eventName from exch_calendar_event a left join exch_calendar_eventName b on a.EventID=b.ID left join exch_calendar_mic c on a.MIC=c.MIC where EventDate=DATE_FORMAT(${DT[$i]},'%Y%m%d') order by a.MIC;" | tr "\t" "~" > file.tmp
if [ -s file.tmp ]
then
{
echo "<table border=1>"
while read -r LINE
do
LINE=$(echo $LINE|sed 's/~/<\/td><td>/g')
echo "<tr><td>$LINE</td></tr>"
done < file.tmp
echo "</table><br><br>"
} >> mail.txt
fi
done
cat header1 mail.txt footer | sendmail -t
OK.. I sorted it out by myself. Below is the script:
day_of_week=`date +%w`
if [ `expr $day_of_week` == 1 ]
then
today=`date +'%Y%m%d'`
yes=`date -d "3 day ago" +'%Y%m%d'`
dfy=`date -d "4 day ago" +'%Y%m%d'`
elif [ `expr $day_of_week - 1` == 1 ]
then
today=`date +'%Y%m%d'`
yes=`date -d "1 day ago" +'%Y%m%d'`
dfy=`date -d "4 day ago" +'%Y%m%d'`
else
today=`date +'%Y%m%d'`
yes=`date -d "1 day ago" +'%Y%m%d'`
dfy=`date -d "2 day ago" +'%Y%m%d'`
fi
#echo $today
#echo $yes
#echo $dfy
DT=($today $yes $dfy)
SEND=0
for i in 0 1 2
do
mysql test -e "select c.CenterID,a.MIC,c.ExchangeName,a.CountryCode,a.EventDate,a.EventDayOfWeek,b.eventName from exch_calendar_event a left join exch_calendar_eventName b on a.EventID=b.ID left join exch_calendar_mic c on a.MIC=c.MIC where EventDate=DATE_FORMAT(${DT[$i]},'%Y%m%d') order by a.MIC;" | tr "\t" "~" > file.tmp
if [ -s file.tmp ]
then
{
SEND=1
echo "<br><b>T-$i (${DT[$i]})"
echo "<table border=1>"
while read -r LINE
do
LINE=$(echo $LINE|sed 's/~/<\/td><td>/g')
echo "<tr><td>$LINE</td></tr>"
done < file.tmp
echo "</table><br>"
} >> mail.txt
fi
done
[[ $SEND == 1 ]] && cat header1 mail.txt footer | sendmail -t
rm -f mail.txt file.tmp
Holidays are dependent on the location. I have no idea how to generally get them.
Regarding the redirection, it seems to make sense to make them even more global, redirecting the whole for-do-done block.
Then the artificial { } block can be omitted, and you can simply > (overwrite) the output file.
#!/bin/bash
DT=(20170811 20170831)
for i in 0 1
do
#echo "<table border=1>"
mysql test -e "select c.CenterID,a.MIC,c.ExchangeName,a.CountryCode,a.EventDate,a.EventDayOfWeek,b.eventName from exch_calendar_event a left join exch_calendar_eventName b on a.EventID=b.ID left join exch_calendar_mic c on a.MIC=c.MIC where EventDate=DATE_FORMAT(${DT[$i]},'%Y%m%d') order by a.MIC;" | tr "\t" "~" > file.tmp
if [ -s file.tmp ]
then
echo "<table border=1>"
while read -r LINE
do
LINE=$(echo $LINE|sed 's/~/<\/td><td>/g')
echo "<tr><td>$LINE</td></tr>"
done < file.tmp
echo "</table><br><br>"
fi
done > mail.txt
cat header1 mail.txt footer | sendmail -t