Subversion Repositories SmartDukaan

from elixir import *

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

from shop2020.config.client.ConfigClient import ConfigClient

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

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

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

ItemVatMaster, CategoryVatMaster

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

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

Decision, RunType, AmazonPromotionType

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

from shop2020.clients.InventoryClient import InventoryClient

from shop2020.clients.TransactionClient import TransactionClient

import time

from time import sleep

from datetime import date, datetime, timedelta

import math

import simplejson as json

import xlwt

import optparse

import sys

from operator import itemgetter

from shop2020.utils import EmailAttachmentSender

from shop2020.utils.EmailAttachmentSender import get_attachment_part

import smtplib

from multiprocessing import Process 

from email.mime.text import MIMEText

import email

from email.mime.multipart import MIMEMultipart

import email.encoders

config_client = ConfigClient()

host = config_client.get_property('staging_hostname')

syncPrice=config_client.get_property('sync_price_on_marketplace')

amazonAsinPrice={}

amazonLongTermActivePromotions = {}

amazonShortTermActivePromotions = {}

wpiTodayExpiry = {}

notListed = []

saleMap = {}

categoryMap = {}

exceptionMap = {1:'WANLC is 0',2:'Unable to fetch our price',3:'No other seller or Unable to fetch competitive pricing',

                4:'Promo misconfigured',5:'Unable to calculate competitive pricing',6:'Vat not available'}

DataService.initialize(db_hostname=host)

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

class __AmazonItemInfo:

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

        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

        self.otherCost = otherCost

        self.exceptionType = exceptionType

        self.isNlcOverridden = isNlcOverridden

class __AmazonDetails:

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

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

                 thirdLowestSellerRating, lowestSellerType, secondLowestSellerType, thirdLowestSellerType, lowestMfnIgnoredOffer, lowestMfnOffer, lowestFbaOffer, \

                 isLowestMfnIgnored, isLowestMfn, isLowestFba, competitivePrice):

        self.sku =sku

        self.ourSp = ourSp

        self.ourRank = ourRank

        self.lowestSellerName = lowestSellerName

        self.lowestSellerSp = lowestSellerSp

        self.secondLowestSellerName = secondLowestSellerName

        self.secondLowestSellerSp = secondLowestSellerSp

        self.thirdLowestSellerName = thirdLowestSellerName

        self.thirdLowestSellerSp = thirdLowestSellerSp

        self.totalSeller = totalSeller

        self.multipleListings = multipleListings

        self.promoPrice = promoPrice

        self.isPromotion = isPromotion

        self.lowestSellerShippingTime =lowestSellerShippingTime

        self.lowestSellerRating = lowestSellerRating

        self.secondLowestSellerShippingTime = secondLowestSellerShippingTime

        self.secondLowestSellerRating = secondLowestSellerRating

        self.thirdLowestSellerShippingTime= thirdLowestSellerShippingTime

        self.thirdLowestSellerRating = thirdLowestSellerRating

        self.lowestSellerType = lowestSellerType

        self.secondLowestSellerType = secondLowestSellerType

        self.thirdLowestSellerType = thirdLowestSellerType

        self.lowestMfnIgnoredOffer = lowestMfnIgnoredOffer

        self.isLowestMfnIgnored = isLowestMfnIgnored 

        self.lowestMfnOffer = lowestMfnOffer

        self.isLowestMfn = isLowestMfn 

        self.lowestFbaOffer = lowestFbaOffer  

        self.isLowestFba = isLowestFba

        self.competitivePrice = competitivePrice 

class __AmazonPricing:

    def __init__(self, ourSp, lowestPossibleSp):

        self.ourSp = ourSp

        self.lowestPossibleSp = lowestPossibleSp

class __Promotion:

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

        self.promoPrice = promoPrice

        self.subsidy = subsidy

        self.promotionType = promotionType

        self.expiryDate = expiryDate 

