Problem with CustomerID in CustomerCompany DB backend

[jmsalomr]

Hi,

I'm getting mad. I have configured both backends for CustomerUsers and for CustomerCompanies. If I use a numeric ID for CustomerID ('custid') it works in both backends, but if I use an alphanumeric ID ('sname') just works in CustomerUser backend, not in CustomerCompany backend. I receive a "Need CustomerID!" Error in the OTRS log. Both IDs are primary Keys and unique in origin DB.

I'm using:
OTRS 3.3.8
ITSM set
Three more plugins from CapeIT and Thales.

The configuration for both is as follows:

Code: Select all

    # CustomerUser
    # (customer user database backend and settings)
    $Self->{CustomerUser} = {
        Name   => 'DBNAME',
        Module => 'Kernel::System::CustomerUser::DB',
        Params => {
            # if you want to use an external database, add the
            # required settings
#            DSN  => 'DBI:odbc:yourdsn',
#            Type => 'mssql', # only for ODBC connections
#            DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
#            User => '',
#            Password => '',
            DSN => 'DBI:mysql:database=DBNAME;host=HOSTNAME',
            User => 'DBUSER',
            Password => 'DBPASSOWRD',
            Table => 'view_otrs_people',
            # if your frontend is unicode and the charset of your
            # customer database server is iso-8859-1, use these options.
           SourceCharset => 'iso-8859-1',
           DestCharset => 'utf-8',

            # CaseSensitive will control if the SQL statements need LOWER()
            #   function calls to work case insensitively. Setting this to
            #   1 will improve performance dramatically on large databases.
            CaseSensitive => 0,
        },
        # customer unique id
        CustomerKey => 'personid',

        # customer #
        CustomerID             => 'sname',
        CustomerValid          => 'active',

        # The last field must always be the email address so that a valid
        #   email address like "John Doe" <john.doe@domain.com> can be constructed from the fields.
        #CustomerUserListFields             => [ 'first_name', 'last_name', 'email' ],
        #CustomerUserListFields             => ['login', 'first_name', 'last_name', 'customer_id', 'email'],

        #CustomerUserSearchFields           => [ 'login', 'first_name', 'last_name', 'customer_id' ],

        #CustomerUserListFields             => [ 'name', 'email' ],
        CustomerUserListFields             => [ 'personid', 'name', 'sname', 'email' ],
        CustomerUserSearchFields           => [ 'personid', 'name', 'email', 'sname' ],
        CustomerUserSearchPrefix           => '*',
        CustomerUserSearchSuffix           => '*',
        CustomerUserSearchListLimit        => 250,
        #CustomerUserPostMasterSearchFields => ['email'],
        CustomerUserPostMasterSearchFields => [ 'email' ],
        #CustomerUserNameFields             => [ 'title', 'first_name', 'last_name' ],
        CustomerUserNameFields             => [ 'name' ],
        CustomerUserEmailUniqCheck         => 1,
#        # show now own tickets in customer panel, CompanyTickets
#        CustomerUserExcludePrimaryCustomerID => 0,
         CustomerUserExcludePrimaryCustomerID => 1,
#        # generate auto logins
#        AutoLoginCreation => 0,
#        # generate auto login prefix
#        AutoLoginCreationPrefix => 'auto',
#        # admin can change customer preferences
#        AdminSetPreferences => 1,
#        # use customer company support (reference to company, See CustomerCompany settings)
#        CustomerCompanySupport => 1,
#        # cache time to live in sec. - cache any database queries
#        CacheTTL => 60 * 60 * 24,
         CacheTTL => 60 * 60,
#        # 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, link class(es)
            #[ 'UserTitle',       'Title',       'title',      1, 0, 'var', '', 0 ],
            #[ 'UserFirstname',   'Firstname',   'first_name', 1, 1, 'var', '', 0 ],
            #[ 'UserLastname',    'Lastname',    'last_name',  1, 1, 'var', '', 0 ],
            #[ 'UserLogin',       'Username',    'login',      1, 1, 'var', '', 0 ],
            #[ 'UserPassword',    'Password',    'pw',         0, 0, 'var', '', 0 ],
            #[ 'UserEmail',       'Email',       'email',      1, 1, 'var', '', 0 ],
            #[ 'UserEmail',       'Email',       'email',           1, 1, 'var', '$Env{"CGIHandle"}?Action=AgentTicketCompose;ResponseID=1;TicketID=$Data{"TicketID"};ArticleID=$Data{"ArticleID"}', 0, '', 'AsPopup OTRSPopup_TicketAction' ],
            #[ 'UserCustomerID',  'CustomerID',  'customer_id', 0, 1, 'var', '', 0 ],
            #[ 'UserCustomerIDs', 'CustomerIDs', 'customer_ids', 1, 0, 'var', '', 0 ],
            #[ 'UserContactType', 'ContactType', 'contact_type', 1, 0, 'var', '', 0 ],
            #[ 'UserStreet',      'Street',      'street',       1, 0, 'var', '', 0 ],
            #[ 'UserZip',         'Zip',         'zip',          1, 0, 'var', '', 0 ],
            #[ 'UserCity',        'City',        'city',         1, 0, 'var', '', 0 ],
            #[ 'UserCountry',     'Country',     'country',      1, 0, 'var', '', 0 ],
            #[ 'UserComment',     'Comment',     'comments',     1, 0, 'var', '', 0 ],
            #[ 'ValidID',         'Valid',       'valid_id',     0, 1, 'int', '', 0 ],
            [ 'UserLastname',     'Nombre',      'name',         1, 1, 'var', '', 0 ],
            [ 'UserLogin',        'Username',    'personid',     1, 1, 'var', '', 0 ],
            [ 'UserPassword',     'Password',    'personid',     0, 0, 'var', '', 0 ],
            [ 'UserEmail',        'Email',       'email',        1, 1, 'var', '', 0 ],
            [ 'UserCustomerID',   'CustomerID',  'sname',        0, 1, 'var', '', 0 ],
            [ 'UserCustomerIDs',  'CustomerIDs', 'sname',        1, 0, 'var', '', 0 ],
            [ 'UserContactType',  'ContactType', 'contact_type', 1, 0, 'var', '', 0 ],
            [ 'UserPhone',        'Phone',       'telno',        1, 0, 'var', '', 0 ],
            [ 'UserAltPhone',     'AltPhone',    'alttelno',     1, 0, 'var', '', 0 ],
            [ 'UserFax',          'Fax',         'faxno',        1, 0, 'var', '', 0 ],
            [ 'UserMobile',       'Mobile',      'mobileno',     1, 0, 'var', '', 0 ],
            [ 'ValidID',          'Valid',       'active',       0, 1, 'int', '', 0 ],
        ],

        # default selections
        Selections => {

#            UserTitle => {
#                'Mr.' => 'Mr.',
#                'Mrs.' => 'Mrs.',
#            },
        },
    };

    $Self->{CustomerCompany} = {
        Name   => 'DBNAME_Company',
        Module => 'Kernel::System::CustomerCompany::DB',
        Params => {
            # if you want to use an external database, add the
            # required settings
#            DSN  => 'DBI:odbc:yourdsn',
#            Type => 'mssql', # only for ODBC connections
#            DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
#            User => '',
#            Password => '',
            #Table => 'customer_company',
            DSN => 'DBI:mysql:database=DBNAME;host=HOSTNAME',
            User => 'DBUSER',
            Password => 'DBPASSWORD',
            Table => 'view_otrs_customer',
#            ForeignDB => 0,    # set this to 1 if your table does not have create_time, create_by, change_time and change_by fields
            ForeignDB => 1,

            # CaseSensitive will control if the SQL statements need LOWER()
            #   function calls to work case insensitively. Setting this to
            #   1 will improve performance dramatically on large databases.
            CaseSensitive => 0,
        },

        # company unique id
        #CustomerCompanyKey             => 'customer_id',
        #CustomerCompanyValid           => 'valid_id',
        #CustomerCompanyListFields      => [ 'customer_id', 'name' ],
        #CustomerCompanySearchFields    => [ 'customer_id', 'name' ],
        CustomerCompanyKey             => 'sname',
        CustomerCompanyValid           => 'active',
        CustomerCompanyListFields      => [ 'sname', 'lname' ],
        CustomerCompanySearchFields    => [ 'sname', 'lname' ],
        CustomerCompanySearchPrefix    => '*',
        CustomerCompanySearchSuffix    => '*',
        CustomerCompanySearchListLimit => 250,
        #CacheTTL                       => 60 * 60 * 24, # use 0 to turn off cache
        CacheTTL                       => 60 * 60, # use 0 to turn off cache
        ReadOnly => 1,

        Map => [
            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly
            #[ 'CustomerID',             'CustomerID', 'customer_id', 0, 1, 'var', '', 0 ],
            #[ 'CustomerCompanyName',    'Customer',   'name',        1, 1, 'var', '', 0 ],
            #[ 'CustomerCompanyStreet',  'Street',     'street',      1, 0, 'var', '', 0 ],
            #[ 'CustomerCompanyZIP',     'Zip',        'zip',         1, 0, 'var', '', 0 ],
            #[ 'CustomerCompanyCity',    'City',       'city',        1, 0, 'var', '', 0 ],
            #[ 'CustomerCompanyCountry', 'Country',    'country',     1, 0, 'var', '', 0 ],
            #[ 'CustomerCompanyURL',     'URL',        'url',         1, 0, 'var', '$Data{"CustomerCompanyURL"}', 0 ],
            #[ 'CustomerCompanyComment', 'Comment',    'comments',    1, 0, 'var', '', 0 ],
            #[ 'ValidID',                'Valid',      'valid_id',    0, 1, 'int', '', 0 ],
            [ 'CustomerID',                'CustomerID',           'sname',        0, 1, 'var', '', 0 ],
            [ 'CustomerCompanyShortName',  'Nombre Corto',         'sname',        1, 1, 'var', '', 0 ],
            [ 'CustomerCompanyName',       'Cliente',              'lname',        1, 1, 'var', '', 0 ],
            [ 'ContactoPrincipal',         'Contacto Principal',   'name_main',    1, 0, 'var', '', 0 ],
            [ 'ContactoPrincipalEmail',    'Email Cto Principal',  'email_main',   1, 0, 'var', '', 0 ],
            [ 'ContactoPrincipalTel',      'Tel Cto Principal',    'telf_main',    1, 0, 'var', '', 0 ],
            [ 'ContactoTecnico',           'Contacto Tecnico',     'name_tech',    1, 0, 'var', '', 0 ],
            [ 'ContactoTecnicoEmail',      'Email Cto Tecnico',    'email_tech',   1, 0, 'var', '', 0 ],
            [ 'ContactoTecnicoTel',        'Tel Cto Tecnico',      'telf_tech',    1, 0, 'var', '', 0 ],
            [ 'ContactoComercial',         'Contacto Comercial',   'name_sales',   1, 0, 'var', '', 0 ],
            [ 'ContactoComercialEmail',    'Email Cto Comercial',  'email_sales',  1, 0, 'var', '', 0 ],
            [ 'ContactoComercialTel',      'Tel Cto Comercial',    'telf_sales',   1, 0, 'var', '', 0 ],
            [ 'ARV',                       'ARV',                  'arv',          1, 0, 'var', '', 0 ],
            [ 'PendingARV',                'ARV pendiente',        'pending_arv',  1, 0, 'var', '', 0 ],
            [ 'CustomerCompanyCategory',   'Categoria',            'category_arv', 1, 0, 'var', '', 0 ],
            [ 'ValidID',                   'Valid',                'active',       0, 1, 'int', '', 0 ],
        ],
    };

