Subversion Repositories SmartDukaan

Rev

Rev 9920 | Rev 9952 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
9881 kshitij.so 1 
from elixir import *
9949 kshitij.so 2 
from sqlalchemy.sql import or_ ,func
9881 kshitij.so 3 
from shop2020.config.client.ConfigClient import ConfigClient
4 
from shop2020.model.v1.catalog.impl import DataService
5 
from shop2020.model.v1.catalog.impl.DataService import SnapdealItem, MarketplaceItems, Item, \
6 
Category, SourcePercentageMaster, MarketPlaceHistory
7 
from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
9897 kshitij.so 8 
from shop2020.thriftpy.model.v1.catalog.ttypes import CompetitionCategory, CompetitionBasis, SalesPotential,\
9949 kshitij.so 9 
Decision, RunType
9881 kshitij.so 10 
from shop2020.clients.CatalogClient import CatalogClient
11 
from shop2020.clients.InventoryClient import InventoryClient
12 
import urllib2
13 
import time
9949 kshitij.so 14 
from datetime import date, datetime, timedelta
15 
from shop2020.utils import EmailAttachmentSender
16 
from shop2020.utils.EmailAttachmentSender import get_attachment_part
17 
import math
9881 kshitij.so 18 
import simplejson as json
9949 kshitij.so 19 
import xlwt
20 
import optparse
9881 kshitij.so 21 
import sys
22 
 
23 
config_client = ConfigClient()
24 
host = config_client.get_property('staging_hostname')
25 
DataService.initialize(db_hostname=host)
9897 kshitij.so 26 
import logging
27 
lgr = logging.getLogger()
28 
lgr.setLevel(logging.DEBUG)
29 
fh = logging.FileHandler('snapdeal-history.log')
30 
fh.setLevel(logging.INFO)
31 
frmt = logging.Formatter('%(asctime)s - %(name)s - %(message)s')
32 
fh.setFormatter(frmt)
33 
lgr.addHandler(fh)
9881 kshitij.so 34 
 
9949 kshitij.so 35 
inventoryMap = {}
36 
itemSaleMap = {}
37 
 
9881 kshitij.so 38 
class __Exception:
39 
    SERVER_SIDE=1
40 
 
41 
 
42 
class __SnapdealDetails:
43 
    def __init__(self, ourSp, ourInventory, otherInventory, rank, lowestSellerName, lowestSellerCode,lowestSp,secondLowestSellerName, secondLowestSellerCode,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice, totalSeller):
44 
        self.ourSp = ourSp
45 
        self.ourOfferPrice = ourOfferPrice
46 
        self.ourInventory = ourInventory
47 
        self.otherInventory = otherInventory
48 
        self.rank = rank
49 
        self.lowestSellerName = lowestSellerName
50 
        self.lowestSellerCode = lowestSellerCode
51 
        self.lowestSp = lowestSp
52 
        self.lowestOfferPrice = lowestOfferPrice
53 
        self.secondLowestSellerName = secondLowestSellerName
54 
        self.secondLowestSellerCode = secondLowestSellerCode
55 
        self.secondLowestSellerSp = secondLowestSellerSp
56 
        self.secondLowestSellerOfferPrice = secondLowestSellerOfferPrice
57 
        self.secondLowestSellerInventory = secondLowestSellerInventory
58 
        self.totalSeller = totalSeller
59 
 
60 
class __SnapdealItemInfo:
61 
 
9949 kshitij.so 62 
    def __init__(self, supc, nlc, courierCost, item_id, product_group, brand, model_name, model_number, color, weight, parent_category, risky, warehouseId, vatRate, runType, parent_category_name):
9881 kshitij.so 63 
        self.supc = supc
64 
        self.nlc = nlc
65 
        self.courierCost = courierCost
66 
        self.item_id = item_id
67 
        self.product_group = product_group
68 
        self.brand = brand
69 
        self.model_name = model_name
70 
        self.model_number = model_number
71 
        self.color = color
72 
        self.weight = weight
73 
        self.parent_category = parent_category
74 
        self.risky = risky
75 
        self.warehouseId = warehouseId
76 
        self.vatRate = vatRate
9949 kshitij.so 77 
        self.runType = runType
78 
        self.parent_category_name = parent_category_name
9881 kshitij.so 79 
 
80 
class __SnapdealPricing:
81 
 
82 
    def __init__(self, ourSp, ourTp, lowestTp, lowestPossibleTp, competitionBasis, secondLowestSellerTp, lowestPossibleSp):
83 
        self.ourTp = ourTp
84 
        self.lowestTp = lowestTp
85 
        self.lowestPossibleTp = lowestPossibleTp
86 
        self.competitionBasis = competitionBasis
87 
        self.ourSp = ourSp
88 
        self.secondLowestSellerTp = secondLowestSellerTp
89 
        self.lowestPossibleSp = lowestPossibleSp
90 
 
9897 kshitij.so 91 
def fetchItemsForAutoDecrease(time):
9920 kshitij.so 92 
    autoDecrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
93 
    .filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE)\
94 
    .filter(MarketplaceItems.source==OrderSource.SNAPDEAL).filter(MarketplaceItems.autoDecrement==True).all()
95 
    #autoDecrementItems = MarketplaceItems.query.filter(MarketplaceItems.autoDecrement==True).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()
9897 kshitij.so 96 
    inventory_client = InventoryClient().get_client()
9949 kshitij.so 97 
    global inventoryMap
9897 kshitij.so 98 
    inventoryMap = inventory_client.getInventorySnapshot(0)
99 
    for autoDecrementItem in autoDecrementItems:
9920 kshitij.so 100 
        #mpHistory = MarketPlaceHistory.get_by(source=OrderSource.SNAPDEAL,item_id=autoDecrementItem.itemId,timestamp=time)
101 
        if not autoDecrementItem.competitiveCategory == CompetitionCategory.COMPETITIVE:
102 
            markReasonForMpItem(autoDecrementItem,'Category is '+CompetitionCategory._VALUES_TO_NAMES.get(autoDecrementItem.competitiveCategory),Decision.AUTO_DECREMENT_FAILED)
9914 kshitij.so 103 
            continue
9920 kshitij.so 104 
        if not autoDecrementItem.risky:
105 
            markReasonForMpItem(autoDecrementItem,'Item is not risky',Decision.AUTO_DECREMENT_FAILED)
9897 kshitij.so 106 
            continue
9920 kshitij.so 107 
        if autoDecrementItem.proposedSellingPrice >= autoDecrementItem.ourSellingPrice:
108 
            markReasonForMpItem(autoDecrementItem,'Proposed SP greater than current SP',Decision.AUTO_DECREMENT_FAILED)
9897 kshitij.so 109 
            continue
9920 kshitij.so 110 
        if autoDecrementItem.otherInventory < 3:
111 
            markReasonForMpItem(autoDecrementItem,'Competition stock is not enough',Decision.AUTO_DECREMENT_FAILED)
9897 kshitij.so 112 
            continue
9949 kshitij.so 113 
        #oosStatus = inventory_client.getOosStatusesForXDaysForItem(autoDecrementItem.item_id,0,3)
114 
        #count,sale,daysOfStock = 0,0,0
115 
        #for obj in oosStatus:
116 
        #    if not obj.is_oos:
117 
        #        count+=1
118 
        #        sale = sale+obj.num_orders
119 
        #avgSalePerDay=0 if count==0 else (float(sale)/count)
9897 kshitij.so 120 
        totalAvailability, totalReserved = 0,0
9920 kshitij.so 121 
        if (not inventoryMap.has_key(autoDecrementItem.item_id)):
9949 kshitij.so 122 
            markReasonForMpItem(autoDecrementItem,'Inventory info not available',Decision.AUTO_DECREMENT_FAILED)
9913 kshitij.so 123 
            continue
9920 kshitij.so 124 
        itemInventory=inventoryMap[autoDecrementItem.item_id]
9897 kshitij.so 125 
        availableMap  = itemInventory.availability
126 
        reserveMap = itemInventory.reserved
127 
        for warehouse,availability in availableMap.iteritems():
128 
            if warehouse==16:
129 
                continue
130 
            totalAvailability = totalAvailability+availability
131 
        for warehouse,reserve in reserveMap.iteritems():
132 
            if warehouse==16:
133 
                continue
134 
            totalReserved = totalReserved+reserve
135 
        if (totalAvailability-totalReserved)<=0:
9949 kshitij.so 136 
            markReasonForMpItem(autoDecrementItem,'Net availability is 0',Decision.AUTO_DECREMENT_FAILED)
9897 kshitij.so 137 
            continue
