Subversion Repositories SmartDukaan

Rev

Rev 9919 | Rev 9949 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed 

from elixir import *
from shop2020.config.client.ConfigClient import ConfigClient
from shop2020.model.v1.catalog.impl import DataService
from shop2020.model.v1.catalog.impl.DataService import SnapdealItem, MarketplaceItems, Item, \
Category, SourcePercentageMaster, MarketPlaceHistory
from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
from shop2020.thriftpy.model.v1.catalog.ttypes import CompetitionCategory, CompetitionBasis, SalesPotential,\
Decision
from shop2020.clients.CatalogClient import CatalogClient
from shop2020.clients.InventoryClient import InventoryClient
import urllib2
import time
from datetime import date, datetime
import simplejson as json
import sys

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

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):
        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
        
class __SnapdealPricing:
    
    def __init__(self, ourSp, ourTp, lowestTp, lowestPossibleTp, competitionBasis, secondLowestSellerTp, lowestPossibleSp):
        self.ourTp = ourTp
        self.lowestTp = lowestTp
        self.lowestPossibleTp = lowestPossibleTp
        self.competitionBasis = competitionBasis
        self.ourSp = ourSp
        self.secondLowestSellerTp = secondLowestSellerTp
        self.lowestPossibleSp = lowestPossibleSp
        
def fetchItemsForAutoDecrease(time):
    autoDecrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE)\
    .filter(MarketplaceItems.source==OrderSource.SNAPDEAL).filter(MarketplaceItems.autoDecrement==True).all()
    #autoDecrementItems = MarketplaceItems.query.filter(MarketplaceItems.autoDecrement==True).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()
    inventory_client = InventoryClient().get_client()
    inventoryMap = inventory_client.getInventorySnapshot(0)
    for autoDecrementItem in autoDecrementItems:
        #mpHistory = MarketPlaceHistory.get_by(source=OrderSource.SNAPDEAL,item_id=autoDecrementItem.itemId,timestamp=time)
        if not autoDecrementItem.competitiveCategory == CompetitionCategory.COMPETITIVE:
            markReasonForMpItem(autoDecrementItem,'Category is '+CompetitionCategory._VALUES_TO_NAMES.get(autoDecrementItem.competitiveCategory),Decision.AUTO_DECREMENT_FAILED)
            continue
        if not autoDecrementItem.risky:
            markReasonForMpItem(autoDecrementItem,'Item is not risky',Decision.AUTO_DECREMENT_FAILED)
            continue
        if autoDecrementItem.proposedSellingPrice >= autoDecrementItem.ourSellingPrice:
            markReasonForMpItem(autoDecrementItem,'Proposed SP greater than current SP',Decision.AUTO_DECREMENT_FAILED)
            continue
        if autoDecrementItem.otherInventory < 3:
            markReasonForMpItem(autoDecrementItem,'Competition stock is not enough',Decision.AUTO_DECREMENT_FAILED)
            continue
        oosStatus = inventory_client.getOosStatusesForXDaysForItem(autoDecrementItem.item_id,0,3)
        count,sale,daysOfStock = 0,0,0
        for obj in oosStatus:
            if not obj.is_oos:
                count+=1
                sale = sale+obj.num_orders
        avgSalePerDay=0 if count==0 else (float(sale)/count)
        totalAvailability, totalReserved = 0,0
        if (not inventoryMap.has_key(autoDecrementItem.item_id)):
            markReasonForMpItem(autoDecrementItem,'Average sale per day is zero',Decision.AUTO_DECREMENT_FAILED)
            continue
        itemInventory=inventoryMap[autoDecrementItem.item_id]
        availableMap  = itemInventory.availability
        reserveMap = itemInventory.reserved
        for warehouse,availability in availableMap.iteritems():
            if warehouse==16:
                continue
            totalAvailability = totalAvailability+availability
        for warehouse,reserve in reserveMap.iteritems():
            if warehouse==16:
                continue
            totalReserved = totalReserved+reserve
        if (totalAvailability-totalReserved)<=0:
            markReasonForMpItem(autoDecrementItem,'Our stock is not enough',Decision.AUTO_DECREMENT_FAILED)
            continue
        if (avgSalePerDay==0):
            markReasonForMpItem(autoDecrementItem,'Average sale per day is zero',Decision.AUTO_DECREMENT_FAILED)
            continue
        daysOfStock = (float(totalAvailability-totalReserved))/avgSalePerDay
        if daysOfStock<2:
            markReasonForMpItem(autoDecrementItem,'Our stock is not enough',Decision.AUTO_DECREMENT_FAILED)
            continue

        autoDecrementItem.competitorEnoughStock = True
        autoDecrementItem.ourEnoughStock = True
        autoDecrementItem.avgSales = avgSalePerDay
        autoDecrementItem.decision = Decision.AUTO_DECREMENT_SUCCESS
        autoDecrementItem.reason = 'All conditions for auto decrement true'
        lgr.info("Auto decrement success for itemId "+str(autoDecrementItem.item_id)+" Days of stock "+str(daysOfStock)+" avg sales "+str(avgSalePerDay))
    session.commit()
    
