Subversion Repositories SmartDukaan

Rev

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

Rev Author Line No. Line
11193 kshitij.so 1 
from elixir import *
2 
from sqlalchemy.sql import or_ ,func, asc
3 
from shop2020.config.client.ConfigClient import ConfigClient
4 
from shop2020.model.v1.catalog.impl import DataService
5 
from shop2020.model.v1.catalog.script import FlipkartScraper
6 
from shop2020.model.v1.catalog.impl.DataService import FlipkartItem, MarketplaceItems, Item, \
7 
Category, SourcePercentageMaster, MarketPlaceHistory, MarketPlaceUpdateHistory, MarketPlaceItemPrice, \
12133 kshitij.so 8 
SourceCategoryPercentage, SourceItemPercentage, SourceReturnPercentage
11193 kshitij.so 9 
from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
10 
from shop2020.thriftpy.model.v1.catalog.ttypes import CompetitionCategory, CompetitionBasis, SalesPotential,\
11 
Decision, RunType
12 
from shop2020.clients.CatalogClient import CatalogClient
13 
from shop2020.clients.InventoryClient import InventoryClient
14 
import urllib2
15 
import requests
16 
import time
17 
from datetime import date, datetime, timedelta
18 
from shop2020.utils import EmailAttachmentSender
19 
from shop2020.utils.EmailAttachmentSender import get_attachment_part
20 
import math
21 
from operator import itemgetter
11560 kshitij.so 22 
from functools import partial
11193 kshitij.so 23 
import simplejson as json
24 
import xlwt
25 
import optparse
26 
import sys
27 
import smtplib
11560 kshitij.so 28 
import threading
29 
from multiprocessing import Process
11193 kshitij.so 30 
from email.mime.text import MIMEText
31 
import email
32 
from email.mime.multipart import MIMEMultipart
33 
import email.encoders
34 
import cookielib
11561 kshitij.so 35 
from multiprocessing import Pool
12218 kshitij.so 36 
from multiprocessing.dummy import Pool as ThreadPool
37 
import gc
12322 kshitij.so 38 
import mechanize
11193 kshitij.so 39 
 
40 
config_client = ConfigClient()
41 
host = config_client.get_property('staging_hostname')
42 
syncPrice=config_client.get_property('sync_price_on_marketplace')
43 
 
44 
 
45 
DataService.initialize(db_hostname=host)
46 
 
47 
inventoryMap = {}
48 
itemSaleMap = {}
11615 kshitij.so 49 
categoryMap = {}
11193 kshitij.so 50 
 
51 
class __FlipkartDetails:
52 
 
53 
    def __init__(self,rank ,ourSp , secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, shippingTimeLowerLimitLowestSeller,shippingTimeUpperLimitLowestSeller, \
54 
    shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller, shippingTimeLowerLimitOur, shippingTimeUpperLimitOur, shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller, totalAvailableSeller, lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
55 
    ourBuyTrend, prefSellerBuyTrend, secondLowestSellerBuyTrend, ourCode ):
56 
 
57 
        self.rank = rank
58 
        self.ourSp = ourSp
59 
        self.secondLowestSellerSp = secondLowestSellerSp
60 
        self.prefSellerSp = prefSellerSp
61 
        self.lowestSellerSp = lowestSellerSp
62 
        self.lowestSellerScore = lowestSellerScore
63 
        self.prefSellerScore = prefSellerScore
64 
        self.secondLowestSellerScore = secondLowestSellerScore
65 
        self.ourScore = ourScore
66 
        self.shippingTimeLowerLimitLowestSeller = shippingTimeLowerLimitLowestSeller
67 
        self.shippingTimeUpperLimitLowestSeller = shippingTimeUpperLimitLowestSeller
68 
        self.shippingTimeLowerLimitPrefSeller = shippingTimeLowerLimitPrefSeller
69 
        self.shippingTimeUpperLimitPrefSeller = shippingTimeUpperLimitPrefSeller
70 
        self.shippingTimeLowerLimitOur = shippingTimeLowerLimitOur
71 
        self.shippingTimeUpperLimitOur = shippingTimeUpperLimitOur
72 
        self.shippingTimeLowerLimitSecondLowestSeller = shippingTimeLowerLimitSecondLowestSeller
73 
        self.shippingTimeUpperLimitSecondLowestSeller = shippingTimeUpperLimitSecondLowestSeller
74 
        self.totalAvailableSeller = totalAvailableSeller
75 
        self.lowestSellerName = lowestSellerName
76 
        self.lowestSellerCode = lowestSellerCode
77 
        self.secondLowestSellerName = secondLowestSellerName
78 
        self.secondLowestSellerCode = secondLowestSellerCode
79 
        self.prefSellerName = prefSellerName
80 
        self.prefSellerCode = prefSellerCode
81 
        self.lowestSellerBuyTrend = lowestSellerBuyTrend
82 
        self.ourBuyTrend = ourBuyTrend
83 
        self.prefSellerBuyTrend = prefSellerBuyTrend
84 
        self.secondLowestSellerBuyTrend = secondLowestSellerBuyTrend
85 
        self.ourCode = ourCode
86 
 
87 
class __FlipkartItemInfo:
88 
 
11581 kshitij.so 89 
    def __init__(self, fkSerialNumber, nlc, courierCost, item_id, product_group, brand, model_name, model_number, color, weight, parent_category, risky, warehouseId, vatRate, runType, parent_category_name, sourcePercentage, ourFlipkartInventory, skuAtFlipkart, flipkartDetails, stateId):
11193 kshitij.so 90 
 
91 
        self.fkSerialNumber = fkSerialNumber
92 
        self.nlc = nlc
93 
        self.courierCost = courierCost
94 
        self.item_id = item_id
95 
        self.product_group = product_group
96 
        self.brand = brand
97 
        self.model_name = model_name
98 
        self.model_number = model_number
99 
        self.color = color
100 
        self.weight = weight
101 
        self.parent_category = parent_category
102 
        self.risky = risky
103 
        self.warehouseId = warehouseId
104 
        self.vatRate = vatRate
105 
        self.runType = runType
106 
        self.parent_category_name = parent_category_name
107 
        self.sourcePercentage = sourcePercentage
11560 kshitij.so 108 
        self.ourFlipkartInventory = ourFlipkartInventory
11571 kshitij.so 109 
        self.skuAtFlipkart = skuAtFlipkart
11581 kshitij.so 110 
        self.flipkartDetails = flipkartDetails
111 
        self.stateId = stateId
11193 kshitij.so 112 
 
113 
class __FlipkartPricing:
114 
 
115 
    def __init__(self, ourSp, ourTp, lowestTp, lowestPossibleTp, secondLowestSellerTp, lowestPossibleSp, prefSellerTp):
116 
        self.ourTp = ourTp
117 
        self.lowestTp = lowestTp
118 
        self.lowestPossibleTp = lowestPossibleTp
119 
        self.ourSp = ourSp
120 
        self.secondLowestSellerTp = secondLowestSellerTp
121 
        self.lowestPossibleSp = lowestPossibleSp
122 
        self.prefSellerTp = prefSellerTp
123 
 
12322 kshitij.so 124 
def getBrowserObject():
125 
    br = mechanize.Browser(factory=mechanize.RobustFactory())
126 
    cj = cookielib.LWPCookieJar()
127 
    br.set_cookiejar(cj)
128 
    br.set_handle_equiv(True)
129 
    br.set_handle_redirect(True)
130 
    br.set_handle_referer(True)
131 
    br.set_handle_robots(False)
132 
    br.set_debug_http(False)
133 
    br.set_debug_redirects(False)
134 
    br.set_debug_responses(False)
135 
 
136 
    br.set_handle_refresh(mechanize._http.HTTPRefreshProcessor(), max_time=1)
137 
 
138 
    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'),
139 
                     ('Accept', 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8'),
140 
                     ('Accept-Encoding', 'gzip,deflate,sdch'),
141 
                     ('Accept-Language', 'en-US,en;q=0.8'),
142 
                     ('Accept-Charset', 'ISO-8859-1,utf-8;q=0.7,*;q=0.3')]
143 
    return br
144 
 
11193 kshitij.so 145 
def markReasonForMpItem(mpHistory,reason,decision):
146 
    mpHistory.decision = decision
147 
    mpHistory.reason = reason
148 
 
149 
def fetchItemsForAutoDecrease(time):
150 
    successfulAutoDecrease = []
151 
    autoDecrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
11615 kshitij.so 152 
    .filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(or_(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE,MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP ))\
11193 kshitij.so 153 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketplaceItems.autoDecrement==True).all()
154 
    inventory_client = InventoryClient().get_client()
155 
    global inventoryMap
156 
    inventoryMap = inventory_client.getInventorySnapshot(0)
157 
    for autoDecrementItem in autoDecrementItems:
11825 kshitij.so 158 
#        if not autoDecrementItem.risky:
159 
#            markReasonForMpItem(autoDecrementItem,'Item is not risky',Decision.AUTO_DECREMENT_FAILED)
160 
#            continue
11193 kshitij.so 161 
        if math.ceil(autoDecrementItem.proposedSellingPrice) >= autoDecrementItem.ourSellingPrice:
162 
            markReasonForMpItem(autoDecrementItem,'Proposed SP greater than or equal to current SP',Decision.AUTO_DECREMENT_FAILED)
163 
            continue
164 
        if autoDecrementItem.proposedSellingPrice < autoDecrementItem.lowestPossibleSp:
165 
            markReasonForMpItem(autoDecrementItem,'Proposed SP less than lowest possible SP',Decision.AUTO_DECREMENT_FAILED)
166 
            continue
11615 kshitij.so 167 
        if autoDecrementItem.competitiveCategory == CompetitionCategory.PREF_BUT_NOT_CHEAP:
168 
            avgSaleLastTwoDay = (itemSaleMap.get(autoDecrementItem.item_id))[6]
12317 kshitij.so 169 
            if avgSaleLastTwoDay >= .5:
11615 kshitij.so 170 
                markReasonForMpItem(autoDecrementItem,'Last two day avg sale is greater than 2',Decision.AUTO_DECREMENT_FAILED)
171 
                continue
12317 kshitij.so 172 
        totalAvailability, totalReserved = 0,0
173 
        if autoDecrementItem.risky:
174 
            if (not inventoryMap.has_key(autoDecrementItem.item_id)):
175 
                markReasonForMpItem(autoDecrementItem,'Inventory info not available',Decision.AUTO_DECREMENT_FAILED)
176 
                continue
177 
            itemInventory=inventoryMap[autoDecrementItem.item_id]
178 
            availableMap  = itemInventory.availability
179 
            reserveMap = itemInventory.reserved
180 
            for warehouse,availability in availableMap.iteritems():
181 
                if warehouse==16 or warehouse==1771:
182 
                    continue
183 
                totalAvailability = totalAvailability+availability
184 
            for warehouse,reserve in reserveMap.iteritems():
185 
                if warehouse==16 or warehouse==1771:
186 
                    continue
187 
                totalReserved = totalReserved+reserve
188 
            if (totalAvailability-totalReserved)<=0:
189 
                markReasonForMpItem(autoDecrementItem,'Net availability is 0',Decision.AUTO_DECREMENT_FAILED)
190 
                continue
191 
            avgSalePerDay = (itemSaleMap.get(autoDecrementItem.item_id))[2]
192 
            try:
193 
                daysOfStock = (float(totalAvailability-totalReserved))/avgSalePerDay
194 
            except ZeroDivisionError,e:
195 
                daysOfStock = float("inf")
196 
            if daysOfStock<2 and autoDecrementItem.risky:
197 
                markReasonForMpItem(autoDecrementItem,'Our stock is not enough',Decision.AUTO_DECREMENT_FAILED)
198 
                continue
11193 kshitij.so 199 
 
200 
        autoDecrementItem.ourEnoughStock = True
201 
        autoDecrementItem.decision = Decision.AUTO_DECREMENT_SUCCESS
202 
        autoDecrementItem.reason = 'All conditions for auto decrement true'
203 
        successfulAutoDecrease.append(autoDecrementItem)
204 
    session.commit()
205 
    return successfulAutoDecrease
206 
 
207 
def fetchItemsForAutoIncrease(time):
208 
    successfulAutoIncrease = []
209 
    autoIncrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
210 
    .filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX)\
211 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketplaceItems.autoIncrement==True).all()
212 
    for autoIncrementItem in autoIncrementItems:
213 
        if not autoIncrementItem.competitiveCategory == CompetitionCategory.BUY_BOX:
214 
            markReasonForMpItem(autoIncrementItem,'Category is '+CompetitionCategory._VALUES_TO_NAMES.get(autoIncrementItem.competitiveCategory),Decision.AUTO_INCREMENT_FAILED)
215 
            continue
216 
        if autoIncrementItem.totalSeller==1 and autoIncrementItem.ourRank==1:
217 
            markReasonForMpItem(autoIncrementItem,'We are the only seller',Decision.AUTO_INCREMENT_FAILED)
218 
            continue
219 
        if autoIncrementItem.proposedSellingPrice <= autoIncrementItem.ourSellingPrice:
220 
            markReasonForMpItem(autoIncrementItem,'Proposed SP less than current SP',Decision.AUTO_INCREMENT_FAILED)
221 
            continue
222 
        if autoIncrementItem.proposedSellingPrice >=10000 and autoIncrementItem.ourSellingPrice<10000:
223 
            markReasonForMpItem(autoIncrementItem,'Proposed SP is greater than 10,000 and current sp is less than 10,000',Decision.AUTO_INCREMENT_FAILED)
224 
            continue
225 
        if getLastDaySale(autoIncrementItem.item_id)<=2:
226 
            markReasonForMpItem(autoIncrementItem,'Last day sale is less than 3',Decision.AUTO_INCREMENT_FAILED)
227 
            continue
228 
        antecedentPrice = session.query(MarketPlaceHistory.ourSellingPrice).filter(MarketPlaceHistory.item_id==autoIncrementItem.item_id).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.timestamp>time-timedelta(days=1)).order_by(asc(MarketPlaceHistory.timestamp)).first()
229 
        if antecedentPrice is not None:
230 
            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:
231 
                markReasonForMpItem(autoIncrementItem,'Maximum price increase in last 24 hours should be 2%',Decision.AUTO_INCREMENT_FAILED)
232 
                continue
233 
        mpItem = MarketplaceItems.get_by(itemId=autoIncrementItem.item_id,source=OrderSource.FLIPKART)
234 
        if mpItem.maximumSellingPrice is not None and mpItem.maximumSellingPrice > 0:
235 
            if autoIncrementItem.ourSellingPrice+max(10,.01*autoIncrementItem.ourSellingPrice) > mpItem.maximumSellingPrice:
236 
                markReasonForMpItem(autoIncrementItem,'Price cannot exceed Maximum Selling Price',Decision.AUTO_INCREMENT_FAILED)
237 
                continue
238 
        #oosStatus = inventory_client.getOosStatusesForXDaysForItem(autoIncrementItem.item_id,0,3)
239 
        #count,sale,daysOfStock = 0,0,0
240 
        #for obj in oosStatus:
241 
        #    if not obj.is_oos:
242 
        #        count+=1
243 
        #        sale = sale+obj.num_orders
244 
        #avgSalePerDay=0 if count==0 else (float(sale)/count)
245 
        totalAvailability, totalReserved = 0,0
12317 kshitij.so 246 
        if autoIncrementItem.risky:
247 
            if (not inventoryMap.has_key(autoIncrementItem.item_id)):
248 
                markReasonForMpItem(autoIncrementItem,'Inventory info not available',Decision.AUTO_INCREMENT_FAILED)
11193 kshitij.so 249 
                continue
12317 kshitij.so 250 
            itemInventory=inventoryMap[autoIncrementItem.item_id]
251 
            availableMap  = itemInventory.availability
252 
            reserveMap = itemInventory.reserved
253 
            for warehouse,availability in availableMap.iteritems():
254 
                if warehouse==16 or warehouse==1771:
255 
                    continue
256 
                totalAvailability = totalAvailability+availability
257 
            for warehouse,reserve in reserveMap.iteritems():
258 
                if warehouse==16 or warehouse==1771:
259 
                    continue
260 
                totalReserved = totalReserved+reserve
261 
            #if (totalAvailability-totalReserved)<=0:
262 
            #    markReasonForMpItem(autoIncrementItem,'Our stock is 0',Decision.AUTO_INCREMENT_FAILED)
263 
            #    continue
264 
            avgSalePerDay = (itemSaleMap.get(autoIncrementItem.item_id))[2]
265 
            if (avgSalePerDay==0):
266 
                markReasonForMpItem(autoIncrementItem,'Average sale per day is zero',Decision.AUTO_INCREMENT_FAILED)
11193 kshitij.so 267 
                continue
12317 kshitij.so 268 
            daysOfStock = (float(totalAvailability-totalReserved))/avgSalePerDay
269 
            if daysOfStock>5:
270 
                markReasonForMpItem(autoIncrementItem,'Our stock is enough',Decision.AUTO_INCREMENT_FAILED)
271 
                continue
11193 kshitij.so 272 
 
273 
        autoIncrementItem.ourEnoughStock = False
274 
        autoIncrementItem.decision = Decision.AUTO_INCREMENT_SUCCESS
275 
        autoIncrementItem.reason = 'All conditions for auto increment true'
276 
        successfulAutoIncrease.append(autoIncrementItem)
277 
    session.commit()
278 
    return successfulAutoIncrease
279 
 
280 
 
281 
def commitExceptionList(exceptionList,timestamp):
282 
    exceptionItems=[]
283 
    for item in exceptionList:
284 
        mpHistory = MarketPlaceHistory()
285 
        mpHistory.item_id =item.item_id
286 
        mpHistory.source = OrderSource.FLIPKART
287 
        mpHistory.competitiveCategory = CompetitionCategory.EXCEPTION
288 
        mpHistory.risky = item.risky
289 
        mpHistory.timestamp = timestamp
290 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(item.runType)
291 
        exceptionItems.append(mpHistory)
292 
    session.commit()
293 
    return exceptionItems
294 
 
295 
def commitCantCompete(cantCompete,timestamp):
296 
    for item in cantCompete:
297 
        flipkartDetails = item[0]
298 
        flipkartItemInfo = item[1]
299 
        flipkartPricing = item[2]
300 
        mpItem = item[3]
301 
        mpHistory = MarketPlaceHistory()
302 
        mpHistory.item_id = flipkartItemInfo.item_id
303 
        mpHistory.source = OrderSource.FLIPKART
304 
        mpHistory.lowestTp = flipkartPricing.lowestTp
305 
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
306 
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
307 
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
308 
        mpHistory.ourRank = flipkartDetails.rank
309 
        mpHistory.competitiveCategory = CompetitionCategory.CANT_COMPETE
310 
        mpHistory.risky = flipkartItemInfo.risky
311 
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
312 
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
313 
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
314 
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
315 
        mpHistory.lowestSellerShippingTime = ''
