Subversion Repositories SmartDukaan

'''Created on Mar 10, 2015

@author: amit
'''
from datetime import date
from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import MySQLdb
import datetime
import smtplib
import xlwt



DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "dtr"
TMP_FILE = "/tmp/useractivationreport.xls"  

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


SEGMENTATION_QUERY = """
SELECT id,email,first_name,mobile_number,mobile_verified,referrer,created 
FROM users WHERE lower(referrer) not like 'emp%'
"""
USER_QUERY="""
SELECT id,email,first_name,mobile_number,mobile_verified,referrer,created 
FROM users WHERE (referrer IS NULL AND id > 350) OR lower(referrer) not like 'emp%' 
"""


date_format = xlwt.XFStyle()
date_format.num_format_str = 'yyyy/mm/dd'

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

default_format = xlwt.XFStyle()


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


def generateActivationReport():
    selectSql = USER_QUERY
    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("User")
        boldStyle = xlwt.XFStyle()
        f = xlwt.Font()
        f.bold = True
        boldStyle.font = f
        column = 0
        row = 0
        
        worksheet.write(row, 0, 'User Id', boldStyle)
        worksheet.write(row, 1, 'Email', boldStyle)
        worksheet.write(row, 2, 'Name', boldStyle)
        worksheet.write(row, 3, 'Mobile Number', boldStyle)
        worksheet.write(row, 4, 'Mobile Verified', boldStyle)
        worksheet.write(row, 5, 'Referrer', boldStyle)
        worksheet.write(row, 6, 'Created', 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, datetime_format if type(data) is datetime.datetime else default_format)
                column += 1
        workbook.save(TMP_FILE)
        sendmail(["rajender.singh@shop2020.in",'amit.sirohi@saholic.com','shailesh.kumar@saholic.com'], "", TMP_FILE, SUBJECT)
    except:
        print "Could not create report"


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)

#    fileMsg1 = MIMEBase('application', 'vnd.ms-excel')
#    fileMsg1.set_payload(file("/root/snapdeal.csv" ).read())
#    encoders.encode_base64(fileMsg1)
#    fileMsg1.add_header('Content-Disposition', 'attachment;filename=Affiliate')
#    msg.attach(fileMsg1)
    
    MAILTO = ['rajender.singh@saholic.com','amit.sirohi@saholic.com','shailesh.kumar@saholic.com']
    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())

def main():
    generateActivationReport()
    

if __name__ == '__main__':
    main()