Subversion Repositories SmartDukaan

Rev

Rev 9455 | Rev 9483 | 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 
 
9426 kshitij.so 29 
    def __init__(self, item_id, product_group, category_name, our_nlc, brand, model_name, model_number, color, weight, parent_category, risky, issue):
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
9426 kshitij.so 41 
        self.issue = issue
9334 kshitij.so 42 
 
43 
 
44 
class SnapdealDetails:
9359 kshitij.so 45 
    def __init__(self, supc, ourSp, offerPrice, ourInventory, otherInventory, rank, lowestSellerName,lowestSp,secondLowestSellerName,secondLowestSellerSp,secondLowestSellerInventory,lowestOfferPrice,secondLowestSellerOfferPrice,ourOfferPrice):
9334 kshitij.so 46 
        self.supc = supc
47 
        self.ourSp = ourSp
48 
        self.offerPrice = offerPrice
49 
        self.ourInventory = ourInventory
50 
        self.otherInventory = otherInventory
51 
        self.rank = rank
52 
        self.lowestSellerName = lowestSellerName
53 
        self.lowestSp = lowestSp
54 
        self.secondLowestSellerName = secondLowestSellerName
55 
        self.secondLowestSellerSp = secondLowestSellerSp
56 
        self.secondLowestSellerInventory = secondLowestSellerInventory
9359 kshitij.so 57 
        self.lowestOfferPrice = lowestOfferPrice
58 
        self.secondLowestSellerOfferPrice = secondLowestSellerOfferPrice
59 
        self.ourOfferPrice = ourOfferPrice
9334 kshitij.so 60 
 
61 
 
62 
def fetchDetails(supc_code):
63 
    url="http://www.snapdeal.com/json/gvbps?supc=%s&catId=91"%(supc_code)
64 
    print url
65 
    time.sleep(2)
66 
    req = urllib2.Request(url)
67 
    response = urllib2.urlopen(req)
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)
9453 kshitij.so 122 
        try:
9454 kshitij.so 123 
            item_id = int(sheet.row_values(rownum)[0])
124 
            supc = sheet.row_values(rownum)[1]
9453 kshitij.so 125 
            item = Item.query.filter_by(id=item_id).one()
126 
        except Exception as e:
127 
            print "No item found"
128 
            print e
9455 kshitij.so 129 
            one_line = _SnapdealItemInfo(sheet.row_values(rownum)[0], '', '', 0, '', '','', '', 0, '', False,"item_related")
9453 kshitij.so 130 
            all_supc.append(supc)
131 
            all_lines.append(one_line)
132 
            continue
133 
 
9334 kshitij.so 134 
        category = Category.query.filter_by(id=item.category).one()
9426 kshitij.so 135 
        parent_category = Category.query.filter_by(parent_category_id=category.parent_category_id).first()
136 
        try:
137 
            snapdeal_item = SnapdealItem.query.filter_by(item_id=item_id).one()
138 
            warehouse = iclient.getWarehouse(snapdeal_item.warehouseId)
139 
            item_pricing = iclient.getItemPricing(item_id, warehouse.vendor.id)
140 
            one_line = _SnapdealItemInfo(item.id, item.product_group, parent_category.display_name, item_pricing.nlc, item.brand, item.model_name, item.model_number, item.color, item.weight, category.parent_category_id, item.risky,"")
141 
        except Exception as e:
142 
            print "Problem with item id ",item_id
143 
            print e
144 
            one_line = _SnapdealItemInfo(item.id, item.product_group, parent_category.display_name, item_pricing.nlc, item.brand, item.model_name, item.model_number, item.color, item.weight, category.parent_category_id, item.risky,"our_side")
145 
 
9359 kshitij.so 146 
        #TO BE USED LATER
147 
        #inventory_snapshot = iclient.getInventorySnapshot(0)
148 
        #warehouses_ours = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("OURS"), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)
149 
        #warehouses_third_party = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("THIRD_PARTY "), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)
9334 kshitij.so 150 
        all_supc.append(supc)
151 
        all_lines.append(one_line)
152 
    write_report("/tmp/snapdeal_running.xls", all_lines, all_supc)
153 
 
9426 kshitij.so 154 
 
155 
def exception_sheet(items,wbk):
156 
    sheet = wbk.add_sheet('Exception Item List')
157 
 
158 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
9334 kshitij.so 159 
 
9426 kshitij.so 160 
    excel_integer_format = '0'
161 
    integer_style = xlwt.XFStyle()
162 
    integer_style.num_format_str = excel_integer_format
163 
 
164 
    sheet.write(0, 0, "Item ID", heading_xf)
165 
    sheet.write(0, 1, "Problem Type", heading_xf)
166 
 
167 
    i=1
168 
    for item in items:
169 
        sheet.write(i, 0, item.item_id)
170 
        if len(item.issue)!=0:
9457 kshitij.so 171 
            sheet.write(i, 1, "Wrong item id or wrong warehouse Id")
9426 kshitij.so 172 
        else:
173 
            sheet.write(i, 1, "Unable to fetch info from snapdeal server")
174 
        i+=1
175 
 
176 
 
177 
 
9334 kshitij.so 178 
 
