REM  *****  BASIC  *****

Sub Main

End Sub

sub Export2CSV
rem Option Compatible
rem --------------------------------------------------------------------------------------
rem Name: Export2CSV 
rem An Open Office macro to convert a Open Office Calc file (similar to an Excel file)
rem to a type of CSV file in order to convert to a SEPA PAIN formatted file by the Windows
rem tool Excel2Sepa.
rem This macro is written by the developer of Excel2Sepa: Jan Willem Teunisse
rem See www.jwteunisse.nl email info(at)jwteunisse.nl
rem Written: 25-04-2020, Edited: 27-04-2020
rem
rem Tested for Credit Transfer (Zakelijke Betalingen) and for Direct debit (Incasso)
rem Copyright: J.W. Teunisse. Usage is at your own risk.
rem This open software is provided 'as-is', without any express or implied warranty. 
rem In no event will the author(s) be held liable for any damages arising from the use of
rem this piece of software. 
rem --------------------------------------------------------------------------------------  
rem define variables
dim document   as object
dim dispatcher as object
Dim oSheets       'The sheets object that contains all of the sheets
Dim oSheet        'Individual sheet
Dim oSheetEnum    'For accessing by enumeration
Dim oSourceDoc, oSourceSheet, oSourceRange
Dim sUrl As String
Dim NoArg()
Dim sheetName as String
Dim s As String   'String variable to hold temporary data
Dim i, k As Integer  'Index variable
Dim dataKolommen as Integer
Dim nrWS, statusRow as integer ' number of worksheets, statusRow for presenting status
Dim shBatch, shData, shIBANS as integer
Dim oDoc as Variant
dim rubriek, waarde as string
dim vandaag, osType as string
dim ODSPathFileName, ODSFileName, CSVPathFileName, ExeE2SPath as string
dim CSVRegel as string
dim tblIBANS as boolean
dim fout as boolean   ' fouten aangetroffen tijdens conversie
dim CSVfh as integer
dim NR_CT, NR_DD as integer
dim result as long
dim returnCode as integer

rem ----------------------------------------------------------------------
NR_CT = 11   ' max number Data columns for Credit Transfer (zakelijke betalingen)
NR_DD = 14   ' max number Data columns for Direct Debit (Incasso's) - kolom A..N
osType = CStr(GetGUIType())
if osType = "1" then
  osType = "Windows"
elseif osType = "4" then
  osType = "Linux"
else
  osType = "Unknown"
end if       
vandaag = Date() & " om "& Time()
shBatch = -1  ' default not present
shData  = -1 
shIBANS = -1
tblIBANS =false ' default no IBANS worksheet present
fout = false    ' default nog geen fouten gevonden
rem get access to the document
ODSPathFileName = ThisComponent.getLocation()
i = ReverseInStr(ODSPathFileName, "/")
if i > 0 then
  ODSFileName = Right(ODSPathFileName, len(ODSPathFileName)-i)
else
  ODSFileName= ODSPathFileName
end if
ExeE2Spath = Environ("EXCEL2SEPA")

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = 1
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem Set source doc/currentController/frame/sheet/range.
  oSourceDoc=ThisComponent
  octl = oSourcedoc.getCurrentController()
  oSourceframe = octl.getFrame()
  nrWS = thisComponent.Sheets.getCount() 
  For i = 0 To nrWS - 1
    waarde = thisComponent.Sheets.getByIndex(i).Name
    s = s & "Sheet " & i & " = " & waarde & CHR$(10)
    waarde = UCase(waarde)
    if waarde = "BATCH" then
       shBatch = i
    elseif waarde = "DATA" then
       shData = i
    elseif waarde = "IBANS" then
       shIBANS = i
    end if
  Next  
  if shBatch = -1 OR shData = -1 then
    Msgbox s, 0, "No Batch or Data WorkSheets found, converting stops!"
    Exit sub  'stop program
  end if  
  
