Subversion Repositories SmartDukaan

Rev

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