A CLIENT-SERVER DATABASE SHELL

This invention relates to a server application and a client application for entering and retrieving information from a database and to a method of entering and retrieving information from a database.

With reducing communication and hardware costs, it is becoming increasingly possible to operate complex and sophisticated networks on a highly distributed basis. Such distributed networks allow users to be chose a location based on need rather than proximity to a server or centralised administration function. This flexibility allows improved productivity and/or reduced operating costs.

For example, users may operate on a so-called 'thin client' model in which a relatively simple application such as a web browser is used to render information provided from a centralised server and which takes small amounts of user input to control what is rendered. This allows economies of scale since typically server costs do not scale linearly with user capacity so that costs per user headcount tend to reduce as servers support greater numbers of users. This type of arrangement works well in a 'broadcast' type environment. For example, in a situation in which all users are expecting to use the same data and view the same information and when data input is minimal so that the system is used largely only for viewing.

However, this distributed model breaks down if users are receiving individualised information and/or customised data downloads for offline use and storage and subsequent upload to the server. Control of versions and archiving becomes difficult to manage and the administrative burden on the end user becomes intense. For example, versions may become unsynchronised between the server and remote users since synchronisation often must be managed manually and initiated by the end user. In another example, collaborative working to refine a download such as an electronic document requires highly ordered working practices to be maintained by the end user for example in terms of document storage, archiving, and purging (of old versions). These functions are difficult to support since by the very nature of these distributed networks, the end user often operates alone or in very small teams. Thus the historical administrative and technical support which has been provided in large offices with all users operating at the same location and on the same LAN is no longer available.

Thus there is a need to maintain control of distribution and versioning of downloads in a distributed network so that the administrative burden and opportunity for errors by end users is minimised. This would provide the optimum advantages of both the distributed and non-distributed models by retaining economies of scale and flexibility of user location without greatly increasing end user administrative burden.

In a first aspect, the invention provides a method of remotely interacting with a database comprising receiving a first file by email, automatically parsing the file to extract data and control information, the control information including instructions to act on the data, and creating a second file containing the parsed data and control information.

in a second aspect, the invention provides a method of transmitting data from a database comprising reading data parameters and instructions from a first file, inserting the parameters into a file template to create a second file, extracting a recipient address from the first file and initiating sending of the second file to the recipient address via email.

In a third aspect, the invention provides a method of controlling data transmission and reception between a client application and a database server application comprising providing a plurality of workflow queues representative of predetermined operating system file storage areas, providing a user interface allowing files to be moved between the said workflow queues, acting on the files in different ways depending on the workflow queues in which they are located and transmitting and receiving files automatically via email to populate and empty at least some of the workflows queues without user intervention.

In a further aspect, the invention provides data structure comprising a plurality of files including a word-processor document of a predetermined structure and having predetermined form fields, and a text document including information at least about a destination email address for the word-processor document.

Embodiments of the invention will now be described by way of example and with reference to the drawings in which:-

Figure 1 is a schematic view of a user desktop;

Figure 2 is a schematic diagram showing data flow between a database, an end user and a third party; and

Figure 3 is a schematic block diagram of a database shell, email interface and user desktop in accordance with the invention.

With reference to Figures 1 and 2, an end user is provided with a desktop system which provides a screen based user interface and sits on top of an email client. Data flow between a centralised database 2 and the end user 4 is handled automatically and transparently using email messages automatically sent and received by the email client under control of the desktop system.

The invention is described below in connection with a flow of electronic documents but it will be appreciated that the system may be used to send and receive any type of electronic data. One example of a user-base concerns the need to process General Practitioners' (GPs) Reports for an underwriting department. The department has a need to maintain a body of records that GPs provide concerning their patients, together with details of payments for these services. Often an agent (the end user 4) is used to assist with some administrative matters and in this case, the GP (third party 14) receives a flow of documents and forms and feeds back relevant information to the end user 4. The system aids the end user in monitoring and assisting a plurality of end users 14.

The system keep track of what is required and when and also maintains a centralised record of a particular third party's 14 special requirements. It also allows centralised updating of legislative and regulatory requirements so that the end user 4 is always on top of those matters in his or her particular area of expertise.

The desktop system is designed to allow the user to process offline information which may be received and/or sent, The system creates four areas 6 to 12 on the desktop, one for working on documents, one for printing documents, one for sending documents and one for document templates. These areas represent different parts of a workflow and contain lists of file names.

One advantage of automatic distribution of incoming information into the different areas 6 to 12 is increased security. The desktop system 26 opens all emails received from the centralised database 2 and checks attachments to decide which area of the desktop 6 to 12 to download them to (as described in more detail below).

