From 56ea3e761904e0852130efd32de601672b38e07c Mon Sep 17 00:00:00 2001 From: "Cameron E. Tidd" Date: Sun, 20 Jul 2025 00:30:38 +0000 Subject: [PATCH 15/16] Filtering the list based on the library limits works --- C4/Reports/Guided.pm | 7 - Koha/Schema/Result/ReportsBranch.pm | 44 +++--- Koha/Schema/Result/SavedSql.pm | 220 ++++++++++++++++++++++++++++ reports/guided_reports.pl | 65 +++++--- 4 files changed, 286 insertions(+), 50 deletions(-) diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index 9781d45e2f..94e271cef7 100644 --- a/C4/Reports/Guided.pm +++ b/C4/Reports/Guided.pm @@ -819,7 +819,6 @@ 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) @@ -874,18 +873,12 @@ 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/Schema/Result/ReportsBranch.pm b/Koha/Schema/Result/ReportsBranch.pm index d6d40f71b3..970c25eabc 100644 --- a/Koha/Schema/Result/ReportsBranch.pm +++ b/Koha/Schema/Result/ReportsBranch.pm @@ -1,4 +1,5 @@ use utf8; + package Koha::Schema::Result::ReportsBranch; # Created by DBIx::Class::Schema::Loader @@ -6,7 +7,7 @@ package Koha::Schema::Result::ReportsBranch; =head1 NAME -Koha::Schema::Result::ItemtypesBranch +Koha::Schema::Result::ReportsBranch =cut @@ -15,7 +16,7 @@ use warnings; use base 'DBIx::Class::Core'; -=head1 TABLE: C +=head1 TABLE: C =cut @@ -25,10 +26,9 @@ __PACKAGE__->table("reports_branches"); =head2 report_id - data_type: 'varchar' + data_type: 'integer' is_foreign_key: 1 - is_nullable: 1 - size: 80 + is_nullable: 0 =head2 branchcode @@ -40,10 +40,10 @@ __PACKAGE__->table("reports_branches"); =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 }, + "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 @@ -57,31 +57,29 @@ Related object: L =cut __PACKAGE__->belongs_to( - "branchcode", - "Koha::Schema::Result::Branch", - { branchcode => "branchcode" }, - { is_deferrable => 1, on_delete => "CASCADE", on_update => "RESTRICT" }, + "branchcode", + "Koha::Schema::Result::Branch", + { branchcode => "branchcode" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "RESTRICT" }, ); -=head2 itemtype +=head2 report Type: belongs_to -Related object: L +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" }, + "report", + "Koha::Schema::Result::SavedSql", + { 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 - +# 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/Koha/Schema/Result/SavedSql.pm b/Koha/Schema/Result/SavedSql.pm index 6f75ae3b31..4f063e8d67 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-06 17:02:11 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:qFqDaOxgdm2I12cJEiCieg +# 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; \ No newline at end of file diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index 726b315b55..74e495245d 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -1120,28 +1120,53 @@ 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->{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| ) { - $report->{seems_obsolete} = 1; - $has_obsolete_reports++; + $filter->{group} = $group; + $filter->{subgroup} = $subgroup; + + my $pref_enable_filtering_reports = C4::Context->preference("EnableFilteringReports"); + if ( $pref_enable_filtering_reports == "1" ) { + my $reports_with_library_limits_results = + Koha::Reports->search_with_library_limits( {}, {}, C4::Context::mybranch() ); + my $reports_list = $reports_with_library_limits_results->unblessed; + my $has_obsolete_reports; + while ( my $report = $reports_with_library_limits_results->next ) { + $report->{results} = C4::Reports::Guided::get_results( $report->{id} ); + if ( $report->{savedsql} =~ m|biblioitems| and $report->{savedsql} =~ m|marcxml| ) { + $report->{seems_obsolete} = 1; + $has_obsolete_reports++; + } + $template->param( + 'manamsg' => $input->param('manamsg') || '', + 'saved1' => 1, + 'savedreports' => $reports_list, + 'usecache' => $usecache, + 'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), + filters => $filter, + has_obsolete_reports => $has_obsolete_reports, + ); + } + } else { + + 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| ) { + $report->{seems_obsolete} = 1; + $has_obsolete_reports++; + } } + $template->param( + 'manamsg' => $input->param('manamsg') || '', + 'saved1' => 1, + 'savedreports' => $reports, + 'usecache' => $usecache, + 'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), + filters => $filter, + has_obsolete_reports => $has_obsolete_reports, + ); } - $template->param( - 'manamsg' => $input->param('manamsg') || '', - 'saved1' => 1, - 'savedreports' => $reports, - 'usecache' => $usecache, - 'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), - filters => $filter, - has_obsolete_reports => $has_obsolete_reports, - ); } # pass $sth, get back an array of names for the column headers -- 2.39.5