# ==============================================================================
# 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 ".*$"
  blank_line_RE = "^\\s*$"
  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|:\\d\\d)"
  date_only_RE = "^" date_RE " (?:12:00AM|00:00:00)$"
  date_time_RE = "^" date_RE " " time_RE "$"
  date_zero_RE = "^Jan [ 0]1 1900"

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

  # our return result
  lines = ""

  # find first and last candidate lines
  bot = length(text)
  top = 0

  # are we in a record/record list?
  inRec = 0
  inRecList = 0

  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

  blankLines = ""

  rec_num = 0
  maxlen = 0
  maxlenstr = ""
  dumpData = 0

  linesArray = split(text, "\n")
  linesArrayInd = 0
  linesArrayMax = linesArray[]
  linesArraySepLen = 0

  # examine each line at a time
  while (linesArrayInd in linesArray)
    {
    line = linesArray[linesArrayInd++]
    top = top + length(line) + 1
    if (linesArrayInd == linesArrayMax)
      nline = line
    else
      nline = line "\n"

    if (inRecList && top >= bot)
      dumpData = 1

    # does the line correspond to a record?
    isValueLine = search_string(line, value_line_RE, 0, "regex") == 0
    isBlankLine = search_string(line, blank_line_RE, 0, "regex") == 0
    isBreakList = !isValueLine && !isBlankLine

    if (isValueLine)
      {
      nline = ""
      blankLines = ""

      if (!inRecList)
        {
        inRecList = 1
        # throw out old values
        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 = ""
        dumpData = 0
        }
      if (!inRec)
        {
        inRec = 1
        }

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

      # add the name to the list of headers
      if (!(name in cur_headers))
        {
        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 = 11    # length of "Jan  1 1900"
          val = substring(val, 0, 11)
          }
        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 || !(name in all_equal))
          {
          all_equal[name] = 1
          all_equal_val[name] = line
          }
        else if (all_equal[name])
          {
          all_equal[name] = (all_equal_val[name] == line)
          }
        # else !all_equal[name] ==> not all equal, so don't test further
        }

      if (maxlen < len)
        {
        maxlen = len
        maxlenstr = val
        }
      prev_name = name
      }
    else if (isBlankLine)
      {
      if (inRecList)
        {
        if (inRec)
          {
          blankLines = blankLines nline
          nline = ""
          ++rec_num
          }
        else    # two blank lines in a row!
          {
          dumpData = 1
          inRecList = 0
          }
        inRec = 0
        }
      # else not inRecList - nothing to do: we will add nline
      }
    else # isBreakList
      {
      if (inRecList)
        {
        dumpData = 1
        inRecList = 0
        }
      # else not inRecList - nothing to do: we will add nline
      }

    if (dumpData)
      {
      # ----------- all data read for table output -----------
      dumpData = 0
      inRecList = 0
      if (inRec)
        ++rec_num
      # now we have everything in cur_table
      # evaluate max lengths and any_header
      any_header = !do_all_eq
      for (name in cur_headers)
        {
        len = length(name)
        cur_lenname[name] = len
        cur_lengths[name] = max(cur_lenvals[name], len)
        if (do_all_eq)
          {
          any_header += !(all_equal[name])
          }
        }
      if (!any_header && do_all_eq && !show_all_eq)
        {
        for (name in cur_headers)
          all_equal[name] = 0
        any_header = 1
        }

      if (any_header)
        {
        # write out columns in order
        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 eq_lines
        }
      if (lines != "")
        lines = lines blankLines

      # all table data written
      }
    # now add nline
    if (nline != "")
      lines = lines nline
    }

  return lines
  }

# ==============================================================================
# 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)

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

  # Underline character
  underline = "-"
  if (!all_defaults)
    {
    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 = 1
  show_all_eq = 1
  if (!all_defaults)
    {
    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)
  }

# ==============================================================================
# HorizontalToVertical(text, col_sep, out_sep, pad): This macro treats sequences of lines of
#       form "name: value" as field/value pairs common to all records; any empty
#       line is treated as a table separator, and handling then stops. If the
#       line starts with the col_sep string, it is treated as a table line
#       containing the non-common values for each record. The first of these
#       sets up field names from the field headings; if all field values are
#       strings of '-' or '=', the line is ignored. The records are output as a
#       sequence of lines of the form "name: value" once more.
#         text:         source text
#         col_sep:      column separator string
#         out_sep:      character(s) to separate fieldname from value
#         pad:          if true (non-zero), cause values to be aligned using
#                       space padding
# ==============================================================================

