Subversion Repositories SmartDukaan

Rev

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