Syed Jahanzaib Personal Blog to Share Knowledge !

June 28, 2016

RM: Auto Renew User if Deposit available

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

AutoRenewal

Revision History:
24-Jun-2015 / Added Base Script
29-Jun-2016 / Added Invoice function / Fixed bug if user quote is under 1 GB limit / few minor tweaks

As requested by an Valenzuela client.

In radius manager, there are few options to purchase credits via online payment gateways like paypal or others. If the user account is expired and he purchase service online, it adds the deposit into user account but it does not auto renew the service (as per my assumption, as paypal doesn’t works here in pakistan, so i have very little to no knowledge on it).

Example:

err

 

To make a workaround for this issue, I made a script that does the following.

  1. Scheduled to run after every 10 minutes
  2. Fetch users lists from rm_users table who have DEPOSIT available (credits above then 0)
  3. Check user account status , if Active Ignore it ,
  4. Else if expires, check the current service price and match it with the available deposit/credits,
  5. If deposit is not sufficient, then print error and exit,
  6. if deposit is enough, renew the service , add 30 days to service, add quota if any and sends email/sms to user about the renewal done by deposit : )

Disclaimer: The script can further be customized according to the requirements. No part of this script is copied from anywhere. You are free to use it, modify it as you like.This is my own idea Just to share with anyone who is inΒ similar need or just for learning purposes !


SCRIPT!

#!/bin/bash
# Script to renew user account via check deposit and act accordingly
# For Radius Manager 4.1.x
# Created by Syed Jahanzaib
# https://aacable.wordpress.com / aacable@hotmail.com
# 24th Jun, 2016 , 18 Ramazan, 1437 Hijri
#set -x
# Colors Config . . . [[ JZ . . . ]]
ESC_SEQ="\x1b["
COL_RESET=$ESC_SEQ"39;49;00m"
COL_RED=$ESC_SEQ"31;01m"
COL_YELLOW=$ESC_SEQ"33;01m"
COL_GREEN=$ESC_SEQ"32;01m"
SQLUSER="root"
SQLPASS="zaib1234"
USERLIST="/tmp/deposituserlist.txt"
#Create list of users which ahve deposite more then 0.00 value, means valid deposite
mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT username, credits FROM rm_users where credits > '0.00';" > $USERLIST
#LOOK FOR VALID USER IN FILE, IF EMPTY THEN EXIT
USRVALID=`cat $USERLIST`
if [ -z "$USRVALID" ]; then
echo -e "ERROR: NO USER FOUND for matching ... exiting!"
exit 0
fi
# Apply Formula to read the file in which users list and act accordingly.
num=0
cat $USERLIST | while read users
do
num=$[$num+1]
USR=`echo $users | awk '{print $1}'`
DEPOSIT=`echo $users | awk '{print $2}' | sed 's/\..*$//'`
######################
# ACCOUNT EXPIRY CHECK and other variables
######################
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'`
FULLNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT firstname, lastname FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
MOBILE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT mobile FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
COUNTRY=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT country FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
STATE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT state FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
ADDRESS=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT address FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
SRVEXPIRYFULLD=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -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:.*//'`
NEXTEXPIRYADD=$(date +"%Y-%m-%d" -d "+30 days")
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"."`
#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))
PKGQUOTABYTES=$(($PKGQUOTA * 1024 * 1024))
LASTUSRBAL=$(($DEPOSIT - $SRVPRICE))

TIMEUNITEXP=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT timeunitexp FROM radius.rm_services WHERE srvid = '$SRVID';"`
TIMEBASEEXP=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT timebaseexp FROM radius.rm_services WHERE srvid = '$SRVID';"`

if [ "$TIMEBASEEXP" == "2" ]; then
EXPERIOD="$TIMEUNITEXP Days"
#echo "$EXPERIOD"
fi
# Set Quota Limit variable which will be used in the end
if [ "$TIMEBASEEXP" == "3" ]; then
EXPERIOD="$TIMEUNITEXP Month"
#echo "$EXPERIOD"
fi

#######zzzzzzzz#timebaseexp
# Set Expiry Date/Month Unit
if [ $PKGQUOTA -eq 0 ]
then
QT="UNLIMITED"
else
QT="$PKGQUOTA MB"
fi