def fetchItemsForAutoIncrease(time):
    autoIncrementItems = session.query(MarketPlaceHistory).join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
    .filter(MarketPlaceHistory.timestamp==time).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX)\
    .filter(MarketplaceItems.source==OrderSource.SNAPDEAL).filter(MarketplaceItems.autoIncrement==True).all()
    #autoIncrementItems = MarketplaceItems.query.filter(MarketplaceItems.autoIncrement==True).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()
    inventory_client = InventoryClient().get_client()
    inventoryMap = inventory_client.getInventorySnapshot(0)
    for autoIncrementItem in autoIncrementItems:
        #mpHistory = MarketPlaceHistory.get_by(source=OrderSource.SNAPDEAL,item_id=autoIncrementItem.itemId,timestamp=time)
        if not autoIncrementItem.competitiveCategory == CompetitionCategory.BUY_BOX:
            markReasonForMpItem(autoIncrementItem,'Category is '+CompetitionCategory._VALUES_TO_NAMES.get(autoIncrementItem.competitiveCategory),Decision.AUTO_INCREMENT_FAILED)
            continue
        if autoIncrementItem.totalSeller==1 and autoIncrementItem.ourRank==1:
            markReasonForMpItem(autoIncrementItem,'We are the only seller',Decision.AUTO_INCREMENT_FAILED)
            continue
        if autoIncrementItem.proposedSellingPrice <= autoIncrementItem.ourSellingPrice:
            markReasonForMpItem(autoIncrementItem,'Proposed SP less than current SP',Decision.AUTO_INCREMENT_FAILED)
            continue
        oosStatus = inventory_client.getOosStatusesForXDaysForItem(autoIncrementItem.item_id,0,3)
        count,sale,daysOfStock = 0,0,0
        for obj in oosStatus:
            if not obj.is_oos:
                count+=1
                sale = sale+obj.num_orders
        avgSalePerDay=0 if count==0 else (float(sale)/count)
        totalAvailability, totalReserved = 0,0
        if (not inventoryMap.has_key(autoIncrementItem.item_id)):
            markReasonForMpItem(autoIncrementItem,'Average sale per day is zero',Decision.AUTO_INCREMENT_FAILED)
            continue
        itemInventory=inventoryMap[autoIncrementItem.item_id]
        availableMap  = itemInventory.availability
        reserveMap = itemInventory.reserved
        for warehouse,availability in availableMap.iteritems():
            if warehouse==16:
                continue
            totalAvailability = totalAvailability+availability
        for warehouse,reserve in reserveMap.iteritems():
            if warehouse==16:
                continue
            totalReserved = totalReserved+reserve
        if (totalAvailability-totalReserved)<=0:
            markReasonForMpItem(autoIncrementItem,'Our stock is 0',Decision.AUTO_INCREMENT_FAILED)
            continue
        if (avgSalePerDay==0):
            markReasonForMpItem(autoIncrementItem,'Average sale per day is zero',Decision.AUTO_INCREMENT_FAILED)
            continue
        daysOfStock = (float(totalAvailability-totalReserved))/avgSalePerDay
        if daysOfStock>5:
            markReasonForMpItem(autoIncrementItem,'Our stock is enough',Decision.AUTO_INCREMENT_FAILED)
            continue

        autoIncrementItem.ourEnoughStock = False
        autoIncrementItem.avgSales = avgSalePerDay
        autoIncrementItem.decision = Decision.AUTO_INCREMENT_SUCCESS
        autoIncrementItem.reason = 'All conditions for auto increment true'
        lgr.info("Auto increment success for itemId "+str(autoIncrementItem.item_id)+" Days of stock "+str(daysOfStock)+" avg sales "+str(avgSalePerDay))
    session.commit()
        
        
