Ability to generate a csv file as output for any queries under Patient List Creation or Clinical reports

[aethelwulffe]

Need to identify or create a universal .csv output function.
There are libraries that extend this stuff, like https://github.com/goodby/csv but that may not be the best solution. If we do not already have a js library that exports the HTML output to a csv, then I feel we should use our existing jquery and add this little asset here: https://github.com/rubo77/table2CSV

[rehoyt]

Whatever you think is best. In a perfect world (like the one we currently live in) I would want to search using several parameters such as age, gender, race, labs, diagnoses and meds and then create a downloadable report. For example, identify all male diabetics over age 50. The determine how many take an ACE inhibitor. Currently, students can query some parameters but then they have to manually count the results and would not allow manipulation in Excel

[aethelwulffe]

Excel is the microsoft version of a spreadsheet program, right?

Wouldn't simply dumping all the tables directly to ODB, MySQL, SQL or Access (I think that is what the microsoft spreadsheet uses for data sources) allow people to use those outside tools better than scraping data out of a report and hoping what you need is in the standard report? If they are dealing with complex data, they need complex tools to answer complex -and highly variable questions. Better to have all the actual data imported into the tool you are really using.

The specific report you are looking for in the above example is already an NQF, which we will have with the mips stuff if we get that merged. It already gives you the patient lists and all that stuff as well.

Personally, in the instances where someone wants data for use with a spreadsheet, my go-to is either an export direct to the format needed after running a query through phpmyadmin, or just a select-paste from the screen into the spreadsheet document. I can understand wanting nice download buttons for those folks that don't readily know how to right-click for a context menu, but in the educational aspect using just the EHR for data manipulation, it seems like integrating a real, open-ended database UI, or a purpose built report tool. Been following these guys this summer: https://medium.com/@manishbisht/week-10-google-summer-of-code-17-with-phpmyadmin-6bf6fb791b49

Speaking of which, I am very interested in the jqplot.js charting tools in phpmyadmin. https://docs.phpmyadmin.net/en/latest/charts.html I think we ought to get some of that cool jqplot stuff for ourselves.

[aethelwulffe]

What this means first and foremost is that we need to put in a good CSV etc... file exporter, and a good grapher tool, then rip out the dozen or so others floating around.

[rehoyt]

I thought @robbyoconnor would not grant people access to phpmyadmin for security reasons. I am trying to get instructors to use SQL queries to examine data but that is somewhat of a stretch. Most are good with spreadsheet manipulation and that is partly why I thought a csv download was reasonable. In addition, Tableau is very popular in healthcare so they might use it for visualization

[aethelwulffe]

That is on the "official" demo and such. Understandable. Things like the ability to manipulate a database are not things you want just anyone to do. However: Security in general was the first reason given for this, and I didn't find any particular current security holes published. Second, you don't have to give root access to every user utilizing phpMyAdmin. In fact, you can restrict the user as to what database they can see, and even what functions they can run. You can restrict them to VIEW only. In that case, they can't make any changes to the data. That is a much lower access than a user of the EHR already has.

Clinical view via an EHR in an infomatics setting is a very important training tool. Heavy weight infomatics demands a full-scale tool, and extensive documentation of the database, and having the "standard" reports in the EHR available to compare results between their home-grown queries and the fully vetted ones in the EHR is a great reality check, possibly one that goes both ways.

There are lots of data viewing and reporting tools. PHPMyAdmin, as far as a fully web-based (accessible for training) platform that is full featured and does not cost big bucks per-user has no significant competition. The designer modes (which has extra install options), the report builder and visualization and graphing tools are all features that folks that "don't like it" have never really even looked at.

[robbyoconnor]

Yeah -- I don't care if you wanna set up a server somewhere and run hogwild and just turn it into a honeypot...my responsibility ends when it's not "official" -- that said phpmyadmin should not be needed -- build a proper reporting framework.

[robbyoconnor]

to add on: That reporting framework should and can allow for raw (controlled) queries to the DB -- so it would have all of the power without the huge gaping security hole.

[tmccormi]

We are using something for that in all my new apps. I'll have to as Ken what it is. I think it's datatables (like we already use) but not sure.

[image: Inline image 1]

Tony McCormick

On Aug 10, 2017 10:08 AM, "Art Eaton" notifications@github.com wrote:

Need to identify or create a universal .csv output function. There are libraries that extend this stuff, like https://github.com/goodby/csv but that may not be the best solution. If we do not already have a js library that exports the HTML output to a csv, then I feel we should use our existing jquery and add this little asset here: https://github.com/rubo77/table2CSV

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/660#issuecomment-321614407, or mute the thread https://github.com/notifications/unsubscribe-auth/AARci2jJaih5hwMyKv2y_3M-wLHswsNSks5sWzkrgaJpZM4OuikZ .

-- Please be aware that e-mail communication can be intercepted in transmission or misdirected. Please consider communicating any sensitive information by telephone. The information contained in this message may be privileged and confidential. If you are NOT the intended recipient, please notify the sender immediately with a copy to hipaa-security@mrsb-ltd.com and destroy this message.

[rehoyt]

Keep in mind that I created an alternate method to analyze all 125 NHANES csv files (not just the 12 @yehster used to populate the EHR. The Data World site has SQL integrated so they can perform SQL queries to their heart's content

[aethelwulffe]

Define (calmly) gaping hole.
Better yet, just using the URL, hijack the system specifically by using the phpmyadmin install that exists there. That will enlighten us all. In the meantime, you are correct, this does not, and will not have anything to do with the LH servers.

In the meantime, we are talking about two things.

1. Single install site maintenance. Mom and pop poor folks (many with MS, MA, and PHD and other random letters after their name) must have tools to maintain the systems. You can look down at GUI applications for things generally thought of as the realm of the UberNerd, but these folks must have tools that both teach as well as enable them to fix serious disasters, or merely make large-scale corrections to data without some poor SOB needing to sit down with reams of paper and checklists updating one record at a time.

2. The education part. This clearly applies to the above case as well. The primary idea here is that to learn to work databases, you need to work with databases...before you learn how to work with databases. It sounds pretty bad, but that is what this class of tool does for you. You can start off with typical searches. These searches then appear as queries. Then you string together those queries. Then you finally build something really cool, and you get to store that. Then, amongst other wonderful labor-saving things in these toolsets, you finally get to export the queries into PHP code...which you use as your learning curve flattens...and so on. PHPMyAdmin, in combination with a complex real-world applied data scheme (and the desperate need to get things fixed, or work efficiently so that no-one starves and the lights go out) supplies an amazingly efficient scenario for turning out useful experience and knowledge. This is the combination that allowed me to go from building wooden boats to being the IT support for a whole pack of free and medicaid-level public service type medical and mental health clinics. That wonderful FOSS combination resulted in tens of thousands of people receiving services, and a couple of hundred people that have jobs that would not, and could not have existed otherwise. Those two pieces of software, and a bored boatwright. Proof in the pudding dude. Have the references and everything.

So yeah, as educational tools, I think that combo is about the best thing since unsliced hot fresh Cuban bread and Gevalia light roast in the morning..

[robbyoconnor]

I mean that phpmyadmin gives far too much access. Installing PHPMyAdmin on a single server in a practice is fine...but not on a demo server. Including it in the distribution for example fine. If you wanna bundle it with the EHR download -- that's fine...but on the not on the demo server.

[teryhill]

Yes that would give access to all the Demo data.

Sent from my iPhone

On Aug 10, 2017, at 11:41 PM, robbyoconnor notifications@github.com wrote:

I mean that phpmyadmin gives far too much access. Installing PHPMyAdmin on a single server in a practice is fine...but not on a demo server. Including it in the distribution for example fine. If you wanna bundle it with the EHR download -- that's fine...but on the not on the demo server.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[robbyoconnor]

And what if phpmyadmin has a vulnerability? It's a piece of software which we have no control over. I'm not keen on giving students free reign over a demo server...they should be working off of something local...

[aethelwulffe]

Heh! If anything we use is easily cracked, then we really really ought to know about it. I am totally with you that the official demo server needs to be as secure and stable (meaning no-one ever wakes up in the morning to a Guy Fawkes mask on the log-in screen), and should not be a general use tool for anyone's pet project. If we are running a conference/seminar demo, doing sloppy dev work, checking out 'What-If' branches and sharing them...all with no PHI of course, well, that is a job for machines set up just for that purpose and no other jobs! Not even on the same network as anything that needs protection. It doesn't matter how secure it may be, anything extra that does not do a job is useless and a potential risk.

So, no, if the folks administering the demo server don't need such a tool, it should not be there. The demo is so folks can see the system operating. It isn't really even for training so much.

I also find value in the honey pot idea frankly. Let's face it. Until someone breaks something that is weak, we may not find that weakness. This goes for the casual black-hat right down to the clueless user that is banging on a keyboard. We need servers with LibreHealth stuff popping up all over the place, hosting all kinds of useful outreach, educational and training sorts of opportunities. If someone pulls a Stuxnet trick on some old hard drive, well lesson learned. In the meantime, having burner machines that folks can put their unreviewed branches that require migration or configuration to be tested keeps other people from having to jump through all the hoops of doing the same on their local machine every time they want to do a review. Seems like it would speed development up a lot.

My final point: In pursuit of these sorts of goals we need to avoid talking negatively about another very mature and useful product. We should not be telling folks like Dr. Hoyt "Don't use that! It's insecure!". Let's make sure we keep our advice in proper context. These folks often listen to the "Experts" more carefully than the experts are speaking.

-yes, @teryhill Very funny. If they hack it, they might steal our latest demo data. Can't be having all that industrial espionage.

[robbyoconnor]

I am not opposed to burner machines -- those are fine. We have in the past set up temporary demo machines for conferences. If @aethelwulffe or someone wants to host a demo of code that's not merged into the master branch due to an incomplete code review -- and I'm not responsible for it -- that's fine. Do whatever you want -- the honeypot comment was uncalled for, sorry -- not sure what you have running as I didn't look. Anyways that's all!