Subversion Repositories SmartDukaan

Rev

Rev 7713 | Rev 7721 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 7713 Rev 7717
Line 2... Line 2...
2 
Created on 08-Jul-2013
 2 
Created on 08-Jul-2013
3 
 
 3 
 
4 
@author: anupam
 4 
@author: anupam
5 
'''
 5 
'''
6 
 
 6 
 
7 
import json
 - 
 
8 
import urllib2, cookielib
 - 
 
9 
import MySQLdb
 7 
import MySQLdb
10 
import datetime
 8 
import datetime
11 
import sys
 - 
 
12 
import smtplib
 9 
import smtplib
13 
import xlwt
 10 
import xlwt
- 
 
 11 
import traceback
14 
 
 12 
 
15 
from email import encoders
 13 
from email import encoders
16 
from email.mime.text import MIMEText
 14 
from email.mime.text import MIMEText
17 
from email.mime.base import MIMEBase
 15 
from email.mime.base import MIMEBase
18 
from email.mime.multipart import MIMEMultipart
 16 
from email.mime.multipart import MIMEMultipart
19 
#from pyExcelerator import Workbook, Font, XFStyle
 17 
#from pyExcelerator import Workbook, Font, XFStyle
20 
from datetime import date
 18 
from datetime import date
21 
 
 19 
 
22 
# Initialize db connection settings.
 20 
# Initialize db connection settings.
23 
DB_HOST = "localhost"
 21 
DB_HOST = "192.168.190.114"
24 
DB_USER = "root"
 22 
DB_USER = "root"
25 
DB_PASSWORD = "shop2020"
 23 
DB_PASSWORD = "shop2020"
26 
DB_NAME = "sales"
 24 
DB_NAME = "transaction"
27 
 
 25 
 
28 
# KEY NAMES
 26 
# KEY NAMES
29 
MONTHNAME = 'monthname'
 - 
 
30 
DATES = 'dates'
 - 
 
31 
 
 - 
 
32 
MAILTO = ['anupam.singh@shop2020.in']
 - 
 
33 
SENDER = "cnc.center@shop2020.in"
 27 
SENDER = "cnc.center@shop2020.in"
34 
PASSWORD = "5h0p2o2o"
 28 
PASSWORD = "5h0p2o2o"
35 
SUBJECT = "Recharge report for week ending on " + datetime.datetime.today()
 29 
SUBJECT = "Recharge report for week ending on " + date.today().isoformat()
36 
SMTP_SERVER = "smtp.gmail.com"
 30 
SMTP_SERVER = "smtp.gmail.com"
37 
SMTP_PORT = 587
31 
SMTP_PORT = 587
38 
 
 32 
 
39 
TMP_FILE="/tmp/recharge_report.xls"
 33 
TMP_FILE="/tmp/recharge_report.xls"
40 
 
 34 
 
Line 66... Line 60...
66 
                    \n<thead>\n
 60 
                    \n<thead>\n
67 
                    <th>Operator</th>\n
 61 
                    <th>Operator</th>\n
68 
                    <th>Count</th>\n
 62 
                    <th>Count</th>\n
69 
                    <th>Amount</th>\n
 63 
                    <th>Amount</th>\n
70 
                    </thead>\n
 64 
                    </thead>\n
- 
 
 65 
                    <tr>
- 
 
 66 
                    <td colspan="1"><b>Total</b></td><td>======QuantityToBeReplaced======</td>
- 
 
 67 
                    <td>======ValueToBeReplaced======</td>
- 
 
 68 
                    </tr>
- 
 
 69 
                    <tr>
71 
                """
70 
                """
72 
        column = 0
 71 
        column = 0
73 
        grossTotal = 0
 72 
        grossTotal = 0
74 
        grossQuantity = 0
 73 
        grossQuantity = 0
75 
        clusterData = []
 74 
        clusterData = []
76 
        currentEmail = ""
 75 
        currentEmail = ""
77 
        for r in result:
 76 
        for r in result:
