Subversion Repositories SmartDukaan

Rev

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