How to get yesterday for history function

Submitted by Guest on Sun, 06/04/2014 - 06:02

Implementation discussion

I am trying to build a report macro that will grab medications for yesterday onwards.

If I use the following datasource expression

$P{dataSource}.getExpressionDataSource("history:medication(openvpms:get(., 'patient.entity'), java.sql.Date.valueOf('2010-01-01'),null)")

then the macro happily expands and dumps all medications since 1Jan2010.

However, if I change this to

$P{dataSource}.getExpressionDataSource("history:medication(openvpms:get(., 'patient.entity'),java.sql.Date($P{yesterday}.getTime()),null)")

where $P{yesterday} is a non-prompted for parameter of type java.util.Date with the expression

new Date($P{today}.getTime()-86400000)

and $P{today} is a non-prompted for parameter of type java.util.Date with the expression

new Date()

then the macro will not expand. [The reason for creating the parameter today, and then calculating yesterday from it is that I convinced myself by doing some testing that doing it this way stripped the time component out of the date - ie the $P{today}.getTime() generates the millisec time for 00:00:00 on today's date.]

How do I give the history:medication as parameter that evaluates to yeterday's date that it is happy with?

Regards, Tim G

Re: How to get yesterday for history function

Submitted by tanderson on Sun, 06/04/2014 - 08:49.

I think:

java.sql.Date($P{yesterday}.getTime())

needs to be:

java.sql.Date.new($P{yesterday}.getTime())

Re: How to get yesterday for history function

Submitted by tgething (not verified) on Mon, 07/04/2014 - 12:05.

Tim A - that didn't help. After much experimentation and testing I have a somewhat crude solution. I think that the underlying problem is that since what is being passed in the datasource expression is a quoted string, you cannot have parameters in it. ie if the expression is like

$P{dataSource}.getExpressionDataSource("history:medication(openvpms:get(., 'patient.entity'),new java.sql.Date($P{yesterday}.getTime()),null)")

the $P{yesterday} in the "history:m.... ...null)" string is not expanded.

So I changed the data source expression to

$P{dataSource}.getExpressionDataSource($P{parameter1})

and defined parameter1 as:

"history:medication(openvpms:get(., 'patient.entity'),java.sql.Date.valueOf('"+$P{yesterdayS}+"'),null)"

with yesterdayS as:

new java.sql.Date((new java.util.Date().getTime())-86400000).toString()

So yesterdayS evaluates to "2014-04-06" (today being 7April 2014), and parameter1 evaluates to

history:medication(openvpms:get(., 'patient.entity'),java.sql.Date.valueOf('2014-04-06'),null)

then everything works.

I suspect that it is possible to do things more cleanly than this, but I now have a recipe that works.

Regards, Tim G

Re: How to get yesterday for history function

Submitted by tanderson on Mon, 07/04/2014 - 14:13.

Within xpath expressions, new objects are created using:

<fully qualified class name>.new(arguments)

The $P{ } is massaged by JasperReports, so it needs to be outside the xpath expression. Something like:

$P{dataSource}.getExpressionDataSource("history:medication(openvpms:get(., 'patient.entity'),java.sql.Date.new(" + $P{yesterday}.getTime() + "), null)")

Re: How to get yesterday for history function

Submitted by gretalloyd on Wed, 09/04/2014 - 20:36.

Hi,

I think I understand that this expression only works in a jasper report??

Is there a way to get 'medications dispensed' into a patient letter (not a jasper report)?

We would like to insert, via expression, into a patient letter (ie transfer notes) the medications dispensed for a patient in within the last visit (or within a short date period if that's not possible).

Thanks,

Greta

Re: How to get yesterday for history function

Submitted by tanderson on Thu, 10/04/2014 - 00:02.

This can be done in OpenVPMS 1.7 using Report Macros, although you will need a Jasper Report to list the medications dispensed.

The steps are as follows:

1. Create a Jasper Report and sub-report that list the medications for the most recent visit for a patient. I've attached a Patient Discharge Summary and Patient Discharge Summary Items as an example.

2. Go to Administration|Templates and load the two reports from 1.

Patient Discharge Summary needs to have Type "Report"
Patient Discharge Summary Items needs to have Type "Sub report"

3. Go to Aministration|Lookups and create a new Report Macro, to run the report

4. In your Patient Letter, include a field that runs the report macro. e.g.:

[macro:eval('@dischargesummary', .)]

See the Test Letter.odt attached below.

In the above:

macro:eval('@dischargesummary',.) evaluates the macro against the current object, which will be a Patient Letter
openvpms:get(.,'patient.entity') gets the patient from the current object (the Patient Letter)
party:getPatientVisit(...) - gets the most recent Visit for the patient

Attachment	Size
Patient Discharge Summary.jrxml	4.95 KB
Patient Discharge Summary Items.jrxml	2.86 KB
Test Letter.odt	12.65 KB

Re: How to get yesterday for history function

Submitted by gretalloyd on Thu, 10/04/2014 - 20:29.

That's great. Works perfectly.

The next thing I can't figure out though is how to edit the jasper report. When I try to change the wording

'While in hospital' to 'Medications dispensed' (or even just delete the p in hospital), then re-upload the jrxml document to the Patient Discharge Summary Template (report) it gives me this:

Failed to read Patient Discharge Summary.jrxml. Please ensure it is saved to be compatible with JasperReports 3.7.6

Re: How to get yesterday for history function

Submitted by tanderson on Thu, 10/04/2014 - 21:59.

OpenVPMS uses JasperReports 3.7.6, so you need to save your .jrxml files in a format compatible with this version. To do this in iReport, click on Tools -> Options and set the Compability to JasperReports3.7.5 as shown below:

Re: How to get yesterday for history function

Submitted by gretalloyd on Sat, 01/11/2014 - 19:47.

Hi again,

I'm slowly progressing with having the medications dispensed printed in the patient documents, but I can't seem to get the expression for label to work. Does anyone have an idea what I'm doing wrong.

Thanks,

Greta

Attachment	Size
Patient Discharge Summary 1.jrxml	4.38 KB
Patient Discharge Summary Items.jrxml	2.75 KB
test1.odt	7.93 KB

Re: How to get yesterday for history function

Submitted by tgething (not verified) on Sun, 02/11/2014 - 07:07.

Greta - can you provide more information on what is going wrong.

Below is Max's medical record: (note that this is from my anonymised database so the medication names are scrambled)

The following is what came out:

So it retrieved the medication name and quantity, but got confused with the label.

Is this the problem that you are seeing?

If so - the problem lies in your sub-report field name - iReports shows:

Notice that you have used "label" - which looks different from all the other field names which all have the form "target.xxxxx"

So the problem is that the system cannot figure out what you mean by "label".

If you look at http://www.openvpms.org/documentation/csh/1.7/reference/reportsForms you will see that in a sub-report all the fields have names of the form target.xxxx where the xxxx bit depends on the data source.

In this case we are dealing with visit items ['items' because the subreport expression in the report is

$P{dataSource}.getDataSource("items", new String[]{"target.patient.entity.name", "target.startTime"})

ie pull all the items from the visit act.patientClinicalEvent, and if we look at thgis archetype we find that the items have archetype actRelationship.patientClinicalEventItem and if we look at this we find it contains all the things that can go into the medical record including act.patientMedication - and if we look at this we find:

so the label is there.

Hence we need to adjust the field name in the sub-report from "label" to "target.label"

Doing this we now get:

which is correct - but probaly should be reformatted to put the label in its own field.

Note that Max's visit shows other stuff like the invoice items and investigations. You could extract these, but in order to do so you need to be a little clever. The trick is to realise that the field target.displayName contains the name of the medical record item, (eg Medication, Weight, etc) and target.shortName contains the archetype name (act.patientMedication, act.patientWeight, etc) so you can extract the data for each of these.

Note that the Print When expression for the Detail1 band is set as follows:

ie the sub-report as written is only showing the Medication items.

If you want other stuff shown, then the easiest way is to add other details bands, one per type, and set each one to print only for its type. For each type, look at its archetype and get the field names, and use those.

Hope this helps.

Regards, Tim G

Re: How to get yesterday for history function

Submitted by gretalloyd on Sun, 02/11/2014 - 11:29.

That helps so much Tim, thank you.

Could you possibly tell me how to make it so all the text in a large label would fit. It seems to be cutting off the text in the label if it is long.

I've attached the new document.

Greta

Attachment	Size
Patient Discharge Summary Items.jrxml	3.3 KB

Re: How to get yesterday for history function

Submitted by tgething (not verified) on Mon, 03/11/2014 - 04:50.

Greta - the following is my version

I have attached the items jrxml. You will see that rather than simply printing $target.label, I have used the expression $F{target.label}.replace("\n"," ") - ie I have replaced the new lines with spaces so as to coerce the label text into one line. The wrap that you see above is due to Open Office wrapping the text, and the only way to stop this is to increase the text that OO will fit on the line by either decreasing the font size, or decreasing the margins. The above may be enough for you because you don't need the dual language text.

Regards, Tim G

Attachment	Size
Patient Discharge Summary Items.jrxml	3.15 KB

Re: How to get yesterday for history function

Submitted by gretalloyd on Sun, 02/11/2014 - 11:37.

Actually, I think I just figured that out. It was in the archetype.

Thanks so much for your help.

Greta

Re: How to get yesterday for history function

Submitted by gretalloyd on Mon, 10/11/2014 - 20:44.

Hi Tim and others,

I have one more question because I have tried font extensions and I keep stuffing it up. I would like the text in the label to be in italics. The document it is being inserted to has text in Verdana size 10.

Is there an easy way to do this within OpenOffice or do I need to keep trying with font extensions?

Thanks,

Greta

Attachment	Size
Patient Discharge Summary Items.jrxml	3.96 KB

Re: How to get yesterday for history function

Submitted by dadmin on Tue, 11/11/2014 - 03:10.

Greta - the font in iReports has no functional effect on the font in the Open Office document. The macro:eval call in the .odt document calls the maco which runs the jrxml file - and this just delivers text back to OO writer which uses the font specified for the field in .odt document. In your Test.odt - this is Arial 14 - as shown below.

You can change this to whatever you want. HOWEVER - if the jrxml report is delivering multi-column data there may be a problem. Let us say it returns:

dd/mm/yyyy col2 text col3 text

The date column is OK because all numbers are the same width, but the coln text will vary in width UNLESS you use a monospaced font such as courier or lucinda sans typewriter.

So if you use a proportional font (eg verdana) then col2 will be aligned as you want (because col1 is the date and thus always the same wdith) BUT col3 will not be aligned because where it starts depends on the characters in col2.

Hence the best approach is to do as much as possible in OpenOffice, and just use the macro report facility to return the information that you cannot get in OO. Below is Scattermoose's (the family test dog) vaccination certificate - as you can see all the information retrieval is done in OO except for the actual vaccation history - and this uses a 2 column display with the first being a date - so I can use a proportional font (its Times Roman) for the history text

and here is the OO document

I hope this clarifies things.

Regards, Tim G

Re: How to get yesterday for history function

Submitted by gretalloyd on Sat, 11/07/2015 - 20:36.

Hi Tim G,

We have been using this report with hardly any issues, but I have been asked to try again to fix it so it doesn't cut off the text when the text is long in the label. I cannot figure it out, so I wonder if you could have another look and see if you can see what I need to change in the Jasper report.

In the screenshot below, the problem is shown for the drug Frusemide, where the label cuts off after May need

My document is attached.

Thanks so much,

Greta

Attachment	Size
Patient Discharge Summary Items.jrxml	4.17 KB

Re: How to get yesterday for history function

Submitted by gretalloyd on Sat, 11/07/2015 - 20:37.

Woops, this is the screenshot:

Re: How to get yesterday for history function

Submitted by tgething (not verified) on Sun, 12/07/2015 - 04:13.

Greta - I also need the OO template that you are using and the text of the report macro.

Can you also tell me what version of OpenVPMS you are running - are you on 1.8 yet? [If not I need to do my testing on a 1.7 system.]

Regards, Tim G

Re: How to get yesterday for history function

Submitted by gretalloyd on Sun, 12/07/2015 - 07:51.

Hi Tim,

Thank you so much. We are using 1.7.1.

The document is attached and the text of the macro is: party:getPatientVisit(openvpms:get(.,'patient.entity'))

Attachment	Size
Referral Letter Advanced Vetcare.odt	426.15 KB
Patient Discharge Summary Items.jrxml	4.17 KB
Patient Discharge Summary.jrxml	4.45 KB

Re: How to get yesterday for history function

Submitted by tgething (not verified) on Sun, 12/07/2015 - 14:28.

Interesting.

Shows as:

but

shows as:

However, there is also a lenght limitation. If I set the label as:

(where the label text is just under the 300 character limit)

then it shows as:

Finally - if I just have a long line of text with no terminating new line, ie

then it shows as:

So there are two problems:

1) the code that transfers the text generated by the report macro is sensitive to the presence of new line characters. If the text does not have a new line at the end then it is not transferred in full.

2) the same code will not handle the full 300 characters that can be present in a label (I would not be surprised if the limit is 255 or 256)

So:

a) we appear to have a real bug - Tim A do you want me to Jira this? [Note I tested both in 1.7 and 1.8 - same problem.]

