Read about OpenEMR's Response to the COVID-19 Pandemic at https://www.open-emr.org/covid19/

Patient List Creation Reports Issues

Hello,
I found out that the date restriction on Reports >> Clients >> Patients LIst Creation; does not restrict to specific dates during pull out.
i.e you submit a request for a pull out for Jan 1, 2020 to Jan 27, 2020.
Outcome: it does pull out for dates both within the specified dates and before the specified dates.

I am using openemr 5.0.1(7)

@timothy1,
Currently for “Demographics” option, date filter is not applied. To make it work, include the highlighted part(refer screenshot) in the file interface/reports/patient_list_creation.php.

Kindly share your views.

Thanks,
ViSolve-120

@visolveemr,
Thanks for your response.
Please what is the specific line in the file will it be posted.
Thanks

@timothy1,
File name : interface/reports/patient_list_creation.php
Line No : 366(Approximately)
Copy and paste the below lines after communication (refer screenshot specified in the above thread)

case “Demographics”:
$whr_stmt=$whr_stmt." AND pd.date >= ? AND pd.date < DATE_ADD(?, INTERVAL 1 DAY) AND pd.date <= ?";
array_push($sqlBindArray, $sql_date_from, $sql_date_to, date(“Y-m-d H:i:s”));
break;

Kindly check and let us know if you have further clarifications.

Thanks,
ViSolve-120

@visolveemr,
I have copied and pasted it, however on clicking on patient list creation there is no display.
It is blank.
A cursory look at the file, from ln 325, specifies the sql joint statement which is not available for demographics.
I may be wrong.
what can be done?

Hello,
@visolveemr,
Can this be solved, any way out?

@timothy1
• Click on Reports >> Clients >> Patients List Creation
• Initially the page will be blank, and on clicking submit button the data will be filtered out.
• The filter option is not applied for demographics. To include it try to replace the file attached below
patient_list_creation.php (38.0 KB)

Still if you find any issues, provide us the detailed steps.

Thanks,
ViSolve-120

Hi,
No more issues.
It worked. thanks @visolveemr.

Hi @visolveemr,
Thanks for your contribution and effort the other day.
There is a new issue. I found out that when we do report pull out there are some patients that are missing.
However on deleting the line of code you gave above, those patients will appear and the number of patients that will be captured in the report pull out will be higher.
What could the issue be and

What can be done?

Thanks.

Tim,
Thanks for the compliments and sorry to hear you are facing new challenges.
We love to support you now but are busy with live customers. We will get back to this when we have some breathing time :-)! assuming no one else responded to your question(s).
-Visolve-899

Thanks for your response.
I will be waiting …

Hello,
This issue still persist and remains unsolved. This could be beneficial to others as it concerns reports.
Please what can be done.
Thanks

@visolveemr
Hello how are you?
Please you said you were to help out concerning the patient list report. This is so because the problem still persist.
Thanks in anticipation

I’m looking for the information how to update the patients list, to be able to see “Days from last Encounter”
Tried to add “Days from last Encounter” to patients list but its not returning any info
In EMR 4.2.2. version, this field was included in patient list, and it was very easy way to get information for patients recall.

You mean this screen?

Or this screen?

It doesn’t look like either is an option in the layout editor.

If the latter option, looks like you’d need to add a new column to the file:
main/finder/dynamic_finder.php

I’m not much of a front-end person but I can speak to the SQL and PHP you’d need.

So the default table you’re seeing appears to only pull data from a few columns in one table in the database. patient_data
Which is convenient since you can get all the columns and included data you with a single query and just display what columns you need.

To get time since last encounter you’ll need to run another query on a different table(form_encounter) for each patient displayed.
So for all patients listed you’d need the patient id(e.g 2) field to pass into the below query.

SELECT MAX(date) FROM form_encounter WHERE pid = 2; Which returns a list of the dates of all encounters if you remove the MAX() surrounding date. With it present you just get the most recent date

Then you can use PHP to calculate distance from the present date.

See method 3:

One potential hiccup with this method is that OpenEMR allows you to set the date of an encounter to be in the future. So it is possible to get a negative number.

Hope this helps,
-Rachel

Going back and actually reading earlier in the thread, looks like you’re talking about /interface/reports/patient_list_creation.php

Accessible via the GUI by going to Reports -> clients -> Patient List creation.

Gave it a quick read, check out line ~500.
We may be able to grab the list of patient ids to query on in one of the below.

while ($row = sqlFetchArray($result)) {
$patArr[] = $row[‘patient_id’];
$patInfoArr = array();
$patInfoArr[‘patient_id’] = $row[‘patient_id’];

Scroll to the bottom of while loop. line ~546
We’ll probably want to append our new column here.
$patFinalDataArr[] = $patInfoArr;

Since I’m too lazy to spend the time to actually figure out what is in each of these arrays by reading the code I just put a bunch of echo and print_r statements inside and after the end of the while loop but before the ?> indicating the end of this chunk of php code.
https://www.php.net/manual/en/function.print-r.php

Then going back to the patients list creation screen, filling out some junk data and hitting submit


The highlighted comes from the below line of code.
print_r($patArr) . nl2br("\n \n");

Looks like the $patArr array grows with every iteration of the while loop. So in theory if we grab this variable just after the end of the while loop it should give us an array of all the PIDs, highlighted below, we need.

So to get from a php array to a list compatible with the IN operator, looks like we’ll want to use the php .implode functionality.

Might want to note that the pid field is a bigint(20), not sure if this will matter later on.


Let’s try:
$sql = “SELECT MAX(date) FROM form_encounter WHERE pid IN (”.implode(’,’,$patArr).")";

It failed with the below error.

When I run:
SELECT MAX(date) FROM form_encounter WHERE pid IN (3,4,6)
it in the SQL tab in phpmyadmin it returns null.

Seems like this should work based on what I read on stackoverflow. Might this be a SQL versioning issue? Can someone a bit more knowledgeable about MariaDB chime in here? @brady.miller

Thank You Rachel for your advise

We had this option on 3.2.0. version, and this column was lost after upgrade.
I even tried to temporary reinstall test comp to 3.2.0. to fund out command in Lists and Layouts.

I’m just switched from Windows based EMR to Linux Mint, and don’t know if its will be same installation process like 5.0.1.