Subversion Repositories SmartDukaan

Rev

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