Subversion Repositories SmartDukaan

Rev

Rev 9512 | Rev 9618 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 9512 Rev 9520
Line 166... Line 166...
166 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
 166 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
167
167
168 
    excel_integer_format = '0'
 168 
    excel_integer_format = '0'
169 
    integer_style = xlwt.XFStyle()
 169 
    integer_style = xlwt.XFStyle()
170 
    integer_style.num_format_str = excel_integer_format
 170 
    integer_style.num_format_str = excel_integer_format
- 
 
 171 
    xstr = lambda s: s or ""
171
172
172 
    sheet.write(0, 0, "Item ID", heading_xf)
 173 
    sheet.write(0, 0, "Item ID", heading_xf)
173 
    sheet.write(0, 1, "Brand", heading_xf)
 174 
    sheet.write(0, 1, "Brand", heading_xf)
174 
    sheet.write(0, 2, "Model Name", heading_xf)
 175 
    sheet.write(0, 2, "Product Name", heading_xf)
175 
    sheet.write(0, 3, "Model Number", heading_xf)
 - 
 
176 
    sheet.write(0, 4, "Color", heading_xf)
 - 
 
177 
    sheet.write(0, 5, "Problem Type", heading_xf)
 176 
    sheet.write(0, 3, "Problem Type", heading_xf)
178
177
179 
    i=1
 178 
    i=1
180 
    for item in items:
 179 
    for item in items:
181 
        sheet.write(i, 0, item.item_id)
 180 
        sheet.write(i, 0, item.item_id)
182 
        sheet.write(i, 1, item.brand)
 181 
        sheet.write(i, 1, item.brand)
183 
        sheet.write(i, 2, item.model_name)
 - 
 
184 
        sheet.write(i, 3, item.model_number)
 182 
        sheet.write(i, 2, xstr(item.brand)+" "+xstr(item.model_name)+" "+xstr(item.model_number)+" "+xstr(item.color))
185 
        sheet.write(i, 4, item.color)
 - 
 
186 
        if len(item.issue)!=0:
 183 
        if len(item.issue)!=0:
187 
            sheet.write(i, 5, "Wrong item id or wrong warehouse Id")
 184 
            sheet.write(i, 3, "Wrong item id or wrong warehouse Id")
188 
        else:
 185 
        else:
189 
            sheet.write(i, 5, "Unable to fetch info from snapdeal server")
 186 
            sheet.write(i, 3, "Unable to fetch info from snapdeal server")
190 
        i+=1
 187 
        i+=1
191
188
192
189
193
190
194 
 
 191 
 
Line 201... Line 198...
201 
    exceptionItems = []
 198 
    exceptionItems = []
202 
    negativeMargin = []
 199 
    negativeMargin = []
203
200
204 
    wbk = xlwt.Workbook()
 201 
    wbk = xlwt.Workbook()
205 
    sheet = wbk.add_sheet('Can\'t Compete')
 202 
    sheet = wbk.add_sheet('Can\'t Compete')
206 
 
 - 
 
- 
 
 203 
    xstr = lambda s: s or ""
207 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
 204 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
208
205
209 
    excel_integer_format = '0'
 206 
    excel_integer_format = '0'
210 
    integer_style = xlwt.XFStyle()
 207 
    integer_style = xlwt.XFStyle()
211 
    integer_style.num_format_str = excel_integer_format
 208 
    integer_style.num_format_str = excel_integer_format
Line 213... Line 210...
213 
    sheet.write(0, 0, "Item ID", heading_xf)
 210 
    sheet.write(0, 0, "Item ID", heading_xf)
214 
    sheet.write(0, 1, "Category", heading_xf)
 211 
    sheet.write(0, 1, "Category", heading_xf)
215 
    sheet.write(0, 2, "Product Group.", heading_xf)
 212 
    sheet.write(0, 2, "Product Group.", heading_xf)
216 
    sheet.write(0, 3, "SUPC", heading_xf)
 213 
    sheet.write(0, 3, "SUPC", heading_xf)
217 
    sheet.write(0, 4, "Brand", heading_xf)
 214 
    sheet.write(0, 4, "Brand", heading_xf)
218 
    sheet.write(0, 5, "Model Name", heading_xf)
 215 
    sheet.write(0, 5, "Product Name", heading_xf)
219 
    sheet.write(0, 6, "Model Number", heading_xf)
 - 
 
220 
    sheet.write(0, 7, "Color", heading_xf)
 - 
 
221 
    sheet.write(0, 8, "Weight", heading_xf)
 216 
    sheet.write(0, 6, "Weight", heading_xf)
222 
    sheet.write(0, 9, "Courier Cost", heading_xf)
 217 
    sheet.write(0, 7, "Courier Cost", heading_xf)
223 
    sheet.write(0, 10, "Risky", heading_xf)
 218 
    sheet.write(0, 8, "Risky", heading_xf)
224 
    sheet.write(0, 11, "Our SP", heading_xf)
 219 
    sheet.write(0, 9, "Our SP", heading_xf)
225 
    sheet.write(0, 13, "Our TP", heading_xf)
 220 
    sheet.write(0, 11, "Our TP", heading_xf)
226 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
 221 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
227 
    sheet.write(0, 14, "Our Rank", heading_xf)
 222 
    sheet.write(0, 12, "Our Rank", heading_xf)
228 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
 223 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
229 
    sheet.write(0, 16, "Lowest SP", heading_xf)
 224 
    sheet.write(0, 14, "Lowest SP", heading_xf)
230 
    sheet.write(0, 17, "Lowest TP", heading_xf)
 225 
    sheet.write(0, 15, "Lowest TP", heading_xf)
231 
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)
 226 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
232 
    sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
 227 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
233 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
 228 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
234 
    sheet.write(0, 21, "Our NLC", heading_xf)
 229 
    sheet.write(0, 19, "Our NLC", heading_xf)
235 
    sheet.write(0, 22, "Our Dealer Price", heading_xf)
 230 
    sheet.write(0, 20, "Our Dealer Price", heading_xf)
236 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
 231 
    sheet.write(0, 21, "Lowest Possible TP", heading_xf)
237 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
 232 
    sheet.write(0, 22, "Lowest Possible SP", heading_xf)
238 
    sheet.write(0, 25, "Competition Basis ", heading_xf)
 233 
    sheet.write(0, 23, "Competition Basis ", heading_xf)
239 
    sheet.write(0, 26, "Can Compete", heading_xf)
 234 
    sheet.write(0, 24, "Can Compete", heading_xf)
240 
    sheet.write(0, 27, "Target TP", heading_xf)
 235 
    sheet.write(0, 25, "Target TP", heading_xf)
241 
    sheet.write(0, 28, "Target SP", heading_xf)
236 
    sheet.write(0, 26, "Target SP", heading_xf)
242 
    sheet.write(0, 29, "Target NLC", heading_xf)
 237 
    sheet.write(0, 27, "Target NLC", heading_xf)
243 
    sheet.write(0, 30, "Sales Potential", heading_xf)
 238 
    sheet.write(0, 28, "Sales Potential", heading_xf)
