Subversion Repositories SmartDukaan

from elixir import *
from sqlalchemy.sql import or_ ,func, asc
from shop2020.config.client.ConfigClient import ConfigClient
from shop2020.model.v1.catalog.impl import DataService
from shop2020.model.v1.catalog.script import FlipkartScraper
from shop2020.model.v1.catalog.impl.DataService import FlipkartItem, MarketplaceItems, Item, \
Category, SourcePercentageMaster, MarketPlaceHistory, MarketPlaceUpdateHistory, MarketPlaceItemPrice, \
SourceCategoryPercentage, SourceItemPercentage, SourceReturnPercentage
from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
from shop2020.thriftpy.model.v1.catalog.ttypes import CompetitionCategory, SalesPotential,\
Decision, RunType
from shop2020.clients.CatalogClient import CatalogClient
from shop2020.clients.InventoryClient import InventoryClient
import urllib2
import requests
import time
from datetime import date, datetime, timedelta
from shop2020.utils import EmailAttachmentSender
from shop2020.utils.EmailAttachmentSender import get_attachment_part
import math
from operator import itemgetter
from functools import partial
import simplejson as json
import xlwt
import optparse
import sys
import smtplib
import threading
from multiprocessing import Process 
from email.mime.text import MIMEText
import email
from email.mime.multipart import MIMEMultipart
import email.encoders
import cookielib
from multiprocessing import Pool
from multiprocessing.dummy import Pool as ThreadPool 
import gc
import mechanize

config_client = ConfigClient()
host = config_client.get_property('staging_hostname')
syncPrice=config_client.get_property('sync_price_on_marketplace')


DataService.initialize(db_hostname=host)

inventoryMap = {}
itemSaleMap = {}
categoryMap = {}

class __FlipkartDetails:
    
    def __init__(self,rank ,ourSp , secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, shippingTimeLowerLimitLowestSeller,shippingTimeUpperLimitLowestSeller, \
    shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller, shippingTimeLowerLimitOur, shippingTimeUpperLimitOur, shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller, totalAvailableSeller, lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
    ourBuyTrend, prefSellerBuyTrend, secondLowestSellerBuyTrend, ourCode ):
    
        self.rank = rank
        self.ourSp = ourSp
        self.secondLowestSellerSp = secondLowestSellerSp
        self.prefSellerSp = prefSellerSp
        self.lowestSellerSp = lowestSellerSp
        self.lowestSellerScore = lowestSellerScore
        self.prefSellerScore = prefSellerScore
        self.secondLowestSellerScore = secondLowestSellerScore
        self.ourScore = ourScore
        self.shippingTimeLowerLimitLowestSeller = shippingTimeLowerLimitLowestSeller
        self.shippingTimeUpperLimitLowestSeller = shippingTimeUpperLimitLowestSeller
        self.shippingTimeLowerLimitPrefSeller = shippingTimeLowerLimitPrefSeller
        self.shippingTimeUpperLimitPrefSeller = shippingTimeUpperLimitPrefSeller
        self.shippingTimeLowerLimitOur = shippingTimeLowerLimitOur
        self.shippingTimeUpperLimitOur = shippingTimeUpperLimitOur
        self.shippingTimeLowerLimitSecondLowestSeller = shippingTimeLowerLimitSecondLowestSeller
        self.shippingTimeUpperLimitSecondLowestSeller = shippingTimeUpperLimitSecondLowestSeller
        self.totalAvailableSeller = totalAvailableSeller
        self.lowestSellerName = lowestSellerName
        self.lowestSellerCode = lowestSellerCode
        self.secondLowestSellerName = secondLowestSellerName
        self.secondLowestSellerCode = secondLowestSellerCode
        self.prefSellerName = prefSellerName
        self.prefSellerCode = prefSellerCode
        self.lowestSellerBuyTrend = lowestSellerBuyTrend
        self.ourBuyTrend = ourBuyTrend
        self.prefSellerBuyTrend = prefSellerBuyTrend
        self.secondLowestSellerBuyTrend = secondLowestSellerBuyTrend
        self.ourCode = ourCode

class __FlipkartItemInfo:
    
    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):
        
        self.fkSerialNumber = fkSerialNumber
        self.nlc = nlc
        self.courierCost = courierCost
        self.item_id = item_id
        self.product_group = product_group
        self.brand = brand
        self.model_name = model_name
        self.model_number = model_number
        self.color = color
        self.weight = weight
        self.parent_category = parent_category
        self.risky = risky
        self.warehouseId = warehouseId
        self.vatRate = vatRate
        self.runType = runType
        self.parent_category_name = parent_category_name
        self.sourcePercentage = sourcePercentage
        self.ourFlipkartInventory = ourFlipkartInventory
        self.skuAtFlipkart = skuAtFlipkart
        self.flipkartDetails = flipkartDetails
        self.stateId = stateId  

class __FlipkartPricing:
    
    def __init__(self, ourSp, ourTp, lowestTp, lowestPossibleTp, secondLowestSellerTp, lowestPossibleSp, prefSellerTp):
        self.ourTp = ourTp
        self.lowestTp = lowestTp
        self.lowestPossibleTp = lowestPossibleTp
        self.ourSp = ourSp
        self.secondLowestSellerTp = secondLowestSellerTp
        self.lowestPossibleSp = lowestPossibleSp
        self.prefSellerTp = prefSellerTp

def getBrowserObject():
    br = mechanize.Browser(factory=mechanize.RobustFactory())
    cj = cookielib.LWPCookieJar()
    br.set_cookiejar(cj)
    br.set_handle_equiv(True)
    br.set_handle_redirect(True)
    br.set_handle_referer(True)
    br.set_handle_robots(False)
    br.set_debug_http(False)
    br.set_debug_redirects(False)
    br.set_debug_responses(False)
    
    br.set_handle_refresh(mechanize._http.HTTPRefreshProcessor(), max_time=1)
    
    br.addheaders = [('User-agent','Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11'),
                     ('Accept', 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8'),
                     ('Accept-Encoding', 'gzip,deflate,sdch'),                  
                     ('Accept-Language', 'en-US,en;q=0.8'),                     
                     ('Accept-Charset', 'ISO-8859-1,utf-8;q=0.7,*;q=0.3')]
    return br

def markReasonForMpItem(mpHistory,reason,decision):
    mpHistory.decision = decision
    mpHistory.reason = reason

def fetchItemsForAutoDecrease(time):
    successfulAutoDecrease = []
    autoDecrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(or_(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE,MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP ))\
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketplaceItems.autoDecrement==True).all()
    inventory_client = InventoryClient().get_client()
    global inventoryMap
    inventoryMap = inventory_client.getInventorySnapshot(0)
    for autoDecrementItem in autoDecrementItems:
#        if not autoDecrementItem.risky:
#            markReasonForMpItem(autoDecrementItem,'Item is not risky',Decision.AUTO_DECREMENT_FAILED)
#            continue
        if math.ceil(autoDecrementItem.proposedSellingPrice) >= autoDecrementItem.ourSellingPrice:
            markReasonForMpItem(autoDecrementItem,'Proposed SP greater than or equal to current SP',Decision.AUTO_DECREMENT_FAILED)
            continue
        if autoDecrementItem.proposedSellingPrice < autoDecrementItem.lowestPossibleSp:
            markReasonForMpItem(autoDecrementItem,'Proposed SP less than lowest possible SP',Decision.AUTO_DECREMENT_FAILED)
            continue
        if autoDecrementItem.competitiveCategory == CompetitionCategory.PREF_BUT_NOT_CHEAP:
            avgSaleLastTwoDay = (itemSaleMap.get(autoDecrementItem.item_id))[6]
            if avgSaleLastTwoDay >= .5:
                markReasonForMpItem(autoDecrementItem,'Last two day avg sale is greater than 2',Decision.AUTO_DECREMENT_FAILED)
                continue
        totalAvailability, totalReserved = 0,0
        if autoDecrementItem.risky:
            if (not inventoryMap.has_key(autoDecrementItem.item_id)):
                markReasonForMpItem(autoDecrementItem,'Inventory info not available',Decision.AUTO_DECREMENT_FAILED)
                continue
            itemInventory=inventoryMap[autoDecrementItem.item_id]
            availableMap  = itemInventory.availability
            reserveMap = itemInventory.reserved
            for warehouse,availability in availableMap.iteritems():
                if warehouse==16 or warehouse==1771:
                    continue
                totalAvailability = totalAvailability+availability
            for warehouse,reserve in reserveMap.iteritems():
                if warehouse==16 or warehouse==1771:
                    continue
                totalReserved = totalReserved+reserve
            if (totalAvailability-totalReserved)<=0:
                markReasonForMpItem(autoDecrementItem,'Net availability is 0',Decision.AUTO_DECREMENT_FAILED)
                continue
            avgSalePerDay = (itemSaleMap.get(autoDecrementItem.item_id))[2]
            try:
                daysOfStock = (float(totalAvailability-totalReserved))/avgSalePerDay
            except ZeroDivisionError,e:
                daysOfStock = float("inf")
            if daysOfStock<2 and autoDecrementItem.risky:
                markReasonForMpItem(autoDecrementItem,'Our stock is not enough',Decision.AUTO_DECREMENT_FAILED)
                continue

        autoDecrementItem.ourEnoughStock = True
        autoDecrementItem.decision = Decision.AUTO_DECREMENT_SUCCESS
        autoDecrementItem.reason = 'All conditions for auto decrement true'
        successfulAutoDecrease.append(autoDecrementItem)
    session.commit()
    return successfulAutoDecrease

def fetchItemsForAutoIncrease(time):
    successfulAutoIncrease = []
    autoIncrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX)\
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketplaceItems.autoIncrement==True).all()
    for autoIncrementItem in autoIncrementItems:
        if not autoIncrementItem.competitiveCategory == CompetitionCategory.BUY_BOX:
            markReasonForMpItem(autoIncrementItem,'Category is '+CompetitionCategory._VALUES_TO_NAMES.get(autoIncrementItem.competitiveCategory),Decision.AUTO_INCREMENT_FAILED)
            continue
        if autoIncrementItem.totalSeller==1 and autoIncrementItem.ourRank==1:
            markReasonForMpItem(autoIncrementItem,'We are the only seller',Decision.AUTO_INCREMENT_FAILED)
            continue
        if autoIncrementItem.proposedSellingPrice <= autoIncrementItem.ourSellingPrice:
            markReasonForMpItem(autoIncrementItem,'Proposed SP less than current SP',Decision.AUTO_INCREMENT_FAILED)
            continue
        if autoIncrementItem.proposedSellingPrice >=10000 and autoIncrementItem.ourSellingPrice<10000:
            markReasonForMpItem(autoIncrementItem,'Proposed SP is greater than 10,000 and current sp is less than 10,000',Decision.AUTO_INCREMENT_FAILED)
            continue
        if getLastDaySale(autoIncrementItem.item_id)<=2:
            markReasonForMpItem(autoIncrementItem,'Last day sale is less than 3',Decision.AUTO_INCREMENT_FAILED)
            continue
        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()
        try:
            if antecedentPrice[0] is not None:
                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:
                    markReasonForMpItem(autoIncrementItem,'Maximum price increase in last 24 hours should be 2%',Decision.AUTO_INCREMENT_FAILED)
                    continue
        except:
            if antecedentPrice is not None:
                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:
                    markReasonForMpItem(autoIncrementItem,'Maximum price increase in last 24 hours should be 2%',Decision.AUTO_INCREMENT_FAILED)
                    continue
        mpItem = MarketplaceItems.get_by(itemId=autoIncrementItem.item_id,source=OrderSource.FLIPKART)
        if mpItem.maximumSellingPrice is not None and mpItem.maximumSellingPrice > 0:
            if autoIncrementItem.ourSellingPrice+max(10,.01*autoIncrementItem.ourSellingPrice) > mpItem.maximumSellingPrice:
                markReasonForMpItem(autoIncrementItem,'Price cannot exceed Maximum Selling Price',Decision.AUTO_INCREMENT_FAILED)
                continue
        #oosStatus = inventory_client.getOosStatusesForXDaysForItem(autoIncrementItem.item_id,0,3)
        #count,sale,daysOfStock = 0,0,0
        #for obj in oosStatus:
        #    if not obj.is_oos:
        #        count+=1
        #        sale = sale+obj.num_orders
        #avgSalePerDay=0 if count==0 else (float(sale)/count)
        totalAvailability, totalReserved = 0,0
        if autoIncrementItem.risky:
            if (not inventoryMap.has_key(autoIncrementItem.item_id)):
                markReasonForMpItem(autoIncrementItem,'Inventory info not available',Decision.AUTO_INCREMENT_FAILED)
                continue
            itemInventory=inventoryMap[autoIncrementItem.item_id]
            availableMap  = itemInventory.availability
            reserveMap = itemInventory.reserved
            for warehouse,availability in availableMap.iteritems():
                if warehouse==16 or warehouse==1771:
                    continue
                totalAvailability = totalAvailability+availability
            for warehouse,reserve in reserveMap.iteritems():
                if warehouse==16 or warehouse==1771:
                    continue
                totalReserved = totalReserved+reserve
            #if (totalAvailability-totalReserved)<=0:
            #    markReasonForMpItem(autoIncrementItem,'Our stock is 0',Decision.AUTO_INCREMENT_FAILED)
            #    continue
            avgSalePerDay = (itemSaleMap.get(autoIncrementItem.item_id))[2]
            if (avgSalePerDay==0):
                markReasonForMpItem(autoIncrementItem,'Average sale per day is zero',Decision.AUTO_INCREMENT_FAILED)
                continue
            daysOfStock = (float(totalAvailability-totalReserved))/avgSalePerDay
            if daysOfStock>5:
                markReasonForMpItem(autoIncrementItem,'Our stock is enough',Decision.AUTO_INCREMENT_FAILED)
                continue

        autoIncrementItem.ourEnoughStock = False
        autoIncrementItem.decision = Decision.AUTO_INCREMENT_SUCCESS
        autoIncrementItem.reason = 'All conditions for auto increment true'
        successfulAutoIncrease.append(autoIncrementItem)
    session.commit()
    return successfulAutoIncrease        


