Subversion Repositories SmartDukaan

Rev

Rev 5726 | Rev 5728 | 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 
 
5726 anupam.sin 26 
MAILTO = ['anupam.singh@shop2020.in']
27 
#['rajneesharora@spiceretail.co.in', 'pankaj.kankar@shop2020.in', 'ashutosh.saxena@shop2020.in', 'anupam.singh@shop2020.in']
5652 anupam.sin 28 
SENDER = "cnc.center@shop2020.in"
29 
PASSWORD = "5h0p2o2o"
30 
SUBJECT = "Previous Day Accessory Sales Report"
31 
SMTP_SERVER = "smtp.gmail.com"
32 
SMTP_PORT = 587
33 
 
34 
TMP_FILE="/tmp/previous_accessory_sales.xls"
35 
 
36 
def getDbConnection():
37 
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
38 
 
39 
def closeConnection(conn):
40 
    conn.close()
41 
 
42 
def getProductSaleData():
5666 anupam.sin 43 
    selectSql = '''SELECT i.parent_category, i.brand, IFNULL(i.model_name, ''),
44 
                    IFNULL(i.model_number, ''), i.color, sum(quantity) AS quantity, sum(total_amount) AS totalAmount
5652 anupam.sin 45 
                    FROM sales s join item i on (i.id = s.item_id)
46 
                    JOIN datedim d on (d.date_id = s.date_id)
47 
                    JOIN orderstatus os on (s.status = os.status)
48 
                    WHERE d.fulldate in (DATE_SUB(curdate(), INTERVAL 1 DAY))
5727 anupam.sin 49 
                    AND os.statusGroup in ('Delivered', 'In process', 'Refunded', 'Return in process', 'Unused')
5652 anupam.sin 50 
                    AND i.parent_category = 'Mobile Accessories'
5727 anupam.sin 51 
                    AND os.statusSubGroup not in ('Cancellation pending',  'Cod verification pending')
5666 anupam.sin 52 
                    GROUP BY i.parent_category, i.category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), i.color
53 
                    ORDER BY i.parent_category, i.brand;
5652 anupam.sin 54 
                '''
55 
 
56 
    conn = getDbConnection()
57 
    monthdatesmap = {}
58 
    prodsalesmap = {}
59 
    try:
60 
        # prepare a cursor object using cursor() method
61 
        cursor = conn.cursor()
62 
        # Execute the SQL command
63 
        # Fetch source id.
64 
        cursor.execute(selectSql)
65 
        result = cursor.fetchall()
66 
        msg =   """\
67 
                    <html>
68 
                    <body>\n<table border="1">
69 
                    \n<thead>\n
70 
                    <th>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>
5666 anupam.sin 76 
                    <th>Value\n</th>
5652 anupam.sin 77 
                    \n</thead>
78 
                """
5666 anupam.sin 79 
        column = 0
80 
        grossTotal = 0
81 
        grossQuantity = 0
82 
        msg = msg + '<tr><td colspan="5"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr>'
5652 anupam.sin 83 
        for r in result:
84 
            msg = msg + '<tr>'
5666 anupam.sin 85 
            for data in r:
86 
                if column == 6 :
87 
                    grossTotal += data
88 
                if column == 5 :
89 
                    grossQuantity += data
90 
                msg = msg + '<td>' + str(data) + '</td>'
91 
                column += 1
92 
            column = 0
5652 anupam.sin 93 
            msg = msg + '</tr>'
5666 anupam.sin 94 
        msg = msg + '</table>\n</body>\n</html>'
95 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
96 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
5652 anupam.sin 97 
    except Exception as e:
98 
      print "Error: unable to fetch data"
99 
      print e
100 
 
101 
    return msg, result
102 
 
103 
def createXlsReport(result):
5666 anupam.sin 104 
    workbook = xlwt.Workbook()
5652 anupam.sin 105 
    worksheet = workbook.add_sheet("Sheet 1")
5666 anupam.sin 106 
    boldStyle = xlwt.XFStyle()
107 
    f = xlwt.Font()
5652 anupam.sin 108 
    f.bold = True
109 
    boldStyle.font = f
110 
 
111 
    datecolmap = {}
112 
    column = 0
113 
    row = 0
114 
 
115 
    worksheet.write(row, 0, 'Category', boldStyle)
5666 anupam.sin 116 
    worksheet.write(row, 1, 'Brand', boldStyle)
117 
    worksheet.write(row, 2, 'Model Name', boldStyle)
118 
    worksheet.write(row, 3, 'Model Number', boldStyle)
119 
    worksheet.write(row, 4, 'Color', boldStyle)
120 
    worksheet.write(row, 5, 'Quantity', boldStyle)
121 
    worksheet.write(row, 6, 'Value', boldStyle)
5652 anupam.sin 122 
 
5666 anupam.sin 123 
    row = 2
124 
    grossTotal = 0
125 
    grossQuantity = 0
5652 anupam.sin 126 
 
127 
    for r in result:
128 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
129 
        #dayofmonth = r[8]
130 
        for data in r :
5666 anupam.sin 131 
            if column == 6 :
132 
                grossTotal += data
133 
            if column == 5 :
134 
                grossQuantity += data
5652 anupam.sin 135 
            worksheet.write(row, column, str(data))
136 
            column += 1
137 
        column = 0
138 
        row += 1
5666 anupam.sin 139 
 
140 
    worksheet.write_merge(1, 1, 0, 4, 'Total')
141 
    worksheet.write(1, 5, str(grossQuantity), boldStyle)
142 
    worksheet.write(1, 6, str(grossTotal), boldStyle)
5652 anupam.sin 143 
    workbook.save(TMP_FILE)
144 
 
145 
def sendmail(message):
146 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
147 
    mailServer.ehlo()
148 
    mailServer.starttls()
149 
    mailServer.ehlo()
150 
 
151 
    # Create the container (outer) email message.
152 
    msg = MIMEMultipart()
153 
    msg['Subject'] = SUBJECT + ' - ' + date.today().isoformat()
154 
    msg['From'] = "bi@saholic.com"
155 
    msg['To'] = 'sku-recipients@saholic.com'
156 
    msg.preamble = SUBJECT + ' - ' + date.today().isoformat()
157 
    html_msg = MIMEText(message, 'html')
158 
    msg.attach(html_msg)
159 
 
160 
    fileMsg = MIMEBase('application','vnd.ms-excel')
161 
    fileMsg.set_payload(file(TMP_FILE).read())
162 
    encoders.encode_base64(fileMsg)
163 
    fileMsg.add_header('Content-Disposition','attachment;filename=Yesterday-Accessory-Sales' + ' - ' + date.today().isoformat() + '.xls')
164 
    msg.attach(fileMsg)
165 
 
166 
    mailServer.login(SENDER, PASSWORD)
167 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
168 
 
169 
def main():
170 
    message, result = getProductSaleData()
171 
    createXlsReport(result)
172 
    sendmail(message)
173 
 
174 
if __name__ == '__main__':
175 
    main()