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

Re-seller Daily Sales Activity Report Via Email in Billing System

This post is my personal notes (for future retrieval or reference) on a script that can be used to query billing system (in this example Radius Manager) and gather data for all re-seller’s yesterday sales activity and summarize it in a file and email it to Administrator. It comes handy to get idea which dealer made how much sale with number of activated users, sale amount, balance and summarize it in the end for admin view.

As showed in the image below …

---

SCRIPT

dealer_renewal_yesterday.sh

• mkdir /temp
• touch /temp/dealer_renewal_yesterday.sh
• chmod +x /temp/dealer_renewal_yesterday.sh
• nano /temp/dealer_renewal_yesterday.sh

Paste the following data [but do make sure you modify the data like id password or other before deploying it.]

As wordpress is not letting my copy paste the full code properly, therefore I have updated on my google drive, check it here

https://drive.google.com/drive/u/0/folders/1BRIvT6lr9s66nzPP2tRsBV6G-0YA-Zkw

---

Install sendEmail Tool

apt-get -y install libio-socket-ssl-perl libnet-ssleay-perl perl
apt-get -y install sendemail

ADD SUPPORTING LIBRARY
For UBUNTU [Life is really easy on ubuntu but with some glitches)
apt-get -y install libio-socket-ssl-perl libnet-ssleay-perl perl
For CENTOS
yum -y install perl perl-Crypt-SSLeay perl-IO-Socket-SSL
TEST SENDING EMAIL
Try to send email using command line: Example
/temp/sendEmail-v1.56/sendEmail -t TO_YOURMAIL@hotmail.com -u "Test Email" -s smtp.gmail.com:587 -xu YOURMGAILID@gmail.com -xp YOURGMAILPASSWORD -f  YOURMGAILIDgmail.com -o tls=yes
If you get message something like “sendEmail[xxxx]: Email was sent successfully!”, then you are good to GO LIVE !

Regard’s
Syed Jahanzaib