Re: [otrs] ODBC to existing Customer database

20 Oct 2003

      Martin Edenhofer wrote:
...
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/
--
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

Re: [otrs] ODBC to existing Customer database

Christian Junk