Subversion Repositories SmartDukaan

Rev

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

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