Subversion Repositories SmartDukaan

Rev

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