Subversion Repositories SmartDukaan

Rev

Rev 5728 | Rev 6652 | Go to most recent revision | Details | Compare with Previous | 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
5666 anupam.sin 7 
import xlwt
5652 anupam.sin 8 
 
9 
from email import encoders
10 
from email.mime.text import MIMEText
11 
from email.mime.base import MIMEBase
12 
from email.mime.multipart import MIMEMultipart
5666 anupam.sin 13 
#from pyExcelerator import Workbook, Font, XFStyle
5652 anupam.sin 14 
from datetime import date
15 
 
16 
# Initialize db connection settings.
17 
DB_HOST = "localhost"
18 
DB_USER = "root"
19 
DB_PASSWORD = "shop2020"
20 
DB_NAME = "sales"
21 
 
22 
# KEY NAMES
23 
MONTHNAME = 'monthname'
24 
DATES = 'dates'
25 
 
5728 anupam.sin 26 
MAILTO = ['rajneesharora@spiceretail.co.in', 'pankaj.kankar@shop2020.in', 'ashutosh.saxena@shop2020.in', 'anupam.singh@shop2020.in']
5652 anupam.sin 27 
SENDER = "cnc.center@shop2020.in"
28 
PASSWORD = "5h0p2o2o"
29 
SUBJECT = "Previous Day Accessory Sales Report"
30 
SMTP_SERVER = "smtp.gmail.com"
31 
SMTP_PORT = 587
32 
 
33 
TMP_FILE="/tmp/previous_accessory_sales.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():
5666 anupam.sin 42 
    selectSql = '''SELECT i.parent_category, i.brand, IFNULL(i.model_name, ''),
43 
                    IFNULL(i.model_number, ''), i.color, sum(quantity) AS quantity, sum(total_amount) AS totalAmount
5652 anupam.sin 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))
5727 anupam.sin 48 
                    AND os.statusGroup in ('Delivered', 'In process', 'Refunded', 'Return in process', 'Unused')
5652 anupam.sin 49 
                    AND i.parent_category = 'Mobile Accessories'
5969 anupam.sin 50 
                    AND os.statusSubGroup not in ('Cancellation pending')
5666 anupam.sin 51 
                    GROUP BY i.parent_category, i.category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), i.color
52 
                    ORDER BY i.parent_category, i.brand;
5652 anupam.sin 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>Brand\n</th>
71 
                    <th>Model Name\n</th>
72 
                    <th>Model Number\n</th>
73 
                    <th>Color\n</th>
74 
                    <th>Quantity\n</th>
5666 anupam.sin 75 
                    <th>Value\n</th>
5652 anupam.sin 76 
                    \n</thead>
77 
                """
5666 anupam.sin 78 
        column = 0
79 
        grossTotal = 0
80 
        grossQuantity = 0
81 
        msg = msg + '<tr><td colspan="5"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr>'
5652 anupam.sin 82 
        for r in result:
83 
            msg = msg + '<tr>'
5666 anupam.sin 84 
            for data in r:
85 
                if column == 6 :
86 
                    grossTotal += data
87 
                if column == 5 :
88 
                    grossQuantity += data
89 
                msg = msg + '<td>' + str(data) + '</td>'
90 
                column += 1
91 
            column = 0
5652 anupam.sin 92 
            msg = msg + '</tr>'
5666 anupam.sin 93 
        msg = msg + '</table>\n</body>\n</html>'
94 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
95 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
5652 anupam.sin 96 
    except Exception as e:
97 
      print "Error: unable to fetch data"
98 
      print e
99 
 
100 
    return msg, result
101 
 
102 
def createXlsReport(result):
5666 anupam.sin 103 
    workbook = xlwt.Workbook()
5652 anupam.sin 104 
    worksheet = workbook.add_sheet("Sheet 1")
5666 anupam.sin 105 
    boldStyle = xlwt.XFStyle()
106 
    f = xlwt.Font()
5652 anupam.sin 107 
    f.bold = True
108 
    boldStyle.font = f
109 
 
110 
    datecolmap = {}
111 
    column = 0
112 
    row = 0
113 
 
114 
    worksheet.write(row, 0, 'Category', boldStyle)
5666 anupam.sin 115 
    worksheet.write(row, 1, 'Brand', boldStyle)
116 
    worksheet.write(row, 2, 'Model Name', boldStyle)
117 
    worksheet.write(row, 3, 'Model Number', boldStyle)
118 
    worksheet.write(row, 4, 'Color', boldStyle)
119 
    worksheet.write(row, 5, 'Quantity', boldStyle)
120 
    worksheet.write(row, 6, 'Value', boldStyle)
5652 anupam.sin 121 
 
5666 anupam.sin 122 
    row = 2
123 
    grossTotal = 0
124 
    grossQuantity = 0
5652 anupam.sin 125 
 
126 
    for r in result:
127 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
128 
        #dayofmonth = r[8]
129 
        for data in r :
5666 anupam.sin 130 
            if column == 6 :
131 
                grossTotal += data
132 
            if column == 5 :
133 
                grossQuantity += data
5652 anupam.sin 134 
            worksheet.write(row, column, str(data))
135 
            column += 1
136 
        column = 0
137 
        row += 1
5666 anupam.sin 138 
 
139 
    worksheet.write_merge(1, 1, 0, 4, 'Total')
140 
    worksheet.write(1, 5, str(grossQuantity), boldStyle)
141 
    worksheet.write(1, 6, str(grossTotal), boldStyle)
5652 anupam.sin 142 
    workbook.save(TMP_FILE)
143 
 
144 
def sendmail(message):
145 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
146 
    mailServer.ehlo()
147 
    mailServer.starttls()
148 
    mailServer.ehlo()
149 
 
150 
    # Create the container (outer) email message.
151 
    msg = MIMEMultipart()
152 
    msg['Subject'] = SUBJECT + ' - ' + date.today().isoformat()
153 
    msg['From'] = "bi@saholic.com"
154 
    msg['To'] = 'sku-recipients@saholic.com'
155 
    msg.preamble = SUBJECT + ' - ' + date.today().isoformat()
156 
    html_msg = MIMEText(message, 'html')
157 
    msg.attach(html_msg)
158 
 
159 
    fileMsg = MIMEBase('application','vnd.ms-excel')
160 
    fileMsg.set_payload(file(TMP_FILE).read())
161 
    encoders.encode_base64(fileMsg)
162 
    fileMsg.add_header('Content-Disposition','attachment;filename=Yesterday-Accessory-Sales' + ' - ' + date.today().isoformat() + '.xls')
163 
    msg.attach(fileMsg)
164 
 
165 
    mailServer.login(SENDER, PASSWORD)
166 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
167 
 
168 
def main():
169 
    message, result = getProductSaleData()
170 
    createXlsReport(result)
171 
    sendmail(message)
172 
 
173 
if __name__ == '__main__':
174 
    main()