Subversion Repositories SmartDukaan

#!/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 csv
import datetime
import optparse
import xlrd
from shop2020.clients.InventoryClient import InventoryClient
import xlwt
import urllib2
import time
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):
        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
        

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)
    print response
    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)
        item_id = int(sheet.row_values(rownum)[0])
        supc = sheet.row_values(rownum)[1]
        item = Item.query.filter_by(id=item_id).one()
        category = Category.query.filter_by(id=item.category).one()
        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)
        #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)
        one_line = _SnapdealItemInfo(item.id, item.product_group, category.label, item_pricing.nlc, item.brand, item.model_name, item.model_number, item.color, item.weight, category.parent_category_id, item.risky)
        all_supc.append(supc)
        all_lines.append(one_line)
    write_report("/tmp/snapdeal_running.xls", all_lines, all_supc)
    
    

def write_report(filename, all_lines, all_supc):
    
    buyBoxItems = []
    competitive = []
    
    wbk = xlwt.Workbook()
    sheet = wbk.add_sheet('main')

    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, "Model Name", heading_xf)
    sheet.write(0, 6, "Model Number", heading_xf)
    sheet.write(0, 7, "Color", heading_xf)
    sheet.write(0, 8, "Weight", heading_xf)
    sheet.write(0, 9, "Risky", heading_xf)
    sheet.write(0, 10, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 12, "Our Offer Price", heading_xf)
    sheet.write(0, 13, "Our Rank", heading_xf)
    sheet.write(0, 14, "Lowest Seller", heading_xf)
    sheet.write(0, 15, "Lowest SP", heading_xf)
    sheet.write(0, 16, "Lowest TP", heading_xf)
    sheet.write(0, 17, "Lowest Offer Price", heading_xf)
    sheet.write(0, 18, "Inventory of Top Vendors", heading_xf)
    sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 20, "Our NLC", 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:
        supc = all_supc[i-1]
        supc_data = fetchDetails(supc)
        
        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 supc_data.rank==1:
            temp = []
            temp.append(supc_data)
            temp.append(one_line)
            buyBoxItems.append(temp)
            i+=1
            continue
        
        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 (lowestTp > lowest_possible_tp):
            temp=[]
            temp.append(supc_data)
            temp.append(one_line)
            competitive.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, one_line.model_name)
        sheet.write(sheet_iterator, 6, one_line.model_number)
        sheet.write(sheet_iterator, 7, one_line.color)
        sheet.write(sheet_iterator, 8, one_line.weight)
        sheet.write(sheet_iterator, 9, one_line.risky)
        sheet.write(sheet_iterator, 10, supc_data.ourSp)
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
        sheet.write(sheet_iterator, 13, supc_data.rank)
        sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
        sheet.write(sheet_iterator, 15, 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, 16, round(lowestTp))
        sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
        sheet.write(sheet_iterator, 18, supc_data.otherInventory)
        sheet.write(sheet_iterator, 19, supc_data.ourInventory)
        sheet.write(sheet_iterator, 20, one_line.our_nlc)
        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):
            sheet.write(sheet_iterator, 23, 'SP')
        else:
            sheet.write(sheet_iterator, 23, 'TP')
        proposed_tp = 0
        if lowestTp > lowest_possible_tp:
            sheet.write(sheet_iterator, 24, "Yes")
            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)) 
        else:
            sheet.write(sheet_iterator, 24, "No")
        
        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)
    wbk.save(filename)
    
    EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@shop2020.in"], " " , "", [get_attachment_part(filename)], ["kshitij.sood@shop2020.in"], [])

    

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

    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, "Model Name", heading_xf)
    sheet.write(0, 6, "Model Number", heading_xf)
    sheet.write(0, 7, "Color", heading_xf)
    sheet.write(0, 8, "Weight", heading_xf)
    sheet.write(0, 9, "Risky", heading_xf)
    sheet.write(0, 10, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 12, "Our Offer Price", heading_xf)
    sheet.write(0, 13, "Our Rank", heading_xf)
    sheet.write(0, 14, "Lowest Seller", heading_xf)
    sheet.write(0, 15, "Lowest Offer Price", heading_xf)
    sheet.write(0, 16, "Second Lowest Seller", heading_xf)
    sheet.write(0, 17, "Second Lowest Price", heading_xf)
    sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)
    sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 20, "Second Lowest Seller Inventory", heading_xf)
    sheet.write(0, 21, "Our NLC", heading_xf)
    sheet.write(0, 22, "Target TP", heading_xf)
    sheet.write(0, 23, "Target SP", heading_xf)
    sheet.write(0, 24, "Target NLC", 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, one_line.model_name)
        sheet.write(i, 6, one_line.model_number)
        sheet.write(i, 7, one_line.color)
        sheet.write(i, 8, one_line.weight)
        sheet.write(i, 9, one_line.risky)
        sheet.write(i, 10, supc_data.ourSp)
        sheet.write(i, 11, round(supc_data.ourSp*0.9597-courierCost))
        sheet.write(i, 12, supc_data.ourOfferPrice)
        sheet.write(i, 13, supc_data.rank)
        sheet.write(i, 14, supc_data.lowestSellerName)
        sheet.write(i, 15, supc_data.lowestOfferPrice)
        sheet.write(i, 16, supc_data.secondLowestSellerName)
        sheet.write(i, 17, supc_data.secondLowestSellerSp)
        sheet.write(i, 18, supc_data.secondLowestSellerOfferPrice)
        sheet.write(i, 19, supc_data.ourInventory)
        sheet.write(i, 20, supc_data.secondLowestSellerInventory)
        sheet.write(i, 21, one_line.our_nlc)
        if one_line.parent_category ==10011:
            lowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
        else:
            lowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
        proposed_tp  = max(lowestTp - max((20, lowestTp*0.002)), lowest_possible_tp)
        sheet.write(i, 22, round(proposed_tp))
        sheet.write(i, 23, round((proposed_tp+courierCost)/0.9597)) 
        sheet.write(i, 24, round(proposed_tp*0.988+21)) 
        sheet.write(i, 25, round(proposed_tp-one_line.our_nlc))     
        i+=1


def createSheetForCometitiveItems(competitiveItems,wbk):
    sheet = wbk.add_sheet('Competitive')

    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, "Model Name", heading_xf)
    sheet.write(0, 6, "Model Number", heading_xf)
    sheet.write(0, 7, "Color", heading_xf)
    sheet.write(0, 8, "Weight", heading_xf)
    sheet.write(0, 9, "Risky", heading_xf)
    sheet.write(0, 10, "Our SP", heading_xf)
    sheet.write(0, 11, "Our TP", heading_xf)
    sheet.write(0, 12, "Our Offer Price", heading_xf)
    sheet.write(0, 13, "Our Rank", heading_xf)
    sheet.write(0, 14, "Lowest Seller", heading_xf)
    sheet.write(0, 15, "Lowest SP", heading_xf)
    sheet.write(0, 16, "Lowest TP", heading_xf)
    sheet.write(0, 17, "Lowest Offer Price", heading_xf)
    sheet.write(0, 18, "Inventory of Top Vendors", heading_xf)
    sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
    sheet.write(0, 20, "Our NLC", 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 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, one_line.model_name)
        sheet.write(sheet_iterator, 6, one_line.model_number)
        sheet.write(sheet_iterator, 7, one_line.color)
        sheet.write(sheet_iterator, 8, one_line.weight)
        sheet.write(sheet_iterator, 9, one_line.risky)
        sheet.write(sheet_iterator, 10, supc_data.ourSp)
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
        sheet.write(sheet_iterator, 13, supc_data.rank)
        sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
        sheet.write(sheet_iterator, 15, 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, 16, round(lowestTp))
        sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
        sheet.write(sheet_iterator, 18, supc_data.otherInventory)
        sheet.write(sheet_iterator, 19, supc_data.ourInventory)
        sheet.write(sheet_iterator, 20, one_line.our_nlc)
        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):
            sheet.write(sheet_iterator, 23, 'SP')
        else:
            sheet.write(sheet_iterator, 23, 'TP')
        proposed_tp = 0
        if lowestTp > lowest_possible_tp:
            sheet.write(sheet_iterator, 24, "Yes")
            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)) 
        else:
            sheet.write(sheet_iterator, 24, "No")
        
        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

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