78 
            msg = '<tr><td colspan="1"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr><tr>'
 - 
 
79 
            for data in r:
 77 
            for data in r:
80 
                if column == 0:
 78 
                if column == 0:
81 
                    if currentEmail != data:
 79 
                    if currentEmail != data:
- 
 
 80 
                        #if email id has changed then we need to create XLS
82 
                        createXlsReport(clusterData)
 81 
                        createXlsReport(clusterData)
- 
 
 82 
                        #and close the message
83 
                        msg = msg + '</table>\n</body>\n</html>'
 83 
                        msg = msg + '</tr></table>\n</body>\n</html>'
84 
                        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
 84 
                        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
85 
                        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
 85 
                        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
- 
 
 86 
                        #and send the mail
- 
 
 87 
                        sendmail(currentEmail, msg)
- 
 
 88
- 
 
 89 
                        #Now we reset fields
86 
                        grossQuantity = 0
 90 
                        grossQuantity = 0
87 
                        grossTotal = 0
 91 
                        grossTotal = 0
88 
                        sendmail(currentEmail, msg)
 92 
                        clusterData = []
89 
                        currentEmail = data
 93 
                        currentEmail = data
- 
 
 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
90
110 
                    continue
- 
 
 111
91 
                if column == 2 :
 112 
                if column == 2 :
92 
                    grossQuantity += data
 113 
                    grossQuantity += data
93 
                if column == 3 :
 114 
                if column == 3 :
94 
                    grossTotal += data
 115 
                    grossTotal += data
95 
                msg = msg + '<td>' + str(data) + '</td>'
 116 
                msg = msg + '<td>' + str(data) + '</td>'
96 
                column += 1
 117 
                column += 1
- 
 
 118 
            msg = msg + '</tr><tr>'
- 
 
 119 
            #Append this row to a list which will be used to create a xls sheet per cluster
97 
            clusterData.append(r)
 120 
            clusterData.append(r)
98 
            column = 0
 121 
            column = 0
99
122
- 
 
 123 
        #After the last row we need to send mail for last cluster
100 
        createXlsReport(clusterData)
 124 
        createXlsReport(clusterData)
101 
        msg = msg + '</table>\n</body>\n</html>'
 125 
        msg = msg + '</table>\n</body>\n</html>'
102 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
 126 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
103 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
 127 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
104 
        grossQuantity = 0
 128 
        grossQuantity = 0
105 
        grossTotal = 0
 129 
        grossTotal = 0
106 
        sendmail(currentEmail, msg)
 130 
        sendmail(currentEmail, msg)
- 
 
 131
107 
    except Exception as e:
 132 
    except Exception as e:
108 
        print "Error: unable to fetch data"
 - 
 
109 
        print e
 133 
        print e
- 
 
 134 
        traceback.print_exc()
110
135
111 
    return msg, result
 136 
    return msg, result
112 
 
 137 
 
113 
def createXlsReport(result):
 138 
def createXlsReport(resultSet):
- 
 
 139 
    if len(resultSet) == 0:
- 
 
 140 
        return
114 
    workbook = xlwt.Workbook()
 141 
    workbook = xlwt.Workbook()
115 
    worksheet = workbook.add_sheet("ClusterReport")
 142 
    worksheet = workbook.add_sheet("ClusterReport")
116 
    boldStyle = xlwt.XFStyle()
 143 
    boldStyle = xlwt.XFStyle()
117 
    f = xlwt.Font()
 144 
    f = xlwt.Font()
118 
    f.bold = True
 145 
    f.bold = True
Line 125... Line 152...
125 
    worksheet.write(row, 2, 'Amount', boldStyle)
 152 
    worksheet.write(row, 2, 'Amount', boldStyle)
126 
    row = 2
 153 
    row = 2
127 
    grossTotal = 0
 154 
    grossTotal = 0
128 
    grossQuantity = 0
 155 
    grossQuantity = 0
129
156
130 
    for r in result:
 157 
    for r in resultSet:
131 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
 - 
 
132 
        #dayofmonth = r[8]
 - 
 
