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 = "User_Invite_Report.xls"

LIST_EMAILS = ['rajender.singh@shop2020.in','shailesh.kumar@shop2020.in,amit.sirohi@shop2020.in,ritesh.chauhan@shop2020.in']

# KEY NAMES

SENDER = "cnc.center@shop2020.in"

PASSWORD = "5h0p2o2o"

SUBJECT = "User Invite Activation Report for " + date.today().isoformat()

SMTP_SERVER = "smtp.gmail.com"

SMTP_PORT = 587    

USER_INVITE_QUERY="""

SELECT id,email,mobile_number,activated,created FROM `users` WHERE activated!=1

"""

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

    selectSql = USER_INVITE_QUERY

    conn = getDbConnection()

    cursor = conn.cursor()

    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, 'ID', boldStyle)

    worksheet.write(row, 1, 'Email', boldStyle)

    worksheet.write(row, 2, 'Mobile Number', boldStyle)

    worksheet.write(row, 3, 'Activated', boldStyle)

    worksheet.write(row, 4, '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','rajneesh.arora@saholic.com','shailesh.kumar@shop2020.in,amit.sirohi@shop2020.in,ritesh.chauhan@shop2020.in'], "", TMP_FILE, SUBJECT)

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)	

    MAILTO=['rajender.singh@shop2020.in','rajneesh.arora@saholic.com','shailesh.kumar@shop2020.in,amit.sirohi@shop2020.in,ritesh.chauhan@shop2020.in']

    mailServer.login(SENDER, PASSWORD)

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

def main():

    generateInviteReport()

if __name__ == '__main__':

    main()