Actors

• Transport Organizer

Steps

1. The transport organizer logs into the system.
2. The transport organizer selects the ‘Our Routes’ tab.
3. (Optional) The transport organizer selects the ‘From destination’ and enters a location.
4. (Optional) The transport organizer selects the ‘To destination’ and enters a location. 5.The transport organizer selects a route to view trips for the selected route.
5. (Optional) The transport organizer selects a date range to filter their trips.
6. (Optional) The transport organizer selects the status to filter their trips.

SELECT lf.name, lt.name, trip.free_seats, trip.date, t_o.company_name, stop_time
FROM trip
JOIN transport_organizer as t_o ON trip.transport_organizer_id = t_o.transport_organizer_id
JOIN route as r ON trip.route_id = r.route_id
JOIN location as lf on r.from_location_id = lf.location_id
JOIN location as lt on r.to_location_id = lt.location_id
JOIN trip_stops as stop on lf.location_id = stop.location_id
WHERE status = 'NOT_STARTED'
ORDER BY date, stop_time;

SELECT lf.name, lt.name, trip.free_seats, trip.date, t_o.company_name, stop_time
FROM trip
JOIN transport_organizer as t_o ON trip.transport_organizer_id = t_o.transport_organizer_id
JOIN route as r ON trip.route_id = r.route_id
JOIN location as lf on r.from_location_id = lf.location_id
JOIN location as lt on r.to_location_id = lt.location_id
JOIN trip_stops as stop on lf.location_id = stop.location_id
WHERE ({selected_status} IS NULL OR tick.status = {selected_status})
AND ({before_date} IS NULL OR trip.date < {before_date})
AND ({after_date} IS NULL OR trip.date > {after_date})
ORDER BY date, stop_time;

6. The transport organizer clicks on the ‘Search’ button.
7. The transport organizer selects the trip they wish to modify.
8. The system displays a form with information related to the trip’s stops and their timestamps.

DELETE
FROM trip_stops as ts
WHERE ts.trip_stop_id = {trip_stop_selected};

INSERT INTO
trip_stops(trip_id, location_id, stop_time)
VALUES({selected_trip_id}, {selected_location_id}, {entered_time});

UPDATE trip_stops
SET {field} = {new_value}
WHERE trip_stops = {selected_trip_stop_id};

9. The transport organizer clicks on the ‘Save changes’ button.