Subversion Repositories SmartDukaan

Rev

Rev 15121 | Rev 15136 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 15121 Rev 15134
Line 1... Line -...
1 
from email import encoders
 - 
 
2 
from email.mime.audio import MIMEAudio
 - 
 
3 
from email.mime.base import MIMEBase
 - 
 
4 
from email.mime.image import MIMEImage
 - 
 
5 
from email.mime.multipart import MIMEMultipart
 - 
 
6 
from email.mime.text import MIMEText
 - 
 
7 
import mimetypes
 - 
 
8 
import os
 1 
import os
9 
import re
 2 
import re
10 
import smtplib
 3 
import smtplib
11 
import csv
 4 
import csv
12 
import MySQLdb
 5 
import MySQLdb
13 
 
 - 
 
14 
class Email:
 - 
 
15 
    """
 - 
 
16 
    This class handles the creation and sending of email messages
 - 
 
17 
    via SMTP.  This class also handles attachments and can send
 - 
 
18 
    HTML messages.  The code comes from various places around
 - 
 
19 
    the net and from my own brain.
 - 
 
20 
    """
 - 
 
21 
    def __init__(self, smtpServer):
 6 
from dtr.storage import DataService
22 
        """
 - 
 
23 
        Create a new empty email message object.
 - 
 
24 
 
 - 
 
25 
        @param smtpServer: The address of the SMTP server
 - 
 
26 
        @type smtpServer: String
 - 
 
27 
        """
 - 
 
28 
        self._textBody = None
 - 
 
29 
        self._htmlBody = None
 - 
 
30 
        self._subject = ""
 - 
 
31 
        self._smtpServer = smtpServer
 7 
from dtr.storage.DataService import Users
32 
        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])?)$")
 8 
from elixir import *
33 
        self.clearRecipients()
 - 
 
34 
        self.clearAttachments()
 - 
 
35
- 
 
36 
    def send(self):
 - 
 
37 
        """
 - 
 
38 
        Send the email message represented by this object.
 - 
 
39 
        """
 - 
 
40 
        # Validate message
 - 
 
41 
        if self._textBody is None and self._htmlBody is None:
 9 
from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound
42 
            raise Exception("Error! Must specify at least one body type (HTML or Text)")
 - 
 
43 
        if len(self._to) == 0:
 10 
from sqlalchemy.sql import func
44 
            raise Exception("Must specify at least one recipient")
 - 
 
45
- 
 
46 
        # Create the message part
 - 
 
47 
        if self._textBody is not None and self._htmlBody is None:
 - 
 
48 
            msg = MIMEText(self._textBody, "plain")
 - 
 
49 
        elif self._textBody is None and self._htmlBody is not None:
 11 
from sqlalchemy.sql.expression import and_, or_, desc, not_, distinct, cast, \
50 
            msg = MIMEText(self._htmlBody, "html")
 - 
 
51 
        else:
 12 
    between, in_
52 
            msg = MIMEMultipart("alternative")
 - 
 
53 
            msg.attach(MIMEText(self._textBody, "plain"))
 - 
 
54 
            msg.attach(MIMEText(self._htmlBody, "html"))
 - 
 
55 
        # Add attachments, if any
 - 
 
56 
        if len(self._attach) != 0:
 - 
 
57 
            tmpmsg = msg
 - 
 
58 
            msg = MIMEMultipart()
 - 
 
59 
            msg.attach(tmpmsg)
 - 
 
60 
        for fname,attachname in self._attach:
 - 
 
61 
            if not os.path.exists(fname):
 - 
 
62 
                print "File '%s' does not exist.  Not attaching to email." % fname
 - 
 
63 
                continue
 - 
 
64 
            if not os.path.isfile(fname):
 - 
 
65 
                print "Attachment '%s' is not a file.  Not attaching to email." % fname
 - 
 
66 
                continue
 - 
 
67 
            # Guess at encoding type
 - 
 
68 
            ctype, encoding = mimetypes.guess_type(fname)
 - 
 
69 
            if ctype is None or encoding is not None:
 - 
 
70 
                # No guess could be made so use a binary type.
 - 
 
71 
                ctype = 'application/octet-stream'
 - 
 