def markReasonForMpItem(mpHistory,reason,decision):
    mpHistory.decision = decision
    mpHistory.reason = reason
        
def fetchDetails(supc_code):
    url="http://www.snapdeal.com/json/gvbps?supc=%s&catId=91"%(supc_code)
    print url
    time.sleep(1)
    req = urllib2.Request(url)
    response = urllib2.urlopen(req)
    json_input = response.read()
    vendorInfo = json.loads(json_input)
    rank ,otherInventory ,ourInventory, ourOfferPrice, ourSp, iterator, secondLowestSellerSp, secondLowestSellerInventory, \
    lowestOfferPrice,  secondLowestSellerOfferPrice = (0,)*10
    lowestSellerName , lowestSellerCode, secondLowestSellerName, secondLowestSellerCode=('',)*4
    for vendor in vendorInfo:
        if iterator == 0:
            lowestSellerName = vendor['vendorDisplayName']
            lowestSellerCode = vendor['vendorCode']
            try:
                lowestSp = vendor['sellingPriceBefIntCashBack']
            except:
                lowestSp = vendor['sellingPrice']
            lowestOfferPrice = vendor['sellingPrice']
            
        if iterator ==1:
            secondLowestSellerName = vendor['vendorDisplayName']
            secondLowestSellerCode =vendor['vendorCode']
            try:
                secondLowestSellerSp = vendor['sellingPriceBefIntCashBack']
            except:
                secondLowestSellerSp = vendor['sellingPrice'] 
            secondLowestSellerOfferPrice = vendor['sellingPrice'] 
            secondLowestSellerInventory = vendor['buyableInventory']
            
        if vendor['vendorDisplayName'] == 'MobilesnMore':
            ourInventory = vendor['buyableInventory']
            try:
                ourSp = vendor['sellingPriceBefIntCashBack']
            except:
                ourSp = vendor['sellingPrice']
            ourOfferPrice = vendor['sellingPrice']
            rank = iterator +1
        else:
            if rank==0:
                otherInventory = otherInventory +vendor['buyableInventory']
        iterator+=1
    snapdealDetails = __SnapdealDetails(ourSp,ourInventory,otherInventory,rank,lowestSellerName, lowestSellerCode,lowestSp,secondLowestSellerName,secondLowestSellerCode,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice,len(vendorInfo))
    return snapdealDetails        
        

def populateStuff():
    itemInfo = []
    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()
        snapdealItemInfo = __SnapdealItemInfo(snapdeal_item.supc, snapdeal_item.maxNlc,snapdeal_item.courierCost, it.id, it.product_group, it.brand, it.model_name, it.model_number, it.color, it.weight, category.parent_category_id, it.risky, snapdeal_item.warehouseId, None)
        itemInfo.append(snapdealItemInfo)
    return itemInfo, spm

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

def commitExceptionList(exceptionList,timestamp):
    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
    session.commit()

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

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

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

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

def getOurTp(snapdealDetails,val,spm,mpItem):
    if snapdealDetails.rank==0:
        return mpItem.currentTp
    return snapdealDetails.ourSp- snapdealDetails.ourSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100));
    
def getLowestPossibleSp(snapdealDetails,val,spm,mpItem):
    if snapdealDetails.rank==0:
        return mpItem.minimumPossibleSp
    return (val.nlc+(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(val.vatRate/100))+(15+mpItem.otherCost)*(1+(val.vatRate)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(val.vatRate)/100)-(mpItem.returnProvision/100)*(1+(val.vatRate)/100));    
    
def getTargetTp(targetSp,mpItem):
    return targetSp- targetSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100));

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

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

def main():
    itemInfo,spm= populateStuff()
    cantCompete, buyBoxItems, competitive, \
    competitiveNoInventory, exceptionItems, negativeMargin = decideCategory(itemInfo,spm)
    timestamp = datetime.now()
    commitExceptionList(exceptionItems,timestamp)
    commitCantCompete(cantCompete,timestamp)
    commitBuyBox(buyBoxItems,timestamp)
    commitCompetitive(competitive,timestamp)
    commitCompetitiveNoInventory(competitiveNoInventory,timestamp)
    commitNegativeMargin(negativeMargin,timestamp)
    fetchItemsForAutoDecrease(timestamp)
    fetchItemsForAutoIncrease(timestamp)
    
    
if __name__ == '__main__':
    main()