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

 from shop2020.model.v1.catalog.impl import DataService
 from shop2020.model.v1.catalog.impl.DataService import Item, Category,\
     SnapdealItem
 from shop2020.thriftpy.model.v1.inventory import ttypes
 from shop2020.thriftpy.model.v1.inventory.ttypes import WarehouseType, InventoryType
-import csv
-import datetime
 import optparse
 import xlrd
 from shop2020.clients.InventoryClient import InventoryClient
 import xlwt
 import urllib2
 def write_report(filename, all_lines, all_supc):
     buyBoxItems = []
     competitive = []
+    competitiveNoInventory = []
     wbk = xlwt.Workbook()
-    sheet = wbk.add_sheet('main')
+    sheet = wbk.add_sheet('Can\'t Compete')
     heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
     excel_integer_format = '0'
     integer_style = xlwt.XFStyle()
         else:
             lowestTp = supc_data.lowestSp*0.9497-courierCost
         lowest_possible_tp = one_line.our_nlc/0.988+15+6
-        if (lowestTp > lowest_possible_tp):
+        if (lowestTp > lowest_possible_tp and supc_data.ourInventory!=0):
             temp=[]
             temp.append(supc_data)
             temp.append(one_line)
             competitive.append(temp)
             i+=1
             continue
+        if (lowestTp > lowest_possible_tp and supc_data.ourInventory==0):
+            temp=[]
+            temp.append(supc_data)
+            temp.append(one_line)
+            competitiveNoInventory.append(temp)
+            i+=1
+            continue
         sheet.write(sheet_iterator, 0, one_line.item_id)
         sheet.write(sheet_iterator, 1, one_line.category_name)
         sheet.write(sheet_iterator, 2, one_line.product_group)
         sheet.write(sheet_iterator, 3, supc)
         else:
             sheet.write(sheet_iterator, 23, 'TP')
         proposed_tp = 0
         if lowestTp > lowest_possible_tp:
             sheet.write(sheet_iterator, 24, "Yes")
-            if competitionBasis=='SP':
-                proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
-                proposed_tp = proposed_sp*.09597-courierCost
-                sheet.write(sheet_iterator, 25, round(proposed_tp))
-                sheet.write(sheet_iterator, 26, round(proposed_sp))
-                sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
-            else:
-                proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
-                sheet.write(sheet_iterator, 25, round(proposed_tp))
-                sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
-                sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
         else:
             sheet.write(sheet_iterator, 24, "No")
+        if competitionBasis=='SP':
+            proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
+            proposed_tp = proposed_sp*0.9597-courierCost
+            sheet.write(sheet_iterator, 25, round(proposed_tp))
+            sheet.write(sheet_iterator, 26, round(proposed_sp))
+            sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
+        else:
+            proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
+            sheet.write(sheet_iterator, 25, round(proposed_tp))
+            sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
+            sheet.write(sheet_iterator, 27, round(proposed_tp*0.988-21))
         if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
             sheet.write(sheet_iterator, 28, "HIGH")
         elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
         i= i+1
         sheet_iterator+=1
     createSheetForBuyBoxItems(buyBoxItems,wbk)
     createSheetForCometitiveItems(competitive,wbk)
+    createSheetForCometitiveItemsNoInventory(competitiveNoInventory,wbk)
     wbk.save(filename)
     EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@shop2020.in"], " " , "", [get_attachment_part(filename)], ["kshitij.sood@shop2020.in"], [])
 def createSheetForBuyBoxItems(buyBoxItems,wbk):
-    sheet = wbk.add_sheet('BuyBoxItems')
+    sheet = wbk.add_sheet('Lowest')
     heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
     excel_integer_format = '0'
     integer_style = xlwt.XFStyle()
     sheet.write(0, 10, "Our SP", heading_xf)
     sheet.write(0, 11, "Our TP", heading_xf)
     sheet.write(0, 12, "Our Offer Price", heading_xf)
     sheet.write(0, 13, "Our Rank", heading_xf)
     sheet.write(0, 14, "Lowest Seller", heading_xf)
-    sheet.write(0, 15, "Lowest Offer Price", heading_xf)
+    sheet.write(0, 15, "Second Lowest Seller", heading_xf)
-    sheet.write(0, 16, "Second Lowest Seller", heading_xf)
+    sheet.write(0, 16, "Second Lowest Price", heading_xf)
-    sheet.write(0, 17, "Second Lowest Price", heading_xf)
+    sheet.write(0, 17, "Second Lowest Offer Price", heading_xf)
-    sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)
+    sheet.write(0, 18, "Second Lowest Seller TP", heading_xf)
+    sheet.write(0, 19, "Second Lowest Seller SP", heading_xf)
-    sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
+    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
-    sheet.write(0, 20, "Second Lowest Seller Inventory", heading_xf)
+    sheet.write(0, 21, "Second Lowest Seller Inventory", heading_xf)
-    sheet.write(0, 21, "Our NLC", heading_xf)
+    sheet.write(0, 22, "Our NLC", heading_xf)
+    sheet.write(0, 23, "Competition Basis", heading_xf)
-    sheet.write(0, 22, "Target TP", heading_xf)
+    sheet.write(0, 24, "Target TP", heading_xf)
-    sheet.write(0, 23, "Target SP", heading_xf)
+    sheet.write(0, 25, "Target SP", heading_xf)
-    sheet.write(0, 24, "Target NLC", heading_xf)
+    sheet.write(0, 26, "Target NLC", heading_xf)
-    sheet.write(0, 25, "MARGIN INCREASED POTENTIAL", heading_xf)
+    sheet.write(0, 27, "MARGIN INCREASED POTENTIAL", heading_xf)
     i=1
     for data in buyBoxItems:
         supc_data =data[0]
         sheet.write(i, 10, supc_data.ourSp)
         sheet.write(i, 11, round(supc_data.ourSp*0.9597-courierCost))
         sheet.write(i, 12, supc_data.ourOfferPrice)
         sheet.write(i, 13, supc_data.rank)
         sheet.write(i, 14, supc_data.lowestSellerName)
-        sheet.write(i, 15, supc_data.lowestOfferPrice)
-        sheet.write(i, 16, supc_data.secondLowestSellerName)
+        sheet.write(i, 15, supc_data.secondLowestSellerName)
-        sheet.write(i, 17, supc_data.secondLowestSellerSp)
+        sheet.write(i, 16, supc_data.secondLowestSellerSp)
-        sheet.write(i, 18, supc_data.secondLowestSellerOfferPrice)
+        sheet.write(i, 17, supc_data.secondLowestSellerOfferPrice)
-        sheet.write(i, 19, supc_data.ourInventory)
-        sheet.write(i, 20, supc_data.secondLowestSellerInventory)
-        sheet.write(i, 21, one_line.our_nlc)
         if one_line.parent_category ==10011:
-            lowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
+            secondlowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
         else:
-            lowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
+            secondlowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
         lowest_possible_tp = one_line.our_nlc/0.988+15+6
+        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
+        sheet.write(i, 18, secondlowestTp)
+        sheet.write(i, 18, secondlowestTp)
+        sheet.write(i, 20, supc_data.ourInventory)
+        sheet.write(i, 21, supc_data.secondLowestSellerInventory)
+        sheet.write(i, 22, one_line.our_nlc)
+        competitionBasis ='TP'
+        if (supc_data.secondLowestSellerOfferPrice == supc_data.secondLowestSellerSp):
+            competitionBasis ='SP'
+            sheet.write(i, 23, 'SP')
+        else:
+            sheet.write(i, 23, 'TP')
+        if competitionBasis=='SP':
+            proposed_sp = max(supc_data.secondLowestSellerSp - max((20, supc_data.secondLowestSellerSp*0.002)), lowest_possible_sp)
+            proposed_tp = proposed_sp*0.9597-courierCost
+            sheet.write(i, 24, round(proposed_tp))
+            sheet.write(i, 25, round(proposed_sp))
+            sheet.write(i, 26, round(proposed_tp*0.988+21))
+        else:
-        proposed_tp  = max(lowestTp - max((20, lowestTp*0.002)), lowest_possible_tp)
+            proposed_tp  = max(secondlowestTp - max((20, secondlowestTp*0.002)), lowest_possible_tp)
-        sheet.write(i, 22, round(proposed_tp))
+            sheet.write(i, 24, round(proposed_tp))
-        sheet.write(i, 23, round((proposed_tp+courierCost)/0.9597))
+            sheet.write(i, 25, round((proposed_tp+courierCost)/0.9597))
-        sheet.write(i, 24, round(proposed_tp*0.988+21))
+            sheet.write(i, 26, round(proposed_tp*0.988-21))
-        sheet.write(i, 25, round(proposed_tp-(supc_data.ourSp*0.9597-courierCost)))
+        sheet.write(i, 27, round(proposed_tp-(supc_data.ourSp*0.9597-courierCost)))
         i+=1
 def createSheetForCometitiveItems(competitiveItems,wbk):
