Subversion Repositories SmartDukaan

Rev

Rev 9391 | Rev 9399 | 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 optparse
10 
import xlrd
11 
from shop2020.clients.InventoryClient import InventoryClient
12 
import xlwt
13 
import urllib2
14 
import time
15 
import simplejson as json
9365 kshitij.so 16 
from shop2020.utils import EmailAttachmentSender
17 
from shop2020.utils.EmailAttachmentSender import get_attachment_part
9334 kshitij.so 18 
 
19 
 
9365 kshitij.so 20 
 
9334 kshitij.so 21 
if __name__ == '__main__' and __package__ is None:
22 
    import sys
23 
    import os
24 
    sys.path.insert(0, os.getcwd())
25 
 
26 
 
27 
class _SnapdealItemInfo:
28 
 
9360 kshitij.so 29 
    def __init__(self, item_id, product_group, category_name, our_nlc, brand, model_name, model_number, color, weight, parent_category, risky):
9334 kshitij.so 30 
        self.item_id = item_id
31 
        self.product_group = product_group
32 
        self.category_name = category_name
33 
        self.our_nlc = our_nlc
34 
        self.brand = brand
35 
        self.model_name = model_name
36 
        self.model_number = model_number
37 
        self.color = color
38 
        self.weight = weight
39 
        self.parent_category = parent_category
9359 kshitij.so 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']
9362 kshitij.so 80 
            try:
81 
                lowestSp = vendor['sellingPriceBefIntCashBack']
82 
            except:
83 
                lowestSp = vendor['sellingPrice']
9359 kshitij.so 84 
            lowestOfferPrice = vendor['sellingPrice']
9334 kshitij.so 85 
 
86 
        if iterator ==1:
87 
            secondLowestSellerName = vendor['vendorDisplayName']
9362 kshitij.so 88 
            try:
89 
                secondLowestSellerSp = vendor['sellingPriceBefIntCashBack']
90 
            except:
91 
                secondLowestSellerSp = vendor['sellingPrice']
9359 kshitij.so 92 
            secondLowestSellerOfferPrice = vendor['sellingPrice']
9334 kshitij.so 93 
            secondLowestSellerInventory = vendor['buyableInventory']
94 
 
95 
        if vendor['vendorDisplayName'] == 'MobilesnMore':
96 
            ourInventory = vendor['buyableInventory']
9362 kshitij.so 97 
            try:
98 
                ourSp = vendor['sellingPriceBefIntCashBack']
99 
            except:
100 
                ourSp = vendor['sellingPrice']
9359 kshitij.so 101 
            ourOfferPrice = vendor['sellingPrice']
9334 kshitij.so 102 
            rank = iterator +1
103 
        else:
104 
            if rank==0:
105 
                otherInventory = otherInventory +vendor['buyableInventory']
106 
 
107 
        iterator+=1
9359 kshitij.so 108 
    snapdealDetails = SnapdealDetails(supc_code,ourSp,offerPrice,ourInventory,otherInventory,rank,lowestSellerName,lowestSp,secondLowestSellerName,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice)
9334 kshitij.so 109 
    return snapdealDetails
110 
 
111 
 
112 
def read_data(filename):
113 
    all_lines = []
114 
    all_supc=[]
115 
    iclient = InventoryClient().get_client()
9337 kshitij.so 116 
    DataService.initialize('catalog','192.168.166.135')
9334 kshitij.so 117 
    workbook = xlrd.open_workbook(filename)
118 
    sheet = workbook.sheet_by_index(0)
119 
    num_rows = sheet.nrows
120 
    for rownum in range(1, num_rows):
121 
        print sheet.row_values(rownum)
122 
        item_id = int(sheet.row_values(rownum)[0])
123 
        supc = sheet.row_values(rownum)[1]
124 
        item = Item.query.filter_by(id=item_id).one()
125 
        category = Category.query.filter_by(id=item.category).one()
126 
        snapdeal_item = SnapdealItem.query.filter_by(item_id=item_id).one()
127 
        warehouse = iclient.getWarehouse(snapdeal_item.warehouseId)
128 
        item_pricing = iclient.getItemPricing(item_id, warehouse.vendor.id)
9359 kshitij.so 129 
        #TO BE USED LATER
130 
        #inventory_snapshot = iclient.getInventorySnapshot(0)
131 
        #warehouses_ours = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("OURS"), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)
132 
        #warehouses_third_party = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("THIRD_PARTY "), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)
133 
        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 134 
        all_supc.append(supc)
135 
        all_lines.append(one_line)
136 
    write_report("/tmp/snapdeal_running.xls", all_lines, all_supc)
137 
 
138 
 
139 
 
140 
def write_report(filename, all_lines, all_supc):
141 
 
142 
    buyBoxItems = []
9387 kshitij.so 143 
    competitive = []
9396 kshitij.so 144 
    competitiveNoInventory = []
9334 kshitij.so 145 
 
146 
    wbk = xlwt.Workbook()
9396 kshitij.so 147 
    sheet = wbk.add_sheet('Can\'t Compete')
9334 kshitij.so 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 
 
9387 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
211 
 
212 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
213 
 
9396 kshitij.so 214 
        if (lowestTp > lowest_possible_tp and supc_data.ourInventory!=0):
9387 kshitij.so 215 
            temp=[]
216 
            temp.append(supc_data)
217 
            temp.append(one_line)
218 
            competitive.append(temp)
219 
            i+=1
220 
            continue
221 
 
9396 kshitij.so 222 
        if (lowestTp > lowest_possible_tp and supc_data.ourInventory==0):
223 
            temp=[]
224 
            temp.append(supc_data)
225 
            temp.append(one_line)
226 
            competitiveNoInventory.append(temp)
227 
            i+=1
228 
            continue
9387 kshitij.so 229 
 
9337 kshitij.so 230 
        sheet.write(sheet_iterator, 0, one_line.item_id)
231 
        sheet.write(sheet_iterator, 1, one_line.category_name)
232 
        sheet.write(sheet_iterator, 2, one_line.product_group)
233 
        sheet.write(sheet_iterator, 3, supc)
234 
        sheet.write(sheet_iterator, 4, one_line.brand)
235 
        sheet.write(sheet_iterator, 5, one_line.model_name)
236 
        sheet.write(sheet_iterator, 6, one_line.model_number)
237 
        sheet.write(sheet_iterator, 7, one_line.color)
9359 kshitij.so 238 
        sheet.write(sheet_iterator, 8, one_line.weight)
239 
        sheet.write(sheet_iterator, 9, one_line.risky)
240 
        sheet.write(sheet_iterator, 10, supc_data.ourSp)
241 
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
9370 kshitij.so 242 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
9359 kshitij.so 243 
        sheet.write(sheet_iterator, 13, supc_data.rank)
244 
        sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
245 
        sheet.write(sheet_iterator, 15, supc_data.lowestSp)
9334 kshitij.so 246 
        if one_line.parent_category ==10011:
247 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
248 
        else:
249 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
9359 kshitij.so 250 
        sheet.write(sheet_iterator, 16, round(lowestTp))
9368 kshitij.so 251 
        sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
9359 kshitij.so 252 
        sheet.write(sheet_iterator, 18, supc_data.otherInventory)
253 
        sheet.write(sheet_iterator, 19, supc_data.ourInventory)
254 
        sheet.write(sheet_iterator, 20, one_line.our_nlc)
9334 kshitij.so 255 
        if supc_data.rank==1:
256 
            i+=1
9337 kshitij.so 257 
            sheet_iterator+=1
9334 kshitij.so 258 
            continue
259 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
9366 kshitij.so 260 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
9359 kshitij.so 261 
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
9366 kshitij.so 262 
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
9390 kshitij.so 263 
        competitionBasis ='TP'
9381 kshitij.so 264 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
9390 kshitij.so 265 
            competitionBasis ='SP'
9366 kshitij.so 266 
            sheet.write(sheet_iterator, 23, 'SP')
267 
        else:
268 
            sheet.write(sheet_iterator, 23, 'TP')
9334 kshitij.so 269 
        proposed_tp = 0
270 
        if lowestTp > lowest_possible_tp:
9366 kshitij.so 271 
            sheet.write(sheet_iterator, 24, "Yes")
9334 kshitij.so 272 
        else:
9366 kshitij.so 273 
            sheet.write(sheet_iterator, 24, "No")
9396 kshitij.so 274 
        if competitionBasis=='SP':
275 
            proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
276 
            proposed_tp = proposed_sp*0.9597-courierCost
277 
            sheet.write(sheet_iterator, 25, round(proposed_tp))
278 
            sheet.write(sheet_iterator, 26, round(proposed_sp))
279 
            sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
280 
        else:
281 
            proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
282 
            sheet.write(sheet_iterator, 25, round(proposed_tp))
283 
            sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
284 
            sheet.write(sheet_iterator, 27, round(proposed_tp*0.988-21))
9381 kshitij.so 285 
 
286 
 
9366 kshitij.so 287 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
9381 kshitij.so 288 
            sheet.write(sheet_iterator, 28, "HIGH")
9366 kshitij.so 289 
        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 290 
            sheet.write(sheet_iterator, 28, "MEDIUM")
9366 kshitij.so 291 
        else:
9381 kshitij.so 292 
            sheet.write(sheet_iterator, 28, "LOW")
9334 kshitij.so 293 
        i= i+1
9337 kshitij.so 294 
        sheet_iterator+=1
295 
 
9334 kshitij.so 296 
    createSheetForBuyBoxItems(buyBoxItems,wbk)
9387 kshitij.so 297 
    createSheetForCometitiveItems(competitive,wbk)
9396 kshitij.so 298 
    createSheetForCometitiveItemsNoInventory(competitiveNoInventory,wbk)
9334 kshitij.so 299 
    wbk.save(filename)
9365 kshitij.so 300 
 
9367 kshitij.so 301 
    EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@shop2020.in"], " " , "", [get_attachment_part(filename)], ["kshitij.sood@shop2020.in"], [])
9334 kshitij.so 302 
 
9365 kshitij.so 303 
 
304 
 
9334 kshitij.so 305 
def createSheetForBuyBoxItems(buyBoxItems,wbk):
9396 kshitij.so 306 
    sheet = wbk.add_sheet('Lowest')
9334 kshitij.so 307 
 
308 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
309 
 
310 
    excel_integer_format = '0'
311 
    integer_style = xlwt.XFStyle()
312 
    integer_style.num_format_str = excel_integer_format
313 
 
314 
    sheet.write(0, 0, "Item ID", heading_xf)
315 
    sheet.write(0, 1, "Category", heading_xf)
316 
    sheet.write(0, 2, "Product Group.", heading_xf)
317 
    sheet.write(0, 3, "SUPC", heading_xf)
318 
    sheet.write(0, 4, "Brand", heading_xf)
319 
    sheet.write(0, 5, "Model Name", heading_xf)
320 
    sheet.write(0, 6, "Model Number", heading_xf)
321 
    sheet.write(0, 7, "Color", heading_xf)
9359 kshitij.so 322 
    sheet.write(0, 8, "Weight", heading_xf)
323 
    sheet.write(0, 9, "Risky", heading_xf)
324 
    sheet.write(0, 10, "Our SP", heading_xf)
325 
    sheet.write(0, 11, "Our TP", heading_xf)
9370 kshitij.so 326 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
9359 kshitij.so 327 
    sheet.write(0, 13, "Our Rank", heading_xf)
328 
    sheet.write(0, 14, "Lowest Seller", heading_xf)
9396 kshitij.so 329 
    sheet.write(0, 15, "Second Lowest Seller", heading_xf)
330 
    sheet.write(0, 16, "Second Lowest Price", heading_xf)
331 
    sheet.write(0, 17, "Second Lowest Offer Price", heading_xf)
332 
    sheet.write(0, 18, "Second Lowest Seller TP", heading_xf)
333 
    sheet.write(0, 19, "Second Lowest Seller SP", heading_xf)
334 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
335 
    sheet.write(0, 21, "Second Lowest Seller Inventory", heading_xf)
336 
    sheet.write(0, 22, "Our NLC", heading_xf)
337 
    sheet.write(0, 23, "Competition Basis", heading_xf)
338 
    sheet.write(0, 24, "Target TP", heading_xf)
339 
    sheet.write(0, 25, "Target SP", heading_xf)
340 
    sheet.write(0, 26, "Target NLC", heading_xf)
341 
    sheet.write(0, 27, "MARGIN INCREASED POTENTIAL", heading_xf)
9334 kshitij.so 342 
 
343 
    i=1
344 
 
345 
    for data in buyBoxItems:
346 
        supc_data =data[0]
347 
        one_line = data[1]
348 
 
349 
        courierCost = 45
350 
 
351 
        if one_line.weight:
9381 kshitij.so 352 
            slab = int(((one_line.weight+0.05) - .001)/.5)
9334 kshitij.so 353 
        for x in range (0,slab):
354 
            courierCost = courierCost + 35
355 
 
356 
        courierCost = courierCost * 1.1236
357 
 
358 
        sheet.write(i, 0, one_line.item_id)
359 
        sheet.write(i, 1, one_line.category_name)
360 
        sheet.write(i, 2, one_line.product_group)
361 
        sheet.write(i, 3, supc_data.supc)
362 
        sheet.write(i, 4, one_line.brand)
363 
        sheet.write(i, 5, one_line.model_name)
364 
        sheet.write(i, 6, one_line.model_number)
365 
        sheet.write(i, 7, one_line.color)
9359 kshitij.so 366 
        sheet.write(i, 8, one_line.weight)
367 
        sheet.write(i, 9, one_line.risky)
368 
        sheet.write(i, 10, supc_data.ourSp)
369 
        sheet.write(i, 11, round(supc_data.ourSp*0.9597-courierCost))
9370 kshitij.so 370 
        sheet.write(i, 12, supc_data.ourOfferPrice)
9359 kshitij.so 371 
        sheet.write(i, 13, supc_data.rank)
372 
        sheet.write(i, 14, supc_data.lowestSellerName)
9396 kshitij.so 373 
        sheet.write(i, 15, supc_data.secondLowestSellerName)
374 
        sheet.write(i, 16, supc_data.secondLowestSellerSp)
375 
        sheet.write(i, 17, supc_data.secondLowestSellerOfferPrice)
9334 kshitij.so 376 
        if one_line.parent_category ==10011:
9396 kshitij.so 377 
            secondlowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
9334 kshitij.so 378 
        else:
9396 kshitij.so 379 
            secondlowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
9381 kshitij.so 380 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
9396 kshitij.so 381 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
382 
        sheet.write(i, 18, secondlowestTp)
383 
        sheet.write(i, 18, secondlowestTp)
384 
        sheet.write(i, 20, supc_data.ourInventory)
385 
        sheet.write(i, 21, supc_data.secondLowestSellerInventory)
386 
        sheet.write(i, 22, one_line.our_nlc)
387 
 
388 
        competitionBasis ='TP'
389 
        if (supc_data.secondLowestSellerOfferPrice == supc_data.secondLowestSellerSp):
390 
            competitionBasis ='SP'
391 
            sheet.write(i, 23, 'SP')
392 
        else:
393 
            sheet.write(i, 23, 'TP')
394 
 
395 
        if competitionBasis=='SP':
396 
            proposed_sp = max(supc_data.secondLowestSellerSp - max((20, supc_data.secondLowestSellerSp*0.002)), lowest_possible_sp)
397 
            proposed_tp = proposed_sp*0.9597-courierCost
398 
            sheet.write(i, 24, round(proposed_tp))
399 
            sheet.write(i, 25, round(proposed_sp))
400 
            sheet.write(i, 26, round(proposed_tp*0.988+21))
401 
        else:
402 
            proposed_tp  = max(secondlowestTp - max((20, secondlowestTp*0.002)), lowest_possible_tp)
403 
            sheet.write(i, 24, round(proposed_tp))
404 
            sheet.write(i, 25, round((proposed_tp+courierCost)/0.9597))
405 
            sheet.write(i, 26, round(proposed_tp*0.988-21))
406 
 
407 
        sheet.write(i, 27, round(proposed_tp-(supc_data.ourSp*0.9597-courierCost)))
9334 kshitij.so 408 
        i+=1
9387 kshitij.so 409 
 
410 
 
411 
def createSheetForCometitiveItems(competitiveItems,wbk):
9396 kshitij.so 412 
    sheet = wbk.add_sheet('Can Compete-With Inventory')
9387 kshitij.so 413 
 
414 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
9334 kshitij.so 415 
 
9387 kshitij.so 416 
    excel_integer_format = '0'
417 
    integer_style = xlwt.XFStyle()
418 
    integer_style.num_format_str = excel_integer_format
419 
 
420 
    sheet.write(0, 0, "Item ID", heading_xf)
421 
    sheet.write(0, 1, "Category", heading_xf)
422 
    sheet.write(0, 2, "Product Group.", heading_xf)
423 
    sheet.write(0, 3, "SUPC", heading_xf)
424 
    sheet.write(0, 4, "Brand", heading_xf)
425 
    sheet.write(0, 5, "Model Name", heading_xf)
426 
    sheet.write(0, 6, "Model Number", heading_xf)
427 
    sheet.write(0, 7, "Color", heading_xf)
428 
    sheet.write(0, 8, "Weight", heading_xf)
429 
    sheet.write(0, 9, "Risky", heading_xf)
430 
    sheet.write(0, 10, "Our SP", heading_xf)
431 
    sheet.write(0, 11, "Our TP", heading_xf)
432 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
433 
    sheet.write(0, 13, "Our Rank", heading_xf)
434 
    sheet.write(0, 14, "Lowest Seller", heading_xf)
435 
    sheet.write(0, 15, "Lowest SP", heading_xf)
436 
    sheet.write(0, 16, "Lowest TP", heading_xf)
437 
    sheet.write(0, 17, "Lowest Offer Price", heading_xf)
438 
    sheet.write(0, 18, "Inventory of Top Vendors", heading_xf)
439 
    sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
440 
    sheet.write(0, 20, "Our NLC", heading_xf)
441 
    sheet.write(0, 21, "Lowest Possible TP", heading_xf)
442 
    sheet.write(0, 22, "Lowest Possible SP", heading_xf)
443 
    sheet.write(0, 23, "Competition Basis ", heading_xf)
444 
    sheet.write(0, 24, "Can Compete", heading_xf)
445 
    sheet.write(0, 25, "Target TP", heading_xf)
446 
    sheet.write(0, 26, "Target SP", heading_xf)
9396 kshitij.so 447 
    sheet.write(0, 27, "Sales Potential", heading_xf)
9387 kshitij.so 448 
 
449 
    i, sheet_iterator=1,1
450 
    for data in competitiveItems:
451 
        supc_data =data[0]
452 
        one_line = data[1]
453 
 
454 
        courierCost = 45
455 
 
456 
        if one_line.weight:
457 
            slab = int(((one_line.weight+0.05) - .001)/.5)
458 
        for x in range (0,slab):
459 
            courierCost = courierCost + 35
460 
 
461 
        courierCost = courierCost * 1.1236
462 
 
463 
 
464 
        sheet.write(sheet_iterator, 0, one_line.item_id)
465 
        sheet.write(sheet_iterator, 1, one_line.category_name)
466 
        sheet.write(sheet_iterator, 2, one_line.product_group)
467 
        sheet.write(sheet_iterator, 3, supc_data.supc)
468 
        sheet.write(sheet_iterator, 4, one_line.brand)
469 
        sheet.write(sheet_iterator, 5, one_line.model_name)
470 
        sheet.write(sheet_iterator, 6, one_line.model_number)
471 
        sheet.write(sheet_iterator, 7, one_line.color)
472 
        sheet.write(sheet_iterator, 8, one_line.weight)
473 
        sheet.write(sheet_iterator, 9, one_line.risky)
474 
        sheet.write(sheet_iterator, 10, supc_data.ourSp)
475 
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
476 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
477 
        sheet.write(sheet_iterator, 13, supc_data.rank)
478 
        sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
479 
        sheet.write(sheet_iterator, 15, supc_data.lowestSp)
480 
        if one_line.parent_category ==10011:
481 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
482 
        else:
483 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
484 
        sheet.write(sheet_iterator, 16, round(lowestTp))
485 
        sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