9949 kshitij.so 138 
        #if (avgSalePerDay==0):  #exclude
139 
        #    markReasonForMpItem(autoDecrementItem,'Average sale per day is zero',Decision.AUTO_DECREMENT_FAILED)
140 
        #    continue
141 
        avgSalePerDay = (itemSaleMap.get(autoDecrementItem.item_id))[2]
142 
        try:
143 
            daysOfStock = (float(totalAvailability-totalReserved))/avgSalePerDay
144 
        except ZeroDivisionError,e:
145 
            lgr.info("Infinite days of stock for item "+str(autoDecrementItem.item_id))
146 
            daysOfStock = float("inf")
9897 kshitij.so 147 
        if daysOfStock<2:
9920 kshitij.so 148 
            markReasonForMpItem(autoDecrementItem,'Our stock is not enough',Decision.AUTO_DECREMENT_FAILED)
9897 kshitij.so 149 
            continue
150 
 
9920 kshitij.so 151 
        autoDecrementItem.competitorEnoughStock = True
152 
        autoDecrementItem.ourEnoughStock = True
9949 kshitij.so 153 
        #autoDecrementItem.avgSales = avgSalePerDay
9920 kshitij.so 154 
        autoDecrementItem.decision = Decision.AUTO_DECREMENT_SUCCESS
155 
        autoDecrementItem.reason = 'All conditions for auto decrement true'
156 
        lgr.info("Auto decrement success for itemId "+str(autoDecrementItem.item_id)+" Days of stock "+str(daysOfStock)+" avg sales "+str(avgSalePerDay))
9897 kshitij.so 157 
    session.commit()
158 
 
159 
def fetchItemsForAutoIncrease(time):
9920 kshitij.so 160 
    autoIncrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
161 
    .filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX)\
162 
    .filter(MarketplaceItems.source==OrderSource.SNAPDEAL).filter(MarketplaceItems.autoIncrement==True).all()
163 
    #autoIncrementItems = MarketplaceItems.query.filter(MarketplaceItems.autoIncrement==True).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()
9949 kshitij.so 164 
    #inventory_client = InventoryClient().get_client()
9897 kshitij.so 165 
    for autoIncrementItem in autoIncrementItems:
9920 kshitij.so 166 
        #mpHistory = MarketPlaceHistory.get_by(source=OrderSource.SNAPDEAL,item_id=autoIncrementItem.itemId,timestamp=time)
167 
        if not autoIncrementItem.competitiveCategory == CompetitionCategory.BUY_BOX:
168 
            markReasonForMpItem(autoIncrementItem,'Category is '+CompetitionCategory._VALUES_TO_NAMES.get(autoIncrementItem.competitiveCategory),Decision.AUTO_INCREMENT_FAILED)
9897 kshitij.so 169 
            continue
9920 kshitij.so 170 
        if autoIncrementItem.totalSeller==1 and autoIncrementItem.ourRank==1:
171 
            markReasonForMpItem(autoIncrementItem,'We are the only seller',Decision.AUTO_INCREMENT_FAILED)
9897 kshitij.so 172 
            continue
9920 kshitij.so 173 
        if autoIncrementItem.proposedSellingPrice <= autoIncrementItem.ourSellingPrice:
174 
            markReasonForMpItem(autoIncrementItem,'Proposed SP less than current SP',Decision.AUTO_INCREMENT_FAILED)
9919 kshitij.so 175 
            continue
9949 kshitij.so 176 
        #oosStatus = inventory_client.getOosStatusesForXDaysForItem(autoIncrementItem.item_id,0,3)
177 
        #count,sale,daysOfStock = 0,0,0
178 
        #for obj in oosStatus:
179 
        #    if not obj.is_oos:
180 
        #        count+=1
181 
        #        sale = sale+obj.num_orders
182 
        #avgSalePerDay=0 if count==0 else (float(sale)/count)
9897 kshitij.so 183 
        totalAvailability, totalReserved = 0,0
9920 kshitij.so 184 
        if (not inventoryMap.has_key(autoIncrementItem.item_id)):
9949 kshitij.so 185 
            markReasonForMpItem(autoIncrementItem,'Inventory info not available',Decision.AUTO_INCREMENT_FAILED)
9913 kshitij.so 186 
            continue
9920 kshitij.so 187 
        itemInventory=inventoryMap[autoIncrementItem.item_id]
9897 kshitij.so 188 
        availableMap  = itemInventory.availability
189 
        reserveMap = itemInventory.reserved
190 
        for warehouse,availability in availableMap.iteritems():
191 
            if warehouse==16:
192 
                continue
193 
            totalAvailability = totalAvailability+availability
194 
        for warehouse,reserve in reserveMap.iteritems():
195 
            if warehouse==16:
196 
                continue
197 
            totalReserved = totalReserved+reserve
9949 kshitij.so 198 
        #if (totalAvailability-totalReserved)<=0:
199 
        #    markReasonForMpItem(autoIncrementItem,'Our stock is 0',Decision.AUTO_INCREMENT_FAILED)
200 
        #    continue
201 
        avgSalePerDay = (itemSaleMap.get(autoIncrementItem.item_id))[2]
9912 kshitij.so 202 
        if (avgSalePerDay==0):
9920 kshitij.so 203 
            markReasonForMpItem(autoIncrementItem,'Average sale per day is zero',Decision.AUTO_INCREMENT_FAILED)
9912 kshitij.so 204 
            continue
9897 kshitij.so 205 
        daysOfStock = (float(totalAvailability-totalReserved))/avgSalePerDay
206 
        if daysOfStock>5:
9920 kshitij.so 207 
            markReasonForMpItem(autoIncrementItem,'Our stock is enough',Decision.AUTO_INCREMENT_FAILED)
9897 kshitij.so 208 
            continue
209 
 
9920 kshitij.so 210 
        autoIncrementItem.ourEnoughStock = False
9949 kshitij.so 211 
        #autoIncrementItem.avgSales = avgSalePerDay
9920 kshitij.so 212 
        autoIncrementItem.decision = Decision.AUTO_INCREMENT_SUCCESS
213 
        autoIncrementItem.reason = 'All conditions for auto increment true'
214 
        lgr.info("Auto increment success for itemId "+str(autoIncrementItem.item_id)+" Days of stock "+str(daysOfStock)+" avg sales "+str(avgSalePerDay))
9897 kshitij.so 215 
    session.commit()
216 
 
9949 kshitij.so 217 
def calculateAverageSale(oosStatus):
218 
    count,sale = 0,0
219 
    for obj in oosStatus:
220 
        if not obj.is_oos:
221 
            count+=1
222 
            sale = sale+obj.num_orders
223 
    avgSalePerDay=0 if count==0 else (float(sale)/count)
224 
    return avgSalePerDay
225 
 
226 
def getNetAvailability(itemInventory):
227 
    totalAvailability, totalReserved = 0,0
228 
    availableMap  = itemInventory.availability
229 
    reserveMap = itemInventory.reserved
230 
    for warehouse,availability in availableMap.iteritems():
231 
        if warehouse==16:
232 
            continue
233 
        totalAvailability = totalAvailability+availability
234 
    for warehouse,reserve in reserveMap.iteritems():
235 
        if warehouse==16:
236 
            continue
237 
        totalReserved = totalReserved+reserve
238 
    return totalAvailability - totalReserved
239 
 
240 
def getOosString(oosStatus):
241 
    lastNdaySale=""
242 
    for obj in oosStatus:
243 
        if obj.is_oos:
244 
            lastNdaySale += "X-"
245 
        else:
246 
            lastNdaySale += str(obj.num_orders) + "-"
247 
    return lastNdaySale
248 
 
249 
def markAutoFavourite():
250 
    previouslyAutoFav = []
251 
    nowAutoFav = []
252 
    marketplaceItems = session.query(MarketplaceItems).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()
