Changes between Initial Version and Version 1 of UseCase0007PrototypeImplementation

Timestamp:

03/16/26 21:54:25 (5 days ago)

Author:

221511

Comment:

--

UseCase0007PrototypeImplementation

@@ +1 @@
+= UC0007 Prototype Implementation - View Resource Usage Analytics =
+
+'''Initiating actor:''' Faculty Administrator
+
+'''Other actors:''' None
+
+The administrator accesses an analytics dashboard with seven reports covering reservation statistics, resource utilization, user activity, and trends. Each report runs an aggregate SQL query and displays the results as a formatted table.
+
+== Scenario ==
+
+ 1. The administrator selects ''View Resource Usage Analytics'' from the main menu. The system shows the analytics sub-menu.
+{{{
+=== Resource Usage Analytics ===
+
+  1. Summary Overview
+  2. Reservations per Resource
+  3. Most Active Users
+  4. Reservation Status Distribution
+  5. Busiest Days of the Week
+  6. Resource Type Utilization
+  7. Monthly Reservation Trends
+  8. Show All Reports
+  0. Cancel / Go back
+}}}
+
+ 2. The administrator selects ''Summary Overview''. The system runs the following query:
+{{{
+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 reservations;
+}}}
+
+The system displays the summary:
+{{{
+  --- Summary Overview ---
+  Total:              26
+  Approved:           17
+  Pending:            4
+  Rejected:           1
+  Completed:          3
+  Cancelled:          1
+  Unique Users:       10
+  Unique Resources:   16
+}}}
+
+ 3. The administrator selects ''Reservation Status Distribution''. The system runs:
+{{{
+SELECT status, COUNT(*) AS count,
+       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS percentage
+FROM reservations
+GROUP BY status
+ORDER BY count DESC;
+}}}
+
+The system displays the breakdown with percentages:
+{{{
+  --- Reservation Status Distribution ---
+  Status    | Count | Percentage (%)
+  ----------+-------+---------------
+  approved  | 17    | 65.4
+  pending   | 4     | 15.4
+  completed | 3     | 11.5
+  rejected  | 1     | 3.8
+  cancelled | 1     | 3.8
+}}}
+
+ 4. The administrator selects ''Most Active Users''. The system runs:
+{{{
+SELECT u.first_name || ' ' || u.last_name, ut.type_name,
+       COUNT(res.reservation_id) AS total,
+       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 users u
+JOIN user_types ut ON u.type_id = ut.type_id
+LEFT JOIN 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 DESC;
+}}}
+
+The system displays the most active users:
+{{{
+  --- Most Active Users ---
+  User                    | Role           | Total | Successful | Rejected
+  ------------------------+----------------+-------+------------+---------
+  Nikola Trajkovski       | Teaching Staff | 8     | 7          | 1
+  Elena Stojanova         | Teaching Staff | 6     | 5          | 0
+  Ivana Kostadinova       | Teaching Staff | 3     | 3          | 0
+  Aleksandar Georgieski   | Teaching Staff | 2     | 1          | 0
+  Stefan Nikolov          | Student        | 2     | 2          | 0
+  ...
+}}}
+
+ 5. The administrator selects ''Busiest Days of the Week''. The system queries only approved or completed reservations on physical resources:
+{{{
+SELECT TRIM(TO_CHAR(res.start_time, 'Day')),
+       COUNT(*) AS reservation_count
+FROM reservations res
+JOIN resources r ON res.resource_id = r.resource_id
+JOIN 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 EXTRACT(ISODOW FROM res.start_time);
+}}}
+
+The system displays the results:
+{{{
+  --- Busiest Days of the Week (Physical Resources) ---
+  Day        | Reservations
+  -----------+-------------
+  Tuesday    | 4
+  Wednesday  | 3
+  Thursday   | 3
+  Friday     | 5
+  Sunday     | 1
+}}}
+
+ 6. The administrator selects ''Resource Type Utilization''. The system runs:
+{{{
+SELECT rt.type_name,
+       CASE WHEN rt.is_physical THEN 'Physical' ELSE 'Digital' END,
+       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)
+FROM resource_types rt
+LEFT JOIN resources r ON rt.type_id = r.type_id
+LEFT JOIN 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;
+}}}
+
+The system displays the utilization per resource type:
+{{{
+  --- Resource Type Utilization ---
+  Type                | Category | Resources | Reservations | Avg/Resource
+  --------------------+----------+-----------+--------------+-------------
+  Classroom           | Physical | 6         | 10           | 1.7
+  Computer Laboratory | Physical | 4         | 5            | 1.3
+  Conference Room     | Physical | 3         | 2            | 0.7
+  3D Printer          | Physical | 2         | 2            | 1.0
+  Virtual Machine     | Digital  | 3         | 2            | 0.7
+  Online Service      | Digital  | 3         | 2            | 0.7
+  Projector           | Physical | 2         | 2            | 1.0
+  Software License    | Digital  | 4         | 1            | 0.3
+}}}
+
+ 7. The administrator selects ''Monthly Reservation Trends''. The system runs:
+{{{
+SELECT TO_CHAR(DATE_TRUNC('month', created_at), 'YYYY-MM'),
+       COUNT(*) AS 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 reservations
+GROUP BY DATE_TRUNC('month', created_at)
+ORDER BY DATE_TRUNC('month', created_at);
+}}}
+
+The system displays the monthly trend:
+{{{
+  --- Monthly Reservation Trends ---
+  Month   | Created | Successful | Rejected | Cancelled
+  --------+---------+------------+----------+----------
+  2026-01 | 15      | 14         | 0        | 0
+  2026-02 | 11      | 6          | 1        | 1
+}}}