Subversion Repositories SmartDukaan

Rev

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