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