#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::BasicRead;
	my @outcsv;
	my @classes;	
	our $Header_row;
my $OUTTXT = "C:\\Temp\\stats.txt";
open( OUTTXT, '>', "$OUTTXT" ) or die "oops $OUTTXT not found \n";
#================================= Set-up File 
my $xlsx_WSD = ( "C:\\Temp\\data.xlsx"),, 1;
my @csvtemp;
		if ( -e $xlsx_WSD ) {
my $ss   = new Spreadsheet::BasicRead($xlsx_WSD) or die;
    my $col = '';
    my $row  = 0;
    while ( my $data = $ss->getNextRow() ) {
        $row++;
        $col= join( "\t", @$data );
			push @csvtemp,  $col . "\n" if ( $col ne "" );
    }
}	
my @arraynew;
my %seen;
 $Header_row = shift (@csvtemp);  # this is where the headers are stored.
our $pupilcount=0;
  foreach (@csvtemp){
chomp;
	 $_ = uc $_ ;
     $_ =~ s/,//g;										#removes commas
	 $_ =~ s/\t/,/g;									#change from tab to  csv
		#	print $_ . "\n" if !$seen{$_}++ ;
			push @arraynew, $_ . "\n" if !$seen{$_}++ ;    #remove any dupes
			$pupilcount ++;
}	
	$Header_row =~ s/\t/,/g ;
#-----------------------------------   get  data	---------------------------------------------
			printf OUTTXT "\n\t\t Totals";
my %seena;
	foreach (@arraynew)
{
			chomp;
my $line = $_;
my @elements = split (",", $line);
my $col_name = $elements[0];
		$col_name="" if(!$col_name);# I do this so warnings will not complain
					$_ = uc $_ for $col_name;
					$col_name =~ s/\s+//g;
					$seena{$col_name}++;			             #get names ;
}										
	while ( my ( $col_name, $times_seen ) = each %seena ) {
my $loc_total = $times_seen;
			printf OUTTXT "\n";

			push @classes, $col_name. "\n" ;
			printf OUTTXT  "\t%-20s | %-10s", $col_name , $loc_total;																					
}
			printf OUTTXT "\n";
			printf OUTTXT "\t%-47s   %-10s\n", "Total      " , "$pupilcount \n" ;
#======================================================================================================================										
#	stats1 stats
#
#=======================================================================================================================	  
my %seen_read;
	foreach (@arraynew)
{
    chomp;
my $line = $_;
my @elements = split (",", $line);   
my $col_name = $elements[0];
my $col_child = $elements[1];
my $stats1 = $elements[3];
			$col_name="" if(!$col_name);# I do this so warnings will not complain
			$stats1="" if(!$stats1);
					$_ = uc $_ for $col_name;
					$col_name =~ s/\s+//g;
					$seen_read{$col_name}++ if $stats1 lt "6";
			push @outcsv, $line ."\n" if $stats1 eq "6";
}
			printf OUTTXT "\n\t\t Statistics\n";
			printf OUTTXT	"      	_____________________________________________________  \n"; 	

	while ( my ( $col_name, $times_seen ) = each %seen_read ) {
my $loc_total = $times_seen ;

}
#-------------------------------------------------------------------------------------------------------------
#	calculate percent  rates      ";
														#	 seen if in both arrays
		foreach my $key2 ( keys %seena ) {
	if( exists $seen_read{$key2} )
        {	
my $totala	= ($seena{$key2})	;	
my $totalread	= ($seen_read{$key2})	;							#warn "     $key2 is in both hashes!\n";  #for testing only before	
my $final_total = $totalread / ($seena{$key2}) * 100  ;

my $totals = $seena{$key2}  ;
my $success	= $final_total ;
		printf OUTTXT"\t%-20s | %-6s | %-6s | %11.f%% \n", $key2, $totals ,$seen_read{$key2} ,  $success ;

    }
		else
    {
my $final_total = $seena{$key2}	 ;	
my $totals = $seena{$key2}  ;

				printf OUTTXT"\t%-20s | %-6s | %-6s | %11.f%% \n", $key2, $totals , "0" ,  "0";


}	 

}
#============================================================
my %seeneal;
	foreach (@arraynew)
{
			chomp;
my $line = $_;
my @elements = split (",", $line);
my $col_pupileal = $elements[3];
		$col_pupileal ="" if(!$col_pupileal);
					$col_pupileal =~ s/\s+//g;
					$seeneal{$col_pupileal}++;
}										
	while (my ( $col_pupileal, $times_seen ) = each %seeneal ) {
			if ($col_pupileal lt 6) {
my $pupileal_total = $times_seen;
my $overalleal  = $pupileal_total / $pupilcount * 100 ;
		printf OUTTXT "\t%-38s   %11.f%% \n", "Total =" , "$overalleal \n" ;

    }}