Subversion Repositories SmartDukaan

Rev

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

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