| Line 579... |
Line 579... |
| 579 |
sheet.write(0, 4, "Brand", heading_xf)
|
579 |
sheet.write(0, 4, "Brand", heading_xf)
|
| 580 |
sheet.write(0, 5, "Product Name", heading_xf)
|
580 |
sheet.write(0, 5, "Product Name", heading_xf)
|
| 581 |
sheet.write(0, 6, "Weight", heading_xf)
|
581 |
sheet.write(0, 6, "Weight", heading_xf)
|
| 582 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
582 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
| 583 |
sheet.write(0, 8, "Risky", heading_xf)
|
583 |
sheet.write(0, 8, "Risky", heading_xf)
|
| - |
|
584 |
sheet.write(0, 9, "Commission Rate", heading_xf)
|
| - |
|
585 |
sheet.write(0, 10, "Return Provision", heading_xf)
|
| 584 |
sheet.write(0, 9, "Our SP", heading_xf)
|
586 |
sheet.write(0, 11, "Our SP", heading_xf)
|
| 585 |
sheet.write(0, 11, "Our TP", heading_xf)
|
587 |
sheet.write(0, 13, "Our TP", heading_xf)
|
| 586 |
sheet.write(0, 10, "Our Offer Price", heading_xf)
|
588 |
sheet.write(0, 12, "Our Offer Price", heading_xf)
|
| 587 |
sheet.write(0, 12, "Our Rank", heading_xf)
|
589 |
sheet.write(0, 14, "Our Rank", heading_xf)
|
| 588 |
sheet.write(0, 13, "Lowest Seller", heading_xf)
|
590 |
sheet.write(0, 15, "Lowest Seller", heading_xf)
|
| 589 |
sheet.write(0, 14, "Lowest SP", heading_xf)
|
591 |
sheet.write(0, 16, "Lowest SP", heading_xf)
|
| 590 |
sheet.write(0, 15, "Lowest TP", heading_xf)
|
592 |
sheet.write(0, 17, "Lowest TP", heading_xf)
|
| 591 |
sheet.write(0, 16, "Lowest Offer Price", heading_xf)
|
593 |
sheet.write(0, 18, "Lowest Offer Price", heading_xf)
|
| 592 |
sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
|
594 |
sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
|
| 593 |
sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
|
595 |
sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
|
| 594 |
sheet.write(0, 19, "Our Net Availability",heading_xf)
|
596 |
sheet.write(0, 21, "Our Net Availability",heading_xf)
|
| 595 |
sheet.write(0, 20, "Last Five Day Sale", heading_xf)
|
597 |
sheet.write(0, 22, "Last Five Day Sale", heading_xf)
|
| 596 |
sheet.write(0, 21, "Average Sale", heading_xf)
|
598 |
sheet.write(0, 23, "Average Sale", heading_xf)
|
| 597 |
sheet.write(0, 22, "Our NLC", heading_xf)
|
599 |
sheet.write(0, 24, "Our NLC", heading_xf)
|
| 598 |
sheet.write(0, 23, "Lowest Possible TP", heading_xf)
|
600 |
sheet.write(0, 25, "Lowest Possible TP", heading_xf)
|
| 599 |
sheet.write(0, 24, "Lowest Possible SP", heading_xf)
|
601 |
sheet.write(0, 26, "Lowest Possible SP", heading_xf)
|
| 600 |
sheet.write(0, 25, "Subsidy Difference", heading_xf)
|
602 |
sheet.write(0, 27, "Subsidy Difference", heading_xf)
|
| 601 |
sheet.write(0, 26, "Target TP", heading_xf)
|
603 |
sheet.write(0, 28, "Target TP", heading_xf)
|
| 602 |
sheet.write(0, 27, "Target SP", heading_xf)
|
604 |
sheet.write(0, 29, "Target SP", heading_xf)
|
| 603 |
sheet.write(0, 28, "Target NLC", heading_xf)
|
605 |
sheet.write(0, 30, "Target NLC", heading_xf)
|
| 604 |
sheet.write(0, 29, "Sales Potential", heading_xf)
|
606 |
sheet.write(0, 31, "Sales Potential", heading_xf)
|
| 605 |
sheet_iterator = 1
|
607 |
sheet_iterator = 1
|
| 606 |
for item in cantCompete:
|
608 |
for item in cantCompete:
|
| 607 |
snapdealDetails = item[0]
|
609 |
snapdealDetails = item[0]
|
| 608 |
snapdealItemInfo = item[1]
|
610 |
snapdealItemInfo = item[1]
|
| 609 |
snapdealPricing = item[2]
|
611 |
snapdealPricing = item[2]
|
| 610 |
mpItem = item[3]
|
612 |
mpItem = item[3]
|
| - |
|
613 |
spmObj = snapdealItemInfo.sourcePercentage
|
| 611 |
sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
|
614 |
sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
|
| 612 |
sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
|
615 |
sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
|
| 613 |
sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
|
616 |
sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
|
| 614 |
sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
|
617 |
sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
|
| 615 |
sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
|
618 |
sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
|
| 616 |
sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
|
619 |
sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
|
| 617 |
sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
|
620 |
sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
|
| 618 |
sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
|
621 |
sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
|
| 619 |
sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
|
622 |
sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
|
| - |
|
623 |
sheet.write(sheet_iterator, 9, spmObj.commission)
|
| - |
|
624 |
sheet.write(sheet_iterator, 10, spmObj.returnProvision)
|
| 620 |
sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
|
625 |
sheet.write(sheet_iterator, 11, snapdealPricing.ourSp)
|
| 621 |
sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
|
626 |
sheet.write(sheet_iterator, 12, snapdealDetails.ourOfferPrice)
|
| 622 |
sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
|
627 |
sheet.write(sheet_iterator, 13, snapdealPricing.ourTp)
|
| 623 |
sheet.write(sheet_iterator, 12, snapdealDetails.rank)
|
628 |
sheet.write(sheet_iterator, 14, snapdealDetails.rank)
|
| 624 |
sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
|
629 |
sheet.write(sheet_iterator, 15, snapdealDetails.lowestSellerName)
|
| 625 |
sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
|
630 |
sheet.write(sheet_iterator, 16, snapdealDetails.lowestSp)
|
| 626 |
sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
|
631 |
sheet.write(sheet_iterator, 17, snapdealPricing.lowestTp)
|
| 627 |
sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
|
632 |
sheet.write(sheet_iterator, 18, snapdealDetails.lowestOfferPrice)
|
| 628 |
sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
|
633 |
sheet.write(sheet_iterator, 19, snapdealDetails.otherInventory)
|
| 629 |
sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
|
634 |
sheet.write(sheet_iterator, 20, snapdealDetails.ourInventory)
|
| 630 |
if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
|
635 |
if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
|
| 631 |
sheet.write(sheet_iterator, 19, 'Info not available')
|
636 |
sheet.write(sheet_iterator, 21, 'Info not available')
|
| 632 |
else:
|
637 |
else:
|
| 633 |
sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
|
638 |
sheet.write(sheet_iterator, 21, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
|
| 634 |
sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
|
639 |
sheet.write(sheet_iterator, 22, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
|
| 635 |
sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
|
640 |
sheet.write(sheet_iterator, 23, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
|
| 636 |
sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
|
641 |
sheet.write(sheet_iterator, 24, snapdealItemInfo.nlc)
|
| 637 |
sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
|
642 |
sheet.write(sheet_iterator, 25, snapdealPricing.lowestPossibleTp)
|
| 638 |
sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
|
643 |
sheet.write(sheet_iterator, 26, snapdealPricing.lowestPossibleSp)
|
| 639 |
sheet.write(sheet_iterator, 25, getSubsidyDiff(snapdealDetails))
|
644 |
sheet.write(sheet_iterator, 27, getSubsidyDiff(snapdealDetails))
|
| 640 |
if (snapdealPricing.competitionBasis=='SP'):
|
645 |
if (snapdealPricing.competitionBasis=='SP'):
|
| 641 |
proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
|
646 |
proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
|
| 642 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
647 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 643 |
target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
648 |
target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
| 644 |
else:
|
649 |
else:
|
| 645 |
#proposed_tp = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
|
650 |
#proposed_tp = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
|
| 646 |
#proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
|
651 |
#proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
|
| 647 |
proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001) - getSubsidyDiff(snapdealDetails)
|
652 |
proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001) - getSubsidyDiff(snapdealDetails)
|
| 648 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
653 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 649 |
target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
654 |
target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
| 650 |
sheet.write(sheet_iterator, 26, round(proposed_tp,2))
|
655 |
sheet.write(sheet_iterator, 28, round(proposed_tp,2))
|
| 651 |
sheet.write(sheet_iterator, 27, round(proposed_sp,2))
|
656 |
sheet.write(sheet_iterator, 29, round(proposed_sp,2))
|
| 652 |
sheet.write(sheet_iterator, 28, round(target_nlc,2))
|
657 |
sheet.write(sheet_iterator, 30, round(target_nlc,2))
|
| 653 |
sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
|
658 |
sheet.write(sheet_iterator, 31, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
|
| 654 |
sheet_iterator+=1
|
659 |
sheet_iterator+=1
|
| 655 |
|
660 |
|
| 656 |
sheet = wbk.add_sheet('Lowest')
|
661 |
sheet = wbk.add_sheet('Lowest')
|
| 657 |
|
662 |
|
| 658 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
663 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 669... |
Line 674... |
| 669 |
sheet.write(0, 4, "Brand", heading_xf)
|
674 |
sheet.write(0, 4, "Brand", heading_xf)
|
| 670 |
sheet.write(0, 5, "Product Name", heading_xf)
|
675 |
sheet.write(0, 5, "Product Name", heading_xf)
|
| 671 |
sheet.write(0, 6, "Weight", heading_xf)
|
676 |
sheet.write(0, 6, "Weight", heading_xf)
|
| 672 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
677 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
| 673 |
sheet.write(0, 8, "Risky", heading_xf)
|
678 |
sheet.write(0, 8, "Risky", heading_xf)
|
| - |
|
679 |
sheet.write(0, 9, "Commission Rate", heading_xf)
|
| - |
|
680 |
sheet.write(0, 10, "Return Provision", heading_xf)
|
| 674 |
sheet.write(0, 9, "Our SP", heading_xf)
|
681 |
sheet.write(0, 11, "Our SP", heading_xf)
|
| 675 |
sheet.write(0, 11, "Our TP", heading_xf)
|
682 |
sheet.write(0, 13, "Our TP", heading_xf)
|
| 676 |
sheet.write(0, 10, "Our Offer Price", heading_xf)
|
683 |
sheet.write(0, 12, "Our Offer Price", heading_xf)
|
| 677 |
sheet.write(0, 12, "Our Rank", heading_xf)
|
684 |
sheet.write(0, 14, "Our Rank", heading_xf)
|
| 678 |
sheet.write(0, 13, "Lowest Seller", heading_xf)
|
685 |
sheet.write(0, 15, "Lowest Seller", heading_xf)
|
| 679 |
sheet.write(0, 14, "Second Lowest Seller", heading_xf)
|
686 |
sheet.write(0, 16, "Second Lowest Seller", heading_xf)
|
| 680 |
sheet.write(0, 15, "Second Lowest Price", heading_xf)
|
687 |
sheet.write(0, 17, "Second Lowest Price", heading_xf)
|
| 681 |
sheet.write(0, 16, "Second Lowest Offer Price", heading_xf)
|
688 |
sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)
|
| 682 |
sheet.write(0, 17, "Second Lowest Seller TP", heading_xf)
|
689 |
sheet.write(0, 19, "Second Lowest Seller TP", heading_xf)
|
| 683 |
sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
|
690 |
sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
|
| 684 |
sheet.write(0, 19, "Our Net Availability",heading_xf)
|
691 |
sheet.write(0, 21, "Our Net Availability",heading_xf)
|
| 685 |
sheet.write(0, 20, "Last Five Day Sale", heading_xf)
|
692 |
sheet.write(0, 22, "Last Five Day Sale", heading_xf)
|
| 686 |
sheet.write(0, 21, "Average Sale", heading_xf)
|
693 |
sheet.write(0, 23, "Average Sale", heading_xf)
|
| 687 |
sheet.write(0, 22, "Second Lowest Seller Inventory", heading_xf)
|
694 |
sheet.write(0, 24, "Second Lowest Seller Inventory", heading_xf)
|
| 688 |
sheet.write(0, 23, "Our NLC", heading_xf)
|
695 |
sheet.write(0, 25, "Our NLC", heading_xf)
|
| 689 |
sheet.write(0, 24, "Subsidy Difference", heading_xf)
|
696 |
sheet.write(0, 26, "Subsidy Difference", heading_xf)
|
| 690 |
sheet.write(0, 25, "Target TP", heading_xf)
|
697 |
sheet.write(0, 27, "Target TP", heading_xf)
|
| 691 |
sheet.write(0, 26, "Target SP", heading_xf)
|
698 |
sheet.write(0, 28, "Target SP", heading_xf)
|
| 692 |
sheet.write(0, 27, "MARGIN INCREASED POTENTIAL", heading_xf)
|
699 |
sheet.write(0, 29, "MARGIN INCREASED POTENTIAL", heading_xf)
|
| 693 |
sheet.write(0, 28, "Auto Pricing Decision", heading_xf)
|
700 |
sheet.write(0, 30, "Auto Pricing Decision", heading_xf)
|
| 694 |
sheet.write(0, 29, "Reason", heading_xf)
|
701 |
sheet.write(0, 31, "Reason", heading_xf)
|
| 695 |
sheet.write(0, 30, "Updated Price", heading_xf)
|
702 |
sheet.write(0, 32, "Updated Price", heading_xf)
|
| 696 |
|
703 |
|
| 697 |
sheet_iterator = 1
|
704 |
sheet_iterator = 1
|
| 698 |
for item in buyBoxItems:
|
705 |
for item in buyBoxItems:
|
| 699 |
snapdealDetails = item[0]
|
706 |
snapdealDetails = item[0]
|
| 700 |
snapdealItemInfo = item[1]
|
707 |
snapdealItemInfo = item[1]
|
| 701 |
snapdealPricing = item[2]
|
708 |
snapdealPricing = item[2]
|
| 702 |
mpItem = item[3]
|
709 |
mpItem = item[3]
|
| - |
|
710 |
spmObj = snapdealItemInfo.sourcePercentage
|
| 703 |
sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
|
711 |
sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
|
| 704 |
sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
|
712 |
sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
|
| 705 |
sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
|
713 |
sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
|
| 706 |
sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
|
714 |
sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
|
| 707 |
sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
|
715 |
sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
|
| 708 |
sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
|
716 |
sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
|
| 709 |
sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
|
717 |
sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
|
| 710 |
sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
|
718 |
sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
|
| 711 |
sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
|
719 |
sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
|
| - |
|
720 |
sheet.write(sheet_iterator, 9, spmObj.commission)
|
| - |
|
721 |
sheet.write(sheet_iterator, 10, spmObj.returnProvision)
|
| 712 |
sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
|
722 |
sheet.write(sheet_iterator, 11, snapdealPricing.ourSp)
|
| 713 |
sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
|
723 |
sheet.write(sheet_iterator, 12, snapdealDetails.ourOfferPrice)
|
| 714 |
sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
|
724 |
sheet.write(sheet_iterator, 13, snapdealPricing.ourTp)
|
| 715 |
sheet.write(sheet_iterator, 12, snapdealDetails.rank)
|
725 |
sheet.write(sheet_iterator, 14, snapdealDetails.rank)
|
| 716 |
sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
|
726 |
sheet.write(sheet_iterator, 15, snapdealDetails.lowestSellerName)
|
| 717 |
sheet.write(sheet_iterator, 14, snapdealDetails.secondLowestSellerName)
|
727 |
sheet.write(sheet_iterator, 16, snapdealDetails.secondLowestSellerName)
|
| 718 |
sheet.write(sheet_iterator, 15, snapdealDetails.secondLowestSellerSp)
|
728 |
sheet.write(sheet_iterator, 17, snapdealDetails.secondLowestSellerSp)
|
| 719 |
sheet.write(sheet_iterator, 16, snapdealDetails.secondLowestSellerOfferPrice)
|
729 |
sheet.write(sheet_iterator, 18, snapdealDetails.secondLowestSellerOfferPrice)
|
| 720 |
sheet.write(sheet_iterator, 17, snapdealPricing.secondLowestSellerTp)
|
730 |
sheet.write(sheet_iterator, 19, snapdealPricing.secondLowestSellerTp)
|
| 721 |
sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
|
731 |
sheet.write(sheet_iterator, 20, snapdealDetails.ourInventory)
|
| 722 |
if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
|
732 |
if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
|
| 723 |
sheet.write(sheet_iterator, 19, 'Info not available')
|
733 |
sheet.write(sheet_iterator, 21, 'Info not available')
|
| 724 |
else:
|
734 |
else:
|
| 725 |
sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
|
735 |
sheet.write(sheet_iterator, 21, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
|
| 726 |
sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
|
736 |
sheet.write(sheet_iterator, 22, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
|
| 727 |
sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
|
737 |
sheet.write(sheet_iterator, 23, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
|
| 728 |
sheet.write(sheet_iterator, 22, snapdealDetails.secondLowestSellerInventory)
|
738 |
sheet.write(sheet_iterator, 24, snapdealDetails.secondLowestSellerInventory)
|
| 729 |
sheet.write(sheet_iterator, 23, snapdealItemInfo.nlc)
|
739 |
sheet.write(sheet_iterator, 25, snapdealItemInfo.nlc)
|
| 730 |
sheet.write(sheet_iterator, 24, getSubsidyDiff(snapdealDetails))
|
740 |
sheet.write(sheet_iterator, 26, getSubsidyDiff(snapdealDetails))
|
| 731 |
if (snapdealPricing.competitionBasis=='SP'):
|
741 |
if (snapdealPricing.competitionBasis=='SP'):
|
| 732 |
proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)), snapdealPricing.lowestPossibleSp)
|
742 |
proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)), snapdealPricing.lowestPossibleSp)
|
| 733 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
743 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 734 |
#target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
744 |
#target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
| 735 |
else:
|
745 |
else:
|
| 736 |
#proposed_tp = max(snapdealPricing.secondLowestSellerTp - max((20, snapdealPricing.secondLowestSellerTp*0.002)), snapdealPricing.lowestPossibleTp)
|
746 |
#proposed_tp = max(snapdealPricing.secondLowestSellerTp - max((20, snapdealPricing.secondLowestSellerTp*0.002)), snapdealPricing.lowestPossibleTp)
|
| 737 |
#proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
|
747 |
#proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
|
| 738 |
proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)) -getSubsidyDiff(snapdealDetails), snapdealPricing.lowestPossibleSp)
|
748 |
proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)) -getSubsidyDiff(snapdealDetails), snapdealPricing.lowestPossibleSp)
|
| 739 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
749 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 740 |
#target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
750 |
#target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
| 741 |
sheet.write(sheet_iterator, 25, round(proposed_tp,2))
|
751 |
sheet.write(sheet_iterator, 27, round(proposed_tp,2))
|
| 742 |
sheet.write(sheet_iterator, 26, round(proposed_sp,2))
|
752 |
sheet.write(sheet_iterator, 28, round(proposed_sp,2))
|
| 743 |
sheet.write(sheet_iterator, 27, round((proposed_tp - snapdealPricing.ourTp),2))
|
753 |
sheet.write(sheet_iterator, 29, round((proposed_tp - snapdealPricing.ourTp),2))
|
| 744 |
mp_history_item = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.item_id==snapdealItemInfo.item_id).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).one()
|
754 |
mp_history_item = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.item_id==snapdealItemInfo.item_id).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).one()
|
| 745 |
if mp_history_item.decision is None:
|
755 |
if mp_history_item.decision is None:
|
| 746 |
sheet.write(sheet_iterator, 28, 'Auto Pricing Inactive')
|
756 |
sheet.write(sheet_iterator, 30, 'Auto Pricing Inactive')
|
| 747 |
sheet_iterator+=1
|
757 |
sheet_iterator+=1
|
| 748 |
continue
|
758 |
continue
|
| 749 |
sheet.write(sheet_iterator, 28, Decision._VALUES_TO_NAMES.get(mp_history_item.decision))
|
759 |
sheet.write(sheet_iterator, 30, Decision._VALUES_TO_NAMES.get(mp_history_item.decision))
|
| 750 |
sheet.write(sheet_iterator, 29, mp_history_item.reason)
|
760 |
sheet.write(sheet_iterator, 31, mp_history_item.reason)
|
| 751 |
if Decision._VALUES_TO_NAMES.get(mp_history_item.decision) == "AUTO_DECREMENT_SUCCESS":
|
761 |
if Decision._VALUES_TO_NAMES.get(mp_history_item.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 752 |
sheet.write(sheet_iterator, 30, math.ceil(mp_history_item.proposedSellingPrice))
|
762 |
sheet.write(sheet_iterator, 32, math.ceil(mp_history_item.proposedSellingPrice))
|
| 753 |
if Decision._VALUES_TO_NAMES.get(mp_history_item.decision) == "AUTO_INCREMENT_SUCCESS":
|
763 |
if Decision._VALUES_TO_NAMES.get(mp_history_item.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 754 |
sheet.write(sheet_iterator, 30, math.ceil(mp_history_item.ourSellingPrice+max(10,.01*mp_history_item.ourSellingPrice)))
|
764 |
sheet.write(sheet_iterator, 32, math.ceil(mp_history_item.ourSellingPrice+max(10,.01*mp_history_item.ourSellingPrice)))
|
| 755 |
sheet_iterator+=1
|
765 |
sheet_iterator+=1
|
| 756 |
|
766 |
|
| 757 |
sheet = wbk.add_sheet('Can Compete-With Inventory')
|
767 |
sheet = wbk.add_sheet('Can Compete-With Inventory')
|
| 758 |
|
768 |
|
| 759 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
769 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 770... |
Line 780... |
| 770 |
sheet.write(0, 4, "Brand", heading_xf)
|
780 |
sheet.write(0, 4, "Brand", heading_xf)
|
| 771 |
sheet.write(0, 5, "Product Name", heading_xf)
|
781 |
sheet.write(0, 5, "Product Name", heading_xf)
|
| 772 |
sheet.write(0, 6, "Weight", heading_xf)
|
782 |
sheet.write(0, 6, "Weight", heading_xf)
|
| 773 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
783 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
| 774 |
sheet.write(0, 8, "Risky", heading_xf)
|
784 |
sheet.write(0, 8, "Risky", heading_xf)
|
| - |
|
785 |
sheet.write(0, 9, "Commission Rate", heading_xf)
|
| - |
|
786 |
sheet.write(0, 10, "Return Provision", heading_xf)
|
| 775 |
sheet.write(0, 9, "Our SP", heading_xf)
|
787 |
sheet.write(0, 11, "Our SP", heading_xf)
|
| 776 |
sheet.write(0, 11, "Our TP", heading_xf)
|
788 |
sheet.write(0, 13, "Our TP", heading_xf)
|
| 777 |
sheet.write(0, 10, "Our Offer Price", heading_xf)
|
789 |
sheet.write(0, 12, "Our Offer Price", heading_xf)
|
| 778 |
sheet.write(0, 12, "Our Rank", heading_xf)
|
790 |
sheet.write(0, 14, "Our Rank", heading_xf)
|
| 779 |
sheet.write(0, 13, "Lowest Seller", heading_xf)
|
791 |
sheet.write(0, 15, "Lowest Seller", heading_xf)
|
| 780 |
sheet.write(0, 14, "Lowest SP", heading_xf)
|
792 |
sheet.write(0, 16, "Lowest SP", heading_xf)
|
| 781 |
sheet.write(0, 15, "Lowest TP", heading_xf)
|
793 |
sheet.write(0, 17, "Lowest TP", heading_xf)
|
| 782 |
sheet.write(0, 16, "Lowest Offer Price", heading_xf)
|
794 |
sheet.write(0, 18, "Lowest Offer Price", heading_xf)
|
| 783 |
sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
|
795 |
sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
|
| 784 |
sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
|
796 |
sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
|
| 785 |
sheet.write(0, 19, "Our Net Availability",heading_xf)
|
797 |
sheet.write(0, 21, "Our Net Availability",heading_xf)
|
| 786 |
sheet.write(0, 20, "Last Five Day Sale", heading_xf)
|
798 |
sheet.write(0, 22, "Last Five Day Sale", heading_xf)
|
| 787 |
sheet.write(0, 21, "Average Sale", heading_xf)
|
799 |
sheet.write(0, 23, "Average Sale", heading_xf)
|
| 788 |
sheet.write(0, 22, "Our NLC", heading_xf)
|
800 |
sheet.write(0, 24, "Our NLC", heading_xf)
|
| 789 |
sheet.write(0, 23, "Lowest Possible TP", heading_xf)
|
801 |
sheet.write(0, 25, "Lowest Possible TP", heading_xf)
|
| 790 |
sheet.write(0, 24, "Lowest Possible SP", heading_xf)
|
802 |
sheet.write(0, 26, "Lowest Possible SP", heading_xf)
|
| 791 |
sheet.write(0, 25, "Subsidy Difference", heading_xf)
|
803 |
sheet.write(0, 27, "Subsidy Difference", heading_xf)
|
| 792 |
sheet.write(0, 26, "Target TP", heading_xf)
|
804 |
sheet.write(0, 28, "Target TP", heading_xf)
|
| 793 |
sheet.write(0, 27, "Target SP", heading_xf)
|
805 |
sheet.write(0, 29, "Target SP", heading_xf)
|
| 794 |
sheet.write(0, 28, "Sales Potential", heading_xf)
|
806 |
sheet.write(0, 30, "Sales Potential", heading_xf)
|
| 795 |
sheet.write(0, 29, "Auto Pricing Decision", heading_xf)
|
807 |
sheet.write(0, 31, "Auto Pricing Decision", heading_xf)
|
| 796 |
sheet.write(0, 30, "Reason", heading_xf)
|
808 |
sheet.write(0, 32, "Reason", heading_xf)
|
| 797 |
sheet.write(0, 31, "Updated Price", heading_xf)
|
809 |
sheet.write(0, 33, "Updated Price", heading_xf)
|
| 798 |
|
810 |
|
| 799 |
sheet_iterator = 1
|
811 |
sheet_iterator = 1
|
| 800 |
for item in competitive:
|
812 |
for item in competitive:
|
| 801 |
snapdealDetails = item[0]
|
813 |
snapdealDetails = item[0]
|
| 802 |
snapdealItemInfo = item[1]
|
814 |
snapdealItemInfo = item[1]
|
| 803 |
snapdealPricing = item[2]
|
815 |
snapdealPricing = item[2]
|
| 804 |
mpItem = item[3]
|
816 |
mpItem = item[3]
|
| - |
|
817 |
spmObj = snapdealItemInfo.sourcePercentage
|
| 805 |
sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
|
818 |
sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
|
| 806 |
sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
|
819 |
sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
|
| 807 |
sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
|
820 |
sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
|
| 808 |
sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
|
821 |
sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
|
| 809 |
sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
|
822 |
sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
|
| 810 |
sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
|
823 |
sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
|
| 811 |
sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
|
824 |
sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
|
| 812 |
sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
|
825 |
sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
|
| 813 |
sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
|
826 |
sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
|
| - |
|
827 |
sheet.write(sheet_iterator, 9, spmObj.commission)
|
| - |
|
828 |
sheet.write(sheet_iterator, 10, spmObj.returnProvision)
|
| 814 |
sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
|
829 |
sheet.write(sheet_iterator, 11, snapdealPricing.ourSp)
|
| 815 |
sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
|
830 |
sheet.write(sheet_iterator, 12, snapdealDetails.ourOfferPrice)
|
| 816 |
sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
|
831 |
sheet.write(sheet_iterator, 13, snapdealPricing.ourTp)
|
| 817 |
sheet.write(sheet_iterator, 12, snapdealDetails.rank)
|
832 |
sheet.write(sheet_iterator, 14, snapdealDetails.rank)
|
| 818 |
sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
|
833 |
sheet.write(sheet_iterator, 15, snapdealDetails.lowestSellerName)
|
| 819 |
sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
|
834 |
sheet.write(sheet_iterator, 16, snapdealDetails.lowestSp)
|
| 820 |
sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
|
835 |
sheet.write(sheet_iterator, 17, snapdealPricing.lowestTp)
|
| 821 |
sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
|
836 |
sheet.write(sheet_iterator, 18, snapdealDetails.lowestOfferPrice)
|
| 822 |
sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
|
837 |
sheet.write(sheet_iterator, 19, snapdealDetails.otherInventory)
|
| 823 |
sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
|
838 |
sheet.write(sheet_iterator, 20, snapdealDetails.ourInventory)
|
| 824 |
if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
|
839 |
if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
|
| 825 |
sheet.write(sheet_iterator, 19, 'Info not available')
|
840 |
sheet.write(sheet_iterator, 21, 'Info not available')
|
| 826 |
else:
|
841 |
else:
|
| 827 |
sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
|
842 |
sheet.write(sheet_iterator, 21, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
|
| 828 |
sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
|
843 |
sheet.write(sheet_iterator, 22, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
|
| 829 |
sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
|
844 |
sheet.write(sheet_iterator, 23, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
|
| 830 |
sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
|
845 |
sheet.write(sheet_iterator, 24, snapdealItemInfo.nlc)
|
| 831 |
sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
|
846 |
sheet.write(sheet_iterator, 25, snapdealPricing.lowestPossibleTp)
|
| 832 |
sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
|
847 |
sheet.write(sheet_iterator, 26, snapdealPricing.lowestPossibleSp)
|
| 833 |
sheet.write(sheet_iterator, 25, getSubsidyDiff(snapdealDetails))
|
848 |
sheet.write(sheet_iterator, 27, getSubsidyDiff(snapdealDetails))
|
| 834 |
if (snapdealPricing.competitionBasis=='SP'):
|
849 |
if (snapdealPricing.competitionBasis=='SP'):
|
| 835 |
proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
|
850 |
proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
|
| 836 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
851 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 837 |
else:
|
852 |
else:
|
| 838 |
#proposed_tp = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
|
853 |
#proposed_tp = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
|
| 839 |
#proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
|
854 |
#proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
|
| 840 |
proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)) -getSubsidyDiff(snapdealDetails), snapdealPricing.lowestPossibleSp)
|
855 |
proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)) -getSubsidyDiff(snapdealDetails), snapdealPricing.lowestPossibleSp)
|
| 841 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
856 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 842 |
sheet.write(sheet_iterator, 26, round(proposed_tp,2))
|
857 |
sheet.write(sheet_iterator, 28, round(proposed_tp,2))
|
| 843 |
sheet.write(sheet_iterator, 27, round(proposed_sp,2))
|
858 |
sheet.write(sheet_iterator, 29, round(proposed_sp,2))
|
| 844 |
sheet.write(sheet_iterator, 28, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
|
859 |
sheet.write(sheet_iterator, 30, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
|
| 845 |
mp_history_item = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.item_id==snapdealItemInfo.item_id).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).one()
|
860 |
mp_history_item = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.item_id==snapdealItemInfo.item_id).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).one()
|
| 846 |
if mp_history_item.decision is None:
|
861 |
if mp_history_item.decision is None:
|
| 847 |
sheet.write(sheet_iterator, 29, 'Auto pricing Inactive')
|
862 |
sheet.write(sheet_iterator, 31, 'Auto pricing Inactive')
|
| 848 |
sheet_iterator+=1
|
863 |
sheet_iterator+=1
|
| 849 |
continue
|
864 |
continue
|
| 850 |
sheet.write(sheet_iterator, 29, Decision._VALUES_TO_NAMES.get(mp_history_item.decision))
|
865 |
sheet.write(sheet_iterator, 31, Decision._VALUES_TO_NAMES.get(mp_history_item.decision))
|
| 851 |
sheet.write(sheet_iterator, 30, mp_history_item.reason)
|
866 |
sheet.write(sheet_iterator, 32, mp_history_item.reason)
|
| 852 |
if Decision._VALUES_TO_NAMES.get(mp_history_item.decision) == "AUTO_DECREMENT_SUCCESS":
|
867 |
if Decision._VALUES_TO_NAMES.get(mp_history_item.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 853 |
sheet.write(sheet_iterator, 31, math.ceil(mp_history_item.proposedSellingPrice))
|
868 |
sheet.write(sheet_iterator, 33, math.ceil(mp_history_item.proposedSellingPrice))
|
| 854 |
if Decision._VALUES_TO_NAMES.get(mp_history_item.decision) == "AUTO_INCREMENT_SUCCESS":
|
869 |
if Decision._VALUES_TO_NAMES.get(mp_history_item.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 855 |
sheet.write(sheet_iterator, 31, math.ceil(mp_history_item.ourSellingPrice+max(10,.01*mp_history_item.ourSellingPrice)))
|
870 |
sheet.write(sheet_iterator, 33, math.ceil(mp_history_item.ourSellingPrice+max(10,.01*mp_history_item.ourSellingPrice)))
|
| 856 |
sheet_iterator+=1
|
871 |
sheet_iterator+=1
|
| 857 |
|
872 |
|
| 858 |
sheet = wbk.add_sheet('Negative Margin')
|
873 |
sheet = wbk.add_sheet('Negative Margin')
|
| 859 |
|
874 |
|
| 860 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
875 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 871... |
Line 886... |
| 871 |
sheet.write(0, 4, "Brand", heading_xf)
|
886 |
sheet.write(0, 4, "Brand", heading_xf)
|
| 872 |
sheet.write(0, 5, "Product Name", heading_xf)
|
887 |
sheet.write(0, 5, "Product Name", heading_xf)
|
| 873 |
sheet.write(0, 6, "Weight", heading_xf)
|
888 |
sheet.write(0, 6, "Weight", heading_xf)
|
| 874 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
889 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
| 875 |
sheet.write(0, 8, "Risky", heading_xf)
|
890 |
sheet.write(0, 8, "Risky", heading_xf)
|
| - |
|
891 |
sheet.write(0, 9, "Commission Rate", heading_xf)
|
| - |
|
892 |
sheet.write(0, 10, "Return Provision", heading_xf)
|
| 876 |
sheet.write(0, 9, "Our SP", heading_xf)
|
893 |
sheet.write(0, 11, "Our SP", heading_xf)
|
| 877 |
sheet.write(0, 11, "Our TP", heading_xf)
|
894 |
sheet.write(0, 13, "Our TP", heading_xf)
|
| 878 |
sheet.write(0, 12, "Lowest Possible TP", heading_xf)
|
895 |
sheet.write(0, 14, "Lowest Possible TP", heading_xf)
|
| 879 |
sheet.write(0, 10, "Our Offer Price", heading_xf)
|
896 |
sheet.write(0, 12, "Our Offer Price", heading_xf)
|
| 880 |
sheet.write(0, 13, "Our Rank", heading_xf)
|
897 |
sheet.write(0, 15, "Our Rank", heading_xf)
|
| 881 |
sheet.write(0, 14, "Our Snapdeal Inventory", heading_xf)
|
898 |
sheet.write(0, 16, "Our Snapdeal Inventory", heading_xf)
|
| 882 |
sheet.write(0, 15, "Net Availability", heading_xf)
|
899 |
sheet.write(0, 17, "Net Availability", heading_xf)
|
| 883 |
sheet.write(0, 16, "Last Five Day Sale", heading_xf)
|
900 |
sheet.write(0, 18, "Last Five Day Sale", heading_xf)
|
| 884 |
sheet.write(0, 17, "Average Sale", heading_xf)
|
901 |
sheet.write(0, 19, "Average Sale", heading_xf)
|
| 885 |
sheet.write(0, 18, "Our NLC", heading_xf)
|
902 |
sheet.write(0, 20, "Our NLC", heading_xf)
|
| 886 |
sheet.write(0, 19, "Margin", heading_xf)
|
903 |
sheet.write(0, 21, "Margin", heading_xf)
|
| 887 |
|
904 |
|
| 888 |
sheet_iterator=1
|
905 |
sheet_iterator=1
|
| 889 |
for item in negativeMargin:
|
906 |
for item in negativeMargin:
|
| 890 |
snapdealDetails = item[0]
|
907 |
snapdealDetails = item[0]
|
| 891 |
snapdealItemInfo = item[1]
|
908 |
snapdealItemInfo = item[1]
|
| 892 |
snapdealPricing = item[2]
|
909 |
snapdealPricing = item[2]
|
| - |
|
910 |
spmObj = snapdealItemInfo.sourcePercentage
|
| 893 |
sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
|
911 |
sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
|
| 894 |
sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
|
912 |
sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
|
| 895 |
sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
|
913 |
sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
|
| 896 |
sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
|
914 |
sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
|
| 897 |
sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
|
915 |
sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
|
| 898 |
sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
|
916 |
sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
|
| 899 |
sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
|
917 |
sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
|
| 900 |
sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
|
918 |
sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
|
| 901 |
sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
|
919 |
sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
|
| - |
|
920 |
sheet.write(sheet_iterator, 9, spmObj.commission)
|
| - |
|
921 |
sheet.write(sheet_iterator, 10, spmObj.returnProvision)
|
| 902 |
sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
|
922 |
sheet.write(sheet_iterator, 11, snapdealPricing.ourSp)
|
| 903 |
sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
|
923 |
sheet.write(sheet_iterator, 12, snapdealDetails.ourOfferPrice)
|
| 904 |
sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
|
924 |
sheet.write(sheet_iterator, 13, snapdealPricing.ourTp)
|
| 905 |
sheet.write(sheet_iterator, 12, snapdealPricing.lowestPossibleTp)
|
925 |
sheet.write(sheet_iterator, 14, snapdealPricing.lowestPossibleTp)
|
| 906 |
sheet.write(sheet_iterator, 13, snapdealDetails.rank)
|
926 |
sheet.write(sheet_iterator, 15, snapdealDetails.rank)
|
| 907 |
sheet.write(sheet_iterator, 14, snapdealDetails.ourInventory)
|
927 |
sheet.write(sheet_iterator, 16, snapdealDetails.ourInventory)
|
| 908 |
if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
|
928 |
if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
|
| 909 |
sheet.write(sheet_iterator, 15, 'Info not available')
|
929 |
sheet.write(sheet_iterator, 17, 'Info not available')
|
| 910 |
else:
|
930 |
else:
|
| 911 |
sheet.write(sheet_iterator, 15, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
|
931 |
sheet.write(sheet_iterator, 17, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
|
| 912 |
sheet.write(sheet_iterator, 16, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
|
932 |
sheet.write(sheet_iterator, 18, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
|
| 913 |
sheet.write(sheet_iterator, 17, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
|
933 |
sheet.write(sheet_iterator, 19, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
|
| 914 |
sheet.write(sheet_iterator, 18, snapdealItemInfo.nlc)
|
934 |
sheet.write(sheet_iterator, 20, snapdealItemInfo.nlc)
|
| 915 |
sheet.write(sheet_iterator, 19, round((snapdealPricing.ourTp - snapdealPricing.lowestPossibleTp),2))
|
935 |
sheet.write(sheet_iterator, 21, round((snapdealPricing.ourTp - snapdealPricing.lowestPossibleTp),2))
|
| 916 |
sheet_iterator+=1
|
936 |
sheet_iterator+=1
|
| 917 |
|
937 |
|
| 918 |
sheet = wbk.add_sheet('Exception Item List')
|
938 |
sheet = wbk.add_sheet('Exception Item List')
|
| 919 |
|
939 |
|
| 920 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
940 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 952... |
Line 972... |
| 952 |
sheet.write(0, 4, "Brand", heading_xf)
|
972 |
sheet.write(0, 4, "Brand", heading_xf)
|
| 953 |
sheet.write(0, 5, "Product Name", heading_xf)
|
973 |
sheet.write(0, 5, "Product Name", heading_xf)
|
| 954 |
sheet.write(0, 6, "Weight", heading_xf)
|
974 |
sheet.write(0, 6, "Weight", heading_xf)
|
| 955 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
975 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
| 956 |
sheet.write(0, 8, "Risky", heading_xf)
|
976 |
sheet.write(0, 8, "Risky", heading_xf)
|
| - |
|
977 |
sheet.write(0, 9, "Commission Rate", heading_xf)
|
| - |
|
978 |
sheet.write(0, 10, "Return Provision", heading_xf)
|
| 957 |
sheet.write(0, 9, "Our SP", heading_xf)
|
979 |
sheet.write(0, 11, "Our SP", heading_xf)
|
| 958 |
sheet.write(0, 11, "Our TP", heading_xf)
|
980 |
sheet.write(0, 13, "Our TP", heading_xf)
|
| 959 |
sheet.write(0, 10, "Our Offer Price", heading_xf)
|
981 |
sheet.write(0, 12, "Our Offer Price", heading_xf)
|
| 960 |
sheet.write(0, 12, "Our Rank", heading_xf)
|
982 |
sheet.write(0, 14, "Our Rank", heading_xf)
|
| 961 |
sheet.write(0, 13, "Lowest Seller", heading_xf)
|
983 |
sheet.write(0, 15, "Lowest Seller", heading_xf)
|
| 962 |
sheet.write(0, 14, "Lowest SP", heading_xf)
|
984 |
sheet.write(0, 16, "Lowest SP", heading_xf)
|
| 963 |
sheet.write(0, 15, "Lowest TP", heading_xf)
|
985 |
sheet.write(0, 17, "Lowest TP", heading_xf)
|
| 964 |
sheet.write(0, 16, "Lowest Offer Price", heading_xf)
|
986 |
sheet.write(0, 18, "Lowest Offer Price", heading_xf)
|
| 965 |
sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
|
987 |
sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
|
| 966 |
sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
|
988 |
sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
|
| 967 |
sheet.write(0, 19, "Our Net Availability",heading_xf)
|
989 |
sheet.write(0, 21, "Our Net Availability",heading_xf)
|
| 968 |
sheet.write(0, 20, "Last Five Day Sale", heading_xf)
|
990 |
sheet.write(0, 22, "Last Five Day Sale", heading_xf)
|
| 969 |
sheet.write(0, 21, "Average Sale", heading_xf)
|
991 |
sheet.write(0, 23, "Average Sale", heading_xf)
|
| 970 |
sheet.write(0, 22, "Our NLC", heading_xf)
|
992 |
sheet.write(0, 24, "Our NLC", heading_xf)
|
| 971 |
sheet.write(0, 23, "Lowest Possible TP", heading_xf)
|
993 |
sheet.write(0, 25, "Lowest Possible TP", heading_xf)
|
| 972 |
sheet.write(0, 24, "Lowest Possible SP", heading_xf)
|
994 |
sheet.write(0, 26, "Lowest Possible SP", heading_xf)
|
| 973 |
sheet.write(0, 25, "Subsidy Difference", heading_xf)
|
995 |
sheet.write(0, 27, "Subsidy Difference", heading_xf)
|
| 974 |
sheet.write(0, 26, "Target TP", heading_xf)
|
996 |
sheet.write(0, 28, "Target TP", heading_xf)
|
| 975 |
sheet.write(0, 27, "Target SP", heading_xf)
|
997 |
sheet.write(0, 29, "Target SP", heading_xf)
|
| 976 |
sheet.write(0, 28, "Target NLC", heading_xf)
|
998 |
sheet.write(0, 30, "Target NLC", heading_xf)
|
| 977 |
sheet.write(0, 29, "Sales Potential", heading_xf)
|
999 |
sheet.write(0, 31, "Sales Potential", heading_xf)
|
| 978 |
|
1000 |
|
| 979 |
sheet_iterator = 1
|
1001 |
sheet_iterator = 1
|
| 980 |
for item in competitiveNoInventory:
|
1002 |
for item in competitiveNoInventory:
|
| 981 |
snapdealDetails = item[0]
|
1003 |
snapdealDetails = item[0]
|
| 982 |
snapdealItemInfo = item[1]
|
1004 |
snapdealItemInfo = item[1]
|
| 983 |
snapdealPricing = item[2]
|
1005 |
snapdealPricing = item[2]
|
| 984 |
mpItem = item[3]
|
1006 |
mpItem = item[3]
|
| - |
|
1007 |
spmObj = snapdealItemInfo.sourcePercentage
|
| 985 |
if ((not inventoryMap.has_key(snapdealItemInfo.item_id)) or getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id))<=0):
|
1008 |
if ((not inventoryMap.has_key(snapdealItemInfo.item_id)) or getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id))<=0):
|
| 986 |
sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
|
1009 |
sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
|
| 987 |
sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
|
1010 |
sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
|
| 988 |
sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
|
1011 |
sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
|
| 989 |
sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
|
1012 |
sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
|
| 990 |
sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
|
1013 |
sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
|
| 991 |
sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
|
1014 |
sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
|
| 992 |
sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
|
1015 |
sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
|
| 993 |
sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
|
1016 |
sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
|
| 994 |
sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
|
1017 |
sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
|
| - |
|
1018 |
sheet.write(sheet_iterator, 9, spmObj.commission)
|
| - |
|
1019 |
sheet.write(sheet_iterator, 10, spmObj.returnProvision)
|
| 995 |
sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
|
1020 |
sheet.write(sheet_iterator, 11, snapdealPricing.ourSp)
|
| 996 |
sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
|
1021 |
sheet.write(sheet_iterator, 12, snapdealDetails.ourOfferPrice)
|
| 997 |
sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
|
1022 |
sheet.write(sheet_iterator, 13, snapdealPricing.ourTp)
|
| 998 |
sheet.write(sheet_iterator, 12, snapdealDetails.rank)
|
1023 |
sheet.write(sheet_iterator, 14, snapdealDetails.rank)
|
| 999 |
sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
|
1024 |
sheet.write(sheet_iterator, 15, snapdealDetails.lowestSellerName)
|
| 1000 |
sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
|
1025 |
sheet.write(sheet_iterator, 16, snapdealDetails.lowestSp)
|
| 1001 |
sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
|
1026 |
sheet.write(sheet_iterator, 17, snapdealPricing.lowestTp)
|
| 1002 |
sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
|
1027 |
sheet.write(sheet_iterator, 18, snapdealDetails.lowestOfferPrice)
|
| 1003 |
sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
|
1028 |
sheet.write(sheet_iterator, 19, snapdealDetails.otherInventory)
|
| 1004 |
sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
|
1029 |
sheet.write(sheet_iterator, 20, snapdealDetails.ourInventory)
|
| 1005 |
if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
|
1030 |
if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
|
| 1006 |
sheet.write(sheet_iterator, 19, 'Info not available')
|
1031 |
sheet.write(sheet_iterator, 21, 'Info not available')
|
| 1007 |
else:
|
1032 |
else:
|
| 1008 |
sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
|
1033 |
sheet.write(sheet_iterator, 21, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
|
| 1009 |
sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
|
1034 |
sheet.write(sheet_iterator, 22, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
|
| 1010 |
sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
|
1035 |
sheet.write(sheet_iterator, 23, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
|
| 1011 |
sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
|
1036 |
sheet.write(sheet_iterator, 24, snapdealItemInfo.nlc)
|
| 1012 |
sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
|
1037 |
sheet.write(sheet_iterator, 25, snapdealPricing.lowestPossibleTp)
|
| 1013 |
sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
|
1038 |
sheet.write(sheet_iterator, 26, snapdealPricing.lowestPossibleSp)
|
| 1014 |
sheet.write(sheet_iterator, 25, getSubsidyDiff(snapdealDetails))
|
1039 |
sheet.write(sheet_iterator, 27, getSubsidyDiff(snapdealDetails))
|
| 1015 |
if (snapdealPricing.competitionBasis=='SP'):
|
1040 |
if (snapdealPricing.competitionBasis=='SP'):
|
| 1016 |
proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
|
1041 |
proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
|
| 1017 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
1042 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 1018 |
target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
1043 |
target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
| 1019 |
else:
|
1044 |
else:
|
| 1020 |
#proposed_tp = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
|
1045 |
#proposed_tp = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
|
| 1021 |
#proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
|
1046 |
#proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
|
| 1022 |
proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001) -getSubsidyDiff(snapdealDetails)
|
1047 |
proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001) -getSubsidyDiff(snapdealDetails)
|
| 1023 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
1048 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 1024 |
target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
1049 |
target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
| 1025 |
sheet.write(sheet_iterator, 26, round(proposed_tp,2))
|
1050 |
sheet.write(sheet_iterator, 28, round(proposed_tp,2))
|
| 1026 |
sheet.write(sheet_iterator, 27, round(proposed_sp,2))
|
1051 |
sheet.write(sheet_iterator, 29, round(proposed_sp,2))
|
| 1027 |
sheet.write(sheet_iterator, 28, round(target_nlc,2))
|
1052 |
sheet.write(sheet_iterator, 30, round(target_nlc,2))
|
| 1028 |
sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
|
1053 |
sheet.write(sheet_iterator, 31, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
|
| 1029 |
sheet_iterator+=1
|
1054 |
sheet_iterator+=1
|
| 1030 |
|
1055 |
|
| 1031 |
sheet = wbk.add_sheet('Can Compete-No Inv On SD')
|
1056 |
sheet = wbk.add_sheet('Can Compete-No Inv On SD')
|
| 1032 |
|
1057 |
|
| 1033 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1058 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 1044... |
Line 1069... |
| 1044 |
sheet.write(0, 4, "Brand", heading_xf)
|
1069 |
sheet.write(0, 4, "Brand", heading_xf)
|
| 1045 |
sheet.write(0, 5, "Product Name", heading_xf)
|
1070 |
sheet.write(0, 5, "Product Name", heading_xf)
|
| 1046 |
sheet.write(0, 6, "Weight", heading_xf)
|
1071 |
sheet.write(0, 6, "Weight", heading_xf)
|
| 1047 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
1072 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
| 1048 |
sheet.write(0, 8, "Risky", heading_xf)
|
1073 |
sheet.write(0, 8, "Risky", heading_xf)
|
| - |
|
1074 |
sheet.write(0, 9, "Commission Rate", heading_xf)
|
| - |
|
1075 |
sheet.write(0, 10, "Return Provision", heading_xf)
|
| 1049 |
sheet.write(0, 9, "Our SP", heading_xf)
|
1076 |
sheet.write(0, 11, "Our SP", heading_xf)
|
| 1050 |
sheet.write(0, 11, "Our TP", heading_xf)
|
1077 |
sheet.write(0, 13, "Our TP", heading_xf)
|
| 1051 |
sheet.write(0, 10, "Our Offer Price", heading_xf)
|
1078 |
sheet.write(0, 12, "Our Offer Price", heading_xf)
|
| 1052 |
sheet.write(0, 12, "Our Rank", heading_xf)
|
1079 |
sheet.write(0, 14, "Our Rank", heading_xf)
|
| 1053 |
sheet.write(0, 13, "Lowest Seller", heading_xf)
|
1080 |
sheet.write(0, 15, "Lowest Seller", heading_xf)
|
| 1054 |
sheet.write(0, 14, "Lowest SP", heading_xf)
|
1081 |
sheet.write(0, 16, "Lowest SP", heading_xf)
|
| 1055 |
sheet.write(0, 15, "Lowest TP", heading_xf)
|
1082 |
sheet.write(0, 17, "Lowest TP", heading_xf)
|
| 1056 |
sheet.write(0, 16, "Lowest Offer Price", heading_xf)
|
1083 |
sheet.write(0, 18, "Lowest Offer Price", heading_xf)
|
| 1057 |
sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
|
1084 |
sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
|
| 1058 |
sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
|
1085 |
sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
|
| 1059 |
sheet.write(0, 19, "Our Net Availability",heading_xf)
|
1086 |
sheet.write(0, 21, "Our Net Availability",heading_xf)
|
| 1060 |
sheet.write(0, 20, "Last Five Day Sale", heading_xf)
|
1087 |
sheet.write(0, 22, "Last Five Day Sale", heading_xf)
|
| 1061 |
sheet.write(0, 21, "Average Sale", heading_xf)
|
1088 |
sheet.write(0, 23, "Average Sale", heading_xf)
|
| 1062 |
sheet.write(0, 22, "Our NLC", heading_xf)
|
1089 |
sheet.write(0, 24, "Our NLC", heading_xf)
|
| 1063 |
sheet.write(0, 23, "Lowest Possible TP", heading_xf)
|
1090 |
sheet.write(0, 25, "Lowest Possible TP", heading_xf)
|
| 1064 |
sheet.write(0, 24, "Lowest Possible SP", heading_xf)
|
1091 |
sheet.write(0, 26, "Lowest Possible SP", heading_xf)
|
| 1065 |
sheet.write(0, 25, "Subsidy Difference", heading_xf)
|
1092 |
sheet.write(0, 27, "Subsidy Difference", heading_xf)
|
| 1066 |
sheet.write(0, 26, "Target TP", heading_xf)
|
1093 |
sheet.write(0, 28, "Target TP", heading_xf)
|
| 1067 |
sheet.write(0, 27, "Target SP", heading_xf)
|
1094 |
sheet.write(0, 29, "Target SP", heading_xf)
|
| 1068 |
sheet.write(0, 28, "Target NLC", heading_xf)
|
1095 |
sheet.write(0, 30, "Target NLC", heading_xf)
|
| 1069 |
sheet.write(0, 29, "Sales Potential", heading_xf)
|
1096 |
sheet.write(0, 31, "Sales Potential", heading_xf)
|
| 1070 |
|
1097 |
|
| 1071 |
sheet_iterator = 1
|
1098 |
sheet_iterator = 1
|
| 1072 |
for item in competitiveNoInventory:
|
1099 |
for item in competitiveNoInventory:
|
| 1073 |
snapdealDetails = item[0]
|
1100 |
snapdealDetails = item[0]
|
| 1074 |
snapdealItemInfo = item[1]
|
1101 |
snapdealItemInfo = item[1]
|
| 1075 |
snapdealPricing = item[2]
|
1102 |
snapdealPricing = item[2]
|
| 1076 |
mpItem = item[3]
|
1103 |
mpItem = item[3]
|
| - |
|
1104 |
spmObj = snapdealItemInfo.sourcePercentage
|
| 1077 |
if (inventoryMap.has_key(snapdealItemInfo.item_id) and getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id))>0):
|
1105 |
if (inventoryMap.has_key(snapdealItemInfo.item_id) and getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id))>0):
|
| 1078 |
sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
|
1106 |
sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
|
| 1079 |
sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
|
1107 |
sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
|
| 1080 |
sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
|
1108 |
sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
|
| 1081 |
sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
|
1109 |
sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
|
| 1082 |
sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
|
1110 |
sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
|
| 1083 |
sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
|
1111 |
sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
|
| 1084 |
sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
|
1112 |
sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
|
| 1085 |
sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
|
1113 |
sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
|
| 1086 |
sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
|
1114 |
sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
|
| - |
|
1115 |
sheet.write(sheet_iterator, 9, spmObj.commission)
|
| - |
|
1116 |
sheet.write(sheet_iterator, 10, spmObj.returnProvision)
|
| 1087 |
sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
|
1117 |
sheet.write(sheet_iterator, 11, snapdealPricing.ourSp)
|
| 1088 |
sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
|
1118 |
sheet.write(sheet_iterator, 12, snapdealDetails.ourOfferPrice)
|
| 1089 |
sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
|
1119 |
sheet.write(sheet_iterator, 13, snapdealPricing.ourTp)
|
| 1090 |
sheet.write(sheet_iterator, 12, snapdealDetails.rank)
|
1120 |
sheet.write(sheet_iterator, 14, snapdealDetails.rank)
|
| 1091 |
sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
|
1121 |
sheet.write(sheet_iterator, 15, snapdealDetails.lowestSellerName)
|
| 1092 |
sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
|
1122 |
sheet.write(sheet_iterator, 16, snapdealDetails.lowestSp)
|
| 1093 |
sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
|
1123 |
sheet.write(sheet_iterator, 17, snapdealPricing.lowestTp)
|
| 1094 |
sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
|
1124 |
sheet.write(sheet_iterator, 18, snapdealDetails.lowestOfferPrice)
|
| 1095 |
sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
|
1125 |
sheet.write(sheet_iterator, 19, snapdealDetails.otherInventory)
|
| 1096 |
sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
|
1126 |
sheet.write(sheet_iterator, 20, snapdealDetails.ourInventory)
|
| 1097 |
if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
|
1127 |
if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
|
| 1098 |
sheet.write(sheet_iterator, 19, 'Info not available')
|
1128 |
sheet.write(sheet_iterator, 21, 'Info not available')
|
| 1099 |
else:
|
1129 |
else:
|
| 1100 |
sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
|
1130 |
sheet.write(sheet_iterator, 21, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
|
| 1101 |
sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
|
1131 |
sheet.write(sheet_iterator, 22, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
|
| 1102 |
sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
|
1132 |
sheet.write(sheet_iterator, 23, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
|
| 1103 |
sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
|
1133 |
sheet.write(sheet_iterator, 24, snapdealItemInfo.nlc)
|
| 1104 |
sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
|
1134 |
sheet.write(sheet_iterator, 25, snapdealPricing.lowestPossibleTp)
|
| 1105 |
sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
|
1135 |
sheet.write(sheet_iterator, 26, snapdealPricing.lowestPossibleSp)
|
| 1106 |
sheet.write(sheet_iterator, 25, getSubsidyDiff(snapdealDetails))
|
1136 |
sheet.write(sheet_iterator, 27, getSubsidyDiff(snapdealDetails))
|
| 1107 |
if (snapdealPricing.competitionBasis=='SP'):
|
1137 |
if (snapdealPricing.competitionBasis=='SP'):
|
| 1108 |
proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
|
1138 |
proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
|
| 1109 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
1139 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 1110 |
target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
1140 |
target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
| 1111 |
else:
|
1141 |
else:
|
| 1112 |
#proposed_tp = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
|
1142 |
#proposed_tp = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
|
| 1113 |
#proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
|
1143 |
#proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
|
| 1114 |
proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001) -getSubsidyDiff(snapdealDetails)
|
1144 |
proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001) -getSubsidyDiff(snapdealDetails)
|
| 1115 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
1145 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 1116 |
target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
1146 |
target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
|
| 1117 |
sheet.write(sheet_iterator, 26, round(proposed_tp,2))
|
1147 |
sheet.write(sheet_iterator, 28, round(proposed_tp,2))
|
| 1118 |
sheet.write(sheet_iterator, 27, round(proposed_sp,2))
|
1148 |
sheet.write(sheet_iterator, 29, round(proposed_sp,2))
|
| 1119 |
sheet.write(sheet_iterator, 28, round(target_nlc,2))
|
1149 |
sheet.write(sheet_iterator, 30, round(target_nlc,2))
|
| 1120 |
sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
|
1150 |
sheet.write(sheet_iterator, 31, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
|
| 1121 |
sheet_iterator+=1
|
1151 |
sheet_iterator+=1
|
| 1122 |
|
1152 |
|
| 1123 |
if (runType=='FULL'):
|
1153 |
if (runType=='FULL'):
|
| 1124 |
sheet = wbk.add_sheet('Auto Favorites')
|
1154 |
sheet = wbk.add_sheet('Auto Favorites')
|
| 1125 |
|
1155 |
|
| Line 1964... |
Line 1994... |
| 1964 |
successfulAutoIncrease = fetchItemsForAutoIncrease(timestamp)
|
1994 |
successfulAutoIncrease = fetchItemsForAutoIncrease(timestamp)
|
| 1965 |
if options.runType=='FULL':
|
1995 |
if options.runType=='FULL':
|
| 1966 |
previousAutoFav, nowAutoFav = markAutoFavourite()
|
1996 |
previousAutoFav, nowAutoFav = markAutoFavourite()
|
| 1967 |
if options.runType=='FULL':
|
1997 |
if options.runType=='FULL':
|
| 1968 |
writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, previousAutoFav, nowAutoFav,timestamp, options.runType)
|
1998 |
writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, previousAutoFav, nowAutoFav,timestamp, options.runType)
|
| - |
|
1999 |
try:
|
| 1969 |
sendAlertForNegativeMargins(timestamp)
|
2000 |
sendAlertForNegativeMargins(timestamp)
|
| - |
|
2001 |
except Exception as e:
|
| - |
|
2002 |
print "Unable to send neagtive margin alert due to ",e
|
| - |
|
2003 |
pass
|
| 1970 |
else:
|
2004 |
else:
|
| 1971 |
writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, None, None, timestamp, options.runType)
|
2005 |
writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, None, None, timestamp, options.runType)
|
| 1972 |
commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp)
|
2006 |
commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp)
|
| 1973 |
sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease)
|
2007 |
sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease)
|
| 1974 |
processLostBuyBoxItems(previousProcessingTimestamp[0],timestamp)
|
2008 |
processLostBuyBoxItems(previousProcessingTimestamp[0],timestamp)
|