Get CurrentUser in External Custom Field Values Script

RT Users
1

Hi,

Is there a way I can get the current user in an external custom field values script ? I want to display the current user projects from a database. Right now I can display all the projects from a data base, but I want to display just the current user projects.

Thank you

UPDATE SOLUTION:

package RT::CustomFieldValues::Proiecte;
use base qw(RT::CustomFieldValues::External);

use strict;
use warnings;
use v5.10; 

use DBI;


sub SourceDescription {
    return 'Proiecte';
}

sub QuerySQL{
    ## MySQL database configurations    
    my $table = 'Projects';
    my $database = 'rt4';
    my $hostname = 'localhost';
    my $port = '3306';
    my $username = " ";
    my $password = ' ';

    my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
    my $dbh = DBI->connect($dsn, $username, $password);

    my @res;
    my $i = 0;


    my ($current_user) = @_;

    my $sth = $dbh->prepare("SELECT * FROM $table WHERE User='$current_user' ");
    $sth->execute() or die "execution failed: $dbh->errstr()";




    while(my $row = $sth->fetchrow_hashref()){
        push @res, {
            name        => $row->{'Project'},
            sortorder   => $i++,
        };
        
    }   
    
    $sth->finish();
    $dbh->disconnect();
    return \@res;
}



sub ExternalValues {  
    my $self = shift;
    my $current_user = $self->CurrentUser->Name;
    my $projects = QuerySQL($current_user); 
    return $projects; 
}



1;
2

You can get the current user object by adding the following to sub ExternalValues:

  my $self = shift;
  my $user = $self->CurrentUser;
3

Hi @knation,

Thanks for your reply. Is there a way I can get the current user outside of sub ExternalValues ? I want to use that variable in the SQL QUERY

Here is my script

package RT::CustomFieldValues::Proiecte;


use base qw(RT::CustomFieldValues::External);

use strict;
use warnings;
use v5.10; 

use DBI;



# MySQL database configurations
my $dsn = "DBI:mysql:rt4";
my $username = "";
my $password = '';

# connect to MySQL database
my %attr = ( PrintError=>0,  # turn off error reporting via warn()
             RaiseError=>1   # report error via die()
);
my $dbh = DBI->connect($dsn,$username,$password,\%attr);

sub SourceDescription {
    return 'Proiecte';
}

sub QuerySQL{

    my @res;
    my $i = 0;


    my ($dbh) = @_;
    my $sql = "SELECT * FROM Projects";
    my $sth = $dbh->prepare($sql); 

    $sth->execute();

    while(my @row = $sth->fetchrow_array()){
        push @res, {
            name        => $row[1],
            sortorder   => $i++,
        };
        # printf("%s\t%s\n",$row[1],$row[2]);
    }   

    return \@res;
    $sth->finish();
}

my $projects = QuerySQL($dbh); 


sub ExternalValues {   
    # my $self = shift;
    # my $current_user = $self->CurrentUser;     
    return $projects
}

RT::Base->_ImportOverlays();

# query data from the links table
#ExternalValues($dbh);

# disconnect from the MySQL database
$dbh->disconnect();

1;
4

Why not just pass the current user to your QuerySQL subroutine?

sub ExternalValues {   
    my $self = shift;

    return QuerySQL($dbh, $self->CurrentUser); 
}
5

I’ve already try this

sub ExternalValues {   
    my $self = shift;
    my $current_user = $self->CurrentUser;  

    return QuerySQL($dbh, $current_user); 
}

with this SQL QUERY

my $sql = "SELECT * FROM Projects WHERE User=$current_user";

but I get this error : Global symbol "$current_user" requires explicit package name (did you forget to declare "my $current_user"?)

It doesn’t know what is the $current_user in the SQL QUERY

6

You have to grab it the same way you grab the database handle

    my ($dbh, $current_user) = @_;
7

Thank for your help @knation, It was my mistake because I’m not very good with Perl language :smiley:

So, this is my solution for anyone who wants to know

package RT::CustomFieldValues::Proiecte;
use base qw(RT::CustomFieldValues::External);

use strict;
use warnings;
use v5.10; 

use DBI;


sub SourceDescription {
    return 'Proiecte';
}

sub QuerySQL{
    ## MySQL database configurations    
    my $table = 'Projects';
    my $database = 'rt4';
    my $hostname = 'localhost';
    my $port = '3306';
    my $username = " ";
    my $password = ' ';

    my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
    my $dbh = DBI->connect($dsn, $username, $password);

    my @res;
    my $i = 0;


    my ($current_user) = @_;

    my $sth = $dbh->prepare("SELECT * FROM $table WHERE User='$current_user' ");
    $sth->execute() or die "execution failed: $dbh->errstr()";




    while(my $row = $sth->fetchrow_hashref()){
        push @res, {
            name        => $row->{'Project'},
            sortorder   => $i++,
        };
        
    }   
    
    $sth->finish();
    $dbh->disconnect();
    return \@res;
}



sub ExternalValues {  
    my $self = shift;
    my $current_user = $self->CurrentUser->Name;
    my $projects = QuerySQL($current_user); 
    return $projects; 
}



1;
1 Like