Subversion Repositories SmartDukaan

Rev

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