#!/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.

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}. " $@<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);


#--- 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} $@<br>\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;

my $title = qq{Family Report - group by selected fields};
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};


if ( not $arr{groupFld} and $linksize > 1 ) { # put up variable to select.
    print qq{<div style="padding:1em;border:1px solid gray;margin:0.5em;width:60ch;">};
    print qq{<form action="$self" method="post">\n};
    print qq{Select Family Link Field <select name="groupFld">}; #<option></option>};
    foreach my $fld ( @g_FamilyGroupLinkList ) {
	print qq{<option value="$fld">$meta{$fld}</option>};
    }
    print qq{</select>\n <input type="submit" value="$lex{Continue}"></form></div>\n};

    # Also allow for an edit of the family values
    print qq{<div style="padding:1em;border:1px solid gray;margin:0.5em;width:60ch;};
    print qq{background-color:#DDD;">};
    print qq{Edit Family Fields that join Students into Families/Provide unified Family Name \n};
    print qq{<form action="/cgi-bin/configure/confedit.pl" method="post" };
    print qq{target="_blank" style="display:inline;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="hidden" name="filename" value="admin">\n};
    print qq{<input type="hidden" name="sectionname" value="family">\n};
    print qq{<input type="submit" value="Configure"></form> (new tab)</div>\n};

    # Reset Family Field
    print qq{<div style="padding:1em;border:1px solid gray;margin:0.5em;width:60ch;};
    print qq{background-color:#DDD;">};
    print qq{<form action="/cgi-bin/eoy/resetselect.pl" method="post" target="_blank" };
    print qq{style="display:inline;">\n};
    print qq{<input type="hidden" name="inputtype" value="text">\n};
    print qq{<select name="field">};
    foreach my $fld ( @g_FamilyGroupLinkList ) {
	print qq{<option value="$fld">$meta{$fld}</option>};
    }
    print qq{</select>\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value="Reset Family Field"></form> (new tab)</div>\n};

    print qq{</body></html>\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<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{<h3>Group Students by $meta{$groupFld} ($groupFld)</h3>\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{<table border="1" cellpadding="3" cellspacing="0" };
		print qq{style="margin-bottom:1em;float:left;margin:1em;">\n};
		print qq{<caption style="font-weight:bold;">$childcount $childvar</caption>\n};
		print qq{<tr><th>$lex{Family}</th><th>$lex{Name}</th>};
		print qq{<th>$lex{Homeroom}</th><th>$lex{Grade}</th></tr>\n};
		$first = 0;
	    }


	    my $famname;
	    if ( $altfamilyname{$gfield} ) {
		$famname = $altfamilyname{$gfield};
	    } else {
		$famname = $familyname{$gfield};
	    }
	    print qq{<tr style="background-color:#DDD;"><td colspan="4">};
	    print qq{<b>$famname</b> - $meta{$groupFld} $gfield</td></tr>\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{<tr><td>$se</td><td>$firstname $lastname ($studnum)</td>\n};
		print qq{<td>$homeroom</td><td>$grade</td></tr>\n};
	    }
	} # $gfield
    } # family name
    print qq{</table>\n};
    
    $tablecount++;
    if ($tablecount % 3 == 0 ) {
	print qq{<br clear="left">\n};
    }

    
} # childcount


# No Grouping Field List
if ( @nogroup ) {
   
    print qq{<table border="1" cellpadding="3" cellspacing="0" };
    print qq{style="margin-bottom:1em;float:left;color:red;">\n};
    print qq{<tr><th>$lex{Family}</th><th>$lex{Name}</th>};
    print qq{<th>$lex{Homeroom}</th><th>$lex{Grade}</th></tr>\n};

    print qq{<caption style="font-size:120%;font-weight:bold;">};
    print qq{Students without Grouping Field $meta{$groupFld}</caption>\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{<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{</body></html>\n};
