Subversion Repositories SmartDukaan

Rev

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