Syed Jahanzaib Personal Blog to Share Knowledge !

June 18, 2015

Freeradius/mysql Account Expiry SMS notification Script using ‘itelservices.net’ bulk SMS Gateway

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

sms

This post is somewhat very specific to PK base bulk sms provider API. Its a simple bash script made on someone’s request [who had a custom billing system based on freeeradius/mysql] and it can be used to send account expiry notifications to users using freeradius/mysql account query  , BUT specifically using HTTP base SMS Gateway services from http://itelservices.net/

However this specific SMS gateway was a bit different as compared to our KANNEL base gw.

  1. It requires ‘Unique transaction ID’ for each sms, therefore i used current time/seconds with username as Transaction ID
  2. The number should be in international format like 923333021909 and the problem was that the operator had simple format for mobile numbers like 03333021909 is all accounts, and it was not acceptable from the API provider, therefore as a workaround, I used awk/sed tools to remove 0 and then in curl added 92 before every number.

At the moment there are two scripts

1- SMS for account expiry notification
2- SMS for new account creation with user details if possible

You must modify the script as required. This is just a simple way to achieve this task, however there are more sophisticated method like using php or other programing language, I just prefer to select the BASH route !

 

Posting it for   H U M A S   as I love them, They’re Amazing ! 🙂


1- SMS for account expiry notification

 

mkdir /temp
touch /temp/sms.sh
chmod +x /temp/sms.sh
nano /temp/sms.sh

Now paste the following code.

#!/bin/sh
# set -x
# BASH base SMS script for sending expiry notification for Freeradius/mysql users
# 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 : 8th June, 2015
# Modified on : 18th june, 2015
# This script was specially modified for APITEL http sms gateway services
# which requires unique transaction ID each time, so i used datetimesecond feature as jugaar
# made for KHI

# MYSQL root id and password
SQLUSER="root"
SQLPASS="sqlpass"
DB="radiusdb"

# APITEL User Name & Password, must be filled
APIUSER="xxxx"
APIPASS="xxxx"
API="YOURSENDERNAME"

# Date functions to find current date, month year and Transaction id using seconds ; ) jugaar way ; )
NOW=$(date)
TID=$(date +"-%s")

# Interval before alert which should be sent to user before this number days
EXPIRY=3

# Export usernames and mobile from the mysql table in a file,  which Expiry is after 3 days
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; SELECT login,mobile FROM users WHERE expirydate = DATE_ADD(CURDATE(), INTERVAL $EXPIRY DAY);"
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; SELECT login,mobile FROM users WHERE expirydate = DATE_ADD(CURDATE(), INTERVAL $EXPIRY DAY);" > /tmp/list

# Remove 0 if any in mobile number and export it to final list
cat /tmp/list | awk '{gsub("^0","",$2); print $1,$2}' > /tmp/finallist

# Add DATE TIME in sms.log to separate date wise entries / zaib
echo ====================================================== >> /var/log/sms.log
echo $NOW >> /var/log/sms.log
echo ====================================================== >> /var/log/sms.log

# Add DATE TIME in smsapi.log to separate date wise entries WITH API STATUS for cross verification / zaib
echo ====================================================== >> /var/log/smsapi.log
echo $NOW >> /var/log/smsapi.log
echo ====================================================== >> /var/log/smsapi.log

# Apply Count Loop Formula while deleting first line which have simple text, and also any line which dont have mobile number [in second column]
num=0
cat /tmp/finallist |sed '1d' |awk 'NF > 1' | while read users
do
num=$[$num+1]
username=`echo $users |awk '{print $1}'`
mobile=`echo $users | awk '{print $2}'`

# SMS Body
BODY="Soft+Reminder:+Dear+$username,+Your+Internet+Service++Will+Expire+after+$EXPIRY+days++++zaibisp"

echo "$NOW ! Expiry Notification have been sent to $username, on cell number 0$mobile"
echo "$NOW ! Expiry Notification have been sent to $username, on cell number 0$mobile" >> /var/log/sms.log

# Add action like send sms or email as per required or designed / zaib
# Sending sms via APITEL API SMS Gatewy / syed jahanzaib / aacable@hotmail.com

curl "http://api1.itelservices.net/send.php?transaction_id=$TID$username&user=$APIUSER&pass=$APIPASS?&number=%2B92$mobile&text=$BODY&from=$API" >> /tmp/smsapi.log
done

