| Line 99... |
Line 99... |
| 99 |
join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id
|
99 |
join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id
|
| 100 |
join transaction.lineitem li on li.order_id=o.id where o.status in (2,3,4,5,7,9,10,12,16,17,40)
|
100 |
join transaction.lineitem li on li.order_id=o.id where o.status in (2,3,4,5,7,9,10,12,16,17,40)
|
| 101 |
and o.total_amount > 1 and o.created_timestamp between CURDATE() - INTERVAL DAY(CURDATE())-1 DAY and CURDATE()
|
101 |
and o.total_amount > 1 and o.created_timestamp between CURDATE() - INTERVAL DAY(CURDATE())-1 DAY and CURDATE()
|
| 102 |
group by c.name with rollup;
|
102 |
group by c.name with rollup;
|
| 103 |
"""
|
103 |
"""
|
| - |
|
104 |
MIS_REPORT_GROUP_PROD_SQL="""
|
| - |
|
105 |
select concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product, sum(if(date(o.created_timestamp)=curdate()-1, 1, 0)) as lastDayQty,
|
| - |
|
106 |
sum(if(date(o.created_timestamp)=curdate()-1, o.total_amount , 0)) lastDayValue,
|
| - |
|
107 |
count(*) as MTDQty, sum(o.total_amount) as MTDValue from transaction.order o
|
| - |
|
108 |
join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id
|
| - |
|
109 |
join transaction.lineitem li on li.order_id=o.id where o.status in (2,3,4,5,7,9,10,12,16,17,40)
|
| - |
|
110 |
and o.total_amount > 1 and o.created_timestamp between CURDATE() - INTERVAL DAY(CURDATE())-1 DAY and CURDATE()
|
| - |
|
111 |
group by product with rollup;
|
| - |
|
112 |
"""
|
| 104 |
MIS_REPORT_SQL="""
|
113 |
MIS_REPORT_SQL="""
|
| 105 |
select c.code,c.name, w.displayName as warehouse, o.id, date(o.created_timestamp) as orderedon, os.statusName , date(mar.activationTime) activatedon,
|
114 |
select c.code,c.name, w.displayName as warehouse, o.id, date(o.created_timestamp) as orderedon, os.statusName , date(mar.activationTime) activatedon,
|
| 106 |
concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount
|
115 |
concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount
|
| 107 |
from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id
|
116 |
from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id
|
| 108 |
join transaction.lineitem li on li.order_id=o.id join inventory.warehouse w on w.id = o.fulfilmentWarehouseId
|
117 |
join transaction.lineitem li on li.order_id=o.id join inventory.warehouse w on w.id = o.fulfilmentWarehouseId
|
| Line 129... |
Line 138... |
| 129 |
shipped_headers=['Counter', 'Product', 'Quantity', 'Value']
|
138 |
shipped_headers=['Counter', 'Product', 'Quantity', 'Value']
|
| 130 |
tertiary_headers=shipped_headers
|
139 |
tertiary_headers=shipped_headers
|
| 131 |
pending_headers=shipped_headers
|
140 |
pending_headers=shipped_headers
|
| 132 |
ageing_headers=['Counter','Product','QtyDelivered','QtySold','QtyInStock','ValueInStock']
|
141 |
ageing_headers=['Counter','Product','QtyDelivered','QtySold','QtyInStock','ValueInStock']
|
| 133 |
mis_headers=['Counter','Last Day Qty', 'Last Day Value','MTD Qty', 'MTD Value']
|
142 |
mis_headers=['Counter','Last Day Qty', 'Last Day Value','MTD Qty', 'MTD Value']
|
| - |
|
143 |
mis_headers_prod=['Product','Last Day Qty', 'Last Day Value','MTD Qty', 'MTD Value']
|
| 134 |
|
144 |
|
| 135 |
report_map['shipped'] = {'title':'Shipped Orders Report', 'query' : SHIPPED_ORDER_GROUP_SQL, 'group':2, 'headers':shipped_headers}
|
145 |
report_map['shipped'] = {'title':'Shipped Orders Report', 'query' : SHIPPED_ORDER_GROUP_SQL, 'group':2, 'headers':shipped_headers}
|
| 136 |
report_map['tertiary'] = {'title':'Tertiary Report', 'query' : SOLD_OUT_GROUP_SQL, 'group':2, 'headers':tertiary_headers}
|
146 |
report_map['tertiary'] = {'title':'Tertiary Report', 'query' : SOLD_OUT_GROUP_SQL, 'group':2, 'headers':tertiary_headers}
|
| 137 |
report_map['pending'] = {'title':'Pending Orders Report', 'query' : PENDING_ORDER_GROUP_SQL, 'group':2, 'headers':pending_headers}
|
147 |
report_map['pending'] = {'title':'Pending Orders Report', 'query' : PENDING_ORDER_GROUP_SQL, 'group':2, 'headers':pending_headers}
|
| 138 |
report_map['ageing'] = {'title':'Spice Stock Ageing Report', 'query' : STOCK_REPORT_GROUP_SQL, 'group':2, 'headers':ageing_headers}
|
148 |
report_map['ageing'] = {'title':'Spice Stock Ageing Report', 'query' : STOCK_REPORT_GROUP_SQL, 'group':2, 'headers':ageing_headers}
|
| 139 |
report_map['mis'] = {'title':'Daily Counter wise Sales Report', 'query' : MIS_REPORT_GROUP_SQL, 'group':1, 'headers':mis_headers}
|
149 |
report_map['mis'] = {'title':'Daily Counter wise Sales Report', 'query' : MIS_REPORT_GROUP_SQL, 'group':1, 'headers':mis_headers}
|
| - |
|
150 |
report_map['mis_prod'] = {'title':'Daily Counter wise Sales Report', 'query' : MIS_REPORT_GROUP_PROD_SQL, 'group':1, 'headers':mis_headers_prod}
|
| 140 |
|
151 |
|
| 141 |
#recipients = []
|
152 |
#recipients = []
|
| 142 |
recipients = ['rajneesh.arora@saholic.com', 'lakshman.g@smobility.in', 'sandeep.sachdeva@shop2020.in', 'amit.sirohi@shop2020.in', 'khushal.bhatia@saholic.com', 'chaitnaya.vats@saholic.com']
|
153 |
recipients = ['rajneesh.arora@saholic.com', 'lakshman.g@smobility.in', 'sandeep.sachdeva@shop2020.in', 'amit.sirohi@shop2020.in', 'khushal.bhatia@saholic.com', 'chaitnaya.vats@saholic.com']
|
| 143 |
|
154 |
|
| 144 |
|
155 |
|
| Line 346... |
Line 357... |
| 346 |
traceback.print_exc()
|
357 |
traceback.print_exc()
|
| 347 |
print "Could not execute query"
|
358 |
print "Could not execute query"
|
| 348 |
|
359 |
|
| 349 |
def sendShippedReport():
|
360 |
def sendShippedReport():
|
| 350 |
mailbody = getMailBody(report_map['shipped'])
|
361 |
mailbody = getMailBody(report_map['shipped'])
|
| 351 |
getMailAttachmentShipped()
|
362 |
getMailAttachmentShipped()
|
| 352 |
sendmail(recipients, mailbody, 'dtrshipped_report.xls', 'DTR Shipped Product Report')
|
363 |
sendmail(recipients, mailbody, 'dtrshipped_report.xls', 'DTR Shipped Product Report')
|
| 353 |
|
364 |
|
| 354 |
def sendTertiaryReport():
|
365 |
def sendTertiaryReport():
|
| 355 |
mailbody = getMailBody(report_map['tertiary'])
|
366 |
mailbody = getMailBody(report_map['tertiary'])
|
| 356 |
getMailAttachmentTertiary()
|
367 |
getMailAttachmentTertiary()
|
| Line 365... |
Line 376... |
| 365 |
mailbody = getMailBody(report_map['ageing'])
|
376 |
mailbody = getMailBody(report_map['ageing'])
|
| 366 |
getMailAttachmenAgeing()
|
377 |
getMailAttachmenAgeing()
|
| 367 |
sendmail(recipients, mailbody, 'dtrstockageing_report.xls', 'DTR Spice Stock Ageing Report')
|
378 |
sendmail(recipients, mailbody, 'dtrstockageing_report.xls', 'DTR Spice Stock Ageing Report')
|
| 368 |
|
379 |
|
| 369 |
def getMisReport():
|
380 |
def getMisReport():
|
| 370 |
mailbody = getMailBody(report_map['mis'])
|
381 |
mailbody = getMailBody(report_map['mis']) + "<br>" + "<br>" + getMailBody(report_map['mis_prod'])
|
| 371 |
getMailAttachmentMis()
|
382 |
getMailAttachmentMis()
|
| 372 |
sendmail(recipients, mailbody, 'dtrcountersale_report.xls', 'DTR Counterwise Sales Report')
|
383 |
sendmail(recipients, mailbody, 'dtrcountersale_report.xls', 'DTR Counterwise Sales Report')
|
| 373 |
|
384 |
|
| 374 |
def getMailBody(reportmap):
|
385 |
def getMailBody(reportmap):
|
| 375 |
conn = getDbConnection()
|
386 |
conn = getDbConnection()
|