72 
            maintype, subtype = ctype.split('/', 1)
 - 
 
73 
            if maintype == 'text':
 - 
 
74 
                fp = open(fname)
 - 
 
75 
                attach = MIMEText(fp.read(), _subtype=subtype)
 - 
 
76 
                fp.close()
 - 
 
77 
            elif maintype == 'image':
 - 
 
78 
                fp = open(fname, 'rb')
 - 
 
79 
                attach = MIMEImage(fp.read(), _subtype=subtype)
 - 
 
80 
                fp.close()
 - 
 
81 
            elif maintype == 'audio':
 - 
 
82 
                fp = open(fname, 'rb')
 - 
 
83 
                attach = MIMEAudio(fp.read(), _subtype=subtype)
 - 
 
84 
                fp.close()
 - 
 
85 
            else:
 - 
 
86 
                fp = open(fname, 'rb')
 - 
 
87 
                attach = MIMEBase(maintype, subtype)
 - 
 
88 
                attach.set_payload(fp.read())
 - 
 
89 
                fp.close()
 - 
 
90 
                # Encode the payload using Base64
 - 
 
91 
                encoders.encode_base64(attach)
 - 
 
92 
            # Set the filename parameter
 - 
 
93 
            if attachname is None:
 - 
 
94 
                filename = os.path.basename(fname)
 - 
 
95 
            else:
 - 
 
96 
                filename = attachname
 - 
 
97 
            attach.add_header('Content-Disposition', 'attachment', filename=filename)
 - 
 
98 
            msg.attach(attach)
 - 
 
99 
        # Some header stuff
 - 
 
100 
        msg['Subject'] = self._subject
 - 
 
101 
        msg['From'] = self._from
 - 
 
102 
        msg['To'] = ", ".join(self._to)
 - 
 
103 
        msg.preamble = "You need a MIME enabled mail reader to see this message"
 - 
 
104 
        # Send message
 - 
 
105 
        msg = msg.as_string()
 - 
 
106 
        server = smtplib.SMTP(self._smtpServer)
 - 
 
107 
        server.sendmail(self._from, self._to, msg)
 - 
 
108 
        server.quit()
 - 
 
109
- 
 
110 
    def setSubject(self, subject):
 - 
 
111 
        """
 - 
 
112 
        Set the subject of the email message.
 - 
 
113 
        """
 - 
 
114 
        self._subject = subject
 - 
 
115
- 
 
116 
    def setFrom(self, address):
 - 
 
117 
        """
 - 
 
118 
        Set the email sender.
 - 
 
119 
        """
 - 
 
120 
        if not self.validateEmailAddress(address):
 - 
 
121 
            raise Exception("Invalid email address '%s'" % address)
 - 
 
122 
        self._from = address
 - 
 
123
13
124 
    def clearRecipients(self):
 - 
 
125 
        """
 - 
 
126 
        Remove all currently defined recipients for
 14 
db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
127 
        the email message.
 - 
 
128 
        """
 - 
 
129 
        self._to = []
 15 
cursor = db.cursor()
130
- 
 
131 
    def addRecipient(self, address):
 - 
 
132 
        """
 - 
 
133 
        Add a new recipient to the email message.
 - 
 
134 
        """
 - 
 
135 
        if not self.validateEmailAddress(address):
 - 
 
136 
            raise Exception("Invalid email address '%s'" % address)
 - 
 
137 
        self._to.append(address)
 - 
 
138
- 
 
139 
    def setTextBody(self, body):
 - 
 
140 
        """
 - 
 
141 
        Set the plain text body of the email message.
 - 
 
142 
        """
 - 
 
143 
        self._textBody = body
 - 
 
144
- 
 
145 
    def setHtmlBody(self, body):
 - 
 
146 
        """
 - 
 
147 
        Set the HTML portion of the email message.
 - 
 
148 
        """
 - 
 
149 
        self._htmlBody = body
 - 
 
150
- 
 
151 
    def clearAttachments(self):
 - 
 
152 
        """
 - 
 
153 
        Remove all file attachments.
 - 
 
154 
        """
 - 
 
155 
        self._attach = []
 - 
 
156
- 
 
157 
    def addAttachment(self, fname, attachname=None):
 - 
 
