Citando Martin Edenhofer <***@edenhofer.de>:
Hi again,
Post by Martin EdenhoferPost by d***@kpnqwest.ptWe'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.
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.
Post by Martin Edenhofer-Martin
--
Martin Edenhofer - <martin at edenhofer.de> -
http://martin.edenhofer.de/
--
Thank you very much!
--
|)avid
-------------------------------------------------
KPNQwest Portugal http://www.KPNQwest.pt