#! /usr/bin/perl # Copyright 2001-2022 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. my %lex = ('Main' => 'Main', 'View' => 'View', 'Download' => 'Download', 'Custom Courselist' => 'Custom Courselist', 'View Log File' => 'View Log File', 'Continue' => 'Continue', 'Homeroom' => 'Homeroom', 'Grade' => 'Grade', 'Cell Width' => 'Cell Width', 'Max Students/Page' => 'Max Students/Page', 'Select' => 'Select', 'Repeat Entry Elements' => 'Repeat Entry Elements', 'Students' => 'Students', 'Paper Size' => 'Paper Size', 'Letter' => 'Letter', 'Legal' => 'Legal', 'A4' => 'A4', 'Font Size' => 'Font Size', 'Gray Shade' => 'Gray Shade', 'Smaller=Darker' => 'Smaller=Darker', 'Additional Field' => 'Additional Field', 'Error' => 'Error', 'Or' => 'Or', 'Teacher' => 'Teacher', 'Terms' => 'Terms', 'No Courses Found' => 'No Courses Found', 'Course' => 'Course', 'Check' => 'Check', 'Next Page' => 'Next Page', 'No Enrollments' => 'No Enrollments', ); my $self = 'rptcustomcourselist.pl'; my %disallow = qw(studid 1 medical 1 owing 1 studnum 1 entry 1); use DBI; use CGI; use Cwd; # Configurable settings my $maxstudents = 28; # maximum students per page. my $cellwidth = 8; # default width of columns, 8 mm; my $extrafieldwidth = 18; # studnum and extra field in mm. my $defaultgrayshade = '0.95'; # shading for alternate rows my $entrylimit = 31; # Max entries allowed for rotated text. my $maxentrysize = 16; # Maximum number of characters for the entry my $namewidth = 50; # Width of the first 'Name' column in mm. eval require "../etc/admin.conf"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } eval require "../lib/liblatex.pl"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; if ( $arr{extrafieldwidth} ) { $extrafieldwidth = $arr{extrafieldwidth}; } my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $iddst) = localtime(time); $year = $year + 1900; $wday++; $mon++; my $currdate = "$dow[$wday], $month[$mon] $mday, $year"; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; # Get current dir so know what CSS to display; if (getcwd() =~ /tcgi/){ # we are in tcgi $css = $tchcss; $homepage = $tchpage; } print qq{$doctype\n$lex{'Custom Courselist'}\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{
[ $lex{Main} ]
\n}; print qq{

$lex{'Custom Courselist'}

\n}; # Get values for Customization, then exit; # Values passed are width, maxstudents, repeat, entry1 to entry8 if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; selectCourses(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; printReport(); } #-------------- sub printReport { #-------------- #foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # Setup Extra field my ( $extrafield, $extrafieldName ); if ( $arr{extrafield} ) { $extrafield = $arr{extrafield}; my $sth = $dbh->prepare("select fieldname from meta where tableid = 'student' and fieldid = ?"); $sth->execute( $arr{extrafield} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } $extrafieldName = $sth->fetchrow; ( $extrafieldName ) = latex_filter( $extrafieldName ); delete $arr{extrafield}; } # Set Sizes # The tabcolsep of 1mm leaves 128 mm where each col is $cellwidth + 1mm wide. my $fontsize = $arr{fontsize}. 'pt'; my ( $papersize, $textwidth, $textheight ); if ( $arr{papersize} eq $lex{Letter} ) { $papersize = 'letterpaper'; $textwidth = $g_letterpaper_textwidth; $textheight = $g_letterpaper_textheight; } elsif ( $arr{papersize} eq $lex{Legal} ) { $papersize = 'legalpaper'; $textwidth = $g_legalpaper_textwidth; $textheight = $g_legalpaper_textheight; } elsif ( $arr{papersize} eq $lex{A4} ) { $papersize = 'a4paper'; $textwidth = $g_a4paper_textwidth; $textheight = $g_a4paper_textheight; } delete $arr{papersize}; # no longer needed. my $paperwidth; # stores numeric width in mm (w/o 'mm') if ( $textwidth =~ m/(\d+)/ ) { $paperwidth = $1; } # Restrict Gray Shading values if ( $arr{grayshade} > 1 ) { $arr{grayshade} = 1; } if ( $arr{grayshade} < 0.5 ) { $arr{grayshade} = 0.5; } my $cellwidth = $arr{cellwidth} - 1; # width of each col desired (in mm) # take away 1 for 1mm tabcolsep. # Set column count after shrinking available size my $availablewidth = $paperwidth - $namewidth; if ( $extrafield ) { $availablewidth -= $extrafieldwidth; } # print qq{Paper:$paperwidth Available:$availablewidth Width:$arr{cellwidth}
\n}; my $colcount = $availablewidth / $arr{cellwidth}; $colcount = int $colcount + 1; # truncate # Filter for Passed values that might crash LaTeX foreach my $key ( keys %arr ) { ( $arr{$key} ) = latex_filter( $arr{$key} ); } my $maxlines; if ( $arr{maxstudents} ) { $maxlines = $arr{maxstudents}; } else { $maxlines = $maxstudents; } my $shortname = "courselist$$"; my $filename = "$shortname.tex"; open(TEX,">$filename") || die "Can't open tex file"; print TEX "\\documentclass[$fontsize,$papersize ]{article} \\usepackage{array,newcent,rotating,colortbl,inputenc} $a_latex_header \\pagestyle{empty} \\setlength{\\textwidth}{$textwidth} \\setlength{\\textheight}{$textheight} \\setlength{\\hoffset}{-24mm} \\setlength{\\voffset}{-36mm} \\setlength{\\evensidemargin}{0mm} \\setlength{\\oddsidemargin}{0mm} \\setlength{\\tabcolsep}{0.5mm} \\setlength{\\extrarowheight}{2mm} \\newcolumntype{G}{>{\\columncolor[gray]{1.00}}p{$namewidth mm}}\n"; # Note above... a columncolor of 1.00 means white, smaller number is darker. print TEX "\\begin{document}\n"; if ( $extrafield ) { $extrafield = ', s.'. $extrafield; } my $sth = $dbh->prepare("select distinct s.studnum, s.lastname, s.firstname $extrafield from eval e, student s where s.studnum = e.studnum and subjcode = ? order by s.lastname, s.firstname"); my $sth1 = $dbh->prepare("select * from subject where subjsec = ?"); my $sth2 = $dbh->prepare("select lastname, firstname from staff where userid = ?"); my $sth3 = $dbh->prepare("select teacher, description from subject where subjsec = ?"); my (%coursesort, %teachername ); foreach my $key ( sort keys %arr ) { my ( $cc, $subjsec ) = split(':', $key); if ( $cc ne 'CC' ) { next; } $sth3->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($userid,$description) = $sth3->fetchrow; # Get Teacher, then his/her name $sth2->execute( $userid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname ) = $sth2->fetchrow; $coursesort{"$lastname$firstname$description$subjsec"} = $subjsec; $teachername{$userid} = qq{$lastname, $firstname}; } # OLD #foreach my $key ( sort keys %arr ) { # my ( $cc, $subjsec ) = split(':', $key); # if ( $cc ne 'CC' ) { next; } foreach my $key ( sort keys %coursesort ) { my $subjsec = $coursesort{$key}; # Course fields $sth1->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $subjref = $sth1->fetchrow_hashref; # Teacher name #$sth2->execute( $subjref->{teacher} ); #if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } #my ($lastname, $firstname ) = $sth2->fetchrow; my $teachername = $teachername{$subjref->{teacher}}; $sth->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } $rows = $sth->rows; if ( $rows < 1 ) { print qq{

$lex{'No Enrollments'} - $subjsec

\n}; next; } # next subject prHeader( $colcount, $cellwidth, $subjsec, $extrafieldName, $teachername ); my $studcount; my $linecount; while ( my ( $studnum, $lastname, $firstname, $efield ) = $sth->fetchrow ) { if ($linecount >= $maxlines) { # We'll print a new page header here # New Page Header $linecount = 0; print TEX "\\end{tabular}\\\\ \\newpage\n"; prHeader( $colcount, $cellwidth, $subjsec, $extrafieldName, $teachername ); } # Print Normal Record. $studcount++; $linecount++; prRecord($lastname, $firstname, $studnum, $colcount, $efield ); } # student loop for this subject prStudCount($studcount, $colcount, $extrafield ); print TEX "\n\\end{tabular} \n\n \\newpage \n"; } # End of Loop for all subjects print TEX "\n\\end{document}\n"; close TEX; # Solve download location issues with cgi vs tcgi.. # Get current dir so know what CSS to display; if (getcwd() =~ /tcgi/){ # we are in tcgi $downloaddir = $tchdownloaddir; $webdownloaddir = $tchwebdownloaddir; } system("$pdflatex $filename >pdflog$$.txt"); system("mv $shortname.pdf $downloaddir"); system("mv pdflog$$.txt $downloaddir"); system("rm -f $shortname.*"); print qq{

}; print qq{$lex{View}/$lex{Download} $lex{'Custom Courselist'}

\n}; print qq{

[ Main |\n}; print qq{$lex{'View Log File'} ]

\n}; print qq{\n}; exit; } #---------------- sub selectCourses { # select courses to print #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my @courses; if ( $arr{groupvalue} ) { # find the courses of these students my $group; if ( $arr{grouptype} eq $lex{Grade} ) { $group = 'grade'; } else { $group = 'homeroom'; } my $sth = $dbh->prepare("select distinct e.subjcode from eval AS e, studentall AS s where e.studnum = s.studnum and s.$group = ? order by e.subjcode"); $sth->execute( $arr{groupvalue} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $subjsec = $sth->fetchrow ) { push @courses, $subjsec; } } elsif ( $arr{teacher} ) { my $sth = $dbh->prepare("select subjsec from subject where subjsec != '' and subjsec is not NULL and teacher = ? order by subjsec"); $sth->execute( $arr{teacher} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $subjsec = $sth->fetchrow ) { push @courses, $subjsec; } } elsif ( $arr{terms} ) { my ( $startterm, $endterm ) = split(/:/, $arr{terms} ); my $sth = $dbh->prepare("select subjsec from subject where startrptperiod = ? and endrptperiod = ? and subjsec != '' order by subjsec"); $sth->execute( $startterm, $endterm ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $subjsec = $sth->fetchrow ) { push @courses, $subjsec; } } # clean up these selectors delete $arr{groupvalue}; delete $arr{grouptype}; delete $arr{teacher}; delete $arr{terms}; #foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } if ( not @courses ) { print qq{

