#!/usr/bin/perl # Copyright 2001-2024 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. # Should go into the configuration, next. my $templatepath = '../../template/'; my %lex = ('Print Invoices' => 'Print Invoices', 'Invoice Date' => 'Invoice Date', 'Main' => 'Main', 'Fees' => 'Fees', 'Continue' => 'Continue', 'View Log File' => 'View Log File', 'View/Download' => 'View/Download', 'Invoices' => 'Invoices', 'Error' => 'Error', 'Font Size' => 'Font Size', 'Paper Size' => 'Paper Size', 'Letter' => 'Letter', 'Legal' => 'Legal', 'A4' => 'A4', 'Email' => 'Email', 'Website' => 'Website', 'Homeroom' => 'Homeroom', 'Student' => 'Student', 'Phone' => 'Phone', 'Fax' => 'Fax', 'Select' => 'Select', 'Transactions' => 'Transactions', 'All' => 'All', 'Unpaid' => 'Unpaid', 'Student Group' => 'Student Group', 'Grade' => 'Grade', 'Homeroom' => 'Homeroom', 'Student' => 'Student', 'Last,First/Last/Initials/Studnum' => 'Last,First/Last/Initials/Studnum', 'Check' => 'Check', 'Next Page' => 'Next Page', 'or' => 'or', 'No Records Found' => 'No Records Found', 'No Student(s) Found' => 'No Student(s) Found', 'Template' => 'Template', ); my $self = 'invoice.pl'; use CGI; use DBI; use Cwd; use Number::Format qw(:all); # calc current date my @tim = localtime(time); my $year = @tim[5] + 1900; my $month = @tim[4] + 1; my $day = @tim[3]; my $currdate = "$year-$month-$day"; eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; eval require "../../etc/fees.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 $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); # Get current dir so know what CSS to display; #if (getcwd() =~ /tcgi/){ # we are in tcgi # $css = $tchcss; #} # Show page Header my $title = $lex{'Print Invoices'}; print qq{$doctype\n$title\n}; print qq{\n}; if ( not $arr{page} ) { # calendar on start page print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; } print qq{$chartype\n[ $lex{Main} |\n}; print qq{$lex{Fees} ]\n}; print qq{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; if ( $arr{student} ) { searchStudent( $arr{student} ); } else { searchGroup( $arr{group}, $arr{groupid} ); } } elsif ( $arr{page} == 2 ) { delete $arr{page}; printInvoices(); } #---------------- sub showStartPage { #---------------- # Setup the form and start of table. print qq{

\n}; print qq{\n}; print qq{\n}; print qq{\n}; # Templates - feeInvoice Report Templates my @files = glob($templatepath."feeInvoice*.tpl"); my %desc = (); for my $tplfile ( @files ) { # read each label file and get description unless ( open (FH,"<$tplfile")) { print $lex{Error}. q{ }. $lex{Template}. ": $!\n"; die $lex{Error}. q{ }. $lex{Template}. ": $!\n"; } # read first 2 lines of the template only. my $desc = ; chomp $desc; $desc =~ s/\[//g; # strip any opening square labels, just in case. my $modeline = ; $modeline =~ s/^\s*//; # strip any leading spaces my ($mode, $fmtstart,$fmtend) = split /::/, $modeline; # ignore rest of the file, not required. $tplfile =~ s/^.*\///; # strip leading path $desc{$tplfile} = "$desc ($mode)"; } print qq{\n}; # Paper Size $defaultpapersize =~ s/paper//; # strip out word paper so lex works; from admin.conf my $defpaper = ucfirst( $defaultpapersize ); print qq{\n\n}; print qq{\n\n }; print qq{\n\n}; # print qq{\n}; print qq{\n\n}; print qq{}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{

$lex{'Invoice Date'}	\n}; print qq{\n}; print qq{\n}; print qq{
$lex{Select} $lex{Template}\n}; print qq{
$lex{'Paper Size'}	}; print qq{
$lex{'Font Size'}	}; print qq{
}; print qq{$lex{Select} $lex{Transactions}	}; print qq{\n}; print qq{

$lex{'Student Group'}	}; print qq{\n }; print qq{}; print qq{
$lex{or}
$lex{Student}	\n}; print qq{$lex{'Last,First/Last/Initials/Studnum'}
$lex{Check} $lex{'Next Page'}
	\n}; print qq{}; print qq{

\n}; print qq{\n}; print qq{\n}; exit; } #---------------- sub printInvoices { #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # remove values from %arr, leaving only student numbers. my $date = $arr{date}; delete $arr{date}; my $papersize = $arr{papersize}; delete $arr{papersize}; my $fontsize = $arr{fontsize}; delete $arr{fontsize}; my $transactmode = $arr{transactmode}; delete $arr{transactmode}; my $template = $arr{template}; delete $arr{template}; # Load Template unless ( open ( FH,"<", $templatepath. $template )) { print qq{$lex{Error} $lex{Template}: $!\n}; die "$lex{Error} $lex{Template}: $!\n"; } my $formtext; my $desc = ; my $modeline = ; chomp $modeline; # remove trailing CR/LF. $modeline =~ s/^\s*//; # strip any leading spaces. my ($mode, $fmtstart, $fmtend) = split /::/, $modeline; my $header = ; # header information for both pdf or html. $header =~ s/^\s*//; # strip any leading spaces # Now put replacement %lex text back in. $header =~ s{\<\*(.*?)\*\>} { exists( $lex{$1} ) ? $lex{$1} : $1 }gsex; # table footer my $footer = ; # Now put replacement %lex text back in. $footer =~ s{\<\*(.*?)\*\>} { exists( $lex{$1} ) ? $lex{$1} : $1 }gsex; # Future Expansion my $line5 = ; # Now load rest of template for layout. { local $/; $formtext = ; close FH;} # Create student sort order. my $sth = $dbh->prepare("select lastname, firstname from studentall where studnum = ?"); # create sorting structure. my %students = (); foreach my $studnum ( keys %arr ) { $sth->execute( $studnum ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname ) = $sth->fetchrow; $students{"$lastname$firstname$studnum"} = $studnum; } # Setup TeX values my $shortname = "rptinvoice$$"; my $filename = "$shortname.tex"; # Set Paper Size, text width and height my ( $textwidth, $textheight ); if ( $papersize eq $lex{Letter} ) { $papersize = 'letterpaper'; $textwidth = $g_letterpaper_textwidth; $textheight = $g_letterpaper_textheight; } elsif ( $papersize eq $lex{Legal} ) { $papersize = 'legalpaper'; $textwidth = $g_legalpaper_textwidth; $textheight = $g_legalpaper_textheight; } elsif ( $papersize eq $lex{A4} ) { $papersize = 'a4paper'; $textwidth = $g_a4paper_textwidth; $textheight = $g_a4paper_textheight; } # Start the TeX file... open(TEX,">$filename") || die "Can't open tex file"; # Letter paper size is 279mm x 213mm # 200mm wide - 50mm name, 20mm extra fld, leaves 130mm for columns. # The tabcolsep of 1mm leaves 128 mm where each col is $width + 1mm wide. print TEX "\\documentclass[ $fontsize, $papersize]{article} \\usepackage{array,newcent,rotating,colortbl} \\pagestyle{empty} \\setlength{\\textwidth}{$textwidth} \\setlength{\\textheight}{$textheight} \\setlength{\\hoffset}{-40mm} \\setlength{\\voffset}{-30mm} \\addtolength{\\evensidemargin}{0mm} \\addtolength{\\oddsidemargin}{0mm} \\setlength{\\tabcolsep}{1mm} \\setlength{\\extrarowheight}{2mm}\n"; print TEX "\\begin{document}\n\\begin{center}"; # Prepare for getting student data my $sth = $dbh->prepare("select * from fees_jrl where studnum = ? order by trans_date, id"); my $sth1 = $dbh->prepare("select lastname, firstname, studnum, homeroom, address1, city1, pcode1, grade from studentall where studnum = ?"); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my $sth2 = $dbh->prepare("select count(*) from fees_jrl where studnum = ? and paid_id is NULL"); my $first = 1; # Loop through all students foreach my $key ( sort keys %students ) { my $studnum = $students{$key}; # Check for unpaid charges $sth2->execute( $studnum ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my $unpaidcount = $sth2->fetchrow; if ( $transactmode eq $lex{Unpaid} and not $unpaidcount ) { # skip student, since no unpaid bills next; } # Get Student values $sth1->execute( $studnum ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname, $studnum, $homeroom, $address1, $city1, $pcode1, $grade ) = $sth1->fetchrow; # Do start of page... print TEX "{\\Large $schoolname}\n\\medskip\n\n"; print TEX "$schooladdr1, $schooladdr2\n\n"; print TEX "$schoolcity, $schoolprov $schoolpcode\n\n"; if ( $schoolphone ) { print TEX "$lex{Phone} $schoolphone\n\n"; } if ( $schoolfax ) { print TEX "$lex{Fax} $schoolfax\n\n"; } if ( $schoolemail ) { print TEX "$lex{Email} $schoolemail\n\n"; } if ( $schoolwebsite ) { print TEX "$lex{Website} $schoolwebsite\n\n"; } print TEX "\\bigskip\n\n"; # print Student Info print TEX "\\begin{tabular}{lll}\n"; print TEX "{\\bf $lex{Student}}: & $firstname $lastname ($studnum) "; print TEX "& $lex{Grade}: $grade \\quad $lex{Homeroom}: $homeroom\\\\ \n"; print TEX " & $address1 & \\\\\n"; print TEX " & $city1 & \\\\\n"; print TEX "\\end{tabular}\n\n\\bigskip\n\n"; # Setup Transactions Tabular layout print TEX "$fmtstart\n"; # Header print print TEX "$header\n"; # Print Transactions section my $studenttotal; $sth->execute( $studnum ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { my %rec = (); %rec = %$ref; # Update total $studenttotal += $rec{total}; # add to total # Skip depending on mode. if ( $transactmode eq $lex{Unpaid} and $rec{paid_id} ) { next; } # skip paid # Filter characters for LaTeX ( $rec{name}, $rec{description} ) = latex_filter($rec{name}, $rec{description} ); # Pad out id length; $padding_digits comes from fees.conf my $id = $rec{id}; while ( length( $id ) < $padding_digits ) { $id = '0'. $id; } my $currec = $formtext; $currec =~ s{\<\@(.*?)\@\>} { exists( $rec{$1} ) ? $rec{$1} : $1 }gsex; # strip any entities back to their numbers $currec =~ s{\&\#(\d+)\;}{$1}gsex; # print normal transaction record. print TEX "$currec\n"; # print tax values # if (@tax) { # from fees.conf; taxes defined # for my $i (1..$#tax) { # print qq{I:$i }; # my $fld = 'tax'. $i; # my $fldname = $fld. '_name'; # print TEX '&'. $$fld. ' '; # } # } } # end of Transaction loop $rec{studenttotal} = format_number( $studenttotal, 2,2); # put in studenttotal value my $newfooter = $footer; $newfooter =~ s{\<\@(.*?)\@\>} { exists( $rec{$1} ) ? $rec{$1} : $1 }gsex; # Footer print print TEX "$newfooter\n"; print TEX "$fmtend\n"; print TEX "\\newpage\n\n"; } # end of Student loop print TEX "\\end{center}\\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 qq{

}; print qq{$lex{'View/Download'} $lex{Invoices}

\n}; print qq{

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

\n}; exit; } #---------------- sub searchStudent { #---------------- my $checked; if ( $arr{check} ) { $checked = q{checked = "checked"}; } my $student = shift; # Setup the Search if ($student =~ /\d+/) { # we have a student number $sth = $dbh->prepare("select lastname, firstname, studnum from studentall where studnum = ?"); $sth->execute( $student ); } else { # we have words possibly with a comma ($lastname,$firstname) = split /\,/, $student; $firstname =~ s/^\s*//; $lastname =~ s/^\s*//; if ($lastname and $firstname){ # both entered. $sth = $dbh->prepare("select lastname, firstname, studnum from studentall where lastname = ? and firstname = ?"); $sth->execute( $lastname, $firstname ); } elsif ($lastname and not $firstname){ # only lastname (no comma) if (length($lastname) == 2){ # search by initials: fi, li. my $fi = substr($lastname,0,1). '%'; my $li = substr($lastname,1,1). '%'; $sth = $dbh->prepare("select lastname, firstname, studnum from studentall where lastname $sql{like} ? and firstname $sql{like} ?"); $sth->execute( $li, $fi ); } else { $sth = $dbh->prepare("select lastname, firstname, studnum from studentall where lastname = ? order by firstname"); $sth->execute( $lastname ); } } else { # print an error.... print qq{

$lex{Error}

\n}; print qq{\n}; exit; } } # Last Else # We should now have a $sth defined. if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $rows = $sth->rows; # print qq{Rows: $rows
\n}; if ( $rows < 1 ) { print qq{

$lex{'No Student(s) Found'}

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

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

$lex{Student} }; print qq{($lex{'Last,First/Last/Initials/Studnum'}) }; print qq{
\n}; print qq{

\n}; exit; } print qq{

\n}; print qq{\n}; print qq{\n}; # pass other values. foreach my $key ('date','papersize', 'fontsize','template') { my $val = $arr{$key}; print qq{\n}; delete $arr{$key}; } print qq{\n}; print qq{\n}; print qq{\n}; my $sth1 = $dbh->prepare("select count(*) from fees_jrl where studnum = ?"); my $first = 1; while ( my ($lastname, $firstname, $studnum) = $sth->fetchrow ) { # Find any transactions; otherwise skip $sth1->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; print qq{\n}; } print qq{\n}; print qq{

$lex{Student}	$lex{Select}
\n}; print qq{
$lastname, $firstname ($studnum)	\n}; if ( $count ) { print qq{\n}; } else { print $lex{'No Records Found'}; } print qq{
\n}; print qq{

\n}; exit; } # end of searchStudent #-------------- sub searchGroup { #-------------- # search for a group of students (by grade(s) or homeroom(s)) # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my $checked; if ( $arr{check} ) { $checked = q{checked = "checked"}; } my ( $group, $groupid ) = @_; # Select Students my @students = (); my $sort = 'name'; # hold type of sort. if ( $groupid ) { # then we have to do something... my @groups = (); @groups = split /\s+/, $arr{groupid}; if ( $group eq $lex{Grade} ) { $group = 'grade'; $sort = 'grade'; } else { $group = 'homeroom'; $sort = 'homeroom'; } my $sth = $dbh->prepare("select studnum from student where $group = ? order by $group, lastname, firstname"); foreach my $grp ( @groups ) { $sth->execute( $grp ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $studnum = $sth->fetchrow ) { push @students, $studnum; } } } else { # all students my $sortorder = "lastname, firstname"; if ( $arr{sortorder} eq $lex{Homeroom} ) { $sortorder = "homeroom, lastname, firstname"; $sort = 'homeroom'; } elsif ( $arr{sortorder} eq $lex{Grade} ) { $sortorder = "grade, lastname, firstname"; $sort = 'grade'; } $sth = $dbh->prepare("select studnum from student order by $sortorder"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $studnum = $sth->fetchrow ) { push @students, $studnum; } } # Now loop through all students print qq{

\n}; print qq{\n}; print qq{\n}; # pass other values. foreach my $key ('date','papersize', 'fontsize','template') { my $val = $arr{$key}; print qq{\n}; delete $arr{$key}; } print qq{\n}; print qq{\n}; print qq{\n}; my $sth = $dbh->prepare("select lastname, firstname from studentall where studnum = ?"); my $sth1 = $dbh->prepare("select count(*) from fees_jrl where studnum = ?"); my $first = 1; foreach my $studnum ( @students ) { $sth->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname ) = $sth->fetchrow; # Find any transactions; otherwise skip $sth1->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; print qq{\n}; } print qq{\n}; print qq{

$lex{Student}	$lex{Select}
\n}; print qq{
$lastname, $firstname ($studnum)	\n}; if ( $count ) { print qq{\n}; } else { print $lex{'No Records Found'}; } print qq{
\n}; print qq{

\n}; exit; } # end of selectGroup