Subversion Repositories SmartDukaan

Rev

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