Retrieving data from external database

[kruegerM]

One of our required fields is the customer's department/cost center. We have that information in a separate MYSQL database; I'd like to grab the information from there rather than use a freetext field with the requirements of having to keep it in sync with the "official" data.
If anyone has any experience or information on how to pull the information from the external source into a <select> within a DTL page, it would be greatly appreciated.
Thanx in advance!

Marc

[crythias]

What kind of data is it? Is it something that can be loaded and used as Company data, or is it something that needs to be attached/selectable per user?

[kruegerM]

Because a user might work under multiple cost centers within a department, they need to select the information each time they create a ticket.

[renee]

Can you describe the requirements more detailed?

In General:
You should create a new Kernel::System::<AMeaningfulName> (You should select a name that indicates what it is for like Kernel::System::Queue handles the queue related stuff) module that does the database queries. That is the so called backend module. In the frontend module (in your case I guess it's Kernel::Modules::AgentTicketPhone or K::M::AgentTicketEmail) you have to "use" that backend module and create an object of it. And you have to retrieve the data from database via the backend module.

You can use the "BuildSelection" method to create the drop down (look for other dropdowns). Last but not least you have to edit the appropriate .dtl and include the dropdown.

This is just a very rough description of the work but without knowing more details it is hard to help...

[kruegerM]

The following is a sample list of what our Department drop-down should have:

Accounting - 12345
Finance - 54321
Medicine - 55555
Medicine - 55543
Medicine - 55343
Nursing - 23252
Nursing - 25112
etc...

A customer must select their department with the correct cost center suffix depending on the type of work to be done. If I do this in a freetext field, I can better define departments:
...
Medicine/Physician - 55555
Medicine/Pharmacy - 55543
etc...
But that means keeping in sync with what Administration puts out in department/cost center listings. As our departments are in a constant state of growth/flux - this could become a tedious proposition - which is why I'm trying to find a way to pull the list from their database.

Don't know if that's enough detail ... I will also take a look into the information you gave - Thank You.

[renee]

Ok. let's give it a try....

Disclaimer: The following code is experimental, you have to change some settings (e.g. I don't know the model of the external database) and test it.

This could be your Kernel/System/CostCenter.pm:

Code: Select all

package Kernel::System::CostCenter;

use strict;
use warnings;

use Kernel::System::DB;

sub new {
    my ( $Class,%Param ) = @_;
    my $Self = bless {}, $Class;

    for my $Object ( qw(LogObject ConfigObject EncodeObject) ) {
        $Self->{$Object} = $Param{$Object} or die "Need $Object!";
    }

    $Self->{DBObject} = Kernel::System::DB->new(
        %{$Self},
        DatabaseDSN => 'DBI:mysql:DBName:Hostname', # TODO: use live data
        DatabaseUser => 'user for external db', # TODO: use live data
        DatabasePw => 'password for external db', # TODO: use live data
    );

    return $Self;
}

# CostCenterList returns a hash with all costcenters
sub CostCenterList {
    my ($Self) = @_;

    my $SQL = 'SELECT id, name, cc_number FROM table_name'; # TODO: use live data
    return if !$Self->{DBObject}->Prepare(
        SQL => $SQL,
    );

    my %List;
    while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
        $List{ $Row[0] } = "$Row[1] - $Row[2]";
    }

    return %List;
}

# This subroutine can be used to get more details for a single costcenter
# it returns a hash with all information.
# it requires 1 parameter: ID
# my %Info = $Object->CostCenterGet( ID => 123 );
sub CostCenterGet {
    my ( $Self, %Param ) = @_;

    if ( !$Param{ID} ) {
        $Self->{LogObject}->Log( Priority => 'error', Message => 'Need ID' );
        return;
    }

    my $SQL = 'SELECT id, name, cc_number FROM table_name where id = ?'; # TODO: use live data
    return if !$Self->{DBObject}->Prepare(
        SQL => $SQL,
        Bind => [ \$Param{ID} ],
        Limit => 1,
    );

    my %Info;
    while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
        $Info{ID} = $Row[0];
        $Info{Name} = $Row[1];
        $Info{Number} = $Row[2];
    }

    return %Info;
}

1;

In Kernel/Modules/AgentTicketPhone.pm;

where the other "use" statements are:

Code: Select all

use Kernel::System::CostCenter; 

where the other objects are created:

Code: Select all

$Self->{CostCenterObject} = Kernel::System::CostCenter->new(%Param);

where the other "BuildSelections()" are:

Code: Select all

my %CostCenter = $Self->{CostCenterObject}->CostCenterList();
$Param{CostCenterSelect} = $Self->{LayoutObject}->BuildSelection(
    Name => 'CostCenterID',
    Data => \%CostCenter,
    PossibleNone => 1,
);

And in your .dtl file:

Code: Select all

$Data{CostCenterSelect}

If you need a freelancer who implements it, you can drop me an email...