Subversion Repositories SmartDukaan

'''
Created on 08-Jul-2013

@author: anupam
'''

import MySQLdb
import datetime
import smtplib
import xlwt
import traceback

from email import encoders
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from datetime import date

# Initialize db connection settings.
DB_HOST = "192.168.190.114"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "transaction"

# KEY NAMES
SENDER = "cnc.center@shop2020.in"
PASSWORD = "5h0p2o2o"
SUBJECT = "Recharge report for week ending on " + date.today().isoformat()
SMTP_SERVER = "smtp.gmail.com"
SMTP_PORT = 587    

TMP_FILE="/tmp/dtrshipped_report.xls"

report_map = {}


SHIPPED_ORDER_GROUP_SQL="""     select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, count(*),sum(o.total_amount) as amount 
                                from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id  
                                join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id  
                                where o.status in (9,10,16,17,40) and o.total_amount > 1 group by c.name, prodname  with rollup
                                
                        """
SHIPPED_ORDER_SQL=      """
                          select c.code,c.name, w.displayName as warehouse, o.id, date(o.created_timestamp) as orderedon, date(o.shipping_timestamp) as shiipedon, os.statusName , date(o.expected_delivery_time), 
                          concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount from transaction.order o 
                          join user.privatedealuser pd on o.customer_id=pd.id  join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id 
                          join inventory.warehouse w on w.id = o.fulfilmentWarehouseId join orderstatus os on o.status = os.status 
                          where o.status in (9,10,16,17,40) and o.total_amount > 1 order by code, prodname   
                        """

SOLD_OUT_GROUP_SQL="""
                    select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, count(*),  sum(o.total_amount) as total_sale 
                        from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id 
                        join mobileactivationrecord mar on mar.orderId = o.id join user.counter c on c.id = pd.counter_id 
                        join transaction.lineitem li on li.order_id=o.id  where date(mar.activationTime) = curdate()- interval 1 day group by c.name, prodname  with rollup;
                   """
SOLD_OUT_SQL="""
                          select c.code,c.name, o.id, date(o.created_timestamp) as orderedon, date(delivery_timestamp), mar.activationTime, 
                          concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount from transaction.order o 
                          join user.privatedealuser pd on o.customer_id=pd.id  join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id 
                          join mobileactivationrecord mar on mar.orderId = o.id where date(mar.activationTime) = curdate()- interval 1 day order by code, prodname
             """
PENDING_ORDER_GROUP_SQL="""     select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, count(*),sum(o.total_amount) as amount 
                                from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id  
                                join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id  
                                where o.status in (3,4,5,7) and o.total_amount > 1 group by c.name, prodname  with rollup
                                
                        """
PENDING_ORDER_SQL= """
                          select c.code,c.name, w.displayName as warehouse, o.id, date(o.created_timestamp) as orderedon, date(o.shipping_timestamp) as shiipedon, os.statusName , date(o.expected_delivery_time), 
                          concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount from transaction.order o 
                          join user.privatedealuser pd on o.customer_id=pd.id  join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id 
                          join inventory.warehouse w on w.id = o.fulfilmentWarehouseId join orderstatus os on o.status = os.status 
                          where o.status in (3,4,5,7) and o.total_amount > 1 order by code, prodname   
                        """

STOCK_REPORT_GROUP_SQL= """
                        select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product,  sum(if(o.status=12, 1,0)) delivered, sum(if(mar.activationTime is not null, 1, 0)) as sold, sum(if(mar.activationTime is null and o.status=12, 1, 0)) as instock, sum(if(mar.activationTime is null and o.status=12, o.total_amount,0)) as valueinstock, sum(if(o.status in (9,10,16,17,40), 1,0)) shipped, sum(if(o.status in (9,10,16,17,40), o.total_amount,0)) shippedAmount, sum(if(o.status in (3,4,5,7), 1,0)) pending, sum(if(o.status in (3,4,5,7), o.total_amount,0)) pendingAmount from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id left join mobileactivationrecord mar on mar.orderId = o.id  join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id where o.status in (12,9,10,16,17,40,3,4,5,7) and o.total_amount > 0 and li.brand = 'Spice' and li.item_id not in (select itemId from catalog.spiceuntraceable) group by name, product  with rollup
                        """
STOCK_REPORT_GROUP_SQL_NO_ROLLUP = """
                        select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product,  sum(if(o.status=12, 1,0)) delivered, sum(if(mar.activationTime is not null, 1, 0)) as sold, sum(if(mar.activationTime is null and o.status=12, 1, 0)) as instock, sum(if(mar.activationTime is null and o.status=12, o.total_amount,0)) as valueinstock, sum(if(o.status in (9,10,16,17,40), 1,0)) shipped, sum(if(o.status in (9,10,16,17,40), o.total_amount,0)) shippedAmount, sum(if(o.status in (3,4,5,7), 1,0)) pending, sum(if(o.status in (3,4,5,7), o.total_amount,0)) pendingAmount from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id left join mobileactivationrecord mar on mar.orderId = o.id  join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id where o.status in (12,9,10,16,17,40,3,4,5,7) and o.total_amount > 0 and li.brand = 'Spice' and li.item_id not in (select itemId from catalog.spiceuntraceable) group by name, product
                        """

STOCK_REPORT_SQL=       """
                        select c.code, c.name, o.id, date(o.created_timestamp) createddate, date(o.delivery_timestamp) delivereddate, os.statusName, 
                        concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product,  li.quantity, o.total_amount, 
                        datediff(curdate(),date(o.delivery_timestamp)) as ageing  from transaction.order o join user.privatedealuser 
                        pd on o.customer_id=pd.id left join mobileactivationrecord mar on mar.orderId = o.id  
                        join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id join orderstatus os on o.status = os.status
                        where o.status=12 and o.total_amount > 0 and mar.activationTime is null and li.brand = 'Spice' and li.item_id not in (select itemId from catalog.spiceuntraceable)
                        """

MIS_REPORT_GROUP_SQL="""
                        select c.name, sum(if(date(o.created_timestamp)=curdate()-interval 1 day, 1, 0)) as lastDayQty, 
                            sum(if(date(o.created_timestamp)=curdate()- interval 1 day, o.total_amount , 0)) lastDayValue, 
                            count(*) as MTDQty, sum(o.total_amount) as MTDValue from transaction.order o 
                            join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id 
                            join transaction.lineitem li on li.order_id=o.id        where o.status in (2,3,4,5,7,9,10,12,16,17,40) 
                            and o.total_amount > 1 and o.created_timestamp between CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day)  day and CURDATE() 
                            group by c.name with rollup;
                     """
MIS_REPORT_GROUP_PROD_SQL="""
                        select concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product, sum(if(date(o.created_timestamp)=curdate()- interval 1 day, 1, 0)) as lastDayQty, 
                            sum(if(date(o.created_timestamp)=curdate()- interval 1 day, o.total_amount , 0)) lastDayValue, 
                            count(*) as MTDQty, sum(o.total_amount) as MTDValue from transaction.order o 
                            join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id 
                            join transaction.lineitem li on li.order_id=o.id        where o.status in (2,3,4,5,7,9,10,12,16,17,40) 
                            and o.total_amount > 1 and o.created_timestamp between CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day)  day and CURDATE() 
                            group by product with rollup;
                     """
MIS_REPORT_SQL="""
                select c.code,c.name, w.displayName as warehouse, o.id, date(o.created_timestamp) as orderedon, os.statusName , date(mar.activationTime) activatedon, 
                concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount 
                from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id  join user.counter c on c.id = pd.counter_id 
                join transaction.lineitem li on li.order_id=o.id join inventory.warehouse w on w.id = o.fulfilmentWarehouseId 
                join orderstatus os on o.status = os.status left join mobileactivationrecord mar on mar.orderId = o.id 
                where o.status in (2,3,4,5,7,9,10,12,16,17,40) and o.total_amount > 1 
                and o.created_timestamp between CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day)  day and CURDATE() order by orderedon desc, c.code 
                """
date_format = xlwt.XFStyle()
date_format.num_format_str = 'dd/mm/yyyy'

datetime_format = xlwt.XFStyle()
datetime_format.num_format_str = 'dd/mm/yyyy HH:MM AM/PM'

number_format = xlwt.XFStyle()
number_format.num_format_str = '#,##0'




valueformat = "{0:,.0f}"

default_format = xlwt.XFStyle()

shipped_headers=['Counter', 'Product', 'Quantity', 'Value']
tertiary_headers=shipped_headers
pending_headers=shipped_headers
ageing_headers=['Counter','Product','Qty Delivered','Qty Sold','Qty In Stock','Value In Stock','Qty Shipped','Value Shipped', 'Qty Pending', 'Value Pending']
mis_headers=['Counter','Last Day Qty', 'Last Day Value','MTD Qty', 'MTD Value']
mis_headers_prod=['Product','Last Day Qty', 'Last Day Value','MTD Qty', 'MTD Value']

report_map['shipped'] = {'title':'Shipped Orders Report', 'query' : SHIPPED_ORDER_GROUP_SQL, 'group':2, 'headers':shipped_headers}
report_map['tertiary'] = {'title':'Tertiary Report', 'query' : SOLD_OUT_GROUP_SQL, 'group':2, 'headers':tertiary_headers}
report_map['pending'] = {'title':'Pending Orders Report', 'query' : PENDING_ORDER_GROUP_SQL, 'group':2, 'headers':pending_headers}
report_map['ageing'] = {'title':'Spice Stock Ageing Report', 'query' : STOCK_REPORT_GROUP_SQL, 'group':2, 'headers':ageing_headers, 'grandIndex':5}
report_map['mis'] = {'title':'Counter wise Sales Report', 'query' : MIS_REPORT_GROUP_SQL, 'group':1, 'headers':mis_headers}
report_map['mis_prod'] = {'title':'Product wise Sales Report', 'query' : MIS_REPORT_GROUP_PROD_SQL, 'group':1, 'headers':mis_headers_prod}

#recipients = []
recipients = ['rajneesh.arora@saholic.com', 'venky864@gmail.com', 'sandeep.sachdeva@shop2020.in', 'amit.sirohi@shop2020.in', 'khushal.bhatia@saholic.com', 'chaitnaya.vats@saholic.com', 'ritesh.chauhan@shop2020.in']


def getDbConnection():
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
  
def closeConnection(conn):
    conn.close()
    


def sendmail(email, message, fileName, title):
    if email == "":
        return
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()
    
    # Create the container (outer) email message.
    msg = MIMEMultipart()
    msg['Subject'] = title
    msg.preamble = title
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    
    fileMsg = MIMEBase('application','vnd.ms-excel')
    fileMsg.set_payload(file(TMP_FILE).read())
    encoders.encode_base64(fileMsg)
    fileMsg.add_header('Content-Disposition','attachment;filename=' + fileName)
    msg.attach(fileMsg)
    email.append('amit.gupta@shop2020.in')
    MAILTO = email 
    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
    
def getMailAttachmentShipped():
    selectSql = SHIPPED_ORDER_SQL
    conn = getDbConnection()
    try:
        # prepare a cursor object using cursor() method
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(selectSql)
        result = cursor.fetchall()
        createXlsReport(result)
        
    except Exception:
        traceback.print_exc()
        print "Could not execute query"
    
def createXlsReport(result):
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet("Shipped Orders")
    boldStyle = xlwt.XFStyle()
    f = xlwt.Font()
    f.bold = True
    boldStyle.font = f
    column = 0
    row = 0
    
    worksheet.write(row, 0, 'Counter Code', boldStyle)
    worksheet.write(row, 1, 'Counter Name', boldStyle)
    worksheet.write(row, 2, 'Warehouse', boldStyle)
    worksheet.write(row, 3, 'Order Id', boldStyle)
    worksheet.write(row, 4, 'Order Date', boldStyle)
    worksheet.write(row, 5, 'Shipped Date', boldStyle)
    worksheet.write(row, 6, 'Order Status', boldStyle)
    worksheet.write(row, 7, 'Expected Delivery Date', boldStyle)
    worksheet.write(row, 8, 'Product', boldStyle)
    worksheet.write(row, 9, 'Qty', boldStyle)
    worksheet.write(row, 10, 'Value', boldStyle)

    for r in result:
        row += 1
        column = 0
        for data in r :
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)
            column += 1
    workbook.save(TMP_FILE)
    
def getMailAttachmentTertiary():
    selectSql = SOLD_OUT_SQL
    conn = getDbConnection()
    try:
        # prepare a cursor object using cursor() method
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(selectSql)
        result = cursor.fetchall()
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet("Tertiary Report")
        boldStyle = xlwt.XFStyle()
        f = xlwt.Font()
        f.bold = True
        boldStyle.font = f
        column = 0
        row = 0
        
        worksheet.write(row, 0, 'Counter Code', boldStyle)
        worksheet.write(row, 1, 'Counter Name', boldStyle)
        worksheet.write(row, 2, 'Order Id', boldStyle)
        worksheet.write(row, 3, 'Order Date', boldStyle)
        worksheet.write(row, 4, 'Delivered Date', boldStyle)
        worksheet.write(row, 5, 'Activated Timestamp', boldStyle)
        worksheet.write(row, 6, 'Product', boldStyle)
        worksheet.write(row, 7, 'Qty', boldStyle)
        worksheet.write(row, 8, 'Value', boldStyle)
    
        for r in result:
            row += 1
            column = 0
            for data in r :
                worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else datetime_format if type(data) is datetime.datetime else default_format)
                column += 1
        workbook.save(TMP_FILE)
        
    except Exception:
        traceback.print_exc()
        print "Could not execute query"
        
def getMailAttachmentPending():
    selectSql = PENDING_ORDER_SQL
    conn = getDbConnection()
    try:
        # prepare a cursor object using cursor() method
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(selectSql)
        result = cursor.fetchall()
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet("Pending Orders")
        boldStyle = xlwt.XFStyle()
        f = xlwt.Font()
        f.bold = True
        boldStyle.font = f
        column = 0
        row = 0
        
        worksheet.write(row, 0, 'Counter Code', boldStyle)
        worksheet.write(row, 1, 'Counter Name', boldStyle)
        worksheet.write(row, 2, 'Warehouse', boldStyle)
        worksheet.write(row, 3, 'Order Id', boldStyle)
        worksheet.write(row, 4, 'Order Date', boldStyle)
        worksheet.write(row, 5, 'Shipped Date', boldStyle)
        worksheet.write(row, 6, 'Order Status', boldStyle)
        worksheet.write(row, 7, 'Expected Delivery Date', boldStyle)
        worksheet.write(row, 8, 'Product', boldStyle)
        worksheet.write(row, 9, 'Qty', boldStyle)
        worksheet.write(row, 10, 'Value', boldStyle)
    
        for r in result:
            row += 1
            column = 0
            for data in r :
                worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)
                column += 1
        workbook.save(TMP_FILE)
            
    except Exception:
            traceback.print_exc()
            print "Could not execute query"

def getMailAttachmenAgeing():
    selectSql = STOCK_REPORT_SQL
    selectSql1 = STOCK_REPORT_GROUP_SQL_NO_ROLLUP
    conn = getDbConnection()
    try:
        # prepare a cursor object using cursor() method
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(selectSql)
        result = cursor.fetchall()

        cursor.execute(selectSql1)
        result1 = cursor.fetchall()
        
        
        workbook = xlwt.Workbook()
        worksheet1 = workbook.add_sheet("Stock Aeging Summary")
        worksheet = workbook.add_sheet("Stock Aeging Orders")
        boldStyle = xlwt.XFStyle()
        f = xlwt.Font()
        f.bold = True
        boldStyle.font = f
        
        column = 0
        row = 0
        worksheet.write(row, 0, 'Counter Code', boldStyle)
        worksheet.write(row, 1, 'Counter Name', boldStyle)
        worksheet.write(row, 2, 'Order Id', boldStyle)
        worksheet.write(row, 3, 'Order Date', boldStyle)
        worksheet.write(row, 4, 'Delivered Date', boldStyle)
        worksheet.write(row, 5, 'Order Status', boldStyle)
        worksheet.write(row, 6, 'Product', boldStyle)
        worksheet.write(row, 7, 'Qty', boldStyle)
        worksheet.write(row, 8, 'Value', boldStyle)
        worksheet.write(row, 9, 'Ageing(Days)', boldStyle)

        worksheet1.write(row, 0, 'Counter', boldStyle)
        worksheet1.write(row, 1, 'Product', boldStyle)
        worksheet1.write(row, 2, 'Qty Delivered', boldStyle)
        worksheet1.write(row, 3, 'Qty Sold', boldStyle)
        worksheet1.write(row, 4, 'Qty In Stock', boldStyle)
        worksheet1.write(row, 5, 'Value In Stock', boldStyle)
        worksheet1.write(row, 6, 'Qty Shipped', boldStyle)
        worksheet1.write(row, 7, 'Value Shipped', boldStyle)
        worksheet1.write(row, 8, 'Qty Pending', boldStyle)
        worksheet1.write(row, 9, 'Value Pending', boldStyle)
    
        for r in result:
            row += 1
            column = 0
            for data in r :
                worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)
                column += 1

        row=0
        column=0
        for r in result1:
            row += 1
            column = 0
            for data in r :
                worksheet1.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)
                column += 1
        workbook.save(TMP_FILE)
    except Exception:
        traceback.print_exc()
        print "Could not execute query"

def sendShippedReport():
    mailbody = getMailBody(report_map['shipped'])
    getMailAttachmentShipped() 
    sendmail(recipients, mailbody, 'dtrshipped_report.xls', 'DTR Shipped Product Report')

def sendTertiaryReport():
    mailbody = getMailBody(report_map['tertiary'])
    getMailAttachmentTertiary()
    sendmail(recipients, mailbody, 'dtrtertiary_report.xls', 'DTR Tertiary Report')
    
def sendPendingReport():
    mailbody = getMailBody(report_map['pending'])
    getMailAttachmentPending()
    sendmail(recipients, mailbody, 'dtrpending_report.xls', 'DTR Pending Orders Report')

def getSpiceStockAgeingReport():
    mailbody = getMailBody(report_map['ageing'])
    getMailAttachmenAgeing()
    sendmail(recipients, mailbody, 'dtrstockageing_report.xls', 'DTR Spice Stock Ageing Report')
    
def getMisReport():
    mailbody = getMailBody(report_map['mis']) + "<br>" + "<br>" + getMailBody(report_map['mis_prod'])
    getMailAttachmentMis()
    sendmail(['rajneesh.arora@saholic.com','khushal.bhatia@saholic.com'], mailbody, 'dtrcountersale_report.xls', 'DTR Counterwise Sales Report')
    
def getMailBody(reportmap):
    conn = getDbConnection()
    mailBodyTemplate="""
    <html>
        <body>
        <table cellspacing="0" border="1" style="text-align:right">
            <thead>
                <tr>
                    <th colspan="{4}" style="text-align:center">{2}</th>
                    <th colspan="{5}" style="text-align:center">Total Amount - {0}</th>
                </tr>
                <tr>
                    {3}
                </tr>
            </thead>
            <tbody>
                {1}
            </tbody>
        </table>
        </body>
    </html>
    """
    try:
        # prepare a cursor object using cursor() method
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(reportmap['query'])
        result = cursor.fetchall()
        grandTotal = 0
        tbody=[]
        headerLen = len(reportmap['headers'])
        for rowindex in range(-1, len(result)-1):
            row = result[rowindex]
            nullColumn = -1
            tag="<td>"
            closeTag="</td>"
            for grColumn in range(0, reportmap['group']):
                if row[grColumn] is None:
                    tag="""<th text-align="right">"""
                    closeTag="</th>"
                    nullColumn = grColumn
                    break

            tbody.append("<tr>")
            
            grandIndex =  headerLen - 1
            if reportmap.has_key('grandIndex'):
                grandIndex = reportmap['grandIndex']
            for column in range(0, headerLen):      
                tbody.append(tag)
                if nullColumn==column:
                    if nullColumn==0:
                        tbody.append('Grand Total')
                        grandTotal = row[grandIndex]
                    else:
                        tbody.append('Sub Total')
                elif row[column] is None:
                        tbody.append('')
                else:
                    data = row[column]
                    tbody.append(intWithCommas(int(data)) if type(data) is float else (data if data!=0 else '-'))
                tbody.append(closeTag)

            tbody.append("</tr>")
            
        tblbody = ''.join([str(x) for x in tbody])
        theader = ''.join(["<th>%s</th>"%header for header in reportmap['headers']])
        return mailBodyTemplate.format(intWithCommas(int(grandTotal)), tblbody, reportmap['title'], theader, (headerLen)/2, (headerLen+1)/2)
    except:
        traceback.print_exc()
        print "Could not execute query"
    return ""

def getMailAttachmentMis():
    selectSql = MIS_REPORT_SQL
    conn = getDbConnection()
    try:
        # prepare a cursor object using cursor() method
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(selectSql)
        result = cursor.fetchall()
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet("Last Day Sale Report")
        worksheet1 = workbook.add_sheet("MTD Sale Report")
        boldStyle = xlwt.XFStyle()
        f = xlwt.Font()
        f.bold = True
        boldStyle.font = f
        column = 0
        row = 0
        
        worksheet.write(row, 0, 'Counter Code', boldStyle)
        worksheet.write(row, 1, 'Counter Name', boldStyle)
        worksheet.write(row, 2, 'Warehouse', boldStyle)
        worksheet.write(row, 3, 'Order Id', boldStyle)
        worksheet.write(row, 4, 'Order Date', boldStyle)
        worksheet.write(row, 5, 'Order Status', boldStyle)
        worksheet.write(row, 6, 'Activation Date', boldStyle)
        worksheet.write(row, 7, 'Product', boldStyle)
        worksheet.write(row, 8, 'Qty', boldStyle)
        worksheet.write(row, 9, 'Value', boldStyle)
   
        worksheet1.write(row, 0, 'Counter Code', boldStyle)
        worksheet1.write(row, 1, 'Counter Name', boldStyle)
        worksheet1.write(row, 2, 'Warehouse', boldStyle)
        worksheet1.write(row, 3, 'Order Id', boldStyle)
        worksheet1.write(row, 4, 'Order Date', boldStyle)
        worksheet1.write(row, 5, 'Order Status', boldStyle)
        worksheet1.write(row, 6, 'Activation Date', boldStyle)
        worksheet1.write(row, 7, 'Product', boldStyle)
        worksheet1.write(row, 8, 'Qty', boldStyle)
        worksheet1.write(row, 9, 'Value', boldStyle)
        
        yesterday = datetime.date.fromordinal(datetime.date.today().toordinal()-1)
        for r in result:
            row += 1
            column = 0
            for data in r :
                if data is not None:
                    if r[4] == yesterday:
                        worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)
                    worksheet1.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)
                column += 1
        workbook.save(TMP_FILE)
    except Exception:
        traceback.print_exc()
        print "Could not execute query"

    

def main():
    sendShippedReport()
    #print getMailBody()
    sendTertiaryReport()
    sendPendingReport()
    getSpiceStockAgeingReport()
    getMisReport()
    
def intWithCommas(x):
    if type(x) not in [type(0), type(0L)]:
        raise TypeError("Parameter must be an integer.")
    if x==0:
        return '-'
    if x < 0:
        return '-' + intWithCommas(-x)
    result = ''
    while x >= 1000:
        x, r = divmod(x, 1000)
        result = ",%03d%s" % (r, result)
    return "%d%s" % (x, result)


if __name__ == '__main__':
    main()