Subversion Repositories SmartDukaan

Rev

Rev 9401 | Rev 9426 | 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()
9411 kshitij.so 126 
        parent_category = Category.query.filter_by(parent_category_id=category.parent_category_id).one()
9334 kshitij.so 127 
        snapdeal_item = SnapdealItem.query.filter_by(item_id=item_id).one()
128 
        warehouse = iclient.getWarehouse(snapdeal_item.warehouseId)
129 
        item_pricing = iclient.getItemPricing(item_id, warehouse.vendor.id)
9359 kshitij.so 130 
        #TO BE USED LATER
131 
        #inventory_snapshot = iclient.getInventorySnapshot(0)
132 
        #warehouses_ours = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("OURS"), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)
133 
        #warehouses_third_party = iclient.getWarehouses(WarehouseType._NAMES_TO_VALUES.get("THIRD_PARTY "), InventoryType._NAMES_TO_VALUES.get("GOOD"), 0,0,0)
9411 kshitij.so 134 
        one_line = _SnapdealItemInfo(item.id, item.product_group, parent_category.displayName, item_pricing.nlc, item.brand, item.model_name, item.model_number, item.color, item.weight, category.parent_category_id, item.risky)
9334 kshitij.so 135 
        all_supc.append(supc)
136 
        all_lines.append(one_line)
137 
    write_report("/tmp/snapdeal_running.xls", all_lines, all_supc)
138 
 
139 
 
140 
 
141 
def write_report(filename, all_lines, all_supc):
142 
 
143 
    buyBoxItems = []
9387 kshitij.so 144 
    competitive = []
9396 kshitij.so 145 
    competitiveNoInventory = []
9334 kshitij.so 146 
 
147 
    wbk = xlwt.Workbook()
9396 kshitij.so 148 
    sheet = wbk.add_sheet('Can\'t Compete')
9334 kshitij.so 149 
 
150 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
151 
 
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, "Category", heading_xf)
158 
    sheet.write(0, 2, "Product Group.", heading_xf)
159 
    sheet.write(0, 3, "SUPC", heading_xf)
160 
    sheet.write(0, 4, "Brand", heading_xf)
161 
    sheet.write(0, 5, "Model Name", heading_xf)
162 
    sheet.write(0, 6, "Model Number", heading_xf)
163 
    sheet.write(0, 7, "Color", heading_xf)
9359 kshitij.so 164 
    sheet.write(0, 8, "Weight", heading_xf)
9400 kshitij.so 165 
    sheet.write(0, 9, "Courier Cost", heading_xf)
9399 kshitij.so 166 
    sheet.write(0, 10, "Risky", heading_xf)
167 
    sheet.write(0, 11, "Our SP", heading_xf)
9411 kshitij.so 168 
    sheet.write(0, 13, "Our TP", heading_xf)
169 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
9399 kshitij.so 170 
    sheet.write(0, 14, "Our Rank", heading_xf)
171 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
172 
    sheet.write(0, 16, "Lowest SP", heading_xf)
173 
    sheet.write(0, 17, "Lowest TP", heading_xf)
174 
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)
175 
    sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
176 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
177 
    sheet.write(0, 21, "Our NLC", heading_xf)
178 
    sheet.write(0, 22, "Lowest Possible TP", heading_xf)
179 
    sheet.write(0, 23, "Lowest Possible SP", heading_xf)
180 
    sheet.write(0, 24, "Competition Basis ", heading_xf)
181 
    sheet.write(0, 25, "Can Compete", heading_xf)
182 
    sheet.write(0, 26, "Target TP", heading_xf)
183 
    sheet.write(0, 27, "Target SP", heading_xf)
184 
    sheet.write(0, 28, "Target NLC", heading_xf)
185 
    sheet.write(0, 29, "Sales Potential", heading_xf)
9334 kshitij.so 186 
 
9337 kshitij.so 187 
    i, sheet_iterator=1,1
9334 kshitij.so 188 
    for one_line in all_lines:
189 
        supc = all_supc[i-1]
190 
        supc_data = fetchDetails(supc)
191 
 
192 
        courierCost = 45
193 
 
194 
        if one_line.weight:
9381 kshitij.so 195 
            slab = int(((one_line.weight+0.05) - .001)/.5)
9334 kshitij.so 196 
        for x in range (0,slab):
197 
            courierCost = courierCost + 35
198 
 
199 
        courierCost = courierCost * 1.1236
200 
 
201 
        if supc_data.rank==1:
202 
            temp = []
203 
            temp.append(supc_data)
204 
            temp.append(one_line)
205 
            buyBoxItems.append(temp)
206 
            i+=1
207 
            continue
208 
 
9387 kshitij.so 209 
        if one_line.parent_category ==10011:
210 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
211 
        else:
212 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
213 
 
214 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
215 
 
9396 kshitij.so 216 
        if (lowestTp > lowest_possible_tp and supc_data.ourInventory!=0):
9387 kshitij.so 217 
            temp=[]
218 
            temp.append(supc_data)
219 
            temp.append(one_line)
220 
            competitive.append(temp)
221 
            i+=1
222 
            continue
223 
 
9396 kshitij.so 224 
        if (lowestTp > lowest_possible_tp and supc_data.ourInventory==0):
225 
            temp=[]
226 
            temp.append(supc_data)
227 
            temp.append(one_line)
228 
            competitiveNoInventory.append(temp)
229 
            i+=1
230 
            continue
9387 kshitij.so 231 
 
9337 kshitij.so 232 
        sheet.write(sheet_iterator, 0, one_line.item_id)
233 
        sheet.write(sheet_iterator, 1, one_line.category_name)
234 
        sheet.write(sheet_iterator, 2, one_line.product_group)
235 
        sheet.write(sheet_iterator, 3, supc)
236 
        sheet.write(sheet_iterator, 4, one_line.brand)
237 
        sheet.write(sheet_iterator, 5, one_line.model_name)
238 
        sheet.write(sheet_iterator, 6, one_line.model_number)
239 
        sheet.write(sheet_iterator, 7, one_line.color)
9359 kshitij.so 240 
        sheet.write(sheet_iterator, 8, one_line.weight)
9400 kshitij.so 241 
        sheet.write(sheet_iterator, 9, round(courierCost))
9399 kshitij.so 242 
        sheet.write(sheet_iterator, 10, one_line.risky)
243 
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
9411 kshitij.so 244 
        sheet.write(sheet_iterator, 13, round(supc_data.ourSp*0.9597-courierCost))
245 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
9399 kshitij.so 246 
        sheet.write(sheet_iterator, 14, supc_data.rank)
247 
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
248 
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
9334 kshitij.so 249 
        if one_line.parent_category ==10011:
250 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
251 
        else:
252 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
9399 kshitij.so 253 
        sheet.write(sheet_iterator, 17, round(lowestTp))
254 
        sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
255 
        sheet.write(sheet_iterator, 19, supc_data.otherInventory)
256 
        sheet.write(sheet_iterator, 20, supc_data.ourInventory)
257 
        sheet.write(sheet_iterator, 21, one_line.our_nlc)
9334 kshitij.so 258 
        if supc_data.rank==1:
259 
            i+=1
9337 kshitij.so 260 
            sheet_iterator+=1
9334 kshitij.so 261 
            continue
262 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
9366 kshitij.so 263 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
9399 kshitij.so 264 
        sheet.write(sheet_iterator, 22, round(lowest_possible_tp))
265 
        sheet.write(sheet_iterator, 23, round(lowest_possible_sp))
9390 kshitij.so 266 
        competitionBasis ='TP'
9411 kshitij.so 267 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice == supc_data.ourSp:
9390 kshitij.so 268 
            competitionBasis ='SP'
9399 kshitij.so 269 
            sheet.write(sheet_iterator, 24, 'SP')
9366 kshitij.so 270 
        else:
9399 kshitij.so 271 
            sheet.write(sheet_iterator, 24, 'TP')
9334 kshitij.so 272 
        proposed_tp = 0
273 
        if lowestTp > lowest_possible_tp:
9399 kshitij.so 274 
            sheet.write(sheet_iterator, 25, "Yes")
9334 kshitij.so 275 
        else:
9399 kshitij.so 276 
            sheet.write(sheet_iterator, 25, "No")
9396 kshitij.so 277 
        if competitionBasis=='SP':
9411 kshitij.so 278 
            proposed_sp = supc_data.lowestSp - max(10, supc_data.lowestSp*0.001)
9396 kshitij.so 279 
            proposed_tp = proposed_sp*0.9597-courierCost
9399 kshitij.so 280 
            sheet.write(sheet_iterator, 26, round(proposed_tp))
281 
            sheet.write(sheet_iterator, 27, round(proposed_sp))
282 
            sheet.write(sheet_iterator, 28, round(proposed_tp*0.988-21))
9396 kshitij.so 283 
        else:
9411 kshitij.so 284 
            proposed_tp  = lowestTp - max(10, lowestTp*0.001)
9399 kshitij.so 285 
            sheet.write(sheet_iterator, 26, round(proposed_tp))
286 
            sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))
287 
            sheet.write(sheet_iterator, 28, round(proposed_tp*0.988-21))
9381 kshitij.so 288 
 
289 
 
9366 kshitij.so 290 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
9399 kshitij.so 291 
            sheet.write(sheet_iterator, 29, "HIGH")
9366 kshitij.so 292 
        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 293 
            sheet.write(sheet_iterator, 29, "MEDIUM")
9366 kshitij.so 294 
        else:
9399 kshitij.so 295 
            sheet.write(sheet_iterator, 29, "LOW")
9334 kshitij.so 296 
        i= i+1
9337 kshitij.so 297 
        sheet_iterator+=1
298 
 
9334 kshitij.so 299 
    createSheetForBuyBoxItems(buyBoxItems,wbk)
9387 kshitij.so 300 
    createSheetForCometitiveItems(competitive,wbk)
9396 kshitij.so 301 
    createSheetForCometitiveItemsNoInventory(competitiveNoInventory,wbk)
9334 kshitij.so 302 
    wbk.save(filename)
9365 kshitij.so 303 
 
9367 kshitij.so 304 
    EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@shop2020.in"], " " , "", [get_attachment_part(filename)], ["kshitij.sood@shop2020.in"], [])
9334 kshitij.so 305 
 
9365 kshitij.so 306 
 
307 
 
9334 kshitij.so 308 
def createSheetForBuyBoxItems(buyBoxItems,wbk):
9396 kshitij.so 309 
    sheet = wbk.add_sheet('Lowest')
9334 kshitij.so 310 
 
311 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
312 
 
313 
    excel_integer_format = '0'
314 
    integer_style = xlwt.XFStyle()
315 
    integer_style.num_format_str = excel_integer_format
316 
 
317 
    sheet.write(0, 0, "Item ID", heading_xf)
318 
    sheet.write(0, 1, "Category", heading_xf)
319 
    sheet.write(0, 2, "Product Group.", heading_xf)
320 
    sheet.write(0, 3, "SUPC", heading_xf)
321 
    sheet.write(0, 4, "Brand", heading_xf)
322 
    sheet.write(0, 5, "Model Name", heading_xf)
323 
    sheet.write(0, 6, "Model Number", heading_xf)
324 
    sheet.write(0, 7, "Color", heading_xf)
9359 kshitij.so 325 
    sheet.write(0, 8, "Weight", heading_xf)
9399 kshitij.so 326 
    sheet.write(0, 9, "Courier Cost", heading_xf)
327 
    sheet.write(0, 10, "Risky", heading_xf)
328 
    sheet.write(0, 11, "Our SP", heading_xf)
9411 kshitij.so 329 
    sheet.write(0, 13, "Our TP", heading_xf)
330 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
9399 kshitij.so 331 
    sheet.write(0, 14, "Our Rank", heading_xf)
332 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
333 
    sheet.write(0, 16, "Second Lowest Seller", heading_xf)
334 
    sheet.write(0, 17, "Second Lowest Price", heading_xf)
335 
    sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)
336 
    sheet.write(0, 19, "Second Lowest Seller TP", heading_xf)
9396 kshitij.so 337 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
338 
    sheet.write(0, 21, "Second Lowest Seller Inventory", heading_xf)
339 
    sheet.write(0, 22, "Our NLC", heading_xf)
340 
    sheet.write(0, 23, "Competition Basis", heading_xf)
341 
    sheet.write(0, 24, "Target TP", heading_xf)
342 
    sheet.write(0, 25, "Target SP", heading_xf)
9411 kshitij.so 343 
    sheet.write(0, 26, "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)
9400 kshitij.so 369 
        sheet.write(i, 9, round(courierCost))
9399 kshitij.so 370 
        sheet.write(i, 10, one_line.risky)
371 
        sheet.write(i, 11, supc_data.ourSp)
9411 kshitij.so 372 
        sheet.write(i, 13, round(supc_data.ourSp*0.9597-courierCost))
373 
        sheet.write(i, 12, supc_data.ourOfferPrice)
9399 kshitij.so 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
9401 kshitij.so 385 
        sheet.write(i, 19, round(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'
9411 kshitij.so 391 
        if (supc_data.secondLowestSellerOfferPrice == supc_data.secondLowestSellerSp) and supc_data.ourOfferPrice==supc_data.ourSp:
9396 kshitij.so 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))
402 
        else:
403 
            proposed_tp  = max(secondlowestTp - max((20, secondlowestTp*0.002)), lowest_possible_tp)
404 
            sheet.write(i, 24, round(proposed_tp))
405 
            sheet.write(i, 25, round((proposed_tp+courierCost)/0.9597))
406 
 
9411 kshitij.so 407 
        sheet.write(i, 26, round(proposed_tp-(supc_data.ourSp*0.9597-courierCost)))
9334 kshitij.so 408 
        i+=1
9387 kshitij.so 409 
 
410 
 
411 
def createSheetForCometitiveItems(competitiveItems,wbk):
9396 kshitij.so 412 
    sheet = wbk.add_sheet('Can Compete-With Inventory')
9387 kshitij.so 413 
 
414 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
9334 kshitij.so 415 
 
9387 kshitij.so 416 
    excel_integer_format = '0'
417 
    integer_style = xlwt.XFStyle()
418 
    integer_style.num_format_str = excel_integer_format
419 
 
420 
    sheet.write(0, 0, "Item ID", heading_xf)
421 
    sheet.write(0, 1, "Category", heading_xf)
422 
    sheet.write(0, 2, "Product Group.", heading_xf)
423 
    sheet.write(0, 3, "SUPC", heading_xf)
424 
    sheet.write(0, 4, "Brand", heading_xf)
425 
    sheet.write(0, 5, "Model Name", heading_xf)
426 
    sheet.write(0, 6, "Model Number", heading_xf)
427 
    sheet.write(0, 7, "Color", heading_xf)
428 
    sheet.write(0, 8, "Weight", heading_xf)
9399 kshitij.so 429 
    sheet.write(0, 9, "Courier Cost", heading_xf)
430 
    sheet.write(0, 10, "Risky", heading_xf)
431 
    sheet.write(0, 11, "Our SP", heading_xf)
9411 kshitij.so 432 
    sheet.write(0, 13, "Our TP", heading_xf)
433 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
9399 kshitij.so 434 
    sheet.write(0, 14, "Our Rank", heading_xf)
435 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
436 
    sheet.write(0, 16, "Lowest SP", heading_xf)
437 
    sheet.write(0, 17, "Lowest TP", heading_xf)
438 
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)
439 
    sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
440 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
441 
    sheet.write(0, 21, "Our NLC", heading_xf)
442 
    sheet.write(0, 22, "Lowest Possible TP", heading_xf)
443 
    sheet.write(0, 23, "Lowest Possible SP", heading_xf)
444 
    sheet.write(0, 24, "Competition Basis ", heading_xf)
445 
    sheet.write(0, 25, "Can Compete", heading_xf)
446 
    sheet.write(0, 26, "Target TP", heading_xf)
