Untitled
unknown
plain_text
a year ago
11 kB
0
Indexable
Never
SELECT queueing_no, appointment_time, registration_no, service_activity_schedule_start_time, service_activity_name, registration_type_name, medical_record_no, patient_name, plan_status, registration_note, status_registration, medical_record_detail_id, patient_id, registration_id, mrsa_medical_staff_name, registration_time, mrsa_medical_staff_id, service_activity_source, registration_date, service_activity_code, updated_at, mrsa_service_activity_code, branch_product_service_id, mrsa_branch_product_service_id
FROM (
SELECT r.branch_id,
r.registration_date,
( SELECT array_to_string(ARRAY( SELECT case when mrsax.service_activity_status = 'Done' then ''
else '-'
end as service_activity_status
FROM public_mr.medical_record_detail mrdx
LEFT JOIN public_mr.medical_record_service_activity mrsax ON mrsax.medical_record_detail_id = mrdx.id
WHERE mrdx.registration_id = r.id
AND mrdx.branch_id = r.branch_id
AND mrdx.status = 1
AND mrsax.status = 1
AND mrsax.medical_staff_id IS NOT NULL
AND mrsax.medical_staff_id = ' '
ORDER BY mrsax.id asc ), '')
) AS mrsa_service_activity_status,
( SELECT array_to_string(ARRAY( SELECT TRIM(mrsax.medical_staff_id)
FROM public_mr.medical_record_detail mrdx
LEFT JOIN public_mr.medical_record_service_activity mrsax ON mrsax.medical_record_detail_id = mrdx.id
WHERE mrdx.registration_id = r.id
AND mrdx.branch_id = r.branch_id
AND mrdx.status = 1
AND mrsax.medical_staff_id IS NOT NULL
ORDER BY mrsax.id ASC ), ', ')
) AS mrsa_medical_staff_id,
r.status_registration,
( SELECT array_to_string(ARRAY( SELECT sa_1.name
FROM public_fo.registration rr
LEFT JOIN public_fo.registration_service_activity rsax ON rr.id = rsax.registration_id
LEFT JOIN public_master.branch_service_activity bsa_1 ON rsax.service_activity_id = bsa_1.id
LEFT JOIN public_master.service_activity sa_1 ON bsa_1.service_activity_id = sa_1.id
WHERE rsax.registration_id = r.id
AND rsax.status = 1
AND sa_1.code <> 'REG'
ORDER BY rsax.id ASC ), ', ')
) AS service_activity_name,
(SELECT CONCAT(bqc.prefix, LPAD(r.queueing_no::text, bqc.digit_no, '0')) FROM public_fo.branch_queueing_counter bqc WHERE bqc.id = r.branch_queueing_counter_id) AS queueing_no,
(SELECT a.appointment_time FROM public_fo.appointment a WHERE a.id = r.appointment_id) AS appointment_time,
r.registration_no,
( SELECT array_to_string(ARRAY( SELECT mrsax.service_activity_start_time
FROM public_mr.medical_record_detail mrdx
LEFT JOIN public_mr.medical_record_service_activity mrsax ON mrsax.medical_record_detail_id = mrdx.id
WHERE mrdx.registration_id = r.id
AND mrdx.branch_id = r.branch_id
AND mrdx.status = 1
AND mrsax.status = 1
AND mrsax.medical_staff_id IS NOT NULL
ORDER BY mrsax.id ASC ), ', ')
) AS service_activity_schedule_start_time,
(SELECT gcd.name FROM public_master.general_code_detail gcd WHERE gcd.id = r.source_type) AS registration_type_name,
(SELECT mr.id
FROM public_mr.medical_record mr
WHERE mr.patient_id = r.patient_id
ORDER BY mr.id ASC
LIMIT 1
) AS medical_record_no,
( SELECT p.patient_full_name
FROM public_fo.patient p
WHERE p.id= r.patient_id
) AS patient_name,
( CASE
WHEN ( SELECT rsax.medical_plan_id
FROM public_fo.registration_service_activity rsax
WHERE rsax.registration_id = r.id
AND rsax.medical_plan_id > 0
AND rsax.status = 1
) > 0 THEN 'Planed'
ELSE '' END
) AS plan_status,
r.note AS registration_note,
( SELECT mrd.id
FROM public_mr.medical_record_detail mrd
WHERE mrd.registration_id = r.id
AND mrd.branch_id = r.branch_id
AND mrd.status = 1
LIMIT 1
) AS medical_record_detail_id,
r.patient_id,
r.id AS registration_id,
TO_CHAR(r.created_at, 'HH24:MI:SS') AS registration_time,
( SELECT array_to_string(ARRAY( SELECT sa_1.code
FROM public_fo.registration_service_activity rsax
JOIN public_master.branch_service_activity bsa_1 ON bsa_1.id = rsax.service_activity_id
JOIN public_master.service_activity sa_1 ON sa_1.id = bsa_1.service_activity_id
WHERE rsax.registration_id = r.id
AND rsax.status = 1
AND sa_1.code <> 'REG'
ORDER BY rsax.id ASC ), ', ')
) AS service_activity_code,
( SELECT array_to_string(ARRAY( SELECT distinct COALESCE(ux.name, 'Therapist/Nurse')
FROM public_mr.medical_record_detail mrdx
LEFT JOIN public_mr.medical_record_service_activity mrsax ON mrsax.medical_record_detail_id = mrdx.id
JOIN public_master.user ux ON ux.userid = mrsax.medical_staff_id
WHERE mrdx.registration_id = r.id
AND mrdx.branch_id = r.branch_id
AND mrdx.status = 1
AND mrsax.status = 1
AND mrsax.medical_staff_id IS NOT NULL
), '; ')
) AS mrsa_medical_staff_name,
(select gcdt.name from public_master.general_code_detail gcdt where r.source_type = gcdt.id) as service_activity_source,
r.updated_at,
( SELECT array_to_string(ARRAY( SELECT sa_1.code
FROM public_mr.medical_record_detail mrdx
JOIN public_mr.medical_record_service_activity mrsax ON mrsax.medical_record_detail_id = mrdx.id
JOIN public_master.branch_service_activity bsa_1 ON bsa_1.id = mrsax.branch_service_activity_id
JOIN public_master.service_activity sa_1 ON sa_1.id = bsa_1.service_activity_id
WHERE mrdx.registration_id = r.id
AND mrdx.branch_id = r.branch_id
AND mrdx.status = 1
AND mrsax.status = 1
AND sa_1.code <> 'REG'
ORDER BY mrsax.id ASC ), ', ')
) AS mrsa_service_activity_code,
( SELECT array_to_string(ARRAY( SELECT rsax.product_service_id
FROM public_fo.registration_service_activity rsax
JOIN public_master.branch_service_activity bsa_1 ON bsa_1.id = rsax.service_activity_id
JOIN public_master.service_activity sa_1 ON sa_1.id = bsa_1.service_activity_id
WHERE rsax.registration_id = r.id
AND rsax.status = 1
AND sa_1.code <> 'REG'
ORDER BY rsax.id ASC ), ', ')
) AS branch_product_service_id,
( SELECT array_to_string(ARRAY( SELECT mrsax.branch_product_service_id
FROM public_mr.medical_record_detail mrdx
LEFT JOIN public_mr.medical_record_service_activity mrsax ON mrsax.medical_record_detail_id = mrdx.id
WHERE mrdx.registration_id = r.id
AND mrdx.branch_id = r.branch_id
AND mrdx.status = 1
AND mrsax.status = 1
AND mrsax.medical_staff_id IS NOT NULL
ORDER BY mrsax.id ASC ), ', ')
) AS mrsa_branch_product_service_id
FROM public_fo.registration r
) reg
WHERE 1 = 1
AND branch_id = 838
AND registration_date BETWEEN '2023-09-13' AND '2023-09-14'
AND LOWER(status_registration) IN('new','waiting','onprocessmr','on%20process%20mr','under%20revision')
AND LOWER(mrsa_medical_staff_id) SIMILAR TO '%()%'
AND LOWER(service_activity_name) SIMILAR TO '%(regular%20treatment)%'
LIMIT 20 OFFSET 0