Subversion Repositories SmartDukaan

Rev

Rev 10097 | Rev 10219 | 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 *
9990 kshitij.so 2 
from sqlalchemy.sql import or_ ,func, asc
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, \
10097 kshitij.so 6 
Category, SourcePercentageMaster, MarketPlaceHistory, MarketPlaceUpdateHistory, MarketPlaceItemPrice
9881 kshitij.so 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
10008 kshitij.so 115 
        if math.ceil(autoDecrementItem.proposedSellingPrice) >= autoDecrementItem.ourSellingPrice:
116 
            markReasonForMpItem(autoDecrementItem,'Proposed SP greater than or equal to 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
9990 kshitij.so 196 
        antecedentPrice = session.query(MarketPlaceHistory.ourSellingPrice).filter(MarketPlaceHistory.item_id==autoIncrementItem.item_id).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.timestamp>time-timedelta(days=1)).order_by(asc(MarketPlaceHistory.timestamp)).first()
197 
        if antecedentPrice is not None:
198 
            if float(math.ceil(autoIncrementItem.ourSellingPrice+max(10,.01*autoIncrementItem.ourSellingPrice))-math.ceil(antecedentPrice[0]+max(10,.01*antecedentPrice[0])))/math.ceil(antecedentPrice[0]+max(10,.01*antecedentPrice[0]))>.02:
199 
                markReasonForMpItem(autoIncrementItem,'Maximum price increase in last 24 hours should be 2%',Decision.AUTO_INCREMENT_FAILED)
200 
                continue
201 
        mpItem = MarketplaceItems.get_by(itemId=autoIncrementItem.item_id,source=OrderSource.SNAPDEAL)
202 
        if mpItem.maximumSellingPrice is not None and mpItem.maximumSellingPrice > 0:
203 
            if autoIncrementItem.ourSellingPrice+max(10,.01*autoIncrementItem.ourSellingPrice) > mpItem.maximumSellingPrice:
204 
                markReasonForMpItem(autoIncrementItem,'Price cannot exceed Maximum Selling Price',Decision.AUTO_INCREMENT_FAILED)
205 
                continue
9949 kshitij.so 206 
        #oosStatus = inventory_client.getOosStatusesForXDaysForItem(autoIncrementItem.item_id,0,3)
207 
        #count,sale,daysOfStock = 0,0,0
208 
        #for obj in oosStatus:
209 
        #    if not obj.is_oos:
210 
        #        count+=1
211 
        #        sale = sale+obj.num_orders
212 
        #avgSalePerDay=0 if count==0 else (float(sale)/count)
9897 kshitij.so 213 
        totalAvailability, totalReserved = 0,0
9920 kshitij.so 214 
        if (not inventoryMap.has_key(autoIncrementItem.item_id)):
9949 kshitij.so 215 
            markReasonForMpItem(autoIncrementItem,'Inventory info not available',Decision.AUTO_INCREMENT_FAILED)
9913 kshitij.so 216 
            continue
9920 kshitij.so 217 
        itemInventory=inventoryMap[autoIncrementItem.item_id]
9897 kshitij.so 218 
        availableMap  = itemInventory.availability
219 
        reserveMap = itemInventory.reserved
220 
        for warehouse,availability in availableMap.iteritems():
221 
            if warehouse==16:
222 
                continue
223 
            totalAvailability = totalAvailability+availability
224 
        for warehouse,reserve in reserveMap.iteritems():
225 
            if warehouse==16:
226 
                continue
227 
            totalReserved = totalReserved+reserve
9949 kshitij.so 228 
        #if (totalAvailability-totalReserved)<=0:
229 
        #    markReasonForMpItem(autoIncrementItem,'Our stock is 0',Decision.AUTO_INCREMENT_FAILED)
230 
        #    continue
231 
        avgSalePerDay = (itemSaleMap.get(autoIncrementItem.item_id))[2]
9912 kshitij.so 232 
        if (avgSalePerDay==0):
9920 kshitij.so 233 
            markReasonForMpItem(autoIncrementItem,'Average sale per day is zero',Decision.AUTO_INCREMENT_FAILED)
9912 kshitij.so 234 
            continue
9897 kshitij.so 235 
        daysOfStock = (float(totalAvailability-totalReserved))/avgSalePerDay
236 
        if daysOfStock>5:
9920 kshitij.so 237 
            markReasonForMpItem(autoIncrementItem,'Our stock is enough',Decision.AUTO_INCREMENT_FAILED)
9897 kshitij.so 238 
            continue
239 
 
9920 kshitij.so 240 
        autoIncrementItem.ourEnoughStock = False
9949 kshitij.so 241 
        #autoIncrementItem.avgSales = avgSalePerDay
9920 kshitij.so 242 
        autoIncrementItem.decision = Decision.AUTO_INCREMENT_SUCCESS
243 
        autoIncrementItem.reason = 'All conditions for auto increment true'
9954 kshitij.so 244 
        successfulAutoIncrease.append(autoIncrementItem)
9920 kshitij.so 245 
        lgr.info("Auto increment success for itemId "+str(autoIncrementItem.item_id)+" Days of stock "+str(daysOfStock)+" avg sales "+str(avgSalePerDay))
9897 kshitij.so 246 
    session.commit()
9954 kshitij.so 247 
    return successfulAutoIncrease
9897 kshitij.so 248 
 
9949 kshitij.so 249 
def calculateAverageSale(oosStatus):
250 
    count,sale = 0,0
251 
    for obj in oosStatus:
252 
        if not obj.is_oos:
253 
            count+=1
254 
            sale = sale+obj.num_orders
255 
    avgSalePerDay=0 if count==0 else (float(sale)/count)
9954 kshitij.so 256 
    return round(avgSalePerDay,2)
9949 kshitij.so 257 
 
258 
def getNetAvailability(itemInventory):
259 
    totalAvailability, totalReserved = 0,0
260 
    availableMap  = itemInventory.availability
261 
    reserveMap = itemInventory.reserved
262 
    for warehouse,availability in availableMap.iteritems():
263 
        if warehouse==16:
264 
            continue
265 
        totalAvailability = totalAvailability+availability
266 
    for warehouse,reserve in reserveMap.iteritems():
267 
        if warehouse==16:
268 
            continue
269 
        totalReserved = totalReserved+reserve
270 
    return totalAvailability - totalReserved
271 
 
272 
def getOosString(oosStatus):
273 
    lastNdaySale=""
274 
    for obj in oosStatus:
275 
        if obj.is_oos:
276 
            lastNdaySale += "X-"
277 
        else:
278 
            lastNdaySale += str(obj.num_orders) + "-"
9954 kshitij.so 279 
    return lastNdaySale[:-1]
9949 kshitij.so 280 
 
9966 kshitij.so 281 
def getLastDaySale(itemId):
282 
    return (itemSaleMap.get(itemId))[4]
283 
 
9949 kshitij.so 284 
def markAutoFavourite():
285 
    previouslyAutoFav = []
286 
    nowAutoFav = []
287 
    marketplaceItems = session.query(MarketplaceItems).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()
