Changes between Version 4 and Version 5 of AdvancedApplicationDesign_v2

Timestamp:

09/22/25 22:08:27 (2 weeks ago)

Author:

212012

Comment:

--

AdvancedApplicationDesign_v2

@@ -266 +266 @@
 {{{#!div
 {{{#!sql
-do $$
+create or replace procedure transfer_case_between_stations(
+    in p_case_id bigint,
+    in p_new_station_id bigint
+)
+language plpgsql as $$
 declare
-    v_case_id bigint := 1;
     v_old_station bigint;
-    v_new_station bigint := 2;
-    v_new_officer bigint;
+    v_new_policeman bigint;
+    v_new_statement_id bigint;
+    v_victim_id bigint;
+    v_witness_id bigint;
 begin
-    select p_id into v_old_station from crime_case where c_id = v_case_id;
-    
-    select pe_id into v_new_officer from police_station where p_id = v_new_station;
-    
-    update crime_case 
-    set p_id = v_new_station 
-    where c_id = v_case_id;
-    
-    insert into statements (s_id, statement_date, description, incident_timestamp, incident_place, c_id, pe_id, victim_pe_id, witness_pe_id)
-    select coalesce(MAX(s_id), 0) + 1, CURRENT_DATE, 'Случајот е префрлен од станица ' || v_old_station || ' во станица ' || v_new_station, CURRENT_TIMESTAMP, 'Префрлување на случај', v_case_id, (select pe_id from policeman where p_id = v_new_station limit 1), 2, 3
-    from statements;
-    
-    raise notice 'Случајот со ID % е префрлен од полициска станица % во полициска станица %', v_case_id, v_old_station, v_new_station;    
-
-exception
-    when others then
-        raise exception 'Transaction failed: %', SQLERRM;
+    begin
+        select p_id INTO v_old_station 
+        from crime_case 
+        where c_id = p_case_id;
+        
+        if not found then
+            raise exception 'Слулајот со ID % не е пронајден', p_case_id;
+        end if;
+        
+        if not exists (select 1 from police_station where p_id = p_new_station_id) then
+            raise exception 'Полициската станица со ID % не е пронајдена', p_new_station_id;
+        end if;
+        
+        update crime_case 
+        set p_id = p_new_station_id 
+        where c_id = p_case_id;
+        
+        select pe_id into v_new_policeman 
+        from policeman 
+        where p_id = p_new_station_id 
+        limit 1;
+        
+        if v_new_policeman is null then
+            select pe_id into v_new_policeman from policeman limit 1;
+            if v_new_policeman is null then
+                raise exception 'Не е пронајден полицаец во системот';
+            end if;
+        end if;
+        
+        select pe_id into v_victim_id from victim limit 1;
+        if v_victim_id is null then
+            raise exception 'Не се пронајдени жртви во системот';
+        end if;
+        
+        select pe_id into v_witness_id from witness limit 1;
+        if v_witness_id is null then
+            raise exception 'Не се пронајдени сведоци во системот';
+        end if;
+        
+        select coalesce(max(s_id), 0) + 1 into v_new_statement_id from statements;
+        
+        insert into statements (
+            s_id, statement_date, description, incident_timestamp, 
+            incident_place, c_id, pe_id, victim_pe_id, witness_pe_id
+        ) values (
+            v_new_statement_id, 
+            current_date, 
+            'Случајот е префрлен од станица ' || v_old_station || ' to station ' || p_new_station_id, 
+            current_timestamp, 
+            'Трансфер на случај', 
+            p_case_id, 
+            v_new_policeman, 
+            v_victim_id,
+            v_witness_id
+        );
+        
+        raise notice 'Случајот % е успешно префрлен од станица % во станица %', 
+                     p_case_id, v_old_station, p_new_station_id;
+        
+        commit;
+        
+    exception
+        when others then
+            rollback;
+            raise exception 'Неуспешен трансфер: %', SQLERRM;
+    end;
 end;
 $$;