REM  *****  BASIC  *****
REM OpenOffice Basic Macro for Calc
OPTION EXPLICIT

Private oDocument As Object
Private oInputSheet As Object
Private oOutputSheet As Object
Private dFirstDate As Date
Private dLastDate As Date
Private iEntries As Integer

Function CheckEntry(oDatumCell As Object, oSoortCell As Object, oAantalCell as Object, _
	oLD As Object, oKD as Object, iLine as Integer) As Boolean
Dim bDatum as Boolean
Dim bSoort as Boolean
Dim bAantal as Boolean
Dim bLD as Boolean
Dim bKD as Boolean
	bDatum = Not (oDatumCell.getType() = com.sun.star.table.CellContentType.EMPTY)
	bSoort = Not (oSoortCell.getType() = com.sun.star.table.CellContentType.EMPTY)
	bAantal = Not (oAantalCell.getType() = com.sun.star.table.CellContentType.EMPTY)
	bLD = Not (oLD.getType() = com.sun.star.table.CellContentType.EMPTY)
	bKD = Not (oKD.getType() = com.sun.star.table.CellContentType.EMPTY)
	If bDatum And bSoort And bAantal And bLD And bKD Then
		If Not IsNumeric(oDatumCell.getFormula) Then
			Msgbox "Datum " + oDatumCell.getFormula + " on line " + iLine + " is not valid"
			Stop
		End If
		If (oAantalCell.getValue < 1) Then
			Msgbox ("Number of containers on line " + iLine + " is wrong")
			Stop
		End If
		If (oLD.getValue < 1) Then
			Msgbox ("Number of LD on line " + iLine + " is wrong")
			Stop
		End If
		If (oKD.getValue < 1) Then
			Msgbox ("Number of KD on line " + iLine + " is wrong")
			Stop
		End If
		GetSheetByName(oSoortCell.getFormula())
	ElseIf bDatum Or bSoort Or bAantal Or bLD Or bKD Then
		Msgbox "Line " +iLine+ " is incomplete"
	End If
	CheckEntry = bDatum And bSoort And bAantal And bLD and bKD
End Function

' This function counts the entries and sees if they are valid
Function CountEntries() as Integer
Dim oDatumCell As Object
Dim oSoortCell As Object
Dim oAantalCell As Object
Dim oLD As Object
Dim oKD As Object
Dim dStart As Date
Dim dEnd As Date
Dim i as Integer
Dim bContinue As Boolean

	oInputSheet.Columns(0).NumberFormat = 37  ' date
	oInputSheet.Columns(2).NumberFormat = 100 ' text
	oInputSheet.Columns(2).NumberFormat = 3   ' integer
	oInputSheet.getCellByPosition(0,0).setFormula("Datum")
	oInputSheet.getCellByPosition(1,0).setFormula("Soort")
	oInputSheet.getCellByPosition(2,0).setFormula("Containers")
	oInputSheet.getCellByPosition(3,0).setFormula("LD")
	oInputSheet.getCellByPosition(4,0).setFormula("KD")
	bContinue = True
	i = 1
	While bContinue
		oDatumCell = oInputSheet.getCellByPosition(0,i)
		oSoortCell = oInputSheet.getCellByPosition(1,i)
		oAantalCell = oInputSheet.getCellByPosition(2,i)
		oLD = oInputSheet.getCellByPosition(3,i)
		oKD = oInputSheet.getCellByPosition(4,i)
		bContinue = CheckEntry(oDatumCell, oSoortCell, oAantalCell, oLD, oKD, i+1)
		dStart = oDatumCell.getValue
		dEnd = dStart + oLD.getValue + oKD.getValue
		If i = 1 And BContinue Then
			dFirstDate = dStart
			dLastDate = dEnd
		ElseIf bContinue Then
			If dFirstDate > dStart Then
				dFirstDate = dStart
			End If
			If dLastDate < dEnd Then
				dLastDate = dEnd
			End If
		End If
		i = i + 1
	Wend
	CountEntries = i - 1
End Function

Sub Calculate(oSheet as Object)
Dim oDatumCell As Object
Dim oSoortCell As Object
Dim oAantalCell As Object
Dim i as Date
Dim j as Date
Dim jEnd as Date
Dim offset as Date
Dim dEnd as Date
Dim oOutputCell as Object
Dim aantal as Integer
Dim temp As Integer

	oOutputSheet.clearContents(com.sun.star.sheet.CellFlags.VALUE _
		Or com.sun.star.sheet.CellFlags.DATETIME Or com.sun.star.sheet.CellFlags.STRING)
	oOutputSheet.Columns(0).NumberFormat = 37
	oOutputSheet.Columns(1).NumberFormat = 3
	oOutputSheet.getCellByPosition(0,0).setFormula("Datum")
	oOutputSheet.getCellByPosition(1,0).setFormula("Containers")

	offset = dFirstDate - 1
	Dim containers(dLastDate-DFirstDate+2) As Integer
	i = dFirstDate
	While i < dLastDate+1
		oOutputSheet.getCellByPosition(0,i-offset).setValue(i)
		containers(i-offset) = 0
		i = i+1
	Wend
	
	For i = 1 To iEntries-1
		oDatumCell = oInputSheet.getCellByPosition(0,i)
		oSoortCell = oInputSheet.getCellByPosition(1,i)
		aantal = oInputSheet.getCellByPosition(2,i).getValue
		jEnd = oDatumCell.getValue + oInputSheet.getCellByPosition(3,i).getValue _
			+ oInputSheet.getCellByPosition(4,i).getValue
		For j = oDatumCell.getValue To jEnd
			temp = containers(j-offset)
			containers(j-offset) = temp + aantal
		Next j
	Next i
	i = dFirstDate
	While i-1 < dLastDate
		oOutputSheet.getCellByPosition(1,i-offset).setValue(containers(i-offset))
		i = i+1
	Wend
End Sub

Function GetSheetByName(sName as String) as Object
	Dim bFound As Boolean
	Dim oSheets As Object
	Dim oSheet As Object
	oSheets = oDocument.Sheets.createEnumeration
	bFound = FALSE
	While (oSheets.hasMoreElements and not bFound)
		oSheet = oSheets.nextElement
		If (oSheet.Name = sName) Then
			GetSheetByName = oSheet
			bFound = TRUE
		End If
	Wend
	If (not bFound) Then
		Msgbox "Sheet " + sName + " does not exist"
		Stop
	End If
End Function

Sub Main
' oDocument = StarDesktop.ActiveComponent ##599##
 oDocument = ThisComponent

 REM Increase speed by using the
 REM com::sun::star::document::XActionLockable Interface
 REM and make all changes to the document between calls
 REM to the addActionLock and removeActionLock methods
 'oDocument.addActionLock
 oInputSheet = GetSheetByName("invoer")
 oOutputSheet = GetSheetByName("uitvoer")
 iEntries = CountEntries()
 Calculate()
 'oDocument.removeActionLock

End Sub