#!/usr/bin/perl # Copyright 2001-2009 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. # File: rpttransmon.pl # Monthly report for Enrollment change. # PDF Output my %lex = ('INAC Nominal Roll' => 'INAC Nominal Roll', 'View/Download' => 'View/Download', 'View Log File' => 'View Log File', 'Main' => 'Main', 'Current Date' => 'Current Date', 'Grade' => 'Grade', 'Error' => 'Error', 'Group' => 'Group', 'Homeroom' => 'Homeroom', 'Values' => 'Values', 'Blank=All' => 'Blank=All', 'Homeroom, Lastname, Firstname' => 'Homeroom, Lastname, Firstname', 'Grade, Lastname, Firstname' => 'Grade, Lastname, Firstname', 'Lastname, Firstname' => 'Lastname, Firstname', 'Continue' => 'Continue', 'Separate with Spaces' => 'Separate with Spaces', 'Sort by' => 'Sort by', ); my $self = 'rptinacroll.pl'; use DBI; use CGI; 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"; } # Used for the category titles my $shortname = "inacroll$$"; my $filename = "$shortname.tex"; my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; # Set Dates my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $iddst) = localtime(time); $year = $year + 1900; $mon++; $wday++; my $currdate = "$dow[$wday], $month[$mon] $mday, $year"; # Print Page Head. print "$doctype\n". $lex{'Monthly Transfer Report'}. " $chartype\n[ ". $lex{Main}. " ]\n"; print "

$schoolname — ". $lex{'INAC Nominal Roll'}. "

\n"; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; if ( not $arr{page} ) { showStartPage(); } else { delete $arr{page}; } #foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # Get passed group values my @values = split /\s/, $arr{groupValue}; # split into array based on spaces delete $arr{groupValue}; #print "Group", @group, "
\n"; my $groupType; if ( $arr{groupType} eq $lex{Grade} ) { $groupType = 'grade'; } else { $groupType = 'homeroom'; } delete $arr{groupType}; # Select students, sorted by lastname, firstname, my $select; if ( @values ) { $select = 'and ( '; $first = 1; foreach my $value ( @values ) { $value = $dbh->quote( $value ); if ( not $first ) { $select .= ' or '; } $select .= "$groupType = $value "; $first = 0; } $select = $select . ' ) ' } #if ($select) { print "Select: $select
\n"; } my $sortorder = "lastname, firstname"; if ( $arr{sortorder} eq $lex{'Lastname, Firstname'} ) { $sortorder = "lastname, firstname"; } elsif ( $arr{sortorder} eq $lex{'Grade, Lastname, Firstname'} ) { $sortorder = "grade, lastname, firstname"; } elsif ( $arr{sortorder} eq $lex{'Homeroom, Lastname, Firstname'} ) { $sortorder = "homeroom, lastname, firstname"; } #print "Sortorder: $sortorder
\n"; $sth = $dbh->prepare("select student.lastname, student.firstname, student.grade, student.treaty, student.birthdate, student.initial, student.sex, student_inac.* from student, student_inac where student.studnum = student_inac.studnum $select order by $sortorder"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } checkStatusNumbers(); open( TEX,">$filename" ) || die "Failed tex file open"; printDocumentTop(); my $continue = 1; while ( $continue ) { printFormTop(); # Print up to 7 Student Records for ( 1 .. 7 ) { # Get next record my $rec_ref = $sth->fetchrow_hashref; if ( $rec_ref ) { # print the record. # Strip off leading text. foreach my $key ( keys %$rec_ref ) { my ($first, $value ) = split /\(/, $$rec_ref{$key}; if ( $value ) { chop $value; # remove trailing ) $$rec_ref{$key} = $value; } } # Truncate the initial $$rec_ref{'initial'} = uc substr $$rec_ref{'initial'}, 0, 1; # LaTeX filter foreach my $key ( keys %{ $rec_ref } ) { ( $$rec_ref{$key} ) = latex_filter( $$rec_ref{$key} ); } %r = %$rec_ref; # foreach my $key ( sort keys %{$rec_ref} ) { print "K:$key V: ${$rec_ref}{$key}
\n"; } my ($band,$family,$student) = unpack("a3a5a2",$r{treaty}); $r{birthdate} =~ s/\-//g; print TEX "$band &$family &$student & $r{lastname} & $r{firstname} & $r{initial} "; print TEX "& $r{birthdate} & $r{status_code} & $r{sex} & $r{grade} & $r{fte} "; print TEX "& $r{residence} &$r{accommodation} &$r{transportation_daily} "; print TEX "&$r{transportation_other} &$r{highcost} &$r{language_home} "; print TEX "&$r{language_instruction} &$r{language_extent} &$r{financial_edu} "; print TEX "&$r{residence_band} &$r{residence_reserve} \\\\ \\hline\n"; } else { # no record print TEX " & & & & & & & & & & & "; print TEX "& & & & & & & & & & \\\\ \\hline\n"; $continue = 0; } } printFormBottom(); } printDocumentBottom(); #--------------------- sub checkStatusNumbers { #--------------------- print "\n"; print "\n"; print "\n"; my $first = 1; # $dbh already open... my $sth = $dbh->prepare("select lastname, firstname, studnum, grade, treaty from student order by lastname, firstname"); $sth->execute; if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $lastname, $firstname, $studnum, $grade, $treaty ) = $sth->fetchrow ) { if ( length( $treaty ) != 10 ) { # problems. my $tlen = length( $treaty ); $first = 0; print ""; print "\n"; } } if ( $first ) { # No Errors print "\n"; } print "
10 Digit Status Number Check
StudentGradeLength
$lastname, $firstname ($studnum)$gradeError: Length is $tlen
All Status Numbers are 10 digits in length

\n"; } #---------------- sub showStartPage { #---------------- # Setup the form and start of table. print "
\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "
". $lex{Group}. "\n"; print "\n"; print "
". $lex{Values}. "\n"; print "\n"; print $lex{'Separate with Spaces'}. q{, }. $lex{'Blank=All'}. "
". $lex{'Sort by'}. "\n"; print "
"; print ""; print "
\n"; print "\n"; exit; } #------------------- sub printDocumentTop { #------------------- my $papersize = 'legalpaper'; print TEX "\\documentclass[12pt,$papersize, landscape]{article} \\usepackage{array,colortbl,helvet,rotating,inputenc,graphicx} $a_latex_header \\renewcommand{\\familydefault}{\\sfdefault} \\pagestyle{empty} \\setlength{\\textwidth}{13.6in} \\setlength{\\textheight}{206mm} \\setlength{\\evensidemargin}{0mm} \\setlength{\\oddsidemargin}{0mm} \\setlength{\\hoffset}{-22.5mm} \\setlength{\\voffset}{-40mm} \\setlength{\\parindent}{0pt} \\setlength{\\tabcolsep}{4pt} \\renewcommand{\\familydefault}{\\sfdefault} \\setlength{\\extrarowheight}{6pt}\n"; print TEX "\\begin{document}\n"; } #--------------- sub printFormTop { #--------------- print TEX "\\includegraphics[width=75mm]{fip-eng.jpg}\\hfill {\\footnotesize\\bf PROTECTED A}\n\n"; print TEX "\\hfill {\\scriptsize DCI 462572.FNITP (2009-2010)}\\medskip\n\n"; print TEX "\\hfill {\\bf NOMINAL ROLL STUDENT CENSUS REPORT}\\bigskip\n\n"; print TEX "\\parbox{\\textwidth}{\\scriptsize The information you provide in this document is collected under the authority of Treasury Board's policy on Transfer payments for the purpose of delivering programs and services for elementary and secondary education. Information on individuals is used by Indian and Northern\n"; print TEX "Affairs Canada employees who need to know the information in order to respond to your request and/or the program requirements. We do not share the personal information with other government departments. The personal information will be kept for a period do 30\n"; print TEX "years and then will be transferred to Library and Archives Canada. Individual have the right to the protection of and access to their personal information under the Privacy Act http://laws.justice .gc.ca/en/p-21/255104.html. The information collected is described under the Personal\n"; print TEX "Information Bank INA PPU 045 which is detailed at www.infosource.gc.ca.}\n\n"; # First Section about the School print TEX "\\begin{tabular}{|p{114mm}|p{82mm}|p{40mm}|p{40mm}|p{28mm}|p{26mm}|} \\hline\n"; print TEX "\\multicolumn{2}{|l|}{\\raisebox{8pt}{\\tiny Official School Name}}"; print TEX "&\\raisebox{8pt}{\\tiny Telephone No.}"; print TEX "&\\raisebox{8pt}{\\tiny Fax No.}"; print TEX "&\\raisebox{8pt}{\\tiny School Number}"; print TEX "&\\raisebox{8pt}{\\tiny Grade Range}\\\\\n"; print TEX "\\multicolumn{2}{|l|}{\\large $schoolname} &\\large $schoolphone&\\large $schoolfax"; print TEX "& $inac_schoolnum & $inac_grades \\\\ "; print TEX "\\cline{1-4}\n"; print TEX "\\raisebox{8pt}{\\tiny School Street / Mailing Address}"; print TEX "&\\raisebox{8pt}{\\tiny City / Town}&\\raisebox{8pt}{\\tiny Province / Territory}"; print TEX "&\\raisebox{8pt}{\\tiny Postal Code}&& \\\\ \n"; print TEX "\\large $schooladdr1&\\large $schoolcity &\\large $schoolprov &\\large $schoolpcode"; print TEX "& & \\\\ \\hline\n"; print TEX "\\end{tabular}\\medskip\n\n"; print TEX "Section 1: Complete for all students eligible for funding by INAC\n\n"; # Start Second table with students. print TEX "\\begin{tabular}{|p{15mm}|p{15mm}|p{13mm}|p{55mm}|p{49mm}|p{11mm}|p{18mm}|"; print TEX "p{5.5mm}|p{5.5mm}|p{5.5mm}|p{5.5mm}|p{5.5mm}|p{5.5mm}|p{5.5mm}|p{5.5mm}|"; print TEX "p{5.5mm}|p{5.5mm}|p{5.5mm}|p{5.5mm}|"; print TEX "p{17mm}|p{12mm}|p{13mm}|} \\hline\n\n"; print TEX "\\multicolumn{3}{|c|}{\\bf 1 }"; print TEX " &\\hfil\\bf 2\\hfil&\\hfil\\bf 3\\hfil &\\hfil\\bf 4\\hfil&\\hfil\\bf 5\\hfil&\\hfil\\bf 6\\hfil"; print TEX "&\\hfil\\bf 7\\hfil&\\hfil\\bf 8\\hfil&\\hfil\\bf 9\\hfil&\\hfil\\bf 10\\hfil&\\hfil\\bf 11\\hfil"; print TEX "&\\hfil\\bf 12\\hfil&\\hfil\\bf 13\\hfil&\\hfil\\bf 14\\hfil&\\hfil\\bf 15\\hfil&\\hfil\\bf"; print TEX " 16\\hfil&\\hfil\\bf 17\\hfil&\\hfil\\bf 18\\hfil&\\hfil\\bf 19\\hfil&\\hfil\\bf 20\\hfil"; print TEX "\\\\ \\hline\n"; print TEX "\\multicolumn{3}{|c|}{\\scriptsize Student Identifier Number } & "; print TEX "\\multicolumn{4}{c|}{ \\parbox{136mm}{\\scriptsize Please note that for registered students, the Family Name,"; print TEX " Given Name, Initials(s), Date of Birth and Gender must match the information listed"; print TEX " in the Indian Registry}}"; print TEX " & & & & & & & & & & & & & & & \\\\ \n"; print TEX "\\cline{1-7}\n"; print TEX "\\scriptsize Band No.&\\scriptsize Family No.&\\scriptsize Child position~ No."; print TEX "&\\scriptsize Family~Name (Insert~Alias~in~Brackets)"; print TEX "&\\scriptsize Given~Name (Insert~Alias~in~Brackets)"; print TEX "&\\scriptsize Initial(s)&\\scriptsize Date~of~Birth (YYYYMMDD)"; # Now Start Rotated Text. print TEX "&\\hfil\\rotatebox[origin=br]{90}{\\parbox{27mm}"; print TEX "{\\scriptsize Status Code}}\\hfil"; print TEX "&\\hfil\\rotatebox[origin=br]{90}{\\parbox{27mm}"; print TEX "{\\scriptsize Gender }}\\hfil"; print TEX "&\\hfil\\rotatebox[origin=br]{90}{\\parbox{27mm}"; print TEX "{\\scriptsize Grade}}\\hfil"; print TEX "&\\hfil\\rotatebox[origin=br]{90}{\\parbox{27mm}"; print TEX "{\\scriptsize Full Time Equivalent}}\\hfil"; print TEX "&\\hfil\\rotatebox[origin=br]{90}{\\parbox{27mm}"; print TEX "{\\scriptsize Residence}}\\hfil"; print TEX "&\\hfil\\rotatebox[origin=br]{90}{\\parbox{27mm}"; print TEX "{\\scriptsize Accommodation}}\\hfil"; print TEX "&\\hfil\\rotatebox[origin=br]{90}{\\parbox{27mm}"; print TEX "{\\scriptsize Daily Transportation}}\\hfil"; print TEX "&\\hfil\\rotatebox[origin=br]{90}{\\parbox{27mm}"; print TEX "{\\scriptsize Other Transportation}}\\hfil"; print TEX "&\\hfil\\rotatebox[origin=br]{90}{\\parbox{26mm}"; print TEX "{\\scriptsize\\raggedright High~Cost~Special Education~Needs}}"; print TEX "\\hfil"; print TEX "&\\hfil\\rotatebox[origin=br]{90}{\\parbox{27mm}"; print TEX "{\\scriptsize Home Language}}\\hfil"; print TEX "&\\hfil\\rotatebox[origin=br]{90}{\\parbox{26mm}"; print TEX "{\\raggedright\\scriptsize Language(s) of Instruction}}\\hfil"; print TEX "&\\rotatebox[origin=r]{90}{\\parbox{27mm}"; print TEX "{\\scriptsize Extent~of~Indian Language~Instruction}}"; print TEX "&\\centering\\scriptsize Band, Tribal Council or Education Authority of Fiscal Responsibility "; print TEX "&\\centering\\scriptsize Band of Residence &{\\raggedright\\scriptsize Reserve of Residence}\\\\ \\hline\n"; } # end of printFormTop #------------------ sub printFormBottom { #------------------ # Now the Last Section print TEX "\\end{tabular}\n\\begin{tabular}"; print TEX "{|p{62mm}|p{62mm}|p{31mm}|p{53mm}|p{61.5mm}|p{31mm}|p{27.5mm}|} \n"; print TEX "\\multicolumn{2}{|l|}{\\raisebox{8pt}"; print TEX "{\\tiny Compiled by (First Nation/School Representative)}}\n"; print TEX "&\\raisebox{8pt}{\\tiny Date(YYYYMMDD)}"; print TEX "&\\multicolumn{2}{l|}{\\raisebox{8pt}"; print TEX "{\\tiny Certified by (First Nation/School Representative)}}\n"; print TEX "&\\raisebox{8pt}{\\tiny Date(YYYYMMDD)}& \\\\\n"; print TEX "\\raisebox{20pt}{\\tiny Given Name}"; print TEX "&\\raisebox{20pt}{\\tiny Family Name}&"; print TEX "&\\raisebox{20pt}{\\tiny Given Name}"; print TEX "&\\raisebox{20pt}{\\tiny Family Name}&&"; print TEX "\\tiny Page\\hspace{5mm} of \\underline{\\hspace{12mm}} \\\\\n"; print TEX "\\hline\n"; print TEX "\\end{tabular}\n\\bigskip\n\n"; print TEX "\\hfill\\includegraphics[width=25mm]{canada.jpg}\n"; } # end of printFormBottom #---------------------- sub printDocumentBottom { #---------------------- print TEX "\\end{document}\n"; close TEX; system("$pdflatex $filename >pdflog$$.txt"); system("mv $shortname.pdf $downloaddir"); system("mv pdflog$$.txt $downloaddir"); system("rm -f $shortname.*"); print "

\n"; print $lex{'View/Download'}. ' '. $lex{'INAC Nominal Roll'}. "

\n"; print "[ ". $lex{'View Log File'}. " |\n"; print "". $lex{Main}. " ]\n"; } # end of printDocumentBottom