WebSVN – SmartDukaan – Diff – /trunk/PyProj/src/shop2020/model/v1/catalog/script/AmazonHistoryPopulator.py



amazonShortTermActivePromotions = {}
wpiTodayExpiry = {}
notListed = []
saleMap = {}
categoryMap = {}
exceptionMap = {1:'WANLC is 0',2:'Unable to fetch our price',3:'No other seller or Unable to fetch competitive pricing',
                4:'Promo misconfigured',5:'Unable to calculate competitive pricing',6:'Vat not available'}
DataService.initialize(db_hostname=host)
 
amScraper = AmazonAsyncScraper.Products("AKIAII3SGRXBJDPCHSGQ", "B92xTbNBTYygbGs98w01nFQUhbec1pNCkCsKVfpg", "AF6E3O0VE0X4D")
 
class __AmazonItemInfo:
    
    def __init__(self, asin, nlc, courierCost, sku, product_group, brand, model_name, model_number, color, weight, parent_category, risky, vatRate, runType, parent_category_name, sourcePercentage, ourInventory, state_id, otherCost, exceptionType):
        self.asin = asin
        self.nlc = nlc
        self.courierCost = courierCost
        self.sku = sku
        self.product_group = product_group

        self.parent_category_name = parent_category_name
        self.sourcePercentage = sourcePercentage
        self.ourInventory = ourInventory
        self.state_id = state_id
        self.otherCost = otherCost
        self.exceptionType = exceptionType 
 
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, \

        try:
            daysOfStock = (float(autoDecrementItem.ourInventory))/autoDecrementItem.avgSale
        except:
            daysOfStock = float("inf")
        if autoDecrementItem.competitiveCategory == CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE:
            if daysOfStock < 5:
                markReasonForItem(autoDecrementItem,'Days of stock less than 5',Decision.AUTO_DECREMENT_FAILED)
                continue
        
        if autoDecrementItem.competitiveCategory == CompetitionCategory.COMPETITIVE and not autoDecrementItem.isPromotion:
            if autoDecrementItem.parentCategoryId in [10006,10009,11001]:
                if daysOfStock < 1 :

            state_id = 2
        else:
            continue
        cc = computeCourierCost(it.weight)
            
        amazonItemInfo = __AmazonItemInfo(None, wanlc,cc, sku, it.product_group, it.brand, it.model_name, it.model_number, it.color, it.weight, category.parent_category_id, it.risky, None, runType, parent_category.display_name,sourcePercentage,fbaInventoryItem.availability,state_id,d_amazon_listed.otherCost,None)
        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 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)
            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')

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

        
        competitivePrice = decideCompetitvePricing(amDetails,val.ourInventory,timestamp)
        amDetails.competitivePrice = competitivePrice
        if amDetails.competitivePrice==0.0 and amDetails.ourRank > 1:
            temp = []
            val.exceptionType = 5
            temp.append(val)
            temp.append("Unable to calculate competitive pricing")
            exceptionList.append(temp)
            continue
        try:

                val.vatRate = itemVatMaster.vatPercentage
                print "vat fetched"
        except:
            print "vat exception"
            temp = []
            val.exceptionType = 6
            temp.append(val)
            temp.append("Vat not available")
            exceptionList.append(temp)
            continue
        

    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.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.timestamp = timestamp
    session.commit()
 

    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 12.36
 
def getClosingFee(data):

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

    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,getNewVatRate(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 getCategory(data):
    return categoryMap[data.category]
 
def writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,runType):

        else:
            sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))
        sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))
        if amScraping.decision is None:
            sheet.write(sheet_iterator, 42, 'Auto Pricing Inactive')
            sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))
            sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))
            sheet_iterator+=1
            continue
        sheet.write(sheet_iterator, 42, Decision._VALUES_TO_NAMES.get(amScraping.decision))
        sheet.write(sheet_iterator, 43, amScraping.reason)
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
            sheet.write(sheet_iterator, 44, math.ceil(amScraping.proposedSp))
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
            sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
        sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))
        sheet.write(sheet_iterator, 46, 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:

        else:
            sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))
        sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))
        if amScraping.decision is None:
            sheet.write(sheet_iterator, 42, 'Auto Pricing Inactive')
            sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))
            sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))
            sheet_iterator+=1
            continue
        sheet.write(sheet_iterator, 42, Decision._VALUES_TO_NAMES.get(amScraping.decision))
        sheet.write(sheet_iterator, 43, amScraping.reason)
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
            sheet.write(sheet_iterator, 44, math.ceil(amScraping.proposedSp))
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
            sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
        sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))
        sheet.write(sheet_iterator, 46, 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:

        else:
            sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))
        sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))
        if amScraping.decision is None:
            sheet.write(sheet_iterator, 42, 'Auto Pricing Inactive')
            sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))
            sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))
            sheet_iterator+=1
            continue
        sheet.write(sheet_iterator, 42, Decision._VALUES_TO_NAMES.get(amScraping.decision))
        sheet.write(sheet_iterator, 43, amScraping.reason)
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
            sheet.write(sheet_iterator, 44, math.ceil(amScraping.proposedSp))
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
            sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
        sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))
        sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))
        sheet_iterator+=1
    