sed 's/\(Status\)/\n\1/g' /tmp/smsapi.log >> /var/log/smsapi.log
echo ======================================================
echo Result for SMSAPI , so that you can verify that how much sms are actually sent with the status codes
cat  /var/log/smsapi.log



 

CRON JOB TO RUN IT DAILY IN NIGHT

Now set cron job to run it daily in night

@daily /temp/sms.sh


 LOGS

you can view log files in following location
/var/log/sms.log

Sample:

Thu Jun 18 11:43:20 PKT 2015 ! Expiry Notification have been sent to USER1, on cell number 033333333333
Thu Jun 18 11:43:20 PKT 2015 ! Expiry Notification have been sent to USER2, on cell number 0333132121211

/var/log/smsapi.log

Results with status from api gateway services (Useful to track the messages are actually sent or having errors from provider like server down, credit finished etc etc)

Sample:

Status: 013, Id: -1434609800USER1, Number: +923452266605
Status: 013, Id: -1434609800USER2, Number: +923222656143


2- SMS for NEW Account Creation

1

mkdir /temp
touch /temp/sms-new-account.sh
chmod +x /temp/sms-new-account.sh
nano /temp/sms-new-account.sh

#!/bin/sh
# set -x
# BASH base SMS script for NEW ACCOUNTnotification for Freeradius/mysql users
# 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 : 19th june, 2015
# This script was specially modified for APITEL http sms gateway services
# which requires unique transaction ID each time, so i used datetimesecond feature as jugaar
# made for KHI/PK

# MYSQL root id and password
SQLUSER="root"
SQLPASS="pass"
DB="radius-db"

# APITEL User Name & Password
APIUSER="APIUSER"
APIPASS="APIPASS"
API="SENDERID"

# Date functions to find current date, month year and Transaction id using seconds ; ) jugaar way ; )
NOW=$(date)
TID=$(date +"-%s")

# Check Account which are created before this number of MINUTES
CREATION=5

touch /tmp/sms-new-account.log
touch /tmp/sms-new-account-api.log
> /tmp/sms-new-account.log
> /tmp/sms-new-account-api.log

# Export usernames and mobile from the mysql table in a file,  which Expiry is after 3 days
USRVALID=`mysql -u$SQLUSER -p$SQLPASS -e "use $DB; select creationdate,login,package,expirydate,mobile from users WHERE creationdate >= NOW() - INTERVAL $CREATION MINUTE;"`
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; select creationdate,login,package,expirydate,mobile from users WHERE creationdate >= NOW() - INTERVAL $CREATION MINUTE;" > /tmp/newact

# Check User Validation, if not found exit with error , else continue
echo
if [ "$USRVALID" == "" ]; then
echo -e "No new user created in last minutes, so nothign to do , zaib !"
else
echo -E "user Created found , proceeding..."

# Remove 0 if any in mobile number and export it to final list
cat /tmp/newact | awk '{gsub("^0","",$7); print $1,$2,$3,$4,$5,$6,$7}' > /tmp/newactfinal

# Add DATE to separate entries in sms-new-account.log
echo ================================ >> /var/log/sms-new-account.log
echo $NOW >> /var/log/sms-new-account.log
echo ================================ >> /var/log/sms-new-account.log

echo ================================ >> /var/log/sms-new-account-api.log
echo $NOW >> /var/log/sms-new-account-api.log
echo ================================ >> /var/log/sms-new-account-api.log

# Apply Count Loop Formula while deleting first line which have simple text, and also any line which dont have mobile number [in second column]
# Apply Count Loop Formula while deleting first line which have simple text, and also any line which dont have mobile number [in second column]
num=0
cat /tmp/newactfinal |sed '1d' |awk 'NF > 6' | while read users
do
num=$[$num+1]
username=`echo $users |awk '{print $3}'`
mobile=`echo $users | awk '{print $7}'`
pkg=`echo $users | awk '{print $4}'`
exp=`echo $users | awk '{print $5}'`
#echo "Welcome to MYNET Broadband Services! Your account details are as follow...
#Username = $username
#Package = $pkg
#Expiry = $exp
#Cell No = $mobile"

# SMS Body
BODY="Welcome+to+MYISP+Services,+Your+account+details+are:++id=$username+/+Package=+$pkg+/+Expiry=+$exp+/+Cell=+0$mobile++++MYISP+BROADBAND"

echo "$NOW ! New Acount Creation Notification have been sent to $username, on cell number 0$mobile"
echo "$NOW ! New Acount Creation Notification have been sent to $username, on cell number 0$mobile" >> /var/log/sms-new-account.log

