Subversion Repositories SmartDukaan

Rev

Rev 9362 | Rev 9365 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

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