Subversion Repositories SmartDukaan

from elixir import *
from sqlalchemy.sql import or_ ,func
from shop2020.config.client.ConfigClient import ConfigClient
from shop2020.model.v1.catalog.impl import DataService
from shop2020.model.v1.catalog.impl.DataService import SnapdealItem, MarketplaceItems, Item, \
Category, SourcePercentageMaster, MarketPlaceHistory
from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
from shop2020.thriftpy.model.v1.catalog.ttypes import CompetitionCategory, CompetitionBasis, SalesPotential,\
Decision, RunType
from shop2020.clients.CatalogClient import CatalogClient
from shop2020.clients.InventoryClient import InventoryClient
import urllib2
import time
from datetime import date, datetime, timedelta
from shop2020.utils import EmailAttachmentSender
from shop2020.utils.EmailAttachmentSender import get_attachment_part
import math
import simplejson as json
import xlwt
import optparse
import sys

config_client = ConfigClient()
host = config_client.get_property('staging_hostname')
DataService.initialize(db_hostname=host)
import logging
lgr = logging.getLogger()
lgr.setLevel(logging.DEBUG)
fh = logging.FileHandler('snapdeal-history.log')
fh.setLevel(logging.INFO)
frmt = logging.Formatter('%(asctime)s - %(name)s - %(message)s')
fh.setFormatter(frmt)
lgr.addHandler(fh)

inventoryMap = {}
itemSaleMap = {}

class __Exception:
    SERVER_SIDE=1
    

class __SnapdealDetails:
    def __init__(self, ourSp, ourInventory, otherInventory, rank, lowestSellerName, lowestSellerCode,lowestSp,secondLowestSellerName, secondLowestSellerCode,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice, totalSeller):
        self.ourSp = ourSp
        self.ourOfferPrice = ourOfferPrice
        self.ourInventory = ourInventory
        self.otherInventory = otherInventory
        self.rank = rank
        self.lowestSellerName = lowestSellerName
        self.lowestSellerCode = lowestSellerCode 
        self.lowestSp = lowestSp
        self.lowestOfferPrice = lowestOfferPrice
        self.secondLowestSellerName = secondLowestSellerName
        self.secondLowestSellerCode = secondLowestSellerCode
        self.secondLowestSellerSp = secondLowestSellerSp
        self.secondLowestSellerOfferPrice = secondLowestSellerOfferPrice
        self.secondLowestSellerInventory = secondLowestSellerInventory
        self.totalSeller = totalSeller

class __SnapdealItemInfo:
    
    def __init__(self, supc, nlc, courierCost, item_id, product_group, brand, model_name, model_number, color, weight, parent_category, risky, warehouseId, vatRate, runType, parent_category_name):
        self.supc = supc
        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
        
class __SnapdealPricing:
    
    def __init__(self, ourSp, ourTp, lowestTp, lowestPossibleTp, competitionBasis, secondLowestSellerTp, lowestPossibleSp):
        self.ourTp = ourTp
        self.lowestTp = lowestTp
        self.lowestPossibleTp = lowestPossibleTp
        self.competitionBasis = competitionBasis
        self.ourSp = ourSp
        self.secondLowestSellerTp = secondLowestSellerTp
        self.lowestPossibleSp = lowestPossibleSp
        
def fetchItemsForAutoDecrease(time):
    autoDecrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE)\
    .filter(MarketplaceItems.source==OrderSource.SNAPDEAL).filter(MarketplaceItems.autoDecrement==True).all()
    #autoDecrementItems = MarketplaceItems.query.filter(MarketplaceItems.autoDecrement==True).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()
    inventory_client = InventoryClient().get_client()
    global inventoryMap
    inventoryMap = inventory_client.getInventorySnapshot(0)
    for autoDecrementItem in autoDecrementItems:
        #mpHistory = MarketPlaceHistory.get_by(source=OrderSource.SNAPDEAL,item_id=autoDecrementItem.itemId,timestamp=time)
        if not autoDecrementItem.competitiveCategory == CompetitionCategory.COMPETITIVE:
            markReasonForMpItem(autoDecrementItem,'Category is '+CompetitionCategory._VALUES_TO_NAMES.get(autoDecrementItem.competitiveCategory),Decision.AUTO_DECREMENT_FAILED)
            continue
        if not autoDecrementItem.risky:
            markReasonForMpItem(autoDecrementItem,'Item is not risky',Decision.AUTO_DECREMENT_FAILED)
            continue
        if autoDecrementItem.proposedSellingPrice >= autoDecrementItem.ourSellingPrice:
            markReasonForMpItem(autoDecrementItem,'Proposed SP greater than current SP',Decision.AUTO_DECREMENT_FAILED)
            continue
        if autoDecrementItem.otherInventory < 3:
            markReasonForMpItem(autoDecrementItem,'Competition stock is not enough',Decision.AUTO_DECREMENT_FAILED)
            continue
        #oosStatus = inventory_client.getOosStatusesForXDaysForItem(autoDecrementItem.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 (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:
                continue
            totalAvailability = totalAvailability+availability
        for warehouse,reserve in reserveMap.iteritems():
            if warehouse==16:
                continue
            totalReserved = totalReserved+reserve
        if (totalAvailability-totalReserved)<=0:
            markReasonForMpItem(autoDecrementItem,'Net availability is 0',Decision.AUTO_DECREMENT_FAILED)
            continue
        #if (avgSalePerDay==0):  #exclude
        #    markReasonForMpItem(autoDecrementItem,'Average sale per day is zero',Decision.AUTO_DECREMENT_FAILED)
        #    continue
        avgSalePerDay = (itemSaleMap.get(autoDecrementItem.item_id))[2]
        try:
            daysOfStock = (float(totalAvailability-totalReserved))/avgSalePerDay
        except ZeroDivisionError,e:
            lgr.info("Infinite days of stock for item "+str(autoDecrementItem.item_id))
            daysOfStock = float("inf")
        if daysOfStock<2:
            markReasonForMpItem(autoDecrementItem,'Our stock is not enough',Decision.AUTO_DECREMENT_FAILED)
            continue

        autoDecrementItem.competitorEnoughStock = True
        autoDecrementItem.ourEnoughStock = True
        #autoDecrementItem.avgSales = avgSalePerDay
        autoDecrementItem.decision = Decision.AUTO_DECREMENT_SUCCESS
        autoDecrementItem.reason = 'All conditions for auto decrement true'
        lgr.info("Auto decrement success for itemId "+str(autoDecrementItem.item_id)+" Days of stock "+str(daysOfStock)+" avg sales "+str(avgSalePerDay))
    session.commit()
    
