Subversion Repositories SmartDukaan

Rev

Rev 12208 | Rev 12210 | 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 
 
12209 kshitij.so 709 
    response_data=response_data
710 
    page=response_data.decode("utf-8")
12208 kshitij.so 711 
    print datetime.now()
712 
    soup = BeautifulSoup(page,convertEntities=BeautifulSoup.HTML_ENTITIES)
713 
    page = None
12209 kshitij.so 714 
    response_data = None
12208 kshitij.so 715 
    print datetime.now()
716 
    print "Soup created from flipkart data for %s" %(url)
717 
    info = []
718 
    oddSeller = soup.findAll("div" , {"class" : "line seller-item odd "})
719 
    for data in oddSeller:
720 
        temp={}
721 
        try:
722 
            businessDays = data.find('span', attrs={'class' : re.compile('fk-deliverable.*')})
723 
            shippingTime = businessDays.find('span', attrs={'class' : re.compile('fk-bold')}).string.replace('to','').replace('business days.','').strip().replace('  ','-')
724 
            temp['shippingTime']=shippingTime
725 
        except:
726 
            pass
727 
        price = data.find('span', attrs={'class' : re.compile('pxs-final-price.*')}).string.strip('Rs.').strip()
728 
        temp['sellingPrice']=float(price)
729 
        for sellerInfo in data.findAll("div",{"class":re.compile(".*seller-info*")}):
730 
            sellerName = sellerInfo.find('a').string
731 
            temp['sellerName'] = sellerName
732 
        for metrics in data.find("div",{"class":"fk-text-right"}):
733 
            try:
734 
                metric = metrics.findAll('input', {'type': 'submit'})
735 
            except AttributeError:
736 
                continue
737 
            try:
738 
                inputTags = metric[0]['data-lst-buytrend']
739 
            except TypeError:
740 
                continue
741 
            dataMetrics = metric[0]['data-listing-metrics']
742 
            try:
743 
                buyTrend = inputTags[0:str(inputTags).index('NWSR')].replace('_','')
744 
            except ValueError:
745 
                buyTrend = inputTags[0:str(inputTags).index('WSR')].replace('_','')
746 
            temp['buyTrend']=buyTrend
747 
            dataMetric = dataMetrics.split(';')
748 
            sellerCode = dataMetric[0]
749 
            temp['sellerCode']=sellerCode
750 
            temp['sellingPriceMetric'] = float(dataMetric[1])
751 
            if not temp.has_key('shippingTime'):
752 
                print "Populating shipping time from metrics"
753 
                temp['shippingTime'] = dataMetric[3]
754 
            temp['sellerScore'] = int(dataMetric[4])
755 
            info.append(temp)
756 
    evenSeller = soup.findAll("div" , {"class" : "line seller-item even "})
757 
    for data in evenSeller:
758 
        temp={}
759 
        price = data.find('span', attrs={'class' : re.compile('pxs-final-price.*')}).string.strip('Rs.')
760 
        try:
761 
            businessDays = data.find('span', attrs={'class' : re.compile('fk-deliverable.*')})
762 
            shippingTime = businessDays.find('span', attrs={'class' : re.compile('fk-bold')}).string.replace('to','').replace('business days.','').strip().replace('  ','-')
763 
            temp['shippingTime']=shippingTime
764 
        except:
765 
            pass
766 
        temp['sellingPrice']=float(price)
767 
        for sellerInfo in data.findAll("div",{"class":re.compile(".*seller-info*")}):
768 
            sellerName = sellerInfo.find('a').string
769 
            temp['sellerName'] = sellerName
770 
        for metrics in data.find("div",{"class":"fk-text-right"}):
771 
            try:
772 
                metric = metrics.findAll('input', {'type': 'submit'})
773 
            except AttributeError:
774 
                continue
775 
            try:
776 
                inputTags = metric[0]['data-lst-buytrend']
777 
            except TypeError:
778 
                continue
779 
            dataMetrics = metric[0]['data-listing-metrics']
780 
            try:
781 
                buyTrend = inputTags[0:str(inputTags).index('NWSR')].replace('_','')
782 
            except ValueError:
783 
                buyTrend = inputTags[0:str(inputTags).index('WSR')].replace('_','')
784 
            temp['buyTrend']=buyTrend
785 
            dataMetric = dataMetrics.split(';')
786 
            temp['sellerCode'] = dataMetric[0]
787 
            temp['sellingPriceMetric'] = float(dataMetric[1])
788 
            if not temp.has_key('shippingTime'):
789 
                print "Populating shipping time from metrics"
790 
                temp['shippingTime'] = dataMetric[3]
791 
            temp['sellerScore'] = int(dataMetric[4])
792 
            info.append(temp)
793 
    vendorsData = info
794 
    info = []
795 
    print "Returning Json response from flipkart for %s" %(url)
12201 kshitij.so 796 
    fin = datetime.now()
797 
    print "Finish with data for serial Number %s %s" %(flipkartSerialNumber,str(fin))
11193 kshitij.so 798 
    sortedVendorsData = sorted(vendorsData, key=itemgetter('sellingPrice'))
11968 kshitij.so 799 
    vendorsData[:]=[]
11193 kshitij.so 800 
    rank ,ourSp, iterator, secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, shippingTimeLowerLimitLowestSeller,shippingTimeUpperLimitLowestSeller, \
801 
    shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller, shippingTimeLowerLimitOur, shippingTimeUpperLimitOur, shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller, totalAvailableSeller= (0,)*19
802 
    lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
803 
    ourBuyTrend, prefSellerBuyTrend, secondLowestSellerBuyTrend, ourCode = ('',)*11
804 
    for data in sortedVendorsData:
805 
        if iterator == 0:
806 
            lowestSellerName = data['sellerName']
807 
            lowestSellerScore = data['sellerScore']
808 
            lowestSellerCode = data['sellerCode']
809 
            lowestSellerSp = data['sellingPrice']
810 
            lowestSellerBuyTrend = data['buyTrend']
811 
            try:
812 
                shippingTimeLowerLimitLowestSeller, shippingTimeUpperLimitLowestSeller = data['shippingTime'].split('-')
813 
            except ValueError:
814 
                shippingTimeLowerLimitLowestSeller = int(data['shippingTime'])
815 
 
816 
        if iterator ==1:
817 
            secondLowestSellerName = data['sellerName']
818 
            secondLowestSellerScore = data['sellerScore']
819 
            secondLowestSellerCode = data['sellerCode']
820 
            secondLowestSellerSp = data['sellingPrice']
821 
            secondLowestSellerBuyTrend = data['buyTrend']
822 
            try:
823 
                shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller = data['shippingTime'].split('-')
824 
            except ValueError:
825 
                shippingTimeLowerLimitSecondLowestSeller = int(data['shippingTime'])
826 
 
827 
        if data['sellerName'] == 'Saholic':
828 
            ourScore = data['sellerScore']
829 
            ourCode = data['sellerCode']
830 
            ourSp = data['sellingPrice']
831 
            ourBuyTrend = data['buyTrend']
832 
            try:
833 
                shippingTimeLowerLimitOur, shippingTimeUpperLimitOur = data['shippingTime'].split('-')
834 
            except ValueError:
835 
                shippingTimeLowerLimitOur = int(data['shippingTime'])
836 
            rank = iterator + 1
837 
 
838 
        if data['buyTrend'] in ('PrefCheap','PrefNCheap',''):
839 
            prefSellerName = data['sellerName']
840 
            prefSellerScore = data['sellerScore']
841 
            prefSellerCode = data['sellerCode']
842 
            prefSellerSp = data['sellingPrice']
843 
            prefSellerBuyTrend = data['buyTrend']
844 
            try:
845 
                shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller = data['shippingTime'].split('-')
846 
            except ValueError:
847 
                shippingTimeLowerLimitPrefSeller = int(data['shippingTime'])
848 
 
849 
        iterator+=1
11774 kshitij.so 850 
 
11193 kshitij.so 851 
    flipkartDetails = __FlipkartDetails(rank ,ourSp , secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, int(shippingTimeLowerLimitLowestSeller),int(shippingTimeUpperLimitLowestSeller), \
852 
    int(shippingTimeLowerLimitPrefSeller), int(shippingTimeUpperLimitPrefSeller), int(shippingTimeLowerLimitOur), int(shippingTimeUpperLimitOur), int(shippingTimeLowerLimitSecondLowestSeller), int(shippingTimeUpperLimitSecondLowestSeller), len(sortedVendorsData), lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
853 
    ourBuyTrend, prefSellerBuyTrend, secondLowestSellerBuyTrend, ourCode)
11774 kshitij.so 854 
 
855 
    if flipkartDetails.ourBuyTrend == 'PrefCheap'and flipkartDetails.rank!=1 and flipkartDetails.ourSp==flipkartDetails.secondLowestSellerSp:
11775 kshitij.so 856 
        print "Under PrefCheap category.Switching data for ",flipkartSerialNumber
11774 kshitij.so 857 
        flipkartDetails.lowestSellerSp, flipkartDetails.secondLowestSellerSp = flipkartDetails.secondLowestSellerSp,flipkartDetails.lowestSellerSp
858 
        flipkartDetails.lowestSellerScore, flipkartDetails.secondLowestSellerScore = flipkartDetails.secondLowestSellerScore,flipkartDetails.lowestSellerScore
859 
        flipkartDetails.shippingTimeLowerLimitLowestSeller, flipkartDetails.shippingTimeLowerLimitSecondLowestSeller = flipkartDetails.shippingTimeLowerLimitSecondLowestSeller,flipkartDetails.shippingTimeLowerLimitLowestSeller
860 
        flipkartDetails.shippingTimeUpperLimitLowestSeller, flipkartDetails.shippingTimeUpperLimitSecondLowestSeller = flipkartDetails.shippingTimeUpperLimitSecondLowestSeller,flipkartDetails.shippingTimeUpperLimitLowestSeller
861 
        flipkartDetails.lowestSellerName, flipkartDetails.secondLowestSellerName = flipkartDetails.secondLowestSellerName,flipkartDetails.lowestSellerName
862 
        flipkartDetails.lowestSellerCode, flipkartDetails.secondLowestSellerCode = flipkartDetails.secondLowestSellerCode,flipkartDetails.lowestSellerCode
863 
        flipkartDetails.lowestSellerBuyTrend, flipkartDetails.secondLowestSellerBuyTrend = flipkartDetails.secondLowestSellerBuyTrend,flipkartDetails.lowestSellerBuyTrend
864 
        flipkartDetails.rank=1
865 
 
866 
    if flipkartDetails.ourBuyTrend == 'NPrefCheap'and flipkartDetails.rank!=1 and flipkartDetails.ourSp==flipkartDetails.lowestSellerSp:
11775 kshitij.so 867 
        print "Under NPrefCheap category.Switching data for ",flipkartSerialNumber
11774 kshitij.so 868 
        flipkartDetails.lowestSellerSp = flipkartDetails.ourSp
869 
        flipkartDetails.lowestSellerScore = flipkartDetails.ourScore
870 
        flipkartDetails.shippingTimeLowerLimitLowestSeller = flipkartDetails.shippingTimeLowerLimitOur
871 
        flipkartDetails.shippingTimeUpperLimitLowestSeller = flipkartDetails.shippingTimeUpperLimitOur
872 
        flipkartDetails.lowestSellerName = 'Saholic'
873 
        flipkartDetails.lowestSellerCode = flipkartDetails.ourCode
874 
        flipkartDetails.lowestSellerBuyTrend = flipkartDetails.ourBuyTrend
875 
 
11193 kshitij.so 876 
    return flipkartDetails
877 
 
878 
def calculateAverageSale(oosStatus):
879 
    count,sale = 0,0
880 
    for obj in oosStatus:
881 
        if not obj.is_oos:
882 
            count+=1
883 
            sale = sale+obj.num_orders
884 
    avgSalePerDay=0 if count==0 else (float(sale)/count)
885 
    return round(avgSalePerDay,2)
886 
 
887 
def calculateTotalSale(oosStatus):
888 
    sale = 0
889 
    for obj in oosStatus:
890 
        if not obj.is_oos:
891 
            sale = sale+obj.num_orders
892 
    return sale
893 
 
894 
def getNetAvailability(itemInventory):
895 
    totalAvailability, totalReserved = 0,0
896 
    availableMap  = itemInventory.availability
897 
    reserveMap = itemInventory.reserved
898 
    for warehouse,availability in availableMap.iteritems():
899 
        if warehouse==16:
900 
            continue
901 
        totalAvailability = totalAvailability+availability
902 
    for warehouse,reserve in reserveMap.iteritems():
903 
        if warehouse==16:
904 
            continue
905 
        totalReserved = totalReserved+reserve
906 
    return totalAvailability - totalReserved
907 
 
908 
def getOosString(oosStatus):
909 
    lastNdaySale=""
910 
    for obj in oosStatus:
911 
        if obj.is_oos:
912 
            lastNdaySale += "X-"
913 
        else:
914 
            lastNdaySale += str(obj.num_orders) + "-"
