Subversion Repositories SmartDukaan

from elixir import *

from sqlalchemy.sql import or_ ,func, asc, desc, and_

from shop2020.config.client.ConfigClient import ConfigClient

from shop2020.model.v1.catalog.impl import DataService

from shop2020.model.v1.catalog.impl.DataService import Amazonlisted, Item, \

Category, SourcePercentageMaster,SourceCategoryPercentage, SourceItemPercentage, AmazonPromotion, AmazonScrapingHistory, \

ItemVatMaster, CategoryVatMaster

from shop2020.thriftpy.model.v1.order.ttypes import OrderSource

from shop2020.thriftpy.model.v1.catalog.ttypes import CompetitionCategory, \

Decision, RunType, AmazonPromotionType

from shop2020.model.v1.catalog.script import AmazonAsyncScraper

from shop2020.clients.InventoryClient import InventoryClient

from shop2020.clients.TransactionClient import TransactionClient

import time 

from datetime import date, datetime, timedelta

import math

import simplejson as json

import xlwt

import optparse

import sys

from operator import itemgetter

config_client = ConfigClient()

host = config_client.get_property('staging_hostname')

syncPrice=config_client.get_property('sync_price_on_marketplace')

amazonAsinPrice={}

amazonLongTermActivePromotions = {}

amazonShortTermActivePromotions = {}

saleMap = {}

DataService.initialize(db_hostname=host)

amScraper = AmazonAsyncScraper.Products("AKIAII3SGRXBJDPCHSGQ", "B92xTbNBTYygbGs98w01nFQUhbec1pNCkCsKVfpg", "AF6E3O0VE0X4D")

class __AmazonItemInfo:

    def __init__(self, asin, nlc, courierCost, sku, product_group, brand, model_name, model_number, color, weight, parent_category, risky, vatRate, runType, parent_category_name, sourcePercentage, ourInventory, state_id):

        self.asin = asin

        self.nlc = nlc

        self.courierCost = courierCost

        self.sku = sku

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

        self.runType = runType

        self.parent_category_name = parent_category_name

        self.sourcePercentage = sourcePercentage

        self.ourInventory = ourInventory

        self.state_id = state_id

class __AmazonDetails:

    def __init__(self, sku, ourSp, ourRank, lowestSellerName,lowestSellerSp,secondLowestSellerName, secondLowestSellerSp, thirdLowestSellerName, thirdLowestSellerSp, totalSeller, multipleListings, \

                 promoPrice, isPromotion, lowestSellerShippingTime, lowestSellerRating, secondLowestSellerShippingTime, secondLowestSellerRating, thirdLowestSellerShippingTime , \

                 thirdLowestSellerRating, lowestSellerType, secondLowestSellerType, thirdLowestSellerType):

        self.sku =sku

        self.ourSp = ourSp

        self.ourRank = ourRank

        self.lowestSellerName = lowestSellerName

        self.lowestSellerSp = lowestSellerSp

        self.secondLowestSellerName = secondLowestSellerName

        self.secondLowestSellerSp = secondLowestSellerSp

        self.thirdLowestSellerName = thirdLowestSellerName

        self.thirdLowestSellerSp = thirdLowestSellerSp

        self.totalSeller = totalSeller

        self.multipleListings = multipleListings

        self.promoPrice = promoPrice

        self.isPromotion = isPromotion

        self.lowestSellerShippingTime =lowestSellerShippingTime

        self.lowestSellerRating = lowestSellerRating

        self.secondLowestSellerShippingTime = secondLowestSellerShippingTime

        self.secondLowestSellerRating = secondLowestSellerRating

        self.thirdLowestSellerShippingTime= thirdLowestSellerShippingTime

        self.thirdLowestSellerRating = thirdLowestSellerRating

        self.lowestSellerType = lowestSellerType

        self.secondLowestSellerType = secondLowestSellerType

        self.thirdLowestSellerType = thirdLowestSellerType    

class __AmazonPricing:

    def __init__(self, ourSp, lowestPossibleSp):

        self.ourSp = ourSp

        self.lowestPossibleSp = lowestPossibleSp

class __Promotion:

    def __init__(self, promoPrice, subsidy, promotionType):

        self.promoPrice = promoPrice

        self.subsidy = subsidy

        self.promotionType = promotionType

def fetchItemsForAutoDecrease(time):

    successfulAutoDecrease = []

    autoDecrementItems = session.query(AmazonScrapingHistory).join((Amazonlisted,AmazonScrapingHistory.item_id==Amazonlisted.itemId))\

    .filter(AmazonScrapingHistory.timestamp==time).filter(or_(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE,AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE, AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE ))\

    .filter(Amazonlisted.autoDecrement==True).all()

    for autoDecrementItem in autoDecrementItems:

        if autoDecrementItem.warehouseLocation == 1:

            sku = 'FBA'+str(autoDecrementItem.item_id)

        else:

            sku = 'FBB'+str(autoDecrementItem.item_id)

        if amazonShortTermActivePromotions.has_key(sku):

            markReasonForItem(autoDecrementItem,'Item in short term promotion',Decision.AUTO_DECREMENT_FAILED)

            continue

        if math.ceil(autoDecrementItem.proposedSp) >= autoDecrementItem.ourSellingPrice:

            markReasonForItem(autoDecrementItem,'Proposed SP greater than or equal to current SP',Decision.AUTO_DECREMENT_FAILED)

            continue

        if autoDecrementItem.proposedSellingPrice < autoDecrementItem.lowestPossibleSp:

            markReasonForItem(autoDecrementItem,'Proposed SP less than lowest possible SP',Decision.AUTO_DECREMENT_FAILED)

            continue

        try:

            daysOfStock = (float(autoDecrementItem.ourInventory))/autoDecrementItem.avgSale

        except:

            daysOfStock = float("inf")

        if autoDecrementItem.competitiveCategory == CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE:

            if daysOfStock < 20:

                markReasonForItem(autoDecrementItem,'Days of stock less than 20',Decision.AUTO_DECREMENT_FAILED)

        if autoDecrementItem.competitiveCategory == CompetitionCategory.COMPETITIVE:

            if autoDecrementItem.parentCategoryId in [10006,10009,11001]:

                if daysOfStock < 1:

                    markReasonForItem(autoDecrementItem,'Days of stock less than 1',Decision.AUTO_DECREMENT_FAILED)

            else:

                if daysOfStock < 3:

                    markReasonForItem(autoDecrementItem,'Days of stock less than 3',Decision.AUTO_DECREMENT_FAILED)

        autoDecrementItem.ourEnoughStock=True

        autoDecrementItem.decision = Decision.AUTO_DECREMENT_SUCCESS

        autoDecrementItem.reason = 'All conditions for auto decrement true'

        successfulAutoDecrease.append(autoDecrementItem)

    session.commit()

    session.close()

    return successfulAutoDecrease

def fetchItemsForAutoIncrease(time):

    successfulAutoIncrease = []

    autoIncrementItems = session.query(AmazonScrapingHistory).join((Amazonlisted,AmazonScrapingHistory.item_id==Amazonlisted.itemId))\

    .filter(AmazonScrapingHistory.timestamp==time).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX)\

    .filter(Amazonlisted.autoIncrement==True).all()

    transaction_client = TransactionClient().get_client()

    for autoIncrementItem in autoIncrementItems:

        if autoIncrementItem.warehouseLocation == 1:

            sku = 'FBA'+str(autoIncrementItem.item_id)

        else:

            sku = 'FBB'+str(autoIncrementItem.item_id)

        if amazonShortTermActivePromotions.has_key(sku):

            markReasonForItem(autoIncrementItem,'Item in short term promotion',Decision.AUTO_INCREMENT_FAILED)

            continue

        if autoIncrementItem.totalSeller==1 and autoIncrementItem.ourRank==1:

            markReasonForItem(autoIncrementItem,'We are the only seller',Decision.AUTO_INCREMENT_FAILED)

            continue 

        if autoIncrementItem.proposedSp <= autoIncrementItem.ourSellingPrice:

            markReasonForItem(autoIncrementItem,'Proposed SP less than current SP',Decision.AUTO_INCREMENT_FAILED)

            continue

        if autoIncrementItem.proposedSellingPrice >=10000 and autoIncrementItem.ourSellingPrice<10000:

            markReasonForItem(autoIncrementItem,'Proposed SP is greater than 10,000 and current sp is less than 10,000',Decision.AUTO_INCREMENT_FAILED)

            continue

        if autoIncrementItem.avgSale==0:

            markReasonForItem(autoIncrementItem,'Avg sale is 0',Decision.AUTO_INCREMENT_FAILED)

            continue

        daysOfStock = (float(autoIncrementItem.ourInventory))/autoIncrementItem.avgSale

        if daysOfStock > 5:

            markReasonForItem(autoIncrementItem,'Days of stock greater than 5',Decision.AUTO_INCREMENT_FAILED)

            continue

        antecedentPrice = session.query(AmazonScrapingHistory.ourSellingPrice).filter(AmazonScrapingHistory.item_id==autoIncrementItem.item_id).filter(AmazonScrapingHistory.timestamp>time-timedelta(days=1)).order_by(asc(AmazonScrapingHistory.timestamp)).first()

        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:

                markReasonForItem(autoIncrementItem,'Maximum price increase in last 24 hours should be 2%',Decision.AUTO_INCREMENT_FAILED)

                continue

        fbaSaleSnapshot = transaction_client.getAmazonFbaSalesLatestSnapshotForItemLocationWise(autoIncrementItem.item_id,autoIncrementItem.warehouseLocation)

        if getLastDaySale(fbaSaleSnapshot,autoIncrementItem.warehouseLocation-1)<=2:

            markReasonForItem(autoIncrementItem,'Last day sale is less than 3',Decision.AUTO_INCREMENT_FAILED)

            continue

        autoIncrementItem.ourEnoughStock = False

        autoIncrementItem.decision = Decision.AUTO_INCREMENT_SUCCESS

        autoIncrementItem.reason = 'All conditions for auto increment true'

        successfulAutoIncrease.append(autoIncrementItem)

    session.commit()

    return successfulAutoIncrease     

def markReasonForItem(amHistory,reason,decision):

    amHistory.decision = decision

    amHistory.reason = reason

def calculateAverageSale(sku):

    count,sale = 0,0

    oosStatus = saleMap.get(sku)

    for obj in oosStatus:

        if not obj.isOutOfStock:

            count+=1

            sale = sale+obj.totalOrderCount

    avgSalePerDay=0 if count==0 else (float(sale)/count)

    return round(avgSalePerDay,2)

def getOosString(oosStatus):

    lastNdaySale=""

    for obj in oosStatus:

        if obj.isOutOfStock:

            lastNdaySale += "X-"

        else:

            lastNdaySale += str(obj.totalOrderCount) + "-"

    return lastNdaySale[:-1]

def getLastDaySale(fbaSaleSnapshot):

    if fbaSaleSnapshot.item_id==0:

        return 0

    else:

        return fbaSaleSnapshot.totalOrderCount

#def syncAsin():

##    notListedOnAmazon = []

##    diffAsins = []

##    login_url = "https://sellercentral.amazon.in/gp/homepage.html"

##    br = SellerCentralInventoryReport.login(login_url)

##    report_url = "https://sellercentral.amazon.in/gp/upload-download-utils/requestReport.html?type=OpenListingReport&marketplaceID=44571&Request+Report="

##    br = SellerCentralInventoryReport.requestReport(br,report_url)

##    status_url="https://sellercentral.amazon.in/gp/upload-download-utils/reportStatusData.html"

##    br, page = SellerCentralInventoryReport.checkStatus(br,status_url)

##    br, batchId = SellerCentralInventoryReport.getReportBatchId(br,page)

##    print "*********************************"

##    print "Batch Id for request is ",batchId

##    print "*********************************"

##    ready = False

##    retryCount = 0

##    while not ready:

##        if retryCount == 10:

