#!/usr/bin/env python ##################################################################### # $Id$ # # Stefano Rivera's SugarCRM Contacts -> LDAP Slurper # Version 0.0.1 # Copyright (C) 2008 Stefano Rivera # # This program is free software; you can redistribute it and/or # modify it under the terms of the GNU General Public License as # published by the Free Software Foundation; version 2 of the # License. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU # General Public License for more details. # # You should have received a copy of the GNU General Public # License along with this program; if not, write to the Free # Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, # MA 02111-1307 USA ##################################################################### import MySQLdb import ldap import ldap.modlist import sys ##################################################################### # Configuration: LDAP_URL = "ldap://localhost/" LDAP_BIND = "cn=ldapslurp,ou=admin_ds,dc=example,dc=com" LDAP_PASS = "password" LDAP_BIND_TYPE = ldap.AUTH_SIMPLE LDAP_ADDRESSES_SUBTREE = "ou=sugar,ou=addresses,dc=example,dc=com" MYSQL_DB = "sugarcrm" MYSQL_USER = "sugarcrm" MYSQL_PASS = "password" ##################################################################### # Connect to LDAP l = ldap.initialize(LDAP_URL) l.bind_s(LDAP_BIND, LDAP_PASS, LDAP_BIND_TYPE) # Connect to MySQL db=MySQLdb.connect(user=MYSQL_USER, passwd=MYSQL_PASS, db=MYSQL_DB) c = db.cursor() # Retrieve contacts c.execute("""SELECT 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, e1.email_address, e2.email_address 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 AND e1.email_address IS NOT NULL;""") # Empty existing LDAP Tree: s = l.search_s(LDAP_ADDRESSES_SUBTREE, ldap.SCOPE_ONELEVEL, filterstr="(objectClass=mozillaAbPersonAlpha)", attrsonly=1) for row in s: dn = row[0] l.delete_s(dn) for row in c.fetchall(): (description, salutation, first_name, last_name, title, department, do_not_call, phone_home, phone_mobile, phone_work, phone_other, 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, company_name, primary_email, secondary_email ) = row # Simple mappings: entry = { 'objectClass': ['top', 'person', 'organizationalPerson', 'inetOrgPerson', 'mozillaAbPersonAlpha'], } # inetOrgPerson: May: givenName # mozillaAbPersonAlpha: May: displayName if first_name != None: entry['givenName'] = [first_name] if last_name != None: entry['displayName'] = ["%s %s" % (first_name, last_name)] else: entry['displayName'] = [first_name] elif last_name != None: entry['displayName'] = [last_name] # Person: Must: cn, sn if first_name == None: first_name = "" if last_name == None: last_name = "" entry['sn'] = [last_name] entry['cn'] = ["%s %s" % (first_name, last_name)] # inetOrgPerson: May: homePhone, mail, mobile if phone_home != None: entry['homePhone'] = [phone_home] if primary_email != None: entry['mail'] = [primary_email] if phone_mobile != None: entry['mobile'] = [phone_mobile] # Person: May: telephoneNumber, description if phone_work != None: entry['telephoneNumber'] = [phone_work] if description != None: entry['description'] = [description] # organizationalPerson: May: title, facsimileTelephoneNumber, street, postalCode, st, l if title != None: entry['title'] = [title] if phone_fax != None: entry['fax'] = [phone_fax] if primary_address_street != None: entry['street'] = [primary_address_street] if primary_address_postalcode != None: entry['postalCode'] = [primary_address_postalcode] if primary_address_state != None: entry['st'] = [primary_address_state] if primary_address_city != None: entry['l'] = [primary_address_city] # mozillaAbPersonAlpha: May: if primary_address_country != None: entry['c'] = [primary_address_country] if alt_address_country != None: entry['mozillaHomeCountryName'] = [alt_address_country] if alt_address_city != None: entry['mozillaHomeLocalityName'] = [alt_address_city] if alt_address_postalcode != None: entry['mozillaHomePostalCode'] = [alt_address_postalcode] if alt_address_state != None: entry['mozillaHomeState'] = [alt_address_state] if alt_address_street != None: entry['mozillaHomeStreet'] = [alt_address_street] if secondary_email != None: entry['mozillaSecondEmail'] = [secondary_email] if department != None: entry['ou'] = [department] if company_name != None: entry['o'] = [company_name] # Bad mappings: if phone_other != None: entry['pager'] = ["Other: %s" % phone_other] if do_not_call == True: if entry['description'][0] != "": entry['description'][0] += "\n" entry['description'][0] += "Do not call." dn = "mail=%s,%s" % (primary_email, LDAP_ADDRESSES_SUBTREE) print dn try: l.add_s(dn, ldap.modlist.addModlist(entry)) except ldap.ALREADY_EXISTS: print "Already exists, skipping..."