Any idea? How could I found where is the problem? I've tried all the things and no luck.

Thanks in advance,
Josep

[jmsalomr]

To help a little bit:

It seems that in $Self->{CustomerUser}, the Customer ID is set using CustomerID var:

Code: Select all

CustomerID             => 'sname',

but in $Self->{CustomerCompany} there is no CustomerID var, but CustomerCompanyKey:

Code: Select all

CustomerCompanyKey             => 'sname',

then find CustomerID inside the map, what have the same value:

Code: Select all

Map => [
...
[ 'CustomerID',             'CustomerID', 'sname', 0, 1, 'var', '', 0 ],
...

Is this ok?

Why a numeric ID (e.g. 2320) works, and an alphanumeric (e.g. CENTRE5) doesn't?

It's crazy!

[crythias]

what does "works"/"doesn't work" mean in terms that are fixable?

[jmsalomr]

Hi crythias,

I mean that if I use 'custid' (numeric field) I can see all the customers in the Customer Company view with an ID associated, and I can click over the ID (e.g. 2320) and look into the register, see:

2014-08-07 16.13.55.png

but if I use 'sname' (alphanumeric field) I cannot see the ID, and if I click the register nothing appears (of course, as I'm not pointing anywhere). I also receive an error in the OTRS register. See:

2014-08-07 16.12.41.png

2014-08-07 16.17.26.png

The Error is:
FECHA Y HORA PRIORIDAD INSTALACIÓN MENSAJE
Thu Aug 7 16:17:15 2014 error OTRS-CGI-01 Need CustomerID!

Hope this helps.

[jmsalomr]

Hi,

It seems the problem is not in the type but in the length. There is any limitation in the length of the CustomerID?

Now I've tried:
Numeric 10 bytes: Ok
Alpha 12 bytes: Not
Alpha 10 bytes: Ok
(It works even using not unique fields)

So the problem must be in another place.

[jmsalomr]

Hi,

I found the problem,but it's weird for me.

It seems that it's not possible to reuse the same field for the CustomerID in the map for another field. I mean, in my config, the following works:

Code: Select all

[ 'CustomerID',                'CustomerID',           'sname',        0, 1, 'var', '', 0 ],
#[ 'CustomerCompanyShortName',  'Nombre Corto',         'sname',        1, 1, 'var', '', 0 ],

but the following desn't:

Code: Select all

[ 'CustomerID',                'CustomerID',           'sname',        0, 1, 'var', '', 0 ],
[ 'CustomerCompanyShortName',  'Nombre Corto',         'sname',        1, 1, 'var', '', 0 ],

I'm using it for the backend of the CustomerUser (not CustomerCompany) and apparently there is no problem:

Code: Select all

[ 'UserCustomerID',   'CustomerID',  'sname',        0, 1, 'var', '', 0 ],
[ 'UserCustomerIDs',  'CustomerIDs', 'sname',        1, 0, 'var', '', 0 ],