| 17137 |
naman |
1 |
'''
|
|
|
2 |
Created on 24-Sep-2015
|
|
|
3 |
|
|
|
4 |
@author: manish
|
|
|
5 |
'''
|
|
|
6 |
import _mysql
|
|
|
7 |
from datetime import date, datetime, timedelta
|
|
|
8 |
from email.mime.multipart import MIMEMultipart
|
|
|
9 |
from email.mime.text import MIMEText
|
|
|
10 |
from pymongo import MongoClient
|
|
|
11 |
import smtplib
|
|
|
12 |
import sys
|
|
|
13 |
import xlwt
|
|
|
14 |
|
|
|
15 |
import MySQLdb as mdb
|
|
|
16 |
from dtr.main import Store, sourceMap
|
|
|
17 |
from dtr.storage import Mysql
|
|
|
18 |
from dtr.utils import utils
|
|
|
19 |
import dtr.utils
|
|
|
20 |
|
|
|
21 |
|
|
|
22 |
# DataService.initialize()
|
|
|
23 |
# client = MongoClient('mongodb://localhost:27017/')
|
|
|
24 |
SENDER = "cnc.center@shop2020.in"
|
|
|
25 |
PASSWORD = "5h0p2o2o"
|
|
|
26 |
SUBJECT = "Summary"
|
|
|
27 |
SMTP_SERVER = "smtp.gmail.com"
|
|
|
28 |
SMTP_PORT = 587
|
|
|
29 |
client = MongoClient()
|
|
|
30 |
con = mdb.connect('localhost','root','shop2020','dtr')
|
|
|
31 |
|
|
|
32 |
def getSkuData(storeId, identifier):
|
| 17139 |
naman |
33 |
if storeId in (1,2,4,5,6,7):
|
| 17137 |
naman |
34 |
skuData = client.Catalog.MasterData.find_one({'identifier':identifier, 'source_id':storeId})
|
|
|
35 |
elif storeId == 3:
|
|
|
36 |
skuData = client.Catalog.MasterData.find_one({'secondaryIdentifier':identifier, 'source_id':storeId})
|
|
|
37 |
return skuData
|
|
|
38 |
|
|
|
39 |
def sendmail(email, message, title, *varargs):
|
|
|
40 |
if email == "":
|
|
|
41 |
return
|
|
|
42 |
mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
|
|
|
43 |
mailServer.ehlo()
|
|
|
44 |
mailServer.starttls()
|
|
|
45 |
mailServer.ehlo()
|
|
|
46 |
|
|
|
47 |
# Create the container (outer) email message.
|
|
|
48 |
msg = MIMEMultipart()
|
|
|
49 |
msg['Subject'] = title
|
|
|
50 |
msg.preamble = title
|
|
|
51 |
html_msg = MIMEText(message, 'html')
|
|
|
52 |
msg.attach(html_msg)
|
|
|
53 |
|
|
|
54 |
# email.append('amit.gupta@shop2020.in')
|
|
|
55 |
MAILTO = email
|
|
|
56 |
mailServer.login(SENDER, PASSWORD)
|
|
|
57 |
mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
|
|
|
58 |
|
|
|
59 |
def addToAllOrders(start_date, end_date=None):
|
|
|
60 |
if not end_date:
|
|
|
61 |
end_date = datetime.now()
|
|
|
62 |
else:
|
|
|
63 |
end_date = end_date + timedelta(days=1)
|
|
|
64 |
try:
|
|
|
65 |
cur = con.cursor()
|
| 17138 |
naman |
66 |
db=client.Dtr
|
| 17137 |
naman |
67 |
db1 = client.Catalog
|
|
|
68 |
cur.execute("delete from allorder where created_on >= %s and created_on < %s", (start_date, end_date))
|
|
|
69 |
con.commit()
|
|
|
70 |
results = Mysql.fetchResult('''
|
|
|
71 |
select ow.*, u.username, crm1.order_count, u.referrer, aua.city,aua.state,aua.pincode from order_view ow
|
|
|
72 |
left join users u on u.id = ow.user_id
|
|
|
73 |
left join (select user_id, count(*) as order_count from order_view where status in ('ORDER_CREATED','DETAIL_CREATED') group by user_id)as crm1 on ow.user_id = crm1.user_id
|
|
|
74 |
left join (select * from (select * from all_user_addresses order by source) s group by user_id)aua on aua.user_id=u.id
|
|
|
75 |
where (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) and u.activated = 1 and ow.status in ('ORDER_CREATED','DETAIL_CREATED');
|
| 17146 |
amit.gupta |
76 |
''')
|
| 17137 |
naman |
77 |
|
| 17145 |
amit.gupta |
78 |
query = """INSERT INTO allorder
|
|
|
79 |
(user_id, user_name, order_id, created_on, store_id, merchant_order_id, status, detailed_status, product_title, referrer, amount_paid, catalog_id, brand, model,category, deal_rank, max_nlc, min_nlc, dp, item_status, city, state, pincode, merchant_suborder_id,cashback_status,cashback_amount)
|
|
|
80 |
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
|
|
|
81 |
"""
|
| 17137 |
naman |
82 |
for result in results:
|
|
|
83 |
morder = db.merchantOrder.find_one({"orderId":result[0]})
|
|
|
84 |
if morder is not None:
|
|
|
85 |
subOrders = morder.get("subOrders")
|
|
|
86 |
if subOrders is not None:
|
|
|
87 |
for subOrder in subOrders:
|
|
|
88 |
user_id = result[1]
|
|
|
89 |
user_name= result[7]
|
|
|
90 |
order_id = result[0]
|
|
|
91 |
created_on =result[6]
|
|
|
92 |
store_id = sourceMap.get(result[2])
|
|
|
93 |
merchant_order_id = morder.get("merchantOrderId")
|
|
|
94 |
status = subOrder.get("status")
|
|
|
95 |
detailed_status = subOrder.get("detailedStatus")
|
|
|
96 |
product_title= subOrder.get("productTitle")
|
|
|
97 |
referrer = result[9]
|
|
|
98 |
if int(subOrder.get("quantity")) > 0:
|
|
|
99 |
amount_paid = int(subOrder.get("amountPaid"))/int(subOrder.get("quantity"))
|
|
|
100 |
skuData = getSkuData(morder.get("storeId"), subOrder.get("productCode"))
|
|
|
101 |
if morder.get("storeId") in (1,2,4,5):
|
|
|
102 |
skuData = db1.MasterData.find_one({'identifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
|
|
|
103 |
elif morder.get("storeId") == 3:
|
|
|
104 |
skuData = db1.MasterData.find_one({'secondaryIdentifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
|
|
|
105 |
if skuData is not None:
|
|
|
106 |
catalog_id = skuData.get("skuBundleId")
|
|
|
107 |
brand= skuData.get("brand")
|
|
|
108 |
model = skuData.get("model_name")
|
|
|
109 |
category = skuData.get("category")
|
|
|
110 |
deal_rank = subOrder.get("dealRank")
|
|
|
111 |
max_nlc = subOrder.get("maxNlc")
|
|
|
112 |
min_nlc = subOrder.get("minNlc")
|
| 17145 |
amit.gupta |
113 |
dp = subOrder.get("db")
|
|
|
114 |
item_status = utils.statusMap.get(subOrder.get("itemStatus"))
|
| 17137 |
naman |
115 |
else:
|
|
|
116 |
catalog_id = 'None'
|
|
|
117 |
brand= 'None'
|
|
|
118 |
model = 'None'
|
|
|
119 |
category = 'None'
|
|
|
120 |
deal_rank = 'None'
|
|
|
121 |
max_nlc = 'None'
|
|
|
122 |
min_nlc = 'None'
|
|
|
123 |
dp = 'None'
|
|
|
124 |
item_status = 'None'
|
|
|
125 |
merchant_suborder_id = subOrder.get("merchantSubOrderId")
|
|
|
126 |
cashback_status = subOrder.get("cashBackStatus")
|
|
|
127 |
cashback_amount = subOrder.get("cashBackAmount")
|
|
|
128 |
city = result[10]
|
|
|
129 |
state = result[11]
|
|
|
130 |
pincode = result[12]
|
|
|
131 |
|
|
|
132 |
print user_id
|
| 17148 |
amit.gupta |
133 |
values = (user_id, user_name, order_id, created_on, store_id, merchant_order_id, status, detailed_status, product_title, referrer, amount_paid, catalog_id, brand, model,category, deal_rank, max_nlc, min_nlc, dp, item_status, city, state, pincode,merchant_suborder_id,cashback_status,cashback_amount)
|
|
|
134 |
cur.execute(query,values)
|
| 17137 |
naman |
135 |
con.commit()
|
|
|
136 |
except _mysql.Error, e:
|
|
|
137 |
|
|
|
138 |
print "Error %d: %s" % (e.args[0], e.args[1])
|
|
|
139 |
sys.exit(1)
|
|
|
140 |
|
|
|
141 |
finally:
|
|
|
142 |
|
|
|
143 |
if con:
|
|
|
144 |
con.close()
|
|
|
145 |
|
|
|
146 |
|
|
|
147 |
def summaryByBrandAndStore():
|
|
|
148 |
con = mdb.connect('localhost','root','shop2020','dtr')
|
|
|
149 |
try:
|
|
|
150 |
cur = con.cursor()
|
|
|
151 |
book = xlwt.Workbook()
|
|
|
152 |
sheet1 = book.add_sheet("data_by_brand")
|
|
|
153 |
sheet2 = book.add_sheet("data_by_store")
|
|
|
154 |
sheet1.write(0,0,"Brand")
|
|
|
155 |
sheet1.write(0,1,"Amount")
|
|
|
156 |
sheet1.write(0,2,"Quantity")
|
|
|
157 |
sheet1.write(0,3,"No. of order")
|
|
|
158 |
|
|
|
159 |
sheet2.write(0,0,"Store")
|
|
|
160 |
sheet2.write(0,1,"Amount")
|
|
|
161 |
sheet2.write(0,2,"Quantity")
|
|
|
162 |
sheet2.write(0,3,"No. of order")
|
|
|
163 |
sheet2.write(0,4,"MTD Amount")
|
|
|
164 |
sheet2.write(0,5,"MTD Quantity")
|
|
|
165 |
sheet2.write(0,6,"MTD No. of order")
|
|
|
166 |
|
|
|
167 |
|
|
|
168 |
# ----Data by brand---
|
|
|
169 |
tbody = []
|
|
|
170 |
rowtemplate_brand="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td></tr>"
|
|
|
171 |
mailBodyTemplate_brand="""
|
|
|
172 |
<html>
|
|
|
173 |
<body>
|
|
|
174 |
<table cellspacing="0" border="1" style="text-align:right">
|
|
|
175 |
<thead>
|
|
|
176 |
<tr>
|
|
|
177 |
<th colspan='4' style="text-align:center">Summary by Brand</th>
|
|
|
178 |
</tr>
|
|
|
179 |
<tr>
|
|
|
180 |
<th style="text-align:center">Brand</th>
|
|
|
181 |
<th style="text-align:center">Amount</th>
|
|
|
182 |
<th style="text-align:center">Quantity</th>
|
|
|
183 |
<th style="text-align:center">Number of Order</th>
|
|
|
184 |
</tr>
|
|
|
185 |
</thead>
|
|
|
186 |
<tbody>
|
|
|
187 |
{0}
|
|
|
188 |
</tbody>
|
|
|
189 |
</table><br><br>
|
|
|
190 |
</body>
|
|
|
191 |
</html>
|
|
|
192 |
"""
|
|
|
193 |
|
| 17149 |
amit.gupta |
194 |
cur.execute('''select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders
|
| 17150 |
amit.gupta |
195 |
from allorder where created_on >= CURDATE() - interval 1 day and created_on < CURDATE() group by brand order by count(brand) desc limit 10
|
| 17149 |
amit.gupta |
196 |
''')
|
| 17137 |
naman |
197 |
rows = cur.fetchall()
|
|
|
198 |
row = 1
|
|
|
199 |
alldata =''
|
|
|
200 |
for data in rows:
|
|
|
201 |
sheet1.write(row,0,data[0])
|
|
|
202 |
sheet1.write(row,1,data[1])
|
|
|
203 |
sheet1.write(row,2,data[2])
|
|
|
204 |
sheet1.write(row,3,data[3])
|
|
|
205 |
row +=1
|
|
|
206 |
if len(alldata)>0:
|
|
|
207 |
alldata = alldata + ",'"+ data[0]+"'"
|
|
|
208 |
else:
|
|
|
209 |
alldata = "'"+data[0]+"'"
|
|
|
210 |
tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))
|
|
|
211 |
|
|
|
212 |
print alldata
|
| 17149 |
amit.gupta |
213 |
cur.execute('''select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder
|
| 17151 |
amit.gupta |
214 |
where created_on >= CURDATE() - interval 1 day and created_on < CURDATE() and brand not in("+alldata+")
|
| 17149 |
amit.gupta |
215 |
''')
|
| 17137 |
naman |
216 |
row_other = cur.fetchall()
|
|
|
217 |
print row_other
|
|
|
218 |
for data in row_other:
|
|
|
219 |
sheet1.write(row,0,'Other')
|
|
|
220 |
sheet1.write(row,1,data[0])
|
|
|
221 |
sheet1.write(row,2,data[1])
|
|
|
222 |
sheet1.write(row,3,data[2])
|
|
|
223 |
tbody.append(rowtemplate_brand.format('Other',data[0],data[1],data[2]))
|
|
|
224 |
tbody.append(rowtemplate_brand.format('','','',''))
|
|
|
225 |
tbody.append(rowtemplate_brand.format('','<b>MTD Amount</b>','<b>MTD Quantity</b>','<b>MTD No. of Order</b>'))
|
|
|
226 |
|
|
|
227 |
# Brand by MTD
|
| 17150 |
amit.gupta |
228 |
cur.execute('''select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders
|
|
|
229 |
from allorder where created_on >= CURDATE() - interval DAY(CURDATE()-INTERVAL 1 day) day and
|
|
|
230 |
created_on < CURDATE() group by brand order by count(brand) desc limit 10''')
|
| 17137 |
naman |
231 |
rows = cur.fetchall()
|
|
|
232 |
row += 2
|
|
|
233 |
alldata =''
|
|
|
234 |
sheet1.write(row,0,"Brands")
|
|
|
235 |
sheet1.write(row,1,"MTD Amount")
|
|
|
236 |
sheet1.write(row,2,"MTD Quantity")
|
|
|
237 |
sheet1.write(row,3,"MTD No. of order")
|
|
|
238 |
row +=1
|
|
|
239 |
|
|
|
240 |
for data in rows:
|
|
|
241 |
sheet1.write(row,0,data[0])
|
|
|
242 |
sheet1.write(row,1,data[1])
|
|
|
243 |
sheet1.write(row,2,data[2])
|
|
|
244 |
sheet1.write(row,3,data[3])
|
|
|
245 |
row +=1
|
|
|
246 |
if len(alldata)>0:
|
|
|
247 |
alldata = alldata + ",'"+ data[0]+"'"
|
|
|
248 |
else:
|
|
|
249 |
alldata = "'"+data[0]+"'"
|
|
|
250 |
tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))
|
|
|
251 |
|
|
|
252 |
print alldata
|
| 17150 |
amit.gupta |
253 |
cur.execute(''''select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder where
|
|
|
254 |
created_on >= CURDATE() - interval DAY(CURDATE()-INTERVAL 1 day) day and
|
|
|
255 |
created_on < CURDATE() AND brand not in("+alldata+")''')
|
| 17137 |
naman |
256 |
mtd_row_other = cur.fetchall()
|
|
|
257 |
print row_other
|
|
|
258 |
for data in mtd_row_other:
|
|
|
259 |
sheet1.write(row,0,'Other')
|
|
|
260 |
sheet1.write(row,1,data[0])
|
|
|
261 |
sheet1.write(row,2,data[1])
|
|
|
262 |
sheet1.write(row,3,data[2])
|
|
|
263 |
tbody.append(rowtemplate_brand.format('Other',data[0],data[1],data[2]))
|
|
|
264 |
|
|
|
265 |
message_by_brand = mailBodyTemplate_brand.format("".join(tbody))
|
|
|
266 |
# sendmail(["naman.kumar@shop2020.in"] ,message_by_brand, "Summary by brand","")
|
|
|
267 |
# Data by Store
|
|
|
268 |
# cur.execute("select store_id store,sum(amount_paid) amount,count(store_id) quantity ,count(distinct order_id) from allorder group by store_id order by store_id ASC;")
|
|
|
269 |
cur.execute("select x.*,a.store_id mtd_store,sum(a.amount_paid) mtd_amount,count(a.store_id) mtd_quantity, count(distinct a.order_id) mtd_num_of_order from allorder a left join (select store_id store,sum(amount_paid) amount,count(store_id) quantity,count(distinct order_id) num_of_orders from allorder where month(created_on) = 4 group by store_id ) as x on a.store_id=x.store group by a.store_id order by store_id ASC;")
|
|
|
270 |
rows = cur.fetchall()
|
|
|
271 |
new_row = 1
|
|
|
272 |
tbody =[]
|
|
|
273 |
rowtemplate="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td></tr>"
|
|
|
274 |
for data in rows:
|
|
|
275 |
print data[0]
|
|
|
276 |
sheet2.write(new_row,0,data[4])
|
|
|
277 |
sheet2.write(new_row,1,data[1])
|
|
|
278 |
sheet2.write(new_row,2,data[2])
|
|
|
279 |
sheet2.write(new_row,3,data[3])
|
|
|
280 |
sheet2.write(new_row,4,data[5])
|
|
|
281 |
sheet2.write(new_row,5,data[6])
|
|
|
282 |
sheet2.write(new_row,6,data[7])
|
|
|
283 |
new_row +=1
|
|
|
284 |
tbody.append(rowtemplate.format(data[4],data[1],data[2],data[3],data[5],data[6],data[7]))
|
|
|
285 |
|
|
|
286 |
# book.save("/home/manish/Desktop/Summary.xls")
|
|
|
287 |
|
|
|
288 |
mailBodyTemplate="""
|
|
|
289 |
<html>
|
|
|
290 |
<body>
|
|
|
291 |
<table cellspacing="0" border="1" style="text-align:right">
|
|
|
292 |
<thead>
|
|
|
293 |
<tr>
|
|
|
294 |
<th colspan='7' style="text-align:center">Summary by Store</th>
|
|
|
295 |
</tr>
|
|
|
296 |
<tr>
|
|
|
297 |
<th style="text-align:center">Brand</th>
|
|
|
298 |
<th style="text-align:center">Amount</th>
|
|
|
299 |
<th style="text-align:center">Quantity</th>
|
|
|
300 |
<th style="text-align:center">Number of Order</th>
|
|
|
301 |
<th style="text-align:center">MTD Amount</th>
|
|
|
302 |
<th style="text-align:center">MTD Quantity</th>
|
|
|
303 |
<th style="text-align:center">MTD Number of Order</th>
|
|
|
304 |
</tr>
|
|
|
305 |
</thead>
|
|
|
306 |
<tbody>
|
|
|
307 |
{0}
|
|
|
308 |
</tbody>
|
|
|
309 |
</table><br><br>
|
|
|
310 |
</body>
|
|
|
311 |
</html>
|
|
|
312 |
"""
|
|
|
313 |
message = mailBodyTemplate.format("".join(tbody))
|
|
|
314 |
return message+message_by_brand
|
|
|
315 |
except _mysql.Error, e:
|
|
|
316 |
|
|
|
317 |
print "Error %d: %s" % (e.args[0], e.args[1])
|
|
|
318 |
sys.exit(1)
|
|
|
319 |
|
|
|
320 |
finally:
|
|
|
321 |
|
|
|
322 |
if con:
|
|
|
323 |
con.close()
|
|
|
324 |
|
|
|
325 |
if __name__ == '__main__':
|
|
|
326 |
addToAllOrders(date(2015,1,1))
|
|
|
327 |
#summaryByBrandAndStore()
|