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