253 
    fromDate = datetime.now()-timedelta(days = 3, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
254 
    toDate = datetime.now()-timedelta(days = 0, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
255 
    items = session.query(MarketPlaceHistory.item_id,func.max(MarketPlaceHistory.timestamp)).group_by(MarketPlaceHistory.item_id).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.run==RunType.FULL).filter(MarketPlaceHistory.timestamp.between (fromDate,toDate)).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
256 
    toUpdate = [key for key, value in itemSaleMap.items() if value[3] >= 3]
257 
    buyBoxLast3days = []
258 
    for item in items:
259 
        buyBoxLast3days.append(item[0])
260 
    for marketplaceItem in marketplaceItems:
261 
        reason = ""
262 
        toMark = False
263 
        if marketplaceItem.itemId in toUpdate:
264 
            toMark = True
265 
            reason+="Average sale is greater than 3 for last five days (Snapdeal)."
266 
        if marketplaceItem.itemId in buyBoxLast3days:
267 
            toMark = True
268 
            reason+="Item is present in buy box in last 3 days"
269 
        if not marketplaceItem.autoFavourite:
270 
            print "Item is not under auto favourite"
271 
        if toMark:
272 
            temp=[]
273 
            temp.append(marketplaceItem.itemId)
274 
            temp.append(reason)
275 
            nowAutoFav.append(temp)
276 
        if (not toMark) and marketplaceItem.autoFavourite:
277 
            previouslyAutoFav.append(marketplaceItem.itemId)
278 
        marketplaceItem.autoFavourite = toMark
279 
    session.commit()
280 
    return previouslyAutoFav, nowAutoFav
9897 kshitij.so 281 
 
9949 kshitij.so 282 
 
283 
 
9897 kshitij.so 284 
def markReasonForMpItem(mpHistory,reason,decision):
285 
    mpHistory.decision = decision
286 
    mpHistory.reason = reason
287 
 
9881 kshitij.so 288 
def fetchDetails(supc_code):
289 
    url="http://www.snapdeal.com/json/gvbps?supc=%s&catId=91"%(supc_code)
290 
    print url
291 
    time.sleep(1)
292 
    req = urllib2.Request(url)
293 
    response = urllib2.urlopen(req)
294 
    json_input = response.read()
295 
    vendorInfo = json.loads(json_input)
296 
    rank ,otherInventory ,ourInventory, ourOfferPrice, ourSp, iterator, secondLowestSellerSp, secondLowestSellerInventory, \
297 
    lowestOfferPrice,  secondLowestSellerOfferPrice = (0,)*10
298 
    lowestSellerName , lowestSellerCode, secondLowestSellerName, secondLowestSellerCode=('',)*4
299 
    for vendor in vendorInfo:
300 
        if iterator == 0:
301 
            lowestSellerName = vendor['vendorDisplayName']
302 
            lowestSellerCode = vendor['vendorCode']
303 
            try:
304 
                lowestSp = vendor['sellingPriceBefIntCashBack']
305 
            except:
306 
                lowestSp = vendor['sellingPrice']
307 
            lowestOfferPrice = vendor['sellingPrice']
308 
 
309 
        if iterator ==1:
310 
            secondLowestSellerName = vendor['vendorDisplayName']
311 
            secondLowestSellerCode =vendor['vendorCode']
312 
            try:
313 
                secondLowestSellerSp = vendor['sellingPriceBefIntCashBack']
314 
            except:
315 
                secondLowestSellerSp = vendor['sellingPrice']
316 
            secondLowestSellerOfferPrice = vendor['sellingPrice']
317 
            secondLowestSellerInventory = vendor['buyableInventory']
318 
 
319 
        if vendor['vendorDisplayName'] == 'MobilesnMore':
320 
            ourInventory = vendor['buyableInventory']
321 
            try:
322 
                ourSp = vendor['sellingPriceBefIntCashBack']
323 
            except:
324 
                ourSp = vendor['sellingPrice']
325 
            ourOfferPrice = vendor['sellingPrice']
326 
            rank = iterator +1
327 
        else:
328 
            if rank==0:
329 
                otherInventory = otherInventory +vendor['buyableInventory']
330 
        iterator+=1
331 
    snapdealDetails = __SnapdealDetails(ourSp,ourInventory,otherInventory,rank,lowestSellerName, lowestSellerCode,lowestSp,secondLowestSellerName,secondLowestSellerCode,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice,len(vendorInfo))
332 
    return snapdealDetails
333 
 
334 
 
9949 kshitij.so 335 
def populateStuff(runType):
9881 kshitij.so 336 
    itemInfo = []
9949 kshitij.so 337 
    if runType=='FAVOURITE':
338 
        items = session.query(SnapdealItem).join((MarketplaceItems,SnapdealItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).\
339 
        filter(or_(MarketplaceItems.autoFavourite==True, MarketplaceItems.manualFavourite==True)).all()
340 
    else:
341 
        items = session.query(SnapdealItem).all()
9881 kshitij.so 342 
    spm = SourcePercentageMaster.get_by(source=OrderSource.SNAPDEAL)
343 
    for snapdeal_item in items:
344 
        it = Item.query.filter_by(id=snapdeal_item.item_id).one()
345 
        category = Category.query.filter_by(id=it.category).one()
9949 kshitij.so 346 
        parent_category = Category.query.filter_by(id=category.parent_category_id).first()
347 
        snapdealItemInfo = __SnapdealItemInfo(snapdeal_item.supc, snapdeal_item.maxNlc,snapdeal_item.courierCost, it.id, it.product_group, it.brand, it.model_name, it.model_number, it.color, it.weight, category.parent_category_id, it.risky, snapdeal_item.warehouseId, None, runType, parent_category.display_name)
9881 kshitij.so 348 
        itemInfo.append(snapdealItemInfo)
349 
    return itemInfo, spm
350 
 
351 
 
352 
def decideCategory(itemInfo,spm):
9949 kshitij.so 353 
    global itemSaleMap
9881 kshitij.so 354 
    cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin = [],[],[],[],[],[]
355 
    catalog_client = CatalogClient().get_client()
356 
    inventory_client = InventoryClient().get_client()
357 
 
358 
    for val in itemInfo:
359 
        try:
360 
            snapdealDetails = fetchDetails(val.supc)
361 
        except:
362 
            exceptionItems.append(val)
363 
            continue
364 
        mpItem = MarketplaceItems.get_by(itemId=val.item_id,source=OrderSource.SNAPDEAL)
365 
        warehouse = inventory_client.getWarehouse(val.warehouseId)
9949 kshitij.so 366 
 
367 
        itemSaleList = []
368 
        oosForAllSources = inventory_client.getOosStatusesForXDaysForItem(val.item_id, 0, 3)
369 
        oosForSnapdeal = inventory_client.getOosStatusesForXDaysForItem(val.item_id, OrderSource.SNAPDEAL, 5)
370 
        itemSaleList.append(oosForAllSources)
371 
        itemSaleList.append(oosForSnapdeal)
372 
        itemSaleList.append(calculateAverageSale(oosForAllSources))
373 
        itemSaleList.append(calculateAverageSale(oosForSnapdeal))
374 
        itemSaleMap[val.item_id]=itemSaleList
375 
 
9881 kshitij.so 376 
        if snapdealDetails.rank==0:
377 
            snapdealDetails.ourSp = mpItem.currentSp
378 
            snapdealDetails.ourOfferPrice = mpItem.currentSp
379 
            ourSp = mpItem.currentSp
380 
        else:
381 
            ourSp = snapdealDetails.ourSp
382 
        vatRate = catalog_client.getVatPercentageForItem(val.item_id, warehouse.stateId, snapdealDetails.ourSp)
383 
        val.vatRate = vatRate
384 
        if (snapdealDetails.rank==1):
385 
            temp=[]
386 
            temp.append(snapdealDetails)
387 
            temp.append(val)
388 
            if (snapdealDetails.secondLowestSellerOfferPrice == snapdealDetails.secondLowestSellerSp) and snapdealDetails.ourOfferPrice==snapdealDetails.ourSp:
389 
                competitionBasis = 'SP'
390 
            else:
391 
                competitionBasis = 'TP'
392 
            secondLowestTp=0 if snapdealDetails.totalSeller==1 else getOtherTp(snapdealDetails,val,spm)
393 
            snapdealPricing = __SnapdealPricing(snapdealDetails.ourSp,getOurTp(snapdealDetails,val,spm,mpItem),None,getLowestPossibleTp(snapdealDetails,val,spm,mpItem),competitionBasis,secondLowestTp,getLowestPossibleSp(snapdealDetails,val,spm,mpItem))
394 
            temp.append(snapdealPricing)
395 
            temp.append(mpItem)
396 
            buyBoxItems.append(temp)
397 
            continue
398 
 
399 
 
400 
        lowestTp = getOtherTp(snapdealDetails,val,spm)
401 
        ourTp = getOurTp(snapdealDetails,val,spm,mpItem)
402 
        lowestPossibleTp = getLowestPossibleTp(snapdealDetails,val,spm,mpItem)
403 
        lowestPossibleSp = getLowestPossibleSp(snapdealDetails,val,spm,mpItem)
404 
 
405 
        if (ourTp<lowestPossibleTp):
406 
            temp=[]
407 
            temp.append(snapdealDetails)
408 
            temp.append(val)
409 
            snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,None,None)
410 
            temp.append(snapdealPricing)
411 
            negativeMargin.append(temp)
412 
            continue
413 
 
414 
        if (snapdealDetails.lowestOfferPrice == snapdealDetails.lowestSp) and snapdealDetails.ourOfferPrice == snapdealDetails.ourSp:
415 
            competitionBasis ='SP'
416 
        else:
417 
            competitionBasis ='TP'
418 
 
419 
        if competitionBasis=='SP':
420 
            if snapdealDetails.lowestSp > lowestPossibleSp and snapdealDetails.ourInventory!=0:
421 
                temp=[]
422 
                temp.append(snapdealDetails)
423 
                temp.append(val)
424 
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'SP',None,lowestPossibleSp)
425 
                temp.append(snapdealPricing)
