SugarCRM LDAP dump

[catnipper]

After having found quite some help for our own SugarCRM setup - I would like to share this code snippet with the community. Maybe somebody may find it useful!

What it does:
Synchronize SugarCRM Contacts with a LDAP directory to be used with third party applications.

Setup:
Place the script somewhere on the webserver
Modify hosting parameters
Check to have PHP LDAP module installed
Create a cron to run scheduled replication

To Do's:
Integrate into SugarCRM as a Module

 <?php
 
/***********************************************
 * Author: Frank Hirsch
 * Contact: frank.hirsch@dalim.com
 * Created: 11/25/2009
 * Version: 0.1
 **********************************************/
 
    ini_set("memory_limit","128M");
    
    $ldap_serv    = 'ldap.example.com';
    $ldap_port    = '389';
    $ldap_path    = 'dc=example,dc=com';
    $ldap_u        = 'cn=Administrator,dc=example,dc=com';
    $ldap_p        = 'bindpw';
    $sugar_host    = 'crm.example.com';
    $sugar_db    = 'example_crm';
    $suagr_db_u    = 'dbuser';
    $sugar_db_p    = 'dbpassword';
    
    $start = time();
    /* DEBUG */ print "<b>Start: ".date("c")."</b> Memory: ".memory_get_usage(true)."<hr />\n";
    if($ldap = LDAPbind($ldap_serv,$ldap_port,$ldap_u,$ldap_p)) {
        if($db = SugarDB($sugar_host,$sugar_db,$suagr_db_u,$sugar_db_p)) {
                    $contactquery = "SELECT c.*, cc.*, a.name AS account_name from contacts c ".
                                        "LEFT OUTER JOIN contacts_cstm cc ON c.id = cc.id_c ".
                                        "LEFT OUTER JOIN accounts_contacts ac ON c.id = ac.contact_id ".
                                        "LEFT OUTER JOIN accounts a ON a.id = ac.account_id LIMIT 15000";
                    $contacts = mysql_query($contactquery);
                    $thisContacts = "0";
                    while ($contact = mysql_fetch_assoc($contacts)) {
                        $contact_o = ldap_escape(utf8_encode(trim($contact['first_name'].' '.$contact['last_name'])));
                        $contact_start = time();
                        /* DEBUG */ print "Contact: ".utf8_encode(trim($contact['first_name'].' '.$contact['last_name']))." [".$contact['id']."][deleted: ".$contact['deleted']."]<br />\n";
                        // Search for uid in LDAP directory
                        $info = LDAPsearch($ldap,$contact['id'],'uid',$ldap_path);
                        // if ucount > 0: delete
                        if ($info['count']>'0') {
                            $deleted = deleteLDAP($ldap,$info[0]['dn'],false);
                            /* DEBUG */ print("Deleting: ".$info[0]['dn']." [deleted: $deleted]<br />\n");
                        }
                        // if deleted == 0: create
                        if ($contact['deleted']=="0") {
                            $contactDetails = array();
                            $contactDetails = LDAPbuild($contact,$db);
                            if(contactDetails)
                                $added = ldap_add($ldap, 'cn='.$contact_o.','.$ldap_path, $contactDetails);
                            /* DEBUG */ print("Adding: cn=".$contact_o.",".$ldap_path." [added: $added]<br />\n");
                        }
                        // if "deleted" = 1 and uid does not exist: do nothing
                        if ($info['count']=='0' && $contact['deleted']=="1") {
                            /* DEBUG */ print("Nothing: ".$contact_o." [not in LDAP]<br />\n");
                        }
                        $allContacts += $added;
                        /* DEBUG */ print "<hr />\n";
                    }
            $end = (time()-$start)/60;
            /* DEBUG */ print "Time to complete: ".$end." min<br />\n";
            /* DEBUG */ print "Memory to complete: ".memory_get_usage(true)." bytes<br />\n";
            /* DEBUG */ print "Memory peak: ".memory_get_peak_usage(true)." bytes<br />\n";
            /* DEBUG */ print "Contacts added: ".$allContacts."<br />\n";
        }
    }
    /* DEBUG */ print "<b>End: ".date("c")."</b>";
    
/* LDAP attributes:
objectClass: organization
x    businessCategory
x    description
    destinationIndicator
x    facsimileTelephoneNumber
    internationaliSDNNumber
    l
    physicalDeliveryOfficeName
    postOfficeBox
x    postalAddress
x    postalCode
    preferredDeliveryMethod
    registeredAddress
    searchGuide
    seeAlso
x    st
x    street
x    telephoneNumber
    teletexTerminalIdentifier
    telexNumber
    userPassword
    x121Address
objectClass: person, inetorgPerson, organizationalPerson
x    description
x    facsimileTelephoneNumber
x    givenName
x    homePhone
    manager
x    mobile
x    o
x    ou
    postOfficeBox
x    postalAddress
x    postalCode
x    secretary
x    st
x    street
x    telephoneNumber
x    title
x    uid
    userPassword
 
other
    creatorsName
    createTimestamp
    modifiersName
    modifyTimestamp
 
*/
 
