Hi David,
On Thu, May 22, 2003 at 07:21:01PM +0100, [EMAIL PROTECTED] wrote:
We'd like to have OTRS Tickets linked to our existing Customer database.
All we need is for the Customer info to be taken from a
table/view. Since it's in a Microsoft SQL2000 database, we're trying to
do this the easy way, by connecting to the sql2k database with
FreeTDS and unixODBC.
[...] You can use the customer database backend
(Kernel::System::CustomerUser::DB)
for external database connects.
But you need to update from CVS:
Kernel/System/DB.pm to 1.27
Kernel/System/CustomerUser/DB.pm to 1.16
[...]
I did as you wrote.
Updated Kernel/System/DB.pm to 1.27, and Kernel/System/CustomerUser/DB.pm to 1.16.
I'm using DBD::Sybase instead of DBD::ODBC because I was having lots of problems compiling ODBC, and using Sybase means one less layer (also had to install freetds-devel.rpm because of ctpublic.h).
Changed Kernel/Config.pm like this:
# CustomerUser
# (customer user database backend and settings)
$Self->{CustomerUser} = {
Module => 'Kernel::System::CustomerUser::DB',
Params => {
DSN => 'DBI:Sybase:MSSQL2KSERVER',
User => 'user_ro',
Password => 'PaSsWoRd',
Database => 'MyCustomerDatabase',
Table => 'MyCustomerTable',
},
CustomerKey => 'MyCustomerName',
CustomerID => 'MyCustomerNumber',
CustomerUserListFields => ['MyCustomerNumber', 'MyCustomerName'],
CustomerUserSearchFields => ['MyCustomerNumber', 'MyCustomerName', 'MyCustomerAddress', 'MyCustome
rPhone', 'MyCustomerEmail'],
CustomerUserNameFields => ['MyCustomerNumber', 'MyCustomerName', 'MyCustomerAddress', 'MyCustome
rPhone', 'MyCustomerEmail'],
# ReadOnly => 1,
Map => [
# note: Login, Email and CustomerID needed!
# var, frontend, storage, shown, required, storage-type, [...]
It's working like we want it to!
[...]
Here are the details:
The linking of tickets to customers works great!!!!!!!! (By the way, congratulations for your great work, OTRS people :))
Thanks! :))
But :), when you click in AdminArea (Misc) CustomerUser Link, the following gets written in /var/log/httpd/error.log:
---
[...]
message number=207 severity=16 state=3 line=1 server=MSSQL2KSERVERtext=Invalid column name 'customer_id'., SQL: 'SELECT
MyCustomerName, login, customer_id FROM MyCustomerTable'
Traceback (15523):
Module: Kernel::System::DB::GetTableData (v1.27) Line: 283
Module: Kernel::System::CustomerUser::DB::CustomerUserList (v1.16) Line: 202
Module: Kernel::System::CustomerUser::CustomerUserList (v1.15) Line: 62
Module: Kernel::Modules::AdminCustomerUser::Run (v1.7 ) Line: 174
Module: Apache::ROOT::otrs::index_2epl::handler (v1.58) Line: 608
Module: (eval) (v2.01) Line: 143
Module: Apache::Registry::handler (v2.01) Line: 143
---
I'm guessing that this means I'll have to change some more stuff somewhere else :)
No, it's a bug in Kernel::System::CustomerUser. :-/ Take the new one (1.17) from the CVS and it will work! :)
[...]
One morequestion: when I go to "Phone View - Phone Call/New Ticket", and select a client or write something (olaolaola), and then
click "take user" or submit, I get this cool message next to the input box: "* invalid olaolaola (rfc822)!"
I was expecting to have the CustomerEmail put there when clicking "take this user" :)
Normally you put the customers name (or something else) into the
"From:" then click on "Search Customer". After that you should get
a select box with the result e. g.
[...]
"customer 1" <[EMAIL PROTECTED]> "customer 2" <[EMAIL PROTECTED]> "customer 3" <[EMAIL PROTECTED]> "customer 4" <[EMAIL PROTECTED]> [...]
Select your wanted customer and click on "Take this User".
After that you should have '"customer x" <[EMAIL PROTECTED]>' in your From: field.
In your case you need to change
CustomerUserListFields => ['MyCustomerNumber', 'MyCustomerName'],
to:
CustomerUserListFields => ['MyCustomerNumber', 'MyCustomerName', 'MyCustomerEmail'],
and it will work.
-- |)avid ------------------------------------------------- KPNQwest Portugal http://www.KPNQwest.pt
-Martin
-- Martin Edenhofer - <martin at edenhofer.de> - http://martin.edenhofer.de/ --
Hi!
We are using a MSSQL Server for our customer user database, too. OTRS itself resides on a separate MySQL server.
I installed OTRS from the current CVS tree and made the necessary changes in Config.pm - i added the following lines:
# ---------------------------------------------------- #
# CustomerUser
# (customer user database backend and settings)
# ---------------------------------------------------- # $Self->{CustomerUser} = {
Module => 'Kernel::System::CustomerUser::DB',
Params => {
DSN => 'DBI:Sybase:server=MSSQL',
User => 'user',
Password => 'pass',
Database => 'WORKM001',
Table => 'kansprechp',
}, # customer uniq id
CustomerKey => 'I_LogName', # customer #
CustomerID => 'KAnsprechpCode',
CustomerValid => 'Entlassen',
CustomerUserListFields => ['I_LogName', 'Vorname', 'Name',
'[E-Mail]'],
CustomerUserSearchFields => ['I_LogName', 'Name',
'KAnsprechpCode'],
CustomerUserPostMasterSearchFields => ['[E-Mail]'],
CustomerUserNameFields => ['AnredeCode', 'Vorname', 'Name'],
ReadOnly => 1, Map => [
# note: Login, Email and CustomerID needed!
# var, frontend, storage, shown, required, storage-type,
http-link
[ 'UserSalutation', 'Salutation', 'AnredeCode', 1, 0,
'var' ],
[ 'UserFirstname', 'Firstname', 'Vorname', 1, 1, 'var'
],
[ 'UserLastname', 'Lastname', 'Name', 1, 1, 'var' ],
[ 'UserLogin', 'Login', 'I_LogName', 1, 1, 'var' ],
[ 'UserPassword', 'Password', 'I_Pass1', 0, 1, 'var' ],
[ 'UserEmail', '[E-Mail]', '[E-Mail]', 0, 1, 'var' ],
[ 'UserCustomerID', 'CustomerID', 'KAnsprechpCode', 0,
1, 'var' ],
[ 'UserComment', 'Comment', 'Notiz', 1, 0, 'var' ],
[ 'ValidID', 'Valid', 'Entlassen', 0, 1, 'int' ],
],};
After that it seems to work fine. When using the Admin Area / Misc / CustomerUser, I can see the User, but when I try to login with a user, I get the following error-message in /var/log/httpd/error.log:
[Mon Oct 20 10:18:21 2003] null: DBD::mysql::st execute failed: Table 'otrs.customer_user' doesn't exist at /opt/otrs/bin/cgi-bin/../../Kernel/System/DB.pm line 265. ERROR: OTRS-CGI-10 Perl: 5.8.0 OS: linux Time: Mon Oct 20 12:18:21 2003
Message: Table 'otrs.customer_user' doesn't exist, SQL: 'SELECT pw, login FROM customer_user WHERE valid_id in ( 1 ) AND login = 'junkc''
Traceback (2568): Module: Kernel::System::CustomerAuth::DB::Auth (v1.7) Line: 88 Module: Kernel::System::CustomerAuth::Auth (v1.4) Line: 53 Module: Apache::ROOT::otrs::customer_2epl::handler (v1.21) Line: 178 Module: (eval) (v2.01) Line: 149 Module: Apache::Registry::handler (v2.01) Line: 149
[Mon Oct 20 12:18:21 2003] null: DBD::mysql::st fetchrow_array failed: fetch() without execute() at /opt/otrs/bin/cgi-bin/../../Kernel/System/DB.pm line 287.
Who can help?
Much thanks in advance, Christian
_______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support oder Consulting f�r Ihr OTRS System? => http://www.otrs.de/