426 
                temp.append(mpItem)
427 
                competitive.append(temp)
428 
                continue
429 
        else:
430 
            if lowestTp > lowestPossibleTp and snapdealDetails.ourInventory!=0:
431 
                temp=[]
432 
                temp.append(snapdealDetails)
433 
                temp.append(val)
434 
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'TP',None,lowestPossibleSp)
435 
                temp.append(snapdealPricing)
436 
                temp.append(mpItem)
437 
                competitive.append(temp)
438 
                continue
439 
 
440 
        if competitionBasis=='SP':
441 
            if snapdealDetails.lowestSp > lowestPossibleSp and snapdealDetails.ourInventory==0:
442 
                temp=[]
443 
                temp.append(snapdealDetails)
444 
                temp.append(val)
445 
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'SP',None,lowestPossibleSp)
446 
                temp.append(snapdealPricing)
447 
                temp.append(mpItem)
448 
                competitiveNoInventory.append(temp)
449 
                continue
450 
        else:
451 
            if lowestTp > lowestPossibleTp and snapdealDetails.ourInventory==0:
452 
                temp=[]
453 
                temp.append(snapdealDetails)
454 
                temp.append(val)
455 
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'TP',None,lowestPossibleSp)
456 
                temp.append(snapdealPricing)
457 
                temp.append(mpItem)
458 
                competitiveNoInventory.append(temp)
459 
                continue
460 
 
461 
        temp=[]
462 
        temp.append(snapdealDetails)
463 
        temp.append(val)
464 
        snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,competitionBasis,None,lowestPossibleSp)
465 
        temp.append(snapdealPricing)
466 
        temp.append(mpItem)
467 
        cantCompete.append(temp)
468 
 
9949 kshitij.so 469 
    return cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin
470 
 
471 
def writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, previousAutoFav, nowAutoFav,timestamp):
472 
    wbk = xlwt.Workbook()
473 
    sheet = wbk.add_sheet('Can\'t Compete')
474 
    xstr = lambda s: s or ""
475 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
476 
 
477 
    excel_integer_format = '0'
478 
    integer_style = xlwt.XFStyle()
479 
    integer_style.num_format_str = excel_integer_format
480 
 
481 
    sheet.write(0, 0, "Item ID", heading_xf)
482 
    sheet.write(0, 1, "Category", heading_xf)
483 
    sheet.write(0, 2, "Product Group.", heading_xf)
484 
    sheet.write(0, 3, "SUPC", heading_xf)
485 
    sheet.write(0, 4, "Brand", heading_xf)
486 
    sheet.write(0, 5, "Product Name", heading_xf)
487 
    sheet.write(0, 6, "Weight", heading_xf)
488 
    sheet.write(0, 7, "Courier Cost", heading_xf)
489 
    sheet.write(0, 8, "Risky", heading_xf)
490 
    sheet.write(0, 9, "Our SP", heading_xf)
491 
    sheet.write(0, 11, "Our TP", heading_xf)
492 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
493 
    sheet.write(0, 12, "Our Rank", heading_xf)
494 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
495 
    sheet.write(0, 14, "Lowest SP", heading_xf)
496 
    sheet.write(0, 15, "Lowest TP", heading_xf)
497 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
498 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
499 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
500 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
501 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
502 
    sheet.write(0, 21, "Average Sale", heading_xf)
503 
    sheet.write(0, 22, "Our NLC", heading_xf)
504 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
505 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
506 
    sheet.write(0, 25, "Competition Basis ", heading_xf)
507 
    sheet.write(0, 26, "Target TP", heading_xf)
508 
    sheet.write(0, 27, "Target SP", heading_xf)
509 
    sheet.write(0, 28, "Target NLC", heading_xf)
510 
    sheet.write(0, 29, "Sales Potential", heading_xf)
511 
    sheet_iterator = 1
512 
    for item in cantCompete:
513 
        snapdealDetails = item[0]
514 
        snapdealItemInfo = item[1]
515 
        snapdealPricing = item[2]
516 
        mpItem = item[3]
517 
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
518 
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
519 
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
520 
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
521 
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
522 
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
523 
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
524 
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
525 
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
526 
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
527 
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
528 
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
529 
        sheet.write(sheet_iterator, 12, snapdealDetails.rank)
530 
        sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
531 
        sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
532 
        sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
533 
        sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
534 
        sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
535 
        sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
536 
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
537 
            sheet.write(sheet_iterator, 19, 'Info not available')
538 
        else:
539 
            sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
540 
        sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
541 
        sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
542 
        sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
543 
        sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
544 
        sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
545 
        sheet.write(sheet_iterator, 25, snapdealPricing.competitionBasis)
546 
        if (snapdealPricing.competitionBasis=='SP'):
547 
            proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
548 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
549 
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
550 
        else:
551 
            proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
552 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
553 
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
554 
        sheet.write(sheet_iterator, 26, proposed_tp)
555 
        sheet.write(sheet_iterator, 27, proposed_sp)
556 
        sheet.write(sheet_iterator, 28, target_nlc)
557 
        sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
558 
        sheet_iterator+=1
559 
 
560 
    sheet = wbk.add_sheet('Lowest')
561 
 
562 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
563 
 
564 
    excel_integer_format = '0'
565 
    integer_style = xlwt.XFStyle()
566 
    integer_style.num_format_str = excel_integer_format
567 
    xstr = lambda s: s or ""
568 
 
569 
    sheet.write(0, 0, "Item ID", heading_xf)
570 
    sheet.write(0, 1, "Category", heading_xf)
571 
    sheet.write(0, 2, "Product Group.", heading_xf)
572 
    sheet.write(0, 3, "SUPC", heading_xf)
573 
    sheet.write(0, 4, "Brand", heading_xf)
574 
    sheet.write(0, 5, "Product Name", heading_xf)
575 
    sheet.write(0, 6, "Weight", heading_xf)
576 
    sheet.write(0, 7, "Courier Cost", heading_xf)
577 
    sheet.write(0, 8, "Risky", heading_xf)
578 
    sheet.write(0, 9, "Our SP", heading_xf)
579 
    sheet.write(0, 11, "Our TP", heading_xf)
580 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
581 
    sheet.write(0, 12, "Our Rank", heading_xf)
582 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
583 
    sheet.write(0, 14, "Second Lowest Seller", heading_xf)
584 
    sheet.write(0, 15, "Second Lowest Price", heading_xf)
585 
    sheet.write(0, 16, "Second Lowest Offer Price", heading_xf)
586 
    sheet.write(0, 17, "Second Lowest Seller TP", heading_xf)
587 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
588 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
589 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
590 
    sheet.write(0, 21, "Average Sale", heading_xf)
591 
    sheet.write(0, 22, "Second Lowest Seller Inventory", heading_xf)
592 
    sheet.write(0, 23, "Our NLC", heading_xf)
593 
    sheet.write(0, 24, "Competition Basis", heading_xf)
594 
    sheet.write(0, 25, "Target TP", heading_xf)
595 
    sheet.write(0, 26, "Target SP", heading_xf)
596 
    sheet.write(0, 27, "MARGIN INCREASED POTENTIAL", heading_xf)
597 
 
598 
    sheet_iterator = 1
599 
    for item in buyBoxItems:
600 
        snapdealDetails = item[0]
601 
        snapdealItemInfo = item[1]
602 
        snapdealPricing = item[2]
603 
        mpItem = item[3]
604 
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
605 
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
606 
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
607 
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
608 
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
609 
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
610 
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
611 
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
612 
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
613 
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
614 
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
615 
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
616 
        sheet.write(sheet_iterator, 12, snapdealDetails.rank)
617 
        sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
618 
        sheet.write(sheet_iterator, 14, snapdealDetails.secondLowestSellerName)
619 
        sheet.write(sheet_iterator, 15, snapdealDetails.secondLowestSellerSp)
620 
        sheet.write(sheet_iterator, 16, snapdealDetails.secondLowestSellerOfferPrice)
621 
        sheet.write(sheet_iterator, 17, snapdealPricing.secondLowestSellerTp)
622 
        sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
623 
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
624 
            sheet.write(sheet_iterator, 19, 'Info not available')
625 
        else:
626 
            sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
627 
        sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
628 
        sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
629 
        sheet.write(sheet_iterator, 22, snapdealDetails.secondLowestSellerInventory)
630 
        sheet.write(sheet_iterator, 23, snapdealItemInfo.nlc)
631 
        sheet.write(sheet_iterator, 24, snapdealPricing.competitionBasis)
632 
        if (snapdealPricing.competitionBasis=='SP'):
633 
            proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)), snapdealPricing.lowestPossibleSp)
634 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
635 
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
636 
        else:
637 
            proposed_tp  = max(snapdealPricing.secondLowestSellerTp - max((20, snapdealPricing.secondLowestSellerTp*0.002)), snapdealPricing.lowestPossibleTp)
638 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
639 
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
640 
        sheet.write(sheet_iterator, 25, proposed_tp)
641 
        sheet.write(sheet_iterator, 26, proposed_sp)
642 
        sheet.write(sheet_iterator, 27, proposed_tp - snapdealPricing.ourTp)
643 
        sheet_iterator+=1
644 
 
645 
    sheet = wbk.add_sheet('Can Compete-With Inventory')
646 
 
647 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
648 
 
649 
    excel_integer_format = '0'
650 
    integer_style = xlwt.XFStyle()
651 
    integer_style.num_format_str = excel_integer_format
652 
    xstr = lambda s: s or ""
653 
 
654 
    sheet.write(0, 0, "Item ID", heading_xf)
655 
    sheet.write(0, 1, "Category", heading_xf)
656 
    sheet.write(0, 2, "Product Group.", heading_xf)
657 
    sheet.write(0, 3, "SUPC", heading_xf)
658 
    sheet.write(0, 4, "Brand", heading_xf)
659 
    sheet.write(0, 5, "Product Name", heading_xf)
660 
    sheet.write(0, 6, "Weight", heading_xf)
661 
    sheet.write(0, 7, "Courier Cost", heading_xf)
662 
    sheet.write(0, 8, "Risky", heading_xf)
663 
    sheet.write(0, 9, "Our SP", heading_xf)
664 
    sheet.write(0, 11, "Our TP", heading_xf)
665 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
666 
    sheet.write(0, 12, "Our Rank", heading_xf)
667 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
668 
    sheet.write(0, 14, "Lowest SP", heading_xf)
669 
    sheet.write(0, 15, "Lowest TP", heading_xf)
670 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
671 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
672 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
673 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
674 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
675 
    sheet.write(0, 21, "Average Sale", heading_xf)
676 
    sheet.write(0, 22, "Our NLC", heading_xf)
677 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
678 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
679 
    sheet.write(0, 25, "Competition Basis ", heading_xf)
680 
    sheet.write(0, 26, "Target TP", heading_xf)
681 
    sheet.write(0, 27, "Target SP", heading_xf)
682 
    sheet.write(0, 28, "Sales Potential", heading_xf)
683 
 
684 
    sheet_iterator = 1
685 
    for item in competitive:
686 
        snapdealDetails = item[0]
687 
        snapdealItemInfo = item[1]
688 
        snapdealPricing = item[2]
689 
        mpItem = item[3]
690 
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
691 
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
692 
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
693 
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
694 
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
695 
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
696 
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
697 
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
698 
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
699 
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
700 
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
701 
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
702 
        sheet.write(sheet_iterator, 12, snapdealDetails.rank)
703 
        sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
704 
        sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
705 
        sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
706 
        sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
707 
        sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
708 
        sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
709 
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
710 
            sheet.write(sheet_iterator, 19, 'Info not available')
711 
        else:
712 
            sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
713 
        sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
714 
        sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
715 
        sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
716 
        sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
717 
        sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
718 
        sheet.write(sheet_iterator, 25, snapdealPricing.competitionBasis)
719 
        if (snapdealPricing.competitionBasis=='SP'):
720 
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
721 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
722 
        else:
723 
            proposed_tp  = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
724 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
725 
        sheet.write(sheet_iterator, 26, proposed_tp)
726 
        sheet.write(sheet_iterator, 27, proposed_sp)
