Hi,
i added new columns to teh customer_user table using this instructions http://doc.otrs.org/3.1/en/html/customer-user-backend.html:
Added fields:
company
department
manager
office
these fields are filled bysync_ldap2db.
This is my Config.pm:
#--------------------------------------------------------------------------------------------
# firma #
#--------------------------------------------------------------------------------------------
#--------------------------------------------------------------------------------------------
# Agenten Authentifizeirung #
#--------------------------------------------------------------------------------------------
$Self->{'AuthModule'} = 'Kernel::System::Auth::LDAP';
$Self->{'AuthModule::LDAP::Host'} = 'dc.firma.local';
$Self->{'AuthModule::LDAP::BaseDN'} = 'dc=firma,dc=local';
$Self->{'AuthModule::LDAP::UID'} = 'sAMAccountName';
$Self->{'AuthModule::LDAP::SearchUserDN'} = 'otrsldap@firma.local';
$Self->{'AuthModule::LDAP::SearchUserPw'} = 'Passwort123';
#--------------------------------------------------------------------------------------------
# Kunden Authentifizeirung #
#--------------------------------------------------------------------------------------------
$Self->{'Customer::AuthModule'} = 'Kernel::System::CustomerAuth::LDAP';
$Self->{'Customer::AuthModule::LDAP::Host1'} = 'dc.firma.local';
$Self->{'Customer::AuthModule::LDAP::BaseDN1'} = 'DC=firma, DC=local';
$Self->{'Customer::AuthModule::LDAP::UID1'} = 'sAMAccountName';
$Self->{'Customer::AuthModule::LDAP::SearchUserDN1'} = 'otrsldap@firma.local';
$Self->{'Customer::AuthModule::LDAP::SearchUserPw1'} = 'Passwort123';
#--------------------------------------------------------------------------------------------
# Kundendaten #
#--------------------------------------------------------------------------------------------
#--------------------------------------------------------------------------------------------
# CustomerUser (customer database backend and settings)
$Self->{CustomerUser1} = {
Name => 'Database Datasource',
Module => 'Kernel::System::CustomerUser::DB',
Params => {
# if you want to use an external database, add the required settings
# DSN => 'DBI:odbc:yourdsn',
# DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
# User => '',
# Password => '',
Table => 'customer_user',company
# if your frontend is unicode and the charset of your
# customer database server is iso-8859-1, use these options.
# SourceCharset => 'iso-8859-1',
# DestCharset => 'utf-8',
# CaseSensitive will control if the SQL statements need LOWER()
# function calls to work case insensitively. Setting this to
# 1 will improve performance dramatically on large databases.
CaseSensitive => 0,
},
# customer unique id
CustomerKey => 'login',
# customer #
CustomerID => 'customer_id',
CustomerValid => 'valid_id',
CustomerUserListFields => ['first_name', 'last_name', 'email'],
CustomerUserSearchFields => ['login', 'last_name', 'customer_id'],
CustomerUserSearchPrefix => '',
CustomerUserSearchSuffix => '*',
CustomerUserSearchListLimit => 500,
CustomerUserPostMasterSearchFields => ['email'],
CustomerUserNameFields => ['title','first_name','last_name'],
CustomerUserEmailUniqCheck => 1,
# # show not own tickets in customer panel, CompanyTickets
# CustomerUserExcludePrimaryCustomerID => 0,
# # generate auto logins
# AutoLoginCreation => 0,
# AutoLoginCreationPrefix => 'auto',
# # admin can change customer preferences
# AdminSetPreferences => 1,
# # cache time to live in sec. - cache any database queries
# CacheTTL => 0,
# # just a read only source
# ReadOnly => 1,
Map => [
# note: Login, Email and CustomerID needed!
# var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target
[ 'UserTitle', 'Title', 'title', 1, 0, 'var', '', 0 ],
[ 'UserFirstname', 'Firstname', 'first_name', 1, 1, 'var', '', 0 ],
[ 'UserLastname', 'Lastname', 'last_name', 1, 1, 'var', '', 0 ],
[ 'UserLogin', 'Username', 'login', 1, 1, 'var', '', 0 ],
[ 'UserPassword', 'Password', 'pw', 0, 0, 'var', '', 0 ],
[ 'UserEmail', 'Email', 'email', 1, 1, 'var', '', 0 ],
[ 'Firma', 'firma', 'company', 1, 0, 'var', '', 0],
[ 'Abteilung', 'abteilung', 'department', 1, 0, 'var', '', 0 ],
[ 'Vorgesetzter', 'vorgesetzter','manager', 1, 0, 'var', '', 0 ],
[ 'Buero', 'buero', 'physicalDeliveryOfficeName', 1, 0, 'var', '', 0 ],
# [ 'UserEmail', 'Email', 'email', 1, 1, 'var', '$Env{"CGIHandle"}?Action=AgentTicketCompose&ResponseID=1&TicketID=$Data{"TicketID"}&ArticleID=$Data{"ArticleID"}', 0 ],
[ 'UserCustomerID', 'CustomerID', 'customer_id', 0, 1, 'var', '', 0 ],
# [ 'UserCustomerIDs', 'CustomerIDs', 'customer_ids', 1, 0, 'var', '', 0 ],
[ 'UserPhone', 'Phone', 'phone', 1, 0, 'var', '', 0 ],
[ 'UserFax', 'Fax', 'fax', 1, 0, 'var', '', 0 ],
[ 'UserMobile', 'Mobile', 'mobile', 1, 0, 'var', '', 0 ],
[ 'UserStreet', 'Street', 'street', 1, 0, 'var', '', 0 ],
[ 'UserZip', 'Zip', 'zip', 1, 0, 'var', '', 0 ],
[ 'UserCity', 'City', 'city', 1, 0, 'var', '', 0 ],
[ 'UserCountry', 'Country', 'country', 1, 0, 'var', '', 0 ],
[ 'UserComment', 'Comment', 'comments', 1, 0, 'var', '', 0 ],
[ 'ValidID', 'Valid', 'valid_id', 0, 1, 'int', '', 0 ],
],
};
With this configuration, the customer are shown in the custom overview without the additional fields.
I thougt the error is here:
$Self->{CustomerUser1} = {
($Self->{CustomerUserDB}, $Self->{CustomerUserWhatever} works too)
so i tried
$Self->{CustomerUser} = {
like in the manal:-)
now the additional fields are displayed but no customers in the customer overview.
In the customer_user table the customers are availiable.
I have no more ideas.
Does anybody know where the error is?
Here is the sync_ldap2db script:
#!/usr/bin/perl -w
# --
# scripts/tools/sync-ldap2db.pl - sync a ldap directory to database
# Copyright (C) 2001-2009 OTRS AG, http://otrs.org/
# --
# $Id: sync-ldap2db.pl,v 1.10 2009/02/16 12:40:23 tr Exp $
# --
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU AFFERO General Public License as published by
# the Free Software Foundation; either version 3 of the License, or
# any later version.
#
# 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 Affero 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
# or see http://www.gnu.org/licenses/agpl.txt.
# --
# use ../ as lib location
use File::Basename;
use FindBin qw($RealBin);
use lib dirname($RealBin) . "/../";
use lib dirname($RealBin) . "/../Kernel/cpan-lib";
use strict;
use warnings;
use vars qw($VERSION);
$VERSION = qw($Revision: 1.10 $) [1];
use Net::LDAP;
use Kernel::Config;
use Kernel::System::Log;
use Kernel::System::Main;
use Kernel::System::DB;
use Kernel::System::Encode;
# create common objects
my %CommonObject = ();
$CommonObject{ConfigObject} = Kernel::Config->new();
$CommonObject{LogObject} = Kernel::System::Log->new(
LogPrefix => 'OTRS-sync-ldap2db',
%CommonObject,
);
$CommonObject{MainObject} = Kernel::System::Main->new(%CommonObject);
$CommonObject{EncodeObject} = Kernel::System::Encode->new(%CommonObject);
$CommonObject{DBObject} = Kernel::System::DB->new(%CommonObject);
my $UidLDAP = 'sAMAccountname';
my $UidDB = 'login';
my %Map = (
# db => ldap
email => 'mail',
customer_id => 'mail',
first_name => 'sn',
last_name => 'givenname',
pw => 'test',
firma => 'company',
abteilung => 'department',
buero => 'physicalDeliveryOfficeName',
phone => 'telephoneNumber',
manager => 'manager',
# comments => 'description',
comments => 'postaladdress',
);
my $LDAPHost = 'dc.firma.local';
my %LDAPParams = ();
my $LDAPBaseDN = 'dc=firma,dc=local';
my $LDAPBindDN = 'otrsldap@compass.local';
my $LDAPBindPW = 'Passwort123';
my $LDAPScope = 'sub';
my $LDAPCharset = 'utf-8';
#my $LDAPFilter = '';
my $LDAPFilter = '(&(objectclass=user)(mail=*.*@firma.de))';
my $DBCharset = 'UTF-8';
my $DBTable = 'customer_user';
# ldap connect and bind (maybe with SearchUserDN and SearchUserPw)
my $LDAP = Net::LDAP->new( $LDAPHost, %LDAPParams ) or die "$@";
if ( !$LDAP->bind( dn => $LDAPBindDN, password => $LDAPBindPW ) ) {
$CommonObject{LogObject}->Log(
Priority => 'error',
Message => "Bind failed!",
);
exit 1;
}
# split request of all accounts
for (qw(0 1 2 3 4 5 6 7 8 9 a b c d e f g h i j k l m n o p q r s t u v w x y z)) {
my $Filter = "($UidLDAP=$_*)";
if ($LDAPFilter) {
$Filter = "(&$LDAPFilter$Filter)";
}
# perform user search
my $Result = $LDAP->search(
base => $LDAPBaseDN,
scope => $LDAPScope,
filter => $Filter,
);
#print "F: ($UidLDAP=$_*)\n";
for my $entry ( $Result->all_entries ) {
my $UID = $entry->get_value($UidLDAP);
if ($UID) {
# check if uid existsis in db
my $Insert = 1;
$CommonObject{DBObject}->Prepare(
SQL => "SELECT $UidDB FROM $DBTable WHERE $UidDB = '"
. $CommonObject{DBObject}->Quote($UID) . "'",
Limit => 1,
);
while ( my @Row = $CommonObject{DBObject}->FetchrowArray() ) {
$Insert = 0;
}
my $SQLPre = '';
my $SQLPost = '';
my $Type = '';
if ($Insert) {
$Type = 'INSERT';
}
else {
$Type = 'UPDATE';
}
for ( keys %Map ) {
my $Value = $CommonObject{DBObject}->Quote(
_ConvertTo( $entry->get_value( $Map{$_} ) ) || ''
);
if ( $Type eq 'UPDATE' ) {
if ($SQLPre) {
$SQLPre .= ", ";
}
$SQLPre .= " $_ = '$Value'";
}
else {
if ($SQLPre) {
$SQLPre .= ", ";
}
$SQLPre .= "$_";
if ($SQLPost) {
$SQLPost .= ", ";
}
$SQLPost .= "'$Value'";
}
}
my $SQL = '';
if ( $Type eq 'UPDATE' ) {
print "UPDATE: $UID\n";
$SQL
= "UPDATE $DBTable SET $SQLPre, valid_id = 1, change_time = current_timestamp, change_by = 1 ";
$SQL .= " WHERE $UidDB = '" . $CommonObject{DBObject}->Quote($UID) . "'";
}
else {
print "INSERT: $UID\n";
$SQL
= "INSERT INTO $DBTable ($SQLPre, $UidDB, valid_id, create_time, create_by, change_time, change_by) VALUES ($SQLPost, '"
. $CommonObject{DBObject}->Quote($UID)
. "', 1, current_timestamp, 1, current_timestamp, 1)";
}
$CommonObject{DBObject}->Do( SQL => $SQL );
}
}
}
sub _ConvertTo {
my $Text = shift;
return if !defined $Text;
return $CommonObject{EncodeObject}->Convert(
Text => $Text,
To => $DBCharset,
From => $LDAPCharset,
);
}
Boris