288 
    fromDate = datetime.now()-timedelta(days = 3, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
289 
    toDate = datetime.now()-timedelta(days = 0, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
9954 kshitij.so 290 
    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 291 
    toUpdate = [key for key, value in itemSaleMap.items() if value[3] >= 3]
292 
    buyBoxLast3days = []
293 
    for item in items:
294 
        buyBoxLast3days.append(item[0])
295 
    for marketplaceItem in marketplaceItems:
296 
        reason = ""
297 
        toMark = False
298 
        if marketplaceItem.itemId in toUpdate:
299 
            toMark = True
300 
            reason+="Average sale is greater than 3 for last five days (Snapdeal)."
301 
        if marketplaceItem.itemId in buyBoxLast3days:
302 
            toMark = True
303 
            reason+="Item is present in buy box in last 3 days"
304 
        if not marketplaceItem.autoFavourite:
305 
            print "Item is not under auto favourite"
306 
        if toMark:
307 
            temp=[]
308 
            temp.append(marketplaceItem.itemId)
309 
            temp.append(reason)
310 
            nowAutoFav.append(temp)
311 
        if (not toMark) and marketplaceItem.autoFavourite:
312 
            previouslyAutoFav.append(marketplaceItem.itemId)
313 
        marketplaceItem.autoFavourite = toMark
314 
    session.commit()
315 
    return previouslyAutoFav, nowAutoFav
9897 kshitij.so 316 
 
9949 kshitij.so 317 
 
318 
 
9897 kshitij.so 319 
def markReasonForMpItem(mpHistory,reason,decision):
320 
    mpHistory.decision = decision
321 
    mpHistory.reason = reason
322 
 
9881 kshitij.so 323 
def fetchDetails(supc_code):
324 
    url="http://www.snapdeal.com/json/gvbps?supc=%s&catId=91"%(supc_code)
325 
    print url
326 
    time.sleep(1)
327 
    req = urllib2.Request(url)
328 
    response = urllib2.urlopen(req)
329 
    json_input = response.read()
330 
    vendorInfo = json.loads(json_input)
331 
    rank ,otherInventory ,ourInventory, ourOfferPrice, ourSp, iterator, secondLowestSellerSp, secondLowestSellerInventory, \
332 
    lowestOfferPrice,  secondLowestSellerOfferPrice = (0,)*10
333 
    lowestSellerName , lowestSellerCode, secondLowestSellerName, secondLowestSellerCode=('',)*4
334 
    for vendor in vendorInfo:
335 
        if iterator == 0:
336 
            lowestSellerName = vendor['vendorDisplayName']
337 
            lowestSellerCode = vendor['vendorCode']
338 
            try:
339 
                lowestSp = vendor['sellingPriceBefIntCashBack']
340 
            except:
341 
                lowestSp = vendor['sellingPrice']
342 
            lowestOfferPrice = vendor['sellingPrice']
343 
 
344 
        if iterator ==1:
345 
            secondLowestSellerName = vendor['vendorDisplayName']
346 
            secondLowestSellerCode =vendor['vendorCode']
347 
            try:
348 
                secondLowestSellerSp = vendor['sellingPriceBefIntCashBack']
349 
            except:
350 
                secondLowestSellerSp = vendor['sellingPrice']
351 
            secondLowestSellerOfferPrice = vendor['sellingPrice']
352 
            secondLowestSellerInventory = vendor['buyableInventory']
353 
 
354 
        if vendor['vendorDisplayName'] == 'MobilesnMore':
355 
            ourInventory = vendor['buyableInventory']
356 
            try:
357 
                ourSp = vendor['sellingPriceBefIntCashBack']
358 
            except:
359 
                ourSp = vendor['sellingPrice']
360 
            ourOfferPrice = vendor['sellingPrice']
361 
            rank = iterator +1
362 
        else:
363 
            if rank==0:
364 
                otherInventory = otherInventory +vendor['buyableInventory']
365 
        iterator+=1
366 
    snapdealDetails = __SnapdealDetails(ourSp,ourInventory,otherInventory,rank,lowestSellerName, lowestSellerCode,lowestSp,secondLowestSellerName,secondLowestSellerCode,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice,len(vendorInfo))
367 
    return snapdealDetails
368 
 
369 
 
9949 kshitij.so 370 
def populateStuff(runType):
9881 kshitij.so 371 
    itemInfo = []
9949 kshitij.so 372 
    if runType=='FAVOURITE':
373 
        items = session.query(SnapdealItem).join((MarketplaceItems,SnapdealItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).\
374 
        filter(or_(MarketplaceItems.autoFavourite==True, MarketplaceItems.manualFavourite==True)).all()
375 
    else:
376 
        items = session.query(SnapdealItem).all()
9881 kshitij.so 377 
    spm = SourcePercentageMaster.get_by(source=OrderSource.SNAPDEAL)
378 
    for snapdeal_item in items:
379 
        it = Item.query.filter_by(id=snapdeal_item.item_id).one()
380 
        category = Category.query.filter_by(id=it.category).one()
9949 kshitij.so 381 
        parent_category = Category.query.filter_by(id=category.parent_category_id).first()
382 
        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 383 
        itemInfo.append(snapdealItemInfo)
384 
    return itemInfo, spm
385 
 
386 
 
387 
def decideCategory(itemInfo,spm):
9949 kshitij.so 388 
    global itemSaleMap
9881 kshitij.so 389 
    cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin = [],[],[],[],[],[]
390 
    catalog_client = CatalogClient().get_client()
391 
    inventory_client = InventoryClient().get_client()
392 
 
393 
    for val in itemInfo:
394 
        try:
395 
            snapdealDetails = fetchDetails(val.supc)
396 
        except:
397 
            exceptionItems.append(val)
398 
            continue
399 
        mpItem = MarketplaceItems.get_by(itemId=val.item_id,source=OrderSource.SNAPDEAL)
400 
        warehouse = inventory_client.getWarehouse(val.warehouseId)
9949 kshitij.so 401 
 
402 
        itemSaleList = []
403 
        oosForAllSources = inventory_client.getOosStatusesForXDaysForItem(val.item_id, 0, 3)
404 
        oosForSnapdeal = inventory_client.getOosStatusesForXDaysForItem(val.item_id, OrderSource.SNAPDEAL, 5)
9966 kshitij.so 405 
        oosForSnapdealLastDay = inventory_client.getOosStatusesForXDaysForItem(val.item_id, OrderSource.SNAPDEAL, 1)
9949 kshitij.so 406 
        itemSaleList.append(oosForAllSources)
407 
        itemSaleList.append(oosForSnapdeal)
408 
        itemSaleList.append(calculateAverageSale(oosForAllSources))
409 
        itemSaleList.append(calculateAverageSale(oosForSnapdeal))
9966 kshitij.so 410 
        itemSaleList.append(calculateAverageSale(oosForSnapdealLastDay))
9949 kshitij.so 411 
        itemSaleMap[val.item_id]=itemSaleList
412 
 
9881 kshitij.so 413 
        if snapdealDetails.rank==0:
414 
            snapdealDetails.ourSp = mpItem.currentSp
415 
            snapdealDetails.ourOfferPrice = mpItem.currentSp
416 
            ourSp = mpItem.currentSp
417 
        else:
418 
            ourSp = snapdealDetails.ourSp
419 
        vatRate = catalog_client.getVatPercentageForItem(val.item_id, warehouse.stateId, snapdealDetails.ourSp)
420 
        val.vatRate = vatRate
421 
        if (snapdealDetails.rank==1):
422 
            temp=[]
423 
            temp.append(snapdealDetails)
424 
            temp.append(val)
425 
            if (snapdealDetails.secondLowestSellerOfferPrice == snapdealDetails.secondLowestSellerSp) and snapdealDetails.ourOfferPrice==snapdealDetails.ourSp:
426 
                competitionBasis = 'SP'
427 
            else:
428 
                competitionBasis = 'TP'
429 
            secondLowestTp=0 if snapdealDetails.totalSeller==1 else getOtherTp(snapdealDetails,val,spm)
430 
            snapdealPricing = __SnapdealPricing(snapdealDetails.ourSp,getOurTp(snapdealDetails,val,spm,mpItem),None,getLowestPossibleTp(snapdealDetails,val,spm,mpItem),competitionBasis,secondLowestTp,getLowestPossibleSp(snapdealDetails,val,spm,mpItem))
431 
            temp.append(snapdealPricing)
432 
            temp.append(mpItem)
433 
            buyBoxItems.append(temp)
434 
            continue
435 
 
436 
 
437 
        lowestTp = getOtherTp(snapdealDetails,val,spm)
438 
        ourTp = getOurTp(snapdealDetails,val,spm,mpItem)
439 
        lowestPossibleTp = getLowestPossibleTp(snapdealDetails,val,spm,mpItem)
440 
        lowestPossibleSp = getLowestPossibleSp(snapdealDetails,val,spm,mpItem)
441 
 
442 
        if (ourTp<lowestPossibleTp):
443 
            temp=[]
444 
            temp.append(snapdealDetails)
445 
            temp.append(val)
446 
            snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,None,None)
447 
            temp.append(snapdealPricing)
448 
            negativeMargin.append(temp)
449 
            continue
450 
 
451 
        if (snapdealDetails.lowestOfferPrice == snapdealDetails.lowestSp) and snapdealDetails.ourOfferPrice == snapdealDetails.ourSp:
452 
            competitionBasis ='SP'
453 
        else:
454 
            competitionBasis ='TP'
455 
 
456 
        if competitionBasis=='SP':
457 
            if snapdealDetails.lowestSp > lowestPossibleSp and snapdealDetails.ourInventory!=0:
458 
                temp=[]
459 
                temp.append(snapdealDetails)
460 
                temp.append(val)
461 
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'SP',None,lowestPossibleSp)
462 
                temp.append(snapdealPricing)
463 
                temp.append(mpItem)
464 
                competitive.append(temp)
465 
                continue
466 
        else:
467 
            if lowestTp > lowestPossibleTp and snapdealDetails.ourInventory!=0:
468 
                temp=[]
469 
                temp.append(snapdealDetails)
470 
                temp.append(val)
471 
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'TP',None,lowestPossibleSp)
472 
                temp.append(snapdealPricing)
473 
                temp.append(mpItem)
474 
                competitive.append(temp)
475 
                continue
476 
 
477 
        if competitionBasis=='SP':
478 
            if snapdealDetails.lowestSp > lowestPossibleSp and snapdealDetails.ourInventory==0:
479 
                temp=[]
480 
                temp.append(snapdealDetails)
481 
                temp.append(val)
482 
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'SP',None,lowestPossibleSp)
483 
                temp.append(snapdealPricing)
484 
                temp.append(mpItem)
485 
                competitiveNoInventory.append(temp)
486 
                continue
487 
        else:
488 
            if lowestTp > lowestPossibleTp and snapdealDetails.ourInventory==0:
489 
                temp=[]
490 
                temp.append(snapdealDetails)
491 
                temp.append(val)
492 
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'TP',None,lowestPossibleSp)
493 
                temp.append(snapdealPricing)
494 
                temp.append(mpItem)
495 
                competitiveNoInventory.append(temp)
496 
                continue
497 
 
498 
        temp=[]
499 
        temp.append(snapdealDetails)
500 
        temp.append(val)
501 
        snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,competitionBasis,None,lowestPossibleSp)
502 
        temp.append(snapdealPricing)
503 
        temp.append(mpItem)
504 
        cantCompete.append(temp)
505 
 
9949 kshitij.so 506 
    return cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin
507 
 
9953 kshitij.so 508 
def writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, previousAutoFav, nowAutoFav,timestamp,runType):
9949 kshitij.so 509 
    wbk = xlwt.Workbook()
