Subversion Repositories SmartDukaan

Rev

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