def fetchItemsForAutoDecrease(time):

    successfulAutoDecrease = []

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

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

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

    print len(autoDecrementItems)

    for autoDecrementItem in autoDecrementItems:

        if autoDecrementItem.warehouseLocation == 1:

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

        else:

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

        if amazonShortTermActivePromotions.has_key(sku):

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

            continue

        if wpiTodayExpiry.has_key(sku) and not amazonLongTermActivePromotions.has_key(sku):

            markReasonForItem(autoDecrementItem,'Last Promotion ended in 24 hours and no current active promotion',Decision.AUTO_DECREMENT_FAILED)

            continue

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

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

            continue

        if autoDecrementItem.proposedSp < 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 < 5:

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

                continue

        if autoDecrementItem.competitiveCategory == CompetitionCategory.COMPETITIVE and not autoDecrementItem.isPromotion:

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

                if daysOfStock < 1 :

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

                    continue

            else:

                if daysOfStock < 3:

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

                    continue

        if autoDecrementItem.competitiveCategory == CompetitionCategory.COMPETITIVE and autoDecrementItem.isPromotion:

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

                if (amazonLongTermActivePromotions.get(sku).expiryDate - datetime.now()).days >2 and daysOfStock < 1 :

                    markReasonForItem(autoDecrementItem,'Promo Item, expiry after 2 days or not enough stock',Decision.AUTO_DECREMENT_FAILED)

                    continue

            else:

                if (amazonLongTermActivePromotions.get(sku).expiryDate - datetime.now()).days >2 and daysOfStock < 3:

                    markReasonForItem(autoDecrementItem,'Promo Item, expiry after 2 days or not enough stock',Decision.AUTO_DECREMENT_FAILED)

                    continue

        autoDecrementItem.ourEnoughStock=True

        autoDecrementItem.decision = Decision.AUTO_DECREMENT_SUCCESS

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

        successfulAutoDecrease.append(autoDecrementItem)

    session.commit()

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

    print len(autoIncrementItems)

    for autoIncrementItem in autoIncrementItems:

        if autoIncrementItem.warehouseLocation == 1:

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

        else:

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

        if amazonShortTermActivePromotions.has_key(sku):

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

            continue

        if wpiTodayExpiry.has_key(sku) and not amazonLongTermActivePromotions.has_key(sku):

            markReasonForItem(autoIncrementItem,'Last Promotion ended in 24 hours and no current active 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.promoPrice:

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

            continue

        if autoIncrementItem.proposedSp >=10000 and autoIncrementItem.promoPrice<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.isPromotion and (math.ceil(autoIncrementItem.promoPrice+max(10,.01*autoIncrementItem.promoPrice)) > (amazonLongTermActivePromotions.get(sku)).promoPrice):

            markReasonForItem(autoIncrementItem,'Proposed SP cant be greater than promo price',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

        if autoIncrementItem.isPromotion:

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

            print "antecedentPrice ",antecedentPrice

            try:

                if antecedentPrice[0] is not None:

                    if float(math.ceil(autoIncrementItem.promoPrice+max(10,.01*autoIncrementItem.promoPrice))-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

            except:

                if antecedentPrice is not None:

                    if float(math.ceil(autoIncrementItem.promoPrice+max(10,.01*autoIncrementItem.promoPrice))-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

        else:

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

            print "antecedentPrice else ",antecedentPrice

            if antecedentPrice is not None and antecedentPrice[0] 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

        if autoIncrementItem.warehouseLocation==1:

            fcLocation = 0

        elif autoIncrementItem.warehouseLocation==2:

            fcLocation = 1

        else:

            pass

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

        if getLastDaySale(fbaSaleSnapshot)<=2:

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

            continue

        autoIncrementItem.ourEnoughStock = False

        autoIncrementItem.decision = Decision.AUTO_INCREMENT_SUCCESS

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

        successfulAutoIncrease.append(autoIncrementItem)

    session.commit()

    return successfulAutoIncrease     

def markReasonForItem(amHistory,reason,decision):

    amHistory.decision = decision

    amHistory.reason = reason

def calculateAverageSale(sku):

    count,sale = 0,0

    oosStatus = saleMap.get(sku)

    if oosStatus is None:

        return 0.0

    for obj in oosStatus:

        if not obj.isOutOfStock:

            count+=1

            sale = sale+obj.totalOrderCount

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

    return round(avgSalePerDay,2)

def getOosString(oosStatus):

    if oosStatus is None:

        return ""

    lastNdaySale=""

    for obj in oosStatus:

        if obj.isOutOfStock:

            lastNdaySale += "X-"

        else:

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

    return lastNdaySale[:-1]

def getLastDaySale(fbaSaleSnapshot):

    if fbaSaleSnapshot.item_id==0:

        return 0

    else:

        return fbaSaleSnapshot.totalOrderCount

def getNoOfDaysInStock(oosStatus):

    inStockCount = 0

    if oosStatus is None:

        return 0

    for obj in oosStatus:

        if not obj.isOutOfStock:

            inStockCount+=1

    return inStockCount

def getCheapestMfnCount(timestamp,itemId):

    query = session.query(func.count(AmazonScrapingHistory.cheapestMfnCount)).filter(AmazonScrapingHistory.item_id==itemId).filter(AmazonScrapingHistory.timestamp>=timestamp-timedelta(days=5))

    cheapestCount = query.filter(AmazonScrapingHistory.cheapestMfnCount==True).scalar()

    total = query.scalar()

    if total==0:

        return 0

    return float(cheapestCount)/total

#def syncAsin():

##    notListedOnAmazon = []

##    diffAsins = []

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

##    br = SellerCentralInventoryReport.login(login_url)

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

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

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

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

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

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

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

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

##    ready = False

##    retryCount = 0

##    while not ready:

##        if retryCount == 10:

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

##            sys.exit(1)

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

##        if download_link is not None:

##            ready= True

##            continue

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

##        retryCount+=1

##        time.sleep(30)

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

#    fPath = "/tmp/9940651090.txt"

#    global amazonAsinPrice

#    for line in open(fPath):

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

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

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

#            amazonAsinPrice[l[0]] = obj

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

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

##    for systemAsin in systemAsins:

##        item = systemAsin[0]

##        amListed = systemAsin[1]

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

##            temp=[]

##            temp.append(item)

##            temp.append(amListed)

##            notListedOnAmazon.append(temp)

##            continue

##        else:

##            temp=[]

##            temp.append(item)

##            temp.append(amListed)

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

##                diffAsins.append(temp)

##                continue

##            

##    for diffAsin in diffAsins:

##        item = diffAsin[0]

##        amListed = diffAsin[1]

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

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

##    session.commit()

##    session.close()

def fetchFbaSale():

    global saleMap

    transaction_client = TransactionClient().get_client()

    fbaSaleSnapshot = transaction_client.getAmazonFbaSalesSnapshotForDays(4)

    for saleSnapshot in fbaSaleSnapshot:

        if saleSnapshot.fcLocation == 0:

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

                temp = []

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

                for l in val:

                    temp.append(l)

                temp.append(saleSnapshot)

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

            else:

                temp = []

                temp.append(saleSnapshot)

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

        else:

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

                temp = []

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

                for l in val:

                    temp.append(l)

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

                temp.append(saleSnapshot)

            else:

                temp = []

                temp.append(saleSnapshot)

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

def computeCourierCost(weight):

    try:

        cCost = 10.0;

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

        for slab in range(0,slabs):

            cCost = cCost + 10.0;

        return cCost;

    except:

        return 10.0

def populateStuff(time,runType):

    global amazonLongTermActivePromotions

    global amazonShortTermActivePromotions

    global wpiTodayExpiry

    itemInfo = []

    inventory_client = InventoryClient().get_client()

    fbaAvailableInventorySnapshot = inventory_client.getAllAvailableAmazonFbaItemInventory()

    if runType=='FAVOURITE':

        favourites = session.query(Amazonlisted.itemId).filter(or_(Amazonlisted.autoFavourite==True, Amazonlisted.manualFavourite==True)).all()

    for fbaInventoryItem in fbaAvailableInventorySnapshot:

        if runType=='FAVOURITE':

            if not (fbaInventoryItem.item_id in favourites):

                continue 

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

        if d_amazon_listed is None:

            if fbaInventoryItem.location==0:

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

                notListed.append(sku)

            elif fbaInventoryItem.location==1:

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

                notListed.append(sku)

            else:

                pass

            continue

        if d_amazon_listed.overrrideWanlc:

            isNlcOverridden = True

            wanlc = d_amazon_listed.exceptionalWanlc

            if wanlc is None:

                wanlc = 0.0

        else:

            isNlcOverridden = False

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

        sourcePercentage = None

        sip = SourceItemPercentage.query.filter(SourceItemPercentage.item_id==it.id).filter(SourceItemPercentage.source==OrderSource.AMAZON).filter(SourceItemPercentage.startDate<=time).filter(SourceItemPercentage.expiryDate>=time).first()

        if sip is not None:

            sourcePercentage = sip

        else:

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

            if scp is not None:

                sourcePercentage = scp

            else:

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

                sourcePercentage = spm

        if fbaInventoryItem.location==0:

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

            state_id = 1

        elif fbaInventoryItem.location==1:

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

            state_id = 2

        else:

            continue

        cc = computeCourierCost(it.weight)

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

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

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

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

    for amPromotion in amPromotions:

        if amazonLongTermActivePromotions.has_key(amPromotion.sku):

            continue

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

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

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

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

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

    for amPromotion in amPromotions:

        if amazonShortTermActivePromotions.has_key(amPromotion.sku):

            continue

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

    amPromotions = AmazonPromotion.query.filter(AmazonPromotion.endDate>=(time-timedelta(days=1))).filter(AmazonPromotion.endDate<=time).filter(AmazonPromotion.promotionType==AmazonPromotionType.LONGTERM).filter(AmazonPromotion.promotionActive==True) \

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

    for amPromotion in amPromotions: 

        if wpiTodayExpiry.has_key(amPromotion.sku):

            continue

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

    session.close()

    print "No of items populated ",len(itemInfo)

    return itemInfo

def getPriceAndAsin(itemInfo):

    skus = []

    for item in itemInfo:

        skus.append(item.sku)

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

    for item in itemInfo:

        ourPricing = ourPricingForSku.get(item.sku)

        if ourPricing is None or len(ourPricing.keys())==0:

            item.ourSp = 0

            item.promoPrice = 0

            item.isPromotion = False

            item.asin = ''

        else:

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

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

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

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

def decideCategory(itemInfo,timestamp):

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

    skus = []

    for item in itemInfo:

        skus.append(item.sku)

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

    aggResponse = pricingResponse[0]

    otherInfo = pricingResponse[1]

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

    for val in itemInfo:

        scrapInfo = aggResponse.get(val.sku)

        competitvePricingInfo = otherInfo.get(val.sku)

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

            temp = []

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

                print "WANLC is 0"

                val.exceptionType = 1

                #temp.append("WANLC is 0")

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

                print "Unable to fetch our price"

                val.exceptionType = 2

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

            else:

                print "No other seller or Unable to fetch competitive pricing"

                val.exceptionType = 3

                #temp.append("No other seller or Unable to fetch competitive pricing")

            temp.append(val)

            if val.exceptionType ==3:

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

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

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

                sku = val.sku

                try:

                    val.vatRate = getVatRate(val.sku[3:],val.state_id,val.promoPrice)

                except:

                    val.exceptionType = 6

                if (ourPricingForSku.get(val.sku).get('promotion')!=(amazonLongTermActivePromotions.has_key(val.sku) or amazonShortTermActivePromotions.has_key(val.sku))):

                    val.exceptionType = 4

                if val.exceptionType ==3:

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

                    amPricing = __AmazonPricing(val.ourSp,lowestPossibleSp)

                    amDetails = __AmazonDetails(sku, float(val.ourSp), 1, '',float(val.ourSp),'', float(0), '', float(0),1,False,val.promoPrice,val.isPromo, \

                    '0-0' ,0, '0-0', 0, '0-0' , 0, 'Amazon', '', '', \

                    0,0,0,False,False,True,None)

                    temp.append(amDetails)

                    temp.append(amPricing)

            exceptionList.append(temp)

            continue

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

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

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

        iterator = 0

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

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

        lowestSellerShippingTime, lowestSellerRating, secondLowestSellerShippingTime, secondLowestSellerRating, thirdLowestSellerShippingTime , \

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

        isPromo = False

        sku = val.sku

        multipleListings = False

        ourSkuDetails = ourPricingForSku.get(val.sku)

        if (ourSkuDetails.get('promotion')!=(amazonLongTermActivePromotions.has_key(val.sku) or amazonShortTermActivePromotions.has_key(val.sku))):

            temp = []

            val.exceptionType = 4

            temp.append(val)

            print "promo misconfigured"

            #temp.append("Promo misconfigured")

            exceptionList.append(temp)

            continue

        scrapInfo.append(ourSkuDetails)

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

        for info in sortedScrapInfo:

            if  not info['notOurSku']:

                ourSp = info['sellingPrice']

                promoPrice = info['promoPrice']

                isPromo = info['promotion']

                ourRank = iterator + 1

            if iterator == 0:

                lowestSellerSp = info['promoPrice']

                lowestSellerShippingTime = info['shippingTime']

                lowestSellerRating = info['rating']

                lowestSellerType = info['fulfillmentChannel']

            if iterator == 1:

                secondLowestSellerSp = info['promoPrice']

                secondLowestSellerShippingTime = info['shippingTime']

                secondLowestSellerRating = info['rating']

                secondLowestSellerType = info['fulfillmentChannel']

            if iterator == 2:

                thirdLowestSellerSp = info['promoPrice']

                thirdLowestSellerShippingTime = info['shippingTime']

                thirdLowestSellerRating = info['rating']

                thirdLowestSellerType = info['fulfillmentChannel']

            iterator += 1

        print "terminating iterator"

        print "Creating object am details",val.sku

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

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

                    competitvePricingInfo['lowestMfnIgnored'],competitvePricingInfo['lowestMfn'],competitvePricingInfo['lowestFba'],competitvePricingInfo['isLowestMfnIgnored'],competitvePricingInfo['isLowestMfn'],competitvePricingInfo['isLowestFba'],None)

        competitivePrice = decideCompetitvePricing(amDetails,val.ourInventory,timestamp)

        amDetails.competitivePrice = competitivePrice

        try:

            val.vatRate = getVatRate(val.sku[3:], val.state_id, amDetails.promoPrice)

        except:

            temp = []

            val.exceptionType = 6

            temp.append(val)

            exceptionList.append(temp)

            continue

        if amDetails.competitivePrice==0.0 and amDetails.ourRank > 1:

            temp = []

            val.exceptionType = 5

            temp.append(val)

            temp.append(amDetails)

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

            amPricing = __AmazonPricing(ourSp,lowestPossibleSp)

            temp.append(amPricing)

            exceptionList.append(temp)

            continue

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

        print "Creating pricing obj"

        amPricing = __AmazonPricing(ourSp,lowestPossibleSp)

        print "sku ",val.sku

        print "oursp ",ourSp

        print "promoPrice ",promoPrice

        print "lowestpossbile sp ",lowestPossibleSp

        print "objlowestPossiblesp ",amPricing.lowestPossibleSp

        if amDetails.promoPrice < amPricing.lowestPossibleSp:

            temp = []

            temp.append(val)

            temp.append(amDetails)

            temp.append(amPricing)

            negativeMargin.append(temp)

            print "val sku cat negative ",val.sku

            continue

        if amDetails.ourRank==1:

            temp = []

            temp.append(val)

            temp.append(amDetails)

            temp.append(amPricing)

            cheapest.append(temp)

            print "val sku cat cheapest ",val.sku

            continue

        if val.parent_category in [10006,10009,11001]:

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

                temp = []

                temp.append(val)

                temp.append(amDetails)

                temp.append(amPricing)

                amongCheapestAndCanCompete.append(temp)

                print "val sku cat amongCheapestAndCanCompete  ",val.sku

                continue

        else:

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

                temp = []

                temp.append(val)

                temp.append(amDetails)

                temp.append(amPricing)

                amongCheapestAndCanCompete.append(temp)

                print "val sku cat amongCheapestAndCanCompete  ",val.sku

                continue

        if (amDetails.competitivePrice > amPricing.lowestPossibleSp):

            temp = []

            temp.append(val)

            temp.append(amDetails)

            temp.append(amPricing)

            canCompete.append(temp)

            print "val sku cat can compete  ",val.sku

            continue

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

            temp = []

            temp.append(val)

            temp.append(amDetails)

            temp.append(amPricing)

            almostCompete.append(temp)

            print "val sku cat almost compete  ",val.sku

            continue

        temp = []

        temp.append(val)

        temp.append(amDetails)

        temp.append(amPricing)

        print "val sku cat cant compete  ",val.sku

        cantCompete.append(temp)

    print "Created category..."

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

def decideCompetitvePricing(amDetails,ourInventory,timestamp):

    '''

        lowestMfnIgnoredOffer, lowestMfnOffer, lowestFbaOffer, isLowestMfnIgnored, isLowestMfn, isLowestFba

    '''

    if amDetails.ourRank==1:

        return 0.0

    else:

        if amDetails.isLowestMfn and amDetails.isLowestFba:

            if amDetails.lowestMfnOffer >= amDetails.lowestFbaOffer:

                return amDetails.lowestFbaOffer

            else:

                #TODO Check last five days history.

                ratio = getCheapestMfnCount(timestamp,amDetails.sku[3:])

                daysInStock = getNoOfDaysInStock(saleMap.get(amDetails.sku))

                try:

                    daysOfStock = (float(ourInventory))/calculateAverageSale(amDetails.sku)

                except:

                    daysOfStock = float("inf")

                if daysInStock >= 4 and daysOfStock > 20 and ratio >=.8:

                    return amDetails.lowestMfnOffer

                else:

                    print "Unable to calculate competitive pricing for %s in block 1"%(amDetails.sku)

                    return amDetails.lowestFbaOffer

        elif amDetails.isLowestFba:

            return amDetails.lowestFbaOffer

        elif amDetails.isLowestMfn:

            #TODO Check last five days history

            ratio = getCheapestMfnCount(timestamp,amDetails.sku[3:])

            daysInStock = getNoOfDaysInStock(saleMap.get(amDetails.sku))

            try:

                daysOfStock = (float(ourInventory))/calculateAverageSale(amDetails.sku)

            except:

                daysOfStock = float("inf")

            if daysInStock >= 4 and daysOfStock > 20 and ratio >.8:

                return amDetails.lowestMfnOffer

            else:

                print "Unable to calculate competitive pricing for %s in block 2"%(amDetails.sku)

                return 0.0

        else:

            return 0.0

def getBreakevenPrice(item,val,spm):

    breakEvenPrice = (val.nlc+(val.courierCost)*(1+(spm.serviceTax/100))*(1+(val.vatRate/100))+(15.0+val.otherCost)*(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(breakEvenPrice,2)

def getLowestPossibleSp(amazonDetails,val,spm):

    if val.isPromo:

        if amazonLongTermActivePromotions.has_key(val.sku):

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

        else:

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

    else:

        subsidy = 0.0

    lowestPossibleSp = (val.nlc-subsidy+(val.courierCost)*(1+(spm.serviceTax/100))*(1+(val.vatRate/100))+(15.0+val.otherCost)*(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));

    #print (val.nlc-subsidy+(val.courierCost)*(1+(spm.serviceTax/100))*(1+(val.vatRate/100))+(15+val.otherCost)*(1+(val.vatRate)/100))

    #print (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 getNewLowestPossibleSp(item,serviceTax,newVatRate):

    lowestPossibleSp = (item.wanlc+(item.courierCost)*(1+(serviceTax/100))*(1+(newVatRate/100))+(15+item.otherCost)*(1+(newVatRate)/100))/(1-(item.commission/100)*(1+(serviceTax/100))*(1+(newVatRate)/100)-(item.returnProvision/100)*(1+(newVatRate)/100));

    if item.isPromotion:

        sku = ''

        if item.warehouseLocation==1:

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

        else:

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

        if amazonLongTermActivePromotions.has_key(sku):

            subsidy = (amazonLongTermActivePromotions.get(sku)).subsidy

        else:

            subsidy = (amazonShortTermActivePromotions.get(sku)).subsidy

        print "subsidy ",subsidy

        lowestPossibleSp = (item.wanlc-subsidy+(item.courierCost)*(1+(serviceTax/100))*(1+(newVatRate/100))+(15+item.otherCost)*(1+(newVatRate)/100))/(1-(item.commission/100)*(1+(serviceTax/100))*(1+(newVatRate)/100)-(item.returnProvision/100)*(1+(newVatRate)/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+(spm.serviceTax/100))

    return round(targetTp,2)

def commitExceptionList(exceptionList,timestamp,runType):

    for exceptionItem in exceptionList:

        val = exceptionItem[0]

        amazonScrapingHistory = AmazonScrapingHistory()

        amazonScrapingHistory.item_id = val.sku[3:]

        amazonScrapingHistory.asin = val.asin

        amazonScrapingHistory.warehouseLocation = val.state_id

        amazonScrapingHistory.parentCategoryId = val.parent_category

        amazonScrapingHistory.ourSellingPrice = val.ourSp

        amazonScrapingHistory.promoPrice = val.promoPrice

        amazonScrapingHistory.reason = exceptionMap.get(val.exceptionType)

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

        amazonScrapingHistory.competitiveCategory = CompetitionCategory.EXCEPTION

        amazonScrapingHistory.exceptionType = val.exceptionType

        amazonScrapingHistory.ourInventory = val.ourInventory

        amazonScrapingHistory.isNlcOverridden = val.isNlcOverridden

        if val.exceptionType in (3,5):

            amDetails = exceptionItem[1]

            amPricing = exceptionItem[2]

            if amazonLongTermActivePromotions.has_key(val.sku):

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

            elif amazonShortTermActivePromotions.has_key(val.sku):

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

            else:

                subsidy = 0

            spm = val.sourcePercentage

            amazonScrapingHistory.subsidy = subsidy

            amazonScrapingHistory.vatRate = val.vatRate

            amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp

            amazonScrapingHistory.ourRank = amDetails.ourRank

            amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp

            amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime

            amazonScrapingHistory.lowestSellerRating = amDetails.lowestSellerRating

            amazonScrapingHistory.lowestSellerType = amDetails.lowestSellerType

            amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp

            amazonScrapingHistory.secondLowestSellerShippingTime = amDetails.secondLowestSellerShippingTime

            amazonScrapingHistory.secondLowestSellerRating = amDetails.secondLowestSellerRating

            amazonScrapingHistory.secondLowestSellerType = amDetails.secondLowestSellerType

            amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp

            amazonScrapingHistory.thirdLowestSellerShippingTime = amDetails.thirdLowestSellerShippingTime

            amazonScrapingHistory.thirdLowestSellerRating = amDetails.thirdLowestSellerRating

            amazonScrapingHistory.thirdLowestSellerType = amDetails.thirdLowestSellerType

            if (amDetails.lowestMfnOffer < amDetails.lowestFbaOffer or amDetails.lowestMfnOffer < amazonScrapingHistory.promoPrice) and amDetails.isLowestMfn:

                amazonScrapingHistory.cheapestMfnCount = True

            else:

                amazonScrapingHistory.cheapestMfnCount = False

            amazonScrapingHistory.wanlc = val.nlc

            amazonScrapingHistory.otherCost = val.otherCost

            amazonScrapingHistory.commission = spm.commission

            amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther

            amazonScrapingHistory.returnProvision = spm.returnProvision

            amazonScrapingHistory.isLowestMfnIgnored = amDetails.isLowestMfnIgnored

            amazonScrapingHistory.isLowestMfn = amDetails.isLowestMfn

            amazonScrapingHistory.isLowestFba = amDetails.isLowestFba

            amazonScrapingHistory.lowestMfnIgnoredOffer =amDetails.lowestMfnIgnoredOffer

            amazonScrapingHistory.lowestMfnOffer = amDetails.lowestMfnOffer

            amazonScrapingHistory.lowestFbaOffer = amDetails.lowestFbaOffer

            amazonScrapingHistory.courierCost = val.courierCost

            amazonScrapingHistory.risky = val.risky

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

            amazonScrapingHistory.totalSeller = amDetails.totalSeller

            amazonScrapingHistory.timestamp = timestamp

            amazonScrapingHistory.multipleListings = amDetails.multipleListings

            amazonScrapingHistory.isPromotion = val.isPromo

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

        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

        if amazonLongTermActivePromotions.has_key(val.sku):

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

        elif amazonShortTermActivePromotions.has_key(val.sku):

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

        else:

            subsidy = 0

        amazonScrapingHistory = AmazonScrapingHistory()

        amazonScrapingHistory.item_id = val.sku[3:]

        amazonScrapingHistory.asin = val.asin

        amazonScrapingHistory.warehouseLocation = val.state_id

        amazonScrapingHistory.parentCategoryId = val.parent_category

        amazonScrapingHistory.ourSellingPrice = amDetails.ourSp

        amazonScrapingHistory.promoPrice = amDetails.promoPrice

        amazonScrapingHistory.subsidy = subsidy

        amazonScrapingHistory.vatRate = val.vatRate

        amazonScrapingHistory.lowestPossibleSp = amPricing.lowestPossibleSp

        amazonScrapingHistory.ourRank = amDetails.ourRank

        amazonScrapingHistory.ourInventory = val.ourInventory

        amazonScrapingHistory.lowestSellerSp = amDetails.lowestSellerSp

        amazonScrapingHistory.lowestSellerShippingTime = amDetails.lowestSellerShippingTime

        amazonScrapingHistory.lowestSellerRating = amDetails.lowestSellerRating

        amazonScrapingHistory.lowestSellerType = amDetails.lowestSellerType

        amazonScrapingHistory.secondLowestSellerSp = amDetails.secondLowestSellerSp

        amazonScrapingHistory.secondLowestSellerShippingTime = amDetails.secondLowestSellerShippingTime

        amazonScrapingHistory.secondLowestSellerRating = amDetails.secondLowestSellerRating

        amazonScrapingHistory.secondLowestSellerType = amDetails.secondLowestSellerType

        amazonScrapingHistory.thirdLowestSellerSp = amDetails.thirdLowestSellerSp

        amazonScrapingHistory.thirdLowestSellerShippingTime = amDetails.thirdLowestSellerShippingTime

        amazonScrapingHistory.thirdLowestSellerRating = amDetails.thirdLowestSellerRating

        amazonScrapingHistory.thirdLowestSellerType = amDetails.thirdLowestSellerType

        if (amDetails.lowestMfnOffer < amDetails.lowestFbaOffer or amDetails.lowestMfnOffer < amazonScrapingHistory.promoPrice) and amDetails.isLowestMfn:

            amazonScrapingHistory.cheapestMfnCount = True

        else:

            amazonScrapingHistory.cheapestMfnCount = False

        amazonScrapingHistory.wanlc = val.nlc

        amazonScrapingHistory.isNlcOverridden = val.isNlcOverridden

        amazonScrapingHistory.otherCost = val.otherCost

        amazonScrapingHistory.commission = spm.commission

        amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther

        amazonScrapingHistory.returnProvision = spm.returnProvision

        amazonScrapingHistory.courierCost = val.courierCost

        amazonScrapingHistory.risky = val.risky

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

        amazonScrapingHistory.totalSeller = amDetails.totalSeller

        amazonScrapingHistory.competitiveCategory = CompetitionCategory.NEGATIVE_MARGIN

        amazonScrapingHistory.timestamp = timestamp

        amazonScrapingHistory.multipleListings = amDetails.multipleListings

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

        amazonScrapingHistory.isPromotion = val.isPromo

    session.commit()

def commitCheapest(cheapest,timestamp,runType):

    for cheapestItem in cheapest:

        val = cheapestItem[0]

        amDetails = cheapestItem[1]

        amPricing = cheapestItem[2]

        spm = val.sourcePercentage

        if amazonLongTermActivePromotions.has_key(val.sku):

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

        elif amazonShortTermActivePromotions.has_key(val.sku):

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

        else:

            subsidy = 0

        amazonScrapingHistory = AmazonScrapingHistory()

        amazonScrapingHistory.item_id = val.sku[3:]

        amazonScrapingHistory.asin = val.asin

        amazonScrapingHistory.warehouseLocation = val.state_id

        amazonScrapingHistory.parentCategoryId = val.parent_category

        amazonScrapingHistory.ourSellingPrice = amDetails.ourSp

        amazonScrapingHistory.promoPrice = amDetails.promoPrice

        amazonScrapingHistory.subsidy = subsidy