915 
    return lastNdaySale[:-1]
916 
 
917 
def getLastDaySale(itemId):
918 
    return (itemSaleMap.get(itemId))[4]
919 
 
920 
def getSalesPotential(lowestSellingPrice,ourNlc):
921 
    if lowestSellingPrice - ourNlc < 0:
922 
        return 'HIGH'
923 
    elif (float(lowestSellingPrice - ourNlc))/lowestSellingPrice >=0 and (float(lowestSellingPrice - ourNlc))/lowestSellingPrice <=.02:
924 
        return 'MEDIUM'
925 
    else:
926 
        return 'LOW'
927 
 
11571 kshitij.so 928 
def decideCategory(itemInfo):
11193 kshitij.so 929 
    global itemSaleMap
11560 kshitij.so 930 
 
11571 kshitij.so 931 
    cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap = [],[],[],[],[],[],[],[]
932 
 
11193 kshitij.so 933 
    catalog_client = CatalogClient().get_client()
934 
 
11571 kshitij.so 935 
    for val in itemInfo:
936 
        spm = val.sourcePercentage
937 
        flipkartDetails = val.flipkartDetails
11581 kshitij.so 938 
        if (flipkartDetails is None or flipkartDetails.totalAvailableSeller==0):
11571 kshitij.so 939 
            exceptionItems.append(val)
940 
            continue
941 
 
942 
        mpItem = MarketplaceItems.get_by(itemId=val.item_id,source=OrderSource.FLIPKART)
943 
        if flipkartDetails.rank==0:
944 
            flipkartDetails.ourSp = mpItem.currentSp
945 
            ourSp = mpItem.currentSp
946 
        else:
947 
            ourSp = flipkartDetails.ourSp
11581 kshitij.so 948 
        vatRate = catalog_client.getVatPercentageForItem(val.item_id, val.stateId, flipkartDetails.ourSp)
11571 kshitij.so 949 
        val.vatRate = vatRate
950 
        if (flipkartDetails.ourBuyTrend == 'PrefCheap') or (flipkartDetails.rank==1 and flipkartDetails.totalAvailableSeller==1):
951 
            temp=[]
952 
            temp.append(flipkartDetails)
953 
            temp.append(val)
954 
            secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
955 
            prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
956 
            flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
957 
            temp.append(flipkartPricing)
958 
            temp.append(mpItem)
959 
            buyBoxItems.append(temp)
960 
            continue
961 
 
962 
        if (flipkartDetails.ourBuyTrend == 'PrefNCheap'):
963 
            temp=[]
964 
            temp.append(flipkartDetails)
965 
            temp.append(val)
11618 kshitij.so 966 
            secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getSecondLowestSellerTp(flipkartDetails,val,spm,False)
11615 kshitij.so 967 
            lowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
11571 kshitij.so 968 
            prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
969 
            flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
970 
            temp.append(flipkartPricing)
971 
            temp.append(mpItem)
972 
            prefButNotCheap.append(temp)
973 
            continue
974 
 
975 
        if (flipkartDetails.ourBuyTrend == 'NPrefCheap') and (flipkartDetails.rank==1):
976 
            temp=[]
977 
            temp.append(flipkartDetails)
978 
            temp.append(val)
11615 kshitij.so 979 
            secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
11571 kshitij.so 980 
            prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)
11615 kshitij.so 981 
            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 982 
            temp.append(flipkartPricing)
983 
            temp.append(mpItem)
984 
            cheapButNotPref.append(temp)
985 
            continue
986 
 
987 
        lowestTp = getOtherTp(flipkartDetails,val,spm,False)
988 
        ourTp = getOurTp(flipkartDetails,val,spm,mpItem)
989 
        lowestPossibleTp = getLowestPossibleTp(flipkartDetails,val,spm,mpItem)
990 
        lowestPossibleSp = getLowestPossibleSp(flipkartDetails,val,spm,mpItem)
991 
        prefSellerTp = getOtherTp(flipkartDetails,val,spm,True)
992 
 
993 
        if (ourTp<lowestPossibleTp):
994 
            temp=[]
995 
            temp.append(flipkartDetails)
996 
            temp.append(val)
12156 kshitij.so 997 
            flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),None)
11571 kshitij.so 998 
            temp.append(flipkartPricing)
999 
            negativeMargin.append(temp)
1000 
            continue
1001 
 
1002 
        if (flipkartDetails.lowestSellerSp > lowestPossibleSp) and val.ourFlipkartInventory!=0:
1003 
            type(val.ourFlipkartInventory)
1004 
            temp=[]
1005 
            temp.append(flipkartDetails)
1006 
            temp.append(val)
1007 
            flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,lowestPossibleSp,prefSellerTp)
1008 
            temp.append(flipkartPricing)
1009 
            temp.append(mpItem)
1010 
            competitive.append(temp)
1011 
            continue
1012 
 
1013 
        if (flipkartDetails.lowestSellerSp) > lowestPossibleSp and val.ourFlipkartInventory==0:
1014 
            temp=[]
1015 
            temp.append(flipkartDetails)
1016 
            temp.append(val)
1017 
            flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,lowestPossibleSp,prefSellerTp)
1018 
            temp.append(flipkartPricing)
1019 
            temp.append(mpItem)
1020 
            competitiveNoInventory.append(temp)
1021 
            continue
1022 
 
11193 kshitij.so 1023 
        temp=[]
1024 
        temp.append(flipkartDetails)
1025 
        temp.append(val)
1026 
        flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,lowestPossibleSp,prefSellerTp)
1027 
        temp.append(flipkartPricing)
1028 
        temp.append(mpItem)
11571 kshitij.so 1029 
        cantCompete.append(temp)
11193 kshitij.so 1030 
 
11969 kshitij.so 1031 
    itemInfo[:]=[]
11571 kshitij.so 1032 
    return cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap
11193 kshitij.so 1033 
 
1034 
def getOtherTp(flipkartDetails,val,spm,prefferedSeller):
1035 
    if val.parent_category==10011 or val.parent_category==12001:
1036 
        commissionPercentage = spm.competitorCommissionAccessory
1037 
    else:
1038 
        commissionPercentage = spm.competitorCommissionOther
1039 
    if flipkartDetails.rank==1 and not prefferedSeller:
1040 
        otherTp = flipkartDetails.secondLowestSellerSp- flipkartDetails.secondLowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
1041 
        return round(otherTp,2)
1042 
    if prefferedSeller:
1043 
        otherTp = flipkartDetails.prefSellerSp- flipkartDetails.prefSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
1044 
        return round(otherTp,2)
1045 
    otherTp = flipkartDetails.lowestSellerSp- flipkartDetails.lowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
1046 
    return round(otherTp,2)
11618 kshitij.so 1047 
 
1048 
def getSecondLowestSellerTp(flipkartDetails,val,spm,prefferedSeller):
1049 
    if val.parent_category==10011 or val.parent_category==12001:
1050 
        commissionPercentage = spm.competitorCommissionAccessory
1051 
    else:
1052 
        commissionPercentage = spm.competitorCommissionOther
1053 
    otherTp = flipkartDetails.secondLowestSellerSp- flipkartDetails.secondLowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
1054 
    return round(otherTp,2)
11193 kshitij.so 1055 
 
1056 
def getLowestPossibleTp(flipkartDetails,val,spm,mpItem):
1057 
    if flipkartDetails.rank==0:
1058 
        return mpItem.minimumPossibleTp
1059 
    vat = (flipkartDetails.ourSp/(1+(val.vatRate/100))-(val.nlc/(1+(val.vatRate/100))))*(val.vatRate/100);
1060 
    inHouseCost = 15+vat+(mpItem.returnProvision/100)*flipkartDetails.ourSp+mpItem.otherCost;
1061 
    lowest_possible_tp = val.nlc+inHouseCost;
1062 
    return round(lowest_possible_tp,2)
1063 
 
1064 
def getOurTp(flipkartDetails,val,spm,mpItem):
1065 
    if flipkartDetails.rank==0:
1066 
        return mpItem.currentTp
1067 
    ourTp = flipkartDetails.ourSp- flipkartDetails.ourSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))
1068 
    return round(ourTp,2)
1069 
 
1070 
def getLowestPossibleSp(flipkartDetails,val,spm,mpItem):
1071 
    if flipkartDetails.rank==0:
1072 
        return mpItem.minimumPossibleSp
1073 
    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));
1074 
    return round(lowestPossibleSp,2)
1075 
 
1076 
def getTargetTp(targetSp,mpItem):
1077 
    targetTp = targetSp- targetSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))
1078 
    return round(targetTp,2)
1079 
 
1080 
def getTargetSp(targetTp,mpItem,ourSp):
1081 
    targetSp = float(targetTp+(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100)))/(1-((mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))))
1082 
    return round(targetSp,2)
1083 
 
11623 kshitij.so 1084 
def getNewLowestPossibleTp(mpItem,nlc,vatRate,proposedSellingPrice):
1085 
    vat = (proposedSellingPrice/(1+(vatRate/100))-(nlc/(1+(vatRate/100))))*(vatRate/100);
1086 
    inHouseCost = 15+vat+(mpItem.returnProvision/100)*proposedSellingPrice+mpItem.otherCost;
1087 
    lowest_possible_tp = nlc+inHouseCost;
1088 
    return round(lowest_possible_tp,2)
1089 
 
1090 
def getNewOurTp(mpItem,proposedSellingPrice):
11624 kshitij.so 1091 
    ourTp = proposedSellingPrice- proposedSellingPrice*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))
11623 kshitij.so 1092 
    return round(ourTp,2)
1093 
 
1094 
def getNewLowestPossibleSp(mpItem,nlc,vatRate):
11624 kshitij.so 1095 
    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 1096 
    return round(lowestPossibleSp,2)
1097 
 
1098 
 
11193 kshitij.so 1099 
def markAutoFavourite():
1100 
    previouslyAutoFav = []
1101 
    nowAutoFav = []
1102 
    marketplaceItems = session.query(MarketplaceItems).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