rem ---- open file for writing exporting to Excel2Sepa CSV format
  CSVPathFileName = Left$(ODSPathFileName,Len(ODSPathFileName)-4) + ".csv"    ' the Export filename
  CSVfh = FreeFile()                                  'Next free file number
  Open CSVPathFileName For Output Access Write As #CSVfh    'Open for read/write
  CSVRegel = "# aangemaakt vanuit Open Office Calc/" & ODSFileName & " op " & vandaag
  Print #CSVfh, CSVRegel ' & CHR$(13)&CHR(10)          'Write some text    

rem ---- loop door Batch rijen naar beneden tot eerste vrije cel A(n, 1) == leeg
  oSheet = thisComponent.Sheets.getByIndex(shBatch)
  oCell = oSheet.getCellRangeByName("StatusLine") 'Cell Batch.StatusLine
  oCell.setString("Verwerken werkblad Batch")
  i = 0  'reset to first row
  Do
    oCell = oSheet.getCellByPosition(0, i) 'Cell A1
    rubriek = oCell.getString()
    if rubriek <> "" then 
       oCell = oSheet.getCellByPosition(1, i) 'Cell B1
       waarde = oCell.getString()
       CSVRegel = rubriek & "=" & waarde
       Print #CSVfh, CSVRegel
       if UCase(rubriek) = "INCASSO_ZAKELIJKEBETALING" then
          if UCase(waarde) = "Z" then
             datakolommen = NR_CT  ' aantal kolommen 
          else
             datakolommen = NR_DD
          end if
       end if
       if UCase(rubriek) = "ZB_MULTIPLE_IBANS" then
          if UCase(waarde) = "TRUE" then
             tblIBANS = true
          end if   
       end if          
       i = i + 1
    else  ' waarde eerste kolom is empty
       i = -1
    end if      
  Loop until i = -1     
  Print #CSVfh, "Data_bestand = intern" ' & CHR$(13)&CHR(10)
  Print #CSVfh, "#eob#" ' & CHR$(13)&CHR(10) 'end of batch part
  Print #CSVfh, ""
rem --- loop door eventueel IBANS worksheet
 if shIBANS <> -1 AND tblIBANS then
   oSheet = thisComponent.Sheets.getByIndex(shBatch)
   oCell = oSheet.getCellRangeByName("StatusLine") 'Cell Batch.StatusLine
   oCell.setString("Verwerken werkblad IBANS")
   oSheet = thisComponent.Sheets.getByIndex(shIBANS)
   Print #CSVfh, "# IBAN Table"
   i = 0  'reset to first row
   Do
     oCell = oSheet.getCellByPosition(0, i) 'Cell A1
     rubriek = oCell.getString()   ' get IBAN 
     if rubriek <> "" then 
       oCell = oSheet.getCellByPosition(1, i) 'Cell B1
       waarde = oCell.getString() ' get Back account name
       CSVRegel = rubriek & ";" & waarde
       Print #CSVfh, CSVRegel          
       i = i + 1
     else  ' waarde eerste kolom is empty
       i = -1
     end if      
   Loop until i = -1
   Print #CSVfh, "#eot#"   
   Print #CSVfh, ""
 elseif shIBANS = -1 AND tblIBANS then
   Msgbox "Geen IBANS werkblad aangetroffen, converting stops!", 0, "Error found!"
   Print #CSVfh, "# Geen IBANS werkblad aangetroffen, is wel vereist. Conversie stopt!"   
   Print #CSVfh, ""    
   Close #CSVfh  'Close the output file    
   Exit sub  'stop program
 elseif shIBANS <> -1 AND NOT tblIBANS then
   fout = true
   Msgbox "Wel IBANS werkblad aangetroffen, maar geen rubriek ZB_Multiple_IBANS=true in Batch werkblad!", 0, "Warning found!"
   Print #CSVfh, "# conversion IBANS werkblad niet uitgevoerd, want geen rubriek ZB_Multiple_IBANS=true in Batch werkblad!"   
   Print #CSVfh, ""             
 end if ' van IBANS worksheet
