Changes between Initial Version and Version 1 of UseCase11

Timestamp:

06/12/26 18:20:14 (5 days ago)

Author:

236021

Comment:

--

UseCase11

@@ +1 @@
+= UseCase11 - Create Referral =
+
+== Initiating Actor - `Doctor` ==
+
+== Description ==
+A doctor creates a referral for a patient to another doctor for specialist opinion or a consultation. The system creates the referral record and automatically schedules an appointment with the referred doctor.
+
+== Scenario ==
+
+1. Doctor opens a patient's medical record.
+
+{{{
+#!sql
+SELECT
+  mr.record_id,
+  mr.patient_id
+FROM medical_records mr
+WHERE mr.patient_id = (
+  SELECT patient_id FROM users WHERE username = 'maja.veljanova'
+);
+}}}
+
+2. System retrieves the list of available specialist doctors.
+
+{{{
+#!sql
+SELECT
+  d.doctor_id,
+  u_d.first_name,
+  u_d.last_name,
+  ds.specialization_name,
+  d.department_id
+FROM doctors d
+JOIN doctor_specialization ds ON d.specialization_id = ds.specialization_id
+JOIN users u_d ON d.doctor_id = u_d.doctor_id
+ORDER BY ds.specialization_name, u_d.last_name;
+}}}
+
+3. Doctor selects the receiving doctor, enters the referral reason, and specifies the appointment date and time.
+
+{{{
+#!sql
+INSERT INTO referrals (record_id, from_doctor_id, to_doctor_id, reason, referral_date, appointment_date, appointment_time)
+VALUES (
+  (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')),
+  (SELECT doctor_id FROM users WHERE username = 'elena.kirova'),
+  (SELECT doctor_id FROM users WHERE username = 'nikola.trajkov'),
+  'Cardiology consultation for hypertension management',
+  '2026-05-30',
+  '2026-06-15',
+  '14:00'
+)
+RETURNING referral_id;
+}}}
+
+4. System automatically creates an appointment with the receiving doctor.
+
+{{{
+#!sql
+INSERT INTO appointments (patient_id, doctor_id, appointment_date, appointment_time, status)
+VALUES (
+  (SELECT patient_id FROM users WHERE username = 'maja.veljanova'),
+  (SELECT doctor_id FROM users WHERE username = 'nikola.trajkov'),
+  '2026-06-15',
+  '14:00',
+  'SCHEDULED'
+)
+RETURNING appointment_id;
+}}}
+
+5. System displays the confirmation with full referral and appointment details.
+
+{{{
+#!sql
+SELECT
+  r.referral_id,
+  r.reason,
+  r.referral_date,
+  r.appointment_date,
+  r.appointment_time,
+  u_from.first_name AS from_doctor_first_name,
+  u_from.last_name AS from_doctor_last_name,
+  u_to.first_name AS to_doctor_first_name,
+  u_to.last_name AS to_doctor_last_name
+FROM referrals r
+JOIN doctors d_from ON r.from_doctor_id = d_from.doctor_id
+JOIN users u_from ON d_from.doctor_id = u_from.doctor_id
+JOIN doctors d_to ON r.to_doctor_id = d_to.doctor_id
+JOIN users u_to ON d_to.doctor_id = u_to.doctor_id
+WHERE r.referral_id = (SELECT MAX(referral_id) FROM referrals);
+}}}
+
+6. System notifies the receiving doctor by displaying their incoming referrals.
+
+{{{
+#!sql
+SELECT
+  r.referral_id,
+  r.reason,
+  r.referral_date,
+  r.appointment_date,
+  u_p.first_name AS patient_first_name,
+  u_p.last_name AS patient_last_name
+FROM referrals r
+JOIN medical_records mr ON r.record_id = mr.record_id
+JOIN patients p ON mr.patient_id = p.patient_id
+JOIN users u_p ON p.patient_id = u_p.patient_id
+WHERE r.to_doctor_id = (SELECT doctor_id FROM users WHERE username = 'nikola.trajkov')
+ORDER BY r.referral_date DESC;
+}}}