| Version 3 (modified by , 2 days ago) ( diff ) |
|---|
Database Programming
Во оваа фаза е имплементирана апликациската логика со функции, процедури и тригери. Секоја од нив е документирана во продолжение.
Процедури
Процедура за вработување на возач во компанија
Процедурата проверува дали 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;
$$;
