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, AmazonFCWarehouseLocation
from shop2020.thriftpy.model.v1.catalog.ttypes import CompetitionCategory, \
Decision, RunType, AmazonPromotionType
from shop2020.model.v1.catalog.script import AmazonAsyncScraper
from shop2020.model.v1.order.script import MongoService
from shop2020.clients.InventoryClient import InventoryClient
from shop2020.utils.Utils import to_py_date
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 = []
toList = []
saleMap = {}
monthlySaleMap = {}
categoryMap = {}
latestHourlySnapshot = {}
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, \
                 packagingLength, packagingWidth, packagingHeight, packagingWeight, isOversized):
        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
        self.packagingLength = packagingLength
        self.packagingWidth = packagingWidth
        self.packagingHeight = packagingHeight
        self.packagingWeight = packagingWeight
        self.isOversized = isOversized
        

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()
    for autoDecrementItem in autoDecrementItems:
        if autoDecrementItem.warehouseLocation == 1:
            sku = 'FBA'+str(autoDecrementItem.item_id)
        elif autoDecrementItem.warehouseLocation == 2:
            sku = 'FBB'+str(autoDecrementItem.item_id)
        else:
            sku = 'FBG'+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 [10001,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 [10001,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()
    print len(autoIncrementItems)
    for autoIncrementItem in autoIncrementItems:
        if autoIncrementItem.warehouseLocation == 1:
            sku = 'FBA'+str(autoIncrementItem.item_id)
        elif autoIncrementItem.warehouseLocation == 2:
            sku = 'FBB'+str(autoIncrementItem.item_id)
        else:
            sku = 'FBG'+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 (min(math.ceil(autoIncrementItem.promoPrice+max(10,.01*autoIncrementItem.promoPrice)),autoIncrementItem.proposedSp) > (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
        elif autoIncrementItem.warehouseLocation==3:
            fcLocation = 2
        else:
            pass
        fbaSaleSnapshot = MongoService.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 get30DaysStats(sku):
    count,sale = 0,0
    oosStatus = monthlySaleMap.get(sku)
    if oosStatus is None:
        return 0,0
    for obj in oosStatus:
        if not obj.isOutOfStock:
            count+=1
            sale = sale+obj.totalOrderCount
    return sale, count

def getInStockStats(item_id, warehouseLocation):
    if warehouseLocation==1:
        fcLocation = 0
    elif warehouseLocation==2:
        fcLocation = 1
    elif warehouseLocation==3:
        fcLocation = 2
    return MongoService.getLastOutOfStock(item_id, fcLocation)
    

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 fetchMonthSale():
    global monthlymonthlySaleMap
    fbaSaleSnapshot = MongoService.getAmazonFbaSalesSnapshotForDays(29)
    for saleSnapshot in fbaSaleSnapshot:
        if saleSnapshot.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(0):
            if monthlySaleMap.has_key('FBA'+str(saleSnapshot.item_id)):
                temp = []
                val = monthlySaleMap.get('FBA'+str(saleSnapshot.item_id))
                for l in val:
                    temp.append(l)
                temp.append(saleSnapshot)
                monthlySaleMap['FBA'+str(saleSnapshot.item_id)]=temp
            else:
                temp = []
                temp.append(saleSnapshot)
                monthlySaleMap['FBA'+str(saleSnapshot.item_id)] = temp
        elif saleSnapshot.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(1):
            if monthlySaleMap.has_key('FBB'+str(saleSnapshot.item_id)):
                temp = []
                val = monthlySaleMap.get('FBB'+str(saleSnapshot.item_id))
                for l in val:
                    temp.append(l)
                temp.append(saleSnapshot)
                monthlySaleMap['FBB'+str(saleSnapshot.item_id)]=temp
            else:
                temp = []
                temp.append(saleSnapshot)
                monthlySaleMap['FBB'+str(saleSnapshot.item_id)] = temp
        elif saleSnapshot.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(2):
            if monthlySaleMap.has_key('FBG'+str(saleSnapshot.item_id)):
                temp = []
                val = monthlySaleMap.get('FBG'+str(saleSnapshot.item_id))
                for l in val:
                    temp.append(l)
                temp.append(saleSnapshot)
                monthlySaleMap['FBG'+str(saleSnapshot.item_id)]=temp
            else:
                temp = []
                temp.append(saleSnapshot)
                monthlySaleMap['FBG'+str(saleSnapshot.item_id)] = temp
        else:
            continue
def populateHourlySnapshot():
    global latestHourlySnapshot
    fbaHourlySnapshot = MongoService.getLatestHourlySnapshot()
    for sale in fbaHourlySnapshot:
        if sale.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(0):
            latestHourlySnapshot['FBA'+str(sale.item_id)] = sale
        elif sale.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(1):
            latestHourlySnapshot['FBB'+str(sale.item_id)] = sale
        elif sale.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(2):
            latestHourlySnapshot['FBG'+str(sale.item_id)] = sale
        else:
            continue
        
def getTodaysSale(sku):
    sale = latestHourlySnapshot.get(sku)
    if sale is None:
        return '', 0
    return (to_py_date(sale.snapshotTime)).strftime("%Y-%m-%d %H:%M"), sale.totalOrderCount 

def fetchFbaSale():
    global saleMap
    fbaSaleSnapshot = MongoService.getAmazonFbaSalesSnapshotForDays(4)
    for saleSnapshot in fbaSaleSnapshot:
        if saleSnapshot.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(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
        elif saleSnapshot.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(1):
            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)
                temp.append(saleSnapshot)
                saleMap['FBB'+str(saleSnapshot.item_id)]=temp
            else:
                temp = []
                temp.append(saleSnapshot)
                saleMap['FBB'+str(saleSnapshot.item_id)] = temp
        elif saleSnapshot.fcLocation == AmazonFCWarehouseLocation._VALUES_TO_NAMES.get(2):
            if saleMap.has_key('FBG'+str(saleSnapshot.item_id)):
                temp = []
                val = saleMap.get('FBG'+str(saleSnapshot.item_id))
                for l in val:
                    temp.append(l)
                temp.append(saleSnapshot)
                saleMap['FBG'+str(saleSnapshot.item_id)]=temp
            else:
                temp = []
                temp.append(saleSnapshot)
                saleMap['FBG'+str(saleSnapshot.item_id)] = temp
        else:
            continue


def computeCourierCost(weight):
    try:
        cCost = 20.0;
        slabs = int((weight*1000)/500-.001)
        for slab in range(0,slabs):
            cCost = cCost + 35.0;
        return cCost;
    except:
        return 20.0

        
def populateStuff(time,runType):
    global amazonLongTermActivePromotions
    global amazonShortTermActivePromotions
    global wpiTodayExpiry
    global toList
    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)
            elif fbaInventoryItem.location==2:
                sku = 'FBG'+str(fbaInventoryItem.item_id)
                notListed.append(sku)
            else:
                pass
            continue
        if fbaInventoryItem.location==0:
            if not d_amazon_listed.isFba:
                sku = 'FBA'+str(fbaInventoryItem.item_id)
                toList.append(sku)
        elif fbaInventoryItem.location==1:
            if not d_amazon_listed.isFbb:
                sku = 'FBB'+str(fbaInventoryItem.item_id)
                toList.append(sku)
        elif fbaInventoryItem.location==2:
            if not d_amazon_listed.isFbg:
                sku = 'FBG'+str(fbaInventoryItem.item_id)
                toList.append(sku)
        else:
            pass
        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
        elif fbaInventoryItem.location==2:
            sku = 'FBG'+str(fbaInventoryItem.item_id)
            state_id = 3
        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, \
                                          d_amazon_listed.packagingLength, d_amazon_listed.packagingWidth, d_amazon_listed.packagingHeight, d_amazon_listed.packagingWeight, checkOverSize(d_amazon_listed))
        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 [10001,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+spm.closingFee)*(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+spm.closingFee)*(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)
        elif item.warehouseLocation==2:
            sku='FBB'+str(item.item_id)
        else:
            sku='FBG'+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
        amazonScrapingHistory.packagingHeight = val.packagingHeight
        amazonScrapingHistory.packagingLength = val.packagingLength
        amazonScrapingHistory.packagingWidth = val.packagingWidth
        amazonScrapingHistory.packagingWeight = val.packagingWeight
        amazonScrapingHistory.isOversized = val.isOversized 
    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
        amazonScrapingHistory.packagingHeight = val.packagingHeight
        amazonScrapingHistory.packagingLength = val.packagingLength
        amazonScrapingHistory.packagingWidth = val.packagingWidth
        amazonScrapingHistory.packagingWeight = val.packagingWeight
        amazonScrapingHistory.isOversized = val.isOversized 
    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
        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
        amazonScrapingHistory.cheapestMfnCount = False
        amazonScrapingHistory.otherCost = val.otherCost
        amazonScrapingHistory.wanlc = val.nlc
        amazonScrapingHistory.isNlcOverridden = val.isNlcOverridden
        amazonScrapingHistory.commission = spm.commission
        amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther
        amazonScrapingHistory.returnProvision = spm.returnProvision
        amazonScrapingHistory.courierCost = val.courierCost
        amazonScrapingHistory.risky = val.risky
        amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)
        amazonScrapingHistory.totalSeller = amDetails.totalSeller
        amazonScrapingHistory.competitiveCategory = CompetitionCategory.BUY_BOX
        amazonScrapingHistory.timestamp = timestamp
        amazonScrapingHistory.multipleListings = amDetails.multipleListings
        proposed_sp = max(amDetails.secondLowestSellerSp - 1, amPricing.lowestPossibleSp)
        if amazonScrapingHistory.isPromotion:
            if amazonLongTermActivePromotions.has_key(val.sku):
                proposed_sp = min(proposed_sp,(amazonLongTermActivePromotions.get(val.sku)).salePrice)
            else:
                proposed_sp = min(proposed_sp,(amazonShortTermActivePromotions.get(val.sku)).salePrice)
        #proposed_tp = getTargetTp(proposed_sp,spm,val)
        amazonScrapingHistory.proposedSp = proposed_sp
        #amazonScrapingHistory.proposedTp = proposed_tp
        #amazonScrapingHistory.marginIncreasedPotential = proposed_tp - amPricing.ourTp
        amazonScrapingHistory.multipleListings = amDetails.multipleListings
        amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
        amazonScrapingHistory.isPromotion = val.isPromo
        amazonScrapingHistory.packagingHeight = val.packagingHeight
        amazonScrapingHistory.packagingLength = val.packagingLength
        amazonScrapingHistory.packagingWidth = val.packagingWidth
        amazonScrapingHistory.packagingWeight = val.packagingWeight
        amazonScrapingHistory.isOversized = val.isOversized 
    session.commit()



def commitAmongCheapestAndCanCompete(amongCheapestAndCanCompete,timestamp,runType):
    for amongCheapestAndCanCompeteItem in amongCheapestAndCanCompete:
        val = amongCheapestAndCanCompeteItem[0]
        amDetails = amongCheapestAndCanCompeteItem[1]
        amPricing = amongCheapestAndCanCompeteItem[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
        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.competitivePrice = amDetails.competitivePrice
        if (amDetails.lowestMfnOffer < amDetails.lowestFbaOffer or amDetails.lowestMfnOffer < amazonScrapingHistory.promoPrice) and amDetails.isLowestMfn:
            amazonScrapingHistory.cheapestMfnCount = True
        else:
            amazonScrapingHistory.cheapestMfnCount = False
        amazonScrapingHistory.otherCost = val.otherCost
        amazonScrapingHistory.wanlc = val.nlc
        amazonScrapingHistory.isNlcOverridden = val.isNlcOverridden
        amazonScrapingHistory.commission = spm.commission
        amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther
        amazonScrapingHistory.returnProvision = spm.returnProvision
        amazonScrapingHistory.courierCost = val.courierCost
        amazonScrapingHistory.risky = val.risky
        amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)
        amazonScrapingHistory.totalSeller = amDetails.totalSeller
        amazonScrapingHistory.competitiveCategory = CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE
        amazonScrapingHistory.timestamp = timestamp
        amazonScrapingHistory.multipleListings = amDetails.multipleListings
        proposed_sp = max(amDetails.competitivePrice - 1, amPricing.lowestPossibleSp)
        #proposed_tp = getTargetTp(proposed_sp,spm,val)
        amazonScrapingHistory.proposedSp = proposed_sp
        #amazonScrapingHistory.proposedTp = proposed_tp
        amazonScrapingHistory.multipleListings = amDetails.multipleListings
        amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
        amazonScrapingHistory.isPromotion = val.isPromo
        amazonScrapingHistory.packagingHeight = val.packagingHeight
        amazonScrapingHistory.packagingLength = val.packagingLength
        amazonScrapingHistory.packagingWidth = val.packagingWidth
        amazonScrapingHistory.packagingWeight = val.packagingWeight
        amazonScrapingHistory.isOversized = val.isOversized 
    session.commit()

def commitCanCompete(canCompete,timestamp,runType):
    for canCompeteItem in canCompete:
        val = canCompeteItem[0]
        amDetails = canCompeteItem[1]
        amPricing = canCompeteItem[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
        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.competitivePrice = amDetails.competitivePrice
        if (amDetails.lowestMfnOffer < amDetails.lowestFbaOffer or amDetails.lowestMfnOffer < amazonScrapingHistory.promoPrice) and amDetails.isLowestMfn:
            amazonScrapingHistory.cheapestMfnCount = True
        else:
            amazonScrapingHistory.cheapestMfnCount = False
        amazonScrapingHistory.otherCost = val.otherCost
        amazonScrapingHistory.wanlc = val.nlc
        amazonScrapingHistory.isNlcOverridden = val.isNlcOverridden
        amazonScrapingHistory.commission = spm.commission
        amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther
        amazonScrapingHistory.returnProvision = spm.returnProvision
        amazonScrapingHistory.courierCost = val.courierCost
        amazonScrapingHistory.risky = val.risky
        amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)
        amazonScrapingHistory.totalSeller = amDetails.totalSeller
        amazonScrapingHistory.competitiveCategory = CompetitionCategory.COMPETITIVE
        amazonScrapingHistory.timestamp = timestamp
        amazonScrapingHistory.multipleListings = amDetails.multipleListings
        proposed_sp = max(amDetails.competitivePrice - 1, amPricing.lowestPossibleSp)
        #proposed_tp = getTargetTp(proposed_sp,spm,val)
        amazonScrapingHistory.proposedSp = proposed_sp
        #amazonScrapingHistory.proposedTp = proposed_tp
        amazonScrapingHistory.multipleListings = amDetails.multipleListings
        amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
        amazonScrapingHistory.isPromotion = val.isPromo
        amazonScrapingHistory.packagingHeight = val.packagingHeight
        amazonScrapingHistory.packagingLength = val.packagingLength
        amazonScrapingHistory.packagingWidth = val.packagingWidth
        amazonScrapingHistory.packagingWeight = val.packagingWeight
        amazonScrapingHistory.isOversized = val.isOversized 
    session.commit()

def commitAlmostCompete(almostCompete,timestamp,runType):
    for almostCompeteItem in almostCompete:
        val = almostCompeteItem[0]
        amDetails = almostCompeteItem[1]
        amPricing = almostCompeteItem[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
        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.competitivePrice = amDetails.competitivePrice
        if (amDetails.lowestMfnOffer < amDetails.lowestFbaOffer or amDetails.lowestMfnOffer < amazonScrapingHistory.promoPrice) and amDetails.isLowestMfn:
            amazonScrapingHistory.cheapestMfnCount = True
        else:
            amazonScrapingHistory.cheapestMfnCount = False
        amazonScrapingHistory.otherCost = val.otherCost
        amazonScrapingHistory.wanlc = val.nlc
        amazonScrapingHistory.isNlcOverridden = val.isNlcOverridden
        amazonScrapingHistory.commission = spm.commission
        amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther
        amazonScrapingHistory.returnProvision = spm.returnProvision
        amazonScrapingHistory.courierCost = val.courierCost
        amazonScrapingHistory.risky = val.risky
        amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)
        amazonScrapingHistory.totalSeller = amDetails.totalSeller
        amazonScrapingHistory.competitiveCategory = CompetitionCategory.ALMOST_COMPETE
        amazonScrapingHistory.timestamp = timestamp
        amazonScrapingHistory.multipleListings = amDetails.multipleListings
        proposed_sp = min(amDetails.competitivePrice*(1+.01),amPricing.lowestPossibleSp)
        #proposed_tp = getTargetTp(proposed_sp,spm,val)
        #target_nlc = proposed_tp - amPricing.lowestPossibleTp + val.nlc
        amazonScrapingHistory.proposedSp = proposed_sp
        #amazonScrapingHistory.proposedTp = proposed_tp
        #amazonScrapingHistory.targetNlc = target_nlc
        amazonScrapingHistory.multipleListings = amDetails.multipleListings
        amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
        amazonScrapingHistory.isPromotion = val.isPromo
        amazonScrapingHistory.packagingHeight = val.packagingHeight
        amazonScrapingHistory.packagingLength = val.packagingLength
        amazonScrapingHistory.packagingWidth = val.packagingWidth
        amazonScrapingHistory.packagingWeight = val.packagingWeight
        amazonScrapingHistory.isOversized = val.isOversized 
    session.commit()


def commitCantCompete(cantCompete, timestamp,runType):
    for cantCompeteItem in cantCompete:
        val = cantCompeteItem[0]
        amDetails = cantCompeteItem[1]
        amPricing = cantCompeteItem[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
        amazonScrapingHistory.isLowestMfnIgnored = amDetails.isLowestMfnIgnored
        amazonScrapingHistory.isLowestMfn = amDetails.isLowestMfn
        amazonScrapingHistory.isLowestFba = amDetails.isLowestFba
        amazonScrapingHistory.lowestMfnIgnoredOffer =amDetails.lowestMfnIgnoredOffer
        amazonScrapingHistory.lowestMfnOffer = amDetails.lowestMfnOffer
        amazonScrapingHistory.lowestFbaOffer = amDetails.lowestFbaOffer
        if (amDetails.lowestMfnOffer < amDetails.lowestFbaOffer or amDetails.lowestMfnOffer < amazonScrapingHistory.promoPrice) and amDetails.isLowestMfn:
            amazonScrapingHistory.cheapestMfnCount = True
        else:
            amazonScrapingHistory.cheapestMfnCount = False
        amazonScrapingHistory.competitivePrice = amDetails.competitivePrice
        amazonScrapingHistory.otherCost = val.otherCost
        amazonScrapingHistory.wanlc = val.nlc
        amazonScrapingHistory.isNlcOverridden = val.isNlcOverridden
        amazonScrapingHistory.commission = spm.commission
        amazonScrapingHistory.competitorCommission = spm.competitorCommissionOther
        amazonScrapingHistory.returnProvision = spm.returnProvision
        amazonScrapingHistory.courierCost = val.courierCost
        amazonScrapingHistory.risky = val.risky
        amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)
        amazonScrapingHistory.totalSeller = amDetails.totalSeller
        amazonScrapingHistory.competitiveCategory = CompetitionCategory.CANT_COMPETE
        amazonScrapingHistory.timestamp = timestamp
        amazonScrapingHistory.multipleListings = amDetails.multipleListings
        proposed_sp = amDetails.competitivePrice - max(5, amDetails.competitivePrice*0.001)
        #proposed_tp = getTargetTp(proposed_sp,spm,val)
        #target_nlc = proposed_tp - amPricing.lowestPossibleTp + val.nlc
        amazonScrapingHistory.proposedSp = proposed_sp
        #amazonScrapingHistory.proposedTp = proposed_tp
        #amazonScrapingHistory.targetNlc = target_nlc
        amazonScrapingHistory.multipleListings = amDetails.multipleListings
        amazonScrapingHistory.avgSale = calculateAverageSale(val.sku) #Last five days
        amazonScrapingHistory.isPromotion = val.isPromo
        amazonScrapingHistory.packagingHeight = val.packagingHeight
        amazonScrapingHistory.packagingLength = val.packagingLength
        amazonScrapingHistory.packagingWidth = val.packagingWidth
        amazonScrapingHistory.packagingWeight = val.packagingWeight
        amazonScrapingHistory.isOversized = val.isOversized 
    session.commit()

def markAutoFavourites(time):
    nowAutoFav = []
    previouslyAutoFav = []
    stockList = []
    saleList = []
    items = session.query(func.sum(AmazonScrapingHistory.ourInventory),AmazonScrapingHistory.item_id).filter(AmazonScrapingHistory.timestamp==time).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','').replace('FBG','')
        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
        val =saleMap.get('FBG'+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+="Fulfillable Stock in FC is >=5"
        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

#Write the excel sheet headers for identical sheets
def writeheaders(sheet,heading_xf):
    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, "URL", heading_xf)
    sheet.write(0, 4, "Location", heading_xf)
    sheet.write(0, 5, "Brand", heading_xf)
    sheet.write(0, 6, "Category", heading_xf)
    sheet.write(0, 7, "Product Name", heading_xf)
    sheet.write(0, 8, "Weight", heading_xf)
    sheet.write(0, 9, "Courier Cost", heading_xf)
    sheet.write(0, 10, "MRP", heading_xf)
    sheet.write(0, 11, "Our SP", heading_xf)
    sheet.write(0, 12, "Promo Price", heading_xf)
    sheet.write(0, 13, "Is Promotion", heading_xf)
    sheet.write(0, 14, "Lowest Possible SP", heading_xf)
    sheet.write(0, 15, "Rank", heading_xf)
    sheet.write(0, 16, "Competitive Category", heading_xf)
    sheet.write(0, 17, "Our Inventory", heading_xf)
    sheet.write(0, 18, "Lowest Seller SP", heading_xf)
    sheet.write(0, 19, "Lowest Seller Rating", heading_xf)
    sheet.write(0, 20, "Lowest Seller Shipping Time", heading_xf)
    sheet.write(0, 21, "Second Lowest Seller SP", heading_xf)
    sheet.write(0, 22, "Second Lowest Seller Rating", heading_xf)
    sheet.write(0, 23, "Second Lowest Seller Shipping Time", heading_xf)
    sheet.write(0, 24, "Third Lowest Seller SP", heading_xf)
    sheet.write(0, 25, "Third Lowest Seller Rating", heading_xf)
    sheet.write(0, 26, "Third Lowest Seller Shipping Time", heading_xf)
    sheet.write(0, 27, "Lowest MFN Ignored", heading_xf)
    sheet.write(0, 28, "Lowest MFN", heading_xf)
    sheet.write(0, 29, "Lowest FBA", heading_xf)
    sheet.write(0, 30, "Competitive Price", heading_xf)
    sheet.write(0, 31, "Other Cost", heading_xf)
    sheet.write(0, 32, "WANLC", heading_xf)
    sheet.write(0, 33, "Overridden WANLC", heading_xf)
    sheet.write(0, 34, "Subsidy", heading_xf)
    sheet.write(0, 35, "MAX SALE PRICE", heading_xf)
    sheet.write(0, 36, "Commission", heading_xf)
    sheet.write(0, 37, "Competitor Commission", heading_xf)
    sheet.write(0, 38, "Return Provision", heading_xf)
    sheet.write(0, 39, "Vat Rate", heading_xf)
    sheet.write(0, 40, "Margin", heading_xf)
    sheet.write(0, 41, "Proposed Sp", heading_xf)
    sheet.write(0, 42, "Avg Sale", heading_xf)
    sheet.write(0, 43, "NOD", heading_xf)
    sheet.write(0, 44, "Sales History", heading_xf)
    sheet.write(0, 45, "Last 30 days sale", heading_xf)
    sheet.write(0, 46, "No of days InStock in last 30 days", heading_xf)
    sheet.write(0, 47, "Avg 30 Days Sale", heading_xf)
    sheet.write(0, 48, "Last OOS Date", heading_xf)
    sheet.write(0, 49, "Total Sale Since InStock (Excluding Today)", heading_xf)
    sheet.write(0, 50, "Total Days Since InStock (Excluding Today)", heading_xf)
    sheet.write(0, 51, "Avg Sale Since InStock (Excluding Today)", heading_xf)
    sheet.write(0, 52, "Todays Sale Snapshot Time", heading_xf)
    sheet.write(0, 53, "Todays Sale", heading_xf)
    sheet.write(0, 54, "Decision", heading_xf)
    sheet.write(0, 55, "Reason", heading_xf)
    sheet.write(0, 56, "Updated Price", heading_xf)
    sheet.write(0, 57, "Proposed Margin", heading_xf)
    sheet.write(0, 58, "Inventory Movement Status", heading_xf)
    sheet.write(0, 59, "Packaging Length (Inch)", heading_xf)
    sheet.write(0, 60, "Packaging Width (Inch)", heading_xf)
    sheet.write(0, 61, "Packaging Height (Inch)", heading_xf)
    sheet.write(0, 62, "Packaging Weight (Kg)", heading_xf)
    sheet.write(0, 63, "Oversized Item", heading_xf)
    

def getPackagingCost(data):
    #TODO : Get packagingCost from marketplaceitems table
    return 15

def getReturnCost(data):
    return round(data.returnProvision * data.promoPrice/100)

def getNewReturnCost(data,proposedSp):
    return round(data.returnProvision * proposedSp/100)

def getServiceTax(data):
    #TODO : Get service tax from marketplaceitems table
    return 14.00

def getClosingFee(data):
    myClosingFee = 10
    return myClosingFee*(1+getServiceTax(data)/100)

def getCommission(data):
    return (data.commission * data.promoPrice/100)*(1+getServiceTax(data)/100)

def getNewCommission(data,proposedSp):
    return (data.commission * proposedSp/100)*(1+getServiceTax(data)/100)

def getCourierCost(data):
    return data.courierCost*(1+getServiceTax(data)/100)

def getCostToAmazon(data):
    myCostToAmazon = round(data.promoPrice*data.commission/100*(1+getServiceTax(data)/100)+getCourierCost(data)*(1+getServiceTax(data)/100))
    return myCostToAmazon

def getMsp(item_id,location):
    if location==1:
        sku='FBA'+str(item_id)
    elif location==2:
        sku='FBB'+str(item_id)
    elif location==3:
        sku='FBG'+str(item_id)
    else:
        return 0.0
    if amazonLongTermActivePromotions.has_key(sku):
        msp = (amazonLongTermActivePromotions.get(sku)).promoPrice
    elif amazonShortTermActivePromotions.has_key(sku):
        msp = (amazonShortTermActivePromotions.get(sku)).promoPrice
    else:
        msp = 0
    return msp


def getInventoryMovementStatus(amScraping):
    try:
        nodStock = (float(amScraping.ourInventory))/amScraping.avgSale
    except Exception as e:
        print "exception in nod stock"
        print e
        nodStock = float("inf")
    try:
        if amScraping.avgSale==0:
            return "Not Moving"
        elif nodStock > 20:
            return "Slow Moving"
        else:
            return "Moving"
    except Exception as e:
        print e
        print "exception in ims"
        return ""

def getMargin(amScraping):
    #sheet.write(sheet_iterator, 30, round(amScraping.promoPrice - amScraping.lowestPossibleSp))
    #Promo Price minus costs plus subsidy
    #costs = WANLC (actual or overrrde whichever is applicable) + Courier cost + Closing fee + Commission + Packaging + VAT + Returns Cost + other cost.
    '''
    if(amScraping.ourSellingPrice >= amScraping.promoPrice):
        mySubsidy = amScraping.subsidy
    else:
        mySubsidy = 0
    '''
    print 'promo price ',amScraping.promoPrice
    #print 'mySubsidy ',mySubsidy
    print 'wanlc ',amScraping.wanlc
    print 'courier cost ',getCourierCost(amScraping)
    print 'closing fee ',getClosingFee(amScraping)
    print 'commission ',getCommission(amScraping) #1
    print 'packaging ',getPackagingCost(amScraping)
    print 'vat ',getVat(amScraping) #2
    print 'return cost ',getReturnCost(amScraping) #3
    print 'other cost ',amScraping.otherCost
    print 'cost to amazon ',getCostToAmazon(amScraping) #4
    myCosts = amScraping.wanlc + getCourierCost(amScraping) + getClosingFee(amScraping) + getCommission(amScraping) + getPackagingCost(amScraping) + getVat(amScraping) + getReturnCost(amScraping) + amScraping.otherCost 
    margin = amScraping.promoPrice - myCosts + amScraping.subsidy
    print 'margin for ',amScraping.item_id,' is ',margin
    return round(margin)

def getNewMargin(amScraping,proposedSp):
    myCosts = amScraping.wanlc + getCourierCost(amScraping) + getClosingFee(amScraping) + getNewCommission(amScraping,proposedSp) + getPackagingCost(amScraping) + getNewVat(amScraping,getVatRate(amScraping.item_id,amScraping.warehouseLocation,proposedSp),proposedSp) + getNewReturnCost(amScraping,proposedSp) + amScraping.otherCost 
    margin = proposedSp - myCosts + amScraping.subsidy
    print 'New margin for ',amScraping.item_id,' is ',margin
    return round(margin)

def getVat(data):
    #VAT amount = Promo Price/(1+Vat Rate)*VAT Rate minus NLC/(1+VAT Rate)*VAT Rate
    myVatPercentage = data.vatRate/100
    myVat = myVatPercentage*(data.promoPrice/(1+myVatPercentage) - data.wanlc/(1+myVatPercentage))
    if(myVat<0):
        return 0
    else:
        return round(myVat)
    
def getNewVat(data,vatRate,proposedSp):
    myVatPercentage = vatRate/100
    myVat = myVatPercentage*(proposedSp/(1+myVatPercentage) - data.wanlc/(1+myVatPercentage))
    if(myVat<0):
        return 0
    else:
        return round(myVat)

def getAvgSale(days, sale):
    avgSalePerDay=0 if days==0 else (float(sale)/days)
    return round(avgSalePerDay,2)

def getCategory(data):
    return categoryMap[data.category]

def writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,runType):
    wbk = xlwt.Workbook(encoding="UTF-8")
    sheet = wbk.add_sheet('Competiton Det')
    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
    writeheaders(sheet,heading_xf)
    
    sheet_iterator = 1
    cantCompeteItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).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'
        elif amScraping.warehouseLocation == 2:
            sku = 'FBB'+str(amScraping.item_id)
            loc = 'BANGLORE'
        else:
            sku = 'FBG'+str(amScraping.item_id)
            loc = 'GURGAON'
        sheet.write(sheet_iterator, 1, sku)
        sheet.write(sheet_iterator, 2, amScraping.asin)
        sheet.write(sheet_iterator, 3, 'http://www.amazon.in/dp/'+amScraping.asin)
        sheet.write(sheet_iterator, 4, loc)
        sheet.write(sheet_iterator, 5, item.brand)
        sheet.write(sheet_iterator, 6, getCategory(item))
        sheet.write(sheet_iterator, 7, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
        sheet.write(sheet_iterator, 8, item.weight)
        sheet.write(sheet_iterator, 9, amScraping.courierCost)
        sheet.write(sheet_iterator, 10, item.mrp)
        sheet.write(sheet_iterator, 11, amScraping.ourSellingPrice)
        sheet.write(sheet_iterator, 12, amScraping.promoPrice)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 13, "Yes")
        else:
            sheet.write(sheet_iterator, 13, "No")
        sheet.write(sheet_iterator, 14, amScraping.lowestPossibleSp)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 15, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 15, amScraping.ourRank)
        sheet.write(sheet_iterator, 16, 'Cant Compete')
        sheet.write(sheet_iterator, 17, amScraping.ourInventory)
        sheet.write(sheet_iterator, 18, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 19, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 20, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 22, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 23, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 25, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 26, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 27, amScraping.lowestMfnIgnoredOffer)
        sheet.write(sheet_iterator, 28, amScraping.lowestMfnOffer)
        sheet.write(sheet_iterator, 29, amScraping.lowestFbaOffer)
        sheet.write(sheet_iterator, 30, amScraping.competitivePrice)
        sheet.write(sheet_iterator, 31, amScraping.otherCost)
        sheet.write(sheet_iterator, 32, amScraping.wanlc)
        sheet.write(sheet_iterator, 33, amScraping.isNlcOverridden)
        sheet.write(sheet_iterator, 34, amScraping.subsidy)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 35, getMsp(amScraping.item_id,amScraping.warehouseLocation))
        else:
            sheet.write(sheet_iterator, 35, 0.0)
        sheet.write(sheet_iterator, 36, amScraping.commission)
        sheet.write(sheet_iterator, 37, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 38, amScraping.returnProvision)
        sheet.write(sheet_iterator, 39, amScraping.vatRate)
        sheet.write(sheet_iterator, 40, getMargin(amScraping))
        sheet.write(sheet_iterator, 41, amScraping.proposedSp)
        sheet.write(sheet_iterator, 42, amScraping.avgSale)
        try:
            daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
        except:
            daysOfStock = float("inf")
        if str(daysOfStock)=='inf':
            sheet.write(sheet_iterator, 43, str(daysOfStock))
        else:
            sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
        sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
        sale, count = get30DaysStats(sku)
        sheet.write(sheet_iterator, 45, sale)
        sheet.write(sheet_iterator, 46, count)
        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
        
        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
        if inStockStats[0].dateOfSale == 0:
            sheet.write(sheet_iterator, 48, 'No History available')
        else:
            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[2], inStockStats[1]))
        toDaysSale = getTodaysSale(sku)
        sheet.write(sheet_iterator, 52, toDaysSale[0])
        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
        
        #sheet.write(sheet_iterator, 44, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))
        sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
        sheet.write(sheet_iterator, 63, amScraping.isOversized)
        sheet_iterator+=1
    #TODO : Take excell sheet generation code inside a function 
    competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for competitiveItem in competitiveItems:
        amScraping =  competitiveItem[0]
        item = competitiveItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:
            sku = 'FBA'+str(amScraping.item_id)
            loc = 'MUMBAI'
        elif amScraping.warehouseLocation == 2:
            sku = 'FBB'+str(amScraping.item_id)
            loc = 'BANGLORE'
        else:
            sku='FBG'+str(amScraping.item_id)
            loc = 'GURGAON'
        sheet.write(sheet_iterator, 1, sku)
        sheet.write(sheet_iterator, 2, amScraping.asin)
        sheet.write(sheet_iterator, 3, 'http://www.amazon.in/dp/'+amScraping.asin)
        sheet.write(sheet_iterator, 4, loc)
        sheet.write(sheet_iterator, 5, item.brand)
        sheet.write(sheet_iterator, 6, getCategory(item))
        sheet.write(sheet_iterator, 7, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
        sheet.write(sheet_iterator, 8, item.weight)
        sheet.write(sheet_iterator, 9, amScraping.courierCost)
        sheet.write(sheet_iterator, 10, item.mrp)
        sheet.write(sheet_iterator, 11, amScraping.ourSellingPrice)
        sheet.write(sheet_iterator, 12, amScraping.promoPrice)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 13, "Yes")
        else:
            sheet.write(sheet_iterator, 13, "No")
        sheet.write(sheet_iterator, 14, amScraping.lowestPossibleSp)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 15, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 15, amScraping.ourRank)
        sheet.write(sheet_iterator, 16, 'Competitive')
        sheet.write(sheet_iterator, 17, amScraping.ourInventory)
        sheet.write(sheet_iterator, 18, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 19, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 20, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 22, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 23, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 25, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 26, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 27, amScraping.lowestMfnIgnoredOffer)
        sheet.write(sheet_iterator, 28, amScraping.lowestMfnOffer)
        sheet.write(sheet_iterator, 29, amScraping.lowestFbaOffer)
        sheet.write(sheet_iterator, 30, amScraping.competitivePrice)
        sheet.write(sheet_iterator, 31, amScraping.otherCost)
        sheet.write(sheet_iterator, 32, amScraping.wanlc)
        sheet.write(sheet_iterator, 33, amScraping.isNlcOverridden)
        sheet.write(sheet_iterator, 34, amScraping.subsidy)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 35, getMsp(amScraping.item_id,amScraping.warehouseLocation))
        else:
            sheet.write(sheet_iterator, 35, 0.0)
        sheet.write(sheet_iterator, 36, amScraping.commission)
        sheet.write(sheet_iterator, 37, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 38, amScraping.returnProvision)
        sheet.write(sheet_iterator, 39, amScraping.vatRate)
        sheet.write(sheet_iterator, 40, getMargin(amScraping))
        sheet.write(sheet_iterator, 41, amScraping.proposedSp)
        sheet.write(sheet_iterator, 42, amScraping.avgSale)
        try:
            daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
        except:
            daysOfStock = float("inf")
        if str(daysOfStock)=='inf':
            sheet.write(sheet_iterator, 43, str(daysOfStock))
        else:
            sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
        sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
        sale, count = get30DaysStats(sku)
        sheet.write(sheet_iterator, 45, sale)
        sheet.write(sheet_iterator, 46, count)
        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
        
        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
        if inStockStats[0].dateOfSale == 0:
            sheet.write(sheet_iterator, 48, 'No History available')
        else:
            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[2], inStockStats[1]))
        toDaysSale = getTodaysSale(sku)
        sheet.write(sheet_iterator, 52, toDaysSale[0])
        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
        sheet.write(sheet_iterator, 63, amScraping.isOversized)
        if amScraping.decision is None:
            sheet.write(sheet_iterator, 54, 'Auto Pricing Inactive')
            sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
            sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
            sheet_iterator+=1
            continue
        sheet.write(sheet_iterator, 54, Decision._VALUES_TO_NAMES.get(amScraping.decision))
        sheet.write(sheet_iterator, 55, amScraping.reason)
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
            sheet.write(sheet_iterator, 56, math.ceil(amScraping.proposedSp))
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
            sheet.write(sheet_iterator, 56, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
        sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
        sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
        sheet_iterator+=1
    
    almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for almostCompetitiveItem in almostCompetitiveItems:
        amScraping =  almostCompetitiveItem[0]
        item = almostCompetitiveItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:
            sku = 'FBA'+str(amScraping.item_id)
            loc = 'MUMBAI'
        elif amScraping.warehouseLocation == 2:
            sku = 'FBB'+str(amScraping.item_id)
            loc = 'BANGLORE'
        else:
            sku = 'FBG'+str(amScraping.item_id)
            loc = 'GURGAON'
        sheet.write(sheet_iterator, 1, sku)
        sheet.write(sheet_iterator, 2, amScraping.asin)
        sheet.write(sheet_iterator, 3, 'http://www.amazon.in/dp/'+amScraping.asin)
        sheet.write(sheet_iterator, 4, loc)
        sheet.write(sheet_iterator, 5, item.brand)
        sheet.write(sheet_iterator, 6, getCategory(item))
        sheet.write(sheet_iterator, 7, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
        sheet.write(sheet_iterator, 8, item.weight)
        sheet.write(sheet_iterator, 9, amScraping.courierCost)
        sheet.write(sheet_iterator, 10, item.mrp)
        sheet.write(sheet_iterator, 11, amScraping.ourSellingPrice)
        sheet.write(sheet_iterator, 12, amScraping.promoPrice)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 13, "Yes")
        else:
            sheet.write(sheet_iterator, 13, "No")
        sheet.write(sheet_iterator, 14, amScraping.lowestPossibleSp)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 15, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 15, amScraping.ourRank)
        sheet.write(sheet_iterator, 16, 'Almost Competitive')
        sheet.write(sheet_iterator, 17, amScraping.ourInventory)
        sheet.write(sheet_iterator, 18, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 19, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 20, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 22, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 23, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 25, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 26, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 27, amScraping.lowestMfnIgnoredOffer)
        sheet.write(sheet_iterator, 28, amScraping.lowestMfnOffer)
        sheet.write(sheet_iterator, 29, amScraping.lowestFbaOffer)
        sheet.write(sheet_iterator, 30, amScraping.competitivePrice)
        sheet.write(sheet_iterator, 31, amScraping.otherCost)
        sheet.write(sheet_iterator, 32, amScraping.wanlc)
        sheet.write(sheet_iterator, 33, amScraping.isNlcOverridden)
        sheet.write(sheet_iterator, 34, amScraping.subsidy)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 35, getMsp(amScraping.item_id,amScraping.warehouseLocation))
        else:
            sheet.write(sheet_iterator, 35, 0.0)
        sheet.write(sheet_iterator, 36, amScraping.commission)
        sheet.write(sheet_iterator, 37, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 38, amScraping.returnProvision)
        sheet.write(sheet_iterator, 39, amScraping.vatRate)
        sheet.write(sheet_iterator, 40, getMargin(amScraping))
        sheet.write(sheet_iterator, 41, amScraping.proposedSp)
        sheet.write(sheet_iterator, 42, amScraping.avgSale)
        try:
            daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
        except:
            daysOfStock = float("inf")
        if str(daysOfStock)=='inf':
            sheet.write(sheet_iterator, 43, str(daysOfStock))
        else:
            sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
        sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
        sale, count = get30DaysStats(sku)
        sheet.write(sheet_iterator, 45, sale)
        sheet.write(sheet_iterator, 46, count)
        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
        
        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
        if inStockStats[0].dateOfSale == 0:
            sheet.write(sheet_iterator, 48, 'No History available')
        else:
            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[2], inStockStats[1]))
        toDaysSale = getTodaysSale(sku)
        sheet.write(sheet_iterator, 52, toDaysSale[0])
        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
        sheet.write(sheet_iterator, 63, amScraping.isOversized)
        if amScraping.decision is None:
            sheet.write(sheet_iterator, 54, 'Auto Pricing Inactive')
            sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
            sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
            sheet_iterator+=1
            continue
        sheet.write(sheet_iterator, 54, Decision._VALUES_TO_NAMES.get(amScraping.decision))
        sheet.write(sheet_iterator, 55, amScraping.reason)
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
            sheet.write(sheet_iterator, 56, math.ceil(amScraping.proposedSp))
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
            sheet.write(sheet_iterator, 56, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
        sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
        sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
        sheet_iterator+=1
    
    amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for amongCheapestItem in amongCheapestItems:
        amScraping =  amongCheapestItem[0]
        item = amongCheapestItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:
            sku = 'FBA'+str(amScraping.item_id)
            loc = 'MUMBAI'
        elif amScraping.warehouseLocation == 2:
            sku = 'FBB'+str(amScraping.item_id)
            loc = 'BANGLORE'
        else:
            sku = 'FBG'+str(amScraping.item_id)
            loc = 'GURGAON'
        sheet.write(sheet_iterator, 1, sku)
        sheet.write(sheet_iterator, 2, amScraping.asin)
        sheet.write(sheet_iterator, 3, 'http://www.amazon.in/dp/'+amScraping.asin)
        sheet.write(sheet_iterator, 4, loc)
        sheet.write(sheet_iterator, 5, item.brand)
        sheet.write(sheet_iterator, 6, getCategory(item))
        sheet.write(sheet_iterator, 7, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
        sheet.write(sheet_iterator, 8, item.weight)
        sheet.write(sheet_iterator, 9, amScraping.courierCost)
        sheet.write(sheet_iterator, 10, item.mrp)
        sheet.write(sheet_iterator, 11, amScraping.ourSellingPrice)
        sheet.write(sheet_iterator, 12, amScraping.promoPrice)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 13, "Yes")
        else:
            sheet.write(sheet_iterator, 13, "No")
        sheet.write(sheet_iterator, 14, amScraping.lowestPossibleSp)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 15, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 15, amScraping.ourRank)
        sheet.write(sheet_iterator, 16, 'Among Cheapest')
        sheet.write(sheet_iterator, 17, amScraping.ourInventory)
        sheet.write(sheet_iterator, 18, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 19, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 20, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 22, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 23, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 25, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 26, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 27, amScraping.lowestMfnIgnoredOffer)
        sheet.write(sheet_iterator, 28, amScraping.lowestMfnOffer)
        sheet.write(sheet_iterator, 29, amScraping.lowestFbaOffer)
        sheet.write(sheet_iterator, 30, amScraping.competitivePrice)
        sheet.write(sheet_iterator, 31, amScraping.otherCost)
        sheet.write(sheet_iterator, 32, amScraping.wanlc)
        sheet.write(sheet_iterator, 33, amScraping.isNlcOverridden)
        sheet.write(sheet_iterator, 34, amScraping.subsidy)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 35, getMsp(amScraping.item_id,amScraping.warehouseLocation))
        else:
            sheet.write(sheet_iterator, 35, 0.0)
        sheet.write(sheet_iterator, 36, amScraping.commission)
        sheet.write(sheet_iterator, 37, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 38, amScraping.returnProvision)
        sheet.write(sheet_iterator, 39, amScraping.vatRate)
        sheet.write(sheet_iterator, 40, getMargin(amScraping))
        sheet.write(sheet_iterator, 41, amScraping.proposedSp)
        sheet.write(sheet_iterator, 42, amScraping.avgSale)
        try:
            daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
        except:
            daysOfStock = float("inf")
        if str(daysOfStock)=='inf':
            sheet.write(sheet_iterator, 43, str(daysOfStock))
        else:
            sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
        sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
        sale, count = get30DaysStats(sku)
        sheet.write(sheet_iterator, 45, sale)
        sheet.write(sheet_iterator, 46, count)
        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
        
        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
        if inStockStats[0].dateOfSale == 0:
            sheet.write(sheet_iterator, 48, 'No History available')
        else:
            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[2], inStockStats[1]))
        toDaysSale = getTodaysSale(sku)
        sheet.write(sheet_iterator, 52, toDaysSale[0])
        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
        sheet.write(sheet_iterator, 63, amScraping.isOversized)
        if amScraping.decision is None:
            sheet.write(sheet_iterator, 54, 'Auto Pricing Inactive')
            sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
            sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
            sheet_iterator+=1
            continue
        sheet.write(sheet_iterator, 54, Decision._VALUES_TO_NAMES.get(amScraping.decision))
        sheet.write(sheet_iterator, 55, amScraping.reason)
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
            sheet.write(sheet_iterator, 56, math.ceil(amScraping.proposedSp))
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
            sheet.write(sheet_iterator, 56, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
        sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
        sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
        sheet_iterator+=1
        
    
#    sheet = wbk.add_sheet('Cheapest')
#    xstr = lambda s: s or ""
#    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
#    
#    excel_integer_format = '0'
#    integer_style = xlwt.XFStyle()
#    integer_style.num_format_str = excel_integer_format
#    sheet.write(0, 0, "Item Id", heading_xf)
#    sheet.write(0, 1, "Amazon Sku", heading_xf)
#    sheet.write(0, 2, "Asin", heading_xf)
#    sheet.write(0, 3, "Location", heading_xf)
#    sheet.write(0, 4, "Brand", heading_xf)
#    sheet.write(0, 5, "Category", heading_xf)
#    sheet.write(0, 6, "Product Name", heading_xf)
#    sheet.write(0, 7, "Weight", heading_xf)
#    sheet.write(0, 8, "Courier Cost", heading_xf)
#    sheet.write(0, 9, "Our SP", heading_xf)
#    sheet.write(0, 10, "Promo Price", heading_xf)
#    sheet.write(0, 11, "Is Promotion", heading_xf)
#    sheet.write(0, 12, "Lowest Possible SP", heading_xf)
#    sheet.write(0, 13, "Rank", heading_xf)
#    sheet.write(0, 14, "Our Inventory", heading_xf)
#    sheet.write(0, 15, "Lowest Seller SP", heading_xf)
#    sheet.write(0, 16, "Lowest Seller Rating", heading_xf)
#    sheet.write(0, 17, "Lowest Seller Shipping Time", heading_xf)
#    sheet.write(0, 18, "Second Lowest Seller SP", heading_xf)
#    sheet.write(0, 19, "Second Lowest Seller Rating", heading_xf)
#    sheet.write(0, 20, "Second Lowest Seller Shipping Time", heading_xf)
#    sheet.write(0, 21, "Third Lowest Seller SP", heading_xf)
#    sheet.write(0, 22, "Third Lowest Seller Rating", heading_xf)
#    sheet.write(0, 23, "Third Lowest Seller Shipping Time", heading_xf)
#    sheet.write(0, 24, "Other Cost", heading_xf)
#    sheet.write(0, 25, "WANLC", heading_xf)
#    sheet.write(0, 26, "Subsidy", heading_xf)
#    sheet.write(0, 27, "Commission", heading_xf)
#    sheet.write(0, 28, "Competitor Commission", heading_xf)
#    sheet.write(0, 29, "Return Provision", heading_xf)
#    sheet.write(0, 30, "Vat Rate", heading_xf)
#    sheet.write(0, 31, "Margin", heading_xf)
#    sheet.write(0, 32, "Proposed Sp", heading_xf)
#    sheet.write(0, 33, "Avg Sale", heading_xf)
#    sheet.write(0, 34, "Sales History", heading_xf)
#    sheet.write(0, 35, "Decision", heading_xf)
#    sheet.write(0, 36, "Reason", heading_xf)
#    sheet.write(0, 37, "Updated Price", heading_xf)
#    sheet_iterator = 1
    cheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for cheapestItem in cheapestItems:
        amScraping =  cheapestItem[0]
        item = cheapestItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:
            sku = 'FBA'+str(amScraping.item_id)
            loc = 'MUMBAI'
        elif amScraping.warehouseLocation == 2:
            sku = 'FBB'+str(amScraping.item_id)
            loc = 'BANGLORE'
        else:
            sku = 'FBG'+str(amScraping.item_id)
            loc = 'GURGAON'
        sheet.write(sheet_iterator, 1, sku)
        sheet.write(sheet_iterator, 2, amScraping.asin)
        sheet.write(sheet_iterator, 3, 'http://www.amazon.in/dp/'+amScraping.asin)
        sheet.write(sheet_iterator, 4, loc)
        sheet.write(sheet_iterator, 5, item.brand)
        sheet.write(sheet_iterator, 6, getCategory(item))
        sheet.write(sheet_iterator, 7, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
        sheet.write(sheet_iterator, 8, item.weight)
        sheet.write(sheet_iterator, 9, amScraping.courierCost)
        sheet.write(sheet_iterator, 10, item.mrp)
        sheet.write(sheet_iterator, 11, amScraping.ourSellingPrice)
        sheet.write(sheet_iterator, 12, amScraping.promoPrice)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 13, "Yes")
        else:
            sheet.write(sheet_iterator, 13, "No")
        sheet.write(sheet_iterator, 14, amScraping.lowestPossibleSp)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 15, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 15, amScraping.ourRank)
        sheet.write(sheet_iterator, 16, 'Cheapest')
        sheet.write(sheet_iterator, 17, amScraping.ourInventory)
        sheet.write(sheet_iterator, 18, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 19, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 20, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 22, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 23, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 25, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 26, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 27, '')
        sheet.write(sheet_iterator, 28, '')
        sheet.write(sheet_iterator, 29, '')
        sheet.write(sheet_iterator, 30, '')
        sheet.write(sheet_iterator, 31, amScraping.otherCost)
        sheet.write(sheet_iterator, 32, amScraping.wanlc)
        sheet.write(sheet_iterator, 33, amScraping.isNlcOverridden)
        sheet.write(sheet_iterator, 34, amScraping.subsidy)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 35, getMsp(amScraping.item_id,amScraping.warehouseLocation))
        else:
            sheet.write(sheet_iterator, 35, 0.0)
        sheet.write(sheet_iterator, 36, amScraping.commission)
        sheet.write(sheet_iterator, 37, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 38, amScraping.returnProvision)
        sheet.write(sheet_iterator, 39, amScraping.vatRate)
        sheet.write(sheet_iterator, 40, getMargin(amScraping))
        sheet.write(sheet_iterator, 41, amScraping.proposedSp)
        sheet.write(sheet_iterator, 42, amScraping.avgSale)
        try:
            daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
        except:
            daysOfStock = float("inf")
        if str(daysOfStock)=='inf':
            sheet.write(sheet_iterator, 43, str(daysOfStock))
        else:
            sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
        sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
        sale, count = get30DaysStats(sku)
        sheet.write(sheet_iterator, 45, sale)
        sheet.write(sheet_iterator, 46, count)
        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
        
        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
        if inStockStats[0].dateOfSale == 0:
            sheet.write(sheet_iterator, 48, 'No History available')
        else:
            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[2], inStockStats[1]))
        toDaysSale = getTodaysSale(sku)
        sheet.write(sheet_iterator, 52, toDaysSale[0])
        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
        sheet.write(sheet_iterator, 63, amScraping.isOversized)
        if amScraping.decision is None:
            sheet.write(sheet_iterator, 54, 'Auto Pricing Inactive')
            sheet.write(sheet_iterator, 57, getNewMargin(amScraping,amScraping.proposedSp))
            sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
            sheet_iterator+=1
            continue
        sheet.write(sheet_iterator, 54, Decision._VALUES_TO_NAMES.get(amScraping.decision))
        sheet.write(sheet_iterator, 55, amScraping.reason)
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
            sheet.write(sheet_iterator, 56, math.ceil(amScraping.proposedSp))
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
            sheet.write(sheet_iterator, 56, min(math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)),amScraping.proposedSp))
        sheet.write(sheet_iterator, 57, getNewMargin(amScraping,math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice))))
        sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
        sheet_iterator+=1
    
    exceptionCheapItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp)\
    .filter(or_(AmazonScrapingHistory.exceptionType==3,AmazonScrapingHistory.exceptionType==5)).all()
    
    for exceptionCheapItem in exceptionCheapItems:
        amScraping =  exceptionCheapItem[0]
        item = exceptionCheapItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:
            sku = 'FBA'+str(amScraping.item_id)
            loc = 'MUMBAI'
        elif amScraping.warehouseLocation == 2:
            sku = 'FBB'+str(amScraping.item_id)
            loc = 'BANGLORE'
        else:
            sku = 'FBG'+str(amScraping.item_id)
            loc = 'GURGAON'
        sheet.write(sheet_iterator, 1, sku)
        sheet.write(sheet_iterator, 2, amScraping.asin)
        sheet.write(sheet_iterator, 3, 'http://www.amazon.in/dp/'+amScraping.asin)
        sheet.write(sheet_iterator, 4, loc)
        sheet.write(sheet_iterator, 5, item.brand)
        sheet.write(sheet_iterator, 6, getCategory(item))
        sheet.write(sheet_iterator, 7, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
        sheet.write(sheet_iterator, 8, item.weight)
        sheet.write(sheet_iterator, 9, amScraping.courierCost)
        sheet.write(sheet_iterator, 10, item.mrp)
        sheet.write(sheet_iterator, 11, amScraping.ourSellingPrice)
        sheet.write(sheet_iterator, 12, amScraping.promoPrice)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 13, "Yes")
        else:
            sheet.write(sheet_iterator, 13, "No")
        sheet.write(sheet_iterator, 14, amScraping.lowestPossibleSp)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 15, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 15, amScraping.ourRank)
        sheet.write(sheet_iterator, 16, 'Cheapest')
        sheet.write(sheet_iterator, 17, amScraping.ourInventory)
        sheet.write(sheet_iterator, 18, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 19, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 20, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 22, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 23, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 25, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 26, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 27, amScraping.lowestMfnIgnoredOffer)
        sheet.write(sheet_iterator, 28, amScraping.lowestMfnOffer)
        sheet.write(sheet_iterator, 29, amScraping.lowestFbaOffer)
        sheet.write(sheet_iterator, 30, amScraping.competitivePrice)
        sheet.write(sheet_iterator, 31, amScraping.otherCost)
        sheet.write(sheet_iterator, 32, amScraping.wanlc)
        sheet.write(sheet_iterator, 33, amScraping.isNlcOverridden)
        sheet.write(sheet_iterator, 34, amScraping.subsidy)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 35, getMsp(amScraping.item_id,amScraping.warehouseLocation))
        else:
            sheet.write(sheet_iterator, 35, 0.0)
        sheet.write(sheet_iterator, 36, amScraping.commission)
        sheet.write(sheet_iterator, 37, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 38, amScraping.returnProvision)
        sheet.write(sheet_iterator, 39, amScraping.vatRate)
        sheet.write(sheet_iterator, 40, getMargin(amScraping))
        sheet.write(sheet_iterator, 41, amScraping.proposedSp)
        sheet.write(sheet_iterator, 42, amScraping.avgSale)
        try:
            daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
        except:
            daysOfStock = float("inf")
        if str(daysOfStock)=='inf':
            sheet.write(sheet_iterator, 43, str(daysOfStock))
        else:
            sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
        sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
        sale, count = get30DaysStats(sku)
        sheet.write(sheet_iterator, 45, sale)
        sheet.write(sheet_iterator, 46, count)
        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
        
        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
        if inStockStats[0].dateOfSale == 0:
            sheet.write(sheet_iterator, 48, 'No History available')
        else:
            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[2], inStockStats[1]))
        toDaysSale = getTodaysSale(sku)
        sheet.write(sheet_iterator, 52, toDaysSale[0])
        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
        sheet.write(sheet_iterator, 54, 'AUTO_INCREMENT_FAILED')
        if amScraping.exceptionType==3:
            sheet.write(sheet_iterator, 55, 'We are the only seller')
        else:
            sheet.write(sheet_iterator, 55, amScraping.reason)
        sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
        sheet.write(sheet_iterator, 63, amScraping.isOversized)
        sheet_iterator+=1

    
