| Line 64... |
Line 64... |
| 64 |
db=client.Dtr
|
64 |
db=client.Dtr
|
| 65 |
db1 = client.Catalog
|
65 |
db1 = client.Catalog
|
| 66 |
cur.execute("delete from allorder where created_on >= %s and created_on < %s", (start_date, end_date))
|
66 |
cur.execute("delete from allorder where created_on >= %s and created_on < %s", (start_date, end_date))
|
| 67 |
con.commit()
|
67 |
con.commit()
|
| 68 |
results = Mysql.fetchResult('''
|
68 |
results = Mysql.fetchResult('''
|
| 69 |
select ow.*, u.username, crm1.order_count, u.referrer, aua.city,aua.state,aua.pincode from order_view ow
|
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
|
| 70 |
left join users u on u.id = ow.user_id
|
70 |
left join users u on u.id = ow.user_id
|
| 71 |
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
|
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
|
| 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
|
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
|
| 73 |
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');
|
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 |
''')
|
74 |
''', start_date, end_date, start_date, end_date, 'emp%')
|
| 75 |
|
75 |
|
| 76 |
query = """INSERT INTO allorder
|
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)
|
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)
|
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 |
"""
|
79 |
"""
|
| Line 216... |
Line 216... |
| 216 |
|
216 |
|
| 217 |
message_by_brand = mailBodyTemplate_brand.format("".join(tbody))
|
217 |
message_by_brand = mailBodyTemplate_brand.format("".join(tbody))
|
| 218 |
|
218 |
|
| 219 |
# Data by Store
|
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;")
|
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;")
|
| - |
|
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
|
| 221 |
cur.execute("select name, c.* from store left join (select b.store_id, a.amount, a.quantity, a.orders, b.mtdamount,b.mtdquantity, b.mtdorders from (select store_id, sum(amount_paid) mtdamount, count(*) mtdquantity, count(distinct order_id) mtdorders from allorder where created_on >= CURDATE() - interval DAY(CURDATE()-INTERVAL 1 day) day group by store_id) b left join (select store_id, sum(amount_paid) amount, count(*) quantity, count(distinct order_id) orders from allorder where date(created_on)=curdate()-interval 1 day group by store_id) a on a.store_id=b.store_id) as c on name = c.store_id")
|
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''')
|
| 222 |
rows = cur.fetchall()
|
229 |
rows = cur.fetchall()
|
| 223 |
tbody =[]
|
230 |
tbody =[]
|
| 224 |
rowtemplate="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td></tr>"
|
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>"
|
| 225 |
for data in rows:
|
232 |
for data in rows:
|
| 226 |
tbody.append(rowtemplate.format(data[0],data[2] if data[2] else "-",data[3] if data[3] else "-",data[4] if data[4] else "-",
|
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 "-",
|
| Line 264... |
Line 271... |
| 264 |
|
271 |
|
| 265 |
if con:
|
272 |
if con:
|
| 266 |
con.close()
|
273 |
con.close()
|
| 267 |
|
274 |
|
| 268 |
if __name__ == '__main__':
|
275 |
if __name__ == '__main__':
|
| 269 |
#addToAllOrders(date(2015,1,1))
|
276 |
addToAllOrders(date(2015,1,1))
|
| 270 |
#summaryByBrandAndStore()
|
277 |
#summaryByBrandAndStore()
|
| 271 |
pass
|
278 |
pass
|
| 272 |
|
279 |
|