Changes between Version 31 and Version 32 of DatabaseProgramming

Timestamp:

06/16/26 02:22:05 (26 hours ago)

Author:

231119

Comment:

--

DatabaseProgramming

@@ -439 +439 @@
 $$
 declare
-    v_driver_user_id  int4;
-    v_start_latitude  double precision;
-    v_start_longitude double precision;
+    v_customer_user_id      int4;
+    v_dispatcher_company_id int4;
+    v_driver_user_id        int4;
+    v_start_latitude        double precision;
+    v_start_longitude       double precision;
 begin
     if not exists(select * from request where request.id = request_id and request.status = 'pending') then
@@ -458 +460 @@
     where id = request_id;
 
-    select d.user_id, dc.vin_vehicle
-    into v_driver_user_id
-    from driver d
-             join driver_vehicle dc on d.user_id = dc.id_driver
-    where dc.time_to is null
-      and d.latitude is not null
-      and d.longitude is not null
-    order by power(v_start_latitude - d.latitude, 2) + power(v_start_longitude - d.longitude, 2)
-    limit 1;
+    v_customer_user_id := (select request.customer_user_id from request where request.id = request_id);
+
+    v_dispatcher_company_id := (select eh.company_id
+                                from dispatcher d
+                                         join employmenthistory eh on eh.employee_user_id = d.user_id and
+                                                                      (eh.end_date is null or eh.end_date > now())
+                                where d.user_id = dispatcher_user_id
+                                limit 1);
+
+    if v_dispatcher_company_id is null then
+        raise exception 'Dispatcher % is not assigned to a company',
+            dispatcher_user_id;
+    end if;
+
+    v_driver_user_id := (select d.user_id
+                         from driver d
+                                  join driver_vehicle dc on d.user_id = dc.id_driver
+                                  join employmenthistory eh
+                                       on eh.employee_user_id = d.user_id and (eh.end_date is null or
+                                                                               eh.end_date > now())
+                         where dc.time_to is null
+                           and eh.company_id = v_dispatcher_company_id
+                         order by power(v_start_latitude - d.latitude, 2) + power(v_start_longitude - d.longitude, 2)
+                         limit 1);
+
+    if not exists(select * from driver where user_id = v_driver_user_id) then
+        raise exception 'No available drivers!';
+    end if;
 
     if (select company_id from employmenthistory where employee_user_id = dispatcher_user_id and end_date is null) !=
@@ -663 +684 @@
 {{{
 create or replace function check_available_seats()
-returns trigger
-language plpgsql
-as $$
-    begin
-        if (select v.passenger_capacity
-            from vehicle v
-            inner join driver_vehicle dv on v.vin=dv.vin_vehicle
-            where dv.id_driver=new.driver_user_id)<(select r.number_of_children+r.number_of_adult_passengers+r.baby_seat_count
-                                                    from request r
-                                                    inner join offer o on r.id=o.request_id
-                                                    where o.id=new.id)
-            then
-            raise exception 'Not enough seats in the vehicle';
-        end if;
-        return new;
-    end;
+    returns trigger
+    language plpgsql
+as
+$$
+begin
+    if (select v.passenger_capacity
+        from vehicle v
+                 inner join driver_vehicle dv on v.vin = dv.vin_vehicle
+        where dv.id_driver = new.driver_user_id and (dv.time_to is null or dv.time_to > now())) <
+       (select r.number_of_children + r.number_of_adult_passengers + r.baby_seat_count
+        from request r
+                 inner join offer o on r.id = o.request_id
+        where o.id = new.id)
+    then
+        raise exception 'Not enough seats in the vehicle';
+    end if;
+    return new;
+end;
 $$;
 create or replace trigger check_valid_seats