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
import csv
import datetime
import optparse
import xlrd
from shop2020.clients.InventoryClient import InventoryClient
import xlwt
import urllib2
import time
import simplejson as json


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

class SnapdealDetails:
    def __init__(self, supc, ourSp, offerPrice, ourInventory, otherInventory, rank, lowestSellerName,lowestSp,secondLowestSellerName,secondLowestSellerSp,secondLowestSellerInventory):
        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
        

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
    for vendor in vendorInfo:
        if iterator == 0:
            lowestSellerName = vendor['vendorDisplayName']
            lowestSp = vendor['sellingPrice']
            
        if iterator ==1:
            secondLowestSellerName = vendor['vendorDisplayName']
            secondLowestSellerSp = vendor['sellingPrice']
            secondLowestSellerInventory = vendor['buyableInventory']
            
        if vendor['vendorDisplayName'] == 'MobilesnMore':
            ourInventory = vendor['buyableInventory']
            ourSp = 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)
    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)
        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)
        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 = []
    
    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, "Our SP", heading_xf)
    sheet.write(0, 9, "Our TP", heading_xf)
    sheet.write(0, 10, "Offer Price", heading_xf)
    sheet.write(0, 11, "Our Rank", heading_xf)
    sheet.write(0, 12, "Lowest Seller", heading_xf)
    sheet.write(0, 13, "Lowest SP", heading_xf)
    sheet.write(0, 14, "Lowest TP", heading_xf)
    sheet.write(0, 15, "Lowest Offer Price", heading_xf)
    sheet.write(0, 16, "Inventory of Top Vendors", heading_xf)
    sheet.write(0, 17, "Our Inventory", heading_xf)
    sheet.write(0, 18, "Our NLC", heading_xf)
    sheet.write(0, 19, "Lowest Possible TP", heading_xf)
    sheet.write(0, 20, "Can Compete", heading_xf)
    sheet.write(0, 21, "Proposed TP", heading_xf)
    sheet.write(0, 22, "Proposed SP", 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 - .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
        
        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, supc_data.ourSp)
        sheet.write(sheet_iterator, 9, round(supc_data.ourSp*0.9597-courierCost))
        sheet.write(sheet_iterator, 10, supc_data.offerPrice)
        sheet.write(sheet_iterator, 11, supc_data.rank)
        sheet.write(sheet_iterator, 12, supc_data.lowestSellerName)
        sheet.write(sheet_iterator, 13, 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, 14, round(lowestTp))
        sheet.write(sheet_iterator, 15, )
        sheet.write(sheet_iterator, 16, supc_data.otherInventory)
        sheet.write(sheet_iterator, 17, supc_data.ourInventory)
        sheet.write(sheet_iterator, 18, 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
        sheet.write(sheet_iterator, 19, round(lowest_possible_tp))
        proposed_tp = 0
        if lowestTp > lowest_possible_tp:
            sheet.write(sheet_iterator, 20, "Yes")
            proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
            sheet.write(sheet_iterator, 21, round(proposed_tp))
            sheet.write(sheet_iterator, 22, round((proposed_tp+courierCost)/0.9597)) 
        else:
            sheet.write(sheet_iterator, 20, "No")
        i= i+1
        sheet_iterator+=1
        
    createSheetForBuyBoxItems(buyBoxItems,wbk)
    wbk.save(filename)

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, "Our SP", heading_xf)
    sheet.write(0, 9, "Our TP", heading_xf)
    sheet.write(0, 10, "Offer Price", heading_xf)
    sheet.write(0, 11, "Our Rank", heading_xf)
    sheet.write(0, 12, "Lowest Seller", heading_xf)
    sheet.write(0, 13, "Lowest Offer Price", heading_xf)
    sheet.write(0, 14, "Second Lowest Seller", heading_xf)
    sheet.write(0, 15, "Second Lowest Price", heading_xf)
    sheet.write(0, 16, "Our Inventory", heading_xf)
    sheet.write(0, 17, "Second Lowest Seller Inventory", heading_xf)
    sheet.write(0, 18, "Our NLC", heading_xf)
    sheet.write(0, 19, "Second Lowest Seller TP", 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 - .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, supc_data.ourSp)
        sheet.write(i, 9, round(supc_data.ourSp*0.9597-courierCost))
        sheet.write(i, 10, supc_data.offerPrice)
        sheet.write(i, 11, supc_data.rank)
        sheet.write(i, 12, supc_data.lowestSellerName)
        sheet.write(i, 13, )
        sheet.write(i, 14, supc_data.secondLowestSellerName)
        sheet.write(i, 15, supc_data.secondLowestSellerSp)
        sheet.write(i, 16, supc_data.ourInventory)
        sheet.write(i, 17, supc_data.secondLowestSellerInventory)
        sheet.write(i, 18, 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
        sheet.write(i, 19, round(lowestTp))
        i+=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()