function LDAPbuild($contact,$db){
            $cc['objectClass'][] = 'person';
            $cc['objectClass'][] = 'inetOrgPerson';
            $cc['objectClass'][] = 'organizationalPerson';
            $cc['cn'] = utf8_encode(trim($contact['first_name'].' '.$contact['last_name']));
            $cc['sn'] = utf8_encode(trim($contact['last_name']));
            $cc['uid'] = $contact['id'];
            if(trim($contact['description'])!='')
                $cc['description'] = trim(utf8_encode($contact['description']));
            if (trim($contact['phone_fax'])!='')
                $cc['facsimileTelephoneNumber'] = utf8_encode($contact['phone_fax']);
            if (trim($contact['first_name'])!='')
                $cc['givenName'] = utf8_encode($contact['first_name']);
            if(trim($contact['phone_home'])!='')
                $cc['homePhone'] = utf8_encode(trim($contact['phone_home']));
//            $cc['manager']
            if (trim($contact['phone_mobile'])!='')
                $cc['mobile'] = utf8_encode($contact['phone_mobile']);
            if (trim($contact['account_name'])!='')
                $cc['o'] = utf8_encode(trim($contact['account_name']));
//            $cc['ou'] = utf8_encode($account['name']);
//            $cc['postOfficeBox']
            $postalAddress = trim(utf8_encode($contact['primary_address_street'])."\n".utf8_encode($contact['primary_address_postalcode'])." ".utf8_encode($contact['primary_address_city'])."\n".utf8_encode($contact['primary_address_country']));
            if ($postalAddress != '')
                $cc['postalAddress'] = $postalAddress;
            if (trim($contact['primary_address_postalcode'])!='' || trim($contact['primary_address_city'])!='')
                $cc['postalCode'] = utf8_encode(trim($contact['primary_address_postalcode'].' '.$contact['primary_address_city']));
//            $cc['secretary']
//            $cc['st']
            if (trim($contact['primary_address_street'])!='')
                $cc['street'] = trim(utf8_encode($contact['primary_address_street']));
//            if (trim($contact['primary_address_street'])!='')
//                $cc['st'] = trim(utf8_encode($contact['primary_address_street']));
            if(trim($contact['phone_work'])!='')
                $cc['telephoneNumber'] = utf8_encode(trim($contact['phone_work']));
//            if(trim($contact['assistant'])!='')
//                $cc['secretary'] = utf8_encode(trim($contact['assistant']));  /* secretary must be a RDN! */
            if(trim($contact['position_c'])!='')
                $cc['title'] = utf8_encode(trim($contact['position_c']));
            $mailquery = "SELECT ea.email_address from email_addr_bean_rel eabr ".
                            "LEFT OUTER JOIN email_addresses ea ON ea.id = eabr.email_address_id ".
                            "WHERE eabr.bean_module LIKE 'Contacts' AND eabr.deleted LIKE '0' AND eabr.bean_id LIKE '".$contact['id']."'";
            $mails = mysql_query($mailquery);
            while ($mail = mysql_fetch_assoc($mails)) {
                if(trim($mail['email_address'])!='')
                    $cc['mail'][] = $mail['email_address'];
            }
            mysql_free_result($mails);
            return $cc;
}
 
 
function deleteLDAP($ds,$dn,$recursive=false){
    if($recursive == false){
        return(ldap_delete($ds,$dn));
    }else{
        //searching for sub entries
        $sr=ldap_list($ds,$dn,"ObjectClass=*",array(""));
        $info = ldap_get_entries($ds, $sr);
        for($i=0;$i<$info['count'];$i++){
            //deleting recursively sub entries
            $result=deleteLDAP($ds,$info[$i]['dn'],$recursive);
            if(!$result){
                //return result code, if delete fails
                return($result);
            }
        }
        return(ldap_delete($ds,$dn));
    }
}
 
 
function ldap_escape($str, $for_dn = true) {
    // see:
    // RFC2254
    // http://msdn.microsoft.com/en-us/library/ms675768(VS.85).aspx
    // http://www-03.ibm.com/systems/i/software/ldap/underdn.html       
    if  ($for_dn)
        $metaChars = array(',','=', '+', '<','>',';', '\\', '"', '#');
    else
        $metaChars = array('(', ')', chr(0));
 
    $quotedMetaChars = array();
    foreach ($metaChars as $key => $value) $quotedMetaChars[$key] = '\\'.str_pad(dechex(ord($value)), 2, '0');
    $str=str_replace($metaChars,$quotedMetaChars,$str); //replace them
    return ($str);
}
 
function SugarDB($sugar_host,$sugar_db,$suagr_db_u,$sugar_db_p) {
    // Connect to SugarCRM DB
    if ($link = mysql_connect($sugar_host,$suagr_db_u,$sugar_db_p)) {
        if($db = mysql_select_db($sugar_db,$link))
            return $db;
    }
    return false;
}
    
    
function LDAPsearch($ldap,$string,$type,$dn) {
    $filter="($type=$string)";
    $sr=ldap_search($ldap, $dn, $filter);
    return (ldap_get_entries($ldap, $sr));
}
 
 
function LDAPbind($ldap_serv,$ldap_port,$ldap_u,$ldap_p) {
    // Connecting to LDAP
    $ldap_conn = ldap_connect($ldap_serv,$ldap_port)
              or die("Could not connect to ".$ldap_serv);
    if ($ldap_conn) {
        ldap_set_option($ldap_conn, LDAP_OPT_PROTOCOL_VERSION, 3);
        // binding to ldap server
        $ldap_bind = ldap_bind($ldap_conn,$ldap_u,$ldap_p);
        // verify binding
        if ($ldap_bind)
            return $ldap_conn;
    }
    return false;
}
?>  

Feel free to improve and update the functionality!

[sebek]

Thank you fo your code....what LDAP schema does it use?