| 9334 |
kshitij.so |
1 |
#!/usr/bin/python
|
|
|
2 |
# coding: ascii
|
|
|
3 |
from elixir import *
|
|
|
4 |
from shop2020.model.v1.catalog.impl import DataService
|
|
|
5 |
from shop2020.model.v1.catalog.impl.DataService import Item, Category,\
|
|
|
6 |
SnapdealItem
|
|
|
7 |
import csv
|
|
|
8 |
import datetime
|
|
|
9 |
import optparse
|
|
|
10 |
import xlrd
|
|
|
11 |
from shop2020.clients.InventoryClient import InventoryClient
|
|
|
12 |
import xlwt
|
|
|
13 |
import urllib2
|
|
|
14 |
import time
|
|
|
15 |
import simplejson as json
|
|
|
16 |
|
|
|
17 |
|
|
|
18 |
if __name__ == '__main__' and __package__ is None:
|
|
|
19 |
import sys
|
|
|
20 |
import os
|
|
|
21 |
sys.path.insert(0, os.getcwd())
|
|
|
22 |
|
|
|
23 |
|
|
|
24 |
class _SnapdealItemInfo:
|
|
|
25 |
|
|
|
26 |
def __init__(self, item_id, product_group, category_name, our_nlc, brand, model_name, model_number, color, weight, parent_category):
|
|
|
27 |
self.item_id = item_id
|
|
|
28 |
self.product_group = product_group
|
|
|
29 |
self.category_name = category_name
|
|
|
30 |
self.our_nlc = our_nlc
|
|
|
31 |
self.brand = brand
|
|
|
32 |
self.model_name = model_name
|
|
|
33 |
self.model_number = model_number
|
|
|
34 |
self.color = color
|
|
|
35 |
self.weight = weight
|
|
|
36 |
self.parent_category = parent_category
|
|
|
37 |
|
|
|
38 |
|
|
|
39 |
class SnapdealDetails:
|
|
|
40 |
def __init__(self, supc, ourSp, offerPrice, ourInventory, otherInventory, rank, lowestSellerName,lowestSp,secondLowestSellerName,secondLowestSellerSp,secondLowestSellerInventory):
|
|
|
41 |
self.supc = supc
|
|
|
42 |
self.ourSp = ourSp
|
|
|
43 |
self.offerPrice = offerPrice
|
|
|
44 |
self.ourInventory = ourInventory
|
|
|
45 |
self.otherInventory = otherInventory
|
|
|
46 |
self.rank = rank
|
|
|
47 |
self.lowestSellerName = lowestSellerName
|
|
|
48 |
self.lowestSp = lowestSp
|
|
|
49 |
self.secondLowestSellerName = secondLowestSellerName
|
|
|
50 |
self.secondLowestSellerSp = secondLowestSellerSp
|
|
|
51 |
self.secondLowestSellerInventory = secondLowestSellerInventory
|
|
|
52 |
|
|
|
53 |
|
|
|
54 |
def fetchDetails(supc_code):
|
|
|
55 |
url="http://www.snapdeal.com/json/gvbps?supc=%s&catId=91"%(supc_code)
|
|
|
56 |
print url
|
|
|
57 |
time.sleep(2)
|
|
|
58 |
req = urllib2.Request(url)
|
|
|
59 |
response = urllib2.urlopen(req)
|
|
|
60 |
print response
|
|
|
61 |
json_input = response.read()
|
|
|
62 |
vendorInfo = json.loads(json_input)
|
|
|
63 |
rank ,otherInventory ,ourInventory, offerPrice, ourSp,iterator = 0, 0, 0, 0, 0, 0
|
| 9337 |
kshitij.so |
64 |
secondLowestSellerName=''
|
|
|
65 |
secondLowestSellerSp=0
|
|
|
66 |
secondLowestSellerInventory=0
|
| 9334 |
kshitij.so |
67 |
for vendor in vendorInfo:
|
|
|
68 |
if iterator == 0:
|
|
|
69 |
lowestSellerName = vendor['vendorDisplayName']
|
|
|
70 |
lowestSp = vendor['sellingPrice']
|
|
|
71 |
|
|
|
72 |
if iterator ==1:
|
|
|
73 |
secondLowestSellerName = vendor['vendorDisplayName']
|
|
|
74 |
secondLowestSellerSp = vendor['sellingPrice']
|
|
|
75 |
secondLowestSellerInventory = vendor['buyableInventory']
|
|
|
76 |
|
|
|
77 |
if vendor['vendorDisplayName'] == 'MobilesnMore':
|
|
|
78 |
ourInventory = vendor['buyableInventory']
|
|
|
79 |
ourSp = vendor['sellingPrice']
|
|
|
80 |
rank = iterator +1
|
|
|
81 |
else:
|
|
|
82 |
if rank==0:
|
|
|
83 |
otherInventory = otherInventory +vendor['buyableInventory']
|
|
|
84 |
|
|
|
85 |
iterator+=1
|
|
|
86 |
snapdealDetails = SnapdealDetails(supc_code,ourSp,offerPrice,ourInventory,otherInventory,rank,lowestSellerName,lowestSp,secondLowestSellerName,secondLowestSellerSp,secondLowestSellerInventory)
|
|
|
87 |
return snapdealDetails
|
|
|
88 |
|
|
|
89 |
|
|
|
90 |
def read_data(filename):
|
|
|
91 |
all_lines = []
|
|
|
92 |
all_supc=[]
|
|
|
93 |
iclient = InventoryClient().get_client()
|
| 9337 |
kshitij.so |
94 |
DataService.initialize('catalog','192.168.166.135')
|
| 9334 |
kshitij.so |
95 |
workbook = xlrd.open_workbook(filename)
|
|
|
96 |
sheet = workbook.sheet_by_index(0)
|
|
|
97 |
num_rows = sheet.nrows
|
|
|
98 |
for rownum in range(1, num_rows):
|
|
|
99 |
print sheet.row_values(rownum)
|
|
|
100 |
item_id = int(sheet.row_values(rownum)[0])
|
|
|
101 |
supc = sheet.row_values(rownum)[1]
|
|
|
102 |
item = Item.query.filter_by(id=item_id).one()
|
|
|
103 |
category = Category.query.filter_by(id=item.category).one()
|
|
|
104 |
snapdeal_item = SnapdealItem.query.filter_by(item_id=item_id).one()
|
|
|
105 |
warehouse = iclient.getWarehouse(snapdeal_item.warehouseId)
|
|
|
106 |
item_pricing = iclient.getItemPricing(item_id, warehouse.vendor.id)
|
|
|
107 |
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)
|
|
|
108 |
all_supc.append(supc)
|
|
|
109 |
all_lines.append(one_line)
|
|
|
110 |
write_report("/tmp/snapdeal_running.xls", all_lines, all_supc)
|
|
|
111 |
|
|
|
112 |
|
|
|
113 |
|
|
|
114 |
def write_report(filename, all_lines, all_supc):
|
|
|
115 |
|
|
|
116 |
buyBoxItems = []
|
|
|
117 |
|
|
|
118 |
wbk = xlwt.Workbook()
|
|
|
119 |
sheet = wbk.add_sheet('main')
|
|
|
120 |
|
|
|
121 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
122 |
|
|
|
123 |
excel_integer_format = '0'
|
|
|
124 |
integer_style = xlwt.XFStyle()
|
|
|
125 |
integer_style.num_format_str = excel_integer_format
|
|
|
126 |
|
|
|
127 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
128 |
sheet.write(0, 1, "Category", heading_xf)
|
|
|
129 |
sheet.write(0, 2, "Product Group.", heading_xf)
|
|
|
130 |
sheet.write(0, 3, "SUPC", heading_xf)
|
|
|
131 |
sheet.write(0, 4, "Brand", heading_xf)
|
|
|
132 |
sheet.write(0, 5, "Model Name", heading_xf)
|
|
|
133 |
sheet.write(0, 6, "Model Number", heading_xf)
|
|
|
134 |
sheet.write(0, 7, "Color", heading_xf)
|
|
|
135 |
sheet.write(0, 8, "Our SP", heading_xf)
|
|
|
136 |
sheet.write(0, 9, "Our TP", heading_xf)
|
|
|
137 |
sheet.write(0, 10, "Offer Price", heading_xf)
|
|
|
138 |
sheet.write(0, 11, "Our Rank", heading_xf)
|
|
|
139 |
sheet.write(0, 12, "Lowest Seller", heading_xf)
|
|
|
140 |
sheet.write(0, 13, "Lowest SP", heading_xf)
|
|
|
141 |
sheet.write(0, 14, "Lowest TP", heading_xf)
|
|
|
142 |
sheet.write(0, 15, "Lowest Offer Price", heading_xf)
|
|
|
143 |
sheet.write(0, 16, "Inventory of Top Vendors", heading_xf)
|
|
|
144 |
sheet.write(0, 17, "Our Inventory", heading_xf)
|
|
|
145 |
sheet.write(0, 18, "Our NLC", heading_xf)
|
|
|
146 |
sheet.write(0, 19, "Lowest Possible TP", heading_xf)
|
|
|
147 |
sheet.write(0, 20, "Can Compete", heading_xf)
|
|
|
148 |
sheet.write(0, 21, "Proposed TP", heading_xf)
|
|
|
149 |
sheet.write(0, 22, "Proposed SP", heading_xf)
|
|
|
150 |
|
| 9337 |
kshitij.so |
151 |
i, sheet_iterator=1,1
|
| 9334 |
kshitij.so |
152 |
for one_line in all_lines:
|
|
|
153 |
supc = all_supc[i-1]
|
|
|
154 |
supc_data = fetchDetails(supc)
|
|
|
155 |
|
|
|
156 |
courierCost = 45
|
|
|
157 |
|
|
|
158 |
if one_line.weight:
|
|
|
159 |
slab = int((one_line.weight - .001)/.5)
|
|
|
160 |
for x in range (0,slab):
|
|
|
161 |
courierCost = courierCost + 35
|
|
|
162 |
|
|
|
163 |
courierCost = courierCost * 1.1236
|
|
|
164 |
|
|
|
165 |
if supc_data.rank==1:
|
|
|
166 |
temp = []
|
|
|
167 |
temp.append(supc_data)
|
|
|
168 |
temp.append(one_line)
|
|
|
169 |
buyBoxItems.append(temp)
|
|
|
170 |
i+=1
|
|
|
171 |
continue
|
|
|
172 |
|
| 9337 |
kshitij.so |
173 |
sheet.write(sheet_iterator, 0, one_line.item_id)
|
|
|
174 |
sheet.write(sheet_iterator, 1, one_line.category_name)
|
|
|
175 |
sheet.write(sheet_iterator, 2, one_line.product_group)
|
|
|
176 |
sheet.write(sheet_iterator, 3, supc)
|
|
|
177 |
sheet.write(sheet_iterator, 4, one_line.brand)
|
|
|
178 |
sheet.write(sheet_iterator, 5, one_line.model_name)
|
|
|
179 |
sheet.write(sheet_iterator, 6, one_line.model_number)
|
|
|
180 |
sheet.write(sheet_iterator, 7, one_line.color)
|
|
|
181 |
sheet.write(sheet_iterator, 8, supc_data.ourSp)
|
|
|
182 |
sheet.write(sheet_iterator, 9, round(supc_data.ourSp*0.9597-courierCost))
|
|
|
183 |
sheet.write(sheet_iterator, 10, supc_data.offerPrice)
|
|
|
184 |
sheet.write(sheet_iterator, 11, supc_data.rank)
|
|
|
185 |
sheet.write(sheet_iterator, 12, supc_data.lowestSellerName)
|
|
|
186 |
sheet.write(sheet_iterator, 13, supc_data.lowestSp)
|
| 9334 |
kshitij.so |
187 |
if one_line.parent_category ==10011:
|
|
|
188 |
lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
|
|
|
189 |
else:
|
|
|
190 |
lowestTp = supc_data.lowestSp*0.9497-courierCost
|
| 9337 |
kshitij.so |
191 |
sheet.write(sheet_iterator, 14, round(lowestTp))
|
|
|
192 |
sheet.write(sheet_iterator, 15, )
|
|
|
193 |
sheet.write(sheet_iterator, 16, supc_data.otherInventory)
|
|
|
194 |
sheet.write(sheet_iterator, 17, supc_data.ourInventory)
|
|
|
195 |
sheet.write(sheet_iterator, 18, one_line.our_nlc)
|
| 9334 |
kshitij.so |
196 |
if supc_data.rank==1:
|
|
|
197 |
i+=1
|
| 9337 |
kshitij.so |
198 |
sheet_iterator+=1
|
| 9334 |
kshitij.so |
199 |
continue
|
|
|
200 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
| 9337 |
kshitij.so |
201 |
sheet.write(sheet_iterator, 19, round(lowest_possible_tp))
|
| 9334 |
kshitij.so |
202 |
proposed_tp = 0
|
|
|
203 |
if lowestTp > lowest_possible_tp:
|
| 9337 |
kshitij.so |
204 |
sheet.write(sheet_iterator, 20, "Yes")
|
|
|
205 |
proposed_tp = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
|
|
|
206 |
sheet.write(sheet_iterator, 21, round(proposed_tp))
|
|
|
207 |
sheet.write(sheet_iterator, 22, round((proposed_tp+courierCost)/0.9597))
|
| 9334 |
kshitij.so |
208 |
else:
|
| 9337 |
kshitij.so |
209 |
sheet.write(sheet_iterator, 20, "No")
|
| 9334 |
kshitij.so |
210 |
i= i+1
|
| 9337 |
kshitij.so |
211 |
sheet_iterator+=1
|
|
|
212 |
|
| 9334 |
kshitij.so |
213 |
createSheetForBuyBoxItems(buyBoxItems,wbk)
|
|
|
214 |
wbk.save(filename)
|
|
|
215 |
|
|
|
216 |
def createSheetForBuyBoxItems(buyBoxItems,wbk):
|
|
|
217 |
sheet = wbk.add_sheet('BuyBoxItems')
|
|
|
218 |
|
|
|
219 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
220 |
|
|
|
221 |
excel_integer_format = '0'
|
|
|
222 |
integer_style = xlwt.XFStyle()
|
|
|
223 |
integer_style.num_format_str = excel_integer_format
|
|
|
224 |
|
|
|
225 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
226 |
sheet.write(0, 1, "Category", heading_xf)
|
|
|
227 |
sheet.write(0, 2, "Product Group.", heading_xf)
|
|
|
228 |
sheet.write(0, 3, "SUPC", heading_xf)
|
|
|
229 |
sheet.write(0, 4, "Brand", heading_xf)
|
|
|
230 |
sheet.write(0, 5, "Model Name", heading_xf)
|
|
|
231 |
sheet.write(0, 6, "Model Number", heading_xf)
|
|
|
232 |
sheet.write(0, 7, "Color", heading_xf)
|
|
|
233 |
sheet.write(0, 8, "Our SP", heading_xf)
|
|
|
234 |
sheet.write(0, 9, "Our TP", heading_xf)
|
|
|
235 |
sheet.write(0, 10, "Offer Price", heading_xf)
|
|
|
236 |
sheet.write(0, 11, "Our Rank", heading_xf)
|
|
|
237 |
sheet.write(0, 12, "Lowest Seller", heading_xf)
|
|
|
238 |
sheet.write(0, 13, "Lowest Offer Price", heading_xf)
|
|
|
239 |
sheet.write(0, 14, "Second Lowest Seller", heading_xf)
|
|
|
240 |
sheet.write(0, 15, "Second Lowest Price", heading_xf)
|
|
|
241 |
sheet.write(0, 16, "Our Inventory", heading_xf)
|
|
|
242 |
sheet.write(0, 17, "Second Lowest Seller Inventory", heading_xf)
|
|
|
243 |
sheet.write(0, 18, "Our NLC", heading_xf)
|
|
|
244 |
sheet.write(0, 19, "Second Lowest Seller TP", heading_xf)
|
|
|
245 |
|
|
|
246 |
i=1
|
|
|
247 |
|
|
|
248 |
for data in buyBoxItems:
|
|
|
249 |
supc_data =data[0]
|
|
|
250 |
one_line = data[1]
|
|
|
251 |
|
|
|
252 |
courierCost = 45
|
|
|
253 |
|
|
|
254 |
if one_line.weight:
|
|
|
255 |
slab = int((one_line.weight - .001)/.5)
|
|
|
256 |
for x in range (0,slab):
|
|
|
257 |
courierCost = courierCost + 35
|
|
|
258 |
|
|
|
259 |
courierCost = courierCost * 1.1236
|
|
|
260 |
|
|
|
261 |
sheet.write(i, 0, one_line.item_id)
|
|
|
262 |
sheet.write(i, 1, one_line.category_name)
|
|
|
263 |
sheet.write(i, 2, one_line.product_group)
|
|
|
264 |
sheet.write(i, 3, supc_data.supc)
|
|
|
265 |
sheet.write(i, 4, one_line.brand)
|
|
|
266 |
sheet.write(i, 5, one_line.model_name)
|
|
|
267 |
sheet.write(i, 6, one_line.model_number)
|
|
|
268 |
sheet.write(i, 7, one_line.color)
|
|
|
269 |
sheet.write(i, 8, supc_data.ourSp)
|
|
|
270 |
sheet.write(i, 9, round(supc_data.ourSp*0.9597-courierCost))
|
|
|
271 |
sheet.write(i, 10, supc_data.offerPrice)
|
|
|
272 |
sheet.write(i, 11, supc_data.rank)
|
|
|
273 |
sheet.write(i, 12, supc_data.lowestSellerName)
|
|
|
274 |
sheet.write(i, 13, )
|
|
|
275 |
sheet.write(i, 14, supc_data.secondLowestSellerName)
|
|
|
276 |
sheet.write(i, 15, supc_data.secondLowestSellerSp)
|
|
|
277 |
sheet.write(i, 16, supc_data.ourInventory)
|
|
|
278 |
sheet.write(i, 17, supc_data.secondLowestSellerInventory)
|
|
|
279 |
sheet.write(i, 18, one_line.our_nlc)
|
|
|
280 |
if one_line.parent_category ==10011:
|
|
|
281 |
lowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
|
|
|
282 |
else:
|
|
|
283 |
lowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
|
|
|
284 |
sheet.write(i, 19, round(lowestTp))
|
|
|
285 |
i+=1
|
|
|
286 |
|
|
|
287 |
|
|
|
288 |
def main():
|
|
|
289 |
parser = optparse.OptionParser()
|
|
|
290 |
parser.add_option("-f", "--file", dest="filename",
|
|
|
291 |
default="ItemList.xls", type="string",
|
|
|
292 |
help="Read the item list from FILE",
|
|
|
293 |
metavar="FILE")
|
|
|
294 |
(options, args) = parser.parse_args()
|
|
|
295 |
if len(args) != 0:
|
|
|
296 |
parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
|
|
|
297 |
filename = options.filename
|
|
|
298 |
read_data(filename)
|
|
|
299 |
|
|
|
300 |
if __name__ == '__main__':
|
|
|
301 |
main()
|
|
|
302 |
|