Subversion Repositories SmartDukaan

Rev

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