get_userinfo
Two modes of operation:
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.
Values are all scalars except inststatus, which is an array.
values will be either: scalar (username) or an array if a single ID matches multiple usernames.
Allowed values include: 1. lastfirst, 2. last, 3. uname corresponding to searches by 1. lastname,firstname; 2. lastname; 3. username
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 {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.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';} elsif ($id ne ") {$srchterm = $uname;
$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}$/) {} elsif ($srchby eq 'lastname') {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.')';
}
if ($srchterm =~ /[A-Za-z\-\.'\s]+/) {} elsif ($srchby eq 'lastfirst') {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.')';
}
my ($srchlast,$srchfirst) = split(/,/,$srchterm);} elsif ($srchby eq 'id') {$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}) {if ($dbflag) {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
$quoted_last = $dbh->quote($srchterm);
$quoted_first = $dbh->quote($srchterm);
$condition = "WHERE ( LastName = $quoted_last AND FirstName = $quoted_first )";
}
$ldapfilter = '(&(sn='.$srchlast.')(givenName='.$srchfirst.'))';
}
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;
sub ldap_search {allusers_infomy ($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;} else {return;
$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;
Three arguments are required:
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.
Values will be either: scalar (username) or an array if a single ID matches multiple usernames.
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;