Sub MakeNewQuerySheet()
	Dim QueryOutputSheetName As String
	Dim QueryOutputSheet
	Dim oSheets
	
	QueryOutputSheetname = "QueryResults"
	
	oSheets = ThisComponent.Sheets
	
	REM ***
	REM * if there's already a QuerySheet: toss it away
	REM * a new one will be made
	REM ***
	If oSheets.hasByName(QueryOutputSheetName) Then
		oSheets.removeByName(QueryOutputSheetName)
	End If
	
	REM ***
	REM * Now make a new sheet
	REM ***
	oSheets.insertNewByName(QueryOutputSheetName,oSheets.getCount())
	
	REM ***
	REM * now fill the sheet with data from the jdbc source
	REM ***
	QueryOutputSheet=oSheets.getByname(QueryOutputSheetName)
	REM QueryOutputSheet.fillFomExternalSource();
	
End Sub

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

Sub Main
End Sub

Sub MaakNieuweQuerySheet()
	Dim QueryOutputSheetName As String
	Dim QueryOutputSheet
	Dim oConnection
	Dim myDS
	Dim oSheets
	Dim DatabaseName as String
	Dim controlSheet
	Dim statusCell
	
	QueryOutputSheetname = "QueryResults"
	DatabaseName = "isbmonitor"
	
	oSheets = ThisComponent.Sheets
	controlSheet = oSheets.getByName("Uitleg")
	
	REM ***
	REM * All status cells red
	REM ***
	statusCell = controlSheet.getCellByPosition(0,12)
	statusCell.CellBackColor=RGB(255,0,0)
	statusCell = controlSheet.getCellByPosition(1,12)
	statusCell.CellBackColor=RGB(255,0,0)
	statusCell = controlSheet.getCellByPosition(2,12)
	statusCell.CellBackColor=RGB(255,0,0)	
	statusCell = controlSheet.getCellByPosition(3,12)
	statusCell.CellBackColor=RGB(255,0,0)
	statusCell = controlSheet.getCellByPosition(4,12)
	statusCell.CellBackColor=RGB(255,0,0)
	statusCell = controlSheet.getCellByPosition(5,12)
	statusCell.CellBackColor=RGB(255,0,0)
	
	' counter cells ook opschonen
	statusCell = controlSheet.getCellByPosition(2,13)
	statusCell.setValue(0)
	statusCell = controlSheet.getCellByPosition(4,13)
	statusCell.setString("0 van 0")
	statusCell = controlSheet.getCellByPosition(5,13)
	statusCell.setString("0 van 0")
	
	MsgBox("Ga pas verder kijken bij de volgende messagebox.", "Start")

	REM ***
	REM * now we can really begin
	REM ***
	statusCell=controlSheet.getCellByPosition(0,12)
	statusCell.setString("Start")
	statusCell.CellBackColor=RGB(255,255,0)
	
	REM ***
	REM * Als er al een query output sheet is, wegmikken
	REM * We maken een nieuwe
	REM ***
	If oSheets.hasByName(QueryOutputSheetName) Then
		oSheets.removeByName(QueryOutputSheetName)
	End If
	statusCell.setstring("Cleanup")
	statusCell.CellBackColor=RGB(0,255,0)
	statusCell = controlSheet.getCellByPosition(1,12)
	statusCell.setString("Query")
	statusCell.CellBackColor=RGB(255,255,0)
	
	REM ***
	REM * Nieuwe sheet maken, de oude is weg
	REM ***
	oSheets.insertNewByName(QueryOutputSheetName,oSheets.getCount())
	
	REM ***
	REM * connect to the database
	REM ***
	Dim oBaseContext
	Dim DBContext
	Dim DBConn
		
	oBaseContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
	If NOT oBaseContext.hasElements() Then
	    MsgBox "There are no registered data sources."
	End If

	If(oBaseContext.hasByName(DatabaseName)) Then
		DBContext = oBaseContext.getByName(DatabaseName)
	Else
		MsgBox "Database " & DatabaseName & " is not registered..."	
	End If
	
	REM ***
	REM * hier gebruiken we een handler voor
	REM ***
	Dim DbConnHanler
	DbConnHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
	DBConn = DBContext.ConnectWithCompletion(DbConnHandler)

	REM ***
	REM * we're connected now, I hope
	REM ***
	Dim oStatement
	Dim sSQL$
	Dim oResult
	oStatement=DBConn.CreateStatement()
	sSQL="SELECT * FROM permanentpdumonitor WHERE monitor_event = 'F' and repair_pdu_id is null"
	oResult = oStatement.executeQuery(sSQL)
	
	statusCell.CellBackColor=RGB(0,255,0)
	statusCell = controlSheet.getCellByPosition(2,12)
	statusCell.setString("toData")
	statusCell.CellBackColor=RGB(255,255,0)
	
	REM ***
	REM * nieuwe sheet vullen met data uit jdbc koppeling
	REM ***
	QueryOutputSheet=oSheets.getByname(QueryOutputSheetName)
	
	Dim oData()
	ResultSetToData(oResult,ThisComponent,"MaakNieuweQuerySheet",oData())
'	ResultSetToSheet(oResult,QueryOutputSheet)
	
	statusCell.CellBackColor=RGB(0,255,0)
	statusCell = controlSheet.getCellByPosition(3,12)
	statusCell.setString("toSheet")
	statusCell.CellBackColor=RGB(255,255,0)
	
	AppendDataToSheet(QueryOutputSheet,oData())
	statusCell.CellBackColor=RGB(0,255,0)
	
	REM ***
	REM * Disconnect from the database
	REM ***
	DBConn.close()
	
	REM ***
	REM * Now clear InBehandeling
	REM ***
	
	statusCell = controlSheet.getCellByPosition(4,12)
	statusCell.setString("InBehandeling")
	statusCell.CellBackColor=RGB(255,255,0)
	CleanupInBehandeling()
	statusCell.CellBackColor=RGB(0,255,0)

	statusCell = controlSheet.getCellByPosition(5,12)
	statusCell.setString("QueryOutput")
	statusCell.CellBackColor=RGB(255,255,0)
	CleanupQueryOutput()
	statusCell.CellBackColor=RGB(0,255,0)

	MsgBox "Aan de slag!"		
End Sub

Sub ResutSetToSheet(oResult,oSheet)

	Dim oSheets
	Dim controlSheet
	Dim controlCell
	Dim oData()
	Dim Counter As Long
	Dim n
	Dim i
	
	oSheets=ThisComponent.sheets
	controlSheet = oSheets.getByName("Uitleg")
	controlCell = controlSheet.getCellByPosition(2,13)

	Counter = 0
	controlCell.setValue(Counter)
	
	n = 0
	Do While oResult.next()
		If n = 0 Then
			oMeta = oResult.getMetaData()
			n = oMeta.getColumnCount()
			oData() = DimArray(n)
			For i = 1 to n
				oData(i-1) = oMeta.getColumnName(i)
				oSheet.addRow(oData())
			Next
		End If
			
	Loop

End Sub

Sub CleanupInBehandeling()
	Dim QueryOutputSheet
	Dim InBehandelingSheet
	Dim ControlSheet
	Dim Sheets
	Dim QueryOutputSheetName As String
	Dim InBehandelingSheetName As String
	Dim ControlSheetName
	Dim StatusString
	Dim StatusCell
	
	QueryOutputSheetName="QueryResults"
	InBehandelingSheetName="InBehandeling"
	ControlSheetName="Uitleg"
	
	Sheets = ThisComponent.Sheets
	QueryOutputSheet=Sheets.getByName(QueryOutputSheetName)
	InBehandelingSheet=Sheets.getByName(InBehandelingSheetName)
	
	REM ***
	REM * for each pdu_id (column name) in InBehandeling
	REM * - see if that pdu exists in QueryOutputSheet
	REM * - if not: delete the row
	REM ***
	
	' pdu_id is column B in "InBehandeling"
	' pdu_id is column A in "QueryResults"
	Dim IBCol As Integer
	Dim IBCurRow As Integer
	Dim IBEndRow As Integer
	Dim IBEndCol As Integer
	Dim aAddress As Variant
	Dim oCursor As Object
	Dim oCell As Object
	Dim StringToFind As String
	Dim Found As Variant
	Dim VerwijderCount As Long
	Dim VerwijderArray()
	Dim n as Long
	
	IBCol=1 ' column B is 1
	oCell=InBehandelingSheet.getCellByPosition(1,1)
	oCursor = InBehandelingSheet.createCursorByRange(oCell)
	oCursor.GoToEndOfUsedArea(True)
	aAddress = oCursor.RangeAddress
	
	IBEndRow=aAddress.EndRow
	IBEndCol=IBCol
	
	VerwijderCount=0
	
	controlSheet=ThisComponent.sheets.getByName(ControlSheetName)
	statusCell = controlSheet.getCellByPosition(4,13)
	statusString = "0 van " & VerwijderCount
	statusCell.setString(StatusString)
	
	For IBCurRow = 1 To IBEndRow 'we skip the header
		oCell = InBehandelingSheet.GetCellByPosition(IBCol,IBCurRow)
		StringToFind=oCell.String
'		Found = true
		
		Found = SheetSearch(StringToFind,QueryOutputSheet,true)
'		Print StringToFind & ":" & Found '& CHR$(10)
		
		if(IsNull(Found)) Then
'			print "String: " & StringToFind & "; Count: " & VerwijderCount & "."
			AppendToArray(VerwijderArray, StringToFind)
'			VerwijderArray(VerwijderCount) = StringToFind
			VerwijderCount = VerwijderCount + 1
		
			statusString = "0 van " & VerwijderCount
			statusCell.setString(StatusString)
		End If
	Next

	Dim IndexCell
	Dim IndexRow
	
	StatusString = "0 van " & VerwijderCount
	statusCell.setString(StatusString)
	If VerwijderCount > 0 Then
		For n = 0 To VerwijderCount-1
			IndexCell=SheetSearch(VerwijderArray(n),InBehandelingSheet,true)
			if(Not IsNull(IndexCell)) Then
				IndexRow=IndexCell.CellAddress.Row
				InBehandelingSheet.Rows.removeByIndex(IndexRow,1)
			
				StatusString = n+1 & " van " & VerwijderCount
				StatusCell.setString(StatusString)
			End If
		Next
	End If
	
'	MsgBox VerwijderCount & " rows uit '" & InBehandelingSheetName & "' verwijderd."
End Sub

