[Rt-devel] Updating Custom Field values from External Source

[Emmanuel Lacour]

On Wed, Jun 09, 2004 at 03:14:57PM -0400, Scott Hebert wrote:
> Hi,
> 
> How can one go about updating Custom Fields values using data from an
> external source?
> 
> I tried using rt-setup-database --action insert using a datafile which
> contains this:
> 


I would suggest using the Rt API or the "rt" command itself.


Here is an example I was using 2 weeks ago ( I don't use customfields for
this purpose anymore).

It's perfectible and not really fast, because it was my first Rt scripT.
And sorry, I don't have time to clean it and comment it, but I give it
to the list anyway, it can give some hints;-)

-----------------
#!/home/rt/perl/bin/perl
# Import customers from my custommer db as rt "client" customfield
# values

use strict;
use DBI;
use Unicode::String qw(utf8 latin1);
use lib "/home/rt/rt/lib";
use lib "/home/rt/rt/etc";
use RT;
use RT::Interface::CLI qw( CleanEnv GetCurrentUser );
use RT::CustomFieldValue;
use RT::CustomFieldValues;
use RT::CustomField;
use RT::CustomFields;

CleanEnv();
RT::LoadConfig();
RT::Init();
#Drop setgid permissions
RT::DropSetGIDPermissions();

#Get the current user all loaded
our $CurrentUser = GetCurrentUser();

unless( $CurrentUser->Id )
{
        print "No RT user found. Please consult your RT
administrator.\n";
        exit 1;
}

my $dbname = "cust";
my $dbuser = "cust";
my $dbpass = "cust";
my $dbtype = "Pg";


my $dbh = DBI->connect(
"dbi:$dbtype:dbname=$dbname","$dbuser","$dbpass");

# Custommers select in my db
my $query = "select company.knot_cid,company.eeid,knot_table.knot_name
from knot_table, company where (knot_table.parent_id='11' or
knot_table.parent_id='9')  and knot_table.knot_cid=company.knot_cid
order by knot_table.knot_name";

my $sth = $dbh->prepare($query);
$sth->execute();

while( my $result = $sth->fetchrow_hashref() ) {
        my $eeid = $result->{'eeid'};
        $eeid =~ s/[ \t]+//g;
        next if ( $eeid eq "" );
        my $found = 0;
        my $CustomFields = RT::CustomFields->new($CurrentUser);
        $CustomFields->LimitToGlobal;
        while (my $CustomField = $CustomFields->Next) {
                next if ($CustomField->Name ne "client");
                my $CustomFieldValues = $CustomField->Values();
                while ((my $value = $CustomFieldValues->Next) && ($found
== 0)) {
                        $found = 1 if ($value->Name =~ /^[ \t]*$eeid[
\t]*$/);
                }
        }
        my $latin1 = Unicode::String::latin1($result->{'knot_name'});
        my $name = $latin1->utf8();
        if ( $found == 1) {
                print $eeid." - ".$name." - already exists\n";
                next;
        }
        my %value = (CustomField => '1',
                     Name => $eeid,
                     Description => $name );
        my $value_obj = new RT::CustomFieldValue( $CurrentUser );
        $value_obj->Create(%value);
}

$sth->finish;
$dbh->disconnect;



-- 
Emmanuel Lacour ------------------------------------ Easter-eggs
44-46 rue de l'Ouest  -  75014 Paris   -   France -  Métro Gaité
Phone: +33 (0) 1 43 35 00 37    -     Fax: +33 (0) 1 41 35 00 76
mailto:elacour at easter-eggs.com   -    http://www.easter-eggs.com