def commitExceptionList(exceptionList,timestamp):
    exceptionItems=[]
    for item in exceptionList:
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id =item.item_id
        mpHistory.source = OrderSource.FLIPKART 
        mpHistory.competitiveCategory = CompetitionCategory.EXCEPTION
        mpHistory.risky = item.risky
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(item.runType)
        exceptionItems.append(mpHistory)
    session.commit()
    return exceptionItems

def commitCantCompete(cantCompete,timestamp):
    for item in cantCompete:
        flipkartDetails = item[0]
        flipkartItemInfo = item[1]
        flipkartPricing = item[2]
        mpItem = item[3]
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id = flipkartItemInfo.item_id
        mpHistory.source = OrderSource.FLIPKART
        mpHistory.lowestTp = flipkartPricing.lowestTp
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
        mpHistory.ourRank = flipkartDetails.rank
        mpHistory.competitiveCategory = CompetitionCategory.CANT_COMPETE
        mpHistory.risky = flipkartItemInfo.risky
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
        mpHistory.lowestSellerShippingTime = ''
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
        mpHistory.ourTp = flipkartPricing.ourTp
        mpHistory.ourNlc = flipkartItemInfo.nlc
        mpHistory.ourRating = flipkartDetails.ourScore
        mpHistory.ourShippingTime = ''
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
        mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
        mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
        mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
        mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
        mpHistory.prefferedSellerShippingTime = ''
        mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
        proposed_sp = flipkartDetails.lowestSellerSp - max(10, flipkartDetails.lowestSellerSp*0.001)
        proposed_tp = getTargetTp(proposed_sp,mpItem)
        target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
        mpHistory.proposedSellingPrice = round(proposed_sp,2)
        mpHistory.proposedTp = round(proposed_tp,2)
        mpHistory.targetNlc = round(target_nlc,2)
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
    session.commit()

def commitBuyBox(buyBoxItems,timestamp):
    for item in buyBoxItems:
        flipkartDetails = item[0]
        flipkartItemInfo = item[1]
        flipkartPricing = item[2]
        mpItem = item[3]
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id = flipkartItemInfo.item_id
        mpHistory.source = OrderSource.FLIPKART
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
        mpHistory.ourRank = flipkartDetails.rank
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
        mpHistory.ourTp = flipkartPricing.ourTp
        mpHistory.ourNlc = flipkartItemInfo.nlc
        mpHistory.ourRating = flipkartDetails.ourScore
        mpHistory.ourShippingTime = ''
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        mpHistory.competitiveCategory = CompetitionCategory.BUY_BOX
        mpHistory.risky = flipkartItemInfo.risky
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
        mpHistory.lowestTp = flipkartPricing.lowestTp
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
        mpHistory.lowestSellerShippingTime = ''
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
        proposed_sp = max(flipkartDetails.secondLowestSellerSp - max((20, flipkartDetails.secondLowestSellerSp*0.002)), flipkartPricing.lowestPossibleSp)
        proposed_tp = getTargetTp(proposed_sp,mpItem)
        #target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
        mpHistory.proposedSellingPrice = round(proposed_sp,2)
        mpHistory.proposedTp = round(proposed_tp,2)
        #mpHistory.targetNlc = target_nlc
        mpHistory.secondLowestSellerName = flipkartDetails.secondLowestSellerName
        mpHistory.secondLowestSellerCode = flipkartDetails.secondLowestSellerCode
        mpHistory.secondLowestSellingPrice = flipkartDetails.secondLowestSellerSp
        mpHistory.secondLowestTp = flipkartPricing.secondLowestSellerTp
        mpHistory.secondLowestSellerRating = flipkartDetails.secondLowestSellerScore
        mpHistory.secondLowestSellerShippingTime = ''
        mpHistory.secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.marginIncreasedPotential = proposed_tp - flipkartPricing.ourTp
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
        mpHistory.timestamp = timestamp
    session.commit()

def commitCompetitiveNoInventory(competitiveNoInventory,timestamp):
    for item in competitiveNoInventory:
        flipkartDetails = item[0]
        flipkartItemInfo = item[1]
        flipkartPricing = item[2]
        mpItem = item[3]
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id = flipkartItemInfo.item_id
        mpHistory.source = OrderSource.FLIPKART
        mpHistory.lowestTp = flipkartPricing.lowestTp
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
        mpHistory.ourRank = flipkartDetails.rank
        mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE_NO_INVENTORY
        mpHistory.risky = flipkartItemInfo.risky
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
        mpHistory.lowestSellerShippingTime = ''
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
        mpHistory.ourTp = flipkartPricing.ourTp
        mpHistory.ourNlc = flipkartItemInfo.nlc
        mpHistory.ourRating = flipkartDetails.ourScore
        mpHistory.ourShippingTime = ''
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
        mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
        mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
        mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
        mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
        mpHistory.prefferedSellerShippingTime = ''
        mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
        proposed_sp = max(flipkartDetails.lowestSellerSp - max((10, flipkartDetails.lowestSellerSp*0.001)), flipkartPricing.lowestPossibleSp)
        proposed_tp = getTargetTp(proposed_sp,mpItem)
        mpHistory.proposedSellingPrice = round(proposed_sp,2)
        mpHistory.proposedTp = round(proposed_tp,2)
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
    session.commit()

def commitCompetitive(competitive,timestamp):
    for item in competitive:
        flipkartDetails = item[0]
        flipkartItemInfo = item[1]
        flipkartPricing = item[2]
        mpItem = item[3]
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id = flipkartItemInfo.item_id
        mpHistory.source = OrderSource.FLIPKART
        mpHistory.lowestTp = flipkartPricing.lowestTp
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
        mpHistory.ourRank = flipkartDetails.rank
        mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE
        mpHistory.risky = flipkartItemInfo.risky
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
        mpHistory.lowestSellerShippingTime = ''
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
        mpHistory.ourTp = flipkartPricing.ourTp
        mpHistory.ourNlc = flipkartItemInfo.nlc
        mpHistory.ourRating = flipkartDetails.ourScore
        mpHistory.ourShippingTime = ''
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
        mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
        mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
        mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
        mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
        mpHistory.prefferedSellerShippingTime = ''
        mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
        proposed_sp = max(flipkartDetails.lowestSellerSp - max((10, flipkartDetails.lowestSellerSp*0.001)), flipkartPricing.lowestPossibleSp)
        proposed_tp = getTargetTp(proposed_sp,mpItem)
        mpHistory.proposedSellingPrice = round(proposed_sp,2)
        mpHistory.proposedTp = round(proposed_tp,2)
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
    session.commit()

def commitNegativeMargin(negativeMargin,timestamp):
    for item in negativeMargin:
        flipkartDetails = item[0]
        flipkartItemInfo = item[1]
        flipkartPricing = item[2]
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id = flipkartItemInfo.item_id
        mpHistory.source = OrderSource.FLIPKART
        mpHistory.lowestTp = flipkartPricing.lowestTp
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
        mpHistory.ourRank = flipkartDetails.rank
        mpHistory.competitiveCategory = CompetitionCategory.NEGATIVE_MARGIN
        mpHistory.risky = flipkartItemInfo.risky
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
        mpHistory.lowestSellerShippingTime = ''
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
        mpHistory.ourTp = flipkartPricing.ourTp
        mpHistory.ourNlc = flipkartItemInfo.nlc
        mpHistory.ourRating = flipkartDetails.ourScore
        mpHistory.ourShippingTime = ''
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
        mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
        mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
        mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
        mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
        mpHistory.prefferedSellerShippingTime = ''
        mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
    session.commit()

def commitCheapButNotPref(cheapButNotPref,timestamp):
    for item in cheapButNotPref:
        flipkartDetails = item[0]
        flipkartItemInfo = item[1]
        flipkartPricing = item[2]
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id = flipkartItemInfo.item_id
        mpHistory.source = OrderSource.FLIPKART
        mpHistory.lowestTp = flipkartPricing.lowestTp
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
        mpHistory.ourRank = flipkartDetails.rank
        mpHistory.competitiveCategory = CompetitionCategory.CHEAP_BUT_NOT_PREF
        mpHistory.risky = flipkartItemInfo.risky
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
        mpHistory.lowestSellerShippingTime = ''
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
        mpHistory.ourTp = flipkartPricing.ourTp
        mpHistory.ourNlc = flipkartItemInfo.nlc
        mpHistory.ourRating = flipkartDetails.ourScore
        mpHistory.ourShippingTime = ''
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
        mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
        mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
        mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
        mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
        mpHistory.prefferedSellerShippingTime = ''
        mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
    session.commit()

def commitPrefButNotCheap(prefButNotCheap,timestamp):
    for item in prefButNotCheap:
        flipkartDetails = item[0]
        flipkartItemInfo = item[1]
        flipkartPricing = item[2]
        mpItem = item[3]
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id = flipkartItemInfo.item_id
        mpHistory.source = OrderSource.FLIPKART
        mpHistory.lowestTp = flipkartPricing.lowestTp
        mpHistory.lowestPossibleTp = flipkartPricing.lowestPossibleTp
        mpHistory.lowestPossibleSp = flipkartPricing.lowestPossibleSp
        mpHistory.ourInventory = flipkartItemInfo.ourFlipkartInventory
        mpHistory.ourRank = flipkartDetails.rank
        mpHistory.competitiveCategory = CompetitionCategory.PREF_BUT_NOT_CHEAP
        mpHistory.risky = flipkartItemInfo.risky
        mpHistory.lowestSellingPrice = flipkartDetails.lowestSellerSp
        mpHistory.lowestSellerName = flipkartDetails.lowestSellerName
        mpHistory.lowestSellerCode = flipkartDetails.lowestSellerCode
        mpHistory.lowestSellerRating = flipkartDetails.lowestSellerScore
        mpHistory.lowestSellerShippingTime = ''
        mpHistory.lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
        mpHistory.ourSellingPrice = flipkartPricing.ourSp
        mpHistory.ourTp = flipkartPricing.ourTp
        mpHistory.ourNlc = flipkartItemInfo.nlc
        mpHistory.ourRating = flipkartDetails.ourScore
        mpHistory.ourShippingTime = ''
        mpHistory.ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        mpHistory.prefferedSellerName = flipkartDetails.prefSellerName
        mpHistory.prefferedSellerCode = flipkartDetails.prefSellerCode
        mpHistory.prefferedSellerRating = flipkartDetails.prefSellerScore
        mpHistory.prefferedSellerSellingPrice = flipkartDetails.prefSellerSp
        mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
        mpHistory.prefferedSellerShippingTime = ''
        mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        proposed_sp = max(flipkartDetails.lowestSellerSp - max((10, flipkartDetails.lowestSellerSp*0.001)), flipkartPricing.lowestPossibleSp)
        proposed_tp = getTargetTp(proposed_sp,mpItem)
        mpHistory.proposedSellingPrice = proposed_sp
        mpHistory.proposedTp = proposed_tp  
        mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
        mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(flipkartDetails.lowestSellerSp,flipkartItemInfo.nlc))
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(flipkartItemInfo.runType)
    session.commit()

def populateStuff(runType,time):
    global itemSaleMap
    global categoryMap
    
    itemInfo = []
    if runType=='FAVOURITE':
        items = session.query(FlipkartItem,MarketplaceItems).join((MarketplaceItems,FlipkartItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.FLIPKART).\
        filter(or_(MarketplaceItems.autoFavourite==True, MarketplaceItems.manualFavourite==True)).all()
    else:
        #items = session.query(FlipkartItem,MarketplaceItems).join((MarketplaceItems,FlipkartItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
        items = session.query(FlipkartItem,MarketplaceItems).join((MarketplaceItems,FlipkartItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
        
    inventory_client = InventoryClient().get_client()
    
    for item in items:
        flipkart_item = item[0]
        mp_item = item[1]
        it = Item.query.filter_by(id=flipkart_item.item_id).one()
        print "Checking percentages for item Id ",it.id
        category = Category.query.filter_by(id=it.category).one()
        parent_category = Category.query.filter_by(id=category.parent_category_id).first()
        if not categoryMap.has_key(category.id):
            temp = []
            temp.append(category.display_name)
            temp.append(parent_category.display_name)
            categoryMap[category.id] = temp
        srm = SourceReturnPercentage.get_by(source=OrderSource.FLIPKART,brand=it.brand,category_id=it.category)
        sip = SourceItemPercentage.query.filter(SourceItemPercentage.item_id==it.id).filter(SourceItemPercentage.source==OrderSource.FLIPKART).filter(SourceItemPercentage.startDate<=time).filter(SourceItemPercentage.expiryDate>=time).first()
        sourcePercentage = None
        if sip is not None:
            sourcePercentage = sip
            sourcePercentage.returnProvision = srm.returnProvision
        else:
            scp = SourceCategoryPercentage.query.filter(SourceCategoryPercentage.category_id==it.category).filter(SourceCategoryPercentage.source==OrderSource.FLIPKART).filter(SourceCategoryPercentage.startDate<=time).filter(SourceCategoryPercentage.expiryDate>=time).first()
            if scp is not None:
                sourcePercentage = scp
                sourcePercentage.returnProvision = srm.returnProvision
            else:
                spm = SourcePercentageMaster.get_by(source=OrderSource.FLIPKART)
                sourcePercentage = spm
                sourcePercentage.returnProvision = srm.returnProvision
        
        
    
        warehouse = inventory_client.getWarehouse(flipkart_item.warehouseId)    
        itemSaleList = []
        oosForAllSources = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, 0, 3)
        oosForFlipkart = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 5)
        oosForFlipkartLastDay = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 1)
        oosForFlipkartTwoDay = inventory_client.getOosStatusesForXDaysForItem(flipkart_item.item_id, OrderSource.FLIPKART, 2)
        itemSaleList.append(oosForAllSources)
        itemSaleList.append(oosForFlipkart)
        itemSaleList.append(calculateAverageSale(oosForAllSources))
        itemSaleList.append(calculateAverageSale(oosForFlipkart))
        itemSaleList.append(calculateAverageSale(oosForFlipkartLastDay))
        itemSaleList.append(calculateTotalSale(oosForFlipkart))
        itemSaleList.append(calculateAverageSale(oosForFlipkartTwoDay))
        itemSaleMap[flipkart_item.item_id]=itemSaleList
        
