Sending SMS/Email Alert For Reseller Account Renewal/Deposit

Following script is used to send SMS and Email alert to reseller and admin about the renewal or deposit in RE-SELLER account in radius manager.

Components used in the script:

• Ubuntu 12.4 32bit
• Kannel as SMS Gateway installed on radius manager server
• Teltonika COM10 Serial Modem with local mobile operator SIM
• sendEmail utility for sending Email

 

---

Note: Few other related guides.

For Kannel Installation
https://aacable.wordpress.com/2012/11/26/howto-configure-your-local-http-gateway-using-kannel-on-ubuntu/

for sendEmail Utility
https://aacable.wordpress.com/2015/11/26/bash-scheduled-script-to-check-linux-service-status-and-smsemail-while-preventing-repeated-alerts/

 

---

 

 

Following script is schedule to run after every 5 minutes , if it found any reseller account renewal, it will send SMS to admin and reseller plus email to admin as well , just for record purposes.

#!/bin/sh
#set -x
# BASH base SMS/EMAIL script for RESELLER ACCOUNT RENEWAL nottification for RADIUS MANAGER based on Freeradius/mysql
# the simple logic can be applied for about any other task as well. I tried to make it as simple as it can be
# By Syed Jahanzaib
# CREATED on : 17th August, 2015
# Last Updated : 9-Aug-2016 / remove Balance showing time bug
SQLUSER="SQLUSER"
SQLPASS="SQLPASS"
MNT="5"
CURDATE=$(date +"%Y-%m-%d")
TIME=$(date +"%T")
COMPANY="ZABBO Company"
TO1="YOUREMAIL@hotmail.com"
FROM="YOURGMAIL@gmail.com"
GMAILPASS="GMAILPASS"
MSG="/tmp/dealer_renewal.html"
MSG2="/tmp/dealer_renewal_final.html"
CONTENT_TYPE="text"
KPASS="kannelpass"

# SMS
cell1="03333021909"


# Admin Email on which dealer renewal mail will be sent"
# Empty Previous TEMP File
> $MSG

# Add HTML TAG to preserve TAB etc

# Check User Validation, if not found exit with error , else continue
USRVALID=`mysql -u$SQLUSER -p$SQLPASS  -e  "use radius; SELECT managername, username, price, date, service from rm_invoices WHERE date >= NOW() - INTERVAL $MNT MINUTE;" |grep Reseller`
if [ ! -n "$USRVALID" ]; then
echo  "INFO: No RESELLER account have been updated in last $MNT minutes ! $COMPANY"

# Add entry in SYSLOG
#logger "INFO: No RESELLER account have been updated in last $MNT minutes ! $COMPANY"
exit 0
fi

# Fetch user account details which were created in last 5 minutes by catchign description name Reseller from rm tables in a temp file which loop will use later, by jahanzaib
mysql -u$SQLUSER -p$SQLPASS  -e  "use radius; SELECT managername, username, price, balance, mobile, service from rm_invoices WHERE date >= NOW() - INTERVAL $MNT MINUTE;" |grep Reseller > /tmp/temp

# Apply Count Loop Formula
num=0
cat /tmp/temp | while read users
do
num=$[$num+1]
mgrname=`echo $users | awk '{print $1}'`
dealer=`echo $users | awk '{print $2}'`
price=`echo $users | awk '{print $3}' |cut -f1 -d"."`
balance=`echo $users | awk '{print $4}' |cut -f1 -d"."`
mobile=`echo $users | awk '{print $5}'`
comment=`echo $users | awk '{print $6}'`

echo "Galaxy Info: Dealer Account Renewed.
Date/Time = $CURDATE $TIME
Dealer = $dealer
Amount Added = $price Rs
New Balance = $balance Rs
Added By = $mgrname
$COMPANY.
=========" >> $MSG

# Print information for screening purposes
echo "Galaxy Info: Dealer Account Renewed.
Date/Time = $CURDATE $TIME
Dealer = $dealer
Mobile = $mobile
Amount Added = $price Rs
New Balance = $balance Rs
Added By = $mgrname
$COMPANY.
========="

# Finally Send Email, if required
#/temp/sendEmail-v1.56/sendEmail -t $TO1 -u "$CURDATE / GT Billing INFO on Dealer Account Renewal" -o tls=yes -s smtp.gmail.com:587 -xu $FROM -xp $GMAILPASS -f $FROM -o message-file=$MSG  -o message-content-type=$CONTENT_TYPE
#/temp/sendEmail-v1.56/sendEmail -t $TO2 -u "$CURDATE / GT Billing INFO on Dealer Account Renewal" -o tls=yes -s smtp.gmail.com:587 -xu $FROM -xp $GMAILPASS -f $FROM -o message-file=$MSG  -o message-content-type=$CONTENT_TYPE

# Finally Send SMS if required, enabled right now
cat $MSG | curl "http://localhost:13013/cgi-bin/sendsms?username=kannel&password=$KPASS&to=$mobile+$cell1" -G --data-urlencode text@-

done

# Script Ends Here.
# Syed Jahanzaib / aacable at hotmail dot com
 

Results are as follows …

---

Regard's
Syed Jahanzaib