Subversion Repositories SmartDukaan

from elixir import *

from shop2020.config.client.ConfigClient import ConfigClient

from shop2020.model.v1.catalog.impl import DataService

from shop2020.model.v1.catalog.impl.DataService import SnapdealItem, MarketplaceItems, Item, Category, \

MarketPlaceUpdateHistory

from shop2020.thriftpy.model.v1.order.ttypes import OrderSource

import mechanize

import sys

import cookielib

from time import sleep

import json

import smtplib

import xlwt

from datetime import datetime

from shop2020.utils import EmailAttachmentSender

from shop2020.utils.EmailAttachmentSender import get_attachment_part

from email.mime.text import MIMEText

import email

from email.mime.multipart import MIMEMultipart

import email.encoders

import copy

config_client = ConfigClient()

host = config_client.get_property('staging_hostname')

DataService.initialize(db_hostname=host)

courierCostToSync = []

oldPricing = []

class __SnapdealInfo:

    def __init__(self, sellingPrice, weight, transferPrice, commission, commissionPercentage, courierCost, sellingPriceSnapdeal, transferPriceSnapdeal, fixedMargin, \

                 fixedMarginPercentage, collectionCharges, logisticCostSnapdeal, weightSnapdeal, supc, itemId, parentCategory, productGroup ,brand, modelName, modelNumber, \

                 color,reason,woodenPackagingCost):

        self.sellingPrice = sellingPrice

        self.weight = weight

        self.transferPrice = transferPrice

        self.commission = commission

        self.commissionPercentage = commissionPercentage

        self.courierCost = courierCost

        self.sellingPriceSnapdeal = sellingPriceSnapdeal

        self.transferPriceSnapdeal = transferPriceSnapdeal

        self.fixedMargin = fixedMargin

        self.fixedMarginPercentage = fixedMarginPercentage

        self.collectionCharges = collectionCharges

        self.logisticCostSnapdeal = logisticCostSnapdeal

        self.weightSnapdeal = weightSnapdeal

        self.supc = supc

        self.itemId = itemId

        self.parentCategory = parentCategory

        self.productGroup = productGroup

        self.brand = brand

        self.modelName = modelName

        self.modelNumber = modelNumber

        self.color = color

        self.reason = reason 

        self.woodenPackagingCost = woodenPackagingCost

def getBrowserObject():

    br = mechanize.Browser(factory=mechanize.RobustFactory())

    cj = cookielib.LWPCookieJar()

    br.set_cookiejar(cj)

    br.set_handle_equiv(True)

    br.set_handle_redirect(True)

    br.set_handle_referer(True)

    br.set_handle_robots(False)

    br.set_debug_http(False)

    br.set_debug_redirects(False)

    br.set_debug_responses(False)

    br.set_handle_refresh(mechanize._http.HTTPRefreshProcessor(), max_time=1)

    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'),

                     ('Accept', 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8'),

                     ('Accept-Encoding', 'gzip,deflate,sdch'),                  

                     ('Accept-Language', 'en-US,en;q=0.8'),                     

                     ('Accept-Charset', 'ISO-8859-1,utf-8;q=0.7,*;q=0.3')]

    return br

def login(url):

    br = getBrowserObject()

    br.open(url)

    response = br.open(url)

    br.select_form(nr=0)

    br.form['username'] = "saholic-snapdeal@saholic.com"

    br.form['password'] = "snapsaholic"

    print "Trying to login"

    response = br.submit()

    return br

def populateStuff(br):

    exceptionList = []

    fetchedItems = []

    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()

    #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()

    for item in items:

        snapdealItem = item[0]

        marketplaceItem = item[1]

        ds_item = item[2] 

        category = Category.query.filter_by(id=ds_item.category).one()

        parent_category = Category.query.filter_by(id=category.parent_category_id).first()

        try:

            snapdealInfo = fetchData(br,snapdealItem.supc)

        except Exception as e:

            exceptionList.append(item)

            print "Unable to fetch details ",e

            continue

        snapdealInfo.sellingPrice = snapdealItem.sellingPrice

        snapdealInfo.courierCost = snapdealItem.courierCostMarketplace

        snapdealInfo.transferPrice = snapdealItem.transferPrice

        snapdealInfo.commissionPercentage = marketplaceItem.commission

        snapdealInfo.commission = snapdealItem.commission

        snapdealInfo.itemId = snapdealItem.item_id

        snapdealInfo.parentCategory = parent_category.display_name

        snapdealInfo.productGroup = ds_item.product_group

        snapdealInfo.brand = ds_item.brand

        snapdealInfo.modelName = ds_item.model_name

        snapdealInfo.modelNumber = ds_item.model_number

        snapdealInfo.color = ds_item.color

        snapdealInfo.weight = ds_item.weight

        fetchedItems.append(snapdealInfo)

    return exceptionList, fetchedItems

