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

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