WebSVN – SmartDukaan – Diff – /trunk/PyProj/src/shop2020/model/v1/catalog/script/Snapdeal.py



        print sheet.row_values(rownum)
        item_id = int(sheet.row_values(rownum)[0])
        supc = sheet.row_values(rownum)[1]
        item = Item.query.filter_by(id=item_id).one()
        category = Category.query.filter_by(id=item.category).one()
        parent_category = Category.query.filter_by(parent_category_id=category.parent_category_id).one()
        snapdeal_item = SnapdealItem.query.filter_by(item_id=item_id).one()
        warehouse = iclient.getWarehouse(snapdeal_item.warehouseId)
        item_pricing = iclient.getItemPricing(item_id, warehouse.vendor.id)
        #TO BE USED LATER
        #inventory_snapshot = iclient.getInventorySnapshot(0)
        #warehouses_ours = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("OURS"), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)
        #warehouses_third_party = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("THIRD_PARTY "), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)
        one_line = _SnapdealItemInfo(item.id, item.product_group, parent_category.displayName, item_pricing.nlc, item.brand, item.model_name, item.model_number, item.color, item.weight, category.parent_category_id, item.risky)
        all_supc.append(supc)
        all_lines.append(one_line)
    write_report("/tmp/snapdeal_running.xls", all_lines, all_supc)
    
    

    sheet.write(0, 7, "Color", heading_xf)
    sheet.write(0, 8, "Weight", heading_xf)
    sheet.write(0, 9, "Courier Cost", heading_xf)
    sheet.write(0, 10, "Risky", heading_xf)
    sheet.write(0, 11, "Our SP", heading_xf)
    sheet.write(0, 13, "Our TP", heading_xf)
    sheet.write(0, 12, "Our Offer Price", heading_xf)
    sheet.write(0, 14, "Our Rank", heading_xf)
    sheet.write(0, 15, "Lowest Seller", heading_xf)
    sheet.write(0, 16, "Lowest SP", heading_xf)
    sheet.write(0, 17, "Lowest TP", heading_xf)
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)

        sheet.write(sheet_iterator, 7, one_line.color)
        sheet.write(sheet_iterator, 8, one_line.weight)
        sheet.write(sheet_iterator, 9, round(courierCost))
        sheet.write(sheet_iterator, 10, one_line.risky)
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
        sheet.write(sheet_iterator, 13, round(supc_data.ourSp*0.9597-courierCost))
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
        sheet.write(sheet_iterator, 14, supc_data.rank)
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
        if one_line.parent_category ==10011:
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost

        lowest_possible_tp = one_line.our_nlc/0.988+15+6
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
        sheet.write(sheet_iterator, 22, round(lowest_possible_tp))
        sheet.write(sheet_iterator, 23, round(lowest_possible_sp))
        competitionBasis ='TP'
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice == supc_data.ourSp:
            competitionBasis ='SP'
            sheet.write(sheet_iterator, 24, 'SP')
        else:
            sheet.write(sheet_iterator, 24, 'TP')
        proposed_tp = 0
        if lowestTp > lowest_possible_tp:
            sheet.write(sheet_iterator, 25, "Yes")
        else:
            sheet.write(sheet_iterator, 25, "No")
        if competitionBasis=='SP':
            proposed_sp = supc_data.lowestSp - max(10, supc_data.lowestSp*0.001)
            proposed_tp = proposed_sp*0.9597-courierCost
            sheet.write(sheet_iterator, 26, round(proposed_tp))
            sheet.write(sheet_iterator, 27, round(proposed_sp))
            sheet.write(sheet_iterator, 28, round(proposed_tp*0.988-21)) 
        else:    
            proposed_tp  = lowestTp - max(10, lowestTp*0.001)
            sheet.write(sheet_iterator, 26, round(proposed_tp))
            sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))
            sheet.write(sheet_iterator, 28, round(proposed_tp*0.988-21)) 
        
        

    sheet.write(0, 7, "Color", heading_xf)
    sheet.write(0, 8, "Weight", heading_xf)
    sheet.write(0, 9, "Courier Cost", heading_xf)
    sheet.write(0, 10, "Risky", heading_xf)
    sheet.write(0, 11, "Our SP", heading_xf)
    sheet.write(0, 13, "Our TP", heading_xf)
    sheet.write(0, 12, "Our Offer Price", heading_xf)
    sheet.write(0, 14, "Our Rank", heading_xf)
    sheet.write(0, 15, "Lowest Seller", heading_xf)
    sheet.write(0, 16, "Second Lowest Seller", heading_xf)
    sheet.write(0, 17, "Second Lowest Price", heading_xf)
    sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)

    sheet.write(0, 21, "Second Lowest Seller Inventory", heading_xf)
    sheet.write(0, 22, "Our NLC", heading_xf)
    sheet.write(0, 23, "Competition Basis", heading_xf)
    sheet.write(0, 24, "Target TP", heading_xf)
    sheet.write(0, 25, "Target SP", heading_xf)
 
    sheet.write(0, 26, "MARGIN INCREASED POTENTIAL", heading_xf)
    
    i=1
    
    for data in buyBoxItems:
        supc_data =data[0]

        sheet.write(i, 7, one_line.color)
        sheet.write(i, 8, one_line.weight)
        sheet.write(i, 9, round(courierCost))
        sheet.write(i, 10, one_line.risky)
        sheet.write(i, 11, supc_data.ourSp)
        sheet.write(i, 13, round(supc_data.ourSp*0.9597-courierCost))
        sheet.write(i, 12, supc_data.ourOfferPrice)
        sheet.write(i, 14, supc_data.rank)
        sheet.write(i, 15, supc_data.lowestSellerName)
        sheet.write(i, 16, supc_data.secondLowestSellerName)
        sheet.write(i, 17, supc_data.secondLowestSellerSp)
        sheet.write(i, 18, supc_data.secondLowestSellerOfferPrice)

        sheet.write(i, 20, supc_data.ourInventory)
        sheet.write(i, 21, supc_data.secondLowestSellerInventory)
        sheet.write(i, 22, one_line.our_nlc)
        
        competitionBasis ='TP'
        if (supc_data.secondLowestSellerOfferPrice == supc_data.secondLowestSellerSp) and supc_data.ourOfferPrice==supc_data.ourSp:
            competitionBasis ='SP'
            sheet.write(i, 23, 'SP')
        else:
            sheet.write(i, 23, 'TP')
        
        if competitionBasis=='SP':
            proposed_sp = max(supc_data.secondLowestSellerSp - max((20, supc_data.secondLowestSellerSp*0.002)), lowest_possible_sp)
            proposed_tp = proposed_sp*0.9597-courierCost
            sheet.write(i, 24, round(proposed_tp))
            sheet.write(i, 25, round(proposed_sp))
 
        else:    
            proposed_tp  = max(secondlowestTp - max((20, secondlowestTp*0.002)), lowest_possible_tp)
            sheet.write(i, 24, round(proposed_tp))
            sheet.write(i, 25, round((proposed_tp+courierCost)/0.9597))
 
        
        sheet.write(i, 26, round(proposed_tp-(supc_data.ourSp*0.9597-courierCost)))     
        i+=1
 
 
def createSheetForCometitiveItems(competitiveItems,wbk):
    sheet = wbk.add_sheet('Can Compete-With Inventory')

    sheet.write(0, 7, "Color", heading_xf)
    sheet.write(0, 8, "Weight", heading_xf)
    sheet.write(0, 9, "Courier Cost", heading_xf)
    sheet.write(0, 10, "Risky", heading_xf)
    sheet.write(0, 11, "Our SP", heading_xf)
    sheet.write(0, 13, "Our TP", heading_xf)
    sheet.write(0, 12, "Our Offer Price", heading_xf)
    sheet.write(0, 14, "Our Rank", heading_xf)
    sheet.write(0, 15, "Lowest Seller", heading_xf)
    sheet.write(0, 16, "Lowest SP", heading_xf)
    sheet.write(0, 17, "Lowest TP", heading_xf)
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)

        sheet.write(sheet_iterator, 7, one_line.color)
        sheet.write(sheet_iterator, 8, one_line.weight)
        sheet.write(sheet_iterator, 9, round(courierCost))
        sheet.write(sheet_iterator, 10, one_line.risky)
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
        sheet.write(sheet_iterator, 13, round(supc_data.ourSp*0.9597-courierCost))
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
        sheet.write(sheet_iterator, 14, supc_data.rank)
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
        if one_line.parent_category ==10011:
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost

        lowest_possible_tp = one_line.our_nlc/0.988+15+6
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
        sheet.write(sheet_iterator, 22, round(lowest_possible_tp))
        sheet.write(sheet_iterator, 23, round(lowest_possible_sp))
        competitionBasis ='TP'
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice==supc_data.ourSp:
            competitionBasis ='SP'
            sheet.write(sheet_iterator, 24, 'SP')
        else:
            sheet.write(sheet_iterator, 24, 'TP')
        proposed_tp = 0

    sheet.write(0, 7, "Color", heading_xf)
    sheet.write(0, 8, "Weight", heading_xf)
    sheet.write(0, 9, "Courier Cost", heading_xf)
    sheet.write(0, 10, "Risky", heading_xf)
    sheet.write(0, 11, "Our SP", heading_xf)
    sheet.write(0, 13, "Our TP", heading_xf)
    sheet.write(0, 12, "Our Offer Price", heading_xf)
    sheet.write(0, 14, "Our Rank", heading_xf)
    sheet.write(0, 15, "Lowest Seller", heading_xf)
    sheet.write(0, 16, "Lowest SP", heading_xf)
    sheet.write(0, 17, "Lowest TP", heading_xf)
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)

        sheet.write(sheet_iterator, 7, one_line.color)
        sheet.write(sheet_iterator, 8, one_line.weight)
        sheet.write(sheet_iterator, 9, round(courierCost))
        sheet.write(sheet_iterator, 10, one_line.risky)
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
        sheet.write(sheet_iterator, 13, '-')
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
        sheet.write(sheet_iterator, 14, supc_data.rank)
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
        if one_line.parent_category ==10011:
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost

Subversion Repositories SmartDukaan

(root)/trunk/PyProj/src/shop2020/model/v1/catalog/script/Snapdeal.py – Rev 9401 → 9411

Rev 9401		Rev 9411
Line 121...		Line 121...
121	`print sheet.row_values(rownum)`	121	`print sheet.row_values(rownum)`
122	`item_id = int(sheet.row_values(rownum)[0])`	122	`item_id = int(sheet.row_values(rownum)[0])`
123	`supc = sheet.row_values(rownum)[1]`	123	`supc = sheet.row_values(rownum)[1]`
124	`item = Item.query.filter_by(id=item_id).one()`	124	`item = Item.query.filter_by(id=item_id).one()`
125	`category = Category.query.filter_by(id=item.category).one()`	125	`category = Category.query.filter_by(id=item.category).one()`
-		126	`parent_category = Category.query.filter_by(parent_category_id=category.parent_category_id).one()`
126	`snapdeal_item = SnapdealItem.query.filter_by(item_id=item_id).one()`	127	`snapdeal_item = SnapdealItem.query.filter_by(item_id=item_id).one()`
127	`warehouse = iclient.getWarehouse(snapdeal_item.warehouseId)`	128	`warehouse = iclient.getWarehouse(snapdeal_item.warehouseId)`
128	`item_pricing = iclient.getItemPricing(item_id, warehouse.vendor.id)`	129	`item_pricing = iclient.getItemPricing(item_id, warehouse.vendor.id)`
129	`#TO BE USED LATER`	130	`#TO BE USED LATER`
130	`#inventory_snapshot = iclient.getInventorySnapshot(0)`	131	`#inventory_snapshot = iclient.getInventorySnapshot(0)`
131	`#warehouses_ours = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("OURS"), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)`	132	`#warehouses_ours = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("OURS"), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)`
132	`#warehouses_third_party = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("THIRD_PARTY "), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)`	133	`#warehouses_third_party = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("THIRD_PARTY "), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)`
133	`one_line = _SnapdealItemInfo(item.id, item.product_group, category.label, item_pricing.nlc, item.brand, item.model_name, item.model_number, item.color, item.weight, category.parent_category_id, item.risky)`	134	`one_line = _SnapdealItemInfo(item.id, item.product_group, parent_category.displayName, item_pricing.nlc, item.brand, item.model_name, item.model_number, item.color, item.weight, category.parent_category_id, item.risky)`
134	`all_supc.append(supc)`	135	`all_supc.append(supc)`
135	`all_lines.append(one_line)`	136	`all_lines.append(one_line)`
136	`write_report("/tmp/snapdeal_running.xls", all_lines, all_supc)`	137	`write_report("/tmp/snapdeal_running.xls", all_lines, all_supc)`
137		138
138		139
Line 162...		Line 163...
162	`sheet.write(0, 7, "Color", heading_xf)`	163	`sheet.write(0, 7, "Color", heading_xf)`
163	`sheet.write(0, 8, "Weight", heading_xf)`	164	`sheet.write(0, 8, "Weight", heading_xf)`
164	`sheet.write(0, 9, "Courier Cost", heading_xf)`	165	`sheet.write(0, 9, "Courier Cost", heading_xf)`
165	`sheet.write(0, 10, "Risky", heading_xf)`	166	`sheet.write(0, 10, "Risky", heading_xf)`
166	`sheet.write(0, 11, "Our SP", heading_xf)`	167	`sheet.write(0, 11, "Our SP", heading_xf)`
167	`sheet.write(0, 12, "Our TP", heading_xf)`	168	`sheet.write(0, 13, "Our TP", heading_xf)`
168	`sheet.write(0, 13, "Our Offer Price", heading_xf)`	169	`sheet.write(0, 12, "Our Offer Price", heading_xf)`
169	`sheet.write(0, 14, "Our Rank", heading_xf)`	170	`sheet.write(0, 14, "Our Rank", heading_xf)`
170	`sheet.write(0, 15, "Lowest Seller", heading_xf)`	171	`sheet.write(0, 15, "Lowest Seller", heading_xf)`
171	`sheet.write(0, 16, "Lowest SP", heading_xf)`	172	`sheet.write(0, 16, "Lowest SP", heading_xf)`
172	`sheet.write(0, 17, "Lowest TP", heading_xf)`	173	`sheet.write(0, 17, "Lowest TP", heading_xf)`
173	`sheet.write(0, 18, "Lowest Offer Price", heading_xf)`	174	`sheet.write(0, 18, "Lowest Offer Price", heading_xf)`
Line 238...		Line 239...
238	`sheet.write(sheet_iterator, 7, one_line.color)`	239	`sheet.write(sheet_iterator, 7, one_line.color)`
239	`sheet.write(sheet_iterator, 8, one_line.weight)`	240	`sheet.write(sheet_iterator, 8, one_line.weight)`
240	`sheet.write(sheet_iterator, 9, round(courierCost))`	241	`sheet.write(sheet_iterator, 9, round(courierCost))`
241	`sheet.write(sheet_iterator, 10, one_line.risky)`	242	`sheet.write(sheet_iterator, 10, one_line.risky)`
242	`sheet.write(sheet_iterator, 11, supc_data.ourSp)`	243	`sheet.write(sheet_iterator, 11, supc_data.ourSp)`
243	`sheet.write(sheet_iterator, 12, round(supc_data.ourSp*0.9597-courierCost))`	244	`sheet.write(sheet_iterator, 13, round(supc_data.ourSp*0.9597-courierCost))`
244	`sheet.write(sheet_iterator, 13, supc_data.ourOfferPrice)`	245	`sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)`
245	`sheet.write(sheet_iterator, 14, supc_data.rank)`	246	`sheet.write(sheet_iterator, 14, supc_data.rank)`
246	`sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)`	247	`sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)`
247	`sheet.write(sheet_iterator, 16, supc_data.lowestSp)`	248	`sheet.write(sheet_iterator, 16, supc_data.lowestSp)`
248	`if one_line.parent_category ==10011:`	249	`if one_line.parent_category ==10011:`
249	`lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost`	250	`lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost`
Line 261...		Line 262...
261	`lowest_possible_tp = one_line.our_nlc/0.988+15+6`	262	`lowest_possible_tp = one_line.our_nlc/0.988+15+6`
262	`lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597`	263	`lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597`
263	`sheet.write(sheet_iterator, 22, round(lowest_possible_tp))`	264	`sheet.write(sheet_iterator, 22, round(lowest_possible_tp))`
264	`sheet.write(sheet_iterator, 23, round(lowest_possible_sp))`	265	`sheet.write(sheet_iterator, 23, round(lowest_possible_sp))`
265	`competitionBasis ='TP'`	266	`competitionBasis ='TP'`
266	`if (supc_data.lowestOfferPrice == supc_data.lowestSp):`	267	`if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice == supc_data.ourSp:`
267	`competitionBasis ='SP'`	268	`competitionBasis ='SP'`
268	`sheet.write(sheet_iterator, 24, 'SP')`	269	`sheet.write(sheet_iterator, 24, 'SP')`
269	`else:`	270	`else:`
270	`sheet.write(sheet_iterator, 24, 'TP')`	271	`sheet.write(sheet_iterator, 24, 'TP')`
271	`proposed_tp = 0`	272	`proposed_tp = 0`
272	`if lowestTp > lowest_possible_tp:`	273	`if lowestTp > lowest_possible_tp:`
273	`sheet.write(sheet_iterator, 25, "Yes")`	274	`sheet.write(sheet_iterator, 25, "Yes")`
274	`else:`	275	`else:`
275	`sheet.write(sheet_iterator, 25, "No")`	276	`sheet.write(sheet_iterator, 25, "No")`
276	`if competitionBasis=='SP':`	277	`if competitionBasis=='SP':`
277	`proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)`	278	`proposed_sp = supc_data.lowestSp - max(10, supc_data.lowestSp*0.001)`
278	`proposed_tp = proposed_sp*0.9597-courierCost`	279	`proposed_tp = proposed_sp*0.9597-courierCost`
279	`sheet.write(sheet_iterator, 26, round(proposed_tp))`	280	`sheet.write(sheet_iterator, 26, round(proposed_tp))`
280	`sheet.write(sheet_iterator, 27, round(proposed_sp))`	281	`sheet.write(sheet_iterator, 27, round(proposed_sp))`
281	`sheet.write(sheet_iterator, 28, round(proposed_tp*0.988-21))`	282	`sheet.write(sheet_iterator, 28, round(proposed_tp*0.988-21))`
282	`else:`	283	`else:`
283	`proposed_tp = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)`	284	`proposed_tp = lowestTp - max(10, lowestTp*0.001)`
284	`sheet.write(sheet_iterator, 26, round(proposed_tp))`	285	`sheet.write(sheet_iterator, 26, round(proposed_tp))`
285	`sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))`	286	`sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))`
286	`sheet.write(sheet_iterator, 28, round(proposed_tp*0.988-21))`	287	`sheet.write(sheet_iterator, 28, round(proposed_tp*0.988-21))`
287		288
288		289
Line 323...		Line 324...
323	`sheet.write(0, 7, "Color", heading_xf)`	324	`sheet.write(0, 7, "Color", heading_xf)`
324	`sheet.write(0, 8, "Weight", heading_xf)`	325	`sheet.write(0, 8, "Weight", heading_xf)`
325	`sheet.write(0, 9, "Courier Cost", heading_xf)`	326	`sheet.write(0, 9, "Courier Cost", heading_xf)`
326	`sheet.write(0, 10, "Risky", heading_xf)`	327	`sheet.write(0, 10, "Risky", heading_xf)`
327	`sheet.write(0, 11, "Our SP", heading_xf)`	328	`sheet.write(0, 11, "Our SP", heading_xf)`
328	`sheet.write(0, 12, "Our TP", heading_xf)`	329	`sheet.write(0, 13, "Our TP", heading_xf)`
329	`sheet.write(0, 13, "Our Offer Price", heading_xf)`	330	`sheet.write(0, 12, "Our Offer Price", heading_xf)`
330	`sheet.write(0, 14, "Our Rank", heading_xf)`	331	`sheet.write(0, 14, "Our Rank", heading_xf)`
331	`sheet.write(0, 15, "Lowest Seller", heading_xf)`	332	`sheet.write(0, 15, "Lowest Seller", heading_xf)`
332	`sheet.write(0, 16, "Second Lowest Seller", heading_xf)`	333	`sheet.write(0, 16, "Second Lowest Seller", heading_xf)`
333	`sheet.write(0, 17, "Second Lowest Price", heading_xf)`	334	`sheet.write(0, 17, "Second Lowest Price", heading_xf)`
334	`sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)`	335	`sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)`
Line 337...		Line 338...
337	`sheet.write(0, 21, "Second Lowest Seller Inventory", heading_xf)`	338	`sheet.write(0, 21, "Second Lowest Seller Inventory", heading_xf)`
338	`sheet.write(0, 22, "Our NLC", heading_xf)`	339	`sheet.write(0, 22, "Our NLC", heading_xf)`
339	`sheet.write(0, 23, "Competition Basis", heading_xf)`	340	`sheet.write(0, 23, "Competition Basis", heading_xf)`
340	`sheet.write(0, 24, "Target TP", heading_xf)`	341	`sheet.write(0, 24, "Target TP", heading_xf)`
341	`sheet.write(0, 25, "Target SP", heading_xf)`	342	`sheet.write(0, 25, "Target SP", heading_xf)`
342	`sheet.write(0, 26, "Target NLC", heading_xf)`	-
343	`sheet.write(0, 27, "MARGIN INCREASED POTENTIAL", heading_xf)`	343	`sheet.write(0, 26, "MARGIN INCREASED POTENTIAL", heading_xf)`
344		344
345	`i=1`	345	`i=1`
346		346
347	`for data in buyBoxItems:`	347	`for data in buyBoxItems:`
348	`supc_data =data[0]`	348	`supc_data =data[0]`
Line 367...		Line 367...
367	`sheet.write(i, 7, one_line.color)`	367	`sheet.write(i, 7, one_line.color)`
368	`sheet.write(i, 8, one_line.weight)`	368	`sheet.write(i, 8, one_line.weight)`
369	`sheet.write(i, 9, round(courierCost))`	369	`sheet.write(i, 9, round(courierCost))`
370	`sheet.write(i, 10, one_line.risky)`	370	`sheet.write(i, 10, one_line.risky)`
371	`sheet.write(i, 11, supc_data.ourSp)`	371	`sheet.write(i, 11, supc_data.ourSp)`
372	`sheet.write(i, 12, round(supc_data.ourSp*0.9597-courierCost))`	372	`sheet.write(i, 13, round(supc_data.ourSp*0.9597-courierCost))`
373	`sheet.write(i, 13, supc_data.ourOfferPrice)`	373	`sheet.write(i, 12, supc_data.ourOfferPrice)`
374	`sheet.write(i, 14, supc_data.rank)`	374	`sheet.write(i, 14, supc_data.rank)`
375	`sheet.write(i, 15, supc_data.lowestSellerName)`	375	`sheet.write(i, 15, supc_data.lowestSellerName)`
376	`sheet.write(i, 16, supc_data.secondLowestSellerName)`	376	`sheet.write(i, 16, supc_data.secondLowestSellerName)`
377	`sheet.write(i, 17, supc_data.secondLowestSellerSp)`	377	`sheet.write(i, 17, supc_data.secondLowestSellerSp)`
378	`sheet.write(i, 18, supc_data.secondLowestSellerOfferPrice)`	378	`sheet.write(i, 18, supc_data.secondLowestSellerOfferPrice)`
Line 386...		Line 386...
386	`sheet.write(i, 20, supc_data.ourInventory)`	386	`sheet.write(i, 20, supc_data.ourInventory)`
387	`sheet.write(i, 21, supc_data.secondLowestSellerInventory)`	387	`sheet.write(i, 21, supc_data.secondLowestSellerInventory)`
388	`sheet.write(i, 22, one_line.our_nlc)`	388	`sheet.write(i, 22, one_line.our_nlc)`
389		389
390	`competitionBasis ='TP'`	390	`competitionBasis ='TP'`
391	`if (supc_data.secondLowestSellerOfferPrice == supc_data.secondLowestSellerSp):`	391	`if (supc_data.secondLowestSellerOfferPrice == supc_data.secondLowestSellerSp) and supc_data.ourOfferPrice==supc_data.ourSp:`
392	`competitionBasis ='SP'`	392	`competitionBasis ='SP'`
393	`sheet.write(i, 23, 'SP')`	393	`sheet.write(i, 23, 'SP')`
394	`else:`	394	`else:`
395	`sheet.write(i, 23, 'TP')`	395	`sheet.write(i, 23, 'TP')`
396		396
397	`if competitionBasis=='SP':`	397	`if competitionBasis=='SP':`
398	`proposed_sp = max(supc_data.secondLowestSellerSp - max((20, supc_data.secondLowestSellerSp*0.002)), lowest_possible_sp)`	398	`proposed_sp = max(supc_data.secondLowestSellerSp - max((20, supc_data.secondLowestSellerSp*0.002)), lowest_possible_sp)`
399	`proposed_tp = proposed_sp*0.9597-courierCost`	399	`proposed_tp = proposed_sp*0.9597-courierCost`
400	`sheet.write(i, 24, round(proposed_tp))`	400	`sheet.write(i, 24, round(proposed_tp))`
401	`sheet.write(i, 25, round(proposed_sp))`	401	`sheet.write(i, 25, round(proposed_sp))`
402	`sheet.write(i, 26, round(proposed_tp*0.988-21))`	-
403	`else:`	402	`else:`
404	`proposed_tp = max(secondlowestTp - max((20, secondlowestTp*0.002)), lowest_possible_tp)`	403	`proposed_tp = max(secondlowestTp - max((20, secondlowestTp*0.002)), lowest_possible_tp)`
405	`sheet.write(i, 24, round(proposed_tp))`	404	`sheet.write(i, 24, round(proposed_tp))`
406	`sheet.write(i, 25, round((proposed_tp+courierCost)/0.9597))`	405	`sheet.write(i, 25, round((proposed_tp+courierCost)/0.9597))`
407	`sheet.write(i, 26, round(proposed_tp*0.988-21))`	-
408		406
409	`sheet.write(i, 27, round(proposed_tp-(supc_data.ourSp*0.9597-courierCost)))`	407	`sheet.write(i, 26, round(proposed_tp-(supc_data.ourSp*0.9597-courierCost)))`
410	`i+=1`	408	`i+=1`
411		409
412		410
413	`def createSheetForCometitiveItems(competitiveItems,wbk):`	411	`def createSheetForCometitiveItems(competitiveItems,wbk):`
414	`sheet = wbk.add_sheet('Can Compete-With Inventory')`	412	`sheet = wbk.add_sheet('Can Compete-With Inventory')`
Line 429...		Line 427...
429	`sheet.write(0, 7, "Color", heading_xf)`	427	`sheet.write(0, 7, "Color", heading_xf)`
430	`sheet.write(0, 8, "Weight", heading_xf)`	428	`sheet.write(0, 8, "Weight", heading_xf)`
431	`sheet.write(0, 9, "Courier Cost", heading_xf)`	429	`sheet.write(0, 9, "Courier Cost", heading_xf)`
432	`sheet.write(0, 10, "Risky", heading_xf)`	430	`sheet.write(0, 10, "Risky", heading_xf)`
433	`sheet.write(0, 11, "Our SP", heading_xf)`	431	`sheet.write(0, 11, "Our SP", heading_xf)`
434	`sheet.write(0, 12, "Our TP", heading_xf)`	432	`sheet.write(0, 13, "Our TP", heading_xf)`
435	`sheet.write(0, 13, "Our Offer Price", heading_xf)`	433	`sheet.write(0, 12, "Our Offer Price", heading_xf)`
436	`sheet.write(0, 14, "Our Rank", heading_xf)`	434	`sheet.write(0, 14, "Our Rank", heading_xf)`
437	`sheet.write(0, 15, "Lowest Seller", heading_xf)`	435	`sheet.write(0, 15, "Lowest Seller", heading_xf)`
438	`sheet.write(0, 16, "Lowest SP", heading_xf)`	436	`sheet.write(0, 16, "Lowest SP", heading_xf)`
439	`sheet.write(0, 17, "Lowest TP", heading_xf)`	437	`sheet.write(0, 17, "Lowest TP", heading_xf)`
440	`sheet.write(0, 18, "Lowest Offer Price", heading_xf)`	438	`sheet.write(0, 18, "Lowest Offer Price", heading_xf)`
Line 474...		Line 472...
474	`sheet.write(sheet_iterator, 7, one_line.color)`	472	`sheet.write(sheet_iterator, 7, one_line.color)`
475	`sheet.write(sheet_iterator, 8, one_line.weight)`	473	`sheet.write(sheet_iterator, 8, one_line.weight)`
476	`sheet.write(sheet_iterator, 9, round(courierCost))`	474	`sheet.write(sheet_iterator, 9, round(courierCost))`
477	`sheet.write(sheet_iterator, 10, one_line.risky)`	475	`sheet.write(sheet_iterator, 10, one_line.risky)`
478	`sheet.write(sheet_iterator, 11, supc_data.ourSp)`	476	`sheet.write(sheet_iterator, 11, supc_data.ourSp)`
479	`sheet.write(sheet_iterator, 12, round(supc_data.ourSp*0.9597-courierCost))`	477	`sheet.write(sheet_iterator, 13, round(supc_data.ourSp*0.9597-courierCost))`
480	`sheet.write(sheet_iterator, 13, supc_data.ourOfferPrice)`	478	`sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)`
481	`sheet.write(sheet_iterator, 14, supc_data.rank)`	479	`sheet.write(sheet_iterator, 14, supc_data.rank)`
482	`sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)`	480	`sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)`
483	`sheet.write(sheet_iterator, 16, supc_data.lowestSp)`	481	`sheet.write(sheet_iterator, 16, supc_data.lowestSp)`
484	`if one_line.parent_category ==10011:`	482	`if one_line.parent_category ==10011:`
485	`lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost`	483	`lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost`
Line 497...		Line 495...
497	`lowest_possible_tp = one_line.our_nlc/0.988+15+6`	495	`lowest_possible_tp = one_line.our_nlc/0.988+15+6`
498	`lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597`	496	`lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597`
499	`sheet.write(sheet_iterator, 22, round(lowest_possible_tp))`	497	`sheet.write(sheet_iterator, 22, round(lowest_possible_tp))`
500	`sheet.write(sheet_iterator, 23, round(lowest_possible_sp))`	498	`sheet.write(sheet_iterator, 23, round(lowest_possible_sp))`
501	`competitionBasis ='TP'`	499	`competitionBasis ='TP'`
502	`if (supc_data.lowestOfferPrice == supc_data.lowestSp):`	500	`if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice==supc_data.ourSp:`
503	`competitionBasis ='SP'`	501	`competitionBasis ='SP'`
504	`sheet.write(sheet_iterator, 24, 'SP')`	502	`sheet.write(sheet_iterator, 24, 'SP')`
505	`else:`	503	`else:`
506	`sheet.write(sheet_iterator, 24, 'TP')`	504	`sheet.write(sheet_iterator, 24, 'TP')`
507	`proposed_tp = 0`	505	`proposed_tp = 0`
Line 548...		Line 546...
548	`sheet.write(0, 7, "Color", heading_xf)`	546	`sheet.write(0, 7, "Color", heading_xf)`
549	`sheet.write(0, 8, "Weight", heading_xf)`	547	`sheet.write(0, 8, "Weight", heading_xf)`
550	`sheet.write(0, 9, "Courier Cost", heading_xf)`	548	`sheet.write(0, 9, "Courier Cost", heading_xf)`
551	`sheet.write(0, 10, "Risky", heading_xf)`	549	`sheet.write(0, 10, "Risky", heading_xf)`
552	`sheet.write(0, 11, "Our SP", heading_xf)`	550	`sheet.write(0, 11, "Our SP", heading_xf)`
553	`sheet.write(0, 12, "Our TP", heading_xf)`	551	`sheet.write(0, 13, "Our TP", heading_xf)`
554	`sheet.write(0, 13, "Our Offer Price", heading_xf)`	552	`sheet.write(0, 12, "Our Offer Price", heading_xf)`
555	`sheet.write(0, 14, "Our Rank", heading_xf)`	553	`sheet.write(0, 14, "Our Rank", heading_xf)`
556	`sheet.write(0, 15, "Lowest Seller", heading_xf)`	554	`sheet.write(0, 15, "Lowest Seller", heading_xf)`
557	`sheet.write(0, 16, "Lowest SP", heading_xf)`	555	`sheet.write(0, 16, "Lowest SP", heading_xf)`
558	`sheet.write(0, 17, "Lowest TP", heading_xf)`	556	`sheet.write(0, 17, "Lowest TP", heading_xf)`
559	`sheet.write(0, 18, "Lowest Offer Price", heading_xf)`	557	`sheet.write(0, 18, "Lowest Offer Price", heading_xf)`
Line 593...		Line 591...
593	`sheet.write(sheet_iterator, 7, one_line.color)`	591	`sheet.write(sheet_iterator, 7, one_line.color)`
594	`sheet.write(sheet_iterator, 8, one_line.weight)`	592	`sheet.write(sheet_iterator, 8, one_line.weight)`
595	`sheet.write(sheet_iterator, 9, round(courierCost))`	593	`sheet.write(sheet_iterator, 9, round(courierCost))`
596	`sheet.write(sheet_iterator, 10, one_line.risky)`	594	`sheet.write(sheet_iterator, 10, one_line.risky)`
597	`sheet.write(sheet_iterator, 11, supc_data.ourSp)`	595	`sheet.write(sheet_iterator, 11, supc_data.ourSp)`
598	`sheet.write(sheet_iterator, 12, '-')`	596	`sheet.write(sheet_iterator, 13, '-')`
599	`sheet.write(sheet_iterator, 13, supc_data.ourOfferPrice)`	597	`sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)`
600	`sheet.write(sheet_iterator, 14, supc_data.rank)`	598	`sheet.write(sheet_iterator, 14, supc_data.rank)`
601	`sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)`	599	`sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)`
602	`sheet.write(sheet_iterator, 16, supc_data.lowestSp)`	600	`sheet.write(sheet_iterator, 16, supc_data.lowestSp)`
603	`if one_line.parent_category ==10011:`	601	`if one_line.parent_category ==10011:`
604	`lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost`	602	`lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost`