Subversion Repositories SmartDukaan

Rev

Rev 5727 | Rev 5969 | 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 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():
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'
5727 anupam.sin 50 
                    AND os.statusSubGroup not in ('Cancellation pending',  'Cod verification pending')
5666 anupam.sin 51 
                    GROUP BY i.parent_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 
    conn = getDbConnection()
55 
    monthdatesmap = {}
56 
    prodsalesmap = {}
57 
    try:
58 
        # prepare a cursor object using cursor() method
59 
        cursor = conn.cursor()
60 
        # Execute the SQL command
61 
        # Fetch source id.
62 
        cursor.execute(selectSql)
63 
        result = cursor.fetchall()
64 
        msg =   """\
65 
                    <html>
66 
                    <body>\n<table border="1">
67 
                    \n<thead>\n
68 
                    <th>Category\n</th>
69 
                    <th>Brand\n</th>
70 
                    <th>Model Name\n</th>
71 
                    <th>Model Number\n</th>
72 
                    <th>Color\n</th>
73 
                    <th>Quantity\n</th>
5666 anupam.sin 74 
                    <th>Value\n</th>
5652 anupam.sin 75 
                    \n</thead>
76 
                """
5666 anupam.sin 77 
        column = 0
78 
        grossTotal = 0
79 
        grossQuantity = 0
80 
        msg = msg + '<tr><td colspan="5"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr>'
5652 anupam.sin 81 
        for r in result:
82 
            msg = msg + '<tr>'
5666 anupam.sin 83 
            for data in r:
84 
                if column == 6 :
85 
                    grossTotal += data
86 
                if column == 5 :
87 
                    grossQuantity += data
88 
                msg = msg + '<td>' + str(data) + '</td>'
89 
                column += 1
90 
            column = 0
5652 anupam.sin 91 
            msg = msg + '</tr>'
5666 anupam.sin 92 
        msg = msg + '</table>\n</body>\n</html>'
93 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
94 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
5652 anupam.sin 95 
    except Exception as e:
96 
      print "Error: unable to fetch data"
97 
      print e
98 
 
99 
    return msg, result
100 
 
101 
def createXlsReport(result):
5666 anupam.sin 102 
    workbook = xlwt.Workbook()
5652 anupam.sin 103 
    worksheet = workbook.add_sheet("Sheet 1")
5666 anupam.sin 104 
    boldStyle = xlwt.XFStyle()
105 
    f = xlwt.Font()
5652 anupam.sin 106 
    f.bold = True
107 
    boldStyle.font = f
108 
 
109 
    datecolmap = {}
110 
    column = 0
111 
    row = 0
112 
 
113 
    worksheet.write(row, 0, 'Category', boldStyle)
5666 anupam.sin 114 
    worksheet.write(row, 1, 'Brand', boldStyle)
115 
    worksheet.write(row, 2, 'Model Name', boldStyle)
116 
    worksheet.write(row, 3, 'Model Number', boldStyle)
117 
    worksheet.write(row, 4, 'Color', boldStyle)
118 
    worksheet.write(row, 5, 'Quantity', boldStyle)
119 
    worksheet.write(row, 6, 'Value', boldStyle)
5652 anupam.sin 120 
 
5666 anupam.sin 121 
    row = 2
122 
    grossTotal = 0
123 
    grossQuantity = 0
5652 anupam.sin 124 
 
125 
    for r in result:
126 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
127 
        #dayofmonth = r[8]
128 
        for data in r :
5666 anupam.sin 129 
            if column == 6 :
130 
                grossTotal += data
131 
            if column == 5 :
132 
                grossQuantity += data
5652 anupam.sin 133 
            worksheet.write(row, column, str(data))
134 
            column += 1
135 
        column = 0
136 
        row += 1
5666 anupam.sin 137 
 
138 
    worksheet.write_merge(1, 1, 0, 4, 'Total')
139 
    worksheet.write(1, 5, str(grossQuantity), boldStyle)
140 
    worksheet.write(1, 6, str(grossTotal), boldStyle)
5652 anupam.sin 141 
    workbook.save(TMP_FILE)
142 
 
143 
def sendmail(message):
144 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
145 
    mailServer.ehlo()
146 
    mailServer.starttls()
147 
    mailServer.ehlo()
148 
 
149 
    # Create the container (outer) email message.
150 
    msg = MIMEMultipart()
151 
    msg['Subject'] = SUBJECT + ' - ' + date.today().isoformat()
152 
    msg['From'] = "bi@saholic.com"
153 
    msg['To'] = 'sku-recipients@saholic.com'
154 
    msg.preamble = SUBJECT + ' - ' + date.today().isoformat()
155 
    html_msg = MIMEText(message, 'html')
156 
    msg.attach(html_msg)
157 
 
158 
    fileMsg = MIMEBase('application','vnd.ms-excel')
159 
    fileMsg.set_payload(file(TMP_FILE).read())
160 
    encoders.encode_base64(fileMsg)
161 
    fileMsg.add_header('Content-Disposition','attachment;filename=Last-Day-Sales' + ' - ' + date.today().isoformat() + '.xls')
162 
    msg.attach(fileMsg)
163 
 
164 
    mailServer.login(SENDER, PASSWORD)
165 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
166 
 
167 
def main():
168 
    message, result = getProductSaleData()
169 
    createXlsReport(result)
170 
    sendmail(message)
171 
 
172 
if __name__ == '__main__':
173 
    main()