Subversion Repositories SmartDukaan

Rev

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