#!/usr/bin/perl # Copyright 2001-2020 Leslie Richardson use DBI; use CGI; use Number::Format qw(:all); my %lex = ('Grade' => 'Grade', 'Homeroom' => 'Homeroom', 'Main' => 'Main', 'Select' => 'Select', 'Values' => 'Values', 'Group' => 'Group', 'Parent' => 'Parent', 'Subject' => 'Subject', 'Message' => 'Message', 'Continue' => 'Continue', 'Student' => 'Student', 'Checked' => 'Checked', 'Email Sent' => 'Email Sent', 'Error' => 'Error', 'Email' => 'Email', 'Next Page' => 'Next Page', ); my $self = 'emailTeacher.pl'; eval require "../etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $dbtype = 'mysql'; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; # Page Header. my $title = "Email Students/Parents"; print "$doctype\n$title\n"; print qq{\n}; print qq{[ Main ]\n}; print qq{

$title

\n}; if ( not $arr{page} ) { # Select Groups and which emails to do (Student, Parents) showStartPage(); } elsif ( $arr{page} == 1 ) { # Select Students, Parents / Set Message, Attachment delete $arr{page}; selectRecipients(); } elsif ( $arr{page} == 2 ) { # post the Mail delete $arr{page}; showReport(); } #---------------- sub showStartPage { #---------------- my (@homerooms, @grades ); # Get Homerooms my $sth = $dbh->prepare("select distinct homeroom from student where homeroom is not NULL and homeroom != ''"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $hr = $sth->fetchrow ) { push @homerooms, $hr; } @homerooms = sort {$a <=> $b} @homerooms; # Get Grades $sth = $dbh->prepare("select distinct grade from student where grade is not NULL and grade != ''"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $gr = $sth->fetchrow ) { push @grades, $gr; } @grades = sort {$a <=> $b} @grades; =head print qq{\n}; print qq{}; print qq{\n}; print qq{}; print qq{\n}; print qq{
Current Grades}; foreach my $gr ( @grades ) { print "$gr "; } print qq{
Current Homerooms}; foreach my $hr ( @homerooms ) { print qq{$hr }; } print qq{
\n}; =cut # Setup the form and start of table. print qq{
\n}; print qq{\n}; print qq{\n}; # Grades; print qq{
\n}; foreach my $gr ( @grades ) { print qq{\n}; } print qq{
Select
Grades
$gr
\n}; # Homerooms; print qq{\n}; foreach my $hr ( @homerooms ) { print qq{\n}; } print qq{
Select
Homerooms
$hr
\n}; # Rest of Options. print qq{\n}; print qq{\n}; print qq{\n}; print qq{
$lex{Select}\n}; print qq{ }; print qq{$lex{Student} $lex{Email}
\n}; print qq{ }; print qq{ $lex{Parent} 1 $lex{Email}
\n}; print qq{ }; print qq{ $lex{Parent} 2 $lex{Email}
\n}; print qq{
$lex{'Next Page'}
$lex{Checked}?		\n}; print qq{
}; print qq{}; print qq{
\n}; print qq{\n}; exit; } #------------------- sub selectRecipients { # Choose students/parents to email to and put in message #------------------- # foreach my $key ( sort keys %arr) { print qq{K:$key V:$arr{$key}
\n}; } # passed 3 email selects, checked, then grades or homerooms as G:* or H:* my $checked; if ( $arr{checked} ) { $checked = qq{checked="checked"}; delete $arr{checked}; } my $studmail = $arr{selStudent}; my $par1mail = $arr{selPar1}; my $par2mail = $arr{selPar2}; delete $arr{selStudent}; delete $arr{selPar1}; delete $arr{selPar2}; # foreach my $key ( sort keys %arr) { print qq{K:$key V:$arr{$key}
\n}; } foreach my $key ( sort keys %arr ) { my ($group, $value ) = split(':', $key); if ( $group eq 'G' ) { push @grades, $value; } elsif ( $group eq 'H' ) { push @homerooms, $value; } else { print qq{

Error: No value for $key

\n}; } } my %groups; @{$groups{homeroom}} = @homerooms; @{$groups{grade}} = @grades; # Start Form / Table. print qq{
\n}; print qq{\n}; print qq{

\n}; foreach my $type ( keys %groups ) { my $sth = $dbh->prepare("select lastname, firstname, studnum, email, par1_email, par2_email from student where $type = ? order by lastname, firstname"); my @group = @{ $groups{$type} }; foreach my $group ( @group ) { $sth->execute( $group ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my $first = 1; # Loop through the students while ( my ($lastname, $firstname, $studnum, $email, $par1_email, $par2_email ) = $sth->fetchrow ) { if ( $first ) { print qq{\n}; my $temptype = ucfirst($type); print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; $first = 0; } print qq{\n}; # NOTE: parent 1 is 1, parent 2 is 2 and student is 3 in # the values below prepended with studnum print qq{\n}; } # end of student loop print qq{
}; print qq{$temptype $group
$lex{Student}$lex{Student}
$lex{Email}		$lex{Parent} 1
$lex{Email}		$lex{Parent} 2
$lex{Email}
$lastname, $firstname ($studnum)}; if ( $email and $studmail ) { print qq{ $email}; } print qq{}; if ( $par1_email and $par1mail ) { print qq{ }; print qq{$par1_email}; } print qq{}; if ( $par2_email and $par2mail ) { print qq{}; print qq{$par2_email}; } print qq{
\n}; } # end of group (grade or homeroom value) } # end of all homerooms or grades. # Print Submit Row print qq{
\n}; print qq{

\n}; print qq{
\n}; exit; } #------------- sub showReport { #------------- #foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # Get Staff Member's Email my $userid = $ENV{'REMOTE_USER'}; my $sth = $dbh->prepare("select firstname, lastname, emailwork from staff where userid = ?"); $sth->execute( $userid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($firstname, $lastname, $staffemail) = $sth->fetchrow; if ( not $staffemail ) { print qq{

No Work Email Address Found. Please add to staff record

\n}; } print qq{

Staff Member $firstname $lastname - $staffemail

\n}; # NOTE: parent 1 is 1, parent 2 is 2 and student is 3 in # the values my %emailmap = ( 1 => 'par1_email', 2 => 'par2_email', 3 => 'email' ); my @email; # emails to send foreach my $key (keys %arr ) { my ($studnum, $index) = split(':', $key); my $field = $emailmap{$index}; # print "Field:$field Index:$index SN:$studnum my $sth = $dbh->prepare("select $field from student where studnum = ?"); $sth->execute( $studnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $emailaddress = $sth->fetchrow; push @email, $emailaddress; } my $em = join(',',@email); print "
Email To: $em
\n"; print qq{
}; print qq{[ Send Email ]
\n}; # Example: print qq{Email Confirmation}; print qq{\n}; exit; } # end of showReport =head #-------------------- sub selectReceipients { #-------------------- # foreach my $key (keys %arr ) { print "K:$key V:$arr{$key}
\n"; } print qq{

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

\n}; my $checked; if ( $arr{check} ) { $checked = qq{checked="checked"}; } delete $arr{check}; my $dbtype = 'mysql'; my %email; # open passed database and get student info. my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; my $sth = $dbh->prepare("select * from member where lastname is not NULL and lastname != '' order by lastname, firstname"); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $count = 1; while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; if ( not $r{email} ) { next; } $email{$r{email}} = {'lastname' => $r{lastname}, 'firstname' => $r{firstname}, db => $db, 'id' => $r{id} }; } # Start Form print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; my $count = 1; # Sort by db; my %sorted; foreach my $em ( keys %email ) { my $ln = $email{$em}{lastname}; my $fn = $email{$em}{firstname}; my $db = $email{$em}{db}; $sorted{"$db$ln$fn"} = $em; # print qq{LN:$ln FN:$fn DB:$db
\n}; } foreach my $key ( sort keys %sorted ) { my $em = $sorted{$key}; my %r = %{ $email{$em}}; print qq{\n}; $count++; } print qq{\n}; print qq{
Select Members
$count. \n}; print qq{$r{lastname}, $r{firstname}
\n}; print qq{\n}; exit; } =cut