Subversion Repositories SmartDukaan

Rev

Rev 10298 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
7713 anupam.sin 1 
'''
2 
Created on 08-Jul-2013
3 
 
4 
@author: anupam
5 
'''
6 
 
7 
import MySQLdb
8 
import datetime
9 
import smtplib
10 
import xlwt
7717 anupam.sin 11 
import traceback
7713 anupam.sin 12 
 
13 
from email import encoders
14 
from email.mime.text import MIMEText
15 
from email.mime.base import MIMEBase
16 
from email.mime.multipart import MIMEMultipart
17 
#from pyExcelerator import Workbook, Font, XFStyle
18 
from datetime import date
19 
 
20 
# Initialize db connection settings.
7717 anupam.sin 21 
DB_HOST = "192.168.190.114"
7713 anupam.sin 22 
DB_USER = "root"
23 
DB_PASSWORD = "shop2020"
7717 anupam.sin 24 
DB_NAME = "transaction"
7713 anupam.sin 25 
 
26 
# KEY NAMES
27 
SENDER = "cnc.center@shop2020.in"
28 
PASSWORD = "5h0p2o2o"
7717 anupam.sin 29 
SUBJECT = "Recharge report for week ending on " + date.today().isoformat()
7713 anupam.sin 30 
SMTP_SERVER = "smtp.gmail.com"
31 
SMTP_PORT = 587
32 
 
33 
TMP_FILE="/tmp/recharge_report.xls"
34 
 
35 
def getDbConnection():
36 
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
37 
 
38 
def closeConnection(conn):
39 
    conn.close()
40 
 
41 
def getProductSaleData():
42 
    selectSql = '''SELECT clusterEmail, s.name, COUNT(*) AS TransactionCount, SUM(amount) AS Amount
43 
                   FROM rechargetransaction r
44 
                   JOIN hotspotstore h ON h.id = r.storeId
45 
                   JOIN serviceprovider s ON s.id = r.operatorId
46 
                   WHERE transactionTime BETWEEN SUBDATE(CURDATE(), 7) AND CURDATE() AND r.status = 5
47 
                   GROUP BY clusterEmail, operatorId;
48 
                '''
49 
    conn = getDbConnection()
50 
    try:
51 
        # prepare a cursor object using cursor() method
52 
        cursor = conn.cursor()
53 
        # Execute the SQL command
54 
        # Fetch source id.
55 
        cursor.execute(selectSql)
56 
        result = cursor.fetchall()
57 
        msg =   """\
58 
                    <html>
59 
                    <body>\n<table border="1">
60 
                    \n<thead>\n
61 
                    <th>Operator</th>\n
62 
                    <th>Count</th>\n
63 
                    <th>Amount</th>\n
64 
                    </thead>\n
7717 anupam.sin 65 
                    <tr>
66 
                    <td colspan="1"><b>Total</b></td><td>======QuantityToBeReplaced======</td>
67 
                    <td>======ValueToBeReplaced======</td>
68 
                    </tr>
69 
                    <tr>
7713 anupam.sin 70 
                """
71 
        column = 0
72 
        grossTotal = 0
73 
        grossQuantity = 0
74 
        clusterData = []
75 
        currentEmail = ""
76 
        for r in result:
77 
            for data in r:
78 
                if column == 0:
79 
                    if currentEmail != data:
7717 anupam.sin 80 
                        #if email id has changed then we need to create XLS
7713 anupam.sin 81 
                        createXlsReport(clusterData)
7717 anupam.sin 82 
                        #and close the message
83 
                        msg = msg + '</tr></table>\n</body>\n</html>'
7713 anupam.sin 84 
                        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
85 
                        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
7717 anupam.sin 86 
                        #and send the mail
87 
                        sendmail(currentEmail, msg)
88 
 
89 
                        #Now we reset fields
7713 anupam.sin 90 
                        grossQuantity = 0
91 
                        grossTotal = 0
7717 anupam.sin 92 
                        clusterData = []
7713 anupam.sin 93 
                        currentEmail = data
7717 anupam.sin 94 
                        msg =   """\
95 
                                    <html>
96 
                                    <body>\n<table border="1">
97 
                                    \n<thead>\n
98 
                                    <th>Operator</th>\n
99 
                                    <th>Count</th>\n
100 
                                    <th>Amount</th>\n
101 
                                    </thead>\n
102 
                                    <tr>
103 
                                    <td colspan="1"><b>Total</b></td><td>======QuantityToBeReplaced======</td>
104 
                                    <td>======ValueToBeReplaced======</td>
105 
                                    </tr>
106 
                                    <tr>
107 
                                """
