Sharing Ideas … Renew Expired User Account via SMS in DMASOFTLAB Radius Manager

EXPANDING POSSIBILITIES

DMASOFTLAB RADIUS MANAGER ACCOUNT RENEWAL ALERT VIA SMS THROUGH KANNEL VIA USING REFILL CARD

 

---

 

 

 

LAST UPDATED: 29th July, 2015 / 15:00

Sharing another theory which can actually work and can be modified to perform about any function which is accessible by web panel in DMASOFTLAB Radius Manager.

Do remember that this is just an lab test scripts , in reality radius manager do add few other info in different tables and places. RM recharge method is somewhat quite different then other billing systems. For the demonstration purpose only, I selected an short route by bypassing all extra codes and just added 30 days in the user account via script .

These are just my own thoughts in old days. Lot of improvement can be made in this regard but all of these things requires dedicated time 🙂 Hope you understand.

---

• PSEUDO CODE [BY ZAiB]
  

• PlaySMS received SMS with script KEYWORD , username and refill card code
• the script will check user name , if found valid then CONTINUE, otherwise EXIT with specific ERROR
• the script will check refill card status, if found valid and usable THEN continue , otherwise EXIT with specific ERROR
• the script will compare refill card price with the user current service price, if its equal or greater it will CONTINUE, otherwise EXIT with specific ERROR
• the script will check for current user account expiry status, and act accordingly, example
• >> if the account was expired in past, it will print last expiry date and will add 30 days to it,
• >> if the account was expired today, it will print last expiry date and will add 30 days to it,
• >> if the account is already active, it will take this expiry date, and will ADD more 30 days 30 days to it, and CONTINUE
• the script will check if the service have Quota limit, it will add the quota to the user account, if no quota is implemented it will simply ignore and print the result as well
• ONCE all the conditions are OK, then it will
• >> echo the results and will add 30 days, if the account is already active with lets say next month date, it will take that date and will add 30 more days in it
• >> Add comments in user properties so that admin can see it in user properties
• >> Add SYSLOG entry in RM last log event
• >> Add entry to mark card as used to prevent re-usage
• >> Print QUOTA information accordingly, if any
• SEND ALL REPLIES VIA SMS USING KANNEL
• and if required you can send report via EMAIL to admin or the user as well including many details you like,.

 

---

 

1- RADIUS SECTION TO CREATE REFILL CARDS:

Create REFILL cards so that we can use it to refill our expired account. Make sure your service have PRICE defined, and the refill card you generate are equal or higher then the service price.
As showed in the image below … [just for example]

---

 

2- SCRIPT SECTION

#!/bin/bash
# Script to renew user account via SMS using refill card in
# Radius Manager
# Created by Syed Jahanzaib
# July, 2014
SQLUSER="root"
SQLPASS="sql_password"
echo $1 > /tmp/user-card
USR=`cat /tmp/user-card | awk {' print $1 '}`
CARD=`cat /tmp/user-card | awk {' print $2 '}`
NEXTEXPIRYADD=$(date +"%Y-%m-%d" -d "+31 days")

#LOOK FOR VALID USER IN RADIUS
USRVALID=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvid FROM radius.rm_users WHERE rm_users.username = '$USR';"`
if [ "$USRVALID" == "" ]; then
echo -e "ERROR: USER NOT FOUND!"
exit 0
fi

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

