Re: [otrs] ODBC to existing Customer database

23 May 2003

      Hi David,

On Thu, May 22, 2003 at 07:21:01PM +0100, david.cruz@kpnqwest.pt 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"  
"customer 2"  
"customer 3"  
"customer 4"  
[...]
Select your wanted customer and click on "Take this User".

After that you should have '"customer x" ' 
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/
--