9426 kshitij.so 179 
 
9334 kshitij.so 180 
def write_report(filename, all_lines, all_supc):
181 
 
182 
    buyBoxItems = []
9387 kshitij.so 183 
    competitive = []
9396 kshitij.so 184 
    competitiveNoInventory = []
9426 kshitij.so 185 
    exceptionItems = []
9451 kshitij.so 186 
    negativeMargin = []
9334 kshitij.so 187 
 
188 
    wbk = xlwt.Workbook()
9396 kshitij.so 189 
    sheet = wbk.add_sheet('Can\'t Compete')
9334 kshitij.so 190 
 
191 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
192 
 
193 
    excel_integer_format = '0'
194 
    integer_style = xlwt.XFStyle()
195 
    integer_style.num_format_str = excel_integer_format
196 
 
197 
    sheet.write(0, 0, "Item ID", heading_xf)
198 
    sheet.write(0, 1, "Category", heading_xf)
199 
    sheet.write(0, 2, "Product Group.", heading_xf)
200 
    sheet.write(0, 3, "SUPC", heading_xf)
201 
    sheet.write(0, 4, "Brand", heading_xf)
202 
    sheet.write(0, 5, "Model Name", heading_xf)
203 
    sheet.write(0, 6, "Model Number", heading_xf)
204 
    sheet.write(0, 7, "Color", heading_xf)
9359 kshitij.so 205 
    sheet.write(0, 8, "Weight", heading_xf)
9400 kshitij.so 206 
    sheet.write(0, 9, "Courier Cost", heading_xf)
9399 kshitij.so 207 
    sheet.write(0, 10, "Risky", heading_xf)
208 
    sheet.write(0, 11, "Our SP", heading_xf)
9411 kshitij.so 209 
    sheet.write(0, 13, "Our TP", heading_xf)
210 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
9399 kshitij.so 211 
    sheet.write(0, 14, "Our Rank", heading_xf)
212 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
213 
    sheet.write(0, 16, "Lowest SP", heading_xf)
214 
    sheet.write(0, 17, "Lowest TP", heading_xf)
215 
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)
216 
    sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
217 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
218 
    sheet.write(0, 21, "Our NLC", heading_xf)
219 
    sheet.write(0, 22, "Lowest Possible TP", heading_xf)
220 
    sheet.write(0, 23, "Lowest Possible SP", heading_xf)
221 
    sheet.write(0, 24, "Competition Basis ", heading_xf)
222 
    sheet.write(0, 25, "Can Compete", heading_xf)
223 
    sheet.write(0, 26, "Target TP", heading_xf)
224 
    sheet.write(0, 27, "Target SP", heading_xf)
225 
    sheet.write(0, 28, "Target NLC", heading_xf)
226 
    sheet.write(0, 29, "Sales Potential", heading_xf)
9334 kshitij.so 227 
 
9337 kshitij.so 228 
    i, sheet_iterator=1,1
9334 kshitij.so 229 
    for one_line in all_lines:
9426 kshitij.so 230 
        if len(one_line.issue)!=0:
231 
            exceptionItems.append(one_line)
232 
            i+=1
233 
            continue
9334 kshitij.so 234 
        supc = all_supc[i-1]
9426 kshitij.so 235 
        try:
236 
            supc_data = fetchDetails(supc)
237 
        except Exception as e:
238 
            print "Unable to get information about SUPC Code: ",supc
239 
            exceptionItems.append(one_line)
240 
            i+=1
241 
            continue
9334 kshitij.so 242 
 
243 
        courierCost = 45
244 
 
245 
        if one_line.weight:
9381 kshitij.so 246 
            slab = int(((one_line.weight+0.05) - .001)/.5)
9334 kshitij.so 247 
        for x in range (0,slab):
248 
            courierCost = courierCost + 35
249 
 
250 
        courierCost = courierCost * 1.1236
251 
 
9451 kshitij.so 252 
        if one_line.parent_category ==10011:
253 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
254 
        else:
255 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
256 
 
257 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
258 
 
259 
        if (supc_data.ourSp*0.9597-courierCost < lowest_possible_tp):
260 
            temp=[]
261 
            temp.append(supc_data)
262 
            temp.append(one_line)
263 
            negativeMargin.append(temp)
264 
            i+=1
265 
            continue
266 
 
9334 kshitij.so 267 
        if supc_data.rank==1:
268 
            temp = []
269 
            temp.append(supc_data)
270 
            temp.append(one_line)
271 
            buyBoxItems.append(temp)
272 
            i+=1
273 
            continue
274 
 
9387 kshitij.so 275 
 
9396 kshitij.so 276 
        if (lowestTp > lowest_possible_tp and supc_data.ourInventory!=0):
9387 kshitij.so 277 
            temp=[]
278 
            temp.append(supc_data)
279 
            temp.append(one_line)
280 
            competitive.append(temp)
281 
            i+=1
282 
            continue
283 
 
9396 kshitij.so 284 
        if (lowestTp > lowest_possible_tp and supc_data.ourInventory==0):
285 
            temp=[]
286 
            temp.append(supc_data)
287 
            temp.append(one_line)
288 
            competitiveNoInventory.append(temp)
289 
            i+=1
290 
            continue
9387 kshitij.so 291 
 
9451 kshitij.so 292 
 
9337 kshitij.so 293 
        sheet.write(sheet_iterator, 0, one_line.item_id)
294 
        sheet.write(sheet_iterator, 1, one_line.category_name)
295 
        sheet.write(sheet_iterator, 2, one_line.product_group)
296 
        sheet.write(sheet_iterator, 3, supc)
297 
        sheet.write(sheet_iterator, 4, one_line.brand)
298 
        sheet.write(sheet_iterator, 5, one_line.model_name)
299 
        sheet.write(sheet_iterator, 6, one_line.model_number)
300 
        sheet.write(sheet_iterator, 7, one_line.color)
9359 kshitij.so 301 
        sheet.write(sheet_iterator, 8, one_line.weight)
9400 kshitij.so 302 
        sheet.write(sheet_iterator, 9, round(courierCost))
9399 kshitij.so 303 
        sheet.write(sheet_iterator, 10, one_line.risky)
304 
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
9411 kshitij.so 305 
        sheet.write(sheet_iterator, 13, round(supc_data.ourSp*0.9597-courierCost))
306 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
9399 kshitij.so 307 
        sheet.write(sheet_iterator, 14, supc_data.rank)
308 
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
309 
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
9334 kshitij.so 310 
        if one_line.parent_category ==10011:
311 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
312 
        else:
313 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
9399 kshitij.so 314 
        sheet.write(sheet_iterator, 17, round(lowestTp))
315 
        sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
316 
        sheet.write(sheet_iterator, 19, supc_data.otherInventory)
317 
        sheet.write(sheet_iterator, 20, supc_data.ourInventory)
318 
        sheet.write(sheet_iterator, 21, one_line.our_nlc)
9334 kshitij.so 319 
        if supc_data.rank==1:
320 
            i+=1
9337 kshitij.so 321 
            sheet_iterator+=1
9334 kshitij.so 322 
            continue
323 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
9366 kshitij.so 324 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
9399 kshitij.so 325 
        sheet.write(sheet_iterator, 22, round(lowest_possible_tp))
326 
        sheet.write(sheet_iterator, 23, round(lowest_possible_sp))
9390 kshitij.so 327 
        competitionBasis ='TP'
9411 kshitij.so 328 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice == supc_data.ourSp:
9390 kshitij.so 329 
            competitionBasis ='SP'
9399 kshitij.so 330 
            sheet.write(sheet_iterator, 24, 'SP')
9366 kshitij.so 331 
        else:
9399 kshitij.so 332 
            sheet.write(sheet_iterator, 24, 'TP')
9334 kshitij.so 333 
        proposed_tp = 0
334 
        if lowestTp > lowest_possible_tp:
9399 kshitij.so 335 
            sheet.write(sheet_iterator, 25, "Yes")
9334 kshitij.so 336 
        else:
9399 kshitij.so 337 
            sheet.write(sheet_iterator, 25, "No")
9396 kshitij.so 338 
        if competitionBasis=='SP':
9411 kshitij.so 339 
            proposed_sp = supc_data.lowestSp - max(10, supc_data.lowestSp*0.001)
9396 kshitij.so 340 
            proposed_tp = proposed_sp*0.9597-courierCost
9399 kshitij.so 341 
            sheet.write(sheet_iterator, 26, round(proposed_tp))
342 
            sheet.write(sheet_iterator, 27, round(proposed_sp))
343 
            sheet.write(sheet_iterator, 28, round(proposed_tp*0.988-21))
9396 kshitij.so 344 
        else:
9411 kshitij.so 345 
            proposed_tp  = lowestTp - max(10, lowestTp*0.001)
9399 kshitij.so 346 
            sheet.write(sheet_iterator, 26, round(proposed_tp))
347 
            sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))
348 
            sheet.write(sheet_iterator, 28, round(proposed_tp*0.988-21))
9381 kshitij.so 349 
 
350 
 
9366 kshitij.so 351 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
9399 kshitij.so 352 
            sheet.write(sheet_iterator, 29, "HIGH")
9366 kshitij.so 353 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
9399 kshitij.so 354 
            sheet.write(sheet_iterator, 29, "MEDIUM")
9366 kshitij.so 355 
        else:
9399 kshitij.so 356 
            sheet.write(sheet_iterator, 29, "LOW")
9334 kshitij.so 357 
        i= i+1
9337 kshitij.so 358 
        sheet_iterator+=1
359 
 
9334 kshitij.so 360 
    createSheetForBuyBoxItems(buyBoxItems,wbk)
9387 kshitij.so 361 
    createSheetForCometitiveItems(competitive,wbk)
9396 kshitij.so 362 
    createSheetForCometitiveItemsNoInventory(competitiveNoInventory,wbk)
9426 kshitij.so 363 
    exception_sheet(exceptionItems,wbk)
9451 kshitij.so 364 
    createSheetForNegativeMargin(negativeMargin,wbk)
9334 kshitij.so 365 
    wbk.save(filename)
9365 kshitij.so 366 
 
9367 kshitij.so 367 
    EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@shop2020.in"], " " , "", [get_attachment_part(filename)], ["kshitij.sood@shop2020.in"], [])
9334 kshitij.so 368 
 
9365 kshitij.so 369 
 
370 
 
9334 kshitij.so 371 
def createSheetForBuyBoxItems(buyBoxItems,wbk):
9396 kshitij.so 372 
    sheet = wbk.add_sheet('Lowest')
9334 kshitij.so 373 
 
374 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
375 
 
376 
    excel_integer_format = '0'
377 
    integer_style = xlwt.XFStyle()
378 
    integer_style.num_format_str = excel_integer_format
379 
 
380 
    sheet.write(0, 0, "Item ID", heading_xf)
381 
    sheet.write(0, 1, "Category", heading_xf)
382 
    sheet.write(0, 2, "Product Group.", heading_xf)
383 
    sheet.write(0, 3, "SUPC", heading_xf)
384 
    sheet.write(0, 4, "Brand", heading_xf)
385 
    sheet.write(0, 5, "Model Name", heading_xf)
386 
    sheet.write(0, 6, "Model Number", heading_xf)
387 
    sheet.write(0, 7, "Color", heading_xf)
9359 kshitij.so 388 
    sheet.write(0, 8, "Weight", heading_xf)
9399 kshitij.so 389 
    sheet.write(0, 9, "Courier Cost", heading_xf)
390 
    sheet.write(0, 10, "Risky", heading_xf)
391 
    sheet.write(0, 11, "Our SP", heading_xf)
9411 kshitij.so 392 
    sheet.write(0, 13, "Our TP", heading_xf)
393 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
9399 kshitij.so 394 
    sheet.write(0, 14, "Our Rank", heading_xf)
395 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
396 
    sheet.write(0, 16, "Second Lowest Seller", heading_xf)
397 
    sheet.write(0, 17, "Second Lowest Price", heading_xf)
398 
    sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)
399 
    sheet.write(0, 19, "Second Lowest Seller TP", heading_xf)
9396 kshitij.so 400 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
401 
    sheet.write(0, 21, "Second Lowest Seller Inventory", heading_xf)
402 
    sheet.write(0, 22, "Our NLC", heading_xf)
403 
    sheet.write(0, 23, "Competition Basis", heading_xf)
404 
    sheet.write(0, 24, "Target TP", heading_xf)
405 
    sheet.write(0, 25, "Target SP", heading_xf)
9411 kshitij.so 406 
    sheet.write(0, 26, "MARGIN INCREASED POTENTIAL", heading_xf)
9334 kshitij.so 407 
 
408 
    i=1
409 
 
410 
    for data in buyBoxItems:
411 
        supc_data =data[0]
412 
        one_line = data[1]
413 
 
414 
        courierCost = 45
415 
 
416 
        if one_line.weight:
9381 kshitij.so 417 
            slab = int(((one_line.weight+0.05) - .001)/.5)
9334 kshitij.so 418 
        for x in range (0,slab):
419 
            courierCost = courierCost + 35
420 
 
421 
        courierCost = courierCost * 1.1236
422 
 
423 
        sheet.write(i, 0, one_line.item_id)
424 
        sheet.write(i, 1, one_line.category_name)
425 
        sheet.write(i, 2, one_line.product_group)
426 
        sheet.write(i, 3, supc_data.supc)
427 
        sheet.write(i, 4, one_line.brand)
428 
        sheet.write(i, 5, one_line.model_name)
429 
        sheet.write(i, 6, one_line.model_number)
430 
        sheet.write(i, 7, one_line.color)
9359 kshitij.so 431 
        sheet.write(i, 8, one_line.weight)
9400 kshitij.so 432 
        sheet.write(i, 9, round(courierCost))
9399 kshitij.so 433 
        sheet.write(i, 10, one_line.risky)
434 
        sheet.write(i, 11, supc_data.ourSp)
9411 kshitij.so 435 
        sheet.write(i, 13, round(supc_data.ourSp*0.9597-courierCost))
436 
        sheet.write(i, 12, supc_data.ourOfferPrice)
9399 kshitij.so 437 
        sheet.write(i, 14, supc_data.rank)
438 
        sheet.write(i, 15, supc_data.lowestSellerName)
439 
        sheet.write(i, 16, supc_data.secondLowestSellerName)
440 
        sheet.write(i, 17, supc_data.secondLowestSellerSp)
441 
        sheet.write(i, 18, supc_data.secondLowestSellerOfferPrice)
9334 kshitij.so 442 
        if one_line.parent_category ==10011:
9396 kshitij.so 443 
            secondlowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
9334 kshitij.so 444 
        else:
9396 kshitij.so 445 
            secondlowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
9381 kshitij.so 446 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
9396 kshitij.so 447 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
9401 kshitij.so 448 
        sheet.write(i, 19, round(secondlowestTp))
9396 kshitij.so 449 
        sheet.write(i, 20, supc_data.ourInventory)
450 
        sheet.write(i, 21, supc_data.secondLowestSellerInventory)
451 
        sheet.write(i, 22, one_line.our_nlc)
452 
 
453 
        competitionBasis ='TP'
9411 kshitij.so 454 
        if (supc_data.secondLowestSellerOfferPrice == supc_data.secondLowestSellerSp) and supc_data.ourOfferPrice==supc_data.ourSp:
9396 kshitij.so 455 
            competitionBasis ='SP'
456 
            sheet.write(i, 23, 'SP')
457 
        else:
458 
            sheet.write(i, 23, 'TP')
459 
 
460 
        if competitionBasis=='SP':
461 
            proposed_sp = max(supc_data.secondLowestSellerSp - max((20, supc_data.secondLowestSellerSp*0.002)), lowest_possible_sp)
462 
            proposed_tp = proposed_sp*0.9597-courierCost
463 
            sheet.write(i, 24, round(proposed_tp))
464 
            sheet.write(i, 25, round(proposed_sp))
465 
        else:
466 
            proposed_tp  = max(secondlowestTp - max((20, secondlowestTp*0.002)), lowest_possible_tp)
467 
            sheet.write(i, 24, round(proposed_tp))
468 
            sheet.write(i, 25, round((proposed_tp+courierCost)/0.9597))
469 
 
9411 kshitij.so 470 
        sheet.write(i, 26, round(proposed_tp-(supc_data.ourSp*0.9597-courierCost)))
9334 kshitij.so 471 
        i+=1
9387 kshitij.so 472 
 
473 
 
474 
def createSheetForCometitiveItems(competitiveItems,wbk):
9396 kshitij.so 475 
    sheet = wbk.add_sheet('Can Compete-With Inventory')
9387 kshitij.so 476 
 
477 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
9334 kshitij.so 478 
 
9387 kshitij.so 479 
    excel_integer_format = '0'
480 
    integer_style = xlwt.XFStyle()
481 
    integer_style.num_format_str = excel_integer_format
482 
 
483 
    sheet.write(0, 0, "Item ID", heading_xf)
484 
    sheet.write(0, 1, "Category", heading_xf)
485 
    sheet.write(0, 2, "Product Group.", heading_xf)
486 
    sheet.write(0, 3, "SUPC", heading_xf)
487 
    sheet.write(0, 4, "Brand", heading_xf)
488 
    sheet.write(0, 5, "Model Name", heading_xf)
489 
    sheet.write(0, 6, "Model Number", heading_xf)
490 
    sheet.write(0, 7, "Color", heading_xf)
491 
    sheet.write(0, 8, "Weight", heading_xf)
9399 kshitij.so 492 
    sheet.write(0, 9, "Courier Cost", heading_xf)
493 
    sheet.write(0, 10, "Risky", heading_xf)
494 
    sheet.write(0, 11, "Our SP", heading_xf)
9411 kshitij.so 495 
    sheet.write(0, 13, "Our TP", heading_xf)
496 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
9399 kshitij.so 497 
    sheet.write(0, 14, "Our Rank", heading_xf)
498 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
499 
    sheet.write(0, 16, "Lowest SP", heading_xf)
500 
    sheet.write(0, 17, "Lowest TP", heading_xf)
501 
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)
502 
    sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
503 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
504 
    sheet.write(0, 21, "Our NLC", heading_xf)
505 
    sheet.write(0, 22, "Lowest Possible TP", heading_xf)
506 
    sheet.write(0, 23, "Lowest Possible SP", heading_xf)
507 
    sheet.write(0, 24, "Competition Basis ", heading_xf)
508 
    sheet.write(0, 25, "Can Compete", heading_xf)
509 
    sheet.write(0, 26, "Target TP", heading_xf)
510 
    sheet.write(0, 27, "Target SP", heading_xf)
511 
    sheet.write(0, 28, "Sales Potential", heading_xf)
9387 kshitij.so 512 
 
513 
    i, sheet_iterator=1,1
514 
    for data in competitiveItems:
515 
        supc_data =data[0]
516 
        one_line = data[1]
517 
 
518 
        courierCost = 45
519 
 
520 
        if one_line.weight:
521 
            slab = int(((one_line.weight+0.05) - .001)/.5)
522 
        for x in range (0,slab):
523 
            courierCost = courierCost + 35
524 
 
525 
        courierCost = courierCost * 1.1236
526 
 
527 
 
528 
        sheet.write(sheet_iterator, 0, one_line.item_id)
529 
        sheet.write(sheet_iterator, 1, one_line.category_name)
530 
        sheet.write(sheet_iterator, 2, one_line.product_group)
531 
        sheet.write(sheet_iterator, 3, supc_data.supc)
532 
        sheet.write(sheet_iterator, 4, one_line.brand)
533 
        sheet.write(sheet_iterator, 5, one_line.model_name)
534 
        sheet.write(sheet_iterator, 6, one_line.model_number)
535 
        sheet.write(sheet_iterator, 7, one_line.color)
536 
        sheet.write(sheet_iterator, 8, one_line.weight)
9400 kshitij.so 537 
        sheet.write(sheet_iterator, 9, round(courierCost))
9399 kshitij.so 538 
        sheet.write(sheet_iterator, 10, one_line.risky)
539 
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
9411 kshitij.so 540 
        sheet.write(sheet_iterator, 13, round(supc_data.ourSp*0.9597-courierCost))
541 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
9399 kshitij.so 542 
        sheet.write(sheet_iterator, 14, supc_data.rank)
543 
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
544 
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
9387 kshitij.so 545 
        if one_line.parent_category ==10011:
546 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
547 
        else:
548 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
9399 kshitij.so 549 
        sheet.write(sheet_iterator, 17, round(lowestTp))
550 
        sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
551 
        sheet.write(sheet_iterator, 19, supc_data.otherInventory)
552 
        sheet.write(sheet_iterator, 20, supc_data.ourInventory)
553 
        sheet.write(sheet_iterator, 21, one_line.our_nlc)
9387 kshitij.so 554 
        if supc_data.rank==1:
555 
            i+=1
556 
            sheet_iterator+=1
557 
            continue
558 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
559 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
9399 kshitij.so 560 
        sheet.write(sheet_iterator, 22, round(lowest_possible_tp))
561 
        sheet.write(sheet_iterator, 23, round(lowest_possible_sp))
9390 kshitij.so 562 
        competitionBasis ='TP'
9411 kshitij.so 563 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice==supc_data.ourSp:
9390 kshitij.so 564 
            competitionBasis ='SP'
9399 kshitij.so 565 
            sheet.write(sheet_iterator, 24, 'SP')
9387 kshitij.so 566 
        else:
9399 kshitij.so 567 
            sheet.write(sheet_iterator, 24, 'TP')
9387 kshitij.so 568 
        proposed_tp = 0
569 
        if lowestTp > lowest_possible_tp:
9399 kshitij.so 570 
            sheet.write(sheet_iterator, 25, "Yes")
9390 kshitij.so 571 
            if competitionBasis=='SP':
572 
                proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
9396 kshitij.so 573 
                proposed_tp = proposed_sp*.9597-courierCost
9399 kshitij.so 574 
                sheet.write(sheet_iterator, 26, round(proposed_tp))
575 
                sheet.write(sheet_iterator, 27, round(proposed_sp))
9390 kshitij.so 576 
            else:
577 
                proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
9399 kshitij.so 578 
                sheet.write(sheet_iterator, 26, round(proposed_tp))
579 
                sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))
9387 kshitij.so 580 
        else:
9399 kshitij.so 581 
            sheet.write(sheet_iterator, 25, "No")
9387 kshitij.so 582 
 
583 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
9399 kshitij.so 584 
            sheet.write(sheet_iterator, 28, "HIGH")
9387 kshitij.so 585 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
9399 kshitij.so 586 
            sheet.write(sheet_iterator, 28, "MEDIUM")
9387 kshitij.so 587 
        else:
9399 kshitij.so 588 
            sheet.write(sheet_iterator, 28, "LOW")
9387 kshitij.so 589 
        i= i+1
590 
        sheet_iterator+=1
9334 kshitij.so 591 
 
9396 kshitij.so 592 
 
593 
def createSheetForCometitiveItemsNoInventory(competitiveNoInventoryItems,wbk):
594 
    sheet = wbk.add_sheet('Can Compete-No Inventory')
595 
 
596 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
597 
 
598 
    excel_integer_format = '0'
599 
    integer_style = xlwt.XFStyle()
600 
    integer_style.num_format_str = excel_integer_format
601 
 
602 
    sheet.write(0, 0, "Item ID", heading_xf)
603 
    sheet.write(0, 1, "Category", heading_xf)
604 
    sheet.write(0, 2, "Product Group.", heading_xf)
605 
    sheet.write(0, 3, "SUPC", heading_xf)
606 
    sheet.write(0, 4, "Brand", heading_xf)
607 
    sheet.write(0, 5, "Model Name", heading_xf)
608 
    sheet.write(0, 6, "Model Number", heading_xf)
609 
    sheet.write(0, 7, "Color", heading_xf)
610 
    sheet.write(0, 8, "Weight", heading_xf)
9399 kshitij.so 611 
    sheet.write(0, 9, "Courier Cost", heading_xf)
612 
    sheet.write(0, 10, "Risky", heading_xf)
613 
    sheet.write(0, 11, "Our SP", heading_xf)
9411 kshitij.so 614 
    sheet.write(0, 13, "Our TP", heading_xf)
615 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
9399 kshitij.so 616 
    sheet.write(0, 14, "Our Rank", heading_xf)
617 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
618 
    sheet.write(0, 16, "Lowest SP", heading_xf)
619 
    sheet.write(0, 17, "Lowest TP", heading_xf)
620 
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)
621 
    sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
622 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
623 
    sheet.write(0, 21, "Our NLC", heading_xf)
624 
    sheet.write(0, 22, "Lowest Possible TP", heading_xf)
625 
    sheet.write(0, 23, "Lowest Possible SP", heading_xf)
626 
    sheet.write(0, 24, "Competition Basis ", heading_xf)
627 
    sheet.write(0, 25, "Can Compete", heading_xf)
628 
    sheet.write(0, 26, "Target TP", heading_xf)
629 
    sheet.write(0, 27, "Target SP", heading_xf)
630 
    sheet.write(0, 28, "Sales Potential", heading_xf)
9396 kshitij.so 631 
 
632 
    i, sheet_iterator=1,1