b) there is a work-around for the truncation problem that you are seeing - just hit Enter at the end of the text to add a terminating new-line.

By the way, the problem is not the jrxml subreport - I played with increasing the label text space in this and it had no effect.

Regards, Tim G

PS - I assume that the reason that you prompt for clinical notes rather than using the Notes text in the medical records is that you want to separate the 'external' clinical notes from the 'internal' medical records Notes text.

Re: How to get yesterday for history function

Submitted by tanderson on Sun, 12/07/2015 - 22:46.

This is a feature of the JasperReports report -> text conversion.

Internally, JasperReports sizes elements on the page in terms of fractions of pixels.

When converting to text, it divides the page up into a grid, based on the character size (10 pixels in your case). It then maps elements into the grid. Those that don't fit are omitted.

Multi-line fields screw up the mapping e.g. a 9 line medication label is stored internally as having a height of 82.256836 pixels, for Monospaced font size 7. For a character size of 10, JasperReports decides that this takes up 8 lines, so the last line is truncated.

So the workaround is as Tim G says - add new lines until it exports correctly.

Regards,

-Tim A

Re: How to get yesterday for history function

Submitted by gretalloyd on Sun, 12/07/2015 - 20:55.

Tim, that is awesome. Thanks for figuring it out. Until the bug is fixed, I have just asked everyone to press ENTER at the end of their labels before pressing OK.

