[Solved] Scripting a structured report for a database

[macuserfr]

Hello all,

First of all, my apologies if something isn't in the rules of this forum. I'm a newbie here and am not used to the local policy.

Well, there is my problem:

I have done an indexing database that you can find here. Now I have to build a report from this database, under this precise format, as a plain text file:

Code: Select all

Academic Life
    [type doc]
        [title] - [pgstart]-[pgend]
        (...)
    (...)

Authors
    [family name], [name] - [pgstart1]-[pgend1], [pgstart2]-[pgend2], (...)
    (...)

Keywords FR
    [french label] - [pgstart1]-[pgend1], [pgstart2]-[pgend2], (...)
    (...)

Keywords EN
    [english label] - [pgstart1]-[pgend1], [pgstart2]-[pgend2], (...)
    (...)

Well, theoretically this may seem simple. But when you are new to open office, this is tricky. I've tried several approaches:
1) Sun Report Builder: This format data with lots of tables and I found no way to put conditions, like for authors that appears in many pages to group the pages.
2) Repport assistant: Same thing. I can have a not too bad system by exporting in pdf, then copying the text and doing some final adjustments.
3) Using Writer and importing data from database: This seems to me to be the better approach of the problem. But I'm stuck with conditional variables. I would need a variable that takes the value of a field of my database. I've searched around and found no way to do this. Nobody seems as tricky as me, wanting to get value from a database field to work with.
4) Using mailing assistant: Could not get the text format I wanted
5) Scripting it with python, basic, macro, whatever: This is what I'm looking for. I have programming skills, but never did a macro or other Open office script. I already did shell scripts, C, Java, Assembler...

So, if somebody skilled with this could give me a main direction on this, that would be very kind. I'm not asking for final code, but for directions for learning and be able to do things by myself. What I would like:

- Advices about the way to do it, ex: Python is a good approach to build a report? Something else would be better? If you already done similar projects, your experience interest me a lot.
- Links to related stuff: I did search, but keywords are still keywords, I've surely missed some important infos.

So a big thanks in advance for all the people that read it until here and have an interest in this project.

[ms777]

Hi,

what you target at is certainly possible by doing OO Basic macro programming. Good starters are www.pitonyak.org/database/AndrewBase.odt and http://www.pitonyak.org/AndrewMacro.odt

Good luck,

ms777

[macuserfr]

Thanks a lot for this indication and those links!
Will study this carefully.
Have a nice weekend

[macuserfr]

Eureka! I found the start of an answer for my question. I recorded a macro that imports fields from my database:

Code: Select all

sub test
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(6) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Type"
args1(0).Value = 19
args1(1).Name = "DBName"
args1(1).Value = "Indexation"
args1(2).Name = "Command"
args1(2).Value = "Vie_acad"
args1(3).Name = "ColumnName"
args1(3).Value = "libelle"
args1(4).Name = "CommandType"
args1(4).Value = 0
args1(5).Name = "Content"
args1(5).Value = ""
args1(6).Name = "Format"
args1(6).Value = 5000

