#!/usr/bin/perl # Copyright 2001-2019 Leslie Richardson # This file is part of Open Admin for Schools. # Open Admin for Schools is free software; you can redistribute it # and/or modify it under the terms of the GNU General Public License # as published by the Free Software Foundation; either version 2 of # the License, or (at your option) any later version. use DBI; use CGI; use Time::JulianDay; my %lex = ('Main' => 'Main', 'Grade' => 'Grade', 'Continue' => 'Continue', 'Section' => 'Section', 'Course' => 'Course', 'Teacher' => 'Teacher', 'Error' => 'Error', ); my $self = 'preplancoursemaster.pl'; # Read Config eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $passingaverage = $pp_Grade9PassingAverage; # from configuration my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; # load the sasked ed courses (they have a grade field). my (%saskedgrade, %saskedname); my $sth = $dbh->prepare("select * from sasked_courses"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( $ref = $sth->fetchrow_hashref ) { my %r = %$ref; $saskedgrade{$r{code}} = $r{grade}; $saskedname{$r{code}} = $r{title}; } my $title = "Preplan Course Offerings for Upcoming Year"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} |\n}; print qq{ Report Card ]\n}; print qq{

$title

\n}; # Find those passing in 9; check those passing courses in grade 10; # Get all requirements for all students, split by grade my %req; # requirements, $req{studnum}{grade} = @incompletes; # Get all students my $sth = $dbh->prepare("select studnum, lastname, firstname, provnum, grade from student"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( $ref = $sth->fetchrow_hashref ) { my %r = %$ref; my $studnum = $r{studnum}; if ( $r{provnum} ) { my @result10 = checkCredit10( $r{provnum}, $dbh, $r{studnum} ); if ( @result10 ) { push @{ $req{$studnum}{10}}, @result10; } my @result11 = checkCredit11( $r{provnum}, $dbh ); if ( @result11 ) { push @{ $req{$studnum}{11}}, @result11; } my @result12 = checkCredit12( $r{provnum}, $dbh ); if ( @result12 ) { push @{ $req{$studnum}{12}}, @result12; } } else { print qq{
Missing Provincial Number for $r{firstname} $r{lastname} ($r{studnum}), }; print qq{Grade $r{grade}
\n}; } } # end student loop loading up %res hash. # Display the structure to test =head foreach my $studnum ( sort keys %req ) { foreach my $gr ( sort keys %{ $req{$studnum}} ) { print @{ $req{$studnum}{$gr} }, "
\n"; # my @inc = @{ $req{$studnum}{$gr} }; # my $inc = join(', ',@inc ); # print qq{
SN:$studnum - GR:$gr - INC:$inc
\n}; } } =cut if ( not $arr{page} ) { findCourseRequirementCounts(); } elsif ( $arr{page} == 1 ) { # Page 2 delete $arr{page}; selectCourses(); # select courses and number of semesters } elsif ( $arr{page} == 2 ) { # Page 3 delete $arr{page}; setCourses(); # set semester, teacher, grade offered, any backings. } elsif ( $arr{page} == 3 ) { # Page 4 delete $arr{page}; setPeriods(); # choose periods for courses for each semester (due to all year courses mixes). } elsif ( $arr{page} == 4 ) { # Page 5 delete $arr{page}; viewCourses(); # update periods, display final table values; provide links to reset. } #-------------- sub showTeacher { # display all teacher values for page 3 and 4. #-------------- # Load staff into hash; not restricted to classroom teachers. my (%teachers, %teachername); my $sth = $dbh->prepare("select lastname, firstname, userid from staff order by lastname, firstname"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my ( $lastname, $firstname, $userid) = $sth->fetchrow ) { $teachers{"$lastname, $firstname ($userid)"} = $userid; $teachername{$userid} = "$lastname, $firstname"; } # Get Number of Semesters in preplan CM table my $sth = $dbh->prepare("select max(semester) from preplan_coursemaster where semester != 'allyear'"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $semestercount = $sth->fetchrow; # Get PERIODS for each course, each semester. my $sth1 = $dbh->prepare("select periods from preplan_courseperiods where subjsec = ? and semester = ?"); # Get TEACHERS in the preplan coursemaster table my %sort; my $sth = $dbh->prepare("select distinct teacher from preplan_coursemaster where teacher is not NULL"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $teacher = $sth->fetchrow ) { # print qq{
Teacher:$teacher
\n}; $sort{"$teachername{$teacher}$teacher"} = $teacher; } # Get BACKINGS for courses. - not done yet! my %backed; my $sth = $dbh->prepare("select subjsec, backedwith from preplan_coursemaster"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my ($subjsec, $backedwith) = $sth->fetchrow ) { if ( $backedwith ) { if ( $backed{$subjsec} and $backed{$subjsec} ne $backedwith ) { # a problem. triple back? } else { $backed{$subjsec} = $backedwith; } $backed{$backedwith} = $subjsec; } } # Display Courses by this teacher. my $sth = $dbh->prepare("select * from preplan_coursemaster where teacher = ?"); # Loop through each teacher foreach my $key ( sort keys %sort ) { my $teacher = $sort{$key}; # print qq{
Teacher:$teacher
\n}; my ( %courses, %totals, %data); # totals{semester}; # Get Courses by this teacher. $sth->execute($teacher); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; my $subjsec = $r{subjsec}; my $semester = $r{semester}; $data{$subjsec} = $ref; push @{ $courses{ $semester } }, $subjsec; } # Get Rows for Table. my $rows; foreach my $semester ( 1.. $semestercount ) { my $temp = scalar @{ $courses{$semester} }; if ( $temp > $rows ) { $rows = $temp; } } # print "Rows:$rows
\n"; # Start Table if ( $rows ) { print qq{\n}; print qq{\n}; print qq{}; foreach my $semester (1..$semestercount) { print qq{}; print qq{}; } print qq{\n}; } # Loop through main rows until we run out of courses in arrays foreach my $row (1..$rows ) { # $rows calculated above. print qq{}; foreach my $semester (1..$semestercount) { my $subjsec = shift @{ $courses{ $semester }}; my ($code, $section) = split('-', $subjsec); my $backed; if ( $data{$subjsec}->{backedwith} ) { $backed = qq{*}; } my $userid = $data{$subjsec}->{teacher}; my $teacher = $teachername{$userid}; # my $grade = $data{$subjsec}->{offeredto}; print qq{}; # print qq{}; # Get Periods for this course, this semester if ( $subjsec ) { # could be blank $sth1->execute($subjsec, $semester); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $periods = $sth1->fetchrow; print qq{\n}; $totals{$semester} += $periods; } else { # empty cell print qq{\n}; } } # end of semester loop print qq{\n}; } # All Year Courses. foreach my $subjsec ( @{ $courses{'allyear'} } ) { print qq{}; my ($code, $section) = split('-', $subjsec); my $userid = $data{$subjsec}->{teacher}; my $teacher = $teachername{$userid}; foreach my $semester (1..$semestercount) { print qq{}; # print qq{}; # Get Periods for this course, this semester if ( $subjsec ) { # could be blank $sth1->execute($subjsec, $semester); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $periods = $sth1->fetchrow; print qq{}; $totals{$semester}+= $periods; } else { print qq{}; } } print qq{\n}; } if ( $rows ) { print qq{\n}; foreach my $semester (1..$semestercount) { print qq{}; } print qq{\n
}; print qq{$teachername{$teacher}
Semester $semesterCodePeriods
$saskedname{$code}$backed$subjsec$teacher$periods
$saskedname{$code}$subjsec$teacher$periods
}; print qq{Semester $semester Totals$totals{$semester}
\n}; } } # end of teacher loop exit; } #---------------- sub viewCourses { # update Periods; display all course values. #---------------- print qq{

Page 5 - View Courses

\n}; # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # Update the Periods my $sth = $dbh->prepare("update preplan_courseperiods set periods = ? where subjsec = ? and semester = ?"); my $sth1 = $dbh->prepare("select count(*) from preplan_courseperiods where subjsec = ? and semester = ?"); my $sth2 = $dbh->prepare("insert into preplan_courseperiods (periods, subjsec, semester) values(?,?,?)"); foreach my $key ( sort keys %arr ) { my ($ident, $courses, $semester) = split(':', $key); my $periods = $arr{$key}; my @courses = split('/', $courses ); # could be compound foreach my $subjsec ( @courses ) { # Check for existing record $sth1->execute($subjsec, $semester); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; if ( $count ) { # update record if we have an existing one. $sth->execute($arr{$key}, $subjsec, $semester); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } } else { # insert record $sth2->execute($arr{$key}, $subjsec, $semester); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } # print "
Insert: $arr{$key}, $subjsec, $semester
\n"; } } } # Course Periods all updated at this point. print qq{
}; print qq{Periods Updated in Preplan Course Master
\n}; # Load staff into hash; not restricted to classroom teachers. my (%teachers, %teachername); my $sth = $dbh->prepare("select lastname, firstname, userid from staff order by lastname, firstname"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my ( $lastname, $firstname, $userid) = $sth->fetchrow ) { $teachers{"$lastname, $firstname ($userid)"} = $userid; $teachername{$userid} = "$lastname, $firstname"; } # Display Course Layout for each grade, and give totals. # Get Number of Semesters my $sth = $dbh->prepare("select max(semester) from preplan_coursemaster where semester != 'allyear'"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $semestercount = $sth->fetchrow; # print qq{
Semesters:$semestercount
\n}; # Get PERIODS for each course, each semester. my $sth1 = $dbh->prepare("select periods from preplan_courseperiods where subjsec = ? and semester = ?"); # Display Courses by Grade my $sth = $dbh->prepare("select c.* from preplan_coursemaster c, sasked_courses s where c.subjcode = s.code and offeredto = ? order by s.title, c.subjsec"); foreach my $grade (10..12) { my (%sort,%courses, %totals, %data); # totals{semester}; # Get Courses $sth->execute($grade); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; my $subjsec = $r{subjsec}; $data{$subjsec} = $ref; push @{ $courses{$r{semester}} }, $subjsec; # could hold entire record, too? } # Get Rows for Table. my $rows; foreach my $semester ( 1.. $semestercount ) { my $temp = scalar @{ $courses{$semester} }; if ( $temp > $rows ) { $rows = $temp; } } # print "Rows:$rows
\n"; # Start Table if ( $rows ) { print qq{\n}; print qq{\n}; print qq{}; foreach my $semester (1..$semestercount) { print qq{}; print qq{}; } print qq{\n}; } # Loop through main rows until we run out of courses in arrays foreach my $row (1..$rows ) { # $rows calculated above. print qq{}; foreach my $semester (1..$semestercount) { my $subjsec = shift @{ $courses{ $semester }}; my ($code, $section) = split('-', $subjsec); my $backed; # #if ( $data{$subjsec}->{backedwith} ) { # $backed = qq{*}; # } my $userid = $data{$subjsec}->{teacher}; my $teacher = $teachername{$userid}; # my $grade = $data{$subjsec}->{offeredto}; print qq{}; print qq{}; # Get Periods for this course, this semester if ( $subjsec ) { # could be blank $sth1->execute($subjsec, $semester); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $periods = $sth1->fetchrow; print qq{\n}; $totals{$semester} += $periods; } else { # empty cell print qq{\n}; } } # end of semester loop print qq{\n}; } # All Year Courses. foreach my $subjsec ( @{ $courses{'allyear'} } ) { print qq{}; my ($code, $section) = split('-', $subjsec); my $userid = $data{$subjsec}->{teacher}; my $teacher = $teachername{$userid}; foreach my $semester (1..$semestercount) { print qq{}; print qq{}; # Get Periods for this course, this semester if ( $subjsec ) { # could be blank $sth1->execute($subjsec, $semester); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $periods = $sth1->fetchrow; print qq{}; $totals{$semester}+= $periods; } else { print qq{}; } } print qq{\n}; } if ( $rows ) { print qq{\n}; foreach my $semester (1..$semestercount) { print qq{}; } print qq{\n
}; print qq{Grade $grade
Semester $semesterCodeTeacherPeriods
$saskedname{$code}$backed$subjsec$teacher$periods
$saskedname{$code}$subjsec$teacher$periods
}; print qq{Semester $semester Totals$totals{$semester}
\n}; } # Now put in backings table # Start Table my $first = 1; foreach my $subjsec ( sort keys %data ) { if ( $data{$subjsec}->{backedwith} ) { if ( $first ) { print qq{\n}; print qq{\n}; print qq{\n}; $first = 0; } my ($code, $section) = split('-',$subjsec); my $backedwith = $data{$subjsec}->{backedwith}; my ($bcode, $bsection) = split('-',$backedwith); print qq{}; print qq{\n}; } } if ( not $first ) { print qq{
}; print qq{Grade $grade Backed Courses (offered at same time)
$lex{Course} A$lex{Course} B
$saskedname{$code} ($subjsec)$saskedname{$bcode} ($backedwith)
\n}; } print qq{
\n}; print qq{
\n}; } # end of grades loop print qq{
\n}; print qq{\n}; print qq{
\n}; print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
\n}; print qq{\n}; exit; } # end of viewCourses; (and update periods) #-------------- sub setPeriods { #-------------- # print qq{
Set Periods
\n}; # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # $id:offered, semester,teacher; then backing courses, then 3 values: print qq{\n\n\n}; print qq{
\n\n}; #==================================== print qq{

Page 4 - Set Course Periods