def fetchData(br,supc):

    print "Fetching data for ",supc

    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)

    response = br.open(url)

    #dataform = str(response.read()).strip("'<>() ").replace('\'', '\"')

    struct = json.loads(response.read())

    sdObj = struct['rows'][0]

    print sdObj

    if type(sdObj['catalogLive']) is None or not (sdObj['catalogLive']):

        raise

    snapdealInfo = __SnapdealInfo(None,None,None,None,None,None,sdObj['sellingPrice'],sdObj['netSellerPayable'],sdObj['fixedMarginAmount'],sdObj['fixedMarginPercent'],sdObj['collectionCharges'],sdObj['logisticCost'],sdObj['deadWeight'],supc,None, \

                    None, None, None, None, None, None, None,sdObj['woodenPackagingCost'])

    return snapdealInfo

def filterData(fetchedItems):

    filterList = []

    for data in fetchedItems:

        if ( data.transferPrice - data.transferPriceSnapdeal >= -3 ) and (data.transferPrice - data.transferPriceSnapdeal <= 3):

            print "continue for",data.itemId

            continue

        filterList.append(data)

    return filterList

def sendMail(filteredData,exceptionList):

    xstr = lambda s: s or ""

    message="""<html>

            <body>

            <h3>Low TP On Snapdeal</h3>

            <table border="1" style="width:100%;">

            <thead>

            <tr><th>Item Id</th>

            <th>Product Name</th>

            <th>Our System Selling Price</th>

            <th>Selling Price Snapdeal</th>

            <th>Our System Transfer Price</th>

            <th>Snapdeal Transfer Price</th>

            <th>Our System Commission</th>

            <th>Snapdeal Commission</th>

            <th>Our System Commission %</th>

            <th>Snapdeal Commission %</th>

            <th>Our System Weight</th>

            <th>Snapdeal Weight</th>

            <th>Our Courier Cost</th>

            <th>Snapdeal Courier Charges</th>

            <th>Reason</th>

            </tr></thead>

            <tbody>"""

    for data in filteredData:

        if data.transferPriceSnapdeal < data.transferPrice:

            message+="""<tr>

            <td style="text-align:center">"""+str(data.itemId)+"""</td>

            <td style="text-align:center">"""+xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color)+"""</td>

            <td style="text-align:center">"""+str(data.sellingPrice)+"""</td>

            <td style="text-align:center">"""+str(data.sellingPriceSnapdeal)+"""</td>

            <td style="text-align:center">"""+str(data.transferPrice)+"""</td>

            <td style="text-align:center">"""+str(data.transferPriceSnapdeal)+"""</td>

            <td style="text-align:center">"""+str(round(data.commission*1.1236,2))+"""</td>

            <td style="text-align:center">"""+str(round(float(data.fixedMargin)+float(data.collectionCharges),2))+"""</td>

            <td style="text-align:center">"""+str(data.commissionPercentage)+"%"+"""</td>

            <td style="text-align:center">"""+str(round(float(data.fixedMarginPercentage)/1.1236,2))+"%"+"""</td>

            <td style="text-align:center">"""+str(data.weight*1000)+" gms"+"""</td>

            <td style="text-align:center">"""+str(data.weightSnapdeal)+" gms"+"""</td>

            <td style="text-align:center">"""+str(round(data.courierCost*1.1236,2))+"""</td>

            <td style="text-align:center">"""+str(round(data.logisticCostSnapdeal,2)+round(data.woodenPackagingCost,2))+"""</td>

            <td style="text-align:center">"""+getReason(data)+"""</td>

            </tr>"""

    message+="""</tbody></table>"""

    message+="""

            <h3>High TP On Snapdeal</h3>

            <table border="1" style="width:100%;">

            <thead>

            <tr><th>Item Id</th>

            <th>Product Name</th>

            <th>Our System Selling Price</th>

            <th>Selling Price Snapdeal</th>

            <th>Our System Transfer Price</th>

            <th>Snapdeal Transfer Price</th>

            <th>Our System Commission</th>

            <th>Snapdeal Commission</th>

            <th>Our System Commission %</th>

            <th>Snapdeal Commission %</th>

            <th>Our System Weight</th>

            <th>Snapdeal Weight</th>

            <th>Our Courier Cost</th>

            <th>Snapdeal Courier Charges</th>

            <th>Reason</th>

            </tr></thead>

            <tbody>"""

    for data in filteredData:

        if data.transferPriceSnapdeal >= data.transferPrice:

            message+="""<tr>

            <td style="text-align:center">"""+str(data.itemId)+"""</td>

            <td style="text-align:center">"""+xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color)+"""</td>

            <td style="text-align:center">"""+str(data.sellingPrice)+"""</td>

            <td style="text-align:center">"""+str(data.sellingPriceSnapdeal)+"""</td>

            <td style="text-align:center">"""+str(data.transferPrice)+"""</td>

            <td style="text-align:center">"""+str(data.transferPriceSnapdeal)+"""</td>

            <td style="text-align:center">"""+str(round(data.commission*1.1236,2))+"""</td>

            <td style="text-align:center">"""+str(round(float(data.fixedMargin)+float(data.collectionCharges),2))+"""</td>

            <td style="text-align:center">"""+str(data.commissionPercentage)+"%"+"""</td>

            <td style="text-align:center">"""+str(round(float(data.fixedMarginPercentage)/1.1236,2))+"%"+"""</td>

            <td style="text-align:center">"""+str(data.weight*1000)+" gms"+"""</td>

            <td style="text-align:center">"""+str(data.weightSnapdeal)+" gms"+"""</td>

            <td style="text-align:center">"""+str(round(data.courierCost*1.1236,2))+"""</td>

            <td style="text-align:center">"""+str(round(data.logisticCostSnapdeal,2)+round(data.woodenPackagingCost,2))+"""</td>

            <td style="text-align:center">"""+getReason(data)+"""</td>

            </tr>"""

    message+="""</tbody></table>"""

    message+="""

            <h3 style="color:red;font-weight:bold">Please Check</h3>

            <h3>Items not live on Snapdeal</h3>

            <table border="1" style="width:100%;">

            <thead>

            <tr><th>Item Id</th>

            <th>Product Name</th>

            <th>Our System Selling Price</th>

            <th>Our System Transfer Price</th>

            <th>Our System Commission</th>

            <th>Our System Commission %</th>

            <th>Our System Weight</th>

            <th>Our Courier Cost</th>

            </tr></thead>

            <tbody>"""

    for data in exceptionList:

        snapdealItem = data[0]

        marketplaceItem = data[1]

        ds_item = data[2]

        message+="""<tr>

            <td style="text-align:center">"""+str(ds_item.id)+"""</td>

            <td style="text-align:center">"""+xstr(ds_item.brand)+" "+xstr(ds_item.model_name)+" "+xstr(ds_item.model_number)+" "+xstr(ds_item.color)+"""</td>

            <td style="text-align:center">"""+str(snapdealItem.sellingPrice)+"""</td>

            <td style="text-align:center">"""+str(snapdealItem.transferPrice)+"""</td>

            <td style="text-align:center">"""+str(round(snapdealItem.commission*1.1236,2))+"""</td>

            <td style="text-align:center">"""+str(marketplaceItem.commission)+"%"+"""</td>

            <td style="text-align:center">"""+str(ds_item.weight*1000)+" gms"+"""</td>

            <td style="text-align:center">"""+str(round(snapdealItem.courierCost*1.1236,2))+"""</td>

            </tr>"""

    message+="""</tbody></table></body></html>"""

    print message

    mailServer = smtplib.SMTP("smtp.gmail.com", 587)

    mailServer.ehlo()

    mailServer.starttls()

    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']

    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']

    msg = MIMEMultipart()

    msg['Subject'] = "Snapdeal TP Reconciliation" + ' - ' + str(datetime.now())

    msg['From'] = ""

    msg['To'] = ",".join(recipients)

    msg.preamble = "Snapdeal TP Reconciliation" + ' - ' + str(datetime.now())

    html_msg = MIMEText(message, 'html')

    msg.attach(html_msg)

    try:

        mailServer.login("build@shop2020.in", "cafe@nes")

        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())

        mailServer.sendmail("cafe@nes", recipients, msg.as_string())

    except Exception as e:

        print e

        print "Unable to send Snapdeal TP Reconciliation mail.Lets try with local SMTP."

        smtpServer = smtplib.SMTP('localhost')

        smtpServer.set_debuglevel(1)

        sender = 'support@shop2020.in'

        try:

            smtpServer.sendmail(sender, recipients, msg.as_string())

            print "Successfully sent email"

        except:

            print "Error: unable to send email."

def write_report(filteredData,exceptionList):

    wbk = xlwt.Workbook()

    sheet = wbk.add_sheet('Low TP SD')

    xstr = lambda s: s or ""

    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')

    excel_integer_format = '0'

    integer_style = xlwt.XFStyle()

    integer_style.num_format_str = excel_integer_format

    sheet.write(0, 0, "Item ID", heading_xf)

    sheet.write(0, 1, "Category", heading_xf)

    sheet.write(0, 2, "Product Group.", heading_xf)

    sheet.write(0, 3, "SUPC", heading_xf)

    sheet.write(0, 4, "Brand", heading_xf)

    sheet.write(0, 5, "Product Name", heading_xf)

    sheet.write(0, 6, "Our System SP", heading_xf)

    sheet.write(0, 7, "Snapdeal SP", heading_xf)

    sheet.write(0, 8, "Our TP", heading_xf)

    sheet.write(0, 9, "Snapdeal TP", heading_xf)

    sheet.write(0, 10, "Our System Commission", heading_xf)

    sheet.write(0, 11, "Snapdeal Commission", heading_xf)

    sheet.write(0, 12, "Our System Commission %", heading_xf)

    sheet.write(0, 13, "Snapdeal Commission %", heading_xf)

    sheet.write(0, 14, "Our System Weight (gms)", heading_xf)

    sheet.write(0, 15, "Snapdeal Weight", heading_xf)

    sheet.write(0, 16, "Our Courier Cost", heading_xf)

    sheet.write(0, 17, "Snapdeal Courier Cost", heading_xf)

    sheet.write(0, 18, "Reason", heading_xf)

    sheet_iterator=1

    for data in filteredData:

        if data.transferPriceSnapdeal < data.transferPrice:

            sheet.write(sheet_iterator, 0, data.itemId)

            sheet.write(sheet_iterator, 1, data.parentCategory)

            sheet.write(sheet_iterator, 2, data.productGroup)

            sheet.write(sheet_iterator, 3, data.supc)

            sheet.write(sheet_iterator, 4, data.brand)

            sheet.write(sheet_iterator, 5, xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color))

            sheet.write(sheet_iterator, 6, data.sellingPrice)

            sheet.write(sheet_iterator, 7, data.sellingPriceSnapdeal)

            sheet.write(sheet_iterator, 8, data.transferPrice)

            sheet.write(sheet_iterator, 9, data.transferPriceSnapdeal)

            sheet.write(sheet_iterator, 10, round(data.commission*1.1236,2))

            sheet.write(sheet_iterator, 11, round(float(data.fixedMargin)+float(data.collectionCharges),2))

            sheet.write(sheet_iterator, 12, data.commissionPercentage)

            sheet.write(sheet_iterator, 13, round(float(data.fixedMarginPercentage)/1.1236,2))

            sheet.write(sheet_iterator, 14, data.weight*1000)

            sheet.write(sheet_iterator, 15, data.weightSnapdeal)

            sheet.write(sheet_iterator, 16, round(data.courierCost*1.1236,2))

            sheet.write(sheet_iterator, 17, round(data.logisticCostSnapdeal,2)+round(data.woodenPackagingCost,2))

            sheet.write(sheet_iterator, 18, getReasonSheet(data))

            sheet_iterator+=1

    sheet = wbk.add_sheet('High TP SD')

    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')

    excel_integer_format = '0'

    integer_style = xlwt.XFStyle()

    integer_style.num_format_str = excel_integer_format

    xstr = lambda s: s or ""

    sheet.write(0, 0, "Item ID", heading_xf)

    sheet.write(0, 1, "Category", heading_xf)

    sheet.write(0, 2, "Product Group.", heading_xf)

    sheet.write(0, 3, "SUPC", heading_xf)

    sheet.write(0, 4, "Brand", heading_xf)

    sheet.write(0, 5, "Product Name", heading_xf)

    sheet.write(0, 6, "Our System SP", heading_xf)

    sheet.write(0, 7, "Snapdeal SP", heading_xf)

    sheet.write(0, 8, "Our TP", heading_xf)

    sheet.write(0, 9, "Snapdeal TP", heading_xf)

    sheet.write(0, 10, "Our System Commission", heading_xf)

    sheet.write(0, 11, "Snapdeal Commission", heading_xf)

    sheet.write(0, 12, "Our System Commission %", heading_xf)

    sheet.write(0, 13, "Snapdeal Commission %", heading_xf)

    sheet.write(0, 14, "Our System Weight (gms)", heading_xf)

    sheet.write(0, 15, "Snapdeal Weight", heading_xf)

    sheet.write(0, 16, "Our Courier Cost", heading_xf)

    sheet.write(0, 17, "Snapdeal Courier Cost", heading_xf)

    sheet.write(0, 18, "Reason", heading_xf)

    sheet_iterator=1

    for data in filteredData:

        if data.transferPriceSnapdeal > data.transferPrice:

            sheet.write(sheet_iterator, 0, data.itemId)

            sheet.write(sheet_iterator, 1, data.parentCategory)

            sheet.write(sheet_iterator, 2, data.productGroup)

            sheet.write(sheet_iterator, 3, data.supc)

            sheet.write(sheet_iterator, 4, data.brand)

            sheet.write(sheet_iterator, 5, xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color))

            sheet.write(sheet_iterator, 6, data.sellingPrice)

            sheet.write(sheet_iterator, 7, data.sellingPriceSnapdeal)

            sheet.write(sheet_iterator, 8, data.transferPrice)

            sheet.write(sheet_iterator, 9, data.transferPriceSnapdeal)

            sheet.write(sheet_iterator, 10, round(data.commission*1.1236,2))

            sheet.write(sheet_iterator, 11, round(float(data.fixedMargin)+float(data.collectionCharges),2))

            sheet.write(sheet_iterator, 12, data.commissionPercentage)

            sheet.write(sheet_iterator, 13, round(float(data.fixedMarginPercentage)/1.1236,2))

            sheet.write(sheet_iterator, 14, data.weight*1000)

            sheet.write(sheet_iterator, 15, data.weightSnapdeal)

            sheet.write(sheet_iterator, 16, round(data.courierCost*1.1236,2))

            sheet.write(sheet_iterator, 17, round(data.logisticCostSnapdeal,2)+round(data.woodenPackagingCost,2))

            sheet.write(sheet_iterator, 18, getReasonSheet(data))

            sheet_iterator+=1

    sheet = wbk.add_sheet('Exceptions')

    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')

    excel_integer_format = '0'

    integer_style = xlwt.XFStyle()

    integer_style.num_format_str = excel_integer_format

    xstr = lambda s: s or ""

    sheet.write(0, 0, "Item ID", heading_xf)

    sheet.write(0, 1, "SUPC", heading_xf)

    sheet.write(0, 2, "Brand", heading_xf)

    sheet.write(0, 3, "Product Name", heading_xf)

    sheet.write(0, 4, "Our System SP", heading_xf)

    sheet.write(0, 5, "Our TP", heading_xf)

    sheet.write(0, 6, "Our System Commission", heading_xf)

    sheet.write(0, 7, "Our System Commission %", heading_xf)

    sheet.write(0, 8, "Our System Weight (gms)", heading_xf)

    sheet.write(0, 9, "Our Courier Cost", heading_xf)

    sheet_iterator=1

    for data in exceptionList:

        snapdealItem = data[0]

        marketplaceItem = data[1]

        ds_item = data[2]

        sheet.write(sheet_iterator, 0, ds_item.id)

        sheet.write(sheet_iterator, 1, snapdealItem.supc)

        sheet.write(sheet_iterator, 2, ds_item.brand)

        sheet.write(sheet_iterator, 3, xstr(ds_item.brand)+" "+xstr(ds_item.model_name)+" "+xstr(ds_item.model_number)+" "+xstr(ds_item.color))

        sheet.write(sheet_iterator, 4, snapdealItem.sellingPrice)

        sheet.write(sheet_iterator, 5, snapdealItem.transferPrice)

        sheet.write(sheet_iterator, 6, round(snapdealItem.commission*1.1236,2))

        sheet.write(sheet_iterator, 7, marketplaceItem.commission)

        sheet.write(sheet_iterator, 8, (ds_item.weight*1000))

        sheet.write(sheet_iterator, 9, round(snapdealItem.courierCost*1.1236,2))

        sheet_iterator+=1

    filename = "/tmp/snapdeal-tp-reconciliation-" + str(datetime.now()) + ".xls"

    wbk.save(filename)

    try:

        #EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Snapdeal TP Reconciliation "+ str(datetime.now()), "", [get_attachment_part(filename)], [""], [])

        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"], [])

    except Exception as e:

        print e

        print "Unable to send report.Trying with local SMTP"

        smtpServer = smtplib.SMTP('localhost')

        smtpServer.set_debuglevel(1)

        sender = 'support@shop2020.in'

        #recipients = ['kshitij.sood@saholic.com']

        msg = MIMEMultipart()

        msg['Subject'] = "Snapdeal TP Reconciliation" + ' - ' + str(datetime.now())

        msg['From'] = sender

        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']

        msg['To'] = ",".join(recipients)

        fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')

        fileMsg.set_payload(file(filename).read())

        email.encoders.encode_base64(fileMsg)

        fileMsg.add_header('Content-Disposition','attachment;filename=snapdeal_tp_recon.xls')

        msg.attach(fileMsg)

        try:

            smtpServer.sendmail(sender, recipients, msg.as_string())

            print "Successfully sent email"

        except:

            print "Error: unable to send email."

def getReason(data):

    global courierCostToSync 

    reason=""

    if data.sellingPrice!=data.sellingPriceSnapdeal:

        reason+="Selling Price is different."

    if data.commissionPercentage!= round(float(data.fixedMarginPercentage)/1.1236,2):

        reason+="Commission is different."

    if round(data.courierCost*1.1236)!=round(data.logisticCostSnapdeal+data.woodenPackagingCost):

        reason+="Courier Cost is different-Check Weight."

        courierCostToSync.append(data)

    return reason

def getReasonSheet(data):

    reason=""

    if data.sellingPrice!=data.sellingPriceSnapdeal:

        reason+="Selling Price is different."

    if data.commissionPercentage!= round(float(data.fixedMarginPercentage)/1.1236,2):

        reason+="Commission is different."

    if round(data.courierCost*1.1236)!=round(data.logisticCostSnapdeal+data.woodenPackagingCost):

        reason+="Courier Cost is different-Check Weight."

    return reason

def syncCourierCost(courierCostToSync):

    global oldPricing

    for item in courierCostToSync:

        sdItem = SnapdealItem.get_by(item_id=item.itemId)

        mpItem = MarketplaceItems.get_by(itemId=item.itemId, source=OrderSource.SNAPDEAL)

        oldMpItem = copy.deepcopy(mpItem)

        temp = []

        temp.append(item)

        temp.append(oldMpItem)

        addHistory(sdItem)

        sdItem.courierCostMarketplace = round((item.logisticCostSnapdeal+item.woodenPackagingCost)/1.1236)

        sdItem.transferPrice = getNewTp(mpItem,item)

        sdItem.serviceTax = getNewServiceTax(mpItem,sdItem,item)

        mpItem.courierCostMarketplace = round((item.logisticCostSnapdeal+item.woodenPackagingCost)/1.1236)

        mpItem.currentTp = getNewTp(mpItem,item)

        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,sdItem,item)

        temp.append(mpItem)

        oldPricing.append(temp)

    session.commit()

def addHistory(item):

    itemHistory = MarketPlaceUpdateHistory()

    itemHistory.item_id = item.item_id

    itemHistory.source = OrderSource.SNAPDEAL

    itemHistory.exceptionPrice = item.exceptionPrice

    itemHistory.warehouseId = item.warehouseId

    itemHistory.isListedOnSource = item.isListedOnSnapdeal

    itemHistory.transferPrice = item.transferPrice

    itemHistory.sellingPrice = item.sellingPrice

    itemHistory.courierCost = item.courierCost

    itemHistory.commission = item.commission

    itemHistory.serviceTax = item.serviceTax

    itemHistory.suppressPriceFeed = item.suppressPriceFeed

    itemHistory.suppressInventoryFeed = item.suppressInventoryFeed

    itemHistory.updatedOn = item.updatedOn

    itemHistory.maxNlc = item.maxNlc

    itemHistory.skuAtSource = item.skuAtSnapdeal

    itemHistory.marketPlaceSerialNumber = item.supc

    itemHistory.priceUpdatedBy = item.priceUpdatedBy

    itemHistory.courierCostMarketplace = item.courierCostMarketplace

def getNewTp(mpItem,data):

    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)));

    return round(ourTp,2)

def getNewServiceTax(mpItem,sdItem,data):

    return round(mpItem.serviceTax/100*(sdItem.commission+(data.logisticCostSnapdeal+data.woodenPackagingCost)/1.1236),2)

