#!/usr/bin/perl # Copyright 2001-2025 Leslie Richardson # This file is part of Open Admin for Schools. use DBI; use CGI; my $self = 'sendmarksMSS.pl'; # Used for Filename Date my @smonth = ('', 'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'); my %lex = ( 'Main' => 'Main', 'Error' => 'Error', ); eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } #eval require "./slxmllibNew.pl"; #if ( $@ ) { # print $lex{Error}. " $@
\n"; # die $lex{Error}. " $@\n"; #} my $q = CGI->new; my %arr = $q->Vars; print $q->header( -charset, $charset ); my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); my @tim = localtime(time); my $year = $tim[5] + 1900; $tim[4]++; $tim[6]++; # wday; not used. # For Filename my $fyear = $year - 2000; my $filecurrdate = $tim[3]. $smonth[ $tim[4] ]. $fyear; my $filecurrtime = $tim[2]. $tim[1]; # End of filename specific time for (0..4){if (length($tim[$_]) == 1){ $tim[$_] = '0'.$tim[$_];}} my $currdate = "$year-$tim[4]-$tim[3]"; my $currtime = "$tim[2]:$tim[1]:$tim[0]"; my $title = qq{Create MSS Student Marks File}; print qq{$doctype\n$title\n}; print qq{$chartype\n}; print qq{[ Main |\n}; print qq{Report Card ]\n}; print qq{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; selectStudents(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; sendMarks(); } #---------------- sub showStartPage { #---------------- print qq{
\n}; print qq{\n}; # Minimum Mark print qq{
Minumum Mark
\n}; print qq{Check Next Page
\n}; print qq{
\n}; print qq{\n}; print qq{}; print qq{\n}; my $sth1 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?"); my $sth2 = $dbh->prepare("select firstname, lastname from staff where userid = ?"); my $sth = $dbh->prepare("select description, grade, teacher, subjsec, startrptperiod, endrptperiod from subject where grade = '10' or grade = '11' or grade = '12' order by startrptperiod, endrptperiod, grade, description"); $sth->execute; if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my ($currterms, $prevterms); while ( my ( $description, $grade, $teacher, $subjsec, $startterm, $endterm ) = $sth->fetchrow ) { if ( $currterms ) { $prevterms = $currterms; } $currterms = "$startterm-$endterm"; if ( $prevterms and $prevterms ne $currterms ) { print qq{\n}; } # skip if no enrollments. $sth1->execute( $subjsec ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; if ( not $count ) { next; }; $sth2->execute( $teacher ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my ($fn,$ln) = $sth2->fetchrow; if ( not $teacher ){ $teacher = "No Teacher"; } print qq{}; print qq{\n}; print qq{\n}; } print qq{\n}; print qq{
SubjectCode-SecTermsTeacherGradeEnrolSelect
$description$subjsec$startterm-$endterm$fn $ln ($teacher)$grade$count
}; print qq{}; print qq{Check Next Page\n}; print qq{
\n}; print qq{\n}; exit; } #----------------- sub selectStudents { #----------------- # passed a list of subjects to submit marks for. my $checked; if ( $arr{checked} ) { $checked = qq{checked = "checked"}; } delete $arr{checked}; my $minmark = $arr{minmark}; delete $arr{minmark}; # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } print qq{
\n}; print qq{\n}; # print qq{\n}; print qq{
Minumum Mark $minmark
\n}; print qq{

\n}; my $count; foreach my $subjsec ( sort keys %arr ){ # Get Subject Name my $sth = $dbh->prepare("select description, endrptperiod from subject where subjsec = ?"); $sth->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die;} my ($subjectname, $endterm) = $sth->fetchrow; # Get Enrolled Students List $sth = $dbh->prepare("select distinct eval.studnum,studentall.lastname, studentall.firstname from eval,studentall where studentall.studnum = eval.studnum and eval.subjcode = ? order by studentall.lastname, studentall.firstname"); $sth->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $rows = $sth->rows; if ($rows < 1){ next;} # print qq{

$subjectname ($subjsec)

\n}; print qq{\n}; print qq{\n}; print qq{\n}; # Prep for checking existence in table. my $sth1 = $dbh->prepare("select count(studid) from studentwd where studnum = ?"); my $sth2 = $dbh->prepare("select a1 from eval where studnum = ? and subjcode = ? and term = ?"); while ( my ( $studnum,$lastname,$firstname ) = $sth->fetchrow ) { # Check if enrolled! $sth1->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; my $wd; if ( $count ) { $wd = qq{WD\n}; } # Get mark to be sent $sth2->execute( $studnum, $subjsec, $endterm ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $mark = $sth2->fetchrow; $mark =~ s/\%//; # strip any percent signs; my $hasletters = 0; # if the mark contains no A-Z letters; if ( $mark =~ m/[a-zA-Z]/ ) { $hasletters = 1; # it HAS letters in it. } print qq{\n\n}; } print qq{
$subjectname ($subjsec)
NameMarkSelect
$wd $lastname, $firstname$mark}; if ( $mark and not $hasletters ) { if ( $mark < $minmark ) { # no check box option> print qq{$mark\n}; } else { print qq{}; } } print qq{
\n}; $count++; if ( $count % 3 == 0 ) { print qq{
\n}; } } print qq{

\n}; print qq{\n}; exit; } # end of selectStudents #------------ sub sendMarks { # actually just create the export file #------------ use Text::CSV_XS; my $csv = Text::CSV_XS->new( {binary => 1} ); # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # Passed subjsec:studnum # print "School Year:$schoolyear
\n"; # print "School Number:$schoolnumber
\n"; my ($sy,$ey) = split('-',$schoolyear); my $year = $ey; # testing; changed to end year. my $filename = 'MARKS_'. $schoolnumber. '_'. $filecurrdate. '_'. $filecurrtime. '.txt'; open(FH,">",$filename) or die "Cannot Open CSV file\n"; # Write the Header print FH qq{"StudentGUID","SchoolID","YearID","CourseCode","SubSection","ReportingTermNumber","FinalGrade"\r\n}; my $studcount = 1; # Main Loop - once for each student-subject. STUDENT: foreach my $key ( sort keys %arr ) { my ( $subjsec, $studnum ) = split ':',$key; # Get Student Bdate and ProvNum, and Name $sth = $dbh->prepare("select lastname, firstname, provnum,birthdate, mssid, grade from studentall where studnum = ?"); $sth->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname, $provnum,$bdate, $mssid, $grade) = $sth->fetchrow; my $name = "$lastname, $firstname ($studnum)"; if ( not $mssid or not $provnum ) { print qq{
}; print qq{Missing Data: $name / Provnum: $provnum / MSSid: $mssid
\n}; next STUDENT; } # Get Course Data my $sth = $dbh->prepare("select subjcode, section, description, subjsec, startrptperiod, endrptperiod, grade from subject where subjsec = ?"); $sth->execute( $subjsec ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my ( $subjcode, $section, $description, $subjsec, $startterm, $endterm, $grade ) = $sth->fetchrow; # test # $endterm = 1; # my $subject = "$description ($subjsec)"; # Get Mark Information # Need subject/section (subjsec), studnum, and end term $sth = $dbh->prepare("select a1 from eval where subjcode = ? and term = ? and studnum = ?"); $sth->execute( $subjsec, $endterm, $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $mark = $sth->fetchrow; if ( not $mark ) { print qq{
}; print qq{No Mark found for in course $description ($subjsec)
\n}; next STUDENT; } # Get rid of spaces and any percent symbols. $mark =~ s/\%//; $mark =~ s/^\s+//; $mark =~ s/\s+$//; print "$studcount. $firstname $lastname GR: $grade MSS:$mssid PN:$provnum Grad Yr:$year
\n"; $studcount++; my @line; push @line, $mssid; push @line, $schoolnumber; push @line, $year; push @line, $subjcode; push @line, $section; push @line, $endterm; push @line, $mark; # qq{'$mssid','$schoolnumber',$year,$subjcode,'$section',$endterm,'$mark'\n}; if ( $csv->combine( @line ) ) { my $record = $csv->string; print FH $record, "\r\n"; } else { my $err = $csv->error_input; print qq{Combine failed: $err\n\n}; } # print FH qq{'$mssid','$schoolnumber',$year,$subjcode,'$section',$endterm,'$mark'\n}; } # End of this student-subjsec close FH; # File upload stuff here. system("mv $filename $downloaddir"); print qq{

View/Download Mark File

\n}; print qq{
[ $filename ]
\n}; print qq{\n}; exit; }