Subversion Repositories SmartDukaan

Rev

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

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