##            print "File not available for download after multiple retries"

##            sys.exit(1)

##        br, download_link = SellerCentralInventoryReport.downloadReport(br,batchId,status_url)

##        if download_link is not None:

##            ready= True

##            continue

##        print "File not ready for download yet.Will try again after 30 seconds."

##        retryCount+=1

##        time.sleep(30)

##    fPath = SellerCentralInventoryReport.fetchFile(download_link['href'],br,batchId)

#    fPath = "/tmp/9940651090.txt"

#    global amazonAsinPrice

#    for line in open(fPath):

#        l = line.split('\t')

#        if (str(l[0]).startswith('FBA') or str(l[0]).startswith('FBB')):

#            obj = __AmazonAsinPrice(l[1],l[2])

#            amazonAsinPrice[l[0]] = obj

##Can be used to sync asins, not doing due to multiple asins corresponding to one itemId

##    systemAsins = session.query(Item,Amazonlisted).join((Amazonlisted,Item.id==Amazonlisted.itemId)).all()

##    for systemAsin in systemAsins:

##        item = systemAsin[0]

##        amListed = systemAsin[1]

##        if amazonAsinPrice.get('FBA'+str(item.id)) is None:

##            temp=[]

##            temp.append(item)

##            temp.append(amListed)

##            notListedOnAmazon.append(temp)

##            continue

##        else:

##            temp=[]

##            temp.append(item)

##            temp.append(amListed)

##            if item.asin!=((amazonAsinPrice.get('FBA'+str(item.id))).asin).strip():

##                diffAsins.append(temp)

##                continue

##            

##    for diffAsin in diffAsins:

##        item = diffAsin[0]

##        amListed = diffAsin[1]

##        item.asin = ((amazonAsinPrice.get('FBA'+str(item.id))).asin).strip()

##        amListed.asin = ((amazonAsinPrice.get('FBA'+str(item.id))).asin).strip()

##    session.commit()

##    session.close()

def fetchFbaSale():

    global saleMap

    transaction_client = TransactionClient().get_client()

    fbaSaleSnapshot = transaction_client.getAmazonFbaSalesSnapshotForDays(4)

    for saleSnapshot in fbaSaleSnapshot:

        if saleSnapshot.fcLocation == 0:

            if saleMap.has_key('FBA'+str(saleSnapshot.item_id)):

                temp = []

                val = saleMap.get('FBA'+str(saleSnapshot.item_id))

                for l in val:

                    temp.append(l)

                temp.append(saleSnapshot)

                saleMap['FBA'+str(saleSnapshot.item_id)]=temp

            else:

                temp = []

                temp.append(saleSnapshot)

                saleMap['FBA'+str(saleSnapshot.item_id)] = temp

        else:

            if saleMap.has_key('FBB'+str(saleSnapshot.item_id)):

                temp = []

                val = saleMap.get('FBB'+str(saleSnapshot.item_id))

                for l in val:

                    temp.append(l)

                saleMap['FBB'+str(saleSnapshot.item_id)]=temp

                temp.append(saleSnapshot)

            else:

                temp = []

                temp.append(saleSnapshot)

                saleMap['FBB'+str(saleSnapshot.item_id)] = temp

def computeCourierCost(weight):

    try:

        cCost = 10.0;

        slabs = int((weight*1000)/500-.001)

        for slab in range(0,slabs):

            cCost = cCost + 10.0;

        return cCost;

    except:

        return 10.0

def populateStuff(time,runType):

    global amazonLongTermActivePromotions

    global amazonShortTermActivePromotions

    itemInfo = []

    inventory_client = InventoryClient().get_client()

    fbaAvailableInventorySnapshot = inventory_client.getAllAvailableAmazonFbaItemInventory()

    print len(fbaAvailableInventorySnapshot)

    for fbaInventoryItem in fbaAvailableInventorySnapshot:

        d_amazon_listed = Amazonlisted.get_by(itemId=fbaInventoryItem.item_id)

        if d_amazon_listed is None:

            continue

        if d_amazon_listed.overrrideWanlc:

            wanlc = d_amazon_listed.exceptionalWanlc

        else:

            wanlc = inventory_client.getWanNlcForSource(fbaInventoryItem.item_id,OrderSource.AMAZON)

        it = Item.query.filter_by(id=fbaInventoryItem.item_id).one()

        category = Category.query.filter_by(id=it.category).one()

        parent_category = Category.query.filter_by(id=category.parent_category_id).first()

        scp = SourceCategoryPercentage.query.filter(SourceCategoryPercentage.category_id==it.category).filter(SourceCategoryPercentage.source==OrderSource.AMAZON).filter(SourceCategoryPercentage.startDate<=time).filter(SourceCategoryPercentage.expiryDate>=time).first()

        if scp is not None:

            sourcePercentage = scp

        else:

            spm = SourcePercentageMaster.get_by(source=OrderSource.AMAZON)

            sourcePercentage = spm

        if fbaInventoryItem.location==0:

            sku = 'FBA'+str(fbaInventoryItem.item_id)

            state_id = 1

        elif fbaInventoryItem.location==1:

            sku = 'FBB'+str(fbaInventoryItem.item_id)

            state_id = 2

        else:

            continue

        cc = computeCourierCost(it.weight)

        amazonItemInfo = __AmazonItemInfo(None, wanlc,cc, sku, it.product_group, it.brand, it.model_name, it.model_number, it.color, it.weight, category.parent_category_id, it.risky, None, runType, parent_category.display_name,sourcePercentage,fbaInventoryItem.availability,state_id)

        itemInfo.append(amazonItemInfo)

    amPromotions = AmazonPromotion.query.filter(AmazonPromotion.startDate<=time).filter(AmazonPromotion.endDate>=time).filter(AmazonPromotion.promotionType==AmazonPromotionType.LONGTERM).filter(AmazonPromotion.promotionActive==True) \

    .group_by(AmazonPromotion.sku).order_by(desc(AmazonPromotion.addedOn)).all()

    for amPromotion in amPromotions:

        amazonLongTermActivePromotions[amPromotion.sku] = __Promotion(amPromotion.salePrice,amPromotion.subsidy,amPromotion.promotionType)

    amPromotions = AmazonPromotion.query.filter(AmazonPromotion.startDate<=time).filter(AmazonPromotion.endDate>=time).filter(AmazonPromotion.promotionType==AmazonPromotionType.SHORTTERM).filter(AmazonPromotion.promotionActive==True) \

    .group_by(AmazonPromotion.sku).order_by(desc(AmazonPromotion.addedOn)).all()

    for amPromotion in amPromotions:

        amazonShortTermActivePromotions[amPromotion.sku] = __Promotion(amPromotion.salePrice,amPromotion.subsidy,amPromotion.promotionType)

    session.close()

    return itemInfo

