# ==============================================================================
# VerticalToHorizontal.nm
# Author: Tony Balinski
#
# These macros are designed for use with Sybase and SQSH. SQSH can output data
# in a vertical format, with each table row being represented by a series of
# lines of the form "<column-name>:<spaces><value>", and separated from the next
# row by an empty line.
#
# Attempts are made to "tidy up" the data: floating numbers have trailing
# decimal zeros removed, "null" dates (Jan  1 1900) and times (12:00AM) are
# reduced leaving remaining text (usually a "pure" date or time), columns whose
# data do not vary are (optionally) removed, and data values are centered in
# their columns.
# ==============================================================================

NEDIT_require_macro_file("extensions.nm")

# ==============================================================================
# VerticalToHorizontal(text, col_sep, underline, do_all_eq, show_all_eq): This
#       macro treats sequences of lines of form "name: value" as field/value
#       pairs; any line sequence without a colon is treated as a record
#       separator. The values are arranged into a single row per record, turning
#       the record display into a table; field names are written horizontally in
#       the same fashion, acting as column headings.
#         text:         source text
#         col_sep:      column separator string
#         underline:    character to use as a heading underline
#         do_all_eq:    if true (non-zero), remove columns with value variation
#         show_all_eq:  with do_all_eq in effect, place all-equal column values
#                       after the table if this is true (non-zero).
# ==============================================================================

define VerticalToHorizontal
  {
  text = $1
  col_sep = $2
  underline = substring($3, 0, 1)
  do_all_eq = $4
  show_all_eq = $5 && do_all_eq

  break_RE = "^[^:]*(?:\n[^:]*)*$"
  col_val_sep_RE = ":\\s+"
  value_line_RE = "^[^:]*" col_val_sep_RE ".*$"
  rem_trail_zeros_RE = "(\\d\\.\\d(?:\\d*[1-9])*)0*$"
  float_point_RE = "^[-+]?(?:\\d+\\.?\\d*|\\.\\d+)(?:e[-+]?\\d+)?$"

  # usual date format is "Mmm dd yyyy HH:MMpm"
  date_RE = "[ADFJMNOS][aceopu][bcglnprtvy] [ \\d]\\d \\d{4}"
  time_RE = "\\d\\d:\\d\\d[AP]M"
  date_only_RE = "^" date_RE " 12:00AM$"
  date_time_RE = "^" date_RE " " time_RE "$"
  date_zero_RE = "^Jan  1 1900"

  if (underline != "")
    underline = quote_literal_as_regex(underline)

  # find first and last candidate lines 
  bot = length(text)
  top = search_string(text, value_line_RE, 0, "regex")
  if (top == -1)
    top = bot

  bot = search_string(text, value_line_RE, bot - 1, "regex", "backward")
  if (bot == -1)
    bot = 0
  else
    bot = $search_end

  # keep hold of prefix, suffix text
  top_part = substring(text, 0, top)
  bot_part = substring(text, bot + 1)

  the_top = top
  the_bot = bot

  cur_headers = $empty_array
  cur_lengths = $empty_array
  cur_lenname = $empty_array
  cur_lenvals = $empty_array
  cur_table = $empty_array
  cur_colpos = $empty_array
  all_equal = $empty_array

  cur_intpts = $empty_array
  cur_decpts = $empty_array

  rec_num = 0
  maxlen = 0
  maxlenstr = ""

  while (top <= bot)
    {
    # foreach record: find its limits
    end_rec = search_string(text, break_RE, top, "regex")
    if (end_rec < 0)
      end_rec = bot
    next_rec = $search_end
    if (next_rec < end_rec)
      next_rec = bot

    prev_name = ""
    # for each field line of form "<name>:<spaces><value>"
    while (top < end_rec)
      {
      left = start_of_line_pos(top, text)
      right = end_of_line_pos(top, text)
      top = right + 1
      line = substring(text, left, right)
      tline = trim(line)

      # pick up the column name/value separator (if any)
      name = replace_in_string(tline, col_val_sep_RE ".*", "", "regex")
      val = replace_in_string(tline, "^.*?" col_val_sep_RE, "", "regex")

      if (name == "" && val == "" && search_string(tline, ":", 0) < 0)
        continue  # forget this line

      # add the name to the list of headers
      if (!(name in cur_headers))
        {
        # TODO: find proper position of our header in relation to prev_name
        col = cur_headers[]
        cur_headers[name] = col
        cur_colpos[col] = name
        cur_lenvals[name] = 0
        cur_intpts[name] = 0
        cur_decpts[name] = 0
        len = length(name)
        if (maxlen < len)
          {
          maxlen = len
          maxlenstr = name
          }
        }

      if (search_string(val, float_point_RE, 0, "regex") == 0)
        {
        # reduce floating point numbers if possible
        # remove trailing decimal zeros (and point)
        val = replace_in_string(val, "(\\.\\d*[1-9])0+$", "\\1", \
                                "regex", "copy")
        val = replace_in_string(val, "\\.0+$", "", "regex", "copy")

        len = length(val)

        # measure intpart length and decpart length
        decpos = search_string(val, ".", 0)
        if (decpos >= 0)
          intlen = decpos
        else
          intlen = len
        declen = len - intlen   # everything from "." to end (or 0)

        intlen = max(cur_intpts[name], intlen)
        declen = max(cur_decpts[name], declen)
        cur_intpts[name] = intlen
        cur_decpts[name] = declen

        # evaluate current value max width
        len = intlen + declen
        }
      else
      if (search_string(val, date_time_RE, 0, "regex") == 0)
        {
        len = length(val)
        # reduce date-time to date or time if applicable
        if (search_string(val, date_only_RE, 0, "regex") == 0)
          {
          len -= 8    # length of " 12:00AM"
          val = substring(val, 0, -8)
          }
        if (search_string(val, date_zero_RE, 0, "regex") == 0)
          {
          len -= 11   # length of "Jan  1 1900"
          val = substring(val, 11)
          }
        if (val == " ")
          {
          len = 0
          val = ""
          }
        }
      else
        {
        len = length(val)
        }
      # we now have a useful length in len - update cur_lenvals
      cur_lenvals[name] = max(cur_lenvals[name], len)

      cur_table[name, rec_num] = val
      if (do_all_eq)
        {
        if (rec_num == 0)
          {
          all_equal[name] = 1
          all_equal_val[name] = line
          }
        else if (all_equal[name])
          {
          all_equal[name] = (all_equal_val[name] == line)
          }
        }

      if (maxlen < len)
        {
        maxlen = len
        maxlenstr = val
        }
      prev_name = name
      }

    # end foreach record
    top = next_rec + 1
    rec_num++
    }
  # ----------- all data read -----------

  # now we have everything in cur_table
  # evaluate max lengths
  for (name in cur_headers)
    {
    len = length(name)
    cur_lenname[name] = len
    cur_lengths[name] = max(cur_lenvals[name], len)
    }

  # write out columns in order
  lines = ""
  line = col_sep
  for (col = 0; col < cur_colpos[]; col++)
    {
    name = cur_colpos[col]
    if (do_all_eq)
      {
      if (all_equal[name])
        continue
      }
    if (cur_lenname[name] < cur_lengths[name])
      val = ljust(name, (cur_lenname[name] + cur_lengths[name]) / 2)
    else
      val = name
    line = line rjust(val, cur_lengths[name]) col_sep
    }
  lines = lines line "\n"

  # write out a line of column underlines
  if (underline != "")
    {
    maxlenstr = replace_in_string(maxlenstr, ".", underline, "regex", "copy")
    line = col_sep
    for (col = 0; col < cur_colpos[]; col++)
      {
      name = cur_colpos[col]
      if (do_all_eq)
        {
        if (all_equal[name])
          continue
        }
      line = line substring(maxlenstr, 0, cur_lengths[name]) col_sep
      }
    lines = lines line "\n"
    }

  # now write out lines
  for (i = 0; i < rec_num; i++)
    {
    line = col_sep
    for (col = 0; col < cur_colpos[]; col++)
      {
      name = cur_colpos[col]
      if (do_all_eq)
        {
        if (all_equal[name])
          continue
        }
      index = name $sub_sep i

      if (index in cur_table)
        val = cur_table[index]
      else
        val = ""

      len = cur_lenvals[name]
      q = search_string(val, float_point_RE, 0, "regex")
      if (q == 0)
        {
        # measure intpart length and decpart length
        decpos = search_string(val, ".", 0)
        vlen = length(val)
        if (decpos >= 0)
          intlen = decpos
        else
          intlen = vlen
        declen = vlen - intlen
        decl = cur_decpts[name] - declen
        # add trailing spaces to line up decimals
        if (decl > 0)
          val = val ljust(" ", decl)
        # now right justify the result
        val = rjust(val, len)
        }
      else
        val = ljust(val, len)

      if (len < cur_lengths[name])
        val = ljust(val, (len + cur_lengths[name]) / 2)

      line = line rjust(val, cur_lengths[name]) col_sep
      }
    lines = lines line "\n"
    }

  # and all-equal values
  if (show_all_eq)
    {
    eq_lines = ""
    for (col = 0; col < cur_colpos[]; col++)
      {
      name = cur_colpos[col]
      if (all_equal[name])
        eq_lines = eq_lines all_equal_val[name] "\n"
      }
    if (eq_lines != "")
      lines = lines "\n" eq_lines
    }
  if (lines != "")
    lines = lines "\n"

  return top_part lines bot_part
  }

# ==============================================================================
# DoVerticalToHorizontal(): This macro uses the current selection or the whole
#       file as text to convert, and asks the user for inter-column string,
#       underline character, and how columns with no value variation should be
#       treated. It then converts text using VerticalToHorizontal() and replaces
#       the source text with the result.
# ==============================================================================

define DoVerticalToHorizontal
  {
  top = 0
  bot = $text_length
  if ($selection_start != -1)
    {
    top = $selection_start
    bot = $selection_end

    top = start_of_line_pos(top)
    botfront = start_of_line_pos(bot)
    if (botfront < bot)
      bot = end_of_line_pos(bot)
    else
      bot = botfront
    }
  # pick up text
  text = get_range(top, bot)

  col_sep = " "
  underline = "-"

  # Column separator string
  ss = string_dialog("Enter column separator string\n" \
                     "(Default is a single space)", \
                     "OK", "Space", "Tab", "Cancel")
  if (ss != "" && $string_dialog_button == 1)
    col_sep = ss
  else if ($string_dialog_button == 2)
    col_sep = " "
  else if ($string_dialog_button == 3)
    col_sep = "\t"
  else if ($string_dialog_button != 1)
    return

  # Underline character
  ss = string_dialog("Enter underline character\n" \
                     "(Default is '-')", \
                     "OK", "'-'", "'='", "None", "Cancel")
  if (ss != "" && $string_dialog_button == 1)
    underline = substring(ss, 0, 1)
  else if ($string_dialog_button == 2)
    underline = "-"
  else if ($string_dialog_button == 3)
    underline = "="
  else if ($string_dialog_button == 4)
    underline = ""
  else if ($string_dialog_button != 1)
    return

  # all equal content?
  do_all_eq = dialog("Remove columns with all equal content?\n" \
                     "(equal-valued content can be displayed after the table)", \
                     "Yes, display", "Yes, no display", "No")
  show_all_eq = (do_all_eq == 1)
  do_all_eq = (do_all_eq == 1 || do_all_eq == 2)

  lines = VerticalToHorizontal(text, col_sep, underline, do_all_eq, show_all_eq)
  # and finally...
  replace_range(top, bot, lines)
  }