Subversion Repositories SmartDukaan

Rev

Rev 9411 | Rev 9451 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

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