#!/usr/bin/perl -w

# This program converts a comma separated values (CSV) grade file into GRIT.
# If gnumeric is installed, this program can also convert an Excel (XLS) file
# to CSV automatically.

# It was written by Reid Priedhorsky. Feel free to contact him with questions
# at reid@umn.edu.

# No particular order of columns is required. The first row contains column
# headers and defines the column order, and column header rows can be repeated
# at will (though only the first has any meaning). The following columns
# contain non-grade or derived grade information:

#   last_name
#   first_name
#   sec
#   email
#   student_id
#   total
#   letter

# Other columns are assumed to be scores. The templates, etc., and other setup
# junk go in grit_top.txt. Everything in the file is ignored until the first
# header line, so you can put commentary, weights, etc. in lines before that.

# BUGS:
#   - I wouldn't say this program is particularly robust... use caution.
#   - Commas and double quotes are not allowed anywhere in data.
#   - You can use multiple sheets in your spreadsheet, and only one needs to
#     be formatted for this script. However, this sheet MUST be the current
#     sheet when you save.


# Config

use strict;

$| = 1;
use File::stat;

my $csv_file_name = "grades.csv";
my $xls_file_name = "grades.xls";


# Program

my ($grit_top, $line);
my (%idx);
my (%data);

# can we make csv from xls?
print "Checking for $xls_file_name... ";
if (not -e $xls_file_name) {
  print "not present\n";
} else {
  my ($xls_time, $csv_time);
  $xls_time = stat($xls_file_name)->mtime;
  if (-e $csv_file_name) {
    $csv_time = stat($csv_file_name)->mtime;
  } else {
    $csv_time = 0;
  }
  if ($csv_time >= $xls_time) {
    print "using $csv_file_name because it is newer\n";
  } elsif (not `whereis ssconvert` =~ /ssconvert: \/.+/) {
    print "present and newer but no ssconvert available\n";
    print "You'll have to convert manually or install Gnumeric. Aborting.\n";
    exit(1);
  } else {
    my $ret;
    print "converting to $csv_file_name\n";
    $ret = system("ssconvert --export-type=Gnumeric_stf:stf_csv $xls_file_name $csv_file_name");
    if ($ret) { die "ssconvert failed with $ret" };
  }
}

# parse CSV file
print "Parsing $csv_file_name...\n";
open(FP, "<$csv_file_name") or die "can't open $csv_file_name: $!";
do {
  $line = <FP>;
  if (not defined $line) {
    die "reached end-of-file without finding a header line";
  }
  xchomp($line);
  %idx = makeidx(split(/,/, $line));
} while (not %idx);

while ($line = <FP>) {
  my (@gs);
  xchomp($line);
  @gs = map { s/"//g; $_ } split(/,/, $line);
  if ($gs[$idx{student_id}] ne 'student_id') {
    # ignore column header lines
    foreach my $key (keys %idx) {
      $data{$gs[$idx{student_id}]}->{$key} = $gs[$idx{$key}];
    }
  }
}
close(FP);

# spew GRIT files
{
  my $grit_top;

  # read grit_top
  local $/; # slurp whole files
  open(FP, "<grit_top.txt") or die "can't open grit_top.txt: $!";
  $grit_top = <FP>;
  close(FP);
  $/ = "\n"; # slurp lines again

  open(FP, ">grades.grit") or die "can't open grades.grit: $!";

  print FP $grit_top;

  foreach my $id (keys %data) {
    my $s = $data{$id};
    print FP "\n# $s->{last_name}, $s->{first_name}\n";

    foreach my $assgn (keys %$s) {
      if ($assgn !~ /last_name|first_name|sec|email|student_id|total|letter/i
          and $s->{$assgn} ne '') {
        print FP "    $id : $assgn = $s->{$assgn}\n";
      }
    }
  }

  close(FP);
  print("Wrote grades.grit\n");
  system("chmod 660 grades.grit");
}


# Subroutines

sub makeidx {
  my %h;
  for (my $i = 0; $i < @_; $i++) {
    $h{$_[$i]} = $i;
  }
  if (exists $h{student_id}) {
    return %h;
  } else {
    return ();
  }
}

# Kill all trailing EOL characters on call-by-reference parameter; works
# for DOS/Unix/Mac newline characters
sub xchomp {
  $_[0] =~ s/[\r\n]*$//;
}