Subversion Repositories SmartDukaan

Rev

Rev 9954 | Rev 9956 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

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