Friday 18 May 2012

Generic RTF Documents - Part 1. Problem definition


Idea / initial approach inspired from a blog by Håvard Kristiansen – blog link

Usual disclaimers apply – use at your own peril, not supported, yada yada.

Note: Over the duration of this set of blogs, the code and screen content will change due to developing in an incremental manner, bug-fixing etc. See the final blog entry for the final solution

My Environment

Oracle 11gXE running under Linux
Oracle Glassfish (Opensource)
Oracle Apex Listener
Oracle Apex 4.1.1

Problem Definition:

I need to be able to create letters/reports from a combination of both the data held within the database and static text. Some of the data that needs to be reported on is just a single attribute (e.g. date, amount), or the data is a collection, e.g. payment records.
The text of the letter / report needs to be customisable by the user (a standard set delivered, but they can copy to create their own version), formatting options / colours need to be allowed.
Templates need to be held within the database, as users may not have the templates.
Users may require additional / less fields on their version of the report than that supplied in the “standard” template.
For repeating data (Collection), the user may/may not want headings, they can change the heading text, sort order, justification, hide/show the column, set the width.
High Level Approach

Create a “template” within a word processor with “fields” inserted for substitution variables. The document should be saved as RTF and uploaded into the database. By using RTF, formatting can be preserved, and as we are just replacing the substitution variables, any formatting (colour, font etc) would therefore be retained.
On loading the RTF document, we need to extract a list of each of these substitution variables, so that we can then articulate what they are at a later point. The RTF needs to be associated with a report type (so that we know what reports / letters to present to the user when they ask for a report – will explain further, but for now, just accept that we need to allocate it a type).
The substitution variable could represent one of three states (you could extend this if needed)
  1. A “Field” variable, part of a bigger piece of sql which is associated with the document type, e.g. a order letter, where there is a set of information which is already known, e.g. Order number, Order date (This is why we associate a type, again, more will make sense later)
  2. A “Single” variable – populated at run time via a piece of dynamic sql which will be associated with that field, e.g sysdate.
  3. A “Table” variable – a set of data that will be built and populated at run-time by some dynamic sql which will be associated with that field.
Once a “variable” has been defined as a TABLE, dynamically execute the SQL to extract the column definitions and store this data so that the format can be changed.
Data Model
Note: I will initially get the basics working, before extending to allow the user to copy. (subject to change as I progress through this journey)

Let Battle Commence ....
First we need to be able to recognise our "fields". In the original blog, Håvard did this by using the substitution delimiters '§¤' and '¤§' (Start and end) - These are good enough for me, and highly unlikely to be text that the end-user would use. 
Now we know how to "recognise" our fields, we can start to expand on the solution. I'm fairly new to Oracle Apex (and my technical Oracle skills are more than a decade old, so this may not be the most efficient / best way to do things, but hey-ho here we go J).

First load the DDL (will be added once I get to the end of the blog). Now log into Apex and start to create our application.

Page 1 – This page will maintain the “standard letter” table, as we need to associate our RTF document against one. I will not describe how to create this page, as it should be evident.

Page (2) – This page will be used to upload the templates. Note: As the RTF document needs to be read into a CLOB rather than BLOB, we will go via WWV_FLOW_FILES. The page should be based on a HTML region rather than table. The fields required are:

Template Name               :               Text Item
Description                         :               TextArea
Letter_type                        :               Select List (based on Standard Letter)
RTF                                         :               File Browser (load into WWV NOT field)

You will also need a button to “Submit” (Save) the details. This button should have a dynamic action to perform the following tasks:-

  1. Submit the page (This will load the template into the WWV_FLOW_FILES View)
  2. Execute PLSql to insert the record having first converted the RTF BLOB in the WWV_FLOW_FILES View to CLOB. The fields will need passing over. See  REPORTING_RTF.UPLOAD_RTF
  3. Refresh the page.

In addition, the page should have a 2nd region, based on a standard report (TEMPLATE Table), having the template_id column set as a hyperlink (say edit icon) to page (3). This will show all the existing templates.

Note:
In running the above, I found an oddity, which took quite a while to understand what was going on. When the submit page is fired, Apex starts the load of the RTF into the WWV_FLOW_FILES table and then it progresses onto the execute PLSql action. The PLSql procedure would then look at the table to read the blob, but would fail as the record wasn’t there – an error would flash on the screen (but too fast to read). No records existed in the destination tables (correct as an error had occurred). Totally baffled, I pressed submit again, concentrating intently to read the error……. Everything worked! - I was even more confused now. After loads of debug statements, it appears that the load of the BLOB is done asynchronously, so the procedure starts before it’s there. The second attempt worked because I was loading the same file, and the previous version was now available. I got around this by having a loop with a sleep – not pretty, and needs to be corrected, but good enough for now.

Page 2 - Current Layout.

7 comments:

  1. Looks cool. I wish I would have some more time to dive into the details.

    ReplyDelete
  2. Hi, Richard:

    This is very helpful for me, thank you. We are developers to implement Oklahoma Sex offender Registry. We need to have RTF template editing and uploading functions in our Oracle APEX application. I read your code, but I did not find clue to implement RTF format editor in APEX, I only found uploading and downloading RTF files functions. Could you give us a hint about it?

    Sam

    ReplyDelete
  3. Hi Sam,

    The template was created using an external RTF editor, with variables having the "formatting" in the template, thus, when the substitution takes place, the formatting persists.

    does this make sense ?

    ReplyDelete
  4. Hi, Richard:

    We uploaded multiple template RTF files and stored in database. However, when we tried to download multiple letters based on different template RTF documents, it always downloaded only the first one. Could you share some suggestions to us?

    Thanks.

    Sam

    ReplyDelete
  5. Here is the link of the thread with code in OTN:

    https://forums.oracle.com/forums/thread.jspa?messageID=11047516#11047516

    ReplyDelete
  6. My understanding (and I could be wrong), is that the RTF will have a end-of-file marker, thus you only see the first "letter"

    A quick google suggests that rtf files start with a "{" and end with a "}" - therefore, onnce the first "{" is closed, it's the end of the file.

    Stepping back a little, how ould you expect the application to work ? - sending the text to the browser will instigate an RTF reader, but then you're trying to get it to read multiple files - if the reader is only expecting a single file, it will just "bin" the rest of the data as invalid - somehow you need to tell the RTF reader that multiple files are being sent.

    What is the requirement ? if you don't need to display on the screen, but want to email, can't you create the letters as multiple files and attach each file to an email ?

    ReplyDelete