#        try:
#            request_url = "https://api.flipkart.net/sellers/skus/%s/listings"%(str(flipkart_item.skuAtFlipkart))
#            r = requests.get(request_url, auth=('m2z93iskuj81qiid', '0c7ab6a5-98c0-4cdc-8be3-72c591e0add4'))
#            print "Inventory info",r.json()
#            stock_count = int((r.json()['attributeValues'])['stock_count'])
#        except:
#            stock_count = 0
        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)
        itemInfo.append(flipkartItemInfo)
    session.close()
    return itemInfo

def fetchDetails(flipkartSerialNumber):
    url = "http://www.flipkart.com/ps/%s"%(flipkartSerialNumber)
    #url = "http://www.flipkart.com/ps/MOBDTXVZXVY3GFG8"
    #scraper.read(url)
    scraper = FlipkartScraper.FlipkartScraper()
    vendorsData = scraper.read(url)
    fin = datetime.now()
    print "Finish with data for serial Number %s %s" %(flipkartSerialNumber,str(fin))
    sortedVendorsData = sorted(vendorsData, key=itemgetter('sellingPrice'))
    vendorsData[:]=[]
    rank ,ourSp, iterator, secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, shippingTimeLowerLimitLowestSeller,shippingTimeUpperLimitLowestSeller, \
    shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller, shippingTimeLowerLimitOur, shippingTimeUpperLimitOur, shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller, totalAvailableSeller= (0,)*19
    lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
    ourBuyTrend, prefSellerBuyTrend, secondLowestSellerBuyTrend, ourCode = ('',)*11
    for data in sortedVendorsData:
        if iterator == 0:
            lowestSellerName = data['sellerName']
            lowestSellerScore = data['sellerScore']
            lowestSellerCode = data['sellerCode']
            lowestSellerSp = data['sellingPrice']
            lowestSellerBuyTrend = data['buyTrend']
            try:
                shippingTimeLowerLimitLowestSeller, shippingTimeUpperLimitLowestSeller = data['shippingTime'].split('-')
            except ValueError:
                shippingTimeLowerLimitLowestSeller = int(data['shippingTime'])
                
        if iterator ==1:
            secondLowestSellerName = data['sellerName']
            secondLowestSellerScore = data['sellerScore']
            secondLowestSellerCode = data['sellerCode']
            secondLowestSellerSp = data['sellingPrice']
            secondLowestSellerBuyTrend = data['buyTrend']
            try:
                shippingTimeLowerLimitSecondLowestSeller, shippingTimeUpperLimitSecondLowestSeller = data['shippingTime'].split('-')
            except ValueError:
                shippingTimeLowerLimitSecondLowestSeller = int(data['shippingTime'])
                
        if data['sellerName'] == 'Saholic':
            ourScore = data['sellerScore']
            ourCode = data['sellerCode']
            ourSp = data['sellingPrice']
            ourBuyTrend = data['buyTrend']
            try:
                shippingTimeLowerLimitOur, shippingTimeUpperLimitOur = data['shippingTime'].split('-')
            except ValueError:
                shippingTimeLowerLimitOur = int(data['shippingTime'])
            rank = iterator + 1
        
        if data['buyTrend'] in ('PrefCheap','PrefNCheap',''):
            prefSellerName = data['sellerName']
            prefSellerScore = data['sellerScore']
            prefSellerCode = data['sellerCode']
            prefSellerSp = data['sellingPrice']
            prefSellerBuyTrend = data['buyTrend']
            try:
                shippingTimeLowerLimitPrefSeller, shippingTimeUpperLimitPrefSeller = data['shippingTime'].split('-')
            except ValueError:
                shippingTimeLowerLimitPrefSeller = int(data['shippingTime'])
        
        iterator+=1
    
    flipkartDetails = __FlipkartDetails(rank ,ourSp , secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, int(shippingTimeLowerLimitLowestSeller),int(shippingTimeUpperLimitLowestSeller), \
    int(shippingTimeLowerLimitPrefSeller), int(shippingTimeUpperLimitPrefSeller), int(shippingTimeLowerLimitOur), int(shippingTimeUpperLimitOur), int(shippingTimeLowerLimitSecondLowestSeller), int(shippingTimeUpperLimitSecondLowestSeller), len(sortedVendorsData), lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
    ourBuyTrend, prefSellerBuyTrend, secondLowestSellerBuyTrend, ourCode)
    
    if flipkartDetails.ourBuyTrend == 'PrefCheap'and flipkartDetails.rank!=1 and flipkartDetails.ourSp==flipkartDetails.secondLowestSellerSp:
        print "Under PrefCheap category.Switching data for ",flipkartSerialNumber
        flipkartDetails.lowestSellerSp, flipkartDetails.secondLowestSellerSp = flipkartDetails.secondLowestSellerSp,flipkartDetails.lowestSellerSp
        flipkartDetails.lowestSellerScore, flipkartDetails.secondLowestSellerScore = flipkartDetails.secondLowestSellerScore,flipkartDetails.lowestSellerScore
        flipkartDetails.shippingTimeLowerLimitLowestSeller, flipkartDetails.shippingTimeLowerLimitSecondLowestSeller = flipkartDetails.shippingTimeLowerLimitSecondLowestSeller,flipkartDetails.shippingTimeLowerLimitLowestSeller
        flipkartDetails.shippingTimeUpperLimitLowestSeller, flipkartDetails.shippingTimeUpperLimitSecondLowestSeller = flipkartDetails.shippingTimeUpperLimitSecondLowestSeller,flipkartDetails.shippingTimeUpperLimitLowestSeller
        flipkartDetails.lowestSellerName, flipkartDetails.secondLowestSellerName = flipkartDetails.secondLowestSellerName,flipkartDetails.lowestSellerName
        flipkartDetails.lowestSellerCode, flipkartDetails.secondLowestSellerCode = flipkartDetails.secondLowestSellerCode,flipkartDetails.lowestSellerCode
        flipkartDetails.lowestSellerBuyTrend, flipkartDetails.secondLowestSellerBuyTrend = flipkartDetails.secondLowestSellerBuyTrend,flipkartDetails.lowestSellerBuyTrend
        flipkartDetails.rank=1
    
    if flipkartDetails.ourBuyTrend == 'NPrefCheap'and flipkartDetails.rank!=1 and flipkartDetails.ourSp==flipkartDetails.lowestSellerSp:
        print "Under NPrefCheap category.Switching data for ",flipkartSerialNumber
        flipkartDetails.lowestSellerSp = flipkartDetails.ourSp
        flipkartDetails.lowestSellerScore = flipkartDetails.ourScore
        flipkartDetails.shippingTimeLowerLimitLowestSeller = flipkartDetails.shippingTimeLowerLimitOur
        flipkartDetails.shippingTimeUpperLimitLowestSeller = flipkartDetails.shippingTimeUpperLimitOur
        flipkartDetails.lowestSellerName = 'Saholic'
        flipkartDetails.lowestSellerCode = flipkartDetails.ourCode
        flipkartDetails.lowestSellerBuyTrend = flipkartDetails.ourBuyTrend
        flipkartDetails.rank=1
        flipkartDetails.ourBuyTrend ='NPrefCheap' 

    return flipkartDetails

def calculateAverageSale(oosStatus):
    count,sale = 0,0
    for obj in oosStatus:
        if not obj.is_oos:
            count+=1
            sale = sale+obj.num_orders
    avgSalePerDay=0 if count==0 else (float(sale)/count)
    return round(avgSalePerDay,2)

def calculateTotalSale(oosStatus):
    sale = 0
    for obj in oosStatus:
        if not obj.is_oos:
            sale = sale+obj.num_orders
    return sale

def getNetAvailability(itemInventory):
    totalAvailability, totalReserved = 0,0
    availableMap  = itemInventory.availability
    reserveMap = itemInventory.reserved
    for warehouse,availability in availableMap.iteritems():
        if warehouse==16 or warehouse==1771:
            continue
        totalAvailability = totalAvailability+availability
    for warehouse,reserve in reserveMap.iteritems():
        if warehouse==16 or warehouse==1771:
            continue
        totalReserved = totalReserved+reserve
    return totalAvailability - totalReserved

def getOosString(oosStatus):
    lastNdaySale=""
    for obj in oosStatus:
        if obj.is_oos:
            lastNdaySale += "X-"
        else:
            lastNdaySale += str(obj.num_orders) + "-"
    return lastNdaySale[:-1]

def getLastDaySale(itemId):
    return (itemSaleMap.get(itemId))[4]

def getSalesPotential(lowestSellingPrice,ourNlc):
    if lowestSellingPrice - ourNlc < 0:
        return 'HIGH'
    elif (float(lowestSellingPrice - ourNlc))/lowestSellingPrice >=0 and (float(lowestSellingPrice - ourNlc))/lowestSellingPrice <=.02:
        return 'MEDIUM'
    else:
        return 'LOW'  

def decideCategory(itemInfo):
    global itemSaleMap
    
    cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap = [],[],[],[],[],[],[],[]
    
    catalog_client = CatalogClient().get_client()
    
    for val in itemInfo:
        spm = val.sourcePercentage
        flipkartDetails = val.flipkartDetails
        if (flipkartDetails is None or flipkartDetails.totalAvailableSeller==0):
            exceptionItems.append(val)
            continue
        if ((flipkartDetails.rank=='' or flipkartDetails.rank==0) and val.ourFlipkartInventory!=0):
            exceptionItems.append(val)
            continue
        
        mpItem = MarketplaceItems.get_by(itemId=val.item_id,source=OrderSource.FLIPKART)
        if flipkartDetails.rank==0:
            flipkartDetails.ourSp = mpItem.currentSp
            ourSp = mpItem.currentSp
        else:
            ourSp = flipkartDetails.ourSp
        vatRate = catalog_client.getVatPercentageForItem(val.item_id, val.stateId, flipkartDetails.ourSp)
        val.vatRate = vatRate
        if (flipkartDetails.ourBuyTrend == 'PrefCheap') or (flipkartDetails.rank==1 and flipkartDetails.totalAvailableSeller==1):
            temp=[]
            temp.append(flipkartDetails)
            temp.append(val)
            secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
            prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)  
            flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
            temp.append(flipkartPricing)
            temp.append(mpItem)
            buyBoxItems.append(temp)
            continue
        
        if (flipkartDetails.ourBuyTrend == 'PrefNCheap'):
            temp=[]
            temp.append(flipkartDetails)
            temp.append(val)
            secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getSecondLowestSellerTp(flipkartDetails,val,spm,False)
            lowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
            prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)  
            flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
            temp.append(flipkartPricing)
            temp.append(mpItem)
            prefButNotCheap.append(temp)
            continue
        
        if (flipkartDetails.ourBuyTrend == 'NPrefCheap') and (flipkartDetails.rank==1):
            temp=[]
            temp.append(flipkartDetails)
            temp.append(val)
            secondLowestTp=0 if flipkartDetails.totalAvailableSeller==1 else getOtherTp(flipkartDetails,val,spm,False)
            prefSellerTp=0 if flipkartDetails.totalAvailableSeller < 2 else getOtherTp(flipkartDetails,val,spm,True)  
            flipkartPricing = __FlipkartPricing(flipkartDetails.ourSp,getOurTp(flipkartDetails,val,spm,mpItem),None,getLowestPossibleTp(flipkartDetails,val,spm,mpItem),secondLowestTp,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),prefSellerTp)
            temp.append(flipkartPricing)
            temp.append(mpItem)
            cheapButNotPref.append(temp)
            continue
        
        lowestTp = getOtherTp(flipkartDetails,val,spm,False)
        ourTp = getOurTp(flipkartDetails,val,spm,mpItem)
        lowestPossibleTp = getLowestPossibleTp(flipkartDetails,val,spm,mpItem)
        lowestPossibleSp = getLowestPossibleSp(flipkartDetails,val,spm,mpItem)
        prefSellerTp = getOtherTp(flipkartDetails,val,spm,True)
        
        if (ourTp<lowestPossibleTp):
            temp=[]
            temp.append(flipkartDetails)
            temp.append(val)
            flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,getLowestPossibleSp(flipkartDetails,val,spm,mpItem),None)
            temp.append(flipkartPricing)
            negativeMargin.append(temp)
            continue
        
        if (flipkartDetails.lowestSellerSp > lowestPossibleSp) and val.ourFlipkartInventory!=0:
            type(val.ourFlipkartInventory)
            temp=[]
            temp.append(flipkartDetails)
            temp.append(val)
            flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,lowestPossibleSp,prefSellerTp)
            temp.append(flipkartPricing)
            temp.append(mpItem)
            competitive.append(temp)
            continue
        
        if (flipkartDetails.lowestSellerSp) > lowestPossibleSp and val.ourFlipkartInventory==0:
            temp=[]
            temp.append(flipkartDetails)
            temp.append(val)
            flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,lowestPossibleSp,prefSellerTp)
            temp.append(flipkartPricing)
            temp.append(mpItem)
            competitiveNoInventory.append(temp)
            continue
        
        temp=[]
        temp.append(flipkartDetails)
        temp.append(val)
        flipkartPricing = __FlipkartPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,lowestPossibleSp,prefSellerTp)
        temp.append(flipkartPricing)
        temp.append(mpItem)
        cantCompete.append(temp)
    
    itemInfo[:]=[]
    return cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap

def getOtherTp(flipkartDetails,val,spm,prefferedSeller):
    if val.parent_category==10011 or val.parent_category==12001:
        commissionPercentage = spm.competitorCommissionAccessory
    else:
        commissionPercentage = spm.competitorCommissionOther
    if flipkartDetails.rank==1 and not prefferedSeller:
        otherTp = flipkartDetails.secondLowestSellerSp- flipkartDetails.secondLowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
        return round(otherTp,2)
    if prefferedSeller:
        otherTp = flipkartDetails.prefSellerSp- flipkartDetails.prefSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
        return round(otherTp,2)
    otherTp = flipkartDetails.lowestSellerSp- flipkartDetails.lowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
    return round(otherTp,2)

def getSecondLowestSellerTp(flipkartDetails,val,spm,prefferedSeller):
    if val.parent_category==10011 or val.parent_category==12001:
        commissionPercentage = spm.competitorCommissionAccessory
    else:
        commissionPercentage = spm.competitorCommissionOther
    otherTp = flipkartDetails.secondLowestSellerSp- flipkartDetails.secondLowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))
    return round(otherTp,2)
    
def getLowestPossibleTp(flipkartDetails,val,spm,mpItem):
    if flipkartDetails.rank==0:
        return mpItem.minimumPossibleTp
    vat = (flipkartDetails.ourSp/(1+(val.vatRate/100))-(val.nlc/(1+(val.vatRate/100))))*(val.vatRate/100);
    inHouseCost = 15+vat+(mpItem.returnProvision/100)*flipkartDetails.ourSp+mpItem.otherCost;
    lowest_possible_tp = val.nlc+inHouseCost;
    return round(lowest_possible_tp,2)

def getOurTp(flipkartDetails,val,spm,mpItem):
    if flipkartDetails.rank==0:
        return mpItem.currentTp
    ourTp = flipkartDetails.ourSp- flipkartDetails.ourSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))
    return round(ourTp,2)

def getLowestPossibleSp(flipkartDetails,val,spm,mpItem):
    if flipkartDetails.rank==0:
        return mpItem.minimumPossibleSp
    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));
    return round(lowestPossibleSp,2)

def getTargetTp(targetSp,mpItem):
    targetTp = targetSp- targetSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))
    return round(targetTp,2)

def getTargetSp(targetTp,mpItem,ourSp):
    targetSp = float(targetTp+(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100)))/(1-((mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))))
    return round(targetSp,2)

def getNewLowestPossibleTp(mpItem,nlc,vatRate,proposedSellingPrice):
    vat = (proposedSellingPrice/(1+(vatRate/100))-(nlc/(1+(vatRate/100))))*(vatRate/100);
    inHouseCost = 15+vat+(mpItem.returnProvision/100)*proposedSellingPrice+mpItem.otherCost;
    lowest_possible_tp = nlc+inHouseCost;
    return round(lowest_possible_tp,2)

def getNewOurTp(mpItem,proposedSellingPrice):
    ourTp = proposedSellingPrice- proposedSellingPrice*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))
    return round(ourTp,2)

def getNewLowestPossibleSp(mpItem,nlc,vatRate):
    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));
    return round(lowestPossibleSp,2)    
    

