Revision History:
- 24-Jun-2015 / Added Base Script
- 29-Jun-2016 / Added Invoice function
- 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 ,,,
- Scheduled to run after every 5 (or x) minute(s)
- Fetch all users who are expired either by
Date
orQuota
- Check if these users have DEPOSIT available (credits above then 0)
- Check the current service price and match it with the available deposit/credits
- If deposit is not sufficient as per service price, Then print error
- If deposit is enough, renew the service , Add Expiration Days (according to the service package)
- 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 !