727 
        sheet.write(sheet_iterator, 28, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
728 
        sheet_iterator+=1
729 
 
730 
    sheet = wbk.add_sheet('Negative Margin')
731 
 
732 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
733 
 
734 
    excel_integer_format = '0'
735 
    integer_style = xlwt.XFStyle()
736 
    integer_style.num_format_str = excel_integer_format
737 
    xstr = lambda s: s or ""
738 
 
739 
    sheet.write(0, 0, "Item ID", heading_xf)
740 
    sheet.write(0, 1, "Category", heading_xf)
741 
    sheet.write(0, 2, "Product Group.", heading_xf)
742 
    sheet.write(0, 3, "SUPC", heading_xf)
743 
    sheet.write(0, 4, "Brand", heading_xf)
744 
    sheet.write(0, 5, "Product Name", heading_xf)
745 
    sheet.write(0, 6, "Weight", heading_xf)
746 
    sheet.write(0, 7, "Courier Cost", heading_xf)
747 
    sheet.write(0, 8, "Risky", heading_xf)
748 
    sheet.write(0, 9, "Our SP", heading_xf)
749 
    sheet.write(0, 11, "Our TP", heading_xf)
750 
    sheet.write(0, 12, "Lowest Possible TP", heading_xf)
751 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
752 
    sheet.write(0, 13, "Our Rank", heading_xf)
753 
    sheet.write(0, 14, "Our Snapdeal Inventory", heading_xf)
754 
    sheet.write(0, 15, "Net Availability", heading_xf)
755 
    sheet.write(0, 16, "Last Five Day Sale", heading_xf)
756 
    sheet.write(0, 17, "Average Sale", heading_xf)
757 
    sheet.write(0, 18, "Our NLC", heading_xf)
758 
    sheet.write(0, 19, "Margin", heading_xf)
759 
 
760 
    sheet_iterator=1
761 
    for item in negativeMargin:
762 
        snapdealDetails = item[0]
763 
        snapdealItemInfo = item[1]
764 
        snapdealPricing = item[2]
765 
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
766 
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
767 
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
768 
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
769 
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
770 
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
771 
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
772 
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
773 
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
774 
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
775 
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
776 
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
777 
        sheet.write(sheet_iterator, 12, snapdealPricing.lowestPossibleTp)
778 
        sheet.write(sheet_iterator, 13, snapdealDetails.rank)
779 
        sheet.write(sheet_iterator, 14, snapdealDetails.ourInventory)
780 
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
781 
            sheet.write(sheet_iterator, 15, 'Info not available')
782 
        else:
783 
            sheet.write(sheet_iterator, 15, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
784 
        sheet.write(sheet_iterator, 16, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
785 
        sheet.write(sheet_iterator, 17, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
786 
        sheet.write(sheet_iterator, 18, snapdealItemInfo.nlc)
787 
        sheet.write(sheet_iterator, 19, snapdealPricing.ourTp - snapdealPricing.lowestPossibleTp)
788 
        sheet_iterator+=1
789 
 
790 
    sheet = wbk.add_sheet('Exception Item List')
791 
 
792 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
793 
 
794 
    excel_integer_format = '0'
795 
    integer_style = xlwt.XFStyle()
796 
    integer_style.num_format_str = excel_integer_format
797 
    xstr = lambda s: s or ""
798 
 
799 
    sheet.write(0, 0, "Item ID", heading_xf)
800 
    sheet.write(0, 1, "Brand", heading_xf)
801 
    sheet.write(0, 2, "Product Name", heading_xf)
802 
    sheet.write(0, 3, "Reason", heading_xf)
803 
    sheet_iterator=1
804 
    for item in exceptionList:
805 
        sheet.write(sheet_iterator, 0, item.item_id)
806 
        sheet.write(sheet_iterator, 1, item.brand)
807 
        sheet.write(sheet_iterator, 2, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
808 
        sheet.write(sheet_iterator, 3, "Unable to fetch info from Snapdeal")
809 
        sheet_iterator+=1
810 
 
811 
    sheet = wbk.add_sheet('Can Compete-No Inv')
812 
 
813 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
814 
 
815 
    excel_integer_format = '0'
816 
    integer_style = xlwt.XFStyle()
817 
    integer_style.num_format_str = excel_integer_format
818 
    xstr = lambda s: s or ""
819 
 
820 
    sheet.write(0, 0, "Item ID", heading_xf)
821 
    sheet.write(0, 1, "Category", heading_xf)
822 
    sheet.write(0, 2, "Product Group.", heading_xf)
823 
    sheet.write(0, 3, "SUPC", heading_xf)
824 
    sheet.write(0, 4, "Brand", heading_xf)
825 
    sheet.write(0, 5, "Product Name", heading_xf)
826 
    sheet.write(0, 6, "Weight", heading_xf)
827 
    sheet.write(0, 7, "Courier Cost", heading_xf)
828 
    sheet.write(0, 8, "Risky", heading_xf)
829 
    sheet.write(0, 9, "Our SP", heading_xf)
830 
    sheet.write(0, 11, "Our TP", heading_xf)
831 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
832 
    sheet.write(0, 12, "Our Rank", heading_xf)
833 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
834 
    sheet.write(0, 14, "Lowest SP", heading_xf)
835 
    sheet.write(0, 15, "Lowest TP", heading_xf)
836 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
837 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
838 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
839 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
840 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
841 
    sheet.write(0, 21, "Average Sale", heading_xf)
842 
    sheet.write(0, 22, "Our NLC", heading_xf)
843 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
844 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
845 
    sheet.write(0, 25, "Competition Basis ", heading_xf)
846 
    sheet.write(0, 26, "Target TP", heading_xf)
847 
    sheet.write(0, 27, "Target SP", heading_xf)
848 
    sheet.write(0, 28, "Target NLC", heading_xf)
849 
    sheet.write(0, 29, "Sales Potential", heading_xf)
850 
 
851 
    sheet_iterator = 1
852 
    for item in competitiveNoInventory:
853 
        snapdealDetails = item[0]
854 
        snapdealItemInfo = item[1]
855 
        snapdealPricing = item[2]
856 
        mpItem = item[3]
857 
        if ((not inventoryMap.has_key(snapdealItemInfo.item_id)) or getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id))<=0):
858 
            sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
859 
            sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
860 
            sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
861 
            sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
862 
            sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
863 
            sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
864 
            sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
865 
            sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
866 
            sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
867 
            sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
868 
            sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
869 
            sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
870 
            sheet.write(sheet_iterator, 12, snapdealDetails.rank)
871 
            sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
872 
            sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
873 
            sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
874 
            sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
875 
            sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
876 
            sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
877 
            if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
878 
                sheet.write(sheet_iterator, 19, 'Info not available')
879 
            else:
880 
                sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
881 
            sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
882 
            sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
883 
            sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
884 
            sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
885 
            sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
886 
            sheet.write(sheet_iterator, 25, snapdealPricing.competitionBasis)
887 
            if (snapdealPricing.competitionBasis=='SP'):
888 
                proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
889 
                proposed_tp = getTargetTp(proposed_sp,mpItem)
890 
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
891 
            else:
892 
                proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
893 
                proposed_sp = getTargetSp(proposed_tp,mpItem)
894 
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
895 
            sheet.write(sheet_iterator, 26, proposed_tp)
896 
            sheet.write(sheet_iterator, 27, proposed_sp)
897 
            sheet.write(sheet_iterator, 28, target_nlc)
898 
            sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
899 
            sheet_iterator+=1
900 
 
901 
    sheet = wbk.add_sheet('Can Compete-No Inv On SD')
902 
 
903 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
904 
 
905 
    excel_integer_format = '0'
906 
    integer_style = xlwt.XFStyle()
907 
    integer_style.num_format_str = excel_integer_format
908 
    xstr = lambda s: s or ""
909 
 
910 
    sheet.write(0, 0, "Item ID", heading_xf)
911 
    sheet.write(0, 1, "Category", heading_xf)
912 
    sheet.write(0, 2, "Product Group.", heading_xf)
913 
    sheet.write(0, 3, "SUPC", heading_xf)
914 
    sheet.write(0, 4, "Brand", heading_xf)
915 
    sheet.write(0, 5, "Product Name", heading_xf)
916 
    sheet.write(0, 6, "Weight", heading_xf)
917 
    sheet.write(0, 7, "Courier Cost", heading_xf)
918 
    sheet.write(0, 8, "Risky", heading_xf)
919 
    sheet.write(0, 9, "Our SP", heading_xf)
920 
    sheet.write(0, 11, "Our TP", heading_xf)
921 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
922 
    sheet.write(0, 12, "Our Rank", heading_xf)
923 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
924 
    sheet.write(0, 14, "Lowest SP", heading_xf)
925 
    sheet.write(0, 15, "Lowest TP", heading_xf)
926 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
927 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
928 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
929 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
930 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
931 
    sheet.write(0, 21, "Average Sale", heading_xf)
932 
    sheet.write(0, 22, "Our NLC", heading_xf)
933 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
934 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
935 
    sheet.write(0, 25, "Competition Basis ", heading_xf)
936 
    sheet.write(0, 26, "Target TP", heading_xf)
937 
    sheet.write(0, 27, "Target SP", heading_xf)
938 
    sheet.write(0, 28, "Target NLC", heading_xf)
939 
    sheet.write(0, 29, "Sales Potential", heading_xf)
940 
 
941 
    sheet_iterator = 1
942 
    for item in competitiveNoInventory:
943 
        snapdealDetails = item[0]
944 
        snapdealItemInfo = item[1]
945 
        snapdealPricing = item[2]
946 
        mpItem = item[3]
947 
        if (inventoryMap.has_key(snapdealItemInfo.item_id) and getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id))>0):
948 
            sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
949 
            sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
950 
            sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
951 
            sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
952 
            sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
953 
            sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
954 
            sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
955 
            sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
956 
            sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
957 
            sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
958 
            sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
959 
            sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
960 
            sheet.write(sheet_iterator, 12, snapdealDetails.rank)
961 
            sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
962 
            sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
963 
            sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
964 
            sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
965 
            sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
966 
            sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
967 
            if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
968 
                sheet.write(sheet_iterator, 19, 'Info not available')
969 
            else:
970 
                sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
971 
            sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
972 
            sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
973 
            sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
974 
            sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
975 
            sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
976 
            sheet.write(sheet_iterator, 25, snapdealPricing.competitionBasis)
977 
            if (snapdealPricing.competitionBasis=='SP'):
978 
                proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
979 
                proposed_tp = getTargetTp(proposed_sp,mpItem)
980 
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
981 
            else:
982 
                proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
983 
                proposed_sp = getTargetSp(proposed_tp,mpItem)
984 
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
985 
            sheet.write(sheet_iterator, 26, proposed_tp)
986 
            sheet.write(sheet_iterator, 27, proposed_sp)
987 
            sheet.write(sheet_iterator, 28, target_nlc)
988 
            sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
989 
            sheet_iterator+=1
990 
 
991 
    sheet = wbk.add_sheet('Auto Favorites')
992 
 
993 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
994 
 
995 
    excel_integer_format = '0'
996 
    integer_style = xlwt.XFStyle()
997 
    integer_style.num_format_str = excel_integer_format
998 
    xstr = lambda s: s or ""
999 
 
1000 
    sheet.write(0, 0, "Item ID", heading_xf)
1001 
    sheet.write(0, 1, "Brand", heading_xf)
1002 
    sheet.write(0, 2, "Product Name", heading_xf)
1003 
    sheet.write(0, 3, "Auto Favourite", heading_xf)
1004 
    sheet.write(0, 4, "Reason", heading_xf)
1005 
 
1006 
    sheet_iterator=1
1007 
    for autoFav in nowAutoFav:
1008 
        itemId = autoFav[0]
1009 
        reason = autoFav[1]
1010 
        it = Item.query.filter_by(id=itemId).one()
1011 
        sheet.write(sheet_iterator, 0, itemId)
1012 
        sheet.write(sheet_iterator, 1, it.brand)
1013 
        sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1014 
        sheet.write(sheet_iterator, 3, "True")
1015 
        sheet.write(sheet_iterator, 4, reason)
1016 
        sheet_iterator+=1
1017 
    for prevFav in previousAutoFav:
1018 
        it = Item.query.filter_by(id=prevFav).one()
1019 
        sheet.write(sheet_iterator, 0, prevFav)
1020 
        sheet.write(sheet_iterator, 1, it.brand)
1021 
        sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1022 
        sheet.write(sheet_iterator, 3, "False")
1023 
        sheet_iterator+=1
1024 
 
1025 
 
1026 
    autoPricingItems = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.decision.in_([1,2,3,4])).all()
