OpenEMR 6.0 Billing Manager "date based queries" not working

Billing Manager “date base queries” not working

Upgraded OpenEMR 5.0.2 to 6.0.0

  • PHP 7.4.3
  • MySQL 8.0.23-0
  • Firefox 85.0
  • Ubuntu 20.04

Billing queries seem to work for “non-date” Criteria such as Patient Name, Encounter, etc …

The default query of “Date of Service = Today” returns many many encounters in chronological order beginning with 2013-06-17. Other date base queries “Date of Service” and “Date of Billing” yield different results also in chronological order starting at other dates such as 2013-06-07 …

Cherry picking the most relevant items from the apache2/error.log just after a date base search …

  • PHP Notice: Undefined index: query_drop_down_master_billing_x12_partner_id
    in /var/www/html/openemr/interface/reports/ on line 128

  • PHP Notice: Undefined variable: returnval
    in /var/www/html/openemr/library/ on line 309

  • PHP Notice: Trying to access array offset on value of type null
    in /var/www/html/openemr/library/ on line 315

  • PHP Notice: Undefined index: ICD9
    in /var/www/html/openemr/interface/billing/billing_report.php on line 1266

I had issues upgrading the database from OpenEMR 5.0.2 to 6.0.0 documented here …

Several “dates” in the mysql database are of value “zero” rather than NULL, I have cleaned some of these up with no effect on the search, other than the erroneous date not being displayed e.g. -0001-11-31 …

Any guidance where to debug my problem would be appreciated. I expect that it has something to do with the funky database upgrade, but I’m not sure where to start …

Thank you ~ Thomas E. Shepherd

hi @ThomasShepherd, it’s an existing issue and has recently been addressed.

Could this be related to my problem?
When I update a document date, the database gets: “0000-00-00 time” and my document gets: 00/00/0000.
Please point me in the correct direction. TIA

may have jumped the gun on that first reply with the cool new billing link

sounds like you had a hiccough in the upgrade, is it possible to redo the upgrade?

1 Like

Hello Stephen,

Yes ~ there were many, issues with my initial “practice” upgrade and database import.

But the “for-real” upgrade went more smoothly ( by memory ) - other than getting Apache TSL/SSL to play nice with OpenEMR. Unfortunately, my day job has been intruding on my discretionary time and I may not have an opportunity to attempt a reinstall until mid next week.

Thank you for responding, and I will keep you posted on my progress …

Tom Shepherd

1 Like

Hello Sandra Gutierrez,

There are many “datetimes” in my database that are empty rather than NULL. These behave as though they have a value of zero ( “0” ) and display weirdly in different places in OpenEMR. I have encountered -0000-11-31 and Unix Epoch 12-31-1969. I’m not sure that I have seen 0000-00-00 though.

I will let you know if I have any better insight into the issue …

Tom Shepherd

1 Like

hi @gutiersa, this sounds like a bug and while related to this thread my bet is that it’s a separate issue with the form not pulling the correct format from globals.

1 Like

That may be true but, I think there is a larger issue with past date formats, the darn 0000-00-00 placeholder that never worked in a table date field as sometimes it’d be empty or 0 resulting in if date range queries, unexpected results.
We may be able to fix in our query setup from Billing Manager. Don’t know but, will look!

1 Like

Hello All,

The upgrade ‘redo’ has the same issues as before …

I believe that the problem is that I am attempting to upgrade from 5.0.0, database version identifier ‘206’ and it is not playing nice with the current sql_upgrade.php … ?

It appears that the ‘keys’ table should be added by line 709 of 5_0_1-to-5_0_2_upgrade.sql

But I still get a bunch of errors

  • ERROR: query failed: SELECT value FROM keys WHERE name = ?

during the 5.0.0 to 5.0.1 sql upgrade … ?

So it appears that the current upgrade logic already anticipates my OpenEMR version to be 5.0.2 even though the process implies that I can upgrade from an earlier version …

