Overview

The goal of this feature is to develop a mechanism for users to import email into xTuple and have it be automatically associated with appropriate contacts and documents in the database.  The solution should allow users to continue to use their preferred mail clients, but allow them to control what mail they want imported into xTuple.  Once imported, users should also be able to manually associate mail to additional documents such as incidents, orders and quotes.

The strategy to accomplish this will involve two main components.  The first part will be to enhance a new open source email project called Trojita to be used as a tool to import mail into the xTuple database.  Trojita is a lightweight GPL licensed IMAP E-mail client written in C++ with Qt.  Today it can be used to receive, organize and send mail against any standard IMAP server.  Trojita will be enhanced to allow users to select personal IMAP folders to export to a Postgres database.   Because Trojita is working against an IMAP server, users will be able to continue using any mail client of their choice.  The synchronization from Trojita to xTuple will be performed whenever Trojita is launched.  As a second stage development, Trojita will be enhanced so that the synchronization may be run as a service in the background so it will be unnecessary for users to launch the client to export their mail.

The second part of the enhancement will be to enhance xTuple Connect to receive mail exported to it by Trojita.  A series of email tables will be added to Connect to receive the exported mail.  As mail is imported it will be processed by business logic in Connect that associates recognized email addresses with their respective contacts.  Additionally, Connect will scan the subject and body for predefined document reference identifiers and, if any are found, will associate the mail with those documents.  An Email tab will be added to the contacts window and other documents that have contact associations such as Sales Orders, Incident and Opportunities so that users can quickly view email associated with those records.  When the email address is clicked from the contact cluster or widget that launches the users preferred Email client, a document reference will automatically be inserted into the subject line that can later be used as a reference to associate mail imported back into xTuple with that document.  This reference should continue to work as users continue to volley email back and forth from their email client using standard reply mechanisms.

Finally, email sent by Connect itself, such as sales order acknowledgements and incident updates, should insert a record intto the email tables as well.

Functional Requirements

Trojita Client Requirements

• Add database settings for xTuple (Postgres) login.
• Allow a user to specify a folder to synchronize the database.
• Provide a visual cue the folder is selected.
• Export mail from selected folders to database when mail is refreshed/fetched.
• A check should exist on the mail identifier to ensure it has not already been imported by another session or client.

Trojita Service Requirements

• Trojita service daemon automatically starts when user logs into their OS.
• Automatically connects to IMAP server, fetches mail, and exports to xTuple database per client settings.
• Changes to client settings immediately affect service.
• Most work on Windows, Macintosh and Linux as the client does.

xTuple Connect Requirements

• E-mail repository tables must be added to the xtbatch schema
• Tables will include triggers to generate associations when mail is inserted
• Add E-mail tab that displays E-mail associations to the following windows:
  • Contact
  • CRM Account
  • Customer
  • Vendor
  • Employee
  • Opportunity
  • Project
  • Quote
  • Sales Order
  • Transfer Order
  • Purchase Order
  • Return Authorization
  • To Do Item
  • Incident
• Users will be able to manually associate mail to documents on the E-mail tab.
• Users will be able to view E-mail detail from the E-mail tab.
• Users will be able to create new messages or respond to existing messages from the email tab using their local email client.
• E-Mail sent by either clicking on the contact E-mail address or via the Connect E-mail sender will generate E-mail records and associations.

Related Existing Functionality

The idea of a scripted widget as used in xtConnect for the "edirelation" widget will be used to create an Email Widget that can be re-used in multiple windows.

The convention of viewing a document by saving a copy to a temporary directory then opening as implemented by the documents widget and the Gantt chart in the Time and Expense extension will be used to view mail in a local email client.

The ability to launch an email client using QDesktop services in the contact widget will be used to create email messages pre-populated with text in the users local client. The script toolbox also already has the necessary support to accomplish this.

Conflicting Features

 xTuple Connect already has a rudimentary email interface for sending automated messages such as reports and responses to incidents.  It may be confusing to users that under certain circumstances they will be presented with that window, and under others they will be presented with their native E-mail client

User-Level Functionality

As described above, there are two halves to this development effort.  One side involves modifying the Trojita IMAP client to support mail export to xTuple.  The second half involves modifying xTuple Connect to support the storage and presentation of imported mail. Links to the UI files pictured below are:

settings.ui

emailWidget.ui

emailAttach.ui

WindowChanges

Trojita Window Changes

The Trojita Settings window will include a new tab to specify database connection settings as pictured.  Code required to support Enhanced Authentication and SSL connections may be borrowed from the xTuple client code base.  The port should require a number 1-9999 and the password should use password echo.  A "Test Connection" button should be included to allow users to validate their connection.

The Trojita IMAP client (v.02) is pictured below.  When a valid database has been setup, a new checkable option should exist on right click beneath a separator to "Synchronize to Database."  When a folder has been flagged, a visual queue should be added to the folder icon such as a rightward pointing or circular arrow.

The toolbar buttons will operate as follows:

• New - Start a new message in the user's e-mail client with the To: address populated with the contact address on the document (if applicable) and an xTuple document reference in the subject.
• Print - Prints the selected email.
• Reply - Start a new message in the user's e-mail client with the To:  address populated with the From address on the selected message.  Subject will be pre-pended with RE: and the xTuple document reference if it does not already exist on the subject.  Text from the body of the selected message will be added to the body of the reply message.
• Reply All - Same as reply except all addresses on the Cc column will be copied to the new message as well.
• Forward - Same as reply except prepend the subject with FW: and no addresses will be populated.
• Attach - Open an email attach dialog window that allows users to search messages by contact and associate them to the current document.
• Detach - Removes the association of the selected message from the document.

Attach Email Dialog

An interactive dialog window will be created to allow users to search for messages by contact to attach to other documents.  The contact widget will allow users to search for the desired contact, while a search box will allow them to further narrow their result set.  The search box will search on address fields, subject and the body fields in the database.  No result set will be returned unless a contact is selected.  The mail result set and corresponding message will be displayed in the same arrangement as the contact widget in a vertical splitter.

The Ok button will only be enabled when a valid message is selected.  When Ok is clicked an association will be made between the document calling the dialog and the selected email.

Contact Cluster

Currently when the E-mail address of a contact cluster is clicked, it launches the user's email client with the address of the contact populated in the "To" address.  xTuple Connect should set the subject text to include a document reference as defined in the algorithms section below.

Submit EDI

The submit EDI window in xTuple connect should be modified so that the document reference as defined in the algorithms section is included in the subject as defined in the algorithms section below.

Report Changes

The only new report definition would be a form used to print selected E-mails.

Problems and Alternatives

The initial design calls for the parsing of an inbound message to find associations using a trigger.  This will allow messages to be processed and appear in xTuple in real time.  However, in large installations this could cause a processing drag on the database.  An alternative implementation could have the processing of inbound message associations handled as a batch job, as xTuple Connect is well suited to do.  The table structure and functions will be built in such a way to make implementing this alternative method relatively straight forward.

As mentioned in the conflicts section, users may find it odd that sometimes a message is rendered in their local client, and sometimes using an xTuple interface.  The alternative would be for xTuple to build a complete interface to display all mail messages, and perhaps even send new ones.  This involves building a complex mail rendering mechanism to handle all the presentation nuances of MIME encoded email.  Furthermore it would require substantial rework of the existing xTuple connect  submitEDI window, which is already fairly complex.  The author of Trojita has suggested that we could use his Qt based code as a template for building such an interface, but for now it is beyond the scope of work.  This strategy could be employed as a subsequent round of development if necessary.  On the other hand, users may be perfectly comfortable using their own client in most cases and wish for the application to stay that way.

The attach function will specifically make users search by contact.  There may be calls to be able to search the entire database if the contact is not known, but this would almost certainly over tax the database since email tends to be a high volume data set, and also because full text searching would be involved.  We will start with the contact centric search on this pass, but leave the door open for adding full text search capability in a future release.

Internal Design

Scripting Protocols

The scripts added to xTuple connect should use the following conventions, whether or not they are already used in the current xTuple Connect code:

• All SQL statements should be stored in and run from the MetaSQL table.
• A global namespace object should be created for xTuple Connect called xtConnect.
• To reduce potential conflicts with other packages, all functions should use fully qualified signatures using namespace.window.function as they are implemented in the Time & Expense extension.
  • Example:  xtConnect.emailWidget.reply = function()
• The emailWidget should be written once, and included in all the various windows it is used.
  • Example: include("emailWidget");

Basic Algorithms

