Subversion Repositories SmartDukaan

Rev

Rev 10687 | Rev 10760 | 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:
10759 kshitij.so 515 
            if (snapdealDetails.lowestSp-getSubsidyDiff(snapdealDetails) > lowestPossibleSp) and snapdealDetails.ourInventory!=0:
516 
            #if lowestTp > lowestPossibleTp and snapdealDetails.ourInventory!=0:
9881 kshitij.so 517 
                temp=[]
518 
                temp.append(snapdealDetails)
519 
                temp.append(val)
520 
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'TP',None,lowestPossibleSp)
521 
                temp.append(snapdealPricing)
522 
                temp.append(mpItem)
523 
                competitive.append(temp)
524 
                continue
525 
 
526 
        if competitionBasis=='SP':
527 
            if snapdealDetails.lowestSp > lowestPossibleSp and snapdealDetails.ourInventory==0:
528 
                temp=[]
529 
                temp.append(snapdealDetails)
530 
                temp.append(val)
531 
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'SP',None,lowestPossibleSp)
532 
                temp.append(snapdealPricing)
533 
                temp.append(mpItem)
534 
                competitiveNoInventory.append(temp)
535 
                continue
536 
        else:
10759 kshitij.so 537 
            if (snapdealDetails.lowestSp-getSubsidyDiff(snapdealDetails) > lowestPossibleSp) and snapdealDetails.ourInventory!=0:
10473 kshitij.so 538 
            #lowest sp - subs diff < lowest pos sp
10759 kshitij.so 539 
            #if lowestTp > lowestPossibleTp and snapdealDetails.ourInventory==0:
9881 kshitij.so 540 
                temp=[]
541 
                temp.append(snapdealDetails)
542 
                temp.append(val)
543 
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'TP',None,lowestPossibleSp)
544 
                temp.append(snapdealPricing)
545 
                temp.append(mpItem)
546 
                competitiveNoInventory.append(temp)
547 
                continue
548 
 
549 
        temp=[]
550 
        temp.append(snapdealDetails)
551 
        temp.append(val)
552 
        snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,competitionBasis,None,lowestPossibleSp)
553 
        temp.append(snapdealPricing)
554 
        temp.append(mpItem)
555 
        cantCompete.append(temp)
556 
 
9949 kshitij.so 557 
    return cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin
558 
 
9953 kshitij.so 559 
def writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, previousAutoFav, nowAutoFav,timestamp,runType):
9949 kshitij.so 560 
    wbk = xlwt.Workbook()
561 
    sheet = wbk.add_sheet('Can\'t Compete')
562 
    xstr = lambda s: s or ""
563 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
564 
 
565 
    excel_integer_format = '0'
566 
    integer_style = xlwt.XFStyle()
567 
    integer_style.num_format_str = excel_integer_format
568 
 
569 
    sheet.write(0, 0, "Item ID", heading_xf)
570 
    sheet.write(0, 1, "Category", heading_xf)
571 
    sheet.write(0, 2, "Product Group.", heading_xf)
572 
    sheet.write(0, 3, "SUPC", heading_xf)
573 
    sheet.write(0, 4, "Brand", heading_xf)
574 
    sheet.write(0, 5, "Product Name", heading_xf)
575 
    sheet.write(0, 6, "Weight", heading_xf)
576 
    sheet.write(0, 7, "Courier Cost", heading_xf)
577 
    sheet.write(0, 8, "Risky", heading_xf)
578 
    sheet.write(0, 9, "Our SP", heading_xf)
579 
    sheet.write(0, 11, "Our TP", heading_xf)
580 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
581 
    sheet.write(0, 12, "Our Rank", heading_xf)
582 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
583 
    sheet.write(0, 14, "Lowest SP", heading_xf)
584 
    sheet.write(0, 15, "Lowest TP", heading_xf)
585 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
586 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
587 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
588 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
589 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
590 
    sheet.write(0, 21, "Average Sale", heading_xf)
591 
    sheet.write(0, 22, "Our NLC", heading_xf)
592 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
593 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
10381 manish.sha 594 
    sheet.write(0, 25, "Subsidy Difference", heading_xf)
9949 kshitij.so 595 
    sheet.write(0, 26, "Target TP", heading_xf)
596 
    sheet.write(0, 27, "Target SP", heading_xf)
597 
    sheet.write(0, 28, "Target NLC", heading_xf)
598 
    sheet.write(0, 29, "Sales Potential", heading_xf)
599 
    sheet_iterator = 1
600 
    for item in cantCompete:
601 
        snapdealDetails = item[0]
602 
        snapdealItemInfo = item[1]
603 
        snapdealPricing = item[2]
604 
        mpItem = item[3]
605 
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
606 
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
607 
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
608 
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
609 
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
610 
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
611 
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
612 
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
613 
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
614 
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
615 
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
616 
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
617 
        sheet.write(sheet_iterator, 12, snapdealDetails.rank)
618 
        sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
619 
        sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
620 
        sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
621 
        sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
622 
        sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
623 
        sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
624 
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
625 
            sheet.write(sheet_iterator, 19, 'Info not available')
626 
        else:
627 
            sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
628 
        sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
629 
        sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
630 
        sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
631 
        sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
632 
        sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
10381 manish.sha 633 
        sheet.write(sheet_iterator, 25, getSubsidyDiff(snapdealDetails))
9949 kshitij.so 634 
        if (snapdealPricing.competitionBasis=='SP'):
635 
            proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
636 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
637 
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
638 
        else:
10381 manish.sha 639 
            #proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
640 
            #proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
641 
            proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001) - getSubsidyDiff(snapdealDetails)
642 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
9949 kshitij.so 643 
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 644 
        sheet.write(sheet_iterator, 26, round(proposed_tp,2))
645 
        sheet.write(sheet_iterator, 27, round(proposed_sp,2))
646 
        sheet.write(sheet_iterator, 28, round(target_nlc,2))
9949 kshitij.so 647 
        sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
648 
        sheet_iterator+=1
649 
 
650 
    sheet = wbk.add_sheet('Lowest')
651 
 
652 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
653 
 
654 
    excel_integer_format = '0'
655 
    integer_style = xlwt.XFStyle()
656 
    integer_style.num_format_str = excel_integer_format
657 
    xstr = lambda s: s or ""
658 
 
659 
    sheet.write(0, 0, "Item ID", heading_xf)
660 
    sheet.write(0, 1, "Category", heading_xf)
661 
    sheet.write(0, 2, "Product Group.", heading_xf)
662 
    sheet.write(0, 3, "SUPC", heading_xf)
663 
    sheet.write(0, 4, "Brand", heading_xf)
664 
    sheet.write(0, 5, "Product Name", heading_xf)
665 
    sheet.write(0, 6, "Weight", heading_xf)
666 
    sheet.write(0, 7, "Courier Cost", heading_xf)
667 
    sheet.write(0, 8, "Risky", heading_xf)
668 
    sheet.write(0, 9, "Our SP", heading_xf)
669 
    sheet.write(0, 11, "Our TP", heading_xf)
670 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
671 
    sheet.write(0, 12, "Our Rank", heading_xf)
672 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
673 
    sheet.write(0, 14, "Second Lowest Seller", heading_xf)
674 
    sheet.write(0, 15, "Second Lowest Price", heading_xf)
675 
    sheet.write(0, 16, "Second Lowest Offer Price", heading_xf)
676 
    sheet.write(0, 17, "Second Lowest Seller TP", heading_xf)
677 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
678 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
679 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
680 
    sheet.write(0, 21, "Average Sale", heading_xf)
681 
    sheet.write(0, 22, "Second Lowest Seller Inventory", heading_xf)
682 
    sheet.write(0, 23, "Our NLC", heading_xf)
10381 manish.sha 683 
    sheet.write(0, 24, "Subsidy Difference", heading_xf)
9949 kshitij.so 684 
    sheet.write(0, 25, "Target TP", heading_xf)
685 
    sheet.write(0, 26, "Target SP", heading_xf)
686 
    sheet.write(0, 27, "MARGIN INCREASED POTENTIAL", heading_xf)
687 
 
688 
    sheet_iterator = 1
689 
    for item in buyBoxItems:
690 
        snapdealDetails = item[0]
