Changes between Version 11 and Version 12 of AdvancedConcepts

Timestamp:

06/16/26 02:54:43 (6 days ago)

Author:

231119

Comment:

--

AdvancedConcepts

@@ -246 +246 @@
 }}}
 
+Останати рефакторирани процедури
+{{{
+create or replace procedure make_request(
+    out request_id int4,
+    in customer_id int4,
+    in start_position geometry,
+    in end_position geometry,
+    in number_of_adult_passengers int4 default 1,
+    in number_of_children int4 default 0,
+    in female_driver boolean default false,
+    in luggage boolean default false,
+    in luggage_count int4 default 0,
+    in baby_seat_count int4 default 0
+)
+    language plpgsql as
+$$
+begin
+    if not exists(select user_id
+                  from customer c
+                  where c.user_id = customer_id)
+    then
+        raise exception 'Customer with id % does not exist', customer_id;
+    end if;
+
+    if number_of_adult_passengers < 0 then
+        raise exception 'Number of adult passengers cannot be negative';
+    end if;
+
+    if number_of_children < 0 then
+        raise exception 'Number of children cannot be negative';
+    end if;
+
+    if number_of_adult_passengers + number_of_children < 0 then
+        raise exception 'There must be at least 1 passenger';
+    end if;
+
+    if baby_seat_count < 0 then
+        raise exception 'Baby seat count cannot be negative';
+    end if;
+
+    if luggage = false and luggage_count > 0 then
+        raise exception 'Luggage count cannot be greater than zero if the luggage flag is set to false.';
+    end if;
+
+    if luggage = true and luggage_count <= 0 then
+        raise exception 'Invalid luggage count parameters';
+    end if;
+
+    if start_position = end_position then
+        raise exception 'Start and destination location cannot be the same';
+    end if;
+
+    insert into request(customer_user_id, start_latitude, start_longitude, end_latitude, end_longitude, timestamp,
+                        number_of_adult_passengers, number_of_children, status, female_driver, luggage, luggage_count,
+                        baby_seat_count, start_location,
+                        end_location)
+    values (customer_id,
+            ST_Y(start_position::geometry),
+            ST_X(start_position::geometry),
+            ST_Y(end_position::geometry),
+            ST_X(end_position::geometry),
+            now(),
+            make_request.number_of_adult_passengers,
+            make_request.number_of_children,
+            'pending',
+            make_request.female_driver,
+            make_request.luggage,
+            make_request.luggage_count,
+            make_request.baby_seat_count,
+            make_request.start_position,
+            make_request.end_position)
+    returning id into request_id;
+    commit;
+end;
+$$;
+
+create or replace procedure start_ride(
+    in v_offer_id int4
+)
+    language plpgsql as
+$$
+declare
+    v_request_id  int4;
+    v_driver_id   int4;
+    v_vehicle_vin varchar(17);
+begin
+    if not exists(select id from offer where id = v_offer_id) then
+        raise exception 'Offer with id % does not exist', v_offer_id;
+    end if;
+
+    if not exists (select 1
+                   from offer
+                   where id = v_offer_id
+                     and status = 'pending') then
+        raise exception 'Offer % is not pending', v_offer_id;
+    end if;
+
+    v_request_id := (select o.request_id
+                     from offer o
+                     where o.id = v_offer_id);
+
+    v_driver_id := (select driver_user_id from offer where id = v_offer_id);
+
+    if v_driver_id is null then
+        raise exception 'No active drivers available';
+    end if;
+
+    v_vehicle_vin := (select vin_vehicle
+                      from driver_vehicle
+                      where id_driver = v_driver_id
+                        and (time_to is null or time_to > now())
+                      limit 1);
+
+    if v_vehicle_vin is null then
+        raise exception 'No active vehicle for driver %', v_driver_id;
+    end if;
+
+    insert into ride(start_time,
+                     end_time,
+                     distance_traveled,
+                     vehicle_vin,
+                     driver_user_id,
+                     request_id,
+                     status,
+                     offer_id)
+    values (now(),
+            null,
+            0,
+            v_vehicle_vin,
+            v_driver_id,
+            v_request_id,
+            'in_progress',
+            v_offer_id);
+
+    update offer
+    set status='accepted'
+    where id = v_offer_id;
+
+    update request
+    set status='accepted'
+    where id = v_request_id;
+
+    commit;
+end;
+$$;
+
+create or replace procedure add_waypoint(
+    in v_request_id int4,
+    in v_location geometry
+)
+    language plpgsql as
+$$
+declare
+    v_prev_seqno int4;
+begin
+    if not exists(select id from request where id = v_request_id) then
+        raise exception 'Request with id % does not exist', v_request_id;
+    end if;
+
+    if exists(select id
+              from waypoints w
+              where w.request_id = v_request_id
+                and ST_DWithin(w.location, v_location, 0.000001)) then
+        raise exception 'Waypoint already added';
+    end if;
+
+    v_prev_seqno := (select count(*)
+                     from waypoints w
+                     where w.request_id = v_request_id);
+
+    insert into waypoints(latitude, longitude, seqno, request_id, location)
+    values (st_y(v_location), st_x(v_location), v_prev_seqno + 1, v_request_id, v_location);
+    commit;
+end;
+$$;
+
+create or replace procedure write_report(
+    id_ride int,
+    user_id int,
+    new_message text,
+    new_title text,
+    incident_location geometry,
+    new_reason text
+)
+language plpgsql
+as $$
+    begin
+        if not exists (
+            select 1
+            from Ride r
+            join Request req on r.request_id = req.id
+            where r.id = id_ride and req.customer_user_id = user_id
+        ) then
+            raise exception 'Invalid report: Customer % was not part of Ride %', user_id, id_ride;
+        end if;
+
+        if exists (
+            select 1
+            from Report
+            where ride_id = id_ride and customer_user_id = user_id
+        ) then
+            raise exception 'Duplicate report: Customer % has already reported Ride %', user_id, id_ride;
+        end if;
+
+        if trim(new_message) = '' or trim(new_title) = '' or trim(new_reason) = '' then
+            raise exception 'Validation error: Title, message, and reason cannot be empty strings.';
+        end if;
+
+        insert into report(ride_id, customer_user_id, message, title, location, latitude, longitude, reason)
+        values (
+            id_ride,
+            user_id,
+            new_message,
+            new_title,
+            st_setsrid(incident_location, 4326),
+            ST_Y(incident_location),
+            ST_X(incident_location),
+            new_reason
+        );
+
+        commit;
+    end;
+$$;
+}}}
+
 
 ** Lambda Архитектура и 4D Траектории на возење **