158 
        """
 - 
 
159 
        Add a file attachment to this email message.
 - 
 
160 
 
 16 
 
161 
        @param fname: The full path and file name of the file
 - 
 
162 
                      to attach.
 - 
 
163 
        @type fname: String
 - 
 
164 
        @param attachname: This will be the name of the file in
 - 
 
165 
                           the email message if set.  If not set
 - 
 
166 
                           then the filename will be taken from
 - 
 
167 
                           the fname parameter above.
 - 
 
168 
        @type attachname: String
 - 
 
169 
        """
 - 
 
170 
        if fname is None:
 - 
 
171 
            return
 - 
 
172 
        self._attach.append( (fname, attachname) )
 17 
DataService.initialize(db_hostname="localhost")
173
- 
 
174 
    def validateEmailAddress(self, address):
 - 
 
175 
        """
 - 
 
176 
        Validate the specified email address.
 - 
 
177
18
178 
        @return: True if valid, False otherwise
 - 
 
179 
        @rtype: Boolean
 - 
 
180 
        """
 19 
def main():
181 
        if self._reEmail.search(address) is None:
 - 
 
182 
            return False
 - 
 
183 
        return True
 - 
 
184
- 
 
185 
if __name__ == "__main__":
 - 
 
186 
    # Run some tests
 - 
 
187 
    mFrom = "dtr@shop2020.in"
 - 
 
188 
    mTo = ["manish.sharma@shop2020.in"]
 - 
 
189 
    m = Email('localhost')
 - 
 
190 
    m.setFrom(mFrom)
 - 
 
191 
    for receipient in mTo:
 - 
 
192 
        m.addRecipient(receipient)
 - 
 
193 
    #reader = csv.reader(open('/root/UserGroupData.tsv'), delimiter="\t" )
 - 
 
194
- 
 
195 
    db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
 - 
 
196 
    cursor = db.cursor()
 - 
 
197
- 
 
198 
    rownum = 0
 - 
 
199
- 
 
200 
    message="""<html>
 - 
 
201 
                <body>"""
 - 
 
202
- 
 
203 
    message +="""<table>"""
 - 
 
204 
    message +="""<tr>"""
 - 
 
205 
    message +="""<th>User Id</th>"""
 - 
 
206
- 
 
207
- 
 
208 
    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"
 20 
    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"
209
21
210 
    cursor.execute(sql)
 22 
    cursor.execute(sql)
211 
    result_data = cursor.fetchall()
 23 
    result_data = cursor.fetchall()
212 
    if result_data:
 24 
    if result_data:
213 
        for record_data in result_data:
 25 
        for record_data in result_data:
- 
 
 26 
            users = Users.query.filter(Users.id.in_(record_data[0]))
- 
 
 27 
            existingGroupId = 0
- 
 
 28 
            for user in users:
- 
 
 29 
                if user.userGroupId and user.userGroupId >0:
- 
 
 30 
                    existingGroupId = user.userGroupId
- 
 
 31 
                    break
- 
 
 32 
            if existingGroupId >0:
- 
 
 33 
                checkForExistingUserGroup = "select * from usergroup where id=%d"%(existingGroupId)
- 
 
 34 
                cursor.execute(checkForExistingUserGroup)
- 
 
 35 
                existingUserGroup = cursor.fetchone()
- 
 
 36 
                updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], record_data[1], record_data[2], record_data[3], existingUserGroup[0])
- 
 
 37 
                try:
- 
 
 38 
                    cursor.execute(updateUserGroup)
- 
 
 39 
                    db.commit()
- 
 
 40 
                except:
- 
 
 41 
                    # Rollback in case there is any error
- 
 
 42 
                    db.rollback()
- 
 
 43
- 
 
 44 
                for user in users:
- 
 
 45 
                    if not user.userGroupId or user.userGroupId==0:
- 
 
 46 
                        user.userGroupId = existingGroupId
- 
 
 47 
                    session.commit()
- 
 
 48 
            else:
- 
 
 49 
                updateUserGroupUserIds = False
- 
 
 50 
                updateUserGroupMobileNos = False
214 
            updateUserGroup = False
 51 
                updateUserGroupImeis = False