691 
        snapdealItemInfo = item[1]
692 
        snapdealPricing = item[2]
693 
        mpItem = item[3]
694 
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
695 
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
696 
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
697 
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
698 
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
699 
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
700 
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
701 
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
702 
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
703 
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
704 
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
705 
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
706 
        sheet.write(sheet_iterator, 12, snapdealDetails.rank)
707 
        sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
708 
        sheet.write(sheet_iterator, 14, snapdealDetails.secondLowestSellerName)
709 
        sheet.write(sheet_iterator, 15, snapdealDetails.secondLowestSellerSp)
710 
        sheet.write(sheet_iterator, 16, snapdealDetails.secondLowestSellerOfferPrice)
711 
        sheet.write(sheet_iterator, 17, snapdealPricing.secondLowestSellerTp)
712 
        sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
713 
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
714 
            sheet.write(sheet_iterator, 19, 'Info not available')
715 
        else:
716 
            sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
717 
        sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
718 
        sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
719 
        sheet.write(sheet_iterator, 22, snapdealDetails.secondLowestSellerInventory)
720 
        sheet.write(sheet_iterator, 23, snapdealItemInfo.nlc)
10381 manish.sha 721 
        sheet.write(sheet_iterator, 24, getSubsidyDiff(snapdealDetails))
9949 kshitij.so 722 
        if (snapdealPricing.competitionBasis=='SP'):
723 
            proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)), snapdealPricing.lowestPossibleSp)
724 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
725 
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
726 
        else:
10381 manish.sha 727 
            #proposed_tp  = max(snapdealPricing.secondLowestSellerTp - max((20, snapdealPricing.secondLowestSellerTp*0.002)), snapdealPricing.lowestPossibleTp)
728 
            #proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
729 
            proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)) -getSubsidyDiff(snapdealDetails), snapdealPricing.lowestPossibleSp)
730 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
9949 kshitij.so 731 
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 732 
        sheet.write(sheet_iterator, 25, round(proposed_tp,2))
733 
        sheet.write(sheet_iterator, 26, round(proposed_sp,2))
734 
        sheet.write(sheet_iterator, 27, round((proposed_tp - snapdealPricing.ourTp),2))
9949 kshitij.so 735 
        sheet_iterator+=1
736 
 
737 
    sheet = wbk.add_sheet('Can Compete-With Inventory')
738 
 
739 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
740 
 
741 
    excel_integer_format = '0'
742 
    integer_style = xlwt.XFStyle()
743 
    integer_style.num_format_str = excel_integer_format
744 
    xstr = lambda s: s or ""
745 
 
746 
    sheet.write(0, 0, "Item ID", heading_xf)
747 
    sheet.write(0, 1, "Category", heading_xf)
748 
    sheet.write(0, 2, "Product Group.", heading_xf)
749 
    sheet.write(0, 3, "SUPC", heading_xf)
750 
    sheet.write(0, 4, "Brand", heading_xf)
751 
    sheet.write(0, 5, "Product Name", heading_xf)
752 
    sheet.write(0, 6, "Weight", heading_xf)
753 
    sheet.write(0, 7, "Courier Cost", heading_xf)
754 
    sheet.write(0, 8, "Risky", heading_xf)
755 
    sheet.write(0, 9, "Our SP", heading_xf)
756 
    sheet.write(0, 11, "Our TP", heading_xf)
757 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
758 
    sheet.write(0, 12, "Our Rank", heading_xf)
759 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
760 
    sheet.write(0, 14, "Lowest SP", heading_xf)
761 
    sheet.write(0, 15, "Lowest TP", heading_xf)
762 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
763 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
764 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
765 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
766 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
767 
    sheet.write(0, 21, "Average Sale", heading_xf)
768 
    sheet.write(0, 22, "Our NLC", heading_xf)
769 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
770 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
10381 manish.sha 771 
    sheet.write(0, 25, "Subsidy Difference", heading_xf)
9949 kshitij.so 772 
    sheet.write(0, 26, "Target TP", heading_xf)
773 
    sheet.write(0, 27, "Target SP", heading_xf)
774 
    sheet.write(0, 28, "Sales Potential", heading_xf)
775 
 
776 
    sheet_iterator = 1
777 
    for item in competitive:
778 
        snapdealDetails = item[0]
779 
        snapdealItemInfo = item[1]
780 
        snapdealPricing = item[2]
781 
        mpItem = item[3]
782 
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
783 
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
784 
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
785 
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
786 
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
787 
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
788 
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
789 
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
790 
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
791 
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
792 
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
793 
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
794 
        sheet.write(sheet_iterator, 12, snapdealDetails.rank)
795 
        sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
796 
        sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
797 
        sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
798 
        sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
799 
        sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
800 
        sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
801 
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
802 
            sheet.write(sheet_iterator, 19, 'Info not available')
803 
        else:
804 
            sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
805 
        sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
806 
        sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
807 
        sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
808 
        sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
809 
        sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
10381 manish.sha 810 
        sheet.write(sheet_iterator, 25, getSubsidyDiff(snapdealDetails))
9949 kshitij.so 811 
        if (snapdealPricing.competitionBasis=='SP'):
812 
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
813 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
814 
        else:
10381 manish.sha 815 
            #proposed_tp  = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
816 
            #proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
817 
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)) -getSubsidyDiff(snapdealDetails), snapdealPricing.lowestPossibleSp)
818 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
9954 kshitij.so 819 
        sheet.write(sheet_iterator, 26, round(proposed_tp,2))
820 
        sheet.write(sheet_iterator, 27, round(proposed_sp,2))
