Skip to content

Instantly share code, notes, and snippets.

@ormaaj
Forked from anonymous/gist:5926435
Last active Dec 19, 2015
Embed
What would you like to do?
modified for sw
#!/bin/bash
${BASH_VERSION+shopt -s extglob lastpipe} 2>/dev/null
# Full path to a file to store our date offset data. Will be overwritten.
datefile=./datefile
# Assign your vars or insert whatever code does so here.
user=foouser password=mypassword db=mydbname
function doit {
typeset x
for x; do
if [[ -z $x ]]; then
echo 'doit: missing an argument.' >&2
return 1
fi
done
typeset template=$(</dev/fd/4)
exec 4<&-
sqlplus "${1}/${2}@${3}" <&3 | {
if [[ -f $datefile ]]; then
. "$datefile" || return 1
else
# An associative array that maps from num -> timestamp
typeset -A msgData
fi
# If we've successfully read the file containing timestamps, then overwrite with new data on RETURN
${msgData+trap 'trap RETURN; typeset -p msgData >"$datefile"' RETURN}
while IFS=, read -r num email limit orders; do
${email:+:} continue
if (( ${msgData[$email]:-0} < $(LC_TIME=C date -d yesterday +%s) )); then
printf -- "$template" "email" "$num" "$limit" "$orders" |
/path/to/sendmail -f sender@example.com -oi -t
msgData[$email]=$(LC_TIME=C date +%s)
else
printf 'Mail already sent to %s within the last 24 hours... skipping.\n' "$email" >&2
fi
done
}
} 5<&0 <<\SQL 3<&0 <<\TEMPLATE 4<&0 <&5-
set pagesize 0
set feedback 0
SELECT kred_lim.kunr ||','|| kust_adr.ku_email ||','|| kred_lim.kred_limit ||','|| kred_lim.kred_zu_zahlen
FROM kred_lim, kust_adr
WHERE kred_lim.kred_zu_zahlen > kred_lim.kred_limit
AND kred_lim.kunr = kust_adr.ku_nr;
SQL
Subject: Credit limit
To: %s
Customer number: %s
Credit limit: %s
Current orders: %s
TEMPLATE
if ! doit "$user" "$pass" "$db"; then
echo 'we failed :(' >&2
exit 1
fi
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment