Changes between Version 1 and Version 2 of Views

Timestamp:

08/24/25 20:01:08 (9 days ago)

Author:

221007

Comment:

--

Views

@@ -4 +4 @@
 
 === 1. !MedicalReport View
-//todo
+
+{{{
+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
-//todo
+
+{{{
+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
-//todo
+{{{
+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
-//todo
+
+{{{
+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;
+}}}