Subversion Repositories SmartDukaan

Rev

Rev 13480 | Blame | Compare with Previous | Last modification | View Log | RSS feed 

from elixir import *
from shop2020.config.client.ConfigClient import ConfigClient
from shop2020.model.v1.catalog.impl import DataService
from shop2020.model.v1.catalog.impl.DataService import AmazonPromotion, Item
from shop2020.model.v1.order.script import MongoService
from shop2020.thriftpy.model.v1.catalog.ttypes import AmazonPromotionType
from shop2020.thriftpy.model.v1.order.ttypes import AmazonFCWarehouseLocation
from shop2020.utils.Utils import to_py_date
from datetime import datetime, timedelta
import xlwt


config_client = ConfigClient()
host = config_client.get_property('staging_hostname')

DataService.initialize(db_hostname=host)

startDate = datetime.strptime('01-12-2014 00:00:00', '%d-%m-%Y %H:%M:%S')
endDate = datetime.strptime('28-12-2014 00:00:00', '%d-%m-%Y %H:%M:%S')

wpiPromoMap = {}
dodPromoMap = {}
itemInfoMap = {}

prefix = {0:'FBA',1:'FBB',2:'FBG'}

offset, limit, sheet_iterator = 0, 1000, 1

class __ItemInfo:
    
    def __init__(self, brand, model_name, model_number, color, productGroup):
        self.brand = brand
        self.model_name = model_name
        self.model_number = model_number
        self.color = color
        self.productGroup = productGroup


def populateItemInfo():
    global itemInfoMap
    item_ids = MongoService.getDistinctItemsFromOrderData(startDate, endDate)
    for item_id in item_ids:
        it = Item.get_by(id=item_id)
        if it is None:
            itemInfoMap[item_id] = __ItemInfo(' ', ' ', ' ', ' ', ' ') 
            continue  
        itemInfoMap[item_id] = __ItemInfo(it.brand, it.model_name, it.model_number, it.color, it.product_group)  

def populateSubsidyInfo():
    promotions = AmazonPromotion.query.filter(AmazonPromotion.endDate>=startDate - timedelta(days=7)).filter(AmazonPromotion.endDate<=endDate + timedelta(days=7)).all()
    for promo in promotions:
        if not promo.promotionActive:
            continue
        temp = []
        if promo.promotionType == AmazonPromotionType.LONGTERM:
            if wpiPromoMap.has_key(promo.sku):
                val = wpiPromoMap.get(promo.sku)
                for l in val:
                    temp.append(l)
                temp.append(promo)
                wpiPromoMap[promo.sku] =  temp
            else:
                temp.append(promo)
                wpiPromoMap[promo.sku] = temp
        else:
            if dodPromoMap.has_key(promo.sku):
                val = dodPromoMap.get(promo.sku)
                for l in val:
                    temp.append(l)
                temp.append(promo)
                dodPromoMap[promo.sku] =  temp
            else:
                temp.append(promo)
                dodPromoMap[promo.sku] = temp

def writeReport():
    global sheet_iterator
    global offset
    wbk = xlwt.Workbook(encoding="UTF-8")
    sheet = wbk.add_sheet('AmazonSubsidy')
    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
    writeHead(sheet,heading_xf)
    while(True):
        orders = MongoService.getAmazonOrderData(startDate, endDate, offset, limit)
        if len(orders) == 0:
            break
        for order in orders:
            sheet.write(sheet_iterator, 0, order.amazonOrderId)
            sheet.write(sheet_iterator, 1, order.item_id)
            sheet.write(sheet_iterator, 2, prefix.get(AmazonFCWarehouseLocation._NAMES_TO_VALUES.get(order.fcLocation))+str(order.item_id))
            sheet.write(sheet_iterator, 3, order.fcLocation)
            sheet.write(sheet_iterator, 4, getItemInfo(order.item_id).productGroup)
            sheet.write(sheet_iterator, 5, getItemInfo(order.item_id).brand)
            sheet.write(sheet_iterator, 6,  xstr(getItemInfo(order.item_id).brand)+" "+xstr(getItemInfo(order.item_id).model_name)+" "+xstr(getItemInfo(order.item_id).model_number)+" "+xstr(getItemInfo(order.item_id).color))
            sheet.write(sheet_iterator, 7, str(to_py_date(order.purchaseDate)))
            sheet.write(sheet_iterator, 8, order.totalAmount)
            sheet.write(sheet_iterator, 9, order.quantity)
            sheet.write(sheet_iterator, 10, order.promotionDiscount)
            subsidy, maxSalePrice, promoStartDate, promoEndDate, promoType, toleranceAmount = calculateSubsidy(order.item_id, AmazonFCWarehouseLocation._NAMES_TO_VALUES.get(order.fcLocation), to_py_date(order.purchaseDate),order.totalAmount, order.amazonOrderId, order.promotionDiscount)
            if subsidy > 0:
                sheet.write(sheet_iterator, 11, (order.quantity)*(subsidy))
                sheet.write(sheet_iterator, 12, toleranceAmount)
                sheet.write(sheet_iterator, 13, str(promoStartDate))
                sheet.write(sheet_iterator, 14, str(promoEndDate))
                sheet.write(sheet_iterator, 15, maxSalePrice)
                sheet.write(sheet_iterator, 16, AmazonPromotionType._VALUES_TO_NAMES.get(promoType))
            sheet_iterator+=1
        offset = offset + 1000
    file_subsidy = "/tmp/subsidy"+" " + str(datetime.now()) + ".xls"
    wbk.save(file_subsidy)
            
def calculateSubsidy(item_id, fcLocation, purchaseDate, sellingPrice, amazonOrderId, promotionDiscount):
    validPromosWpi = []
    validPromosDod = []
    sku = prefix.get(fcLocation)+str(item_id)
    if wpiPromoMap.has_key(sku):
        promos = wpiPromoMap.get(sku)
        for promo in promos:
            if promo.startDate <= purchaseDate and promo.endDate >= purchaseDate:
                validPromosWpi.append(promo)
    if dodPromoMap.has_key(sku):
        promos = dodPromoMap.get(sku)
        for promo in promos:
            if promo.startDate <= purchaseDate and promo.endDate >= purchaseDate:
                validPromosDod.append(promo)
    return figureOutBestPossibleSubsidy(validPromosWpi, validPromosDod, sellingPrice, purchaseDate, amazonOrderId, promotionDiscount, sku)

#def figureOutBestPossibleSubsidy(validPromosWpi, validPromosDod , sellingPrice, purchaseDate,amazonOrderId):
#    if len(validPromos) > 0:
#        print "No of valid promo for sku ", (validPromos[0]).sku ,"is ", len(validPromos), "and purchase date ", purchaseDate, "amazon order id ",amazonOrderId
#    subsidy, maxSalePrice, promoStartDate, promoEndDate, promoType = 0.0, 0.0, None, None, None 
#    toleranceAmount = 50 if sellingPrice <=1000 else 100
#    for validPromo in validPromos:
#        if validPromo.promotionType == AmazonPromotionType.LONGTERM:
#            if validPromo.salePrice >= sellingPrice:
#                if validPromo.subsidy > subsidy:
#                    subsidy = validPromo.subsidy
#                    maxSalePrice = validPromo.salePrice
#                    promoStartDate = validPromo.startDate
#                    promoEndDate = validPromo.endDate
#                    promoType = AmazonPromotionType.LONGTERM
#            elif  validPromo.salePrice >= sellingPrice - toleranceAmount:
#                if validPromo.subsidy > subsidy:
#                    subsidy = validPromo.subsidy
#                    maxSalePrice = validPromo.salePrice
#                    promoStartDate = validPromo.startDate
#                    promoEndDate = validPromo.endDate
#                    promoType = AmazonPromotionType.LONGTERM
#        elif validPromo.promotionType == AmazonPromotionType.SHORTTERM:
#            if validPromo.salePrice >= sellingPrice:
#                if validPromo.subsidy > subsidy:
#                    subsidy = validPromo.subsidy
#                    maxSalePrice = validPromo.salePrice
#                    promoStartDate = validPromo.startDate
#                    promoEndDate = validPromo.endDate
#                    promoType = AmazonPromotionType.SHORTTERM
#            elif  validPromo.salePrice >= sellingPrice - toleranceAmount:
#                if validPromo.subsidy > subsidy:
#                    subsidy = validPromo.subsidy
#                    maxSalePrice = validPromo.salePrice
#                    promoStartDate = validPromo.startDate
#                    promoEndDate = validPromo.endDate
#                    promoType = AmazonPromotionType.SHORTTERM
#    return subsidy, maxSalePrice, promoStartDate, promoEndDate, promoType

def figureOutBestPossibleSubsidy(validPromosWpi, validPromosDod, sellingPrice, purchaseDate, amazonOrderId, promotionDiscount, sku):
    toleranceAmount = 50 if sellingPrice <=1000 else 100
    subsidy, maxSalePrice, tolerance, startDate, endDate = 0.0, 0.0, 0.0, None, None
    if len(validPromosDod)==0:
        """No info about deals , check wpiPromo or add buffer to dod promos"""
        dodPromos = dodPromoMap.get(sku)
        if dodPromos is not None:
            for dodPromo in dodPromos:
                if dodPromo.salePrice >=(sellingPrice -toleranceAmount) and purchaseDate >= dodPromo.startDate and purchaseDate <= dodPromo.endDate + timedelta(days=2) and dodPromo.subsidy > subsidy:
                    subsidy =  dodPromo.subsidy
                    maxSalePrice = dodPromo.salePrice
                    tolerance = toleranceAmount
                    startDate = dodPromo.startDate
                    endDate = dodPromo.endDate
        if subsidy == 0.0:
            """Still no information after adding buffer, checking wpi now"""
            return getInformationForWpi(validPromosWpi, sellingPrice, purchaseDate, amazonOrderId, sku, toleranceAmount)
        return subsidy, maxSalePrice, startDate, endDate, AmazonPromotionType.SHORTTERM, tolerance
    elif len(validPromosDod)==1:
        """Single deal found"""
        if validPromosDod[0].salePrice >= sellingPrice:
            return validPromosDod[0].subsidy, validPromosDod[0].salePrice, validPromosDod[0].startDate, validPromosDod[0].endDate, AmazonPromotionType.SHORTTERM, 0.0
        elif validPromosDod[0].salePrice >= sellingPrice - toleranceAmount:
            return validPromosDod[0].subsidy, validPromosDod[0].salePrice, validPromosDod[0].startDate, validPromosDod[0].endDate, AmazonPromotionType.SHORTTERM, toleranceAmount
        else:
            return getInformationForWpi(validPromosWpi, sellingPrice, purchaseDate, amazonOrderId, sku, toleranceAmount)
    else:
        """Mutiple validPromosDod exist for order.Selecting best possible"""
        for promo in validPromosDod:
            if promo.salePrice >=sellingPrice and promo.subsidy > subsidy:
                subsidy =  promo.subsidy
                maxSalePrice = promo.salePrice
                tolerance = 0.0
                startDate = promo.startDate
                endDate = promo.endDate
            if subsidy == 0.0:
                """Adding tolerance amount"""
                if promo.salePrice >=(sellingPrice-toleranceAmount) and promo.subsidy > subsidy:
                    subsidy =  promo.subsidy
                    maxSalePrice = promo.salePrice
                    tolerance = toleranceAmount
                    startDate = promo.startDate
                    endDate = promo.endDate                    
        return subsidy, maxSalePrice, startDate, endDate, AmazonPromotionType.SHORTTERM, tolerance
        
def getInformationForWpi(validPromosWpi, sellingPrice, purchaseDate, amazonOrderId, sku, toleranceAmount):
    subsidy, maxSalePrice, tolerance, startDate, endDate, addedOnDate = 0.0, 0.0, 0.0, None, None, None
    if len(validPromosWpi) == 0:
        return subsidy, maxSalePrice, tolerance, startDate, endDate, 0.0
    
    elif len(validPromosWpi) == 1:
        if validPromosWpi[0].salePrice >= sellingPrice:
            return validPromosWpi[0].subsidy, validPromosWpi[0].salePrice, validPromosWpi[0].startDate, validPromosWpi[0].endDate, AmazonPromotionType.LONGTERM, 0.0
        
        elif validPromosWpi[0].salePrice >= sellingPrice - toleranceAmount:
            return validPromosWpi[0].subsidy, validPromosWpi[0].salePrice, validPromosWpi[0].startDate, validPromosWpi[0].endDate, AmazonPromotionType.LONGTERM, toleranceAmount
        
        else:
            return subsidy, validPromosWpi[0].salePrice, validPromosWpi[0].startDate, validPromosWpi[0].endDate, AmazonPromotionType.LONGTERM, toleranceAmount
    else:
        """Mutiple WPI""" 
        for wpiPromo in validPromosWpi:
            if wpiPromo.salePrice >= sellingPrice and addedOnDate is None:
                subsidy = wpiPromo.subsidy
                maxSalePrice = wpiPromo.salePrice
                startDate = wpiPromo.startDate
                endDate = wpiPromo.endDate
                tolerance = 0.0
                addedOnDate = wpiPromo.addedOn
            elif wpiPromo.salePrice >= (sellingPrice -toleranceAmount) and addedOnDate is None:
                subsidy = wpiPromo.subsidy
                maxSalePrice = wpiPromo.salePrice
                startDate = wpiPromo.startDate
                endDate = wpiPromo.endDate
                tolerance = toleranceAmount
                addedOnDate = wpiPromo.addedOn
            elif wpiPromo.salePrice >= sellingPrice and wpiPromo.subsidy > subsidy :
                subsidy = wpiPromo.subsidy
                maxSalePrice = wpiPromo.salePrice
                startDate = wpiPromo.startDate
                endDate = wpiPromo.endDate
                tolerance = 0.0
                addedOnDate = wpiPromo.addedOn
            elif wpiPromo.salePrice >= (sellingPrice- toleranceAmount) and wpiPromo.subsidy > subsidy:
                subsidy = wpiPromo.subsidy
                maxSalePrice = wpiPromo.salePrice
                startDate = wpiPromo.startDate
                endDate = wpiPromo.endDate
                tolerance = toleranceAmount
                addedOnDate = wpiPromo.addedOn
            else:
                pass
    return subsidy, maxSalePrice, startDate, endDate, AmazonPromotionType.LONGTERM, tolerance
                    

def writeHead(sheet,heading_xf):
    sheet.write(0, 0, "Amazon Order Id", heading_xf)
    sheet.write(0, 1, "Item Id", heading_xf)
    sheet.write(0, 2, "Amazon Sku", heading_xf)
    sheet.write(0, 3, "Location", heading_xf)
    sheet.write(0, 4, "Product Group", heading_xf)
    sheet.write(0, 5, "Brand", heading_xf)
    sheet.write(0, 6, "Product Name", heading_xf)
    sheet.write(0, 7, "Purchase Date", heading_xf)
    sheet.write(0, 8, "Selling Price", heading_xf)
    sheet.write(0, 9, "Quantity", heading_xf)
    sheet.write(0, 10, "Item Promotion Discount", heading_xf)
    sheet.write(0, 11, "Subsidy", heading_xf)
    sheet.write(0, 12, "Tolerance Amount", heading_xf)
    sheet.write(0, 13, "Promo Start Date", heading_xf)
    sheet.write(0, 14, "Promo End Date", heading_xf)
    sheet.write(0, 15, "MSP", heading_xf)
    sheet.write(0, 16, "Promo Type", heading_xf)
    
def getItemInfo(item_id):
    return itemInfoMap.get(item_id)
        
def main():
    populateItemInfo()
    populateSubsidyInfo()
    writeReport()

if __name__=='__main__':
    main()