#!/usr/bin/perl
#  Copyright 2001-2017 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.


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} );


createSchoolData();
createStudentData();
createTeachers();
createAdmins();
createSections();
createEnrollments();


#-------------------
sub createSchoolData {  # create school file
#-------------------

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

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


    # Do Header Section
    @head = ('School_id','School_name', 'School_number');
    if ($csv->combine(@head)) {
	my $record = $csv->string;
	print EX $record, "\r\n";
    } else {
	my $err = $csv->error_input;
	print $lex{'Combine failed on input'}. ": $err\n\n";
    }


    # Loop through all schools and add to file
    # Load the data first.
    eval require "/opt/openadmin/central/cgi/admin.conf";
    if ( $@ ) {
	print $lex{Error}. " $@<br>\n";
	die $lex{Error}. " $@\n";
    }

    my %codes = reverse %schoolcodes_to_dbase;

    foreach my $db ( keys %alldbase ) {
	my $code = %codes{$db};
	my $name = $alldbase{$db};

	my @vals = ($code,$name,$code);
	
	if ($csv->combine(@vals)) {
	    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;

    return;

} # End of createSchoolData


#-------------------
sub createStudentData {  # create students.csv file
#-------------------

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


    # Loop through all schools and add to file
    # Load the data first.
    eval require "/opt/openadmin/central/cgi/admin.conf";
    if ( $@ ) {
	print $lex{Error}. " $@<br>\n";
	die $lex{Error}. " $@\n";
    }

    my %codes = reverse %schoolcodes_to_dbase;

    my (%mastername, %data); # hold all values to check for collisions.

    
    foreach my $dbase ( keys %alldbase ) {
	
	# my $schoolid = $codes{$dbase};
	# my $schoolname = $alldbase{$db};

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


	my $sth = $dbh->prepare("select lastname, firstname, initial, studnum, password, birthdate, grade 
           from student  order by lastname, firstname");
	$sth->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}

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

	    my @fn = split(/\s+/, $r{firstname} );
	    $r{firstname} = $fn[0]; # only use the first name of first name.
	    
	    $r{firstname} =~ s/^\s+//;
	    $r{firstname} =~ s/\s+$//;
	    $r{firstname} =~ s/\W|-//g; # remove any non letters or dashes.

	    $r{lastname} =~ s/^\s+//;
	    $r{lastname} =~ s/\s+$//;
	    my @ln = split(/\s+/, $r{lastname});
	    $r{lastname} = $ln[0];
	    $r{lastname} =~ s/\W|-//g;
	
	    $r{initial} =~ s/^\s+//;
	    $r{initial} =~ s/\s+$//;

	    $sref->{lastname} = $r{lastname};
	    $sref->{firstname} = $r{firstname};

	    my $name = "$r{lastname},$r{firstname}";

	    if ( $mastername{$name} ) { # we have a collision.

		# This is already present in mastername 
		my ($db,$studnum) = split('-', $mastername{$name});
		my $ref = $data{$db}{$studnum};
		my %cr = %$ref;

		if ( $cr{birthdate} eq $r{birthdate} and $r{birthdate} ) { # ok, same person
		    next;
		}
		if ( $dbase eq 'chieftaylor' and $db eq 'pewasenakwan' ) { # remove pewa record; put in chieftaylor
		    $mastername{$name} = "$dbase-$r{studnum}";
		    $data{$dbase}{$r{studnum}} = $sref; # save everything;
		    next;
		}

		# If here, then we have to create 2 mastername records, one for each student;
		# First the original in %mastername;
		my $originalname = $name;

		my @mn = split(/\s+/, $r{initial}); # split on spaces.
		my $firstmiddlename = $mn[0]; # only take the first one.

	    
		# Now the second student, not yet in mastername hash
		my @cmn = split(/\s+/, $cr{initial}); # split on spaces.
		my $secondmiddlename = $cmn[0]; # only take the first one.

		if ( $firstmiddlename eq $secondmiddlename and $firstmiddlename ) { # same student;
		    print qq{\n Same Middle Name! $firstmiddlename - $r{firstname} $r{lastname}\n\n};
		}

		# reset first name
		my $newname = $name. ",$firstmiddlename";
		$mastername{$newname} = $mastername{$name};
		delete $mastername{$name};

		# Add Second name to mastername;
		my $newname = $name. ",$secondmiddlename";
		$mastername{$newname} = "$dbase-$r{studnum}";
	    
	    
		$collisioncount++;
	    
	    } else { # add to mastername
		$mastername{$name} = "$dbase-$r{studnum}";
	    }

	    # Save everything.
	    $data{$dbase}{$r{studnum}} = $sref; # save everything;
	    

	} # end of student loop

    } # end of school database loop.

    
    # Now put into a CSV file.

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


    # Do Header Section
    @head = ('School_id','Student_id', 'Last_name', 'First_name', 'Username','Password');
    if ($csv->combine(@head)) {
	my $record = $csv->string;
	print EX $record, "\r\n";
    } else {
	my $err = $csv->error_input;
	print $lex{'Combine failed on input'}. ": $err\n\n";
    }

    
    foreach my $key ( sort keys %mastername ) {

	my $val = $mastername{$key};

	my ($db, $studnum) = split('-', $val);

	my $schoolid = $codes{$db};
	    
	my $ref = $data{$db}{$studnum};
	my %r = %$ref;
    
	my ($ln,$fn) =  split(',',$key);
	my $emailname = "$ln$fn";
	$username = $emailname. '@tsec.ca';

	my $studentid = "$db$r{studnum}";
    
	my @vals = ($schoolid, $studentid, $ln,$fn, $username, $r{password});    
   
	if ($csv->combine(@vals )) {
	    my $record = $csv->string;
	    print EX $record, "\r\n";
	} else {
	    my $err = $csv->error_input;
	    print "Combine failed on input:$err\n\n";
	}
    }
	

    close EX;
	
    return;

} # End of create Student Data



#-----------------
sub createTeachers {  # create teachers.csv file
#-----------------

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


    # Loop through all schools and add to file
    # Load the data first.
    eval require "/opt/openadmin/central/cgi/admin.conf";
    if ( $@ ) {
	print $lex{Error}. " $@<br>\n";
	die $lex{Error}. " $@\n";
    }

    my %codes = reverse %schoolcodes_to_dbase;


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


    # Do Header Section
    @head = ('School_id','Teacher_id', 'Teacher_email', 'First_name', 'Last_name',);
    if ($csv->combine(@head)) {
	my $record = $csv->string;
	print EX $record, "\r\n";
    } else {
	my $err = $csv->error_input;
	print $lex{'Combine failed on input'}. ": $err\n\n";
    }


    
    foreach my $dbase ( keys %alldbase ) {
	
	my $schoolid = $codes{$dbase};

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


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

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

	    my $teacherid = "$dbase-$r{userid}";
	    my $email = $r{email};
	    if ( not $email ) { $email = "$r{lastname}$r{firstname}\@tsec.ca"; }
	    
    	    my @vals = ($schoolid, $teacherid, $email, $r{firstname}, $r{lastname});    
   
	    if ($csv->combine(@vals )) {
		my $record = $csv->string;
		print EX $record, "\r\n";
	    } else {
		my $err = $csv->error_input;
		print "Combine failed on input:$err\n\n";
	    }
	}
	
    } # end of database

    close EX;
	
    return;

} # End of createTeachers



#-----------------
sub createAdmins {  # create admins.csv file
#-----------------

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


    # Loop through all schools and add to file
    # Load the data first.
    eval require "/opt/openadmin/central/cgi/admin.conf";
    if ( $@ ) {
	print $lex{Error}. " $@<br>\n";
	die $lex{Error}. " $@\n";
    }

    my %codes = reverse %schoolcodes_to_dbase;


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


    # Do Header Section
    @head = ('School_id','Staff_id', 'Admin_email', 'First_name', 'Last_name',);
    if ($csv->combine(@head)) {
	my $record = $csv->string;
	print EX $record, "\r\n";
    } else {
	my $err = $csv->error_input;
	print $lex{'Combine failed on input'}. ": $err\n\n";
    }


    foreach my $dbase ( keys %alldbase ) {
	
	my $schoolid = $codes{$dbase};

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


	my $sth = $dbh->prepare("select s.* from staff s, staff_multi sm where s.userid = sm.userid and
          field_name = 'position' and ( field_value = 'Principal' or field_value = 'Vice-Principal' )");
	$sth->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}

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

	    my $teacherid = "$dbase-$r{userid}";
	    my $email = $r{email};
	    if ( not $email ) { $email = "$r{lastname}$r{firstname}\@tsec.ca"; }
	    
    	    my @vals = ($schoolid, $teacherid, $email, $r{firstname}, $r{lastname});    
   
	    if ($csv->combine(@vals )) {
		my $record = $csv->string;
		print EX $record, "\r\n";
	    } else {
		my $err = $csv->error_input;
		print "Combine failed on input:$err\n\n";
	    }
	}
	
    } # end of database

    close EX;
	
    return;

} # End of createAdmins



