Citando Martin Edenhofer : Hi again,

On Wed, May 21, 2003 at 04:25:02PM +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

You also need the DBD::ODBC (http://search.cpan.org/author/JURL/DBD-ODBC-1.05/) module for DBI to connect to unixODBC DSNs.

Copy the "CustomerUser" DB example from Kernel/Config/Defaults.pm into your custom Kernel/Config.pm.

Change the table and column names like you need (like your table is).

Add to the CustomerUser params

[...] Params => { DSN => 'DBI:odbc:yourdsn', User => 'youruser', Password => 'yourpw', Table => 'your_customer_user_table', }, [...]

If you need any more help, send a follow up! :)

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, http-link ['UserCustomerID', 'MyCustomerNumber', 'MyCustomerNumber', 1, 1, 'var'], ['UserLogin', 'MyCustomerName', 'MyCustomerName', 1, 1, 'var'], ['UserFirstname', 'MyCustomerAddress', 'MyCustomerAddress', 1, 1, 'var'], ['UserLastname', 'MyCustomerPhone', 'MyCustomerPhone', 1, 1, 'var'], ['UserEmail', 'MyCustomerEmail', 'MyCustomerEmail', 1, 1, 'var'], ], }; It's working like we want it to! I'm just not sure about one thing: there's the "ReadOnly => 1," and I'm not sure if I should activate it. I only need readonly, so I've already given those permissions to the connecting user (user_ro). Here are the details: The linking of tickets to customers works great!!!!!!!! (By the way, congratulations for your great work, OTRS people :)) But :), when you click in AdminArea (Misc) CustomerUser Link, the following gets written in /var/log/httpd/error.log: --- [Thu May 22 16:56:08 2003] null: DBD::Sybase::st execute failed: Server message number=207 severity=16 state=3 line=1 server=MSSQL2KSERVERtext=Invalid column name 'login'.Server message number=207 severity=16 state=3 line=1 server=MSSQL2KSERVERtext=Invalid column name 'customer_id'. at /opt/otrs//Kernel/System/DB.pm line 226. ERROR: OTRS-CGI-10 Perl: 5.6.1 OS: linux Time: Thu May 22 16:56:08 2003 Message: Server message number=207 severity=16 state=3 line=1 server=MSSQL2KSERVERtext=Invalid column name 'login'.Server 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 :) (the purpose of using this CustomerUser Admin page would be to get a better listing of user details, but if it stays like it is, we won't worry a bit - after all, it's us admins who get to click on links inside the AdminArea :>) I changed all our secret details to 'MySomething' and 'MSSQL2KSERVER' :) because we're using Portuguese names in those fields, and you might not understand the meaning. 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" :) I also saw you have a "write email (new ticket) to customer" in TODO... Probably it's the same thing.

-Martin

-- Martin Edenhofer - <martin at edenhofer.de> - http://martin.edenhofer.de/ --

Thank you very much! -- |)avid ------------------------------------------------- KPNQwest Portugal http://www.KPNQwest.pt

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