def getNewLowestPossibleSp(mpItem,sdItem,data):

    if (mpItem.pgFee/100)*mpItem.currentSp>=20:

        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));

    else:

        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));

    return round(lowestPossibleSp,2)   

def sendCCSyncMail():

    if len(courierCostToSync) == 0:

        return 

    xstr = lambda s: s or ""

    message="""<html>

            <body>

            <h3>Courier cost synced</h3>

            <table border="1" style="width:100%;">

            <thead>

            <tr><th>Item Id</th>

            <th>Product Name</th>

            <th>Selling Price</th>

            <th>Old Transfer Price</th>

            <th>New Transfer Price</th>

            <th>Old Courier Cost</th>

            <th>New Courier Cost</th>

            <th>Old Margin</th>

            <th>New Margin</th>

            <th>Old Margin %</th>

            <th>New Margin %</th>

            </tr></thead>

            <tbody>"""

    for value in oldPricing:

        data = value[0]

        mpItemOld = value[1]

        mpItemNew = value[2]

        message+="""<tr>

        <td style="text-align:center">"""+str(data.itemId)+"""</td>

        <td style="text-align:center">"""+xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color)+"""</td>

        <td style="text-align:center">"""+str(mpItemOld.currentSp)+"""</td>

        <td style="text-align:center">"""+str(mpItemOld.currentTp)+"""</td>

        <td style="text-align:center">"""+str(mpItemNew.currentTp)+"""</td>

        <td style="text-align:center">"""+str(mpItemOld.courierCostMarketplace)+"""</td>

        <td style="text-align:center">"""+str(mpItemNew.courierCostMarketplace)+"""</td>

        <td style="text-align:center">"""+str(round(mpItemOld.currentTp-mpItemOld.minimumPossibleTp))+"""</td>

        <td style="text-align:center">"""+str(round(mpItemNew.currentTp-mpItemNew.minimumPossibleTp))+"""</td>

        <td style="text-align:center">"""+str(round(((mpItemOld.currentTp-mpItemOld.minimumPossibleTp)/mpItemOld.currentSp)*100,2))+"""</td>

        <td style="text-align:center">"""+str(round(((mpItemNew.currentTp-mpItemNew.minimumPossibleTp)/mpItemNew.currentSp)*100,2))+"""</td>

        </tr>"""

    message+="""</tbody></table></body></html>"""

    print message

    mailServer = smtplib.SMTP("smtp.gmail.com", 587)

    mailServer.ehlo()

    mailServer.starttls()

    mailServer.ehlo()

    #recipients = ['kshitij.sood@saholic.com']

    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']

    msg = MIMEMultipart()

    msg['Subject'] = "Snapdeal Courier Cost Synced" + ' - ' + str(datetime.now())

    msg['From'] = ""

    msg['To'] = ",".join(recipients)

    msg.preamble = "Snapdeal Courier Cost Synced" + ' - ' + str(datetime.now())

    html_msg = MIMEText(message, 'html')

    msg.attach(html_msg)

    try:

        mailServer.login("build@shop2020.in", "cafe@nes")

        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())

        mailServer.sendmail("cafe@nes", recipients, msg.as_string())

    except Exception as e:

        print e

        print "Unable to send Snapdeal Courier Cost mail.Lets try with local SMTP."

        smtpServer = smtplib.SMTP('localhost')

        smtpServer.set_debuglevel(1)

        sender = 'support@shop2020.in'

        try:

            smtpServer.sendmail(sender, recipients, msg.as_string())

            print "Successfully sent email"

        except:

            print "Error: unable to send email."

def main():

    print "Opening snapdeal seller login page"

    br = login("http://selleraccounts.snapdeal.com/")

    exceptionList, fetchedItems = populateStuff(br)

    filteredData = filterData(fetchedItems)

    sendMail(filteredData,exceptionList)

    write_report(filteredData,exceptionList)

    syncCourierCost(courierCostToSync)

    sendCCSyncMail()

if __name__ == "__main__":

    main()