- 
 
 52 
                updateUserGroupReferrers = False
215 
            print record_data[0]
 53 
                print record_data[0]
216 
            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])
 54 
                checkForCurrentRecord = "select * from usergroup where mobileNos like '%%%s%%' or imeis like '%%%s%%' or referrers like '%%%s%%'"%(record_data[1], record_data[2], record_data[3])
217 
            print checkForCurrentRecord
 55 
                print checkForCurrentRecord
218 
            '''
 56
219 
            cursor.execute(checkForCurrentRecord)
 57 
                cursor.execute(checkForCurrentRecord)
220 
            currentRecord = cursor.fetchone()
 58 
                currentRecord = cursor.fetchone()
221 
            if currentRecord is not None:
 59 
                if currentRecord is not None:
222 
                if currentRecord[0] != record_data[0]:
 60 
                    if currentRecord[1] != record_data[0]:
- 
 
 61 
                        updateUserGroupUserIds = True
- 
 
 62 
                    if currentRecord[2] != record_data[1]:
- 
 
 63 
                        updateUserGroupMobileNos = True
- 
 
 64 
                    if currentRecord[3] != record_data[2]:
223 
                    updateUserGroup = True
 65 
                        updateUserGroupImeis = True
- 
 
 66 
                    if currentRecord[4] != record_data[3]:
- 
 
 67 
                        updateUserGroupReferrers = True
224 
            '''
 68 
                    updateUserGroup = None
- 
 
 69 
                    if updateUserGroupUserIds and updateUserGroupMobileNos and updateUserGroupImeis and updateUserGroupReferrers:
225 
            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])
 70 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], record_data[1], record_data[2], record_data[3], currentRecord[0])
- 
 
 71 
                    elif not updateUserGroupUserIds and not updateUserGroupMobileNos and not updateUserGroupImeis and not updateUserGroupReferrers:
226 
            print sql
 72 
                        updateUserGroup =None
- 
 
 73 
                    elif updateUserGroupUserIds and updateUserGroupMobileNos and updateUserGroupImeis and not updateUserGroupReferrers:
- 
 
 74 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], record_data[1], record_data[2], currentRecord[4], currentRecord[0])
- 
 
 75 
                    elif updateUserGroupUserIds and updateUserGroupMobileNos and not updateUserGroupImeis and not updateUserGroupReferrers:
- 
 
 76 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], record_data[1], currentRecord[3], currentRecord[4], currentRecord[0])
- 
 
 77 
                    elif updateUserGroupUserIds and not updateUserGroupMobileNos and not updateUserGroupImeis and not updateUserGroupReferrers:
- 
 
 78 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], currentRecord[2], currentRecord[3], currentRecord[4], currentRecord[0])
- 
 
 79 
                    elif not updateUserGroupUserIds and updateUserGroupMobileNos and not updateUserGroupImeis and not updateUserGroupReferrers:
- 
 
 80 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], record_data[1], currentRecord[3], currentRecord[4], currentRecord[0])
- 
 
 81 
                    elif not updateUserGroupUserIds and updateUserGroupMobileNos and updateUserGroupImeis and not updateUserGroupReferrers:
- 
 
 82 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], record_data[1], record_data[2], currentRecord[4], currentRecord[0])
- 
 
 83 
                    elif not updateUserGroupUserIds and updateUserGroupMobileNos and not updateUserGroupImeis and updateUserGroupReferrers:
- 
 
 84 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], record_data[1], currentRecord[3], record_data[3], currentRecord[0])
- 
 
 85 
                    elif not updateUserGroupUserIds and not updateUserGroupMobileNos and updateUserGroupImeis and updateUserGroupReferrers:
- 
 
 86 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], currentRecord[2], record_data[2], record_data[3], currentRecord[0])
- 
 
 87 
                    elif not updateUserGroupUserIds and not updateUserGroupMobileNos and updateUserGroupImeis and not updateUserGroupReferrers:
- 
 
 88 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], currentRecord[2], record_data[2], currentRecord[4], currentRecord[0])
- 
 
 89 
                    elif not updateUserGroupUserIds and not updateUserGroupMobileNos and not updateUserGroupImeis and updateUserGroupReferrers:
- 
 
 90 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], currentRecord[2], currentRecord[3], record_data[3], currentRecord[0])
- 
 
 91 
                    elif not updateUserGroupUserIds and updateUserGroupMobileNos and updateUserGroupImeis and updateUserGroupReferrers:
- 
 
 92 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], record_data[1], record_data[2], record_data[3], currentRecord[0])
- 
 
 93 
                    elif updateUserGroupUserIds and not updateUserGroupMobileNos and updateUserGroupImeis and not updateUserGroupReferrers:
- 
 
 94 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], currentRecord[2], record_data[2], currentRecord[4], currentRecord[0])
- 
 
 95 
                    elif updateUserGroupUserIds and not updateUserGroupMobileNos and updateUserGroupImeis and updateUserGroupReferrers:
- 
 
 96 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], currentRecord[2], record_data[2], record_data[3], currentRecord[0])
- 
 
 97 
                    elif updateUserGroupUserIds and not updateUserGroupMobileNos and not updateUserGroupImeis and updateUserGroupReferrers:
- 
 
 98 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], currentRecord[2], currentRecord[3], record_data[3], currentRecord[0])
227 
            '''
 99
- 
 
 100 
                    if updateUserGroup is not None:
228 
            try:
 101 
                        try:
229 
                cursor.execute(sql)
 102 
                            cursor.execute(updateUserGroup)
230 
                db.commit()
 103 
                            db.commit()
231 
            except:
 104 
                        except:
232 
                # Rollback in case there is any error
 105 
                            # Rollback in case there is any error
233 
                db.rollback()
 106 
                            db.rollback()
234 
            '''
 107
235 
'''
- 
 
236 
    # initialize rownum variable
 108 
                    for user in users:
- 
 
 109 
                        if not user.userGroupId or user.userGroupId==0:
237 
    rownum = 0
 110 
                            user.userGroupId = currentRecord[0]
238
- 
 
239 
    message="""<html>
 111 
                        session.commit()
240 
                <body>"""
 112 
                else:
241
- 
 
- 
 
 113 
                    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])
242 
    message +="""<table>"""
 114 
                    print sql
243
115
244 
    # generate table contents
 116 
                    try:
245 
    for row in reader: # Read a single row from the CSV file
 117 
                        cursor.execute(sql)
246 
        if rownum == 0:
 - 
 
247 
            message +="""<tr>"""
 118 
                        db.commit()
248 
            for column in row:
 119 
                    except:
249 
                message +="""<th> """ + column + """ </th>"""
 120 
                        # Rollback in case there is any error
250 
            message +="""</tr>"""
 121 
                        db.rollback()
251
- 
 
252 
        #write all other rows
122
253 
        else:
 - 
 
- 
 
 123 
                    userGroupRecordSql = "select * from usergroup where mobileNos like '%%%s%%' or imeis like '%%%s%%' or referrers like '%%%s%%'"%(record_data[1], record_data[2], record_data[3])
254 
            message +="""<tr>"""
124 
                    cursor.execute(userGroupRecordSql)
255 
            for column in row:
 125
256 
                message +="""<th> """ + column + """ </th>"""
 126 
                    userGroupRecord = cursor.fetchone()
257 
            message +="""</tr>"""
 127
258
- 
 
259 
        #increment row count
128 
                    for user in users:
260 
        rownum += 1
 - 
 
261
- 
 
262 
    # write </table> tag
 129 
                        if not user.userGroupId or user.userGroupId==0:
263 
    message +="""</table>
 130 
                            user.userGroupId = userGroupRecord[0]
264 
                </body>
 131 
                        session.commit()
265 
                </html>"""
 132
266
133 
    db.close()
267 
    # Simple Plain Text Email
 134 
    if session.is_active:
268 
    m.setSubject("User Group Data")
 135 
        print "session is active. closing it."
269 
    m.setTextBody(None)
 136 
        session.close()
- 
 
 137 
 
270 
    m.setHtmlBody(message)
 138 
if __name__=='__main__':
271 
    m.addAttachment("/root/Snapdeal_Pending_Orders_Report_Analysis.xls")
 - 
 
272 
    m.send()
 139 
    main()
273 
'''
 - 
 
274 

            140