[prev in list] [next in list] [prev in thread] [next in thread]
List: lon-capa-cvs
Subject: [LON-CAPA-cvs] cvs: modules /msu localenroll.pm
From: raeburn via LON-CAPA-cvs <lon-capa-cvs () mail ! lon-capa ! org>
Date: 2022-01-28 23:29:43
Message-ID: cvsraeburn1643412583 () cvsserver
[Download RAW message or body]
This is a MIME encoded message
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.
["raeburn-20220128232943.txt" (text/plain)]
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,@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') {
_______________________________________________
LON-CAPA-cvs mailing list
LON-CAPA-cvs@mail.lon-capa.org
http://mail.lon-capa.org/mailman/listinfo/lon-capa-cvs
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic