Subversion Repositories SmartDukaan

Rev

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

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