Project: Report Generator for EHR


(Tigpezeghe Rodrige Kwenchu) #6

@pri2si17 @teryhill @aethelwulffe I wish to know how we’ll display the draggable components in the report generator. I need you all to pour your ideas here. I need this to keep moving with coding.

This is what I’m thinking. The reports have particular tables and columns from which they’re generated. So we can sort those tables and columns, and create ‘table components’ which have ‘column draggable components’ which appear as dropdown list under the particular table. This is going to be lots of components to display. We’ll use tooltips to show more information for a particular column. This route is painful, but this is what I have for now.

Terry suggested we use the lists in the system to create the draggable components. In his method it’ll be easy to get the query that we’ll need to execute at the back. ‘Wise Gramps Terry’ explains that we’ll have draggable components like this:

  • Patient_Full :patient_data|fname|mname|lname

  • Patient_Last_first :patient_data|lname|fname

  • Patient_full_reverse :patient_data|lname|fname|mname

Patient_Full, Patient_Last_first and Patient_full_reverse will be the draggable components (and will appear as title in the list). patient_data is that table, that has columns fname,lname, mname. the user will choose the component in any order that suits them.

The lists in the system have fields, id, title, order, default, active which are mandatory. We’ll fill these fields accordingly. ‘The wise man’, @teryhill says we’ll add fields; notes, toggle_1 and toggle_2.

  • notes is something like this: patient_data|lname|fname|mname. This will give the field the generator needs to generate a report.
  • toggle_1 : sorts each column depending on the value of the checkbox
  • toggle_2: From my understanding, this will act the same as ‘ACTIVE’. Except Terry has another thing in mind.

I’ve tried to tell you how @teryhill and me are thinking. @pri2si17 and @aethelwulffe any ideas? You will understand these better if see the interface I’ve made for the generator.

(Priyanshu Sinha) #7

Hi @Trodrige,

  • The reports have particular tables and columns from which they’re generated.

What I think is majority of reports will use same column and fields with some modifications. Correct me if I’m wrong.

Now come to implementation, what I think is separate your generator in two parts :

  • Playground : This is area where you will bring your draggable components and arrange to generate report.
  • Toolbox : This contains all your draggable components.

First make a list of all common tools needed in report generation, and give them certain unique ids. There should be option for creating custom tools, and you will associate each custom tool generated with uuid. I don’t think this list is going to be long.

Now from database perspective, I would suggest to use a new table, as you are not mingling with current code base and this is your separate module, so this should have its own database/tables.

Now save data in table, for every value and link that with the user creating it, in another table. That’s how I think.

Note : I am not able to run your code as my internet services are down and using mobile hotspot.

I would like to have opinion of @teryhill @aethelwulffe @tony

(Tigpezeghe Rodrige Kwenchu) #8

@pri2si17 do you mean draggable components here? That is draggable components = common tools? If I get you clearly, you want that we use 2 databases, the main database you started already, and a report generator specific database.


  • The list of draggable components will be stored in the list_options table found in the main database.
  • The reports that are generated will get the data from the main database. This includes the user to whom a particular custom draggable component or report format is tied to. Of course, users exists in the main database.

(Priyanshu Sinha) #9

do you mean draggable components here? That is draggable components = common tools?


If I get you clearly, you want that we use 2 databases, the main database you started already, and a report generator specific database.

I would have to research about it, but I think it is a good practice to separate module wise database. Will let you know after some research.

(Tigpezeghe Rodrige Kwenchu) #10

This is the link to my 3rd blog post:

(Tigpezeghe Rodrige Kwenchu) #11

Link to my 4th blog post:

(Tigpezeghe Rodrige Kwenchu) #12

Link to my 5th post:

(Tigpezeghe Rodrige Kwenchu) #13

I’m writing this to get ideas on how we can create and display clauses (WHERE clause especially) for the report generator. @teryhill gave some suggestions, which he can better explain. His was close to mine. It would be great if @pri2si17 and @aethelwulffe can chip in something here. My idea about the WHERE clause:

  • We create a table to store the possible variable we can think of. We’ll add more entries when the users start using the tool. Some of these variables could be patient_name, appointment, provider, facility, age, gender, medical problems etc
  • On dragging components, we display a dropdown list of these WHERE and ORDERBY variables for the users to select after dragging and dropping components. For example, the user can select ‘patient name’ and enter the patient’s name. That is … WHERE patient_name = ‘Mr. Nice’.
  • Each of these variables are tied to a particular table and column. When any is used, the value is used to filter the results of the dragged components that referenced the same table. For example, a user drags the Patient_Fullname and Facility_Name components. He further specifies the ‘provider_name’ WHERE variable as provider_name=Mr. Kanus. The query selects all patient fullnames where provider=Mr. Kanus, and joins it to their respective Facilities.

I wish to have other views, and possible ideas you think we can go about this. I can elaborate on how I’m thinking if you don’t get the point.

I also wish that @teryhill contributes his ideas here, so we can come up with the best solution here. Thanks.

(Art Eaton) #14

This is going to be a bit of fun.

  1. A local store and a remote store of these would be nice.
  2. We should allow compound clause storage too, even if they are built up out of several clauses. Basically like burning a big logic circuit to a chip.

I’ll try to do up a “builder” demo piece without reference to anything else, then look at what you guys are thinking. I have done stuff like this before, basically drag-and-drop coding with icons for brackets and icons for functions out of a standard library.

I think that the key is to use the “visual code” approach. Basically that means that the user can think about object classes and other uninstanciated stuff, the user thinks about it at the instance level.
Kind of like the user can create a class called “toy” (top level parent object), an extended version of that called “ball” (parent object), and a non-abstract version called “red_ball”, but he is going to be thinking in terms of “red_ball_1”, “red_ball_2”, “green_ball_1” etc… so just draw the colored balls on the screen.

(Tigpezeghe Rodrige Kwenchu) #15

Hello @aethelwulffe, thanks for replying. I get the object and instance parts of what you said. What I don’t get is the local store, remote store, compound clause storage, and how we’ll use these (including the object-instance part) to provide users with the opportunity to specify WHERE clauses that we’ll use in querying our database.

Also, how do we use here? I’m sure @aethelwulffe has lots of cool, and fun stuff running in his mind.

Thanks, Tigpezeghe.

(Terry Hill) #16

for right now we just need to keep it within the scope of the project. Store the items like you are doing for the fields in the drag and drop. setting the basic where clauses and letting the enduser add to them.

(Tigpezeghe Rodrige Kwenchu) #17

Ok @teryhill this is clearer now. So we need a database table to store these fields to be used in the where clauses. That’s clear. So the users can add fields to this table like the can add new system features. I get this, but how will they specify the table columns as most users don’t understand how the database looks like.

(Priyanshu Sinha) #18


If I get you clear, then you probably want a way for end user to select what columns of lets say ‘X’ table they want in report. If wrong then correct me. I am providing suggestion using this assumption.

You will need to use map data structure. For eg : In table X you have, three columns ‘pat_name’, ‘pat_id’ and ‘pat_addr’. On UI you should display columns as (Checkbox) [1] Patient Name [2] Patient ID and [3]Patient Address. and in program/code you should implement this as map like :
$table_column_map = array("pat_name" => "Patient Name")

or $table_column_map = {}
$table_column_map['pat_name'] = "Patient Name"

This way, whatever user will select, that can be sent as POST request, and internally you can use your code display result according to corresponding table columns.

Hope I 'm clear. If my assumption is wrong, or anything you haven’t got, please let me know.

(Tigpezeghe Rodrige Kwenchu) #19

@pri2si17 we’ve passed this stage of the report generator. What I mean is providing an opportunity for a user to provide a where clause from the report main page. This will be used to reduce the number of entries for the generated report. However, this is similar to what Terry was describing, mapping possible fields that can be used in the where clause to the actual fields in the database. This is the same way we did for draggable components.

(Priyanshu Sinha) #20

I think this will cover it also. In either case you need to do mapping. @Trodrige

(Terry Hill) #21

The where clause should be for the tables that they have in the report. So the patient_data table would have fields in the where selection like lname,fname,DOB,zip code, sex etc.

When the table is being built the enduser will need a knowledge of the fields that are in the table they are working with. (Advanced User/Vendor)

(Tigpezeghe Rodrige Kwenchu) #22

@teryhill consider a user who drags and drops 4 components that point to 4 different tables in the database. Let’s say patient_data, appointments, drugs and addresses tables. He then specifies drug_name==‘Maalohx’ for the where clause.

  1. Does this mean the report(table) will be generated before the user specifies the where parameter to generate an accurate report?
  2. A report may be generated from more than one table at a time. How do we handle this?
  1. How do we achieve this?

(Terry Hill) #23

the user , for right now, would select the where clause based on the fields used. I do not think that there are any current users that will understand how to apply the logic of the where clause across the tables that are not included in the report.

(Terry Hill) #24
  1. the user will be an advanced user and will be familiar with PHPmyadmin or adminer for now

(Priyanshu Sinha) #25

Hello @teryhill @tony @aethelwulffe @Trodrige

What Rodrige want to implement is something similar to what we have right now (Reports->Clients->Patient List->Provider), and get all the reports. What he is trying to get is something different. What I get is, he wanted to generate report for all patient whose first name is XYZ or who resides in street Honolulu. @Trodrige correct me if I am wrong.

What he wants is from Admin perspective (To get report depending on various fields.) And what we have is more like Clinic perspective. So my question is, do we need report from Admin perspective? If yes then any reasons?

My understanding is he should generate report with clinic as end user. In that case, one clinic can see reports of patients associated with their clinic or others clinic.