Disclaimer: This is not a reliable method for some OP to acquire the monthly sale report done by reseller. It requires some criteria to match like strict account creation by specific reseller id only. Better to use provided GUI in DMA_RM to inquire the proper report.
This is just a personal way of collecting reporting via automated email which MUST be cross verified by the OP .
Z@iB
BASH:
Following is a bash script which will simply query invoices, categorize it according to reseller account, format it using html coding, sort, total and send email admin.
Logic:
When you will execute this script, it will fetch the reports from rm_users database using last month dates, it will auto calculate last month dates.
Example:
Current month is March,2017. the script will pick Last Month which was February,2017 and will calculate number of days as well using CAL function.
Sample of Report is as follows.

RM Reseller Monthly Report via email

RM Reseller Monthly Report via email
Schelde following script via crontab @monthly. Modify information as per your local network.
#!/bin/bash # Script to query INVOICES section of Radius manager mysql, and # categorize it according to resellers , calculate accounts,sort and total it. # These are my personal way of doing things, and no one is bound to agree with these. # Created: Year 2014 # set -x # MYSQL USER ID PASSWORD SQLUSER="root" SQLPASS="YOUR_MYSQL_PASSWORD" # DATE RELATED STUFF LASTMY=`date +'%Y-%m' -d 'last month'` LAST_MONTH_DIGIT=`date +'%m' -d 'last month'` YEAR_DIGIT=`date +'%Y' -d 'last month'` LAST_MONTH_LAST_DATE=`cal $LAST_MONTH_DIGIT $YEAR_DIGIT |egrep -v [a-z] |wc -w` START="$LASTMY-01" END="$LASTMY-$LAST_MONTH_LAST_DATE" FMONTH=`date +'%B-%Y' -d 'last month'` DATE=`date` # EMAIL RELATED STUFF TO1="YOUR_EMAIL_ID@hotmail.com" GMAILID="GMAIL_ID_TO_SEND_EMAIL_FROM@gmail.com" GMAILPASS="GMAIL_PASSWORD" CONTENT_TYPE="text/html" # LOG FILES FILE="/tmp/dealer_renewal_month.html" FINALFILE="/tmp/dealer_renewal_month_final.html" COMPANY="ZAIB(Pvt)_Ltd. This System is powered by Syed_Jahanzaib aacable@hotmail.com" BODY_TITLE="Report For Dealer Account asof $FMONTH" > $FILE > $FINALFILE # HTML BODY echo " <pre>" > $FILE echo "<b>$BODY_TITLE</b>" >> $FILE echo "<b>DEALER User's_Activated Used_Amount Balance</b> " >> $FILE # QUERY MANAGERS FROM RM_MANAGERS TABLE mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select managername from rm_managers;" | while read dealer do num=$[$num+1] DEALER=`echo $dealer | awk '{print $1}'` # GATHER DATA OF ACTIVE USERS, USED AMOUNT, CURRENT BALANCE, (MOBILE NUMBER IF SMS IS REQUIRED TO SEND) ACTIVEUSERSNO=`mysql -uroot -p$PASS --skip-column-names -e "use radius; SELECT price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '1' OR paymode = '2' OR paymode = '3' OR paymode = '4' OR paymode = '5' OR paymode = '6' OR paymode = '7' OR paymode = '8' OR paymode = '9' OR paymode = '10' OR paymode = '11' ) AND (invgroup = '0' ) AND invnum != '' AND rm_users.owner = '$DEALER';" | sed '/credited/d' | wc -l` USEDAMOUNT=`mysql -uroot -p$PASS --skip-column-names -e "use radius; SELECT price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '1' OR paymode = '2' OR paymode = '3' OR paymode = '4' OR paymode = '5' OR paymode = '6' OR paymode = '7' OR paymode = '8' OR paymode = '9' OR paymode = '10' OR paymode = '11' ) AND (invgroup = '0' ) AND invnum != '' AND rm_users.owner = '$DEALER';" | sed '/credited/d' | awk '{ sum+=$1} END {print sum}'` BALANCE=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select balance from rm_managers WHERE managername = '$DEALER';" |cut -f1 -d"."` MOBILE=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select mobile from rm_managers WHERE managername = '$DEALER';"` SRV=`mysql -uroot -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.service FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '2' ) AND (invgroup = '0' OR invgroup = '1' ) AND invnum != '' AND rm_users.owner = '$DEALER' ORDER BY id LIMIT 0, 10000;" | sed '/credited/d' | awk '{print $1}' | sort | uniq -c` #LOOK FOR ZERO VALUE AMOUNT AND REPLACE IT WITH 0 , IF FOUND #if [ ! -n "$USEDAMOUNT" ]; then #if [ "USEDAMOUNT == "" ]; then #USEDAMOUNT="X" # PRINT ALL GATHERED DATA INTO FILE #echo "<b>$DEALER</b> $ACTIVEUSERSNO $USEDAMOUNT - $BALANCE #------------------------------------------------------------------------" >> $FILE #else # PRINT ALL GATHERED DATA INTO FILE echo "<b>$DEALER</b> $ACTIVEUSERSNO $USEDAMOUNT - $BALANCE Details of Services Activated: Qty Service Name $SRV ------------------------------------------------------------------------" >> $FILE #fi done # MAKE COLUMNS SO THAT IT GETs EASIER TO READS sed -e 's/\t//g' $FILE | column -t | sed 's/ //g' | sed 's/ User/User/g' > $FINALFILE # GATHER DATA OF ACTIVE USERS, USED AMOUNT, CURRENT BALANCE, (MOBILE NUMBER IF SMS IS REQUIRED TO SEND) TOTNO=`mysql -uroot -p$PASS --skip-column-names -e "use radius; SELECT price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '1' OR paymode = '2' OR paymode = '3' OR paymode = '4' OR paymode = '5' OR paymode = '6' OR paymode = '7' OR paymode = '8' OR paymode = '9' OR paymode = '10' OR paymode = '11' ) AND (invgroup = '0' ) AND invnum != '';" | sed '/credited/d' | wc -l` SALES=`mysql -uroot -p$PASS --skip-column-names -e "use radius; SELECT price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '1' OR paymode = '2' OR paymode = '3' OR paymode = '4' OR paymode = '5' OR paymode = '6' OR paymode = '7' OR paymode = '8' OR paymode = '9' OR paymode = '10' OR paymode = '11' ) AND (invgroup = '0' ) AND invnum != '';" | sed '/credited/d' | paste -sd+ | bc | cut -f1 -d"."` echo "Total Users Activated/Renewed in $FMONTH = <b>$TOTNO</b>" >> $FINALFILE echo "Total SALES Done in $FMONTH = <b>$SALES</b>" >> $FINALFILE echo " <b>$COMPANY</b>" >> $FINALFILE echo "Generated on $DATE" >> $FINALFILE echo "</pre> " >> $FINALFILE ##Finally send email with all the data gathered USING SEND_EMAIL TOOL /temp/sendEmail-v1.56/sendEmail -t $TO1 -u "INFO: GT DEALERS MONTHLY BILLING INFO for $FMONTH" -o tls=yes -s smtp.gmail.com:587 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$FINALFILE -o message-content-type=$CONTENT_TYPE # Type file for view cat $FINALFILE
Asalam O Alaikum bhai,
i am looking for a setup where hotspot users are 10000+, want to do it with Mikrotik … any good solution setup sir ? do i really need Radius Manager in any case, how many default hotspot users are supported in userman in Mikrotik ? please help sir…
LikeLike
Comment by Malik Mazhar — May 13, 2017 @ 4:07 PM
Go with Linux base billing system.
Mikrotik User manager is not stable / recommended for large environment.
LikeLike
Comment by Syed Jahanzaib / Pinochio~:) — May 15, 2017 @ 8:17 AM