Saturday, October 3, 2015

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

10:37:00 AM    No comments
This post is my personnel notes (for future retrieval or reference) on a script that can be used to query billing system (in this exampleRadius 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.]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
# 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 .
# last updated: 25/08/2015
#!/bin/bash
#set -x
clear
# MYSQL USER ID PASSWORD
SQLUSER="root"
SQLPASS="YOUR_SQLPASS"
 
# 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="YOURGMAIL_ID@gmail.com"
GMAILPASS="YOURGMAIL_PASS"
CONTENT_TYPE="text/html"
 
# LOG FILES
FILE="/tmp/dealer_renewal_today.html"
FINALFILE="/tmp/dealer_renewal_today_final.html"
CSHORT="YOUR_COMPANY_NAME"
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
 
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 webbrowser
cat $FINALFILE
cp $FINALFILE /var/www/sales.html

Install sendEmail Tool

1
2
3
4
5
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)
1
apt-get -y install libio-socket-ssl-perl libnet-ssleay-perl perl
For CENTOS
1
yum -y install perl perl-Crypt-SSLeay perl-IO-Socket-SSL

TEST SENDING EMAIL

Try to send email using command line: Example
1
/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 !


0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)