- Add null check around Koha::Patrons->find() in guided_reports.pl
before calling ->has_permission, matching the defensive pattern
already used in Koha::Report::check_edit_permission
- Add missing description to Koha::Exceptions::Report base class
for consistency with its sub-exceptions
Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk>
Signed-off-by: Pedro Amorim <pedro.amorim@openfifth.co.uk>
Test plan:
1) Apply this patch set
2) Run updatedatabase.pl
3) Restart all the things!
4) Create 2 users with create_reports and not edit_all_reports
5) Create reports with each
6) Note that each cannot edit the others reports
7) Create a user with create_reports and edit_all_reports
8) Note the this user can edit reports from the previous two users
Edit button placements to look for:
* Reports table row
* Actions menu
* Toolbar
Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Signed-off-by: Brendan Lawlor <blawlor@clamsnet.org>
Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk>
Signed-off-by: Pedro Amorim <pedro.amorim@openfifth.co.uk>
Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Signed-off-by: Brendan Lawlor <blawlor@clamsnet.org>
Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk>
Signed-off-by: Pedro Amorim <pedro.amorim@openfifth.co.uk>
As a continuation of bug 41919, we should limit the total number of simultaneous reports that can be run at a time.
Test Plan:
1) Set total_running_reports_per_instance_limit to 2 in your koha-conf.xml
2) Create 3 long running reports like: SELECT COUNT(*) FROM items i1 JOIN items i2 JOIN items i3 JOIN items i4 JOIN items i5 JOIN items i6
3) Run each report in turn by opening a new tab, browsing to the reports
module, and choosing to run that report
5) Note the first two continue to run, but the last one gives an error
that you have too many reports running simultaneously!
Signed-off-by: Alex Carver [Acerock7] <alex@rcls.org>
Signed-off-by: Andrew Fuerste Henry <andrew@bywatersolutions.com>
Signed-off-by: Pedro Amorim <pedro.amorim@openfifth.co.uk>
This patch implements the library_limit concept for Report/Reports. Staff with the correct permission and superlibrarians can set libaries to which the report should be visible, if the system preference is enabled.
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 and restart services. If using Koha Testing Docker (KTD): from the Koha repo directory, apply the patch (e.g. `git bz apply 16631`, or check out the patched branch with `git fetch && git checkout <branch>`), then run `ktd --shell` to enter the KTD shell and run `restart_all`. If the patched branch is already checked out, just enter the KTD shell and run `restart_all`.
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 LimitReportsByBranch 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 1 library limitation and Centerville (CPL) on hover, Report 2 should show 1 library limitation and Fairfield (FFL) on hover, and Report 3 should show no limitation).
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 StaffNoLimitPermission, the previously created staff user without the Manage Report Limits permission.
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.
[NEW] Verify library limits are auto-assigned and preserved correctly for non-permissioned users
76. Still logged in as StaffNoLimitPermission (Fairfield) from the previous section. Go to Saved reports and click New report. Enter "Limit Auto-Assign Test" as the report name and SELECT 1 as the SQL. Confirm there is no Library limitations select box visible on the form. Click Save.
77. Go back to Saved reports. Click Edit for Report 2 (limited to Fairfield). Add `-- test` as a trailing SQL comment and click Update SQL. This simulates a non-permissioned user saving an existing report that already has limits.
78. Go back to Saved reports. Click Edit for Report 3 (no library limits). Add `-- test` as a trailing SQL comment and click Update SQL. This simulates a non-permissioned user saving an existing report that has no limits.
79. Log out and log back in as StaffReportLimitUser (Centerville, has manage_report_limits).
80. Go to Saved reports. Use Show all reports if needed. Find "Limit Auto-Assign Test" and click Edit. Confirm the Library limitations select box shows Fairfield as the only selected library — the report was automatically limited to the creator's home library on create.
81. Find Report 2 and click Edit. Confirm the Library limitations select box still shows only Fairfield — the existing limit was preserved when the non-permissioned user saved the report.
82. Find Report 3 and click Edit. Confirm the Library limitations select box is empty — no limits were introduced when the non-permissioned user saved a report that had no existing limits.
Verify limited reports are not visible to users without permissions
83. While still logged in as StaffReportLimitUser from step 79, copy the URL for the edit page of Report 1.
84. Log out of the StaffReportLimitUser account.
85. Log in as StaffNoLimitPermission, the user without the Manage Report Limits permission.
86. Paste the copied URL for the edit page of Report 1 into the browser and navigate to it.
87. 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
88. Using Koha Testing Docker, in the Koha home directory run the command: ktd --shell to start the ktd shell.
89. Run command: reset_all to drop and recreate the koha database.
90. Run command prove t/db_dependent/Koha/Reports.t to run the test.
91. This file contains the unit tests for the reports_branches table and the new subtest `library limits enforcement on save and update`, which covers auto-assignment of branch limits on create and preservation of limits on update for non-permissioned users.
92. All tests should pass.
93. Run command prove t/Koha/Auth/Permissions.t
94. Permissions unit tests in this file were updated.
95. All tests should pass.
96. Run command prove t/db_dependent/Reports/Guided.t
97. The Guided report tests were updated to account for the new library limitations and permissions.
98. All tests should pass.
Signoff
99. Sign off if the steps above check out.
Sponsored-by: Ramapo Catskill Library System
Co-authored-by: Casey Conlin <casey.conlin@gmail.com>
Signed-off-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi>
Signed-off-by: Andrew Fuerste Henry <andrew@bywatersolutions.com>
Signed-off-by: Pedro Amorim <pedro.amorim@openfifth.co.uk>
Previously, report templates were rendered using process_tt directly.
This made the available variables inconsistent with other notices and
slips. This patch switches to rendering report templates through the
same function as other notices. To match the arguments to this system,
this patch also makes the report system reference templates by their
code rather than ID.
Test plan:
1. Under Tools > Notices and Slips, create a new notice in the Reports
module. Choose a code and a name, and under Email assign a subject
and the following body:
[% today %]
[% FOREACH d IN data %]
[% d.title %]
[% END %]
2. Under Reports, create a new report from SQL with a name and the
following query:
SELECT * FROM biblio LIMIT 1
3. Create another report from SQL with a different name and the
following query, specifying a runtime parameter:
SELECT * FROM biblio WHERE 'a'=<<Param>> LIMIT 1
4. On the first report, select "Run with template" and choose the
template created earlier.
5. Verify that the report runs successfully, and the output does not
include the current date.
6. On the second report, select "Run with template" and choose the
template created earlier. Choose "a" as the runtime parameter value
when asked.
7. Verify that the report runs successfully, and the output does not
include the current date.
8. Apply patch.
9. On the first report, select "Run with template" and choose the
template created earlier.
10. Verify that the report runs successfully, and the output now
includes the current date along with the fetched data.
11. Under the Download dropdown, select "Rendered template (...)".
12. Verify the downloaded file matches the report output.
13. On the second report, select "Run with template" and choose the
template created earlier. Choose "a" as the runtime parameter value
when asked.
14. Verify that the report runs successfully, and the output now
includes the current date along with the fetched data.
15. Under the Download dropdown, select "Rendered template (...)".
16. Verify the downloaded file matches the report output.
Sponsored-by: Open Fifth
Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Signed-off-by: Pedro Amorim <pedro.amorim@openfifth.co.uk>
Bug 34478 removed a '&' by mistake in the redirect URL
Test plan:
1. Setup a local instance of Mana
(https://gitlab.com/koha-community/koha-mana) with disabled email
validation (skip_email_validation in config.yml)
2. Connect Koha to this local instance (mana_config in $KOHA_CONF)
3. From Koha Admin interface, create a Mana security token
4. Share a report to Mana
5. You should be redirected to the list of reports with a message saying
the report was saved successfully
Signed-off-by: Emmanuel Bétemps <e.betemps@gmail.com>
Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Pedro Amorim <pedro.amorim@openfifth.co.uk>
Building on bug 41918, it would be good to limit the number of reports
a given user can run at a time. This control is config-file based as
it is tied to the server resources provided by the administrator.
Adds a new koha-conf.xml option total_running_reports_per_user_limit
(0 = disabled). When the configured limit is reached, the user is told
"You have too many reports running at the moment. Please wait for some
of them to finish before running this report again."
Note on implementation: the original patch on bz (attachment 198034)
performed a second pre-flight Koha::Reports->running call in
reports/guided_reports.pl. The bug 41918 QA follow-ups already pushed
the duplicate-running guard into Koha::Report::prep_report so it would
fire across the staff UI, svc/report and opac/svc/report. This patch
extends that same model-layer architecture rather than reintroducing
controller-layer enforcement:
- New Koha::Exceptions::Report::TotalRunning subclass of
Koha::Exceptions::Report.
- prep_report now performs a second check after the duplicate one and
throws TotalRunning when the user has total_running_reports_per_user_limit
reports of any kind already in flight.
- All three callers (guided_reports.pl, svc/report, opac/svc/report)
catch the new exception alongside DuplicateRunning, mapping it to a
new template error key total_running_reports_limit_exceeded for the
staff UI and a JSON {error: total_running_reports_limit_exceeded}
with HTTP 429 for the JSON endpoints.
Test Plan:
1) Set total_running_reports_per_user_limit to 3 in your koha-conf.xml.
2) Create 3 long-running reports like:
SELECT COUNT(*) FROM items i1 JOIN items i2 JOIN items i3 JOIN items i4 JOIN items i5 JOIN items i6
3) Run each report in turn by opening a new tab, browsing to the
reports module, and choosing to run that report.
4) Note the first three continue to run, but a fourth attempt gives
the new "too many reports running" alert in the staff UI.
5) Repeat against /cgi-bin/koha/svc/report?id=N and confirm an HTTP
429 response with JSON {error: total_running_reports_limit_exceeded}.
6) Set total_running_reports_per_user_limit back to 0 and confirm
normal behaviour resumes.
7) prove t/db_dependent/Koha/Reports.t -> 13 tests pass, including a
new subtest for TotalRunning.
Signed-off-by: Alex Carver [Acerock7] <alex@rcls.org>
Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
The CSV/tab export branch (op=export) of reports/guided_reports.pl
calls Koha::Report::prep_report just like the op=run branch and the
two svc/report endpoints, but unlike the others it had no eval/catch
around the call. The QA follow-ups that pushed enforcement into the
model layer therefore left this one path emitting a 500 (uncaught
Koha::Exceptions::Report::DuplicateRunning) when the configured
duplicate-running limit is exceeded.
Wrap the call in the same pattern as svc/report: on
DuplicateRunning return HTTP 429 Too Many Requests with a plain-text
body explaining the situation. Plain text rather than the staff
template because op=export sets file-download headers and the user is
expecting either the file or an error - we don't have a sensible way
to fall back into the report-run template from this branch.
The CLI runners (misc/cronjobs/runreport.pl, misc/export_records.pl)
still bypass prep_report and therefore the limit, which is intended:
scheduled / batch jobs should not be subject to user-quota
enforcement.
Test plan:
1) Set duplicate_running_reports_per_user_limit=1 in koha-conf.xml,
restart_all.
2) Start a long-running report from the staff UI.
3) While it is running, navigate to op=export with the same report
id (eg. via the "Export" button).
4) Before the patch: 500 with a stack trace in the logs.
After the patch: 429 with body "This report is already running.
Please wait for it to finish before running it again."
5) Set the limit back to 0 and confirm exports work normally again.
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
The previous patches enforced duplicate_running_reports_per_user_limit
only in reports/guided_reports.pl. Two other code paths run saved SQL
reports for an authenticated user and bypassed the guard entirely:
- svc/report (staff JSON endpoint, eg. used for chart widgets and any
caller hitting /cgi-bin/koha/svc/report?id=N directly)
- opac/svc/report (OPAC JSON endpoint)
A user who hit their staff-UI limit could still spawn additional copies
of the same long-running report via either svc endpoint, defeating the
guard.
Push the check into Koha::Report::prep_report itself, which is the one
function all three callers go through. When the limit is reached
prep_report now throws a new Koha::Exceptions::Report::DuplicateRunning
(carrying report_id, user_id and the configured limit). The guided
reports CGI catches it and re-renders the existing "report is already
running" alert; both svc/report endpoints catch it and return HTTP 429
Too Many Requests with a JSON { error: "duplicate_running_report" }
payload.
Notes:
- The pre-flight Koha::Reports->running call is removed from the
guided-reports CGI; the model now owns that policy and the
controller is just rendering. The TOCTOU window remains identical
in size and is the same intentional trade-off as before.
- Limit=0 (the default) and absent userenv both bypass the check, so
CLI cronjobs (misc/cronjobs/runreport.pl) and direct DB users are
unaffected.
Test plan:
1) prove t/db_dependent/Koha/Reports.t - now includes a new subtest
covering limit=0 (lives), under limit (lives) and at limit (throws
Koha::Exceptions::Report::DuplicateRunning).
2) Set duplicate_running_reports_per_user_limit=1 in koha-conf.xml,
restart_all.
3) Open a long-running report in two staff tabs - second tab shows
the "report is already running" alert (unchanged behaviour).
4) While one run is in flight, hit /cgi-bin/koha/svc/report?id=N as
the same user - now returns HTTP 429 with the JSON error payload
(previously: ran a second copy of the query).
5) Same check on /cgi-bin/koha/opac/svc/report?id=N.
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Two small documentation improvements following on from the duplicate-
running guard:
- Koha::Report::prep_report now spells out the tracking-comment format
in its POD. Koha::Reports->running parses this comment, and external
log/audit tooling that consumes Koha SQL relied on the previous
format (/* saved_sql.id: <id> */). Calling out the new format
explicitly makes the public contract visible to anyone reading the
POD or grepping for it, and warns future contributors that changing
the wrapper will break running() too.
- reports/guided_reports.pl gets an inline comment explaining the
TOCTOU window between the count check and the actual report
execution. The window is intentional - the guard exists to stop
accidental double-clicks and two-tab submissions, not to be a
serializing lock - so worst case is one extra concurrent run, which
is acceptable and cheaper than the alternative.
No behaviour change.
Test plan:
1) prove t/db_dependent/Koha/Reports.t still passes.
2) perldoc Koha::Report -> prep_report section now mentions the
saved_sql.id / user_id wrapper format.
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
- Fix POD synopsis: invalid syntax and wrong field (userid vs
borrowernumber) replaced with a correct example and parameter list.
- Build the WHERE clause with parameterised bindings via
$schema->storage->dbh, removing string interpolation of
$user_id / $report_id into LIKE patterns and the regex match.
IDs are formatted with %d so non-integer input becomes 0 and cannot
inject regex/LIKE metacharacters.
- Return a Koha::Reports resultset instead of a list of MySQL process
ids. The method name now matches what it returns and callers can use
the standard ->count / iteration interface. The single in-tree caller
in reports/guided_reports.pl is updated; the template error key is
simplified from duplicate_running_report_ids to a boolean
duplicate_running_report (the ids were never displayed).
Test plan:
1) Apply the previous Bug 41918 patches and this follow-up.
2) Set duplicate_running_reports_per_user_limit to 1 in koha-conf.xml.
3) Restart all the things.
4) Open a long-running saved report in two tabs and run it from both.
5) Verify the second tab shows "This report is already running..."
6) Set the limit to 0 and confirm both tabs run normally.
7) prove t/db_dependent/Reports.t still passes.
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
This commit add the option duplicate_running_reports_per_user_limit to
koha-conf.xml. If this option is empty or 0, the feature is disabled. If
this option is enabled, Koha will limit the number of times a given
report can be run of a single Koha user.
Test Plan:
1) Apply this patch
2) Set duplicate_running_reports_per_user_limit to 1, verify previous
behavior is maintained
2) Set duplicate_running_reports_per_user_limit to 0, verify the feature
is disabled
3) Set duplicate_running_reports_per_user_limit to 2, verify Koha limits
the number of duplicates of the report to the number specified in duplicate_running_reports_per_user_limit
Signed-off-by: Alex Carver [Acerock7] <alex@rcls.org>
Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Test Plan:
1) create a long running report such as:
SELECT COUNT(*) FROM items i1 JOIN items i2 JOIN items i3 JOIN items i4 JOIN items i5 JOIN items i6
2) Run this report multiple times
3) Note the processes are running via SHOW FULL PROCESSLIST from koha-mysql
4) Kill those processes
5) Apply this patch
6) Restart all the things!
7) Try to run the report in two tabs, note the second tab opened says
the report is already running!
Signed-off-by: Alex Carver [Acerock7] <alex@rcls.org>
Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
This patch splits delete_reports into delete_own_reports and delete_all_reports
While delete_all_reports supercedes delete_own_reports it seems to make more sense than
delete_own_reports and delete_others_reports.
With delete_all_reports we can have a user who can delete all, without specific permission to delete their own
The other way we would have delete others, and you could not delete your own, which seems more odd
To test:
1 - Have 2 users - 1 with delete_reports, 1 without
2 - Apply patches, update database
3 - Confirm user with permission now has 'delete_all_reports' and other user still has no reports deletion permission
4 - Give the second user 'delete_own_reports' permission
5 - Create 4 reports as each user
6 - Confirm view/edit pages show the delete button for each user, and confirm they both work
7 - You shoudl have 2 reports left for each user
8 - Confirm each user can delete their own report from the saved SQL list:
http://localhost:8081/cgi-bin/koha/reports/guided_reports.pl?op=list
9 - There should be 1 report from each
10 - Give the second user 'delete_all_reports' permission in one tab and reload the saved reports in another tab
11 - Select both reports in the second tab
12 - Remove 'delete_all_reports' from the second user, leaving 'delete_own_reports' in the first tab
13 - Click 'Delete selected' in the reports tab
14 - Confirm you get 'You do not have permission to continue'
15 - Hit back, deselect other users report, confirm you can delete your own with 'Delete selected'
16 - Sign off
Signed-off-by: Judy Poyer <jpoyer@aacpl.net>
Signed-off-by: David Cook <dcook@prosentient.com.au>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Test plan:
0. Apply the patch and restart_all
1. Using a user with only "create_reports", "execute_reports", or "delete_reports",
try to perform operations not covered by that permission.
That is, if you have "create_reports", try to run, export, and delete reports.
If you have "execute_reports", try to create, modify, and delete reports
If you have "delete_reports", try to create, modify, and run reports
2. Note that a user with each of these permissions can list and view SQL.
Signed-off-by: David Cook <dcook@prosentient.com.au>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
This bug refactors the preview sql buttons to pass the permissions granted for each report to
the javascript that clones and updates the modal.
That allows us to check for the appropriate permissions when preparing the buttons for the modal
and to remove any that are not allowed.
This allows for different permissions per report for future devs
(see bugs 42406, 16631)
To test:
1. Create some reports
2. Create a user with only execute_reports permissions
3. Login as that user and go to Reports > use saved
4. In the actions column click the ^ and select 'Preview sql'
5. Notice delete button renders and works to delete
6. Apply patch and restart_all
7. Repeat step 4, now only the Run report button renders and works
8. Add permissions for create_reports
9. Repeat step for, now Edit, Duplicate, Schedule and run buttons render and work
10. Add permissions for delete_reports
11. Repeat step 4, confirm the delete button renders and works
12. Confirm the preview sql button works to preview different reports
We also fix some error in the script, several wrong things there:
* delete was not replaced with cud-delete
* missing op for the creation of new reports
* update and run requires two permssions
Signed-off-by: David Cook <dcook@prosentient.com.au>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Scott Barter <sbarter@aacpl.net>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Signed-off-by: Michaela Sieber <michaela.sieber@kit.edu>
Signed-off-by: Andrew Fuerste Henry <andrew@bywatersolutions.com>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
This is a POC (i.e. not ready for inclusion yet)
It adds a "Open in DataTables" button in the toolbar, that will refetch
the report (with a limit=100000, that needs to be configurable, how?)
and load the results in a kohaTable (DataTables)
Signed-off-by: Michaela Sieber <michaela.sieber@kit.edu>
Signed-off-by: Andrew Fuerste Henry <andrew@bywatersolutions.com>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: Brendan Lawlor <blawlor@clamsnet.org>
Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Signed-off-by: Owen Leonard <oleonard@myacpl.org>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
This change makes it so that you can provide runtime parameters
and use "Run with template".
Test plan:
0. Apply the patch and koha-plack --restart kohadev
1. Create a report template. For example:
<table>
<tr><th>Transformed Biblionumber</th></tr>
[% FOREACH row IN data %]
<tr><td>Biblionumber [% row.biblionumber %]</td></tr>
[% END %]
</table>
2. Create a SQL report with a runtime parameter. For example:
SELECT biblionumber
FROM biblio
WHERE biblionumber > <<biblionumber>>
3. Run the report using "Run with template"
4. Specify a biblionumber of 1
5. Click "Run the report"
6. Note that the output is the template output and not regular
SQL output
Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
This commit is generated using:
% perl misc/devel/tidy.pl
*within* ktd, to get the same version of perltidy than what will be used
by our CI (currently v20230309).
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
reports/dictionary.tt and reports/guided_reports_start.tt display a list
of the columns available using optgroup to group the columns per table.
The template code is quite ugly because of the perl structure choose in
C4::Reports::Guided->get_columns
This patch removes $cgi and the 'first' variable that were not used.
Now we have an usual structure with the table names as keys and column
info in an arrayref.
Test plan:
Create a report and a new dictionary definition.
At step 3 you should see the list of the columns.
Note that they are now identical. The list when you create a dictionary
now show the description of the columns.
Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
The system is vulnerable to Formula Injection attacks as the data
stored within the database and exported as CSV/Excel is not being
sanitized or validated against implanted formula payloads
This patch modifies all uses of Text::CSV and derived classes to pass
the "formula" parameter with value of "empty" which replaces formulas
by empty string.
Test Plan:
1) Apply this patch
2) For guided_reports.pl, attempt to export CSV where you've set a column to a formula somehow
( such as "=1+3" )
3) Export that CSV file
4) Note the formula has not been exported
5) Repeat this plan for the remaining scripts that export CSV files
where users can define the outputted data
Signed-off-by: Magnus Enger <magnus@libriotech.no>
Fixed two conflicts. I have tested that this works as advertised on:
- Reports (Download > Comma separated text (.csv)) [Text::CSV::Encoded]
- Circulation > Overdues > Download file of all overdues [Text::CSV_XS]
- misc/export_borrowers.pl [Text::CSV]
This covers all modules used, and both GUI and command line.
Signed-off-by: Chris Cormack <chris@bigballofwax.co.nz>
Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
[EDIT] Change none to empty in the commit message ! None is the default,
doing nothing. Empty clears the formulas.
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
Downloading SQL report in .tab format was significantly
slower after changes made in bug 37382. Apparently using
HTML::Scrubber to redeclare content variable after
mapping it is slow. Instead sending whole map function
as parameter to module speeds up downloading to couple
of seconds as it was before.
To test:
1. Find or create report that returns several thousand rows
(e.g. SELECT * FROM items LIMIT 4000).
2. Run report and attempt to download it as .tab file.
=> Notice that this takes a lot of time (around 20 seconds).
3. Apply this patch.
4. Download report again.
=> Dowloading should now take couple second.
=> Confirm that the content of .tab file still contains table
data.
Sponsored-by: Koha-Suomi Oy
Signed-off-by: Paul Derscheid <paul.derscheid@lmscloud.de>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
When you export a report as CSV excel will not recognise that it's
encoding is utf-8 unless there is a correct Byte Order Mark prepended to
the file.
This patch adds such a BOM to the output file allowing Excel to treat
the file correctly.
Signed-off-by: Michaela Sieber <michaela.sieber@kit.edu>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
The pref ReportsExportFormatODS should be true for exporting
to ODS in Reporting.
Test plan:
Set pref to false value.
Check if option is no longer available on report's Download menu.
Manipulate URL with op=export&format=ods. No data expected.
Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
Test plan:
Set pref ReportsExportLimit to some positive integer.
Test if exporting a report respects that limit.
Run t/db_dependent/Koha/Reports.t
Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
Just attaching this to start.
Sponsored-by: Waikato Institute of Technology
Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
This patch corrects an error where the SQL editor screen would appear
after the report results when using 'Update and run SQL' when editing
reports.
This also corrects a related issue where the saved_sql.id of the report
would be repeatedly appended to SQL code when using 'Update and run
SQL'.
To test:
1) Login to staff client
2) Navigate to Reports -> Create from SQL
3) Create a short report (SELECT * FROM items), name it and SAVE it.
4) On the resulting "Edit SQL report' page, click 'Update and run SQL'
5) See the report runs, but at the 'Edit SQL report' screen shows at the
bottom.
6) Apply Patch
7) Return to Reports -> Saved Reports and Edit the report you created.
8) On "Edit SQL report' page, click 'Update and run SQL'
9) Verify that the report runs, but the 'Edit SQL report' section is
gone.
Sponsored-by: Westlake Porter Public Library
Signed-off-by: Sam Sowanick <sam.sowanick@corvallisoregon.gov>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
This patch removes escaping of ampersands in the part of
guided_reports.pl that builds information for the pagination routine.
The information is going to be escaped in a later step.
To test, apply the patch and go to Reports.
- If necessary, create a report which will return more than 20 results.
- Run the report. You should see a pagination bar at the top of the
table of results.
- View the page source and look for the markup for the pagination menu.
Searching for 'rel="start"' works well.
- Confirm that you see '&' between the link parameters instead of
'&amp;', e.g.
guided_reports.pl?id=347&op=run&limit=20&want_full_chart=0&page=1
Signed-off-by: Fridolin Somers <fridolin.somers@biblibre.com>
Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
When exporting a report in tab delimited format, the downloaded file
would have the header rows but no data results.
Also, misc files are created on the server file system in koha/reports
with data that should have been in the .tab file.
This patch fixes both of these issues.
To test:
1. Login to staff client
2. Go to Reports and create a report from SQL - sample report
SELECT i.barcode, b.title, b.biblionumber, i.itemnumber
FROM items i
LEFT JOIN biblio b on i.biblionumber = b.biblionumber
WHERE i.itemnumber < 50
3. Run the report
4. Click 'Download'->'Tab separated text'
5. Download file
6. Open file in a text editor or favorite spreadsheet app
File will have headers rows, but no other item data
6a. If able, check the server filesystem in koha/reports
There may be files there related to report data. Remove.
7. Apply Patch
8. Repeat steps 3-6,
File will now have the table data, hooray!
8a. If able, check the server filesystem in koha/reports
No new files should be created.
Signed-off-by: Laura ONeil <laura@bywatersolutions.com>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
To test:
1 - Create a report like:
SELECT "a"
FROM borrowers
WHERE <<Test>> != ''
2 - Run report
3 - Enter "password"
4 - Internal server error / stacktrace
5 - Apply patch
6 - Repeat
7 - Get a yellow warning box
Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
To test:
- apply patch, restart all
- run a report
- export results
- confirm file name is the report ID, followed by report name
Signed-off-by: Barbara Johnson <barbara.johnson@bedfordtx.gov>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
We already have a scrubber module we can use; This patch switches us
from the newly introduced dependancy to using the existing and well
respected Scrubber we already have.
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
This patch uses HTML::Restrict to strip out HTML tags from the CSV
download of reports.
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
Rebased for changes to guided_reports_start.tt
This patch adds the ability to use multi select in report dropdowns.
It uses syntax similar to using a list and when using the :all option
WHERE i.homebranch in <<Select libraries|brnaches:in>>
Test plan:
1. Create an SQL report with with new syntax for multi select
SELECT
i.homebranch,
count(*)
FROM items i
WHERE i.homebranch in <<Select libraries|branches:in>>
GROUP BY i.homebranch
2. Save the report
3. Note that you get the error
The authorized value category (branches:in) you selected does not exist.
4. Apply the patch and repeat steps 1 and 2
5. Note the report saves successfully
6. Run the report
7. Note the select dropdown is now a multiselect
8. Run the report with multiple selections
10. Click show SQL and note that multi select parameters get inserted into the query like:
WHERE i.homebranch in ('CPL', 'FFL', 'FPL')
11. Test other types of queries with multiple multi selects and lists etc
Sponsored-by: CLAMS
Signed-off-by: Andrew Fuerste-Henry <andrewfh@dubcolib.org>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
Bug 34478: [TO SQUASH] Add 'op' to reports/guided_reports
Found bug 36071 when working on this. This is not a regression from this
patch.
Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Bug 34136 adds the ability to render a report using a notice template rather than displaying data in a table. It would be even more useful to be able to download the contents as a file where the notice subject can be used as the filename so the filename can be generated dynamically ( such as adding the current date as part of the filename ).
Test Plan:
1) Follow the test plan for Bug 34136
2) Run your report
3) Note under the Download menu the new item "Download as" with the
subject line for your template
4) Click that link, note the file contains the contents of your report!
Signed-off-by: Sam Lau <samalau@gmail.com>
Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Martin Renvoize
Kyle M Hall
Lucas Gass
Casey Conlin
Cameron E. Tidd
Olivia Reynolds
Julian Maurice
Nick Clemens
David Cook
Jonathan Druart
Tomas Cohen Arazi
Koha Development Team
Emmi Takkinen
Martin Renvoize
Marcel de Rooy
Aleisha Amohia
CJ Lynce
Owen Leonard
Andrew Fuerste Henry
Brendan Lawlor
Katrin Fischer