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