[LON-CAPA-cvs] cvs: modules /msu localenroll.pm

raeburn raeburn at source.lon-capa.org
Thu Feb 11 13:29:10 EST 2021


raeburn		Thu Feb 11 18:29:10 2021 EDT

  Modified files:              
    /modules/msu	localenroll.pm 
  Log:
  - Add standard subroutine stubs for &crsreq_updates() and &export_grades()
  - Replace use of SIS with SISCS for Fall 2021 and beyond:
    Queries to SISCRSS go to SISCS.R_CLASSSCHEDULE_RV, and queries to SISCRSD
    go to SISCS.R_COURSECATALOG_RV or SISCS.R_CLASSSCHEDULE_RV      
  
  
-------------- next part --------------
Index: modules/msu/localenroll.pm
diff -u modules/msu/localenroll.pm:1.68 modules/msu/localenroll.pm:1.69
--- modules/msu/localenroll.pm:1.68	Thu Feb  1 15:36:50 2018
+++ modules/msu/localenroll.pm	Thu Feb 11 18:29:10 2021
@@ -1,6 +1,6 @@
 # functions to glue school database system into Lon-CAPA for
 # automated enrollment
-# $Id: localenroll.pm,v 1.68 2018/02/01 15:36:50 raeburn Exp $
+# $Id: localenroll.pm,v 1.69 2021/02/11 18:29:10 raeburn Exp $
 #
 # Copyright Michigan State University Board of Trustees
 #
@@ -63,7 +63,6 @@
 
 sub connect_DB {
     my ($type,$keydir) = @_;
-    $ENV{SYBASE} = '/usr/local/freetds';
     my $DB_PATH;
     my $DB_USER = '';
     my $DB_PASSWD = '';
@@ -71,10 +70,18 @@
         my $configvars = &LONCAPA::Configuration::read_conf('loncapa.conf');
         $keydir = $$configvars{'lonDaemons'};
     }
