Syed Jahanzaib Personal Blog to Share Knowledge !

February 14, 2022

RM: Auto Renew User if Deposit available

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

Revision History:

  1. 24-Jun-2015 / Added Base Script
  2. 29-Jun-2016 / Added Invoice function
  3. 14-Feb-2022 / Added multi check for quota related functions


As requested by an Valenzuela / African friends

Scenario:

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

 

As a workaround, I made below script that can perform following functions ,,,

  1. Scheduled to run after every 5 (or x) minute(s)
  2. Fetch all users who are expired either by Date or Quota
  3. Check if these users have DEPOSIT available (credits above then 0)
  4. Check the current service price and match it with the available deposit/credits
  5. If deposit is not sufficient as per service price, Then print error
  6. If deposit is enough, renew the service , Add Expiration Days (according to the service package)
  7. Reset the QUOTA …
    * if account is fresh, add new quote as per package
    * if account is old and quota is expired, then reset it add new quota as per package
    * if account date is expired but quota is remaining, then add the new quota in existing quota (addictive mode
    * Show user Before / After Quota
    – If user is online show his IP / NAS IP / Time / NAS-IP]if any and sends email/sms to user about the renewal done by deposit : )

We can further add the SMS/EMAIL function in the script as per requirements to let know about the renewal.

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 [keep the header intact].
This is my own idea Just to share with anyone who is in similar requirements or just for learning purposes !


SCRIPT!

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

& paste following, make sure to change the SQL Password !

#!/bin/bash
#set -x
clear
# 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
# Last modified on 14-Feb-2022
# 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="XXXXXXX"
export MYSQL_PWD=$SQLPASS
DB=radius
CMD="mysql -u$SQLUSER --skip-column-names -s -e"
bytesToHuman() {
b=${1:-0}; d=''; s=0; S=(Bytes {K,M,G,T,P,E,Z,Y}iB)
while ((b > 1024)); do
d="$(printf ".%02d" $((b % 1024 * 100 / 1024)))"
b=$((b / 1024))
let s++
done
echo "$b$d ${S[$s]}"
}
USERLIST="/tmp/deposituserlist.txt"
> $USERLIST
DATE=`date`
#Create list of users which have deposit more then 0.00 value, means valid deposite
$CMD "use $DB; SELECT SQL_CALC_FOUND_ROWS username, firstname, lastname, address, city, zip, country, state, phone, mobile,
email, company, taxid, srvid, downlimit, uplimit, comblimit, expiration, uptimelimit, credits, comment,
enableuser, staticipcpe, staticipcm, ipmodecpe, ipmodecm, srvname, limitdl, limitul, limitcomb, limitexpiration,
limituptime, createdon, verifycode, verified, selfreg, acctype, maccm, LEFT(lastlogoff, 10)
, IF (limitdl = 1, downlimit - COALESCE((SELECT SUM(acctoutputoctets) FROM radacct
WHERE radacct.username = tmp.username) -
(SELECT COALESCE(SUM(dlbytes), 0) FROM rm_radacct
WHERE rm_radacct.username = tmp.username), 0), 0),
IF (limitul = 1, uplimit - COALESCE((SELECT SUM(acctinputoctets) FROM radacct
WHERE radacct.username = tmp.username) -
(SELECT COALESCE(SUM(ulbytes), 0) FROM rm_radacct
WHERE rm_radacct.username = tmp.username), 0), 0),
IF (limitcomb =1, comblimit - COALESCE((SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct
WHERE radacct.username = tmp.username) -
(SELECT COALESCE(SUM(ulbytes + dlbytes), 0) FROM rm_radacct
WHERE rm_radacct.username = tmp.username), 0), 0),
IF (limituptime = 1, uptimelimit - COALESCE((SELECT SUM(acctsessiontime) FROM radacct
WHERE radacct.username = tmp.username) -
(SELECT COALESCE(SUM(acctsessiontime), 0) FROM rm_radacct
WHERE rm_radacct.username = tmp.username), 0), 0)
FROM
(
SELECT username, firstname, lastname, address, city, zip, country, state, phone, mobile, email, company,
taxid, rm_users.srvid, rm_users.downlimit, rm_users.uplimit, rm_users.comblimit, rm_users.expiration,
rm_users.uptimelimit, credits, comment, enableuser, staticipcpe, staticipcm, ipmodecpe, ipmodecm, srvname, limitdl,
limitul, limitcomb, limitexpiration, limituptime, createdon, verifycode, verified, selfreg, acctype, maccm,
mac, groupid, contractid, contractvalid, rm_users.owner, srvtype, lastlogoff
FROM rm_users
JOIN rm_services USING (srvid)
ORDER BY username ASC
) AS tmp
WHERE 1
AND (tmp.acctype = '0' OR tmp.acctype = '1' OR tmp.acctype = '2' OR tmp.acctype = '3' OR tmp.acctype = '4' OR tmp.acctype = '5' )
AND tmp.enableuser = 1 AND
(IF (limitdl = 1, downlimit - (SELECT COALESCE(SUM(acctoutputoctets), 0)
FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(dlbytes), 0)
FROM rm_radacct WHERE rm_radacct.username = tmp.username) , 1) <= 0
OR
IF (limitul = 1, uplimit - (SELECT COALESCE(SUM(acctinputoctets), 0)
FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(ulbytes), 0)
FROM rm_radacct WHERE rm_radacct.username = tmp.username) , 1) <= 0
OR
IF (limitcomb = 1, comblimit -
(SELECT COALESCE(SUM(acctinputoctets + acctoutputoctets), 0)
FROM radacct WHERE radacct.username = tmp.username) +
(SELECT COALESCE(SUM(ulbytes + dlbytes), 0)
FROM rm_radacct WHERE rm_radacct.username = tmp.username) , 1) <= 0
OR
IF (limituptime = 1, uptimelimit - (SELECT COALESCE(SUM(acctsessiontime), 0)
FROM radacct WHERE radacct.username = tmp.username) + (SELECT COALESCE(SUM(acctsessiontime), 0)
FROM rm_radacct WHERE rm_radacct.username = tmp.username) , 1) <= 0
OR
IF (limitexpiration=1, UNIX_TIMESTAMP(expiration) - UNIX_TIMESTAMP(NOW()), 1) <= 0) LIMIT 0, 50;" | awk '{print $1}' > $USERLIST
TOTUSR=`$CMD "use $DB; select username from rm_users;" | wc -l`
echo "- INFO: Total Users scanned: $TOTUSR

"
#LOOK FOR VALID USER IN FILE, IF EMPTY THEN EXIT
USRVALID=`cat $USERLIST`
if [ -z "$USRVALID" ]; then
echo "INFO: No user found with Expired Date/Quota package ... Exiting peacefully!"
exit 1
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=`$CMD "use radius; SELECT credits FROM rm_users where username = '$USR';" | sed 's/\..*$//'`
###########################################
# ACCOUNT EXPIRY CHECK and other variables#
###########################################
TODAY=$(date +"%Y-%m-%d")
TODAYHM=$(date +"%Y-%m-%d-%H-%M")
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'`
CURR_MONTHYEAR=$(date +"%Y-%m")
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'`
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"."`
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:.*//'`
PKGNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$SRVID';" |awk 'FNR == 2'`
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))
USR_CUR_COMBLIMIT=`$CMD "use $DB; SELECT comblimit FROM rm_users WHERE username= '$USR';"`
USR_CUR_COMBLIMIT_HUMAN=`bytesToHuman $USR_CUR_COMBLIMIT`
TOT_DOWN_UP_CURR_MONTH_IN_BYTES=`$CMD "use $DB; SELECT ((SUM(AcctInputOctets)+SUM(AcctOutputOctets))) FROM radacct WHERE username ='$USR' AND acctstarttime LIKE '$CURR_MONTHYEAR-%' LIMIT 0 , 30;"`
TOT_USER_DOWNLOAD_SINCE_LAST_REFRESH_IN_BYTES=`$CMD "use $DB; SELECT downlimit from rm_users where username = '$USR';" |sed 's/[\._-]//g'`
TOT_USER_UPLOAD_SINCE_LAST_REFRESH_IN_BYTES=`$CMD "use $DB; SELECT uplimit from rm_users where username = '$USR';" |sed 's/[\._-]//g'`
RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE=`$CMD "use $DB; SELECT SQL_CALC_FOUND_ROWS IF (limitdl = 1, downlimit - COALESCE((SELECT SUM(acctoutputoctets) FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(dlbytes), 0) FROM rm_radacct WHERE rm_radacct.username = tmp.username), 0), 0), IF (limitul = 1, uplimit - COALESCE((SELECT SUM(acctinputoctets) FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(ulbytes), 0) FROM rm_radacct WHERE rm_radacct.username = tmp.username), 0), 0), IF (limitcomb =1, comblimit - COALESCE((SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(ulbytes + dlbytes), 0) FROM rm_radacct WHERE rm_radacct.username = tmp.username), 0), 0), IF (limituptime = 1, uptimelimit - COALESCE((SELECT SUM(acctsessiontime) FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(acctsessiontime), 0) FROM rm_radacct WHERE rm_radacct.username = tmp.username), 0), 0) FROM ( SELECT username, firstname, lastname, address, city, zip, country, state, phone, mobile, email, company, taxid, rm_users.srvid, rm_users.downlimit, rm_users.uplimit, rm_users.comblimit, rm_users.expiration, rm_users.uptimelimit, credits, comment, enableuser, staticipcpe, staticipcm, ipmodecpe, ipmodecm, srvname, limitdl, limitul, limitcomb, limitexpiration, limituptime, createdon, verifycode, verified, selfreg, acctype, maccm, mac, groupid, contractid, contractvalid, rm_users.owner, srvtype, lastlogoff FROM rm_users JOIN rm_services USING (srvid) ORDER BY username ASC ) AS tmp WHERE 1 AND username LIKE '$USR%' AND (tmp.acctype = '0' OR tmp.acctype = '1' OR tmp.acctype = '2' OR tmp.acctype = '3' OR tmp.acctype = '4' OR tmp.acctype = '5' ) LIMIT 0, 50;" | awk '{print $3}'`
RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE_IN_HUMAN_FRIENDLY_FORMAT=`bytesToHuman $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE`
IS_QUOTA_LEFT_IN_NEGATIVE=`echo $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE_IN_HUMAN_FRIENDLY_FORMAT | grep -i -c "-"`
RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_NEGATIVE_VALUE_IN_BYTES_WITHOUT_DASH=`echo $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE_IN_HUMAN_FRIENDLY_FORMAT |sed 's/[\._-]//g'`
RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_NEGATIVE_VALUE_IN_HUMAN_FRIENDLY_VALUE=`bytesToHuman $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_NEGATIVE_VALUE_IN_BYTES_WITHOUT_DASH`
RQT=`$CMD "use $DB; SELECT username,
IF (limitcomb =1, comblimit - COALESCE((SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct
WHERE radacct.username = tmp.username) -
(SELECT COALESCE(SUM(ulbytes + dlbytes), 0) FROM rm_radacct
WHERE rm_radacct.username = tmp.username), 0), 0)
FROM
(
SELECT username, firstname, lastname, address, city, zip, country, state, phone, mobile, email, company,
taxid, rm_users.srvid, rm_users.downlimit, rm_users.uplimit, rm_users.comblimit, rm_users.expiration,
rm_users.uptimelimit, credits, comment, enableuser, staticipcpe, staticipcm, ipmodecpe, ipmodecm, srvname, limitdl,
limitul, limitcomb, limitexpiration, limituptime, createdon, verifycode, verified, selfreg, acctype, maccm,
mac, groupid, contractid, contractvalid, rm_users.owner, srvtype
FROM rm_users JOIN rm_services USING (srvid) ORDER BY username ASC
) AS tmp
WHERE 1 AND (tmp.acctype = '0' OR tmp.acctype = '1' OR tmp.acctype = '3' OR tmp.acctype = '4')
AND username LIKE '$USR%';" | awk '{print $2}'`
RQT_OUT=`echo $RQT`
RQT_RESULT1=`echo $RQT | grep -i -c "-"`
RQT_RESULT_REMOVE_HYPHEN=`echo $RQT | sed -e 's/-//g'`
#exit 1
RQT4=`bytesToHuman $RQT_RESULT_REMOVE_HYPHEN`
QTL_Y_OR_NO=`$CMD "use $DB; SELECT limitcomb FROM rm_services WHERE srvid = '$SRVID';"`
if [ "$DEPOSIT" -eq 0 ]; then
LASTUSRBAL=0
else
LASTUSRBAL=$(($DEPOSIT - $SRVPRICE))
fi
TIMEUNITEXP=`$CMD "use $DB; SELECT timeunitexp FROM radius.rm_services WHERE srvid = '$SRVID';"`
TIMEBASEEXP=`$CMD "use $DB; SELECT timebaseexp FROM radius.rm_services WHERE srvid = '$SRVID';"`
if [ "$TIMEBASEEXP" == "2" ]; then
NEXTEXPIRYADD=$(date +"%Y-%m-%d" -d "+$TIMEUNITEXP days")
EXPERIOD="$TIMEUNITEXP Days"
fi
# Set Quota Limit variable which will be used in the end zzzzzzzzzzzz
if [ "$TIMEBASEEXP" == "3" ]; then
NEXTEXPIRYADD=$(date +"%Y-%m-%d" -d "+$TIMEUNITEXP month")
EXPERIOD="$TIMEUNITEXP Month"
fi
# 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
IS_USER_EXPIRED=`rmauth 127.0.0.1 $USR 1 |grep -i -c "The account has expired"`
if [ "$IS_USER_EXPIRED" -eq 1 ];then
USREXPORNOT=1
else
USREXPORNOT=0
fi
# Check if user quota is ended or not
IS_USER_QUOTA_END=`rmauth 127.0.0.1 $USR 1 |grep -i -c "Total traffic limit reached"`
if [ "$IS_USER_QUOTA_END" -eq 1 ];then
USRQTORNOT=1
else
USRQTORNOT=0
fi
if [ "$USREXPORNOT" -eq 1 ]; then
DATEEXP="$USR - *** Account Date Expired that is $SRVEXPIRYFULL !! **"
else
DATEEXP="$USR - Account Date is OK that is $SRVEXPIRYFULL."
fi
#IS_QUOTA_LEFT_IN_NEGATIVE zzzzzzzzzzzzzzzzzzz
if [ "$USRQTORNOT" -eq 1 ] && [ "$IS_QUOTA_LEFT_IN_NEGATIVE" -eq 1 ] ; then
RQT_in_NEGATIVE_HUMAN=`bytesToHuman $RQT_RESULT_REMOVE_HYPHEN`
QTEXP="$USR - *** Account QUOTA Expired that is Negative $RQT_in_NEGATIVE_HUMAN !! **"
fi
if [ "$USRQTORNOT" -eq 0 ]; then
RQT_in_POSITIVE_HUMAN=`bytesToHuman $RQT_RESULT_REMOVE_HYPHEN`
QTEXP="$USR - Account QUOTA is OK that is $RQT_in_POSITIVE_HUMAN "
fi
if [ "$DL" == "NULL" ]; then
QTEXP="$USR - NO QUOTA have been added yet! seems fresh account."
fi
if [ "$UL" == "NULL" ]; then
QTEXP="$USR - NO QUOTA have been added yet! seems fresh account."
fi
if [ "$RQT_OUT" == "0" ]; then
QTEXP="$USR - QUOTA INFO: No Quota hase been added yet! seems fresh account."
#echo "$USR - QUOTA INFO: NO quota hase been added yet! seems fresh account."
fi

########### ACCOUNT STATUS EXPIRED BUT NOT ENOUGH DEPOSIT to RENEW ACTION ############
if [ "$DEPOSIT" -eq 0 ]; then
USRDEPORNOT=0
fi
if [ "$DEPOSIT" -lt "$SRVPRICE" ]; then
USRDEPORNOT=0
echo "
$DATEEXP
$QTEXP
$USR - Current Expiry = $SRVEXPIRYFULLD
$USR - Pacakge Name/Price: $PKGNAME | $SRVPRICE PKR
$USR - Current Deposite Available = $DEPOSIT"
echo -e "$COL_RED$USR | ERROR: Insufficient deposit for Renewal ! Current Deposite is $DEPOSIT and SRV renewal price is $SRVPRICE $COL_RESET
"
fi
if [ "$DEPOSIT" -eq "$SRVPRICE" ] || [ "$DEPOSIT" -gt "$SRVPRICE" ]; then
USRDEPORNOT=1
echo -e "$COL_GREEN$USR INFO: | Deposit Balance: $DEPOSIT | Service Name: $PKGNAME | Price $SRVPRICE $COL_RESET"
fi
########### ACCOUNT STATUS EXPIRED and DEPOSIT IS ENOUGH TO RENEW ACTION ############
if [ "$USREXPORNOT" -eq 1 ] || [ "$USRQTORNOT" -eq 1 ]; then
if [ "$USRDEPORNOT" -eq 1 ] ; then
# RENEW USERS IF ALL CONDITIONS MATCHED / PRINT FETCHED VALUES , JUST FOR INFO / ZAIB bbbbb
echo "
$DATEEXP
$QTEXP
$USR - Resetting Date: Current Expiry = $SRVEXPIRYFULLD / Next Expiry: $NEXTEXPIRYADD | UNIT = $EXPERIOD
$USR - Pacakge Name/Price: $PKGNAME | $SRVPRICE PKR
$USR - Current Deposite Available = $DEPOSIT | Deposite Balance after Deduction: $LASTUSRBAL"
# ADD 30 DAYS VALUE TO EXPIRED USER ACCOUNTzzzzzzzzzz
$CMD "use radius; UPDATE rm_users SET expiration = '$NEXTEXPIRYADD' WHERE username = '$USR';"
# ADD COMMENTS
$CMD "use radius; UPDATE rm_users SET comment = '$USR account last renewed from previous DEPOSIT $DATE' WHERE username = '$USR';"
# ADD SYSLOG ENTRY
#$CMD "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
$CMD "use radius; UPDATE rm_users SET credits = '$LASTUSRBAL' WHERE username = '$USR';"
# ADD INVOICE
$CMD "use $DB; 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', '$TODAYHM', '$ADDRESS', '$CITY', '00000', '$COUNTRY', '$STATE', '$FULLNAME', 'n/a',
DATE_ADD(CURDATE(), INTERVAL '10' DAY), '0', '0', '$TODAY', '$SRVPRICE', '0.000000', '$TODAYHM', '$LASTUSRBAL', '', '$MOBILE', '$MOBILE', '0.00' );"