-    sheet = wbk.add_sheet('Competitive')
+    sheet = wbk.add_sheet('Can Compete-With Inventory')
     heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
     excel_integer_format = '0'
     integer_style = xlwt.XFStyle()
     sheet.write(0, 22, "Lowest Possible SP", heading_xf)
     sheet.write(0, 23, "Competition Basis ", heading_xf)
     sheet.write(0, 24, "Can Compete", heading_xf)
     sheet.write(0, 25, "Target TP", heading_xf)
     sheet.write(0, 26, "Target SP", heading_xf)
-    sheet.write(0, 27, "Target NLC", heading_xf)
-    sheet.write(0, 28, "Sales Potential", heading_xf)
+    sheet.write(0, 27, "Sales Potential", heading_xf)
     i, sheet_iterator=1,1
     for data in competitiveItems:
         supc_data =data[0]
         one_line = data[1]
         proposed_tp = 0
         if lowestTp > lowest_possible_tp:
             sheet.write(sheet_iterator, 24, "Yes")
             if competitionBasis=='SP':
                 proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
-                proposed_tp = proposed_sp*.09597-courierCost
+                proposed_tp = proposed_sp*.9597-courierCost
                 sheet.write(sheet_iterator, 25, round(proposed_tp))
                 sheet.write(sheet_iterator, 26, round(proposed_sp))
-                sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
             else:
                 proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
                 sheet.write(sheet_iterator, 25, round(proposed_tp))
                 sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
-                sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
         else:
             sheet.write(sheet_iterator, 24, "No")
         if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
-            sheet.write(sheet_iterator, 28, "HIGH")
+            sheet.write(sheet_iterator, 27, "HIGH")
         elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
-            sheet.write(sheet_iterator, 28, "MEDIUM")
+            sheet.write(sheet_iterator, 27, "MEDIUM")
         else:
-            sheet.write(sheet_iterator, 28, "LOW")
+            sheet.write(sheet_iterator, 27, "LOW")
         i= i+1
         sheet_iterator+=1
