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