Import Customers

Moderator: crythias

Post Reply
georgegal
Znuny newbie
Posts: 18
Joined: 09 Jun 2016, 13:47
Znuny Version: 6.0.30
Real Name: George

Import Customers

Post by georgegal »

Hello,

What is the best way to import the customers from another system?
Since changes (additions, deletions, changes) may happen to customers in the existing system, these changes should pass also to OTRS in a scheduled basis.

Thank you!
George
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Import Customers

Post by root »

Hi,

Why not connecting the other system as a data source?

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
georgegal
Znuny newbie
Posts: 18
Joined: 09 Jun 2016, 13:47
Znuny Version: 6.0.30
Real Name: George

Re: Import Customers

Post by georgegal »

Hi Roy,

Is this option described somewhere?

Thanks,
George
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Import Customers

Post by root »

georgegal wrote: 20 Apr 2021, 15:58 Is this option described somewhere?
Hi George,

In every admin training ;-)

Here's a rough "recipe":

1. Copy https://github.com/znuny/Znuny/blob/rel ... s.pm#L1451 to L1560 for Customer User into your Config.pm an replace $Self->{CustomerUser} with $Self->{CustomerUser1} (or the 1 with any other free number between 1 and 10
2. If you need also CustomerCompany do the same with https://github.com/znuny/Znuny/blob/rel ... s.pm#L1636 to line 1697 and also add a number into $Self->{CustomerCompany}
3. Configure both configurations as needed (read the comments), especially regarding database connection settings and column names

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
georgegal
Znuny newbie
Posts: 18
Joined: 09 Jun 2016, 13:47
Znuny Version: 6.0.30
Real Name: George

Re: Import Customers

Post by georgegal »

Hi Roy,

Thank you for your answer.
I copied the company part in the Config.pm and added the number 1 ( $Self->{CustomerCompany1} = {).
Now on Customer Management I see two Database Backend with the same name.
I guess now I can add a new customer to which one database I choose, or import the customers from other system to the new one.
Is this the right way to go? To have several Database Backends instead of one?

Another question.
Assuming I have some Car Leasing Companies as customers.
Company 1 with cars Car1 to Car100 and each one with different plate number (Plate1 to Plate100).
Company 2 with cars Car101 to Car200 and each one with different plate number (Plate101 to Plate200).
I guess CarX and PlateX should be specific dynamic fields ("Car" and "Plate").
How can I bind "Car1 to Car100" and "Plate1 to Plate100" to Company 1 and "Car101 to Car200" and "Plate101 to Plate200" to Company 2?
So when I open a ticket for Company 1, the possible options in the dynamic field "Car" could be just Car1 to Car100.

Thank you for your time.

Regards,
George
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Import Customers

Post by root »

Hi,
georgegal wrote: 21 Apr 2021, 11:16 I copied the company part in the Config.pm and added the number 1 ( $Self->{CustomerCompany1} = {).
Now on Customer Management I see two Database Backend with the same name.
Change the name by changing "Database Backend" to something you want.

Code: Select all

Name   => Translatable('Database Backend'),
georgegal wrote: 21 Apr 2021, 11:16 I guess now I can add a new customer to which one database I choose,
yes
georgegal wrote: 21 Apr 2021, 11:16 or import the customers from other system to the new one.
no, there is nothing such as import from to the other. This is no import, it's live accessing to a database.
georgegal wrote: 21 Apr 2021, 11:16 Is this the right way to go? To have several Database Backends instead of one?
I could, but must not. I would go with a few but not with many. It always depends on your environment.
georgegal wrote: 21 Apr 2021, 11:16 Another question.
For another question, I recommend using a new post. Consider that your headline "Import Customer" might spook others which could have an answer for you.


- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
georgegal
Znuny newbie
Posts: 18
Joined: 09 Jun 2016, 13:47
Znuny Version: 6.0.30
Real Name: George

Re: Import Customers

Post by georgegal »

Hi Roy,

Since the second database is now set, I will try to connect it to my other system and access the customers.
As for the other question I will open a new post.

Thank you again for your time!

Regards,
George
georgegal
Znuny newbie
Posts: 18
Joined: 09 Jun 2016, 13:47
Znuny Version: 6.0.30
Real Name: George

Re: Import Customers

Post by georgegal »

Hello all,

I'm having problem reading the customers from the other system.
I have completed the configuration for the remote mysql (dsn, user, password, table) in the Config.pm file under $Self->{CustomerCompany1}.
For testing this I'm using just three fields (CustomerID, CustomerCompanyName, ValidID), all the others are commented out.
How should I configure the part for mapping the relevant remote database table fields with these three?

Regards,
George
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Import Customers

Post by root »

Hi,

Like this?

Code: Select all

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 ],
      [ 'ValidID',                'Valid',      'valid_id',    0, 1, 'int', '', 0 ],
],
- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
georgegal
Znuny newbie
Posts: 18
Joined: 09 Jun 2016, 13:47
Znuny Version: 6.0.30
Real Name: George

