#!/usr/bin/perl # Copyright 2001-2020 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 = ('Edit' => 'Edit', 'Teacher' => 'Teacher', 'Attendance' => 'Attendance', 'Main' => 'Main', 'Delete' => 'Delete', 'Error' => 'Error', 'Student' => 'Student', 'Grade' => 'Grade', 'Select' => 'Select', 'Records' => 'Records', 'Updated' => 'Updated', 'Continue' => 'Continue', 'All' => 'All', 'Fields' => 'Fields', 'Students' => 'Students', 'Homeroom' => 'Homeroom', 'Check' => 'Check', 'Next Page' => 'Next Page', 'Blank=All' => 'Blank=All', 'Name' => 'Name', 'Moved' => 'Moved', 'Not Found' => 'Not Found', 'Record' => 'Record', 'Group' => 'Group', 'Save' => 'Save', 'Months' => 'Months', ); use DBI; use CGI; my $self = 'tattEdit.pl'; eval require "../etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } eval require "../lib/libsession.pl"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $q = new CGI; my %arr = $q->Vars; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); print $q->header( -charset, $charset ); my $userid; # no need for session, just get userid. if ( not defined $arr{userid} ) { $userid = $ENV{'REMOTE_USER'}; } else { $userid = $arr{userid}; } # Print Page Header my $title = "$lex{Edit}/$lex{Delete} $lex{Attendance}"; print qq{$doctype\n$title\n}; print qq{\n}; if ( $arr{page} == 2 ) { # Setup for Calendar popup. print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; } print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} ] Userid:$userid\n}; print qq{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; selectStudents(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; delEditStudents(); # delete immediately, then show Edit forms. } elsif ( $arr{page} == 3 ) { delete $arr{page}; updateRecords(); # update any record edits. } #---------------- sub showStartPage { # Select Month or Student. #---------------- # get teacher's homerooms. my @homerooms; my $sth = $dbh->prepare("select field_value from staff_multi where userid = ? and field_name = 'homeroom'"); $sth->execute( $userid ); while ( my $hr = $sth->fetchrow ) { push @homerooms, $hr; } if ( not @homerooms ) { print qq{

No Homerooms Found

\n}; print qq{\n}; exit; } my $sth = $dbh->prepare("select studnum from student where homeroom = ?"); my $sth1 = $dbh->prepare("select distinct year(absdate), month(absdate) from attend where studentid = ?"); my (@students, %yrmo); # select attendance by student or year-month. foreach my $hr ( @homerooms ) { $sth->execute($hr); if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $studnum = $sth->fetchrow ) { push @students, $studnum; # get their year-month absences. $sth1->execute($studnum); if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ($year,$month) = $sth1->fetchrow ) { if ( length $month == 1 ) { $month = '0'. $month; } my $yrmo = qq{$year-$month}; $yrmo{$yrmo} = 1; } } } my $hrooms = join(',',@homerooms); # for passing to next script. # get sorted students and names. my (%sort, %name); my $sth = $dbh->prepare("select lastname, firstname from studentall where studnum = ?"); foreach my $studnum ( @students ) { $sth->execute($studnum); my ($lastname, $firstname) = $sth->fetchrow; $sort{"$lastname$firstname$studnum"} = $studnum; $name{$studnum} = qq{$lastname, $firstname}; } # Notes print qq{
If you select:
\n}; # Start the form. print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
\n}; # Display Students print qq{\n}; print qq{\n}; foreach my $key ( sort keys %sort ) { my $studnum = $sort{$key}; print qq{\n}; } print qq{
$lex{Select} $lex{Students}
$name{$studnum}
\n}; # Display Year-Months. print qq{\n}; print qq{}; foreach my $yrmo ( sort keys %yrmo ) { my ($yr,$mo) = split '-', $yrmo; print qq{\n}; } print qq{
$lex{Select} $lex{Months}
$month[$mo] $yr
\n}; # Continue print qq{
\n}; print qq{

\n}; print qq{
\n}; exit; } #----------------- sub selectStudents { #----------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # values passed:yrmo type is year-month, s is student; my $hrooms = $arr{homerooms}; delete $arr{homerooms}; my @homerooms = split(',',$hrooms); my (@yrmo, @students, %sort, %studname ); foreach my $key ( keys %arr ) { my ($type, $val) = split(':', $key); if ( $type eq 's' ) { push @students, $val; } if ( $type eq 'yrmo' ) { push @yrmo, $val; } } if ( not @students and not @yrmo ) { print qq{

No Selection

