#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::BasicRead;
	my @outcsv;
	my @classes;	
	our $Header_row;

#================================= 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 "\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;
			print "\n";

			push @classes, $col_name. "\n" ;
			printf  "\t%-20s | %-10s", $col_name , $loc_total;																					
}
			print "\n";
			printf "\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";
}
			print "\n\t\t Statistics\n";
			printf	"      	_____________________________________________________  \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 "\t%-20s | %-6s | %-6s | %11.f%% \n", $key2, $totals ,$seen_read{$key2} ,  $success ;
    next;
    }
 									
}
#============================================================
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 "\t%-38s   %11.f%% \n", "Total =" , "$overalleal \n" ;

    }}	



##</code><code>##

CLASS	PUPIL	M/F	READING  PROGRESS	
GIRAFFE	 JAMES LE	 M	 1	N
GIRAFFE	 MICKY JAMES	 M	6	N
LION	 AMIE CAHORT	 M	 -1	Y
LEOPARD	 JIM LEE	 M	 4	Y
ZEBRA	 JAMES LEHERN	 M	 4	N
ZEBRA	 DAN DARE	 M	 -1	N
ZEBRA	 DENIS MENICE	 M	 -1	Y
MEERKAT	 JOHN JONES	 M	6	Y
MONGOOSE	 A PELE	 M	 4	N
MONKEY	 MINNIE MOPOCHER	 F	 1	N
MONKEY	 MO SIZLAK	 F	 1	Y
HIPPO	 JACKIE LEAN	 F	6	Y
HIPPO	 PETER PICKLES	 M	 -1	N
HIPPO	 REG VAERNY	 M	 0	N
HIPPO	 WAYNE SMITH	 M	 0	Y



##</code><code>##


		 Totals
	GIRAFFE              | 2         
	LEOPARD              | 1         
	MONGOOSE             | 1         
	MONKEY               | 2         
	HIPPO                | 4         
	MEERKAT              | 1         
	LION                 | 1         
	ZEBRA                | 3         
	Total                                             15 
      

		 Statistics
      	_____________________________________________________  
	GIRAFFE              | 2      | 1      |          50% 
	LEOPARD              | 1      | 1      |         100% 
	MONGOOSE             | 1      | 1      |         100% 
	MONKEY               | 2      | 2      |         100% 
	HIPPO                | 4      | 3      |          75% 
	LION                 | 1      | 1      |         100% 
	ZEBRA                | 3      | 3      |         100% 
	Total =                                           13% 
	Total =                                           20% 
	Total =                                           20% 
	Total =                                           27% 

##</code><code>##

#!/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" ;

    }}	

##</code><code>##


		 Totals
	GIRAFFE              | 2         
	LEOPARD              | 1         
	MONGOOSE             | 1         
	MONKEY               | 2         
	HIPPO                | 4         
	MEERKAT              | 1         
	LION                 | 1         
	ZEBRA                | 3         
	Total                                             15 
      

		 Statistics
      	_____________________________________________________  
	GIRAFFE              | 2      | 1      |          50% 
	LEOPARD              | 1      | 1      |         100% 
	MONGOOSE             | 1      | 1      |         100% 
	MONKEY               | 2      | 2      |         100% 
	HIPPO                | 4      | 3      |          75% 
	MEERKAT              | 1      | 0      |           0% 
	LION                 | 1      | 1      |         100% 
	ZEBRA                | 3      | 3      |         100% 
	Total =                                           13% 
	Total =                                           20% 
	Total =                                           20% 
	Total =                                           27% 

##</code><code>##


		 Totals
	GIRAFFE              | 2         
	LEOPARD              | 1         
	MONGOOSE             | 1         
	MONKEY               | 2         
	HIPPO                | 4         
	MEERKAT              | 1         
	LION                 | 1         
	ZEBRA                | 3         
	Total                                             15 
      

		 Statistics
      	_____________________________________________________  
	GIRAFFE              | 2      | 1      |          50%
	LEOPARD              | 1      | 1      |         100%
	MONGOOSE             | 1      | 1      |         100%
	MONKEY               | 2      | 2      |         100%
	HIPPO                | 4      | 3      |          75%
	MEERKAT              | 1      | 1      |	   0%
	LION                 | 1      | 1      |         100%
	ZEBRA                | 3      | 3      |         100%
	Total =                                           86%