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,expiryDate):
        self.promoPrice = promoPrice
        self.subsidy = subsidy
        self.promotionType = promotionType
        self.expiryDate = expiryDate 
    


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)
                continue
        
        if autoDecrementItem.competitiveCategory == CompetitionCategory.COMPETITIVE and not autoDecrementItem.isPromotion:
            if autoDecrementItem.parentCategoryId in [10006,10009,11001]:
                if daysOfStock < 1 :
                    markReasonForItem(autoDecrementItem,'Days of stock less than 1',Decision.AUTO_DECREMENT_FAILED)
                    continue
                
            else:
                if daysOfStock < 3:
                    markReasonForItem(autoDecrementItem,'Days of stock less than 3',Decision.AUTO_DECREMENT_FAILED)
                    continue
        
        if autoDecrementItem.competitiveCategory == CompetitionCategory.COMPETITIVE and autoDecrementItem.isPromotion:
            if autoDecrementItem.parentCategoryId in [10006,10009,11001]:
                if (amazonLongTermActivePromotions.get(sku).expiryDate - datetime.now()).days >2 and daysOfStock < 1 :
                    markReasonForItem(autoDecrementItem,'Promo Item, expiry after 2 days or not enough stock',Decision.AUTO_DECREMENT_FAILED)
                    continue
                
            else:
                if (amazonLongTermActivePromotions.get(sku).expiryDate - datetime.now()).days >2 and daysOfStock < 3:
                    markReasonForItem(autoDecrementItem,'Promo Item, expiry after 2 days or not enough stock',Decision.AUTO_DECREMENT_FAILED)
                    continue
                    
        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,amPromotion.endDate)
    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,amPromotion.endDate)
    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 ourPricing is None or 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) is None or 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)
        if amazonScrapingHistory.isPromotion:
            if amazonLongTermActivePromotions.has_key(val.sku):
                proposed_sp = min(proposed_sp,(amazonLongTermActivePromotions.has_key(val.sku)).salePrice)
            else:
                proposed_sp = min(proposed_sp,(amazonShortTermActivePromotions.has_key(val.sku)).salePrice)
        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)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 12, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 12, amScraping.ourRank)
        sheet.write(sheet_iterator, 13, amScraping.ourInventory)
        sheet.write(sheet_iterator, 14, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 15, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 16, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 20, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 23, amScraping.wanlc)
        sheet.write(sheet_iterator, 24, amScraping.commission)
        sheet.write(sheet_iterator, 25, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 26, amScraping.returnProvision)
        sheet.write(sheet_iterator, 27, round(amScraping.ourSellingPrice - amScraping.lowestPossibleSp))
        sheet.write(sheet_iterator, 28, item.risky)
        sheet.write(sheet_iterator, 29, amScraping.proposedSp)
        sheet.write(sheet_iterator, 30, amScraping.proposedTp)
        sheet.write(sheet_iterator, 31, amScraping.targetNlc)
        sheet.write(sheet_iterator, 32, amScraping.avgSale)
        sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
        sheet_iterator+=1
    
    sheet = wbk.add_sheet('Competitive')
    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, "Avg Sale", heading_xf)
    sheet.write(0, 32, "Sales History", heading_xf)
    
    sheet_iterator = 1
    competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).all()
    for competitiveItem in competitiveItems:
        amScraping =  competitiveItem[0]
        item = competitiveItem[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)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 12, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 12, amScraping.ourRank)
        sheet.write(sheet_iterator, 13, amScraping.ourInventory)
        sheet.write(sheet_iterator, 14, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 15, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 16, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 20, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 23, amScraping.wanlc)
        sheet.write(sheet_iterator, 24, amScraping.commission)
        sheet.write(sheet_iterator, 25, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 26, amScraping.returnProvision)
        sheet.write(sheet_iterator, 27, round(amScraping.ourSellingPrice - amScraping.lowestPossibleSp))
        sheet.write(sheet_iterator, 28, item.risky)
        sheet.write(sheet_iterator, 29, amScraping.proposedSp)
        sheet.write(sheet_iterator, 30, amScraping.proposedTp)
        sheet.write(sheet_iterator, 31, amScraping.avgSale)
        sheet.write(sheet_iterator, 32, getOosString(saleMap.get(sku)))
        sheet_iterator+=1
    
    sheet = wbk.add_sheet('Almost Competitive')
    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, "Avg Sale", heading_xf)
    sheet.write(0, 32, "Sales History", heading_xf)
    
    sheet_iterator = 1
    almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).all()
    for almostCompetitiveItem in almostCompetitiveItems:
        amScraping =  almostCompetitiveItem[0]
        item = almostCompetitiveItem[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'
        amScraping =  competitiveItem[0]
        item = competitiveItem[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)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 12, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 12, amScraping.ourRank)
        sheet.write(sheet_iterator, 13, amScraping.ourInventory)
        sheet.write(sheet_iterator, 14, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 15, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 16, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 20, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 23, amScraping.wanlc)
        sheet.write(sheet_iterator, 24, amScraping.commission)
        sheet.write(sheet_iterator, 25, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 26, amScraping.returnProvision)
        sheet.write(sheet_iterator, 27, round(amScraping.ourSellingPrice - amScraping.lowestPossibleSp))
        sheet.write(sheet_iterator, 28, item.risky)
        sheet.write(sheet_iterator, 29, amScraping.proposedSp)
        sheet.write(sheet_iterator, 30, amScraping.proposedTp)
        sheet.write(sheet_iterator, 31, amScraping.avgSale)
        sheet.write(sheet_iterator, 32, getOosString(saleMap.get(sku)))
        sheet_iterator+=1
    
    sheet = wbk.add_sheet('Among Cheapest')
    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, "Avg Sale", heading_xf)
    sheet.write(0, 32, "Sales History", heading_xf)
    
    sheet_iterator = 1
    amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE).all()
    for amongCheapestItem in amongCheapestItems:
        amScraping =  amongCheapestItem[0]
        item = amongCheapestItem[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)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 12, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 12, amScraping.ourRank)
        sheet.write(sheet_iterator, 13, amScraping.ourInventory)
        sheet.write(sheet_iterator, 14, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 15, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 16, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 20, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 23, amScraping.wanlc)
        sheet.write(sheet_iterator, 24, amScraping.commission)
        sheet.write(sheet_iterator, 25, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 26, amScraping.returnProvision)
        sheet.write(sheet_iterator, 27, round(amScraping.ourSellingPrice - amScraping.lowestPossibleSp))
        sheet.write(sheet_iterator, 28, item.risky)
        sheet.write(sheet_iterator, 29, amScraping.proposedSp)
        sheet.write(sheet_iterator, 30, amScraping.proposedTp)
        sheet.write(sheet_iterator, 31, amScraping.avgSale)
        sheet.write(sheet_iterator, 32, getOosString(saleMap.get(sku)))
        sheet_iterator+=1
    
    sheet = wbk.add_sheet('Cheapest')
    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, "Margin Increased Potential", heading_xf)
    sheet.write(0, 32, "Avg Sale", heading_xf)
    sheet.write(0, 33, "Sales History", heading_xf)
    
    sheet_iterator = 1
    cheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
    for cheapestItem in cheapestItems:
        amScraping =  cheapestItem[0]
        item = cheapestItem[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)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 12, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 12, amScraping.ourRank)
        sheet.write(sheet_iterator, 13, amScraping.ourInventory)
        sheet.write(sheet_iterator, 14, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 15, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 16, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 20, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 23, amScraping.wanlc)
        sheet.write(sheet_iterator, 24, amScraping.commission)
        sheet.write(sheet_iterator, 25, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 26, amScraping.returnProvision)
        sheet.write(sheet_iterator, 27, round(amScraping.ourSellingPrice - amScraping.lowestPossibleSp))
        sheet.write(sheet_iterator, 28, item.risky)
        sheet.write(sheet_iterator, 29, amScraping.proposedSp)
        sheet.write(sheet_iterator, 30, amScraping.proposedTp)
        sheet.write(sheet_iterator, 31, amScraping.targetNlc)
        sheet.write(sheet_iterator, 32, amScraping.avgSale)
        sheet.write(sheet_iterator, 33, getOosString(saleMap.get(sku)))
        sheet_iterator+=1
    
    sheet = wbk.add_sheet('Negative Margin')
    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, "Avg Sale", heading_xf)
    sheet.write(0, 29, "Sales History", heading_xf)
    
    sheet_iterator = 1
    amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE).all()
    for amongCheapestItem in amongCheapestItems:
        amScraping =  amongCheapestItem[0]
        item = amongCheapestItem[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)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 12, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 12, amScraping.ourRank)
        sheet.write(sheet_iterator, 13, amScraping.ourInventory)
        sheet.write(sheet_iterator, 14, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 15, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 16, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 18, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 19, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 20, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 21, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 22, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 23, amScraping.wanlc)
        sheet.write(sheet_iterator, 24, amScraping.commission)
        sheet.write(sheet_iterator, 25, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 26, amScraping.returnProvision)
        sheet.write(sheet_iterator, 27, round(amScraping.ourTp - amScraping.lowestPossibleTp))
        sheet.write(sheet_iterator, 28, amScraping.avgSale)
        sheet.write(sheet_iterator, 29, getOosString(saleMap.get(sku)))
        sheet_iterator+=1
    
    sheet = wbk.add_sheet('Exception List')
    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, "Reason", heading_xf)
    
    sheet_iterator = 1
    amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE).all()
    for amongCheapestItem in amongCheapestItems:
        amScraping =  amongCheapestItem[0]
        item = amongCheapestItem[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, amScraping.reason)
        sheet_iterator+=1      
    
    filename = "/tmp/amazon-scraping.xls"
    wbk.save(filename)
    
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)
    time.sleep(5)
    timestamp = datetime.now()
    fetchFbaSale()
    itemInfo = populateStuff(timestamp,options.runType)
    itemsToPopulate = 0
    toSync = 0
    lenItems = len(itemInfo)
    while(toSync < lenItems):
        oldSync = toSync
        if lenItems >= 20:
            toSync = 20
        else:
            toSync = lenItems - oldSync
        getPriceAndAsin(itemInfo[oldSync:toSync+oldSync])
        toSync = oldSync + toSync
        
    while (len(itemInfo)>0):
        if len(itemInfo) >= 20:
            itemsToPopulate = 20
        else:
            itemsToPopulate = len(itemInfo)
        print itemsToPopulate
        exceptionList, negativeMargin, cheapest, amongCheapestAndCanCompete, canCompete, almostCompete, cantCompete = decideCategory(itemInfo[0:itemsToPopulate])
        itemInfo[0:itemsToPopulate] = []
        commitExceptionList(exceptionList,timestamp,options.runType)
        commitNegativeMargin(negativeMargin,timestamp,options.runType)
        commitCheapest(cheapest,timestamp,options.runType)
        commitAmongCheapestAndCanCompete(amongCheapestAndCanCompete,timestamp,options.runType)
        commitCanCompete(canCompete,timestamp,options.runType)
        commitAlmostCompete(almostCompete,timestamp,options.runType)
        commitCantCompete(cantCompete, timestamp,options.runType)
        exceptionList[:], negativeMargin[:], cheapest[:], amongCheapestAndCanCompete[:], canCompete[:], almostCompete[:], cantCompete[:] =[],[],[],[],[],[],[]
    autoDecreaseItems = fetchItemsForAutoDecrease(timestamp)
    autoIncreaseItems = fetchItemsForAutoIncrease(timestamp)
    previousAutoFav, nowAutoFav = markAutoFavourites(timestamp)
    writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav)
if __name__=='__main__':
    main()