Subversion Repositories SmartDukaan

Rev

Rev 9386 | Rev 9390 | 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 = []
9387 kshitij.so 145 
    competitive = []
9334 kshitij.so 146 
 
147 
    wbk = xlwt.Workbook()
148 
    sheet = wbk.add_sheet('main')
149 
 
150 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
151 
 
152 
    excel_integer_format = '0'
153 
    integer_style = xlwt.XFStyle()
154 
    integer_style.num_format_str = excel_integer_format
155 
 
156 
    sheet.write(0, 0, "Item ID", heading_xf)
157 
    sheet.write(0, 1, "Category", heading_xf)
158 
    sheet.write(0, 2, "Product Group.", heading_xf)
159 
    sheet.write(0, 3, "SUPC", heading_xf)
160 
    sheet.write(0, 4, "Brand", heading_xf)
161 
    sheet.write(0, 5, "Model Name", heading_xf)
162 
    sheet.write(0, 6, "Model Number", heading_xf)
163 
    sheet.write(0, 7, "Color", heading_xf)
9359 kshitij.so 164 
    sheet.write(0, 8, "Weight", heading_xf)
165 
    sheet.write(0, 9, "Risky", heading_xf)
166 
    sheet.write(0, 10, "Our SP", heading_xf)
167 
    sheet.write(0, 11, "Our TP", heading_xf)
9370 kshitij.so 168 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
9359 kshitij.so 169 
    sheet.write(0, 13, "Our Rank", heading_xf)
170 
    sheet.write(0, 14, "Lowest Seller", heading_xf)
171 
    sheet.write(0, 15, "Lowest SP", heading_xf)
172 
    sheet.write(0, 16, "Lowest TP", heading_xf)
173 
    sheet.write(0, 17, "Lowest Offer Price", heading_xf)
174 
    sheet.write(0, 18, "Inventory of Top Vendors", heading_xf)
9381 kshitij.so 175 
    sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
9359 kshitij.so 176 
    sheet.write(0, 20, "Our NLC", heading_xf)
177 
    sheet.write(0, 21, "Lowest Possible TP", heading_xf)
9366 kshitij.so 178 
    sheet.write(0, 22, "Lowest Possible SP", heading_xf)
179 
    sheet.write(0, 23, "Competition Basis ", heading_xf)
180 
    sheet.write(0, 24, "Can Compete", heading_xf)
9381 kshitij.so 181 
    sheet.write(0, 25, "Target TP", heading_xf)
182 
    sheet.write(0, 26, "Target SP", heading_xf)
183 
    sheet.write(0, 27, "Target NLC", heading_xf)
184 
    sheet.write(0, 28, "Sales Potential", heading_xf)
9334 kshitij.so 185 
 
9337 kshitij.so 186 
    i, sheet_iterator=1,1
9334 kshitij.so 187 
    for one_line in all_lines:
188 
        supc = all_supc[i-1]
189 
        supc_data = fetchDetails(supc)
190 
 
191 
        courierCost = 45
192 
 
193 
        if one_line.weight:
9381 kshitij.so 194 
            slab = int(((one_line.weight+0.05) - .001)/.5)
9334 kshitij.so 195 
        for x in range (0,slab):
196 
            courierCost = courierCost + 35
197 
 
198 
        courierCost = courierCost * 1.1236
199 
 
200 
        if supc_data.rank==1:
201 
            temp = []
202 
            temp.append(supc_data)
203 
            temp.append(one_line)
204 
            buyBoxItems.append(temp)
205 
            i+=1
206 
            continue
207 
 
9387 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
212 
 
213 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
214 
 
215 
        if (lowestTp > lowest_possible_tp):
216 
            temp=[]
217 
            temp.append(supc_data)
218 
            temp.append(one_line)
219 
            competitive.append(temp)
220 
            i+=1
221 
            continue
222 
 
223 
 
9337 kshitij.so 224 
        sheet.write(sheet_iterator, 0, one_line.item_id)
225 
        sheet.write(sheet_iterator, 1, one_line.category_name)
226 
        sheet.write(sheet_iterator, 2, one_line.product_group)
227 
        sheet.write(sheet_iterator, 3, supc)
228 
        sheet.write(sheet_iterator, 4, one_line.brand)
229 
        sheet.write(sheet_iterator, 5, one_line.model_name)
230 
        sheet.write(sheet_iterator, 6, one_line.model_number)
231 
        sheet.write(sheet_iterator, 7, one_line.color)
9359 kshitij.so 232 
        sheet.write(sheet_iterator, 8, one_line.weight)
233 
        sheet.write(sheet_iterator, 9, one_line.risky)
234 
        sheet.write(sheet_iterator, 10, supc_data.ourSp)
