Subversion Repositories SmartDukaan

Rev

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

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