From 35fe6cb36329854c625c987f59e209f0e8e5464d Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 5 Oct 2015 10:37:20 -0400 Subject: [PATCH] JBAS-920 Port DoB as DATE to KCLS schema Porting SQL from LP#838525. Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/dob-as-date.sql | 202 +++++++++++++++++++++++++++++++++ KCLS/sql/schema/revert/dob-as-date.sql | 201 ++++++++++++++++++++++++++++++++ KCLS/sql/schema/sqitch.plan | 1 + KCLS/sql/schema/verify/dob-as-date.sql | 12 ++ 4 files changed, 416 insertions(+) create mode 100644 KCLS/sql/schema/deploy/dob-as-date.sql create mode 100644 KCLS/sql/schema/revert/dob-as-date.sql create mode 100644 KCLS/sql/schema/verify/dob-as-date.sql diff --git a/KCLS/sql/schema/deploy/dob-as-date.sql b/KCLS/sql/schema/deploy/dob-as-date.sql new file mode 100644 index 0000000000..52343c01ad --- /dev/null +++ b/KCLS/sql/schema/deploy/dob-as-date.sql @@ -0,0 +1,202 @@ +-- Deploy kcls-evergreen:dob-as-date to pg +-- requires: student-groups + +BEGIN; + +DROP VIEW action.all_circulation; +DROP VIEW reporter.classic_current_circ; +DROP VIEW reporter.classic_current_billing_summary; +DROP VIEW reporter.demographic; +DROP VIEW auditor.actor_usr_lifecycle; +DROP VIEW action.all_hold_request; + +ALTER TABLE actor.usr ALTER dob TYPE DATE + USING (dob + '3 hours'::INTERVAL)::DATE; + +ALTER TABLE auditor.actor_usr_history + ALTER dob TYPE DATE USING (dob + '3 hours'::INTERVAL)::DATE; + +-- this recreates auditor.actor_usr_lifecycle +SELECT auditor.update_auditors(); + +CREATE OR REPLACE VIEW action.all_circulation AS + SELECT aged_circulation.id, aged_circulation.usr_post_code, + aged_circulation.usr_home_ou, aged_circulation.usr_profile, + aged_circulation.usr_birth_year, aged_circulation.copy_call_number, + aged_circulation.copy_location, aged_circulation.copy_owning_lib, + aged_circulation.copy_circ_lib, aged_circulation.copy_bib_record, + aged_circulation.xact_start, aged_circulation.xact_finish, + aged_circulation.target_copy, aged_circulation.circ_lib, + aged_circulation.circ_staff, aged_circulation.checkin_staff, + aged_circulation.checkin_lib, aged_circulation.renewal_remaining, + aged_circulation.grace_period, aged_circulation.due_date, + aged_circulation.stop_fines_time, aged_circulation.checkin_time, + aged_circulation.create_time, aged_circulation.duration, + aged_circulation.fine_interval, aged_circulation.recurring_fine, + aged_circulation.max_fine, aged_circulation.phone_renewal, + aged_circulation.desk_renewal, aged_circulation.opac_renewal, + aged_circulation.duration_rule, + aged_circulation.recurring_fine_rule, + aged_circulation.max_fine_rule, aged_circulation.stop_fines, + aged_circulation.workstation, aged_circulation.checkin_workstation, + aged_circulation.checkin_scan_time, aged_circulation.parent_circ + FROM action.aged_circulation +UNION ALL + SELECT DISTINCT circ.id, + COALESCE(a.post_code, b.post_code) AS usr_post_code, + p.home_ou AS usr_home_ou, p.profile AS usr_profile, + date_part('year'::text, p.dob)::integer AS usr_birth_year, + cp.call_number AS copy_call_number, circ.copy_location, + cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib, + cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, + circ.target_copy, circ.circ_lib, circ.circ_staff, + circ.checkin_staff, circ.checkin_lib, circ.renewal_remaining, + circ.grace_period, circ.due_date, circ.stop_fines_time, + circ.checkin_time, circ.create_time, circ.duration, + circ.fine_interval, circ.recurring_fine, circ.max_fine, + circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, + circ.duration_rule, circ.recurring_fine_rule, circ.max_fine_rule, + circ.stop_fines, circ.workstation, circ.checkin_workstation, + circ.checkin_scan_time, circ.parent_circ + FROM action.circulation circ + JOIN asset.copy cp ON circ.target_copy = cp.id + JOIN asset.call_number cn ON cp.call_number = cn.id + JOIN actor.usr p ON circ.usr = p.id + LEFT JOIN actor.usr_address a ON p.mailing_address = a.id + LEFT JOIN actor.usr_address b ON p.billing_address = b.id; + +CREATE OR REPLACE VIEW reporter.demographic AS + SELECT u.id, u.dob, + CASE + WHEN u.dob IS NULL THEN 'Adult'::text + WHEN age(u.dob) > '18 years'::interval THEN 'Adult'::text + ELSE 'Juvenile'::text + END AS general_division + FROM actor.usr u; + +CREATE OR REPLACE VIEW action.all_hold_request AS + SELECT DISTINCT COALESCE(a.post_code, b.post_code) AS usr_post_code, + p.home_ou AS usr_home_ou, p.profile AS usr_profile, + date_part('year'::text, p.dob)::integer AS usr_birth_year, + ahr.requestor <> ahr.usr AS staff_placed, ahr.id, ahr.request_time, + ahr.capture_time, ahr.fulfillment_time, ahr.checkin_time, + ahr.return_time, ahr.prev_check_time, ahr.expire_time, + ahr.cancel_time, ahr.cancel_cause, ahr.cancel_note, ahr.target, + ahr.current_copy, ahr.fulfillment_staff, ahr.fulfillment_lib, + ahr.request_lib, ahr.selection_ou, ahr.selection_depth, + ahr.pickup_lib, ahr.hold_type, ahr.holdable_formats, + CASE + WHEN ahr.phone_notify IS NULL THEN false + WHEN ahr.phone_notify = ''::text THEN false + ELSE true + END AS phone_notify, + ahr.email_notify, + CASE + WHEN ahr.sms_notify IS NULL THEN false + WHEN ahr.sms_notify = ''::text THEN false + ELSE true + END AS sms_notify, + ahr.frozen, ahr.thaw_date, ahr.shelf_time, ahr.cut_in_line, + ahr.mint_condition, ahr.shelf_expire_time, ahr.current_shelf_lib + FROM action.hold_request ahr + JOIN actor.usr p ON ahr.usr = p.id + LEFT JOIN actor.usr_address a ON p.mailing_address = a.id + LEFT JOIN actor.usr_address b ON p.billing_address = b.id +UNION ALL + SELECT aged_hold_request.usr_post_code, aged_hold_request.usr_home_ou, + aged_hold_request.usr_profile, aged_hold_request.usr_birth_year, + aged_hold_request.staff_placed, aged_hold_request.id, + aged_hold_request.request_time, aged_hold_request.capture_time, + aged_hold_request.fulfillment_time, aged_hold_request.checkin_time, + aged_hold_request.return_time, aged_hold_request.prev_check_time, + aged_hold_request.expire_time, aged_hold_request.cancel_time, + aged_hold_request.cancel_cause, aged_hold_request.cancel_note, + aged_hold_request.target, aged_hold_request.current_copy, + aged_hold_request.fulfillment_staff, + aged_hold_request.fulfillment_lib, aged_hold_request.request_lib, + aged_hold_request.selection_ou, aged_hold_request.selection_depth, + aged_hold_request.pickup_lib, aged_hold_request.hold_type, + aged_hold_request.holdable_formats, aged_hold_request.phone_notify, + aged_hold_request.email_notify, aged_hold_request.sms_notify, + aged_hold_request.frozen, aged_hold_request.thaw_date, + aged_hold_request.shelf_time, aged_hold_request.cut_in_line, + aged_hold_request.mint_condition, + aged_hold_request.shelf_expire_time, + aged_hold_request.current_shelf_lib + FROM action.aged_hold_request; + +CREATE OR REPLACE VIEW reporter.classic_current_billing_summary AS + SELECT x.id, x.usr, bl.shortname AS billing_location_shortname, + bl.name AS billing_location_name, x.billing_location, c.barcode, + u.home_ou AS usr_home_ou, ul.shortname AS usr_home_ou_shortname, + ul.name AS usr_home_ou_name, x.xact_start, x.xact_finish, x.xact_type, + x.total_paid, x.total_owed, x.balance_owed, x.last_payment_ts, + x.last_payment_note, x.last_payment_type, x.last_billing_ts, + x.last_billing_note, x.last_billing_type, paddr.county AS patron_county, + paddr.city AS patron_city, paddr.post_code AS patron_zip, + g.name AS profile_group, + dem.general_division AS demographic_general_division + FROM money.open_billable_xact_summary x + JOIN actor.org_unit bl ON x.billing_location = bl.id + JOIN actor.usr u ON u.id = x.usr + JOIN actor.org_unit ul ON u.home_ou = ul.id + JOIN actor.card c ON u.card = c.id + JOIN permission.grp_tree g ON u.profile = g.id + JOIN reporter.demographic dem ON dem.id = u.id + JOIN actor.usr_address paddr ON paddr.id = u.billing_address; + +CREATE OR REPLACE VIEW reporter.classic_current_circ AS + SELECT cl.shortname AS circ_lib, cl.id AS circ_lib_id, circ.xact_start, + circ_type.type AS circ_type, cp.id AS copy_id, cp.circ_modifier, + ol.shortname AS owning_lib_name, lm.value AS language, + lfm.value AS lit_form, ifm.value AS item_form, itm.value AS item_type, + sl.name AS shelving_location, p.id AS patron_id, g.name AS profile_group, + dem.general_division AS demographic_general_division, circ.id, + cn.id AS call_number, cn.label AS call_number_label, + call_number_dewey(cn.label) AS dewey, + CASE + WHEN call_number_dewey(cn.label) ~ '^[0-9.]+$'::text THEN btrim(to_char(10::double precision * floor(call_number_dewey(cn.label)::double precision / 10::double precision), '000'::text)) + ELSE NULL::text + END AS dewey_block_tens, + CASE + WHEN call_number_dewey(cn.label) ~ '^[0-9.]+$'::text THEN btrim(to_char(100::double precision * floor(call_number_dewey(cn.label)::double precision / 100::double precision), '000'::text)) + ELSE NULL::text + END AS dewey_block_hundreds, + CASE + WHEN call_number_dewey(cn.label) ~ '^[0-9.]+$'::text THEN (btrim(to_char(10::double precision * floor(call_number_dewey(cn.label)::double precision / 10::double precision), '000'::text)) || '-'::text) || btrim(to_char(10::double precision * floor(call_number_dewey(cn.label)::double precision / 10::double precision) + 9::double precision, '000'::text)) + ELSE NULL::text + END AS dewey_range_tens, + CASE + WHEN call_number_dewey(cn.label) ~ '^[0-9.]+$'::text THEN (btrim(to_char(100::double precision * floor(call_number_dewey(cn.label)::double precision / 100::double precision), '000'::text)) || '-'::text) || btrim(to_char(100::double precision * floor(call_number_dewey(cn.label)::double precision / 100::double precision) + 99::double precision, '000'::text)) + ELSE NULL::text + END AS dewey_range_hundreds, + hl.id AS patron_home_lib, hl.shortname AS patron_home_lib_shortname, + paddr.county AS patron_county, paddr.city AS patron_city, + paddr.post_code AS patron_zip, sc1.stat_cat_entry AS stat_cat_1, + sc2.stat_cat_entry AS stat_cat_2, sce1.value AS stat_cat_1_value, + sce2.value AS stat_cat_2_value + FROM action.circulation circ + JOIN reporter.circ_type circ_type ON circ.id = circ_type.id + JOIN asset.copy cp ON cp.id = circ.target_copy + JOIN asset.copy_location sl ON cp.location = sl.id + JOIN asset.call_number cn ON cp.call_number = cn.id + JOIN actor.org_unit ol ON cn.owning_lib = ol.id + JOIN metabib.rec_descriptor rd ON rd.record = cn.record + JOIN actor.org_unit cl ON circ.circ_lib = cl.id + JOIN actor.usr p ON p.id = circ.usr + JOIN actor.org_unit hl ON p.home_ou = hl.id + JOIN permission.grp_tree g ON p.profile = g.id + JOIN reporter.demographic dem ON dem.id = p.id + JOIN actor.usr_address paddr ON paddr.id = p.billing_address + LEFT JOIN config.language_map lm ON rd.item_lang = lm.code + LEFT JOIN config.lit_form_map lfm ON rd.lit_form = lfm.code + LEFT JOIN config.item_form_map ifm ON rd.item_form = ifm.code + LEFT JOIN config.item_type_map itm ON rd.item_type = itm.code + LEFT JOIN asset.stat_cat_entry_copy_map sc1 ON sc1.owning_copy = cp.id AND sc1.stat_cat = 1 + LEFT JOIN asset.stat_cat_entry sce1 ON sce1.id = sc1.stat_cat_entry + LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON sc2.owning_copy = cp.id AND sc2.stat_cat = 2 + LEFT JOIN asset.stat_cat_entry sce2 ON sce2.id = sc2.stat_cat_entry; + + + +COMMIT; diff --git a/KCLS/sql/schema/revert/dob-as-date.sql b/KCLS/sql/schema/revert/dob-as-date.sql new file mode 100644 index 0000000000..78ca84a190 --- /dev/null +++ b/KCLS/sql/schema/revert/dob-as-date.sql @@ -0,0 +1,201 @@ +-- Revert kcls-evergreen:dob-as-date from pg + +BEGIN; + +DROP VIEW action.all_circulation; +DROP VIEW reporter.classic_current_billing_summary; +DROP VIEW reporter.classic_current_circ; +DROP VIEW reporter.demographic; +DROP VIEW auditor.actor_usr_lifecycle; +DROP VIEW action.all_hold_request; + +ALTER TABLE actor.usr ALTER dob TYPE TIMESTAMPTZ + USING (dob + '12 hours'::INTERVAL)::DATE; + +ALTER TABLE auditor.actor_usr_history ALTER dob TYPE TIMESTAMPTZ + USING (dob + '12 hours'::INTERVAL)::DATE; + +-- this recreates auditor.actor_usr_lifecycle +SELECT auditor.update_auditors(); + +CREATE OR REPLACE VIEW action.all_circulation AS + SELECT aged_circulation.id, aged_circulation.usr_post_code, + aged_circulation.usr_home_ou, aged_circulation.usr_profile, + aged_circulation.usr_birth_year, aged_circulation.copy_call_number, + aged_circulation.copy_location, aged_circulation.copy_owning_lib, + aged_circulation.copy_circ_lib, aged_circulation.copy_bib_record, + aged_circulation.xact_start, aged_circulation.xact_finish, + aged_circulation.target_copy, aged_circulation.circ_lib, + aged_circulation.circ_staff, aged_circulation.checkin_staff, + aged_circulation.checkin_lib, aged_circulation.renewal_remaining, + aged_circulation.grace_period, aged_circulation.due_date, + aged_circulation.stop_fines_time, aged_circulation.checkin_time, + aged_circulation.create_time, aged_circulation.duration, + aged_circulation.fine_interval, aged_circulation.recurring_fine, + aged_circulation.max_fine, aged_circulation.phone_renewal, + aged_circulation.desk_renewal, aged_circulation.opac_renewal, + aged_circulation.duration_rule, + aged_circulation.recurring_fine_rule, + aged_circulation.max_fine_rule, aged_circulation.stop_fines, + aged_circulation.workstation, aged_circulation.checkin_workstation, + aged_circulation.checkin_scan_time, aged_circulation.parent_circ + FROM action.aged_circulation +UNION ALL + SELECT DISTINCT circ.id, + COALESCE(a.post_code, b.post_code) AS usr_post_code, + p.home_ou AS usr_home_ou, p.profile AS usr_profile, + date_part('year'::text, p.dob)::integer AS usr_birth_year, + cp.call_number AS copy_call_number, circ.copy_location, + cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib, + cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, + circ.target_copy, circ.circ_lib, circ.circ_staff, + circ.checkin_staff, circ.checkin_lib, circ.renewal_remaining, + circ.grace_period, circ.due_date, circ.stop_fines_time, + circ.checkin_time, circ.create_time, circ.duration, + circ.fine_interval, circ.recurring_fine, circ.max_fine, + circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, + circ.duration_rule, circ.recurring_fine_rule, circ.max_fine_rule, + circ.stop_fines, circ.workstation, circ.checkin_workstation, + circ.checkin_scan_time, circ.parent_circ + FROM action.circulation circ + JOIN asset.copy cp ON circ.target_copy = cp.id + JOIN asset.call_number cn ON cp.call_number = cn.id + JOIN actor.usr p ON circ.usr = p.id + LEFT JOIN actor.usr_address a ON p.mailing_address = a.id + LEFT JOIN actor.usr_address b ON p.billing_address = b.id; + +CREATE OR REPLACE VIEW reporter.demographic AS + SELECT u.id, u.dob, + CASE + WHEN u.dob IS NULL THEN 'Adult'::text + WHEN age(u.dob) > '18 years'::interval THEN 'Adult'::text + ELSE 'Juvenile'::text + END AS general_division + FROM actor.usr u; + +CREATE OR REPLACE VIEW action.all_hold_request AS + SELECT DISTINCT COALESCE(a.post_code, b.post_code) AS usr_post_code, + p.home_ou AS usr_home_ou, p.profile AS usr_profile, + date_part('year'::text, p.dob)::integer AS usr_birth_year, + ahr.requestor <> ahr.usr AS staff_placed, ahr.id, ahr.request_time, + ahr.capture_time, ahr.fulfillment_time, ahr.checkin_time, + ahr.return_time, ahr.prev_check_time, ahr.expire_time, + ahr.cancel_time, ahr.cancel_cause, ahr.cancel_note, ahr.target, + ahr.current_copy, ahr.fulfillment_staff, ahr.fulfillment_lib, + ahr.request_lib, ahr.selection_ou, ahr.selection_depth, + ahr.pickup_lib, ahr.hold_type, ahr.holdable_formats, + CASE + WHEN ahr.phone_notify IS NULL THEN false + WHEN ahr.phone_notify = ''::text THEN false + ELSE true + END AS phone_notify, + ahr.email_notify, + CASE + WHEN ahr.sms_notify IS NULL THEN false + WHEN ahr.sms_notify = ''::text THEN false + ELSE true + END AS sms_notify, + ahr.frozen, ahr.thaw_date, ahr.shelf_time, ahr.cut_in_line, + ahr.mint_condition, ahr.shelf_expire_time, ahr.current_shelf_lib + FROM action.hold_request ahr + JOIN actor.usr p ON ahr.usr = p.id + LEFT JOIN actor.usr_address a ON p.mailing_address = a.id + LEFT JOIN actor.usr_address b ON p.billing_address = b.id +UNION ALL + SELECT aged_hold_request.usr_post_code, aged_hold_request.usr_home_ou, + aged_hold_request.usr_profile, aged_hold_request.usr_birth_year, + aged_hold_request.staff_placed, aged_hold_request.id, + aged_hold_request.request_time, aged_hold_request.capture_time, + aged_hold_request.fulfillment_time, aged_hold_request.checkin_time, + aged_hold_request.return_time, aged_hold_request.prev_check_time, + aged_hold_request.expire_time, aged_hold_request.cancel_time, + aged_hold_request.cancel_cause, aged_hold_request.cancel_note, + aged_hold_request.target, aged_hold_request.current_copy, + aged_hold_request.fulfillment_staff, + aged_hold_request.fulfillment_lib, aged_hold_request.request_lib, + aged_hold_request.selection_ou, aged_hold_request.selection_depth, + aged_hold_request.pickup_lib, aged_hold_request.hold_type, + aged_hold_request.holdable_formats, aged_hold_request.phone_notify, + aged_hold_request.email_notify, aged_hold_request.sms_notify, + aged_hold_request.frozen, aged_hold_request.thaw_date, + aged_hold_request.shelf_time, aged_hold_request.cut_in_line, + aged_hold_request.mint_condition, + aged_hold_request.shelf_expire_time, + aged_hold_request.current_shelf_lib + FROM action.aged_hold_request; + +CREATE OR REPLACE VIEW reporter.classic_current_billing_summary AS + SELECT x.id, x.usr, bl.shortname AS billing_location_shortname, + bl.name AS billing_location_name, x.billing_location, c.barcode, + u.home_ou AS usr_home_ou, ul.shortname AS usr_home_ou_shortname, + ul.name AS usr_home_ou_name, x.xact_start, x.xact_finish, x.xact_type, + x.total_paid, x.total_owed, x.balance_owed, x.last_payment_ts, + x.last_payment_note, x.last_payment_type, x.last_billing_ts, + x.last_billing_note, x.last_billing_type, paddr.county AS patron_county, + paddr.city AS patron_city, paddr.post_code AS patron_zip, + g.name AS profile_group, + dem.general_division AS demographic_general_division + FROM money.open_billable_xact_summary x + JOIN actor.org_unit bl ON x.billing_location = bl.id + JOIN actor.usr u ON u.id = x.usr + JOIN actor.org_unit ul ON u.home_ou = ul.id + JOIN actor.card c ON u.card = c.id + JOIN permission.grp_tree g ON u.profile = g.id + JOIN reporter.demographic dem ON dem.id = u.id + JOIN actor.usr_address paddr ON paddr.id = u.billing_address; + +CREATE OR REPLACE VIEW reporter.classic_current_circ AS + SELECT cl.shortname AS circ_lib, cl.id AS circ_lib_id, circ.xact_start, + circ_type.type AS circ_type, cp.id AS copy_id, cp.circ_modifier, + ol.shortname AS owning_lib_name, lm.value AS language, + lfm.value AS lit_form, ifm.value AS item_form, itm.value AS item_type, + sl.name AS shelving_location, p.id AS patron_id, g.name AS profile_group, + dem.general_division AS demographic_general_division, circ.id, + cn.id AS call_number, cn.label AS call_number_label, + call_number_dewey(cn.label) AS dewey, + CASE + WHEN call_number_dewey(cn.label) ~ '^[0-9.]+$'::text THEN btrim(to_char(10::double precision * floor(call_number_dewey(cn.label)::double precision / 10::double precision), '000'::text)) + ELSE NULL::text + END AS dewey_block_tens, + CASE + WHEN call_number_dewey(cn.label) ~ '^[0-9.]+$'::text THEN btrim(to_char(100::double precision * floor(call_number_dewey(cn.label)::double precision / 100::double precision), '000'::text)) + ELSE NULL::text + END AS dewey_block_hundreds, + CASE + WHEN call_number_dewey(cn.label) ~ '^[0-9.]+$'::text THEN (btrim(to_char(10::double precision * floor(call_number_dewey(cn.label)::double precision / 10::double precision), '000'::text)) || '-'::text) || btrim(to_char(10::double precision * floor(call_number_dewey(cn.label)::double precision / 10::double precision) + 9::double precision, '000'::text)) + ELSE NULL::text + END AS dewey_range_tens, + CASE + WHEN call_number_dewey(cn.label) ~ '^[0-9.]+$'::text THEN (btrim(to_char(100::double precision * floor(call_number_dewey(cn.label)::double precision / 100::double precision), '000'::text)) || '-'::text) || btrim(to_char(100::double precision * floor(call_number_dewey(cn.label)::double precision / 100::double precision) + 99::double precision, '000'::text)) + ELSE NULL::text + END AS dewey_range_hundreds, + hl.id AS patron_home_lib, hl.shortname AS patron_home_lib_shortname, + paddr.county AS patron_county, paddr.city AS patron_city, + paddr.post_code AS patron_zip, sc1.stat_cat_entry AS stat_cat_1, + sc2.stat_cat_entry AS stat_cat_2, sce1.value AS stat_cat_1_value, + sce2.value AS stat_cat_2_value + FROM action.circulation circ + JOIN reporter.circ_type circ_type ON circ.id = circ_type.id + JOIN asset.copy cp ON cp.id = circ.target_copy + JOIN asset.copy_location sl ON cp.location = sl.id + JOIN asset.call_number cn ON cp.call_number = cn.id + JOIN actor.org_unit ol ON cn.owning_lib = ol.id + JOIN metabib.rec_descriptor rd ON rd.record = cn.record + JOIN actor.org_unit cl ON circ.circ_lib = cl.id + JOIN actor.usr p ON p.id = circ.usr + JOIN actor.org_unit hl ON p.home_ou = hl.id + JOIN permission.grp_tree g ON p.profile = g.id + JOIN reporter.demographic dem ON dem.id = p.id + JOIN actor.usr_address paddr ON paddr.id = p.billing_address + LEFT JOIN config.language_map lm ON rd.item_lang = lm.code + LEFT JOIN config.lit_form_map lfm ON rd.lit_form = lfm.code + LEFT JOIN config.item_form_map ifm ON rd.item_form = ifm.code + LEFT JOIN config.item_type_map itm ON rd.item_type = itm.code + LEFT JOIN asset.stat_cat_entry_copy_map sc1 ON sc1.owning_copy = cp.id AND sc1.stat_cat = 1 + LEFT JOIN asset.stat_cat_entry sce1 ON sce1.id = sc1.stat_cat_entry + LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON sc2.owning_copy = cp.id AND sc2.stat_cat = 2 + LEFT JOIN asset.stat_cat_entry sce2 ON sce2.id = sc2.stat_cat_entry; + + + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index 254a844b52..ad4037b944 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -15,3 +15,4 @@ collection-hq-floating-case [blanket-po] 2015-09-17T18:42:09Z Bill Erickson # New groups for student cards blanket-po-print-template [student-groups] 2015-10-12T15:38:31Z Bill Erickson # Blanket PO print template po-print-li-count-and-date [blanket-po-print-template] 2015-11-20T18:56:33Z Bill Erickson # Minor PO print template repairs +dob-as-date [student-groups] 2015-10-05T14:17:54Z Bill Erickson # Store DoB as date diff --git a/KCLS/sql/schema/verify/dob-as-date.sql b/KCLS/sql/schema/verify/dob-as-date.sql new file mode 100644 index 0000000000..a187e66cce --- /dev/null +++ b/KCLS/sql/schema/verify/dob-as-date.sql @@ -0,0 +1,12 @@ +-- Verify kcls-evergreen:dob-as-date on pg + +BEGIN; + +SELECT 1/COUNT(*) + FROM information_schema.columns + WHERE column_name = 'dob' + AND table_name = 'usr' + AND table_schema = 'actor' + AND data_type = 'date'; + +ROLLBACK; -- 2.11.0