447 
    sheet.write(0, 27, "Target SP", heading_xf)
448 
    sheet.write(0, 28, "Sales Potential", heading_xf)
9387 kshitij.so 449 
 
450 
    i, sheet_iterator=1,1
451 
    for data in competitiveItems:
452 
        supc_data =data[0]
453 
        one_line = data[1]
454 
 
455 
        courierCost = 45
456 
 
457 
        if one_line.weight:
458 
            slab = int(((one_line.weight+0.05) - .001)/.5)
459 
        for x in range (0,slab):
460 
            courierCost = courierCost + 35
461 
 
462 
        courierCost = courierCost * 1.1236
463 
 
464 
 
465 
        sheet.write(sheet_iterator, 0, one_line.item_id)
466 
        sheet.write(sheet_iterator, 1, one_line.category_name)
467 
        sheet.write(sheet_iterator, 2, one_line.product_group)
468 
        sheet.write(sheet_iterator, 3, supc_data.supc)
469 
        sheet.write(sheet_iterator, 4, one_line.brand)
470 
        sheet.write(sheet_iterator, 5, one_line.model_name)
471 
        sheet.write(sheet_iterator, 6, one_line.model_number)
472 
        sheet.write(sheet_iterator, 7, one_line.color)
473 
        sheet.write(sheet_iterator, 8, one_line.weight)
9400 kshitij.so 474 
        sheet.write(sheet_iterator, 9, round(courierCost))
9399 kshitij.so 475 
        sheet.write(sheet_iterator, 10, one_line.risky)
476 
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
9411 kshitij.so 477 
        sheet.write(sheet_iterator, 13, round(supc_data.ourSp*0.9597-courierCost))
478 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
9399 kshitij.so 479 
        sheet.write(sheet_iterator, 14, supc_data.rank)
480 
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
481 
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
9387 kshitij.so 482 
        if one_line.parent_category ==10011:
483 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
484 
        else:
485 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
9399 kshitij.so 486 
        sheet.write(sheet_iterator, 17, round(lowestTp))
487 
        sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
488 
        sheet.write(sheet_iterator, 19, supc_data.otherInventory)
489 
        sheet.write(sheet_iterator, 20, supc_data.ourInventory)
490 
        sheet.write(sheet_iterator, 21, one_line.our_nlc)
9387 kshitij.so 491 
        if supc_data.rank==1:
492 
            i+=1
493 
            sheet_iterator+=1
494 
            continue
495 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
496 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
9399 kshitij.so 497 
        sheet.write(sheet_iterator, 22, round(lowest_possible_tp))
498 
        sheet.write(sheet_iterator, 23, round(lowest_possible_sp))
9390 kshitij.so 499 
        competitionBasis ='TP'
9411 kshitij.so 500 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp) and supc_data.ourOfferPrice==supc_data.ourSp:
9390 kshitij.so 501 
            competitionBasis ='SP'
9399 kshitij.so 502 
            sheet.write(sheet_iterator, 24, 'SP')
9387 kshitij.so 503 
        else:
9399 kshitij.so 504 
            sheet.write(sheet_iterator, 24, 'TP')
9387 kshitij.so 505 
        proposed_tp = 0
506 
        if lowestTp > lowest_possible_tp:
9399 kshitij.so 507 
            sheet.write(sheet_iterator, 25, "Yes")
9390 kshitij.so 508 
            if competitionBasis=='SP':
509 
                proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
9396 kshitij.so 510 
                proposed_tp = proposed_sp*.9597-courierCost
9399 kshitij.so 511 
                sheet.write(sheet_iterator, 26, round(proposed_tp))
512 
                sheet.write(sheet_iterator, 27, round(proposed_sp))
9390 kshitij.so 513 
            else:
514 
                proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
9399 kshitij.so 515 
                sheet.write(sheet_iterator, 26, round(proposed_tp))
516 
                sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))
9387 kshitij.so 517 
        else:
9399 kshitij.so 518 
            sheet.write(sheet_iterator, 25, "No")
9387 kshitij.so 519 
 
520 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
9399 kshitij.so 521 
            sheet.write(sheet_iterator, 28, "HIGH")
9387 kshitij.so 522 
        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 523 
            sheet.write(sheet_iterator, 28, "MEDIUM")
9387 kshitij.so 524 
        else:
9399 kshitij.so 525 
            sheet.write(sheet_iterator, 28, "LOW")
9387 kshitij.so 526 
        i= i+1
527 
        sheet_iterator+=1
9334 kshitij.so 528 
 
9396 kshitij.so 529 
 
530 
def createSheetForCometitiveItemsNoInventory(competitiveNoInventoryItems,wbk):
531 
    sheet = wbk.add_sheet('Can Compete-No Inventory')
532 
 
533 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
534 
 
535 
    excel_integer_format = '0'
536 
    integer_style = xlwt.XFStyle()
537 
    integer_style.num_format_str = excel_integer_format
538 
 
539 
    sheet.write(0, 0, "Item ID", heading_xf)
540 
    sheet.write(0, 1, "Category", heading_xf)
541 
    sheet.write(0, 2, "Product Group.", heading_xf)
542 
    sheet.write(0, 3, "SUPC", heading_xf)
543 
    sheet.write(0, 4, "Brand", heading_xf)
544 
    sheet.write(0, 5, "Model Name", heading_xf)
545 
    sheet.write(0, 6, "Model Number", heading_xf)
546 
    sheet.write(0, 7, "Color", heading_xf)
547 
    sheet.write(0, 8, "Weight", heading_xf)
9399 kshitij.so 548 
    sheet.write(0, 9, "Courier Cost", heading_xf)
549 
    sheet.write(0, 10, "Risky", heading_xf)
550 
    sheet.write(0, 11, "Our SP", heading_xf)
9411 kshitij.so 551 
    sheet.write(0, 13, "Our TP", heading_xf)
552 
    sheet.write(0, 12, "Our Offer Price", heading_xf)
9399 kshitij.so 553 
    sheet.write(0, 14, "Our Rank", heading_xf)
554 
    sheet.write(0, 15, "Lowest Seller", heading_xf)
555 
    sheet.write(0, 16, "Lowest SP", heading_xf)
556 
    sheet.write(0, 17, "Lowest TP", heading_xf)
557 
    sheet.write(0, 18, "Lowest Offer Price", heading_xf)
558 
    sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
559 
    sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
560 
    sheet.write(0, 21, "Our NLC", heading_xf)
561 
    sheet.write(0, 22, "Lowest Possible TP", heading_xf)
562 
    sheet.write(0, 23, "Lowest Possible SP", heading_xf)
563 
    sheet.write(0, 24, "Competition Basis ", heading_xf)
564 
    sheet.write(0, 25, "Can Compete", heading_xf)
565 
    sheet.write(0, 26, "Target TP", heading_xf)
566 
    sheet.write(0, 27, "Target SP", heading_xf)
567 
    sheet.write(0, 28, "Sales Potential", heading_xf)
9396 kshitij.so 568 
 
569 
    i, sheet_iterator=1,1
570 
    for data in competitiveNoInventoryItems:
571 
        supc_data =data[0]
572 
        one_line = data[1]
573 
 
574 
        courierCost = 45
575 
 
576 
        if one_line.weight:
577 
            slab = int(((one_line.weight+0.05) - .001)/.5)
578 
        for x in range (0,slab):
579 
            courierCost = courierCost + 35
580 
 
581 
        courierCost = courierCost * 1.1236
582 
 
583 
 
584 
        sheet.write(sheet_iterator, 0, one_line.item_id)
585 
        sheet.write(sheet_iterator, 1, one_line.category_name)
586 
        sheet.write(sheet_iterator, 2, one_line.product_group)
587 
        sheet.write(sheet_iterator, 3, supc_data.supc)
588 
        sheet.write(sheet_iterator, 4, one_line.brand)
589 
        sheet.write(sheet_iterator, 5, one_line.model_name)
590 
        sheet.write(sheet_iterator, 6, one_line.model_number)