9949 kshitij.so 821 
        sheet.write(sheet_iterator, 28, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
822 
        sheet_iterator+=1
823 
 
824 
    sheet = wbk.add_sheet('Negative Margin')
825 
 
826 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
827 
 
828 
    excel_integer_format = '0'
829 
    integer_style = xlwt.XFStyle()
830 
    integer_style.num_format_str = excel_integer_format
831 
    xstr = lambda s: s or ""
832 
 
833 
    sheet.write(0, 0, "Item ID", heading_xf)
834 
    sheet.write(0, 1, "Category", heading_xf)
835 
    sheet.write(0, 2, "Product Group.", heading_xf)
836 
    sheet.write(0, 3, "SUPC", heading_xf)
837 
    sheet.write(0, 4, "Brand", heading_xf)
838 
    sheet.write(0, 5, "Product Name", heading_xf)
839 
    sheet.write(0, 6, "Weight", heading_xf)
840 
    sheet.write(0, 7, "Courier Cost", heading_xf)
841 
    sheet.write(0, 8, "Risky", heading_xf)
842 
    sheet.write(0, 9, "Our SP", heading_xf)
843 
    sheet.write(0, 11, "Our TP", heading_xf)
844 
    sheet.write(0, 12, "Lowest Possible TP", heading_xf)
845 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
846 
    sheet.write(0, 13, "Our Rank", heading_xf)
847 
    sheet.write(0, 14, "Our Snapdeal Inventory", heading_xf)
848 
    sheet.write(0, 15, "Net Availability", heading_xf)
849 
    sheet.write(0, 16, "Last Five Day Sale", heading_xf)
850 
    sheet.write(0, 17, "Average Sale", heading_xf)
851 
    sheet.write(0, 18, "Our NLC", heading_xf)
852 
    sheet.write(0, 19, "Margin", heading_xf)
853 
 
854 
    sheet_iterator=1
855 
    for item in negativeMargin:
856 
        snapdealDetails = item[0]
857 
        snapdealItemInfo = item[1]
858 
        snapdealPricing = item[2]
859 
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
860 
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
861 
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
862 
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
863 
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
864 
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
865 
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
866 
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
867 
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
868 
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
869 
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
870 
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
871 
        sheet.write(sheet_iterator, 12, snapdealPricing.lowestPossibleTp)
872 
        sheet.write(sheet_iterator, 13, snapdealDetails.rank)
873 
        sheet.write(sheet_iterator, 14, snapdealDetails.ourInventory)
874 
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
875 
            sheet.write(sheet_iterator, 15, 'Info not available')
876 
        else:
877 
            sheet.write(sheet_iterator, 15, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
878 
        sheet.write(sheet_iterator, 16, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
879 
        sheet.write(sheet_iterator, 17, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
880 
        sheet.write(sheet_iterator, 18, snapdealItemInfo.nlc)
9954 kshitij.so 881 
        sheet.write(sheet_iterator, 19, round((snapdealPricing.ourTp - snapdealPricing.lowestPossibleTp),2))
9949 kshitij.so 882 
        sheet_iterator+=1
883 
 
884 
    sheet = wbk.add_sheet('Exception Item List')
885 
 
886 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
887 
 
888 
    excel_integer_format = '0'
889 
    integer_style = xlwt.XFStyle()
890 
    integer_style.num_format_str = excel_integer_format
891 
    xstr = lambda s: s or ""
892 
 
893 
    sheet.write(0, 0, "Item ID", heading_xf)
894 
    sheet.write(0, 1, "Brand", heading_xf)
895 
    sheet.write(0, 2, "Product Name", heading_xf)
896 
    sheet.write(0, 3, "Reason", heading_xf)
897 
    sheet_iterator=1
898 
    for item in exceptionList:
899 
        sheet.write(sheet_iterator, 0, item.item_id)
900 
        sheet.write(sheet_iterator, 1, item.brand)
901 
        sheet.write(sheet_iterator, 2, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
902 
        sheet.write(sheet_iterator, 3, "Unable to fetch info from Snapdeal")
903 
        sheet_iterator+=1
904 
 
905 
    sheet = wbk.add_sheet('Can Compete-No Inv')
906 
 
907 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
908 
 
909 
    excel_integer_format = '0'
910 
    integer_style = xlwt.XFStyle()
911 
    integer_style.num_format_str = excel_integer_format
912 
    xstr = lambda s: s or ""
913 
 
914 
    sheet.write(0, 0, "Item ID", heading_xf)
915 
    sheet.write(0, 1, "Category", heading_xf)
916 
    sheet.write(0, 2, "Product Group.", heading_xf)
917 
    sheet.write(0, 3, "SUPC", heading_xf)
918 
    sheet.write(0, 4, "Brand", heading_xf)
919 
    sheet.write(0, 5, "Product Name", heading_xf)
920 
    sheet.write(0, 6, "Weight", heading_xf)
921 
    sheet.write(0, 7, "Courier Cost", heading_xf)
922 
    sheet.write(0, 8, "Risky", heading_xf)
923 
    sheet.write(0, 9, "Our SP", heading_xf)
924 
    sheet.write(0, 11, "Our TP", heading_xf)
925 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
926 
    sheet.write(0, 12, "Our Rank", heading_xf)
927 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
928 
    sheet.write(0, 14, "Lowest SP", heading_xf)
929 
    sheet.write(0, 15, "Lowest TP", heading_xf)
930 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
931 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
932 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
933 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
934 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
935 
    sheet.write(0, 21, "Average Sale", heading_xf)
936 
    sheet.write(0, 22, "Our NLC", heading_xf)
937 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
938 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
10381 manish.sha 939 
    sheet.write(0, 25, "Subsidy Difference", heading_xf)
9949 kshitij.so 940 
    sheet.write(0, 26, "Target TP", heading_xf)
941 
    sheet.write(0, 27, "Target SP", heading_xf)
942 
    sheet.write(0, 28, "Target NLC", heading_xf)
943 
    sheet.write(0, 29, "Sales Potential", heading_xf)
944 
 
945 
    sheet_iterator = 1
946 
    for item in competitiveNoInventory:
947 
        snapdealDetails = item[0]
948 
        snapdealItemInfo = item[1]
949 
        snapdealPricing = item[2]
950 
        mpItem = item[3]
951 
        if ((not inventoryMap.has_key(snapdealItemInfo.item_id)) or getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id))<=0):
952 
            sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
953 
            sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
954 
            sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
955 
            sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
956 
            sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
957 
            sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
958 
            sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
959 
            sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
960 
            sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
961 
            sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
962 
            sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
963 
            sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
964 
            sheet.write(sheet_iterator, 12, snapdealDetails.rank)
965 
            sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
966 
            sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
967 
            sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
968 
            sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
969 
            sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
970 
            sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
971 
            if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
972 
                sheet.write(sheet_iterator, 19, 'Info not available')
973 
            else:
974 
                sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
975 
            sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
976 
            sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
977 
            sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
978 
            sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
979 
            sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
10381 manish.sha 980 
            sheet.write(sheet_iterator, 25, getSubsidyDiff(snapdealDetails))
9949 kshitij.so 981 
            if (snapdealPricing.competitionBasis=='SP'):
982 
                proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
983 
                proposed_tp = getTargetTp(proposed_sp,mpItem)
984 
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
985 
            else:
10381 manish.sha 986 
                #proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
987 
                #proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
988 
                proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001) -getSubsidyDiff(snapdealDetails)
989 
                proposed_tp = getTargetTp(proposed_sp,mpItem)
9949 kshitij.so 990 
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 991 
            sheet.write(sheet_iterator, 26, round(proposed_tp,2))
992 
            sheet.write(sheet_iterator, 27, round(proposed_sp,2))
993 
            sheet.write(sheet_iterator, 28, round(target_nlc,2))
9949 kshitij.so 994 
            sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
995 
            sheet_iterator+=1
996 
 
997 
    sheet = wbk.add_sheet('Can Compete-No Inv On SD')
998 
 
999 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1000 
 
1001 
    excel_integer_format = '0'
1002 
    integer_style = xlwt.XFStyle()
1003 
    integer_style.num_format_str = excel_integer_format
1004 
    xstr = lambda s: s or ""
1005 
 
1006 
    sheet.write(0, 0, "Item ID", heading_xf)
1007 
    sheet.write(0, 1, "Category", heading_xf)
1008 
    sheet.write(0, 2, "Product Group.", heading_xf)
1009 
    sheet.write(0, 3, "SUPC", heading_xf)
1010 
    sheet.write(0, 4, "Brand", heading_xf)
1011 
    sheet.write(0, 5, "Product Name", heading_xf)
1012 
    sheet.write(0, 6, "Weight", heading_xf)
1013 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1014 
    sheet.write(0, 8, "Risky", heading_xf)
1015 
    sheet.write(0, 9, "Our SP", heading_xf)
1016 
    sheet.write(0, 11, "Our TP", heading_xf)
1017 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
1018 
    sheet.write(0, 12, "Our Rank", heading_xf)
1019 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
1020 
    sheet.write(0, 14, "Lowest SP", heading_xf)
1021 
    sheet.write(0, 15, "Lowest TP", heading_xf)
1022 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
1023 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
1024 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
1025 
    sheet.write(0, 19, "Our Net Availability",heading_xf)
1026 
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
1027 
    sheet.write(0, 21, "Average Sale", heading_xf)
1028 
    sheet.write(0, 22, "Our NLC", heading_xf)
1029 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
1030 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
10381 manish.sha 1031 
    sheet.write(0, 25, "Subsidy Difference", heading_xf)
9949 kshitij.so 1032 
    sheet.write(0, 26, "Target TP", heading_xf)
1033 
    sheet.write(0, 27, "Target SP", heading_xf)
1034 
    sheet.write(0, 28, "Target NLC", heading_xf)
1035 
    sheet.write(0, 29, "Sales Potential", heading_xf)
1036 
 
1037 
    sheet_iterator = 1
1038 
    for item in competitiveNoInventory:
1039 
        snapdealDetails = item[0]
1040 
        snapdealItemInfo = item[1]
1041 
        snapdealPricing = item[2]
1042 
        mpItem = item[3]
1043 
        if (inventoryMap.has_key(snapdealItemInfo.item_id) and getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id))>0):
1044 
            sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
1045 
            sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
1046 
            sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
1047 
            sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
1048 
            sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
1049 
            sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
1050 
            sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
1051 
            sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
1052 
            sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
1053 
            sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
1054 
            sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
1055 
            sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
1056 
            sheet.write(sheet_iterator, 12, snapdealDetails.rank)
1057 
            sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
1058 
            sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
1059 
            sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
1060 
            sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
1061 
            sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
1062 
            sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
1063 
            if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
1064 
                sheet.write(sheet_iterator, 19, 'Info not available')
1065 
            else:
1066 
                sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
1067 
            sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
1068 
            sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
