Subversion Repositories SmartDukaan

Rev

Rev 7717 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed 

'''
Created on 08-Jul-2013

@author: anupam
'''

import json
import urllib2, cookielib
import MySQLdb
import datetime
import sys
import smtplib
import xlwt

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 = "localhost"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "sales"

# KEY NAMES
MONTHNAME = 'monthname'
DATES = 'dates'

MAILTO = ['anupam.singh@shop2020.in']
SENDER = "cnc.center@shop2020.in"
PASSWORD = "5h0p2o2o"
SUBJECT = "Recharge report for week ending on " + datetime.datetime.today()
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
                """ 
        column = 0
        grossTotal = 0
        grossQuantity = 0
        clusterData = []
        currentEmail = ""
        for r in result:
            msg = '<tr><td colspan="1"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr><tr>'
            for data in r:
                if column == 0:
                    if currentEmail != data:
                        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)
                        currentEmail = data
                        
                if column == 2 :
                    grossQuantity += data
                if column == 3 :
                    grossTotal += data
                msg = msg + '<td>' + str(data) + '</td>'
                column += 1
            clusterData.append(r)
            column = 0
            
        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 "Error: unable to fetch data"
        print e
    
    return msg, result

def createXlsReport(result):
    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 result:
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
        #dayofmonth = r[8]
        for data in r :
            if column == 1 :
                grossQuantity += data
            if column == 2 :
                grossTotal += data
            worksheet.write(row, column, str(data))
            column += 1
        column = 0
        row += 1
    
    worksheet.write_merge(1, 1, 'Total')
    worksheet.write(1, 2, str(grossQuantity), boldStyle)
    worksheet.write(1, 3, str(grossTotal), boldStyle)
    workbook.save(TMP_FILE)

def sendmail(email, message):
    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)

    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())

def main():
    message, result = getProductSaleData()
    createXlsReport(result)
    sendmail(message)

if __name__ == '__main__':
    main()