\n}; print qq{\n}; exit; } my $mode = 'student'; # student, month, both if ( @students and @yrmo ) { $mode = 'both'; } elsif ( @yrmo ) { $mode = 'month'; } # Students. if ( $mode eq 'month' ) { # no students selected; we'll need all students for search by month my $sth = $dbh->prepare("select studnum from student where homeroom = ?"); foreach my $hr ( @homerooms ) { $sth->execute($hr); if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $studnum = $sth->fetchrow ) { push @students, $studnum; } } } # get names, sort order. my $sth = $dbh->prepare("select lastname, firstname from studentall where studnum = ?"); foreach my $studnum ( @students ) { $sth->execute( $studnum ); if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname ) = $sth->fetchrow; $sort{"$lastname$firstname$studnum"} = $studnum; $studname{$studnum} = "$lastname, $firstname"; } # Students and Year-Month. my %attendance; # attendance{studnum}{date}{id}; if ( $mode eq 'both' or $mode eq 'month' ) { my $sth = $dbh->prepare("select attid, absdate from attend where studentid = ? and year(absdate) = ? and month(absdate) = ?"); foreach my $studnum ( @students ) { foreach my $yrmo ( @yrmo ) { my ($year,$month) = split('-', $yrmo); $sth->execute( $studnum, $year, $month ); if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ($id, $date) = $sth->fetchrow ) { $attendance{$studnum}{$date}{$id} = 1; } } } } else { # Students Only; no months selected; all records for each. my $sth = $dbh->prepare("select attid, absdate from attend where studentid = ? order by absdate"); foreach my $studnum ( @students ) { $sth->execute( $studnum ); if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($id, $date) = $sth->fetchrow; $attendance{$studnum}{$date}{$id} = 1; } } # end of students only =head foreach my $studnum ( sort keys %attendance ) { foreach my $date ( sort keys %{ $attendance{$studnum} } ) { foreach my $id ( sort {$a <=> $b} keys %{ $attendance{$studnum}{$date} } ) { print "SN:$studnum Date:$date ID:$id
\n"; } } } =cut # Start the form. print qq{
\n}; print qq{\n}; # Get Records for Edit/Delete my $first = 1; my $count = 1; my $sth = $dbh->prepare("select * from attend where attid = ?"); # Loop over all students. foreach my $key ( sort keys %sort ) { my $studnum = $sort{$key}; foreach my $date ( sort keys %{ $attendance{$studnum} } ) { foreach my $id ( sort {$a <=> $b} keys %{ $attendance{$studnum}{$date} } ) { # print qq{Studnum:$studnum Date:$date ID:$id
\n}; if ( $first ) { # start table layout. print qq{
\n}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; $first = 0; } $sth->execute( $id ); if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $ref = $sth->fetchrow_hashref; my %r = %$ref; print qq{}; print qq{\n}; print qq{\n}; print qq{\n}; $count++; if ( $count % 10 == 0 ) { # put in title line print qq{}; print qq{\n}; } } # end of id loop } # end of date loop } # end of student loop print qq{
No Confirmation on Delete. Immediately Removed.
$lex{Student}EditDeleteDate/PeriodReason
$count. $studname{$studnum} ($studnum)$r{absdate} / $r{period}$r{reason}
$lex{Student}EditDeleteDate/PeriodReason
\n}; print qq{
\n}; print qq{
\n}; print qq{\n}; } # end of selectStudents / months #------------------ sub delEditStudents { #------------------ # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # setup selects my $sth = $dbh->prepare("select * from attend where attid = ?"); my $sth1 = $dbh->prepare("delete from attend where attid = ?"); my $sth2 = $dbh->prepare("select lastname, firstname, grade from studentall where studnum = ?"); print qq{

Delete Attendance Records

\n}; my $first = 1; foreach my $id ( keys %arr ) { if ( $arr{$id} eq 'D' ) { # delete # get record $sth->execute($id); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $ref = $sth->fetchrow_hashref; my %r = %$ref; # get name, grade (not needed yet) $sth2->execute( $r{studentid} ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($lastname,$firstname, $grade) = $sth2->fetchrow; # delete record from attend table. $sth1->execute($id); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } print qq{
Deleted $firstname $lastname on $r{absdate}, period $r{period} }; print qq{for $r{reason}
\n}; # delete from %arr hash delete $arr{$id}; } } if ( $first ) { print qq{

No Attendance Deletion

\n}; } # now do the edits. print qq{\n\n

Edit Attendance Records

\n}; my $first = 1; my %absdate; # $absdate{$id} = $absdate foreach my $id ( keys %arr ) { if ( $arr{$id} eq 'E' ) { # edit, likely redundant since deletes are done. # get record $sth->execute($id); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $ref = $sth->fetchrow_hashref; my %r = %$ref; $absdate{$id} = $r{absdate}; # used below for calendar # get name, grade $sth2->execute( $r{studentid} ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($lastname,$firstname, $grade) = $sth2->fetchrow; # grade used below if ( $first ) { # start the form print qq{
\n}; print qq{\n}; $first = 0; } print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; my $ppd = $g_ppd{$grade}; # attendance periods from grade of student; g_ppd is from conf print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
Name$lastname, $firstname ($r{studentid})
Date }; print qq{\n}; print qq{
Reason
Period
Minutes Late
\n\n}; } # end of if an edit } # end of edit loop if ( not $first ) { # we had some edits. print qq{\n}; print qq{
\n}; } else { print qq{

No Attendance Edits

\n}; } # now calendar javascript foreach my $id ( keys %arr ) { if ( $arr{$id} eq 'E' ) { # edit, likely redundant since deletes are done. print qq{\n}; } # end of IF } # end of %arr loop print qq{\n}; exit; } #---------------- sub updateRecords { #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } foreach my $key ( sort keys %arr ) { my ( $id, $field ) = split(':', $key); # Update Record my $sth = $dbh->prepare("update attend set $field = ? where attid = ?"); $sth->execute( $arr{$key}, $id ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } print qq{
$id - $field - $arr{$key} Updated
\n}; } print qq{

$lex{Records} $lex{Updated}

\n}; print qq{

[ $lex{Edit}/$lex{Delete} Attendance}; print qq{ | $lex{Main} ]

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