1103 
    fromDate = datetime.now()-timedelta(days = 3, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
1104 
    toDate = datetime.now()-timedelta(days = 0, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
11615 kshitij.so 1105 
    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 1106 
    toUpdate = [key for key, value in itemSaleMap.items() if value[5] >= 1]
1107 
    buyBoxLast3days = []
1108 
    for item in items:
1109 
        buyBoxLast3days.append(item[0])
1110 
    for marketplaceItem in marketplaceItems:
1111 
        reason = ""
1112 
        toMark = False
1113 
        if marketplaceItem.itemId in toUpdate:
1114 
            toMark = True
1115 
            reason+="Total sale is greater than 1 for last five days (Flipkart)."
1116 
        if marketplaceItem.itemId in buyBoxLast3days:
1117 
            toMark = True
1118 
            reason+="Item is present in buy box in last 3 days"
1119 
        if not marketplaceItem.autoFavourite:
1120 
            print "Item is not under auto favourite"
1121 
        if toMark:
1122 
            temp=[]
1123 
            temp.append(marketplaceItem.itemId)
1124 
            temp.append(reason)
1125 
            nowAutoFav.append(temp)
1126 
        if (not toMark) and marketplaceItem.autoFavourite:
1127 
            previouslyAutoFav.append(marketplaceItem.itemId)
1128 
        marketplaceItem.autoFavourite = toMark
1129 
    session.commit()
1130 
    return previouslyAutoFav, nowAutoFav
1131 
 
11615 kshitij.so 1132 
def write_report(previousAutoFav, nowAutoFav,timestamp, runType):
11193 kshitij.so 1133 
    wbk = xlwt.Workbook()
1134 
    sheet = wbk.add_sheet('Can\'t Compete')
1135 
    xstr = lambda s: s or ""
1136 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1137 
 
1138 
    excel_integer_format = '0'
1139 
    integer_style = xlwt.XFStyle()
1140 
    integer_style.num_format_str = excel_integer_format
1141 
 
1142 
    sheet.write(0, 0, "Item ID", heading_xf)
1143 
    sheet.write(0, 1, "Category", heading_xf)
1144 
    sheet.write(0, 2, "Product Group.", heading_xf)
1145 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1146 
    sheet.write(0, 4, "Brand", heading_xf)
1147 
    sheet.write(0, 5, "Product Name", heading_xf)
1148 
    sheet.write(0, 6, "Weight", heading_xf)
1149 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1150 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1151 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1152 
    sheet.write(0, 10, "Return Provision", heading_xf)
1153 
    sheet.write(0, 11, "Our Rating", heading_xf)
1154 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1155 
    sheet.write(0, 13, "Our Rank", heading_xf)
1156 
    sheet.write(0, 14, "Our SP", heading_xf)
1157 
    sheet.write(0, 15, "Our TP", heading_xf)
1158 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1159 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1160 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1161 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1162 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1163 
    sheet.write(0, 21, "Preffered Seller", heading_xf)
1164 
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
1165 
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
1166 
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
1167 
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
1168 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
1169 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
1170 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
1171 
    sheet.write(0, 29, "Average Sale", heading_xf)
1172 
    sheet.write(0, 30, "Our NLC", heading_xf)
1173 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
1174 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
1175 
    sheet.write(0, 33, "Target SP", heading_xf)
1176 
    sheet.write(0, 34, "Target TP", heading_xf)
1177 
    sheet.write(0, 35, "Target NLC", heading_xf)
1178 
    sheet.write(0, 36, "Sales Potential", heading_xf)
1179 
    sheet.write(0, 37, "Total Seller", heading_xf)
11193 kshitij.so 1180 
    sheet_iterator = 1
11620 kshitij.so 1181 
    canCompeteItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
11622 kshitij.so 1182 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1183 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1184 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1185 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1186 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
1187 
    for item in canCompeteItems:
1188 
        mpHistory = item[0]
1189 
        flipkartItem = item[1]
1190 
        mpItem = item[2]
1191 
        catItem = item[3]
1192 
        sheet.write(sheet_iterator,0,mpHistory.item_id)
1193 
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1194 
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1195 
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1196 
        sheet.write(sheet_iterator,4,catItem.brand)
1197 
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1198 
        sheet.write(sheet_iterator,6,catItem.weight)
1199 
        sheet.write(sheet_iterator,7,mpItem.courierCost)
1200 
        sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1201 
        sheet.write(sheet_iterator,9,mpItem.commission)
1202 
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
1203 
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 1204 
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1205 
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1206 
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
1207 
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
1208 
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
1209 
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
1210 
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
1211 
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 1212 
#       lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
1213 
#       else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 1214 
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
1215 
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
1216 
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
1217 
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
1218 
        sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
11615 kshitij.so 1219 
#        prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
1220 
#        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
11790 kshitij.so 1221 
        sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
1222 
        sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
1223 
        sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
1224 
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 1225 
        if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1226 
            sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 1227 
        else:
11790 kshitij.so 1228 
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1229 
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1230 
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
1231 
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
1232 
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
1233 
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
11615 kshitij.so 1234 
        proposed_sp = mpHistory.lowestSellingPrice - max(10, mpHistory.lowestSellingPrice*0.001)
11193 kshitij.so 1235 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
11615 kshitij.so 1236 
        target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
11790 kshitij.so 1237 
        sheet.write(sheet_iterator, 33, proposed_sp)
1238 
        sheet.write(sheet_iterator, 34, proposed_tp)
1239 
        sheet.write(sheet_iterator, 35, target_nlc)
1240 
        sheet.write(sheet_iterator, 36, getSalesPotential(mpHistory.lowestSellingPrice,mpHistory.ourNlc))
1241 
        sheet.write(sheet_iterator, 37, mpHistory.totalSeller)
11193 kshitij.so 1242 
        sheet_iterator+=1
11615 kshitij.so 1243 
 
1244 
    canCompeteItems[:] = []
1245 
 
11193 kshitij.so 1246 
 
1247 
    sheet = wbk.add_sheet('Pref and Cheap')
1248 
 
1249 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1250 
 
1251 
    excel_integer_format = '0'
1252 
    integer_style = xlwt.XFStyle()
1253 
    integer_style.num_format_str = excel_integer_format
1254 
    xstr = lambda s: s or ""
1255 
 
1256 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1257 
 
1258 
    excel_integer_format = '0'
1259 
    integer_style = xlwt.XFStyle()
1260 
    integer_style.num_format_str = excel_integer_format
1261 
 
1262 
    sheet.write(0, 0, "Item ID", heading_xf)
1263 
    sheet.write(0, 1, "Category", heading_xf)
1264 
    sheet.write(0, 2, "Product Group.", heading_xf)
1265 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1266 
    sheet.write(0, 4, "Brand", heading_xf)
1267 
    sheet.write(0, 5, "Product Name", heading_xf)
1268 
    sheet.write(0, 6, "Weight", heading_xf)
1269 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1270 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1271 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1272 
    sheet.write(0, 10, "Return Provision", heading_xf)
1273 
    sheet.write(0, 11, "Our Rating", heading_xf)
1274 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1275 
    sheet.write(0, 13, "Our Rank", heading_xf)
1276 
    sheet.write(0, 14, "Our SP", heading_xf)
1277 
    sheet.write(0, 15, "Our TP", heading_xf)
1278 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1279 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1280 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1281 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1282 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1283 
    sheet.write(0, 21, "Second Lowest Seller", heading_xf)
1284 
    sheet.write(0, 22, "Second Lowest Seller Rating", heading_xf)
1285 
    sheet.write(0, 23, "Second Lowest Seller Shipping Time", heading_xf)
1286 
    sheet.write(0, 24, "Second Lowest Seller SP", heading_xf)
1287 
    sheet.write(0, 25, "Second Lowest Seller TP", heading_xf)
1288 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
1289 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
1290 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
1291 
    sheet.write(0, 29, "Average Sale", heading_xf)
1292 
    sheet.write(0, 30, "Our NLC", heading_xf)
1293 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
1294 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
1295 
    sheet.write(0, 33, "Target SP", heading_xf)
1296 
    sheet.write(0, 34, "Target TP", heading_xf)
1297 
    sheet.write(0, 35, "Margin Increased Potential", heading_xf)
1298 
    sheet.write(0, 36, "Total Seller", heading_xf)
1299 
    sheet.write(0, 37, "Auto Pricing Decision", heading_xf)
1300 
    sheet.write(0, 38, "Reason", heading_xf)
1301 
    sheet.write(0, 39, "Updated Price", heading_xf)
11193 kshitij.so 1302 
    sheet_iterator = 1
11615 kshitij.so 1303 
 
11622 kshitij.so 1304 
    buyBoxItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1305 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1306 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1307 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1308 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1309 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
1310 
 
1311 
 
11193 kshitij.so 1312 
    for item in buyBoxItems:
11615 kshitij.so 1313 
        mpHistory = item[0]
1314 
        flipkartItem = item[1]
1315 
        mpItem = item[2]
1316 
        catItem = item[3]
1317 
        sheet.write(sheet_iterator,0,mpHistory.item_id)
1318 
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1319 
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1320 
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1321 
        sheet.write(sheet_iterator,4,catItem.brand)
1322 
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1323 
        sheet.write(sheet_iterator,6,catItem.weight)
1324 
        sheet.write(sheet_iterator,7,mpItem.courierCost)
1325 
        sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1326 
        sheet.write(sheet_iterator,9,mpItem.commission)
1327 
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
1328 
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 1329 
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1330 
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1331 
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
1332 
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
1333 
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
1334 
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
1335 
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
1336 
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 1337 
#        lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
1338 
#        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 1339 
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
1340 
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
1341 
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
1342 
        sheet.write(sheet_iterator,21,mpHistory.secondLowestSellerName)
1343 
        sheet.write(sheet_iterator,22,mpHistory.secondLowestSellerRating)
11615 kshitij.so 1344 
#        secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
1345 
#        else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
11790 kshitij.so 1346 
        sheet.write(sheet_iterator,23,mpHistory.secondLowestSellerShippingTime)
1347 
        sheet.write(sheet_iterator,24,mpHistory.secondLowestSellingPrice)
1348 
        sheet.write(sheet_iterator,25,mpHistory.secondLowestTp)
1349 
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 1350 
        if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1351 
            sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 1352 
        else:
11790 kshitij.so 1353 
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1354 
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1355 
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
1356 
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
1357 
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
1358 
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
11615 kshitij.so 1359 
        proposed_sp = max(mpHistory.secondLowestSellingPrice - max((20, mpHistory.secondLowestSellingPrice*0.002)), mpHistory.lowestPossibleSp)
11193 kshitij.so 1360 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
11615 kshitij.so 1361 
        target_nlc = proposed_tp -  mpHistory.lowestPossibleTp + mpHistory.ourNlc
11790 kshitij.so 1362 
        sheet.write(sheet_iterator, 33, proposed_sp)
1363 
        sheet.write(sheet_iterator, 34, proposed_tp)
1364 
        sheet.write(sheet_iterator, 35, proposed_tp -mpHistory.ourTp )
1365 
        sheet.write(sheet_iterator, 36, mpHistory.totalSeller)
11775 kshitij.so 1366 
        if mpHistory.decision is None:
11790 kshitij.so 1367 
            sheet.write(sheet_iterator, 37, 'Auto Pricing Inactive')
11775 kshitij.so 1368 
            sheet_iterator+=1
1369 
            continue
11790 kshitij.so 1370 
        sheet.write(sheet_iterator, 37, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
1371 
        sheet.write(sheet_iterator, 38, mpHistory.reason)
11776 kshitij.so 1372 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
11790 kshitij.so 1373 
            sheet.write(sheet_iterator, 39, math.ceil(mpHistory.proposedSellingPrice))
11776 kshitij.so 1374 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
11790 kshitij.so 1375 
            sheet.write(sheet_iterator, 39, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
11776 kshitij.so 1376 
 
11193 kshitij.so 1377 
        sheet_iterator+=1
1378 
 
11615 kshitij.so 1379 
    buyBoxItems[:] = []
11193 kshitij.so 1380 
 
11615 kshitij.so 1381 
    sheet = wbk.add_sheet('Pref Not Cheap')
11193 kshitij.so 1382 
 
1383 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1384 
 
1385 
    excel_integer_format = '0'
1386 
    integer_style = xlwt.XFStyle()
1387 
    integer_style.num_format_str = excel_integer_format
1388 
    xstr = lambda s: s or ""
1389 
 
1390 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1391 
 
1392 
    excel_integer_format = '0'
1393 
    integer_style = xlwt.XFStyle()
1394 
    integer_style.num_format_str = excel_integer_format
1395 
 
1396 
    sheet.write(0, 0, "Item ID", heading_xf)
1397 
    sheet.write(0, 1, "Category", heading_xf)
1398 
    sheet.write(0, 2, "Product Group.", heading_xf)
1399 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1400 
    sheet.write(0, 4, "Brand", heading_xf)
1401 
    sheet.write(0, 5, "Product Name", heading_xf)
1402 
    sheet.write(0, 6, "Weight", heading_xf)
1403 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1404 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1405 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1406 
    sheet.write(0, 10, "Return Provision", heading_xf)
1407 
    sheet.write(0, 11, "Our Rating", heading_xf)
1408 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1409 
    sheet.write(0, 13, "Our Rank", heading_xf)
1410 
    sheet.write(0, 14, "Our SP", heading_xf)
1411 
    sheet.write(0, 15, "Our TP", heading_xf)
1412 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1413 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1414 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1415 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1416 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1417 
    sheet.write(0, 21, "Preffered Seller", heading_xf)
1418 
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
1419 
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
1420 
    sheet.write(0, 24, "Preffered Seller SP", heading_xf)
1421 
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
1422 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
1423 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
1424 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
1425 
    sheet.write(0, 29, "Average Sale", heading_xf)
1426 
    sheet.write(0, 30, "Our NLC", heading_xf)
1427 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
1428 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
1429 
    sheet.write(0, 33, "Target SP", heading_xf)
1430 
    sheet.write(0, 34, "Target TP", heading_xf)
1431 
    sheet.write(0, 35, "Total Seller", heading_xf)
1432 
    sheet.write(0, 36, "Auto Pricing Decision", heading_xf)
1433 
    sheet.write(0, 37, "Reason", heading_xf)
1434 
    sheet.write(0, 38, "Updated Price", heading_xf)
11775 kshitij.so 1435 
 
11193 kshitij.so 1436 
    sheet_iterator = 1
11615 kshitij.so 1437 
 
11622 kshitij.so 1438 
    prefNotCheapItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1439 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1440 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1441 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1442 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1443 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP).all()
1444 
 
1445 
 
1446 
    for item in prefNotCheapItems:
1447 
        mpHistory = item[0]
1448 
        flipkartItem = item[1]
1449 
        mpItem = item[2]
1450 
        catItem = item[3]
1451 
        sheet.write(sheet_iterator,0,mpHistory.item_id)
1452 
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1453 
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1454 
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1455 
        sheet.write(sheet_iterator,4,catItem.brand)
1456 
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1457 
        sheet.write(sheet_iterator,6,catItem.weight)
1458 
        sheet.write(sheet_iterator,7,mpItem.courierCost)
1459 
        sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1460 
        sheet.write(sheet_iterator,9,mpItem.commission)
1461 
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
1462 
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 1463 
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1464 
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1465 
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
1466 
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
1467 
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
1468 
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
1469 
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
1470 
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 1471 
#        lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
1472 
#        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 1473 
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
1474 
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
1475 
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
1476 
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
1477 
        sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
11615 kshitij.so 1478 
#        secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
1479 
#        else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
11790 kshitij.so 1480 
        sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
1481 
        sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
1482 
        sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
1483 
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 1484 
        if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1485 
            sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 1486 
        else:
11790 kshitij.so 1487 
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1488 
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1489 
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
1490 
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
1491 
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
1492 
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
11775 kshitij.so 1493 
        proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
11615 kshitij.so 1494 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
1495 
        target_nlc = proposed_tp -  mpHistory.lowestPossibleTp + mpHistory.ourNlc
11790 kshitij.so 1496 
        sheet.write(sheet_iterator, 33, proposed_sp)
1497 
        sheet.write(sheet_iterator, 34, proposed_tp)
1498 
        sheet.write(sheet_iterator, 35, mpHistory.totalSeller)
11775 kshitij.so 1499 
        if mpHistory.decision is None:
11790 kshitij.so 1500 
            sheet.write(sheet_iterator, 36, 'Auto Pricing Inactive')
11775 kshitij.so 1501 
            sheet_iterator+=1
1502 
            continue
11790 kshitij.so 1503 
        sheet.write(sheet_iterator, 36, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
1504 
        sheet.write(sheet_iterator, 37, mpHistory.reason)
11776 kshitij.so 1505 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
11790 kshitij.so 1506 
            sheet.write(sheet_iterator, 38, math.ceil(mpHistory.proposedSellingPrice))
11776 kshitij.so 1507 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
11790 kshitij.so 1508 
            sheet.write(sheet_iterator, 38, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
11775 kshitij.so 1509 
 
11193 kshitij.so 1510 
        sheet_iterator+=1
11615 kshitij.so 1511 
 
1512 
    prefNotCheapItems[:] = []
11193 kshitij.so 1513 
 
1514 
    sheet = wbk.add_sheet('Cheap But Not Pref')
1515 
 
1516 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1517 
 
1518 
    excel_integer_format = '0'
1519 
    integer_style = xlwt.XFStyle()
1520 
    integer_style.num_format_str = excel_integer_format
1521 
    xstr = lambda s: s or ""
1522 
 
1523 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1524 
 
1525 
    excel_integer_format = '0'
1526 
    integer_style = xlwt.XFStyle()
1527 
    integer_style.num_format_str = excel_integer_format
1528 
 
1529 
    sheet.write(0, 0, "Item ID", heading_xf)
1530 
    sheet.write(0, 1, "Category", heading_xf)
1531 
    sheet.write(0, 2, "Product Group.", heading_xf)
1532 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1533 
    sheet.write(0, 4, "Brand", heading_xf)
1534 
    sheet.write(0, 5, "Product Name", heading_xf)
1535 
    sheet.write(0, 6, "Weight", heading_xf)
1536 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1537 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1538 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1539 
    sheet.write(0, 10, "Return Provision", heading_xf)
1540 
    sheet.write(0, 11, "Our Rank", heading_xf)
1541 
    sheet.write(0, 12, "Lowest Seller", heading_xf)
1542 
    sheet.write(0, 13, "Our Rating", heading_xf)
1543 
    sheet.write(0, 14, "Our Shipping Time", heading_xf)
1544 
    sheet.write(0, 15, "Our SP", heading_xf)
1545 
    sheet.write(0, 16, "Our TP", heading_xf)
1546 
    sheet.write(0, 17, "Preffered Seller", heading_xf)
1547 
    sheet.write(0, 18, "Preffered Seller Rating", heading_xf)
1548 
    sheet.write(0, 19, "Preffered Seller Shipping Time", heading_xf)
1549 
    sheet.write(0, 20, "Preffered Seller SP", heading_xf)
1550 
    sheet.write(0, 21, "Preffered Seller TP", heading_xf)
1551 
    sheet.write(0, 22, "Our Flipkart Inventory", heading_xf)
1552 
    sheet.write(0, 23, "Our Net Availability",heading_xf)
1553 
    sheet.write(0, 24, "Last Five Day Sale", heading_xf)
1554 
    sheet.write(0, 25, "Average Sale", heading_xf)
1555 
    sheet.write(0, 26, "Our NLC", heading_xf)
1556 
    sheet.write(0, 27, "Lowest Possible SP", heading_xf)
1557 
    sheet.write(0, 28, "Lowest Possible TP", heading_xf)
1558 
    sheet.write(0, 29, "Total Seller", heading_xf)
11193 kshitij.so 1559 
    sheet_iterator = 1
11615 kshitij.so 1560 
 
11622 kshitij.so 1561 
    cheapNotPrefferedItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1562 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1563 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1564 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1565 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1566 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CHEAP_BUT_NOT_PREF).all()
1567 
 
1568 
    for item in cheapNotPrefferedItems:
1569 
        mpHistory = item[0]
1570 
        flipkartItem = item[1]
1571 
        mpItem = item[2]
1572 
        catItem = item[3]
1573 
        sheet.write(sheet_iterator,0,mpHistory.item_id)
1574 
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1575 
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1576 
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1577 
        sheet.write(sheet_iterator,4,catItem.brand)
1578 
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1579 
        sheet.write(sheet_iterator,6,catItem.weight)
1580 
        sheet.write(sheet_iterator,7,mpItem.courierCost)
1581 
        sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1582 
        sheet.write(sheet_iterator,9,mpItem.commission)
1583 
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
1584 
        sheet.write(sheet_iterator,11,mpHistory.ourRank)
1585 
        sheet.write(sheet_iterator,12,mpHistory.lowestSellerName)
1586 
        sheet.write(sheet_iterator,13,mpHistory.ourRating)
11615 kshitij.so 1587 
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1588 
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1589 
        sheet.write(sheet_iterator,14,mpHistory.lowestSellerShippingTime)
1590 
        sheet.write(sheet_iterator,15,mpHistory.lowestSellingPrice)
1591 
        sheet.write(sheet_iterator,16,mpHistory.lowestTp)
1592 
        sheet.write(sheet_iterator,17,mpHistory.prefferedSellerName)
1593 
        sheet.write(sheet_iterator,18,mpHistory.prefferedSellerRating)
11615 kshitij.so 1594 
#        prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
1595 
#        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
11790 kshitij.so 1596 
        sheet.write(sheet_iterator,19,mpHistory.prefferedSellerShippingTime)
1597 
        sheet.write(sheet_iterator,20,mpHistory.prefferedSellerSellingPrice)
1598 
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerTp)
1599 
        sheet.write(sheet_iterator,22,mpHistory.ourInventory)
11615 kshitij.so 1600 
        if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1601 
            sheet.write(sheet_iterator, 23, 'Info not available')
11193 kshitij.so 1602 
        else:
11790 kshitij.so 1603 
            sheet.write(sheet_iterator, 23, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1604 
        sheet.write(sheet_iterator, 24, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1605 
        sheet.write(sheet_iterator, 25, (itemSaleMap.get(mpHistory.item_id))[3])
1606 
        sheet.write(sheet_iterator, 26, mpHistory.ourNlc)
1607 
        sheet.write(sheet_iterator, 27, mpHistory.lowestPossibleSp)
1608 
        sheet.write(sheet_iterator, 28, mpHistory.lowestPossibleTp)
11193 kshitij.so 1609 
        #proposed_sp = max(flipkartDetails.secondLowestSellerSp - max((20, flipkartDetails.secondLowestSellerSp*0.002)), flipkartPricing.lowestPossibleSp)
1610 
        #proposed_tp = getTargetTp(proposed_sp,mpItem)
1611 
        #target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
11790 kshitij.so 1612 
        sheet.write(sheet_iterator, 29, mpHistory.totalSeller)
11193 kshitij.so 1613 
        sheet_iterator+=1
11615 kshitij.so 1614 
 
1615 
    cheapNotPrefferedItems[:]=[]
11193 kshitij.so 1616 
 
1617 
    sheet = wbk.add_sheet('Can Compete-With Inventory')
1618 
    xstr = lambda s: s or ""
1619 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1620 
 
1621 
    excel_integer_format = '0'
1622 
    integer_style = xlwt.XFStyle()
1623 
    integer_style.num_format_str = excel_integer_format
1624 
 
1625 
    sheet.write(0, 0, "Item ID", heading_xf)
1626 
    sheet.write(0, 1, "Category", heading_xf)
1627 
    sheet.write(0, 2, "Product Group.", heading_xf)
1628 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1629 
    sheet.write(0, 4, "Brand", heading_xf)
1630 
    sheet.write(0, 5, "Product Name", heading_xf)
1631 
    sheet.write(0, 6, "Weight", heading_xf)
1632 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1633 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1634 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1635 
    sheet.write(0, 10, "Return Provision", heading_xf)
1636 
    sheet.write(0, 11, "Our Rating", heading_xf)
1637 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1638 
    sheet.write(0, 13, "Our Rank", heading_xf)
1639 
    sheet.write(0, 14, "Our SP", heading_xf)
1640 
    sheet.write(0, 15, "Our TP", heading_xf)
1641 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1642 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1643 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1644 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1645 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1646 
    sheet.write(0, 21, "Preffered Seller", heading_xf)
1647 
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
1648 
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
1649 
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
1650 
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
1651 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
1652 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
1653 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
1654 
    sheet.write(0, 29, "Average Sale", heading_xf)
1655 
    sheet.write(0, 30, "Our NLC", heading_xf)
1656 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
1657 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
1658 
    sheet.write(0, 33, "Target SP", heading_xf)
1659 
    sheet.write(0, 34, "Target TP", heading_xf)
1660 
    sheet.write(0, 35, "Target NLC", heading_xf)
1661 
    sheet.write(0, 36, "Sales Potential", heading_xf)
1662 
    sheet.write(0, 37, "Total Seller", heading_xf)
1663 
    sheet.write(0, 38, "Auto Pricing Decision", heading_xf)
1664 
    sheet.write(0, 39, "Reason", heading_xf)
1665 
    sheet.write(0, 40, "Updated Price", heading_xf)
11193 kshitij.so 1666 
    sheet_iterator = 1
11615 kshitij.so 1667 
 
11622 kshitij.so 1668 
    competitiveItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1669 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1670 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1671 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1672 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1673 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).all()
1674 
 
1675 
    for item in competitiveItems:
1676 
        mpHistory = item[0]
1677 
        flipkartItem = item[1]
1678 
        mpItem = item[2]
1679 
        catItem = item[3]
1680 
        sheet.write(sheet_iterator,0,mpHistory.item_id)
1681 
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1682 
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1683 
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1684 
        sheet.write(sheet_iterator,4,catItem.brand)
1685 
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1686 
        sheet.write(sheet_iterator,6,catItem.weight)
1687 
        sheet.write(sheet_iterator,7,mpItem.courierCost)
1688 
        sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1689 
        sheet.write(sheet_iterator,9,mpItem.commission)
1690 
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
1691 
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 1692 
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1693 
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1694 
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
1695 
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
1696 
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
1697 
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
1698 
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
1699 
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 1700 
#        lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
1701 
#        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 1702 
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
1703 
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
1704 
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
1705 
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
1706 
        sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
11615 kshitij.so 1707 
#        prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
1708 
#        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
11790 kshitij.so 1709 
        sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
1710 
        sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
1711 
        sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
1712 
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 1713 
        if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1714 
            sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 1715 
        else:
11790 kshitij.so 1716 
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1717 
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1718 
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
1719 
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
1720 
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
1721 
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
11775 kshitij.so 1722 
        proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
11193 kshitij.so 1723 
        proposed_tp = getTargetTp(proposed_sp,mpItem)
11615 kshitij.so 1724 
        target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
11790 kshitij.so 1725 
        sheet.write(sheet_iterator, 33, proposed_sp)
1726 
        sheet.write(sheet_iterator, 34, proposed_tp)
1727 
        sheet.write(sheet_iterator, 35, target_nlc)
1728 
        sheet.write(sheet_iterator, 36, getSalesPotential(mpHistory.lowestSellingPrice,mpHistory.ourNlc))
1729 
        sheet.write(sheet_iterator, 37, mpHistory.totalSeller)
11775 kshitij.so 1730 
        if mpHistory.decision is None:
11790 kshitij.so 1731 
            sheet.write(sheet_iterator, 38, 'Auto Pricing Inactive')
11775 kshitij.so 1732 
            sheet_iterator+=1
1733 
            continue
11790 kshitij.so 1734 
        sheet.write(sheet_iterator, 38, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
1735 
        sheet.write(sheet_iterator, 39, mpHistory.reason)
11776 kshitij.so 1736 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
11790 kshitij.so 1737 
            sheet.write(sheet_iterator, 40, math.ceil(mpHistory.proposedSellingPrice))
11776 kshitij.so 1738 
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
11790 kshitij.so 1739 
            sheet.write(sheet_iterator, 40, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
11775 kshitij.so 1740 
 
11193 kshitij.so 1741 
        sheet_iterator+=1
11615 kshitij.so 1742 
 
1743 
    competitiveItems[:]=[]
1744 
 
11193 kshitij.so 1745 
    sheet = wbk.add_sheet('Negative Margin')
1746 
    xstr = lambda s: s or ""
1747 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1748 
 
1749 
    excel_integer_format = '0'
1750 
    integer_style = xlwt.XFStyle()
1751 
    integer_style.num_format_str = excel_integer_format
1752 
 
1753 
    sheet.write(0, 0, "Item ID", heading_xf)
1754 
    sheet.write(0, 1, "Category", heading_xf)
1755 
    sheet.write(0, 2, "Product Group.", heading_xf)
1756 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1757 
    sheet.write(0, 4, "Brand", heading_xf)
1758 
    sheet.write(0, 5, "Product Name", heading_xf)
1759 
    sheet.write(0, 6, "Weight", heading_xf)
1760 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1761 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1762 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1763 
    sheet.write(0, 10, "Return Provision", heading_xf)
1764 
    sheet.write(0, 11, "Our Rating", heading_xf)
1765 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1766 
    sheet.write(0, 13, "Our Rank", heading_xf)
1767 
    sheet.write(0, 14, "Our SP", heading_xf)
1768 
    sheet.write(0, 15, "Our TP", heading_xf)
1769 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1770 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1771 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1772 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1773 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1774 
    sheet.write(0, 21, "Preffered Seller", heading_xf)
1775 
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
1776 
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
1777 
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
1778 
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
1779 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
1780 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
1781 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
1782 
    sheet.write(0, 29, "Average Sale", heading_xf)
1783 
    sheet.write(0, 30, "Our NLC", heading_xf)
1784 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
1785 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
1786 
    sheet.write(0, 33, "Margin", heading_xf)
1787 
    sheet.write(0, 34, "Total Seller", heading_xf)
11193 kshitij.so 1788 
    sheet_iterator = 1
11615 kshitij.so 1789 
 
11622 kshitij.so 1790 
    negativeMargin = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1791 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1792 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1793 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1794 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1795 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).all()
1796 
 
11193 kshitij.so 1797 
    for item in negativeMargin:
11615 kshitij.so 1798 
        mpHistory = item[0]
1799 
        flipkartItem = item[1]
1800 
        mpItem = item[2]
1801 
        catItem = item[3]
1802 
        sheet.write(sheet_iterator,0,mpHistory.item_id)
1803 
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1804 
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1805 
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1806 
        sheet.write(sheet_iterator,4,catItem.brand)
1807 
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1808 
        sheet.write(sheet_iterator,6,catItem.weight)
1809 
        sheet.write(sheet_iterator,7,mpItem.courierCost)
1810 
        sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1811 
        sheet.write(sheet_iterator,9,mpItem.commission)
1812 
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
1813 
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 1814 
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
1815 
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 1816 
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
1817 
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
1818 
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
1819 
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
1820 
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
1821 
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 1822 
#        lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
1823 
#        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 1824 
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
1825 
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
1826 
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
1827 
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
1828 
        sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
11615 kshitij.so 1829 
#        prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
1830 
#        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
11790 kshitij.so 1831 
        sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
1832 
        sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
1833 
        sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
1834 
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 1835 
        if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 1836 
            sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 1837 
        else:
11790 kshitij.so 1838 
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
1839 
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
1840 
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
1841 
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
1842 
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
1843 
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
1844 
        sheet.write(sheet_iterator, 33, round((mpHistory.ourTp - mpHistory.lowestPossibleTp),2))
1845 
        sheet.write(sheet_iterator, 34, mpHistory.totalSeller)
11193 kshitij.so 1846 
        sheet_iterator+=1
11615 kshitij.so 1847 
 
1848 
    negativeMargin[:]=[]
11193 kshitij.so 1849 
 
1850 
    if (runType=='FULL'):
1851 
        sheet = wbk.add_sheet('Auto Favorites')
1852 
 
1853 
        heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1854 
 
1855 
        excel_integer_format = '0'
1856 
        integer_style = xlwt.XFStyle()
1857 
        integer_style.num_format_str = excel_integer_format
1858 
        xstr = lambda s: s or ""
1859 
 
1860 
        sheet.write(0, 0, "Item ID", heading_xf)
1861 
        sheet.write(0, 1, "Brand", heading_xf)
1862 
        sheet.write(0, 2, "Product Name", heading_xf)
1863 
        sheet.write(0, 3, "Auto Favourite", heading_xf)
1864 
        sheet.write(0, 4, "Reason", heading_xf)
1865 
 
1866 
        sheet_iterator=1
1867 
        for autoFav in nowAutoFav:
1868 
            itemId = autoFav[0]
1869 
            reason = autoFav[1]
1870 
            it = Item.query.filter_by(id=itemId).one()
1871 
            sheet.write(sheet_iterator, 0, itemId)
1872 
            sheet.write(sheet_iterator, 1, it.brand)
1873 
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1874 
            sheet.write(sheet_iterator, 3, "True")
1875 
            sheet.write(sheet_iterator, 4, reason)
1876 
            sheet_iterator+=1
1877 
        for prevFav in previousAutoFav:
1878 
            it = Item.query.filter_by(id=prevFav).one()
1879 
            sheet.write(sheet_iterator, 0, prevFav)
1880 
            sheet.write(sheet_iterator, 1, it.brand)
1881 
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
1882 
            sheet.write(sheet_iterator, 3, "False")
1883 
            sheet_iterator+=1
1884 
 
1885 
    sheet = wbk.add_sheet('Exception Item List')
1886 
 
1887 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1888 
 
1889 
    excel_integer_format = '0'
1890 
    integer_style = xlwt.XFStyle()
1891 
    integer_style.num_format_str = excel_integer_format
1892 
    xstr = lambda s: s or ""
1893 
 
1894 
    sheet.write(0, 0, "Item ID", heading_xf)
1895 
    sheet.write(0, 1, "FK Serial number", heading_xf)
1896 
    sheet.write(0, 2, "Brand", heading_xf)
1897 
    sheet.write(0, 3, "Product Name", heading_xf)
1898 
    sheet.write(0, 4, "Reason", heading_xf)
1899 
    sheet_iterator=1
11615 kshitij.so 1900 
 
11622 kshitij.so 1901 
    exeptionItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1902 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1903 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1904 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1905 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1906 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.EXCEPTION).all()
1907 
 
1908 
    for item in exeptionItems:
1909 
        mpHistory = item[0]
1910 
        flipkartItem = item[1]
1911 
        mpItem = item[2]
1912 
        catItem = item[3]
1913 
        sheet.write(sheet_iterator, 0, mpHistory.item_id)
1914 
        sheet.write(sheet_iterator, 1, flipkartItem.flipkartSerialNumber)
1915 
        sheet.write(sheet_iterator, 2, catItem.brand)
1916 
        sheet.write(sheet_iterator, 3, xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
11193 kshitij.so 1917 
        try:
11615 kshitij.so 1918 
            if mpHistory.totalSeller is None:
11193 kshitij.so 1919 
                pass
1920 
        except:
1921 
            sheet.write(sheet_iterator, 4, "Unable to fetch info from Flipkart")
1922 
            sheet_iterator+=1
1923 
            continue
1924 
        sheet.write(sheet_iterator, 4, "No Seller Available")
1925 
        sheet_iterator+=1
11615 kshitij.so 1926 
 
1927 
    exeptionItems[:]=[]
11193 kshitij.so 1928 
 
1929 
    sheet = wbk.add_sheet('Can Compete-No Inv')
1930 
    xstr = lambda s: s or ""
1931 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
1932 
 
1933 
    excel_integer_format = '0'
1934 
    integer_style = xlwt.XFStyle()
1935 
    integer_style.num_format_str = excel_integer_format
1936 
 
1937 
    sheet.write(0, 0, "Item ID", heading_xf)
1938 
    sheet.write(0, 1, "Category", heading_xf)
1939 
    sheet.write(0, 2, "Product Group.", heading_xf)
1940 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
1941 
    sheet.write(0, 4, "Brand", heading_xf)
1942 
    sheet.write(0, 5, "Product Name", heading_xf)
1943 
    sheet.write(0, 6, "Weight", heading_xf)
1944 
    sheet.write(0, 7, "Courier Cost", heading_xf)
1945 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 1946 
    sheet.write(0, 9, "Commission Rate", heading_xf)
1947 
    sheet.write(0, 10, "Return Provision", heading_xf)
1948 
    sheet.write(0, 11, "Our Rating", heading_xf)
1949 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
1950 
    sheet.write(0, 13, "Our Rank", heading_xf)
1951 
    sheet.write(0, 14, "Our SP", heading_xf)
1952 
    sheet.write(0, 15, "Our TP", heading_xf)
1953 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
1954 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
1955 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
1956 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
1957 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
1958 
    sheet.write(0, 21, "Preffered Seller", heading_xf)
1959 
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
1960 
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
1961 
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
1962 
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
1963 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
1964 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
1965 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
1966 
    sheet.write(0, 29, "Average Sale", heading_xf)
1967 
    sheet.write(0, 30, "Our NLC", heading_xf)
1968 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
1969 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
1970 
    sheet.write(0, 33, "Target SP", heading_xf)
1971 
    sheet.write(0, 34, "Target TP", heading_xf)
1972 
    sheet.write(0, 35, "Sales Potential", heading_xf)
1973 
    sheet.write(0, 36, "Total Seller", heading_xf)
11193 kshitij.so 1974 
    sheet_iterator = 1
11615 kshitij.so 1975 
 
11622 kshitij.so 1976 
    competitiveNoInventory = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
1977 
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
1978 
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
1979 
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
11615 kshitij.so 1980 
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
1981 
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE_NO_INVENTORY).all()
1982 
 
11193 kshitij.so 1983 
    for item in competitiveNoInventory:
11615 kshitij.so 1984 
        mpHistory = item[0]
1985 
        flipkartItem = item[1]
1986 
        mpItem = item[2]
1987 
        catItem = item[3]
1988 
        if ((not inventoryMap.has_key(mpHistory.item_id)) or getNetAvailability(inventoryMap.get(mpHistory.item_id))<=0):
1989 
            sheet.write(sheet_iterator,0,mpHistory.item_id)
1990 
            sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
1991 
            sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
1992 
            sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
1993 
            sheet.write(sheet_iterator,4,catItem.brand)
1994 
            sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
1995 
            sheet.write(sheet_iterator,6,catItem.weight)
1996 
            sheet.write(sheet_iterator,7,mpItem.courierCost)
1997 
            sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 1998 
            sheet.write(sheet_iterator,9,mpItem.commission)
1999 
            sheet.write(sheet_iterator,10,mpItem.returnProvision)
2000 
            sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 2001 
#            ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
2002 
#            else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 2003 
            sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
2004 
            sheet.write(sheet_iterator,13,mpHistory.ourRank)
2005 
            sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
2006 
            sheet.write(sheet_iterator,15,mpHistory.ourTp)
2007 
            sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
2008 
            sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 2009 
#            lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
2010 
#            else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 2011 
            sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
2012 
            sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
2013 
            sheet.write(sheet_iterator,20,mpHistory.lowestTp)
2014 
            sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
2015 
            sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
11615 kshitij.so 2016 
#            prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
2017 
#            else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
11790 kshitij.so 2018 
            sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
2019 
            sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
2020 
            sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
2021 
            sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 2022 
            if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 2023 
                sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 2024 
            else:
11790 kshitij.so 2025 
                sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
2026 
            sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
2027 
            sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
2028 
            sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
2029 
            sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
2030 
            sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
11615 kshitij.so 2031 
            proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
11193 kshitij.so 2032 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
11790 kshitij.so 2033 
            sheet.write(sheet_iterator, 33, proposed_sp)
2034 
            sheet.write(sheet_iterator, 34, proposed_tp)
2035 
            sheet.write(sheet_iterator, 35, getSalesPotential(mpHistory.lowestPossibleSp,mpHistory.ourNlc))
2036 
            sheet.write(sheet_iterator, 36, mpHistory.totalSeller)
11193 kshitij.so 2037 
            sheet_iterator+=1
2038 
 
11615 kshitij.so 2039 
 
11193 kshitij.so 2040 
    sheet = wbk.add_sheet('Can Compete-No Inv On FK')
2041 
    xstr = lambda s: s or ""
2042 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
2043 
 
2044 
    excel_integer_format = '0'
2045 
    integer_style = xlwt.XFStyle()
2046 
    integer_style.num_format_str = excel_integer_format
2047 
 
2048 
    sheet.write(0, 0, "Item ID", heading_xf)
2049 
    sheet.write(0, 1, "Category", heading_xf)
2050 
    sheet.write(0, 2, "Product Group.", heading_xf)
2051 
    sheet.write(0, 3, "FK Serial Number", heading_xf)
2052 
    sheet.write(0, 4, "Brand", heading_xf)
2053 
    sheet.write(0, 5, "Product Name", heading_xf)
2054 
    sheet.write(0, 6, "Weight", heading_xf)
2055 
    sheet.write(0, 7, "Courier Cost", heading_xf)
2056 
    sheet.write(0, 8, "Risky", heading_xf)
11790 kshitij.so 2057 
    sheet.write(0, 9, "Commission Rate", heading_xf)
2058 
    sheet.write(0, 10, "Return Provision", heading_xf)
2059 
    sheet.write(0, 11, "Our Rating", heading_xf)
2060 
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
2061 
    sheet.write(0, 13, "Our Rank", heading_xf)
2062 
    sheet.write(0, 14, "Our SP", heading_xf)
2063 
    sheet.write(0, 15, "Our TP", heading_xf)
2064 
    sheet.write(0, 16, "Lowest Seller", heading_xf)
2065 
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
2066 
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
2067 
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
2068 
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
2069 
    sheet.write(0, 21, "Preffered Seller", heading_xf)
2070 
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
2071 
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
2072 
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
2073 
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
2074 
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
2075 
    sheet.write(0, 27, "Our Net Availability",heading_xf)
2076 
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
2077 
    sheet.write(0, 29, "Average Sale", heading_xf)
2078 
    sheet.write(0, 30, "Our NLC", heading_xf)
2079 
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
2080 
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
2081 
    sheet.write(0, 33, "Target SP", heading_xf)
2082 
    sheet.write(0, 34, "Target TP", heading_xf)
2083 
    sheet.write(0, 35, "Sales Potential", heading_xf)
2084 
    sheet.write(0, 36, "Total Seller", heading_xf)
11193 kshitij.so 2085 
    sheet_iterator = 1
2086 
    for item in competitiveNoInventory:
11615 kshitij.so 2087 
        mpHistory = item[0]
2088 
        flipkartItem = item[1]
2089 
        mpItem = item[2]
2090 
        catItem = item[3]
2091 
        if (inventoryMap.has_key(mpHistory.item_id) and getNetAvailability(inventoryMap.get(mpHistory.item_id))>0):
2092 
            sheet.write(sheet_iterator,0,mpHistory.item_id)
2093 
            sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
2094 
            sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
2095 
            sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
2096 
            sheet.write(sheet_iterator,4,catItem.brand)
2097 
            sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
2098 
            sheet.write(sheet_iterator,6,catItem.weight)
2099 
            sheet.write(sheet_iterator,7,mpItem.courierCost)
2100 
            sheet.write(sheet_iterator,8,catItem.risky)
11790 kshitij.so 2101 
            sheet.write(sheet_iterator,9,mpItem.commission)
2102 
            sheet.write(sheet_iterator,10,mpItem.returnProvision)
2103 
            sheet.write(sheet_iterator,11,mpHistory.ourRating)
11615 kshitij.so 2104 
#            ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
2105 
#            else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
11790 kshitij.so 2106 
            sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
2107 
            sheet.write(sheet_iterator,13,mpHistory.ourRank)
2108 
            sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
2109 
            sheet.write(sheet_iterator,15,mpHistory.ourTp)
2110 
            sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
2111 
            sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
11615 kshitij.so 2112 
#            lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
2113 
#            else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
11790 kshitij.so 2114 
            sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
2115 
            sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
2116 
            sheet.write(sheet_iterator,20,mpHistory.lowestTp)
2117 
            sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
2118 
            sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
11615 kshitij.so 2119 
#            prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
2120 
#            else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
11790 kshitij.so 2121 
            sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
2122 
            sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
2123 
            sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
2124 
            sheet.write(sheet_iterator,26,mpHistory.ourInventory)
11615 kshitij.so 2125 
            if (not inventoryMap.has_key(mpHistory.item_id)):
11790 kshitij.so 2126 
                sheet.write(sheet_iterator, 27, 'Info not available')
11193 kshitij.so 2127 
            else:
11790 kshitij.so 2128 
                sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
2129 
            sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
2130 
            sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
2131 
            sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
2132 
            sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
2133 
            sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
11615 kshitij.so 2134 
            proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
11193 kshitij.so 2135 
            proposed_tp = getTargetTp(proposed_sp,mpItem)
11790 kshitij.so 2136 
            sheet.write(sheet_iterator, 33, proposed_sp)
2137 
            sheet.write(sheet_iterator, 34, proposed_tp)
2138 
            sheet.write(sheet_iterator, 35, getSalesPotential(mpHistory.lowestPossibleSp,mpHistory.ourNlc))
2139 
            sheet.write(sheet_iterator, 36, mpHistory.totalSeller)
11193 kshitij.so 2140 
            sheet_iterator+=1
11615 kshitij.so 2141 
    competitiveNoInventory[:]=[]
11193 kshitij.so 2142 
 
11775 kshitij.so 2143 
#    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()
2144 
#    sheet = wbk.add_sheet('Auto Inc and Dec')
2145 
#
2146 
#    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
2147 
#
2148 
#    excel_integer_format = '0'
2149 
#    integer_style = xlwt.XFStyle()
2150 
#    integer_style.num_format_str = excel_integer_format
2151 
#    xstr = lambda s: s or ""
2152 
#
2153 
#    sheet.write(0, 0, "Item ID", heading_xf)
2154 
#    sheet.write(0, 1, "Brand", heading_xf)
2155 
#    sheet.write(0, 2, "Product Name", heading_xf)
2156 
#    sheet.write(0, 3, "Decision", heading_xf)
2157 
#    sheet.write(0, 4, "Reason", heading_xf)
2158 
#    sheet.write(0, 5, "Old Selling Price", heading_xf)
2159 
#    sheet.write(0, 6, "Selling Price Updated",heading_xf)
2160 
#
2161 
#    sheet_iterator=1
2162 
#    for autoPricingItem in autoPricingItems:
2163 
#        mpHistory = autoPricingItem[0]
2164 
#        item = autoPricingItem[1]
2165 
#        it = Item.query.filter_by(id=item.id).one()
2166 
#        sheet.write(sheet_iterator, 0, item.id)
2167 
#        sheet.write(sheet_iterator, 1, it.brand)
2168 
#        sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
2169 
#        sheet.write(sheet_iterator, 3, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
2170 
#        sheet.write(sheet_iterator, 4, mpHistory.reason)
2171 
#        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
2172 
#            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
2173 
#            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.proposedSellingPrice))
2174 
#        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
2175 
#            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
2176 
#            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
2177 
#        sheet_iterator+=1
11193 kshitij.so 2178 
 
2179 
    filename = "/tmp/flipkart-report-"+runType+" " + str(timestamp) + ".xls"
2180 
    wbk.save(filename)
2181 
    try:
12181 kshitij.so 2182 
        EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Flipkart Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
2183 
        #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 2184 
    except Exception as e:
2185 
        print e
2186 
        print "Unable to send report.Trying with local SMTP"
2187 
        smtpServer = smtplib.SMTP('localhost')
2188 
        smtpServer.set_debuglevel(1)
11779 kshitij.so 2189 
        sender = 'build@shop2020.in'
12181 kshitij.so 2190 
        recipients = ["kshitij.sood@saholic.com"]
11193 kshitij.so 2191 
        msg = MIMEMultipart()
11228 kshitij.so 2192 
        msg['Subject'] = "Flipkart Scraping" + ' '+runType+' - ' + str(datetime.now())
11193 kshitij.so 2193 
        msg['From'] = sender
12181 kshitij.so 2194 
        #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 2195 
        msg['To'] = ",".join(recipients)
2196 
        fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
2197 
        fileMsg.set_payload(file(filename).read())
2198 
        email.encoders.encode_base64(fileMsg)
2199 
        fileMsg.add_header('Content-Disposition','attachment;filename=flipkart.xls')
2200 
        msg.attach(fileMsg)
2201 
        try:
2202 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2203 
            print "Successfully sent email"
2204 
        except:
2205 
            print "Error: unable to send email."
11560 kshitij.so 2206 
 
11571 kshitij.so 2207 
def populateScrapingResults(val):
2208 
    try:
12193 kshitij.so 2209 
        now = datetime.now()
2210 
        print "Fetching data for serial Number %s %s" %(val.fkSerialNumber,str(now))
12204 kshitij.so 2211 
        flipkartDetails = fetchDetails(val.fkSerialNumber)
11571 kshitij.so 2212 
        val.flipkartDetails = flipkartDetails
2213 
    except Exception as e:
12193 kshitij.so 2214 
        print "Unable to fetch details of %s" %(val.fkSerialNumber)
11571 kshitij.so 2215 
        print e
2216 
        val.flipkartDetails = None
2217 
        return
2218 
 
12181 kshitij.so 2219 
#    try:
2220 
#        request_url = "https://api.flipkart.net/sellers/skus/%s/listings"%(str(val.skuAtFlipkart))
2221 
#        r = requests.get(request_url, auth=('m2z93iskuj81qiid', '0c7ab6a5-98c0-4cdc-8be3-72c591e0add4'))
2222 
#        print "Inventory info",r.json()
2223 
#        stock_count = int((r.json()['attributeValues'])['stock_count'])
2224 
#    except:
2225 
#        stock_count = 0
2226 
#    finally:
2227 
#        r={}
2228 
#
2229 
    val.ourFlipkartInventory = 0
11560 kshitij.so 2230 
 
12204 kshitij.so 2231 
def threadsToSpawn(runType,itemInfo,itemPopulated):
11560 kshitij.so 2232 
    if runType == RunType.FAVOURITE:
11615 kshitij.so 2233 
        count = 0
2234 
        pool = ThreadPool(3)
2235 
        startOffset = 0
11560 kshitij.so 2236 
        endOffset = startOffset
11615 kshitij.so 2237 
        while(count<3 and endOffset<len(itemInfo)):
11560 kshitij.so 2238 
            endOffset = startOffset + 20
2239 
            if (endOffset >= len(itemInfo)):
2240 
                endOffset = len(itemInfo)
11615 kshitij.so 2241 
            print "pool offset start end count"+str(startOffset)+" "+str(endOffset)+" "+str(count)
2242 
            pool.map(populateScrapingResults,itemInfo[startOffset:endOffset])
2243 
            #t = Process(target=decideCategory,args=(itemInfo[startOffset:endOffset], scraper))
2244 
            #t = threading.Thread(target=partial(decideCategory, itemInfo[startOffset:endOffset], scraper))
11560 kshitij.so 2245 
            #t = threading.Thread(target=partial(test, startOffset, endOffset))
11615 kshitij.so 2246 
            #threads.append(t)
11560 kshitij.so 2247 
            startOffset = startOffset + 20
11615 kshitij.so 2248 
            count+=1
2249 
        #[t.start() for t in threads]
2250 
        #[t.join() for t in threads]
2251 
        #threads = []
2252 
        pool.close()
2253 
        pool.join()
11560 kshitij.so 2254 
        return endOffset
2255 
    else:
11561 kshitij.so 2256 
        count = 0
12195 kshitij.so 2257 
        pool = ThreadPool(50)
11581 kshitij.so 2258 
        startOffset = 0
11560 kshitij.so 2259 
        endOffset = startOffset
12187 kshitij.so 2260 
        while(count<1 and endOffset<len(itemInfo)):
12185 kshitij.so 2261 
            endOffset = startOffset + 50
11560 kshitij.so 2262 
            if (endOffset >= len(itemInfo)):
2263 
                endOffset = len(itemInfo)
11581 kshitij.so 2264 
            print "pool offset start end count"+str(startOffset)+" "+str(endOffset)+" "+str(count)
11571 kshitij.so 2265 
            pool.map(populateScrapingResults,itemInfo[startOffset:endOffset])
11561 kshitij.so 2266 
            #t = Process(target=decideCategory,args=(itemInfo[startOffset:endOffset], scraper))
11560 kshitij.so 2267 
            #t = threading.Thread(target=partial(decideCategory, itemInfo[startOffset:endOffset], scraper))
2268 
            #t = threading.Thread(target=partial(test, startOffset, endOffset))
11561 kshitij.so 2269 
            #threads.append(t)
12185 kshitij.so 2270 
            startOffset = startOffset + 50
11561 kshitij.so 2271 
            count+=1
2272 
        #[t.start() for t in threads]
2273 
        #[t.join() for t in threads]
2274 
        #threads = []
11581 kshitij.so 2275 
        print "terminating while"
11563 kshitij.so 2276 
        pool.close()
2277 
        pool.join()
11581 kshitij.so 2278 
        print "joining threads"
2279 
        print "returning offset******"
11560 kshitij.so 2280 
        return endOffset
2281 
 
11623 kshitij.so 2282 
def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
2283 
    if len(successfulAutoDecrease)==0 and len(successfulAutoIncrease)==0 :
2284 
        return
2285 
    xstr = lambda s: s or ""
2286 
    catalog_client = CatalogClient().get_client()
2287 
    inventory_client = InventoryClient().get_client()
2288 
    message="""<html>
2289 
            <body>
11625 kshitij.so 2290 
            <h3 style="color:red;font-weight:bold;">This is test run.Prices are not being updated by system.</h3>
11623 kshitij.so 2291 
            <h3>Auto Decrease Items</h3>
2292 
            <table border="1" style="width:100%;">
2293 
            <thead>
2294 
            <tr><th>Item Id</th>
2295 
            <th>Product Name</th>
2296 
            <th>Old Price</th>
2297 
            <th>New Price</th>
2298 
            <th>Old Margin</th>
2299 
            <th>New Margin</th>
11790 kshitij.so 2300 
            <th>Commission %</th>
2301 
            <th>Return Provision %</th>
11623 kshitij.so 2302 
            <th>Flipkart Inventory</th>
2303 
            <th>Sales History</th>
12158 kshitij.so 2304 
            <th>Category</th>
11623 kshitij.so 2305 
            </tr></thead>
2306 
            <tbody>"""
2307 
    for item in successfulAutoDecrease:
2308 
        it = Item.query.filter_by(id=item.item_id).one()
2309 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
2310 
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
2311 
        warehouse = inventory_client.getWarehouse(fkItem.warehouseId)
2312 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
2313 
        newMargin = round(getNewOurTp(mpItem,item.proposedSellingPrice) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice))
2314 
        message+="""<tr>
2315 
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
2316 
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
2317 
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
2318 
                <td style="text-align:center">"""+str(math.ceil(item.proposedSellingPrice))+"""</td>
2319 
                <td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
2320 
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/item.proposedSellingPrice)*100,1))+"%)"+"""</td>
11806 kshitij.so 2321 
                <td style="text-align:center">"""+str(mpItem.commission)+" %"+"""</td>
11790 kshitij.so 2322 
                <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
11806 kshitij.so 2323 
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
11623 kshitij.so 2324 
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
12158 kshitij.so 2325 
                <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(item.competitiveCategory))+"""</td>
11623 kshitij.so 2326 
                </tr>"""
2327 
    message+="""</tbody></table><h3>Auto Increase Items</h3><table border="1" style="width:100%;">
2328 
            <thead>
2329 
            <tr><th>Item Id</th>
2330 
            <th>Product Name</th>
2331 
            <th>Old Price</th>
2332 
            <th>New Price</th>
2333 
            <th>Old Margin</th>
2334 
            <th>New Margin</th>
11790 kshitij.so 2335 
            <th>Commission %</th>
2336 
            <th>Return Provision %</th>
11623 kshitij.so 2337 
            <th>Flipkart Inventory</th>
2338 
            <th>Sales History</th>
12158 kshitij.so 2339 
            <th>Category</th>
11623 kshitij.so 2340 
            </tr></thead>
2341 
            <tbody>"""
2342 
    for item in successfulAutoIncrease:
2343 
        it = Item.query.filter_by(id=item.item_id).one()
2344 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
2345 
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
2346 
        warehouse = inventory_client.getWarehouse(fkItem.warehouseId)
2347 
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
2348 
        newMargin = round(getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
2349 
        message+="""<tr>
2350 
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
2351 
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
2352 
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
2353 
                <td style="text-align:center">"""+str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))+"""</td>
2354 
                <td style="text-align:center">"""+str(round((item.margin),1))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
2355 
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))*100,1))+"%)"+"""</td>
11806 kshitij.so 2356 
                <td style="text-align:center">"""+str(mpItem.commission)+" %"+"""</td>
11790 kshitij.so 2357 
                <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
11623 kshitij.so 2358 
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
2359 
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
12158 kshitij.so 2360 
                <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(item.competitiveCategory))+"""</td>