So do I need to walk the upgrades … ?

  1. make a 5.0.1 installation
  2. upgrade from 5.0.0 to 5.0.1
  3. make a 5.0.2 installation
  4. upgrade from 5.0.1 to 5.0.2
  5. make a 6.0.0 installation
  6. upgrade from 5.0.2 to 6.0.0

Or is there a way to appropriately call the upgrade sql files sequentially outside of the OpenEMR installations … ?

Thanks for any guidance … : )

Tom Shepherd

Hi @ThomasShepherd,
You should not need to do anything however the 5.0.0 to 5.0.1 was a major database change. I’m reviewing now and will get back to you.

Hello Jerry ~ Thank you … : )

Hi @ThomasShepherd
I found a problem, at least for upgrades from prior to v5.0.1, so if you can patch your source files then replace library/ajax/sql_server_status.php with attached.

sql_server_status.php (2.0 KB)

You can try rerunning the sql_upgrade script from v5.0.0 to see how it goes then perhaps from scratch/prior database if need be.

Hello Jerry Padgett,

I’m well into my 5_0_2-to-6_0_0 sql upgrade with no obvious errors yet,
e.g. 5.0.0-to-5.0.1 and 5.0.1-to-5.0.2 have run to completion … : )

Thank you … !

I am working on my “practice machine” with the original database dump from a couple of weeks ago. Assuming that this works, can you recommend an approach to access the updates to the “real” installation in my wife’s practice … ?

It is a very small practice - more of a passion project for my wife - so there should not have been that many patient encounters and billing changes that need to be addressed.

This is my burden to fix, and thank you for solving the root problem, but if there is a preferred approach to fixing the “problematic database”, I would appreciate your guidance.

The two obvious approaches are to

  1. attempt the “fixed” 5.0.0 to 6.0.0 upgrade on a dump of the current problematic database
    ( I will try this first but am not sure that it is feasible )
  2. start with a clean upgrade of the 5.0.0 dump from two weeks ago and attempt to re-enter the patient encounters and billing activity
    ( this is what I expect I will end up doing )
  3. ?

Thank you again for you help,

Tom Shepherd

Tom I went from a 4.2.2 test database I keep around all the way through v6. Granted the database is small with only a couple patient/encounters etc but, no issues.
I do know of at least one production site going from < v5.0 to v6 with a minor issue which he fixed in existing upgrade code.
I think you should be okay just doing the upgrade on your production database. Remember though, garbage in, garbage out i.e don’t count on upgrade fixing any existing data corruption issues if you have any.
For the billing dates, perhaps an error free upgrade may sort that issue out because having upgrade interrupted by the previous error could yield unexpected results.

Sorry for the issue as that was my bad for not testing from prior to 5.0.1 upgrade when I added the status pane. psst, don’t tell anyone it was me!:slight_smile:

1 Like

I’m seeing the exact same issue in the Billing Manager with “date based queries” after upgrading to 6.0.0.

Can this be fixed in the query setup code in the Billing Manager ? How can I see what query is being sent to the database?


hi @DONMNELL, they’ll be in the first patch, if you need them before then look here

here’s the latest

Thanks Stephen.

I upgraded from 5.0.1(7) to 6.0.0 in hopes of resolving a sluggish performance issue ( which it did amazingly well ) but then noticed the “date based query” issue.

Glad we have a fix for it.

1 Like

Ya don’t know how that heartens me!:slightly_smiling_face:

1 Like

Ya don’t know how that heartens me!:slightly_smiling_face:

Well, if it’s anything like the tears of joy my staff and I felt getting our responsive EMR system back, then I have a fairly good idea. :partying_face:

Now I don’t have to worry about reverting back to a 4.22 backup, getting an SSD (which we may do anyway but not really needed now), migrating to Linux from Windows or just “living with it”.

Thanks so much for your help in making that happen.