def markAutoFavourite():
    previouslyAutoFav = []
    nowAutoFav = []
    marketplaceItems = session.query(MarketplaceItems).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
    fromDate = datetime.now()-timedelta(days = 3, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
    toDate = datetime.now()-timedelta(days = 0, hours=datetime.now().hour, minutes=datetime.now().minute, seconds=datetime.now().second)
    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()
    toUpdate = [key for key, value in itemSaleMap.items() if value[5] >= 1]
    buyBoxLast3days = []
    for item in items:
        buyBoxLast3days.append(item[0])
    for marketplaceItem in marketplaceItems:
        reason = ""
        toMark = False
        if marketplaceItem.itemId in toUpdate:
            toMark = True
            reason+="Total sale is greater than 1 for last five days (Flipkart)."
        if marketplaceItem.itemId in buyBoxLast3days:
            toMark = True
            reason+="Item is present in buy box in last 3 days"
        if not marketplaceItem.autoFavourite:
            print "Item is not under auto favourite"
        if toMark:
            temp=[]
            temp.append(marketplaceItem.itemId)
            temp.append(reason)
            nowAutoFav.append(temp)
        if (not toMark) and marketplaceItem.autoFavourite:
            previouslyAutoFav.append(marketplaceItem.itemId)
        marketplaceItem.autoFavourite = toMark
    session.commit()
    return previouslyAutoFav, nowAutoFav

def write_report(previousAutoFav, nowAutoFav,timestamp, runType):
    wbk = xlwt.Workbook(encoding="UTF-8")
    sheet = wbk.add_sheet('Can\'t Compete')
    xstr = lambda s: s or ""
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    
    sheet.write(0, 0, "Item ID", heading_xf)
    sheet.write(0, 1, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "FK Serial Number", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Commission Rate", heading_xf)
    sheet.write(0, 10, "Return Provision", heading_xf)
    sheet.write(0, 11, "Our Rating", heading_xf)
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
    sheet.write(0, 13, "Our Rank", heading_xf)
    sheet.write(0, 14, "Our SP", heading_xf)
    sheet.write(0, 15, "Our TP", heading_xf)
    sheet.write(0, 16, "Lowest Seller", heading_xf)
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
    sheet.write(0, 21, "Preffered Seller", heading_xf)
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
    sheet.write(0, 27, "Our Net Availability",heading_xf)
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
    sheet.write(0, 29, "Average Sale", heading_xf)
    sheet.write(0, 30, "Our NLC", heading_xf)
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
    sheet.write(0, 33, "Target SP", heading_xf)
    sheet.write(0, 34, "Target TP", heading_xf)  
    sheet.write(0, 35, "Target NLC", heading_xf)
    sheet.write(0, 36, "Sales Potential", heading_xf)
    sheet.write(0, 37, "Total Seller", heading_xf)
    sheet_iterator = 1
    canCompeteItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
    for item in canCompeteItems:
        mpHistory = item[0]
        flipkartItem = item[1]
        mpItem = item[2]
        catItem = item[3]
        sheet.write(sheet_iterator,0,mpHistory.item_id)
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
        sheet.write(sheet_iterator,4,catItem.brand)
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
        sheet.write(sheet_iterator,6,catItem.weight)
        sheet.write(sheet_iterator,7,mpItem.courierCost)
        sheet.write(sheet_iterator,8,catItem.risky)
        sheet.write(sheet_iterator,9,mpItem.commission)
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
#       lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
#       else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
        sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
#        prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
#        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
        sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
        sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
        sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
        if (not inventoryMap.has_key(mpHistory.item_id)):
            sheet.write(sheet_iterator, 27, 'Info not available')
        else:
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
        proposed_sp = mpHistory.lowestSellingPrice - max(10, mpHistory.lowestSellingPrice*0.001)
        proposed_tp = getTargetTp(proposed_sp,mpItem)
        target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
        sheet.write(sheet_iterator, 33, proposed_sp)
        sheet.write(sheet_iterator, 34, proposed_tp)
        sheet.write(sheet_iterator, 35, target_nlc)
        sheet.write(sheet_iterator, 36, getSalesPotential(mpHistory.lowestSellingPrice,mpHistory.ourNlc))
        sheet.write(sheet_iterator, 37, mpHistory.totalSeller)
        sheet_iterator+=1
    
    canCompeteItems[:] = []
    
        
    sheet = wbk.add_sheet('Pref and Cheap')

    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    xstr = lambda s: s or ""

    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    
    sheet.write(0, 0, "Item ID", heading_xf)
    sheet.write(0, 1, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "FK Serial Number", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Commission Rate", heading_xf)
    sheet.write(0, 10, "Return Provision", heading_xf)
    sheet.write(0, 11, "Our Rating", heading_xf)
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
    sheet.write(0, 13, "Our Rank", heading_xf)
    sheet.write(0, 14, "Our SP", heading_xf)
    sheet.write(0, 15, "Our TP", heading_xf)
    sheet.write(0, 16, "Lowest Seller", heading_xf)
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
    sheet.write(0, 21, "Second Lowest Seller", heading_xf)
    sheet.write(0, 22, "Second Lowest Seller Rating", heading_xf)
    sheet.write(0, 23, "Second Lowest Seller Shipping Time", heading_xf)
    sheet.write(0, 24, "Second Lowest Seller SP", heading_xf)
    sheet.write(0, 25, "Second Lowest Seller TP", heading_xf)
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
    sheet.write(0, 27, "Our Net Availability",heading_xf)
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
    sheet.write(0, 29, "Average Sale", heading_xf)
    sheet.write(0, 30, "Our NLC", heading_xf)
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
    sheet.write(0, 33, "Target SP", heading_xf)
    sheet.write(0, 34, "Target TP", heading_xf)  
    sheet.write(0, 35, "Margin Increased Potential", heading_xf)
    sheet.write(0, 36, "Total Seller", heading_xf)
    sheet.write(0, 37, "Auto Pricing Decision", heading_xf)
    sheet.write(0, 38, "Reason", heading_xf)
    sheet.write(0, 39, "Updated Price", heading_xf)
    sheet_iterator = 1
    
    buyBoxItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
    
    
    for item in buyBoxItems:
        mpHistory = item[0]
        flipkartItem = item[1]
        mpItem = item[2]
        catItem = item[3]
        sheet.write(sheet_iterator,0,mpHistory.item_id)
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
        sheet.write(sheet_iterator,4,catItem.brand)
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
        sheet.write(sheet_iterator,6,catItem.weight)
        sheet.write(sheet_iterator,7,mpItem.courierCost)
        sheet.write(sheet_iterator,8,catItem.risky)
        sheet.write(sheet_iterator,9,mpItem.commission)
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
#        lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
#        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
        sheet.write(sheet_iterator,21,mpHistory.secondLowestSellerName)
        sheet.write(sheet_iterator,22,mpHistory.secondLowestSellerRating)
#        secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
#        else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
        sheet.write(sheet_iterator,23,mpHistory.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator,24,mpHistory.secondLowestSellingPrice)
        sheet.write(sheet_iterator,25,mpHistory.secondLowestTp)
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
        if (not inventoryMap.has_key(mpHistory.item_id)):
            sheet.write(sheet_iterator, 27, 'Info not available')
        else:
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
        proposed_sp = max(mpHistory.secondLowestSellingPrice - max((20, mpHistory.secondLowestSellingPrice*0.002)), mpHistory.lowestPossibleSp)
        proposed_tp = getTargetTp(proposed_sp,mpItem)
        target_nlc = proposed_tp -  mpHistory.lowestPossibleTp + mpHistory.ourNlc
        sheet.write(sheet_iterator, 33, proposed_sp)
        sheet.write(sheet_iterator, 34, proposed_tp)
        sheet.write(sheet_iterator, 35, proposed_tp -mpHistory.ourTp )
        sheet.write(sheet_iterator, 36, mpHistory.totalSeller)
        if mpHistory.decision is None:
            sheet.write(sheet_iterator, 37, 'Auto Pricing Inactive')
            sheet_iterator+=1
            continue
        sheet.write(sheet_iterator, 37, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
        sheet.write(sheet_iterator, 38, mpHistory.reason)
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
            sheet.write(sheet_iterator, 39, math.ceil(mpHistory.proposedSellingPrice))
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
            sheet.write(sheet_iterator, 39, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
        
        sheet_iterator+=1
    
    buyBoxItems[:] = []
    
    sheet = wbk.add_sheet('Pref Not Cheap')

    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    xstr = lambda s: s or ""

    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    
    sheet.write(0, 0, "Item ID", heading_xf)
    sheet.write(0, 1, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "FK Serial Number", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Commission Rate", heading_xf)
    sheet.write(0, 10, "Return Provision", heading_xf)
    sheet.write(0, 11, "Our Rating", heading_xf)
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
    sheet.write(0, 13, "Our Rank", heading_xf)
    sheet.write(0, 14, "Our SP", heading_xf)
    sheet.write(0, 15, "Our TP", heading_xf)
    sheet.write(0, 16, "Lowest Seller", heading_xf)
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
    sheet.write(0, 21, "Preffered Seller", heading_xf)
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
    sheet.write(0, 24, "Preffered Seller SP", heading_xf)
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
    sheet.write(0, 27, "Our Net Availability",heading_xf)
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
    sheet.write(0, 29, "Average Sale", heading_xf)
    sheet.write(0, 30, "Our NLC", heading_xf)
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
    sheet.write(0, 33, "Target SP", heading_xf)
    sheet.write(0, 34, "Target TP", heading_xf)  
    sheet.write(0, 35, "Total Seller", heading_xf)
    sheet.write(0, 36, "Auto Pricing Decision", heading_xf)
    sheet.write(0, 37, "Reason", heading_xf)
    sheet.write(0, 38, "Updated Price", heading_xf)

    sheet_iterator = 1
    
    prefNotCheapItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP).all()
    
    
    for item in prefNotCheapItems:
        mpHistory = item[0]
        flipkartItem = item[1]
        mpItem = item[2]
        catItem = item[3]
        sheet.write(sheet_iterator,0,mpHistory.item_id)
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
        sheet.write(sheet_iterator,4,catItem.brand)
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
        sheet.write(sheet_iterator,6,catItem.weight)
        sheet.write(sheet_iterator,7,mpItem.courierCost)
        sheet.write(sheet_iterator,8,catItem.risky)
        sheet.write(sheet_iterator,9,mpItem.commission)
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
#        lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
#        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
        sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
#        secondLowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller) if flipkartDetails.shippingTimeUpperLimitSecondLowestSeller==0\
#        else str(flipkartDetails.shippingTimeLowerLimitSecondLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitSecondLowestSeller)
        sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
        sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
        sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
        if (not inventoryMap.has_key(mpHistory.item_id)):
            sheet.write(sheet_iterator, 27, 'Info not available')
        else:
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
        proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
        proposed_tp = getTargetTp(proposed_sp,mpItem)
        target_nlc = proposed_tp -  mpHistory.lowestPossibleTp + mpHistory.ourNlc
        sheet.write(sheet_iterator, 33, proposed_sp)
        sheet.write(sheet_iterator, 34, proposed_tp)
        sheet.write(sheet_iterator, 35, mpHistory.totalSeller)
        if mpHistory.decision is None:
            sheet.write(sheet_iterator, 36, 'Auto Pricing Inactive')
            sheet_iterator+=1
            continue
        sheet.write(sheet_iterator, 36, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
        sheet.write(sheet_iterator, 37, mpHistory.reason)
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
            sheet.write(sheet_iterator, 38, math.ceil(mpHistory.proposedSellingPrice))
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
            sheet.write(sheet_iterator, 38, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
        
        sheet_iterator+=1
        
    prefNotCheapItems[:] = []
    
    sheet = wbk.add_sheet('Cheap But Not Pref')

    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    xstr = lambda s: s or ""

    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    
    sheet.write(0, 0, "Item ID", heading_xf)
    sheet.write(0, 1, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "FK Serial Number", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Commission Rate", heading_xf)
    sheet.write(0, 10, "Return Provision", heading_xf)
    sheet.write(0, 11, "Our Rank", heading_xf)
    sheet.write(0, 12, "Lowest Seller", heading_xf)
    sheet.write(0, 13, "Our Rating", heading_xf)
    sheet.write(0, 14, "Our Shipping Time", heading_xf)
    sheet.write(0, 15, "Our SP", heading_xf)
    sheet.write(0, 16, "Our TP", heading_xf)
    sheet.write(0, 17, "Preffered Seller", heading_xf)
    sheet.write(0, 18, "Preffered Seller Rating", heading_xf)
    sheet.write(0, 19, "Preffered Seller Shipping Time", heading_xf)
    sheet.write(0, 20, "Preffered Seller SP", heading_xf)
    sheet.write(0, 21, "Preffered Seller TP", heading_xf)
    sheet.write(0, 22, "Our Flipkart Inventory", heading_xf)
    sheet.write(0, 23, "Our Net Availability",heading_xf)
    sheet.write(0, 24, "Last Five Day Sale", heading_xf)
    sheet.write(0, 25, "Average Sale", heading_xf)
    sheet.write(0, 26, "Our NLC", heading_xf)
    sheet.write(0, 27, "Lowest Possible SP", heading_xf)
    sheet.write(0, 28, "Lowest Possible TP", heading_xf)
    sheet.write(0, 29, "Total Seller", heading_xf)
    sheet_iterator = 1
    
    cheapNotPrefferedItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CHEAP_BUT_NOT_PREF).all()
    
    for item in cheapNotPrefferedItems:
        mpHistory = item[0]
        flipkartItem = item[1]
        mpItem = item[2]
        catItem = item[3]
        sheet.write(sheet_iterator,0,mpHistory.item_id)
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
        sheet.write(sheet_iterator,4,catItem.brand)
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
        sheet.write(sheet_iterator,6,catItem.weight)
        sheet.write(sheet_iterator,7,mpItem.courierCost)
        sheet.write(sheet_iterator,8,catItem.risky)
        sheet.write(sheet_iterator,9,mpItem.commission)
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
        sheet.write(sheet_iterator,11,mpHistory.ourRank)
        sheet.write(sheet_iterator,12,mpHistory.lowestSellerName)
        sheet.write(sheet_iterator,13,mpHistory.ourRating)
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        sheet.write(sheet_iterator,14,mpHistory.lowestSellerShippingTime)
        sheet.write(sheet_iterator,15,mpHistory.lowestSellingPrice)
        sheet.write(sheet_iterator,16,mpHistory.lowestTp)
        sheet.write(sheet_iterator,17,mpHistory.prefferedSellerName)
        sheet.write(sheet_iterator,18,mpHistory.prefferedSellerRating)
#        prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
#        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
        sheet.write(sheet_iterator,19,mpHistory.prefferedSellerShippingTime)
        sheet.write(sheet_iterator,20,mpHistory.prefferedSellerSellingPrice)
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerTp)
        sheet.write(sheet_iterator,22,mpHistory.ourInventory)
        if (not inventoryMap.has_key(mpHistory.item_id)):
            sheet.write(sheet_iterator, 23, 'Info not available')
        else:
            sheet.write(sheet_iterator, 23, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
        sheet.write(sheet_iterator, 24, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
        sheet.write(sheet_iterator, 25, (itemSaleMap.get(mpHistory.item_id))[3])
        sheet.write(sheet_iterator, 26, mpHistory.ourNlc)
        sheet.write(sheet_iterator, 27, mpHistory.lowestPossibleSp)
        sheet.write(sheet_iterator, 28, mpHistory.lowestPossibleTp)
        #proposed_sp = max(flipkartDetails.secondLowestSellerSp - max((20, flipkartDetails.secondLowestSellerSp*0.002)), flipkartPricing.lowestPossibleSp)
        #proposed_tp = getTargetTp(proposed_sp,mpItem)
        #target_nlc = proposed_tp - flipkartPricing.lowestPossibleTp + flipkartItemInfo.nlc
        sheet.write(sheet_iterator, 29, mpHistory.totalSeller)
        sheet_iterator+=1
        
    cheapNotPrefferedItems[:]=[]
    
    sheet = wbk.add_sheet('Can Compete-With Inventory')
    xstr = lambda s: s or ""
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    
    sheet.write(0, 0, "Item ID", heading_xf)
    sheet.write(0, 1, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "FK Serial Number", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Commission Rate", heading_xf)
    sheet.write(0, 10, "Return Provision", heading_xf)
    sheet.write(0, 11, "Our Rating", heading_xf)
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
    sheet.write(0, 13, "Our Rank", heading_xf)
    sheet.write(0, 14, "Our SP", heading_xf)
    sheet.write(0, 15, "Our TP", heading_xf)
    sheet.write(0, 16, "Lowest Seller", heading_xf)
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
    sheet.write(0, 21, "Preffered Seller", heading_xf)
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
    sheet.write(0, 27, "Our Net Availability",heading_xf)
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
    sheet.write(0, 29, "Average Sale", heading_xf)
    sheet.write(0, 30, "Our NLC", heading_xf)
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
    sheet.write(0, 33, "Target SP", heading_xf)
    sheet.write(0, 34, "Target TP", heading_xf)  
    sheet.write(0, 35, "Target NLC", heading_xf)
    sheet.write(0, 36, "Sales Potential", heading_xf)
    sheet.write(0, 37, "Total Seller", heading_xf)
    sheet.write(0, 38, "Auto Pricing Decision", heading_xf)
    sheet.write(0, 39, "Reason", heading_xf)
    sheet.write(0, 40, "Updated Price", heading_xf)
    sheet_iterator = 1
    
    competitiveItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).all()
    
    for item in competitiveItems:
        mpHistory = item[0]
        flipkartItem = item[1]
        mpItem = item[2]
        catItem = item[3]
        sheet.write(sheet_iterator,0,mpHistory.item_id)
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
        sheet.write(sheet_iterator,4,catItem.brand)
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
        sheet.write(sheet_iterator,6,catItem.weight)
        sheet.write(sheet_iterator,7,mpItem.courierCost)
        sheet.write(sheet_iterator,8,catItem.risky)
        sheet.write(sheet_iterator,9,mpItem.commission)
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
#        lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
#        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
        sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
#        prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
#        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
        sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
        sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
        sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
        if (not inventoryMap.has_key(mpHistory.item_id)):
            sheet.write(sheet_iterator, 27, 'Info not available')
        else:
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
        proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
        proposed_tp = getTargetTp(proposed_sp,mpItem)
        target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
        sheet.write(sheet_iterator, 33, proposed_sp)
        sheet.write(sheet_iterator, 34, proposed_tp)
        sheet.write(sheet_iterator, 35, target_nlc)
        sheet.write(sheet_iterator, 36, getSalesPotential(mpHistory.lowestSellingPrice,mpHistory.ourNlc))
        sheet.write(sheet_iterator, 37, mpHistory.totalSeller)
        if mpHistory.decision is None:
            sheet.write(sheet_iterator, 38, 'Auto Pricing Inactive')
            sheet_iterator+=1
            continue
        sheet.write(sheet_iterator, 38, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
        sheet.write(sheet_iterator, 39, mpHistory.reason)
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
            sheet.write(sheet_iterator, 40, math.ceil(mpHistory.proposedSellingPrice))
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
            sheet.write(sheet_iterator, 40, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
        
        sheet_iterator+=1
    
    competitiveItems[:]=[]
    
    sheet = wbk.add_sheet('Negative Margin')
    xstr = lambda s: s or ""
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    
    sheet.write(0, 0, "Item ID", heading_xf)
    sheet.write(0, 1, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "FK Serial Number", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Commission Rate", heading_xf)
    sheet.write(0, 10, "Return Provision", heading_xf)
    sheet.write(0, 11, "Our Rating", heading_xf)
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
    sheet.write(0, 13, "Our Rank", heading_xf)
    sheet.write(0, 14, "Our SP", heading_xf)
    sheet.write(0, 15, "Our TP", heading_xf)
    sheet.write(0, 16, "Lowest Seller", heading_xf)
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
    sheet.write(0, 21, "Preffered Seller", heading_xf)
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
    sheet.write(0, 27, "Our Net Availability",heading_xf)
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
    sheet.write(0, 29, "Average Sale", heading_xf)
    sheet.write(0, 30, "Our NLC", heading_xf)
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
    sheet.write(0, 33, "Margin", heading_xf)
    sheet.write(0, 34, "Total Seller", heading_xf)
    sheet_iterator = 1
    
    negativeMargin = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).all()
    
    for item in negativeMargin:
        mpHistory = item[0]
        flipkartItem = item[1]
        mpItem = item[2]
        catItem = item[3]
        sheet.write(sheet_iterator,0,mpHistory.item_id)
        sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
        sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
        sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
        sheet.write(sheet_iterator,4,catItem.brand)
        sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
        sheet.write(sheet_iterator,6,catItem.weight)
        sheet.write(sheet_iterator,7,mpItem.courierCost)
        sheet.write(sheet_iterator,8,catItem.risky)
        sheet.write(sheet_iterator,9,mpItem.commission)
        sheet.write(sheet_iterator,10,mpItem.returnProvision)
        sheet.write(sheet_iterator,11,mpHistory.ourRating)
#        ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
#        else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
        sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
        sheet.write(sheet_iterator,13,mpHistory.ourRank)
        sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
        sheet.write(sheet_iterator,15,mpHistory.ourTp)
        sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
        sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
#        lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
#        else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
        sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
        sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
        sheet.write(sheet_iterator,20,mpHistory.lowestTp)
        sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
        sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
#        prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
#        else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
        sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
        sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
        sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
        sheet.write(sheet_iterator,26,mpHistory.ourInventory)
        if (not inventoryMap.has_key(mpHistory.item_id)):
            sheet.write(sheet_iterator, 27, 'Info not available')
        else:
            sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
        sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
        sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
        sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
        sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
        sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
        sheet.write(sheet_iterator, 33, round((mpHistory.ourTp - mpHistory.lowestPossibleTp),2))
        sheet.write(sheet_iterator, 34, mpHistory.totalSeller)
        sheet_iterator+=1
        
    negativeMargin[:]=[]

    if (runType=='FULL'):    
        sheet = wbk.add_sheet('Auto Favorites')
        
        heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
        
        excel_integer_format = '0'
        integer_style = xlwt.XFStyle()
        integer_style.num_format_str = excel_integer_format
        xstr = lambda s: s or ""
        
        sheet.write(0, 0, "Item ID", heading_xf)
        sheet.write(0, 1, "Brand", heading_xf)
        sheet.write(0, 2, "Product Name", heading_xf)
        sheet.write(0, 3, "Auto Favourite", heading_xf)
        sheet.write(0, 4, "Reason", heading_xf)
        
        sheet_iterator=1
        for autoFav in nowAutoFav:
            itemId = autoFav[0]
            reason = autoFav[1]
            it = Item.query.filter_by(id=itemId).one()
            sheet.write(sheet_iterator, 0, itemId)
            sheet.write(sheet_iterator, 1, it.brand)
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
            sheet.write(sheet_iterator, 3, "True")
            sheet.write(sheet_iterator, 4, reason)
            sheet_iterator+=1
        for prevFav in previousAutoFav:
            it = Item.query.filter_by(id=prevFav).one()
            sheet.write(sheet_iterator, 0, prevFav)
            sheet.write(sheet_iterator, 1, it.brand)
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
            sheet.write(sheet_iterator, 3, "False")
            sheet_iterator+=1
    
    sheet = wbk.add_sheet('Exception Item List')

    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    xstr = lambda s: s or ""
    
    sheet.write(0, 0, "Item ID", heading_xf)
    sheet.write(0, 1, "FK Serial number", heading_xf)
    sheet.write(0, 2, "Brand", heading_xf)
    sheet.write(0, 3, "Product Name", heading_xf)
    sheet.write(0, 4, "Reason", heading_xf)
    sheet_iterator=1
    
    exeptionItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.EXCEPTION).all()
    
    for item in exeptionItems:
        mpHistory = item[0]
        flipkartItem = item[1]
        mpItem = item[2]
        catItem = item[3]
        sheet.write(sheet_iterator, 0, mpHistory.item_id)
        sheet.write(sheet_iterator, 1, flipkartItem.flipkartSerialNumber)
        sheet.write(sheet_iterator, 2, catItem.brand)
        sheet.write(sheet_iterator, 3, xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
        try:
            if mpHistory.totalSeller is None:
                pass
        except:
            sheet.write(sheet_iterator, 4, "Unable to fetch info from Flipkart")
            sheet_iterator+=1
            continue
        sheet.write(sheet_iterator, 4, "No Seller Available")
        sheet_iterator+=1
    
    exeptionItems[:]=[]

    sheet = wbk.add_sheet('Can Compete-No Inv')
    xstr = lambda s: s or ""
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    
    sheet.write(0, 0, "Item ID", heading_xf)
    sheet.write(0, 1, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "FK Serial Number", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Commission Rate", heading_xf)
    sheet.write(0, 10, "Return Provision", heading_xf)
    sheet.write(0, 11, "Our Rating", heading_xf)
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
    sheet.write(0, 13, "Our Rank", heading_xf)
    sheet.write(0, 14, "Our SP", heading_xf)
    sheet.write(0, 15, "Our TP", heading_xf)
    sheet.write(0, 16, "Lowest Seller", heading_xf)
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
    sheet.write(0, 21, "Preffered Seller", heading_xf)
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
    sheet.write(0, 27, "Our Net Availability",heading_xf)
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
    sheet.write(0, 29, "Average Sale", heading_xf)
    sheet.write(0, 30, "Our NLC", heading_xf)
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
    sheet.write(0, 33, "Target SP", heading_xf)
    sheet.write(0, 34, "Target TP", heading_xf)  
    sheet.write(0, 35, "Sales Potential", heading_xf)
    sheet.write(0, 36, "Total Seller", heading_xf)
    sheet_iterator = 1
    
    competitiveNoInventory = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
    .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
    .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .join((Item,MarketPlaceHistory.item_id==Item.id))\
    .filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
    .filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE_NO_INVENTORY).all()
    
    for item in competitiveNoInventory:
        mpHistory = item[0]
        flipkartItem = item[1]
        mpItem = item[2]
        catItem = item[3]
        if ((not inventoryMap.has_key(mpHistory.item_id)) or getNetAvailability(inventoryMap.get(mpHistory.item_id))<=0):
            sheet.write(sheet_iterator,0,mpHistory.item_id)
            sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
            sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
            sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
            sheet.write(sheet_iterator,4,catItem.brand)
            sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
            sheet.write(sheet_iterator,6,catItem.weight)
            sheet.write(sheet_iterator,7,mpItem.courierCost)
            sheet.write(sheet_iterator,8,catItem.risky)
            sheet.write(sheet_iterator,9,mpItem.commission)
            sheet.write(sheet_iterator,10,mpItem.returnProvision)
            sheet.write(sheet_iterator,11,mpHistory.ourRating)
#            ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
#            else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
            sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
            sheet.write(sheet_iterator,13,mpHistory.ourRank)
            sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
            sheet.write(sheet_iterator,15,mpHistory.ourTp)
            sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
            sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
#            lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
#            else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
            sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
            sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
            sheet.write(sheet_iterator,20,mpHistory.lowestTp)
            sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
            sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
#            prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
#            else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
            sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
            sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
            sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
            sheet.write(sheet_iterator,26,mpHistory.ourInventory)
            if (not inventoryMap.has_key(mpHistory.item_id)):
                sheet.write(sheet_iterator, 27, 'Info not available')
            else:
                sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
            sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
            sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
            sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
            sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
            sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
            proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
            proposed_tp = getTargetTp(proposed_sp,mpItem)
            sheet.write(sheet_iterator, 33, proposed_sp)
            sheet.write(sheet_iterator, 34, proposed_tp)
            sheet.write(sheet_iterator, 35, getSalesPotential(mpHistory.lowestPossibleSp,mpHistory.ourNlc))
            sheet.write(sheet_iterator, 36, mpHistory.totalSeller)
            sheet_iterator+=1
    
    
    sheet = wbk.add_sheet('Can Compete-No Inv On FK')
    xstr = lambda s: s or ""
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    
    sheet.write(0, 0, "Item ID", heading_xf)
    sheet.write(0, 1, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "FK Serial Number", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Commission Rate", heading_xf)
    sheet.write(0, 10, "Return Provision", heading_xf)
    sheet.write(0, 11, "Our Rating", heading_xf)
    sheet.write(0, 12, "Our Shipping Time", heading_xf)
    sheet.write(0, 13, "Our Rank", heading_xf)
    sheet.write(0, 14, "Our SP", heading_xf)
    sheet.write(0, 15, "Our TP", heading_xf)
    sheet.write(0, 16, "Lowest Seller", heading_xf)
    sheet.write(0, 17, "Lowest Seller Rating", heading_xf)
    sheet.write(0, 18, "Lowest Seller Shipping Time", heading_xf)
    sheet.write(0, 19, "Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "Lowest Seller TP", heading_xf)
    sheet.write(0, 21, "Preffered Seller", heading_xf)
    sheet.write(0, 22, "Preffered Seller Rating", heading_xf)
    sheet.write(0, 23, "Preffered Seller Shipping Time", heading_xf)
    sheet.write(0, 24, "Preffer Seller SP", heading_xf)
    sheet.write(0, 25, "Preffered Seller TP", heading_xf)
    sheet.write(0, 26, "Our Flipkart Inventory", heading_xf)
    sheet.write(0, 27, "Our Net Availability",heading_xf)
    sheet.write(0, 28, "Last Five Day Sale", heading_xf)
    sheet.write(0, 29, "Average Sale", heading_xf)
    sheet.write(0, 30, "Our NLC", heading_xf)
    sheet.write(0, 31, "Lowest Possible SP", heading_xf)
    sheet.write(0, 32, "Lowest Possible TP", heading_xf)
    sheet.write(0, 33, "Target SP", heading_xf)
    sheet.write(0, 34, "Target TP", heading_xf)  
    sheet.write(0, 35, "Sales Potential", heading_xf)
    sheet.write(0, 36, "Total Seller", heading_xf)
    sheet_iterator = 1
    for item in competitiveNoInventory:
        mpHistory = item[0]
        flipkartItem = item[1]
        mpItem = item[2]
        catItem = item[3]
        if (inventoryMap.has_key(mpHistory.item_id) and getNetAvailability(inventoryMap.get(mpHistory.item_id))>0):
            sheet.write(sheet_iterator,0,mpHistory.item_id)
            sheet.write(sheet_iterator,1,categoryMap.get(catItem.category)[0])
            sheet.write(sheet_iterator,2,categoryMap.get(catItem.category)[1])
            sheet.write(sheet_iterator,3,flipkartItem.flipkartSerialNumber)
            sheet.write(sheet_iterator,4,catItem.brand)
            sheet.write(sheet_iterator,5,xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color))
            sheet.write(sheet_iterator,6,catItem.weight)
            sheet.write(sheet_iterator,7,mpItem.courierCost)
            sheet.write(sheet_iterator,8,catItem.risky)
            sheet.write(sheet_iterator,9,mpItem.commission)
            sheet.write(sheet_iterator,10,mpItem.returnProvision)
            sheet.write(sheet_iterator,11,mpHistory.ourRating)
#            ourShippingTime= str(flipkartDetails.shippingTimeLowerLimitOur) if flipkartDetails.shippingTimeUpperLimitOur==0\
#            else str(flipkartDetails.shippingTimeLowerLimitOur)+'-'+str(flipkartDetails.shippingTimeUpperLimitOur)
            sheet.write(sheet_iterator,12,mpHistory.ourShippingTime)
            sheet.write(sheet_iterator,13,mpHistory.ourRank)
            sheet.write(sheet_iterator,14,mpHistory.ourSellingPrice)
            sheet.write(sheet_iterator,15,mpHistory.ourTp)
            sheet.write(sheet_iterator,16,mpHistory.lowestSellerName)
            sheet.write(sheet_iterator,17,mpHistory.lowestSellerRating)
#            lowestSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitLowestSeller) if flipkartDetails.shippingTimeUpperLimitLowestSeller==0\
#            else str(flipkartDetails.shippingTimeLowerLimitLowestSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitLowestSeller)
            sheet.write(sheet_iterator,18,mpHistory.lowestSellerShippingTime)
            sheet.write(sheet_iterator,19,mpHistory.lowestSellingPrice)
            sheet.write(sheet_iterator,20,mpHistory.lowestTp)
            sheet.write(sheet_iterator,21,mpHistory.prefferedSellerName)
            sheet.write(sheet_iterator,22,mpHistory.prefferedSellerRating)
#            prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
#            else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
            sheet.write(sheet_iterator,23,mpHistory.prefferedSellerShippingTime)
            sheet.write(sheet_iterator,24,mpHistory.prefferedSellerSellingPrice)
            sheet.write(sheet_iterator,25,mpHistory.prefferedSellerTp)
            sheet.write(sheet_iterator,26,mpHistory.ourInventory)
            if (not inventoryMap.has_key(mpHistory.item_id)):
                sheet.write(sheet_iterator, 27, 'Info not available')
            else:
                sheet.write(sheet_iterator, 27, getNetAvailability(inventoryMap.get(mpHistory.item_id)))
            sheet.write(sheet_iterator, 28, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
            sheet.write(sheet_iterator, 29, (itemSaleMap.get(mpHistory.item_id))[3])
            sheet.write(sheet_iterator, 30, mpHistory.ourNlc)
            sheet.write(sheet_iterator, 31, mpHistory.lowestPossibleSp)
            sheet.write(sheet_iterator, 32, mpHistory.lowestPossibleTp)
            proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
            proposed_tp = getTargetTp(proposed_sp,mpItem)
            sheet.write(sheet_iterator, 33, proposed_sp)
            sheet.write(sheet_iterator, 34, proposed_tp)
            sheet.write(sheet_iterator, 35, getSalesPotential(mpHistory.lowestPossibleSp,mpHistory.ourNlc))
            sheet.write(sheet_iterator, 36, mpHistory.totalSeller)
            sheet_iterator+=1
    competitiveNoInventory[:]=[]
    
#    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()
#    sheet = wbk.add_sheet('Auto Inc and Dec')
#
#    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
#    
#    excel_integer_format = '0'
#    integer_style = xlwt.XFStyle()
#    integer_style.num_format_str = excel_integer_format
#    xstr = lambda s: s or ""
#    
#    sheet.write(0, 0, "Item ID", heading_xf)
#    sheet.write(0, 1, "Brand", heading_xf)
#    sheet.write(0, 2, "Product Name", heading_xf)
#    sheet.write(0, 3, "Decision", heading_xf)
#    sheet.write(0, 4, "Reason", heading_xf)
#    sheet.write(0, 5, "Old Selling Price", heading_xf)
#    sheet.write(0, 6, "Selling Price Updated",heading_xf)
#    
#    sheet_iterator=1
#    for autoPricingItem in autoPricingItems:
#        mpHistory = autoPricingItem[0]
#        item = autoPricingItem[1]
#        it = Item.query.filter_by(id=item.id).one()
#        sheet.write(sheet_iterator, 0, item.id)
#        sheet.write(sheet_iterator, 1, it.brand)
#        sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
#        sheet.write(sheet_iterator, 3, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
#        sheet.write(sheet_iterator, 4, mpHistory.reason)
#        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
#            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
#            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.proposedSellingPrice))
#        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
#            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
#            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
#        sheet_iterator+=1
    
    filename = "/tmp/flipkart-report-"+runType+" " + str(timestamp) + ".xls"
    wbk.save(filename)
    try:
        #EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Flipkart Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
        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","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
    except Exception as e:
        print e
        print "Unable to send report.Trying with local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        #recipients = ["kshitij.sood@saholic.com"]
        msg = MIMEMultipart()
        msg['Subject'] = "Flipkart Scraping" + ' '+runType+' - ' + str(datetime.now())
        msg['From'] = sender
        recipients = ['rajneesh.arora@saholic.com','anikendra.das@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']
        msg['To'] = ",".join(recipients)
        fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
        fileMsg.set_payload(file(filename).read())
        email.encoders.encode_base64(fileMsg)
        fileMsg.add_header('Content-Disposition','attachment;filename=flipkart.xls')
        msg.attach(fileMsg)
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."
            
def populateScrapingResults(val):
    try:
        now = datetime.now()
        print "Fetching data for serial Number %s %s" %(val.fkSerialNumber,str(now))
        flipkartDetails = fetchDetails(val.fkSerialNumber)
        val.flipkartDetails = flipkartDetails 
    except Exception as e:
        print "Unable to fetch details of %s" %(val.fkSerialNumber)
        print e
        val.flipkartDetails = None
        return
        
    try:
        request_url = "https://api.flipkart.net/sellers/skus/%s/listings"%(str(val.skuAtFlipkart))
        r = requests.get(request_url, auth=('m2z93iskuj81qiid', '0c7ab6a5-98c0-4cdc-8be3-72c591e0add4'))
        print "Inventory info",r.json()
        stock_count = int((r.json()['attributeValues'])['stock_count'])
    except:
        stock_count = 0
    finally:
        r={}
    
    val.ourFlipkartInventory = stock_count
            
def threadsToSpawn(runType,itemInfo,itemPopulated):
    if runType == RunType.FAVOURITE:
        count = 0
        pool = ThreadPool(3)
        startOffset = 0
        endOffset = startOffset
        while(count<3 and endOffset<len(itemInfo)):
            endOffset = startOffset + 20
            if (endOffset >= len(itemInfo)):
                endOffset = len(itemInfo)
            print "pool offset start end count"+str(startOffset)+" "+str(endOffset)+" "+str(count)
            pool.map(populateScrapingResults,itemInfo[startOffset:endOffset])
            #t = Process(target=decideCategory,args=(itemInfo[startOffset:endOffset], scraper))
            #t = threading.Thread(target=partial(decideCategory, itemInfo[startOffset:endOffset], scraper))
            #t = threading.Thread(target=partial(test, startOffset, endOffset))
            #threads.append(t)
            startOffset = startOffset + 20
            count+=1
        #[t.start() for t in threads]
        #[t.join() for t in threads] 
        #threads = []
        pool.close()
        pool.join()
        return endOffset
    else:
        count = 0
        pool = ThreadPool(50)
        startOffset = 0
        endOffset = startOffset
        while(count<1 and endOffset<len(itemInfo)):
            endOffset = startOffset + 50
            if (endOffset >= len(itemInfo)):
                endOffset = len(itemInfo)
            print "pool offset start end count"+str(startOffset)+" "+str(endOffset)+" "+str(count)
            pool.map(populateScrapingResults,itemInfo[startOffset:endOffset])
            #t = Process(target=decideCategory,args=(itemInfo[startOffset:endOffset], scraper))
            #t = threading.Thread(target=partial(decideCategory, itemInfo[startOffset:endOffset], scraper))
            #t = threading.Thread(target=partial(test, startOffset, endOffset))
            #threads.append(t)
            startOffset = startOffset + 50
            count+=1
        #[t.start() for t in threads]
        #[t.join() for t in threads] 
        #threads = []
        print "terminating while"
        pool.close()
        pool.join()
        print "joining threads"
        print "returning offset******"
        return endOffset
    
def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
    if len(successfulAutoDecrease)==0 and len(successfulAutoIncrease)==0 :
        return
    xstr = lambda s: s or ""
    catalog_client = CatalogClient().get_client()
    inventory_client = InventoryClient().get_client()
    message="""<html>
            <body>
            <h3>Auto Decrease Items</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Product Name</th>
            <th>Old Price</th>
            <th>New Price</th>
            <th>Old Margin</th>
            <th>New Margin</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Flipkart Inventory</th>
            <th>Sales History</th>
            <th>Category</th>
            </tr></thead>
            <tbody>"""
    for item in successfulAutoDecrease:
        it = Item.query.filter_by(id=item.item_id).one()
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
        warehouse = inventory_client.getWarehouse(fkItem.warehouseId)
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
        newMargin = round(getNewOurTp(mpItem,item.proposedSellingPrice) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice))  
        message+="""<tr>
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
                <td style="text-align:center">"""+str(math.ceil(item.proposedSellingPrice))+"""</td>
                <td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/item.proposedSellingPrice)*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(mpItem.commission)+" %"+"""</td>
                <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
                <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(item.competitiveCategory))+"""</td>
                </tr>"""
    message+="""</tbody></table><h3>Auto Increase Items</h3><table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Product Name</th>
            <th>Old Price</th>
            <th>New Price</th>
            <th>Old Margin</th>
            <th>New Margin</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Flipkart Inventory</th>
            <th>Sales History</th>
            <th>Category</th>
            </tr></thead>
            <tbody>"""
    for item in successfulAutoIncrease:
        it = Item.query.filter_by(id=item.item_id).one()
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
        warehouse = inventory_client.getWarehouse(fkItem.warehouseId)
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
        newMargin = round(getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))  
        message+="""<tr>
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
                <td style="text-align:center">"""+str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))+"""</td>
                <td style="text-align:center">"""+str(round((item.margin),1))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(mpItem.commission)+" %"+"""</td>
                <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
                <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(item.competitiveCategory))+"""</td>
                </tr>"""
    message+="""</tbody></table></body></html>"""
    print message
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@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']
    msg = MIMEMultipart()
    msg['Subject'] = "Flipkart Auto Pricing" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Flipkart Auto Pricing" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send pricing mail.Lets try with local SMTP."
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."

def processLostBuyBoxItems(previousProcessingTimestamp,currentTimestamp):
    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()
    print "previous buy box ",previous_buy_box
    cant_compete = session.query(MarketPlaceHistory.item_id).filter(MarketPlaceHistory.timestamp==currentTimestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
    print "cant compete ",cant_compete
    if previous_buy_box is None or previous_buy_box==[]:
        print "No item in buy box for last run"
        return
    lost_buy_box = list(set(list(zip(*previous_buy_box)[0]))&set(list(zip(*cant_compete)[0])))
    if len(lost_buy_box)==0:
        return
    xstr = lambda s: s or ""
    message="""<html>
            <body>
            <h3>Lost Buy Box</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Product Name</th>
            <th>Current Price</th>
            <th>Current Margin</th>
            <th>Lowest Seller</th>
            <th>Lowest Selling Price</th>
            <th>Preffered Seller</th>
            <th>Preffered Selling Price</th>
            <th>NLC</th>
            <th>Target NLC</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Flipkart Inventory</th>
            <th>Total Inventory</th>
            <th>Sales History</th>
            </tr></thead>
            <tbody>"""
    items = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==currentTimestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.item_id.in_(lost_buy_box)).all()
    for item in items:
        it = Item.query.filter_by(id=item.item_id).one()
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
        netInventory=''
        if not inventoryMap.has_key(item.item_id):
            netInventory='Info Not Available'
        else:
            netInventory = str(getNetAvailability(inventoryMap.get(item.item_id)))
        message+="""<tr>
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
                <td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(item.lowestSellerName)+"""</td>
                <td style="text-align:center">"""+str(item.lowestSellingPrice)+"""</td>
                <td style="text-align:center">"""+str(item.prefferedSellerName)+"""</td>
                <td style="text-align:center">"""+str(item.prefferedSellerSellingPrice)+"""</td>
                <td style="text-align:center">"""+str(item.ourNlc)+"""</td>
                <td style="text-align:center">"""+str(item.targetNlc)+"""</td>
                <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
                <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
                <td style="text-align:center">"""+netInventory+"""</td>
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
                </tr>"""
    message+="""</tbody></table></body></html>"""
    print message
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@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']
    msg = MIMEMultipart()
    msg['Subject'] = "Flipkart Lost Buy Box" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Flipkart Lost Buy Box" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send lost buy box mail.Lets try local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."

def cheapButNotPrefAlert(timestamp):
    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()
    if len(cheap_but_not_pref)==0:
        return
    xstr = lambda s: s or ""
    message="""<html>
            <body>
            <h3>Cheap But Not Preferred</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Product Name</th>
            <th>Current Price</th>
            <th>Our Rating</th>
            <th>Our Shipping Time</th>
            <th>Preffered Seller</th>
            <th>Preffered Seller SP</th>
            <th>Preffered Seller Rating</th>
            <th>Preffered Seller Shipping Time</th>
            <th>Price Variance %</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Flipkart Inventory</th>
            <th>Total Inventory</th>
            <th>Sales History</th>
            </tr></thead>
            <tbody>"""
    for item in cheap_but_not_pref:
        mpHistory = item[0]
        catItem = item[1]
        netInventory=''
        if not inventoryMap.has_key(mpHistory.item_id):
            netInventory='Info Not Available'
        else:
            netInventory = str(getNetAvailability(inventoryMap.get(mpHistory.item_id)))
        ourSt = mpHistory.ourShippingTime.split('-')
        pfSt = mpHistory.prefferedSellerShippingTime.split('-')
        mpItem = MarketplaceItems.get_by(itemId=mpHistory.item_id,source=OrderSource.FLIPKART)
        if mpHistory.prefferedSellerName=='WS Retail' and mpHistory.ourRating > mpHistory.prefferedSellerRating and int(ourSt[0])<=int(pfSt[0]):
            style="""background-color:red;\""""
        else:
            style="\""
        message+="""<tr>
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.item_id)+"""</td>
            <td style="text-align:center;"""+str(style)+""">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourSellingPrice)+"""</td>
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourRating)+"""</td>
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourShippingTime)+"""</td>
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerName)+"""</td>
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerSellingPrice)+"""</td>
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerRating)+"""</td>
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.prefferedSellerShippingTime)+"""</td>
            <td style="text-align:center;"""+str(style)+""">"""+str(round(((mpHistory.prefferedSellerSellingPrice-mpHistory.ourSellingPrice)/mpHistory.ourSellingPrice)*100))+"%"+"""</td>
            <td style="text-align:center">"""+str(mpItem.commission)+" %"+"""</td>
            <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
            <td style="text-align:center;"""+str(style)+""">"""+str(mpHistory.ourInventory)+"""</td>
            <td style="text-align:center;"""+str(style)+""">"""+netInventory+"""</td>
            <td style="text-align:center;"""+str(style)+""">"""+getOosString((itemSaleMap.get(mpHistory.item_id))[1])+"""</td>
            </tr>"""
    message+="""</tbody></table></body></html>"""
    print message
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@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']
    msg = MIMEMultipart()
    msg['Subject'] = "Flipkart Cheap But Not In BuyBox Items" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Flipkart Cheap But Not In BuyBox Items" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send Flipkart Cheap But Not In BuyBox Items mail.Lets try local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."

def sendPricingMismatch(timestamp):
    xstr = lambda s: s or ""
    message="""<html>
            <body>
            <h3>Flipkart Pricing Mismatch</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Product Name</th>
            <th>Our System Price</th>
            <th>Flipkart Price</th>
            <th>Flipkart Inventory</th>
            <th>Total Inventory</th>
            <th>Sales History</th>
            </tr></thead>
            <tbody>"""
    flipkartPricing = {}
    saholicPricing = {}
    mpHistoryItems = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).all()
    for val in mpHistoryItems:
        temp = []
        temp.append(val[0].ourSellingPrice)
        temp.append(xstr(val[1].brand)+" "+xstr(val[1].model_name)+" "+xstr(val[1].model_number)+" "+xstr(val[1].color))
        temp.append(val[0].ourInventory)
        flipkartPricing[val[0].item_id] = temp
    mpHistoryItems[:] = []
    mpItems = session.query(MarketplaceItems).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()
    for val in mpItems:
        saholicPricing[val.itemId] = val.currentSp
    mpItems[:] = []
    mismatches = []
    for k,v in flipkartPricing.iteritems():
        flipkartSellingPrice = v[0]
        ourSellingPrice = saholicPricing.get(k)
        if flipkartSellingPrice is not None and not((ourSellingPrice - flipkartSellingPrice >= -3) and (ourSellingPrice - flipkartSellingPrice <=3)):
            mismatches.append(k)
    print "mismatches are ",mismatches
    if len(mismatches)==0:
        return
    for item in mismatches:
        netInventory=''
        if not inventoryMap.has_key(item):
            netInventory='Info Not Available'
        else:
            netInventory = str(getNetAvailability(inventoryMap.get(item)))
        message+="""<tr>
            <td style="text-align:center">"""+str(item)+"""</td>
            <td style="text-align:center">"""+str((flipkartPricing.get(item))[1])+"""</td>
            <td style="text-align:center">"""+str(saholicPricing.get(item))+"""</td>
            <td style="text-align:center">"""+str((flipkartPricing.get(item))[0])+"""</td>
            <td style="text-align:center">"""+str((flipkartPricing.get(item))[2])+"""</td>
            <td style="text-align:center">"""+netInventory+"""</td>
            <td style="text-align:center">"""+getOosString((itemSaleMap.get(item))[1])+"""</td>
            </tr>"""
    message+="""</tbody></table></body></html>"""
    print message
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@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']
    msg = MIMEMultipart()
    msg['Subject'] = "Flipkart Price Mismatch" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Flipkart Price Mismatch" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send Flipkart Price Mismatch mail.Lets try local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."
            
def sendAlertForNegativeMargins(timestamp):
    xstr = lambda s: s or ""
    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()
    if len(negativeMargins) == 0:
        return
    message="""<html>
            <body>
            <h3 style="color:red;font-weight:bold;">Flipkart Negative Margins</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Product Name</th>
            <th>SP</th>
            <th>TP</th>
            <th>Lowest Possible SP</th>
            <th>Lowest Possible TP</th>
            <th>Margin</th>
            <th>Margin %</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Flipkart Inventory</th>
            <th>Total Inventory</th>
            <th>Sales History</th>
            </tr></thead>
            <tbody>"""
    for item in negativeMargins:
        mpHistory = item[0]
        catItem = item[1]
        netInventory=''
        if not inventoryMap.has_key(mpHistory.item_id):
            netInventory='Info Not Available'
        else:
            netInventory = str(getNetAvailability(inventoryMap.get(mpHistory.item_id)))
        mpItem = MarketplaceItems.get_by(itemId=mpHistory.item_id,source=OrderSource.FLIPKART)
        message+="""<tr>
            <td style="text-align:center">"""+str(mpHistory.item_id)+"""</td>
            <td style="text-align:center">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.ourSellingPrice)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.ourTp)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.lowestPossibleSp)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.lowestPossibleTp)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.margin)+"""</td>
            <td style="text-align:center">"""+str(round((mpHistory.margin/mpHistory.ourSellingPrice)*100,1))+" %"+"""</td>
            <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
            <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
            <td style="text-align:center">"""+str(mpHistory.ourInventory)+"""</td>
            <td style="text-align:center">"""+netInventory+"""</td>
            <td style="text-align:center">"""+getOosString((itemSaleMap.get(mpHistory.item_id))[1])+"""</td>
            </tr>"""
    message+="""</tbody></table></body></html>"""
    print message
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@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']
    msg = MIMEMultipart()
    msg['Subject'] = "Flipkart Negative Margin" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Flipkart Negative Margin" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send Flipkart Negative margin mail.Lets try local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."

def sendAlertForCompetitiveNoInventory(timestamp):
    xstr = lambda s: s or ""
    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()
    if len(competitiveNoInv) == 0:
        return
    message="""<html>
            <body>
            <h3 style="color:red;font-weight:bold;">Flipkart Competitive But No Inventory</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Product Name</th>
            <th>SP</th>
            <th>TP</th>
            <th>Lowest Possible SP</th>
            <th>Lowest Possible TP</th>
            <th>Lowest Seller</th>
            <th>Lowest Seller SP</th>
            <th>Margin</th>
            <th>Margin %</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Flipkart Inventory</th>
            <th>Total Inventory</th>
            <th>Sales History</th>
            </tr></thead>
            <tbody>"""
    for item in competitiveNoInv:
        mpHistory = item[0]
        catItem = item[1]
        netInventory=''
        if not inventoryMap.has_key(mpHistory.item_id):
            netInventory='Info Not Available'
        else:
            netInventory = str(getNetAvailability(inventoryMap.get(mpHistory.item_id)))
        mpItem = MarketplaceItems.get_by(itemId=mpHistory.item_id,source=OrderSource.FLIPKART)
        message+="""<tr>
            <td style="text-align:center">"""+str(mpHistory.item_id)+"""</td>
            <td style="text-align:center">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.ourSellingPrice)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.ourTp)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.lowestPossibleSp)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.lowestPossibleTp)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.lowestSellerName)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.lowestSellingPrice)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.margin)+"""</td>
            <td style="text-align:center">"""+str(round((mpHistory.margin/mpHistory.ourSellingPrice)*100,1))+" %"+"""</td>
            <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
            <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
            <td style="text-align:center">"""+str(mpHistory.ourInventory)+"""</td>
            <td style="text-align:center">"""+netInventory+"""</td>
            <td style="text-align:center">"""+getOosString((itemSaleMap.get(mpHistory.item_id))[1])+"""</td>
            </tr>"""
    message+="""</tbody></table></body></html>"""
    print message
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@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']
    msg = MIMEMultipart()
    msg['Subject'] = "Flipkart Competitive But No Inventory" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Flipkart Competitive But No Inventory" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send Flipkart Competitive But No Inventory mail.Lets try local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."