$CMD "use $DB; 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-$TODAYHM', '2', '1', DATE_ADD(CURDATE(), INTERVAL '10' DAY),
'577343812eee0' );"
if [ "$RQT_RESULT1" -eq 1 ] ;then
echo "$USR - Total Data Reamining since last renewal = NO DATA REMAINING !"
else
echo "$USR - Total Data Reamining since last renewal = $RQT4"
fi
######################################################
############## QUOTA SECTION STARTS HERE #############
######################################################
# UPDATE Quota limitations if any, else ignore
DL=`$CMD "use radius; SELECT (SELECT SUM(acctoutputoctets) FROM radacct WHERE username = '$USR') - (SELECT COALESCE(SUM(dlbytes), 0) FROM rm_radacct WHERE username = '$USR');"`
UL=`$CMD "use radius; SELECT (SELECT SUM(acctinputoctets) FROM radacct WHERE username = '$USR') - (SELECT COALESCE(SUM(ulbytes), 0) FROM rm_radacct WHERE username = '$USR');"`
if [ "$DL" == "NULL" ]; then
DL=x
fi
if [ "$UL" == "NULL" ]; then
UL=x
fi
if [ "$RQT_OUT" == "0" ]; then
QTEXP="QUOTA INFO: NO QUOTA have been added yet! seems fresh account."
echo "QUOTA INFO: NO QUOTA have been added yet! seems fresh account."
fi
# QUOTA FRESH ADD MODE FOR NEW ACCOUNT
if [ "$DL" == "x" ] || [ "$UL" == "x" ]; then
echo "*********** QUOTA FRESH ADD MODE FOR NEW ACCOUNT"
echo "$USR - QUOTA INFO: Account Seems Fresh, Adding NEW Quota ..."
#$CMD "use $DB; UPDATE rm_users SET comblimit = '$PKGQUOTABYTES' WHERE username = '$USR';"
ADDICTIVE_COMB_LIMIT=`echo "1+$PKGQUOTABYTES" |bc -l`
$CMD "use $DB; UPDATE rm_users SET comblimit = '$ADDICTIVE_COMB_LIMIT' WHERE username = '$USR';"
fi
#exit 1
# QUOTA RESET MODE for expired QUOTA
IS_USER_QUOTA_END=`rmauth 127.0.0.1 $USR 1 |grep -i -c "Total traffic limit reached"`
if [ "$IS_USER_QUOTA_END" -eq 1 ] && [ "$RQT_RESULT1" -eq 1 ] ;then
DL=`$CMD "use radius; SELECT (SELECT SUM(acctoutputoctets) FROM radacct WHERE username = '$USR') - (SELECT COALESCE(SUM(dlbytes), 0) FROM rm_radacct WHERE username = '$USR');"`
UL=`$CMD "use radius; SELECT (SELECT SUM(acctinputoctets) FROM radacct WHERE username = '$USR') - (SELECT COALESCE(SUM(ulbytes), 0) FROM rm_radacct WHERE username = '$USR');"`
FINAL1_COMB_LIMIT=`echo "$DL+$UL" |bc -l`
FINAL2_COMB_LIMIT=`echo "$FINAL1_COMB_LIMIT+$PKGQUOTABYTES" |bc -l`
# ADDing Quota
echo "$USR - QUOTA info: RESET MODE: Existing Quota limit is Used. Adding New Quota as per package ..."
$CMD "use radius; UPDATE rm_users SET downlimit = '- $DL', uplimit = '- $UL', comblimit = '$FINAL2_COMB_LIMIT' WHERE username = '$USR';"
fi
## QUOTA ADDICTIVE MODE
IS_USER_QUOTA_END=`rmauth 127.0.0.1 $USR 1 |grep -i -c "Total traffic limit reached"`
if [ "$IS_USER_QUOTA_END" -eq 1 ] && [ "$RQT_RESULT_REMOVE_HYPHEN" -gt 0 ] ;then
#echo "********* QUOTA ADDICTIVE MODE"
echo "$USR - QUOTA info: ADDICTIVE MODE: Existing Quota is remaining. Adding New Quota in existing quota as per pacakge (IF ANY) ..."
##echo "$USR - QUOTA setting is - ADDICTIVE ..."
ADDICTIVE_COMB_LIMIT=`echo "$USR_CUR_COMBLIMIT+$PKGQUOTABYTES" |bc -l`
$CMD "use $DB; UPDATE rm_users SET comblimit = '$ADDICTIVE_COMB_LIMIT' WHERE username = '$USR';"
fi
# QUOTA ADDICTIVE MODE for DATE EXPIRED USERS ONLY
IS_USER_QUOTA_END=`rmauth 127.0.0.1 $USR 1 |grep -i -c "Total traffic limit reached"`
if [ "$IS_USER_QUOTA_END" -eq 0 ] && [ "$RQT_RESULT1" -eq 0 ] ;then
echo "$USR - QUOTA info: ADDING quota for DATE EXPIRED USERS ONLY (Existing Quota will be added in new Quota, IF ANY) ..."
ADDICTIVE_COMB_LIMIT=`echo "$USR_CUR_COMBLIMIT+$PKGQUOTABYTES" |bc -l`
$CMD "use $DB; UPDATE rm_users SET comblimit = '$ADDICTIVE_COMB_LIMIT' WHERE username = '$USR';"
fi
#####################################################
############## QUOTA SECTION ENDS HERE ##############
#####################################################
RQT=`$CMD "use radius; SELECT username,
IF (limitcomb =1, comblimit - COALESCE((SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct
WHERE radacct.username = tmp.username) -
(SELECT COALESCE(SUM(ulbytes + dlbytes), 0) FROM rm_radacct
WHERE rm_radacct.username = tmp.username), 0), 0)
FROM
(
SELECT username, firstname, lastname, address, city, zip, country, state, phone, mobile, email, company,
taxid, rm_users.srvid, rm_users.downlimit, rm_users.uplimit, rm_users.comblimit, rm_users.expiration,
rm_users.uptimelimit, credits, comment, enableuser, staticipcpe, staticipcm, ipmodecpe, ipmodecm, srvname, limitdl,
limitul, limitcomb, limitexpiration, limituptime, createdon, verifycode, verified, selfreg, acctype, maccm,
mac, groupid, contractid, contractvalid, rm_users.owner, srvtype
FROM rm_users JOIN rm_services USING (srvid) ORDER BY username ASC
) AS tmp
WHERE 1 AND (tmp.acctype = '0' OR tmp.acctype = '1' OR tmp.acctype = '3' OR tmp.acctype = '4')
AND username LIKE '$USR%';" | awk '{print $2}'`
RQT_HUMAN_FRIENDLY=`bytesToHuman $RQT`
echo "$USR - Current Updated Quota after the renewal ... : $RQT_HUMAN_FRIENDLY"
USRIP=`$CMD "use $DB; SELECT framedipaddress FROM radacct WHERE acctstoptime IS NULL AND username = '$USR';"`
if [ -z "$USRIP" ]; then
echo "$USR - Online Status: Offline !"
else
NASIP=`$CMD "use $DB; SELECT nasipaddress FROM radacct WHERE username = '$USR' and acctstoptime IS NULL;"`
ONLINE_TIME=`$CMD "use $DB; SELECT acctsessiontime FROM radacct WHERE username = '$USR' and acctstoptime IS NULL;"`
ONLINE_START_TIME=`$CMD "use $DB; SELECT acctstarttime FROM radacct WHERE username = '$USR'and acctstoptime IS NULL;"`
ONLINE_TIME_FIN=`echo "$ONLINE_TIME/60/60" |bc -l | cut -c1-4`
echo "$USR - Online Status: IP: $USRIP | Online Since: $ONLINE_START_TIME | Online Time: $ONLINE_TIME_FIN Hour(s) | NAS: $NASIP "
fi
fi
fi
done


