| Line 505... |
Line 505... |
| 505 |
temp.append("WANLC is 0")
|
505 |
temp.append("WANLC is 0")
|
| 506 |
elif ourPricingForSku.get(val.sku) is None or ourPricingForSku.get(val.sku).keys()==0:
|
506 |
elif ourPricingForSku.get(val.sku) is None or ourPricingForSku.get(val.sku).keys()==0:
|
| 507 |
print "Unable to fetch our price"
|
507 |
print "Unable to fetch our price"
|
| 508 |
temp.append("Unable to fetch our price")
|
508 |
temp.append("Unable to fetch our price")
|
| 509 |
else:
|
509 |
else:
|
| 510 |
print "Unable to fetch competive pricing"
|
510 |
print "No other seller or Unable to fetch competitive pricing"
|
| 511 |
temp.append("Unable to fetch competitive pricing")
|
511 |
temp.append("No other seller or Unable to fetch competitive pricing")
|
| 512 |
exceptionList.append(temp)
|
512 |
exceptionList.append(temp)
|
| 513 |
continue
|
513 |
continue
|
| 514 |
val.ourSp = ourPricingForSku.get(val.sku).get('sellingPrice')
|
514 |
val.ourSp = ourPricingForSku.get(val.sku).get('sellingPrice')
|
| 515 |
val.promoPrice = ourPricingForSku.get(val.sku).get('promoPrice')
|
515 |
val.promoPrice = ourPricingForSku.get(val.sku).get('promoPrice')
|
| 516 |
val.isPromo = ourPricingForSku.get(val.sku).get('promotion')
|
516 |
val.isPromo = ourPricingForSku.get(val.sku).get('promotion')
|
| Line 567... |
Line 567... |
| 567 |
competitvePricingInfo['lowestMfnIgnored'],competitvePricingInfo['lowestMfn'],competitvePricingInfo['lowestFba'],competitvePricingInfo['isLowestMfnIgnored'],competitvePricingInfo['isLowestMfn'],competitvePricingInfo['isLowestFba'],None)
|
567 |
competitvePricingInfo['lowestMfnIgnored'],competitvePricingInfo['lowestMfn'],competitvePricingInfo['lowestFba'],competitvePricingInfo['isLowestMfnIgnored'],competitvePricingInfo['isLowestMfn'],competitvePricingInfo['isLowestFba'],None)
|
| 568 |
|
568 |
|
| 569 |
competitivePrice = decideCompetitvePricing(amDetails,val.ourInventory,timestamp)
|
569 |
competitivePrice = decideCompetitvePricing(amDetails,val.ourInventory,timestamp)
|
| 570 |
amDetails.competitivePrice = competitivePrice
|
570 |
amDetails.competitivePrice = competitivePrice
|
| 571 |
if amDetails.competitivePrice==0.0 and amDetails.ourRank > 1:
|
571 |
if amDetails.competitivePrice==0.0 and amDetails.ourRank > 1:
|
| 572 |
print "vat exception"
|
- |
|
| 573 |
temp = []
|
572 |
temp = []
|
| 574 |
temp.append(val)
|
573 |
temp.append(val)
|
| 575 |
temp.append("Unable to calculate competitive pricing")
|
574 |
temp.append("Unable to calculate competitive pricing")
|
| 576 |
exceptionList.append(temp)
|
575 |
exceptionList.append(temp)
|
| 577 |
continue
|
576 |
continue
|
| Line 760... |
Line 759... |
| 760 |
reason = exceptionItem[1]
|
759 |
reason = exceptionItem[1]
|
| 761 |
amazonScrapingHistory = AmazonScrapingHistory()
|
760 |
amazonScrapingHistory = AmazonScrapingHistory()
|
| 762 |
amazonScrapingHistory.item_id = val.sku[3:]
|
761 |
amazonScrapingHistory.item_id = val.sku[3:]
|
| 763 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
762 |
amazonScrapingHistory.warehouseLocation = val.state_id
|
| 764 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
763 |
amazonScrapingHistory.parentCategoryId = val.parent_category
|
| - |
|
764 |
amazonScrapingHistory.ourSellingPrice = val.ourSp
|
| - |
|
765 |
amazonScrapingHistory.promoPrice = val.promoPrice
|
| 765 |
amazonScrapingHistory.reason = reason
|
766 |
amazonScrapingHistory.reason = reason
|
| 766 |
amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)
|
767 |
amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)
|
| 767 |
amazonScrapingHistory.competitiveCategory = CompetitionCategory.EXCEPTION
|
768 |
amazonScrapingHistory.competitiveCategory = CompetitionCategory.EXCEPTION
|
| 768 |
amazonScrapingHistory.timestamp = timestamp
|
769 |
amazonScrapingHistory.timestamp = timestamp
|
| 769 |
session.commit()
|
770 |
session.commit()
|
| Line 1230... |
Line 1231... |
| 1230 |
sheet.write(0, 9, "Our SP", heading_xf)
|
1231 |
sheet.write(0, 9, "Our SP", heading_xf)
|
| 1231 |
sheet.write(0, 10, "Promo Price", heading_xf)
|
1232 |
sheet.write(0, 10, "Promo Price", heading_xf)
|
| 1232 |
sheet.write(0, 11, "Is Promotion", heading_xf)
|
1233 |
sheet.write(0, 11, "Is Promotion", heading_xf)
|
| 1233 |
sheet.write(0, 12, "Lowest Possible SP", heading_xf)
|
1234 |
sheet.write(0, 12, "Lowest Possible SP", heading_xf)
|
| 1234 |
sheet.write(0, 13, "Rank", heading_xf)
|
1235 |
sheet.write(0, 13, "Rank", heading_xf)
|
| - |
|
1236 |
sheet.write(0, 14, "Competitive Category", heading_xf)
|
| 1235 |
sheet.write(0, 14, "Our Inventory", heading_xf)
|
1237 |
sheet.write(0, 15, "Our Inventory", heading_xf)
|
| 1236 |
sheet.write(0, 15, "Lowest Seller SP", heading_xf)
|
1238 |
sheet.write(0, 16, "Lowest Seller SP", heading_xf)
|
| 1237 |
sheet.write(0, 16, "Lowest Seller Rating", heading_xf)
|
1239 |
sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
|
| 1238 |
sheet.write(0, 17, "Lowest Seller Shipping Time", heading_xf)
|
1240 |
sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
|
| 1239 |
sheet.write(0, 18, "Second Lowest Seller SP", heading_xf)
|
1241 |
sheet.write(0, 19, "Second Lowest Seller SP", heading_xf)
|
| 1240 |
sheet.write(0, 19, "Second Lowest Seller Rating", heading_xf)
|
1242 |
sheet.write(0, 20, "Second Lowest Seller Rating", heading_xf)
|
| 1241 |
sheet.write(0, 20, "Second Lowest Seller Shipping Time", heading_xf)
|
1243 |
sheet.write(0, 21, "Second Lowest Seller Shipping Time", heading_xf)
|
| 1242 |
sheet.write(0, 21, "Third Lowest Seller SP", heading_xf)
|
1244 |
sheet.write(0, 22, "Third Lowest Seller SP", heading_xf)
|
| 1243 |
sheet.write(0, 22, "Third Lowest Seller Rating", heading_xf)
|
1245 |
sheet.write(0, 23, "Third Lowest Seller Rating", heading_xf)
|
| 1244 |
sheet.write(0, 23, "Third Lowest Seller Shipping Time", heading_xf)
|
1246 |
sheet.write(0, 24, "Third Lowest Seller Shipping Time", heading_xf)
|
| 1245 |
sheet.write(0, 24, "Lowest MFN Ignored", heading_xf)
|
1247 |
sheet.write(0, 25, "Lowest MFN Ignored", heading_xf)
|
| 1246 |
sheet.write(0, 25, "Lowest MFN", heading_xf)
|
1248 |
sheet.write(0, 26, "Lowest MFN", heading_xf)
|
| 1247 |
sheet.write(0, 26, "Lowest FBA", heading_xf)
|
1249 |
sheet.write(0, 27, "Lowest FBA", heading_xf)
|
| 1248 |
sheet.write(0, 27, "Competitive Price", heading_xf)
|
1250 |
sheet.write(0, 28, "Competitive Price", heading_xf)
|
| 1249 |
sheet.write(0, 28, "Other Cost", heading_xf)
|
1251 |
sheet.write(0, 29, "Other Cost", heading_xf)
|
| 1250 |
sheet.write(0, 29, "WANLC", heading_xf)
|
1252 |
sheet.write(0, 30, "WANLC", heading_xf)
|
| 1251 |
sheet.write(0, 30, "Subsidy", heading_xf)
|
1253 |
sheet.write(0, 31, "Subsidy", heading_xf)
|
| 1252 |
sheet.write(0, 31, "Commission", heading_xf)
|
1254 |
sheet.write(0, 32, "Commission", heading_xf)
|
| 1253 |
sheet.write(0, 32, "Competitor Commission", heading_xf)
|
1255 |
sheet.write(0, 33, "Competitor Commission", heading_xf)
|
| 1254 |
sheet.write(0, 33, "Return Provision", heading_xf)
|
1256 |
sheet.write(0, 34, "Return Provision", heading_xf)
|
| 1255 |
sheet.write(0, 34, "Vat Rate", heading_xf)
|
1257 |
sheet.write(0, 35, "Vat Rate", heading_xf)
|
| 1256 |
sheet.write(0, 35, "Margin", heading_xf)
|
1258 |
sheet.write(0, 36, "Margin", heading_xf)
|
| 1257 |
sheet.write(0, 36, "Proposed Sp", heading_xf)
|
1259 |
sheet.write(0, 37, "Proposed Sp", heading_xf)
|
| 1258 |
sheet.write(0, 37, "Avg Sale", heading_xf)
|
1260 |
sheet.write(0, 38, "Avg Sale", heading_xf)
|
| - |
|
1261 |
sheet.write(0, 39, "NOD", heading_xf)
|
| 1259 |
sheet.write(0, 38, "Sales History", heading_xf)
|
1262 |
sheet.write(0, 40, "Sales History", heading_xf)
|
| - |
|
1263 |
sheet.write(0, 41, "Decision", heading_xf)
|
| - |
|
1264 |
sheet.write(0, 42, "Reason", heading_xf)
|
| - |
|
1265 |
sheet.write(0, 43, "Updated Price", heading_xf)
|
| - |
|
1266 |
sheet.write(0, 44, "Proposed Margin", heading_xf)
|
| - |
|
1267 |
sheet.write(0, 45, "Inventory Movement Status")
|
| 1260 |
|
1268 |
|
| 1261 |
def getPackagingCost(data):
|
1269 |
def getPackagingCost(data):
|
| 1262 |
#TODO : Get packagingCost from marketplaceitems table
|
1270 |
#TODO : Get packagingCost from marketplaceitems table
|
| 1263 |
return 15
|
1271 |
return 15
|
| 1264 |
|
1272 |
|
| Line 1281... |
Line 1289... |
| 1281 |
|
1289 |
|
| 1282 |
def getCostToAmazon(data):
|
1290 |
def getCostToAmazon(data):
|
| 1283 |
myCostToAmazon = round(data.promoPrice*data.commission/100*(1+getServiceTax(data)/100)+getCourierCost(data)*(1+getServiceTax(data)/100))
|
1291 |
myCostToAmazon = round(data.promoPrice*data.commission/100*(1+getServiceTax(data)/100)+getCourierCost(data)*(1+getServiceTax(data)/100))
|
| 1284 |
return myCostToAmazon
|
1292 |
return myCostToAmazon
|
| 1285 |
|
1293 |
|
| - |
|
1294 |
def getInventoryMovementStatus(amScraping):
|
| - |
|
1295 |
try:
|
| - |
|
1296 |
nodStock = (float(amScraping.avgSale))/amScraping.ourInventory
|
| - |
|
1297 |
except:
|
| - |
|
1298 |
nodStock = float("inf")
|
| - |
|
1299 |
try:
|
| - |
|
1300 |
if amScraping.avgSale==0:
|
| - |
|
1301 |
return "Not Moving"
|
| - |
|
1302 |
elif nodStock > 30:
|
| - |
|
1303 |
return "Slow Moving"
|
| - |
|
1304 |
else:
|
| - |
|
1305 |
"Fast Moving"
|
| - |
|
1306 |
except:
|
| - |
|
1307 |
return ""
|
| - |
|
1308 |
|
| 1286 |
def getMargin(amScraping):
|
1309 |
def getMargin(amScraping):
|
| 1287 |
#sheet.write(sheet_iterator, 30, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
1310 |
#sheet.write(sheet_iterator, 30, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
|
| 1288 |
#Promo Price minus costs plus subsidy
|
1311 |
#Promo Price minus costs plus subsidy
|
| 1289 |
#costs = WANLC (actual or overrrde whichever is applicable) + Courier cost + Closing fee + Commission + Packaging + VAT + Returns Cost + other cost.
|
1312 |
#costs = WANLC (actual or overrrde whichever is applicable) + Courier cost + Closing fee + Commission + Packaging + VAT + Returns Cost + other cost.
|
| 1290 |
'''
|
1313 |
'''
|
| Line 1398... |
Line 1421... |
| 1398 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
1421 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
| 1399 |
if amScraping.ourRank > 3:
|
1422 |
if amScraping.ourRank > 3:
|
| 1400 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
1423 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
| 1401 |
else:
|
1424 |
else:
|
| 1402 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
1425 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
| - |
|
1426 |
sheet.write(sheet_iterator, 14, 'Cant Compete')
|
| 1403 |
sheet.write(sheet_iterator, 14, amScraping.ourInventory)
|
1427 |
sheet.write(sheet_iterator, 15, amScraping.ourInventory)
|
| 1404 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
|
1428 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerSp)
|
| 1405 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerRating)
|
1429 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerRating)
|
| 1406 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerShippingTime)
|
1430 |
sheet.write(sheet_iterator, 18, amScraping.lowestSellerShippingTime)
|
| 1407 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerSp)
|
1431 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerSp)
|
| 1408 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerRating)
|
1432 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerRating)
|
| 1409 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerShippingTime)
|
1433 |
sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerShippingTime)
|
| 1410 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerSp)
|
1434 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerSp)
|
| 1411 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerRating)
|
1435 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerRating)
|
| 1412 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerShippingTime)
|
1436 |
sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerShippingTime)
|
| 1413 |
sheet.write(sheet_iterator, 24, amScraping.lowestMfnIgnoredOffer)
|
1437 |
sheet.write(sheet_iterator, 25, amScraping.lowestMfnIgnoredOffer)
|
| 1414 |
sheet.write(sheet_iterator, 25, amScraping.lowestMfnOffer)
|
1438 |
sheet.write(sheet_iterator, 26, amScraping.lowestMfnOffer)
|
| 1415 |
sheet.write(sheet_iterator, 26, amScraping.lowestFbaOffer)
|
1439 |
sheet.write(sheet_iterator, 27, amScraping.lowestFbaOffer)
|
| 1416 |
sheet.write(sheet_iterator, 27, amScraping.competitivePrice)
|
1440 |
sheet.write(sheet_iterator, 28, amScraping.competitivePrice)
|
| 1417 |
sheet.write(sheet_iterator, 28, amScraping.otherCost)
|
1441 |
sheet.write(sheet_iterator, 29, amScraping.otherCost)
|
| 1418 |
sheet.write(sheet_iterator, 29, amScraping.wanlc)
|
1442 |
sheet.write(sheet_iterator, 30, amScraping.wanlc)
|
| 1419 |
sheet.write(sheet_iterator, 30, amScraping.subsidy)
|
1443 |
sheet.write(sheet_iterator, 31, amScraping.subsidy)
|
| 1420 |
sheet.write(sheet_iterator, 31, amScraping.commission)
|
1444 |
sheet.write(sheet_iterator, 32, amScraping.commission)
|
| 1421 |
sheet.write(sheet_iterator, 32, amScraping.competitorCommission)
|
1445 |
sheet.write(sheet_iterator, 33, amScraping.competitorCommission)
|
| 1422 |
sheet.write(sheet_iterator, 33, amScraping.returnProvision)
|
1446 |
sheet.write(sheet_iterator, 34, amScraping.returnProvision)
|
| 1423 |
sheet.write(sheet_iterator, 34, amScraping.vatRate)
|
1447 |
sheet.write(sheet_iterator, 35, amScraping.vatRate)
|
| 1424 |
sheet.write(sheet_iterator, 35, getMargin(amScraping))
|
1448 |
sheet.write(sheet_iterator, 36, getMargin(amScraping))
|
| 1425 |
sheet.write(sheet_iterator, 36, amScraping.proposedSp)
|
1449 |
sheet.write(sheet_iterator, 37, amScraping.proposedSp)
|
| 1426 |
sheet.write(sheet_iterator, 37, amScraping.avgSale)
|
1450 |
sheet.write(sheet_iterator, 38, amScraping.avgSale)
|
| - |
|
1451 |
try:
|
| - |
|
1452 |
daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
|
| - |
|
1453 |
except:
|
| - |
|
1454 |
daysOfStock = float("inf")
|
| - |
|
1455 |
sheet.write(sheet_iterator, 39, daysOfStock)
|
| 1427 |
sheet.write(sheet_iterator, 38, getOosString(saleMap.get(sku)))
|
1456 |
sheet.write(sheet_iterator, 40, getOosString(saleMap.get(sku)))
|
| - |
|
1457 |
sheet.write(sheet_iterator, 44, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))
|
| - |
|
1458 |
sheet.write(sheet_iterator, 45, getInventoryMovementStatus(amScraping))
|
| 1428 |
sheet_iterator+=1
|
1459 |
sheet_iterator+=1
|
| 1429 |
#TODO : Take excell sheet generation code inside a function
|
1460 |
#TODO : Take excell sheet generation code inside a function
|
| 1430 |
sheet = wbk.add_sheet('Competitive')
|
- |
|
| 1431 |
xstr = lambda s: s or ""
|
- |
|
| 1432 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
- |
|
| 1433 |
|
- |
|
| 1434 |
excel_integer_format = '0'
|
- |
|
| 1435 |
integer_style = xlwt.XFStyle()
|
- |
|
| 1436 |
integer_style.num_format_str = excel_integer_format
|
- |
|
| 1437 |
|
- |
|
| 1438 |
writeheaders(sheet,heading_xf)
|
- |
|
| 1439 |
sheet.write(0, 39, "Decision", heading_xf)
|
- |
|
| 1440 |
sheet.write(0, 40, "Reason", heading_xf)
|
- |
|
| 1441 |
sheet.write(0, 41, "Updated Price", heading_xf)
|
- |
|
| 1442 |
|
- |
|
| 1443 |
sheet_iterator = 1
|
- |
|
| 1444 |
competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1461 |
competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1445 |
for competitiveItem in competitiveItems:
|
1462 |
for competitiveItem in competitiveItems:
|
| 1446 |
amScraping = competitiveItem[0]
|
1463 |
amScraping = competitiveItem[0]
|
| 1447 |
item = competitiveItem[1]
|
1464 |
item = competitiveItem[1]
|
| 1448 |
sheet.write(sheet_iterator, 0, amScraping.item_id)
|
1465 |
sheet.write(sheet_iterator, 0, amScraping.item_id)
|
| Line 1469... |
Line 1486... |
| 1469 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
1486 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
| 1470 |
if amScraping.ourRank > 3:
|
1487 |
if amScraping.ourRank > 3:
|
| 1471 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
1488 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
| 1472 |
else:
|
1489 |
else:
|
| 1473 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
1490 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
| - |
|
1491 |
sheet.write(sheet_iterator, 14, 'Competitive')
|
| 1474 |
sheet.write(sheet_iterator, 14, amScraping.ourInventory)
|
1492 |
sheet.write(sheet_iterator, 15, amScraping.ourInventory)
|
| 1475 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
|
1493 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerSp)
|
| 1476 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerRating)
|
1494 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerRating)
|
| 1477 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerShippingTime)
|
1495 |
sheet.write(sheet_iterator, 18, amScraping.lowestSellerShippingTime)
|
| 1478 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerSp)
|
1496 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerSp)
|
| 1479 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerRating)
|
1497 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerRating)
|
| 1480 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerShippingTime)
|
1498 |
sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerShippingTime)
|
| 1481 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerSp)
|
1499 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerSp)
|
| 1482 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerRating)
|
1500 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerRating)
|
| 1483 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerShippingTime)
|
1501 |
sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerShippingTime)
|
| 1484 |
sheet.write(sheet_iterator, 24, amScraping.lowestMfnIgnoredOffer)
|
1502 |
sheet.write(sheet_iterator, 25, amScraping.lowestMfnIgnoredOffer)
|
| 1485 |
sheet.write(sheet_iterator, 25, amScraping.lowestMfnOffer)
|
1503 |
sheet.write(sheet_iterator, 26, amScraping.lowestMfnOffer)
|
| 1486 |
sheet.write(sheet_iterator, 26, amScraping.lowestFbaOffer)
|
1504 |
sheet.write(sheet_iterator, 27, amScraping.lowestFbaOffer)
|
| 1487 |
sheet.write(sheet_iterator, 27, amScraping.competitivePrice)
|
1505 |
sheet.write(sheet_iterator, 28, amScraping.competitivePrice)
|
| 1488 |
sheet.write(sheet_iterator, 28, amScraping.otherCost)
|
1506 |
sheet.write(sheet_iterator, 29, amScraping.otherCost)
|
| 1489 |
sheet.write(sheet_iterator, 29, amScraping.wanlc)
|
1507 |
sheet.write(sheet_iterator, 30, amScraping.wanlc)
|
| 1490 |
sheet.write(sheet_iterator, 30, amScraping.subsidy)
|
1508 |
sheet.write(sheet_iterator, 31, amScraping.subsidy)
|
| 1491 |
sheet.write(sheet_iterator, 31, amScraping.commission)
|
1509 |
sheet.write(sheet_iterator, 32, amScraping.commission)
|
| 1492 |
sheet.write(sheet_iterator, 32, amScraping.competitorCommission)
|
1510 |
sheet.write(sheet_iterator, 33, amScraping.competitorCommission)
|
| 1493 |
sheet.write(sheet_iterator, 33, amScraping.returnProvision)
|
1511 |
sheet.write(sheet_iterator, 34, amScraping.returnProvision)
|
| 1494 |
sheet.write(sheet_iterator, 34, amScraping.vatRate)
|
1512 |
sheet.write(sheet_iterator, 35, amScraping.vatRate)
|
| 1495 |
sheet.write(sheet_iterator, 35, getMargin(amScraping))
|
1513 |
sheet.write(sheet_iterator, 36, getMargin(amScraping))
|
| 1496 |
sheet.write(sheet_iterator, 36, amScraping.proposedSp)
|
1514 |
sheet.write(sheet_iterator, 37, amScraping.proposedSp)
|
| 1497 |
sheet.write(sheet_iterator, 37, amScraping.avgSale)
|
1515 |
sheet.write(sheet_iterator, 38, amScraping.avgSale)
|
| - |
|
1516 |
try:
|
| - |
|
1517 |
daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
|
| - |
|
1518 |
except:
|
| - |
|
1519 |
daysOfStock = float("inf")
|
| - |
|
1520 |
sheet.write(sheet_iterator, 39, daysOfStock)
|
| 1498 |
sheet.write(sheet_iterator, 38, getOosString(saleMap.get(sku)))
|
1521 |
sheet.write(sheet_iterator, 40, getOosString(saleMap.get(sku)))
|
| 1499 |
if amScraping.decision is None:
|
1522 |
if amScraping.decision is None:
|
| 1500 |
sheet.write(sheet_iterator, 39, 'Auto Pricing Inactive')
|
1523 |
sheet.write(sheet_iterator, 41, 'Auto Pricing Inactive')
|
| 1501 |
sheet_iterator+=1
|
1524 |
sheet_iterator+=1
|
| 1502 |
continue
|
1525 |
continue
|
| 1503 |
sheet.write(sheet_iterator, 39, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
1526 |
sheet.write(sheet_iterator, 41, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
| 1504 |
sheet.write(sheet_iterator, 40, amScraping.reason)
|
1527 |
sheet.write(sheet_iterator, 42, amScraping.reason)
|
| 1505 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
1528 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 1506 |
sheet.write(sheet_iterator, 41, math.ceil(amScraping.proposedSp))
|
1529 |
sheet.write(sheet_iterator, 43, math.ceil(amScraping.proposedSp))
|
| 1507 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
1530 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 1508 |
sheet.write(sheet_iterator, 41, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
1531 |
sheet.write(sheet_iterator, 43, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
| - |
|
1532 |
sheet.write(sheet_iterator, 44, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))
|
| - |
|
1533 |
sheet.write(sheet_iterator, 45, getInventoryMovementStatus(amScraping))
|
| 1509 |
sheet_iterator+=1
|
1534 |
sheet_iterator+=1
|
| 1510 |
|
1535 |
|
| 1511 |
sheet = wbk.add_sheet('Almost Competitive')
|
- |
|
| 1512 |
xstr = lambda s: s or ""
|
- |
|
| 1513 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
- |
|
| 1514 |
|
- |
|
| 1515 |
excel_integer_format = '0'
|
- |
|
| 1516 |
integer_style = xlwt.XFStyle()
|
- |
|
| 1517 |
integer_style.num_format_str = excel_integer_format
|
- |
|
| 1518 |
writeheaders(sheet,heading_xf)
|
- |
|
| 1519 |
sheet.write(0, 39, "Decision", heading_xf)
|
- |
|
| 1520 |
sheet.write(0, 40, "Reason", heading_xf)
|
- |
|
| 1521 |
sheet.write(0, 41, "Updated Price", heading_xf)
|
- |
|
| 1522 |
sheet_iterator = 1
|
- |
|
| 1523 |
almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1536 |
almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1524 |
for almostCompetitiveItem in almostCompetitiveItems:
|
1537 |
for almostCompetitiveItem in almostCompetitiveItems:
|
| 1525 |
amScraping = almostCompetitiveItem[0]
|
1538 |
amScraping = almostCompetitiveItem[0]
|
| 1526 |
item = almostCompetitiveItem[1]
|
1539 |
item = almostCompetitiveItem[1]
|
| 1527 |
sheet.write(sheet_iterator, 0, amScraping.item_id)
|
1540 |
sheet.write(sheet_iterator, 0, amScraping.item_id)
|
| Line 1548... |
Line 1561... |
| 1548 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
1561 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
| 1549 |
if amScraping.ourRank > 3:
|
1562 |
if amScraping.ourRank > 3:
|
| 1550 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
1563 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
| 1551 |
else:
|
1564 |
else:
|
| 1552 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
1565 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
| - |
|
1566 |
sheet.write(sheet_iterator, 14, 'Almost Competitive')
|
| 1553 |
sheet.write(sheet_iterator, 14, amScraping.ourInventory)
|
1567 |
sheet.write(sheet_iterator, 15, amScraping.ourInventory)
|
| 1554 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
|
1568 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerSp)
|
| 1555 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerRating)
|
1569 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerRating)
|
| 1556 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerShippingTime)
|
1570 |
sheet.write(sheet_iterator, 18, amScraping.lowestSellerShippingTime)
|
| 1557 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerSp)
|
1571 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerSp)
|
| 1558 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerRating)
|
1572 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerRating)
|
| 1559 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerShippingTime)
|
1573 |
sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerShippingTime)
|
| 1560 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerSp)
|
1574 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerSp)
|
| 1561 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerRating)
|
1575 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerRating)
|
| 1562 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerShippingTime)
|
1576 |
sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerShippingTime)
|
| 1563 |
sheet.write(sheet_iterator, 24, amScraping.lowestMfnIgnoredOffer)
|
1577 |
sheet.write(sheet_iterator, 25, amScraping.lowestMfnIgnoredOffer)
|
| 1564 |
sheet.write(sheet_iterator, 25, amScraping.lowestMfnOffer)
|
1578 |
sheet.write(sheet_iterator, 26, amScraping.lowestMfnOffer)
|
| 1565 |
sheet.write(sheet_iterator, 26, amScraping.lowestFbaOffer)
|
1579 |
sheet.write(sheet_iterator, 27, amScraping.lowestFbaOffer)
|
| 1566 |
sheet.write(sheet_iterator, 27, amScraping.competitivePrice)
|
1580 |
sheet.write(sheet_iterator, 28, amScraping.competitivePrice)
|
| 1567 |
sheet.write(sheet_iterator, 28, amScraping.otherCost)
|
1581 |
sheet.write(sheet_iterator, 29, amScraping.otherCost)
|
| 1568 |
sheet.write(sheet_iterator, 29, amScraping.wanlc)
|
1582 |
sheet.write(sheet_iterator, 30, amScraping.wanlc)
|
| 1569 |
sheet.write(sheet_iterator, 30, amScraping.subsidy)
|
1583 |
sheet.write(sheet_iterator, 31, amScraping.subsidy)
|
| 1570 |
sheet.write(sheet_iterator, 31, amScraping.commission)
|
1584 |
sheet.write(sheet_iterator, 32, amScraping.commission)
|
| 1571 |
sheet.write(sheet_iterator, 32, amScraping.competitorCommission)
|
1585 |
sheet.write(sheet_iterator, 33, amScraping.competitorCommission)
|
| 1572 |
sheet.write(sheet_iterator, 33, amScraping.returnProvision)
|
1586 |
sheet.write(sheet_iterator, 34, amScraping.returnProvision)
|
| 1573 |
sheet.write(sheet_iterator, 34, amScraping.vatRate)
|
1587 |
sheet.write(sheet_iterator, 35, amScraping.vatRate)
|
| 1574 |
sheet.write(sheet_iterator, 35, getMargin(amScraping))
|
1588 |
sheet.write(sheet_iterator, 36, getMargin(amScraping))
|
| 1575 |
sheet.write(sheet_iterator, 36, amScraping.proposedSp)
|
1589 |
sheet.write(sheet_iterator, 37, amScraping.proposedSp)
|
| 1576 |
sheet.write(sheet_iterator, 37, amScraping.avgSale)
|
1590 |
sheet.write(sheet_iterator, 38, amScraping.avgSale)
|
| - |
|
1591 |
try:
|
| - |
|
1592 |
daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
|
| - |
|
1593 |
except:
|
| - |
|
1594 |
daysOfStock = float("inf")
|
| - |
|
1595 |
sheet.write(sheet_iterator, 39, daysOfStock)
|
| 1577 |
sheet.write(sheet_iterator, 38, getOosString(saleMap.get(sku)))
|
1596 |
sheet.write(sheet_iterator, 40, getOosString(saleMap.get(sku)))
|
| 1578 |
if amScraping.decision is None:
|
1597 |
if amScraping.decision is None:
|
| 1579 |
sheet.write(sheet_iterator, 39, 'Auto Pricing Inactive')
|
1598 |
sheet.write(sheet_iterator, 41, 'Auto Pricing Inactive')
|
| 1580 |
sheet_iterator+=1
|
1599 |
sheet_iterator+=1
|
| 1581 |
continue
|
1600 |
continue
|
| 1582 |
sheet.write(sheet_iterator, 39, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
1601 |
sheet.write(sheet_iterator, 41, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
| 1583 |
sheet.write(sheet_iterator, 40, amScraping.reason)
|
1602 |
sheet.write(sheet_iterator, 42, amScraping.reason)
|
| 1584 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
1603 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 1585 |
sheet.write(sheet_iterator, 41, math.ceil(amScraping.proposedSp))
|
1604 |
sheet.write(sheet_iterator, 43, math.ceil(amScraping.proposedSp))
|
| 1586 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
1605 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 1587 |
sheet.write(sheet_iterator, 41, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
1606 |
sheet.write(sheet_iterator, 43, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
| - |
|
1607 |
sheet.write(sheet_iterator, 44, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))
|
| - |
|
1608 |
sheet.write(sheet_iterator, 45, getInventoryMovementStatus(amScraping))
|
| 1588 |
sheet_iterator+=1
|
1609 |
sheet_iterator+=1
|
| 1589 |
|
1610 |
|
| 1590 |
sheet = wbk.add_sheet('Among Cheapest')
|
- |
|
| 1591 |
xstr = lambda s: s or ""
|
- |
|
| 1592 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
- |
|
| 1593 |
|
- |
|
| 1594 |
excel_integer_format = '0'
|
- |
|
| 1595 |
integer_style = xlwt.XFStyle()
|
- |
|
| 1596 |
integer_style.num_format_str = excel_integer_format
|
- |
|
| 1597 |
writeheaders(sheet,heading_xf)
|
- |
|
| 1598 |
sheet.write(0, 39, "Decision", heading_xf)
|
- |
|
| 1599 |
sheet.write(0, 40, "Reason", heading_xf)
|
- |
|
| 1600 |
sheet.write(0, 41, "Updated Price", heading_xf)
|
- |
|
| 1601 |
sheet_iterator = 1
|
- |
|
| 1602 |
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()
|
1611 |
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()
|
| 1603 |
for amongCheapestItem in amongCheapestItems:
|
1612 |
for amongCheapestItem in amongCheapestItems:
|
| 1604 |
amScraping = amongCheapestItem[0]
|
1613 |
amScraping = amongCheapestItem[0]
|
| 1605 |
item = amongCheapestItem[1]
|
1614 |
item = amongCheapestItem[1]
|
| 1606 |
sheet.write(sheet_iterator, 0, amScraping.item_id)
|
1615 |
sheet.write(sheet_iterator, 0, amScraping.item_id)
|
| Line 1627... |
Line 1636... |
| 1627 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
1636 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
| 1628 |
if amScraping.ourRank > 3:
|
1637 |
if amScraping.ourRank > 3:
|
| 1629 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
1638 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
| 1630 |
else:
|
1639 |
else:
|
| 1631 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
1640 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
| - |
|
1641 |
sheet.write(sheet_iterator, 14, 'Among Cheapest')
|
| 1632 |
sheet.write(sheet_iterator, 14, amScraping.ourInventory)
|
1642 |
sheet.write(sheet_iterator, 15, amScraping.ourInventory)
|
| 1633 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
|
1643 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerSp)
|
| 1634 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerRating)
|
1644 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerRating)
|
| 1635 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerShippingTime)
|
1645 |
sheet.write(sheet_iterator, 18, amScraping.lowestSellerShippingTime)
|
| 1636 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerSp)
|
1646 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerSp)
|
| 1637 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerRating)
|
1647 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerRating)
|
| 1638 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerShippingTime)
|
1648 |
sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerShippingTime)
|
| 1639 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerSp)
|
1649 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerSp)
|
| 1640 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerRating)
|
1650 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerRating)
|
| 1641 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerShippingTime)
|
1651 |
sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerShippingTime)
|
| 1642 |
sheet.write(sheet_iterator, 24, amScraping.lowestMfnIgnoredOffer)
|
1652 |
sheet.write(sheet_iterator, 25, amScraping.lowestMfnIgnoredOffer)
|
| 1643 |
sheet.write(sheet_iterator, 25, amScraping.lowestMfnOffer)
|
1653 |
sheet.write(sheet_iterator, 26, amScraping.lowestMfnOffer)
|
| 1644 |
sheet.write(sheet_iterator, 26, amScraping.lowestFbaOffer)
|
1654 |
sheet.write(sheet_iterator, 27, amScraping.lowestFbaOffer)
|
| 1645 |
sheet.write(sheet_iterator, 27, amScraping.competitivePrice)
|
1655 |
sheet.write(sheet_iterator, 28, amScraping.competitivePrice)
|
| 1646 |
sheet.write(sheet_iterator, 28, amScraping.otherCost)
|
1656 |
sheet.write(sheet_iterator, 29, amScraping.otherCost)
|
| 1647 |
sheet.write(sheet_iterator, 29, amScraping.wanlc)
|
1657 |
sheet.write(sheet_iterator, 30, amScraping.wanlc)
|
| 1648 |
sheet.write(sheet_iterator, 30, amScraping.subsidy)
|
1658 |
sheet.write(sheet_iterator, 31, amScraping.subsidy)
|
| 1649 |
sheet.write(sheet_iterator, 31, amScraping.commission)
|
1659 |
sheet.write(sheet_iterator, 32, amScraping.commission)
|
| 1650 |
sheet.write(sheet_iterator, 32, amScraping.competitorCommission)
|
1660 |
sheet.write(sheet_iterator, 33, amScraping.competitorCommission)
|
| 1651 |
sheet.write(sheet_iterator, 33, amScraping.returnProvision)
|
1661 |
sheet.write(sheet_iterator, 34, amScraping.returnProvision)
|
| 1652 |
sheet.write(sheet_iterator, 34, amScraping.vatRate)
|
1662 |
sheet.write(sheet_iterator, 35, amScraping.vatRate)
|
| 1653 |
sheet.write(sheet_iterator, 35, getMargin(amScraping))
|
1663 |
sheet.write(sheet_iterator, 36, getMargin(amScraping))
|
| 1654 |
sheet.write(sheet_iterator, 36, amScraping.proposedSp)
|
1664 |
sheet.write(sheet_iterator, 37, amScraping.proposedSp)
|
| 1655 |
sheet.write(sheet_iterator, 37, amScraping.avgSale)
|
1665 |
sheet.write(sheet_iterator, 38, amScraping.avgSale)
|
| - |
|
1666 |
try:
|
| - |
|
1667 |
daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
|
| - |
|
1668 |
except:
|
| - |
|
1669 |
daysOfStock = float("inf")
|
| - |
|
1670 |
sheet.write(sheet_iterator, 39, daysOfStock)
|
| 1656 |
sheet.write(sheet_iterator, 38, getOosString(saleMap.get(sku)))
|
1671 |
sheet.write(sheet_iterator, 40, getOosString(saleMap.get(sku)))
|
| 1657 |
if amScraping.decision is None:
|
1672 |
if amScraping.decision is None:
|
| 1658 |
sheet.write(sheet_iterator, 39, 'Auto Pricing Inactive')
|
1673 |
sheet.write(sheet_iterator, 41, 'Auto Pricing Inactive')
|
| 1659 |
sheet_iterator+=1
|
1674 |
sheet_iterator+=1
|
| 1660 |
continue
|
1675 |
continue
|
| 1661 |
sheet.write(sheet_iterator, 39, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
1676 |
sheet.write(sheet_iterator, 41, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
| 1662 |
sheet.write(sheet_iterator, 40, amScraping.reason)
|
1677 |
sheet.write(sheet_iterator, 42, amScraping.reason)
|
| 1663 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
1678 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 1664 |
sheet.write(sheet_iterator, 41, math.ceil(amScraping.proposedSp))
|
1679 |
sheet.write(sheet_iterator, 43, math.ceil(amScraping.proposedSp))
|
| 1665 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
1680 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 1666 |
sheet.write(sheet_iterator, 41, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
1681 |
sheet.write(sheet_iterator, 43, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
| - |
|
1682 |
sheet.write(sheet_iterator, 44, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))
|
| - |
|
1683 |
sheet.write(sheet_iterator, 45, getInventoryMovementStatus(amScraping))
|
| 1667 |
sheet_iterator+=1
|
1684 |
sheet_iterator+=1
|
| 1668 |
|
1685 |
|
| 1669 |
sheet = wbk.add_sheet('Cheapest')
|
1686 |
# sheet = wbk.add_sheet('Cheapest')
|
| 1670 |
xstr = lambda s: s or ""
|
1687 |
# xstr = lambda s: s or ""
|
| 1671 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1688 |
# heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1672 |
|
1689 |
#
|
| 1673 |
excel_integer_format = '0'
|
1690 |
# excel_integer_format = '0'
|
| 1674 |
integer_style = xlwt.XFStyle()
|
1691 |
# integer_style = xlwt.XFStyle()
|
| 1675 |
integer_style.num_format_str = excel_integer_format
|
1692 |
# integer_style.num_format_str = excel_integer_format
|
| 1676 |
sheet.write(0, 0, "Item Id", heading_xf)
|
1693 |
# sheet.write(0, 0, "Item Id", heading_xf)
|
| 1677 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
1694 |
# sheet.write(0, 1, "Amazon Sku", heading_xf)
|
| 1678 |
sheet.write(0, 2, "Asin", heading_xf)
|
1695 |
# sheet.write(0, 2, "Asin", heading_xf)
|
| 1679 |
sheet.write(0, 3, "Location", heading_xf)
|
1696 |
# sheet.write(0, 3, "Location", heading_xf)
|
| 1680 |
sheet.write(0, 4, "Brand", heading_xf)
|
1697 |
# sheet.write(0, 4, "Brand", heading_xf)
|
| 1681 |
sheet.write(0, 5, "Category", heading_xf)
|
1698 |
# sheet.write(0, 5, "Category", heading_xf)
|
| 1682 |
sheet.write(0, 6, "Product Name", heading_xf)
|
1699 |
# sheet.write(0, 6, "Product Name", heading_xf)
|
| 1683 |
sheet.write(0, 7, "Weight", heading_xf)
|
1700 |
# sheet.write(0, 7, "Weight", heading_xf)
|
| 1684 |
sheet.write(0, 8, "Courier Cost", heading_xf)
|
1701 |
# sheet.write(0, 8, "Courier Cost", heading_xf)
|
| 1685 |
sheet.write(0, 9, "Our SP", heading_xf)
|
1702 |
# sheet.write(0, 9, "Our SP", heading_xf)
|
| 1686 |
sheet.write(0, 10, "Promo Price", heading_xf)
|
1703 |
# sheet.write(0, 10, "Promo Price", heading_xf)
|
| 1687 |
sheet.write(0, 11, "Is Promotion", heading_xf)
|
1704 |
# sheet.write(0, 11, "Is Promotion", heading_xf)
|
| 1688 |
sheet.write(0, 12, "Lowest Possible SP", heading_xf)
|
1705 |
# sheet.write(0, 12, "Lowest Possible SP", heading_xf)
|
| 1689 |
sheet.write(0, 13, "Rank", heading_xf)
|
1706 |
# sheet.write(0, 13, "Rank", heading_xf)
|
| 1690 |
sheet.write(0, 14, "Our Inventory", heading_xf)
|
1707 |
# sheet.write(0, 14, "Our Inventory", heading_xf)
|
| 1691 |
sheet.write(0, 15, "Lowest Seller SP", heading_xf)
|
1708 |
# sheet.write(0, 15, "Lowest Seller SP", heading_xf)
|
| 1692 |
sheet.write(0, 16, "Lowest Seller Rating", heading_xf)
|
1709 |
# sheet.write(0, 16, "Lowest Seller Rating", heading_xf)
|
| 1693 |
sheet.write(0, 17, "Lowest Seller Shipping Time", heading_xf)
|
1710 |
# sheet.write(0, 17, "Lowest Seller Shipping Time", heading_xf)
|
| 1694 |
sheet.write(0, 18, "Second Lowest Seller SP", heading_xf)
|
1711 |
# sheet.write(0, 18, "Second Lowest Seller SP", heading_xf)
|
| 1695 |
sheet.write(0, 19, "Second Lowest Seller Rating", heading_xf)
|
1712 |
# sheet.write(0, 19, "Second Lowest Seller Rating", heading_xf)
|
| 1696 |
sheet.write(0, 20, "Second Lowest Seller Shipping Time", heading_xf)
|
1713 |
# sheet.write(0, 20, "Second Lowest Seller Shipping Time", heading_xf)
|
| 1697 |
sheet.write(0, 21, "Third Lowest Seller SP", heading_xf)
|
1714 |
# sheet.write(0, 21, "Third Lowest Seller SP", heading_xf)
|
| 1698 |
sheet.write(0, 22, "Third Lowest Seller Rating", heading_xf)
|
1715 |
# sheet.write(0, 22, "Third Lowest Seller Rating", heading_xf)
|
| 1699 |
sheet.write(0, 23, "Third Lowest Seller Shipping Time", heading_xf)
|
1716 |
# sheet.write(0, 23, "Third Lowest Seller Shipping Time", heading_xf)
|
| 1700 |
sheet.write(0, 24, "Other Cost", heading_xf)
|
1717 |
# sheet.write(0, 24, "Other Cost", heading_xf)
|
| 1701 |
sheet.write(0, 25, "WANLC", heading_xf)
|
1718 |
# sheet.write(0, 25, "WANLC", heading_xf)
|
| 1702 |
sheet.write(0, 26, "Subsidy", heading_xf)
|
1719 |
# sheet.write(0, 26, "Subsidy", heading_xf)
|
| 1703 |
sheet.write(0, 27, "Commission", heading_xf)
|
1720 |
# sheet.write(0, 27, "Commission", heading_xf)
|
| 1704 |
sheet.write(0, 28, "Competitor Commission", heading_xf)
|
1721 |
# sheet.write(0, 28, "Competitor Commission", heading_xf)
|
| 1705 |
sheet.write(0, 29, "Return Provision", heading_xf)
|
1722 |
# sheet.write(0, 29, "Return Provision", heading_xf)
|
| 1706 |
sheet.write(0, 30, "Vat Rate", heading_xf)
|
1723 |
# sheet.write(0, 30, "Vat Rate", heading_xf)
|
| 1707 |
sheet.write(0, 31, "Margin", heading_xf)
|
1724 |
# sheet.write(0, 31, "Margin", heading_xf)
|
| 1708 |
sheet.write(0, 32, "Proposed Sp", heading_xf)
|
1725 |
# sheet.write(0, 32, "Proposed Sp", heading_xf)
|
| 1709 |
sheet.write(0, 33, "Avg Sale", heading_xf)
|
1726 |
# sheet.write(0, 33, "Avg Sale", heading_xf)
|
| 1710 |
sheet.write(0, 34, "Sales History", heading_xf)
|
1727 |
# sheet.write(0, 34, "Sales History", heading_xf)
|
| 1711 |
sheet.write(0, 35, "Decision", heading_xf)
|
1728 |
# sheet.write(0, 35, "Decision", heading_xf)
|
| 1712 |
sheet.write(0, 36, "Reason", heading_xf)
|
1729 |
# sheet.write(0, 36, "Reason", heading_xf)
|
| 1713 |
sheet.write(0, 37, "Updated Price", heading_xf)
|
1730 |
# sheet.write(0, 37, "Updated Price", heading_xf)
|
| 1714 |
sheet_iterator = 1
|
1731 |
# sheet_iterator = 1
|
| 1715 |
cheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1732 |
cheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1716 |
for cheapestItem in cheapestItems:
|
1733 |
for cheapestItem in cheapestItems:
|
| 1717 |
amScraping = cheapestItem[0]
|
1734 |
amScraping = cheapestItem[0]
|
| 1718 |
item = cheapestItem[1]
|
1735 |
item = cheapestItem[1]
|
| 1719 |
sheet.write(sheet_iterator, 0, amScraping.item_id)
|
1736 |
sheet.write(sheet_iterator, 0, amScraping.item_id)
|
| Line 1740... |
Line 1757... |
| 1740 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
1757 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
| 1741 |
if amScraping.ourRank > 3:
|
1758 |
if amScraping.ourRank > 3:
|
| 1742 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
1759 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
| 1743 |
else:
|
1760 |
else:
|
| 1744 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
1761 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
| - |
|
1762 |
sheet.write(sheet_iterator, 14, 'Cheapest')
|
| 1745 |
sheet.write(sheet_iterator, 14, amScraping.ourInventory)
|
1763 |
sheet.write(sheet_iterator, 15, amScraping.ourInventory)
|
| 1746 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
|
1764 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerSp)
|
| 1747 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerRating)
|
1765 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerRating)
|
| 1748 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerShippingTime)
|
1766 |
sheet.write(sheet_iterator, 18, amScraping.lowestSellerShippingTime)
|
| 1749 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerSp)
|
1767 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerSp)
|
| 1750 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerRating)
|
1768 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerRating)
|
| 1751 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerShippingTime)
|
1769 |
sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerShippingTime)
|
| 1752 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerSp)
|
1770 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerSp)
|
| 1753 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerRating)
|
1771 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerRating)
|
| 1754 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerShippingTime)
|
1772 |
sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerShippingTime)
|
| - |
|
1773 |
sheet.write(sheet_iterator, 25, '')
|
| - |
|
1774 |
sheet.write(sheet_iterator, 26, '')
|
| - |
|
1775 |
sheet.write(sheet_iterator, 27, '')
|
| - |
|
1776 |
sheet.write(sheet_iterator, 28, '')
|
| 1755 |
sheet.write(sheet_iterator, 24, amScraping.otherCost)
|
1777 |
sheet.write(sheet_iterator, 29, amScraping.otherCost)
|
| 1756 |
sheet.write(sheet_iterator, 25, amScraping.wanlc)
|
1778 |
sheet.write(sheet_iterator, 30, amScraping.wanlc)
|
| 1757 |
sheet.write(sheet_iterator, 26, amScraping.subsidy)
|
1779 |
sheet.write(sheet_iterator, 31, amScraping.subsidy)
|
| 1758 |
sheet.write(sheet_iterator, 27, amScraping.commission)
|
1780 |
sheet.write(sheet_iterator, 32, amScraping.commission)
|
| 1759 |
sheet.write(sheet_iterator, 28, amScraping.competitorCommission)
|
1781 |
sheet.write(sheet_iterator, 33, amScraping.competitorCommission)
|
| 1760 |
sheet.write(sheet_iterator, 29, amScraping.returnProvision)
|
1782 |
sheet.write(sheet_iterator, 34, amScraping.returnProvision)
|
| 1761 |
sheet.write(sheet_iterator, 30, amScraping.vatRate)
|
1783 |
sheet.write(sheet_iterator, 35, amScraping.vatRate)
|
| 1762 |
sheet.write(sheet_iterator, 31, getMargin(amScraping))
|
1784 |
sheet.write(sheet_iterator, 36, getMargin(amScraping))
|
| 1763 |
sheet.write(sheet_iterator, 32, amScraping.proposedSp)
|
1785 |
sheet.write(sheet_iterator, 37, amScraping.proposedSp)
|
| 1764 |
sheet.write(sheet_iterator, 33, amScraping.avgSale)
|
1786 |
sheet.write(sheet_iterator, 38, amScraping.avgSale)
|
| - |
|
1787 |
try:
|
| - |
|
1788 |
daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
|
| - |
|
1789 |
except:
|
| - |
|
1790 |
daysOfStock = float("inf")
|
| - |
|
1791 |
sheet.write(sheet_iterator, 39, daysOfStock)
|
| 1765 |
sheet.write(sheet_iterator, 34, getOosString(saleMap.get(sku)))
|
1792 |
sheet.write(sheet_iterator, 40, getOosString(saleMap.get(sku)))
|
| 1766 |
if amScraping.decision is None:
|
1793 |
if amScraping.decision is None:
|
| 1767 |
sheet.write(sheet_iterator, 35, 'Auto Pricing Inactive')
|
1794 |
sheet.write(sheet_iterator, 41, 'Auto Pricing Inactive')
|
| 1768 |
sheet_iterator+=1
|
1795 |
sheet_iterator+=1
|
| 1769 |
continue
|
1796 |
continue
|
| 1770 |
sheet.write(sheet_iterator, 35, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
1797 |
sheet.write(sheet_iterator, 41, Decision._VALUES_TO_NAMES.get(amScraping.decision))
|
| 1771 |
sheet.write(sheet_iterator, 36, amScraping.reason)
|
1798 |
sheet.write(sheet_iterator, 42, amScraping.reason)
|
| 1772 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
1799 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
|
| 1773 |
sheet.write(sheet_iterator, 37, math.ceil(amScraping.proposedSp))
|
1800 |
sheet.write(sheet_iterator, 43, math.ceil(amScraping.proposedSp))
|
| 1774 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
1801 |
if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
|
| 1775 |
sheet.write(sheet_iterator, 37, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
1802 |
sheet.write(sheet_iterator, 43, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
|
| - |
|
1803 |
sheet.write(sheet_iterator, 44, round(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)) - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice))))))
|
| - |
|
1804 |
sheet.write(sheet_iterator, 45, getInventoryMovementStatus(amScraping))
|
| 1776 |
sheet_iterator+=1
|
1805 |
sheet_iterator+=1
|
| 1777 |
|
1806 |
|
| 1778 |
sheet = wbk.add_sheet('Negative Margin')
|
1807 |
# sheet = wbk.add_sheet('Negative Margin')
|
| 1779 |
xstr = lambda s: s or ""
|
1808 |
# xstr = lambda s: s or ""
|
| 1780 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1809 |
# heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1781 |
|
1810 |
#
|
| 1782 |
excel_integer_format = '0'
|
1811 |
# excel_integer_format = '0'
|
| 1783 |
integer_style = xlwt.XFStyle()
|
1812 |
# integer_style = xlwt.XFStyle()
|
| 1784 |
integer_style.num_format_str = excel_integer_format
|
1813 |
# integer_style.num_format_str = excel_integer_format
|
| 1785 |
sheet.write(0, 0, "Item Id", heading_xf)
|
1814 |
# sheet.write(0, 0, "Item Id", heading_xf)
|
| 1786 |
sheet.write(0, 1, "Amazon Sku", heading_xf)
|
1815 |
# sheet.write(0, 1, "Amazon Sku", heading_xf)
|
| 1787 |
sheet.write(0, 2, "Asin", heading_xf)
|
1816 |
# sheet.write(0, 2, "Asin", heading_xf)
|
| 1788 |
sheet.write(0, 3, "Location", heading_xf)
|
1817 |
# sheet.write(0, 3, "Location", heading_xf)
|
| 1789 |
sheet.write(0, 4, "Brand", heading_xf)
|
1818 |
# sheet.write(0, 4, "Brand", heading_xf)
|
| 1790 |
sheet.write(0, 5, "Category", heading_xf)
|
1819 |
# sheet.write(0, 5, "Category", heading_xf)
|
| 1791 |
sheet.write(0, 6, "Product Name", heading_xf)
|
1820 |
# sheet.write(0, 6, "Product Name", heading_xf)
|
| 1792 |
sheet.write(0, 7, "Weight", heading_xf)
|
1821 |
# sheet.write(0, 7, "Weight", heading_xf)
|
| 1793 |
sheet.write(0, 8, "Courier Cost", heading_xf)
|
1822 |
# sheet.write(0, 8, "Courier Cost", heading_xf)
|
| 1794 |
sheet.write(0, 9, "Our SP", heading_xf)
|
1823 |
# sheet.write(0, 9, "Our SP", heading_xf)
|
| 1795 |
sheet.write(0, 10, "Promo Price", heading_xf)
|
1824 |
# sheet.write(0, 10, "Promo Price", heading_xf)
|
| 1796 |
sheet.write(0, 11, "Is Promotion", heading_xf)
|
1825 |
# sheet.write(0, 11, "Is Promotion", heading_xf)
|
| 1797 |
sheet.write(0, 12, "Lowest Possible SP", heading_xf)
|
1826 |
# sheet.write(0, 12, "Lowest Possible SP", heading_xf)
|
| 1798 |
sheet.write(0, 13, "Rank", heading_xf)
|
1827 |
# sheet.write(0, 13, "Rank", heading_xf)
|
| 1799 |
sheet.write(0, 14, "Our Inventory", heading_xf)
|
1828 |
# sheet.write(0, 14, "Our Inventory", heading_xf)
|
| 1800 |
sheet.write(0, 15, "Lowest Seller SP", heading_xf)
|
1829 |
# sheet.write(0, 15, "Lowest Seller SP", heading_xf)
|
| 1801 |
sheet.write(0, 16, "Lowest Seller Rating", heading_xf)
|
1830 |
# sheet.write(0, 16, "Lowest Seller Rating", heading_xf)
|
| 1802 |
sheet.write(0, 17, "Lowest Seller Shipping Time", heading_xf)
|
1831 |
# sheet.write(0, 17, "Lowest Seller Shipping Time", heading_xf)
|
| 1803 |
sheet.write(0, 18, "Second Lowest Seller SP", heading_xf)
|
1832 |
# sheet.write(0, 18, "Second Lowest Seller SP", heading_xf)
|
| 1804 |
sheet.write(0, 19, "Second Lowest Seller Rating", heading_xf)
|
1833 |
# sheet.write(0, 19, "Second Lowest Seller Rating", heading_xf)
|
| 1805 |
sheet.write(0, 20, "Second Lowest Seller Shipping Time", heading_xf)
|
1834 |
# sheet.write(0, 20, "Second Lowest Seller Shipping Time", heading_xf)
|
| 1806 |
sheet.write(0, 21, "Third Lowest Seller SP", heading_xf)
|
1835 |
# sheet.write(0, 21, "Third Lowest Seller SP", heading_xf)
|
| 1807 |
sheet.write(0, 22, "Third Lowest Seller Rating", heading_xf)
|
1836 |
# sheet.write(0, 22, "Third Lowest Seller Rating", heading_xf)
|
| 1808 |
sheet.write(0, 23, "Third Lowest Seller Shipping Time", heading_xf)
|
1837 |
# sheet.write(0, 23, "Third Lowest Seller Shipping Time", heading_xf)
|
| 1809 |
sheet.write(0, 24, "Other Cost", heading_xf)
|
1838 |
# sheet.write(0, 24, "Other Cost", heading_xf)
|
| 1810 |
sheet.write(0, 25, "WANLC", heading_xf)
|
1839 |
# sheet.write(0, 25, "WANLC", heading_xf)
|
| 1811 |
sheet.write(0, 26, "Subsidy", heading_xf)
|
1840 |
# sheet.write(0, 26, "Subsidy", heading_xf)
|
| 1812 |
sheet.write(0, 27, "Commission", heading_xf)
|
1841 |
# sheet.write(0, 27, "Commission", heading_xf)
|
| 1813 |
sheet.write(0, 28, "Competitor Commission", heading_xf)
|
1842 |
# sheet.write(0, 28, "Competitor Commission", heading_xf)
|
| 1814 |
sheet.write(0, 29, "Return Provision", heading_xf)
|
1843 |
# sheet.write(0, 29, "Return Provision", heading_xf)
|
| 1815 |
sheet.write(0, 30, "Vat Rate", heading_xf)
|
1844 |
# sheet.write(0, 30, "Vat Rate", heading_xf)
|
| 1816 |
sheet.write(0, 31, "Margin", heading_xf)
|
1845 |
# sheet.write(0, 31, "Margin", heading_xf)
|
| 1817 |
sheet.write(0, 32, "Avg Sale", heading_xf)
|
1846 |
# sheet.write(0, 32, "Avg Sale", heading_xf)
|
| 1818 |
sheet.write(0, 33, "Sales History", heading_xf)
|
1847 |
# sheet.write(0, 33, "Sales History", heading_xf)
|
| 1819 |
|
1848 |
#
|
| 1820 |
sheet_iterator = 1
|
1849 |
# sheet_iterator = 1
|
| 1821 |
amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1850 |
negativeMargins = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1822 |
for amongCheapestItem in amongCheapestItems:
|
1851 |
for negativeMarginItem in negativeMargins:
|
| 1823 |
amScraping = amongCheapestItem[0]
|
1852 |
amScraping = negativeMarginItem[0]
|
| 1824 |
item = amongCheapestItem[1]
|
1853 |
item = negativeMarginItem[1]
|
| 1825 |
sheet.write(sheet_iterator, 0, amScraping.item_id)
|
1854 |
sheet.write(sheet_iterator, 0, amScraping.item_id)
|
| 1826 |
if amScraping.warehouseLocation == 1:
|
1855 |
if amScraping.warehouseLocation == 1:
|
| 1827 |
sku = 'FBA'+str(amScraping.item_id)
|
1856 |
sku = 'FBA'+str(amScraping.item_id)
|
| 1828 |
loc = 'MUMBAI'
|
1857 |
loc = 'MUMBAI'
|
| 1829 |
else:
|
1858 |
else:
|
| Line 1846... |
Line 1875... |
| 1846 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
1875 |
sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
|
| 1847 |
if amScraping.ourRank > 3:
|
1876 |
if amScraping.ourRank > 3:
|
| 1848 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
1877 |
sheet.write(sheet_iterator, 13, 'Greater than 3')
|
| 1849 |
else:
|
1878 |
else:
|
| 1850 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
1879 |
sheet.write(sheet_iterator, 13, amScraping.ourRank)
|
| - |
|
1880 |
sheet.write(sheet_iterator, 14, 'Negative Margin')
|
| 1851 |
sheet.write(sheet_iterator, 14, amScraping.ourInventory)
|
1881 |
sheet.write(sheet_iterator, 15, amScraping.ourInventory)
|
| 1852 |
sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
|
1882 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerSp)
|
| 1853 |
sheet.write(sheet_iterator, 16, amScraping.lowestSellerRating)
|
1883 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerRating)
|
| 1854 |
sheet.write(sheet_iterator, 17, amScraping.lowestSellerShippingTime)
|
1884 |
sheet.write(sheet_iterator, 18, amScraping.lowestSellerShippingTime)
|
| 1855 |
sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerSp)
|
1885 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerSp)
|
| 1856 |
sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerRating)
|
1886 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerRating)
|
| 1857 |
sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerShippingTime)
|
1887 |
sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerShippingTime)
|
| 1858 |
sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerSp)
|
1888 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerSp)
|
| 1859 |
sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerRating)
|
1889 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerRating)
|
| 1860 |
sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerShippingTime)
|
1890 |
sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerShippingTime)
|
| - |
|
1891 |
sheet.write(sheet_iterator, 25, '')
|
| - |
|
1892 |
sheet.write(sheet_iterator, 26, '')
|
| - |
|
1893 |
sheet.write(sheet_iterator, 27, '')
|
| - |
|
1894 |
sheet.write(sheet_iterator, 28, '')
|
| 1861 |
sheet.write(sheet_iterator, 24, amScraping.otherCost)
|
1895 |
sheet.write(sheet_iterator, 29, amScraping.otherCost)
|
| 1862 |
sheet.write(sheet_iterator, 25, amScraping.wanlc)
|
1896 |
sheet.write(sheet_iterator, 30, amScraping.wanlc)
|
| 1863 |
sheet.write(sheet_iterator, 26, amScraping.subsidy)
|
1897 |
sheet.write(sheet_iterator, 31, amScraping.subsidy)
|
| 1864 |
sheet.write(sheet_iterator, 27, amScraping.commission)
|
1898 |
sheet.write(sheet_iterator, 32, amScraping.commission)
|
| 1865 |
sheet.write(sheet_iterator, 28, amScraping.competitorCommission)
|
1899 |
sheet.write(sheet_iterator, 33, amScraping.competitorCommission)
|
| 1866 |
sheet.write(sheet_iterator, 29, amScraping.returnProvision)
|
1900 |
sheet.write(sheet_iterator, 34, amScraping.returnProvision)
|
| 1867 |
sheet.write(sheet_iterator, 30, amScraping.vatRate)
|
1901 |
sheet.write(sheet_iterator, 35, amScraping.vatRate)
|
| 1868 |
sheet.write(sheet_iterator, 31, getMargin(amScraping))
|
1902 |
sheet.write(sheet_iterator, 36, getMargin(amScraping))
|
| 1869 |
sheet.write(sheet_iterator, 32, amScraping.avgSale)
|
1903 |
sheet.write(sheet_iterator, 38, amScraping.avgSale)
|
| - |
|
1904 |
try:
|
| - |
|
1905 |
daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
|
| - |
|
1906 |
except:
|
| - |
|
1907 |
daysOfStock = float("inf")
|
| - |
|
1908 |
sheet.write(sheet_iterator, 39, daysOfStock)
|
| 1870 |
sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
|
1909 |
sheet.write(sheet_iterator, 40, getOosString(saleMap.get(sku)))
|
| - |
|
1910 |
sheet.write(sheet_iterator, 45, getInventoryMovementStatus(amScraping))
|
| 1871 |
sheet_iterator+=1
|
1911 |
sheet_iterator+=1
|
| 1872 |
|
1912 |
|
| 1873 |
sheet = wbk.add_sheet('Exception List')
|
1913 |
sheet = wbk.add_sheet('Exception List')
|
| 1874 |
xstr = lambda s: s or ""
|
1914 |
xstr = lambda s: s or ""
|
| 1875 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1915 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 1883... |
Line 1923... |
| 1883 |
sheet.write(0, 2, "Asin", heading_xf)
|
1923 |
sheet.write(0, 2, "Asin", heading_xf)
|
| 1884 |
sheet.write(0, 3, "Location", heading_xf)
|
1924 |
sheet.write(0, 3, "Location", heading_xf)
|
| 1885 |
sheet.write(0, 4, "Brand", heading_xf)
|
1925 |
sheet.write(0, 4, "Brand", heading_xf)
|
| 1886 |
sheet.write(0, 5, "Category", heading_xf)
|
1926 |
sheet.write(0, 5, "Category", heading_xf)
|
| 1887 |
sheet.write(0, 6, "Product Name", heading_xf)
|
1927 |
sheet.write(0, 6, "Product Name", heading_xf)
|
| - |
|
1928 |
sheet.write(0, 7, "Selling Price", heading_xf)
|
| - |
|
1929 |
sheet.write(0, 8, "Promo Price", heading_xf)
|
| 1888 |
sheet.write(0, 7, "Reason", heading_xf)
|
1930 |
sheet.write(0, 9, "Reason", heading_xf)
|
| 1889 |
|
1931 |
|
| 1890 |
sheet_iterator = 1
|
1932 |
sheet_iterator = 1
|
| 1891 |
amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
1933 |
amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| 1892 |
for amongCheapestItem in amongCheapestItems:
|
1934 |
for amongCheapestItem in amongCheapestItems:
|
| 1893 |
amScraping = amongCheapestItem[0]
|
1935 |
amScraping = amongCheapestItem[0]
|
| Line 1903... |
Line 1945... |
| 1903 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
1945 |
sheet.write(sheet_iterator, 2, amScraping.asin)
|
| 1904 |
sheet.write(sheet_iterator, 3, loc)
|
1946 |
sheet.write(sheet_iterator, 3, loc)
|
| 1905 |
sheet.write(sheet_iterator, 4, item.brand)
|
1947 |
sheet.write(sheet_iterator, 4, item.brand)
|
| 1906 |
sheet.write(sheet_iterator, 5, getCategory(item))
|
1948 |
sheet.write(sheet_iterator, 5, getCategory(item))
|
| 1907 |
sheet.write(sheet_iterator, 6, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
1949 |
sheet.write(sheet_iterator, 6, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
| - |
|
1950 |
sheet.write(sheet_iterator, 7, amScraping.ourSellingPrice)
|
| - |
|
1951 |
sheet.write(sheet_iterator, 8, amScraping.promoPrice)
|
| 1908 |
sheet.write(sheet_iterator, 7, amScraping.reason)
|
1952 |
sheet.write(sheet_iterator, 9, amScraping.reason)
|
| 1909 |
sheet_iterator+=1
|
1953 |
sheet_iterator+=1
|
| 1910 |
|
1954 |
|
| 1911 |
|
1955 |
|
| 1912 |
if (runType=='FULL'):
|
1956 |
if (runType in ('FULL','FULL-OTHER')):
|
| 1913 |
sheet = wbk.add_sheet('Auto Favorites')
|
1957 |
sheet = wbk.add_sheet('Auto Favorites')
|
| 1914 |
|
1958 |
|
| 1915 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1959 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1916 |
|
1960 |
|
| 1917 |
excel_integer_format = '0'
|
1961 |
excel_integer_format = '0'
|
| Line 1945... |
Line 1989... |
| 1945 |
sheet_iterator+=1
|
1989 |
sheet_iterator+=1
|
| 1946 |
|
1990 |
|
| 1947 |
filename = "/tmp/amazon-report-"+runType+" " + str(timestamp) + ".xls"
|
1991 |
filename = "/tmp/amazon-report-"+runType+" " + str(timestamp) + ".xls"
|
| 1948 |
wbk.save(filename)
|
1992 |
wbk.save(filename)
|
| 1949 |
try:
|
1993 |
try:
|
| 1950 |
#EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Amazon Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
|
1994 |
EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Amazon Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
|
| 1951 |
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","amit.gupta@saholic.com","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
|
1995 |
#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","amit.gupta@saholic.com","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
|
| 1952 |
except Exception as e:
|
1996 |
except Exception as e:
|
| 1953 |
print e
|
1997 |
print e
|
| 1954 |
print "Unable to send report.Trying with local SMTP"
|
1998 |
print "Unable to send report.Trying with local SMTP"
|
| 1955 |
smtpServer = smtplib.SMTP('localhost')
|
1999 |
smtpServer = smtplib.SMTP('localhost')
|
| 1956 |
smtpServer.set_debuglevel(1)
|
2000 |
smtpServer.set_debuglevel(1)
|
| 1957 |
sender = 'build@shop2020.in'
|
2001 |
sender = 'build@shop2020.in'
|
| 1958 |
#recipients = ["kshitij.sood@saholic.com"]
|
2002 |
recipients = ["kshitij.sood@saholic.com"]
|
| 1959 |
msg = MIMEMultipart()
|
2003 |
msg = MIMEMultipart()
|
| 1960 |
msg['Subject'] = "Amazon Auto Pricing" + ' '+runType+' - ' + str(datetime.now())
|
2004 |
msg['Subject'] = "Amazon Auto Pricing" + ' '+runType+' - ' + str(datetime.now())
|
| 1961 |
msg['From'] = sender
|
2005 |
msg['From'] = sender
|
| 1962 |
recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','amit.gupta@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']
|
2006 |
#recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','amit.gupta@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']
|
| 1963 |
msg['To'] = ",".join(recipients)
|
2007 |
msg['To'] = ",".join(recipients)
|
| 1964 |
fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
|
2008 |
fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
|
| 1965 |
fileMsg.set_payload(file(filename).read())
|
2009 |
fileMsg.set_payload(file(filename).read())
|
| 1966 |
email.encoders.encode_base64(fileMsg)
|
2010 |
email.encoders.encode_base64(fileMsg)
|
| 1967 |
fileMsg.add_header('Content-Disposition','attachment;filename=amazon-auto-pricing.xls')
|
2011 |
fileMsg.add_header('Content-Disposition','attachment;filename=amazon-auto-pricing.xls')
|
| Line 1988... |
Line 2032... |
| 1988 |
vatRate = itemVatMaster.vatPercentage
|
2032 |
vatRate = itemVatMaster.vatPercentage
|
| 1989 |
return vatRate
|
2033 |
return vatRate
|
| 1990 |
|
2034 |
|
| 1991 |
def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
|
2035 |
def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
|
| 1992 |
if len(successfulAutoDecrease)==0 and len(successfulAutoIncrease)==0 :
|
2036 |
if len(successfulAutoDecrease)==0 and len(successfulAutoIncrease)==0 :
|
| 1993 |
print "returning"
|
- |
|
| 1994 |
return
|
2037 |
return
|
| 1995 |
xstr = lambda s: s or ""
|
2038 |
xstr = lambda s: s or ""
|
| 1996 |
message="""<html>
|
2039 |
message="""<html>
|
| 1997 |
<h3 style="color:red;">Test Run.Please validate with costing sheet before taking any decision</h3>
|
2040 |
<h3 style="color:red;">Test Run.Please validate with costing sheet before taking any decision</h3>
|
| 1998 |
<body>
|
2041 |
<body>
|
| Line 2100... |
Line 2143... |
| 2100 |
mailServer = smtplib.SMTP("smtp.gmail.com", 587)
|
2143 |
mailServer = smtplib.SMTP("smtp.gmail.com", 587)
|
| 2101 |
mailServer.ehlo()
|
2144 |
mailServer.ehlo()
|
| 2102 |
mailServer.starttls()
|
2145 |
mailServer.starttls()
|
| 2103 |
mailServer.ehlo()
|
2146 |
mailServer.ehlo()
|
| 2104 |
|
2147 |
|
| 2105 |
#recipients = ['kshitij.sood@saholic.com']
|
2148 |
recipients = ['kshitij.sood@saholic.com']
|
| 2106 |
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']
|
2149 |
#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']
|
| 2107 |
msg = MIMEMultipart()
|
2150 |
msg = MIMEMultipart()
|
| 2108 |
msg['Subject'] = "Amazon Auto Pricing" + ' - ' + str(datetime.now())
|
2151 |
msg['Subject'] = "Amazon Auto Pricing" + ' - ' + str(datetime.now())
|
| 2109 |
msg['From'] = ""
|
2152 |
msg['From'] = ""
|
| 2110 |
msg['To'] = ",".join(recipients)
|
2153 |
msg['To'] = ",".join(recipients)
|
| 2111 |
msg.preamble = "Amazon Auto Pricing" + ' - ' + str(datetime.now())
|
2154 |
msg.preamble = "Amazon Auto Pricing" + ' - ' + str(datetime.now())
|
| Line 2131... |
Line 2174... |
| 2131 |
global categoryMap
|
2174 |
global categoryMap
|
| 2132 |
result = session.query(Category.id,Category.display_name).all()
|
2175 |
result = session.query(Category.id,Category.display_name).all()
|
| 2133 |
for cat in result:
|
2176 |
for cat in result:
|
| 2134 |
categoryMap[cat.id] = cat.display_name
|
2177 |
categoryMap[cat.id] = cat.display_name
|
| 2135 |
|
2178 |
|
| - |
|
2179 |
def sendAlertForNegativeMargins(timestamp):
|
| - |
|
2180 |
negativeMargins = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| - |
|
2181 |
if negativeMargins[0] is None or len(negativeMargins[0])==0:
|
| - |
|
2182 |
return
|
| - |
|
2183 |
xstr = lambda s: s or ""
|
| - |
|
2184 |
message="""<html>
|
| - |
|
2185 |
<body>
|
| - |
|
2186 |
<h3 style="color:red;">Amazon FC Negative Margins</h3>
|
| - |
|
2187 |
<table border="1" style="width:100%;">
|
| - |
|
2188 |
<thead>
|
| - |
|
2189 |
<tr><th>Item Id</th>
|
| - |
|
2190 |
<th>Amazon SKU</th>
|
| - |
|
2191 |
<th>Product Name</th>
|
| - |
|
2192 |
<th>Selling Price</th>
|
| - |
|
2193 |
<th>Promo Price</th>
|
| - |
|
2194 |
<th>Subsidy</th>
|
| - |
|
2195 |
<th>Lowest Possible SP</th>
|
| - |
|
2196 |
<th>WANLC <th>
|
| - |
|
2197 |
<th>Margin</th>
|
| - |
|
2198 |
<th>Commission %</th>
|
| - |
|
2199 |
<th>Return Provision %</th>
|
| - |
|
2200 |
<th>Inventory</th>
|
| - |
|
2201 |
<th>Sales History</th>
|
| - |
|
2202 |
</tr></thead>
|
| - |
|
2203 |
<tbody>"""
|
| - |
|
2204 |
for negativeMarginItems in negativeMargins:
|
| - |
|
2205 |
amScraping = negativeMarginItems[0]
|
| - |
|
2206 |
item = negativeMarginItems[1]
|
| - |
|
2207 |
if amScraping.warehouseLocation==1:
|
| - |
|
2208 |
sku='FBA'+str(item.item_id)
|
| - |
|
2209 |
else:
|
| - |
|
2210 |
sku='FBB'+str(item.item_id)
|
| - |
|
2211 |
if amazonLongTermActivePromotions.has_key(amScraping.sku):
|
| - |
|
2212 |
subsidy = (amazonLongTermActivePromotions.get(amScraping.sku)).subsidy
|
| - |
|
2213 |
elif amazonShortTermActivePromotions.has_key(amScraping.sku):
|
| - |
|
2214 |
subsidy = (amazonShortTermActivePromotions.get(sku)).subsidy
|
| - |
|
2215 |
else:
|
| - |
|
2216 |
subsidy = 0
|
| - |
|
2217 |
message+="""<tr>
|
| - |
|
2218 |
<td style="text-align:center">"""+str(amScraping.item_id)+"""</td>
|
| - |
|
2219 |
<td style="text-align:center">"""+amScraping.sku+"""</td>
|
| - |
|
2220 |
<td style="text-align:center">"""+xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color)+"""</td>
|
| - |
|
2221 |
<td style="text-align:center">"""+str(amScraping.ourSellingPrice)+"""</td>
|
| - |
|
2222 |
<td style="text-align:center">"""+str(amScraping.promoPrice)+"""</td>
|
| - |
|
2223 |
<td style="text-align:center">"""+str(subsidy)+"""</td>
|
| - |
|
2224 |
<td style="text-align:center">"""+str(amScraping.lowestPossibleSp)+"""</td>
|
| - |
|
2225 |
<td style="text-align:center">"""+str(amScraping.wanlc)+"""</td>
|
| - |
|
2226 |
<td style="text-align:center">"""+str(round(getMargin(amScraping)))+" ("+str(round((getMargin(amScraping)/amScraping.ourSellingPrice)*100,1))+"%)"+"""</td>
|
| - |
|
2227 |
<td style="text-align:center">"""+str(amScraping.commission)+" %"+"""</td>
|
| - |
|
2228 |
<td style="text-align:center">"""+str(amScraping.returnProvision)+" %"+"""</td>
|
| - |
|
2229 |
<td style="text-align:center">"""+str(amScraping.ourInventory)+"""</td>
|
| - |
|
2230 |
<td style="text-align:center">"""+getOosString(saleMap.get(amScraping.sku))+"""</td>
|
| - |
|
2231 |
</tr>"""
|
| - |
|
2232 |
message+="""</tbody></table></body></html>"""
|
| - |
|
2233 |
mailServer = smtplib.SMTP("smtp.gmail.com", 587)
|
| - |
|
2234 |
mailServer.ehlo()
|
| - |
|
2235 |
mailServer.starttls()
|
| - |
|
2236 |
mailServer.ehlo()
|
| - |
|
2237 |
|
| - |
|
2238 |
recipients = ['kshitij.sood@saholic.com']
|
| - |
|
2239 |
#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']
|
| - |
|
2240 |
msg = MIMEMultipart()
|
| - |
|
2241 |
msg['Subject'] = "Amazon Negative Margin" + ' - ' + str(datetime.now())
|
| - |
|
2242 |
msg['From'] = ""
|
| - |
|
2243 |
msg['To'] = ",".join(recipients)
|
| - |
|
2244 |
msg.preamble = "Amazon Negative Margin" + ' - ' + str(datetime.now())
|
| - |
|
2245 |
html_msg = MIMEText(message, 'html')
|
| - |
|
2246 |
msg.attach(html_msg)
|
| - |
|
2247 |
try:
|
| - |
|
2248 |
mailServer.login("build@shop2020.in", "cafe@nes")
|
| - |
|
2249 |
#mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
|
| - |
|
2250 |
mailServer.sendmail("cafe@nes", recipients, msg.as_string())
|
| - |
|
2251 |
except Exception as e:
|
| - |
|
2252 |
print e
|
| - |
|
2253 |
print "Unable to send Amazon Negative margin mail.Lets try local SMTP"
|
| - |
|
2254 |
smtpServer = smtplib.SMTP('localhost')
|
| - |
|
2255 |
smtpServer.set_debuglevel(1)
|
| - |
|
2256 |
sender = 'build@shop2020.in'
|
| - |
|
2257 |
try:
|
| - |
|
2258 |
smtpServer.sendmail(sender, recipients, msg.as_string())
|
| - |
|
2259 |
print "Successfully sent email"
|
| - |
|
2260 |
except:
|
| - |
|
2261 |
print "Error: unable to send email."
|
| - |
|
2262 |
|
| - |
|
2263 |
def sendAlertForCantCompete(timestamp):
|
| - |
|
2264 |
cantCompeteItemsList = session.query(AmazonScrapingHistory).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
|
| - |
|
2265 |
if cantCompeteItemsList is None or len(cantCompeteItemsList)==0:
|
| - |
|
2266 |
return
|
| - |
|
2267 |
xstr = lambda s: s or ""
|
| - |
|
2268 |
message="""<html>
|
| - |
|
2269 |
<body>
|
| - |
|
2270 |
<h3 style="color:red;">Amazon FC Cant Compete Items</h3>
|
| - |
|
2271 |
<table border="1" style="width:100%;">
|
| - |
|
2272 |
<thead>
|
| - |
|
2273 |
<tr><th>Item Id</th>
|
| - |
|
2274 |
<th>Amazon SKU</th>
|
| - |
|
2275 |
<th>Product Name</th>
|
| - |
|
2276 |
<th>Selling Price</th>
|
| - |
|
2277 |
<th>Promo Price</th>
|
| - |
|
2278 |
<th>Subsidy</th>
|
| - |
|
2279 |
<th>Lowest Possible SP</th>
|
| - |
|
2280 |
<th>WANLC</th>
|
| - |
|
2281 |
<th>Margin</th>
|
| - |
|
2282 |
<th>Commission %</th>
|
| - |
|
2283 |
<th>Return Provision %</th>
|
| - |
|
2284 |
<th>Competitive Price</th>
|
| - |
|
2285 |
<th>Proposed SP<th>
|
| - |
|
2286 |
<th>Inventory</th>
|
| - |
|
2287 |
<th>Sales History</th>
|
| - |
|
2288 |
</tr></thead>
|
| - |
|
2289 |
<tbody>"""
|
| - |
|
2290 |
cantCompeteItems = sorted(cantCompeteItemsList, key=itemgetter('ourInventory'))
|
| - |
|
2291 |
for cantCompeteItem in cantCompeteItems:
|
| - |
|
2292 |
amScraping = cantCompeteItem
|
| - |
|
2293 |
item = Item.query.filter_by(id=amScraping.item_id).one()
|
| - |
|
2294 |
if amScraping.warehouseLocation==1:
|
| - |
|
2295 |
sku='FBA'+str(item.item_id)
|
| - |
|
2296 |
else:
|
| - |
|
2297 |
sku='FBB'+str(item.item_id)
|
| - |
|
2298 |
if amazonLongTermActivePromotions.has_key(amScraping.sku):
|
| - |
|
2299 |
subsidy = (amazonLongTermActivePromotions.get(amScraping.sku)).subsidy
|
| - |
|
2300 |
elif amazonShortTermActivePromotions.has_key(amScraping.sku):
|
| - |
|
2301 |
subsidy = (amazonShortTermActivePromotions.get(sku)).subsidy
|
| - |
|
2302 |
else:
|
| - |
|
2303 |
subsidy = 0
|
| - |
|
2304 |
message+="""<tr>
|
| - |
|
2305 |
<td style="text-align:center">"""+str(amScraping.item_id)+"""</td>
|
| - |
|
2306 |
<td style="text-align:center">"""+amScraping.sku+"""</td>
|
| - |
|
2307 |
<td style="text-align:center">"""+xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color)+"""</td>
|
| - |
|
2308 |
<td style="text-align:center">"""+str(amScraping.ourSellingPrice)+"""</td>
|
| - |
|
2309 |
<td style="text-align:center">"""+str(amScraping.promoPrice)+"""</td>
|
| - |
|
2310 |
<td style="text-align:center">"""+str(subsidy)+"""</td>
|
| - |
|
2311 |
<td style="text-align:center">"""+str(amScraping.lowestPossibleSp)+"""</td>
|
| - |
|
2312 |
<td style="text-align:center">"""+str(amScraping.wanlc)+"""</td>
|
| - |
|
2313 |
<td style="text-align:center">"""+str(round(getMargin(amScraping)))+" ("+str(round((getMargin(amScraping)/amScraping.ourSellingPrice)*100,1))+"%)"+"""</td>
|
| - |
|
2314 |
<td style="text-align:center">"""+str(amScraping.commission)+" %"+"""</td>
|
| - |
|
2315 |
<td style="text-align:center">"""+str(amScraping.returnProvision)+" %"+"""</td>
|
| - |
|
2316 |
<td style="text-align:center">"""+str(amScraping.competitivePrice)+" %"+"""</td>
|
| - |
|
2317 |
<td style="text-align:center">"""+str(amScraping.proposedSp)+" %"+"""</td>
|
| - |
|
2318 |
<td style="text-align:center">"""+str(amScraping.ourInventory)+"""</td>
|
| - |
|
2319 |
<td style="text-align:center">"""+getOosString(saleMap.get(amScraping.sku))+"""</td>
|
| - |
|
2320 |
</tr>"""
|
| - |
|
2321 |
message+="""</tbody></table></body></html>"""
|
| - |
|
2322 |
mailServer = smtplib.SMTP("smtp.gmail.com", 587)
|
| - |
|
2323 |
mailServer.ehlo()
|
| - |
|
2324 |
mailServer.starttls()
|
| - |
|
2325 |
mailServer.ehlo()
|
| - |
|
2326 |
|
| - |
|
2327 |
recipients = ['kshitij.sood@saholic.com']
|
| - |
|
2328 |
#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']
|
| - |
|
2329 |
msg = MIMEMultipart()
|
| - |
|
2330 |
msg['Subject'] = "Amazon Cant Compete Items" + ' - ' + str(datetime.now())
|
| - |
|
2331 |
msg['From'] = ""
|
| - |
|
2332 |
msg['To'] = ",".join(recipients)
|
| - |
|
2333 |
msg.preamble = "Amazon Cant Compete Items" + ' - ' + str(datetime.now())
|
| - |
|
2334 |
html_msg = MIMEText(message, 'html')
|
| - |
|
2335 |
msg.attach(html_msg)
|
| - |
|
2336 |
try:
|
| - |
|
2337 |
mailServer.login("build@shop2020.in", "cafe@nes")
|
| - |
|
2338 |
#mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
|
| - |
|
2339 |
mailServer.sendmail("cafe@nes", recipients, msg.as_string())
|
| - |
|
2340 |
except Exception as e:
|
| - |
|
2341 |
print e
|
| - |
|
2342 |
print "Unable to send Amazon Cant Compete Items mail.Lets try local SMTP"
|
| - |
|
2343 |
smtpServer = smtplib.SMTP('localhost')
|
| - |
|
2344 |
smtpServer.set_debuglevel(1)
|
| - |
|
2345 |
sender = 'build@shop2020.in'
|
| - |
|
2346 |
try:
|
| - |
|
2347 |
smtpServer.sendmail(sender, recipients, msg.as_string())
|
| - |
|
2348 |
print "Successfully sent email"
|
| - |
|
2349 |
except:
|
| - |
|
2350 |
print "Error: unable to send email."
|
| - |
|
2351 |
|
| 2136 |
def main():
|
2352 |
def main():
|
| 2137 |
parser = optparse.OptionParser()
|
2353 |
parser = optparse.OptionParser()
|
| 2138 |
parser.add_option("-t", "--type", dest="runType",
|
2354 |
parser.add_option("-t", "--type", dest="runType",
|
| 2139 |
default="FULL", type="string",
|
2355 |
default="FULL", type="string",
|
| 2140 |
help="Run type FULL or FAVOURITE")
|
2356 |
help="Run type FULL or FAVOURITE or FULL-OTHER")
|
| 2141 |
(options, args) = parser.parse_args()
|
2357 |
(options, args) = parser.parse_args()
|
| 2142 |
if options.runType not in ('FULL','FAVOURITE'):
|
2358 |
if options.runType not in ('FULL','FAVOURITE','FULL-OTHER'):
|
| 2143 |
print "Run type argument illegal."
|
2359 |
print "Run type argument illegal."
|
| 2144 |
sys.exit(1)
|
2360 |
sys.exit(1)
|
| 2145 |
time.sleep(5)
|
2361 |
time.sleep(5)
|
| 2146 |
timestamp = datetime.now()
|
2362 |
timestamp = datetime.now()
|
| 2147 |
generateCategoryMap()
|
2363 |
generateCategoryMap()
|
| Line 2180... |
Line 2396... |
| 2180 |
autoIncreaseItems = fetchItemsForAutoIncrease(timestamp)
|
2396 |
autoIncreaseItems = fetchItemsForAutoIncrease(timestamp)
|
| 2181 |
previousAutoFav, nowAutoFav = markAutoFavourites(timestamp)
|
2397 |
previousAutoFav, nowAutoFav = markAutoFavourites(timestamp)
|
| 2182 |
writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,options.runType)
|
2398 |
writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,options.runType)
|
| 2183 |
print "send auto pricing email"
|
2399 |
print "send auto pricing email"
|
| 2184 |
sendAutoPricingMail(autoDecreaseItems,autoIncreaseItems)
|
2400 |
sendAutoPricingMail(autoDecreaseItems,autoIncreaseItems)
|
| - |
|
2401 |
if options.runType == 'FULL-OTHER':
|
| - |
|
2402 |
sendAlertForNegativeMargins(timestamp)
|
| - |
|
2403 |
sendAlertForCantCompete(timestamp)
|
| 2185 |
if __name__=='__main__':
|
2404 |
if __name__=='__main__':
|
| 2186 |
main()
|
2405 |
main()
|