# Add action like send sms or email as per required or designed / zaib
# Sending sms via APITEL API SMS Gatewy / syed jahanzaib / aacable@hotmail.com

curl "http://api1.itelservices.net/send.php?transaction_id=$TID$username&user=$APIUSER&pass=$APIPASS?&number=%2B92$mobile&text=$BODY&from=$API" >> /tmp/sms-new-account-api.log
sed 's/\(Status\)/\n\1/g' /tmp/sms-new-account-api.log >> /var/log/sms-new-account-api.log
echo
echo Result for SMSAPI , so that you can verify that how much sms are actually sent with the status codes
#cat  /var/log/sms-new-account.log
done

fi

Cron it to run after every 5 minutes

*/5 * * * * /temp/sms-new-account.sh


 3- SMS for ALL users (I deployed it for Webmin usage)


#!/bin/bash
# set -x
# Script to send GENERAL SMS via WEBMIn
# Syed Jahanzaib
# aacable @ hotmail.com
# https://aacable.wordpress.com
# Created on 24th June, 2015

SQLUSER="root"
SQLPASS="mysqlpassword"
DB="radiusdb"

# APITEL User Name & Password
APIUSER="xxxx"
APIPASS="xxxxx"
API="xxxx"

######################
# ACCOUNT EXPIRY CHECK
######################

# Date functions to find current date, month year and Transaction id using seconds ; ) jugaar way ; )
NOW=$(date)
TID=$(date +"-%s")

# Adding files
touch /tmp/smspanel.log
touch /tmp/smapanel-api.log
> /tmp/smspanel.log
> /tmp/smapanel-api.log

mysql -uroot -pgatewayb3 -e "use mynet; SELECT login,mobile FROM users;"  > /tmp/smspanellist

# Remove 0 if any in mobile number and export it to final list
cat /tmp/smspanellist | awk '{gsub("^0","",$2); print $1,$2}' > /tmp/smspanellistfinal

# Add DATE TIME in /tmp/smspanel.log to separate date wise entries / zaib
echo ====================================================== >> /var/log/smspanel.log
echo $NOW >> /var/log/smspanel.log
echo ====================================================== >> /var/log/smspanel.log

# Add DATE TIME in /tmp/smspanel-api.log to separate date wise entries WITH API STATUS for cross verification / zaib
echo ====================================================== >> /var/log/smspanel-api.log
echo $NOW >> /var/log/smspanel-api.log
echo ====================================================== >> /var/log/smspanel-api.log

# Apply Count Loop Formula while deleting first line which have simple text, and also any line which dont have mobile number [in second column]
num=0
# remove first line which have simple text, then remove dash in second column which is mobile numbers
cat /tmp/smspanellistfinal |sed '1d' |awk 'NF > 1' | awk '{gsub("-","",$2)}1' | while read users
do
num=$[$num+1]
username=`echo $users |awk '{print $1}'`
mobile=`echo $users | awk '{print $2}'`

# SMS Body in local file and remove new lines and replace spaces with plus sign for api acceptance
BODY=`cat /tmp/smspanelmsg.txt  |tr '\r\n' ' ' | sed -e "s/\s\{1,\}/+/g"`

#echo "$NOW ! $BODY ---- MSG was sent to $username, on cell number 0$mobile"
echo "$NOW ! Your MSG was sent to $username, on cell number 0$mobile" >> /var/log/smspanel.log

# Sending sms via APITEL API SMS Gatewy / syed jahanzaib / aacable@hotmail.com

curl "http://api1.itelservices.net/send.php?transaction_id=$TID$username&user=$APIUSER&pass=$APIPASS?&number=%2B92$mobile&from=$API&text=$BODY" >> /tmp/smspanel-api.log
sed 's/\(Status\)/\n\1/g' /tmp/smspanel-api.log >> /var/log/smspanel-api.log
done

 

ITELSERVICES.NET related information

Sample of URL to send SMS

http://api1.itelservices.net/send.php?transaction_id=message1&user=bilal&pass=bilal2015?&number=%2B923333021909&text=hello&from=MyNet

Please note that the transaction id must be unique for each sms, example message1, message2 and so on any word is acceptable, i used date time as transaction id, you may use your own.

 

INFORMATION AND ERROR CODES related to API

For the information/error codes

 

1

 

2

 

3


 

Regard’s
Syed Jahanzaib

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: