RM: Auto Renew User if Deposit available

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:

 

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 !