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)