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
from operator import itemgetter

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/v2/gvbps?supc=%s&catUrl=&bn=&catId=175&start=0&count=10000"%(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)
    rank ,otherInventory ,ourInventory, ourOfferPrice, ourSp, iterator, secondLowestSellerSp, secondLowestSellerInventory, \
    lowestOfferPrice,  secondLowestSellerOfferPrice = (0,)*10
    lowestSellerName , lowestSellerCode, secondLowestSellerName, secondLowestSellerCode=('',)*4
    sortedVendorsData = sorted(vendorInfo['vendors'], key=itemgetter('sellingPrice'))
    for vendor in sortedVendorsData:
        if iterator == 0:
            lowestSellerName = vendor['vendorDisplayName'].encode('utf-8')
            lowestSellerCode = vendor['vendorCode'].encode('utf-8')
            try:
                lowestSp = vendor['sellingPriceBefIntCashBack']
            except:
                lowestSp = vendor['sellingPrice']
            lowestOfferPrice = vendor['sellingPrice']
            
        if iterator ==1:
            secondLowestSellerName = vendor['vendorDisplayName'].encode('utf-8')
            secondLowestSellerCode =vendor['vendorCode'].encode('utf-8')
            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.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:
            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)
        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)
    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, "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 competitiveNoInventory:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        mpItem = item[3]
        spmObj = snapdealItemInfo.sourcePercentage
        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, 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('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, "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 competitiveNoInventory:
        snapdealDetails = item[0]
        snapdealItemInfo = item[1]
        snapdealPricing = item[2]
        mpItem = item[3]
        spmObj = snapdealItemInfo.sourcePercentage
        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, 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
        
    if (runType=='FULL'):    
        sheet = wbk.add_sheet('Auto Favorites')
        
        heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
        
        excel_integer_format = '0'
        integer_style = xlwt.XFStyle()
        integer_style.num_format_str = excel_integer_format
        xstr = lambda s: s or ""
        
        sheet.write(0, 0, "Item ID", heading_xf)
        sheet.write(0, 1, "Brand", heading_xf)
        sheet.write(0, 2, "Product Name", heading_xf)
        sheet.write(0, 3, "Auto Favourite", heading_xf)
        sheet.write(0, 4, "Reason", heading_xf)
        
        sheet_iterator=1
        for autoFav in nowAutoFav:
            itemId = autoFav[0]
            reason = autoFav[1]
            it = Item.query.filter_by(id=itemId).one()
            sheet.write(sheet_iterator, 0, itemId)
            sheet.write(sheet_iterator, 1, it.brand)
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
            sheet.write(sheet_iterator, 3, "True")
            sheet.write(sheet_iterator, 4, reason)
            sheet_iterator+=1
        for prevFav in previousAutoFav:
            it = Item.query.filter_by(id=prevFav).one()
            sheet.write(sheet_iterator, 0, prevFav)
            sheet.write(sheet_iterator, 1, it.brand)
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
            sheet.write(sheet_iterator, 3, "False")
            sheet_iterator+=1
    
    
#    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, "Old Selling Price", heading_xf)
#    sheet.write(0, 6, "Selling Price Updated",heading_xf)
#    
#    sheet_iterator=1
#    for autoPricingItem in autoPricingItems:
#        mpHistory = autoPricingItem[0]
#        item = autoPricingItem[1]
#        it = Item.query.filter_by(id=item.id).one()
#        sheet.write(sheet_iterator, 0, item.id)
#        sheet.write(sheet_iterator, 1, it.brand)
#        sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
#        sheet.write(sheet_iterator, 3, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
#        sheet.write(sheet_iterator, 4, mpHistory.reason)
#        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
#            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
#            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.proposedSellingPrice))
#        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
#            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
#            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
#        sheet_iterator+=1
    
    filename = "/tmp/snapdeal-report-"+runType+" " + str(timestamp) + ".xls"
    wbk.save(filename)
    try:
        #EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Snapdeal Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
        EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["chandan.kumar@saholic.com","manoj.kumar@saholic.com","yukti.jain@saholic.com","ankush.dhingra@saholic.com","manoj.pal@saholic.com"], " Snapdeal Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], ["rajneesh.arora@saholic.com","anikendra.das@saholic.com","amit.gupta@shop2020.in","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
    except Exception as e:
        print e
        print "Unable to send report.Trying with local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        #recipients = ['kshitij.sood@saholic.com']
        msg = MIMEMultipart()
        msg['Subject'] = "Snapdeal Auto Pricing " +runType+" " + str(timestamp)
        msg['From'] = sender
        recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','amit.gupta@shop2020.in','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
        msg['To'] = ",".join(recipients)
        fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
        fileMsg.set_payload(file(filename).read())
        email.encoders.encode_base64(fileMsg)
        fileMsg.add_header('Content-Disposition','attachment;filename=snapdeal.xls')
        msg.attach(fileMsg)
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."
        
        
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.lowestPossibleSp = snapdealPricing.lowestPossibleSp
        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))[3]
        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 = round(proposed_sp,2)
            mpHistory.proposedTp = round(proposed_tp,2)
        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)
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
            mpHistory.proposedTp = round(proposed_tp,2)
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.totalSeller = snapdealDetails.totalSeller
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[3]
        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 = round(proposed_sp,2)
            mpHistory.proposedTp = round(proposed_tp,2)
        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)
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
            mpHistory.proposedTp = round(proposed_tp,2)
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.totalSeller = snapdealDetails.totalSeller
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[3]
        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 = round(proposed_sp,2)
            mpHistory.proposedTp = round(proposed_tp,2)
            mpHistory.targetNlc = round(target_nlc,2)
        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
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
            mpHistory.proposedTp = round(proposed_tp,2)
            mpHistory.targetNlc = round(target_nlc,2)
        mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
        mpHistory.totalSeller = snapdealDetails.totalSeller
        mpHistory.avgSales = (itemSaleMap.get(snapdealItemInfo.item_id))[3]
        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 = round(proposed_sp,2)
            mpHistory.proposedTp = round(proposed_tp,2)
            #mpHistory.targetNlc = target_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
            mpHistory.proposedSellingPrice = round(proposed_sp,2)
            mpHistory.proposedTp = round(proposed_tp,2)
            #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))[3]
        mpHistory.timestamp = timestamp
        mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
        buyBoxList.append(mpHistory)
    session.commit()
    return buyBoxList 