633 
    for data in competitiveNoInventoryItems:
634 
        supc_data =data[0]
635 
        one_line = data[1]
636 
 
637 
        courierCost = 45
638 
 
639 
        if one_line.weight:
640 
            slab = int(((one_line.weight+0.05) - .001)/.5)
641 
        for x in range (0,slab):
642 
            courierCost = courierCost + 35
643 
 
644 
        courierCost = courierCost * 1.1236
645 
 
646 
 
647 
        sheet.write(sheet_iterator, 0, one_line.item_id)
648 
        sheet.write(sheet_iterator, 1, one_line.category_name)
649 
        sheet.write(sheet_iterator, 2, one_line.product_group)
650 
        sheet.write(sheet_iterator, 3, supc_data.supc)
651 
        sheet.write(sheet_iterator, 4, one_line.brand)
652 
        sheet.write(sheet_iterator, 5, one_line.model_name)
653 
        sheet.write(sheet_iterator, 6, one_line.model_number)
654 
        sheet.write(sheet_iterator, 7, one_line.color)
655 
        sheet.write(sheet_iterator, 8, one_line.weight)
9400 kshitij.so 656 
        sheet.write(sheet_iterator, 9, round(courierCost))
9399 kshitij.so 657 
        sheet.write(sheet_iterator, 10, one_line.risky)
658 
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
9411 kshitij.so 659 
        sheet.write(sheet_iterator, 13, '-')
660 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
9399 kshitij.so 661 
        sheet.write(sheet_iterator, 14, supc_data.rank)
662 
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
663 
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
9396 kshitij.so 664 
        if one_line.parent_category ==10011:
665 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
666 
        else:
667 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
9399 kshitij.so 668 
        sheet.write(sheet_iterator, 17, round(lowestTp))
669 
        sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
670 
        sheet.write(sheet_iterator, 19, supc_data.otherInventory)
671 
        sheet.write(sheet_iterator, 20, supc_data.ourInventory)
672 
        sheet.write(sheet_iterator, 21, one_line.our_nlc)
9396 kshitij.so 673 
        if supc_data.rank==1:
674 
            i+=1
675 
            sheet_iterator+=1
676 
            continue
677 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
678 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
9399 kshitij.so 679 
        sheet.write(sheet_iterator, 22, round(lowest_possible_tp))
680 
        sheet.write(sheet_iterator, 23, round(lowest_possible_sp))
9396 kshitij.so 681 
        competitionBasis ='TP'
682 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
683 
            competitionBasis ='SP'
9399 kshitij.so 684 
            sheet.write(sheet_iterator, 24, 'SP')
9396 kshitij.so 685 
        else:
9399 kshitij.so 686 
            sheet.write(sheet_iterator, 24, 'TP')
9396 kshitij.so 687 
        proposed_tp = 0
688 
        if lowestTp > lowest_possible_tp:
9399 kshitij.so 689 
            sheet.write(sheet_iterator, 25, "Yes")
9396 kshitij.so 690 
            if competitionBasis=='SP':
691 
                proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
692 
                proposed_tp = proposed_sp*.9597-courierCost
9399 kshitij.so 693 
                sheet.write(sheet_iterator, 26, round(proposed_tp))
694 
                sheet.write(sheet_iterator, 27, round(proposed_sp))
9396 kshitij.so 695 
            else:
696 
                proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
9399 kshitij.so 697 
                sheet.write(sheet_iterator, 26, round(proposed_tp))
698 
                sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))
9396 kshitij.so 699 
        else:
9399 kshitij.so 700 
            sheet.write(sheet_iterator, 25, "No")
9396 kshitij.so 701 
 
702 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
9399 kshitij.so 703 
            sheet.write(sheet_iterator, 28, "HIGH")
9396 kshitij.so 704 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
9399 kshitij.so 705 
            sheet.write(sheet_iterator, 28, "MEDIUM")
9396 kshitij.so 706 
        else:
9399 kshitij.so 707 
            sheet.write(sheet_iterator, 28, "LOW")
9396 kshitij.so 708 
        i= i+1
709 
        sheet_iterator+=1
710 
 
9451 kshitij.so 711 
def createSheetForNegativeMargin(negativeMargin,wbk):
712 
    sheet = wbk.add_sheet('Negative Margin')
713 
 
714 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
9396 kshitij.so 715 
 
9451 kshitij.so 716 
    excel_integer_format = '0'
717 
    integer_style = xlwt.XFStyle()
718 
    integer_style.num_format_str = excel_integer_format
719 
 
720 
    sheet.write(0, 0, "Item ID", heading_xf)
721 
    sheet.write(0, 1, "Category", heading_xf)
722 
    sheet.write(0, 2, "Product Group.", heading_xf)
723 
    sheet.write(0, 3, "SUPC", heading_xf)
724 
    sheet.write(0, 4, "Brand", heading_xf)
725 
    sheet.write(0, 5, "Model Name", heading_xf)
726 
    sheet.write(0, 6, "Model Number", heading_xf)
727 
    sheet.write(0, 7, "Color", heading_xf)
728 
    sheet.write(0, 8, "Weight", heading_xf)
729 
    sheet.write(0, 9, "Courier Cost", heading_xf)
730 
    sheet.write(0, 10, "Risky", heading_xf)
731 
    sheet.write(0, 11, "Our SP", heading_xf)
732 
    sheet.write(0, 13, "Our TP", heading_xf)
733 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
734 
    sheet.write(0, 14, "Our Rank", heading_xf)
735 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
736 
    sheet.write(0, 16, "Lowest SP", heading_xf)
737 
    sheet.write(0, 17, "Lowest TP", heading_xf)
738 
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)
739 
    sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
740 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
741 
    sheet.write(0, 21, "Our NLC", heading_xf)
742 
    sheet.write(0, 22, "Lowest Possible TP", heading_xf)
743 
    sheet.write(0, 23, "Lowest Possible SP", heading_xf)
744 
    sheet.write(0, 24, "Competition Basis ", heading_xf)
745 
    sheet.write(0, 25, "Can Compete", heading_xf)
746 
    sheet.write(0, 26, "Target TP", heading_xf)
747 
    sheet.write(0, 27, "Target SP", heading_xf)
748 
    sheet.write(0, 28, "Sales Potential", heading_xf)
749 
 
750 
    i, sheet_iterator=1,1
751 
    for data in negativeMargin:
752 
        supc_data =data[0]
753 
        one_line = data[1]
754 
 
755 
        courierCost = 45
756 
 
757 
        if one_line.weight:
758 
            slab = int(((one_line.weight+0.05) - .001)/.5)
759 
        for x in range (0,slab):
760 
            courierCost = courierCost + 35
761 
 
762 
        courierCost = courierCost * 1.1236
763 
 
764 
 
765 
        sheet.write(sheet_iterator, 0, one_line.item_id)
766 
        sheet.write(sheet_iterator, 1, one_line.category_name)
767 
        sheet.write(sheet_iterator, 2, one_line.product_group)
768 
        sheet.write(sheet_iterator, 3, supc_data.supc)
769 
        sheet.write(sheet_iterator, 4, one_line.brand)
770 
        sheet.write(sheet_iterator, 5, one_line.model_name)
771 
        sheet.write(sheet_iterator, 6, one_line.model_number)
772 
        sheet.write(sheet_iterator, 7, one_line.color)
773 
        sheet.write(sheet_iterator, 8, one_line.weight)
774 
        sheet.write(sheet_iterator, 9, round(courierCost))
775 
        sheet.write(sheet_iterator, 10, one_line.risky)
776 
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
777 
        sheet.write(sheet_iterator, 13, '-')
778 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
779 
        sheet.write(sheet_iterator, 14, supc_data.rank)
780 
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
781 
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
782 
        if one_line.parent_category ==10011:
783 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
784 
        else:
785 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
786 
        sheet.write(sheet_iterator, 17, round(lowestTp))
787 
        sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
788 
        sheet.write(sheet_iterator, 19, supc_data.otherInventory)
789 
        sheet.write(sheet_iterator, 20, supc_data.ourInventory)
790 
        sheet.write(sheet_iterator, 21, one_line.our_nlc)
791 
        if supc_data.rank==1:
792 
            i+=1
793 
            sheet_iterator+=1
794 
            continue
795 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
796 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
797 
        sheet.write(sheet_iterator, 22, round(lowest_possible_tp))
798 
        sheet.write(sheet_iterator, 23, round(lowest_possible_sp))
799 
        competitionBasis ='TP'
800 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
801 
            competitionBasis ='SP'
802 
            sheet.write(sheet_iterator, 24, 'SP')
803 
        else:
804 
            sheet.write(sheet_iterator, 24, 'TP')
805 
        proposed_tp = 0
806 
        if lowestTp > lowest_possible_tp:
807 
            sheet.write(sheet_iterator, 25, "Yes")
808 
            if competitionBasis=='SP':
809 
                proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
810 
                proposed_tp = proposed_sp*.9597-courierCost
811 
                sheet.write(sheet_iterator, 26, round(proposed_tp))
812 
                sheet.write(sheet_iterator, 27, round(proposed_sp))
813 
            else:
814 
                proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
815 
                sheet.write(sheet_iterator, 26, round(proposed_tp))
816 
                sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))
817 
        else:
818 
            sheet.write(sheet_iterator, 25, "No")
819 
 
820 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
821 
            sheet.write(sheet_iterator, 28, "HIGH")
822 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
823 
            sheet.write(sheet_iterator, 28, "MEDIUM")
824 
        else:
825 
            sheet.write(sheet_iterator, 28, "LOW")
826 
        i= i+1
827 
        sheet_iterator+=1
828 
 
9396 kshitij.so 829 
 
9334 kshitij.so 830 
def main():
831 
    parser = optparse.OptionParser()
832 
    parser.add_option("-f", "--file", dest="filename",
833 
                   default="ItemList.xls", type="string",
834 
                   help="Read the item list from FILE",
835 
                   metavar="FILE")
836 
    (options, args) = parser.parse_args()
837 
    if len(args) != 0:
838 
        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
839 
    filename = options.filename
840 
    read_data(filename)
841 
 
842 
if __name__ == '__main__':
843 
    main()
9451 kshitij.so 844