#!/usr/bin/perl
# Copyright 2001-2021 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 = ('Family List' => 'Family List',
'Main' => 'Main',
'Family' => 'Family',
'Name' => 'Name',
'Homeroom' => 'Homeroom',
'Grade' => 'Grade',
'Error' => 'Error',
'SE = Special Ed' => 'SE = Special Ed',
'SE' => 'SE',
'Sorted by Family Size & Name' => 'Sorted by Family Size & Name',
'Continue' => 'Continue',
);
my $self = 'rptfamily.pl';
use DBI;
use CGI;
use Cwd;
# Read config variables
eval require "../etc/admin.conf";
if ( $@ ) {
print $lex{Error}. " $@ \n";
die $lex{Error}. " $@\n";
}
my $q = new CGI;
my %arr = $q->Vars;
print $q->header( -charset, $charset );
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";
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
$dbh->{mysql_enable_utf8} = 1;
#--- Now do the IEP Database -------
my $schooldbase = $dbase; # just in case...
# don't move this up...
eval require "$iepdir/cgi/ppp.conf";
if ( $@ ) {
print qq{$lex{Error} $@ \n};
die qq{$lex{Error} $@\n};
}
$dsniep = "DBI:$dbtype:dbname=$dbase";
$dbhiep = DBI->connect($dsniep,$user,$password);
# use to find if student is in special ed.
my $sthiep = $dbhiep->prepare("select count(*) from special where studnum = ?");
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
#--------------
# Create meta hash for fieldnames from meta.
my $sth = $dbh->prepare("select fieldid, fieldname from meta where tableid = ?");
$sth->execute( 'student' );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my %meta;
while ( my ( $fieldid, $fieldname, $required ) = $sth->fetchrow ) {
$meta{$fieldid} = $fieldname;
}
# Get the ID value for g_FamilyGroupListList variable in conf_system
my $sth = $dbh->prepare("select id from conf_system where dataname = ?");
$sth->execute('g_FamilyGroupLinkList');
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $listid = $sth->fetchrow;
my $linksize = scalar @g_FamilyGroupLinkList;
print qq{$doctype\n$title\n};
print qq{\n};
print qq{$chartype\n\n};
print qq{[ $lex{Main} ]\n};
print qq{ $currdate ($lex{'SE = Special Ed'})\n};
print qq{
$title
\n};
if ( not $arr{groupFld} and $linksize > 1 ) { # put up variable to select.
print qq{
};
print qq{
\n};
# Also allow for an edit of the family values
print qq{
};
print qq{Edit Family Fields that join Students into Families/Provide unified Family Name \n};
print qq{ (new tab)
\n};
# Reset Bus Routes
print qq{
};
print qq{ (new tab)
\n};
print qq{\n};
exit;
}
my $groupFld; # field used for grouping into families.
if ( $arr{groupFld} ) {
$groupFld = $arr{groupFld};
} else { # only 1 value in @g_FamilyGroupLinkList; use it.
$groupFld = $g_FamilyGroupLinkList[0];
}
# A field to give a family name rather than an appended list of lastnames a/b/c.
my $altNameField = $g_FamilyGroupNameField;
# Find all groups.
my $sth = $dbh->prepare("select distinct $groupFld from student
where $groupFld != '' and $groupFld is not null");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my @groupFields;
while ( my $fld = $sth->fetchrow ) {
push @groupFields, $fld; #
}
# Find all blank/null grouped students.
$sth = $dbh->prepare("select studnum from student
where $groupFld = '' or $groupFld is null
order by grade,homeroom,lastname, firstname");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my @nogroup;
while ( my $sn = $sth->fetchrow ) {
push @nogroup, $sn;
}
# get the groups (families) based on grouping field chosen
my $sth = $dbh->prepare("select distinct $groupFld from student
where $groupFld != '' and $groupFld is not null");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my @groupFields;
while ( my $fld = $sth->fetchrow ) {
push @groupFields, $fld;
}
# Now get family groupings by field chosen
# family groupings are in %family hash with {groupFld} -> @studnum array.
my (@family, %familysize,%familyname,%altfamilyname);
$sth = $dbh->prepare("select lastname, firstname, studnum, $altNameField from student
where $groupFld = ? order by lastname, firstname");
foreach my $gfield ( @groupFields ) {
$sth->execute( $gfield );
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my %familynames;
# All of these people have the same group name / 'family' name
while ( my ( $lastname, $firstname, $studnum, $altname ) = $sth->fetchrow ) {
if ( $altname ) { $altfamilyname{$gfield} = $altname; };
$familynames{$lastname} = 1; # all lastnames into a hash.
# family{$gfield}[ studnums ];
if ( @{$family{$gfield}} ) { # that family already has studnums.
push @{$family{$gfield}}, $studnum;
} else {
$family{$gfield} = [ $studnum ];
}
}
my $famname = join('/', keys %familynames);
$familyname{$gfield} = $famname;
my $childcount = scalar @{ $family{$gfield} };
$familysize{$childcount}{$famname}{$gfield} = $gfield;
}
my $title = $lex{'Family List'};
# print page head
#print qq{$doctype\n$title\n};
#print qq{\n};
#print qq{$chartype\n\n};
print qq{
Group Students by $meta{$groupFld} ($groupFld)
\n};
my $sth = $dbh->prepare("select lastname, firstname, homeroom, grade from student
where studnum = ?");
my $tablecount;
foreach my $childcount ( sort {$b <=> $a} keys %familysize ) {
my $first = 1;
foreach my $famname ( sort keys %{ $familysize{$childcount} } ) {
foreach my $gfield ( sort keys %{ $familysize{$childcount}{$famname} } ) {
if ( $first ) {
my $childvar = 'Children';
if ( $childcount == 1 ) { $childvar = 'Child'; }
print qq{
\n};
foreach my $studnum ( @{ $family{$gfield} } ) {
$sth->execute($studnum);
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my ($lastname, $firstname, $homeroom, $grade ) = $sth->fetchrow;
# Set $se for Special Ed.
$sthiep->execute( $studnum ); # Is child in Special Ed?
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my $iepcount = $sthiep->fetchrow;
my $se;
if ( $iepcount > 0 ) {
$se = '*'. $lex{SE}. '*';
}
print qq{
$se
$firstname $lastname ($studnum)
\n};
print qq{
$homeroom
$grade
\n};
}
} # $gfield
} # family name
print qq{
\n};
$tablecount++;
if ($tablecount % 3 == 0 ) {
print qq{ \n};
}
} # childcount
print qq{\n};
# No Grouping Field List
if ( @nogroup ) {
print qq{
\n};
print qq{
$lex{Family}
$lex{Name}
};
print qq{
$lex{Homeroom}
$lex{Grade}
\n};
print qq{
};
print qq{Students without Grouping Field $meta{$groupFld}
\n};
my $sth = $dbh->prepare("select lastname, firstname, homeroom, grade from student
where studnum = ?");
for my $studnum ( @nogroup ) {
$sth->execute( $studnum );
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my ($lastname, $firstname, $homeroom, $grade ) = $sth->fetchrow;
print qq{