Subversion Repositories SmartDukaan

Rev

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