def getPriceAndAsin(itemInfo):

    skus = []

    for item in itemInfo:

        skus.append(item.sku)

    ourPricingForSku = amScraper.get_my_pricing_for_sku('A21TJRUUN4KGV', skus)

    for item in itemInfo:

        ourPricing = ourPricingForSku.get(item.sku)

        if len(ourPricing.keys())==0:

            item.ourSp = 0

            item.promoPrice = 0

            item.isPromotion = False

        else:

            item.ourSp = ourPricing.get('sellingPrice')

            item.promoPrice = ourPricing.get('promoPrice')

            item.isPromotion = ourPricing.get('promotion')

def decideCategory(itemInfo):

    exceptionList, negativeMargin, cheapest, amongCheapestAndCanCompete, canCompete, almostCompete, cantCompete = [],[],[],[],[],[],[] 

    skus = []

    for item in itemInfo:

        skus.append(item.sku)

    aggResponse = amScraper.get_competitive_pricing_for_sku('A21TJRUUN4KGV', skus)

    ourPricingForSku = amScraper.get_my_pricing_for_sku('A21TJRUUN4KGV', skus)

    for val in itemInfo:

        scrapInfo = aggResponse.get(val.sku)

        if len(scrapInfo)==0 or val.nlc==0 or len(ourPricingForSku.get(val.sku).keys())==0:

            temp = []

            temp.append(val)

            if val.nlc==0 or val.nlc is None:

                temp.append("WANLC is 0")

            elif len(ourPricingForSku.get(val.sku).keys())==0:

                temp.append("Unable to fetch our price")

            else:

                temp.append("Unable to fetch competitive pricing")

            exceptionList.append(temp)

            continue

        val.asin = ourPricingForSku.get(val.sku).get('asin')

        val.ourSp = ourPricingForSku.get(val.sku).get('sellingPrice')

        val.promoPrice = ourPricingForSku.get(val.sku).get('promoPrice')

        val.isPromo = ourPricingForSku.get(val.sku).get('promotion')

        iterator = 0

        sku, lowestSellerName,secondLowestSellerName, thirdLowestSellerName = ('',)*4

        ourSp, ourRank, lowestSellerSp, secondLowestSellerSp, thirdLowestSellerSp, lowestPossibleSp = (0,)*6

        lowestSellerShippingTime, lowestSellerRating, secondLowestSellerShippingTime, secondLowestSellerRating, thirdLowestSellerShippingTime , \

        thirdLowestSellerRating, lowestSellerType, secondLowestSellerType, thirdLowestSellerType = (0,)*9

        isPromo = False

        sku = val.sku

        multipleListings = False

        ourSkuDetails = ourPricingForSku.get(val.sku)

        if not (ourSkuDetails.promotion and (amazonLongTermActivePromotions.has_key(val.sku) or amazonShortTermActivePromotions.has_key(val.sku))):

            temp = []

            temp.append(val)

            temp.append("Promo misconfigured")

            continue

        scrapInfo.append(ourSkuDetails)

        sortedScrapInfo =  sorted(scrapInfo, key=itemgetter('promoPrice','notOurSku'))

        for info in sortedScrapInfo:

            if  not info.notOurSku:

                ourSp = info.sellingPrice

                promoPrice = info.promoPrice

                isPromo = info.promotion

                ourRank = iterator + 1

            if iterator == 0:

                lowestSellerSp = info['promoPrice']

                lowestSellerShippingTime = info['shippingTime']

                lowestSellerRating = info['rating']

                lowestSellerType = info['fulfillmentChannel']

            if iterator == 1:

                secondLowestSellerSp = info['promoPrice']

                secondLowestSellerShippingTime = info['shippingTime']

                secondLowestSellerRating = info['rating']

                secondLowestSellerType = info['fulfillmentChannel']

            if iterator == 2:

                thirdLowestSellerSp = info['promoPrice']

                thirdLowestSellerShippingTime = info['shippingTime']

                thirdLowestSellerRating = info['rating']

                thirdLowestSellerType = info['fulfillmentChannel']

            iterator += 1

        print "terminating iterator"

        print "Creating object am details",val.sku

        amDetails = __AmazonDetails(sku, float(ourSp), ourRank, lowestSellerName,float(lowestSellerSp),secondLowestSellerName, float(secondLowestSellerSp), thirdLowestSellerName, float(thirdLowestSellerSp),len(scrapInfo),multipleListings,promoPrice,isPromo, \

                    lowestSellerShippingTime ,lowestSellerRating, secondLowestSellerShippingTime, secondLowestSellerRating, thirdLowestSellerShippingTime , thirdLowestSellerRating, lowestSellerType, secondLowestSellerType, thirdLowestSellerType)

        print "am details obj created"

        try:

            print "inside val getter"

            itemVatMaster = ItemVatMaster.query.filter(and_(ItemVatMaster.itemId==int(val.sku[3:]), ItemVatMaster.stateId==val.state_id)).first()

            if itemVatMaster is None:

                d_item = Item.query.filter_by(id=int(val.sku[3:])).first()

                if d_item is None:

                    raise 

                else:

                    vatMaster = CategoryVatMaster.query.filter(and_(CategoryVatMaster.categoryId==d_item.category, CategoryVatMaster.minVal<=amDetails.promoPrice,  CategoryVatMaster.maxVal>=amDetails.promoPrice,  CategoryVatMaster.stateId == val.state_id)).first()

                if vatMaster is None:

                    raise

                else:

                    val.vatRate = vatMaster.vatPercent

                    print "vat fetched"

            else:

                val.vatRate = itemVatMaster.vatPercentage

                print "vat fetched"

        except:

            print "vat exception"

            temp = []

            temp.append(val)

            temp.append("Vat not available")

            exceptionList.append(temp)

            continue

        lowestPossibleSp = getLowestPossibleSp(amDetails,val,val.sourcePercentage)

        print "Creating pricing obj"

        amPricing = __AmazonPricing(ourSp,lowestPossibleSp)

        if amPricing.promoPrice < amPricing.lowestPossibleSp:

            temp = []

            temp.append(val)

            temp.append(amDetails)

            temp.append(amPricing)

            negativeMargin.append(temp)

            continue

        if amDetails.ourRank==1:

            temp = []

            temp.append(val)

            temp.append(amDetails)

            temp.append(amPricing)

            cheapest.append(temp)

            continue

        if (amDetails.lowestSellerSp > amPricing.lowestPossibleSp) and ((((float(amDetails.promoPrice - amDetails.lowestSellerSp))/amDetails.promoPrice)<=.01) or ((amDetails.promoPrice - amDetails.lowestSellerSp)<=25)):

            temp = []

            temp.append(val)

            temp.append(amDetails)

            temp.append(amPricing)

            amongCheapestAndCanCompete.append(temp)

            continue

        if (amDetails.lowestSellerSp > amPricing.lowestPossibleSp):

            temp = []

            temp.append(val)

            temp.append(amDetails)

            temp.append(amPricing)

            canCompete.append(temp)

            continue

        if amDetails.lowestSellerSp*(1+.01) >= amPricing.lowestPossibleSp:

            temp = []

            temp.append(val)

            temp.append(amDetails)

            temp.append(amPricing)

            almostCompete.append(temp)

            continue

        temp = []

        temp.append(val)

        temp.append(amDetails)

        temp.append(amPricing)

        cantCompete.append(temp)

    print "Created category..."

    return exceptionList, negativeMargin, cheapest, amongCheapestAndCanCompete, canCompete, almostCompete, cantCompete

