Напредни Процедури

1. Складирана процедура за додавање на на нов MedicalReport објект за конкретен Person запис

CREATE OR REPLACE PROCEDURE insert_medical_report(
    IN param_person_id INT,
    IN param_summary TEXT,
    IN param_doctor_id INT,
    IN param_next_control_date DATE,
    IN param_diagnosis_ids INT[]
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    new_report_id         INT;
    variable_diagnosis_id INT;
BEGIN
    IF NOT EXISTS (SELECT 1
                   FROM person
                   WHERE person_id = param_person_id) THEN
        RAISE EXCEPTION 'Person with ID % does not exist', param_person_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM doctor WHERE doctor_id = param_doctor_id) THEN
        RAISE EXCEPTION 'Doctor with ID % does not exist', param_doctor_id;
    END IF;

    INSERT INTO report (report_type, summary, created_at, person_id)
    VALUES ('Medical', param_summary, CURRENT_TIMESTAMP, param_person_id)
    RETURNING report_id INTO new_report_id;

    INSERT INTO medicalreport (report_id, doctor_id, next_control_date)
    VALUES (new_report_id, param_doctor_id, param_next_control_date);

    IF param_diagnosis_ids IS NOT NULL THEN
        FOREACH variable_diagnosis_id IN ARRAY param_diagnosis_ids
            LOOP
                IF NOT EXISTS (SELECT 1 FROM diagnosis WHERE variable_diagnosis_id = variable_diagnosis_id) THEN
                    RAISE EXCEPTION 'Diagnosis ID % does not exist', variable_diagnosis_id;
                END IF;
                INSERT INTO medicalreport_diagnosis (report_id, diagnosis_id, added_on)
                VALUES (new_report_id, variable_diagnosis_id, CURRENT_TIMESTAMP);
            END LOOP;
    END IF;
END;
$$;

2. Складирана процедура за додавање на на нов EmploymentReport објект за конкретен Person запис

CREATE OR REPLACE PROCEDURE insert_employment_report(
    IN param_person_id INT,
    IN param_start_date DATE,
    IN param_end_date DATE,
    IN param_job_role TEXT,
    IN param_income NUMERIC,
    IN param_summary TEXT
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    new_report_id INT;
BEGIN
    IF NOT EXISTS (SELECT 1
                   FROM person
                   WHERE person_id = param_person_id) THEN
        RAISE EXCEPTION 'Person with ID % does not exist', param_person_id;
    END IF;

    IF param_end_date is not null and param_end_date < param_start_date then
        raise exception 'End date can not be before starting date';
    end if;

    IF param_income is not null and param_income <= 0 then
        raise exception 'Income must be greater than 0';
    end if;

    INSERT INTO report (report_type, summary, created_at, person_id)
    VALUES ('Employment', param_summary, CURRENT_TIMESTAMP, param_person_id)
    RETURNING report_id INTO new_report_id;

    INSERT INTO employmentreport (report_id, start_date, end_date, job_role, income_per_month)
    VALUES (new_report_id,
            param_start_date,
            param_end_date,
            param_job_role,
            param_income);
END;
$$;

3. Складирана процедура за додавање на на нов CriminalReport објект за конкретен Person запис

CREATE OR REPLACE PROCEDURE insert_criminal_report(
    IN param_person_id INT,
    IN param_case_summary TEXT,
    IN param_location TEXT,
    IN param_is_resolved BOOLEAN,
    IN param_crime_type_id INT,
    IN param_punishment_type TEXT,
    IN param_fine_to_pay NUMERIC,
    IN param_release_date DATE
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    new_report_id     INT;
    new_punishment_id INT;
BEGIN
    IF NOT EXISTS (SELECT 1
                   FROM person
                   WHERE person_id = param_person_id) THEN
        RAISE EXCEPTION 'Person with ID % does not exist', param_person_id;
    END IF;

    IF NOT EXISTS (SELECT 1
                   FROM crimetype
                   WHERE crime_type_id = param_crime_type_id) THEN
        RAISE EXCEPTION 'CrimeType with ID % does not exist', param_crime_type_id;
    END IF;

    INSERT INTO report (report_type, summary, created_at, person_id)
    VALUES ('Criminal', param_case_summary, CURRENT_TIMESTAMP, param_person_id)
    RETURNING report_id INTO new_report_id;

    INSERT INTO criminalreport (report_id, location, resolved, crime_type_id)
    VALUES (new_report_id, param_location, param_is_resolved, param_crime_type_id);

    IF param_punishment_type = 'PRISON' AND param_release_date IS NOT NULL THEN
        INSERT INTO punishment (report_id, value_unit, punishment_type, fine_to_pay, release_date)
        VALUES (new_report_id, 'years', LOWER(param_punishment_type), NULL, param_release_date)
        RETURNING punishment_id INTO new_punishment_id;
    END IF;

    IF param_punishment_type = 'FINE' AND param_fine_to_pay IS NOT NULL THEN
        INSERT INTO punishment (report_id, value_unit, punishment_type, fine_to_pay, release_date)
        VALUES (new_report_id, 'euros', LOWER(param_punishment_type), param_fine_to_pay, NULL)
        RETURNING punishment_id INTO new_punishment_id;
    END IF;

END;
$$;

4. Складирана процедура за додавање на на нов AcademicReport објект за конкретен Person запис

CREATE OR REPLACE PROCEDURE insert_academic_report(
    IN param_person_id INT,
    IN param_institution_id INT,
    IN param_academic_field TEXT,
    IN param_description_of_report TEXT
)
    LANGUAGE plpgsql
as
$$
DECLARE
    new_report_id INT;
BEGIN
    IF NOT EXISTS (SELECT 1
                   FROM person
                   WHERE person_id = param_person_id) THEN
        RAISE EXCEPTION 'Person with ID % does not exist', param_person_id;
    END IF;
    IF NOT EXISTS (SELECT 1
                   FROM institution
                   WHERE institution_id = param_institution_id) THEN
        RAISE EXCEPTION 'Institution with ID % does not exist', param_institution_id;
    END IF;

    INSERT INTO report (report_type, summary, created_at, person_id)
    VALUES ('Academic', param_description_of_report, CURRENT_TIMESTAMP, param_person_id)
    RETURNING report_id INTO new_report_id;

    INSERT INTO academicreport (report_id, institution_id, academic_field, description_of_report)
    VALUES (new_report_id, param_institution_id, param_academic_field, param_description_of_report);
END;
$$;

Напреден извештај за извлекување на статистики за еден Person објект

WITH
    selected_person_reports AS (
        SELECT * FROM report WHERE person_id = :person_id
    ),

    academic AS (
        SELECT r.created_at, ar.academic_field, ar.report_id
        FROM report r
                 JOIN academicreport ar ON r.report_id = ar.report_id
                 JOIN institution i ON i.institution_id = ar.institution_id
        WHERE r.person_id = :person_id
    ),

    employment AS (
        SELECT e.start_date, COALESCE(e.end_date, CURRENT_DATE) AS end_date, e.income_per_month
        FROM employmentreport e
                 JOIN report r ON r.report_id = e.report_id
        WHERE r.person_id = :person_id
    ),

    medical AS (
        SELECT d.short_description, d.is_chronic
        FROM report r
                 JOIN medicalreport mr ON r.report_id = mr.report_id
                 JOIN medicalreport_diagnosis mrd ON mrd.report_id = mr.report_id
                 JOIN diagnosis d ON d.diagnosis_id = mrd.diagnosis_id
        WHERE r.person_id = :person_id
    ),

    criminal AS (
        SELECT cr.descriptive_punishment, cr.resolved as is_resolved
        FROM report r
                 JOIN criminalreport cr ON r.report_id = cr.report_id
                 JOIN crimetype ct ON ct.crime_type_id = cr.crime_type_id
        WHERE r.person_id = :person_id
    ),

    ordered_academic_reports_by_date AS (
        SELECT
            ar.academic_field,
            r.created_at,
            ROW_NUMBER() OVER (PARTITION BY ar.academic_field ORDER BY r.created_at) AS row_num
        FROM report r
                 JOIN academicreport ar ON ar.report_id = r.report_id
        WHERE r.person_id = :person_id
    ),

    filtered_academic_pathway AS (
        SELECT academic_field, MIN(created_at) AS started_on
        FROM ordered_academic_reports_by_date
        GROUP BY academic_field
    ),

    employment_report_stats AS (
        SELECT
            CAST(SUM(ABS(COALESCE(e.end_date, CURRENT_DATE) - e.start_date)) AS BIGINT) AS total_working_in_days,
            CAST(CEIL(SUM(ABS(COALESCE(e.end_date, CURRENT_DATE) - e.start_date)) / 30.0) AS BIGINT) AS total_working_in_months,
            CAST(CEIL(SUM(ABS(COALESCE(e.end_date, CURRENT_DATE) - e.start_date)) / 365.0) AS BIGINT) AS total_working_in_years,
            CAST(MAX(COALESCE(e.end_date, CURRENT_DATE) - e.start_date) AS BIGINT) AS longest_job_days
        FROM employmentreport e
                 JOIN report r ON r.report_id = e.report_id
        WHERE r.person_id = :person_id
    )
SELECT
    -- General
    CAST((SELECT COUNT(*) FROM selected_person_reports) AS BIGINT) AS total_reports_found,
    CAST((SELECT created_at::date FROM selected_person_reports ORDER BY created_at ASC LIMIT 1) AS DATE) AS first_report_of_person,
    CAST((SELECT created_at::date FROM selected_person_reports ORDER BY created_at DESC LIMIT 1) AS DATE) AS latest_report_of_person,
    -- Academic
    CAST((SELECT COUNT(*) FROM academic) AS BIGINT) AS academic_total,
    CAST((SELECT academic_field
          FROM academic
          GROUP BY academic_field
          ORDER BY COUNT(*) DESC
        LIMIT 1) AS TEXT) AS most_common_field,
    CAST((SELECT STRING_AGG(academic_field, ' → ' ORDER BY started_on)
          FROM filtered_academic_pathway) AS TEXT) AS education_path,
    -- Employment
    CAST((SELECT COUNT(*) FROM employment) AS BIGINT) AS job_count,
    (SELECT total_working_in_days FROM employment_report_stats),
    (SELECT total_working_in_months FROM employment_report_stats),
    (SELECT total_working_in_years FROM employment_report_stats),
    (SELECT longest_job_days FROM employment_report_stats),
    CAST((SELECT MAX(income_per_month) FROM employment) AS DOUBLE PRECISION) AS max_income_from_job,
    -- Medical
    CAST((SELECT COUNT(*) FROM medical) AS BIGINT) AS diagnosis_total,
    CAST((SELECT ROUND(SUM(CASE WHEN is_chronic THEN 1 ELSE 0 END)::decimal / COUNT(*), 2)
          FROM medical) AS DOUBLE PRECISION) AS chronic_ratio,
    CAST((SELECT short_description
          FROM medical
          GROUP BY short_description
          ORDER BY COUNT(*) DESC
        LIMIT 1) AS TEXT) AS most_frequent_diagnosis,
    -- Criminal
    CAST((SELECT COUNT(*) FROM criminal) AS BIGINT) AS criminal_case_total,
    CAST((SELECT ROUND(SUM(CASE WHEN is_resolved THEN 1 ELSE 0 END)::decimal / COUNT(*), 2)
          FROM criminal) AS DOUBLE PRECISION) AS resolution_ratio;