From 4cce82764668745c49b18f08f6210e1d75793d26 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Tue, 23 Mar 2021 19:03:32 -0400 Subject: [PATCH] LP#1921057 - Expand reporter.demographic to include detailed age breakdown Add "age_division" column to reporter.demographic. Expose the new column to the reporter as "Detailed Age Division" resulting in the following options: Child 0-5 Years Old Child 6-12 Years Old Teen 13-17 Years Old Adult 18-25 Years Old Adult 50-59 Years Old Adult 50-59 Years Old Adult 60-69 Years Old Adult 70+ If no DOB is present, say so. Signed-off-by: Chris Sharp Signed-off-by: Ruth Frasur Signed-off-by: Galen Charlton --- Open-ILS/examples/fm_IDL.xml | 1 + Open-ILS/src/sql/Pg/reporter-schema.sql | 41 ++++++++++++++++------ .../XXXX.schema.reporter-demographic-expansion.sql | 38 ++++++++++++++++++++ 3 files changed, 70 insertions(+), 10 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.reporter-demographic-expansion.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 62c547138d..227619a60f 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -10959,6 +10959,7 @@ SELECT usr, + diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index ec1b6c8737..066071f7c7 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -257,16 +257,37 @@ CREATE OR REPLACE VIEW reporter.asset_call_number_dewey AS WHERE call_number_dewey(label) ~ '^[0-9]'::text; CREATE OR REPLACE VIEW reporter.demographic AS -SELECT u.id, - u.dob, - CASE - WHEN u.dob IS NULL - THEN 'Adult' - WHEN AGE(u.dob) > '18 years'::INTERVAL - THEN 'Adult' - ELSE 'Juvenile' - END AS general_division - FROM actor.usr u; +SELECT u.id, + u.dob, + CASE + WHEN u.dob IS NULL + THEN 'Adult' + WHEN AGE(u.dob) > '18 years'::INTERVAL + THEN 'Adult' + ELSE 'Juvenile' + END AS general_division, + CASE + WHEN u.dob IS NULL + THEN 'No Date of Birth Entered'::text + WHEN age(u.dob::timestamp with time zone) >= '0 years'::interval and age(u.dob::timestamp with time zone) < '6 years'::interval + THEN 'Child 0-5 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '6 years'::interval and age(u.dob::timestamp with time zone) < '13 years'::interval + THEN 'Child 6-12 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '13 years'::interval and age(u.dob::timestamp with time zone) < '18 years'::interval + THEN 'Teen 13-17 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '18 years'::interval and age(u.dob::timestamp with time zone) < '26 years'::interval + THEN 'Adult 18-25 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '26 years'::interval and age(u.dob::timestamp with time zone) < '50 years'::interval + THEN 'Adult 26-49 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '50 years'::interval and age(u.dob::timestamp with time zone) < '60 years'::interval + THEN 'Adult 50-59 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '60 years'::interval and age(u.dob::timestamp with time zone) < '70 years'::interval + THEN 'Adult 60-69 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '70 years'::interval + THEN 'Adult 70+'::text + ELSE NULL::text + END AS age_division + FROM actor.usr u; CREATE OR REPLACE VIEW reporter.circ_type AS SELECT id, diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.reporter-demographic-expansion.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.reporter-demographic-expansion.sql new file mode 100644 index 0000000000..6cc62fb3d0 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.reporter-demographic-expansion.sql @@ -0,0 +1,38 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE VIEW reporter.demographic AS +SELECT u.id, + u.dob, + CASE + WHEN u.dob IS NULL + THEN 'Adult' + WHEN AGE(u.dob) > '18 years'::INTERVAL + THEN 'Adult' + ELSE 'Juvenile' + END AS general_division, + CASE + WHEN u.dob IS NULL + THEN 'No Date of Birth Entered'::text + WHEN age(u.dob::timestamp with time zone) >= '0 years'::interval and age(u.dob::timestamp with time zone) < '6 years'::interval + THEN 'Child 0-5 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '6 years'::interval and age(u.dob::timestamp with time zone) < '13 years'::interval + THEN 'Child 6-12 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '13 years'::interval and age(u.dob::timestamp with time zone) < '18 years'::interval + THEN 'Teen 13-17 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '18 years'::interval and age(u.dob::timestamp with time zone) < '26 years'::interval + THEN 'Adult 18-25 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '26 years'::interval and age(u.dob::timestamp with time zone) < '50 years'::interval + THEN 'Adult 26-49 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '50 years'::interval and age(u.dob::timestamp with time zone) < '60 years'::interval + THEN 'Adult 50-59 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '60 years'::interval and age(u.dob::timestamp with time zone) < '70 years'::interval + THEN 'Adult 60-69 Years Old'::text + WHEN age(u.dob::timestamp with time zone) >= '70 years'::interval + THEN 'Adult 70+'::text + ELSE NULL::text + END AS age_division + FROM actor.usr u; + +COMMIT; -- 2.11.0