From da4504f13e51389db7e6e418cfb85d9f9df7c630 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Thu, 9 Jan 2020 16:46:19 -0500 Subject: [PATCH] remove old scripts, combine new features into post-upgrade script --- ...lp1777677-action-triggers-test-notification.sql | 85 -------- .../XXXX.function.special-bib-vis-handling.sql | 49 ----- .../XXXX.lp1747542_action_item_user_circ_test.sql | 237 --------------------- ...lp1788260.schema.non-cat-in-house-use-aacct.sql | 60 ------ .../XXXX.schema.acq_invoice_reporter_view.sql | 185 ---------------- .../sql/Pg/upgrade/XXXX.schema.patron-guardian.sql | 171 --------------- .../XXXX.schema.webstaff.cat.copy.templates.sql | 8 - .../Pg/version-upgrade/pines-post-3.4-upgrade.sql | 65 ++++++ 8 files changed, 65 insertions(+), 795 deletions(-) delete mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.data.lp1777677-action-triggers-test-notification.sql delete mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.special-bib-vis-handling.sql delete mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.lp1747542_action_item_user_circ_test.sql delete mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.lp1788260.schema.non-cat-in-house-use-aacct.sql delete mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_invoice_reporter_view.sql delete mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron-guardian.sql delete mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.webstaff.cat.copy.templates.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.lp1777677-action-triggers-test-notification.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.lp1777677-action-triggers-test-notification.sql deleted file mode 100644 index 4c22110dad..0000000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.lp1777677-action-triggers-test-notification.sql +++ /dev/null @@ -1,85 +0,0 @@ -BEGIN; - -SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); - - -INSERT into action_trigger.hook (key, core_type, description) VALUES ( - 'au.email.test', 'au', 'A test email has been requested for this user' -), -( - 'au.sms_text.test', 'au', 'A test SMS has been requested for this user' -); - -INSERT INTO action_trigger.event_definition (active, owner, name, hook, validator, reactor, delay, template) -VALUES ( - 't', 1, 'Send Test Email', 'au.email.test', 'NOOP_True', 'SendEmail', '00:01:00', -$$ -[%- USE date -%] -[%- user = target -%] -[%- lib = target.home_ou -%] -To: [%- user.email %] -From: [%- helpers.get_org_setting(target.home_ou.id, 'org.bounced_emails') || lib.email || params.sender_email || default_sender %] -Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %] -Reply-To: [%- lib.email || params.sender_email || default_sender %] -Subject: Email Test Notification -Auto-Submitted: auto-generated - -Dear [% user.first_given_name %] [% user.family_name %], - -This is a test of the email associated with your account at [%- lib.name -%]. If you are receiving this message, your email information is correct. - -Sincerely, -[% lib.name %] - -Contact your library for more information: - -[% lib.name %] -[%- SET addr = lib.mailing_address -%] -[%- IF !addr -%] [%- SET addr = lib.billing_address -%] [%- END %] -[% addr.street1 %] [% addr.street2 %] -[% addr.city %], [% addr.state %] -[% addr.post_code %] -[% lib.phone %] -$$); - -INSERT INTO action_trigger.environment (event_def, path) -VALUES (currval('action_trigger.event_definition_id_seq'), 'home_ou'), - (currval('action_trigger.event_definition_id_seq'), 'home_ou.mailing_address'), - (currval('action_trigger.event_definition_id_seq'), 'home_ou.billing_address'); - -INSERT INTO action_trigger.event_definition (active, owner, name, hook, validator, reactor, delay, template) -VALUES ( - 't', 1, 'Send Test SMS', 'au.sms_text.test', 'NOOP_True', 'SendSMS', '00:01:00', -$$ -[%- USE date -%] -[%- user = target -%] -[%- lib = user.home_ou -%] -[%- sms_number = helpers.get_user_setting(target.id, 'opac.default_sms_notify') -%] -[%- sms_carrier = helpers.get_user_setting(target.id, 'opac.default_sms_carrier') -%] -From: [%- helpers.get_org_setting(target.home_ou.id, 'org.bounced_emails') || lib.email || params.sender_email || default_sender %] -To: [%- helpers.get_sms_gateway_email(sms_carrier,sms_number) %] -Subject: Test Text Message - -This is a test confirming your mobile number for [% lib.name %] is correct. - -Sincerely, -[% lib.name %] - -Contact your library for more information: - -[% lib.name %] -[%- SET addr = lib.mailing_address -%] -[%- IF !addr -%] [%- SET addr = lib.billing_address -%] [%- END %] -[% addr.street1 %] [% addr.street2 %] -[% addr.city %], [% addr.state %] -[% addr.post_code %] -[% lib.phone %] -$$); - -INSERT INTO action_trigger.environment (event_def, path) -VALUES (currval('action_trigger.event_definition_id_seq'), 'home_ou'), - (currval('action_trigger.event_definition_id_seq'), 'home_ou.mailing_address'), - (currval('action_trigger.event_definition_id_seq'), 'home_ou.billing_address'); - - -COMMIT; \ No newline at end of file diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.special-bib-vis-handling.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.special-bib-vis-handling.sql deleted file mode 100644 index 11c5a2d098..0000000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.special-bib-vis-handling.sql +++ /dev/null @@ -1,49 +0,0 @@ -BEGIN; - -CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$ -DECLARE - copy_flags TEXT; -- "c" attr - - owning_lib TEXT; -- "c" attr - circ_lib TEXT; -- "c" attr - status TEXT; -- "c" attr - location TEXT; -- "c" attr - location_group TEXT; -- "c" attr - - luri_org TEXT; -- "b" attr - bib_sources TEXT; -- "b" attr - - bib_tests TEXT := ''; -BEGIN - copy_flags := asset.all_visible_flags(); -- Will always have at least one - - owning_lib := NULLIF(asset.owning_lib_default(),'!()'); - - circ_lib := NULLIF(asset.circ_lib_default(),'!()'); - status := NULLIF(asset.status_default(),'!()'); - location := NULLIF(asset.location_default(),'!()'); - location_group := NULLIF(asset.location_group_default(),'!()'); - - -- LURIs will be handled at the perl layer directly - -- luri_org := NULLIF(asset.luri_org_default(),'!()'); - bib_sources := NULLIF(asset.bib_source_default(),'()'); - - - IF luri_org IS NOT NULL AND bib_sources IS NOT NULL THEN - bib_tests := '('||ARRAY_TO_STRING( ARRAY[luri_org,bib_sources], '|')||')&('||luri_org||')&'; - ELSIF luri_org IS NOT NULL THEN - bib_tests := luri_org || '&'; - ELSIF bib_sources IS NOT NULL THEN - bib_tests := bib_sources || '|'; - END IF; - - RETURN QUERY SELECT bib_tests, - '('||ARRAY_TO_STRING( - ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[], - '&' - )||')'; -END; -$f$ LANGUAGE PLPGSQL STABLE ROWS 1; - -COMMIT; - diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.lp1747542_action_item_user_circ_test.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.lp1747542_action_item_user_circ_test.sql deleted file mode 100644 index 0db9c3d5d5..0000000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.lp1747542_action_item_user_circ_test.sql +++ /dev/null @@ -1,237 +0,0 @@ -BEGIN; - -SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); - -CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$ -DECLARE - user_object actor.usr%ROWTYPE; - standing_penalty config.standing_penalty%ROWTYPE; - item_object asset.copy%ROWTYPE; - item_status_object config.copy_status%ROWTYPE; - item_location_object asset.copy_location%ROWTYPE; - result action.circ_matrix_test_result; - circ_test action.found_circ_matrix_matchpoint; - circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE; - circ_limit_set config.circ_limit_set%ROWTYPE; - hold_ratio action.hold_stats%ROWTYPE; - penalty_type TEXT; - items_out INT; - context_org_list INT[]; - done BOOL := FALSE; - item_prox INT; - home_prox INT; -BEGIN - -- Assume success unless we hit a failure condition - result.success := TRUE; - - -- Need user info to look up matchpoints - SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted; - - -- (Insta)Fail if we couldn't find the user - IF user_object.id IS NULL THEN - result.fail_part := 'no_user'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - RETURN; - END IF; - - -- Need item info to look up matchpoints - SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted; - - -- (Insta)Fail if we couldn't find the item - IF item_object.id IS NULL THEN - result.fail_part := 'no_item'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - RETURN; - END IF; - - SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal); - - circ_matchpoint := circ_test.matchpoint; - result.matchpoint := circ_matchpoint.id; - result.circulate := circ_matchpoint.circulate; - result.duration_rule := circ_matchpoint.duration_rule; - result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule; - result.max_fine_rule := circ_matchpoint.max_fine_rule; - result.hard_due_date := circ_matchpoint.hard_due_date; - result.renewals := circ_matchpoint.renewals; - result.grace_period := circ_matchpoint.grace_period; - result.buildrows := circ_test.buildrows; - - -- (Insta)Fail if we couldn't find a matchpoint - IF circ_test.success = false THEN - result.fail_part := 'no_matchpoint'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - RETURN; - END IF; - - -- All failures before this point are non-recoverable - -- Below this point are possibly overridable failures - - -- Fail if the user is barred - IF user_object.barred IS TRUE THEN - result.fail_part := 'actor.usr.barred'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - - -- Fail if the item can't circulate - IF item_object.circulate IS FALSE THEN - result.fail_part := 'asset.copy.circulate'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - - -- Fail if the item isn't in a circulateable status on a non-renewal - IF NOT renewal AND item_object.status <> 8 AND item_object.status NOT IN ( - (SELECT id FROM config.copy_status WHERE is_available) ) THEN - result.fail_part := 'asset.copy.status'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - -- Alternately, fail if the item isn't checked out on a renewal - ELSIF renewal AND item_object.status <> 1 THEN - result.fail_part := 'asset.copy.status'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - - -- Fail if the item can't circulate because of the shelving location - SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location; - IF item_location_object.circulate IS FALSE THEN - result.fail_part := 'asset.copy_location.circulate'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - - -- Use Circ OU for penalties and such - SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou ); - - -- Proximity of user's home_ou to circ_ou to see if penalties should be ignored. - SELECT INTO home_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = circ_ou; - - -- Proximity of user's home_ou to item circ_lib to see if penalties should be ignored. - SELECT INTO item_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = item_object.circ_lib; - - IF renewal THEN - penalty_type = '%RENEW%'; - ELSE - penalty_type = '%CIRC%'; - END IF; - - FOR standing_penalty IN - SELECT DISTINCT csp.* - FROM actor.usr_standing_penalty usp - JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) - WHERE usr = match_user - AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) - AND (usp.stop_date IS NULL or usp.stop_date > NOW()) - AND (csp.ignore_proximity IS NULL - OR csp.ignore_proximity < home_prox - OR csp.ignore_proximity < item_prox) - AND csp.block_list LIKE penalty_type LOOP - - result.fail_part := standing_penalty.name; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END LOOP; - - -- Fail if the test is set to hard non-circulating - IF circ_matchpoint.circulate IS FALSE THEN - result.fail_part := 'config.circ_matrix_test.circulate'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - - -- Fail if the total copy-hold ratio is too low - IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN - SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); - IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN - result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - END IF; - - -- Fail if the available copy-hold ratio is too low - IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN - IF hold_ratio.hold_count IS NULL THEN - SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); - END IF; - IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN - result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - END IF; - - -- Fail if the user has too many items out by defined limit sets - FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls - JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id - WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR - ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough ) - ) LOOP - IF circ_limit_set.items_out > 0 AND NOT renewal THEN - SELECT INTO context_org_list ARRAY_AGG(aou.id) - FROM actor.org_unit_full_path( circ_ou ) aou - JOIN actor.org_unit_type aout ON aou.ou_type = aout.id - WHERE aout.depth >= circ_limit_set.depth; - IF circ_limit_set.global THEN - WITH RECURSIVE descendant_depth AS ( - SELECT ou.id, - ou.parent_ou - FROM actor.org_unit ou - WHERE ou.id IN (SELECT * FROM unnest(context_org_list)) - UNION - SELECT ou.id, - ou.parent_ou - FROM actor.org_unit ou - JOIN descendant_depth ot ON (ot.id = ou.parent_ou) - ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id); - END IF; - SELECT INTO items_out COUNT(DISTINCT circ.id) - FROM action.circulation circ - JOIN asset.copy copy ON (copy.id = circ.target_copy) - LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ) - WHERE circ.usr = match_user - AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list)) - AND circ.checkin_time IS NULL - AND circ.xact_finish IS NULL - AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL) - AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id) - OR copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_map WHERE limit_set = circ_limit_set.id) - OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id) - ); - IF items_out >= circ_limit_set.items_out THEN - result.fail_part := 'config.circ_matrix_circ_mod_test'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - END IF; - SELECT INTO result.limit_groups result.limit_groups || ARRAY_AGG(limit_group) FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id AND NOT check_only; - END LOOP; - - -- If we passed everything, return the successful matchpoint - IF NOT done THEN - RETURN NEXT result; - END IF; - - RETURN; -END; -$func$ LANGUAGE plpgsql; - -COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.lp1788260.schema.non-cat-in-house-use-aacct.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.lp1788260.schema.non-cat-in-house-use-aacct.sql deleted file mode 100644 index 853d0bd089..0000000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.lp1788260.schema.non-cat-in-house-use-aacct.sql +++ /dev/null @@ -1,60 +0,0 @@ -BEGIN; - -CREATE OR REPLACE VIEW action.all_circulation_combined_types AS - SELECT acirc.id AS id, - acirc.xact_start, - acirc.circ_lib, - acirc.circ_staff, - acirc.create_time, - ac_acirc.circ_modifier AS item_type, - 'regular_circ'::text AS circ_type - FROM action.circulation acirc, - asset.copy ac_acirc - WHERE acirc.target_copy = ac_acirc.id -UNION ALL - SELECT ancc.id::BIGINT AS id, - ancc.circ_time AS xact_start, - ancc.circ_lib, - ancc.staff AS circ_staff, - ancc.circ_time AS create_time, - cnct_ancc.name AS item_type, - 'non-cat_circ'::text AS circ_type - FROM action.non_cataloged_circulation ancc, - config.non_cataloged_type cnct_ancc - WHERE ancc.item_type = cnct_ancc.id -UNION ALL - SELECT aihu.id::BIGINT AS id, - aihu.use_time AS xact_start, - aihu.org_unit AS circ_lib, - aihu.staff AS circ_staff, - aihu.use_time AS create_time, - ac_aihu.circ_modifier AS item_type, - 'in-house_use'::text AS circ_type - FROM action.in_house_use aihu, - asset.copy ac_aihu - WHERE aihu.item = ac_aihu.id -UNION ALL - SELECT ancihu.id::BIGINT AS id, - ancihu.use_time AS xact_start, - ancihu.org_unit AS circ_lib, - ancihu.staff AS circ_staff, - ancihu.use_time AS create_time, - cnct_ancihu.name AS item_type, - 'non-cat-in-house_use'::text AS circ_type - FROM action.non_cat_in_house_use ancihu, - config.non_cataloged_type cnct_ancihu - WHERE ancihu.item_type = cnct_ancihu.id -UNION ALL - SELECT aacirc.id AS id, - aacirc.xact_start, - aacirc.circ_lib, - aacirc.circ_staff, - aacirc.create_time, - ac_aacirc.circ_modifier AS item_type, - 'aged_circ'::text AS circ_type - FROM action.aged_circulation aacirc, - asset.copy ac_aacirc - WHERE aacirc.target_copy = ac_aacirc.id; - -COMMIT; - diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_invoice_reporter_view.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_invoice_reporter_view.sql deleted file mode 100644 index 37b8223ba2..0000000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_invoice_reporter_view.sql +++ /dev/null @@ -1,185 +0,0 @@ -BEGIN; - -CREATE OR REPLACE VIEW reporter.acq_invoice_total_cost_billed AS -WITH acq_cost_billed (invoice, total_cost_billed) AS - (SELECT acqie.invoice, - COALESCE(SUM(acqie.cost_billed), '0.00') AS total_cost_billed - FROM acq.invoice_entry acqie - GROUP BY acqie.invoice - UNION ALL - SELECT acqii.invoice, - COALESCE(SUM(acqii.cost_billed), '0.00') AS total_cost_billed - FROM acq.invoice_item acqii - GROUP BY acqii.invoice) - SELECT invoice, COALESCE(SUM(total_cost_billed), '0.00') AS total_cost_billed - FROM acq_cost_billed - GROUP BY invoice; - -CREATE OR REPLACE VIEW reporter.acq_invoice_total_actual_cost AS -WITH acq_actual_cost (invoice, total_actual_cost) AS - (SELECT acqie.invoice, - COALESCE(SUM(acqie.actual_cost), '0.00') AS total_actual_cost - FROM acq.invoice_entry acqie - GROUP BY acqie.invoice - UNION ALL - SELECT acqii.invoice, - COALESCE(SUM(acqii.actual_cost), '0.00') AS total_actual_cost - FROM acq.invoice_item acqii - GROUP BY acqii.invoice) - SELECT invoice, COALESCE(SUM(total_actual_cost), '0.00') AS total_actual_cost - FROM acq_actual_cost - GROUP BY invoice; - -CREATE OR REPLACE VIEW reporter.acq_invoice_total_amount_paid AS -WITH acq_amount_paid (invoice, total_amount_paid) AS - (SELECT acqie.invoice, - COALESCE(SUM(acqie.amount_paid), '0.00') AS total_amount_paid - FROM acq.invoice_entry acqie - GROUP BY acqie.invoice - UNION ALL - SELECT acqii.invoice, - COALESCE(SUM(acqii.amount_paid), '0.00') AS total_amount_paid - FROM acq.invoice_item acqii - GROUP BY acqii.invoice) - SELECT invoice, COALESCE(SUM(total_amount_paid), '0.00') AS total_amount_paid - FROM acq_amount_paid - GROUP BY invoice; - -CREATE OR REPLACE VIEW reporter.acq_invoice_summary_view AS - SELECT invoice.id AS inv_id, - invoice.inv_ident AS inv_vendor_ident, - invoice.recv_date, - provider.id AS provider_id, - provider.name AS provider_name, - provider.code AS provider_code, - shipper.id AS shipper_id, - shipper.name AS shipper_name, - shipper.code AS shipper_code, - receiver.id AS receiver_id, - receiver.shortname AS receiver_shortname, - receiver.name AS receiver_name, - invoice.payment_auth, - invoice.payment_method, - raitcb.total_cost_billed AS total_cost_billed, - raitac.total_actual_cost AS total_actual_cost, - raitap.total_amount_paid AS total_amount_paid - FROM acq.invoice invoice - JOIN reporter.acq_invoice_total_cost_billed raitcb ON (raitcb.invoice = invoice.id) - JOIN reporter.acq_invoice_total_actual_cost raitac ON (raitac.invoice = invoice.id) - JOIN reporter.acq_invoice_total_amount_paid raitap ON (raitap.invoice = invoice.id) - JOIN acq.provider provider ON (invoice.provider = provider.id) - LEFT OUTER JOIN acq.provider shipper ON (invoice.shipper = shipper.id) - LEFT OUTER JOIN actor.org_unit receiver ON (invoice.receiver = receiver.id); - -CREATE VIEW reporter.acq_purchase_order_summary_view AS -SELECT po.id, - po.owner, - po.creator, - po.editor, - po.ordering_agency, - po.create_time, - po.edit_time, - po.provider, - po.state, - po.order_date, - po.name, - po.cancel_reason, - po.prepayment_required, - -- lineitem_count - (SELECT COALESCE(COUNT(*), 0) - FROM acq.lineitem li - WHERE li.purchase_order = po.id) AS lineitem_count, - -- amount_encumbered - (SELECT COALESCE(SUM(amount), '0.00') - FROM acq.fund_debit afd - WHERE afd.encumbrance = TRUE - AND (afd.id IN ( - SELECT fund_debit - FROM acq.lineitem_detail lid - WHERE lineitem IN ( - SELECT id - FROM acq.lineitem - WHERE purchase_order = po.id) - ) OR afd.id IN ( - SELECT fund_debit - FROM acq.po_item - WHERE purchase_order = po.id - ) OR afd.id IN ( - SELECT fund_debit - FROM acq.invoice_item - WHERE purchase_order = po.id - ))) AS amount_encumbered, - -- amount_spent - (SELECT COALESCE(SUM(amount), '0.00') - FROM acq.fund_debit afd - WHERE afd.encumbrance = FALSE - AND afd.id IN ( - SELECT fund_debit - FROM acq.lineitem_detail lid - WHERE lineitem IN ( - SELECT id - FROM acq.lineitem - WHERE purchase_order = po.id) - ) OR afd.id IN ( - SELECT fund_debit - FROM acq.po_item - WHERE purchase_order = po.id - ) OR afd.id IN ( - SELECT fund_debit - FROM acq.invoice_item - WHERE purchase_order = po.id - )) AS amount_spent, - -- amount_estimated - CASE - WHEN ( - SELECT id - FROM acq.fund_debit - WHERE id IN ( - SELECT fund_debit - FROM acq.lineitem_detail - WHERE lineitem IN ( - SELECT id - FROM acq.lineitem - WHERE purchase_order = po.id - ) - ) OR id IN ( - SELECT fund_debit - FROM acq.po_item - WHERE purchase_order = po.id - ) OR id IN ( - SELECT fund_debit - FROM acq.invoice_item - WHERE purchase_order = po.id - ) limit 1 - ) IS NOT NULL THEN ( - SELECT COALESCE(SUM(amount), '0.00') - FROM acq.fund_debit - WHERE id IN ( - SELECT fund_debit - FROM acq.lineitem_detail - WHERE lineitem IN ( - SELECT id - FROM acq.lineitem - WHERE purchase_order = po.id - ) - ) OR id IN ( - SELECT fund_debit - FROM acq.po_item - WHERE purchase_order = po.id - ) OR id IN ( - SELECT fund_debit - FROM acq.invoice_item - WHERE purchase_order = po.id - ) - ) ELSE ( - SELECT ( - COALESCE(SUM(li.estimated_unit_price), '0.00') + - COALESCE(SUM(poi.estimated_cost), '0.00') - ) FROM acq.lineitem li - LEFT OUTER JOIN acq.lineitem_detail lid ON (li.id = lid.lineitem) - LEFT OUTER JOIN acq.po_item poi ON (poi.purchase_order = po.id) - WHERE li.purchase_order = po.id - ) END AS amount_estimated -FROM acq.purchase_order po; - -COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron-guardian.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron-guardian.sql deleted file mode 100644 index c63e1ceee8..0000000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron-guardian.sql +++ /dev/null @@ -1,171 +0,0 @@ - -BEGIN; - -ALTER TABLE actor.usr ADD COLUMN guardian TEXT; - -CREATE INDEX actor_usr_guardian_idx - ON actor.usr (evergreen.lowercase(guardian)); -CREATE INDEX actor_usr_guardian_unaccent_idx - ON actor.usr (evergreen.unaccent_and_squash(guardian)); - --- Modify auditor tables accordingly. -SELECT auditor.update_auditors(); - --- clear the guardian field on delete -CREATE OR REPLACE FUNCTION actor.usr_delete( - src_usr IN INTEGER, - dest_usr IN INTEGER -) RETURNS VOID AS $$ -DECLARE - old_profile actor.usr.profile%type; - old_home_ou actor.usr.home_ou%type; - new_profile actor.usr.profile%type; - new_home_ou actor.usr.home_ou%type; - new_name text; - new_dob actor.usr.dob%type; -BEGIN - SELECT - id || '-PURGED-' || now(), - profile, - home_ou, - dob - INTO - new_name, - old_profile, - old_home_ou, - new_dob - FROM - actor.usr - WHERE - id = src_usr; - -- - -- Quit if no such user - -- - IF old_profile IS NULL THEN - RETURN; - END IF; - -- - perform actor.usr_purge_data( src_usr, dest_usr ); - -- - -- Find the root grp_tree and the root org_unit. This would be simpler if we - -- could assume that there is only one root. Theoretically, someday, maybe, - -- there could be multiple roots, so we take extra trouble to get the right ones. - -- - SELECT - id - INTO - new_profile - FROM - permission.grp_ancestors( old_profile ) - WHERE - parent is null; - -- - SELECT - id - INTO - new_home_ou - FROM - actor.org_unit_ancestors( old_home_ou ) - WHERE - parent_ou is null; - -- - -- Truncate date of birth - -- - IF new_dob IS NOT NULL THEN - new_dob := date_trunc( 'year', new_dob ); - END IF; - -- - UPDATE - actor.usr - SET - card = NULL, - profile = new_profile, - usrname = new_name, - email = NULL, - passwd = random()::text, - standing = DEFAULT, - ident_type = - ( - SELECT MIN( id ) - FROM config.identification_type - ), - ident_value = NULL, - ident_type2 = NULL, - ident_value2 = NULL, - net_access_level = DEFAULT, - photo_url = NULL, - prefix = NULL, - first_given_name = new_name, - guardian = NULL, - family_name = new_name, - suffix = NULL, - alias = NULL, - guardian = NULL, - day_phone = NULL, - evening_phone = NULL, - other_phone = NULL, - mailing_address = NULL, - billing_address = NULL, - home_ou = new_home_ou, - dob = new_dob, - active = FALSE, - master_account = DEFAULT, - super_user = DEFAULT, - barred = FALSE, - deleted = TRUE, - juvenile = DEFAULT, - usrgroup = 0, - claims_returned_count = DEFAULT, - credit_forward_balance = DEFAULT, - last_xact_id = DEFAULT, - alert_message = NULL, - create_date = now(), - expire_date = now() - WHERE - id = src_usr; -END; -$$ LANGUAGE plpgsql; - -INSERT into config.org_unit_setting_type (name, label, description, datatype) -VALUES ( - 'ui.patron.edit.au.guardian.show', - oils_i18n_gettext( - 'ui.patron.edit.au.guardian.show', - 'GUI: Show guardian field on patron registration', - 'coust', 'label' - ), - oils_i18n_gettext( - 'ui.patron.edit.au.guardian.show', - 'The guardian field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', - 'coust', 'description' - ), - 'bool' -), ( - 'ui.patron.edit.au.guardian.suggest', - oils_i18n_gettext( - 'ui.patron.edit.au.guardian.suggest', - 'GUI: Suggest guardian field on patron registration', - 'coust', 'label' - ), - oils_i18n_gettext( - 'ui.patron.edit.au.guardian.suggest', - 'The guardian field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', - 'coust', 'description'), - 'bool' -), ( - 'ui.patron.edit.guardian_required_for_juv', - oils_i18n_gettext( - 'ui.patron.edit.guardian_required_for_juv', - 'GUI: Juvenile account requires parent/guardian', - 'coust', 'label' - ), - oils_i18n_gettext( - 'ui.patron.edit.guardian_required_for_juv', - 'Require a value for the parent/guardian field in the patron editor for patrons marked as juvenile', - 'coust', 'description'), - 'bool' -); - - -COMMIT; - diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.webstaff.cat.copy.templates.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.webstaff.cat.copy.templates.sql deleted file mode 100644 index 9b9cee3c4e..0000000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.webstaff.cat.copy.templates.sql +++ /dev/null @@ -1,8 +0,0 @@ -BEGIN; - -SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); - -INSERT INTO config.usr_setting_type (name, label, description, datatype) - VALUES ('webstaff.cat.copy.templates', 'Web Client Copy Editor Templates', 'Web Client Copy Editor Templates', 'object'); - -COMMIT; diff --git a/Open-ILS/src/sql/Pg/version-upgrade/pines-post-3.4-upgrade.sql b/Open-ILS/src/sql/Pg/version-upgrade/pines-post-3.4-upgrade.sql index 7c12d3836c..a33956213c 100644 --- a/Open-ILS/src/sql/Pg/version-upgrade/pines-post-3.4-upgrade.sql +++ b/Open-ILS/src/sql/Pg/version-upgrade/pines-post-3.4-upgrade.sql @@ -75,6 +75,71 @@ insert into config.hold_matrix_matchpoint ( false ); +-- hopeless holds + +INSERT into config.workstation_setting_type (name, grp, datatype, label) +VALUES ( + 'eg.grid.hopeless.wide_holds', 'gui', 'object', + oils_i18n_gettext( + 'eg.grid.hopeless.wide_holds', + 'Grid Config: hopeless.wide_holds', + 'cwst', 'label' + ) +); + +ALTER TABLE config.copy_status ADD COLUMN hopeless_prone BOOL NOT NULL DEFAULT FALSE; -- 002.schema.config.sql +ALTER TABLE action.hold_request ADD COLUMN hopeless_date TIMESTAMP WITH TIME ZONE; -- 090.schema.action.sql + +-- OpenAthens + +CREATE TABLE config.openathens_uid_field ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL +); + +INSERT INTO config.openathens_uid_field + (id, name) +VALUES + (1,'id'), + (2,'usrname') +; + +SELECT SETVAL('config.openathens_uid_field_id_seq'::TEXT, 100); + +CREATE TABLE config.openathens_name_field ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL +); + +INSERT INTO config.openathens_name_field + (id, name) +VALUES + (1,'id'), + (2,'usrname'), + (3,'fullname') +; + +SELECT SETVAL('config.openathens_name_field_id_seq'::TEXT, 100); + +CREATE TABLE config.openathens_identity ( + id SERIAL PRIMARY KEY, + active BOOL NOT NULL DEFAULT true, + org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + api_key TEXT NOT NULL, + connection_id TEXT NOT NULL, + connection_uri TEXT NOT NULL, + auto_signon_enabled BOOL NOT NULL DEFAULT true, + auto_signout_enabled BOOL NOT NULL DEFAULT false, + unique_identifier INT NOT NULL REFERENCES config.openathens_uid_field (id) DEFAULT 1, + display_name INT NOT NULL REFERENCES config.openathens_name_field (id) DEFAULT 1, + release_prefix BOOL NOT NULL DEFAULT false, + release_first_given_name BOOL NOT NULL DEFAULT false, + release_second_given_name BOOL NOT NULL DEFAULT false, + release_family_name BOOL NOT NULL DEFAULT false, + release_suffix BOOL NOT NULL DEFAULT false, + release_email BOOL NOT NULL DEFAULT false, + release_home_ou BOOL NOT NULL DEFAULT false +); commit; -- 2.11.0