Subversion Repositories SmartDukaan

Rev

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

Rev Author Line No. Line
10401 amar.kumar 1 
from elixir import *
2 
from shop2020.config.client.ConfigClient import ConfigClient
3 
from shop2020.model.v1.catalog.impl import DataService
11098 kshitij.so 4 
from shop2020.model.v1.catalog.impl.DataService import SnapdealItem, MarketplaceItems, Item, Category, \
5 
MarketPlaceUpdateHistory
10401 amar.kumar 6 
from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
7 
import mechanize
8 
import sys
9 
import cookielib
10 
from time import sleep
11 
import json
12 
import smtplib
10414 kshitij.so 13 
import xlwt
10401 amar.kumar 14 
from datetime import datetime
15 
from shop2020.utils import EmailAttachmentSender
16 
from shop2020.utils.EmailAttachmentSender import get_attachment_part
17 
from email.mime.text import MIMEText
18 
import email
19 
from email.mime.multipart import MIMEMultipart
20 
import email.encoders
11098 kshitij.so 21 
import copy
10401 amar.kumar 22 
 
23 
config_client = ConfigClient()
24 
host = config_client.get_property('staging_hostname')
25 
DataService.initialize(db_hostname=host)
26 
 
11098 kshitij.so 27 
courierCostToSync = []
28 
oldPricing = []
29 
 
10401 amar.kumar 30 
class __SnapdealInfo:
10414 kshitij.so 31 
    def __init__(self, sellingPrice, weight, transferPrice, commission, commissionPercentage, courierCost, sellingPriceSnapdeal, transferPriceSnapdeal, fixedMargin, \
32 
                 fixedMarginPercentage, collectionCharges, logisticCostSnapdeal, weightSnapdeal, supc, itemId, parentCategory, productGroup ,brand, modelName, modelNumber, \
11098 kshitij.so 33 
                 color,reason,woodenPackagingCost):
10401 amar.kumar 34 
 
35 
        self.sellingPrice = sellingPrice
36 
        self.weight = weight
37 
        self.transferPrice = transferPrice
38 
        self.commission = commission
39 
        self.commissionPercentage = commissionPercentage
40 
        self.courierCost = courierCost
41 
        self.sellingPriceSnapdeal = sellingPriceSnapdeal
42 
        self.transferPriceSnapdeal = transferPriceSnapdeal
43 
        self.fixedMargin = fixedMargin
44 
        self.fixedMarginPercentage = fixedMarginPercentage
45 
        self.collectionCharges = collectionCharges
46 
        self.logisticCostSnapdeal = logisticCostSnapdeal
47 
        self.weightSnapdeal = weightSnapdeal
48 
        self.supc = supc
49 
        self.itemId = itemId
10414 kshitij.so 50 
        self.parentCategory = parentCategory
51 
        self.productGroup = productGroup
52 
        self.brand = brand
53 
        self.modelName = modelName
54 
        self.modelNumber = modelNumber
10428 kshitij.so 55 
        self.color = color
56 
        self.reason = reason
11098 kshitij.so 57 
        self.woodenPackagingCost = woodenPackagingCost
10414 kshitij.so 58 
 
10401 amar.kumar 59 
 
60 
def getBrowserObject():
61 
    br = mechanize.Browser(factory=mechanize.RobustFactory())
62 
    cj = cookielib.LWPCookieJar()
63 
    br.set_cookiejar(cj)
64 
    br.set_handle_equiv(True)
65 
    br.set_handle_redirect(True)
66 
    br.set_handle_referer(True)
67 
    br.set_handle_robots(False)
68 
    br.set_debug_http(False)
69 
    br.set_debug_redirects(False)
70 
    br.set_debug_responses(False)
71 
 
72 
    br.set_handle_refresh(mechanize._http.HTTPRefreshProcessor(), max_time=1)
73 
 
74 
    br.addheaders = [('User-agent','Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11'),
75 
                     ('Accept', 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8'),
76 
                     ('Accept-Encoding', 'gzip,deflate,sdch'),
77 
                     ('Accept-Language', 'en-US,en;q=0.8'),
78 
                     ('Accept-Charset', 'ISO-8859-1,utf-8;q=0.7,*;q=0.3')]
79 
    return br
80 
 
81 
def login(url):
82 
    br = getBrowserObject()
83 
    br.open(url)
84 
    response = br.open(url)
85 
    br.select_form(nr=0)
86 
    br.form['username'] = "saholic-snapdeal@saholic.com"
11265 kshitij.so 87 
    br.form['password'] = "snapsaholic"
10401 amar.kumar 88 
    print "Trying to login"
89 
    response = br.submit()
90 
    return br
91 
 
92 
def populateStuff(br):
93 
    exceptionList = []
94 
    fetchedItems = []
10414 kshitij.so 95 
    items = session.query(SnapdealItem,MarketplaceItems,Item).join((MarketplaceItems,SnapdealItem.item_id==MarketplaceItems.itemId)).join((Item,SnapdealItem.item_id==Item.id)).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()
11098 kshitij.so 96 
    #items = session.query(SnapdealItem,MarketplaceItems,Item).join((MarketplaceItems,SnapdealItem.item_id==MarketplaceItems.itemId)).join((Item,SnapdealItem.item_id==Item.id)).filter(SnapdealItem.item_id==12613).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()
10401 amar.kumar 97 
    for item in items:
98 
        snapdealItem = item[0]
99 
        marketplaceItem = item[1]
10414 kshitij.so 100 
        ds_item = item[2]
101 
        category = Category.query.filter_by(id=ds_item.category).one()
102 
        parent_category = Category.query.filter_by(id=category.parent_category_id).first()
10404 kshitij.so 103 
        try:
104 
            snapdealInfo = fetchData(br,snapdealItem.supc)
105 
        except Exception as e:
106 
            exceptionList.append(item)
107 
            print "Unable to fetch details ",e
108 
            continue
109 
        snapdealInfo.sellingPrice = snapdealItem.sellingPrice
11102 kshitij.so 110 
        snapdealInfo.courierCost = snapdealItem.courierCostMarketplace
10404 kshitij.so 111 
        snapdealInfo.transferPrice = snapdealItem.transferPrice
112 
        snapdealInfo.commissionPercentage = marketplaceItem.commission
113 
        snapdealInfo.commission = snapdealItem.commission
114 
        snapdealInfo.itemId = snapdealItem.item_id
10414 kshitij.so 115 
        snapdealInfo.parentCategory = parent_category.display_name
116 
        snapdealInfo.productGroup = ds_item.product_group
117 
        snapdealInfo.brand = ds_item.brand
118 
        snapdealInfo.modelName = ds_item.model_name
119 
        snapdealInfo.modelNumber = ds_item.model_number
120 
        snapdealInfo.color = ds_item.color
121 
        snapdealInfo.weight = ds_item.weight
10404 kshitij.so 122 
        fetchedItems.append(snapdealInfo)
10401 amar.kumar 123 
    return exceptionList, fetchedItems
124 
 
125 
def fetchData(br,supc):
11098 kshitij.so 126 
    print "Fetching data for ",supc
10401 amar.kumar 127 
    url="http://seller.snapdeal.com/pricing/search?searchType=SUPC&searchValue=%s&gridType=normal&_search=false&nd=1396007375971&rows=30&page=1&sidx=&sord=asc"%(supc)
128 
    response = br.open(url)
12161 kshitij.so 129 
    #dataform = str(response.read()).strip("'<>() ").replace('\'', '\"')
130 
    struct = json.loads(response.read())
10401 amar.kumar 131 
    sdObj = struct['rows'][0]
132 
    print sdObj
12817 kshitij.so 133 
    if type(sdObj['catalogLive']) is None or not (sdObj['catalogLive']):
11772 kshitij.so 134 
        raise
12818 kshitij.so 135 
    woodenPackaging = 0.0
136 
    if sdObj['woodenPackaging']:
137 
        woodenPackaging = sdObj['woodenPackagingCost']
10414 kshitij.so 138 
    snapdealInfo = __SnapdealInfo(None,None,None,None,None,None,sdObj['sellingPrice'],sdObj['netSellerPayable'],sdObj['fixedMarginAmount'],sdObj['fixedMarginPercent'],sdObj['collectionCharges'],sdObj['logisticCost'],sdObj['deadWeight'],supc,None, \
12818 kshitij.so 139 
                    None, None, None, None, None, None, None,woodenPackaging)
10401 amar.kumar 140 
    return snapdealInfo
141 
 
142 
def filterData(fetchedItems):
10414 kshitij.so 143 
    filterList = []
10401 amar.kumar 144 
    for data in fetchedItems:
145 
        if ( data.transferPrice - data.transferPriceSnapdeal >= -3 ) and (data.transferPrice - data.transferPriceSnapdeal <= 3):
11098 kshitij.so 146 
            print "continue for",data.itemId
10414 kshitij.so 147 
            continue
148 
        filterList.append(data)
149 
    return filterList
10401 amar.kumar 150 
 
151 
def sendMail(filteredData,exceptionList):
152 
    xstr = lambda s: s or ""
153 
    message="""<html>
154 
            <body>
155 
            <h3>Low TP On Snapdeal</h3>
156 
            <table border="1" style="width:100%;">
157 
            <thead>
158 
            <tr><th>Item Id</th>
159 
            <th>Product Name</th>
160 
            <th>Our System Selling Price</th>
161 
            <th>Selling Price Snapdeal</th>
162 
            <th>Our System Transfer Price</th>
163 
            <th>Snapdeal Transfer Price</th>
164 
            <th>Our System Commission</th>
165 
            <th>Snapdeal Commission</th>
166 
            <th>Our System Commission %</th>
167 
            <th>Snapdeal Commission %</th>
168 
            <th>Our System Weight</th>
169 
            <th>Snapdeal Weight</th>
170 
            <th>Our Courier Cost</th>
171 
            <th>Snapdeal Courier Charges</th>
10428 kshitij.so 172 
            <th>Reason</th>
10401 amar.kumar 173 
            </tr></thead>
174 
            <tbody>"""
175 
    for data in filteredData:
176 
        if data.transferPriceSnapdeal < data.transferPrice:
177 
            message+="""<tr>
178 
            <td style="text-align:center">"""+str(data.itemId)+"""</td>
10414 kshitij.so 179 
            <td style="text-align:center">"""+xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color)+"""</td>
10401 amar.kumar 180 
            <td style="text-align:center">"""+str(data.sellingPrice)+"""</td>
181 
            <td style="text-align:center">"""+str(data.sellingPriceSnapdeal)+"""</td>
182 
            <td style="text-align:center">"""+str(data.transferPrice)+"""</td>
183 
            <td style="text-align:center">"""+str(data.transferPriceSnapdeal)+"""</td>
10414 kshitij.so 184 
            <td style="text-align:center">"""+str(round(data.commission*1.1236,2))+"""</td>
10401 amar.kumar 185 
            <td style="text-align:center">"""+str(round(float(data.fixedMargin)+float(data.collectionCharges),2))+"""</td>
186 
            <td style="text-align:center">"""+str(data.commissionPercentage)+"%"+"""</td>
187 
            <td style="text-align:center">"""+str(round(float(data.fixedMarginPercentage)/1.1236,2))+"%"+"""</td>
10414 kshitij.so 188 
            <td style="text-align:center">"""+str(data.weight*1000)+" gms"+"""</td>
10401 amar.kumar 189 
            <td style="text-align:center">"""+str(data.weightSnapdeal)+" gms"+"""</td>
10414 kshitij.so 190 
            <td style="text-align:center">"""+str(round(data.courierCost*1.1236,2))+"""</td>
11098 kshitij.so 191 
            <td style="text-align:center">"""+str(round(data.logisticCostSnapdeal,2)+round(data.woodenPackagingCost,2))+"""</td>
10428 kshitij.so 192 
            <td style="text-align:center">"""+getReason(data)+"""</td>
10401 amar.kumar 193 
            </tr>"""
194 
    message+="""</tbody></table>"""
195 
    message+="""
196 
            <h3>High TP On Snapdeal</h3>
197 
            <table border="1" style="width:100%;">
198 
            <thead>
199 
            <tr><th>Item Id</th>
200 
            <th>Product Name</th>
201 
            <th>Our System Selling Price</th>
202 
            <th>Selling Price Snapdeal</th>
203 
            <th>Our System Transfer Price</th>
204 
            <th>Snapdeal Transfer Price</th>
205 
            <th>Our System Commission</th>
206 
            <th>Snapdeal Commission</th>
207 
            <th>Our System Commission %</th>
208 
            <th>Snapdeal Commission %</th>
209 
            <th>Our System Weight</th>
210 
            <th>Snapdeal Weight</th>
211 
            <th>Our Courier Cost</th>
212 
            <th>Snapdeal Courier Charges</th>
10428 kshitij.so 213 
            <th>Reason</th>
10401 amar.kumar 214 
            </tr></thead>
215 
            <tbody>"""
216 
    for data in filteredData:
217 
        if data.transferPriceSnapdeal >= data.transferPrice:
218 
            message+="""<tr>
219 
            <td style="text-align:center">"""+str(data.itemId)+"""</td>
10414 kshitij.so 220 
            <td style="text-align:center">"""+xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color)+"""</td>
10401 amar.kumar 221 
            <td style="text-align:center">"""+str(data.sellingPrice)+"""</td>
222 
            <td style="text-align:center">"""+str(data.sellingPriceSnapdeal)+"""</td>
223 
            <td style="text-align:center">"""+str(data.transferPrice)+"""</td>
224 
            <td style="text-align:center">"""+str(data.transferPriceSnapdeal)+"""</td>
10414 kshitij.so 225 
            <td style="text-align:center">"""+str(round(data.commission*1.1236,2))+"""</td>
10401 amar.kumar 226 
            <td style="text-align:center">"""+str(round(float(data.fixedMargin)+float(data.collectionCharges),2))+"""</td>
227 
            <td style="text-align:center">"""+str(data.commissionPercentage)+"%"+"""</td>
228 
            <td style="text-align:center">"""+str(round(float(data.fixedMarginPercentage)/1.1236,2))+"%"+"""</td>
10414 kshitij.so 229 
            <td style="text-align:center">"""+str(data.weight*1000)+" gms"+"""</td>
10401 amar.kumar 230 
            <td style="text-align:center">"""+str(data.weightSnapdeal)+" gms"+"""</td>
10414 kshitij.so 231 
            <td style="text-align:center">"""+str(round(data.courierCost*1.1236,2))+"""</td>
11098 kshitij.so 232 
            <td style="text-align:center">"""+str(round(data.logisticCostSnapdeal,2)+round(data.woodenPackagingCost,2))+"""</td>
10428 kshitij.so 233 
            <td style="text-align:center">"""+getReason(data)+"""</td>
10401 amar.kumar 234 
            </tr>"""
235 
    message+="""</tbody></table>"""
236 
    message+="""
11772 kshitij.so 237 
            <h3 style="color:red;font-weight:bold">Please Check</h3>
238 
            <h3>Items not live on Snapdeal</h3>
10401 amar.kumar 239 
            <table border="1" style="width:100%;">
240 
            <thead>
241 
            <tr><th>Item Id</th>
242 
            <th>Product Name</th>
243 
            <th>Our System Selling Price</th>
244 
            <th>Our System Transfer Price</th>
245 
            <th>Our System Commission</th>
246 
            <th>Our System Commission %</th>
247 
            <th>Our System Weight</th>
248 
            <th>Our Courier Cost</th>
249 
            </tr></thead>
250 
            <tbody>"""
251 
    for data in exceptionList:
252 
        snapdealItem = data[0]
253 
        marketplaceItem = data[1]
10414 kshitij.so 254 
        ds_item = data[2]
10401 amar.kumar 255 
        message+="""<tr>
10414 kshitij.so 256 
            <td style="text-align:center">"""+str(ds_item.id)+"""</td>
257 
            <td style="text-align:center">"""+xstr(ds_item.brand)+" "+xstr(ds_item.model_name)+" "+xstr(ds_item.model_number)+" "+xstr(ds_item.color)+"""</td>
10401 amar.kumar 258 
            <td style="text-align:center">"""+str(snapdealItem.sellingPrice)+"""</td>
259 
            <td style="text-align:center">"""+str(snapdealItem.transferPrice)+"""</td>
10419 kshitij.so 260 
            <td style="text-align:center">"""+str(round(snapdealItem.commission*1.1236,2))+"""</td>
10414 kshitij.so 261 
            <td style="text-align:center">"""+str(marketplaceItem.commission)+"%"+"""</td>
262 
            <td style="text-align:center">"""+str(ds_item.weight*1000)+" gms"+"""</td>
10419 kshitij.so 263 
            <td style="text-align:center">"""+str(round(snapdealItem.courierCost*1.1236,2))+"""</td>
10401 amar.kumar 264 
            </tr>"""
265 
    message+="""</tbody></table></body></html>"""
266 
    print message
267 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
268 
    mailServer.ehlo()
269 
    mailServer.starttls()
270 
    mailServer.ehlo()
271 
 
10434 kshitij.so 272 
    #recipients = ['kshitij.sood@saholic.com']
11117 kshitij.so 273 
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com','sandeep.sachdeva@saholic.com']
10401 amar.kumar 274 
    msg = MIMEMultipart()
275 
    msg['Subject'] = "Snapdeal TP Reconciliation" + ' - ' + str(datetime.now())
276 
    msg['From'] = ""
277 
    msg['To'] = ",".join(recipients)
278 
    msg.preamble = "Snapdeal TP Reconciliation" + ' - ' + str(datetime.now())
279 
    html_msg = MIMEText(message, 'html')
280 
    msg.attach(html_msg)
281 
    try:
282 
        mailServer.login("build@shop2020.in", "cafe@nes")
283 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
284 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
285 
    except Exception as e:
286 
        print e
287 
        print "Unable to send Snapdeal TP Reconciliation mail.Lets try with local SMTP."
288 
        smtpServer = smtplib.SMTP('localhost')
289 
        smtpServer.set_debuglevel(1)
290 
        sender = 'support@shop2020.in'
10420 kshitij.so 291 
        try:
292 
            smtpServer.sendmail(sender, recipients, msg.as_string())
293 
            print "Successfully sent email"
294 
        except:
295 
            print "Error: unable to send email."
10401 amar.kumar 296 
 
10414 kshitij.so 297 
def write_report(filteredData,exceptionList):
298 
    wbk = xlwt.Workbook()
299 
    sheet = wbk.add_sheet('Low TP SD')
300 
    xstr = lambda s: s or ""
301 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
302 
 
303 
    excel_integer_format = '0'
304 
    integer_style = xlwt.XFStyle()
305 
    integer_style.num_format_str = excel_integer_format
306 
 
307 
    sheet.write(0, 0, "Item ID", heading_xf)
308 
    sheet.write(0, 1, "Category", heading_xf)
309 
    sheet.write(0, 2, "Product Group.", heading_xf)
310 
    sheet.write(0, 3, "SUPC", heading_xf)
311 
    sheet.write(0, 4, "Brand", heading_xf)
312 
    sheet.write(0, 5, "Product Name", heading_xf)
313 
    sheet.write(0, 6, "Our System SP", heading_xf)
314 
    sheet.write(0, 7, "Snapdeal SP", heading_xf)
315 
    sheet.write(0, 8, "Our TP", heading_xf)
316 
    sheet.write(0, 9, "Snapdeal TP", heading_xf)
317 
    sheet.write(0, 10, "Our System Commission", heading_xf)
318 
    sheet.write(0, 11, "Snapdeal Commission", heading_xf)
319 
    sheet.write(0, 12, "Our System Commission %", heading_xf)
320 
    sheet.write(0, 13, "Snapdeal Commission %", heading_xf)
321 
    sheet.write(0, 14, "Our System Weight (gms)", heading_xf)
322 
    sheet.write(0, 15, "Snapdeal Weight", heading_xf)
323 
    sheet.write(0, 16, "Our Courier Cost", heading_xf)
324 
    sheet.write(0, 17, "Snapdeal Courier Cost", heading_xf)
10428 kshitij.so 325 
    sheet.write(0, 18, "Reason", heading_xf)
10414 kshitij.so 326 
 
327 
    sheet_iterator=1
328 
    for data in filteredData:
329 
        if data.transferPriceSnapdeal < data.transferPrice:
330 
            sheet.write(sheet_iterator, 0, data.itemId)
331 
            sheet.write(sheet_iterator, 1, data.parentCategory)
10420 kshitij.so 332 
            sheet.write(sheet_iterator, 2, data.productGroup)
333 
            sheet.write(sheet_iterator, 3, data.supc)
10414 kshitij.so 334 
            sheet.write(sheet_iterator, 4, data.brand)
335 
            sheet.write(sheet_iterator, 5, xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color))
336 
            sheet.write(sheet_iterator, 6, data.sellingPrice)
337 
            sheet.write(sheet_iterator, 7, data.sellingPriceSnapdeal)
338 
            sheet.write(sheet_iterator, 8, data.transferPrice)
339 
            sheet.write(sheet_iterator, 9, data.transferPriceSnapdeal)
340 
            sheet.write(sheet_iterator, 10, round(data.commission*1.1236,2))
341 
            sheet.write(sheet_iterator, 11, round(float(data.fixedMargin)+float(data.collectionCharges),2))
342 
            sheet.write(sheet_iterator, 12, data.commissionPercentage)
343 
            sheet.write(sheet_iterator, 13, round(float(data.fixedMarginPercentage)/1.1236,2))
344 
            sheet.write(sheet_iterator, 14, data.weight*1000)
345 
            sheet.write(sheet_iterator, 15, data.weightSnapdeal)
346 
            sheet.write(sheet_iterator, 16, round(data.courierCost*1.1236,2))
11098 kshitij.so 347 
            sheet.write(sheet_iterator, 17, round(data.logisticCostSnapdeal,2)+round(data.woodenPackagingCost,2))
348 
            sheet.write(sheet_iterator, 18, getReasonSheet(data))
10414 kshitij.so 349 
            sheet_iterator+=1
350 
 
351 
    sheet = wbk.add_sheet('High TP SD')
352 
 
353 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
354 
 
355 
    excel_integer_format = '0'
356 
    integer_style = xlwt.XFStyle()
357 
    integer_style.num_format_str = excel_integer_format
358 
    xstr = lambda s: s or ""
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, "Product Name", heading_xf)
366 
    sheet.write(0, 6, "Our System SP", heading_xf)
367 
    sheet.write(0, 7, "Snapdeal SP", heading_xf)
368 
    sheet.write(0, 8, "Our TP", heading_xf)
369 
    sheet.write(0, 9, "Snapdeal TP", heading_xf)
370 
    sheet.write(0, 10, "Our System Commission", heading_xf)
371 
    sheet.write(0, 11, "Snapdeal Commission", heading_xf)
372 
    sheet.write(0, 12, "Our System Commission %", heading_xf)
373 
    sheet.write(0, 13, "Snapdeal Commission %", heading_xf)
374 
    sheet.write(0, 14, "Our System Weight (gms)", heading_xf)
375 
    sheet.write(0, 15, "Snapdeal Weight", heading_xf)
376 
    sheet.write(0, 16, "Our Courier Cost", heading_xf)
377 
    sheet.write(0, 17, "Snapdeal Courier Cost", heading_xf)
10428 kshitij.so 378 
    sheet.write(0, 18, "Reason", heading_xf)
10414 kshitij.so 379 
 
380 
    sheet_iterator=1
381 
    for data in filteredData:
382 
        if data.transferPriceSnapdeal > data.transferPrice:
383 
            sheet.write(sheet_iterator, 0, data.itemId)
384 
            sheet.write(sheet_iterator, 1, data.parentCategory)
10420 kshitij.so 385 
            sheet.write(sheet_iterator, 2, data.productGroup)
386 
            sheet.write(sheet_iterator, 3, data.supc)
10414 kshitij.so 387 
            sheet.write(sheet_iterator, 4, data.brand)
388 
            sheet.write(sheet_iterator, 5, xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color))
389 
            sheet.write(sheet_iterator, 6, data.sellingPrice)
390 
            sheet.write(sheet_iterator, 7, data.sellingPriceSnapdeal)
391 
            sheet.write(sheet_iterator, 8, data.transferPrice)
392 
            sheet.write(sheet_iterator, 9, data.transferPriceSnapdeal)
393 
            sheet.write(sheet_iterator, 10, round(data.commission*1.1236,2))
394 
            sheet.write(sheet_iterator, 11, round(float(data.fixedMargin)+float(data.collectionCharges),2))
395 
            sheet.write(sheet_iterator, 12, data.commissionPercentage)
396 
            sheet.write(sheet_iterator, 13, round(float(data.fixedMarginPercentage)/1.1236,2))
397 
            sheet.write(sheet_iterator, 14, data.weight*1000)
398 
            sheet.write(sheet_iterator, 15, data.weightSnapdeal)
399 
            sheet.write(sheet_iterator, 16, round(data.courierCost*1.1236,2))
11098 kshitij.so 400 
            sheet.write(sheet_iterator, 17, round(data.logisticCostSnapdeal,2)+round(data.woodenPackagingCost,2))
401 
            sheet.write(sheet_iterator, 18, getReasonSheet(data))
10414 kshitij.so 402 
            sheet_iterator+=1
403 
 
404 
    sheet = wbk.add_sheet('Exceptions')
405 
 
406 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
407 
 
408 
    excel_integer_format = '0'
409 
    integer_style = xlwt.XFStyle()
410 
    integer_style.num_format_str = excel_integer_format
411 
    xstr = lambda s: s or ""
412 
 
413 
    sheet.write(0, 0, "Item ID", heading_xf)
414 
    sheet.write(0, 1, "SUPC", heading_xf)
415 
    sheet.write(0, 2, "Brand", heading_xf)
416 
    sheet.write(0, 3, "Product Name", heading_xf)
417 
    sheet.write(0, 4, "Our System SP", heading_xf)
418 
    sheet.write(0, 5, "Our TP", heading_xf)
419 
    sheet.write(0, 6, "Our System Commission", heading_xf)
420 
    sheet.write(0, 7, "Our System Commission %", heading_xf)
421 
    sheet.write(0, 8, "Our System Weight (gms)", heading_xf)
422 
    sheet.write(0, 9, "Our Courier Cost", heading_xf)
423 
 
424 
    sheet_iterator=1
425 
    for data in exceptionList:
426 
        snapdealItem = data[0]
427 
        marketplaceItem = data[1]
428 
        ds_item = data[2]
429 
        sheet.write(sheet_iterator, 0, ds_item.id)
430 
        sheet.write(sheet_iterator, 1, snapdealItem.supc)
431 
        sheet.write(sheet_iterator, 2, ds_item.brand)
10415 kshitij.so 432 
        sheet.write(sheet_iterator, 3, xstr(ds_item.brand)+" "+xstr(ds_item.model_name)+" "+xstr(ds_item.model_number)+" "+xstr(ds_item.color))
10414 kshitij.so 433 
        sheet.write(sheet_iterator, 4, snapdealItem.sellingPrice)
434 
        sheet.write(sheet_iterator, 5, snapdealItem.transferPrice)
435 
        sheet.write(sheet_iterator, 6, round(snapdealItem.commission*1.1236,2))
436 
        sheet.write(sheet_iterator, 7, marketplaceItem.commission)
437 
        sheet.write(sheet_iterator, 8, (ds_item.weight*1000))
10417 kshitij.so 438 
        sheet.write(sheet_iterator, 9, round(snapdealItem.courierCost*1.1236,2))
10419 kshitij.so 439 
        sheet_iterator+=1
10414 kshitij.so 440 
 
441 
    filename = "/tmp/snapdeal-tp-reconciliation-" + str(datetime.now()) + ".xls"
442 
    wbk.save(filename)
443 
 
444 
    try:
10434 kshitij.so 445 
        #EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Snapdeal TP Reconciliation "+ str(datetime.now()), "", [get_attachment_part(filename)], [""], [])
446 
        EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["chandan.kumar@saholic.com","manoj.kumar@saholic.com","yukti.jain@saholic.com","ankush.dhingra@saholic.com","manoj.pal@saholic.com","sandeep.sachdeva@saholic.com"], " Snapdeal TP Reconciliation "+ str(datetime.now()), "", [get_attachment_part(filename)], ["rajneesh.arora@saholic.com","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
10414 kshitij.so 447 
    except Exception as e:
448 
        print e
449 
        print "Unable to send report.Trying with local SMTP"
450 
        smtpServer = smtplib.SMTP('localhost')
451 
        smtpServer.set_debuglevel(1)
452 
        sender = 'support@shop2020.in'
11772 kshitij.so 453 
        #recipients = ['kshitij.sood@saholic.com']
10414 kshitij.so 454 
        msg = MIMEMultipart()
455 
        msg['Subject'] = "Snapdeal TP Reconciliation" + ' - ' + str(datetime.now())
456 
        msg['From'] = sender
11772 kshitij.so 457 
        recipients = ['rajneesh.arora@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com','sandeep.sachdeva@saholic.com']
10414 kshitij.so 458 
        msg['To'] = ",".join(recipients)
459 
        fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
460 
        fileMsg.set_payload(file(filename).read())
461 
        email.encoders.encode_base64(fileMsg)
10419 kshitij.so 462 
        fileMsg.add_header('Content-Disposition','attachment;filename=snapdeal_tp_recon.xls')
10414 kshitij.so 463 
        msg.attach(fileMsg)
464 
        try:
465 
            smtpServer.sendmail(sender, recipients, msg.as_string())
466 
            print "Successfully sent email"
467 
        except:
468 
            print "Error: unable to send email."
10428 kshitij.so 469 
 
470 
def getReason(data):
11098 kshitij.so 471 
    global courierCostToSync
10428 kshitij.so 472 
    reason=""
473 
    if data.sellingPrice!=data.sellingPriceSnapdeal:
474 
        reason+="Selling Price is different."
10432 kshitij.so 475 
    if data.commissionPercentage!= round(float(data.fixedMarginPercentage)/1.1236,2):
10430 kshitij.so 476 
        reason+="Commission is different."
11098 kshitij.so 477 
    if round(data.courierCost*1.1236)!=round(data.logisticCostSnapdeal+data.woodenPackagingCost):
10428 kshitij.so 478 
        reason+="Courier Cost is different-Check Weight."
11098 kshitij.so 479 
        courierCostToSync.append(data)
10428 kshitij.so 480 
    return reason
11098 kshitij.so 481 
 
482 
def getReasonSheet(data):
483 
    reason=""
484 
    if data.sellingPrice!=data.sellingPriceSnapdeal:
485 
        reason+="Selling Price is different."
486 
    if data.commissionPercentage!= round(float(data.fixedMarginPercentage)/1.1236,2):
487 
        reason+="Commission is different."
488 
    if round(data.courierCost*1.1236)!=round(data.logisticCostSnapdeal+data.woodenPackagingCost):
489 
        reason+="Courier Cost is different-Check Weight."
490 
    return reason
491 
 
492 
def syncCourierCost(courierCostToSync):
493 
    global oldPricing
494 
    for item in courierCostToSync:
495 
        sdItem = SnapdealItem.get_by(item_id=item.itemId)
496 
        mpItem = MarketplaceItems.get_by(itemId=item.itemId, source=OrderSource.SNAPDEAL)
497 
        oldMpItem = copy.deepcopy(mpItem)
498 
        temp = []
499 
        temp.append(item)
500 
        temp.append(oldMpItem)
501 
        addHistory(sdItem)
502 
        sdItem.courierCostMarketplace = round((item.logisticCostSnapdeal+item.woodenPackagingCost)/1.1236)
503 
        sdItem.transferPrice = getNewTp(mpItem,item)
504 
        sdItem.serviceTax = getNewServiceTax(mpItem,sdItem,item)
505 
        mpItem.courierCostMarketplace = round((item.logisticCostSnapdeal+item.woodenPackagingCost)/1.1236)
506 
        mpItem.currentTp = getNewTp(mpItem,item)
507 
        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,sdItem,item)
508 
        temp.append(mpItem)
509 
        oldPricing.append(temp)
510 
    session.commit()
511 
 
512 
def addHistory(item):
513 
    itemHistory = MarketPlaceUpdateHistory()
514 
    itemHistory.item_id = item.item_id
515 
    itemHistory.source = OrderSource.SNAPDEAL
516 
    itemHistory.exceptionPrice = item.exceptionPrice
517 
    itemHistory.warehouseId = item.warehouseId
518 
    itemHistory.isListedOnSource = item.isListedOnSnapdeal
519 
    itemHistory.transferPrice = item.transferPrice
520 
    itemHistory.sellingPrice = item.sellingPrice
521 
    itemHistory.courierCost = item.courierCost
522 
    itemHistory.commission = item.commission
523 
    itemHistory.serviceTax = item.serviceTax
524 
    itemHistory.suppressPriceFeed = item.suppressPriceFeed
525 
    itemHistory.suppressInventoryFeed = item.suppressInventoryFeed
526 
    itemHistory.updatedOn = item.updatedOn
527 
    itemHistory.maxNlc = item.maxNlc
528 
    itemHistory.skuAtSource = item.skuAtSnapdeal
529 
    itemHistory.marketPlaceSerialNumber = item.supc
530 
    itemHistory.priceUpdatedBy = item.priceUpdatedBy
531 
    itemHistory.courierCostMarketplace = item.courierCostMarketplace
10414 kshitij.so 532 
 
533 
 
11098 kshitij.so 534 
 
535 
def getNewTp(mpItem,data):
536 
    ourTp = mpItem.currentSp- mpItem.currentSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-((data.logisticCostSnapdeal+data.woodenPackagingCost)/1.1236+mpItem.closingFee)*(1+(mpItem.serviceTax/100))-(max(20,(mpItem.pgFee/100)*mpItem.currentSp)*(1+(mpItem.serviceTax/100)));
537 
    return round(ourTp,2)
538 
 
539 
def getNewServiceTax(mpItem,sdItem,data):
540 
    return round(mpItem.serviceTax/100*(sdItem.commission+(data.logisticCostSnapdeal+data.woodenPackagingCost)/1.1236),2)
541 
 
542 
def getNewLowestPossibleSp(mpItem,sdItem,data):
543 
    if (mpItem.pgFee/100)*mpItem.currentSp>=20:
12146 kshitij.so 544 
        lowestPossibleSp = (sdItem.maxNlc+(((data.logisticCostSnapdeal+data.woodenPackagingCost)/1.1236)+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(mpItem.vat/100))+(mpItem.packagingCost+mpItem.otherCost)*(1+(mpItem.vat)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100+mpItem.pgFee/100)*(1+(mpItem.serviceTax/100))*(1+(mpItem.vat)/100)-(mpItem.returnProvision/100)*(1+(mpItem.vat)/100));
11098 kshitij.so 545 
    else:
12146 kshitij.so 546 
        lowestPossibleSp = (sdItem.maxNlc+(((data.logisticCostSnapdeal+data.woodenPackagingCost)/1.1236)+mpItem.closingFee+20)*(1+(mpItem.serviceTax/100))*(1+(mpItem.vat/100))+(mpItem.packagingCost+mpItem.otherCost)*(1+(mpItem.vat)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(mpItem.vat)/100)-(mpItem.returnProvision/100)*(1+(mpItem.vat)/100));
11098 kshitij.so 547 
    return round(lowestPossibleSp,2)
548 
 
549 
def sendCCSyncMail():
550 
    if len(courierCostToSync) == 0:
551 
        return
552 
    xstr = lambda s: s or ""
553 
    message="""<html>
554 
            <body>
555 
            <h3>Courier cost synced</h3>
556 
            <table border="1" style="width:100%;">
557 
            <thead>
558 
            <tr><th>Item Id</th>
559 
            <th>Product Name</th>
560 
            <th>Selling Price</th>
561 
            <th>Old Transfer Price</th>
562 
            <th>New Transfer Price</th>
563 
            <th>Old Courier Cost</th>
564 
            <th>New Courier Cost</th>
565 
            <th>Old Margin</th>
566 
            <th>New Margin</th>
567 
            <th>Old Margin %</th>
568 
            <th>New Margin %</th>
569 
            </tr></thead>
570 
            <tbody>"""
571 
    for value in oldPricing:
572 
        data = value[0]
573 
        mpItemOld = value[1]
574 
        mpItemNew = value[2]
575 
        message+="""<tr>
576 
        <td style="text-align:center">"""+str(data.itemId)+"""</td>
577 
        <td style="text-align:center">"""+xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color)+"""</td>
578 
        <td style="text-align:center">"""+str(mpItemOld.currentSp)+"""</td>
579 
        <td style="text-align:center">"""+str(mpItemOld.currentTp)+"""</td>
580 
        <td style="text-align:center">"""+str(mpItemNew.currentTp)+"""</td>
581 
        <td style="text-align:center">"""+str(mpItemOld.courierCostMarketplace)+"""</td>
582 
        <td style="text-align:center">"""+str(mpItemNew.courierCostMarketplace)+"""</td>
583 
        <td style="text-align:center">"""+str(round(mpItemOld.currentTp-mpItemOld.minimumPossibleTp))+"""</td>
584 
        <td style="text-align:center">"""+str(round(mpItemNew.currentTp-mpItemNew.minimumPossibleTp))+"""</td>
585 
        <td style="text-align:center">"""+str(round(((mpItemOld.currentTp-mpItemOld.minimumPossibleTp)/mpItemOld.currentSp)*100,2))+"""</td>
586 
        <td style="text-align:center">"""+str(round(((mpItemNew.currentTp-mpItemNew.minimumPossibleTp)/mpItemNew.currentSp)*100,2))+"""</td>
587 
        </tr>"""
588 
    message+="""</tbody></table></body></html>"""
589 
    print message
590 
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
591 
    mailServer.ehlo()
592 
    mailServer.starttls()
593 
    mailServer.ehlo()
594 
 
595 
    #recipients = ['kshitij.sood@saholic.com']
11117 kshitij.so 596 
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com','sandeep.sachdeva@saholic.com']
11098 kshitij.so 597 
    msg = MIMEMultipart()
598 
    msg['Subject'] = "Snapdeal Courier Cost Synced" + ' - ' + str(datetime.now())
599 
    msg['From'] = ""
600 
    msg['To'] = ",".join(recipients)
601 
    msg.preamble = "Snapdeal Courier Cost Synced" + ' - ' + str(datetime.now())
602 
    html_msg = MIMEText(message, 'html')
603 
    msg.attach(html_msg)
604 
    try:
605 
        mailServer.login("build@shop2020.in", "cafe@nes")
606 
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
607 
        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
608 
    except Exception as e:
609 
        print e
610 
        print "Unable to send Snapdeal Courier Cost mail.Lets try with local SMTP."
611 
        smtpServer = smtplib.SMTP('localhost')
612 
        smtpServer.set_debuglevel(1)
613 
        sender = 'support@shop2020.in'
614 
        try:
615 
            smtpServer.sendmail(sender, recipients, msg.as_string())
616 
            print "Successfully sent email"
617 
        except:
618 
            print "Error: unable to send email."
619 
 
10401 amar.kumar 620 
def main():
621 
    print "Opening snapdeal seller login page"
622 
    br = login("http://selleraccounts.snapdeal.com/")
623 
    exceptionList, fetchedItems = populateStuff(br)
624 
    filteredData = filterData(fetchedItems)
625 
    sendMail(filteredData,exceptionList)
10414 kshitij.so 626 
    write_report(filteredData,exceptionList)
11098 kshitij.so 627 
    syncCourierCost(courierCostToSync)
628 
    sendCCSyncMail()
10401 amar.kumar 629 
 
630 
 
631 
if __name__ == "__main__":
632 
    main()
633 
 
634