510 
    sheet = wbk.add_sheet('Can\'t Compete')
511 
    xstr = lambda s: s or ""
512 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
513 
 
514 
    excel_integer_format = '0'
515 
    integer_style = xlwt.XFStyle()
516 
    integer_style.num_format_str = excel_integer_format
517 
 
518 
    sheet.write(0, 0, "Item ID", heading_xf)
519 
    sheet.write(0, 1, "Category", heading_xf)
520 
    sheet.write(0, 2, "Product Group.", heading_xf)
521 
    sheet.write(0, 3, "SUPC", heading_xf)
522 
    sheet.write(0, 4, "Brand", heading_xf)
523 
    sheet.write(0, 5, "Product Name", heading_xf)
524 
    sheet.write(0, 6, "Weight", heading_xf)
525 
    sheet.write(0, 7, "Courier Cost", heading_xf)
526 
    sheet.write(0, 8, "Risky", heading_xf)
527 
    sheet.write(0, 9, "Our SP", heading_xf)
528 
    sheet.write(0, 11, "Our TP", heading_xf)
529 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
530 
    sheet.write(0, 12, "Our Rank", heading_xf)
531 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
532 
    sheet.write(0, 14, "Lowest SP", heading_xf)
533 
    sheet.write(0, 15, "Lowest TP", heading_xf)
534 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
535 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
536 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
537 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
538 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
539 
    sheet.write(0, 21, "Average Sale", heading_xf)
540 
    sheet.write(0, 22, "Our NLC", heading_xf)
541 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
542 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
543 
    sheet.write(0, 25, "Competition Basis ", heading_xf)
544 
    sheet.write(0, 26, "Target TP", heading_xf)
545 
    sheet.write(0, 27, "Target SP", heading_xf)
546 
    sheet.write(0, 28, "Target NLC", heading_xf)
547 
    sheet.write(0, 29, "Sales Potential", heading_xf)
548 
    sheet_iterator = 1
549 
    for item in cantCompete:
550 
        snapdealDetails = item[0]
551 
        snapdealItemInfo = item[1]
552 
        snapdealPricing = item[2]
553 
        mpItem = item[3]
554 
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
555 
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
556 
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
557 
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
558 
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
559 
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
560 
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
561 
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
562 
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
563 
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
564 
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
565 
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
566 
        sheet.write(sheet_iterator, 12, snapdealDetails.rank)
567 
        sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
568 
        sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
569 
        sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
570 
        sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
571 
        sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
572 
        sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
573 
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
574 
            sheet.write(sheet_iterator, 19, 'Info not available')
575 
        else:
576 
            sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
577 
        sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
578 
        sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
579 
        sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
580 
        sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
581 
        sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
582 
        sheet.write(sheet_iterator, 25, snapdealPricing.competitionBasis)
583 
        if (snapdealPricing.competitionBasis=='SP'):
584 
            proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
585 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
586 
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
587 
        else:
588 
            proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
589 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
590 
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 591 
        sheet.write(sheet_iterator, 26, round(proposed_tp,2))
592 
        sheet.write(sheet_iterator, 27, round(proposed_sp,2))
593 
        sheet.write(sheet_iterator, 28, round(target_nlc,2))
9949 kshitij.so 594 
        sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
595 
        sheet_iterator+=1
596 
 
597 
    sheet = wbk.add_sheet('Lowest')
598 
 
599 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
600 
 
601 
    excel_integer_format = '0'
602 
    integer_style = xlwt.XFStyle()
603 
    integer_style.num_format_str = excel_integer_format
604 
    xstr = lambda s: s or ""
605 
 
606 
    sheet.write(0, 0, "Item ID", heading_xf)
607 
    sheet.write(0, 1, "Category", heading_xf)
608 
    sheet.write(0, 2, "Product Group.", heading_xf)
609 
    sheet.write(0, 3, "SUPC", heading_xf)
610 
    sheet.write(0, 4, "Brand", heading_xf)
611 
    sheet.write(0, 5, "Product Name", heading_xf)
612 
    sheet.write(0, 6, "Weight", heading_xf)
613 
    sheet.write(0, 7, "Courier Cost", heading_xf)
614 
    sheet.write(0, 8, "Risky", heading_xf)
615 
    sheet.write(0, 9, "Our SP", heading_xf)
616 
    sheet.write(0, 11, "Our TP", heading_xf)
617 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
618 
    sheet.write(0, 12, "Our Rank", heading_xf)
619 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
620 
    sheet.write(0, 14, "Second Lowest Seller", heading_xf)
621 
    sheet.write(0, 15, "Second Lowest Price", heading_xf)
622 
    sheet.write(0, 16, "Second Lowest Offer Price", heading_xf)
623 
    sheet.write(0, 17, "Second Lowest Seller TP", heading_xf)
624 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
625 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
626 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
627 
    sheet.write(0, 21, "Average Sale", heading_xf)
628 
    sheet.write(0, 22, "Second Lowest Seller Inventory", heading_xf)
629 
    sheet.write(0, 23, "Our NLC", heading_xf)
630 
    sheet.write(0, 24, "Competition Basis", heading_xf)
631 
    sheet.write(0, 25, "Target TP", heading_xf)
632 
    sheet.write(0, 26, "Target SP", heading_xf)
633 
    sheet.write(0, 27, "MARGIN INCREASED POTENTIAL", heading_xf)
634 
 
635 
    sheet_iterator = 1
636 
    for item in buyBoxItems:
637 
        snapdealDetails = item[0]
638 
        snapdealItemInfo = item[1]
639 
        snapdealPricing = item[2]
640 
        mpItem = item[3]
641 
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
642 
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
643 
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
644 
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
645 
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
646 
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
647 
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
648 
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
649 
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
650 
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
651 
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
652 
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
653 
        sheet.write(sheet_iterator, 12, snapdealDetails.rank)
654 
        sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
655 
        sheet.write(sheet_iterator, 14, snapdealDetails.secondLowestSellerName)
656 
        sheet.write(sheet_iterator, 15, snapdealDetails.secondLowestSellerSp)
657 
        sheet.write(sheet_iterator, 16, snapdealDetails.secondLowestSellerOfferPrice)
658 
        sheet.write(sheet_iterator, 17, snapdealPricing.secondLowestSellerTp)
659 
        sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
660 
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
661 
            sheet.write(sheet_iterator, 19, 'Info not available')
662 
        else:
663 
            sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
664 
        sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
665 
        sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
666 
        sheet.write(sheet_iterator, 22, snapdealDetails.secondLowestSellerInventory)
667 
        sheet.write(sheet_iterator, 23, snapdealItemInfo.nlc)
668 
        sheet.write(sheet_iterator, 24, snapdealPricing.competitionBasis)
669 
        if (snapdealPricing.competitionBasis=='SP'):
670 
            proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)), snapdealPricing.lowestPossibleSp)
671 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
672 
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
673 
        else:
674 
            proposed_tp  = max(snapdealPricing.secondLowestSellerTp - max((20, snapdealPricing.secondLowestSellerTp*0.002)), snapdealPricing.lowestPossibleTp)
675 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
676 
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 677 
        sheet.write(sheet_iterator, 25, round(proposed_tp,2))
678 
        sheet.write(sheet_iterator, 26, round(proposed_sp,2))
679 
        sheet.write(sheet_iterator, 27, round((proposed_tp - snapdealPricing.ourTp),2))
9949 kshitij.so 680 
        sheet_iterator+=1
681 
 
682 
    sheet = wbk.add_sheet('Can Compete-With Inventory')
683 
 
684 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
685 
 
686 
    excel_integer_format = '0'
687 
    integer_style = xlwt.XFStyle()
688 
    integer_style.num_format_str = excel_integer_format
689 
    xstr = lambda s: s or ""
690 
 
691 
    sheet.write(0, 0, "Item ID", heading_xf)
692 
    sheet.write(0, 1, "Category", heading_xf)
693 
    sheet.write(0, 2, "Product Group.", heading_xf)
694 
    sheet.write(0, 3, "SUPC", heading_xf)
695 
    sheet.write(0, 4, "Brand", heading_xf)
696 
    sheet.write(0, 5, "Product Name", heading_xf)
697 
    sheet.write(0, 6, "Weight", heading_xf)
698 
    sheet.write(0, 7, "Courier Cost", heading_xf)
699 
    sheet.write(0, 8, "Risky", heading_xf)
700 
    sheet.write(0, 9, "Our SP", heading_xf)
701 
    sheet.write(0, 11, "Our TP", heading_xf)
702 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
703 
    sheet.write(0, 12, "Our Rank", heading_xf)
704 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
705 
    sheet.write(0, 14, "Lowest SP", heading_xf)
706 
    sheet.write(0, 15, "Lowest TP", heading_xf)
707 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
708 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
709 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
710 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
711 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
712 
    sheet.write(0, 21, "Average Sale", heading_xf)
713 
    sheet.write(0, 22, "Our NLC", heading_xf)
714 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
715 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
716 
    sheet.write(0, 25, "Competition Basis ", heading_xf)
717 
    sheet.write(0, 26, "Target TP", heading_xf)
718 
    sheet.write(0, 27, "Target SP", heading_xf)
719 
    sheet.write(0, 28, "Sales Potential", heading_xf)
720 
 
721 
    sheet_iterator = 1
722 
    for item in competitive:
723 
        snapdealDetails = item[0]
724 
        snapdealItemInfo = item[1]
725 
        snapdealPricing = item[2]
726 
        mpItem = item[3]
727 
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
728 
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
729 
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
730 
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
731 
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
732 
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
733 
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
734 
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
735 
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
736 
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
737 
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
738 
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
739 
        sheet.write(sheet_iterator, 12, snapdealDetails.rank)
740 
        sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
741 
        sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
742 
        sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
743 
        sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
744 
        sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
745 
        sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
746 
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
747 
            sheet.write(sheet_iterator, 19, 'Info not available')
748 
        else:
749 
            sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
750 
        sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
751 
        sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
752 
        sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
753 
        sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
754 
        sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
755 
        sheet.write(sheet_iterator, 25, snapdealPricing.competitionBasis)
756 
        if (snapdealPricing.competitionBasis=='SP'):
757 
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
758 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
759 
        else:
760 
            proposed_tp  = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
761 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
9954 kshitij.so 762 
        sheet.write(sheet_iterator, 26, round(proposed_tp,2))
763 
        sheet.write(sheet_iterator, 27, round(proposed_sp,2))
9949 kshitij.so 764 
        sheet.write(sheet_iterator, 28, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
765 
        sheet_iterator+=1
766 
 
767 
    sheet = wbk.add_sheet('Negative Margin')
768 
 
769 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
770 
 
771 
    excel_integer_format = '0'
772 
    integer_style = xlwt.XFStyle()
773 
    integer_style.num_format_str = excel_integer_format
774 
    xstr = lambda s: s or ""
775 
 
776 
    sheet.write(0, 0, "Item ID", heading_xf)
777 
    sheet.write(0, 1, "Category", heading_xf)
778 
    sheet.write(0, 2, "Product Group.", heading_xf)
779 
    sheet.write(0, 3, "SUPC", heading_xf)
780 
    sheet.write(0, 4, "Brand", heading_xf)
781 
    sheet.write(0, 5, "Product Name", heading_xf)
782 
    sheet.write(0, 6, "Weight", heading_xf)
783 
    sheet.write(0, 7, "Courier Cost", heading_xf)
784 
    sheet.write(0, 8, "Risky", heading_xf)
785 
    sheet.write(0, 9, "Our SP", heading_xf)
786 
    sheet.write(0, 11, "Our TP", heading_xf)
787 
    sheet.write(0, 12, "Lowest Possible TP", heading_xf)
788 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
789 
    sheet.write(0, 13, "Our Rank", heading_xf)
790 
    sheet.write(0, 14, "Our Snapdeal Inventory", heading_xf)
791 
    sheet.write(0, 15, "Net Availability", heading_xf)
792 
    sheet.write(0, 16, "Last Five Day Sale", heading_xf)
793 
    sheet.write(0, 17, "Average Sale", heading_xf)
794 
    sheet.write(0, 18, "Our NLC", heading_xf)
795 
    sheet.write(0, 19, "Margin", heading_xf)
796 
 
797 
    sheet_iterator=1
798 
    for item in negativeMargin:
799 
        snapdealDetails = item[0]
800 
        snapdealItemInfo = item[1]
801 
        snapdealPricing = item[2]
802 
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
803 
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
804 
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
805 
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
806 
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
807 
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
808 
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
809 
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
810 
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
811 
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
812 
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
813 
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
814 
        sheet.write(sheet_iterator, 12, snapdealPricing.lowestPossibleTp)
815 
        sheet.write(sheet_iterator, 13, snapdealDetails.rank)
816 
        sheet.write(sheet_iterator, 14, snapdealDetails.ourInventory)
817 
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
818 
            sheet.write(sheet_iterator, 15, 'Info not available')
819 
        else:
820 
            sheet.write(sheet_iterator, 15, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
821 
        sheet.write(sheet_iterator, 16, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
822 
        sheet.write(sheet_iterator, 17, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
823 
        sheet.write(sheet_iterator, 18, snapdealItemInfo.nlc)
9954 kshitij.so 824 
        sheet.write(sheet_iterator, 19, round((snapdealPricing.ourTp - snapdealPricing.lowestPossibleTp),2))
9949 kshitij.so 825 
        sheet_iterator+=1
826 
 
827 
    sheet = wbk.add_sheet('Exception Item List')
828 
 
829 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
830 
 
831 
    excel_integer_format = '0'
832 
    integer_style = xlwt.XFStyle()
833 
    integer_style.num_format_str = excel_integer_format
834 
    xstr = lambda s: s or ""
835 
 
836 
    sheet.write(0, 0, "Item ID", heading_xf)
837 
    sheet.write(0, 1, "Brand", heading_xf)
838 
    sheet.write(0, 2, "Product Name", heading_xf)
839 
    sheet.write(0, 3, "Reason", heading_xf)
840 
    sheet_iterator=1
841 
    for item in exceptionList:
842 
        sheet.write(sheet_iterator, 0, item.item_id)
843 
        sheet.write(sheet_iterator, 1, item.brand)
844 
        sheet.write(sheet_iterator, 2, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
845 
        sheet.write(sheet_iterator, 3, "Unable to fetch info from Snapdeal")
846 
        sheet_iterator+=1
847 
 
848 
    sheet = wbk.add_sheet('Can Compete-No Inv')
849 
 
850 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
851 
 
852 
    excel_integer_format = '0'
853 
    integer_style = xlwt.XFStyle()
854 
    integer_style.num_format_str = excel_integer_format
855 
    xstr = lambda s: s or ""
856 
 
857 
    sheet.write(0, 0, "Item ID", heading_xf)
858 
    sheet.write(0, 1, "Category", heading_xf)
859 
    sheet.write(0, 2, "Product Group.", heading_xf)
860 
    sheet.write(0, 3, "SUPC", heading_xf)
861 
    sheet.write(0, 4, "Brand", heading_xf)
862 
    sheet.write(0, 5, "Product Name", heading_xf)
863 
    sheet.write(0, 6, "Weight", heading_xf)
864 
    sheet.write(0, 7, "Courier Cost", heading_xf)
865 
    sheet.write(0, 8, "Risky", heading_xf)
866 
    sheet.write(0, 9, "Our SP", heading_xf)
867 
    sheet.write(0, 11, "Our TP", heading_xf)
868 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
869 
    sheet.write(0, 12, "Our Rank", heading_xf)
870 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
871 
    sheet.write(0, 14, "Lowest SP", heading_xf)
872 
    sheet.write(0, 15, "Lowest TP", heading_xf)
873 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
874 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
875 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
876 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
877 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
878 
    sheet.write(0, 21, "Average Sale", heading_xf)
879 
    sheet.write(0, 22, "Our NLC", heading_xf)
880 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
881 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
882 
    sheet.write(0, 25, "Competition Basis ", heading_xf)
883 
    sheet.write(0, 26, "Target TP", heading_xf)
884 
    sheet.write(0, 27, "Target SP", heading_xf)
885 
    sheet.write(0, 28, "Target NLC", heading_xf)
886 
    sheet.write(0, 29, "Sales Potential", heading_xf)
887 
 
888 
    sheet_iterator = 1
889 
    for item in competitiveNoInventory:
890 
        snapdealDetails = item[0]
891 
        snapdealItemInfo = item[1]
892 
        snapdealPricing = item[2]
893 
        mpItem = item[3]
894 
        if ((not inventoryMap.has_key(snapdealItemInfo.item_id)) or getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id))<=0):
895 
            sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
896 
            sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
897 
            sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
898 
            sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
899 
            sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
900 
            sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
901 
            sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
902 
            sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
903 
            sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
904 
            sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
905 
            sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
906 
            sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
907 
            sheet.write(sheet_iterator, 12, snapdealDetails.rank)
908 
            sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
909 
            sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
910 
            sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
911 
            sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
912 
            sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
913 
            sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
914 
            if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
915 
                sheet.write(sheet_iterator, 19, 'Info not available')
916 
            else:
917 
                sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
918 
            sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
919 
            sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
920 
            sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
921 
            sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
922 
            sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
923 
            sheet.write(sheet_iterator, 25, snapdealPricing.competitionBasis)
924 
            if (snapdealPricing.competitionBasis=='SP'):
925 
                proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
926 
                proposed_tp = getTargetTp(proposed_sp,mpItem)
927 
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
928 
            else:
929 
                proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
930 
                proposed_sp = getTargetSp(proposed_tp,mpItem)
931 
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 932 
            sheet.write(sheet_iterator, 26, round(proposed_tp,2))
933 
            sheet.write(sheet_iterator, 27, round(proposed_sp,2))
934 
            sheet.write(sheet_iterator, 28, round(target_nlc,2))
9949 kshitij.so 935 
            sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
936 
            sheet_iterator+=1
937 
 
938 
    sheet = wbk.add_sheet('Can Compete-No Inv On SD')
939 
 
940 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
941 
 
942 
    excel_integer_format = '0'
943 
    integer_style = xlwt.XFStyle()
944 
    integer_style.num_format_str = excel_integer_format
945 
    xstr = lambda s: s or ""
946 
 
947 
    sheet.write(0, 0, "Item ID", heading_xf)
948 
    sheet.write(0, 1, "Category", heading_xf)
949 
    sheet.write(0, 2, "Product Group.", heading_xf)
950 
    sheet.write(0, 3, "SUPC", heading_xf)
951 
    sheet.write(0, 4, "Brand", heading_xf)
952 
    sheet.write(0, 5, "Product Name", heading_xf)
953 
    sheet.write(0, 6, "Weight", heading_xf)
954 
    sheet.write(0, 7, "Courier Cost", heading_xf)
955 
    sheet.write(0, 8, "Risky", heading_xf)
956 
    sheet.write(0, 9, "Our SP", heading_xf)
957 
    sheet.write(0, 11, "Our TP", heading_xf)
958 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
959 
    sheet.write(0, 12, "Our Rank", heading_xf)
960 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
961 
    sheet.write(0, 14, "Lowest SP", heading_xf)
962 
    sheet.write(0, 15, "Lowest TP", heading_xf)
963 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
964 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
965 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
966 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
967 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
968 
    sheet.write(0, 21, "Average Sale", heading_xf)
969 
    sheet.write(0, 22, "Our NLC", heading_xf)
970 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
971 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
972 
    sheet.write(0, 25, "Competition Basis ", heading_xf)
973 
    sheet.write(0, 26, "Target TP", heading_xf)
974 
    sheet.write(0, 27, "Target SP", heading_xf)
975 
    sheet.write(0, 28, "Target NLC", heading_xf)
976 
    sheet.write(0, 29, "Sales Potential", heading_xf)
977 
 
978 
    sheet_iterator = 1
979 
    for item in competitiveNoInventory:
980 
        snapdealDetails = item[0]
981 
        snapdealItemInfo = item[1]
982 
        snapdealPricing = item[2]
983 
        mpItem = item[3]
984 
        if (inventoryMap.has_key(snapdealItemInfo.item_id) and getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id))>0):
985 
            sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
986 
            sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
987 
            sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
988 
            sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
989 
            sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
990 
            sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
991 
            sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
992 
            sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
993 
            sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
994 
            sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
995 
            sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
996 
            sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
997 
            sheet.write(sheet_iterator, 12, snapdealDetails.rank)
998 
            sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
999 
            sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
1000 
            sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
1001 
            sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
1002 
            sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
1003 
            sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
1004 
            if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
1005 
                sheet.write(sheet_iterator, 19, 'Info not available')
1006 
            else:
1007 
                sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
1008 
            sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
1009 
            sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
1010 
            sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
1011 
            sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
1012 
            sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
1013 
            sheet.write(sheet_iterator, 25, snapdealPricing.competitionBasis)
1014 
            if (snapdealPricing.competitionBasis=='SP'):
1015 
                proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
1016 
                proposed_tp = getTargetTp(proposed_sp,mpItem)
1017 
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
1018 
            else:
1019 
                proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
1020 
                proposed_sp = getTargetSp(proposed_tp,mpItem)
1021 
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 1022 
            sheet.write(sheet_iterator, 26, round(proposed_tp,2))
1023 
            sheet.write(sheet_iterator, 27, round(proposed_sp,2))
1024 
            sheet.write(sheet_iterator, 28, round(target_nlc,2))
9949 kshitij.so 1025 
            sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
1026 
            sheet_iterator+=1
9953 kshitij.so 1027 
 
1028 
    if (runType=='FULL'):
1029 
        sheet = wbk.add_sheet('Auto Favorites')
1030 
 
1031 
        heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1032 
 
1033 
        excel_integer_format = '0'
1034 
        integer_style = xlwt.XFStyle()
1035 
        integer_style.num_format_str = excel_integer_format
1036 
        xstr = lambda s: s or ""
1037 
 
1038 
        sheet.write(0, 0, "Item ID", heading_xf)
1039 
        sheet.write(0, 1, "Brand", heading_xf)
1040 
        sheet.write(0, 2, "Product Name", heading_xf)
1041 
        sheet.write(0, 3, "Auto Favourite", heading_xf)
1042 
        sheet.write(0, 4, "Reason", heading_xf)
1043 
 
1044 
        sheet_iterator=1
1045 
        for autoFav in nowAutoFav:
1046 
            itemId = autoFav[0]
1047 
            reason = autoFav[1]
1048 
            it = Item.query.filter_by(id=itemId).one()
1049 
            sheet.write(sheet_iterator, 0, itemId)
1050 
            sheet.write(sheet_iterator, 1, it.brand)
1051 
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1052 
            sheet.write(sheet_iterator, 3, "True")
1053 
            sheet.write(sheet_iterator, 4, reason)
1054 
            sheet_iterator+=1
1055 
        for prevFav in previousAutoFav:
1056 
            it = Item.query.filter_by(id=prevFav).one()
1057 
            sheet.write(sheet_iterator, 0, prevFav)
1058 
            sheet.write(sheet_iterator, 1, it.brand)
1059 
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1060 
            sheet.write(sheet_iterator, 3, "False")
1061 
            sheet_iterator+=1
9949 kshitij.so 1062 
 
1063 
 
1064 
    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()
1065 
    sheet = wbk.add_sheet('Auto Inc and Dec')
1066 
 
1067 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1068 
 
1069 
    excel_integer_format = '0'
1070 
    integer_style = xlwt.XFStyle()
1071 
    integer_style.num_format_str = excel_integer_format
1072 
    xstr = lambda s: s or ""
1073 
 
1074 
    sheet.write(0, 0, "Item ID", heading_xf)
1075 
    sheet.write(0, 1, "Brand", heading_xf)
1076 
    sheet.write(0, 2, "Product Name", heading_xf)
1077 
    sheet.write(0, 3, "Decision", heading_xf)
1078 
    sheet.write(0, 4, "Reason", heading_xf)
9954 kshitij.so 1079 
    sheet.write(0, 5, "Old Selling Price", heading_xf)
1080 
    sheet.write(0, 6, "Selling Price Updated",heading_xf)
9949 kshitij.so 1081 
 
1082 
    sheet_iterator=1
1083 
    for autoPricingItem in autoPricingItems:
1084 
        mpHistory = autoPricingItem[0]
1085 
        item = autoPricingItem[1]
9954 kshitij.so 1086 
        it = Item.query.filter_by(id=item.id).one()
9949 kshitij.so 1087 
        sheet.write(sheet_iterator, 0, item.id)
1088 
        sheet.write(sheet_iterator, 1, it.brand)
1089 
        sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1090 
        sheet.write(sheet_iterator, 3, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
1091 
        sheet.write(sheet_iterator, 4, mpHistory.reason)
1092 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
9954 kshitij.so 1093 
            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
1094 
            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.proposedSellingPrice))