133 
        for data in r :
 158 
        for data in r :
134 
            if column == 1 :
 159 
            if column == 0:
135 
                grossQuantity += data
 160 
                column += 1
- 
 
 161 
                continue
136 
            if column == 2 :
 162 
            if column == 2 :
- 
 
 163 
                grossQuantity += int(data)
- 
 
 164 
            if column == 3 :
137 
                grossTotal += data
 165 
                grossTotal += int(data)
138 
            worksheet.write(row, column, str(data))
 166 
            worksheet.write(row, column - 1, str(data))
139 
            column += 1
 167 
            column += 1
140 
        column = 0
 168 
        column = 0
141 
        row += 1
 169 
        row += 1
142
170
143 
    worksheet.write_merge(1, 1, 'Total')
 171 
    worksheet.write(1, 0, 'Total')
144 
    worksheet.write(1, 2, str(grossQuantity), boldStyle)
 172 
    worksheet.write(1, 1, str(grossQuantity), boldStyle)
145 
    worksheet.write(1, 3, str(grossTotal), boldStyle)
 173 
    worksheet.write(1, 2, str(grossTotal), boldStyle)
146 
    workbook.save(TMP_FILE)
 174 
    workbook.save(TMP_FILE)
147 
 
 175 
 
148 
def sendmail(email, message):
 176 
def sendmail(email, message):
- 
 
 177 
    if email == "":
- 
 
 178 
        return
149 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
 179 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
150 
    mailServer.ehlo()
 180 
    mailServer.ehlo()
151 
    mailServer.starttls()
 181 
    mailServer.starttls()
152 
    mailServer.ehlo()
 182 
    mailServer.ehlo()
153
183
154 
    # Create the container (outer) email message.
 184 
    # Create the container (outer) email message.
155 
    msg = MIMEMultipart()
 185 
    msg = MIMEMultipart()
156 
    msg['Subject'] = SUBJECT + ' for ' + email
 186 
    msg['Subject'] = SUBJECT
157 
    msg['From'] = "ClusterWiseReport@saholic.com"
 187 
    msg['From'] = "ClusterWiseReport@saholic.com"
158 
    msg['To'] = 'cluster-heads@saholic.com'
 188 
    msg['To'] = 'cluster-heads@saholic.com'
159 
    msg.preamble = SUBJECT + ' for ' + email
 189 
    msg.preamble = SUBJECT
160 
    html_msg = MIMEText(message, 'html')
 190 
    html_msg = MIMEText(message, 'html')
161 
    msg.attach(html_msg)
 191 
    msg.attach(html_msg)
162
192
163 
    fileMsg = MIMEBase('application','vnd.ms-excel')
 193 
    fileMsg = MIMEBase('application','vnd.ms-excel')
164 
    fileMsg.set_payload(file(TMP_FILE).read())
 194 
    fileMsg.set_payload(file(TMP_FILE).read())
165 
    encoders.encode_base64(fileMsg)
 195 
    encoders.encode_base64(fileMsg)
166 
    fileMsg.add_header('Content-Disposition','attachment;filename=recharge-report-for-week-ending-on-' + date.today().isoformat() + '.xls')
 196 
    fileMsg.add_header('Content-Disposition','attachment;filename=recharge-report-for-week-ending-on-' + date.today().isoformat() + '.xls')
167 
    msg.attach(fileMsg)
 197 
    msg.attach(fileMsg)
168 
 
 198
- 
 
 199 
    MAILTO = [email]
169 
    mailServer.login(SENDER, PASSWORD)
 200 
    mailServer.login(SENDER, PASSWORD)
170 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
 201 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
171 
 
 202 
 
172 
def main():
 203 
def main():
173 
    message, result = getProductSaleData()
 204 
    getProductSaleData()
174 
    createXlsReport(result)
 - 
 
175 
    sendmail(message)
 - 
 
176 
 
 205 
 
177 
if __name__ == '__main__':
 206 
if __name__ == '__main__':
178 
    main()
 207 
    main()
179 

            208