Author: Dmitri Popov
If you spend most of your time in OpenOffice.org, you might want to be able to manage to-do lists and tasks without leaving the comfort of the office suite. Since Writer doesn’t have such a feature, you can create your own no-frills task tool and at the same time sharpen your OOoBasic skills.
In a previous article, we covered how to build a handy basket tool. You can use the same techniques when creating a simple task manager. The one we’ll build offers only three basic commands: add a task, view all tasks, and purge tasks.
This tool is part of Google’s Writer’s Tools extension, released under GPL. If you don’t feel like building a tasks manager from scratch, grab the extension and start experimenting.
All the tasks are stored in a Base database table with three fields: ID (type INTEGER), Task (type VARCHAR), and Date (type DATE). The ID field acts as a unique identifier for each task, the Task field stores the task’s description, and the Date field is for the task’s due date.
Before you start working on the task utility, you have to create a Base database (name it, for example, TasksDB) with a “tasks” table that has the described fields in it. You must also register the created database with OpenOffice.org. To do this, launch OpenOffice.org and choose Tools -> Options. Select OpenOffice.org Base -> Databases and press the New button. Select the TasksDB database and give the new connection the name “TasksDB.”
Now you are ready to start coding the New Task macro that allows you to add tasks. First, you have to create a new dialog (name it AddTaskDialog) containing two text fields: TextField1, where you enter the task description, and TextField2, for the due date. You should also add two buttons, which you will use to submit the entered data and to cancel the action.
The first thing the New Task macro has to do is to connect to the TasksDB database:
DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("WriterDB")
ConnectToDB=DataSource.GetConnection ("","")
When the connection is established, the macro initiates and displays the Add New task dialog:
exitOK=com.sun.star.ui.dialogs.ExecutableDialogResults.OK
DialogLibraries.LoadLibrary("Tasks")
Library=DialogLibraries.GetByName("Tasks")
TheDialog=Library.GetByName("AddTaskDialog")
Dialog=CreateUnoDialog(TheDialog)
It then acquires the data entered in TextField1 and TextField2:
If Dialog.Execute=exitOK Then
DialogField1=Dialog.getControl("TextField1")
Task=DialogField1.Text
DialogField2=Dialog.GetControl("TextField2")
DueDate=DialogField2.Text
When the macro has the required data, it inserts it into the appropriate fields in the tasks table in the TasksDB database using the INSERT INTO SQL command:
SQLQuery="INSERT INTO ""tasks"" " + "(""Task"", ""Date"") VALUES "_
+ "('" + Task + "','" + DueDate + "')"
The final macro should look something like this:
Sub NewTask()
Dim Dialog As Object, Library As Object, TheDialog As Object
ThisDoc=ThisComponent
SelectedSnippet=ThisDoc.getCurrentController().getSelection().getByIndex(0).getString()
DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("TasksDB")
ConnectToDB=DataSource.GetConnection ("","")
exitOK=com.sun.star.ui.dialogs.ExecutableDialogResults.OK
DialogLibraries.LoadLibrary("Tasks")
Library=DialogLibraries.GetByName("Tasks")
TheDialog=Library.GetByName("AddTaskDialog")
Dialog=CreateUnoDialog(TheDialog)
DialogField1=Dialog.getControl("TextField1").setText(SelectedSnippet)
If Dialog.Execute=exitOK Then
DialogField1=Dialog.getControl("TextField1")
Task=DialogField1.Text
DialogField2=Dialog.GetControl("TextField2")
DueDate=DialogField2.Text
SQLQuery="INSERT INTO ""tasks"" " + "(""Task"", ""Date"") VALUES "_
+ "('" + Task + "','" + DueDate + "')"
SQLStatement=ConnectToDB.createStatement
Result=SQLStatement.executeQuery (SQLQuery)
ConnectToDB.close
ConnectToDB.dispose()
MsgBox ("The task has been added.", , "All done!")
Dialog.Dispose
End If
End Sub
The astute reader may notice three extra lines in the macro:
ThisDoc=ThisComponent SelectedSnippet=ThisDoc.getCurrentController().getSelection().getByIndex(0).getString()
and
DialogField1=Dialog.getControl("TextField1").setText(SelectedSnippet)
They add a small but convenient feature: when you select a text fragment in your current document, the macro automatically inserts it into the TextField1 field of the Add Task dialog. This allows you to quickly create a new task from a text selection.
The Show Tasks macro also uses an SQL query to fetch tasks from the database. However, the query works in a slightly different way. It uses the SELECT command to pull the task data from the tasks table using the following query:
SQLQuery= "SELECT ""Task"", ""Date"" FROM ""tasks"""
This returns a list of tasks in the order they were entered. If you would like to sort them by the due dates, you might want to use the ORDER BY command in the query:
SQLQuery= "SELECT ""Task"", ""Date"" FROM ""tasks"" ORDER BY ""Date"" ASC"
The constructed query is then executed using the same code as the New Task macro:
SQLStatement=ConnectToDB.createStatement RowSetObj=SQLStatement.executeQuery (SQLQuery)
But to display the tasks, the macro uses the While…Wend loop that reads data in the Task and Date fields record by record:
While RowSetObj.Next TaskList=TaskList & " " & RowSetObj.getString(1) & ": " & RowSetObj.getString(2) & Chr(13) Wend
It then uses a simple message box to display a list of tasks:
MsgBox (TaskList, , "Task list")
Finally, you need a macro that deletes tasks from the database. The Purge Tasks macro is similar to the New Task macro in almost every respect. The only difference is the SQL query, which uses the DELETE command to remove all the tasks from the table:
SQLQuery="DELETE FROM ""tasks"""
That’s all there is to it. Your no-frills task manager is ready to go, and you can use it to manage your tasks. Of course, since this is a rather bare-bones tool, there is a lot of room for improvement here. For example, you might want to configure OpenOffice.org to display your tasks every time you launch the application. To do this, choose Tools -> Macros -> Organize Macros -> OpenOffice.org Basic. Press the Assign button, and switch to the Events tab. Select the Start Application event, press the macro button, and select the ShowTasks macro. Press OK, choose OpenOffice.org from Save in drop-down list, and press OK again. From now on, you will see the task list every time you launch OpenOffice.org. Other improvements may include the ability to purge tasks for a specific date, selectively delete tasks, better way to display the task list, and so on.
Category:
- Office Software
