Howto integrate 3rd-party software with SugarCRM-1

               
This series discusses the various means of integrating 3rd-party
software, open source or not, with the well-known “commercial open
source” CRM-Software
SugarCRM
. The first part describes the most basic integration by simply sharing database access.

In
a lot of projects, no tight integration of your software with SugarCRM
is needed - you only want to access the data, most of the times
readonly, but also have the possibility to make changes. No UI needed,
or no UI which is integrated into SugarCRM. For those cases it might be
a good solution to simply share database with Sugar. ODBC, OLEDB as
well as JDBC and Perl::DB drivers are available for free to access the
mysql database. SugarCRMs table structure is rather easy to understand:

There are “data” tables, “link” tables and some helper and config tables. All of the “data” tables have a set of common fields:

• “id” - holds the unique id of the object
• “date_entered” - original creation data
• “created_by” - unique id of the user who created the object
• “date_modified” - data of last change
• “modified_user_id” - unique id of the user who made last change
• “assigned_user_id” - unique id of the user the object is assigned
• “deleted” - if set to 1, the object won’t be displayed anymore

The data tables usually are called just the same as the module they belong to - “accounts”, “contacts”, “projects” and so on.

The
“link” tables relate two or more objects and are used for building
relationships - all the subpanels in Sugar, for example the contacts
matching an account, are built upon those link tables. The tables are
called as the objects they relate - for example “account_contacts”.
They always contain the following fields:

• “id” - holds the unique id of the relationship
• “deleted” - if set to 1, the relationship is invalid

The “id”-field is never used explicitely in Sugar, as
it makes no sense for itself - the interesting part in the link-tables
are the additional fields, which link to the related objects. They are
called like “object-name_id”, for example “account_id” and “contact_id”
in the link table “account_contacts”.

This is an example query which lists all contact names with associated account name:

select
c.first_name, c.last_name, a.name from contacts c, accounts_contacts
ac, accounts a where c.deleted != 1 and ac.deleted != 1 and a.deleted
!= 1 and c.id = ac.contact_id and ac.account_id = a.id order by a.name,
c.last_name

Using this query and an the MySQL ODBC driver (available here:
http://dev.mysql.com/downloads/connector/odbc/
) it
is possible to do a mail merge with Microsoft Word, or even to perform
calculations and build charts with Microsoft Excel. Some examples on
how to do this are available in the ZuckerReports project
at SugarForge:
http://www.sugarforge.org/projects/zuckerreports/
注：这篇文章是
ftreml
所写，另外感谢他把C3CRM开发的模块改成兼容SugarCRM的新版本和支持英文，并与非中文国家的用户分享，在此表示感谢。