316 
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
317 
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
318 
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
319 
        mpHistory.ourTp = flipkartPricing.ourTp
320 
        mpHistory.ourNlc = flipkartItemInfo.nlc
321 
        mpHistory.ourRating = flipkartDetails.ourScore
322 
        mpHistory.ourShippingTime = ''
323 
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
324 
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
325 
        mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
326 
        mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
327 
        mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
328 
        mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
329 
        mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
330 
        mpHistory.prefferedSellerShippingTime = ''
331 
        mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
332 
        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
333 
        proposed_sp = flipkartDetails.lowestSellerSp - max(10, flipkartDetails.lowestSellerSp*0.001)
334 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
335 
        target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
336 
        mpHistory.proposedSellingPrice = round(proposed_sp,2)
337 
        mpHistory.proposedTp = round(proposed_tp,2)
338 
        mpHistory.targetNlc = round(target_nlc,2)
339 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
340 
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
341 
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
342 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
343 
        mpHistory.timestamp = timestamp
344 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
345 
    session.commit()
346 
 
347 
def commitBuyBox(buyBoxItems,timestamp):
348 
    for item in buyBoxItems:
349 
        flipkartDetails = item[0]
350 
        flipkartItemInfo = item[1]
351 
        flipkartPricing = item[2]
352 
        mpItem = item[3]
353 
        mpHistory = MarketPlaceHistory()
354 
        mpHistory.item_id = flipkartItemInfo.item_id
355 
        mpHistory.source = OrderSource.FLIPKART
356 
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
357 
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
358 
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
359 
        mpHistory.ourRank = flipkartDetails.rank
360 
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
361 
        mpHistory.ourTp = flipkartPricing.ourTp
362 
        mpHistory.ourNlc = flipkartItemInfo.nlc
363 
        mpHistory.ourRating = flipkartDetails.ourScore
364 
        mpHistory.ourShippingTime = ''
365 
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
366 
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
367 
        mpHistory.competitiveCategory = CompetitionCategory.BUY_BOX
368 
        mpHistory.risky = flipkartItemInfo.risky
369 
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
370 
        mpHistory.lowestTp = flipkartPricing.lowestTp
371 
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
372 
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
373 
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
374 
        mpHistory.lowestSellerShippingTime = ''
375 
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
376 
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
377 
        proposed_sp = max(flipkartDetails.secondLowestSellerSp - max((20, flipkartDetails.secondLowestSellerSp*0.002)), flipkartPricing.lowestPossibleSp)
378 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
379 
        #target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
380 
        mpHistory.proposedSellingPrice = round(proposed_sp,2)
381 
        mpHistory.proposedTp = round(proposed_tp,2)
382 
        #mpHistory.targetNlc = target_nlc
383 
        mpHistory.secondLowestSellerName = flipkartDetails.secondLowestSellerName
384 
        mpHistory.secondLowestSellerCode = flipkartDetails.secondLowestSellerCode
385 
        mpHistory.secondLowestSellingPrice = flipkartDetails.secondLowestSellerSp
386 
        mpHistory.secondLowestTp = flipkartPricing.secondLowestSellerTp
387 
        mpHistory.secondLowestSellerRating = flipkartDetails.secondLowestSellerScore
388 
        mpHistory.secondLowestSellerShippingTime = ''
389 
        mpHistory.secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
390 
        else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
391 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
392 
        mpHistory.marginIncreasedPotential = proposed_tp - flipkartPricing.ourTp
393 
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
394 
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
395 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
396 
        mpHistory.timestamp = timestamp
397 
    session.commit()
398 
 
399 
def commitCompetitiveNoInventory(competitiveNoInventory,timestamp):
400 
    for item in competitiveNoInventory:
401 
        flipkartDetails = item[0]
402 
        flipkartItemInfo = item[1]
403 
        flipkartPricing = item[2]
404 
        mpItem = item[3]
405 
        mpHistory = MarketPlaceHistory()
406 
        mpHistory.item_id = flipkartItemInfo.item_id
407 
        mpHistory.source = OrderSource.FLIPKART
408 
        mpHistory.lowestTp = flipkartPricing.lowestTp
409 
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
410 
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
411 
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
412 
        mpHistory.ourRank = flipkartDetails.rank
413 
        mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE_NO_INVENTORY
414 
        mpHistory.risky = flipkartItemInfo.risky
415 
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
416 
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
417 
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
418 
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
419 
        mpHistory.lowestSellerShippingTime = ''
420 
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
421 
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
422 
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
423 
        mpHistory.ourTp = flipkartPricing.ourTp
424 
        mpHistory.ourNlc = flipkartItemInfo.nlc
425 
        mpHistory.ourRating = flipkartDetails.ourScore
426 
        mpHistory.ourShippingTime = ''
427 
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
428 
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
429 
        mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
430 
        mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
431 
        mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
432 
        mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
433 
        mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
434 
        mpHistory.prefferedSellerShippingTime = ''
435 
        mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
436 
        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
437 
        proposed_sp = max(flipkartDetails.lowestSellerSp - max((10, flipkartDetails.lowestSellerSp*0.001)), flipkartPricing.lowestPossibleSp)
438 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
439 
        mpHistory.proposedSellingPrice = round(proposed_sp,2)
440 
        mpHistory.proposedTp = round(proposed_tp,2)
441 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
442 
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
443 
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
444 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
445 
        mpHistory.timestamp = timestamp
446 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
447 
    session.commit()
448 
 
449 
def commitCompetitive(competitive,timestamp):
450 
    for item in competitive:
451 
        flipkartDetails = item[0]
452 
        flipkartItemInfo = item[1]
453 
        flipkartPricing = item[2]
454 
        mpItem = item[3]
455 
        mpHistory = MarketPlaceHistory()
456 
        mpHistory.item_id = flipkartItemInfo.item_id
457 
        mpHistory.source = OrderSource.FLIPKART
458 
        mpHistory.lowestTp = flipkartPricing.lowestTp
459 
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
460 
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
461 
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
462 
        mpHistory.ourRank = flipkartDetails.rank
463 
        mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE
464 
        mpHistory.risky = flipkartItemInfo.risky
465 
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
466 
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
467 
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
468 
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
469 
        mpHistory.lowestSellerShippingTime = ''
470 
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
471 
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
472 
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
473 
        mpHistory.ourTp = flipkartPricing.ourTp
474 
        mpHistory.ourNlc = flipkartItemInfo.nlc
475 
        mpHistory.ourRating = flipkartDetails.ourScore
476 
        mpHistory.ourShippingTime = ''
477 
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
478 
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
479 
        mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
480 
        mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
481 
        mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
482 
        mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
483 
        mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
484 
        mpHistory.prefferedSellerShippingTime = ''
485 
        mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
486 
        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
487 
        proposed_sp = max(flipkartDetails.lowestSellerSp - max((10, flipkartDetails.lowestSellerSp*0.001)), flipkartPricing.lowestPossibleSp)
488 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
489 
        mpHistory.proposedSellingPrice = round(proposed_sp,2)
490 
        mpHistory.proposedTp = round(proposed_tp,2)
491 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
492 
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
493 
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
494 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
495 
        mpHistory.timestamp = timestamp
496 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
497 
    session.commit()
498 
 
499 
def commitNegativeMargin(negativeMargin,timestamp):
500 
    for item in negativeMargin:
501 
        flipkartDetails = item[0]
502 
        flipkartItemInfo = item[1]
503 
        flipkartPricing = item[2]
504 
        mpHistory = MarketPlaceHistory()
505 
        mpHistory.item_id = flipkartItemInfo.item_id
506 
        mpHistory.source = OrderSource.FLIPKART
507 
        mpHistory.lowestTp = flipkartPricing.lowestTp
508 
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
509 
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
510 
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
511 
        mpHistory.ourRank = flipkartDetails.rank
512 
        mpHistory.competitiveCategory = CompetitionCategory.NEGATIVE_MARGIN
513 
        mpHistory.risky = flipkartItemInfo.risky
514 
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
515 
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
516 
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
517 
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
518 
        mpHistory.lowestSellerShippingTime = ''
519 
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
520 
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
521 
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
522 
        mpHistory.ourTp = flipkartPricing.ourTp
523 
        mpHistory.ourNlc = flipkartItemInfo.nlc
524 
        mpHistory.ourRating = flipkartDetails.ourScore
525 
        mpHistory.ourShippingTime = ''
526 
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
527 
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
528 
        mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
529 
        mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
530 
        mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
531 
        mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
532 
        mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
533 
        mpHistory.prefferedSellerShippingTime = ''
534 
        mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
535 
        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
536 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
537 
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
538 
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
539 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
540 
        mpHistory.timestamp = timestamp
541 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
542 
    session.commit()
543 
 
544 
def commitCheapButNotPref(cheapButNotPref,timestamp):
545 
    for item in cheapButNotPref:
546 
        flipkartDetails = item[0]
547 
        flipkartItemInfo = item[1]
548 
        flipkartPricing = item[2]
549 
        mpHistory = MarketPlaceHistory()
550 
        mpHistory.item_id = flipkartItemInfo.item_id
551 
        mpHistory.source = OrderSource.FLIPKART
552 
        mpHistory.lowestTp = flipkartPricing.lowestTp
553 
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
554 
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
555 
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
556 
        mpHistory.ourRank = flipkartDetails.rank
557 
        mpHistory.competitiveCategory = CompetitionCategory.CHEAP_BUT_NOT_PREF
558 
        mpHistory.risky = flipkartItemInfo.risky
559 
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
560 
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
561 
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
562 
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
563 
        mpHistory.lowestSellerShippingTime = ''
564 
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
565 
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
566 
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
567 
        mpHistory.ourTp = flipkartPricing.ourTp
568 
        mpHistory.ourNlc = flipkartItemInfo.nlc
569 
        mpHistory.ourRating = flipkartDetails.ourScore
570 
        mpHistory.ourShippingTime = ''
571 
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
572 
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
573 
        mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
574 
        mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
575 
        mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
576 
        mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
577 
        mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
578 
        mpHistory.prefferedSellerShippingTime = ''
579 
        mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
580 
        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
581 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
582 
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
583 
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
584 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
585 
        mpHistory.timestamp = timestamp
586 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
587 
    session.commit()
588 
 
589 
def commitPrefButNotCheap(prefButNotCheap,timestamp):
590 
    for item in prefButNotCheap:
591 
        flipkartDetails = item[0]
592 
        flipkartItemInfo = item[1]
593 
        flipkartPricing = item[2]
11619 kshitij.so 594 
        mpItem = item[3]
11193 kshitij.so 595 
        mpHistory = MarketPlaceHistory()
596 
        mpHistory.item_id = flipkartItemInfo.item_id
597 
        mpHistory.source = OrderSource.FLIPKART
598 
        mpHistory.lowestTp = flipkartPricing.lowestTp
599 
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
600 
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
601 
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
602 
        mpHistory.ourRank = flipkartDetails.rank
603 
        mpHistory.competitiveCategory = CompetitionCategory.PREF_BUT_NOT_CHEAP
604 
        mpHistory.risky = flipkartItemInfo.risky
605 
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
606 
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
607 
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
608 
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
609 
        mpHistory.lowestSellerShippingTime = ''
610 
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
611 
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
612 
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
613 
        mpHistory.ourTp = flipkartPricing.ourTp
614 
        mpHistory.ourNlc = flipkartItemInfo.nlc
615 
        mpHistory.ourRating = flipkartDetails.ourScore
616 
        mpHistory.ourShippingTime = ''
617 
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
618 
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
619 
        mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
620 
        mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
621 
        mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
622 
        mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
623 
        mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
624 
        mpHistory.prefferedSellerShippingTime = ''
625 
        mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
626 
        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
627 
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
11775 kshitij.so 628 
        proposed_sp = max(flipkartDetails.lowestSellerSp - max((10, flipkartDetails.lowestSellerSp*0.001)), flipkartPricing.lowestPossibleSp)
11619 kshitij.so 629 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
630 
        mpHistory.proposedSellingPrice = proposed_sp
631 
        mpHistory.proposedTp = proposed_tp
11193 kshitij.so 632 
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
633 
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
634 
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
635 
        mpHistory.timestamp = timestamp
636 
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
637 
    session.commit()
638 
 
639 
def populateStuff(runType,time):
11581 kshitij.so 640 
    global itemSaleMap
11615 kshitij.so 641 
    global categoryMap
642 
 
11193 kshitij.so 643 
    itemInfo = []
644 
    if runType=='FAVOURITE':
645 
        items = session.query(FlipkartItem,MarketplaceItems).join((MarketplaceItems,FlipkartItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.FLIPKART).\
646 
        filter(or_(MarketplaceItems.autoFavourite==True, MarketplaceItems.manualFavourite==True)).all()
647 
    else:
648 
        #items = session.query(FlipkartItem,MarketplaceItems).join((MarketplaceItems,FlipkartItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
649 
        items = session.query(FlipkartItem,MarketplaceItems).join((MarketplaceItems,FlipkartItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
11581 kshitij.so 650 
 
651 
    inventory_client = InventoryClient().get_client()
652 
 
11193 kshitij.so 653 
    for item in items:
654 
        flipkart_item = item[0]
655 
        mp_item = item[1]
656 
        it = Item.query.filter_by(id=flipkart_item.item_id).one()
12237 kshitij.so 657 
        print "Checking percentages for item Id ",it.id
11193 kshitij.so 658 
        category = Category.query.filter_by(id=it.category).one()
659 
        parent_category = Category.query.filter_by(id=category.parent_category_id).first()
11615 kshitij.so 660 
        if not categoryMap.has_key(category.id):
661 
            temp = []
11616 kshitij.so 662 
            temp.append(category.display_name)
663 
            temp.append(parent_category.display_name)
11615 kshitij.so 664 
            categoryMap[category.id] = temp
12134 kshitij.so 665 
        srm = SourceReturnPercentage.get_by(source=OrderSource.FLIPKART,brand=it.brand,category_id=it.category)
11193 kshitij.so 666 
        sip = SourceItemPercentage.query.filter(SourceItemPercentage.item_id==it.id).filter(SourceItemPercentage.source==OrderSource.FLIPKART).filter(SourceItemPercentage.startDate<=time).filter(SourceItemPercentage.expiryDate>=time).first()
667 
        sourcePercentage = None
668 
        if sip is not None:
669 
            sourcePercentage = sip
12137 kshitij.so 670 
            sourcePercentage.returnProvision = srm.returnProvision
11193 kshitij.so 671 
        else:
672 
            scp = SourceCategoryPercentage.query.filter(SourceCategoryPercentage.category_id==it.category).filter(SourceCategoryPercentage.source==OrderSource.FLIPKART).filter(SourceCategoryPercentage.startDate<=time).filter(SourceCategoryPercentage.expiryDate>=time).first()
673 
            if scp is not None:
674 
                sourcePercentage = scp
12137 kshitij.so 675 
                sourcePercentage.returnProvision = srm.returnProvision
11193 kshitij.so 676 
            else:
677 
                spm = SourcePercentageMaster.get_by(source=OrderSource.FLIPKART)
678 
                sourcePercentage = spm
12137 kshitij.so 679 
                sourcePercentage.returnProvision = srm.returnProvision
680 
 
681 
 
11581 kshitij.so 682 
 
683 
        warehouse = inventory_client.getWarehouse(flipkart_item.warehouseId)
684 
        itemSaleList = []
685 
        oosForAllSources = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, 0, 3)
686 
        oosForFlipkart = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 5)
687 
        oosForFlipkartLastDay = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 1)
11615 kshitij.so 688 
        oosForFlipkartTwoDay = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 2)
11581 kshitij.so 689 
        itemSaleList.append(oosForAllSources)
690 
        itemSaleList.append(oosForFlipkart)
691 
        itemSaleList.append(calculateAverageSale(oosForAllSources))
692 
        itemSaleList.append(calculateAverageSale(oosForFlipkart))
693 
        itemSaleList.append(calculateAverageSale(oosForFlipkartLastDay))
694 
        itemSaleList.append(calculateTotalSale(oosForFlipkart))
11615 kshitij.so 695 
        itemSaleList.append(calculateAverageSale(oosForFlipkartTwoDay))
11581 kshitij.so 696 
        itemSaleMap[flipkart_item.item_id]=itemSaleList
697 
 
11560 kshitij.so 698 
#        try:
699 
#            request_url = "https://api.flipkart.net/sellers/skus/%s/listings"%(str(flipkart_item.skuAtFlipkart))
700 
#            r = requests.get(request_url, auth=('m2z93iskuj81qiid', '0c7ab6a5-98c0-4cdc-8be3-72c591e0add4'))
701 
#            print "Inventory info",r.json()
702 
#            stock_count = int((r.json()['attributeValues'])['stock_count'])
703 
#        except:
704 
#            stock_count = 0
11581 kshitij.so 705 
        flipkartItemInfo = __FlipkartItemInfo(flipkart_item.flipkartSerialNumber, flipkart_item.maxNlc,mp_item.courierCost, it.id, it.product_group, it.brand, it.model_name, it.model_number, it.color, it.weight, category.parent_category_id, it.risky, flipkart_item.warehouseId, None, runType, parent_category.display_name,sourcePercentage,None,flipkart_item.skuAtFlipkart,None,warehouse.stateId)
11193 kshitij.so 706 
        itemInfo.append(flipkartItemInfo)
12149 kshitij.so 707 
    session.close()
11193 kshitij.so 708 
    return itemInfo
709 
 
12204 kshitij.so 710 
def fetchDetails(flipkartSerialNumber):
12211 kshitij.so 711 
    url = "http://www.flipkart.com/ps/%s"%(flipkartSerialNumber)
712 
    #url = "http://www.flipkart.com/ps/MOBDTXVZXVY3GFG8"
713 
    #scraper.read(url)
714 
    scraper = FlipkartScraper.FlipkartScraper()
715 
    vendorsData = scraper.read(url)
716 
    fin = datetime.now()
717 
    print "Finish with data for serial Number %s %s" %(flipkartSerialNumber,str(fin))
718 
    sortedVendorsData = sorted(vendorsData, key=itemgetter('sellingPrice'))
719 
    vendorsData[:]=[]
720 
    rank ,ourSp, iterator, secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, shippingTimeLowerLimitLowestSeller,shippingTimeUpperLimitLowestSeller, \
721 
    shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller, shippingTimeLowerLimitOur, shippingTimeUpperLimitOur, shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller, totalAvailableSeller= (0,)*19
722 
    lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
723 
    ourBuyTrend, prefSellerBuyTrend, secondLowestSellerBuyTrend, ourCode = ('',)*11
724 
    for data in sortedVendorsData:
725 
        if iterator == 0:
726 
            lowestSellerName = data['sellerName']
727 
            lowestSellerScore = data['sellerScore']
728 
            lowestSellerCode = data['sellerCode']
729 
            lowestSellerSp = data['sellingPrice']
730 
            lowestSellerBuyTrend = data['buyTrend']
731 
            try:
732 
                shippingTimeLowerLimitLowestSeller, shippingTimeUpperLimitLowestSeller = data['shippingTime'].split('-')