#    sheet = wbk.add_sheet('Cheapest')
#    xstr = lambda s: s or ""

        else:
            sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))
        sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))
        if amScraping.decision is None:
            sheet.write(sheet_iterator, 42, 'Auto Pricing Inactive')
            sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))
            sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))
            sheet_iterator+=1
            continue
        sheet.write(sheet_iterator, 42, Decision._VALUES_TO_NAMES.get(amScraping.decision))
        sheet.write(sheet_iterator, 43, amScraping.reason)
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":
            sheet.write(sheet_iterator, 44, math.ceil(amScraping.proposedSp))
        if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":
            sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))
        sheet.write(sheet_iterator, 45, getNewMargin(amScraping,math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice))))
        sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))
        sheet_iterator+=1
    
#    sheet = wbk.add_sheet('Negative Margin')
#    xstr = lambda s: s or ""

                priceSuppress = "Yes"
            else:
                priceSuppress = "No"
        else:
            pass
        #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)
        else:
            sku='FBB'+str(item.item_id)  

                priceSuppress = "Yes"
            else:
                priceSuppress = "No"
        else:
            pass
        #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(item.promoPrice+max(10,.01*item.promoPrice)))
        sku = ''
        if item.warehouseLocation==1:
            sku='FBA'+str(item.item_id)
        else:
            sku='FBB'+str(item.item_id)  

                <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>"""

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

Rev 12721	Rev 12736
Line 39...	Line 39...
39	`amazonShortTermActivePromotions = {}`	39	`amazonShortTermActivePromotions = {}`
40	`wpiTodayExpiry = {}`	40	`wpiTodayExpiry = {}`
41	`notListed = []`	41	`notListed = []`
42	`saleMap = {}`	42	`saleMap = {}`
43	`categoryMap = {}`	43	`categoryMap = {}`
-		44	`exceptionMap = {1:'WANLC is 0',2:'Unable to fetch our price',3:'No other seller or Unable to fetch competitive pricing',`
-		45	`4:'Promo misconfigured',5:'Unable to calculate competitive pricing',6:'Vat not available'}`
44	`DataService.initialize(db_hostname=host)`	46	`DataService.initialize(db_hostname=host)`
45		47
46	`amScraper = AmazonAsyncScraper.Products("AKIAII3SGRXBJDPCHSGQ", "B92xTbNBTYygbGs98w01nFQUhbec1pNCkCsKVfpg", "AF6E3O0VE0X4D")`	48	`amScraper = AmazonAsyncScraper.Products("AKIAII3SGRXBJDPCHSGQ", "B92xTbNBTYygbGs98w01nFQUhbec1pNCkCsKVfpg", "AF6E3O0VE0X4D")`
47		49
48	`class __AmazonItemInfo:`	50	`class __AmazonItemInfo:`
49		51
50	`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):`	52	`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):`
51	`self.asin = asin`	53	`self.asin = asin`
52	`self.nlc = nlc`	54	`self.nlc = nlc`
53	`self.courierCost = courierCost`	55	`self.courierCost = courierCost`
54	`self.sku = sku`	56	`self.sku = sku`
55	`self.product_group = product_group`	57	`self.product_group = product_group`
Line 65...	Line 67...
65	`self.parent_category_name = parent_category_name`	67	`self.parent_category_name = parent_category_name`
66	`self.sourcePercentage = sourcePercentage`	68	`self.sourcePercentage = sourcePercentage`
67	`self.ourInventory = ourInventory`	69	`self.ourInventory = ourInventory`
68	`self.state_id = state_id`	70	`self.state_id = state_id`
69	`self.otherCost = otherCost`	71	`self.otherCost = otherCost`
-		72	`self.exceptionType = exceptionType`
70		73
71	`class __AmazonDetails:`	74	`class __AmazonDetails:`
72	`def __init__(self, sku, ourSp, ourRank, lowestSellerName,lowestSellerSp,secondLowestSellerName, secondLowestSellerSp, thirdLowestSellerName, thirdLowestSellerSp, totalSeller, multipleListings, \`	75	`def __init__(self, sku, ourSp, ourRank, lowestSellerName,lowestSellerSp,secondLowestSellerName, secondLowestSellerSp, thirdLowestSellerName, thirdLowestSellerSp, totalSeller, multipleListings, \`
73	`promoPrice, isPromotion, lowestSellerShippingTime, lowestSellerRating, secondLowestSellerShippingTime, secondLowestSellerRating, thirdLowestSellerShippingTime , \`	76	`promoPrice, isPromotion, lowestSellerShippingTime, lowestSellerRating, secondLowestSellerShippingTime, secondLowestSellerRating, thirdLowestSellerShippingTime , \`
74	`thirdLowestSellerRating, lowestSellerType, secondLowestSellerType, thirdLowestSellerType, lowestMfnIgnoredOffer, lowestMfnOffer, lowestFbaOffer, \`	77	`thirdLowestSellerRating, lowestSellerType, secondLowestSellerType, thirdLowestSellerType, lowestMfnIgnoredOffer, lowestMfnOffer, lowestFbaOffer, \`
Line 145...	Line 148...
145	`try:`	148	`try:`
146	`daysOfStock = (float(autoDecrementItem.ourInventory))/autoDecrementItem.avgSale`	149	`daysOfStock = (float(autoDecrementItem.ourInventory))/autoDecrementItem.avgSale`
147	`except:`	150	`except:`
148	`daysOfStock = float("inf")`	151	`daysOfStock = float("inf")`
149	`if autoDecrementItem.competitiveCategory == CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE:`	152	`if autoDecrementItem.competitiveCategory == CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE:`
150	`if daysOfStock < 20:`	153	`if daysOfStock < 5:`
151	`markReasonForItem(autoDecrementItem,'Days of stock less than 20',Decision.AUTO_DECREMENT_FAILED)`	154	`markReasonForItem(autoDecrementItem,'Days of stock less than 5',Decision.AUTO_DECREMENT_FAILED)`
152	`continue`	155	`continue`
153		156
154	`if autoDecrementItem.competitiveCategory == CompetitionCategory.COMPETITIVE and not autoDecrementItem.isPromotion:`	157	`if autoDecrementItem.competitiveCategory == CompetitionCategory.COMPETITIVE and not autoDecrementItem.isPromotion:`
155	`if autoDecrementItem.parentCategoryId in [10006,10009,11001]:`	158	`if autoDecrementItem.parentCategoryId in [10006,10009,11001]:`
156	`if daysOfStock < 1 :`	159	`if daysOfStock < 1 :`
Line 455...	Line 458...
455	`state_id = 2`	458	`state_id = 2`
456	`else:`	459	`else:`
457	`continue`	460	`continue`
458	`cc = computeCourierCost(it.weight)`	461	`cc = computeCourierCost(it.weight)`
459		462
460	`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)`	463	`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)`
461	`itemInfo.append(amazonItemInfo)`	464	`itemInfo.append(amazonItemInfo)`
462	`#amPromotions = AmazonPromotion.query.filter(AmazonPromotion.startDate<=time).filter(AmazonPromotion.endDate>=time).filter(AmazonPromotion.promotionType==AmazonPromotionType.LONGTERM).filter(AmazonPromotion.promotionActive==True) \`	465	`#amPromotions = AmazonPromotion.query.filter(AmazonPromotion.startDate<=time).filter(AmazonPromotion.endDate>=time).filter(AmazonPromotion.promotionType==AmazonPromotionType.LONGTERM).filter(AmazonPromotion.promotionActive==True) \`
463	`#.group_by(AmazonPromotion.sku).order_by(desc(AmazonPromotion.addedOn)).all()`	466	`#.group_by(AmazonPromotion.sku).order_by(desc(AmazonPromotion.addedOn)).all()`
464	`amPromotions = AmazonPromotion.query.filter(AmazonPromotion.startDate<=time).filter(AmazonPromotion.endDate>=time).filter(AmazonPromotion.promotionType==AmazonPromotionType.LONGTERM).filter(AmazonPromotion.promotionActive==True) \`	467	`amPromotions = AmazonPromotion.query.filter(AmazonPromotion.startDate<=time).filter(AmazonPromotion.endDate>=time).filter(AmazonPromotion.promotionType==AmazonPromotionType.LONGTERM).filter(AmazonPromotion.promotionActive==True) \`
465	`.order_by(desc(AmazonPromotion.addedOn)).all()`	468	`.order_by(desc(AmazonPromotion.addedOn)).all()`
Line 519...	Line 522...
519	`for val in itemInfo:`	522	`for val in itemInfo:`
520	`scrapInfo = aggResponse.get(val.sku)`	523	`scrapInfo = aggResponse.get(val.sku)`
521	`competitvePricingInfo = otherInfo.get(val.sku)`	524	`competitvePricingInfo = otherInfo.get(val.sku)`
522	`if scrapInfo is None or len(scrapInfo)==0 or val.nlc==0 or len(ourPricingForSku.get(val.sku).keys())==0:`	525	`if scrapInfo is None or len(scrapInfo)==0 or val.nlc==0 or len(ourPricingForSku.get(val.sku).keys())==0:`
523	`temp = []`	526	`temp = []`
524	`temp.append(val)`	-
525	`if val.nlc==0 or val.nlc is None:`	527	`if val.nlc==0 or val.nlc is None:`
526	`print "WANLC is 0"`	528	`print "WANLC is 0"`
-		529	`val.exceptionType = 1`
527	`temp.append("WANLC is 0")`	530	`#temp.append("WANLC is 0")`
528	`elif ourPricingForSku.get(val.sku) is None or len(ourPricingForSku.get(val.sku).keys())==0:`	531	`elif ourPricingForSku.get(val.sku) is None or len(ourPricingForSku.get(val.sku).keys())==0:`
529	`print "Unable to fetch our price"`	532	`print "Unable to fetch our price"`
-		533	`val.exceptionType = 2`
530	`temp.append("Unable to fetch our price")`	534	`#temp.append("Unable to fetch our price")`
531	`else:`	535	`else:`
532	`print "No other seller or Unable to fetch competitive pricing"`	536	`print "No other seller or Unable to fetch competitive pricing"`
-		537	`val.exceptionType = 3`
533	`temp.append("No other seller or Unable to fetch competitive pricing")`	538	`#temp.append("No other seller or Unable to fetch competitive pricing")`
-		539	`temp.append(val)`
534	`exceptionList.append(temp)`	540	`exceptionList.append(temp)`
535	`continue`	541	`continue`
536	`val.ourSp = ourPricingForSku.get(val.sku).get('sellingPrice')`	542	`val.ourSp = ourPricingForSku.get(val.sku).get('sellingPrice')`
537	`val.promoPrice = ourPricingForSku.get(val.sku).get('promoPrice')`	543	`val.promoPrice = ourPricingForSku.get(val.sku).get('promoPrice')`
538	`val.isPromo = ourPricingForSku.get(val.sku).get('promotion')`	544	`val.isPromo = ourPricingForSku.get(val.sku).get('promotion')`
Line 545...	Line 551...
545	`sku = val.sku`	551	`sku = val.sku`
546	`multipleListings = False`	552	`multipleListings = False`
547	`ourSkuDetails = ourPricingForSku.get(val.sku)`	553	`ourSkuDetails = ourPricingForSku.get(val.sku)`
548	`if (ourSkuDetails.get('promotion')!=(amazonLongTermActivePromotions.has_key(val.sku) or amazonShortTermActivePromotions.has_key(val.sku))):`	554	`if (ourSkuDetails.get('promotion')!=(amazonLongTermActivePromotions.has_key(val.sku) or amazonShortTermActivePromotions.has_key(val.sku))):`
549	`temp = []`	555	`temp = []`
-		556	`val.exceptionType = 4`
550	`temp.append(val)`	557	`temp.append(val)`
551	`print "promo misconfigured"`	558	`print "promo misconfigured"`
552	`temp.append("Promo misconfigured")`	559	`#temp.append("Promo misconfigured")`
553	`exceptionList.append(temp)`	560	`exceptionList.append(temp)`
554	`continue`	561	`continue`
555		562
556	`scrapInfo.append(ourSkuDetails)`	563	`scrapInfo.append(ourSkuDetails)`
557	`sortedScrapInfo = sorted(scrapInfo, key=itemgetter('promoPrice','notOurSku'))`	564	`sortedScrapInfo = sorted(scrapInfo, key=itemgetter('promoPrice','notOurSku'))`
Line 590...	Line 597...
590		597
591	`competitivePrice = decideCompetitvePricing(amDetails,val.ourInventory,timestamp)`	598	`competitivePrice = decideCompetitvePricing(amDetails,val.ourInventory,timestamp)`
592	`amDetails.competitivePrice = competitivePrice`	599	`amDetails.competitivePrice = competitivePrice`
593	`if amDetails.competitivePrice==0.0 and amDetails.ourRank > 1:`	600	`if amDetails.competitivePrice==0.0 and amDetails.ourRank > 1:`
594	`temp = []`	601	`temp = []`
-		602	`val.exceptionType = 5`
595	`temp.append(val)`	603	`temp.append(val)`
596	`temp.append("Unable to calculate competitive pricing")`	604	`temp.append("Unable to calculate competitive pricing")`
597	`exceptionList.append(temp)`	605	`exceptionList.append(temp)`
598	`continue`	606	`continue`
599	`try:`	607	`try:`
Line 614...	Line 622...
614	`val.vatRate = itemVatMaster.vatPercentage`	622	`val.vatRate = itemVatMaster.vatPercentage`
615	`print "vat fetched"`	623	`print "vat fetched"`
616	`except:`	624	`except:`
617	`print "vat exception"`	625	`print "vat exception"`
618	`temp = []`	626	`temp = []`
-		627	`val.exceptionType = 6`
619	`temp.append(val)`	628	`temp.append(val)`
620	`temp.append("Vat not available")`	629	`temp.append("Vat not available")`
621	`exceptionList.append(temp)`	630	`exceptionList.append(temp)`
622	`continue`	631	`continue`
623		632
Line 778...	Line 787...
778	`return round(targetTp,2)`	787	`return round(targetTp,2)`
779		788
780	`def commitExceptionList(exceptionList,timestamp,runType):`	789	`def commitExceptionList(exceptionList,timestamp,runType):`
781	`for exceptionItem in exceptionList:`	790	`for exceptionItem in exceptionList:`
782	`val = exceptionItem[0]`	791	`val = exceptionItem[0]`
783	`reason = exceptionItem[1]`	-
784	`amazonScrapingHistory = AmazonScrapingHistory()`	792	`amazonScrapingHistory = AmazonScrapingHistory()`
785	`amazonScrapingHistory.item_id = val.sku[3:]`	793	`amazonScrapingHistory.item_id = val.sku[3:]`
786	`amazonScrapingHistory.warehouseLocation = val.state_id`	794	`amazonScrapingHistory.warehouseLocation = val.state_id`
787	`amazonScrapingHistory.parentCategoryId = val.parent_category`	795	`amazonScrapingHistory.parentCategoryId = val.parent_category`
788	`amazonScrapingHistory.ourSellingPrice = val.ourSp`	796	`amazonScrapingHistory.ourSellingPrice = val.ourSp`
789	`amazonScrapingHistory.promoPrice = val.promoPrice`	797	`amazonScrapingHistory.promoPrice = val.promoPrice`
790	`amazonScrapingHistory.reason = reason`	798	`amazonScrapingHistory.reason = exceptionMap.get(val.exceptionType)`
791	`amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)`	799	`amazonScrapingHistory.runType = RunType._NAMES_TO_VALUES.get(runType)`
792	`amazonScrapingHistory.competitiveCategory = CompetitionCategory.EXCEPTION`	800	`amazonScrapingHistory.competitiveCategory = CompetitionCategory.EXCEPTION`
793	`amazonScrapingHistory.timestamp = timestamp`	801	`amazonScrapingHistory.timestamp = timestamp`
794	`session.commit()`	802	`session.commit()`
795		803
Line 1296...	Line 1304...
1296	`return 15`	1304	`return 15`
1297		1305
1298	`def getReturnCost(data):`	1306	`def getReturnCost(data):`
1299	`return round(data.returnProvision * data.promoPrice/100)`	1307	`return round(data.returnProvision * data.promoPrice/100)`
1300		1308
-		1309	`def getNewReturnCost(data,proposedSp):`
-		1310	`return round(data.returnProvision * proposedSp/100)`
-		1311
1301	`def getServiceTax(data):`	1312	`def getServiceTax(data):`
1302	`#TODO : Get service tax from marketplaceitems table`	1313	`#TODO : Get service tax from marketplaceitems table`
1303	`return 12.36`	1314	`return 12.36`
1304		1315
1305	`def getClosingFee(data):`	1316	`def getClosingFee(data):`
Line 1307...	Line 1318...
1307	`return myClosingFee*(1+getServiceTax(data)/100)`	1318	`return myClosingFee*(1+getServiceTax(data)/100)`
1308		1319
1309	`def getCommission(data):`	1320	`def getCommission(data):`
1310	`return (data.commission * data.promoPrice/100)*(1+getServiceTax(data)/100)`	1321	`return (data.commission * data.promoPrice/100)*(1+getServiceTax(data)/100)`
1311		1322
-		1323	`def getNewCommission(data,proposedSp):`
-		1324	`return (data.commission * proposedSp/100)*(1+getServiceTax(data)/100)`
-		1325
1312	`def getCourierCost(data):`	1326	`def getCourierCost(data):`
1313	`return data.courierCost*(1+getServiceTax(data)/100)`	1327	`return data.courierCost*(1+getServiceTax(data)/100)`
1314		1328
1315	`def getCostToAmazon(data):`	1329	`def getCostToAmazon(data):`
1316	`myCostToAmazon = round(data.promoPricedata.commission/100(1+getServiceTax(data)/100)+getCourierCost(data)*(1+getServiceTax(data)/100))`	1330	`myCostToAmazon = round(data.promoPricedata.commission/100(1+getServiceTax(data)/100)+getCourierCost(data)*(1+getServiceTax(data)/100))`
Line 1364...	Line 1378...
1364	`print 'promo price ',amScraping.promoPrice`	1378	`print 'promo price ',amScraping.promoPrice`
1365	`#print 'mySubsidy ',mySubsidy`	1379	`#print 'mySubsidy ',mySubsidy`
1366	`print 'wanlc ',amScraping.wanlc`	1380	`print 'wanlc ',amScraping.wanlc`
1367	`print 'courier cost ',getCourierCost(amScraping)`	1381	`print 'courier cost ',getCourierCost(amScraping)`
1368	`print 'closing fee ',getClosingFee(amScraping)`	1382	`print 'closing fee ',getClosingFee(amScraping)`
1369	`print 'commission ',getCommission(amScraping)`	1383	`print 'commission ',getCommission(amScraping) #1`
1370	`print 'packaging ',getPackagingCost(amScraping)`	1384	`print 'packaging ',getPackagingCost(amScraping)`
1371	`print 'vat ',getVat(amScraping)`	1385	`print 'vat ',getVat(amScraping) #2`
1372	`print 'return cost ',getReturnCost(amScraping)`	1386	`print 'return cost ',getReturnCost(amScraping) #3`
1373	`print 'other cost ',amScraping.otherCost`	1387	`print 'other cost ',amScraping.otherCost`
1374	`print 'cost to amazon ',getCostToAmazon(amScraping)`	1388	`print 'cost to amazon ',getCostToAmazon(amScraping) #4`
1375	`myCosts = amScraping.wanlc + getCourierCost(amScraping) + getClosingFee(amScraping) + getCommission(amScraping) + getPackagingCost(amScraping) + getVat(amScraping) + getReturnCost(amScraping) + amScraping.otherCost`	1389	`myCosts = amScraping.wanlc + getCourierCost(amScraping) + getClosingFee(amScraping) + getCommission(amScraping) + getPackagingCost(amScraping) + getVat(amScraping) + getReturnCost(amScraping) + amScraping.otherCost`
1376	`margin = amScraping.promoPrice - myCosts + amScraping.subsidy`	1390	`margin = amScraping.promoPrice - myCosts + amScraping.subsidy`
1377	`print 'margin for ',amScraping.item_id,' is ',margin`	1391	`print 'margin for ',amScraping.item_id,' is ',margin`
1378	`return round(margin)`	1392	`return round(margin)`
1379		1393
-		1394	`def getNewMargin(amScraping,proposedSp):`
-		1395	`myCosts = amScraping.wanlc + getCourierCost(amScraping) + getClosingFee(amScraping) + getNewCommission(amScraping,proposedSp) + getPackagingCost(amScraping) + getNewVat(amScraping,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,proposedSp),proposedSp) + getNewReturnCost(amScraping,proposedSp) + amScraping.otherCost`
-		1396	`margin = proposedSp - myCosts + amScraping.subsidy`
-		1397	`print 'New margin for ',amScraping.item_id,' is ',margin`
-		1398	`return round(margin)`
-		1399
1380	`def getVat(data):`	1400	`def getVat(data):`
1381	`#VAT amount = Promo Price/(1+Vat Rate)VAT Rate minus NLC/(1+VAT Rate)VAT Rate`	1401	`#VAT amount = Promo Price/(1+Vat Rate)VAT Rate minus NLC/(1+VAT Rate)VAT Rate`
1382	`myVatPercentage = data.vatRate/100`	1402	`myVatPercentage = data.vatRate/100`
1383	`myVat = myVatPercentage*(data.promoPrice/(1+myVatPercentage) - data.wanlc/(1+myVatPercentage))`	1403	`myVat = myVatPercentage*(data.promoPrice/(1+myVatPercentage) - data.wanlc/(1+myVatPercentage))`
1384	`if(myVat<0):`	1404	`if(myVat<0):`
1385	`return 0`	1405	`return 0`
1386	`else:`	1406	`else:`
1387	`return round(myVat)`	1407	`return round(myVat)`
-		1408
-		1409	`def getNewVat(data,vatRate,proposedSp):`
-		1410	`myVatPercentage = vatRate/100`
-		1411	`myVat = myVatPercentage*(proposedSp/(1+myVatPercentage) - data.wanlc/(1+myVatPercentage))`
-		1412	`if(myVat<0):`
-		1413	`return 0`
-		1414	`else:`
-		1415	`return round(myVat)`
-		1416
1388		1417
1389	`def getCategory(data):`	1418	`def getCategory(data):`
1390	`return categoryMap[data.category]`	1419	`return categoryMap[data.category]`
1391		1420
1392	`def writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,runType):`	1421	`def writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,runType):`
Line 1578...	Line 1607...
1578	`else:`	1607	`else:`
1579	`sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))`	1608	`sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))`
1580	`sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))`	1609	`sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))`
1581	`if amScraping.decision is None:`	1610	`if amScraping.decision is None:`
1582	`sheet.write(sheet_iterator, 42, 'Auto Pricing Inactive')`	1611	`sheet.write(sheet_iterator, 42, 'Auto Pricing Inactive')`
1583	`sheet.write(sheet_iterator, 45, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))`	1612	`sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))`
1584	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`	1613	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`
1585	`sheet_iterator+=1`	1614	`sheet_iterator+=1`
1586	`continue`	1615	`continue`
1587	`sheet.write(sheet_iterator, 42, Decision._VALUES_TO_NAMES.get(amScraping.decision))`	1616	`sheet.write(sheet_iterator, 42, Decision._VALUES_TO_NAMES.get(amScraping.decision))`
1588	`sheet.write(sheet_iterator, 43, amScraping.reason)`	1617	`sheet.write(sheet_iterator, 43, amScraping.reason)`
1589	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":`	1618	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":`
1590	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.proposedSp))`	1619	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.proposedSp))`
1591	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":`	1620	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":`
1592	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))`	1621	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))`
1593	`sheet.write(sheet_iterator, 45, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))`	1622	`sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))`
1594	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`	1623	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`
1595	`sheet_iterator+=1`	1624	`sheet_iterator+=1`
1596		1625
1597	`almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()`	1626	`almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()`
1598	`for almostCompetitiveItem in almostCompetitiveItems:`	1627	`for almostCompetitiveItem in almostCompetitiveItems:`
Line 1662...	Line 1691...
1662	`else:`	1691	`else:`
1663	`sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))`	1692	`sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))`
1664	`sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))`	1693	`sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))`
1665	`if amScraping.decision is None:`	1694	`if amScraping.decision is None:`
1666	`sheet.write(sheet_iterator, 42, 'Auto Pricing Inactive')`	1695	`sheet.write(sheet_iterator, 42, 'Auto Pricing Inactive')`
1667	`sheet.write(sheet_iterator, 45, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))`	1696	`sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))`
1668	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`	1697	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`
1669	`sheet_iterator+=1`	1698	`sheet_iterator+=1`
1670	`continue`	1699	`continue`
1671	`sheet.write(sheet_iterator, 42, Decision._VALUES_TO_NAMES.get(amScraping.decision))`	1700	`sheet.write(sheet_iterator, 42, Decision._VALUES_TO_NAMES.get(amScraping.decision))`
1672	`sheet.write(sheet_iterator, 43, amScraping.reason)`	1701	`sheet.write(sheet_iterator, 43, amScraping.reason)`
1673	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":`	1702	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":`
1674	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.proposedSp))`	1703	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.proposedSp))`
1675	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":`	1704	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":`
1676	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))`	1705	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))`
1677	`sheet.write(sheet_iterator, 45, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))`	1706	`sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))`
1678	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`	1707	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`
1679	`sheet_iterator+=1`	1708	`sheet_iterator+=1`
1680		1709
1681	`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()`	1710	`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()`
1682	`for amongCheapestItem in amongCheapestItems:`	1711	`for amongCheapestItem in amongCheapestItems:`
Line 1746...	Line 1775...
1746	`else:`	1775	`else:`
1747	`sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))`	1776	`sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))`
1748	`sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))`	1777	`sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))`
1749	`if amScraping.decision is None:`	1778	`if amScraping.decision is None:`
1750	`sheet.write(sheet_iterator, 42, 'Auto Pricing Inactive')`	1779	`sheet.write(sheet_iterator, 42, 'Auto Pricing Inactive')`
1751	`sheet.write(sheet_iterator, 45, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))`	1780	`sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))`
1752	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`	1781	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`
1753	`sheet_iterator+=1`	1782	`sheet_iterator+=1`
1754	`continue`	1783	`continue`
1755	`sheet.write(sheet_iterator, 42, Decision._VALUES_TO_NAMES.get(amScraping.decision))`	1784	`sheet.write(sheet_iterator, 42, Decision._VALUES_TO_NAMES.get(amScraping.decision))`
1756	`sheet.write(sheet_iterator, 43, amScraping.reason)`	1785	`sheet.write(sheet_iterator, 43, amScraping.reason)`
1757	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":`	1786	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":`
1758	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.proposedSp))`	1787	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.proposedSp))`
1759	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":`	1788	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":`
1760	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))`	1789	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))`
1761	`sheet.write(sheet_iterator, 45, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))`	1790	`sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))`
1762	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`	1791	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`
1763	`sheet_iterator+=1`	1792	`sheet_iterator+=1`
1764		1793
1765	`# sheet = wbk.add_sheet('Cheapest')`	1794	`# sheet = wbk.add_sheet('Cheapest')`
1766	`# xstr = lambda s: s or ""`	1795	`# xstr = lambda s: s or ""`
Line 1876...	Line 1905...
1876	`else:`	1905	`else:`
1877	`sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))`	1906	`sheet.write(sheet_iterator, 40, str(round(daysOfStock,1)))`
1878	`sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))`	1907	`sheet.write(sheet_iterator, 41, getOosString(saleMap.get(sku)))`
1879	`if amScraping.decision is None:`	1908	`if amScraping.decision is None:`
1880	`sheet.write(sheet_iterator, 42, 'Auto Pricing Inactive')`	1909	`sheet.write(sheet_iterator, 42, 'Auto Pricing Inactive')`
1881	`sheet.write(sheet_iterator, 45, round(amScraping.proposedSp - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,amScraping.proposedSp))))`	1910	`sheet.write(sheet_iterator, 45, getNewMargin(amScraping,amScraping.proposedSp))`
1882	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`	1911	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`
1883	`sheet_iterator+=1`	1912	`sheet_iterator+=1`
1884	`continue`	1913	`continue`
1885	`sheet.write(sheet_iterator, 42, Decision._VALUES_TO_NAMES.get(amScraping.decision))`	1914	`sheet.write(sheet_iterator, 42, Decision._VALUES_TO_NAMES.get(amScraping.decision))`
1886	`sheet.write(sheet_iterator, 43, amScraping.reason)`	1915	`sheet.write(sheet_iterator, 43, amScraping.reason)`
1887	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":`	1916	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_DECREMENT_SUCCESS":`
1888	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.proposedSp))`	1917	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.proposedSp))`
1889	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":`	1918	`if Decision._VALUES_TO_NAMES.get(amScraping.decision) == "AUTO_INCREMENT_SUCCESS":`
1890	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))`	1919	`sheet.write(sheet_iterator, 44, math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice)))`
1891	`sheet.write(sheet_iterator, 45, round(math.ceil(amScraping.promoPrice+max(10,.01amScraping.promoPrice)) - getNewLowestPossibleSp(amScraping,12.36,getNewVatRate(amScraping.item_id,amScraping.warehouseLocation,math.ceil(amScraping.promoPrice+max(10,.01amScraping.promoPrice))))))`	1920	`sheet.write(sheet_iterator, 45, getNewMargin(amScraping,math.ceil(amScraping.promoPrice+max(10,.01*amScraping.promoPrice))))`
1892	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`	1921	`sheet.write(sheet_iterator, 46, getInventoryMovementStatus(amScraping))`
1893	`sheet_iterator+=1`	1922	`sheet_iterator+=1`
1894		1923
1895	`# sheet = wbk.add_sheet('Negative Margin')`	1924	`# sheet = wbk.add_sheet('Negative Margin')`
1896	`# xstr = lambda s: s or ""`	1925	`# xstr = lambda s: s or ""`
Line 2176...	Line 2205...
2176	`priceSuppress = "Yes"`	2205	`priceSuppress = "Yes"`
2177	`else:`	2206	`else:`
2178	`priceSuppress = "No"`	2207	`priceSuppress = "No"`
2179	`else:`	2208	`else:`
2180	`pass`	2209	`pass`
2181	`vatRate = getNewVatRate(item.item_id,item.warehouseLocation,item.proposedSp)`	2210	`#vatRate = getNewVatRate(item.item_id,item.warehouseLocation,item.proposedSp)`
2182	`#oldMargin = item.ourSellingPrice - item.lowestPossibleSp`	2211	`#oldMargin = item.ourSellingPrice - item.lowestPossibleSp`
2183	`oldMargin = item.promoPrice - item.lowestPossibleSp`	2212	`oldMargin = getMargin(item)`
2184	`newMargin = round(item.proposedSp - getNewLowestPossibleSp(item,12.36,vatRate))`	2213	`#newMargin = round(item.proposedSp - getNewLowestPossibleSp(item,12.36,vatRate))`
-		2214	`newMargin = getNewMargin(item,item.proposedSp)`
2185	`sku = ''`	2215	`sku = ''`
2186	`if item.warehouseLocation==1:`	2216	`if item.warehouseLocation==1:`
2187	`sku='FBA'+str(item.item_id)`	2217	`sku='FBA'+str(item.item_id)`
2188	`else:`	2218	`else:`
2189	`sku='FBB'+str(item.item_id)`	2219	`sku='FBB'+str(item.item_id)`
Line 2252...	Line 2282...
2252	`priceSuppress = "Yes"`	2282	`priceSuppress = "Yes"`
2253	`else:`	2283	`else:`
2254	`priceSuppress = "No"`	2284	`priceSuppress = "No"`
2255	`else:`	2285	`else:`
2256	`pass`	2286	`pass`
2257	`vatRate = getNewVatRate(item.item_id,item.warehouseLocation,math.ceil(item.promoPrice+max(10,.01*item.promoPrice)))`	2287	`#vatRate = getNewVatRate(item.item_id,item.warehouseLocation,math.ceil(item.promoPrice+max(10,.01*item.promoPrice)))`
2258	`#oldMargin = item.ourSellingPrice - item.lowestPossibleSp`	2288	`#oldMargin = item.ourSellingPrice - item.lowestPossibleSp`
2259	`oldMargin = item.promoPrice - item.lowestPossibleSp`	2289	`oldMargin = getMargin(item)`
2260	`newMargin = round(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)) - getNewLowestPossibleSp(item,12.36,vatRate))`	2290	`#newMargin = round(math.ceil(item.promoPrice+max(10,.01*item.promoPrice)) - getNewLowestPossibleSp(item,12.36,vatRate))`
-		2291	`newMargin = getNewMargin(item,math.ceil(item.promoPrice+max(10,.01*item.promoPrice)))`
2261	`sku = ''`	2292	`sku = ''`
2262	`if item.warehouseLocation==1:`	2293	`if item.warehouseLocation==1:`
2263	`sku='FBA'+str(item.item_id)`	2294	`sku='FBA'+str(item.item_id)`
2264	`else:`	2295	`else:`
2265	`sku='FBB'+str(item.item_id)`	2296	`sku='FBB'+str(item.item_id)`
Line 2388...	Line 2419...
2388	`<td style="text-align:center">"""+str(amScraping.ourSellingPrice)+"""</td>`	2419	`<td style="text-align:center">"""+str(amScraping.ourSellingPrice)+"""</td>`
2389	`<td style="text-align:center">"""+str(amScraping.promoPrice)+"""</td>`	2420	`<td style="text-align:center">"""+str(amScraping.promoPrice)+"""</td>`
2390	`<td style="text-align:center">"""+str(subsidy)+"""</td>`	2421	`<td style="text-align:center">"""+str(subsidy)+"""</td>`
2391	`<td style="text-align:center">"""+str(amScraping.lowestPossibleSp)+"""</td>`	2422	`<td style="text-align:center">"""+str(amScraping.lowestPossibleSp)+"""</td>`
2392	`<td style="text-align:center">"""+str(amScraping.wanlc)+"""</td>`	2423	`<td style="text-align:center">"""+str(amScraping.wanlc)+"""</td>`
2393	`<td style="text-align:center">"""+str(round(getMargin(amScraping)))+" ("+str(round((getMargin(amScraping)/amScraping.ourSellingPrice)*100,1))+"%)"+"""</td>`	2424	`<td style="text-align:center">"""+str(round(getMargin(amScraping)))+" ("+str(round((getMargin(amScraping)/amScraping.promoPrice)*100,1))+"%)"+"""</td>`
2394	`<td style="text-align:center">"""+str(amScraping.commission)+" %"+"""</td>`	2425	`<td style="text-align:center">"""+str(amScraping.commission)+" %"+"""</td>`
2395	`<td style="text-align:center">"""+str(amScraping.returnProvision)+" %"+"""</td>`	2426	`<td style="text-align:center">"""+str(amScraping.returnProvision)+" %"+"""</td>`
2396	`<td style="text-align:center">"""+str(amScraping.ourInventory)+"""</td>`	2427	`<td style="text-align:center">"""+str(amScraping.ourInventory)+"""</td>`
2397	`<td style="text-align:center">"""+getOosString(saleMap.get(sku))+"""</td>`	2428	`<td style="text-align:center">"""+getOosString(saleMap.get(sku))+"""</td>`
2398	`</tr>"""`	2429	`</tr>"""`
Line 2476...	Line 2507...
2476	`<td style="text-align:center">"""+str(amScraping.ourSellingPrice)+"""</td>`	2507	`<td style="text-align:center">"""+str(amScraping.ourSellingPrice)+"""</td>`
2477	`<td style="text-align:center">"""+str(amScraping.promoPrice)+"""</td>`	2508	`<td style="text-align:center">"""+str(amScraping.promoPrice)+"""</td>`
2478	`<td style="text-align:center">"""+str(subsidy)+"""</td>`	2509	`<td style="text-align:center">"""+str(subsidy)+"""</td>`
2479	`<td style="text-align:center">"""+str(amScraping.lowestPossibleSp)+"""</td>`	2510	`<td style="text-align:center">"""+str(amScraping.lowestPossibleSp)+"""</td>`
2480	`<td style="text-align:center">"""+str(amScraping.wanlc)+"""</td>`	2511	`<td style="text-align:center">"""+str(amScraping.wanlc)+"""</td>`
2481	`<td style="text-align:center">"""+str(round(getMargin(amScraping)))+" ("+str(round((getMargin(amScraping)/amScraping.ourSellingPrice)*100,1))+"%)"+"""</td>`	2512	`<td style="text-align:center">"""+str(round(getMargin(amScraping)))+" ("+str(round((getMargin(amScraping)/amScraping.promoPrice)*100,1))+"%)"+"""</td>`
2482	`<td style="text-align:center">"""+str(amScraping.commission)+" %"+"""</td>`	2513	`<td style="text-align:center">"""+str(amScraping.commission)+" %"+"""</td>`
2483	`<td style="text-align:center">"""+str(amScraping.returnProvision)+" %"+"""</td>`	2514	`<td style="text-align:center">"""+str(amScraping.returnProvision)+" %"+"""</td>`
2484	`<td style="text-align:center">"""+str(amScraping.competitivePrice)+" %"+"""</td>`	2515	`<td style="text-align:center">"""+str(amScraping.competitivePrice)+" %"+"""</td>`
2485	`<td style="text-align:center">"""+str(amScraping.proposedSp)+" %"+"""</td>`	2516	`<td style="text-align:center">"""+str(amScraping.proposedSp)+" %"+"""</td>`
2486	`<td style="text-align:center">"""+str(amScraping.ourInventory)+"""</td>`	2517	`<td style="text-align:center">"""+str(amScraping.ourInventory)+"""</td>`

Subversion Repositories SmartDukaan

(root)/trunk/PyProj/src/shop2020/model/v1/catalog/script/AmazonHistoryPopulator.py – Rev 12721 → 12736