11623 kshitij.so 2361 
                </tr>"""
2362 
    message+="""</tbody></table></body></html>"""
2363 
    print message
2364 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
2365 
    mailServer.ehlo()
2366 
    mailServer.starttls()
2367 
    mailServer.ehlo()
2368 
 
12181 kshitij.so 2369 
    recipients = ['kshitij.sood@saholic.com']
2370 
    #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 2371 
    msg = MIMEMultipart()
2372 
    msg['Subject'] = "Flipkart Auto Pricing" + ' - ' + str(datetime.now())
2373 
    msg['From'] = ""
2374 
    msg['To'] = ",".join(recipients)
2375 
    msg.preamble = "Flipkart Auto Pricing" + ' - ' + str(datetime.now())
2376 
    html_msg = MIMEText(message, 'html')
2377 
    msg.attach(html_msg)
2378 
    try:
2379 
        mailServer.login("build@shop2020.in", "cafe@nes")
2380 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
2381 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
2382 
    except Exception as e:
2383 
        print e
2384 
        print "Unable to send pricing mail.Lets try with local SMTP."
2385 
        smtpServer = smtplib.SMTP('localhost')
2386 
        smtpServer.set_debuglevel(1)
11779 kshitij.so 2387 
        sender = 'build@shop2020.in'
11623 kshitij.so 2388 
        try:
2389 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2390 
            print "Successfully sent email"
2391 
        except:
2392 
            print "Error: unable to send email."
2393 
 
2394 
def processLostBuyBoxItems(previousProcessingTimestamp,currentTimestamp):
2395 
    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()
2396 
    cant_compete = session.query(MarketPlaceHistory.item_id).filter(MarketPlaceHistory.timestamp==currentTimestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
2397 
    if previous_buy_box is None:
2398 
        print "No item in buy box for last run"
2399 
        return
2400 
    lost_buy_box = list(set(list(zip(*previous_buy_box)[0]))&set(list(zip(*cant_compete)[0])))
2401 
    if len(lost_buy_box)==0:
2402 
        return
2403 
    xstr = lambda s: s or ""
2404 
    message="""<html>
