Changes between Version 8 and Version 9 of AdvancedConcepts

Timestamp:

06/15/26 22:40:50 (13 hours ago)

Author:

231093

Comment:

--

AdvancedConcepts

@@ -157 +157 @@
     v_request_id int4,
     v_dispatcher_user_id int4,
+    v_driver_user_id int4,
     v_price numeric(19, 2),
     v_currency_catalog_id int4,
@@ -165 +166 @@
 $$
 declare
-    v_driver_user_id        int4;
     v_dispatcher_company_id int4;
     v_start_position        geometry;
     v_customer_user_id      int4;
+    v_computed_driver_id    int4;
 begin
     if not exists(select * from request where request.id = v_request_id and request.status = 'pending') then
@@ -182 +183 @@
     v_customer_user_id := (select customer_user_id from request where request.id = v_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 = v_dispatcher_user_id
-                                limit 1);
-
-    if v_dispatcher_company_id is null then
-        raise exception 'Dispatcher % is not assigned to a company',
-            v_dispatcher_user_id;
-    end if;
-
-    v_start_position := (select start_location from request where id = v_request_id);
-
-    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
-                           and d.location is not null
-                         order by d.location <-> v_start_position
-                         limit 1);
-
-    if v_driver_user_id is null then
-        raise exception 'No available drivers found for request %', v_request_id;
+    if v_dispatcher_user_id is not null then
+        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 = v_dispatcher_user_id
+                                    limit 1);
+
+        if v_dispatcher_company_id is null then
+            raise exception 'Dispatcher % is not assigned to a company', v_dispatcher_user_id;
+        end if;
+
+        v_start_position := (select start_location from request where id = v_request_id);
+
+       
+        v_computed_driver_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
+                                   and d.location is not null
+                                 order by d.location <-> v_start_position
+                                 limit 1);
+
+        if v_computed_driver_id is null then
+            raise exception 'No available drivers found for request %', v_request_id;
+        end if;
+
+    else
+        if v_driver_user_id is null then
+            raise exception 'A driver_user_id must be specified if dispatcher is null';
+        end if;
+
+        if not exists(select 1 from freelancedriver where driver_user_id = v_driver_user_id) then
+            raise exception 'Driver % is not a registered freelance driver', v_driver_user_id;
+        end if;
+
+        v_computed_driver_id := v_driver_user_id;
     end if;
 
     insert into offer(status, created_at, request_id, dispatcher_user_id, driver_user_id, price, currency_catalog_id,
                       eta, customer_user_id)
-    values ('pending', now(), v_request_id, v_dispatcher_user_id, v_driver_user_id,
+    values ('pending', now(), v_request_id, v_dispatcher_user_id, v_computed_driver_id,
             v_price, v_currency_catalog_id, v_eta, v_customer_user_id);
+            
     commit;
 end;