/*
   Test.prg
   Convert csv file to dbf table with Libre Office.
   Using dBase 2.01, Windows 10, Libre Office 7.5.2.2.
   This code is a basic sample for tests.

   To look in GuestRequest.csv :
      - DateTime is a format YYYY-MM-DD hh:........
      - Date is a format MM/DD/YYYY.
   Append to txt, look the Excel formats before copy to the clipboard in WorksheetToDbf7Class.cc :
   http://news.dbase.com/newsgroups.php?art_group=dbase.shared-code&article_id=1334
*/

parameter cPath

// Its for me.
try
   set directory to &cPath
catch (exception e)
endtry
release cPath
// End for me.

// Parameters.
lDisplay = true                                      // Display Calc.
cFile    = set( "directory" ) + "\GuestRequest.csv"  // Csv file to open.
//cFile  = set( "directory" ) + "\Test_csv.csv"      // Csv file to open.
cTable   = set( "directory" ) + "\Test_dbf.dbf"      // Dbf table created.

main()
return

function main()
   local oVb   // VBScript object.

   oVb = new OleAutoClient( "msscriptcontrol.scriptcontrol" )
   oVb.language := "vbscript"

   oVb.addcode( 'Set oServiceManager = CreateObject( "com.sun.star.serviceManager" )' )
   oVb.addcode( 'Set Desktop = oServiceManager.createInstance( "com.sun.star.frame.Desktop" )' )

   // Open csv file config.
   oVb.addcode( 'Dim args(2)' )                                                   // Array for Desktop.
   oVb.addcode( 'Set args(0) = oServiceManager.Bridge_GetStruct( "com.sun.star.beans.PropertyValue" )' )
   oVb.addcode( 'args(0).name = "Hidden"' )                                   // Create a bridge for display or hide.
   oVb.addcode( 'args(0).value = ' + iif( not lDisplay, "true", "false" ) )    // true = display ( default ), false = hide.
   oVb.addcode( 'Set args(1) = oServiceManager.Bridge_GetStruct( "com.sun.star.beans.PropertyValue" )' )
   oVb.addcode( 'args(1).Name = "FilterName"' )
   oVb.addcode( 'args(1).Value = "Text - txt - csv (StarCalc)"' )
   // https://forum.openoffice.org/en/forum/viewtopic.php?t=103969&hilit=FilterOptions+loadComponentFromURL
   oVb.addcode( 'Set args(2) = oServiceManager.Bridge_GetStruct( "com.sun.star.beans.PropertyValue" )' )
   oVb.addcode( 'args(2).Name = "FilterOptions"' )
   oVb.addcode( 'args(2).Value = "44,34,76,1"' )     // GuestRequest.csv
//   oVb.addcode( 'args(2).Value = "59,34,76,1"' )   // Test_csv.csv

   try
      oVb.addcode( 'Set oDoc = Desktop.loadComponentFromURL("' ;
                  + FileToUrl( cFile ) +'", "_blank", 0, args )' )
   catch ( exception e )
      msgbox( e.message + chr( 10 )+ chr( 10 ) + "File : " + cFile, " Not Open.", 16 )
      return
   endtry

   // Save dbf table config.
   oVb.addcode( 'Dim args_SaveDbf(0)' )                                          // Array for Save dbf files.
   oVb.addcode( 'Set args_SaveDbf(0) = oServiceManager.Bridge_GetStruct( "com.sun.star.beans.PropertyValue" )' )
   oVb.addcode( 'args_SaveDbf(0).Name = "FilterName"' )
   oVb.addcode( 'args_SaveDbf(0).Value = "dBase"' )

   try
      oVb.addcode( 'oDoc.storeAsURL "' + FileToUrl( cTable ) + '", args_SaveDbf' )
   catch ( exception e )
      msgbox( e.message + chr( 10 )+ chr( 10 ) + "Table : " + cTable, " Not Save", 16 )
      return
   endtry

   oVb.addcode( 'oDoc.Close( True )' )
   oVb.addcode( 'Desktop.Terminate()' )

   oVb := null

   use "&cTable"
   list structure
   use

   return

// Transform a directory name with ("..\") in string ("c:\").
function DirPointToString( cDir )
   local f                                // File object.
   local cReturn                          // Directory name transformed.
   local cFileBat                         // File name of the batch file.
   local cFileDos                         // File created with Dos.
   cFileBat = set( "directory" ) + "\DirPointToString_" + Millisec( true ) + ".bat"
   cFileDos = set( "directory" ) + "\DirPointToString_" + Millisec( true ) + ".fbm"
   f = new File()
   f.delete( cFileBat )
   f.delete( cFileDos )
   f.create( cFileBat, "A" )
   f.puts( "cd " + cDir )
   f.puts( "echo %cd% >" + cFileDos )
   f.close()
   RunVBs('cmd /K "' + cFileBat + '" &exit', 0, true )
   f.delete( cFileBat )
   f.open( cFileDos, "R" )
   cReturn = trim( f.gets() )
   f.close()
   f.delete( cFileDos )
   return cReturn

// Transform a file name with ("..\") in string ("c:\").
function FilePointToString( cFile )
   local cReturn                          // File name transformed.
   local sFile                            // Name of the file without directory.
   sFile    = StringExtractRight( cFile, "\" )
   cReturn  = StringExtractLeft(  cFile, "\" )
   cReturn = DirPointToString( cReturn )
   cReturn += "\" + sFile
   return cReturn

// Convert a file name to Url.
function FileToUrl( cFile )
   local i                                       // Count variable.
   local cReturn                                 // Return Url name.
   local wFile                                   // File to transform in work.
   if at( "/", cFile ) > 0                       // Nothing to do.
      return cFile
   endif
   i       = 1
   cReturn = ""
   wFile   = ( cFile )
   if at( "\", wFile) = 0                        // Not back slash.
      wFile := set( "directory") + "\" + wFile
   endif
   if at( "..\", wFile ) > 0
      wFile := FilePointToString( wFile )
   endif
   do while i <= len( wFile )
      if subs( wFile, i, 1 ) = "\"
         cReturn += "/" 
      else
         cReturn += subs( wFile, i, 1 )
      endif  
      i++
   enddo
   cReturn = "file:///" + cReturn
   return cReturn

// Returns the milli seconds of the hour.
function MilliSec( lString )
   local d
   local xReturn
   if argcount() = 0
      lString = false
   endif
   d = new Date()
   xReturn = right( str( d.getTime(), 20, 0 ), 3 )
   if not lString
      xReturn = val( xReturn )
   endif
   return xReturn

// Run a command with options hidden and/or stop dBase.
function RunVBs( cCommand, nDisplay, lStop )
   local oVBscript           // Object for VBscript.
   if argcount() < 3
      lStop = true           // true = Stop dBasePLUS, false = not stop dBasePlus.
   endif
   if argcount() < 2
      nDisplay = 1           // 1 = Display normal cExe, 0 = Hidden.
   endif
   oVBscript = new OleAutoClient( "WScript.Shell" )
   oVBscript.Run( cCommand, nDisplay, lStop )
   oVBscript := null
   return

// Return the left string before 1 selected char( eg. directory name ).
function StringExtractLeft( sString, cComma )
   local cReturn
   local nRat
   nRat = rat( cComma, sString )
   cReturn = left( sString, nRat - 1 )
   return cReturn

// Return the right string after 1 string Comma ( eg. file name ).
function StringExtractRight( sString, cComma )
   return right( sString, len( sString ) - rat( cComma, sString ) - len( cComma ) + 1 )