Mapping from CustomerDB with duplicate mail addresses

[StephanSch]

Hi,

I have a problem on the assignment of customer data from a second customer db.
Sometimes a customer is several times in this db. It has the same basic data (name, title, ..., also multiple times with the same mail address) but different further data like a shop he is responsible for, and another, ... Every record has a unique id (not mail address). See structure below.

When there comes a ticket/mail of this (multiple existing) customer OTRS doesn't know which record to choose. This is ok for this case that the mail address is duplicated. Then the agent would choose the correct customer manually.

If I take the unique id as CustomerKey the customer data will never be shown in ticket zoom (even if record/mail adress is unique in db). If I take email as CustomerKey the assignment is working on incomming tickets but if the agent searches for a different record of thsi customer he allways gets only one search result. Surely because OTRS takes CustomerKey as db primary key.

The customer mySQL db looks like this (extract):

Code: Select all

# id, login, email, title, first_name, valid_id, shop, cluster, status
'307880', 'info@123.de', 'info@123.de', NULL, 'Winrich', '1', '123.de', 'E', '21'
'307881', 'muster@abc.biz', 'muster@abc.biz', NULL, 'Chris', '1', 'abc.biz', 'D', '21'
'307884', 'muster@567.com', 'muster@567.com', NULL, 'Franziska', '1', '567890abc.de', 'E', '21'
'307886', 'muster@alpha-xyz.de', 'muster@alpha-xyz.de', NULL, NULL, '1', 'musterurl.de', 'E', '21'
'307887', 'kontakt@alpha-xyz.de', 'kontakt@alpha-xyz.de', NULL, NULL, '1', 'musterurl.de', 'E', '21'
'307888', 'kontakt@alpha-xyz.de', 'kontakt@alpha-xyz.de', NULL, NULL, '1', 'musterurl-outlet.de', 'E', '21'

The config looks like this (extract):

Code: Select all

$Self->{CustomerUser2} = {
    Name => 'ShopDB',
    Module => 'Kernel::System::CustomerUser::DB',
    Params => {
            DSN => 'DBI:mysql:database=otrs_customer_bla;host=localhost',
            User => 'isnich',
            Password => 'denkste',
            Table => 'customer',
            SourceCharset => 'utf-8',
            DestCharset => 'utf-8',
            CaseSensitive => 0,
        },
        # customer unique id
        CustomerKey => 'id',

        # customer #
        CustomerID => 'id',
        CustomerValid => 'valid_id',
    CustomerUserListFields => ['first_name', 'last_name','shop', 'oracleid', 'email'],
    CustomerUserSearchFields => ['id', 'last_name', 'email', 'shop', 'oracleid', 'akquiseid'],
    CustomerUserSearchPrefix => '',
    CustomerUserSearchSuffix => '*',
    CustomerUserSearchListLimit => 250,
    CustomerUserPostMasterSearchFields => ['email'],
    CustomerUserNameFields => ['title','first_name','last_name'],
    CustomerUserEmailUniqCheck => 1,
#    # show not own tickets in customer panel, CompanyTickets
#    CustomerUserExcludePrimaryCustomerID => 0,
#    # generate auto logins
#    AutoLoginCreation => 0,
#    AutoLoginCreationPrefix => 'auto',
#    # admin can change customer preferences
    AdminSetPreferences => 0,
#    # cache time to live in sec. - cache any database queries
    CustomerCompanySupport => 0,
    CacheTTL => 3600,
#    # 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
        [ 'Shop',               'Shop',         'shop',                                 1, 0, 'var', '$Data{"ShopURL"}', 1 , "_blank"],
        [ 'ShopCluster',        'Shop-Cluster', 'cluster',                              1, 1, 'var', '', 1 ],
        [ 'UserTechAccountManager','Tech Account Manager','tech_account_manager',       1, 0, 'var', '', 1 ],
        [ 'UserAccountManager', 'Account Manager','account_manager',                    1, 0, 'var', '', 1 ],
        [ 'UserSalesManager',   'Sales Manager','sales_manager',                        1, 0, 'var', '', 1 ],
        [ 'UserTitle',          'Title',      'title',                                  1, 0, 'var', '', 1 ],
        [ 'UserFirstname',      'Firstname',  'first_name',                             1, 1, 'var', '', 1 ],
        [ 'UserLastname',       'Lastname',   'last_name',                              1, 1, 'var', '', 1 ],
        [ 'UserLogin',          'Username',   'login',                                  0, 1, 'var', '', 1 ],
#        [ 'UserPassword',      'Password',   'pw',                                     0, 0, 'var', '', 0 ],
        [ 'UserEmail',          'Email',      'email',                                  0, 1, 'var', '', 1 ],
        [ 'ShopFax',            'Shop-Fax',    'shop_fax',                              0, 0, 'var', '', 1 ],
        [ 'UserMobile',         'Mobile',      'mobile',                                0, 0, 'var', '', 1 ],
        [ 'UserStreet',         'Street',      'street',                                0, 0, 'var', '', 1 ],
        [ 'UserZip',            'Zip',         'zip',                                   0, 0, 'var', '', 1 ],
        [ 'UserCity',           'City',        'city',                                  0, 0, 'var', '', 1 ],
        [ 'UserCountry',        'Country',     'country',                               0, 0, 'var', '', 1 ],
        [ 'UserComment',        'Comment',     'comments',                              0, 0, 'var', '', 1 ],
        [ 'ValidID',            'Valid',       'valid_id',                              0, 1, 'int', '', 1 ],
        [ 'UserRole',           'Rolle',       'role',                                  1, 0, 'var', '', 1 ],
    ],
    # default selections
    Selections => {
#        UserTitle => {
#            'Mr.' => 'Mr.',
#            'Mrs.' => 'Mrs.',
#        },
    },
};

So I need a way that on incoming mails the customer is identified (by mail address) but with the unique id as primary key and not the mail address.

[alexus]

Try this option:

Code: Select all

 CustomerUserEmailUniqCheck => 0,

[StephanSch]

Try this option:

Code: Select all

 CustomerUserEmailUniqCheck => 0,

Hi,

thanks for the answer but there is no difference in behaviour of OTRS.
If I set CustomerKey => 'id' OTRS doesn't recognize the customer automatically, but all choices are there with manually searching. If I set CutomerKey => 'email' OTRS recognizes one data record of this customer automatically on incoming mail but with the attempt to change the customer (to another record with the same mail address) I get only one record as search result.

Code: Select all

            Table => 'customer',
            SourceCharset => 'utf-8',
            DestCharset => 'utf-8',
            CaseSensitive => 0,
        },
        # customer unique id
        CustomerKey => 'email',

        # customer #
        CustomerID => 'id',
        CustomerValid => 'valid_id',
    CustomerUserListFields => ['first_name', 'last_name','shop', 'oracleid', 'email'],
    CustomerUserSearchFields => ['id', 'last_name', 'email', 'shop', 'oracleid', 'akquiseid'],
    CustomerUserSearchPrefix => '',
    CustomerUserSearchSuffix => '*',
    CustomerUserSearchListLimit => 250,
    CustomerUserPostMasterSearchFields => ['email'],
    CustomerUserNameFields => ['title','first_name','last_name'],
    CustomerUserEmailUniqCheck => 0,