Subversion Repositories SmartDukaan

Rev

Rev 9390 | Rev 9396 | 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))
9390 kshitij.so 257 
        competitionBasis ='TP'
9381 kshitij.so 258 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
9390 kshitij.so 259 
            competitionBasis ='SP'
9366 kshitij.so 260 
            sheet.write(sheet_iterator, 23, 'SP')
261 
        else:
262 
            sheet.write(sheet_iterator, 23, 'TP')
9334 kshitij.so 263 
        proposed_tp = 0
264 
        if lowestTp > lowest_possible_tp:
9366 kshitij.so 265 
            sheet.write(sheet_iterator, 24, "Yes")
9390 kshitij.so 266 
            if competitionBasis=='SP':
267 
                proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
268 
                proposed_tp = proposed_sp*.09597-courierCost
269 
                sheet.write(sheet_iterator, 25, round(proposed_tp))
270 
                sheet.write(sheet_iterator, 26, round(proposed_sp))
271 
                sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
272 
            else:
273 
                proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
274 
                sheet.write(sheet_iterator, 25, round(proposed_tp))
275 
                sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
276 
                sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
9334 kshitij.so 277 
        else:
9366 kshitij.so 278 
            sheet.write(sheet_iterator, 24, "No")
9381 kshitij.so 279 
 
280 
 
9366 kshitij.so 281 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
9381 kshitij.so 282 
            sheet.write(sheet_iterator, 28, "HIGH")
9366 kshitij.so 283 
        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 284 
            sheet.write(sheet_iterator, 28, "MEDIUM")
9366 kshitij.so 285 
        else:
9381 kshitij.so 286 
            sheet.write(sheet_iterator, 28, "LOW")
9334 kshitij.so 287 
        i= i+1
9337 kshitij.so 288 
        sheet_iterator+=1
289 
 
9334 kshitij.so 290 
    createSheetForBuyBoxItems(buyBoxItems,wbk)
9387 kshitij.so 291 
    createSheetForCometitiveItems(competitive,wbk)
9334 kshitij.so 292 
    wbk.save(filename)
9365 kshitij.so 293 
 
9367 kshitij.so 294 
    EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@shop2020.in"], " " , "", [get_attachment_part(filename)], ["kshitij.sood@shop2020.in"], [])
9334 kshitij.so 295 
 
9365 kshitij.so 296 
 
297 
 
9334 kshitij.so 298 
def createSheetForBuyBoxItems(buyBoxItems,wbk):
299 
    sheet = wbk.add_sheet('BuyBoxItems')
300 
 
301 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
302 
 
303 
    excel_integer_format = '0'
304 
    integer_style = xlwt.XFStyle()
305 
    integer_style.num_format_str = excel_integer_format
306 
 
307 
    sheet.write(0, 0, "Item ID", heading_xf)
308 
    sheet.write(0, 1, "Category", heading_xf)
309 
    sheet.write(0, 2, "Product Group.", heading_xf)
310 
    sheet.write(0, 3, "SUPC", heading_xf)
311 
    sheet.write(0, 4, "Brand", heading_xf)
312 
    sheet.write(0, 5, "Model Name", heading_xf)
313 
    sheet.write(0, 6, "Model Number", heading_xf)
314 
    sheet.write(0, 7, "Color", heading_xf)
9359 kshitij.so 315 
    sheet.write(0, 8, "Weight", heading_xf)
316 
    sheet.write(0, 9, "Risky", heading_xf)
317 
    sheet.write(0, 10, "Our SP", heading_xf)
318 
    sheet.write(0, 11, "Our TP", heading_xf)
9370 kshitij.so 319 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
9359 kshitij.so 320 
    sheet.write(0, 13, "Our Rank", heading_xf)
321 
    sheet.write(0, 14, "Lowest Seller", heading_xf)
322 
    sheet.write(0, 15, "Lowest Offer Price", heading_xf)
323 
    sheet.write(0, 16, "Second Lowest Seller", heading_xf)
324 
    sheet.write(0, 17, "Second Lowest Price", heading_xf)
9368 kshitij.so 325 
    sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)
9381 kshitij.so 326 
    sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
9368 kshitij.so 327 
    sheet.write(0, 20, "Second Lowest Seller Inventory", heading_xf)
328 
    sheet.write(0, 21, "Our NLC", heading_xf)
9381 kshitij.so 329 
    sheet.write(0, 22, "Target TP", heading_xf)
330 
    sheet.write(0, 23, "Target SP", heading_xf)
331 
    sheet.write(0, 24, "Target NLC", heading_xf)
332 
    sheet.write(0, 25, "MARGIN INCREASED POTENTIAL", heading_xf)
9334 kshitij.so 333 
 
334 
    i=1
335 
 
336 
    for data in buyBoxItems:
337 
        supc_data =data[0]
338 
        one_line = data[1]
339 
 
340 
        courierCost = 45
341 
 
342 
        if one_line.weight:
9381 kshitij.so 343 
            slab = int(((one_line.weight+0.05) - .001)/.5)
9334 kshitij.so 344 
        for x in range (0,slab):
345 
            courierCost = courierCost + 35
346 
 
347 
        courierCost = courierCost * 1.1236
348 
 
349 
        sheet.write(i, 0, one_line.item_id)
350 
        sheet.write(i, 1, one_line.category_name)
351 
        sheet.write(i, 2, one_line.product_group)
352 
        sheet.write(i, 3, supc_data.supc)
353 
        sheet.write(i, 4, one_line.brand)
354 
        sheet.write(i, 5, one_line.model_name)
355 
        sheet.write(i, 6, one_line.model_number)
356 
        sheet.write(i, 7, one_line.color)
9359 kshitij.so 357 
        sheet.write(i, 8, one_line.weight)
358 
        sheet.write(i, 9, one_line.risky)
359 
        sheet.write(i, 10, supc_data.ourSp)
360 
        sheet.write(i, 11, round(supc_data.ourSp*0.9597-courierCost))
9370 kshitij.so 361 
        sheet.write(i, 12, supc_data.ourOfferPrice)
9359 kshitij.so 362 
        sheet.write(i, 13, supc_data.rank)
363 
        sheet.write(i, 14, supc_data.lowestSellerName)
9368 kshitij.so 364 
        sheet.write(i, 15, supc_data.lowestOfferPrice)
9359 kshitij.so 365 
        sheet.write(i, 16, supc_data.secondLowestSellerName)
366 
        sheet.write(i, 17, supc_data.secondLowestSellerSp)
9369 kshitij.so 367 
        sheet.write(i, 18, supc_data.secondLowestSellerOfferPrice)
9368 kshitij.so 368 
        sheet.write(i, 19, supc_data.ourInventory)
369 
        sheet.write(i, 20, supc_data.secondLowestSellerInventory)
370 
        sheet.write(i, 21, one_line.our_nlc)
9334 kshitij.so 371 
        if one_line.parent_category ==10011:
372 
            lowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
373 
        else:
374 
            lowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
9381 kshitij.so 375 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
376 
        proposed_tp  = max(lowestTp - max((20, lowestTp*0.002)), lowest_possible_tp)
377 
        sheet.write(i, 22, round(proposed_tp))
378 
        sheet.write(i, 23, round((proposed_tp+courierCost)/0.9597))
9383 kshitij.so 379 
        sheet.write(i, 24, round(proposed_tp*0.988+21))
9391 kshitij.so 380 
        sheet.write(i, 25, round(proposed_tp-(supc_data.ourSp*0.9597-courierCost)))
9334 kshitij.so 381 
        i+=1
9387 kshitij.so 382 
 
383 
 
384 
def createSheetForCometitiveItems(competitiveItems,wbk):
385 
    sheet = wbk.add_sheet('Competitive')
386 
 
387 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
9334 kshitij.so 388 
 
9387 kshitij.so 389 
    excel_integer_format = '0'
390 
    integer_style = xlwt.XFStyle()
391 
    integer_style.num_format_str = excel_integer_format
392 
 
393 
    sheet.write(0, 0, "Item ID", heading_xf)
394 
    sheet.write(0, 1, "Category", heading_xf)
395 
    sheet.write(0, 2, "Product Group.", heading_xf)
396 
    sheet.write(0, 3, "SUPC", heading_xf)
397 
    sheet.write(0, 4, "Brand", heading_xf)
398 
    sheet.write(0, 5, "Model Name", heading_xf)
399 
    sheet.write(0, 6, "Model Number", heading_xf)
400 
    sheet.write(0, 7, "Color", heading_xf)
401 
    sheet.write(0, 8, "Weight", heading_xf)
402 
    sheet.write(0, 9, "Risky", heading_xf)
403 
    sheet.write(0, 10, "Our SP", heading_xf)
404 
    sheet.write(0, 11, "Our TP", heading_xf)
405 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
406 
    sheet.write(0, 13, "Our Rank", heading_xf)
407 
    sheet.write(0, 14, "Lowest Seller", heading_xf)
408 
    sheet.write(0, 15, "Lowest SP", heading_xf)
409 
    sheet.write(0, 16, "Lowest TP", heading_xf)
410 
    sheet.write(0, 17, "Lowest Offer Price", heading_xf)
411 
    sheet.write(0, 18, "Inventory of Top Vendors", heading_xf)
412 
    sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
413 
    sheet.write(0, 20, "Our NLC", heading_xf)
414 
    sheet.write(0, 21, "Lowest Possible TP", heading_xf)
415 
    sheet.write(0, 22, "Lowest Possible SP", heading_xf)
416 
    sheet.write(0, 23, "Competition Basis ", heading_xf)
417 
    sheet.write(0, 24, "Can Compete", heading_xf)
418 
    sheet.write(0, 25, "Target TP", heading_xf)
419 
    sheet.write(0, 26, "Target SP", heading_xf)
420 
    sheet.write(0, 27, "Target NLC", heading_xf)
421 
    sheet.write(0, 28, "Sales Potential", heading_xf)
422 
 
423 
    i, sheet_iterator=1,1
424 
    for data in competitiveItems:
425 
        supc_data =data[0]
426 
        one_line = data[1]
427 
 
428 
        courierCost = 45
429 
 
430 
        if one_line.weight:
431 
            slab = int(((one_line.weight+0.05) - .001)/.5)
432 
        for x in range (0,slab):
433 
            courierCost = courierCost + 35
434 
 
435 
        courierCost = courierCost * 1.1236
436 
 
437 
 
438 
        sheet.write(sheet_iterator, 0, one_line.item_id)
439 
        sheet.write(sheet_iterator, 1, one_line.category_name)
440 
        sheet.write(sheet_iterator, 2, one_line.product_group)
441 
        sheet.write(sheet_iterator, 3, supc_data.supc)
442 
        sheet.write(sheet_iterator, 4, one_line.brand)
443 
        sheet.write(sheet_iterator, 5, one_line.model_name)
444 
        sheet.write(sheet_iterator, 6, one_line.model_number)
445 
        sheet.write(sheet_iterator, 7, one_line.color)
446 
        sheet.write(sheet_iterator, 8, one_line.weight)
447 
        sheet.write(sheet_iterator, 9, one_line.risky)
448 
        sheet.write(sheet_iterator, 10, supc_data.ourSp)
449 
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
450 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
451 
        sheet.write(sheet_iterator, 13, supc_data.rank)
452 
        sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
453 
        sheet.write(sheet_iterator, 15, supc_data.lowestSp)
454 
        if one_line.parent_category ==10011:
455 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
456 
        else:
457 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
458 
        sheet.write(sheet_iterator, 16, round(lowestTp))
459 
        sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
460 
        sheet.write(sheet_iterator, 18, supc_data.otherInventory)
461 
        sheet.write(sheet_iterator, 19, supc_data.ourInventory)
462 
        sheet.write(sheet_iterator, 20, one_line.our_nlc)
463 
        if supc_data.rank==1:
464 
            i+=1
465 
            sheet_iterator+=1
466 
            continue
467 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
468 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
469 
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
470 
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
9390 kshitij.so 471 
        competitionBasis ='TP'
9387 kshitij.so 472 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
9390 kshitij.so 473 
            competitionBasis ='SP'
9387 kshitij.so 474 
            sheet.write(sheet_iterator, 23, 'SP')
475 
        else:
476 
            sheet.write(sheet_iterator, 23, 'TP')
477 
        proposed_tp = 0
478 
        if lowestTp > lowest_possible_tp:
479 
            sheet.write(sheet_iterator, 24, "Yes")
9390 kshitij.so 480 
            if competitionBasis=='SP':
481 
                proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
482 
                proposed_tp = proposed_sp*.09597-courierCost
483 
                sheet.write(sheet_iterator, 25, round(proposed_tp))
484 
                sheet.write(sheet_iterator, 26, round(proposed_sp))
485 
                sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
486 
            else:
487 
                proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
488 
                sheet.write(sheet_iterator, 25, round(proposed_tp))
489 
                sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
490 
                sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
9387 kshitij.so 491 
        else:
492 
            sheet.write(sheet_iterator, 24, "No")
493 
 
494 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
495 
            sheet.write(sheet_iterator, 28, "HIGH")
496 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
497 
            sheet.write(sheet_iterator, 28, "MEDIUM")
498 
        else:
499 
            sheet.write(sheet_iterator, 28, "LOW")
500 
        i= i+1
501 
        sheet_iterator+=1
9334 kshitij.so 502 
 
503 
def main():
504 
    parser = optparse.OptionParser()
505 
    parser.add_option("-f", "--file", dest="filename",
506 
                   default="ItemList.xls", type="string",
507 
                   help="Read the item list from FILE",
508 
                   metavar="FILE")
509 
    (options, args) = parser.parse_args()
510 
    if len(args) != 0:
511 
        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
512 
    filename = options.filename
513 
    read_data(filename)
514 
 
515 
if __name__ == '__main__':
516 
    main()
517