Sub CleanupQueryOutput()
	Dim QueryOutputSheet
	Dim InBehandelingSheet
	Dim Sheets
	Dim QueryOutputSheetName As String
	Dim InBehandelingSheetName As String
	Dim ControlSheet
	Dim ControlSheetName
	Dim StatusCell
	Dim StatusString As String
	
	QueryOutputSheetName="QueryResults"
	InBehandelingSheetName="InBehandeling"
	ControlSheetName="Uitleg"
	
	Sheets = ThisComponent.Sheets
	QueryOutputSheet=Sheets.getByName(QueryOutputSheetName)
	InBehandelingSheet=Sheets.getByName(InBehandelingSheetName)
	ControlSheet=Sheets.getByName(ControlSheetName)
	
	REM ***
	REM * for each pdu_id (column name) in QueryOutputSheet
	REM * - see if that pdu exists in InBehandelingSheet
	REM * - if so: delete the row (we're already working on that item)
	REM ***
	
	' pdu_id is column B in "InBehandeling"
	' pdu_id is column A in "QueryResults"
	Dim QOCol As Integer
	Dim QOCurRow As Integer
	Dim QOEndRow As Integer
	Dim QOEndCol As Integer
	Dim aAddress As Variant
	Dim oCursor As Object
	Dim oCell As Object
	Dim StringToFind As String
	Dim Found As Variant
	Dim VerwijderCount As Long
	Dim VerwijderArray()
	
	QOCol=0 ' column A is 0
	oCell=QueryOutputSheet.getCellByPosition(1,0)
	oCursor = QueryOutputSheet.createCursorByRange(oCell)
	oCursor.GoToEndOfUsedArea(True)
	aAddress = oCursor.RangeAddress
	
	QOEndRow=aAddress.EndRow
	QOEndCol=IBCol
	
	VerwijderCount=0
	StatusCell = controlSheet.getCellByPosition(5,13)
	statusString = "0 van " & VerwijderCount
	
	For QOCurRow = 1 To QOEndRow 'we skip the header
		oCell = QueryOutputSheet.GetCellByPosition(QOCol,QOCurRow)
		StringToFind=oCell.String
		
		Found = SheetSearch(StringToFind,InBehandelingSheet,true)
		
		if(not IsNull(Found)) Then
			AppendToArray(VerwijderArray, StringToFind)
			VerwijderCount = VerwijderCount + 1
			
'			removeArray(removeCounter) = StringToFind
'			removeCounter = removeCounter + 1
		
			StatusString = "0 van " & VerwijderCount
			StatusCell.setString(StatusString)
		End If
	Next
	
	Dim n As Long
	Dim RemoveCell
	Dim RemoveRow
	for n = 0 To VerwijderCount-1
		RemoveCell=SheetSearch(VerwijderArray(n),QueryOutputSheet,true)
		RemoveRow=RemoveCell.CellAddress.Row
		QueryOutputSheet.Rows.removeByIndex(RemoveRow,1)
		
		StatusString = n+1 & " van " & VerwijderCount
		statusCell.setString(StatusString)
	Next
	
'	MsgBox VerwijderCount & " rows uit '" & QueryOutputSheetName & "' verwijderd."
	
End Sub


Function SheetSearch(sString$, oSheet, bWholeWord As Boolean) As Variant
	Dim oDescriptor
	Dim oFound
	
	oDescriptor = oSheet.createSearchDescriptor()
	
	With oDescriptor
		.SearchString = sString$
		.SearchWords = bWholeWord
		.SearchCaseSensitive = True
	End With

	oFound = oSheet.findFirst(oDescriptor)
	
	SheetSearch = oFound	
End Function

Sub AppendDataToSheet(oSheet, oData())
REM 	Dim oSheet     'The first sheet in the document.
	Dim oAddr      'Address of the current cursor.
	Dim iNumRows%  'Number of rows to add.
	Dim iNumCols%  'Number of columns to add.
	Dim x()        'Utility variable used as a single row.
	Dim oRange     'Range to add the data and focus the cursor.

	oAddr = oSheet.getCellByPosition(0,0)

	REM Determine the number of rows in the data.
	REM Next, obtain the first row and see how many columns it has.
	iNumRows% = UBound(oData()) - LBound(oData())
	x() = oData(lBound(oData()))
	iNumCols = UBound(x()) - LBound(x())

	oRange = oSheet.getCellRangeByPosition(0, 0, iNumCols, iNumRows)
	
	oRange.setDataArray(oData())
	oRange.getColumns().optimalWidth = True
	oRange = oSheet.getCellByPosition(0, iNumRows+1)
End Sub


Sub ResultSetToData(oResult, oDoc, sFunc$, oData())
	Dim oData2()   'Temporary row data array.
	Dim oMeta      'Each result set also has meta-data.
	Dim n As Long  'Number of columns returned by a result set.
	Dim i As Long  'General index variable.
	Dim nRowCount& 'Count of the number of rows.
	Dim x          'Generic work variable.
	Dim oSheets
	Dim controlSheet
	Dim controlCell
	
	oSheets=ThisComponent.sheets
	controlSheet = oSheets.getByName("Uitleg")
	controlCell = controlSheet.getCellByPosition(2,13)
	
	If IsNull(oResult) OR IsEmpty(oResult) Then
		oData() = Array(Array(sFunc, "NULL result set returned"))
		Exit Sub
	End If

	oData() = Array()
	Do While oResult.next()
		If n = 0 Then
			REM Add column titles.
			oMeta = oResult.getMetaData()
			n = oMeta.getColumnCount()
			oData2() = DimArray(n)
			For i = 1 To n
				oData2(i-1) = oMeta.getColumnName(i)
			Next
			REM Advanced programming concept....
			REM Do not replace the next two lines with:
			REM      oData() = Array(oData2())
			REM because it would use a reference to oData2().
			REM AppendToArray() copies the array by value.
			AppendToArray(oData(), oData2())
		End If

		REM The first column is always the function name.
		oData2() = Array()
		oData2() = DimArray(n)
    
		REM Although the row number should be available using
		REM oResult.getRow(), this is not always the case.
		nRowCount = nRowCount + 1
		For i = 1 To n
				oData2(i-1) = oResult.getString(i)
				controlCell.setValue(nRowCount)
		Next
		AppendToArray(oData(), oData2())
	Loop

	REM In case no rows are returned...
	If n = 0 AND nRowCount = 0 Then
		oData() = Array(Array(sFunc, "No rows returned"))
	End If
End Sub

Sub AppendToArray(oData(), ByVal x)
	Dim iUB As Long  'The upper bound of the array.
	Dim iLB As Long  'The lower bound of the array.
	iUB = UBound(oData()) + 1
	iLB = LBound(oData())
	ReDim Preserve oData(iLB To iUB)
	oData(iUB) = x
End Sub


Sub SchoonQueryOutput()
	REM ***
	REM * Variabelen declaraties
	REM ***
	
	Dim QueryOutputSheetName As String
	Dim InVerwerkingSheetName As String
	Dim VerwerkSheet
	Dim QuerySheet
	Dim VerwerkPDUCells
	Dim VerwerkPDUArray()
		
	QueryOutputSheetName = "QueryResults"
	InVerwerkingSheetName = "InVerwerking"
	
	VerwerkSheet = ThisComponent.Sheets.getByName(InVerwerkingSheetName)
	QuerySheet   = ThisComponent.Sheets.getByName(QueryOutputSheetName)
	
	VerwerkPDUCells=VerwerkSheet.getCellRange(1)
	
End Sub