1069 
            sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
1070 
            sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
1071 
            sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
10381 manish.sha 1072 
            sheet.write(sheet_iterator, 25, getSubsidyDiff(snapdealDetails))
9949 kshitij.so 1073 
            if (snapdealPricing.competitionBasis=='SP'):
1074 
                proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
1075 
                proposed_tp = getTargetTp(proposed_sp,mpItem)
1076 
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
1077 
            else:
10381 manish.sha 1078 
                #proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
1079 
                #proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
1080 
                proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001) -getSubsidyDiff(snapdealDetails)
1081 
                proposed_tp = getTargetTp(proposed_sp,mpItem)
9949 kshitij.so 1082 
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 1083 
            sheet.write(sheet_iterator, 26, round(proposed_tp,2))
1084 
            sheet.write(sheet_iterator, 27, round(proposed_sp,2))
1085 
            sheet.write(sheet_iterator, 28, round(target_nlc,2))
9949 kshitij.so 1086 
            sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
1087 
            sheet_iterator+=1
9953 kshitij.so 1088 
 
1089 
    if (runType=='FULL'):
1090 
        sheet = wbk.add_sheet('Auto Favorites')
1091 
 
1092 
        heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1093 
 
1094 
        excel_integer_format = '0'
1095 
        integer_style = xlwt.XFStyle()
1096 
        integer_style.num_format_str = excel_integer_format
1097 
        xstr = lambda s: s or ""
1098 
 
1099 
        sheet.write(0, 0, "Item ID", heading_xf)
1100 
        sheet.write(0, 1, "Brand", heading_xf)
1101 
        sheet.write(0, 2, "Product Name", heading_xf)
1102 
        sheet.write(0, 3, "Auto Favourite", heading_xf)
1103 
        sheet.write(0, 4, "Reason", heading_xf)
1104 
 
1105 
        sheet_iterator=1
1106 
        for autoFav in nowAutoFav:
1107 
            itemId = autoFav[0]
1108 
            reason = autoFav[1]
1109 
            it = Item.query.filter_by(id=itemId).one()
1110 
            sheet.write(sheet_iterator, 0, itemId)
1111 
            sheet.write(sheet_iterator, 1, it.brand)
1112 
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1113 
            sheet.write(sheet_iterator, 3, "True")
1114 
            sheet.write(sheet_iterator, 4, reason)
1115 
            sheet_iterator+=1
1116 
        for prevFav in previousAutoFav:
1117 
            it = Item.query.filter_by(id=prevFav).one()
1118 
            sheet.write(sheet_iterator, 0, prevFav)
1119 
            sheet.write(sheet_iterator, 1, it.brand)
1120 
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1121 
            sheet.write(sheet_iterator, 3, "False")
1122 
            sheet_iterator+=1
9949 kshitij.so 1123 
 
1124 
 
1125 
    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()
1126 
    sheet = wbk.add_sheet('Auto Inc and Dec')
1127 
 
1128 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1129 
 
1130 
    excel_integer_format = '0'
1131 
    integer_style = xlwt.XFStyle()
1132 
    integer_style.num_format_str = excel_integer_format
1133 
    xstr = lambda s: s or ""
1134 
 
1135 
    sheet.write(0, 0, "Item ID", heading_xf)
1136 
    sheet.write(0, 1, "Brand", heading_xf)
1137 
    sheet.write(0, 2, "Product Name", heading_xf)
1138 
    sheet.write(0, 3, "Decision", heading_xf)
1139 
    sheet.write(0, 4, "Reason", heading_xf)
9954 kshitij.so 1140 
    sheet.write(0, 5, "Old Selling Price", heading_xf)
1141 
    sheet.write(0, 6, "Selling Price Updated",heading_xf)
9949 kshitij.so 1142 
 
1143 
    sheet_iterator=1
1144 
    for autoPricingItem in autoPricingItems:
1145 
        mpHistory = autoPricingItem[0]
1146 
        item = autoPricingItem[1]
9954 kshitij.so 1147 
        it = Item.query.filter_by(id=item.id).one()
9949 kshitij.so 1148 
        sheet.write(sheet_iterator, 0, item.id)
1149 
        sheet.write(sheet_iterator, 1, it.brand)
1150 
        sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1151 
        sheet.write(sheet_iterator, 3, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
1152 
        sheet.write(sheet_iterator, 4, mpHistory.reason)
1153 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
9954 kshitij.so 1154 
            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
1155 
            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.proposedSellingPrice))
9949 kshitij.so 1156 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
9954 kshitij.so 1157 
            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
1158 
            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
9949 kshitij.so 1159 
        sheet_iterator+=1
1160 
 
9953 kshitij.so 1161 
    filename = "/tmp/snapdeal-report-"+runType+" " + str(timestamp) + ".xls"
9949 kshitij.so 1162 
    wbk.save(filename)
10207 kshitij.so 1163 
    try:
10293 kshitij.so 1164 
        #EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Snapdeal Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
1165 
        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 1166 
    except Exception as e:
1167 
        print e
10219 kshitij.so 1168 
        print "Unable to send report.Trying with local SMTP"
1169 
        smtpServer = smtplib.SMTP('localhost')
1170 
        smtpServer.set_debuglevel(1)
1171 
        sender = 'support@shop2020.in'
10293 kshitij.so 1172 
        #recipients = ['kshitij.sood@saholic.com']
10219 kshitij.so 1173 
        msg = MIMEMultipart()
1174 
        msg['Subject'] = "Snapdeal Auto Pricing" + ' - ' + str(datetime.now())
1175 
        msg['From'] = sender
10293 kshitij.so 1176 
        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 1177 
        msg['To'] = ",".join(recipients)
10219 kshitij.so 1178 
        fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
1179 
        fileMsg.set_payload(file(filename).read())
1180 
        email.encoders.encode_base64(fileMsg)
1181 
        fileMsg.add_header('Content-Disposition','attachment;filename=snapdeal.xls')
1182 
        msg.attach(fileMsg)
1183 
        try:
1184 
            smtpServer.sendmail(sender, recipients, msg.as_string())
1185 
            print "Successfully sent email"
1186 
        except:
1187 
            print "Error: unable to send email."
9881 kshitij.so 1188 
 
10219 kshitij.so 1189 
 
9881 kshitij.so 1190 
def commitExceptionList(exceptionList,timestamp):
9949 kshitij.so 1191 
    exceptionItems=[]
9881 kshitij.so 1192 
    for item in exceptionList:
1193 
        mpHistory = MarketPlaceHistory()
1194 
        mpHistory.item_id =item.item_id
1195 
        mpHistory.source = OrderSource.SNAPDEAL
1196 
        mpHistory.competitiveCategory = CompetitionCategory.EXCEPTION
1197 
        mpHistory.risky = item.risky
1198 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1199 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(item.runType)
1200 
        exceptionItems.append(mpHistory)
9881 kshitij.so 1201 
    session.commit()
9949 kshitij.so 1202 
    return exceptionItems
9881 kshitij.so 1203 
 
1204 
def commitNegativeMargin(negativeMargin,timestamp):
9949 kshitij.so 1205 
    negativeMarginItems = []
9881 kshitij.so 1206 
    for item in negativeMargin:
1207 
        snapdealDetails = item[0]
1208 
        snapdealItemInfo = item[1]
1209 
        snapdealPricing = item[2]
1210 
        mpHistory = MarketPlaceHistory()
1211 
        mpHistory.item_id = snapdealItemInfo.item_id
1212 
        mpHistory.source = OrderSource.SNAPDEAL
1213 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1214 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1215 
        mpHistory.ourTp = snapdealPricing.ourTp
9919 kshitij.so 1216 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
9881 kshitij.so 1217 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1218 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1219 
        mpHistory.otherInventory = snapdealDetails.otherInventory
1220 
        mpHistory.ourRank = snapdealDetails.rank
9919 kshitij.so 1221 
        mpHistory.risky = snapdealItemInfo.risky
1222 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1223 
        mpHistory.competitiveCategory = CompetitionCategory.NEGATIVE_MARGIN
1224 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1225 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1226 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1227 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1228 
        negativeMarginItems.append(mpHistory)
9881 kshitij.so 1229 
    session.commit()
