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

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;
$$;