Subversion Repositories SmartDukaan

Rev

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