244
239
245 
    i, sheet_iterator=1,1
 240 
    i, sheet_iterator=1,1
246 
    for one_line in all_lines:
 241 
    for one_line in all_lines:
247 
        if len(one_line.issue)!=0:
 242 
        if len(one_line.issue)!=0:
248 
            exceptionItems.append(one_line)
 243 
            exceptionItems.append(one_line)
Line 333... Line 328...
333 
        sheet.write(sheet_iterator, 0, one_line.item_id)
 328 
        sheet.write(sheet_iterator, 0, one_line.item_id)
334 
        sheet.write(sheet_iterator, 1, one_line.category_name)
 329 
        sheet.write(sheet_iterator, 1, one_line.category_name)
335 
        sheet.write(sheet_iterator, 2, one_line.product_group)
 330 
        sheet.write(sheet_iterator, 2, one_line.product_group)
336 
        sheet.write(sheet_iterator, 3, supc)
 331 
        sheet.write(sheet_iterator, 3, supc)
337 
        sheet.write(sheet_iterator, 4, one_line.brand)
 332 
        sheet.write(sheet_iterator, 4, one_line.brand)
338 
        sheet.write(sheet_iterator, 5, one_line.model_name)
 - 
 
339 
        sheet.write(sheet_iterator, 6, one_line.model_number)
 333 
        sheet.write(sheet_iterator, 5, xstr(one_line.brand)+" "+xstr(one_line.model_name)+" "+xstr(one_line.model_number)+" "+xstr(one_line.color))
340 
        sheet.write(sheet_iterator, 7, one_line.color)
 - 
 
341 
        sheet.write(sheet_iterator, 8, one_line.weight)
 334 
        sheet.write(sheet_iterator, 6, one_line.weight)
342 
        sheet.write(sheet_iterator, 9, round(courierCost))
 335 
        sheet.write(sheet_iterator, 7, round(courierCost))
343 
        sheet.write(sheet_iterator, 10, one_line.risky)
 336 
        sheet.write(sheet_iterator, 8, one_line.risky)
344 
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
 337 
        sheet.write(sheet_iterator, 9, supc_data.ourSp)
345 
        sheet.write(sheet_iterator, 13, round(supc_data.ourSp*0.9597-courierCost))
 338 
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
346 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
 339 
        sheet.write(sheet_iterator, 10, supc_data.ourOfferPrice)
347 
        sheet.write(sheet_iterator, 14, supc_data.rank)
 340 
        sheet.write(sheet_iterator, 12, supc_data.rank)
348 
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
 341 
        sheet.write(sheet_iterator, 13, supc_data.lowestSellerName)
349 
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
 342 
        sheet.write(sheet_iterator, 14, supc_data.lowestSp)
350 
        if one_line.parent_category ==10011:
 343 
        if one_line.parent_category ==10011:
351 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
 344 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
352 
        else:
 345 
        else:
353 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
 346 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
354 
        sheet.write(sheet_iterator, 17, round(lowestTp))
 347 
        sheet.write(sheet_iterator, 15, round(lowestTp))
355 
        sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
 348 
        sheet.write(sheet_iterator, 16, supc_data.lowestOfferPrice)
356 
        sheet.write(sheet_iterator, 19, supc_data.otherInventory)
 349 
        sheet.write(sheet_iterator, 17, supc_data.otherInventory)
357 
        sheet.write(sheet_iterator, 20, supc_data.ourInventory)
 350 
        sheet.write(sheet_iterator, 18, supc_data.ourInventory)
358 
        sheet.write(sheet_iterator, 21, one_line.our_nlc)
 351 
        sheet.write(sheet_iterator, 19, one_line.our_nlc)
359 
        sheet.write(sheet_iterator, 22, one_line.dealerPrice)
 352 
        sheet.write(sheet_iterator, 20, one_line.dealerPrice)
360 
        if supc_data.rank==1:
 353 
        if supc_data.rank==1:
361 
            i+=1
 354 
            i+=1
362 
            sheet_iterator+=1
 355 
            sheet_iterator+=1
363 
            continue
 356 
            continue
364 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
 357 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
365 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
 358 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
366 
        sheet.write(sheet_iterator, 23, round(lowest_possible_tp))
 359 
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
367 
        sheet.write(sheet_iterator, 24, round(lowest_possible_sp))
 360 
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
368 
        competitionBasis ='TP'
 361 
        competitionBasis ='TP'
369 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice == supc_data.ourSp:
 362 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice == supc_data.ourSp:
370 
            competitionBasis ='SP'
 363 
            competitionBasis ='SP'
371 
            sheet.write(sheet_iterator, 25, 'SP')
 364 
            sheet.write(sheet_iterator, 23, 'SP')
372 
        else:
 365 
        else:
373 
            sheet.write(sheet_iterator, 25, 'TP')
 366 
            sheet.write(sheet_iterator, 23, 'TP')
374 
        proposed_tp = 0
 367 
        proposed_tp = 0
375 
        if competitionBasis=='SP':
 368 
        if competitionBasis=='SP':
376 
            if supc_data.lowestSp > lowest_possible_sp:
 369 
            if supc_data.lowestSp > lowest_possible_sp:
377 
                sheet.write(sheet_iterator, 26, "Yes")
 370 
                sheet.write(sheet_iterator, 24, "Yes")
378 
            else:
 371 
            else:
379 
                sheet.write(sheet_iterator, 26, "No")
 372 
                sheet.write(sheet_iterator, 24, "No")
380 
        else:
 373 
        else:
381 
            if lowestTp > lowest_possible_tp:
 374 
            if lowestTp > lowest_possible_tp:
382 
                sheet.write(sheet_iterator, 26, "Yes")
 375 
                sheet.write(sheet_iterator, 24, "Yes")
383 
            else:
 376 
            else:
384 
                sheet.write(sheet_iterator, 26, "No")
 377 
                sheet.write(sheet_iterator, 24, "No")
385 
        if competitionBasis=='SP':
 378 
        if competitionBasis=='SP':
386 
            proposed_sp = supc_data.lowestSp - max(10, supc_data.lowestSp*0.001)
 379 
            proposed_sp = supc_data.lowestSp - max(10, supc_data.lowestSp*0.001)
387 
            proposed_tp = proposed_sp*0.9597-courierCost
 380 
            proposed_tp = proposed_sp*0.9597-courierCost
388 
            sheet.write(sheet_iterator, 27, round(proposed_tp))
 381 
            sheet.write(sheet_iterator, 25, round(proposed_tp))
389 
            sheet.write(sheet_iterator, 28, round(proposed_sp))
 382 
            sheet.write(sheet_iterator, 26, round(proposed_sp))
390 
            sheet.write(sheet_iterator, 29, round(proposed_tp*0.988-21))
383 
            sheet.write(sheet_iterator, 27, round(proposed_tp*0.988-21))
391 
        else:
384 
        else:
392 
            proposed_tp  = lowestTp - max(10, lowestTp*0.001)
 385 
            proposed_tp  = lowestTp - max(10, lowestTp*0.001)
