Actors

• Transport Organizer

Steps

1. The transport organizer logs into the system.
2. The transport organizer selects the ‘Our Trips’ tab.
3. The transport organizer selects the ‘From destination’ and enters a location.
4. The transport organizer selects the ‘To destination’ and enters a location.
5. (Optional) The transport organizer selects a date range to filter their trips. By default, all trips with a NOT_STARTED status are displayed.
6. (Optional) The transport organizer selects the status to filter their trips. By default, all trips with an NOT_STARTED status are displayed.

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.