Syed Jahanzaib Personal Blog to Share Knowledge !

August 20, 2015

Re-seller Daily Sales Activity Report Via Email in Billing System

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

This post is my personnel notes (for future retrieval or reference) on a script that can be used to query billing system (in this example Radius Manager) and gather data for all re-seller’s yesterday sales activity and summarize it in a file and email it to Administrator. It comes handy to get idea which dealer made how much sale with number of activated users, sale amount, balance and summarize it in the end for admin view.

As showed in the image below …

 

1

 

2

1

 


 

SCRIPT

dealer_renewal_yesterday.sh

  • mkdir /temp
  • touch /temp/dealer_renewal_yesterday.sh
  • chmod +x /temp/dealer_renewal_yesterday.sh
  • nano /temp/dealer_renewal_yesterday.sh

Paste the following data [but do make sure you modify the data like id password or other before deploying it.]

#!/bin/bash
# Script to query all re-seller's account for yesterday's sale and there balances. 
# and at end, email the results to admin in html format .
# Created by SYED JAHANZAIBB - aacable @ hotmail . com / 0333-3021-909
# last updated: 25/08/2015
#!/bin/bash

#set -x
clear
# MYSQL USER ID PASSWORD
SQLUSER="root"
SQLPASS="PASSWORD

# DATE RELATED STUFF
TODAY=`date +"%Y-%m-%d"`
YESTERDAY=`date +"%Y-%m-%d" -d '-1 days'`
CURDATE=`date`

# EMAIL RELATED STUFF
TO1="aacable@hotmail.com"
GMAILID="YOURGMAILID@gmail.com"
GMAILPASS="GMAIL-PASSWORD"
CONTENT_TYPE="text/html"

# LOG FILES
FILE="/tmp/dealer_renewal_today.html"
FINALFILE="/tmp/dealer_renewal_today_final.html"
CSHORT="ZAIB-COMPANY"
COMPANY="$CSHORT_Pvt_Ltd.<br>This System is powered by Syed_Jahanzaib aacable@hotmail.com"
BODY_TITLE="<h1>Report&nbsp;For&nbsp;Dealer&nbsp;Account&nbsp;asof&nbsp;$YESTERDAY</h1>"


> $FILE
> $FINALFILE
> /tmp/temp.txt
echo "<pre>" > $FILE
echo "<b>$BODY_TITLE</b>" >> $FILE
echo "<b>DEALER&nbsp;            User's_Activated             Used_Amount             &Tab;Balance</b><br>" >> $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 -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.managername, rm_invoices.username, rm_invoices.date, rm_invoices.expiration, rm_invoices.service, rm_invoices.amount, rm_invoices.price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$YESTERDAY' AND date <= '$TODAY' AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0'  OR invgroup = '1' ) AND invnum != '' AND rm_invoices.managername = '$DEALER' ORDER BY id LIMIT 0, 500;" | sed '/credited/d' | wc -l`
USEDAMOUNT=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.price, rm_invoices.id, rm_invoices.invnum, rm_invoices.managername, rm_invoices.username, rm_invoices.date, rm_invoices.bytesdl, rm_invoices.bytesul, rm_invoices.bytescomb, rm_invoices.downlimit, rm_invoices.uplimit, rm_invoices.comblimit, rm_invoices.time, rm_invoices.uptimelimit, rm_invoices.days, rm_invoices.expiration, rm_invoices.comment, rm_invoices.service, rm_invoices.amount, rm_invoices.paid, rm_invoices.paymentopt, rm_invoices.paymode, rm_invoices.tax, rm_invoices.balance, rm_invoices.invgroup FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$YESTERDAY' AND date <= '$TODAY' AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0'  OR invgroup = '1' )  AND invnum != '' AND rm_invoices.managername = '$DEALER'  ORDER BY id  LIMIT 0, 500;" | 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';" | sed '/credited/d' |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 -u$SQLUSER -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 >= '$YESTERDAY' AND date <= '$TODAY'  AND  rm_invoices.managername = '$DEALER' AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0' ) AND invnum != ''  ORDER BY id LIMIT 0, 50;" | 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  &Tab;$BALANCE
------------------------------------------------------------------------"  >> $FILE
else

# PRINT ALL GATHERED DATA INTO FILE
echo "<b>$DEALER</b>  $ACTIVEUSERSNO  $USEDAMOUNT  &Tab;$BALANCE
<br>
Details&nbsp;of&nbsp;Services&nbsp;Activated:<br>Qty&Tab;Service&nbsp;Name<br>
$SRV
<br>------------------------------------------------------------------------" >> $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$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 >= '$YESTERDAY' AND date <= '$TODAY'  AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0' ) AND invnum != ''  ORDER BY id LIMIT 0, 50;" | sed '/credited/d' | awk '{print $1}' | wc -l`
SALES=`mysql -uroot -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$YESTERDAY' AND date <= '$TODAY'  AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0' ) AND invnum != ''  ORDER BY id LIMIT 0, 50;" | awk '{ sum+=$1} END {print sum}'`
echo "Total Users Activated/Renewed on $YESTERDAY     = <b>$TOTNO</b>" >> $FINALFILE
echo "Total SALES Done on $YESTERDAY                  = <b>$SALES</b>" >> $FINALFILE
echo "<br><b>$COMPANY</b>" >> $FINALFILE
echo "Generated on $CURDATE" >> $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: $CSHORT DEALERS DAILY BILLING INFO for $YESTERDAY" -o tls=yes -s smtp.gmail.com:587 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$FINALFILE  -o message-content-type=$CONTENT_TYPE

# Print and copy files as sales.html into www folder so any1 can view from web browser
cat $FINALFILE
cp $FINALFILE /var/www/sales.html

 

Install sendEmail Tool

mkdir /temp
cd /temp
wget http://caspian.dotconf.net/menu/Software/SendEmail/sendEmail-v1.56.tar.gz
tar zxvf sendEmail-v1.56.tar.gz
cd sendEmail-v1.56/

ADD SUPPORTING LIBRARY

For UBUNTU [Life is really easy on ubuntu but with some glitches)

apt-get -y install libio-socket-ssl-perl libnet-ssleay-perl perl

For CENTOS

yum -y install perl perl-Crypt-SSLeay perl-IO-Socket-SSL

TEST SENDING EMAIL

Try to send email using command line: Example

/temp/sendEmail-v1.56/sendEmail -t TO_YOURMAIL@hotmail.com -u "Test Email" -s smtp.gmail.com:587 -xu YOURMGAILID@gmail.com -xp YOURGMAILPASSWORD -f  YOURMGAILIDgmail.com -o tls=yes

If you get message something like “sendEmail[xxxx]: Email was sent successfully!”, then you are good to GO LIVE !


 

Regard’s

Syed Jahanzaib

Advertisements

10 Comments »

  1. Dear sir, i need to add script to radiusmanager that changes user speed after consuming his gb limit without disconnecting his account and stop account disconnect after using all his limit. if possible email me and ill pay for this script.thanks

    Sent from my Samsung device

    Like

    Comment by tareckinho — August 20, 2015 @ 4:02 PM

    • in general not possible.
      Once the user DAILY GB limit crossed, he will be disconnected, so that next service shoudl activated for the rest of the day.

      Like

      Comment by Syed Jahanzaib / Pinochio~:) — August 21, 2015 @ 10:41 AM

  2. Very good. ¡¡..

    Like

    Comment by Juan Carlos — August 20, 2015 @ 6:59 PM

  3. salam jhanzaib,
    dear jhanzaib i need u to reply on this post https://aacable.wordpress.com/2015/06/15/sending-email-on-router-reboot-with-logs-and-lastseen-time/#comments
    atleast tell me if u r helping to solve this issue or not
    if yes then how much i have to wait, i;ll wait but please reply me.
    my problem is
    i follow this post https://aacable.wordpress.com/2011/08/29/mikrotik-4-wan-load-balancing-using-pcc-with-pppoe-server-complete-script/
    after this my squid box not work properly as it was working,
    then i do too much google also i try ur other ways to redirect http traffic to squid from MT
    https://aacable.wordpress.com/2011/07/21/mikrotik-howto-redirect-http-traffic-to-squid-with-original-source-client-ip/
    and two more post by u but i failed to gain my goal,
    when i check access.log it shows some request of web sites with client source address but some time not in that time i got timeout error in browser
    1 another thing i have to mention that when i access my PTCL router page when MT is DNAT to squid its opening normally from squid every time
    but it hangs on browsing, i guess MT not working properly in DNAT to squid
    please correct me if i m wrong or atleast reply me,
    should i have to wait for it if yes then how long bcoz i cant leave my network without squid
    i just want to solve https broken issue when PCC loadbalancing happening if u have any other way to gain this goal then please let me know i will follow that
    i have 1 MTx86 os with 14 port lan card
    1 CRS series 24 port switch+router.
    i glad if u reply me here or in older posts.
    thanking you, 🙂

    Like

    Comment by Rehmat Ali Gulwating — August 23, 2015 @ 9:05 PM

  4. Hello Dear Sir Syed Jahanzaib

    sir main multan se Jalil Abbas m NetCable Chala raha hoo menay abhi Wateen se Fiber par connection liya hai pehlay to PTCL chal rahe thay
    to sir problem yeh hai k Wateen ka Self Care Portal login system hai
    jab tak login nahi karo to net nahi chalta aksar bar bar logout ho jata hai
    to bar bar login karna parta hai sir please mujhe koe tareeka btayen k
    jis se har 6 hr ya 12 hr k baad auto login ho jaye i mean k mere pass ubuntu bhi chal raha hai proxy cache server ubuntu 10.04 ya to us m koe python Script ban jaye auto login ya phir mikrotik m bas auto login hota rahe
    har 6 hr ya phir 12 hr baad so please help me i hope k aap k pass is koe
    hull hoga main Wait karo ga ap k reply ka

    Like

    Comment by Jalil Abbas — September 3, 2015 @ 5:22 PM

    • you have to first contact wateen if they support API or script base login support then you can use auto login feature.

      possibly there could be a way to auto login but it really depends on the operator network. its possible.

      Like

      Comment by Syed Jahanzaib / Pinochio~:) — September 4, 2015 @ 9:12 AM

  5. Dear Sir,
    I have done as you instructed in this tutorial. But I had set the services as per day pricing. Like 10 for monthly 300 plan. Its only showing 10 when users are credited for 300 with amount 30. What I have to change to get 300 without changing the services.

    Like

    Comment by Arjun Neupane — September 30, 2015 @ 12:19 PM

  6. dear jahanziab Vhai, i forget my mysql password do you know what is default password and i need to setup payment gateway but nobody setup this in my country and DMA want lot of money. can you help me.

    Like

    Comment by santonu — April 26, 2016 @ 5:58 PM

  7. Dear Boss
    Check this
    https://zonatsolutions.blogspot.com/p/ip-calc.html

    Like

    Comment by Talha Ali — November 20, 2016 @ 2:10 AM


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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: