#!/usr/bin/perl
#  Copyright 2001-2026 Treaty Six Education

#  This file is part of Open Admin for Schools.


my  %lex = ('Phone' => 'Phone',
	    'Report' => 'Report',
	    'Student' => 'Student',
	    'Main' => 'Main',
	    'Emergency' => 'Emergency',
	    'Parent 1' => 'Parent 1',
	    'Parent 2' => 'Parent 2',
	    'Student' => 'Student',
	    'Emergency' => 'Emergency',
	    'HRm' => 'HRm',
	    'Gr' => 'Gr',
	    'Work' => 'Work',
	    'Home' => 'Home',
	    'Cell' => 'Cell',
	    'Error' => 'Error',
	    'Blank=All' => 'Blank=All',
	    'Select by' => 'Select by',
	    'Continue' => 'Continue',
	    'Grade' => 'Grade',
	    'Homeroom' => 'Homeroom',
	    'Name' => 'Name',
	    'Group' => 'Group',
	    'Sort by' => 'Sort by',
	    );

use DBI;
use CGI;
use Cwd;

my $self = "rptphone.pl";

my $userid = $ENV{'REMOTE_USER'};

eval require "../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}

my $q = new CGI;
print $q->header( -charset, $charset );
my %arr = $q->Vars;

my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);


# Get current dir so know what CSS to display;
if ( getcwd() =~ /tcgi/ ){ # we are in tcgi
    $css = $tchcss;
    $homepage = $tchpage;
}


my $title = qq{$lex{Phone} $lex{Report}};
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 style="margin:1em;">\n};

print qq{[ <a href="$homepage">$lex{Main}</a> ] $userid\n};
print qq{<h1>$title</h1>\n};


if ( not $arr{page} ) {
    showStartPage();

} elsif ($arr{page} == 1 ) {
    delete $arr{page};
    showStudent();
    
} elsif ($arr{page} == 2 ) {
    delete $arr{page};
    showCourse();
} 