393 
            sheet.write(sheet_iterator, 27, round(proposed_tp))
 386 
            sheet.write(sheet_iterator, 25, round(proposed_tp))
394 
            sheet.write(sheet_iterator, 28, round((proposed_tp+courierCost)/0.9597))
 387 
            sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
395 
            sheet.write(sheet_iterator, 29, round(proposed_tp*0.988-21))
388 
            sheet.write(sheet_iterator, 27, round(proposed_tp*0.988-21))
396
389
397
390
398 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
 391 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
399 
            sheet.write(sheet_iterator, 30, "HIGH")
 392 
            sheet.write(sheet_iterator, 28, "HIGH")
400 
        elif (supc_data.lowestOfferPrice -
 - 
 
401 
               one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
 393 
        elif (supc_data.lowestOfferPrice -one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
402 
            sheet.write(sheet_iterator, 30, "MEDIUM")
 394 
            sheet.write(sheet_iterator, 28, "MEDIUM")
403 
        else:
 395 
        else:
404 
            sheet.write(sheet_iterator, 30, "LOW")
 396 
            sheet.write(sheet_iterator, 28, "LOW")
405 
        i= i+1
 397 
        i= i+1
406 
        sheet_iterator+=1
 398 
        sheet_iterator+=1
407
399
408 
    createSheetForBuyBoxItems(buyBoxItems,wbk)
 400 
    createSheetForBuyBoxItems(buyBoxItems,wbk)
409 
    createSheetForCometitiveItems(competitive,wbk)
 401 
    createSheetForCometitiveItems(competitive,wbk)
Line 423... Line 415...
423 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
 415 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
424
416
425 
    excel_integer_format = '0'
 417 
    excel_integer_format = '0'
426 
    integer_style = xlwt.XFStyle()
 418 
    integer_style = xlwt.XFStyle()
427 
    integer_style.num_format_str = excel_integer_format
 419 
    integer_style.num_format_str = excel_integer_format
- 
 
 420 
    xstr = lambda s: s or ""
428
421
429 
    sheet.write(0, 0, "Item ID", heading_xf)
 422 
    sheet.write(0, 0, "Item ID", heading_xf)
430 
    sheet.write(0, 1, "Category", heading_xf)
 423 
    sheet.write(0, 1, "Category", heading_xf)
431 
    sheet.write(0, 2, "Product Group.", heading_xf)
 424 
    sheet.write(0, 2, "Product Group.", heading_xf)
432 
    sheet.write(0, 3, "SUPC", heading_xf)
 425 
    sheet.write(0, 3, "SUPC", heading_xf)
433 
    sheet.write(0, 4, "Brand", heading_xf)
 426 
    sheet.write(0, 4, "Brand", heading_xf)
434 
    sheet.write(0, 5, "Model Name", heading_xf)
 427 
    sheet.write(0, 5, "Product Name", heading_xf)
435 
    sheet.write(0, 6, "Model Number", heading_xf)
 - 
 
436 
    sheet.write(0, 7, "Color", heading_xf)
 - 
 
437 
    sheet.write(0, 8, "Weight", heading_xf)
 428 
    sheet.write(0, 6, "Weight", heading_xf)
438 
    sheet.write(0, 9, "Courier Cost", heading_xf)
 429 
    sheet.write(0, 7, "Courier Cost", heading_xf)
439 
    sheet.write(0, 10, "Risky", heading_xf)
 430 
    sheet.write(0, 8, "Risky", heading_xf)
440 
    sheet.write(0, 11, "Our SP", heading_xf)
 431 
    sheet.write(0, 9, "Our SP", heading_xf)
441 
    sheet.write(0, 13, "Our TP", heading_xf)
 432 
    sheet.write(0, 11, "Our TP", heading_xf)
442 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
 433 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
443 
    sheet.write(0, 14, "Our Rank", heading_xf)
 434 
    sheet.write(0, 12, "Our Rank", heading_xf)
444 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
 435 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
445 
    sheet.write(0, 16, "Second Lowest Seller", heading_xf)
 436 
    sheet.write(0, 14, "Second Lowest Seller", heading_xf)
446 
    sheet.write(0, 17, "Second Lowest Price", heading_xf)
 437 
    sheet.write(0, 15, "Second Lowest Price", heading_xf)
447 
    sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)
 438 
    sheet.write(0, 16, "Second Lowest Offer Price", heading_xf)
448 
    sheet.write(0, 19, "Second Lowest Seller TP", heading_xf)
 439 
    sheet.write(0, 17, "Second Lowest Seller TP", heading_xf)
449 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
 440 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
450 
    sheet.write(0, 21, "Second Lowest Seller Inventory", heading_xf)
 441 
    sheet.write(0, 19, "Second Lowest Seller Inventory", heading_xf)
451 
    sheet.write(0, 22, "Our NLC", heading_xf)
 442 
    sheet.write(0, 20, "Our NLC", heading_xf)
452 
    sheet.write(0, 23, "Our Dealer Price", heading_xf)
 443 
    sheet.write(0, 21, "Our Dealer Price", heading_xf)
453 
    sheet.write(0, 24, "Competition Basis", heading_xf)
 444 
    sheet.write(0, 22, "Competition Basis", heading_xf)
454 
    sheet.write(0, 25, "Target TP", heading_xf)
 445 
    sheet.write(0, 23, "Target TP", heading_xf)
455 
    sheet.write(0, 26, "Target SP", heading_xf)
 446 
    sheet.write(0, 24, "Target SP", heading_xf)
456 
    sheet.write(0, 27, "MARGIN INCREASED POTENTIAL", heading_xf)
 447 
    sheet.write(0, 25, "MARGIN INCREASED POTENTIAL", heading_xf)
457
448
458 
    i=1
 449 
    i=1
459
450
460 
    for data in buyBoxItems:
 451 
    for data in buyBoxItems:
461 
        supc_data =data[0]
 452 
        supc_data =data[0]
Line 473... Line 464...
473 
        sheet.write(i, 0, one_line.item_id)
 464 
        sheet.write(i, 0, one_line.item_id)
474 
        sheet.write(i, 1, one_line.category_name)
 465 
        sheet.write(i, 1, one_line.category_name)
475 
        sheet.write(i, 2, one_line.product_group)
 466 
        sheet.write(i, 2, one_line.product_group)
476 
        sheet.write(i, 3, supc_data.supc)
 467 
        sheet.write(i, 3, supc_data.supc)
477 
        sheet.write(i, 4, one_line.brand)
 468 
        sheet.write(i, 4, one_line.brand)
478 
        sheet.write(i, 5, one_line.model_name)
 - 
 
479 
        sheet.write(i, 6, one_line.model_number)
 469 
        sheet.write(i, 5, xstr(one_line.brand)+" "+xstr(one_line.model_name)+" "+xstr(one_line.model_number)+" "+xstr(one_line.color))
480 
        sheet.write(i, 7, one_line.color)
 - 
 
481 
        sheet.write(i, 8, one_line.weight)
 470 
        sheet.write(i, 6, one_line.weight)
482 
        sheet.write(i, 9, round(courierCost))
 471 
        sheet.write(i, 7, round(courierCost))
483 
        sheet.write(i, 10, one_line.risky)
 472 
        sheet.write(i, 8, one_line.risky)
484 
        sheet.write(i, 11, supc_data.ourSp)
 473 
        sheet.write(i, 9, supc_data.ourSp)
485 
        sheet.write(i, 13, round(supc_data.ourSp*0.9597-courierCost))
 474 
        sheet.write(i, 11, round(supc_data.ourSp*0.9597-courierCost))
486 
        sheet.write(i, 12, supc_data.ourOfferPrice)
 475 
        sheet.write(i, 10, supc_data.ourOfferPrice)
487 
        sheet.write(i, 14, supc_data.rank)
 476 
        sheet.write(i, 12, supc_data.rank)
488 
        sheet.write(i, 15, supc_data.lowestSellerName)
 477 
        sheet.write(i, 13, supc_data.lowestSellerName)
489 
        sheet.write(i, 16, supc_data.secondLowestSellerName)
 478 
        sheet.write(i, 14, supc_data.secondLowestSellerName)
490 
        sheet.write(i, 17, supc_data.secondLowestSellerSp)
 479 
        sheet.write(i, 15, supc_data.secondLowestSellerSp)
491 
        sheet.write(i, 18, supc_data.secondLowestSellerOfferPrice)
 480 
        sheet.write(i, 16, supc_data.secondLowestSellerOfferPrice)
492 
        if one_line.parent_category ==10011:
 481 
        if one_line.parent_category ==10011:
493 
            secondlowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
 482 
            secondlowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
494 
        else:
 483 
        else:
495 
            secondlowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
 484 
            secondlowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
496 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
 485 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
497 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
 486 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
498 
        sheet.write(i, 19, round(secondlowestTp))
 487 
        sheet.write(i, 17, round(secondlowestTp))
499 
        sheet.write(i, 20, supc_data.ourInventory)
 488 
        sheet.write(i, 18, supc_data.ourInventory)
500 
        sheet.write(i, 21, supc_data.secondLowestSellerInventory)
 489 
        sheet.write(i, 19, supc_data.secondLowestSellerInventory)
501 
        sheet.write(i, 22, one_line.our_nlc)
 490 
        sheet.write(i, 20, one_line.our_nlc)
502 
        sheet.write(i, 23, one_line.dealerPrice)
 491 
        sheet.write(i, 21, one_line.dealerPrice)
503
492
504 
        competitionBasis ='TP'
 493 
        competitionBasis ='TP'
505 
        if (supc_data.secondLowestSellerOfferPrice == supc_data.secondLowestSellerSp) and supc_data.ourOfferPrice==supc_data.ourSp:
 494 
        if (supc_data.secondLowestSellerOfferPrice == supc_data.secondLowestSellerSp) and supc_data.ourOfferPrice==supc_data.ourSp:
506 
            competitionBasis ='SP'
 495 
            competitionBasis ='SP'
507 
            sheet.write(i, 24, 'SP')
 496 
            sheet.write(i, 22, 'SP')
508 
        else:
 497 
        else:
509 
            sheet.write(i, 24, 'TP')
 498 
            sheet.write(i, 22, 'TP')
510
499
511 
        if competitionBasis=='SP':
 500 
        if competitionBasis=='SP':
512 
            proposed_sp = max(supc_data.secondLowestSellerSp - max((20, supc_data.secondLowestSellerSp*0.002)), lowest_possible_sp)
 501 
            proposed_sp = max(supc_data.secondLowestSellerSp - max((20, supc_data.secondLowestSellerSp*0.002)), lowest_possible_sp)
513 
            proposed_tp = proposed_sp*0.9597-courierCost
 502 
            proposed_tp = proposed_sp*0.9597-courierCost
514 
            sheet.write(i, 25, round(proposed_tp))
 503 
            sheet.write(i, 23, round(proposed_tp))
515 
            sheet.write(i, 26, round(proposed_sp))
 504 
            sheet.write(i, 24, round(proposed_sp))
516 
        else:
505 
        else:
517 
            proposed_tp  = max(secondlowestTp - max((20, secondlowestTp*0.002)), lowest_possible_tp)
 506 
            proposed_tp  = max(secondlowestTp - max((20, secondlowestTp*0.002)), lowest_possible_tp)
518 
            sheet.write(i, 25, round(proposed_tp))
 507 
            sheet.write(i, 23, round(proposed_tp))
519 
            sheet.write(i, 26, round((proposed_tp+courierCost)/0.9597))
 508 
            sheet.write(i, 24, round((proposed_tp+courierCost)/0.9597))
520
509
521 
        sheet.write(i, 27, round(proposed_tp-(supc_data.ourSp*0.9597-courierCost)))
510 
        sheet.write(i, 25, round(proposed_tp-(supc_data.ourSp*0.9597-courierCost)))
522 
        i+=1
 511 
        i+=1
523 
 
 512 
 
524 
 
 513 
 
525 
def createSheetForCometitiveItems(competitiveItems,wbk):
 514 
def createSheetForCometitiveItems(competitiveItems,wbk):
526 
    sheet = wbk.add_sheet('Can Compete-With Inventory')
 515 
    sheet = wbk.add_sheet('Can Compete-With Inventory')
Line 528... Line 517...
528 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
 517 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
529
518
530 
    excel_integer_format = '0'
 519 
    excel_integer_format = '0'
531 
    integer_style = xlwt.XFStyle()
 520 
    integer_style = xlwt.XFStyle()
532 
    integer_style.num_format_str = excel_integer_format
 521 
    integer_style.num_format_str = excel_integer_format
- 
 
 522 
    xstr = lambda s: s or ""
533
523
534 
    sheet.write(0, 0, "Item ID", heading_xf)
 524 
    sheet.write(0, 0, "Item ID", heading_xf)
535 
    sheet.write(0, 1, "Category", heading_xf)
 525 
    sheet.write(0, 1, "Category", heading_xf)
536 
    sheet.write(0, 2, "Product Group.", heading_xf)
 526 
    sheet.write(0, 2, "Product Group.", heading_xf)
537 
    sheet.write(0, 3, "SUPC", heading_xf)
 527 
    sheet.write(0, 3, "SUPC", heading_xf)
538 
    sheet.write(0, 4, "Brand", heading_xf)
 528 
    sheet.write(0, 4, "Brand", heading_xf)
539 
    sheet.write(0, 5, "Model Name", heading_xf)
 529 
    sheet.write(0, 5, "Product Name", heading_xf)
540 
    sheet.write(0, 6, "Model Number", heading_xf)
 - 
 
541 
    sheet.write(0, 7, "Color", heading_xf)
 - 
 
542 
    sheet.write(0, 8, "Weight", heading_xf)
 530 
    sheet.write(0, 6, "Weight", heading_xf)
543 
    sheet.write(0, 9, "Courier Cost", heading_xf)
 531 
    sheet.write(0, 7, "Courier Cost", heading_xf)
544 
    sheet.write(0, 10, "Risky", heading_xf)
 532 
    sheet.write(0, 8, "Risky", heading_xf)
545 
    sheet.write(0, 11, "Our SP", heading_xf)
 533 
    sheet.write(0, 9, "Our SP", heading_xf)
546 
    sheet.write(0, 13, "Our TP", heading_xf)
 534 
    sheet.write(0, 11, "Our TP", heading_xf)
547 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
 535 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
548 
    sheet.write(0, 14, "Our Rank", heading_xf)
 536 
    sheet.write(0, 12, "Our Rank", heading_xf)
549 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
 537 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
550 
    sheet.write(0, 16, "Lowest SP", heading_xf)
 538 
    sheet.write(0, 14, "Lowest SP", heading_xf)
551 
    sheet.write(0, 17, "Lowest TP", heading_xf)
 539 
    sheet.write(0, 15, "Lowest TP", heading_xf)
552 
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)
 540 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
553 
    sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
 541 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
554 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
 542 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
555 
    sheet.write(0, 21, "Our NLC", heading_xf)
 543 
    sheet.write(0, 19, "Our NLC", heading_xf)
556 
    sheet.write(0, 22, "Our Dealer Price", heading_xf)
 544 
    sheet.write(0, 20, "Our Dealer Price", heading_xf)
557 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
 545 
    sheet.write(0, 21, "Lowest Possible TP", heading_xf)
558 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
 546 
    sheet.write(0, 22, "Lowest Possible SP", heading_xf)
559 
    sheet.write(0, 25, "Competition Basis ", heading_xf)
 547 
    sheet.write(0, 23, "Competition Basis ", heading_xf)
560 
    sheet.write(0, 26, "Can Compete", heading_xf)
 548 
    sheet.write(0, 24, "Can Compete", heading_xf)
561 
    sheet.write(0, 27, "Target TP", heading_xf)
 549 
    sheet.write(0, 25, "Target TP", heading_xf)
562 
    sheet.write(0, 28, "Target SP", heading_xf)
550 
    sheet.write(0, 26, "Target SP", heading_xf)
563 
    sheet.write(0, 29, "Sales Potential", heading_xf)
 551 
    sheet.write(0, 27, "Sales Potential", heading_xf)
564
552
565 
    i, sheet_iterator=1,1
 553 
    i, sheet_iterator=1,1
566 
    for data in competitiveItems:
 554 
    for data in competitiveItems:
567 
        supc_data =data[0]
 555 
        supc_data =data[0]
568 
        one_line = data[1]
 556 
        one_line = data[1]
Line 580... Line 568...
580 
        sheet.write(sheet_iterator, 0, one_line.item_id)
 568 
        sheet.write(sheet_iterator, 0, one_line.item_id)
581 
        sheet.write(sheet_iterator, 1, one_line.category_name)
 569 
        sheet.write(sheet_iterator, 1, one_line.category_name)
582 
        sheet.write(sheet_iterator, 2, one_line.product_group)
 570 
        sheet.write(sheet_iterator, 2, one_line.product_group)
583 
        sheet.write(sheet_iterator, 3, supc_data.supc)
 571 
        sheet.write(sheet_iterator, 3, supc_data.supc)
584 
        sheet.write(sheet_iterator, 4, one_line.brand)
 572 
        sheet.write(sheet_iterator, 4, one_line.brand)
585 
        sheet.write(sheet_iterator, 5, one_line.model_name)
 - 
 
586 
        sheet.write(sheet_iterator, 6, one_line.model_number)
 573 
        sheet.write(sheet_iterator, 5, xstr(one_line.brand)+" "+xstr(one_line.model_name)+" "+xstr(one_line.model_number)+" "+xstr(one_line.color))
587 
        sheet.write(sheet_iterator, 7, one_line.color)
 - 
 
588 
        sheet.write(sheet_iterator, 8, one_line.weight)
 574 
        sheet.write(sheet_iterator, 6, one_line.weight)
589 
        sheet.write(sheet_iterator, 9, round(courierCost))
 575 
        sheet.write(sheet_iterator, 7, round(courierCost))
590 
        sheet.write(sheet_iterator, 10, one_line.risky)
 576 
        sheet.write(sheet_iterator, 8, one_line.risky)
591 
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
 577 
        sheet.write(sheet_iterator, 9, supc_data.ourSp)
592 
        sheet.write(sheet_iterator, 13, round(supc_data.ourSp*0.9597-courierCost))
 578 
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
593 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
 579 
        sheet.write(sheet_iterator, 10, supc_data.ourOfferPrice)
594 
        sheet.write(sheet_iterator, 14, supc_data.rank)
 580 
        sheet.write(sheet_iterator, 12, supc_data.rank)
595 
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
 581 
        sheet.write(sheet_iterator, 13, supc_data.lowestSellerName)
596 
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
 582 
        sheet.write(sheet_iterator, 14, supc_data.lowestSp)
597 
        if one_line.parent_category ==10011:
 583 
        if one_line.parent_category ==10011:
598 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
 584 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
599 
        else:
 585 
        else:
600 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
 586 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
601 
        sheet.write(sheet_iterator, 17, round(lowestTp))
 587 
        sheet.write(sheet_iterator, 15, round(lowestTp))
602 
        sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
 588 
        sheet.write(sheet_iterator, 16, supc_data.lowestOfferPrice)
603 
        sheet.write(sheet_iterator, 19, supc_data.otherInventory)
 589 
        sheet.write(sheet_iterator, 17, supc_data.otherInventory)
604 
        sheet.write(sheet_iterator, 20, supc_data.ourInventory)
 590 
        sheet.write(sheet_iterator, 18, supc_data.ourInventory)
605 
        sheet.write(sheet_iterator, 21, one_line.our_nlc)
 591 
        sheet.write(sheet_iterator, 19, one_line.our_nlc)
606 
        sheet.write(sheet_iterator, 22, one_line.dealerPrice)
 592 
        sheet.write(sheet_iterator, 20, one_line.dealerPrice)
607 
        if supc_data.rank==1:
 593 
        if supc_data.rank==1:
608 
            i+=1
 594 
            i+=1
609 
            sheet_iterator+=1
 595 
            sheet_iterator+=1
610 
            continue
 596 
            continue
611 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
 597 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
612 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
 598 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
613 
        sheet.write(sheet_iterator, 23, round(lowest_possible_tp))
 599 
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
614 
        sheet.write(sheet_iterator, 24, round(lowest_possible_sp))
 600 
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
615 
        competitionBasis ='TP'
 601 
        competitionBasis ='TP'
616 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice==supc_data.ourSp:
 602 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice==supc_data.ourSp:
617 
            competitionBasis ='SP'
 603 
            competitionBasis ='SP'
618 
            sheet.write(sheet_iterator, 25, 'SP')
 604 
            sheet.write(sheet_iterator, 23, 'SP')
619 
        else:
 605 
        else:
620 
            sheet.write(sheet_iterator, 25, 'TP')
 606 
            sheet.write(sheet_iterator, 23, 'TP')
621 
        proposed_tp = 0
 607 
        proposed_tp = 0
622 
 
 608 
 
623 
        if competitionBasis=='SP':
 609 
        if competitionBasis=='SP':
624 
            if supc_data.lowestSp > lowest_possible_sp:
 610 
            if supc_data.lowestSp > lowest_possible_sp:
625 
                sheet.write(sheet_iterator, 26, "Yes")
 611 
                sheet.write(sheet_iterator, 24, "Yes")
626 
            else:
 612 
            else:
627 
                sheet.write(sheet_iterator, 26, "No")
 613 
                sheet.write(sheet_iterator, 24, "No")
628 
            proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
 614 
            proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
629 
            proposed_tp = proposed_sp*.9597-courierCost
 615 
            proposed_tp = proposed_sp*.9597-courierCost
630 
            sheet.write(sheet_iterator, 27, round(proposed_tp))
 616 
            sheet.write(sheet_iterator, 25, round(proposed_tp))
631 
            sheet.write(sheet_iterator, 28, round(proposed_sp))
 617 
            sheet.write(sheet_iterator, 26, round(proposed_sp))
632 
        else:
 618 
        else:
633 
            if lowestTp > lowest_possible_tp:
 619 
            if lowestTp > lowest_possible_tp:
634 
                sheet.write(sheet_iterator, 26, "Yes")
 620 
                sheet.write(sheet_iterator, 24, "Yes")
635 
            else:
 621 
            else:
636 
                sheet.write(sheet_iterator, 26, "No")
 622 
                sheet.write(sheet_iterator, 24, "No")
637 
            proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
 623 
            proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
638 
            sheet.write(sheet_iterator, 27, round(proposed_tp))
 624 
            sheet.write(sheet_iterator, 25, round(proposed_tp))
639 
            sheet.write(sheet_iterator, 28, round((proposed_tp+courierCost)/0.9597))
 625 
            sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
640
626
641 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
 627 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
642 
            sheet.write(sheet_iterator, 29, "HIGH")
 628 
            sheet.write(sheet_iterator, 27, "HIGH")
643 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
 629 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
644 
            sheet.write(sheet_iterator, 29, "MEDIUM")
 630 
            sheet.write(sheet_iterator, 27, "MEDIUM")
645 
        else:
 631 
        else:
646 
            sheet.write(sheet_iterator, 29, "LOW")
 632 
            sheet.write(sheet_iterator, 27, "LOW")
647 
        i= i+1
 633 
        i= i+1
648 
        sheet_iterator+=1
 634 
        sheet_iterator+=1
649 
 
 635 
 
650 
 
 636 
 
651 
def createSheetForCometitiveItemsNoInventory(competitiveNoInventoryItems,wbk):
 637 
def createSheetForCometitiveItemsNoInventory(competitiveNoInventoryItems,wbk):
Line 654... Line 640...
654 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
 640 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
655
641
656 
    excel_integer_format = '0'
 642 
    excel_integer_format = '0'
657 
    integer_style = xlwt.XFStyle()
 643 
    integer_style = xlwt.XFStyle()
658 
    integer_style.num_format_str = excel_integer_format
 644 
    integer_style.num_format_str = excel_integer_format
- 
 
 645 
    xstr = lambda s: s or ""
659
646
660 
    sheet.write(0, 0, "Item ID", heading_xf)
 647 
    sheet.write(0, 0, "Item ID", heading_xf)
661 
    sheet.write(0, 1, "Category", heading_xf)
 648 
    sheet.write(0, 1, "Category", heading_xf)
662 
    sheet.write(0, 2, "Product Group.", heading_xf)
 649 
    sheet.write(0, 2, "Product Group.", heading_xf)
663 
    sheet.write(0, 3, "SUPC", heading_xf)
 650 
    sheet.write(0, 3, "SUPC", heading_xf)
664 
    sheet.write(0, 4, "Brand", heading_xf)
 651 
    sheet.write(0, 4, "Brand", heading_xf)
665 
    sheet.write(0, 5, "Model Name", heading_xf)
 652 
    sheet.write(0, 5, "Product Name", heading_xf)
666 
    sheet.write(0, 6, "Model Number", heading_xf)
 - 
 
667 
    sheet.write(0, 7, "Color", heading_xf)
 - 
 
668 
    sheet.write(0, 8, "Weight", heading_xf)
 653 
    sheet.write(0, 6, "Weight", heading_xf)
669 
    sheet.write(0, 9, "Courier Cost", heading_xf)
 654 
    sheet.write(0, 7, "Courier Cost", heading_xf)
670 
    sheet.write(0, 10, "Risky", heading_xf)
 655 
    sheet.write(0, 8, "Risky", heading_xf)
671 
    sheet.write(0, 11, "Our SP", heading_xf)
 656 
    sheet.write(0, 9, "Our SP", heading_xf)
672 
    sheet.write(0, 13, "Our TP", heading_xf)
 657 
    sheet.write(0, 11, "Our TP", heading_xf)
673 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
 658 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
674 
    sheet.write(0, 14, "Our Rank", heading_xf)
 659 
    sheet.write(0, 12, "Our Rank", heading_xf)
675 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
 660 
    sheet.write(0, 13, "Lowest Seller", heading_xf)
676 
    sheet.write(0, 16, "Lowest SP", heading_xf)
 661 
    sheet.write(0, 14, "Lowest SP", heading_xf)
677 
    sheet.write(0, 17, "Lowest TP", heading_xf)
 662 
    sheet.write(0, 15, "Lowest TP", heading_xf)
678 
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)
 663 
    sheet.write(0, 16, "Lowest Offer Price", heading_xf)
679 
    sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
 664 
    sheet.write(0, 17, "Inventory of Top Vendors", heading_xf)
680 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
 665 
    sheet.write(0, 18, "Our Snapdeal Inventory", heading_xf)
681 
    sheet.write(0, 21, "Our NLC", heading_xf)
 666 
    sheet.write(0, 19, "Our NLC", heading_xf)
682 
    sheet.write(0, 22, "Our Dealer Price", heading_xf)
 667 
    sheet.write(0, 20, "Our Dealer Price", heading_xf)
683 
    sheet.write(0, 23, "Lowest Possible TP", heading_xf)
 668 
    sheet.write(0, 21, "Lowest Possible TP", heading_xf)
684 
    sheet.write(0, 24, "Lowest Possible SP", heading_xf)
 669 
    sheet.write(0, 22, "Lowest Possible SP", heading_xf)
685 
    sheet.write(0, 25, "Competition Basis ", heading_xf)
 670 
    sheet.write(0, 23, "Competition Basis ", heading_xf)
686 
    sheet.write(0, 26, "Can Compete", heading_xf)
 671 
    sheet.write(0, 24, "Can Compete", heading_xf)
687 
    sheet.write(0, 27, "Target TP", heading_xf)
 672 
    sheet.write(0, 25, "Target TP", heading_xf)
688 
    sheet.write(0, 28, "Target SP", heading_xf)
673 
    sheet.write(0, 26, "Target SP", heading_xf)
689 
    sheet.write(0, 29, "Sales Potential", heading_xf)
 674 
    sheet.write(0, 27, "Sales Potential", heading_xf)
