Subversion Repositories SmartDukaan

from elixir import *
from sqlalchemy.sql import func
from shop2020.model.v1.order.impl import DataService
from shop2020.model.v1.order.impl.DataService import Order, LineItem
from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
from datetime import datetime, timedelta
import MySQLdb
import xlwt
from operator import itemgetter
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email import encoders
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart


DataService.initialize(db_hostname='192.168.190.114')
SaleMap = {}
BrandCatMap = {}
UnUsableStatus = [25, 27, 30, 32, 47, 48, 68, 71, 72, 73, 74]
UsableStatus = [21, 28, 64, 67, 69, 70]
db = MySQLdb.connect('localhost',"root","shop2020","warehouse" )
cursor = db.cursor()

class __SaleAndReturnInfo:
    def __init__(self, websiteSale, amazonSale, ebaySale, snapdealSale, flipkartSale, productGroup, sellableCountWebsite, nonSellableCountWebsite, sellableCountAmazon, \
                 nonSellableCountAmazon,sellableCountEbay,nonSellableCountEbay,sellableCountSnapdeal,nonSellableCountSnapdeal,sellableCountFlipkart,nonSellableCountFlipkart, \
                 brand, model_name, model_number, color, sellableWebsitePercentage, nonSellableWebsitePercentage, sellableAmazonPercentage, nonSellableAmazonPercentage, \
                 sellableEbayPercentage, nonSellableEbayPercentage, sellableSnapdealPercentage, nonSellableSnapdealPercentage, sellableFlipkartPercentage, nonSellableFlipkartPercentage, \
                 sellableOverallPercentage, nonSellableOverallPercentage, item_id):
        
        self.websiteSale = websiteSale
        self.amazonSale = amazonSale
        self.ebaySale = ebaySale
        self.snapdealSale = snapdealSale
        self.flipkartSale = flipkartSale
        self.productGroup = productGroup
        self.sellableCountWebsite = sellableCountWebsite
        self.nonSellableCountWebsite = nonSellableCountWebsite
        self.sellableCountAmazon = sellableCountAmazon
        self.nonSellableCountAmazon = nonSellableCountAmazon
        self.sellableCountEbay = sellableCountEbay
        self.nonSellableCountEbay = nonSellableCountEbay
        self.sellableCountSnapdeal = sellableCountSnapdeal
        self.nonSellableCountSnapdeal = nonSellableCountSnapdeal
        self.sellableCountFlipkart = sellableCountFlipkart
        self.nonSellableCountFlipkart = nonSellableCountFlipkart
        self.brand = brand
        self.model_name = model_name
        self.model_number = model_number
        self.color = color
        self.sellableWebsitePercentage = sellableWebsitePercentage
        self.nonSellableWebsitePercentage = nonSellableWebsitePercentage
        self.sellableAmazonPercentage = sellableAmazonPercentage
        self.nonSellableAmazonPercentage = nonSellableAmazonPercentage
        self.sellableEbayPercentage = sellableEbayPercentage
        self.nonSellableEbayPercentage = nonSellableEbayPercentage 
        self.sellableSnapdealPercentage = sellableSnapdealPercentage
        self.nonSellableSnapdealPercentage = nonSellableSnapdealPercentage 
        self.sellableFlipkartPercentage = sellableFlipkartPercentage
        self.nonSellableFlipkartPercentage = nonSellableFlipkartPercentage
        self.sellablOverallPercentage = sellableOverallPercentage
        self.nonSellableOverallPercentage = nonSellableOverallPercentage
        self.item_id = item_id
        
class __BrandCatInfo:
    def __init__(self, totalSale, nonSellableReturn, nonSellableReturnPercentage, brand, productGroup):
        self.totalSale = totalSale
        self.nonSellableReturn  = nonSellableReturn
        self.nonSellableReturnPercentage = nonSellableReturnPercentage
        self.brand = brand
        self.productGroup = productGroup



def populateSaleInfo():
    global SaleMap
    fromDate = (datetime.now()-timedelta(days=45))
    toDate = (fromDate + timedelta(days=15))
    #allShippedOrders = Order.query.filter(Order.created_timestamp.between(fromDate.date(),toDate.date())).filter(Order.shipping_timestamp!=None).all()
    
    allShippedOrders = session.query(LineItem.item_id,LineItem.productGroup,Order.source,Order.status,Order.received_return_timestamp, \
                                     LineItem.quantity,Order.id,LineItem.brand,LineItem.model_name,LineItem.model_number,LineItem.color) \
    .join((Order,LineItem.order_id==Order.id)).filter(Order.created_timestamp.between(fromDate.date(),toDate.date())) \
    .filter(Order.shipping_timestamp!=None).filter(LineItem.productGroup.in_(('Handsets','Mobile Handset','Smart Phone','Mobile Phone','Tablets'))).all()

    for shippedOrder in allShippedOrders:
        if SaleMap.has_key(shippedOrder[0]):
            val = SaleMap.get(shippedOrder[0])
            if shippedOrder[2] in (OrderSource.WEBSITE,OrderSource.MOBILESITE):
                val.websiteSale = val.websiteSale + shippedOrder[5]
            elif shippedOrder[2]==OrderSource.AMAZON:
                val.amazonSale = val.amazonSale + shippedOrder[5]
            elif shippedOrder[2]==OrderSource.EBAY:
                val.ebaySale = val.ebaySale + shippedOrder[5]
            elif shippedOrder[2]==OrderSource.SNAPDEAL:
                val.snapdealSale = val.snapdealSale + shippedOrder[5]
            elif shippedOrder[2]==OrderSource.FLIPKART:
                val.flipkartSale = val.flipkartSale + shippedOrder[5]
            else:
                print "Unknown source for orderId ",shippedOrder[6]
        else:
            saleInfo = __SaleAndReturnInfo(0,0,0,0,0,shippedOrder[1],0,0,0,0,0,0,0,0,0,0,shippedOrder[7],shippedOrder[8],shippedOrder[9],shippedOrder[10], \
                                           0,0,0,0,0,0,0,0,0,0,0,0,shippedOrder[0])
            if shippedOrder[2] in (OrderSource.WEBSITE,OrderSource.MOBILESITE):
                saleInfo.websiteSale = saleInfo.websiteSale + shippedOrder[5]
            elif shippedOrder[2]==OrderSource.AMAZON:
                saleInfo.amazonSale = saleInfo.amazonSale + shippedOrder[5]
            elif shippedOrder[2]==OrderSource.EBAY:
                saleInfo.ebaySale = saleInfo.ebaySale + shippedOrder[5]
            elif shippedOrder[2]==OrderSource.SNAPDEAL:
                saleInfo.snapdealSale = saleInfo.snapdealSale + shippedOrder[5]
            elif shippedOrder[2]==OrderSource.FLIPKART:
                saleInfo.flipkartSale = saleInfo.flipkartSale + shippedOrder[5]
            else:
                print "Unknown source for orderId ",shippedOrder[6]
            SaleMap[shippedOrder[0]]=saleInfo
    
    #Checking returns
    
    for shippedOrder in allShippedOrders:
        if shippedOrder[4] is not None:
            val = SaleMap.get(shippedOrder[0])
            sql = "select quantity,type from scanNew where orderId=%d and type in ('SALE_RET','DOA_IN','SALE_RET_UNUSABLE')" %(shippedOrder[6])
            cursor.execute(sql)
            result = cursor.fetchone()
            print result
            if result is None:
                continue
            quantity = result[0]
            returnType = result[1]
            if shippedOrder[2] in (OrderSource.WEBSITE,OrderSource.MOBILESITE):
                if returnType in ('DOA_IN','SALE_RET_UNUSABLE'):
                    val.nonSellableCountWebsite = val.nonSellableCountWebsite + quantity
                else:
                    val.sellableCountWebsite = val.sellableCountWebsite + quantity
            elif shippedOrder[2]==OrderSource.AMAZON:
                if returnType in ('DOA_IN','SALE_RET_UNUSABLE'):
                    val.nonSellableCountAmazon = val.nonSellableCountAmazon + quantity
                else:
                    val.sellableCountAmazon = val.sellableCountAmazon + quantity
            elif shippedOrder[2]==OrderSource.EBAY:
                if returnType in ('DOA_IN','SALE_RET_UNUSABLE'):
                    val.nonSellableCountEbay = val.nonSellableCountEbay + quantity
                else:
                    val.sellableCountEbay = val.sellableCountEbay + quantity
            elif shippedOrder[2]==OrderSource.SNAPDEAL:
                if returnType in ('DOA_IN','SALE_RET_UNUSABLE'):
                    val.nonSellableCountSnapdeal = val.nonSellableCountSnapdeal + quantity
                else:
                    val.sellableCountSnapdeal = val.sellableCountSnapdeal + quantity
            elif shippedOrder[2]==OrderSource.FLIPKART:
                if returnType in ('DOA_IN','SALE_RET_UNUSABLE'):
                    val.nonSellableCountFlipkart = val.nonSellableCountFlipkart + quantity
                else:
                    val.sellableCountFlipkart = val.sellableCountFlipkart + quantity
            else:
                print "Not counting, unknown source"
    
    allShippedOrders[:] = []
    db.close()

def calculateReturnPercentage():
    global SaleMap
    for k, v in SaleMap.iteritems():
        if v.websiteSale > 0:
            v.sellableWebsitePercentage = float(v.sellableCountWebsite)/v.websiteSale
            v.nonSellableWebsitePercentage = float(v.nonSellableCountWebsite)/v.websiteSale
        if v.amazonSale > 0:
            v.sellableAmazonPercentage = float(v.sellableCountAmazon)/v.amazonSale
            v.nonSellableAmazonPercentage = float(v.nonSellableCountAmazon)/v.amazonSale
        if v.ebaySale > 0:
            v.sellableEbayPercentage = float(v.sellableCountEbay)/v.ebaySale
            v.nonSellableEbayPercentage = float(v.nonSellableCountEbay)/v.ebaySale
        if v.snapdealSale > 0:
            v.sellableSnapdealPercentage = float(v.sellableCountSnapdeal)/v.snapdealSale
            v.nonSellableSnapdealPercentage = float(v.nonSellableCountSnapdeal)/v.snapdealSale
        if v.flipkartSale > 0:
            v.sellableFlipkartPercentage = float(v.sellableCountFlipkart)/v.flipkartSale
            v.nonSellableFlipkartPercentage = float(v.nonSellableCountFlipkart)/v.flipkartSale
        if (v.websiteSale+v.amazonSale+v.ebaySale+v.snapdealSale+v.flipkartSale) > 0:
            v.sellableOverallPercentage = float(v.sellableCountWebsite+v.sellableCountAmazon+v.sellableCountEbay+ \
                                           v.sellableCountSnapdeal+v.sellableCountFlipkart)/(v.websiteSale+v.amazonSale+v.ebaySale+v.snapdealSale+v.flipkartSale)
            v.nonSellableOverallPercentage = float(v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+ \
                                               v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart)/(v.websiteSale+v.amazonSale+v.ebaySale+v.snapdealSale+v.flipkartSale)
        

def exit():
    import sys
    sys.exit(1)


def sendEmail():
    xstr = lambda s: s or ""
    message="""<html>
            <body>
            <h3>Source Wise Return</h3>
            <h4>Orders from """+str((datetime.now()-timedelta(days=45)).date())+"""
             to """+str((datetime.now()-timedelta(days=30)).date())+"""</h4>
            <table border="1" style="width:50%;">
            <thead>
            <tr><th>Source</th>
            <th>Total Sale</th>
            <th>Total Returns</th>
            <th>Total Sellable Returns</th>
            <th>Total Non Sellable Returns</th>
            <th>Return %</th>
            <th>Sellable Return %</th>
            <th>Non Sellable Return %</th>
            </tr></thead>
            <tbody>"""
    websiteSale, websiteSellableReturns, websiteNonSellableReturns, amazonSale, amazonSellableReturns, amazonNonSellableReturns, \
    ebaySale, ebaySellableReturns, ebayNonSellableReturns, snapdealSale, snapdealSellableReturns, snapdealNonSellableReturns, \
    flipkartSale, flipkartSellableReturns ,  flipkartNonSellableReturns, overallSale, overallSellableReturns, overallNonSellableReturns = (0,)*18
    for k, v in SaleMap.iteritems():
        websiteSale = websiteSale + v.websiteSale
        websiteSellableReturns = websiteSellableReturns + v.sellableCountWebsite
        websiteNonSellableReturns = websiteNonSellableReturns + v.nonSellableCountWebsite
        
        amazonSale = amazonSale + v.amazonSale
        amazonSellableReturns = amazonSellableReturns + v.sellableCountAmazon
        amazonNonSellableReturns = amazonNonSellableReturns + v.nonSellableCountAmazon
        
        ebaySale = ebaySale + v.ebaySale
        ebaySellableReturns = ebaySellableReturns + v.sellableCountEbay
        ebayNonSellableReturns = ebayNonSellableReturns + v.nonSellableCountEbay
        
        snapdealSale = snapdealSale + v.snapdealSale
        snapdealSellableReturns = snapdealSellableReturns + v.sellableCountSnapdeal
        snapdealNonSellableReturns = snapdealNonSellableReturns + v.nonSellableCountSnapdeal
        
        flipkartSale = flipkartSale + v.flipkartSale
        flipkartSellableReturns = flipkartSellableReturns + v.sellableCountFlipkart
        flipkartNonSellableReturns = flipkartNonSellableReturns + v.nonSellableCountFlipkart
        
    overallSale = overallSale + websiteSale + amazonSale + ebaySale + snapdealSale + flipkartSale
    overallSellableReturns = overallSellableReturns + websiteSellableReturns + amazonSellableReturns + ebaySellableReturns + snapdealSellableReturns + flipkartSellableReturns 
    overallNonSellableReturns = overallNonSellableReturns + websiteNonSellableReturns + amazonNonSellableReturns + ebayNonSellableReturns + snapdealNonSellableReturns + flipkartNonSellableReturns
    message+="""<tr>
                <td style="text-align:center">"""+"Website"+"""</td>
                <td style="text-align:center">"""+str(int(websiteSale))+"""</td>
                <td style="text-align:center">"""+str(websiteSellableReturns+websiteNonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(websiteSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(websiteNonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(round(float(websiteSellableReturns+websiteNonSellableReturns)*100/websiteSale,1))+" %"+"""</td>
                <td style="text-align:center">"""+str(round(float(websiteSellableReturns)*100/websiteSale,1))+" %"+"""</td>
                <td style="text-align:center">"""+str(round(float(websiteNonSellableReturns)*100/websiteSale,1))+" %"+"""</td>
                </tr>
                <tr>
                <td style="text-align:center">"""+"Amazon MFN"+"""</td>
                <td style="text-align:center">"""+str(int(amazonSale))+"""</td>
                <td style="text-align:center">"""+str(amazonSellableReturns+amazonNonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(amazonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(amazonNonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(round(float(amazonSellableReturns+amazonNonSellableReturns)*100/amazonSale,1))+" %"+"""</td>
                <td style="text-align:center">"""+str(round(float(amazonSellableReturns)*100/amazonSale,1))+" %"+"""</td>
                <td style="text-align:center">"""+str(round(float(amazonNonSellableReturns)*100/amazonSale,1))+" %"+"""</td>
                </tr>
                <tr>
                <td style="text-align:center">"""+"Ebay"+"""</td>
                <td style="text-align:center">"""+str(int(ebaySale))+"""</td>
                <td style="text-align:center">"""+str(ebaySellableReturns+ebayNonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(ebaySellableReturns)+"""</td>
                <td style="text-align:center">"""+str(ebayNonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(round(float(ebaySellableReturns+ebayNonSellableReturns)*100/ebaySale,1))+" %"+"""</td>
                <td style="text-align:center">"""+str(round(float(ebaySellableReturns)*100/ebaySale,1))+" %"+"""</td>
                <td style="text-align:center">"""+str(round(float(ebayNonSellableReturns)*100/ebaySale,1))+" %"+"""</td>
                </tr>
                <tr>
                <td style="text-align:center">"""+"Snapdeal"+"""</td>
                <td style="text-align:center">"""+str(int(snapdealSale))+"""</td>
                <td style="text-align:center">"""+str(snapdealSellableReturns+snapdealNonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(snapdealSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(snapdealNonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(round(float(snapdealSellableReturns+snapdealNonSellableReturns)*100/snapdealSale,1))+" %"+"""</td>
                <td style="text-align:center">"""+str(round(float(snapdealSellableReturns)*100/snapdealSale,1))+" %"+"""</td>
                <td style="text-align:center">"""+str(round(float(snapdealNonSellableReturns)*100/snapdealSale,1))+" %"+"""</td>
                </tr>
                <tr>
                <td style="text-align:center">"""+"Flipkart"+"""</td>
                <td style="text-align:center">"""+str(int(flipkartSale))+"""</td>
                <td style="text-align:center">"""+str(flipkartSellableReturns+flipkartNonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(flipkartSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(flipkartNonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(round(float(flipkartSellableReturns+flipkartNonSellableReturns)*100/flipkartSale,1))+" %"+"""</td>
                <td style="text-align:center">"""+str(round(float(flipkartSellableReturns)*100/flipkartSale,1))+" %"+"""</td>
                <td style="text-align:center">"""+str(round(float(flipkartNonSellableReturns)*100/flipkartSale,1))+" %"+"""</td>
                </tr>
                <tr>
                <td style="text-align:center">"""+"Total"+"""</td>
                <td style="text-align:center">"""+str(int(overallSale))+"""</td>
                <td style="text-align:center">"""+str(overallSellableReturns+overallNonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(overallSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(overallNonSellableReturns)+"""</td>
                <td style="text-align:center">"""+str(round(float(overallSellableReturns+overallNonSellableReturns)*100/overallSale,1))+" %"+"""</td>
                <td style="text-align:center">"""+str(round(float(overallSellableReturns)*100/overallSale,1))+" %"+"""</td>
                <td style="text-align:center">"""+str(round(float(overallNonSellableReturns)*100/overallSale,1))+" %"+"""</td>
                </tr></tbody></table>"""
    
    topWebsite, topAmazon, topEbay, topSnapdeal, topFlipkart, topOverall = [],[],[],[],[],[]

    for v in SaleMap.itervalues():
#        if v.websiteSale >=10:
#            topWebsite.append(v)
#        if v.amazonSale >=10:
#            topAmazon.append(v)
#        if v.ebaySale >=10:
#            topEbay.append(v)
#        if v.snapdealSale >=10:
#            topSnapdeal.append(v)
#        if v.flipkartSale >=10:
#            topFlipkart.append(v)
        if (v.websiteSale + v.amazonSale + v.ebaySale + v.snapdealSale + v.flipkartSale) >=25:
            topOverall.append(v)

    sortedHighReturnRate = sorted(topOverall, key=lambda x: x.nonSellableOverallPercentage, reverse=True)
    
    message+="""<h3>Top Sku's with high sale return</h3>
            <table border="1" style="width:100%;">
            <thead>
            <tr><th>Item Id</th>
            <th>Product Name</th>
            <th>Website Sale</th>
            <th>Website Sellable Returns</th>
            <th>Website Non Sellable Returns</th>
            <th>Website Sellable Returns %</th>
            <th>Website Non Sellable Returns %</th>
            <th>Amazon Sale</th>
            <th>Amazon Sellable Returns</th>
            <th>Amazon Non Sellable Returns</th>
            <th>Amazon Sellable Returns %</th>
            <th>Amazon Non Sellable Returns %</th>
            <th>Ebay Sale</th>
            <th>Ebay Sellable Returns</th>
            <th>Ebay Non Sellable Returns</th>
            <th>Ebay Sellable Returns %</th>
            <th>Ebay Non Sellable Returns %</th>
            <th>Snapdeal Sale</th>
            <th>Snapdeal Sellable Returns</th>
            <th>Snapdeal Non Sellable Returns</th>
            <th>Snapdeal Sellable Returns %</th>
            <th>Snapdeal Non Sellable Returns %</th>
            <th>Flipkart Sale</th>
            <th>Flipkart Sellable Returns</th>
            <th>Flipkart Non Sellable Returns</th>
            <th>Flipkart Sellable Returns %</th>
            <th>Flipkart Non Sellable Returns %</th>
            <th>Total Sale</th>
            <th>Total Sellable Returns</th>
            <th>Total Non Sellable Returns</th>
            <th>Total Sellable Returns %</th>
            <th>Total Non Sellable Returns %</th>
            </tr></thead>
            <tbody>"""
    countItems = 0
    for highReturnData in sortedHighReturnRate:
        if countItems==11:
            break
        countItems+=1
        message+="""<tr>
                <td style="text-align:center">"""+str(highReturnData.item_id)+"""</td>
                <td style="text-align:center">"""+xstr(highReturnData.brand)+" "+xstr(highReturnData.model_name)+" "+xstr(highReturnData.model_number)+" "+xstr(highReturnData.color)+"""</td>
                <td style="text-align:center">"""+str(int(highReturnData.websiteSale))+"""</td>
                <td style="text-align:center">"""+str(highReturnData.sellableCountWebsite)+"""</td>
                <td style="text-align:center">"""+str(highReturnData.nonSellableCountWebsite)+"""</td>
                <td style="text-align:center">"""+str(round(highReturnData.sellableWebsitePercentage*100,1))+"""</td>
                <td style="text-align:center">"""+str(round(highReturnData.nonSellableWebsitePercentage*100,1))+"""</td>
                <td style="text-align:center">"""+str(int(highReturnData.amazonSale))+"""</td>
                <td style="text-align:center">"""+str(highReturnData.sellableCountAmazon)+"""</td>
                <td style="text-align:center">"""+str(highReturnData.nonSellableCountAmazon)+"""</td>
                <td style="text-align:center">"""+str(round(highReturnData.sellableAmazonPercentage*100,1))+"""</td>
                <td style="text-align:center">"""+str(round(highReturnData.nonSellableAmazonPercentage*100,1))+"""</td>
                <td style="text-align:center">"""+str(int(highReturnData.ebaySale))+"""</td>
                <td style="text-align:center">"""+str(highReturnData.sellableCountEbay)+"""</td>
                <td style="text-align:center">"""+str(highReturnData.nonSellableCountEbay)+"""</td>
                <td style="text-align:center">"""+str(round(highReturnData.sellableEbayPercentage*100,1))+"""</td>
                <td style="text-align:center">"""+str(round(highReturnData.nonSellableEbayPercentage*100,1))+"""</td>
                <td style="text-align:center">"""+str(int(highReturnData.snapdealSale))+"""</td>
                <td style="text-align:center">"""+str(highReturnData.sellableCountSnapdeal)+"""</td>
                <td style="text-align:center">"""+str(highReturnData.nonSellableCountSnapdeal)+"""</td>
                <td style="text-align:center">"""+str(round(highReturnData.sellableSnapdealPercentage*100,1))+"""</td>
                <td style="text-align:center">"""+str(round(highReturnData.nonSellableSnapdealPercentage*100,1))+"""</td>
                <td style="text-align:center">"""+str(int(highReturnData.flipkartSale))+"""</td>
                <td style="text-align:center">"""+str(highReturnData.sellableCountFlipkart)+"""</td>
                <td style="text-align:center">"""+str(highReturnData.nonSellableCountFlipkart)+"""</td>
                <td style="text-align:center">"""+str(round(highReturnData.sellableFlipkartPercentage*100,1))+"""</td>
                <td style="text-align:center">"""+str(round(highReturnData.nonSellableFlipkartPercentage*100,1))+"""</td>
                <td style="text-align:center">"""+str(int(highReturnData.websiteSale+highReturnData.amazonSale+highReturnData.ebaySale+highReturnData.snapdealSale+highReturnData.flipkartSale))+"""</td>
                <td style="text-align:center">"""+str(highReturnData.sellableCountWebsite+highReturnData.sellableCountAmazon+highReturnData.sellableCountEbay+highReturnData.sellableCountSnapdeal+highReturnData.sellableCountFlipkart)+"""</td>
                <td style="text-align:center">"""+str(highReturnData.nonSellableCountWebsite+highReturnData.nonSellableCountAmazon+highReturnData.nonSellableCountEbay+highReturnData.nonSellableCountSnapdeal+highReturnData.nonSellableCountFlipkart)+"""</td>
                <td style="text-align:center">"""+str(round(highReturnData.sellableOverallPercentage*100,1))+"""</td>
                <td style="text-align:center">"""+str(round(highReturnData.nonSellableOverallPercentage*100,1))+"""</td>
                </tr>"""
    
    message+="""</tr></tbody></table>"""
    
    topBrandCatReturns = []

    for y in BrandCatMap.itervalues():
        if y.totalSale > 25:
            topBrandCatReturns.append(y)
    
    sortedHighReturnRateBrandCat = sorted(topBrandCatReturns, key=lambda x: x.nonSellableReturnPercentage, reverse=True)
    message+="""<h3>Brand Category Wise Non-Sellable Return</h3>
            <table border="1" style="width:50%;">
            <thead>
            <tr><th>Brand</th>
            <th>Category</th>
            <th>Total Sale</th>
            <th>Non Sellable Returns</th>
            <th>Non Sellable Returns %</th>
            </tr></thead>
            <tbody>"""
    for highReturnDataBrandCat in sortedHighReturnRateBrandCat:
        message+="""<tr>
                    <td style="text-align:center">"""+str(highReturnDataBrandCat.brand)+"""</td>
                    <td style="text-align:center">"""+str(highReturnDataBrandCat.productGroup)+"""</td>
                    <td style="text-align:center">"""+str(int(highReturnDataBrandCat.totalSale))+"""</td>
                    <td style="text-align:center">"""+str(int(highReturnDataBrandCat.nonSellableReturn))+"""</td>
                    <td style="text-align:center">"""+str(round(highReturnDataBrandCat.nonSellableReturnPercentage*100,1))+"""</td>
                     </tr>"""
    message+="""</tr></tbody></table></body></html>"""
    
    wbk = xlwt.Workbook(encoding="UTF-8")
    sheet = wbk.add_sheet('Source Wise Return')
    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, "Product Group", heading_xf)
    sheet.write(0, 2, "Brand", heading_xf)
    sheet.write(0, 3, "Product", heading_xf)
    sheet.write(0, 4, "Total Sale", heading_xf)
    sheet.write(0, 5, "Website Sale", heading_xf)
    sheet.write(0, 6, "Amazon Sale", heading_xf)
    sheet.write(0, 7, "Ebay Sale", heading_xf)
    sheet.write(0, 8, "Snapdeal Sale", heading_xf)
    sheet.write(0, 9, "Flipkart Sale", heading_xf)
    sheet.write(0, 10, "Sellable Website", heading_xf)
    sheet.write(0, 11, "Non Sellable Website", heading_xf)
    sheet.write(0, 12, "Sellable Amazon", heading_xf)
    sheet.write(0, 13, "Non Sellable Amazon", heading_xf)
    sheet.write(0, 14, "Sellable Ebay", heading_xf)
    sheet.write(0, 15, "Non Sellable Ebay", heading_xf)
    sheet.write(0, 16, "Sellable Snapdeal", heading_xf)
    sheet.write(0, 17, "Non Sellable Snapdeal", heading_xf)
    sheet.write(0, 18, "Sellable Flipkart", heading_xf)
    sheet.write(0, 19, "Non Sellable Flipkart", heading_xf)
    sheet.write(0, 20, "Total Sellable Returns", heading_xf)
    sheet.write(0, 21, "Sellable Returns %", heading_xf)
    sheet.write(0, 22, "Total Non Sellable Returns", heading_xf)
    sheet.write(0, 23, "Non Sellable Returns %", heading_xf)
    
    sheet_iterator=1
    for k, v in SaleMap.iteritems():
        sheet.write(sheet_iterator,0,k)
        sheet.write(sheet_iterator,1,v.productGroup)
        sheet.write(sheet_iterator,2,v.brand)
        sheet.write(sheet_iterator,3,xstr(v.brand)+" "+xstr(v.model_name)+" "+xstr(v.model_number)+" "+xstr(v.color))
        sheet.write(sheet_iterator,4,v.websiteSale+v.amazonSale+v.ebaySale+v.flipkartSale+v.snapdealSale)
        sheet.write(sheet_iterator,5,v.websiteSale)
        sheet.write(sheet_iterator,6,v.amazonSale)
        sheet.write(sheet_iterator,7,v.ebaySale)
        sheet.write(sheet_iterator,8,v.snapdealSale)
        sheet.write(sheet_iterator,9,v.flipkartSale)
        sheet.write(sheet_iterator,10,v.sellableCountWebsite)
        sheet.write(sheet_iterator,11,v.nonSellableCountWebsite)
        sheet.write(sheet_iterator,12,v.sellableCountAmazon)
        sheet.write(sheet_iterator,13,v.nonSellableCountAmazon)
        sheet.write(sheet_iterator,14,v.sellableCountEbay)
        sheet.write(sheet_iterator,15,v.nonSellableCountEbay)
        sheet.write(sheet_iterator,16,v.sellableCountSnapdeal)
        sheet.write(sheet_iterator,17,v.nonSellableCountSnapdeal)
        sheet.write(sheet_iterator,18,v.sellableCountFlipkart)
        sheet.write(sheet_iterator,19,v.nonSellableCountFlipkart)
        sheet.write(sheet_iterator,20,v.sellableCountWebsite+v.sellableCountAmazon+v.sellableCountEbay+v.sellableCountSnapdeal+v.sellableCountFlipkart)
        if (v.websiteSale+v.amazonSale+v.ebaySale+v.flipkartSale+v.snapdealSale) > 0:
            sheet.write(sheet_iterator,21,round(float(v.sellableCountWebsite+v.sellableCountAmazon+v.sellableCountEbay+v.sellableCountSnapdeal+v.sellableCountFlipkart)*100/(v.websiteSale+v.amazonSale+v.ebaySale+v.flipkartSale+v.snapdealSale),1))
        else:
            sheet.write(sheet_iterator,21,0)
        sheet.write(sheet_iterator,22,v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart)
        if (v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart) > 0:
            sheet.write(sheet_iterator,23,round(float(v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart)*100/(v.websiteSale+v.amazonSale+v.ebaySale+v.flipkartSale+v.snapdealSale),1))
        else:
            sheet.write(sheet_iterator,23,0)
        sheet_iterator+=1
    filename = "/tmp/return-report-source-mob-tab.xls"
    wbk.save(filename)
        
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()

    recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','amit.sirohi@saholic.com','khushal.bhatia@saholic.com']
    msg = MIMEMultipart()
    msg['Subject'] = "Sale Return Source Wise Mobiles & Tablets" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Sale Return Source Wise Sale Return Source Wise Mobiles & Tablets" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    fileMsg = MIMEBase('application','vnd.ms-excel')
    fileMsg.set_payload(file(filename).read())
    encoders.encode_base64(fileMsg)
    from datetime import date
    fileMsg.add_header('Content-Disposition','attachment;filename=Return-Source-Wise-Mob-Tab' + ' - ' + date.today().isoformat() + '.xls')
    msg.attach(fileMsg)
    try:
        mailServer.login("cnc.center@shop2020.in", "5h0p2o2o")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("5h0p2o2o", recipients, msg.as_string())
    except Exception as e:
        print e
        print "Unable to send return .Lets try with local SMTP."
        smtpServer = smtplib.SMTP('localhost')
        smtpServer.set_debuglevel(1)
        sender = 'cnc.center@shop2020.in'
        try:
            smtpServer.sendmail(sender, recipients, msg.as_string())
            print "Successfully sent email"
        except:
            print "Error: unable to send email."
             
def populateBrandCategoryReturn():
    global BrandCatMap
    for v in SaleMap.itervalues():
        if BrandCatMap.has_key(v.productGroup+" "+v.brand):
            brandCatObj = BrandCatMap.get(v.productGroup+" "+v.brand)
            totalSale = brandCatObj.totalSale + v.websiteSale + v.amazonSale + v.ebaySale + v.snapdealSale +v.flipkartSale
            totalNonSellableReturn = brandCatObj.nonSellableReturn + v.nonSellableCountWebsite + v.nonSellableCountAmazon + v.nonSellableCountEbay + v.nonSellableCountSnapdeal + v.nonSellableCountFlipkart
            nonSellableReturnPercentage = float(totalNonSellableReturn)/totalSale
            brandCatObj.totalSale = totalSale
            brandCatObj.nonSellableReturn = totalNonSellableReturn
            brandCatObj.nonSellableReturnPercentage = nonSellableReturnPercentage 
        else:
            totalSale = v.websiteSale + v.amazonSale + v.ebaySale + v.snapdealSale +v.flipkartSale
            totalNonSellableReturn = v.nonSellableCountWebsite + v.nonSellableCountAmazon + v.nonSellableCountEbay + v.nonSellableCountSnapdeal + v.nonSellableCountFlipkart
            nonSellableReturnPercentage = float(totalNonSellableReturn)/totalSale
            brandCatObj = __BrandCatInfo(totalSale,totalNonSellableReturn,nonSellableReturnPercentage,v.brand,v.productGroup)
            BrandCatMap[v.productGroup+" "+v.brand] =  brandCatObj
    
        
def main():
    populateSaleInfo()
    calculateReturnPercentage()
    populateBrandCategoryReturn()
    sendEmail()
    

if __name__=='__main__':
    main()