Subversion Repositories SmartDukaan

Rev

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