#    sheet = wbk.add_sheet('Negative Margin')
#    xstr = lambda s: s or ""
#    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
#    
#    excel_integer_format = '0'
#    integer_style = xlwt.XFStyle()
#    integer_style.num_format_str = excel_integer_format
#    sheet.write(0, 0, "Item Id", heading_xf)
#    sheet.write(0, 1, "Amazon Sku", heading_xf)
#    sheet.write(0, 2, "Asin", heading_xf)
#    sheet.write(0, 3, "Location", heading_xf)
#    sheet.write(0, 4, "Brand", heading_xf)
#    sheet.write(0, 5, "Category", heading_xf)
#    sheet.write(0, 6, "Product Name", heading_xf)
#    sheet.write(0, 7, "Weight", heading_xf)
#    sheet.write(0, 8, "Courier Cost", heading_xf)
#    sheet.write(0, 9, "Our SP", heading_xf)
#    sheet.write(0, 10, "Promo Price", heading_xf)
#    sheet.write(0, 11, "Is Promotion", heading_xf)
#    sheet.write(0, 12, "Lowest Possible SP", heading_xf)
#    sheet.write(0, 13, "Rank", heading_xf)
#    sheet.write(0, 14, "Our Inventory", heading_xf)
#    sheet.write(0, 15, "Lowest Seller SP", heading_xf)
#    sheet.write(0, 16, "Lowest Seller Rating", heading_xf)
#    sheet.write(0, 17, "Lowest Seller Shipping Time", heading_xf)
#    sheet.write(0, 18, "Second Lowest Seller SP", heading_xf)
#    sheet.write(0, 19, "Second Lowest Seller Rating", heading_xf)
#    sheet.write(0, 20, "Second Lowest Seller Shipping Time", heading_xf)
#    sheet.write(0, 21, "Third Lowest Seller SP", heading_xf)
#    sheet.write(0, 22, "Third Lowest Seller Rating", heading_xf)
#    sheet.write(0, 23, "Third Lowest Seller Shipping Time", heading_xf)
#    sheet.write(0, 24, "Other Cost", heading_xf)
#    sheet.write(0, 25, "WANLC", heading_xf)
#    sheet.write(0, 26, "Subsidy", heading_xf)
#    sheet.write(0, 27, "Commission", heading_xf)
#    sheet.write(0, 28, "Competitor Commission", heading_xf)
#    sheet.write(0, 29, "Return Provision", heading_xf)
#    sheet.write(0, 30, "Vat Rate", heading_xf)
#    sheet.write(0, 31, "Margin", heading_xf)
#    sheet.write(0, 32, "Avg Sale", heading_xf)
#    sheet.write(0, 33, "Sales History", heading_xf)
#    
#    sheet_iterator = 1
    negativeMargins = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for negativeMarginItem in negativeMargins:
        amScraping =  negativeMarginItem[0]
        item = negativeMarginItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:
            sku = 'FBA'+str(amScraping.item_id)
            loc = 'MUMBAI'
        elif amScraping.warehouseLocation == 2:
            sku = 'FBB'+str(amScraping.item_id)
            loc = 'BANGLORE'
        else:
            sku = 'FBG'+str(amScraping.item_id)
            loc = 'GURGAON'
        sheet.write(sheet_iterator, 1, sku)
        sheet.write(sheet_iterator, 2, amScraping.asin)
        sheet.write(sheet_iterator, 3, 'http://www.amazon.in/dp/'+amScraping.asin)
        sheet.write(sheet_iterator, 4, loc)
        sheet.write(sheet_iterator, 5, item.brand)
        sheet.write(sheet_iterator, 6, getCategory(item))
        sheet.write(sheet_iterator, 7, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
        sheet.write(sheet_iterator, 8, item.weight)
        sheet.write(sheet_iterator, 9, amScraping.courierCost)
        sheet.write(sheet_iterator, 10, item.mrp)
        sheet.write(sheet_iterator, 11, amScraping.ourSellingPrice)
        sheet.write(sheet_iterator, 12, amScraping.promoPrice)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 13, "Yes")
        else:
            sheet.write(sheet_iterator, 13, "No")
        sheet.write(sheet_iterator, 14, amScraping.lowestPossibleSp)
        if amScraping.ourRank > 3:
            sheet.write(sheet_iterator, 15, 'Greater than 3')
        else:
            sheet.write(sheet_iterator, 15, amScraping.ourRank)
        sheet.write(sheet_iterator, 16, 'Negative Margin')
        sheet.write(sheet_iterator, 17, amScraping.ourInventory)
        sheet.write(sheet_iterator, 18, amScraping.lowestSellerSp)
        sheet.write(sheet_iterator, 19, amScraping.lowestSellerRating)
        sheet.write(sheet_iterator, 20, amScraping.lowestSellerShippingTime)
        sheet.write(sheet_iterator, 21, amScraping.secondLowestSellerSp)
        sheet.write(sheet_iterator, 22, amScraping.secondLowestSellerRating)
        sheet.write(sheet_iterator, 23, amScraping.secondLowestSellerShippingTime)
        sheet.write(sheet_iterator, 24, amScraping.thirdLowestSellerSp)
        sheet.write(sheet_iterator, 25, amScraping.thirdLowestSellerRating)
        sheet.write(sheet_iterator, 26, amScraping.thirdLowestSellerShippingTime)
        sheet.write(sheet_iterator, 27, '')
        sheet.write(sheet_iterator, 28, '')
        sheet.write(sheet_iterator, 29, '')
        sheet.write(sheet_iterator, 30, '')
        sheet.write(sheet_iterator, 31, amScraping.otherCost)
        sheet.write(sheet_iterator, 32, amScraping.wanlc)
        sheet.write(sheet_iterator, 33, amScraping.isNlcOverridden)
        sheet.write(sheet_iterator, 34, amScraping.subsidy)
        if amScraping.isPromotion:
            sheet.write(sheet_iterator, 35, getMsp(amScraping.item_id,amScraping.warehouseLocation))
        else:
            sheet.write(sheet_iterator, 35, 0.0)
        sheet.write(sheet_iterator, 36, amScraping.commission)
        sheet.write(sheet_iterator, 37, amScraping.competitorCommission)
        sheet.write(sheet_iterator, 38, amScraping.returnProvision)
        sheet.write(sheet_iterator, 39, amScraping.vatRate)
        sheet.write(sheet_iterator, 40, getMargin(amScraping))
        sheet.write(sheet_iterator, 42, amScraping.avgSale)
        try:
            daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
        except:
            daysOfStock = float("inf")
        if str(daysOfStock)=='inf':
            sheet.write(sheet_iterator, 43, str(daysOfStock))
        else:
            sheet.write(sheet_iterator, 43, str(round(daysOfStock,1)))
        sheet.write(sheet_iterator, 44, getOosString(saleMap.get(sku)))
        sale, count = get30DaysStats(sku)
        sheet.write(sheet_iterator, 45, sale)
        sheet.write(sheet_iterator, 46, count)
        sheet.write(sheet_iterator, 47, getAvgSale(count, sale))
        
        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
        if inStockStats[0].dateOfSale == 0:
            sheet.write(sheet_iterator, 48, 'No History available')
        else:
            sheet.write(sheet_iterator, 48, str(to_py_date(inStockStats[0].dateOfSale).date()))
        sheet.write(sheet_iterator, 49, str(inStockStats[1]))
        sheet.write(sheet_iterator, 50, str(inStockStats[2]))
        sheet.write(sheet_iterator, 51, getAvgSale(inStockStats[2], inStockStats[1]))
        toDaysSale = getTodaysSale(sku)
        sheet.write(sheet_iterator, 52, toDaysSale[0])
        sheet.write(sheet_iterator, 53, str(toDaysSale[1]))
        sheet.write(sheet_iterator, 58, getInventoryMovementStatus(amScraping))
        sheet.write(sheet_iterator, 59, amScraping.packagingLength)
        sheet.write(sheet_iterator, 60, amScraping.packagingWidth)
        sheet.write(sheet_iterator, 61, amScraping.packagingHeight)
        sheet.write(sheet_iterator, 62, amScraping.packagingWeight)
        sheet.write(sheet_iterator, 63, amScraping.isOversized)
        sheet_iterator+=1
    
    sheet = wbk.add_sheet('Exception List')
    xstr = lambda s: s or ""
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format
    
    sheet.write(0, 0, "Item Id", heading_xf)
    sheet.write(0, 1, "Amazon Sku", heading_xf)
    sheet.write(0, 2, "Asin", heading_xf)
    sheet.write(0, 3, "URL", heading_xf)
    sheet.write(0, 4, "Location", heading_xf)
    sheet.write(0, 5, "Brand", heading_xf)
    sheet.write(0, 6, "Category", heading_xf)
    sheet.write(0, 7, "Product Name", heading_xf)
    sheet.write(0, 8, "MRP", heading_xf)
    sheet.write(0, 9, "Selling Price", heading_xf)
    sheet.write(0, 10, "Promo Price", heading_xf)
    sheet.write(0, 11, "Inventory", heading_xf)
    sheet.write(0, 12, "Avg Sale", heading_xf)
    sheet.write(0, 13, "NOD", heading_xf)
    sheet.write(0, 14, "Sales History", heading_xf)
    sheet.write(0, 15, "Last 30 days sale", heading_xf)
    sheet.write(0, 16, "No of days InStock in last 30 days", heading_xf)
    sheet.write(0, 17, "Avg 30 Days Sale", heading_xf)
    sheet.write(0, 18, "Last OOS Date", heading_xf)
    sheet.write(0, 19, "Total Sale Since InStock (Excluding Today)", heading_xf)
    sheet.write(0, 20, "Total Days Since InStock (Excluding Today)", heading_xf)
    sheet.write(0, 21, "Avg Sale Since InStock (Excluding Today)", heading_xf)
    sheet.write(0, 22, "Todays Sale Snapshot Time", heading_xf)
    sheet.write(0, 23, "Todays Sale", heading_xf)
    sheet.write(0, 24, "Inventory Movement Status", heading_xf)
    sheet.write(0, 25, "Reason", heading_xf)
    sheet.write(0, 26, "Packaging Length (Inch)", heading_xf)
    sheet.write(0, 27, "Packaging Width (Inch)", heading_xf)
    sheet.write(0, 28, "Packaging Height (Inch)", heading_xf)
    sheet.write(0, 29, "Packaging Weight (Kg)", heading_xf)
    sheet.write(0, 30, "Oversized Item", heading_xf)
    
    
    sheet_iterator = 1
    exceptionListItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for exceptionListItem in exceptionListItems:
        amScraping =  exceptionListItem[0]
        if amScraping.exceptionType in (3,5):
            continue
        item = exceptionListItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:
            sku = 'FBA'+str(amScraping.item_id)
            loc = 'MUMBAI'
        elif amScraping.warehouseLocation == 2:
            sku = 'FBB'+str(amScraping.item_id)
            loc = 'BANGLORE'
        else:
            sku = 'FBG'+str(amScraping.item_id)
            loc = 'GURGAON'
        sheet.write(sheet_iterator, 1, sku)
        sheet.write(sheet_iterator, 2, amScraping.asin)
        sheet.write(sheet_iterator, 3, 'http://www.amazon.in/dp/'+amScraping.asin)
        sheet.write(sheet_iterator, 4, loc)
        sheet.write(sheet_iterator, 5, item.brand)
        sheet.write(sheet_iterator, 6, getCategory(item))
        sheet.write(sheet_iterator, 7, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
        sheet.write(sheet_iterator, 8, item.mrp)
        sheet.write(sheet_iterator, 9, amScraping.ourSellingPrice)
        sheet.write(sheet_iterator, 10, amScraping.promoPrice)
        sheet.write(sheet_iterator, 11, amScraping.ourInventory)
        sheet.write(sheet_iterator, 12, amScraping.avgSale)
        try:
            daysOfStock = (float(amScraping.ourInventory))/amScraping.avgSale
        except:
            daysOfStock = float("inf")
        if str(daysOfStock)=='inf':
            sheet.write(sheet_iterator, 13, str(daysOfStock))
        else:
            sheet.write(sheet_iterator, 13, str(round(daysOfStock,1)))
        sheet.write(sheet_iterator, 14, getOosString(saleMap.get(sku)))
        sale, count = get30DaysStats(sku)
        sheet.write(sheet_iterator, 15, sale)
        sheet.write(sheet_iterator, 16, count)
        sheet.write(sheet_iterator, 17, getAvgSale(count, sale))
        
        inStockStats = getInStockStats(amScraping.item_id, amScraping.warehouseLocation)
        if inStockStats[0].dateOfSale == 0:
            sheet.write(sheet_iterator, 18, 'No History available')
        else:
            sheet.write(sheet_iterator, 18, str(to_py_date(inStockStats[0].dateOfSale).date()))
        sheet.write(sheet_iterator, 19, str(inStockStats[1]))
        sheet.write(sheet_iterator, 20, str(inStockStats[2]))
        sheet.write(sheet_iterator, 21, getAvgSale(inStockStats[2], inStockStats[1]))
        toDaysSale = getTodaysSale(sku)
        sheet.write(sheet_iterator, 22, toDaysSale[0])
        sheet.write(sheet_iterator, 23, str(toDaysSale[1]))
        sheet.write(sheet_iterator, 24, getInventoryMovementStatus(amScraping))
        sheet.write(sheet_iterator, 25, amScraping.reason)
        sheet.write(sheet_iterator, 26, amScraping.packagingLength)
        sheet.write(sheet_iterator, 27, amScraping.packagingWidth)
        sheet.write(sheet_iterator, 28, amScraping.packagingHeight)
        sheet.write(sheet_iterator, 29, amScraping.packagingWeight)
        sheet.write(sheet_iterator, 30, amScraping.isOversized)
        sheet_iterator+=1      
    
    
    if (runType in ('FULL','FULL-OTHER')):    
        sheet = wbk.add_sheet('Auto Favorites')
        
        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
        xstr = lambda s: s or ""
        
        sheet.write(0, 0, "Item ID", heading_xf)
        sheet.write(0, 1, "Brand", heading_xf)
        sheet.write(0, 2, "Product Name", heading_xf)
        sheet.write(0, 3, "Auto Favourite", heading_xf)
        sheet.write(0, 4, "Reason", heading_xf)
        
        sheet_iterator=1
        for autoFav in nowAutoFav:
            itemId = autoFav[0]
            reason = autoFav[1]
            it = Item.query.filter_by(id=itemId).one()
            sheet.write(sheet_iterator, 0, itemId)
            sheet.write(sheet_iterator, 1, it.brand)
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
            sheet.write(sheet_iterator, 3, "True")
            sheet.write(sheet_iterator, 4, reason)
            sheet_iterator+=1
        for prevFav in previousAutoFav:
            it = Item.query.filter_by(id=prevFav).one()
            sheet.write(sheet_iterator, 0, prevFav)
            sheet.write(sheet_iterator, 1, it.brand)
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
            sheet.write(sheet_iterator, 3, "False")
            sheet_iterator+=1
    
    filename = "/tmp/amazon-report-"+runType+" " + str(timestamp) + ".xls"
    wbk.save(filename)
    try:
        #EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Amazon Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
        EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["chandan.kumar@saholic.com","manoj.kumar@saholic.com","yukti.jain@saholic.com","ankush.dhingra@saholic.com","manoj.pal@saholic.com"], " Amazon Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], ["rajneesh.arora@saholic.com","anikendra.das@saholic.com","amit.gupta@saholic.com","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
    except Exception as e:
        print e
        print "Unable to send report.Trying with local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        #recipients = ["kshitij.sood@saholic.com"]
        msg = MIMEMultipart()
        msg['Subject'] = "Amazon Auto Pricing" + ' '+runType+' - ' + str(datetime.now())
        msg['From'] = sender
        recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','amit.gupta@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
        msg['To'] = ",".join(recipients)
        fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
        fileMsg.set_payload(file(filename).read())
        email.encoders.encode_base64(fileMsg)
        fileMsg.add_header('Content-Disposition','attachment;filename=amazon-auto-pricing.xls')
        msg.attach(fileMsg)
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."

def getVatRate(item_id,state,price):
    itemVatMaster = ItemVatMaster.query.filter(and_(ItemVatMaster.itemId==item_id, ItemVatMaster.stateId==state)).first()
    if itemVatMaster is None:
        d_item = Item.query.filter_by(id=item_id).first()
        if d_item is None:
            raise 
        else:
            vatMaster = CategoryVatMaster.query.filter(and_(CategoryVatMaster.categoryId==d_item.category, CategoryVatMaster.minVal<=price,  CategoryVatMaster.maxVal>=price,  CategoryVatMaster.stateId == state)).first()
        if vatMaster is None:
            raise
        else:
            vatRate = vatMaster.vatPercent
    else:
        vatRate = itemVatMaster.vatPercentage
    return vatRate

def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease,timestamp):
    if len(successfulAutoDecrease)==0 and len(successfulAutoIncrease)==0 :
        return
    xstr = lambda s: s or ""
    message="""<html>
            <body>
            <h3>Auto Decrease Items</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Amazon SKU</th>
            <th>Product Name</th>
            <th>Old Price</th>
            <th>New Price</th>
            <th>Subsidy</th>
            <th>Old Margin</th>
            <th>New Margin</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Inventory</th>
            <th>Sales History</th>
            <th>30 Days Sale</th>
            <th>Todays Sale</th>
            <th style="width:10%;">Todays Sale Snapshot Time</th>
            <th>Category</th>
            <th>isListed</th>
            <th>Price Feed Suppressed</th>
            </tr></thead>
            <tbody>"""
    for item in successfulAutoDecrease:
        it = Item.query.filter_by(id=item.item_id).one()
        amItem = Amazonlisted.get_by(itemId=item.item_id)
        isListed =""
        priceSuppress =""
        if item.warehouseLocation==1:
            if amItem.isFba:
                isListed = "Yes"
            else:
                isListed = "No"
            if amItem.suppressFbaPriceUpdate:
                priceSuppress = "Yes"
            else:   
                priceSuppress = "No"
        elif item.warehouseLocation==2:
            if amItem.isFbb:
                isListed = "Yes"
            else:
                isListed = "No"
            if amItem.suppressFbbPriceUpdate:
                priceSuppress = "Yes"
            else:
                priceSuppress = "No"
        elif item.warehouseLocation==3:
            if amItem.isFbg:
                isListed = "Yes"
            else:
                isListed = "No"
            if amItem.suppressFbgPriceUpdate:
                priceSuppress = "Yes"
            else:
                priceSuppress = "No"
        else:
            continue
        #vatRate = getNewVatRate(item.item_id,item.warehouseLocation,item.proposedSp)
        #oldMargin = item.ourSellingPrice - item.lowestPossibleSp
        oldMargin = getMargin(item)
        #newMargin = round(item.proposedSp - getNewLowestPossibleSp(item,12.36,vatRate))
        newMargin = getNewMargin(item,item.proposedSp)
        sku = ''
        if item.warehouseLocation==1:
            sku='FBA'+str(item.item_id)
        elif item.warehouseLocation==2:
            sku='FBB'+str(item.item_id)
        else:
            sku='FBG'+str(item.item_id)
        if amazonLongTermActivePromotions.has_key(sku):
            subsidy = (amazonLongTermActivePromotions.get(sku)).subsidy
        elif amazonShortTermActivePromotions.has_key(sku):
            subsidy = (amazonShortTermActivePromotions.get(sku)).subsidy
        else:
            subsidy = 0
        toDaysSale = getTodaysSale(sku)
        sale, count = get30DaysStats(sku)
        message+="""<tr>
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
                <td style="text-align:center">"""+sku+"""</td>
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
                <td style="text-align:center">"""+str(item.promoPrice)+"""</td>
                <td style="text-align:center">"""+str(math.ceil(item.proposedSp))+"""</td>
                <td style="text-align:center">"""+str(round(subsidy))+"""</td>
                <td style="text-align:center">"""+str(round(oldMargin))+" ("+str(round((oldMargin/item.promoPrice)*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/item.proposedSp)*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(item.commission)+" %"+"""</td>
                <td style="text-align:center">"""+str(item.returnProvision)+" %"+"""</td>
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
                <td style="text-align:center">"""+getOosString(saleMap.get(sku))+"""</td>
                <td style="text-align:center">"""+str(sale)+"""</td>
                <td style="text-align:center">"""+str(toDaysSale[1])+"""</td>
                <td style="text-align:center">"""+toDaysSale[0]+"""</td>
                <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(item.competitiveCategory))+"""</td>
                <td style="text-align:center">"""+isListed+"""</td>
                <td style="text-align:center">"""+priceSuppress+"""</td>
                </tr>"""
    message+="""</tbody></table><h3>Auto Increase Items</h3><table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Amazon SKU</th>
            <th>Product Name</th>
            <th>Old Price</th>
            <th>New Price</th>
            <th>Subsidy</th>
            <th>Old Margin</th>
            <th>New Margin</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Inventory</th>
            <th>Sales History</th>
            <th>30 Days Sale</th>
            <th>Todays Sale</th>
            <th style="width:10%;">Todays Sale Snapshot Time</th>
            <th>Category</th>
            <th>isListed</th>
            <th>Price Feed Suppressed</th>
            </tr></thead>
            <tbody>"""
    for item in successfulAutoIncrease:
        it = Item.query.filter_by(id=item.item_id).one()
        amItem = Amazonlisted.get_by(itemId=item.item_id)
        isListed =""
        priceSuppress =""
        if item.warehouseLocation==1:
            if amItem.isFba:
                isListed = "Yes"
            else:
                isListed = "No"
            if amItem.suppressFbaPriceUpdate:
                priceSuppress = "Yes"
            else:
                priceSuppress = "No"
        elif item.warehouseLocation==2:
            if amItem.isFbb:
                isListed = "Yes"
            else:
                isListed = "No"
            if amItem.suppressFbbPriceUpdate:
                priceSuppress = "Yes"
            else:
                priceSuppress = "No"
        elif item.warehouseLocation==3:
            if amItem.isFbg:
                isListed = "Yes"
            else:
                isListed = "No"
            if amItem.suppressFbgPriceUpdate:
                priceSuppress = "Yes"
            else:
                priceSuppress = "No"
        else:
            continue
        #vatRate = getNewVatRate(item.item_id,item.warehouseLocation,math.ceil(item.promoPrice+max(10,.01*item.promoPrice)))
        #oldMargin = item.ourSellingPrice - item.lowestPossibleSp
        oldMargin = getMargin(item)
        #newMargin = round(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)) - getNewLowestPossibleSp(item,12.36,vatRate))
        newMargin = getNewMargin(item,math.ceil(min(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)),item.proposedSp)))
        sku = ''
        if item.warehouseLocation==1:
            sku='FBA'+str(item.item_id)
        elif item.warehouseLocation==2:
            sku='FBB'+str(item.item_id)
        else:
            sku='FBG'+str(item.item_id)
        if amazonLongTermActivePromotions.has_key(sku):
            subsidy = (amazonLongTermActivePromotions.get(sku)).subsidy
        elif amazonShortTermActivePromotions.has_key(sku):
            subsidy = (amazonShortTermActivePromotions.get(sku)).subsidy
        else:
            subsidy = 0
        toDaysSale = getTodaysSale(sku)
        sale, count = get30DaysStats(sku)
        message+="""<tr>
                <td style="text-align:center">"""+str(item.item_id)+"""</td>
                <td style="text-align:center">"""+sku+"""</td>
                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
                <td style="text-align:center">"""+str(item.promoPrice)+"""</td>
                <td style="text-align:center">"""+str(math.ceil(min(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)),item.proposedSp)))+"""</td>
                <td style="text-align:center">"""+str(round(subsidy))+"""</td>
                <td style="text-align:center">"""+str(round((oldMargin),1))+" ("+str(round((oldMargin/item.promoPrice)*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/(math.ceil(min(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)),item.proposedSp))))*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(item.commission)+" %"+"""</td>
                <td style="text-align:center">"""+str(item.returnProvision)+" %"+"""</td>
                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
                <td style="text-align:center">"""+getOosString(saleMap.get(sku))+"""</td>
                <td style="text-align:center">"""+str(sale)+"""</td>
                <td style="text-align:center">"""+str(toDaysSale[1])+"""</td>
                <td style="text-align:center">"""+toDaysSale[0]+"""</td>
                <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(item.competitiveCategory))+"""</td>
                <td style="text-align:center">"""+isListed+"""</td>
                <td style="text-align:center">"""+priceSuppress+"""</td>
                </tr>"""
    
    message+="""</tbody></table><h3>Create Listing On Our Dashboard - Stock present in FC</h3><table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Amazon SKU</th>
            <th>Product Name</th></tr></thead>
            <tbody>
            """
    for sku in notListed:
        try:
            it = Item.query.filter_by(id=int(sku[3:])).one()
            productName = xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)
        except:
            it = None
            productName = "Not found"
        message+="""<tr>
        <td style="text-align:center">"""+sku[3:]+"""</td>
        <td style="text-align:center">"""+sku+"""</td>
        <td style="text-align:center">"""+productName+"""</td>
        </tr>"""
    
    exceptionListItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    message+="""</tbody></table><h3 style="color:red;">Exception Items</h3><table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Amazon SKU</th>
            <th>Product Name</th>
            <th>Selling Price</th>
            <th>Promo Price</th>
            <th>Inventory</th>
            <th>30 Days Sale</th>
            <th>Todays Sale</th>
            <th style="width:10%;">Todays Sale Snapshot Time</th>
            <th>Sales History</th>
            <th>Reason</th>
            </tr></thead>
            <tbody>
            """
    for exceptionListItem in exceptionListItems:
        amScraping =  exceptionListItem[0]
        if amScraping.exceptionType in (3,5):
            continue
        item = exceptionListItem[1]
        sku = ''
        if amScraping.warehouseLocation==1:
            sku='FBA'+str(item.id)
        elif amScraping.warehouseLocation==2:
            sku='FBB'+str(item.id)
        else:
            sku='FBG'+str(item.id)
        toDaysSale = getTodaysSale(sku)
        sale, count = get30DaysStats(sku)
        message+="""<tr>
                <td style="text-align:center">"""+str(item.id)+"""</td>
                <td style="text-align:center">"""+sku+"""</td>
                <td style="text-align:center">"""+xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color)+"""</td>
                <td style="text-align:center">"""+str(amScraping.ourSellingPrice)+"""</td>
                <td style="text-align:center">"""+str(amScraping.promoPrice)+"""</td>
                <td style="text-align:center">"""+str(amScraping.ourInventory)+"""</td>
                <td style="text-align:center">"""+str(sale)+"""</td>
                <td style="text-align:center">"""+str(toDaysSale[1])+"""</td>
                <td style="text-align:center">"""+toDaysSale[0]+"""</td>
                <td style="text-align:center">"""+getOosString(saleMap.get(sku))+"""</td>
                <td style="text-align:center">"""+amScraping.reason+"""</td>
                </tr>"""
    
    message+="""</tbody></table></body></html>"""
    print message
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
    msg = MIMEMultipart()
    msg['Subject'] = "Amazon Auto Pricing" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Amazon Auto Pricing" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send pricing mail.Lets try with local SMTP."
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."