733 
            except ValueError:
734 
                shippingTimeLowerLimitLowestSeller = int(data['shippingTime'])
735 
 
736 
        if iterator ==1:
737 
            secondLowestSellerName = data['sellerName']
738 
            secondLowestSellerScore = data['sellerScore']
739 
            secondLowestSellerCode = data['sellerCode']
740 
            secondLowestSellerSp = data['sellingPrice']
741 
            secondLowestSellerBuyTrend = data['buyTrend']
742 
            try:
743 
                shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller = data['shippingTime'].split('-')
744 
            except ValueError:
745 
                shippingTimeLowerLimitSecondLowestSeller = int(data['shippingTime'])
746 
 
747 
        if data['sellerName'] == 'Saholic':
748 
            ourScore = data['sellerScore']
749 
            ourCode = data['sellerCode']
750 
            ourSp = data['sellingPrice']
751 
            ourBuyTrend = data['buyTrend']
752 
            try:
753 
                shippingTimeLowerLimitOur, shippingTimeUpperLimitOur = data['shippingTime'].split('-')
754 
            except ValueError:
755 
                shippingTimeLowerLimitOur = int(data['shippingTime'])
756 
            rank = iterator + 1
757 
 
758 
        if data['buyTrend'] in ('PrefCheap','PrefNCheap',''):
759 
            prefSellerName = data['sellerName']
760 
            prefSellerScore = data['sellerScore']
761 
            prefSellerCode = data['sellerCode']
762 
            prefSellerSp = data['sellingPrice']
763 
            prefSellerBuyTrend = data['buyTrend']
764 
            try:
765 
                shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller = data['shippingTime'].split('-')
766 
            except ValueError:
767 
                shippingTimeLowerLimitPrefSeller = int(data['shippingTime'])
768 
 
769 
        iterator+=1
770 
 
771 
    flipkartDetails = __FlipkartDetails(rank ,ourSp , secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, int(shippingTimeLowerLimitLowestSeller),int(shippingTimeUpperLimitLowestSeller), \
772 
    int(shippingTimeLowerLimitPrefSeller), int(shippingTimeUpperLimitPrefSeller), int(shippingTimeLowerLimitOur), int(shippingTimeUpperLimitOur), int(shippingTimeLowerLimitSecondLowestSeller), int(shippingTimeUpperLimitSecondLowestSeller), len(sortedVendorsData), lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
773 
    ourBuyTrend, prefSellerBuyTrend, secondLowestSellerBuyTrend, ourCode)
774 
 
775 
    if flipkartDetails.ourBuyTrend == 'PrefCheap'and flipkartDetails.rank!=1 and flipkartDetails.ourSp==flipkartDetails.secondLowestSellerSp:
776 
        print "Under PrefCheap category.Switching data for ",flipkartSerialNumber
777 
        flipkartDetails.lowestSellerSp, flipkartDetails.secondLowestSellerSp = flipkartDetails.secondLowestSellerSp,flipkartDetails.lowestSellerSp
778 
        flipkartDetails.lowestSellerScore, flipkartDetails.secondLowestSellerScore = flipkartDetails.secondLowestSellerScore,flipkartDetails.lowestSellerScore
779 
        flipkartDetails.shippingTimeLowerLimitLowestSeller, flipkartDetails.shippingTimeLowerLimitSecondLowestSeller = flipkartDetails.shippingTimeLowerLimitSecondLowestSeller,flipkartDetails.shippingTimeLowerLimitLowestSeller
780 
        flipkartDetails.shippingTimeUpperLimitLowestSeller, flipkartDetails.shippingTimeUpperLimitSecondLowestSeller = flipkartDetails.shippingTimeUpperLimitSecondLowestSeller,flipkartDetails.shippingTimeUpperLimitLowestSeller
781 
        flipkartDetails.lowestSellerName, flipkartDetails.secondLowestSellerName = flipkartDetails.secondLowestSellerName,flipkartDetails.lowestSellerName
782 
        flipkartDetails.lowestSellerCode, flipkartDetails.secondLowestSellerCode = flipkartDetails.secondLowestSellerCode,flipkartDetails.lowestSellerCode
783 
        flipkartDetails.lowestSellerBuyTrend, flipkartDetails.secondLowestSellerBuyTrend = flipkartDetails.secondLowestSellerBuyTrend,flipkartDetails.lowestSellerBuyTrend
784 
        flipkartDetails.rank=1
785 
 
786 
    if flipkartDetails.ourBuyTrend == 'NPrefCheap'and flipkartDetails.rank!=1 and flipkartDetails.ourSp==flipkartDetails.lowestSellerSp:
787 
        print "Under NPrefCheap category.Switching data for ",flipkartSerialNumber
788 
        flipkartDetails.lowestSellerSp = flipkartDetails.ourSp
789 
        flipkartDetails.lowestSellerScore = flipkartDetails.ourScore
790 
        flipkartDetails.shippingTimeLowerLimitLowestSeller = flipkartDetails.shippingTimeLowerLimitOur
791 
        flipkartDetails.shippingTimeUpperLimitLowestSeller = flipkartDetails.shippingTimeUpperLimitOur
792 
        flipkartDetails.lowestSellerName = 'Saholic'
793 
        flipkartDetails.lowestSellerCode = flipkartDetails.ourCode
794 
        flipkartDetails.lowestSellerBuyTrend = flipkartDetails.ourBuyTrend
12317 kshitij.so 795 
        flipkartDetails.rank=1
796 
        flipkartDetails.ourBuyTrend ='NPrefCheap'
11774 kshitij.so 797 
 
12211 kshitij.so 798 
    return flipkartDetails
799 
 
11193 kshitij.so 800 
def calculateAverageSale(oosStatus):
801 
    count,sale = 0,0
802 
    for obj in oosStatus:
803 
        if not obj.is_oos:
804 
            count+=1
805 
            sale = sale+obj.num_orders
806 
    avgSalePerDay=0 if count==0 else (float(sale)/count)
807 
    return round(avgSalePerDay,2)
808 
 
809 
def calculateTotalSale(oosStatus):
810 
    sale = 0
811 
    for obj in oosStatus:
812 
        if not obj.is_oos:
813 
            sale = sale+obj.num_orders
814 
    return sale
815 
 
816 
def getNetAvailability(itemInventory):
817 
    totalAvailability, totalReserved = 0,0
818 
    availableMap  = itemInventory.availability
819 
    reserveMap = itemInventory.reserved
820 
    for warehouse,availability in availableMap.iteritems():
12317 kshitij.so 821 
        if warehouse==16 or warehouse==1771:
11193 kshitij.so 822 
            continue
823 
        totalAvailability = totalAvailability+availability
824 
    for warehouse,reserve in reserveMap.iteritems():
12317 kshitij.so 825 
        if warehouse==16 or warehouse==1771:
11193 kshitij.so 826 
            continue
827 
        totalReserved = totalReserved+reserve
828 
    return totalAvailability - totalReserved
829 
 
830 
def getOosString(oosStatus):
831 
    lastNdaySale=""
832 
    for obj in oosStatus:
833 
        if obj.is_oos:
834 
            lastNdaySale += "X-"
835 
        else:
836 
            lastNdaySale += str(obj.num_orders) + "-"
837 
    return lastNdaySale[:-1]
838 
 
839 
def getLastDaySale(itemId):
840 
    return (itemSaleMap.get(itemId))[4]
841 
 
842 
def getSalesPotential(lowestSellingPrice,ourNlc):
843 
    if lowestSellingPrice - ourNlc < 0:
844 
        return 'HIGH'
845 
    elif (float(lowestSellingPrice - ourNlc))/lowestSellingPrice >=0 and (float(lowestSellingPrice - ourNlc))/lowestSellingPrice <=.02:
846 
        return 'MEDIUM'
847 
    else:
848 
        return 'LOW'
849 
 
11571 kshitij.so 850 
def decideCategory(itemInfo):
11193 kshitij.so 851 
    global itemSaleMap
11560 kshitij.so 852 
 
11571 kshitij.so 853 
    cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap = [],[],[],[],[],[],[],[]
854 
 
11193 kshitij.so 855 
    catalog_client = CatalogClient().get_client()
856 
 
11571 kshitij.so 857 
    for val in itemInfo:
858 
        spm = val.sourcePercentage
859 
        flipkartDetails = val.flipkartDetails
11581 kshitij.so 860 
        if (flipkartDetails is None or flipkartDetails.totalAvailableSeller==0):
11571 kshitij.so 861 
            exceptionItems.append(val)
862 
            continue
12317 kshitij.so 863 
        if ((flipkartDetails.rank=='' or flipkartDetails.rank==0) and val.ourFlipkartInventory!=0):
864 
            exceptionItems.append(val)
865 
            continue
11571 kshitij.so 866 
 
867 
        mpItem = MarketplaceItems.get_by(itemId=val.item_id,source=OrderSource.FLIPKART)
868 
        if flipkartDetails.rank==0:
869 
            flipkartDetails.ourSp = mpItem.currentSp
870 
            ourSp = mpItem.currentSp
871 
        else:
872 
            ourSp = flipkartDetails.ourSp
11581 kshitij.so 873 
        vatRate = catalog_client.getVatPercentageForItem(val.item_id, val.stateId, flipkartDetails.ourSp)
11571 kshitij.so 874 
        val.vatRate = vatRate
875 
        if (flipkartDetails.ourBuyTrend == 'PrefCheap') or (flipkartDetails.rank==1 and flipkartDetails.totalAvailableSeller==1):
876 
            temp=[]
877 
            temp.append(flipkartDetails)
878 
            temp.append(val)
879 
            secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
880 
            prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
881 
            flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
882 
            temp.append(flipkartPricing)
883 
            temp.append(mpItem)
884 
            buyBoxItems.append(temp)
885 
            continue
886 
 
887 
        if (flipkartDetails.ourBuyTrend == 'PrefNCheap'):
888 
            temp=[]
889 
            temp.append(flipkartDetails)
890 
            temp.append(val)
11618 kshitij.so 891 
            secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getSecondLowestSellerTp(flipkartDetails,val,spm,False)
11615 kshitij.so 892 
            lowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
11571 kshitij.so 893 
            prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
894 
            flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
895 
            temp.append(flipkartPricing)
896 
            temp.append(mpItem)
897 
            prefButNotCheap.append(temp)
898 
            continue
899 
 
900 
        if (flipkartDetails.ourBuyTrend == 'NPrefCheap') and (flipkartDetails.rank==1):
901 
            temp=[]
902 
            temp.append(flipkartDetails)
903 
            temp.append(val)
11615 kshitij.so 904 
            secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
11571 kshitij.so 905 
            prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
11615 kshitij.so 906 
            flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
11571 kshitij.so 907 
            temp.append(flipkartPricing)
908 
            temp.append(mpItem)
909 
            cheapButNotPref.append(temp)
910 
            continue
911 
 
912 
        lowestTp = getOtherTp(flipkartDetails,val,spm,False)
913 
        ourTp = getOurTp(flipkartDetails,val,spm,mpItem)
914 
        lowestPossibleTp = getLowestPossibleTp(flipkartDetails,val,spm,mpItem)
915 
        lowestPossibleSp = getLowestPossibleSp(flipkartDetails,val,spm,mpItem)
916 
        prefSellerTp = getOtherTp(flipkartDetails,val,spm,True)
917 
 
918 
        if (ourTp<lowestPossibleTp):
919 
            temp=[]
920 
            temp.append(flipkartDetails)
921 
            temp.append(val)
12156 kshitij.so 922 
            flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),None)
11571 kshitij.so 923 
            temp.append(flipkartPricing)
924 
            negativeMargin.append(temp)
925 
            continue
926 
 
927 
        if (flipkartDetails.lowestSellerSp > lowestPossibleSp) and val.ourFlipkartInventory!=0:
928 
            type(val.ourFlipkartInventory)
929 
            temp=[]
930 
            temp.append(flipkartDetails)
931 
            temp.append(val)
932 
            flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,lowestPossibleSp,prefSellerTp)
933 
            temp.append(flipkartPricing)
934 
            temp.append(mpItem)
935 
            competitive.append(temp)
936 
            continue
937 
 
938 
        if (flipkartDetails.lowestSellerSp) > lowestPossibleSp and val.ourFlipkartInventory==0:
939 
            temp=[]
940 
            temp.append(flipkartDetails)
941 
            temp.append(val)
942 
            flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,lowestPossibleSp,prefSellerTp)
943 
            temp.append(flipkartPricing)
944 
            temp.append(mpItem)
945 
            competitiveNoInventory.append(temp)
946 
            continue
947 
 
11193 kshitij.so 948 
        temp=[]
949 
        temp.append(flipkartDetails)
950 
        temp.append(val)
951 
        flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,lowestPossibleSp,prefSellerTp)
952 
        temp.append(flipkartPricing)
953 
        temp.append(mpItem)
11571 kshitij.so 954 
        cantCompete.append(temp)
11193 kshitij.so 955 
 
11969 kshitij.so 956 
    itemInfo[:]=[]
11571 kshitij.so 957 
    return cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap
11193 kshitij.so 958 
 
959 
def getOtherTp(flipkartDetails,val,spm,prefferedSeller):
960 
    if val.parent_category==10011 or val.parent_category==12001:
961 
        commissionPercentage = spm.competitorCommissionAccessory
962 
    else:
963 
        commissionPercentage = spm.competitorCommissionOther
964 
    if flipkartDetails.rank==1 and not prefferedSeller:
965 
        otherTp = flipkartDetails.secondLowestSellerSp- flipkartDetails.secondLowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
966 
        return round(otherTp,2)
967 
    if prefferedSeller:
968 
        otherTp = flipkartDetails.prefSellerSp- flipkartDetails.prefSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
969 
        return round(otherTp,2)
970 
    otherTp = flipkartDetails.lowestSellerSp- flipkartDetails.lowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
971 
    return round(otherTp,2)
11618 kshitij.so 972 
 
973 
def getSecondLowestSellerTp(flipkartDetails,val,spm,prefferedSeller):
974 
    if val.parent_category==10011 or val.parent_category==12001:
975 
        commissionPercentage = spm.competitorCommissionAccessory
976 
    else:
977 
        commissionPercentage = spm.competitorCommissionOther
978 
    otherTp = flipkartDetails.secondLowestSellerSp- flipkartDetails.secondLowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
979 
    return round(otherTp,2)
11193 kshitij.so 980 
 
981 
def getLowestPossibleTp(flipkartDetails,val,spm,mpItem):
982 
    if flipkartDetails.rank==0:
983 
        return mpItem.minimumPossibleTp
984 
    vat = (flipkartDetails.ourSp/(1+(val.vatRate/100))-(val.nlc/(1+(val.vatRate/100))))*(val.vatRate/100);
985 
    inHouseCost = 15+vat+(mpItem.returnProvision/100)*flipkartDetails.ourSp+mpItem.otherCost;
986 
    lowest_possible_tp = val.nlc+inHouseCost;
987 
    return round(lowest_possible_tp,2)
988 
 
989 
def getOurTp(flipkartDetails,val,spm,mpItem):
990 
    if flipkartDetails.rank==0:
991 
        return mpItem.currentTp
992 
    ourTp = flipkartDetails.ourSp- flipkartDetails.ourSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))
993 
    return round(ourTp,2)
994 
 
995 
def getLowestPossibleSp(flipkartDetails,val,spm,mpItem):
996 
    if flipkartDetails.rank==0:
997 
        return mpItem.minimumPossibleSp
998 
    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));
999 
    return round(lowestPossibleSp,2)
1000 
 
1001 
def getTargetTp(targetSp,mpItem):
1002 
    targetTp = targetSp- targetSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))
1003 
    return round(targetTp,2)
1004 
 
1005 
def getTargetSp(targetTp,mpItem,ourSp):
1006 
    targetSp = float(targetTp+(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100)))/(1-((mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))))
1007 
    return round(targetSp,2)
1008 
 
11623 kshitij.so 1009 
def getNewLowestPossibleTp(mpItem,nlc,vatRate,proposedSellingPrice):
1010 
    vat = (proposedSellingPrice/(1+(vatRate/100))-(nlc/(1+(vatRate/100))))*(vatRate/100);
1011 
    inHouseCost = 15+vat+(mpItem.returnProvision/100)*proposedSellingPrice+mpItem.otherCost;
1012 
    lowest_possible_tp = nlc+inHouseCost;
1013 
    return round(lowest_possible_tp,2)
1014 
 
1015 
def getNewOurTp(mpItem,proposedSellingPrice):
11624 kshitij.so 1016 
    ourTp = proposedSellingPrice- proposedSellingPrice*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))
11623 kshitij.so 1017 
    return round(ourTp,2)
1018 
 
1019 
def getNewLowestPossibleSp(mpItem,nlc,vatRate):
11624 kshitij.so 1020 
    lowestPossibleSp = (nlc+(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(vatRate/100))+(15+mpItem.otherCost)*(1+(vatRate)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(vatRate)/100)-(mpItem.returnProvision/100)*(1+(vatRate)/100));
11623 kshitij.so 1021 
    return round(lowestPossibleSp,2)
1022 
 
1023 
 
11193 kshitij.so 1024 
def markAutoFavourite():
1025 
    previouslyAutoFav = []
1026 
    nowAutoFav = []
1027 
    marketplaceItems = session.query(MarketplaceItems).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
1028 
    fromDate = datetime.now()-timedelta(days = 3, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
1029 
    toDate = datetime.now()-timedelta(days = 0, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
11615 kshitij.so 1030 
    items = session.query(MarketPlaceHistory.item_id,func.max(MarketPlaceHistory.timestamp)).group_by(MarketPlaceHistory.item_id).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.timestamp.between (fromDate,toDate)).filter(or_(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX,MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP)).all()
11193 kshitij.so 1031 
    toUpdate = [key for key, value in itemSaleMap.items() if value[5] >= 1]
1032 
    buyBoxLast3days = []
1033 
    for item in items:
1034 
        buyBoxLast3days.append(item[0])
1035 
    for marketplaceItem in marketplaceItems:
1036 
        reason = ""
1037 
        toMark = False
1038 
        if marketplaceItem.itemId in toUpdate:
1039 
            toMark = True
1040 
            reason+="Total sale is greater than 1 for last five days (Flipkart)."
1041 
        if marketplaceItem.itemId in buyBoxLast3days:
1042 
            toMark = True
1043 
            reason+="Item is present in buy box in last 3 days"
1044 
        if not marketplaceItem.autoFavourite:
1045 
            print "Item is not under auto favourite"
1046 
        if toMark:
1047 
            temp=[]
1048 
            temp.append(marketplaceItem.itemId)
1049 
            temp.append(reason)
1050 
            nowAutoFav.append(temp)
1051 
        if (not toMark) and marketplaceItem.autoFavourite:
1052 
            previouslyAutoFav.append(marketplaceItem.itemId)
1053 
        marketplaceItem.autoFavourite = toMark
1054 
    session.commit()
1055 
    return previouslyAutoFav, nowAutoFav
1056 
 
11615 kshitij.so 1057 
def write_report(previousAutoFav, nowAutoFav,timestamp, runType):
11193 kshitij.so 1058 
    wbk = xlwt.Workbook()
1059 
    sheet = wbk.add_sheet('Can\'t Compete')
1060 
    xstr = lambda s: s or ""
1061 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1062 
 
1063 
    excel_integer_format = '0'
1064 
    integer_style = xlwt.XFStyle()
1065 
    integer_style.num_format_str = excel_integer_format
1066 
 
1067 
    sheet.write(0, 0, "Item ID", heading_xf)
1068 
    sheet.write(0, 1, "Category", heading_xf)
1069 
    sheet.write(0, 2, "Product Group.", heading_xf)
1070 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1071 
    sheet.write(0, 4, "Brand", heading_xf)
1072 
    sheet.write(0, 5, "Product Name", heading_xf)
1073 
    sheet.write(0, 6, "Weight", heading_xf)
1074 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1075 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1076 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1077 
    sheet.write(0, 10, "Return Provision", heading_xf)
1078 
    sheet.write(0, 11, "Our Rating", heading_xf)
1079 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1080 
    sheet.write(0, 13, "Our Rank", heading_xf)
1081 
    sheet.write(0, 14, "Our SP", heading_xf)
1082 
    sheet.write(0, 15, "Our TP", heading_xf)
1083 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1084 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1085 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1086 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1087 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1088 
    sheet.write(0, 21, "Preffered Seller", heading_xf)
1089 
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
1090 
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
1091 
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
1092 
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
1093 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
1094 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
1095 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
1096 
    sheet.write(0, 29, "Average Sale", heading_xf)
1097 
    sheet.write(0, 30, "Our NLC", heading_xf)
1098 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
1099 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
1100 
    sheet.write(0, 33, "Target SP", heading_xf)
1101 
    sheet.write(0, 34, "Target TP", heading_xf)
1102 
    sheet.write(0, 35, "Target NLC", heading_xf)
1103 
    sheet.write(0, 36, "Sales Potential", heading_xf)
1104 
    sheet.write(0, 37, "Total Seller", heading_xf)
11193 kshitij.so 1105 
    sheet_iterator = 1
11620 kshitij.so 1106 
    canCompeteItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
11622 kshitij.so 1107 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1108 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1109 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1110 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1111 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
1112 
    for item in canCompeteItems:
1113 
        mpHistory = item[0]
1114 
        flipkartItem = item[1]
1115 
        mpItem = item[2]
1116 
        catItem = item[3]
1117 
        sheet.write(sheet_iterator,0,mpHistory.item_id)
1118 
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1119 
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1120 
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1121 
        sheet.write(sheet_iterator,4,catItem.brand)
1122 
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1123 
        sheet.write(sheet_iterator,6,catItem.weight)
1124 
        sheet.write(sheet_iterator,7,mpItem.courierCost)
1125 
        sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1126 
        sheet.write(sheet_iterator,9,mpItem.commission)
1127 
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
1128 
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 1129 
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1130 
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1131 
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
1132 
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
1133 
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
1134 
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
1135 
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
1136 
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 1137 
#       lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
1138 
#       else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 1139 
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
1140 
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
1141 
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
1142 
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
1143 
        sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
11615 kshitij.so 1144 
#        prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
1145 
#        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
11790 kshitij.so 1146 
        sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
1147 
        sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
1148 
        sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
1149 
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 1150 
        if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1151 
            sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 1152 
        else:
11790 kshitij.so 1153 
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1154 
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1155 
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
1156 
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
1157 
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
1158 
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
11615 kshitij.so 1159 
        proposed_sp = mpHistory.lowestSellingPrice - max(10, mpHistory.lowestSellingPrice*0.001)
11193 kshitij.so 1160 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
11615 kshitij.so 1161 
        target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
11790 kshitij.so 1162 
        sheet.write(sheet_iterator, 33, proposed_sp)
1163 
        sheet.write(sheet_iterator, 34, proposed_tp)
1164 
        sheet.write(sheet_iterator, 35, target_nlc)
1165 
        sheet.write(sheet_iterator, 36, getSalesPotential(mpHistory.lowestSellingPrice,mpHistory.ourNlc))
1166 
        sheet.write(sheet_iterator, 37, mpHistory.totalSeller)
11193 kshitij.so 1167 
        sheet_iterator+=1
11615 kshitij.so 1168 
 
1169 
    canCompeteItems[:] = []
1170 
 
11193 kshitij.so 1171 
 
1172 
    sheet = wbk.add_sheet('Pref and Cheap')
1173 
 
1174 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1175 
 
1176 
    excel_integer_format = '0'
1177 
    integer_style = xlwt.XFStyle()
1178 
    integer_style.num_format_str = excel_integer_format
1179 
    xstr = lambda s: s or ""
1180 
 
1181 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1182 
 
1183 
    excel_integer_format = '0'
1184 
    integer_style = xlwt.XFStyle()
1185 
    integer_style.num_format_str = excel_integer_format
1186 
 
1187 
    sheet.write(0, 0, "Item ID", heading_xf)
1188 
    sheet.write(0, 1, "Category", heading_xf)
1189 
    sheet.write(0, 2, "Product Group.", heading_xf)
1190 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1191 
    sheet.write(0, 4, "Brand", heading_xf)
1192 
    sheet.write(0, 5, "Product Name", heading_xf)
1193 
    sheet.write(0, 6, "Weight", heading_xf)
1194 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1195 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1196 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1197 
    sheet.write(0, 10, "Return Provision", heading_xf)
1198 
    sheet.write(0, 11, "Our Rating", heading_xf)
1199 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1200 
    sheet.write(0, 13, "Our Rank", heading_xf)
1201 
    sheet.write(0, 14, "Our SP", heading_xf)
1202 
    sheet.write(0, 15, "Our TP", heading_xf)
1203 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1204 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1205 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1206 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1207 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1208 
    sheet.write(0, 21, "Second Lowest Seller", heading_xf)
1209 
    sheet.write(0, 22, "Second Lowest Seller Rating", heading_xf)
1210 
    sheet.write(0, 23, "Second Lowest Seller Shipping Time", heading_xf)
1211 
    sheet.write(0, 24, "Second Lowest Seller SP", heading_xf)
1212 
    sheet.write(0, 25, "Second Lowest Seller TP", heading_xf)
1213 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
1214 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
1215 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
1216 
    sheet.write(0, 29, "Average Sale", heading_xf)
1217 
    sheet.write(0, 30, "Our NLC", heading_xf)
1218 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
1219 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
1220 
    sheet.write(0, 33, "Target SP", heading_xf)
1221 
    sheet.write(0, 34, "Target TP", heading_xf)
1222 
    sheet.write(0, 35, "Margin Increased Potential", heading_xf)
1223 
    sheet.write(0, 36, "Total Seller", heading_xf)
1224 
    sheet.write(0, 37, "Auto Pricing Decision", heading_xf)
1225 
    sheet.write(0, 38, "Reason", heading_xf)
1226 
    sheet.write(0, 39, "Updated Price", heading_xf)
11193 kshitij.so 1227 
    sheet_iterator = 1
11615 kshitij.so 1228 
 
11622 kshitij.so 1229 
    buyBoxItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1230 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1231 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1232 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1233 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1234 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
1235 
 
1236 
 
11193 kshitij.so 1237 
    for item in buyBoxItems:
11615 kshitij.so 1238 
        mpHistory = item[0]
1239 
        flipkartItem = item[1]
1240 
        mpItem = item[2]
1241 
        catItem = item[3]
1242 
        sheet.write(sheet_iterator,0,mpHistory.item_id)
1243 
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1244 
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1245 
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1246 
        sheet.write(sheet_iterator,4,catItem.brand)
1247 
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1248 
        sheet.write(sheet_iterator,6,catItem.weight)
1249 
        sheet.write(sheet_iterator,7,mpItem.courierCost)
1250 
        sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1251 
        sheet.write(sheet_iterator,9,mpItem.commission)
1252 
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
1253 
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 1254 
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1255 
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1256 
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
1257 
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
1258 
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
1259 
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
1260 
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
1261 
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 1262 
#        lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
1263 
#        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 1264 
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
1265 
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
1266 
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
1267 
        sheet.write(sheet_iterator,21,mpHistory.secondLowestSellerName)
1268 
        sheet.write(sheet_iterator,22,mpHistory.secondLowestSellerRating)
11615 kshitij.so 1269 
#        secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
1270 
#        else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
11790 kshitij.so 1271 
        sheet.write(sheet_iterator,23,mpHistory.secondLowestSellerShippingTime)
1272 
        sheet.write(sheet_iterator,24,mpHistory.secondLowestSellingPrice)
1273 
        sheet.write(sheet_iterator,25,mpHistory.secondLowestTp)
1274 
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 1275 
        if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1276 
            sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 1277 
        else:
11790 kshitij.so 1278 
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1279 
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1280 
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
1281 
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
1282 
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
1283 
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
11615 kshitij.so 1284 
        proposed_sp = max(mpHistory.secondLowestSellingPrice - max((20, mpHistory.secondLowestSellingPrice*0.002)), mpHistory.lowestPossibleSp)
11193 kshitij.so 1285 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
11615 kshitij.so 1286 
        target_nlc = proposed_tp -  mpHistory.lowestPossibleTp + mpHistory.ourNlc
11790 kshitij.so 1287 
        sheet.write(sheet_iterator, 33, proposed_sp)
1288 
        sheet.write(sheet_iterator, 34, proposed_tp)
1289 
        sheet.write(sheet_iterator, 35, proposed_tp -mpHistory.ourTp )
1290 
        sheet.write(sheet_iterator, 36, mpHistory.totalSeller)
11775 kshitij.so 1291 
        if mpHistory.decision is None:
11790 kshitij.so 1292 
            sheet.write(sheet_iterator, 37, 'Auto Pricing Inactive')
11775 kshitij.so 1293 
            sheet_iterator+=1
1294 
            continue
11790 kshitij.so 1295 
        sheet.write(sheet_iterator, 37, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
1296 
        sheet.write(sheet_iterator, 38, mpHistory.reason)
11776 kshitij.so 1297 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
11790 kshitij.so 1298 
            sheet.write(sheet_iterator, 39, math.ceil(mpHistory.proposedSellingPrice))
11776 kshitij.so 1299 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
11790 kshitij.so 1300 
            sheet.write(sheet_iterator, 39, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
11776 kshitij.so 1301 
 
11193 kshitij.so 1302 
        sheet_iterator+=1
1303 
 
11615 kshitij.so 1304 
    buyBoxItems[:] = []
11193 kshitij.so 1305 
 
11615 kshitij.so 1306 
    sheet = wbk.add_sheet('Pref Not Cheap')
11193 kshitij.so 1307 
 
1308 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1309 
 
1310 
    excel_integer_format = '0'
1311 
    integer_style = xlwt.XFStyle()
1312 
    integer_style.num_format_str = excel_integer_format
1313 
    xstr = lambda s: s or ""
1314 
 
1315 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1316 
 
1317 
    excel_integer_format = '0'
1318 
    integer_style = xlwt.XFStyle()
1319 
    integer_style.num_format_str = excel_integer_format
1320 
 
1321 
    sheet.write(0, 0, "Item ID", heading_xf)
1322 
    sheet.write(0, 1, "Category", heading_xf)
1323 
    sheet.write(0, 2, "Product Group.", heading_xf)
1324 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1325 
    sheet.write(0, 4, "Brand", heading_xf)
1326 
    sheet.write(0, 5, "Product Name", heading_xf)
1327 
    sheet.write(0, 6, "Weight", heading_xf)
1328 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1329 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1330 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1331 
    sheet.write(0, 10, "Return Provision", heading_xf)
1332 
    sheet.write(0, 11, "Our Rating", heading_xf)
1333 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1334 
    sheet.write(0, 13, "Our Rank", heading_xf)
1335 
    sheet.write(0, 14, "Our SP", heading_xf)
1336 
    sheet.write(0, 15, "Our TP", heading_xf)
1337 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1338 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1339 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1340 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1341 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1342 
    sheet.write(0, 21, "Preffered Seller", heading_xf)
1343 
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
1344 
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
1345 
    sheet.write(0, 24, "Preffered Seller SP", heading_xf)
1346 
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
1347 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
1348 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
1349 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
1350 
    sheet.write(0, 29, "Average Sale", heading_xf)
1351 
    sheet.write(0, 30, "Our NLC", heading_xf)
1352 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
1353 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
1354 
    sheet.write(0, 33, "Target SP", heading_xf)
1355 
    sheet.write(0, 34, "Target TP", heading_xf)
1356 
    sheet.write(0, 35, "Total Seller", heading_xf)
1357 
    sheet.write(0, 36, "Auto Pricing Decision", heading_xf)
1358 
    sheet.write(0, 37, "Reason", heading_xf)
1359 
    sheet.write(0, 38, "Updated Price", heading_xf)
11775 kshitij.so 1360 
 
11193 kshitij.so 1361 
    sheet_iterator = 1
11615 kshitij.so 1362 
 
11622 kshitij.so 1363 
    prefNotCheapItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1364 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1365 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1366 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1367 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1368 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP).all()
1369 
 
1370 
 
1371 
    for item in prefNotCheapItems:
1372 
        mpHistory = item[0]
1373 
        flipkartItem = item[1]
1374 
        mpItem = item[2]
1375 
        catItem = item[3]
1376 
        sheet.write(sheet_iterator,0,mpHistory.item_id)
1377 
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1378 
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1379 
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1380 
        sheet.write(sheet_iterator,4,catItem.brand)
1381 
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1382 
        sheet.write(sheet_iterator,6,catItem.weight)
1383 
        sheet.write(sheet_iterator,7,mpItem.courierCost)
1384 
        sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1385 
        sheet.write(sheet_iterator,9,mpItem.commission)
1386 
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
1387 
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 1388 
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1389 
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1390 
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
1391 
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
1392 
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
1393 
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
1394 
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
1395 
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 1396 
#        lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
1397 
#        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 1398 
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
1399 
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
1400 
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
1401 
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
1402 
        sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
11615 kshitij.so 1403 
#        secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
1404 
#        else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
11790 kshitij.so 1405 
        sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
1406 
        sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
1407 
        sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
1408 
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 1409 
        if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1410 
            sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 1411 
        else:
11790 kshitij.so 1412 
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1413 
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1414 
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
1415 
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
1416 
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
1417 
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
11775 kshitij.so 1418 
        proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
11615 kshitij.so 1419 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
1420 
        target_nlc = proposed_tp -  mpHistory.lowestPossibleTp + mpHistory.ourNlc
11790 kshitij.so 1421 
        sheet.write(sheet_iterator, 33, proposed_sp)
1422 
        sheet.write(sheet_iterator, 34, proposed_tp)
1423 
        sheet.write(sheet_iterator, 35, mpHistory.totalSeller)
11775 kshitij.so 1424 
        if mpHistory.decision is None:
11790 kshitij.so 1425 
            sheet.write(sheet_iterator, 36, 'Auto Pricing Inactive')
11775 kshitij.so 1426 
            sheet_iterator+=1
1427 
            continue
11790 kshitij.so 1428 
        sheet.write(sheet_iterator, 36, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
1429 
        sheet.write(sheet_iterator, 37, mpHistory.reason)
11776 kshitij.so 1430 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
11790 kshitij.so 1431 
            sheet.write(sheet_iterator, 38, math.ceil(mpHistory.proposedSellingPrice))
11776 kshitij.so 1432 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
11790 kshitij.so 1433 
            sheet.write(sheet_iterator, 38, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
11775 kshitij.so 1434 
 
11193 kshitij.so 1435 
        sheet_iterator+=1
11615 kshitij.so 1436 
 
1437 
    prefNotCheapItems[:] = []
11193 kshitij.so 1438 
 
1439 
    sheet = wbk.add_sheet('Cheap But Not Pref')
1440 
 
1441 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1442 
 
1443 
    excel_integer_format = '0'
1444 
    integer_style = xlwt.XFStyle()
1445 
    integer_style.num_format_str = excel_integer_format
1446 
    xstr = lambda s: s or ""
1447 
 
1448 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1449 
 
1450 
    excel_integer_format = '0'
1451 
    integer_style = xlwt.XFStyle()
1452 
    integer_style.num_format_str = excel_integer_format
1453 
 
1454 
    sheet.write(0, 0, "Item ID", heading_xf)
1455 
    sheet.write(0, 1, "Category", heading_xf)
1456 
    sheet.write(0, 2, "Product Group.", heading_xf)
1457 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1458 
    sheet.write(0, 4, "Brand", heading_xf)
1459 
    sheet.write(0, 5, "Product Name", heading_xf)
1460 
    sheet.write(0, 6, "Weight", heading_xf)
1461 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1462 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1463 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1464 
    sheet.write(0, 10, "Return Provision", heading_xf)
1465 
    sheet.write(0, 11, "Our Rank", heading_xf)
1466 
    sheet.write(0, 12, "Lowest Seller", heading_xf)
1467 
    sheet.write(0, 13, "Our Rating", heading_xf)
1468 
    sheet.write(0, 14, "Our Shipping Time", heading_xf)
1469 
    sheet.write(0, 15, "Our SP", heading_xf)
1470 
    sheet.write(0, 16, "Our TP", heading_xf)
1471 
    sheet.write(0, 17, "Preffered Seller", heading_xf)
1472 
    sheet.write(0, 18, "Preffered Seller Rating", heading_xf)
1473 
    sheet.write(0, 19, "Preffered Seller Shipping Time", heading_xf)
1474 
    sheet.write(0, 20, "Preffered Seller SP", heading_xf)
1475 
    sheet.write(0, 21, "Preffered Seller TP", heading_xf)
1476 
    sheet.write(0, 22, "Our Flipkart Inventory", heading_xf)
1477 
    sheet.write(0, 23, "Our Net Availability",heading_xf)
1478 
    sheet.write(0, 24, "Last Five Day Sale", heading_xf)
1479 
    sheet.write(0, 25, "Average Sale", heading_xf)
1480 
    sheet.write(0, 26, "Our NLC", heading_xf)
1481 
    sheet.write(0, 27, "Lowest Possible SP", heading_xf)
1482 
    sheet.write(0, 28, "Lowest Possible TP", heading_xf)
1483 
    sheet.write(0, 29, "Total Seller", heading_xf)
11193 kshitij.so 1484 
    sheet_iterator = 1
11615 kshitij.so 1485 
 
11622 kshitij.so 1486 
    cheapNotPrefferedItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1487 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1488 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1489 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1490 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1491 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CHEAP_BUT_NOT_PREF).all()
1492 
 
1493 
    for item in cheapNotPrefferedItems:
1494 
        mpHistory = item[0]
1495 
        flipkartItem = item[1]
1496 
        mpItem = item[2]
1497 
        catItem = item[3]
1498 
        sheet.write(sheet_iterator,0,mpHistory.item_id)
1499 
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1500 
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1501 
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1502 
        sheet.write(sheet_iterator,4,catItem.brand)
1503 
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1504 
        sheet.write(sheet_iterator,6,catItem.weight)
1505 
        sheet.write(sheet_iterator,7,mpItem.courierCost)
1506 
        sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1507 
        sheet.write(sheet_iterator,9,mpItem.commission)
1508 
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
1509 
        sheet.write(sheet_iterator,11,mpHistory.ourRank)
1510 
        sheet.write(sheet_iterator,12,mpHistory.lowestSellerName)
1511 
        sheet.write(sheet_iterator,13,mpHistory.ourRating)
11615 kshitij.so 1512 
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1513 
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1514 
        sheet.write(sheet_iterator,14,mpHistory.lowestSellerShippingTime)
1515 
        sheet.write(sheet_iterator,15,mpHistory.lowestSellingPrice)
1516 
        sheet.write(sheet_iterator,16,mpHistory.lowestTp)
1517 
        sheet.write(sheet_iterator,17,mpHistory.prefferedSellerName)
1518 
        sheet.write(sheet_iterator,18,mpHistory.prefferedSellerRating)
11615 kshitij.so 1519 
#        prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
1520 
#        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
11790 kshitij.so 1521 
        sheet.write(sheet_iterator,19,mpHistory.prefferedSellerShippingTime)
1522 
        sheet.write(sheet_iterator,20,mpHistory.prefferedSellerSellingPrice)
1523 
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerTp)
1524 
        sheet.write(sheet_iterator,22,mpHistory.ourInventory)
11615 kshitij.so 1525 
        if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1526 
            sheet.write(sheet_iterator, 23, 'Info not available')
11193 kshitij.so 1527 
        else:
11790 kshitij.so 1528 
            sheet.write(sheet_iterator, 23, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1529 
        sheet.write(sheet_iterator, 24, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1530 
        sheet.write(sheet_iterator, 25, (itemSaleMap.get(mpHistory.item_id))[3])
1531 
        sheet.write(sheet_iterator, 26, mpHistory.ourNlc)
1532 
        sheet.write(sheet_iterator, 27, mpHistory.lowestPossibleSp)
1533 
        sheet.write(sheet_iterator, 28, mpHistory.lowestPossibleTp)
11193 kshitij.so 1534 
        #proposed_sp = max(flipkartDetails.secondLowestSellerSp - max((20, flipkartDetails.secondLowestSellerSp*0.002)), flipkartPricing.lowestPossibleSp)
1535 
        #proposed_tp = getTargetTp(proposed_sp,mpItem)
1536 
        #target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
11790 kshitij.so 1537 
        sheet.write(sheet_iterator, 29, mpHistory.totalSeller)
11193 kshitij.so 1538 
        sheet_iterator+=1
11615 kshitij.so 1539 
 
1540 
    cheapNotPrefferedItems[:]=[]
11193 kshitij.so 1541 
 
1542 
    sheet = wbk.add_sheet('Can Compete-With Inventory')
1543 
    xstr = lambda s: s or ""
1544 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1545 
 
1546 
    excel_integer_format = '0'
1547 
    integer_style = xlwt.XFStyle()
1548 
    integer_style.num_format_str = excel_integer_format
1549 
 
1550 
    sheet.write(0, 0, "Item ID", heading_xf)
1551 
    sheet.write(0, 1, "Category", heading_xf)
1552 
    sheet.write(0, 2, "Product Group.", heading_xf)
1553 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1554 
    sheet.write(0, 4, "Brand", heading_xf)
1555 
    sheet.write(0, 5, "Product Name", heading_xf)
1556 
    sheet.write(0, 6, "Weight", heading_xf)
1557 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1558 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1559 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1560 
    sheet.write(0, 10, "Return Provision", heading_xf)
1561 
    sheet.write(0, 11, "Our Rating", heading_xf)
1562 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1563 
    sheet.write(0, 13, "Our Rank", heading_xf)
1564 
    sheet.write(0, 14, "Our SP", heading_xf)
1565 
    sheet.write(0, 15, "Our TP", heading_xf)
1566 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1567 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1568 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1569 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1570 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1571 
    sheet.write(0, 21, "Preffered Seller", heading_xf)
1572 
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
1573 
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
1574 
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
1575 
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
1576 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
1577 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
1578 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
1579 
    sheet.write(0, 29, "Average Sale", heading_xf)
1580 
    sheet.write(0, 30, "Our NLC", heading_xf)
1581 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
1582 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
1583 
    sheet.write(0, 33, "Target SP", heading_xf)
1584 
    sheet.write(0, 34, "Target TP", heading_xf)
1585 
    sheet.write(0, 35, "Target NLC", heading_xf)
1586 
    sheet.write(0, 36, "Sales Potential", heading_xf)
1587 
    sheet.write(0, 37, "Total Seller", heading_xf)
1588 
    sheet.write(0, 38, "Auto Pricing Decision", heading_xf)
1589 
    sheet.write(0, 39, "Reason", heading_xf)
1590 
    sheet.write(0, 40, "Updated Price", heading_xf)
11193 kshitij.so 1591 
    sheet_iterator = 1
11615 kshitij.so 1592 
 
11622 kshitij.so 1593 
    competitiveItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1594 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1595 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1596 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1597 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1598 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).all()
1599 
 
1600 
    for item in competitiveItems:
1601 
        mpHistory = item[0]
1602 
        flipkartItem = item[1]
1603 
        mpItem = item[2]
1604 
        catItem = item[3]
1605 
        sheet.write(sheet_iterator,0,mpHistory.item_id)
1606 
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1607 
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1608 
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1609 
        sheet.write(sheet_iterator,4,catItem.brand)
1610 
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1611 
        sheet.write(sheet_iterator,6,catItem.weight)
1612 
        sheet.write(sheet_iterator,7,mpItem.courierCost)
1613 
        sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1614 
        sheet.write(sheet_iterator,9,mpItem.commission)
1615 
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
1616 
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 1617 
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1618 
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1619 
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
1620 
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
1621 
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
1622 
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
1623 
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
1624 
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 1625 
#        lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
1626 
#        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 1627 
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
1628 
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
1629 
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
1630 
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
1631 
        sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
11615 kshitij.so 1632 
#        prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
1633 
#        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
11790 kshitij.so 1634 
        sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
1635 
        sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
1636 
        sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
1637 
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 1638 
        if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1639 
            sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 1640 
        else:
11790 kshitij.so 1641 
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1642 
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1643 
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
1644 
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
1645 
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
1646 
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
11775 kshitij.so 1647 
        proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
11193 kshitij.so 1648 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
11615 kshitij.so 1649 
        target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
11790 kshitij.so 1650 
        sheet.write(sheet_iterator, 33, proposed_sp)
1651 
        sheet.write(sheet_iterator, 34, proposed_tp)
1652 
        sheet.write(sheet_iterator, 35, target_nlc)
1653 
        sheet.write(sheet_iterator, 36, getSalesPotential(mpHistory.lowestSellingPrice,mpHistory.ourNlc))
1654 
        sheet.write(sheet_iterator, 37, mpHistory.totalSeller)
11775 kshitij.so 1655 
        if mpHistory.decision is None:
11790 kshitij.so 1656 
            sheet.write(sheet_iterator, 38, 'Auto Pricing Inactive')
11775 kshitij.so 1657 
            sheet_iterator+=1
1658 
            continue
11790 kshitij.so 1659 
        sheet.write(sheet_iterator, 38, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
1660 
        sheet.write(sheet_iterator, 39, mpHistory.reason)
11776 kshitij.so 1661 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
11790 kshitij.so 1662 
            sheet.write(sheet_iterator, 40, math.ceil(mpHistory.proposedSellingPrice))
11776 kshitij.so 1663 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
11790 kshitij.so 1664 
            sheet.write(sheet_iterator, 40, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
11775 kshitij.so 1665 
 
11193 kshitij.so 1666 
        sheet_iterator+=1
11615 kshitij.so 1667 
 
1668 
    competitiveItems[:]=[]
1669 
 
11193 kshitij.so 1670 
    sheet = wbk.add_sheet('Negative Margin')
1671 
    xstr = lambda s: s or ""
1672 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1673 
 
1674 
    excel_integer_format = '0'
1675 
    integer_style = xlwt.XFStyle()
1676 
    integer_style.num_format_str = excel_integer_format
1677 
 
1678 
    sheet.write(0, 0, "Item ID", heading_xf)
1679 
    sheet.write(0, 1, "Category", heading_xf)
1680 
    sheet.write(0, 2, "Product Group.", heading_xf)
1681 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1682 
    sheet.write(0, 4, "Brand", heading_xf)
1683 
    sheet.write(0, 5, "Product Name", heading_xf)
1684 
    sheet.write(0, 6, "Weight", heading_xf)
1685 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1686 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1687 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1688 
    sheet.write(0, 10, "Return Provision", heading_xf)
1689 
    sheet.write(0, 11, "Our Rating", heading_xf)
1690 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1691 
    sheet.write(0, 13, "Our Rank", heading_xf)
1692 
    sheet.write(0, 14, "Our SP", heading_xf)
1693 
    sheet.write(0, 15, "Our TP", heading_xf)
1694 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1695 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1696 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1697 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1698 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1699 
    sheet.write(0, 21, "Preffered Seller", heading_xf)
1700 
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
1701 
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
1702 
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
1703 
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
1704 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
1705 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
1706 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
1707 
    sheet.write(0, 29, "Average Sale", heading_xf)
1708 
    sheet.write(0, 30, "Our NLC", heading_xf)
1709 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
1710 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
1711 
    sheet.write(0, 33, "Margin", heading_xf)
1712 
    sheet.write(0, 34, "Total Seller", heading_xf)
11193 kshitij.so 1713 
    sheet_iterator = 1
11615 kshitij.so 1714 
 
11622 kshitij.so 1715 
    negativeMargin = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1716 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1717 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1718 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1719 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1720 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).all()
1721 
 
11193 kshitij.so 1722 
    for item in negativeMargin:
11615 kshitij.so 1723 
        mpHistory = item[0]
1724 
        flipkartItem = item[1]
1725 
        mpItem = item[2]
1726 
        catItem = item[3]
1727 
        sheet.write(sheet_iterator,0,mpHistory.item_id)
1728 
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1729 
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1730 
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1731 
        sheet.write(sheet_iterator,4,catItem.brand)
1732 
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1733 
        sheet.write(sheet_iterator,6,catItem.weight)
1734 
        sheet.write(sheet_iterator,7,mpItem.courierCost)
1735 
        sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1736 
        sheet.write(sheet_iterator,9,mpItem.commission)
1737 
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
1738 
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 1739 
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1740 
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1741 
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
1742 
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
1743 
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
1744 
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
1745 
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
1746 
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 1747 
#        lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
1748 
#        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 1749 
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
1750 
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
1751 
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
1752 
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
1753 
        sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
11615 kshitij.so 1754 
#        prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
1755 
#        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
11790 kshitij.so 1756 
        sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
1757 
        sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
1758 
        sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
1759 
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 1760 
        if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1761 
            sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 1762 
        else:
11790 kshitij.so 1763 
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1764 
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1765 
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
1766 
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
1767 
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
1768 
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
1769 
        sheet.write(sheet_iterator, 33, round((mpHistory.ourTp - mpHistory.lowestPossibleTp),2))
1770 
        sheet.write(sheet_iterator, 34, mpHistory.totalSeller)
11193 kshitij.so 1771 
        sheet_iterator+=1
11615 kshitij.so 1772 
 
1773 
    negativeMargin[:]=[]
11193 kshitij.so 1774 
 
1775 
    if (runType=='FULL'):
1776 
        sheet = wbk.add_sheet('Auto Favorites')
1777 
 
1778 
        heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1779 
 
1780 
        excel_integer_format = '0'
1781 
        integer_style = xlwt.XFStyle()
1782 
        integer_style.num_format_str = excel_integer_format
1783 
        xstr = lambda s: s or ""
1784 
 
1785 
        sheet.write(0, 0, "Item ID", heading_xf)
1786 
        sheet.write(0, 1, "Brand", heading_xf)
1787 
        sheet.write(0, 2, "Product Name", heading_xf)
1788 
        sheet.write(0, 3, "Auto Favourite", heading_xf)
1789 
        sheet.write(0, 4, "Reason", heading_xf)
1790 
 
1791 
        sheet_iterator=1
1792 
        for autoFav in nowAutoFav:
1793 
            itemId = autoFav[0]
1794 
            reason = autoFav[1]
1795 
            it = Item.query.filter_by(id=itemId).one()
1796 
            sheet.write(sheet_iterator, 0, itemId)
1797 
            sheet.write(sheet_iterator, 1, it.brand)
1798 
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1799 
            sheet.write(sheet_iterator, 3, "True")
1800 
            sheet.write(sheet_iterator, 4, reason)
1801 
            sheet_iterator+=1
1802 
        for prevFav in previousAutoFav:
1803 
            it = Item.query.filter_by(id=prevFav).one()
1804 
            sheet.write(sheet_iterator, 0, prevFav)
1805 
            sheet.write(sheet_iterator, 1, it.brand)
1806 
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1807 
            sheet.write(sheet_iterator, 3, "False")
1808 
            sheet_iterator+=1
1809 
 
1810 
    sheet = wbk.add_sheet('Exception Item List')
1811 
 
1812 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1813 
 
1814 
    excel_integer_format = '0'
1815 
    integer_style = xlwt.XFStyle()
1816 
    integer_style.num_format_str = excel_integer_format
1817 
    xstr = lambda s: s or ""
1818 
 
1819 
    sheet.write(0, 0, "Item ID", heading_xf)
1820 
    sheet.write(0, 1, "FK Serial number", heading_xf)
1821 
    sheet.write(0, 2, "Brand", heading_xf)
1822 
    sheet.write(0, 3, "Product Name", heading_xf)
1823 
    sheet.write(0, 4, "Reason", heading_xf)
1824 
    sheet_iterator=1
11615 kshitij.so 1825 
 
11622 kshitij.so 1826 
    exeptionItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1827 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1828 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1829 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1830 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1831 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.EXCEPTION).all()
1832 
 
1833 
    for item in exeptionItems:
1834 
        mpHistory = item[0]
1835 
        flipkartItem = item[1]
1836 
        mpItem = item[2]
1837 
        catItem = item[3]
1838 
        sheet.write(sheet_iterator, 0, mpHistory.item_id)
1839 
        sheet.write(sheet_iterator, 1, flipkartItem.flipkartSerialNumber)
1840 
        sheet.write(sheet_iterator, 2, catItem.brand)
1841 
        sheet.write(sheet_iterator, 3, xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
11193 kshitij.so 1842 
        try:
11615 kshitij.so 1843 
            if mpHistory.totalSeller is None:
11193 kshitij.so 1844 
                pass
1845 
        except:
1846 
            sheet.write(sheet_iterator, 4, "Unable to fetch info from Flipkart")
1847 
            sheet_iterator+=1
1848 
            continue
1849 
        sheet.write(sheet_iterator, 4, "No Seller Available")
1850 
        sheet_iterator+=1
11615 kshitij.so 1851 
 
1852 
    exeptionItems[:]=[]
11193 kshitij.so 1853 
 
1854 
    sheet = wbk.add_sheet('Can Compete-No Inv')
1855 
    xstr = lambda s: s or ""
1856 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1857 
 
1858 
    excel_integer_format = '0'
1859 
    integer_style = xlwt.XFStyle()
1860 
    integer_style.num_format_str = excel_integer_format
1861 
 
1862 
    sheet.write(0, 0, "Item ID", heading_xf)
1863 
    sheet.write(0, 1, "Category", heading_xf)
1864 
    sheet.write(0, 2, "Product Group.", heading_xf)
1865 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1866 
    sheet.write(0, 4, "Brand", heading_xf)
1867 
    sheet.write(0, 5, "Product Name", heading_xf)
1868 
    sheet.write(0, 6, "Weight", heading_xf)
1869 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1870 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1871 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1872 
    sheet.write(0, 10, "Return Provision", heading_xf)
1873 
    sheet.write(0, 11, "Our Rating", heading_xf)
1874 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1875 
    sheet.write(0, 13, "Our Rank", heading_xf)
1876 
    sheet.write(0, 14, "Our SP", heading_xf)
1877 
    sheet.write(0, 15, "Our TP", heading_xf)
1878 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1879 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1880 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1881 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1882 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1883 
    sheet.write(0, 21, "Preffered Seller", heading_xf)
1884 
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
1885 
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
1886 
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
1887 
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
1888 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
1889 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
1890 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
1891 
    sheet.write(0, 29, "Average Sale", heading_xf)
1892 
    sheet.write(0, 30, "Our NLC", heading_xf)
1893 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
1894 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
1895 
    sheet.write(0, 33, "Target SP", heading_xf)
1896 
    sheet.write(0, 34, "Target TP", heading_xf)
1897 
    sheet.write(0, 35, "Sales Potential", heading_xf)
1898 
    sheet.write(0, 36, "Total Seller", heading_xf)
11193 kshitij.so 1899 
    sheet_iterator = 1
11615 kshitij.so 1900 
 
11622 kshitij.so 1901 
    competitiveNoInventory = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1902 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1903 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1904 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1905 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1906 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE_NO_INVENTORY).all()
1907 
 
11193 kshitij.so 1908 
    for item in competitiveNoInventory:
11615 kshitij.so 1909 
        mpHistory = item[0]
1910 
        flipkartItem = item[1]
1911 
        mpItem = item[2]
1912 
        catItem = item[3]
1913 
        if ((not inventoryMap.has_key(mpHistory.item_id)) or getNetAvailability(inventoryMap.get(mpHistory.item_id))<=0):
1914 
            sheet.write(sheet_iterator,0,mpHistory.item_id)
1915 
            sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1916 
            sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1917 
            sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1918 
            sheet.write(sheet_iterator,4,catItem.brand)
1919 
            sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1920 
            sheet.write(sheet_iterator,6,catItem.weight)
1921 
            sheet.write(sheet_iterator,7,mpItem.courierCost)
1922 
            sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1923 
            sheet.write(sheet_iterator,9,mpItem.commission)
1924 
            sheet.write(sheet_iterator,10,mpItem.returnProvision)
1925 
            sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 1926 
#            ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1927 
#            else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1928 
            sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
1929 
            sheet.write(sheet_iterator,13,mpHistory.ourRank)
1930 
            sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
1931 
            sheet.write(sheet_iterator,15,mpHistory.ourTp)
1932 
            sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
1933 
            sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 1934 
#            lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
1935 
#            else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 1936 
            sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
1937 
            sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
1938 
            sheet.write(sheet_iterator,20,mpHistory.lowestTp)
1939 
            sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
1940 
            sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
11615 kshitij.so 1941 
#            prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
1942 
#            else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
11790 kshitij.so 1943 
            sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
1944 
            sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
1945 
            sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
1946 
            sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 1947 
            if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1948 
                sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 1949 
            else:
11790 kshitij.so 1950 
                sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1951 
            sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1952 
            sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
1953 
            sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
1954 
            sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
1955 
            sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
11615 kshitij.so 1956 
            proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
11193 kshitij.so 1957 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
11790 kshitij.so 1958 
            sheet.write(sheet_iterator, 33, proposed_sp)
1959 
            sheet.write(sheet_iterator, 34, proposed_tp)
1960 
            sheet.write(sheet_iterator, 35, getSalesPotential(mpHistory.lowestPossibleSp,mpHistory.ourNlc))
1961 
            sheet.write(sheet_iterator, 36, mpHistory.totalSeller)
11193 kshitij.so 1962 
            sheet_iterator+=1
1963 
 
11615 kshitij.so 1964 
 
11193 kshitij.so 1965 
    sheet = wbk.add_sheet('Can Compete-No Inv On FK')
1966 
    xstr = lambda s: s or ""
1967 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1968 
 
1969 
    excel_integer_format = '0'
1970 
    integer_style = xlwt.XFStyle()
1971 
    integer_style.num_format_str = excel_integer_format
1972 
 
1973 
    sheet.write(0, 0, "Item ID", heading_xf)
1974 
    sheet.write(0, 1, "Category", heading_xf)
1975 
    sheet.write(0, 2, "Product Group.", heading_xf)
1976 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1977 
    sheet.write(0, 4, "Brand", heading_xf)
1978 
    sheet.write(0, 5, "Product Name", heading_xf)
1979 
    sheet.write(0, 6, "Weight", heading_xf)
1980 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1981 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1982 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1983 
    sheet.write(0, 10, "Return Provision", heading_xf)
1984 
    sheet.write(0, 11, "Our Rating", heading_xf)
1985 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1986 
    sheet.write(0, 13, "Our Rank", heading_xf)
1987 
    sheet.write(0, 14, "Our SP", heading_xf)
1988 
    sheet.write(0, 15, "Our TP", heading_xf)
1989 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1990 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1991 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1992 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1993 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1994 
    sheet.write(0, 21, "Preffered Seller", heading_xf)
1995 
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
1996 
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
1997 
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
1998 
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
1999 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
2000 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
2001 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
2002 
    sheet.write(0, 29, "Average Sale", heading_xf)
2003 
    sheet.write(0, 30, "Our NLC", heading_xf)
2004 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
2005 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
2006 
    sheet.write(0, 33, "Target SP", heading_xf)
2007 
    sheet.write(0, 34, "Target TP", heading_xf)
2008 
    sheet.write(0, 35, "Sales Potential", heading_xf)
2009 
    sheet.write(0, 36, "Total Seller", heading_xf)
11193 kshitij.so 2010 
    sheet_iterator = 1
2011 
    for item in competitiveNoInventory:
11615 kshitij.so 2012 
        mpHistory = item[0]
2013 
        flipkartItem = item[1]
2014 
        mpItem = item[2]
2015 
        catItem = item[3]
2016 
        if (inventoryMap.has_key(mpHistory.item_id) and getNetAvailability(inventoryMap.get(mpHistory.item_id))>0):
2017 
            sheet.write(sheet_iterator,0,mpHistory.item_id)
2018 
            sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
2019 
            sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
2020 
            sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
2021 
            sheet.write(sheet_iterator,4,catItem.brand)
2022 
            sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
2023 
            sheet.write(sheet_iterator,6,catItem.weight)
2024 
            sheet.write(sheet_iterator,7,mpItem.courierCost)
2025 
            sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 2026 
            sheet.write(sheet_iterator,9,mpItem.commission)
2027 
            sheet.write(sheet_iterator,10,mpItem.returnProvision)
2028 
            sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 2029 
#            ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
2030 
#            else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 2031 
            sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
2032 
            sheet.write(sheet_iterator,13,mpHistory.ourRank)
2033 
            sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
2034 
            sheet.write(sheet_iterator,15,mpHistory.ourTp)
2035 
            sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
2036 
            sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 2037 
#            lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
2038 
#            else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 2039 
            sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
2040 
            sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
2041 
            sheet.write(sheet_iterator,20,mpHistory.lowestTp)
2042 
            sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
2043 
            sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
11615 kshitij.so 2044 
#            prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
2045 
#            else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
11790 kshitij.so 2046 
            sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
2047 
            sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
2048 
            sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
2049 
            sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 2050 
            if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 2051 
                sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 2052 
            else:
11790 kshitij.so 2053 
                sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
2054 
            sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
2055 
            sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
2056 
            sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
2057 
            sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
2058 
            sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
11615 kshitij.so 2059 
            proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
11193 kshitij.so 2060 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
11790 kshitij.so 2061 
            sheet.write(sheet_iterator, 33, proposed_sp)
2062 
            sheet.write(sheet_iterator, 34, proposed_tp)
2063 
            sheet.write(sheet_iterator, 35, getSalesPotential(mpHistory.lowestPossibleSp,mpHistory.ourNlc))
2064 
            sheet.write(sheet_iterator, 36, mpHistory.totalSeller)
11193 kshitij.so 2065 
            sheet_iterator+=1
11615 kshitij.so 2066 
    competitiveNoInventory[:]=[]
11193 kshitij.so 2067 
 
11775 kshitij.so 2068 
#    autoPricingItems = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.decision.in_([1,2,3,4])).all()
2069 
#    sheet = wbk.add_sheet('Auto Inc and Dec')
2070 
#
2071 
#    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
2072 
#
2073 
#    excel_integer_format = '0'
2074 
#    integer_style = xlwt.XFStyle()
2075 
#    integer_style.num_format_str = excel_integer_format
2076 
#    xstr = lambda s: s or ""
2077 
#
2078 
#    sheet.write(0, 0, "Item ID", heading_xf)
2079 
#    sheet.write(0, 1, "Brand", heading_xf)
2080 
#    sheet.write(0, 2, "Product Name", heading_xf)
2081 
#    sheet.write(0, 3, "Decision", heading_xf)
2082 
#    sheet.write(0, 4, "Reason", heading_xf)
2083 
#    sheet.write(0, 5, "Old Selling Price", heading_xf)
2084 
#    sheet.write(0, 6, "Selling Price Updated",heading_xf)
2085 
#
2086 
#    sheet_iterator=1
2087 
#    for autoPricingItem in autoPricingItems:
2088 
#        mpHistory = autoPricingItem[0]
2089 
#        item = autoPricingItem[1]
2090 
#        it = Item.query.filter_by(id=item.id).one()
2091 
#        sheet.write(sheet_iterator, 0, item.id)
2092 
#        sheet.write(sheet_iterator, 1, it.brand)
2093 
#        sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
2094 
#        sheet.write(sheet_iterator, 3, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
2095 
#        sheet.write(sheet_iterator, 4, mpHistory.reason)
2096 
#        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
2097 
#            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
2098 
#            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.proposedSellingPrice))
2099 
#        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
2100 
#            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
2101 
#            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
2102 
#        sheet_iterator+=1
11193 kshitij.so 2103 
 
2104 
    filename = "/tmp/flipkart-report-"+runType+" " + str(timestamp) + ".xls"
2105 
    wbk.save(filename)
2106 
    try:
12219 kshitij.so 2107 
        #EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Flipkart Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
2108 
        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"], " Flipkart Scraping "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], ["rajneesh.arora@saholic.com","anikendra.das@saholic.com","vikram.raghav@saholic.com","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
11193 kshitij.so 2109 
    except Exception as e:
2110 
        print e
2111 
        print "Unable to send report.Trying with local SMTP"
2112 
        smtpServer = smtplib.SMTP('localhost')
2113 
        smtpServer.set_debuglevel(1)
11779 kshitij.so 2114 
        sender = 'build@shop2020.in'
12219 kshitij.so 2115 
        #recipients = ["kshitij.sood@saholic.com"]
11193 kshitij.so 2116 
        msg = MIMEMultipart()
11228 kshitij.so 2117 
        msg['Subject'] = "Flipkart Scraping" + ' '+runType+' - ' + str(datetime.now())
11193 kshitij.so 2118 
        msg['From'] = sender
12219 kshitij.so 2119 
        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']
11193 kshitij.so 2120 
        msg['To'] = ",".join(recipients)
2121 
        fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
2122 
        fileMsg.set_payload(file(filename).read())
2123 
        email.encoders.encode_base64(fileMsg)
2124 
        fileMsg.add_header('Content-Disposition','attachment;filename=flipkart.xls')
2125 
        msg.attach(fileMsg)
2126 
        try:
2127 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2128 
            print "Successfully sent email"
2129 
        except:
2130 
            print "Error: unable to send email."
11560 kshitij.so 2131 
 
11571 kshitij.so 2132 
def populateScrapingResults(val):
2133 
    try:
12193 kshitij.so 2134 
        now = datetime.now()
2135 
        print "Fetching data for serial Number %s %s" %(val.fkSerialNumber,str(now))
12211 kshitij.so 2136 
        flipkartDetails = fetchDetails(val.fkSerialNumber)
2137 
        val.flipkartDetails = flipkartDetails
11571 kshitij.so 2138 
    except Exception as e:
12193 kshitij.so 2139 
        print "Unable to fetch details of %s" %(val.fkSerialNumber)
11571 kshitij.so 2140 
        print e
2141 
        val.flipkartDetails = None
2142 
        return
2143 
 
12214 kshitij.so 2144 
    try:
2145 
        request_url = "https://api.flipkart.net/sellers/skus/%s/listings"%(str(val.skuAtFlipkart))
2146 
        r = requests.get(request_url, auth=('m2z93iskuj81qiid', '0c7ab6a5-98c0-4cdc-8be3-72c591e0add4'))
2147 
        print "Inventory info",r.json()
2148 
        stock_count = int((r.json()['attributeValues'])['stock_count'])
2149 
    except:
2150 
        stock_count = 0
2151 
    finally:
2152 
        r={}
2153 
 
2154 
    val.ourFlipkartInventory = stock_count
11560 kshitij.so 2155 
 
12213 kshitij.so 2156 
def threadsToSpawn(runType,itemInfo,itemPopulated):
11560 kshitij.so 2157 
    if runType == RunType.FAVOURITE:
11615 kshitij.so 2158 
        count = 0
2159 
        pool = ThreadPool(3)
2160 
        startOffset = 0
11560 kshitij.so 2161 
        endOffset = startOffset
11615 kshitij.so 2162 
        while(count<3 and endOffset<len(itemInfo)):
11560 kshitij.so 2163 
            endOffset = startOffset + 20
2164 
            if (endOffset >= len(itemInfo)):
2165 
                endOffset = len(itemInfo)
11615 kshitij.so 2166 
            print "pool offset start end count"+str(startOffset)+" "+str(endOffset)+" "+str(count)
2167 
            pool.map(populateScrapingResults,itemInfo[startOffset:endOffset])
2168 
            #t = Process(target=decideCategory,args=(itemInfo[startOffset:endOffset], scraper))
2169 
            #t = threading.Thread(target=partial(decideCategory, itemInfo[startOffset:endOffset], scraper))
11560 kshitij.so 2170 
            #t = threading.Thread(target=partial(test, startOffset, endOffset))
11615 kshitij.so 2171 
            #threads.append(t)
11560 kshitij.so 2172 
            startOffset = startOffset + 20
11615 kshitij.so 2173 
            count+=1
2174 
        #[t.start() for t in threads]
2175 
        #[t.join() for t in threads]
2176 
        #threads = []
2177 
        pool.close()
2178 
        pool.join()
11560 kshitij.so 2179 
        return endOffset
2180 
    else:
11561 kshitij.so 2181 
        count = 0
12195 kshitij.so 2182 
        pool = ThreadPool(50)
11581 kshitij.so 2183 
        startOffset = 0
11560 kshitij.so 2184 
        endOffset = startOffset
12218 kshitij.so 2185 
        while(count<1 and endOffset<len(itemInfo)):
2186 
            endOffset = startOffset + 50
11560 kshitij.so 2187 
            if (endOffset >= len(itemInfo)):
2188 
                endOffset = len(itemInfo)
11581 kshitij.so 2189 
            print "pool offset start end count"+str(startOffset)+" "+str(endOffset)+" "+str(count)
12218 kshitij.so 2190 
            pool.map(populateScrapingResults,itemInfo[startOffset:endOffset])
11561 kshitij.so 2191 
            #t = Process(target=decideCategory,args=(itemInfo[startOffset:endOffset], scraper))
11560 kshitij.so 2192 
            #t = threading.Thread(target=partial(decideCategory, itemInfo[startOffset:endOffset], scraper))
2193 
            #t = threading.Thread(target=partial(test, startOffset, endOffset))
11561 kshitij.so 2194 
            #threads.append(t)
12218 kshitij.so 2195 
            startOffset = startOffset + 50
11561 kshitij.so 2196 
            count+=1
2197 
        #[t.start() for t in threads]
2198 
        #[t.join() for t in threads]
2199 
        #threads = []
11581 kshitij.so 2200 
        print "terminating while"
12218 kshitij.so 2201 
        pool.close()
2202 
        pool.join()
11581 kshitij.so 2203 
        print "joining threads"
2204 
        print "returning offset******"
11560 kshitij.so 2205 
        return endOffset
2206 
 
11623 kshitij.so 2207 
def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
2208 
    if len(successfulAutoDecrease)==0 and len(successfulAutoIncrease)==0 :
2209 
        return
2210 
    xstr = lambda s: s or ""
2211 
    catalog_client = CatalogClient().get_client()
2212 
    inventory_client = InventoryClient().get_client()
2213 
    message="""<html>
2214 
            <body>
2215 
            <h3>Auto Decrease Items</h3>
2216 
            <table border="1" style="width:100%;">
2217 
            <thead>
2218 
            <tr><th>Item Id</th>
2219 
            <th>Product Name</th>
2220 
            <th>Old Price</th>
2221 
            <th>New Price</th>
2222 
            <th>Old Margin</th>
2223 
            <th>New Margin</th>
11790 kshitij.so 2224 
            <th>Commission %</th>
2225 
            <th>Return Provision %</th>
11623 kshitij.so 2226 
            <th>Flipkart Inventory</th>
2227 
            <th>Sales History</th>
12158 kshitij.so 2228 
            <th>Category</th>
11623 kshitij.so 2229 
            </tr></thead>
2230 
            <tbody>"""
2231 
    for item in successfulAutoDecrease:
2232 
        it = Item.query.filter_by(id=item.item_id).one()
2233 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
2234 
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
2235 
        warehouse = inventory_client.getWarehouse(fkItem.warehouseId)
2236 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
2237 
        newMargin = round(getNewOurTp(mpItem,item.proposedSellingPrice) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice))
2238 
        message+="""<tr>
2239 
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
2240 
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
2241 
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
2242 
                <td style="text-align:center">"""+str(math.ceil(item.proposedSellingPrice))+"""</td>
2243 
                <td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
2244 
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/item.proposedSellingPrice)*100,1))+"%)"+"""</td>
11806 kshitij.so 2245 
                <td style="text-align:center">"""+str(mpItem.commission)+" %"+"""</td>
11790 kshitij.so 2246 
                <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
11806 kshitij.so 2247 
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
11623 kshitij.so 2248 
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
12158 kshitij.so 2249 
                <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(item.competitiveCategory))+"""</td>
11623 kshitij.so 2250 
                </tr>"""
2251 
    message+="""</tbody></table><h3>Auto Increase Items</h3><table border="1" style="width:100%;">
2252 
            <thead>
2253 
            <tr><th>Item Id</th>
2254 
            <th>Product Name</th>
2255 
            <th>Old Price</th>
2256 
            <th>New Price</th>
2257 
            <th>Old Margin</th>
2258 
            <th>New Margin</th>
11790 kshitij.so 2259 
            <th>Commission %</th>
2260 
            <th>Return Provision %</th>
11623 kshitij.so 2261 
            <th>Flipkart Inventory</th>
2262 
            <th>Sales History</th>
12158 kshitij.so 2263 
            <th>Category</th>
11623 kshitij.so 2264 
            </tr></thead>
2265 
            <tbody>"""
2266 
    for item in successfulAutoIncrease:
2267 
        it = Item.query.filter_by(id=item.item_id).one()
2268 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
2269 
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
2270 
        warehouse = inventory_client.getWarehouse(fkItem.warehouseId)
2271 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
2272 
        newMargin = round(getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
2273 
        message+="""<tr>
2274 
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
2275 
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
2276 
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
2277 
                <td style="text-align:center">"""+str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))+"""</td>
2278 
                <td style="text-align:center">"""+str(round((item.margin),1))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
