$Self->{Customer} = {

[jevans]

Does anyone have all the options for "$Self->{CustomerUser} = {" backend?

I'd like to use a SQL Query / MAP for my SugarCRM Database.

Thank You,
-Jake

[reneeb]

See Kernel/Config/Defaults.pm

[jevans]

Thanks, it looks like Customer Company is what I want to use, is there a way to to a SQL Query? It looks like I may be better off with a script.

[jojo]

you don't need a SQL query. Just add the structure from Defaults.pm to Config.pm
Have a deeper look here: http://otrs.github.io/doc/manual/admin/ ... er-backend

[jevans]

The information I need is in 2 tables that I want to inner join.

Company_ID is an internal account number in accounts_cstm
Company Name, Address and other info is in accounts

This is SuiteCRM (sugarcrm)

Thanks,
-Jake

[crythias]

create a view and query it instead.

[jevans]

That is working nicely, say 95%.

the last 5% if I can get the CRM link to show nicely.

I used the Comment frontend field for the SugarCRM account link, but it shows the full URL

Code: Select all

            [ 'CustomerCompanyComment', 'SugarCRM',   'sugarlink',   1, 0, 'var', '[% Data.CustomerCompanyComment | html %]', 1 ],

Renders
SugarCRM:
https://sugarcrm/index.php?module=Accou ... -xxxx-xxxx

is there a way to give the link a title, either from the database or a static name (looking though the .dtl/.tt files without success)


MySQL View

Code: Select all

create view otrs as select name, billing_address_street as street, billing_address_postalcode as zip, billing_address_city as city, 'United States' as country, account_number_c as customer_id, website as url, CONCAT(  "https://sugarcrm/index.php?module=Accounts&action=DetailView&record=", id ) AS sugarlink from accounts inner join accounts_cstm on id = id_c where deleted = 0 and account_number_c <> '' and account_number_c <> 'account0000' and account_number_c <> 'account9999';

Config.pm

Code: Select all

    $Self->{CustomerCompany1} = {
        Name   => 'SugarCRM',
        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=sugarcrm;host=sugarcrm',
            User => 'RO_Account',
            Password => 'Password',
            Table => 'otrs',
            ForeignDB => 1,    # set this to 1 if your table does not have create_time, create_by, change_time and change_by fields

            # 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 => 1,
        },

        # 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 => [
            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly
            [ 'CustomerID',             'CustomerID', 'customer_id', 0, 1, 'var', '', 1 ],
            [ 'CustomerCompanyName',    'Customer',   'name',        1, 1, 'var', '', 1 ],
            [ 'CustomerCompanyStreet',  'Street',     'street',      1, 0, 'var', '', 1 ],
            [ 'CustomerCompanyZIP',     'Zip',        'zip',         1, 0, 'var', '', 1 ],
            [ 'CustomerCompanyCity',    'City',       'city',        1, 0, 'var', '', 1 ],
            [ 'CustomerCompanyCountry', 'Country',    'country',     1, 0, 'var', '', 1 ],
            [ 'CustomerCompanyURL',     'URL',        'url',         1, 0, 'var', '[% Data.CustomerCompanyURL | html %]', 1 ],
            #[ 'CustomerCompanyComment','Comment',    'comments',    1, 0, 'var', '', 1 ],
            [ 'CustomerCompanyComment', 'SugarCRM',   'sugarlink',   1, 0, 'var', '[% Data.CustomerCompanyComment | html %]', 1 ],
            #[ 'ValidID',               'Valid',      'valid_id',    0, 1, 'int', '', 1 ],
        ],
    };

I hope someone else finds this helpful.

[crythias]

don't make the link attached to data. Use the url wrapper of the Map (http-link).

viewtopic.php?f=60&t=7787&p=30834

[jevans]

I took a look at DashboardCustomerCompanyInformation.pm and added this:

Code: Select all

   122         # CUSTOM SugarCRM Link check if a link must be placed
    123         if ( $Entry->[7] ) {
    124             $LayoutObject->Block(
    125                 Name => "ContentSmallCustomerCompanyInformationRowLink",
    126                 Data => {
    127                     %CustomerCompany,
    128                     Label  => $Entry->[1],
    129                     Value  => $CustomerCompany{$Key},
    130                     URL    => $Entry->[7],
    131                     Target => '_blank',
    132                 },
    133             );

but I have no idea what determines Entry #

Appreciate the help.