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

'dim sheet as object
'dim cell as object

'sub main
'	loadNewFile
'	sheet=thisComponent.sheets(0)
'	cell=sheet.getCellByPosition(0,0)
'
'	cell.value = simple_function
'end sub

'dim sheet_number as integer
'dim sheet as object
'dim cell as object

'sub main
'	loadNewFile
'	set_sheetnumber
'	sheet= _
'	thisComponent.sheets(sheet_number)
'	cell=sheet.getCellByPosition(0,0)
'	cell.value = sheet_number
'end sub

'sub main
'	loadNewFile
'	changeSheetNames(array("Disk Usage", "Current users"))
'end sub

'Sub main
'	dim command as string
'	command = buildCommand("df|grep -v Filesystem")
'	msgbox(command)
'end sub

sub main 
dummy
end sub

 
sub set_sheetnumber
	dim sheet_number as integer
	sheet_number = 1
end sub

sub loadNewFile (optional filetype as string)
	dim doc as object
	dim desk as object
	dim myFile as string
	dim Dummy()
	
	if isMissing(filetype) then
		filetype = "scalc"
	end if

	myFile = "private:factory/" & filetype

	desk = CreateUnoService("com.sun.star.frame.Desktop")
	doc = desk.loadComponentfromurl(myFile,"_blank",0,Dummy())
end sub

sub writeToCell_1
	dim sheet as object
	dim cell as object
	
	sheet=thisComponent.sheets(1)
	cell=sheet.getCellByPosition(0,1)
	cell.string="Hello World"
end sub

sub writeToCell
	dim sheet as object
	dim cell as object
	
	sheet=thisComponent.sheets.getByName("Sheet1")
	cell=sheet.getCellByPosition(0,1)
	cell.string="Hello World"
end sub

sub simple_maths_1
	dim sheet as object
	dim cell as object
	
	sheet=thisComponent.sheets.getByName("Sheet1")
	
	cell=sheet.getCellByPosition(0,0)
	cell.value=10
	cell=sheet.getCellByPosition(0,1)
	cell.value=10
	cell=sheet.getCellByPosition(0,2)
	cell.formula="=A1+A2"
end sub


sub simple_maths(numbA as double, numbB as double)
	dim sheet as object
	dim cell as object
	
	sheet=thisComponent.sheets.getByName("Sheet1")
	
	cell=sheet.getCellByPosition(0,0)
	cell.value=numbA
	cell=sheet.getCellByPosition(0,1)
	cell.value=numbB
	cell=sheet.getCellByPosition(0,2)
	cell.formula="=A1+A2"
end sub

sub Incorrect_simple_maths
	dim sheet as object
	dim cell as object
	
	sheet=thisComponent.sheets.getByName("Sheet1")
	
	cell=sheet.getCellByPosition(0,0)
	cell.string=10
	cell=sheet.getCellByPosition(0,1)
	cell.string=10
	cell=sheet.getCellByPosition(0,2)
	cell.string="=A1+A2"
end sub

sub simple_maths_array(numbers)
	dim sheet as object
	dim cell as object
	dim r as integer
	dim sum as double
	sheet = thisComponent.sheets.getByName("Sheet1")
	sum = 0
	for r = 0 to ubound(numbers)
		sum = sum + numbers(r)
		cell = sheet.getCellByPosition(0,r)
		cell.value = numbers(r)
	next
	
	cell = sheet.getCellByPosition(0,r+1)
	cell.value = sum
end sub


sub dataFromExistingFile
	dim doc as object
	dim desk as object
	dim url as string
	dim sheet as object
	dim cell as object
	dim contents as double
	dim Dummy()

	desk = CreateUnoService("com.sun.star.frame.Desktop")
 
	url="file://~/test.ods"
	doc=desk.loadComponentfromurl(url,"_blank",0,Dummy())
	sheet = thisComponent.sheets.getByName("Sheet1")
	cell = sheet.getCellByPosition(0,0)
	contents = cell.value
	msgbox(contents)
end sub




sub usingOOoFunctions1(	fType as string, sName as string, _
						c as integer, r as integer, iArray	)
	dim service as object
	dim sheet as object
	dim cell as object
	
	service = createUnoService( "com.sun.star.sheet.FunctionAccess" )
	sheet = thisComponent.sheets.getByName(sName)
	cell = sheet.getCellByPosition(c,r)
	cell.value = service.callFunction( fType, iArray )
end sub

sub usingOOoFunctions(	fType as string, sName as string, _
						c as integer, r as integer, iArray	)
	dim service as object
	dim sheet as object
	dim cell as object
	
	if (fType <> "SQRT" and iArray(0) <> -1 ) then
		service = _
		createUnoService( "com.sun.star.sheet.FunctionAccess" )
		sheet = thisComponent.sheets.getByName(sName)
		cell = sheet.getCellByPosition(c,r)
		cell.value = service.callFunction( fType, iArray )
	end if 
end sub


sub extraOOoFunctions(	fType as string, sName as string, _
						c as integer, r as integer, iArray	)
	dim service as object
	dim sheet as object
	dim cell as object
	dim i as integer
	
	service = createUnoService( "com.sun.star.sheet.FunctionAccess" )
	sheet = thisComponent.sheets.getByName(sName)
	
	for i = 0 to ubound(iArray)
		cell = sheet.getCellByPosition(0,i)
		cell.value = iArray(i)
	next
	
	
	cell = sheet.getCellByPosition(1,0)
	cell.formula = "=A1:A" & i
'	cell.value = service.callFunction( fType, iArray )
end sub

function dummy1 as double
	dim service as object
	service = createUnoService( "com.sun.star.sheet.FunctionAccess" )
	dummy = service.callFunction( "SQRT", array(-1) )
end function

function dummy2 as double
	dim service as object
	on error resume next
	service = createUnoService( "com.sun.star.sheet.FunctionAccess" )
	dummy = service.callFunction( "SQRT", array(-1) )
end function

function dummy as double
	dim service as object
	on error goto errorFound
	service = createUnoService( "com.sun.star.sheet.FunctionAccess" )
	dummy = service.callFunction( "SQRT", array(-1) )
	exit function
	
	errorFound:
	msgbox("Invalid input. Result set to -1")
	dummy=-1
end function

sub simple_sub
	cell.value = 1
end sub

function simple_function as integer
	simple_function = 1
end function

sub changeSheetNames (sheetNames)
	dim sheet as object
	dim i as integer
	
	for i = 0 to ubound(sheetNames)
		sheet = thisComponent.createInstance("com.sun.star.sheet.Spreadsheet")
		thisComponent.Sheets.insertByName(sheetNames(i), Sheet)
	next 
	
	thisComponent.sheets.removebyname("Sheet1")
	thisComponent.sheets.removebyname("Sheet2")
	thisComponent.sheets.removebyname("Sheet3")	
end sub

sub runCommand (command as string)
	dim oSvc as object
	oSvc = createUnoService("com.sun.star.system.SystemShellExecute")
   	oSvc.execute(command, "", 0) 
end sub


const tmpFile as string = "/tmp/myfile.tmp"
const aFile as string = "c:\cygwin\tmp\runme.bsh"

function buildCommand (ipCommand as string) as string
	buildCommand = "rm -f " & tmpFile & ";" _
	& ipCommand & "  | sed s/'\t'/' '/g >" & tmpFile & ";" _
	& "while [ ""$(grep '  ' " & tmpFile & ")"" != """" ];" _
	& "do cat " & tmpFile & " | sed s/'  '/' '/g > " & tmpFile & "1;" _
	& "mv " & tmpFile & "1 " & tmpFile & ";" _
	& "done"
end function


sub theFullWorks
	dim command as string
	
	loadNewFile
	changeSheetNames (array("Disk Space Usage","File Usage"))

	command = buildCommand("df|grep -v Filesystem")
	reportSheet(command,"Disk Space Usage")
	
	command = buildCommand("du /| sort -nr")
	reportSheet(command,"File Usage")
	
	
end sub

sub reportSheet (command as string, sheetName as string)
	Dim iNumber As Integer
	Dim oNumber As Integer
	iNumber = Freefile
	oNumber = Freefile
	Dim iLine As String
	dim i as integer
	dim c as integer
	dim sheet as object
	dim cell as object
	
	Open aFile For output As #oNumber	
	print #oNumber,command
	close #oNumber
	 
	shell("bash -c ""/tmp/runme.bsh""",,,true)
	
	i = 1
	sheet=thisComponent.sheets.getByName(sheetName)
	
	Open "c:/cygwin/tmp/myfile.tmp" For Input As #iNumber
	While not EOF(iNumber)
		Dim cArray
		Line Input #iNumber, iLine
	'	msgbox (1)
		cArray = split(iLine)
		for c=0 to ubound(cArray)
			cell=sheet.getCellByPosition(c,i)
			cell.string=cArray(c)
		next
		i = i + 1
	wend
	Close #iNumber
end sub