From 61f63bd3d32c07f02a6a1f9148a2e7694cf6931d Mon Sep 17 00:00:00 2001 From: Jason Boyer Date: Tue, 14 Dec 2021 10:33:32 -0500 Subject: [PATCH] Add Simple Reporter Schema Changes New permission for Simple Reporter: RUN_SIMPLE_REPORTS A simple_reporter field on reporter folder tables to maintain separation between Simple Reporter and It's Complicated. Also adjust indexes to allow duplicate names between reporters if for some reason you want to do that. And a reporter.completed_reports view to compliment reporter.currently_running. Sponsored-by: C/W MARS Sponsored-by: Missouri Evergreen Consortium Signed-off-by: Jason Boyer Signed-off-by: rfrasur Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 22 ++++++- Open-ILS/src/sql/Pg/reporter-schema.sql | 36 ++++++++++- .../sql/Pg/upgrade/XXXX.schema.simple_reporter.sql | 71 ++++++++++++++++++++++ 3 files changed, 124 insertions(+), 5 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.simple_reporter.sql diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 021ce8fe10..4b212703c3 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -1966,9 +1966,11 @@ INSERT INTO permission.perm_list ( id, code, description ) VALUES ( 635, 'DELETE_RECORD_NOTE', oils_i18n_gettext(635, 'Allow the user to delete a record note', 'ppl', 'description')), ( 636, 'ADMIN_STAFF_PORTAL_PAGE', oils_i18n_gettext( 636, - 'Update the staff client portal page', 'ppl', 'description' )) + 'Update the staff client portal page', 'ppl', 'description' )), ( 637, 'UPLOAD_COVER_IMAGE', oils_i18n_gettext(637, - 'Upload local cover images for added content.', 'ppl', 'description')) + 'Upload local cover images for added content.', 'ppl', 'description')), + ( 638, 'RUN_SIMPLE_REPORTS', oils_i18n_gettext(638, + 'Build and run simple reports', 'ppl', 'description')) ; SELECT SETVAL('permission.perm_list_id_seq'::TEXT, 1000); @@ -2344,6 +2346,7 @@ INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) 'CREATE_COPY_STAT_CAT_ENTRY', 'CREATE_COPY_STAT_CAT_ENTRY_MAP', 'RUN_REPORTS', + 'RUN_SIMPLE_REPORTS', 'CREATE_REPORT_TEMPLATE', 'SHARE_REPORT_FOLDER', 'UPDATE_COPY_LOCATION', @@ -2784,6 +2787,7 @@ INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) 'DELETE_INVOICE_METHOD', 'DELETE_PROVIDER', 'RUN_REPORTS', + 'RUN_SIMPLE_REPORTS', 'CREATE_REPORT_TEMPLATE', 'SHARE_REPORT_FOLDER', 'UPDATE_ACQ_FUNDING_SOURCE', @@ -19922,6 +19926,20 @@ VALUES ( 'Grid Config: circ.patron.billhistory_xacts', 'cwst', 'label' ) +), ( + 'eg.grid.reporter.simple.reports', 'gui', 'object', + oils_i18n_gettext( + 'eg.grid.reporter.simple.reports', + 'Grid Config: eg.grid.reporter.simple.reports', + 'cwst', 'label' + ) +), ( + 'eg.grid.reporter.simple.outputs', 'gui', 'object', + oils_i18n_gettext( + 'eg.grid.reporter.simple.outputs', + 'Grid Config: eg.grid.reporter.simple.outputs', + 'cwst', 'label' + ) ); diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index 066071f7c7..e4bb55a2fd 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -28,11 +28,12 @@ CREATE TABLE reporter.template_folder ( create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), name TEXT NOT NULL, shared BOOL NOT NULL DEFAULT FALSE, + simple_reporter BOOL NOT NULL DEFAULT FALSE, share_with INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED ); CREATE INDEX rpt_tmpl_fldr_owner_idx ON reporter.template_folder (owner); CREATE UNIQUE INDEX rpt_template_folder_once_parent_idx ON reporter.template_folder (name,parent); -CREATE UNIQUE INDEX rpt_template_folder_once_idx ON reporter.template_folder (name,owner) WHERE parent IS NULL; +CREATE UNIQUE INDEX rpt_template_folder_once_idx ON reporter.template_folder (name,owner,simple_reporter) WHERE parent IS NULL; CREATE TABLE reporter.report_folder ( id SERIAL PRIMARY KEY, @@ -41,11 +42,12 @@ CREATE TABLE reporter.report_folder ( create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), name TEXT NOT NULL, shared BOOL NOT NULL DEFAULT FALSE, + simple_reporter BOOL NOT NULL DEFAULT FALSE, share_with INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED ); CREATE INDEX rpt_rpt_fldr_owner_idx ON reporter.report_folder (owner); CREATE UNIQUE INDEX rpt_report_folder_once_parent_idx ON reporter.report_folder (name,parent); -CREATE UNIQUE INDEX rpt_report_folder_once_idx ON reporter.report_folder (name,owner) WHERE parent IS NULL; +CREATE UNIQUE INDEX rpt_report_folder_once_idx ON reporter.report_folder (name,owner,simple_reporter) WHERE parent IS NULL; CREATE TABLE reporter.output_folder ( id SERIAL PRIMARY KEY, @@ -54,11 +56,12 @@ CREATE TABLE reporter.output_folder ( create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), name TEXT NOT NULL, shared BOOL NOT NULL DEFAULT FALSE, + simple_reporter BOOL NOT NULL DEFAULT FALSE, share_with INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED ); CREATE INDEX rpt_output_fldr_owner_idx ON reporter.output_folder (owner); CREATE UNIQUE INDEX rpt_output_folder_once_parent_idx ON reporter.output_folder (name,parent); -CREATE UNIQUE INDEX rpt_output_folder_once_idx ON reporter.output_folder (name,owner) WHERE parent IS NULL; +CREATE UNIQUE INDEX rpt_output_folder_once_idx ON reporter.output_folder (name,owner,simple_reporter) WHERE parent IS NULL; CREATE TABLE reporter.template ( @@ -419,5 +422,32 @@ CREATE OR REPLACE VIEW reporter.currently_running AS JOIN actor.card c ON c.id = u.card WHERE s.start_time IS NOT NULL AND s.complete_time IS NULL; +CREATE OR REPLACE VIEW reporter.completed_reports AS + SELECT s.id AS run, + r.id AS report, + t.id AS template, + t.owner AS template_owner, + r.owner AS report_owner, + s.runner AS runner, + t.folder AS template_folder, + r.folder AS report_folder, + s.folder AS output_folder, + r.name AS report_name, + t.name AS template_name, + s.start_time, + s.run_time, + s.complete_time, + s.error_code, + s.error_text + FROM reporter.schedule s + JOIN reporter.report r ON r.id = s.report + JOIN reporter.template t ON t.id = r.template + WHERE s.complete_time IS NOT NULL; + +CREATE OR REPLACE FUNCTION reporter.intersect_user_perm_ou(context_ou BIGINT, staff_id BIGINT, perm_code TEXT) +RETURNS BOOLEAN AS $$ + SELECT CASE WHEN context_ou IN (SELECT * FROM permission.usr_has_perm_at_all(staff_id::INT, perm_code)) THEN TRUE ELSE FALSE END; +$$ LANGUAGE SQL; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.simple_reporter.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.simple_reporter.sql new file mode 100644 index 0000000000..a06d641d41 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.simple_reporter.sql @@ -0,0 +1,71 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); -- jboyer / / + +ALTER TABLE reporter.template_folder ADD COLUMN simple_reporter BOOLEAN DEFAULT FALSE; +ALTER TABLE reporter.report_folder ADD COLUMN simple_reporter BOOLEAN DEFAULT FALSE; +ALTER TABLE reporter.output_folder ADD COLUMN simple_reporter BOOLEAN DEFAULT FALSE; + +DROP INDEX reporter.rpt_template_folder_once_idx; +DROP INDEX reporter.rpt_report_folder_once_idx; +DROP INDEX reporter.rpt_output_folder_once_idx; + +CREATE UNIQUE INDEX rpt_template_folder_once_idx ON reporter.template_folder (name,owner,simple_reporter) WHERE parent IS NULL; +CREATE UNIQUE INDEX rpt_report_folder_once_idx ON reporter.report_folder (name,owner,simple_reporter) WHERE parent IS NULL; +CREATE UNIQUE INDEX rpt_output_folder_once_idx ON reporter.output_folder (name,owner,simple_reporter) WHERE parent IS NULL; + +-- Private "transform" to allow for simple report permissions verification +CREATE OR REPLACE FUNCTION reporter.intersect_user_perm_ou(context_ou BIGINT, staff_id BIGINT, perm_code TEXT) +RETURNS BOOLEAN AS $$ + SELECT CASE WHEN context_ou IN (SELECT * FROM permission.usr_has_perm_at_all(staff_id::INT, perm_code)) THEN TRUE ELSE FALSE END; +$$ LANGUAGE SQL; + +-- Hey committer, make sure this id is good to go and also in 950.data.seed-values.sql +INSERT INTO permission.perm_list (id, code, description) VALUES + ( 636, 'RUN_SIMPLE_REPORTS', oils_i18n_gettext(636, + 'Build and run simple reports', 'ppl', 'description')); + + +INSERT INTO config.workstation_setting_type (name, grp, datatype, label) +VALUES ( + 'eg.grid.reporter.simple.reports', 'gui', 'object', + oils_i18n_gettext( + 'eg.grid.reporter.simple.reports', + 'Grid Config: eg.grid.reporter.simple.reports', + 'cwst', 'label' + ) +), ( + 'eg.grid.reporter.simple.outputs', 'gui', 'object', + oils_i18n_gettext( + 'eg.grid.reporter.simple.outputs', + 'Grid Config: eg.grid.reporter.simple.outputs', + 'cwst', 'label' + ) +); + +-- new view parallel to reporter.currently_running +-- and reporter.overdue_reports +CREATE OR REPLACE VIEW reporter.completed_reports AS + SELECT s.id AS run, + r.id AS report, + t.id AS template, + t.owner AS template_owner, + r.owner AS report_owner, + s.runner AS runner, + t.folder AS template_folder, + r.folder AS report_folder, + s.folder AS output_folder, + r.name AS report_name, + t.name AS template_name, + s.start_time, + s.run_time, + s.complete_time, + s.error_code, + s.error_text + FROM reporter.schedule s + JOIN reporter.report r ON r.id = s.report + JOIN reporter.template t ON t.id = r.template + WHERE s.complete_time IS NOT NULL; + +COMMIT; + -- 2.11.0