2405 
            <body>
2406 
            <h3>Lost Buy Box</h3>
2407 
            <table border="1" style="width:100%;">
2408 
            <thead>
2409 
            <tr><th>Item Id</th>
2410 
            <th>Product Name</th>
2411 
            <th>Current Price</th>
2412 
            <th>Current Margin</th>
11844 kshitij.so 2413 
            <th>Lowest Seller</th>
2414 
            <th>Lowest Selling Price</th>
2415 
            <th>Preffered Seller</th>
2416 
            <th>Preffered Selling Price</th>
11623 kshitij.so 2417 
            <th>NLC</th>
2418 
            <th>Target NLC</th>
11790 kshitij.so 2419 
            <th>Commission %</th>
2420 
            <th>Return Provision %</th>
11623 kshitij.so 2421 
            <th>Flipkart Inventory</th>
2422 
            <th>Total Inventory</th>
2423 
            <th>Sales History</th>
2424 
            </tr></thead>
2425 
            <tbody>"""
2426 
    items = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==currentTimestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.item_id.in_(lost_buy_box)).all()
2427 
    for item in items:
2428 
        it = Item.query.filter_by(id=item.item_id).one()
11790 kshitij.so 2429 
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
11623 kshitij.so 2430 
        netInventory=''
2431 
        if not inventoryMap.has_key(item.item_id):
2432 
            netInventory='Info Not Available'
2433 
        else:
2434 
            netInventory = str(getNetAvailability(inventoryMap.get(item.item_id)))
2435 
        message+="""<tr>