9949 kshitij.so 1095 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
9954 kshitij.so 1096 
            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
1097 
            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
9949 kshitij.so 1098 
        sheet_iterator+=1
1099 
 
9953 kshitij.so 1100 
    filename = "/tmp/snapdeal-report-"+runType+" " + str(timestamp) + ".xls"
9949 kshitij.so 1101 
    wbk.save(filename)
10207 kshitij.so 1102 
    try:
1103 
        #EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Snapdeal Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
1104 
        EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["chandan.kumar@saholic.com","manoj.kumar@saholic.com","yukti.jain@saholic.com","ankush.dhingra@saholic.com","manoj.pal@saholic.com"], " Snapdeal Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], ["rajneesh.arora@saholic.com","rajveer.singh@saholic.com","vikram.raghav@saholic.com","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
1105 
    except Exception as e:
1106 
        print e
1107 
        print "Unable to send report"
9881 kshitij.so 1108 
 
1109 
def commitExceptionList(exceptionList,timestamp):
9949 kshitij.so 1110 
    exceptionItems=[]
9881 kshitij.so 1111 
    for item in exceptionList:
1112 
        mpHistory = MarketPlaceHistory()
1113 
        mpHistory.item_id =item.item_id
1114 
        mpHistory.source = OrderSource.SNAPDEAL
1115 
        mpHistory.competitiveCategory = CompetitionCategory.EXCEPTION
1116 
        mpHistory.risky = item.risky
1117 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1118 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(item.runType)
1119 
        exceptionItems.append(mpHistory)
9881 kshitij.so 1120 
    session.commit()
9949 kshitij.so 1121 
    return exceptionItems
9881 kshitij.so 1122 
 
1123 
def commitNegativeMargin(negativeMargin,timestamp):
9949 kshitij.so 1124 
    negativeMarginItems = []
9881 kshitij.so 1125 
    for item in negativeMargin:
1126 
        snapdealDetails = item[0]
1127 
        snapdealItemInfo = item[1]
1128 
        snapdealPricing = item[2]
1129 
        mpHistory = MarketPlaceHistory()
1130 
        mpHistory.item_id = snapdealItemInfo.item_id
1131 
        mpHistory.source = OrderSource.SNAPDEAL
1132 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1133 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1134 
        mpHistory.ourTp = snapdealPricing.ourTp
9919 kshitij.so 1135 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
9881 kshitij.so 1136 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1137 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1138 
        mpHistory.otherInventory = snapdealDetails.otherInventory
1139 
        mpHistory.ourRank = snapdealDetails.rank
9919 kshitij.so 1140 
        mpHistory.risky = snapdealItemInfo.risky
1141 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1142 
        mpHistory.competitiveCategory = CompetitionCategory.NEGATIVE_MARGIN
1143 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1144 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1145 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1146 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1147 
        negativeMarginItems.append(mpHistory)
9881 kshitij.so 1148 
    session.commit()
9949 kshitij.so 1149 
    return negativeMarginItems
9881 kshitij.so 1150 
 