235 
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
9370 kshitij.so 236 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
9359 kshitij.so 237 
        sheet.write(sheet_iterator, 13, supc_data.rank)
238 
        sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
239 
        sheet.write(sheet_iterator, 15, supc_data.lowestSp)
9334 kshitij.so 240 
        if one_line.parent_category ==10011:
241 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
242 
        else:
243 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
9359 kshitij.so 244 
        sheet.write(sheet_iterator, 16, round(lowestTp))
9368 kshitij.so 245 
        sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
9359 kshitij.so 246 
        sheet.write(sheet_iterator, 18, supc_data.otherInventory)
247 
        sheet.write(sheet_iterator, 19, supc_data.ourInventory)
248 
        sheet.write(sheet_iterator, 20, one_line.our_nlc)
9334 kshitij.so 249 
        if supc_data.rank==1:
250 
            i+=1
9337 kshitij.so 251 
            sheet_iterator+=1
9334 kshitij.so 252 
            continue
253 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
9366 kshitij.so 254 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
9359 kshitij.so 255 
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
9366 kshitij.so 256 
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
9381 kshitij.so 257 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
9366 kshitij.so 258 
            sheet.write(sheet_iterator, 23, 'SP')
259 
        else:
260 
            sheet.write(sheet_iterator, 23, 'TP')
9334 kshitij.so 261 
        proposed_tp = 0
262 
        if lowestTp > lowest_possible_tp:
9366 kshitij.so 263 
            sheet.write(sheet_iterator, 24, "Yes")
9337 kshitij.so 264 
            proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
9366 kshitij.so 265 
            sheet.write(sheet_iterator, 25, round(proposed_tp))
266 
            sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
9334 kshitij.so 267 
        else:
9366 kshitij.so 268 
            sheet.write(sheet_iterator, 24, "No")
9381 kshitij.so 269 
 
9386 kshitij.so 270 
        sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
9381 kshitij.so 271 
 
9366 kshitij.so 272 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
9381 kshitij.so 273 
            sheet.write(sheet_iterator, 28, "HIGH")
9366 kshitij.so 274 
        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 275 
            sheet.write(sheet_iterator, 28, "MEDIUM")
9366 kshitij.so 276 
        else:
9381 kshitij.so 277 
            sheet.write(sheet_iterator, 28, "LOW")
9334 kshitij.so 278 
        i= i+1
9337 kshitij.so 279 
        sheet_iterator+=1
280 
 
9334 kshitij.so 281 
    createSheetForBuyBoxItems(buyBoxItems,wbk)
9387 kshitij.so 282 
    createSheetForCometitiveItems(competitive,wbk)
9334 kshitij.so 283 
    wbk.save(filename)
9365 kshitij.so 284 
 
9367 kshitij.so 285 
    EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@shop2020.in"], " " , "", [get_attachment_part(filename)], ["kshitij.sood@shop2020.in"], [])
9334 kshitij.so 286 
 
9365 kshitij.so 287 
 
288 
 
9334 kshitij.so 289 
def createSheetForBuyBoxItems(buyBoxItems,wbk):
290 
    sheet = wbk.add_sheet('BuyBoxItems')
291 
 
292 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
293 
 
294 
    excel_integer_format = '0'
295 
    integer_style = xlwt.XFStyle()
296 
    integer_style.num_format_str = excel_integer_format
297 
 
298 
    sheet.write(0, 0, "Item ID", heading_xf)
299 
    sheet.write(0, 1, "Category", heading_xf)
300 
    sheet.write(0, 2, "Product Group.", heading_xf)
301 
    sheet.write(0, 3, "SUPC", heading_xf)
302 
    sheet.write(0, 4, "Brand", heading_xf)
303 
    sheet.write(0, 5, "Model Name", heading_xf)
304 
    sheet.write(0, 6, "Model Number", heading_xf)
305 
    sheet.write(0, 7, "Color", heading_xf)
9359 kshitij.so 306 
    sheet.write(0, 8, "Weight", heading_xf)
307 
    sheet.write(0, 9, "Risky", heading_xf)
308 
    sheet.write(0, 10, "Our SP", heading_xf)
309 
    sheet.write(0, 11, "Our TP", heading_xf)
9370 kshitij.so 310 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
9359 kshitij.so 311 
    sheet.write(0, 13, "Our Rank", heading_xf)
312 
    sheet.write(0, 14, "Lowest Seller", heading_xf)
313 
    sheet.write(0, 15, "Lowest Offer Price", heading_xf)
314 
    sheet.write(0, 16, "Second Lowest Seller", heading_xf)
315 
    sheet.write(0, 17, "Second Lowest Price", heading_xf)
9368 kshitij.so 316 
    sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)
9381 kshitij.so 317 
    sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
9368 kshitij.so 318 
    sheet.write(0, 20, "Second Lowest Seller Inventory", heading_xf)
319 
    sheet.write(0, 21, "Our NLC", heading_xf)
9381 kshitij.so 320 
    sheet.write(0, 22, "Target TP", heading_xf)
321 
    sheet.write(0, 23, "Target SP", heading_xf)
322 
    sheet.write(0, 24, "Target NLC", heading_xf)
323 
    sheet.write(0, 25, "MARGIN INCREASED POTENTIAL", heading_xf)
9334 kshitij.so 324 
 
325 
    i=1
326 
 
327 
    for data in buyBoxItems:
328 
        supc_data =data[0]
329 
        one_line = data[1]
330 
 
331 
        courierCost = 45
332 
 
333 
        if one_line.weight:
9381 kshitij.so 334 
            slab = int(((one_line.weight+0.05) - .001)/.5)
9334 kshitij.so 335 
        for x in range (0,slab):
336 
            courierCost = courierCost + 35
337 
 
338 
        courierCost = courierCost * 1.1236
339 
 
340 
        sheet.write(i, 0, one_line.item_id)
341 
        sheet.write(i, 1, one_line.category_name)
342 
        sheet.write(i, 2, one_line.product_group)
343 
        sheet.write(i, 3, supc_data.supc)
344 
        sheet.write(i, 4, one_line.brand)
345 
        sheet.write(i, 5, one_line.model_name)
346 
        sheet.write(i, 6, one_line.model_number)
347 
        sheet.write(i, 7, one_line.color)
9359 kshitij.so 348 
        sheet.write(i, 8, one_line.weight)
349 
        sheet.write(i, 9, one_line.risky)
350 
        sheet.write(i, 10, supc_data.ourSp)
351 
        sheet.write(i, 11, round(supc_data.ourSp*0.9597-courierCost))
9370 kshitij.so 352 
        sheet.write(i, 12, supc_data.ourOfferPrice)
9359 kshitij.so 353 
        sheet.write(i, 13, supc_data.rank)
354 
        sheet.write(i, 14, supc_data.lowestSellerName)
9368 kshitij.so 355 
        sheet.write(i, 15, supc_data.lowestOfferPrice)
9359 kshitij.so 356 
        sheet.write(i, 16, supc_data.secondLowestSellerName)
357 
        sheet.write(i, 17, supc_data.secondLowestSellerSp)
9369 kshitij.so 358 
        sheet.write(i, 18, supc_data.secondLowestSellerOfferPrice)
9368 kshitij.so 359 
        sheet.write(i, 19, supc_data.ourInventory)
360 
        sheet.write(i, 20, supc_data.secondLowestSellerInventory)
361 
        sheet.write(i, 21, one_line.our_nlc)
9334 kshitij.so 362 
        if one_line.parent_category ==10011:
363 
            lowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
364 
        else:
365 
            lowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
9381 kshitij.so 366 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
367 
        proposed_tp  = max(lowestTp - max((20, lowestTp*0.002)), lowest_possible_tp)
368 
        sheet.write(i, 22, round(proposed_tp))
369 
        sheet.write(i, 23, round((proposed_tp+courierCost)/0.9597))
9383 kshitij.so 370 
        sheet.write(i, 24, round(proposed_tp*0.988+21))
9387 kshitij.so 371 
        sheet.write(i, 25, round(proposed_tp-one_line.our_nlc))
9334 kshitij.so 372 
        i+=1
9387 kshitij.so 373 
 
374 
 
375 
def createSheetForCometitiveItems(competitiveItems,wbk):
376 
    sheet = wbk.add_sheet('Competitive')
377 
 
378 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
9334 kshitij.so 379 
 
9387 kshitij.so 380 
    excel_integer_format = '0'
381 
    integer_style = xlwt.XFStyle()
382 
    integer_style.num_format_str = excel_integer_format
383 
 
384 
    sheet.write(0, 0, "Item ID", heading_xf)
385 
    sheet.write(0, 1, "Category", heading_xf)
386 
    sheet.write(0, 2, "Product Group.", heading_xf)
387 
    sheet.write(0, 3, "SUPC", heading_xf)
388 
    sheet.write(0, 4, "Brand", heading_xf)
389 
    sheet.write(0, 5, "Model Name", heading_xf)
390 
    sheet.write(0, 6, "Model Number", heading_xf)
391 
    sheet.write(0, 7, "Color", heading_xf)
392 
    sheet.write(0, 8, "Weight", heading_xf)
393 
    sheet.write(0, 9, "Risky", heading_xf)
394 
    sheet.write(0, 10, "Our SP", heading_xf)
395 
    sheet.write(0, 11, "Our TP", heading_xf)
396 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
397 
    sheet.write(0, 13, "Our Rank", heading_xf)
398 
    sheet.write(0, 14, "Lowest Seller", heading_xf)
399 
    sheet.write(0, 15, "Lowest SP", heading_xf)
400 
    sheet.write(0, 16, "Lowest TP", heading_xf)
401 
    sheet.write(0, 17, "Lowest Offer Price", heading_xf)
402 
    sheet.write(0, 18, "Inventory of Top Vendors", heading_xf)
403 
    sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
404 
    sheet.write(0, 20, "Our NLC", heading_xf)
405 
    sheet.write(0, 21, "Lowest Possible TP", heading_xf)
406 
    sheet.write(0, 22, "Lowest Possible SP", heading_xf)
407 
    sheet.write(0, 23, "Competition Basis ", heading_xf)
408 
    sheet.write(0, 24, "Can Compete", heading_xf)
409 
    sheet.write(0, 25, "Target TP", heading_xf)
410 
    sheet.write(0, 26, "Target SP", heading_xf)
411 
    sheet.write(0, 27, "Target NLC", heading_xf)
412 
    sheet.write(0, 28, "Sales Potential", heading_xf)
413 
 
414 
    i, sheet_iterator=1,1
415 
    for data in competitiveItems:
416 
        supc_data =data[0]
417 
        one_line = data[1]
418 
 
419 
        courierCost = 45
420 
 
421 
        if one_line.weight:
422 
            slab = int(((one_line.weight+0.05) - .001)/.5)
423 
        for x in range (0,slab):
424 
            courierCost = courierCost + 35
425 
 
426 
        courierCost = courierCost * 1.1236
427 
 
428 
 
429 
        sheet.write(sheet_iterator, 0, one_line.item_id)
430 
        sheet.write(sheet_iterator, 1, one_line.category_name)
431 
        sheet.write(sheet_iterator, 2, one_line.product_group)
432 
        sheet.write(sheet_iterator, 3, supc_data.supc)
433 
        sheet.write(sheet_iterator, 4, one_line.brand)
434 
        sheet.write(sheet_iterator, 5, one_line.model_name)
435 
        sheet.write(sheet_iterator, 6, one_line.model_number)
436 
        sheet.write(sheet_iterator, 7, one_line.color)
437 
        sheet.write(sheet_iterator, 8, one_line.weight)
438 
        sheet.write(sheet_iterator, 9, one_line.risky)
439 
        sheet.write(sheet_iterator, 10, supc_data.ourSp)
440 
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
441 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
442 
        sheet.write(sheet_iterator, 13, supc_data.rank)
443 
        sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
444 
        sheet.write(sheet_iterator, 15, supc_data.lowestSp)
445 
        if one_line.parent_category ==10011:
446 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
447 
        else:
448 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
449 
        sheet.write(sheet_iterator, 16, round(lowestTp))
450 
        sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
451 
        sheet.write(sheet_iterator, 18, supc_data.otherInventory)
452 
        sheet.write(sheet_iterator, 19, supc_data.ourInventory)
453 
        sheet.write(sheet_iterator, 20, one_line.our_nlc)
454 
        if supc_data.rank==1:
455 
            i+=1
456 
            sheet_iterator+=1
457 
            continue
458 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
459 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
460 
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
461 
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
462 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
463 
            sheet.write(sheet_iterator, 23, 'SP')
464 
        else:
465 
            sheet.write(sheet_iterator, 23, 'TP')
466 
        proposed_tp = 0
467 
        if lowestTp > lowest_possible_tp:
468 
            sheet.write(sheet_iterator, 24, "Yes")
469 
            proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
470 
            sheet.write(sheet_iterator, 25, round(proposed_tp))
471 
            sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
472 
        else:
473 
            sheet.write(sheet_iterator, 24, "No")
474 
 
475 
        sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
476 
 
477 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
478 
            sheet.write(sheet_iterator, 28, "HIGH")
479 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
480 
            sheet.write(sheet_iterator, 28, "MEDIUM")
481 
        else:
482 
            sheet.write(sheet_iterator, 28, "LOW")
483 
        i= i+1
484 
        sheet_iterator+=1
9334 kshitij.so 485 
 
486 
def main():
487 
    parser = optparse.OptionParser()
488 
    parser.add_option("-f", "--file", dest="filename",
489 
                   default="ItemList.xls", type="string",
490 
                   help="Read the item list from FILE",
491 
                   metavar="FILE")
492 
    (options, args) = parser.parse_args()
493 
    if len(args) != 0:
494 
        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
495 
    filename = options.filename
496 
    read_data(filename)
497 
 
498 
if __name__ == '__main__':
499 
    main()
500