2436 
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
2437 
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
2438 
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
2439 
                <td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
11844 kshitij.so 2440 
                <td style="text-align:center">"""+str(item.lowestSellerName)+"""</td>
2441 
                <td style="text-align:center">"""+str(item.lowestSellingPrice)+"""</td>
2442 
                <td style="text-align:center">"""+str(item.prefferedSellerName)+"""</td>
2443 
                <td style="text-align:center">"""+str(item.prefferedSellerSellingPrice)+"""</td>
11623 kshitij.so 2444 
                <td style="text-align:center">"""+str(item.ourNlc)+"""</td>
2445 
                <td style="text-align:center">"""+str(item.targetNlc)+"""</td>
11790 kshitij.so 2446 
                <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
2447 
                <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
11623 kshitij.so 2448 
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
2449 
                <td style="text-align:center">"""+netInventory+"""</td>
2450 
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
2451 
                </tr>"""
2452 
    message+="""</tbody></table></body></html>"""
2453 
    print message
2454 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
2455 
    mailServer.ehlo()
2456 
    mailServer.starttls()
2457 
    mailServer.ehlo()
2458 
 
11791 kshitij.so 2459 
    #recipients = ['kshitij.sood@saholic.com']
2460 
    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 2461 
    msg = MIMEMultipart()
2462 
    msg['Subject'] = "Flipkart Lost Buy Box" + ' - ' + str(datetime.now())
2463 
    msg['From'] = ""
2464 
    msg['To'] = ",".join(recipients)
2465 
    msg.preamble = "Flipkart Lost Buy Box" + ' - ' + str(datetime.now())
2466 
    html_msg = MIMEText(message, 'html')
2467 
    msg.attach(html_msg)
2468 
    try:
2469 
        mailServer.login("build@shop2020.in", "cafe@nes")
2470 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
2471 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
2472 
    except Exception as e:
2473 
        print e
2474 
        print "Unable to send lost buy box mail.Lets try local SMTP"
2475 
        smtpServer = smtplib.SMTP('localhost')
2476 
        smtpServer.set_debuglevel(1)
11779 kshitij.so 2477 
        sender = 'build@shop2020.in'
11623 kshitij.so 2478 
        try:
2479 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2480 
            print "Successfully sent email"
2481 
        except:
2482 
            print "Error: unable to send email."
2483 
 
2484 
def cheapButNotPrefAlert(timestamp):
11626 kshitij.so 2485 
    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 2486 
    if len(cheap_but_not_pref)==0:
11623 kshitij.so 2487 
        return
2488 
    xstr = lambda s: s or ""
2489 
    message="""<html>
