76 lines
5.7 KiB
MySQL
76 lines
5.7 KiB
MySQL
|
select bed.Ward as 'Cabin/Ward name',
|
||
|
|
CONCAT(IF(pn.given_name IS NULL, '', CONCAT((pn.given_name), ' ')),
|
||
|
|
IF(pn.middle_name IS NULL, '', CONCAT((pn.middle_name), ' ')),
|
||
|
|
IF(pn.family_name IS NULL, '', CONCAT((pn.family_name), ' '))) as 'Patient Name',
|
||
|
|
pid.identifier as 'Reg. No',
|
||
|
|
p.gender as 'Gender',
|
||
|
|
CONCAT(IF(TIMESTAMPDIFF(YEAR, p.birthdate, CURRENT_DATE()) = 0, '',
|
||
|
|
CONCAT(TIMESTAMPDIFF(YEAR, p.birthdate, CURRENT_DATE()), ' Y ')),
|
||
|
|
IF((TIMESTAMPDIFF(MONTH, p.birthdate, CURRENT_DATE()) % 12) = 0, '',
|
||
|
|
CONCAT((TIMESTAMPDIFF(MONTH, p.birthdate, CURRENT_DATE()) % 12), ' M ')),
|
||
|
|
IF((FLOOR(TIMESTAMPDIFF(DAY, p.birthdate, CURRENT_DATE()) % 30.4375)) = 0, '',
|
||
|
|
CONCAT(FLOOR(TIMESTAMPDIFF(DAY, p.birthdate, CURRENT_DATE()) % 30.4375), ' D'))) as 'Age',
|
||
|
|
per_at.value as 'Father/Suppose Name',
|
||
|
|
CONCAT(IF(pad.address1 IS NULL, '', CONCAT((CONCAT((pad.address1), ',')), ' ')),
|
||
|
|
IF(pad.address3 IS NULL, '', CONCAT((CONCAT((pad.address3), ',')), ' ')),
|
||
|
|
IF(pad.postal_code IS NULL, '', CONCAT((CONCAT('- ', (pad.postal_code), ',')), ' ')),
|
||
|
|
IF(pad.address5 IS NULL, '', CONCAT((CONCAT((pad.address5), ',')), ' ')),
|
||
|
|
IF(pad.county_district IS NULL, '', CONCAT((CONCAT((pad.county_district), ',')), ' ')),
|
||
|
|
IF(pad.state_province IS NULL, '', CONCAT((CONCAT((pad.state_province))), ' '))) as 'Address',
|
||
|
|
pa.value as 'Mobile',
|
||
|
|
bed.Bed as 'Bed',
|
||
|
|
bed.unit as 'Unit',
|
||
|
|
DATE_FORMAT(bpatient.date_started, '%Y-%m-%d %h:%i %p') as 'Admission Date',
|
||
|
|
IF(discharge.discharge_time IS NULL, 'N/A',
|
||
|
|
DATE_FORMAT(discharge.discharge_time, '%Y-%m-%d %h:%i %p')) as 'Discharge Date'
|
||
|
|
from bed_patient_assignment_map bpatient
|
||
|
|
inner join encounter e on bpatient.encounter_id = e.encounter_id
|
||
|
|
inner join encounter_type et on e.encounter_type = et.encounter_type_id
|
||
|
|
inner join person_name pn on bpatient.patient_id = pn.person_id
|
||
|
|
inner join person p on bpatient.patient_id = p.person_id
|
||
|
|
left join person_address as pad on p.person_id = pad.person_id
|
||
|
|
left join patient_identifier pid on bpatient.patient_id = pid.patient_id
|
||
|
|
left join person_attribute as pa on p.person_id = pa.person_id and pa.person_attribute_type_id =
|
||
|
|
(select pat.person_attribute_type_id
|
||
|
|
from person_attribute_type as pat
|
||
|
|
where pat.name = 'phoneNumber')
|
||
|
|
left join person_attribute as per_at on p.person_id = per_at.person_id and per_at.person_attribute_type_id =
|
||
|
|
(select pat.person_attribute_type_id
|
||
|
|
from person_attribute_type as pat
|
||
|
|
where pat.name = 'primaryRelative')
|
||
|
|
INNER JOIN obs o on e.patient_id = o.person_id
|
||
|
|
and o.value_coded in (select cv.concept_id
|
||
|
|
from concept_view cv
|
||
|
|
where concept_full_name = 'Admit Patient')
|
||
|
|
left JOIN obs dis_o on bpatient.patient_id = dis_o.person_id and dis_o.voided = 0
|
||
|
|
and dis_o.value_coded in (select cv.concept_id
|
||
|
|
from concept_view cv
|
||
|
|
where concept_full_name = 'Discharge Patient')
|
||
|
|
left join
|
||
|
|
(select e.patient_id as 'id',
|
||
|
|
e.date_created as 'discharge_time'
|
||
|
|
from encounter e
|
||
|
|
inner join users dis_u on e.creator = dis_u.user_id
|
||
|
|
inner join person_name dis_name on dis_u.person_id = dis_name.person_id
|
||
|
|
inner join encounter_type et on e.encounter_type = et.encounter_type_id
|
||
|
|
where et.name = 'DISCHARGE') discharge on bpatient.patient_id = discharge.id
|
||
|
|
inner join (SELECT bed_patient_assignment_map_id, bed_id, patient_id
|
||
|
|
FROM bed_patient_assignment_map
|
||
|
|
WHERE bed_patient_assignment_map_id IN (
|
||
|
|
SELECT MAX(bed_patient_assignment_map_id)
|
||
|
|
FROM bed_patient_assignment_map
|
||
|
|
GROUP BY patient_id)) latest on bpatient.patient_id = latest.patient_id
|
||
|
|
inner join
|
||
|
|
(SELECT ward.name as 'Ward',
|
||
|
|
bed.bed_number as Bed,
|
||
|
|
bt.name as 'unit',
|
||
|
|
bed.bed_id as 'id'
|
||
|
|
from bed
|
||
|
|
inner join bed_location_map on bed.bed_id = bed_location_map.bed_id
|
||
|
|
inner join location as ward on bed_location_map.location_id = ward.location_id
|
||
|
|
left join bed_tag_map btm on bed.bed_id = btm.bed_id
|
||
|
|
left join bed_tag bt on btm.bed_tag_id = bt.bed_tag_id) bed
|
||
|
|
on latest.bed_id = bed.id
|
||
|
|
where et.name = 'ADMISSION'
|
||
|
|
and bpatient.date_started BETWEEN '#startDate#' AND '#endDate#';
|