| Line 43... |
Line 43... |
| 43 |
DataService.initialize(db_hostname=host)
|
43 |
DataService.initialize(db_hostname=host)
|
| 44 |
|
44 |
|
| 45 |
inventoryMap = {}
|
45 |
inventoryMap = {}
|
| 46 |
itemSaleMap = {}
|
46 |
itemSaleMap = {}
|
| 47 |
scraper = FlipkartScraper.FlipkartScraper()
|
47 |
scraper = FlipkartScraper.FlipkartScraper()
|
| 48 |
testcount = 0
|
48 |
categoryMap = {}
|
| 49 |
|
49 |
|
| 50 |
class __FlipkartDetails:
|
50 |
class __FlipkartDetails:
|
| 51 |
|
51 |
|
| 52 |
def __init__(self,rank ,ourSp , secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, shippingTimeLowerLimitLowestSeller,shippingTimeUpperLimitLowestSeller, \
|
52 |
def __init__(self,rank ,ourSp , secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, shippingTimeLowerLimitLowestSeller,shippingTimeUpperLimitLowestSeller, \
|
| 53 |
shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller, shippingTimeLowerLimitOur, shippingTimeUpperLimitOur, shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller, totalAvailableSeller, lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
|
53 |
shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller, shippingTimeLowerLimitOur, shippingTimeUpperLimitOur, shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller, totalAvailableSeller, lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
|
| Line 125... |
Line 125... |
| 125 |
mpHistory.reason = reason
|
125 |
mpHistory.reason = reason
|
| 126 |
|
126 |
|
| 127 |
def fetchItemsForAutoDecrease(time):
|
127 |
def fetchItemsForAutoDecrease(time):
|
| 128 |
successfulAutoDecrease = []
|
128 |
successfulAutoDecrease = []
|
| 129 |
autoDecrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
129 |
autoDecrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
| 130 |
.filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE)\
|
130 |
.filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(or_(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE,MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP ))\
|
| 131 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketplaceItems.autoDecrement==True).all()
|
131 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketplaceItems.autoDecrement==True).all()
|
| 132 |
inventory_client = InventoryClient().get_client()
|
132 |
inventory_client = InventoryClient().get_client()
|
| 133 |
global inventoryMap
|
133 |
global inventoryMap
|
| 134 |
inventoryMap = inventory_client.getInventorySnapshot(0)
|
134 |
inventoryMap = inventory_client.getInventorySnapshot(0)
|
| 135 |
for autoDecrementItem in autoDecrementItems:
|
135 |
for autoDecrementItem in autoDecrementItems:
|
| 136 |
if not autoDecrementItem.competitiveCategory == CompetitionCategory.COMPETITIVE:
|
- |
|
| 137 |
markReasonForMpItem(autoDecrementItem,'Category is '+CompetitionCategory._VALUES_TO_NAMES.get(autoDecrementItem.competitiveCategory),Decision.AUTO_DECREMENT_FAILED)
|
- |
|
| 138 |
continue
|
- |
|
| 139 |
if not autoDecrementItem.risky:
|
136 |
if not autoDecrementItem.risky:
|
| 140 |
markReasonForMpItem(autoDecrementItem,'Item is not risky',Decision.AUTO_DECREMENT_FAILED)
|
137 |
markReasonForMpItem(autoDecrementItem,'Item is not risky',Decision.AUTO_DECREMENT_FAILED)
|
| 141 |
continue
|
138 |
continue
|
| 142 |
if math.ceil(autoDecrementItem.proposedSellingPrice) >= autoDecrementItem.ourSellingPrice:
|
139 |
if math.ceil(autoDecrementItem.proposedSellingPrice) >= autoDecrementItem.ourSellingPrice:
|
| 143 |
markReasonForMpItem(autoDecrementItem,'Proposed SP greater than or equal to current SP',Decision.AUTO_DECREMENT_FAILED)
|
140 |
markReasonForMpItem(autoDecrementItem,'Proposed SP greater than or equal to current SP',Decision.AUTO_DECREMENT_FAILED)
|
| Line 169... |
Line 166... |
| 169 |
except ZeroDivisionError,e:
|
166 |
except ZeroDivisionError,e:
|
| 170 |
daysOfStock = float("inf")
|
167 |
daysOfStock = float("inf")
|
| 171 |
if daysOfStock<2:
|
168 |
if daysOfStock<2:
|
| 172 |
markReasonForMpItem(autoDecrementItem,'Our stock is not enough',Decision.AUTO_DECREMENT_FAILED)
|
169 |
markReasonForMpItem(autoDecrementItem,'Our stock is not enough',Decision.AUTO_DECREMENT_FAILED)
|
| 173 |
continue
|
170 |
continue
|
| - |
|
171 |
|
| - |
|
172 |
if autoDecrementItem.competitiveCategory == CompetitionCategory.PREF_BUT_NOT_CHEAP:
|
| - |
|
173 |
avgSaleLastTwoDay = (itemSaleMap.get(autoDecrementItem.item_id))[6]
|
| - |
|
174 |
if avgSaleLastTwoDay >= 3:
|
| - |
|
175 |
markReasonForMpItem(autoDecrementItem,'Last two day avg sale is greater than 2',Decision.AUTO_DECREMENT_FAILED)
|
| - |
|
176 |
continue
|
| 174 |
|
177 |
|
| 175 |
autoDecrementItem.ourEnoughStock = True
|
178 |
autoDecrementItem.ourEnoughStock = True
|
| 176 |
autoDecrementItem.decision = Decision.AUTO_DECREMENT_SUCCESS
|
179 |
autoDecrementItem.decision = Decision.AUTO_DECREMENT_SUCCESS
|
| 177 |
autoDecrementItem.reason = 'All conditions for auto decrement true'
|
180 |
autoDecrementItem.reason = 'All conditions for auto decrement true'
|
| 178 |
successfulAutoDecrease.append(autoDecrementItem)
|
181 |
successfulAutoDecrease.append(autoDecrementItem)
|
| Line 265... |
Line 268... |
| 265 |
exceptionItems.append(mpHistory)
|
268 |
exceptionItems.append(mpHistory)
|
| 266 |
session.commit()
|
269 |
session.commit()
|
| 267 |
return exceptionItems
|
270 |
return exceptionItems
|
| 268 |
|
271 |
|
| 269 |
def commitCantCompete(cantCompete,timestamp):
|
272 |
def commitCantCompete(cantCompete,timestamp):
|
| 270 |
cantComepeteItems = []
|
- |
|
| 271 |
for item in cantCompete:
|
273 |
for item in cantCompete:
|
| 272 |
flipkartDetails = item[0]
|
274 |
flipkartDetails = item[0]
|
| 273 |
flipkartItemInfo = item[1]
|
275 |
flipkartItemInfo = item[1]
|
| 274 |
flipkartPricing = item[2]
|
276 |
flipkartPricing = item[2]
|
| 275 |
mpItem = item[3]
|
277 |
mpItem = item[3]
|
| Line 315... |
Line 317... |
| 315 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
317 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
| 316 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
318 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
| 317 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
319 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
| 318 |
mpHistory.timestamp = timestamp
|
320 |
mpHistory.timestamp = timestamp
|
| 319 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
321 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
| 320 |
cantComepeteItems.append(mpHistory)
|
- |
|
| 321 |
session.commit()
|
322 |
session.commit()
|
| 322 |
return cantComepeteItems
|
- |
|
| 323 |
|
323 |
|
| 324 |
def commitBuyBox(buyBoxItems,timestamp):
|
324 |
def commitBuyBox(buyBoxItems,timestamp):
|
| 325 |
buyBoxList = []
|
- |
|
| 326 |
for item in buyBoxItems:
|
325 |
for item in buyBoxItems:
|
| 327 |
flipkartDetails = item[0]
|
326 |
flipkartDetails = item[0]
|
| 328 |
flipkartItemInfo = item[1]
|
327 |
flipkartItemInfo = item[1]
|
| 329 |
flipkartPricing = item[2]
|
328 |
flipkartPricing = item[2]
|
| 330 |
mpItem = item[3]
|
329 |
mpItem = item[3]
|
| Line 370... |
Line 369... |
| 370 |
mpHistory.marginIncreasedPotential = proposed_tp - flipkartPricing.ourTp
|
369 |
mpHistory.marginIncreasedPotential = proposed_tp - flipkartPricing.ourTp
|
| 371 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
370 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
| 372 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
371 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
| 373 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
372 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
| 374 |
mpHistory.timestamp = timestamp
|
373 |
mpHistory.timestamp = timestamp
|
| 375 |
buyBoxList.append(mpHistory)
|
- |
|
| 376 |
session.commit()
|
374 |
session.commit()
|
| 377 |
return buyBoxList
|
- |
|
| 378 |
|
375 |
|
| 379 |
def commitCompetitiveNoInventory(competitiveNoInventory,timestamp):
|
376 |
def commitCompetitiveNoInventory(competitiveNoInventory,timestamp):
|
| 380 |
competitiveNoInventoryItems = []
|
- |
|
| 381 |
for item in competitiveNoInventory:
|
377 |
for item in competitiveNoInventory:
|
| 382 |
flipkartDetails = item[0]
|
378 |
flipkartDetails = item[0]
|
| 383 |
flipkartItemInfo = item[1]
|
379 |
flipkartItemInfo = item[1]
|
| 384 |
flipkartPricing = item[2]
|
380 |
flipkartPricing = item[2]
|
| 385 |
mpItem = item[3]
|
381 |
mpItem = item[3]
|
| Line 423... |
Line 419... |
| 423 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
419 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
| 424 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
420 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
| 425 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
421 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
| 426 |
mpHistory.timestamp = timestamp
|
422 |
mpHistory.timestamp = timestamp
|
| 427 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
423 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
| 428 |
competitiveNoInventoryItems.append(mpHistory)
|
- |
|
| 429 |
session.commit()
|
424 |
session.commit()
|
| 430 |
return competitiveNoInventoryItems
|
- |
|
| 431 |
|
425 |
|
| 432 |
def commitCompetitive(competitive,timestamp):
|
426 |
def commitCompetitive(competitive,timestamp):
|
| 433 |
competitiveItems = []
|
- |
|
| 434 |
for item in competitive:
|
427 |
for item in competitive:
|
| 435 |
flipkartDetails = item[0]
|
428 |
flipkartDetails = item[0]
|
| 436 |
flipkartItemInfo = item[1]
|
429 |
flipkartItemInfo = item[1]
|
| 437 |
flipkartPricing = item[2]
|
430 |
flipkartPricing = item[2]
|
| 438 |
mpItem = item[3]
|
431 |
mpItem = item[3]
|
| Line 476... |
Line 469... |
| 476 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
469 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
| 477 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
470 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
| 478 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
471 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
| 479 |
mpHistory.timestamp = timestamp
|
472 |
mpHistory.timestamp = timestamp
|
| 480 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
473 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
| 481 |
competitiveItems.append(mpHistory)
|
- |
|
| 482 |
session.commit()
|
474 |
session.commit()
|
| 483 |
return competitiveItems
|
- |
|
| 484 |
|
475 |
|
| 485 |
def commitNegativeMargin(negativeMargin,timestamp):
|
476 |
def commitNegativeMargin(negativeMargin,timestamp):
|
| 486 |
negativeItems = []
|
- |
|
| 487 |
for item in negativeMargin:
|
477 |
for item in negativeMargin:
|
| 488 |
flipkartDetails = item[0]
|
478 |
flipkartDetails = item[0]
|
| 489 |
flipkartItemInfo = item[1]
|
479 |
flipkartItemInfo = item[1]
|
| 490 |
flipkartPricing = item[2]
|
480 |
flipkartPricing = item[2]
|
| 491 |
mpHistory = MarketPlaceHistory()
|
481 |
mpHistory = MarketPlaceHistory()
|
| Line 524... |
Line 514... |
| 524 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
514 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
| 525 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
515 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
| 526 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
516 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
| 527 |
mpHistory.timestamp = timestamp
|
517 |
mpHistory.timestamp = timestamp
|
| 528 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
518 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
| 529 |
negativeItems.append(mpHistory)
|
- |
|
| 530 |
session.commit()
|
519 |
session.commit()
|
| 531 |
return negativeItems
|
- |
|
| 532 |
|
520 |
|
| 533 |
def commitCheapButNotPref(cheapButNotPref,timestamp):
|
521 |
def commitCheapButNotPref(cheapButNotPref,timestamp):
|
| 534 |
cheapButNotPrefItems = []
|
- |
|
| 535 |
for item in cheapButNotPref:
|
522 |
for item in cheapButNotPref:
|
| 536 |
flipkartDetails = item[0]
|
523 |
flipkartDetails = item[0]
|
| 537 |
flipkartItemInfo = item[1]
|
524 |
flipkartItemInfo = item[1]
|
| 538 |
flipkartPricing = item[2]
|
525 |
flipkartPricing = item[2]
|
| 539 |
mpHistory = MarketPlaceHistory()
|
526 |
mpHistory = MarketPlaceHistory()
|
| Line 572... |
Line 559... |
| 572 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
559 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
| 573 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
560 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
| 574 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
561 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
| 575 |
mpHistory.timestamp = timestamp
|
562 |
mpHistory.timestamp = timestamp
|
| 576 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
563 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
| 577 |
cheapButNotPrefItems.append(mpHistory)
|
- |
|
| 578 |
session.commit()
|
564 |
session.commit()
|
| 579 |
return cheapButNotPrefItems
|
- |
|
| 580 |
|
565 |
|
| 581 |
def commitPrefButNotCheap(prefButNotCheap,timestamp):
|
566 |
def commitPrefButNotCheap(prefButNotCheap,timestamp):
|
| 582 |
prefButNotCheapItems = []
|
- |
|
| 583 |
for item in prefButNotCheap:
|
567 |
for item in prefButNotCheap:
|
| 584 |
flipkartDetails = item[0]
|
568 |
flipkartDetails = item[0]
|
| 585 |
flipkartItemInfo = item[1]
|
569 |
flipkartItemInfo = item[1]
|
| 586 |
flipkartPricing = item[2]
|
570 |
flipkartPricing = item[2]
|
| 587 |
mpHistory = MarketPlaceHistory()
|
571 |
mpHistory = MarketPlaceHistory()
|
| Line 620... |
Line 604... |
| 620 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
604 |
mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
|
| 621 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
605 |
mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
|
| 622 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
606 |
mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
| 623 |
mpHistory.timestamp = timestamp
|
607 |
mpHistory.timestamp = timestamp
|
| 624 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
608 |
mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
|
| 625 |
prefButNotCheapItems.append(mpHistory)
|
- |
|
| 626 |
session.commit()
|
609 |
session.commit()
|
| 627 |
return prefButNotCheapItems
|
- |
|
| 628 |
|
610 |
|
| 629 |
def populateStuff(runType,time):
|
611 |
def populateStuff(runType,time):
|
| 630 |
global itemSaleMap
|
612 |
global itemSaleMap
|
| - |
|
613 |
global categoryMap
|
| - |
|
614 |
|
| 631 |
itemInfo = []
|
615 |
itemInfo = []
|
| 632 |
if runType=='FAVOURITE':
|
616 |
if runType=='FAVOURITE':
|
| 633 |
items = session.query(FlipkartItem,MarketplaceItems).join((MarketplaceItems,FlipkartItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.FLIPKART).\
|
617 |
items = session.query(FlipkartItem,MarketplaceItems).join((MarketplaceItems,FlipkartItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.FLIPKART).\
|
| 634 |
filter(or_(MarketplaceItems.autoFavourite==True, MarketplaceItems.manualFavourite==True)).all()
|
618 |
filter(or_(MarketplaceItems.autoFavourite==True, MarketplaceItems.manualFavourite==True)).all()
|
| 635 |
else:
|
619 |
else:
|
| Line 642... |
Line 626... |
| 642 |
flipkart_item = item[0]
|
626 |
flipkart_item = item[0]
|
| 643 |
mp_item = item[1]
|
627 |
mp_item = item[1]
|
| 644 |
it = Item.query.filter_by(id=flipkart_item.item_id).one()
|
628 |
it = Item.query.filter_by(id=flipkart_item.item_id).one()
|
| 645 |
category = Category.query.filter_by(id=it.category).one()
|
629 |
category = Category.query.filter_by(id=it.category).one()
|
| 646 |
parent_category = Category.query.filter_by(id=category.parent_category_id).first()
|
630 |
parent_category = Category.query.filter_by(id=category.parent_category_id).first()
|
| - |
|
631 |
if not categoryMap.has_key(category.id):
|
| - |
|
632 |
temp = []
|
| - |
|
633 |
temp.append(category.displayName)
|
| - |
|
634 |
temp.append(parent_category. parent_category.display_name)
|
| - |
|
635 |
categoryMap[category.id] = temp
|
| 647 |
sip = SourceItemPercentage.query.filter(SourceItemPercentage.item_id==it.id).filter(SourceItemPercentage.source==OrderSource.FLIPKART).filter(SourceItemPercentage.startDate<=time).filter(SourceItemPercentage.expiryDate>=time).first()
|
636 |
sip = SourceItemPercentage.query.filter(SourceItemPercentage.item_id==it.id).filter(SourceItemPercentage.source==OrderSource.FLIPKART).filter(SourceItemPercentage.startDate<=time).filter(SourceItemPercentage.expiryDate>=time).first()
|
| 648 |
sourcePercentage = None
|
637 |
sourcePercentage = None
|
| 649 |
if sip is not None:
|
638 |
if sip is not None:
|
| 650 |
sourcePercentage = sip
|
639 |
sourcePercentage = sip
|
| 651 |
else:
|
640 |
else:
|
| Line 659... |
Line 648... |
| 659 |
warehouse = inventory_client.getWarehouse(flipkart_item.warehouseId)
|
648 |
warehouse = inventory_client.getWarehouse(flipkart_item.warehouseId)
|
| 660 |
itemSaleList = []
|
649 |
itemSaleList = []
|
| 661 |
oosForAllSources = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, 0, 3)
|
650 |
oosForAllSources = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, 0, 3)
|
| 662 |
oosForFlipkart = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 5)
|
651 |
oosForFlipkart = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 5)
|
| 663 |
oosForFlipkartLastDay = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 1)
|
652 |
oosForFlipkartLastDay = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 1)
|
| - |
|
653 |
oosForFlipkartTwoDay = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 2)
|
| 664 |
itemSaleList.append(oosForAllSources)
|
654 |
itemSaleList.append(oosForAllSources)
|
| 665 |
itemSaleList.append(oosForFlipkart)
|
655 |
itemSaleList.append(oosForFlipkart)
|
| 666 |
itemSaleList.append(calculateAverageSale(oosForAllSources))
|
656 |
itemSaleList.append(calculateAverageSale(oosForAllSources))
|
| 667 |
itemSaleList.append(calculateAverageSale(oosForFlipkart))
|
657 |
itemSaleList.append(calculateAverageSale(oosForFlipkart))
|
| 668 |
itemSaleList.append(calculateAverageSale(oosForFlipkartLastDay))
|
658 |
itemSaleList.append(calculateAverageSale(oosForFlipkartLastDay))
|
| 669 |
itemSaleList.append(calculateTotalSale(oosForFlipkart))
|
659 |
itemSaleList.append(calculateTotalSale(oosForFlipkart))
|
| - |
|
660 |
itemSaleList.append(calculateAverageSale(oosForFlipkartTwoDay))
|
| 670 |
itemSaleMap[flipkart_item.item_id]=itemSaleList
|
661 |
itemSaleMap[flipkart_item.item_id]=itemSaleList
|
| 671 |
|
662 |
|
| 672 |
# try:
|
663 |
# try:
|
| 673 |
# request_url = "https://api.flipkart.net/sellers/skus/%s/listings"%(str(flipkart_item.skuAtFlipkart))
|
664 |
# request_url = "https://api.flipkart.net/sellers/skus/%s/listings"%(str(flipkart_item.skuAtFlipkart))
|
| 674 |
# r = requests.get(request_url, auth=('m2z93iskuj81qiid', '0c7ab6a5-98c0-4cdc-8be3-72c591e0add4'))
|
665 |
# r = requests.get(request_url, auth=('m2z93iskuj81qiid', '0c7ab6a5-98c0-4cdc-8be3-72c591e0add4'))
|
| Line 793... |
Line 784... |
| 793 |
return 'MEDIUM'
|
784 |
return 'MEDIUM'
|
| 794 |
else:
|
785 |
else:
|
| 795 |
return 'LOW'
|
786 |
return 'LOW'
|
| 796 |
|
787 |
|
| 797 |
def decideCategory(itemInfo):
|
788 |
def decideCategory(itemInfo):
|
| 798 |
print "length****",len(itemInfo)
|
- |
|
| 799 |
global itemSaleMap
|
789 |
global itemSaleMap
|
| 800 |
|
790 |
|
| 801 |
cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap = [],[],[],[],[],[],[],[]
|
791 |
cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap = [],[],[],[],[],[],[],[]
|
| 802 |
|
792 |
|
| 803 |
catalog_client = CatalogClient().get_client()
|
793 |
catalog_client = CatalogClient().get_client()
|
| Line 831... |
Line 821... |
| 831 |
|
821 |
|
| 832 |
if (flipkartDetails.ourBuyTrend == 'PrefNCheap'):
|
822 |
if (flipkartDetails.ourBuyTrend == 'PrefNCheap'):
|
| 833 |
temp=[]
|
823 |
temp=[]
|
| 834 |
temp.append(flipkartDetails)
|
824 |
temp.append(flipkartDetails)
|
| 835 |
temp.append(val)
|
825 |
temp.append(val)
|
| 836 |
secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
|
826 |
lowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
|
| 837 |
prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
|
827 |
prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
|
| 838 |
flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
|
828 |
flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
|
| 839 |
temp.append(flipkartPricing)
|
829 |
temp.append(flipkartPricing)
|
| 840 |
temp.append(mpItem)
|
830 |
temp.append(mpItem)
|
| 841 |
prefButNotCheap.append(temp)
|
831 |
prefButNotCheap.append(temp)
|
| Line 843... |
Line 833... |
| 843 |
|
833 |
|
| 844 |
if (flipkartDetails.ourBuyTrend == 'NPrefCheap') and (flipkartDetails.rank==1):
|
834 |
if (flipkartDetails.ourBuyTrend == 'NPrefCheap') and (flipkartDetails.rank==1):
|
| 845 |
temp=[]
|
835 |
temp=[]
|
| 846 |
temp.append(flipkartDetails)
|
836 |
temp.append(flipkartDetails)
|
| 847 |
temp.append(val)
|
837 |
temp.append(val)
|
| - |
|
838 |
secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
|
| 848 |
prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
|
839 |
prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
|
| 849 |
flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
|
840 |
flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
|
| 850 |
temp.append(flipkartPricing)
|
841 |
temp.append(flipkartPricing)
|
| 851 |
temp.append(mpItem)
|
842 |
temp.append(mpItem)
|
| 852 |
cheapButNotPref.append(temp)
|
843 |
cheapButNotPref.append(temp)
|
| 853 |
continue
|
844 |
continue
|
| 854 |
|
845 |
|
| 855 |
|
- |
|
| 856 |
lowestTp = getOtherTp(flipkartDetails,val,spm,False)
|
846 |
lowestTp = getOtherTp(flipkartDetails,val,spm,False)
|
| 857 |
ourTp = getOurTp(flipkartDetails,val,spm,mpItem)
|
847 |
ourTp = getOurTp(flipkartDetails,val,spm,mpItem)
|
| 858 |
lowestPossibleTp = getLowestPossibleTp(flipkartDetails,val,spm,mpItem)
|
848 |
lowestPossibleTp = getLowestPossibleTp(flipkartDetails,val,spm,mpItem)
|
| 859 |
lowestPossibleSp = getLowestPossibleSp(flipkartDetails,val,spm,mpItem)
|
849 |
lowestPossibleSp = getLowestPossibleSp(flipkartDetails,val,spm,mpItem)
|
| 860 |
prefSellerTp = getOtherTp(flipkartDetails,val,spm,True)
|
850 |
prefSellerTp = getOtherTp(flipkartDetails,val,spm,True)
|
| Line 945... |
Line 935... |
| 945 |
previouslyAutoFav = []
|
935 |
previouslyAutoFav = []
|
| 946 |
nowAutoFav = []
|
936 |
nowAutoFav = []
|
| 947 |
marketplaceItems = session.query(MarketplaceItems).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
|
937 |
marketplaceItems = session.query(MarketplaceItems).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
|
| 948 |
fromDate = datetime.now()-timedelta(days = 3, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
|
938 |
fromDate = datetime.now()-timedelta(days = 3, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
|
| 949 |
toDate = datetime.now()-timedelta(days = 0, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
|
939 |
toDate = datetime.now()-timedelta(days = 0, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
|
| 950 |
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(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
|
940 |
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()
|
| 951 |
toUpdate = [key for key, value in itemSaleMap.items() if value[5] >= 1]
|
941 |
toUpdate = [key for key, value in itemSaleMap.items() if value[5] >= 1]
|
| 952 |
buyBoxLast3days = []
|
942 |
buyBoxLast3days = []
|
| 953 |
for item in items:
|
943 |
for item in items:
|
| 954 |
buyBoxLast3days.append(item[0])
|
944 |
buyBoxLast3days.append(item[0])
|
| 955 |
for marketplaceItem in marketplaceItems:
|
945 |
for marketplaceItem in marketplaceItems:
|
| Line 972... |
Line 962... |
| 972 |
previouslyAutoFav.append(marketplaceItem.itemId)
|
962 |
previouslyAutoFav.append(marketplaceItem.itemId)
|
| 973 |
marketplaceItem.autoFavourite = toMark
|
963 |
marketplaceItem.autoFavourite = toMark
|
| 974 |
session.commit()
|
964 |
session.commit()
|
| 975 |
return previouslyAutoFav, nowAutoFav
|
965 |
return previouslyAutoFav, nowAutoFav
|
| 976 |
|
966 |
|
| 977 |
def write_report(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, cheapButNotPref, prefButNotCheap, previousAutoFav, nowAutoFav,timestamp,runType):
|
967 |
def write_report(previousAutoFav, nowAutoFav,timestamp, runType):
|
| 978 |
wbk = xlwt.Workbook()
|
968 |
wbk = xlwt.Workbook()
|
| 979 |
sheet = wbk.add_sheet('Can\'t Compete')
|
969 |
sheet = wbk.add_sheet('Can\'t Compete')
|
| 980 |
xstr = lambda s: s or ""
|
970 |
xstr = lambda s: s or ""
|
| 981 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
971 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 982 |
|
972 |
|
| Line 1019... |
Line 1009... |
| 1019 |
sheet.write(0, 32, "Target TP", heading_xf)
|
1009 |
sheet.write(0, 32, "Target TP", heading_xf)
|
| 1020 |
sheet.write(0, 33, "Target NLC", heading_xf)
|
1010 |
sheet.write(0, 33, "Target NLC", heading_xf)
|
| 1021 |
sheet.write(0, 34, "Sales Potential", heading_xf)
|
1011 |
sheet.write(0, 34, "Sales Potential", heading_xf)
|
| 1022 |
sheet.write(0, 35, "Total Seller", heading_xf)
|
1012 |
sheet.write(0, 35, "Total Seller", heading_xf)
|
| 1023 |
sheet_iterator = 1
|
1013 |
sheet_iterator = 1
|
| - |
|
1014 |
canCompeteItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
| - |
|
1015 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
| - |
|
1016 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
| - |
|
1017 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| - |
|
1018 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
|
| 1024 |
for item in cantCompete:
|
1019 |
for item in canCompeteItems:
|
| 1025 |
flipkartDetails = item[0]
|
1020 |
mpHistory = item[0]
|
| 1026 |
flipkartItemInfo = item[1]
|
1021 |
flipkartItem = item[1]
|
| 1027 |
flipkartPricing = item[2]
|
1022 |
mpItem = item[2]
|
| 1028 |
mpItem = item[3]
|
1023 |
catItem = item[3]
|
| 1029 |
sheet.write(sheet_iterator,0,flipkartItemInfo.item_id)
|
1024 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
| 1030 |
sheet.write(sheet_iterator,1,flipkartItemInfo.parent_category_name)
|
1025 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
| 1031 |
sheet.write(sheet_iterator,2,flipkartItemInfo.product_group)
|
1026 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
| 1032 |
sheet.write(sheet_iterator,3,flipkartItemInfo.fkSerialNumber)
|
1027 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
| 1033 |
sheet.write(sheet_iterator,4,flipkartItemInfo.brand)
|
1028 |
sheet.write(sheet_iterator,4,catItem.brand)
|
| 1034 |
sheet.write(sheet_iterator,5,xstr(flipkartItemInfo.brand)+" "+xstr(flipkartItemInfo.model_name)+" "+xstr(flipkartItemInfo.model_number)+" "+xstr(flipkartItemInfo.color))
|
1029 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
| 1035 |
sheet.write(sheet_iterator,6,flipkartItemInfo.weight)
|
1030 |
sheet.write(sheet_iterator,6,catItem.weight)
|
| 1036 |
sheet.write(sheet_iterator,7,flipkartItemInfo.courierCost)
|
1031 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
| 1037 |
sheet.write(sheet_iterator,8,flipkartItemInfo.risky)
|
1032 |
sheet.write(sheet_iterator,8,catItem.risky)
|
| 1038 |
sheet.write(sheet_iterator,9,flipkartDetails.ourScore)
|
1033 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
| 1039 |
ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
1034 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
| 1040 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
1035 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
| 1041 |
sheet.write(sheet_iterator,10,ourShippingTime)
|
1036 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
| 1042 |
sheet.write(sheet_iterator,11,flipkartDetails.rank)
|
1037 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
| 1043 |
sheet.write(sheet_iterator,12,flipkartPricing.ourSp)
|
1038 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
| 1044 |
sheet.write(sheet_iterator,13,flipkartPricing.ourTp)
|
1039 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
| 1045 |
sheet.write(sheet_iterator,14,flipkartDetails.lowestSellerName)
|
1040 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
| 1046 |
sheet.write(sheet_iterator,15,flipkartDetails.lowestSellerScore)
|
1041 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
| 1047 |
lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
1042 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
| 1048 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
1043 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
| 1049 |
sheet.write(sheet_iterator,16,lowestSellerShippingTime)
|
1044 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
| 1050 |
sheet.write(sheet_iterator,17,flipkartDetails.lowestSellerSp)
|
1045 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
| 1051 |
sheet.write(sheet_iterator,18,flipkartPricing.lowestTp)
|
1046 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
| 1052 |
sheet.write(sheet_iterator,19,flipkartDetails.prefSellerName)
|
1047 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerName)
|
| 1053 |
sheet.write(sheet_iterator,20,flipkartDetails.prefSellerScore)
|
1048 |
sheet.write(sheet_iterator,20,mpHistory.prefferedSellerRating)
|
| 1054 |
prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
1049 |
# prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
| 1055 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
1050 |
# else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
| 1056 |
sheet.write(sheet_iterator,21,prefferedSellerShippingTime)
|
1051 |
sheet.write(sheet_iterator,21,mpHistory.prefferedSellerShippingTime)
|
| 1057 |
sheet.write(sheet_iterator,22,flipkartDetails.prefSellerSp)
|
1052 |
sheet.write(sheet_iterator,22,mpHistory.prefferedSellerSellingPrice)
|
| 1058 |
sheet.write(sheet_iterator,23,flipkartPricing.prefSellerTp)
|
1053 |
sheet.write(sheet_iterator,23,mpHistory.prefferedSellerTp)
|
| 1059 |
sheet.write(sheet_iterator,24,flipkartItemInfo.ourFlipkartInventory)
|
1054 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
| 1060 |
if (not inventoryMap.has_key(flipkartItemInfo.item_id)):
|
1055 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 1061 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
1056 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
| 1062 |
else:
|
1057 |
else:
|
| 1063 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(flipkartItemInfo.item_id)))
|
1058 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
| 1064 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(flipkartItemInfo.item_id))[1]))
|
1059 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
| 1065 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(flipkartItemInfo.item_id))[3])
|
1060 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
| 1066 |
sheet.write(sheet_iterator, 28, flipkartItemInfo.nlc)
|
1061 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
| 1067 |
sheet.write(sheet_iterator, 29, flipkartPricing.lowestPossibleSp)
|
1062 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
| 1068 |
sheet.write(sheet_iterator, 30, flipkartPricing.lowestPossibleTp)
|
1063 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
| 1069 |
proposed_sp = flipkartDetails.lowestSellerSp - max(10, flipkartDetails.lowestSellerSp*0.001)
|
1064 |
proposed_sp = mpHistory.lowestSellingPrice - max(10, mpHistory.lowestSellingPrice*0.001)
|
| 1070 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
1065 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 1071 |
target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
|
1066 |
target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
|
| 1072 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
1067 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
| 1073 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
1068 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
| 1074 |
sheet.write(sheet_iterator, 33, target_nlc)
|
1069 |
sheet.write(sheet_iterator, 33, target_nlc)
|
| 1075 |
sheet.write(sheet_iterator, 34, getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
1070 |
sheet.write(sheet_iterator, 34, getSalesPotential(mpHistory.lowestSellingPrice,mpHistory.ourNlc))
|
| 1076 |
sheet.write(sheet_iterator, 35, flipkartDetails.totalAvailableSeller)
|
1071 |
sheet.write(sheet_iterator, 35, mpHistory.totalSeller)
|
| 1077 |
sheet_iterator+=1
|
1072 |
sheet_iterator+=1
|
| - |
|
1073 |
|
| - |
|
1074 |
canCompeteItems[:] = []
|
| - |
|
1075 |
|
| 1078 |
|
1076 |
|
| 1079 |
sheet = wbk.add_sheet('Pref and Cheap')
|
1077 |
sheet = wbk.add_sheet('Pref and Cheap')
|
| 1080 |
|
1078 |
|
| 1081 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1079 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1082 |
|
1080 |
|
| Line 1125... |
Line 1123... |
| 1125 |
sheet.write(0, 31, "Target SP", heading_xf)
|
1123 |
sheet.write(0, 31, "Target SP", heading_xf)
|
| 1126 |
sheet.write(0, 32, "Target TP", heading_xf)
|
1124 |
sheet.write(0, 32, "Target TP", heading_xf)
|
| 1127 |
sheet.write(0, 33, "Margin Increased Potential", heading_xf)
|
1125 |
sheet.write(0, 33, "Margin Increased Potential", heading_xf)
|
| 1128 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
1126 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
| 1129 |
sheet_iterator = 1
|
1127 |
sheet_iterator = 1
|
| - |
|
1128 |
|
| - |
|
1129 |
buyBoxItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
| - |
|
1130 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
| - |
|
1131 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
| - |
|
1132 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| - |
|
1133 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
|
| - |
|
1134 |
|
| - |
|
1135 |
|
| 1130 |
for item in buyBoxItems:
|
1136 |
for item in buyBoxItems:
|
| 1131 |
flipkartDetails = item[0]
|
1137 |
mpHistory = item[0]
|
| 1132 |
flipkartItemInfo = item[1]
|
1138 |
flipkartItem = item[1]
|
| 1133 |
flipkartPricing = item[2]
|
1139 |
mpItem = item[2]
|
| 1134 |
mpItem = item[3]
|
1140 |
catItem = item[3]
|
| 1135 |
sheet.write(sheet_iterator,0,flipkartItemInfo.item_id)
|
1141 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
| 1136 |
sheet.write(sheet_iterator,1,flipkartItemInfo.parent_category_name)
|
1142 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
| 1137 |
sheet.write(sheet_iterator,2,flipkartItemInfo.product_group)
|
1143 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
| 1138 |
sheet.write(sheet_iterator,3,flipkartItemInfo.fkSerialNumber)
|
1144 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
| 1139 |
sheet.write(sheet_iterator,4,flipkartItemInfo.brand)
|
1145 |
sheet.write(sheet_iterator,4,catItem.brand)
|
| 1140 |
sheet.write(sheet_iterator,5,xstr(flipkartItemInfo.brand)+" "+xstr(flipkartItemInfo.model_name)+" "+xstr(flipkartItemInfo.model_number)+" "+xstr(flipkartItemInfo.color))
|
1146 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
| 1141 |
sheet.write(sheet_iterator,6,flipkartItemInfo.weight)
|
1147 |
sheet.write(sheet_iterator,6,catItem.weight)
|
| 1142 |
sheet.write(sheet_iterator,7,flipkartItemInfo.courierCost)
|
1148 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
| 1143 |
sheet.write(sheet_iterator,8,flipkartItemInfo.risky)
|
1149 |
sheet.write(sheet_iterator,8,catItem.risky)
|
| 1144 |
sheet.write(sheet_iterator,9,flipkartDetails.ourScore)
|
1150 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
| 1145 |
ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
1151 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
| 1146 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
1152 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
| 1147 |
sheet.write(sheet_iterator,10,ourShippingTime)
|
1153 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
| 1148 |
sheet.write(sheet_iterator,11,flipkartDetails.rank)
|
1154 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
| 1149 |
sheet.write(sheet_iterator,12,flipkartPricing.ourSp)
|
1155 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
| 1150 |
sheet.write(sheet_iterator,13,flipkartPricing.ourTp)
|
1156 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
| 1151 |
sheet.write(sheet_iterator,14,flipkartDetails.lowestSellerName)
|
1157 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
| 1152 |
sheet.write(sheet_iterator,15,flipkartDetails.lowestSellerScore)
|
1158 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
| 1153 |
lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
1159 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
| 1154 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
1160 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
| 1155 |
sheet.write(sheet_iterator,16,lowestSellerShippingTime)
|
1161 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
| 1156 |
sheet.write(sheet_iterator,17,flipkartDetails.lowestSellerSp)
|
1162 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
| 1157 |
sheet.write(sheet_iterator,18,flipkartPricing.lowestTp)
|
1163 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
| 1158 |
sheet.write(sheet_iterator,19,flipkartDetails.secondLowestSellerName)
|
1164 |
sheet.write(sheet_iterator,19,mpHistory.secondLowestSellerName)
|
| 1159 |
sheet.write(sheet_iterator,20,flipkartDetails.secondLowestSellerScore)
|
1165 |
sheet.write(sheet_iterator,20,mpHistory.secondLowestSellerRating)
|
| 1160 |
secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
|
1166 |
# secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
|
| 1161 |
else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
|
1167 |
# else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
|
| 1162 |
sheet.write(sheet_iterator,21,secondLowestSellerShippingTime)
|
1168 |
sheet.write(sheet_iterator,21,mpHistory.secondLowestSellerShippingTime)
|
| 1163 |
sheet.write(sheet_iterator,22,flipkartDetails.secondLowestSellerSp)
|
1169 |
sheet.write(sheet_iterator,22,mpHistory.secondLowestSellingPrice)
|
| 1164 |
sheet.write(sheet_iterator,23,flipkartPricing.secondLowestSellerTp)
|
1170 |
sheet.write(sheet_iterator,23,mpHistory.secondLowestTp)
|
| 1165 |
sheet.write(sheet_iterator,24,flipkartItemInfo.ourFlipkartInventory)
|
1171 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
| 1166 |
if (not inventoryMap.has_key(flipkartItemInfo.item_id)):
|
1172 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 1167 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
1173 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
| 1168 |
else:
|
1174 |
else:
|
| 1169 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(flipkartItemInfo.item_id)))
|
1175 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
| 1170 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(flipkartItemInfo.item_id))[1]))
|
1176 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
| 1171 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(flipkartItemInfo.item_id))[3])
|
1177 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
| 1172 |
sheet.write(sheet_iterator, 28, flipkartItemInfo.nlc)
|
1178 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
| 1173 |
sheet.write(sheet_iterator, 29, flipkartPricing.lowestPossibleSp)
|
1179 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
| 1174 |
sheet.write(sheet_iterator, 30, flipkartPricing.lowestPossibleTp)
|
1180 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
| 1175 |
proposed_sp = max(flipkartDetails.secondLowestSellerSp - max((20, flipkartDetails.secondLowestSellerSp*0.002)), flipkartPricing.lowestPossibleSp)
|
1181 |
proposed_sp = max(mpHistory.secondLowestSellingPrice - max((20, mpHistory.secondLowestSellingPrice*0.002)), mpHistory.lowestPossibleSp)
|
| 1176 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
1182 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 1177 |
target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
|
1183 |
target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
|
| 1178 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
1184 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
| 1179 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
1185 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
| 1180 |
sheet.write(sheet_iterator, 33, proposed_tp - flipkartPricing.ourTp)
|
1186 |
sheet.write(sheet_iterator, 33, proposed_tp -mpHistory.ourTp )
|
| 1181 |
sheet.write(sheet_iterator, 34, flipkartDetails.totalAvailableSeller)
|
1187 |
sheet.write(sheet_iterator, 34, mpHistory.totalSeller)
|
| 1182 |
sheet_iterator+=1
|
1188 |
sheet_iterator+=1
|
| 1183 |
|
1189 |
|
| - |
|
1190 |
buyBoxItems[:] = []
|
| 1184 |
|
1191 |
|
| 1185 |
sheet = wbk.add_sheet('PREF BUT NOT CHEAP')
|
1192 |
sheet = wbk.add_sheet('Pref Not Cheap')
|
| 1186 |
|
1193 |
|
| 1187 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1194 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1188 |
|
1195 |
|
| 1189 |
excel_integer_format = '0'
|
1196 |
excel_integer_format = '0'
|
| 1190 |
integer_style = xlwt.XFStyle()
|
1197 |
integer_style = xlwt.XFStyle()
|
| Line 1204... |
Line 1211... |
| 1204 |
sheet.write(0, 4, "Brand", heading_xf)
|
1211 |
sheet.write(0, 4, "Brand", heading_xf)
|
| 1205 |
sheet.write(0, 5, "Product Name", heading_xf)
|
1212 |
sheet.write(0, 5, "Product Name", heading_xf)
|
| 1206 |
sheet.write(0, 6, "Weight", heading_xf)
|
1213 |
sheet.write(0, 6, "Weight", heading_xf)
|
| 1207 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
1214 |
sheet.write(0, 7, "Courier Cost", heading_xf)
|
| 1208 |
sheet.write(0, 8, "Risky", heading_xf)
|
1215 |
sheet.write(0, 8, "Risky", heading_xf)
|
| 1209 |
sheet.write(0, 9, "Our Rank", heading_xf)
|
1216 |
sheet.write(0, 9, "Our Rating", heading_xf)
|
| 1210 |
sheet.write(0, 10, "Lowest Seller", heading_xf)
|
1217 |
sheet.write(0, 10, "Our Shipping Time", heading_xf)
|
| 1211 |
sheet.write(0, 11, "Our Rating", heading_xf)
|
1218 |
sheet.write(0, 11, "Our Rank", heading_xf)
|
| 1212 |
sheet.write(0, 12, "Our Shipping Time", heading_xf)
|
1219 |
sheet.write(0, 12, "Our SP", heading_xf)
|
| 1213 |
sheet.write(0, 13, "Our SP", heading_xf)
|
1220 |
sheet.write(0, 13, "Our TP", heading_xf)
|
| 1214 |
sheet.write(0, 14, "Our TP", heading_xf)
|
1221 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
| - |
|
1222 |
sheet.write(0, 15, "Lowest Seller Rating", heading_xf)
|
| - |
|
1223 |
sheet.write(0, 16, "Lowest Seller Shipping Time", heading_xf)
|
| - |
|
1224 |
sheet.write(0, 17, "Lowest Seller SP", heading_xf)
|
| - |
|
1225 |
sheet.write(0, 18, "Lowest Seller TP", heading_xf)
|
| 1215 |
sheet.write(0, 15, "Preffered Seller", heading_xf)
|
1226 |
sheet.write(0, 19, "Preffered Seller", heading_xf)
|
| 1216 |
sheet.write(0, 16, "Preffered Seller Rating", heading_xf)
|
1227 |
sheet.write(0, 20, "Preffered Seller Rating", heading_xf)
|
| 1217 |
sheet.write(0, 17, "Preffered Seller Shipping Time", heading_xf)
|
1228 |
sheet.write(0, 21, "Preffered Seller Shipping Time", heading_xf)
|
| 1218 |
sheet.write(0, 18, "Preffered Seller SP", heading_xf)
|
1229 |
sheet.write(0, 22, "Preffered Seller SP", heading_xf)
|
| 1219 |
sheet.write(0, 19, "Preffered Seller TP", heading_xf)
|
1230 |
sheet.write(0, 23, "Preffered Seller TP", heading_xf)
|
| 1220 |
sheet.write(0, 20, "Our Flipkart Inventory", heading_xf)
|
1231 |
sheet.write(0, 24, "Our Flipkart Inventory", heading_xf)
|
| 1221 |
sheet.write(0, 21, "Our Net Availability",heading_xf)
|
1232 |
sheet.write(0, 25, "Our Net Availability",heading_xf)
|
| 1222 |
sheet.write(0, 22, "Last Five Day Sale", heading_xf)
|
1233 |
sheet.write(0, 26, "Last Five Day Sale", heading_xf)
|
| 1223 |
sheet.write(0, 23, "Average Sale", heading_xf)
|
1234 |
sheet.write(0, 27, "Average Sale", heading_xf)
|
| 1224 |
sheet.write(0, 24, "Our NLC", heading_xf)
|
1235 |
sheet.write(0, 28, "Our NLC", heading_xf)
|
| 1225 |
sheet.write(0, 25, "Lowest Possible SP", heading_xf)
|
1236 |
sheet.write(0, 29, "Lowest Possible SP", heading_xf)
|
| 1226 |
sheet.write(0, 26, "Lowest Possible TP", heading_xf)
|
1237 |
sheet.write(0, 30, "Lowest Possible TP", heading_xf)
|
| - |
|
1238 |
sheet.write(0, 31, "Target SP", heading_xf)
|
| - |
|
1239 |
sheet.write(0, 32, "Target TP", heading_xf)
|
| 1227 |
sheet.write(0, 27, "Total Seller", heading_xf)
|
1240 |
sheet.write(0, 33, "Total Seller", heading_xf)
|
| 1228 |
sheet_iterator = 1
|
1241 |
sheet_iterator = 1
|
| - |
|
1242 |
|
| - |
|
1243 |
prefNotCheapItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
| - |
|
1244 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
| - |
|
1245 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
| - |
|
1246 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| - |
|
1247 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP).all()
|
| - |
|
1248 |
|
| - |
|
1249 |
|
| 1229 |
for item in prefButNotCheap:
|
1250 |
for item in prefNotCheapItems:
|
| 1230 |
flipkartDetails = item[0]
|
1251 |
mpHistory = item[0]
|
| 1231 |
flipkartItemInfo = item[1]
|
1252 |
flipkartItem = item[1]
|
| 1232 |
flipkartPricing = item[2]
|
1253 |
mpItem = item[2]
|
| 1233 |
mpItem = item[3]
|
1254 |
catItem = item[3]
|
| 1234 |
sheet.write(sheet_iterator,0,flipkartItemInfo.item_id)
|
1255 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
| 1235 |
sheet.write(sheet_iterator,1,flipkartItemInfo.parent_category_name)
|
1256 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
| 1236 |
sheet.write(sheet_iterator,2,flipkartItemInfo.product_group)
|
1257 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
| 1237 |
sheet.write(sheet_iterator,3,flipkartItemInfo.fkSerialNumber)
|
1258 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
| 1238 |
sheet.write(sheet_iterator,4,flipkartItemInfo.brand)
|
1259 |
sheet.write(sheet_iterator,4,catItem.brand)
|
| 1239 |
sheet.write(sheet_iterator,5,xstr(flipkartItemInfo.brand)+" "+xstr(flipkartItemInfo.model_name)+" "+xstr(flipkartItemInfo.model_number)+" "+xstr(flipkartItemInfo.color))
|
1260 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
| 1240 |
sheet.write(sheet_iterator,6,flipkartItemInfo.weight)
|
1261 |
sheet.write(sheet_iterator,6,catItem.weight)
|
| 1241 |
sheet.write(sheet_iterator,7,flipkartItemInfo.courierCost)
|
1262 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
| 1242 |
sheet.write(sheet_iterator,8,flipkartItemInfo.risky)
|
1263 |
sheet.write(sheet_iterator,8,catItem.risky)
|
| - |
|
1264 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
| - |
|
1265 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
| - |
|
1266 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
| - |
|
1267 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
| 1243 |
sheet.write(sheet_iterator,9,flipkartDetails.rank)
|
1268 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
| - |
|
1269 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
| - |
|
1270 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
| 1244 |
sheet.write(sheet_iterator,10,flipkartDetails.lowestSellerName)
|
1271 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
| 1245 |
sheet.write(sheet_iterator,11,flipkartDetails.ourScore)
|
1272 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
| 1246 |
ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
1273 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
| 1247 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
1274 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
| 1248 |
sheet.write(sheet_iterator,12,ourShippingTime)
|
1275 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
| 1249 |
sheet.write(sheet_iterator,13,flipkartPricing.ourSp)
|
1276 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
| 1250 |
sheet.write(sheet_iterator,14,flipkartPricing.ourTp)
|
1277 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
| 1251 |
sheet.write(sheet_iterator,15,flipkartDetails.prefSellerName)
|
1278 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerName)
|
| 1252 |
sheet.write(sheet_iterator,16,flipkartDetails.prefSellerScore)
|
1279 |
sheet.write(sheet_iterator,20,mpHistory.prefferedSellerRating)
|
| 1253 |
prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
1280 |
# secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
|
| 1254 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
1281 |
# else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
|
| 1255 |
sheet.write(sheet_iterator,17,prefferedSellerShippingTime)
|
1282 |
sheet.write(sheet_iterator,21,mpHistory.prefferedSellerShippingTime)
|
| 1256 |
sheet.write(sheet_iterator,18,flipkartDetails.prefSellerSp)
|
1283 |
sheet.write(sheet_iterator,22,mpHistory.prefferedSellerSellingPrice)
|
| 1257 |
sheet.write(sheet_iterator,19,flipkartPricing.prefSellerTp)
|
1284 |
sheet.write(sheet_iterator,23,mpHistory.prefferedSellerTp)
|
| 1258 |
sheet.write(sheet_iterator,20,flipkartItemInfo.ourFlipkartInventory)
|
1285 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
| 1259 |
if (not inventoryMap.has_key(flipkartItemInfo.item_id)):
|
1286 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 1260 |
sheet.write(sheet_iterator, 21, 'Info not available')
|
1287 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
| 1261 |
else:
|
1288 |
else:
|
| 1262 |
sheet.write(sheet_iterator, 21, getNetAvailability(inventoryMap.get(flipkartItemInfo.item_id)))
|
1289 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
| 1263 |
sheet.write(sheet_iterator, 22, getOosString((itemSaleMap.get(flipkartItemInfo.item_id))[1]))
|
1290 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
| 1264 |
sheet.write(sheet_iterator, 23, (itemSaleMap.get(flipkartItemInfo.item_id))[3])
|
1291 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
| 1265 |
sheet.write(sheet_iterator, 24, flipkartItemInfo.nlc)
|
1292 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
| 1266 |
sheet.write(sheet_iterator, 25, flipkartPricing.lowestPossibleSp)
|
1293 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
| 1267 |
sheet.write(sheet_iterator, 26, flipkartPricing.lowestPossibleTp)
|
1294 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
| 1268 |
#proposed_sp = max(flipkartDetails.secondLowestSellerSp - max((20, flipkartDetails.secondLowestSellerSp*0.002)), flipkartPricing.lowestPossibleSp)
|
1295 |
proposed_sp = max(mpHistory.lowestSellingPrice - max((20, mpHistory.lowestSellingPrice*0.002)), mpHistory.lowestPossibleSp)
|
| 1269 |
#proposed_tp = getTargetTp(proposed_sp,mpItem)
|
1296 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 1270 |
#target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
|
1297 |
target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
|
| - |
|
1298 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
| - |
|
1299 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
| 1271 |
sheet.write(sheet_iterator, 27, flipkartDetails.totalAvailableSeller)
|
1300 |
sheet.write(sheet_iterator, 33, mpHistory.totalSeller)
|
| 1272 |
sheet_iterator+=1
|
1301 |
sheet_iterator+=1
|
| - |
|
1302 |
|
| - |
|
1303 |
prefNotCheapItems[:] = []
|
| 1273 |
|
1304 |
|
| 1274 |
sheet = wbk.add_sheet('Cheap But Not Pref')
|
1305 |
sheet = wbk.add_sheet('Cheap But Not Pref')
|
| 1275 |
|
1306 |
|
| 1276 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1307 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1277 |
|
1308 |
|
| Line 1313... |
Line 1344... |
| 1313 |
sheet.write(0, 24, "Our NLC", heading_xf)
|
1344 |
sheet.write(0, 24, "Our NLC", heading_xf)
|
| 1314 |
sheet.write(0, 25, "Lowest Possible SP", heading_xf)
|
1345 |
sheet.write(0, 25, "Lowest Possible SP", heading_xf)
|
| 1315 |
sheet.write(0, 26, "Lowest Possible TP", heading_xf)
|
1346 |
sheet.write(0, 26, "Lowest Possible TP", heading_xf)
|
| 1316 |
sheet.write(0, 27, "Total Seller", heading_xf)
|
1347 |
sheet.write(0, 27, "Total Seller", heading_xf)
|
| 1317 |
sheet_iterator = 1
|
1348 |
sheet_iterator = 1
|
| - |
|
1349 |
|
| - |
|
1350 |
cheapNotPrefferedItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
| - |
|
1351 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
| - |
|
1352 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
| - |
|
1353 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| - |
|
1354 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CHEAP_BUT_NOT_PREF).all()
|
| - |
|
1355 |
|
| 1318 |
for item in cheapButNotPref:
|
1356 |
for item in cheapNotPrefferedItems:
|
| 1319 |
flipkartDetails = item[0]
|
1357 |
mpHistory = item[0]
|
| 1320 |
flipkartItemInfo = item[1]
|
1358 |
flipkartItem = item[1]
|
| 1321 |
flipkartPricing = item[2]
|
1359 |
mpItem = item[2]
|
| 1322 |
mpItem = item[3]
|
1360 |
catItem = item[3]
|
| 1323 |
sheet.write(sheet_iterator,0,flipkartItemInfo.item_id)
|
1361 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
| 1324 |
sheet.write(sheet_iterator,1,flipkartItemInfo.parent_category_name)
|
1362 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
| 1325 |
sheet.write(sheet_iterator,2,flipkartItemInfo.product_group)
|
1363 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
| 1326 |
sheet.write(sheet_iterator,3,flipkartItemInfo.fkSerialNumber)
|
1364 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
| 1327 |
sheet.write(sheet_iterator,4,flipkartItemInfo.brand)
|
1365 |
sheet.write(sheet_iterator,4,catItem.brand)
|
| 1328 |
sheet.write(sheet_iterator,5,xstr(flipkartItemInfo.brand)+" "+xstr(flipkartItemInfo.model_name)+" "+xstr(flipkartItemInfo.model_number)+" "+xstr(flipkartItemInfo.color))
|
1366 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
| 1329 |
sheet.write(sheet_iterator,6,flipkartItemInfo.weight)
|
1367 |
sheet.write(sheet_iterator,6,catItem.weight)
|
| 1330 |
sheet.write(sheet_iterator,7,flipkartItemInfo.courierCost)
|
1368 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
| 1331 |
sheet.write(sheet_iterator,8,flipkartItemInfo.risky)
|
1369 |
sheet.write(sheet_iterator,8,catItem.risky)
|
| 1332 |
sheet.write(sheet_iterator,9,flipkartDetails.rank)
|
1370 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
| 1333 |
sheet.write(sheet_iterator,10,flipkartDetails.lowestSellerName)
|
1371 |
sheet.write(sheet_iterator,10,mpHistory.lowestSellerName)
|
| 1334 |
sheet.write(sheet_iterator,11,flipkartDetails.ourScore)
|
1372 |
sheet.write(sheet_iterator,11,mpHistory.ourRating)
|
| 1335 |
ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
1373 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
| 1336 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
1374 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
| 1337 |
sheet.write(sheet_iterator,12,ourShippingTime)
|
1375 |
sheet.write(sheet_iterator,12,mpHistory.lowestSellerShippingTime)
|
| 1338 |
sheet.write(sheet_iterator,13,flipkartPricing.ourSp)
|
1376 |
sheet.write(sheet_iterator,13,mpHistory.lowestSellingPrice)
|
| 1339 |
sheet.write(sheet_iterator,14,flipkartPricing.ourTp)
|
1377 |
sheet.write(sheet_iterator,14,mpHistory.lowestTp)
|
| 1340 |
sheet.write(sheet_iterator,15,flipkartDetails.prefSellerName)
|
1378 |
sheet.write(sheet_iterator,15,mpHistory.prefferedSellerName)
|
| 1341 |
sheet.write(sheet_iterator,16,flipkartDetails.prefSellerScore)
|
1379 |
sheet.write(sheet_iterator,16,mpHistory.prefferedSellerRating)
|
| 1342 |
prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
1380 |
# prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
| 1343 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
1381 |
# else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
| 1344 |
sheet.write(sheet_iterator,17,prefferedSellerShippingTime)
|
1382 |
sheet.write(sheet_iterator,17,mpHistory.prefferedSellerShippingTime)
|
| 1345 |
sheet.write(sheet_iterator,18,flipkartDetails.prefSellerSp)
|
1383 |
sheet.write(sheet_iterator,18,mpHistory.prefferedSellerSellingPrice)
|
| 1346 |
sheet.write(sheet_iterator,19,flipkartPricing.prefSellerTp)
|
1384 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerTp)
|
| 1347 |
sheet.write(sheet_iterator,20,flipkartItemInfo.ourFlipkartInventory)
|
1385 |
sheet.write(sheet_iterator,20,mpHistory.ourInventory)
|
| 1348 |
if (not inventoryMap.has_key(flipkartItemInfo.item_id)):
|
1386 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 1349 |
sheet.write(sheet_iterator, 21, 'Info not available')
|
1387 |
sheet.write(sheet_iterator, 21, 'Info not available')
|
| 1350 |
else:
|
1388 |
else:
|
| 1351 |
sheet.write(sheet_iterator, 21, getNetAvailability(inventoryMap.get(flipkartItemInfo.item_id)))
|
1389 |
sheet.write(sheet_iterator, 21, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
| 1352 |
sheet.write(sheet_iterator, 22, getOosString((itemSaleMap.get(flipkartItemInfo.item_id))[1]))
|
1390 |
sheet.write(sheet_iterator, 22, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
| 1353 |
sheet.write(sheet_iterator, 23, (itemSaleMap.get(flipkartItemInfo.item_id))[3])
|
1391 |
sheet.write(sheet_iterator, 23, (itemSaleMap.get(mpHistory.item_id))[3])
|
| 1354 |
sheet.write(sheet_iterator, 24, flipkartItemInfo.nlc)
|
1392 |
sheet.write(sheet_iterator, 24, mpHistory.ourNlc)
|
| 1355 |
sheet.write(sheet_iterator, 25, flipkartPricing.lowestPossibleSp)
|
1393 |
sheet.write(sheet_iterator, 25, mpHistory.lowestPossibleSp)
|
| 1356 |
sheet.write(sheet_iterator, 26, flipkartPricing.lowestPossibleTp)
|
1394 |
sheet.write(sheet_iterator, 26, mpHistory.lowestPossibleTp)
|
| 1357 |
#proposed_sp = max(flipkartDetails.secondLowestSellerSp - max((20, flipkartDetails.secondLowestSellerSp*0.002)), flipkartPricing.lowestPossibleSp)
|
1395 |
#proposed_sp = max(flipkartDetails.secondLowestSellerSp - max((20, flipkartDetails.secondLowestSellerSp*0.002)), flipkartPricing.lowestPossibleSp)
|
| 1358 |
#proposed_tp = getTargetTp(proposed_sp,mpItem)
|
1396 |
#proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 1359 |
#target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
|
1397 |
#target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
|
| 1360 |
sheet.write(sheet_iterator, 27, flipkartDetails.totalAvailableSeller)
|
1398 |
sheet.write(sheet_iterator, 27, mpHistory.totalSeller)
|
| 1361 |
sheet_iterator+=1
|
1399 |
sheet_iterator+=1
|
| - |
|
1400 |
|
| - |
|
1401 |
cheapNotPrefferedItems[:]=[]
|
| 1362 |
|
1402 |
|
| 1363 |
sheet = wbk.add_sheet('Can Compete-With Inventory')
|
1403 |
sheet = wbk.add_sheet('Can Compete-With Inventory')
|
| 1364 |
xstr = lambda s: s or ""
|
1404 |
xstr = lambda s: s or ""
|
| 1365 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1405 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1366 |
|
1406 |
|
| Line 1403... |
Line 1443... |
| 1403 |
sheet.write(0, 32, "Target TP", heading_xf)
|
1443 |
sheet.write(0, 32, "Target TP", heading_xf)
|
| 1404 |
sheet.write(0, 33, "Target NLC", heading_xf)
|
1444 |
sheet.write(0, 33, "Target NLC", heading_xf)
|
| 1405 |
sheet.write(0, 34, "Sales Potential", heading_xf)
|
1445 |
sheet.write(0, 34, "Sales Potential", heading_xf)
|
| 1406 |
sheet.write(0, 35, "Total Seller", heading_xf)
|
1446 |
sheet.write(0, 35, "Total Seller", heading_xf)
|
| 1407 |
sheet_iterator = 1
|
1447 |
sheet_iterator = 1
|
| - |
|
1448 |
|
| - |
|
1449 |
competitiveItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
| - |
|
1450 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
| - |
|
1451 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
| - |
|
1452 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| - |
|
1453 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).all()
|
| - |
|
1454 |
|
| 1408 |
for item in competitive:
|
1455 |
for item in competitiveItems:
|
| 1409 |
flipkartDetails = item[0]
|
1456 |
mpHistory = item[0]
|
| 1410 |
flipkartItemInfo = item[1]
|
1457 |
flipkartItem = item[1]
|
| 1411 |
flipkartPricing = item[2]
|
1458 |
mpItem = item[2]
|
| 1412 |
mpItem = item[3]
|
1459 |
catItem = item[3]
|
| 1413 |
sheet.write(sheet_iterator,0,flipkartItemInfo.item_id)
|
1460 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
| 1414 |
sheet.write(sheet_iterator,1,flipkartItemInfo.parent_category_name)
|
1461 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
| 1415 |
sheet.write(sheet_iterator,2,flipkartItemInfo.product_group)
|
1462 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
| 1416 |
sheet.write(sheet_iterator,3,flipkartItemInfo.fkSerialNumber)
|
1463 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
| 1417 |
sheet.write(sheet_iterator,4,flipkartItemInfo.brand)
|
1464 |
sheet.write(sheet_iterator,4,catItem.brand)
|
| 1418 |
sheet.write(sheet_iterator,5,xstr(flipkartItemInfo.brand)+" "+xstr(flipkartItemInfo.model_name)+" "+xstr(flipkartItemInfo.model_number)+" "+xstr(flipkartItemInfo.color))
|
1465 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
| 1419 |
sheet.write(sheet_iterator,6,flipkartItemInfo.weight)
|
1466 |
sheet.write(sheet_iterator,6,catItem.weight)
|
| 1420 |
sheet.write(sheet_iterator,7,flipkartItemInfo.courierCost)
|
1467 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
| 1421 |
sheet.write(sheet_iterator,8,flipkartItemInfo.risky)
|
1468 |
sheet.write(sheet_iterator,8,catItem.risky)
|
| 1422 |
sheet.write(sheet_iterator,9,flipkartDetails.ourScore)
|
1469 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
| 1423 |
ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
1470 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
| 1424 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
1471 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
| 1425 |
sheet.write(sheet_iterator,10,ourShippingTime)
|
1472 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
| 1426 |
sheet.write(sheet_iterator,11,flipkartDetails.rank)
|
1473 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
| 1427 |
sheet.write(sheet_iterator,12,flipkartPricing.ourSp)
|
1474 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
| 1428 |
sheet.write(sheet_iterator,13,flipkartPricing.ourTp)
|
1475 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
| 1429 |
sheet.write(sheet_iterator,14,flipkartDetails.lowestSellerName)
|
1476 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
| 1430 |
sheet.write(sheet_iterator,15,flipkartDetails.lowestSellerScore)
|
1477 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
| 1431 |
lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
1478 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
| 1432 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
1479 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
| 1433 |
sheet.write(sheet_iterator,16,lowestSellerShippingTime)
|
1480 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
| 1434 |
sheet.write(sheet_iterator,17,flipkartDetails.lowestSellerSp)
|
1481 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
| 1435 |
sheet.write(sheet_iterator,18,flipkartPricing.lowestTp)
|
1482 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
| 1436 |
sheet.write(sheet_iterator,19,flipkartDetails.prefSellerName)
|
1483 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerName)
|
| 1437 |
sheet.write(sheet_iterator,20,flipkartDetails.prefSellerScore)
|
1484 |
sheet.write(sheet_iterator,20,mpHistory.prefferedSellerRating)
|
| 1438 |
prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
1485 |
# prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
| 1439 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
1486 |
# else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
| 1440 |
sheet.write(sheet_iterator,21,prefferedSellerShippingTime)
|
1487 |
sheet.write(sheet_iterator,21,mpHistory.prefferedSellerShippingTime)
|
| 1441 |
sheet.write(sheet_iterator,22,flipkartDetails.prefSellerSp)
|
1488 |
sheet.write(sheet_iterator,22,mpHistory.prefferedSellerSellingPrice)
|
| 1442 |
sheet.write(sheet_iterator,23,flipkartPricing.prefSellerTp)
|
1489 |
sheet.write(sheet_iterator,23,mpHistory.prefferedSellerTp)
|
| 1443 |
sheet.write(sheet_iterator,24,flipkartItemInfo.ourFlipkartInventory)
|
1490 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
| 1444 |
if (not inventoryMap.has_key(flipkartItemInfo.item_id)):
|
1491 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 1445 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
1492 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
| 1446 |
else:
|
1493 |
else:
|
| 1447 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(flipkartItemInfo.item_id)))
|
1494 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
| 1448 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(flipkartItemInfo.item_id))[1]))
|
1495 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
| 1449 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(flipkartItemInfo.item_id))[3])
|
1496 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
| 1450 |
sheet.write(sheet_iterator, 28, flipkartItemInfo.nlc)
|
1497 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
| 1451 |
sheet.write(sheet_iterator, 29, flipkartPricing.lowestPossibleSp)
|
1498 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
| 1452 |
sheet.write(sheet_iterator, 30, flipkartPricing.lowestPossibleTp)
|
1499 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
| 1453 |
proposed_sp = max(flipkartDetails.lowestSellerSp - max((10, flipkartDetails.lowestSellerSp*0.001)), flipkartPricing.lowestPossibleSp)
|
1500 |
proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestSellingPrice)
|
| 1454 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
1501 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 1455 |
target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
|
1502 |
target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
|
| 1456 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
1503 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
| 1457 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
1504 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
| 1458 |
sheet.write(sheet_iterator, 33, target_nlc)
|
1505 |
sheet.write(sheet_iterator, 33, target_nlc)
|
| 1459 |
sheet.write(sheet_iterator, 34, getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
1506 |
sheet.write(sheet_iterator, 34, getSalesPotential(mpHistory.lowestSellingPrice,mpHistory.ourNlc))
|
| 1460 |
sheet.write(sheet_iterator, 35, flipkartDetails.totalAvailableSeller)
|
1507 |
sheet.write(sheet_iterator, 35, mpHistory.totalSeller)
|
| 1461 |
sheet_iterator+=1
|
1508 |
sheet_iterator+=1
|
| - |
|
1509 |
|
| - |
|
1510 |
competitiveItems[:]=[]
|
| 1462 |
|
1511 |
|
| 1463 |
sheet = wbk.add_sheet('Negative Margin')
|
1512 |
sheet = wbk.add_sheet('Negative Margin')
|
| 1464 |
xstr = lambda s: s or ""
|
1513 |
xstr = lambda s: s or ""
|
| 1465 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1514 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1466 |
|
1515 |
|
| 1467 |
excel_integer_format = '0'
|
1516 |
excel_integer_format = '0'
|
| Line 1500... |
Line 1549... |
| 1500 |
sheet.write(0, 29, "Lowest Possible SP", heading_xf)
|
1549 |
sheet.write(0, 29, "Lowest Possible SP", heading_xf)
|
| 1501 |
sheet.write(0, 30, "Lowest Possible TP", heading_xf)
|
1550 |
sheet.write(0, 30, "Lowest Possible TP", heading_xf)
|
| 1502 |
sheet.write(0, 31, "Margin", heading_xf)
|
1551 |
sheet.write(0, 31, "Margin", heading_xf)
|
| 1503 |
sheet.write(0, 32, "Total Seller", heading_xf)
|
1552 |
sheet.write(0, 32, "Total Seller", heading_xf)
|
| 1504 |
sheet_iterator = 1
|
1553 |
sheet_iterator = 1
|
| - |
|
1554 |
|
| - |
|
1555 |
negativeMargin = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
| - |
|
1556 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
| - |
|
1557 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
| - |
|
1558 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| - |
|
1559 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).all()
|
| - |
|
1560 |
|
| 1505 |
for item in negativeMargin:
|
1561 |
for item in negativeMargin:
|
| 1506 |
flipkartDetails = item[0]
|
1562 |
mpHistory = item[0]
|
| 1507 |
flipkartItemInfo = item[1]
|
1563 |
flipkartItem = item[1]
|
| 1508 |
flipkartPricing = item[2]
|
1564 |
mpItem = item[2]
|
| - |
|
1565 |
catItem = item[3]
|
| 1509 |
sheet.write(sheet_iterator,0,flipkartItemInfo.item_id)
|
1566 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
| 1510 |
sheet.write(sheet_iterator,1,flipkartItemInfo.parent_category_name)
|
1567 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
| 1511 |
sheet.write(sheet_iterator,2,flipkartItemInfo.product_group)
|
1568 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
| 1512 |
sheet.write(sheet_iterator,3,flipkartItemInfo.fkSerialNumber)
|
1569 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
| 1513 |
sheet.write(sheet_iterator,4,flipkartItemInfo.brand)
|
1570 |
sheet.write(sheet_iterator,4,catItem.brand)
|
| 1514 |
sheet.write(sheet_iterator,5,xstr(flipkartItemInfo.brand)+" "+xstr(flipkartItemInfo.model_name)+" "+xstr(flipkartItemInfo.model_number)+" "+xstr(flipkartItemInfo.color))
|
1571 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
| 1515 |
sheet.write(sheet_iterator,6,flipkartItemInfo.weight)
|
1572 |
sheet.write(sheet_iterator,6,catItem.weight)
|
| 1516 |
sheet.write(sheet_iterator,7,flipkartItemInfo.courierCost)
|
1573 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
| 1517 |
sheet.write(sheet_iterator,8,flipkartItemInfo.risky)
|
1574 |
sheet.write(sheet_iterator,8,catItem.risky)
|
| 1518 |
sheet.write(sheet_iterator,9,flipkartDetails.ourScore)
|
1575 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
| 1519 |
ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
1576 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
| 1520 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
1577 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
| 1521 |
sheet.write(sheet_iterator,10,ourShippingTime)
|
1578 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
| 1522 |
sheet.write(sheet_iterator,11,flipkartDetails.rank)
|
1579 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
| 1523 |
sheet.write(sheet_iterator,12,flipkartPricing.ourSp)
|
1580 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
| 1524 |
sheet.write(sheet_iterator,13,flipkartPricing.ourTp)
|
1581 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
| 1525 |
sheet.write(sheet_iterator,14,flipkartDetails.lowestSellerName)
|
1582 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
| 1526 |
sheet.write(sheet_iterator,15,flipkartDetails.lowestSellerScore)
|
1583 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
| 1527 |
lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
1584 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
| 1528 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
1585 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
| 1529 |
sheet.write(sheet_iterator,16,lowestSellerShippingTime)
|
1586 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
| 1530 |
sheet.write(sheet_iterator,17,flipkartDetails.lowestSellerSp)
|
1587 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
| 1531 |
sheet.write(sheet_iterator,18,flipkartPricing.lowestTp)
|
1588 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
| 1532 |
sheet.write(sheet_iterator,19,flipkartDetails.prefSellerName)
|
1589 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerName)
|
| 1533 |
sheet.write(sheet_iterator,20,flipkartDetails.prefSellerScore)
|
1590 |
sheet.write(sheet_iterator,20,mpHistory.prefferedSellerRating)
|
| 1534 |
prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
1591 |
# prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
| 1535 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
1592 |
# else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
| 1536 |
sheet.write(sheet_iterator,21,prefferedSellerShippingTime)
|
1593 |
sheet.write(sheet_iterator,21,mpHistory.prefferedSellerShippingTime)
|
| 1537 |
sheet.write(sheet_iterator,22,flipkartDetails.prefSellerSp)
|
1594 |
sheet.write(sheet_iterator,22,mpHistory.prefferedSellerSellingPrice)
|
| 1538 |
sheet.write(sheet_iterator,23,flipkartPricing.prefSellerTp)
|
1595 |
sheet.write(sheet_iterator,23,mpHistory.prefferedSellerTp)
|
| 1539 |
sheet.write(sheet_iterator,24,flipkartItemInfo.ourFlipkartInventory)
|
1596 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
| 1540 |
if (not inventoryMap.has_key(flipkartItemInfo.item_id)):
|
1597 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 1541 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
1598 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
| 1542 |
else:
|
1599 |
else:
|
| 1543 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(flipkartItemInfo.item_id)))
|
1600 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
| 1544 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(flipkartItemInfo.item_id))[1]))
|
1601 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
| 1545 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(flipkartItemInfo.item_id))[3])
|
1602 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
| 1546 |
sheet.write(sheet_iterator, 28, flipkartItemInfo.nlc)
|
1603 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
| 1547 |
sheet.write(sheet_iterator, 29, flipkartPricing.lowestPossibleSp)
|
1604 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
| 1548 |
sheet.write(sheet_iterator, 30, flipkartPricing.lowestPossibleTp)
|
1605 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
| 1549 |
sheet.write(sheet_iterator, 31, round((flipkartPricing.ourTp - flipkartPricing.lowestPossibleTp),2))
|
1606 |
sheet.write(sheet_iterator, 31, round((mpHistory.ourTp - mpHistory.lowestPossibleTp),2))
|
| 1550 |
sheet.write(sheet_iterator, 32, flipkartDetails.totalAvailableSeller)
|
1607 |
sheet.write(sheet_iterator, 32, mpHistory.totalSeller)
|
| 1551 |
sheet_iterator+=1
|
1608 |
sheet_iterator+=1
|
| - |
|
1609 |
|
| - |
|
1610 |
negativeMargin[:]=[]
|
| 1552 |
|
1611 |
|
| 1553 |
if (runType=='FULL'):
|
1612 |
if (runType=='FULL'):
|
| 1554 |
sheet = wbk.add_sheet('Auto Favorites')
|
1613 |
sheet = wbk.add_sheet('Auto Favorites')
|
| 1555 |
|
1614 |
|
| 1556 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1615 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 1598... |
Line 1657... |
| 1598 |
sheet.write(0, 1, "FK Serial number", heading_xf)
|
1657 |
sheet.write(0, 1, "FK Serial number", heading_xf)
|
| 1599 |
sheet.write(0, 2, "Brand", heading_xf)
|
1658 |
sheet.write(0, 2, "Brand", heading_xf)
|
| 1600 |
sheet.write(0, 3, "Product Name", heading_xf)
|
1659 |
sheet.write(0, 3, "Product Name", heading_xf)
|
| 1601 |
sheet.write(0, 4, "Reason", heading_xf)
|
1660 |
sheet.write(0, 4, "Reason", heading_xf)
|
| 1602 |
sheet_iterator=1
|
1661 |
sheet_iterator=1
|
| - |
|
1662 |
|
| - |
|
1663 |
exeptionItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
| - |
|
1664 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
| - |
|
1665 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
| - |
|
1666 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| - |
|
1667 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.EXCEPTION).all()
|
| - |
|
1668 |
|
| 1603 |
for item in exceptionList:
|
1669 |
for item in exeptionItems:
|
| - |
|
1670 |
mpHistory = item[0]
|
| - |
|
1671 |
flipkartItem = item[1]
|
| - |
|
1672 |
mpItem = item[2]
|
| - |
|
1673 |
catItem = item[3]
|
| 1604 |
sheet.write(sheet_iterator, 0, item.item_id)
|
1674 |
sheet.write(sheet_iterator, 0, mpHistory.item_id)
|
| 1605 |
sheet.write(sheet_iterator, 1, item.fkSerialNumber)
|
1675 |
sheet.write(sheet_iterator, 1, flipkartItem.flipkartSerialNumber)
|
| 1606 |
sheet.write(sheet_iterator, 2, item.brand)
|
1676 |
sheet.write(sheet_iterator, 2, catItem.brand)
|
| 1607 |
sheet.write(sheet_iterator, 3, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
|
1677 |
sheet.write(sheet_iterator, 3, xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
| 1608 |
try:
|
1678 |
try:
|
| 1609 |
if item.totalAvailableSeller is None:
|
1679 |
if mpHistory.totalSeller is None:
|
| 1610 |
pass
|
1680 |
pass
|
| 1611 |
except:
|
1681 |
except:
|
| 1612 |
sheet.write(sheet_iterator, 4, "Unable to fetch info from Flipkart")
|
1682 |
sheet.write(sheet_iterator, 4, "Unable to fetch info from Flipkart")
|
| 1613 |
sheet_iterator+=1
|
1683 |
sheet_iterator+=1
|
| 1614 |
continue
|
1684 |
continue
|
| 1615 |
sheet.write(sheet_iterator, 4, "No Seller Available")
|
1685 |
sheet.write(sheet_iterator, 4, "No Seller Available")
|
| 1616 |
sheet_iterator+=1
|
1686 |
sheet_iterator+=1
|
| - |
|
1687 |
|
| - |
|
1688 |
exeptionItems[:]=[]
|
| 1617 |
|
1689 |
|
| 1618 |
sheet = wbk.add_sheet('Can Compete-No Inv')
|
1690 |
sheet = wbk.add_sheet('Can Compete-No Inv')
|
| 1619 |
xstr = lambda s: s or ""
|
1691 |
xstr = lambda s: s or ""
|
| 1620 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1692 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1621 |
|
1693 |
|
| Line 1657... |
Line 1729... |
| 1657 |
sheet.write(0, 31, "Target SP", heading_xf)
|
1729 |
sheet.write(0, 31, "Target SP", heading_xf)
|
| 1658 |
sheet.write(0, 32, "Target TP", heading_xf)
|
1730 |
sheet.write(0, 32, "Target TP", heading_xf)
|
| 1659 |
sheet.write(0, 33, "Sales Potential", heading_xf)
|
1731 |
sheet.write(0, 33, "Sales Potential", heading_xf)
|
| 1660 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
1732 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
| 1661 |
sheet_iterator = 1
|
1733 |
sheet_iterator = 1
|
| - |
|
1734 |
|
| - |
|
1735 |
competitiveNoInventory = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
| - |
|
1736 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
| - |
|
1737 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
| - |
|
1738 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| - |
|
1739 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE_NO_INVENTORY).all()
|
| - |
|
1740 |
|
| 1662 |
for item in competitiveNoInventory:
|
1741 |
for item in competitiveNoInventory:
|
| 1663 |
flipkartDetails = item[0]
|
1742 |
mpHistory = item[0]
|
| 1664 |
flipkartItemInfo = item[1]
|
1743 |
flipkartItem = item[1]
|
| 1665 |
flipkartPricing = item[2]
|
1744 |
mpItem = item[2]
|
| 1666 |
mpItem = item[3]
|
1745 |
catItem = item[3]
|
| 1667 |
if ((not inventoryMap.has_key(flipkartItemInfo.item_id)) or getNetAvailability(inventoryMap.get(flipkartItemInfo.item_id))<=0):
|
1746 |
if ((not inventoryMap.has_key(mpHistory.item_id)) or getNetAvailability(inventoryMap.get(mpHistory.item_id))<=0):
|
| 1668 |
sheet.write(sheet_iterator,0,flipkartItemInfo.item_id)
|
1747 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
| 1669 |
sheet.write(sheet_iterator,1,flipkartItemInfo.parent_category_name)
|
1748 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
| 1670 |
sheet.write(sheet_iterator,2,flipkartItemInfo.product_group)
|
1749 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
| 1671 |
sheet.write(sheet_iterator,3,flipkartItemInfo.fkSerialNumber)
|
1750 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
| 1672 |
sheet.write(sheet_iterator,4,flipkartItemInfo.brand)
|
1751 |
sheet.write(sheet_iterator,4,catItem.brand)
|
| 1673 |
sheet.write(sheet_iterator,5,xstr(flipkartItemInfo.brand)+" "+xstr(flipkartItemInfo.model_name)+" "+xstr(flipkartItemInfo.model_number)+" "+xstr(flipkartItemInfo.color))
|
1752 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
| 1674 |
sheet.write(sheet_iterator,6,flipkartItemInfo.weight)
|
1753 |
sheet.write(sheet_iterator,6,catItem.weight)
|
| 1675 |
sheet.write(sheet_iterator,7,flipkartItemInfo.courierCost)
|
1754 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
| 1676 |
sheet.write(sheet_iterator,8,flipkartItemInfo.risky)
|
1755 |
sheet.write(sheet_iterator,8,catItem.risky)
|
| 1677 |
sheet.write(sheet_iterator,9,flipkartDetails.ourScore)
|
1756 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
| 1678 |
ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
1757 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
| 1679 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
1758 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
| 1680 |
sheet.write(sheet_iterator,10,ourShippingTime)
|
1759 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
| 1681 |
sheet.write(sheet_iterator,11,flipkartDetails.rank)
|
1760 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
| 1682 |
sheet.write(sheet_iterator,12,flipkartPricing.ourSp)
|
1761 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
| 1683 |
sheet.write(sheet_iterator,13,flipkartPricing.ourTp)
|
1762 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
| 1684 |
sheet.write(sheet_iterator,14,flipkartDetails.lowestSellerName)
|
1763 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
| 1685 |
sheet.write(sheet_iterator,15,flipkartDetails.lowestSellerScore)
|
1764 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
| 1686 |
lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
1765 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
| 1687 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
1766 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
| 1688 |
sheet.write(sheet_iterator,16,lowestSellerShippingTime)
|
1767 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
| 1689 |
sheet.write(sheet_iterator,17,flipkartDetails.lowestSellerSp)
|
1768 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
| 1690 |
sheet.write(sheet_iterator,18,flipkartPricing.lowestTp)
|
1769 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
| 1691 |
sheet.write(sheet_iterator,19,flipkartDetails.prefSellerName)
|
1770 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerName)
|
| 1692 |
sheet.write(sheet_iterator,20,flipkartDetails.prefSellerScore)
|
1771 |
sheet.write(sheet_iterator,20,mpHistory.prefferedSellerRating)
|
| 1693 |
prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
1772 |
# prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
| 1694 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
1773 |
# else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
| 1695 |
sheet.write(sheet_iterator,21,prefferedSellerShippingTime)
|
1774 |
sheet.write(sheet_iterator,21,mpHistory.prefferedSellerShippingTime)
|
| 1696 |
sheet.write(sheet_iterator,22,flipkartDetails.prefSellerSp)
|
1775 |
sheet.write(sheet_iterator,22,mpHistory.prefferedSellerSellingPrice)
|
| 1697 |
sheet.write(sheet_iterator,23,flipkartPricing.prefSellerTp)
|
1776 |
sheet.write(sheet_iterator,23,mpHistory.prefferedSellerTp)
|
| 1698 |
sheet.write(sheet_iterator,24,flipkartItemInfo.ourFlipkartInventory)
|
1777 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
| 1699 |
if (not inventoryMap.has_key(flipkartItemInfo.item_id)):
|
1778 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 1700 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
1779 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
| 1701 |
else:
|
1780 |
else:
|
| 1702 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(flipkartItemInfo.item_id)))
|
1781 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
| 1703 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(flipkartItemInfo.item_id))[1]))
|
1782 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
| 1704 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(flipkartItemInfo.item_id))[3])
|
1783 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
| 1705 |
sheet.write(sheet_iterator, 28, flipkartItemInfo.nlc)
|
1784 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
| 1706 |
sheet.write(sheet_iterator, 29, flipkartPricing.lowestPossibleSp)
|
1785 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
| 1707 |
sheet.write(sheet_iterator, 30, flipkartPricing.lowestPossibleTp)
|
1786 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
| 1708 |
proposed_sp = max(flipkartDetails.lowestSellerSp - max((10, flipkartDetails.lowestSellerSp*0.001)), flipkartPricing.lowestPossibleSp)
|
1787 |
proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
|
| 1709 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
1788 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 1710 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
1789 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
| 1711 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
1790 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
| 1712 |
sheet.write(sheet_iterator, 33, getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
1791 |
sheet.write(sheet_iterator, 33, getSalesPotential(mpHistory.lowestPossibleSp,mpHistory.ourNlc))
|
| 1713 |
sheet.write(sheet_iterator, 34, flipkartDetails.totalAvailableSeller)
|
1792 |
sheet.write(sheet_iterator, 34, mpHistory.totalSeller)
|
| 1714 |
sheet_iterator+=1
|
1793 |
sheet_iterator+=1
|
| 1715 |
|
1794 |
|
| - |
|
1795 |
|
| 1716 |
sheet = wbk.add_sheet('Can Compete-No Inv On FK')
|
1796 |
sheet = wbk.add_sheet('Can Compete-No Inv On FK')
|
| 1717 |
xstr = lambda s: s or ""
|
1797 |
xstr = lambda s: s or ""
|
| 1718 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1798 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| 1719 |
|
1799 |
|
| 1720 |
excel_integer_format = '0'
|
1800 |
excel_integer_format = '0'
|
| Line 1756... |
Line 1836... |
| 1756 |
sheet.write(0, 32, "Target TP", heading_xf)
|
1836 |
sheet.write(0, 32, "Target TP", heading_xf)
|
| 1757 |
sheet.write(0, 33, "Sales Potential", heading_xf)
|
1837 |
sheet.write(0, 33, "Sales Potential", heading_xf)
|
| 1758 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
1838 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
| 1759 |
sheet_iterator = 1
|
1839 |
sheet_iterator = 1
|
| 1760 |
for item in competitiveNoInventory:
|
1840 |
for item in competitiveNoInventory:
|
| 1761 |
flipkartDetails = item[0]
|
1841 |
mpHistory = item[0]
|
| 1762 |
flipkartItemInfo = item[1]
|
1842 |
flipkartItem = item[1]
|
| 1763 |
flipkartPricing = item[2]
|
1843 |
mpItem = item[2]
|
| 1764 |
mpItem = item[3]
|
1844 |
catItem = item[3]
|
| 1765 |
if (inventoryMap.has_key(flipkartItemInfo.item_id) and getNetAvailability(inventoryMap.get(flipkartItemInfo.item_id))>0):
|
1845 |
if (inventoryMap.has_key(mpHistory.item_id) and getNetAvailability(inventoryMap.get(mpHistory.item_id))>0):
|
| 1766 |
sheet.write(sheet_iterator,0,flipkartItemInfo.item_id)
|
1846 |
sheet.write(sheet_iterator,0,mpHistory.item_id)
|
| 1767 |
sheet.write(sheet_iterator,1,flipkartItemInfo.parent_category_name)
|
1847 |
sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
|
| 1768 |
sheet.write(sheet_iterator,2,flipkartItemInfo.product_group)
|
1848 |
sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
|
| 1769 |
sheet.write(sheet_iterator,3,flipkartItemInfo.fkSerialNumber)
|
1849 |
sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
|
| 1770 |
sheet.write(sheet_iterator,4,flipkartItemInfo.brand)
|
1850 |
sheet.write(sheet_iterator,4,catItem.brand)
|
| 1771 |
sheet.write(sheet_iterator,5,xstr(flipkartItemInfo.brand)+" "+xstr(flipkartItemInfo.model_name)+" "+xstr(flipkartItemInfo.model_number)+" "+xstr(flipkartItemInfo.color))
|
1851 |
sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
|
| 1772 |
sheet.write(sheet_iterator,6,flipkartItemInfo.weight)
|
1852 |
sheet.write(sheet_iterator,6,catItem.weight)
|
| 1773 |
sheet.write(sheet_iterator,7,flipkartItemInfo.courierCost)
|
1853 |
sheet.write(sheet_iterator,7,mpItem.courierCost)
|
| 1774 |
sheet.write(sheet_iterator,8,flipkartItemInfo.risky)
|
1854 |
sheet.write(sheet_iterator,8,catItem.risky)
|
| 1775 |
sheet.write(sheet_iterator,9,flipkartDetails.ourScore)
|
1855 |
sheet.write(sheet_iterator,9,mpHistory.ourRating)
|
| 1776 |
ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
1856 |
# ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
|
| 1777 |
else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
1857 |
# else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
|
| 1778 |
sheet.write(sheet_iterator,10,ourShippingTime)
|
1858 |
sheet.write(sheet_iterator,10,mpHistory.ourShippingTime)
|
| 1779 |
sheet.write(sheet_iterator,11,flipkartDetails.rank)
|
1859 |
sheet.write(sheet_iterator,11,mpHistory.ourRank)
|
| 1780 |
sheet.write(sheet_iterator,12,flipkartPricing.ourSp)
|
1860 |
sheet.write(sheet_iterator,12,mpHistory.ourSellingPrice)
|
| 1781 |
sheet.write(sheet_iterator,13,flipkartPricing.ourTp)
|
1861 |
sheet.write(sheet_iterator,13,mpHistory.ourTp)
|
| 1782 |
sheet.write(sheet_iterator,14,flipkartDetails.lowestSellerName)
|
1862 |
sheet.write(sheet_iterator,14,mpHistory.lowestSellerName)
|
| 1783 |
sheet.write(sheet_iterator,15,flipkartDetails.lowestSellerScore)
|
1863 |
sheet.write(sheet_iterator,15,mpHistory.lowestSellerRating)
|
| 1784 |
lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
1864 |
# lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
|
| 1785 |
else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
1865 |
# else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
|
| 1786 |
sheet.write(sheet_iterator,16,lowestSellerShippingTime)
|
1866 |
sheet.write(sheet_iterator,16,mpHistory.lowestSellerShippingTime)
|
| 1787 |
sheet.write(sheet_iterator,17,flipkartDetails.lowestSellerSp)
|
1867 |
sheet.write(sheet_iterator,17,mpHistory.lowestSellingPrice)
|
| 1788 |
sheet.write(sheet_iterator,18,flipkartPricing.lowestTp)
|
1868 |
sheet.write(sheet_iterator,18,mpHistory.lowestTp)
|
| 1789 |
sheet.write(sheet_iterator,19,flipkartDetails.prefSellerName)
|
1869 |
sheet.write(sheet_iterator,19,mpHistory.prefferedSellerName)
|
| 1790 |
sheet.write(sheet_iterator,20,flipkartDetails.prefSellerScore)
|
1870 |
sheet.write(sheet_iterator,20,mpHistory.prefferedSellerRating)
|
| 1791 |
prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
1871 |
# prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
|
| 1792 |
else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
1872 |
# else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
|
| 1793 |
sheet.write(sheet_iterator,21,prefferedSellerShippingTime)
|
1873 |
sheet.write(sheet_iterator,21,mpHistory.prefferedSellerShippingTime)
|
| 1794 |
sheet.write(sheet_iterator,22,flipkartDetails.prefSellerSp)
|
1874 |
sheet.write(sheet_iterator,22,mpHistory.prefferedSellerSellingPrice)
|
| 1795 |
sheet.write(sheet_iterator,23,flipkartPricing.prefSellerTp)
|
1875 |
sheet.write(sheet_iterator,23,mpHistory.prefferedSellerTp)
|
| 1796 |
sheet.write(sheet_iterator,24,flipkartItemInfo.ourFlipkartInventory)
|
1876 |
sheet.write(sheet_iterator,24,mpHistory.ourInventory)
|
| 1797 |
if (not inventoryMap.has_key(flipkartItemInfo.item_id)):
|
1877 |
if (not inventoryMap.has_key(mpHistory.item_id)):
|
| 1798 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
1878 |
sheet.write(sheet_iterator, 25, 'Info not available')
|
| 1799 |
else:
|
1879 |
else:
|
| 1800 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(flipkartItemInfo.item_id)))
|
1880 |
sheet.write(sheet_iterator, 25, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
|
| 1801 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(flipkartItemInfo.item_id))[1]))
|
1881 |
sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
|
| 1802 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(flipkartItemInfo.item_id))[3])
|
1882 |
sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
|
| 1803 |
sheet.write(sheet_iterator, 28, flipkartItemInfo.nlc)
|
1883 |
sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
|
| 1804 |
sheet.write(sheet_iterator, 29, flipkartPricing.lowestPossibleSp)
|
1884 |
sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
|
| 1805 |
sheet.write(sheet_iterator, 30, flipkartPricing.lowestPossibleTp)
|
1885 |
sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
|
| 1806 |
proposed_sp = max(flipkartDetails.lowestSellerSp - max((10, flipkartDetails.lowestSellerSp*0.001)), flipkartPricing.lowestPossibleSp)
|
1886 |
proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
|
| 1807 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
1887 |
proposed_tp = getTargetTp(proposed_sp,mpItem)
|
| 1808 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
1888 |
sheet.write(sheet_iterator, 31, proposed_sp)
|
| 1809 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
1889 |
sheet.write(sheet_iterator, 32, proposed_tp)
|
| 1810 |
sheet.write(sheet_iterator, 33, getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
|
1890 |
sheet.write(sheet_iterator, 33, getSalesPotential(mpHistory.lowestPossibleSp,mpHistory.ourNlc))
|
| 1811 |
sheet.write(sheet_iterator, 34, flipkartDetails.totalAvailableSeller)
|
1891 |
sheet.write(sheet_iterator, 34, mpHistory.totalSeller)
|
| 1812 |
sheet_iterator+=1
|
1892 |
sheet_iterator+=1
|
| - |
|
1893 |
competitiveNoInventory[:]=[]
|
| 1813 |
|
1894 |
|
| 1814 |
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()
|
1895 |
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()
|
| 1815 |
sheet = wbk.add_sheet('Auto Inc and Dec')
|
1896 |
sheet = wbk.add_sheet('Auto Inc and Dec')
|
| 1816 |
|
1897 |
|
| 1817 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
1898 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
| Line 1874... |
Line 1955... |
| 1874 |
print "Successfully sent email"
|
1955 |
print "Successfully sent email"
|
| 1875 |
except:
|
1956 |
except:
|
| 1876 |
print "Error: unable to send email."
|
1957 |
print "Error: unable to send email."
|
| 1877 |
|
1958 |
|
| 1878 |
def populateScrapingResults(val):
|
1959 |
def populateScrapingResults(val):
|
| 1879 |
global testcount
|
- |
|
| 1880 |
testcount+=1
|
- |
|
| 1881 |
print "*****testcount",testcount
|
- |
|
| 1882 |
print val
|
- |
|
| 1883 |
try:
|
1960 |
try:
|
| 1884 |
flipkartDetails = fetchDetails(val.fkSerialNumber,scraper)
|
1961 |
flipkartDetails = fetchDetails(val.fkSerialNumber,scraper)
|
| 1885 |
val.flipkartDetails = flipkartDetails
|
1962 |
val.flipkartDetails = flipkartDetails
|
| 1886 |
except Exception as e:
|
1963 |
except Exception as e:
|
| 1887 |
print "Unable to fetch details of",val.fkSerialNumber
|
1964 |
print "Unable to fetch details of",val.fkSerialNumber
|
| Line 1899... |
Line 1976... |
| 1899 |
|
1976 |
|
| 1900 |
val.ourFlipkartInventory = stock_count
|
1977 |
val.ourFlipkartInventory = stock_count
|
| 1901 |
|
1978 |
|
| 1902 |
def threadsToSpawn(runType,itemInfo,scraper,itemPopulated):
|
1979 |
def threadsToSpawn(runType,itemInfo,scraper,itemPopulated):
|
| 1903 |
if runType == RunType.FAVOURITE:
|
1980 |
if runType == RunType.FAVOURITE:
|
| - |
|
1981 |
count = 0
|
| - |
|
1982 |
pool = ThreadPool(3)
|
| 1904 |
startOffset = itemPopulated
|
1983 |
startOffset = 0
|
| 1905 |
endOffset = startOffset
|
1984 |
endOffset = startOffset
|
| 1906 |
while(len(threads)< 3 and endOffset<len(itemInfo)):
|
1985 |
while(count<3 and endOffset<len(itemInfo)):
|
| - |
|
1986 |
print "inside while",testcount
|
| 1907 |
endOffset = startOffset + 20
|
1987 |
endOffset = startOffset + 20
|
| 1908 |
if (endOffset >= len(itemInfo)):
|
1988 |
if (endOffset >= len(itemInfo)):
|
| 1909 |
endOffset = len(itemInfo)
|
1989 |
endOffset = len(itemInfo)
|
| - |
|
1990 |
print "pool offset start end count"+str(startOffset)+" "+str(endOffset)+" "+str(count)
|
| - |
|
1991 |
time.sleep(10)
|
| - |
|
1992 |
pool.map(populateScrapingResults,itemInfo[startOffset:endOffset])
|
| - |
|
1993 |
#t = Process(target=decideCategory,args=(itemInfo[startOffset:endOffset], scraper))
|
| 1910 |
t = threading.Thread(target=partial(populateScrapingResults, itemInfo[startOffset:endOffset],scraper))
|
1994 |
#t = threading.Thread(target=partial(decideCategory, itemInfo[startOffset:endOffset], scraper))
|
| 1911 |
#t = threading.Thread(target=partial(test, startOffset, endOffset))
|
1995 |
#t = threading.Thread(target=partial(test, startOffset, endOffset))
|
| 1912 |
threads.append(t)
|
1996 |
#threads.append(t)
|
| 1913 |
startOffset = startOffset + 20
|
1997 |
startOffset = startOffset + 20
|
| - |
|
1998 |
count+=1
|
| 1914 |
[t.start() for t in threads]
|
1999 |
#[t.start() for t in threads]
|
| 1915 |
[t.join() for t in threads]
|
2000 |
#[t.join() for t in threads]
|
| 1916 |
threads = []
|
2001 |
#threads = []
|
| - |
|
2002 |
print "terminating while"
|
| - |
|
2003 |
pool.close()
|
| - |
|
2004 |
pool.join()
|
| - |
|
2005 |
print "joining threads"
|
| - |
|
2006 |
print "returning offset******"
|
| 1917 |
return endOffset
|
2007 |
return endOffset
|
| 1918 |
else:
|
2008 |
else:
|
| 1919 |
count = 0
|
2009 |
count = 0
|
| 1920 |
pool = ThreadPool(5)
|
2010 |
pool = ThreadPool(5)
|
| 1921 |
startOffset = 0
|
2011 |
startOffset = 0
|
| Line 1958... |
Line 2048... |
| 1958 |
sys.exit(1)
|
2048 |
sys.exit(1)
|
| 1959 |
timestamp = datetime.now()
|
2049 |
timestamp = datetime.now()
|
| 1960 |
previousProcessingTimestamp = session.query(func.max(MarketPlaceHistory.timestamp)).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).one()
|
2050 |
previousProcessingTimestamp = session.query(func.max(MarketPlaceHistory.timestamp)).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).one()
|
| 1961 |
itemInfo= populateStuff(options.runType,timestamp)
|
2051 |
itemInfo= populateStuff(options.runType,timestamp)
|
| 1962 |
itemsPopulated = 0
|
2052 |
itemsPopulated = 0
|
| 1963 |
while (itemsPopulated<len(itemInfo)):
|
2053 |
while (len(itemInfo)>0):
|
| 1964 |
itemsPopulated = threadsToSpawn(options.runType,itemInfo,scraper,itemsPopulated)
|
2054 |
itemsPopulated = threadsToSpawn(options.runType,itemInfo,scraper,itemsPopulated)
|
| 1965 |
cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap = decideCategory(itemInfo[0:itemsPopulated])
|
2055 |
cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap = decideCategory(itemInfo[0:itemsPopulated])
|
| 1966 |
itemInfo[0:itemsPopulated] = []
|
2056 |
itemInfo[0:itemsPopulated] = []
|
| 1967 |
commitExceptionList(exceptionItems,timestamp)
|
2057 |
commitExceptionList(exceptionItems,timestamp)
|
| 1968 |
commitCantCompete(cantCompete,timestamp)
|
2058 |
commitCantCompete(cantCompete,timestamp)
|
| Line 1972... |
Line 2062... |
| 1972 |
commitNegativeMargin(negativeMargin,timestamp)
|
2062 |
commitNegativeMargin(negativeMargin,timestamp)
|
| 1973 |
commitCheapButNotPref(cheapButNotPref,timestamp)
|
2063 |
commitCheapButNotPref(cheapButNotPref,timestamp)
|
| 1974 |
commitPrefButNotCheap(prefButNotCheap, timestamp)
|
2064 |
commitPrefButNotCheap(prefButNotCheap, timestamp)
|
| 1975 |
cantCompete[:], buyBoxItems[:], competitive[:], competitiveNoInventory[:], exceptionItems[:], negativeMargin[:], cheapButNotPref[:], prefButNotCheap[:] =[],[],[],[],[],[],[],[]
|
2065 |
cantCompete[:], buyBoxItems[:], competitive[:], competitiveNoInventory[:], exceptionItems[:], negativeMargin[:], cheapButNotPref[:], prefButNotCheap[:] =[],[],[],[],[],[],[],[]
|
| 1976 |
|
2066 |
|
| 1977 |
#getLastUpdatedOn(mailSender.start, mailSender.stop)
|
- |
|
| 1978 |
#successfulAutoDecrease = fetchItemsForAutoDecrease(timestamp)
|
2067 |
successfulAutoDecrease = fetchItemsForAutoDecrease(timestamp)
|
| 1979 |
#successfulAutoIncrease = fetchItemsForAutoIncrease(timestamp)
|
2068 |
successfulAutoIncrease = fetchItemsForAutoIncrease(timestamp)
|
| 1980 |
# if options.runType=='FULL':
|
2069 |
if options.runType=='FULL':
|
| 1981 |
# previousAutoFav, nowAutoFav = markAutoFavourite()
|
2070 |
previousAutoFav, nowAutoFav = markAutoFavourite()
|
| 1982 |
# if options.runType=='FULL':
|
- |
|
| 1983 |
# write_report(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap, previousAutoFav, nowAutoFav,timestamp, options.runType)
|
2071 |
write_report(previousAutoFav,nowAutoFav,timestamp,options.runType)
|
| 1984 |
# else:
|
- |
|
| 1985 |
# write_report(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap, None, None, timestamp, options.runType)
|
- |
|
| 1986 |
|
2072 |
|
| 1987 |
if __name__ == '__main__':
|
2073 |
if __name__ == '__main__':
|
| 1988 |
main()
|
2074 |
main()
|
| 1989 |
|
2075 |
|