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的新版本和支持英文，并与非中文国家的用户分享，在此表示感谢。

Tags: c3crm 研究sugarcrm