690
675
691 
    i, sheet_iterator=1,1
 676 
    i, sheet_iterator=1,1
692 
    for data in competitiveNoInventoryItems:
 677 
    for data in competitiveNoInventoryItems:
693 
        supc_data =data[0]
 678 
        supc_data =data[0]
694 
        one_line = data[1]
 679 
        one_line = data[1]
Line 706... Line 691...
706 
        sheet.write(sheet_iterator, 0, one_line.item_id)
 691 
        sheet.write(sheet_iterator, 0, one_line.item_id)
707 
        sheet.write(sheet_iterator, 1, one_line.category_name)
 692 
        sheet.write(sheet_iterator, 1, one_line.category_name)
708 
        sheet.write(sheet_iterator, 2, one_line.product_group)
 693 
        sheet.write(sheet_iterator, 2, one_line.product_group)
709 
        sheet.write(sheet_iterator, 3, supc_data.supc)
 694 
        sheet.write(sheet_iterator, 3, supc_data.supc)
710 
        sheet.write(sheet_iterator, 4, one_line.brand)
 695 
        sheet.write(sheet_iterator, 4, one_line.brand)
711 
        sheet.write(sheet_iterator, 5, one_line.model_name)
 - 
 
712 
        sheet.write(sheet_iterator, 6, one_line.model_number)
 696 
        sheet.write(sheet_iterator, 5, xstr(one_line.brand)+" "+xstr(one_line.model_name)+" "+xstr(one_line.model_number)+" "+xstr(one_line.color))
713 
        sheet.write(sheet_iterator, 7, one_line.color)
 - 
 
714 
        sheet.write(sheet_iterator, 8, one_line.weight)
 697 
        sheet.write(sheet_iterator, 6, one_line.weight)
715 
        sheet.write(sheet_iterator, 9, round(courierCost))
 698 
        sheet.write(sheet_iterator, 7, round(courierCost))
716 
        sheet.write(sheet_iterator, 10, one_line.risky)
 699 
        sheet.write(sheet_iterator, 8, one_line.risky)
717 
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
 700 
        sheet.write(sheet_iterator, 9, supc_data.ourSp)
718 
        sheet.write(sheet_iterator, 13, '-')
 701 
        sheet.write(sheet_iterator, 11, '-')
719 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
 702 
        sheet.write(sheet_iterator, 10, supc_data.ourOfferPrice)
720 
        sheet.write(sheet_iterator, 14, supc_data.rank)
 703 
        sheet.write(sheet_iterator, 12, supc_data.rank)
721 
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
 704 
        sheet.write(sheet_iterator, 13, supc_data.lowestSellerName)
722 
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
 705 
        sheet.write(sheet_iterator, 14, supc_data.lowestSp)
723 
        if one_line.parent_category ==10011:
 706 
        if one_line.parent_category ==10011:
724 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
 707 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
725 
        else:
 708 
        else:
726 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
 709 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
727 
        sheet.write(sheet_iterator, 17, round(lowestTp))
 710 
        sheet.write(sheet_iterator, 15, round(lowestTp))
728 
        sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
 711 
        sheet.write(sheet_iterator, 16, supc_data.lowestOfferPrice)
729 
        sheet.write(sheet_iterator, 19, supc_data.otherInventory)
 712 
        sheet.write(sheet_iterator, 17, supc_data.otherInventory)
730 
        sheet.write(sheet_iterator, 20, supc_data.ourInventory)
 713 
        sheet.write(sheet_iterator, 18, supc_data.ourInventory)
731 
        sheet.write(sheet_iterator, 21, one_line.our_nlc)
 714 
        sheet.write(sheet_iterator, 19, one_line.our_nlc)
732 
        sheet.write(sheet_iterator, 22, one_line.dealerPrice)
 715 
        sheet.write(sheet_iterator, 20, one_line.dealerPrice)
733 
        if supc_data.rank==1:
 716 
        if supc_data.rank==1:
734 
            i+=1
 717 
            i+=1
735 
            sheet_iterator+=1
 718 
            sheet_iterator+=1
736 
            continue
 719 
            continue
737 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
 720 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
738 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
 721 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
739 
        sheet.write(sheet_iterator, 23, round(lowest_possible_tp))
 722 
        sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
740 
        sheet.write(sheet_iterator, 24, round(lowest_possible_sp))
 723 
        sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
741 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
 724 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
742 
            competitionBasis ='SP'
 725 
            competitionBasis ='SP'
743 
            sheet.write(sheet_iterator, 25, 'SP')
 726 
            sheet.write(sheet_iterator, 23, 'SP')
744 
        else:
 727 
        else:
745 
            sheet.write(sheet_iterator, 25, 'TP')
 728 
            sheet.write(sheet_iterator, 23, 'TP')
746 
        proposed_tp = 0
 729 
        proposed_tp = 0
747 
        if competitionBasis=='SP':
 730 
        if competitionBasis=='SP':
748 
            if supc_data.lowestSp > lowest_possible_sp:
 731 
            if supc_data.lowestSp > lowest_possible_sp:
749 
                sheet.write(sheet_iterator, 26, "Yes")
 732 
                sheet.write(sheet_iterator, 24, "Yes")
750 
            else:
 733 
            else:
751 
                sheet.write(sheet_iterator, 26, "No")
 734 
                sheet.write(sheet_iterator, 24, "No")
752 
            proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
 735 
            proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
753 
            proposed_tp = proposed_sp*.9597-courierCost
 736 
            proposed_tp = proposed_sp*.9597-courierCost
754 
            sheet.write(sheet_iterator, 27, round(proposed_tp))
 737 
            sheet.write(sheet_iterator, 25, round(proposed_tp))
755 
            sheet.write(sheet_iterator, 28, round(proposed_sp))
 738 
            sheet.write(sheet_iterator, 26, round(proposed_sp))
756 
        else:
739 
        else:
757 
            if lowestTp > lowest_possible_tp:
 740 
            if lowestTp > lowest_possible_tp:
758 
                sheet.write(sheet_iterator, 26, "Yes")
 741 
                sheet.write(sheet_iterator, 24, "Yes")
759 
                if competitionBasis=='SP':
 742 
                if competitionBasis=='SP':
760 
                    proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
 743 
                    proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
761 
                    proposed_tp = proposed_sp*.9597-courierCost
 744 
                    proposed_tp = proposed_sp*.9597-courierCost
762 
                    sheet.write(sheet_iterator, 27, round(proposed_tp))
 745 
                    sheet.write(sheet_iterator, 25, round(proposed_tp))
763 
                    sheet.write(sheet_iterator, 28, round(proposed_sp))
 746 
                    sheet.write(sheet_iterator, 26, round(proposed_sp))
764 
                else:
 747 
                else:
765 
                    proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
 748 
                    proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
766 
                    sheet.write(sheet_iterator, 27, round(proposed_tp))
 749 
                    sheet.write(sheet_iterator, 25, round(proposed_tp))
767 
                    sheet.write(sheet_iterator, 28, round((proposed_tp+courierCost)/0.9597))
 750 
                    sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