dispatcher.executeDispatch(document, ".uno:InsertDBField", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertPara", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Text"
args3(0).Value = CHR$(9)

dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(6) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Type"
args4(0).Value = 19
args4(1).Name = "DBName"
args4(1).Value = "Indexation"
args4(2).Name = "Command"
args4(2).Value = "Vie_acad"
args4(3).Name = "ColumnName"
args4(3).Value = "titre"
args4(4).Name = "CommandType"
args4(4).Value = 0
args4(5).Name = "Content"
args4(5).Value = ""
args4(6).Name = "Format"
args4(6).Value = 5000

dispatcher.executeDispatch(document, ".uno:InsertDBField", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Text"
args5(0).Value = ", "

dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(6) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Type"
args6(0).Value = 19
args6(1).Name = "DBName"
args6(1).Value = "Indexation"
args6(2).Name = "Command"
args6(2).Value = "Vie_acad"
args6(3).Name = "ColumnName"
args6(3).Value = "pgdebut"
args6(4).Name = "CommandType"
args6(4).Value = 0
args6(5).Name = "Content"
args6(5).Value = ""
args6(6).Name = "Format"
args6(6).Value = 5000

dispatcher.executeDispatch(document, ".uno:InsertDBField", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Text"
args7(0).Value = " - "

dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(6) as new com.sun.star.beans.PropertyValue
args8(0).Name = "Type"
args8(0).Value = 19
args8(1).Name = "DBName"
args8(1).Value = "Indexation"
args8(2).Name = "Command"
args8(2).Value = "Vie_acad"
args8(3).Name = "ColumnName"
args8(3).Value = "pgfin"
args8(4).Name = "CommandType"
args8(4).Value = 0
args8(5).Name = "Content"
args8(5).Value = ""
args8(6).Name = "Format"
args8(6).Value = 5000

dispatcher.executeDispatch(document, ".uno:InsertDBField", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(6) as new com.sun.star.beans.PropertyValue
args9(0).Name = "Type"
args9(0).Value = 24
args9(1).Name = "DBName"
args9(1).Value = "Indexation"
args9(2).Name = "Command"
args9(2).Value = "Vie_acad"
args9(3).Name = "ColumnName"
args9(3).Value = "TRUE"
args9(4).Name = "CommandType"
args9(4).Value = 0
args9(5).Name = "Content"
args9(5).Value = ""
args9(6).Name = "Format"
args9(6).Value = 0

dispatcher.executeDispatch(document, ".uno:InsertDBField", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(1) as new com.sun.star.beans.PropertyValue
args10(0).Name = "Count"
args10(0).Value = 1
args10(1).Name = "Select"
args10(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args10())

rem ----------------------------------------------------------------------
dim args11(1) as new com.sun.star.beans.PropertyValue
args11(0).Name = "Count"
args11(0).Value = 1
args11(1).Name = "Select"
args11(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args11())

rem ----------------------------------------------------------------------
dim args12(1) as new com.sun.star.beans.PropertyValue
args12(0).Name = "Count"
args12(0).Value = 1
args12(1).Name = "Select"
args12(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args12())

rem ----------------------------------------------------------------------
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "ControlCodes"
args13(0).Value = true

dispatcher.executeDispatch(document, ".uno:ControlCodes", "", 0, args13())

rem ----------------------------------------------------------------------
dim args14(1) as new com.sun.star.beans.PropertyValue
args14(0).Name = "Count"
args14(0).Value = 1
args14(1).Name = "Select"
args14(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args14())

rem ----------------------------------------------------------------------
dim args15(1) as new com.sun.star.beans.PropertyValue
args15(0).Name = "Count"
args15(0).Value = 1
args15(1).Name = "Select"
args15(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args15())

rem ----------------------------------------------------------------------
dim args16(1) as new com.sun.star.beans.PropertyValue
args16(0).Name = "Count"
args16(0).Value = 1
args16(1).Name = "Select"
args16(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args16())

rem ----------------------------------------------------------------------
dim args17(1) as new com.sun.star.beans.PropertyValue
args17(0).Name = "Count"
args17(0).Value = 1
args17(1).Name = "Select"
args17(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args17())

rem ----------------------------------------------------------------------
dim args18(1) as new com.sun.star.beans.PropertyValue
args18(0).Name = "Count"
args18(0).Value = 1
args18(1).Name = "Select"
args18(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args18())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertPara", "", 0, Array())

rem ----------------------------------------------------------------------
dim args20(0) as new com.sun.star.beans.PropertyValue
args20(0).Name = "Text"
args20(0).Value = CHR$(9)

dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, args20())

rem ----------------------------------------------------------------------
dim args21(6) as new com.sun.star.beans.PropertyValue
args21(0).Name = "Type"
args21(0).Value = 19
args21(1).Name = "DBName"
args21(1).Value = "Indexation"
args21(2).Name = "Command"
args21(2).Value = "Vie_acad"
args21(3).Name = "ColumnName"
args21(3).Value = "titre"
args21(4).Name = "CommandType"
args21(4).Value = 0
args21(5).Name = "Content"
args21(5).Value = ""
args21(6).Name = "Format"
args21(6).Value = 5000

dispatcher.executeDispatch(document, ".uno:InsertDBField", "", 0, args21())

rem ----------------------------------------------------------------------
dim args22(0) as new com.sun.star.beans.PropertyValue
args22(0).Name = "Text"
args22(0).Value = ", "

dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, args22())

rem ----------------------------------------------------------------------
dim args23(6) as new com.sun.star.beans.PropertyValue
args23(0).Name = "Type"
args23(0).Value = 19
args23(1).Name = "DBName"
args23(1).Value = "Indexation"
args23(2).Name = "Command"
args23(2).Value = "Vie_acad"
args23(3).Name = "ColumnName"
args23(3).Value = "pgdebut"
args23(4).Name = "CommandType"
args23(4).Value = 0
args23(5).Name = "Content"
args23(5).Value = ""
args23(6).Name = "Format"
args23(6).Value = 5000

dispatcher.executeDispatch(document, ".uno:InsertDBField", "", 0, args23())

rem ----------------------------------------------------------------------
dim args24(0) as new com.sun.star.beans.PropertyValue
args24(0).Name = "Text"
args24(0).Value = " - "

dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, args24())

rem ----------------------------------------------------------------------
dim args25(6) as new com.sun.star.beans.PropertyValue
args25(0).Name = "Type"
args25(0).Value = 19
args25(1).Name = "DBName"
args25(1).Value = "Indexation"
args25(2).Name = "Command"
args25(2).Value = "Vie_acad"
args25(3).Name = "ColumnName"
args25(3).Value = "pgfin"
args25(4).Name = "CommandType"
args25(4).Value = 0
args25(5).Name = "Content"
args25(5).Value = ""
args25(6).Name = "Format"
args25(6).Value = 5000

dispatcher.executeDispatch(document, ".uno:InsertDBField", "", 0, args25())

rem ----------------------------------------------------------------------
dim args26(6) as new com.sun.star.beans.PropertyValue
args26(0).Name = "Type"
args26(0).Value = 24
args26(1).Name = "DBName"
args26(1).Value = "Indexation"
args26(2).Name = "Command"
args26(2).Value = "Vie_acad"
args26(3).Name = "ColumnName"
args26(3).Value = "TRUE"
args26(4).Name = "CommandType"
args26(4).Value = 0
args26(5).Name = "Content"
args26(5).Value = ""
args26(6).Name = "Format"
args26(6).Value = 0

dispatcher.executeDispatch(document, ".uno:InsertDBField", "", 0, args26())


end sub

This takes the two first records for a field.

Now I have to learn how to put conditional instructions on it. Like: "if field libelle not equal previous field libelle then ..."

My problem now is how to get the database information in variables to do the conditions. I've tried:

Code: Select all

if args1(5).Value = label(5).Value then
	dispatcher.executeDispatch(document, ".uno:InsertDBField", "", 0, args1())
	dispatcher.executeDispatch(document, ".uno:InsertPara", "", 0, Array())
endif

But args1(5).Value doesn't contain the value of the database field. I'll have to connect to the database and ask for values "by hand", what I don't know by now. I'm reading section 8 of AndrewBase document that talks about it.

Sorry if this seams a mess, by so is my head. I should begin macro and basic learning by an easier project... But I have no choice.

[Hagar Delest]

Somebody have a link to a documentation of Basic instructions where I can find the syntax of if else while etc?

See here: Reference manual for OOo Basic.

Note that for maintenance, it's better to use the Basic language instead of the DispatchHelper (recording macros). It'll be easier afterwards to modify the macro because dispatcher commands are not user-friendly.

[macuserfr]

Thanks for the tip!
This recorded script is the better I could get by now. I've already saw that it creates several times the same variables, so I can optimise it. But it's really hard for a beginner to get Basic commands. C/C++ have large site documenting, Java has Javadoc... I'll try to install the sdk from the topic you pointed, but I feel so alone face to this project! Seems that nobody do program using OO basic.

[Hagar Delest]

As stated above, a good starting point is the Andrew Pitonyak's document. Then, search the web for code snippets of the features you want and adapt them to your need. That's what I do (with the help of the SDK) and it works fine. Very often, the task you want to perform has already been handled somewhere, the point is to find where!

Good luck!

[macuserfr]

Thanks guys for the help!
I finally got what I wanted with the macro above:

Code: Select all

rem ----------------------------------------------------------------------
rem define variables
rem ----------------------------------------------------------------------
Dim document   as object	'Document object
Dim dispatcher as object	'dispatcher object
Dim oBaseContext  			'Global database context. 
Dim oDataSource   			'Data source for the specified database.
Dim oHandler      			'Interaction handler in case a password is required. 
Dim oCon          			'Connection to a database.
Dim oStatement   			'A created statement that can execute SQL.
Dim oResult as Object
Dim boldPropriety(0) as new com.sun.star.beans.PropertyValue

rem ----------------------------------------------------------------------
rem Generic Index maker procedure
rem Parameters:
rem tableName	- Name of the table which contains relevant data into the database
rem indexCount	- Number of columns to be considered as index (like first name and last name).
rem				  Nowadays it can be 1 or 2 maximum.
rem title		- Title of the index
rem ----------------------------------------------------------------------
Sub IndexMaker(tableName as String, indexCount as Integer, title as String)

rem ----------------------------------------------------------------------
rem define variables
rem ----------------------------------------------------------------------
Dim textToWrite(0) as new com.sun.star.beans.PropertyValue 'Text entry
Dim indexed$					'The object to be indexed
Dim indexed2$					'The object to be indexed

rem ----------------------------------------------------------------------
rem Initialize variables
rem ----------------------------------------------------------------------
textToWrite(0).Name = "Text"
indexed= ""
indexed2= ""

rem ----------------------------------------------------------------------
rem Write the title
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertPara", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:InsertPara", "", 0, Array())
textToWrite(0).Value = title
boldPropriety(0).Value = true
dispatcher.executeDispatch(document, ".uno:Bold", "", 0, boldPropriety())
dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, textToWrite())
boldPropriety(0).Value = false
dispatcher.executeDispatch(document, ".uno:Bold", "", 0, boldPropriety())

