Syed Jahanzaib Personal Blog to Share Knowledge !

February 9, 2016

Sending SMS/Email Alert For Reseller Account Renewal/Deposit

Filed under: Linux Related, Radius Manager — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 1:04 PM

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 …

1 - email alert
2- mobile


Regard's
Syed Jahanzaib

August 20, 2015

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

Filed under: Linux Related, Radius Manager — Tags: , , , , , — Syed Jahanzaib / Pinochio~:) @ 11:01 AM

This post is my personnel 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 …

 

1

 

2

1

 


 

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.]

#!/bin/bash
# Script to query all re-seller's account for yesterday's sale and there balances. 
# and at end, email the results to admin in html format .
# Created by SYED JAHANZAIBB - aacable @ hotmail . com / 0333-3021-909
# last updated: 25/08/2015
#!/bin/bash

#set -x
clear
# MYSQL USER ID PASSWORD
SQLUSER="root"
SQLPASS="PASSWORD

# DATE RELATED STUFF
TODAY=`date +"%Y-%m-%d"`
YESTERDAY=`date +"%Y-%m-%d" -d '-1 days'`
CURDATE=`date`

# EMAIL RELATED STUFF
TO1="aacable@hotmail.com"
GMAILID="YOURGMAILID@gmail.com"
GMAILPASS="GMAIL-PASSWORD"
CONTENT_TYPE="text/html"

# LOG FILES
FILE="/tmp/dealer_renewal_today.html"
FINALFILE="/tmp/dealer_renewal_today_final.html"
CSHORT="ZAIB-COMPANY"
COMPANY="$CSHORT_Pvt_Ltd.<br>This System is powered by Syed_Jahanzaib aacable@hotmail.com"
BODY_TITLE="<h1>Report&nbsp;For&nbsp;Dealer&nbsp;Account&nbsp;asof&nbsp;$YESTERDAY</h1>"


> $FILE
> $FINALFILE
> /tmp/temp.txt
echo "<pre>" > $FILE
echo "<b>$BODY_TITLE</b>" >> $FILE
echo "<b>DEALER&nbsp;            User's_Activated             Used_Amount             &Tab;Balance</b><br>" >> $FILE

# QUERY MANAGERS FROM RM_MANAGERS TABLE
mysql -u$SQLUSER -p$SQLPASS --skip-column-names  -e "use radius; select managername from rm_managers;" | while read dealer
do
num=$[$num+1]
DEALER=`echo $dealer | awk '{print $1}'`

# GATHER DATA OF ACTIVE USERS, USED AMOUNT, CURRENT BALANCE, (MOBILE NUMBER IF SMS IS REQUIRED TO SEND)
ACTIVEUSERSNO=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.managername, rm_invoices.username, rm_invoices.date, rm_invoices.expiration, rm_invoices.service, rm_invoices.amount, rm_invoices.price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$YESTERDAY' AND date <= '$TODAY' AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0'  OR invgroup = '1' ) AND invnum != '' AND rm_invoices.managername = '$DEALER' ORDER BY id LIMIT 0, 500;" | sed '/credited/d' | wc -l`
USEDAMOUNT=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.price, rm_invoices.id, rm_invoices.invnum, rm_invoices.managername, rm_invoices.username, rm_invoices.date, rm_invoices.bytesdl, rm_invoices.bytesul, rm_invoices.bytescomb, rm_invoices.downlimit, rm_invoices.uplimit, rm_invoices.comblimit, rm_invoices.time, rm_invoices.uptimelimit, rm_invoices.days, rm_invoices.expiration, rm_invoices.comment, rm_invoices.service, rm_invoices.amount, rm_invoices.paid, rm_invoices.paymentopt, rm_invoices.paymode, rm_invoices.tax, rm_invoices.balance, rm_invoices.invgroup FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$YESTERDAY' AND date <= '$TODAY' AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0'  OR invgroup = '1' )  AND invnum != '' AND rm_invoices.managername = '$DEALER'  ORDER BY id  LIMIT 0, 500;" | sed '/credited/d' | awk '{ sum+=$1} END {print sum}'`
BALANCE=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names  -e "use radius; select balance from rm_managers WHERE managername = '$DEALER';" | sed '/credited/d' |cut -f1 -d"."`
MOBILE=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names  -e "use radius; select mobile from rm_managers WHERE managername = '$DEALER';"`
SRV=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.service FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$YESTERDAY' AND date <= '$TODAY'  AND  rm_invoices.managername = '$DEALER' AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0' ) AND invnum != ''  ORDER BY id LIMIT 0, 50;" | sed '/credited/d' | awk '{print $1}' | sort | uniq -c`




#LOOK FOR ZERO VALUE AMOUNT AND REPLACE IT WITH 0 , IF FOUND
if [ ! -n "$USEDAMOUNT" ]; then
#if [ "USEDAMOUNT  == "" ]; then
USEDAMOUNT="X"

# PRINT ALL GATHERED DATA INTO FILE
echo "<b>$DEALER</b>  $ACTIVEUSERSNO  $USEDAMOUNT  &Tab;$BALANCE
------------------------------------------------------------------------"  >> $FILE
else

# PRINT ALL GATHERED DATA INTO FILE
echo "<b>$DEALER</b>  $ACTIVEUSERSNO  $USEDAMOUNT  &Tab;$BALANCE
<br>
Details&nbsp;of&nbsp;Services&nbsp;Activated:<br>Qty&Tab;Service&nbsp;Name<br>
$SRV
<br>------------------------------------------------------------------------" >> $FILE

fi
done

# MAKE COLUMNS SO THAT IT GETs EASIER TO READS
sed -e 's/\t//g' $FILE |  column -t | sed 's/                         //g' | sed 's/    User/User/g'  > $FINALFILE

# GATHER DATA OF ACTIVE USERS, USED AMOUNT, CURRENT BALANCE, (MOBILE NUMBER IF SMS IS REQUIRED TO SEND)
TOTNO=`mysql -uroot -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.service FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$YESTERDAY' AND date <= '$TODAY'  AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0' ) AND invnum != ''  ORDER BY id LIMIT 0, 50;" | sed '/credited/d' | awk '{print $1}' | wc -l`
SALES=`mysql -uroot -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$YESTERDAY' AND date <= '$TODAY'  AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0' ) AND invnum != ''  ORDER BY id LIMIT 0, 50;" | awk '{ sum+=$1} END {print sum}'`
echo "Total Users Activated/Renewed on $YESTERDAY     = <b>$TOTNO</b>" >> $FINALFILE
echo "Total SALES Done on $YESTERDAY                  = <b>$SALES</b>" >> $FINALFILE
echo "<br><b>$COMPANY</b>" >> $FINALFILE
echo "Generated on $CURDATE" >> $FINALFILE
echo "</pre>" >> $FINALFILE

##Finally send email with all the data gathered USING SEND_EMAIL TOOL
/temp/sendEmail-v1.56/sendEmail -t $TO1 -u "INFO: $CSHORT DEALERS DAILY BILLING INFO for $YESTERDAY" -o tls=yes -s smtp.gmail.com:587 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$FINALFILE  -o message-content-type=$CONTENT_TYPE

# Print and copy files as sales.html into www folder so any1 can view from web browser
cat $FINALFILE
cp $FINALFILE /var/www/sales.html

 

Install sendEmail Tool

mkdir /temp
cd /temp
wget http://caspian.dotconf.net/menu/Software/SendEmail/sendEmail-v1.56.tar.gz
tar zxvf sendEmail-v1.56.tar.gz
cd sendEmail-v1.56/

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

%d bloggers like this: