Subversion Repositories SmartDukaan

from email import encoders

from email.mime.audio import MIMEAudio

from email.mime.base import MIMEBase

from email.mime.image import MIMEImage

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

import mimetypes

import os

import re

import smtplib

import csv

import MySQLdb

class Email:

    """

    This class handles the creation and sending of email messages

    via SMTP.  This class also handles attachments and can send

    HTML messages.  The code comes from various places around

    the net and from my own brain.

    """

    def __init__(self, smtpServer):

        """

        Create a new empty email message object.

        @param smtpServer: The address of the SMTP server

        @type smtpServer: String

        """

        self._textBody = None

        self._htmlBody = None

        self._subject = ""

        self._smtpServer = smtpServer

        self._reEmail = re.compile("^([\\w \\._]+\\<[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\\>|[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?)$")

        self.clearRecipients()

        self.clearAttachments()

    def send(self):

        """

        Send the email message represented by this object.

        """

        # Validate message

        if self._textBody is None and self._htmlBody is None:

            raise Exception("Error! Must specify at least one body type (HTML or Text)")

        if len(self._to) == 0:

            raise Exception("Must specify at least one recipient")

        # Create the message part

        if self._textBody is not None and self._htmlBody is None:

            msg = MIMEText(self._textBody, "plain")

        elif self._textBody is None and self._htmlBody is not None:

            msg = MIMEText(self._htmlBody, "html")

        else:

            msg = MIMEMultipart("alternative")

            msg.attach(MIMEText(self._textBody, "plain"))

            msg.attach(MIMEText(self._htmlBody, "html"))

        # Add attachments, if any

        if len(self._attach) != 0:

            tmpmsg = msg

            msg = MIMEMultipart()

            msg.attach(tmpmsg)

        for fname,attachname in self._attach:

            if not os.path.exists(fname):

                print "File '%s' does not exist.  Not attaching to email." % fname

                continue

            if not os.path.isfile(fname):

                print "Attachment '%s' is not a file.  Not attaching to email." % fname

                continue

            # Guess at encoding type

            ctype, encoding = mimetypes.guess_type(fname)

            if ctype is None or encoding is not None:

                # No guess could be made so use a binary type.

                ctype = 'application/octet-stream'

            maintype, subtype = ctype.split('/', 1)

            if maintype == 'text':

                fp = open(fname)

                attach = MIMEText(fp.read(), _subtype=subtype)

                fp.close()

            elif maintype == 'image':

                fp = open(fname, 'rb')

                attach = MIMEImage(fp.read(), _subtype=subtype)

                fp.close()

            elif maintype == 'audio':

                fp = open(fname, 'rb')

                attach = MIMEAudio(fp.read(), _subtype=subtype)

                fp.close()

            else:

                fp = open(fname, 'rb')

                attach = MIMEBase(maintype, subtype)

                attach.set_payload(fp.read())

                fp.close()

                # Encode the payload using Base64

                encoders.encode_base64(attach)

            # Set the filename parameter

            if attachname is None:

                filename = os.path.basename(fname)

            else:

                filename = attachname

            attach.add_header('Content-Disposition', 'attachment', filename=filename)

            msg.attach(attach)

        # Some header stuff

        msg['Subject'] = self._subject

        msg['From'] = self._from

        msg['To'] = ", ".join(self._to)

        msg.preamble = "You need a MIME enabled mail reader to see this message"

        # Send message

        msg = msg.as_string()

        server = smtplib.SMTP(self._smtpServer)

        server.sendmail(self._from, self._to, msg)

        server.quit()

    def setSubject(self, subject):

        """

        Set the subject of the email message.

        """

        self._subject = subject

    def setFrom(self, address):

        """

        Set the email sender.

        """

        if not self.validateEmailAddress(address):

            raise Exception("Invalid email address '%s'" % address)

        self._from = address

    def clearRecipients(self):

        """

        Remove all currently defined recipients for

        the email message.

        """

        self._to = []

    def addRecipient(self, address):

        """

        Add a new recipient to the email message.

        """

        if not self.validateEmailAddress(address):

            raise Exception("Invalid email address '%s'" % address)

        self._to.append(address)

    def setTextBody(self, body):

        """

        Set the plain text body of the email message.

        """

        self._textBody = body

    def setHtmlBody(self, body):

        """

        Set the HTML portion of the email message.

        """

        self._htmlBody = body

    def clearAttachments(self):

        """

        Remove all file attachments.

        """

        self._attach = []

    def addAttachment(self, fname, attachname=None):

        """

        Add a file attachment to this email message.

        @param fname: The full path and file name of the file

                      to attach.

        @type fname: String

        @param attachname: This will be the name of the file in

                           the email message if set.  If not set

                           then the filename will be taken from

                           the fname parameter above.

        @type attachname: String

        """

        if fname is None:

            return

        self._attach.append( (fname, attachname) )

    def validateEmailAddress(self, address):

        """

        Validate the specified email address.

        @return: True if valid, False otherwise

        @rtype: Boolean

        """

        if self._reEmail.search(address) is None:

            return False

        return True

