Subversion Repositories SmartDukaan

Rev

Rev 9368 | Rev 9370 | 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)
167 
    sheet.write(0, 12, "Offer Price", heading_xf)
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)
174 
    sheet.write(0, 19, "Our Inventory", heading_xf)
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)
180 
    sheet.write(0, 25, "Proposed TP", heading_xf)
181 
    sheet.write(0, 26, "Proposed SP", heading_xf)
182 
    sheet.write(0, 27, "Sales Potential", heading_xf)
9334 kshitij.so 183 
 
9337 kshitij.so 184 
    i, sheet_iterator=1,1
9334 kshitij.so 185 
    for one_line in all_lines:
186 
        supc = all_supc[i-1]
187 
        supc_data = fetchDetails(supc)
188 
 
189 
        courierCost = 45
190 
 
191 
        if one_line.weight:
9364 kshitij.so 192 
            slab = int(((one_line.weight+.1) - .001)/.5)
9334 kshitij.so 193 
        for x in range (0,slab):
194 
            courierCost = courierCost + 35
195 
 
196 
        courierCost = courierCost * 1.1236
197 
 
198 
        if supc_data.rank==1:
199 
            temp = []
200 
            temp.append(supc_data)
201 
            temp.append(one_line)
202 
            buyBoxItems.append(temp)
203 
            i+=1
204 
            continue
205 
 
9337 kshitij.so 206 
        sheet.write(sheet_iterator, 0, one_line.item_id)
207 
        sheet.write(sheet_iterator, 1, one_line.category_name)
208 
        sheet.write(sheet_iterator, 2, one_line.product_group)
209 
        sheet.write(sheet_iterator, 3, supc)
210 
        sheet.write(sheet_iterator, 4, one_line.brand)
211 
        sheet.write(sheet_iterator, 5, one_line.model_name)
212 
        sheet.write(sheet_iterator, 6, one_line.model_number)
213 
        sheet.write(sheet_iterator, 7, one_line.color)
9359 kshitij.so 214 
        sheet.write(sheet_iterator, 8, one_line.weight)
215 
        sheet.write(sheet_iterator, 9, one_line.risky)
216 
        sheet.write(sheet_iterator, 10, supc_data.ourSp)
217 
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
218 
        sheet.write(sheet_iterator, 12, supc_data.offerPrice)
219 
        sheet.write(sheet_iterator, 13, supc_data.rank)
220 
        sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
221 
        sheet.write(sheet_iterator, 15, supc_data.lowestSp)
9334 kshitij.so 222 
        if one_line.parent_category ==10011:
223 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
224 
        else:
225 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
9359 kshitij.so 226 
        sheet.write(sheet_iterator, 16, round(lowestTp))
9368 kshitij.so 227 
        sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
9359 kshitij.so 228 
        sheet.write(sheet_iterator, 18, supc_data.otherInventory)
229 
        sheet.write(sheet_iterator, 19, supc_data.ourInventory)
230 
        sheet.write(sheet_iterator, 20, one_line.our_nlc)
9334 kshitij.so 231 
        if supc_data.rank==1:
232 
            i+=1
9337 kshitij.so 233 
            sheet_iterator+=1
9334 kshitij.so 234 
            continue
235 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
9366 kshitij.so 236 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
9359 kshitij.so 237 
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
9366 kshitij.so 238 
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
239 
        if (supc_data.ourOfferPrice == supc_data.ourSp):
240 
            sheet.write(sheet_iterator, 23, 'SP')
241 
        else:
242 
            sheet.write(sheet_iterator, 23, 'TP')
9334 kshitij.so 243 
        proposed_tp = 0
244 
        if lowestTp > lowest_possible_tp:
9366 kshitij.so 245 
            sheet.write(sheet_iterator, 24, "Yes")
9337 kshitij.so 246 
            proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
9366 kshitij.so 247 
            sheet.write(sheet_iterator, 25, round(proposed_tp))
248 
            sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
9334 kshitij.so 249 
        else:
9366 kshitij.so 250 
            sheet.write(sheet_iterator, 24, "No")
251 
 
252 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
253 
            sheet.write(sheet_iterator, 27, "HIGH")
254 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
255 
            sheet.write(sheet_iterator, 27, "MEDIUM")
256 
        else:
257 
            sheet.write(sheet_iterator, 27, "LOW")
9334 kshitij.so 258 
        i= i+1
9337 kshitij.so 259 
        sheet_iterator+=1
260 
 
9334 kshitij.so 261 
    createSheetForBuyBoxItems(buyBoxItems,wbk)
262 
    wbk.save(filename)
9365 kshitij.so 263 
 
9367 kshitij.so 264 
    EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@shop2020.in"], " " , "", [get_attachment_part(filename)], ["kshitij.sood@shop2020.in"], [])
9334 kshitij.so 265 
 
9365 kshitij.so 266 
 
267 
 
9334 kshitij.so 268 
def createSheetForBuyBoxItems(buyBoxItems,wbk):
269 
    sheet = wbk.add_sheet('BuyBoxItems')
270 
 
271 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
272 
 
273 
    excel_integer_format = '0'
274 
    integer_style = xlwt.XFStyle()
275 
    integer_style.num_format_str = excel_integer_format
276 
 
277 
    sheet.write(0, 0, "Item ID", heading_xf)
278 
    sheet.write(0, 1, "Category", heading_xf)
279 
    sheet.write(0, 2, "Product Group.", heading_xf)
280 
    sheet.write(0, 3, "SUPC", heading_xf)
281 
    sheet.write(0, 4, "Brand", heading_xf)
282 
    sheet.write(0, 5, "Model Name", heading_xf)
283 
    sheet.write(0, 6, "Model Number", heading_xf)
284 
    sheet.write(0, 7, "Color", heading_xf)
9359 kshitij.so 285 
    sheet.write(0, 8, "Weight", heading_xf)
286 
    sheet.write(0, 9, "Risky", heading_xf)
287 
    sheet.write(0, 10, "Our SP", heading_xf)
288 
    sheet.write(0, 11, "Our TP", heading_xf)
289 
    sheet.write(0, 12, "Offer Price", heading_xf)
290 
    sheet.write(0, 13, "Our Rank", heading_xf)
291 
    sheet.write(0, 14, "Lowest Seller", heading_xf)
292 
    sheet.write(0, 15, "Lowest Offer Price", heading_xf)
293 
    sheet.write(0, 16, "Second Lowest Seller", heading_xf)
294 
    sheet.write(0, 17, "Second Lowest Price", heading_xf)
9368 kshitij.so 295 
    sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)
296 
    sheet.write(0, 19, "Our Inventory", heading_xf)
297 
    sheet.write(0, 20, "Second Lowest Seller Inventory", heading_xf)
298 
    sheet.write(0, 21, "Our NLC", heading_xf)
299 
    sheet.write(0, 22, "Second Lowest Seller TP", heading_xf)
9334 kshitij.so 300 
 
301 
    i=1
302 
 
303 
    for data in buyBoxItems:
304 
        supc_data =data[0]
305 
        one_line = data[1]
306 
 
307 
        courierCost = 45
308 
 
309 
        if one_line.weight:
9364 kshitij.so 310 
            slab = int(((one_line.weight+.1) - .001)/.5)
9334 kshitij.so 311 
        for x in range (0,slab):
312 
            courierCost = courierCost + 35
313 
 
314 
        courierCost = courierCost * 1.1236
315 
 
316 
        sheet.write(i, 0, one_line.item_id)
317 
        sheet.write(i, 1, one_line.category_name)
318 
        sheet.write(i, 2, one_line.product_group)
319 
        sheet.write(i, 3, supc_data.supc)
320 
        sheet.write(i, 4, one_line.brand)
321 
        sheet.write(i, 5, one_line.model_name)
322 
        sheet.write(i, 6, one_line.model_number)
323 
        sheet.write(i, 7, one_line.color)
9359 kshitij.so 324 
        sheet.write(i, 8, one_line.weight)
325 
        sheet.write(i, 9, one_line.risky)
326 
        sheet.write(i, 10, supc_data.ourSp)
327 
        sheet.write(i, 11, round(supc_data.ourSp*0.9597-courierCost))
328 
        sheet.write(i, 12, supc_data.offerPrice)
329 
        sheet.write(i, 13, supc_data.rank)
330 
        sheet.write(i, 14, supc_data.lowestSellerName)
9368 kshitij.so 331 
        sheet.write(i, 15, supc_data.lowestOfferPrice)
9359 kshitij.so 332 
        sheet.write(i, 16, supc_data.secondLowestSellerName)
333 
        sheet.write(i, 17, supc_data.secondLowestSellerSp)
9369 kshitij.so 334 
        sheet.write(i, 18, supc_data.secondLowestSellerOfferPrice)
9368 kshitij.so 335 
        sheet.write(i, 19, supc_data.ourInventory)
336 
        sheet.write(i, 20, supc_data.secondLowestSellerInventory)
337 
        sheet.write(i, 21, one_line.our_nlc)
9334 kshitij.so 338 
        if one_line.parent_category ==10011:
339 
            lowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
340 
        else:
341 
            lowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
9368 kshitij.so 342 
        sheet.write(i, 22, round(lowestTp))
9334 kshitij.so 343 
        i+=1
344 
 
345 
 
346 
def main():
347 
    parser = optparse.OptionParser()
348 
    parser.add_option("-f", "--file", dest="filename",
349 
                   default="ItemList.xls", type="string",
350 
                   help="Read the item list from FILE",
351 
                   metavar="FILE")
352 
    (options, args) = parser.parse_args()
353 
    if len(args) != 0:
354 
        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
355 
    filename = options.filename
356 
    read_data(filename)
357 
 
358 
if __name__ == '__main__':
359 
    main()
360