Patient Clinical Summaries
From OpenEMR Project Wiki
MU Requirements
Meaningful Use Measures:
Clinical summaries are provided to patients for at least 80% of all office visits.
Certification Criteria for EHR:
1. Enable a user to provide clinical summaries to patients (in paper or electronic form) or each office visit that include, at a minimum, diagnostic test results, medication list, medication allergy list, procedures, problem list, and immunizations.
2. If the clinical summary is provided electronically (i.e., not printed), it must be provided in: 1) human readable format; and 2) accordance with the standards % specified in Table 2A row 1 to provide to a patient on electronic media, or through some other electronic means.
Proposed Solution
The following SQL view is meant as an example of one way that you can gather all the typical information needed for Heathcare Data Management, continuing care reporting, etc ... contributed by tony and aron@mi-squared.com. Modified and updated by Thomas.
CREATE OR REPLACE VIEW current_insurance_vw AS
SELECT max(id) id, pid FROM insurance_data
where type = 'primary'
GROUP BY pid;
create or replace view encounter_ar_activity_vw as
select encounter,
ars.check_date as pay_date,
sum(pay_total) paid
from ar_activity ara
join ar_session ars on ara.session_id = ars.session_id
group by encounter;
create or replace view history_data_latest_vw as
select max(id) as id, pid
from history_data
group by pid;
/* general data of the encounter */
create or replace view encounter_report_vw as
select enc.encounter as encounter,
enc.date as encounter_date,
pd.pid as pid,
ifnull( isd.subscriber_ss, '') as ee_ssn,
ifnull( pd.ss, '') as member_ssn,
pd.pubpid as account_number,
pd.pubpid as ee_id,
ifnull( pd.DOB, '') as member_dob,
ifnull( pd.sex, '') as member_sex,
ear.pay_date as paid_date,
ifnull( pu.npi, '') as provider_number,
concat(pu.fname, ' ', pu.lname) as provider_name,
ifnull( fac.state, '') as provider_state,
substr( fac.postal_code, 1, 3 ) as provider_zip3,
ifnull( fac.postal_code, '') as provider_zip,
'' as vendor_tos,
'' as vendor_provider_type,
ifnull( fac.name, '') as clinic_identifier,
ifnull( pcc.pc_catname, '') as visit_type,
ifnull(bil.justify, '') AS diags,
ifnull( bil.code, '') as cpt,
ifnull( bil.modifier, '') as cpt_modifier,
ifnull( bil.units, '') as units,
ifnull( hd.alcohol, '') as alcohol,
ifnull( hd.tobacco, '') as smokes,
ifnull( hd.exercise_patterns, '') as exercise,
vitals.weight as weight,
vitals.height as height,
vitals.waist_circ as waist,
'' as hip,
ifnull( vitals.bmi, '') as bmi,
ifnull( vitals.bps, '') as systolic,
ifnull( vitals.bpd, '') as diastolic,
'' as triglycerides,
ifnull(hd.exams,'') as exams,
'' as platelet_count,
ifnull(hd.value_1,'') as readiness_to_change_weight,
ifnull(hd.value_2,'') as readiness_to_quit_smoking,
'' as other_risks_captured,
'' as family_risk_factor,
'' as risk_factor_indicator,
'' as risk_factor_totals,
'' as risk_factor_totals_panic,
po.order_encounter_id,
po.patient_id,
po.date_ordered,
po.patient_instructions,
po.order_status,
po.provider_id,
pt.name,
pt.procedure_code,
pt.procedure_type,
pt.body_site,
pt.specimen,
pt.route_admin,
pt.laterality,
pt.description
from form_encounter enc
join patient_data pd on pd.pid = enc.pid
left join users pu on pu.id = enc.provider_id
left join facility fac on fac.id = pu.facility_id
left join current_insurance_vw isv on isv.pid = pd.pid
left join insurance_data isd on isd.id = isv.id
left join encounter_ar_activity_vw ear on ear.encounter = enc.encounter
left join billing bil on ( bil.encounter = enc.encounter and bil.code_type = 'CPT4' )
left join history_data_latest_vw hdl on hdl.pid = pd.pid
left join history_data hd on hdl.id = hd.id
left join forms enc_vitals on ( enc_vitals.encounter = enc.encounter and enc_vitals.form_name = 'Vitals' )
left join form_vitals vitals on enc_vitals.form_id = vitals.id
left join forms enc_exam on ( enc_exam.encounter = enc.encounter )
left join procedure_order po on ( po.order_encounter_id = enc.encounter )
left join procedure_type pt on ( pt.procedure_type_id = po.procedure_type_id )
left join openemr_postcalendar_categories pcc on pcc.pc_catid = enc.pc_catid;
/* Various issues - problem, medication, allergy ... + prescriptions
may have multiple rows */
select type, title, begdate, enddate, occurrence, classification, referredby, diagnosis, activity, comments, destination
from lists
where pid = [patient id]
and date(date) = [date of encounter]
/* prescriptions */
select ifnull(p.drug, p.drug_id).
p.dosage,
p.quantity,
p.size,
lof.title form,
lou.title unit,
lor.title route,
loi.title interval,
if(p.substitute=1,'substitute allowed','do not substitute'),
p.refills,
p.per_refill,
p.note
from prescriptions p,
list_options lof,
list_options lou,
list_options lor,
list_options loi,
users u
where p.patient_id = [patient id]
and date(p.date_added) = [date of encounter]
and u.id = p.provider_id
and lof.list_id = 'drug_form'
and lof.option_id = p.form
and lof.list_id = 'drug_units'
and lof.option_id = p.unit
and lof.list_id = 'drug_route'
and lof.option_id = p.route
and lof.list_id = 'drug_interval'
and lof.option_id = p.interval
/* if procedure_order.order_encounter_id != 0
procedure ordered */
select po.date_ordered,
po.patient_instructions,
po.order_status,
pt.name,
pt.procedure_code,
pt.procedure_type,
pt.body_site,
pt.specimen,
pt.route_admin,
pt.laterality,
pt.description,
CONCAT(u.title, ' ', trim(fname), ' ', trim(lname))
from procedure_order po,
procedure_type pt,
users u
where po.patient_id = [patient id]
and po.order_encounter_id = [encounter id]
and pt.procedure_type_id = po.procedure_type_id
and u.id = po.provider_id
/* if procedure_order.report_encounter_id != 0
results of ordered procedure
contains multiple rows */
select pc.date_collected,
pc.patient_instructions,
pt.name,
pt.procedure_code,
pt.procedure_type,
pt.body_site,
pt.specimen,
pt.route_admin,
pt.laterality,
pt.description,
preport.date_report,
preport.specimen_num,
preport.report_status,
presult.facility,
presult.result,
presult.units,
presult.range,
presult.abnormal,
presult.comments,
presult.result_status,
CONCAT(u.title, ' ', trim(fname), ' ', trim(lname))
from procedure_order pc,
procedure_type pt,
procedure_report preport,
procedure_result presult,
users u
where pc.patient_id = [patient id]
and pc.report_encounter_id = [encounter id]
and pt.procedure_type_id = pc.procedure_type_id
and preport.procedure_order_id = pc.procedure_order_id
and presult.procedure_report_id = preport.procedure_report_id
and pt.procedure_type_id = presult.procedure_type_id
and u.id = pc.provider_id
/* immunizations */
select lo.title,
i.manufacturer,
i.lot_number,
i.note,
CONCAT(u.title, ' ', trim(fname), ' ', trim(lname))
from immunizations i,
list_options lo,
users u
where i.patient_id = [patient id]
and lo.list_id = 'immunizations'
and lo.option_id = i.immunization_id
and date(i.administered_date) = [date of encounter]
and u.id = i.administered_by_id
The following reports can be generated:
1. Patient Info (limited demographics)
2. Office Visit Info
3. Diagnosis
4. Treatment Plan/Progress Notes - Not available yet
5. Medication List
6. Allergy List
7. Medication Allergy List
8. Problem List
9. Procedures (need work, Referral Summary not available)
10. Immunization
11. Prescription
Effected Code, Tables, etc
All the tables and lists mentioned in the Proposed Solutions are affected. No work is needed except for those new tables or lists that need to be built.
Owner and Status
Design - Tony McCormick (MI2) and Thomas Wong (Intesync)
Coding - TBA
