Sorry am new to openbahmni, I tried to create simple query to get lab report but i keep get this error what may be the cause of this error?
org.postgresql.util.PSQLException: Can't use query methods that take a query string on a PreparedStatement
Hello @inno,
Can you provide more details about your Bahmni Lab Report?
-
Provide Report Configuration.
-
Share the SQL query of the report.
This is the query which throw that error…
SELECT test_section.name AS SECTION,
test.name AS Test_Name,
(SELECT COUNT(id)
FROM test_result
WHERE test_id=test.id
AND cast(test_result.lastupdated AS DATE) BETWEEN '2020-07-01' AND '2020-07-18') AS Total
FROM test_section
LEFT JOIN test on test_section.id=test.test_section_id
WHERE
(SELECT COUNT(id)
FROM test_result
WHERE test_id=test.id
AND cast(test_result.lastupdated AS DATE) BETWEEN '2020-07-01' AND '2020-07-18')>0
ORDER BY SECTION ASC;
But whne i run this query direct to the database, I get result without any problem but when i generate it from bahmni i get that error, I do not know waht is the problem ?
@inno, The Above SQL query looks good. As I have asked you to share the Bahmni Lab Report configuration added in the report.json.
okey sorry, i did not understand at first
"testSection": {
"name": "Lab Test Section",
"id": "bahmni.reports.labAndRadiology.testSection",
"description": "Lab Test Section",
"type": "ElisGeneric",
"periodType": "Monthly",
"category": "DHIS2",
"url": "#/dashboard/deptreports?dept=labAndRadiology&reportId=testSection",
"config": {
"sqlPath": "/var/www/bahmni_config/openmrs/apps/reports/sql/lab/test_section.sql",
"dept": "labAndRadiology"
}
},Hey @inno,
For the Bahmni EMR reports the configuration should be like this in the report.json:
"testSection": {
"name": "Lab Test Section",
"type": "ElisGeneric",
"config": {
"sqlPath": "/var/www/bahmni_config/openmrs/apps/reports/sql/lab/test_section.sql"
}
},
For more reference, Bahmni report configuration is here.
@amolsatvix Would it be possible to use an ElisGeneric report on a concatenated report with other MRSGeneric queries?
I just found this: Bahmni concatenated type report is not working for different data source reports
So I guess the answer is no.
1 Like
Yes, @lauravignoli, To make it work with other data sources also. We need to do some customization for backward compatibility.
Thanks for your reply!
@amolsatvix @dkayiwa Hello, I am also new to EMR, I customized an appointment status report, it was working fine a month ago but I am not sure what went wrong as the report does not match the appointment scheduling module, can not find something useful in the logs either, I am sharing my SQL file and config file hoping someone can point out what seems to be the problem.
Thanks in advance
@amolsatvix @lauravignoli @dkayiwa
SELECT
identifierOfPatient.identifier AS "Patient ID",
concat_ws(' ', nameOfTheProviders.given_name, nameOfTheProviders.family_name) AS "Provider",
serviceAppointment.name AS "Appointment Service",
serviceTypeAppointment.name AS "Appointment Service Type",
DATE(pai.start_date_time) AS "Date Of Appointment",
DATE_FORMAT(pai.start_date_time, '%I:%i %p') AS "Appointment Start Time",
DATE_FORMAT(pai.end_date_time, '%I:%i %p') AS "Appointment End Time",
pai.status AS "Status",
(CASE WHEN pai.status in('Scheduled') then NULL ELSE DATE_FORMAT(CONVERT_TZ(appCheckinTime.notes,'+00:00','+05:30'),'%I:%i %p') END) AS "Start CheckedIn Time",
(CASE WHEN language.concept_full_name is not null and otherLanguage.value is Not NULL
then concat_ws(', ',language.concept_full_name,otherLanguage.value)
WHEN language.concept_full_name is not null and otherLanguage.value is NULL then language.concept_full_name
WHEN language.concept_full_name is null and otherLanguage.value is NOT NULL THEN otherLanguage.value
ELSE NULL END) AS "Patient's Language",
pai.comments AS "Notes"
FROM
patient pa
LEFT JOIN patient_appointment pai ON pa.patient_id = pai.patient_id and pai.voided = 0
LEFT JOIN
(/*getting checkin time*/
SELECT
latest_appointment_audit.appointment_id,
latest_appointment_audit.notes,
latest_appointment_audit.date_created,
pa.patient_id,
latest_appointment_audit.status
FROM
patient_appointment pa
INNER JOIN
(
SELECT
latest_appointment_audit.appointment_id,
latest_appointment_audit.notes,
latest_appointment_audit.date_created,
pa.patient_id,
latest_appointment_audit.status
FROM
patient_appointment pa
INNER JOIN
(
SELECT
paa.patient_appointment_audit_id,
paa.appointment_id,
paa.date_created,
paa.notes,
paa.status
FROM
patient_appointment_audit paa
INNER JOIN
(
/*getting latest status of appointment*/
SELECT
MAX(paa.date_created) AS date_created,
paa.appointment_id,
paa.notes
FROM
patient_appointment_audit paa
WHERE
paa.status = 'CheckedIn'
GROUP BY
paa.appointment_id
)
latest_audit_for_appointment
ON latest_audit_for_appointment.appointment_id = paa.appointment_id
AND latest_audit_for_appointment.date_created = paa.date_created
)
latest_appointment_audit
ON pa.patient_appointment_id = latest_appointment_audit.appointment_id
AND pa.voided = 0
)
latest_appointment_audit
ON pa.patient_appointment_id = latest_appointment_audit.appointment_id
AND pa.voided = 0
) as appCheckinTime on pai.patient_id = appCheckinTime.patient_id and appCheckinTime.appointment_id = pai.patient_appointment_id
LEFT JOIN appointment_service serviceAppointment ON pai.appointment_service_id = serviceAppointment.appointment_service_id AND serviceAppointment.voided = 0
LEFT JOIN appointment_service_type serviceTypeAppointment ON serviceAppointment.appointment_service_id = serviceTypeAppointment.appointment_service_id AND pai.appointment_service_type_id = serviceTypeAppointment.appointment_service_type_id AND serviceTypeAppointment.voided = 0
LEFT JOIN patient_identifier identifierOfPatient ON pai.patient_id = identifierOfPatient.patient_id
LEFT JOIN visit checkinTime ON pai.patient_id = checkinTime.patient_id AND DATE(checkinTime.date_started) = DATE(pai.start_date_time)/*getting Start visit time*/
LEFT JOIN provider providerForAppointment ON pai.provider_id = providerForAppointment.provider_id
LEFT JOIN person_name nameOfTheProviders ON providerForAppointment.person_id = nameOfTheProviders.person_id
LEFT JOIN person_attribute attributeOfPatient ON pai.patient_id = attributeOfPatient.person_id
LEFT JOIN person_attribute_type typeOfPatientAttribute ON attributeOfPatient.person_attribute_type_id = typeOfPatientAttribute.person_attribute_type_id
LEFT JOIN concept_view cv ON cv.concept_id = attributeOfPatient.value
Left Join
(/*Getting the value for language*/
SELECT
attributeOfPatient.person_id,
cv.concept_full_name
FROM
person_attribute attributeOfPatient
INNER JOIN
person_attribute_type typeOfPatientAttribute
ON attributeOfPatient.person_attribute_type_id = typeOfPatientAttribute.person_attribute_type_id
INNER JOIN
concept_view cv
ON cv.concept_id = attributeOfPatient.VALUE
WHERE
typeOfPatientAttribute.name = "Language"
AND attributeOfPatient.voided = 0
) as language on pai.patient_id = language.person_id
Left join
(/*Getting the value for Other language*/
SELECT
attributeOfPatient.person_id,
attributeOfPatient.VALUE
FROM
person_attribute attributeOfPatient
INNER JOIN
person_attribute_type typeOfPatientAttribute
ON attributeOfPatient.person_attribute_type_id = typeOfPatientAttribute.person_attribute_type_id
WHERE
typeOfPatientAttribute.name = "Other Language"
AND attributeOfPatient.voided = 0
) as otherLanguage on pai.patient_id = otherLanguage.person_id
WHERE
DATE(pai.start_date_time) BETWEEN DATE('#startDate#') AND DATE('#endDate#')
AND pai.appointment_kind='Scheduled'
AND pai.appointment_service_id IN
(
SELECT appointment_service_id
FROM appointment_service
where voided = 0
)
AND identifierOfPatient.identifier not in ('A00000')/*removing dummy patient from reports*/
GROUP BY pai.patient_appointment_id
ORDER BY pai.start_date_time;{ “appointmentStatusReport”: { “name”: “Appointment Details”, “type”: “MRSGeneric”, “config”: { “sqlPath”: “/var/www/bahmni_config/openmrs/apps/reports/sql/appointment_status_report.sql” } } }