$lex{'No Courses Found'}

\n}; print qq{\n}; exit; } # Items Checked? my $check; if ( $arr{check} ) { $check = q{checked = "checked"}; delete $arr{check}; } # now start form and show subjects for selection. print qq{
\n}; print qq{\n}; print qq{
\n}; # pass hidden values to next page foreach my $key ( keys %arr ) { print qq{\n}; } # Start Table print qq{\n}; print qq{}; my $sth = $dbh->prepare("select description, startrptperiod, endrptperiod, teacher, grade from subject where subjsec = ?"); my (%courses, %sortcourse); foreach my $subjsec ( @courses ) { # Get Course Information $sth->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } # my ( $description, $startterm, $endterm, $teacher, $grade ) = $sth->fetchrow_hashref; my $ref = $sth->fetchrow_hashref; $courses{$subjsec} = $ref; $sortcourse{"$ref->{description}$subjsec"} = $subjsec; } foreach my $key ( sort keys %sortcourse ) { my $subjsec = $sortcourse{$key}; my %c = %{ $courses{$subjsec}}; print qq{}; print qq{\n}; print qq{\n}; } print qq{
$lex{Course}$lex{Terms}$lex{Grade}
}; print qq{$c{description} ($subjsec)$c{startrptperiod} - $c{endrptperiod}$c{grade}
\n}; print qq{
\n}; print qq{
\n}; print qq{\n}; exit; } #---------------- sub showStartPage { # Entry Values for Custom Script #---------------- # Read student fields, and defaults into a @fields and %fieldname hash. my $sth = $dbh->prepare("select fieldid, fieldname from meta where tableid = 'student' order by fieldname"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my @fields; my %fieldnames; while ( ( my $fieldid, $fieldname ) = $sth->fetchrow ) { if ( $disallow{$fieldid} ) { next; } $fieldname =~ s/\(//g; $fieldname =~ s/\)//g; # strip parentheses push @fields, $fieldid; $fieldname{ $fieldid } = $fieldname; } # Get Term Patterns. @termpattern; my $sth1 = $dbh->prepare("select distinct startrptperiod, endrptperiod from subject"); $sth1->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ($start, $end) = $sth1->fetchrow ) { push @termpattern, "$start:$end"; } @termpattern = sort {$a <=> $b} @termpattern; # Get staff teaching subjects. my %teachers; my $sth2 = $dbh->prepare("select distinct e.subjcode, s.teacher from eval e, subject s where s.subjsec = e.subjcode"); $sth2->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ($subjsec, $userid) = $sth2->fetchrow ) { if ( not $userid ) { next; } $teachers{$userid} = 1; } # Now fill in the %teachers hash with names; my %sorted; # sorted{Name} = $userid; my $sth3 = $dbh->prepare("select lastname, firstname from staff where userid = ?"); foreach my $userid ( keys %teachers ) { $sth3->execute( $userid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname ) = $sth3->fetchrow; $sorted{"$lastname$firstname$userid"} = $userid; $teachers{$userid} = "$lastname, $firstname"; } print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; # Select Students print qq{\n}; # Spacer print qq{\n}; # Select Teachers print qq{\n}; # Spacer print qq{\n}; # Select Term Pattern print qq{\n}; # Check Next Page print qq{\n}; # spacer print qq{\n}; # Cell Width print qq{\n}; print qq{\n}; # Font size print qq{\n}; # Gray Shading print qq{\n}; print qq{\n}; # Paper Size print qq{}; print qq{\n}; # Extra Field (optional) print qq{\n}; print qq{\n}; # Extra Field Width print qq{\n}; print qq{\n}; # Max Students / Page. print qq{\n\n}; print qq{\n}; print qq{
$lex{Select} $lex{Students}}; print qq{\n}; print qq{
$lex{Or}
$lex{Select} $lex{Teacher}}; print qq{
$lex{Or}
$lex{Select} $lex{Terms}}; print qq{
$lex{Check} $lex{'Next Page'}}; print qq{

$lex{'Cell Width'} mm. Skinnier cells = More Cells up to 31 max (4.5mm)
$lex{'Font Size'}\n}; print qq{
$lex{'Gray Shade'}}; print qq{ (0-1) $lex{'Smaller=Darker'}
$lex{'Paper Size'}
$lex{'Additional Field'}
$lex{'Additional Field'} Width mm
$lex{'Max Students/Page'}}; print qq{}; print qq{
\n}; print qq{\n}; # Repeat? print qq{\n\n}; for ( 1..$entrylimit ) { my $entrytxt = "entry$_"; print qq{\n}; print qq{}; print qq{\n}; } print qq{\n}; print qq{
$lex{'Repeat Entry Elements'}}; print qq{?
Entry $_
\n}; exit; } #----------- sub prHeader { #----------- my ( $colcount, $cellwidth, $subjsec, $extrafield, $teachername ) = @_; # push entry text into array. my @entry = (); for (1..$entrylimit){ my $entryname = 'entry'.$_; if ($arr{$entryname}){ # if it exists...push into array push @entry,$arr{$entryname}; } } my $entrysize = $#entry + 1; # print qq{Entry size: $entrysize
\nEntry:", @entry, "
\n};; if ($arr{repeat} and $entrysize){ $repeatcount = $colcount / $entrysize; $repeatcount = int $repeatcount; } elsif ($entrysize){ $repeatcount = 1; # only do it once if not repeat. } else { $repeatcount = 0;} # print qq{Repeat Count: $repeatcount\n}; # Done at the start of a new page. $cellwidthtext = $cellwidth. 'mm'; print TEX "\\begin{tabular}{G|"; if ( $extrafield ) { print TEX "p{$extrafieldwidth mm}|"; } for (1..$colcount){ print TEX "p{$cellwidthtext}|"; } print TEX "}\n"; print TEX "\\raggedright\\bf $schoolname "; if ( $extrafield ) { print TEX "& "; } for ( 1..$colcount ){ print TEX "& ";} print TEX "\\\\ \n"; print TEX "\\small\\raggedright $currdate "; if ( $extrafield ) { print TEX "& "; } for ( 1..$colcount ){ print TEX "& ";} print TEX "\\\\ \n"; print TEX "\\raggedright\\bf $teachername "; if ( $extrafield ) { print TEX "& "; } for ( 1..$colcount ){ print TEX "& ";} print TEX "\\\\ \n"; # Courses my $sth = $dbh->prepare("select smdesc, description from subject where subjsec = ?"); $sth->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($smdesc, $desc) = $sth->fetchrow; ($smdesc, $desc ) = latex_filter($smdesc, $desc); if ( length($desc) > 20 ) { $desc = $smdesc; } # use smdesc if too long. print TEX "\\bf\\raggedright $desc - $subjsec "; if ( $extrafield ) { print TEX "&\\hfil\\rule{6pt}{0pt}\\begin{rotate}{90}$extrafield"; print TEX "\\end{rotate}\\hfil"; } my $remainder = $colcount - ($repeatcount * $entrysize); if ( $repeatcount ){ for (1..$repeatcount){ # times to loop to print rotated text. foreach my $txt ( @entry ) { print TEX "&\\hfil\\rule{6pt}{0pt}\\begin{rotate}{90}$txt"; print TEX "\\end{rotate}\\hfil"; } } } for (1..$remainder){ print TEX "& ";} print TEX "\\\\ \\hline\n"; } # End of prHeader #----------- sub prRecord { # print normal record (ie. line) #----------- my ( $lastname, $firstname, $studnum, $colcount, $extrafield ) = @_; if ( $extrafield ) { ( $extrafield ) = latex_filter( $extrafield ); } if ( $alternatingcolor == 0 ) { print TEX "\\rowcolor[gray]{". $arr{grayshade}. "}"; $alternatingcolor = 1; } else { $alternatingcolor = 0;} print TEX "\\raggedright $lastname, $firstname"; if ( $extrafield ) { print TEX "&\\footnotesize{$extrafield}"; } for (1..$colcount){ print TEX "& ";} print TEX "\\\\\\hline\n"; } # end of prrecord #-------------- sub prStudCount { #-------------- my ($studcount, $colcount, $extrafield) = @_; my $grayval = $arr{grayshade} - 0.08; # make it darker, slightly print TEX "\\rowcolor[gray]{$grayval}"; print TEX $lex{Students}. ": $studcount"; if ( $extrafield ) { print TEX "& ";} for ( 1..$colcount ){ print TEX "& ";} print TEX "\\\\\\hline\n"; }