def fetchItemsForAutoIncrease(time):
    autoIncrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX)\
    .filter(MarketplaceItems.source==OrderSource.SNAPDEAL).filter(MarketplaceItems.autoIncrement==True).all()
    #autoIncrementItems = MarketplaceItems.query.filter(MarketplaceItems.autoIncrement==True).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()
    #inventory_client = InventoryClient().get_client()
    for autoIncrementItem in autoIncrementItems:
        #mpHistory = MarketPlaceHistory.get_by(source=OrderSource.SNAPDEAL,item_id=autoIncrementItem.itemId,timestamp=time)
        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
        #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 (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:
                continue
            totalAvailability = totalAvailability+availability
        for warehouse,reserve in reserveMap.iteritems():
            if warehouse==16:
                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.avgSales = avgSalePerDay
        autoIncrementItem.decision = Decision.AUTO_INCREMENT_SUCCESS
        autoIncrementItem.reason = 'All conditions for auto increment true'
        lgr.info("Auto increment success for itemId "+str(autoIncrementItem.item_id)+" Days of stock "+str(daysOfStock)+" avg sales "+str(avgSalePerDay))
    session.commit()
        
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 avgSalePerDay

def getNetAvailability(itemInventory):
    totalAvailability, totalReserved = 0,0
    availableMap  = itemInventory.availability
    reserveMap = itemInventory.reserved
    for warehouse,availability in availableMap.iteritems():
        if warehouse==16:
            continue
        totalAvailability = totalAvailability+availability
    for warehouse,reserve in reserveMap.iteritems():
        if warehouse==16:
            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

def markAutoFavourite():
    previouslyAutoFav = []
    nowAutoFav = []
    marketplaceItems = session.query(MarketplaceItems).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).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.SNAPDEAL).filter(MarketPlaceHistory.run==RunType.FULL).filter(MarketPlaceHistory.timestamp.between (fromDate,toDate)).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
    toUpdate = [key for key, value in itemSaleMap.items() if value[3] >= 3]
    buyBoxLast3days = []
    for item in items:
        buyBoxLast3days.append(item[0])
    for marketplaceItem in marketplaceItems:
        reason = ""
        toMark = False
        if marketplaceItem.itemId in toUpdate:
            toMark = True
            reason+="Average sale is greater than 3 for last five days (Snapdeal)."
        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 markReasonForMpItem(mpHistory,reason,decision):
    mpHistory.decision = decision
    mpHistory.reason = reason
        
def fetchDetails(supc_code):
    url="http://www.snapdeal.com/json/gvbps?supc=%s&catId=91"%(supc_code)
    print url
    time.sleep(1)
    req = urllib2.Request(url)
    response = urllib2.urlopen(req)
    json_input = response.read()
    vendorInfo = json.loads(json_input)
    rank ,otherInventory ,ourInventory, ourOfferPrice, ourSp, iterator, secondLowestSellerSp, secondLowestSellerInventory, \
    lowestOfferPrice,  secondLowestSellerOfferPrice = (0,)*10
    lowestSellerName , lowestSellerCode, secondLowestSellerName, secondLowestSellerCode=('',)*4
    for vendor in vendorInfo:
        if iterator == 0:
            lowestSellerName = vendor['vendorDisplayName']
            lowestSellerCode = vendor['vendorCode']
            try:
                lowestSp = vendor['sellingPriceBefIntCashBack']
            except:
                lowestSp = vendor['sellingPrice']
            lowestOfferPrice = vendor['sellingPrice']
            
        if iterator ==1:
            secondLowestSellerName = vendor['vendorDisplayName']
            secondLowestSellerCode =vendor['vendorCode']
            try:
                secondLowestSellerSp = vendor['sellingPriceBefIntCashBack']
            except:
                secondLowestSellerSp = vendor['sellingPrice'] 
            secondLowestSellerOfferPrice = vendor['sellingPrice'] 
            secondLowestSellerInventory = vendor['buyableInventory']
            
        if vendor['vendorDisplayName'] == 'MobilesnMore':
            ourInventory = vendor['buyableInventory']
            try:
                ourSp = vendor['sellingPriceBefIntCashBack']
            except:
                ourSp = vendor['sellingPrice']
            ourOfferPrice = vendor['sellingPrice']
            rank = iterator +1
        else:
            if rank==0:
                otherInventory = otherInventory +vendor['buyableInventory']
        iterator+=1
    snapdealDetails = __SnapdealDetails(ourSp,ourInventory,otherInventory,rank,lowestSellerName, lowestSellerCode,lowestSp,secondLowestSellerName,secondLowestSellerCode,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice,len(vendorInfo))
    return snapdealDetails        
        

