[Phpgroupware-developers] LDAP support

[Philipp Kamps]

Hi ya'll,

I developed an SQL-statment for the new addressbook
database structure.

It gets quite a lot of informations from all the
addressbook-tables into one flat table.

This table could be used to sync with a LDAP-Server.
I named the table-fields like the LDAP-Attributes.

I was supprised how many informations of the new
addressbook database structure could possibly mapped
to LDAP.

Any comments are very welcome.

Cheers,
  fips from probiz


SELECT phpgw_contact.contact_id,
       first_name                   AS givenName,
       last_name                    AS sn,
       initials                     AS initials,
       add1                         AS postalAddress0,
       add2                         AS postalAddress1,
       add3                         AS postalAddress2,
       city                         AS l,
       state                        AS st,
       postal_code                  AS postalCode,
       country                      AS c,
       phpgw_contact_addr.preferred AS preferredaddr,
       title                        AS title,
       department                   AS tobespecified,
       pubkey                       AS userPKCS12,
       descr                        AS LDAPCommAttribute,
       comm_data                    AS LDAPCommValue,
       phpgw_contact_comm.preferred AS preferredcomm,
       name                         AS o,
       note_text                    AS description

FROM  phpgw_contact, phpgw_contact_person

LEFT JOIN phpgw_contact_addr ON (phpgw_contact.contact_id =
phpgw_contact_addr.contact_id)
LEFT JOIN phpgw_contact_comm  ON (phpgw_contact.contact_id =
phpgw_contact_comm.contact_id)
LEFT JOIN phpgw_contact_comm_descr
       ON (phpgw_contact_comm.comm_descr_id =
phpgw_contact_comm_descr.comm_descr_id AND
            (descr = 'work email' OR descr = 'home email' OR descr =
'work phone'
              OR descr = 'home phone' OR descr = 'pager' OR descr =
'work fax'
              OR descr = 'mobile (cell) phone' OR descr = ' car phone'
OR descr = 'isdn'
              OR descr = 'website'
            )
          )
LEFT JOIN phpgw_contact_note ON (phpgw_contact_note.contact_id =
phpgw_contact.contact_id)
LEFT JOIN phpgw_contact_org_person ON (phpgw_contact_person.person_id =
phpgw_contact_org_person.person_id)
LEFT JOIN phpgw_contact_org ON (phpgw_contact_org_person.org_id =
phpgw_contact_org.org_id
                                 AND phpgw_contact_org.active = 'Y'
                               )

WHERE ( phpgw_contact.contact_id = phpgw_contact_person.person_id )

ORDER BY last_name