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

#!/usr/bin/python
# coding: ascii
from elixir import *
from shop2020.model.v1.catalog.impl import DataService
from shop2020.model.v1.catalog.impl.DataService import Item, Category,\
    SnapdealItem
from shop2020.thriftpy.model.v1.inventory import ttypes
from shop2020.thriftpy.model.v1.inventory.ttypes import WarehouseType, InventoryType
import optparse
import xlrd
from shop2020.clients.InventoryClient import InventoryClient
import xlwt
import urllib2
import time
from datetime import date
import simplejson as json
from shop2020.utils import EmailAttachmentSender
from shop2020.utils.EmailAttachmentSender import get_attachment_part



if __name__ == '__main__' and __package__ is None:
    import sys
    import os
    sys.path.insert(0, os.getcwd())


class _SnapdealItemInfo:
    
    def __init__(self, item_id, product_group, category_name, our_nlc, brand, model_name, model_number, color, weight, parent_category, risky, issue, dealerPrice):
        self.item_id = item_id
        self.product_group = product_group
        self.category_name = category_name
        self.our_nlc = our_nlc
        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.issue = issue
        self.dealerPrice = dealerPrice
        

class SnapdealDetails:
    def __init__(self, supc, ourSp, offerPrice, ourInventory, otherInventory, rank, lowestSellerName,lowestSp,secondLowestSellerName,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice):
        self.supc = supc
        self.ourSp = ourSp
        self.offerPrice = offerPrice
        self.ourInventory = ourInventory
        self.otherInventory = otherInventory
        self.rank = rank
        self.lowestSellerName = lowestSellerName
        self.lowestSp = lowestSp
        self.secondLowestSellerName = secondLowestSellerName
        self.secondLowestSellerSp = secondLowestSellerSp
        self.secondLowestSellerInventory = secondLowestSellerInventory
        self.lowestOfferPrice = lowestOfferPrice
        self.secondLowestSellerOfferPrice = secondLowestSellerOfferPrice
        self.ourOfferPrice = ourOfferPrice
        

def fetchDetails(supc_code):
    url="http://www.snapdeal.com/json/gvbps?supc=%s&catId=91"%(supc_code)
    print url
    time.sleep(2)
    req = urllib2.Request(url)
    response = urllib2.urlopen(req)
    json_input = response.read()
    vendorInfo = json.loads(json_input)
    rank ,otherInventory ,ourInventory, offerPrice, ourSp,iterator = 0, 0, 0, 0, 0, 0
    secondLowestSellerName=''
    secondLowestSellerSp=0
    secondLowestSellerInventory=0
    lowestOfferPrice = 0
    secondLowestSellerOfferPrice = 0
    ourOfferPrice = 0
    for vendor in vendorInfo:
        if iterator == 0:
            lowestSellerName = vendor['vendorDisplayName']
            try:
                lowestSp = vendor['sellingPriceBefIntCashBack']
            except:
                lowestSp = vendor['sellingPrice']
            lowestOfferPrice = vendor['sellingPrice']
            
        if iterator ==1:
            secondLowestSellerName = vendor['vendorDisplayName']
            try:
                secondLowestSellerSp = vendor['sellingPriceBefIntCashBack']
            except:
                secondLowestSellerSp = vendor['sellingPrice'] 
            secondLowestSellerOfferPrice = vendor['sellingPrice'] 
            secondLowestSellerInventory = vendor['buyableInventory']
            
        if vendor['vendorDisplayName'] == 'MobilesnMore':
            ourInventory = vendor['buyableInventory']
            try:
                ourSp = vendor['sellingPriceBefIntCashBack']
            except:
                ourSp = vendor['sellingPrice']
            ourOfferPrice = vendor['sellingPrice']
            rank = iterator +1
        else:
            if rank==0:
                otherInventory = otherInventory +vendor['buyableInventory']
            
        iterator+=1
    snapdealDetails = SnapdealDetails(supc_code,ourSp,offerPrice,ourInventory,otherInventory,rank,lowestSellerName,lowestSp,secondLowestSellerName,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice)
    return snapdealDetails


def read_data(filename):
    all_lines = []
    all_supc=[]
    iclient = InventoryClient().get_client()
    DataService.initialize('catalog','192.168.166.135')
    workbook = xlrd.open_workbook(filename)
    sheet = workbook.sheet_by_index(0)
    num_rows = sheet.nrows
    for rownum in range(1, num_rows):
        print sheet.row_values(rownum)
        try:
            supc = sheet.row_values(rownum)[1]
            item_id = int(sheet.row_values(rownum)[0])
            item = Item.query.filter_by(id=item_id).one()
        except Exception as e:
            print "No item found.Maybe due to snapdeal item code in place of our sku id.Lets try again."
            print e
            try:
                item_id = sheet.row_values(rownum)[0]
                snapdeal_item = SnapdealItem.query.filter_by(skuAtSnapdeal=item_id).one()
                item = Item.query.filter_by(id=snapdeal_item.item_id).one()
            except Exception as ex:
                print ex
                one_line = _SnapdealItemInfo(sheet.row_values(rownum)[0], '', '', 0, '', '','', '', 0, '', False,"item_related",0)
                all_supc.append(supc)
                all_lines.append(one_line)
                continue
            
        category = Category.query.filter_by(id=item.category).one()
        parent_category = Category.query.filter_by(id=category.parent_category_id).first()
        try:
            snapdeal_item = SnapdealItem.query.filter_by(item_id=item.id).one()
            warehouse = iclient.getWarehouse(snapdeal_item.warehouseId)
            item_pricing = iclient.getItemPricing(item.id, warehouse.vendor.id)
            one_line = _SnapdealItemInfo(item.id, item.product_group, parent_category.display_name, item_pricing.nlc, item.brand, item.model_name, item.model_number, item.color, item.weight, category.parent_category_id, item.risky,"",item_pricing.dealerPrice)
        except Exception as e:
            print "Problem with item id ",item.id
            print e
            one_line = _SnapdealItemInfo(item.id, item.product_group, parent_category.display_name, item_pricing.nlc, item.brand, item.model_name, item.model_number, item.color, item.weight, category.parent_category_id, item.risky,"our_side",item_pricing.dealerPrice)
            
        #TO BE USED LATER
        #inventory_snapshot = iclient.getInventorySnapshot(0)
        #warehouses_ours = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("OURS"), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)
        #warehouses_third_party = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("THIRD_PARTY "), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)
        all_supc.append(supc)
        all_lines.append(one_line)
    write_report("/tmp/snapdeal_running.xls", all_lines, all_supc)
    

def exception_sheet(items,wbk):
    sheet = wbk.add_sheet('Exception Item List')

    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, "Problem Type", heading_xf)
    
    i=1
    for item in items:
        sheet.write(i, 0, item.item_id)
        sheet.write(i, 1, item.brand)
        sheet.write(i, 2, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
        if len(item.issue)!=0:
            sheet.write(i, 3, "Wrong item id or wrong warehouse Id")
        else:
            sheet.write(i, 3, "Unable to fetch info from snapdeal server")
        i+=1
    
    
    


def write_report(filename, all_lines, all_supc):
    
    buyBoxItems = []
    competitive = []
    competitiveNoInventory = []
    exceptionItems = []
    negativeMargin = []
    
    wbk = xlwt.Workbook()
    sheet = wbk.add_sheet('Can\'t Compete')
    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, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "SUPC", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 10, "Our Offer Price", heading_xf)
    sheet.write(0, 12, "Our Rank", heading_xf)
    sheet.write(0, 13, "Lowest Seller", heading_xf)
    sheet.write(0, 14, "Lowest SP", heading_xf)
    sheet.write(0, 15, "Lowest TP", heading_xf)
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 19, "Our NLC", heading_xf)
    sheet.write(0, 20, "Our Dealer Price", heading_xf)
    sheet.write(0, 21, "Lowest Possible TP", heading_xf)
    sheet.write(0, 22, "Lowest Possible SP", heading_xf)
    sheet.write(0, 23, "Competition Basis ", heading_xf)
    sheet.write(0, 24, "Can Compete", heading_xf)
    sheet.write(0, 25, "Target TP", heading_xf)
    sheet.write(0, 26, "Target SP", heading_xf)  
    sheet.write(0, 27, "Target NLC", heading_xf)
    sheet.write(0, 28, "Sales Potential", heading_xf)
    
    i, sheet_iterator=1,1
    for one_line in all_lines:
        if len(one_line.issue)!=0:
            exceptionItems.append(one_line)
            i+=1
            continue
        supc = all_supc[i-1]
        try:
            supc_data = fetchDetails(supc)
        except Exception as e:
            print "Unable to get information about SUPC Code: ",supc
            exceptionItems.append(one_line)
            i+=1
            continue
        
        courierCost = 45
        
        if one_line.weight:
            slab = int(((one_line.weight+0.05) - .001)/.5)
        for x in range (0,slab):
            courierCost = courierCost + 35
        
        courierCost = courierCost * 1.1236
        
        if one_line.parent_category ==10011:
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
        else:
            lowestTp = supc_data.lowestSp*0.9497-courierCost
        
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
        
        if (supc_data.ourSp*0.9597-courierCost < lowest_possible_tp and supc_data.ourSp > 0 and round((supc_data.ourSp*0.9597-courierCost) - lowest_possible_tp) < 0):
            temp=[]
            temp.append(supc_data)
            temp.append(one_line)
            negativeMargin.append(temp)
            i+=1
            continue
        
        if supc_data.rank==1:
            temp = []
            temp.append(supc_data)
            temp.append(one_line)
            buyBoxItems.append(temp)
            i+=1
            continue
        
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice == supc_data.ourSp:
            competitionBasis ='SP'
        else:
            competitionBasis ='TP'
        
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
        
        if competitionBasis=='SP':
            if supc_data.lowestSp > lowest_possible_sp and supc_data.ourInventory!=0:
                temp=[]
                temp.append(supc_data)
                temp.append(one_line)
                competitive.append(temp)
                i+=1
                continue
        else:
            if lowestTp > lowest_possible_tp and supc_data.ourInventory!=0:
                temp=[]
                temp.append(supc_data)
                temp.append(one_line)
                competitive.append(temp)
                i+=1
                continue
        
        if competitionBasis=='SP':
            if supc_data.lowestSp > lowest_possible_sp and supc_data.ourInventory==0:
                temp=[]
                temp.append(supc_data)
                temp.append(one_line)
                competitiveNoInventory.append(temp)
                i+=1
                continue
        else:
            if lowestTp > lowest_possible_tp and supc_data.ourInventory==0:
                temp=[]
                temp.append(supc_data)
                temp.append(one_line)
                competitiveNoInventory.append(temp)
                i+=1
                continue        
 
        sheet.write(sheet_iterator, 0, one_line.item_id)
        sheet.write(sheet_iterator, 1, one_line.category_name)
        sheet.write(sheet_iterator, 2, one_line.product_group)
        sheet.write(sheet_iterator, 3, supc)
        sheet.write(sheet_iterator, 4, one_line.brand)
        sheet.write(sheet_iterator, 5, xstr(one_line.brand)+" "+xstr(one_line.model_name)+" "+xstr(one_line.model_number)+" "+xstr(one_line.color))
        sheet.write(sheet_iterator, 6, one_line.weight)
        sheet.write(sheet_iterator, 7, round(courierCost))
        sheet.write(sheet_iterator, 8, one_line.risky)
        sheet.write(sheet_iterator, 9, supc_data.ourSp)
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
        sheet.write(sheet_iterator, 10, supc_data.ourOfferPrice)
        sheet.write(sheet_iterator, 12, supc_data.rank)
        sheet.write(sheet_iterator, 13, supc_data.lowestSellerName)
        sheet.write(sheet_iterator, 14, supc_data.lowestSp)
        if one_line.parent_category ==10011:
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
        else:
            lowestTp = supc_data.lowestSp*0.9497-courierCost
        sheet.write(sheet_iterator, 15, round(lowestTp))
        sheet.write(sheet_iterator, 16, supc_data.lowestOfferPrice)
        sheet.write(sheet_iterator, 17, supc_data.otherInventory)
        sheet.write(sheet_iterator, 18, supc_data.ourInventory)
        sheet.write(sheet_iterator, 19, one_line.our_nlc)
        sheet.write(sheet_iterator, 20, one_line.dealerPrice)
        if supc_data.rank==1:
            i+=1
            sheet_iterator+=1
            continue
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
        competitionBasis ='TP'
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice == supc_data.ourSp:
            competitionBasis ='SP'
            sheet.write(sheet_iterator, 23, 'SP')
        else:
            sheet.write(sheet_iterator, 23, 'TP')
        proposed_tp = 0
        if competitionBasis=='SP':
            if supc_data.lowestSp > lowest_possible_sp:
                sheet.write(sheet_iterator, 24, "Yes")
            else:
                sheet.write(sheet_iterator, 24, "No")
        else:
            if lowestTp > lowest_possible_tp:
                sheet.write(sheet_iterator, 24, "Yes")
            else:
                sheet.write(sheet_iterator, 24, "No")
        if competitionBasis=='SP':
            proposed_sp = supc_data.lowestSp - max(10, supc_data.lowestSp*0.001)
            proposed_tp = proposed_sp*0.9597-courierCost
            sheet.write(sheet_iterator, 25, round(proposed_tp))
            sheet.write(sheet_iterator, 26, round(proposed_sp))
            sheet.write(sheet_iterator, 27, round(proposed_tp*0.988-21)) 
        else:    
            proposed_tp  = lowestTp - max(10, lowestTp*0.001)
            sheet.write(sheet_iterator, 25, round(proposed_tp))
            sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
            sheet.write(sheet_iterator, 27, round(proposed_tp*0.988-21)) 
        
        
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
            sheet.write(sheet_iterator, 28, "HIGH")
        elif (supc_data.lowestOfferPrice -one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
            sheet.write(sheet_iterator, 28, "MEDIUM")
        else:
            sheet.write(sheet_iterator, 28, "LOW")
        i= i+1
        sheet_iterator+=1
        
    createSheetForBuyBoxItems(buyBoxItems,wbk)
    createSheetForCometitiveItems(competitive,wbk)
    createSheetForCometitiveItemsNoInventory(competitiveNoInventory,wbk)
    exception_sheet(exceptionItems,wbk)
    createSheetForNegativeMargin(negativeMargin,wbk)
    wbk.save(filename)
    today = date.today()
    datestr = str(today.year) + "-" + str(today.month) + "-" + str(today.day)
    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"], " Snapdeal Sheet " + datestr, "", [get_attachment_part(filename)], ["rajneesh.arora@saholic.com","kshitij.sood@saholic.com","khushal.bhatia@saholic.com"], [])

    

def createSheetForBuyBoxItems(buyBoxItems,wbk):
    sheet = wbk.add_sheet('Lowest')

    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, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "SUPC", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 10, "Our Offer Price", heading_xf)
    sheet.write(0, 12, "Our Rank", heading_xf)
    sheet.write(0, 13, "Lowest Seller", heading_xf)
    sheet.write(0, 14, "Second Lowest Seller", heading_xf)
    sheet.write(0, 15, "Second Lowest Price", heading_xf)
    sheet.write(0, 16, "Second Lowest Offer Price", heading_xf)
    sheet.write(0, 17, "Second Lowest Seller TP", heading_xf)
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 19, "Second Lowest Seller Inventory", heading_xf)
    sheet.write(0, 20, "Our NLC", heading_xf)
    sheet.write(0, 21, "Our Dealer Price", heading_xf)
    sheet.write(0, 22, "Competition Basis", heading_xf)
    sheet.write(0, 23, "Target TP", heading_xf)
    sheet.write(0, 24, "Target SP", heading_xf)
    sheet.write(0, 25, "MARGIN INCREASED POTENTIAL", heading_xf)
    
    i=1
    
    for data in buyBoxItems:
        supc_data =data[0]
        one_line = data[1]
        
        courierCost = 45
        
        if one_line.weight:
            slab = int(((one_line.weight+0.05) - .001)/.5)
        for x in range (0,slab):
            courierCost = courierCost + 35
        
        courierCost = courierCost * 1.1236
        
        sheet.write(i, 0, one_line.item_id)
        sheet.write(i, 1, one_line.category_name)
        sheet.write(i, 2, one_line.product_group)
        sheet.write(i, 3, supc_data.supc)
        sheet.write(i, 4, one_line.brand)
        sheet.write(i, 5, xstr(one_line.brand)+" "+xstr(one_line.model_name)+" "+xstr(one_line.model_number)+" "+xstr(one_line.color))
        sheet.write(i, 6, one_line.weight)
        sheet.write(i, 7, round(courierCost))
        sheet.write(i, 8, one_line.risky)
        sheet.write(i, 9, supc_data.ourSp)
        sheet.write(i, 11, round(supc_data.ourSp*0.9597-courierCost))
        sheet.write(i, 10, supc_data.ourOfferPrice)
        sheet.write(i, 12, supc_data.rank)
        sheet.write(i, 13, supc_data.lowestSellerName)
        sheet.write(i, 14, supc_data.secondLowestSellerName)
        sheet.write(i, 15, supc_data.secondLowestSellerSp)
        sheet.write(i, 16, supc_data.secondLowestSellerOfferPrice)
        if one_line.parent_category ==10011:
            secondlowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
        else:
            secondlowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
        sheet.write(i, 17, round(secondlowestTp))
        sheet.write(i, 18, supc_data.ourInventory)
        sheet.write(i, 19, supc_data.secondLowestSellerInventory)
        sheet.write(i, 20, one_line.our_nlc)
        sheet.write(i, 21, one_line.dealerPrice)
        
        competitionBasis ='TP'
        if (supc_data.secondLowestSellerOfferPrice == supc_data.secondLowestSellerSp) and supc_data.ourOfferPrice==supc_data.ourSp:
            competitionBasis ='SP'
            sheet.write(i, 22, 'SP')
        else:
            sheet.write(i, 22, 'TP')
        
        if competitionBasis=='SP':
            proposed_sp = max(supc_data.secondLowestSellerSp - max((20, supc_data.secondLowestSellerSp*0.002)), lowest_possible_sp)
            proposed_tp = proposed_sp*0.9597-courierCost
            sheet.write(i, 23, round(proposed_tp))
            sheet.write(i, 24, round(proposed_sp))
        else:    
            proposed_tp  = max(secondlowestTp - max((20, secondlowestTp*0.002)), lowest_possible_tp)
            sheet.write(i, 23, round(proposed_tp))
            sheet.write(i, 24, round((proposed_tp+courierCost)/0.9597))
        
        sheet.write(i, 25, round(proposed_tp-(supc_data.ourSp*0.9597-courierCost)))     
        i+=1


def createSheetForCometitiveItems(competitiveItems,wbk):
    sheet = wbk.add_sheet('Can Compete-With Inventory')

    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, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "SUPC", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 10, "Our Offer Price", heading_xf)
    sheet.write(0, 12, "Our Rank", heading_xf)
    sheet.write(0, 13, "Lowest Seller", heading_xf)
    sheet.write(0, 14, "Lowest SP", heading_xf)
    sheet.write(0, 15, "Lowest TP", heading_xf)
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 19, "Our NLC", heading_xf)
    sheet.write(0, 20, "Our Dealer Price", heading_xf)
    sheet.write(0, 21, "Lowest Possible TP", heading_xf)
    sheet.write(0, 22, "Lowest Possible SP", heading_xf)
    sheet.write(0, 23, "Competition Basis ", heading_xf)
    sheet.write(0, 24, "Can Compete", heading_xf)
    sheet.write(0, 25, "Target TP", heading_xf)
    sheet.write(0, 26, "Target SP", heading_xf)  
    sheet.write(0, 27, "Sales Potential", heading_xf)
    
    i, sheet_iterator=1,1
    for data in competitiveItems:
        supc_data =data[0]
        one_line = data[1]
        
        courierCost = 45
        
        if one_line.weight:
            slab = int(((one_line.weight+0.05) - .001)/.5)
        for x in range (0,slab):
            courierCost = courierCost + 35
        
        courierCost = courierCost * 1.1236
        
        
        sheet.write(sheet_iterator, 0, one_line.item_id)
        sheet.write(sheet_iterator, 1, one_line.category_name)
        sheet.write(sheet_iterator, 2, one_line.product_group)
        sheet.write(sheet_iterator, 3, supc_data.supc)
        sheet.write(sheet_iterator, 4, one_line.brand)
        sheet.write(sheet_iterator, 5, xstr(one_line.brand)+" "+xstr(one_line.model_name)+" "+xstr(one_line.model_number)+" "+xstr(one_line.color))
        sheet.write(sheet_iterator, 6, one_line.weight)
        sheet.write(sheet_iterator, 7, round(courierCost))
        sheet.write(sheet_iterator, 8, one_line.risky)
        sheet.write(sheet_iterator, 9, supc_data.ourSp)
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
        sheet.write(sheet_iterator, 10, supc_data.ourOfferPrice)
        sheet.write(sheet_iterator, 12, supc_data.rank)
        sheet.write(sheet_iterator, 13, supc_data.lowestSellerName)
        sheet.write(sheet_iterator, 14, supc_data.lowestSp)
        if one_line.parent_category ==10011:
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
        else:
            lowestTp = supc_data.lowestSp*0.9497-courierCost
        sheet.write(sheet_iterator, 15, round(lowestTp))
        sheet.write(sheet_iterator, 16, supc_data.lowestOfferPrice)
        sheet.write(sheet_iterator, 17, supc_data.otherInventory)
        sheet.write(sheet_iterator, 18, supc_data.ourInventory)
        sheet.write(sheet_iterator, 19, one_line.our_nlc)
        sheet.write(sheet_iterator, 20, one_line.dealerPrice)
        if supc_data.rank==1:
            i+=1
            sheet_iterator+=1
            continue
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
        competitionBasis ='TP'
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice==supc_data.ourSp:
            competitionBasis ='SP'
            sheet.write(sheet_iterator, 23, 'SP')
        else:
            sheet.write(sheet_iterator, 23, 'TP')
        proposed_tp = 0

        if competitionBasis=='SP':
            if supc_data.lowestSp > lowest_possible_sp:
                sheet.write(sheet_iterator, 24, "Yes")
            else:
                sheet.write(sheet_iterator, 24, "No")
            proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
            proposed_tp = proposed_sp*.9597-courierCost
            sheet.write(sheet_iterator, 25, round(proposed_tp))
            sheet.write(sheet_iterator, 26, round(proposed_sp))
        else:
            if lowestTp > lowest_possible_tp:
                sheet.write(sheet_iterator, 24, "Yes")
            else:
                sheet.write(sheet_iterator, 24, "No")
            proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
            sheet.write(sheet_iterator, 25, round(proposed_tp))
            sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
        
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
            sheet.write(sheet_iterator, 27, "HIGH")
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
            sheet.write(sheet_iterator, 27, "MEDIUM")
        else:
            sheet.write(sheet_iterator, 27, "LOW")
        i= i+1
        sheet_iterator+=1


def createSheetForCometitiveItemsNoInventory(competitiveNoInventoryItems,wbk):
    sheet = wbk.add_sheet('Can Compete-No Inventory')

    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, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "SUPC", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 10, "Our Offer Price", heading_xf)
    sheet.write(0, 12, "Our Rank", heading_xf)
    sheet.write(0, 13, "Lowest Seller", heading_xf)
    sheet.write(0, 14, "Lowest SP", heading_xf)
    sheet.write(0, 15, "Lowest TP", heading_xf)
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 19, "Our NLC", heading_xf)
    sheet.write(0, 20, "Our Dealer Price", heading_xf)
    sheet.write(0, 21, "Lowest Possible TP", heading_xf)
    sheet.write(0, 22, "Lowest Possible SP", heading_xf)
    sheet.write(0, 23, "Competition Basis ", heading_xf)
    sheet.write(0, 24, "Can Compete", heading_xf)
    sheet.write(0, 25, "Target TP", heading_xf)
    sheet.write(0, 26, "Target SP", heading_xf)  
    sheet.write(0, 27, "Sales Potential", heading_xf)
    
    i, sheet_iterator=1,1
    for data in competitiveNoInventoryItems:
        supc_data =data[0]
        one_line = data[1]
        
        courierCost = 45
        
        if one_line.weight:
            slab = int(((one_line.weight+0.05) - .001)/.5)
        for x in range (0,slab):
            courierCost = courierCost + 35
        
        courierCost = courierCost * 1.1236
        
        
        sheet.write(sheet_iterator, 0, one_line.item_id)
        sheet.write(sheet_iterator, 1, one_line.category_name)
        sheet.write(sheet_iterator, 2, one_line.product_group)
        sheet.write(sheet_iterator, 3, supc_data.supc)
        sheet.write(sheet_iterator, 4, one_line.brand)
        sheet.write(sheet_iterator, 5, xstr(one_line.brand)+" "+xstr(one_line.model_name)+" "+xstr(one_line.model_number)+" "+xstr(one_line.color))
        sheet.write(sheet_iterator, 6, one_line.weight)
        sheet.write(sheet_iterator, 7, round(courierCost))
        sheet.write(sheet_iterator, 8, one_line.risky)
        sheet.write(sheet_iterator, 9, supc_data.ourSp)
        sheet.write(sheet_iterator, 11, '-')
        sheet.write(sheet_iterator, 10, supc_data.ourOfferPrice)
        sheet.write(sheet_iterator, 12, supc_data.rank)
        sheet.write(sheet_iterator, 13, supc_data.lowestSellerName)
        sheet.write(sheet_iterator, 14, supc_data.lowestSp)
        if one_line.parent_category ==10011:
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
        else:
            lowestTp = supc_data.lowestSp*0.9497-courierCost
        sheet.write(sheet_iterator, 15, round(lowestTp))
        sheet.write(sheet_iterator, 16, supc_data.lowestOfferPrice)
        sheet.write(sheet_iterator, 17, supc_data.otherInventory)
        sheet.write(sheet_iterator, 18, supc_data.ourInventory)
        sheet.write(sheet_iterator, 19, one_line.our_nlc)
        sheet.write(sheet_iterator, 20, one_line.dealerPrice)
        if supc_data.rank==1:
            i+=1
            sheet_iterator+=1
            continue
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
            competitionBasis ='SP'
            sheet.write(sheet_iterator, 23, 'SP')
        else:
            competitionBasis ='TP'
            sheet.write(sheet_iterator, 23, 'TP')
        proposed_tp = 0
        if competitionBasis=='SP':
            if supc_data.lowestSp > lowest_possible_sp:
                sheet.write(sheet_iterator, 24, "Yes")
            else:
                sheet.write(sheet_iterator, 24, "No")
            proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
            proposed_tp = proposed_sp*.9597-courierCost
            sheet.write(sheet_iterator, 25, round(proposed_tp))
            sheet.write(sheet_iterator, 26, round(proposed_sp))
        else:    
            if lowestTp > lowest_possible_tp:
                sheet.write(sheet_iterator, 24, "Yes")
                if competitionBasis=='SP':
                    proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
                    proposed_tp = proposed_sp*.9597-courierCost
                    sheet.write(sheet_iterator, 25, round(proposed_tp))
                    sheet.write(sheet_iterator, 26, round(proposed_sp))
                else:
                    proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
                    sheet.write(sheet_iterator, 25, round(proposed_tp))
                    sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
        
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
            sheet.write(sheet_iterator, 27, "HIGH")
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
            sheet.write(sheet_iterator, 27, "MEDIUM")
        else:
            sheet.write(sheet_iterator, 27, "LOW")
        i= i+1
        sheet_iterator+=1
    
def createSheetForNegativeMargin(negativeMargin,wbk):
    sheet = wbk.add_sheet('Negative Margin')

    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, "Category", heading_xf)
    sheet.write(0, 2, "Product Group.", heading_xf)
    sheet.write(0, 3, "SUPC", heading_xf)
    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Weight", heading_xf)
    sheet.write(0, 7, "Courier Cost", heading_xf)
    sheet.write(0, 8, "Risky", heading_xf)
    sheet.write(0, 9, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 10, "Our Offer Price", heading_xf)
    sheet.write(0, 12, "Our Rank", heading_xf)
    sheet.write(0, 13, "Our NLC", heading_xf)
    sheet.write(0, 14, "Our Dealer Price", heading_xf)
    sheet.write(0, 15, "Margin", heading_xf)
    
    i, sheet_iterator=1,1
    for data in negativeMargin:
        supc_data =data[0]
        one_line = data[1]
        
        courierCost = 45
        
        if one_line.weight:
            slab = int(((one_line.weight+0.05) - .001)/.5)
        for x in range (0,slab):
            courierCost = courierCost + 35
        
        courierCost = courierCost * 1.1236
        
        '''if one_line.parent_category ==10011:
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
        else:
            lowestTp = supc_data.lowestSp*0.9497-courierCost
        '''
        
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
        
        sheet.write(sheet_iterator, 0, one_line.item_id)
        sheet.write(sheet_iterator, 1, one_line.category_name)
        sheet.write(sheet_iterator, 2, one_line.product_group)
        sheet.write(sheet_iterator, 3, supc_data.supc)
        sheet.write(sheet_iterator, 4, one_line.brand)
        sheet.write(sheet_iterator, 5, xstr(one_line.brand)+" "+xstr(one_line.model_name)+" "+xstr(one_line.model_number)+" "+xstr(one_line.color))
        sheet.write(sheet_iterator, 6, one_line.weight)
        sheet.write(sheet_iterator, 7, round(courierCost))
        sheet.write(sheet_iterator, 8, one_line.risky)
        sheet.write(sheet_iterator, 9, supc_data.ourSp)
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
        sheet.write(sheet_iterator, 10, supc_data.ourOfferPrice)
        sheet.write(sheet_iterator, 12, supc_data.rank)
        sheet.write(sheet_iterator, 13, one_line.our_nlc)
        sheet.write(sheet_iterator, 14, one_line.dealerPrice)
        sheet.write(sheet_iterator, 15, round((supc_data.ourSp*0.9597-courierCost) - lowest_possible_tp))
        i= i+1
        sheet_iterator+=1
       

def main():
    parser = optparse.OptionParser()
    parser.add_option("-f", "--file", dest="filename",
                   default="ItemList.xls", type="string",
                   help="Read the item list from FILE",
                   metavar="FILE")
    (options, args) = parser.parse_args()
    if len(args) != 0:
        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
    filename = options.filename
    read_data(filename)

if __name__ == '__main__':
    main()
Subversion Repositories SmartDukaan

(root)/trunk/PyProj/src/shop2020/model/v1/catalog/script/Snapdeal.py @ 34412 – Rev 9856
