#!/bin/ksh

usage ()
{
  echo "" 1>&2
  echo "`basename $0` [opts]" 1>&2
  echo "  -d bpo-date   default = yesterday" 1>&2
  echo "  -a accounts   default = CT" 1>&2
  echo "  -f outfile    default = <stdout>" 1>&2
  echo "  -t table      default = corp_pfd_namr (always in BloombergDb..)" 1>&2
  echo "  -x            (generate for Excel importing, disables -s)" 1>&2
  echo "  -s sepChar    default = <tab>" 1>&2
  echo "" 1>&2
  exit 1
}

date="dateadd(day, -1, getdate())"
accts=CT
table=corp_pfd_namr
cmd="`basename $0` $*"
forExcel=n

while getopts d:a:f:t:xs: opt; do
  case $opt in
    d)
      date="'$OPTARG'"
      if [ "$date" = yesterday ]; then
        date="dateadd(day, -1, getdate())"
      elif [ "$date" = today ]; then
        date="getdate()"
      fi
      ;;
    a)
      accts=`echo "$OPTARG" | tr , ' '`
      ;;
    f)
      outfile="$OPTARG"
      ;;
    t)
      table="$OPTARG"
      ;;
    x)
      forExcel=y
      taborsp='\t'
      ;;
    s)
      sepChar="$OPTARG"
      ;;
    *)
      usage
      ;;
  esac
done

shift $(($OPTIND - 1))

[ -z "$taborsp" ] && taborsp=' '
acctsql=""
conj=""
pl=0
for acct in $accts; do
  (( pl = pl + 1 ))
  acctsql="$acctsql${conj}substring(p.trader, 1, ${#acct}) = '$acct'"
  conj="
    or  "
done
if [ -n "$acctsql" ]; then
  if [[ $pl -gt 1 ]]; then
    acctsql="(${acctsql})
   and"
  else
    acctsql="$acctsql
   and"
  fi
fi

if [ -n "$outfile" ]; then
  set -o noglob
  touch "$outfile" && outfileOK=y
  if [ "$outfileOK" != y ]; then
    echo "Cannot write to output file \"$outfile\"" 1>&2
    usage
  fi
fi

exec > "$outfile"

date
echo "$cmd"
echo "date               ~[-d] = $date"                 | tr '~' "$taborsp"
echo "account prefix     ~[-a] = '$accts'"              | tr '~' "$taborsp"
echo "security info table~[-t] = BloombergDb..$table"   | tr '~' "$taborsp"
echo "separator          ~[-s] = '$sepChar'"            | tr '~' "$taborsp"

sql="
select p.trader
     , p.secid
     , b.id_cusip
     , b.id_bb_unique
     , p.position
     , b.calc_typ
     , b.calc_typ_des
     , b.issue_px
     , b.par_amt
     , *
  from BBTradeDb..t_bb_bpo p
     , BloombergDb..$table b
 where ${acctsql} p.report_date >= $date
   and (p.secid *= b.id_cusip or p.secid *= substring(b.id_bb_unique, 3, 9) )
order by p.trader, b.calc_typ, b.id_cusip
;-m vert
"

# get best awk:
if type gawk 1>/dev/null 2>&1; then
  AWK=gawk
elif type nawk 1>/dev/null 2>&1; then
  AWK=nawk
else
  AWK=awk
fi

#echo "$sql"
#exit

# The following awk script will tidy retrieved data into columnar format
awkScriptUser='
BEGIN   { nrec = 0; ncol = 0; if (!sepChar) sepChar = "\t"; }
/^ *$/  {
          nrec++; next;
        }
        {
          pos = index($0, ":");
          if (!pos) next;
          hdr = substr($0, 1, pos-1);
          # make sure header is registered
          if (!(hdr in hdrs)) {
            lens[hdr] = length(hdr);
            hdrs[hdr] = ncol;
            cols[ncol] = hdr;
            ncol++;
          }
          val = substr($0, pos+1);
          sub("^ *", "", val);
          sub(" *$", "", val);
          if (match(val, "^[-+]?[0-9]*\.[0-9]*$")) {
            # remove trailing decimal zeros (and point)
            sub("\\.0+$", "", val);
            if (match(val, "\\.[0-9]*[1-9]0+$"))
              sub("0+$", "", val);
            # measure intpart length and decpart length
            decpos = index(val, "."); # position of decimal point
            vlen = length(val);
            intlen = decpos ? decpos - 1 : vlen; # length of integer part
            declen = decpos ? vlen - decpos + 1 : 0; # include the point itself
            if (intlen > ints[hdr])
              ints[hdr] = intlen;   # maximise integer length portion for column
            if (declen > decs[hdr])
              decs[hdr] = declen;   # maximise decimal length portion for column
            # evaluate current value max width
            vlen = ints[hdr] + decs[hdr];
            if (vlen > lens[hdr])
              lens[hdr] = vlen;
          }
          data[nrec, hdr] = val;
          vlen = length(val);
          if (vlen > lens[hdr])
            lens[hdr] = vlen;
        }
END     {
          system("date");
          print "";
          # print headings
          sep = "";
          for (col = 0; col < ncol; col++) {
            hdr = cols[col];
            len = lens[hdr];
            printf("%s%-*s", sep, len, hdr);
            sep = sepChar;
          }
          print "";

          # print bars
          sep = "";
          for (col = 0; col < ncol; col++) {
            hdr = cols[col];
            bar = sprintf("%*s", lens[hdr], "");
            gsub(".", "-", bar);
            printf("%s%s", sep, bar);
            sep = sepChar;
          }
          print "";

          # print data
          for (rec = 0; rec < nrec; rec++) {
            sep = "";
            for (col = 0; col < ncol; col++) {
              hdr = cols[col];
              len = lens[hdr];
              val = data[rec, hdr];
              # check whether it is numeric to determine justification
              if (match(val, "^[-+]?[0-9]*\.[0-9]*$") ||
                  match(val, "^[-+]?[0-9]*$")) {
                fmt="%s%*s";
                decpos = index(val, ".")
                vlen = length(val)
                declen = decpos ? vlen - decpos + 1 : 0;
                decl = decs[hdr] - declen;
                if (decl)
                  val = val sprintf("%*s", decl, "");
              }
              else
                fmt="%s%-*s";
              printf(fmt, sep, len, val);
              sep = sepChar;
            }
            print "";
          }
        }
'
awkScriptTabXL='
BEGIN   { nrec = 0; ncol = 0; }
/^ *$/  {
          nrec++; next;
        }
        {
          pos = index($0, ":");
          if (!pos) next;
          hdr = substr($0, 1, pos-1);
          # make sure header is registered
          if (!(hdr in hdrs)) {
            hdrs[hdr] = ncol;
            cols[ncol] = hdr;
            ncol++;
          }
          val = substr($0, pos+1);
          sub("^ *", "", val);
          sub(" *$", "", val);
          data[nrec, hdr] = val;
        }
END     {
          system("date");
          print "";
          # print headings
          for (col = 0; col < ncol; col++) {
            hdr = cols[col];
            printf("%s\t", hdr);
          }
          print "\r";

          # print data
          for (rec = 0; rec < nrec; rec++) {
            for (col = 0; col < ncol; col++) {
              hdr = cols[col];
              val = data[rec, hdr];
              printf("%s\t", val);
            }
            print "\r";
          }
        }
'

if [ "$forExcel" = y ]; then
  echo "$sql" | sqsh -w1000 | $AWK "$awkScriptTabXL"
else
  if [ -z "$sepChar" ]; then
    echo "$sql" | sqsh -w1000 | $AWK "$awkScriptUser"
  else
    echo "$sql" | sqsh -w1000 | $AWK "$awkScriptUser" sepChar="$sepChar"
  fi
fi