#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;

my @groups = ([2,3,7,8,9,15,18,21,22,23,24,25,26,27],
 # Low hour vfr, low hour instruments
              [4,10,12,16,17,19], [1,5,6,11,13,14,20]);
 # high hour instruments
my @cats =    ('BL','BLSTDGG','PR1','PRFN1','PRFN3','PRFN30','EBG1',
 'EBGFN1','EBGFN3','EBGFN30','CCFN1','CCFN30','CCFN30NT');

my $file = 'C:\WINDOWS\Profiles\chemphysio\Desktop\Test data\TEST\complete_ordered2.txt';
open (TABFILE,$file) or die "$file: $!";

# Create a new Excel workbook
my $workbook  = Spreadsheet::WriteExcel->new('TEST.xls');
my $worksheet = $workbook->addworksheet();

my (@avgs,@sums,@totals);
my @data = <TABFILE>;
my $row  = 0;

for my $r (0..2) {
  for my $c (0..3) {
    for my $cat (0..$#cats) {
      ($sums[$r])->[$c][$cat] = 0;
      if ($r == 0) {
        ($sums[$r])->[4][$cat] = 0;
        ($sums[$r])->[5][$cat] = 0;
      }
    }
  }
}
for my $i (0..2) {
  for my $cat (0..$#cats) {
    $totals[$i]->[$cat]->[0] = 0;
    if ($i == 0) {
      $totals[$i]->[$cat]->[1] = 0;
      $totals[$i]->[$cat]->[1] = 0;
    }
  }
}

for (@data) {
  chomp;
  my @vals = split('\t', $_);
  $vals[0] =~ s/.txt//;

  my ($ts,$cat) = $vals[0] =~ /TS(\d+)(.+)$/;
  my ($i,$j);

  # Some of the data has slight errors in it (these were
  # the filenames, though they represent the condition)
  $cat =~ s/CCCFN30NT/CCFN30NT/; $cat =~ s/BLSTDGS/BLSTDGG/;
  $cat =~ s/BLSTDGG?/BLSTDGG/;
  for(0..$#cats) { if ($cats[$_] =~ /$cat/) { $j = $_ } }

  for(@{$groups[0]}) { if ($ts == $_) { $i = 0 } }
  for(@{$groups[1]}) { if ($ts == $_) { $i = 1 } }
  for(@{$groups[2]}) { if ($ts == $_) { $i = 2 } }

  # A basic representation of @sums (@avgs too)
  # REST|IMC ... represents one of six values (the sums)
  # condition is the array index for @cats
  # $sums[GROUP] -> [(REST|IMC)(HRT|SKT|(EMG)?) ->
  #  [CONDITION]

  for(0..3) {
    ($sums[$i])->[$_][$j] += $vals[$_+1];
    if ($ts >= 21) {
      $sums[$i]->[4][$j] += $vals[5];
      $sums[$i]->[5][$j] += $vals[6];
    }
  }
  $totals[$i]->[$j]->[0]++;
 if ($ts >= 21) { $totals[$i]->[$j]->[1]++ }

  my $col = 0;
  for (@vals) {
    $worksheet->write($row, $col, $_);
    $col++;
  }
  $row++;
}

for my $r (0..2) {
  for my $c (0..3) {
    for my $cat (0..$#cats) {
      $avgs[$r]->[$c][$cat] = $sums[$r]->[$c][$cat]/$totals[$r]->[$cat]->[0];
      if ($r == 0) {
        $avgs[$r]->[4][$cat] = $sums[$r]->[4][$cat]/$totals[$r]->[$cat]->[1];
        $avgs[$r]->[5][$cat] = $sums[$r]->[5][$cat]/$totals[$r]->[$cat]->[1];
      }
    }
  }
}

my $format = [
               [@cats],
               $avgs[0]->[0],
               $avgs[0]->[1],
               $avgs[0]->[2],
               $avgs[0]->[3],
               $avgs[0]->[4],
               $avgs[0]->[5],
];

$worksheet->write_row('J6', $format);