UseCase06 - Doctor Requests Lab Test

Initiating Actor - Doctor

Description

A doctor requests a laboratory test for a patient. The system creates a performed lab test record linking the doctor, patient, and the specific test.

Scenario

1. Doctor opens a patient's medical record.

SELECT
  mr.record_id,
  mr.patient_id
FROM medical_records mr
WHERE mr.patient_id = (
    SELECT p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);

2. System displays all available lab tests.

SELECT
  lt.test_id,
  lt.test_name,
  lt.description,
  lt.cost
FROM lab_tests lt
ORDER BY lt.test_name;

3. Doctor selects a lab test, specifies the test date and notes, and submits the request.

INSERT INTO performed_lab_tests (performed_test_id, patient_id, doctor_id, technician_id, test_id, test_date, notes)
VALUES (
  (SELECT COALESCE(MAX(performed_test_id), 0) + 1 FROM performed_lab_tests),
  (SELECT p.patient_id
   FROM patients p
   JOIN users u ON u.user_id = p.user_id
   WHERE u.username = '1402994123456'),
  (SELECT d.doctor_id
   FROM doctors d
   JOIN users u ON u.user_id = d.user_id
   WHERE u.username = 'elena.kirova@medora.com'),
  (SELECT technician_id FROM lab_technician WHERE username = 'lab_marina'),
  (SELECT test_id FROM lab_tests WHERE test_name = 'Blood test'),
  '2026-06-05',
  'Blood work for hypertension monitoring'
)
RETURNING performed_test_id;

4. System calculates the total cost of lab tests performed on that date for this patient.

SELECT SUM(lt.cost) AS total_cost
FROM performed_lab_tests plt
JOIN lab_tests lt ON plt.test_id = lt.test_id
WHERE plt.patient_id = (
    SELECT p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
)
  AND plt.test_date = '2026-06-05';

5. System creates a billing record for the patient.

INSERT INTO billing (bill_id, total_cost, payment_status, record_id, admin_id)
VALUES (
  (SELECT COALESCE(MAX(bill_id), 0) + 1 FROM billing),
  30.00,
  'PENDING',
  (SELECT mr.record_id
   FROM medical_records mr
   JOIN patients p ON mr.patient_id = p.patient_id
   JOIN users u ON u.user_id = p.user_id
   WHERE u.username = '1402994123456'),
  (SELECT admin_id FROM admin LIMIT 1)
)
RETURNING bill_id;

6. System links the billing record to the performed lab test.

INSERT INTO billing_lab_tests (bill_id, test_id)
VALUES (
  (SELECT MAX(bill_id) FROM billing),
  (SELECT test_id FROM lab_tests WHERE test_name = 'Blood test')
);

7. System confirms the request and displays all pending lab tests for the patient.

SELECT
  plt.performed_test_id,
  lt.test_name,
  plt.test_date,
  plt.notes
FROM performed_lab_tests plt
JOIN lab_tests lt ON plt.test_id = lt.test_id
WHERE plt.patient_id = (
    SELECT p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);