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

[raeburn]

raeburn		Thu Jun 17 16:43:07 2021 EDT

  Modified files:              
    /modules/msu	localenroll.pm 
  Log:
  - Integration with MSU campus information systems
    Use SQL server instead of Oracle server for queries to SISCS database.
  
  
Index: modules/msu/localenroll.pm
diff -u modules/msu/localenroll.pm:1.79 modules/msu/localenroll.pm:1.80
--- modules/msu/localenroll.pm:1.79	Tue Jun 15 20:55:57 2021
+++ modules/msu/localenroll.pm	Thu Jun 17 16:43:07 2021
@@ -1,6 +1,6 @@
 # functions to glue school database system into Lon-CAPA for
 # automated enrollment
-# $Id: localenroll.pm,v 1.79 2021/06/15 20:55:57 raeburn Exp $
+# $Id: localenroll.pm,v 1.80 2021/06/17 16:43:07 raeburn Exp $
 #
 # Copyright Michigan State University Board of Trustees
 #
@@ -93,7 +93,7 @@
     } 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';
+        $DB_PATH = ''dbi:Sybase:server=MSUEDW;database=SISCS';
     } elsif ($type eq 'LC') {
         $DB_PATH = "DBI:mysql:msuenroll";
     }
@@ -267,7 +267,7 @@
                 my $quotedterm = $dbh3->quote($term);
                 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 ");
+                    $sth = $dbh3->prepare( "SELECT CLASS_SECTION FROM 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 ");
                 }
@@ -517,7 +517,7 @@
                 my $quotedsec = $dbh3->quote($sec);
                 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 ");
+                    $count = $dbh3->selectrow_array( "SELECT count(*) FROM 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 ");
                 }
@@ -614,7 +614,7 @@
                     my $quotedterm = $dbh->quote($term);
                     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 ");
+                        $count = $dbh->selectrow_array( "SELECT count(*) FROM 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 ");
                     }
@@ -638,7 +638,7 @@
                             }
                             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'");
+                                $title = $dbh->selectrow_array("SELECT DISTINCT(COURSE_TITLE_LONG) FROM R_COURSECATALOG_RV WHERE SUBJECT = $quotedsubj AND CRSE_CODE = $quotedcrse AND CRSE_EFFDT < '$crsdate' AND CRSE_EFFDT_END > '$crsdate'");
                             };
                         } else {
                             eval {
@@ -662,7 +662,7 @@
                         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");
+                            $credsth = $dbh->prepare("SELECT UNITS_MAXIMUM FROM 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");
                         }
@@ -938,7 +938,7 @@
             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 = $dbh->prepare("SELECT DISTINCT(SUBJECT) FROM R_CLASSSCHEDULE_RV ORDER BY SUBJECT");
             }
             $sth->execute();
             while ( my ($dept) = $sth->fetchrow_array()) {