RM Reseller Monthly Report via GMAiL

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&nbsp;of&nbsp;Services&nbsp;Activated:
Qty Service&nbsp;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