Document reference

The subject of all email correspondence generated from an xTuple document should include a document reference code in the subject line that can be used later by xTuple to associate mail imported back into the database with the appropriate document.  If the necessary reference code already exists in the subject, it should not be re-inserted.

The reference code in the subject will be structured as follows:

[xtref:{type code}-{doc number}]

Example: Quote 10001 -> [xtref:Q-10001]

The type codes will be the same as those used by comments and documents as listed here:

• T           = Contact
• CRMA = CRM Account
• C          = Customer
• V          = Vendor
• EMP    = Employee
• RA      = Return Authorization
• OPP     = Opportunity
• J            = Project
• Q           = Quote
• S           = Sales Order
• TO        = Transfer Order
• P           = Purchase Order
• TD        = To Do Item
• INCDT = Incident

Contact Cluster and Contact Widget

The contact cluster has a setEmailSubject function accessible to scripting that can be used to add the association when the user clicks on the email address of a contact.  An implementation on Incident, for example, might look something like this:

var xtConnect = new Object;

xtConnect.incident = new Object;

var _cntct = mywindow.findChild("_cntct");

var _number = mywindow.findChild("_number");

xtConnect.incident.numberChanged = function () msg = "[xtref:INCDT-" + _number.text + "]";

_cntct.setEmailSubjectText(msg); _number.textChanged.connect(xtConnect.incident.numberChanged);

Email Widget

The new email widget can use the openUrl() function in the script toolbox to launch the e-mail client with a new message when corresponding buttons are clicked. This toolbox function is an implementation of the openUrl function in the QDesktopServices class which is documented here:

http://doc.trolltech.com/4.6/qdesktopservices.html#openUrl

Submit EDI

The code for the submitEDI screen should also be altered to include the reference code in the subject wherever it is used.  The document type is already passed as a variable to this window, and there is an available document number token that can be re-purposed to add in the reference code in the subject.

Email generated by the Submit EDI should be imported into the eml table.  However, this should be accomplished in the xTuple Connect binary after the message has been successfully sent.  Specifically, code would be added to the sendMail function of process.cpp in the xtbatch application to insert a record into the eml table after a successful send.

Processing inbound mail

Inbound email should be processed by a series of function calls activated by a trigger on the eml table.  There should be two functions that parse content to make associations in the emlassc table: asscAddress and asscText.  The logic on the trigger would work as follows:

// Process each address field
PERFORM asscAddress(NEW.eml_id, NEW.eml_to);
PERFORM asscAddress(NEW.eml_id, NEW.eml_cc);
PERFORM asscAddress(NEW.eml_id, NEW.eml_bcc);

// Process the contents
PERFORM asscText(NEW.eml_id, NEW.eml_subj);
PERFORM asscText(NEW.eml_id, NEW.eml_body);

// Update the record as processed

NEW.eml_proc = true;

The asscAddress function will parse address out and make associations between contacts with matching addresses.  Postgres string functions can be used for parsing: http://www.postgresql.org/docs/8.2/static/functions-string.html

Count the number of addresses based on the ';' delimter
Loop through the address using split_part to extract each address
Extract the address component from each part
Select contact(s) using the found address
Loop through each contact and insert an emlassc record
Return TRUE if associations made, FALSE if none.

The asscText function looks for an xTuple reference in the text and makes an association

Use the substring function to find a reference match.
If found
  Parse the document type and document number from the substring.
  Select the id for the document based on the number from the appropriate table.
  Insert an emlassoc record for the document.
  Return TRUE
else
  Return FALSE

Opening Mail

When a user chooses to open a message from the e-mail widget it will happen using the user's email client using file manipulation and the openUrl functions in the script toolbox as follows:

Get the current email record.
Locate the current temp folder using toolbox.getTempDir();
Create a file path name with the temporary directory + mail unique id + .eml extension.
If the file doesn't already exist, using toolbox.fileExists(string) then
  Use toolboxTextStreamWrite(path, message) to write the contents of the eml_msg field to the new path
Call toolbox.openUrl(string) to open the file.
Use toolbox.removeFile(string) to remove the file.

Schema Changes

A set of tables will be created as a repository for email in xTuple and to make the associations between documents. Note the tables will reside in the xTuple Connect schema called xtbatch. The definitions are below:

E-mail Repository Table