def generateCategoryMap():
    global categoryMap
    result = session.query(Category.id,Category.display_name).all()
    for cat in result:
        categoryMap[cat.id] = cat.display_name

def sendAlertForNegativeMargins(timestamp):
    negativeMargins = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    if negativeMargins[0] is None or len(negativeMargins[0])==0:
        return
    xstr = lambda s: s or ""
    message="""<html>
            <body>
            <h3 style="color:red;">Amazon FC Negative Margins</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Amazon SKU</th>
            <th>Product Name</th>
            <th>Selling Price</th>
            <th>Promo Price</th>
            <th>Subsidy</th>
            <th>Lowest Possible SP</th>
            <th>WANLC</th>
            <th>Margin</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Inventory</th>
            <th>Sales History</th>
            </tr></thead>
            <tbody>"""
    for negativeMarginItems in negativeMargins:
        amScraping = negativeMarginItems[0]
        item = negativeMarginItems[1]
        if amScraping.warehouseLocation==1:
            sku='FBA'+str(amScraping.item_id)
        elif amScraping.warehouseLocation==2:
            sku='FBB'+str(amScraping.item_id)
        else:
            sku='FBG'+str(amScraping.item_id)
        if amazonLongTermActivePromotions.has_key(sku):
            subsidy = (amazonLongTermActivePromotions.get(sku)).subsidy
        elif amazonShortTermActivePromotions.has_key(sku):
            subsidy = (amazonShortTermActivePromotions.get(sku)).subsidy
        else:
            subsidy = 0
        message+="""<tr>
                <td style="text-align:center">"""+str(amScraping.item_id)+"""</td>
                <td style="text-align:center">"""+sku+"""</td>
                <td style="text-align:center">"""+xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color)+"""</td>
                <td style="text-align:center">"""+str(amScraping.ourSellingPrice)+"""</td>
                <td style="text-align:center">"""+str(amScraping.promoPrice)+"""</td>
                <td style="text-align:center">"""+str(subsidy)+"""</td>
                <td style="text-align:center">"""+str(amScraping.lowestPossibleSp)+"""</td>
                <td style="text-align:center">"""+str(amScraping.wanlc)+"""</td>
                <td style="text-align:center">"""+str(round(getMargin(amScraping)))+" ("+str(round((getMargin(amScraping)/amScraping.promoPrice)*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(amScraping.commission)+" %"+"""</td>
                <td style="text-align:center">"""+str(amScraping.returnProvision)+" %"+"""</td>
                <td style="text-align:center">"""+str(amScraping.ourInventory)+"""</td>
                <td style="text-align:center">"""+getOosString(saleMap.get(sku))+"""</td>
                </tr>"""
    message+="""</tbody></table></body></html>"""
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
    msg = MIMEMultipart()
    msg['Subject'] = "Amazon Negative Margin" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Amazon Negative Margin" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send Amazon Negative margin mail.Lets try local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."
            
def sendAlertForCantCompete(timestamp):
    cantCompeteItemsList = session.query(AmazonScrapingHistory).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    print cantCompeteItemsList
    if cantCompeteItemsList is None or len(cantCompeteItemsList)==0:
        return
    xstr = lambda s: s or ""
    message="""<html>
            <body>
            <h3 style="color:red;">Amazon FC Cant Compete Items</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Amazon SKU</th>
            <th>Product Name</th>
            <th>Selling Price</th>
            <th>Promo Price</th>
            <th>Subsidy</th>
            <th>Lowest Possible SP</th>
            <th>WANLC</th>
            <th>Margin</th>
            <th>Commission %</th>
            <th>Return Provision %</th>
            <th>Competitive Price</th>
            <th>Proposed SP</th>
            <th>Inventory</th>
            <th>Sales History</th>
            </tr></thead>
            <tbody>"""
    cantCompeteItems = sorted(list(cantCompeteItemsList), key=lambda x: x.ourInventory, reverse=True)
    for cantCompeteItem in cantCompeteItems:
        amScraping = cantCompeteItem
        item = Item.query.filter_by(id=amScraping.item_id).one()
        if amScraping.warehouseLocation==1:
            sku='FBA'+str(amScraping.item_id)
        elif amScraping.warehouseLocation==2:
            sku='FBB'+str(amScraping.item_id)
        else:
            sku='FBG'+str(amScraping.item_id)
        if amazonLongTermActivePromotions.has_key(sku):
            subsidy = (amazonLongTermActivePromotions.get(sku)).subsidy
        elif amazonShortTermActivePromotions.has_key(sku):
            subsidy = (amazonShortTermActivePromotions.get(sku)).subsidy
        else:
            subsidy = 0
        message+="""<tr>
                <td style="text-align:center">"""+str(amScraping.item_id)+"""</td>
                <td style="text-align:center">"""+sku+"""</td>
                <td style="text-align:center">"""+xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color)+"""</td>
                <td style="text-align:center">"""+str(amScraping.ourSellingPrice)+"""</td>
                <td style="text-align:center">"""+str(amScraping.promoPrice)+"""</td>
                <td style="text-align:center">"""+str(subsidy)+"""</td>
                <td style="text-align:center">"""+str(amScraping.lowestPossibleSp)+"""</td>
                <td style="text-align:center">"""+str(amScraping.wanlc)+"""</td>
                <td style="text-align:center">"""+str(round(getMargin(amScraping)))+" ("+str(round((getMargin(amScraping)/amScraping.promoPrice)*100,1))+"%)"+"""</td>
                <td style="text-align:center">"""+str(amScraping.commission)+" %"+"""</td>
                <td style="text-align:center">"""+str(amScraping.returnProvision)+" %"+"""</td>
                <td style="text-align:center">"""+str(amScraping.competitivePrice)+" %"+"""</td>
                <td style="text-align:center">"""+str(amScraping.proposedSp)+" %"+"""</td>
                <td style="text-align:center">"""+str(amScraping.ourInventory)+"""</td>
                <td style="text-align:center">"""+getOosString(saleMap.get(sku))+"""</td>
                </tr>"""
    message+="""</tbody></table></body></html>"""
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
    msg = MIMEMultipart()
    msg['Subject'] = "Amazon Cant Compete Items" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Amazon Cant Compete Items" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    try:
        mailServer.login("build@shop2020.in", "cafe@nes")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send Amazon Cant Compete Items mail.Lets try local SMTP"
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'build@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."

def commitPricing(successfulAutoDecrease,successfulAutoIncrease):
    if len(successfulAutoDecrease)==0 and len(successfulAutoIncrease)==0 :
        return
    for item in successfulAutoDecrease:
        amItem = Amazonlisted.get_by(itemId=item.item_id)
        if item.warehouseLocation==1:
            if item.isPromotion:
                amItem.fbaPromoPrice = math.ceil(item.proposedSp)
            else:
                amItem.fbaPrice = math.ceil(item.proposedSp)
            amItem.fbaPriceLastUpdatedOn = datetime.now()
        elif item.warehouseLocation==2:
            if item.isPromotion:
                amItem.fbbPromoPrice = math.ceil(item.proposedSp)
            else:
                amItem.fbbPrice = math.ceil(item.proposedSp)
            amItem.fbbPriceLastUpdatedOn = datetime.now()
        elif item.warehouseLocation==3:
            if item.isPromotion:
                amItem.fbgPromoPrice = math.ceil(item.proposedSp)
            else:
                amItem.fbgPrice = math.ceil(item.proposedSp)
            amItem.fbgPriceLastUpdatedOn = datetime.now()
        else:
            continue
    session.commit()
    for item in successfulAutoIncrease:
        amItem = Amazonlisted.get_by(itemId=item.item_id)
        if item.warehouseLocation==1:
            if item.isPromotion:
                amItem.fbaPromoPrice = math.ceil(min(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)),item.proposedSp))
            else:
                amItem.fbaPrice = math.ceil(min(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)),item.proposedSp))
            amItem.fbaPriceLastUpdatedOn = datetime.now()
        elif item.warehouseLocation==2:
            if item.isPromotion:
                amItem.fbbPromoPrice = math.ceil(min(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)),item.proposedSp))
            else:
                amItem.fbbPrice = math.ceil(min(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)),item.proposedSp))
            amItem.fbbPriceLastUpdatedOn = datetime.now()
        elif item.warehouseLocation==3:
            if item.isPromotion:
                amItem.fbgPromoPrice = math.ceil(min(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)),item.proposedSp))
            else:
                amItem.fbgPrice = math.ceil(min(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)),item.proposedSp))
            amItem.fbgPriceLastUpdatedOn = datetime.now()
        else:
            continue
    session.commit()
    
def fixListingAnomaly():
    for sku in toList:
        if sku.startswith('FBA'):
            am_listed = Amazonlisted.get_by(itemId=sku[3:])
            am_listed.isFba = True
        elif sku.startswith('FBB'):
            am_listed = Amazonlisted.get_by(itemId=sku[3:])
            am_listed.isFbb = True
        elif sku.startswith('FBG'):
            am_listed = Amazonlisted.get_by(itemId=sku[3:])
            am_listed.isFbg = True
        else:
            pass
    session.commit()

def checkOverSize(d_amazon_listed):
    try:
        if d_amazon_listed.packagingWeight * .453592 > 12 or d_amazon_listed.packagingLength > 20 or d_amazon_listed.packagingWidth > 16 or d_amazon_listed.packagingHeight > 10:
            return True
        else:
            return False
    except:
        return False 

def main():
    parser = optparse.OptionParser()
    parser.add_option("-t", "--type", dest="runType",
                   default="FULL", type="string",
                   help="Run type FULL or FAVOURITE or FULL-OTHER")
    (options, args) = parser.parse_args()
    if options.runType not in ('FULL','FAVOURITE','FULL-OTHER'):
        print "Run type argument illegal."
        sys.exit(1)
    time.sleep(5)
    timestamp = datetime.now()
    generateCategoryMap()
    fetchFbaSale()
    itemInfo = populateStuff(timestamp,options.runType)
    try:
        fixListingAnomaly()
    except:
        pass
    itemsToPopulate = 0
    toSync = 0
    lenItems = len(itemInfo)
    while(toSync < lenItems):
        oldSync = toSync
        if lenItems >= 20:
            toSync = 20
        else:
            toSync = lenItems - oldSync
        getPriceAndAsin(itemInfo[oldSync:toSync+oldSync])
        toSync = oldSync + toSync
        
    while (len(itemInfo)>0):
        if len(itemInfo) >= 20:
            itemsToPopulate = 20
        else:
            itemsToPopulate = len(itemInfo)
        exceptionList, negativeMargin, cheapest, amongCheapestAndCanCompete, canCompete, almostCompete, cantCompete = decideCategory(itemInfo[0:itemsToPopulate],timestamp)
        itemInfo[0:itemsToPopulate] = []
        commitExceptionList(exceptionList,timestamp,options.runType)
        commitNegativeMargin(negativeMargin,timestamp,options.runType)
        commitCheapest(cheapest,timestamp,options.runType)
        commitAmongCheapestAndCanCompete(amongCheapestAndCanCompete,timestamp,options.runType)
        commitCanCompete(canCompete,timestamp,options.runType)
        commitAlmostCompete(almostCompete,timestamp,options.runType)
        commitCantCompete(cantCompete, timestamp,options.runType)
        exceptionList[:], negativeMargin[:], cheapest[:], amongCheapestAndCanCompete[:], canCompete[:], almostCompete[:], cantCompete[:] =[],[],[],[],[],[],[]
    autoDecreaseItems = fetchItemsForAutoDecrease(timestamp)
    autoIncreaseItems = fetchItemsForAutoIncrease(timestamp)
    previousAutoFav, nowAutoFav = markAutoFavourites(timestamp)
    fetchMonthSale()
    populateHourlySnapshot()
    writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,options.runType)
    commitPricing(autoDecreaseItems,autoIncreaseItems)
    sendAutoPricingMail(autoDecreaseItems,autoIncreaseItems, timestamp)
    if options.runType == 'FULL-OTHER':
        sendAlertForNegativeMargins(timestamp)
        sendAlertForCantCompete(timestamp)
if __name__=='__main__':
    main()