def populateStuff(runType):
    itemInfo = []
    if runType=='FAVOURITE':
        items = session.query(SnapdealItem).join((MarketplaceItems,SnapdealItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).\
        filter(or_(MarketplaceItems.autoFavourite==True, MarketplaceItems.manualFavourite==True)).all()
    else:
        items = session.query(SnapdealItem).all()
    spm = SourcePercentageMaster.get_by(source=OrderSource.SNAPDEAL)
    for snapdeal_item in items:
        it = Item.query.filter_by(id=snapdeal_item.item_id).one()
        category = Category.query.filter_by(id=it.category).one()
        parent_category = Category.query.filter_by(id=category.parent_category_id).first()
        snapdealItemInfo = __SnapdealItemInfo(snapdeal_item.supc, snapdeal_item.maxNlc,snapdeal_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, snapdeal_item.warehouseId, None, runType, parent_category.display_name)
        itemInfo.append(snapdealItemInfo)
    return itemInfo, spm

    
def decideCategory(itemInfo,spm):
    global itemSaleMap
    cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin = [],[],[],[],[],[]
    catalog_client = CatalogClient().get_client()
    inventory_client = InventoryClient().get_client()
    
    for val in itemInfo:
        try:
            snapdealDetails = fetchDetails(val.supc)
        except:
            exceptionItems.append(val)
            continue
        mpItem = MarketplaceItems.get_by(itemId=val.item_id,source=OrderSource.SNAPDEAL)
        warehouse = inventory_client.getWarehouse(val.warehouseId)
        
        itemSaleList = []
        oosForAllSources = inventory_client.getOosStatusesForXDaysForItem(val.item_id, 0, 3)
        oosForSnapdeal = inventory_client.getOosStatusesForXDaysForItem(val.item_id, OrderSource.SNAPDEAL, 5)
        itemSaleList.append(oosForAllSources)
        itemSaleList.append(oosForSnapdeal)
        itemSaleList.append(calculateAverageSale(oosForAllSources))
        itemSaleList.append(calculateAverageSale(oosForSnapdeal))
        itemSaleMap[val.item_id]=itemSaleList
        
        if snapdealDetails.rank==0:
            snapdealDetails.ourSp = mpItem.currentSp
            snapdealDetails.ourOfferPrice = mpItem.currentSp
            ourSp = mpItem.currentSp
        else:
            ourSp = snapdealDetails.ourSp
        vatRate = catalog_client.getVatPercentageForItem(val.item_id, warehouse.stateId, snapdealDetails.ourSp)
        val.vatRate = vatRate
        if (snapdealDetails.rank==1):
            temp=[]
            temp.append(snapdealDetails)
            temp.append(val)
            if (snapdealDetails.secondLowestSellerOfferPrice == snapdealDetails.secondLowestSellerSp) and snapdealDetails.ourOfferPrice==snapdealDetails.ourSp:
                competitionBasis = 'SP'
            else:
                competitionBasis = 'TP'
            secondLowestTp=0 if snapdealDetails.totalSeller==1 else getOtherTp(snapdealDetails,val,spm)
            snapdealPricing = __SnapdealPricing(snapdealDetails.ourSp,getOurTp(snapdealDetails,val,spm,mpItem),None,getLowestPossibleTp(snapdealDetails,val,spm,mpItem),competitionBasis,secondLowestTp,getLowestPossibleSp(snapdealDetails,val,spm,mpItem))
            temp.append(snapdealPricing)
            temp.append(mpItem)
            buyBoxItems.append(temp)
            continue
        
        
        lowestTp = getOtherTp(snapdealDetails,val,spm)
        ourTp = getOurTp(snapdealDetails,val,spm,mpItem)
        lowestPossibleTp = getLowestPossibleTp(snapdealDetails,val,spm,mpItem)
        lowestPossibleSp = getLowestPossibleSp(snapdealDetails,val,spm,mpItem)
        
        if (ourTp<lowestPossibleTp):
            temp=[]
            temp.append(snapdealDetails)
            temp.append(val)
            snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,None,None,None)
            temp.append(snapdealPricing)
            negativeMargin.append(temp)
            continue
            
        if (snapdealDetails.lowestOfferPrice == snapdealDetails.lowestSp) and snapdealDetails.ourOfferPrice == snapdealDetails.ourSp:
            competitionBasis ='SP'
        else:
            competitionBasis ='TP'
        
        if competitionBasis=='SP':
            if snapdealDetails.lowestSp > lowestPossibleSp and snapdealDetails.ourInventory!=0:
                temp=[]
                temp.append(snapdealDetails)
                temp.append(val)
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'SP',None,lowestPossibleSp)
                temp.append(snapdealPricing)
                temp.append(mpItem)
                competitive.append(temp)
                continue
        else:
            if lowestTp > lowestPossibleTp and snapdealDetails.ourInventory!=0:
                temp=[]
                temp.append(snapdealDetails)
                temp.append(val)
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'TP',None,lowestPossibleSp)
                temp.append(snapdealPricing)
                temp.append(mpItem)
                competitive.append(temp)
                continue
        
        if competitionBasis=='SP':
            if snapdealDetails.lowestSp > lowestPossibleSp and snapdealDetails.ourInventory==0:
                temp=[]
                temp.append(snapdealDetails)
                temp.append(val)
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'SP',None,lowestPossibleSp)
                temp.append(snapdealPricing)
                temp.append(mpItem)
                competitiveNoInventory.append(temp)
                continue
        else:
            if lowestTp > lowestPossibleTp and snapdealDetails.ourInventory==0:
                temp=[]
                temp.append(snapdealDetails)
                temp.append(val)
                snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,'TP',None,lowestPossibleSp)
                temp.append(snapdealPricing)
                temp.append(mpItem)
                competitiveNoInventory.append(temp)
                continue
        
        temp=[]
        temp.append(snapdealDetails)
        temp.append(val)
        snapdealPricing = __SnapdealPricing(ourSp,ourTp,lowestTp,lowestPossibleTp,competitionBasis,None,lowestPossibleSp)
        temp.append(snapdealPricing)
        temp.append(mpItem)
        cantCompete.append(temp)
    
    return cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin

def writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, previousAutoFav, nowAutoFav,timestamp):
    wbk = xlwt.Workbook()
    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, "SUPC", 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, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 10, "Our Offer Price", heading_xf)
    sheet.write(0, 12, "Our Rank", heading_xf)
    sheet.write(0, 13, "Lowest Seller", heading_xf)
    sheet.write(0, 14, "Lowest SP", heading_xf)
    sheet.write(0, 15, "Lowest TP", heading_xf)
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 19, "Our Net Availability",heading_xf)
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
    sheet.write(0, 21, "Average Sale", heading_xf)
    sheet.write(0, 22, "Our NLC", heading_xf)
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
    sheet.write(0, 25, "Competition Basis ", heading_xf)
    sheet.write(0, 26, "Target TP", heading_xf)
    sheet.write(0, 27, "Target SP", heading_xf)  
    sheet.write(0, 28, "Target NLC", heading_xf)
    sheet.write(0, 29, "Sales Potential", heading_xf)
    sheet_iterator = 1
    for item in cantCompete:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        mpItem = item[3]
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
        sheet.write(sheet_iterator, 12, snapdealDetails.rank)
        sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
        sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
        sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
        sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
        sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
        sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
            sheet.write(sheet_iterator, 19, 'Info not available')
        else:
            sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
        sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
        sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
        sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
        sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
        sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
        sheet.write(sheet_iterator, 25, snapdealPricing.competitionBasis)
        if (snapdealPricing.competitionBasis=='SP'):
            proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
            proposed_tp = getTargetTp(proposed_sp,mpItem)
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
        else:
            proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
            proposed_sp = getTargetSp(proposed_tp,mpItem)
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
        sheet.write(sheet_iterator, 26, proposed_tp)
        sheet.write(sheet_iterator, 27, proposed_sp)
        sheet.write(sheet_iterator, 28, target_nlc)
        sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
        sheet_iterator+=1
    
    sheet = wbk.add_sheet('Lowest')

    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, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "SUPC", 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, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 10, "Our Offer Price", heading_xf)
    sheet.write(0, 12, "Our Rank", heading_xf)
    sheet.write(0, 13, "Lowest Seller", heading_xf)
    sheet.write(0, 14, "Second Lowest Seller", heading_xf)
    sheet.write(0, 15, "Second Lowest Price", heading_xf)
    sheet.write(0, 16, "Second Lowest Offer Price", heading_xf)
    sheet.write(0, 17, "Second Lowest Seller TP", heading_xf)
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 19, "Our Net Availability",heading_xf)
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
    sheet.write(0, 21, "Average Sale", heading_xf)
    sheet.write(0, 22, "Second Lowest Seller Inventory", heading_xf)
    sheet.write(0, 23, "Our NLC", heading_xf)
    sheet.write(0, 24, "Competition Basis", heading_xf)
    sheet.write(0, 25, "Target TP", heading_xf)
    sheet.write(0, 26, "Target SP", heading_xf)
    sheet.write(0, 27, "MARGIN INCREASED POTENTIAL", heading_xf)
    
    sheet_iterator = 1
    for item in buyBoxItems:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        mpItem = item[3]
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
        sheet.write(sheet_iterator, 12, snapdealDetails.rank)
        sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
        sheet.write(sheet_iterator, 14, snapdealDetails.secondLowestSellerName)
        sheet.write(sheet_iterator, 15, snapdealDetails.secondLowestSellerSp)
        sheet.write(sheet_iterator, 16, snapdealDetails.secondLowestSellerOfferPrice)
        sheet.write(sheet_iterator, 17, snapdealPricing.secondLowestSellerTp)
        sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
            sheet.write(sheet_iterator, 19, 'Info not available')
        else:
            sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
        sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
        sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
        sheet.write(sheet_iterator, 22, snapdealDetails.secondLowestSellerInventory)
        sheet.write(sheet_iterator, 23, snapdealItemInfo.nlc)
        sheet.write(sheet_iterator, 24, snapdealPricing.competitionBasis)
        if (snapdealPricing.competitionBasis=='SP'):
            proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)), snapdealPricing.lowestPossibleSp)
            proposed_tp = getTargetTp(proposed_sp,mpItem)
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
        else:
            proposed_tp  = max(snapdealPricing.secondLowestSellerTp - max((20, snapdealPricing.secondLowestSellerTp*0.002)), snapdealPricing.lowestPossibleTp)
            proposed_sp = getTargetSp(proposed_tp,mpItem)
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
        sheet.write(sheet_iterator, 25, proposed_tp)
        sheet.write(sheet_iterator, 26, proposed_sp)
        sheet.write(sheet_iterator, 27, proposed_tp - snapdealPricing.ourTp)
        sheet_iterator+=1
    
    sheet = wbk.add_sheet('Can Compete-With Inventory')

    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, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "SUPC", 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, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 10, "Our Offer Price", heading_xf)
    sheet.write(0, 12, "Our Rank", heading_xf)
    sheet.write(0, 13, "Lowest Seller", heading_xf)
    sheet.write(0, 14, "Lowest SP", heading_xf)
    sheet.write(0, 15, "Lowest TP", heading_xf)
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 19, "Our Net Availability",heading_xf)
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
    sheet.write(0, 21, "Average Sale", heading_xf)
    sheet.write(0, 22, "Our NLC", heading_xf)
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
    sheet.write(0, 25, "Competition Basis ", heading_xf)
    sheet.write(0, 26, "Target TP", heading_xf)
    sheet.write(0, 27, "Target SP", heading_xf)  
    sheet.write(0, 28, "Sales Potential", heading_xf)
    
    sheet_iterator = 1
    for item in competitive:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        mpItem = item[3]
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
        sheet.write(sheet_iterator, 12, snapdealDetails.rank)
        sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
        sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
        sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
        sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
        sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
        sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
            sheet.write(sheet_iterator, 19, 'Info not available')
        else:
            sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
        sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
        sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
        sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
        sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
        sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
        sheet.write(sheet_iterator, 25, snapdealPricing.competitionBasis)
        if (snapdealPricing.competitionBasis=='SP'):
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
            proposed_tp = getTargetTp(proposed_sp,mpItem)
        else:
            proposed_tp  = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
            proposed_sp = getTargetSp(proposed_tp,mpItem)
        sheet.write(sheet_iterator, 26, proposed_tp)
        sheet.write(sheet_iterator, 27, proposed_sp)
        sheet.write(sheet_iterator, 28, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
        sheet_iterator+=1
    
    sheet = wbk.add_sheet('Negative Margin')

    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, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "SUPC", 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, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 12, "Lowest Possible TP", heading_xf)
    sheet.write(0, 10, "Our Offer Price", heading_xf)
    sheet.write(0, 13, "Our Rank", heading_xf)
    sheet.write(0, 14, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 15, "Net Availability", heading_xf)
    sheet.write(0, 16, "Last Five Day Sale", heading_xf)
    sheet.write(0, 17, "Average Sale", heading_xf)
    sheet.write(0, 18, "Our NLC", heading_xf)
    sheet.write(0, 19, "Margin", heading_xf)
    
    sheet_iterator=1
    for item in negativeMargin:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
        sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
        sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
        sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
        sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
        sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
        sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
        sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
        sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
        sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
        sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
        sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
        sheet.write(sheet_iterator, 12, snapdealPricing.lowestPossibleTp)
        sheet.write(sheet_iterator, 13, snapdealDetails.rank)
        sheet.write(sheet_iterator, 14, snapdealDetails.ourInventory)
        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
            sheet.write(sheet_iterator, 15, 'Info not available')
        else:
            sheet.write(sheet_iterator, 15, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
        sheet.write(sheet_iterator, 16, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
        sheet.write(sheet_iterator, 17, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
        sheet.write(sheet_iterator, 18, snapdealItemInfo.nlc)
        sheet.write(sheet_iterator, 19, snapdealPricing.ourTp - snapdealPricing.lowestPossibleTp)
        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, "Brand", heading_xf)
    sheet.write(0, 2, "Product Name", heading_xf)
    sheet.write(0, 3, "Reason", heading_xf)
    sheet_iterator=1
    for item in exceptionList:
        sheet.write(sheet_iterator, 0, item.item_id)
        sheet.write(sheet_iterator, 1, item.brand)
        sheet.write(sheet_iterator, 2, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
        sheet.write(sheet_iterator, 3, "Unable to fetch info from Snapdeal")
        sheet_iterator+=1
    
    sheet = wbk.add_sheet('Can Compete-No Inv')

    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, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "SUPC", 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, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 10, "Our Offer Price", heading_xf)
    sheet.write(0, 12, "Our Rank", heading_xf)
    sheet.write(0, 13, "Lowest Seller", heading_xf)
    sheet.write(0, 14, "Lowest SP", heading_xf)
    sheet.write(0, 15, "Lowest TP", heading_xf)
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 19, "Our Net Availability",heading_xf)
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
    sheet.write(0, 21, "Average Sale", heading_xf)
    sheet.write(0, 22, "Our NLC", heading_xf)
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
    sheet.write(0, 25, "Competition Basis ", heading_xf)
    sheet.write(0, 26, "Target TP", heading_xf)
    sheet.write(0, 27, "Target SP", heading_xf)  
    sheet.write(0, 28, "Target NLC", heading_xf)
    sheet.write(0, 29, "Sales Potential", heading_xf)
    
    sheet_iterator = 1
    for item in competitiveNoInventory:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        mpItem = item[3]
        if ((not inventoryMap.has_key(snapdealItemInfo.item_id)) or getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id))<=0):
            sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
            sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
            sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
            sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
            sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
            sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
            sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
            sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
            sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
            sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
            sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
            sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
            sheet.write(sheet_iterator, 12, snapdealDetails.rank)
            sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
            sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
            sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
            sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
            sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
            sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
            if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
                sheet.write(sheet_iterator, 19, 'Info not available')
            else:
                sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
            sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
            sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
            sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
            sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
            sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
            sheet.write(sheet_iterator, 25, snapdealPricing.competitionBasis)
            if (snapdealPricing.competitionBasis=='SP'):
                proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
                proposed_tp = getTargetTp(proposed_sp,mpItem)
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
            else:
                proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
                proposed_sp = getTargetSp(proposed_tp,mpItem)
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
            sheet.write(sheet_iterator, 26, proposed_tp)
            sheet.write(sheet_iterator, 27, proposed_sp)
            sheet.write(sheet_iterator, 28, target_nlc)
            sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
            sheet_iterator+=1
            
    sheet = wbk.add_sheet('Can Compete-No Inv On SD')

    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, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "SUPC", 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, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 10, "Our Offer Price", heading_xf)
    sheet.write(0, 12, "Our Rank", heading_xf)
    sheet.write(0, 13, "Lowest Seller", heading_xf)
    sheet.write(0, 14, "Lowest SP", heading_xf)
    sheet.write(0, 15, "Lowest TP", heading_xf)
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 19, "Our Net Availability",heading_xf)
    sheet.write(0, 20, "Last Five Day Sale", heading_xf)
    sheet.write(0, 21, "Average Sale", heading_xf)
    sheet.write(0, 22, "Our NLC", heading_xf)
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
    sheet.write(0, 25, "Competition Basis ", heading_xf)
    sheet.write(0, 26, "Target TP", heading_xf)
    sheet.write(0, 27, "Target SP", heading_xf)  
    sheet.write(0, 28, "Target NLC", heading_xf)
    sheet.write(0, 29, "Sales Potential", heading_xf)
    
    sheet_iterator = 1
    for item in competitiveNoInventory:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        mpItem = item[3]
        if (inventoryMap.has_key(snapdealItemInfo.item_id) and getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id))>0):
            sheet.write(sheet_iterator, 0, snapdealItemInfo.item_id)
            sheet.write(sheet_iterator, 1, snapdealItemInfo.parent_category_name)
            sheet.write(sheet_iterator, 2, snapdealItemInfo.product_group)
            sheet.write(sheet_iterator, 3, snapdealItemInfo.supc)
            sheet.write(sheet_iterator, 4, snapdealItemInfo.brand)
            sheet.write(sheet_iterator, 5, xstr(snapdealItemInfo.brand)+" "+xstr(snapdealItemInfo.model_name)+" "+xstr(snapdealItemInfo.model_number)+" "+xstr(snapdealItemInfo.color))
            sheet.write(sheet_iterator, 6, snapdealItemInfo.weight)
            sheet.write(sheet_iterator, 7, snapdealItemInfo.courierCost)
            sheet.write(sheet_iterator, 8, snapdealItemInfo.risky)
            sheet.write(sheet_iterator, 9, snapdealPricing.ourSp)
            sheet.write(sheet_iterator, 10, snapdealDetails.ourOfferPrice)
            sheet.write(sheet_iterator, 11, snapdealPricing.ourTp)
            sheet.write(sheet_iterator, 12, snapdealDetails.rank)
            sheet.write(sheet_iterator, 13, snapdealDetails.lowestSellerName)
            sheet.write(sheet_iterator, 14, snapdealDetails.lowestSp)
            sheet.write(sheet_iterator, 15, snapdealPricing.lowestTp)
            sheet.write(sheet_iterator, 16, snapdealDetails.lowestOfferPrice)
            sheet.write(sheet_iterator, 17, snapdealDetails.otherInventory)
            sheet.write(sheet_iterator, 18, snapdealDetails.ourInventory)
            if (not inventoryMap.has_key(snapdealItemInfo.item_id)):
                sheet.write(sheet_iterator, 19, 'Info not available')
            else:
                sheet.write(sheet_iterator, 19, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))
            sheet.write(sheet_iterator, 20, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))
            sheet.write(sheet_iterator, 21, (itemSaleMap.get(snapdealItemInfo.item_id))[3])
            sheet.write(sheet_iterator, 22, snapdealItemInfo.nlc)
            sheet.write(sheet_iterator, 23, snapdealPricing.lowestPossibleTp)
            sheet.write(sheet_iterator, 24, snapdealPricing.lowestPossibleSp)
            sheet.write(sheet_iterator, 25, snapdealPricing.competitionBasis)
            if (snapdealPricing.competitionBasis=='SP'):
                proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
                proposed_tp = getTargetTp(proposed_sp,mpItem)
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
            else:
                proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
                proposed_sp = getTargetSp(proposed_tp,mpItem)
                target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
            sheet.write(sheet_iterator, 26, proposed_tp)
            sheet.write(sheet_iterator, 27, proposed_sp)
            sheet.write(sheet_iterator, 28, target_nlc)
            sheet.write(sheet_iterator, 29, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
            sheet_iterator+=1
    
    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
    
    
    autoPricingItems = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).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, "Selling Price Updated",heading_xf)
    
    sheet_iterator=1
    for autoPricingItem in autoPricingItems:
        mpHistory = autoPricingItem[0]
        item = autoPricingItem[1]
        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, math.ceil(mpHistory.proposedSellingPrice))
        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
            sheet.write(sheet_iterator, 5, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
        sheet_iterator+=1
    
    filename = "/tmp/snapdeal-auto-" + str(timestamp) + ".xls"
    wbk.save(filename)
    EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Snapdeal Auto Pricing " + str(timestamp), "", [get_attachment_part(filename)], ["kshitij.sood@saholic.com"], [])
        
def commitExceptionList(exceptionList,timestamp):
    exceptionItems=[]
    for item in exceptionList:
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id =item.item_id
        mpHistory.source = OrderSource.SNAPDEAL 
        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 commitNegativeMargin(negativeMargin,timestamp):
    negativeMarginItems = []
    for item in negativeMargin:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id = snapdealItemInfo.item_id
        mpHistory.source = OrderSource.SNAPDEAL
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
        mpHistory.ourTp = snapdealPricing.ourTp
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
        mpHistory.ourNlc = snapdealItemInfo.nlc
        mpHistory.ourInventory = snapdealDetails.ourInventory
        mpHistory.otherInventory = snapdealDetails.otherInventory
        mpHistory.ourRank = snapdealDetails.rank
        mpHistory.risky = snapdealItemInfo.risky
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp  
        mpHistory.competitiveCategory = CompetitionCategory.NEGATIVE_MARGIN
        mpHistory.totalSeller = snapdealDetails.totalSeller
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
        negativeMarginItems.append(mpHistory) 
    session.commit()
    return negativeMarginItems
        
def commitCompetitive(competitive,timestamp):
    competitiveItems = []
    for item in competitive:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        mpItem = item[3]
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id = snapdealItemInfo.item_id
        mpHistory.source = OrderSource.SNAPDEAL
        mpHistory.lowestTp = snapdealPricing.lowestTp
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
        mpHistory.ourInventory = snapdealDetails.ourInventory
        mpHistory.otherInventory = snapdealDetails.otherInventory
        mpHistory.ourRank = snapdealDetails.rank
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
        mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE
        mpHistory.risky = snapdealItemInfo.risky
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
        mpHistory.ourTp = snapdealPricing.ourTp
        mpHistory.ourNlc = snapdealItemInfo.nlc
        if (snapdealPricing.competitionBasis=='SP'):
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
            proposed_tp = getTargetTp(proposed_sp,mpItem)
            mpHistory.proposedSellingPrice = proposed_sp
            mpHistory.proposedTp = proposed_tp
        else:
            proposed_tp  = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
            proposed_sp = getTargetSp(proposed_tp,mpItem)
            mpHistory.proposedSellingPrice = proposed_sp
            mpHistory.proposedTp = proposed_tp
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.totalSeller = snapdealDetails.totalSeller
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
        competitiveItems.append(mpHistory) 
    session.commit()
    return competitiveItems

def commitCompetitiveNoInventory(competitiveNoInventory,timestamp):
    competitiveNoInventoryItems = []
    for item in competitiveNoInventory:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        mpItem = item[3]
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id = snapdealItemInfo.item_id
        mpHistory.source = OrderSource.SNAPDEAL
        mpHistory.lowestTp = snapdealPricing.lowestTp
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
        mpHistory.ourInventory = snapdealDetails.ourInventory
        mpHistory.otherInventory = snapdealDetails.otherInventory
        mpHistory.ourRank = snapdealDetails.rank
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
        mpHistory.competitiveCategory = CompetitionCategory.COMPETITIVE_NO_INVENTORY
        mpHistory.risky = snapdealItemInfo.risky
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
        mpHistory.ourTp = snapdealPricing.ourTp
        mpHistory.ourNlc = snapdealItemInfo.nlc
        if (snapdealPricing.competitionBasis=='SP'):
            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)), snapdealPricing.lowestPossibleSp)
            proposed_tp = getTargetTp(proposed_sp,mpItem)
            mpHistory.proposedSellingPrice = proposed_sp
            mpHistory.proposedTp = proposed_tp
        else:
            proposed_tp  = max(snapdealPricing.lowestTp - max((10, snapdealPricing.lowestTp*0.001)), snapdealPricing.lowestPossibleTp)
            proposed_sp = getTargetSp(proposed_tp,mpItem)
            mpHistory.proposedSellingPrice = proposed_sp
            mpHistory.proposedTp = proposed_tp
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.totalSeller = snapdealDetails.totalSeller
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
        competitiveNoInventoryItems.append(mpHistory)
    session.commit()
    return competitiveNoInventoryItems

def commitCantCompete(cantCompete,timestamp):
    cantComepeteItems = []
    for item in cantCompete:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        mpItem = item[3]
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id = snapdealItemInfo.item_id
        mpHistory.source = OrderSource.SNAPDEAL
        mpHistory.lowestTp = snapdealPricing.lowestTp
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
        mpHistory.ourInventory = snapdealDetails.ourInventory
        mpHistory.otherInventory = snapdealDetails.otherInventory
        mpHistory.ourRank = snapdealDetails.rank
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
        mpHistory.competitiveCategory = CompetitionCategory.CANT_COMPETE
        mpHistory.risky = snapdealItemInfo.risky
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
        mpHistory.ourTp = snapdealPricing.ourTp
        mpHistory.ourNlc = snapdealItemInfo.nlc
        if (snapdealPricing.competitionBasis=='SP'):
            proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001)
            proposed_tp = getTargetTp(proposed_sp,mpItem)
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
            mpHistory.proposedSellingPrice = proposed_sp
            mpHistory.proposedTp = proposed_tp
            mpHistory.targetNlc = target_nlc
        else:
            proposed_tp  = snapdealPricing.lowestTp - max(10, snapdealPricing.lowestTp*0.001)
            proposed_sp = getTargetSp(proposed_tp,mpItem)
            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
            mpHistory.proposedSellingPrice = proposed_sp
            mpHistory.proposedTp = proposed_tp
            mpHistory.targetNlc = target_nlc
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.totalSeller = snapdealDetails.totalSeller
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
        mpHistory.salesPotential = SalesPotential._NAMES_TO_VALUES.get(getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
        cantComepeteItems.append(mpHistory)
    session.commit()
    return cantComepeteItems

def commitBuyBox(buyBoxItems,timestamp):
    buyBoxList = []
    for item in buyBoxItems:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        mpItem = item[3]
        mpHistory = MarketPlaceHistory()
        mpHistory.item_id = snapdealItemInfo.item_id
        mpHistory.source = OrderSource.SNAPDEAL
        mpHistory.lowestPossibleTp = snapdealPricing.lowestPossibleTp
        mpHistory.lowestPossibleSp = snapdealPricing.lowestPossibleSp
        mpHistory.ourInventory = snapdealDetails.ourInventory
        mpHistory.secondLowestInventory = snapdealDetails.secondLowestSellerInventory
        mpHistory.ourRank = snapdealDetails.rank
        mpHistory.competitionBasis = CompetitionBasis._NAMES_TO_VALUES.get(snapdealPricing.competitionBasis)
        mpHistory.competitiveCategory = CompetitionCategory.BUY_BOX
        mpHistory.risky = snapdealItemInfo.risky
        mpHistory.lowestOfferPrice = snapdealDetails.lowestOfferPrice
        mpHistory.lowestSellingPrice = snapdealDetails.lowestSp
        mpHistory.lowestSellerName = snapdealDetails.lowestSellerName
        mpHistory.lowestSellerCode = snapdealDetails.lowestSellerCode
        mpHistory.ourOfferPrice = snapdealDetails.ourOfferPrice
        mpHistory.ourSellingPrice = snapdealPricing.ourSp
        mpHistory.ourTp = snapdealPricing.ourTp
        mpHistory.ourNlc = snapdealItemInfo.nlc
        mpHistory.secondLowestSellerName = snapdealDetails.secondLowestSellerName
        mpHistory.secondLowestSellerCode = snapdealDetails.secondLowestSellerCode
        mpHistory.secondLowestSellingPrice = snapdealDetails.secondLowestSellerSp
        mpHistory.secondLowestOfferPrice = snapdealDetails.secondLowestSellerOfferPrice
        mpHistory.secondLowestTp = snapdealPricing.secondLowestSellerTp
        if (snapdealPricing.competitionBasis=='SP'):
            proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)), snapdealPricing.lowestPossibleSp)
            proposed_tp = getTargetTp(proposed_sp,mpItem)
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
            mpHistory.proposedSellingPrice = proposed_sp
            mpHistory.proposedTp = proposed_tp
            #mpHistory.targetNlc = target_nlc
        else:
            proposed_tp  = max(snapdealPricing.secondLowestSellerTp - max((20, snapdealPricing.secondLowestSellerTp*0.002)), snapdealPricing.lowestPossibleTp)
            proposed_sp = getTargetSp(proposed_tp,mpItem)
            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc
            mpHistory.proposedSellingPrice = proposed_sp
            mpHistory.proposedTp = proposed_tp
            #mpHistory.targetNlc = target_nlc
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.marginIncreasedPotential = proposed_tp - snapdealPricing.ourTp
        mpHistory.totalSeller = snapdealDetails.totalSeller
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[2]
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
        buyBoxList.append(mpHistory)
    session.commit()
    return buyBoxList 
        
        