2490 
            <body>
2491 
            <h3>Cheap But Not Preferred</h3>
2492 
            <table border="1" style="width:100%;">
2493 
            <thead>
2494 
            <tr><th>Item Id</th>
2495 
            <th>Product Name</th>
2496 
            <th>Current Price</th>
11670 kshitij.so 2497 
            <th>Our Rating</th>
2498 
            <th>Our Shipping Time</th>
11623 kshitij.so 2499 
            <th>Preffered Seller</th>
2500 
            <th>Preffered Seller SP</th>
11670 kshitij.so 2501 
            <th>Preffered Seller Rating</th>
2502 
            <th>Preffered Seller Shipping Time</th>
2503 
            <th>Price Variance %</th>
11792 kshitij.so 2504 
            <th>Commission %</th>
2505 
            <th>Return Provision %</th>
11623 kshitij.so 2506 
            <th>Flipkart Inventory</th>
2507 
            <th>Total Inventory</th>
2508 
            <th>Sales History</th>
2509 
            </tr></thead>
2510 
            <tbody>"""
2511 
    for item in cheap_but_not_pref:
2512 
        mpHistory = item[0]
2513 
        catItem = item[1]
2514 
        netInventory=''
2515 
        if not inventoryMap.has_key(mpHistory.item_id):
2516 
            netInventory='Info Not Available'
2517 
        else:
2518 
            netInventory = str(getNetAvailability(inventoryMap.get(mpHistory.item_id)))
11670 kshitij.so 2519 
        ourSt = mpHistory.ourShippingTime.split('-')
2520 
        pfSt = mpHistory.prefferedSellerShippingTime.split('-')
11792 kshitij.so 2521 
        mpItem = MarketplaceItems.get_by(itemId=mpHistory.item_id,source=OrderSource.FLIPKART)
11670 kshitij.so 2522 
        if mpHistory.prefferedSellerName=='WS Retail' and mpHistory.ourRating > mpHistory.prefferedSellerRating and int(ourSt[0])<=int(pfSt[0]):
11623 kshitij.so 2523 
            style="""background-color:red;\""""
2524 
        else:
2525 
            style="\""
11754 kshitij.so 2526 
        message+="""<tr>
2527 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.item_id)+"""</td>
2528 
            <td style="text-align:center;"""+str(style)+""">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
2529 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourSellingPrice)+"""</td>
2530 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourRating)+"""</td>
2531 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourShippingTime)+"""</td>
2532 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerName)+"""</td>
2533 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerSellingPrice)+"""</td>
2534 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerRating)+"""</td>
2535 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerShippingTime)+"""</td>
2536 
            <td style="text-align:center;"""+str(style)+""">"""+str(round(((mpHistory.prefferedSellerSellingPrice-mpHistory.ourSellingPrice)/mpHistory.ourSellingPrice)*100))+"%"+"""</td>
11806 kshitij.so 2537 
            <td style="text-align:center">"""+str(mpItem.commission)+" %"+"""</td>
11792 kshitij.so 2538 
            <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
11754 kshitij.so 2539 
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourInventory)+"""</td>
2540 
            <td style="text-align:center;"""+str(style)+""">"""+netInventory+"""</td>
2541 
            <td style="text-align:center;"""+str(style)+""">"""+getOosString((itemSaleMap.get(mpHistory.item_id))[1])+"""</td>
2542 
            </tr>"""
11623 kshitij.so 2543 
    message+="""</tbody></table></body></html>"""
2544 
    print message
2545 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
2546 
    mailServer.ehlo()
2547 
    mailServer.starttls()
2548 
    mailServer.ehlo()
2549 
 
12181 kshitij.so 2550 
    recipients = ['kshitij.sood@saholic.com']
2551 
    #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 2552 
    msg = MIMEMultipart()
2553 
    msg['Subject'] = "Flipkart Cheap But Not In BuyBox Items" + ' - ' + str(datetime.now())
2554 
    msg['From'] = ""
2555 
    msg['To'] = ",".join(recipients)
2556 
    msg.preamble = "Flipkart Cheap But Not In BuyBox Items" + ' - ' + str(datetime.now())
2557 
    html_msg = MIMEText(message, 'html')
