| 11193 |
kshitij.so |
1 |
from elixir import *
|
|
|
2 |
from sqlalchemy.sql import or_ ,func, asc
|
|
|
3 |
from shop2020.config.client.ConfigClient import ConfigClient
|
|
|
4 |
from shop2020.model.v1.catalog.impl import DataService
|
|
|
5 |
from shop2020.model.v1.catalog.script import FlipkartScraper
|
|
|
6 |
from shop2020.model.v1.catalog.impl.DataService import FlipkartItem, MarketplaceItems, Item, \
|
|
|
7 |
Category, SourcePercentageMaster, MarketPlaceHistory, MarketPlaceUpdateHistory, MarketPlaceItemPrice, \
|
|
|
8 |
SourceCategoryPercentage, SourceItemPercentage
|
|
|
9 |
from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
|
|
|
10 |
from shop2020.thriftpy.model.v1.catalog.ttypes import CompetitionCategory, CompetitionBasis, SalesPotential,\
|
|
|
11 |
Decision, RunType
|
|
|
12 |
from shop2020.clients.CatalogClient import CatalogClient
|
|
|
13 |
from shop2020.clients.InventoryClient import InventoryClient
|
|
|
14 |
import urllib2
|
|
|
15 |
import requests
|
|
|
16 |
import time
|
|
|
17 |
from datetime import date, datetime, timedelta
|
|
|
18 |
from shop2020.utils import EmailAttachmentSender
|
|
|
19 |
from shop2020.utils.EmailAttachmentSender import get_attachment_part
|
|
|
20 |
import math
|
|
|
21 |
from operator import itemgetter
|
| 11560 |
kshitij.so |
22 |
from functools import partial
|
| 11193 |
kshitij.so |
23 |
import simplejson as json
|
|
|
24 |
import xlwt
|
|
|
25 |
import optparse
|
|
|
26 |
import sys
|
|
|
27 |
import smtplib
|
| 11560 |
kshitij.so |
28 |
import threading
|
|
|
29 |
from multiprocessing import Process
|
| 11193 |
kshitij.so |
30 |
from email.mime.text import MIMEText
|
|
|
31 |
import email
|
|
|
32 |
from email.mime.multipart import MIMEMultipart
|
|
|
33 |
import email.encoders
|
|
|
34 |
import cookielib
|
| 11561 |
kshitij.so |
35 |
from multiprocessing import Pool
|
|
|
36 |
from multiprocessing.dummy import Pool as ThreadPool
|
| 11193 |
kshitij.so |
37 |
|
|
|
38 |
config_client = ConfigClient()
|
|
|
39 |
host = config_client.get_property('staging_hostname')
|
|
|
40 |
syncPrice=config_client.get_property('sync_price_on_marketplace')
|
|
|
41 |
|
|
|
42 |
|
|
|
43 |
DataService.initialize(db_hostname=host)
|
|
|
44 |
|
|
|
45 |
inventoryMap = {}
|
|
|
46 |
itemSaleMap = {}
|
| 11562 |
kshitij.so |
47 |
scraper = FlipkartScraper.FlipkartScraper()
|
| 11615 |
kshitij.so |
48 |
categoryMap = {}
|
| 11193 |
kshitij.so |
49 |
|
|
|
50 |
class __FlipkartDetails:
|
|
|
51 |
|
|
|
52 |
def __init__(self,rank ,ourSp , secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, shippingTimeLowerLimitLowestSeller,shippingTimeUpperLimitLowestSeller, \
|
|
|
53 |
shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller, shippingTimeLowerLimitOur, shippingTimeUpperLimitOur, shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller, totalAvailableSeller, lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
|
|
|
54 |
ourBuyTrend, prefSellerBuyTrend, secondLowestSellerBuyTrend, ourCode ):
|
|
|
55 |
|
|
|
56 |
self.rank = rank
|
|
|
57 |
self.ourSp = ourSp
|
|
|
58 |
self.secondLowestSellerSp = secondLowestSellerSp
|
|
|
59 |
self.prefSellerSp = prefSellerSp
|
|
|
60 |
self.lowestSellerSp = lowestSellerSp
|
|
|
61 |
self.lowestSellerScore = lowestSellerScore
|
|
|
62 |
self.prefSellerScore = prefSellerScore
|
|
|
63 |
self.secondLowestSellerScore = secondLowestSellerScore
|
|
|
64 |
self.ourScore = ourScore
|
|
|
65 |
self.shippingTimeLowerLimitLowestSeller = shippingTimeLowerLimitLowestSeller
|
|
|
66 |
self.shippingTimeUpperLimitLowestSeller = shippingTimeUpperLimitLowestSeller
|
|
|
67 |
self.shippingTimeLowerLimitPrefSeller = shippingTimeLowerLimitPrefSeller
|
|
|
68 |
self.shippingTimeUpperLimitPrefSeller = shippingTimeUpperLimitPrefSeller
|
|
|
69 |
self.shippingTimeLowerLimitOur = shippingTimeLowerLimitOur
|
|
|
70 |
self.shippingTimeUpperLimitOur = shippingTimeUpperLimitOur
|
|
|
71 |
self.shippingTimeLowerLimitSecondLowestSeller = shippingTimeLowerLimitSecondLowestSeller
|
|
|
72 |
self.shippingTimeUpperLimitSecondLowestSeller = shippingTimeUpperLimitSecondLowestSeller
|
|
|
73 |
self.totalAvailableSeller = totalAvailableSeller
|
|
|
74 |
self.lowestSellerName = lowestSellerName
|
|
|
75 |
self.lowestSellerCode = lowestSellerCode
|
|
|
76 |
self.secondLowestSellerName = secondLowestSellerName
|
|
|
77 |
self.secondLowestSellerCode = secondLowestSellerCode
|
|
|
78 |
self.prefSellerName = prefSellerName
|
|
|
79 |
self.prefSellerCode = prefSellerCode
|
|
|
80 |
self.lowestSellerBuyTrend = lowestSellerBuyTrend
|
|
|
81 |
self.ourBuyTrend = ourBuyTrend
|
|
|
82 |
self.prefSellerBuyTrend = prefSellerBuyTrend
|
|
|
83 |
self.secondLowestSellerBuyTrend = secondLowestSellerBuyTrend
|
|
|
84 |
self.ourCode = ourCode
|
|
|
85 |
|
|
|
86 |
class __FlipkartItemInfo:
|
|
|
87 |
|
| 11581 |
kshitij.so |
88 |
def __init__(self, fkSerialNumber, nlc, courierCost, item_id, product_group, brand, model_name, model_number, color, weight, parent_category, risky, warehouseId, vatRate, runType, parent_category_name, sourcePercentage, ourFlipkartInventory, skuAtFlipkart, flipkartDetails, stateId):
|
| 11193 |
kshitij.so |
89 |
|
|
|
90 |
self.fkSerialNumber = fkSerialNumber
|
|
|
91 |
self.nlc = nlc
|
|
|
92 |
self.courierCost = courierCost
|
|
|
93 |
self.item_id = item_id
|
|
|
94 |
self.product_group = product_group
|
|
|
95 |
self.brand = brand
|
|
|
96 |
self.model_name = model_name
|
|
|
97 |
self.model_number = model_number
|
|
|
98 |
self.color = color
|
|
|
99 |
self.weight = weight
|
|
|
100 |
self.parent_category = parent_category
|
|
|
101 |
self.risky = risky
|
|
|
102 |
self.warehouseId = warehouseId
|
|
|
103 |
self.vatRate = vatRate
|
|
|
104 |
self.runType = runType
|
|
|
105 |
self.parent_category_name = parent_category_name
|
|
|
106 |
self.sourcePercentage = sourcePercentage
|
| 11560 |
kshitij.so |
107 |
self.ourFlipkartInventory = ourFlipkartInventory
|
| 11571 |
kshitij.so |
108 |
self.skuAtFlipkart = skuAtFlipkart
|
| 11581 |
kshitij.so |
109 |
self.flipkartDetails = flipkartDetails
|
|
|
110 |
self.stateId = stateId
|
| 11193 |
kshitij.so |
111 |
|
|
|
112 |
class __FlipkartPricing:
|
|
|
113 |
|
|
|
114 |
def __init__(self, ourSp, ourTp, lowestTp, lowestPossibleTp, secondLowestSellerTp, lowestPossibleSp, prefSellerTp):
|
|
|
115 |
self.ourTp = ourTp
|
|
|
116 |
self.lowestTp = lowestTp
|
|
|
117 |
self.lowestPossibleTp = lowestPossibleTp
|
|
|
118 |
self.ourSp = ourSp
|
|
|
119 |
self.secondLowestSellerTp = secondLowestSellerTp
|
|
|
120 |
self.lowestPossibleSp = lowestPossibleSp
|
|
|
121 |
self.prefSellerTp = prefSellerTp
|
|
|
122 |
|
|
|
123 |
def markReasonForMpItem(mpHistory,reason,decision):
|
|
|
124 |
mpHistory.decision = decision
|
|
|
125 |
mpHistory.reason = reason
|
|
|
126 |
|
|
|
127 |
def fetchItemsForAutoDecrease(time):
|
|
|
128 |
successfulAutoDecrease = []
|
|
|
129 |
autoDecrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
| 11615 |
kshitij.so |
130 |
.filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(or_(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE,MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP ))\
|
| 11193 |
kshitij.so |
131 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketplaceItems.autoDecrement==True).all()
|
|
|
132 |
inventory_client = InventoryClient().get_client()
|
|
|
133 |
global inventoryMap
|
|
|
134 |
inventoryMap = inventory_client.getInventorySnapshot(0)
|
|
|
135 |
for autoDecrementItem in autoDecrementItems:
|
|
|
136 |
if not autoDecrementItem.risky:
|
|
|
137 |
markReasonForMpItem(autoDecrementItem,'Item is not risky',Decision.AUTO_DECREMENT_FAILED)
|
|
|
138 |
continue
|
|
|
139 |
if math.ceil(autoDecrementItem.proposedSellingPrice) >= autoDecrementItem.ourSellingPrice:
|
|
|
140 |
markReasonForMpItem(autoDecrementItem,'Proposed SP greater than or equal to current SP',Decision.AUTO_DECREMENT_FAILED)
|
|
|
141 |
continue
|
|
|
142 |
if autoDecrementItem.proposedSellingPrice < autoDecrementItem.lowestPossibleSp:
|
|
|
143 |
markReasonForMpItem(autoDecrementItem,'Proposed SP less than lowest possible SP',Decision.AUTO_DECREMENT_FAILED)
|
|
|
144 |
continue
|
|
|
145 |
totalAvailability, totalReserved = 0,0
|
|
|
146 |
if (not inventoryMap.has_key(autoDecrementItem.item_id)):
|
|
|
147 |
markReasonForMpItem(autoDecrementItem,'Inventory info not available',Decision.AUTO_DECREMENT_FAILED)
|
|
|
148 |
continue
|
|
|
149 |
itemInventory=inventoryMap[autoDecrementItem.item_id]
|
|
|
150 |
availableMap = itemInventory.availability
|
|
|
151 |
reserveMap = itemInventory.reserved
|
|
|
152 |
for warehouse,availability in availableMap.iteritems():
|
|
|
153 |
if warehouse==16:
|
|
|
154 |
continue
|
|
|
155 |
totalAvailability = totalAvailability+availability
|
|
|
156 |
for warehouse,reserve in reserveMap.iteritems():
|
|
|
157 |
if warehouse==16:
|
|
|
158 |
continue
|
|
|
159 |
totalReserved = totalReserved+reserve
|
|
|
160 |
if (totalAvailability-totalReserved)<=0:
|
|
|
161 |
markReasonForMpItem(autoDecrementItem,'Net availability is 0',Decision.AUTO_DECREMENT_FAILED)
|
|
|
162 |
continue
|
|
|
163 |
avgSalePerDay = (itemSaleMap.get(autoDecrementItem.item_id))[2]
|
|
|
164 |
try:
|
|
|
165 |
daysOfStock = (float(totalAvailability-totalReserved))/avgSalePerDay
|
|
|
166 |
except ZeroDivisionError,e:
|
|
|
167 |
daysOfStock = float("inf")
|
|
|
168 |
if daysOfStock<2:
|
|
|
169 |
markReasonForMpItem(autoDecrementItem,'Our stock is not enough',Decision.AUTO_DECREMENT_FAILED)
|
|
|
170 |
continue
|
| 11615 |
kshitij.so |
171 |
|
|
|
172 |
if autoDecrementItem.competitiveCategory == CompetitionCategory.PREF_BUT_NOT_CHEAP:
|
|
|
173 |
avgSaleLastTwoDay = (itemSaleMap.get(autoDecrementItem.item_id))[6]
|
|
|
174 |
if avgSaleLastTwoDay >= 3:
|
|
|
175 |
markReasonForMpItem(autoDecrementItem,'Last two day avg sale is greater than 2',Decision.AUTO_DECREMENT_FAILED)
|
|
|
176 |
continue
|
| 11193 |
kshitij.so |
177 |
|
|
|
178 |
autoDecrementItem.ourEnoughStock = True
|
|
|
179 |
autoDecrementItem.decision = Decision.AUTO_DECREMENT_SUCCESS
|
|
|
180 |
autoDecrementItem.reason = 'All conditions for auto decrement true'
|
|
|
181 |
successfulAutoDecrease.append(autoDecrementItem)
|
|
|
182 |
session.commit()
|
|
|
183 |
return successfulAutoDecrease
|
|
|
184 |
|
|
|
185 |
def fetchItemsForAutoIncrease(time):
|
|
|
186 |
successfulAutoIncrease = []
|
|
|
187 |
autoIncrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
|
|
188 |
.filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX)\
|
|
|
189 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketplaceItems.autoIncrement==True).all()
|
|
|
190 |
for autoIncrementItem in autoIncrementItems:
|
|
|
191 |
if not autoIncrementItem.competitiveCategory == CompetitionCategory.BUY_BOX:
|
|
|
192 |
markReasonForMpItem(autoIncrementItem,'Category is '+CompetitionCategory._VALUES_TO_NAMES.get(autoIncrementItem.competitiveCategory),Decision.AUTO_INCREMENT_FAILED)
|
|
|
193 |
continue
|
|
|
194 |
if autoIncrementItem.totalSeller==1 and autoIncrementItem.ourRank==1:
|
|
|
195 |
markReasonForMpItem(autoIncrementItem,'We are the only seller',Decision.AUTO_INCREMENT_FAILED)
|
|
|
196 |
continue
|
|
|
197 |
if autoIncrementItem.proposedSellingPrice <= autoIncrementItem.ourSellingPrice:
|
|
|
198 |
markReasonForMpItem(autoIncrementItem,'Proposed SP less than current SP',Decision.AUTO_INCREMENT_FAILED)
|
|
|
199 |
continue
|
|
|
200 |
if autoIncrementItem.proposedSellingPrice >=10000 and autoIncrementItem.ourSellingPrice<10000:
|
|
|
201 |
markReasonForMpItem(autoIncrementItem,'Proposed SP is greater than 10,000 and current sp is less than 10,000',Decision.AUTO_INCREMENT_FAILED)
|
|
|
202 |
continue
|
|
|
203 |
if getLastDaySale(autoIncrementItem.item_id)<=2:
|
|
|
204 |
markReasonForMpItem(autoIncrementItem,'Last day sale is less than 3',Decision.AUTO_INCREMENT_FAILED)
|
|
|
205 |
continue
|
|
|
206 |
antecedentPrice = session.query(MarketPlaceHistory.ourSellingPrice).filter(MarketPlaceHistory.item_id==autoIncrementItem.item_id).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.timestamp>time-timedelta(days=1)).order_by(asc(MarketPlaceHistory.timestamp)).first()
|
|
|
207 |
if antecedentPrice is not None:
|
|
|
208 |
if float(math.ceil(autoIncrementItem.ourSellingPrice+max(10,.01*autoIncrementItem.ourSellingPrice))-math.ceil(antecedentPrice[0]+max(10,.01*antecedentPrice[0])))/math.ceil(antecedentPrice[0]+max(10,.01*antecedentPrice[0]))>.02:
|
|
|
209 |
markReasonForMpItem(autoIncrementItem,'Maximum price increase in last 24 hours should be 2%',Decision.AUTO_INCREMENT_FAILED)
|
|
|
210 |
continue
|
|
|
211 |
mpItem = MarketplaceItems.get_by(itemId=autoIncrementItem.item_id,source=OrderSource.FLIPKART)
|
|
|
212 |
if mpItem.maximumSellingPrice is not None and mpItem.maximumSellingPrice > 0:
|
|
|
213 |
if autoIncrementItem.ourSellingPrice+max(10,.01*autoIncrementItem.ourSellingPrice) > mpItem.maximumSellingPrice:
|
|
|
214 |
markReasonForMpItem(autoIncrementItem,'Price cannot exceed Maximum Selling Price',Decision.AUTO_INCREMENT_FAILED)
|
|
|
215 |
continue
|
|
|
216 |
#oosStatus = inventory_client.getOosStatusesForXDaysForItem(autoIncrementItem.item_id,0,3)
|
|
|
217 |
#count,sale,daysOfStock = 0,0,0
|
|
|
218 |
#for obj in oosStatus:
|
|
|
219 |
# if not obj.is_oos:
|
|
|
220 |
# count+=1
|
|
|
221 |
# sale = sale+obj.num_orders
|
|
|
222 |
#avgSalePerDay=0 if count==0 else (float(sale)/count)
|
|
|
223 |
totalAvailability, totalReserved = 0,0
|
|
|
224 |
if (not inventoryMap.has_key(autoIncrementItem.item_id)):
|
|
|
225 |
markReasonForMpItem(autoIncrementItem,'Inventory info not available',Decision.AUTO_INCREMENT_FAILED)
|
|
|
226 |
continue
|
|
|
227 |
itemInventory=inventoryMap[autoIncrementItem.item_id]
|
|
|
228 |
availableMap = itemInventory.availability
|
|
|
229 |
reserveMap = itemInventory.reserved
|
|
|
230 |
for warehouse,availability in availableMap.iteritems():
|
|
|
231 |
if warehouse==16:
|
|
|
232 |
continue
|
|
|
233 |
totalAvailability = totalAvailability+availability
|
|
|
234 |
for warehouse,reserve in reserveMap.iteritems():
|
|
|
235 |
if warehouse==16:
|
|
|
236 |
continue
|
|
|
237 |
totalReserved = totalReserved+reserve
|
|
|
238 |
#if (totalAvailability-totalReserved)<=0:
|
|
|
239 |
# markReasonForMpItem(autoIncrementItem,'Our stock is 0',Decision.AUTO_INCREMENT_FAILED)
|
|
|
240 |
# continue
|
|
|
241 |
avgSalePerDay = (itemSaleMap.get(autoIncrementItem.item_id))[2]
|
|
|
242 |
if (avgSalePerDay==0):
|
|
|
243 |
markReasonForMpItem(autoIncrementItem,'Average sale per day is zero',Decision.AUTO_INCREMENT_FAILED)
|
|
|
244 |
continue
|
|
|
245 |
daysOfStock = (float(totalAvailability-totalReserved))/avgSalePerDay
|
|
|
246 |
if daysOfStock>5:
|
|
|
247 |
markReasonForMpItem(autoIncrementItem,'Our stock is enough',Decision.AUTO_INCREMENT_FAILED)
|
|
|
248 |
continue
|
|
|
249 |
|
|
|
250 |
autoIncrementItem.ourEnoughStock = False
|
|
|
251 |
autoIncrementItem.decision = Decision.AUTO_INCREMENT_SUCCESS
|
|
|
252 |
autoIncrementItem.reason = 'All conditions for auto increment true'
|
|
|
253 |
successfulAutoIncrease.append(autoIncrementItem)
|
|
|
254 |
session.commit()
|
|
|
255 |
return successfulAutoIncrease
|
|
|
256 |
|
|
|
257 |
|
|
|
258 |
def commitExceptionList(exceptionList,timestamp):
|
|
|
259 |
exceptionItems=[]
|
|
|
260 |
for item in exceptionList:
|
|
|
261 |
mpHistory = MarketPlaceHistory()
|
|
|
262 |
mpHistory.item_id =item.item_id
|
|
|
263 |
mpHistory.source = OrderSource.FLIPKART
|
|
|
264 |
mpHistory.competitiveCategory = CompetitionCategory.EXCEPTION
|
|
|
265 |
mpHistory.risky = item.risky
|
|
|
266 |
mpHistory.timestamp = timestamp
|
|
|
267 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(item.runType)
|
|
|
268 |
exceptionItems.append(mpHistory)
|
|
|
269 |
session.commit()
|
|
|
270 |
return exceptionItems
|
|
|
271 |
|
|
|
272 |
def commitCantCompete(cantCompete,timestamp):
|
|
|
273 |
for item in cantCompete:
|
|
|
274 |
flipkartDetails = item[0]
|
|
|
275 |
flipkartItemInfo = item[1]
|
|
|
276 |
flipkartPricing = item[2]
|
|
|
277 |
mpItem = item[3]
|
|
|
278 |
mpHistory = MarketPlaceHistory()
|
|
|
279 |
mpHistory.item_id = flipkartItemInfo.item_id
|
|
|
280 |
mpHistory.source = OrderSource.FLIPKART
|
|
|
281 |
mpHistory.lowestTp = flipkartPricing.lowestTp
|
|
|
282 |
mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
|
|
|
283 |
mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
|
|
|
284 |
mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
|
|
|
285 |
mpHistory.ourRank = flipkartDetails.rank
|
|
|
286 |
mpHistory.competitiveCategory = CompetitionCategory.CANT_COMPETE
|
|
|
287 |
mpHistory.risky = flipkartItemInfo.risky
|
|
|
288 |
mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
|
|
|
289 |
mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
|
|
|
290 |
mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
|
|
|
291 |
mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
|
|
|
292 |
mpHistory.lowestSellerShippingTime = ''
|
|
|
293 |
mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
294 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
295 |
mpHistory.ourSellingPrice = flipkartPricing.ourSp
|
|
|
296 |
mpHistory.ourTp = flipkartPricing.ourTp
|
|
|
297 |
mpHistory.ourNlc = flipkartItemInfo.nlc
|
|
|
298 |
mpHistory.ourRating = flipkartDetails.ourScore
|
|
|
299 |
mpHistory.ourShippingTime = ''
|
|
|
300 |
mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
301 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
302 |
mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
|
|
|
303 |
mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
|
|
|
304 |
mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
|
|
|
305 |
mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
|
|
|
306 |
mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
|
|
|
307 |
mpHistory.prefferedSellerShippingTime = ''
|
|
|
308 |
mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
|
|
309 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
|
|
310 |
proposed_sp = flipkartDetails.lowestSellerSp - max(10, flipkartDetails.lowestSellerSp*0.001)
|
|
|
311 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
|
|
312 |
target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
|
|
|
313 |
mpHistory.proposedSellingPrice = round(proposed_sp,2)
|
|
|
314 |
mpHistory.proposedTp = round(proposed_tp,2)
|
|
|
315 |
mpHistory.targetNlc = round(target_nlc,2)
|
|
|
316 |
mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
|
|
|
317 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
|
|
318 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
|
|
319 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
|
|
320 |
mpHistory.timestamp = timestamp
|
|
|
321 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
|
|
322 |
session.commit()
|
|
|
323 |
|
|
|
324 |
def commitBuyBox(buyBoxItems,timestamp):
|
|
|
325 |
for item in buyBoxItems:
|
|
|
326 |
flipkartDetails = item[0]
|
|
|
327 |
flipkartItemInfo = item[1]
|
|
|
328 |
flipkartPricing = item[2]
|
|
|
329 |
mpItem = item[3]
|
|
|
330 |
mpHistory = MarketPlaceHistory()
|
|
|
331 |
mpHistory.item_id = flipkartItemInfo.item_id
|
|
|
332 |
mpHistory.source = OrderSource.FLIPKART
|
|
|
333 |
mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
|
|
|
334 |
mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
|
|
|
335 |
mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
|
|
|
336 |
mpHistory.ourRank = flipkartDetails.rank
|
|
|
337 |
mpHistory.ourSellingPrice = flipkartPricing.ourSp
|
|
|
338 |
mpHistory.ourTp = flipkartPricing.ourTp
|
|
|
339 |
mpHistory.ourNlc = flipkartItemInfo.nlc
|
|
|
340 |
mpHistory.ourRating = flipkartDetails.ourScore
|
|
|
341 |
mpHistory.ourShippingTime = ''
|
|
|
342 |
mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
343 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
344 |
mpHistory.competitiveCategory = CompetitionCategory.BUY_BOX
|
|
|
345 |
mpHistory.risky = flipkartItemInfo.risky
|
|
|
346 |
mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
|
|
|
347 |
mpHistory.lowestTp = flipkartPricing.lowestTp
|
|
|
348 |
mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
|
|
|
349 |
mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
|
|
|
350 |
mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
|
|
|
351 |
mpHistory.lowestSellerShippingTime = ''
|
|
|
352 |
mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
353 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
354 |
proposed_sp = max(flipkartDetails.secondLowestSellerSp - max((20, flipkartDetails.secondLowestSellerSp*0.002)), flipkartPricing.lowestPossibleSp)
|
|
|
355 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
|
|
356 |
#target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
|
|
|
357 |
mpHistory.proposedSellingPrice = round(proposed_sp,2)
|
|
|
358 |
mpHistory.proposedTp = round(proposed_tp,2)
|
|
|
359 |
#mpHistory.targetNlc = target_nlc
|
|
|
360 |
mpHistory.secondLowestSellerName = flipkartDetails.secondLowestSellerName
|
|
|
361 |
mpHistory.secondLowestSellerCode = flipkartDetails.secondLowestSellerCode
|
|
|
362 |
mpHistory.secondLowestSellingPrice = flipkartDetails.secondLowestSellerSp
|
|
|
363 |
mpHistory.secondLowestTp = flipkartPricing.secondLowestSellerTp
|
|
|
364 |
mpHistory.secondLowestSellerRating = flipkartDetails.secondLowestSellerScore
|
|
|
365 |
mpHistory.secondLowestSellerShippingTime = ''
|
|
|
366 |
mpHistory.secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
|
|
|
367 |
else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
|
|
|
368 |
mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
|
|
|
369 |
mpHistory.marginIncreasedPotential = proposed_tp - flipkartPricing.ourTp
|
|
|
370 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
|
|
371 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
|
|
372 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
|
|
373 |
mpHistory.timestamp = timestamp
|
|
|
374 |
session.commit()
|
|
|
375 |
|
|
|
376 |
def commitCompetitiveNoInventory(competitiveNoInventory,timestamp):
|
|
|
377 |
for item in competitiveNoInventory:
|
|
|
378 |
flipkartDetails = item[0]
|
|
|
379 |
flipkartItemInfo = item[1]
|
|
|
380 |
flipkartPricing = item[2]
|
|
|
381 |
mpItem = item[3]
|
|
|
382 |
mpHistory = MarketPlaceHistory()
|
|
|
383 |
mpHistory.item_id = flipkartItemInfo.item_id
|
|
|
384 |
mpHistory.source = OrderSource.FLIPKART
|
|
|
385 |
mpHistory.lowestTp = flipkartPricing.lowestTp
|
|
|
386 |
mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
|
|
|
387 |
mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
|
|
|
388 |
mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
|
|
|
389 |
mpHistory.ourRank = flipkartDetails.rank
|
|
|
390 |
mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE_NO_INVENTORY
|
|
|
391 |
mpHistory.risky = flipkartItemInfo.risky
|
|
|
392 |
mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
|
|
|
393 |
mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
|
|
|
394 |
mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
|
|
|
395 |
mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
|
|
|
396 |
mpHistory.lowestSellerShippingTime = ''
|
|
|
397 |
mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
398 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
399 |
mpHistory.ourSellingPrice = flipkartPricing.ourSp
|
|
|
400 |
mpHistory.ourTp = flipkartPricing.ourTp
|
|
|
401 |
mpHistory.ourNlc = flipkartItemInfo.nlc
|
|
|
402 |
mpHistory.ourRating = flipkartDetails.ourScore
|
|
|
403 |
mpHistory.ourShippingTime = ''
|
|
|
404 |
mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
405 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
406 |
mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
|
|
|
407 |
mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
|
|
|
408 |
mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
|
|
|
409 |
mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
|
|
|
410 |
mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
|
|
|
411 |
mpHistory.prefferedSellerShippingTime = ''
|
|
|
412 |
mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
|
|
413 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
|
|
414 |
proposed_sp = max(flipkartDetails.lowestSellerSp - max((10, flipkartDetails.lowestSellerSp*0.001)), flipkartPricing.lowestPossibleSp)
|
|
|
415 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
|
|
416 |
mpHistory.proposedSellingPrice = round(proposed_sp,2)
|
|
|
417 |
mpHistory.proposedTp = round(proposed_tp,2)
|
|
|
418 |
mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
|
|
|
419 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
|
|
420 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
|
|
421 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
|
|
422 |
mpHistory.timestamp = timestamp
|
|
|
423 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
|
|
424 |
session.commit()
|
|
|
425 |
|
|
|
426 |
def commitCompetitive(competitive,timestamp):
|
|
|
427 |
for item in competitive:
|
|
|
428 |
flipkartDetails = item[0]
|
|
|
429 |
flipkartItemInfo = item[1]
|
|
|
430 |
flipkartPricing = item[2]
|
|
|
431 |
mpItem = item[3]
|
|
|
432 |
mpHistory = MarketPlaceHistory()
|
|
|
433 |
mpHistory.item_id = flipkartItemInfo.item_id
|
|
|
434 |
mpHistory.source = OrderSource.FLIPKART
|
|
|
435 |
mpHistory.lowestTp = flipkartPricing.lowestTp
|
|
|
436 |
mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
|
|
|
437 |
mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
|
|
|
438 |
mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
|
|
|
439 |
mpHistory.ourRank = flipkartDetails.rank
|
|
|
440 |
mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE
|
|
|
441 |
mpHistory.risky = flipkartItemInfo.risky
|
|
|
442 |
mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
|
|
|
443 |
mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
|
|
|
444 |
mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
|
|
|
445 |
mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
|
|
|
446 |
mpHistory.lowestSellerShippingTime = ''
|
|
|
447 |
mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
448 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
449 |
mpHistory.ourSellingPrice = flipkartPricing.ourSp
|
|
|
450 |
mpHistory.ourTp = flipkartPricing.ourTp
|
|
|
451 |
mpHistory.ourNlc = flipkartItemInfo.nlc
|
|
|
452 |
mpHistory.ourRating = flipkartDetails.ourScore
|
|
|
453 |
mpHistory.ourShippingTime = ''
|
|
|
454 |
mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
455 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
456 |
mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
|
|
|
457 |
mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
|
|
|
458 |
mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
|
|
|
459 |
mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
|
|
|
460 |
mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
|
|
|
461 |
mpHistory.prefferedSellerShippingTime = ''
|
|
|
462 |
mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
|
|
463 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
|
|
464 |
proposed_sp = max(flipkartDetails.lowestSellerSp - max((10, flipkartDetails.lowestSellerSp*0.001)), flipkartPricing.lowestPossibleSp)
|
|
|
465 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
|
|
466 |
mpHistory.proposedSellingPrice = round(proposed_sp,2)
|
|
|
467 |
mpHistory.proposedTp = round(proposed_tp,2)
|
|
|
468 |
mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
|
|
|
469 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
|
|
470 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
|
|
471 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
|
|
472 |
mpHistory.timestamp = timestamp
|
|
|
473 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
|
|
474 |
session.commit()
|
|
|
475 |
|
|
|
476 |
def commitNegativeMargin(negativeMargin,timestamp):
|
|
|
477 |
for item in negativeMargin:
|
|
|
478 |
flipkartDetails = item[0]
|
|
|
479 |
flipkartItemInfo = item[1]
|
|
|
480 |
flipkartPricing = item[2]
|
|
|
481 |
mpHistory = MarketPlaceHistory()
|
|
|
482 |
mpHistory.item_id = flipkartItemInfo.item_id
|
|
|
483 |
mpHistory.source = OrderSource.FLIPKART
|
|
|
484 |
mpHistory.lowestTp = flipkartPricing.lowestTp
|
|
|
485 |
mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
|
|
|
486 |
mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
|
|
|
487 |
mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
|
|
|
488 |
mpHistory.ourRank = flipkartDetails.rank
|
|
|
489 |
mpHistory.competitiveCategory = CompetitionCategory.NEGATIVE_MARGIN
|
|
|
490 |
mpHistory.risky = flipkartItemInfo.risky
|
|
|
491 |
mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
|
|
|
492 |
mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
|
|
|
493 |
mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
|
|
|
494 |
mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
|
|
|
495 |
mpHistory.lowestSellerShippingTime = ''
|
|
|
496 |
mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
497 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
498 |
mpHistory.ourSellingPrice = flipkartPricing.ourSp
|
|
|
499 |
mpHistory.ourTp = flipkartPricing.ourTp
|
|
|
500 |
mpHistory.ourNlc = flipkartItemInfo.nlc
|
|
|
501 |
mpHistory.ourRating = flipkartDetails.ourScore
|
|
|
502 |
mpHistory.ourShippingTime = ''
|
|
|
503 |
mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
504 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
505 |
mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
|
|
|
506 |
mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
|
|
|
507 |
mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
|
|
|
508 |
mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
|
|
|
509 |
mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
|
|
|
510 |
mpHistory.prefferedSellerShippingTime = ''
|
|
|
511 |
mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
|
|
512 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
|
|
513 |
mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
|
|
|
514 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
|
|
515 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
|
|
516 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
|
|
517 |
mpHistory.timestamp = timestamp
|
|
|
518 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
|
|
519 |
session.commit()
|
|
|
520 |
|
|
|
521 |
def commitCheapButNotPref(cheapButNotPref,timestamp):
|
|
|
522 |
for item in cheapButNotPref:
|
|
|
523 |
flipkartDetails = item[0]
|
|
|
524 |
flipkartItemInfo = item[1]
|
|
|
525 |
flipkartPricing = item[2]
|
|
|
526 |
mpHistory = MarketPlaceHistory()
|
|
|
527 |
mpHistory.item_id = flipkartItemInfo.item_id
|
|
|
528 |
mpHistory.source = OrderSource.FLIPKART
|
|
|
529 |
mpHistory.lowestTp = flipkartPricing.lowestTp
|
|
|
530 |
mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
|
|
|
531 |
mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
|
|
|
532 |
mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
|
|
|
533 |
mpHistory.ourRank = flipkartDetails.rank
|
|
|
534 |
mpHistory.competitiveCategory = CompetitionCategory.CHEAP_BUT_NOT_PREF
|
|
|
535 |
mpHistory.risky = flipkartItemInfo.risky
|
|
|
536 |
mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
|
|
|
537 |
mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
|
|
|
538 |
mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
|
|
|
539 |
mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
|
|
|
540 |
mpHistory.lowestSellerShippingTime = ''
|
|
|
541 |
mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
542 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
543 |
mpHistory.ourSellingPrice = flipkartPricing.ourSp
|
|
|
544 |
mpHistory.ourTp = flipkartPricing.ourTp
|
|
|
545 |
mpHistory.ourNlc = flipkartItemInfo.nlc
|
|
|
546 |
mpHistory.ourRating = flipkartDetails.ourScore
|
|
|
547 |
mpHistory.ourShippingTime = ''
|
|
|
548 |
mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
549 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
550 |
mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
|
|
|
551 |
mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
|
|
|
552 |
mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
|
|
|
553 |
mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
|
|
|
554 |
mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
|
|
|
555 |
mpHistory.prefferedSellerShippingTime = ''
|
|
|
556 |
mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
|
|
557 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
|
|
558 |
mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
|
|
|
559 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
|
|
560 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
|
|
561 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
|
|
562 |
mpHistory.timestamp = timestamp
|
|
|
563 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
|
|
564 |
session.commit()
|
|
|
565 |
|
|
|
566 |
def commitPrefButNotCheap(prefButNotCheap,timestamp):
|
|
|
567 |
for item in prefButNotCheap:
|
|
|
568 |
flipkartDetails = item[0]
|
|
|
569 |
flipkartItemInfo = item[1]
|
|
|
570 |
flipkartPricing = item[2]
|
| 11619 |
kshitij.so |
571 |
mpItem = item[3]
|
| 11193 |
kshitij.so |
572 |
mpHistory = MarketPlaceHistory()
|
|
|
573 |
mpHistory.item_id = flipkartItemInfo.item_id
|
|
|
574 |
mpHistory.source = OrderSource.FLIPKART
|
|
|
575 |
mpHistory.lowestTp = flipkartPricing.lowestTp
|
|
|
576 |
mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
|
|
|
577 |
mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
|
|
|
578 |
mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
|
|
|
579 |
mpHistory.ourRank = flipkartDetails.rank
|
|
|
580 |
mpHistory.competitiveCategory = CompetitionCategory.PREF_BUT_NOT_CHEAP
|
|
|
581 |
mpHistory.risky = flipkartItemInfo.risky
|
|
|
582 |
mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
|
|
|
583 |
mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
|
|
|
584 |
mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
|
|
|
585 |
mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
|
|
|
586 |
mpHistory.lowestSellerShippingTime = ''
|
|
|
587 |
mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
588 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
589 |
mpHistory.ourSellingPrice = flipkartPricing.ourSp
|
|
|
590 |
mpHistory.ourTp = flipkartPricing.ourTp
|
|
|
591 |
mpHistory.ourNlc = flipkartItemInfo.nlc
|
|
|
592 |
mpHistory.ourRating = flipkartDetails.ourScore
|
|
|
593 |
mpHistory.ourShippingTime = ''
|
|
|
594 |
mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
595 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
596 |
mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
|
|
|
597 |
mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
|
|
|
598 |
mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
|
|
|
599 |
mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
|
|
|
600 |
mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
|
|
|
601 |
mpHistory.prefferedSellerShippingTime = ''
|
|
|
602 |
mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
|
|
603 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
|
|
604 |
mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
|
| 11619 |
kshitij.so |
605 |
proposed_sp = max(mpHistory.lowestSellingPrice - max((20, mpHistory.lowestSellingPrice*0.002)), mpHistory.lowestPossibleSp)
|
|
|
606 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
|
|
607 |
mpHistory.proposedSellingPrice = proposed_sp
|
|
|
608 |
mpHistory.proposedTp = proposed_tp
|
| 11193 |
kshitij.so |
609 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
|
|
610 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
|
|
611 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
|
|
612 |
mpHistory.timestamp = timestamp
|
|
|
613 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
|
|
614 |
session.commit()
|
|
|
615 |
|
|
|
616 |
def populateStuff(runType,time):
|
| 11581 |
kshitij.so |
617 |
global itemSaleMap
|
| 11615 |
kshitij.so |
618 |
global categoryMap
|
|
|
619 |
|
| 11193 |
kshitij.so |
620 |
itemInfo = []
|
|
|
621 |
if runType=='FAVOURITE':
|
|
|
622 |
items = session.query(FlipkartItem,MarketplaceItems).join((MarketplaceItems,FlipkartItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.FLIPKART).\
|
|
|
623 |
filter(or_(MarketplaceItems.autoFavourite==True, MarketplaceItems.manualFavourite==True)).all()
|
|
|
624 |
else:
|
|
|
625 |
#items = session.query(FlipkartItem,MarketplaceItems).join((MarketplaceItems,FlipkartItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
|
|
|
626 |
items = session.query(FlipkartItem,MarketplaceItems).join((MarketplaceItems,FlipkartItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
|
| 11581 |
kshitij.so |
627 |
|
|
|
628 |
inventory_client = InventoryClient().get_client()
|
|
|
629 |
|
| 11193 |
kshitij.so |
630 |
for item in items:
|
|
|
631 |
flipkart_item = item[0]
|
|
|
632 |
mp_item = item[1]
|
|
|
633 |
it = Item.query.filter_by(id=flipkart_item.item_id).one()
|
|
|
634 |
category = Category.query.filter_by(id=it.category).one()
|
|
|
635 |
parent_category = Category.query.filter_by(id=category.parent_category_id).first()
|
| 11615 |
kshitij.so |
636 |
if not categoryMap.has_key(category.id):
|
|
|
637 |
temp = []
|
| 11616 |
kshitij.so |
638 |
temp.append(category.display_name)
|
|
|
639 |
temp.append(parent_category.display_name)
|
| 11615 |
kshitij.so |
640 |
categoryMap[category.id] = temp
|
| 11193 |
kshitij.so |
641 |
sip = SourceItemPercentage.query.filter(SourceItemPercentage.item_id==it.id).filter(SourceItemPercentage.source==OrderSource.FLIPKART).filter(SourceItemPercentage.startDate<=time).filter(SourceItemPercentage.expiryDate>=time).first()
|
|
|
642 |
sourcePercentage = None
|
|
|
643 |
if sip is not None:
|
|
|
644 |
sourcePercentage = sip
|
|
|
645 |
else:
|
|
|
646 |
scp = SourceCategoryPercentage.query.filter(SourceCategoryPercentage.category_id==it.category).filter(SourceCategoryPercentage.source==OrderSource.FLIPKART).filter(SourceCategoryPercentage.startDate<=time).filter(SourceCategoryPercentage.expiryDate>=time).first()
|
|
|
647 |
if scp is not None:
|
|
|
648 |
sourcePercentage = scp
|
|
|
649 |
else:
|
|
|
650 |
spm = SourcePercentageMaster.get_by(source=OrderSource.FLIPKART)
|
|
|
651 |
sourcePercentage = spm
|
| 11581 |
kshitij.so |
652 |
|
|
|
653 |
warehouse = inventory_client.getWarehouse(flipkart_item.warehouseId)
|
|
|
654 |
itemSaleList = []
|
|
|
655 |
oosForAllSources = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, 0, 3)
|
|
|
656 |
oosForFlipkart = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 5)
|
|
|
657 |
oosForFlipkartLastDay = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 1)
|
| 11615 |
kshitij.so |
658 |
oosForFlipkartTwoDay = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 2)
|
| 11581 |
kshitij.so |
659 |
itemSaleList.append(oosForAllSources)
|
|
|
660 |
itemSaleList.append(oosForFlipkart)
|
|
|
661 |
itemSaleList.append(calculateAverageSale(oosForAllSources))
|
|
|
662 |
itemSaleList.append(calculateAverageSale(oosForFlipkart))
|
|
|
663 |
itemSaleList.append(calculateAverageSale(oosForFlipkartLastDay))
|
|
|
664 |
itemSaleList.append(calculateTotalSale(oosForFlipkart))
|
| 11615 |
kshitij.so |
665 |
itemSaleList.append(calculateAverageSale(oosForFlipkartTwoDay))
|
| 11581 |
kshitij.so |
666 |
itemSaleMap[flipkart_item.item_id]=itemSaleList
|
|
|
667 |
|
| 11560 |
kshitij.so |
668 |
# try:
|
|
|
669 |
# request_url = "https://api.flipkart.net/sellers/skus/%s/listings"%(str(flipkart_item.skuAtFlipkart))
|
|
|
670 |
# r = requests.get(request_url, auth=('m2z93iskuj81qiid', '0c7ab6a5-98c0-4cdc-8be3-72c591e0add4'))
|
|
|
671 |
# print "Inventory info",r.json()
|
|
|
672 |
# stock_count = int((r.json()['attributeValues'])['stock_count'])
|
|
|
673 |
# except:
|
|
|
674 |
# stock_count = 0
|
| 11581 |
kshitij.so |
675 |
flipkartItemInfo = __FlipkartItemInfo(flipkart_item.flipkartSerialNumber, flipkart_item.maxNlc,mp_item.courierCost, it.id, it.product_group, it.brand, it.model_name, it.model_number, it.color, it.weight, category.parent_category_id, it.risky, flipkart_item.warehouseId, None, runType, parent_category.display_name,sourcePercentage,None,flipkart_item.skuAtFlipkart,None,warehouse.stateId)
|
| 11193 |
kshitij.so |
676 |
itemInfo.append(flipkartItemInfo)
|
|
|
677 |
return itemInfo
|
|
|
678 |
|
|
|
679 |
def fetchDetails(flipkartSerialNumber,scraper):
|
|
|
680 |
url = "http://www.flipkart.com/ps/%s"%(flipkartSerialNumber)
|
|
|
681 |
#url = "http://www.flipkart.com/ps/MOBDTXVZXVY3GFG8"
|
|
|
682 |
scraper.read(url)
|
|
|
683 |
vendorsData = scraper.createData()
|
|
|
684 |
print "Vendor data for flipkartSerialNumber",flipkartSerialNumber
|
|
|
685 |
print "Json Data",vendorsData
|
| 11581 |
kshitij.so |
686 |
sortedVendorsData = []
|
| 11193 |
kshitij.so |
687 |
sortedVendorsData = sorted(vendorsData, key=itemgetter('sellingPrice'))
|
|
|
688 |
rank ,ourSp, iterator, secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, shippingTimeLowerLimitLowestSeller,shippingTimeUpperLimitLowestSeller, \
|
|
|
689 |
shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller, shippingTimeLowerLimitOur, shippingTimeUpperLimitOur, shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller, totalAvailableSeller= (0,)*19
|
|
|
690 |
lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
|
|
|
691 |
ourBuyTrend, prefSellerBuyTrend, secondLowestSellerBuyTrend, ourCode = ('',)*11
|
|
|
692 |
for data in sortedVendorsData:
|
|
|
693 |
if iterator == 0:
|
|
|
694 |
lowestSellerName = data['sellerName']
|
|
|
695 |
lowestSellerScore = data['sellerScore']
|
|
|
696 |
lowestSellerCode = data['sellerCode']
|
|
|
697 |
lowestSellerSp = data['sellingPrice']
|
|
|
698 |
lowestSellerBuyTrend = data['buyTrend']
|
|
|
699 |
try:
|
|
|
700 |
shippingTimeLowerLimitLowestSeller, shippingTimeUpperLimitLowestSeller = data['shippingTime'].split('-')
|
|
|
701 |
except ValueError:
|
|
|
702 |
shippingTimeLowerLimitLowestSeller = int(data['shippingTime'])
|
|
|
703 |
|
|
|
704 |
if iterator ==1:
|
|
|
705 |
secondLowestSellerName = data['sellerName']
|
|
|
706 |
secondLowestSellerScore = data['sellerScore']
|
|
|
707 |
secondLowestSellerCode = data['sellerCode']
|
|
|
708 |
secondLowestSellerSp = data['sellingPrice']
|
|
|
709 |
secondLowestSellerBuyTrend = data['buyTrend']
|
|
|
710 |
try:
|
|
|
711 |
shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller = data['shippingTime'].split('-')
|
|
|
712 |
except ValueError:
|
|
|
713 |
shippingTimeLowerLimitSecondLowestSeller = int(data['shippingTime'])
|
|
|
714 |
|
|
|
715 |
if data['sellerName'] == 'Saholic':
|
|
|
716 |
ourScore = data['sellerScore']
|
|
|
717 |
ourCode = data['sellerCode']
|
|
|
718 |
ourSp = data['sellingPrice']
|
|
|
719 |
ourBuyTrend = data['buyTrend']
|
|
|
720 |
try:
|
|
|
721 |
shippingTimeLowerLimitOur, shippingTimeUpperLimitOur = data['shippingTime'].split('-')
|
|
|
722 |
except ValueError:
|
|
|
723 |
shippingTimeLowerLimitOur = int(data['shippingTime'])
|
|
|
724 |
rank = iterator + 1
|
|
|
725 |
|
|
|
726 |
if data['buyTrend'] in ('PrefCheap','PrefNCheap',''):
|
|
|
727 |
prefSellerName = data['sellerName']
|
|
|
728 |
prefSellerScore = data['sellerScore']
|
|
|
729 |
prefSellerCode = data['sellerCode']
|
|
|
730 |
prefSellerSp = data['sellingPrice']
|
|
|
731 |
prefSellerBuyTrend = data['buyTrend']
|
|
|
732 |
try:
|
|
|
733 |
shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller = data['shippingTime'].split('-')
|
|
|
734 |
except ValueError:
|
|
|
735 |
shippingTimeLowerLimitPrefSeller = int(data['shippingTime'])
|
|
|
736 |
|
|
|
737 |
iterator+=1
|
|
|
738 |
flipkartDetails = __FlipkartDetails(rank ,ourSp , secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, int(shippingTimeLowerLimitLowestSeller),int(shippingTimeUpperLimitLowestSeller), \
|
|
|
739 |
int(shippingTimeLowerLimitPrefSeller), int(shippingTimeUpperLimitPrefSeller), int(shippingTimeLowerLimitOur), int(shippingTimeUpperLimitOur), int(shippingTimeLowerLimitSecondLowestSeller), int(shippingTimeUpperLimitSecondLowestSeller), len(sortedVendorsData), lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
|
|
|
740 |
ourBuyTrend, prefSellerBuyTrend, secondLowestSellerBuyTrend, ourCode)
|
|
|
741 |
return flipkartDetails
|
|
|
742 |
|
|
|
743 |
def calculateAverageSale(oosStatus):
|
|
|
744 |
count,sale = 0,0
|
|
|
745 |
for obj in oosStatus:
|
|
|
746 |
if not obj.is_oos:
|
|
|
747 |
count+=1
|
|
|
748 |
sale = sale+obj.num_orders
|
|
|
749 |
avgSalePerDay=0 if count==0 else (float(sale)/count)
|
|
|
750 |
return round(avgSalePerDay,2)
|
|
|
751 |
|
|
|
752 |
def calculateTotalSale(oosStatus):
|
|
|
753 |
sale = 0
|
|
|
754 |
for obj in oosStatus:
|
|
|
755 |
if not obj.is_oos:
|
|
|
756 |
sale = sale+obj.num_orders
|
|
|
757 |
return sale
|
|
|
758 |
|
|
|
759 |
def getNetAvailability(itemInventory):
|
|
|
760 |
totalAvailability, totalReserved = 0,0
|
|
|
761 |
availableMap = itemInventory.availability
|
|
|
762 |
reserveMap = itemInventory.reserved
|
|
|
763 |
for warehouse,availability in availableMap.iteritems():
|
|
|
764 |
if warehouse==16:
|
|
|
765 |
continue
|
|
|
766 |
totalAvailability = totalAvailability+availability
|
|
|
767 |
for warehouse,reserve in reserveMap.iteritems():
|
|
|
768 |
if warehouse==16:
|
|
|
769 |
continue
|
|
|
770 |
totalReserved = totalReserved+reserve
|
|
|
771 |
return totalAvailability - totalReserved
|
|
|
772 |
|
|
|
773 |
def getOosString(oosStatus):
|
|
|
774 |
lastNdaySale=""
|
|
|
775 |
for obj in oosStatus:
|
|
|
776 |
if obj.is_oos:
|
|
|
777 |
lastNdaySale += "X-"
|
|
|
778 |
else:
|
|
|
779 |
lastNdaySale += str(obj.num_orders) + "-"
|
|
|
780 |
return lastNdaySale[:-1]
|
|
|
781 |
|
|
|
782 |
def getLastDaySale(itemId):
|
|
|
783 |
return (itemSaleMap.get(itemId))[4]
|
|
|
784 |
|
|
|
785 |
def getSalesPotential(lowestSellingPrice,ourNlc):
|
|
|
786 |
if lowestSellingPrice - ourNlc < 0:
|
|
|
787 |
return 'HIGH'
|
|
|
788 |
elif (float(lowestSellingPrice - ourNlc))/lowestSellingPrice >=0 and (float(lowestSellingPrice - ourNlc))/lowestSellingPrice <=.02:
|
|
|
789 |
return 'MEDIUM'
|
|
|
790 |
else:
|
|
|
791 |
return 'LOW'
|
|
|
792 |
|
| 11571 |
kshitij.so |
793 |
def decideCategory(itemInfo):
|
| 11193 |
kshitij.so |
794 |
global itemSaleMap
|
| 11560 |
kshitij.so |
795 |
|
| 11571 |
kshitij.so |
796 |
cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap = [],[],[],[],[],[],[],[]
|
|
|
797 |
|
| 11193 |
kshitij.so |
798 |
catalog_client = CatalogClient().get_client()
|
|
|
799 |
|
| 11571 |
kshitij.so |
800 |
for val in itemInfo:
|
|
|
801 |
spm = val.sourcePercentage
|
|
|
802 |
flipkartDetails = val.flipkartDetails
|
| 11581 |
kshitij.so |
803 |
if (flipkartDetails is None or flipkartDetails.totalAvailableSeller==0):
|
| 11571 |
kshitij.so |
804 |
exceptionItems.append(val)
|
|
|
805 |
continue
|
|
|
806 |
|
|
|
807 |
mpItem = MarketplaceItems.get_by(itemId=val.item_id,source=OrderSource.FLIPKART)
|
|
|
808 |
if flipkartDetails.rank==0:
|
|
|
809 |
flipkartDetails.ourSp = mpItem.currentSp
|
|
|
810 |
ourSp = mpItem.currentSp
|
|
|
811 |
else:
|
|
|
812 |
ourSp = flipkartDetails.ourSp
|
| 11581 |
kshitij.so |
813 |
vatRate = catalog_client.getVatPercentageForItem(val.item_id, val.stateId, flipkartDetails.ourSp)
|
| 11571 |
kshitij.so |
814 |
val.vatRate = vatRate
|
|
|
815 |
if (flipkartDetails.ourBuyTrend == 'PrefCheap') or (flipkartDetails.rank==1 and flipkartDetails.totalAvailableSeller==1):
|
|
|
816 |
temp=[]
|
|
|
817 |
temp.append(flipkartDetails)
|
|
|
818 |
temp.append(val)
|
|
|
819 |
secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
|
|
|
820 |
prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
|
|
|
821 |
flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
|
|
|
822 |
temp.append(flipkartPricing)
|
|
|
823 |
temp.append(mpItem)
|
|
|
824 |
buyBoxItems.append(temp)
|
|
|
825 |
continue
|
|
|
826 |
|
|
|
827 |
if (flipkartDetails.ourBuyTrend == 'PrefNCheap'):
|
|
|
828 |
temp=[]
|
|
|
829 |
temp.append(flipkartDetails)
|
|
|
830 |
temp.append(val)
|
| 11618 |
kshitij.so |
831 |
secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getSecondLowestSellerTp(flipkartDetails,val,spm,False)
|
| 11615 |
kshitij.so |
832 |
lowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
|
| 11571 |
kshitij.so |
833 |
prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
|
|
|
834 |
flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
|
|
|
835 |
temp.append(flipkartPricing)
|
|
|
836 |
temp.append(mpItem)
|
|
|
837 |
prefButNotCheap.append(temp)
|
|
|
838 |
continue
|
|
|
839 |
|
|
|
840 |
if (flipkartDetails.ourBuyTrend == 'NPrefCheap') and (flipkartDetails.rank==1):
|
|
|
841 |
temp=[]
|
|
|
842 |
temp.append(flipkartDetails)
|
|
|
843 |
temp.append(val)
|
| 11615 |
kshitij.so |
844 |
secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
|
| 11571 |
kshitij.so |
845 |
prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
|
| 11615 |
kshitij.so |
846 |
flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
|
| 11571 |
kshitij.so |
847 |
temp.append(flipkartPricing)
|
|
|
848 |
temp.append(mpItem)
|
|
|
849 |
cheapButNotPref.append(temp)
|
|
|
850 |
continue
|
|
|
851 |
|
|
|
852 |
lowestTp = getOtherTp(flipkartDetails,val,spm,False)
|
|
|
853 |
ourTp = getOurTp(flipkartDetails,val,spm,mpItem)
|
|
|
854 |
lowestPossibleTp = getLowestPossibleTp(flipkartDetails,val,spm,mpItem)
|
|
|
855 |
lowestPossibleSp = getLowestPossibleSp(flipkartDetails,val,spm,mpItem)
|
|
|
856 |
prefSellerTp = getOtherTp(flipkartDetails,val,spm,True)
|
|
|
857 |
|
|
|
858 |
if (ourTp<lowestPossibleTp):
|
|
|
859 |
temp=[]
|
|
|
860 |
temp.append(flipkartDetails)
|
|
|
861 |
temp.append(val)
|
|
|
862 |
flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,None,None)
|
|
|
863 |
temp.append(flipkartPricing)
|
|
|
864 |
negativeMargin.append(temp)
|
|
|
865 |
continue
|
|
|
866 |
|
|
|
867 |
if (flipkartDetails.lowestSellerSp > lowestPossibleSp) and val.ourFlipkartInventory!=0:
|
|
|
868 |
type(val.ourFlipkartInventory)
|
|
|
869 |
temp=[]
|
|
|
870 |
temp.append(flipkartDetails)
|
|
|
871 |
temp.append(val)
|
|
|
872 |
flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,lowestPossibleSp,prefSellerTp)
|
|
|
873 |
temp.append(flipkartPricing)
|
|
|
874 |
temp.append(mpItem)
|
|
|
875 |
competitive.append(temp)
|
|
|
876 |
continue
|
|
|
877 |
|
|
|
878 |
if (flipkartDetails.lowestSellerSp) > lowestPossibleSp and val.ourFlipkartInventory==0:
|
|
|
879 |
temp=[]
|
|
|
880 |
temp.append(flipkartDetails)
|
|
|
881 |
temp.append(val)
|
|
|
882 |
flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,lowestPossibleSp,prefSellerTp)
|
|
|
883 |
temp.append(flipkartPricing)
|
|
|
884 |
temp.append(mpItem)
|
|
|
885 |
competitiveNoInventory.append(temp)
|
|
|
886 |
continue
|
|
|
887 |
|
| 11193 |
kshitij.so |
888 |
temp=[]
|
|
|
889 |
temp.append(flipkartDetails)
|
|
|
890 |
temp.append(val)
|
|
|
891 |
flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,lowestPossibleSp,prefSellerTp)
|
|
|
892 |
temp.append(flipkartPricing)
|
|
|
893 |
temp.append(mpItem)
|
| 11571 |
kshitij.so |
894 |
cantCompete.append(temp)
|
| 11193 |
kshitij.so |
895 |
|
| 11571 |
kshitij.so |
896 |
return cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap
|
| 11193 |
kshitij.so |
897 |
|
|
|
898 |
def getOtherTp(flipkartDetails,val,spm,prefferedSeller):
|
|
|
899 |
if val.parent_category==10011 or val.parent_category==12001:
|
|
|
900 |
commissionPercentage = spm.competitorCommissionAccessory
|
|
|
901 |
else:
|
|
|
902 |
commissionPercentage = spm.competitorCommissionOther
|
|
|
903 |
if flipkartDetails.rank==1 and not prefferedSeller:
|
|
|
904 |
otherTp = flipkartDetails.secondLowestSellerSp- flipkartDetails.secondLowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
|
|
|
905 |
return round(otherTp,2)
|
|
|
906 |
if prefferedSeller:
|
|
|
907 |
otherTp = flipkartDetails.prefSellerSp- flipkartDetails.prefSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
|
|
|
908 |
return round(otherTp,2)
|
|
|
909 |
otherTp = flipkartDetails.lowestSellerSp- flipkartDetails.lowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
|
|
|
910 |
return round(otherTp,2)
|
| 11618 |
kshitij.so |
911 |
|
|
|
912 |
def getSecondLowestSellerTp(flipkartDetails,val,spm,prefferedSeller):
|
|
|
913 |
if val.parent_category==10011 or val.parent_category==12001:
|
|
|
914 |
commissionPercentage = spm.competitorCommissionAccessory
|
|
|
915 |
else:
|
|
|
916 |
commissionPercentage = spm.competitorCommissionOther
|
|
|
917 |
otherTp = flipkartDetails.secondLowestSellerSp- flipkartDetails.secondLowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
|
|
|
918 |
return round(otherTp,2)
|
| 11193 |
kshitij.so |
919 |
|
|
|
920 |
def getLowestPossibleTp(flipkartDetails,val,spm,mpItem):
|
|
|
921 |
if flipkartDetails.rank==0:
|
|
|
922 |
return mpItem.minimumPossibleTp
|
|
|
923 |
vat = (flipkartDetails.ourSp/(1+(val.vatRate/100))-(val.nlc/(1+(val.vatRate/100))))*(val.vatRate/100);
|
|
|
924 |
inHouseCost = 15+vat+(mpItem.returnProvision/100)*flipkartDetails.ourSp+mpItem.otherCost;
|
|
|
925 |
lowest_possible_tp = val.nlc+inHouseCost;
|
|
|
926 |
return round(lowest_possible_tp,2)
|
|
|
927 |
|
|
|
928 |
def getOurTp(flipkartDetails,val,spm,mpItem):
|
|
|
929 |
if flipkartDetails.rank==0:
|
|
|
930 |
return mpItem.currentTp
|
|
|
931 |
ourTp = flipkartDetails.ourSp- flipkartDetails.ourSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))
|
|
|
932 |
return round(ourTp,2)
|
|
|
933 |
|
|
|
934 |
def getLowestPossibleSp(flipkartDetails,val,spm,mpItem):
|
|
|
935 |
if flipkartDetails.rank==0:
|
|
|
936 |
return mpItem.minimumPossibleSp
|
|
|
937 |
lowestPossibleSp = (val.nlc+(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(val.vatRate/100))+(15+mpItem.otherCost)*(1+(val.vatRate)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(val.vatRate)/100)-(mpItem.returnProvision/100)*(1+(val.vatRate)/100));
|
|
|
938 |
return round(lowestPossibleSp,2)
|
|
|
939 |
|
|
|
940 |
def getTargetTp(targetSp,mpItem):
|
|
|
941 |
targetTp = targetSp- targetSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))
|
|
|
942 |
return round(targetTp,2)
|
|
|
943 |
|
|
|
944 |
def getTargetSp(targetTp,mpItem,ourSp):
|
|
|
945 |
targetSp = float(targetTp+(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100)))/(1-((mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))))
|
|
|
946 |
return round(targetSp,2)
|
|
|
947 |
|
| 11623 |
kshitij.so |
948 |
def getNewLowestPossibleTp(mpItem,nlc,vatRate,proposedSellingPrice):
|
|
|
949 |
vat = (proposedSellingPrice/(1+(vatRate/100))-(nlc/(1+(vatRate/100))))*(vatRate/100);
|
|
|
950 |
inHouseCost = 15+vat+(mpItem.returnProvision/100)*proposedSellingPrice+mpItem.otherCost;
|
|
|
951 |
lowest_possible_tp = nlc+inHouseCost;
|
|
|
952 |
return round(lowest_possible_tp,2)
|
|
|
953 |
|
|
|
954 |
def getNewOurTp(mpItem,proposedSellingPrice):
|
| 11624 |
kshitij.so |
955 |
ourTp = proposedSellingPrice- proposedSellingPrice*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))
|
| 11623 |
kshitij.so |
956 |
return round(ourTp,2)
|
|
|
957 |
|
|
|
958 |
def getNewLowestPossibleSp(mpItem,nlc,vatRate):
|
| 11624 |
kshitij.so |
959 |
lowestPossibleSp = (nlc+(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(vatRate/100))+(15+mpItem.otherCost)*(1+(vatRate)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(vatRate)/100)-(mpItem.returnProvision/100)*(1+(vatRate)/100));
|
| 11623 |
kshitij.so |
960 |
return round(lowestPossibleSp,2)
|
|
|
961 |
|
|
|
962 |
|
| 11193 |
kshitij.so |
963 |
def markAutoFavourite():
|
|
|
964 |
previouslyAutoFav = []
|
|
|
965 |
nowAutoFav = []
|
|
|
966 |
marketplaceItems = session.query(MarketplaceItems).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
|
|
|
967 |
fromDate = datetime.now()-timedelta(days = 3, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
|
|
|
968 |
toDate = datetime.now()-timedelta(days = 0, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
|
| 11615 |
kshitij.so |
969 |
items = session.query(MarketPlaceHistory.item_id,func.max(MarketPlaceHistory.timestamp)).group_by(MarketPlaceHistory.item_id).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.timestamp.between (fromDate,toDate)).filter(or_(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX,MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP)).all()
|
| 11193 |
kshitij.so |
970 |
toUpdate = [key for key, value in itemSaleMap.items() if value[5] >= 1]
|
|
|
971 |
buyBoxLast3days = []
|
|
|
972 |
for item in items:
|
|
|
973 |
buyBoxLast3days.append(item[0])
|
|
|
974 |
for marketplaceItem in marketplaceItems:
|
|
|
975 |
reason = ""
|
|
|
976 |
toMark = False
|
|
|
977 |
if marketplaceItem.itemId in toUpdate:
|
|
|
978 |
toMark = True
|
|
|
979 |
reason+="Total sale is greater than 1 for last five days (Flipkart)."
|
|
|
980 |
if marketplaceItem.itemId in buyBoxLast3days:
|
|
|
981 |
toMark = True
|
|
|
982 |
reason+="Item is present in buy box in last 3 days"
|
|
|
983 |
if not marketplaceItem.autoFavourite:
|
|
|
984 |
print "Item is not under auto favourite"
|
|
|
985 |
if toMark:
|
|
|
986 |
temp=[]
|
|
|
987 |
temp.append(marketplaceItem.itemId)
|
|
|
988 |
temp.append(reason)
|
|
|
989 |
nowAutoFav.append(temp)
|
|
|
990 |
if (not toMark) and marketplaceItem.autoFavourite:
|
|
|
991 |
previouslyAutoFav.append(marketplaceItem.itemId)
|
|
|
992 |
marketplaceItem.autoFavourite = toMark
|
|
|
993 |
session.commit()
|
|
|
994 |
return previouslyAutoFav, nowAutoFav
|
|
|
995 |
|
| 11615 |
kshitij.so |
996 |
def write_report(previousAutoFav, nowAutoFav,timestamp, runType):
|
| 11193 |
kshitij.so |
997 |
wbk = xlwt.Workbook()
|
|
|
998 |
sheet = wbk.add_sheet('Can\'t Compete')
|
|
|
999 |
xstr = lambda s: s or ""
|
|
|
1000 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1001 |
|
|
|
1002 |
excel_integer_format = '0'
|
|
|
1003 |
integer_style = xlwt.XFStyle()
|
|
|
1004 |
integer_style.num_format_str = excel_integer_format
|
|
|
1005 |
|
|
|
1006 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
1007 |
sheet.write(0, 1, "Category", heading_xf)
|
|
|
1008 |
sheet.write(0, 2, "Product Group.", heading_xf)
|
|
|
1009 |
sheet.write(0, 3, "FK Serial Number", heading_xf)
|
|
|
1010 |
sheet.write(0, 4, "Brand", heading_xf)
|
|
|
1011 |
sheet.write(0, 5, "Product Name", heading_xf)
|
|
|
1012 |
sheet.write(0, 6, "Weight", heading_xf)
|
|
|
1013 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
|
|
1014 |
sheet.write(0, 8, "Risky", heading_xf)
|
|
|
1015 |
sheet.write(0, 9, "Our Rating", heading_xf)
|
|
|
1016 |
sheet.write(0, 10, "Our Shipping Time", heading_xf)
|
|
|
1017 |
sheet.write(0, 11, "Our Rank", heading_xf)
|
|
|
1018 |
sheet.write(0, 12, "Our SP", heading_xf)
|
|
|
1019 |
sheet.write(0, 13, "Our TP", heading_xf)
|
|
|
1020 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
|
|
1021 |
sheet.write(0, 15, "Lowest Seller Rating", heading_xf)
|
|
|
1022 |
sheet.write(0, 16, "Lowest Seller Shipping Time", heading_xf)
|
|
|
1023 |
sheet.write(0, 17, "Lowest Seller SP", heading_xf)
|
|
|
1024 |
sheet.write(0, 18, "Lowest Seller TP", heading_xf)
|
|
|
1025 |
sheet.write(0, 19, "Preffered Seller", heading_xf)
|
|
|
1026 |
sheet.write(0, 20, "Preffered Seller Rating", heading_xf)
|
|
|
1027 |
sheet.write(0, 21, "Preffered Seller Shipping Time", heading_xf)
|
|
|
1028 |
sheet.write(0, 22, "Preffer Seller SP", heading_xf)
|
|
|
1029 |
sheet.write(0, 23, "Preffered Seller TP", heading_xf)
|
|
|
1030 |
sheet.write(0, 24, "Our Flipkart Inventory", heading_xf)
|
|
|
1031 |
sheet.write(0, 25, "Our Net Availability",heading_xf)
|
|
|
1032 |
sheet.write(0, 26, "Last Five Day Sale", heading_xf)
|
|
|
1033 |
sheet.write(0, 27, "Average Sale", heading_xf)
|
|
|
1034 |
sheet.write(0, 28, "Our NLC", heading_xf)
|
|
|
1035 |
sheet.write(0, 29, "Lowest Possible SP", heading_xf)
|
|
|
1036 |
sheet.write(0, 30, "Lowest Possible TP", heading_xf)
|
|
|
1037 |
sheet.write(0, 31, "Target SP", heading_xf)
|
|
|
1038 |
sheet.write(0, 32, "Target TP", heading_xf)
|
|
|
1039 |
sheet.write(0, 33, "Target NLC", heading_xf)
|
|
|
1040 |
sheet.write(0, 34, "Sales Potential", heading_xf)
|
|
|
1041 |
sheet.write(0, 35, "Total Seller", heading_xf)
|
|
|
1042 |
sheet_iterator = 1
|
| 11620 |
kshitij.so |
1043 |
canCompeteItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
|
| 11622 |
kshitij.so |
1044 |
.join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
|
|
1045 |
.join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
|
|
1046 |
.join((Item,MarketPlaceHistory.item_id==Item.id))\
|
| 11615 |
kshitij.so |
1047 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
|
|
1048 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
|
|
|
1049 |
for item in canCompeteItems:
|
|
|
1050 |
mpHistory = item[0]
|
|
|
1051 |
flipkartItem = item[1]
|
|
|
1052 |
mpItem = item[2]
|
|
|
1053 |
catItem = item[3]
|
|
|
1054 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
|
|
1055 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
|
|
1056 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
|
|
1057 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
|
|
1058 |
sheet.write(sheet_iterator,4,catItem.brand)
|
|
|
1059 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
|
|
1060 |
sheet.write(sheet_iterator,6,catItem.weight)
|
|
|
1061 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
|
|
1062 |
sheet.write(sheet_iterator,8,catItem.risky)
|
|
|
1063 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
|
|
1064 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
1065 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
1066 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
|
|
1067 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
|
|
1068 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
|
|
1069 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
|
|
1070 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
|
|
1071 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
|
|
1072 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
1073 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
1074 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
|
|
1075 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
|
|
1076 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
|
|
1077 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerName)
|
|
|
1078 |
sheet.write(sheet_iterator,20,mpHistory.prefferedSellerRating)
|
|
|
1079 |
# prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
|
|
1080 |
# else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
|
|
1081 |
sheet.write(sheet_iterator,21,mpHistory.prefferedSellerShippingTime)
|
|
|
1082 |
sheet.write(sheet_iterator,22,mpHistory.prefferedSellerSellingPrice)
|
|
|
1083 |
sheet.write(sheet_iterator,23,mpHistory.prefferedSellerTp)
|
|
|
1084 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
|
|
1085 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 11193 |
kshitij.so |
1086 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
|
|
1087 |
else:
|
| 11615 |
kshitij.so |
1088 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
|
|
1089 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
|
|
1090 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
|
|
1091 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
|
|
1092 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
|
|
1093 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
|
|
1094 |
proposed_sp = mpHistory.lowestSellingPrice - max(10, mpHistory.lowestSellingPrice*0.001)
|
| 11193 |
kshitij.so |
1095 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 11615 |
kshitij.so |
1096 |
target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
|
| 11193 |
kshitij.so |
1097 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
|
|
1098 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
|
|
1099 |
sheet.write(sheet_iterator, 33, target_nlc)
|
| 11615 |
kshitij.so |
1100 |
sheet.write(sheet_iterator, 34, getSalesPotential(mpHistory.lowestSellingPrice,mpHistory.ourNlc))
|
|
|
1101 |
sheet.write(sheet_iterator, 35, mpHistory.totalSeller)
|
| 11193 |
kshitij.so |
1102 |
sheet_iterator+=1
|
| 11615 |
kshitij.so |
1103 |
|
|
|
1104 |
canCompeteItems[:] = []
|
|
|
1105 |
|
| 11193 |
kshitij.so |
1106 |
|
|
|
1107 |
sheet = wbk.add_sheet('Pref and Cheap')
|
|
|
1108 |
|
|
|
1109 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1110 |
|
|
|
1111 |
excel_integer_format = '0'
|
|
|
1112 |
integer_style = xlwt.XFStyle()
|
|
|
1113 |
integer_style.num_format_str = excel_integer_format
|
|
|
1114 |
xstr = lambda s: s or ""
|
|
|
1115 |
|
|
|
1116 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1117 |
|
|
|
1118 |
excel_integer_format = '0'
|
|
|
1119 |
integer_style = xlwt.XFStyle()
|
|
|
1120 |
integer_style.num_format_str = excel_integer_format
|
|
|
1121 |
|
|
|
1122 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
1123 |
sheet.write(0, 1, "Category", heading_xf)
|
|
|
1124 |
sheet.write(0, 2, "Product Group.", heading_xf)
|
|
|
1125 |
sheet.write(0, 3, "FK Serial Number", heading_xf)
|
|
|
1126 |
sheet.write(0, 4, "Brand", heading_xf)
|
|
|
1127 |
sheet.write(0, 5, "Product Name", heading_xf)
|
|
|
1128 |
sheet.write(0, 6, "Weight", heading_xf)
|
|
|
1129 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
|
|
1130 |
sheet.write(0, 8, "Risky", heading_xf)
|
|
|
1131 |
sheet.write(0, 9, "Our Rating", heading_xf)
|
|
|
1132 |
sheet.write(0, 10, "Our Shipping Time", heading_xf)
|
|
|
1133 |
sheet.write(0, 11, "Our Rank", heading_xf)
|
|
|
1134 |
sheet.write(0, 12, "Our SP", heading_xf)
|
|
|
1135 |
sheet.write(0, 13, "Our TP", heading_xf)
|
|
|
1136 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
|
|
1137 |
sheet.write(0, 15, "Lowest Seller Rating", heading_xf)
|
|
|
1138 |
sheet.write(0, 16, "Lowest Seller Shipping Time", heading_xf)
|
|
|
1139 |
sheet.write(0, 17, "Lowest Seller SP", heading_xf)
|
|
|
1140 |
sheet.write(0, 18, "Lowest Seller TP", heading_xf)
|
|
|
1141 |
sheet.write(0, 19, "Second Lowest Seller", heading_xf)
|
|
|
1142 |
sheet.write(0, 20, "Second Lowest Seller Rating", heading_xf)
|
|
|
1143 |
sheet.write(0, 21, "Second Lowest Seller Shipping Time", heading_xf)
|
|
|
1144 |
sheet.write(0, 22, "Second Lowest Seller SP", heading_xf)
|
|
|
1145 |
sheet.write(0, 23, "Second Lowest Seller TP", heading_xf)
|
|
|
1146 |
sheet.write(0, 24, "Our Flipkart Inventory", heading_xf)
|
|
|
1147 |
sheet.write(0, 25, "Our Net Availability",heading_xf)
|
|
|
1148 |
sheet.write(0, 26, "Last Five Day Sale", heading_xf)
|
|
|
1149 |
sheet.write(0, 27, "Average Sale", heading_xf)
|
|
|
1150 |
sheet.write(0, 28, "Our NLC", heading_xf)
|
|
|
1151 |
sheet.write(0, 29, "Lowest Possible SP", heading_xf)
|
|
|
1152 |
sheet.write(0, 30, "Lowest Possible TP", heading_xf)
|
|
|
1153 |
sheet.write(0, 31, "Target SP", heading_xf)
|
|
|
1154 |
sheet.write(0, 32, "Target TP", heading_xf)
|
|
|
1155 |
sheet.write(0, 33, "Margin Increased Potential", heading_xf)
|
|
|
1156 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
|
|
1157 |
sheet_iterator = 1
|
| 11615 |
kshitij.so |
1158 |
|
| 11622 |
kshitij.so |
1159 |
buyBoxItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
|
|
|
1160 |
.join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
|
|
1161 |
.join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
|
|
1162 |
.join((Item,MarketPlaceHistory.item_id==Item.id))\
|
| 11615 |
kshitij.so |
1163 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
|
|
1164 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
|
|
|
1165 |
|
|
|
1166 |
|
| 11193 |
kshitij.so |
1167 |
for item in buyBoxItems:
|
| 11615 |
kshitij.so |
1168 |
mpHistory = item[0]
|
|
|
1169 |
flipkartItem = item[1]
|
|
|
1170 |
mpItem = item[2]
|
|
|
1171 |
catItem = item[3]
|
|
|
1172 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
|
|
1173 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
|
|
1174 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
|
|
1175 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
|
|
1176 |
sheet.write(sheet_iterator,4,catItem.brand)
|
|
|
1177 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
|
|
1178 |
sheet.write(sheet_iterator,6,catItem.weight)
|
|
|
1179 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
|
|
1180 |
sheet.write(sheet_iterator,8,catItem.risky)
|
|
|
1181 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
|
|
1182 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
1183 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
1184 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
|
|
1185 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
|
|
1186 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
|
|
1187 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
|
|
1188 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
|
|
1189 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
|
|
1190 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
1191 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
1192 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
|
|
1193 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
|
|
1194 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
|
|
1195 |
sheet.write(sheet_iterator,19,mpHistory.secondLowestSellerName)
|
|
|
1196 |
sheet.write(sheet_iterator,20,mpHistory.secondLowestSellerRating)
|
|
|
1197 |
# secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
|
|
|
1198 |
# else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
|
|
|
1199 |
sheet.write(sheet_iterator,21,mpHistory.secondLowestSellerShippingTime)
|
|
|
1200 |
sheet.write(sheet_iterator,22,mpHistory.secondLowestSellingPrice)
|
|
|
1201 |
sheet.write(sheet_iterator,23,mpHistory.secondLowestTp)
|
|
|
1202 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
|
|
1203 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 11193 |
kshitij.so |
1204 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
|
|
1205 |
else:
|
| 11615 |
kshitij.so |
1206 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
|
|
1207 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
|
|
1208 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
|
|
1209 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
|
|
1210 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
|
|
1211 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
|
|
1212 |
proposed_sp = max(mpHistory.secondLowestSellingPrice - max((20, mpHistory.secondLowestSellingPrice*0.002)), mpHistory.lowestPossibleSp)
|
| 11193 |
kshitij.so |
1213 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 11615 |
kshitij.so |
1214 |
target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
|
| 11193 |
kshitij.so |
1215 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
|
|
1216 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
| 11615 |
kshitij.so |
1217 |
sheet.write(sheet_iterator, 33, proposed_tp -mpHistory.ourTp )
|
|
|
1218 |
sheet.write(sheet_iterator, 34, mpHistory.totalSeller)
|
| 11193 |
kshitij.so |
1219 |
sheet_iterator+=1
|
|
|
1220 |
|
| 11615 |
kshitij.so |
1221 |
buyBoxItems[:] = []
|
| 11193 |
kshitij.so |
1222 |
|
| 11615 |
kshitij.so |
1223 |
sheet = wbk.add_sheet('Pref Not Cheap')
|
| 11193 |
kshitij.so |
1224 |
|
|
|
1225 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1226 |
|
|
|
1227 |
excel_integer_format = '0'
|
|
|
1228 |
integer_style = xlwt.XFStyle()
|
|
|
1229 |
integer_style.num_format_str = excel_integer_format
|
|
|
1230 |
xstr = lambda s: s or ""
|
|
|
1231 |
|
|
|
1232 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1233 |
|
|
|
1234 |
excel_integer_format = '0'
|
|
|
1235 |
integer_style = xlwt.XFStyle()
|
|
|
1236 |
integer_style.num_format_str = excel_integer_format
|
|
|
1237 |
|
|
|
1238 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
1239 |
sheet.write(0, 1, "Category", heading_xf)
|
|
|
1240 |
sheet.write(0, 2, "Product Group.", heading_xf)
|
|
|
1241 |
sheet.write(0, 3, "FK Serial Number", heading_xf)
|
|
|
1242 |
sheet.write(0, 4, "Brand", heading_xf)
|
|
|
1243 |
sheet.write(0, 5, "Product Name", heading_xf)
|
|
|
1244 |
sheet.write(0, 6, "Weight", heading_xf)
|
|
|
1245 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
|
|
1246 |
sheet.write(0, 8, "Risky", heading_xf)
|
| 11615 |
kshitij.so |
1247 |
sheet.write(0, 9, "Our Rating", heading_xf)
|
|
|
1248 |
sheet.write(0, 10, "Our Shipping Time", heading_xf)
|
|
|
1249 |
sheet.write(0, 11, "Our Rank", heading_xf)
|
|
|
1250 |
sheet.write(0, 12, "Our SP", heading_xf)
|
|
|
1251 |
sheet.write(0, 13, "Our TP", heading_xf)
|
|
|
1252 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
|
|
1253 |
sheet.write(0, 15, "Lowest Seller Rating", heading_xf)
|
|
|
1254 |
sheet.write(0, 16, "Lowest Seller Shipping Time", heading_xf)
|
|
|
1255 |
sheet.write(0, 17, "Lowest Seller SP", heading_xf)
|
|
|
1256 |
sheet.write(0, 18, "Lowest Seller TP", heading_xf)
|
|
|
1257 |
sheet.write(0, 19, "Preffered Seller", heading_xf)
|
|
|
1258 |
sheet.write(0, 20, "Preffered Seller Rating", heading_xf)
|
|
|
1259 |
sheet.write(0, 21, "Preffered Seller Shipping Time", heading_xf)
|
|
|
1260 |
sheet.write(0, 22, "Preffered Seller SP", heading_xf)
|
|
|
1261 |
sheet.write(0, 23, "Preffered Seller TP", heading_xf)
|
|
|
1262 |
sheet.write(0, 24, "Our Flipkart Inventory", heading_xf)
|
|
|
1263 |
sheet.write(0, 25, "Our Net Availability",heading_xf)
|
|
|
1264 |
sheet.write(0, 26, "Last Five Day Sale", heading_xf)
|
|
|
1265 |
sheet.write(0, 27, "Average Sale", heading_xf)
|
|
|
1266 |
sheet.write(0, 28, "Our NLC", heading_xf)
|
|
|
1267 |
sheet.write(0, 29, "Lowest Possible SP", heading_xf)
|
|
|
1268 |
sheet.write(0, 30, "Lowest Possible TP", heading_xf)
|
|
|
1269 |
sheet.write(0, 31, "Target SP", heading_xf)
|
|
|
1270 |
sheet.write(0, 32, "Target TP", heading_xf)
|
|
|
1271 |
sheet.write(0, 33, "Total Seller", heading_xf)
|
| 11193 |
kshitij.so |
1272 |
sheet_iterator = 1
|
| 11615 |
kshitij.so |
1273 |
|
| 11622 |
kshitij.so |
1274 |
prefNotCheapItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
|
|
|
1275 |
.join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
|
|
1276 |
.join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
|
|
1277 |
.join((Item,MarketPlaceHistory.item_id==Item.id))\
|
| 11615 |
kshitij.so |
1278 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
|
|
1279 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP).all()
|
|
|
1280 |
|
|
|
1281 |
|
|
|
1282 |
for item in prefNotCheapItems:
|
|
|
1283 |
mpHistory = item[0]
|
|
|
1284 |
flipkartItem = item[1]
|
|
|
1285 |
mpItem = item[2]
|
|
|
1286 |
catItem = item[3]
|
|
|
1287 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
|
|
1288 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
|
|
1289 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
|
|
1290 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
|
|
1291 |
sheet.write(sheet_iterator,4,catItem.brand)
|
|
|
1292 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
|
|
1293 |
sheet.write(sheet_iterator,6,catItem.weight)
|
|
|
1294 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
|
|
1295 |
sheet.write(sheet_iterator,8,catItem.risky)
|
|
|
1296 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
|
|
1297 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
1298 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
1299 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
|
|
1300 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
|
|
1301 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
|
|
1302 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
|
|
1303 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
|
|
1304 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
|
|
1305 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
1306 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
1307 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
|
|
1308 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
|
|
1309 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
|
|
1310 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerName)
|
|
|
1311 |
sheet.write(sheet_iterator,20,mpHistory.prefferedSellerRating)
|
|
|
1312 |
# secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
|
|
|
1313 |
# else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
|
|
|
1314 |
sheet.write(sheet_iterator,21,mpHistory.prefferedSellerShippingTime)
|
|
|
1315 |
sheet.write(sheet_iterator,22,mpHistory.prefferedSellerSellingPrice)
|
|
|
1316 |
sheet.write(sheet_iterator,23,mpHistory.prefferedSellerTp)
|
|
|
1317 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
|
|
1318 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
|
|
1319 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
| 11193 |
kshitij.so |
1320 |
else:
|
| 11615 |
kshitij.so |
1321 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
|
|
1322 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
|
|
1323 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
|
|
1324 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
|
|
1325 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
|
|
1326 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
|
|
1327 |
proposed_sp = max(mpHistory.lowestSellingPrice - max((20, mpHistory.lowestSellingPrice*0.002)), mpHistory.lowestPossibleSp)
|
|
|
1328 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
|
|
1329 |
target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
|
|
|
1330 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
|
|
1331 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
|
|
1332 |
sheet.write(sheet_iterator, 33, mpHistory.totalSeller)
|
| 11193 |
kshitij.so |
1333 |
sheet_iterator+=1
|
| 11615 |
kshitij.so |
1334 |
|
|
|
1335 |
prefNotCheapItems[:] = []
|
| 11193 |
kshitij.so |
1336 |
|
|
|
1337 |
sheet = wbk.add_sheet('Cheap But Not Pref')
|
|
|
1338 |
|
|
|
1339 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1340 |
|
|
|
1341 |
excel_integer_format = '0'
|
|
|
1342 |
integer_style = xlwt.XFStyle()
|
|
|
1343 |
integer_style.num_format_str = excel_integer_format
|
|
|
1344 |
xstr = lambda s: s or ""
|
|
|
1345 |
|
|
|
1346 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1347 |
|
|
|
1348 |
excel_integer_format = '0'
|
|
|
1349 |
integer_style = xlwt.XFStyle()
|
|
|
1350 |
integer_style.num_format_str = excel_integer_format
|
|
|
1351 |
|
|
|
1352 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
1353 |
sheet.write(0, 1, "Category", heading_xf)
|
|
|
1354 |
sheet.write(0, 2, "Product Group.", heading_xf)
|
|
|
1355 |
sheet.write(0, 3, "FK Serial Number", heading_xf)
|
|
|
1356 |
sheet.write(0, 4, "Brand", heading_xf)
|
|
|
1357 |
sheet.write(0, 5, "Product Name", heading_xf)
|
|
|
1358 |
sheet.write(0, 6, "Weight", heading_xf)
|
|
|
1359 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
|
|
1360 |
sheet.write(0, 8, "Risky", heading_xf)
|
|
|
1361 |
sheet.write(0, 9, "Our Rank", heading_xf)
|
|
|
1362 |
sheet.write(0, 10, "Lowest Seller", heading_xf)
|
|
|
1363 |
sheet.write(0, 11, "Our Rating", heading_xf)
|
|
|
1364 |
sheet.write(0, 12, "Our Shipping Time", heading_xf)
|
|
|
1365 |
sheet.write(0, 13, "Our SP", heading_xf)
|
|
|
1366 |
sheet.write(0, 14, "Our TP", heading_xf)
|
|
|
1367 |
sheet.write(0, 15, "Preffered Seller", heading_xf)
|
|
|
1368 |
sheet.write(0, 16, "Preffered Seller Rating", heading_xf)
|
|
|
1369 |
sheet.write(0, 17, "Preffered Seller Shipping Time", heading_xf)
|
|
|
1370 |
sheet.write(0, 18, "Preffered Seller SP", heading_xf)
|
|
|
1371 |
sheet.write(0, 19, "Preffered Seller TP", heading_xf)
|
|
|
1372 |
sheet.write(0, 20, "Our Flipkart Inventory", heading_xf)
|
|
|
1373 |
sheet.write(0, 21, "Our Net Availability",heading_xf)
|
|
|
1374 |
sheet.write(0, 22, "Last Five Day Sale", heading_xf)
|
|
|
1375 |
sheet.write(0, 23, "Average Sale", heading_xf)
|
|
|
1376 |
sheet.write(0, 24, "Our NLC", heading_xf)
|
|
|
1377 |
sheet.write(0, 25, "Lowest Possible SP", heading_xf)
|
|
|
1378 |
sheet.write(0, 26, "Lowest Possible TP", heading_xf)
|
|
|
1379 |
sheet.write(0, 27, "Total Seller", heading_xf)
|
|
|
1380 |
sheet_iterator = 1
|
| 11615 |
kshitij.so |
1381 |
|
| 11622 |
kshitij.so |
1382 |
cheapNotPrefferedItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
|
|
|
1383 |
.join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
|
|
1384 |
.join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
|
|
1385 |
.join((Item,MarketPlaceHistory.item_id==Item.id))\
|
| 11615 |
kshitij.so |
1386 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
|
|
1387 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CHEAP_BUT_NOT_PREF).all()
|
|
|
1388 |
|
|
|
1389 |
for item in cheapNotPrefferedItems:
|
|
|
1390 |
mpHistory = item[0]
|
|
|
1391 |
flipkartItem = item[1]
|
|
|
1392 |
mpItem = item[2]
|
|
|
1393 |
catItem = item[3]
|
|
|
1394 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
|
|
1395 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
|
|
1396 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
|
|
1397 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
|
|
1398 |
sheet.write(sheet_iterator,4,catItem.brand)
|
|
|
1399 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
|
|
1400 |
sheet.write(sheet_iterator,6,catItem.weight)
|
|
|
1401 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
|
|
1402 |
sheet.write(sheet_iterator,8,catItem.risky)
|
| 11623 |
kshitij.so |
1403 |
sheet.write(sheet_iterator,9,mpHistory.ourRank)
|
| 11615 |
kshitij.so |
1404 |
sheet.write(sheet_iterator,10,mpHistory.lowestSellerName)
|
|
|
1405 |
sheet.write(sheet_iterator,11,mpHistory.ourRating)
|
|
|
1406 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
1407 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
1408 |
sheet.write(sheet_iterator,12,mpHistory.lowestSellerShippingTime)
|
|
|
1409 |
sheet.write(sheet_iterator,13,mpHistory.lowestSellingPrice)
|
|
|
1410 |
sheet.write(sheet_iterator,14,mpHistory.lowestTp)
|
|
|
1411 |
sheet.write(sheet_iterator,15,mpHistory.prefferedSellerName)
|
|
|
1412 |
sheet.write(sheet_iterator,16,mpHistory.prefferedSellerRating)
|
|
|
1413 |
# prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
|
|
1414 |
# else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
|
|
1415 |
sheet.write(sheet_iterator,17,mpHistory.prefferedSellerShippingTime)
|
|
|
1416 |
sheet.write(sheet_iterator,18,mpHistory.prefferedSellerSellingPrice)
|
|
|
1417 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerTp)
|
|
|
1418 |
sheet.write(sheet_iterator,20,mpHistory.ourInventory)
|
|
|
1419 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 11193 |
kshitij.so |
1420 |
sheet.write(sheet_iterator, 21, 'Info not available')
|
|
|
1421 |
else:
|
| 11615 |
kshitij.so |
1422 |
sheet.write(sheet_iterator, 21, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
|
|
1423 |
sheet.write(sheet_iterator, 22, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
|
|
1424 |
sheet.write(sheet_iterator, 23, (itemSaleMap.get(mpHistory.item_id))[3])
|
|
|
1425 |
sheet.write(sheet_iterator, 24, mpHistory.ourNlc)
|
|
|
1426 |
sheet.write(sheet_iterator, 25, mpHistory.lowestPossibleSp)
|
|
|
1427 |
sheet.write(sheet_iterator, 26, mpHistory.lowestPossibleTp)
|
| 11193 |
kshitij.so |
1428 |
#proposed_sp = max(flipkartDetails.secondLowestSellerSp - max((20, flipkartDetails.secondLowestSellerSp*0.002)), flipkartPricing.lowestPossibleSp)
|
|
|
1429 |
#proposed_tp = getTargetTp(proposed_sp,mpItem)
|
|
|
1430 |
#target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
|
| 11615 |
kshitij.so |
1431 |
sheet.write(sheet_iterator, 27, mpHistory.totalSeller)
|
| 11193 |
kshitij.so |
1432 |
sheet_iterator+=1
|
| 11615 |
kshitij.so |
1433 |
|
|
|
1434 |
cheapNotPrefferedItems[:]=[]
|
| 11193 |
kshitij.so |
1435 |
|
|
|
1436 |
sheet = wbk.add_sheet('Can Compete-With Inventory')
|
|
|
1437 |
xstr = lambda s: s or ""
|
|
|
1438 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1439 |
|
|
|
1440 |
excel_integer_format = '0'
|
|
|
1441 |
integer_style = xlwt.XFStyle()
|
|
|
1442 |
integer_style.num_format_str = excel_integer_format
|
|
|
1443 |
|
|
|
1444 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
1445 |
sheet.write(0, 1, "Category", heading_xf)
|
|
|
1446 |
sheet.write(0, 2, "Product Group.", heading_xf)
|
|
|
1447 |
sheet.write(0, 3, "FK Serial Number", heading_xf)
|
|
|
1448 |
sheet.write(0, 4, "Brand", heading_xf)
|
|
|
1449 |
sheet.write(0, 5, "Product Name", heading_xf)
|
|
|
1450 |
sheet.write(0, 6, "Weight", heading_xf)
|
|
|
1451 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
|
|
1452 |
sheet.write(0, 8, "Risky", heading_xf)
|
|
|
1453 |
sheet.write(0, 9, "Our Rating", heading_xf)
|
|
|
1454 |
sheet.write(0, 10, "Our Shipping Time", heading_xf)
|
|
|
1455 |
sheet.write(0, 11, "Our Rank", heading_xf)
|
|
|
1456 |
sheet.write(0, 12, "Our SP", heading_xf)
|
|
|
1457 |
sheet.write(0, 13, "Our TP", heading_xf)
|
|
|
1458 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
|
|
1459 |
sheet.write(0, 15, "Lowest Seller Rating", heading_xf)
|
|
|
1460 |
sheet.write(0, 16, "Lowest Seller Shipping Time", heading_xf)
|
|
|
1461 |
sheet.write(0, 17, "Lowest Seller SP", heading_xf)
|
|
|
1462 |
sheet.write(0, 18, "Lowest Seller TP", heading_xf)
|
|
|
1463 |
sheet.write(0, 19, "Preffered Seller", heading_xf)
|
|
|
1464 |
sheet.write(0, 20, "Preffered Seller Rating", heading_xf)
|
|
|
1465 |
sheet.write(0, 21, "Preffered Seller Shipping Time", heading_xf)
|
|
|
1466 |
sheet.write(0, 22, "Preffer Seller SP", heading_xf)
|
|
|
1467 |
sheet.write(0, 23, "Preffered Seller TP", heading_xf)
|
|
|
1468 |
sheet.write(0, 24, "Our Flipkart Inventory", heading_xf)
|
|
|
1469 |
sheet.write(0, 25, "Our Net Availability",heading_xf)
|
|
|
1470 |
sheet.write(0, 26, "Last Five Day Sale", heading_xf)
|
|
|
1471 |
sheet.write(0, 27, "Average Sale", heading_xf)
|
|
|
1472 |
sheet.write(0, 28, "Our NLC", heading_xf)
|
|
|
1473 |
sheet.write(0, 29, "Lowest Possible SP", heading_xf)
|
|
|
1474 |
sheet.write(0, 30, "Lowest Possible TP", heading_xf)
|
|
|
1475 |
sheet.write(0, 31, "Target SP", heading_xf)
|
|
|
1476 |
sheet.write(0, 32, "Target TP", heading_xf)
|
|
|
1477 |
sheet.write(0, 33, "Target NLC", heading_xf)
|
|
|
1478 |
sheet.write(0, 34, "Sales Potential", heading_xf)
|
|
|
1479 |
sheet.write(0, 35, "Total Seller", heading_xf)
|
|
|
1480 |
sheet_iterator = 1
|
| 11615 |
kshitij.so |
1481 |
|
| 11622 |
kshitij.so |
1482 |
competitiveItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
|
|
|
1483 |
.join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
|
|
1484 |
.join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
|
|
1485 |
.join((Item,MarketPlaceHistory.item_id==Item.id))\
|
| 11615 |
kshitij.so |
1486 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
|
|
1487 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).all()
|
|
|
1488 |
|
|
|
1489 |
for item in competitiveItems:
|
|
|
1490 |
mpHistory = item[0]
|
|
|
1491 |
flipkartItem = item[1]
|
|
|
1492 |
mpItem = item[2]
|
|
|
1493 |
catItem = item[3]
|
|
|
1494 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
|
|
1495 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
|
|
1496 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
|
|
1497 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
|
|
1498 |
sheet.write(sheet_iterator,4,catItem.brand)
|
|
|
1499 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
|
|
1500 |
sheet.write(sheet_iterator,6,catItem.weight)
|
|
|
1501 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
|
|
1502 |
sheet.write(sheet_iterator,8,catItem.risky)
|
|
|
1503 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
|
|
1504 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
1505 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
1506 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
|
|
1507 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
|
|
1508 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
|
|
1509 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
|
|
1510 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
|
|
1511 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
|
|
1512 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
1513 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
1514 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
|
|
1515 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
|
|
1516 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
|
|
1517 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerName)
|
|
|
1518 |
sheet.write(sheet_iterator,20,mpHistory.prefferedSellerRating)
|
|
|
1519 |
# prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
|
|
1520 |
# else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
|
|
1521 |
sheet.write(sheet_iterator,21,mpHistory.prefferedSellerShippingTime)
|
|
|
1522 |
sheet.write(sheet_iterator,22,mpHistory.prefferedSellerSellingPrice)
|
|
|
1523 |
sheet.write(sheet_iterator,23,mpHistory.prefferedSellerTp)
|
|
|
1524 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
|
|
1525 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 11193 |
kshitij.so |
1526 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
|
|
1527 |
else:
|
| 11615 |
kshitij.so |
1528 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
|
|
1529 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
|
|
1530 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
|
|
1531 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
|
|
1532 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
|
|
1533 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
|
|
1534 |
proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestSellingPrice)
|
| 11193 |
kshitij.so |
1535 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 11615 |
kshitij.so |
1536 |
target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
|
| 11193 |
kshitij.so |
1537 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
|
|
1538 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
|
|
1539 |
sheet.write(sheet_iterator, 33, target_nlc)
|
| 11615 |
kshitij.so |
1540 |
sheet.write(sheet_iterator, 34, getSalesPotential(mpHistory.lowestSellingPrice,mpHistory.ourNlc))
|
|
|
1541 |
sheet.write(sheet_iterator, 35, mpHistory.totalSeller)
|
| 11193 |
kshitij.so |
1542 |
sheet_iterator+=1
|
| 11615 |
kshitij.so |
1543 |
|
|
|
1544 |
competitiveItems[:]=[]
|
|
|
1545 |
|
| 11193 |
kshitij.so |
1546 |
sheet = wbk.add_sheet('Negative Margin')
|
|
|
1547 |
xstr = lambda s: s or ""
|
|
|
1548 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1549 |
|
|
|
1550 |
excel_integer_format = '0'
|
|
|
1551 |
integer_style = xlwt.XFStyle()
|
|
|
1552 |
integer_style.num_format_str = excel_integer_format
|
|
|
1553 |
|
|
|
1554 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
1555 |
sheet.write(0, 1, "Category", heading_xf)
|
|
|
1556 |
sheet.write(0, 2, "Product Group.", heading_xf)
|
|
|
1557 |
sheet.write(0, 3, "FK Serial Number", heading_xf)
|
|
|
1558 |
sheet.write(0, 4, "Brand", heading_xf)
|
|
|
1559 |
sheet.write(0, 5, "Product Name", heading_xf)
|
|
|
1560 |
sheet.write(0, 6, "Weight", heading_xf)
|
|
|
1561 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
|
|
1562 |
sheet.write(0, 8, "Risky", heading_xf)
|
|
|
1563 |
sheet.write(0, 9, "Our Rating", heading_xf)
|
|
|
1564 |
sheet.write(0, 10, "Our Shipping Time", heading_xf)
|
|
|
1565 |
sheet.write(0, 11, "Our Rank", heading_xf)
|
|
|
1566 |
sheet.write(0, 12, "Our SP", heading_xf)
|
|
|
1567 |
sheet.write(0, 13, "Our TP", heading_xf)
|
|
|
1568 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
|
|
1569 |
sheet.write(0, 15, "Lowest Seller Rating", heading_xf)
|
|
|
1570 |
sheet.write(0, 16, "Lowest Seller Shipping Time", heading_xf)
|
|
|
1571 |
sheet.write(0, 17, "Lowest Seller SP", heading_xf)
|
|
|
1572 |
sheet.write(0, 18, "Lowest Seller TP", heading_xf)
|
|
|
1573 |
sheet.write(0, 19, "Preffered Seller", heading_xf)
|
|
|
1574 |
sheet.write(0, 20, "Preffered Seller Rating", heading_xf)
|
|
|
1575 |
sheet.write(0, 21, "Preffered Seller Shipping Time", heading_xf)
|
|
|
1576 |
sheet.write(0, 22, "Preffer Seller SP", heading_xf)
|
|
|
1577 |
sheet.write(0, 23, "Preffered Seller TP", heading_xf)
|
|
|
1578 |
sheet.write(0, 24, "Our Flipkart Inventory", heading_xf)
|
|
|
1579 |
sheet.write(0, 25, "Our Net Availability",heading_xf)
|
|
|
1580 |
sheet.write(0, 26, "Last Five Day Sale", heading_xf)
|
|
|
1581 |
sheet.write(0, 27, "Average Sale", heading_xf)
|
|
|
1582 |
sheet.write(0, 28, "Our NLC", heading_xf)
|
|
|
1583 |
sheet.write(0, 29, "Lowest Possible SP", heading_xf)
|
|
|
1584 |
sheet.write(0, 30, "Lowest Possible TP", heading_xf)
|
|
|
1585 |
sheet.write(0, 31, "Margin", heading_xf)
|
|
|
1586 |
sheet.write(0, 32, "Total Seller", heading_xf)
|
|
|
1587 |
sheet_iterator = 1
|
| 11615 |
kshitij.so |
1588 |
|
| 11622 |
kshitij.so |
1589 |
negativeMargin = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
|
|
|
1590 |
.join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
|
|
1591 |
.join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
|
|
1592 |
.join((Item,MarketPlaceHistory.item_id==Item.id))\
|
| 11615 |
kshitij.so |
1593 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
|
|
1594 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).all()
|
|
|
1595 |
|
| 11193 |
kshitij.so |
1596 |
for item in negativeMargin:
|
| 11615 |
kshitij.so |
1597 |
mpHistory = item[0]
|
|
|
1598 |
flipkartItem = item[1]
|
|
|
1599 |
mpItem = item[2]
|
|
|
1600 |
catItem = item[3]
|
|
|
1601 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
|
|
1602 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
|
|
1603 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
|
|
1604 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
|
|
1605 |
sheet.write(sheet_iterator,4,catItem.brand)
|
|
|
1606 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
|
|
1607 |
sheet.write(sheet_iterator,6,catItem.weight)
|
|
|
1608 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
|
|
1609 |
sheet.write(sheet_iterator,8,catItem.risky)
|
|
|
1610 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
|
|
1611 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
1612 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
1613 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
|
|
1614 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
|
|
1615 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
|
|
1616 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
|
|
1617 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
|
|
1618 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
|
|
1619 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
1620 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
1621 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
|
|
1622 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
|
|
1623 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
|
|
1624 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerName)
|
|
|
1625 |
sheet.write(sheet_iterator,20,mpHistory.prefferedSellerRating)
|
|
|
1626 |
# prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
|
|
1627 |
# else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
|
|
1628 |
sheet.write(sheet_iterator,21,mpHistory.prefferedSellerShippingTime)
|
|
|
1629 |
sheet.write(sheet_iterator,22,mpHistory.prefferedSellerSellingPrice)
|
|
|
1630 |
sheet.write(sheet_iterator,23,mpHistory.prefferedSellerTp)
|
|
|
1631 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
|
|
1632 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 11193 |
kshitij.so |
1633 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
|
|
1634 |
else:
|
| 11615 |
kshitij.so |
1635 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
|
|
1636 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
|
|
1637 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
|
|
1638 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
|
|
1639 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
|
|
1640 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
|
|
1641 |
sheet.write(sheet_iterator, 31, round((mpHistory.ourTp - mpHistory.lowestPossibleTp),2))
|
|
|
1642 |
sheet.write(sheet_iterator, 32, mpHistory.totalSeller)
|
| 11193 |
kshitij.so |
1643 |
sheet_iterator+=1
|
| 11615 |
kshitij.so |
1644 |
|
|
|
1645 |
negativeMargin[:]=[]
|
| 11193 |
kshitij.so |
1646 |
|
|
|
1647 |
if (runType=='FULL'):
|
|
|
1648 |
sheet = wbk.add_sheet('Auto Favorites')
|
|
|
1649 |
|
|
|
1650 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1651 |
|
|
|
1652 |
excel_integer_format = '0'
|
|
|
1653 |
integer_style = xlwt.XFStyle()
|
|
|
1654 |
integer_style.num_format_str = excel_integer_format
|
|
|
1655 |
xstr = lambda s: s or ""
|
|
|
1656 |
|
|
|
1657 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
1658 |
sheet.write(0, 1, "Brand", heading_xf)
|
|
|
1659 |
sheet.write(0, 2, "Product Name", heading_xf)
|
|
|
1660 |
sheet.write(0, 3, "Auto Favourite", heading_xf)
|
|
|
1661 |
sheet.write(0, 4, "Reason", heading_xf)
|
|
|
1662 |
|
|
|
1663 |
sheet_iterator=1
|
|
|
1664 |
for autoFav in nowAutoFav:
|
|
|
1665 |
itemId = autoFav[0]
|
|
|
1666 |
reason = autoFav[1]
|
|
|
1667 |
it = Item.query.filter_by(id=itemId).one()
|
|
|
1668 |
sheet.write(sheet_iterator, 0, itemId)
|
|
|
1669 |
sheet.write(sheet_iterator, 1, it.brand)
|
|
|
1670 |
sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
|
|
|
1671 |
sheet.write(sheet_iterator, 3, "True")
|
|
|
1672 |
sheet.write(sheet_iterator, 4, reason)
|
|
|
1673 |
sheet_iterator+=1
|
|
|
1674 |
for prevFav in previousAutoFav:
|
|
|
1675 |
it = Item.query.filter_by(id=prevFav).one()
|
|
|
1676 |
sheet.write(sheet_iterator, 0, prevFav)
|
|
|
1677 |
sheet.write(sheet_iterator, 1, it.brand)
|
|
|
1678 |
sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
|
|
|
1679 |
sheet.write(sheet_iterator, 3, "False")
|
|
|
1680 |
sheet_iterator+=1
|
|
|
1681 |
|
|
|
1682 |
sheet = wbk.add_sheet('Exception Item List')
|
|
|
1683 |
|
|
|
1684 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1685 |
|
|
|
1686 |
excel_integer_format = '0'
|
|
|
1687 |
integer_style = xlwt.XFStyle()
|
|
|
1688 |
integer_style.num_format_str = excel_integer_format
|
|
|
1689 |
xstr = lambda s: s or ""
|
|
|
1690 |
|
|
|
1691 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
1692 |
sheet.write(0, 1, "FK Serial number", heading_xf)
|
|
|
1693 |
sheet.write(0, 2, "Brand", heading_xf)
|
|
|
1694 |
sheet.write(0, 3, "Product Name", heading_xf)
|
|
|
1695 |
sheet.write(0, 4, "Reason", heading_xf)
|
|
|
1696 |
sheet_iterator=1
|
| 11615 |
kshitij.so |
1697 |
|
| 11622 |
kshitij.so |
1698 |
exeptionItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
|
|
|
1699 |
.join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
|
|
1700 |
.join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
|
|
1701 |
.join((Item,MarketPlaceHistory.item_id==Item.id))\
|
| 11615 |
kshitij.so |
1702 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
|
|
1703 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.EXCEPTION).all()
|
|
|
1704 |
|
|
|
1705 |
for item in exeptionItems:
|
|
|
1706 |
mpHistory = item[0]
|
|
|
1707 |
flipkartItem = item[1]
|
|
|
1708 |
mpItem = item[2]
|
|
|
1709 |
catItem = item[3]
|
|
|
1710 |
sheet.write(sheet_iterator, 0, mpHistory.item_id)
|
|
|
1711 |
sheet.write(sheet_iterator, 1, flipkartItem.flipkartSerialNumber)
|
|
|
1712 |
sheet.write(sheet_iterator, 2, catItem.brand)
|
|
|
1713 |
sheet.write(sheet_iterator, 3, xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
| 11193 |
kshitij.so |
1714 |
try:
|
| 11615 |
kshitij.so |
1715 |
if mpHistory.totalSeller is None:
|
| 11193 |
kshitij.so |
1716 |
pass
|
|
|
1717 |
except:
|
|
|
1718 |
sheet.write(sheet_iterator, 4, "Unable to fetch info from Flipkart")
|
|
|
1719 |
sheet_iterator+=1
|
|
|
1720 |
continue
|
|
|
1721 |
sheet.write(sheet_iterator, 4, "No Seller Available")
|
|
|
1722 |
sheet_iterator+=1
|
| 11615 |
kshitij.so |
1723 |
|
|
|
1724 |
exeptionItems[:]=[]
|
| 11193 |
kshitij.so |
1725 |
|
|
|
1726 |
sheet = wbk.add_sheet('Can Compete-No Inv')
|
|
|
1727 |
xstr = lambda s: s or ""
|
|
|
1728 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1729 |
|
|
|
1730 |
excel_integer_format = '0'
|
|
|
1731 |
integer_style = xlwt.XFStyle()
|
|
|
1732 |
integer_style.num_format_str = excel_integer_format
|
|
|
1733 |
|
|
|
1734 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
1735 |
sheet.write(0, 1, "Category", heading_xf)
|
|
|
1736 |
sheet.write(0, 2, "Product Group.", heading_xf)
|
|
|
1737 |
sheet.write(0, 3, "FK Serial Number", heading_xf)
|
|
|
1738 |
sheet.write(0, 4, "Brand", heading_xf)
|
|
|
1739 |
sheet.write(0, 5, "Product Name", heading_xf)
|
|
|
1740 |
sheet.write(0, 6, "Weight", heading_xf)
|
|
|
1741 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
|
|
1742 |
sheet.write(0, 8, "Risky", heading_xf)
|
|
|
1743 |
sheet.write(0, 9, "Our Rating", heading_xf)
|
|
|
1744 |
sheet.write(0, 10, "Our Shipping Time", heading_xf)
|
|
|
1745 |
sheet.write(0, 11, "Our Rank", heading_xf)
|
|
|
1746 |
sheet.write(0, 12, "Our SP", heading_xf)
|
|
|
1747 |
sheet.write(0, 13, "Our TP", heading_xf)
|
|
|
1748 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
|
|
1749 |
sheet.write(0, 15, "Lowest Seller Rating", heading_xf)
|
|
|
1750 |
sheet.write(0, 16, "Lowest Seller Shipping Time", heading_xf)
|
|
|
1751 |
sheet.write(0, 17, "Lowest Seller SP", heading_xf)
|
|
|
1752 |
sheet.write(0, 18, "Lowest Seller TP", heading_xf)
|
|
|
1753 |
sheet.write(0, 19, "Preffered Seller", heading_xf)
|
|
|
1754 |
sheet.write(0, 20, "Preffered Seller Rating", heading_xf)
|
|
|
1755 |
sheet.write(0, 21, "Preffered Seller Shipping Time", heading_xf)
|
|
|
1756 |
sheet.write(0, 22, "Preffer Seller SP", heading_xf)
|
|
|
1757 |
sheet.write(0, 23, "Preffered Seller TP", heading_xf)
|
|
|
1758 |
sheet.write(0, 24, "Our Flipkart Inventory", heading_xf)
|
|
|
1759 |
sheet.write(0, 25, "Our Net Availability",heading_xf)
|
|
|
1760 |
sheet.write(0, 26, "Last Five Day Sale", heading_xf)
|
|
|
1761 |
sheet.write(0, 27, "Average Sale", heading_xf)
|
|
|
1762 |
sheet.write(0, 28, "Our NLC", heading_xf)
|
|
|
1763 |
sheet.write(0, 29, "Lowest Possible SP", heading_xf)
|
|
|
1764 |
sheet.write(0, 30, "Lowest Possible TP", heading_xf)
|
|
|
1765 |
sheet.write(0, 31, "Target SP", heading_xf)
|
|
|
1766 |
sheet.write(0, 32, "Target TP", heading_xf)
|
|
|
1767 |
sheet.write(0, 33, "Sales Potential", heading_xf)
|
|
|
1768 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
|
|
1769 |
sheet_iterator = 1
|
| 11615 |
kshitij.so |
1770 |
|
| 11622 |
kshitij.so |
1771 |
competitiveNoInventory = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
|
|
|
1772 |
.join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
|
|
1773 |
.join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
|
|
1774 |
.join((Item,MarketPlaceHistory.item_id==Item.id))\
|
| 11615 |
kshitij.so |
1775 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
|
|
1776 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE_NO_INVENTORY).all()
|
|
|
1777 |
|
| 11193 |
kshitij.so |
1778 |
for item in competitiveNoInventory:
|
| 11615 |
kshitij.so |
1779 |
mpHistory = item[0]
|
|
|
1780 |
flipkartItem = item[1]
|
|
|
1781 |
mpItem = item[2]
|
|
|
1782 |
catItem = item[3]
|
|
|
1783 |
if ((not inventoryMap.has_key(mpHistory.item_id)) or getNetAvailability(inventoryMap.get(mpHistory.item_id))<=0):
|
|
|
1784 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
|
|
1785 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
|
|
1786 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
|
|
1787 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
|
|
1788 |
sheet.write(sheet_iterator,4,catItem.brand)
|
|
|
1789 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
|
|
1790 |
sheet.write(sheet_iterator,6,catItem.weight)
|
|
|
1791 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
|
|
1792 |
sheet.write(sheet_iterator,8,catItem.risky)
|
|
|
1793 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
|
|
1794 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
1795 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
1796 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
|
|
1797 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
|
|
1798 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
|
|
1799 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
|
|
1800 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
|
|
1801 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
|
|
1802 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
1803 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
1804 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
|
|
1805 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
|
|
1806 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
|
|
1807 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerName)
|
|
|
1808 |
sheet.write(sheet_iterator,20,mpHistory.prefferedSellerRating)
|
|
|
1809 |
# prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
|
|
1810 |
# else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
|
|
1811 |
sheet.write(sheet_iterator,21,mpHistory.prefferedSellerShippingTime)
|
|
|
1812 |
sheet.write(sheet_iterator,22,mpHistory.prefferedSellerSellingPrice)
|
|
|
1813 |
sheet.write(sheet_iterator,23,mpHistory.prefferedSellerTp)
|
|
|
1814 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
|
|
1815 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 11193 |
kshitij.so |
1816 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
|
|
1817 |
else:
|
| 11615 |
kshitij.so |
1818 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
|
|
1819 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
|
|
1820 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
|
|
1821 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
|
|
1822 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
|
|
1823 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
|
|
1824 |
proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
|
| 11193 |
kshitij.so |
1825 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
|
|
1826 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
|
|
1827 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
| 11615 |
kshitij.so |
1828 |
sheet.write(sheet_iterator, 33, getSalesPotential(mpHistory.lowestPossibleSp,mpHistory.ourNlc))
|
|
|
1829 |
sheet.write(sheet_iterator, 34, mpHistory.totalSeller)
|
| 11193 |
kshitij.so |
1830 |
sheet_iterator+=1
|
|
|
1831 |
|
| 11615 |
kshitij.so |
1832 |
|
| 11193 |
kshitij.so |
1833 |
sheet = wbk.add_sheet('Can Compete-No Inv On FK')
|
|
|
1834 |
xstr = lambda s: s or ""
|
|
|
1835 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1836 |
|
|
|
1837 |
excel_integer_format = '0'
|
|
|
1838 |
integer_style = xlwt.XFStyle()
|
|
|
1839 |
integer_style.num_format_str = excel_integer_format
|
|
|
1840 |
|
|
|
1841 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
1842 |
sheet.write(0, 1, "Category", heading_xf)
|
|
|
1843 |
sheet.write(0, 2, "Product Group.", heading_xf)
|
|
|
1844 |
sheet.write(0, 3, "FK Serial Number", heading_xf)
|
|
|
1845 |
sheet.write(0, 4, "Brand", heading_xf)
|
|
|
1846 |
sheet.write(0, 5, "Product Name", heading_xf)
|
|
|
1847 |
sheet.write(0, 6, "Weight", heading_xf)
|
|
|
1848 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
|
|
1849 |
sheet.write(0, 8, "Risky", heading_xf)
|
|
|
1850 |
sheet.write(0, 9, "Our Rating", heading_xf)
|
|
|
1851 |
sheet.write(0, 10, "Our Shipping Time", heading_xf)
|
|
|
1852 |
sheet.write(0, 11, "Our Rank", heading_xf)
|
|
|
1853 |
sheet.write(0, 12, "Our SP", heading_xf)
|
|
|
1854 |
sheet.write(0, 13, "Our TP", heading_xf)
|
|
|
1855 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
|
|
1856 |
sheet.write(0, 15, "Lowest Seller Rating", heading_xf)
|
|
|
1857 |
sheet.write(0, 16, "Lowest Seller Shipping Time", heading_xf)
|
|
|
1858 |
sheet.write(0, 17, "Lowest Seller SP", heading_xf)
|
|
|
1859 |
sheet.write(0, 18, "Lowest Seller TP", heading_xf)
|
|
|
1860 |
sheet.write(0, 19, "Preffered Seller", heading_xf)
|
|
|
1861 |
sheet.write(0, 20, "Preffered Seller Rating", heading_xf)
|
|
|
1862 |
sheet.write(0, 21, "Preffered Seller Shipping Time", heading_xf)
|
|
|
1863 |
sheet.write(0, 22, "Preffer Seller SP", heading_xf)
|
|
|
1864 |
sheet.write(0, 23, "Preffered Seller TP", heading_xf)
|
|
|
1865 |
sheet.write(0, 24, "Our Flipkart Inventory", heading_xf)
|
|
|
1866 |
sheet.write(0, 25, "Our Net Availability",heading_xf)
|
|
|
1867 |
sheet.write(0, 26, "Last Five Day Sale", heading_xf)
|
|
|
1868 |
sheet.write(0, 27, "Average Sale", heading_xf)
|
|
|
1869 |
sheet.write(0, 28, "Our NLC", heading_xf)
|
|
|
1870 |
sheet.write(0, 29, "Lowest Possible SP", heading_xf)
|
|
|
1871 |
sheet.write(0, 30, "Lowest Possible TP", heading_xf)
|
|
|
1872 |
sheet.write(0, 31, "Target SP", heading_xf)
|
|
|
1873 |
sheet.write(0, 32, "Target TP", heading_xf)
|
|
|
1874 |
sheet.write(0, 33, "Sales Potential", heading_xf)
|
|
|
1875 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
|
|
1876 |
sheet_iterator = 1
|
|
|
1877 |
for item in competitiveNoInventory:
|
| 11615 |
kshitij.so |
1878 |
mpHistory = item[0]
|
|
|
1879 |
flipkartItem = item[1]
|
|
|
1880 |
mpItem = item[2]
|
|
|
1881 |
catItem = item[3]
|
|
|
1882 |
if (inventoryMap.has_key(mpHistory.item_id) and getNetAvailability(inventoryMap.get(mpHistory.item_id))>0):
|
|
|
1883 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
|
|
1884 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
|
|
1885 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
|
|
1886 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
|
|
1887 |
sheet.write(sheet_iterator,4,catItem.brand)
|
|
|
1888 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
|
|
1889 |
sheet.write(sheet_iterator,6,catItem.weight)
|
|
|
1890 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
|
|
1891 |
sheet.write(sheet_iterator,8,catItem.risky)
|
|
|
1892 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
|
|
1893 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
|
|
1894 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
|
|
1895 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
|
|
1896 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
|
|
1897 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
|
|
1898 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
|
|
1899 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
|
|
1900 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
|
|
1901 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
|
|
1902 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
|
|
1903 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
|
|
1904 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
|
|
1905 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
|
|
1906 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerName)
|
|
|
1907 |
sheet.write(sheet_iterator,20,mpHistory.prefferedSellerRating)
|
|
|
1908 |
# prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
|
|
1909 |
# else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
|
|
1910 |
sheet.write(sheet_iterator,21,mpHistory.prefferedSellerShippingTime)
|
|
|
1911 |
sheet.write(sheet_iterator,22,mpHistory.prefferedSellerSellingPrice)
|
|
|
1912 |
sheet.write(sheet_iterator,23,mpHistory.prefferedSellerTp)
|
|
|
1913 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
|
|
1914 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 11193 |
kshitij.so |
1915 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
|
|
1916 |
else:
|
| 11615 |
kshitij.so |
1917 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
|
|
1918 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
|
|
1919 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
|
|
1920 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
|
|
1921 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
|
|
1922 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
|
|
1923 |
proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
|
| 11193 |
kshitij.so |
1924 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
|
|
1925 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
|
|
1926 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
| 11615 |
kshitij.so |
1927 |
sheet.write(sheet_iterator, 33, getSalesPotential(mpHistory.lowestPossibleSp,mpHistory.ourNlc))
|
|
|
1928 |
sheet.write(sheet_iterator, 34, mpHistory.totalSeller)
|
| 11193 |
kshitij.so |
1929 |
sheet_iterator+=1
|
| 11615 |
kshitij.so |
1930 |
competitiveNoInventory[:]=[]
|
| 11193 |
kshitij.so |
1931 |
|
|
|
1932 |
autoPricingItems = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.decision.in_([1,2,3,4])).all()
|
|
|
1933 |
sheet = wbk.add_sheet('Auto Inc and Dec')
|
|
|
1934 |
|
|
|
1935 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
1936 |
|
|
|
1937 |
excel_integer_format = '0'
|
|
|
1938 |
integer_style = xlwt.XFStyle()
|
|
|
1939 |
integer_style.num_format_str = excel_integer_format
|
|
|
1940 |
xstr = lambda s: s or ""
|
|
|
1941 |
|
|
|
1942 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
1943 |
sheet.write(0, 1, "Brand", heading_xf)
|
|
|
1944 |
sheet.write(0, 2, "Product Name", heading_xf)
|
|
|
1945 |
sheet.write(0, 3, "Decision", heading_xf)
|
|
|
1946 |
sheet.write(0, 4, "Reason", heading_xf)
|
|
|
1947 |
sheet.write(0, 5, "Old Selling Price", heading_xf)
|
|
|
1948 |
sheet.write(0, 6, "Selling Price Updated",heading_xf)
|
|
|
1949 |
|
|
|
1950 |
sheet_iterator=1
|
|
|
1951 |
for autoPricingItem in autoPricingItems:
|
|
|
1952 |
mpHistory = autoPricingItem[0]
|
|
|
1953 |
item = autoPricingItem[1]
|
|
|
1954 |
it = Item.query.filter_by(id=item.id).one()
|
|
|
1955 |
sheet.write(sheet_iterator, 0, item.id)
|
|
|
1956 |
sheet.write(sheet_iterator, 1, it.brand)
|
|
|
1957 |
sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
|
|
|
1958 |
sheet.write(sheet_iterator, 3, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
|
|
|
1959 |
sheet.write(sheet_iterator, 4, mpHistory.reason)
|
|
|
1960 |
if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
|
|
|
1961 |
sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
|
|
|
1962 |
sheet.write(sheet_iterator, 6, math.ceil(mpHistory.proposedSellingPrice))
|
|
|
1963 |
if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
|
|
|
1964 |
sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
|
|
|
1965 |
sheet.write(sheet_iterator, 6, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
|
|
|
1966 |
sheet_iterator+=1
|
|
|
1967 |
|
|
|
1968 |
filename = "/tmp/flipkart-report-"+runType+" " + str(timestamp) + ".xls"
|
|
|
1969 |
wbk.save(filename)
|
|
|
1970 |
try:
|
| 11560 |
kshitij.so |
1971 |
EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Flipkart Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
|
|
|
1972 |
#EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["chandan.kumar@saholic.com","manoj.kumar@saholic.com","yukti.jain@saholic.com","ankush.dhingra@saholic.com","manoj.pal@saholic.com"], " Flipkart Scraping "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], ["rajneesh.arora@saholic.com","anikendra.das@saholic.com","vikram.raghav@saholic.com","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
|
| 11193 |
kshitij.so |
1973 |
except Exception as e:
|
|
|
1974 |
print e
|
|
|
1975 |
print "Unable to send report.Trying with local SMTP"
|
|
|
1976 |
smtpServer = smtplib.SMTP('localhost')
|
|
|
1977 |
smtpServer.set_debuglevel(1)
|
|
|
1978 |
sender = 'support@shop2020.in'
|
| 11560 |
kshitij.so |
1979 |
recipients = ["kshitij.sood@saholic.com"]
|
| 11193 |
kshitij.so |
1980 |
msg = MIMEMultipart()
|
| 11228 |
kshitij.so |
1981 |
msg['Subject'] = "Flipkart Scraping" + ' '+runType+' - ' + str(datetime.now())
|
| 11193 |
kshitij.so |
1982 |
msg['From'] = sender
|
| 11560 |
kshitij.so |
1983 |
#recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','vikram.raghav@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
|
| 11193 |
kshitij.so |
1984 |
msg['To'] = ",".join(recipients)
|
|
|
1985 |
fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
|
|
|
1986 |
fileMsg.set_payload(file(filename).read())
|
|
|
1987 |
email.encoders.encode_base64(fileMsg)
|
|
|
1988 |
fileMsg.add_header('Content-Disposition','attachment;filename=flipkart.xls')
|
|
|
1989 |
msg.attach(fileMsg)
|
|
|
1990 |
try:
|
|
|
1991 |
smtpServer.sendmail(sender, recipients, msg.as_string())
|
|
|
1992 |
print "Successfully sent email"
|
|
|
1993 |
except:
|
|
|
1994 |
print "Error: unable to send email."
|
| 11560 |
kshitij.so |
1995 |
|
| 11571 |
kshitij.so |
1996 |
def populateScrapingResults(val):
|
|
|
1997 |
try:
|
|
|
1998 |
flipkartDetails = fetchDetails(val.fkSerialNumber,scraper)
|
|
|
1999 |
val.flipkartDetails = flipkartDetails
|
|
|
2000 |
except Exception as e:
|
|
|
2001 |
print "Unable to fetch details of",val.fkSerialNumber
|
|
|
2002 |
print e
|
|
|
2003 |
val.flipkartDetails = None
|
|
|
2004 |
return
|
|
|
2005 |
|
|
|
2006 |
try:
|
|
|
2007 |
request_url = "https://api.flipkart.net/sellers/skus/%s/listings"%(str(val.skuAtFlipkart))
|
|
|
2008 |
r = requests.get(request_url, auth=('m2z93iskuj81qiid', '0c7ab6a5-98c0-4cdc-8be3-72c591e0add4'))
|
|
|
2009 |
print "Inventory info",r.json()
|
|
|
2010 |
stock_count = int((r.json()['attributeValues'])['stock_count'])
|
|
|
2011 |
except:
|
|
|
2012 |
stock_count = 0
|
|
|
2013 |
|
|
|
2014 |
val.ourFlipkartInventory = stock_count
|
| 11560 |
kshitij.so |
2015 |
|
|
|
2016 |
def threadsToSpawn(runType,itemInfo,scraper,itemPopulated):
|
|
|
2017 |
if runType == RunType.FAVOURITE:
|
| 11615 |
kshitij.so |
2018 |
count = 0
|
|
|
2019 |
pool = ThreadPool(3)
|
|
|
2020 |
startOffset = 0
|
| 11560 |
kshitij.so |
2021 |
endOffset = startOffset
|
| 11615 |
kshitij.so |
2022 |
while(count<3 and endOffset<len(itemInfo)):
|
| 11560 |
kshitij.so |
2023 |
endOffset = startOffset + 20
|
|
|
2024 |
if (endOffset >= len(itemInfo)):
|
|
|
2025 |
endOffset = len(itemInfo)
|
| 11615 |
kshitij.so |
2026 |
print "pool offset start end count"+str(startOffset)+" "+str(endOffset)+" "+str(count)
|
|
|
2027 |
pool.map(populateScrapingResults,itemInfo[startOffset:endOffset])
|
|
|
2028 |
#t = Process(target=decideCategory,args=(itemInfo[startOffset:endOffset], scraper))
|
|
|
2029 |
#t = threading.Thread(target=partial(decideCategory, itemInfo[startOffset:endOffset], scraper))
|
| 11560 |
kshitij.so |
2030 |
#t = threading.Thread(target=partial(test, startOffset, endOffset))
|
| 11615 |
kshitij.so |
2031 |
#threads.append(t)
|
| 11560 |
kshitij.so |
2032 |
startOffset = startOffset + 20
|
| 11615 |
kshitij.so |
2033 |
count+=1
|
|
|
2034 |
#[t.start() for t in threads]
|
|
|
2035 |
#[t.join() for t in threads]
|
|
|
2036 |
#threads = []
|
|
|
2037 |
print "terminating while"
|
|
|
2038 |
pool.close()
|
|
|
2039 |
pool.join()
|
|
|
2040 |
print "joining threads"
|
|
|
2041 |
print "returning offset******"
|
| 11560 |
kshitij.so |
2042 |
return endOffset
|
|
|
2043 |
else:
|
| 11561 |
kshitij.so |
2044 |
count = 0
|
| 11571 |
kshitij.so |
2045 |
pool = ThreadPool(5)
|
| 11581 |
kshitij.so |
2046 |
startOffset = 0
|
| 11560 |
kshitij.so |
2047 |
endOffset = startOffset
|
| 11571 |
kshitij.so |
2048 |
while(count<5 and endOffset<len(itemInfo)):
|
| 11581 |
kshitij.so |
2049 |
endOffset = startOffset + 20
|
| 11560 |
kshitij.so |
2050 |
if (endOffset >= len(itemInfo)):
|
|
|
2051 |
endOffset = len(itemInfo)
|
| 11581 |
kshitij.so |
2052 |
print "pool offset start end count"+str(startOffset)+" "+str(endOffset)+" "+str(count)
|
|
|
2053 |
time.sleep(10)
|
| 11571 |
kshitij.so |
2054 |
pool.map(populateScrapingResults,itemInfo[startOffset:endOffset])
|
| 11561 |
kshitij.so |
2055 |
#t = Process(target=decideCategory,args=(itemInfo[startOffset:endOffset], scraper))
|
| 11560 |
kshitij.so |
2056 |
#t = threading.Thread(target=partial(decideCategory, itemInfo[startOffset:endOffset], scraper))
|
|
|
2057 |
#t = threading.Thread(target=partial(test, startOffset, endOffset))
|
| 11561 |
kshitij.so |
2058 |
#threads.append(t)
|
| 11581 |
kshitij.so |
2059 |
startOffset = startOffset + 20
|
| 11561 |
kshitij.so |
2060 |
count+=1
|
|
|
2061 |
#[t.start() for t in threads]
|
|
|
2062 |
#[t.join() for t in threads]
|
|
|
2063 |
#threads = []
|
| 11581 |
kshitij.so |
2064 |
print "terminating while"
|
| 11563 |
kshitij.so |
2065 |
pool.close()
|
|
|
2066 |
pool.join()
|
| 11581 |
kshitij.so |
2067 |
print "joining threads"
|
|
|
2068 |
time.sleep(5)
|
|
|
2069 |
print "returning offset******"
|
| 11571 |
kshitij.so |
2070 |
time.sleep(5)
|
| 11560 |
kshitij.so |
2071 |
return endOffset
|
|
|
2072 |
|
| 11623 |
kshitij.so |
2073 |
def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
|
|
|
2074 |
if len(successfulAutoDecrease)==0 and len(successfulAutoIncrease)==0 :
|
|
|
2075 |
return
|
|
|
2076 |
xstr = lambda s: s or ""
|
|
|
2077 |
catalog_client = CatalogClient().get_client()
|
|
|
2078 |
inventory_client = InventoryClient().get_client()
|
|
|
2079 |
message="""<html>
|
|
|
2080 |
<body>
|
| 11625 |
kshitij.so |
2081 |
<h3 style="color:red;font-weight:bold;">This is test run.Prices are not being updated by system.</h3>
|
| 11623 |
kshitij.so |
2082 |
<h3>Auto Decrease Items</h3>
|
|
|
2083 |
<table border="1" style="width:100%;">
|
|
|
2084 |
<thead>
|
|
|
2085 |
<tr><th>Item Id</th>
|
|
|
2086 |
<th>Product Name</th>
|
|
|
2087 |
<th>Old Price</th>
|
|
|
2088 |
<th>New Price</th>
|
|
|
2089 |
<th>Old Margin</th>
|
|
|
2090 |
<th>New Margin</th>
|
|
|
2091 |
<th>Flipkart Inventory</th>
|
|
|
2092 |
<th>Sales History</th>
|
|
|
2093 |
</tr></thead>
|
|
|
2094 |
<tbody>"""
|
|
|
2095 |
for item in successfulAutoDecrease:
|
|
|
2096 |
it = Item.query.filter_by(id=item.item_id).one()
|
|
|
2097 |
mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
|
|
|
2098 |
fkItem = FlipkartItem.get_by(item_id=item.item_id)
|
|
|
2099 |
warehouse = inventory_client.getWarehouse(fkItem.warehouseId)
|
|
|
2100 |
vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
|
|
|
2101 |
newMargin = round(getNewOurTp(mpItem,item.proposedSellingPrice) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice))
|
|
|
2102 |
message+="""<tr>
|
|
|
2103 |
<td style="text-align:center">"""+str(item.item_id)+"""</td>
|
|
|
2104 |
<td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
|
|
|
2105 |
<td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
|
|
|
2106 |
<td style="text-align:center">"""+str(math.ceil(item.proposedSellingPrice))+"""</td>
|
|
|
2107 |
<td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
|
|
|
2108 |
<td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/item.proposedSellingPrice)*100,1))+"%)"+"""</td>
|
|
|
2109 |
<td style="text-align:center">"""+str(item.ourInventory)+"""</td>
|
|
|
2110 |
<td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
|
|
|
2111 |
</tr>"""
|
|
|
2112 |
message+="""</tbody></table><h3>Auto Increase Items</h3><table border="1" style="width:100%;">
|
|
|
2113 |
<thead>
|
|
|
2114 |
<tr><th>Item Id</th>
|
|
|
2115 |
<th>Product Name</th>
|
|
|
2116 |
<th>Old Price</th>
|
|
|
2117 |
<th>New Price</th>
|
|
|
2118 |
<th>Old Margin</th>
|
|
|
2119 |
<th>New Margin</th>
|
|
|
2120 |
<th>Flipkart Inventory</th>
|
|
|
2121 |
<th>Sales History</th>
|
|
|
2122 |
</tr></thead>
|
|
|
2123 |
<tbody>"""
|
|
|
2124 |
for item in successfulAutoIncrease:
|
|
|
2125 |
it = Item.query.filter_by(id=item.item_id).one()
|
|
|
2126 |
mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
|
|
|
2127 |
fkItem = FlipkartItem.get_by(item_id=item.item_id)
|
|
|
2128 |
warehouse = inventory_client.getWarehouse(fkItem.warehouseId)
|
|
|
2129 |
vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
|
|
|
2130 |
newMargin = round(getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
|
|
|
2131 |
message+="""<tr>
|
|
|
2132 |
<td style="text-align:center">"""+str(item.item_id)+"""</td>
|
|
|
2133 |
<td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
|
|
|
2134 |
<td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
|
|
|
2135 |
<td style="text-align:center">"""+str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))+"""</td>
|
|
|
2136 |
<td style="text-align:center">"""+str(round((item.margin),1))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
|
|
|
2137 |
<td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))*100,1))+"%)"+"""</td>
|
|
|
2138 |
<td style="text-align:center">"""+str(item.ourInventory)+"""</td>
|
|
|
2139 |
<td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
|
|
|
2140 |
</tr>"""
|
|
|
2141 |
message+="""</tbody></table></body></html>"""
|
|
|
2142 |
print message
|
|
|
2143 |
mailServer = smtplib.SMTP("smtp.gmail.com", 587)
|
|
|
2144 |
mailServer.ehlo()
|
|
|
2145 |
mailServer.starttls()
|
|
|
2146 |
mailServer.ehlo()
|
|
|
2147 |
|
|
|
2148 |
recipients = ['kshitij.sood@saholic.com']
|
|
|
2149 |
#recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','vikram.raghav@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
|
|
|
2150 |
msg = MIMEMultipart()
|
|
|
2151 |
msg['Subject'] = "Flipkart Auto Pricing" + ' - ' + str(datetime.now())
|
|
|
2152 |
msg['From'] = ""
|
|
|
2153 |
msg['To'] = ",".join(recipients)
|
|
|
2154 |
msg.preamble = "Flipkart Auto Pricing" + ' - ' + str(datetime.now())
|
|
|
2155 |
html_msg = MIMEText(message, 'html')
|
|
|
2156 |
msg.attach(html_msg)
|
|
|
2157 |
try:
|
|
|
2158 |
mailServer.login("build@shop2020.in", "cafe@nes")
|
|
|
2159 |
#mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
|
|
|
2160 |
mailServer.sendmail("cafe@nes", recipients, msg.as_string())
|
|
|
2161 |
except Exception as e:
|
|
|
2162 |
print e
|
|
|
2163 |
print "Unable to send pricing mail.Lets try with local SMTP."
|
|
|
2164 |
smtpServer = smtplib.SMTP('localhost')
|
|
|
2165 |
smtpServer.set_debuglevel(1)
|
|
|
2166 |
sender = 'support@shop2020.in'
|
|
|
2167 |
try:
|
|
|
2168 |
smtpServer.sendmail(sender, recipients, msg.as_string())
|
|
|
2169 |
print "Successfully sent email"
|
|
|
2170 |
except:
|
|
|
2171 |
print "Error: unable to send email."
|
|
|
2172 |
|
|
|
2173 |
def processLostBuyBoxItems(previousProcessingTimestamp,currentTimestamp):
|
|
|
2174 |
previous_buy_box = session.query(MarketPlaceHistory.item_id).filter(MarketPlaceHistory.timestamp==previousProcessingTimestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(or_(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX,MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP)).all()
|
|
|
2175 |
cant_compete = session.query(MarketPlaceHistory.item_id).filter(MarketPlaceHistory.timestamp==currentTimestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
|
|
|
2176 |
if previous_buy_box is None:
|
|
|
2177 |
print "No item in buy box for last run"
|
|
|
2178 |
return
|
|
|
2179 |
lost_buy_box = list(set(list(zip(*previous_buy_box)[0]))&set(list(zip(*cant_compete)[0])))
|
|
|
2180 |
if len(lost_buy_box)==0:
|
|
|
2181 |
return
|
|
|
2182 |
xstr = lambda s: s or ""
|
|
|
2183 |
message="""<html>
|
|
|
2184 |
<body>
|
|
|
2185 |
<h3>Lost Buy Box</h3>
|
|
|
2186 |
<table border="1" style="width:100%;">
|
|
|
2187 |
<thead>
|
|
|
2188 |
<tr><th>Item Id</th>
|
|
|
2189 |
<th>Product Name</th>
|
|
|
2190 |
<th>Current Price</th>
|
|
|
2191 |
<th>Current TP</th>
|
|
|
2192 |
<th>Current Margin</th>
|
|
|
2193 |
<th>Competition TP</th>
|
|
|
2194 |
<th>Lowest Possible TP</th>
|
|
|
2195 |
<th>NLC</th>
|
|
|
2196 |
<th>Target NLC</th>
|
|
|
2197 |
<th>Flipkart Inventory</th>
|
|
|
2198 |
<th>Total Inventory</th>
|
|
|
2199 |
<th>Sales History</th>
|
|
|
2200 |
</tr></thead>
|
|
|
2201 |
<tbody>"""
|
|
|
2202 |
items = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==currentTimestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.item_id.in_(lost_buy_box)).all()
|
|
|
2203 |
for item in items:
|
|
|
2204 |
it = Item.query.filter_by(id=item.item_id).one()
|
|
|
2205 |
netInventory=''
|
|
|
2206 |
if not inventoryMap.has_key(item.item_id):
|
|
|
2207 |
netInventory='Info Not Available'
|
|
|
2208 |
else:
|
|
|
2209 |
netInventory = str(getNetAvailability(inventoryMap.get(item.item_id)))
|
|
|
2210 |
message+="""<tr>
|
|
|
2211 |
<td style="text-align:center">"""+str(item.item_id)+"""</td>
|
|
|
2212 |
<td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
|
|
|
2213 |
<td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
|
|
|
2214 |
<td style="text-align:center">"""+str(item.ourTp)+"""</td>
|
|
|
2215 |
<td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
|
|
|
2216 |
<td style="text-align:center">"""+str(item.lowestTp)+"""</td>
|
|
|
2217 |
<td style="text-align:center">"""+str(item.lowestPossibleTp)+"""</td>
|
|
|
2218 |
<td style="text-align:center">"""+str(item.ourNlc)+"""</td>
|
|
|
2219 |
<td style="text-align:center">"""+str(item.targetNlc)+"""</td>
|
|
|
2220 |
<td style="text-align:center">"""+str(item.ourInventory)+"""</td>
|
|
|
2221 |
<td style="text-align:center">"""+netInventory+"""</td>
|
|
|
2222 |
<td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
|
|
|
2223 |
</tr>"""
|
|
|
2224 |
message+="""</tbody></table></body></html>"""
|
|
|
2225 |
print message
|
|
|
2226 |
mailServer = smtplib.SMTP("smtp.gmail.com", 587)
|
|
|
2227 |
mailServer.ehlo()
|
|
|
2228 |
mailServer.starttls()
|
|
|
2229 |
mailServer.ehlo()
|
|
|
2230 |
|
|
|
2231 |
recipients = ['kshitij.sood@saholic.com']
|
|
|
2232 |
#recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','vikram.raghav@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
|
|
|
2233 |
msg = MIMEMultipart()
|
|
|
2234 |
msg['Subject'] = "Flipkart Lost Buy Box" + ' - ' + str(datetime.now())
|
|
|
2235 |
msg['From'] = ""
|
|
|
2236 |
msg['To'] = ",".join(recipients)
|
|
|
2237 |
msg.preamble = "Flipkart Lost Buy Box" + ' - ' + str(datetime.now())
|
|
|
2238 |
html_msg = MIMEText(message, 'html')
|
|
|
2239 |
msg.attach(html_msg)
|
|
|
2240 |
try:
|
|
|
2241 |
mailServer.login("build@shop2020.in", "cafe@nes")
|
|
|
2242 |
#mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
|
|
|
2243 |
mailServer.sendmail("cafe@nes", recipients, msg.as_string())
|
|
|
2244 |
except Exception as e:
|
|
|
2245 |
print e
|
|
|
2246 |
print "Unable to send lost buy box mail.Lets try local SMTP"
|
|
|
2247 |
smtpServer = smtplib.SMTP('localhost')
|
|
|
2248 |
smtpServer.set_debuglevel(1)
|
|
|
2249 |
sender = 'support@shop2020.in'
|
|
|
2250 |
try:
|
|
|
2251 |
smtpServer.sendmail(sender, recipients, msg.as_string())
|
|
|
2252 |
print "Successfully sent email"
|
|
|
2253 |
except:
|
|
|
2254 |
print "Error: unable to send email."
|
|
|
2255 |
|
|
|
2256 |
def cheapButNotPrefAlert(timestamp):
|
|
|
2257 |
cheap_but_not_pref = session.query(MarketPlaceHistory,Item).join(Item,MarketPlaceHistory.item_id==Item.id).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CHEAP_BUT_NOT_PREF).all()
|
|
|
2258 |
if cheap_but_not_pref is None:
|
|
|
2259 |
return
|
|
|
2260 |
xstr = lambda s: s or ""
|
|
|
2261 |
message="""<html>
|
|
|
2262 |
<body>
|
|
|
2263 |
<h3>Cheap But Not Preferred</h3>
|
|
|
2264 |
<table border="1" style="width:100%;">
|
|
|
2265 |
<thead>
|
|
|
2266 |
<tr><th>Item Id</th>
|
|
|
2267 |
<th>Product Name</th>
|
|
|
2268 |
<th>Current Price</th>
|
|
|
2269 |
<th>Preffered Seller</th>
|
|
|
2270 |
<th>Preffered Seller SP</th>
|
|
|
2271 |
<th>Flipkart Inventory</th>
|
|
|
2272 |
<th>Total Inventory</th>
|
|
|
2273 |
<th>Sales History</th>
|
|
|
2274 |
</tr></thead>
|
|
|
2275 |
<tbody>"""
|
|
|
2276 |
for item in cheap_but_not_pref:
|
|
|
2277 |
mpHistory = item[0]
|
|
|
2278 |
catItem = item[1]
|
|
|
2279 |
netInventory=''
|
|
|
2280 |
if not inventoryMap.has_key(mpHistory.item_id):
|
|
|
2281 |
netInventory='Info Not Available'
|
|
|
2282 |
else:
|
|
|
2283 |
netInventory = str(getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
|
|
2284 |
if mpHistory.prefferedSellerSellingPrice=='WS Retail':
|
|
|
2285 |
style="""background-color:red;\""""
|
|
|
2286 |
else:
|
|
|
2287 |
style="\""
|
|
|
2288 |
message+="""<tr>
|
|
|
2289 |
<td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.item_id)+"""</td>
|
|
|
2290 |
<td style="text-align:center;"""+str(style)+""">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
|
|
|
2291 |
<td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourSellingPrice)+"""</td>
|
|
|
2292 |
<td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerName)+"""</td>
|
|
|
2293 |
<td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerSellingPrice)+"""</td>
|
|
|
2294 |
<td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourInventory)+"""</td>
|
|
|
2295 |
<td style="text-align:center;"""+str(style)+""">"""+netInventory+"""</td>
|
|
|
2296 |
<td style="text-align:center;"""+str(style)+""">"""+getOosString((itemSaleMap.get(mpHistory.item_id))[1])+"""</td>
|
|
|
2297 |
</tr>"""
|
|
|
2298 |
message+="""</tbody></table></body></html>"""
|
|
|
2299 |
print message
|
|
|
2300 |
mailServer = smtplib.SMTP("smtp.gmail.com", 587)
|
|
|
2301 |
mailServer.ehlo()
|
|
|
2302 |
mailServer.starttls()
|
|
|
2303 |
mailServer.ehlo()
|
|
|
2304 |
|
|
|
2305 |
recipients = ['kshitij.sood@saholic.com']
|
|
|
2306 |
#recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','vikram.raghav@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
|
|
|
2307 |
msg = MIMEMultipart()
|
|
|
2308 |
msg['Subject'] = "Flipkart Cheap But Not In BuyBox Items" + ' - ' + str(datetime.now())
|
|
|
2309 |
msg['From'] = ""
|
|
|
2310 |
msg['To'] = ",".join(recipients)
|
|
|
2311 |
msg.preamble = "Flipkart Cheap But Not In BuyBox Items" + ' - ' + str(datetime.now())
|
|
|
2312 |
html_msg = MIMEText(message, 'html')
|
|
|
2313 |
msg.attach(html_msg)
|
|
|
2314 |
try:
|
|
|
2315 |
mailServer.login("build@shop2020.in", "cafe@nes")
|
|
|
2316 |
#mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
|
|
|
2317 |
mailServer.sendmail("cafe@nes", recipients, msg.as_string())
|
|
|
2318 |
except Exception as e:
|
|
|
2319 |
print e
|
|
|
2320 |
print "Unable to send Flipkart Cheap But Not In BuyBox Items mail.Lets try local SMTP"
|
|
|
2321 |
smtpServer = smtplib.SMTP('localhost')
|
|
|
2322 |
smtpServer.set_debuglevel(1)
|
|
|
2323 |
sender = 'support@shop2020.in'
|
|
|
2324 |
try:
|
|
|
2325 |
smtpServer.sendmail(sender, recipients, msg.as_string())
|
|
|
2326 |
print "Successfully sent email"
|
|
|
2327 |
except:
|
|
|
2328 |
print "Error: unable to send email."
|
|
|
2329 |
|
| 11560 |
kshitij.so |
2330 |
|
| 11623 |
kshitij.so |
2331 |
|
| 11193 |
kshitij.so |
2332 |
def main():
|
|
|
2333 |
parser = optparse.OptionParser()
|
|
|
2334 |
parser.add_option("-t", "--type", dest="runType",
|
|
|
2335 |
default="FULL", type="string",
|
|
|
2336 |
help="Run type FULL or FAVOURITE")
|
|
|
2337 |
(options, args) = parser.parse_args()
|
|
|
2338 |
if options.runType not in ('FULL','FAVOURITE'):
|
|
|
2339 |
print "Run type argument illegal."
|
|
|
2340 |
sys.exit(1)
|
|
|
2341 |
timestamp = datetime.now()
|
| 11581 |
kshitij.so |
2342 |
previousProcessingTimestamp = session.query(func.max(MarketPlaceHistory.timestamp)).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).one()
|
| 11193 |
kshitij.so |
2343 |
itemInfo= populateStuff(options.runType,timestamp)
|
| 11560 |
kshitij.so |
2344 |
itemsPopulated = 0
|
| 11615 |
kshitij.so |
2345 |
while (len(itemInfo)>0):
|
| 11560 |
kshitij.so |
2346 |
itemsPopulated = threadsToSpawn(options.runType,itemInfo,scraper,itemsPopulated)
|
| 11581 |
kshitij.so |
2347 |
cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap = decideCategory(itemInfo[0:itemsPopulated])
|
|
|
2348 |
itemInfo[0:itemsPopulated] = []
|
|
|
2349 |
commitExceptionList(exceptionItems,timestamp)
|
|
|
2350 |
commitCantCompete(cantCompete,timestamp)
|
|
|
2351 |
commitBuyBox(buyBoxItems,timestamp)
|
|
|
2352 |
commitCompetitive(competitive,timestamp)
|
|
|
2353 |
commitCompetitiveNoInventory(competitiveNoInventory,timestamp)
|
|
|
2354 |
commitNegativeMargin(negativeMargin,timestamp)
|
|
|
2355 |
commitCheapButNotPref(cheapButNotPref,timestamp)
|
|
|
2356 |
commitPrefButNotCheap(prefButNotCheap, timestamp)
|
|
|
2357 |
cantCompete[:], buyBoxItems[:], competitive[:], competitiveNoInventory[:], exceptionItems[:], negativeMargin[:], cheapButNotPref[:], prefButNotCheap[:] =[],[],[],[],[],[],[],[]
|
|
|
2358 |
|
| 11615 |
kshitij.so |
2359 |
successfulAutoDecrease = fetchItemsForAutoDecrease(timestamp)
|
|
|
2360 |
successfulAutoIncrease = fetchItemsForAutoIncrease(timestamp)
|
|
|
2361 |
if options.runType=='FULL':
|
|
|
2362 |
previousAutoFav, nowAutoFav = markAutoFavourite()
|
| 11621 |
kshitij.so |
2363 |
if options.runType =='FULL':
|
|
|
2364 |
write_report(previousAutoFav,nowAutoFav,timestamp,options.runType)
|
|
|
2365 |
else:
|
|
|
2366 |
write_report(None,None,timestamp,options.runType)
|
| 11623 |
kshitij.so |
2367 |
sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease)
|
| 11625 |
kshitij.so |
2368 |
if previousProcessingTimestamp[0] is not None:
|
|
|
2369 |
processLostBuyBoxItems(previousProcessingTimestamp[0],timestamp)
|
| 11623 |
kshitij.so |
2370 |
if options.runType=='FULL':
|
|
|
2371 |
cheapButNotPrefAlert(timestamp)
|
| 11193 |
kshitij.so |
2372 |
|
|
|
2373 |
if __name__ == '__main__':
|
|
|
2374 |
main()
|