9949 kshitij.so 1230 
    return negativeMarginItems
9881 kshitij.so 1231 
 
1232 
def commitCompetitive(competitive,timestamp):
9949 kshitij.so 1233 
    competitiveItems = []
9881 kshitij.so 1234 
    for item in competitive:
1235 
        snapdealDetails = item[0]
1236 
        snapdealItemInfo = item[1]
1237 
        snapdealPricing = item[2]
1238 
        mpItem = item[3]
1239 
        mpHistory = MarketPlaceHistory()
1240 
        mpHistory.item_id = snapdealItemInfo.item_id
1241 
        mpHistory.source = OrderSource.SNAPDEAL
1242 
        mpHistory.lowestTp = snapdealPricing.lowestTp
1243 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
1244 
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
1245 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1246 
        mpHistory.otherInventory = snapdealDetails.otherInventory
1247 
        mpHistory.ourRank = snapdealDetails.rank
1248 
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
1249 
        mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE
1250 
        mpHistory.risky = snapdealItemInfo.risky
1251 
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
1252 
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
1253 
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
1254 
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
1255 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1256 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1257 
        mpHistory.ourTp = snapdealPricing.ourTp
1258 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1259 
        if (snapdealPricing.competitionBasis=='SP'):
1260 
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
1261 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
9954 kshitij.so 1262 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1263 
            mpHistory.proposedTp = round(proposed_tp,2)
9881 kshitij.so 1264 
        else:
10381 manish.sha 1265 
            #proposed_tp  = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
1266 
            #proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
1267 
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)) -getSubsidyDiff(snapdealDetails), snapdealPricing.lowestPossibleSp)
1268 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
9954 kshitij.so 1269 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1270 
            mpHistory.proposedTp = round(proposed_tp,2)
9919 kshitij.so 1271 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1272 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1273 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1274 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
1275 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1276 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1277 
        competitiveItems.append(mpHistory)
9881 kshitij.so 1278 
    session.commit()
9949 kshitij.so 1279 
    return competitiveItems
9881 kshitij.so 1280 
 
1281 
def commitCompetitiveNoInventory(competitiveNoInventory,timestamp):
9949 kshitij.so 1282 
    competitiveNoInventoryItems = []
9881 kshitij.so 1283 
    for item in competitiveNoInventory:
1284 
        snapdealDetails = item[0]
1285 
        snapdealItemInfo = item[1]
1286 
        snapdealPricing = item[2]
1287 
        mpItem = item[3]
1288 
        mpHistory = MarketPlaceHistory()
1289 
        mpHistory.item_id = snapdealItemInfo.item_id
1290 
        mpHistory.source = OrderSource.SNAPDEAL
1291 
        mpHistory.lowestTp = snapdealPricing.lowestTp
1292 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
1293 
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
1294 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1295 
        mpHistory.otherInventory = snapdealDetails.otherInventory
1296 
        mpHistory.ourRank = snapdealDetails.rank
1297 
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
1298 
        mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE_NO_INVENTORY
1299 
        mpHistory.risky = snapdealItemInfo.risky
1300 
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
1301 
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
1302 
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
1303 
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
1304 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1305 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1306 
        mpHistory.ourTp = snapdealPricing.ourTp
1307 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1308 
        if (snapdealPricing.competitionBasis=='SP'):
1309 
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
1310 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
9954 kshitij.so 1311 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1312 
            mpHistory.proposedTp = round(proposed_tp,2)
9881 kshitij.so 1313 
        else:
10381 manish.sha 1314 
            #proposed_tp  = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
1315 
            #proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
1316 
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)) -getSubsidyDiff(snapdealDetails), snapdealPricing.lowestPossibleSp)
1317 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
9954 kshitij.so 1318 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1319 
            mpHistory.proposedTp = round(proposed_tp,2)
9919 kshitij.so 1320 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1321 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1322 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1323 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
1324 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1325 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1326 
        competitiveNoInventoryItems.append(mpHistory)
9881 kshitij.so 1327 
    session.commit()
9949 kshitij.so 1328 
    return competitiveNoInventoryItems
9881 kshitij.so 1329 
 
1330 
def commitCantCompete(cantCompete,timestamp):
9949 kshitij.so 1331 
    cantComepeteItems = []
9881 kshitij.so 1332 
    for item in cantCompete:
1333 
        snapdealDetails = item[0]
1334 
        snapdealItemInfo = item[1]
1335 
        snapdealPricing = item[2]
1336 
        mpItem = item[3]
1337 
        mpHistory = MarketPlaceHistory()
1338 
        mpHistory.item_id = snapdealItemInfo.item_id
1339 
        mpHistory.source = OrderSource.SNAPDEAL
1340 
        mpHistory.lowestTp = snapdealPricing.lowestTp
1341 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
1342 
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
1343 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1344 
        mpHistory.otherInventory = snapdealDetails.otherInventory
1345 
        mpHistory.ourRank = snapdealDetails.rank
1346 
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
1347 
        mpHistory.competitiveCategory = CompetitionCategory.CANT_COMPETE
1348 
        mpHistory.risky = snapdealItemInfo.risky
1349 
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
1350 
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
1351 
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
1352 
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
1353 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1354 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1355 
        mpHistory.ourTp = snapdealPricing.ourTp
1356 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1357 
        if (snapdealPricing.competitionBasis=='SP'):
1358 
            proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
1359 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
1360 
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 1361 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1362 
            mpHistory.proposedTp = round(proposed_tp,2)
1363 
            mpHistory.targetNlc = round(target_nlc,2)
9881 kshitij.so 1364 
        else:
10381 manish.sha 1365 
            #proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
1366 
            #proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
1367 
            proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001) -getSubsidyDiff(snapdealDetails)
1368 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
9881 kshitij.so 1369 
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 1370 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1371 
            mpHistory.proposedTp = round(proposed_tp,2)
1372 
            mpHistory.targetNlc = round(target_nlc,2)
9919 kshitij.so 1373 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1374 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1375 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1376 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
1377 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1378 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1379 
        cantComepeteItems.append(mpHistory)
9881 kshitij.so 1380 
    session.commit()
9949 kshitij.so 1381 
    return cantComepeteItems
9881 kshitij.so 1382 
 
1383 
def commitBuyBox(buyBoxItems,timestamp):
9949 kshitij.so 1384 
    buyBoxList = []
9881 kshitij.so 1385 
    for item in buyBoxItems:
1386 
        snapdealDetails = item[0]
1387 
        snapdealItemInfo = item[1]
1388 
        snapdealPricing = item[2]
1389 
        mpItem = item[3]
1390 
        mpHistory = MarketPlaceHistory()
1391 
        mpHistory.item_id = snapdealItemInfo.item_id
1392 
        mpHistory.source = OrderSource.SNAPDEAL
1393 
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
1394 
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
1395 
        mpHistory.ourInventory = snapdealDetails.ourInventory
1396 
        mpHistory.secondLowestInventory = snapdealDetails.secondLowestSellerInventory
1397 
        mpHistory.ourRank = snapdealDetails.rank
1398 
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
1399 
        mpHistory.competitiveCategory = CompetitionCategory.BUY_BOX
1400 
        mpHistory.risky = snapdealItemInfo.risky
1401 
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
1402 
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
1403 
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
1404 
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
1405 
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
1406 
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
1407 
        mpHistory.ourTp = snapdealPricing.ourTp
1408 
        mpHistory.ourNlc = snapdealItemInfo.nlc
1409 
        mpHistory.secondLowestSellerName = snapdealDetails.secondLowestSellerName
1410 
        mpHistory.secondLowestSellerCode = snapdealDetails.secondLowestSellerCode
9885 kshitij.so 1411 
        mpHistory.secondLowestSellingPrice = snapdealDetails.secondLowestSellerSp
9888 kshitij.so 1412 
        mpHistory.secondLowestOfferPrice = snapdealDetails.secondLowestSellerOfferPrice
9881 kshitij.so 1413 
        mpHistory.secondLowestTp = snapdealPricing.secondLowestSellerTp
1414 
        if (snapdealPricing.competitionBasis=='SP'):
1415 
            proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)), snapdealPricing.lowestPossibleSp)
1416 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
1417 
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 1418 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1419 
            mpHistory.proposedTp = round(proposed_tp,2)
9881 kshitij.so 1420 
            #mpHistory.targetNlc = target_nlc
1421 
        else:
10381 manish.sha 1422 
            #proposed_tp  = max(snapdealPricing.secondLowestSellerTp - max((20, snapdealPricing.secondLowestSellerTp*0.002)), snapdealPricing.lowestPossibleTp)
1423 
            #proposed_sp = getTargetSp(proposed_tp,mpItem,snapdealPricing.ourSp)
1424 
            proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)) -getSubsidyDiff(snapdealDetails), snapdealPricing.lowestPossibleSp)
1425 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
9881 kshitij.so 1426 
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
9954 kshitij.so 1427 
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
1428 
            mpHistory.proposedTp = round(proposed_tp,2)
9881 kshitij.so 1429 
            #mpHistory.targetNlc = target_nlc
9919 kshitij.so 1430 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
9881 kshitij.so 1431 
        mpHistory.marginIncreasedPotential = proposed_tp - snapdealPricing.ourTp
1432 
        mpHistory.totalSeller = snapdealDetails.totalSeller
9949 kshitij.so 1433 
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
9881 kshitij.so 1434 
        mpHistory.timestamp = timestamp
9949 kshitij.so 1435 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
1436 
        buyBoxList.append(mpHistory)
9881 kshitij.so 1437 
    session.commit()
9949 kshitij.so 1438 
    return buyBoxList
10219 kshitij.so 1439 
 
9990 kshitij.so 1440 
def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
1441 
    xstr = lambda s: s or ""
1442 
    catalog_client = CatalogClient().get_client()
1443 
    inventory_client = InventoryClient().get_client()
1444 
    message="""<html>
1445 
            <body>
1446 
            <h3>Auto Decrease Items</h3>
1447 
            <table border="1" style="width:100%;">
1448 
            <thead>
1449 
            <tr><th>Item Id</th>
1450 
            <th>Product Name</th>
1451 
            <th>Old Price</th>
1452 
            <th>New Price</th>
1453 
            <th>Old Margin</th>
1454 
            <th>New Margin</th>
1455 
            <th>Snapdeal Inventory</th>
1456 
            </tr></thead>
1457 
            <tbody>"""
1458 
    for item in successfulAutoDecrease:
1459 
        it = Item.query.filter_by(id=item.item_id).one()
1460 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
1461 
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
1462 
        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
1463 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
1464 
        newMargin = round(getNewOurTp(mpItem,item.proposedSellingPrice) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice))
1465 
        message+="""<tr>
1466 
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
1467 
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
1468 
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
1469 
                <td style="text-align:center">"""+str(math.ceil(item.proposedSellingPrice))+"""</td>
1470 
                <td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
1471 
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/item.proposedSellingPrice)*100,1))+"%)"+"""</td>
1472 
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
1473 
                </tr>"""
1474 
    message+="""</tbody></table><h3>Auto Increase Items</h3><table border="1" style="width:100%;">
1475 
            <thead>
1476 
            <tr><th>Item Id</th>
1477 
            <th>Product Name</th>
1478 
            <th>Old Price</th>
1479 
            <th>New Price</th>
1480 
            <th>Old Margin</th>
1481 
            <th>New Margin</th>
1482 
            <th>Snapdeal Inventory</th>
1483 
            </tr></thead>
1484 
            <tbody>"""
1485 
    for item in successfulAutoIncrease:
1486 
        it = Item.query.filter_by(id=item.item_id).one()
1487 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
1488 
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
1489 
        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
1490 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
1491 
        newMargin = round(getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
1492 
        message+="""<tr>
1493 
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
1494 
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
1495 
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
1496 
                <td style="text-align:center">"""+str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))+"""</td>