591 
        sheet.write(sheet_iterator, 7, one_line.color)
592 
        sheet.write(sheet_iterator, 8, one_line.weight)
9400 kshitij.so 593 
        sheet.write(sheet_iterator, 9, round(courierCost))
9399 kshitij.so 594 
        sheet.write(sheet_iterator, 10, one_line.risky)
595 
        sheet.write(sheet_iterator, 11, supc_data.ourSp)
9411 kshitij.so 596 
        sheet.write(sheet_iterator, 13, '-')
597 
        sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
9399 kshitij.so 598 
        sheet.write(sheet_iterator, 14, supc_data.rank)
599 
        sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
600 
        sheet.write(sheet_iterator, 16, supc_data.lowestSp)
9396 kshitij.so 601 
        if one_line.parent_category ==10011:
602 
            lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
603 
        else:
604 
            lowestTp = supc_data.lowestSp*0.9497-courierCost
9399 kshitij.so 605 
        sheet.write(sheet_iterator, 17, round(lowestTp))
606 
        sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
607 
        sheet.write(sheet_iterator, 19, supc_data.otherInventory)
608 
        sheet.write(sheet_iterator, 20, supc_data.ourInventory)
609 
        sheet.write(sheet_iterator, 21, one_line.our_nlc)
9396 kshitij.so 610 
        if supc_data.rank==1:
611 
            i+=1
612 
            sheet_iterator+=1
613 
            continue
614 
        lowest_possible_tp = one_line.our_nlc/0.988+15+6
615 
        lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
9399 kshitij.so 616 
        sheet.write(sheet_iterator, 22, round(lowest_possible_tp))
617 
        sheet.write(sheet_iterator, 23, round(lowest_possible_sp))
9396 kshitij.so 618 
        competitionBasis ='TP'
619 
        if (supc_data.lowestOfferPrice == supc_data.lowestSp):
620 
            competitionBasis ='SP'
9399 kshitij.so 621 
            sheet.write(sheet_iterator, 24, 'SP')
9396 kshitij.so 622 
        else:
9399 kshitij.so 623 
            sheet.write(sheet_iterator, 24, 'TP')
9396 kshitij.so 624 
        proposed_tp = 0
625 
        if lowestTp > lowest_possible_tp:
9399 kshitij.so 626 
            sheet.write(sheet_iterator, 25, "Yes")
9396 kshitij.so 627 
            if competitionBasis=='SP':
628 
                proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
629 
                proposed_tp = proposed_sp*.9597-courierCost
9399 kshitij.so 630 
                sheet.write(sheet_iterator, 26, round(proposed_tp))
631 
                sheet.write(sheet_iterator, 27, round(proposed_sp))
9396 kshitij.so 632 
            else:
633 
                proposed_tp  = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
9399 kshitij.so 634 
                sheet.write(sheet_iterator, 26, round(proposed_tp))
635 
                sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))
9396 kshitij.so 636 
        else:
9399 kshitij.so 637 
            sheet.write(sheet_iterator, 25, "No")
9396 kshitij.so 638 
 
639 
        if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
9399 kshitij.so 640 
            sheet.write(sheet_iterator, 28, "HIGH")
9396 kshitij.so 641 
        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 642 
            sheet.write(sheet_iterator, 28, "MEDIUM")
9396 kshitij.so 643 
        else:
9399 kshitij.so 644 
            sheet.write(sheet_iterator, 28, "LOW")
9396 kshitij.so 645 
        i= i+1
646 
        sheet_iterator+=1
647 
 
648 
 
649 
 
9334 kshitij.so 650 
def main():
651 
    parser = optparse.OptionParser()
652 
    parser.add_option("-f", "--file", dest="filename",
653 
                   default="ItemList.xls", type="string",
654 
                   help="Read the item list from FILE",
655 
                   metavar="FILE")
656 
    (options, args) = parser.parse_args()
657 
    if len(args) != 0:
658 
        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
659 
    filename = options.filename
660 
    read_data(filename)
661 
 
662 
if __name__ == '__main__':
663 
    main()
664