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.impl.DataService import SnapdealItem, 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, 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

import smtplib

from email.mime.text import MIMEText

import email

from email.mime.multipart import MIMEMultipart

import email.encoders

import mechanize

import cookielib

from urllib2 import Request

config_client = ConfigClient()

host = config_client.get_property('staging_hostname')

syncPrice=config_client.get_property('sync_price_on_marketplace')

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 = {}

headers = { 

           '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-Language' : 'en-US,en;q=0.8',                     

            'Accept-Charset' : 'ISO-8859-1,utf-8;q=0.7,*;q=0.3'

        }

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, sourcePercentage):

        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

        self.sourcePercentage = sourcePercentage 

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 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 fetchItemsForAutoDecrease(time):

    successfulAutoDecrease = []

    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 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.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 autoDecrementItem.risky:

            if ((not inventoryMap.has_key(autoDecrementItem.item_id)) and autoDecrementItem.risky):

                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

            #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 and autoDecrementItem.risky:

                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'

        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.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

        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.SNAPDEAL).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.SNAPDEAL)

        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

        avgSalePerDay = (itemSaleMap.get(autoIncrementItem.item_id))[2]

        if (avgSalePerDay==0):

            markReasonForMpItem(autoIncrementItem,'Average sale per day is zero',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)) and autoIncrementItem.risky):

                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

            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'

        successfulAutoIncrease.append(autoIncrementItem)

    session.commit()

    return successfulAutoIncrease

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 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.timestamp.between (fromDate,toDate)).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).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 (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/acors/json/gvbps?supc=%s&catId=175&sort=sellingPrice"%(supc_code)

    print url

    time.sleep(1)

    req = urllib2.Request(url,headers=headers)

    response = urllib2.urlopen(req)

    json_input = response.read()

    vendorInfo = json.loads(json_input)

    print vendorInfo

    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 = str(vendor['vendorDisplayName'])

            lowestSellerCode = str(vendor['vendorCode'])

            try:

                lowestSp = vendor['sellingPriceBefIntCashBack']

            except:

                lowestSp = vendor['sellingPrice']

            lowestOfferPrice = vendor['sellingPrice']

        if iterator ==1:

            secondLowestSellerName = str(vendor['vendorDisplayName'])

            secondLowestSellerCode =str(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,str(lowestSellerName), lowestSellerCode,lowestSp,str(secondLowestSellerName),secondLowestSellerCode,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice,len(vendorInfo))

    return snapdealDetails        

def populateStuff(runType,time):

    itemInfo = []

    if runType=='FAVOURITE':

        items = session.query(SnapdealItem).join((MarketplaceItems,SnapdealItem.item_id==MarketplaceItems.itemId)).filter(MarketplaceItems.itemId==17209).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()

        srm = SourceReturnPercentage.get_by(source=OrderSource.SNAPDEAL,brand=it.brand,category_id=it.category)

        sip = SourceItemPercentage.query.filter(SourceItemPercentage.item_id==it.id).filter(SourceItemPercentage.source==OrderSource.SNAPDEAL).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.SNAPDEAL).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.SNAPDEAL)

                sourcePercentage = spm

                sourcePercentage.returnProvision = srm.returnProvision

        snapdealItemInfo = __SnapdealItemInfo(snapdeal_item.supc, snapdeal_item.maxNlc,snapdeal_item.courierCostMarketplace, 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,sourcePercentage)

        itemInfo.append(snapdealItemInfo)

    session.close()

    return itemInfo