I don't know about clinical notes vs medical records, didn't do whatever I did on purpose ;)

Thank you again so much,

Greta

Re: How to get yesterday for history function

Submitted by tgething (not verified) on Mon, 13/07/2015 - 09:20.

I have been playing some more.

a) I can fix the 'user needs ensure that they press Enter at the end' problem - by adjusting the code to do this in the jrxml - ie print $F{target.label}+"\n\n" rather than just $F{target.label} [I found that I needed two new lines to reliably print all the label text.]

b) I experimented with medication with no label text - which should result in $F{target.label} being null - as all a result one normally copes with this by using the expression: ($F{target.label}==null)?"":($F{target.label}+"\n\n")

However, this fails with Expression Error when the label has no text. Checking the full log I find:

13 Jul 2015 08:53:40,532  WARN AbstractExpressionEvaluator,http-8080-4:104 - Failed to evaluate: [macro:eval('@dischargesummary', .)]
org.apache.commons.jxpath.JXPathInvalidAccessException: Cannot invoke public java.lang.String org.openvpms.macro.impl.MacroFunctions.eval(java.lang.String,java.lang.Object); Failed to evaluate macro=@dischargesummary
    at org.apache.commons.jxpath.functions.MethodFunction.invoke(MethodFunction.java:99)
    at org.openvpms.component.system.common.jxpath.AbstractObjectFunctions$1.invoke(AbstractObjectFunctions.java:128)
    at org.apache.commons.jxpath.ri.compiler.ExtensionFunction.computeValue(ExtensionFunction.java:102)
    at org.apache.commons.jxpath.ri.JXPathContextReferenceImpl.getValue(JXPathContextReferenceImpl.java:353)

