[LON-CAPA-cvs] cvs: modules /msu localenroll.pm
raeburn
raeburn at source.lon-capa.org
Fri Jan 28 18:29:43 EST 2022
raeburn Fri Jan 28 23:29:43 2022 EDT
Modified files:
/modules/msu localenroll.pm
Log:
- Integration with MSU campus information systems
- Institutional directory search and username checking for single user or
multiple users (for csv file upload) will query RO, CS, and HR after
LDAP, unless this an exact search by username, and match found in LDAP.
-------------- next part --------------
Index: modules/msu/localenroll.pm
diff -u modules/msu/localenroll.pm:1.88 modules/msu/localenroll.pm:1.89
--- modules/msu/localenroll.pm:1.88 Fri Jan 14 18:21:17 2022
+++ modules/msu/localenroll.pm Fri Jan 28 23:29:43 2022
@@ -1,6 +1,6 @@
# functions to glue school database system into Lon-CAPA for
# automated enrollment
-# $Id: localenroll.pm,v 1.88 2022/01/14 18:21:17 raeburn Exp $
+# $Id: localenroll.pm,v 1.89 2022/01/28 23:29:43 raeburn Exp $
#
# Copyright Michigan State University Board of Trustees
#
@@ -1385,11 +1385,30 @@
if ($caller eq 'id') {
$outcome = &query_by_id($instusers,$instids,'',$userhash,$types);
} else {
+ my $srchby = 'uname';
+ my $stoponmatch = 1;
+ my (%dbh,%dbflag,%srchtables,%affiliation_by_table);
+ my @connected = &userinfo_searchtables(\%dbh,\%dbflag,\%srchtables,
+ \%affiliation_by_table,$types);
foreach my $key (keys(%{$userhash})) {
- $outcome = &query_ldap($instusers,'','uname',$key,'',$types);
+ $outcome = &query_ldap($instusers,'',$srchby,$key,'',$types);
+ if (ref($instusers->{$key}) eq 'HASH') {
+ my %userinfo;
+ $userinfo{$key} = $instusers->{$key};
+ &get_pids(\%userinfo,$instids,$types);
+ } elsif (@connected) {
+ my (%ldap_users,%counts,%condition);
+ &get_query_condition(\%dbh,\%dbflag,$srchby,$key,undef,\%condition);
+ &query_user_tables(\%dbflag,\%dbh,\%srchtables,\%affiliation_by_table,
+ $instusers,$instids,undef,\%ldap_users,\%counts,
+ \%condition,$stoponmatch);
+ }
}
- if (keys(%{$instusers})) {
- &get_pids($instusers,$instids,$types);
+ $outcome = 'ok';
+ foreach my $conn (@connected) {
+ if ($dbflag{$conn}) {
+ &disconnect_DB($dbh{$conn});
+ }
}
}
} else {
@@ -1400,29 +1419,236 @@
sub get_userinfo {
my ($dom,$uname,$id,$instusers,$instids,$types,$srchby,$srchterm,$srchtype) = @_;
- my $outcome;
+ my ($outcome,$stoponmatch);
if ($srchby eq '' && $srchterm eq '') {
if ($uname ne '') {
$srchby = 'uname';
$srchterm = $uname;
+ $stoponmatch = 1;
} elsif ($id ne '') {
$srchby = 'id';
$srchterm = $id;
}
+ } elsif (($srchby eq 'uname') && ($srchtype ne 'begins') && ($srchtype ne 'contains')) {
+ $stoponmatch = 1;
}
if ($srchterm ne '') {
if ($srchby eq 'id') {
$outcome = &query_by_id($instusers,$instids,$srchterm,'',$types);
} else {
$outcome = &query_ldap($instusers,'',$srchby,$srchterm,$srchtype,$types);
+ my %ldap_users;
if (keys(%{$instusers})) {
- &get_pids($instusers,$instids,$types);
+ if (($srchby eq 'uname') && ($srchtype ne 'begins') && ($srchtype ne 'contains')) {
+ &get_pids($instusers,$instids,$types);
+ return $outcome;
+ } else {
+ %ldap_users = %{$instusers};
+ }
+ }
+ my (%dbh,%dbflag,%srchtables,%affiliation_by_table);
+ my @connected = &userinfo_searchtables(\%dbh,\%dbflag,\%srchtables,
+ \%affiliation_by_table);
+ if (@connected) {
+ my (%counts,%condition);
+ &get_query_condition(\%dbh,\%dbflag,$srchby,$srchterm,$srchtype,\%condition);
+ $outcome = &query_user_tables(\%dbflag,\%dbh,\%srchtables,\%affiliation_by_table,
+ $instusers,$instids,undef,\%ldap_users,\%counts,
+ \%condition,$stoponmatch);
+ foreach my $conn (@connected) {
+ if ($dbflag{$conn}) {
+ &disconnect_DB($dbh{$conn});
+ }
+ }
}
}
}
return $outcome;
}
+sub get_query_condition {
+ my ($dbh,$dbflag,$srchby,$srchterm,$srchtype,$condition) = @_;
+ return unless ((ref($dbh) eq 'HASH') &&
+ (ref($dbflag) eq 'HASH') &&
+ (ref($condition) eq 'HASH'));
+ $condition->{hr} = '';
+ $condition->{ro} = {};
+ $condition->{cs} = {};
+ if ($srchby eq 'uname') {
+ if ($srchterm =~ /^\w{2,8}$/) {
+ $condition->{'hr'} = "MSUNetID";
+ $condition->{'ro'}{'Faculty'} = "MSUNetID";
+ $condition->{'ro'}{'Student'} = "Pilot_Id";
+ $condition->{'cs'}{'Faculty'} = "CAMPUS_ID";
+ if ($srchtype eq 'contains') {
+ foreach my $key (keys(%{$condition})) {
+ if (ref($condition->{$key}) eq 'HASH') {
+ foreach my $inner (keys(%{$condition->{$key}})) {
+ $condition->{$key}{$inner} .= " LIKE '%$srchterm%'";
+ }
+ } else {
+ $condition->{$key} .= " LIKE '%$srchterm%'";
+ }
+ }
+ } elsif ($srchtype eq 'begins') {
+ foreach my $key (keys(%{$condition})) {
+ if (ref($condition->{$key}) eq 'HASH') {
+ foreach my $inner (keys(%{$condition->{$key}})) {
+ $condition->{$key}{$inner} .= " LIKE '$srchterm%'";
+ }
+ } else {
+ $condition->{$key} .= " LIKE '$srchterm%'";
+ }
+ }
+ } else {
+ foreach my $key (keys(%{$condition})) {
+ if (ref($condition->{$key}) eq 'HASH') {
+ foreach my $inner (keys(%{$condition->{$key}})) {
+ $condition->{$key}{$inner} .= " = '$srchterm'";
+ }
+ } else {
+ $condition->{$key} .= " = '$srchterm'";
+ }
+ }
+ }
+ }
+ } elsif ($srchby eq 'lastname') {
+ if ($srchterm =~ /[A-Za-z\-\.'\s]+/) {
+ $condition->{'hr'} = "LastName";
+ $condition->{'ro'}{'Faculty'} = "Name";
+ $condition->{'ro'}{'Student'} = "Student_Name";
+ $condition->{'cs'}{'Faculty'} = "INSTR_UNIV_NAME";
+ if ($srchtype eq 'contains') {
+ foreach my $key (keys(%{$condition})) {
+ next if ($key eq 'hr');
+ if ($dbflag->{$key}) {
+ my $quoted_last = $dbh->{$key}->quote('%'.$srchterm.'%,%');
+ if (ref($condition->{$key}) eq 'HASH') {
+ foreach my $inner (keys(%{$condition->{$key}})) {
+ $condition->{$key}{$inner} .= " LIKE $quoted_last";
+ }
+ } else {
+ $condition->{$key} .= " LIKE $quoted_last";
+ }
+ }
+ }
+ if ($dbflag->{'hr'}) {
+ my $quoted_last = $dbh->{'hr'}->quote('%'.$srchterm.'%');
+ $condition->{'hr'} .= " LIKE $quoted_last";
+ }
+ } elsif ($srchtype eq 'begins') {
+ foreach my $key (keys(%{$condition})) {
+ next if ($key eq 'hr');
+ if ($dbflag->{$key}) {
+ my $quoted_last = $dbh->{$key}->quote($srchterm.'%,%');
+ if (ref($condition->{$key}) eq 'HASH') {
+ foreach my $inner (keys(%{$condition->{$key}})) {
+ $condition->{$key}{$inner} .= " LIKE $quoted_last";
+ }
+ } else {
+ $condition->{$key} .= " LIKE $quoted_last";
+ }
+ }
+ }
+ if ($dbflag->{'hr'}) {
+ my $quoted_last = $dbh->{'hr'}->quote($srchterm.'%');
+ $condition->{'hr'} .= " LIKE $quoted_last";
+ }
+ } else {
+ foreach my $key (keys(%{$condition})) {
+ next if ($key eq 'hr');
+ if ($dbflag->{$key}) {
+ my $quoted_last = $dbh->{$key}->quote($srchterm.',%');
+ if (ref($condition->{$key}) eq 'HASH') {
+ foreach my $inner (keys(%{$condition->{$key}})) {
+ $condition->{$key}{$inner} .= " LIKE $quoted_last";
+ }
+ } else {
+ $condition->{$key} .= " LIKE $quoted_last";
+ }
+ }
+ }
+ if ($dbflag->{'hr'}) {
+ my $quoted_last = $dbh->{'hr'}->quote($srchterm);
+ $condition->{'hr'} = "LastName = $quoted_last";
+ }
+ }
+ }
+ } 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]+/)) {
+ $condition->{'hr'} = "LastName";
+ $condition->{'ro'}{'Faculty'} = "Name";
+ $condition->{'ro'}{'Student'} = "Student_Name";
+ $condition->{'cs'}{'Faculty'} = "INSTR_UNIV_NAME";
+ my ($quoted_first,$quoted_last,$quoted_name);
+ if (($srchtype eq 'contains') || ($srchtype eq 'begins')) {
+ if ($dbflag->{'hr'}) {
+ my ($quoted_first,$quoted_last);
+ if ($srchtype eq 'contains') {
+ $quoted_last = $dbh->{'hr'}->quote('%'.$srchlast.'%');
+ $quoted_first = $dbh->{'hr'}->quote('%'.$srchfirst.'%');
+ } elsif ($srchtype eq 'begins') {
+ $quoted_last = $dbh->{'hr'}->quote($srchlast.'%');
+ $quoted_first = $dbh->{'hr'}->quote($srchfirst.'%');
+ }
+ $condition->{'hr'} = "(LastName LIKE $quoted_last AND ".
+ "FirstName LIKE $quoted_first)";
+ }
+ foreach my $key (keys(%{$condition})) {
+ next if ($key eq 'hr');
+ if ($dbflag->{$key}) {
+ my $quoted_name;
+ if ($srchtype eq 'contains') {
+ $quoted_name = $dbh->{$key}->quote('%'.$srchlast.'%,%'.$srchfirst.'%');
+ } elsif ($srchtype eq 'begins') {
+ $quoted_name = $dbh->{$key}->quote($srchlast.'%,'.$srchfirst.'%');
+ }
+ if (ref($condition->{$key}) eq 'HASH') {
+ foreach my $inner (keys(%{$condition->{$key}})) {
+ $condition->{$key}{$inner} .= " LIKE $quoted_name";
+ }
+ } else {
+ $condition->{$key} .= " LIKE $quoted_name";
+ }
+ }
+ }
+ } else {
+ if ($dbflag->{'hr'}) {
+ my $quoted_last = $dbh->{'hr'}->quote($srchterm);
+ my $quoted_first = $dbh->{'hr'}->quote($srchterm);
+ $condition->{'hr'} = "(LastName = $quoted_last AND ".
+ "FirstName = $quoted_first)";
+ }
+ foreach my $key (keys(%{$condition})) {
+ next if ($key eq 'hr');
+ if ($dbflag->{$key}) {
+ my $quoted_name = $dbh->{$key}->quote($srchlast.','.$srchfirst.' %');
+ if (ref($condition->{$key}) eq 'HASH') {
+ foreach my $inner (keys(%{$condition->{$key}})) {
+ $condition->{$key}{$inner} .= " LIKE $quoted_name";
+ }
+ } else {
+ $condition->{$key} .= " LIKE $quoted_name";
+ }
+ }
+ }
+ }
+ }
+ } elsif ($srchby eq 'email') {
+ if ($srchterm =~ /^(\w{2,8})\@msu\.edu$/) {
+ $condition->{'hr'} = "MSUNetID = '$1'";
+ $condition->{'ro'}{'Faculty'} = "MSUNetID = '$1'";
+ $condition->{'ro'}{'Student'} = "Pilot_Id = '$1'";
+ $condition->{'cs'}{'Faculty'} = "CAMPUS_ID = '$1'";
+ }
+ }
+ return;
+}
+
sub query_ldap {
my ($instusers,$lc_users,$srchby,$srchterm,$srchtype,$types,$counts) = @_;
my $outcome;
@@ -1687,15 +1913,18 @@
sub query_user_tables {
my ($dbflagref,$dbhref,$srchtablesref,$affiliation_by_tableref,$instusers,
- $instids,$lcusersref,$ldap_users,$counts) = @_;
+ $instids,$lcusersref,$ldap_users,$counts,$conditionref,$stoponmatch) = @_;
return unless ((ref($dbflagref) eq 'HASH') && (ref($dbhref) eq 'HASH') &&
(ref($srchtablesref) eq 'HASH') &&(ref($instusers) eq 'HASH') &&
(ref($instids) eq 'HASH') && (ref($ldap_users) eq 'HASH'));
- my ($outcome,$total,%users,%multipids,%ldap_checked);
+ my ($outcome,$total,%users,%multipids,%ldap_checked,%condition);
if (ref($lcusersref) eq 'HASH') {
%users = %{$lcusersref};
$total = scalar(keys(%users));
}
+ if (ref($conditionref) eq 'HASH') {
+ %condition = %{$conditionref};
+ }
return if (($total ne '') && ($total == 0));
if (keys(%{$dbflagref})) {
my %table_to_key;
@@ -1703,22 +1932,42 @@
%table_to_key = %{$affiliation_by_tableref};
}
my @order = ('ro','cs','hr');
+ my $matched = 0;
foreach my $key (@order) {
+ last if ($stoponmatch && $matched);
if ($dbflagref->{$key}) {
foreach my $table (@{$srchtablesref->{$key}}) {
+ last if ($stoponmatch && $matched);
my $statement;
if ($key eq 'hr') {
$statement = "SELECT MSUNetID,Pid,FirstName,LastName,Person_Type FROM $table";
+ if ($condition{$key}) {
+ $statement .= ' WHERE '.$condition{$key};
+ }
} elsif ($key eq 'ro') {
if ($table eq 'RO_CLIFMS') {
- $statement = "SELECT DISTINCT MSUNetId,Emp_Id,PID,Name,Record_Type,Term_Seq_Id FROM $table WHERE (Record_Type = '1' OR Record_Type = 'PI') ORDER BY MSUNetId,Term_Seq_Id";
+ $statement = "SELECT DISTINCT MSUNetId,Emp_Id,PID,Name,Record_Type,Term_Seq_Id FROM $table;
+ if ($condition{$key}{$table_to_key{$table}}) {
+ $statement .= " WHERE (($condition{$key}{$table_to_key{$table}}) AND (Record_Type = '1' OR Record_Type = 'PI'))";
+ } else {
+ $statement .= " WHERE (Record_Type = '1' OR Record_Type = 'PI')";
+ }
+ $statement .= " ORDER BY MSUNetId,Term_Seq_Id";
} else {
- $statement = "SELECT DISTINCT Pilot_Id,Pid,Student_Preferred_First_Name,Student_Name,Term_Seq_Id FROM $table ORDER BY Pilot_Id,Term_Seq_Id Desc";
+ $statement = "SELECT DISTINCT Pilot_Id,Pid,Student_Preferred_First_Name,Student_Name,Term_Seq_Id FROM $table";
+ if ($condition{$key}{$table_to_key{$table}}) {
+ $statement .= " WHERE $condition{$key}{$table_to_key{$table}}";
+ }
+ $statement .= " ORDER BY Pilot_Id,Term_Seq_Id Desc";
}
} elsif ($key eq 'cs') {
$statement = "SELECT DISTINCT CAMPUS_ID,EMPLID,INSTR_UNIV_NAME,INSTR_ROLE FROM $table";
+ if ($condition{$key}{$table_to_key{$table}}) {
+ $statement .= " WHERE $condition{$key}{$table_to_key{$table}}";
+ }
}
if (ref($dbhref->{$key})) {
+ next if ($statement eq '');
my $sth = $dbhref->{$key}->prepare("$statement");
$sth->execute();
while ( my ($uname,$pid, at rest) = $sth->fetchrow_array ) {
@@ -1872,6 +2121,7 @@
if ($generation ne '') {
$instusers->{$uname}{generation} = $generation;
}
+ $matched = 1;
}
if (defined($instids->{$pid})) {
if (ref($instids->{$pid}) eq 'ARRAY') {
More information about the LON-CAPA-cvs
mailing list