def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
    if len(successfulAutoDecrease)==0 and len(successfulAutoIncrease)==0 :
        return
    xstr = lambda s: s or ""
    catalog_client = CatalogClient().get_client()
    inventory_client = InventoryClient().get_client()
    message="""<html>
            <body>
            <h3>Auto Decrease Items</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Product Name</th>
            <th>Old Price</th>
            <th>New Price</th>
            <th>Old Margin</th>
            <th>New Margin</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Snapdeal Inventory</th>
            <th>Sales History</th>
            </tr></thead>
            <tbody>"""
    for item in successfulAutoDecrease:
        it = Item.query.filter_by(id=item.item_id).one()
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
        newMargin = round(getNewOurTp(mpItem,item.proposedSellingPrice) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice))  
        message+="""<tr>
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
                <td style="text-align:center">"""+str(math.ceil(item.proposedSellingPrice))+"""</td>
                <td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/item.proposedSellingPrice)*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
                <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
                </tr>"""
    message+="""</tbody></table><h3>Auto Increase Items</h3><table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Product Name</th>
            <th>Old Price</th>
            <th>New Price</th>
            <th>Old Margin</th>
            <th>New Margin</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Snapdeal Inventory</th>
            <th>Sales History</th>
            </tr></thead>
            <tbody>"""
    for item in successfulAutoIncrease:
        it = Item.query.filter_by(id=item.item_id).one()
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
        newMargin = round(getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))  
        message+="""<tr>
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
                <td style="text-align:center">"""+str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))+"""</td>
                <td style="text-align:center">"""+str(round((item.margin),1))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
                <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
                <td style="text-align:center">"""+getOosString((itemSaleMap.get(item.item_id))[1])+"""</td>
                </tr>"""
    message+="""</tbody></table></body></html>"""
    print message
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
    msg = MIMEMultipart()
    msg['Subject'] = "Snapdeal Auto Pricing" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Snapdeal Auto Pricing" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send pricing mail.Lets try with local SMTP."
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."
            

def commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp):
    catalog_client = CatalogClient().get_client()
    inventory_client = InventoryClient().get_client()
    for item in successfulAutoDecrease:
        it = Item.query.filter_by(id=item.item_id).one()
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
        addHistory(sdItem)
        sdItem.transferPrice = getNewOurTp(mpItem,item.proposedSellingPrice)
        sdItem.sellingPrice = math.ceil(item.proposedSellingPrice)
        if ((mpItem.pgFee/100)*sdItem.sellingPrice)>=20:
            sdItem.commission = round((mpItem.commission/100+mpItem.pgFee/100)*(sdItem.sellingPrice),2)
        else:
            sdItem.commission = round((mpItem.commission/100)*(sdItem.sellingPrice),2)+20
        sdItem.serviceTax = round((mpItem.serviceTax/100)*(sdItem.commission+sdItem.courierCostMarketplace),2)
        sdItem.updatedOn = timestamp
        sdItem.priceUpdatedBy = 'SYSTEM'
        mpItem.currentSp = sdItem.sellingPrice
        mpItem.currentTp = sdItem.transferPrice
        mpItem.minimumPossibleTp = getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice) 
        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,item.ourNlc,vatRate)
        markStatusForMarketplaceItems(sdItem,mpItem)
    session.commit()
    for item in successfulAutoIncrease:
        it = Item.query.filter_by(id=item.item_id).one()
        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
        addHistory(sdItem)
        sdItem.transferPrice = getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice))
        sdItem.sellingPrice = math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice))
        if ((mpItem.pgFee/100)*sdItem.sellingPrice)>=20:
            sdItem.commission = round((mpItem.commission/100+mpItem.pgFee/100)*(sdItem.sellingPrice),2)
        else:
            sdItem.commission = round((mpItem.commission/100)*(sdItem.sellingPrice),2)+20
        sdItem.serviceTax = round((mpItem.serviceTax/100)*(sdItem.commission+sdItem.courierCostMarketplace),2)
        sdItem.updatedOn = timestamp
        sdItem.priceUpdatedBy = 'SYSTEM'
        mpItem.currentSp = sdItem.sellingPrice
        mpItem.currentTp = sdItem.transferPrice
        mpItem.minimumPossibleTp = getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,sdItem.sellingPrice) 
        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,item.ourNlc,vatRate)
        markStatusForMarketplaceItems(sdItem,mpItem)
    session.commit()

def updatePricesOnSnapdeal(successfulAutoDecrease,successfulAutoIncrease):
    if syncPrice=='false':
        return
    url = 'http://support.shop2020.in:8080/Support/reports'
    br = getBrowserObject()
    br.open(url)
    br.select_form(nr=0)
    br.form['username'] = "manoj"
    br.form['password'] = "man0j"
    br.submit()
    for item in successfulAutoDecrease:
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
        sellingPrice =  str(math.ceil(item.proposedSellingPrice))
        supc = sdItem.supc
        updateUrl = 'http://support.shop2020.in:8080/Support/snapdeal-list!updateForAutoPricing?sellingPrice=%s&supc=%s&itemId=%s'%(sellingPrice,supc,str(item.item_id))
        br.open(updateUrl)
    for item in successfulAutoIncrease:
        sdItem = SnapdealItem.get_by(item_id=item.item_id)
        sellingPrice =  str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
        supc = sdItem.supc
        updateUrl = 'http://support.shop2020.in:8080/Support/snapdeal-list!updateForAutoPricing?sellingPrice=%s&supc=%s&itemId=%s'%(sellingPrice,supc,str(item.item_id))
        br.open(updateUrl)
    
        

def addHistory(item):
    itemHistory = MarketPlaceUpdateHistory()
    itemHistory.item_id = item.item_id
    itemHistory.source = OrderSource.SNAPDEAL
    itemHistory.exceptionPrice = item.exceptionPrice
    itemHistory.warehouseId = item.warehouseId
    itemHistory.isListedOnSource = item.isListedOnSnapdeal
    itemHistory.transferPrice = item.transferPrice
    itemHistory.sellingPrice = item.sellingPrice
    itemHistory.courierCost = item.courierCost
    itemHistory.commission = item.commission
    itemHistory.serviceTax = item.serviceTax
    itemHistory.suppressPriceFeed = item.suppressPriceFeed
    itemHistory.suppressInventoryFeed = item.suppressInventoryFeed
    itemHistory.updatedOn = item.updatedOn
    itemHistory.maxNlc = item.maxNlc
    itemHistory.skuAtSource = item.skuAtSnapdeal
    itemHistory.marketPlaceSerialNumber = item.supc
    itemHistory.priceUpdatedBy = item.priceUpdatedBy
    itemHistory.courierCostMarketplace = item.courierCostMarketplace

def markStatusForMarketplaceItems(snapdealItem,marketplaceItem):
    markUpdatedItem = MarketPlaceItemPrice.query.filter(MarketPlaceItemPrice.item_id==snapdealItem.item_id).filter(MarketPlaceItemPrice.source==marketplaceItem.source).first()
    if markUpdatedItem is None:
        marketPlaceItemPrice = MarketPlaceItemPrice()
        marketPlaceItemPrice.item_id = snapdealItem.item_id
        marketPlaceItemPrice.source = marketplaceItem.source
        marketPlaceItemPrice.lastUpdatedOn = snapdealItem.updatedOn
        marketPlaceItemPrice.sellingPrice = snapdealItem.sellingPrice 
        marketPlaceItemPrice.suppressPriceFeed = snapdealItem.suppressPriceFeed
        marketPlaceItemPrice.isListedOnSource = snapdealItem.isListedOnSnapdeal
    else:
        if (markUpdatedItem.sellingPrice!=snapdealItem.sellingPrice or markUpdatedItem.suppressPriceFeed!=snapdealItem.suppressPriceFeed or markUpdatedItem.isListedOnSource!=snapdealItem.isListedOnSnapdeal):
            markUpdatedItem.lastUpdatedOn = snapdealItem.updatedOn
        markUpdatedItem.sellingPrice = snapdealItem.sellingPrice
        markUpdatedItem.suppressPriceFeed = snapdealItem.suppressPriceFeed
        markUpdatedItem.isListedOnSource = snapdealItem.isListedOnSnapdeal

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

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
    msg = MIMEMultipart()
    msg['Subject'] = "Snapdeal Lost Buy Box" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Snapdeal Lost Buy Box" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send lost buy box mail.Lets try local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."
        
def getSubsidyDiff(snapdealDetails):
    ourSubsidy = snapdealDetails.ourSp - snapdealDetails.ourOfferPrice
    if snapdealDetails.rank !=1:
        competitionSubsidy = snapdealDetails.lowestSp - snapdealDetails.lowestOfferPrice
    else:
        competitionSubsidy = snapdealDetails.secondLowestSellerSp - snapdealDetails.secondLowestSellerOfferPrice
    return competitionSubsidy - ourSubsidy    
        
def getOtherTp(snapdealDetails,val,spm):
    if val.parent_category==10011 or val.parent_category==12001:
        commissionPercentage = spm.competitorCommissionAccessory
    else:
        commissionPercentage = spm.competitorCommissionOther
    if snapdealDetails.rank==1:
        otherTp = snapdealDetails.secondLowestSellerSp- snapdealDetails.secondLowestSellerSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))-(max(20,(spm.pgFee/100)*snapdealDetails.secondLowestSellerSp)*(1+(spm.serviceTax/100)));
        return round(otherTp,2)
    otherTp = snapdealDetails.lowestSp- snapdealDetails.lowestSp*(commissionPercentage/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost+spm.closingFee)*(1+(spm.serviceTax/100))-(max(20,(spm.pgFee/100)*snapdealDetails.lowestSp)*(1+(spm.serviceTax/100)));
    return round(otherTp,2)
    
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 = mpItem.packagingCost+vat+(mpItem.returnProvision/100)*snapdealDetails.ourSp+mpItem.otherCost;
    lowest_possible_tp = val.nlc+inHouseCost;
    return round(lowest_possible_tp,2)

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

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

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

def getNewLowestPossibleSp(mpItem,nlc,vatRate):
    if (mpItem.pgFee/100)*mpItem.currentSp>=20:
        lowestPossibleSp = (nlc+(mpItem.courierCostMarketplace+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(vatRate/100))+(mpItem.packagingCost+mpItem.otherCost)*(1+(vatRate)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100+mpItem.pgFee/100)*(1+(mpItem.serviceTax/100))*(1+(vatRate)/100)-(mpItem.returnProvision/100)*(1+(vatRate)/100));
    else:
        lowestPossibleSp = (nlc+(mpItem.courierCostMarketplace+mpItem.closingFee+20)*(1+(mpItem.serviceTax/100))*(1+(vatRate/100))+(mpItem.packagingCost+mpItem.otherCost)*(1+(vatRate)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(vatRate)/100)-(mpItem.returnProvision/100)*(1+(vatRate)/100));
    return round(lowestPossibleSp,2)    
    
def getLowestPossibleSp(snapdealDetails,val,spm,mpItem):
    if snapdealDetails.rank==0:
        return mpItem.minimumPossibleSp
    #lowestPossibleSp = (val.nlc+(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(val.vatRate/100))+(15+mpItem.otherCost)*(1+(val.vatRate)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(val.vatRate)/100)-(mpItem.returnProvision/100)*(1+(val.vatRate)/100));
    if (mpItem.pgFee/100)*snapdealDetails.ourSp>=20:
        lowestPossibleSp = (val.nlc+(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(mpItem.vat/100))+(mpItem.packagingCost+mpItem.otherCost)*(1+(mpItem.vat)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100+mpItem.pgFee/100)*(1+(mpItem.serviceTax/100))*(1+(mpItem.vat)/100)-(mpItem.returnProvision/100)*(1+(mpItem.vat)/100))
    else:
        lowestPossibleSp = (val.nlc+(val.courierCost+mpItem.closingFee+20)*(1+(mpItem.serviceTax/100))*(1+(mpItem.vat/100))+(mpItem.packagingCost+mpItem.otherCost)*(1+(mpItem.vat)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(mpItem.vat)/100)-(mpItem.returnProvision/100)*(1+(mpItem.vat)/100))
    return round(lowestPossibleSp,2)    
    
def getTargetTp(targetSp,mpItem):
    targetTp = targetSp- targetSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCostMarketplace+mpItem.closingFee)*(1+(mpItem.serviceTax/100))-(max(20,(mpItem.pgFee/100)*targetSp)*(1+(mpItem.serviceTax/100)))
    return round(targetTp,2)

def getTargetSp(targetTp,mpItem,ourSp):
    if (ourSp*(mpItem.pgFee/100)) < 20:
        targetSp = float(targetTp+(mpItem.courierCostMarketplace+mpItem.closingFee+20)*(1+(mpItem.serviceTax/100)))/(1-((mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))))
    else:
        targetSp = float(targetTp+(mpItem.courierCostMarkeplace+mpItem.closingFee)*(1+(mpItem.serviceTax/100)))/(1-((mpItem.commission/100+mpItem.emiFee/100+mpItem.pgFee/100)*(1+(mpItem.serviceTax/100))))
    return round(targetSp,2)

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 groupData(previousTimestamp,timestampNow):
    previousData = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==previousTimestamp).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).all()
    for data in previousData:
        latestItemData = session.query(MarketPlaceHistory).filter(MarketPlaceHistory.timestamp==timestampNow).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.item_id==data.item_id).first()
        if latestItemData is None:
            continue
        if data.ourSellingPrice == latestItemData.ourSellingPrice and data.ourOfferPrice == latestItemData.ourOfferPrice and data.competitiveCategory == latestItemData.competitiveCategory:
            if data.toGroup is None:
                data.toGroup=False
                latestItemData.toGroup=True
            else:
                latestItemData.toGroup=True
                data.toGroup=False
        else:
            latestItemData=None
    session.commit()

def sendAlertForNegativeMargins(timestamp):
    xstr = lambda s: s or ""
    negativeMargins = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).all()
    if len(negativeMargins) == 0:
        return
    message="""<html>
            <body>
            <h3 style="color:red;font-weight:bold;">Snapdeal Negative Margins</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Product Name</th>
            <th>SP</th>
            <th>TP</th>
            <th>Lowest Possible SP</th>
            <th>Lowest Possible TP</th>
            <th>Margin</th>
            <th>Margin %</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Snapdeal Inventory</th>
            <th>Total Inventory</th>
            <th>Sales History</th>
            </tr></thead>
            <tbody>"""
    for item in negativeMargins:
        mpHistory = item[0]
        catItem = item[1]
        netInventory=''
        if not inventoryMap.has_key(mpHistory.item_id):
            netInventory='Info Not Available'
        else:
            netInventory = str(getNetAvailability(inventoryMap.get(mpHistory.item_id)))
        mpItem = MarketplaceItems.get_by(itemId=mpHistory.item_id,source=OrderSource.SNAPDEAL)
        message+="""<tr>
            <td style="text-align:center">"""+str(mpHistory.item_id)+"""</td>
            <td style="text-align:center">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.ourSellingPrice)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.ourTp)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.lowestPossibleSp)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.lowestPossibleTp)+"""</td>
            <td style="text-align:center">"""+str(mpHistory.margin)+"""</td>
            <td style="text-align:center">"""+str(round((mpHistory.margin/mpHistory.ourSellingPrice)*100,1))+"""</td>
            <td style="text-align:center">"""+str(mpItem.commission)+"""</td>
            <td style="text-align:center">"""+str(mpItem.returnProvision)+" %"+"""</td>
            <td style="text-align:center">"""+str(mpHistory.ourInventory)+"""</td>
            <td style="text-align:center">"""+netInventory+"""</td>
            <td style="text-align:center">"""+getOosString((itemSaleMap.get(mpHistory.item_id))[1])+"""</td>
            </tr>"""
    message+="""</tbody></table></body></html>"""
    print message
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
    msg = MIMEMultipart()
    msg['Subject'] = "Snapdeal Negative Margin" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Snapdeal Negative Margin" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send Snapdeal Negative margin mail.Lets try local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."

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

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
    msg = MIMEMultipart()
    msg['Subject'] = "Snapdeal Competitive But No Inventory" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Snapdeal Competitive But No Inventory" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send Snapdeal Competitive But No Inventory mail.Lets try local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."

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

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
    msg = MIMEMultipart()
    msg['Subject'] = "Snapdeal Auto Pricing Inactive" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Snapdeal Auto Pricing Inactive" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send Snapdeal Auto Pricing Inactive mail.Lets try local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."     
    
def main():
    parser = optparse.OptionParser()
    parser.add_option("-t", "--type", dest="runType",
                   default="FULL", type="string",
                   help="Run type FULL or FAVOURITE")
    (options, args) = parser.parse_args()
    if options.runType not in ('FULL','FAVOURITE'):
        print "Run type argument illegal."
        sys.exit(1)
    timestamp = datetime.now()
    itemInfo= populateStuff(options.runType,timestamp)
    cantCompete, buyBoxItems, competitive, \
    competitiveNoInventory, exceptionList, negativeMargin = decideCategory(itemInfo)
    previousProcessingTimestamp = session.query(func.max(MarketPlaceHistory.timestamp)).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).one()
    exceptionItems = commitExceptionList(exceptionList,timestamp)
    cantComepeteItems = commitCantCompete(cantCompete,timestamp)
    buyBoxList = commitBuyBox(buyBoxItems,timestamp)
    competitiveItems = commitCompetitive(competitive,timestamp)
    competitiveNoInventoryItems = commitCompetitiveNoInventory(competitiveNoInventory,timestamp)
    negativeMarginItems = commitNegativeMargin(negativeMargin,timestamp)
    groupData(previousProcessingTimestamp[0],timestamp)
    successfulAutoDecrease = fetchItemsForAutoDecrease(timestamp)
    successfulAutoIncrease = fetchItemsForAutoIncrease(timestamp)
    if options.runType=='FULL':
        previousAutoFav, nowAutoFav = markAutoFavourite()
    if options.runType=='FULL':
        writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, previousAutoFav, nowAutoFav,timestamp, options.runType)
        try:
            sendAlertForNegativeMargins(timestamp)
        except Exception as e:
            print "Unable to send neagtive margin alert due to ",e
            pass
    else:
        writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, None, None, timestamp, options.runType)
    commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp)
    sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease)
    processLostBuyBoxItems(previousProcessingTimestamp[0],timestamp)
    updatePricesOnSnapdeal(successfulAutoDecrease,successfulAutoIncrease)
    if options.runType=='FULL':
        sendAlertForCompetitiveNoInventory(timestamp)
        sendAlertForInactiveAutoPricing(timestamp)
if __name__ == '__main__':
    main()