.....

    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Unknown Source)
Caused by: org.openvpms.macro.MacroException: Failed to evaluate macro=@dischargesummary
    at org.openvpms.macro.impl.LookupMacros.run(LookupMacros.java:152)
    at org.openvpms.macro.impl.LookupMacros.run(LookupMacros.java:126)
    at org.openvpms.macro.impl.MacroFunctions.eval(MacroFunctions.java:95)
    at sun.reflect.GeneratedMethodAccessor790.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.apache.commons.jxpath.functions.MethodFunction.invoke(MethodFunction.java:93)
    ... 73 more
Caused by: java.lang.ArrayIndexOutOfBoundsException

So there appears to be a problem if the medication has label text.

c) I also played with ammending the report to include any notes in the medical record. This does not work fully because of the 'things don't work if the text is too long' problem. That is with the visit as follows:

the referral letter comes out as: (as you can see the bottom half of the note is missing)

Tim A - any comments appreciated. I have attached the jrxml FYI, the odt letter is unaltered. [The above testing has been done with 1.7.1 and iReports 3.7.6]

Regards, Tim G

Attachment	Size
Patient Discharge Summary-n.jrxml	3.48 KB
Patient Discharge Summary Items-n.jrxml	6.08 KB

Re: How to get yesterday for history function