def getLowestPossibleSp(amazonDetails,val,spm):

    lowestPossibleSp = (val.nlc+(val.courierCost)*(1+(spm.serviceTax/100))*(1+(val.vatRate/100))+(15)*(1+(val.vatRate)/100))/(1-(spm.commission/100+spm.emiFee/100)*(1+(spm.serviceTax/100))*(1+(val.vatRate)/100)-(spm.returnProvision/100)*(1+(val.vatRate)/100));

    if val.isPromo:

        if amazonLongTermActivePromotions.has_key(val.sku):

            subsidy = (amazonLongTermActivePromotions.has_key(val.sku)).subsidy

        else:

            subsidy = (amazonShortTermActivePromotions.has_key(val.sku)).subsidy

        lowestPossibleSp = lowestPossibleSp - subsidy

    return round(lowestPossibleSp,2)

def getTargetTp(targetSp,spm,val):

    targetTp = targetSp- targetSp*(spm.commission/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost)*(1+(spm.serviceTax/100))

    return round(targetTp,2)

def commitExceptionList(exceptionList,timestamp,runType):

    for exceptionItem in exceptionList:

        val = exceptionItem[0]

        reason = exceptionItem[1]

        amazonScrapingHistory = AmazonScrapingHistory()

        amazonScrapingHistory.item_id = val.sku[3:]

        amazonScrapingHistory.warehouseLocation = val.state_id

        amazonScrapingHistory.parentCategoryId = val.parent_category

        amazonScrapingHistory.reason = reason

        amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)

        amazonScrapingHistory.competitiveCategory = CompetitionCategory.EXCEPTION

        amazonScrapingHistory.timestamp = timestamp

    session.commit()

def commitNegativeMargin(negativeMargin,timestamp,runType):

    for negativeMarginItem in negativeMargin:

        val = negativeMarginItem[0]

        amDetails = negativeMarginItem[1]

        amPricing = negativeMarginItem[2]

        spm = val.sourcePercentage

        amazonScrapingHistory = AmazonScrapingHistory()

        amazonScrapingHistory.item_id = val.sku[3:]

        amazonScrapingHistory.warehouseLocation = val.state_id

        amazonScrapingHistory.parentCategoryId = val.parent_category

        amazonScrapingHistory.ourSellingPrice = amDetails.ourSp

        amazonScrapingHistory.promoPrice = amDetails.promoPrice

        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp

        amazonScrapingHistory.ourRank = amDetails.ourRank

        amazonScrapingHistory.ourInventory = val.ourInventory

        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp

        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp

        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp

        amazonScrapingHistory.wanlc = val.nlc

        amazonScrapingHistory.commission = spm.commission

        amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther

        amazonScrapingHistory.returnProvision = spm.returnProvision

        amazonScrapingHistory.courierCost = val.courierCost

        amazonScrapingHistory.risky = val.risky

        amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)

        amazonScrapingHistory.totalSeller = amDetails.totalSeller

        amazonScrapingHistory.competitiveCategory = CompetitionCategory.NEGATIVE_MARGIN

        amazonScrapingHistory.timestamp = timestamp

        amazonScrapingHistory.multipleListings = amDetails.multipleListings

        amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days

        amazonScrapingHistory.isPromotion = val.isPromo

    session.commit()