Result:

  • /temp/auto.sh
- INFO: Total Users scanned: 2
INFO: No user found with Expired Date/Quota package ... Exiting peacefully!
  • /temp/auto.sh
- INFO: Total Users scanned: 2

test50 INFO: | Deposit Balance: 99960 | Service Name: 2mb Speed - 30 Days - 1 GB Quota | Price 10

test50 - *** Account Date Expired that is 2022-02-14 00:00:00 !! **
test50 - Account QUOTA is OK that is 2.00 GiB
test50 - Resetting Date: Current Expiry = 2022-02-14 / Next Expiry: 2022-03-16 | UNIT = 30 Days
test50 - Package Name/Price: 2mb Speed - 30 Days - 1 GB Quota | 10 PKR
test50 - Current Deposit Available = 99960 | Deposite Balance after Deduction: 99950
test50 - Total Data Reamaining since last renewal = 2.00 GiB
test50 - QUOTA info: ADDING quota for DATE EXPIRED USERS ONLY (Existing Quota will be added in new Quota, IF ANY) ...
test50 - Current Updated Quota after the renewal ... : 3.00 GiB
test50 - Online Status: Offline !
  • /temp/auto.sh

- INFO: Total Users scanned: 2

test50 INFO: | Deposit Balance: 99950 | Service Name: 2mb Speed - 30 Days - 1 GB Quota | Price 10

test50 - Account Date is OK that is 2022-03-16 00:00:00.
test50 - *** Account QUOTA Expired that is Negative 742.69 MiB !! **
test50 - Resetting Date: Current Expiry = 2022-03-16 / Next Expiry: 2022-03-16 | UNIT = 30 Days
test50 - Pacakge Name/Price: 2mb Speed - 30 Days - 1 GB Quota | 10 PKR
test50 - Current Deposite Available = 99950 | Deposite Balance after Deduction: 99940
test50 - Total Data Reamining since last renewal = NO DATA REMAINING !
test50 - QUOTA info: RESET MODE: Existing Quota limit is Used. Adding New Quota as per package ...
test50 - Current Updated Quota after the renewal ... : 1024.00 MiB
test50 - Online Status: Offline !

20 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

  11. how to install script

    Like

    Comment by Akash Patel — January 30, 2019 @ 10:10 PM

  12. I hv copy script and creat install.sh file in. Temp folder then put script in that file
    Its working but after renew customer is disabled…why????

    Like

    Comment by Akash Patel — February 2, 2019 @ 10:24 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 )

Twitter picture

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

Facebook photo

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

Connecting to %s

%d bloggers like this: