help logoLON-CAPA Help


Two subroutines exist in localenroll.pm to provide a connection between institutional directory data (e.g., user information from LDAP) and LON-CAPA. The first is get_userinfo() which can operate in two modes.: (a) it can be used to provide first name, last name, e-mail address, student/employee ID etc., for a specified username, e.g., for a new user being created in LON-CAPA, and (b) it can be used to retrieve user information for multiple users from an institutional directory searches when (for example) a course coordinator is adding a new user directly to a course. At MSU the routine which actually queries institutional data sources is itself called by get_userinfo(). This was done so that the same underlying routine can also be used by the second of the two subroutines: allusers_info() which is called by Autoupdate.pl (a script which can be run periodically to reconcile user information in LON-CAPA with institutional directory data for all users).

get_userinfo

Four required arguments and additional optional arguments

Two modes of operation:

  1. Retrieves institutional data for a single user either by username, if $uname is included as second argument,

    or by ID if $id is included as a third argument. Either second or third arguments must be provided; seventh, eighth and ninth args will be undefined.

  2. Retrieves institutional user data from search of an institutional directory based on a search. seventh and eighth args are required; ninth is optional. second and third will be undefined.

Arguments:

  1. $dom - domain

  2. $uname - username of user

  3. $id - student/faculty ID of user

  4. $instusers - reference to hash which will contain info for user as key = value; keys will be one or all of: lastname, firstname, middlename, generation, id, inststatus - institutional status (e.g., faculty,staff,student).

    Values are all scalars except inststatus, which is an array.

  5. $instids - reference to hash which will contain ID numbers - keys will be unique IDs (student or faculty/staff ID)

    values will be either: scalar (username) or an array if a single ID matches multiple usernames.

  6. $types - optional reference to array which contains institutional types to check.

  7. $srchby - optional if $uname or $id defined, otherwise required.

    Allowed values include: 1. lastfirst, 2. last, 3. uname corresponding to searches by 1. lastname,firstname; 2. lastname; 3. username

  8. $srchterm - optional if $uname or $id defined, otherwise required - String to search for.

  9. $srchtype - optional. Allowed values: contains, begins (defaults to exact match otherwise).

Returns 'ok' if no error occurred. Side effects - populates the $instusers and $instids refs to hashes with information for specified username, or specified id, if fifth argument provided, from all available, or specified (e.g., faculty only) institutional datafeeds, if sixth argument provided.

At MSU six separate MS-SQL database tables are queried, with each table corresponding to a specific institutional type. A routine is called to connect to the database. and the actual queries are handled by a separate routine - query_user_tables().

sub get_userinfo {
my ($dom,$uname,$id,$instusers,$instids,$types,
$srchby,$srchterm,$srchtype) = @_;
my $outcome;
my @srchtables;
my %tables = (
Faculty => 'FACULTY_VU',

Staff => 'STAFF_VU',

Student => 'STUDENT',

Assistant => 'ASSISTANT',

StaffAff => 'AFFILIATE',

StuAff => 'STUDENT_AFFILIATE'

);

my ($dbh,$dbflag) = &connect_DB('HR');

foreach my $type (@{$types}) {

if (exists($tables{$type})) {
push(@srchtables,$tables{$type});
}
}

if (@srchtables == 0) {

foreach my $type (keys(%tables)) {
push(@srchtables,$tables{$type});
}
}

if ($srchby eq " && $srchterm eq ") {

if ($uname ne ") {
$srchby = 'uname';

$srchterm = $uname;

} elsif ($id ne ") {
$srchby = 'id';

$srchterm = $id;

}
}

if ($srchterm ne ") {

$outcome = &query_user_tables($dbflag,$dbh,\@srchtables,
       $instusers,$instids,$srchby,$srchterm,$srchtype,$types);
}

if ($dbflag) {

&disconnect_DB($dbh);
}

return $outcome;

}

Although query_user_tables() is not a subroutine included as a stub in the standard localenroll.pm, it is included below to show how the database queries are implemented at MSU.

sub query_user_tables {

my ($dbflag,$dbh,$srchtables,$instusers,$instids,$srchby,$srchterm,     $srchtype,$types) = @_;

my ($outcome,$condition,%multipids,$ldapfilter);

if ($srchby eq 'uname') {

if ($srchterm =~ /^\w{2,8}$/) {
if ($srchtype eq 'contains') {

  $condition = "WHERE MSUNetID LIKE '%$srchterm%'";

  $ldapfilter = '(uid=*'.$srchterm.'*)';

} elsif ($srchtype eq 'begins') {

  $condition = "WHERE MSUNetID LIKE '$srchterm%'";

  $ldapfilter = '(uid='.$srchterm.'*)';

} else {

  $condition = "WHERE MSUNetID = '$srchterm'";

  $ldapfilter = '(uid='.$srchterm.')';

}

}
} elsif ($srchby eq 'lastname') {
if ($srchterm =~ /[A-Za-z\-\.'\s]+/) {
if ($srchtype eq 'contains') {

  if ($dbflag) {

    my $quoted_last = $dbh->quote('%'.$srchterm.'%');

    $condition = "WHERE LastName LIKE $quoted_last";

  }

  $ldapfilter = '(sn=*'.$srchterm.'*)';

} elsif ($srchtype eq 'begins') {

  if ($dbflag) {

    my $quoted_last = $dbh->quote($srchterm.'%');

    $condition = "WHERE LastName LIKE $quoted_last";

  }

  $ldapfilter = '(sn='.$srchterm.'*)';

} else {

  if ($dbflag) {

   my $quoted_last = $dbh->quote($srchterm);

    $condition = "WHERE LastName = $quoted_last";

  }

  $ldapfilter = '(sn='.$srchterm.')';

}

}
} elsif ($srchby eq 'lastfirst') {
my ($srchlast,$srchfirst) = split(/,/,$srchterm);

$srchlast =~ s/\s+$//;

$srchfirst =~ s/^\s+//;

if (($srchlast =~ /[A-Za-z\-\.'\s]+/) && ($srchfirst =~ /[A-Za-z\-\.'\s]+/)) {

my ($quoted_first,$quoted_last);

if ($srchtype eq 'contains') {

  if ($dbflag) {

    $quoted_last = $dbh->quote('%'.$srchlast.'%');

    $quoted_first = $dbh->quote('%'.$srchfirst.'%');

    $condition = "WHERE ( LastName LIKE $quoted_last AND FirstName LIKE $quoted_first )";

  }

  $ldapfilter = '(&(sn='.$srchlast.'*)(givenName='.$srchfirst.'*))';

} else {

foreach my $table (@{$srchtables}) {
next if ($srchby && $condition eq ");

my $statement = "SELECT MSUNetID,Pid,FirstName,LastName,                  Person_Type FROM $table $condition";

my $sth = $dbh->prepare("$statement");

$sth->execute();

while ( my($uname,$pid,$first,$last,$type) = $sth->fetchrow_array ) {

  $pid=lc($pid);

  if (ref($instusers->{$uname}) eq 'HASH') {

    if (ref($instusers->{$uname}{'instst

  if ($dbflag) {

    $quoted_last = $dbh->quote($srchterm);

    $quoted_first = $dbh->quote($srchterm);

    $condition = "WHERE ( LastName = $quoted_last AND FirstName = $quoted_first )";

  }

  $ldapfilter = '(&(sn='.$srchlast.')(givenName='.$srchfirst.'))';

}

}
} elsif ($srchby eq 'id') {
if ($dbflag) {

  if ($srchterm =~ /^[AZ]\d{8}$/) {

  $condition = "WHERE Pid = '$srchterm'";
  }

}

}

if ($dbflag) {

foreach my $table (@{$srchtables}) {
next if ($srchby && $condition eq ");

my $statement = "SELECT MSUNetID,Pid,FirstName,LastName,Person_Type FROM $table $condition";

my $sth = $dbh->prepare("$statement");

$sth->execute();

while ( my($uname,$pid,$first,$last,$type) = $sth->fetchrow_array ) {

  $pid=lc($pid);

  if (ref($instusers->{$uname}) eq 'HASH') {

    if (ref($instusers->{$uname}{'inststatus'}) eq 'ARRAY') {

      if (!grep(/^$type$/,@{$instusers->{$uname}{'inststatus'}})) {

        push(@{$instusers->{$uname}{'inststatus'}},$type);

      }

    }

    if ($pid ne $instusers->{$uname}{'id'}) {

      if ($instusers->{$uname}{'id'} =~ /^A\d{8}$/) {

        if ($pid =~ /^A\d{8}$/) {

          if (ref($multipids{$uname}) eq 'ARRAY') {

            if (!grep(/^$pid$/,@{$multipids{$uname}})) {

              push(@{$multipids{$uname}},$pid);

            }

          } else {

            @{$multipids{$uname}}=($instusers->{$uname}{'id'},$pid);

          }

          $instusers->{$uname}{'id'} = $pid;

        }

      } elsif ($instusers->{$uname}{'id'} =~ /^Z\d{8}$/) {

        if ($pid =~ /^Z\d{8}$/) {

          if (ref($multipids{$uname}) eq 'ARRAY') {

            if (!grep(/^$pid$/,@{$multipids{$uname}})) {

              push(@{$multipids{$uname}},$pid);

            }

          } else {

            @{$multipids{$uname}}=($instusers->{$uname}{'id'},$pid);

          }

        } elsif ($pid =~ /^A\d{8}$/) {

          $instusers->{$uname}{'id'} = $pid;

        }

      }

    }

  } else {

    $instusers->{$uname} = {

      firstname => $first,

      lastname => $last,

      id => $pid,

      permanentemail => $uname.'@msu.edu',

      inststatus => [$type],

    };

  }

  if (defined($instids->{$pid})) {

    if (ref($instids->{$pid}) eq 'ARRAY') {

      if (!grep(/^$uname$/,@{$instids->{$pid}})) {

        push(@{$instids->{$pid}},$uname);

      }

    } elsif ($instids->{$pid} ne $uname) {

      @{$instids->{$pid}} = ($instids->{$pid},$uname);

    }

  } else {

    $instids->{$pid} = $uname;

  }

}

$outcome = 'ok';

}
}

if ($ldapfilter ne ") {

my $ldapres = &ldap_search($ldapfilter,$instusers,$types);

if (!$dbflag) {

$outcome = $ldapres;
}
}

return $outcome;

}
At MSU, a search of the LDAP directory is used to supplement SQL queries of Faculty, Staff and Student database tables, because there are no student/employee IDs available from MSU's LDAP service. The LDAP search is used to retrieve information about users who have MSUNetIDs (i.e., official usernames from MSU), but are not currently affiliated with any of the institutional user types, so are absent from the six SQL database tables.

sub ldap_search {
my ($ldapfilter,$instusers,$types) = @_;

my $outcome;

my $ldap = Net::LDAP->new( 'ldap.msu.edu' );

if ($ldap) {

$ldap->bind;

my $mesg = $ldap->search(

base => "dc=msu, dc=edu",

filter => $ldapfilter,

attrs => ['sn','givenName','title','uid','mail','employeeType'],

);

if ($mesg->code) {

$ldap->unbind;

return;

} else {
$outcome = 'ok';
}

foreach my $entry ($mesg->entries) {

my $uname = $entry->get_value('uid');

next if ($uname eq ");

my $first = $entry->get_value('givenName');

my $last = $entry->get_value('sn');

my $email = $entry->get_value('mail');

my $type;

if (($entry->get_value('employeeType') eq 'Faculty') || ($entry->get_value('employeeType') eq 'Staff')) {

$type = $entry->get_value('employeeType');
} elsif ($entry->get_value('title') eq 'Student') {
$type = $entry->get_value('title');
}

if (ref($types) eq 'ARRAY') {

  if (@{$types} > 0) {

  if (($type ne ") && !(grep(/^$type$/,@{$types})))

    next if (!grep(/^default$/,@{$types}));

  }

  next if (($type eq ") && (!grep(/^default$/,@{$types})));

}

}

if (ref($instusers->{$uname}) eq 'HASH') {

  if (ref($instusers->{$uname}{'inststatus'}) eq 'ARRAY') {

    if (!grep(/^$type$/,@{$instusers->{$uname}{'inststatus'}})) {

      push(@{$instusers->{$uname}{'inststatus'}},$type);

    }

  }

} else {

  $instusers->{$uname} = {

  firstname => $first,

  lastname => $last,

  id => ",

  permanentemail => $email,

  inststatus => [$type],

};

}

$ldap->unbind;

}

return $outcome;

}
allusers_info

Three arguments are required:

  1. $dom - domain

  2. $instusers - reference to hash which will contain hashes, where keys will be usernames and value will be a hash of user information.

    Keys in the inner hash will be some or all of: lastname, firstname, middlename, generation, id, inststatus - institutional status (e.g., faculty,staff,student)

    Values are all scalars except inststatus, which is an array.

  3. $instids - reference to hash which will contain ID numbers. keys will be unique IDs (student or faculty/staff ID).

    Values will be either: scalar (username) or an array if a single ID matches multiple usernames.

Returns 'ok' if no error occurred.

Side effects - populates the $instusers and $instids refs to hashes with information for all users from all available institutional datafeeds.

In the MSU case, six SQL database tables are queried via the query_user_tables() routine described above.

sub allusers_info {
my ($dom,$instusers,$instids) = @_;

my $outcome;

my ($dbh,$dbflag) = &connect_DB('HR');

if ($dbflag) {

my @srchtables = ('FACULTY_VU','STAFF_VU','STUDENT','AFFILIATE',                   'ASSISTANT','STUDENT_AFFILIATE');

&query_user_tables($dbflag,$dbh,\@srchtables,$instusers,$instids);

$outcome = 'ok';

&disconnect_DB($dbh);

}

return $outcome;

}