1027 
    sheet = wbk.add_sheet('Auto Inc and Dec')
1028 
 
1029 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1030 
 
1031 
    excel_integer_format = '0'
1032 
    integer_style = xlwt.XFStyle()
1033 
    integer_style.num_format_str = excel_integer_format
1034 
    xstr = lambda s: s or ""
1035 
 
1036 
    sheet.write(0, 0, "Item ID", heading_xf)
1037 
    sheet.write(0, 1, "Brand", heading_xf)
1038 
    sheet.write(0, 2, "Product Name", heading_xf)
1039 
    sheet.write(0, 3, "Decision", heading_xf)
1040 
    sheet.write(0, 4, "Reason", heading_xf)
1041 
    sheet.write(0, 5, "Selling Price Updated",heading_xf)
1042 
 
1043 
    sheet_iterator=1
1044 
    for autoPricingItem in autoPricingItems:
1045 
        mpHistory = autoPricingItem[0]
1046 
        item = autoPricingItem[1]
1047 
        sheet.write(sheet_iterator, 0, item.id)
1048 
        sheet.write(sheet_iterator, 1, it.brand)
1049 
        sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1050 
        sheet.write(sheet_iterator, 3, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
1051 
        sheet.write(sheet_iterator, 4, mpHistory.reason)
1052 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
1053 
            sheet.write(sheet_iterator, 5, math.ceil(mpHistory.proposedSellingPrice))
1054 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
1055 
            sheet.write(sheet_iterator, 5, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
1056 
        sheet_iterator+=1
1057 
 
1058 
    filename = "/tmp/snapdeal-auto-" + str(timestamp) + ".xls"
1059 
    wbk.save(filename)
1060 
    EmailAttachmentSender.mail("cnc.center@shop2020.in", "5h0p2o2o", [], "Snapdeal Auto Pricing for time " + str(timestamp), "Please find attached details.", [get_attachment_part(filename)], [""], [])
9881 kshitij.so 1061 
 
1062 
def commitExceptionList(exceptionList,timestamp):
9949 kshitij.so 1063 
    exceptionItems=[]
9881 kshitij.so 1064 
    for item in exceptionList:
1065 
        mpHistory = MarketPlaceHistory()
1066 
        mpHistory.item_id =item.item_id
1067 
        mpHistory.source = OrderSource.SNAPDEAL
1068 
        mpHistory.competitiveCategory = CompetitionCategory.EXCEPTION
1069 
        mpHistory.risky = item.risky
1070 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1071 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(item.runType)
1072 
        exceptionItems.append(mpHistory)
9881 kshitij.so 1073 
    session.commit()
9949 kshitij.so 1074 
    return exceptionItems
9881 kshitij.so 1075 
 
1076 
def commitNegativeMargin(negativeMargin,timestamp):
9949 kshitij.so 1077 
    negativeMarginItems = []
9881 kshitij.so 1078 
    for item in negativeMargin:
1079 
        snapdealDetails = item[0]
1080 
        snapdealItemInfo = item[1]
1081 
        snapdealPricing = item[2]
1082 
        mpHistory = MarketPlaceHistory()
1083 
        mpHistory.item_id = snapdealItemInfo.item_id
1084 
        mpHistory.source = OrderSource.SNAPDEAL
1085 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1086 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1087 
        mpHistory.ourTp = snapdealPricing.ourTp
9919 kshitij.so 1088 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
9881 kshitij.so 1089 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1090 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1091 
        mpHistory.otherInventory = snapdealDetails.otherInventory
1092 
        mpHistory.ourRank = snapdealDetails.rank
9919 kshitij.so 1093 
        mpHistory.risky = snapdealItemInfo.risky
1094 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1095 
        mpHistory.competitiveCategory = CompetitionCategory.NEGATIVE_MARGIN
1096 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1097 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1098 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1099 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1100 
        negativeMarginItems.append(mpHistory)
9881 kshitij.so 1101 
    session.commit()
9949 kshitij.so 1102 
    return negativeMarginItems
9881 kshitij.so 1103 
 
1104 
def commitCompetitive(competitive,timestamp):
9949 kshitij.so 1105 
    competitiveItems = []
9881 kshitij.so 1106 
    for item in competitive:
1107 
        snapdealDetails = item[0]
1108 
        snapdealItemInfo = item[1]
1109 
        snapdealPricing = item[2]
1110 
        mpItem = item[3]
1111 
        mpHistory = MarketPlaceHistory()
1112 
        mpHistory.item_id = snapdealItemInfo.item_id
1113 
        mpHistory.source = OrderSource.SNAPDEAL
1114 
        mpHistory.lowestTp = snapdealPricing.lowestTp
1115 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
1116 
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
1117 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1118 
        mpHistory.otherInventory = snapdealDetails.otherInventory
1119 
        mpHistory.ourRank = snapdealDetails.rank
1120 
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
1121 
        mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE
1122 
        mpHistory.risky = snapdealItemInfo.risky
1123 
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
1124 
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
1125 
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
1126 
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
1127 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1128 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1129 
        mpHistory.ourTp = snapdealPricing.ourTp
1130 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1131 
        if (snapdealPricing.competitionBasis=='SP'):
1132 
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
1133 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
1134 
            mpHistory.proposedSellingPrice = proposed_sp
1135 
            mpHistory.proposedTp = proposed_tp
1136 
        else:
1137 
            proposed_tp  = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
1138 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
1139 
            mpHistory.proposedSellingPrice = proposed_sp
1140 
            mpHistory.proposedTp = proposed_tp
9919 kshitij.so 1141 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1142 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1143 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1144 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
1145 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1146 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1147 
        competitiveItems.append(mpHistory)
9881 kshitij.so 1148 
    session.commit()
9949 kshitij.so 1149 
    return competitiveItems
9881 kshitij.so 1150 
 
1151 
def commitCompetitiveNoInventory(competitiveNoInventory,timestamp):
9949 kshitij.so 1152 
    competitiveNoInventoryItems = []
9881 kshitij.so 1153 
    for item in competitiveNoInventory:
1154 
        snapdealDetails = item[0]
1155 
        snapdealItemInfo = item[1]
1156 
        snapdealPricing = item[2]
1157 
        mpItem = item[3]
1158 
        mpHistory = MarketPlaceHistory()
1159 
        mpHistory.item_id = snapdealItemInfo.item_id
1160 
        mpHistory.source = OrderSource.SNAPDEAL
1161 
        mpHistory.lowestTp = snapdealPricing.lowestTp
1162 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
1163 
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
1164 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1165 
        mpHistory.otherInventory = snapdealDetails.otherInventory
1166 
        mpHistory.ourRank = snapdealDetails.rank
1167 
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
1168 
        mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE_NO_INVENTORY
1169 
        mpHistory.risky = snapdealItemInfo.risky
1170 
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
1171 
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
1172 
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
1173 
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
1174 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1175 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1176 
        mpHistory.ourTp = snapdealPricing.ourTp
1177 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1178 
        if (snapdealPricing.competitionBasis=='SP'):
1179 
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
1180 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
1181 
            mpHistory.proposedSellingPrice = proposed_sp
1182 
            mpHistory.proposedTp = proposed_tp
1183 
        else:
1184 
            proposed_tp  = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
1185 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
1186 
            mpHistory.proposedSellingPrice = proposed_sp
1187 
            mpHistory.proposedTp = proposed_tp
9919 kshitij.so 1188 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1189 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1190 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1191 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
1192 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1193 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1194 
        competitiveNoInventoryItems.append(mpHistory)
9881 kshitij.so 1195 
    session.commit()
9949 kshitij.so 1196 
    return competitiveNoInventoryItems
9881 kshitij.so 1197 
 
1198 
def commitCantCompete(cantCompete,timestamp):
9949 kshitij.so 1199 
    cantComepeteItems = []
9881 kshitij.so 1200 
    for item in cantCompete:
1201 
        snapdealDetails = item[0]
1202 
        snapdealItemInfo = item[1]
1203 
        snapdealPricing = item[2]
1204 
        mpItem = item[3]
1205 
        mpHistory = MarketPlaceHistory()
1206 
        mpHistory.item_id = snapdealItemInfo.item_id
1207 
        mpHistory.source = OrderSource.SNAPDEAL
1208 
        mpHistory.lowestTp = snapdealPricing.lowestTp
1209 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
1210 
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
1211 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1212 
        mpHistory.otherInventory = snapdealDetails.otherInventory
1213 
        mpHistory.ourRank = snapdealDetails.rank
1214 
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
1215 
        mpHistory.competitiveCategory = CompetitionCategory.CANT_COMPETE
1216 
        mpHistory.risky = snapdealItemInfo.risky
1217 
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
1218 
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
1219 
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
1220 
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
1221 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1222 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1223 
        mpHistory.ourTp = snapdealPricing.ourTp
1224 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1225 
        if (snapdealPricing.competitionBasis=='SP'):
1226 
            proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
1227 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
1228 
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
1229 
            mpHistory.proposedSellingPrice = proposed_sp
1230 
            mpHistory.proposedTp = proposed_tp
1231 
            mpHistory.targetNlc = target_nlc
1232 
        else:
1233 
            proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
1234 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
1235 
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
1236 
            mpHistory.proposedSellingPrice = proposed_sp
1237 
            mpHistory.proposedTp = proposed_tp
1238 
            mpHistory.targetNlc = target_nlc
9919 kshitij.so 1239 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1240 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1241 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1242 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
1243 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1244 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1245 
        cantComepeteItems.append(mpHistory)
9881 kshitij.so 1246 
    session.commit()
9949 kshitij.so 1247 
    return cantComepeteItems
9881 kshitij.so 1248 
 
1249 
def commitBuyBox(buyBoxItems,timestamp):
9949 kshitij.so 1250 
    buyBoxList = []
9881 kshitij.so 1251 
    for item in buyBoxItems:
1252 
        snapdealDetails = item[0]
1253 
        snapdealItemInfo = item[1]
1254 
        snapdealPricing = item[2]
1255 
        mpItem = item[3]
1256 
        mpHistory = MarketPlaceHistory()
1257 
        mpHistory.item_id = snapdealItemInfo.item_id
1258 
        mpHistory.source = OrderSource.SNAPDEAL
1259 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
1260 
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
1261 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1262 
        mpHistory.secondLowestInventory = snapdealDetails.secondLowestSellerInventory
1263 
        mpHistory.ourRank = snapdealDetails.rank
1264 
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
1265 
        mpHistory.competitiveCategory = CompetitionCategory.BUY_BOX
1266 
        mpHistory.risky = snapdealItemInfo.risky
1267 
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
1268 
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
1269 
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
1270 
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
1271 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1272 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1273 
        mpHistory.ourTp = snapdealPricing.ourTp
1274 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1275 
        mpHistory.secondLowestSellerName = snapdealDetails.secondLowestSellerName
1276 
        mpHistory.secondLowestSellerCode = snapdealDetails.secondLowestSellerCode
9885 kshitij.so 1277 
        mpHistory.secondLowestSellingPrice = snapdealDetails.secondLowestSellerSp
9888 kshitij.so 1278 
        mpHistory.secondLowestOfferPrice = snapdealDetails.secondLowestSellerOfferPrice
9881 kshitij.so 1279 
        mpHistory.secondLowestTp = snapdealPricing.secondLowestSellerTp
1280 
        if (snapdealPricing.competitionBasis=='SP'):
1281 
            proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)), snapdealPricing.lowestPossibleSp)
