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, SalesPotential,\
Decision, RunType, AmazonPromotionType
from shop2020.model.v1.catalog.script import SellerCentralInventoryReport, AmazonAsyncScraper
from shop2020.clients.CatalogClient import CatalogClient
from shop2020.clients.InventoryClient import InventoryClient
from shop2020.clients.TransactionClient import TransactionClient
import urllib2
import time 
from datetime import date, datetime, timedelta
from shop2020.utils import EmailAttachmentSender
from shop2020.utils.EmailAttachmentSender import get_attachment_part
import math
import simplejson as json
import xlwt
import optparse
import sys
import smtplib
from email.mime.text import MIMEText
import email
from email.mime.multipart import MIMEMultipart
import email.encoders
import mechanize
import cookielib


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.AmazonAsyncScraper()

class __AmazonAsinPrice:
    def __init__(self, asin, price):
        self.asin = asin
        self.price = price

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

class __AmazonPricing:
    
    def __init__(self, ourSp, ourTp, lowestPossibleTp, lowestPossibleSp):
        self.ourTp = ourTp
        self.lowestPossibleTp = lowestPossibleTp
        self.ourSp = ourSp
        self.lowestPossibleSp = lowestPossibleSp


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 sku in amazonShortTermActivePromotions:
            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 sku in amazonShortTermActivePromotions:
            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 getOosString(oosStatus):
    lastNdaySale=""
    for obj in oosStatus:
        if obj.is_oos:
            lastNdaySale += "X-"
        else:
            lastNdaySale += str(obj.num_orders) + "-"
    return lastNdaySale[:-1]

def getLastDaySale(fbaSaleSnapshot):
    if fbaSaleSnapshot.item_id==0:
        return 0
    else:
        return fbaSaleSnapshot.totalSale
            
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 calculateAverageSale(sku):
    count,sale = 0,0
    oosStatus = saleMap.get(sku)
    for obj in oosStatus:
        if not obj.isOutOfStock:
            count+=1
            sale = sale+obj.totalSales
    avgSalePerDay=0 if count==0 else (float(sale)/count)
    return round(avgSalePerDay,2)
    
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 "length****"
    print len(fbaAvailableInventorySnapshot)
    for fbaInventoryItem in fbaAvailableInventorySnapshot:
        d_amazon_listed = Amazonlisted.get_by(itemId=fbaInventoryItem.item_id)
        if d_amazon_listed is None:
            print "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
        print "$$$$$$$$$$$$$$$$$"
        print fbaInventoryItem
        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:
            print "continue*****"
            continue
        cc = computeCourierCost(it.weight)
        if amazonAsinPrice.get(sku) is None:
            asin = ''
        elif amazonAsinPrice.get(sku).asin is None:
            asin = ''
        else:
            asin = amazonAsinPrice.get(sku).asin
            
        amazonItemInfo = __AmazonItemInfo(asin, 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)
        print amazonItemInfo
        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.append(amPromotion.sku)
    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.append(amPromotion.sku)
    session.close()
    print "item info length"
    print len(itemInfo)
    return itemInfo

