Материјализирани погледи

Статистика на случаи по полициска станица

create materialized view case_statistics_by_station as
select 
    ps.p_id,
    ps.p_address as station_address,
    sia.city,
    count(cc.c_id) as total_cases,
    count(case when cc.c_status = 'A' then 1 end) as active_cases,
    count(CASE when cc.c_status = 'Z' then 1 end) as closed_cases,
    count(sc.c_id) as solved_cases
from Police_station as ps
left join Crime_case as cc on ps.p_id = cc.p_id
left join Solved_case as sc on cc.c_id = sc.c_id
left join Sector_of_interal_affairs as sia on ps.s_id = sia.s_id
group by ps.p_id, ps.p_address, sia.city;

Анализа на типови на криминал

create materialized view crime_type_analysis as
select
    tc.t_id,
    tc.t_name,
    count(cbtc.c_id) as total_cases,
    count(sc.c_id) as solved_cases,
        count(distinct af.accused_pe_id) as total_accused
from Type_of_crime as tc
left join Case_belongs_to_type_of_crime as cbtc on tc.t_id = cbtc.t_id
left join Crime_case as cc on cbtc.c_id = cc.c_id
left join Solved_case as sc on cc.c_id = sc.c_id
left join Accused_for as af on cc.c_id = af.c_id
group by tc.t_id, tc.t_name;

Анализа на перформансот на секој службеник

create materialized view police_performance as
select 
    'Officer' as role_type,
    o.pe_id,
    p.first_name,
    p.last_name,
    o.o_badge_no as badge_number,
    o.o_date_of_employment as date_of_employment,
    ps.p_address as station_address,
    count(distinct cc.c_id) as cases_managed
from Officer as o
join People as p on o.pe_id = p.pe_id
left join Police_station ps on o.pe_id = ps.pe_id
left join Crime_case cc on ps.p_id = cc.p_id
group by o.pe_id, p.first_name, p.last_name, o.o_badge_no, o.o_date_of_employment, ps.p_address

union all

select 
    'Policeman' as role_type,
    pm.pe_id,
    p.first_name,
    p.last_name,
    pm.badge_no as badge_number,
    pm.p_date_of_employment as date_of_employment,
    ps.p_address as station_address,
    count(distinct s.c_id) as cases_handled
from Policeman as pm
join People as p on pm.pe_id = p.pe_id
left join Police_station as ps on pm.p_id = ps.p_id
left join Statements as s on pm.pe_id = s.pe_id
group by pm.pe_id, p.first_name, p.last_name, pm.badge_no, pm.p_date_of_employment, ps.p_address;

Сумирање на типови на докази

create materialized view evidence_summary as
select 
    e.e_type,
    count(*) as total_evidence,
    count(case when e.is_found = true then 1 end) as found_evidence,
    count(case when e.is_found = false then 1 end) as missing_evidence
from Evidence as e
group by e.e_type;

Индексирање

Индекси за People табелата

create index idx_people_embg on People(embg);

create index idx_people_names on People(first_name, last_name);

create index idx_people_nationality on People(nationality);

Индекси за Crime_case табелата

create index idx_crime_case_status on Crime_case(c_status);

create index idx_crime_case_date on Crime_case(opening_date);

Индекси за Statements табелата

create index idx_statements_date on Statements(statement_date);

create index idx_statements_incident on Statements(incident_timestamp);

Индекси за Evidence табелата

create index idx_evidence_type on Evidence(e_type);

create index idx_evidence_found on Evidence(is_found);

Индекси за Policeman табелата

create index idx_policeman_rank on Policeman(rank);

Функции и тригери

Траење на случај во денови

create or replace function calculate_case_duration(case_id bigint)
returns integer as $$
declare
    opening_date date;
    closing_date date;
    duration integer;
begin
    select cc.opening_date into opening_date
    from Crime_case as cc
    where cc.c_id = case_id;
    
    select sc.date_of_closing into closing_date
    from Solved_case as sc
    where sc.c_id = case_id;
    
    if closing_date is not null then
        duration := closing_date - opening_date;
    else
        duration := CURRENT_DATE - opening_date;
    end if;
    
    return duration;
end;
$$ language plpgsql;

Промена на статус на случајот кога ќе биде завршен

create or replace function update_case_status_on_solve()
returns trigger as $$
begin
    update Crime_case
    set c_status = 'Z'
    where c_id = NEW.c_id and c_status = 'A';
    
    return new;
end;
$$ language plpgsql;

create trigger trg_update_case_status
    after insert on Solved_case
    for each row
    execute function update_case_status_on_solve();

Процедури и трансакции

Креирање на нов случај со иницјална изјава

create or replace procedure create_crime_case(
    in p_case_name varchar(255),
    in p_police_station_id bigint,
    in p_policeman_id bigint,
    in p_victim_id bigint,
    in p_witness_id bigint,
    in p_statement_description varchar(10000),
    in p_incident_timestamp timestamp,
    in p_incident_place varchar(4000),
    in p_crime_types bigint[]
)
language plpgsql as $$
declare
    new_case_id bigint;
    new_statement_id bigint;
    crime_type_id bigint;
begin
    select coalesce(MAX(c_id), 0) + 1 into new_case_id from Crime_case;
    
    insert into Crime_case (c_id, c_name, opening_date, c_status, p_id)
    values (new_case_id, p_case_name, CURRENT_DATE, 'A', p_police_station_id);
    
    select coalesce(MAX(s_id), 0) + 1 INTO 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 (new_statement_id, CURRENT_DATE, p_statement_description, p_incident_timestamp, p_incident_place, new_case_id, p_policeman_id, p_victim_id, p_witness_id);
    
    foreach crime_type_id IN ARRAY p_crime_types
    loop
        insert into Case_belongs_to_type_of_crime (c_id, t_id)
        values (new_case_id, crime_type_id);
    end loop;
    
    raise notice 'Случајот % е успешно креиран со изјава %', new_case_id, new_statement_id;
end;
$$;

Трансфер на случај помеѓу полициски станици

do $$
declare
    v_case_id bigint := 1;
    v_old_station bigint;
    v_new_station bigint := 2;
    v_new_officer bigint;
begin
    -- Get current station
    select p_id into v_old_station from Crime_case where c_id = v_case_id;
    
    -- Get officer for new station
    select pe_id into v_new_officer from Police_station where p_id = v_new_station;
    
    -- Transfer case
    update Crime_case 
    set p_id = v_new_station 
    where c_id = v_case_id;
    
    -- Add transfer statement
    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;
end;
$$;