How To

Complete

This section contains sections describing how to do various things. 

Note that 'Administering OpenVPMS' covers topics such as Backup that are not accomplished with the OpenVPMS web application.

Administering OpenVPMS

Complete

This section includes things that are not done using the OpenVPMS web application.

Backup

Complete

This section covers how to backup your OpenVPMS system - both what to backup and how to.

In the following it is assumed that you are running OpenVPMS on a Windows system.  If you are using a linux system, then you should be able to do the appropriate folder name conversions.

Note also that you really should practice disaster recovery, ie assume that your system was lost or destroyed and that you have to start from scratch with a new computer.  That is, your backups are only any good if they can be restored. Backups should be periodically tested or restored to a backup server as part of your backup process. If you don't have a 'backup server', at least get your hands on a system that you can install OpenVPMS on and check that you can restore your database.

What to backup

The MySQL database itself. OpenVPMS keeps everything in the database including the report and document templates and all patient and customer documents, attachments and images. So backing up the openvpms database itself secures most of what you will need to recover in the event of a disaster. Note that MySQL will normally have two other databases (or 'schemas'), mysql and performance_schema.  You don't need to backup these.

Other files. Apart from the database itself, you should backup:

  1. the mysql configuration file, normally called 'my.ini'. In a standard windows system this is located in C:\Program Files\MySQL\MySQL Server 5.5\my.ini - remember that even if you have not done any other adjustments to the standard my.ini file, you will have made the changes required by openvpms as documented in Installing OpenVPMS|Requirements.
  2. any other modified versions of files in the standard release package. These will include any modifed archetypes (.adl files) and any modified files in the <TOMCAT_HOME>/webapps/openvpms/WEB-INF/classes/org/openvpms/web/resource/localisation and the <TOMCAT_HOME>/webapps/openvpms/WEB-INF/classes/style folders.
  3. any other installation specific files. These include any files you have that will be lost if you have a complete disaster and have to start from scratch.

It is also wise to occasionally make a 'system image' that you can restore the system disk from in the event of a disk failure (otherwise you are going to have to reinstall all your software). In Windows 7 this can be done via Control Program|Backup and Restore, and in Windows 8 via Control Program|File History.

When to backup

Essentially this is governed by the amount of data you can afford to lose and have to re-enter. If you do a daily backup at 6pm each business day, and the system fails at 2pm, then you will have to re-enter everything that happened after 6pm the previous day.  Hence a weekly backup is probably not a viable option.

How to backup

Essentially you need to take a copy and put it somewhere else, where 'somewhere else' means, at a minimum, an external disk or storage device. You should also strongly consider having a copy held off-site so that you can cope with your building being destroyed or all your computer equipment being stolen.

Although USB memory sticks are now available with large capacities, and are wonderful for transferring files, they are not reliable enough to use as backup storage. Use a USB hard drive or NAS (Network Attached Storage) device instead.

Backing up to 'the cloud' is a possibility, but most sites do not have sufficient upload bandwidth to make this a viable solution.

So how to backup MySQL.  There are at least three ways to do this: mysqldump, raw file copy, and database replication.

mysqldump
The mysqldump command line utility has lots of options, but for our purposes it is invoked as follows:

mysqldump -u <username> --password=<password> --single-transaction openvpms > <dumpfile>

where both <username> and <password> will be 'openvpms' unless you have set these differently in your system, and <dumpfile> is where you want the data to go, eg E:\backups\opvdump.sql

The single-transaction option allows for the fact that someone may be using the system entering data - and as this implies you do not have to shut down Tomcat to prevent people using the system while mysql dump runs.  You certainly do not want to shutdown mysql itself - it has to be running for mysqldump to work.

In the windows environment, especially with a large database, it is more efficient to use the results-file argument rather than piping the output, eg as follows:

mysqldump -u <username> --password=<password> --single-transaction --result-file=<dumpfile> --log-error=<logfile>

Note that the MySQL Workbench utility includes a facility to export (ie dump) a database.  However, the command line mysqldump utility is more useful for backups since it can be invoked in a bat file.

To restore the database, you use the mysql command line utility as follows:

mysql -u <username> -p openvpms < <dumpfile>

Note that MySQL dumps can be large - see also 'File Sizes' below. Compression can achieve some space saving at the cost of the extra time required to do the compression. Linux users will be familiar with the process or piping the output of one program into another, and thus on a linux system one can do:

  • To backup: mysqldump -u openvpms -p openvpms | gzip > dump.sql.gz
  • To restore: gzip -cd dump.sql.gz | mysql -u openvpms -p openvpms

On Windows systems the same can be done using the 7-Zip compression utility (see http://www.7-zip.org) as follows:

  • To backup: mysqldump -u openvpms -p openvpms | 7z a -si dump.sql.7z
  • To restore: 7z x dump.sql.7z > mysql -u openvpms -p openvpms

 

raw file copy
If you want to minimise the database restore time, then you can simply copy the MySQL database files to another device.  In MySQL terminology, this is called a 'cold backup' - see http://dev.mysql.com/doc/refman/5.5/en/innodb-backup.html. To do this you need to shut down MySQL before you copy the files, and you have to copy nearly everything in the MySQL data folder. Specifically you need to copy the ibdata files and each of the sub-folders, ie mysql, performance_schema, and openvpms.  You do not need to copy ib_logfile1 and ib_logfile2.

In the windows environment, the best copy program to use is robocopy.

Note that when do the recovery (by copying the files back) you MUST still be using the same version of MySQL, and you may need to be wary about the log files.  If you have not been backing these up, then when you copy the other files back you must ensure that any old log files are deleted

database replication
If you have a large busy practice, you should consider using MySQL's ability to replicate the database on another machine. There is a slave database on the other machine, and updates to the master database are continuously copied to the slave. When a disaster occurs, it is quite easy and very quick to switch operations to the backup machine which was running the slave database which now becomes the master.

See http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html

other information
For more information on backing up and restoring MySQL databases, see:

File Sizes

The size of an OpenVPMS database depends mainly on two things, the number of transactions recorded, and the number (and size) of the stored documents. The following data is taken from a reasonably sized business. ('participations' counts the number of links between data.)

Item Size (GB) Count Time (min)
MySQL Database 14.8   8
documents.ibd file 7.9 36,800  
participations.ibd file 4.3 9,907,500  
mysql dump 10.4   45
compressed mysql dump 7.7   105

As you can see, the documents and participations files make up some 80% of the total database size. The Time column shows the time required for the raw file copy, and the uncompressed and compressed mysql dumps. In all cases the data is moved to a NAS unit via a 1gigabit network connection.  Note that although compression gives a space saving of 26%, it increases the time required by 130%. The relatively small space saving is due to the fact that many of the documents are jpeg images and thus uncompressible.

Hardware redundancy

Apart from backing up, it is possible to decrease the probability of failure by providing some redundancy in the system.  There are two things to consider, RAID and server class systems.

RAID (Redundant Array of Inexpensive Disks) - see http://en.wikipedia.org/wiki/RAID - provides a storage system that is resilient to failure. These can be configured as a simple 2 disk mirror RAID-1 setup where data is written to both drives, and the system will continue to operate when one fails. At the other end of the scale there are multi-disk RAID-5 and -6 systems with hot stand-by drives.

Server class hardware  There is a very real difference between a standard desktop machine and a server class system (apart from the price and weight). Server machines typically offer a remote access console (so you can power on the system from afar), large fast storage systems, and dual power supplies.

Even if you don't use a server class machine, you should consider the use of enterprise rather than consumer class disk drives.

Second system  The simplest approach to hardware redundancy is simply to have a second system that is a exact duplicate. You can use this system for other non-critical things (perhaps as a spare worstation).  You can then make an image backup of the primary system.  When it fails, you disconnect the primary system from the network (in case it is still partially alive), restore the image backup to the backup system, and then restore your last database backup. You may also need to unplug any printers from the failed system and plug them into the backup system. Your users can then resume work - but they will have to re-key transactions since the last backup.

Note we need the backup machine to be an exact duplicate so that the image backup from the primary system can be restored and run without any changes.

Housekeeping

Complete

This section deals with various housekeeping activities.

OpenVPMS Logs

The openvpms log files (in <TOMCAT-HOME>/logs) are kept under control by the settings in <TOMCAT-HOME>/webapps/openvpms/WEB-INF/classes/log4j.properties file.  The standard setup is to 'rotate' these when they get to 1024KB and keep only 1 backup.  Thus in a mature system you will see an openvpms.log.1 of size 1MB and a smaller openvpms.log (and similarly for the openvpms-full.log).

You can keep more data by increasing the log4j.appender.fileout.MaxBackupIndex parameter from 1 to something greater.

Note that the openvpms.log and openvps-full.log backups are not in sync (ie openvpms-full.log.1 does not contain the full data for the events in openvpms.log.1). This is because the full log grows significantly faster than the standard log.

If you wish to keep older synchronised versions then you need to implement a system for renaming the log files on a regular basis before they grow to 1MB in size. In unix systems this can be done using the logrotate facility.  In windows systems you can have a bat file containing commands like the following and run it via the task scheduler.

set DSTAMP=%date:~6,4%-%date:~3,2%-%date:~0,2%

net stop "Apache Tomcat 6.0 Tomcat6"
net stop "MySQL55"

sleep 10

ren C:\openvpms\data\mysql-slow.log %DSTAMP%-mysql-slow.log
ren C:\openvpms\data\mysql-slow.log %DSTAMP%-mysql-error.log
ren "C:\Program Files\Apache Software Foundation\Tomcat 6.0\logs\openvpms.log" %DSTAMP%-openvpms.log
ren "C:\Program Files\Apache Software Foundation\Tomcat 6.0\logs\openvpms-full.log" %DSTAMP%-openvpms-full.log

..... other stuff to backup database etc

net start "MySQL55"
net start "Apache Tomcat 6.0 Tomcat6"

exit

docload Destination Directory

The document loader destination directory receives the files that have been loaded.  Since the documents are now in the database, these files can either be deleted or archived elsewhere.

Note that regularly sweeping this directory allows the document loader to load updated copies of documents when the overwrite option is set. Otherwise if the document 3214567_xray.jpg was previously loaded, then when an attempt is made to load an updated copy, this will fail because the file 3214567_xray.jpg is already present in the destination directory.

 

 

Correct 'patient added to wrong customer'

Complete

If you have a situation is which a patient was mistakenly added to the wrong customer some time ago and you need to currect the situation and keep the patient's medical history but move all financial transations involving the patient to the correct owner, then do as follows:

1. Reverse any transactions on the OLD Customer that involved the patient to be transferred....even if they included another pet from that customer's file.

2.  Reverse any payments associated with those invoices. If payments cannot be reversed that match the invoice total - create a REFUND of the correct payment type.

3. At this point the OLD customer balance should be the same before you started.

4. Transfer the Patient to the NEW(Correct) Customer.

5. At this point you have the history, patient and customer correct - you just need to fix the transaction history.

6.  If invoices that you reversed contained transactions that were still relevant to the OLD Customer, recreate these invoices and pay them using the same payment type you reversed.

7.  In the new Customer recreate the relevant invoice items you reversed from the old customer and pay them using the same payment method you either created the refund for OR reversed in step 2.  Adjust line item costs to be the same (if prices have risen).

8. Make notes on each new invoice about the actual date the transaction took place on - do the same for payments.

9. At this point both client Balances would be in theory either zero or reflect the balance before you started the process.

10. Run a balance check over each customer.

11. The TILL for this day will have a lot of transactions that don't really exist - but the reversals and payments should balance each other out, so the nett effect on the daily total should be zero.

HL7 Pharmacies

Complete

Overview

This page describes how to configure OpenVPMS to interface with an HL7 pharmacy such as Cubex. For an overview of HL7 in OpenVPMS, see Concepts - HL7.

Configuration

To configure OpenVPMS to interface with an HL7 pharmacy the following steps must be performed:

1. Configure an HL7 MLLP Sender, to send HL7 messages to the pharmacy

2. Configure an HL7 MLLP Receiver, to receive HL7 messages from the pharmacy

3. Configure an HL7 Pharmacy with the Receiver and Sender

4. Configure an HL7 Pharmacy Group (optional)

5. Configure products to be dispensed via the Pharmacy/Pharmacy Group

1. Configuring an HL7 MLLP Sender

The HL7 MLLP Sender is used to send pharmacy orders, and patient admission and discharge messages to the pharmacy. It is configured via the Aministration - HL7 - Connectors page.

Select New - HL7 MLLP Sender and enter the pharmacy connection information as per your pharmacy provider's instructions.

2. Configuring an HL7 MLLP Receiver

The HL7 MLLP Receiver is used to receive pharmacy dispense messages from the pharmacy. It is configured via the Aministration - HL7 - Connectors page.

Select New - HL7 MLLP Receiver and enter the pharmacy information as per your pharmacy provider's instructions.

Note that Port is a TCP/IP port that OpenVPMS listens on. It must be accessible to the pharmacy through any firewall. Appropriate security precautions should be taken to avoid other parties from accessing the port.

3. Configuring an HL7 Pharmacy

The HL7 Pharmacy is used to specify a pharmacy to send pharmacy orders to, and receive dispense messages from. It is configured via the Administration - HL7 - Services page.

Select New - HL7 Pharmacy and enter the:

  • HL7 MLLP Sender configured in step 1 for the Order Connector
  • HL7 MLLP Receiver configured in step 2 for the Dispense Connector

4. Configuring an HL7 Pharmacy Group

An HL7 Pharmacy Group is used to group pharmacies by Practice Location.

It is only required for multi-location practices that use different pharmacies for each practice location.

It is configured via the Administration - HL7 - Services page.

Select New - HL7 Pharmacy Group and enter the HL7 Pharmacy or Pharmacies configured in step 3.

5. Configuring Products

To configure products to be dispensed by a Pharmacy/Pharmacy Group, set the Pharmacy on the appropriate Medication or Merchandise product, or the Product Type.

For large numbers of products, specifying the Pharmacy on the Product Type is the preferred approach, although all products with the Product Type must be dispensed this way.

Testing the Connection

The simplest way to test the connection between OpenVPMS and the pharmacy provider is to admit a patient. This will send an HL7 ADT A01 message via the Order Connector to the pharmacy.

Its progress can be viewed on the OpenVPMS side by going to Aministration - HL7 - Connectors, selecting the appropriate HL7 MLLP Sender, and clicking Messages.

To test the connection from the pharmacy to OpenVPMS, dispense a product. A corresponding:

  • HL7 RDS O13 should appear in the Messages window for the HL7 MLLP Receiver
  • Customer Order should appear in Customer - Orders

Orders and Deliveries

Complete

This page covers how to run the Supplier's side of OpenVPMS - ie how to generate orders and receive deliveries.

Note that the ESCI system is not covered here.

The first thing to do is to ensure that you have things set up so that ordering system will work. ie

  • the suppliers have been created using Suppliers|Information|New
  • products from that supplier set to use it and the supplier is set as preferred, and the ordering information is set in the product's Supplier Tab
  • products have their stock location data set with current, ideal and critical quantities set in the product's Stock Locations Tab

 

The basic flow is:

  1. Use Suppliers|Orders to create an order to a given supplier for a given stock location, edit if necessary, and when satisfactory, finalise the order [and print and send off to the supplier]. You can either use the Generate Orders button to generate the order(s), or you can create the order by keying in the line items and quantities, or you can do both - ie use Generate Orders, and then edit the order to add more items.
     
  2. When stuff arrives, use Suppliers|Deliveries to create the delivery record to tell the system that stuff has arrived, edit if necessary, and when satisfactory, finalise the delivery. Note that the system makes it easy for you to create the delivery record - it will bring up a list of what is on order and you just tick off the items (or if all got delivered, the complete order). If an item was short-shipped (eg you ordered 10 and got 7), then tick the item to get it listed as one of the delivered line items, and then edit the delivery line item to record the lower actual quantity. If an item was over-shipped (eg you ordered 10 and got 15) and you are going to accept the extras (and not send them back to the supplier) just record the quantity actually received.

    Note that when you press the Finalise button you are finalising THIS delivery - you are not necessarily indicating that the complete order has all been delivered - just the stuff that arrived today. The act of finalising the delivery updates the stock and and if you have Auto-Price update set, then the cost price (and hence your sell price) will be automatically updated.

    If this was a part delivery, when more stuff arrives, create the delivery, edit if required, and finalise this delivery.

    If you receive less than you ordered, and you know that there will be no more deliveries, then you need to edit the order (not the delivery) to indicate this.  There are two ways to do this: either you can change the order's status to Cancelled, or (more correctly), for those line items that short-shipped, set the Quantity Cancelled equal to (Quantity Ordered - Quantity Received). In this second case, the order's delivery status will change to Full when all the line items have (Ordered - Received - Cancelled) less than or equal to zero.

We have now done all that is really required. However, if you want OpenVPMS to track the supplier financials, then you need to generate the supplier invoices and enter the payments. Note that you do not need to do this, and many practices use their accounting system to handle the supplier finances.

Invoices and payments are handled as follows:

  1. Select a finalised delivery on the Suppliers|Deliveries  screen and press the Invoice button.  The system will create the invoice to the supplier for all delivered items from that supplier. Hence if the order was delivered in three lots, then if you wait until you have three finalised deliveries, pressing Invoice will create just one invoice.
  2. Now use Suppliers|Charges to process the invoice - edit it if necessary, and then finalise it.  Note that the Supplier will not want this invoice - he should have already sent you one.  Hopefully the two will agree - in fact you should delay finalising the invoice until the two do agree. [You may have set the supplier's list price wrongly, so your order shows the widgets at $10 each, but the supplier is actually charging $11.50 each.]
  3. Having finalised the invoice, it will now appear in Suppliers|Accounts. After you pay the supplier, you can now use Supplier|Payments to tell the system that you have paid.

 

Given the above flow, when the system is running happily, you use the Generate Orders button on the Suppliers|Orders screen to generate the order (s).  See the above link for the algorithm used.

In a startup situation, you will have orders outstanding that the system does not know about. You could use Suppliers|Orders|New to enter the order.  However, this is not mandatory - you can simply create the delivery records as the stuff is delivered. Note that because you have not entered the order, when you press New on the Suppliers|Deliveries screen,  the system will not find any orders to be delivered. Just OK this and it will generate an empty delivery record for you and you can manually add it all the line items that have been delivered.  Finalising this delivery will cause the stock to be updated.

 

 

Over the Counter Refund

Complete

If you do an OTC sale to an anonymous customer, and then they return the goods and you want to do a refund, the procedure is as follows:

  1. Call up the Customers|Account screen
  2. Press the Select button and search for the OTC account and select it
  3. Look through the transactions to find both the Invoice for the OTC sale you want to refund and its payment
  4. Select each in turn and press the Reverse button to reverse the two transactions. This will create a credit to reverse the invoice and a refund to reverse the payment

Now do the physical reversal - ie put the item(s) back on the shelf, and give the customer back their money.

Note that the refund created by reversing the payment will have the same payment type as the original payment, and you will need to do the refund the same way.

Thus, if they paid by credit card, you will need to do a credit on their credit card. You should NOT just give them money from the till.

 

Patients and Customers - Adding

Complete

The fastest way to add a new customer and their patient(s) is to do as follows:

1. On Customers|Information screen, click New then fill in the name etc, and then press the Add button on the Patient tab, and then the binoculars after the Patient name slot - ie as follows:

 

Having pressed the Patient binoculars, you will get:

Press the New button, and fill in the patient details so it looks like the following (note the use of the relative date facility to quickly set the Date of Birth and hence Age):

 

And then press the OK button, which will bring you back to the new customer screen as follows:

If you want you can set the ownership date to the real ownership start date (ie ask Mr Smith when he acquired Rover) but it’s not necessary.

If there are more patients, press Apply to save what you have entered, and then click the Patient Add button to add the next patient.

Having added the patients, you can now click the Contacts tab so that you can add Mr Smith’s contact details.

Finally, press the OK button and you have a new customer with their patient(s).

 

Patients and Customers - Finding

Complete

If you have already found your customer, then you can either select the patient from the Customers|Information screen, or you can click Patients in the top menu and use the Select button (useful if the customer is a boarding kennel and has 300 patients recorded).

However, if you are looking for a patient and have not already found the customer, then the best approach is not go via Patients|Information|Select, but rather Customers|Information|Select.

As shown below, if you omit any customer information, but provide some or all of the patient name, you will be shown all the matching patients, irrespective of owner.