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

 from shop2020.config.client.ConfigClient import ConfigClient
 from shop2020.model.v1.catalog.impl import DataService
 from shop2020.model.v1.catalog.impl.DataService import Amazonlisted, Item, \
 Category, SourcePercentageMaster,SourceCategoryPercentage, SourceItemPercentage, AmazonPromotion, AmazonScrapingHistory, \
 ItemVatMaster, CategoryVatMaster
-from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
+from shop2020.thriftpy.model.v1.order.ttypes import OrderSource, AmazonFCWarehouseLocation
 from shop2020.thriftpy.model.v1.catalog.ttypes import CompetitionCategory, \
 Decision, RunType, AmazonPromotionType
 from shop2020.model.v1.catalog.script import AmazonAsyncScraper
+from shop2020.model.v1.order.script import MongoService
 from shop2020.clients.InventoryClient import InventoryClient
-from shop2020.clients.TransactionClient import TransactionClient
+from shop2020.utils.Utils import to_py_date
 import time
 from time import sleep
 from datetime import date, datetime, timedelta
 import math
 import simplejson as json
 amazonLongTermActivePromotions = {}
 amazonShortTermActivePromotions = {}
 wpiTodayExpiry = {}
 notListed = []
 saleMap = {}
+monthlySaleMap = {}
 categoryMap = {}
+latestHourlySnapshot = {}
 exceptionMap = {1:'WANLC is 0',2:'Unable to fetch our price',3:'No other seller or Unable to fetch competitive pricing',
 :'Promo misconfigured',5:'Unable to calculate competitive pricing',6:'Vat not available'}
 DataService.initialize(db_hostname=host)
 amScraper = AmazonAsyncScraper.Products("AKIAII3SGRXBJDPCHSGQ", "B92xTbNBTYygbGs98w01nFQUhbec1pNCkCsKVfpg", "AF6E3O0VE0X4D")
 def fetchItemsForAutoIncrease(time):
     successfulAutoIncrease = []
     autoIncrementItems = session.query(AmazonScrapingHistory).join((Amazonlisted,AmazonScrapingHistory.item_id==Amazonlisted.itemId))\
     .filter(AmazonScrapingHistory.timestamp==time).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX)\
     .filter(Amazonlisted.autoIncrement==True).all()
-    transaction_client = TransactionClient().get_client()
     print len(autoIncrementItems)
     for autoIncrementItem in autoIncrementItems:
         if autoIncrementItem.warehouseLocation == 1:
             sku = 'FBA'+str(autoIncrementItem.item_id)
         elif autoIncrementItem.warehouseLocation == 2:
             fcLocation = 1
         elif autoIncrementItem.warehouseLocation==3:
             fcLocation = 2
         else:
             pass
-        fbaSaleSnapshot = transaction_client.getAmazonFbaSalesLatestSnapshotForItemLocationWise(autoIncrementItem.item_id,fcLocation)
+        fbaSaleSnapshot = MongoService.getAmazonFbaSalesLatestSnapshotForItemLocationWise(autoIncrementItem.item_id,fcLocation)
         if getLastDaySale(fbaSaleSnapshot)<=2:
             markReasonForItem(autoIncrementItem,'Last day sale is less than 3',Decision.AUTO_INCREMENT_FAILED)
             continue
         autoIncrementItem.ourEnoughStock = False
         if not obj.isOutOfStock:
             count+=1
             sale = sale+obj.totalOrderCount
     avgSalePerDay=0 if count==0 else (float(sale)/count)
     return round(avgSalePerDay,2)
+def get30DaysStats(sku):
+    count,sale = 0,0
+    oosStatus = monthlySaleMap.get(sku)
+    if oosStatus is None:
+        return 0,0
+    for obj in oosStatus:
+        if not obj.isOutOfStock:
+            count+=1
+            sale = sale+obj.totalOrderCount
+    return sale, count
+def getInStockStats(item_id, warehouseLocation):
+    if warehouseLocation==1:
+        fcLocation = 0
+    elif warehouseLocation==2:
+        fcLocation = 1
+    elif warehouseLocation==3:
+        fcLocation = 2
+    return MongoService.getLastOutOfStock(item_id, fcLocation)
 def getOosString(oosStatus):
     if oosStatus is None:
         return ""
 ##        item.asin = ((amazonAsinPrice.get('FBA'+str(item.id))).asin).strip()
 ##        amListed.asin = ((amazonAsinPrice.get('FBA'+str(item.id))).asin).strip()
 ##    session.commit()
 ##    session.close()
+def fetchMonthSale():
+    global monthlymonthlySaleMap
+    fbaSaleSnapshot = MongoService.getAmazonFbaSalesSnapshotForDays(29)
+    for saleSnapshot in fbaSaleSnapshot:
+        if saleSnapshot.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(0):
+            if monthlySaleMap.has_key('FBA'+str(saleSnapshot.item_id)):
+                temp = []
+                val = monthlySaleMap.get('FBA'+str(saleSnapshot.item_id))
+                for l in val:
+                    temp.append(l)
+                temp.append(saleSnapshot)
+                monthlySaleMap['FBA'+str(saleSnapshot.item_id)]=temp
+            else:
+                temp = []
+                temp.append(saleSnapshot)
+                monthlySaleMap['FBA'+str(saleSnapshot.item_id)] = temp
+        elif saleSnapshot.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(1):
+            if monthlySaleMap.has_key('FBB'+str(saleSnapshot.item_id)):
+                temp = []
+                val = monthlySaleMap.get('FBB'+str(saleSnapshot.item_id))
+                for l in val:
+                    temp.append(l)
+                temp.append(saleSnapshot)
+                monthlySaleMap['FBB'+str(saleSnapshot.item_id)]=temp
+            else:
+                temp = []
+                temp.append(saleSnapshot)
+                monthlySaleMap['FBB'+str(saleSnapshot.item_id)] = temp
+        elif saleSnapshot.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(2):
+            if monthlySaleMap.has_key('FBG'+str(saleSnapshot.item_id)):
+                temp = []
+                val = monthlySaleMap.get('FBG'+str(saleSnapshot.item_id))
+                for l in val:
+                    temp.append(l)
+                temp.append(saleSnapshot)
+                monthlySaleMap['FBG'+str(saleSnapshot.item_id)]=temp
+            else:
+                temp = []
+                temp.append(saleSnapshot)
+                monthlySaleMap['FBG'+str(saleSnapshot.item_id)] = temp
+        else:
+            continue
+def populateHourlySnapshot():
+    global latestHourlySnapshot
+    fbaHourlySnapshot = MongoService.getLatestHourlySnapshot()
+    for sale in fbaHourlySnapshot:
+        if sale.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(0):
+            latestHourlySnapshot['FBA'+str(sale.item_id)] = sale
+        elif sale.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(1):
+            latestHourlySnapshot['FBB'+str(sale.item_id)] = sale
+        elif sale.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(2):
+            latestHourlySnapshot['FBG'+str(sale.item_id)] = sale
+        else:
+            continue
+def getTodaysSale(sku):
+    sale = latestHourlySnapshot.get(sku)
+    if sale is None:
+        return '', 0
+    return (to_py_date(sale.snapshotTime)).strftime("%Y-%m-%d %H:%M"), sale.totalOrderCount
 def fetchFbaSale():
     global saleMap
-    transaction_client = TransactionClient().get_client()
-    fbaSaleSnapshot = transaction_client.getAmazonFbaSalesSnapshotForDays(4)
+    fbaSaleSnapshot = MongoService.getAmazonFbaSalesSnapshotForDays(4)
     for saleSnapshot in fbaSaleSnapshot:
-        if saleSnapshot.fcLocation == 0:
+        if saleSnapshot.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(0):
             if saleMap.has_key('FBA'+str(saleSnapshot.item_id)):
                 temp = []
                 val = saleMap.get('FBA'+str(saleSnapshot.item_id))
                 for l in val:
                     temp.append(l)
                 saleMap['FBA'+str(saleSnapshot.item_id)]=temp
             else:
                 temp = []
                 temp.append(saleSnapshot)
                 saleMap['FBA'+str(saleSnapshot.item_id)] = temp
-        elif saleSnapshot.fcLocation == 1:
+        elif saleSnapshot.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(1):
             if saleMap.has_key('FBB'+str(saleSnapshot.item_id)):
                 temp = []
                 val = saleMap.get('FBB'+str(saleSnapshot.item_id))
                 for l in val:
                     temp.append(l)
                 saleMap['FBB'+str(saleSnapshot.item_id)]=temp
             else:
                 temp = []
                 temp.append(saleSnapshot)
                 saleMap['FBB'+str(saleSnapshot.item_id)] = temp
-        elif saleSnapshot.fcLocation == 2:
+        elif saleSnapshot.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(2):
             if saleMap.has_key('FBG'+str(saleSnapshot.item_id)):
                 temp = []
                 val = saleMap.get('FBG'+str(saleSnapshot.item_id))
                 for l in val:
                     temp.append(l)
     sheet.write(0, 40, "Margin", heading_xf)
     sheet.write(0, 41, "Proposed Sp", heading_xf)
     sheet.write(0, 42, "Avg Sale", heading_xf)
     sheet.write(0, 43, "NOD", heading_xf)
     sheet.write(0, 44, "Sales History", heading_xf)
+    sheet.write(0, 45, "Last 30 days sale", heading_xf)
+    sheet.write(0, 46, "No of days InStock in last 30 days", heading_xf)
+    sheet.write(0, 47, "Avg 30 Days Sale", heading_xf)
+    sheet.write(0, 48, "Last OOS Date", heading_xf)
+    sheet.write(0, 49, "Total Sale Since InStock (Excluding Today)", heading_xf)
+    sheet.write(0, 50, "Total Days Since InStock (Excluding Today)", heading_xf)
+    sheet.write(0, 51, "Avg Sale Since InStock (Excluding Today)", heading_xf)
+    sheet.write(0, 52, "Todays Sale Snapshot Time", heading_xf)
+    sheet.write(0, 53, "Todays Sale", heading_xf)
-    sheet.write(0, 45, "Decision", heading_xf)
+    sheet.write(0, 54, "Decision", heading_xf)
-    sheet.write(0, 46, "Reason", heading_xf)
+    sheet.write(0, 55, "Reason", heading_xf)
-    sheet.write(0, 47, "Updated Price", heading_xf)
+    sheet.write(0, 56, "Updated Price", heading_xf)
-    sheet.write(0, 48, "Proposed Margin", heading_xf)
+    sheet.write(0, 57, "Proposed Margin", heading_xf)
-    sheet.write(0, 49, "Inventory Movement Status", heading_xf)
+    sheet.write(0, 58, "Inventory Movement Status", heading_xf)
 def getPackagingCost(data):
     #TODO : Get packagingCost from marketplaceitems table
     return 15
     if(myVat<0):
         return 0
     else:
         return round(myVat)
+def getAvgSale(days, sale):
+    avgSalePerDay=0 if days==0 else (float(sale)/days)
+    return round(avgSalePerDay,2)
 def getCategory(data):
     return categoryMap[data.category]
 def writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,runType):
     excel_integer_format = '0'
     integer_style = xlwt.XFStyle()
     integer_style.num_format_str = excel_integer_format
     writeheaders(sheet,heading_xf)
-    '''
-    sheet.write(0, 0, "Item Id", heading_xf)
-    sheet.write(0, 1, "Amazon Sku", heading_xf)
-    sheet.write(0, 2, "Asin", heading_xf)
-    sheet.write(0, 3, "Location", heading_xf)
-    sheet.write(0, 4, "Brand", heading_xf)
-    sheet.write(0, 5, "Product Name", heading_xf)
-    sheet.write(0, 6, "Weight", heading_xf)
-    sheet.write(0, 7, "Courier Cost", heading_xf)
-    sheet.write(0, 8, "Our SP", heading_xf)
-    sheet.write(0, 9, "Promo Price", heading_xf)
-    sheet.write(0, 10, "Is Promotion", heading_xf)
-    sheet.write(0, 11, "Lowest Possible SP", heading_xf)
-    sheet.write(0, 12, "Rank", heading_xf)
-    sheet.write(0, 13, "Our Inventory", heading_xf)
-    sheet.write(0, 14, "Lowest Seller SP", heading_xf)
-    sheet.write(0, 15, "Lowest Seller Rating", heading_xf)
-    sheet.write(0, 16, "Lowest Seller Shipping Time", heading_xf)
-    sheet.write(0, 17, "Second Lowest Seller SP", heading_xf)
-    sheet.write(0, 18, "Second Lowest Seller Rating", heading_xf)
-    sheet.write(0, 19, "Second Lowest Seller Shipping Time", heading_xf)
-    sheet.write(0, 20, "Third Lowest Seller SP", heading_xf)
-    sheet.write(0, 21, "Third Lowest Seller Rating", heading_xf)
-    sheet.write(0, 22, "Third Lowest Seller Shipping Time", heading_xf)
-    sheet.write(0, 23, "Other Cost", heading_xf)
-    sheet.write(0, 24, "WANLC", heading_xf)
-    sheet.write(0, 25, "Subsidy", heading_xf)
-    sheet.write(0, 26, "Commission", heading_xf)
-    sheet.write(0, 27, "Competitor Commission", heading_xf)
-    sheet.write(0, 28, "Return Provision", heading_xf)
-    sheet.write(0, 29, "Vat Rate", heading_xf)
-    sheet.write(0, 30, "Margin", heading_xf)
-    sheet.write(0, 31, "Risky", heading_xf)
-    sheet.write(0, 32, "Proposed Sp", heading_xf)
-    sheet.write(0, 33, "Avg Sale", heading_xf)
-    sheet.write(0, 34, "Sales History", heading_xf)
-    '''
     sheet_iterator = 1
     cantCompeteItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
     for cantCompeteItem in cantCompeteItems:
         amScraping =  cantCompeteItem[0]
         item = cantCompeteItem[1]
         if str(daysOfStock)=='inf':
             sheet.write(sheet_iterator, 43, str(daysOfStock))
         else:
             sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
         sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
+        sale, count = get30DaysStats(sku)
+        sheet.write(sheet_iterator, 45, sale)
+        sheet.write(sheet_iterator, 46, count)
+        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
+        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
+        if inStockStats[0].dateOfSale == 0:
+            sheet.write(sheet_iterator, 48, 'No History available')
+        else:
+            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
+        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
+        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
+        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[1], inStockStats[2]))
+        toDaysSale = getTodaysSale(sku)
+        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, 49, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 56, getInventoryMovementStatus(amScraping))
         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]
         if str(daysOfStock)=='inf':
             sheet.write(sheet_iterator, 43, str(daysOfStock))
         else:
             sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
         sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
+        sale, count = get30DaysStats(sku)
+        sheet.write(sheet_iterator, 45, sale)
+        sheet.write(sheet_iterator, 46, count)
+        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
+        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
+        if inStockStats[0].dateOfSale == 0:
+            sheet.write(sheet_iterator, 48, 'No History available')
+        else:
+            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
+        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
+        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
+        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[1], inStockStats[2]))
+        toDaysSale = getTodaysSale(sku)
+        sheet.write(sheet_iterator, 52, toDaysSale[0])
+        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
         if amScraping.decision is None:
-            sheet.write(sheet_iterator, 45, 'Auto Pricing Inactive')
+            sheet.write(sheet_iterator, 54, 'Auto Pricing Inactive')
-            sheet.write(sheet_iterator, 48, getNewMargin(amScraping,amScraping.proposedSp))
+            sheet.write(sheet_iterator, 55, getNewMargin(amScraping,amScraping.proposedSp))
-            sheet.write(sheet_iterator, 49, getInventoryMovementStatus(amScraping))
+            sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
             sheet_iterator+=1
             continue
-        sheet.write(sheet_iterator, 45, Decision._VALUES_TO_NAMES.get(amScraping.decision))
+        sheet.write(sheet_iterator, 54, Decision._VALUES_TO_NAMES.get(amScraping.decision))
-        sheet.write(sheet_iterator, 46, amScraping.reason)
+        sheet.write(sheet_iterator, 55, amScraping.reason)
         if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
-            sheet.write(sheet_iterator, 47, math.ceil(amScraping.proposedSp))
+            sheet.write(sheet_iterator, 56, math.ceil(amScraping.proposedSp))
         if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
-            sheet.write(sheet_iterator, 47, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
+            sheet.write(sheet_iterator, 56, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
-        sheet.write(sheet_iterator, 48, getNewMargin(amScraping,amScraping.proposedSp))
+        sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
-        sheet.write(sheet_iterator, 49, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
         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]
         if str(daysOfStock)=='inf':
             sheet.write(sheet_iterator, 43, str(daysOfStock))
         else:
             sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
         sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
+        sale, count = get30DaysStats(sku)
+        sheet.write(sheet_iterator, 45, sale)
+        sheet.write(sheet_iterator, 46, count)
+        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
+        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
+        if inStockStats[0].dateOfSale == 0:
+            sheet.write(sheet_iterator, 48, 'No History available')
+        else:
+            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
+        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
+        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
+        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[1], inStockStats[2]))
+        toDaysSale = getTodaysSale(sku)
+        sheet.write(sheet_iterator, 52, toDaysSale[0])
+        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
         if amScraping.decision is None:
-            sheet.write(sheet_iterator, 45, 'Auto Pricing Inactive')
+            sheet.write(sheet_iterator, 54, 'Auto Pricing Inactive')
-            sheet.write(sheet_iterator, 48, getNewMargin(amScraping,amScraping.proposedSp))
+            sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
-            sheet.write(sheet_iterator, 49, getInventoryMovementStatus(amScraping))
+            sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
             sheet_iterator+=1
             continue
-        sheet.write(sheet_iterator, 45, Decision._VALUES_TO_NAMES.get(amScraping.decision))
+        sheet.write(sheet_iterator, 54, Decision._VALUES_TO_NAMES.get(amScraping.decision))
-        sheet.write(sheet_iterator, 46, amScraping.reason)
+        sheet.write(sheet_iterator, 55, amScraping.reason)
         if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
-            sheet.write(sheet_iterator, 47, math.ceil(amScraping.proposedSp))
+            sheet.write(sheet_iterator, 56, math.ceil(amScraping.proposedSp))
         if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
-            sheet.write(sheet_iterator, 47, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
+            sheet.write(sheet_iterator, 56, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
-        sheet.write(sheet_iterator, 48, getNewMargin(amScraping,amScraping.proposedSp))
+        sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
-        sheet.write(sheet_iterator, 49, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
         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]
         if str(daysOfStock)=='inf':
             sheet.write(sheet_iterator, 43, str(daysOfStock))
         else:
             sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
         sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
+        sale, count = get30DaysStats(sku)
+        sheet.write(sheet_iterator, 45, sale)
+        sheet.write(sheet_iterator, 46, count)
+        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
+        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
+        if inStockStats[0].dateOfSale == 0:
+            sheet.write(sheet_iterator, 48, 'No History available')
+        else:
+            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
+        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
+        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
+        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[1], inStockStats[2]))
+        toDaysSale = getTodaysSale(sku)
+        sheet.write(sheet_iterator, 52, toDaysSale[0])
+        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
         if amScraping.decision is None:
-            sheet.write(sheet_iterator, 45, 'Auto Pricing Inactive')
+            sheet.write(sheet_iterator, 54, 'Auto Pricing Inactive')
-            sheet.write(sheet_iterator, 48, getNewMargin(amScraping,amScraping.proposedSp))
+            sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
-            sheet.write(sheet_iterator, 49, getInventoryMovementStatus(amScraping))
+            sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
             sheet_iterator+=1
             continue
-        sheet.write(sheet_iterator, 45, Decision._VALUES_TO_NAMES.get(amScraping.decision))
+        sheet.write(sheet_iterator, 54, Decision._VALUES_TO_NAMES.get(amScraping.decision))
-        sheet.write(sheet_iterator, 46, amScraping.reason)
+        sheet.write(sheet_iterator, 55, amScraping.reason)
         if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
-            sheet.write(sheet_iterator, 47, math.ceil(amScraping.proposedSp))
+            sheet.write(sheet_iterator, 56, math.ceil(amScraping.proposedSp))
         if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
-            sheet.write(sheet_iterator, 47, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
+            sheet.write(sheet_iterator, 56, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
-        sheet.write(sheet_iterator, 48, getNewMargin(amScraping,amScraping.proposedSp))
+        sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
-        sheet.write(sheet_iterator, 49, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
         sheet_iterator+=1
 #    sheet = wbk.add_sheet('Cheapest')
 #    xstr = lambda s: s or ""
         if str(daysOfStock)=='inf':
             sheet.write(sheet_iterator, 43, str(daysOfStock))
         else:
             sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
         sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
+        sale, count = get30DaysStats(sku)
+        sheet.write(sheet_iterator, 45, sale)
+        sheet.write(sheet_iterator, 46, count)
+        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
+        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
+        if inStockStats[0].dateOfSale == 0:
+            sheet.write(sheet_iterator, 48, 'No History available')
+        else:
+            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
+        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
+        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
+        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[1], inStockStats[2]))
+        toDaysSale = getTodaysSale(sku)
+        sheet.write(sheet_iterator, 52, toDaysSale[0])
+        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
         if amScraping.decision is None:
-            sheet.write(sheet_iterator, 45, 'Auto Pricing Inactive')
+            sheet.write(sheet_iterator, 54, 'Auto Pricing Inactive')
-            sheet.write(sheet_iterator, 48, getNewMargin(amScraping,amScraping.proposedSp))
+            sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
-            sheet.write(sheet_iterator, 49, getInventoryMovementStatus(amScraping))
+            sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
             sheet_iterator+=1
             continue
-        sheet.write(sheet_iterator, 45, Decision._VALUES_TO_NAMES.get(amScraping.decision))
+        sheet.write(sheet_iterator, 54, Decision._VALUES_TO_NAMES.get(amScraping.decision))
-        sheet.write(sheet_iterator, 46, amScraping.reason)
+        sheet.write(sheet_iterator, 55, amScraping.reason)
         if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
-            sheet.write(sheet_iterator, 47, math.ceil(amScraping.proposedSp))
+            sheet.write(sheet_iterator, 56, math.ceil(amScraping.proposedSp))
         if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
-            sheet.write(sheet_iterator, 47, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
+            sheet.write(sheet_iterator, 56, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
-        sheet.write(sheet_iterator, 48, getNewMargin(amScraping,math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice))))
+        sheet.write(sheet_iterator, 57, getNewMargin(amScraping,math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice))))
-        sheet.write(sheet_iterator, 49, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 58, 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()
         if str(daysOfStock)=='inf':
             sheet.write(sheet_iterator, 43, str(daysOfStock))
         else:
             sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
         sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
+        sale, count = get30DaysStats(sku)
+        sheet.write(sheet_iterator, 45, sale)
+        sheet.write(sheet_iterator, 46, count)
+        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
+        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
+        if inStockStats[0].dateOfSale == 0:
+            sheet.write(sheet_iterator, 48, 'No History available')
+        else:
+            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
+        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
+        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
+        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[1], inStockStats[2]))
+        toDaysSale = getTodaysSale(sku)
+        sheet.write(sheet_iterator, 52, toDaysSale[0])
+        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
-        sheet.write(sheet_iterator, 45, 'AUTO_INCREMENT_FAILED')
+        sheet.write(sheet_iterator, 54, 'AUTO_INCREMENT_FAILED')
         if amScraping.exceptionType==3:
-            sheet.write(sheet_iterator, 46, 'We are the only seller')
+            sheet.write(sheet_iterator, 55, 'We are the only seller')
         else:
-            sheet.write(sheet_iterator, 46, amScraping.reason)
+            sheet.write(sheet_iterator, 55, amScraping.reason)
-        sheet.write(sheet_iterator, 49, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
         sheet_iterator+=1
 #    sheet = wbk.add_sheet('Negative Margin')
 #    xstr = lambda s: s or ""
         if str(daysOfStock)=='inf':
             sheet.write(sheet_iterator, 43, str(daysOfStock))
         else:
             sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
         sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
+        sale, count = get30DaysStats(sku)
+        sheet.write(sheet_iterator, 45, sale)
+        sheet.write(sheet_iterator, 46, count)
+        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
+        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
+        if inStockStats[0].dateOfSale == 0:
+            sheet.write(sheet_iterator, 48, 'No History available')
+        else:
+            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
+        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
+        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
+        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[1], inStockStats[2]))
+        toDaysSale = getTodaysSale(sku)
+        sheet.write(sheet_iterator, 52, toDaysSale[0])
+        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
-        sheet.write(sheet_iterator, 49, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 56, getInventoryMovementStatus(amScraping))
         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, 10, "Promo Price", heading_xf)
     sheet.write(0, 11, "Inventory", heading_xf)
     sheet.write(0, 12, "Avg Sale", heading_xf)
     sheet.write(0, 13, "NOD", heading_xf)
     sheet.write(0, 14, "Sales History", heading_xf)
+    sheet.write(0, 15, "Last 30 days sale", heading_xf)
+    sheet.write(0, 16, "No of days InStock in last 30 days", heading_xf)
+    sheet.write(0, 17, "Avg 30 Days Sale", heading_xf)
+    sheet.write(0, 18, "Last OOS Date", heading_xf)
+    sheet.write(0, 19, "Total Sale Since InStock (Excluding Today)", heading_xf)
+    sheet.write(0, 20, "Total Days Since InStock (Excluding Today)", heading_xf)
+    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, 15, "Inventory Movement Status", heading_xf)
+    sheet.write(0, 24, "Inventory Movement Status", heading_xf)
-    sheet.write(0, 16, "Reason", heading_xf)
+    sheet.write(0, 25, "Reason", 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]
         if str(daysOfStock)=='inf':
             sheet.write(sheet_iterator, 13, str(daysOfStock))
         else:
             sheet.write(sheet_iterator, 13, str(round(daysOfStock,1)))
         sheet.write(sheet_iterator, 14, getOosString(saleMap.get(sku)))
+        sale, count = get30DaysStats(sku)
+        sheet.write(sheet_iterator, 15, sale)
+        sheet.write(sheet_iterator, 16, count)
+        sheet.write(sheet_iterator, 17, getAvgSale(count, sale))
+        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
+        if inStockStats[0].dateOfSale == 0:
+            sheet.write(sheet_iterator, 18, 'No History available')
+        else:
+            sheet.write(sheet_iterator, 18, str(to_py_date(inStockStats[0].dateOfSale).date()))
+        sheet.write(sheet_iterator, 19, str(inStockStats[1]))
+        sheet.write(sheet_iterator, 20, str(inStockStats[2]))
+        sheet.write(sheet_iterator, 21, getAvgSale(inStockStats[1], inStockStats[2]))
+        toDaysSale = getTodaysSale(sku)
+        sheet.write(sheet_iterator, 22, toDaysSale[0])
+        sheet.write(sheet_iterator, 23, str(toDaysSale[1]))
-        sheet.write(sheet_iterator, 15, getInventoryMovementStatus(amScraping))
+        sheet.write(sheet_iterator, 24, getInventoryMovementStatus(amScraping))
-        sheet.write(sheet_iterator, 16, amScraping.reason)
+        sheet.write(sheet_iterator, 25, amScraping.reason)
         sheet_iterator+=1
     if (runType in ('FULL','FULL-OTHER')):
         sheet = wbk.add_sheet('Auto Favorites')
             vatRate = vatMaster.vatPercent
     else:
         vatRate = itemVatMaster.vatPercentage
     return vatRate
-def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
+def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease,timestamp):
     if len(successfulAutoDecrease)==0 and len(successfulAutoIncrease)==0 :
         return
     xstr = lambda s: s or ""
     message="""<html>
             <body>
             <th>New Margin</th>
             <th>Commission %</th>
             <th>Return Provision %</th>
             <th>Inventory</th>
             <th>Sales History</th>
+            <th>30 Days Sale</th>
+            <th>Todays Sale</th>
+            <th>Todays Sale Snapshot Time</th>
             <th>Category</th>
             <th>isListed</th>
             <th>Price Feed Suppressed</th>
             </tr></thead>
             <tbody>"""
             subsidy = (amazonLongTermActivePromotions.get(sku)).subsidy
         elif amazonShortTermActivePromotions.has_key(sku):
             subsidy = (amazonShortTermActivePromotions.get(sku)).subsidy
         else:
             subsidy = 0
+        toDaysSale = getTodaysSale(sku)
+        sale, count = get30DaysStats(sku)
         message+="""<tr>
                 <td style="text-align:center">"""+str(item.item_id)+"""</td>
                 <td style="text-align:center">"""+sku+"""</td>
                 <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
                 <td style="text-align:center">"""+str(item.promoPrice)+"""</td>
                 <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/item.proposedSp)*100,1))+"%)"+"""</td>
                 <td style="text-align:center">"""+str(item.commission)+" %"+"""</td>
                 <td style="text-align:center">"""+str(item.returnProvision)+" %"+"""</td>
                 <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
                 <td style="text-align:center">"""+getOosString(saleMap.get(sku))+"""</td>
+                <td style="text-align:center">"""+str(sale)+"""</td>
+                <td style="text-align:center">"""+str(toDaysSale[1])+"""</td>
+                <td style="text-align:center">"""+toDaysSale[0]+"""</td>
                 <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(item.competitiveCategory))+"""</td>
                 <td style="text-align:center">"""+isListed+"""</td>
                 <td style="text-align:center">"""+priceSuppress+"""</td>
                 </tr>"""
     message+="""</tbody></table><h3>Auto Increase Items</h3><table border="1" style="width:100%;">
             <th>New Margin</th>
             <th>Commission %</th>
             <th>Return Provision %</th>
             <th>Inventory</th>
             <th>Sales History</th>
+            <th>30 Days Sale</th>
+            <th>Todays Sale</th>
+            <th>Todays Sale Snapshot Time</th>
             <th>Category</th>
             <th>isListed</th>
             <th>Price Feed Suppressed</th>
             </tr></thead>
             <tbody>"""
             subsidy = (amazonLongTermActivePromotions.get(sku)).subsidy
         elif amazonShortTermActivePromotions.has_key(sku):
             subsidy = (amazonShortTermActivePromotions.get(sku)).subsidy
         else:
             subsidy = 0
+        toDaysSale = getTodaysSale(sku)
+        sale, count = get30DaysStats(sku)
         message+="""<tr>
                 <td style="text-align:center">"""+str(item.item_id)+"""</td>
                 <td style="text-align:center">"""+sku+"""</td>
                 <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
                 <td style="text-align:center">"""+str(item.promoPrice)+"""</td>
                 <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/(math.ceil(min(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)),item.proposedSp))))*100,1))+"%)"+"""</td>
                 <td style="text-align:center">"""+str(item.commission)+" %"+"""</td>
                 <td style="text-align:center">"""+str(item.returnProvision)+" %"+"""</td>
                 <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
                 <td style="text-align:center">"""+getOosString(saleMap.get(sku))+"""</td>
+                <td style="text-align:center">"""+str(sale)+"""</td>
+                <td style="text-align:center">"""+str(toDaysSale[1])+"""</td>
+                <td style="text-align:center">"""+toDaysSale[0]+"""</td>
                 <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(item.competitiveCategory))+"""</td>
                 <td style="text-align:center">"""+isListed+"""</td>
                 <td style="text-align:center">"""+priceSuppress+"""</td>
                 </tr>"""
-    message+="""</tbody></table><h3>Create Listing On Our Dashboard - Stock is present in FC</h3><table border="1" style="width:100%;">
+    message+="""</tbody></table><h3>Create Listing On Our Dashboard - Stock present in FC</h3><table border="1" style="width:100%;">
             <thead>
             <tr><th>Item Id</th>
             <th>Amazon SKU</th>
             <th>Product Name</th></tr></thead>
             <tbody>
         message+="""<tr>
         <td style="text-align:center">"""+sku[3:]+"""</td>
         <td style="text-align:center">"""+sku+"""</td>
         <td style="text-align:center">"""+productName+"""</td>
         </tr>"""
+    exceptionListItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp).all()
+    message+="""</tbody></table><h3 style="color:red;">Exception Items</h3><table border="1" style="width:100%;">
+            <thead>
+            <tr><th>Item Id</th>
+            <th>Amazon SKU</th>
+            <th>Product Name</th>
+            <th>Selling Price</th>
+            <th>Promo Price</th>
+            <th>Inventory</th>
+            <th>30 Days Sale</th>
+            <th>Todays Sale</th>
+            <th>Todays Sale Snapshot Time</th>
+            <th>Sales History</th>
+            <th>Reason</th>
+            </tr></thead>
+            <tbody>
+            """
+    for exceptionListItem in exceptionListItems:
+        amScraping =  exceptionListItem[0]
+        if amScraping.exceptionType in (3,5):
+            continue
+        item = exceptionListItem[1]
+        sku = ''
+        if amScraping.warehouseLocation==1:
+            sku='FBA'+str(item.id)
+        elif amScraping.warehouseLocation==2:
+            sku='FBB'+str(item.id)
+        else:
+            sku='FBG'+str(item.id)
+        toDaysSale = getTodaysSale(sku)
+        sale, count = get30DaysStats(sku)
+        message+="""<tr>
+                <td style="text-align:center">"""+str(item.id)+"""</td>
+                <td style="text-align:center">"""+sku+"""</td>
+                <td style="text-align:center">"""+xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color)+"""</td>
+                <td style="text-align:center">"""+str(amScraping.ourSellingPrice)+"""</td>
+                <td style="text-align:center">"""+str(amScraping.promoPrice)+"""</td>
+                <td style="text-align:center">"""+str(amScraping.ourInventory)+"""</td>
+                <td style="text-align:center">"""+str(sale)+"""</td>
+                <td style="text-align:center">"""+str(toDaysSale[1])+"""</td>
+                <td style="text-align:center">"""+toDaysSale[0]+"""</td>
+                <td style="text-align:center">"""+getOosString(saleMap.get(sku))+"""</td>
+                <td style="text-align:center">"""+amScraping.reason+"""</td>
+                </tr>"""
     message+="""</tbody></table></body></html>"""
     print message
     mailServer = smtplib.SMTP("smtp.gmail.com", 587)
     mailServer.ehlo()
     mailServer.starttls()
         commitCantCompete(cantCompete, timestamp,options.runType)
         exceptionList[:], negativeMargin[:], cheapest[:], amongCheapestAndCanCompete[:], canCompete[:], almostCompete[:], cantCompete[:] =[],[],[],[],[],[],[]
     autoDecreaseItems = fetchItemsForAutoDecrease(timestamp)
     autoIncreaseItems = fetchItemsForAutoIncrease(timestamp)
     previousAutoFav, nowAutoFav = markAutoFavourites(timestamp)
+    fetchMonthSale()
+    populateHourlySnapshot()
     writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,options.runType)
     commitPricing(autoDecreaseItems,autoIncreaseItems)
-    sendAutoPricingMail(autoDecreaseItems,autoIncreaseItems)
+    sendAutoPricingMail(autoDecreaseItems,autoIncreaseItems, timestamp)
     if options.runType == 'FULL-OTHER':
         sendAlertForNegativeMargins(timestamp)
         sendAlertForCantCompete(timestamp)
 if __name__=='__main__':
     main()

Subversion Repositories SmartDukaan

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