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