| Line 140... |
Line 140... |
| 140 |
|
140 |
|
| 141 |
|
141 |
|
| 142 |
def write_report(filename, all_lines, all_supc):
|
142 |
def write_report(filename, all_lines, all_supc):
|
| 143 |
|
143 |
|
| 144 |
buyBoxItems = []
|
144 |
buyBoxItems = []
|
| - |
|
145 |
competitive = []
|
| 145 |
|
146 |
|
| 146 |
wbk = xlwt.Workbook()
|
147 |
wbk = xlwt.Workbook()
|
| 147 |
sheet = wbk.add_sheet('main')
|
148 |
sheet = wbk.add_sheet('main')
|
| 148 |
|
149 |
|
| 149 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
150 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 202... |
Line 203... |
| 202 |
temp.append(one_line)
|
203 |
temp.append(one_line)
|
| 203 |
buyBoxItems.append(temp)
|
204 |
buyBoxItems.append(temp)
|
| 204 |
i+=1
|
205 |
i+=1
|
| 205 |
continue
|
206 |
continue
|
| 206 |
|
207 |
|
| - |
|
208 |
if one_line.parent_category ==10011:
|
| - |
|
209 |
lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
|
| - |
|
210 |
else:
|
| - |
|
211 |
lowestTp = supc_data.lowestSp*0.9497-courierCost
|
| - |
|
212 |
|
| - |
|
213 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
| - |
|
214 |
|
| - |
|
215 |
if (lowestTp > lowest_possible_tp):
|
| - |
|
216 |
temp=[]
|
| - |
|
217 |
temp.append(supc_data)
|
| - |
|
218 |
temp.append(one_line)
|
| - |
|
219 |
competitive.append(temp)
|
| - |
|
220 |
i+=1
|
| - |
|
221 |
continue
|
| - |
|
222 |
|
| - |
|
223 |
|
| 207 |
sheet.write(sheet_iterator, 0, one_line.item_id)
|
224 |
sheet.write(sheet_iterator, 0, one_line.item_id)
|
| 208 |
sheet.write(sheet_iterator, 1, one_line.category_name)
|
225 |
sheet.write(sheet_iterator, 1, one_line.category_name)
|
| 209 |
sheet.write(sheet_iterator, 2, one_line.product_group)
|
226 |
sheet.write(sheet_iterator, 2, one_line.product_group)
|
| 210 |
sheet.write(sheet_iterator, 3, supc)
|
227 |
sheet.write(sheet_iterator, 3, supc)
|
| 211 |
sheet.write(sheet_iterator, 4, one_line.brand)
|
228 |
sheet.write(sheet_iterator, 4, one_line.brand)
|
| Line 260... |
Line 277... |
| 260 |
sheet.write(sheet_iterator, 28, "LOW")
|
277 |
sheet.write(sheet_iterator, 28, "LOW")
|
| 261 |
i= i+1
|
278 |
i= i+1
|
| 262 |
sheet_iterator+=1
|
279 |
sheet_iterator+=1
|
| 263 |
|
280 |
|
| 264 |
createSheetForBuyBoxItems(buyBoxItems,wbk)
|
281 |
createSheetForBuyBoxItems(buyBoxItems,wbk)
|
| - |
|
282 |
createSheetForCometitiveItems(competitive,wbk)
|
| 265 |
wbk.save(filename)
|
283 |
wbk.save(filename)
|
| 266 |
|
284 |
|
| 267 |
EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@shop2020.in"], " " , "", [get_attachment_part(filename)], ["kshitij.sood@shop2020.in"], [])
|
285 |
EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@shop2020.in"], " " , "", [get_attachment_part(filename)], ["kshitij.sood@shop2020.in"], [])
|
| 268 |
|
286 |
|
| 269 |
|
287 |
|
| Line 348... |
Line 366... |
| 348 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
366 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
| 349 |
proposed_tp = max(lowestTp - max((20, lowestTp*0.002)), lowest_possible_tp)
|
367 |
proposed_tp = max(lowestTp - max((20, lowestTp*0.002)), lowest_possible_tp)
|
| 350 |
sheet.write(i, 22, round(proposed_tp))
|
368 |
sheet.write(i, 22, round(proposed_tp))
|
| 351 |
sheet.write(i, 23, round((proposed_tp+courierCost)/0.9597))
|
369 |
sheet.write(i, 23, round((proposed_tp+courierCost)/0.9597))
|
| 352 |
sheet.write(i, 24, round(proposed_tp*0.988+21))
|
370 |
sheet.write(i, 24, round(proposed_tp*0.988+21))
|
| 353 |
sheet.write(i, 25, proposed_tp-one_line.our_nlc)
|
371 |
sheet.write(i, 25, round(proposed_tp-one_line.our_nlc))
|
| 354 |
i+=1
|
372 |
i+=1
|
| - |
|
373 |
|
| - |
|
374 |
|
| - |
|
375 |
def createSheetForCometitiveItems(competitiveItems,wbk):
|
| - |
|
376 |
sheet = wbk.add_sheet('Competitive')
|
| - |
|
377 |
|
| - |
|
378 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| - |
|
379 |
|
| - |
|
380 |
excel_integer_format = '0'
|
| - |
|
381 |
integer_style = xlwt.XFStyle()
|
| - |
|
382 |
integer_style.num_format_str = excel_integer_format
|
| - |
|
383 |
|
| - |
|
384 |
sheet.write(0, 0, "Item ID", heading_xf)
|
| - |
|
385 |
sheet.write(0, 1, "Category", heading_xf)
|
| - |
|
386 |
sheet.write(0, 2, "Product Group.", heading_xf)
|
| - |
|
387 |
sheet.write(0, 3, "SUPC", heading_xf)
|
| - |
|
388 |
sheet.write(0, 4, "Brand", heading_xf)
|
| - |
|
389 |
sheet.write(0, 5, "Model Name", heading_xf)
|
| - |
|
390 |
sheet.write(0, 6, "Model Number", heading_xf)
|
| - |
|
391 |
sheet.write(0, 7, "Color", heading_xf)
|
| - |
|
392 |
sheet.write(0, 8, "Weight", heading_xf)
|
| - |
|
393 |
sheet.write(0, 9, "Risky", heading_xf)
|
| - |
|
394 |
sheet.write(0, 10, "Our SP", heading_xf)
|
| - |
|
395 |
sheet.write(0, 11, "Our TP", heading_xf)
|
| - |
|
396 |
sheet.write(0, 12, "Our Offer Price", heading_xf)
|
| - |
|
397 |
sheet.write(0, 13, "Our Rank", heading_xf)
|
| - |
|
398 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
| - |
|
399 |
sheet.write(0, 15, "Lowest SP", heading_xf)
|
| - |
|
400 |
sheet.write(0, 16, "Lowest TP", heading_xf)
|
| - |
|
401 |
sheet.write(0, 17, "Lowest Offer Price", heading_xf)
|
| - |
|
402 |
sheet.write(0, 18, "Inventory of Top Vendors", heading_xf)
|
| - |
|
403 |
sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
|
| - |
|
404 |
sheet.write(0, 20, "Our NLC", heading_xf)
|
| - |
|
405 |
sheet.write(0, 21, "Lowest Possible TP", heading_xf)
|
| - |
|
406 |
sheet.write(0, 22, "Lowest Possible SP", heading_xf)
|
| - |
|
407 |
sheet.write(0, 23, "Competition Basis ", heading_xf)
|
| - |
|
408 |
sheet.write(0, 24, "Can Compete", heading_xf)
|
| - |
|
409 |
sheet.write(0, 25, "Target TP", heading_xf)
|
| - |
|
410 |
sheet.write(0, 26, "Target SP", heading_xf)
|
| - |
|
411 |
sheet.write(0, 27, "Target NLC", heading_xf)
|
| - |
|
412 |
sheet.write(0, 28, "Sales Potential", heading_xf)
|
| 355 |
|
413 |
|
| - |
|
414 |
i, sheet_iterator=1,1
|
| - |
|
415 |
for data in competitiveItems:
|
| - |
|
416 |
supc_data =data[0]
|
| - |
|
417 |
one_line = data[1]
|
| - |
|
418 |
|
| - |
|
419 |
courierCost = 45
|
| - |
|
420 |
|
| - |
|
421 |
if one_line.weight:
|
| - |
|
422 |
slab = int(((one_line.weight+0.05) - .001)/.5)
|
| - |
|
423 |
for x in range (0,slab):
|
| - |
|
424 |
courierCost = courierCost + 35
|
| - |
|
425 |
|
| - |
|
426 |
courierCost = courierCost * 1.1236
|
| - |
|
427 |
|
| - |
|
428 |
|
| - |
|
429 |
sheet.write(sheet_iterator, 0, one_line.item_id)
|
| - |
|
430 |
sheet.write(sheet_iterator, 1, one_line.category_name)
|
| - |
|
431 |
sheet.write(sheet_iterator, 2, one_line.product_group)
|
| - |
|
432 |
sheet.write(sheet_iterator, 3, supc_data.supc)
|
| - |
|
433 |
sheet.write(sheet_iterator, 4, one_line.brand)
|
| - |
|
434 |
sheet.write(sheet_iterator, 5, one_line.model_name)
|
| - |
|
435 |
sheet.write(sheet_iterator, 6, one_line.model_number)
|
| - |
|
436 |
sheet.write(sheet_iterator, 7, one_line.color)
|
| - |
|
437 |
sheet.write(sheet_iterator, 8, one_line.weight)
|
| - |
|
438 |
sheet.write(sheet_iterator, 9, one_line.risky)
|
| - |
|
439 |
sheet.write(sheet_iterator, 10, supc_data.ourSp)
|
| - |
|
440 |
sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
|
| - |
|
441 |
sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
|
| - |
|
442 |
sheet.write(sheet_iterator, 13, supc_data.rank)
|
| - |
|
443 |
sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
|
| - |
|
444 |
sheet.write(sheet_iterator, 15, supc_data.lowestSp)
|
| - |
|
445 |
if one_line.parent_category ==10011:
|
| - |
|
446 |
lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
|
| - |
|
447 |
else:
|
| - |
|
448 |
lowestTp = supc_data.lowestSp*0.9497-courierCost
|
| - |
|
449 |
sheet.write(sheet_iterator, 16, round(lowestTp))
|
| - |
|
450 |
sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
|
| - |
|
451 |
sheet.write(sheet_iterator, 18, supc_data.otherInventory)
|
| - |
|
452 |
sheet.write(sheet_iterator, 19, supc_data.ourInventory)
|
| - |
|
453 |
sheet.write(sheet_iterator, 20, one_line.our_nlc)
|
| - |
|
454 |
if supc_data.rank==1:
|
| - |
|
455 |
i+=1
|
| - |
|
456 |
sheet_iterator+=1
|
| - |
|
457 |
continue
|
| - |
|
458 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
| - |
|
459 |
lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
|
| - |
|
460 |
sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
|
| - |
|
461 |
sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
|
| - |
|
462 |
if (supc_data.lowestOfferPrice == supc_data.lowestSp):
|
| - |
|
463 |
sheet.write(sheet_iterator, 23, 'SP')
|
| - |
|
464 |
else:
|
| - |
|
465 |
sheet.write(sheet_iterator, 23, 'TP')
|
| - |
|
466 |
proposed_tp = 0
|
| - |
|
467 |
if lowestTp > lowest_possible_tp:
|
| - |
|
468 |
sheet.write(sheet_iterator, 24, "Yes")
|
| - |
|
469 |
proposed_tp = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
|
| - |
|
470 |
sheet.write(sheet_iterator, 25, round(proposed_tp))
|
| - |
|
471 |
sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
|
| - |
|
472 |
else:
|
| - |
|
473 |
sheet.write(sheet_iterator, 24, "No")
|
| - |
|
474 |
|
| - |
|
475 |
sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
|
| - |
|
476 |
|
| - |
|
477 |
if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
|
| - |
|
478 |
sheet.write(sheet_iterator, 28, "HIGH")
|
| - |
|
479 |
elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
|
| - |
|
480 |
sheet.write(sheet_iterator, 28, "MEDIUM")
|
| - |
|
481 |
else:
|
| - |
|
482 |
sheet.write(sheet_iterator, 28, "LOW")
|
| - |
|
483 |
i= i+1
|
| - |
|
484 |
sheet_iterator+=1
|
| 356 |
|
485 |
|
| 357 |
def main():
|
486 |
def main():
|
| 358 |
parser = optparse.OptionParser()
|
487 |
parser = optparse.OptionParser()
|
| 359 |
parser.add_option("-f", "--file", dest="filename",
|
488 |
parser.add_option("-f", "--file", dest="filename",
|
| 360 |
default="ItemList.xls", type="string",
|
489 |
default="ItemList.xls", type="string",
|