def sendAlertForInactiveAutoPricing(timestamp):
    xstr = lambda s: s or ""
    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()
    if len(inactiveAutoPricing) == 0:
        return
    message="""<html>
            <body>
            <h3 style="color:red;font-weight:bold;">Flipkart Inactive Auto Pricing</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Product Name</th>
            <th>Selling Price</th>
            <th>Competitive Category</th>
            <th>Margin</th>
            <th>Margin %</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Flipkart Inventory</th>
            <th>Total Inventory</th>
            <th>Sales History</th>
            <th>Action</th>
            </tr></thead>
            <tbody>"""
    for item in inactiveAutoPricing:
        mpHistory = item[0]
        catItem = item[1]
        mpItem = item[2]
        netInventory=''
        if not inventoryMap.has_key(mpHistory.item_id):
            netInventory='Info Not Available'
        else:
            netInventory = str(getNetAvailability(inventoryMap.get(mpHistory.item_id)))
        if (mpHistory.competitiveCategory==2):
            decision="Auto Increment"
        elif (mpHistory.competitiveCategory in (3,8)):
            decision="Auto Decrement"
        else:
            decision=""
        message+="""<tr>
            <td style="text-align:center">"""+str(mpHistory.item_id)+"""</td>
            <td style="text-align:center">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.ourSellingPrice)+"""</td>
            <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(mpHistory.competitiveCategory))+"""</td>
            <td style="text-align:center">"""+str(mpHistory.margin)+"""</td>
            <td style="text-align:center">"""+str(round((mpHistory.margin/mpHistory.ourSellingPrice)*100,1))+" %"+"""</td>
            <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
            <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
            <td style="text-align:center">"""+str(mpHistory.ourInventory)+"""</td>
            <td style="text-align:center">"""+netInventory+"""</td>
            <td style="text-align:center">"""+getOosString((itemSaleMap.get(mpHistory.item_id))[1])+"""</td>
            <td style="text-align:center">"""+decision+"""</td>
            </tr>"""
    message+="""</tbody></table></body></html>"""
    print message
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@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']
    msg = MIMEMultipart()
    msg['Subject'] = "Flipkart Auto Pricing Inactive" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Flipkart Auto Pricing Inactive" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send Flipkart Auto Pricing Inactive mail.Lets try local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."

def commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp):
    catalog_client = CatalogClient().get_client()
    inventory_client = InventoryClient().get_client()
    for item in successfulAutoDecrease:
        it = Item.query.filter_by(id=item.item_id).one()
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
        warehouse = inventory_client.getWarehouse(fkItem.warehouseId)
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.proposedSellingPrice))
        mpItem.currentTp = getNewOurTp(mpItem,math.ceil(item.proposedSellingPrice))
        mpItem.currentSp = math.ceil(item.proposedSellingPrice)
        fkItem.commissionValue = round((mpItem.commission/100)*(mpItem.currentSp),2)
        fkItem.serviceTaxValue = round((mpItem.serviceTax/100)*(fkItem.commissionValue+mpItem.courierCost),2)
        fkItem.updatedOn = timestamp
        fkItem.priceUpdatedBy = 'SYSTEM'
        mpItem.minimumPossibleTp = getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,mpItem.currentSp) 
        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,item.ourNlc,vatRate)
        addHistory(fkItem,mpItem)
        markStatusForMarketplaceItems(fkItem,mpItem)
    session.commit()
    for item in successfulAutoIncrease:
        it = Item.query.filter_by(id=item.item_id).one()
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.FLIPKART)
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
        addHistory(fkItem,mpItem)
        warehouse = inventory_client.getWarehouse(fkItem.warehouseId)
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
        mpItem.currentTp = getNewOurTp(mpItem,math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
        mpItem.currentSp = math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice))
        fkItem.commissionValue = round((mpItem.commission/100)*(mpItem.currentSp),2)
        fkItem.serviceTaxValue = round((mpItem.serviceTax/100)*(fkItem.commissionValue+mpItem.courierCost),2)
        fkItem.updatedOn = timestamp
        fkItem.priceUpdatedBy = 'SYSTEM'
        mpItem.minimumPossibleTp = getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,mpItem.currentSp) 
        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,item.ourNlc,vatRate)
        addHistory(fkItem,mpItem)
        markStatusForMarketplaceItems(fkItem,mpItem)
    session.commit()
    
