'Code examples for LXF82 - OOO Basic Tutotial III

Dim RowSet
Dim dlgAccounts as object
dim lstCustomers as object

Sub main 
	connectToDatabase ("Accounts", "bainm", "kawasaki")
'	sql1
'	loadNewFile
'	createCustomerReport
	showDialogBox
End Sub

Sub connectToDatabase(database as string, username as string, password as string)
    RowSet = createUnoService("com.sun.star.sdb.RowSet")
    RowSet.DataSourceName = database
    RowSet.User = username
    RowSet.Password = password
End Sub

Sub updateRowSet(sql as string)
    RowSet.Command = sql
    RowSet.execute()
End Sub

Sub sql1
   updateRowSet("SELECT count(*) c FROM item")
   RowSet.First()
   MsgBox "There are " + rowSet.getString(1) + " items"
End Sub

'The wrong way

'sub main
'	sql_notgood
'end sub

Sub sql1_notgood
	Dim RowSet
    RowSet = createUnoService("com.sun.star.sdb.RowSet")
    RowSet.DataSourceName = "Accounts"
    RowSet.User="bainm"
    RowSet.Password = "kawasaki"
    RowSet.Command = "SELECT count(*) c FROM item"
    RowSet.execute()
    RowSet.First()
    MsgBox "There are " + rowSet.getString(1) + " items"
End Sub


Sub sql2
	updateRowSet("SELECT id, surname, firstname  FROM customer")
	while RowSet.Next() 
        MsgBox "Customer No. " + rowSet.getString(1) + " " + rowSet.getString(2) + " " + rowSet.getString(3)
    wend
End Sub

Sub loadNewFile(optional filetype as string)
	dim doc as object
	dim desk as object
	dim url as string
	dim args()
	if isMissing(filetype) then
		filetype="swriter"
	end if 
	desk = CreateUnoService("com.sun.star.frame.Desktop")
	url = "private:factory/" + filetype
	doc = desk.loadComponentFromUrl (url,"_blank", 0, args())
End Sub



Sub add_paragraph (myText as string)
	dim doc as object
	dim cursor as object
	doc=thisComponent
	cursor=doc.text.createTextCursor
	cursor.gotoEnd(False)
	doc.text.insertControlCharacter(cursor,com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, False)
	doc.text.insertControlCharacter(cursor,com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, False)
	cursor.string = myText
End Sub

Sub createCustomerReport
	updateRowSet("SELECT id, surname, firstname  FROM customer")
	while RowSet.Next() 
    	add_paragraph("Customer No. " + _
    	rowSet.getString(1) + " " + rowSet.getString(2) + " " + rowSet.getString(3))
   	wend
End Sub 

Sub showDialogBox
	basicLibraries.loadLibrary("Tools")
	dlgAccounts = loadDialog("Standard","dlgAccounts")
	loadlstCustomers
	dlgAccounts.execute()
End Sub

Sub loadlstCustomers
	Dim i as integer
	lstCustomers = dlgAccounts.getControl("lstCustomers")
	updateRowSet("SELECT surname, firstname  FROM customer")
	i=0
	while RowSet.Next() 
		lstCustomers.AddItem(rowSet.getString(2) + " " + rowSet.getString(1), i)
		let i = i+1
	 wend
End Sub

Sub cmdItemReport
	updateRowSet(itemListSql)
	loadNewFile("swriter")
	add_paragraph("Items for " + lstCustomers.selectedItem)
	while RowSet.Next() 
    	add_paragraph(rowSet.getString(1) + " £" + rowSet.getString(2))
   	wend
End Sub

Sub cmdSpreadSheet
	Dim sheet as object
	Dim cell as object
	Dim sheetName as string
	Dim i as integer
	
	loadNewFile("scalc")
	sheetName = "Item List"
	changeSheetNames(array(sheetName))
	sheet=thisComponent.sheets.getByName(sheetName)
	cell=sheet.getCellByPosition(0,0)
	cell.string="Name:"
	cell=sheet.getCellByPosition(1,0)
	cell.string=lstCustomers.selectedItem
	
	updateRowSet(itemListSql)
	i=2
	while RowSet.Next() 
		cell=sheet.getCellByPosition(3,i)
		cell.string = rowSet.getString(1)
		cell=sheet.getCellByPosition(4,i)
    	cell.value = rowSet.getString(2)
    	i=i+1
   	wend
   	
end Sub

Function itemListSql as string
	itemListSql = " select title,value from customer, item " + _
	" where customer.id = item.customer_id " + _
	" and concat(customer.firstname,concat(' ',customer.surname))  = '" + lstCustomers.selectedItem +"'"
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