Changes between Version 1 and Version 2 of AdvancedQueries

Timestamp:

08/24/25 20:44:42 (2 months ago)

Author:

221007

Comment:

--

AdvancedQueries

@@ -168 +168 @@
 }}}
 
+
+==== PERSON избор
+{{{
+SELECT r.report_id, r.created_at, r.person_id, r.report_type, r.summary
+FROM report r
+LEFT JOIN person p ON p.person_id = r.person_id
+WHERE 1=1
+  /* name contains */
+  AND (:namePattern IS NULL OR LOWER(p.name) LIKE :namePattern)
+  /* surname contains */
+  AND (:surnamePattern IS NULL OR LOWER(p.surname) LIKE :surnamePattern)
+  /* alive / not alive */
+  AND (
+        (:isAlive = TRUE  AND p.date_of_death IS NULL)
+     OR (:isAlive = FALSE AND p.date_of_death IS NOT NULL)
+  )
+  /* exact age (years) using Postgres functionalities */
+  AND (
+        :correctAge IS NULL OR :correctAge = 0 OR
+        date_part('year', age(current_date, p.date_of_birth)) = :correctAge
+  )
+  /* age range expressed via date_of_birth bounds (computed in app, with function) */
+  AND (:dobMax IS NULL OR p.date_of_birth <= :dobMax)
+  AND (:dobMin IS NULL OR p.date_of_birth >= :dobMin)
+  /* gender equals */
+  AND (:gender IS NULL OR p.gender = :gender)
+  /* address contains */
+  AND (:addrPattern IS NULL OR LOWER(p.address) LIKE :addrPattern);
+}}}
+
+==== ACADEMIC избор
+
+{{{
+SELECT r.report_id, r.created_at, r.person_id, r.report_type, r.summary
+FROM report r
+LEFT JOIN academicreport ar ON ar.report_id = r.report_id
+LEFT JOIN institution i ON i.institution_id = ar.institution_id
+WHERE 1=1
+  /* academic field contains */
+  AND (:fieldPattern IS NULL OR LOWER(ar.academic_field) LIKE :fieldPattern)
+  /* institution type equals */
+  AND (:instType IS NULL OR i.type = :instType);
+}}}
+
+==== CRIMINAL избор
+
+{{{
+SELECT r.report_id, r.created_at, r.person_id, r.report_type, r.summary
+FROM report r
+LEFT JOIN criminalreport cr ON cr.report_id = r.report_id
+LEFT JOIN crimetype ct ON ct.crime_type_id = cr.crime_type_id
+JOIN punishment p ON p.report_id = cr.report_id
+WHERE 1=1
+  /* severity level equals */
+  AND (:severityLevel IS NULL OR ct.severity_level = :severityLevel)
+  /* resolved flag equals */
+  AND (:isResolved IS NULL OR cr.resolved = :isResolved)
+  /* fine (applies when punishment_type = FINE) */
+  AND (:punType <> 'FINE' OR :fineAmount IS NULL OR p.fine_to_pay = :fineAmount)
+  /* prison years via years_total(created_at, release_date) (applies when punishment_type = PRISON) */
+  AND (:punType <> 'PRISON' OR :prisonYears IS NULL OR :prisonYears <= 0
+       OR years_total(r.created_at, p.release_date) = :prisonYears)
+  /* crime type label contains */
+  AND (:crimeLabelPattern IS NULL OR LOWER(ct.label) LIKE :crimeLabelPattern);
+}}}
+
+==== MEDICAL избор
+
+{{{
+SELECT r.report_id, r.created_at, r.person_id, r.report_type, r.summary
+FROM report r
+LEFT JOIN medicalreport mr ON mr.report_id = r.report_id
+LEFT JOIN doctor d         ON d.doctor_id = mr.doctor_id
+LEFT JOIN medicalreport_diagnosis mrd ON mrd.report_id = mr.report_id
+LEFT JOIN diagnosis diag   ON diag.diagnosis_id = mrd.diagnosis_id
+WHERE 1=1
+  /* next control presence (choose exactly one branch in code) */
+  AND (
+        (:hasNextControl = TRUE  AND mr.next_control_date IS NOT NULL) OR
+        (:hasNextControl = FALSE AND mr.next_control_date IS NULL) OR
+        (:hasNextControl IS NULL)
+      )
+  /* doctor name/surname contains */
+  AND (:docNamePattern    IS NULL OR LOWER(d.name)    LIKE :docNamePattern)
+  AND (:docSurnamePattern IS NULL OR LOWER(d.surname) LIKE :docSurnamePattern)
+  /* specialization equals */
+  AND (:specialization IS NULL OR d.specialization = :specialization)
+  /* chronic flag equals */
+  AND (:isChronic IS NULL OR diag.is_chronic = :isChronic);
+}}}
+
+==== EMPLOYMENT избор
+
+{{{
+SELECT r.report_id, r.created_at, r.person_id, r.report_type, r.summary
+FROM report r
+LEFT JOIN employmentreport e ON e.report_id = r.report_id
+WHERE 1=1
+  /* income comparison: more/equal/less (include only one branch) */
+  AND (
+       (:incomeCmp = 'more'  AND e.income_per_month >  :incomeAmount) OR
+       (:incomeCmp = 'equal' AND e.income_per_month =  :incomeAmount) OR
+       (:incomeCmp = 'less'  AND e.income_per_month <  :incomeAmount) OR
+       (:incomeCmp IS NULL)
+  )
+  /* years_total(start_date,end_date) custom DB function */
+  AND (
+       :yearsCmp IS NULL OR :yearsValue IS NULL OR :yearsValue <= 0 OR
+       (
+         (:yearsCmp = 'more'  AND years_total(e.start_date, e.end_date) >  :yearsValue) OR
+         (:yearsCmp = 'equal' AND years_total(e.start_date, e.end_date) =  :yearsValue) OR
+         (:yearsCmp = 'less'  AND years_total(e.start_date, e.end_date) <  :yearsValue)
+       )
+  );
+}}}