486 
        sheet.write(sheet_iterator, 18, supc_data.otherInventory)
487 
        sheet.write(sheet_iterator, 19, supc_data.ourInventory)
488 
        sheet.write(sheet_iterator, 20, one_line.our_nlc)
489 
        if supc_data.rank==1:
490 
            i+=1
491 
            sheet_iterator+=1
492 
            continue
493 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
494 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
495 
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
496 
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
9390 kshitij.so 497 
        competitionBasis ='TP'
9387 kshitij.so 498 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
9390 kshitij.so 499 
            competitionBasis ='SP'
9387 kshitij.so 500 
            sheet.write(sheet_iterator, 23, 'SP')
501 
        else:
502 
            sheet.write(sheet_iterator, 23, 'TP')
503 
        proposed_tp = 0
504 
        if lowestTp > lowest_possible_tp:
505 
            sheet.write(sheet_iterator, 24, "Yes")
9390 kshitij.so 506 
            if competitionBasis=='SP':
507 
                proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
9396 kshitij.so 508 
                proposed_tp = proposed_sp*.9597-courierCost
9390 kshitij.so 509 
                sheet.write(sheet_iterator, 25, round(proposed_tp))
510 
                sheet.write(sheet_iterator, 26, round(proposed_sp))
511 
            else:
512 
                proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
513 
                sheet.write(sheet_iterator, 25, round(proposed_tp))
514 
                sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
9387 kshitij.so 515 
        else:
516 
            sheet.write(sheet_iterator, 24, "No")
517 
 
518 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
9396 kshitij.so 519 
            sheet.write(sheet_iterator, 27, "HIGH")
9387 kshitij.so 520 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
9396 kshitij.so 521 
            sheet.write(sheet_iterator, 27, "MEDIUM")
9387 kshitij.so 522 
        else:
9396 kshitij.so 523 
            sheet.write(sheet_iterator, 27, "LOW")
9387 kshitij.so 524 
        i= i+1
525 
        sheet_iterator+=1
9334 kshitij.so 526 
 
9396 kshitij.so 527 
 
528 
def createSheetForCometitiveItemsNoInventory(competitiveNoInventoryItems,wbk):
529 
    sheet = wbk.add_sheet('Can Compete-No Inventory')
530 
 
531 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
532 
 
533 
    excel_integer_format = '0'
534 
    integer_style = xlwt.XFStyle()
535 
    integer_style.num_format_str = excel_integer_format
536 
 
537 
    sheet.write(0, 0, "Item ID", heading_xf)
538 
    sheet.write(0, 1, "Category", heading_xf)
539 
    sheet.write(0, 2, "Product Group.", heading_xf)
540 
    sheet.write(0, 3, "SUPC", heading_xf)
541 
    sheet.write(0, 4, "Brand", heading_xf)
542 
    sheet.write(0, 5, "Model Name", heading_xf)
543 
    sheet.write(0, 6, "Model Number", heading_xf)
544 
    sheet.write(0, 7, "Color", heading_xf)
545 
    sheet.write(0, 8, "Weight", heading_xf)
546 
    sheet.write(0, 9, "Risky", heading_xf)
547 
    sheet.write(0, 10, "Our SP", heading_xf)
548 
    sheet.write(0, 11, "Our TP", heading_xf)
549 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
550 
    sheet.write(0, 13, "Our Rank", heading_xf)
551 
    sheet.write(0, 14, "Lowest Seller", heading_xf)
552 
    sheet.write(0, 15, "Lowest SP", heading_xf)
553 
    sheet.write(0, 16, "Lowest TP", heading_xf)
554 
    sheet.write(0, 17, "Lowest Offer Price", heading_xf)
555 
    sheet.write(0, 18, "Inventory of Top Vendors", heading_xf)
556 
    sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
557 
    sheet.write(0, 20, "Our NLC", heading_xf)
558 
    sheet.write(0, 21, "Lowest Possible TP", heading_xf)
559 
    sheet.write(0, 22, "Lowest Possible SP", heading_xf)
560 
    sheet.write(0, 23, "Competition Basis ", heading_xf)
561 
    sheet.write(0, 24, "Can Compete", heading_xf)
562 
    sheet.write(0, 25, "Target TP", heading_xf)
563 
    sheet.write(0, 26, "Target SP", heading_xf)
564 
    sheet.write(0, 27, "Sales Potential", heading_xf)
565 
 
566 
    i, sheet_iterator=1,1
567 
    for data in competitiveNoInventoryItems:
568 
        supc_data =data[0]
569 
        one_line = data[1]
570 
 
571 
        courierCost = 45
572 
 
573 
        if one_line.weight:
574 
            slab = int(((one_line.weight+0.05) - .001)/.5)
575 
        for x in range (0,slab):
576 
            courierCost = courierCost + 35
577 
 
578 
        courierCost = courierCost * 1.1236
579 
 
580 
 
581 
        sheet.write(sheet_iterator, 0, one_line.item_id)
582 
        sheet.write(sheet_iterator, 1, one_line.category_name)
583 
        sheet.write(sheet_iterator, 2, one_line.product_group)
584 
        sheet.write(sheet_iterator, 3, supc_data.supc)
585 
        sheet.write(sheet_iterator, 4, one_line.brand)
586 
        sheet.write(sheet_iterator, 5, one_line.model_name)
587 
        sheet.write(sheet_iterator, 6, one_line.model_number)
588 
        sheet.write(sheet_iterator, 7, one_line.color)
589 
        sheet.write(sheet_iterator, 8, one_line.weight)
590 
        sheet.write(sheet_iterator, 9, one_line.risky)
591 
        sheet.write(sheet_iterator, 10, supc_data.ourSp)
592 
        sheet.write(sheet_iterator, 11, '-')
593 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
594 
        sheet.write(sheet_iterator, 13, supc_data.rank)
595 
        sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
596 
        sheet.write(sheet_iterator, 15, supc_data.lowestSp)
597 
        if one_line.parent_category ==10011:
598 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
599 
        else:
600 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
601 
        sheet.write(sheet_iterator, 16, round(lowestTp))
602 
        sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
603 
        sheet.write(sheet_iterator, 18, supc_data.otherInventory)
604 
        sheet.write(sheet_iterator, 19, supc_data.ourInventory)
605 
        sheet.write(sheet_iterator, 20, one_line.our_nlc)
606 
        if supc_data.rank==1:
607 
            i+=1
608 
            sheet_iterator+=1
609 
            continue
610 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
611 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
612 
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
613 
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
614 
        competitionBasis ='TP'
615 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
616 
            competitionBasis ='SP'
617 
            sheet.write(sheet_iterator, 23, 'SP')
618 
        else:
619 
            sheet.write(sheet_iterator, 23, 'TP')
620 
        proposed_tp = 0
621 
        if lowestTp > lowest_possible_tp:
622 
            sheet.write(sheet_iterator, 24, "Yes")
623 
            if competitionBasis=='SP':
624 
                proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
625 
                proposed_tp = proposed_sp*.9597-courierCost
626 
                sheet.write(sheet_iterator, 25, round(proposed_tp))
627 
                sheet.write(sheet_iterator, 26, round(proposed_sp))
628 
            else:
629 
                proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
630 
                sheet.write(sheet_iterator, 25, round(proposed_tp))
631 
                sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
632 
        else:
633 
            sheet.write(sheet_iterator, 24, "No")
634 
 
635 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
636 
            sheet.write(sheet_iterator, 27, "HIGH")
637 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
638 
            sheet.write(sheet_iterator, 27, "MEDIUM")
639 
        else:
640 
            sheet.write(sheet_iterator, 27, "LOW")
641 
        i= i+1
642 
        sheet_iterator+=1
643 
 
644 
 
645 
 
9334 kshitij.so 646 
def main():
647 
    parser = optparse.OptionParser()
648 
    parser.add_option("-f", "--file", dest="filename",
649 
                   default="ItemList.xls", type="string",
650 
                   help="Read the item list from FILE",
651 
                   metavar="FILE")
652 
    (options, args) = parser.parse_args()
653 
    if len(args) != 0:
654 
        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
655 
    filename = options.filename
656 
    read_data(filename)
657 
 
658 
if __name__ == '__main__':
659 
    main()
660