def decideCategory(itemInfo):

    global itemSaleMap

    cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionItems, negativeMargin = [],[],[],[],[],[]

    catalog_client = CatalogClient().get_client()

    inventory_client = InventoryClient().get_client()

    for val in itemInfo:

        spm = val.sourcePercentage

        try:

            snapdealDetails = fetchDetails(val.supc)

        except Exception as e:

            print "Error while getting supc ",val.supc

            print e

            exceptionItems.append(val)

            continue

        print snapdealDetails

        sys.exit(1)

        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)

        oosForSnapdealLastDay = inventory_client.getOosStatusesForXDaysForItem(val.item_id, OrderSource.SNAPDEAL, 1)

        itemSaleList.append(oosForAllSources)

        itemSaleList.append(oosForSnapdeal)

        itemSaleList.append(calculateAverageSale(oosForAllSources))

        itemSaleList.append(calculateAverageSale(oosForSnapdeal))

        itemSaleList.append(calculateAverageSale(oosForSnapdealLastDay))

        itemSaleList.append(calculateTotalSale(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,getLowestPossibleSp(snapdealDetails,val,spm,mpItem))

            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 (snapdealDetails.lowestSp-getSubsidyDiff(snapdealDetails) > lowestPossibleSp) and snapdealDetails.ourInventory!=0:

            #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 (snapdealDetails.lowestSp-getSubsidyDiff(snapdealDetails) > lowestPossibleSp) and snapdealDetails.ourInventory==0:

            #lowest sp - subs diff < lowest pos sp 

            #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,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, "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, "Commission Rate", heading_xf)

    sheet.write(0, 10, "Return Provision", heading_xf)

    sheet.write(0, 11, "Our SP", heading_xf)

    sheet.write(0, 13, "Our TP", heading_xf)

    sheet.write(0, 12, "Our Offer Price", heading_xf)

    sheet.write(0, 14, "Our Rank", heading_xf)

    sheet.write(0, 15, "Lowest Seller", heading_xf)

    sheet.write(0, 16, "Lowest SP", heading_xf)

    sheet.write(0, 17, "Lowest TP", heading_xf)

    sheet.write(0, 18, "Lowest Offer Price", heading_xf)

    sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)

    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)

    sheet.write(0, 21, "Our Net Availability",heading_xf)

    sheet.write(0, 22, "Last Five Day Sale", heading_xf)

    sheet.write(0, 23, "Average Sale", heading_xf)

    sheet.write(0, 24, "Our NLC", heading_xf)

    sheet.write(0, 25, "Lowest Possible TP", heading_xf)

    sheet.write(0, 26, "Lowest Possible SP", heading_xf)

    sheet.write(0, 27, "Subsidy Difference", heading_xf)

    sheet.write(0, 28, "Target TP", heading_xf)

    sheet.write(0, 29, "Target SP", heading_xf)  

    sheet.write(0, 30, "Target NLC", heading_xf)

    sheet.write(0, 31, "Sales Potential", heading_xf)

    sheet_iterator = 1

    for item in cantCompete:

        snapdealDetails = item[0]

        snapdealItemInfo = item[1]

        snapdealPricing = item[2]

        mpItem = item[3]

        spmObj = snapdealItemInfo.sourcePercentage

        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, spmObj.commission)

        sheet.write(sheet_iterator, 10, spmObj.returnProvision)

        sheet.write(sheet_iterator, 11, snapdealPricing.ourSp)

        sheet.write(sheet_iterator, 12, snapdealDetails.ourOfferPrice)

        sheet.write(sheet_iterator, 13, snapdealPricing.ourTp)

        sheet.write(sheet_iterator, 14, snapdealDetails.rank)

        sheet.write(sheet_iterator, 15, snapdealDetails.lowestSellerName)

        sheet.write(sheet_iterator, 16, snapdealDetails.lowestSp)

        sheet.write(sheet_iterator, 17, snapdealPricing.lowestTp)

        sheet.write(sheet_iterator, 18, snapdealDetails.lowestOfferPrice)

        sheet.write(sheet_iterator, 19, snapdealDetails.otherInventory)

        sheet.write(sheet_iterator, 20, snapdealDetails.ourInventory)

        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):

            sheet.write(sheet_iterator, 21, 'Info not available')

        else:

            sheet.write(sheet_iterator, 21, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))

        sheet.write(sheet_iterator, 22, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))

        sheet.write(sheet_iterator, 23, (itemSaleMap.get(snapdealItemInfo.item_id))[3])

        sheet.write(sheet_iterator, 24, snapdealItemInfo.nlc)

        sheet.write(sheet_iterator, 25, snapdealPricing.lowestPossibleTp)

        sheet.write(sheet_iterator, 26, snapdealPricing.lowestPossibleSp)

        sheet.write(sheet_iterator, 27, getSubsidyDiff(snapdealDetails))

        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,snapdealPricing.ourSp)

            proposed_sp = snapdealDetails.lowestSp - max(10, snapdealDetails.lowestSp*0.001) - getSubsidyDiff(snapdealDetails)

            proposed_tp = getTargetTp(proposed_sp,mpItem)

            target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc

        sheet.write(sheet_iterator, 28, round(proposed_tp,2))

        sheet.write(sheet_iterator, 29, round(proposed_sp,2))

        sheet.write(sheet_iterator, 30, round(target_nlc,2))

        sheet.write(sheet_iterator, 31, 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, "Commission Rate", heading_xf)

    sheet.write(0, 10, "Return Provision", heading_xf)

    sheet.write(0, 11, "Our SP", heading_xf)

    sheet.write(0, 13, "Our TP", heading_xf)

    sheet.write(0, 12, "Our Offer Price", heading_xf)

    sheet.write(0, 14, "Our Rank", heading_xf)

    sheet.write(0, 15, "Lowest Seller", heading_xf)

    sheet.write(0, 16, "Second Lowest Seller", heading_xf)

    sheet.write(0, 17, "Second Lowest Price", heading_xf)

    sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)

    sheet.write(0, 19, "Second Lowest Seller TP", heading_xf)

    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)

    sheet.write(0, 21, "Our Net Availability",heading_xf)

    sheet.write(0, 22, "Last Five Day Sale", heading_xf)

    sheet.write(0, 23, "Average Sale", heading_xf)

    sheet.write(0, 24, "Second Lowest Seller Inventory", heading_xf)

    sheet.write(0, 25, "Our NLC", heading_xf)

    sheet.write(0, 26, "Subsidy Difference", heading_xf)

    sheet.write(0, 27, "Target TP", heading_xf)

    sheet.write(0, 28, "Target SP", heading_xf)

    sheet.write(0, 29, "MARGIN INCREASED POTENTIAL", heading_xf)

    sheet.write(0, 30, "Auto Pricing Decision", heading_xf)

    sheet.write(0, 31, "Reason", heading_xf)

    sheet.write(0, 32, "Updated Price", heading_xf)

    sheet_iterator = 1

    for item in buyBoxItems:

        snapdealDetails = item[0]

        snapdealItemInfo = item[1]

        snapdealPricing = item[2]

        mpItem = item[3]

        spmObj = snapdealItemInfo.sourcePercentage

        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, spmObj.commission)

        sheet.write(sheet_iterator, 10, spmObj.returnProvision)

        sheet.write(sheet_iterator, 11, snapdealPricing.ourSp)

        sheet.write(sheet_iterator, 12, snapdealDetails.ourOfferPrice)

        sheet.write(sheet_iterator, 13, snapdealPricing.ourTp)

        sheet.write(sheet_iterator, 14, snapdealDetails.rank)

        sheet.write(sheet_iterator, 15, snapdealDetails.lowestSellerName)

        sheet.write(sheet_iterator, 16, snapdealDetails.secondLowestSellerName)

        sheet.write(sheet_iterator, 17, snapdealDetails.secondLowestSellerSp)

        sheet.write(sheet_iterator, 18, snapdealDetails.secondLowestSellerOfferPrice)

        sheet.write(sheet_iterator, 19, snapdealPricing.secondLowestSellerTp)

        sheet.write(sheet_iterator, 20, snapdealDetails.ourInventory)

        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):

            sheet.write(sheet_iterator, 21, 'Info not available')

        else:

            sheet.write(sheet_iterator, 21, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))

        sheet.write(sheet_iterator, 22, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))

        sheet.write(sheet_iterator, 23, (itemSaleMap.get(snapdealItemInfo.item_id))[3])

        sheet.write(sheet_iterator, 24, snapdealDetails.secondLowestSellerInventory)

        sheet.write(sheet_iterator, 25, snapdealItemInfo.nlc)

        sheet.write(sheet_iterator, 26, getSubsidyDiff(snapdealDetails))

        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,snapdealPricing.ourSp)

            proposed_sp = max(snapdealDetails.secondLowestSellerSp - max((20, snapdealDetails.secondLowestSellerSp*0.002)) -getSubsidyDiff(snapdealDetails), snapdealPricing.lowestPossibleSp)

            proposed_tp = getTargetTp(proposed_sp,mpItem)

            #target_nlc = proposed_tp - snapdealPricing.lowestPossibleTp + snapdealItemInfo.nlc

        sheet.write(sheet_iterator, 27, round(proposed_tp,2))

        sheet.write(sheet_iterator, 28, round(proposed_sp,2))

        sheet.write(sheet_iterator, 29, round((proposed_tp - snapdealPricing.ourTp),2))

        mp_history_item = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.item_id==snapdealItemInfo.item_id).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).one()

        if mp_history_item.decision is None:

            sheet.write(sheet_iterator, 30, 'Auto Pricing Inactive')

            sheet_iterator+=1

            continue

        sheet.write(sheet_iterator, 30, Decision._VALUES_TO_NAMES.get(mp_history_item.decision))

        sheet.write(sheet_iterator, 31, mp_history_item.reason)

        if Decision._VALUES_TO_NAMES.get(mp_history_item.decision) == "AUTO_DECREMENT_SUCCESS":

            sheet.write(sheet_iterator, 32, math.ceil(mp_history_item.proposedSellingPrice))

        if Decision._VALUES_TO_NAMES.get(mp_history_item.decision) == "AUTO_INCREMENT_SUCCESS":

            sheet.write(sheet_iterator, 32, math.ceil(mp_history_item.ourSellingPrice+max(10,.01*mp_history_item.ourSellingPrice)))

        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, "Commission Rate", heading_xf)

    sheet.write(0, 10, "Return Provision", heading_xf)

    sheet.write(0, 11, "Our SP", heading_xf)

    sheet.write(0, 13, "Our TP", heading_xf)

    sheet.write(0, 12, "Our Offer Price", heading_xf)

    sheet.write(0, 14, "Our Rank", heading_xf)

    sheet.write(0, 15, "Lowest Seller", heading_xf)

    sheet.write(0, 16, "Lowest SP", heading_xf)

    sheet.write(0, 17, "Lowest TP", heading_xf)

    sheet.write(0, 18, "Lowest Offer Price", heading_xf)

    sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)

    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)

    sheet.write(0, 21, "Our Net Availability",heading_xf)

    sheet.write(0, 22, "Last Five Day Sale", heading_xf)

    sheet.write(0, 23, "Average Sale", heading_xf)

    sheet.write(0, 24, "Our NLC", heading_xf)

    sheet.write(0, 25, "Lowest Possible TP", heading_xf)

    sheet.write(0, 26, "Lowest Possible SP", heading_xf)

    sheet.write(0, 27, "Subsidy Difference", heading_xf)

    sheet.write(0, 28, "Target TP", heading_xf)

    sheet.write(0, 29, "Target SP", heading_xf)  

    sheet.write(0, 30, "Sales Potential", heading_xf)

    sheet.write(0, 31, "Auto Pricing Decision", heading_xf)

    sheet.write(0, 32, "Reason", heading_xf)

    sheet.write(0, 33, "Updated Price", heading_xf)

    sheet_iterator = 1

    for item in competitive:

        snapdealDetails = item[0]

        snapdealItemInfo = item[1]

        snapdealPricing = item[2]

        mpItem = item[3]

        spmObj = snapdealItemInfo.sourcePercentage

        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, spmObj.commission)

        sheet.write(sheet_iterator, 10, spmObj.returnProvision)

        sheet.write(sheet_iterator, 11, snapdealPricing.ourSp)

        sheet.write(sheet_iterator, 12, snapdealDetails.ourOfferPrice)

        sheet.write(sheet_iterator, 13, snapdealPricing.ourTp)

        sheet.write(sheet_iterator, 14, snapdealDetails.rank)

        sheet.write(sheet_iterator, 15, snapdealDetails.lowestSellerName)

        sheet.write(sheet_iterator, 16, snapdealDetails.lowestSp)

        sheet.write(sheet_iterator, 17, snapdealPricing.lowestTp)

        sheet.write(sheet_iterator, 18, snapdealDetails.lowestOfferPrice)

        sheet.write(sheet_iterator, 19, snapdealDetails.otherInventory)

        sheet.write(sheet_iterator, 20, snapdealDetails.ourInventory)

        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):

            sheet.write(sheet_iterator, 21, 'Info not available')

        else:

            sheet.write(sheet_iterator, 21, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))

        sheet.write(sheet_iterator, 22, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))

        sheet.write(sheet_iterator, 23, (itemSaleMap.get(snapdealItemInfo.item_id))[3])

        sheet.write(sheet_iterator, 24, snapdealItemInfo.nlc)

        sheet.write(sheet_iterator, 25, snapdealPricing.lowestPossibleTp)

        sheet.write(sheet_iterator, 26, snapdealPricing.lowestPossibleSp)

        sheet.write(sheet_iterator, 27, getSubsidyDiff(snapdealDetails))

        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,snapdealPricing.ourSp)

            proposed_sp = max(snapdealDetails.lowestSp - max((10, snapdealDetails.lowestSp*0.001)) -getSubsidyDiff(snapdealDetails), snapdealPricing.lowestPossibleSp)

            proposed_tp = getTargetTp(proposed_sp,mpItem)

        sheet.write(sheet_iterator, 28, round(proposed_tp,2))

        sheet.write(sheet_iterator, 29, round(proposed_sp,2))

        sheet.write(sheet_iterator, 30, getSalesPotential(snapdealDetails.lowestOfferPrice,snapdealItemInfo.nlc))

        mp_history_item = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.item_id==snapdealItemInfo.item_id).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).one()

        if mp_history_item.decision is None:

            sheet.write(sheet_iterator, 31, 'Auto pricing Inactive')

            sheet_iterator+=1

            continue

        sheet.write(sheet_iterator, 31, Decision._VALUES_TO_NAMES.get(mp_history_item.decision))

        sheet.write(sheet_iterator, 32, mp_history_item.reason)

        if Decision._VALUES_TO_NAMES.get(mp_history_item.decision) == "AUTO_DECREMENT_SUCCESS":

            sheet.write(sheet_iterator, 33, math.ceil(mp_history_item.proposedSellingPrice))

        if Decision._VALUES_TO_NAMES.get(mp_history_item.decision) == "AUTO_INCREMENT_SUCCESS":

            sheet.write(sheet_iterator, 33, math.ceil(mp_history_item.ourSellingPrice+max(10,.01*mp_history_item.ourSellingPrice)))

        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, "Commission Rate", heading_xf)

    sheet.write(0, 10, "Return Provision", heading_xf)

    sheet.write(0, 11, "Our SP", heading_xf)

    sheet.write(0, 13, "Our TP", heading_xf)

    sheet.write(0, 14, "Lowest Possible TP", heading_xf)

    sheet.write(0, 12, "Our Offer Price", heading_xf)

    sheet.write(0, 15, "Our Rank", heading_xf)

    sheet.write(0, 16, "Our Snapdeal Inventory", heading_xf)

    sheet.write(0, 17, "Net Availability", heading_xf)

    sheet.write(0, 18, "Last Five Day Sale", heading_xf)

    sheet.write(0, 19, "Average Sale", heading_xf)

    sheet.write(0, 20, "Our NLC", heading_xf)

    sheet.write(0, 21, "Margin", heading_xf)

    sheet_iterator=1

    for item in negativeMargin:

        snapdealDetails = item[0]

        snapdealItemInfo = item[1]

        snapdealPricing = item[2]

        spmObj = snapdealItemInfo.sourcePercentage

        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, spmObj.commission)

        sheet.write(sheet_iterator, 10, spmObj.returnProvision)

        sheet.write(sheet_iterator, 11, snapdealPricing.ourSp)

        sheet.write(sheet_iterator, 12, snapdealDetails.ourOfferPrice)

        sheet.write(sheet_iterator, 13, snapdealPricing.ourTp)

        sheet.write(sheet_iterator, 14, snapdealPricing.lowestPossibleTp)

        sheet.write(sheet_iterator, 15, snapdealDetails.rank)

        sheet.write(sheet_iterator, 16, snapdealDetails.ourInventory)

        if (not inventoryMap.has_key(snapdealItemInfo.item_id)):

            sheet.write(sheet_iterator, 17, 'Info not available')

        else:

            sheet.write(sheet_iterator, 17, getNetAvailability(inventoryMap.get(snapdealItemInfo.item_id)))

        sheet.write(sheet_iterator, 18, getOosString((itemSaleMap.get(snapdealItemInfo.item_id))[1]))

        sheet.write(sheet_iterator, 19, (itemSaleMap.get(snapdealItemInfo.item_id))[3])

        sheet.write(sheet_iterator, 20, snapdealItemInfo.nlc)

        sheet.write(sheet_iterator, 21, round((snapdealPricing.ourTp - snapdealPricing.lowestPossibleTp),2))

        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)