'CARPLST1 - process cross-tab data from dbase

external   $title lpath progress() sch scw

public     ptstr

global     #lines i #cols linesum newcell #newcol x y newright percent p1 p2
global     colsum #tot_lines


MAIN
single-step off
  load lpath|"displib.rf3" in-memory
  progress(15,10," Preparing report ... please wait ",0)

' justify Headings
  at r1c1
  layout justify right rows 1
  sheet goto cell r1c1
  at r1c1
  layout justify left block rc
  sheet goto right-edge                ' find Nr of rows in s/s
  #cols = column-1
' message "#cols is:"&str(#cols)
  sheet goto lower-edge                ' find Nr of rows in s/s
  #lines = row-1
'   layout format all numeric commas minus zero-blank precision 0

' insert totals & sort
  for i = 1 to #lines
    linesum = indirect("sum(r"|str(i+1)|"c2:"|str(#cols+1)|")")
    ssput(linesum,i+1,#cols+2)
  end for
  at r7c2
  layout format columns 6 numeric commas minus zero-blank precision 0
  at r2c2
'   edit sort r2:21c1:7 descending using column "7"
  edit sort "r2:"|str(#lines+1)|"c1:7" descending using column "7"

' add blank columns
  at r1c3
  edit insert columns 1
  at r1c5
  edit insert columns 1
  at r1c7
  edit insert columns 1
  at r1c9
  edit insert columns 1
  at r1c11
  edit insert columns 1
  sheet goto right-edge
  newright = column                    'message "newright is:"&str(newright)
  at r1c12
  enter text "TOTAL"

' insert percentages
  for x = 1 to #cols*2 step 2
    for y = 1 to #lines
      percent = ssget(y+1,x+1)/ssget(y+1,newright) ' message "percent is:"&str(percent)
      ssput(percent,y+1,x+2)
    end for
    newcell = "r"|str(1)|"c"|str(x+2)
    sheet goto cell newcell
    layout format columns 1 percent nocommas minus zero-blank precision 0
    layout cell-size width 4 columns 1
'     layout set-font change columns 1 1
  end for
  sheet goto lower-edge                ' find Nr of rows in s/s
  #tot_lines = row

' sum each column
  for i = 2 to 2*(#cols+1) step 2
    colsum = indirect("sum(r2:"|str(#tot_lines)|"c"|str(i)|")")
    ssput(colsum,#tot_lines+1,i)
  end for
  sheet goto lower-edge                ' find Nr of rows in s/s
  layout format rows 1 numeric commas minus zero-blank precision 0

' calc percent for each column
  for i = 3 to 2*(#cols+1) step 2
    percent = ssget(#tot_lines+1,i-1)/ssget(#tot_lines+1,newright)
    ssput(percent,#tot_lines+1,i)
    newcell = "r"|str(#tot_lines+1)|"c"|str(i)
    layout format block newcell percent nocommas minus show-all precision 0
    layout cell-size width 4 columns 1
  end for

' calc percent for TOTAL column
  for y = 1 to #tot_lines
    percent = ssget(y+1,newright)/ssget(#tot_lines+1,newright) 'message "percent is:"&str(percent)
    ssput(percent,y+1,newright+1)
    newcell = "r"|str(y+1)|"c"|str(newright+1)
'     layout format block newcell percent nocommas minus show-all precision 1
  end for
  newcell = "r"|str(y+1)|"c"|str(newright+1)
  sheet goto cell newcell
  layout format columns 1 percent nocommas minus zero-blank precision 1
  layout cell-size width 6 columns 1

' format & add headers
  at r1c1
'   layout set-font change columns 1 3

' add line at top
  at r1c1
  edit insert rows 1
  at r1c1
  ssput($title,1,1)

' add line below Shop titles
  at r3c1
  edit insert rows 1
  at r2c1
  layout lines bottom r2c1:13
'     newcell = "r"|str(y+1)|"c"|str(newright+1)
  layout lines bottom "r"|str(#tot_lines+2)|"c1:13"
  layout lines outline all

  layout justify right block rc
  screen clear box 1 1 sch scw 0 0 no-border
  repaint off
  progress(15,10," Printing report ... please wait ",0)
  print worksheet all enhanced copies 1 printer
  quit database project-file "retsht_1.rf3"
END MAIN