2279 
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))*100,1))+"%)"+"""</td>
11806 kshitij.so 2280 
                <td style="text-align:center">"""+str(mpItem.commission)+" %"+"""</td>
11790 kshitij.so 2281 
                <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
11623 kshitij.so 2282 
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
2283 
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
12158 kshitij.so 2284 
                <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(item.competitiveCategory))+"""</td>
11623 kshitij.so 2285 
                </tr>"""
2286 
    message+="""</tbody></table></body></html>"""
2287 
    print message
2288 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
2289 
    mailServer.ehlo()
2290 
    mailServer.starttls()
2291 
    mailServer.ehlo()
2292 
 
12219 kshitij.so 2293 
    #recipients = ['kshitij.sood@saholic.com']
2294 
    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']
11623 kshitij.so 2295 
    msg = MIMEMultipart()
2296 
    msg['Subject'] = "Flipkart Auto Pricing" + ' - ' + str(datetime.now())
2297 
    msg['From'] = ""
2298 
    msg['To'] = ",".join(recipients)
2299 
    msg.preamble = "Flipkart Auto Pricing" + ' - ' + str(datetime.now())
2300 
    html_msg = MIMEText(message, 'html')
2301 
    msg.attach(html_msg)
2302 
    try:
2303 
        mailServer.login("build@shop2020.in", "cafe@nes")
2304 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
2305 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
2306 
    except Exception as e:
2307 
        print e
2308 
        print "Unable to send pricing mail.Lets try with local SMTP."
2309 
        smtpServer = smtplib.SMTP('localhost')
2310 
        smtpServer.set_debuglevel(1)
11779 kshitij.so 2311 
        sender = 'build@shop2020.in'
11623 kshitij.so 2312 
        try:
2313 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2314 
            print "Successfully sent email"
2315 
        except:
2316 
            print "Error: unable to send email."
2317 
 
2318 
def processLostBuyBoxItems(previousProcessingTimestamp,currentTimestamp):
2319 
    previous_buy_box = session.query(MarketPlaceHistory.item_id).filter(MarketPlaceHistory.timestamp==previousProcessingTimestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(or_(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX,MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP)).all()
12225 kshitij.so 2320 
    print "previous buy box ",previous_buy_box
11623 kshitij.so 2321 
    cant_compete = session.query(MarketPlaceHistory.item_id).filter(MarketPlaceHistory.timestamp==currentTimestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
12225 kshitij.so 2322 
    print "cant compete ",cant_compete
12218 kshitij.so 2323 
    if previous_buy_box is None or previous_buy_box==[]:
11623 kshitij.so 2324 
        print "No item in buy box for last run"
2325 
        return
2326 
    lost_buy_box = list(set(list(zip(*previous_buy_box)[0]))&set(list(zip(*cant_compete)[0])))
2327 
    if len(lost_buy_box)==0:
2328 
        return
2329 
    xstr = lambda s: s or ""
2330 
    message="""<html>