define HorizontalToVertical
  {
  text = $1
  col_sep = $2
  out_sep = ": "
  dopad = 0
  if ($n_args >= 3) out_sep = $3
  if ($n_args >= 4) dopad = $4

  common = $empty_array
  recs = $empty_array
  nrecs = 0
  colnames = $empty_array
  colnamesp = ""

  lend = 0
  havecolnames = 0
  basecolnames = 0
  for (lbeg = search_string(text, "^.*", lend, "regex"); \
       lbeg >= 0; \
       lbeg = search_string(text, "^.*", lend + 1, "regex"))
    {
    lend = $search_end
    line = substring(text, lbeg, lend)
    line = replace_in_string(line, "^\\s*$", "", "regex", "copy")
    if (line == "")
      break
    underline = ""
    allempty = 1
    if (substring(line, 0, length(col_sep)) == col_sep)
      {
      pieces = split(line, col_sep, "case")
      if (!havecolnames)
        {
        basecolnames = colnames[]
        for (i = 0; i in pieces; i++)
          {
          s = trim(pieces[i])
          colnames[basecolnames + i] = s
          if (length(s) > length(colnamesp))
            colnamesp = replace_in_string(s, ".", " ", "regex")
          }
        havecolnames = colnames[]
        }
      else
        {
        rec = $empty_array
        for (i = 0; i in pieces; i++)
          {
          s = trim(pieces[i])
          if (s != "" && (i in colnames))
            rec[colnames[basecolnames + i]] = s
          if (allempty && s != "")
            {
            c = substring(s, 0, 1)
            underline = "[^" quote_literal_as_regex(c) "]"
            if (search_string(s, underline, 0, "regex") >= 0)
              underline = ""
            }
          allempty *= (s == "")
          if (!allempty && underline != "" && \
              search_string(s, underline, 0, "regex") >= 0)
            underline = ""
          }
        if (underline == "" && !allempty)
          {
          recs[nrecs++] = rec
          rec = $empty_array
          }
        }
      }
    else
      {
      colon = search_string(line, ":", 0)
      if (colon <= 0)
        break
      colname = trim(substring(line, 0, colon))
      colval = trim(substring(line, colon + 1))
      if (colname != "")
        {
        colnames[colnames[]] = colname
        common[colname] = colval
        if (length(colname) > length(colnamesp))
          colnamesp = replace_in_string(colname, ".", " ", "regex")
        }
      }
    }
  # OK - generate output
  res = ""
  colname_to_i = $empty_array
  for (i = 0; i in colnames; i++)
    if (!(colnames[i] in colname_to_i))
      colname_to_i[colnames[i]] = i

  for (r = 0; r in recs; r++)
    {
    rec = recs[r]
    for (i = 0; i in colnames; i++)
      {
      colname = colnames[i]
      if (colname == "")
        continue
      if (i != colname_to_i[colname])
        continue
      colval = ""
      if (colname in common)
        colval = common[colname]
      if (colname in rec)
        colval = rec[colname]
      if (colval == "")
        continue

      if (dopad)
        pad = substring(colnamesp, 0, -length(colname))
      else
        pad = ""

      res = res colname out_sep pad colval "\n"
      }
    res = res "\n"
    }
  return res
  }

# ==============================================================================
# DoHorizontalToVertical(): This macro uses the current selection or the whole
#       file as text to convert, and asks the user for inter-column string. It
#       then converts text using HorizontalToVertical() and replaces the source
#       text with the result.
# ==============================================================================

define DoHorizontalToVertical
  {
  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)

  # Column separator string
  col_sep = " "
  ss = ""
  while (ss == "")
    {
    ss = string_dialog("Enter column separator string\n", \
                       "OK", "Tab", "Cancel")
    btn = $string_dialog_button
    all_defaults = (btn == 1 && ss == "")
    if (!all_defaults)
      {
      if (ss != "" && btn == 1)
        col_sep = ss
      else if (btn == 2)
        col_sep = "\t"
      else
        return
      }
    }

  out_sep = ": "
  ss = ""
  while (ss == "")
    {
    ss = string_dialog("Enter name-value separator string\n", \
                       "OK or '" out_sep "'", "Tab", "Cancel")
    btn = $string_dialog_button
    all_defaults = (btn == 1 && ss == "")
    if (!all_defaults)
      {
      if (ss != "" && btn == 1)
        out_sep = ss
      else if (btn == 2)
        out_sep = "\t"
      else
        return
      }
    }

  dopad = (2 == dialog("Align all values using spaces?", "No", "Yes"))

  repl = ""
  block = ""
  doblock = 0
  lend = 0
  for (lbeg = search_string(text, "^.*$", lend, "regex"); \
       lbeg >= 0; \
       lbeg = search_string(text, "^.*$", lend + 1, "regex"))
    {
    lend = $search_end
    fullline = substring(text, lbeg, lend)
    line = replace_in_string(fullline, "^\\s*$", "", "regex", "copy")

    if (line == "" || \
        !(substring(line, 0, length(col_sep)) == col_sep || \
          search_string(line, "^[^:]+:", 0, "regex") == 0))
      {
      if (block != "")
        {
        repl = repl HorizontalToVertical(block, col_sep, out_sep, dopad) \
                    "\n" fullline "\n"
        block = ""
        }
      else
        repl = repl fullline "\n"
      }
    else
      block = block fullline "\n"
    }

  # and finally...
  replace_range(top, bot, repl)
  }