768
751
769 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
 752 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
770 
            sheet.write(sheet_iterator, 29, "HIGH")
 753 
            sheet.write(sheet_iterator, 27, "HIGH")
771 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
 754 
        elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
772 
            sheet.write(sheet_iterator, 29, "MEDIUM")
 755 
            sheet.write(sheet_iterator, 27, "MEDIUM")
773 
        else:
 756 
        else:
774 
            sheet.write(sheet_iterator, 29, "LOW")
 757 
            sheet.write(sheet_iterator, 27, "LOW")
775 
        i= i+1
 758 
        i= i+1
776 
        sheet_iterator+=1
 759 
        sheet_iterator+=1
777
760
778 
def createSheetForNegativeMargin(negativeMargin,wbk):
 761 
def createSheetForNegativeMargin(negativeMargin,wbk):
779 
    sheet = wbk.add_sheet('Negative Margin')
 762 
    sheet = wbk.add_sheet('Negative Margin')
Line 781... Line 764...
781 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
 764 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
782
765
783 
    excel_integer_format = '0'
 766 
    excel_integer_format = '0'
784 
    integer_style = xlwt.XFStyle()
 767 
    integer_style = xlwt.XFStyle()
785 
    integer_style.num_format_str = excel_integer_format
 768 
    integer_style.num_format_str = excel_integer_format
- 
 
 769 
    xstr = lambda s: s or ""
786
770
787 
    sheet.write(0, 0, "Item ID", heading_xf)
 771 
    sheet.write(0, 0, "Item ID", heading_xf)
788 
    sheet.write(0, 1, "Category", heading_xf)
 772 
    sheet.write(0, 1, "Category", heading_xf)
789 
    sheet.write(0, 2, "Product Group.", heading_xf)
 773 
    sheet.write(0, 2, "Product Group.", heading_xf)
790 
    sheet.write(0, 3, "SUPC", heading_xf)
 774 
    sheet.write(0, 3, "SUPC", heading_xf)
791 
    sheet.write(0, 4, "Brand", heading_xf)
 775 
    sheet.write(0, 4, "Brand", heading_xf)
792 
    sheet.write(0, 5, "Model Name", heading_xf)
 776 
    sheet.write(0, 5, "Product Name", heading_xf)
793 
    sheet.write(0, 6, "Model Number", heading_xf)
 - 
 
794 
    sheet.write(0, 7, "Color", heading_xf)
 - 
 
795 
    sheet.write(0, 8, "Weight", heading_xf)
 777 
    sheet.write(0, 6, "Weight", heading_xf)
796 
    sheet.write(0, 9, "Courier Cost", heading_xf)
 778 
    sheet.write(0, 7, "Courier Cost", heading_xf)
797 
    sheet.write(0, 10, "Risky", heading_xf)
 779 
    sheet.write(0, 8, "Risky", heading_xf)
798 
    sheet.write(0, 11, "Our SP", heading_xf)
 780 
    sheet.write(0, 9, "Our SP", heading_xf)
799 
    sheet.write(0, 13, "Our TP", heading_xf)
 781 
    sheet.write(0, 11, "Our TP", heading_xf)
800 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
 782 
    sheet.write(0, 10, "Our Offer Price", heading_xf)
801 
    sheet.write(0, 14, "Our Rank", heading_xf)
 783 
    sheet.write(0, 12, "Our Rank", heading_xf)
802 
    sheet.write(0, 15, "Our NLC", heading_xf)
 784 
    sheet.write(0, 13, "Our NLC", heading_xf)
803 
    sheet.write(0, 16, "Our Dealer Price", heading_xf)
 785 
    sheet.write(0, 14, "Our Dealer Price", heading_xf)
804 
    sheet.write(0, 17, "Margin", heading_xf)
 786 
    sheet.write(0, 15, "Margin", heading_xf)
805
787
806 
    i, sheet_iterator=1,1
 788 
    i, sheet_iterator=1,1
807 
    for data in negativeMargin:
 789 
    for data in negativeMargin:
808 
        supc_data =data[0]
 790 
        supc_data =data[0]
809 
        one_line = data[1]
 791 
        one_line = data[1]
Line 828... Line 810...
828 
        sheet.write(sheet_iterator, 0, one_line.item_id)
 810 
        sheet.write(sheet_iterator, 0, one_line.item_id)
829 
        sheet.write(sheet_iterator, 1, one_line.category_name)
 811 
        sheet.write(sheet_iterator, 1, one_line.category_name)
830 
        sheet.write(sheet_iterator, 2, one_line.product_group)
 812 
        sheet.write(sheet_iterator, 2, one_line.product_group)
831 
        sheet.write(sheet_iterator, 3, supc_data.supc)
 813 
        sheet.write(sheet_iterator, 3, supc_data.supc)
832 
        sheet.write(sheet_iterator, 4, one_line.brand)
 814 
        sheet.write(sheet_iterator, 4, one_line.brand)
833 
        sheet.write(sheet_iterator, 5, one_line.model_name)
 - 
 
834 
        sheet.write(sheet_iterator, 6, one_line.model_number)
 815 
        sheet.write(sheet_iterator, 5, xstr(one_line.brand)+" "+xstr(one_line.model_name)+" "+xstr(one_line.model_number)+" "+xstr(one_line.color))
835 
        sheet.write(sheet_iterator, 7, one_line.color)
 - 
 
836 
        sheet.write(sheet_iterator, 8, one_line.weight)
 816 
        sheet.write(sheet_iterator, 6, one_line.weight)
837 
        sheet.write(sheet_iterator, 9, round(courierCost))
 817 
        sheet.write(sheet_iterator, 7, round(courierCost))
838 
        sheet.write(sheet_iterator, 10, one_line.risky)
 818 
        sheet.write(sheet_iterator, 8, one_line.risky)
839 
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
 819 
        sheet.write(sheet_iterator, 9, supc_data.ourSp)
840 
        sheet.write(sheet_iterator, 13, round(supc_data.ourSp*0.9597-courierCost))
 820 
        sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
841 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
 821 
        sheet.write(sheet_iterator, 10, supc_data.ourOfferPrice)
842 
        sheet.write(sheet_iterator, 14, supc_data.rank)
 822 
        sheet.write(sheet_iterator, 12, supc_data.rank)
843 
        sheet.write(sheet_iterator, 15, one_line.our_nlc)
 823 
        sheet.write(sheet_iterator, 13, one_line.our_nlc)
844 
        sheet.write(sheet_iterator, 16, one_line.dealerPrice)
 824 
        sheet.write(sheet_iterator, 14, one_line.dealerPrice)
845 
        sheet.write(sheet_iterator, 17, round((supc_data.ourSp*0.9597-courierCost) - lowest_possible_tp))
 825 
        sheet.write(sheet_iterator, 15, round((supc_data.ourSp*0.9597-courierCost) - lowest_possible_tp))
846 
        i= i+1
 826 
        i= i+1
847 
        sheet_iterator+=1
 827 
        sheet_iterator+=1
848
828
849 
 
 829 
 
850 
def main():
 830 
def main():