1282 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
1283 
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
1284 
            mpHistory.proposedSellingPrice = proposed_sp
1285 
            mpHistory.proposedTp = proposed_tp
1286 
            #mpHistory.targetNlc = target_nlc
1287 
        else:
1288 
            proposed_tp  = max(snapdealPricing.secondLowestSellerTp - max((20, snapdealPricing.secondLowestSellerTp*0.002)), snapdealPricing.lowestPossibleTp)
1289 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
1290 
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
1291 
            mpHistory.proposedSellingPrice = proposed_sp
1292 
            mpHistory.proposedTp = proposed_tp
1293 
            #mpHistory.targetNlc = target_nlc
9919 kshitij.so 1294 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1295 
        mpHistory.marginIncreasedPotential = proposed_tp - snapdealPricing.ourTp
1296 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1297 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1298 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1299 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1300 
        buyBoxList.append(mpHistory)
9881 kshitij.so 1301 
    session.commit()
9949 kshitij.so 1302 
    return buyBoxList
9881 kshitij.so 1303 
 
1304 
 
1305 
def getOtherTp(snapdealDetails,val,spm):
1306 
    if val.parent_category==10011:
1307 
        commissionPercentage = spm.competitorCommissionAccessory
1308 
    else:
1309 
        commissionPercentage = spm.competitorCommissionOther
1310 
    if snapdealDetails.rank==1:
1311 
        return snapdealDetails.secondLowestSellerSp- snapdealDetails.secondLowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100));
1312 
    return snapdealDetails.lowestSp- snapdealDetails.lowestSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100));
1313 
 
1314 
def getLowestPossibleTp(snapdealDetails,val,spm,mpItem):
1315 
    if snapdealDetails.rank==0:
1316 
        return mpItem.minimumPossibleTp
1317 
    vat = (snapdealDetails.ourSp/(1+(val.vatRate/100))-(val.nlc/(1+(val.vatRate/100))))*(val.vatRate/100);
1318 
    inHouseCost = 15+vat+(mpItem.returnProvision/100)*snapdealDetails.ourSp+mpItem.otherCost;
1319 
    lowest_possible_tp = val.nlc+inHouseCost;
1320 
    return lowest_possible_tp
1321 
 
1322 
def getOurTp(snapdealDetails,val,spm,mpItem):
1323 
    if snapdealDetails.rank==0:
1324 
        return mpItem.currentTp
1325 
    return snapdealDetails.ourSp- snapdealDetails.ourSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100));
1326 
 
1327 
def getLowestPossibleSp(snapdealDetails,val,spm,mpItem):
1328 
    if snapdealDetails.rank==0:
1329 
        return mpItem.minimumPossibleSp
1330 
    return (val.nlc+(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(val.vatRate/100))+(15+mpItem.otherCost)*(1+(val.vatRate)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(val.vatRate)/100)-(mpItem.returnProvision/100)*(1+(val.vatRate)/100));
1331 
 
1332 
def getTargetTp(targetSp,mpItem):
1333 
    return targetSp- targetSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100));
1334 
 
1335 
def getTargetSp(targetTp,mpItem):
9897 kshitij.so 1336 
    return float(targetTp+(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100)))/(1-((mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))))
9881 kshitij.so 1337 
 
1338 
def getSalesPotential(lowestOfferPrice,ourNlc):
1339 
    if lowestOfferPrice - ourNlc < 0:
1340 
        return 'HIGH'
9897 kshitij.so 1341 
    elif (float(lowestOfferPrice - ourNlc))/lowestOfferPrice >=0 and (float(lowestOfferPrice - ourNlc))/lowestOfferPrice <=.02:
9881 kshitij.so 1342 
        return 'MEDIUM'
1343 
    else:
1344 
        return 'LOW'
1345 
 
1346 
def main():
9949 kshitij.so 1347 
    parser = optparse.OptionParser()
1348 
    parser.add_option("-t", "--type", dest="runType",
1349 
                   default="FULL", type="string",
1350 
                   help="Run type FULL or FAVOURITE")
1351 
    (options, args) = parser.parse_args()
1352 
    if options.runType not in ('FULL','FAVOURITE'):
1353 
        print "Run type argument illegal."
1354 
        sys.exit(1)
1355 
    itemInfo,spm= populateStuff(options.runType)
9881 kshitij.so 1356 
    cantCompete, buyBoxItems, competitive, \
9949 kshitij.so 1357 
    competitiveNoInventory, exceptionList, negativeMargin = decideCategory(itemInfo,spm)
9881 kshitij.so 1358 
    timestamp = datetime.now()
9949 kshitij.so 1359 
    exceptionItems = commitExceptionList(exceptionList,timestamp)
1360 
    cantComepeteItems = commitCantCompete(cantCompete,timestamp)
1361 
    buyBoxList = commitBuyBox(buyBoxItems,timestamp)
1362 
    competitiveItems = commitCompetitive(competitive,timestamp)
1363 
    competitiveNoInventoryItems = commitCompetitiveNoInventory(competitiveNoInventory,timestamp)
1364 
    negativeMarginItems = commitNegativeMargin(negativeMargin,timestamp)
9897 kshitij.so 1365 
    fetchItemsForAutoDecrease(timestamp)
1366 
    fetchItemsForAutoIncrease(timestamp)
9949 kshitij.so 1367 
    if options.runType=='FULL':
1368 
        previousAutoFav, nowAutoFav = markAutoFavourite()
1369 
    writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, previousAutoFav, nowAutoFav,timestamp)
9897 kshitij.so 1370 
 
9881 kshitij.so 1371 
if __name__ == '__main__':
1372 
    main()