1151 
def commitCompetitive(competitive,timestamp):
9949 kshitij.so 1152 
    competitiveItems = []
9881 kshitij.so 1153 
    for item in competitive:
1154 
        snapdealDetails = item[0]
1155 
        snapdealItemInfo = item[1]
1156 
        snapdealPricing = item[2]
1157 
        mpItem = item[3]
1158 
        mpHistory = MarketPlaceHistory()
1159 
        mpHistory.item_id = snapdealItemInfo.item_id
1160 
        mpHistory.source = OrderSource.SNAPDEAL
1161 
        mpHistory.lowestTp = snapdealPricing.lowestTp
1162 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
1163 
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
1164 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1165 
        mpHistory.otherInventory = snapdealDetails.otherInventory
1166 
        mpHistory.ourRank = snapdealDetails.rank
1167 
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
1168 
        mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE
1169 
        mpHistory.risky = snapdealItemInfo.risky
1170 
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
1171 
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
1172 
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
1173 
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
1174 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1175 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1176 
        mpHistory.ourTp = snapdealPricing.ourTp
1177 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1178 
        if (snapdealPricing.competitionBasis=='SP'):
1179 
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
1180 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
9954 kshitij.so 1181 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1182 
            mpHistory.proposedTp = round(proposed_tp,2)
9881 kshitij.so 1183 
        else:
1184 
            proposed_tp  = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
1185 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
9954 kshitij.so 1186 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1187 
            mpHistory.proposedTp = round(proposed_tp,2)
9919 kshitij.so 1188 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1189 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1190 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1191 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
1192 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1193 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1194 
        competitiveItems.append(mpHistory)
9881 kshitij.so 1195 
    session.commit()
9949 kshitij.so 1196 
    return competitiveItems
9881 kshitij.so 1197 
 
1198 
def commitCompetitiveNoInventory(competitiveNoInventory,timestamp):
9949 kshitij.so 1199 
    competitiveNoInventoryItems = []
9881 kshitij.so 1200 
    for item in competitiveNoInventory:
1201 
        snapdealDetails = item[0]
1202 
        snapdealItemInfo = item[1]
1203 
        snapdealPricing = item[2]
1204 
        mpItem = item[3]
1205 
        mpHistory = MarketPlaceHistory()
1206 
        mpHistory.item_id = snapdealItemInfo.item_id
1207 
        mpHistory.source = OrderSource.SNAPDEAL
1208 
        mpHistory.lowestTp = snapdealPricing.lowestTp
1209 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
1210 
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
1211 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1212 
        mpHistory.otherInventory = snapdealDetails.otherInventory
1213 
        mpHistory.ourRank = snapdealDetails.rank
1214 
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
1215 
        mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE_NO_INVENTORY
1216 
        mpHistory.risky = snapdealItemInfo.risky
1217 
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
1218 
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
1219 
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
1220 
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
1221 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1222 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1223 
        mpHistory.ourTp = snapdealPricing.ourTp
1224 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1225 
        if (snapdealPricing.competitionBasis=='SP'):
1226 
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
1227 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
9954 kshitij.so 1228 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1229 
            mpHistory.proposedTp = round(proposed_tp,2)
9881 kshitij.so 1230 
        else:
1231 
            proposed_tp  = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
1232 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
9954 kshitij.so 1233 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1234 
            mpHistory.proposedTp = round(proposed_tp,2)
9919 kshitij.so 1235 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1236 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1237 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1238 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
1239 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1240 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1241 
        competitiveNoInventoryItems.append(mpHistory)
9881 kshitij.so 1242 
    session.commit()
9949 kshitij.so 1243 
    return competitiveNoInventoryItems
9881 kshitij.so 1244 
 
1245 
def commitCantCompete(cantCompete,timestamp):
9949 kshitij.so 1246 
    cantComepeteItems = []
9881 kshitij.so 1247 
    for item in cantCompete:
1248 
        snapdealDetails = item[0]
1249 
        snapdealItemInfo = item[1]
1250 
        snapdealPricing = item[2]
1251 
        mpItem = item[3]
1252 
        mpHistory = MarketPlaceHistory()
1253 
        mpHistory.item_id = snapdealItemInfo.item_id
1254 
        mpHistory.source = OrderSource.SNAPDEAL
1255 
        mpHistory.lowestTp = snapdealPricing.lowestTp
1256 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
1257 
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
1258 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1259 
        mpHistory.otherInventory = snapdealDetails.otherInventory
1260 
        mpHistory.ourRank = snapdealDetails.rank
1261 
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
1262 
        mpHistory.competitiveCategory = CompetitionCategory.CANT_COMPETE
1263 
        mpHistory.risky = snapdealItemInfo.risky
1264 
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
1265 
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
1266 
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
1267 
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
1268 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1269 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1270 
        mpHistory.ourTp = snapdealPricing.ourTp
1271 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1272 
        if (snapdealPricing.competitionBasis=='SP'):
1273 
            proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
1274 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
1275 
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 1276 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1277 
            mpHistory.proposedTp = round(proposed_tp,2)
1278 
            mpHistory.targetNlc = round(target_nlc,2)
9881 kshitij.so 1279 
        else:
1280 
            proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
1281 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
1282 
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 1283 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1284 
            mpHistory.proposedTp = round(proposed_tp,2)
1285 
            mpHistory.targetNlc = round(target_nlc,2)
9919 kshitij.so 1286 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1287 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1288 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1289 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
1290 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1291 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1292 
        cantComepeteItems.append(mpHistory)
9881 kshitij.so 1293 
    session.commit()
9949 kshitij.so 1294 
    return cantComepeteItems
9881 kshitij.so 1295 
 
1296 
def commitBuyBox(buyBoxItems,timestamp):
9949 kshitij.so 1297 
    buyBoxList = []
9881 kshitij.so 1298 
    for item in buyBoxItems:
1299 
        snapdealDetails = item[0]
1300 
        snapdealItemInfo = item[1]
1301 
        snapdealPricing = item[2]
1302 
        mpItem = item[3]
1303 
        mpHistory = MarketPlaceHistory()
1304 
        mpHistory.item_id = snapdealItemInfo.item_id
1305 
        mpHistory.source = OrderSource.SNAPDEAL
1306 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
1307 
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
1308 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1309 
        mpHistory.secondLowestInventory = snapdealDetails.secondLowestSellerInventory
1310 
        mpHistory.ourRank = snapdealDetails.rank
1311 
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
1312 
        mpHistory.competitiveCategory = CompetitionCategory.BUY_BOX
1313 
        mpHistory.risky = snapdealItemInfo.risky
1314 
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
1315 
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
1316 
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
1317 
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
1318 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1319 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1320 
        mpHistory.ourTp = snapdealPricing.ourTp
1321 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1322 
        mpHistory.secondLowestSellerName = snapdealDetails.secondLowestSellerName
1323 
        mpHistory.secondLowestSellerCode = snapdealDetails.secondLowestSellerCode
9885 kshitij.so 1324 
        mpHistory.secondLowestSellingPrice = snapdealDetails.secondLowestSellerSp
9888 kshitij.so 1325 
        mpHistory.secondLowestOfferPrice = snapdealDetails.secondLowestSellerOfferPrice
9881 kshitij.so 1326 
        mpHistory.secondLowestTp = snapdealPricing.secondLowestSellerTp
1327 
        if (snapdealPricing.competitionBasis=='SP'):
1328 
            proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)), snapdealPricing.lowestPossibleSp)
1329 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
1330 
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 1331 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1332 
            mpHistory.proposedTp = round(proposed_tp,2)
9881 kshitij.so 1333 
            #mpHistory.targetNlc = target_nlc
1334 
        else:
1335 
            proposed_tp  = max(snapdealPricing.secondLowestSellerTp - max((20, snapdealPricing.secondLowestSellerTp*0.002)), snapdealPricing.lowestPossibleTp)
1336 
            proposed_sp = getTargetSp(proposed_tp,mpItem)
1337 
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 1338 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1339 
            mpHistory.proposedTp = round(proposed_tp,2)
9881 kshitij.so 1340 
            #mpHistory.targetNlc = target_nlc
9919 kshitij.so 1341 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1342 
        mpHistory.marginIncreasedPotential = proposed_tp - snapdealPricing.ourTp
1343 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1344 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1345 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1346 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1347 
        buyBoxList.append(mpHistory)
9881 kshitij.so 1348 
    session.commit()
9949 kshitij.so 1349 
    return buyBoxList
9990 kshitij.so 1350 
def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
1351 
    xstr = lambda s: s or ""
1352 
    catalog_client = CatalogClient().get_client()
1353 
    inventory_client = InventoryClient().get_client()
1354 
    message="""<html>
1355 
            <body>
1356 
            <h3>Auto Decrease Items</h3>
1357 
            <table border="1" style="width:100%;">
1358 
            <thead>
1359 
            <tr><th>Item Id</th>
1360 
            <th>Product Name</th>
1361 
            <th>Old Price</th>
1362 
            <th>New Price</th>
1363 
            <th>Old Margin</th>
1364 
            <th>New Margin</th>
1365 
            <th>Snapdeal Inventory</th>
1366 
            </tr></thead>
1367 
            <tbody>"""
1368 
    for item in successfulAutoDecrease:
1369 
        it = Item.query.filter_by(id=item.item_id).one()
1370 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
1371 
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
1372 
        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