1497 
                <td style="text-align:center">"""+str(round((item.margin),1))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
1498 
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))*100,1))+"%)"+"""</td>
1499 
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
1500 
                </tr>"""
1501 
    message+="""</tbody></table></body></html>"""
1502 
    print message
1503 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
1504 
    mailServer.ehlo()
1505 
    mailServer.starttls()
1506 
    mailServer.ehlo()
1507 
 
10293 kshitij.so 1508 
    #recipients = ['kshitij.sood@saholic.com']
1509 
    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 1510 
    msg = MIMEMultipart()
1511 
    msg['Subject'] = "Snapdeal Auto Pricing" + ' - ' + str(datetime.now())
1512 
    msg['From'] = ""
10005 kshitij.so 1513 
    msg['To'] = ",".join(recipients)
9990 kshitij.so 1514 
    msg.preamble = "Snapdeal Auto Pricing" + ' - ' + str(datetime.now())
1515 
    html_msg = MIMEText(message, 'html')
1516 
    msg.attach(html_msg)
10207 kshitij.so 1517 
    try:
1518 
        mailServer.login("build@shop2020.in", "cafe@nes")
1519 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
1520 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
1521 
    except Exception as e:
1522 
        print e
10219 kshitij.so 1523 
        print "Unable to send pricing mail.Lets try with local SMTP."
1524 
        smtpServer = smtplib.SMTP('localhost')
1525 
        smtpServer.set_debuglevel(1)
1526 
        sender = 'support@shop2020.in'
1527 
        try:
1528 
            smtpServer.sendmail(sender, recipients, msg.as_string())
1529 
            print "Successfully sent email"
1530 
        except:
1531 
            print "Error: unable to send email."
1532 
 
9990 kshitij.so 1533 
 
1534 
def commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp):
1535 
    catalog_client = CatalogClient().get_client()
1536 
    inventory_client = InventoryClient().get_client()
1537 
    for item in successfulAutoDecrease:
1538 
        it = Item.query.filter_by(id=item.item_id).one()
1539 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
1540 
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
1541 
        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
1542 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
1543 
        addHistory(sdItem)
1544 
        sdItem.transferPrice = getNewOurTp(mpItem,item.proposedSellingPrice)
1545 
        sdItem.sellingPrice = math.ceil(item.proposedSellingPrice)
10293 kshitij.so 1546 
        if ((mpItem.pgFee/100)*sdItem.sellingPrice)>=20:
1547 
            sdItem.commission = round((mpItem.commission/100+mpItem.pgFee/100)*(sdItem.sellingPrice),2)
1548 
        else:
1549 
            sdItem.commission = round((mpItem.commission/100)*(sdItem.sellingPrice),2)+20
9990 kshitij.so 1550 
        sdItem.serviceTax = round((mpItem.serviceTax/100)*(sdItem.commission+sdItem.courierCost),2)
1551 
        sdItem.updatedOn = timestamp
1552 
        sdItem.priceUpdatedBy = 'SYSTEM'
1553 
        mpItem.currentSp = sdItem.sellingPrice
1554 
        mpItem.currentTp = sdItem.transferPrice
1555 
        mpItem.minimumPossibleTp = getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice)
1556 
        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,item.ourNlc,vatRate)
1557 
        markStatusForMarketplaceItems(sdItem,mpItem)
1558 
    session.commit()
1559 
    for item in successfulAutoIncrease:
1560 
        it = Item.query.filter_by(id=item.item_id).one()
1561 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
1562 
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
1563 
        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
1564 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
1565 
        addHistory(sdItem)
1566 
        sdItem.transferPrice = getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice))
1567 
        sdItem.sellingPrice = math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice))
10293 kshitij.so 1568 
        if ((mpItem.pgFee/100)*sdItem.sellingPrice)>=20:
1569 
            sdItem.commission = round((mpItem.commission/100+mpItem.pgFee/100)*(sdItem.sellingPrice),2)
1570 
        else:
1571 
            sdItem.commission = round((mpItem.commission/100)*(sdItem.sellingPrice),2)+20
9990 kshitij.so 1572 
        sdItem.serviceTax = round((mpItem.serviceTax/100)*(sdItem.commission+sdItem.courierCost),2)
1573 
        sdItem.updatedOn = timestamp
1574 
        sdItem.priceUpdatedBy = 'SYSTEM'
1575 
        mpItem.currentSp = sdItem.sellingPrice
1576 
        mpItem.currentTp = sdItem.transferPrice
1577 
        mpItem.minimumPossibleTp = getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,sdItem.sellingPrice)
1578 
        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,item.ourNlc,vatRate)
1579 
        markStatusForMarketplaceItems(sdItem,mpItem)
1580 
    session.commit()
1581 
 
10280 kshitij.so 1582 
def updatePricesOnSnapdeal(successfulAutoDecrease,successfulAutoIncrease):
10284 kshitij.so 1583 
    if syncPrice=='false':
10280 kshitij.so 1584 
        return
1585 
    url = 'http://support.shop2020.in:8080/Support/reports'
1586 
    br = getBrowserObject()
1587 
    br.open(url)
1588 
    br.open(url)
1589 
    br.select_form(nr=0)
10286 kshitij.so 1590 
    br.form['username'] = "manoj"
1591 
    br.form['password'] = "man0j"
10280 kshitij.so 1592 
    br.submit()
1593 
    for item in successfulAutoDecrease:
1594 
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
1595 
        sellingPrice =  str(math.ceil(item.proposedSellingPrice))
1596 
        supc = sdItem.supc
10286 kshitij.so 1597 
        updateUrl = 'http://support.shop2020.in:8080/Support/snapdeal-list!updateForAutoPricing?sellingPrice=%s&supc=%s&itemId=%s'%(sellingPrice,supc,str(item.item_id))
1598 
        br.open(updateUrl)
10280 kshitij.so 1599 
    for item in successfulAutoIncrease:
1600 
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
1601 
        sellingPrice =  str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
1602 
        supc = sdItem.supc
1603 
        updateUrl = 'http://support.shop2020.in:8080/Support/snapdeal-list!updateForAutoPricing?sellingPrice=%s&supc=%s&itemId=%s'%(sellingPrice,supc,str(item.item_id))
1604 
        br.open(updateUrl)
1605 
 
1606 
 
1607 
 
9990 kshitij.so 1608 
def addHistory(item):
10097 kshitij.so 1609 
    itemHistory = MarketPlaceUpdateHistory()
9990 kshitij.so 1610 
    itemHistory.item_id = item.item_id
10097 kshitij.so 1611 
    itemHistory.source = OrderSource.SNAPDEAL
9990 kshitij.so 1612 
    itemHistory.exceptionPrice = item.exceptionPrice
1613 
    itemHistory.warehouseId = item.warehouseId
10097 kshitij.so 1614 
    itemHistory.isListedOnSource = item.isListedOnSnapdeal
9990 kshitij.so 1615 
    itemHistory.transferPrice = item.transferPrice
1616 
    itemHistory.sellingPrice = item.sellingPrice
1617 
    itemHistory.courierCost = item.courierCost
1618 
    itemHistory.commission = item.commission
1619 
    itemHistory.serviceTax = item.serviceTax
1620 
    itemHistory.suppressPriceFeed = item.suppressPriceFeed
1621 
    itemHistory.suppressInventoryFeed = item.suppressInventoryFeed
1622 
    itemHistory.updatedOn = item.updatedOn
1623 
    itemHistory.maxNlc = item.maxNlc
10097 kshitij.so 1624 
    itemHistory.skuAtSource = item.skuAtSnapdeal
1625 
    itemHistory.marketPlaceSerialNumber = item.supc
9990 kshitij.so 1626 
    itemHistory.priceUpdatedBy = item.priceUpdatedBy
1627 
 
1628 
def markStatusForMarketplaceItems(snapdealItem,marketplaceItem):
1629 
    markUpdatedItem = MarketPlaceItemPrice.query.filter(MarketPlaceItemPrice.item_id==snapdealItem.item_id).filter(MarketPlaceItemPrice.source==marketplaceItem.source).first()
1630 
    if markUpdatedItem is None:
1631 
        marketPlaceItemPrice = MarketPlaceItemPrice()
1632 
        marketPlaceItemPrice.item_id = snapdealItem.item_id
1633 
        marketPlaceItemPrice.source = marketplaceItem.source
1634 
        marketPlaceItemPrice.lastUpdatedOn = snapdealItem.updatedOn
1635 
        marketPlaceItemPrice.sellingPrice = snapdealItem.sellingPrice
1636 
        marketPlaceItemPrice.suppressPriceFeed = snapdealItem.suppressPriceFeed
1637 
        marketPlaceItemPrice.isListedOnSource = snapdealItem.isListedOnSnapdeal
1638 
    else:
1639 
        if (markUpdatedItem.sellingPrice!=snapdealItem.sellingPrice or markUpdatedItem.suppressPriceFeed!=snapdealItem.suppressPriceFeed or markUpdatedItem.isListedOnSource!=snapdealItem.isListedOnSnapdeal):
1640 
            markUpdatedItem.lastUpdatedOn = snapdealItem.updatedOn
1641 
        markUpdatedItem.sellingPrice = snapdealItem.sellingPrice
1642 
        markUpdatedItem.suppressPriceFeed = snapdealItem.suppressPriceFeed
1643 
        markUpdatedItem.isListedOnSource = snapdealItem.isListedOnSnapdeal
10031 kshitij.so 1644 
 
1645 
def processLostBuyBoxItems(previousProcessingTimestamp,currentTimestamp):
1646 
    previous_buy_box = session.query(MarketPlaceHistory.item_id).filter(MarketPlaceHistory.timestamp==previousProcessingTimestamp).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
1647 
    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 1648 
    lost_buy_box = list(set(list(zip(*previous_buy_box)[0]))&set(list(zip(*cant_compete)[0])))
10031 kshitij.so 1649 
    if len(lost_buy_box)==0:
1650 
        return
1651 
    xstr = lambda s: s or ""
1652 
    message="""<html>
1653 
            <body>
1654 
            <h3>Lost Buy Box</h3>
1655 
            <table border="1" style="width:100%;">
1656 
            <thead>
1657 
            <tr><th>Item Id</th>
1658 
            <th>Product Name</th>
1659 
            <th>Current Price</th>
1660 
            <th>Current TP</th>
1661 
            <th>Current Margin</th>
1662 
            <th>Competition TP</th>
1663 
            <th>Lowest Possible TP</th>
1664 
            <th>NLC</th>
1665 
            <th>Target NLC</th>
1666 
            <th>Snapdeal Inventory</th>
1667 
            <th>Total Inventory</th>
1668 
            <th>Sales History</th>
1669 
            </tr></thead>
1670 
            <tbody>"""
1671 
    items = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==currentTimestamp).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.item_id.in_(lost_buy_box)).all()
1672 
    for item in items:
1673 
        it = Item.query.filter_by(id=item.item_id).one()
1674 
        netInventory=''
1675 
        if not inventoryMap.has_key(item.item_id):
1676 
            netInventory='Info Not Available'
1677 
        else:
1678 
            netInventory = str(getNetAvailability(inventoryMap.get(item.item_id)))
1679 
        message+="""<tr>
1680 
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
1681 
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
1682 
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
1683 
                <td style="text-align:center">"""+str(item.ourTp)+"""</td>
1684 
                <td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
1685 
                <td style="text-align:center">"""+str(item.lowestTp)+"""</td>
1686 
                <td style="text-align:center">"""+str(item.lowestPossibleTp)+"""</td>
1687 
                <td style="text-align:center">"""+str(item.ourNlc)+"""</td>
1688 
                <td style="text-align:center">"""+str(item.targetNlc)+"""</td>
1689 
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
1690 
                <td style="text-align:center">"""+netInventory+"""</td>
