#!/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 = ('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 = 'explibdestiny.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);
$dbh->{mysql_enable_utf8} = 1;

# load all the fields of the student table
my $fields = "studnum, lastname, firstname, sex, homeroom, grade, birthdate";
my $allfields = 

my $title = "$lex{Export} $lex{Student} for Destiny Library Patron Import Converter";
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};

createExport();



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

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

    # Get Grad Year
    my ($startyear,$endyear) = split('-', $schoolyear);
    my $schoolyear = $endyear; # just for clarity.... 2017-2018 becomes 2018.
    
    my $districtid;
    if ( $dbase eq 'eagleview' or $dbase eq 'chieftaylor' or
	 $dbase eq 'pewasenakwan' or $dbase eq 'khwc' ) {
	$districtid = 'onionlake';
    }
    

    # Get Records
    $sth = $dbh->prepare("select * from student order by lastname, firstname");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}

    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
	$r{birthdate} =~ s/-//g; # strip hyphens from birthdate

	my $tempgrade = $r{grade}; # in case of K, PK, P3
	if ( $tempgrade eq 'K' ) { $tempgrade = 0; }
	if ( $tempgrade eq 'PK' ) { $tempgrade = -1; }
	if ( $tempgrade eq 'P3' ) { $tempgrade = -2; }
	my $delta = 12 - $tempgrade; # ie. 12 - grade 10 = 2 year delta.
	my $gradyear = $schoolyear + $delta;

#	print "School Year:$schoolyear TEMP: $tempgrade Delta:$delta GRadyear:$gradyear";

	my @fields = ( $dbase, $r{studnum}, $districtid, $r{lastname}, $r{firstname}, $r{sex}, 
		       $r{homeroom}, $r{grade}, $r{birthdate},'', $gradyear);
	
	foreach $key (keys %r) {  # strip CR/LF
	    $r{$key} =~ s/\n/ /g;
	    $r{$key} =~ 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( @fields )) {
	    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'} for $title</a> ]</h1>\n};

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

    exit;

} # End of createExport