1373 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
1374 
        newMargin = round(getNewOurTp(mpItem,item.proposedSellingPrice) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice))
1375 
        message+="""<tr>
1376 
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
1377 
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
1378 
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
1379 
                <td style="text-align:center">"""+str(math.ceil(item.proposedSellingPrice))+"""</td>
1380 
                <td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
1381 
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/item.proposedSellingPrice)*100,1))+"%)"+"""</td>
1382 
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
1383 
                </tr>"""
1384 
    message+="""</tbody></table><h3>Auto Increase Items</h3><table border="1" style="width:100%;">
1385 
            <thead>
1386 
            <tr><th>Item Id</th>
1387 
            <th>Product Name</th>
1388 
            <th>Old Price</th>
1389 
            <th>New Price</th>
1390 
            <th>Old Margin</th>
1391 
            <th>New Margin</th>
1392 
            <th>Snapdeal Inventory</th>
1393 
            </tr></thead>
1394 
            <tbody>"""
1395 
    for item in successfulAutoIncrease:
1396 
        it = Item.query.filter_by(id=item.item_id).one()
1397 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
1398 
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
1399 
        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
1400 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
1401 
        newMargin = round(getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
1402 
        message+="""<tr>
1403 
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
1404 
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
1405 
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
1406 
                <td style="text-align:center">"""+str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))+"""</td>
1407 
                <td style="text-align:center">"""+str(round((item.margin),1))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
1408 
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))*100,1))+"%)"+"""</td>
1409 
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
1410 
                </tr>"""
1411 
    message+="""</tbody></table></body></html>"""
1412 
    print message
1413 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
1414 
    mailServer.ehlo()
1415 
    mailServer.starttls()
1416 
    mailServer.ehlo()
1417 
 
10031 kshitij.so 1418 
    #recipients = ['kshitij.sood@saholic.com']
10005 kshitij.so 1419 
    recipients = ['rajneesh.arora@saholic.com','rajveer.singh@saholic.com','vikram.raghav@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
9990 kshitij.so 1420 
    msg = MIMEMultipart()
1421 
    msg['Subject'] = "Snapdeal Auto Pricing" + ' - ' + str(datetime.now())
1422 
    msg['From'] = ""
10005 kshitij.so 1423 
    msg['To'] = ",".join(recipients)
9990 kshitij.so 1424 
    msg.preamble = "Snapdeal Auto Pricing" + ' - ' + str(datetime.now())
1425 
    html_msg = MIMEText(message, 'html')
1426 
    msg.attach(html_msg)
10207 kshitij.so 1427 
    try:
1428 
        mailServer.login("build@shop2020.in", "cafe@nes")
1429 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
1430 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
1431 
    except Exception as e:
1432 
        print e
1433 
        print "Unable to send pricing mail"
9990 kshitij.so 1434 
 
1435 
def commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp):
1436 
    catalog_client = CatalogClient().get_client()
1437 
    inventory_client = InventoryClient().get_client()
1438 
    for item in successfulAutoDecrease:
1439 
        it = Item.query.filter_by(id=item.item_id).one()
1440 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
1441 
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
1442 
        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
1443 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
1444 
        addHistory(sdItem)
1445 
        sdItem.transferPrice = getNewOurTp(mpItem,item.proposedSellingPrice)
1446 
        sdItem.sellingPrice = math.ceil(item.proposedSellingPrice)
1447 
        sdItem.commission = round((mpItem.commission/100)*(sdItem.sellingPrice),2)
1448 
        sdItem.serviceTax = round((mpItem.serviceTax/100)*(sdItem.commission+sdItem.courierCost),2)
1449 
        sdItem.updatedOn = timestamp
1450 
        sdItem.priceUpdatedBy = 'SYSTEM'
1451 
        mpItem.currentSp = sdItem.sellingPrice
1452 
        mpItem.currentTp = sdItem.transferPrice
1453 
        mpItem.minimumPossibleTp = getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice)
1454 
        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,item.ourNlc,vatRate)
1455 
        markStatusForMarketplaceItems(sdItem,mpItem)
1456 
    session.commit()
1457 
    for item in successfulAutoIncrease:
1458 
        it = Item.query.filter_by(id=item.item_id).one()
1459 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
1460 
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
1461 
        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
1462 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
1463 
        addHistory(sdItem)
1464 
        sdItem.transferPrice = getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice))
1465 
        sdItem.sellingPrice = math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice))
1466 
        sdItem.commission = round((mpItem.commission/100)*(sdItem.sellingPrice),2)
1467 
        sdItem.serviceTax = round((mpItem.serviceTax/100)*(sdItem.commission+sdItem.courierCost),2)
1468 
        sdItem.updatedOn = timestamp
1469 
        sdItem.priceUpdatedBy = 'SYSTEM'
1470 
        mpItem.currentSp = sdItem.sellingPrice
1471 
        mpItem.currentTp = sdItem.transferPrice
1472 
        mpItem.minimumPossibleTp = getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,sdItem.sellingPrice)
1473 
        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,item.ourNlc,vatRate)
1474 
        markStatusForMarketplaceItems(sdItem,mpItem)
1475 
    session.commit()
1476 
 
1477 
def addHistory(item):
10097 kshitij.so 1478 
    itemHistory = MarketPlaceUpdateHistory()
9990 kshitij.so 1479 
    itemHistory.item_id = item.item_id
10097 kshitij.so 1480 
    itemHistory.source = OrderSource.SNAPDEAL
9990 kshitij.so 1481 
    itemHistory.exceptionPrice = item.exceptionPrice
1482 
    itemHistory.warehouseId = item.warehouseId
10097 kshitij.so 1483 
    itemHistory.isListedOnSource = item.isListedOnSnapdeal
9990 kshitij.so 1484 
    itemHistory.transferPrice = item.transferPrice
1485 
    itemHistory.sellingPrice = item.sellingPrice
1486 
    itemHistory.courierCost = item.courierCost
1487 
    itemHistory.commission = item.commission
1488 
    itemHistory.serviceTax = item.serviceTax
1489 
    itemHistory.suppressPriceFeed = item.suppressPriceFeed
1490 
    itemHistory.suppressInventoryFeed = item.suppressInventoryFeed
1491 
    itemHistory.updatedOn = item.updatedOn
1492 
    itemHistory.maxNlc = item.maxNlc
10097 kshitij.so 1493 
    itemHistory.skuAtSource = item.skuAtSnapdeal
1494 
    itemHistory.marketPlaceSerialNumber = item.supc
9990 kshitij.so 1495 
    itemHistory.priceUpdatedBy = item.priceUpdatedBy
1496 
 
1497 
def markStatusForMarketplaceItems(snapdealItem,marketplaceItem):
1498 
    markUpdatedItem = MarketPlaceItemPrice.query.filter(MarketPlaceItemPrice.item_id==snapdealItem.item_id).filter(MarketPlaceItemPrice.source==marketplaceItem.source).first()
1499 
    if markUpdatedItem is None:
1500 
        marketPlaceItemPrice = MarketPlaceItemPrice()
1501 
        marketPlaceItemPrice.item_id = snapdealItem.item_id
1502 
        marketPlaceItemPrice.source = marketplaceItem.source
1503 
        marketPlaceItemPrice.lastUpdatedOn = snapdealItem.updatedOn
1504 
        marketPlaceItemPrice.sellingPrice = snapdealItem.sellingPrice
1505 
        marketPlaceItemPrice.suppressPriceFeed = snapdealItem.suppressPriceFeed
1506 
        marketPlaceItemPrice.isListedOnSource = snapdealItem.isListedOnSnapdeal
1507 
    else:
1508 
        if (markUpdatedItem.sellingPrice!=snapdealItem.sellingPrice or markUpdatedItem.suppressPriceFeed!=snapdealItem.suppressPriceFeed or markUpdatedItem.isListedOnSource!=snapdealItem.isListedOnSnapdeal):
1509 
            markUpdatedItem.lastUpdatedOn = snapdealItem.updatedOn
1510 
        markUpdatedItem.sellingPrice = snapdealItem.sellingPrice
1511 
        markUpdatedItem.suppressPriceFeed = snapdealItem.suppressPriceFeed
1512 
        markUpdatedItem.isListedOnSource = snapdealItem.isListedOnSnapdeal
10031 kshitij.so 1513 
 
1514 
def processLostBuyBoxItems(previousProcessingTimestamp,currentTimestamp):
1515 
    previous_buy_box = session.query(MarketPlaceHistory.item_id).filter(MarketPlaceHistory.timestamp==previousProcessingTimestamp).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
1516 
    cant_compete = session.query(MarketPlaceHistory.item_id).filter(MarketPlaceHistory.timestamp==currentTimestamp).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
10032 kshitij.so 1517 
    lost_buy_box = list(set(list(zip(*previous_buy_box)[0]))&set(list(zip(*cant_compete)[0])))
10031 kshitij.so 1518 
    if len(lost_buy_box)==0:
1519 
        return
1520 
    xstr = lambda s: s or ""
1521 
    message="""<html>
1522 
            <body>
1523 
            <h3>Lost Buy Box</h3>
1524 
            <table border="1" style="width:100%;">
1525 
            <thead>
1526 
            <tr><th>Item Id</th>
1527 
            <th>Product Name</th>
1528 
            <th>Current Price</th>
1529 
            <th>Current TP</th>
1530 
            <th>Current Margin</th>
