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

# Export Contact Name, rather than Student Name

my %lex = ('Main' => 'Main',
	   'Export' => 'Export',
	   'Student' => 'Student',
	   'Staff' => 'Staff',
	   'Combine failed on input' => 'Combine failed on input',
	   'CSV File' => 'CSV File',
	   'Download' => 'Download',
	   'Note' => 'Note',
	   'Cannot open' => 'Cannot open',
	   'Error' => 'Error',
	   'Continue' => 'Continue',
	   
	   );


use DBI;
use CGI;
use Text::CSV_XS;

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

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

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



# Print Page Header
my $title = qq{$lex{Export} $lex{Student}/$lex{Staff} - Mathletics};
print qq{$doctype\n<html><head><title>$title</title>
<link rel="stylesheet" type="text/css" href="$css">
$chartype\n</head><body>\n};

print qq{[ <a href="$homepage">$lex{Main}</a> | \n};
print qq{<a href="$exppage">$lex{Export}</a> ]\n};

print qq{<h1>$title</h1>\n};


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

} else {
    delete $arr{page};
}


my $csv = Text::CSV_XS->new( {binary => 1} );

# Open output file
my $fileName = "mathletics$$.csv";
open (EX,">$fileName") || die $lex{'Cannot open'}. " Export file $fileName\n";



# Load Staff First my (%staff, %homeroom); $sth1 =
my $sth1 = $dbh->prepare("select field_value from staff_multi where 
			 field_name = 'homeroom' and userid = ?");

my $sth = $dbh->prepare("select lastname, firstname, sal, userid, emailwork, emailpersonal from staff");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
while ( my ($ln, $fn, $sal, $userid,$emailwork,$emailpersonal) = $sth->fetchrow ) {
    
    $sth1->execute($userid);
    if ($DBI::errstr){ print $DBI::errstr; die;}
    while ( my $hr = $sth1->fetchrow ) {
	$homeroom{$hr} = $userid;
    }

    $staff{$userid}{lastname} = $ln;
    $staff{$userid}{firstname} = $fn;
    $staff{$userid}{sal} = $sal;
    $staff{$userid}{emailwork} = $emailwork;
    
}



# Now do the students
my $sth = $dbh->prepare("select lastname, firstname, homeroom, grade from student 
  order by grade, homeroom, lastname,firstname");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die;}

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

    my %r = %$ref;
    
    foreach $key ( keys %r ) {
	$r{$key} =~ s/\n|\r/ /g;
    }  

    $labels[0] = $r{lastname};
    $labels[1] = $r{firstname};
    $labels[2] = $r{grade};
    $labels[3] = $r{homeroom};
    
    my $userid = $homeroom{$r{homeroom}};

    $labels[4] = $staff{$userid}{sal};
    $labels[5] = $staff{$userid}{firstname};
    $labels[6] = $staff{$userid}{lastname};

    if ( not $staff{$userid}{email} ) { $staff{$userid}{email} = $arr{defaultemail}; } # if set.
    $labels[7] = $staff{$userid}{email};

    
    if ($csv->combine(@labels)) {
	my $record = $csv->string;
	print EX $record, "\r\n";
    } else {
	my $err = $csv->error_input;
	print $lex{'Combine failed on input'}. ": ",$err,":\n\n";
    }
}

close EX;
system("mv $fileName $downloaddir");

print qq{<p><a href="$webdownloaddir/$fileName">\n};
print qq{<span tyle="font-size:120%;">$lex{Download} $lex{'CSV File'}</span></a></p>};

print qq{<i>$lex{Note}</i>: \n};
print qq{You should &quot;right click&quot; on this and save the file\n};

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



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

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    print qq{<div style="width:80ch;padding:0.5em; border:1px solid gray;margin:1em;">\n};
    print qq{Export staff and students to a CSV data file. };
    print qq{If there are any staff without email addresses set, };
    print qq{use the box below to fill in a default email address value</div>\n};

    print qq{<table cellpadding="3" cellspacing="0" border="0" };
    print qq{style="border:1px solid gray;padding:0.5em;">\n};
    
    print qq{<tr><td class="la">Fill Empty Staff Email with };
    print qq{<input type="text" style="width:20ch;" name="defaultemail"></td></tr>\n};
    print qq{<tr><td><input type="submit" value="$lex{Continue}"></td></tr>\n};
    print qq{</table>\n};
    print qq{</form>\n};

    
    # find homeroom teacher emails.
    my $sth = $dbh->prepare("select field_value, userid from staff_multi where 
			    field_name = 'homeroom'");

    my $sth1 = $dbh->prepare("select lastname, firstname, sal, emailwork, emailpersonal 
			     from staff where userid = ?");
    
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}

    my $first = 1;
    while ( my ($hr, $userid) = $sth->fetchrow ) {
	
	# Get Teacher Info
	$sth1->execute($userid);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my $ref = $sth1->fetchrow_hashref;
	my %r = %$ref;
#	print "REF:", %ref, "<br>REC:", %r, "<br>\n";
	
	if ( $first ) { # start table
	    print qq{<table cellpadding="3" cellspacing="0" border="1" };
	    print qq{style="margin:0.5em;">\n};
	    print qq{<caption style="font-size:120%;font-weight:bold;">};
	    print qq{Current Homeroom Teachers' Email Addresses</caption>\n};
	    print qq{<tr><th>Teacher</th><th>HRoom</th><th>Work Email</th>};
	    print qq{<th>Personal Email</th></tr>\n};
	    $first = 0;
	}

	print qq{<tr><td>$r{sal} $r{firstname} $r{lastname}</td><td class="cn">$hr</td>\n};
	if ( not $r{emailwork} ) { $r{emailwork} = qq{<span style="color:red;">Missing</span>}; }
	if ( not $r{emailpersonal} ) { $r{emailpersonal} = qq{<span style="color:red;">Missing</span>}; }
	
	print qq{<td>$r{emailwork}</td><td>$r{emailpersonal}</td></tr>\n};
    }

    
    if ( not $first ) { # close table;
	print qq{</table>\n};
    } else {
	print qq{<h3>No Homerooms Found</h3>\n};
    }
    
    print qq{</body></html>\n};

    exit;

}
