Reading 3-dimensional arrays

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

   file threedimarr.mos
   ````````````````````
   Reading 3-dimensional arrays.
   - Using 'initializations from' with the odbc driver -
       
   (c) 2010 Fair Isaac Corporation
       author: S. Heipcke, July 2010, rev. Jan. 2014
*******************************************************!)

model ThreeDimArr
 uses "mmodbc"

 parameters
  CSTR = "threedim.mdb"        ! Use Access database `threedim.mdb'
!  CSTR = 'threedim.sqlite'    ! Use SQLite database `threedim.sqlite' directly
                               ! Use SQLite database `threedim.sqlite' via ODBC
!  CSTR = 'DSN=sqlite;DATABASE=threedim.sqlite'
 end-parameters

 declarations
  I: range
  J: set of string
  K = 1..5	              ! The last index set must be defined in the model
  A: dynamic array(I,J,K) of real
 end-declarations

 initializations from 'mmodbc.odbc:debug;noindex;' + CSTR
  A as 'Tab_23'
 end-initializations

 writeln("A: ")
 forall(i in I, j in J, k in K | exists(A(i,j,k)))
  writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))

end-model

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

   file threedimarr2.mos
   `````````````````````
   Reading 3-dimensional arrays.
   - Using SQL commands -
       
   (c) 2014 Fair Isaac Corporation
       author: S. Heipcke, Jan. 2014, rev. Aug. 2023
*******************************************************!)

model "ThreeDimArr (SQL)"
 uses "mmodbc", "mmsystem"

 parameters
!  CSTR = "threedim.xls"      ! Use Excel spreadsheet `threedim.xls'
  CSTR = "threedim.mdb"       ! Use Access database `threedim.mdb' 
!  CSTR = 'threedim.sqlite'   ! Use SQLite database `threedim.sqlite' directly
                              ! Use SQLite database `threedim.sqlite' via ODBC
!  CSTR = 'DSN=sqlite;DATABASE=threedim.sqlite'
 end-parameters

 declarations
  I: range
  J: set of string
  K = 1..5	              ! The last index set must be defined in the model
  A: dynamic array(I,J,K) of real
  Idx3: text
 end-declarations

 SQLconnect(CSTR)
 if not getparam("SQLsuccess"): setioerr("Database connection failed")

 setparam("SQLndxcol", false) ! Partially indexed data
 
 forall(k in K) Idx3+= (", Index3_"+k )
 SQLexecute("select Index1, Index2" + Idx3 + " from Tab_23", A)
 SQLdisconnect

 writeln("A: ")
 forall(i in I, j in J, k in K | exists(A(i,j,k)))
  writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))

  
end-model

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

   file threedimarr3.mos
   `````````````````````
   Reading 3-dimensional arrays.
   - Using 'initializations from' with Excel -
       
   (c) 2010 Fair Isaac Corporation
       author: S. Heipcke, July 2010, rev. Apr. 2014
*******************************************************!)

model "ThreeDimArr (Excel)"
 uses "mmsheet", "mmodbc"

 declarations
  I: range
  J: set of string
  K = 1..5	              ! The last index set must be defined in the model	
  A,B: dynamic array(I,J,K) of real
 end-declarations

 initializations from 'mmsheet.excel:partndx;threedim.xls'
  A as 'Tab_23'
 end-initializations

 writeln("A: ")
 forall(i in I, j in J, k in K | exists(A(i,j,k)))
  writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))

 (!
 initializations from 'mmodbc.odbc:debug;partndx;threedim.xls'
  B as 'Tab23odbc'
 end-initializations

 writeln("B: ")
 forall(i in I, j in J, k in K | exists(B(i,j,k)))
  writeln("B(", i, ",", j, ",", k, "): ", B(i,j,k))
!)
end-model

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

   file threedimarr4.mos
   `````````````````````
   Reading 3-dimensional arrays.
   - Using OCI statements -
       
   (c) 2014 Fair Isaac Corporation
       author: S. Heipcke, Jan. 2014, rev. Aug. 2023
*******************************************************!)

model "ThreeDimArr (OCI)"
 uses "mmoci", "mmsystem"

 parameters
  DB="myname/mypassword@dbname"     ! Login to Oracle database (not provided)
  CNCT="mmoci.oci:debug;"+DB
 end-parameters

 declarations
  I: range
  J: set of string
  K = 1..5	              ! The last index set must be defined in the model
  A: dynamic array(I,J,K) of real
  Idx3: text
 end-declarations

 setparam("OCIdebug",true)
 OCIlogon(DB)
 if not getparam("OCIsuccess"): setioerr("Database connection failed")
 
 setparam("OCIndxcol", false) ! Partially indexed data

 forall(k in K) Idx3+= (", Index3_"+k )
 OCIexecute("select Index1, Index2" + Idx3 + " from Tab_23", A)
 OCIlogoff

 writeln("A: ")
 forall(i in I, j in J, k in K | exists(A(i,j,k)))
  writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))
 
end-model

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

   file threedimarr5.mos
   `````````````````````
   Reading 3-dimensional arrays.
   - Using 'initializations from' with the xls/xlsx driver -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Apr. 2014
*******************************************************!)

model "ThreeDimArr (generic spreadsheet)"
 uses "mmsheet"

 parameters
  CSTR1= 'mmsheet.xls:threedim.xls'
  CSTR2= 'mmsheet.xlsx:threedim.xlsx'
 end-parameters

 declarations
  I: range
  J: set of string
  K = 1..5	              ! The last index set must be defined in the model
  A,B,C: dynamic array(I,J,K) of real
 end-declarations

 initializations from CSTR1
  A as 'partndx;Tab_23'
 end-initializations

 writeln("A: ")
 forall(i in I, j in J, k in K | exists(A(i,j,k)))
  writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))

 initializations from CSTR2
  B as 'partndx;Tab_23'
 end-initializations

 writeln("B: ")
 forall(i in I, j in J, k in K | exists(B(i,j,k)))
  writeln("B(", i, ",", j, ",", k, "): ", B(i,j,k))

! Using explicit cell addresses instead of a named range:
 initializations from CSTR1
  C as 'partndx;[Sheet1$A3:G8]'
 end-initializations

 writeln("C: ")
 forall(i in I, j in J, k in K | exists(C(i,j,k)))
  writeln("C(", i, ",", j, ",", k, "): ", C(i,j,k))
  
end-model

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

   file threedimarr6.mos
   `````````````````````
   Reading 3-dimensional arrays.
   - Using 'initializations from' with the csv driver -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Apr. 2014
*******************************************************!)

model "ThreeDimArr (CSV)"
 uses "mmsheet"

 parameters
  CSTR= 'mmsheet.csv:threedim.csv'
 end-parameters

 declarations
  I: range
  J: set of string
  K = 1..5	              ! The last index set must be defined in the model
  A: dynamic array(I,J,K) of real
 end-declarations

 initializations from CSTR
  A as 'partndx;[A3:G8]'
 end-initializations

 writeln("A: ")
 forall(i in I, j in J, k in K | exists(A(i,j,k)))
  writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))
  
end-model

(!******************************************************
   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