def commitCheapest(cheapest,timestamp,runType):

    for cheapestItem in cheapest:

        val = cheapestItem[0]

        amDetails = cheapestItem[1]

        amPricing = cheapestItem[2]

        spm = val.sourcePercentage

        amazonScrapingHistory = AmazonScrapingHistory()

        amazonScrapingHistory.item_id = val.sku[3:]

        amazonScrapingHistory.warehouseLocation = val.state_id

        amazonScrapingHistory.parentCategoryId = val.parent_category

        amazonScrapingHistory.ourSellingPrice = amDetails.ourSp

        amazonScrapingHistory.promoPrice = amDetails.promoPrice

        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp

        amazonScrapingHistory.ourRank = amDetails.ourRank

        amazonScrapingHistory.ourInventory = val.ourInventory

        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp

        amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime

        amazonScrapingHistory.lowestSellerRating = amDetails.lowestSellerRating

        amazonScrapingHistory.lowestSellerType = amDetails.lowestSellerType

        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp

        amazonScrapingHistory.secondSellerShippingTime = amDetails.secondSellerShippingTime

        amazonScrapingHistory.secondSellerRating = amDetails.secondSellerRating

        amazonScrapingHistory.secondSellerType = amDetails.secondSellerType

        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp

        amazonScrapingHistory.thirdSellerShippingTime = amDetails.thirdSellerShippingTime

        amazonScrapingHistory.thirdSellerRating = amDetails.thirdSellerRating

        amazonScrapingHistory.thirdSellerType = amDetails.thirdSellerType

        amazonScrapingHistory.wanlc = val.nlc

        amazonScrapingHistory.commission = spm.commission

        amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther

        amazonScrapingHistory.returnProvision = spm.returnProvision

        amazonScrapingHistory.courierCost = val.courierCost

        amazonScrapingHistory.risky = val.risky

        amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)

        amazonScrapingHistory.totalSeller = amDetails.totalSeller

        amazonScrapingHistory.competitiveCategory = CompetitionCategory.BUY_BOX

        amazonScrapingHistory.timestamp = timestamp

        amazonScrapingHistory.multipleListings = amDetails.multipleListings

        proposed_sp = max(amDetails.secondLowestSellerSp - max((20, amDetails.secondLowestSellerSp*0.002)), amPricing.lowestPossibleSp)

        proposed_tp = getTargetTp(proposed_sp,spm,val)

        amazonScrapingHistory.proposedSp = proposed_sp

        amazonScrapingHistory.proposedTp = proposed_tp

        amazonScrapingHistory.marginIncreasedPotential = proposed_tp - amPricing.ourTp

        amazonScrapingHistory.multipleListings = amDetails.multipleListings

        amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days

        amazonScrapingHistory.isPromotion = val.isPromo

    session.commit()

def commitAmongCheapestAndCanCompete(amongCheapestAndCanCompete,timestamp,runType):

    for amongCheapestAndCanCompeteItem in amongCheapestAndCanCompete:

        val = amongCheapestAndCanCompeteItem[0]

        amDetails = amongCheapestAndCanCompeteItem[1]

        amPricing = amongCheapestAndCanCompeteItem[2]

        spm = val.sourcePercentage

        amazonScrapingHistory = AmazonScrapingHistory()

        amazonScrapingHistory.item_id = val.sku[3:]

        amazonScrapingHistory.warehouseLocation = val.state_id

        amazonScrapingHistory.parentCategoryId = val.parent_category

        amazonScrapingHistory.ourSellingPrice = amDetails.ourSp

        amazonScrapingHistory.promoPrice = amDetails.promoPrice

        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp

        amazonScrapingHistory.ourRank = amDetails.ourRank

        amazonScrapingHistory.ourInventory = val.ourInventory

        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp

        amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime

        amazonScrapingHistory.lowestSellerRating = amDetails.lowestSellerRating

        amazonScrapingHistory.lowestSellerType = amDetails.lowestSellerType

        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp

        amazonScrapingHistory.secondSellerShippingTime = amDetails.secondSellerShippingTime

        amazonScrapingHistory.secondSellerRating = amDetails.secondSellerRating

        amazonScrapingHistory.secondSellerType = amDetails.secondSellerType

        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp

        amazonScrapingHistory.thirdSellerShippingTime = amDetails.thirdSellerShippingTime

        amazonScrapingHistory.thirdSellerRating = amDetails.thirdSellerRating

        amazonScrapingHistory.thirdSellerType = amDetails.thirdSellerType

        amazonScrapingHistory.wanlc = val.nlc

        amazonScrapingHistory.commission = spm.commission

        amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther

        amazonScrapingHistory.returnProvision = spm.returnProvision

        amazonScrapingHistory.courierCost = val.courierCost

        amazonScrapingHistory.risky = val.risky

        amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)

        amazonScrapingHistory.totalSeller = amDetails.totalSeller

        amazonScrapingHistory.competitiveCategory = CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE

        amazonScrapingHistory.timestamp = timestamp

        amazonScrapingHistory.multipleListings = amDetails.multipleListings

        proposed_sp = max(amDetails.lowestSellerSp - max((5, amDetails.lowestSellerSp*0.001)), amPricing.lowestPossibleSp)

        proposed_tp = getTargetTp(proposed_sp,spm,val)

        amazonScrapingHistory.proposedSp = proposed_sp

        amazonScrapingHistory.proposedTp = proposed_tp

        amazonScrapingHistory.multipleListings = amDetails.multipleListings

        amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days

        amazonScrapingHistory.isPromotion = val.isPromo

    session.commit()

def commitCanCompete(canCompete,timestamp,runType):

    for canCompeteItem in canCompete:

        val = canCompeteItem[0]

        amDetails = canCompeteItem[1]

        amPricing = canCompeteItem[2]

        spm = val.sourcePercentage

        amazonScrapingHistory = AmazonScrapingHistory()

        amazonScrapingHistory.item_id = val.sku[3:]

        amazonScrapingHistory.warehouseLocation = val.state_id

        amazonScrapingHistory.parentCategoryId = val.parent_category

        amazonScrapingHistory.ourSellingPrice = amDetails.ourSp

        amazonScrapingHistory.promoPrice = amDetails.promoPrice

        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp

        amazonScrapingHistory.ourRank = amDetails.ourRank

        amazonScrapingHistory.ourInventory = val.ourInventory

        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp

        amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime

        amazonScrapingHistory.lowestSellerRating = amDetails.lowestSellerRating

        amazonScrapingHistory.lowestSellerType = amDetails.lowestSellerType

        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp

        amazonScrapingHistory.secondSellerShippingTime = amDetails.secondSellerShippingTime

        amazonScrapingHistory.secondSellerRating = amDetails.secondSellerRating

        amazonScrapingHistory.secondSellerType = amDetails.secondSellerType

        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp

        amazonScrapingHistory.thirdSellerShippingTime = amDetails.thirdSellerShippingTime

        amazonScrapingHistory.thirdSellerRating = amDetails.thirdSellerRating

        amazonScrapingHistory.thirdSellerType = amDetails.thirdSellerType

        amazonScrapingHistory.wanlc = val.nlc

        amazonScrapingHistory.commission = spm.commission

        amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther

        amazonScrapingHistory.returnProvision = spm.returnProvision

        amazonScrapingHistory.courierCost = val.courierCost

        amazonScrapingHistory.risky = val.risky

        amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)

        amazonScrapingHistory.totalSeller = amDetails.totalSeller

        amazonScrapingHistory.competitiveCategory = CompetitionCategory.COMPETITIVE

        amazonScrapingHistory.timestamp = timestamp

        amazonScrapingHistory.multipleListings = amDetails.multipleListings

        proposed_sp = max(amDetails.lowestSellerSp - max((5, amDetails.lowestSellerSp*0.001)), amPricing.lowestPossibleSp)

        proposed_tp = getTargetTp(proposed_sp,spm,val)

        amazonScrapingHistory.proposedSp = proposed_sp

        amazonScrapingHistory.proposedTp = proposed_tp

        amazonScrapingHistory.multipleListings = amDetails.multipleListings

        amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days

        amazonScrapingHistory.isPromotion = val.isPromo

    session.commit()

def commitAlmostCompete(almostCompete,timestamp,runType):

    for almostCompeteItem in almostCompete:

        val = almostCompeteItem[0]

        amDetails = almostCompeteItem[1]

        amPricing = almostCompeteItem[2]

        spm = val.sourcePercentage

        amazonScrapingHistory = AmazonScrapingHistory()

        amazonScrapingHistory.item_id = val.sku[3:]

        amazonScrapingHistory.warehouseLocation = val.state_id

        amazonScrapingHistory.parentCategoryId = val.parent_category

        amazonScrapingHistory.ourSellingPrice = amDetails.ourSp

        amazonScrapingHistory.promoPrice = amDetails.promoPrice

        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp

        amazonScrapingHistory.ourRank = amDetails.ourRank

        amazonScrapingHistory.ourInventory = val.ourInventory

        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp

        amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime

        amazonScrapingHistory.lowestSellerRating = amDetails.lowestSellerRating

        amazonScrapingHistory.lowestSellerType = amDetails.lowestSellerType

        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp

        amazonScrapingHistory.secondSellerShippingTime = amDetails.secondSellerShippingTime

        amazonScrapingHistory.secondSellerRating = amDetails.secondSellerRating

        amazonScrapingHistory.secondSellerType = amDetails.secondSellerType

        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp

        amazonScrapingHistory.thirdSellerShippingTime = amDetails.thirdSellerShippingTime

        amazonScrapingHistory.thirdSellerRating = amDetails.thirdSellerRating

        amazonScrapingHistory.thirdSellerType = amDetails.thirdSellerType

        amazonScrapingHistory.wanlc = val.nlc

        amazonScrapingHistory.commission = spm.commission

        amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther

        amazonScrapingHistory.returnProvision = spm.returnProvision

        amazonScrapingHistory.courierCost = val.courierCost

        amazonScrapingHistory.risky = val.risky

        amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)

        amazonScrapingHistory.totalSeller = amDetails.totalSeller

        amazonScrapingHistory.competitiveCategory = CompetitionCategory.ALMOST_COMPETE

        amazonScrapingHistory.timestamp = timestamp

        amazonScrapingHistory.multipleListings = amDetails.multipleListings

        proposed_sp = min(amDetails.lowestSellerSp*(1+.01),amPricing.lowestPossibleSp)

        proposed_tp = getTargetTp(proposed_sp,spm,val)

        target_nlc = proposed_tp - amPricing.lowestPossibleTp + val.nlc

        amazonScrapingHistory.proposedSp = proposed_sp

        amazonScrapingHistory.proposedTp = proposed_tp

        amazonScrapingHistory.targetNlc = target_nlc

        amazonScrapingHistory.multipleListings = amDetails.multipleListings

        amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days

        amazonScrapingHistory.isPromotion = val.isPromo

    session.commit()

def commitCantCompete(cantCompete, timestamp,runType):

    for cantCompeteItem in cantCompete:

        val = cantCompeteItem[0]

        amDetails = cantCompeteItem[1]

        amPricing = cantCompeteItem[2]

        spm = val.sourcePercentage

        amazonScrapingHistory = AmazonScrapingHistory()

        amazonScrapingHistory.item_id = val.sku[3:]

        amazonScrapingHistory.warehouseLocation = val.state_id

        amazonScrapingHistory.parentCategoryId = val.parent_category

        amazonScrapingHistory.ourSellingPrice = amDetails.ourSp

        amazonScrapingHistory.promoPrice = amDetails.promoPrice

        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp

        amazonScrapingHistory.ourRank = amDetails.ourRank

        amazonScrapingHistory.ourInventory = val.ourInventory

        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp

        amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime

        amazonScrapingHistory.lowestSellerRating = amDetails.lowestSellerRating

        amazonScrapingHistory.lowestSellerType = amDetails.lowestSellerType

        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp

        amazonScrapingHistory.secondSellerShippingTime = amDetails.secondSellerShippingTime

        amazonScrapingHistory.secondSellerRating = amDetails.secondSellerRating

        amazonScrapingHistory.secondSellerType = amDetails.secondSellerType

        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp

        amazonScrapingHistory.thirdSellerShippingTime = amDetails.thirdSellerShippingTime

        amazonScrapingHistory.thirdSellerRating = amDetails.thirdSellerRating

        amazonScrapingHistory.thirdSellerType = amDetails.thirdSellerType

        amazonScrapingHistory.wanlc = val.nlc

        amazonScrapingHistory.commission = spm.commission

        amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther

        amazonScrapingHistory.returnProvision = spm.returnProvision

        amazonScrapingHistory.courierCost = val.courierCost

        amazonScrapingHistory.risky = val.risky

        amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)

        amazonScrapingHistory.totalSeller = amDetails.totalSeller

        amazonScrapingHistory.competitiveCategory = CompetitionCategory.CANT_COMPETE

        amazonScrapingHistory.timestamp = timestamp

        amazonScrapingHistory.multipleListings = amDetails.multipleListings

        proposed_sp = amDetails.lowestSellerSp - max(5, amDetails.lowestSellerSp*0.001)

        proposed_tp = getTargetTp(proposed_sp,spm,val)

        target_nlc = proposed_tp - amPricing.lowestPossibleTp + val.nlc

        amazonScrapingHistory.proposedSp = proposed_sp

        amazonScrapingHistory.proposedTp = proposed_tp

        amazonScrapingHistory.targetNlc = target_nlc

        amazonScrapingHistory.multipleListings = amDetails.multipleListings

        amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days

        amazonScrapingHistory.isPromotion = val.isPromo

    session.commit()

