#!/usr/bin/env bash

# Import grades from $1.sc (not formulas so use $1.txt) column $2  to $3.sc column $4, for example
#     sccp quizgrades QZD grades QZT

# $var:t    ${var##*/}
# $var:h    ${var%/*}
# $var:e    ${var##*.}
# $var:r    ${var%.*}

debug=0

if [[ $# -lt 1 ]]; then
  echo "Usage: sccp srcspreadsheet srccol destspreadsheet destcol"
  exit 1
fi

tempfile=/tmp/sccp.$USER.$$.awk

make ${1%.*}.txt

destcollet=`sed -n 's+.* \([A-Z][A-Z]*\)[0-9][0-9]* = "'$4'"+\1+p' ${3%.*}.sc`

echo 'BEGIN {' > $tempfile # Generating awk program to associate grades with email addresses
cat ${1%.*}.txt | excol NAME $2 | tail +2 | tr -d ' ' \
  | sed 's+NS$+ NS +' | sed 's+\([^0-9]*\)+\1 +' \
  | sed 's+\([a-zA-Z][^ ]*\) \([^ ]*\)+score["\1"]=\2;+' >> $tempfile
echo '}\
{ if ($2!="") {if (score[$2]!="") print "let '$destcollet'"$1" = "score[$2]; else print "rightstring '$destcollet'"$1" = \"NS\"";} }' >> $tempfile

if (( $debug )) ; then cat $tempfile; fi

del . ${3%.*}.sc
grep -v 'let '$destcollet'[0-9]* = [0-9]' .del/${3%.*}.sc > ${3%.*}.sc

# Following sends linenum/email pairs to awk program
make ${3%.*}.txt
cat ${3%.*}.txt | excol NAME | tail +2 | numlines | tr -d ' ' \
  | awk -F: -f $tempfile >> ${3%.*}.sc

rm $tempfile