SugarCRM to Mozilla LDAP contact slurper
Seeing as SugarCRM is now truly Open Source, I decided to support them buy using SugarCRM as a contact database for a client.
This script extracts contact data from Sugar, and imports it into an LDAP tree, so that Thunderbird clients can use it as an address book.
It’s written for Sugar 5.0, and a suitable LDAP installation with the Mozilla Schema
A suitable VIEW for printing out a contact directory would look like:
CREATE OR REPLACE VIEW sugarab AS
SELECT c.id, c.description, salutation, first_name, last_name, title, department,
do_not_call, phone_home, phone_mobile, phone_work, phone_other, c.phone_fax,
primary_address_street, primary_address_city, primary_address_state,
primary_address_postalcode, primary_address_country, alt_address_street,
alt_address_city, alt_address_state, alt_address_postalcode, alt_address_country,
assistant, assistant_phone, lead_source, birthdate,
a.name AS account_name,
e1.email_address AS primary_email, e2.email_address AS secondary_email
FROM contacts AS c
LEFT OUTER JOIN accounts_contacts AS j ON (c.id = j.contact_id AND j.deleted = 0)
LEFT OUTER JOIN accounts AS a ON (j.account_id = a.id AND a.deleted = 0)
LEFT OUTER JOIN email_addr_bean_rel as eb ON (eb.bean_id = c.id AND eb.deleted = 0)
LEFT OUTER JOIN email_addresses as e1 ON (eb.email_address_id = e1.id AND eb.primary_address = 1 AND e1.deleted = 0)
LEFT OUTER JOIN email_addresses as e2 ON (eb.email_address_id = e2.id AND e1.id != e2.id AND e2.deleted = 0)
WHERE c.deleted = 0;
SELECT c.id, c.description, salutation, first_name, last_name, title, department,
do_not_call, phone_home, phone_mobile, phone_work, phone_other, c.phone_fax,
primary_address_street, primary_address_city, primary_address_state,
primary_address_postalcode, primary_address_country, alt_address_street,
alt_address_city, alt_address_state, alt_address_postalcode, alt_address_country,
assistant, assistant_phone, lead_source, birthdate,
a.name AS account_name,
e1.email_address AS primary_email, e2.email_address AS secondary_email
FROM contacts AS c
LEFT OUTER JOIN accounts_contacts AS j ON (c.id = j.contact_id AND j.deleted = 0)
LEFT OUTER JOIN accounts AS a ON (j.account_id = a.id AND a.deleted = 0)
LEFT OUTER JOIN email_addr_bean_rel as eb ON (eb.bean_id = c.id AND eb.deleted = 0)
LEFT OUTER JOIN email_addresses as e1 ON (eb.email_address_id = e1.id AND eb.primary_address = 1 AND e1.deleted = 0)
LEFT OUTER JOIN email_addresses as e2 ON (eb.email_address_id = e2.id AND e1.id != e2.id AND e2.deleted = 0)
WHERE c.deleted = 0;
| Attachment | Size |
|---|---|
| sugarslurp.py.txt | 6.25 KB |
Comments
Erk, there is already a plugin
Jonathan Carter has pointed out to me that there already is a Thunderbird Plugin for Sugar. Why the stupid idiots haven’t registered it on add-ons.mozilla.com, I don’t know… I’ll have to try it out :-)
Fix for utf-8
Post new comment