Subversion Repositories SmartDukaan

Rev

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