#-----------------
sub createSections {  # create sections.csv file
#-----------------

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


    # Loop through all schools and add to file
    # Load the data first.
    eval require "/opt/openadmin/central/cgi/admin.conf";
    if ( $@ ) {
	print $lex{Error}. " $@<br>\n";
	die $lex{Error}. " $@\n";
    }

    my %codes = reverse %schoolcodes_to_dbase;


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


    # Do Header Section
    @head = ('School_id','Section_id', 'Teacher_id', 'Course_name',);
    if ($csv->combine(@head)) {
	my $record = $csv->string;
	print EX $record, "\r\n";
    } else {
	my $err = $csv->error_input;
	print $lex{'Combine failed on input'}. ": $err\n\n";
    }


    foreach my $dbase ( keys %alldbase ) {
	
	my $schoolid = $codes{$dbase};

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


	
	my $sth1 = $dbh->prepare("select count(*) from eval where subjcode = ?");

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

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

	    # Check for enrollments, and skip if none.
	    $sth1->execute( $r{subjsec} );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	    my $count = $sth1->fetchrow;
	    if ( not $count ) { next; } # skip, no enrollments
	    
	    
	    my $teacherid = "$dbase-$r{teacher}";
	    
    	    my @vals = ($schoolid, $r{subjsec}, $teacherid, $r{description} );    
   
	    if ($csv->combine(@vals )) {
		my $record = $csv->string;
		print EX $record, "\r\n";
	    } else {
		my $err = $csv->error_input;
		print "Combine failed on input:$err\n\n";
	    }
	}
	
    } # end of database

    close EX;
	
    return;

} # End of createSections



#--------------------
sub createEnrollments {  # create enrollments.csv file
#--------------------

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


    # Loop through all schools and add to file
    # Load the data first.
    eval require "/opt/openadmin/central/cgi/admin.conf";
    if ( $@ ) {
	print $lex{Error}. " $@<br>\n";
	die $lex{Error}. " $@\n";
    }

    my %codes = reverse %schoolcodes_to_dbase;


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


    # Do Header Section
    @head = ('School_id','Section_id', 'Student_id');
    if ($csv->combine(@head)) {
	my $record = $csv->string;
	print EX $record, "\r\n";
    } else {
	my $err = $csv->error_input;
	print $lex{'Combine failed on input'}. ": $err\n\n";
    }


    foreach my $dbase ( keys %alldbase ) {
	
	my $schoolid = $codes{$dbase};

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


	
	my $sth = $dbh->prepare("select distinct subjcode, studnum from eval");
	$sth->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}

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

	    my $studentid = "$dbase-$r{studnum}";
	    
    	    my @vals = ($schoolid, $r{subjcode}, $studentid );    
   
	    if ($csv->combine(@vals )) {
		my $record = $csv->string;
		print EX $record, "\r\n";
	    } else {
		my $err = $csv->error_input;
		print "Combine failed on input:$err\n\n";
	    }
	}
	
    } # end of database

    close EX;
	
    return;

} # End of createEnrollments