Subversion Repositories SmartDukaan

Rev

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