Погледи (Views)

Употребуваните погледи подолу се со цел да се олесни користењето на апликацијата и истите се вметнати во база, за да не се повлекуваат податоци во постојаност со извршување на исто query.

1. MedicalReport View

CREATE OR REPLACE VIEW medical_report_view AS
select
    r.report_id AS report_id,
    summary,
    r.created_at AS report_created_at,
    p.embg AS embg_of_person,
    (p.name || ' ' || p.surname) AS person_fullname,
    p.gender,
    p.date_of_birth,
    p.date_of_death,
    p.address AS address_of_living,
    p.contact_phone,
    r.report_type AS type_of_report,
    added_on as diagnosis_creation_date,
    short_description as diagnosis_description,
    is_chronic,
    severity,
    therapy as therapy_for_diagnosis,
    next_control_date,
    ('Dr. ' || doc.name || ' ' || doc.surname) AS doctor_fullname,
    specialization as doctor_specialization,
    is_active as is_doctor_still_active,
    years_of_experience
from report r join person p on p.person_id = r.person_id
join medicalreport mr on mr.report_id = r.report_id
join medicalreport_diagnosis mrd on mr.report_id = mrd.report_id
join diagnosis d on mrd.diagnosis_id = d.diagnosis_id
join doctor doc on doc.doctor_id = mr.doctor_id;

2. EmploymentReport View

CREATE OR REPLACE VIEW employment_report_view AS
SELECT
    r.report_id AS report_id,
    r.summary,
    r.created_at AS report_created_at,
    p.embg AS embg_of_person,
    (p.name || ' ' || p.surname) AS person_fullname,
    p.gender,
    p.date_of_birth,
    p.date_of_death,
    p.address AS address_of_living,
    p.contact_phone,
    r.report_type AS type_of_report,
    er.start_date AS started_working_date,
    er.end_date AS ended_working_date,
    er.job_role,
    er.income_per_month AS income_per_month_in_euros
FROM report r
JOIN person p ON p.person_id = r.person_id
JOIN employmentreport er ON er.report_id = r.report_id;

3. AcademicReport View

CREATE OR REPLACE VIEW academic_report_view AS
select
    r.report_id AS report_id,
    summary,
    r.created_at AS report_created_at,
    p.embg AS embg_of_person,
    (p.name || ' ' || p.surname) AS person_fullname,
    p.gender,
    p.date_of_birth,
    p.date_of_death,
    p.address AS address_of_living,
    p.contact_phone,
    r.report_type AS type_of_report,
    academic_field,
    description_of_report as academic_report_description,
    i.name as institution_name,
    i.address as institution_address,
    year_established as institution_year_of_establishing,
    city as city_where_educating,
    type as type_of_education
from report r
join person p on p.person_id = r.person_id
join academicreport ar on ar.report_id = r.report_id
join institution i on ar.institution_id = i.institution_id;

4. CriminalReport View

CREATE OR REPLACE VIEW criminal_report_view AS
select
    r.report_id AS report_id,
    summary,
    r.created_at AS report_created_at,
    p.embg AS embg_of_person,
    (p.name || ' ' || p.surname) AS person_fullname,
    p.gender,
    p.date_of_birth,
    p.date_of_death,
    p.address AS address_of_living,
    p.contact_phone,
    r.report_type AS type_of_report,
    label as type_of_criminal,
    location as where_criminal_is_reported,
    resolved as is_resolved,
    descriptive_punishment,
    severity_level,
    CASE
        WHEN punishment_type = 'fine' AND value_unit = 'euros' THEN
            'Fine: ' || fine_to_pay || ' €'
        WHEN punishment_type = 'prison' AND value_unit = 'years' THEN
            'Release Date: ' || COALESCE(TO_CHAR(release_date, 'YYYY-MM-DD'), 'N/A')
        ELSE
            'Unknown Punishment'
    END AS punishment
from report r
join person p on p.person_id = r.person_id
join criminalreport cr on cr.report_id = r.report_id
join crimetype ct on ct.crime_type_id = cr.crime_type_id
join punishment punish on r.report_id = punish.report_id;