1691 
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
1692 
                </tr>"""
1693 
    message+="""</tbody></table></body></html>"""
1694 
    print message
1695 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
1696 
    mailServer.ehlo()
1697 
    mailServer.starttls()
1698 
    mailServer.ehlo()
1699 
 
10033 kshitij.so 1700 
    #recipients = ['kshitij.sood@saholic.com']
1701 
    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 1702 
    msg = MIMEMultipart()
1703 
    msg['Subject'] = "Snapdeal Lost Buy Box" + ' - ' + str(datetime.now())
1704 
    msg['From'] = ""
1705 
    msg['To'] = ",".join(recipients)
1706 
    msg.preamble = "Snapdeal Lost Buy Box" + ' - ' + str(datetime.now())
1707 
    html_msg = MIMEText(message, 'html')
1708 
    msg.attach(html_msg)
10207 kshitij.so 1709 
    try:
1710 
        mailServer.login("build@shop2020.in", "cafe@nes")
1711 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
1712 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
1713 
    except Exception as e:
1714 
        print e
10381 manish.sha 1715 
        print "Unable to send lost buy box mail.Lets try local SMTP"
10219 kshitij.so 1716 
        smtpServer = smtplib.SMTP('localhost')
1717 
        smtpServer.set_debuglevel(1)
1718 
        sender = 'support@shop2020.in'
1719 
        try:
1720 
            smtpServer.sendmail(sender, recipients, msg.as_string())
1721 
            print "Successfully sent email"
1722 
        except:
1723 
            print "Error: unable to send email."
1724 
 
10381 manish.sha 1725 
def getSubsidyDiff(snapdealDetails):
1726 
    ourSubsidy = snapdealDetails.ourSp - snapdealDetails.ourOfferPrice
1727 
    if snapdealDetails.rank !=1:
1728 
        competitionSubsidy = snapdealDetails.lowestSp - snapdealDetails.lowestOfferPrice
1729 
    else:
10386 manish.sha 1730 
        competitionSubsidy = snapdealDetails.secondLowestSellerSp - snapdealDetails.secondLowestSellerOfferPrice
10381 manish.sha 1731 
    return competitionSubsidy - ourSubsidy
9881 kshitij.so 1732 
 
1733 
def getOtherTp(snapdealDetails,val,spm):
10473 kshitij.so 1734 
    if val.parent_category==10011 or val.parent_category==12001:
9881 kshitij.so 1735 
        commissionPercentage = spm.competitorCommissionAccessory
1736 
    else:
1737 
        commissionPercentage = spm.competitorCommissionOther
1738 
    if snapdealDetails.rank==1:
10289 kshitij.so 1739 
        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 1740 
        return round(otherTp,2)
10289 kshitij.so 1741 
    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 1742 
    return round(otherTp,2)
9881 kshitij.so 1743 
 
1744 
def getLowestPossibleTp(snapdealDetails,val,spm,mpItem):
1745 
    if snapdealDetails.rank==0:
1746 
        return mpItem.minimumPossibleTp
1747 
    vat = (snapdealDetails.ourSp/(1+(val.vatRate/100))-(val.nlc/(1+(val.vatRate/100))))*(val.vatRate/100);
1748 
    inHouseCost = 15+vat+(mpItem.returnProvision/100)*snapdealDetails.ourSp+mpItem.otherCost;
1749 
    lowest_possible_tp = val.nlc+inHouseCost;
9954 kshitij.so 1750 
    return round(lowest_possible_tp,2)
9881 kshitij.so 1751 
 
1752 
def getOurTp(snapdealDetails,val,spm,mpItem):
1753 
    if snapdealDetails.rank==0:
1754 
        return mpItem.currentTp
10289 kshitij.so 1755 
    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 1756 
    return round(ourTp,2)
9966 kshitij.so 1757 
 
1758 
def getNewLowestPossibleTp(mpItem,nlc,vatRate,proposedSellingPrice):
1759 
    vat = (proposedSellingPrice/(1+(vatRate/100))-(nlc/(1+(vatRate/100))))*(vatRate/100);
1760 
    inHouseCost = 15+vat+(mpItem.returnProvision/100)*proposedSellingPrice+mpItem.otherCost;
1761 
    lowest_possible_tp = nlc+inHouseCost;
1762 
    return round(lowest_possible_tp,2)
1763 
 
1764 
def getNewOurTp(mpItem,proposedSellingPrice):
10289 kshitij.so 1765 
    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 1766 
    return round(ourTp,2)
9990 kshitij.so 1767 
 
1768 
def getNewLowestPossibleSp(mpItem,nlc,vatRate):
10294 kshitij.so 1769 
    if (mpItem.pgFee/100)*mpItem.currentSp>=20:
10289 kshitij.so 1770 
        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));
1771 
    else:
1772 
        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 1773 
    return round(lowestPossibleSp,2)
9881 kshitij.so 1774 
 
1775 
def getLowestPossibleSp(snapdealDetails,val,spm,mpItem):
1776 
    if snapdealDetails.rank==0:
1777 
        return mpItem.minimumPossibleSp
10289 kshitij.so 1778 
    #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));
1779 
    if (mpItem.pgFee/100)*snapdealDetails.ourSp>=20:
1780 
        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))
1781 
    else:
1782 
        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 1783 
    return round(lowestPossibleSp,2)
9881 kshitij.so 1784 
 
1785 
def getTargetTp(targetSp,mpItem):
10289 kshitij.so 1786 
    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 1787 
    return round(targetTp,2)
9881 kshitij.so 1788 
 
10289 kshitij.so 1789 
def getTargetSp(targetTp,mpItem,ourSp):
10292 kshitij.so 1790 
    if (ourSp*(mpItem.pgFee/100)) < 20:
10289 kshitij.so 1791 
        targetSp = float(targetTp+(mpItem.courierCost+mpItem.closingFee+20)*(1+(mpItem.serviceTax/100)))/(1-((mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))))
1792 
    else:
1793 
        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 1794 
    return round(targetSp,2)
9881 kshitij.so 1795 
 
1796 
def getSalesPotential(lowestOfferPrice,ourNlc):
1797 
    if lowestOfferPrice - ourNlc < 0:
1798 
        return 'HIGH'
9897 kshitij.so 1799 
    elif (float(lowestOfferPrice - ourNlc))/lowestOfferPrice >=0 and (float(lowestOfferPrice - ourNlc))/lowestOfferPrice <=.02:
9881 kshitij.so 1800 
        return 'MEDIUM'
1801 
    else:
1802 
        return 'LOW'
1803 
 
1804 
def main():
9949 kshitij.so 1805 
    parser = optparse.OptionParser()
1806 
    parser.add_option("-t", "--type", dest="runType",
1807 
                   default="FULL", type="string",
1808 
                   help="Run type FULL or FAVOURITE")
1809 
    (options, args) = parser.parse_args()
1810 
    if options.runType not in ('FULL','FAVOURITE'):
1811 
        print "Run type argument illegal."
1812 
        sys.exit(1)
10289 kshitij.so 1813 
    timestamp = datetime.now()
1814 
    itemInfo= populateStuff(options.runType,timestamp)
9881 kshitij.so 1815 
    cantCompete, buyBoxItems, competitive, \
10289 kshitij.so 1816 
    competitiveNoInventory, exceptionList, negativeMargin = decideCategory(itemInfo)
10759 kshitij.so 1817 
    previousProcessingTimestamp = session.query(func.max(MarketPlaceHistory.timestamp)).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).one()
9949 kshitij.so 1818 
    exceptionItems = commitExceptionList(exceptionList,timestamp)
1819 
    cantComepeteItems = commitCantCompete(cantCompete,timestamp)
1820 
    buyBoxList = commitBuyBox(buyBoxItems,timestamp)
1821 
    competitiveItems = commitCompetitive(competitive,timestamp)
1822 
    competitiveNoInventoryItems = commitCompetitiveNoInventory(competitiveNoInventory,timestamp)
1823 
    negativeMarginItems = commitNegativeMargin(negativeMargin,timestamp)
9954 kshitij.so 1824 
    successfulAutoDecrease = fetchItemsForAutoDecrease(timestamp)
1825 
    successfulAutoIncrease = fetchItemsForAutoIncrease(timestamp)
9949 kshitij.so 1826 
    if options.runType=='FULL':
1827 
        previousAutoFav, nowAutoFav = markAutoFavourite()
9954 kshitij.so 1828 
    if options.runType=='FULL':
1829 
        writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, previousAutoFav, nowAutoFav,timestamp, options.runType)
1830 
    else:
1831 
        writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, None, None, timestamp, options.runType)
10293 kshitij.so 1832 
    commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp)
9954 kshitij.so 1833 
    sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease)
10031 kshitij.so 1834 
    processLostBuyBoxItems(previousProcessingTimestamp[0],timestamp)
10293 kshitij.so 1835 
    updatePricesOnSnapdeal(successfulAutoDecrease,successfulAutoIncrease)
9897 kshitij.so 1836 
 
9881 kshitij.so 1837 
if __name__ == '__main__':
10759 kshitij.so 1838 
    main()