Automatically importing emails to SugarCRM using custom logic

Some six months ago I accepted a new position with SugarCRM, which I am enjoying enormously. Here's a bit of code that I've sent out as an example a couple of times when advising developers, so I thought I would share it.


Suppose you want to integrate a legacy email interface into SugarCRM, or that you are using some custom header to identify a customer, or something along those lines. As far as you have a very specific idea of what you want to be doing, you can implement that easily in, for example, Python, as shown below.

import poplib
from email.Parser import Parser
import email as emaillib
import re
from suds.client import Client
import logging

#configure logging
logging.basicConfig(level=logging.INFO)
logging.getLogger('suds.client').setLevel(logging.INFO)

#get email from gmail
host = "pop.gmail.com"
mail = poplib.POP3_SSL(host)
print mail.getwelcome()
print mail.user("SOMETESTEMAILADDR")
print mail.pass_("THEPASSWORD")
print mail.stat()
print mail.list()
print ""

if mail.stat()[1] > 0:
 print "You have new mail."
else:
 print "No new mail."

print ""

#initialize generic email parser
parser = Parser()

#process message list
numMessages = len(mail.list()[1])
if numMessages > 0:

  #initialize SugarCRM Session (login, etc)
  USERNAME= "someusername"
  PASSWORD= "somepassword"

  SUGAR_SOAP_URL = "http://MYSUGARCRMHOST.URL/soap.php"
  SUGAR_WSDL_URL = SUGAR_SOAP_URL + '?wsdl'

  #create proxy for the SOAP calls
  sugar = Client(SUGAR_WSDL_URL)

  #build an authentication object for login
  auth = sugar.factory.create("user_auth")
  auth.user_name = USERNAME
  auth.password = PASSWORD
  auth.version = "1.1"

  #perform login and keep sessionid for later calls
  session = sugar.service.login(auth, "mailarchiver")
  sessionid = session.id

  #iterate through the messages and import them in Sugar, associated to a contact with the email
  #address specified in the "EmailToRelate" field in the body. The association can also function trhough
  #email headers, any type of body data that can be looked up, etc.
  for i in range(numMessages):

    #parse the message
    msg = mail.retr(i+1)[1]
    email = parser.parsestr('\n'.join(msg))

    #find the email address in the body field with a regexp and store it
    bodyText = email.get_payload()
    m = re.search('^EmailToRelate\: (.*)$', bodyText, re.MULTILINE)

    searchemail =  m.group(1)

    #lookup the contact with the email. Normally lookup works easier, but for email addresses of
    #users/leads/contacts use this example
    query = "contacts.id in (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 and ea.email_address = '" + searchemail + "')" 
    response = sugar.service.get_entry_list(sessionid,'Contacts',query, '', 0, ['id'],1,0)
    contactid = response.entry_list[0].id

    #collect the data we will archive in Sugar from the parsed email
    emaildata = [
      {'name' : 'from_addr', 'value' : email.get("From") },
      {'name' : 'to_addrs', 'value' : email.get("To").replace('\n','') },
      {'name' : 'name', 'value' : email.get("Subject") },
      {'name' : 'description', 'value' : email.get_payload() }
    ]

    #store the email in Sugar, and keep its newly generated id in res
    res = sugar.service.set_entry(sessionid, 'Emails', emaildata)

    #associate the newly created email with the contact we looked up
    relval = sugar.factory.create("set_relationship_value")
    relval.module1='Contacts'
    relval.module1_id = contactid
    relval.module2='Emails'
    relval.module2_id=res.id
    sugar.service.set_relationship(sessionid, relval)

#flush and quit
mail.quit()

The code above fetches an email from gmail, parses its body to find a specific "field", EmailToRelate in this case, identifies a record within SugarCRM related to the field, stores the email in SugarCRM, and associates it with the record found. The example illustrates using Python and suds for call the SugarCRM SOAP interface, which is really quite easy. I suspect my Python leaves things to be desired, and I will appreciate corrections.


On a more general note, I recently had a discussion with a partner regarding the most elegant way to archive emails, independent of the email client the end user is using. At the moment, the best way to archive emails is using our interactive Outlook plugin, available for Outlook 2003, 2007 and 2010. Forwarding the emails to a machine readable mailbox, and having them automatically harvested and imported into Sugar is generic enough, but the lack of interactivity might prevent the system from recognising the user's intent, and lead to archiving the email at the wrong level (e.g. at the Contact level, instead of the Opportunity level, etc). My current thinking is that it would be cool to do forwarding and automatic harvesting, but follow it with an interactive popup within SugarCRM, where the proper association can be defined. This would achieve both platform independence and flexibility. In order to achieve this in a scalable manner, however, one needs a real-time notification mechanism for SugarCRM. I my next post, I will demonstrate a way to build such a mechanism using a simple evented node.js notification server.

On the meaning of gwana, a simple trick, and social tools for developers

So, Scott Hanselman gave a talk on the usefulness of blogs and social tools to the community of developers, which prompted me to come back here and give you this post.

On the question of what gwana is – I picked up the word in an article on The Register a few years ago. What I liked about it was that it did not seem to mean anything at all, and was however somehow stuck in my head. Soon after that I was debugging an old CGI webapp written in C. It was a time before I had learned to properly use a debugger, so figuring out which code path was taken took me writing some tracing printfs. Finding my tracing output required putting some marker string in the string I was printing, so I ended up using “gwana” for that. After all, it did not seem likely that it would pop up accidentally somewhere in normal output.

In the tradition of metasyntactic variables, I kept using gwana as my universal marker with no specific meaning. Searching for gwana would take me directly to my tracing outputs, finding it where it didn’t belong meant that I haven’t properly deactivated my tracing statements. Nowadays I catch myself routinely adding it to debug logging statements in order to more easily match a place in code with a line of a log. When having to edit a file without a proper text editor I type in a “gwana” to put a placemark to which I can easily navigate back to.

And so, a nonsensical word became my personal mark in code and text. This kind of made it suitable for the address of this blog.

Getting Google AppEngine update script to work behind a HTTP proxy

Google announced that AppEngine is now open to the public! They also announced the pricing for usage beyond the free 5'000'000 pageviews etc, and it seems, hmmm, competitive :). Either way, I had to try it out.

I had a simple app which I had developed locally in the dev environment Google provides, so I decided to upload this to the cloud. A simple ./appcfg.py update MyApp/ should have been all I need to do. However, I am sitting behind a Squid proxy here, so, Google instructs me to set http_proxy in my environment and fire away. I do as Google tells me to do and fail.

Apparently there is a bug in Python's urllib2, when dealing with HTTPS over Squid. Luckily, patch is available here.

What really got me, and is somewhat puzzling to me still, is that I did not manage to get this running even after this step and doing an:

export http_proxy="http://my.proxyserver.com:3128/"

I had to do also:

export https_proxy="http://my.proxyserver.com:3128/"

And only then it worked. However, now that I got the cookie after the initial login, having only http_proxy set seems to be enough.

Hope this helps! Let me know if you needed the extra environment variable on the first run. I wonder if it was some glitch with my setup, or if there is something more sinister and worthy of some digging in the Python libs.

Cheers,

Andro

Sharenata sianka

Wonderful Bulgarian [post-punk]/[new wave]/[whatever you wanna call it I love it] from the end of the 80s.

Technical test

Here I am, testing Blogger, integration with Disqus, etc.