2331 
            <body>
2332 
            <h3>Lost Buy Box</h3>
2333 
            <table border="1" style="width:100%;">
2334 
            <thead>
2335 
            <tr><th>Item Id</th>
2336 
            <th>Product Name</th>
2337 
            <th>Current Price</th>
2338 
            <th>Current Margin</th>
11844 kshitij.so 2339 
            <th>Lowest Seller</th>
2340 
            <th>Lowest Selling Price</th>
2341 
            <th>Preffered Seller</th>
2342 
            <th>Preffered Selling Price</th>
11623 kshitij.so 2343 
            <th>NLC</th>
2344 
            <th>Target NLC</th>
11790 kshitij.so 2345 
            <th>Commission %</th>
2346 
            <th>Return Provision %</th>
11623 kshitij.so 2347 
            <th>Flipkart Inventory</th>
2348 
            <th>Total Inventory</th>
2349 
            <th>Sales History</th>
2350 
            </tr></thead>
2351 
            <tbody>"""
2352 
    items = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==currentTimestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.item_id.in_(lost_buy_box)).all()
2353 
    for item in items:
2354 
        it = Item.query.filter_by(id=item.item_id).one()
11790 kshitij.so 2355 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
11623 kshitij.so 2356 
        netInventory=''
2357 
        if not inventoryMap.has_key(item.item_id):
2358 
            netInventory='Info Not Available'
2359 
        else:
2360 
            netInventory = str(getNetAvailability(inventoryMap.get(item.item_id)))
2361 
        message+="""<tr>
