Subversion Repositories SmartDukaan

Rev

Rev 6713 | 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 
 
6713 anupam.sin 26 
MAILTO = ['rajneesharora@spiceretail.co.in', 'chaitnaya.vats@shop2020.in', 'ashutosh.saxena@shop2020.in', 'chandan.kumar@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, ''),
8207 rajveer 43 
                    IFNULL(i.model_number, ''), i.color, s.source, 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')
8207 rajveer 51 
                    GROUP BY i.parent_category, i.category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), i.color, s.source
5666 anupam.sin 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>
8207 rajveer 67 
                    <body>\n<h3>Website  ======WebsiteSale====== </h3><h3>Amazon  ======AmazonSale====== </h3>\n<table border="1">
5652 anupam.sin 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>
8207 rajveer 74 
                    <th>Source\n</th>
5652 anupam.sin 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
8207 rajveer 82 
        websiteSale = 0
83 
        amazonSale = 0
84 
        websiteQty = 0
85 
        amazonQty = 0
5666 anupam.sin 86 
        msg = msg + '<tr><td colspan="5"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr>'
5652 anupam.sin 87 
        for r in result:
88 
            msg = msg + '<tr>'
8207 rajveer 89 
	    source = 1
5666 anupam.sin 90 
            for data in r:
8207 rajveer 91 
                if column == 7 :
92 
                    grossTotal += data
93 
		    if source == 1:
94 
			websiteSale += data
95 
		    if source == 3:
96 
			amazonSale += data
5666 anupam.sin 97 
                if column == 6 :
98 
                    grossQuantity += data
8207 rajveer 99 
		    if source == 1:
100 
			websiteQty += data
101 
		    if source == 3:
102 
			amazonQty += data
103 
		if column == 5 :
104 
		    source = data
5666 anupam.sin 105 
                msg = msg + '<td>' + str(data) + '</td>'
106 
                column += 1
107 
            column = 0
5652 anupam.sin 108 
            msg = msg + '</tr>'
8207 rajveer 109 
 
5666 anupam.sin 110 
        msg = msg + '</table>\n</body>\n</html>'
111 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
112 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
8207 rajveer 113 
        #msg = msg.replace('======WebsiteQty=====', str(websiteQty))
114 
        #msg = msg.replace('======AmazonQty=====', str(amazonQty))
115 
        msg = msg.replace('======WebsiteSale======', "Sale - " + str(websiteSale) + "    Quantity - " + str(websiteQty))
116 
        msg = msg.replace('======AmazonSale======', "Sale - " + str(amazonSale) + "    Quantity - " + str(amazonQty))
5652 anupam.sin 117 
    except Exception as e:
118 
      print "Error: unable to fetch data"
119 
      print e
120 
 
121 
    return msg, result
122 
 
123 
def createXlsReport(result):
5666 anupam.sin 124 
    workbook = xlwt.Workbook()
5652 anupam.sin 125 
    worksheet = workbook.add_sheet("Sheet 1")
5666 anupam.sin 126 
    boldStyle = xlwt.XFStyle()
127 
    f = xlwt.Font()
5652 anupam.sin 128 
    f.bold = True
129 
    boldStyle.font = f
130 
 
131 
    datecolmap = {}
132 
    column = 0
133 
    row = 0
134 
 
135 
    worksheet.write(row, 0, 'Category', boldStyle)
5666 anupam.sin 136 
    worksheet.write(row, 1, 'Brand', boldStyle)
137 
    worksheet.write(row, 2, 'Model Name', boldStyle)
138 
    worksheet.write(row, 3, 'Model Number', boldStyle)
139 
    worksheet.write(row, 4, 'Color', boldStyle)
8207 rajveer 140 
    worksheet.write(row, 5, 'Source', boldStyle)
141 
    worksheet.write(row, 6, 'Quantity', boldStyle)
142 
    worksheet.write(row, 7, 'Value', boldStyle)
5652 anupam.sin 143 
 
5666 anupam.sin 144 
    row = 2
145 
    grossTotal = 0
146 
    grossQuantity = 0
5652 anupam.sin 147 
 
148 
    for r in result:
149 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
150 
        #dayofmonth = r[8]
151 
        for data in r :
8207 rajveer 152 
            if column == 7 :
153 
                grossTotal += data
5666 anupam.sin 154 
            if column == 6 :
155 
                grossQuantity += data
5652 anupam.sin 156 
            worksheet.write(row, column, str(data))
157 
            column += 1
158 
        column = 0
159 
        row += 1
8207 rajveer 160 
 
5666 anupam.sin 161 
    worksheet.write_merge(1, 1, 0, 4, 'Total')
8207 rajveer 162 
    worksheet.write(1, 6, str(grossQuantity), boldStyle)
163 
    worksheet.write(1, 7, str(grossTotal), boldStyle)
5652 anupam.sin 164 
    workbook.save(TMP_FILE)
165 
 
166 
def sendmail(message):
167 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
168 
    mailServer.ehlo()
169 
    mailServer.starttls()
170 
    mailServer.ehlo()
171 
 
172 
    # Create the container (outer) email message.
173 
    msg = MIMEMultipart()
174 
    msg['Subject'] = SUBJECT + ' - ' + date.today().isoformat()
175 
    msg['From'] = "bi@saholic.com"
176 
    msg['To'] = 'sku-recipients@saholic.com'
177 
    msg.preamble = SUBJECT + ' - ' + date.today().isoformat()
178 
    html_msg = MIMEText(message, 'html')
179 
    msg.attach(html_msg)
180 
 
181 
    fileMsg = MIMEBase('application','vnd.ms-excel')
182 
    fileMsg.set_payload(file(TMP_FILE).read())
183 
    encoders.encode_base64(fileMsg)
184 
    fileMsg.add_header('Content-Disposition','attachment;filename=Yesterday-Accessory-Sales' + ' - ' + date.today().isoformat() + '.xls')
185 
    msg.attach(fileMsg)
186 
 
187 
    mailServer.login(SENDER, PASSWORD)
188 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
189 
 
190 
def main():
191 
    message, result = getProductSaleData()
192 
    createXlsReport(result)
193 
    sendmail(message)
194 
 
195 
if __name__ == '__main__':
8207 rajveer 196 
    main()