| Line 75... |
Line 75... |
| 75 |
"""
|
75 |
"""
|
| 76 |
|
76 |
|
| 77 |
STOCK_REPORT_GROUP_SQL= """
|
77 |
STOCK_REPORT_GROUP_SQL= """
|
| 78 |
select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product, sum(if(o.status=12, 1,0)) delivered, sum(if(mar.activationTime is not null, 1, 0)) as sold, sum(if(mar.activationTime is null and o.status=12, 1, 0)) as instock, sum(if(mar.activationTime is null and o.status=12, o.total_amount,0)) as valueinstock, sum(if(o.status in (9,10,16,17,40), 1,0)) shipped, sum(if(o.status in (9,10,16,17,40), o.total_amount,0)) shippedAmount, sum(if(o.status in (3,4,5,7), 1,0)) pending, sum(if(o.status in (3,4,5,7), o.total_amount,0)) pendingAmount from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id left join mobileactivationrecord mar on mar.orderId = o.id join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id where o.status in (12,9,10,16,17,40,3,4,5,7) and o.total_amount > 0 and li.brand = 'Spice' and li.item_id not in (select itemId from catalog.spiceuntraceable) group by name, product with rollup
|
78 |
select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product, sum(if(o.status=12, 1,0)) delivered, sum(if(mar.activationTime is not null, 1, 0)) as sold, sum(if(mar.activationTime is null and o.status=12, 1, 0)) as instock, sum(if(mar.activationTime is null and o.status=12, o.total_amount,0)) as valueinstock, sum(if(o.status in (9,10,16,17,40), 1,0)) shipped, sum(if(o.status in (9,10,16,17,40), o.total_amount,0)) shippedAmount, sum(if(o.status in (3,4,5,7), 1,0)) pending, sum(if(o.status in (3,4,5,7), o.total_amount,0)) pendingAmount from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id left join mobileactivationrecord mar on mar.orderId = o.id join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id where o.status in (12,9,10,16,17,40,3,4,5,7) and o.total_amount > 0 and li.brand = 'Spice' and li.item_id not in (select itemId from catalog.spiceuntraceable) group by name, product with rollup
|
| 79 |
"""
|
79 |
"""
|
| - |
|
80 |
STOCK_REPORT_GROUP_SQL_NO_ROLLUP = """
|
| - |
|
81 |
select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product, sum(if(o.status=12, 1,0)) delivered, sum(if(mar.activationTime is not null, 1, 0)) as sold, sum(if(mar.activationTime is null and o.status=12, 1, 0)) as instock, sum(if(mar.activationTime is null and o.status=12, o.total_amount,0)) as valueinstock, sum(if(o.status in (9,10,16,17,40), 1,0)) shipped, sum(if(o.status in (9,10,16,17,40), o.total_amount,0)) shippedAmount, sum(if(o.status in (3,4,5,7), 1,0)) pending, sum(if(o.status in (3,4,5,7), o.total_amount,0)) pendingAmount from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id left join mobileactivationrecord mar on mar.orderId = o.id join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id where o.status in (12,9,10,16,17,40,3,4,5,7) and o.total_amount > 0 and li.brand = 'Spice' and li.item_id not in (select itemId from catalog.spiceuntraceable) group by name, product
|
| - |
|
82 |
"""
|
| 80 |
|
83 |
|
| 81 |
STOCK_REPORT_SQL= """
|
84 |
STOCK_REPORT_SQL= """
|
| 82 |
select c.code, c.name, o.id, date(o.created_timestamp) createddate, date(o.delivery_timestamp) delivereddate, os.statusName,
|
85 |
select c.code, c.name, o.id, date(o.created_timestamp) createddate, date(o.delivery_timestamp) delivereddate, os.statusName,
|
| 83 |
concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product, li.quantity, o.total_amount,
|
86 |
concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product, li.quantity, o.total_amount,
|
| 84 |
datediff(curdate(),date(o.delivery_timestamp)) as ageing from transaction.order o join user.privatedealuser
|
87 |
datediff(curdate(),date(o.delivery_timestamp)) as ageing from transaction.order o join user.privatedealuser
|
| Line 311... |
Line 314... |
| 311 |
traceback.print_exc()
|
314 |
traceback.print_exc()
|
| 312 |
print "Could not execute query"
|
315 |
print "Could not execute query"
|
| 313 |
|
316 |
|
| 314 |
def getMailAttachmenAgeing():
|
317 |
def getMailAttachmenAgeing():
|
| 315 |
selectSql = STOCK_REPORT_SQL
|
318 |
selectSql = STOCK_REPORT_SQL
|
| - |
|
319 |
selectSql1 = STOCK_REPORT_GROUP_SQL_NO_ROLLUP
|
| 316 |
conn = getDbConnection()
|
320 |
conn = getDbConnection()
|
| 317 |
try:
|
321 |
try:
|
| 318 |
# prepare a cursor object using cursor() method
|
322 |
# prepare a cursor object using cursor() method
|
| 319 |
cursor = conn.cursor()
|
323 |
cursor = conn.cursor()
|
| 320 |
# Execute the SQL command
|
324 |
# Execute the SQL command
|
| 321 |
# Fetch source id.
|
325 |
# Fetch source id.
|
| 322 |
cursor.execute(selectSql)
|
326 |
cursor.execute(selectSql)
|
| 323 |
result = cursor.fetchall()
|
327 |
result = cursor.fetchall()
|
| - |
|
328 |
|
| - |
|
329 |
cursor.execute(selectSql1)
|
| - |
|
330 |
result1 = cursor.fetchall()
|
| - |
|
331 |
|
| - |
|
332 |
|
| 324 |
workbook = xlwt.Workbook()
|
333 |
workbook = xlwt.Workbook()
|
| - |
|
334 |
worksheet1 = workbook.add_sheet("Stock Aeging Summary")
|
| 325 |
worksheet = workbook.add_sheet("Stock Aeging Orders")
|
335 |
worksheet = workbook.add_sheet("Stock Aeging Orders")
|
| 326 |
boldStyle = xlwt.XFStyle()
|
336 |
boldStyle = xlwt.XFStyle()
|
| 327 |
f = xlwt.Font()
|
337 |
f = xlwt.Font()
|
| 328 |
f.bold = True
|
338 |
f.bold = True
|
| 329 |
boldStyle.font = f
|
339 |
boldStyle.font = f
|
| - |
|
340 |
|
| 330 |
column = 0
|
341 |
column = 0
|
| 331 |
row = 0
|
342 |
row = 0
|
| 332 |
|
- |
|
| 333 |
worksheet.write(row, 0, 'Counter Code', boldStyle)
|
343 |
worksheet.write(row, 0, 'Counter Code', boldStyle)
|
| 334 |
worksheet.write(row, 1, 'Counter Name', boldStyle)
|
344 |
worksheet.write(row, 1, 'Counter Name', boldStyle)
|
| 335 |
worksheet.write(row, 2, 'Order Id', boldStyle)
|
345 |
worksheet.write(row, 2, 'Order Id', boldStyle)
|
| 336 |
worksheet.write(row, 3, 'Order Date', boldStyle)
|
346 |
worksheet.write(row, 3, 'Order Date', boldStyle)
|
| 337 |
worksheet.write(row, 4, 'Delivered Date', boldStyle)
|
347 |
worksheet.write(row, 4, 'Delivered Date', boldStyle)
|
| 338 |
worksheet.write(row, 5, 'Order Status', boldStyle)
|
348 |
worksheet.write(row, 5, 'Order Status', boldStyle)
|
| 339 |
worksheet.write(row, 6, 'Product', boldStyle)
|
349 |
worksheet.write(row, 6, 'Product', boldStyle)
|
| 340 |
worksheet.write(row, 7, 'Qty', boldStyle)
|
350 |
worksheet.write(row, 7, 'Qty', boldStyle)
|
| 341 |
worksheet.write(row, 8, 'Value', boldStyle)
|
351 |
worksheet.write(row, 8, 'Value', boldStyle)
|
| 342 |
worksheet.write(row, 9, 'Ageing(Days)', boldStyle)
|
352 |
worksheet.write(row, 9, 'Ageing(Days)', boldStyle)
|
| - |
|
353 |
|
| - |
|
354 |
worksheet1.write(row, 0, 'Counter', boldStyle)
|
| - |
|
355 |
worksheet1.write(row, 1, 'Product', boldStyle)
|
| - |
|
356 |
worksheet1.write(row, 2, 'Qty Delivered', boldStyle)
|
| - |
|
357 |
worksheet1.write(row, 3, 'Qty Sold', boldStyle)
|
| - |
|
358 |
worksheet1.write(row, 4, 'Qty In Stock', boldStyle)
|
| - |
|
359 |
worksheet1.write(row, 5, 'Value In Stock', boldStyle)
|
| - |
|
360 |
worksheet1.write(row, 6, 'Qty Shipped', boldStyle)
|
| - |
|
361 |
worksheet1.write(row, 7, 'Value Shipped', boldStyle)
|
| - |
|
362 |
worksheet1.write(row, 8, 'Qty Pending', boldStyle)
|
| - |
|
363 |
worksheet1.write(row, 9, 'Value Pending', boldStyle)
|
| 343 |
|
364 |
|
| 344 |
for r in result:
|
365 |
for r in result:
|
| 345 |
row += 1
|
366 |
row += 1
|
| 346 |
column = 0
|
367 |
column = 0
|
| 347 |
for data in r :
|
368 |
for data in r :
|
| 348 |
worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)
|
369 |
worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)
|
| 349 |
column += 1
|
370 |
column += 1
|
| - |
|
371 |
|
| - |
|
372 |
row=0
|
| - |
|
373 |
column=0
|
| - |
|
374 |
for r in result1:
|
| - |
|
375 |
row += 1
|
| - |
|
376 |
column = 0
|
| - |
|
377 |
for data in r :
|
| - |
|
378 |
worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)
|
| - |
|
379 |
column += 1
|
| 350 |
workbook.save(TMP_FILE)
|
380 |
workbook.save(TMP_FILE)
|
| 351 |
except Exception:
|
381 |
except Exception:
|
| 352 |
traceback.print_exc()
|
382 |
traceback.print_exc()
|
| 353 |
print "Could not execute query"
|
383 |
print "Could not execute query"
|
| 354 |
|
384 |
|