Subversion Repositories SmartDukaan

Rev

Rev 10298 | Blame | Compare with Previous | Last modification | View Log | RSS feed 

'''
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 pyExcelerator import Workbook, Font, XFStyle
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/recharge_report.xls"

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

def getProductSaleData():
    selectSql = '''SELECT clusterEmail, s.name, COUNT(*) AS TransactionCount, SUM(amount) AS Amount 
                   FROM rechargetransaction r 
                   JOIN hotspotstore h ON h.id = r.storeId 
                   JOIN serviceprovider s ON s.id = r.operatorId 
                   WHERE transactionTime BETWEEN SUBDATE(CURDATE(), 7) AND CURDATE() AND r.status = 5 
                   GROUP BY clusterEmail, operatorId;
                '''
    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()
        msg =   """\
                    <html>
                    <body>\n<table border="1">
                    \n<thead>\n
                    <th>Operator</th>\n
                    <th>Count</th>\n
                    <th>Amount</th>\n
                    </thead>\n
                    <tr>
                    <td colspan="1"><b>Total</b></td><td>======QuantityToBeReplaced======</td>
                    <td>======ValueToBeReplaced======</td>
                    </tr>
                    <tr>
                """ 
        column = 0
        grossTotal = 0
        grossQuantity = 0
        clusterData = []
        currentEmail = ""
        for r in result:
            for data in r:
                if column == 0:
                    if currentEmail != data:
                        #if email id has changed then we need to create XLS
                        createXlsReport(clusterData)
                        #and close the message
                        msg = msg + '</tr></table>\n</body>\n</html>'
                        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
                        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
                        #and send the mail
                        sendmail(currentEmail, msg)
                        
                        #Now we reset fields
                        grossQuantity = 0
                        grossTotal = 0
                        clusterData = []
                        currentEmail = data
                        msg =   """\
                                    <html>
                                    <body>\n<table border="1">
                                    \n<thead>\n
                                    <th>Operator</th>\n
                                    <th>Count</th>\n
                                    <th>Amount</th>\n
                                    </thead>\n
                                    <tr>
                                    <td colspan="1"><b>Total</b></td><td>======QuantityToBeReplaced======</td>
                                    <td>======ValueToBeReplaced======</td>
                                    </tr>
                                    <tr>
                                """ 
                    #We need not do anything for this column so we just move on
                    column += 1
                    continue
                
                if column == 2 :
                    grossQuantity += data
                if column == 3 :
                    grossTotal += data
                msg = msg + '<td>' + str(data) + '</td>'
                column += 1
            msg = msg + '</tr><tr>'
            #Append this row to a list which will be used to create a xls sheet per cluster
            clusterData.append(r)
            column = 0
            
        #After the last row we need to send mail for last cluster
        createXlsReport(clusterData)
        msg = msg + '</table>\n</body>\n</html>'
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
        grossQuantity = 0
        grossTotal = 0
        sendmail(currentEmail, msg)
        
    except Exception as e:
        print e
        traceback.print_exc()
    
    return msg, result

def createXlsReport(resultSet):
    if len(resultSet) == 0:
        return
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet("ClusterReport")
    boldStyle = xlwt.XFStyle()
    f = xlwt.Font()
    f.bold = True
    boldStyle.font = f
    column = 0
    row = 0
    
    worksheet.write(row, 0, 'Operator', boldStyle)
    worksheet.write(row, 1, 'Count', boldStyle)
    worksheet.write(row, 2, 'Amount', boldStyle)
    row = 2
    grossTotal = 0
    grossQuantity = 0
    
    for r in resultSet:
        for data in r :
            if column == 0:
                column += 1
                continue
            if column == 2 :
                grossQuantity += int(data)
            if column == 3 :
                grossTotal += int(data)
            worksheet.write(row, column - 1, str(data))
            column += 1
        column = 0
        row += 1
    
    worksheet.write(1, 0, 'Total')
    worksheet.write(1, 1, str(grossQuantity), boldStyle)
    worksheet.write(1, 2, str(grossTotal), boldStyle)
    workbook.save(TMP_FILE)

def sendmail(email, message):
    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'] = SUBJECT + ' for ' + email
    msg['From'] = "ClusterWiseReport@saholic.com"
    msg['To'] = 'cluster-heads@saholic.com'
    msg.preamble = SUBJECT + ' for ' + email
    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=recharge-report-for-week-ending-on-' + date.today().isoformat() + '.xls')
    msg.attach(fileMsg)
    
    MAILTO = [email, 'kshitij.sood@shop2020.in', 'digamber.chauhan@spiceretail.co.in', 'adarsh.verma@spiceretail.co.in']
    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())

def main():
    getProductSaleData()

if __name__ == '__main__':
    main()