TODAY=$(date +"%Y-%m-%d")
TODAYDIGIT=`echo $TODAY  | sed -e 's/-//g'`
MONTH=$(date +"-%m")
CMONTH=`echo $MONTH  | sed -e 's/-//g'`
MONTHYEAR=$(date +"%B-%Y")
ALPHAMONTHYEAR=`echo $MONTHYEAR #| sed -e 's/-//g'`
SRVEXPIRYFULL=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT expiration FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
SRVEXPIRYFULLD=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT expiration FROM radius.rm_users WHERE username = '$USR';" |awk '{print $1}' | sed 's/expiration//'`
SRVEXPIRY=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT expiration FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2' | sed -e 's/-//g' | sed 's/00:.*//'`
LOGOFFDATE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT lastlogoff FROM radius.rm_users WHERE username = '$USR';"  |awk 'FNR == 2 {print $1,$2}'`
SRVID=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvid FROM radius.rm_users WHERE rm_users.username = '$USR';" |awk 'FNR == 2 {print $1}'`
SRVPRICE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius;  SELECT unitprice FROM radius.rm_services WHERE rm_services.srvid = $SRVID;" |awk 'FNR == 2 {print $1}' | cut -f1 -d"."`
CARDPRICE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius;  SELECT value FROM rm_cards WHERE cardnum = $CARD;" |awk 'FNR == 2 {print $1}' | cut -f1 -d"."`
#LOOK FOR USER ACTUAL SERVICE NAME
PKGNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$SRVID';" |awk 'FNR == 2'`
# Look for Pakacge Quota trafficunitcomb
PKGQUOTA=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT trafficunitcomb FROM rm_services WHERE srvid= '$SRVID';" |awk 'FNR == 2'`
PKGQUOTAB=$(($PKGQUOTA / 1024))

# LOOK FOR VALID REFILL CARD CODE IN RADIUS CARDS LIST
CARDVALIDATION=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT value, expiration FROM rm_cards WHERE cardnum = '$CARD' AND used = '0000-00-00 00:00:00';"`
if [ "$CARDVALIDATION" == "" ]; then
echo -e "ERROR: INVALID CARD NUMBER!"
exit 0
else

# IF CARD VALUE IS LESS THEN CURRENT PACKAGE PRICE THEN PRINT ERROR AND GOTO END
if [ $CARDPRICE -lt $SRVPRICE ]
then
echo -e "ERROR: CARD PRICE IS NOT SUFFICIENT TO REFRESH $PKGNAME SERVICE"
exit 0
else

# IF CARD VALUE IS EQUAL OR HIGHER  THEN CURRENT PACKAGE PRICE THEN OK
if [ $CARDPRICE -eq $SRVPRICE ]
then
echo
fi

########### ACCOUNT STATUS EXPIRED TODAY ACTION ############
if [ $SRVEXPIRY -eq $TODAYDIGIT ]
then
        echo "Account have been EXPIRED TODAY! Last LOGOUT date was $LOGOFFDATE"
NEXTEXPIRYADD=$(date +"%Y-%m-%d" -d "+31 days")

# PRINT FETCHED VALUES , JUST FOR INFO / ZAIB
echo User Account  = $USR
echo User Actual Package at Billing = $PKGNAME
echo Service Price at Billing = $SRVPRICE PKR
echo This Card Value is    = $CARDPRICE PKR
echo -e "Next Expiry =  $NEXTEXPIRYADD"

# ADD 30 DAYS VALUE TO EXPIRED USER ACCOUNT
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET expiration = '$NEXTEXPIRYADD' WHERE username = '$USR';"

# ADD COMMENTS
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET comment = 'This account was last refresh from scratch code by SMS' WHERE username = '$USR';"

# ADD SYSLOG ENTRY
mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO rm_syslog (datetime, ip, name, eventid, data1) VALUES (NOW(), 'n/a', 'SMSUSER_$USR', '$USR', '$USR renewd service > $PKGNAME');"

# ADD ENTRY FOR CURRENT DATE TIME IN REFIL CARD TO PREVENT RE-USAGE OF SAME CARD NUMBER
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_cards SET owner = '$USR', used = NOW() WHERE cardnum = '$CARD';"

########### ACCOUNT STATUS EXPIRED IN PAST ACTION ############

elif [ $SRVEXPIRY -lt $TODAYDIGIT ]
then
        echo "ACCOUNT WAS EXPIRED on $SRVEXPIRYFULL !  Last LOGOUT date was $LOGOFFDATE"
NEXTEXPIRYADD=$(date +"%Y-%m-%d" -d "+31 days")

# PRINT FETCHED VALUES , JUST FOR INFO / ZAIB
echo User Account  = $USR
echo User Actual Package at Billing = $PKGNAME PKR
echo Service Price at Billing = $SRVPRICE PKR
echo This Card Value is    = $CARDPRICE PKR
echo -e "Next Expiry =  $NEXTEXPIRYADD"

# ADD 30 DAYS VALUE TO EXPIRED USER ACCOUNT
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET expiration = '$NEXTEXPIRYADD' WHERE username = '$USR';"

# ADD COMMENTS
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET comment = 'This account was last refresh from scratch code by SMS';"

# ADD SYSLOG ENTRY
mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO rm_syslog (datetime, ip, name, eventid, data1) VALUES (NOW(), 'n/a', 'SMSUSER_$USR', '$USR', '$USR renewd service > $PKGNAME');"

# ADD ENTRY FOR CURRENT DATE TIME IN REFIL CARD TO PREVENT RE-USAGE OF SAME CARD NUMBER
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_cards SET owner = '$USR', used = NOW() WHERE cardnum = '$CARD';"

# Update QUOTA for the USER
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET comblimit = '$PKGQUOTAB' WHERE username = '$USR';"

else
########### ACCOUNT STATUS OK! ACTION ############

echo -e "User Billing Info:"
echo "Account STATUS= OK!"

NEXTEXPIRYADD=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; select DATE_ADD(expiration, INTERVAL 31 DAY) as x from rm_users where username= '$USR';" |awk 'FNR == 2'`

# PRINT FETCHED VALUES , JUST FOR INFO / ZAIB
echo User Account  = $USR
echo User Actual Package at Billing = $PKGNAME PKR
echo Service Price at Billing = $SRVPRICE PKR
echo This Card Value is    = $CARDPRICE PKR
echo -e "Next Expiry =  $NEXTEXPIRYADD"

NEXTEXPIRYADD=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; select DATE_ADD(expiration, INTERVAL 31 DAY) as x from rm_users where username= '$USR';" |awk 'FNR == 2'`

# ADD 30 DAYS VALUE TO EXPIRED USER ACCOUNT
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET expiration = '$NEXTEXPIRYADD' WHERE username = '$USR';"

# ADD COMMENTS
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET comment = 'This account was last refresh from scratch code by SMS' WHERE username = '$USR';"

# ADD SYSLOG ENTRY
mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO rm_syslog (datetime, ip, name, eventid, data1) VALUES (NOW(), 'n/a', 'SMSUSER_$USR', '$USR', '$USR renewd service > $PKGNAME');"

# ADD ENTRY FOR CURRENT DATE TIME IN REFIL CARD TO PREVENT RE-USAGE OF SAME CARD NUMBER
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_cards SET owner = '$USR', used = NOW() WHERE cardnum = '$CARD';"

fi
fi
fi

########### ACCOUNT STATUS EXPIRED TODAY ACTION ############
if [ $PKGQUOTA -eq 0 ]
then
echo -e "Total Quota Allowed = UNLIMITED"
else
echo -e "Total Quota Allowed = $PKGQUOTAB GB"
fi

---

Copy the script in playsms script admin folder

copy the above renew.sh script into /var/lib/playsms/sms_command/1

---

 

---

3- playSMS COMMAND SECTION

Create command in playSMS
As showed in the image below ..

---

 

4- TESTING RENEWAL VIA SENDING SMS

Send sms to the server (playSMS) with following format

renew refilltest 0942 

renew = is the keyword that playSMS will catch and will execute the renew.sh
refilltest = is the user name
0942 = REFILL card number/code

and if all conditions matched like user name and card number valid, then it will execture the rest of script and will add 30 days expiry limit to the account (so if the account is expired it will be re-activated). It will also check if total quota is implemented on the service, it will add the appropriate quota as well.

 

---

 

 

and if the specific service dont ahve any QUOTA , it will display the result

---

 

if the code is wrong it will send error

---

 

and if user is invalid, it will let you know as well.

You can also see its entry in SYSLOG and user properties comments section as well.

---

 

Lot’s of possibilities here. You can add more details in the script to do more Logging , send alerts etc etc .

All depend on your capabilities and passion towards achieving the Goal !

If time will allow, I will add more details Insha Allah !

Regard’s
Syed Jahanzaib