2362 
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
2363 
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
2364 
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
2365 
                <td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
11844 kshitij.so 2366 
                <td style="text-align:center">"""+str(item.lowestSellerName)+"""</td>
2367 
                <td style="text-align:center">"""+str(item.lowestSellingPrice)+"""</td>
2368 
                <td style="text-align:center">"""+str(item.prefferedSellerName)+"""</td>
2369 
                <td style="text-align:center">"""+str(item.prefferedSellerSellingPrice)+"""</td>
11623 kshitij.so 2370 
                <td style="text-align:center">"""+str(item.ourNlc)+"""</td>
2371 
                <td style="text-align:center">"""+str(item.targetNlc)+"""</td>
11790 kshitij.so 2372 
                <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
2373 
                <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
11623 kshitij.so 2374 
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
2375 
                <td style="text-align:center">"""+netInventory+"""</td>
2376 
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
2377 
                </tr>"""
2378 
    message+="""</tbody></table></body></html>"""
2379 
    print message
2380 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
2381 
    mailServer.ehlo()
2382 
    mailServer.starttls()
2383 
    mailServer.ehlo()
2384 
 
11791 kshitij.so 2385 
    #recipients = ['kshitij.sood@saholic.com']
2386 
    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']
11623 kshitij.so 2387 
    msg = MIMEMultipart()
2388 
    msg['Subject'] = "Flipkart Lost Buy Box" + ' - ' + str(datetime.now())
2389 
    msg['From'] = ""
2390 
    msg['To'] = ",".join(recipients)
2391 
    msg.preamble = "Flipkart Lost Buy Box" + ' - ' + str(datetime.now())
2392 
    html_msg = MIMEText(message, 'html')
2393 
    msg.attach(html_msg)
2394 
    try:
2395 
        mailServer.login("build@shop2020.in", "cafe@nes")
2396 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
2397 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
2398 
    except Exception as e:
2399 
        print e
2400 
        print "Unable to send lost buy box mail.Lets try local SMTP"
2401 
        smtpServer = smtplib.SMTP('localhost')
2402 
        smtpServer.set_debuglevel(1)
11779 kshitij.so 2403 
        sender = 'build@shop2020.in'
11623 kshitij.so 2404 
        try:
2405 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2406 
            print "Successfully sent email"
2407 
        except:
2408 
            print "Error: unable to send email."
2409 
 
2410 
def cheapButNotPrefAlert(timestamp):
11626 kshitij.so 2411 
    cheap_but_not_pref = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CHEAP_BUT_NOT_PREF).all()
11758 kshitij.so 2412 
    if len(cheap_but_not_pref)==0:
11623 kshitij.so 2413 
        return
2414 
    xstr = lambda s: s or ""
2415 
    message="""<html>
2416 
            <body>
2417 
            <h3>Cheap But Not Preferred</h3>
2418 
            <table border="1" style="width:100%;">
2419 
            <thead>
2420 
            <tr><th>Item Id</th>
2421 
            <th>Product Name</th>
2422 
            <th>Current Price</th>
11670 kshitij.so 2423 
            <th>Our Rating</th>
2424 
            <th>Our Shipping Time</th>
11623 kshitij.so 2425 
            <th>Preffered Seller</th>
2426 
            <th>Preffered Seller SP</th>
11670 kshitij.so 2427 
            <th>Preffered Seller Rating</th>
2428 
            <th>Preffered Seller Shipping Time</th>
2429 
            <th>Price Variance %</th>
11792 kshitij.so 2430 
            <th>Commission %</th>
2431 
            <th>Return Provision %</th>
11623 kshitij.so 2432 
            <th>Flipkart Inventory</th>
2433 
            <th>Total Inventory</th>
2434 
            <th>Sales History</th>
2435 
            </tr></thead>
2436 
            <tbody>"""
2437 
    for item in cheap_but_not_pref:
2438 
        mpHistory = item[0]
2439 
        catItem = item[1]
2440 
        netInventory=''
2441 
        if not inventoryMap.has_key(mpHistory.item_id):
2442 
            netInventory='Info Not Available'
2443 
        else:
2444 
            netInventory = str(getNetAvailability(inventoryMap.get(mpHistory.item_id)))
11670 kshitij.so 2445 
        ourSt = mpHistory.ourShippingTime.split('-')
2446 
        pfSt = mpHistory.prefferedSellerShippingTime.split('-')
11792 kshitij.so 2447 
        mpItem = MarketplaceItems.get_by(itemId=mpHistory.item_id,source=OrderSource.FLIPKART)
11670 kshitij.so 2448 
        if mpHistory.prefferedSellerName=='WS Retail' and mpHistory.ourRating > mpHistory.prefferedSellerRating and int(ourSt[0])<=int(pfSt[0]):
11623 kshitij.so 2449 
            style="""background-color:red;\""""
2450 
        else:
2451 
            style="\""
11754 kshitij.so 2452 
        message+="""<tr>
2453 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.item_id)+"""</td>
2454 
            <td style="text-align:center;"""+str(style)+""">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
2455 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourSellingPrice)+"""</td>
2456 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourRating)+"""</td>
2457 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourShippingTime)+"""</td>
2458 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerName)+"""</td>
2459 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerSellingPrice)+"""</td>
2460 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerRating)+"""</td>
2461 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerShippingTime)+"""</td>
2462 
            <td style="text-align:center;"""+str(style)+""">"""+str(round(((mpHistory.prefferedSellerSellingPrice-mpHistory.ourSellingPrice)/mpHistory.ourSellingPrice)*100))+"%"+"""</td>
11806 kshitij.so 2463 
            <td style="text-align:center">"""+str(mpItem.commission)+" %"+"""</td>
11792 kshitij.so 2464 
            <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
11754 kshitij.so 2465 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourInventory)+"""</td>
2466 
            <td style="text-align:center;"""+str(style)+""">"""+netInventory+"""</td>
2467 
            <td style="text-align:center;"""+str(style)+""">"""+getOosString((itemSaleMap.get(mpHistory.item_id))[1])+"""</td>
2468 
            </tr>"""
11623 kshitij.so 2469 
    message+="""</tbody></table></body></html>"""
2470 
    print message
2471 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
2472 
    mailServer.ehlo()
2473 
    mailServer.starttls()
2474 
    mailServer.ehlo()
2475 
 
12219 kshitij.so 2476 
    #recipients = ['kshitij.sood@saholic.com']
2477 
    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']
11623 kshitij.so 2478 
    msg = MIMEMultipart()
2479 
    msg['Subject'] = "Flipkart Cheap But Not In BuyBox Items" + ' - ' + str(datetime.now())
2480 
    msg['From'] = ""
2481 
    msg['To'] = ",".join(recipients)
2482 
    msg.preamble = "Flipkart Cheap But Not In BuyBox Items" + ' - ' + str(datetime.now())
2483 
    html_msg = MIMEText(message, 'html')
2484 
    msg.attach(html_msg)
2485 
    try:
2486 
        mailServer.login("build@shop2020.in", "cafe@nes")
2487 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
2488 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
2489 
    except Exception as e:
2490 
        print e
2491 
        print "Unable to send Flipkart Cheap But Not In BuyBox Items mail.Lets try local SMTP"
2492 
        smtpServer = smtplib.SMTP('localhost')
2493 
        smtpServer.set_debuglevel(1)
11779 kshitij.so 2494 
        sender = 'build@shop2020.in'
11623 kshitij.so 2495 
        try:
2496 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2497 
            print "Successfully sent email"
2498 
        except:
2499 
            print "Error: unable to send email."
11754 kshitij.so 2500 
 
2501 
def sendPricingMismatch(timestamp):
2502 
    xstr = lambda s: s or ""
2503 
    message="""<html>
2504 
            <body>
2505 
            <h3>Flipkart Pricing Mismatch</h3>
2506 
            <table border="1" style="width:100%;">
2507 
            <thead>
2508 
            <tr><th>Item Id</th>
2509 
            <th>Product Name</th>
2510 
            <th>Our System Price</th>
2511 
            <th>Flipkart Price</th>
2512 
            <th>Flipkart Inventory</th>
2513 
            <th>Total Inventory</th>
2514 
            <th>Sales History</th>
2515 
            </tr></thead>
2516 
            <tbody>"""
2517 
    flipkartPricing = {}
2518 
    saholicPricing = {}
2519 
    mpHistoryItems = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).all()
2520 
    for val in mpHistoryItems:
2521 
        temp = []
2522 
        temp.append(val[0].ourSellingPrice)
2523 
        temp.append(xstr(val[1].brand)+" "+xstr(val[1].model_name)+" "+xstr(val[1].model_number)+" "+xstr(val[1].color))
11755 kshitij.so 2524 
        temp.append(val[0].ourInventory)
11754 kshitij.so 2525 
        flipkartPricing[val[0].item_id] = temp
2526 
    mpHistoryItems[:] = []
2527 
    mpItems = session.query(MarketplaceItems).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
2528 
    for val in mpItems:
2529 
        saholicPricing[val.itemId] = val.currentSp
2530 
    mpItems[:] = []
2531 
    mismatches = []
2532 
    for k,v in flipkartPricing.iteritems():
2533 
        flipkartSellingPrice = v[0]
2534 
        ourSellingPrice = saholicPricing.get(k)
11771 kshitij.so 2535 
        if flipkartSellingPrice is not None and not((ourSellingPrice - flipkartSellingPrice >= -3) and (ourSellingPrice - flipkartSellingPrice <=3)):
11754 kshitij.so 2536 
            mismatches.append(k)
2537 
    print "mismatches are ",mismatches
11755 kshitij.so 2538 
    if len(mismatches)==0:
2539 
        return
11754 kshitij.so 2540 
    for item in mismatches:
2541 
        netInventory=''
2542 
        if not inventoryMap.has_key(item):
2543 
            netInventory='Info Not Available'
2544 
        else:
2545 
            netInventory = str(getNetAvailability(inventoryMap.get(item)))
2546 
        message+="""<tr>
11756 kshitij.so 2547 
            <td style="text-align:center">"""+str(item)+"""</td>
2548 
            <td style="text-align:center">"""+str((flipkartPricing.get(item))[1])+"""</td>
2549 
            <td style="text-align:center">"""+str(saholicPricing.get(item))+"""</td>
2550 
            <td style="text-align:center">"""+str((flipkartPricing.get(item))[0])+"""</td>
2551 
            <td style="text-align:center">"""+str((flipkartPricing.get(item))[2])+"""</td>
2552 
            <td style="text-align:center">"""+netInventory+"""</td>
2553 
            <td style="text-align:center">"""+getOosString((itemSaleMap.get(item))[1])+"""</td>
11754 kshitij.so 2554 
            </tr>"""
2555 
    message+="""</tbody></table></body></html>"""
2556 
    print message
2557 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
2558 
    mailServer.ehlo()
2559 
    mailServer.starttls()
2560 
    mailServer.ehlo()
2561 
 
12219 kshitij.so 2562 
    #recipients = ['kshitij.sood@saholic.com']
2563 
    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']
11754 kshitij.so 2564 
    msg = MIMEMultipart()
2565 
    msg['Subject'] = "Flipkart Price Mismatch" + ' - ' + str(datetime.now())
2566 
    msg['From'] = ""
2567 
    msg['To'] = ",".join(recipients)
2568 
    msg.preamble = "Flipkart Price Mismatch" + ' - ' + str(datetime.now())
2569 
    html_msg = MIMEText(message, 'html')
2570 
    msg.attach(html_msg)
2571 
    try:
2572 
        mailServer.login("build@shop2020.in", "cafe@nes")
2573 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
2574 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
2575 
    except Exception as e:
2576 
        print e
2577 
        print "Unable to send Flipkart Price Mismatch mail.Lets try local SMTP"
2578 
        smtpServer = smtplib.SMTP('localhost')
2579 
        smtpServer.set_debuglevel(1)
11779 kshitij.so 2580 
        sender = 'build@shop2020.in'
11754 kshitij.so 2581 
        try:
2582 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2583 
            print "Successfully sent email"
2584 
        except:
2585 
            print "Error: unable to send email."
11775 kshitij.so 2586 
 
2587 
def sendAlertForNegativeMargins(timestamp):
2588 
    xstr = lambda s: s or ""
2589 
    negativeMargins = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).all()
2590 
    if len(negativeMargins) == 0:
2591 
        return
2592 
    message="""<html>
2593 
            <body>
2594 
            <h3 style="color:red;font-weight:bold;">Flipkart Negative Margins</h3>
2595 
            <table border="1" style="width:100%;">
2596 
            <thead>
2597 
            <tr><th>Item Id</th>
2598 
            <th>Product Name</th>
2599 
            <th>SP</th>
2600 
            <th>TP</th>
2601 
            <th>Lowest Possible SP</th>
2602 
            <th>Lowest Possible TP</th>
2603 
            <th>Margin</th>
2604 
            <th>Margin %</th>
11790 kshitij.so 2605 
            <th>Commission %</th>
2606 
            <th>Return Provision %</th>
11775 kshitij.so 2607 
            <th>Flipkart Inventory</th>
2608 
            <th>Total Inventory</th>
2609 
            <th>Sales History</th>
2610 
            </tr></thead>
2611 
            <tbody>"""
2612 
    for item in negativeMargins:
2613 
        mpHistory = item[0]
2614 
        catItem = item[1]
2615 
        netInventory=''
11776 kshitij.so 2616 
        if not inventoryMap.has_key(mpHistory.item_id):
11775 kshitij.so 2617 
            netInventory='Info Not Available'
2618 
        else:
11776 kshitij.so 2619 
            netInventory = str(getNetAvailability(inventoryMap.get(mpHistory.item_id)))
11790 kshitij.so 2620 
        mpItem = MarketplaceItems.get_by(itemId=mpHistory.item_id,source=OrderSource.FLIPKART)
11775 kshitij.so 2621 
        message+="""<tr>
2622 
            <td style="text-align:center">"""+str(mpHistory.item_id)+"""</td>
2623 
            <td style="text-align:center">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
2624 
            <td style="text-align:center">"""+str(mpHistory.ourSellingPrice)+"""</td>
2625 
            <td style="text-align:center">"""+str(mpHistory.ourTp)+"""</td>
2626 
            <td style="text-align:center">"""+str(mpHistory.lowestPossibleSp)+"""</td>
2627 
            <td style="text-align:center">"""+str(mpHistory.lowestPossibleTp)+"""</td>
2628 
            <td style="text-align:center">"""+str(mpHistory.margin)+"""</td>
11952 kshitij.so 2629 
            <td style="text-align:center">"""+str(round((mpHistory.margin/mpHistory.ourSellingPrice)*100,1))+" %"+"""</td>
11790 kshitij.so 2630 
            <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
2631 
            <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
11775 kshitij.so 2632 
            <td style="text-align:center">"""+str(mpHistory.ourInventory)+"""</td>
2633 
            <td style="text-align:center">"""+netInventory+"""</td>
11776 kshitij.so 2634 
            <td style="text-align:center">"""+getOosString((itemSaleMap.get(mpHistory.item_id))[1])+"""</td>
11775 kshitij.so 2635 
            </tr>"""
2636 
    message+="""</tbody></table></body></html>"""
2637 
    print message
2638 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
2639 
    mailServer.ehlo()
2640 
    mailServer.starttls()
2641 
    mailServer.ehlo()
2642 
 
12219 kshitij.so 2643 
    #recipients = ['kshitij.sood@saholic.com']
2644 
    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']
11775 kshitij.so 2645 
    msg = MIMEMultipart()
2646 
    msg['Subject'] = "Flipkart Negative Margin" + ' - ' + str(datetime.now())
2647 
    msg['From'] = ""
2648 
    msg['To'] = ",".join(recipients)
2649 
    msg.preamble = "Flipkart Negative Margin" + ' - ' + str(datetime.now())
2650 
    html_msg = MIMEText(message, 'html')
2651 
    msg.attach(html_msg)
2652 
    try:
2653 
        mailServer.login("build@shop2020.in", "cafe@nes")
2654 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
2655 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
2656 
    except Exception as e:
2657 
        print e
2658 
        print "Unable to send Flipkart Negative margin mail.Lets try local SMTP"
2659 
        smtpServer = smtplib.SMTP('localhost')
2660 
        smtpServer.set_debuglevel(1)
11779 kshitij.so 2661 
        sender = 'build@shop2020.in'
11775 kshitij.so 2662 
        try:
2663 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2664 
            print "Successfully sent email"
2665 
        except:
2666 
            print "Error: unable to send email."
12317 kshitij.so 2667 
 
2668 
def sendAlertForCompetitiveNoInventory(timestamp):
2669 
    xstr = lambda s: s or ""
2670 
    competitiveNoInv = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE_NO_INVENTORY).all()
2671 
    if len(competitiveNoInv) == 0:
2672 
        return