Re: Import Customers

Post by georgegal »

Hi Roy,

Yes like this.
But on the third column instead of customer_id, name and valid_id I'm using the column names of the remote database table.
Is this correct?

Thanks,
George
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Import Customers

Post by root »

georgegal wrote: 23 Apr 2021, 12:44 But on the third column instead of customer_id, name and valid_id I'm using the column names of the remote database table.
Is this correct?
Absolutely.

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
georgegal
Znuny newbie
Posts: 18
Joined: 09 Jun 2016, 13:47
Znuny Version: 6.0.30
Real Name: George

Re: Import Customers

Post by georgegal »

Hi Roy,

This is my configuration in Config.pm
I don't know what is wrong. I can't see any customer from this database.

$Self->{CustomerCompany1} = {
Name => Translatable('CustomerCareSystem'),
Module => 'Kernel::System::CustomerCompany::DB',
Params => {

DSN => 'DBI:mysql:database=db-name;host=192.168.1.105',
User => 'username',
Password => 'password',
Table => 'table-name',
ForeignDB => 1,

SearchCaseSensitive => 0,
},

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

Map => [

[ 'CustomerID', 'CustomerID', 'id', 0, 1, 'var', '', 0 ],
[ 'CustomerCompanyName', 'Customer', 'company', 1, 1, 'var', '', 0 ],
[ 'ValidID', 'Valid', 'active', 0, 1, 'var', '', 0 ],

],
};

Thanks,
George
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Import Customers

Post by root »

Hi,

Turn off the cache as long as you test the configuration and check the system log. Maybe there's a hint.

And:

CustomerCompanyKey => 'customer_id',
CustomerCompanyListFields => [ 'customer_id', 'name' ],
CustomerCompanySearchFields => [ 'customer_id', 'name' ],

Replace customer_id by id and name with company (that's what I got from your mapping)

-Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
georgegal
Znuny newbie
Posts: 18
Joined: 09 Jun 2016, 13:47
Znuny Version: 6.0.30
Real Name: George

Re: Import Customers

Post by georgegal »

Hi Roy,

I changed the values but nothing happened.
The error log i get is the following.

[Fri Apr 23 15:45:08 2021][Debug][Kernel::System::DynamicField::Backend::ValueGet][956] Unable to fetch object mapping for object name 503 and type CustomerCompany!

Does it have to do with $Self->{CustomerCompany} in Config.pm?
This part is copy/paste from Defaults.pm file.

Thanks,
George
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Import Customers

Post by root »

Hi,
georgegal wrote: 23 Apr 2021, 16:02 [Fri Apr 23 15:45:08 2021][Debug][Kernel::System::DynamicField::Backend::ValueGet][956] Unable to fetch object mapping for object name 503 and type CustomerCompany!
Do you have any Dynamic Field for CustomerCompany configured?

georgegal wrote: 23 Apr 2021, 16:02 Does it have to do with $Self->{CustomerCompany} in Config.pm?
This part is copy/paste from Defaults.pm file.
Don't think so, bwt the log does not show the number from the config,

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
georgegal
Znuny newbie
Posts: 18
Joined: 09 Jun 2016, 13:47
Znuny Version: 6.0.30
Real Name: George

Re: Import Customers

Post by georgegal »

Yes, I had two Dynamic Field for CustomerCompany configured.
But I commented them out earlier just to see if this causes the problem.
Still, nothing gets in from CustomerCompany1.

Thanks,
George
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Import Customers

Post by root »

Hi,

Please paste your complete CustomerCompany1 configuration (xxx the password please) and the table schema (desc TABLENAME).

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
georgegal
Znuny newbie
Posts: 18
Joined: 09 Jun 2016, 13:47
Znuny Version: 6.0.30
Real Name: George

Re: Import Customers

Post by georgegal »

Hi Roy,

Pasting CustomerCompany1 and table schema.

Code: Select all

    $Self->{CustomerCompany1} = {
        Name   => Translatable('CustomerCareSystem'),
        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 => '',
            DSN => 'DBI:mysql:database=mytickets;host=192.168.1.105',
            User => 'ticketing',
            Password => 'xxx',
            Table => 'cc_agent',
            ForeignDB => 1,    # set this to 1 if your table does not have create_time, create_by, change_time and change_by fields

            # CaseSensitive defines if the data storage of your DBMS is case sensitive and will be
            # preconfigured within the database driver by default.
            # If the collation of your data storage differs from the default settings,
            # you can set the current behavior ( either 1 = CaseSensitive or 0 = CaseINSensitive )
            # to fit your environment.
            #
#            CaseSensitive => 0,

            # SearchCaseSensitive will control if the searches within the data storage are performed
            # case sensitively (if possible) or not. Change this option to 1, if you want to search case sensitive.
            # This can improve the performance dramatically on large databases.
            SearchCaseSensitive => 0,
        },

        # company unique id

        CustomerCompanyKey             => 'id',
        CustomerCompanyValid           => 'active',
        CustomerCompanyListFields      => [ 'id', 'company' ],
        CustomerCompanySearchFields    => [ 'id', 'company' ],
        CustomerCompanySearchPrefix    => '*',
        CustomerCompanySearchSuffix    => '*',
        CustomerCompanySearchListLimit => 250,
        CacheTTL                       => 0 * 0 * 0,
#        CacheTTL                       => 60 * 60 * 24, # use 0 to turn off cache

        Map => [

            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly
            [ 'CustomerID',             'CustomerID', 'id', 0, 1, 'var', '', 0 ],
            [ 'CustomerCompanyName',    'Customer',   'company',        1, 1, 'var', '', 0 ],
#            [ 'CustomerCompanyStreet',  'Street',     'address',      1, 0, 'var', '', 0 ],
#            [ 'CustomerCompanyZIP',     'Zip',        'zipcode',         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 | html %]', 0 ],
#            [ 'CustomerCompanyComment', 'Comment',    'email',    1, 0, 'var', '', 0 ],
            [ 'ValidID',                'Valid',      'active',    0, 1, 'var', '', 0 ],

        ],
    };
