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

     amHistory.reason = reason
 def calculateAverageSale(sku):
     count,sale = 0,0
     oosStatus = saleMap.get(sku)
+    if oosStatus is None:
+        return 0.0
     for obj in oosStatus:
         if not obj.isOutOfStock:
             count+=1
             sale = sale+obj.totalOrderCount
     avgSalePerDay=0 if count==0 else (float(sale)/count)
     return round(avgSalePerDay,2)
 def getOosString(oosStatus):
+    if oosStatus is None:
+        return ""
     lastNdaySale=""
     for obj in oosStatus:
         if obj.isOutOfStock:
             lastNdaySale += "X-"
         else:
     else:
         return fbaSaleSnapshot.totalOrderCount
 def getNoOfDaysInStock(oosStatus):
     inStockCount = 0
+    if oosStatus is None:
+        return 0
     for obj in oosStatus:
         if not obj.isOutOfStock:
             inStockCount+=1
     return inStockCount
         if wpiTodayExpiry.has_key(amPromotion.sku):
             continue
         wpiTodayExpiry[amPromotion.sku] = __Promotion(amPromotion.salePrice,amPromotion.subsidy,amPromotion.promotionType,amPromotion.endDate)
     session.close()
     print "No of items populated ",len(itemInfo)
-    sleep(5)
     return itemInfo
 def getPriceAndAsin(itemInfo):
     skus = []
     for item in itemInfo:
             else:
                 print "No other seller or Unable to fetch competitive pricing"
                 val.exceptionType = 3
                 #temp.append("No other seller or Unable to fetch competitive pricing")
             temp.append(val)
+            if val.exceptionType ==3:
+                val.ourSp = ourPricingForSku.get(val.sku).get('sellingPrice')
+                val.promoPrice = ourPricingForSku.get(val.sku).get('promoPrice')
+                val.isPromo = ourPricingForSku.get(val.sku).get('promotion')
+                sku = val.sku
+                try:
+                    val.vatRate = getVatRate(val.sku[3:],val.state_id,val.promoPrice)
+                except:
+                    val.exceptionType = 6
+                if (ourPricingForSku.get(val.sku).get('promotion')!=(amazonLongTermActivePromotions.has_key(val.sku) or amazonShortTermActivePromotions.has_key(val.sku))):
+                    val.exceptionType = 4
+                if val.exceptionType ==3:
+                    lowestPossibleSp = getLowestPossibleSp(None,val,val.sourcePercentage)
+                    amPricing = __AmazonPricing(val.ourSp,lowestPossibleSp)
+                    amDetails = __AmazonDetails(sku, float(val.ourSp), 1, '',float(val.ourSp),'', float(0), '', float(0),1,False,val.promoPrice,val.isPromo, \
+                    '0-0' ,0, '0-0', 0, '0-0' , 0, 'Amazon', '', '', \
+,0,0,False,False,True,None)
+                    temp.append(amDetails)
+                    temp.append(amPricing)
             exceptionList.append(temp)
             continue
         val.ourSp = ourPricingForSku.get(val.sku).get('sellingPrice')
         val.promoPrice = ourPricingForSku.get(val.sku).get('promoPrice')
         val.isPromo = ourPricingForSku.get(val.sku).get('promotion')
                     lowestSellerShippingTime ,lowestSellerRating, secondLowestSellerShippingTime, secondLowestSellerRating, thirdLowestSellerShippingTime , thirdLowestSellerRating, lowestSellerType, secondLowestSellerType, thirdLowestSellerType, \
                     competitvePricingInfo['lowestMfnIgnored'],competitvePricingInfo['lowestMfn'],competitvePricingInfo['lowestFba'],competitvePricingInfo['isLowestMfnIgnored'],competitvePricingInfo['isLowestMfn'],competitvePricingInfo['isLowestFba'],None)
         competitivePrice = decideCompetitvePricing(amDetails,val.ourInventory,timestamp)
         amDetails.competitivePrice = competitivePrice
+        try:
-        if amDetails.competitivePrice==0.0 and amDetails.ourRank > 1:
+            val.vatRate = getVatRate(val.sku[3:], val.state_id, amDetails.promoPrice)
+        except:
             temp = []
-            val.exceptionType = 5
+            val.exceptionType = 6
             temp.append(val)
-            temp.append("Unable to calculate competitive pricing")
             exceptionList.append(temp)
             continue
-        try:
-            print "inside val getter"
-            itemVatMaster = ItemVatMaster.query.filter(and_(ItemVatMaster.itemId==int(val.sku[3:]), ItemVatMaster.stateId==val.state_id)).first()
-            if itemVatMaster is None:
-                d_item = Item.query.filter_by(id=int(val.sku[3:])).first()
-                if d_item is None:
-                    raise
-                else:
-                    vatMaster = CategoryVatMaster.query.filter(and_(CategoryVatMaster.categoryId==d_item.category, CategoryVatMaster.minVal<=amDetails.promoPrice,  CategoryVatMaster.maxVal>=amDetails.promoPrice,  CategoryVatMaster.stateId == val.state_id)).first()
+        if amDetails.competitivePrice==0.0 and amDetails.ourRank > 1:
-                if vatMaster is None:
-                    raise
-                else:
-                    val.vatRate = vatMaster.vatPercent
-                    print "vat fetched"
-            else:
-                val.vatRate = itemVatMaster.vatPercentage
-                print "vat fetched"
-        except:
-            print "vat exception"
             temp = []
-            val.exceptionType = 6
+            val.exceptionType = 5
             temp.append(val)
-            temp.append("Vat not available")
+            temp.append(amDetails)
+            lowestPossibleSp = getLowestPossibleSp(amDetails,val,val.sourcePercentage)
+            amPricing = __AmazonPricing(ourSp,lowestPossibleSp)
+            temp.append(amPricing)
             exceptionList.append(temp)
             continue
         lowestPossibleSp = getLowestPossibleSp(amDetails,val,val.sourcePercentage)
         print "Creating pricing obj"
 def commitExceptionList(exceptionList,timestamp,runType):
     for exceptionItem in exceptionList:
         val = exceptionItem[0]
         amazonScrapingHistory = AmazonScrapingHistory()
         amazonScrapingHistory.item_id = val.sku[3:]
+        amazonScrapingHistory.asin = val.asin
         amazonScrapingHistory.warehouseLocation = val.state_id
         amazonScrapingHistory.parentCategoryId = val.parent_category
         amazonScrapingHistory.ourSellingPrice = val.ourSp
         amazonScrapingHistory.promoPrice = val.promoPrice
         amazonScrapingHistory.reason = exceptionMap.get(val.exceptionType)
         amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)
         amazonScrapingHistory.competitiveCategory = CompetitionCategory.EXCEPTION
+        amazonScrapingHistory.exceptionType = val.exceptionType
+        amazonScrapingHistory.ourInventory = val.ourInventory
+        if val.exceptionType in (3,5):
+            amDetails = exceptionItem[1]
+            amPricing = exceptionItem[2]
+            if amazonLongTermActivePromotions.has_key(val.sku):
+                subsidy = (amazonLongTermActivePromotions.get(val.sku)).subsidy
+            elif amazonShortTermActivePromotions.has_key(val.sku):
+                subsidy = (amazonShortTermActivePromotions.get(val.sku)).subsidy
+            else:
+                subsidy = 0
+            spm = val.sourcePercentage
+            amazonScrapingHistory.subsidy = subsidy
+            amazonScrapingHistory.vatRate = val.vatRate
+            amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
+            amazonScrapingHistory.ourRank = amDetails.ourRank
+            amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
+            amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime
+            amazonScrapingHistory.lowestSellerRating = amDetails.lowestSellerRating
+            amazonScrapingHistory.lowestSellerType = amDetails.lowestSellerType
+            amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp
+            amazonScrapingHistory.secondLowestSellerShippingTime = amDetails.secondLowestSellerShippingTime
+            amazonScrapingHistory.secondLowestSellerRating = amDetails.secondLowestSellerRating
+            amazonScrapingHistory.secondLowestSellerType = amDetails.secondLowestSellerType
+            amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp
+            amazonScrapingHistory.thirdLowestSellerShippingTime = amDetails.thirdLowestSellerShippingTime
+            amazonScrapingHistory.thirdLowestSellerRating = amDetails.thirdLowestSellerRating
+            amazonScrapingHistory.thirdLowestSellerType = amDetails.thirdLowestSellerType
+            if (amDetails.lowestMfnOffer < amDetails.lowestFbaOffer or amDetails.lowestMfnOffer < amazonScrapingHistory.promoPrice) and amDetails.isLowestMfn:
+                amazonScrapingHistory.cheapestMfnCount = True
+            else:
+                amazonScrapingHistory.cheapestMfnCount = False
+            amazonScrapingHistory.wanlc = val.nlc
+            amazonScrapingHistory.otherCost = val.otherCost
+            amazonScrapingHistory.commission = spm.commission
+            amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther
+            amazonScrapingHistory.returnProvision = spm.returnProvision
+            amazonScrapingHistory.isLowestMfnIgnored = amDetails.isLowestMfnIgnored
+            amazonScrapingHistory.isLowestMfn = amDetails.isLowestMfn
+            amazonScrapingHistory.isLowestFba = amDetails.isLowestFba
+            amazonScrapingHistory.lowestMfnIgnoredOffer =amDetails.lowestMfnIgnoredOffer
+            amazonScrapingHistory.lowestMfnOffer = amDetails.lowestMfnOffer
+            amazonScrapingHistory.lowestFbaOffer = amDetails.lowestFbaOffer
+            amazonScrapingHistory.courierCost = val.courierCost
+            amazonScrapingHistory.risky = val.risky
+            amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)
+            amazonScrapingHistory.totalSeller = amDetails.totalSeller
+            amazonScrapingHistory.timestamp = timestamp
+            amazonScrapingHistory.multipleListings = amDetails.multipleListings
+            amazonScrapingHistory.isPromotion = val.isPromo
+        amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
         amazonScrapingHistory.timestamp = timestamp
     session.commit()
 def commitNegativeMargin(negativeMargin,timestamp,runType):
     for negativeMarginItem in negativeMargin:
 def markAutoFavourites(time):
     nowAutoFav = []
     previouslyAutoFav = []
     stockList = []
     saleList = []
-    items = session.query(func.sum(AmazonScrapingHistory.ourInventory),AmazonScrapingHistory.item_id).group_by(AmazonScrapingHistory.item_id).all()
+    items = session.query(func.sum(AmazonScrapingHistory.ourInventory),AmazonScrapingHistory.item_id).filter(AmazonScrapingHistory.timestamp==time).group_by(AmazonScrapingHistory.item_id).all()
     allItems = session.query(Amazonlisted).all()
     for item in items:
         reason = ""
         if item[0]>=5:
             stockList.append(item[1])
     sheet.write(0, 41, "Sales History", heading_xf)
     sheet.write(0, 42, "Decision", heading_xf)
     sheet.write(0, 43, "Reason", heading_xf)
     sheet.write(0, 44, "Updated Price", heading_xf)
     sheet.write(0, 45, "Proposed Margin", heading_xf)
-    sheet.write(0, 46, "Inventory Movement Status")
+    sheet.write(0, 46, "Inventory Movement Status", heading_xf)
 def getPackagingCost(data):
     #TODO : Get packagingCost from marketplaceitems table
     return 15
     margin = amScraping.promoPrice - myCosts + amScraping.subsidy
     print 'margin for ',amScraping.item_id,' is ',margin
     return round(margin)
 def getNewMargin(amScraping,proposedSp):
-    myCosts = amScraping.wanlc + getCourierCost(amScraping) + getClosingFee(amScraping) + getNewCommission(amScraping,proposedSp) + getPackagingCost(amScraping) + getNewVat(amScraping,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,proposedSp),proposedSp) + getNewReturnCost(amScraping,proposedSp) + amScraping.otherCost
+    myCosts = amScraping.wanlc + getCourierCost(amScraping) + getClosingFee(amScraping) + getNewCommission(amScraping,proposedSp) + getPackagingCost(amScraping) + getNewVat(amScraping,getVatRate(amScraping.item_id,amScraping.warehouseLocation,proposedSp),proposedSp) + getNewReturnCost(amScraping,proposedSp) + amScraping.otherCost
     margin = proposedSp - myCosts + amScraping.subsidy
     print 'New margin for ',amScraping.item_id,' is ',margin
     return round(margin)
 def getVat(data):
             sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
         sheet.write(sheet_iterator, 45, getNewMargin(amScraping,math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice))))
         sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))
         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()
+    for exceptionCheapItem in exceptionCheapItems:
+        amScraping =  exceptionCheapItem[0]
+        item = exceptionCheapItem[1]
+        sheet.write(sheet_iterator, 0, amScraping.item_id)
+        if amScraping.warehouseLocation == 1:
+            sku = 'FBA'+str(amScraping.item_id)
+            loc = 'MUMBAI'
+        else:
+            sku = 'FBB'+str(amScraping.item_id)
+            loc = 'BANGLORE'
+        sheet.write(sheet_iterator, 1, sku)
+        sheet.write(sheet_iterator, 2, amScraping.asin)
+        sheet.write(sheet_iterator, 3, loc)
+        sheet.write(sheet_iterator, 4, item.brand)
+        sheet.write(sheet_iterator, 5, getCategory(item))
+        sheet.write(sheet_iterator, 6, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
+        sheet.write(sheet_iterator, 7, item.weight)
+        sheet.write(sheet_iterator, 8, amScraping.courierCost)
+        sheet.write(sheet_iterator, 9, amScraping.ourSellingPrice)
+        sheet.write(sheet_iterator, 10, amScraping.promoPrice)
+        if amScraping.isPromotion:
+            sheet.write(sheet_iterator, 11, "Yes")
+        else:
+            sheet.write(sheet_iterator, 11, "No")
+        sheet.write(sheet_iterator, 12, amScraping.lowestPossibleSp)
+        if amScraping.ourRank > 3:
+            sheet.write(sheet_iterator, 13, 'Greater than 3')
+        else:
+            sheet.write(sheet_iterator, 13, amScraping.ourRank)
+        sheet.write(sheet_iterator, 14, 'Cheapest')
+        sheet.write(sheet_iterator, 15, amScraping.ourInventory)
+        sheet.write(sheet_iterator, 16, amScraping.lowestSellerSp)
+        sheet.write(sheet_iterator, 17, amScraping.lowestSellerRating)
+        sheet.write(sheet_iterator, 18, amScraping.lowestSellerShippingTime)
+        sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerSp)
+        sheet.write(sheet_iterator, 20, amScraping.secondLowestSellerRating)
+        sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerShippingTime)
+        sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerSp)
+        sheet.write(sheet_iterator, 23, amScraping.thirdLowestSellerRating)
+        sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerShippingTime)
+        sheet.write(sheet_iterator, 25, amScraping.lowestMfnIgnoredOffer)
+        sheet.write(sheet_iterator, 26, amScraping.lowestMfnOffer)
+        sheet.write(sheet_iterator, 27, amScraping.lowestFbaOffer)
+        sheet.write(sheet_iterator, 28, amScraping.competitivePrice)
+        sheet.write(sheet_iterator, 29, amScraping.otherCost)
+        sheet.write(sheet_iterator, 30, amScraping.wanlc)
+        sheet.write(sheet_iterator, 31, amScraping.subsidy)
+        if amScraping.isPromotion:
+            sheet.write(sheet_iterator, 32, getMsp(amScraping.item_id,amScraping.warehouseLocation))
+        else:
+            sheet.write(sheet_iterator, 32, 0.0)
+        sheet.write(sheet_iterator, 33, amScraping.commission)
+        sheet.write(sheet_iterator, 34, amScraping.competitorCommission)
+        sheet.write(sheet_iterator, 35, amScraping.returnProvision)
+        sheet.write(sheet_iterator, 36, amScraping.vatRate)
+        sheet.write(sheet_iterator, 37, getMargin(amScraping))
+        sheet.write(sheet_iterator, 38, amScraping.proposedSp)
+        sheet.write(sheet_iterator, 39, amScraping.avgSale)
+        try:
+            daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
+        except:
+            daysOfStock = float("inf")
+        if str(daysOfStock)=='inf':
+            sheet.write(sheet_iterator, 40, str(daysOfStock))
+        else:
+            sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))
+        sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))
+        sheet.write(sheet_iterator, 42, 'AUTO_INCREMENT_FAILED')
+        if amScraping.exceptionType==3:
+            sheet.write(sheet_iterator, 43, 'We are the only seller')
+        else:
+            sheet.write(sheet_iterator, 43, amScraping.reason)
+        sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))
+        sheet_iterator+=1
 #    sheet = wbk.add_sheet('Negative Margin')
 #    xstr = lambda s: s or ""
 #    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
+#
 #    excel_integer_format = '0'
     sheet.write(0, 4, "Brand", heading_xf)
     sheet.write(0, 5, "Category", heading_xf)
     sheet.write(0, 6, "Product Name", heading_xf)
     sheet.write(0, 7, "Selling Price", heading_xf)
     sheet.write(0, 8, "Promo Price", heading_xf)
+    sheet.write(0, 9, "Inventory", heading_xf)
+    sheet.write(0, 10, "Avg Sale", heading_xf)
+    sheet.write(0, 11, "NOD", heading_xf)
+    sheet.write(0, 12, "Sales History", heading_xf)
+    sheet.write(0, 13, "Inventory Movement Status", heading_xf)
-    sheet.write(0, 9, "Reason", heading_xf)
+    sheet.write(0, 14, "Reason", heading_xf)
     sheet_iterator = 1
-    amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp).all()
+    exceptionListItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp).all()
-    for amongCheapestItem in amongCheapestItems:
+    for exceptionListItem in exceptionListItems:
-        amScraping =  amongCheapestItem[0]
+        amScraping =  exceptionListItem[0]
+        if amScraping.exceptionType in (3,5):
+            continue
-        item = amongCheapestItem[1]
+        item = exceptionListItem[1]
         sheet.write(sheet_iterator, 0, amScraping.item_id)
         if amScraping.warehouseLocation == 1:
             sku = 'FBA'+str(amScraping.item_id)
             loc = 'MUMBAI'
         else:
         sheet.write(sheet_iterator, 4, item.brand)
         sheet.write(sheet_iterator, 5, getCategory(item))
         sheet.write(sheet_iterator, 6, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
         sheet.write(sheet_iterator, 7, amScraping.ourSellingPrice)
         sheet.write(sheet_iterator, 8, amScraping.promoPrice)
+        sheet.write(sheet_iterator, 9, amScraping.ourInventory)
+        sheet.write(sheet_iterator, 10, amScraping.avgSale)
+        try:
+            daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
+        except:
+            daysOfStock = float("inf")
+        if str(daysOfStock)=='inf':
+            sheet.write(sheet_iterator, 11, str(daysOfStock))
+        else:
+            sheet.write(sheet_iterator, 11, str(round(daysOfStock,1)))
+        sheet.write(sheet_iterator, 12, getOosString(saleMap.get(sku)))
+        sheet.write(sheet_iterator, 13, getInventoryMovementStatus(amScraping))
-        sheet.write(sheet_iterator, 9, amScraping.reason)
+        sheet.write(sheet_iterator, 14, amScraping.reason)
         sheet_iterator+=1
     if (runType in ('FULL','FULL-OTHER')):
         sheet = wbk.add_sheet('Auto Favorites')
             smtpServer.sendmail(sender, recipients, msg.as_string())
             print "Successfully sent email"
         except:
             print "Error: unable to send email."
-def getNewVatRate(item_id,state,price):
+def getVatRate(item_id,state,price):
     itemVatMaster = ItemVatMaster.query.filter(and_(ItemVatMaster.itemId==item_id, ItemVatMaster.stateId==state)).first()
     if itemVatMaster is None:
         d_item = Item.query.filter_by(id=item_id).first()
         if d_item is None:
             raise

Subversion Repositories SmartDukaan

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