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()