Clinical report throws SQL error with some options.

[htuck]

using https://ehr.librehealth.io/interface/main/tabs/main.php?url=TAB_ONE_DEFAULT Reports: Clients/ Clinical

Report fails w/ sql error when some of the sort options are selected. Can run the report with no dates selected; it does the same behavior. REgards- HTuck

In the Sort By options:

works fine with any one or multiple of these options: 'none', 'name', 'age', 'NDC number' Medical Problem: shown in pic below Allergies: ERROR: query failed: select concat(pd.fname, ' ', pd.lname) AS patient_name, pd.pid AS patient_id, DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),pd.dob)), '%Y')+0 AS patient_age, pd.sex AS patient_sex, pd.race AS patient_race,pd.ethnicity AS patient_ethinic, concat(u.fname, ' ', u.lname) AS users_provider, REPLACE(REPLACE(concat_ws(',',IF(pd.hipaa_allowemail = 'YES', 'Allow Email','NO'),IF(pd.hipaa_allowsms = 'YES', 'Allow SMS','NO') , IF(pd.hipaa_mail = 'YES', 'Allow Mail Message','NO') , IF(pd.hipaa_voice = 'YES', 'Allow Voice Message','NO') ), ',NO',''), 'NO,','') as communications,li.date AS lists_date, li.diagnosis AS lists_diagnosis, li.title AS lists_title from patient_data as pd left outer join users as u on u.id = pd.providerid left outer join facility as f on f.id = u.facility_id left outer join lists as li on (li.pid = pd.pid AND (li.type='allergy')) where 1=1 AND li.date >= ? AND li.date < DATE_ADD(?,INTERVAL 1 DAY) AND DATE(li.date) <= ? ORDER BY lists_title

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND li.date < DATE_ADD(?,INTERVAL 1 DAY) AND DATE(li.date) <= ? ORDER BY list' at line 11

/opt/ehr/interface/reports/reports_controllers/ClinicalController.php at 353:sqlStatement /opt/ehr/interface/reports/clinical_reports.php at 349:prepareResults()

Age and allergies: more or less the above except: ... pd.pid AND (li.type='allergy')) where 1=1 AND li.date >= ? AND li.date < DATE_ADD(?,INTERVAL 1 DAY) AND DATE(li.date) <= ? ORDER BY lists_title

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND li.date < DATE_ADD(?,INTERVAL 1 DAY) AND DATE(li.date) <= ? ORDER BY list' at line 11

Drug: more or less the above except: ... patient_data as pd left outer join users as u on u.id = pd.providerid left outer join facility as f on f.id = u.facility_id left outer join prescriptions AS r on r.patient_id=pd.pid LEFT OUTER JOIN drugs AS d ON d.drug_id = r.drug_id where 1=1 AND r.date_modified >= ? AND r.date_modified < DATE_ADD(?, INTERVAL 1 DAY) AND DATE(r.date_modified) <= ? AND ( d.name LIKE ? OR r.drug LIKE ? ) ORDER BY r.drug

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND r.date_modified < DATE_ADD(?, INTERVAL 1 DAY) AND DATE(r.date_modified) <=' at line 11 ...

lab results: more or less the above except: ... pr.result AS procedure_result_result, pr.range AS procedure_result_range, pr.abnormal AS procedure_result_abnormal, pr.comments AS procedure_result_comments, pr.document_id AS procedure_result_document_id from patient_data as pd left outer join users as u on u.id = pd.providerid left outer join facility as f on f.id = u.facility_id left outer join procedure_order as po on po.patient_id = pd.pid left outer join procedure_order_code as pc on pc.procedure_order_id = po.procedure_order_id left outer join procedure_report as pp on pp.procedure_order_id = po.procedure_order_id left outer join procedure_type as pt on pt.procedure_code = pc.procedure_code and pt.lab_id = po.lab_id left outer join procedure_result as pr on pr.procedure_report_id = pp.procedure_report_id where 1=1 AND pr.date >= ? AND pr.date < DATE_ADD(?,INTERVAL 1 DAY) AND DATE(pr.date) <= ? AND (pr.result LIKE ?) ORDER BY procedure_result_result

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND pr.date < DATE_ADD(?,INTERVAL 1 DAY) AND DATE(pr.date) <= ? AND (pr.result' at line 19

[tmccormi]

I recommend removing this report as it has never worked well, when it worked at all. New versions of this kind of report are in Reports->Clients->Clinical Statistics: * More like those for the use cases in this report are needed.

[KoniKodes]

[KoniKodes]

We cannot search on this report.

[tmccormi]

This report is broken and needs to be removed from the repo. Don't bother with it.

[KoniKodes]

Thank you. We will remove them from our documentation.

[aethelwulffe]

C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\reports\clinical_reports.php: 14 // Added five new reports as per EHR certification requirements for Patient Lists - LibreEHR Support LLC, 2010 15
16: require_once "reports_controllers/ClinicalController.php"; 17 ?> 18

C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\reports\clinical_stats_by_demographics_report.php: 26 require_once("$srcdir/formatting.inc.php"); 27 require_once("$srcdir/vendor/libreehr/Framework/DataTable/DataTable.php"); 28: require_once "reports_controllers/ClinicalController.php"; 29 require_once($GLOBALS['srcdir'].'/headers.inc.php'); 30 $library_array = array('datatables');

C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\reports\lab_stats_by_demographics_report.php: 26 require_once("$srcdir/formatting.inc.php"); 27 require_once("$srcdir/vendor/libreehr/Framework/DataTable/DataTable.php"); 28: require_once "reports_controllers/ClinicalController.php"; 29 require_once($GLOBALS['srcdir'].'/headers.inc.php');

So, several reports use the controller that provides this error. Should all be pulled, and all the bits hooked to it, or just this report itself and associated controller functions?

[Ngai-E]

Hi @muarachmann i noticed the PR i Made for patient clinic filter ( PR #1464) takes care of these errors. Can u kindly check

[aethelwulffe]

I have approved that PR. Hey, Mua!