From 60a958ce4938d9c09593450e2c65371c56cb4e40 Mon Sep 17 00:00:00 2001 From: "Cameron E. Tidd" Date: Sat, 26 Jul 2025 02:02:50 +0000 Subject: [PATCH 1/2] Bug 16631: [DO NOT PUSH] Add schema files for reports_branches and adjust saved_sql table MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch adds the reports_branches table to enforce library limits for reports, and adjusts the saved_sql table to point to reports_branches. Sponsored by: Ramapo Catskill Library System Test plan: Confirm Initial Settings 1. Log in to Koha staff with username: koha and password: koha and set your library as Centerville if it’s not already. 2. Visit the Saved reports page, and create and save three reports: Report 1, Report 2, and Report 3. The SQL query for each report doesn't matter, it should just be something valid. 3. Visit the Patrons page and create two staff users: StaffReportLimitUser and StaffNoLimitPermission. Set their home libraries to Centerville and Fairfield, respectively. 4. Under Allow access to the reports module, ensure both users have report management permissions for Create, Delete, Execute. Verify Patch Settings 5. Apply the patch, then Restart services. 6. Click Patrons in the top navbar to go to the Patrons page. 7. Search for “StaffReportLimitUser” in the patron search field. 8. Click StaffReportLimitUser to edit the patron record for this user. 9. Click More -> Set permissions to access the permissions screens for the StaffReportLimitUser. 10. In the row for Allow access to the reports module (reports), click Show details to view relevant permissions. 11. Confirm Manage Report Limits (manage_report_limits) is an option under Allow access to the reports module (reports). 12. Click the checkbox for Manage Report Limits to give this user this permission, and click Save. 13. Go to the System Preferences page (Click the More dropdown from the top nav bar, click Administration, click System Preferences). 14. Verify Reports is an option in the left side navigation bar, and that when clicked, you see the LimitReportsByBranch option. 15. Enable LimitReportsByBranch as described in step 14. 16. Go back to Saved reports as in Step 2. 17. Click Edit in the row menu for Report 1. 18. At the bottom of the Report 1 edit page, you should see a Library limitation select box. Verify the Library Limitation Settings are Persisted 19. Choose Centerville from the select box and click Update SQL. 20. Reload the page, or navigate away from the page and go back to the edit screen for Report 1. 21. Verify that Centerville is still in the Library limitation select box. 22. Add Fairfield in the Library limitation select box and click Update and run SQL. 23. Go back to editing Report 1. 24. Confirm the Library limitation select box has both Centerville and Fairfield selected. 25. Duplicate Report 1. 26. Confirm the Library limitation select box has both Centerville and Fairfield selected. 27. Click Save Report. 28. Confirm the Library limitation select box has both Centerville and Fairfield selected. Verify Reports are Limited Based on Library Limitations 29. Edit Report 1 to add Centerville in the Library limitation select box as the only choice and click Update SQL. 30. Edit Report 2 to add Fairfield in the Library limitation select box as the only choice and click Update SQL. 31. Report 3 should have no library limitations, so it should be visible to all libraries. 32. Go to Saved reports as in Step 2. 33. Confirm you do not see Report 2, and only Report 1 and Report 3. 34. In the upper right corner, click the drop down that says koha Centerville and click Set library to change your library. 35. Set your library to Fairfield. 36. Go to Saved reports as in Step 2. 37. Confirm you do not see Report 1, and only Report 2 and Report 3. Verify Disabling LimitReportsByBranch Preference Shows All Reports 38. Disable LimitReportsByBranch preference as in steps 13 to 15. 39. Go to Saved reports as in Step 2. 40. Confirm Report 1, Report 2, Report 3 are visible. 41. Click to edit Report 1 and verify there is no Library limitation select box. Verify Library limitations are Persisted When EnableFilteringReports is Toggled On or Off 42. Enable EnableFilteringReports preference as in steps 13 to 15. 43. Ensure your library is set to Fairfield as in step 35. 44. Go to Saved reports as in Step 2. 45. Confirm Report 1 is not visible, and Report 2 and Report 3 are visible. 46. Click to edit Report 2 and verify the Library limitation select box is at the bottom of the page and that it has Fairfield selected. Verify the Show all reports option shows all reports for users with the Manage Report Limits permission and superlibrarians 47. Go to Saved reports as in Step 2 and in the Show reports dropdown, select Show all reports, and click Apply filter. 48. Confirm the page reloads with all reports visible: Report 1, Report 2, and Report 3 should be visible. 49. Select Show my branch reports from the dropdown and click Apply filter. 50. Confirm Report 1 is not visible, and Report 2 and Report 3 are visible (you should only see reports for your branch, Fairfield). Verify Report filters return the correct results 51. Go to Saved reports as in Step 2. 52. Verify you’re viewing only your branch’s reports in the filter. 53. Under Filter in the left sidebar, type “2” in the Keyword field and click Apply filter. 54. Confirm only Report 2 is visible. 55. Under Filter in the left sidebar, type “1” in the Keyword field and click Apply filter. 56. Confirm there are no reports visible, and the message "No saved reports match your criteria" is displayed. Verify Library Limits column is available to superlibrarians and users with the Manage Report Limits permission 57. Go to Saved reports as in Step 2. 58. Click the Columns dropdown and ensure that the Library limits column is available to select for display. Select it. 59. Confirm that the Library limits column is now visible in the list of reports and that it shows the correct library limitations for each report (e.g., Report 1 should show CPL (Centerville), Report 2 should show FFL (Fairfield), and Report 3 should show no limitations). Verify the Show all reports and Limit reports options are available to users that are not superlibrarian but have the Manage Report Limits permission 60. For the StaffReportLimitUser, add the permission for Manage Report Limits as described in steps 9 to 12. This will allow this user to see the Show all reports and Limit reports options. 61. Log out of the koha admin account. In the upper right corner, click the drop down that says koha Fairfield and click Log out. 62. Log in as StaffReportLimitUser, the previously created staff user with report permissions. 63. Confirm the staff user’s library is Centerville in the upper right corner of the screen. 64. Go to Saved reports as in Step 2. 65. Confirm the filter option for Show reports is visible. 66. Click to edit Report 1. 67. Verify there is a Library limitation select box and that it can be interacted with. Verify the Show all reports and Limit reports options are not available to users that are not superlibrarian and do not have the Manage Report Limits permission 68. Log out of the StaffReportLimitUser account. 69. Log in as a previously created staff user without report permissions. 70. Confirm the staff user’s library is Fairfield in the upper right corner of the screen. 71. Go to Saved reports as in Step 2. 72. Click the Columns dropdown and ensure that the Library limits column is not in the list of available columns to select for display. 73. Confirm the filter option for Show reports is not available. 74. Click to edit Report 2. 75. Verify there is no Library limitation select box at the bottom of the page. Verify limited reports are not visible to users without permissions 76. Log out of the staff user account without permissions. 77. Log in as a superlibrarian or a user with the Manage Report Limits permission. 78. Copy the URL for the edit page of Report 1, and log out of the superlibrarian or user with permissions account. 79. Log in as, StaffNoLimitPermission, the user without the Manage Report Limits permission. 80. Paste the copied URL for the edit page of Report 1 into the browser and navigate to it. 81. Confirm that you see an error message that says "This report is restricted and cannot be accessed by the current user." and that the page does not load the report details. Run Unit Tests 82. Using Koha Testing Docker, in the Koha home directory run the command: ktd --shell to start the ktd shell. 83. Run command: reset_all to drop and recreate the koha database. 84. Run command prove t/db_dependent/Koha/Reports.t to run the test. 85. The new unit tests for the reports_branches tables are in this file. 86. All tests should pass. 87. Run command prove t/Koha/Auth/Permissions.t 88. Permissions unit tests in this file were updated. 89. All tests should pass 90. Run command prove t/db_dependent/Reports/Guided.t 91. The Guided report tests were updated to account for the new library limitations and permissions. 92. All tests should pass. Signoff 93. Sign off if the steps above check out. --- Koha/Schema/Result/Branch.pm | 19 +++- Koha/Schema/Result/ReportsBranch.pm | 86 ++++++++++++++ Koha/Schema/Result/SavedSql.pm | 220 ++++++++++++++++++++++++++++++++++++ 3 files changed, 323 insertions(+), 2 deletions(-) create mode 100644 Koha/Schema/Result/ReportsBranch.pm diff --git a/Koha/Schema/Result/Branch.pm b/Koha/Schema/Result/Branch.pm index 787c077219..151e09a4f3 100644 --- a/Koha/Schema/Result/Branch.pm +++ b/Koha/Schema/Result/Branch.pm @@ -905,6 +905,21 @@ __PACKAGE__->has_many( { cascade_copy => 0, cascade_delete => 0 }, ); +=head2 reports_branches + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "reports_branches", + "Koha::Schema::Result::ReportsBranch", + { "foreign.branchcode" => "self.branchcode" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + =head2 reserves Type: has_many @@ -996,8 +1011,8 @@ __PACKAGE__->has_many( ); -# Created by DBIx::Class::Schema::Loader v0.07051 @ 2024-05-03 13:13:25 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:HiH1QNlDqKcq9GeM85Pu0A +# Created by DBIx::Class::Schema::Loader v0.07051 @ 2025-08-25 23:49:56 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:FzlapmLTM+K9Lh7Fq4BwdQ __PACKAGE__->has_many( "additional_field_values", diff --git a/Koha/Schema/Result/ReportsBranch.pm b/Koha/Schema/Result/ReportsBranch.pm new file mode 100644 index 0000000000..80472a5ff1 --- /dev/null +++ b/Koha/Schema/Result/ReportsBranch.pm @@ -0,0 +1,86 @@ +use utf8; + +package Koha::Schema::Result::ReportsBranch; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Koha::Schema::Result::ItemtypesBranch + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("reports_branches"); + +=head1 ACCESSORS + +=head2 report_id + + data_type: 'varchar' + is_foreign_key: 1 + is_nullable: 1 + size: 80 + +=head2 branchcode + + data_type: 'varchar' + is_foreign_key: 1 + is_nullable: 0 + size: 10 + +=cut + +__PACKAGE__->add_columns( + "report_id", + { data_type => "varchar", is_foreign_key => 1, is_nullable => 1, size => 80 }, + "branchcode", + { data_type => "varchar", is_foreign_key => 1, is_nullable => 0, size => 10 }, +); + +=head1 RELATIONS + +=head2 branchcode + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "branchcode", + "Koha::Schema::Result::Branch", + { branchcode => "branchcode" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "RESTRICT" }, +); + +=head2 itemtype + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "report_id", + "Koha::Schema::Result::Report", + { report_id => "report_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "RESTRICT" }, +); + +# Created by DBIx::Class::Schema::Loader v0.07048 @ 2019-07-04 04:56:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:cBTswjKV8VWN1iueB+PygQ + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/Koha/Schema/Result/SavedSql.pm b/Koha/Schema/Result/SavedSql.pm index 6f75ae3b31..88d54b52ef 100644 --- a/Koha/Schema/Result/SavedSql.pm +++ b/Koha/Schema/Result/SavedSql.pm @@ -185,6 +185,221 @@ __PACKAGE__->add_columns( __PACKAGE__->set_primary_key("id"); +=head1 RELATIONS + +=head2 reports_branches + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "reports_branches", + "Koha::Schema::Result::ReportsBranch", + { "foreign.report_id" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07051 @ 2025-07-26 01:58:59 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:w7XKvOV/ylS6Dq8tsUNmSg +# These lines were loaded from '/kohadevbox/koha/Koha/Schema/Result/SavedSql.pm' found in @INC. +# They are now part of the custom portion of this file +# for you to hand-edit. If you do not either delete +# this section or remove that file from @INC, this section +# will be repeated redundantly when you re-create this +# file again via Loader! See skip_load_external to disable +# this feature. + +use utf8; +package Koha::Schema::Result::SavedSql; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Koha::Schema::Result::SavedSql + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("saved_sql"); + +=head1 ACCESSORS + +=head2 id + + data_type: 'integer' + is_auto_increment: 1 + is_nullable: 0 + +unique id and primary key assigned by Koha + +=head2 borrowernumber + + data_type: 'integer' + is_nullable: 1 + +the staff member who created this report (borrowers.borrowernumber) + +=head2 date_created + + data_type: 'datetime' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +the date this report was created + +=head2 last_modified + + data_type: 'datetime' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +the date this report was last edited + +=head2 savedsql + + data_type: 'mediumtext' + is_nullable: 1 + +the SQL for this report + +=head2 last_run + + data_type: 'datetime' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +=head2 report_name + + data_type: 'varchar' + default_value: (empty string) + is_nullable: 0 + size: 255 + +the name of this report + +=head2 type + + data_type: 'varchar' + is_nullable: 1 + size: 255 + +always 1 for tabular + +=head2 notes + + data_type: 'mediumtext' + is_nullable: 1 + +the notes or description given to this report + +=head2 cache_expiry + + data_type: 'integer' + default_value: 300 + is_nullable: 0 + +=head2 public + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +=head2 report_area + + data_type: 'varchar' + is_nullable: 1 + size: 6 + +=head2 report_group + + data_type: 'varchar' + is_nullable: 1 + size: 80 + +=head2 report_subgroup + + data_type: 'varchar' + is_nullable: 1 + size: 80 + +=head2 mana_id + + data_type: 'integer' + is_nullable: 1 + +=cut + +__PACKAGE__->add_columns( + "id", + { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, + "borrowernumber", + { data_type => "integer", is_nullable => 1 }, + "date_created", + { + data_type => "datetime", + datetime_undef_if_invalid => 1, + is_nullable => 1, + }, + "last_modified", + { + data_type => "datetime", + datetime_undef_if_invalid => 1, + is_nullable => 1, + }, + "savedsql", + { data_type => "mediumtext", is_nullable => 1 }, + "last_run", + { + data_type => "datetime", + datetime_undef_if_invalid => 1, + is_nullable => 1, + }, + "report_name", + { data_type => "varchar", default_value => "", is_nullable => 0, size => 255 }, + "type", + { data_type => "varchar", is_nullable => 1, size => 255 }, + "notes", + { data_type => "mediumtext", is_nullable => 1 }, + "cache_expiry", + { data_type => "integer", default_value => 300, is_nullable => 0 }, + "public", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, + "report_area", + { data_type => "varchar", is_nullable => 1, size => 6 }, + "report_group", + { data_type => "varchar", is_nullable => 1, size => 80 }, + "report_subgroup", + { data_type => "varchar", is_nullable => 1, size => 80 }, + "mana_id", + { data_type => "integer", is_nullable => 1 }, +); + +=head1 PRIMARY KEY + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("id"); + # Created by DBIx::Class::Schema::Loader v0.07049 @ 2021-01-21 13:39:29 # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:198dNG9DGQzop+s5IHy7sw @@ -214,3 +429,8 @@ sub koha_objects_class { } 1; +# End of lines loaded from '/kohadevbox/koha/Koha/Schema/Result/SavedSql.pm' + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; -- 2.15.0