Subversion Repositories SmartDukaan

Rev

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