(!******************************************************
   Mosel Example Problems
   ======================

   file partidxstr.mos
   ```````````````````
   Reading partial-index table format with string column header 
   for the last index for spreadsheet files in XLS, XLSX or CSV format  

   The subroutine 'makelabel' provides a generic way of creating
   the required header definition for the 'partndx' format.

  (c) 2024 Fair Isaac Corporation
      author: S.Heipcke,Y.Colombani, Jan. 2024
*******************************************************!) 

model partidxstr
 uses "mmsystem", "mmreflect", "mmsheet"
 
(!@doc.
 @descr Constructing the header selection string for reading a partially indexed array
 @param fname Spreadsheet file name (XLS, XLSX, or CSV format) 
 @param rangename Name of a sheet or range (first sheet if not specified)
 @param u Array to be populated from the specified input
 @info The data array 'u' serves to determine the number of indices. If the array has N dimensions, it is assumed that the values of the first N-1 indices are contained in the N-1 initial columns of the data range and all other columns correspond to index values for the last dimension of the input array. 
!)
public function makelabel(fname:text,rangename:string,u:any):string
 declarations
  colnames:dynamic array(1..1,1..1000) of string  ! Due to the 'dynamic' flag this will work even if the actually selected area exceeds 1000 columns
  label:text
  allcols:set of string
  colorder:array(string) of integer
  cr: range
 end-declarations

 initialisations from fname
   colnames as "noindex;"+rangename
 end-initialisations
 if not u is array then
   writeln("Third argument of function 'makelabel' must be an array.")
   return
 end-if
 with nbdim=u.array.nbdim, ndx=u.array.index(nbdim).string do
  allcols:=union(i in nbdim..colnames.size){string(colnames(1,i))}
  if not allcols <= ndx then ndx+=allcols; end-if
  cr:=1..colnames.size
  forall(i in cr) colorder(colnames(1,i)):=i
  label:="skiph;partndx;"+rangename+"(#1"
  forall(c in 2..nbdim-1) label+=",#"+c
  forall(c in ndx|c in allcols) label+=",#"+colorder(c)
 end-do
 returned:=string(label+')')
end-function

!*******************************************************************

 procedure readpartindxl(drvfilename:string)
  declarations
   TabA: dynamic array(string,range,string) of real
   TabP: dynamic array(S1:set of string,R:range,S2:set of string) of real
   it: iterator
  end-declarations

 !**** Reading a partially indexed table with string header fields
  fopen(drvfilename,F_INPUT)
  initialisations from getfname(F_INPUT)
    TabA as makelabel(getfname(F_INPUT),"[]",TabA)
  end-initialisations
!  fclose(F_INPUT)

  writeln("Index sets:", TabA.index(1), TabA.index(2), TabA.index(3))
 ! Enumerate the existing array entries (using an iterator since sets are not named)
  inititer(it,TabA)
  while (nextcell(it)) writeln(it.indices, ":", TabA(it))

 !**** Reading a partially indexed table with preselection
  S1:={"xy","Z","BB","ab"}    ! Prepopulating the index set
  
!  fopen(drvfilename,F_INPUT)
! Various range specifications that result in the same selection:
  initialisations from getfname(F_INPUT)
    TabP as makelabel(getfname(F_INPUT),"pidxtest",TabP)  ! predef. range name
!    TabP as makelabel(getfname(F_INPUT),"[1$A:F]",TabP)
!    TabP as makelabel(getfname(F_INPUT),"[A:F]",TabP)
  end-initialisations
  fclose(F_INPUT)

  writeln("Index sets:", S1,R,S2)
 ! Enumerate the existing array entries
  forall(i in S1, j in R, k in S2 | exists(TabP(i,j,k)))
    writeln([i,j,k], ":", TabP(i,j,k))
 end-procedure

!*******************************************************************

 procedure readpartindcsv
  declarations
   TabA: dynamic array(string,range,string) of real
   TabP: dynamic array(S1:set of string,R:range,S2:set of string) of real
   it: iterator
  end-declarations

 !**** Reading a partially indexed table with string header fields
  fopen("mmsheet.csv:partidxdata.csv",F_INPUT)
  initialisations from getfname(F_INPUT)
    TabA as makelabel(getfname(F_INPUT),"[]",TabA)
  end-initialisations
!  fclose(F_INPUT)

  writeln("Index sets:", TabA.index(1), TabA.index(2), TabA.index(3))
 ! Enumerate the existing array entries (using an iterator since sets are not named)
  inititer(it,TabA)
  while (nextcell(it)) writeln(it.indices, ":", TabA(it))

 !**** Reading a partially indexed table with preselection
  S1:={"xy","Z","BB","ab"}    ! Prepopulating the index set
  
!  fopen("mmsheet.csv:partidxdata.csv",F_INPUT)
  initialisations from getfname(F_INPUT)
    TabP as makelabel(getfname(F_INPUT),"[A:F]",TabP)   
  end-initialisations
  fclose(F_INPUT)

  writeln("Index sets:", S1,R,S2)
 ! Enumerate the existing array entries
  forall(i in S1, j in R, k in S2 | exists(TabP(i,j,k)))
    writeln([i,j,k], ":", TabP(i,j,k))
 end-procedure

!**********************************************************

 writeln("XLS:")
 readpartindxl("mmsheet.xls:partidxdata.xls")
 writeln("XLSX:")
 readpartindxl("mmsheet.xlsx:partidxdata.xlsx")
 writeln("CSV:")
 readpartindcsv

end-model