Subversion Repositories SmartDukaan

Rev

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