Changes between Initial Version and Version 1 of UseCase0007

Timestamp:

03/16/26 20:43:18 (5 days ago)

Author:

221511

Comment:

--

UseCase0007

@@ +1 @@
+= UC0007: View Resource Usage Analytics =
+
+'''Initiating actor:''' Faculty Administrator
+
+'''Other actors:''' None
+
+A faculty administrator wants to analyze how resources are being utilized across the faculty. The system provides analytical reports including reservation counts per resource, most active users, reservation status distribution, busiest days of the week, and resource type utilization rates. These reports support data-driven decisions about resource allocation and scheduling policies.
+
+== Scenario ==
+
+ 1. The administrator (Marko Dimitrovski, user_id = 2) opens the usage analytics dashboard. The system displays a summary overview with total counts.
+{{{
+SELECT
+    COUNT(*) AS total_reservations,
+    COUNT(*) FILTER (WHERE status = 'approved') AS approved,
+    COUNT(*) FILTER (WHERE status = 'pending') AS pending,
+    COUNT(*) FILTER (WHERE status = 'rejected') AS rejected,
+    COUNT(*) FILTER (WHERE status = 'completed') AS completed,
+    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
+    COUNT(DISTINCT user_id) AS unique_users,
+    COUNT(DISTINCT resource_id) AS unique_resources
+FROM project.reservations;
+}}}
+
+ 2. The administrator views the ''Reservations per Resource'' report. The system shows resources ranked by total number of reservations.
+{{{
+SELECT r.name AS resource_name,
+       rt.type_name AS resource_type,
+       COUNT(res.reservation_id) AS total_reservations,
+       COUNT(res.reservation_id) FILTER (WHERE res.status = 'approved') AS approved,
+       COUNT(res.reservation_id) FILTER (WHERE res.status = 'completed') AS completed,
+       COUNT(res.reservation_id) FILTER (WHERE res.status = 'rejected') AS rejected
+FROM project.resources r
+JOIN project.resource_types rt ON r.type_id = rt.type_id
+LEFT JOIN project.reservations res ON r.resource_id = res.resource_id
+GROUP BY r.resource_id, r.name, rt.type_name
+ORDER BY total_reservations DESC;
+}}}
+
+ 3. The administrator views the ''Most Active Users'' report. The system shows users ranked by number of reservations made.
+{{{
+SELECT u.first_name || ' ' || u.last_name AS user_name,
+       ut.type_name AS user_role,
+       COUNT(res.reservation_id) AS total_reservations,
+       COUNT(res.reservation_id) FILTER (WHERE res.status IN ('approved', 'completed')) AS successful,
+       COUNT(res.reservation_id) FILTER (WHERE res.status = 'rejected') AS rejected
+FROM project.users u
+JOIN project.user_types ut ON u.type_id = ut.type_id
+LEFT JOIN project.reservations res ON u.user_id = res.user_id
+GROUP BY u.user_id, u.first_name, u.last_name, ut.type_name
+HAVING COUNT(res.reservation_id) > 0
+ORDER BY total_reservations DESC;
+}}}
+
+ 4. The administrator views the ''Reservation Status Distribution'' report. The system shows the breakdown of all reservations by status with percentages.
+{{{
+SELECT status,
+       COUNT(*) AS count,
+       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS percentage
+FROM project.reservations
+GROUP BY status
+ORDER BY count DESC;
+}}}
+
+ 5. The administrator views the ''Busiest Days of the Week'' report. The system shows how many approved or completed reservations fall on each weekday for physical resources.
+{{{
+SELECT
+    TO_CHAR(res.start_time, 'Day') AS day_of_week,
+    EXTRACT(ISODOW FROM res.start_time) AS day_number,
+    COUNT(*) AS reservation_count
+FROM project.reservations res
+JOIN project.resources r ON res.resource_id = r.resource_id
+JOIN project.resource_types rt ON r.type_id = rt.type_id
+WHERE rt.is_physical = TRUE
+  AND res.status IN ('approved', 'completed')
+GROUP BY TO_CHAR(res.start_time, 'Day'), EXTRACT(ISODOW FROM res.start_time)
+ORDER BY day_number;
+}}}
+
+ 6. The administrator views the ''Resource Type Utilization'' report. The system shows usage per resource category with averages.
+{{{
+SELECT rt.type_name,
+       CASE WHEN rt.is_physical THEN 'Physical' ELSE 'Digital' END AS category,
+       COUNT(DISTINCT r.resource_id) AS total_resources,
+       COUNT(res.reservation_id) AS total_reservations,
+       ROUND(COUNT(res.reservation_id)::NUMERIC /
+             NULLIF(COUNT(DISTINCT r.resource_id), 0), 1) AS avg_reservations_per_resource
+FROM project.resource_types rt
+LEFT JOIN project.resources r ON rt.type_id = r.type_id
+LEFT JOIN project.reservations res ON r.resource_id = res.resource_id
+GROUP BY rt.type_id, rt.type_name, rt.is_physical
+ORDER BY total_reservations DESC;
+}}}
+
+ 7. The administrator views the ''Monthly Reservation Trends'' report. The system shows how many reservations were created each month and their outcomes.
+{{{
+SELECT
+    TO_CHAR(DATE_TRUNC('month', created_at), 'YYYY-MM') AS month,
+    COUNT(*) AS reservations_created,
+    COUNT(*) FILTER (WHERE status IN ('approved', 'completed')) AS successful,
+    COUNT(*) FILTER (WHERE status = 'rejected') AS rejected,
+    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
+FROM project.reservations
+GROUP BY DATE_TRUNC('month', created_at)
+ORDER BY month;
+}}}