This approach provides an extra layer of security because the application 26 is able to examine the attachment before it is released to the desktop. There are several optional aspects to this.

The system 26 may implement rules using naming conventions that allow it to place the attachments in specifically designated areas of the desktop. If necessary, potentially sensitive information could automatically be placed in a secure folder.

The system may check for passwords or other flags to ensure that the documents are safe to download onto the desktop.

Optionally, the emails and/or commands may be passed between the desktop system and the database through an encrypted tunnel such as that provided by an SSH connection in order further to improve security and privacy.

The system periodically checks emails in the email client inbox and extracts the required attachments from specially constructed emails. The emails are created automatically by the database system which is described in more detail below. The attachments are automatically placed either in the work area 6 or the print area 8 (depending on whether the document will be worked on or just printed and then forwarded to a third party 14).

The system periodically checks the contents of the send area 10 and automatically sends any documents in that area as attachments in an email. It then deletes the oldest items from the mail client's sent items folder in order to prevent a build-up of files. The sending of materials from the send area can be manually initiated using a dedicated button 20.

The user can work on the documents in the work area, for example, by entering information or reviewing what is required and then can drag and drop the document into the send area for email transmission back to the centralised system.

The user can print all the documents in the print area by pressing a print button 16. Documents that have been printed are automatically sent to an archive area before they are deleted from the print area thus keeping an audit trail of work completed. Printed documents may then be sent to the third party 14.

The operation of the desktop client will now be described in detail.

When the application is started, it reads initialization data, calculates the size of the screen and works out sizes of the controls. It then sets up the file paths for the filelist controls 6 to 12.

When the user double-clicks within any of the filelist controls 6 to 12, a Word processor application (typically Word) is opened and maximised. The document that was selected with the double-click operation is then opened in the word processor application.

In the background, email is retrieved from the email client. This process may be initiated manually. The process checks through the inbox folder for messages intended for the desktop system (typically identified by email header information such as a combination of attachment name and subject line text) and extracts all the attachments from the relevant emails. It copies the attachments into predetermined file paths and lists them in either the print area or the work area according to the name of the attachment. It then deletes the email message from the inbox and refreshes the filelists.

The user 4 typically completes a job when it enters the print area. The documents received for printing typically will have been pre-populated with all the necessary standard paragraphs (which can be kept up-to-date centrally) and the specific information appropriate for the third party 14. Once printed, it may be sent to the third party 14 by post, for example. Documents in the work area are incomplete and require further input and sending off back to the centralised database system.

When the document is to be printed, the user 4 presses a print button 16 on the desktop and for each document in the print filelist, the system opens an instance of Word, opens the document in Word, prints the document, releases Word and moves the document from the print area to an archive area.

The archive area may be viewed by pressing another button 18 which causes the archive area (a predetermined operating system folder ) to open for viewing.

The user 4, may place a file in the send area by using a drag-drop operation on the file name. This has the effect of moving the files between pre-designated operating system folders.

The word documents are typed in with fields denoting areas of the text which will be completed later. Each field is given a name which is used to uniquely match up with information from the dbase system.

Fields can be set to be fill-in enabled which allows the user 4 to enter information into the field, or not fill-in enabled which means that the user 4 cannot change the information in the field. This allows the system to have some fields that it populates and only displays, and some fields that it populates that can later be overwritten.

All documents are protected when they are sent to the user 4, thus preventing the user from changing any part of the document apart from fill-in enabled fields.

New documents are initiated by populating a template file chosen form the templates area 12. Once populated it is dragged to the send area to be sent back to the database system as described below.

Sending is initiated by clicking a send button 20. This causes the system to check through the send mail folder and for each attachment, to create an email (using standard parameters and a standard format described below) for the attachment. The system then deletes the attachment and sends the attachment using the email client. The system then refreshes the filelists

Email Interface

With reference to Figure 3, an email interface 22 sends and receives emails using a mail client and interfaces with a database shell 24. The interface 22 is used to convert the information transmitted by the desktop client 26 into a format that can be used by both senders and recipients.

The email interface 22 carries out the following processes:

Firstly, when information is being sent out to the user 4, the interface 22 picks up a data file containing the content data and an email address from the database shell 24. It then merges data with a template (for example using a Word mailmerge

operation) and then sends the populated template by email to the user(s) 4 as an attachment. The user can open the attachment in a user-friendly format (e.g. Word or another file format) as described above, which can then be saved or worked on before being returned.

Secondly, when the information is returned it is sent as an email attachment; typically a user-edited version of a Word file whether created from scratch using a template from the templates area 12 or as a modified version of something received earlier, the interface 22 extracts the merged data, creates a new data file and sends it to the database shell 24 with a function type telling the database what actions to carry out on the data.

The functions identified by the function type for this example can be split into generic types as follows with the codes shown in the table below:

Other function types are used to control the management of the different processes, according to the information that has been received from the user. These include:

• Logs of fee payments to be made by electronic bank transfer or cheque. • Logs of fee payments that are cancelled or need to be paid immediately.

• Logs of the amount of fee to pay.

• Log of GPR being cancelled before it was sent to the GP.

• Log that fees have been paid.

• Log that reminder letters have been sent.

It will be appreciated that other applications of this technology may require different function types.

Detailed communication between database shell and email interface The data flow between the database shell 24 and the email interface 22 is now described in detail.

The system works by looking for form fields in a Word document received from the user 4 via the desktop system 26. Each form field has a unique name that is matched with the name of a data field used by the database system 24.

If the Word document is to be read, the field contents are extracted from the Word document and sent through to the database system via the shell 24.

When a Word document is to be written for sending to a user 4, the field contents are inserted from values read from the database system via the shell 24.

In the case of incoming email from a user 4, the email comes into an email inbox 28. The email is automatically moved to a predetermined folder based on an email header such as the subject header. The Word document attachment is moved to a predetermined folder (inreq) 30.

A process (readword.exe) 31 then reads the Word document attachment from inreq 30 and converts it to a text file format and places it in predetermined folder inrep 32 in the database shell.

A polling process 34 picks up the file from folder inrep 32 and processes it; placing it in predetermined folder outreq 36. The processing includes any database reading and writing operations which are required.

A process (writeword.exe) 38 reads the processed text file from outreq 36 and converts it to a Word document and creates an .ini instruction file in a predetermined folder 'out' 40 in the email interface 22. The process may reads a template from a predetermined 'originals' folder. The .ini instruction file contains at least the name of the attachment file to send (the newly created Word document - in a predetermined location) and the email address to send it to. It may also include predetermined

subject text for the email which aids automated filtering and sorting within the desktop system 26.

The .ini file is then processed to provide instructions for creating a complete email in the outbox 42 for onward transmission by email to the desktop client 26.

The detailed parameters for readword.exe, writeword.exe and the .ini files are set out below.

Parameters for Writeword.exe

The .ini text file read by writeword.exe from outreq 36 may contain the following parameters:

1. Names of variable to insert into word document

2. Values for each of the variables to insert into Word document 3. A flag to indicate whether the field is to be repeated and which repeat this value is for

4. The name of the document that the data is to be inserted into

5. The email address to send the document to

6. The names of any attachments that should be appended to the end of the document

7. Subject text for the email subject line

E.g.

Parameters for readword.exe

Readword.exe reads a Word document and extracts data from bookmarks which delineate different section of the automatically generated document. It sends through three items of information to the database process 24 for each parameter. These include a variable name and a value as above, and a repeating flag to show repeating data.

The process sends through a function type as noted above, which allows the process 24 to decide what to do with the information extracted from the Word file.

Each file processed by readword.exe 31 and writeword.exe 38 has an associated .ini file which provides additional information on processing. Typically these have the same name as the file to which they relate except for the different filename extension. The contents of the .ini files are described below.

Contents of readini.ini

Value Used for c:\gpr\inrep\ Output path to put the extracted data from the Word document. (outpath) c:\gpr\inreq\ Input path for Word documents to process (requestpath) c:\gpr\temp\ Path to put temporary copies of the documents into for processing. (temppath)

Contents of writeini.ini

Program creates the following path

Requestmergepath+\temp Temporary area for merge documents. (requestmergepathtemp)

The detailed description of writeword.exe and readword.exe and their internal processes is provided below.

Writeword.exe

Overview

This process opens a file (presently a known filename with a .ini extension) containing key information that controls the creation of an attachment for an email. The program reads information from the file, which it uses to customize the document, which is finally emailed to the recipient.

The process uses the following functional modules.

Load the form

• Opens up the ini file and reads parameters from it.

•

• Enables two timers (1 for the bookmark files and 1 for mail merge)

Timer for bookmark files (begin_polling)

• Starts the polling for the bookmark files

• Locates the oldest file in the relevant folder

• Disables the timer • Adds the name of the file to the list box for display

• Calls the begin processing procedure (processjob)

• Deletes the file

• Enables the timer

Location of oldest file name {getoldestfilename)

• Reads the directory of the relevant folder

• Uses the creation time to discover the oldest file

Begin processing (processjob)

• Reads all the values and associated field names from the data file {readdatafile) [getvalues)

• Gets the name of the template Word document

• Gets the name of the email recipient's address • Gets the name of the document suffix (for client specific documents)

• Gets the investment number (no longer required)

• Opens a new copy of the Word template document

• Inserts documents if necessary (addattachments)

• Calls the insert bookmarks procedure (insertbookmarks) • Decides the naming convention for the new Word document (getfilenames)

• Save the new Word document

• Close and clean up Word from memory

• Create the new .ini file

Reads the data file (readdatafile)

• Creates a 2 dimensional array of values and field names concatenating the repeating field to the end of the field name i.e. surnamel, smith, 1 would become surname1_1 for the field name and smith for the value

• Creates a 1 dimensional array to store the field names (for quick searching purposes)

Insert the attachments (addattachments)

• Looks for bookmarks called QN01 to QNO6 (only 6 extra documents at present) • Inserts the document in place of the bookmark (QNO1 to QNO6)

• Inserts a page break after each attachment

Insert the data from the data file into the Word document (insertbookmarks)

• Finds the first block of data (repeating groups) in the document that there is no data in the data file for (findvalue)

• Deletes all the bookmarks and anything else in between the blocks from the first bookmark with no data to the last one that there is no data for

• Deletes any bookmarks that have no data and that are not fill in enabled (this is for formatting purposes to avoid gaps in the Word document) (ignores funcjype)

• Protects the Word document

• Inserts the data from the data file into the Word document

Works out what to call the Word document {getfilenames) • Check to see if the document suffix has been provided in the data file

• If the suffix is provided use it to create the document name (see naming conventions)

• If the suffix has not been provided - create one using the name of the Word document and date and time (see naming conventions) • Create the name of the ini file based on the document name

Creates the ini file (writeinifile)

• Writes the name of the Word document and the email recipient's address to the ini file • Optionally write information relating to a destination folder in the desktop system 26. Typically this information is used to create a predetermined email header (such as a subject header) which dictates the destination folder.

Timer for mail merge (beginjpollingZ) • Checks to see if there are any files called temp2.txt in the temp area and deletes them

• Looks for oldest file name in the relevant directory (getoldestfilename)

• Disables the timer

• Adds the file name to the list box for display • Processes the mail merge

• Deletes the file

• Enables the timer

Process the mail merge {processjob2) • Creates the files for the merge and reads information from the data file

(readdatafile2)

• Adds the name of the Word document being created to the list box

• Opens the mail merge Word document template

• Merges the document with the merge data file (mergedata)

• Decides the naming convention for the new Word document (getfilenames) • Save the new Word document

• Close and clean up Word from memory

• Create the new .ini file

Create the merge text file and extract information from data file (readdatafile∑) • Reads in the name of the Word document template

• Reads in the name of the email recipient

• Creates the new merge data file in the temporary merge path folder and calls it temp.txt

• Copies over the new merge data file to a new file • Deletes the original merge data file

Merges the data from the text file in the temporary merge path folder with the Word document

• Opens the Word template document • Opens the data source

• Merges the data to create a new document

Readword.exe

Overview This process opens Word documents and extracts data written into form fields. Extracted data is written to a file where another program picks it up and processes it.

The process uses the following functional modules.

Load the form

• Opens up the ini file and reads parameters from it.

• Enables a timer.

Timer for files (begin_polling)

• Starts the polling for any files in the relevant folder

• Locates the oldest file in the relevant folder

• Disables the timer

• Adds the name of the file to the list box for display

• Calls the begin processing procedure (processjob) • Deletes the file

• Enables the timer

Begin processing (processjob)

• Open the Word document. • Create a data file

• Extract the data from the bookmarks

• Close the Word document

• Copy data file into relevant folder for dBase polling

• Write to list box that document has been processed.

Extracts the bookmarks (extract_bookmarks)

• Checks that document has a bookmark called funcjype - if not generates an error message because this is not a valid Word file for the process.

• Reads through each bookmark extracting information • Writes information from bookmarks to data file using agreed structure for repeating sets of information

• When all the bookmarks are read, closes the data file.

Document naming conventions

Documents are named according to a convention so that they can be treated correctly by the desktop system 26 on the user's pc.

Documents that are not specific to a customer are named in the following way:

Worddocname+Day+month+year+_+hour+-+minute+.doc

E.g.

gprlog3312April2006_13-54.doc

The /mi file is exactly the same apart from the extension which is .ini instead of .doc e.g.

gprlog3312April2006_13-54.ini

Client specific documents

Documents that are for a specific customer are named according to the customer's name and account number. Subsequent repeated documents for the same customer are differentiated using a sequence number. e.g. gpr10let_607107#8_Posiak.doc would be the 8 ^th gpriOlet for customer Posiak, with account number 607107.

The .ini file follows the same convention apart from the extension which is .ini instead of .doc e.g. gpr10let_607107S8__Posiak.ini