Rev 9387 | Rev 9391 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
#!/usr/bin/python# coding: asciifrom elixir import *from shop2020.model.v1.catalog.impl import DataServicefrom shop2020.model.v1.catalog.impl.DataService import Item, Category,\SnapdealItemfrom shop2020.thriftpy.model.v1.inventory import ttypesfrom shop2020.thriftpy.model.v1.inventory.ttypes import WarehouseType, InventoryTypeimport csvimport datetimeimport optparseimport xlrdfrom shop2020.clients.InventoryClient import InventoryClientimport xlwtimport urllib2import timeimport simplejson as jsonfrom shop2020.utils import EmailAttachmentSenderfrom shop2020.utils.EmailAttachmentSender import get_attachment_partif __name__ == '__main__' and __package__ is None:import sysimport ossys.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_idself.product_group = product_groupself.category_name = category_nameself.our_nlc = our_nlcself.brand = brandself.model_name = model_nameself.model_number = model_numberself.color = colorself.weight = weightself.parent_category = parent_categoryself.risky = riskyclass SnapdealDetails:def __init__(self, supc, ourSp, offerPrice, ourInventory, otherInventory, rank, lowestSellerName,lowestSp,secondLowestSellerName,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice):self.supc = supcself.ourSp = ourSpself.offerPrice = offerPriceself.ourInventory = ourInventoryself.otherInventory = otherInventoryself.rank = rankself.lowestSellerName = lowestSellerNameself.lowestSp = lowestSpself.secondLowestSellerName = secondLowestSellerNameself.secondLowestSellerSp = secondLowestSellerSpself.secondLowestSellerInventory = secondLowestSellerInventoryself.lowestOfferPrice = lowestOfferPriceself.secondLowestSellerOfferPrice = secondLowestSellerOfferPriceself.ourOfferPrice = ourOfferPricedef fetchDetails(supc_code):url="http://www.snapdeal.com/json/gvbps?supc=%s&catId=91"%(supc_code)print urltime.sleep(2)req = urllib2.Request(url)response = urllib2.urlopen(req)print responsejson_input = response.read()vendorInfo = json.loads(json_input)rank ,otherInventory ,ourInventory, offerPrice, ourSp,iterator = 0, 0, 0, 0, 0, 0secondLowestSellerName=''secondLowestSellerSp=0secondLowestSellerInventory=0lowestOfferPrice = 0secondLowestSellerOfferPrice = 0ourOfferPrice = 0for 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 +1else:if rank==0:otherInventory = otherInventory +vendor['buyableInventory']iterator+=1snapdealDetails = SnapdealDetails(supc_code,ourSp,offerPrice,ourInventory,otherInventory,rank,lowestSellerName,lowestSp,secondLowestSellerName,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice)return snapdealDetailsdef 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.nrowsfor 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_formatsheet.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,1for one_line in all_lines:supc = all_supc[i-1]supc_data = fetchDetails(supc)courierCost = 45if one_line.weight:slab = int(((one_line.weight+0.05) - .001)/.5)for x in range (0,slab):courierCost = courierCost + 35courierCost = courierCost * 1.1236if supc_data.rank==1:temp = []temp.append(supc_data)temp.append(one_line)buyBoxItems.append(temp)i+=1continueif one_line.parent_category ==10011:lowestTp = (supc_data.lowestSp*(1-.0803))-courierCostelse:lowestTp = supc_data.lowestSp*0.9497-courierCostlowest_possible_tp = one_line.our_nlc/0.988+15+6if (lowestTp > lowest_possible_tp):temp=[]temp.append(supc_data)temp.append(one_line)competitive.append(temp)i+=1continuesheet.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))-courierCostelse:lowestTp = supc_data.lowestSp*0.9497-courierCostsheet.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+=1sheet_iterator+=1continuelowest_possible_tp = one_line.our_nlc/0.988+15+6lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597sheet.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):competitionBasis ='SP'sheet.write(sheet_iterator, 23, 'SP')else:sheet.write(sheet_iterator, 23, 'TP')proposed_tp = 0if 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*.09597-courierCostsheet.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 = 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))sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))else:sheet.write(sheet_iterator, 24, "No")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+1sheet_iterator+=1createSheetForBuyBoxItems(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_formatsheet.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=1for data in buyBoxItems:supc_data =data[0]one_line = data[1]courierCost = 45if one_line.weight:slab = int(((one_line.weight+0.05) - .001)/.5)for x in range (0,slab):courierCost = courierCost + 35courierCost = courierCost * 1.1236sheet.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))-courierCostelse:lowestTp = supc_data.secondLowestSellerSp*0.9497-courierCostlowest_possible_tp = one_line.our_nlc/0.988+15+6proposed_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+=1def 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_formatsheet.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,1for data in competitiveItems:supc_data =data[0]one_line = data[1]courierCost = 45if one_line.weight:slab = int(((one_line.weight+0.05) - .001)/.5)for x in range (0,slab):courierCost = courierCost + 35courierCost = courierCost * 1.1236sheet.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))-courierCostelse:lowestTp = supc_data.lowestSp*0.9497-courierCostsheet.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+=1sheet_iterator+=1continuelowest_possible_tp = one_line.our_nlc/0.988+15+6lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597sheet.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):competitionBasis ='SP'sheet.write(sheet_iterator, 23, 'SP')else:sheet.write(sheet_iterator, 23, 'TP')proposed_tp = 0if 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*.09597-courierCostsheet.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 = 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))sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))else:sheet.write(sheet_iterator, 24, "No")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+1sheet_iterator+=1def 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.filenameread_data(filename)if __name__ == '__main__':main()