if __name__ == "__main__":

    # Run some tests

    mFrom = "dtr@shop2020.in"

    mTo = ["manish.sharma@shop2020.in"]

    m = Email('localhost')

    m.setFrom(mFrom)

    for receipient in mTo:

        m.addRecipient(receipient)

    #reader = csv.reader(open('/root/UserGroupData.tsv'), delimiter="\t" )

    db = MySQLdb.connect('localhost',"root","shop2020","dtr" )

    cursor = db.cursor()

    rownum = 0

    message="""<html>

                <body>"""

    message +="""<table>"""

    message +="""<tr>"""

    message +="""<th>User Id</th>"""

    sql = "select GROUP_CONCAT( distinct x.user_ids ) user_ids, x.mobileNos, GROUP_CONCAT( distinct x.imeis) imeis, GROUP_CONCAT(distinct x.referrers) referrers from (SELECT GROUP_CONCAT( distinct u.id ) user_ids, GROUP_CONCAT(distinct u.mobile_number) mobileNos, d.imeinumber imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM  `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is not null and u.mobile_number is not null GROUP BY d.imeinumber) as x group by x.mobileNos UNION SELECT GROUP_CONCAT( distinct u.id ) user_ids, u.mobile_number mobileNos, GROUP_CONCAT( distinct d.imeinumber) imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM  `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is null and u.mobile_number is not null GROUP BY u.mobile_number UNION SELECT GROUP_CONCAT( distinct u.id ) user_ids, u.mobile_number mobileNos, GROUP_CONCAT( distinct d.imeinumber) imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM  `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is null and u.mobile_number is null and LOWER(u.referrer) not like 'emp%' and LOWER(u.referrer) not like 'crm%' and LOWER(u.referrer) not like 'fos%' GROUP BY u.referrer"

    cursor.execute(sql)

    result_data = cursor.fetchall()

    if result_data:

        for record_data in result_data:

            updateUserGroup = False

            print record_data[0]

            checkForCurrentRecord = "select * from usergroup where user_ids like '%%%s%%' or mobileNos like '%%%s%%' or imeis like '%%%s%%' or referrers like '%%%s%%'"%(record_data[0], record_data[1], record_data[2], record_data[3])

            print checkForCurrentRecord

            '''

            cursor.execute(checkForCurrentRecord)

            currentRecord = cursor.fetchone()

            if currentRecord is not None:

                if currentRecord[0] != record_data[0]:

                    updateUserGroup = True

            '''

            sql = "insert into usergroup (user_ids, mobileNos, imeis, referrers) values('%s', '%s', '%s', '%s')"%(record_data[0], record_data[1], record_data[2], record_data[3])

            print sql

            '''

            try:

                cursor.execute(sql)

                db.commit()

            except:

                # Rollback in case there is any error

                db.rollback()

            '''

'''    

    # initialize rownum variable

    rownum = 0

    message="""<html>

                <body>"""

    message +="""<table>"""

    # generate table contents

    for row in reader: # Read a single row from the CSV file

        if rownum == 0:

            message +="""<tr>"""

            for column in row:

                message +="""<th> """ + column + """ </th>"""

            message +="""</tr>"""

        #write all other rows    

        else:

            message +="""<tr>"""   

            for column in row:

                message +="""<th> """ + column + """ </th>"""

            message +="""</tr>"""

        #increment row count    

        rownum += 1

    # write </table> tag

    message +="""</table>

                </body>

                </html>"""

    # Simple Plain Text Email

    m.setSubject("User Group Data")

    m.setTextBody(None)

    m.setHtmlBody(message)

    m.addAttachment("/root/Snapdeal_Pending_Orders_Report_Analysis.xls")

    m.send()

'''