Subversion Repositories SmartDukaan

from elixir import *

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

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

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 = []

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 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/9921129430.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

#    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.is_oos:

            count+=1

            sale = sale+obj.num_orders

    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

    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)

    session.close()

    print "item info length"

    print len(itemInfo)

    return itemInfo

def decideCategory(itemInfo):

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

    skuUrls = []

    kshitij = []

    #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')

        kshitij.append(item.asin)

    aggResponse = amScraper.read(skuUrls, True)

    notfetch = list(set(kshitij)-set(aggResponse.keys()))

    print "kshitij"

    print len(notfetch)

    print notfetch 

    time.sleep(2)

#    for asin, scrapInfo in aggResponse:

#        skuList = skuAsinMap.get(asin)

#        for sku in skuList:

#            amDetails = __AmazonDetails(None, None, None, None, None,secondLowestSellerName, secondLowestSellerSp, thirdLowestSellerName, thirdLowestSellerSp)

#            for info in scrapInfo:

    catalog_client = CatalogClient().get_client()

    for val in itemInfo:

        if val.asin is None or len(val.asin)==0:

            continue

        scrapInfo = aggResponse.get(val.asin)

        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:

            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

        #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

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

        try:

            val.vatRate = catalog_client.getVatPercentageForItem(int(val.sku[3:]), val.state_id, amDetails.ourSp)

        except:

            temp = []

            temp.append(val)

            temp.append("Vat not available")

            exceptionList.append(temp)

            continue

        ourTp = getOurTp(amDetails,val,val.sourcePercentage)

        lowestPossibleTp = getLowestPossibleTp(amDetails,val,val.sourcePercentage)

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

        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

        temp = []

        temp.append(val)

        temp.append(amDetails)

        temp.append(amPricing)

        cantCompete.append(temp)

    itemInfo[:]=[]

    scrapInfo[:]=[]

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

    return

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

            itemsToPopulate = 20

        else:

            itemsToPopulate = len(itemInfo)

        print itemsToPopulate

        exceptionList, negativeMargin, cheapest, amongCheapestAndCanCompete, canCompete, almostCompete, cantCompete = decideCategory(itemInfo[0:itemsToPopulate])

        itemInfo[0:itemsToPopulate] = []

        commitExceptionList(exceptionList,timestamp,options.runType)

        commitNegativeMargin(negativeMargin,timestamp,options.runType)

        commitCheapest(cheapest,timestamp,options.runType)

        commitAmongCheapestAndCanCompete(amongCheapestAndCanCompete,timestamp,options.runType)

        commitCanCompete(canCompete,timestamp,options.runType)

        commitAlmostCompete(almostCompete,timestamp,options.runType)

        commitCantCompete(cantCompete, timestamp,options.runType)

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

if __name__=='__main__':

    main()