Changes between Initial Version and Version 1 of UseCase13

Timestamp:

06/14/26 20:55:58 (5 days ago)

Author:

236021

Comment:

--

UseCase13

@@ +1 @@
+= UseCase13 - Create / Update Billing Record =
+
+== Initiating Actor - `System Administrator / Billing Admin ` ==
+
+== Description ==
+The system automatically creates and updates billing records when lab tests or procedures are requested. Billing admins can view pending records, inspect itemized costs, and update payment status when payment is received.
+
+== Scenario ==
+
+1. Doctor requests a lab test and the system calculates the total cost for tests requested on that date.
+
+{{{
+#!sql
+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 patient_id 
+FROM users 
+WHERE username = 'maja.veljanova')
+  AND plt.test_date = '2026-06-05';
+}}}
+
+2. System creates a billing record for the patient on that date.
+
+{{{
+#!sql
+INSERT INTO billing (total_cost, payment_status, record_id, admin_id)
+VALUES (
+  75.00,
+  'PENDING',
+  (SELECT record_id FROM medical_records WHERE patient_id = (
+SELECT patient_id 
+FROM users 
+WHERE username = 'maja.veljanova')),
+  (
+SELECT admin_id 
+FROM admin LIMIT 1)
+)
+RETURNING bill_id;
+}}}
+
+3. System links the performed lab test to the billing record.
+
+{{{
+#!sql
+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 = 'Complete Blood Count')
+);
+}}}
+
+4. System links the performed procedure to the billing record.
+
+{{{
+#!sql
+INSERT INTO billing_procedures (bill_id, procedure_id)
+VALUES (
+  (SELECT MAX(bill_id) FROM billing),
+  (SELECT procedure_id FROM procedures WHERE procedure_type = 'ECG')
+);
+}}}
+
+5. Billing admin logs in and retrieves all pending billing records.
+
+{{{
+#!sql
+SELECT
+  b.bill_id,
+  u_p.first_name AS patient_first_name,
+  u_p.last_name AS patient_last_name,
+  b.total_cost,
+  b.payment_status
+FROM billing b
+JOIN medical_records mr ON b.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 b.payment_status = 'PENDING'
+ORDER BY b.bill_id;
+}}}
+
+6. Admin views the detailed billing record with itemized costs.
+
+{{{
+#!sql
+SELECT
+  b.bill_id,
+  b.total_cost,
+  b.payment_status,
+  u_p.first_name AS patient_first_name,
+  u_p.last_name AS patient_last_name,
+  p.embg,
+  lt.test_name,
+  lt.cost AS test_cost,
+  pr.procedure_type,
+  pr.cost AS procedure_cost
+FROM billing b
+JOIN medical_records mr ON b.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
+LEFT JOIN billing_lab_tests blt ON b.bill_id = blt.bill_id
+LEFT JOIN lab_tests lt ON blt.test_id = lt.test_id
+LEFT JOIN billing_procedures bp ON b.bill_id = bp.bill_id
+LEFT JOIN procedures pr ON bp.procedure_id = pr.procedure_id
+WHERE b.bill_id = (SELECT MAX(bill_id) FROM billing);
+}}}
+
+7. Admin marks the billing record as paid once payment is received.
+
+{{{
+#!sql
+UPDATE billing
+SET payment_status = 'PAID',
+    payment_date = '2026-06-09'
+WHERE bill_id = (SELECT MAX(bill_id) FROM billing WHERE payment_status = 'PENDING');
+}}}