# Check Service Expiry Date, if Active then ignore
if [ $SRVEXPIRY -gt $TODAYDIGIT ]; then
echo -e "$COL_GREEN User Account = $USR | ALREADY ACTIVE | TIMEUNIT = $EXPERIOD | Expiry Date = $SRVEXPIRYFULLD | User Deposit Available = $DEPOSIT | Pacakge Price = $SRVPRICE PKR | Next Expiry = $NEXTEXPIRYADD | Quota = $QT $COL_RESET
"
else
########### ACCOUNT STATUS EXPIRED BUT NOT ENOUGH DEPOSIT to RENEW ACTION ############
if [ "$DEPOSIT" -lt "$SRVPRICE" ]; then
echo -e "$COL_RED User Account = $USR | TIMEUNIT = $EXPERIOD | ERROR: Account was expired on $SRVEXPIRYFULLD but user $USR DOES NOT HAVE ENOUGH DEPOSIT IN USER ACCOUNT! Current Deposite is $DEPOSIT and Required is $SRVPRICE $COL_RESET
"
else
########### ACCOUNT STATUS EXPIRED and DEPOSIT IS ENOUGH TO RENEW ACTION ############
if [ $SRVEXPIRY -lt $TODAYDIGIT ] || [$SRVEXPIRY -eq $TODAYDIGIT ]; then

# RENEW USERS IF ALL CONDITIONS MATCHED / PRINT FETCHED VALUES , JUST FOR INFO / ZAIB
echo -e "$COL_YELLOW User Account = $USR ** RENEWING NOW ** | TIMEUNIT = $EXPERIOD | Expiry Date = $SRVEXPIRYFULLD | User Deposite Available = $DEPOSIT | Pacakge Price = $SRVPRICE PKR | Next Expiry = $NEXTEXPIRYADD | Quota = $QT $COL_RESET
Now Balance is = $LASTUSRBAL PKR"

# 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 DEPOSIT $DATE' 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', 'DEPOSIT_$USR', '$USR', '$USR renewd service > $PKGNAME');"
# UPDATE User Balance
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET credits = '$LASTUSRBAL' WHERE username = '$USR';"
# ADD INVOICE
mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO rm_invoices (managername, username, date, bytesdl, bytesul, bytescomb, downlimit, uplimit, comblimit, time, uptimelimit,
 days, expiration, capdl, capul, captotal, captime, capdate, service, comment, transid, amount, invnum,
 address, city, zip, country, state, fullname, taxid, paymentopt, paymode, invtype, paid, price, tax, remark,
 balance, gwtransid, phone, mobile, vatpercent )
 VALUES
 ('admin', '$USR', NOW(), '0', '0', '$PKGQUOTABYTES', '0', '0', '$PKGQUOTABYTES', '0', '0', '30', '$NEXTEXPIRYADD', '0', '0', '1', '0', '1', '$PKGNAME', 'This user service renewed by Deposit/Payment', '577343812eee0', '1', '2016-0021', '$ADDRESS', '$CITY', '00000', '$COUNTRY', '$STATE', '$FULLNAME', 'n/a',
 DATE_ADD(CURDATE(), INTERVAL '10' DAY), '0', '0', '$TODAY', '$SRVPRICE', '0.000000', '', '$LASTUSRBAL', '', '$MOBILE', '$MOBILE', '0.00' );"

mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO rm_invoices (managername, username, amount, price, tax, vatpercent, balance,
 date, service, paymode, invgroup, paymentopt, transid)
 VALUES ('admin', 'admin', 1, '-$SRVPRICE', '0', '0.00',
 '', NOW(), 'Ref.: C-$TODAY', '2', '1', DATE_ADD(CURDATE(), INTERVAL '10' DAY),
 '577343812eee0' );"

# UPDATE Quota limitations if any, else ignore
if [ "$PKGQUOTA" -ne 0 ]; then
echo "Adding $PKGQUOTA MB Quota Limit for $USR as well
"
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET comblimit = '$PKGQUOTABYTES' WHERE username = '$USR';"
else
echo "No need to add quota"
fi
fi
fi
fi
done
# THE END SCRIPT ENDS HERE #
# SYED JAHANZAIB

RESULT!

1


 

Advertisements