-    if (open (my $fh, "<$keydir/autoenroll.dat") ) {
-        ($DB_USER,$DB_PASSWD) = split/:/,<$fh>;
-        chomp($DB_PASSWD);
-        close($fh);
+    my $datfile = 'autoenroll.dat';
+    $ENV{SYBASE} = '/usr/local/freetds';
+    if ($type eq 'SISCS') {
+        $datfile = 'autoenrollnew.dat';
+        delete($ENV{SYBASE});
+    }
+    if (-e "$keydir/$datfile") {
+        if (open(my $fh,'<',"$keydir/$datfile")) {
+            ($DB_USER,$DB_PASSWD) = split(/:/,<$fh>);
+            chomp($DB_PASSWD);
+            close($fh);
+        }
     }
 
     if ($type eq 'RO') {
@@ -83,18 +90,24 @@
         $DB_PATH = "dbi:Sybase:server=msudata;database=SISInfo";
     } elsif ($type eq 'HR') {
         $DB_PATH = "dbi:Sybase:server=msudata;database=HR";
+    } elsif ($type eq 'SISCS') {
+        $DB_PATH = 'dbi:Oracle://ebsprd390d.ais.msu.edu:1521/MSUEDW.EBSP.MSU.EDU';
     }
 
     my $dbh;
     my $dbflag = 0;
-    eval { $dbh = DBI->connect($DB_PATH,$DB_USER,$DB_PASSWD); };
-    if ($@) {
-        $dbflag = 0;
-    } else {
-        if (defined($dbh)) {
-            $dbh->do('SET ANSI_NULLS ON');
-            $dbh->do('SET ANSI_WARNINGS ON');
-            $dbflag = 1;
+    if ($DB_USER ne '' && $DB_PASSWD ne '') {
+        eval { $dbh = DBI->connect($DB_PATH,$DB_USER,$DB_PASSWD); };
+        if ($@) {
+            $dbflag = 0;
+        } else {
+            if (defined($dbh)) {
+                unless ($type eq 'SISCS') {
+                    $dbh->do('SET ANSI_NULLS ON');
+                    $dbh->do('SET ANSI_WARNINGS ON');
+                }
+                $dbflag = 1;
+            }
         }
     }
     return ($dbh,$dbflag);
@@ -196,16 +209,26 @@
         my $term = $1;
         my $subj = $2;
         my $crse = $3;
+        my ($sem,$yr) = ($term =~ /^([suf]s)(\d{2})$/);
         $subj =~tr/a-z/A-Z/;
         $crse =~tr/a-z/A-Z/;
         $term =~tr/a-z/A-Z/;
-        my ($dbh3,$dbflag) = &connect_DB('SIS');
+        my $database = 'SIS';
+        if (($yr > 21) || (($yr == 21) && ($sem eq 'fs'))) {
+            $database = 'SISCS';
+        }
+        my ($dbh3,$dbflag) = &connect_DB($database);
         if ($dbflag) {
             eval {
                 my $quotedsubj = $dbh3->quote($subj);
                 my $quotedcrse = $dbh3->quote($crse);
                 my $quotedterm = $dbh3->quote($term);
-                my $sth = $dbh3->prepare( "SELECT Sctn_Code FROM SISCRSS WHERE Subj_Code = $quotedsubj AND Crse_Code= $quotedcrse AND Sctn_Code != '998' AND Sctn_Term_Code =$quotedterm ");
+                my $sth;
+                if ($database eq 'SISCS') {
+                    $sth = $dbh3->prepare( "SELECT CLASS_SECTION FROM SISCS.R_CLASSSCHEDULE_RV WHERE (SUBJECT = $quotedsubj AND CRSE_CODE = $quotedcrse AND TERM_DESCRSHORT = $quotedterm) ORDER BY CLASS_SECTION ");
+                } else {
+                    $sth = $dbh3->prepare( "SELECT Sctn_Code FROM SISCRSS WHERE Subj_Code = $quotedsubj AND Crse_Code= $quotedcrse AND Sctn_Code != '998' AND Sctn_Term_Code =$quotedterm ");
+                }
                 $sth->execute();
                 while ( my @row = $sth->fetchrow_array() ) {
                     if (!grep/^99/, at row) {
@@ -410,28 +433,38 @@
         my $subj = $2;
         my $crse = $3;
         my $sec = $4;
+        my ($sem,$yr) = ($term =~ /^([suf]s)(\d{2})$/);
         $subj =~tr/a-z/A-Z/;
         $crse =~tr/a-z/A-Z/;
         $term =~tr/a-z/A-Z/;
-        my ($dbh3,$dbflag) = &connect_DB('SIS');
+        my $database = 'SIS';
+        if (($yr > 21) || (($yr == 21) && ($sem eq 'fs'))) {
+            $database = 'SISCS';
+        }
+        my ($dbh3,$dbflag) = &connect_DB($database);
         if ($dbflag) {
             eval {
                 my $quotedsubj = $dbh3->quote($subj);
                 my $quotedcrse = $dbh3->quote($crse);
                 my $quotedterm = $dbh3->quote($term);
                 my $quotedsec = $dbh3->quote($sec);
-                my $count = $dbh3->selectrow_array( "SELECT count(*) FROM SISCRSS WHERE Subj_Code = $quotedsubj AND Crse_Code= $quotedcrse AND Sctn_Code= $quotedsec AND Sctn_Term_Code =$quotedterm ");
+                my $count;
+                if ($database eq 'SISCS') {
+                    $count = $dbh3->selectrow_array( "SELECT count(*) FROM SISCS.R_CLASSSCHEDULE_RV WHERE SUBJECT = $quotedsubj AND CRSE_CODE = $quotedcrse AND CLASS_SECTION = $quotedsec AND TERM_DESCRSHORT = $quotedterm ");
+                } else {
+                    $count = $dbh3->selectrow_array( "SELECT count(*) FROM SISCRSS WHERE Subj_Code = $quotedsubj AND Crse_Code= $quotedcrse AND Sctn_Code= $quotedsec AND Sctn_Term_Code =$quotedterm ");
+                }
                 if ($count > 0) {
                     $outcome = 'ok';
                 } else {
-                    $outcome = "There was no match in the MSU SIS database to the combination of semester ($term), subject ($subj), course ($crse) and section ($sec) you entered."; 
+                    $outcome = "There was no match in the MSU $database database to the combination of semester ($term), subject ($subj), course ($crse) and section ($sec) you entered."; 
                 }
             };
             if ($@) {
-                $outcome = "There was a problem connecting to the SIS database, so the course section could not be validated as a valid course section.";
+                $outcome = "There was a problem connecting to the $database database, so the course section could not be validated as a valid course section.";
             } 
         } else {
-            $outcome = "There was a problem connecting to the SIS database, so the course section could not be validated as a valid course section.";
+            $outcome = "There was a problem connecting to the $database database, so the course section could not be validated as a valid course section.";
         }
     } else {
         $outcome = "The course section number you entered is not a valid MSU course section";
@@ -451,8 +484,8 @@
                   );
     if ($instcode =~ m/^([suf]s)(\d{2})(\w{2,4})(\d{3,4}\w?)$/) {
         my $sem = $1;
-        my $year = $2;
-        my $term = $sem.$year;
+        my $yr = $2;
+        my $term = $sem.$yr;
         my $subj = $3;
         my $crse = $4;
         $subj =~tr/a-z/A-Z/;
@@ -479,7 +512,7 @@
                         unless ($outcome eq 'valid') {
 # Check if instructor is a Dept. Administrator
                             eval {
-                                my $seqid = &semyr_to_seqid($term,$sem,$year);
+                                my $seqid = &semyr_to_seqid($term,$sem,$yr);
                                 my $admincount = 0;
                                 my $adminsth = $dbh->prepare("SELECT Start_Term_Seq_Id,End_Term_Seq_Id FROM LONCAPA_DeptAdministrators WHERE subj_code = '$subj' AND crse_code = '$crse' AND PilotID = '$ownername'");
                                 $adminsth->execute();
@@ -511,48 +544,93 @@
                 $outcome = "The course owner is not from the msu domain so can not be validated.";
             }
         } else {
-            $database = 'SIS';  
-            ($dbh,$dbflag) = &connect_DB('SIS');
+            $database = 'SIS';
+            if (($yr > 21) || (($yr == 21) && ($sem eq 'fs'))) {
+                $database = 'SISCS';
+            }
+            ($dbh,$dbflag) = &connect_DB($database);
             if ($dbflag) {
-                my ($quotedsubj,$quotedcrse,$quotedterm);
                 eval {
                     my $quotedsubj = $dbh->quote($subj);
                     my $quotedcrse = $dbh->quote($crse);
                     my $quotedterm = $dbh->quote($term);
-                    my $count = $dbh->selectrow_array( "SELECT count(*) FROM SISCRSS WHERE Subj_Code = $quotedsubj AND Crse_Code= $quotedcrse AND Sctn_Term_Code =$quotedterm ");
+                    my $count;
+                    if ($database eq 'SISCS') {
+                        $count = $dbh->selectrow_array( "SELECT count(*) FROM SISCS.R_CLASSSCHEDULE_RV WHERE SUBJECT = $quotedsubj AND CRSE_CODE = $quotedcrse AND TERM_DESCRSHORT = $quotedterm ");
+                    } else {
+                        $count = $dbh->selectrow_array( "SELECT count(*) FROM SISCRSS WHERE Subj_Code = $quotedsubj AND Crse_Code= $quotedcrse AND Sctn_Term_Code =$quotedterm ");
+                    }
                     if ($count > 0) {
                         $outcome = 'valid';
                         $description = $subj.$crse;
-                        $year =~ s/^0//;
-                        my $longyr = 2000 + int($year);
-                        if (defined($longsem{$sem}) && ($year)) {
+                        $yr =~ s/^0//;
+                        my $longyr = 2000 + int($yr);
+                        if (defined($longsem{$sem}) && ($yr)) {
                             $description .= ', '.$longsem{$sem}.' '.$longyr;
                         }
-                        eval {
-                            my $title = $dbh->selectrow_array( "SELECT DISTINCT(Crse_Title) FROM SISCRSD WHERE (Subj_Code = $quotedsubj AND Crse_Code= $quotedcrse)");
-                            if ($title ne '') {
-                                $description .= ' - '.$title;
+                        my $title;
+                        if ($database eq 'SISCS') {
+                            my $crsdate;
+                            if ($sem eq 'fs') {
+                                $crsdate = $longyr.'-10-01';
+                            } elsif ($sem eq 'ss') {
+                                $crsdate = $longyr.'-02-01';
+                            } elsif ($sem eq 'us') {
+                                $crsdate = $longyr.'-06-01';
                             }
-                        };
+                            eval {
+                                $dbh->do("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'");
+                                $title = $dbh->selectrow_array("SELECT DISTINCT(COURSE_TITLE_LONG) FROM SISCS.R_COURSECATALOG_RV WHERE SUBJECT = $quotedsubj AND CRSE_CODE = $quotedcrse AND CRSE_EFFDT < '$crsdate' AND CRSE_EFFDT_END > '$crsdate'");
+                            };
+                        } else {
+                            eval {
+                                $title = $dbh->selectrow_array( "SELECT DISTINCT(Crse_Title) FROM SISCRSD WHERE (Subj_Code = $quotedsubj AND Crse_Code= $quotedcrse)");
+                            };
+                        }
+                        if ($title ne '') {
+                            $description .= ' - '.$title;
+                        }
                     } else {
                         $outcome = "There was no match in the MSU $database database to the combination of semester ($term), subject ($subj), course number ($crse) you entered.";
                     }
                 };
                 if ($@) {
-                    $outcome = "There was a problem connecting to the SIS database, so the course number could not be validated.";
+                    $outcome = "There was a problem connecting to the $database database, so the course number could not be validated.";
                 }
                 if ($outcome eq 'valid') {
-                    my $seqid = &semyr_to_seqid($term,$sem,$year);
                     eval {
-                        my $credsth = $dbh->prepare("SELECT Start_Term_Seq_Id,End_Term_Seq_Id,Credits FROM SISCRSD WHERE Subj_Code = '$subj' AND Crse_Code = '$crse'");
+                        my $quotedsubj = $dbh->quote($subj);
+                        my $quotedcrse = $dbh->quote($crse);
+                        my $quotedterm = $dbh->quote($term);
+                        my $credsth;
+                        if ($database eq 'SISCS') {
+                            $credsth = $dbh->prepare("SELECT UNITS_MAXIMUM FROM SISCS.R_CLASSSCHEDULE_RV WHERE SUBJECT = $quotedsubj AND CRSE_CODE = $quotedcrse AND TERM_DESCRSHORT = $quotedterm");
+                        } else {
+                            $credsth = $dbh->prepare("SELECT Start_Term_Seq_Id,End_Term_Seq_Id,Credits FROM SISCRSD WHERE Subj_Code = $quotedsubj AND Crse_Code = $quotedcrse");
+                        }
                         $credsth->execute();
-                        while (my($start_seqid,$end_seqid,$creds) = $credsth->fetchrow_array) {
-                            $start_seqid =~ s/^0//;
-                            $end_seqid =~ s/^0//;
-                            if (($seqid >= $start_seqid) &&
-                                ($seqid <= $end_seqid)) {
-                                if ($creds > $credits) {
-                                    $credits = $creds;
+                        if ($database eq 'SISCS') {
+                            while (my($creds) = $credsth->fetchrow_array) {
+                                $creds =~ s/\s+//g;
+                                if ($creds =~ /^\d+\.?\d*$/) {
+                                    if ($creds > $credits) {
+                                        $credits = $creds;
+                                    }
+                                }
+                            }
+                        } else {
+                            my $seqid = &semyr_to_seqid($term,$sem,$yr);   
+                            while (my($start_seqid,$end_seqid,$creds) = $credsth->fetchrow_array) {
+                                $start_seqid =~ s/^0//;
+                                $end_seqid =~ s/^0//;
+                                if (($seqid >= $start_seqid) &&
+                                    ($seqid <= $end_seqid)) {
+                                    $creds =~ s/\s+//g;
+                                    if ($creds =~ /^\d+\.?\d*$/) {
+                                        if ($creds > $credits) {
+                                            $credits = $creds;
+                                        }
+                                    }
                                 }
                             }
                         }
@@ -575,6 +653,19 @@
     return ($outcome,$description,$credits);
 }
 
+sub validate_crsreq {
+    my ($dom,$owner,$crstype,$inststatuslist,$instcode,$instseclist) = @_;
+    my ($outcome,$message);
+    my ($result,$description) = &validate_instcode($dom,$instcode,$owner);
+    if ($result eq 'valid') {
+        $outcome = 'process';
+    } else {
+        $outcome = 'pending';
+        $message = $description;
+    }
+    return $outcome.':'.$message;
+}
+
 sub crsreq_checks {
     my ($dom,$reqtypes,$validations) = @_;
     if ((ref($reqtypes) eq 'ARRAY') && (ref($validations) eq 'HASH')) {
@@ -594,17 +685,72 @@
     return 'ok';
 }
 
-sub validate_crsreq {
-    my ($dom,$owner,$crstype,$inststatuslist,$instcode,$instseclist) = @_;
-    my ($outcome,$message);
-    my ($result,$description) = &validate_instcode($dom,$instcode,$owner);
-    if ($result eq 'valid') {
-        $outcome = 'process';
+sub crsreq_updates {
+    my ($cdom,$cnum,$crstype,$action,$ownername,$ownerdomain,$fullname,$title,
+        $code,$accessstart,$accessend,$incoming,$outgoing) = @_;
+    unless (ref($outgoing) eq 'HASH') {
+        return 'fail';
+    }
+    my %extrainfo;
+    if (ref($incoming) eq 'HASH') {
+        %extrainfo = %{$incoming};
+    }
+    if ($action eq 'review') {
+        $outgoing->{'reviewweb'} = '';
+    } elsif ($action eq 'prevalidate') {
+        $outgoing->{'formitems'} = {}; # key=>value, where key is form element name
+                                       #             and value is multiple, if there
+                                       #             are multiple form elements with
+                                       #             the same name.
+    } elsif ($action eq 'process') {
+        $outgoing->{'formitems'} = {}; # key=>value, where key is form element name
+                                       #             and value is multiple, if there
+                                       #             are multiple form elements with
+                                       #             the same name.
+    } elsif ($action eq 'created') {
+        $outgoing->{'createdweb'} = '';
+        $outgoing->{'createdmsg'} = [{
+                                     mt => '',
+                                     args => [],
+                                    }];
+        $outgoing->{'createdactions'} = {
+                                            environment => {},
+                                        };
+                                        # environment can contain key=>value for
+                                        # items to set in the course environment.
+                                        # These would be items which are NOT included
+                                        # in the items set via options in the course
+                                        # request form. Currently self-enrollment
+                                        # settings are the only ones allowed, i.e.,
+                                        # internal.selfenroll_types internal.selfenroll_registered
+                                        # internal.selfenroll_section internal.selfenroll_start_access
+                                        # internal.selfenroll_end_access internal.selfenroll_limit
+                                        # internal.selfenroll_cap internal.selfenroll_approval
+                                        # internal.selfenroll_notifylist
+    } elsif ($action eq 'queued') {
+        $outgoing->{'queuedmsg'} = [{
+                                     mt   => '',
+                                     args => [],
+                                    }];
+        $outgoing->{'queuedweb'} = '';
+    }
+    return 'ok'
+}
+
+sub export_grades {
+    my ($cnum,$cdom,$hashref,$dataref,$outgoing) = @_;
+    my %info;
+    if (ref($hashref) eq 'HASH') {
+        %info = %{$hashref};
+    }
+    if ((ref($dataref) eq 'HASH') && (ref($outgoing) eq 'HASH')) {
+        foreach my $key (keys(%{$dataref})) {
+            $outgoing->{$key} = 1;
+        }
+        return 'ok';
     } else {
-        $outcome = 'pending';
-        $message = $description;
+        return 'error';
     }
-    return $outcome.':'.$message;
 }
 
 sub check_instclasses {
@@ -677,11 +823,21 @@
 
 sub get_all_depts {
     my ($dom) = @_;
-    my ($dbh,$dbflag) = &connect_DB('SIS');
+    my $now = time;
+    my $database = 'SIS';
+    if ($now > 1627776000) {
+        $database = 'SISCS';
+    }
+    my ($dbh,$dbflag) = &connect_DB($database);
     my (@depts,%deptnames); 
     if ($dbflag) {
         eval {
-            my $sth = $dbh->prepare("SELECT DISTINCT(Subj_Code) FROM SISCRSS");
+            my $sth;
+            if ($database eq 'SIS') {
+                $sth = $dbh->prepare("SELECT DISTINCT(Subj_Code) FROM SISCRSS");
+            } else {
+                $sth = $dbh->prepare("SELECT DISTINCT(SUBJECT) FROM SISCS.R_CLASSSCHEDULE_RV ORDER BY SUBJECT");
+            }
             $sth->execute();
             while ( my ($dept) = $sth->fetchrow_array()) {
                 $dept = lc($dept);
@@ -921,7 +1077,7 @@
 sub get_multusersinfo {
     my ($dom,$caller,$userhash,$instusers,$instids,$types) = @_;
     my $outcome;
-    if (ref($userhash) eq 'HASH') { 
+    if (ref($userhash) eq 'HASH') {
         my ($srchby,$stoponmatch);
         if ($caller eq 'id') {
             $srchby = 'id';
@@ -1102,7 +1258,7 @@
     if ($dbflag) {
         my $matched = 0;
         foreach my $table (@{$srchtables}) {
-            last if ($srchby && $condition eq '');   
+            last if ($srchby && $condition eq '');
             last if ($stoponmatch && $matched);
             my $statement = "SELECT MSUNetID,Pid,FirstName,LastName,Person_Type FROM $table $condition";
             my $sth = $dbh->prepare("$statement");


More information about the LON-CAPA-cvs mailing list