Customer BackEnd [SOLVED]

[Muad_Dib]

Hi,

I'm currently trying to install OTRS in my company and I have some trouble configuring all the stuff for the Customer BackEnd Database. I have to use the DB from the ERP to use it as the Customer Database.
So I changed Config.pm to use it and it works almost fine

here is my config.pm:

Code: Select all

        # customer uniq id
        CustomerKey => 'Address_ID',

        # customer #
        CustomerID             => 'Customer_id',
        CustomerValid          => 'AD_Status',
        CustomerUserListFields => ['AD_Name'],	

        # List of fields used in the where clause searching in backend DB
        CustomerUserSearchFields           => ['AD_Name', 'ADK_Key_2'],
        CustomerUserSearchPrefix           => '',
        CustomerUserSearchSuffix           => '*',
        CustomerUserSearchListLimit        => 250,
        CustomerUserPostMasterSearchFields => ['Email'],
        CustomerUserNameFields     => ['ad_first_name', 'ad_last_name'],
        CustomerUserEmailUniqCheck => 1,

        # show not own tickets in customer panel, CompanyTickets
        #CustomerUserExcludePrimaryCustomerID => 0,
        # generate auto logins
        AutoLoginCreation => 0,
        # admin can change customer preferences
        AdminSetPreferences => 1,
        # use customer company support (reference to company, See CustomerCompany settings)
        CustomerCompanySupport => 0,
        # cache time to life in sec. - cache any database queris
        CacheTTL => 0,
        # just a read only source
        ReadOnly => 1,
		
        Map => [
            # note: Login, Email and CustomerID needed!
            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target
            [ 'UserFirstname',  'Firstname',  'AD_First_Name', 1, 1, 'var', '', 0 ],
            [ 'UserLastname',   'Lastname',   'AD_Last_Name',  1, 1, 'var', '', 0 ],
            [ 'UserLogin',      'Username',   'ADK_Key_2',      1, 1, 'var', '', 0 ],
            [ 'UserPassword',   'Password',   'Pwd',         0, 0, 'var', '', 0 ],
            [ 'UserEmail',      'Email',      'Email',      1, 1, 'var', '', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'Customer_id', 0, 1, 'var', '', 0 ],
            [ 'UserPhone',        'Phone',       'TEL_number',        1, 0, 'var', '', 0 ],
            [ 'UserFax',          'Fax',         'FAX_number',          1, 0, 'var', '', 0 ],
            [ 'UserStreet',       'Street',      'AD_Address_1',       1, 0, 'var', '', 0 ],
            [ 'UserZip',          'Zip',         'AD_Zip_Code',          1, 0, 'var', '', 0 ],
            [ 'UserCity',         'City',        'AD_City',         1, 0, 'var', '', 0 ],
            [ 'UserCountry',      'Country',     'CTY_ISO_Code',      1, 0, 'var', '', 0 ],
            [ 'UserComment',      'Comment',     'AD_Remark',     1, 0, 'var', '', 0 ],
            [ 'ValidID',          'Valid',       'AD_Status',     0, 1, 'int', '', 0 ],
        ],
    };

With this config, the search for the Customer are just fine but when I click on the link to see the details it doesn't work. I saw on a profiler that the SQL Query sent by OTRS for this is

Code: Select all

SELECT  AD_First_Name,  AD_Last_Name,  ADK_Key_2,  Pwd,  Email,  Customer_id,  TEL_number,  FAX_number,  AD_Address_1,  AD_Zip_Code,  AD_City,  CTY_ISO_Code,  AD_Remark,  AD_Status, Customer_ID FROM v_Address_OTRS WHERE LOWER(Customer_ID) = LOWER(''SUNIE mo'')'

But it cannot work as the string "SUNIE mo" is the field "ADK_Key_2"... I try to change lot of things so OTRS use Customer_ID field but never can make it work!
Anyone have an idea?

The only way I found to make it work is to modify some values in config.pm:

Code: Select all

        CustomerKey => 'Address_ID',
            [ 'UserLogin',      'Username',   'Address_ID',      1, 1, 'var', '', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'ADK_Key_2', 0, 1, 'var', '', 0 ],

But it's not what my users want !

Second question is what is the purpose of CustomerUserPostMasterSearchFields?

Thanks a lot!

[Daniel Obee]

# customer uniq id
CustomerKey => 'Address_ID',

Shouldn't that be 'ADK_Key_2' or 'Customer_id'? I'm not sure though.

For the second question: It's the field(s) that the postmaster searches to determine which customer's mail it was. Normally it's just 'email' but you can add fields containing alternative mail addresses.

Greets
Daniel

[Muad_Dib]

Shouldn't that be 'ADK_Key_2' or 'Customer_id'? I'm not sure though.

So if I assume that CustomerKey is the login name the user should be use to login on the portal, I have to use ADK_Key_2 (which is more user-friendly than an ID) and to store the ID of the Customer I set CustomerID to 'Customer_ID' (seems logical ).

So I change the Config.pm as follows:

Code: Select all

        # customer uniq id
        CustomerKey => 'ADK_Key_2',

        # customer #
        CustomerID             => 'Customer_ID',
        CustomerValid          => 'AD_Status',
        CustomerUserListFields => ['Email'],	

        CustomerUserSearchFields           => ['AD_Name', 'ADK_Key_2'],
        CustomerUserSearchPrefix           => '',
        CustomerUserSearchSuffix           => '*',
        CustomerUserSearchListLimit        => 250,
        CustomerUserPostMasterSearchFields => ['Email'],
        CustomerUserNameFields     => ['AD_First_Name', 'AD_Last_Name'],
        CustomerUserEmailUniqCheck => 1,

        # generate auto logins
        AutoLoginCreation => 0,
        # admin can change customer preferences
        AdminSetPreferences => 1,
        # use customer company support (reference to company, See CustomerCompany settings)
        CustomerCompanySupport => 0,
        # cache time to life in sec. - cache any database queris
        CacheTTL => 0,
        # just a read only source
        ReadOnly => 1,
		
        Map => [
            # note: Login, Email and CustomerID needed!
            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target
            [ 'UserFirstname',  'Firstname',  'AD_First_Name', 1, 1, 'var', '', 0 ],
            [ 'UserLastname',   'Lastname',   'AD_Last_Name',  1, 1, 'var', '', 0 ],
            [ 'UserLogin',      'Username',   'ADK_Key_2',      1, 1, 'var', '', 0 ],
            [ 'UserPassword',   'Password',   'Pwd',         0, 0, 'var', '', 0 ],
            [ 'UserEmail',      'Email',      'Email',      1, 1, 'var', '', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'Customer_id', 0, 1, 'var', '', 0 ],
            [ 'UserPhone',        'Phone',       'TEL_number',        1, 0, 'var', '', 0 ],
            [ 'UserFax',          'Fax',         'FAX_number',          1, 0, 'var', '', 0 ],
            [ 'UserStreet',       'Street',      'AD_Address_1',       1, 0, 'var', '', 0 ],
            [ 'UserZip',          'Zip',         'AD_Zip_Code',          1, 0, 'var', '', 0 ],
            [ 'UserCity',         'City',        'AD_City',         1, 0, 'var', '', 0 ],
            [ 'UserCountry',      'Country',     'CTY_ISO_Code',      1, 0, 'var', '', 0 ],
            [ 'UserComment',      'Comment',     'AD_Remark',     1, 0, 'var', '', 0 ],
            [ 'ValidID',          'Valid',       'AD_Status',     0, 1, 'int', '', 0 ],
        ],
    };

And it works just fine!
Thanks a log!