2558 
    msg.attach(html_msg)
2559 
    try:
2560 
        mailServer.login("build@shop2020.in", "cafe@nes")
2561 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
2562 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
2563 
    except Exception as e:
2564 
        print e
2565 
        print "Unable to send Flipkart Cheap But Not In BuyBox Items mail.Lets try local SMTP"
2566 
        smtpServer = smtplib.SMTP('localhost')
2567 
        smtpServer.set_debuglevel(1)
11779 kshitij.so 2568 
        sender = 'build@shop2020.in'
11623 kshitij.so 2569 
        try:
2570 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2571 
            print "Successfully sent email"
2572 
        except:
2573 
            print "Error: unable to send email."
11754 kshitij.so 2574 
 
2575 
def sendPricingMismatch(timestamp):
2576 
    xstr = lambda s: s or ""
2577 
    message="""<html>
2578 
            <body>
2579 
            <h3>Flipkart Pricing Mismatch</h3>
2580 
            <table border="1" style="width:100%;">
2581 
            <thead>
2582 
            <tr><th>Item Id</th>
2583 
            <th>Product Name</th>
2584 
            <th>Our System Price</th>
2585 
            <th>Flipkart Price</th>
2586 
            <th>Flipkart Inventory</th>
2587 
            <th>Total Inventory</th>
2588 
            <th>Sales History</th>
2589 
            </tr></thead>
2590 
            <tbody>"""
2591 
    flipkartPricing = {}
2592 
    saholicPricing = {}
2593 
    mpHistoryItems = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).all()
2594 
    for val in mpHistoryItems:
2595 
        temp = []
2596 
        temp.append(val[0].ourSellingPrice)
2597 
        temp.append(xstr(val[1].brand)+" "+xstr(val[1].model_name)+" "+xstr(val[1].model_number)+" "+xstr(val[1].color))
11755 kshitij.so 2598 
        temp.append(val[0].ourInventory)
11754 kshitij.so 2599 
        flipkartPricing[val[0].item_id] = temp
2600 
    mpHistoryItems[:] = []
2601 
    mpItems = session.query(MarketplaceItems).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
2602 
    for val in mpItems:
2603 
        saholicPricing[val.itemId] = val.currentSp
2604 
    mpItems[:] = []
2605 
    mismatches = []
2606 
    for k,v in flipkartPricing.iteritems():
2607 
        flipkartSellingPrice = v[0]
2608 
        ourSellingPrice = saholicPricing.get(k)
11771 kshitij.so 2609 
        if flipkartSellingPrice is not None and not((ourSellingPrice - flipkartSellingPrice >= -3) and (ourSellingPrice - flipkartSellingPrice <=3)):
11754 kshitij.so 2610 
            mismatches.append(k)
2611 
    print "mismatches are ",mismatches
11755 kshitij.so 2612 
    if len(mismatches)==0:
2613 
        return
11754 kshitij.so 2614 
    for item in mismatches:
2615 
        netInventory=''
2616 
        if not inventoryMap.has_key(item):
2617 
            netInventory='Info Not Available'
2618 
        else:
2619 
            netInventory = str(getNetAvailability(inventoryMap.get(item)))
2620 
        message+="""<tr>
11756 kshitij.so 2621 
            <td style="text-align:center">"""+str(item)+"""</td>
2622 
            <td style="text-align:center">"""+str((flipkartPricing.get(item))[1])+"""</td>
2623 
            <td style="text-align:center">"""+str(saholicPricing.get(item))+"""</td>
2624 
            <td style="text-align:center">"""+str((flipkartPricing.get(item))[0])+"""</td>
2625 
            <td style="text-align:center">"""+str((flipkartPricing.get(item))[2])+"""</td>
2626 
            <td style="text-align:center">"""+netInventory+"""</td>
2627 
            <td style="text-align:center">"""+getOosString((itemSaleMap.get(item))[1])+"""</td>
11754 kshitij.so 2628 
            </tr>"""
2629 
    message+="""</tbody></table></body></html>"""
2630 
    print message
2631 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
2632 
    mailServer.ehlo()
2633 
    mailServer.starttls()
2634 
    mailServer.ehlo()
2635 
 
12181 kshitij.so 2636 
    recipients = ['kshitij.sood@saholic.com']
2637 
    #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 2638 
    msg = MIMEMultipart()
2639 
    msg['Subject'] = "Flipkart Price Mismatch" + ' - ' + str(datetime.now())
2640 
    msg['From'] = ""
2641 
    msg['To'] = ",".join(recipients)
2642 
    msg.preamble = "Flipkart Price Mismatch" + ' - ' + str(datetime.now())
2643 
    html_msg = MIMEText(message, 'html')
2644 
    msg.attach(html_msg)
2645 
    try:
2646 
        mailServer.login("build@shop2020.in", "cafe@nes")
2647 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
2648 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
2649 
    except Exception as e:
2650 
        print e
2651 
        print "Unable to send Flipkart Price Mismatch mail.Lets try local SMTP"
2652 
        smtpServer = smtplib.SMTP('localhost')
2653 
        smtpServer.set_debuglevel(1)
11779 kshitij.so 2654 
        sender = 'build@shop2020.in'
11754 kshitij.so 2655 
        try:
2656 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2657 
            print "Successfully sent email"
2658 
        except:
2659 
            print "Error: unable to send email."
11775 kshitij.so 2660 
 
2661 
def sendAlertForNegativeMargins(timestamp):
2662 
    xstr = lambda s: s or ""
2663 
    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()
2664 
    if len(negativeMargins) == 0:
2665 
        return
2666 
    message="""<html>
2667 
            <body>
2668 
            <h3 style="color:red;font-weight:bold;">Flipkart Negative Margins</h3>
2669 
            <table border="1" style="width:100%;">
2670 
            <thead>
2671 
            <tr><th>Item Id</th>
2672 
            <th>Product Name</th>
2673 
            <th>SP</th>
2674 
            <th>TP</th>
2675 
            <th>Lowest Possible SP</th>
2676 
            <th>Lowest Possible TP</th>
2677 
            <th>Margin</th>
2678 
            <th>Margin %</th>
11790 kshitij.so 2679 
            <th>Commission %</th>
2680 
            <th>Return Provision %</th>
11775 kshitij.so 2681 
            <th>Flipkart Inventory</th>
2682 
            <th>Total Inventory</th>
2683 
            <th>Sales History</th>
2684 
            </tr></thead>
2685 
            <tbody>"""
2686 
    for item in negativeMargins:
2687 
        mpHistory = item[0]
2688 
        catItem = item[1]
2689 
        netInventory=''
11776 kshitij.so 2690 
        if not inventoryMap.has_key(mpHistory.item_id):
11775 kshitij.so 2691 
            netInventory='Info Not Available'
2692 
        else:
11776 kshitij.so 2693 
            netInventory = str(getNetAvailability(inventoryMap.get(mpHistory.item_id)))
11790 kshitij.so 2694 
        mpItem = MarketplaceItems.get_by(itemId=mpHistory.item_id,source=OrderSource.FLIPKART)
11775 kshitij.so 2695 
        message+="""<tr>
2696 
            <td style="text-align:center">"""+str(mpHistory.item_id)+"""</td>
2697 
            <td style="text-align:center">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
2698 
            <td style="text-align:center">"""+str(mpHistory.ourSellingPrice)+"""</td>
2699 
            <td style="text-align:center">"""+str(mpHistory.ourTp)+"""</td>
2700 
            <td style="text-align:center">"""+str(mpHistory.lowestPossibleSp)+"""</td>
2701 
            <td style="text-align:center">"""+str(mpHistory.lowestPossibleTp)+"""</td>
2702 
            <td style="text-align:center">"""+str(mpHistory.margin)+"""</td>
11952 kshitij.so 2703 
            <td style="text-align:center">"""+str(round((mpHistory.margin/mpHistory.ourSellingPrice)*100,1))+" %"+"""</td>
11790 kshitij.so 2704 
            <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
2705 
            <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
11775 kshitij.so 2706 
            <td style="text-align:center">"""+str(mpHistory.ourInventory)+"""</td>
2707 
            <td style="text-align:center">"""+netInventory+"""</td>
11776 kshitij.so 2708 
            <td style="text-align:center">"""+getOosString((itemSaleMap.get(mpHistory.item_id))[1])+"""</td>
11775 kshitij.so 2709 
            </tr>"""
2710 
    message+="""</tbody></table></body></html>"""
2711 
    print message
2712 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
2713 
    mailServer.ehlo()
2714 
    mailServer.starttls()
2715 
    mailServer.ehlo()
2716 
 
12181 kshitij.so 2717 
    recipients = ['kshitij.sood@saholic.com']
2718 
    #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 2719 
    msg = MIMEMultipart()
2720 
    msg['Subject'] = "Flipkart Negative Margin" + ' - ' + str(datetime.now())
2721 
    msg['From'] = ""
2722 
    msg['To'] = ",".join(recipients)
2723 
    msg.preamble = "Flipkart Negative Margin" + ' - ' + str(datetime.now())
2724 
    html_msg = MIMEText(message, 'html')
2725 
    msg.attach(html_msg)
2726 
    try:
2727 
        mailServer.login("build@shop2020.in", "cafe@nes")
2728 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
2729 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
2730 
    except Exception as e:
2731 
        print e
2732 
        print "Unable to send Flipkart Negative margin mail.Lets try local SMTP"
2733 
        smtpServer = smtplib.SMTP('localhost')
2734 
        smtpServer.set_debuglevel(1)
11779 kshitij.so 2735 
        sender = 'build@shop2020.in'
11775 kshitij.so 2736 
        try:
2737 
            smtpServer.sendmail(sender, recipients, msg.as_string())
2738 
            print "Successfully sent email"
2739 
        except:
2740 
            print "Error: unable to send email."
11560 kshitij.so 2741 
 
11623 kshitij.so 2742 
 
11775 kshitij.so 2743 
 
2744 
 
11193 kshitij.so 2745 
def main():
2746 
    parser = optparse.OptionParser()
2747 
    parser.add_option("-t", "--type", dest="runType",
2748 
                   default="FULL", type="string",
2749 
                   help="Run type FULL or FAVOURITE")
2750 
    (options, args) = parser.parse_args()
2751 
    if options.runType not in ('FULL','FAVOURITE'):
2752 
        print "Run type argument illegal."
2753 
        sys.exit(1)
2754 
    timestamp = datetime.now()
11581 kshitij.so 2755 
    previousProcessingTimestamp = session.query(func.max(MarketPlaceHistory.timestamp)).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).one()
11193 kshitij.so 2756 
    itemInfo= populateStuff(options.runType,timestamp)
11560 kshitij.so 2757 
    itemsPopulated = 0
11615 kshitij.so 2758 
    while (len(itemInfo)>0):
12204 kshitij.so 2759 
        itemsPopulated = threadsToSpawn(options.runType,itemInfo,itemsPopulated)
11581 kshitij.so 2760 
        cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap = decideCategory(itemInfo[0:itemsPopulated])
2761 
        itemInfo[0:itemsPopulated] = []
2762 
        commitExceptionList(exceptionItems,timestamp)
2763 
        commitCantCompete(cantCompete,timestamp)
2764 
        commitBuyBox(buyBoxItems,timestamp)
2765 
        commitCompetitive(competitive,timestamp)
2766 
        commitCompetitiveNoInventory(competitiveNoInventory,timestamp)
2767 
        commitNegativeMargin(negativeMargin,timestamp)
2768 
        commitCheapButNotPref(cheapButNotPref,timestamp)
2769 
        commitPrefButNotCheap(prefButNotCheap, timestamp)
2770 
        cantCompete[:], buyBoxItems[:], competitive[:], competitiveNoInventory[:], exceptionItems[:], negativeMargin[:], cheapButNotPref[:], prefButNotCheap[:] =[],[],[],[],[],[],[],[]
2771 
 
11615 kshitij.so 2772 
    successfulAutoDecrease = fetchItemsForAutoDecrease(timestamp)
2773 
    successfulAutoIncrease = fetchItemsForAutoIncrease(timestamp)
2774 
    if options.runType=='FULL':
2775 
        previousAutoFav, nowAutoFav = markAutoFavourite()
11621 kshitij.so 2776 
    if options.runType =='FULL':
2777 
        write_report(previousAutoFav,nowAutoFav,timestamp,options.runType)
2778 
    else:
2779 
        write_report(None,None,timestamp,options.runType)
11623 kshitij.so 2780 
    sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease)
11625 kshitij.so 2781 
    if previousProcessingTimestamp[0] is not None:
2782 
        processLostBuyBoxItems(previousProcessingTimestamp[0],timestamp)
11623 kshitij.so 2783 
    if options.runType=='FULL':
2784 
        cheapButNotPrefAlert(timestamp)
11754 kshitij.so 2785 
        sendPricingMismatch(timestamp)
11775 kshitij.so 2786 
        sendAlertForNegativeMargins(timestamp)
11193 kshitij.so 2787 
 
2788 
if __name__ == '__main__':
2789 
    main()