def markAutoFavourites(time):

    nowAutoFav = []

    previouslyAutoFav = []

    stockList = []

    saleList = []

    items = session.query(func.sum(AmazonScrapingHistory.ourInventory),AmazonScrapingHistory.item_id).group_by(AmazonScrapingHistory.item_id).all()

    allItems = session.query(Amazonlisted).all()

    for item in items:

        reason = ""

        if item[0]>=5:

            stockList.append(item[1])

    for sku, val in saleMap.iteritems():

        totalSale = 0

        item_id = sku.replace('FBA','').replace('FBB','')

        val =saleMap.get('FBA'+str(item_id))

        if val is not None:

            for sale in val:

                totalSale += sale.totalOrderCount

        val =saleMap.get('FBB'+str(item_id))

        if val is not None:

            for sale in val:

                totalSale += sale.totalOrderCount

        if totalSale > 0:

            saleList.append(item_id)

    for aItem in allItems:

        reason = ""

        toMark = False

        if aItem.itemId in saleList:

            toMark = True

            reason+="Total FC sale is greater than 1 for last five days.."

        if aItem.itemId in stockList:

            toMark = True

            reason+="Item is present in buy box in last 3 days"

        if not aItem.autoFavourite:

            print "Item is not under auto favourite"

        if toMark:

            temp=[]

            temp.append(aItem.itemId)

            temp.append(reason)

            nowAutoFav.append(temp)

        if (not toMark) and aItem.autoFavourite:

            previouslyAutoFav.append(aItem.itemId)

        aItem.autoFavourite = toMark

    session.commit()

    return previouslyAutoFav, nowAutoFav

def writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav):

    wbk = xlwt.Workbook()

    sheet = wbk.add_sheet('Can\'t Compete')

    xstr = lambda s: s or ""

    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')

    excel_integer_format = '0'

    integer_style = xlwt.XFStyle()

    integer_style.num_format_str = excel_integer_format

    sheet.write(0, 0, "Item Id", heading_xf)

    sheet.write(0, 1, "Amazon Sku", heading_xf)

    sheet.write(0, 2, "Asin", heading_xf)

    sheet.write(0, 3, "Location", 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, "Our SP", heading_xf)

    sheet.write(0, 9, "Promo Price", heading_xf)

    sheet.write(0, 10, "Is Promotion", heading_xf)

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

    sheet.write(0, 12, "Rank", heading_xf)

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

    sheet.write(0, 14, "Lowest Seller SP", heading_xf)

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

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

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

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

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

    sheet.write(0, 20, "Third Lowest Seller SP", heading_xf)

    sheet.write(0, 21, "Third Lowest Seller Rating", heading_xf)

    sheet.write(0, 22, "Third Lowest Seller Shipping Time", heading_xf)

    sheet.write(0, 23, "WANLC", heading_xf)

    sheet.write(0, 24, "Commission", heading_xf)

    sheet.write(0, 25, "Competitor Commission", heading_xf)

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

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

    sheet.write(0, 28, "Risky", heading_xf)

    sheet.write(0, 29, "Proposed Sp", heading_xf)

    sheet.write(0, 30, "Proposed Tp", heading_xf)

    sheet.write(0, 31, "Target Nlc", heading_xf)

    sheet.write(0, 32, "Avg Sale", heading_xf)

    sheet.write(0, 33, "Sales History", heading_xf)

    sheet_iterator = 1

    cantCompeteItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()

    for cantCompeteItem in cantCompeteItems:

        amScraping =  cantCompeteItem[0]

        item = cantCompeteItem[1]

        sheet.write(sheet_iterator, 0, amScraping.item_id)

        if amScraping.warehouseLocation == 1:

            sku = 'FBA'+str(amScraping.item_id)

            loc = 'MUMBAI'

        else:

            sku = 'FBB'+str(amScraping.item_id)

            loc = 'BANGLORE'

        sheet.write(sheet_iterator, 1, sku)

        sheet.write(sheet_iterator, 2, (amazonAsinPrice.get(sku).asin))

        sheet.write(sheet_iterator, 3, loc)

        sheet.write(sheet_iterator, 4, item.brand)

        sheet.write(sheet_iterator, 5, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))

        sheet.write(sheet_iterator, 6, item.weight)

        sheet.write(sheet_iterator, 7, amScraping.courierCost)

        sheet.write(sheet_iterator, 8, amScraping.ourSellingPrice)

        sheet.write(sheet_iterator, 9, amScraping.promoPrice)

        if amScraping.isPromotion:

            sheet.write(sheet_iterator, 10, "Yes")

        else:

            sheet.write(sheet_iterator, 10, "Yes")

        sheet.write(sheet_iterator, 11, amScraping.lowestPossibleSp)