| Version 2 (modified by , 13 days ago) ( diff ) |
|---|
Фаза 4 — Функции, Процедури, Тригери
Тригери
1. trg_validate_ballot
Тип: BEFORE INSERT на табела ballot
Се активира пред секое внесување на гласачко ливче и ги проверува три услови: дали изборот е активен (status=1), дали гласачкото место е назначено на тој избор, и дали entity_id учествувал во изборот. Ја имплементира бизнис логиката за интегритет на гласање — спречува невалидни или фалсификувани ливчиња да влезат во системот.
CREATE OR REPLACE FUNCTION public.fn_validate_ballot()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
v_status SMALLINT;
v_station_ok BOOLEAN;
v_entity_ok BOOLEAN;
BEGIN
-- Check (a): election must be active
SELECT status
INTO v_status
FROM election
WHERE election_id = NEW.election_id;
IF NOT FOUND THEN
RAISE EXCEPTION
'Election % does not exist', NEW.election_id;
END IF;
IF v_status <> 1 THEN
RAISE EXCEPTION
'Election % is not active (status=%). Ballots cannot be cast.',
NEW.election_id, v_status;
END IF;
-- Check (b): station must be in this election
SELECT EXISTS (
SELECT 1
FROM station_election
WHERE station_id = NEW.station_id
AND election_id = NEW.election_id
) INTO v_station_ok;
IF NOT v_station_ok THEN
RAISE EXCEPTION
'Polling station % is not assigned to election %.',
NEW.station_id, NEW.election_id;
END IF;
-- Check (c/d): entity validation for valid ballots
IF NEW.is_valid = TRUE THEN
IF NEW.entity_id IS NULL AND NEW.candidate_id IS NULL THEN
RAISE EXCEPTION
'A valid ballot must have either an entity_id or candidate_id.';
END IF;
IF NEW.entity_id IS NOT NULL THEN
SELECT EXISTS (
SELECT 1
FROM election_participant ep
JOIN electoral_district ed
ON ed.district_id = ep.district_id
WHERE ep.entity_id = NEW.entity_id
AND ed.election_id = NEW.election_id
) INTO v_entity_ok;
IF NOT v_entity_ok THEN
RAISE EXCEPTION
'Entity % did not participate in election %.',
NEW.entity_id, NEW.election_id;
END IF;
END IF;
END IF;
RETURN NEW;
END;
$function$;
-- Trigger definition
CREATE TRIGGER trg_validate_ballot
BEFORE INSERT ON public.ballot
FOR EACH ROW EXECUTE FUNCTION fn_validate_ballot();
2. trg_after_insert_ballot_live_count
Тип: AFTER INSERT на табела ballot
Се активира по секое внесување на валидно ливче и автоматски го ажурира бројачот во vote_result преку UPSERT — ако записот постои го зголемува за 1, инаку создава нов. Ја имплементира логиката за live бројање на гласови во реално време, без потреба од рачна агрегација на резултати.
CREATE OR REPLACE FUNCTION public.fn_trigger_live_vote_counter()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
v_result_id_exists BIGINT;
v_max_id BIGINT;
BEGIN
IF NEW.is_valid = TRUE THEN
SELECT result_id INTO v_result_id_exists
FROM public.vote_result
WHERE election_id = NEW.election_id
AND station_id = NEW.station_id
AND entity_id = NEW.entity_id
AND (candidate_id = NEW.candidate_id
OR (candidate_id IS NULL AND NEW.candidate_id IS NULL));
IF v_result_id_exists IS NOT NULL THEN
UPDATE public.vote_result
SET votes = votes + 1
WHERE result_id = v_result_id_exists;
ELSE
SELECT COALESCE(MAX(result_id), 0) + 1
INTO v_max_id
FROM public.vote_result;
INSERT INTO public.vote_result
(result_id, election_id, station_id, entity_id, candidate_id, votes)
VALUES
(v_max_id, NEW.election_id, NEW.station_id, NEW.entity_id, NEW.candidate_id, 1);
END IF;
END IF;
RETURN NEW;
END;
$function$;
-- Trigger definition
CREATE TRIGGER trg_after_insert_ballot_live_count
AFTER INSERT ON public.ballot
FOR EACH ROW EXECUTE FUNCTION fn_trigger_live_vote_counter();
3. trg_before_insert_voter_age
Тип: BEFORE INSERT на табела voter
Се активира пред регистрација на нов гласач и проверува дали лицето е полнолетно (≥18 години), фрлајќи детален исклучок со името на лицето ако не е. Директно ја имплементира законската забрана за гласање на малолетни лица на ниво на базата.
CREATE OR REPLACE FUNCTION public.fn_check_voter_age_limit()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
v_birth_date DATE;
v_full_name TEXT;
BEGIN
SELECT date_of_birth, (name || ' ' || surname)
INTO v_birth_date, v_full_name
FROM public.person
WHERE person_id = NEW.person_id;
IF NOT FOUND THEN
RAISE EXCEPTION
'Грешка: Лицето со ID % не постои во матичната евиденција (табела person).',
NEW.person_id;
END IF;
IF v_birth_date > CURRENT_DATE - INTERVAL '18 years' THEN
RAISE EXCEPTION
'Критична грешка: Лицето "%" (ID: %) има % години и е малолетно. '
'Регистрацијата во базата на гласачи е ОДБИЕНА.',
v_full_name,
NEW.person_id,
EXTRACT(YEAR FROM AGE(CURRENT_DATE, v_birth_date));
END IF;
RETURN NEW;
END;
$function$;
-- Trigger definition
CREATE TRIGGER trg_before_insert_voter_age
BEFORE INSERT ON public.voter
FOR EACH ROW EXECUTE FUNCTION fn_check_voter_age_limit();
4. trg_after_voter_change_sync_stats
Тип: AFTER INSERT OR DELETE на табела voter
Автоматски го ажурира registered_voter бројачот во polling_station при секоја промена на гласачкиот список — +1 при регистрација, -1 при бришење. Ја одржува конзистентноста на статистиките за регистрирани гласачи без рачно одржување.
CREATE OR REPLACE FUNCTION public.fn_trigger_sync_registered_voters()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE public.polling_station
SET registered_voter = COALESCE(registered_voter, 0) + 1
WHERE station_id = NEW.station_id;
ELSIF TG_OP = 'DELETE' THEN
UPDATE public.polling_station
SET registered_voter = COALESCE(registered_voter, 0) - 1
WHERE station_id = OLD.station_id;
END IF;
RETURN NULL;
END;
$function$;
-- Trigger definition
CREATE TRIGGER trg_after_voter_change_sync_stats
AFTER INSERT OR DELETE ON public.voter
FOR EACH ROW EXECUTE FUNCTION fn_trigger_sync_registered_voters();
5. trg_prevent_duplicate_voter_checkin
Тип: BEFORE INSERT на табела voter_election
Проверува три услови: изборот е активен, гласачот не се пријавил веќе на тој избор, и check-in е на неговата матична станица. Ја имплементира основната изборна контрола против двојно гласање и гласање надвор од матичното место.
CREATE OR REPLACE FUNCTION public.fn_prevent_duplicate_voter_checkin()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
v_registered_station BIGINT;
v_election_status SMALLINT;
v_election_name VARCHAR;
v_voter_name TEXT;
BEGIN
-- (1) Изборот мора да биде активен
SELECT e.status, e.name
INTO v_election_status, v_election_name
FROM election e
WHERE e.election_id = NEW.election_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Election % does not exist.', NEW.election_id;
END IF;
IF v_election_status <> 1 THEN
RAISE EXCEPTION
'Election "%" is not active. Voter check-in is not allowed.',
v_election_name;
END IF;
-- (2) Гласачот не смее двапати да се пријави
IF EXISTS (
SELECT 1
FROM voter_election ve
WHERE ve.voter_id = NEW.voter_id
AND ve.election_id = NEW.election_id
) THEN
SELECT (p.name || ' ' || p.surname)
INTO v_voter_name
FROM voter v
JOIN person p ON p.person_id = v.person_id
WHERE v.voter_id = NEW.voter_id;
RAISE EXCEPTION
'Voter "%" (id: %) has already checked in for election "%".',
v_voter_name, NEW.voter_id, v_election_name;
END IF;
-- (3) Check-in мора да биде на матичната станица
SELECT v.station_id
INTO v_registered_station
FROM voter v
WHERE v.voter_id = NEW.voter_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Voter % does not exist.', NEW.voter_id;
END IF;
IF v_registered_station <> NEW.station_id THEN
RAISE EXCEPTION
'Voter % must check in at their registered station (%). '
'Attempted check-in at station %.',
NEW.voter_id, v_registered_station, NEW.station_id;
END IF;
RETURN NEW;
END;
$function$;
-- Trigger definition
CREATE TRIGGER trg_prevent_duplicate_voter_checkin
BEFORE INSERT ON public.voter_election
FOR EACH ROW EXECUTE FUNCTION fn_prevent_duplicate_voter_checkin();
Функции
6. fn_calculate_dhondt
Сигнатура: fn_calculate_dhondt(p_election_id bigint, p_district_id bigint)
Враќа: TABLE(round, entity_id, entity_name, votes, divisor, quotient, seat_awarded)
Ја пресметува распределбата на мандати по D'Hondt метод за даден избор и изборна единица — генерира табела со сите квоциенти, дивизори, позиции и флаг дали е доделен мандат. Ја имплементира пропорционалната изборна математика која е законски задолжителна за парламентарни избори во Македонија и Србија, со вграден 5% праг на влез.
CREATE OR REPLACE FUNCTION public.fn_calculate_dhondt(p_election_id bigint, p_district_id bigint)
RETURNS TABLE(round bigint, entity_id bigint, entity_name character varying,
votes bigint, divisor integer, quotient numeric, seat_awarded boolean)
LANGUAGE plpgsql
AS $function$
DECLARE
v_seats INTEGER;
v_method_id BIGINT;
v_election_name VARCHAR;
v_region_name VARCHAR;
BEGIN
SELECT e.winner_method_id, e.name
INTO v_method_id, v_election_name
FROM election e
WHERE e.election_id = p_election_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Election % does not exist', p_election_id;
END IF;
IF v_method_id <> 8 THEN
RAISE EXCEPTION
'Election "%" does not use D''Hondt method. '
'This function only works for proportional elections.',
v_election_name;
END IF;
SELECT ed.seats_available, r.name
INTO v_seats, v_region_name
FROM electoral_district ed
JOIN region r ON r.region_id = ed.region_id
WHERE ed.district_id = p_district_id
AND ed.election_id = p_election_id;
IF NOT FOUND THEN
RAISE EXCEPTION
'District % does not exist for election %',
p_district_id, p_election_id;
END IF;
IF v_seats IS NULL OR v_seats = 0 THEN
RAISE EXCEPTION 'District % has no seats defined', p_district_id;
END IF;
RAISE NOTICE 'Calculating D''Hondt for: % — % (% seats)',
v_election_name, v_region_name, v_seats;
RETURN QUERY
WITH
-- Чекор 1: гласови по ентитет во дистриктот
raw_votes AS (
SELECT
COALESCE(pe2.entity_id, vr.entity_id) AS entity_id,
SUM(vr.votes) AS district_votes
FROM vote_result vr
JOIN polling_station ps ON ps.station_id = vr.station_id
JOIN region r2 ON r2.region_id = ps.municipality_id
JOIN political_entity pe ON pe.entity_id = vr.entity_id
LEFT JOIN coalition_member cm ON cm.party_id = pe.party_id
LEFT JOIN party_coalition pc ON pc.coalition_id = cm.coalition_id
AND pc.election_id = vr.election_id
LEFT JOIN political_entity pe2 ON pe2.coalition_id = pc.coalition_id
WHERE vr.election_id = p_election_id
AND vr.candidate_id IS NULL
AND (
r2.region_id = (SELECT region_id FROM electoral_district
WHERE district_id = p_district_id)
OR
r2.parent_region_id = (SELECT region_id FROM electoral_district
WHERE district_id = p_district_id)
)
GROUP BY COALESCE(pe2.entity_id, vr.entity_id)
),
-- Чекор 2: филтрирање на ентитети над 5% праг
district_total AS (
SELECT SUM(district_votes) AS total FROM raw_votes
),
eligible AS (
SELECT rv.entity_id, rv.district_votes
FROM raw_votes rv
CROSS JOIN district_total dt
WHERE rv.district_votes * 100.0 / NULLIF(dt.total, 0) >= 5.0
),
-- Чекор 3: генерирање на сите квоциенти
quotients AS (
SELECT
e.entity_id,
e.district_votes,
d.divisor,
ROUND(e.district_votes::NUMERIC / d.divisor, 4) AS quotient
FROM eligible e
CROSS JOIN generate_series(1, v_seats) AS d(divisor)
),
-- Чекор 4: рангирање на сите квоциенти
ranked AS (
SELECT
q.*,
RANK() OVER (ORDER BY q.quotient DESC) AS global_rank
FROM quotients q
)
-- Чекор 5: враќање со флаг за доделен мандат
SELECT
r.global_rank AS round,
r.entity_id,
pe.name AS entity_name,
r.district_votes AS votes,
r.divisor,
r.quotient,
(r.global_rank <= v_seats) AS seat_awarded
FROM ranked r
JOIN political_entity pe ON pe.entity_id = r.entity_id
ORDER BY r.global_rank, r.entity_id;
END;
$function$;
7. fn_get_candidate_list_for_entity
Сигнатура: fn_get_candidate_list_for_entity(p_entity_id bigint, p_election_id bigint)
Враќа: TABLE(list_position, candidate_id, full_name, date_of_birth, gender, municipality, party_name, entity_name, election_name)
Ја враќа целосната листа на кандидати за даден политички ентитет на даден избор, подредена по позиција, со лични податоци, партиска припадност и општина. Ја имплементира логиката за јавна транспарентност — граѓаните и медиумите можат да видат кој е на каква позиција на изборната листа.
CREATE OR REPLACE FUNCTION public.fn_get_candidate_list_for_entity(p_entity_id bigint, p_election_id bigint)
RETURNS TABLE(list_position integer, candidate_id bigint, full_name text,
date_of_birth date, gender character, municipality character varying,
party_name character varying, entity_name character varying,
election_name character varying)
LANGUAGE plpgsql
AS $function$
DECLARE
v_entity_name VARCHAR;
v_election_name VARCHAR;
v_list_id BIGINT;
BEGIN
SELECT pe.name INTO v_entity_name
FROM political_entity pe
WHERE pe.entity_id = p_entity_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Political entity % does not exist.', p_entity_id;
END IF;
SELECT e.name INTO v_election_name
FROM election e
WHERE e.election_id = p_election_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Election % does not exist.', p_election_id;
END IF;
SELECT cl.list_id INTO v_list_id
FROM candidate_list cl
WHERE cl.entity_id = p_entity_id
AND cl.election_id = p_election_id;
IF NOT FOUND THEN
RAISE EXCEPTION
'No candidate list found for entity "%" on election "%".',
v_entity_name, v_election_name;
END IF;
RAISE NOTICE 'Candidate list for "%" — Election: "%" (list_id: %)',
v_entity_name, v_election_name, v_list_id;
RETURN QUERY
SELECT
cli.position AS list_position,
c.candidate_id,
(p.name || ' ' || p.surname)::TEXT AS full_name,
p.date_of_birth,
p.gender,
COALESCE(r.name, 'No municipality data') AS municipality,
COALESCE(
(
SELECT pp.name
FROM candidate_party cp
JOIN political_party pp ON pp.party_id = cp.party_id
WHERE cp.candidate_id = c.candidate_id
AND cp.election_id = p_election_id
ORDER BY cp.candidate_party_id
LIMIT 1
),
v_entity_name
) AS party_name,
v_entity_name AS entity_name,
v_election_name AS election_name
FROM candidate_list_item cli
JOIN candidate c ON c.candidate_id = cli.candidate_id
JOIN person p ON p.person_id = c.person_id
LEFT JOIN region r ON r.region_id = p.municipality_id
WHERE cli.list_id = v_list_id
ORDER BY cli.position ASC;
END;
$function$;
8. fn_station_anomaly_score
Сигнатура: fn_station_anomaly_score(p_station_id bigint, p_election_id bigint)
Враќа: TABLE(station_id, station_name, election_name, registered_voters, ballots_cast, valid_ballots, invalid_ballots, turnout_pct, invalid_pct, total_votes_vr, anomaly_score, flag_high_turnout, flag_impossible_turnout, flag_high_invalid, flag_no_ballots, flag_impossible_votes, recommendation)
Ја анализира статистиката на гласачко место и пресметува anomaly_score врз основа на 5 флагови: висока излезност (>95%), невозможна излезност (>100%), многу невалидни ливчиња (>10%), нула ливчиња, и гласови поголеми од регистрираните гласачи. Ја имплементира автоматската детекција на потенцијални изборни измами или грешки во внесот со конкретна препорака за акција.
| Anomaly Score | Препорака |
|---|---|
| 0 | OK — No anomalies detected. |
| 1–2 | MINOR — Monitor this station. |
| 3–5 | WARNING — Manual review recommended. |
| >5 | CRITICAL — Immediate investigation required. |
CREATE OR REPLACE FUNCTION public.fn_station_anomaly_score(p_station_id bigint, p_election_id bigint)
RETURNS TABLE(station_id bigint, station_name character varying, election_name character varying,
registered_voters integer, ballots_cast bigint, valid_ballots bigint,
invalid_ballots bigint, turnout_pct numeric, invalid_pct numeric,
total_votes_vr bigint, anomaly_score integer,
flag_high_turnout boolean, flag_impossible_turnout boolean,
flag_high_invalid boolean, flag_no_ballots boolean,
flag_impossible_votes boolean, recommendation text)
LANGUAGE plpgsql
AS $function$
DECLARE
v_station_name VARCHAR;
v_election_name VARCHAR;
v_registered INT;
v_ballots BIGINT;
v_valid BIGINT;
v_invalid BIGINT;
v_votes_vr BIGINT;
v_turnout NUMERIC;
v_invalid_pct NUMERIC;
v_score INT := 0;
v_flag_turnout BOOLEAN := FALSE;
v_flag_impossible BOOLEAN := FALSE;
v_flag_invalid BOOLEAN := FALSE;
v_flag_no_ballot BOOLEAN := FALSE;
v_flag_imp_votes BOOLEAN := FALSE;
v_recommendation TEXT;
BEGIN
SELECT ps.name, ps.registered_voter
INTO v_station_name, v_registered
FROM polling_station ps
WHERE ps.station_id = p_station_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Polling station % does not exist.', p_station_id;
END IF;
SELECT e.name INTO v_election_name
FROM election e
WHERE e.election_id = p_election_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Election % does not exist.', p_election_id;
END IF;
IF NOT EXISTS (
SELECT 1 FROM station_election se
WHERE se.station_id = p_station_id
AND se.election_id = p_election_id
) THEN
RAISE EXCEPTION 'Station % is not assigned to election %.',
p_station_id, p_election_id;
END IF;
SELECT
COUNT(*),
COUNT(*) FILTER (WHERE b.is_valid = TRUE),
COUNT(*) FILTER (WHERE b.is_valid = FALSE)
INTO v_ballots, v_valid, v_invalid
FROM ballot b
WHERE b.station_id = p_station_id
AND b.election_id = p_election_id;
SELECT COALESCE(SUM(vr.votes), 0)
INTO v_votes_vr
FROM vote_result vr
WHERE vr.station_id = p_station_id
AND vr.election_id = p_election_id;
v_turnout := ROUND(
v_ballots::NUMERIC * 100.0 / NULLIF(COALESCE(v_registered, 0), 0), 2);
v_invalid_pct := ROUND(
v_invalid::NUMERIC * 100.0 / NULLIF(v_ballots, 0), 2);
-- Flag 1: Излезност > 95%
IF v_turnout > 95.0 THEN
v_flag_turnout := TRUE;
v_score := v_score + 3;
END IF;
-- Flag 2: Излезност > 100% (невозможно)
IF v_turnout > 100.0 THEN
v_flag_impossible := TRUE;
v_score := v_score + 10;
END IF;
-- Flag 3: Невалидни > 10%
IF v_invalid_pct > 10.0 THEN
v_flag_invalid := TRUE;
v_score := v_score + 2;
END IF;
-- Flag 4: Нула балоти
IF v_ballots = 0 THEN
v_flag_no_ballot := TRUE;
v_score := v_score + 2;
END IF;
-- Flag 5: Гласови во vote_result > регистрирани гласачи
IF v_votes_vr > COALESCE(v_registered, 0) THEN
v_flag_imp_votes := TRUE;
v_score := v_score + 5;
END IF;
v_recommendation := CASE
WHEN v_score = 0 THEN 'OK — No anomalies detected.'
WHEN v_score BETWEEN 1 AND 2 THEN 'MINOR — Monitor this station.'
WHEN v_score BETWEEN 3 AND 5 THEN 'WARNING — Manual review recommended.'
ELSE 'CRITICAL — Immediate investigation required.'
END;
RETURN QUERY
SELECT
p_station_id, v_station_name, v_election_name,
v_registered, v_ballots, v_valid, v_invalid,
COALESCE(v_turnout, 0), COALESCE(v_invalid_pct, 0),
v_votes_vr, v_score,
v_flag_turnout, v_flag_impossible, v_flag_invalid,
v_flag_no_ballot, v_flag_imp_votes, v_recommendation;
END;
$function$;
Процедури
9. sp_close_election
Сигнатура: sp_close_election(IN p_election_id bigint)
Ги верифицира резултатите, го наоѓа победникот со процент на гласови, го затвора изборот (status=0) и логира детален извештај. Ја имплементира официјалната процедура за завршување на изборен циклус — само избори со внесени резултати можат да се затворат.
CREATE OR REPLACE PROCEDURE public.sp_close_election(IN p_election_id bigint)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_election_name VARCHAR;
v_election_type VARCHAR;
v_status SMALLINT;
v_station_count INT;
v_total_votes BIGINT;
v_winner_name VARCHAR;
v_winner_votes BIGINT;
v_winner_pct NUMERIC;
BEGIN
-- (1) Провери дали изборот постои
SELECT e.name, e.status, et.type_name
INTO v_election_name, v_status, v_election_type
FROM election e
JOIN election_type et ON et.election_type_id = e.election_type_id
WHERE e.election_id = p_election_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Election % does not exist.', p_election_id;
END IF;
-- (2) Провери дали е активен
IF v_status = 0 THEN
RAISE EXCEPTION 'Election "%" is already closed.', v_election_name;
END IF;
-- (3) Провери дали постојат vote_result записи
SELECT COUNT(DISTINCT vr.station_id), SUM(vr.votes)
INTO v_station_count, v_total_votes
FROM vote_result vr
WHERE vr.election_id = p_election_id
AND vr.candidate_id IS NULL;
IF v_total_votes IS NULL OR v_total_votes = 0 THEN
RAISE EXCEPTION
'Election "%" has no vote results. Cannot close an election without results.',
v_election_name;
END IF;
-- Најди победник
SELECT pe.name, SUM(vr.votes),
ROUND(SUM(vr.votes)::NUMERIC * 100.0 / NULLIF(v_total_votes, 0), 2)
INTO v_winner_name, v_winner_votes, v_winner_pct
FROM vote_result vr
JOIN political_entity pe ON pe.entity_id = vr.entity_id
WHERE vr.election_id = p_election_id
AND vr.candidate_id IS NULL
GROUP BY pe.name
ORDER BY SUM(vr.votes) DESC
LIMIT 1;
-- Затвори го изборот
UPDATE election SET status = 0 WHERE election_id = p_election_id;
RAISE NOTICE '════════════════════════════════════════';
RAISE NOTICE 'Election closed: % (%)', v_election_name, v_election_type;
RAISE NOTICE 'Stations reported : %', v_station_count;
RAISE NOTICE 'Total votes : %', v_total_votes;
RAISE NOTICE 'Winner : %', v_winner_name;
RAISE NOTICE 'Winner votes : %', v_winner_votes;
RAISE NOTICE 'Winner share : % pct', v_winner_pct;
RAISE NOTICE '════════════════════════════════════════';
END;
$procedure$;
10. sp_open_election
Сигнатура: sp_open_election(IN p_election_id bigint)
Го активира изборот (status=1) по верификација дека не постои веќе активен избор од ист тип во системот. Ја имплементира бизнис правилото дека не можат да се одржуваат два истовидни избори истовремено.
CREATE OR REPLACE PROCEDURE public.sp_open_election(IN p_election_id bigint)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_election_name VARCHAR;
v_type_id BIGINT;
v_status SMALLINT;
v_same_type_active BOOLEAN;
BEGIN
SELECT name, status, election_type_id
INTO v_election_name, v_status, v_type_id
FROM public.election
WHERE election_id = p_election_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Грешка: Изборот со ID % не постои.', p_election_id;
END IF;
IF v_status = 1 THEN
RAISE EXCEPTION
'Забелешка: Изборот "%" е веќе отворен и активен.', v_election_name;
END IF;
SELECT EXISTS (
SELECT 1 FROM public.election
WHERE status = 1 AND election_type_id = v_type_id
) INTO v_same_type_active;
IF v_same_type_active THEN
RAISE EXCEPTION
'Предупредување: Веќе постојат активни избори од овој тип во системот. '
'Мора прво да ги затворите претходните од истата категорија.';
END IF;
UPDATE public.election SET status = 1 WHERE election_id = p_election_id;
RAISE NOTICE '========================================';
RAISE NOTICE 'Изборот "%" е УСПЕШНО ОТВОРЕН!', v_election_name;
RAISE NOTICE 'Овозможено е истовремено гласање со други активни различни типови избори.';
RAISE NOTICE '========================================';
END;
$procedure$;
11. sp_register_newly_eligible_voters
Сигнатура: sp_register_newly_eligible_voters()
Ги регистрира сите лица кои наполниле 18 години а сè уште не се во гласачкиот список, и ги распоредува на најмалку оптоварената станица во batch-ови од 10,000. Ја автоматизира периодичната ажурирање на гласачкиот список која државните институции ја вршат пред секои избори.
CREATE OR REPLACE PROCEDURE public.sp_register_newly_eligible_voters()
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_count INT := 0;
v_max_voter_id BIGINT;
v_station_id BIGINT;
v_region_id BIGINT;
rec RECORD;
BEGIN
SELECT COALESCE(MAX(voter_id), 0)
INTO v_max_voter_id
FROM voter;
-- Најди најмалку оптоварена станица
SELECT ps.station_id, ps.municipality_id
INTO v_station_id, v_region_id
FROM polling_station ps
LEFT JOIN voter v ON v.station_id = ps.station_id
GROUP BY ps.station_id, ps.municipality_id, ps.registered_voter
ORDER BY COUNT(v.voter_id) * 1.0 / NULLIF(ps.registered_voter, 1) ASC
LIMIT 1;
IF v_station_id IS NULL THEN
RAISE EXCEPTION 'No polling stations found in the system';
END IF;
FOR rec IN
SELECT p.person_id
FROM person p
WHERE p.date_of_birth <= CURRENT_DATE - INTERVAL '18 years'
AND p.person_id NOT IN (SELECT person_id FROM voter)
ORDER BY p.person_id
LOOP
v_max_voter_id := v_max_voter_id + 1;
INSERT INTO voter (voter_id, person_id, region_id, station_id)
VALUES (v_max_voter_id, rec.person_id, v_region_id, v_station_id)
ON CONFLICT (person_id) DO NOTHING;
v_count := v_count + 1;
-- Commit во batch-ови од 10,000
IF v_count % 10000 = 0 THEN
RAISE NOTICE 'Registered % voters so far...', v_count;
END IF;
END LOOP;
RAISE NOTICE 'Done. Total newly registered voters: %', v_count;
END;
$procedure$;
Note:
See TracWiki
for help on using the wiki.