rem --- loop door de DATA worksheet
  oSheet = thisComponent.Sheets.getByIndex(shBatch)
  oCell = oSheet.getCellRangeByName("StatusLine") 'Cell Batch.StatusLine
  oCell.setString("Verwerken werkblad Data")
  Print #CSVfh, "# Data transactions"
  oSheet = thisComponent.Sheets.getByIndex(shData)
  i = 0  'reset to first row
  Do
    oCell = oSheet.getCellByPosition(0, i) 'Cell A1
    waarde = oCell.getString()
    if waarde <> "" then
       if Left(waarde,1,1) <> ";" AND Left(waarde,1,1) <> "#" then ' geen comment line
         CSVRegel = waarde
         if i = 0 then
           if UCase(Left$(waarde, 4)) <> "NAAM" then
              dataKolommen = dataKolommen + 1
           elseif UCase(Left$(waarde, 4)) = "NAAM" AND tblIBANS then
             fout = true
             Print #CSVfh, "# Geen kolom A met IBANS aangetroffen, terwijl door optie ZB_Multiple_IBANS dit wordt verwacht"
             Msgbox "Geen kolom A met IBANS aangetroffen, terwijl door Batch optie ZB_Multiple_IBANS dit wordt verwacht", 0, "Error found!" 
           end if    
         end if
         for k = 1 to dataKolommen - 1   ' we start at zero   
           oCell = oSheet.getCellByPosition(k, i) 'Cell B1
           waarde = oCell.getString()
           CSVRegel = CSVRegel & ";" & waarde
         next
       else ' comment line
         CSVRegel = "#" & Mid(waarde, 2,len(waarde))
       endif      
       Print #CSVfh, CSVRegel
       i = i + 1
    else  ' waarde is empty
       i = -1
    end if      
  Loop until i = -1   
rem ---- afsluiten
  CSVRegel = "# einde Data transacties deel"
  Print #CSVfh, CSVRegel ' & CHR$(13)&CHR(10)
  CSVRegel = "#eof#"
  Print #CSVfh, CSVRegel ' & CHR$(13)&CHR(10)
  Close #CSVfh  'Close the output file
  if osType = "Windows" then
    CSVPathFileName = ConvertFromURL(CSVPathFileName)  'zet om naar Windows local notatie
  end if
  if NOT fout then
    if osType = "Windows" AND ExeE2SPath <> "" then 
      returnCode = MsgBox("Export File " & CSVPathFileName & " aangemaakt, wilt u nu Excel2Sepa laten opstarten", 4, "Open Office Calc Excel2Sepa conversie")
      ' todo indien Windows dan Excel2Sepa opstarten?
      if returnCode = 6 then  '6 means YES
        ExeE2SPath = ExeE2SPath & "\Excel2Sepa.exe"
        s = CSVPathFileName  ' argumenten op cmdline
        result = Shell(ExeE2SPath, 1, s, True)  ' Shell(Pathname, Windowstyle, Param, bSync)
        'result afvangen
      else
       MsgBox("U kunt de Export File " & CSVPathFileName & " later met Excel2Sepa verwerken.", 0,"Open Office Calc Excel2Sepa conversie")    
      end if ' end of Excel2Sepa runnen
    else ' geen Windows 
      MsgBox "Export File " & CSVPathFileName & " aangemaakt, u kunt nu Excel2Sepa opstarten voor verdere verwerking naar Sepa", 0, "Open Office Calc Excel2Sepa conversie"
    end if  
  else ' wel fouten aangetroffen
    MsgBox("Fouten gevonden tijdens maken van de Export File, graag eerst herstellen.", 0, "Open Office Calc Excel2Sepa conversie")  
  end if
  oSheet = thisComponent.Sheets.getByIndex(shBatch)
  oCell = oSheet.getCellRangeByName("StatusLine") 'Cell Batch.StatusLine
  vandaag = Date() & " om "& Time()
  oCell.setString("Klaar met Exporteren op " & Vandaag)
end sub
rem einde Export2CSV

rem integer function ReverseInStr(orgStr, sStr)
rem returns integer position of the searchString sStr in the orginalString orgStr
rem if nothing is found -1 or 0 is returned
function ReverseInStr(orgStr, sStr) as Integer
 lo = len(orgStr)
 ls = len(sStr)
 i = lo - ls
 midStr = ""
 found = false
 do
  midStr = Mid(orgStr, i, ls) 
  if midStr = sStr then
    found = true
  else
   i = i - ls
  end if    
 loop until found OR i <= 0
 ReverseInStr = i
end function