WebSVN – SmartDukaan – Diff – /trunk/PyProj/src/shop2020/model/v1/catalog/script/AmazonHistoryPopulator.py

 amScraper = AmazonAsyncScraper.Products("AKIAII3SGRXBJDPCHSGQ", "B92xTbNBTYygbGs98w01nFQUhbec1pNCkCsKVfpg", "AF6E3O0VE0X4D")
 class __AmazonItemInfo:
-    def __init__(self, asin, nlc, courierCost, sku, product_group, brand, model_name, model_number, color, weight, parent_category, risky, vatRate, runType, parent_category_name, sourcePercentage, ourInventory, state_id, otherCost, exceptionType, isNlcOverridden):
+    def __init__(self, asin, nlc, courierCost, sku, product_group, brand, model_name, model_number, color, weight, parent_category, risky, vatRate, runType, parent_category_name, sourcePercentage, ourInventory, state_id, otherCost, exceptionType, isNlcOverridden, \
+                 packagingLength, packagingWidth, packagingHeight, packagingWeight, isOversized):
         self.asin = asin
         self.nlc = nlc
         self.courierCost = courierCost
         self.sku = sku
         self.product_group = product_group
         self.ourInventory = ourInventory
         self.state_id = state_id
         self.otherCost = otherCost
         self.exceptionType = exceptionType
         self.isNlcOverridden = isNlcOverridden
+        self.packagingLength = packagingLength
+        self.packagingWidth = packagingWidth
+        self.packagingHeight = packagingHeight
+        self.packagingWeight = packagingWeight
+        self.isOversized = isOversized
 class __AmazonDetails:
     def __init__(self, sku, ourSp, ourRank, lowestSellerName,lowestSellerSp,secondLowestSellerName, secondLowestSellerSp, thirdLowestSellerName, thirdLowestSellerSp, totalSeller, multipleListings, \
                  promoPrice, isPromotion, lowestSellerShippingTime, lowestSellerRating, secondLowestSellerShippingTime, secondLowestSellerRating, thirdLowestSellerShippingTime , \
                  thirdLowestSellerRating, lowestSellerType, secondLowestSellerType, thirdLowestSellerType, lowestMfnIgnoredOffer, lowestMfnOffer, lowestFbaOffer, \
             state_id = 3
         else:
             continue
         cc = computeCourierCost(it.weight)
-        amazonItemInfo = __AmazonItemInfo(None, wanlc,cc, sku, it.product_group, it.brand, it.model_name, it.model_number, it.color, it.weight, category.parent_category_id, it.risky, None, runType, parent_category.display_name,sourcePercentage,fbaInventoryItem.availability,state_id,d_amazon_listed.otherCost,None,isNlcOverridden)
+        amazonItemInfo = __AmazonItemInfo(None, wanlc,cc, sku, it.product_group, it.brand, it.model_name, it.model_number, it.color, it.weight, category.parent_category_id, it.risky, None, runType, parent_category.display_name,sourcePercentage,fbaInventoryItem.availability,state_id,d_amazon_listed.otherCost,None,isNlcOverridden, \
+                                          d_amazon_listed.packagingLength, d_amazon_listed.packagingWidth, d_amazon_listed.packagingHeight, d_amazon_listed.packagingWeight, checkOverSize(d_amazon_listed))
         itemInfo.append(amazonItemInfo)
     #amPromotions = AmazonPromotion.query.filter(AmazonPromotion.startDate<=time).filter(AmazonPromotion.endDate>=time).filter(AmazonPromotion.promotionType==AmazonPromotionType.LONGTERM).filter(AmazonPromotion.promotionActive==True) \
     #.group_by(AmazonPromotion.sku).order_by(desc(AmazonPromotion.addedOn)).all()
     amPromotions = AmazonPromotion.query.filter(AmazonPromotion.startDate<=time).filter(AmazonPromotion.endDate>=time).filter(AmazonPromotion.promotionType==AmazonPromotionType.LONGTERM).filter(AmazonPromotion.promotionActive==True) \
     .order_by(desc(AmazonPromotion.addedOn)).all()
             amazonScrapingHistory.timestamp = timestamp
             amazonScrapingHistory.multipleListings = amDetails.multipleListings
             amazonScrapingHistory.isPromotion = val.isPromo
         amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
         amazonScrapingHistory.timestamp = timestamp
+        amazonScrapingHistory.packagingHeight = val.packagingHeight
+        amazonScrapingHistory.packagingLength = val.packagingLength
+        amazonScrapingHistory.packagingWidth = val.packagingWidth
+        amazonScrapingHistory.packagingWeight = val.packagingWeight
+        amazonScrapingHistory.isOversized = val.isOversized
     session.commit()
 def commitNegativeMargin(negativeMargin,timestamp,runType):
     for negativeMarginItem in negativeMargin:
         val = negativeMarginItem[0]
         amazonScrapingHistory.competitiveCategory = CompetitionCategory.NEGATIVE_MARGIN
         amazonScrapingHistory.timestamp = timestamp
         amazonScrapingHistory.multipleListings = amDetails.multipleListings
         amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
         amazonScrapingHistory.isPromotion = val.isPromo
+        amazonScrapingHistory.packagingHeight = val.packagingHeight
+        amazonScrapingHistory.packagingLength = val.packagingLength
+        amazonScrapingHistory.packagingWidth = val.packagingWidth
+        amazonScrapingHistory.packagingWeight = val.packagingWeight
+        amazonScrapingHistory.isOversized = val.isOversized
     session.commit()
 def commitCheapest(cheapest,timestamp,runType):
     for cheapestItem in cheapest:
         #amazonScrapingHistory.proposedTp = proposed_tp
         #amazonScrapingHistory.marginIncreasedPotential = proposed_tp - amPricing.ourTp
         amazonScrapingHistory.multipleListings = amDetails.multipleListings
         amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
         amazonScrapingHistory.isPromotion = val.isPromo
+        amazonScrapingHistory.packagingHeight = val.packagingHeight
+        amazonScrapingHistory.packagingLength = val.packagingLength
+        amazonScrapingHistory.packagingWidth = val.packagingWidth
+        amazonScrapingHistory.packagingWeight = val.packagingWeight
+        amazonScrapingHistory.isOversized = val.isOversized
     session.commit()
 def commitAmongCheapestAndCanCompete(amongCheapestAndCanCompete,timestamp,runType):
         amazonScrapingHistory.proposedSp = proposed_sp
         #amazonScrapingHistory.proposedTp = proposed_tp
         amazonScrapingHistory.multipleListings = amDetails.multipleListings
         amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
         amazonScrapingHistory.isPromotion = val.isPromo
+        amazonScrapingHistory.packagingHeight = val.packagingHeight
+        amazonScrapingHistory.packagingLength = val.packagingLength
+        amazonScrapingHistory.packagingWidth = val.packagingWidth
+        amazonScrapingHistory.packagingWeight = val.packagingWeight
+        amazonScrapingHistory.isOversized = val.isOversized
     session.commit()
 def commitCanCompete(canCompete,timestamp,runType):
     for canCompeteItem in canCompete:
         val = canCompeteItem[0]
         amazonScrapingHistory.proposedSp = proposed_sp
         #amazonScrapingHistory.proposedTp = proposed_tp
         amazonScrapingHistory.multipleListings = amDetails.multipleListings
         amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
         amazonScrapingHistory.isPromotion = val.isPromo
+        amazonScrapingHistory.packagingHeight = val.packagingHeight
+        amazonScrapingHistory.packagingLength = val.packagingLength
+        amazonScrapingHistory.packagingWidth = val.packagingWidth
+        amazonScrapingHistory.packagingWeight = val.packagingWeight
+        amazonScrapingHistory.isOversized = val.isOversized
     session.commit()
 def commitAlmostCompete(almostCompete,timestamp,runType):
     for almostCompeteItem in almostCompete:
         val = almostCompeteItem[0]
         #amazonScrapingHistory.proposedTp = proposed_tp
         #amazonScrapingHistory.targetNlc = target_nlc
         amazonScrapingHistory.multipleListings = amDetails.multipleListings
         amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
         amazonScrapingHistory.isPromotion = val.isPromo
+        amazonScrapingHistory.packagingHeight = val.packagingHeight
+        amazonScrapingHistory.packagingLength = val.packagingLength
+        amazonScrapingHistory.packagingWidth = val.packagingWidth
+        amazonScrapingHistory.packagingWeight = val.packagingWeight
+        amazonScrapingHistory.isOversized = val.isOversized
     session.commit()
 def commitCantCompete(cantCompete, timestamp,runType):
     for cantCompeteItem in cantCompete:
         #amazonScrapingHistory.proposedTp = proposed_tp
         #amazonScrapingHistory.targetNlc = target_nlc
         amazonScrapingHistory.multipleListings = amDetails.multipleListings
         amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
         amazonScrapingHistory.isPromotion = val.isPromo
+        amazonScrapingHistory.packagingHeight = val.packagingHeight
+        amazonScrapingHistory.packagingLength = val.packagingLength
+        amazonScrapingHistory.packagingWidth = val.packagingWidth
+        amazonScrapingHistory.packagingWeight = val.packagingWeight
+        amazonScrapingHistory.isOversized = val.isOversized
     session.commit()
 def markAutoFavourites(time):
     nowAutoFav = []
     previouslyAutoFav = []
     sheet.write(0, 54, "Decision", heading_xf)
     sheet.write(0, 55, "Reason", heading_xf)
     sheet.write(0, 56, "Updated Price", heading_xf)
     sheet.write(0, 57, "Proposed Margin", heading_xf)
     sheet.write(0, 58, "Inventory Movement Status", heading_xf)
+    sheet.write(0, 59, "Packaging Length", heading_xf)
+    sheet.write(0, 60, "Packaging Width", heading_xf)
+    sheet.write(0, 61, "Packaging Height", heading_xf)
+    sheet.write(0, 62, "Packaging Weight", heading_xf)
+    sheet.write(0, 63, "Oversized Item", heading_xf)
 def getPackagingCost(data):
     #TODO : Get packagingCost from marketplaceitems table
     return 15
         sheet.write(sheet_iterator, 52, toDaysSale[0])
         sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
         #sheet.write(sheet_iterator, 44, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))
         sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
+        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
+        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
+        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
+        sheet.write(sheet_iterator, 63, amScraping.isOversized)
         sheet_iterator+=1
     #TODO : Take excell sheet generation code inside a function
     competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
     for competitiveItem in competitiveItems:
         amScraping =  competitiveItem[0]
             sheet.write(sheet_iterator, 56, math.ceil(amScraping.proposedSp))
         if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
             sheet.write(sheet_iterator, 56, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
         sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
         sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
+        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
+        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
+        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
+        sheet.write(sheet_iterator, 63, amScraping.isOversized)
         sheet_iterator+=1
     almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
     for almostCompetitiveItem in almostCompetitiveItems:
         amScraping =  almostCompetitiveItem[0]
             sheet.write(sheet_iterator, 56, math.ceil(amScraping.proposedSp))
         if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
             sheet.write(sheet_iterator, 56, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
         sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
         sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
+        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
+        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
+        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
+        sheet.write(sheet_iterator, 63, amScraping.isOversized)
         sheet_iterator+=1
     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()
     for amongCheapestItem in amongCheapestItems:
         amScraping =  amongCheapestItem[0]
             sheet.write(sheet_iterator, 56, math.ceil(amScraping.proposedSp))
         if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
             sheet.write(sheet_iterator, 56, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
         sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
         sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
+        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
+        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
+        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
+        sheet.write(sheet_iterator, 63, amScraping.isOversized)
         sheet_iterator+=1
 #    sheet = wbk.add_sheet('Cheapest')
 #    xstr = lambda s: s or ""
             sheet.write(sheet_iterator, 56, math.ceil(amScraping.proposedSp))
         if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
             sheet.write(sheet_iterator, 56, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
         sheet.write(sheet_iterator, 57, getNewMargin(amScraping,math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice))))
         sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
+        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
+        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
+        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
+        sheet.write(sheet_iterator, 63, amScraping.isOversized)
         sheet_iterator+=1
     exceptionCheapItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp)\
     .filter(or_(AmazonScrapingHistory.exceptionType==3,AmazonScrapingHistory.exceptionType==5)).all()
         if amScraping.exceptionType==3:
             sheet.write(sheet_iterator, 55, 'We are the only seller')
         else:
             sheet.write(sheet_iterator, 55, amScraping.reason)
         sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
+        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
+        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
+        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
+        sheet.write(sheet_iterator, 63, amScraping.isOversized)
         sheet_iterator+=1
 #    sheet = wbk.add_sheet('Negative Margin')
 #    xstr = lambda s: s or ""
         sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[2], inStockStats[1]))
         toDaysSale = getTodaysSale(sku)
         sheet.write(sheet_iterator, 52, toDaysSale[0])
         sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
         sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
+        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
+        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
+        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
+        sheet.write(sheet_iterator, 63, amScraping.isOversized)
         sheet_iterator+=1
     sheet = wbk.add_sheet('Exception List')
     xstr = lambda s: s or ""
     heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
     sheet.write(0, 21, "Avg Sale Since InStock (Excluding Today)", heading_xf)
     sheet.write(0, 22, "Todays Sale Snapshot Time", heading_xf)
     sheet.write(0, 23, "Todays Sale", heading_xf)
     sheet.write(0, 24, "Inventory Movement Status", heading_xf)
     sheet.write(0, 25, "Reason", heading_xf)
+    sheet.write(0, 26, "Packaging Length", heading_xf)
+    sheet.write(0, 27, "Packaging Width", heading_xf)
+    sheet.write(0, 28, "Packaging Height", heading_xf)
+    sheet.write(0, 29, "Packaging Weight", heading_xf)
+    sheet.write(0, 30, "Oversized Item", heading_xf)
     sheet_iterator = 1
     exceptionListItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp).all()
     for exceptionListItem in exceptionListItems:
         amScraping =  exceptionListItem[0]
         toDaysSale = getTodaysSale(sku)
         sheet.write(sheet_iterator, 22, toDaysSale[0])
         sheet.write(sheet_iterator, 23, str(toDaysSale[1]))
         sheet.write(sheet_iterator, 24, getInventoryMovementStatus(amScraping))
         sheet.write(sheet_iterator, 25, amScraping.reason)
+        sheet.write(sheet_iterator, 26, amScraping.packagingLength)
+        sheet.write(sheet_iterator, 27, amScraping.packagingWidth)
+        sheet.write(sheet_iterator, 28, amScraping.packagingHeight)
+        sheet.write(sheet_iterator, 29, amScraping.packagingWeight)
+        sheet.write(sheet_iterator, 30, amScraping.isOversized)
         sheet_iterator+=1
     if (runType in ('FULL','FULL-OTHER')):
         sheet = wbk.add_sheet('Auto Favorites')
             am_listed.isFbg = True
         else:
             pass
     session.commit()
+def checkOverSize(d_amazon_listed):
+    try:
+        if d_amazon_listed.packagingWeight * .453592 > 12 or d_amazon_listed.packagingLength > 20 or d_amazon_listed.packagingWidth > 16 or d_amazon_listed.packagingHeight > 10:
+            return True
+        else:
+            return False
+    except:
+        return False
 def main():
     parser = optparse.OptionParser()
     parser.add_option("-t", "--type", dest="runType",
                    default="FULL", type="string",
                    help="Run type FULL or FAVOURITE or FULL-OTHER")

Subversion Repositories SmartDukaan

(root)/trunk/PyProj/src/shop2020/model/v1/catalog/script/AmazonHistoryPopulator.py – Rev 15665 → 15695