1531 
            <th>Competition TP</th>
1532 
            <th>Lowest Possible TP</th>
1533 
            <th>NLC</th>
1534 
            <th>Target NLC</th>
1535 
            <th>Snapdeal Inventory</th>
1536 
            <th>Total Inventory</th>
1537 
            <th>Sales History</th>
1538 
            </tr></thead>
1539 
            <tbody>"""
1540 
    items = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==currentTimestamp).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.item_id.in_(lost_buy_box)).all()
1541 
    for item in items:
1542 
        it = Item.query.filter_by(id=item.item_id).one()
1543 
        netInventory=''
1544 
        if not inventoryMap.has_key(item.item_id):
1545 
            netInventory='Info Not Available'
1546 
        else:
1547 
            netInventory = str(getNetAvailability(inventoryMap.get(item.item_id)))
1548 
        message+="""<tr>
1549 
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
1550 
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
1551 
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
1552 
                <td style="text-align:center">"""+str(item.ourTp)+"""</td>
1553 
                <td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
1554 
                <td style="text-align:center">"""+str(item.lowestTp)+"""</td>
1555 
                <td style="text-align:center">"""+str(item.lowestPossibleTp)+"""</td>
1556 
                <td style="text-align:center">"""+str(item.ourNlc)+"""</td>
1557 
                <td style="text-align:center">"""+str(item.targetNlc)+"""</td>
1558 
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
1559 
                <td style="text-align:center">"""+netInventory+"""</td>
1560 
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
1561 
                </tr>"""
1562 
    message+="""</tbody></table></body></html>"""
1563 
    print message
1564 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
1565 
    mailServer.ehlo()
1566 
    mailServer.starttls()
1567 
    mailServer.ehlo()
1568 
 
10033 kshitij.so 1569 
    #recipients = ['kshitij.sood@saholic.com']
1570 
    recipients = ['rajneesh.arora@saholic.com','rajveer.singh@saholic.com','vikram.raghav@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
10031 kshitij.so 1571 
    msg = MIMEMultipart()
1572 
    msg['Subject'] = "Snapdeal Lost Buy Box" + ' - ' + str(datetime.now())
1573 
    msg['From'] = ""
1574 
    msg['To'] = ",".join(recipients)
1575 
    msg.preamble = "Snapdeal Lost Buy Box" + ' - ' + str(datetime.now())
1576 
    html_msg = MIMEText(message, 'html')
1577 
    msg.attach(html_msg)
10207 kshitij.so 1578 
    try:
1579 
        mailServer.login("build@shop2020.in", "cafe@nes")
1580 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
1581 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
1582 
    except Exception as e:
1583 
        print e
1584 
        print "Unable to send lost buy bix mail"
10031 kshitij.so 1585 
 
9881 kshitij.so 1586 
 
1587 
 
1588 
def getOtherTp(snapdealDetails,val,spm):
1589 
    if val.parent_category==10011:
1590 
        commissionPercentage = spm.competitorCommissionAccessory
1591 
    else:
1592 
        commissionPercentage = spm.competitorCommissionOther
1593 
    if snapdealDetails.rank==1:
9954 kshitij.so 1594 
        otherTp = snapdealDetails.secondLowestSellerSp- snapdealDetails.secondLowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100));
1595 
        return round(otherTp,2)
1596 
    otherTp = snapdealDetails.lowestSp- snapdealDetails.lowestSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100));
1597 
    return round(otherTp,2)
9881 kshitij.so 1598 
 
1599 
def getLowestPossibleTp(snapdealDetails,val,spm,mpItem):
1600 
    if snapdealDetails.rank==0:
1601 
        return mpItem.minimumPossibleTp
1602 
    vat = (snapdealDetails.ourSp/(1+(val.vatRate/100))-(val.nlc/(1+(val.vatRate/100))))*(val.vatRate/100);
1603 
    inHouseCost = 15+vat+(mpItem.returnProvision/100)*snapdealDetails.ourSp+mpItem.otherCost;
1604 
    lowest_possible_tp = val.nlc+inHouseCost;
9954 kshitij.so 1605 
    return round(lowest_possible_tp,2)
9881 kshitij.so 1606 
 
1607 
def getOurTp(snapdealDetails,val,spm,mpItem):
1608 
    if snapdealDetails.rank==0:
1609 
        return mpItem.currentTp
9954 kshitij.so 1610 
    ourTp = snapdealDetails.ourSp- snapdealDetails.ourSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100));
1611 
    return round(ourTp,2)
9966 kshitij.so 1612 
 
1613 
def getNewLowestPossibleTp(mpItem,nlc,vatRate,proposedSellingPrice):
1614 
    vat = (proposedSellingPrice/(1+(vatRate/100))-(nlc/(1+(vatRate/100))))*(vatRate/100);
1615 
    inHouseCost = 15+vat+(mpItem.returnProvision/100)*proposedSellingPrice+mpItem.otherCost;
1616 
    lowest_possible_tp = nlc+inHouseCost;
1617 
    return round(lowest_possible_tp,2)
1618 
 
1619 
def getNewOurTp(mpItem,proposedSellingPrice):
1620 
    ourTp = proposedSellingPrice- proposedSellingPrice*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100));
1621 
    return round(ourTp,2)
9990 kshitij.so 1622 
 
1623 
def getNewLowestPossibleSp(mpItem,nlc,vatRate):
1624 
    lowestPossibleSp = (nlc+(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(vatRate/100))+(15+mpItem.otherCost)*(1+(vatRate)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(vatRate)/100)-(mpItem.returnProvision/100)*(1+(vatRate)/100));
1625 
    return round(lowestPossibleSp,2)
9881 kshitij.so 1626 
 
1627 
def getLowestPossibleSp(snapdealDetails,val,spm,mpItem):
1628 
    if snapdealDetails.rank==0:
1629 
        return mpItem.minimumPossibleSp
9954 kshitij.so 1630 
    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));
1631 
    return round(lowestPossibleSp,2)
9881 kshitij.so 1632 
 
1633 
def getTargetTp(targetSp,mpItem):
9954 kshitij.so 1634 
    targetTp = targetSp- targetSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100));
1635 
    return round(targetTp,2)
9881 kshitij.so 1636 
 
1637 
def getTargetSp(targetTp,mpItem):
9954 kshitij.so 1638 
    targetSp = float(targetTp+(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100)))/(1-((mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))))
1639 
    return round(targetSp,2)
9881 kshitij.so 1640 
 
1641 
def getSalesPotential(lowestOfferPrice,ourNlc):
1642 
    if lowestOfferPrice - ourNlc < 0:
1643 
        return 'HIGH'
9897 kshitij.so 1644 
    elif (float(lowestOfferPrice - ourNlc))/lowestOfferPrice >=0 and (float(lowestOfferPrice - ourNlc))/lowestOfferPrice <=.02:
9881 kshitij.so 1645 
        return 'MEDIUM'
1646 
    else:
1647 
        return 'LOW'
1648 
 
1649 
def main():
9949 kshitij.so 1650 
    parser = optparse.OptionParser()
1651 
    parser.add_option("-t", "--type", dest="runType",
1652 
                   default="FULL", type="string",
1653 
                   help="Run type FULL or FAVOURITE")
1654 
    (options, args) = parser.parse_args()
1655 
    if options.runType not in ('FULL','FAVOURITE'):
1656 
        print "Run type argument illegal."
1657 
        sys.exit(1)
1658 
    itemInfo,spm= populateStuff(options.runType)
9881 kshitij.so 1659 
    cantCompete, buyBoxItems, competitive, \
9949 kshitij.so 1660 
    competitiveNoInventory, exceptionList, negativeMargin = decideCategory(itemInfo,spm)
9881 kshitij.so 1661 
    timestamp = datetime.now()
10031 kshitij.so 1662 
    previousProcessingTimestamp = session.query(func.max(MarketPlaceHistory.timestamp)).filter(MarketPlaceHistory.source==7).one()
9949 kshitij.so 1663 
    exceptionItems = commitExceptionList(exceptionList,timestamp)
1664 
    cantComepeteItems = commitCantCompete(cantCompete,timestamp)
1665 
    buyBoxList = commitBuyBox(buyBoxItems,timestamp)
1666 
    competitiveItems = commitCompetitive(competitive,timestamp)
1667 
    competitiveNoInventoryItems = commitCompetitiveNoInventory(competitiveNoInventory,timestamp)
1668 
    negativeMarginItems = commitNegativeMargin(negativeMargin,timestamp)
9954 kshitij.so 1669 
    successfulAutoDecrease = fetchItemsForAutoDecrease(timestamp)
1670 
    successfulAutoIncrease = fetchItemsForAutoIncrease(timestamp)
9949 kshitij.so 1671 
    if options.runType=='FULL':
1672 
        previousAutoFav, nowAutoFav = markAutoFavourite()
9954 kshitij.so 1673 
    if options.runType=='FULL':
1674 
        writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, previousAutoFav, nowAutoFav,timestamp, options.runType)
1675 
    else:
1676 
        writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, None, None, timestamp, options.runType)
9990 kshitij.so 1677 
    commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp)
9954 kshitij.so 1678 
    sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease)
10031 kshitij.so 1679 
    processLostBuyBoxItems(previousProcessingTimestamp[0],timestamp)
9897 kshitij.so 1680 
 
9881 kshitij.so 1681 
if __name__ == '__main__':
1682 
    main()