def getOtherTp(snapdealDetails,val,spm):
    if val.parent_category==10011:
        commissionPercentage = spm.competitorCommissionAccessory
    else:
        commissionPercentage = spm.competitorCommissionOther
    if snapdealDetails.rank==1:
        return snapdealDetails.secondLowestSellerSp- snapdealDetails.secondLowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100));  
    return snapdealDetails.lowestSp- snapdealDetails.lowestSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100));
    
def getLowestPossibleTp(snapdealDetails,val,spm,mpItem):
    if snapdealDetails.rank==0:
        return mpItem.minimumPossibleTp
    vat = (snapdealDetails.ourSp/(1+(val.vatRate/100))-(val.nlc/(1+(val.vatRate/100))))*(val.vatRate/100);
    inHouseCost = 15+vat+(mpItem.returnProvision/100)*snapdealDetails.ourSp+mpItem.otherCost;
    lowest_possible_tp = val.nlc+inHouseCost;
    return lowest_possible_tp

def getOurTp(snapdealDetails,val,spm,mpItem):
    if snapdealDetails.rank==0:
        return mpItem.currentTp
    return snapdealDetails.ourSp- snapdealDetails.ourSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100));
    
def getLowestPossibleSp(snapdealDetails,val,spm,mpItem):
    if snapdealDetails.rank==0:
        return mpItem.minimumPossibleSp
    return (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));    
    
def getTargetTp(targetSp,mpItem):
    return targetSp- targetSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100));

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

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

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)
    itemInfo,spm= populateStuff(options.runType)
    cantCompete, buyBoxItems, competitive, \
    competitiveNoInventory, exceptionList, negativeMargin = decideCategory(itemInfo,spm)
    timestamp = datetime.now()
    exceptionItems = commitExceptionList(exceptionList,timestamp)
    cantComepeteItems = commitCantCompete(cantCompete,timestamp)
    buyBoxList = commitBuyBox(buyBoxItems,timestamp)
    competitiveItems = commitCompetitive(competitive,timestamp)
    competitiveNoInventoryItems = commitCompetitiveNoInventory(competitiveNoInventory,timestamp)
    negativeMarginItems = commitNegativeMargin(negativeMargin,timestamp)
    fetchItemsForAutoDecrease(timestamp)
    fetchItemsForAutoIncrease(timestamp)
    if options.runType=='FULL':
        previousAutoFav, nowAutoFav = markAutoFavourite()
    writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, previousAutoFav, nowAutoFav,timestamp)
    
if __name__ == '__main__':
    main()