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/criteria.tab.php on line 128
PHP Notice: Undefined variable: returnval
in /var/www/html/openemr/library/patient.inc on line 309
PHP Notice: Trying to access array offset on value of type null
in /var/www/html/openemr/library/patient.inc 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 …
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
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 …
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 …
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.
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!
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 … ?
make a 5.0.1 installation
upgrade from 5.0.0 to 5.0.1
make a 5.0.2 installation
upgrade from 5.0.1 to 5.0.2
make a 6.0.0 installation
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 … ?
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.
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.
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
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 )
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 )
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!
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.
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.
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.
