[LON-CAPA-cvs] cvs: modules /msu localenroll.pm
raeburn
lon-capa-cvs@mail.lon-capa.org
Thu, 11 Dec 2003 00:37:34 -0000
This is a MIME encoded message
--raeburn1071103054
Content-Type: text/plain
raeburn Wed Dec 10 19:37:34 2003 EDT
Modified files:
/modules/msu localenroll.pm
Log:
Added some additional subroutines to gather official sections for a course,
to validate courseIDs of course sections and crosslisted courses that are to
be affiliated with a course, to add an entry fo each new course section in
the Registrar's Office LONCAPA table that acts as the gatekeeper that determines which classlists are available to MSU LONCAPA courses. Also added logic to check
course owner against RO_CLIMS_VIEW to determine whether the course owner has the
requisite entry in CLIFMS needed to retrieve classlist data.
Modified subroutines that call RO_ClassList and SISInfo databases. Credentials for
RO database retrieved from file.
--raeburn1071103054
Content-Type: text/plain
Content-Disposition: attachment; filename="raeburn-20031210193734.txt"
Index: modules/msu/localenroll.pm
diff -u modules/msu/localenroll.pm:1.1 modules/msu/localenroll.pm:1.2
--- modules/msu/localenroll.pm:1.1 Tue Dec 9 15:03:04 2003
+++ modules/msu/localenroll.pm Wed Dec 10 19:37:34 2003
@@ -6,27 +6,57 @@
sub fetch_enrollment {
my ($dom,$affiliatesref,$replyref) = @_;
- $ENV{SYBASE} = '/usr/local/freetds';
-
- my $DB_PATH = "dbi:Sybase:server=ESDB1;database=RO_ClassList";
- my $DB_USER =""; # Not stored in CVS
- my $DB_PASSWD = ""; # Not stored in CVS
-
my $configvars = &LONCAPA::Configuration::read_conf('loncapa.conf');
-
- my $dbh= DBI->connect($DB_PATH,$DB_USER,$DB_PASSWD);
- my $dbflag = 0;
- if (defined $dbh) {
- $dbflag = 1;
+
+ my ($dbh,$dbflag) = &connect_DB($$configvars{'lonDaemons'},'RO');
+ if ($dbflag) {
foreach my $crs (sort keys %{$affiliatesref}) {
my $xmlstem = $$configvars{'lonDaemons'}."/tmp/".$dom."_".$crs."_";
$$replyref{$crs} = &write_class_data ($dbh,$xmlstem,\@{$$affiliatesref{$crs}});
}
- $dbh->disconnect;
+ &disconnect_DB($dbh);
}
return $dbflag;
}
+sub connect_DB {
+ my ($keydir,$type) = @_;
+ $ENV{SYBASE} = '/usr/local/freetds';
+ my $DB_PATH;
+ my $DB_USER = '';
+ my $DB_PASSWD = '';
+
+ if ($type eq 'RO') {
+ $DB_PATH = "dbi:Sybase:server=ESDB1;database=RO_ClassList";
+ if (open (my $fh, "<$keydir/autoenroll.dat") ) {
+ ($DB_USER,$DB_PASSWD) = split/:/,<$fh>;
+ chomp($DB_PASSWD);
+ close($fh);
+ }
+ } elsif ($type eq 'SIS') {
+ $DB_PATH = "dbi:Sybase:server=msudata;database=SISInfo";
+ $DB_USER = ""; # Not in CVS
+ $DB_PASSWD = ""; # Not in CVS
+ }
+
+ my $dbh;
+ my $dbflag = 0;
+ eval { $dbh = DBI->connect($DB_PATH,$DB_USER,$DB_PASSWD); };
+ if ($@) {
+ $dbflag = 0;
+ } else {
+ if (defined $dbh) {
+ $dbflag = 1;
+ }
+ }
+ return ($dbh,$dbflag);
+}
+
+sub disconnect_DB() {
+ my $dbh = shift;
+ $dbh->disconnect;
+}
+
sub write_class_data {
my ($dbh,$xmlstem,$coursesref) = @_;
my $stucount = 0;
@@ -38,7 +68,6 @@
<!DOCTYPE text>
<students>
|;
-
my $sth = $dbh->prepare("SELECT Pid,Pilot_Id,Student_Name FROM LONCAPA_ClassList WHERE Term_Code = '$1' AND Subj_Code = '$2' AND Crse_Code = '$3' AND Sctn_Code = '$4' ORDER BY Student_Name");
$sth->execute();
while ( my($pid,$pilot,$name) = $sth->fetchrow_array ) {
@@ -86,7 +115,124 @@
}
sub get_sections {
+ my $coursecode = shift;
+ my @secs = ();
+ if ($coursecode =~ m/^([suf]s\d{2})(\w{2,3})(\d{3,4}\w?)/) {
+ my $term = $1;
+ my $subj = $2;
+ my $crse = $3;
+ $subj =~tr/a-z/A-Z/;
+ $crse =~tr/a-z/A-Z/;
+ $term =~tr/a-z/A-Z/;
+ my $configvars = &LONCAPA::Configuration::read_conf('loncapa.conf');
+
+ my ($dbh3,$dbflag) = &connect_DB($$configvars{'lonDaemons'},'SIS');
+ 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 ");
+ $sth->execute();
+ while ( my @row = $sth->fetchrow_array( ) ) {
+ if (!grep/^99/,@row) {
+ push @secs, @row;
+ }
+ }
+ $sth->finish;
+ };
+ &disconnect_DB($dbh3);
+ }
+ }
+ return @secs;
+}
+
+sub new_course {
+ my ($course_id,$owner) = @_;
+ my $outcome = '';
+ if ($owner eq '') {
+ $outcome = "Inclusion of enrollment could not be established for the course section $course_id because no owner was provided for this LON-CAPA course.";
+ } else {
+ my $configvars = &LONCAPA::Configuration::read_conf('loncapa.conf');
+ if ($course_id =~ m/^([suf]s\d{2})(\w{2,3})(\d{3,4}\w?)(\d{3})$/) {
+ my $sem = $1;
+ my $subj = $2;
+ my $crse = $3;
+ my $sec = $4;
+ $sem =~tr/a-z/A-Z/;
+ $subj =~tr/a-z/A-Z/;
+ $crse =~tr/a-z/A-Z/;
+ my ($dbh,$dbflag) = &connect_DB($$configvars{'lonDaemons'},'RO');
+ if ($dbflag) {
+# Check if instructor is in CLIFMS for this course
+ my $clifmscount = $dbh->selectrow_array("SELECT count(*) FROM RO_CLIFMS_VIEW WHERE Term_Code='$sem' AND Subj_Code='$subj' AND Crse_Code='$crse' AND Sctn_Code='$sec' AND MSUNetID='$owner' AND Record_Type='1'");
+ if ($clifmscount > 0) {
+ my $loncount = $dbh->selectrow_array("SELECT count(*) FROM LONCAPA WHERE Term_Code='$sem' AND Subj_Code='$subj' AND Crse_Code='$crse' AND Sctn_Code='$sec' AND MSUNetID='$owner'");
+ if ($loncount == 0) {
+ my $sth = $dbh->prepare("INSERT INTO LONCAPA (Term_Code, Subj_Code, Crse_Code, Sctn_Code, MSUNetId) VALUES ('$sem','$subj','$crse','$sec','$owner') ");
+ $sth->execute;
+ $sth->finish;
+ }
+ $outcome = 'ok';
+ } else {
+ $outcome = "Inclusion of enrollment could not be established for the course section $course_id because the owner of this LON-CAPA course ($owner) is not included in CLIFMS as instructional faculty for the requested semester, course and section. Please contact the administrator of the department that controls course $course_id and ask for $owner to be added as faculty.";
+ }
+ &disconnect_DB($dbh);
+ } else {
+ $outcome = "Inclusion of enrollment could not be established, because a connection to the Registrar's database failed.\n";
+ }
+ } else {
+ $outcome = "Inclusion of enrollment could not be established becauses $course_id is not a valid MSU course section.";
+ }
+ }
+ return $outcome;
+}
+
+sub validate_courseID {
+ my $course_id = shift;
+ my @secs = ();
+ my $outcome = '';
+ if ($course_id =~ m/^([suf]s\d{2})(\w{2,3})(\d{3,4}\w?)(\d{3})/) {
+ my $term = $1;
+ my $subj = $2;
+ my $crse = $3;
+ my $sec = $4;
+ $subj =~tr/a-z/A-Z/;
+ $crse =~tr/a-z/A-Z/;
+ $term =~tr/a-z/A-Z/;
+
+ my $configvars = &LONCAPA::Configuration::read_conf('loncapa.conf');
+
+ my ($dbh3,$dbflag) = &connect_DB($$configvars{'lonDaemons'},'SIS');
+ 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 ");
+ 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.';
+ }
+ };
+ if ($@) {
+ $outcome = "There was a problem connecting to the SIS 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.";
+ }
+ } else {
+ $outcome = "The course section number you entered is not a valid MSU course section";
+ }
+}
+sub create_password {
+ my $authparam = shift;
+ my $authchk = 'ok';
+ my $create_passwd = 0;
+ return ($authparam,$create_passwd,$authchk);
}
1;
--raeburn1071103054--