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


# Outline: TODO: Display stored formats (.fmt) files.
#  Also display all student fields to select. 


my %lex = ('Main' => 'Main',
	   'Export' => 'Export',
	   'Student' => 'Student',
	   'Field' => 'Field',
	   'Select' => 'Select',
	   'Combine failed on input' => 'Combine failed on input',
	   'CSV File' => 'CSV File',
	   'Download' => 'Download',
	   'Note' => 'Note',
	   'Save' => 'Save',
	   'Cannot open file' => 'Cannot open file',
	   'Error' => 'Error',
	   'No Selection' => 'No Selection'
	   );

my $self = 'expstudent.pl';

use DBI;
use CGI;
#use Text::CSV_XS;
use Text::CSV::Encoded;
# use Encode qw( is_utf8 );

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 $csv = Text::CSV::Encoded->new ({ encoding  => undef }); # accepts UTF8 marked strings
#my $csv = Text::CSV_XS->new( {binary => 1} );


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


# load all the fields of the student table
my @fields;
$sth = $dbh->prepare("select fieldid,arrayidx from meta
  where tableid = 'student' and fieldid != '' order by arrayidx");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
while ( my ($field,$idx) = $sth->fetchrow){
    push @fields, "$field:$idx";
}

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

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


if ($arr{writeflag}){
    delete $arr{writeflag};
    createExport();
}


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

# Sort Order # 1
print qq{<div style="position:absolute; padding: 1em; margin-top: 0; };
print qq{margin-left:40%; border: 1px solid gray; background-color: #BDF;">
<b>Record Sort</b>
<p>First Sort by: <select name="sort1"><option></option>\n};
foreach my $fld ( @fields ){
    my ($field,$idx) = split /:/,$fld;
    print qq{<option>$field</option>};
}
print qq{</select></p>\n};


# Sort Order # 2
print qq{<p>Then Sort by: <select name="sort2"><option></option>\n};
foreach my $fld ( @fields ){
    my ($field,$idx) = split /:/,$fld;
    print qq{<option>$field</option>};
}
print qq{</select></p>\n};


# Sort Order # 3
print qq{<p>Finally Sort by: <select name="sort3"><option></option>\n};
foreach my $fld ( @fields ){
    my ($field,$idx) = split /:/,$fld;
    print qq{<option>$field</option>};
}
print qq{</select></p></div>\n};


print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
print qq{<tr><th>$lex{Field}</th><th>$lex{Select}</th></tr>\n};
print qq{<tr><td colspan="2" class="cn">\n};
print qq{<input type="submit" value="$lex{Export}">};
#print qq{<input type="submit" name="save" value="". $lex{Save}. "">};
print qq{</td></tr>\n};


foreach my $fld (@fields){
    my ($field,$idx) = split /:/,$fld;
    print qq{<tr><td>$field</td><td><input type="checkbox" name="$fld" };
    print qq{value="1"></td></tr>\n}; 
}

print qq{<tr><td colspan="2" class="cn">\n};
print qq{<input type="submit" value="$lex{Export}">};
print qq{</td></tr></table>\n};

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



#---------------
sub createExport {  # create exported data for download
#---------------

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

    # Sort by 'sortorder'
    my $sortorder;
    for (1..3){
	my $sortname = 'sort'.$_;
	if ($arr{$sortname}){
	    $sortorder .= "$arr{$sortname},";
	}
	delete $arr{$sortname};
    }
    chop $sortorder; # remove trailing comma.
    if ($sortorder){ # if we have any sortorder, add first part.
	$sortorder = "order by $sortorder";
    }

    # Check for missing selected fields.... duh.
    if ( not %arr) { # no values passed...
	print qq{<h3>$lex{'No Selection'}</h3>\n};
	print qq{</body></html>\n};
	exit;
    }


    # Open output file (utf-8)
    $filename = "expstudent$$.csv";
    open (EX,">:encoding(UTF-8)", $filename) || die "$lex{'Cannot open file'} $filename\n";


    # build select string and array for reading.
    my $select;
    my @expfields;
    # Create the array
    foreach $key (keys %arr){
	my ($fld,$idx) = split /:/,$key;
	$expfields[$idx] = $fld;
    }

    # Now create the select
    foreach $val (@expfields){
	if (not $val){ next;}
	$select .= "$val,";
    }
    chop $select; # remove trailing comma


    # Get count
    $sth = $dbh->prepare("select count(*) from student");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    my $rows = $sth->fetchrow;

    # Get Records
    $sth = $dbh->prepare("select $select from student $sortorder");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}

    for ($i=1; $i <= $rows; ++$i) {

	my @arr = $sth->fetchrow;

	foreach $element (@arr) {  # strip CR/LF
	    $element =~ s/\n/ /g;
	    $element =~ s/\r/ /g;
	}

#	foreach my $val ( @arr ) {
#	    my $ok = is_utf8( $val, 1 );
#	    print qq{V:$val utf-8?:$ok<br>\n};
#	}


	if ($csv->combine(@arr)) {
	    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{<h1>[ <a href="$webdownloaddir/$filename">};
    print qq{$lex{Download} $lex{'CSV File'}</a> ]</h1>\n};

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

    exit;

} # End of createExport
