I wanted to list out some observations for a patient and used the following Groovy script:
def sql(s) {admin.executeSQL(s,true) }
patientIdentifier = "999-3" // a test patient, of course
sql("""
select
date(o.obs_datetime),
(select name from concept_name where concept_id=o.concept_id limit 1) as question,
case c.datatype_id
when 1 /* numeric */ then cast(o.value_numeric as char)
when 2 /* coded */ then (select min(name) from concept_name where concept_id=o.value_coded)
when 3 /* text */ then value_text
when 6 /* date */ then cast(date(o.value_datetime) as char)
when 7 /* time */ then cast(time(o.value_datetime) as char)
when 8 /* datetime */ then cast(o.value_datetime as char)
when 10 /* boolean */ then if(o.value_numeric=1,'TRUE','FALSE')
else '?'
end as answer
from
obs o
left outer join
concept c
on c.concept_id=o.concept_id
where
o.person_id = (select patient_id from patient_identifier where identifier = '$patientIdentifier' limit 1)
order by
o.obs_datetime desc
""").collect{ it.join(": ") }.join("n")
which generated output like this:
2011-02-18: PATIENT HAD SEX IN LAST 6MO: TRUE 2011-02-18: PlAN FOR METHOD OF FAMILY PLANNING, DETAILED: ? 2011-02-18: QUANTITY: 5 2011-02-18: FAMILY PLANNING METHOD PLAN: INITIATION 2011-02-18: METHOD OF FAMILY PLANNING: ECPS 2011-02-18: HIV DISCLOSURE TO ANYONE, SPECIFIC: OTHER HOUSEHOLD MEMBER 2011-02-18: REASON FOR REFUSAL - FAMILY PLANNING: TRYING TO CONCEIVE NOW 2011-02-18: PlAN FOR METHOD OF FAMILY PLANNING, DETAILED: ? 2011-02-18: METHOD OF FAMILY PLANNING: MALE CONDOMS 2011-02-18: QUANTITY: 5 2011-02-18: FAMILY PLANNING METHOD PLAN: INITIATION 2011-02-18: METHOD OF FAMILY PLANNING: BTL 2011-02-18: FREETEXT, GENERAL: POSITIVE 2011-02-18: REASON FOR REFUSAL - FAMILY PLANNING: ABSTINENCE 2011-02-18: FAMILY PLANNING: TRUE 2011-02-18: REVIEW OF MEDICAL HISTORY: ICTERUS 2011-02-18: PATIENT REPORTED PROBLEM: YES 2011-02-18: CURRENT MEDICATIONS: ALUVIA 2011-02-18: REVIEW OF MEDICAL HISTORY: DEPRESSION