+def createSheetForCometitiveItemsNoInventory(competitiveNoInventoryItems,wbk):
+    sheet = wbk.add_sheet('Can Compete-No Inventory')
+    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
+    sheet.write(0, 0, "Item ID", heading_xf)
+    sheet.write(0, 1, "Category", heading_xf)
+    sheet.write(0, 2, "Product Group.", heading_xf)
+    sheet.write(0, 3, "SUPC", heading_xf)
+    sheet.write(0, 4, "Brand", heading_xf)
+    sheet.write(0, 5, "Model Name", heading_xf)
+    sheet.write(0, 6, "Model Number", heading_xf)
+    sheet.write(0, 7, "Color", heading_xf)
+    sheet.write(0, 8, "Weight", heading_xf)
+    sheet.write(0, 9, "Risky", heading_xf)
+    sheet.write(0, 10, "Our SP", heading_xf)
+    sheet.write(0, 11, "Our TP", heading_xf)
+    sheet.write(0, 12, "Our Offer Price", heading_xf)
+    sheet.write(0, 13, "Our Rank", heading_xf)
+    sheet.write(0, 14, "Lowest Seller", heading_xf)
+    sheet.write(0, 15, "Lowest SP", heading_xf)
+    sheet.write(0, 16, "Lowest TP", heading_xf)
+    sheet.write(0, 17, "Lowest Offer Price", heading_xf)
+    sheet.write(0, 18, "Inventory of Top Vendors", heading_xf)
+    sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
+    sheet.write(0, 20, "Our NLC", heading_xf)
+    sheet.write(0, 21, "Lowest Possible TP", heading_xf)
+    sheet.write(0, 22, "Lowest Possible SP", heading_xf)
+    sheet.write(0, 23, "Competition Basis ", heading_xf)
+    sheet.write(0, 24, "Can Compete", heading_xf)
+    sheet.write(0, 25, "Target TP", heading_xf)
+    sheet.write(0, 26, "Target SP", heading_xf)
+    sheet.write(0, 27, "Sales Potential", heading_xf)
+    i, sheet_iterator=1,1
+    for data in competitiveNoInventoryItems:
+        supc_data =data[0]
+        one_line = data[1]
+        courierCost = 45
+        if one_line.weight:
+            slab = int(((one_line.weight+0.05) - .001)/.5)
+        for x in range (0,slab):
+            courierCost = courierCost + 35
+        courierCost = courierCost * 1.1236
+        sheet.write(sheet_iterator, 0, one_line.item_id)
+        sheet.write(sheet_iterator, 1, one_line.category_name)
+        sheet.write(sheet_iterator, 2, one_line.product_group)
+        sheet.write(sheet_iterator, 3, supc_data.supc)
+        sheet.write(sheet_iterator, 4, one_line.brand)
+        sheet.write(sheet_iterator, 5, one_line.model_name)
+        sheet.write(sheet_iterator, 6, one_line.model_number)
+        sheet.write(sheet_iterator, 7, one_line.color)
+        sheet.write(sheet_iterator, 8, one_line.weight)
+        sheet.write(sheet_iterator, 9, one_line.risky)
+        sheet.write(sheet_iterator, 10, supc_data.ourSp)
+        sheet.write(sheet_iterator, 11, '-')
+        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
+        sheet.write(sheet_iterator, 13, supc_data.rank)
+        sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
+        sheet.write(sheet_iterator, 15, supc_data.lowestSp)
+        if one_line.parent_category ==10011:
+            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
+        else:
+            lowestTp = supc_data.lowestSp*0.9497-courierCost
+        sheet.write(sheet_iterator, 16, round(lowestTp))
+        sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
+        sheet.write(sheet_iterator, 18, supc_data.otherInventory)
+        sheet.write(sheet_iterator, 19, supc_data.ourInventory)
+        sheet.write(sheet_iterator, 20, one_line.our_nlc)
+        if supc_data.rank==1:
+            i+=1
+            sheet_iterator+=1
+            continue
+        lowest_possible_tp = one_line.our_nlc/0.988+15+6
+        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
+        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
+        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
+        competitionBasis ='TP'
+        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
+            competitionBasis ='SP'
+            sheet.write(sheet_iterator, 23, 'SP')
+        else:
+            sheet.write(sheet_iterator, 23, 'TP')
+        proposed_tp = 0
+        if lowestTp > lowest_possible_tp:
+            sheet.write(sheet_iterator, 24, "Yes")
+            if competitionBasis=='SP':
+                proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
+                proposed_tp = proposed_sp*.9597-courierCost
+                sheet.write(sheet_iterator, 25, round(proposed_tp))
+                sheet.write(sheet_iterator, 26, round(proposed_sp))
+            else:
+                proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
+                sheet.write(sheet_iterator, 25, round(proposed_tp))
+                sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
+        else:
+            sheet.write(sheet_iterator, 24, "No")
+        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
+            sheet.write(sheet_iterator, 27, "HIGH")
+        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
+            sheet.write(sheet_iterator, 27, "MEDIUM")
+        else:
+            sheet.write(sheet_iterator, 27, "LOW")
+        i= i+1
+        sheet_iterator+=1
 def main():
     parser = optparse.OptionParser()
     parser.add_option("-f", "--file", dest="filename",
                    default="ItemList.xls", type="string",
                    help="Read the item list from FILE",

Subversion Repositories SmartDukaan

(root)/trunk/PyProj/src/shop2020/model/v1/catalog/script/Snapdeal.py – Rev 9391 → 9396