\n};
print qq{\n};
} # end of printPDF
#-----------
sub writeCSV {
#-----------
my $ref = shift;
# print "Val:", @val, " Ref:$ref \n";
if ( $csv->combine( @$ref ) ) {
my $record = $csv->string;
print EX $record, "\r\n";
} else {
my $err = $csv->error_input;
print qq{$lex{'Combine failed on input'}: $err \n};
}
return;
} # end writeCSV
#------------
sub exportCSV {
#------------
# foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key} \n}; }
# Remove the extra:
delete $arr{grouping};
delete $arr{papersize};
$csv = Text::CSV_XS->new( {binary => 1} );
# Open output file
my $filename = "expReadProgress$$.csv";
open (EX,">$filename") || die $lex{'Cannot open Export file'}. " $fileName";
my $sdate = $arr{startdate};
delete $arr{startdate};
my $edate = $arr{enddate};
delete $arr{enddate};
# Write date range.
my @data = ();
#for my $i (1..20){ $data[$i] = ' ';}
$data[0] = "$lex{'Start Date'} $sdate";
$data[2] = "$lex{'End Date'} $edate";
writeCSV(\@data);
my $startjd = julian_day( split(/-/, $sdate ));
my $endjd = julian_day( split(/-/, $edate ));
my %overall;
foreach my $db ( sort keys %arr ) {
my $database = $db;
my $dsn = "DBI:$dbtype:dbname=$database";
my $dbh = DBI->connect($dsn,$guser,$gpassword);
my $schoolname = $alldbase{"$db"};
# Write the school name
my @data = ();
#for my $i (1..20){ $data[$i] = ' ';}
$data[0] = $schoolname;
writeCSV(\@data);
# Get all students in the school who have tests into a @students array
my @students;
my $sth = $dbh->prepare("select distinct studnum from read_test");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while ( my $studnum = $sth->fetchrow ) {
push @students, $studnum;
}
if ( not @students ) {
my @data = ();
for my $i (1..20){ $data[$i] = ' ';}
$data[0] = "No Student Tests Found";
writeCSV(\@data);
# Write blank line.
my @data = ();
$data[0] = ' ';
writeCSV(\@data);
next;
}
# Now loop through all students printing results; setup queries first
my $sth1 = $dbh->prepare("select id, tdate, readlevel, tgrade, dratype from read_test
where studnum = ? order by tdate desc");
my $sth2 = $dbh->prepare("select sum(score) from read_test_score where testid = ?");
my %schooldata = ();
foreach my $studnum ( @students ) {
# Get Tests
$sth1->execute( $studnum );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
# Only use latest value for the student.
my $trows = $sth1->rows;
# if $onlylatest, limit to 1 test
#if ( $onlylatest and $trows > 1 ) { $trows = 1; }
$trows = 1;
for ( 1 .. $trows ) {
my ( $testid, $tdate, $readlevel, $tgrade, $dratype ) = $sth1->fetchrow;
# print qq{JD:$testjd Start:$startjd \n};
# if ( $testjd > $startjd ) { print qq{ GREATER \n}; }
# if ( $testjd < $endjd ) { print qq{ GREATER \n}; }
# skip out of date range.
my $testjd = julian_day( split('-', $tdate));
if ( $testjd < $startjd or $testjd > $endjd ) { next; }
# Get the score total;
$sth2->execute( $testid );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $total = $sth2->fetchrow;
# Equivalent Grade print
my $equivgrade = scoreToGrade($total, $readlevel, $dratype );
# print qq{
$testid - $total - $equivgrade
\n};
my $grdelta;
if ( not $tgrade ) {
print qq{Grade Missing: $studnum - $readlevel - $tdate \n};
next;
} else { # Find grade delta
$grdelta = $equivgrade - $tgrade;
}
if ( $grdelta >= 0 ) { $grdelta = int $grdelta; } # truncate.
else { $grdelta = int $grdelta - 1; }
$schooldata{$tgrade}{$grdelta}++;
$overall{$tgrade}{$grdelta}++;
} # end of trows loop
} # end of student loop
my ($max, $min, $studcount );
my %dcount = ();
my @stats = ();
foreach my $grade ( sort keys %schooldata ) {
foreach my $delta ( sort keys %{ $schooldata{$grade} } ) {
# Set Min/Max values
if ( $delta > $max ) { $max = $delta; }
if ( $delta < $min ) { $min = $delta; }
# Get Count and Delta Counts.
$studcount += $schooldata{$grade}{$delta};
$dcount{$delta} += $schooldata{$grade}{$delta};
for (1..$dcount{$delta} ) { push @stats, $delta; } # put delta values into @stats
# print qq{G:$grade D:$delta Count:$schooldata{$grade}{$delta} \n};
}
}
# Table Heading
@data = ();
$data[0] = "$lex{Grade}";
foreach my $delta ( $min .. $max ) {
push @data, $delta;
}
push @data, "SDev";
push @data, "Var";
writeCSV(\@data);
# Write each grade row.
foreach my $grade ( sort {$a <=> $b} keys %schooldata ) {
my @data = ();
my @grstats = ();
$data[0] = $grade;
foreach my $delta ( $min .. $max ) {
push @data, $schooldata{$grade}{$delta};
for (1..$schooldata{$grade}{$delta}) { push @grstats, $delta; }
}
push @data, stddev(\@grstats);
push @data, variance(\@grstats);
writeCSV(\@data);
}
# Write average Row
my @data = ();
my @data1 = ();
$data[0] = 'Average';
$data1[0] = 'Count';
foreach my $delta ( $min .. $max ) {
my $avg;
if ( $studcount ) {
$avg = round( $dcount{$delta} / $studcount * 100, 2);
}
push @data, $avg;
push @data1, $dcount{$delta}
# print qq{
$dcount{$delta}/$studcount $avg%
};
}
push @data, stddev(\@stats);
push @data, variance(\@stats);
writeCSV(\@data); # average
writeCSV(\@data1); # count
# Duplicate of the studcount row...
# Write Column Sum
# my @data = ();
# $data[0] = 'Column Sum';
# foreach my $delta ( $min .. $max ) {
# push @data, $dcount{$delta}
# }
# writeCSV(\@data);
# Write studcount Row
my @data = ();
$data[0] = 'S Count';
foreach my $delta ( $min .. $max ) {
push @data, $studcount;
}
writeCSV(\@data);
# Write blank line.
my @data = ();
$data[0] = ' ';
writeCSV(\@data);
} # database loop
# All Schools Combined.
my @data = ();
$data[0] = 'Combined Schools Result';
writeCSV(\@data);
# Next Line
$max = 0,
$min = 0,
$studcount = 0;
%dcount = ();
@stats = ();
foreach my $grade ( sort keys %overall ) {
foreach my $delta ( sort keys %{ $overall{$grade} } ) {
if ( $delta > $max ) { $max = $delta; }
if ( $delta < $min ) { $min = $delta; }
$studcount += $overall{$grade}{$delta};
$dcount{$delta} += $overall{$grade}{$delta};
for (1..$overall{$grade}{$delta}) { push @stats, $delta; }
# print qq{G:$grade D:$delta Count:$overall{$grade}{$delta} \n};
}
}
# Write Heading Line
my @data = ();
$data[0] = $lex{Grade};
foreach my $delta ( $min .. $max ) {
push @data, $delta;
}
push @data, "SDev";
push @data, "Var";
writeCSV(\@data);
# Write all grade rows.
foreach my $grade ( sort {$a <=> $b} keys %overall ) {
my @data = ();
my @grstats = ();
$data[0] = $grade;
foreach my $delta ( $min .. $max ) {
push @data, $overall{$grade}{$delta};
for (1..$overall{$grade}{$delta}) { push @grstats, $delta; }
}
push @data, stddev(\@grstats);
push @data, variance(\@grstats);
writeCSV(\@data);
}
# Write average Row
my @data = ();
$data[0] = 'Average';
foreach my $delta ( $min .. $max ) {
my $avg;
if ( $studcount ) {
$avg = round( $dcount{$delta} / $studcount * 100, 2);
}
push @data, $avg;
# print qq{