Patient Documents and PHP-Smarty

OpenEMR Development
1

cfapress wrote on Monday, June 08, 2009:

My head is about to explode. The level of obfuscation in the PHP code surrounding patient documents is horrendous. It’s taken me about 90 minutes to get this unique-ID problem sorted out.

Here’s what I’m trying to do. Our Agency is beginning to scan thousands of old patient charts as PDF files. I thought this would be a great opportunity to merge this paper data with OpenEMR records. All of the patient’s are already in OpenEMR so it should be pretty easy to automate.

I have a simple mechanism that lets a human-user match the PDF document to an OpenEMR record.

  The hard part is attaching that document to a patient in the OpenEMR database.

Yeah, it should be simple. And it would be except for the fact the ‘id’ column in the ‘documents’ table does not auto-increment. In fact, the value of that field is based upon the value  of the ‘id’ column in the table ‘sequences’. WTF?!

Now get this… Other tables are using the same column for their unique ID in their own tables. I see similar code around Notes, Insurance Company, and Addresses. There must be a few others. Wow.

I’m not sure how this method for coming up with a unique ID value came about but I’d love to have a discussion with whomever came up with it. I’m having a hard time rationalizing the purpose of such a complex system for creating a unique ID.

Anyway, enough ranting.

Just in case anyone else runs into a problem like this here is the solution that I’m using:

1) Increment the value in the ‘id’ column of the ‘sequences’ table
2) Assign that new ID value to the document being imported

This way, I’m not breaking the use of the ‘sequences’ ID uniqueness.

Very messy. Yuck!
Jason

2

omo66 wrote on Tuesday, June 09, 2009:

Hi Jason,
I found this complex as well, I gave up on it last week.
Could you help me since this subject is now open.
I need to attach a  pdf (made with ezPDF) automatically into the chart e.g progress notes folder of documents categories.
what is the URL or the pieces of functions to to be used?
Auto-naming a pdf output was very difficult, I had to save it first inside a folder with a descriptive name  then a second script will download it again, is there any trick for that? ezPDF manual was not good enough.
Thanks
 

3

markleeds wrote on Tuesday, June 09, 2009:

Is this like the encounter id generating code? The thing that someone made with adodb probably meaning to become database independent?

I don’t have much experience with other databases.  Are there some that don’t have auto-increment? I gave up on chasing that one and just accept the encounter id as being unique for a given pid.

4

cfapress wrote on Tuesday, June 09, 2009:

Here is a brief version of the code I have created to add a PDF file to a patient without using OpenEMR code:

====================================
$oldfilename = "the_original_file.pdf";
$newfilename = "the_new_file.pdf";

$oemr_pid = 1234; // value found in the pid column in the patient_data table

// connect to database
$db_username = "test";
$db_password = "test";
mysql_connect("localhost", $db_username, $db_password) or
    die("Could not connect: " . mysql_error());
mysql_select_db("openemr");

// directories of where to find the file and
// where to store the file in OpenEMR’s folder structure
$dir = “./files/pending/”;
$newdir = “/home/www/openemr/documents/”.$oemr_pid."/";

// increment and retrieve the next ID for the new document
$sqlstmt = "update sequences set id=LAST_INSERT_ID(id+1)";
$sqlstmt = "select id from sequences";
$result = mysql_query($sqlstmt);
$seqResult = mysql_fetch_assoc($result);

// add the document to the patient
$sqlstmt = “insert into documents (”.
            "id, type, size, date, url, mimetype ".
            ", pages, owner, revision, foreign_id ".
            “, docdate, list_id “.
            “) VALUES (”.
            $seqResult[‘id’].
            “, ‘file_url’”.
            “, “.filesize($dir.$oldfilename).
            “, '”.date(“Y-m-d”, time()).”’”.
            “, 'file://”.$newdir.$newfilename.”’”.
            “, ‘application/pdf’”.
            “, null”.
            “, null”.
            “, null”.
            “, “.$oemr_pid.
            “, '”.date(“Y-m-d”, time()).”’”.
            “, 0”.
            “)”;
$result = mysql_query($sqlstmt);

// assign the document to a category
$catID = 3; // ID found in categories table
$sqlstmt = “insert into categories_to_documents (”.
            "category_id, document_id ".
            “) VALUES (”.
            $catID.
            ", ".$seqResult[‘id’].
            “)”;
$result = mysql_query($sqlstmt);

// now copy the file into the OpenEMR folder structure
if (! is_dir($newdir)) { mkdir ($newdir); }
copy($dir.$oldfilename, $newdir.$newfilename)

====================================

The code I just included is missing error-checks and a few other nice things. But it’s enough to give you an idea of what I did.

As for the Encounter-ID generation, that is an auto-increment column. The code for adding a new encounter can be found in
<oemr>/interface/forms/newpatient/save.php

After my first post I had to do more hunting to figure out exactly what GenID in ADODB did so that I wouldn’t break anything. Indeed it’s a method to get around the fact that some databases do not have an auto-increment column type. I suppose this is just a hold-over from ancient OpenEMR times when the database choice was not so tightly married to the code.

Jason

5

omo66 wrote on Wednesday, June 10, 2009:

Jason,
In your code above, what is this variable  ‘file_url’
It was inside insert values.
Is this a PHP variable?
Thank you

6

cfapress wrote on Wednesday, June 10, 2009:

Hi Omar,

file_url is the ‘type’ of document. Other values for that field can be ‘blob’ and ‘web_url’. Since I’m dealing with actual files stored on the server the proper choice is ‘file_url’.

Jason