Changes between Version 2 and Version 3 of DatabaseProgramming

Timestamp:

05/23/26 20:06:51 (2 days ago)

Author:

231028

Comment:

--

DatabaseProgramming

@@ -3 +3 @@
 Во оваа фаза е имплементирана апликациската логика со функции, процедури и тригери. Секоја од нив е документирана во продолжение.
 
-**Процедури**
+== Процедури
 
-**Функции и тригери**
+**Процедура за вработување на возач во компанија**
 
+Процедурата проверува дали id-то на возачот е валидно, доколку не постои фрла соодветен исклучок.
+
+create or replace procedure employ_driver(id_employee int, id_company int)
+language plpgsql
+as $$
+    begin
+        if not exists (
+                   select 1
+                   from driver
+                   where user_id=id_employee)
+               then
+                   raise exception 'Driver does not exist';
+        else
+               insert into
+                   employmenthistory (employee_user_id, start_date, end_date, company_id)
+                   values (id_employee,current_date, null,id_company);
+        end if;
+    end;
+$$;
+
+**Процедура за вработување на диспечер во компанија**
+
+Процедурата проверува дали id-то на диспечерот е валидно, доколку не постои фрла соодветен исклучок.
+
+create or replace procedure employ_dispatcher(id_employee int, id_company int)
+language plpgsql
+as $$
+    begin
+        if not exists (
+                   select 1
+                   from dispatcher
+                   where user_id=id_employee)
+               then
+                   raise notice 'Dispatcher does not exist';
+        else
+               insert into
+                   employmenthistory (employee_user_id, start_date, end_date, company_id)
+                   values (id_employee,current_date, null,id_company);
+        end if;
+    end;
+$$;
+
+create or replace procedure fire_driver(id_employee int, id_company int)
+language plpgsql
+as $$
+    begin
+        if not exists (
+               select 1
+               from driver
+               where user_id=id_employee)
+           then
+               raise exception 'Driver does not exist';
+       else
+           if not exists (
+               select 1
+               from employmenthistory
+               where employee_user_id=id_employee and end_date==null and company_id=id_company)
+           then
+               raise exception 'Driver does not have an employment';
+           else
+               update employmenthistory
+               set end_date=current_date
+               where employee_user_id==id_employee and end_date==null and company_id=id_company;
+               raise notice 'Driver is fired';
+               commit;
+           end if;
+       end if;
+    end;
+$$;
+
+create or replace procedure fire_dispatcher(id_employee int, id_company int)
+language plpgsql
+as $$
+    begin
+        if not exists (
+               select 1
+               from dispatcher
+               where user_id=id_employee)
+           then
+               raise notice 'Dispatcher does not exist';
+       else
+           if not exists(
+               select 1
+               from employmenthistory
+               where employee_user_id=id_employee and end_date==null and company_id=id_company)
+           then
+               raise exception 'Dispatcher does not have an employment';
+           else
+               update employmenthistory
+               set end_date=current_date
+               where employee_user_id=id_employee and end_date==null and company_id=id_company;
+               raise notice 'Dispatcher is fired';
+               commit;
+           end if;
+       end if;
+    end;
+$$;
+
+
+create or replace procedure write_rating(rating numeric, comment text, id_ride int, id_customer int)
+language plpgsql
+as $$
+    begin
+        insert into review (rating, comment, ride_id, customer_user_id)
+        values (write_rating.rating,write_rating.comment,id_ride,id_customer);
+    end;
+$$;
+
+create or replace procedure write_chat(new_message text, id_ride int, id_user int)
+language plpgsql
+as $$
+    begin
+        insert into chatmessage(message, user_id_from, ride_id)
+        values (new_message,id_user,id_ride);
+    end;
+$$;
+
+create or replace procedure write_report(id_ride int, user_id int, new_message text, new_title text,temp_latitude float, temp_long float, new_reason text)
+language plpgsql
+as $$
+    begin
+        insert into report(ride_id, customer_user_id, message, title, latitude, longitude, reason)
+        values (id_ride,user_id,new_message,new_title,temp_latitude,temp_long,new_reason);
+    end;
+$$;
+
+== Функции и тригери
+