Coding An Electronic EEG Logbook [Part 2]

Welcome to Part 2 of “Coding An Electronic EEG Logbook.” In this post, I will outline the dashboard, email notifications, my main SQL table structure, and adding ICD-10 codes.

Dashboard

For my dashboard I used Highcharts. Here is an example of ~6 months of data entered for 2018 displaying the volume of patients from each unit. I have two separate files, one to display the data, and one to query the database.

Highcharts PHP/HTML displaying the chart:

[pastacode lang=”php” manual=”%3C!DOCTYPE%20HTML%3E%0A%3Chtml%3E%0A%3Chead%3E%0A%3Cmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3Dutf-8%22%3E%0A%3Ctitle%3EUnit%20Volume%202018%3C%2Ftitle%3E%0A%0A%3Cscript%20type%3D%22text%2Fjavascript%22%20src%3D%22code%2Fhighcharts.js%22%20%3E%3C%2Fscript%3E%0A%0A%3Cscript%20src%3D%22http%3A%2F%2Fajax.googleapis.com%2Fajax%2Flibs%2Fjquery%2F1.8.1%2Fjquery.min.js%22%3E%3C%2Fscript%3E%0A%0A%3Cscript%20type%3D%22text%2Fjavascript%22%3E%0A%24(document).ready(function()%20%7B%0Avar%20options%20%3D%20%7B%0Achart%3A%20%7B%0ArenderTo%3A%20’container’%2C%0AplotBackgroundColor%3A%20null%2C%0AplotBorderWidth%3A%20null%2C%0AplotShadow%3A%20false%0A%7D%2C%0Atitle%3A%20%7B%0Atext%3A%20’Unit%20Volume%202018’%0A%7D%2C%0A%20%20%20%20xAxis%3A%20%7B%0A%20%20%20%20%20%20%20%20type%3A%20’category’%2C%0A%20%20%20%20%20%20%20%20labels%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20rotation%3A%20-45%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20style%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20fontSize%3A%20’13px’%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20fontFamily%3A%20’Verdana%2C%20sans-serif’%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%2C%0A%20%20%20%20yAxis%3A%20%7B%0A%20%20%20%20%20%20%20%20min%3A%200%2C%0A%20%20%20%20%20%20%20%20title%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20text%3A%20’Volume’%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%2C%0Atooltip%3A%20%7B%0Aformatter%3A%20function()%20%7B%0Areturn%20’%3Cb%3E’%2B%20this.point.name%20%2B’%3C%2Fb%3E%3A%20’%2B%20this.y%20%2B’%20’%3B%0A%7D%0A%7D%2C%0AplotOptions%3A%20%7B%0Abar%3A%20%7B%0AallowPointSelect%3A%20true%2C%0Acursor%3A%20’pointer’%2C%0AdataLabels%3A%20%7B%0Aenabled%3A%20true%2C%0Acolor%3A%20’%23000000’%2C%0AconnectorColor%3A%20’%23000000’%2C%0Aformatter%3A%20function()%20%7B%0Areturn%20’%3Cb%3E’%2B%20this.point.name%20%2B’%3C%2Fb%3E%3A%20’%2B%20this.y%20%2B’%20’%3B%0A%7D%0A%7D%0A%7D%0A%7D%2C%0Aseries%3A%20%5B%7B%0Atype%3A%20’column’%2C%0AdataLabels%3A%20%7B%0Aenabled%3A%20true%2C%7D%2C%0AcolorByPoint%3A%20true%2C%0Aname%3A%20’Unit%20Volume’%2C%0Adata%3A%20%5B%5D%0A%7D%5D%0A%7D%0A%20%0A%24.getJSON(%22unit_data2018.php%22%2C%20function(json)%20%7B%0Aoptions.series%5B0%5D.data%20%3D%20json%3B%0Achart%20%3D%20new%20Highcharts.Chart(options)%3B%0A%7D)%3B%0A%20%0A%20%0A%20%0A%7D)%3B%0A%3C%2Fscript%3E%0A%3C%2Fhead%3E%0A%3Cbody%3E%0A%3Cdiv%20id%3D%22container%22%20style%3D%22min-width%3A%20400px%3B%20height%3A%20400px%3B%20margin%3A%200%20auto%22%3E%3C%2Fdiv%3E%0A%3C%2Fbody%3E%0A%3C%2Fhtml%3E” message=”” highlight=”” provider=”manual”/]

Highcharts PHP data file to run the MYSQL query (See the SQL query highlighted):

[pastacode lang=”php” manual=”%3C%3Fphp%20%0A%24con%20%3D%20mysql_connect(%22localhost%22%2C%22username%22%2C%22password%22)%3B%20%0Aif%20(!%24con)%20%0A%09%7B%20%0A%09die(‘Could%20not%20connect%3A%20’%20.%20mysql_error())%3B%20%0A%09%7D%20%0A%09%0Amysql_select_db(%22mate2%22%2C%20%24con)%3B%20%0A%0A%24result%20%3D%20mysql_query(%0A%22SELECT%20%20COUNT(*)%20location_count%2C%20b.unit%0AFROM%20%20%20%20eeg_reading%20a%0AINNER%20JOIN%20location_table%20b%0AON%20a.location_unit%20%3D%20b.id%0AWHERE%20YEAR(date_recorded)%3D2018%0AGROUP%20BY%20b.unit%0A%22%0A)%3B%20%0A%0A%2F%2F%20while(%24row%20%3D%20mysql_fetch_array(%24result))%20%0A%09%2F%2F%20%7B%20%0A%09%2F%2F%20echo%20%09%24row%5B’reading_md’%5D%20.%20%22%5Ct%22%20.%20%0A%09%09%09%2F%2F%20%24row%5B’reading_md_count’%5D.%20%22%5Cn%22%3B%20%0A%09%2F%2F%20%7D%20%0A%09%0A%20%0A%24rows%20%3D%20array()%3B%0Awhile(%24r%20%3D%20mysql_fetch_array(%24result))%20%7B%0A%24row%5B0%5D%20%3D%20%24r%5B1%5D%3B%0A%24row%5B1%5D%20%3D%20%24r%5B0%5D%3B%0Aarray_push(%24rows%2C%24row)%3B%0A%09%7D%0Aprint%20json_encode(%24rows%2C%20JSON_NUMERIC_CHECK)%3B%0A%09%0Amysql_close(%24con)%3B%20%0A%3F%3E” message=”” highlight=”11-17″ provider=”manual”/]

Example JSON output:

[[” Program Admit”,466],[“Clinic EEG”,719],[“Clinic JPB PT EEG”,50],[“EEG INPT”,360],[“EEG Other (ISAT\/PET)”,24],[“EEG Program Admit”,318],[“EEG UH 4920”,244],[“EEG UH ICU”,485],[“INPT 5000”,828],[“INPT EEG”,55],[“INPT EEG H8000”,591],[“INPT EEG ICU”,927],[“INPT EEG PICU”,244]]

HTML/JQUERY output column chart displaying unit volume:

Automatic email notifications 

Now that you have data coming into your database, you can find scripts online or write your own that will simply query your database with an SQL statement and then email you or others with the results. This can be helpful for daily/weekly/monthly reports. I used the following script to query my database and send monthly results to our accounting team.

Here is a Github link to an example script I used. You will need to set up a CRON job on your webserver. No relation to StudiOwens but I do like the name.

https://github.com/StudiOwens/email-mysql-csv

MySql database structure for the EEG Logbook table

You should start off with a unique id for every row, (e.g. id). For the ‘id’ row make it an integer that is set so AUTO_INCREMENT, this will assign it a new unique id number for every entry in the database.

For any file data you wish to upload, like pictures or Word documents, make it a BLOB. In my database I have one medium blob called file_data, this is for uploading the EEG reports.

Adding ICD-10 codes to your database

You can easily download all of the ICD-10 diagnosis codes in a text file from the CMS website here.

Extract and open this file in any text editor like Textwrangler for Mac or Notepad++ for Windows and you will see the entire list. I extracted only the pertinent epilepsy codes from the list and imported them into my SQL database. I use PhpMyAdmin to import and administer my MySQL database.

Example of the ICD-10 codes in a drop-down from our MEG/MSI database:

Stay tuned for Part 3, I’ll upload a demo with anonymous data and talk about putting the database into the cloud for ease of access with secure authentication and HIPAA compliance.