Subversion Repositories SmartDukaan

Rev

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

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