rem ----------------------------------------------------------------------
rem Get the database's table
rem ----------------------------------------------------------------------
oCon = oDataSource.ConnectWithCompletion(oHandler)
oStatement = oCon.CreateStatement()
oResult = oStatement.executeQuery("SELECT * FROM "+tableName)

Do While oResult.next()
	If indexed <> oResult.getString(1) OR (indexCount = 2 AND indexed2 <> oResult.getString(2)) Then
rem ----------------------------------------------------------------------
rem Insert a paragraph
rem ----------------------------------------------------------------------
		dispatcher.executeDispatch(document, ".uno:InsertPara", "", 0, Array())
		
		If Left(indexed,1) <> Left(oResult.getString(1),1) Then
rem ----------------------------------------------------------------------
rem Insert letter label
rem ----------------------------------------------------------------------
			textToWrite(0).Value = Left(oResult.getString(1),1)
			boldPropriety(0).Value = true
			dispatcher.executeDispatch(document, ".uno:Bold", "", 0, boldPropriety())
			dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, textToWrite())
			boldPropriety(0).Value = false
			dispatcher.executeDispatch(document, ".uno:Bold", "", 0, boldPropriety())
			dispatcher.executeDispatch(document, ".uno:InsertPara", "", 0, Array())			
		EndIf
		indexed = oResult.getString(1)
		If indexCount = 2 Then
			indexed2 = oResult.getString(2)
		EndIf
		
rem ----------------------------------------------------------------------
rem Insert name entry
rem ----------------------------------------------------------------------
		textToWrite(0).Value = indexed	
		If indexCount = 2 Then
			textToWrite(0).Value = textToWrite(0).Value + " " + indexed2
		EndIf
		dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, textToWrite())
	EndIf

rem ----------------------------------------------------------------------
rem Insert a Page number item
rem ----------------------------------------------------------------------
	textToWrite(0).Value = ", " + oResult.getLong(indexCount+1) + "-" + oResult.getLong(indexCount+2)
	dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, textToWrite())
Loop

oCon.close() 

rem ----------------------------------------------------------------------
rem End of the Indexmaker procedure
rem ----------------------------------------------------------------------
End Sub

Sub Auteurs
IndexMaker("Index_auteurs", 2, "INDEX AUTEURS")
End Sub

Sub MotsCle
IndexMaker("Index_mot_cle", 1, "INDEX MOTS MATIÈRES")
End Sub

Sub Keywords
IndexMaker("Index_keywords", 1, "INDEX KEYWORDS")
End Sub

rem ----------------------------------------------------------------------
rem Start of the Vie académique macro
rem ----------------------------------------------------------------------
Sub VieAcad

rem ----------------------------------------------------------------------
rem define variables
rem ----------------------------------------------------------------------
Dim textToWrite(0) as new com.sun.star.beans.PropertyValue 'Text entry
Dim libelle$

rem ----------------------------------------------------------------------
rem Initialize variables
rem ----------------------------------------------------------------------
textToWrite(0).Name = "Text"
libelle = ""
	
rem ----------------------------------------------------------------------
rem Get the Vie Academique's table
rem ----------------------------------------------------------------------
oCon = oDataSource.ConnectWithCompletion(oHandler)
oStatement = oCon.CreateStatement()
oResult = oStatement.executeQuery("SELECT * FROM Vie_acad")

dispatcher.executeDispatch(document, ".uno:InsertPara", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:InsertPara", "", 0, Array())
textToWrite(0).Value = "INDEX VIE ACADÉMIQUE"
boldPropriety(0).Value = true
dispatcher.executeDispatch(document, ".uno:Bold", "", 0, boldPropriety())
dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, textToWrite())
boldPropriety(0).Value = false
dispatcher.executeDispatch(document, ".uno:Bold", "", 0, boldPropriety())

Do While oResult.next()
rem ----------------------------------------------------------------------
rem Insert a paragraph
rem ----------------------------------------------------------------------
	dispatcher.executeDispatch(document, ".uno:InsertPara", "", 0, Array())
	
	If libelle <> oResult.getString(1) Then
		libelle = oResult.getString(1)
rem ----------------------------------------------------------------------
rem Insert label field
rem ----------------------------------------------------------------------
		textToWrite(0).Value = libelle
		boldPropriety(0).Value = true
		dispatcher.executeDispatch(document, ".uno:Bold", "", 0, boldPropriety())
		dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, textToWrite())
		boldPropriety(0).Value = false
		dispatcher.executeDispatch(document, ".uno:Bold", "", 0, boldPropriety())
	
rem ----------------------------------------------------------------------
rem Insert a paragraph
rem ----------------------------------------------------------------------
		dispatcher.executeDispatch(document, ".uno:InsertPara", "", 0, Array())
	EndIf

rem ----------------------------------------------------------------------
rem Insert a Title line
rem ----------------------------------------------------------------------
	textToWrite(0).Value = "—" + oResult.getString(2) + ", " + oResult.getLong(3) + "-" + oResult.getLong(4)
	dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, textToWrite())
Loop

rem ----------------------------------------------------------------------
rem End of the VieAcad macro
rem ----------------------------------------------------------------------
End Sub

Sub Index

rem ----------------------------------------------------------------------
rem get access to the document
rem ----------------------------------------------------------------------
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
rem get access to the database
rem ----------------------------------------------------------------------
oBaseContext = CreateUnoService("com.sun.star.sdb.DatabaseContext") 
oDataSource = oBaseContext.getByName("Indexation") 
oHandler = createUnoService("com.sun.star.sdb.InteractionHandler")

boldPropriety(0).Name = "Bold"

rem ----------------------------------------------------------------------
rem Execute Index procedures
rem ----------------------------------------------------------------------
Auteurs
Keywords
MotsCle
VieAcad

oCon.close()
End Sub

I know, it uses executeDispatcher() that isn't the cleaner fashion to do it. If you have improvement suggestions, I accept them happily I had to do it quickly and it's my first basic macro. There is another point that could be improved, I have to reconnect to the database each time I do a query, otherwise I raise a DisposedException on my oCon. If you know how to keep the connection alive, I would love to know how to do it.