Import Customers
Moderator: crythias
Import Customers
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
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
-
- Administrator
- Posts: 3964
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: Import Customers
Hi,
Why not connecting the other system as a data source?
- Roy
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 ?
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 ?
Re: Import Customers
Hi Roy,
Is this option described somewhere?
Thanks,
George
Is this option described somewhere?
Thanks,
George
-
- Administrator
- Posts: 3964
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: Import Customers
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 ?
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 ?
Re: Import Customers
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
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
-
- Administrator
- Posts: 3964
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: Import Customers
Hi,
- Roy
Change the name by changing "Database Backend" to something you want.
Code: Select all
Name => Translatable('Database Backend'),
yes
no, there is nothing such as import from to the other. This is no import, it's live accessing to a database.
I could, but must not. I would go with a few but not with many. It always depends on your environment.
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 ?
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 ?
Re: Import Customers
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
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
Re: Import Customers
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
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
-
- Administrator
- Posts: 3964
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: Import Customers
Hi,
Like this?
- Roy
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 ],
],
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 ?
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 ?
Re: Import Customers
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
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
-
- Administrator
- Posts: 3964
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: Import Customers
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 ?
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 ?
Re: Import Customers
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
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
-
- Administrator
- Posts: 3964
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: Import Customers
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
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 ?
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 ?
Re: Import Customers
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
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
-
- Administrator
- Posts: 3964
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: Import Customers
Hi,
- Roy
Do you have any Dynamic Field for CustomerCompany configured?
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 ?
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 ?
Re: Import Customers
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
But I commented them out earlier just to see if this causes the problem.
Still, nothing gets in from CustomerCompany1.
Thanks,
George
-
- Administrator
- Posts: 3964
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: Import Customers
Hi,
Please paste your complete CustomerCompany1 configuration (xxx the password please) and the table schema (desc TABLENAME).
- Roy
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 ?
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 ?
Re: Import Customers
Hi Roy,
Pasting CustomerCompany1 and table schema.
@@@@@@@@
Thanks for your help.
Regards,
George
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 | |
+----------------------+---------------+------+-----+-------------------+----------------+
Regards,
George
Last edited by crythias on 27 Apr 2021, 18:34, edited 1 time in total.
Reason: Code tags added for clarity
Reason: Code tags added for clarity
-
- Administrator
- Posts: 3964
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: Import Customers
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
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 ?
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 ?
Re: Import Customers
Hi,
Tried it again step by step and it worked.
I used another table though.
Thanks for your help!
George
Tried it again step by step and it worked.
I used another table though.
Thanks for your help!
George