
Citando Martin Edenhofer
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