108 
                    #We need not do anything for this column so we just move on
109 
                    column += 1
110 
                    continue
111 
 
7713 anupam.sin 112 
                if column == 2 :
113 
                    grossQuantity += data
114 
                if column == 3 :
115 
                    grossTotal += data
116 
                msg = msg + '<td>' + str(data) + '</td>'
117 
                column += 1
7717 anupam.sin 118 
            msg = msg + '</tr><tr>'
119 
            #Append this row to a list which will be used to create a xls sheet per cluster
7713 anupam.sin 120 
            clusterData.append(r)
121 
            column = 0
122 
 
7717 anupam.sin 123 
        #After the last row we need to send mail for last cluster
7713 anupam.sin 124 
        createXlsReport(clusterData)
125 
        msg = msg + '</table>\n</body>\n</html>'
126 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
127 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
128 
        grossQuantity = 0
129 
        grossTotal = 0
130 
        sendmail(currentEmail, msg)
7717 anupam.sin 131 
 
7713 anupam.sin 132 
    except Exception as e:
133 
        print e
7717 anupam.sin 134 
        traceback.print_exc()
7713 anupam.sin 135 
 
136 
    return msg, result
137 
 
7717 anupam.sin 138 
def createXlsReport(resultSet):
139 
    if len(resultSet) == 0:
140 
        return
7713 anupam.sin 141 
    workbook = xlwt.Workbook()
142 
    worksheet = workbook.add_sheet("ClusterReport")
143 
    boldStyle = xlwt.XFStyle()
144 
    f = xlwt.Font()
145 
    f.bold = True
146 
    boldStyle.font = f
147 
    column = 0
148 
    row = 0
149 
 
150 
    worksheet.write(row, 0, 'Operator', boldStyle)
151 
    worksheet.write(row, 1, 'Count', boldStyle)
152 
    worksheet.write(row, 2, 'Amount', boldStyle)
153 
    row = 2
154 
    grossTotal = 0
155 
    grossQuantity = 0
156 
 
7717 anupam.sin 157 
    for r in resultSet:
7713 anupam.sin 158 
        for data in r :
7717 anupam.sin 159 
            if column == 0:
160 
                column += 1
161 
                continue
7713 anupam.sin 162 
            if column == 2 :
7717 anupam.sin 163 
                grossQuantity += int(data)
164 
            if column == 3 :
165 
                grossTotal += int(data)
166 
            worksheet.write(row, column - 1, str(data))
7713 anupam.sin 167 
            column += 1
168 
        column = 0
169 
        row += 1
170 
 
7717 anupam.sin 171 
    worksheet.write(1, 0, 'Total')
172 
    worksheet.write(1, 1, str(grossQuantity), boldStyle)
173 
    worksheet.write(1, 2, str(grossTotal), boldStyle)
7713 anupam.sin 174 
    workbook.save(TMP_FILE)
175 
 
176 
def sendmail(email, message):
7717 anupam.sin 177 
    if email == "":
178 
        return
7713 anupam.sin 179 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
180 
    mailServer.ehlo()
181 
    mailServer.starttls()
182 
    mailServer.ehlo()
183 
 
184 
    # Create the container (outer) email message.
185 
    msg = MIMEMultipart()
7722 anupam.sin 186 
    msg['Subject'] = SUBJECT + ' for ' + email
7713 anupam.sin 187 
    msg['From'] = "ClusterWiseReport@saholic.com"
188 
    msg['To'] = 'cluster-heads@saholic.com'
7722 anupam.sin 189 
    msg.preamble = SUBJECT + ' for ' + email
7713 anupam.sin 190 
    html_msg = MIMEText(message, 'html')
191 
    msg.attach(html_msg)
192 
 
193 
    fileMsg = MIMEBase('application','vnd.ms-excel')
194 
    fileMsg.set_payload(file(TMP_FILE).read())
195 
    encoders.encode_base64(fileMsg)
196 
    fileMsg.add_header('Content-Disposition','attachment;filename=recharge-report-for-week-ending-on-' + date.today().isoformat() + '.xls')
197 
    msg.attach(fileMsg)
7717 anupam.sin 198 
 
10399 rajveer 199 
    MAILTO = [email, 'kshitij.sood@shop2020.in', 'digamber.chauhan@spiceretail.co.in', 'adarsh.verma@spiceretail.co.in']
7713 anupam.sin 200 
    mailServer.login(SENDER, PASSWORD)
201 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
202 
 
203 
def main():
7717 anupam.sin 204 
    getProductSaleData()
7713 anupam.sin 205 
 
206 
if __name__ == '__main__':
9633 rajveer 207 
    main()