Subversion Repositories SmartDukaan

Rev

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