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 = []

    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, "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 Inventory", heading_xf)

    sheet.write(0, 20, "Our NLC", heading_xf)

    sheet.write(0, 21, "Lowest Possible TP", heading_xf)

    sheet.write(0, 22, "Can Compete", heading_xf)

    sheet.write(0, 23, "Proposed TP", heading_xf)

    sheet.write(0, 24, "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+.1) - .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, 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.offerPrice)

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

        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

        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))

        proposed_tp = 0

        if lowestTp > lowest_possible_tp:

            sheet.write(sheet_iterator, 22, "Yes")

            proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)

            sheet.write(sheet_iterator, 23, round(proposed_tp))

            sheet.write(sheet_iterator, 24, round((proposed_tp+courierCost)/0.9597)) 

        else:

            sheet.write(sheet_iterator, 22, "No")

        i= i+1

        sheet_iterator+=1

    createSheetForBuyBoxItems(buyBoxItems,wbk)

    wbk.save(filename)

    EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@shop2020.in"], "Insurance Details for date " , "Please find attached insurance details for today.", [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, "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, "Our 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, "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+.1) - .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.offerPrice)

        sheet.write(i, 13, supc_data.rank)

        sheet.write(i, 14, supc_data.lowestSellerName)

        sheet.write(i, 15, )

        sheet.write(i, 16, supc_data.secondLowestSellerName)

        sheet.write(i, 17, supc_data.secondLowestSellerSp)

        sheet.write(i, 18, supc_data.ourInventory)

        sheet.write(i, 19, supc_data.secondLowestSellerInventory)

        sheet.write(i, 20, 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, 21, 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()