@@@@@@@@

Code: Select all

+----------------------+---------------+------+-----+-------------------+----------------+
| Field                | Type          | Null | Key | Default           | Extra          |
+----------------------+---------------+------+-----+-------------------+----------------+
| id                   | bigint(20)    | NO   | PRI | NULL              | auto_increment |
| datecreation         | timestamp     | NO   |     | CURRENT_TIMESTAMP |                |
| active               | char(1)       | NO   |     | f                 |                |
| login                | char(20)      | NO   | UNI | NULL              |                |
| passwd               | char(40)      | YES  |     | NULL              |                |
| location             | text          | YES  |     | NULL              |                |
| language             | char(5)       | YES  |     | en                |                |
| id_tariffgroup       | int(11)       | YES  |     | NULL              |                |
| options              | int(11)       | NO   |     | 0                 |                |
| credit               | decimal(15,5) | NO   |     | 0.00000           |                |
| currency             | char(3)       | YES  |     | USD               |                |
| locale               | char(10)      | YES  |     | C                 |                |
| commission           | decimal(10,4) | NO   |     | 0.0000            |                |
| vat                  | decimal(10,4) | NO   |     | 0.0000            |                |
| banner               | text          | YES  |     | NULL              |                |
| perms                | int(11)       | YES  |     | NULL              |                |
| lastname             | char(50)      | YES  |     | NULL              |                |
| firstname            | char(50)      | YES  |     | NULL              |                |
| address              | char(100)     | YES  |     | NULL              |                |
| city                 | char(40)      | YES  |     | NULL              |                |
| state                | char(40)      | YES  |     | NULL              |                |
| country              | char(40)      | YES  |     | NULL              |                |
| zipcode              | char(20)      | YES  |     | NULL              |                |
| phone                | char(20)      | YES  |     | NULL              |                |
| email                | char(70)      | YES  |     | NULL              |                |
| fax                  | char(20)      | YES  |     | NULL              |                |
| company              | varchar(50)   | YES  |     | NULL              |                |
| com_balance          | decimal(15,5) | NO   |     | NULL              |                |
| threshold_remittance | decimal(15,5) | NO   |     | NULL              |                |
| bank_info            | mediumtext    | YES  |     | NULL              |                |
+----------------------+---------------+------+-----+-------------------+----------------+
Thanks for your help.

Regards,
George
Last edited by crythias on 27 Apr 2021, 18:34, edited 1 time in total.
Reason: Code tags added for clarity
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Import Customers

Post by root »

Hi,

This configuration works for me. The only issue that came up is the field id in the table because it's integer and auto-increment. This might cause some trouble when creating a company.

My debug steps would be:

- Configure the SysConfig setting MinimumLogLevel to debug
- Search for a company from cc_table AdminCustomerCompany
- Check Apache's error_log and OTRS log

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
georgegal
Znuny newbie
Posts: 18
Joined: 09 Jun 2016, 13:47
Znuny Version: 6.0.30
Real Name: George

Re: Import Customers

Post by georgegal »

Hi,

Tried it again step by step and it worked.
I used another table though.

Thanks for your help!

George
Post Reply