/*
AdvantageTableImport  

This Genie will import a table of data from Advantage. You must have Advantage running first and a box on the page for which the data is destined.
Written by Don Cox, Aug 93. Based on "ProCalc Import" genie and Advantage "Table" script. 
*/
numeric digits 8
cr  = '0a'x
options results

trace n
address command
call SafeEndEdit.rexx()
oldunits = ppm_GetUnits()
call ppm_SetUnits(1)
call ppm_AutoUpdate(0)

signal on halt
signal on break_c
signal on break_e
signal on break_d

if ~show(p, "Advantage") then exit_msg("Please start Advantage and select a range before running this Genie.")

box = ppm_ClickOnBox("Click on box in which to import table..")
if box = 0 then exit_msg()

/*  extract box attributes  */
boxsize = ppm_GetBoxSize(box)
boxposition = ppm_GetBoxPosition(box)

if ppm_Inform(2, "Delete box?",) = 1 then call ppm_DeleteBox(box)

/* tell user to start up Advantage    */
call ppm_Inform(1, "Select Range in Advantage",)
call ppm_ShowStatus("Extracting range from Advantage")


address 'Advantage'             /* Send commands to Advantage. */
options results                 /* Enable return of string results. */

'Current'                       /* Determine current selected range. */
range = result

colon_posn = pos(":",range)     /* Look for colon delimiter in range. */

if colon_posn = 0 then          /* If no colon, this is not a range. */
   do                           /*  Set the start and end cells to   */
      start_cell = range        /*   the selected cell.              */
      end_cell = range
   end
else                            /* Otherwise, it is a range. */
   /* Extract the start/end cells from the specified range. */
   do
      start_cell = left(range,colon_posn - 1)
      end_cell   = substr(range,colon_posn + 1)
   end
   
start_column = GetColumn(start_cell)    /* Determine range of columns */
end_column   = GetColumn(end_cell)      /*  to be filled.             */

start_row = GetRow(start_cell)          /* Determine range of rows */
end_row   = GetRow(end_cell)            /*  to be filled.          */

matrix. = ""

do rownum = start_row to end_row
   
   column = start_column                /* Reset column pointer.  */
   
   /* Read all columns in the current row. */
   do until c2d(column) > c2d(end_column)
      /* Build complete cell name from row and column. */
      next_cell = column || rownum
      /* Select the cell to be modified. */
      'SelectCell'                   
      value(next_cell)
      'GetValue'
      /* Put results into a matrix */
      matrix.rownum.column = result
      /* Advance to the next column and table entry. */
      column = NextColumn(column)
      
   end
   
end rownum
      

EndCol = c2d(end_column)
StartCol=c2d(start_column)

/*  calculate the dimensions of the new boxes   */
boxwidth    = word(boxsize, 1 ) / (EndCol - StartCol + 1 )
boxheight   = word(boxsize, 2 ) / (end_row - start_row + 1 )
boxleft     = word(boxposition, 1 )
boxtop      = word(boxposition, 2 )
boxright    = word(boxsize, 1) + boxleft
boxbottom   = word(boxsize, 2) + boxtop
boxmargin   = boxheight/20


call ppm_AutoUpdate(0)

bleft   = boxleft
btop    = boxtop

selection   = "Right Justify Numbers"cr"Make Grid Lines"
selection   = upper(ppm_SelectFromList("Select Options", 30, 5, 1, selection))
if pos(GRID, selection) ~= 0 then grid = 1
else grid       = 0
if pos(RIGHT, selection) ~= 0 then right = 1
else right  = 0


call ppm_SetSize(boxheight*52) /* Type size a bit less than box height, in points */ 

do rownum = start_row to end_row

    do column = StartCol to EndCol

        col = d2c(column)
        box = ppm_CreateBox(bleft, btop, boxwidth, boxheight, 0,        "CELL "col"-"rownum)
        bleft   = bleft + boxwidth
        call ppm_SetBoxMargins(box, boxmargin*2, boxmargin, boxmargin*2, boxmargin)

        if grid then
        do
            call ppm_SetBoxFrame(box, 1)
            call ppm_SetBoxFrameData(box, "black", "black", .5, 1, 0)
        end

                if matrix.rownum.col = "" then matrix.rownum.col = " "

        if right & datatype(matrix.rownum.col) = 'NUM' then
            call ppm_TextIntoBox(box, "\jr"matrix.rownum.col)
        else
            call ppm_TextIntoBox(box, "\jl"matrix.rownum.col)
    end

    bleft   = boxleft
    btop    = btop  + boxheight

end


exit_msg("Done","Resume")
break_d:
break_e:
break_c:
halt:
    call exit_msg("User aborted Genie")

exit_msg: procedure expose oldunits
do
    parse arg message

    call ppm_ClearStatus()
    if message ~= '' then call ppm_Inform(1, message,)
    call ppm_SetUnits(oldunits)
    call ppm_AutoUpdate(1)
    exit
end


/* ----------------------------------------------------------------- */
/*            Internal Functions (subroutines)                       */
/* ----------------------------------------------------------------- */

/* == GetRow: Extract the row number from a cell name. == */

GetRow: procedure
   arg CellName      /* Function expects to be passed a cell name. */

   /* Find the first numeric digit in the cell name. */
   start_number = verify(CellName,"0123456789","Match")
   
   /* Extract all characters starting at the first digit and continuing */
   /* to the end of the cell name.                                      */
   rownum = substr(CellName,start_number)

return rownum

/* == GetColumn: Extract the column label from a cell name. == */

GetColumn: procedure
   arg CellName      /* Function expects to be passed a cell name. */

   /* Find the first numeric digit in the cell name. */
   start_number = verify(CellName,"0123456789","Match")
   
   /* Extract all characters in the cell name up to the first numeric */
   /* character (start of row number).                                */
   column = left(CellName,start_number-1)

return column

/* == NextColumn: Given the current column label, determine the label == */
/* ==              for the next sequential column. NOTE: This is a    == */
/* ==              recursive function.                                == */

NextColumn: procedure
   arg ThisColumn    /* Function expects to be passed a column label. */

   /* If the column label is empty (null string), then we must be */
   /* starting a new group of column labels (for example, going   */
   /* from column ZZ to column AAA. Return an "A".                */
   if length(ThisColumn) = 0 then
      new_column = "A"
   
   /* Otherwise, we must advance the last character in the column */
   /* name to the next sequential alphabetic character.           */
   else
      do
         col_number = ,                 /* Convert last character */
            c2d( right(ThisColumn,1) )  /*  to decimal value.     */
   
         col_number = col_number + 1    /* Increment to next character. */
         new_char = d2c(col_number)     /* Convert back to a character. */
   
         /* If we've gone past 'Z', find the next column for the column */
         /* label minus the last character and then append an "A" to    */
         /* this label (start of a new label set).                      */
         if new_char > "Z" then
            do
               temp_column = left( ThisColumn, length(ThisColumn)-1 )
               new_column = NextColumn(temp_column) || "A"
            end
         
         /* Otherwise, replace the last character of the column label */
         /* with the next sequential character.                       */
         else
            new_column = overlay(new_char,ThisColumn,length(ThisColumn))
      end

return new_column