From f911bec7a881ad55ac0b14389354fdb250082633 Mon Sep 17 00:00:00 2001 From: Casey Conlin Date: Fri, 25 Jul 2025 20:56:07 +0000 Subject: [PATCH] Squash commits for reports branch. --- C4/Reports/Guided.pm | 7 ++ Koha.pm | 1 + Koha/Manual.pm | 1 + Koha/Report.pm | 20 ++++- Koha/Reports.pm | 27 +++++- Koha/Schema/Result/ReportsBranch.pm | 86 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 13 +++ installer/data/mysql/updatedatabase.pl | 16 ++++ .../prog/en/includes/prefs-menu.inc | 11 +++ .../en/modules/admin/preferences/reports.pref | 9 ++ .../modules/reports/guided_reports_start.tt | 28 +++++- .../devel/Koha/Schema/Result/ReportsBranch.pm | 85 ++++++++++++++++++ reports/guided_reports.pl | 41 ++++++--- t/db_dependent/Koha/Reports.t | 38 +++++++- 14 files changed, 368 insertions(+), 15 deletions(-) create mode 100644 Koha/Schema/Result/ReportsBranch.pm create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/reports.pref create mode 100644 misc/devel/Koha/Schema/Result/ReportsBranch.pm diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index 94e271cef7..9781d45e2f 100644 --- a/C4/Reports/Guided.pm +++ b/C4/Reports/Guided.pm @@ -819,6 +819,7 @@ sub get_saved_reports_base_query { SELECT s.*, $area_name_sql_snippet, av_g.lib AS groupname, av_sg.lib AS subgroupname, b.firstname AS borrowerfirstname, b.surname AS borrowersurname FROM saved_sql s +INNER JOIN reports_branches rb ON s.id = rb.report_id LEFT JOIN saved_reports r ON r.report_id = s.id LEFT OUTER JOIN authorised_values av_g ON (av_g.category = 'REPORT_GROUP' AND av_g.authorised_value = s.report_group) LEFT OUTER JOIN authorised_values av_sg ON (av_sg.category = 'REPORT_SUBGROUP' AND av_sg.lib_opac = s.report_group AND av_sg.authorised_value = s.report_subgroup) @@ -873,12 +874,18 @@ sub get_saved_reports { push @cond, "report_subgroup = ?"; push @args, $filter->{subgroup}; } + if ( $filter->{branchcode} ) { + push @cond, "rb.branchcode = ?"; + push @args, $filter->{branchcode}; + } } $query .= " WHERE " . join( " AND ", map "($_)", @cond ) if @cond; $query .= " GROUP BY s.id, s.borrowernumber, s.date_created, s.last_modified, s.savedsql, s.last_run, s.report_name, s.type, s.notes, s.cache_expiry, s.public, s.report_area, s.report_group, s.report_subgroup, s.mana_id, av_g.lib, av_sg.lib, b.firstname, b.surname"; $query .= " ORDER by date_created"; + say STDERR $query; + my $result = $dbh->selectall_arrayref( $query, { Slice => {} }, @args ); return $result; diff --git a/Koha.pm b/Koha.pm index 7b7c35f959..57f5a41d10 100644 --- a/Koha.pm +++ b/Koha.pm @@ -29,6 +29,7 @@ use vars qw{ $VERSION }; # - #4 : the developer version. The 4th number is the database subversion. # used by developers when the database changes. updatedatabase take care of the changes itself # and is automatically called by Auth.pm when needed. + $VERSION = "25.06.00.007"; sub version { diff --git a/Koha/Manual.pm b/Koha/Manual.pm index fe5cc2c5b3..e811c64867 100644 --- a/Koha/Manual.pm +++ b/Koha/Manual.pm @@ -117,6 +117,7 @@ our $mapping = { 'admin/preferences#logs' => '/logspreferences.html', 'admin/preferences#opac' => '/opacpreferences.html', 'admin/preferences#patrons' => '/patronspreferences.html', + 'admin/preferences#reports' => '/reportspreferences.html', 'admin/preferences#searching' => '/searchingpreferences.html', 'admin/preferences#serials' => '/serialspreferences.html', 'admin/preferences#staff_interface' => '/staffclientpreferences.html', diff --git a/Koha/Report.pm b/Koha/Report.pm index 54511cdc4c..8c292a16a8 100644 --- a/Koha/Report.pm +++ b/Koha/Report.pm @@ -20,9 +20,13 @@ use Modern::Perl; use Koha::Database; use Koha::Reports; +use Koha::Object; +use Koha::Object::Limit::Library; + +use base qw(Koha::Object Koha::Object::Limit::Library); + #use Koha::DateUtils qw( dt_from_string output_pref ); -use base qw(Koha::Object); # # FIXME We could only return an error code instead of the arrayref # Only 1 error is returned @@ -268,4 +272,18 @@ sub _type { return 'SavedSql'; } +=head3 _library_limits + +Configurable library limits + +=cut + +sub _library_limits { + return { + class => "ReportsBranch", + id => "report_id", + library => "branchcode", + }; +} + 1; diff --git a/Koha/Reports.pm b/Koha/Reports.pm index 7d99232b9b..2803c8c02c 100644 --- a/Koha/Reports.pm +++ b/Koha/Reports.pm @@ -21,7 +21,7 @@ use Koha::Database; use Koha::Report; -use base qw(Koha::Objects); +use base qw(Koha::Objects Koha::Objects::Limit::Library); =head1 NAME @@ -33,6 +33,31 @@ Koha::Reports - Koha Report Object set class =cut +=head3 search_with_localization + +=cut + +sub search_with_localization { + my ( $self, $params, $attributes ) = @_; + + my $language = C4::Languages::getlanguage(); + $Koha::Schema::Result::Itemtype::LANGUAGE = $language; + $attributes->{order_by} = 'translated_description' unless exists $attributes->{order_by}; + $attributes->{join} = 'localization'; + $attributes->{'+select'} = [ + { + coalesce => [qw( localization.translation me.description )], + -as => 'translated_description' + } + ]; + if ( defined $params->{branchcode} ) { + my $branchcode = delete $params->{branchcode}; + $self->search_with_library_limits( $params, $attributes, $branchcode ); + } else { + $self->SUPER::search( $params, $attributes ); + } +} + =head3 _type Returns name of corresponding DBIC resultset 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/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e7448b7fca..dd06d8a58a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -5772,6 +5772,19 @@ CREATE TABLE `saved_reports` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `reports_branches`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8mb4 */; +CREATE TABLE `reports_branches` ( + `report_id` int(11) NOT NULL, + `branchcode` varchar(10) NOT NULL, + KEY `report_id` (`report_id`), + KEY `branchcode` (`branchcode`), + CONSTRAINT `reports_branches_ibfk_1` FOREIGN KEY (`report_id`) REFERENCES `saved_sql` (`id`) ON DELETE CASCADE, + CONSTRAINT `reports_branches_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `saved_sql` -- diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index adbd5e4c50..b234d7cfad 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -29455,6 +29455,22 @@ if ( CheckVersion($DBversion) ) { NewVersion( $DBversion, 28108, "Add new systempreference OpacHiddenItemsHidesRecord" ); } +$DBversion = '25.06.00.003'; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do( + "CREATE TABLE `reports_branches` ( + `report_id` int(11) NOT NULL, + `branchcode` varchar(10) NOT NULL, + KEY `report_id` (`report_id`), + KEY `branchcode` (`branchcode`), + CONSTRAINT `reports_branches_ibfk_1` FOREIGN KEY (`report_id`) REFERENCES `saved_sql` (`id`) ON DELETE CASCADE, + CONSTRAINT `reports_branches_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;" + ); + print "Upgrade to $DBversion done (reports_branches added)\n"; + SetVersion($DBversion); +} + $DBversion = '21.05.00.000'; if ( CheckVersion($DBversion) ) { NewVersion( $DBversion, "", "Koha 21.05.00 release" ); diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/prefs-menu.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/prefs-menu.inc index 937e174112..2b17d1f079 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/prefs-menu.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/prefs-menu.inc @@ -165,6 +165,17 @@ [% END %] + [% IF ( reports ) %] +
  • + Reports + [% PROCESS subtabs %] +
  • + [% ELSE %] +
  • + Reports +
  • + [% END %] + [% IF ( searching ) %]
  • Searching diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/reports.pref b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/reports.pref new file mode 100644 index 0000000000..1579aaf090 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/reports.pref @@ -0,0 +1,9 @@ +Reports: + Reports Access: + - + - pref: EnableFilteringReports + choices: + 1: Enable + 0: Disable + - "filtering report access based on staff home library." + - "
    NOTE: A NOTE ABOUT THE EFFECTS OF THIS PREFERENCE / SETTING" \ No newline at end of file diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt index b954e48a9d..ade44dd2c7 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt @@ -3,6 +3,7 @@ [% USE AuthorisedValues %] [% USE KohaDates %] [% USE Koha %] +[% USE Branches %] [% USE TablesSettings %] [% USE HtmlScrubber %] [% USE JSON.Escape %] @@ -850,6 +851,7 @@ + @@ -1434,7 +1436,18 @@ Required - + [% IF ( Koha.Preference('EnableFilteringReports') ) %] +
    + Library limitation: +
    + +
    Limits the use of this report to the selected libraries.
    +
    +
    + [% END %]
    @@ -1548,7 +1561,18 @@
    Required
    - + [% IF ( Koha.Preference('EnableFilteringReports') ) %] +
    + Library limitation: +
    + +
    Limits the use of this report to the selected libraries.
    +
    +
    + [% END %]
    diff --git a/misc/devel/Koha/Schema/Result/ReportsBranch.pm b/misc/devel/Koha/Schema/Result/ReportsBranch.pm new file mode 100644 index 0000000000..970c25eabc --- /dev/null +++ b/misc/devel/Koha/Schema/Result/ReportsBranch.pm @@ -0,0 +1,85 @@ +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::ReportsBranch + +=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: 'integer' + is_foreign_key: 1 + is_nullable: 0 + +=head2 branchcode + + data_type: 'varchar' + is_foreign_key: 1 + is_nullable: 0 + size: 10 + +=cut + +__PACKAGE__->add_columns( + "report_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "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 report + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "report", + "Koha::Schema::Result::SavedSql", + { id => "report_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "RESTRICT" }, +); + +# Created by DBIx::Class::Schema::Loader v0.07051 @ 2025-07-06 17:02:11 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:SJqUxMgLJHAjbX3GJlpB+A + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index fe47501ee7..90096bb778 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -57,6 +57,7 @@ my $input = CGI->new; my $usecache = Koha::Caches->get_instance->memcached_cache; my $op = $input->param('op') // ''; + my $flagsrequired; if ( ( $op eq 'add_form' ) || ( $op eq 'add_form_sql' ) @@ -127,7 +128,7 @@ if ( !$op ) { 'showsql' => 1, 'mana_success' => scalar $input->param('mana_success'), 'mana_id' => $report->{mana_id}, - 'mana_comments' => $report->{comments} + 'mana_comments' => $report->{comments}, ); } elsif ( $op eq 'edit_form' ) { @@ -136,6 +137,7 @@ if ( !$op ) { my $group = $report->report_group; my $subgroup = $report->report_subgroup; my $tables = get_tables(); + $template->param( 'sql' => $report->savedsql, 'reportname' => $report->report_name, @@ -148,11 +150,13 @@ if ( !$op ) { 'editsql' => 1, 'mana_id' => $report->{mana_id}, 'mana_comments' => $report->{comments}, - 'tables' => $tables + 'tables' => $tables, + 'report' => $report, ); } elsif ( $op eq 'cud-update_sql' || $op eq 'cud-update_and_run_sql' ) { my $id = $input->param('id'); + my $report = Koha::Reports->find($id); my $sql = $input->param('sql'); my $reportname = $input->param('reportname'); my $group = $input->param('group'); @@ -162,10 +166,10 @@ if ( !$op ) { my $cache_expiry_units = $input->param('cache_expiry_units'); my $public = $input->param('public'); my $save_anyway = $input->param('save_anyway'); + my $tables = get_tables(); + my @branches = grep { $_ ne q{} } $input->multi_param('branches'); my @errors; - my $tables = get_tables(); - # if we have the units, then we came from creating a report from SQL and thus need to handle converting units if ($cache_expiry_units) { if ( $cache_expiry_units eq "minutes" ) { $cache_expiry *= 60; @@ -211,6 +215,8 @@ if ( !$op ) { 'problematic_authvals' => $problematic_authvals, 'warn_authval_problem' => 1, 'phase_update' => 1, + 'branches' => @branches, + 'report' => $report, ); } else { @@ -229,6 +235,9 @@ if ( !$op ) { } ); + $report->store; + $report->replace_library_limits( \@branches ); + my $editsql = 1; if ( $op eq 'cud-update_and_run_sql' ) { $editsql = 0; @@ -245,7 +254,8 @@ if ( !$op ) { 'cache_expiry' => $cache_expiry, 'public' => $public, 'usecache' => $usecache, - 'tables' => $tables + 'tables' => $tables, + 'report' => $report, ); logaction( "REPORTS", "MODIFY", $id, "$reportname | $sql" ) if C4::Context->preference("ReportsLog"); } @@ -514,6 +524,7 @@ if ( !$op ) { my $public = $input->param('public'); my $save_anyway = $input->param('save_anyway'); my $tables = get_tables(); + my @branches = grep { $_ ne q{} } $input->multi_param('branches'); # if we have the units, then we came from creating a report from SQL and thus need to handle converting units if ($cache_expiry_units) { @@ -591,6 +602,9 @@ if ( !$op ) { public => $public, } ); + my $report = Koha::Reports->find($id); + $report->replace_library_limits( \@branches ); + logaction( "REPORTS", "ADD", $id, "$name | $sql" ) if C4::Context->preference("ReportsLog"); $template->param( 'save_successful' => 1, @@ -603,7 +617,8 @@ if ( !$op ) { 'cache_expiry' => $cache_expiry, 'public' => $public, 'usecache' => $usecache, - 'tables' => $tables + 'tables' => $tables, + 'report' => $report, ); } } @@ -746,20 +761,23 @@ if ( !$op ) { } elsif ( $op eq 'add_form_sql' || $op eq 'duplicate' ) { - my ( $group, $subgroup, $sql, $reportname, $notes ); + my ( $group, $subgroup, $sql, $reportname, $notes, @branches, $report ); if ( $input->param('sql') ) { $group = $input->param('report_group'); $subgroup = $input->param('report_subgroup'); $sql = $input->param('sql') // ''; $reportname = $input->param('reportname') // ''; $notes = $input->param('notes') // ''; + @branches = grep { $_ ne q{} } $input->multi_param('branches'); } elsif ( my $report_id = $input->param('id') ) { - my $report = Koha::Reports->find($report_id); + $report = Koha::Reports->find($report_id); $group = $report->report_group; $subgroup = $report->report_subgroup; $sql = $report->savedsql // ''; $reportname = $report->report_name // ''; $notes = $report->notes // ''; + @branches = grep { $_ ne q{} } $input->multi_param('branches'); + } my $tables = get_tables(); @@ -774,6 +792,7 @@ if ( !$op ) { 'cache_expiry' => 300, 'usecache' => $usecache, 'tables' => $tables, + 'report' => $report, ); } @@ -1079,10 +1098,12 @@ if ( $op eq 'list' || $op eq 'convert' ) { # get list of reports and display them my $group = $input->param('group'); my $subgroup = $input->param('subgroup'); - $filter->{group} = $group; - $filter->{subgroup} = $subgroup; + $filter->{group} = $group; + $filter->{subgroup} = $subgroup; + $filter->{branchcode} = C4::Context::mybranch(); my $reports = get_saved_reports($filter); my $has_obsolete_reports; + for my $report (@$reports) { $report->{results} = C4::Reports::Guided::get_results( $report->{id} ); if ( $report->{savedsql} =~ m|biblioitems| and $report->{savedsql} =~ m|marcxml| ) { diff --git a/t/db_dependent/Koha/Reports.t b/t/db_dependent/Koha/Reports.t index 205b1df60a..9778357d87 100755 --- a/t/db_dependent/Koha/Reports.t +++ b/t/db_dependent/Koha/Reports.t @@ -18,7 +18,7 @@ use Modern::Perl; use Test::NoWarnings; -use Test::More tests => 9; +use Test::More tests => 10; use Koha::Report; use Koha::Reports; @@ -185,4 +185,40 @@ subtest '_might_add_limit' => sub { ); }; +subtest 'reports_branches are added and removed from report_branches table' => sub { + plan tests => 4; + + my $updated_nb_of_reports = Koha::Reports->search->count; + my $report = Koha::Report->new( + { + report_name => 'report_name_for_test_1', + savedsql => 'SELECT * FROM items WHERE itemnumber IN <>', + } + )->store; + + my $id = $report->id; + my $library1 = $builder->build_object( { class => 'Koha::Libraries' } ); + my $library2 = $builder->build_object( { class => 'Koha::Libraries' } ); + my $library3 = $builder->build_object( { class => 'Koha::Libraries' } ); + my @branches = ( $library1->branchcode, $library2->branchcode, $library3->branchcode ); + + $report->replace_library_limits( \@branches ); + + my @branches_loop = $report->get_library_limits->as_list; + is( scalar @branches_loop, 3, '3 branches added to report_branches table' ); + + $report->replace_library_limits( [ $library1->branchcode, $library2->branchcode ] ); + + @branches_loop = $report->get_library_limits->as_list; + is( scalar @branches_loop, 2, '1 branch removed from report_branches table' ); + + $report->delete; + is( Koha::Reports->search->count, $updated_nb_of_reports, 'Report deleted, count is back to original' ); + is( + $schema->resultset('ReportsBranch')->search( { report_id => $id } )->count, + 0, + 'No branches left in reports_branches table after report deletion' + ); +}; + $schema->storage->txn_rollback; -- 2.39.5