\n}; my $dayspercycle = $pp_DaysPerCycle; my $teachingperiods = $pp_PeriodsPerDay; my $defppc = $pp_DefaultPeriodsPerCycle; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{}; print qq{\n}; print qq{}; print qq{\n}; print qq{
From Configuration System
Teaching Periods Per Day$pp_PeriodsPerDay
Days per Cycle$pp_DaysPerCycle
Default Periods per Cycle
(for a course)		$pp_DefaultPeriodsPerCycle
\n\n}; # delete $arr{defppc}; # delete $arr{dayspercycle}; # delete $arr{teachingperiods}; #----------------------------------------------------------- # This first section of the subroutine updates values passed from # previous page: teacher, semester, and offeredto, and backings. # ---------------------------------------------------------- # Extract 'BackAxx' and 'BackBxx' values. my %back; # {$idx}{'backA' or 'backB' foreach my $key ( sort keys %arr ) { my ($val, $idx) = split(':', $key); if ( $val =~ m/back.+/ ) { # match! if ( $arr{$key} ) { # we have a value $back{$idx}{$val} = $arr{$key}; } delete $arr{$key}; } } # Update the Backing Field (backedwith) my %backed; # add both pairs into the hash A->B, B->A my $sth = $dbh->prepare("update preplan_coursemaster set backedwith = ? where subjsec = ?"); my $sth1 = $dbh->prepare("select backedwith from preplan_coursemaster where subjsec = ?"); foreach my $idx ( sort keys %back ) { foreach my $gr ( 10..12 ) { my $crsA = $back{$idx}{"backA$gr"}; my $crsB = $back{$idx}{"backB$gr"}; if ( $crsA and $crsB ) { # we have the 2 courses # set B into A record my ($codeA,$section) = split('-',$crsA); my ($codeB,$section) = split('-',$crsB); $backed{$crsA} = $crsB; $backed{$crsB} = $crsA; # Check for an existing backed subjsec value in 'backedwith' field. # $sth1->execute($crsB); # if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } # my $count = $sth1->fetchrow; # if ( not $count ) { # $sth->execute($crsA, $crsB); # if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } # } # $sth1->execute($crsA); # if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } # my $count = $sth1->fetchrow; # if ( not $count ) { $sth->execute($crsB, $crsA); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } # } # print qq{
Backed Courses: $saskedname{$codeA} # ($crsA) / $saskedname{$codeB} ($crsB)
\n}; } } } # Now do the course Information. my (%courses, %periods); foreach my $key ( sort keys %arr ) { my ($id, $field) = split(':', $key); if ( $id =~ m/\d/ ) { if ( $arr{$key} ) { # if we have a value if ( $field eq 'periods') { $periods{$id} = $arr{$key}; } else { $courses{$id}{$field} = $arr{$key}; } } delete $arr{$key}; } } # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # %arr should now be empty. # Write Updated Values to the preplan course master. foreach my $id ( sort keys %courses ) { # print qq{
ID:$id - }; foreach my $field ( sort keys %{ $courses{$id}} ) { my $sth = $dbh->prepare("update preplan_coursemaster set $field = ? where id = ?"); $sth->execute($courses{$id}{$field}, $id); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } # print qq{ $field $courses{$id}{$field}/ }; } } print qq{
}; print qq{Preplan Course Master Updated - Semester, Teacher, Grade Offered, and Backings
\n}; # Passed Updates now done: teacher, semester, offeredto, backings. #----------------------------------------------------------------------------------- #---------------------------------------------- # Now get the periods for the courses - page 4 #---------------------------------------------- # Get Number of Semesters; not using configuration since we need to use set values. my $sth = $dbh->prepare("select max(semester) from preplan_coursemaster where semester != 'allyear'"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $semestercount = $sth->fetchrow; # print qq{
Semesters:$semestercount
\n}; # Get PERIODS for each course, each semester. my $sth1 = $dbh->prepare("select periods from preplan_courseperiods where subjsec = ? and semester = ?"); # Display Courses by Grade my $sth = $dbh->prepare("select c.* from preplan_coursemaster c, sasked_courses s where c.subjcode = s.code and offeredto = ? order by s.title, c.subjsec"); # Form Start print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
}; print qq{Backed Courses are in bold type
\n}; print qq{\n}; foreach my $grade (10..12) { my (%sort,%courses, %totals, %data ); # Get Courses for this grade, and compress backings. $sth->execute($grade); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; my $subjsec = $r{subjsec}; $temp{$subjsec} = $ref; $data{$subjsec} = $ref; } my $cref = compressCourse( \%temp ); my %compressed = %$cref; # only keys of use; values will be damaged for compound keys # foreach my $key ( sort keys %compressed ) { # print qq{K:$key V:$compressed{$key}
\n}; # } foreach my $key ( sort keys %compressed ) { # may have compound keys with / my @vals = split('/', $key); # use $val[0], first subjsec my $subjsec = $vals[0]; %r = %{ $data{$subjsec}}; push @{ $courses{$r{semester}} }, $key; # could be compound } # Get Rows for Table. my $rows; foreach my $semester ( 1.. $semestercount ) { my $temp = scalar @{ $courses{$semester} }; if ( $temp > $rows ) { $rows = $temp; } } #print "Rows:$rows
\n"; # Start Table if ( $rows ) { print qq{\n}; print qq{\n}; print qq{}; foreach my $semester (1..$semestercount) { print qq{}; } print qq{\n}; } # Loop through main rows until we run out of courses in arrays foreach my $row (1..$rows ) { # $rows calculated above. print qq{}; foreach my $semester (1..$semestercount) { my $subjsec = shift @{ $courses{ $semester }}; # check for compound key... my @vals = split('/', $subjsec); # use $val[0], first subjsec if ( scalar @vals > 1 ) { print qq{}; # subjsec (or compound subjsec/subjsec) # Get Periods for this course, this semester; if compound use first only if ( $subjsec ) { # could be blank my $tempsubjsec; if ( $subjsec =~ m/\// ) { # compound subjsec my @vals = split('/', $subjsec); # use $val[0], first subjsec $tempsubjsec = $vals[0]; } else { $tempsubjsec = $subjsec; } $sth1->execute($tempsubjsec, $semester); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $periods = $sth1->fetchrow; # if ( not $periods ) { $periods = $defppc; } # default periods per cycle; above. print qq{\n}; $totals{$semester} += $periods; } else { # empty cell print qq{\n}; } } # end of semester loop print qq{\n}; } # All Year Courses. foreach my $subjsec ( @{ $courses{'allyear'} } ) { print qq{}; my ($code, $section) = split('-', $subjsec); my $backed; if ( $data{$subjsec}->{backedwith} ) { $backed = qq{*}; } foreach my $semester (1..$semestercount) { print qq{}; # Get Periods for this course, this semester if ( $subjsec ) { # could be blank $sth1->execute($subjsec, $semester); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $periods = $sth1->fetchrow; if ( not $periods ) { $periods = $defppc; } # default periods per cycle; above. print qq{\n}; $totals{$semester}+= $periods; } else { # empty cell; not possible? print qq{\n}; } } } if ( $rows ) { print qq{\n}; foreach my $semester (1..$semestercount) { print qq{}; } print qq{\n
Grade $grade
Semester $semesterCodePeriods
}; } else { print qq{}; } foreach my $val ( @vals ) { my ($code, $section) = split('-', $val); # my $backed; # if ( $data{$subjsec}->{backedwith} ) { # $backed = qq{*}; # } print qq{$saskedname{$code}
\n}; } print qq{		$subjsec
$saskedname{$code}$backed$subjsec
}; print qq{Semester $semester Totals$totals{$semester}
\n}; } } # end of grades loop print qq{\n}; print qq{
\n}; #========================= print qq{\n
\n}; # next column print qq{
}; print qq{[ * = Backed ]
\n}; print qq{
}; print qq{Current Teacher Load
\n}; showTeacherSummary(); showTeacher(); # show current loading. print qq{
\n}; print qq{\n}; exit; } # end of setPeriods - Page 4 #--------------- sub setCourses { # Page 3 #--------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } print qq{\n\n\n}; print qq{
\n\n}; #==================================== print qq{

Page 3 - Set Course Values: Teacher, Semester, Grade Offered, Backings

\n}; print qq{
The Grade Offered is where students take a course designed for a different grade. (ie. Art 30 offered to Grade 10's). Course Backings are 2 (or more) courses that are taught at the same time (typically to split a large student group based on interests or ability). They may actually be taught in the same classroom by the same teacher or they may be in separate rooms with separate teachers. These backed courses must be taught in the same semester and have the same number of periods per cycle.
\n}; print qq{

For courses longer than a semester, but not all year, select 'All Year', and then only set the periods for the semesters that the course is offered, on page 4.

\n}; # Extract sections from %arr my %section; foreach my $key ( sort keys %arr ) { my ($sec, $code ) = split(':', $key); if ( $sec eq 'sec' ) { # we have a section $section{$code} = $arr{$key}; delete $arr{$key}; } } my $addelectives10 = $arr{addelectives10}; delete $arr{addelectives10}; my $addelectives11 = $arr{addelectives11}; delete $arr{addelectives11}; my $addelectives12 = $arr{addelectives12}; delete $arr{addelectives12}; my $next10 = $arr{next10}; delete $arr{next10}; my $next11 = $arr{next11}; delete $arr{next11}; my $next12 = $arr{next12}; delete $arr{next12}; # Get Semesters my $semesters; if ( $pp_SemesterCount ) { print qq{

Semester Count - $pp_SemesterCount (from configuration)

\n}; $semesters = $pp_SemesterCount; } else { # get from table. my $sth = $dbh->prepare("select max(semester) from preplan_coursemaster where semester != 'allyear'"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } $semesters = $sth->fetchrow; print qq{

Semester Count - $semesters (from preplan table)

\n}; } my $deleterecords = $arr{deleterecords}; delete $arr{deleterecords}; # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # Only course codes left in %arr now. # Load staff into hash; not restricted to classroom teachers. my (%teachers, %teachername); my $sth = $dbh->prepare("select lastname, firstname, userid from staff order by lastname, firstname"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my ( $lastname, $firstname, $userid) = $sth->fetchrow ) { $teachers{"$lastname, $firstname ($userid)"} = $userid; $teachername{$userid} = "$lastname,$firstname"; } if ( $deleterecords ) { # Delete any existing records in preplan_coursemaster table. my $sth = $dbh->prepare("delete from preplan_coursemaster"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} # insert the new records. my $sth = $dbh->prepare("insert into preplan_coursemaster (subjcode, section, subjsec, description, grade) values(?,?,?,?,?)"); foreach my $coursecode ( keys %arr ) { foreach my $section (1.. $section{$coursecode} ) { my $subjsec = $coursecode. '-'. $section; $sth->execute($coursecode, $section, $subjsec, $saskedname{$coursecode}, $saskedgrade{$coursecode}); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} } } # table now populated. print qq{

Courses Added to PrePlan Course Master

\n}; } # Delete current table. # Put in a teacher of that course if blank in PPCM my $sth1 = $dbh->prepare("select teacher from subject where subjcode = ?"); my $sth2 = $dbh->prepare("update preplan_coursemaster set teacher = ? where id = ?"); my $sth = $dbh->prepare("select id, subjcode, teacher from preplan_coursemaster"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my ($id, $subjcode, $teacher) = $sth->fetchrow ) { if ( not $teacher ) { # we will add from subject table, if present. # Get current a teacher from 'subject' table for this course (NOTE: not unique)) $sth1->execute( $subjcode ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $teacher = $sth1->fetchrow; if ( $teacher ) { # update coursemaster $sth2->execute( $teacher, $id ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } } } } # Check for records in preplan_coursemaster; my $sth = $dbh->prepare("select count(*) from preplan_coursemaster"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my $count = $sth->fetchrow; if ( not $count ) { print qq{
\n}; print qq{

No Pre-plan Course Master records found. Please start on Page 1

\n}; print qq{
\n}; print qq{
\n}; print qq{\n}; exit; } print qq{

Course Teachers Added, where none set

\n}; # Start Form print qq{
\n}; print qq{\n}; # Not Needed anymore # Show Days per cycle, PPD # print qq{\n}; # print qq{\n}; # print qq{}; # print qq{\n}; # # print qq{}; # print qq{\n}; # print qq{
From Configuration System
Teaching Periods Per Day$pp_PeriodsPerDay
Days per Cycle$pp_DaysPerCycle
\n\n}; # Submit Button print qq{
}; print qq{
\n}; # Queries setup my $sth2 = $dbh->prepare("select lastname, firstname from staff where userid = ?"); # Get Subject Info my $sth = $dbh->prepare("select * from preplan_coursemaster where grade = ? order by semester, description "); foreach my $grade ( 10..12 ) { # Start Table print qq{}; print qq{\n}; # Table Heading print qq{}; print qq{\n}; $sth->execute( $grade ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my (%courses, %sortc, %data); my $count = 1; my %data; while ( my $ref = $sth->fetchrow_hashref ) { %r = %$ref; my $id = $r{id}; $data{$r{subjsec}} = $ref; $courses{$r{subjsec}} = $r{description}; $sortc{"$r{description}-$r{subjsec}"} = $r{subjsec}; print qq{}; print qq{\n}; # Teacher print qq{\n}; # Semester my $defsem = ($count % $semesters) + 1; $count++; print qq{\n}; # Grade Offering print qq{\n}; } print qq{
}; print qq{Grade $grade
$lex{Course} ($lex{Section})$lex{Grade}$lex{Teacher}SemesterGrade
Offered
$count. $r{description} ($r{subjsec})$r{grade}
\n}; # Backings # Start Table print qq{}; print qq{\n}; print qq{\n}; # Look for existing backings. my $count = 1; # count any existing values foreach my $desc ( sort keys %sortc ) { my $subjsec = $sortc{$desc}; if ( $data{$subjsec}->{backedwith} ) { my $back = $data{$subjsec}->{backedwith}; my ($bcode,$bsection) = split('-',$back); my ($code,$section) = split('-', $subjsec); print qq{\n}; print qq{\n\n}; $count++; } } foreach my $idx ($count..10) { print qq{\n}; print qq{\n}; } print qq{
}; print qq{Grade $grade Backed Courses (offered at same time)
\n}; print qq{Courses must have same semester and #periods.}; print qq{
$lex{Course} A$lex{Course} B
\n}; # End of backings tables. print qq{
\n}; print qq{
\n}; } # next grade; # Submit Button print qq{
}; print qq{
\n}; print qq{
\n}; #========================= print qq{\n		\n}; # next column print qq{
}; print qq{[ * = Backed ]
\n}; print qq{
}; print qq{Current Teacher Load
\n}; showTeacherSummary(); showTeacher(); # show current loading. print qq{
\n}; print qq{\n}; exit; } # end of setCourses - Page 3 #------------------------------ sub findCourseRequirementCounts { #------------------------------ print qq{

Page 1 - View Numbers of Students for Required Courses

\n}; print qq{

What's a section? A section is a group of students taking a particular course with a teacher. If you have too many students taking grade 10 science to fit in a room or to be effectively taught, you have another teacher teach the same course in another location/time. Each section will have a different unique code made up of the Sasked course code (4 digit) and then a section number (starting from 1 typically) with the 2 separated with a dash. (4017-1). Two sections of ELA A10 would be 4017-1, and 4017-2).

\n}; print qq{
These numbers of students will help you plan for an appropriate number of sections for each course. The next page, page 2, will allow you to select the courses to be offered next year, and how many sections of each course. Change the numbers in the select columns, if necessary to help in your decision making, so you can see those numbers on the next page.
\n}; # find grade 9 pass/fail. my $res = findGrade9Pass(); my ($pass9, $fail9) = split(':', $res); print qq{
}; print qq{Grade 9's with Average >= $passingaverage is $pass9
\n}; # These students will all need the required courses below. print qq{

\n}; # Form Start print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
}; print qq{Change any selected values as necessary for course requirement student counts
\n}; print qq{
\n}; # Grade 10 my %incomplete; # students in upper grades 10-12 with incomplete requirements #(or failing current course). # incomplete{requirement}{grade of student missing this requirement}; # Go through 10, 11, 12 for any missing grade 10 requirements. (skipping electives) my @req10 = ( 'ELA A10', 'ELA B10', 'Science 10', '10 Math', '10 Social' ); my %required10 = map { $_ => 1 } @req10; # Klunky but what the heck... my %adultprogram; my $sth = $dbh->prepare("select studnum, grade, program from student where grade = 10 or grade = 11 or grade = 12"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my ($studnum, $grade, $program) = $sth->fetchrow ) { if ( $program eq '09' ) { $adultprogram{$studnum} = 1; next; } # set for adult program if ( my @inc = @{ $req{$studnum}{10} } ) { foreach my $val ( @inc ) { if ( $required10{ $val } ) { $incomplete{$val}{$grade}++; } } } } # Display the grade 10 requirements my %reqtotal; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; foreach my $req ( @req10 ) { my $reqtotal = $pass9 + $incomplete{$req}{10} + $incomplete{$req}{11} + $incomplete{$req}{12}; print qq{}; print qq{}; print qq{\n}; } print qq{\n}; print qq{
}; print qq{Grade 10 Required Courses
RequirementGrade 9Grade 10Grade 11Grade 12TotalSelect
$req$pass9$incomplete{$req}{10}$incomplete{$req}{11}$incomplete{$req}{12}$reqtotal
}; print qq{Does not include students taking the course and currently passing
\n}; # now calculate the grade 11 requirements for next year; my %incomplete; # incomplete{requirement}{grade of student missing this requirement}; # Go through 10, 11, 12 for any missing grade 10 requirements. (skipping electives) my @req11 = ( 'ELA 20', '11 Science', '11 Math', '11 Social' ); my %required11 = map { $_ => 1 } @req11; my $sth = $dbh->prepare("select studnum, grade from student where grade = 10 or grade = 11 or grade = 12"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my ($studnum, $grade) = $sth->fetchrow ) { if ( $adultprogram{$studnum} ) { next; } # skip adult program if ( my @inc = @{ $req{$studnum}{11} } ) { foreach my $val ( @inc ) { if ( $required11{ $val } ) { $incomplete{$val}{$grade}++; } } } } # Display the grade 11 requirements my %reqtotal; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; foreach my $req ( @req11 ) { my $reqtotal = $incomplete{$req}{10} + $incomplete{$req}{11} + $incomplete{$req}{12}; print qq{}; print qq{}; print qq{\n}; } print qq{\n}; print qq{
}; print qq{Grade 11 Required Courses
RequirementGrade 10Grade 11Grade 12TotalSelect
$req$incomplete{$req}{10}$incomplete{$req}{11}$incomplete{$req}{12}$reqtotal
}; print qq{Does not include students taking the course and currently passing
\n}; print qq{
\n}; # end of grade 11 # grade 12 requirements for next year; my %incomplete; # incomplete{requirement}{grade of student missing this requirement}; # Go through 10, 11, 12 for any missing grade 10 requirements. (skipping electives) my @req12 = ( 'ELA A30', 'ELA B30', '12 Social' ); my %required12 = map { $_ => 1 } @req12; my $sth = $dbh->prepare("select studnum, grade from student where grade = 11 or grade = 12"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my ($studnum, $grade) = $sth->fetchrow ) { if ( $adultprogram{$studnum} ) { next; } # skip adult program if ( my @inc = @{ $req{$studnum}{12} } ) { foreach my $val ( @inc ) { if ( $required12{ $val } ) { $incomplete{$val}{$grade}++; } } } } # Display the grade 12 requirements my %reqtotal; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; foreach my $req ( @req12 ) { my $reqtotal = $incomplete{$req}{11} + $incomplete{$req}{12}; print qq{}; print qq{}; print qq{\n}; } print qq{\n}; print qq{\n}; print qq{
}; print qq{Grade 12 Required Courses
RequirementGrade 11Grade 12TotalSelect
$req$incomplete{$req}{11}$incomplete{$req}{12}$reqtotal
}; print qq{Does not include students taking the course and currently passing
Adult Program Students not counted above - }; print scalar keys %adultprogram; print qq{
\n}; # Grade 12 End # Phys Ed. Requirement my $sth = $dbh->prepare("select studnum, provnum, lastname, firstname, grade from student where grade = ?"); my $sth1 = $dbh->prepare("select courseid from sasked_completedcourses where provnum = ? and creditsearned = '1'"); my %incomplete; foreach my $grade ( '10','11','12' ) { $sth->execute($grade); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my ( $studnum, $provnum, $lastname, $firstname, $grade ) = $sth->fetchrow ) { # Load their completed courses $sth1->execute( $provnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my %completed; while ( my $courseid = $sth1->fetchrow ) { $completed{$courseid} = 1; } # Wellness 10 (4600), Phys Ed 20(6603), Phys Ed 30(8603) if ( not $completed{4600} and not $completed{6603} and not $completed{8603} ) { $incomplete{$grade}++; } } } my %reqtotal; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{}; foreach my $grade ( '10','11','12' ) { $reqtotal += $incomplete{$grade}; print qq{}; } print qq{}; print qq{\n}; print qq{\n}; print qq{
}; print qq{Phys Ed Requirement 10-12
Grade 10Grade 11Grade 12TotalSelect
$incomplete{$grade}$reqtotal
Students without any Phys Ed course }; print qq{( Wellness 10 (4600), Phys Ed 20(6603), Phys Ed 30(8603))
\n}; print qq{
\n}; print qq{
\n}; print qq{
\n}; print qq{\n}; exit; } # end of findCourseRequirementCounts #---------------- sub selectCourses { #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } print qq{

Page 2 - Select Courses and Sections

\n}; print qq{
Use the pull down menus to set the number of sections that you want to offer. Select the courses that you wish to offer with the checkboxes. Required courses are automatically selected.
\n}; # Grade 10 pass count (1-5). my @req10 = ( 'ELA A10', 'ELA B10', 'Science 10', '10 Math', '10 Social' ); my %required10 = map { $_ => 1 } @req10; my ($promote10, $fail10); my $sth = $dbh->prepare("select studnum from student where grade = 10"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my $studnum = $sth->fetchrow ) { my $passcount = 0; my @completed = @{ $req{$studnum}{10} }; foreach my $req ( @completed ) { if ( $required10{$req} ) { $passcount++; } } if ( $passcount >= 4) { $promote10++; } else { $fail10++; } } my $next10 = $arr{pass9} + $fail10; # Start Table print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; #----------------------------------------------------------------- # Grade 11 Numbers. #----------------------------------------------------------------- # Grade 11 pass count (1-4). my @req11 = ( 'ELA 20', '11 Science', '11 Math', '11 Social' ); my %required11 = map { $_ => 1 } @req11; my ($promote11, $fail11); my $sth = $dbh->prepare("select studnum from student where grade = 11"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my $studnum = $sth->fetchrow ) { my $passcount = 0; my @completed = @{ $req{$studnum}{11} }; foreach my $req ( @completed ) { if ( $required11{$req} ) { $passcount++; } } # print qq{SN:$studnum - $passcount
\n}; if ( $passcount >= 3) { $promote11++; } else { $fail11++; } } my $next11 = $promote10 + $fail11; # print qq{
Grade 11 students to promote: $promote11
\n}; # print qq{
Next Year Grade 11 - Gr 10's ($promote10) + }; # print qq{Gr 11's without enough reqs($fail11) = $next11
\n}; print qq{}; print qq{\n}; #----------------------------------------------------------------- # Grade 12 Numbers. #----------------------------------------------------------------- # Grade 12 pass count (1-3). my @req12 = ( 'ELA A30', 'ELA B30', '12 Social' ); my %required12 = map { $_ => 1 } @req12; my ($promote12, $fail12); my $sth = $dbh->prepare("select studnum from student where grade = 12"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my $studnum = $sth->fetchrow ) { my $passcount = 0; my @completed = @{ $req{$studnum}{12} }; foreach my $req ( @completed ) { if ( $required12{$req} ) { $passcount++; } } # print qq{SN:$studnum - $passcount
\n}; if ( $passcount >= 2) { $promote12++; } else { $fail12++; } } my $next12 = $promote11 + $fail12; # print qq{
Grade 12 students to graduate: $promote12 (if sufficient credits)
\n}; # print qq{
Next Year Grade 12 - Gr 11's ($promote11) + }; # print qq{Gr 12's without enough reqs($fail12) = $next12
\n}; print qq{}; print qq{\n}; # The values of $next10, $next11, and $next12 are the numbers for next year. my $nextyeartotal = $next10 + $next11 + $next12; my $curryeartotal = $promote10 + $fail10 + $promote11 + $fail11 + $promote12 + $fail12; print qq{}; print qq{\n}; print qq{\n}; print qq{
Next Year Student Numbers
Current
Grade		PassedIncompleteNext Year
Students
9$arr{pass9}
10$promote10$fail10$next10
11$promote11$fail11$next11
12$promote12$fail12$next12
Next Year Grade 10-12 Students$nextyeartotal
Current Year Grade 10-12 Students$curryeartotal
\n}; print qq{
Incomplete students have more than 1 required area of study missing; Electives ignored
\n}; # Form Start print qq{
\n}; print qq{\n}; # print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; # hidden values for required areas of study 10-12 print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; # Grade 10 Course Selection Area # --------------------------------- # print qq{
\n}; print qq{

Grade 10 - $next10 Students

\n}; print qq{
\n}; my @req10code = qw(4017 4018 4214 4424 4423 4307 4306 4309); my %req10code = map {$_ => 1} @req10code; # Current 10 Courses. my (%elective10, %course10, %sort); # elective 10 will have all courses to start and then have req stripped below my $sth1 =$dbh->prepare("select title, grade, ctype from sasked_courses where code = ?"); my $sth = $dbh->prepare("select distinct subjcode from subject where grade = 10"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my $subjcode = $sth->fetchrow ) { $sth1->execute($subjcode); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my ($title, $grade, $ctype) = $sth1->fetchrow; $elective10{$subjcode} = 1; $sort{"$saskedname{$subjcode}$subjcode"} = $subjcode; if ( $grade != 10 ) { print qq{
Error for course $subjcode, $title - not grade 10!
\n};} $course10{$subjcode} = $title; $ctype10{$subjcode} = $ctype; } print qq{\n}; print qq{\n}; print qq{\n}; my $count = 1; foreach my $key ( sort keys %sort ) { $subjcode = $sort{$key}; my $category = 'Elective'; if ( $req10code{$subjcode} ) { $category = qq{Required}; } print qq{}; print qq{\n}; $count++; } print qq{
Current Grade 10 Courses
TitleCodeTypeCategory
$count. $course10{$subjcode}$subjcode$ctype10{$subjcode}$category
\n}; # Required Areas of Study. print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{}; print qq{}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
Required Areas of Study 10
TitleCodeStud
Count		Sections
1. English Language Arts A104017$arr{'ELA A10'}
2. English Language Arts B104018$arr{'ELA B10'}
3. Science 104214$arr{'Science 10'}
4. Math 10#Stud$arr{'10 Math'}
Math: Foundations and Pre-calculus 104424
Math: Workplace and Apprenticeship 104423
5. Social 10#Stud$arr{'10 Social'}
Social Studies 104307
History 104306
Native Studies4309
\n}; # strip %elective10 and then display electives. foreach my $code ( keys %elective10 ) { if ( $req10code{$code} ) { delete $elective10{$code}; } } my %sort; foreach my $code ( keys %elective10 ) { $sort{"$saskedname{$code}$code"} = $code; } # Electives Offered. print qq{\n}; print qq{\n}; print qq{\n}; foreach my $key ( sort keys %sort ) { my $subjcode = $sort{$key}; print qq{}; print qq{\n}; print qq{\n}; } print qq{\n}; print qq{}; print qq{\n}; print qq{
Grade 10 Electives
TitleCodeSelectSections
$saskedname{$subjcode}$subjcode
Additional Elective Courses }; print qq{
Separate Course Codes with spaces \n}; print qq{[ View Course Codes in New Tab ]
\n}; print qq{
\n}; # Grade 11 Course Selection Area # --------------------------------- # print qq{
\n}; print qq{

Grade 11 - $next11 Students

\n}; print qq{
\n}; my @req11code = qw(6017 6245 6246 6247 6702 6212 6213 6423 6425 6426); # ELA 20, a Science, a Math my %req11code = map {$_ => 1} @req11code; # foreach my $code ( sort keys %req11code ) { # print "$saskedname{$code} - $code
\n"; # } # Current 11 Courses. my (%elective11, %course11, %sort); # elective 11 will have all courses to start and then have req stripped below my $sth1 =$dbh->prepare("select title, grade, ctype from sasked_courses where code = ?"); my $sth = $dbh->prepare("select distinct subjcode from subject where grade = 11"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my $subjcode = $sth->fetchrow ) { $sth1->execute($subjcode); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my ($title, $grade, $ctype) = $sth1->fetchrow; $elective11{$subjcode} = 1; $sort{"$saskedname{$subjcode}$subjcode"} = $subjcode; if ( $grade != 11 ) { print qq{
Error for course $subjcode, $title - not grade 11!
\n};} $course11{$subjcode} = $title; $ctype11{$subjcode} = $ctype; } print qq{\n}; print qq{\n}; print qq{\n}; my $count = 1; foreach my $key ( sort keys %sort ) { $subjcode = $sort{$key}; my $category = 'Elective'; if ( $req11code{$subjcode} ) { $category = qq{Required}; } print qq{}; print qq{\n}; $count++; } print qq{
Current Grade 11 Courses
TitleCodeTypeCategory
$count. $course11{$subjcode}$subjcode$ctype11{$subjcode}$category
\n}; # Required Areas of Study. print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
Required Areas of Study 11
TitleCodeStud
Count		Sections
1. English Language Arts 206017$arr{'ELA 20'}
2. Math 11#Stud$arr{'11 Math'}
Math: Workplace and Apprenticeship 206423
Math: Foundations 206425
Math: Pre-calculus 206426
3. Science 11#Stud$arr{'11 Science'}
Chemistry 206212
Physics 206213
Health Science 206245
Environmental Science 206246
Physical Science 206247
Computer Science 206702
Required Area Single Courses are automatically selected
\n}; # strip %elective11 and then display electives. foreach my $code ( keys %elective11 ) { if ( $req11code{$code} ) { delete $elective11{$code}; } } my %sort; foreach my $code ( keys %elective11 ) { $sort{"$saskedname{$code}$code"} = $code; } # Electives Offered. print qq{\n}; print qq{\n}; print qq{\n}; foreach my $key ( sort keys %sort ) { my $subjcode = $sort{$key}; print qq{}; print qq{\n}; print qq{\n}; } print qq{\n}; print qq{\n}; print qq{
Grade 11 Electives
TitleCodeSelectSections
$saskedname{$subjcode}$subjcode
Additional Elective Courses }; print qq{
Separate Course Codes with spaces \n}; print qq{[ }; print qq{View Course Codes in New Tab ]
\n}; print qq{
\n}; # Grade 12 Course Selection Area # --------------------------------- # print qq{
\n}; print qq{

Grade 12 - $next12 Students

\n}; print qq{
\n}; my @req12code = qw(8017 8018 8306 8321 8307); my %req12code = map {$_ => 1} @req12code; # foreach my $code ( sort keys %req12code ) { # print "$saskedname{$code} - $code
\n"; # } # Current 12 Courses. my (%elective12, %course12, %sort); # elective 12 will have all courses to start and then have req stripped below my $sth1 =$dbh->prepare("select title, grade, ctype from sasked_courses where code = ?"); my $sth = $dbh->prepare("select distinct subjcode from subject where grade = 12"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my $subjcode = $sth->fetchrow ) { $sth1->execute($subjcode); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my ($title, $grade, $ctype) = $sth1->fetchrow; $elective12{$subjcode} = 1; $sort{"$saskedname{$subjcode}$subjcode"} = $subjcode; if ( $grade != 12 ) { print qq{
Error for course $subjcode, $title - not grade 12!
\n}; } $course12{$subjcode} = $title; $ctype12{$subjcode} = $ctype; } print qq{\n}; print qq{\n}; print qq{\n}; my $count = 1; foreach my $key ( sort keys %sort ) { $subjcode = $sort{$key}; my $category = 'Elective'; if ( $req12code{$subjcode} ) { $category = qq{Required}; } print qq{}; print qq{\n}; $count++; } print qq{
Current Grade 12 Courses
TitleCodeTypeCategory
$count. $course12{$subjcode}$subjcode$ctype12{$subjcode}$category
\n}; # Required Areas of Study. print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; foreach my $code ('8306','8307','8321') { print qq{}; print qq{\n}; print qq{\n}; } print qq{\n}; print qq{
Required Areas of Study 12
TitleCodeStud
Count		Sections
1. English Language Arts A 308017$arr{'ELA A30'}
2. English Language Arts B 308018$arr{'ELA B30'}
3. Social 12#Stud$arr{'12 Social'}
$saskedname{$code}$code
Required Area Single Courses are automatically selected
\n}; # strip %elective12 and then display electives. foreach my $code ( keys %elective12 ) { if ( $req12code{$code} ) { delete $elective12{$code}; } } my %sort; foreach my $code ( keys %elective12 ) { $sort{"$saskedname{$code}$code"} = $code; } # Electives Offered. print qq{\n}; print qq{\n}; print qq{\n}; foreach my $key ( sort keys %sort ) { my $subjcode = $sort{$key}; print qq{}; print qq{\n}; print qq{\n}; } print qq{\n}; print qq{\n}; print qq{
Grade 12 Electives
TitleCodeSelectSections
$saskedname{$subjcode}$subjcode
Additional Elective Courses }; print qq{
Separate Course Codes with spaces \n}; print qq{[ }; print qq{View Course Codes in New Tab ]
\n}; # end of 12 area print qq{
\n}; print qq{
\n}; print qq{

What's A Semester? A Semester is the normal length of a course. In a typical 4 term, 2 semester school, there are 2 terms (2 report cards) per semester (September-January, February-June).

\n}; print qq{

A Term is a period of time with a report card at the end. In other schools with a trimester system there could be 6 terms and 2 terms per semester. This system could also have just 3 terms (ie. 3 report cards) as is typical of K-9 schools.

\n}; # Update Table? print qq{
}; print qq{Update PrePlan Course Master? }; print qq{
\n}; print qq{(delete existing courses and insert these new courses instead)}; print qq{
\n}; print qq{
\n}; print qq{
\n}; print qq{\n}; exit; } # end of selectCourses #------------- sub getCredits { #------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my ($provnum, $dbh) = @_; # passed provincial number, dbh # Load their completed courses my $sth = $dbh->prepare("select courseid from sasked_completedcourses where provnum = $provnum and creditsearned = '1'"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my %completed; while ( my $courseid = $sth->fetchrow ) { $completed{$courseid} = $saskedgrade{$courseid}; } return \%completed; } # end of getCredits #---------------- sub checkCredit10 { # check grade 10 completion list; return any errors (missing requirements) #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my ($provnum, $dbh, $studnum) = @_; # passed provincial number, dbh, localnumber. # Load their completed courses my $sth = $dbh->prepare("select courseid from sasked_completedcourses where provnum = $provnum and creditsearned = '1'"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my %completed; while ( my $courseid = $sth->fetchrow ) { $completed{$courseid} = 1; } # Load courses in progress, and passing. # my %inprogress; my $sth = $dbh->prepare("select subjcode, a1 from eval where studnum = ?"); $sth->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my ($subjcode, $a1) = $sth->fetchrow ) { $subjcode =~ s/-.*//g; if ( $a1 >= 50 ) { # assume they will complete; # and not $completed{$subjcode} ) { $completed{$subjcode} = 1; } } my @incomplete; # return text based codes my @ignore = qw(4017 4018 4214 4424 4423 4307 4306 4309); # courses required to skip in finding electives. my %ignore = map { $_ => 1 } @ignore; # Test. # foreach my $key ( sort keys %ignore ) { # print qq{K:$key V:$ignore{$key}
\n}; # } # Go through the completion requirements; # 1. ELA A10 (4017) if ( not $completed{4017} ) { push @incomplete, 'ELA A10'; } # 2. ELA B10 (4018) if ( not $completed{4018} ) { push @incomplete, 'ELA B10'; } # 3. Science 10 (4214) if ( not $completed{4214} ) { push @incomplete, 'Science 10'; } # 4. A Math (4424 (Foundations), or 4423 (Workplace)) if ( not $completed{4424} and not $completed{4423} ) { # haven't done either math push @incomplete, '10 Math'; } # 5. A Social Science SS10 (4307), Hist10 (4306), Native Stud 10 (4309) if ( not $completed{4307} and not $completed{4306} and not $completed{4309} ) { # haven't done any social push @incomplete, '10 Social'; } # 6. 3 more electives at 10 level or higher. # Loop over completed courses my @electives; foreach my $courseid ( keys %completed ) { my $grade = $saskedgrade{$courseid}; if ( $ignore{$courseid} ) { next; } # these don't count, done above. if ( $grade < 10 ) { next; } # lower grade level push @electives, $courseid; } if ( @electives < 3 ) { # error push @incomplete, '10 Electives'; } return @incomplete; } # end of checkCredit10 #---------------- sub checkCredit11 { # check grade 11 completion list; return any errors (missing requirements) #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my ($provnum,$dbh) = @_; # Load their completed courses my $sth = $dbh->prepare("select courseid from sasked_completedcourses where provnum = $provnum and creditsearned = '1'"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my %completed; # print qq{Credit 11:$provnum
\n}; while ( my $courseid = $sth->fetchrow ) { # print "$saskedname{$courseid} ($courseid)/ "; $completed{$courseid} = 1; } my @incomplete; # return text based codes my %ignore; # codes to ignore (since credit already given) # Go through the completion requirements; # 1. ELA 20 (6017) if ( not $completed{6017} ) { push @incomplete, 'ELA 20'; } else { $ignore{6017} = 1; } # 2. Sciences ( Enviro 6246, Physical 6247, Health 6245, Computer # 6702 ) (#physics 20 (6213) and chem 20 (6212) no longer allowed my $incompleteflag = 1; # assume incomplete unless we find a completed course; foreach my $cid ( qw(6245 6246 6247 6702 6212 6213)) { if ( $completed{$cid} ) { $incompleteflag = 0; $ignore{$cid} = 1; # add this course to the ignore list, # since we get a credit for this, can't be elective. } } if ( $incompleteflag ) { push @incomplete, '11 Science'; } # 3. A Math ( 6423 Workplace, 6425 Foundations, 6426 Pre-Calculus ) my $incompleteflag = 1; # assume incomplete unless we find a completed course; foreach my $cid (qw(6423 6425 6426)) { if ( $completed{$cid} ) { $incompleteflag = 0; $ignore{$cid} = 1; # add this course to the ignore list, # since we get a credit for this, can't be elective. } } if ( $incompleteflag ) { push @incomplete, '11 Math'; } # 4. A Social Science (big list) my $incompleteflag = 1; # assume incomplete unless we find a completed course; foreach my $cid ( qw(6306 6309 6307 2140 2150 6340 8306 8321 8307 8339 3140 3150 8340 )) { if ( $completed{$cid} ) { $incompleteflag = 0; $ignore{$cid} = 1; # add this course to the ignore list, can't be elective. } } if ( $incompleteflag ) { push @incomplete, '11 Social'; } # 5. 3 more electives at 11 level or higher. # Loop over completed courses my @electives; foreach my $courseid ( keys %completed ) { my $grade = $saskedgrade{$courseid}; # print "Course:$courseid Grade:$grade
\n"; if ( $ignore{$courseid} ) { next; } # these don't count, done above. if ( $grade < 11 ) { next; } # lower grade level push @electives, $courseid; } if ( @electives < 3 ) { # error push @incomplete, '11 Electives'; } return @incomplete; } # end of checkCredit11 #---------------- sub checkCredit12 { # check grade 12 completion list; return any errors (missing requirements) #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my ($provnum,$dbh) = @_; # Load their completed courses my $sth = $dbh->prepare("select courseid from sasked_completedcourses where provnum = $provnum and creditsearned = '1'"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my %completed; # print qq{Credit 11:$provnum
\n}; while ( my $courseid = $sth->fetchrow ) { # print "$saskedname{$courseid} ($courseid)/ "; $completed{$courseid} = 1; } my @incomplete; # return text based codes my %ignore; # codes to ignore (since credit already given) # Go through the completion requirements; # 1. ELA A30 if ( not $completed{8017} ) { push @incomplete, 'ELA A30'; } else { $ignore{8017}; } # 2. ELA B30 if ( not $completed{8018} ) { push @incomplete, 'ELA B30'; } else { $ignore{8018}; } # 4. A Social Science my ($cdnflag, $g10flag, $g1112flag); # flags to show if done both gr 10 credit and 11/12 credit for SS courses. foreach my $cid ( qw(8306 8321 8307)) { if ( $completed{$cid} ) { $cdnflag = 1; $ignore{$cid} = 1; # add this course to the ignore list, can get a credit for this, can't be elective. } } # Now check the rest foreach my $cid ( qw(4306 4307 4309 6306 6307 6309 2140 2150 6340 8339 3140 3150 8340 )) { if ( $completed{$cid} ) { if ( $saskedgrade{$cid} == 10 ) { $g10flag = 1; } if ( $saskedgrade{$cid} == 11 or $saskedgrade{$cid} == 12 ) { $g1112flag = 1; } $ignore{$cid} = 1; } } if ( not $cdnflag or not $g10flag or not $g1112flag ) { push @incomplete, '12 Social'; } # 5. Electives: 6 more electives at 11 level or higher. # Loop over completed courses my @electives; foreach my $courseid ( keys %completed ) { my $grade = $saskedgrade{$courseid}; if ( $ignore{$courseid} ) { next; } # these don't count, done above. if ( $grade < 11 ) { next; } # lower grade level push @electives, $courseid; } if ( @electives < 6 ) { # error push @incomplete, '12 Electives'; } return @incomplete; } # end of checkCredit12 #----------------- sub findGrade9Pass { #----------------- use Number::Format qw(:all); my $sth1 = $dbh->prepare("select distinct subjcode from eval where studnum = ?"); my $sth2 = $dbh->prepare("select term, a1 from eval where studnum = ? and subjcode = ? order by term desc"); # Get students in grade 9, then check their marks. my $sth = $dbh->prepare("select studnum from student where grade = 9"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my ($fail, $pass); my @marks; while ( my $studnum = $sth->fetchrow ) { # Get Courses. $sth1->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} COURSE: while ( my $subjsec = $sth1->fetchrow ) { # Get Marks. $sth2->execute( $studnum, $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my ($term,$mark) = $sth2->fetchrow ) { if ( not $mark ) { # print "No Mark - STUD:$studnum CRSE:$subjsec Term $term
\n"; next; } push @marks, $mark; next COURSE; } } # Average for this student my ($sum,$count,$avg); foreach my $val ( @marks ) { $sum += $val; $count++; } if ( $count ) { $avg = round($sum / $count, 1); } if ( $avg >= $passingaverage ) { $pass++; } else { $fail++; } # print qq{
SN:$studnum AVG:$avg
\n}; } # end of student loop # print qq{
Pass:$pass Fail:$fail
\n}; return "$pass:$fail"; } # end of findGrade9Pass #----------------- sub compressCourse { # full data in; non-destructive; only keys back out. #----------------- my $cref = shift; # passed a reference to %courses; my %crs = %$cref; my %courses = %crs; # create a new hash. my (%back,%newkey ); foreach my $subjsec ( keys %courses ) { if ( $courses{$subjsec}->{backedwith} ) { my ($crsA, $crsB) = ($subjsec, $courses{$subjsec}->{backedwith} ); # check for any values in %back; my $first = 1; foreach my $key (keys %back) { # key format is: subjsec/subjsec/subjsec, etc. my @crs = split('/', $key); foreach my $cr ( @crs ) { if ( $cr eq $crsA or $cr eq $crsB ) { # we have to delete the existing key and add a new larger key # put 2 backed courses together with this group. my %newkey = map { $_ => 1 } @crs; $newkey{$crsA} = 1; $newkey{$crsB} = 1; # all subjsec values now are keys my $newkey = join('/', keys %newkey); $first = 0; # out with old, in with new delete $back{$key}; $back{$newkey} = 1; last; } } } if ( $first ) { # no records found in %back; my $key = join('/', ($crsA,$crsB)); $back{$key} = 1; } } } # end of course loop # Display %back # print qq{Backings Hash\n}; # foreach my $key ( sort keys %back) { # print qq{K:$key
\n}; # } # Remove the single course values found in %back compound keys foreach my $key ( sort keys %back) { my @crs = split('/', $key); foreach my $subjsec ( @crs ) { delete $courses{$subjsec}; } } # Add compound keys into %courses foreach my $key ( sort keys %back) { $courses{$key} = 1; } # Clean up rest of hash values (set to 1) foreach my $key ( sort keys %courses) { $courses{$key} = 1; } return \%courses; # Note: the values of the hash are useless. } # end of compressCourse (join backings) #--------------------- sub showTeacherSummary { # summary table of teacher loading. #--------------------- # Load staff into hash; not restricted to classroom teachers. my (%teachers, %teachername); my $sth = $dbh->prepare("select lastname, firstname, userid from staff order by lastname, firstname"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my ( $lastname, $firstname, $userid) = $sth->fetchrow ) { $teachers{"$lastname, $firstname ($userid)"} = $userid; $teachername{$userid} = "$lastname, $firstname"; } # Load all course data my (%courses, %data); my $sth = $dbh->prepare("select * from preplan_coursemaster"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; my $subjsec = $r{subjsec}; $data{$subjsec} = $ref; } # Compress Course List my $ref = compressCourse( \%data ); # %data not affected. %courses = %$ref; # Get Periods, Semester my $sth1 = $dbh->prepare("select semester, periods from preplan_courseperiods where subjsec = ?"); my %periods; foreach my $subjsec ( keys %courses ) { my @temp; if ( $subjsec =~ m/\// ) { # compound entry (backing) @temp = split('/', $subjsec); } else { push @temp, $subjsec; # only a single subjsec; } my %teacher; # track if more than one entry for the same teacher in compound course list foreach my $subjsec ( @temp ) { my %r = %{ $data{$subjsec} }; if ( $teacher{ $r{teacher} } ) { next; } # only one period update per teacher. $sth1->execute( $r{subjsec} ); while ( my ($semester, $periods ) = $sth1->fetchrow ) { $periods{ $r{teacher} }{ $r{semester} } += $periods; } $teacher{ $r{teacher} } = 1; # tracking for how many periods added. } } # end of adding periods to %periods hash # Get Number of Semesters my $sth = $dbh->prepare("select max(semester) from preplan_coursemaster where semester != 'allyear'"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $semestercount = $sth->fetchrow; # Get TEACHERS in the preplan coursemaster table my %sort; my $sth2 = $dbh->prepare("select distinct teacher from preplan_coursemaster where teacher is not NULL"); $sth2->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $teacher = $sth2->fetchrow ) { # print qq{
Teacher:$teacher
\n}; $sort{"$teachername{$teacher}$teacher"} = $teacher; } my $first = 1; # Loop through all teachers. foreach my $key ( sort keys %sort ) { my $teacher = $sort{$key}; # Start Table if ( $first ) { print qq{\n}; print qq{}; foreach my $semester (1..$semestercount) { print qq{}; } print qq{\n}; $first = 0; } print qq{\n}; foreach my $semester (1..$semestercount) { print qq{}; } # end of semester loop print qq{\n}; } # end of teacher loop print qq{
TeacherSemester $semester
$teachername{$teacher}$periods{$teacher}{$semester}
\n}; return; }