#!/usr/bin/perl
#  Copyright 2001-2018 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',
	   'Students Without Phones' => 'Students Without Phones',
	   'Students With Phones' => 'Students With Phones',
	   'Sorted by Family Size & Name' => 'Sorted by Family Size & Name',

	   );

my $self = 'rptfamily.pl';

use DBI;
use CGI;
use Cwd;


# Read config variables
eval require "../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. " $@<br>\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" || die "Cannot read the ppp.conf file!";
if ( $@ ) {
    print $lex{Error}. " $@<br>\n";
    die $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; }
#--------------


# Find all homephone1 groups. 
my $sth = $dbh->prepare("select distinct hphone1 from student 
  where hphone1 != '' and hphone1 is not null");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my @phones;
while ( my $hphone = $sth->fetchrow ) {
    push @phones, $hphone;
}

# Find all blank/null hphone1 students.
$sth = $dbh->prepare("select studnum from student 
  where hphone1 = '' or hphone1 is null 
  order by grade, homeroom,lastname, firstname");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my @nophone;
while ( my $sn = $sth->fetchrow ) {
    push @nophone, $sn;
}


# Now get family groupings by hphone1
# family groupings are in %family hash with {hphone1} -> @studnum array.

my (@family, @familysize,%familyname);
$sth = $dbh->prepare("select lastname, firstname, studnum, contact from student 
 where hphone1 = ? order by lastname, firstname");

foreach my $phone ( @phones ) {

    $sth->execute( $phone );
    if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }

    my ($famname, $prevname);

    while ( my ( $lastname, $firstname, $studnum, $contact  ) = $sth->fetchrow ) {
	$prevname = $lastname;

	if ( index($contact,$lastname) != -1 ) { # if lastname in contact name
	    $famname = $lastname;
	}
	    
	if ( @{$family{$phone}} ) {
	    push @{$family{$phone}}, $studnum;
	} else {
	    $family{$phone} = [ $studnum ];
	}
    }
    if ( not $famname ) { $famname = $prevname; } # last chance bailout to get famname

    my $famsize = $#{$family{$phone}};

    if ( not defined $familysize[$famsize] ) {
        $familysize[$famsize] = { };
    }
	
    $familysize[$famsize]->{"$famname:$phone"} = $phone;
    $familyname{$phone} = $famname;

}


my $title = $lex{'Family List'};

# print page head
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{$chartype\n</head><body><a name="top"></a>\n};

print qq{[ <a href="$homepage">$lex{Main}</a> ]\n};
print qq{ $currdate ($lex{'SE = Special Ed'})\n};
print qq{<h1>$title</h1>\n};
print qq{<h3>$lex{'Sorted by Family Size & Name'}</h3>\n};

# No Phone List
if ( @nophone ) {
    print qq{<h1>$lex{'Students Without Phones'}</h1>\n};
    print qq{<table border="1" cellpadding="3" cellspacing="0" style="margin-bottom:1em;">\n};
    print qq{<tr><th>$lex{Family}</th><th>$lex{Name}</th>};
    print qq{<th>$lex{Homeroom}</th><th>$lex{Grade}</th></tr>\n};

    my $sth = $dbh->prepare("select lastname, firstname, homeroom, grade from student 
     where studnum = ?");

    for my $studnum ( @nophone ) {

	$sth->execute( $studnum );
	if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname, $homeroom, $grade ) = $sth->fetchrow;	

	print qq{<tr><td></td><td>$firstname $lastname ($studnum)</td>\n};
	print qq{<td>$homeroom</td><td>$grade</td></tr>\n};

    }
    
    print qq{</table>\n};
    print qq{<h1>$lex{'Students With Phones'}</h1>\n};
}



print qq{<table border="1" cellpadding="3" cellspacing="0" style="margin-bottom:1em;">\n};
print qq{<tr><th>$lex{Family}</th><th>$lex{Name}</th>};
print qq{<th>$lex{Homeroom}</th><th>$lex{Grade}</th></tr>\n};

my $sth = $dbh->prepare("select lastname, firstname, homeroom, grade from student 
  where studnum = ?");


while ( my $familyHashRef = pop( @familysize ) ) {
    foreach my $key (sort keys %{ $familyHashRef } ) {
	my $phone = $familyHashRef->{$key};

	print qq{<tr><td colspan="4"><b>$familyname{$phone}</b> - $phone</td></tr>\n};
	foreach my $studnum ( @{ $family{$phone} } ) {
	    $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{<tr><td>$se</td><td>$firstname $lastname ($studnum)</td>\n};
	    print qq{<td>$homeroom</td><td>$grade</td></tr>\n};
	}

    }
}

print qq{</table></body></html>\n};