Submitted by tanderson on Mon, 13/07/2015 - 11:01.

If I run this in 1.8, the problem can be traced to JasperReports:

java.lang.ArrayIndexOutOfBoundsException: 38
    at net.sf.jasperreports.engine.export.JRTextExporter.exportText(JRTextExporter.java:596)
    at net.sf.jasperreports.engine.export.JRTextExporter.exportElements(JRTextExporter.java:374)
    at net.sf.jasperreports.engine.export.JRTextExporter.exportPage(JRTextExporter.java:350)
    at net.sf.jasperreports.engine.export.JRTextExporter.exportReportToWriter(JRTextExporter.java:326)
    at net.sf.jasperreports.engine.export.JRTextExporter.exportReport(JRTextExporter.java:207)
    at org.openvpms.report.jasper.AbstractJasperIMReport.exportWriter(AbstractJasperIMReport.java:703)
    at org.openvpms.report.jasper.AbstractJasperIMReport.exportToText(AbstractJasperIMReport.java:669)
    at org.openvpms.report.jasper.AbstractJasperIMReport.export(AbstractJasperIMReport.java:580)
    at org.openvpms.report.jasper.AbstractJasperIMReport.generate(AbstractJasperIMReport.java:364)
    at org.openvpms.macro.impl.ReportMacroRunner.run(ReportMacroRunner.java:84)
    at org.openvpms.macro.impl.MacroContext.run(MacroContext.java:138)

This is being caused by the number of lines (39) in the output exceeding the calculated page height in characters (38). This is for an element height of 387 pixels, so JasperReports is rounding down when it should be rounding up.

I can't think of an easy workaround for this, as there doesn't appear to be a way to calculate how much vertical space a multi-line label will occupy. Ideally, it would be possible to select a font size + line spacing which corresponds exactly to the character size.

Using a font size of 8 with no line spacing seems to work best, but may not work for all input data, and may vary across hosts.

Attachment	Size
Patient Discharge Summary Items.jrxml	6.43 KB

Re: How to get yesterday for history function

Submitted by gretalloyd on Tue, 29/11/2016 - 18:54.

Hello,

Sorry to continue with this, but the issue with this is back for us. I am not sure how long the issue has been back for, but it has just been brought to my attention today.

Was it something that was fixed in 1.8, but now we're on 1.9 it's gone back?

Also, is it possible to make it so the [macro:eval('@dischargesummary',openvpms:get(.,'patient.entity'))] is editable within the OpenOffice document? Currently we can't edit the medications that are in our discharge letters and if you try it just deletes the whole lot (the whole merged dischargesummary). Which makes it a bit difficult if the label instructions are cut off.

Also, is it possible to have [macro:eval('@dischargesummary',openvpms:get(.,'patient.entity'))] in OpenOffice only print items that have a label? For example, a dog might get Cephazolin injectable (medication) when it's in hospital as part of it's surgery, but the owner doesn't need to see this on the discharge letter. The owner really only needs to see items that have been dispensed and sent home with (all these items will have a label). I thought there was something in Jasper Reports that stipulated that only printed items that had label, but maybe I'm making that up.

Greta

Re: How to get yesterday for history function

Submitted by tanderson on Wed, 30/11/2016 - 12:18.

Your report macro may be incorrect, but I will contact you off list about it.

Regarding your other points:

to make the generated text editable in OpenOffice, you need to convert it from a field to text. To do this, you can:

1. Select the field and choose Edit - Cut.
2. Choose Edit - Paste Special.
3. Click "Unformatted text" in the Selection list, and then click OK

to only print items that have a label, you need to change the Patient Discharge Summary Items JasperReport. If you click on the Detail 1 band, it has a Print When Expression property, with value:

$F{target.displayName}.equals("Medication")

Change this to:

$F{target.displayName}.equals("Medication") && $F{target.label} != null

Re: How to get yesterday for history function

Submitted by gretalloyd on Wed, 30/11/2016 - 12:41.

Hi Tim,

Perfect! All of that has fixed it. Thanks so much.

Greta

How to get yesterday for history function

Comment viewing options