Failed to Generate Bahmni Lab Reports

Tags: #<Tag:0x00007f5ff8492ed8>

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;

@amolsatvix @dkayiwa

{ “appointmentStatusReport”: { “name”: “Appointment Details”, “type”: “MRSGeneric”, “config”: { “sqlPath”: “/var/www/bahmni_config/openmrs/apps/reports/sql/appointment_status_report.sql” } } }