| Line 662... |
Line 662... |
| 662 |
for negativeMarginItem in negativeMargin:
|
662 |
for negativeMarginItem in negativeMargin:
|
| 663 |
val = negativeMarginItem[0]
|
663 |
val = negativeMarginItem[0]
|
| 664 |
amDetails = negativeMarginItem[1]
|
664 |
amDetails = negativeMarginItem[1]
|
| 665 |
amPricing = negativeMarginItem[2]
|
665 |
amPricing = negativeMarginItem[2]
|
| 666 |
spm = val.sourcePercentage
|
666 |
spm = val.sourcePercentage
|
| - |
|
667 |
if amazonLongTermActivePromotions.has_key(val.sku):
|
| - |
|
668 |
subsidy = (amazonLongTermActivePromotions.get(val.sku)).subsidy
|
| - |
|
669 |
elif amazonShortTermActivePromotions.has_key(val.sku):
|
| - |
|
670 |
subsidy = (amazonShortTermActivePromotions.get(val.sku)).subsidy
|
| - |
|
671 |
else:
|
| - |
|
672 |
subsidy = 0
|
| 667 |
amazonScrapingHistory = AmazonScrapingHistory()
|
673 |
amazonScrapingHistory = AmazonScrapingHistory()
|
| 668 |
amazonScrapingHistory.item_id = val.sku[3:]
|
674 |
amazonScrapingHistory.item_id = val.sku[3:]
|
| 669 |
amazonScrapingHistory.asin = val.asin
|
675 |
amazonScrapingHistory.asin = val.asin
|
| 670 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
676 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
| 671 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
677 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
| 672 |
amazonScrapingHistory.ourSellingPrice = amDetails.ourSp
|
678 |
amazonScrapingHistory.ourSellingPrice = amDetails.ourSp
|
| 673 |
amazonScrapingHistory.promoPrice = amDetails.promoPrice
|
679 |
amazonScrapingHistory.promoPrice = amDetails.promoPrice
|
| - |
|
680 |
amazonScrapingHistory.subsidy = subsidy
|
| - |
|
681 |
amazonScrapingHistory.vatRate = val.vatRate
|
| 674 |
amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
|
682 |
amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
|
| 675 |
amazonScrapingHistory.ourRank = amDetails.ourRank
|
683 |
amazonScrapingHistory.ourRank = amDetails.ourRank
|
| 676 |
amazonScrapingHistory.ourInventory = val.ourInventory
|
684 |
amazonScrapingHistory.ourInventory = val.ourInventory
|
| 677 |
amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
|
685 |
amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
|
| 678 |
amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
|
686 |
amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
|
| Line 707... |
Line 715... |
| 707 |
for cheapestItem in cheapest:
|
715 |
for cheapestItem in cheapest:
|
| 708 |
val = cheapestItem[0]
|
716 |
val = cheapestItem[0]
|
| 709 |
amDetails = cheapestItem[1]
|
717 |
amDetails = cheapestItem[1]
|
| 710 |
amPricing = cheapestItem[2]
|
718 |
amPricing = cheapestItem[2]
|
| 711 |
spm = val.sourcePercentage
|
719 |
spm = val.sourcePercentage
|
| - |
|
720 |
if amazonLongTermActivePromotions.has_key(val.sku):
|
| - |
|
721 |
subsidy = (amazonLongTermActivePromotions.get(val.sku)).subsidy
|
| - |
|
722 |
elif amazonShortTermActivePromotions.has_key(val.sku):
|
| - |
|
723 |
subsidy = (amazonShortTermActivePromotions.get(val.sku)).subsidy
|
| - |
|
724 |
else:
|
| - |
|
725 |
subsidy = 0
|
| 712 |
amazonScrapingHistory = AmazonScrapingHistory()
|
726 |
amazonScrapingHistory = AmazonScrapingHistory()
|
| 713 |
amazonScrapingHistory.item_id = val.sku[3:]
|
727 |
amazonScrapingHistory.item_id = val.sku[3:]
|
| 714 |
amazonScrapingHistory.asin = val.asin
|
728 |
amazonScrapingHistory.asin = val.asin
|
| 715 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
729 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
| 716 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
730 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
| 717 |
amazonScrapingHistory.ourSellingPrice = amDetails.ourSp
|
731 |
amazonScrapingHistory.ourSellingPrice = amDetails.ourSp
|
| 718 |
amazonScrapingHistory.promoPrice = amDetails.promoPrice
|
732 |
amazonScrapingHistory.promoPrice = amDetails.promoPrice
|
| - |
|
733 |
amazonScrapingHistory.subsidy = subsidy
|
| - |
|
734 |
amazonScrapingHistory.vatRate = val.vatRate
|
| 719 |
amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
|
735 |
amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
|
| 720 |
amazonScrapingHistory.ourRank = amDetails.ourRank
|
736 |
amazonScrapingHistory.ourRank = amDetails.ourRank
|
| 721 |
amazonScrapingHistory.ourInventory = val.ourInventory
|
737 |
amazonScrapingHistory.ourInventory = val.ourInventory
|
| 722 |
amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
|
738 |
amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
|
| 723 |
amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
|
739 |
amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
|
| Line 764... |
Line 780... |
| 764 |
for amongCheapestAndCanCompeteItem in amongCheapestAndCanCompete:
|
780 |
for amongCheapestAndCanCompeteItem in amongCheapestAndCanCompete:
|
| 765 |
val = amongCheapestAndCanCompeteItem[0]
|
781 |
val = amongCheapestAndCanCompeteItem[0]
|
| 766 |
amDetails = amongCheapestAndCanCompeteItem[1]
|
782 |
amDetails = amongCheapestAndCanCompeteItem[1]
|
| 767 |
amPricing = amongCheapestAndCanCompeteItem[2]
|
783 |
amPricing = amongCheapestAndCanCompeteItem[2]
|
| 768 |
spm = val.sourcePercentage
|
784 |
spm = val.sourcePercentage
|
| - |
|
785 |
if amazonLongTermActivePromotions.has_key(val.sku):
|
| - |
|
786 |
subsidy = (amazonLongTermActivePromotions.get(val.sku)).subsidy
|
| - |
|
787 |
elif amazonShortTermActivePromotions.has_key(val.sku):
|
| - |
|
788 |
subsidy = (amazonShortTermActivePromotions.get(val.sku)).subsidy
|
| - |
|
789 |
else:
|
| - |
|
790 |
subsidy = 0
|
| 769 |
amazonScrapingHistory = AmazonScrapingHistory()
|
791 |
amazonScrapingHistory = AmazonScrapingHistory()
|
| 770 |
amazonScrapingHistory.item_id = val.sku[3:]
|
792 |
amazonScrapingHistory.item_id = val.sku[3:]
|
| 771 |
amazonScrapingHistory.asin = val.asin
|
793 |
amazonScrapingHistory.asin = val.asin
|
| 772 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
794 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
| 773 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
795 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
| 774 |
amazonScrapingHistory.ourSellingPrice = amDetails.ourSp
|
796 |
amazonScrapingHistory.ourSellingPrice = amDetails.ourSp
|
| 775 |
amazonScrapingHistory.promoPrice = amDetails.promoPrice
|
797 |
amazonScrapingHistory.promoPrice = amDetails.promoPrice
|
| - |
|
798 |
amazonScrapingHistory.subsidy = subsidy
|
| - |
|
799 |
amazonScrapingHistory.vatRate = val.vatRate
|
| 776 |
amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
|
800 |
amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
|
| 777 |
amazonScrapingHistory.ourRank = amDetails.ourRank
|
801 |
amazonScrapingHistory.ourRank = amDetails.ourRank
|
| 778 |
amazonScrapingHistory.ourInventory = val.ourInventory
|
802 |
amazonScrapingHistory.ourInventory = val.ourInventory
|
| 779 |
amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
|
803 |
amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
|
| 780 |
amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
|
804 |
amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
|
| Line 813... |
Line 837... |
| 813 |
for canCompeteItem in canCompete:
|
837 |
for canCompeteItem in canCompete:
|
| 814 |
val = canCompeteItem[0]
|
838 |
val = canCompeteItem[0]
|
| 815 |
amDetails = canCompeteItem[1]
|
839 |
amDetails = canCompeteItem[1]
|
| 816 |
amPricing = canCompeteItem[2]
|
840 |
amPricing = canCompeteItem[2]
|
| 817 |
spm = val.sourcePercentage
|
841 |
spm = val.sourcePercentage
|
| - |
|
842 |
if amazonLongTermActivePromotions.has_key(val.sku):
|
| - |
|
843 |
subsidy = (amazonLongTermActivePromotions.get(val.sku)).subsidy
|
| - |
|
844 |
elif amazonShortTermActivePromotions.has_key(val.sku):
|
| - |
|
845 |
subsidy = (amazonShortTermActivePromotions.get(val.sku)).subsidy
|
| - |
|
846 |
else:
|
| - |
|
847 |
subsidy = 0
|
| 818 |
amazonScrapingHistory = AmazonScrapingHistory()
|
848 |
amazonScrapingHistory = AmazonScrapingHistory()
|
| 819 |
amazonScrapingHistory.item_id = val.sku[3:]
|
849 |
amazonScrapingHistory.item_id = val.sku[3:]
|
| 820 |
amazonScrapingHistory.asin = val.asin
|
850 |
amazonScrapingHistory.asin = val.asin
|
| 821 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
851 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
| 822 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
852 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
| 823 |
amazonScrapingHistory.ourSellingPrice = amDetails.ourSp
|
853 |
amazonScrapingHistory.ourSellingPrice = amDetails.ourSp
|
| 824 |
amazonScrapingHistory.promoPrice = amDetails.promoPrice
|
854 |
amazonScrapingHistory.promoPrice = amDetails.promoPrice
|
| - |
|
855 |
amazonScrapingHistory.subsidy = subsidy
|
| - |
|
856 |
amazonScrapingHistory.vatRate = val.vatRate
|
| 825 |
amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
|
857 |
amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
|
| 826 |
amazonScrapingHistory.ourRank = amDetails.ourRank
|
858 |
amazonScrapingHistory.ourRank = amDetails.ourRank
|
| 827 |
amazonScrapingHistory.ourInventory = val.ourInventory
|
859 |
amazonScrapingHistory.ourInventory = val.ourInventory
|
| 828 |
amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
|
860 |
amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
|
| 829 |
amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
|
861 |
amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
|
| Line 862... |
Line 894... |
| 862 |
for almostCompeteItem in almostCompete:
|
894 |
for almostCompeteItem in almostCompete:
|
| 863 |
val = almostCompeteItem[0]
|
895 |
val = almostCompeteItem[0]
|
| 864 |
amDetails = almostCompeteItem[1]
|
896 |
amDetails = almostCompeteItem[1]
|
| 865 |
amPricing = almostCompeteItem[2]
|
897 |
amPricing = almostCompeteItem[2]
|
| 866 |
spm = val.sourcePercentage
|
898 |
spm = val.sourcePercentage
|
| - |
|
899 |
if amazonLongTermActivePromotions.has_key(val.sku):
|
| - |
|
900 |
subsidy = (amazonLongTermActivePromotions.get(val.sku)).subsidy
|
| - |
|
901 |
elif amazonShortTermActivePromotions.has_key(val.sku):
|
| - |
|
902 |
subsidy = (amazonShortTermActivePromotions.get(val.sku)).subsidy
|
| - |
|
903 |
else:
|
| - |
|
904 |
subsidy = 0
|
| 867 |
amazonScrapingHistory = AmazonScrapingHistory()
|
905 |
amazonScrapingHistory = AmazonScrapingHistory()
|
| 868 |
amazonScrapingHistory.item_id = val.sku[3:]
|
906 |
amazonScrapingHistory.item_id = val.sku[3:]
|
| 869 |
amazonScrapingHistory.asin = val.asin
|
907 |
amazonScrapingHistory.asin = val.asin
|
| 870 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
908 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
| 871 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
909 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
| 872 |
amazonScrapingHistory.ourSellingPrice = amDetails.ourSp
|
910 |
amazonScrapingHistory.ourSellingPrice = amDetails.ourSp
|
| 873 |
amazonScrapingHistory.promoPrice = amDetails.promoPrice
|
911 |
amazonScrapingHistory.promoPrice = amDetails.promoPrice
|
| - |
|
912 |
amazonScrapingHistory.subsidy = subsidy
|
| - |
|
913 |
amazonScrapingHistory.vatRate = val.vatRate
|
| 874 |
amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
|
914 |
amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
|
| 875 |
amazonScrapingHistory.ourRank = amDetails.ourRank
|
915 |
amazonScrapingHistory.ourRank = amDetails.ourRank
|
| 876 |
amazonScrapingHistory.ourInventory = val.ourInventory
|
916 |
amazonScrapingHistory.ourInventory = val.ourInventory
|
| 877 |
amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
|
917 |
amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
|
| 878 |
amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
|
918 |
amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
|
| Line 914... |
Line 954... |
| 914 |
for cantCompeteItem in cantCompete:
|
954 |
for cantCompeteItem in cantCompete:
|
| 915 |
val = cantCompeteItem[0]
|
955 |
val = cantCompeteItem[0]
|
| 916 |
amDetails = cantCompeteItem[1]
|
956 |
amDetails = cantCompeteItem[1]
|
| 917 |
amPricing = cantCompeteItem[2]
|
957 |
amPricing = cantCompeteItem[2]
|
| 918 |
spm = val.sourcePercentage
|
958 |
spm = val.sourcePercentage
|
| - |
|
959 |
if amazonLongTermActivePromotions.has_key(val.sku):
|
| - |
|
960 |
subsidy = (amazonLongTermActivePromotions.get(val.sku)).subsidy
|
| - |
|
961 |
elif amazonShortTermActivePromotions.has_key(val.sku):
|
| - |
|
962 |
subsidy = (amazonShortTermActivePromotions.get(val.sku)).subsidy
|
| - |
|
963 |
else:
|
| - |
|
964 |
subsidy = 0
|
| 919 |
amazonScrapingHistory = AmazonScrapingHistory()
|
965 |
amazonScrapingHistory = AmazonScrapingHistory()
|
| 920 |
amazonScrapingHistory.item_id = val.sku[3:]
|
966 |
amazonScrapingHistory.item_id = val.sku[3:]
|
| 921 |
amazonScrapingHistory.asin = val.asin
|
967 |
amazonScrapingHistory.asin = val.asin
|
| 922 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
968 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
| 923 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
969 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
| 924 |
amazonScrapingHistory.ourSellingPrice = amDetails.ourSp
|
970 |
amazonScrapingHistory.ourSellingPrice = amDetails.ourSp
|
| 925 |
amazonScrapingHistory.promoPrice = amDetails.promoPrice
|
971 |
amazonScrapingHistory.promoPrice = amDetails.promoPrice
|
| - |
|
972 |
amazonScrapingHistory.subsidy = subsidy
|
| - |
|
973 |
amazonScrapingHistory.vatRate = val.vatRate
|
| 926 |
amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
|
974 |
amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
|
| 927 |
amazonScrapingHistory.ourRank = amDetails.ourRank
|
975 |
amazonScrapingHistory.ourRank = amDetails.ourRank
|
| 928 |
amazonScrapingHistory.ourInventory = val.ourInventory
|
976 |
amazonScrapingHistory.ourInventory = val.ourInventory
|
| 929 |
amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
|
977 |
amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
|
| 930 |
amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
|
978 |
amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
|
| Line 1045... |
Line 1093... |
| 1045 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
1093 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
| 1046 |
sheet.write(0, 24, "WANLC", heading_xf)
|
1094 |
sheet.write(0, 24, "WANLC", heading_xf)
|
| 1047 |
sheet.write(0, 25, "Commission", heading_xf)
|
1095 |
sheet.write(0, 25, "Commission", heading_xf)
|
| 1048 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
1096 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
| 1049 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
1097 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
| - |
|
1098 |
sheet.write(0, 28, "Vat Rate", heading_xf)
|
| 1050 |
sheet.write(0, 28, "Margin", heading_xf)
|
1099 |
sheet.write(0, 29, "Margin", heading_xf)
|
| 1051 |
sheet.write(0, 29, "Risky", heading_xf)
|
1100 |
sheet.write(0, 30, "Risky", heading_xf)
|
| 1052 |
sheet.write(0, 30, "Proposed Sp", heading_xf)
|
1101 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
| 1053 |
sheet.write(0, 31, "Avg Sale", heading_xf)
|
1102 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
| 1054 |
sheet.write(0, 32, "Sales History", heading_xf)
|
1103 |
sheet.write(0, 33, "Sales History", heading_xf)
|
| 1055 |
|
1104 |
|
| 1056 |
sheet_iterator = 1
|
1105 |
sheet_iterator = 1
|
| 1057 |
cantCompeteItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1106 |
cantCompeteItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1058 |
for cantCompeteItem in cantCompeteItems:
|
1107 |
for cantCompeteItem in cantCompeteItems:
|
| 1059 |
amScraping = cantCompeteItem[0]
|
1108 |
amScraping = cantCompeteItem[0]
|
| Line 1096... |
Line 1145... |
| 1096 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
1145 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
| 1097 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
1146 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
| 1098 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
1147 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
| 1099 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
1148 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
| 1100 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
1149 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
| - |
|
1150 |
sheet.write(sheet_iterator, 28, amScraping.vatRate)
|
| 1101 |
sheet.write(sheet_iterator, 28, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1151 |
sheet.write(sheet_iterator, 29, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
| 1102 |
sheet.write(sheet_iterator, 29, item.risky)
|
1152 |
sheet.write(sheet_iterator, 30, item.risky)
|
| 1103 |
sheet.write(sheet_iterator, 30, amScraping.proposedSp)
|
1153 |
sheet.write(sheet_iterator, 31, amScraping.proposedSp)
|
| 1104 |
sheet.write(sheet_iterator, 31, amScraping.avgSale)
|
1154 |
sheet.write(sheet_iterator, 32, amScraping.avgSale)
|
| 1105 |
sheet.write(sheet_iterator, 32, getOosString(saleMap.get(sku)))
|
1155 |
sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
|
| 1106 |
sheet_iterator+=1
|
1156 |
sheet_iterator+=1
|
| 1107 |
|
1157 |
|
| 1108 |
sheet = wbk.add_sheet('Competitive')
|
1158 |
sheet = wbk.add_sheet('Competitive')
|
| 1109 |
xstr = lambda s: s or ""
|
1159 |
xstr = lambda s: s or ""
|
| 1110 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1160 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 1139... |
Line 1189... |
| 1139 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
1189 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
| 1140 |
sheet.write(0, 24, "WANLC", heading_xf)
|
1190 |
sheet.write(0, 24, "WANLC", heading_xf)
|
| 1141 |
sheet.write(0, 25, "Commission", heading_xf)
|
1191 |
sheet.write(0, 25, "Commission", heading_xf)
|
| 1142 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
1192 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
| 1143 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
1193 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
| - |
|
1194 |
sheet.write(0, 28, "Vat Rate", heading_xf)
|
| 1144 |
sheet.write(0, 28, "Margin", heading_xf)
|
1195 |
sheet.write(0, 29, "Margin", heading_xf)
|
| 1145 |
sheet.write(0, 29, "Risky", heading_xf)
|
1196 |
sheet.write(0, 30, "Risky", heading_xf)
|
| 1146 |
sheet.write(0, 30, "Proposed Sp", heading_xf)
|
1197 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
| 1147 |
sheet.write(0, 31, "Avg Sale", heading_xf)
|
1198 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
| 1148 |
sheet.write(0, 32, "Sales History", heading_xf)
|
1199 |
sheet.write(0, 33, "Sales History", heading_xf)
|
| 1149 |
sheet.write(0, 33, "Decision", heading_xf)
|
1200 |
sheet.write(0, 34, "Decision", heading_xf)
|
| 1150 |
sheet.write(0, 34, "Reason", heading_xf)
|
1201 |
sheet.write(0, 35, "Reason", heading_xf)
|
| 1151 |
sheet.write(0, 35, "Updated Price", heading_xf)
|
1202 |
sheet.write(0, 36, "Updated Price", heading_xf)
|
| 1152 |
|
1203 |
|
| 1153 |
sheet_iterator = 1
|
1204 |
sheet_iterator = 1
|
| 1154 |
competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1205 |
competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1155 |
for competitiveItem in competitiveItems:
|
1206 |
for competitiveItem in competitiveItems:
|
| 1156 |
amScraping = competitiveItem[0]
|
1207 |
amScraping = competitiveItem[0]
|
| Line 1193... |
Line 1244... |
| 1193 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
1244 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
| 1194 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
1245 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
| 1195 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
1246 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
| 1196 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
1247 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
| 1197 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
1248 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
| - |
|
1249 |
sheet.write(sheet_iterator, 28, amScraping.vatRate)
|
| 1198 |
sheet.write(sheet_iterator, 28, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1250 |
sheet.write(sheet_iterator, 29, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
| 1199 |
sheet.write(sheet_iterator, 29, item.risky)
|
1251 |
sheet.write(sheet_iterator, 30, item.risky)
|
| 1200 |
sheet.write(sheet_iterator, 30, amScraping.proposedSp)
|
1252 |
sheet.write(sheet_iterator, 31, amScraping.proposedSp)
|
| 1201 |
sheet.write(sheet_iterator, 31, amScraping.avgSale)
|
1253 |
sheet.write(sheet_iterator, 32, amScraping.avgSale)
|
| 1202 |
sheet.write(sheet_iterator, 32, getOosString(saleMap.get(sku)))
|
1254 |
sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
|
| 1203 |
if amScraping.decision is None:
|
1255 |
if amScraping.decision is None:
|
| 1204 |
sheet.write(sheet_iterator, 33, 'Auto Pricing Inactive')
|
1256 |
sheet.write(sheet_iterator, 34, 'Auto Pricing Inactive')
|
| 1205 |
sheet_iterator+=1
|
1257 |
sheet_iterator+=1
|
| 1206 |
continue
|
1258 |
continue
|
| 1207 |
sheet.write(sheet_iterator, 33, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
1259 |
sheet.write(sheet_iterator, 34, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
| 1208 |
sheet.write(sheet_iterator, 34, amScraping.reason)
|
1260 |
sheet.write(sheet_iterator, 35, amScraping.reason)
|
| 1209 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
1261 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 1210 |
sheet.write(sheet_iterator, 35, math.ceil(amScraping.proposedSp))
|
1262 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.proposedSp))
|
| 1211 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
1263 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 1212 |
sheet.write(sheet_iterator, 35, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
1264 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
| 1213 |
sheet_iterator+=1
|
1265 |
sheet_iterator+=1
|
| 1214 |
|
1266 |
|
| 1215 |
sheet = wbk.add_sheet('Almost Competitive')
|
1267 |
sheet = wbk.add_sheet('Almost Competitive')
|
| 1216 |
xstr = lambda s: s or ""
|
1268 |
xstr = lambda s: s or ""
|
| 1217 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1269 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 1246... |
Line 1298... |
| 1246 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
1298 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
| 1247 |
sheet.write(0, 24, "WANLC", heading_xf)
|
1299 |
sheet.write(0, 24, "WANLC", heading_xf)
|
| 1248 |
sheet.write(0, 25, "Commission", heading_xf)
|
1300 |
sheet.write(0, 25, "Commission", heading_xf)
|
| 1249 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
1301 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
| 1250 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
1302 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
| - |
|
1303 |
sheet.write(0, 28, "Vat Rate", heading_xf)
|
| 1251 |
sheet.write(0, 28, "Margin", heading_xf)
|
1304 |
sheet.write(0, 29, "Margin", heading_xf)
|
| 1252 |
sheet.write(0, 29, "Risky", heading_xf)
|
1305 |
sheet.write(0, 30, "Risky", heading_xf)
|
| 1253 |
sheet.write(0, 30, "Proposed Sp", heading_xf)
|
1306 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
| 1254 |
sheet.write(0, 31, "Avg Sale", heading_xf)
|
1307 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
| 1255 |
sheet.write(0, 32, "Sales History", heading_xf)
|
1308 |
sheet.write(0, 33, "Sales History", heading_xf)
|
| 1256 |
sheet.write(0, 33, "Decision", heading_xf)
|
1309 |
sheet.write(0, 34, "Decision", heading_xf)
|
| 1257 |
sheet.write(0, 34, "Reason", heading_xf)
|
1310 |
sheet.write(0, 35, "Reason", heading_xf)
|
| 1258 |
sheet.write(0, 35, "Updated Price", heading_xf)
|
1311 |
sheet.write(0, 36, "Updated Price", heading_xf)
|
| 1259 |
|
1312 |
|
| 1260 |
sheet_iterator = 1
|
1313 |
sheet_iterator = 1
|
| 1261 |
almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1314 |
almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1262 |
for almostCompetitiveItem in almostCompetitiveItems:
|
1315 |
for almostCompetitiveItem in almostCompetitiveItems:
|
| 1263 |
amScraping = almostCompetitiveItem[0]
|
1316 |
amScraping = almostCompetitiveItem[0]
|
| Line 1308... |
Line 1361... |
| 1308 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
1361 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
| 1309 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
1362 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
| 1310 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
1363 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
| 1311 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
1364 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
| 1312 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
1365 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
| - |
|
1366 |
sheet.write(sheet_iterator, 28, amScraping.vatRate)
|
| 1313 |
sheet.write(sheet_iterator, 28, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1367 |
sheet.write(sheet_iterator, 29, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
| 1314 |
sheet.write(sheet_iterator, 29, item.risky)
|
1368 |
sheet.write(sheet_iterator, 30, item.risky)
|
| 1315 |
sheet.write(sheet_iterator, 30, amScraping.proposedSp)
|
1369 |
sheet.write(sheet_iterator, 31, amScraping.proposedSp)
|
| 1316 |
sheet.write(sheet_iterator, 31, amScraping.avgSale)
|
1370 |
sheet.write(sheet_iterator, 32, amScraping.avgSale)
|
| 1317 |
sheet.write(sheet_iterator, 32, getOosString(saleMap.get(sku)))
|
1371 |
sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
|
| 1318 |
if amScraping.decision is None:
|
1372 |
if amScraping.decision is None:
|
| 1319 |
sheet.write(sheet_iterator, 33, 'Auto Pricing Inactive')
|
1373 |
sheet.write(sheet_iterator, 34, 'Auto Pricing Inactive')
|
| 1320 |
sheet_iterator+=1
|
1374 |
sheet_iterator+=1
|
| 1321 |
continue
|
1375 |
continue
|
| 1322 |
sheet.write(sheet_iterator, 33, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
1376 |
sheet.write(sheet_iterator, 34, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
| 1323 |
sheet.write(sheet_iterator, 34, amScraping.reason)
|
1377 |
sheet.write(sheet_iterator, 35, amScraping.reason)
|
| 1324 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
1378 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 1325 |
sheet.write(sheet_iterator, 35, math.ceil(amScraping.proposedSp))
|
1379 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.proposedSp))
|
| 1326 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
1380 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 1327 |
sheet.write(sheet_iterator, 35, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
1381 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
| 1328 |
sheet_iterator+=1
|
1382 |
sheet_iterator+=1
|
| 1329 |
|
1383 |
|
| 1330 |
sheet = wbk.add_sheet('Among Cheapest')
|
1384 |
sheet = wbk.add_sheet('Among Cheapest')
|
| 1331 |
xstr = lambda s: s or ""
|
1385 |
xstr = lambda s: s or ""
|
| 1332 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1386 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 1361... |
Line 1415... |
| 1361 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
1415 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
| 1362 |
sheet.write(0, 24, "WANLC", heading_xf)
|
1416 |
sheet.write(0, 24, "WANLC", heading_xf)
|
| 1363 |
sheet.write(0, 25, "Commission", heading_xf)
|
1417 |
sheet.write(0, 25, "Commission", heading_xf)
|
| 1364 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
1418 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
| 1365 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
1419 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
| - |
|
1420 |
sheet.write(0, 28, "Vat Rate", heading_xf)
|
| 1366 |
sheet.write(0, 28, "Margin", heading_xf)
|
1421 |
sheet.write(0, 29, "Margin", heading_xf)
|
| 1367 |
sheet.write(0, 29, "Risky", heading_xf)
|
1422 |
sheet.write(0, 30, "Risky", heading_xf)
|
| 1368 |
sheet.write(0, 30, "Proposed Sp", heading_xf)
|
1423 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
| 1369 |
sheet.write(0, 31, "Avg Sale", heading_xf)
|
1424 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
| 1370 |
sheet.write(0, 32, "Sales History", heading_xf)
|
1425 |
sheet.write(0, 33, "Sales History", heading_xf)
|
| 1371 |
sheet.write(0, 33, "Decision", heading_xf)
|
1426 |
sheet.write(0, 34, "Decision", heading_xf)
|
| 1372 |
sheet.write(0, 34, "Reason", heading_xf)
|
1427 |
sheet.write(0, 35, "Reason", heading_xf)
|
| 1373 |
sheet.write(0, 35, "Updated Price", heading_xf)
|
1428 |
sheet.write(0, 36, "Updated Price", heading_xf)
|
| 1374 |
|
1429 |
|
| 1375 |
sheet_iterator = 1
|
1430 |
sheet_iterator = 1
|
| 1376 |
amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1431 |
amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1377 |
for amongCheapestItem in amongCheapestItems:
|
1432 |
for amongCheapestItem in amongCheapestItems:
|
| 1378 |
amScraping = amongCheapestItem[0]
|
1433 |
amScraping = amongCheapestItem[0]
|
| Line 1415... |
Line 1470... |
| 1415 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
1470 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
| 1416 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
1471 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
| 1417 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
1472 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
| 1418 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
1473 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
| 1419 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
1474 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
| - |
|
1475 |
sheet.write(sheet_iterator, 28, amScraping.vatRate)
|
| 1420 |
sheet.write(sheet_iterator, 28, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1476 |
sheet.write(sheet_iterator, 29, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
| 1421 |
sheet.write(sheet_iterator, 29, item.risky)
|
1477 |
sheet.write(sheet_iterator, 30, item.risky)
|
| 1422 |
sheet.write(sheet_iterator, 30, amScraping.proposedSp)
|
1478 |
sheet.write(sheet_iterator, 31, amScraping.proposedSp)
|
| 1423 |
sheet.write(sheet_iterator, 31, amScraping.avgSale)
|
1479 |
sheet.write(sheet_iterator, 32, amScraping.avgSale)
|
| 1424 |
sheet.write(sheet_iterator, 32, getOosString(saleMap.get(sku)))
|
1480 |
sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
|
| 1425 |
if amScraping.decision is None:
|
1481 |
if amScraping.decision is None:
|
| 1426 |
sheet.write(sheet_iterator, 33, 'Auto Pricing Inactive')
|
1482 |
sheet.write(sheet_iterator, 34, 'Auto Pricing Inactive')
|
| 1427 |
sheet_iterator+=1
|
1483 |
sheet_iterator+=1
|
| 1428 |
continue
|
1484 |
continue
|
| 1429 |
sheet.write(sheet_iterator, 33, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
1485 |
sheet.write(sheet_iterator, 34, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
| 1430 |
sheet.write(sheet_iterator, 34, amScraping.reason)
|
1486 |
sheet.write(sheet_iterator, 35, amScraping.reason)
|
| 1431 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
1487 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 1432 |
sheet.write(sheet_iterator, 35, math.ceil(amScraping.proposedSp))
|
1488 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.proposedSp))
|
| 1433 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
1489 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 1434 |
sheet.write(sheet_iterator, 35, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
1490 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
| 1435 |
sheet_iterator+=1
|
1491 |
sheet_iterator+=1
|
| 1436 |
|
1492 |
|
| 1437 |
sheet = wbk.add_sheet('Cheapest')
|
1493 |
sheet = wbk.add_sheet('Cheapest')
|
| 1438 |
xstr = lambda s: s or ""
|
1494 |
xstr = lambda s: s or ""
|
| 1439 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1495 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 1468... |
Line 1524... |
| 1468 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
1524 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
| 1469 |
sheet.write(0, 24, "WANLC", heading_xf)
|
1525 |
sheet.write(0, 24, "WANLC", heading_xf)
|
| 1470 |
sheet.write(0, 25, "Commission", heading_xf)
|
1526 |
sheet.write(0, 25, "Commission", heading_xf)
|
| 1471 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
1527 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
| 1472 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
1528 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
| - |
|
1529 |
sheet.write(0, 28, "Vat Rate", heading_xf)
|
| 1473 |
sheet.write(0, 28, "Margin", heading_xf)
|
1530 |
sheet.write(0, 29, "Margin", heading_xf)
|
| 1474 |
sheet.write(0, 29, "Risky", heading_xf)
|
1531 |
sheet.write(0, 30, "Risky", heading_xf)
|
| 1475 |
sheet.write(0, 30, "Proposed Sp", heading_xf)
|
1532 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
| 1476 |
sheet.write(0, 31, "Avg Sale", heading_xf)
|
1533 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
| 1477 |
sheet.write(0, 32, "Sales History", heading_xf)
|
1534 |
sheet.write(0, 33, "Sales History", heading_xf)
|
| 1478 |
sheet.write(0, 33, "Decision", heading_xf)
|
1535 |
sheet.write(0, 34, "Decision", heading_xf)
|
| 1479 |
sheet.write(0, 34, "Reason", heading_xf)
|
1536 |
sheet.write(0, 35, "Reason", heading_xf)
|
| 1480 |
sheet.write(0, 35, "Updated Price", heading_xf)
|
1537 |
sheet.write(0, 36, "Updated Price", heading_xf)
|
| 1481 |
|
1538 |
|
| 1482 |
sheet_iterator = 1
|
1539 |
sheet_iterator = 1
|
| 1483 |
cheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1540 |
cheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1484 |
for cheapestItem in cheapestItems:
|
1541 |
for cheapestItem in cheapestItems:
|
| 1485 |
amScraping = cheapestItem[0]
|
1542 |
amScraping = cheapestItem[0]
|
| Line 1521... |
Line 1578... |
| 1521 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
|
1578 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
|
| 1522 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
1579 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
| 1523 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
1580 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
| 1524 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
1581 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
| 1525 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
1582 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
| - |
|
1583 |
sheet.write(sheet_iterator, 27, amScraping.vatRate)
|
| 1526 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
1584 |
sheet.write(sheet_iterator, 28, amScraping.returnProvision)
|
| 1527 |
sheet.write(sheet_iterator, 28, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1585 |
sheet.write(sheet_iterator, 29, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
| 1528 |
sheet.write(sheet_iterator, 29, item.risky)
|
1586 |
sheet.write(sheet_iterator, 30, item.risky)
|
| 1529 |
sheet.write(sheet_iterator, 30, amScraping.proposedSp)
|
1587 |
sheet.write(sheet_iterator, 31, amScraping.proposedSp)
|
| 1530 |
sheet.write(sheet_iterator, 31, amScraping.avgSale)
|
1588 |
sheet.write(sheet_iterator, 32, amScraping.avgSale)
|
| 1531 |
sheet.write(sheet_iterator, 32, getOosString(saleMap.get(sku)))
|
1589 |
sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
|
| 1532 |
if amScraping.decision is None:
|
1590 |
if amScraping.decision is None:
|
| 1533 |
sheet.write(sheet_iterator, 33, 'Auto Pricing Inactive')
|
1591 |
sheet.write(sheet_iterator, 34, 'Auto Pricing Inactive')
|
| 1534 |
sheet_iterator+=1
|
1592 |
sheet_iterator+=1
|
| 1535 |
continue
|
1593 |
continue
|
| 1536 |
sheet.write(sheet_iterator, 33, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
1594 |
sheet.write(sheet_iterator, 34, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
| 1537 |
sheet.write(sheet_iterator, 34, amScraping.reason)
|
1595 |
sheet.write(sheet_iterator, 35, amScraping.reason)
|
| 1538 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
1596 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 1539 |
sheet.write(sheet_iterator, 35, math.ceil(amScraping.proposedSp))
|
1597 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.proposedSp))
|
| 1540 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
1598 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 1541 |
sheet.write(sheet_iterator, 35, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
1599 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
| 1542 |
sheet_iterator+=1
|
1600 |
sheet_iterator+=1
|
| 1543 |
|
1601 |
|
| 1544 |
sheet = wbk.add_sheet('Negative Margin')
|
1602 |
sheet = wbk.add_sheet('Negative Margin')
|
| 1545 |
xstr = lambda s: s or ""
|
1603 |
xstr = lambda s: s or ""
|
| 1546 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1604 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 1575... |
Line 1633... |
| 1575 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
1633 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
| 1576 |
sheet.write(0, 24, "WANLC", heading_xf)
|
1634 |
sheet.write(0, 24, "WANLC", heading_xf)
|
| 1577 |
sheet.write(0, 25, "Commission", heading_xf)
|
1635 |
sheet.write(0, 25, "Commission", heading_xf)
|
| 1578 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
1636 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
| 1579 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
1637 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
| - |
|
1638 |
sheet.write(0, 28, "Vat Rate", heading_xf)
|
| 1580 |
sheet.write(0, 28, "Margin", heading_xf)
|
1639 |
sheet.write(0, 29, "Margin", heading_xf)
|
| 1581 |
sheet.write(0, 29, "Avg Sale", heading_xf)
|
1640 |
sheet.write(0, 30, "Avg Sale", heading_xf)
|
| 1582 |
sheet.write(0, 30, "Sales History", heading_xf)
|
1641 |
sheet.write(0, 31, "Sales History", heading_xf)
|
| 1583 |
|
1642 |
|
| 1584 |
sheet_iterator = 1
|
1643 |
sheet_iterator = 1
|
| 1585 |
amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1644 |
amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1586 |
for amongCheapestItem in amongCheapestItems:
|
1645 |
for amongCheapestItem in amongCheapestItems:
|
| 1587 |
amScraping = amongCheapestItem[0]
|
1646 |
amScraping = amongCheapestItem[0]
|
| Line 1624... |
Line 1683... |
| 1624 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
1683 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
| 1625 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
1684 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
| 1626 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
1685 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
| 1627 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
1686 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
| 1628 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
1687 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
| - |
|
1688 |
sheet.write(sheet_iterator, 28, amScraping.vatRate)
|
| 1629 |
sheet.write(sheet_iterator, 28, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1689 |
sheet.write(sheet_iterator, 29, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
| 1630 |
sheet.write(sheet_iterator, 29, amScraping.avgSale)
|
1690 |
sheet.write(sheet_iterator, 30, amScraping.avgSale)
|
| 1631 |
sheet.write(sheet_iterator, 30, getOosString(saleMap.get(sku)))
|
1691 |
sheet.write(sheet_iterator, 31, getOosString(saleMap.get(sku)))
|
| 1632 |
sheet_iterator+=1
|
1692 |
sheet_iterator+=1
|
| 1633 |
|
1693 |
|
| 1634 |
sheet = wbk.add_sheet('Exception List')
|
1694 |
sheet = wbk.add_sheet('Exception List')
|
| 1635 |
xstr = lambda s: s or ""
|
1695 |
xstr = lambda s: s or ""
|
| 1636 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1696 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|