2673 
    message="""<html>
2674 
            <body>
2675 
            <h3 style="color:red;font-weight:bold;">Flipkart Competitive But No Inventory</h3>
2676 
            <table border="1" style="width:100%;">
2677 
            <thead>
2678 
            <tr><th>Item Id</th>
2679 
            <th>Product Name</th>
2680 
            <th>SP</th>
2681 
            <th>TP</th>
2682 
            <th>Lowest Possible SP</th>
2683 
            <th>Lowest Possible TP</th>
2684 
            <th>Lowest Seller</th>
2685 
            <th>Lowest Seller SP</th>
2686 
            <th>Margin</th>
2687 
            <th>Margin %</th>
2688 
            <th>Commission %</th>
2689 
            <th>Return Provision %</th>
2690 
            <th>Flipkart Inventory</th>
2691 
            <th>Total Inventory</th>
2692 
            <th>Sales History</th>
2693 
            </tr></thead>
2694 
            <tbody>"""
2695 
    for item in competitiveNoInv:
2696 
        mpHistory = item[0]
2697 
        catItem = item[1]
2698 
        netInventory=''
2699 
        if not inventoryMap.has_key(mpHistory.item_id):
2700 
            netInventory='Info Not Available'
2701 
        else:
2702 
            netInventory = str(getNetAvailability(inventoryMap.get(mpHistory.item_id)))
2703 
        mpItem = MarketplaceItems.get_by(itemId=mpHistory.item_id,source=OrderSource.FLIPKART)
2704 
        message+="""<tr>
2705 
            <td style="text-align:center">"""+str(mpHistory.item_id)+"""</td>
2706 
            <td style="text-align:center">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
2707 
            <td style="text-align:center">"""+str(mpHistory.ourSellingPrice)+"""</td>
2708 
            <td style="text-align:center">"""+str(mpHistory.ourTp)+"""</td>
2709 
            <td style="text-align:center">"""+str(mpHistory.lowestPossibleSp)+"""</td>
2710 
            <td style="text-align:center">"""+str(mpHistory.lowestPossibleTp)+"""</td>
2711 
            <td style="text-align:center">"""+str(mpHistory.lowestSellerName)+"""</td>
2712 
            <td style="text-align:center">"""+str(mpHistory.lowestSellingPrice)+"""</td>
2713 
            <td style="text-align:center">"""+str(mpHistory.margin)+"""</td>
2714 
            <td style="text-align:center">"""+str(round((mpHistory.margin/mpHistory.ourSellingPrice)*100,1))+" %"+"""</td>
2715 
            <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
2716 
            <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
2717 
            <td style="text-align:center">"""+str(mpHistory.ourInventory)+"""</td>
2718 
            <td style="text-align:center">"""+netInventory+"""</td>
2719 
            <td style="text-align:center">"""+getOosString((itemSaleMap.get(mpHistory.item_id))[1])+"""</td>
2720 
            </tr>"""
2721 
    message+="""</tbody></table></body></html>"""
2722 
    print message
2723 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
2724 
    mailServer.ehlo()
2725 
    mailServer.starttls()
2726 
    mailServer.ehlo()
2727 
 
2728 
    #recipients = ['kshitij.sood@saholic.com']
2729 
    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']
2730 
    msg = MIMEMultipart()
2731 
    msg['Subject'] = "Flipkart Competitive But No Inventory" + ' - ' + str(datetime.now())
2732 
    msg['From'] = ""
2733 
    msg['To'] = ",".join(recipients)
2734 
    msg.preamble = "Flipkart Competitive But No Inventory" + ' - ' + str(datetime.now())
2735 
    html_msg = MIMEText(message, 'html')
2736 
    msg.attach(html_msg)
2737 
    try:
2738 
        mailServer.login("build@shop2020.in", "cafe@nes")
2739 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
2740 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
2741 
    except Exception as e:
2742 
        print e
2743 
        print "Unable to send Flipkart Competitive But No Inventory mail.Lets try local SMTP"
2744 
        smtpServer = smtplib.SMTP('localhost')
2745 
        smtpServer.set_debuglevel(1)
2746 
        sender = 'build@shop2020.in'
2747 
        try:
2748 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2749 
            print "Successfully sent email"
2750 
        except:
2751 
            print "Error: unable to send email."
2752 
 
2753 
def sendAlertForInactiveAutoPricing(timestamp):
2754 
    xstr = lambda s: s or ""
2755 
    inactiveAutoPricing = session.query(MarketPlaceHistory,Item,MarketplaceItems).join((Item,MarketPlaceHistory.item_id==Item.id)).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(or_(MarketplaceItems.autoDecrement==0,MarketplaceItems.autoIncrement==0)).filter(MarketPlaceHistory.competitiveCategory.in_([CompetitionCategory.BUY_BOX,CompetitionCategory.COMPETITIVE,CompetitionCategory.PREF_BUT_NOT_CHEAP])).all()
2756 
    if len(inactiveAutoPricing) == 0:
2757 
        return
2758 
    message="""<html>
2759 
            <body>
12319 kshitij.so 2760 
            <h3 style="color:red;font-weight:bold;">Flipkart Inactive Auto Pricing</h3>
12317 kshitij.so 2761 
            <table border="1" style="width:100%;">
2762 
            <thead>
2763 
            <tr><th>Item Id</th>
2764 
            <th>Product Name</th>
2765 
            <th>Selling Price</th>
2766 
            <th>Competitive Category</th>
2767 
            <th>Margin</th>
2768 
            <th>Margin %</th>
2769 
            <th>Commission %</th>
2770 
            <th>Return Provision %</th>
2771 
            <th>Flipkart Inventory</th>
2772 
            <th>Total Inventory</th>
2773 
            <th>Sales History</th>
2774 
            </tr></thead>
2775 
            <tbody>"""
2776 
    for item in inactiveAutoPricing:
2777 
        mpHistory = item[0]
2778 
        catItem = item[1]
2779 
        mpItem = item[2]
2780 
        netInventory=''
2781 
        if not inventoryMap.has_key(mpHistory.item_id):
2782 
            netInventory='Info Not Available'
2783 
        else:
2784 
            netInventory = str(getNetAvailability(inventoryMap.get(mpHistory.item_id)))
2785 
        mpItem = MarketplaceItems.get_by(itemId=mpHistory.item_id,source=OrderSource.FLIPKART)
2786 
        message+="""<tr>
2787 
            <td style="text-align:center">"""+str(mpHistory.item_id)+"""</td>
2788 
            <td style="text-align:center">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
2789 
            <td style="text-align:center">"""+str(mpHistory.ourSellingPrice)+"""</td>
2790 
            <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(mpHistory.competitiveCategory))+"""</td>
2791 
            <td style="text-align:center">"""+str(mpHistory.margin)+"""</td>
2792 
            <td style="text-align:center">"""+str(round((mpHistory.margin/mpHistory.ourSellingPrice)*100,1))+" %"+"""</td>
2793 
            <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
2794 
            <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
2795 
            <td style="text-align:center">"""+str(mpHistory.ourInventory)+"""</td>
2796 
            <td style="text-align:center">"""+netInventory+"""</td>
2797 
            <td style="text-align:center">"""+getOosString((itemSaleMap.get(mpHistory.item_id))[1])+"""</td>
2798 
            </tr>"""
2799 
    message+="""</tbody></table></body></html>"""
2800 
    print message
2801 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
2802 
    mailServer.ehlo()
2803 
    mailServer.starttls()
2804 
    mailServer.ehlo()
2805 
 
2806 
    #recipients = ['kshitij.sood@saholic.com']
2807 
    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']
2808 
    msg = MIMEMultipart()
2809 
    msg['Subject'] = "Flipkart Auto Pricing Inactive" + ' - ' + str(datetime.now())
2810 
    msg['From'] = ""
2811 
    msg['To'] = ",".join(recipients)
2812 
    msg.preamble = "Flipkart Auto Pricing Inactive" + ' - ' + str(datetime.now())
2813 
    html_msg = MIMEText(message, 'html')
2814 
    msg.attach(html_msg)
2815 
    try:
2816 
        mailServer.login("build@shop2020.in", "cafe@nes")
2817 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
2818 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
2819 
    except Exception as e:
2820 
        print e
2821 
        print "Unable to send Flipkart Auto Pricing Inactive mail.Lets try local SMTP"
2822 
        smtpServer = smtplib.SMTP('localhost')
2823 
        smtpServer.set_debuglevel(1)
2824 
        sender = 'build@shop2020.in'
2825 
        try:
2826 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2827 
            print "Successfully sent email"
2828 
        except:
2829 
            print "Error: unable to send email."
2830 
 
12322 kshitij.so 2831 
def commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp):
2832 
    catalog_client = CatalogClient().get_client()
2833 
    inventory_client = InventoryClient().get_client()
2834 
    for item in successfulAutoDecrease:
2835 
        it = Item.query.filter_by(id=item.item_id).one()
2836 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
2837 
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
2838 
        warehouse = inventory_client.getWarehouse(fkItem.warehouseId)
2839 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
2840 
        mpItem.currentTp = getNewOurTp(mpItem,item.proposedSellingPrice)
2841 
        mpItem.currentSp = math.ceil(item.proposedSellingPrice)
2842 
        fkItem.commissionValue = round((mpItem.commission/100)*(mpItem.currentSp),2)
2843 
        fkItem.serviceTaxValue = round((mpItem.serviceTax/100)*(fkItem.commissionValue+mpItem.courierCost),2)
2844 
        fkItem.updatedOn = timestamp
2845 
        fkItem.priceUpdatedBy = 'SYSTEM'
12328 kshitij.so 2846 
        mpItem.minimumPossibleTp = getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,mpItem.currentSp)
12322 kshitij.so 2847 
        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,item.ourNlc,vatRate)
2848 
        addHistory(fkItem,mpItem)
2849 
        markStatusForMarketplaceItems(fkItem,mpItem)
2850 
    session.commit()
2851 
    for item in successfulAutoIncrease:
2852 
        it = Item.query.filter_by(id=item.item_id).one()
2853 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
2854 
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
2855 
        addHistory(fkItem,mpItem)
2856 
        warehouse = inventory_client.getWarehouse(fkItem.warehouseId)
12326 kshitij.so 2857 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
12322 kshitij.so 2858 
        mpItem.currentTp = getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice))
2859 
        mpItem.currentSp = math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice))
2860 
        fkItem.commissionValue = round((mpItem.commission/100)*(mpItem.currentSp),2)
2861 
        fkItem.serviceTaxValue = round((mpItem.serviceTax/100)*(fkItem.commissionValue+mpItem.courierCost),2)
2862 
        fkItem.updatedOn = timestamp
2863 
        fkItem.priceUpdatedBy = 'SYSTEM'
2864 
        mpItem.minimumPossibleTp = getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,mpItem.currentSp)
2865 
        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,item.ourNlc,vatRate)
2866 
        addHistory(fkItem,mpItem)
2867 
        markStatusForMarketplaceItems(fkItem,mpItem)
2868 
    session.commit()
2869 
 
2870 
def addHistory(fkItem,mpItem):
2871 
    itemHistory = MarketPlaceUpdateHistory()
2872 
    itemHistory.item_id = fkItem.item_id
2873 
    itemHistory.source = OrderSource.FLIPKART
2874 
    itemHistory.exceptionPrice = fkItem.exceptionPrice
2875 
    itemHistory.warehouseId = fkItem.warehouseId
2876 
    itemHistory.isListedOnSource = fkItem.isListedOnFlipkart
2877 
    itemHistory.transferPrice = mpItem.currentTp
2878 
    itemHistory.sellingPrice = mpItem.currentSp
2879 
    itemHistory.courierCost = mpItem.courierCost
2880 
    itemHistory.commission = fkItem.commissionValue
2881 
    itemHistory.serviceTax = fkItem.serviceTaxValue
2882 
    itemHistory.suppressPriceFeed = fkItem.suppressPriceFeed
2883 
    itemHistory.suppressInventoryFeed = fkItem.suppressInventoryFeed
2884 
    itemHistory.updatedOn = fkItem.updatedOn
2885 
    itemHistory.maxNlc = fkItem.maxNlc
2886 
    itemHistory.skuAtSource = fkItem.skuAtFlipkart
2887 
    itemHistory.marketPlaceSerialNumber = fkItem.flipkartSerialNumber
2888 
    itemHistory.priceUpdatedBy = fkItem.updatedBy
2889 
    itemHistory.courierCostMarketplace = mpItem.courierCostMarketplace
12317 kshitij.so 2890 
 
12322 kshitij.so 2891 
def markStatusForMarketplaceItems(fkItem,mpItem):
2892 
    markUpdatedItem = MarketPlaceItemPrice.query.filter(MarketPlaceItemPrice.item_id==fkItem.item_id).filter(MarketPlaceItemPrice.source==mpItem.source).first()
2893 
    if markUpdatedItem is None:
2894 
        marketPlaceItemPrice = MarketPlaceItemPrice()
2895 
        marketPlaceItemPrice.item_id = fkItem.item_id
2896 
        marketPlaceItemPrice.source = mpItem.source
2897 
        marketPlaceItemPrice.lastUpdatedOn = fkItem.updatedOn
2898 
        marketPlaceItemPrice.sellingPrice = mpItem.currentSp
2899 
        marketPlaceItemPrice.suppressPriceFeed = fkItem.suppressPriceFeed
2900 
        marketPlaceItemPrice.isListedOnSource = fkItem.isListedOnFlipkart
2901 
    else:
2902 
        if (markUpdatedItem.sellingPrice!=mpItem.currentSp or markUpdatedItem.suppressPriceFeed!=fkItem.suppressPriceFeed or markUpdatedItem.isListedOnSource!=fkItem.isListedOnFlipkart):
2903 
            markUpdatedItem.lastUpdatedOn = fkItem.updatedOn
2904 
        markUpdatedItem.sellingPrice = mpItem.currentSp
2905 
        markUpdatedItem.suppressPriceFeed = fkItem.suppressPriceFeed
2906 
        markUpdatedItem.isListedOnSource = fkItem.isListedOnFlipkart
2907 
 
11560 kshitij.so 2908 
 
12322 kshitij.so 2909 
 
2910 
def updatePriceOnFlipkart(successfulAutoDecrease,successfulAutoIncrease):
2911 
    if syncPrice=='false':
2912 
        return
2913 
    url = 'http://support.shop2020.in:8080/Support/reports'
2914 
    br = getBrowserObject()
2915 
    br.open(url)
2916 
    br.select_form(nr=0)
2917 
    br.form['username'] = "manoj"
2918 
    br.form['password'] = "man0j"
2919 
    br.submit()
2920 
    for item in successfulAutoDecrease:
2921 
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
2922 
        sellingPrice =  str(math.ceil(item.proposedSellingPrice))
2923 
        flipkartSku = fkItem.skuAtFlipkart
2924 
        if fkItem.isListedOnFlipkart:
2925 
            updateUrl = 'http://support.shop2020.in:8080/Support/flipkart-list!updateForAutoPricing?sellingPrice=%s&fkItemCode=%s&itemId=%s'%(sellingPrice,flipkartSku,str(item.item_id))
2926 
            br.open(updateUrl)
2927 
    for item in successfulAutoIncrease:
2928 
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
2929 
        sellingPrice =  str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
2930 
        flipkartSku = fkItem.skuAtFlipkart
2931 
        if fkItem.isListedOnFlipkart:
2932 
            updateUrl = 'http://support.shop2020.in:8080/Support/flipkart-list!updateForAutoPricing?sellingPrice=%s&fkItemCode=%s&itemId=%s'%(sellingPrice,flipkartSku,str(item.item_id))
2933 
            br.open(updateUrl)
11623 kshitij.so 2934 
 
11193 kshitij.so 2935 
def main():
2936 
    parser = optparse.OptionParser()
2937 
    parser.add_option("-t", "--type", dest="runType",
2938 
                   default="FULL", type="string",
2939 
                   help="Run type FULL or FAVOURITE")
2940 
    (options, args) = parser.parse_args()
2941 
    if options.runType not in ('FULL','FAVOURITE'):
2942 
        print "Run type argument illegal."
2943 
        sys.exit(1)
2944 
    timestamp = datetime.now()
11581 kshitij.so 2945 
    previousProcessingTimestamp = session.query(func.max(MarketPlaceHistory.timestamp)).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).one()
11193 kshitij.so 2946 
    itemInfo= populateStuff(options.runType,timestamp)
11560 kshitij.so 2947 
    itemsPopulated = 0
11615 kshitij.so 2948 
    while (len(itemInfo)>0):
12213 kshitij.so 2949 
        itemsPopulated = threadsToSpawn(options.runType,itemInfo,itemsPopulated)
11581 kshitij.so 2950 
        cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap = decideCategory(itemInfo[0:itemsPopulated])
2951 
        itemInfo[0:itemsPopulated] = []
2952 
        commitExceptionList(exceptionItems,timestamp)
2953 
        commitCantCompete(cantCompete,timestamp)
2954 
        commitBuyBox(buyBoxItems,timestamp)
2955 
        commitCompetitive(competitive,timestamp)
2956 
        commitCompetitiveNoInventory(competitiveNoInventory,timestamp)
2957 
        commitNegativeMargin(negativeMargin,timestamp)
2958 
        commitCheapButNotPref(cheapButNotPref,timestamp)
2959 
        commitPrefButNotCheap(prefButNotCheap, timestamp)
2960 
        cantCompete[:], buyBoxItems[:], competitive[:], competitiveNoInventory[:], exceptionItems[:], negativeMargin[:], cheapButNotPref[:], prefButNotCheap[:] =[],[],[],[],[],[],[],[]
12218 kshitij.so 2961 
        collected = gc.collect()
2962 
        print "Garbage collector: collected %d objects." % (collected)
11581 kshitij.so 2963 
 
11615 kshitij.so 2964 
    successfulAutoDecrease = fetchItemsForAutoDecrease(timestamp)
2965 
    successfulAutoIncrease = fetchItemsForAutoIncrease(timestamp)
2966 
    if options.runType=='FULL':
2967 
        previousAutoFav, nowAutoFav = markAutoFavourite()
11621 kshitij.so 2968 
    if options.runType =='FULL':
2969 
        write_report(previousAutoFav,nowAutoFav,timestamp,options.runType)
2970 
    else:
2971 
        write_report(None,None,timestamp,options.runType)
11623 kshitij.so 2972 
    if options.runType=='FULL':
2973 
        cheapButNotPrefAlert(timestamp)
11754 kshitij.so 2974 
        sendPricingMismatch(timestamp)
11775 kshitij.so 2975 
        sendAlertForNegativeMargins(timestamp)
12317 kshitij.so 2976 
        sendAlertForCompetitiveNoInventory(timestamp)
2977 
        sendAlertForInactiveAutoPricing(timestamp)
12328 kshitij.so 2978 
    commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp)
2979 
    sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease)
2980 
    updatePriceOnFlipkart(successfulAutoDecrease,successfulAutoIncrease)
2981 
    if previousProcessingTimestamp[0] is not None:
2982 
        processLostBuyBoxItems(previousProcessingTimestamp[0],timestamp)
11193 kshitij.so 2983 
 
2984 
if __name__ == '__main__':
2985 
    main()