def addHistory(fkItem,mpItem):
    itemHistory = MarketPlaceUpdateHistory()
    itemHistory.item_id = fkItem.item_id
    itemHistory.source = OrderSource.FLIPKART
    itemHistory.exceptionPrice = fkItem.exceptionPrice
    itemHistory.warehouseId = fkItem.warehouseId
    itemHistory.isListedOnSource = fkItem.isListedOnFlipkart
    itemHistory.transferPrice = mpItem.currentTp
    itemHistory.sellingPrice = mpItem.currentSp
    itemHistory.courierCost = mpItem.courierCost
    itemHistory.commission = fkItem.commissionValue
    itemHistory.serviceTax = fkItem.serviceTaxValue
    itemHistory.suppressPriceFeed = fkItem.suppressPriceFeed
    itemHistory.suppressInventoryFeed = fkItem.suppressInventoryFeed
    itemHistory.updatedOn = fkItem.updatedOn
    itemHistory.maxNlc = fkItem.maxNlc
    itemHistory.skuAtSource = fkItem.skuAtFlipkart
    itemHistory.marketPlaceSerialNumber = fkItem.flipkartSerialNumber
    itemHistory.priceUpdatedBy = fkItem.updatedBy
    itemHistory.courierCostMarketplace = mpItem.courierCostMarketplace

def markStatusForMarketplaceItems(fkItem,mpItem):
    markUpdatedItem = MarketPlaceItemPrice.query.filter(MarketPlaceItemPrice.item_id==fkItem.item_id).filter(MarketPlaceItemPrice.source==mpItem.source).first()
    if markUpdatedItem is None:
        marketPlaceItemPrice = MarketPlaceItemPrice()
        marketPlaceItemPrice.item_id = fkItem.item_id
        marketPlaceItemPrice.source = mpItem.source
        marketPlaceItemPrice.lastUpdatedOn = fkItem.updatedOn
        marketPlaceItemPrice.sellingPrice = mpItem.currentSp
        marketPlaceItemPrice.suppressPriceFeed = fkItem.suppressPriceFeed
        marketPlaceItemPrice.isListedOnSource = fkItem.isListedOnFlipkart
    else:
        if (markUpdatedItem.sellingPrice!=mpItem.currentSp or markUpdatedItem.suppressPriceFeed!=fkItem.suppressPriceFeed or markUpdatedItem.isListedOnSource!=fkItem.isListedOnFlipkart):
            markUpdatedItem.lastUpdatedOn = fkItem.updatedOn
        markUpdatedItem.sellingPrice = mpItem.currentSp
        markUpdatedItem.suppressPriceFeed = fkItem.suppressPriceFeed
        markUpdatedItem.isListedOnSource = fkItem.isListedOnFlipkart

    

def updatePriceOnFlipkart(successfulAutoDecrease,successfulAutoIncrease):
    if syncPrice=='false':
        return
    url = 'http://support.shop2020.in:8080/Support/reports'
    br = getBrowserObject()
    br.open(url)
    br.select_form(nr=0)
    br.form['username'] = "manoj"
    br.form['password'] = "man0j"
    br.submit()
    for item in successfulAutoDecrease:
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
        sellingPrice =  str(math.ceil(item.proposedSellingPrice))
        flipkartSku = fkItem.skuAtFlipkart
        if fkItem.isListedOnFlipkart:
            updateUrl = 'http://support.shop2020.in:8080/Support/flipkart-list!updateForAutoPricing?sellingPrice=%s&fkItemCode=%s&itemId=%s'%(sellingPrice,flipkartSku,str(item.item_id))
            br.open(updateUrl)
    for item in successfulAutoIncrease:
        fkItem = FlipkartItem.get_by(item_id=item.item_id)
        sellingPrice =  str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
        flipkartSku = fkItem.skuAtFlipkart
        if fkItem.isListedOnFlipkart:
            updateUrl = 'http://support.shop2020.in:8080/Support/flipkart-list!updateForAutoPricing?sellingPrice=%s&fkItemCode=%s&itemId=%s'%(sellingPrice,flipkartSku,str(item.item_id))
            br.open(updateUrl)
    
def main():
    parser = optparse.OptionParser()
    parser.add_option("-t", "--type", dest="runType",
                   default="FULL", type="string",
                   help="Run type FULL or FAVOURITE")
    (options, args) = parser.parse_args()
    if options.runType not in ('FULL','FAVOURITE'):
        print "Run type argument illegal."
        sys.exit(1)
    timestamp = datetime.now()
    previousProcessingTimestamp = session.query(func.max(MarketPlaceHistory.timestamp)).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).one()
    itemInfo= populateStuff(options.runType,timestamp)
    itemsPopulated = 0
    while (len(itemInfo)>0):
        itemsPopulated = threadsToSpawn(options.runType,itemInfo,itemsPopulated)
        cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin, cheapButNotPref, prefButNotCheap = decideCategory(itemInfo[0:itemsPopulated])
        itemInfo[0:itemsPopulated] = []
        commitExceptionList(exceptionItems,timestamp)
        commitCantCompete(cantCompete,timestamp)
        commitBuyBox(buyBoxItems,timestamp)
        commitCompetitive(competitive,timestamp)
        commitCompetitiveNoInventory(competitiveNoInventory,timestamp)
        commitNegativeMargin(negativeMargin,timestamp)
        commitCheapButNotPref(cheapButNotPref,timestamp)
        commitPrefButNotCheap(prefButNotCheap, timestamp)
        cantCompete[:], buyBoxItems[:], competitive[:], competitiveNoInventory[:], exceptionItems[:], negativeMargin[:], cheapButNotPref[:], prefButNotCheap[:] =[],[],[],[],[],[],[],[]
        collected = gc.collect()
        print "Garbage collector: collected %d objects." % (collected)
        
    successfulAutoDecrease = fetchItemsForAutoDecrease(timestamp)
    successfulAutoIncrease = fetchItemsForAutoIncrease(timestamp)
    if options.runType=='FULL':
        previousAutoFav, nowAutoFav = markAutoFavourite()
    if options.runType =='FULL':
        write_report(previousAutoFav,nowAutoFav,timestamp,options.runType)
    else:
        write_report(None,None,timestamp,options.runType)
    if options.runType=='FULL':
        cheapButNotPrefAlert(timestamp)
        sendPricingMismatch(timestamp)
        sendAlertForNegativeMargins(timestamp)
        sendAlertForCompetitiveNoInventory(timestamp)
        sendAlertForInactiveAutoPricing(timestamp)
    commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp)
    sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease)
    updatePriceOnFlipkart(successfulAutoDecrease,successfulAutoIncrease)
    if previousProcessingTimestamp[0] is not None:
        processLostBuyBoxItems(previousProcessingTimestamp[0],timestamp)

if __name__ == '__main__':
    main()