#-------------
sub showCourse {
#-------------
    
    # foreach my $key ( sort keys %arr ) { print "K:$key VAL:$arr{$key}<br>\n"; }
    
    # Get Students in this course.
    my $subjsec = $arr{subjsec};

    # Get course master info
    my $sth = $dbh->prepare("select * from subject where subjsec = ?");
    $sth->execute( $subjsec);
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    my $ref = $sth->fetchrow_hashref;
    my %c = %$ref;

    print qq{<h3>$c{description} ($c{subjsec})  Terms $c{startrptperiod} - $c{endrptperiod}</h3>\n};

    
    # Now get students;
    my (%sort, %name, %rec);
    my $sth = $dbh->prepare("select distinct e.studnum, s.* from eval e, student s
       where e.studnum = s.studnum and e.subjcode = ?");
    $sth->execute( $subjsec);
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    while (my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
	my $studnum = $r{studnum};
	$rec{$studnum} = $ref; # store the record
	
	my $fn = $r{firstname};
	my $ln = $r{lastname};
	$name{$studnum} = qq{<b>$r{lastname}</b>, $r{firstname} };
	$sort{"$ln$fn$studnum"} = $studnum;
    }

    print qq{<table border="1" cellpadding ="3" cellspacing="0" style="float:left;">\n};
    print qq{<tr><th>$lex{Student}</th><th>$lex{'Parent 1'}</th>\n};
    print qq{<th>$lex{'Parent 2'}</th><th>$lex{Emergency}</th></tr>\n};

    
    foreach my $key ( sort keys %sort ) {
	my $studnum = $sort{$key};
	my %r = %{$rec{$studnum}};

	
	print qq{<tr><td style="vertical-align:top;"><b>$r{lastname}</b>, $r{firstname} $r{initial}<br>\n};
	print qq{<b>$lex{HRm}</b> $r{homeroom} <b>$lex{Gr}</b> $r{grade}</td>\n};

	# Parent 1
	print qq{<td style="vertical-align:top;">};
	if ( $r{relation1} or $r{par1_lastname} ) {
	    print qq{<b>$r{relation1}</b><br>$r{par1_firstname} $r{par1_lastname}\n};
	}

	if ( $r{hphone1} ) {
	    print qq{<br>$lex{Home} $r{hphone1}};
	}
	if ( $r{wphone1} ) {
	    print qq{<br>$lex{Work} $r{wphone1}};
	}
	if ( $r{cell1} ) {
	    print qq{<br>$lex{Cell} $r{cell1}};
	}
	print qq{</td>\n};


	# Parent 2
	print qq{<td style="vertical-align:top;">};
	if ( $r{relation2} or $r{par2_lastname} ) {
	    print qq{<b>$r{relation2}</b><br>$r{par2_firstname} $r{par2_lastname}\n};
	}
	
	if ( $r{hphone2} ) {
	    print qq{<br>$lex{Home} $r{hphone2}};
	}
	if ( $r{wphone2} ) {
	    print qq{<br>$lex{Work} $r{wphone2}};
	}
	if ( $r{cell2} ) {
	    print qq{<br>$lex{Cell} $r{cell2}};
	}
	print qq{</td>\n};


	# Emergency
	print qq{<td style="vertical-align:top;">};
	if ( $r{emrelation} or $r{emname} ) {
	    print qq{<b>$r{emrelation}</b><br>$r{emname}\n};
	}
	if ( $r{emhphone} ) {
	    print qq{<br>$lex{Home} $r{emhphone}};
	}
	if ( $r{emwphone} ) {
	    print qq{<br>$lex{Work} $emwphone};
	}
	if ( $r{emcell} ) {
	    print qq{<br>$lex{Cell} $emcell};
	}
	print qq{</td></tr>\n};
  
    }

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

#-------------
sub showStudent {
#-------------

    # foreach my $key ( sort keys %arr ) { print "K:$key VAL:$arr{$key}<br>\n"; }

    if ( $arr{grade} ) {
	print qq{<h3>$lex{Grade} $arr{grade}</h3>\n};
    
    } elsif ( $arr{homeroom} ) {
	print qq{<h3>$lex{Homeroom} $arr{homeroom}</h3>\n};

    } else {
	print qq{<h3>Entire School</h3>\n};
    }


    my ($grp, $select, $sortorder);
    if ( $arr{grade} ) {
	$grp = $dbh->quote( $arr{grade} );
	$select = "where grade = $grp";
	$sortorder = "grade, lastname, firstname";
    
    } elsif ( $arr{homeroom} ) {
	$grp = $dbh->quote( $arr{homeroom} );
	$select = "where homeroom = $grp";
	$sortorder = "homeroom, lastname, firstname";
    
    } else {  # all school, no $select value
	$sortorder = "lastname, firstname";
	$grp = "grade";
    }

    # print qq{<div>Group:$grp Select:$select Sortorder:$sortorder</div>\n};

    my $sth = $dbh->prepare("select * from student $select order by $sortorder");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}

    my @students; # hold for course, if requested
    
    print qq{<table border="1" cellpadding ="3" cellspacing="0" style="float:left;">\n};
    print qq{<tr><th>$lex{Student}</th><th>$lex{'Parent 1'}</th>\n};
    print qq{<th>$lex{'Parent 2'}</th><th>$lex{Emergency}</th></tr>\n};

    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;

	push @students, $r{studnum};
	
	print qq{<tr><td style="vertical-align:top;"><b>$r{lastname}</b>, $r{firstname} $r{initial}<br>\n};
	print qq{<b>$lex{HRm}</b> $r{homeroom} <b>$lex{Gr}</b> $r{grade}</td>\n};

	# Parent 1
	print qq{<td style="vertical-align:top;">};
	if ( $r{relation1} or $r{par1_lastname} ) {
	    print qq{<b>$r{relation1}</b><br>$r{par1_firstname} $r{par1_lastname}\n};
	}

	if ( $r{hphone1} ) {
	    print qq{<br>$lex{Home} $r{hphone1}};
	}
	if ( $r{wphone1} ) {
	    print qq{<br>$lex{Work} $r{wphone1}};
	}
	if ( $r{cell1} ) {
	    print qq{<br>$lex{Cell} $r{cell1}};
	}
	print qq{</td>\n};


	# Parent 2
	print qq{<td style="vertical-align:top;">};
	if ( $r{relation2} or $r{par2_lastname} ) {
	    print qq{<b>$r{relation2}</b><br>$r{par2_firstname} $r{par2_lastname}\n};
	}
	
	if ( $r{hphone2} ) {
	    print qq{<br>$lex{Home} $r{hphone2}};
	}
	if ( $r{wphone2} ) {
	    print qq{<br>$lex{Work} $r{wphone2}};
	}
	if ( $r{cell2} ) {
	    print qq{<br>$lex{Cell} $r{cell2}};
	}
	print qq{</td>\n};


	# Emergency
	print qq{<td style="vertical-align:top;">};
	if ( $r{emrelation} or $r{emname} ) {
	    print qq{<b>$r{emrelation}</b><br>$r{emname}\n};
	}
	if ( $r{emhphone} ) {
	    print qq{<br>$lex{Home} $r{emhphone}};
	}
	if ( $r{emwphone} ) {
	    print qq{<br>$lex{Work} $emwphone};
	}
	if ( $r{emcell} ) {
	    print qq{<br>$lex{Cell} $emcell};
	}
	print qq{</td></tr>\n};
  
    }

    print qq{</table>\n};

    if ( $arr{showcourse} ) {

	my $sth = $dbh->prepare("select distinct subjcode from eval where studnum = ?");

	my %courses; # subjsec, like 8014-1
	
	# Get all the courses and create a sort by course name.
	foreach my $studnum ( @students ) {
	    $sth->execute($studnum);
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	    
	    while (my $subjsec = $sth->fetchrow ) {
		$courses{$subjsec} = 1;
	    }
	}

	my (%sort,%coursename);
	my $sth = $dbh->prepare("select description from subject where subjsec = ?");
	foreach my $subjsec ( keys %courses ) {
	    $sth->execute($subjsec);
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	    my $desc = $sth->fetchrow;
	    $sort{"$desc$subjsec"} = $subjsec;
	    $coursename{$subjsec} = $desc;
	}

	print qq{<table border="1" cellpadding ="5" cellspacing="0" style="float:left;margin-left:1em;">\n};
	print qq{<tr><th>Show Students in Courses</th></tr>\n};
	
	# now display courses;
	foreach my $key ( sort keys %sort ) {
	    my $subjsec = $sort{$key};
	    print qq{<tr><td>};
	    print qq{<form action="$self" method="post">\n};
	    print qq{<input type="hidden" name="page" value="2">\n};
	    print qq{<input type="hidden" name="subjsec" value="$subjsec">\n};
	    print qq{<input type="submit" value="$coursename{$subjsec} ($subjsec)">\n};
	    print qq{</form></td></tr>\n};
	}
	print qq{</table>\n};
	
    } # end of showcourses option
	
    print qq{</body></html>\n};

    exit;
    
} # end of showStudent



#----------------
sub showStartPage {
#----------------

    my @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;
    
    my @grades;
    my $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;
    
    
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    print qq{<div style="font-weight:bold;">Select Grade or Homeroom</div>\n};

    # Homeroom
    print qq{<div style="float:left;margin:0.5em;border:1px solid gray;padding:0.4em;">};
    print qq{<span style="font-weight:bold;font-size:120%;background-color:#CCC;padding:0.2em;">};
    print qq{$lex{Select} $lex{Homeroom}</span>\n};
    foreach my $hr (@homerooms) {
	print qq{<div style="margin:0.2em;"><input type="radio" name="homeroom" value="$hr"> $hr</div>};
    }

    # OR
    print qq{</div><div style="float:left;margin:1em 0;">OR</div>\n\n};

    # Grades
    print qq{<div style="float:left;margin:0.5em;border:1px solid gray;padding:0.4em;">};
    print qq{<span style="font-weight:bold;font-size:120%;background-color:#CCC;padding:0.2em;">};
    print qq{$lex{Select} $lex{Grade}</span>\n};
    foreach my $gr (@grades) {
	print qq{<div style="margin:0.2em;"><input type="radio" name="grade" value="$gr">$gr</div>};
    }

    # OR
    print qq{</div><div style="float:left;margin:1em 0;">OR</div>\n\n};
    
    print qq{<div style="float:left;margin:0.5em;border:1px solid gray;padding:0.4em;font-weight:bold;">};
    print qq{$lex{'Blank=All'}</div>\n};
    print qq{<br clear="left">\n};
    
    print qq{<div><input type="checkbox" name="showcourse" value="1"> Show Courses?</div>\n};
    print qq{<p><input style="margin:0 2em;" type="submit" value="$lex{Continue}"></p>};

    print qq{</form>\n};
    print qq{<br clear="left">\n};

    
    # MyCourses;
    my $sth1 = $dbh->prepare("select count(*) from eval where subjcode = ?");
    
    my $sth = $dbh->prepare("select * from subject where teacher = ? order by description");
    $sth->execute($userid);
    if ( $DBI::errstr ) {print $DBI::errstr; die $DBI::errstr; }

    my $first = 1;
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %c = %$ref;

	# any students enrolled?
	$sth1->execute($c{subjsec});
	if (my $count = $sth1->fetchrow ) { # we have enrollments

	    if ( $first ) { # we have courses with students
		print qq{<div style="float:left;margin:0.5em;border:1px solid gray;padding:0.4em;">};
		print qq{<span style="font-weight:bold;font-size:120%;background-color:#CCC;padding:0.2em;">};
		print qq{My Courses</span><br>\n};
		$first = 0;
	    }

	    print qq{<div style="padding:0.1em;"><form action="$self" method="post">\n};
	    print qq{<input type="hidden" name="page" value="2">\n};
	    print qq{<input type="hidden" name="subjsec" value="$c{subjsec}">\n};
	    print qq{<input type="submit" value="$c{description} ($c{subjsec})">\n};
	    print qq{</form></div>\n};

	}
	
    }
    
    if ( not $first ) {
	print qq{</div>\n};
    }
    

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

    exit;
}