| Line 36... |
Line 36... |
| 36 |
|
36 |
|
| 37 |
amazonAsinPrice={}
|
37 |
amazonAsinPrice={}
|
| 38 |
amazonLongTermActivePromotions = {}
|
38 |
amazonLongTermActivePromotions = {}
|
| 39 |
amazonShortTermActivePromotions = {}
|
39 |
amazonShortTermActivePromotions = {}
|
| 40 |
saleMap = {}
|
40 |
saleMap = {}
|
| - |
|
41 |
categoryMap = {}
|
| 41 |
DataService.initialize(db_hostname=host)
|
42 |
DataService.initialize(db_hostname=host)
|
| 42 |
|
43 |
|
| 43 |
amScraper = AmazonAsyncScraper.Products("AKIAII3SGRXBJDPCHSGQ", "B92xTbNBTYygbGs98w01nFQUhbec1pNCkCsKVfpg", "AF6E3O0VE0X4D")
|
44 |
amScraper = AmazonAsyncScraper.Products("AKIAII3SGRXBJDPCHSGQ", "B92xTbNBTYygbGs98w01nFQUhbec1pNCkCsKVfpg", "AF6E3O0VE0X4D")
|
| 44 |
|
45 |
|
| 45 |
class __AmazonItemInfo:
|
46 |
class __AmazonItemInfo:
|
| Line 218... |
Line 219... |
| 218 |
markReasonForItem(autoIncrementItem,'Maximum price increase in last 24 hours should be 2%',Decision.AUTO_INCREMENT_FAILED)
|
219 |
markReasonForItem(autoIncrementItem,'Maximum price increase in last 24 hours should be 2%',Decision.AUTO_INCREMENT_FAILED)
|
| 219 |
continue
|
220 |
continue
|
| 220 |
else:
|
221 |
else:
|
| 221 |
antecedentPrice = session.query(AmazonScrapingHistory.ourSellingPrice).filter(AmazonScrapingHistory.item_id==autoIncrementItem.item_id).filter(AmazonScrapingHistory.timestamp>time-timedelta(days=1)).order_by(asc(AmazonScrapingHistory.timestamp)).first()
|
222 |
antecedentPrice = session.query(AmazonScrapingHistory.ourSellingPrice).filter(AmazonScrapingHistory.item_id==autoIncrementItem.item_id).filter(AmazonScrapingHistory.timestamp>time-timedelta(days=1)).order_by(asc(AmazonScrapingHistory.timestamp)).first()
|
| 222 |
print "antecedentPrice else ",antecedentPrice
|
223 |
print "antecedentPrice else ",antecedentPrice
|
| 223 |
if antecedentPrice is not None:
|
224 |
if antecedentPrice is not None and antecedentPrice[0] is not None:
|
| 224 |
if float(math.ceil(autoIncrementItem.ourSellingPrice+max(10,.01*autoIncrementItem.ourSellingPrice))-math.ceil(antecedentPrice[0]+max(10,.01*antecedentPrice[0])))/math.ceil(antecedentPrice[0]+max(10,.01*antecedentPrice[0]))>.02:
|
225 |
if float(math.ceil(autoIncrementItem.ourSellingPrice+max(10,.01*autoIncrementItem.ourSellingPrice))-math.ceil(antecedentPrice[0]+max(10,.01*antecedentPrice[0])))/math.ceil(antecedentPrice[0]+max(10,.01*antecedentPrice[0]))>.02:
|
| 225 |
markReasonForItem(autoIncrementItem,'Maximum price increase in last 24 hours should be 2%',Decision.AUTO_INCREMENT_FAILED)
|
226 |
markReasonForItem(autoIncrementItem,'Maximum price increase in last 24 hours should be 2%',Decision.AUTO_INCREMENT_FAILED)
|
| 226 |
continue
|
227 |
continue
|
| 227 |
fbaSaleSnapshot = transaction_client.getAmazonFbaSalesLatestSnapshotForItemLocationWise(autoIncrementItem.item_id,autoIncrementItem.warehouseLocation)
|
228 |
fbaSaleSnapshot = transaction_client.getAmazonFbaSalesLatestSnapshotForItemLocationWise(autoIncrementItem.item_id,autoIncrementItem.warehouseLocation)
|
| 228 |
if getLastDaySale(fbaSaleSnapshot)<=2:
|
229 |
if getLastDaySale(fbaSaleSnapshot)<=2:
|
| Line 1063... |
Line 1064... |
| 1063 |
previouslyAutoFav.append(aItem.itemId)
|
1064 |
previouslyAutoFav.append(aItem.itemId)
|
| 1064 |
aItem.autoFavourite = toMark
|
1065 |
aItem.autoFavourite = toMark
|
| 1065 |
session.commit()
|
1066 |
session.commit()
|
| 1066 |
return previouslyAutoFav, nowAutoFav
|
1067 |
return previouslyAutoFav, nowAutoFav
|
| 1067 |
|
1068 |
|
| - |
|
1069 |
#Write the excel sheet headers for identical sheets
|
| - |
|
1070 |
def writeheaders(sheet,heading_xf):
|
| - |
|
1071 |
sheet.write(0, 0, "Item Id", heading_xf)
|
| - |
|
1072 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
| - |
|
1073 |
sheet.write(0, 2, "Asin", heading_xf)
|
| - |
|
1074 |
sheet.write(0, 3, "Location", heading_xf)
|
| - |
|
1075 |
sheet.write(0, 4, "Brand", heading_xf)
|
| - |
|
1076 |
sheet.write(0, 5, "Category", heading_xf)
|
| - |
|
1077 |
sheet.write(0, 6, "Product Name", heading_xf)
|
| - |
|
1078 |
sheet.write(0, 7, "Weight", heading_xf)
|
| - |
|
1079 |
sheet.write(0, 8, "Courier Cost", heading_xf)
|
| - |
|
1080 |
sheet.write(0, 9, "Our SP", heading_xf)
|
| - |
|
1081 |
sheet.write(0, 10, "Promo Price", heading_xf)
|
| - |
|
1082 |
sheet.write(0, 11, "Is Promotion", heading_xf)
|
| - |
|
1083 |
sheet.write(0, 12, "Lowest Possible SP", heading_xf)
|
| - |
|
1084 |
sheet.write(0, 13, "Rank", heading_xf)
|
| - |
|
1085 |
sheet.write(0, 14, "Our Inventory", heading_xf)
|
| - |
|
1086 |
sheet.write(0, 15, "Lowest Seller SP", heading_xf)
|
| - |
|
1087 |
sheet.write(0, 16, "Lowest Seller Rating", heading_xf)
|
| - |
|
1088 |
sheet.write(0, 17, "Lowest Seller Shipping Time", heading_xf)
|
| - |
|
1089 |
sheet.write(0, 18, "Second Lowest Seller SP", heading_xf)
|
| - |
|
1090 |
sheet.write(0, 19, "Second Lowest Seller Rating", heading_xf)
|
| - |
|
1091 |
sheet.write(0, 20, "Second Lowest Seller Shipping Time", heading_xf)
|
| - |
|
1092 |
sheet.write(0, 21, "Third Lowest Seller SP", heading_xf)
|
| - |
|
1093 |
sheet.write(0, 22, "Third Lowest Seller Rating", heading_xf)
|
| - |
|
1094 |
sheet.write(0, 23, "Third Lowest Seller Shipping Time", heading_xf)
|
| - |
|
1095 |
sheet.write(0, 24, "Other Cost", heading_xf)
|
| - |
|
1096 |
sheet.write(0, 25, "WANLC", heading_xf)
|
| - |
|
1097 |
sheet.write(0, 26, "Subsidy", heading_xf)
|
| - |
|
1098 |
sheet.write(0, 27, "Commission", heading_xf)
|
| - |
|
1099 |
sheet.write(0, 28, "Competitor Commission", heading_xf)
|
| - |
|
1100 |
sheet.write(0, 29, "Return Provision", heading_xf)
|
| - |
|
1101 |
sheet.write(0, 30, "Vat Rate", heading_xf)
|
| - |
|
1102 |
sheet.write(0, 31, "Margin", heading_xf)
|
| - |
|
1103 |
sheet.write(0, 32, "Risky", heading_xf)
|
| - |
|
1104 |
sheet.write(0, 33, "Proposed Sp", heading_xf)
|
| - |
|
1105 |
sheet.write(0, 34, "Avg Sale", heading_xf)
|
| - |
|
1106 |
sheet.write(0, 35, "Sales History", heading_xf)
|
| - |
|
1107 |
|
| - |
|
1108 |
def getPackagingCost(data):
|
| - |
|
1109 |
#TODO : Get packagingCost from marketplaceitems table
|
| - |
|
1110 |
return 15
|
| - |
|
1111 |
|
| - |
|
1112 |
def getReturnCost(data):
|
| - |
|
1113 |
return round(data.returnProvision * data.promoPrice/100)
|
| - |
|
1114 |
|
| - |
|
1115 |
def getServiceTax(data):
|
| - |
|
1116 |
#TODO : Get service tax from marketplaceitems table
|
| - |
|
1117 |
return 12.36
|
| - |
|
1118 |
|
| - |
|
1119 |
def getClosingFee(data):
|
| - |
|
1120 |
myClosingFee = 0
|
| - |
|
1121 |
return round(myClosingFee*(1+getServiceTax(data)/100))
|
| - |
|
1122 |
|
| - |
|
1123 |
def getCommission(data):
|
| - |
|
1124 |
return round((data.commission * data.promoPrice/100)*(1+getServiceTax(data)/100))
|
| - |
|
1125 |
|
| - |
|
1126 |
def getCourierCost(data):
|
| - |
|
1127 |
return round((data.courierCost)*(1+getServiceTax(data)/100))
|
| - |
|
1128 |
|
| - |
|
1129 |
def getMargin(amScraping):
|
| - |
|
1130 |
#sheet.write(sheet_iterator, 30, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
| - |
|
1131 |
#Promo Price minus costs plus subsidy
|
| - |
|
1132 |
#costs = WANLC (actual or overrrde whichever is applicable) + Courier cost + Closing fee + Commission + Packaging + VAT + Returns Cost + other cost.
|
| - |
|
1133 |
if(amScraping.ourSellingPrice >= amScraping.promoPrice):
|
| - |
|
1134 |
mySubsidy = amScraping.subsidy
|
| - |
|
1135 |
else:
|
| - |
|
1136 |
mySubsidy = 0
|
| - |
|
1137 |
print 'promo price ',amScraping.promoPrice
|
| - |
|
1138 |
print 'mySubsidy ',mySubsidy
|
| - |
|
1139 |
print 'wanlc ',amScraping.wanlc
|
| - |
|
1140 |
print 'courier cost ',getCourierCost(amScraping)
|
| - |
|
1141 |
print 'closing fee ',getClosingFee(amScraping)
|
| - |
|
1142 |
print 'commission ',getCommission(amScraping)
|
| - |
|
1143 |
print 'packaging ',getPackagingCost(amScraping)
|
| - |
|
1144 |
print 'vat ',getVat(amScraping)
|
| - |
|
1145 |
print 'return cost ',getReturnCost(amScraping)
|
| - |
|
1146 |
print 'other cost ',amScraping.otherCost
|
| - |
|
1147 |
myCosts = amScraping.wanlc + getCourierCost(amScraping) + getClosingFee(amScraping) + getCommission(amScraping) + getPackagingCost(amScraping) + getVat(amScraping) + getReturnCost(amScraping) + amScraping.otherCost
|
| - |
|
1148 |
margin = amScraping.promoPrice - myCosts + mySubsidy
|
| - |
|
1149 |
return round(margin)
|
| - |
|
1150 |
|
| - |
|
1151 |
def getVat(data):
|
| - |
|
1152 |
#VAT amount = Promo Price/(1+Vat Rate)*VAT Rate minus NLC/(1+VAT Rate)*VAT Rate
|
| - |
|
1153 |
myVatPercentage = data.vatRate/100
|
| - |
|
1154 |
myVat = myVatPercentage*(data.promoPrice/(1+myVatPercentage) - data.wanlc/(1+myVatPercentage))
|
| - |
|
1155 |
if(myVat<0):
|
| - |
|
1156 |
return 0
|
| - |
|
1157 |
else:
|
| - |
|
1158 |
return round(myVat)
|
| - |
|
1159 |
|
| - |
|
1160 |
def getCategory(data):
|
| - |
|
1161 |
global categoryMap
|
| - |
|
1162 |
return categoryMap[data.category]
|
| - |
|
1163 |
|
| 1068 |
def writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,runType):
|
1164 |
def writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,runType):
|
| 1069 |
wbk = xlwt.Workbook()
|
1165 |
wbk = xlwt.Workbook()
|
| 1070 |
sheet = wbk.add_sheet('Can\'t Compete')
|
1166 |
sheet = wbk.add_sheet('Can\'t Compete')
|
| 1071 |
xstr = lambda s: s or ""
|
1167 |
xstr = lambda s: s or ""
|
| 1072 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1168 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1073 |
|
1169 |
|
| 1074 |
excel_integer_format = '0'
|
1170 |
excel_integer_format = '0'
|
| 1075 |
integer_style = xlwt.XFStyle()
|
1171 |
integer_style = xlwt.XFStyle()
|
| 1076 |
integer_style.num_format_str = excel_integer_format
|
1172 |
integer_style.num_format_str = excel_integer_format
|
| - |
|
1173 |
writeheaders(sheet,heading_xf)
|
| 1077 |
|
1174 |
'''
|
| 1078 |
sheet.write(0, 0, "Item Id", heading_xf)
|
1175 |
sheet.write(0, 0, "Item Id", heading_xf)
|
| 1079 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
1176 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
| 1080 |
sheet.write(0, 2, "Asin", heading_xf)
|
1177 |
sheet.write(0, 2, "Asin", heading_xf)
|
| 1081 |
sheet.write(0, 3, "Location", heading_xf)
|
1178 |
sheet.write(0, 3, "Location", heading_xf)
|
| 1082 |
sheet.write(0, 4, "Brand", heading_xf)
|
1179 |
sheet.write(0, 4, "Brand", heading_xf)
|
| Line 1098... |
Line 1195... |
| 1098 |
sheet.write(0, 20, "Third Lowest Seller SP", heading_xf)
|
1195 |
sheet.write(0, 20, "Third Lowest Seller SP", heading_xf)
|
| 1099 |
sheet.write(0, 21, "Third Lowest Seller Rating", heading_xf)
|
1196 |
sheet.write(0, 21, "Third Lowest Seller Rating", heading_xf)
|
| 1100 |
sheet.write(0, 22, "Third Lowest Seller Shipping Time", heading_xf)
|
1197 |
sheet.write(0, 22, "Third Lowest Seller Shipping Time", heading_xf)
|
| 1101 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
1198 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
| 1102 |
sheet.write(0, 24, "WANLC", heading_xf)
|
1199 |
sheet.write(0, 24, "WANLC", heading_xf)
|
| - |
|
1200 |
sheet.write(0, 25, "Subsidy", heading_xf)
|
| 1103 |
sheet.write(0, 25, "Commission", heading_xf)
|
1201 |
sheet.write(0, 26, "Commission", heading_xf)
|
| 1104 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
1202 |
sheet.write(0, 27, "Competitor Commission", heading_xf)
|
| 1105 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
1203 |
sheet.write(0, 28, "Return Provision", heading_xf)
|
| 1106 |
sheet.write(0, 28, "Vat Rate", heading_xf)
|
1204 |
sheet.write(0, 29, "Vat Rate", heading_xf)
|
| 1107 |
sheet.write(0, 29, "Margin", heading_xf)
|
1205 |
sheet.write(0, 30, "Margin", heading_xf)
|
| 1108 |
sheet.write(0, 30, "Risky", heading_xf)
|
1206 |
sheet.write(0, 31, "Risky", heading_xf)
|
| 1109 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
1207 |
sheet.write(0, 32, "Proposed Sp", heading_xf)
|
| 1110 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
1208 |
sheet.write(0, 33, "Avg Sale", heading_xf)
|
| 1111 |
sheet.write(0, 33, "Sales History", heading_xf)
|
1209 |
sheet.write(0, 34, "Sales History", heading_xf)
|
| 1112 |
|
1210 |
'''
|
| 1113 |
sheet_iterator = 1
|
1211 |
sheet_iterator = 1
|
| 1114 |
cantCompeteItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1212 |
cantCompeteItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1115 |
for cantCompeteItem in cantCompeteItems:
|
1213 |
for cantCompeteItem in cantCompeteItems:
|
| 1116 |
amScraping = cantCompeteItem[0]
|
1214 |
amScraping = cantCompeteItem[0]
|
| 1117 |
item = cantCompeteItem[1]
|
1215 |
item = cantCompeteItem[1]
|
| - |
|
1216 |
print item
|
| 1118 |
sheet.write(sheet_iterator, 0, amScraping.item_id)
|
1217 |
sheet.write(sheet_iterator, 0, amScraping.item_id)
|
| 1119 |
if amScraping.warehouseLocation == 1:
|
1218 |
if amScraping.warehouseLocation == 1:
|
| 1120 |
sku = 'FBA'+str(amScraping.item_id)
|
1219 |
sku = 'FBA'+str(amScraping.item_id)
|
| 1121 |
loc = 'MUMBAI'
|
1220 |
loc = 'MUMBAI'
|
| 1122 |
else:
|
1221 |
else:
|
| Line 1124... |
Line 1223... |
| 1124 |
loc = 'BANGLORE'
|
1223 |
loc = 'BANGLORE'
|
| 1125 |
sheet.write(sheet_iterator, 1, sku)
|
1224 |
sheet.write(sheet_iterator, 1, sku)
|
| 1126 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
1225 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
| 1127 |
sheet.write(sheet_iterator, 3, loc)
|
1226 |
sheet.write(sheet_iterator, 3, loc)
|
| 1128 |
sheet.write(sheet_iterator, 4, item.brand)
|
1227 |
sheet.write(sheet_iterator, 4, item.brand)
|
| - |
|
1228 |
sheet.write(sheet_iterator, 5, getCategory(item))
|
| 1129 |
sheet.write(sheet_iterator, 5, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
1229 |
sheet.write(sheet_iterator, 6, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
| 1130 |
sheet.write(sheet_iterator, 6, item.weight)
|
1230 |
sheet.write(sheet_iterator, 7, item.weight)
|
| 1131 |
sheet.write(sheet_iterator, 7, amScraping.courierCost)
|
1231 |
sheet.write(sheet_iterator, 8, amScraping.courierCost)
|
| 1132 |
sheet.write(sheet_iterator, 8, amScraping.ourSellingPrice)
|
1232 |
sheet.write(sheet_iterator, 9, amScraping.ourSellingPrice)
|
| 1133 |
sheet.write(sheet_iterator, 9, amScraping.promoPrice)
|
1233 |
sheet.write(sheet_iterator, 10, amScraping.promoPrice)
|
| 1134 |
if amScraping.isPromotion:
|
1234 |
if amScraping.isPromotion:
|
| 1135 |
sheet.write(sheet_iterator, 10, "Yes")
|
1235 |
sheet.write(sheet_iterator, 11, "Yes")
|
| 1136 |
else:
|
1236 |
else:
|
| 1137 |
sheet.write(sheet_iterator, 10, "No")
|
1237 |
sheet.write(sheet_iterator, 11, "No")
|
| 1138 |
sheet.write(sheet_iterator, 11, amScraping.lowestPossibleSp)
|
1238 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
| 1139 |
if amScraping.ourRank > 3:
|
1239 |
if amScraping.ourRank > 3:
|
| 1140 |
sheet.write(sheet_iterator, 12, 'Greater than 3')
|
1240 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
| 1141 |
else:
|
1241 |
else:
|
| 1142 |
sheet.write(sheet_iterator, 12, amScraping.ourRank)
|
1242 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
| 1143 |
sheet.write(sheet_iterator, 13, amScraping.ourInventory)
|
1243 |
sheet.write(sheet_iterator, 14, amScraping.ourInventory)
|
| 1144 |
sheet.write(sheet_iterator, 14, amScraping.lowestSellerSp)
|
1244 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
|
| 1145 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerRating)
|
1245 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerRating)
|
| 1146 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerShippingTime)
|
1246 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerShippingTime)
|
| 1147 |
sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
|
1247 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerSp)
|
| 1148 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerRating)
|
1248 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerRating)
|
| 1149 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerShippingTime)
|
1249 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerShippingTime)
|
| 1150 |
sheet.write(sheet_iterator, 20, amScraping.thirdLowestSellerSp)
|
1250 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerSp)
|
| 1151 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerRating)
|
1251 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerRating)
|
| 1152 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
|
1252 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerShippingTime)
|
| 1153 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
1253 |
sheet.write(sheet_iterator, 24, amScraping.otherCost)
|
| 1154 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
1254 |
sheet.write(sheet_iterator, 25, amScraping.wanlc)
|
| - |
|
1255 |
sheet.write(sheet_iterator, 26, amScraping.subsidy)
|
| 1155 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
1256 |
sheet.write(sheet_iterator, 27, amScraping.commission)
|
| 1156 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
1257 |
sheet.write(sheet_iterator, 28, amScraping.competitorCommission)
|
| 1157 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
1258 |
sheet.write(sheet_iterator, 29, amScraping.returnProvision)
|
| 1158 |
sheet.write(sheet_iterator, 28, amScraping.vatRate)
|
1259 |
sheet.write(sheet_iterator, 30, amScraping.vatRate)
|
| 1159 |
sheet.write(sheet_iterator, 29, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1260 |
sheet.write(sheet_iterator, 31, getMargin(amScraping))
|
| 1160 |
sheet.write(sheet_iterator, 30, item.risky)
|
1261 |
sheet.write(sheet_iterator, 32, item.risky)
|
| 1161 |
sheet.write(sheet_iterator, 31, amScraping.proposedSp)
|
1262 |
sheet.write(sheet_iterator, 33, amScraping.proposedSp)
|
| 1162 |
sheet.write(sheet_iterator, 32, amScraping.avgSale)
|
1263 |
sheet.write(sheet_iterator, 34, amScraping.avgSale)
|
| 1163 |
sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
|
1264 |
sheet.write(sheet_iterator, 35, getOosString(saleMap.get(sku)))
|
| 1164 |
sheet_iterator+=1
|
1265 |
sheet_iterator+=1
|
| 1165 |
|
- |
|
| - |
|
1266 |
#TODO : Take excell sheet generation code inside a function
|
| 1166 |
sheet = wbk.add_sheet('Competitive')
|
1267 |
sheet = wbk.add_sheet('Competitive')
|
| 1167 |
xstr = lambda s: s or ""
|
1268 |
xstr = lambda s: s or ""
|
| 1168 |
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')
|
| 1169 |
|
1270 |
|
| 1170 |
excel_integer_format = '0'
|
1271 |
excel_integer_format = '0'
|
| 1171 |
integer_style = xlwt.XFStyle()
|
1272 |
integer_style = xlwt.XFStyle()
|
| 1172 |
integer_style.num_format_str = excel_integer_format
|
1273 |
integer_style.num_format_str = excel_integer_format
|
| 1173 |
|
1274 |
|
| - |
|
1275 |
writeheaders(sheet,heading_xf)
|
| - |
|
1276 |
'''
|
| 1174 |
sheet.write(0, 0, "Item Id", heading_xf)
|
1277 |
sheet.write(0, 0, "Item Id", heading_xf)
|
| 1175 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
1278 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
| 1176 |
sheet.write(0, 2, "Asin", heading_xf)
|
1279 |
sheet.write(0, 2, "Asin", heading_xf)
|
| 1177 |
sheet.write(0, 3, "Location", heading_xf)
|
1280 |
sheet.write(0, 3, "Location", heading_xf)
|
| 1178 |
sheet.write(0, 4, "Brand", heading_xf)
|
1281 |
sheet.write(0, 4, "Brand", heading_xf)
|
| Line 1203... |
Line 1306... |
| 1203 |
sheet.write(0, 29, "Margin", heading_xf)
|
1306 |
sheet.write(0, 29, "Margin", heading_xf)
|
| 1204 |
sheet.write(0, 30, "Risky", heading_xf)
|
1307 |
sheet.write(0, 30, "Risky", heading_xf)
|
| 1205 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
1308 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
| 1206 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
1309 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
| 1207 |
sheet.write(0, 33, "Sales History", heading_xf)
|
1310 |
sheet.write(0, 33, "Sales History", heading_xf)
|
| - |
|
1311 |
'''
|
| 1208 |
sheet.write(0, 34, "Decision", heading_xf)
|
1312 |
sheet.write(0, 36, "Decision", heading_xf)
|
| 1209 |
sheet.write(0, 35, "Reason", heading_xf)
|
1313 |
sheet.write(0, 37, "Reason", heading_xf)
|
| 1210 |
sheet.write(0, 36, "Updated Price", heading_xf)
|
1314 |
sheet.write(0, 38, "Updated Price", heading_xf)
|
| 1211 |
|
1315 |
|
| 1212 |
sheet_iterator = 1
|
1316 |
sheet_iterator = 1
|
| 1213 |
competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1317 |
competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1214 |
for competitiveItem in competitiveItems:
|
1318 |
for competitiveItem in competitiveItems:
|
| 1215 |
amScraping = competitiveItem[0]
|
1319 |
amScraping = competitiveItem[0]
|
| 1216 |
item = competitiveItem[1]
|
1320 |
item = competitiveItem[1]
|
| Line 1223... |
Line 1327... |
| 1223 |
loc = 'BANGLORE'
|
1327 |
loc = 'BANGLORE'
|
| 1224 |
sheet.write(sheet_iterator, 1, sku)
|
1328 |
sheet.write(sheet_iterator, 1, sku)
|
| 1225 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
1329 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
| 1226 |
sheet.write(sheet_iterator, 3, loc)
|
1330 |
sheet.write(sheet_iterator, 3, loc)
|
| 1227 |
sheet.write(sheet_iterator, 4, item.brand)
|
1331 |
sheet.write(sheet_iterator, 4, item.brand)
|
| - |
|
1332 |
sheet.write(sheet_iterator, 5, getCategory(item))
|
| 1228 |
sheet.write(sheet_iterator, 5, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
1333 |
sheet.write(sheet_iterator, 6, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
| 1229 |
sheet.write(sheet_iterator, 6, item.weight)
|
1334 |
sheet.write(sheet_iterator, 7, item.weight)
|
| 1230 |
sheet.write(sheet_iterator, 7, amScraping.courierCost)
|
1335 |
sheet.write(sheet_iterator, 8, amScraping.courierCost)
|
| 1231 |
sheet.write(sheet_iterator, 8, amScraping.ourSellingPrice)
|
1336 |
sheet.write(sheet_iterator, 9, amScraping.ourSellingPrice)
|
| 1232 |
sheet.write(sheet_iterator, 9, amScraping.promoPrice)
|
1337 |
sheet.write(sheet_iterator, 10, amScraping.promoPrice)
|
| 1233 |
if amScraping.isPromotion:
|
1338 |
if amScraping.isPromotion:
|
| 1234 |
sheet.write(sheet_iterator, 10, "Yes")
|
1339 |
sheet.write(sheet_iterator, 11, "Yes")
|
| 1235 |
else:
|
1340 |
else:
|
| 1236 |
sheet.write(sheet_iterator, 10, "No")
|
1341 |
sheet.write(sheet_iterator, 11, "No")
|
| 1237 |
sheet.write(sheet_iterator, 11, amScraping.lowestPossibleSp)
|
1342 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
| 1238 |
if amScraping.ourRank > 3:
|
1343 |
if amScraping.ourRank > 3:
|
| 1239 |
sheet.write(sheet_iterator, 12, 'Greater than 3')
|
1344 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
| 1240 |
else:
|
1345 |
else:
|
| 1241 |
sheet.write(sheet_iterator, 12, amScraping.ourRank)
|
1346 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
| 1242 |
sheet.write(sheet_iterator, 13, amScraping.ourInventory)
|
1347 |
sheet.write(sheet_iterator, 14, amScraping.ourInventory)
|
| 1243 |
sheet.write(sheet_iterator, 14, amScraping.lowestSellerSp)
|
1348 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
|
| 1244 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerRating)
|
1349 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerRating)
|
| 1245 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerShippingTime)
|
1350 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerShippingTime)
|
| 1246 |
sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
|
1351 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerSp)
|
| 1247 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerRating)
|
1352 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerRating)
|
| 1248 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerShippingTime)
|
1353 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerShippingTime)
|
| 1249 |
sheet.write(sheet_iterator, 20, amScraping.thirdLowestSellerSp)
|
1354 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerSp)
|
| 1250 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerRating)
|
1355 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerRating)
|
| 1251 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
|
1356 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerShippingTime)
|
| 1252 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
1357 |
sheet.write(sheet_iterator, 24, amScraping.otherCost)
|
| 1253 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
1358 |
sheet.write(sheet_iterator, 25, amScraping.wanlc)
|
| - |
|
1359 |
sheet.write(sheet_iterator, 26, amScraping.subsidy)
|
| 1254 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
1360 |
sheet.write(sheet_iterator, 27, amScraping.commission)
|
| 1255 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
1361 |
sheet.write(sheet_iterator, 28, amScraping.competitorCommission)
|
| 1256 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
1362 |
sheet.write(sheet_iterator, 29, amScraping.returnProvision)
|
| 1257 |
sheet.write(sheet_iterator, 28, amScraping.vatRate)
|
1363 |
sheet.write(sheet_iterator, 30, amScraping.vatRate)
|
| 1258 |
sheet.write(sheet_iterator, 29, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1364 |
sheet.write(sheet_iterator, 31, getMargin(amScraping))
|
| 1259 |
sheet.write(sheet_iterator, 30, item.risky)
|
1365 |
sheet.write(sheet_iterator, 32, item.risky)
|
| 1260 |
sheet.write(sheet_iterator, 31, amScraping.proposedSp)
|
1366 |
sheet.write(sheet_iterator, 33, amScraping.proposedSp)
|
| 1261 |
sheet.write(sheet_iterator, 32, amScraping.avgSale)
|
1367 |
sheet.write(sheet_iterator, 34, amScraping.avgSale)
|
| 1262 |
sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
|
1368 |
sheet.write(sheet_iterator, 35, getOosString(saleMap.get(sku)))
|
| 1263 |
if amScraping.decision is None:
|
1369 |
if amScraping.decision is None:
|
| 1264 |
sheet.write(sheet_iterator, 34, 'Auto Pricing Inactive')
|
1370 |
sheet.write(sheet_iterator, 36, 'Auto Pricing Inactive')
|
| 1265 |
sheet_iterator+=1
|
1371 |
sheet_iterator+=1
|
| 1266 |
continue
|
1372 |
continue
|
| 1267 |
sheet.write(sheet_iterator, 34, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
1373 |
sheet.write(sheet_iterator, 36, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
| 1268 |
sheet.write(sheet_iterator, 35, amScraping.reason)
|
1374 |
sheet.write(sheet_iterator, 37, amScraping.reason)
|
| 1269 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
1375 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 1270 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.proposedSp))
|
1376 |
sheet.write(sheet_iterator, 38, math.ceil(amScraping.proposedSp))
|
| 1271 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
1377 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 1272 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
1378 |
sheet.write(sheet_iterator, 38, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
| 1273 |
sheet_iterator+=1
|
1379 |
sheet_iterator+=1
|
| 1274 |
|
1380 |
|
| 1275 |
sheet = wbk.add_sheet('Almost Competitive')
|
1381 |
sheet = wbk.add_sheet('Almost Competitive')
|
| 1276 |
xstr = lambda s: s or ""
|
1382 |
xstr = lambda s: s or ""
|
| 1277 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1383 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1278 |
|
1384 |
|
| 1279 |
excel_integer_format = '0'
|
1385 |
excel_integer_format = '0'
|
| 1280 |
integer_style = xlwt.XFStyle()
|
1386 |
integer_style = xlwt.XFStyle()
|
| 1281 |
integer_style.num_format_str = excel_integer_format
|
1387 |
integer_style.num_format_str = excel_integer_format
|
| - |
|
1388 |
writeheaders(sheet,heading_xf)
|
| 1282 |
|
1389 |
'''
|
| 1283 |
sheet.write(0, 0, "Item Id", heading_xf)
|
1390 |
sheet.write(0, 0, "Item Id", heading_xf)
|
| 1284 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
1391 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
| 1285 |
sheet.write(0, 2, "Asin", heading_xf)
|
1392 |
sheet.write(0, 2, "Asin", heading_xf)
|
| 1286 |
sheet.write(0, 3, "Location", heading_xf)
|
1393 |
sheet.write(0, 3, "Location", heading_xf)
|
| 1287 |
sheet.write(0, 4, "Brand", heading_xf)
|
1394 |
sheet.write(0, 4, "Brand", heading_xf)
|
| Line 1312... |
Line 1419... |
| 1312 |
sheet.write(0, 29, "Margin", heading_xf)
|
1419 |
sheet.write(0, 29, "Margin", heading_xf)
|
| 1313 |
sheet.write(0, 30, "Risky", heading_xf)
|
1420 |
sheet.write(0, 30, "Risky", heading_xf)
|
| 1314 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
1421 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
| 1315 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
1422 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
| 1316 |
sheet.write(0, 33, "Sales History", heading_xf)
|
1423 |
sheet.write(0, 33, "Sales History", heading_xf)
|
| - |
|
1424 |
'''
|
| 1317 |
sheet.write(0, 34, "Decision", heading_xf)
|
1425 |
sheet.write(0, 36, "Decision", heading_xf)
|
| 1318 |
sheet.write(0, 35, "Reason", heading_xf)
|
1426 |
sheet.write(0, 37, "Reason", heading_xf)
|
| 1319 |
sheet.write(0, 36, "Updated Price", heading_xf)
|
1427 |
sheet.write(0, 38, "Updated Price", heading_xf)
|
| 1320 |
|
- |
|
| 1321 |
sheet_iterator = 1
|
1428 |
sheet_iterator = 1
|
| 1322 |
almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1429 |
almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1323 |
for almostCompetitiveItem in almostCompetitiveItems:
|
1430 |
for almostCompetitiveItem in almostCompetitiveItems:
|
| 1324 |
amScraping = almostCompetitiveItem[0]
|
1431 |
amScraping = almostCompetitiveItem[0]
|
| 1325 |
item = almostCompetitiveItem[1]
|
1432 |
item = almostCompetitiveItem[1]
|
| Line 1340... |
Line 1447... |
| 1340 |
loc = 'BANGLORE'
|
1447 |
loc = 'BANGLORE'
|
| 1341 |
sheet.write(sheet_iterator, 1, sku)
|
1448 |
sheet.write(sheet_iterator, 1, sku)
|
| 1342 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
1449 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
| 1343 |
sheet.write(sheet_iterator, 3, loc)
|
1450 |
sheet.write(sheet_iterator, 3, loc)
|
| 1344 |
sheet.write(sheet_iterator, 4, item.brand)
|
1451 |
sheet.write(sheet_iterator, 4, item.brand)
|
| - |
|
1452 |
sheet.write(sheet_iterator, 5, getCategory(item))
|
| 1345 |
sheet.write(sheet_iterator, 5, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
1453 |
sheet.write(sheet_iterator, 6, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
| 1346 |
sheet.write(sheet_iterator, 6, item.weight)
|
1454 |
sheet.write(sheet_iterator, 7, item.weight)
|
| 1347 |
sheet.write(sheet_iterator, 7, amScraping.courierCost)
|
1455 |
sheet.write(sheet_iterator, 8, amScraping.courierCost)
|
| 1348 |
sheet.write(sheet_iterator, 8, amScraping.ourSellingPrice)
|
1456 |
sheet.write(sheet_iterator, 9, amScraping.ourSellingPrice)
|
| 1349 |
sheet.write(sheet_iterator, 9, amScraping.promoPrice)
|
1457 |
sheet.write(sheet_iterator, 10, amScraping.promoPrice)
|
| 1350 |
if amScraping.isPromotion:
|
1458 |
if amScraping.isPromotion:
|
| 1351 |
sheet.write(sheet_iterator, 10, "Yes")
|
1459 |
sheet.write(sheet_iterator, 11, "Yes")
|
| 1352 |
else:
|
1460 |
else:
|
| 1353 |
sheet.write(sheet_iterator, 10, "No")
|
1461 |
sheet.write(sheet_iterator, 11, "No")
|
| 1354 |
sheet.write(sheet_iterator, 11, amScraping.lowestPossibleSp)
|
1462 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
| 1355 |
if amScraping.ourRank > 3:
|
1463 |
if amScraping.ourRank > 3:
|
| 1356 |
sheet.write(sheet_iterator, 12, 'Greater than 3')
|
1464 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
| 1357 |
else:
|
1465 |
else:
|
| 1358 |
sheet.write(sheet_iterator, 12, amScraping.ourRank)
|
1466 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
| 1359 |
sheet.write(sheet_iterator, 13, amScraping.ourInventory)
|
1467 |
sheet.write(sheet_iterator, 14, amScraping.ourInventory)
|
| 1360 |
sheet.write(sheet_iterator, 14, amScraping.lowestSellerSp)
|
1468 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
|
| 1361 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerRating)
|
1469 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerRating)
|
| 1362 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerShippingTime)
|
1470 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerShippingTime)
|
| 1363 |
sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
|
1471 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerSp)
|
| 1364 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerRating)
|
1472 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerRating)
|
| 1365 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerShippingTime)
|
1473 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerShippingTime)
|
| 1366 |
sheet.write(sheet_iterator, 20, amScraping.thirdLowestSellerSp)
|
1474 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerSp)
|
| 1367 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerRating)
|
1475 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerRating)
|
| 1368 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
|
1476 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerShippingTime)
|
| 1369 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
1477 |
sheet.write(sheet_iterator, 24, amScraping.otherCost)
|
| 1370 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
1478 |
sheet.write(sheet_iterator, 25, amScraping.wanlc)
|
| - |
|
1479 |
sheet.write(sheet_iterator, 26, amScraping.subsidy)
|
| 1371 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
1480 |
sheet.write(sheet_iterator, 27, amScraping.commission)
|
| 1372 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
1481 |
sheet.write(sheet_iterator, 28, amScraping.competitorCommission)
|
| 1373 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
1482 |
sheet.write(sheet_iterator, 29, amScraping.returnProvision)
|
| 1374 |
sheet.write(sheet_iterator, 28, amScraping.vatRate)
|
1483 |
sheet.write(sheet_iterator, 30, amScraping.vatRate)
|
| 1375 |
sheet.write(sheet_iterator, 29, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1484 |
sheet.write(sheet_iterator, 31, getMargin(amScraping))
|
| 1376 |
sheet.write(sheet_iterator, 30, item.risky)
|
1485 |
sheet.write(sheet_iterator, 32, item.risky)
|
| 1377 |
sheet.write(sheet_iterator, 31, amScraping.proposedSp)
|
1486 |
sheet.write(sheet_iterator, 33, amScraping.proposedSp)
|
| 1378 |
sheet.write(sheet_iterator, 32, amScraping.avgSale)
|
1487 |
sheet.write(sheet_iterator, 34, amScraping.avgSale)
|
| 1379 |
sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
|
1488 |
sheet.write(sheet_iterator, 35, getOosString(saleMap.get(sku)))
|
| 1380 |
if amScraping.decision is None:
|
1489 |
if amScraping.decision is None:
|
| 1381 |
sheet.write(sheet_iterator, 34, 'Auto Pricing Inactive')
|
1490 |
sheet.write(sheet_iterator, 36, 'Auto Pricing Inactive')
|
| 1382 |
sheet_iterator+=1
|
1491 |
sheet_iterator+=1
|
| 1383 |
continue
|
1492 |
continue
|
| 1384 |
sheet.write(sheet_iterator, 34, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
1493 |
sheet.write(sheet_iterator, 36, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
| 1385 |
sheet.write(sheet_iterator, 35, amScraping.reason)
|
1494 |
sheet.write(sheet_iterator, 37, amScraping.reason)
|
| 1386 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
1495 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 1387 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.proposedSp))
|
1496 |
sheet.write(sheet_iterator, 38, math.ceil(amScraping.proposedSp))
|
| 1388 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
1497 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 1389 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
1498 |
sheet.write(sheet_iterator, 38, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
| 1390 |
sheet_iterator+=1
|
1499 |
sheet_iterator+=1
|
| 1391 |
|
1500 |
|
| 1392 |
sheet = wbk.add_sheet('Among Cheapest')
|
1501 |
sheet = wbk.add_sheet('Among Cheapest')
|
| 1393 |
xstr = lambda s: s or ""
|
1502 |
xstr = lambda s: s or ""
|
| 1394 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1503 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1395 |
|
1504 |
|
| 1396 |
excel_integer_format = '0'
|
1505 |
excel_integer_format = '0'
|
| 1397 |
integer_style = xlwt.XFStyle()
|
1506 |
integer_style = xlwt.XFStyle()
|
| 1398 |
integer_style.num_format_str = excel_integer_format
|
1507 |
integer_style.num_format_str = excel_integer_format
|
| - |
|
1508 |
writeheaders(sheet,heading_xf)
|
| 1399 |
|
1509 |
'''
|
| 1400 |
sheet.write(0, 0, "Item Id", heading_xf)
|
1510 |
sheet.write(0, 0, "Item Id", heading_xf)
|
| 1401 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
1511 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
| 1402 |
sheet.write(0, 2, "Asin", heading_xf)
|
1512 |
sheet.write(0, 2, "Asin", heading_xf)
|
| 1403 |
sheet.write(0, 3, "Location", heading_xf)
|
1513 |
sheet.write(0, 3, "Location", heading_xf)
|
| 1404 |
sheet.write(0, 4, "Brand", heading_xf)
|
1514 |
sheet.write(0, 4, "Brand", heading_xf)
|
| Line 1429... |
Line 1539... |
| 1429 |
sheet.write(0, 29, "Margin", heading_xf)
|
1539 |
sheet.write(0, 29, "Margin", heading_xf)
|
| 1430 |
sheet.write(0, 30, "Risky", heading_xf)
|
1540 |
sheet.write(0, 30, "Risky", heading_xf)
|
| 1431 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
1541 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
| 1432 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
1542 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
| 1433 |
sheet.write(0, 33, "Sales History", heading_xf)
|
1543 |
sheet.write(0, 33, "Sales History", heading_xf)
|
| - |
|
1544 |
'''
|
| 1434 |
sheet.write(0, 34, "Decision", heading_xf)
|
1545 |
sheet.write(0, 36, "Decision", heading_xf)
|
| 1435 |
sheet.write(0, 35, "Reason", heading_xf)
|
1546 |
sheet.write(0, 37, "Reason", heading_xf)
|
| 1436 |
sheet.write(0, 36, "Updated Price", heading_xf)
|
1547 |
sheet.write(0, 38, "Updated Price", heading_xf)
|
| 1437 |
|
- |
|
| 1438 |
sheet_iterator = 1
|
1548 |
sheet_iterator = 1
|
| 1439 |
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()
|
1549 |
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()
|
| 1440 |
for amongCheapestItem in amongCheapestItems:
|
1550 |
for amongCheapestItem in amongCheapestItems:
|
| 1441 |
amScraping = amongCheapestItem[0]
|
1551 |
amScraping = amongCheapestItem[0]
|
| 1442 |
item = amongCheapestItem[1]
|
1552 |
item = amongCheapestItem[1]
|
| Line 1449... |
Line 1559... |
| 1449 |
loc = 'BANGLORE'
|
1559 |
loc = 'BANGLORE'
|
| 1450 |
sheet.write(sheet_iterator, 1, sku)
|
1560 |
sheet.write(sheet_iterator, 1, sku)
|
| 1451 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
1561 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
| 1452 |
sheet.write(sheet_iterator, 3, loc)
|
1562 |
sheet.write(sheet_iterator, 3, loc)
|
| 1453 |
sheet.write(sheet_iterator, 4, item.brand)
|
1563 |
sheet.write(sheet_iterator, 4, item.brand)
|
| - |
|
1564 |
sheet.write(sheet_iterator, 5, getCategory(item))
|
| 1454 |
sheet.write(sheet_iterator, 5, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
1565 |
sheet.write(sheet_iterator, 6, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
| 1455 |
sheet.write(sheet_iterator, 6, item.weight)
|
1566 |
sheet.write(sheet_iterator, 7, item.weight)
|
| 1456 |
sheet.write(sheet_iterator, 7, amScraping.courierCost)
|
1567 |
sheet.write(sheet_iterator, 8, amScraping.courierCost)
|
| 1457 |
sheet.write(sheet_iterator, 8, amScraping.ourSellingPrice)
|
1568 |
sheet.write(sheet_iterator, 9, amScraping.ourSellingPrice)
|
| 1458 |
sheet.write(sheet_iterator, 9, amScraping.promoPrice)
|
1569 |
sheet.write(sheet_iterator, 10, amScraping.promoPrice)
|
| 1459 |
if amScraping.isPromotion:
|
1570 |
if amScraping.isPromotion:
|
| 1460 |
sheet.write(sheet_iterator, 10, "Yes")
|
1571 |
sheet.write(sheet_iterator, 11, "Yes")
|
| 1461 |
else:
|
1572 |
else:
|
| 1462 |
sheet.write(sheet_iterator, 10, "No")
|
1573 |
sheet.write(sheet_iterator, 11, "No")
|
| 1463 |
sheet.write(sheet_iterator, 11, amScraping.lowestPossibleSp)
|
1574 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
| 1464 |
if amScraping.ourRank > 3:
|
1575 |
if amScraping.ourRank > 3:
|
| 1465 |
sheet.write(sheet_iterator, 12, 'Greater than 3')
|
1576 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
| 1466 |
else:
|
1577 |
else:
|
| 1467 |
sheet.write(sheet_iterator, 12, amScraping.ourRank)
|
1578 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
| 1468 |
sheet.write(sheet_iterator, 13, amScraping.ourInventory)
|
1579 |
sheet.write(sheet_iterator, 14, amScraping.ourInventory)
|
| 1469 |
sheet.write(sheet_iterator, 14, amScraping.lowestSellerSp)
|
1580 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
|
| 1470 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerRating)
|
1581 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerRating)
|
| 1471 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerShippingTime)
|
1582 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerShippingTime)
|
| 1472 |
sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
|
1583 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerSp)
|
| 1473 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerRating)
|
1584 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerRating)
|
| 1474 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerShippingTime)
|
1585 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerShippingTime)
|
| 1475 |
sheet.write(sheet_iterator, 20, amScraping.thirdLowestSellerSp)
|
1586 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerSp)
|
| 1476 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerRating)
|
1587 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerRating)
|
| 1477 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
|
1588 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerShippingTime)
|
| 1478 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
1589 |
sheet.write(sheet_iterator, 24, amScraping.otherCost)
|
| 1479 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
1590 |
sheet.write(sheet_iterator, 25, amScraping.wanlc)
|
| - |
|
1591 |
sheet.write(sheet_iterator, 26, amScraping.subsidy)
|
| 1480 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
1592 |
sheet.write(sheet_iterator, 27, amScraping.commission)
|
| 1481 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
1593 |
sheet.write(sheet_iterator, 28, amScraping.competitorCommission)
|
| 1482 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
1594 |
sheet.write(sheet_iterator, 29, amScraping.returnProvision)
|
| 1483 |
sheet.write(sheet_iterator, 28, amScraping.vatRate)
|
1595 |
sheet.write(sheet_iterator, 30, amScraping.vatRate)
|
| 1484 |
sheet.write(sheet_iterator, 29, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1596 |
sheet.write(sheet_iterator, 31, getMargin(amScraping))
|
| 1485 |
sheet.write(sheet_iterator, 30, item.risky)
|
1597 |
sheet.write(sheet_iterator, 32, item.risky)
|
| 1486 |
sheet.write(sheet_iterator, 31, amScraping.proposedSp)
|
1598 |
sheet.write(sheet_iterator, 33, amScraping.proposedSp)
|
| 1487 |
sheet.write(sheet_iterator, 32, amScraping.avgSale)
|
1599 |
sheet.write(sheet_iterator, 34, amScraping.avgSale)
|
| 1488 |
sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
|
1600 |
sheet.write(sheet_iterator, 35, getOosString(saleMap.get(sku)))
|
| 1489 |
if amScraping.decision is None:
|
1601 |
if amScraping.decision is None:
|
| 1490 |
sheet.write(sheet_iterator, 34, 'Auto Pricing Inactive')
|
1602 |
sheet.write(sheet_iterator, 36, 'Auto Pricing Inactive')
|
| 1491 |
sheet_iterator+=1
|
1603 |
sheet_iterator+=1
|
| 1492 |
continue
|
1604 |
continue
|
| 1493 |
sheet.write(sheet_iterator, 34, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
1605 |
sheet.write(sheet_iterator, 36, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
| 1494 |
sheet.write(sheet_iterator, 35, amScraping.reason)
|
1606 |
sheet.write(sheet_iterator, 37, amScraping.reason)
|
| 1495 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
1607 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 1496 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.proposedSp))
|
1608 |
sheet.write(sheet_iterator, 38, math.ceil(amScraping.proposedSp))
|
| 1497 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
1609 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 1498 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
1610 |
sheet.write(sheet_iterator, 38, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
| 1499 |
sheet_iterator+=1
|
1611 |
sheet_iterator+=1
|
| 1500 |
|
1612 |
|
| 1501 |
sheet = wbk.add_sheet('Cheapest')
|
1613 |
sheet = wbk.add_sheet('Cheapest')
|
| 1502 |
xstr = lambda s: s or ""
|
1614 |
xstr = lambda s: s or ""
|
| 1503 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1615 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1504 |
|
1616 |
|
| 1505 |
excel_integer_format = '0'
|
1617 |
excel_integer_format = '0'
|
| 1506 |
integer_style = xlwt.XFStyle()
|
1618 |
integer_style = xlwt.XFStyle()
|
| 1507 |
integer_style.num_format_str = excel_integer_format
|
1619 |
integer_style.num_format_str = excel_integer_format
|
| - |
|
1620 |
writeheaders(sheet,heading_xf)
|
| 1508 |
|
1621 |
'''
|
| 1509 |
sheet.write(0, 0, "Item Id", heading_xf)
|
1622 |
sheet.write(0, 0, "Item Id", heading_xf)
|
| 1510 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
1623 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
| 1511 |
sheet.write(0, 2, "Asin", heading_xf)
|
1624 |
sheet.write(0, 2, "Asin", heading_xf)
|
| 1512 |
sheet.write(0, 3, "Location", heading_xf)
|
1625 |
sheet.write(0, 3, "Location", heading_xf)
|
| 1513 |
sheet.write(0, 4, "Brand", heading_xf)
|
1626 |
sheet.write(0, 4, "Brand", heading_xf)
|
| Line 1538... |
Line 1651... |
| 1538 |
sheet.write(0, 29, "Margin", heading_xf)
|
1651 |
sheet.write(0, 29, "Margin", heading_xf)
|
| 1539 |
sheet.write(0, 30, "Risky", heading_xf)
|
1652 |
sheet.write(0, 30, "Risky", heading_xf)
|
| 1540 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
1653 |
sheet.write(0, 31, "Proposed Sp", heading_xf)
|
| 1541 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
1654 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
| 1542 |
sheet.write(0, 33, "Sales History", heading_xf)
|
1655 |
sheet.write(0, 33, "Sales History", heading_xf)
|
| - |
|
1656 |
'''
|
| 1543 |
sheet.write(0, 34, "Decision", heading_xf)
|
1657 |
sheet.write(0, 36, "Decision", heading_xf)
|
| 1544 |
sheet.write(0, 35, "Reason", heading_xf)
|
1658 |
sheet.write(0, 37, "Reason", heading_xf)
|
| 1545 |
sheet.write(0, 36, "Updated Price", heading_xf)
|
1659 |
sheet.write(0, 38, "Updated Price", heading_xf)
|
| 1546 |
|
- |
|
| 1547 |
sheet_iterator = 1
|
1660 |
sheet_iterator = 1
|
| 1548 |
cheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1661 |
cheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1549 |
for cheapestItem in cheapestItems:
|
1662 |
for cheapestItem in cheapestItems:
|
| 1550 |
amScraping = cheapestItem[0]
|
1663 |
amScraping = cheapestItem[0]
|
| 1551 |
item = cheapestItem[1]
|
1664 |
item = cheapestItem[1]
|
| Line 1558... |
Line 1671... |
| 1558 |
loc = 'BANGLORE'
|
1671 |
loc = 'BANGLORE'
|
| 1559 |
sheet.write(sheet_iterator, 1, sku)
|
1672 |
sheet.write(sheet_iterator, 1, sku)
|
| 1560 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
1673 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
| 1561 |
sheet.write(sheet_iterator, 3, loc)
|
1674 |
sheet.write(sheet_iterator, 3, loc)
|
| 1562 |
sheet.write(sheet_iterator, 4, item.brand)
|
1675 |
sheet.write(sheet_iterator, 4, item.brand)
|
| - |
|
1676 |
sheet.write(sheet_iterator, 5, getCategory(item))
|
| 1563 |
sheet.write(sheet_iterator, 5, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
1677 |
sheet.write(sheet_iterator, 6, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
| 1564 |
sheet.write(sheet_iterator, 6, item.weight)
|
1678 |
sheet.write(sheet_iterator, 7, item.weight)
|
| 1565 |
sheet.write(sheet_iterator, 7, amScraping.courierCost)
|
1679 |
sheet.write(sheet_iterator, 8, amScraping.courierCost)
|
| 1566 |
sheet.write(sheet_iterator, 8, amScraping.ourSellingPrice)
|
1680 |
sheet.write(sheet_iterator, 9, amScraping.ourSellingPrice)
|
| 1567 |
sheet.write(sheet_iterator, 9, amScraping.promoPrice)
|
1681 |
sheet.write(sheet_iterator, 10, amScraping.promoPrice)
|
| 1568 |
if amScraping.isPromotion:
|
1682 |
if amScraping.isPromotion:
|
| 1569 |
sheet.write(sheet_iterator, 10, "Yes")
|
1683 |
sheet.write(sheet_iterator, 11, "Yes")
|
| 1570 |
else:
|
1684 |
else:
|
| 1571 |
sheet.write(sheet_iterator, 10, "No")
|
1685 |
sheet.write(sheet_iterator, 11, "No")
|
| 1572 |
sheet.write(sheet_iterator, 11, amScraping.lowestPossibleSp)
|
1686 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
| 1573 |
if amScraping.ourRank > 3:
|
1687 |
if amScraping.ourRank > 3:
|
| 1574 |
sheet.write(sheet_iterator, 12, 'Greater than 3')
|
1688 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
| 1575 |
else:
|
1689 |
else:
|
| 1576 |
sheet.write(sheet_iterator, 12, amScraping.ourRank)
|
1690 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
| 1577 |
sheet.write(sheet_iterator, 13, amScraping.ourInventory)
|
1691 |
sheet.write(sheet_iterator, 14, amScraping.ourInventory)
|
| 1578 |
sheet.write(sheet_iterator, 14, amScraping.lowestSellerSp)
|
1692 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
|
| 1579 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerRating)
|
1693 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerRating)
|
| 1580 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerShippingTime)
|
1694 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerShippingTime)
|
| 1581 |
sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
|
1695 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerSp)
|
| 1582 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerRating)
|
1696 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerRating)
|
| 1583 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerShippingTime)
|
1697 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerShippingTime)
|
| 1584 |
sheet.write(sheet_iterator, 20, amScraping.thirdLowestSellerSp)
|
1698 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerSp)
|
| 1585 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerRating)
|
1699 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerRating)
|
| 1586 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
|
1700 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerShippingTime)
|
| 1587 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
1701 |
sheet.write(sheet_iterator, 24, amScraping.otherCost)
|
| 1588 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
1702 |
sheet.write(sheet_iterator, 25, amScraping.wanlc)
|
| - |
|
1703 |
sheet.write(sheet_iterator, 26, amScraping.subsidy)
|
| 1589 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
1704 |
sheet.write(sheet_iterator, 27, amScraping.commission)
|
| 1590 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
1705 |
sheet.write(sheet_iterator, 28, amScraping.competitorCommission)
|
| 1591 |
sheet.write(sheet_iterator, 27, amScraping.vatRate)
|
1706 |
sheet.write(sheet_iterator, 29, amScraping.vatRate)
|
| 1592 |
sheet.write(sheet_iterator, 28, amScraping.returnProvision)
|
1707 |
sheet.write(sheet_iterator, 30, amScraping.returnProvision)
|
| 1593 |
sheet.write(sheet_iterator, 29, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1708 |
sheet.write(sheet_iterator, 31, getMargin(amScraping))
|
| 1594 |
sheet.write(sheet_iterator, 30, item.risky)
|
1709 |
sheet.write(sheet_iterator, 32, item.risky)
|
| 1595 |
sheet.write(sheet_iterator, 31, amScraping.proposedSp)
|
1710 |
sheet.write(sheet_iterator, 33, amScraping.proposedSp)
|
| 1596 |
sheet.write(sheet_iterator, 32, amScraping.avgSale)
|
1711 |
sheet.write(sheet_iterator, 34, amScraping.avgSale)
|
| 1597 |
sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
|
1712 |
sheet.write(sheet_iterator, 35, getOosString(saleMap.get(sku)))
|
| 1598 |
if amScraping.decision is None:
|
1713 |
if amScraping.decision is None:
|
| 1599 |
sheet.write(sheet_iterator, 34, 'Auto Pricing Inactive')
|
1714 |
sheet.write(sheet_iterator, 36, 'Auto Pricing Inactive')
|
| 1600 |
sheet_iterator+=1
|
1715 |
sheet_iterator+=1
|
| 1601 |
continue
|
1716 |
continue
|
| 1602 |
sheet.write(sheet_iterator, 34, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
1717 |
sheet.write(sheet_iterator, 36, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
| 1603 |
sheet.write(sheet_iterator, 35, amScraping.reason)
|
1718 |
sheet.write(sheet_iterator, 37, amScraping.reason)
|
| 1604 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
1719 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 1605 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.proposedSp))
|
1720 |
sheet.write(sheet_iterator, 38, math.ceil(amScraping.proposedSp))
|
| 1606 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
1721 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 1607 |
sheet.write(sheet_iterator, 36, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
1722 |
sheet.write(sheet_iterator, 38, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
| 1608 |
sheet_iterator+=1
|
1723 |
sheet_iterator+=1
|
| 1609 |
|
1724 |
|
| 1610 |
sheet = wbk.add_sheet('Negative Margin')
|
1725 |
sheet = wbk.add_sheet('Negative Margin')
|
| 1611 |
xstr = lambda s: s or ""
|
1726 |
xstr = lambda s: s or ""
|
| 1612 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1727 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1613 |
|
1728 |
|
| 1614 |
excel_integer_format = '0'
|
1729 |
excel_integer_format = '0'
|
| 1615 |
integer_style = xlwt.XFStyle()
|
1730 |
integer_style = xlwt.XFStyle()
|
| 1616 |
integer_style.num_format_str = excel_integer_format
|
1731 |
integer_style.num_format_str = excel_integer_format
|
| 1617 |
|
- |
|
| 1618 |
sheet.write(0, 0, "Item Id", heading_xf)
|
1732 |
sheet.write(0, 0, "Item Id", heading_xf)
|
| 1619 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
1733 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
| 1620 |
sheet.write(0, 2, "Asin", heading_xf)
|
1734 |
sheet.write(0, 2, "Asin", heading_xf)
|
| 1621 |
sheet.write(0, 3, "Location", heading_xf)
|
1735 |
sheet.write(0, 3, "Location", heading_xf)
|
| 1622 |
sheet.write(0, 4, "Brand", heading_xf)
|
1736 |
sheet.write(0, 4, "Brand", heading_xf)
|
| - |
|
1737 |
sheet.write(0, 5, "Category", heading_xf)
|
| 1623 |
sheet.write(0, 5, "Product Name", heading_xf)
|
1738 |
sheet.write(0, 6, "Product Name", heading_xf)
|
| 1624 |
sheet.write(0, 6, "Weight", heading_xf)
|
1739 |
sheet.write(0, 7, "Weight", heading_xf)
|
| 1625 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
1740 |
sheet.write(0, 8, "Courier Cost", heading_xf)
|
| 1626 |
sheet.write(0, 8, "Our SP", heading_xf)
|
1741 |
sheet.write(0, 9, "Our SP", heading_xf)
|
| 1627 |
sheet.write(0, 9, "Promo Price", heading_xf)
|
1742 |
sheet.write(0, 10, "Promo Price", heading_xf)
|
| 1628 |
sheet.write(0, 10, "Is Promotion", heading_xf)
|
1743 |
sheet.write(0, 11, "Is Promotion", heading_xf)
|
| 1629 |
sheet.write(0, 11, "Lowest Possible SP", heading_xf)
|
1744 |
sheet.write(0, 12, "Lowest Possible SP", heading_xf)
|
| 1630 |
sheet.write(0, 12, "Rank", heading_xf)
|
1745 |
sheet.write(0, 13, "Rank", heading_xf)
|
| 1631 |
sheet.write(0, 13, "Our Inventory", heading_xf)
|
1746 |
sheet.write(0, 14, "Our Inventory", heading_xf)
|
| 1632 |
sheet.write(0, 14, "Lowest Seller SP", heading_xf)
|
1747 |
sheet.write(0, 15, "Lowest Seller SP", heading_xf)
|
| 1633 |
sheet.write(0, 15, "Lowest Seller Rating", heading_xf)
|
1748 |
sheet.write(0, 16, "Lowest Seller Rating", heading_xf)
|
| 1634 |
sheet.write(0, 16, "Lowest Seller Shipping Time", heading_xf)
|
1749 |
sheet.write(0, 17, "Lowest Seller Shipping Time", heading_xf)
|
| 1635 |
sheet.write(0, 17, "Second Lowest Seller SP", heading_xf)
|
1750 |
sheet.write(0, 18, "Second Lowest Seller SP", heading_xf)
|
| 1636 |
sheet.write(0, 18, "Second Lowest Seller Rating", heading_xf)
|
1751 |
sheet.write(0, 19, "Second Lowest Seller Rating", heading_xf)
|
| 1637 |
sheet.write(0, 19, "Second Lowest Seller Shipping Time", heading_xf)
|
1752 |
sheet.write(0, 20, "Second Lowest Seller Shipping Time", heading_xf)
|
| 1638 |
sheet.write(0, 20, "Third Lowest Seller SP", heading_xf)
|
1753 |
sheet.write(0, 21, "Third Lowest Seller SP", heading_xf)
|
| 1639 |
sheet.write(0, 21, "Third Lowest Seller Rating", heading_xf)
|
1754 |
sheet.write(0, 22, "Third Lowest Seller Rating", heading_xf)
|
| 1640 |
sheet.write(0, 22, "Third Lowest Seller Shipping Time", heading_xf)
|
1755 |
sheet.write(0, 23, "Third Lowest Seller Shipping Time", heading_xf)
|
| 1641 |
sheet.write(0, 23, "Other Cost", heading_xf)
|
1756 |
sheet.write(0, 24, "Other Cost", heading_xf)
|
| 1642 |
sheet.write(0, 24, "WANLC", heading_xf)
|
1757 |
sheet.write(0, 25, "WANLC", heading_xf)
|
| - |
|
1758 |
sheet.write(0, 26, "Subsidy", heading_xf)
|
| 1643 |
sheet.write(0, 25, "Commission", heading_xf)
|
1759 |
sheet.write(0, 27, "Commission", heading_xf)
|
| 1644 |
sheet.write(0, 26, "Competitor Commission", heading_xf)
|
1760 |
sheet.write(0, 28, "Competitor Commission", heading_xf)
|
| 1645 |
sheet.write(0, 27, "Return Provision", heading_xf)
|
1761 |
sheet.write(0, 29, "Return Provision", heading_xf)
|
| 1646 |
sheet.write(0, 28, "Vat Rate", heading_xf)
|
1762 |
sheet.write(0, 30, "Vat Rate", heading_xf)
|
| 1647 |
sheet.write(0, 29, "Margin", heading_xf)
|
1763 |
sheet.write(0, 31, "Margin", heading_xf)
|
| 1648 |
sheet.write(0, 30, "Avg Sale", heading_xf)
|
1764 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
| 1649 |
sheet.write(0, 31, "Sales History", heading_xf)
|
1765 |
sheet.write(0, 33, "Sales History", heading_xf)
|
| 1650 |
|
1766 |
|
| 1651 |
sheet_iterator = 1
|
1767 |
sheet_iterator = 1
|
| 1652 |
amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1768 |
amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1653 |
for amongCheapestItem in amongCheapestItems:
|
1769 |
for amongCheapestItem in amongCheapestItems:
|
| 1654 |
amScraping = amongCheapestItem[0]
|
1770 |
amScraping = amongCheapestItem[0]
|
| Line 1662... |
Line 1778... |
| 1662 |
loc = 'BANGLORE'
|
1778 |
loc = 'BANGLORE'
|
| 1663 |
sheet.write(sheet_iterator, 1, sku)
|
1779 |
sheet.write(sheet_iterator, 1, sku)
|
| 1664 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
1780 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
| 1665 |
sheet.write(sheet_iterator, 3, loc)
|
1781 |
sheet.write(sheet_iterator, 3, loc)
|
| 1666 |
sheet.write(sheet_iterator, 4, item.brand)
|
1782 |
sheet.write(sheet_iterator, 4, item.brand)
|
| - |
|
1783 |
sheet.write(sheet_iterator, 5, getCategory(item))
|
| 1667 |
sheet.write(sheet_iterator, 5, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
1784 |
sheet.write(sheet_iterator, 6, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
| 1668 |
sheet.write(sheet_iterator, 6, item.weight)
|
1785 |
sheet.write(sheet_iterator, 7, item.weight)
|
| 1669 |
sheet.write(sheet_iterator, 7, amScraping.courierCost)
|
1786 |
sheet.write(sheet_iterator, 8, amScraping.courierCost)
|
| 1670 |
sheet.write(sheet_iterator, 8, amScraping.ourSellingPrice)
|
1787 |
sheet.write(sheet_iterator, 9, amScraping.ourSellingPrice)
|
| 1671 |
sheet.write(sheet_iterator, 9, amScraping.promoPrice)
|
1788 |
sheet.write(sheet_iterator, 10, amScraping.promoPrice)
|
| 1672 |
if amScraping.isPromotion:
|
1789 |
if amScraping.isPromotion:
|
| 1673 |
sheet.write(sheet_iterator, 10, "Yes")
|
1790 |
sheet.write(sheet_iterator, 11, "Yes")
|
| 1674 |
else:
|
1791 |
else:
|
| 1675 |
sheet.write(sheet_iterator, 10, "No")
|
1792 |
sheet.write(sheet_iterator, 11, "No")
|
| 1676 |
sheet.write(sheet_iterator, 11, amScraping.lowestPossibleSp)
|
1793 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
| 1677 |
if amScraping.ourRank > 3:
|
1794 |
if amScraping.ourRank > 3:
|
| 1678 |
sheet.write(sheet_iterator, 12, 'Greater than 3')
|
1795 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
| 1679 |
else:
|
1796 |
else:
|
| 1680 |
sheet.write(sheet_iterator, 12, amScraping.ourRank)
|
1797 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
| 1681 |
sheet.write(sheet_iterator, 13, amScraping.ourInventory)
|
1798 |
sheet.write(sheet_iterator, 14, amScraping.ourInventory)
|
| 1682 |
sheet.write(sheet_iterator, 14, amScraping.lowestSellerSp)
|
1799 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
|
| 1683 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerRating)
|
1800 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerRating)
|
| 1684 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerShippingTime)
|
1801 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerShippingTime)
|
| 1685 |
sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
|
1802 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerSp)
|
| 1686 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerRating)
|
1803 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerRating)
|
| 1687 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerShippingTime)
|
1804 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerShippingTime)
|
| 1688 |
sheet.write(sheet_iterator, 20, amScraping.thirdLowestSellerSp)
|
1805 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerSp)
|
| 1689 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerRating)
|
1806 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerRating)
|
| 1690 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
|
1807 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerShippingTime)
|
| 1691 |
sheet.write(sheet_iterator, 23, amScraping.otherCost)
|
1808 |
sheet.write(sheet_iterator, 24, amScraping.otherCost)
|
| 1692 |
sheet.write(sheet_iterator, 24, amScraping.wanlc)
|
1809 |
sheet.write(sheet_iterator, 25, amScraping.wanlc)
|
| - |
|
1810 |
sheet.write(sheet_iterator, 26, amScraping.subsidy)
|
| 1693 |
sheet.write(sheet_iterator, 25, amScraping.commission)
|
1811 |
sheet.write(sheet_iterator, 27, amScraping.commission)
|
| 1694 |
sheet.write(sheet_iterator, 26, amScraping.competitorCommission)
|
1812 |
sheet.write(sheet_iterator, 28, amScraping.competitorCommission)
|
| 1695 |
sheet.write(sheet_iterator, 27, amScraping.returnProvision)
|
1813 |
sheet.write(sheet_iterator, 29, amScraping.returnProvision)
|
| 1696 |
sheet.write(sheet_iterator, 28, amScraping.vatRate)
|
1814 |
sheet.write(sheet_iterator, 30, amScraping.vatRate)
|
| 1697 |
sheet.write(sheet_iterator, 29, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1815 |
sheet.write(sheet_iterator, 31, getMargin(amScraping))
|
| 1698 |
sheet.write(sheet_iterator, 30, amScraping.avgSale)
|
1816 |
sheet.write(sheet_iterator, 32, amScraping.avgSale)
|
| 1699 |
sheet.write(sheet_iterator, 31, getOosString(saleMap.get(sku)))
|
1817 |
sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
|
| 1700 |
sheet_iterator+=1
|
1818 |
sheet_iterator+=1
|
| 1701 |
|
1819 |
|
| 1702 |
sheet = wbk.add_sheet('Exception List')
|
1820 |
sheet = wbk.add_sheet('Exception List')
|
| 1703 |
xstr = lambda s: s or ""
|
1821 |
xstr = lambda s: s or ""
|
| 1704 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1822 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 1710... |
Line 1828... |
| 1710 |
sheet.write(0, 0, "Item Id", heading_xf)
|
1828 |
sheet.write(0, 0, "Item Id", heading_xf)
|
| 1711 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
1829 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
| 1712 |
sheet.write(0, 2, "Asin", heading_xf)
|
1830 |
sheet.write(0, 2, "Asin", heading_xf)
|
| 1713 |
sheet.write(0, 3, "Location", heading_xf)
|
1831 |
sheet.write(0, 3, "Location", heading_xf)
|
| 1714 |
sheet.write(0, 4, "Brand", heading_xf)
|
1832 |
sheet.write(0, 4, "Brand", heading_xf)
|
| - |
|
1833 |
sheet.write(0, 5, "Category", heading_xf)
|
| 1715 |
sheet.write(0, 5, "Product Name", heading_xf)
|
1834 |
sheet.write(0, 6, "Product Name", heading_xf)
|
| 1716 |
sheet.write(0, 6, "Reason", heading_xf)
|
1835 |
sheet.write(0, 7, "Reason", heading_xf)
|
| 1717 |
|
1836 |
|
| 1718 |
sheet_iterator = 1
|
1837 |
sheet_iterator = 1
|
| 1719 |
amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1838 |
amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1720 |
for amongCheapestItem in amongCheapestItems:
|
1839 |
for amongCheapestItem in amongCheapestItems:
|
| 1721 |
amScraping = amongCheapestItem[0]
|
1840 |
amScraping = amongCheapestItem[0]
|
| Line 1729... |
Line 1848... |
| 1729 |
loc = 'BANGLORE'
|
1848 |
loc = 'BANGLORE'
|
| 1730 |
sheet.write(sheet_iterator, 1, sku)
|
1849 |
sheet.write(sheet_iterator, 1, sku)
|
| 1731 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
1850 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
| 1732 |
sheet.write(sheet_iterator, 3, loc)
|
1851 |
sheet.write(sheet_iterator, 3, loc)
|
| 1733 |
sheet.write(sheet_iterator, 4, item.brand)
|
1852 |
sheet.write(sheet_iterator, 4, item.brand)
|
| - |
|
1853 |
sheet.write(sheet_iterator, 5, getCategory(item))
|
| 1734 |
sheet.write(sheet_iterator, 5, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
1854 |
sheet.write(sheet_iterator, 6, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
| 1735 |
sheet.write(sheet_iterator, 6, amScraping.reason)
|
1855 |
sheet.write(sheet_iterator, 7, amScraping.reason)
|
| 1736 |
sheet_iterator+=1
|
1856 |
sheet_iterator+=1
|
| 1737 |
|
1857 |
|
| 1738 |
|
1858 |
|
| 1739 |
if (runType=='FULL'):
|
1859 |
if (runType=='FULL'):
|
| 1740 |
sheet = wbk.add_sheet('Auto Favorites')
|
1860 |
sheet = wbk.add_sheet('Auto Favorites')
|
| Line 1772... |
Line 1892... |
| 1772 |
sheet_iterator+=1
|
1892 |
sheet_iterator+=1
|
| 1773 |
|
1893 |
|
| 1774 |
filename = "/tmp/amazon-report-"+runType+" " + str(timestamp) + ".xls"
|
1894 |
filename = "/tmp/amazon-report-"+runType+" " + str(timestamp) + ".xls"
|
| 1775 |
wbk.save(filename)
|
1895 |
wbk.save(filename)
|
| 1776 |
try:
|
1896 |
try:
|
| 1777 |
#EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Amazon Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
|
1897 |
#EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["anikendra.das@saholic.com"], " Amazon Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
|
| 1778 |
EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["chandan.kumar@saholic.com","manoj.kumar@saholic.com","yukti.jain@saholic.com","ankush.dhingra@saholic.com","manoj.pal@saholic.com"], " Amazon Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], ["rajneesh.arora@saholic.com","anikendra.das@saholic.com","vikram.raghav@saholic.com","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
|
1898 |
EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["chandan.kumar@saholic.com","manoj.kumar@saholic.com","yukti.jain@saholic.com","ankush.dhingra@saholic.com","manoj.pal@saholic.com"], " Amazon Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], ["rajneesh.arora@saholic.com","anikendra.das@saholic.com","vikram.raghav@saholic.com","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
|
| 1779 |
except Exception as e:
|
1899 |
except Exception as e:
|
| 1780 |
print e
|
1900 |
print e
|
| 1781 |
print "Unable to send report.Trying with local SMTP"
|
1901 |
print "Unable to send report.Trying with local SMTP"
|
| 1782 |
smtpServer = smtplib.SMTP('localhost')
|
1902 |
smtpServer = smtplib.SMTP('localhost')
|
| 1783 |
smtpServer.set_debuglevel(1)
|
1903 |
smtpServer.set_debuglevel(1)
|
| 1784 |
sender = 'build@shop2020.in'
|
1904 |
sender = 'build@shop2020.in'
|
| 1785 |
recipients = ["kshitij.sood@saholic.com"]
|
1905 |
#recipients = ["anikendra.das@saholic.com"]
|
| 1786 |
msg = MIMEMultipart()
|
1906 |
msg = MIMEMultipart()
|
| 1787 |
msg['Subject'] = "Amazon Auto Pricing" + ' '+runType+' - ' + str(datetime.now())
|
1907 |
msg['Subject'] = "Amazon Auto Pricing" + ' '+runType+' - ' + str(datetime.now())
|
| 1788 |
msg['From'] = sender
|
1908 |
msg['From'] = sender
|
| 1789 |
#recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','vikram.raghav@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
|
1909 |
recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','vikram.raghav@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
|
| 1790 |
msg['To'] = ",".join(recipients)
|
1910 |
msg['To'] = ",".join(recipients)
|
| 1791 |
fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
|
1911 |
fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
|
| 1792 |
fileMsg.set_payload(file(filename).read())
|
1912 |
fileMsg.set_payload(file(filename).read())
|
| 1793 |
email.encoders.encode_base64(fileMsg)
|
1913 |
email.encoders.encode_base64(fileMsg)
|
| 1794 |
fileMsg.add_header('Content-Disposition','attachment;filename=amazon-auto-pricing.xls')
|
1914 |
fileMsg.add_header('Content-Disposition','attachment;filename=amazon-auto-pricing.xls')
|
| Line 1950... |
Line 2070... |
| 1950 |
smtpServer.sendmail(sender, recipients, msg.as_string())
|
2070 |
smtpServer.sendmail(sender, recipients, msg.as_string())
|
| 1951 |
print "Successfully sent email"
|
2071 |
print "Successfully sent email"
|
| 1952 |
except:
|
2072 |
except:
|
| 1953 |
print "Error: unable to send email."
|
2073 |
print "Error: unable to send email."
|
| 1954 |
|
2074 |
|
| - |
|
2075 |
def generateCategoryMap():
|
| - |
|
2076 |
global categoryMap
|
| - |
|
2077 |
result = session.query(Category.id,Category.display_name).all()
|
| - |
|
2078 |
for cat in result:
|
| - |
|
2079 |
categoryMap[cat.id] = cat.display_name
|
| - |
|
2080 |
print categoryMap
|
| 1955 |
|
2081 |
|
| 1956 |
def main():
|
2082 |
def main():
|
| 1957 |
parser = optparse.OptionParser()
|
2083 |
parser = optparse.OptionParser()
|
| 1958 |
parser.add_option("-t", "--type", dest="runType",
|
2084 |
parser.add_option("-t", "--type", dest="runType",
|
| 1959 |
default="FULL", type="string",
|
2085 |
default="FULL", type="string",
|
| 1960 |
help="Run type FULL or FAVOURITE")
|
2086 |
help="Run type FULL or FAVOURITE")
|
| 1961 |
(options, args) = parser.parse_args()
|
2087 |
(options, args) = parser.parse_args()
|
| 1962 |
if options.runType not in ('FULL','FAVOURITE'):
|
2088 |
if options.runType not in ('FULL','FAVOURITE'):
|
| 1963 |
print "Run type argument illegal."
|
2089 |
print "Run type argument illegal."
|
| 1964 |
sys.exit(1)
|
2090 |
sys.exit(1)
|
| 1965 |
time.sleep(5)
|
2091 |
time.sleep(5)
|
| 1966 |
timestamp = datetime.now()
|
2092 |
timestamp = datetime.now()
|
| - |
|
2093 |
generateCategoryMap()
|
| 1967 |
fetchFbaSale()
|
2094 |
fetchFbaSale()
|
| 1968 |
itemInfo = populateStuff(timestamp,options.runType)
|
2095 |
itemInfo = populateStuff(timestamp,options.runType)
|
| 1969 |
itemsToPopulate = 0
|
2096 |
itemsToPopulate = 0
|
| 1970 |
toSync = 0
|
2097 |
toSync = 0
|
| 1971 |
lenItems = len(itemInfo)
|
2098 |
lenItems = len(itemInfo)
|
| Line 2000... |
Line 2127... |
| 2000 |
previousAutoFav, nowAutoFav = markAutoFavourites(timestamp)
|
2127 |
previousAutoFav, nowAutoFav = markAutoFavourites(timestamp)
|
| 2001 |
writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,options.runType)
|
2128 |
writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,options.runType)
|
| 2002 |
print "send auto pricing email"
|
2129 |
print "send auto pricing email"
|
| 2003 |
sendAutoPricingMail(autoDecreaseItems,autoIncreaseItems)
|
2130 |
sendAutoPricingMail(autoDecreaseItems,autoIncreaseItems)
|
| 2004 |
if __name__=='__main__':
|
2131 |
if __name__=='__main__':
|
| 2005 |
main()
|
- |
|
| 2006 |
|
2132 |
main()
|
| - |
|
2133 |
|