Subversion Repositories SmartDukaan

Rev

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