18 Comments »

  1. Good idea and thanks for sharing.
    can it work for hourly rates too.
    Thanks

    Like

    Comment by Baba- — June 28, 2016 @ 2:36 PM

  2. Good Script need on for hotspot situation. When we deal with hours and data missed.
    Thanks for sharing

    Like

    Comment by Baba- — June 28, 2016 @ 2:46 PM

  3. hello,
    dear sir i want to know how to retrieve admin (acp) 0ld/original password .

    Like

    Comment by rk — July 19, 2016 @ 5:08 PM

  4. A.A SYED,
    script is not fully writen there can you please re attache it.

    Like

    Comment by Luay — July 24, 2016 @ 2:59 PM

  5. Hi Syed,

    The materials you share in your blog is Amazing. I learned a lot from these. But I am trying to host connection tracking database on a different server. can you please publish an article on the same. It will be a great help to me and other.

    Like

    Comment by Anupam — August 18, 2016 @ 1:46 PM

  6. Hi Syed,

    Your articles are very educative. Thanks for the time and energy spend on sharing these valuable information with us. I wan to configure connection tracking database on a different server. Please help me resolve this issue. Thanks for the support.

    Like

    Comment by Anupam — August 18, 2016 @ 1:54 PM

  7. HI,

    Can you please publish a post on remote CTS database setup for radius manager.

    Like

    Comment by Anupam Pradhan — August 23, 2016 @ 4:24 PM

  8. i dont know where can copy this script
    please can you help me??

    Liked by 1 person

    Comment by adee — November 15, 2017 @ 6:21 AM

  9. i dont know where can copy this script
    please can you help me??

    Like

    Comment by katem07 — April 2, 2018 @ 12:16 AM

    • connect with your radius with ROOT ID using any SSH client like PUTTY. then on terminal first create a file as mentioned in the script, and then paste script text in it.

      Liked by 1 person

      Comment by Syed Jahanzaib / Pinochio~:) — April 4, 2018 @ 11:55 AM

      • Assalamu alaikum

        Thank’s for ur response sir

        I did it as u guide me and everything goes as excepted πŸ™‚

        and i Scheduled it by crontab -e by this command :
        ————————————————
        #Auto_Refill_By_Syed Jahanzaib
        */10 * * * * /root/temp/autorefill.sh
        ————————————————

        so it’s running every ten minutes as i wish

        But i have one more request if u would help ..

        I have an Expired service for my expired Customers redirecting them to web-proxy page (AS a Non-Payed Page)

        so when the user reached out of his date , he goes to the expired service and that makes his status (Expired BUT ACTIVE)

        so the script looking for the offline customers only and when it find an active customer the script ignoring him even though he have a deposit

        My request is to remove this condition :

        ————————————————————
        # Check Service Expiry Date, if Active then ignore
        if [ $SRVEXPIRY -gt $TODAYDIGIT ]; then
        echo -e “$COL_GREEN User Account = $USR | ALREADY ACTIVE | TIMEUNIT = $EXPERIOD | Expiry Date = $SRVEXPIRYFULLD | User Deposit Available = $DEPOSIT | Pacakge Price = $SRVPRICE PKR | Next Expiry = $NEXTEXPIRYADD | Quota = $QT $COL_RESET

        ————————————————————
        i tried to remove this condition by my self , but the script didn’t run
        so i need ur help sir

        Thank’s a lot for your worthy posts,

        Like

        Comment by katem07 — April 4, 2018 @ 1:56 PM

      • The point is

        Renew all customers who have this conditions :
        1-expired (Orange color)
        2-have an enough deposit even though if he is an Active

        Like

        Comment by katem07 — April 4, 2018 @ 4:02 PM

      • Also u have some missing in the script

        -When the Deposit added to the user in the day that he expired .. the script didn’t renew it till 00:00:00 o’clock which mean the next day

        i hope u solve it for us cause we need this system for our client’s to be applied

        Like

        Comment by katem07 — April 8, 2018 @ 1:09 AM

      • I am unaware if some one still using it. I made it ages ago, and later modified it as per client but didnt updated the blog dueto time shortage.
        if you have modified it kindly email me the complete script with your comments in each modified section.
        thanks

        Like

        Comment by Syed Jahanzaib / Pinochio~:) — April 14, 2018 @ 5:13 PM

  10. How to Schedule it please ?

    Like

    Comment by katem07 — April 2, 2018 @ 12:17 AM

    • i did it

      β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”
      #Auto_Refill_By_Syed Jahanzaib
      */10 * * * * /root/temp/autorefill.sh
      β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”

      Like

      Comment by katem07 — April 4, 2018 @ 3:14 PM


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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

%d bloggers like this: