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

             previouslyAutoFav.append(aItem.itemId)
         aItem.autoFavourite = toMark
     session.commit()
     return previouslyAutoFav, nowAutoFav
-def writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav):
+def writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,runType):
     wbk = xlwt.Workbook()
     sheet = wbk.add_sheet('Can\'t Compete')
     xstr = lambda s: s or ""
     heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
     sheet.write(0, 29, "Proposed Sp", heading_xf)
     sheet.write(0, 30, "Proposed Tp", heading_xf)
     sheet.write(0, 31, "Target Nlc", heading_xf)
     sheet.write(0, 32, "Avg Sale", heading_xf)
     sheet.write(0, 33, "Sales History", heading_xf)
+    sheet.write(0, 34, "Decision", heading_xf)
+    sheet.write(0, 35, "Reason", heading_xf)
+    sheet.write(0, 36, "Updated Price", heading_xf)
     sheet_iterator = 1
     cantCompeteItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
     for cantCompeteItem in cantCompeteItems:
         amScraping =  cantCompeteItem[0]
         sheet.write(sheet_iterator, 29, amScraping.proposedSp)
         sheet.write(sheet_iterator, 30, amScraping.proposedTp)
         sheet.write(sheet_iterator, 31, amScraping.targetNlc)
         sheet.write(sheet_iterator, 32, amScraping.avgSale)
         sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
+        if amScraping.decision is None:
+            sheet.write(sheet_iterator, 34, 'Auto Pricing Inactive')
+            sheet_iterator+=1
+            continue
+        sheet.write(sheet_iterator, 34, Decision._VALUES_TO_NAMES.get(amScraping.decision))
+        sheet.write(sheet_iterator, 35, amScraping.reason)
+        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
+            sheet.write(sheet_iterator, 36, math.ceil(amScraping.proposedSellingPrice))
+        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
+            sheet.write(sheet_iterator, 36, math.ceil(amScraping.ourSellingPrice+max(10,.01*amScraping.ourSellingPrice)))
         sheet_iterator+=1
     sheet = wbk.add_sheet('Competitive')
     xstr = lambda s: s or ""
     heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
     sheet.write(0, 28, "Risky", heading_xf)
     sheet.write(0, 29, "Proposed Sp", heading_xf)
     sheet.write(0, 30, "Proposed Tp", heading_xf)
     sheet.write(0, 31, "Avg Sale", heading_xf)
     sheet.write(0, 32, "Sales History", heading_xf)
+    sheet.write(0, 33, "Decision", heading_xf)
+    sheet.write(0, 34, "Reason", heading_xf)
+    sheet.write(0, 35, "Updated Price", heading_xf)
     sheet_iterator = 1
     competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).all()
     for competitiveItem in competitiveItems:
         amScraping =  competitiveItem[0]
         sheet.write(sheet_iterator, 28, item.risky)
         sheet.write(sheet_iterator, 29, amScraping.proposedSp)
         sheet.write(sheet_iterator, 30, amScraping.proposedTp)
         sheet.write(sheet_iterator, 31, amScraping.avgSale)
         sheet.write(sheet_iterator, 32, getOosString(saleMap.get(sku)))
+        if amScraping.decision is None:
+            sheet.write(sheet_iterator, 33, 'Auto Pricing Inactive')
+            sheet_iterator+=1
+            continue
+        sheet.write(sheet_iterator, 33, Decision._VALUES_TO_NAMES.get(amScraping.decision))
+        sheet.write(sheet_iterator, 34, amScraping.reason)
+        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
+            sheet.write(sheet_iterator, 35, math.ceil(amScraping.proposedSellingPrice))
+        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
+            sheet.write(sheet_iterator, 35, math.ceil(amScraping.ourSellingPrice+max(10,.01*amScraping.ourSellingPrice)))
         sheet_iterator+=1
     sheet = wbk.add_sheet('Almost Competitive')
     xstr = lambda s: s or ""
     heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
     sheet.write(0, 28, "Risky", heading_xf)
     sheet.write(0, 29, "Proposed Sp", heading_xf)
     sheet.write(0, 30, "Proposed Tp", heading_xf)
     sheet.write(0, 31, "Avg Sale", heading_xf)
     sheet.write(0, 32, "Sales History", heading_xf)
+    sheet.write(0, 33, "Decision", heading_xf)
+    sheet.write(0, 34, "Reason", heading_xf)
+    sheet.write(0, 35, "Updated Price", heading_xf)
     sheet_iterator = 1
     almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).all()
     for almostCompetitiveItem in almostCompetitiveItems:
         amScraping =  almostCompetitiveItem[0]
         sheet.write(sheet_iterator, 28, item.risky)
         sheet.write(sheet_iterator, 29, amScraping.proposedSp)
         sheet.write(sheet_iterator, 30, amScraping.proposedTp)
         sheet.write(sheet_iterator, 31, amScraping.avgSale)
         sheet.write(sheet_iterator, 32, getOosString(saleMap.get(sku)))
+        if amScraping.decision is None:
+            sheet.write(sheet_iterator, 33, 'Auto Pricing Inactive')
+            sheet_iterator+=1
+            continue
+        sheet.write(sheet_iterator, 33, Decision._VALUES_TO_NAMES.get(amScraping.decision))
+        sheet.write(sheet_iterator, 34, amScraping.reason)
+        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
+            sheet.write(sheet_iterator, 35, math.ceil(amScraping.proposedSellingPrice))
+        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
+            sheet.write(sheet_iterator, 35, math.ceil(amScraping.ourSellingPrice+max(10,.01*amScraping.ourSellingPrice)))
         sheet_iterator+=1
     sheet = wbk.add_sheet('Among Cheapest')
     xstr = lambda s: s or ""
     heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
     sheet.write(0, 28, "Risky", heading_xf)
     sheet.write(0, 29, "Proposed Sp", heading_xf)
     sheet.write(0, 30, "Proposed Tp", heading_xf)
     sheet.write(0, 31, "Avg Sale", heading_xf)
     sheet.write(0, 32, "Sales History", heading_xf)
+    sheet.write(0, 33, "Decision", heading_xf)
+    sheet.write(0, 34, "Reason", heading_xf)
+    sheet.write(0, 35, "Updated Price", heading_xf)
     sheet_iterator = 1
     amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE).all()
     for amongCheapestItem in amongCheapestItems:
         amScraping =  amongCheapestItem[0]
         sheet.write(sheet_iterator, 28, item.risky)
         sheet.write(sheet_iterator, 29, amScraping.proposedSp)
         sheet.write(sheet_iterator, 30, amScraping.proposedTp)
         sheet.write(sheet_iterator, 31, amScraping.avgSale)
         sheet.write(sheet_iterator, 32, getOosString(saleMap.get(sku)))
+        if amScraping.decision is None:
+            sheet.write(sheet_iterator, 33, 'Auto Pricing Inactive')
+            sheet_iterator+=1
+            continue
+        sheet.write(sheet_iterator, 33, Decision._VALUES_TO_NAMES.get(amScraping.decision))
+        sheet.write(sheet_iterator, 34, amScraping.reason)
+        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
+            sheet.write(sheet_iterator, 35, math.ceil(amScraping.proposedSellingPrice))
+        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
+            sheet.write(sheet_iterator, 35, math.ceil(amScraping.ourSellingPrice+max(10,.01*amScraping.ourSellingPrice)))
         sheet_iterator+=1
     sheet = wbk.add_sheet('Cheapest')
     xstr = lambda s: s or ""
     heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
     sheet.write(0, 29, "Proposed Sp", heading_xf)
     sheet.write(0, 30, "Proposed Tp", heading_xf)
     sheet.write(0, 31, "Margin Increased Potential", heading_xf)
     sheet.write(0, 32, "Avg Sale", heading_xf)
     sheet.write(0, 33, "Sales History", heading_xf)
+    sheet.write(0, 34, "Decision", heading_xf)
+    sheet.write(0, 35, "Reason", heading_xf)
+    sheet.write(0, 36, "Updated Price", heading_xf)
     sheet_iterator = 1
     cheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
     for cheapestItem in cheapestItems:
         amScraping =  cheapestItem[0]
         sheet.write(sheet_iterator, 29, amScraping.proposedSp)
         sheet.write(sheet_iterator, 30, amScraping.proposedTp)
         sheet.write(sheet_iterator, 31, amScraping.targetNlc)
         sheet.write(sheet_iterator, 32, amScraping.avgSale)
         sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
+        if amScraping.decision is None:
+            sheet.write(sheet_iterator, 34, 'Auto Pricing Inactive')
+            sheet_iterator+=1
+            continue
+        sheet.write(sheet_iterator, 34, Decision._VALUES_TO_NAMES.get(amScraping.decision))
+        sheet.write(sheet_iterator, 35, amScraping.reason)
+        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
+            sheet.write(sheet_iterator, 36, math.ceil(amScraping.proposedSellingPrice))
+        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
+            sheet.write(sheet_iterator, 36, math.ceil(amScraping.ourSellingPrice+max(10,.01*amScraping.ourSellingPrice)))
         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')
         sheet.write(sheet_iterator, 4, item.brand)
         sheet.write(sheet_iterator, 5, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
         sheet.write(sheet_iterator, 6, amScraping.reason)
         sheet_iterator+=1
+    if (runType=='FULL'):
+        sheet = wbk.add_sheet('Auto Favorites')
+        heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
+        excel_integer_format = '0'
+        integer_style = xlwt.XFStyle()
+        integer_style.num_format_str = excel_integer_format
+        xstr = lambda s: s or ""
+        sheet.write(0, 0, "Item ID", heading_xf)
+        sheet.write(0, 1, "Brand", heading_xf)
+        sheet.write(0, 2, "Product Name", heading_xf)
+        sheet.write(0, 3, "Auto Favourite", heading_xf)
+        sheet.write(0, 4, "Reason", heading_xf)
+        sheet_iterator=1
+        for autoFav in nowAutoFav:
+            itemId = autoFav[0]
+            reason = autoFav[1]
+            it = Item.query.filter_by(id=itemId).one()
+            sheet.write(sheet_iterator, 0, itemId)
+            sheet.write(sheet_iterator, 1, it.brand)
+            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
+            sheet.write(sheet_iterator, 3, "True")
+            sheet.write(sheet_iterator, 4, reason)
+            sheet_iterator+=1
+        for prevFav in previousAutoFav:
+            it = Item.query.filter_by(id=prevFav).one()
+            sheet.write(sheet_iterator, 0, prevFav)
+            sheet.write(sheet_iterator, 1, it.brand)
+            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
+            sheet.write(sheet_iterator, 3, "False")
+            sheet_iterator+=1
     filename = "/tmp/amazon-scraping.xls"
     wbk.save(filename)
 def main():
     parser = optparse.OptionParser()
         commitCantCompete(cantCompete, timestamp,options.runType)
         exceptionList[:], negativeMargin[:], cheapest[:], amongCheapestAndCanCompete[:], canCompete[:], almostCompete[:], cantCompete[:] =[],[],[],[],[],[],[]
     autoDecreaseItems = fetchItemsForAutoDecrease(timestamp)
     autoIncreaseItems = fetchItemsForAutoIncrease(timestamp)
     previousAutoFav, nowAutoFav = markAutoFavourites(timestamp)
-    writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav)
+    writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,options.runType)
 if __name__=='__main__':
     main()

Subversion Repositories SmartDukaan

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