def decideCategory(itemInfo):
    exceptionList, negativeMargin, cheapest, amongCheapestAndCanCompete, canCompete, almostCompete, cantCompete = [],[],[],[],[],[],[] 
    skuUrls = []
    #skuAsinMap = {}
    for item in itemInfo:
        if item.asin is None or len(item.asin)==0:
            temp = []
            temp.append(item)
            temp.append("Asin not available")
            exceptionList.append(temp)
            continue
        skuUrls.append('http://www.amazon.in/gp/offer-listing/'+item.asin+'/ref=olp_sort_ps')
    aggResponse = amScraper.read(skuUrls, True)

    for val in itemInfo:
        print "Trying to get details of ",val.sku
        if val.asin is None or len(val.asin)==0:
            continue
        scrapInfo = aggResponse.get(val.asin)
        print "Scraped info for asin is ",scrapInfo
        if val.sku in amazonLongTermActivePromotions:
            print "Sku in promotion, will handle it later.Moving to other..."
            continue
        if scrapInfo is None or val.nlc==0:
            temp = []
            temp.append(val)
            if val.nlc==0 or val.nlc is None:
                temp.append("WANLC is 0")
            else:
                temp.append("Not able to fetch")
            exceptionList.append(temp)
            continue
        iterator = 0
        sku, lowestSellerName,secondLowestSellerName, thirdLowestSellerName = ('',)*4
        ourSp, ourRank, lowestSellerSp, secondLowestSellerSp, thirdLowestSellerSp, ourTp, lowestPossibleSp, lowestPossibleTp = (0,)*8
        sku = val.sku
        scrapedSkuLocation = None
        multipleListings = False
        for info in scrapInfo:
            print "iterating scrap info"
            if iterator ==3:
                break
            if (info['sellerName']).strip()=='Saholic':
                if ourRank>0:
                    multipleListings = True
                ourSp = info['sellerPrice']
                ourRank = iterator+1
                if val.state_id==1:
                    #It means sku starts with FBA
                    fbaPrice = (amazonAsinPrice.get(val.sku)).price
                    try:
                        if ourSp==fbaPrice:
                            scrapedSkuLocation = val.state_id
                    except:
                        scrapedSkuLocation = None
                elif val.state_id==2:
                    #It means sku starts with FBB
                    fbbPrice = (amazonAsinPrice.get(val.sku)).price
                    try:
                        if ourSp==fbbPrice:
                            scrapedSkuLocation = val.state_id
                    except:
                        scrapedSkuLocation = None
                else:
                    scrapedSkuLocation = None
                if scrapedSkuLocation is None:
                    print "fishy...confused for ", val.sku
            
            if iterator == 0:
                lowestSellerName = info['sellerName']
                lowestSellerSp = info['sellerPrice']
            
            if iterator == 1:
                secondLowestSellerName = info['sellerName']
                secondLowestSellerSp = info['sellerPrice']
            
            if iterator == 2:
                thirdLowestSellerName = info['sellerName']
                thirdLowestSellerSp = info['sellerPrice']
            
            iterator += 1
        print "terminating iterator"
        #if cheapestSkuLocation!=val.state_id
        
        if ourSp==0 or scrapedSkuLocation is None:
            print "Sku not present in top 3.Getting price from amazonAsinPrice...or multiple listings"
            if ourSp==0:
                ourRank = 999 #Due to pagination and large no of sellers.Taking it as dummy value, means we are not in top 3
                ourSp = (amazonAsinPrice.get(val.sku)).price
                if ourSp is None or ourSp==0:
                    temp = []
                    temp.append(val)
                    temp.append("Price not available")
                    exceptionList.append(temp)
                    continue
            else:
                #determine rank
                if ourSp <= lowestSellerSp or lowestSellerSp==0:
                    ourRank = 1
                elif ourSp > lowestSellerSp and (ourSp <= secondLowestSellerSp or secondLowestSellerSp==0):
                    ourRank = 2
                elif ourSp > secondLowestSellerSp and (ourSp<=thirdLowestSellerSp or thirdLowestSellerSp==0):
                    ourRank = 3
                else:
                    ourRank = 999
        
        if multipleListings:
            print "multiple listings..."
            ourSp = (amazonAsinPrice.get(val.sku)).price
            if ourSp is None or ourSp==0:
                temp = []
                temp.append(val)
                temp.append("Price not available")
                exceptionList.append(temp)
                continue
            if ourSp <= lowestSellerSp:
                    ourRank = 1
            elif ourSp > lowestSellerSp and (ourSp <= secondLowestSellerSp or secondLowestSellerSp==0):
                ourRank = 2
            elif ourSp > secondLowestSellerSp and (ourSp<=thirdLowestSellerSp or thirdLowestSellerSp==0):
                ourRank = 3
            else:
                ourRank = 999
            
        
        print "Creating object am details",val.sku
        amDetails = __AmazonDetails(sku, ourSp, ourRank, lowestSellerName,lowestSellerSp,secondLowestSellerName, secondLowestSellerSp, thirdLowestSellerName, thirdLowestSellerSp,len(scrapInfo),multipleListings)
        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:
                item = Item.query.filter_by(id=int(val.sku[3:]).first()
                if item is None:
                    raise
                vatMaster = CategoryVatMaster.query.filter(and_(CategoryVatMaster.categoryId==item.category, CategoryVatMaster.minVal<=amDetails.ourSp,  CategoryVatMaster.maxVal>=amDetails.ourSp,  CategoryVatMaster.stateId == val.state_id)).first()
            if vatMaster is None:
                raise 
                val.vatRate = vatMaster.vatPercent
            else:
                val.vatRate = itemVatMaster.vatPercentage
            print "vat fetched"
        except:
            print "vat exception"
            temp = []
            temp.append(val)
            temp.append("Vat not available")
            exceptionList.append(temp)
            print "added in exception list"
            continue
        
        ourTp = getOurTp(amDetails,val,val.sourcePercentage)
        lowestPossibleTp = getLowestPossibleTp(amDetails,val,val.sourcePercentage)
        lowestPossibleSp = getLowestPossibleSp(amDetails,val,val.sourcePercentage)
        print "Creating pricing obj"
        amPricing = __AmazonPricing(ourSp,ourTp,lowestPossibleTp,lowestPossibleSp)
        
        if amPricing.ourTp < amPricing.lowestPossibleTp:
            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.ourSp - amDetails.lowestSellerSp))/amDetails.ourSp)<=.01) or ((amDetails.ourSp - 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 getOurTp(amazonDetails,val,spm):
    ourTp = amazonDetails.ourSp- amazonDetails.ourSp*(spm.commission/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost)*(1+(spm.serviceTax/100))*(1+(spm.serviceTax/100));
    return round(ourTp,2)

def getLowestPossibleTp(amazonDetails,val,spm):
    vat = (amazonDetails.ourSp/(1+(val.vatRate/100))-(val.nlc/(1+(val.vatRate/100))))*(val.vatRate/100)
    inHouseCost = 15+vat+(spm.returnProvision/100)*amazonDetails.ourSp
    lowest_possible_tp = val.nlc+inHouseCost
    return round(lowest_possible_tp,2)

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));
    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+(val.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.ourTp = amPricing.ourTp
        amazonScrapingHistory.lowestPossibleTp = amPricing.lowestPossibleTp
        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
        amazonScrapingHistory.ourRank = amDetails.ourRank
        amazonScrapingHistory.ourInventory = val.ourInventory
        amazonScrapingHistory.lowestSellerName = amDetails.lowestSellerName
        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
        amazonScrapingHistory.secondLowestSellerName = amDetails.secondLowestSellerName
        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp
        amazonScrapingHistory.thirdLowestSellerName = amDetails.thirdLowestSellerName
        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp
        amazonScrapingHistory.wanlc = val.nlc
        amazonScrapingHistory.commission = spm.commission
        amazonScrapingHistory.competitorCommission = spm.competitorCommission
        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
    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.ourTp = amPricing.ourTp
        amazonScrapingHistory.lowestPossibleTp = amPricing.lowestPossibleTp
        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
        amazonScrapingHistory.ourRank = amDetails.ourRank
        amazonScrapingHistory.ourInventory = val.ourInventory
        amazonScrapingHistory.lowestSellerName = amDetails.lowestSellerName
        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
        amazonScrapingHistory.secondLowestSellerName = amDetails.secondLowestSellerName
        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp
        amazonScrapingHistory.thirdLowestSellerName = amDetails.thirdLowestSellerName
        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp
        amazonScrapingHistory.wanlc = val.nlc
        amazonScrapingHistory.commission = spm.commission
        amazonScrapingHistory.competitorCommission = spm.competitorCommission
        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
        if amDetails.secondLowestSellerName!='Saholic':
            competitorSp = amDetails.secondLowestSellerSp
        else:
            competitorSp = amDetails.thirdLowestSellerSp
        proposed_sp = max(competitorSp - max((20, competitorSp*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
    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.ourTp = amPricing.ourTp
        amazonScrapingHistory.lowestPossibleTp = amPricing.lowestPossibleTp
        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
        amazonScrapingHistory.ourRank = amDetails.ourRank
        amazonScrapingHistory.ourInventory = val.ourInventory
        amazonScrapingHistory.lowestSellerName = amDetails.lowestSellerName
        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
        amazonScrapingHistory.secondLowestSellerName = amDetails.secondLowestSellerName
        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp
        amazonScrapingHistory.thirdLowestSellerName = amDetails.thirdLowestSellerName
        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp
        amazonScrapingHistory.wanlc = val.nlc
        amazonScrapingHistory.commission = spm.commission
        amazonScrapingHistory.competitorCommission = spm.competitorCommission
        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
    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.ourTp = amPricing.ourTp
        amazonScrapingHistory.lowestPossibleTp = amPricing.lowestPossibleTp
        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
        amazonScrapingHistory.ourRank = amDetails.ourRank
        amazonScrapingHistory.ourInventory = val.ourInventory
        amazonScrapingHistory.lowestSellerName = amDetails.lowestSellerName
        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
        amazonScrapingHistory.secondLowestSellerName = amDetails.secondLowestSellerName
        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp
        amazonScrapingHistory.thirdLowestSellerName = amDetails.thirdLowestSellerName
        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp
        amazonScrapingHistory.wanlc = val.nlc
        amazonScrapingHistory.commission = spm.commission
        amazonScrapingHistory.competitorCommission = spm.competitorCommission
        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
    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.ourTp = amPricing.ourTp
        amazonScrapingHistory.lowestPossibleTp = amPricing.lowestPossibleTp
        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
        amazonScrapingHistory.ourRank = amDetails.ourRank
        amazonScrapingHistory.ourInventory = val.ourInventory
        amazonScrapingHistory.lowestSellerName = amDetails.lowestSellerName
        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
        amazonScrapingHistory.secondLowestSellerName = amDetails.secondLowestSellerName
        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp
        amazonScrapingHistory.thirdLowestSellerName = amDetails.thirdLowestSellerName
        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp
        amazonScrapingHistory.wanlc = val.nlc
        amazonScrapingHistory.commission = spm.commission
        amazonScrapingHistory.competitorCommission = spm.competitorCommission
        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
    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.ourTp = amPricing.ourTp
        amazonScrapingHistory.lowestPossibleTp = amPricing.lowestPossibleTp
        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp
        amazonScrapingHistory.ourRank = amDetails.ourRank
        amazonScrapingHistory.ourInventory = val.ourInventory
        amazonScrapingHistory.lowestSellerName = amDetails.lowestSellerName
        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp
        amazonScrapingHistory.secondLowestSellerName = amDetails.secondLowestSellerName
        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp
        amazonScrapingHistory.thirdLowestSellerName = amDetails.thirdLowestSellerName
        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp
        amazonScrapingHistory.wanlc = val.nlc
        amazonScrapingHistory.commission = spm.commission
        amazonScrapingHistory.competitorCommission = spm.competitorCommission
        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
    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, "Our Tp", heading_xf)
    sheet.write(0, 10, "Lowest Possible SP", heading_xf)
    sheet.write(0, 11, "Lowest Possible TP", heading_xf)
    sheet.write(0, 12, "Rank", heading_xf)
    sheet.write(0, 13, "Our Inventory", heading_xf)
    sheet.write(0, 14, "Lowest Seller Name", heading_xf)
    sheet.write(0, 15, "Lowest Seller SP", heading_xf)
    sheet.write(0, 16, "Second Lowest Seller Name", heading_xf)
    sheet.write(0, 17, "Second Lowest Seller SP", heading_xf)
    sheet.write(0, 18, "Third Lowest Seller Name", heading_xf)
    sheet.write(0, 19, "Third Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "WANLC", heading_xf)
    sheet.write(0, 21, "Commission", heading_xf)
    sheet.write(0, 22, "Competitor Commission", heading_xf)
    sheet.write(0, 23, "Return Provision", heading_xf)
    sheet.write(0, 24, "Margin", heading_xf)
    sheet.write(0, 25, "Risky", heading_xf)
    sheet.write(0, 26, "Proposed Sp", heading_xf)
    sheet.write(0, 27, "Proposed Tp", heading_xf)
    sheet.write(0, 28, "Target Nlc", heading_xf)
    sheet.write(0, 29, "Avg Sale", heading_xf)
    sheet.write(0, 30, "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.ourTp)
        sheet.write(sheet_iterator, 10, amScraping.lowestPossibleSp)
        sheet.write(sheet_iterator, 11, amScraping.lowestPossibleTp)
        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.lowestSellerName)
        sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 16, amScraping.secondLowestSellerName)
        sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 18, amScraping.thirdLowestSellerName)
        sheet.write(sheet_iterator, 19, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 20, amScraping.wanlc)
        sheet.write(sheet_iterator, 21, amScraping.commission)
        sheet.write(sheet_iterator, 22, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 23, amScraping.returnProvision)
        sheet.write(sheet_iterator, 24, round(amScraping.ourTp - amScraping.lowestPossibleTp))
        sheet.write(sheet_iterator, 25, item.risky)
        sheet.write(sheet_iterator, 26, amScraping.proposedSp)
        sheet.write(sheet_iterator, 27, amScraping.proposedTp)
        sheet.write(sheet_iterator, 28, amScraping.targetNlc)
        sheet.write(sheet_iterator, 29, amScraping.avgSale)
        sheet.write(sheet_iterator, 30, 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, "Our Tp", heading_xf)
    sheet.write(0, 10, "Lowest Possible SP", heading_xf)
    sheet.write(0, 11, "Lowest Possible TP", heading_xf)
    sheet.write(0, 12, "Rank", heading_xf)
    sheet.write(0, 13, "Our Inventory", heading_xf)
    sheet.write(0, 14, "Lowest Seller Name", heading_xf)
    sheet.write(0, 15, "Lowest Seller SP", heading_xf)
    sheet.write(0, 16, "Second Lowest Seller Name", heading_xf)
    sheet.write(0, 17, "Second Lowest Seller SP", heading_xf)
    sheet.write(0, 18, "Third Lowest Seller Name", heading_xf)
    sheet.write(0, 19, "Third Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "WANLC", heading_xf)
    sheet.write(0, 21, "Commission", heading_xf)
    sheet.write(0, 22, "Competitor Commission", heading_xf)
    sheet.write(0, 23, "Return Provision", heading_xf)
    sheet.write(0, 24, "Margin", heading_xf)
    sheet.write(0, 25, "Risky", heading_xf)
    sheet.write(0, 26, "Proposed Sp", heading_xf)
    sheet.write(0, 27, "Proposed Tp", heading_xf)
    sheet.write(0, 28, "Avg Sale", heading_xf)
    sheet.write(0, 29, "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.ourTp)
        sheet.write(sheet_iterator, 10, amScraping.lowestPossibleSp)
        sheet.write(sheet_iterator, 11, amScraping.lowestPossibleTp)
        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.lowestSellerName)
        sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 16, amScraping.secondLowestSellerName)
        sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 18, amScraping.thirdLowestSellerName)
        sheet.write(sheet_iterator, 19, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 20, amScraping.wanlc)
        sheet.write(sheet_iterator, 21, amScraping.commission)
        sheet.write(sheet_iterator, 22, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 23, amScraping.returnProvision)
        sheet.write(sheet_iterator, 24, round(amScraping.ourTp - amScraping.lowestPossibleTp))
        sheet.write(sheet_iterator, 25, item.risky)
        sheet.write(sheet_iterator, 26, amScraping.proposedSp)
        sheet.write(sheet_iterator, 27, amScraping.proposedTp)
        sheet.write(sheet_iterator, 28, amScraping.avgSale)
        sheet.write(sheet_iterator, 29, 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, "Our Tp", heading_xf)
    sheet.write(0, 10, "Lowest Possible SP", heading_xf)
    sheet.write(0, 11, "Lowest Possible TP", heading_xf)
    sheet.write(0, 12, "Rank", heading_xf)
    sheet.write(0, 13, "Our Inventory", heading_xf)
    sheet.write(0, 14, "Lowest Seller Name", heading_xf)
    sheet.write(0, 15, "Lowest Seller SP", heading_xf)
    sheet.write(0, 16, "Second Lowest Seller Name", heading_xf)
    sheet.write(0, 17, "Second Lowest Seller SP", heading_xf)
    sheet.write(0, 18, "Third Lowest Seller Name", heading_xf)
    sheet.write(0, 19, "Third Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "WANLC", heading_xf)
    sheet.write(0, 21, "Commission", heading_xf)
    sheet.write(0, 22, "Competitor Commission", heading_xf)
    sheet.write(0, 23, "Return Provision", heading_xf)
    sheet.write(0, 24, "Margin", heading_xf)
    sheet.write(0, 25, "Risky", heading_xf)
    sheet.write(0, 26, "Proposed Sp", heading_xf)
    sheet.write(0, 27, "Proposed Tp", heading_xf)
    sheet.write(0, 28, "Avg Sale", heading_xf)
    sheet.write(0, 29, "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'
        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.ourTp)
        sheet.write(sheet_iterator, 10, amScraping.lowestPossibleSp)
        sheet.write(sheet_iterator, 11, amScraping.lowestPossibleTp)
        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.lowestSellerName)
        sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 16, amScraping.secondLowestSellerName)
        sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 18, amScraping.thirdLowestSellerName)
        sheet.write(sheet_iterator, 19, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 20, amScraping.wanlc)
        sheet.write(sheet_iterator, 21, amScraping.commission)
        sheet.write(sheet_iterator, 22, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 23, amScraping.returnProvision)
        sheet.write(sheet_iterator, 24, round(amScraping.ourTp - amScraping.lowestPossibleTp))
        sheet.write(sheet_iterator, 25, item.risky)
        sheet.write(sheet_iterator, 26, amScraping.proposedSp)
        sheet.write(sheet_iterator, 27, amScraping.proposedTp)
        sheet.write(sheet_iterator, 28, amScraping.avgSale)
        sheet.write(sheet_iterator, 29, 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, "Our Tp", heading_xf)
    sheet.write(0, 10, "Lowest Possible SP", heading_xf)
    sheet.write(0, 11, "Lowest Possible TP", heading_xf)
    sheet.write(0, 12, "Rank", heading_xf)
    sheet.write(0, 13, "Our Inventory", heading_xf)
    sheet.write(0, 14, "Lowest Seller Name", heading_xf)
    sheet.write(0, 15, "Lowest Seller SP", heading_xf)
    sheet.write(0, 16, "Second Lowest Seller Name", heading_xf)
    sheet.write(0, 17, "Second Lowest Seller SP", heading_xf)
    sheet.write(0, 18, "Third Lowest Seller Name", heading_xf)
    sheet.write(0, 19, "Third Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "WANLC", heading_xf)
    sheet.write(0, 21, "Commission", heading_xf)
    sheet.write(0, 22, "Competitor Commission", heading_xf)
    sheet.write(0, 23, "Return Provision", heading_xf)
    sheet.write(0, 24, "Margin", heading_xf)
    sheet.write(0, 25, "Risky", heading_xf)
    sheet.write(0, 26, "Proposed Sp", heading_xf)
    sheet.write(0, 27, "Proposed Tp", 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.ourTp)
        sheet.write(sheet_iterator, 10, amScraping.lowestPossibleSp)
        sheet.write(sheet_iterator, 11, amScraping.lowestPossibleTp)
        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.lowestSellerName)
        sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 16, amScraping.secondLowestSellerName)
        sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 18, amScraping.thirdLowestSellerName)
        sheet.write(sheet_iterator, 19, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 20, amScraping.wanlc)
        sheet.write(sheet_iterator, 21, amScraping.commission)
        sheet.write(sheet_iterator, 22, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 23, amScraping.returnProvision)
        sheet.write(sheet_iterator, 24, round(amScraping.ourTp - amScraping.lowestPossibleTp))
        sheet.write(sheet_iterator, 25, item.risky)
        sheet.write(sheet_iterator, 26, amScraping.proposedSp)
        sheet.write(sheet_iterator, 27, amScraping.proposedTp)
        sheet.write(sheet_iterator, 28, amScraping.avgSale)
        sheet.write(sheet_iterator, 29, 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, "Our Tp", heading_xf)
    sheet.write(0, 10, "Lowest Possible SP", heading_xf)
    sheet.write(0, 11, "Lowest Possible TP", heading_xf)
    sheet.write(0, 12, "Rank", heading_xf)
    sheet.write(0, 13, "Our Inventory", heading_xf)
    sheet.write(0, 14, "Lowest Seller Name", heading_xf)
    sheet.write(0, 15, "Lowest Seller SP", heading_xf)
    sheet.write(0, 16, "Second Lowest Seller Name", heading_xf)
    sheet.write(0, 17, "Second Lowest Seller SP", heading_xf)
    sheet.write(0, 18, "Third Lowest Seller Name", heading_xf)
    sheet.write(0, 19, "Third Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "WANLC", heading_xf)
    sheet.write(0, 21, "Commission", heading_xf)
    sheet.write(0, 22, "Competitor Commission", heading_xf)
    sheet.write(0, 23, "Return Provision", heading_xf)
    sheet.write(0, 24, "Margin", heading_xf)
    sheet.write(0, 25, "Risky", heading_xf)
    sheet.write(0, 26, "Proposed Sp", heading_xf)
    sheet.write(0, 27, "Proposed Tp", heading_xf)
    sheet.write(0, 28, "Margin Increased Potential", heading_xf)
    sheet.write(0, 29, "Avg Sale", heading_xf)
    sheet.write(0, 30, "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.ourTp)
        sheet.write(sheet_iterator, 10, amScraping.lowestPossibleSp)
        sheet.write(sheet_iterator, 11, amScraping.lowestPossibleTp)
        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.lowestSellerName)
        sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 16, amScraping.secondLowestSellerName)
        sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 18, amScraping.thirdLowestSellerName)
        sheet.write(sheet_iterator, 19, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 20, amScraping.wanlc)
        sheet.write(sheet_iterator, 21, amScraping.commission)
        sheet.write(sheet_iterator, 22, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 23, amScraping.returnProvision)
        sheet.write(sheet_iterator, 24, round(amScraping.ourTp - amScraping.lowestPossibleTp))
        sheet.write(sheet_iterator, 25, item.risky)
        sheet.write(sheet_iterator, 26, amScraping.proposedSp)
        sheet.write(sheet_iterator, 27, amScraping.proposedTp)
        sheet.write(sheet_iterator, 28, amScraping.marginIncreasedPotential)
        sheet.write(sheet_iterator, 29, amScraping.avgSale)
        sheet.write(sheet_iterator, 30, 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, "Our Tp", heading_xf)
    sheet.write(0, 10, "Lowest Possible SP", heading_xf)
    sheet.write(0, 11, "Lowest Possible TP", heading_xf)
    sheet.write(0, 12, "Rank", heading_xf)
    sheet.write(0, 13, "Our Inventory", heading_xf)
    sheet.write(0, 14, "Lowest Seller Name", heading_xf)
    sheet.write(0, 15, "Lowest Seller SP", heading_xf)
    sheet.write(0, 16, "Second Lowest Seller Name", heading_xf)
    sheet.write(0, 17, "Second Lowest Seller SP", heading_xf)
    sheet.write(0, 18, "Third Lowest Seller Name", heading_xf)
    sheet.write(0, 19, "Third Lowest Seller SP", heading_xf)
    sheet.write(0, 20, "WANLC", heading_xf)
    sheet.write(0, 21, "Commission", heading_xf)
    sheet.write(0, 22, "Competitor Commission", heading_xf)
    sheet.write(0, 23, "Return Provision", heading_xf)
    sheet.write(0, 24, "Margin", heading_xf)
    sheet.write(0, 25, "Avg Sale", heading_xf)
    sheet.write(0, 26, "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.ourTp)
        sheet.write(sheet_iterator, 10, amScraping.lowestPossibleSp)
        sheet.write(sheet_iterator, 11, amScraping.lowestPossibleTp)
        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.lowestSellerName)
        sheet.write(sheet_iterator, 15, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 16, amScraping.secondLowestSellerName)
        sheet.write(sheet_iterator, 17, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 18, amScraping.thirdLowestSellerName)
        sheet.write(sheet_iterator, 19, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 20, amScraping.wanlc)
        sheet.write(sheet_iterator, 21, amScraping.commission)
        sheet.write(sheet_iterator, 22, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 23, amScraping.returnProvision)
        sheet.write(sheet_iterator, 24, round(amScraping.ourTp - amScraping.lowestPossibleTp))
        sheet.write(sheet_iterator, 25, amScraping.avgSale)
        sheet.write(sheet_iterator, 26, 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()
    syncAsin()
    fetchFbaSale()
    itemInfo = populateStuff(timestamp,options.runType)
    itemsToPopulate = 0
    print len(itemInfo)
    while (len(itemInfo)>0):
        if len(itemInfo) > 50:
            itemsToPopulate = 50
        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()