Subversion Repositories SmartDukaan

Rev

Rev 5653 | Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
5652 anupam.sin 1 
import json
2 
import urllib2, cookielib
3 
import MySQLdb
4 
import datetime
5 
import sys
6 
import smtplib
7 
 
8 
from email import encoders
9 
from email.mime.text import MIMEText
10 
from email.mime.base import MIMEBase
11 
from email.mime.multipart import MIMEMultipart
12 
from pyExcelerator import Workbook, Font, XFStyle
13 
from datetime import date
14 
 
15 
# Initialize db connection settings.
16 
DB_HOST = "localhost"
17 
DB_USER = "root"
18 
DB_PASSWORD = "shop2020"
19 
DB_NAME = "sales"
20 
 
21 
# KEY NAMES
22 
MONTHNAME = 'monthname'
23 
DATES = 'dates'
24 
 
25 
MAILTO = ['anupam.singh@shop2020.in']
26 
#['rajneesharora@spiceretail.co.in', 'pankaj.kankar@shop2020.in', 'ashutosh.saxena@shop2020.in', 'anupam.singh@shop2020.in']
27 
SENDER = "cnc.center@shop2020.in"
28 
PASSWORD = "5h0p2o2o"
29 
SUBJECT = "Previous Day Orders Report"
30 
SMTP_SERVER = "smtp.gmail.com"
31 
SMTP_PORT = 587
32 
 
33 
TMP_FILE="/tmp/previous_orders_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 i.parent_category, i.category, i.brand, IFNULL(i.model_name, ''),
43 
                    IFNULL(i.model_number, ''), i.color, sum(quantity) AS quantity
44 
                    FROM sales s join item i on (i.id = s.item_id)
45 
                    JOIN datedim d on (d.date_id = s.date_id)
46 
                    JOIN orderstatus os on (s.status = os.status)
47 
                    WHERE d.fulldate in (DATE_SUB(curdate(), INTERVAL 1 DAY))
48 
                    AND os.statusGroup in ('In process', 'Delivered', 'Cancelled', 'Return in process', 'Reshipped', 'Refunded')
49 
                    AND i.parent_category != 'Mobile Accessories'
50 
                    AND os.statusSubGroup != 'Cod verification failed'
51 
                    GROUP BY i.parent_category, i.category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), i.color;
52 
                '''
53 
 
54 
 
55 
    conn = getDbConnection()
56 
    monthdatesmap = {}
57 
    prodsalesmap = {}
58 
    try:
59 
        # prepare a cursor object using cursor() method
60 
        cursor = conn.cursor()
61 
        # Execute the SQL command
62 
        # Fetch source id.
63 
        cursor.execute(selectSql)
64 
        result = cursor.fetchall()
65 
        msg =   """\
66 
                    <html>
67 
                    <body>\n<table border="1">
68 
                    \n<thead>\n
69 
                    <th>Category\n</th>
70 
                    <th>Sub Category\n</th>
71 
                    <th>Brand\n</th>
72 
                    <th>Model Name\n</th>
73 
                    <th>Model Number\n</th>
74 
                    <th>Color\n</th>
75 
                    <th>Quantity\n</th>
76 
                    \n</thead>
77 
                """
78 
        for r in result:
79 
            #(parent, category, brand, model_name, model_number, color) = r[0:6]
80 
            #dayofmonth = r[8]
81 
            msg = msg + '<tr>'
82 
            for col in r:
83 
                msg = msg + '<td>' + str(col) + '</td>'
84 
            msg = msg + '</tr>'
85 
    except Exception as e:
86 
      print "Error: unable to fetch data"
87 
      print e
88 
 
89 
    msg = msg + '</table>\n</body>\n</html>'
90 
    return msg, result
91 
 
92 
def createXlsReport(result):
93 
    workbook = Workbook()
94 
    worksheet = workbook.add_sheet("Sheet 1")
95 
    boldStyle = XFStyle()
96 
    f = Font()
97 
    f.bold = True
98 
    boldStyle.font = f
99 
 
100 
    datecolmap = {}
101 
    column = 0
102 
    row = 0
103 
 
104 
    worksheet.write(row, 0, 'Category', boldStyle)
105 
    worksheet.write(row, 1, 'Sub Category', boldStyle)
106 
    worksheet.write(row, 2, 'Brand', boldStyle)
107 
    worksheet.write(row, 3, 'Model Name', boldStyle)
108 
    worksheet.write(row, 4, 'Model Number', boldStyle)
109 
    worksheet.write(row, 5, 'Color', boldStyle)
110 
    worksheet.write(row, 6, 'Quantity', boldStyle)
111 
 
112 
    row = 1
113 
 
114 
    for r in result:
115 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
116 
        #dayofmonth = r[8]
117 
        for data in r :
118 
            worksheet.write(row, column, str(data))
119 
            column += 1
120 
        column = 0
121 
        row += 1
122 
    workbook.save(TMP_FILE)
123 
 
124 
def sendmail(message):
125 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
126 
    mailServer.ehlo()
127 
    mailServer.starttls()
128 
    mailServer.ehlo()
129 
 
130 
    # Create the container (outer) email message.
131 
    msg = MIMEMultipart()
132 
    msg['Subject'] = SUBJECT + ' - ' + date.today().isoformat()
133 
    msg['From'] = "bi@saholic.com"
134 
    msg['To'] = 'sku-recipients@saholic.com'
135 
    msg.preamble = SUBJECT + ' - ' + date.today().isoformat()
136 
    html_msg = MIMEText(message, 'html')
137 
    msg.attach(html_msg)
138 
 
139 
    fileMsg = MIMEBase('application','vnd.ms-excel')
140 
    fileMsg.set_payload(file(TMP_FILE).read())
141 
    encoders.encode_base64(fileMsg)
142 
    fileMsg.add_header('Content-Disposition','attachment;filename=Last-Day-Sales' + ' - ' + date.today().isoformat() + '.xls')
143 
    msg.attach(fileMsg)
144 
 
145 
    mailServer.login(SENDER, PASSWORD)
146 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
147 
 
148 
def main():
149 
    message, result = getProductSaleData()
150 
    createXlsReport(result)
151 
    sendmail(message)
152 
 
153 
if __name__ == '__main__':
154 
    main()