From 198c21d0c0866b16104b57f0ce08ddea5329ef1e Mon Sep 17 00:00:00 2001 From: Matt Blenkinsop Date: Thu, 8 May 2025 11:05:05 +0100 Subject: [PATCH] Bug 33430: Pass search parameters to the template This patch removes the existing process intensive searches and moves the search logic to a single group query that returns the values that will be the counts in the tabs. It then formats the parameters for each type and passes them to the template for the datatables ajax requests Signed-off-by: Emily Lamancusa Signed-off-by: Lisette Scheer Signed-off-by: krimsonkharne --- Koha/Schema/Result/Suggestion.pm | 24 ++++++++ suggestion/suggestion.pl | 102 ++++++++++++++++++------------- 2 files changed, 84 insertions(+), 42 deletions(-) diff --git a/Koha/Schema/Result/Suggestion.pm b/Koha/Schema/Result/Suggestion.pm index fdbd355b53..969c040798 100644 --- a/Koha/Schema/Result/Suggestion.pm +++ b/Koha/Schema/Result/Suggestion.pm @@ -572,6 +572,30 @@ __PACKAGE__->belongs_to( }, ); +__PACKAGE__->belongs_to( + "manager", + "Koha::Schema::Result::Borrower", + { "foreign.borrowernumber" => "self.managedby" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "SET NULL", + on_update => "CASCADE", + }, +); + +__PACKAGE__->belongs_to( + "last_modifier", + "Koha::Schema::Result::Borrower", + { "foreign.borrowernumber" => "self.managedby" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "SET NULL", + on_update => "CASCADE", + }, +); + __PACKAGE__->add_columns( '+archived' => { is_boolean => 1 }, ); diff --git a/suggestion/suggestion.pl b/suggestion/suggestion.pl index a72e7a2a48..de37a7196f 100755 --- a/suggestion/suggestion.pl +++ b/suggestion/suggestion.pl @@ -425,67 +425,85 @@ if ( $op eq 'else' ) { $suggestion_ref->{branchcode} = C4::Context->userenv->{'branch'}; } + my $search_params = {%$suggestion_ref}; + + # filter on date fields + foreach my $field (qw( suggesteddate manageddate accepteddate )) { + my $from = delete $search_params->{"${field}_from"}; + my $to = delete $search_params->{"${field}_to"}; + + my $from_dt = $from && eval { dt_from_string($from) }; + my $to_dt = $to && eval { dt_from_string($to) }; + + if ( $from_dt || $to_dt ) { + my $dtf = Koha::Database->new->schema->storage->datetime_parser; + if ( $from_dt && $to_dt ) { + $search_params->{$field} = + { -between => [ $dtf->format_date($from_dt), $dtf->format_date($to_dt) ] }; + } elsif ($from_dt) { + $search_params->{$field} = { '>=' => $dtf->format_date($from_dt) }; + } elsif ($to_dt) { + $search_params->{$field} = { '<=' => $dtf->format_date($to_dt) }; + } + } + } + if ( $search_params->{budgetid} && $search_params->{budgetid} eq '__NONE__' ) { + $search_params->{budgetid} = [ undef, '' ]; + } + for my $f (qw (branchcode budgetid)) { + delete $search_params->{$f} + if $search_params->{$f} eq '__ANY__' + || $search_params->{$f} eq ''; + } + for my $bi (qw (title author isbn publishercode copyrightdate collectiontitle)) { + $search_params->{$bi} = { 'LIKE' => "%" . $search_params->{$bi} . "%" } if $search_params->{$bi}; + } + + $search_params->{archived} = 0 if !$filter_archived; + foreach my $key ( keys %$search_params ) { + if ( $key eq 'branchcode' ) { + my $branch_param = delete $search_params->{$key}; + $search_params->{"me.branchcode"} = $branch_param; + } + } + + # Retrieve the count to display on each tab + my $suggestion_tabs = Koha::Suggestions->search_limited( + $search_params, + { + select => [ $displayby, { count => $displayby, -as => 'count' } ], + as => [ $displayby, 'count' ], + group_by => [$displayby], + } + ); + my $tab_counts = { map { $_->get_column($displayby) => $_->get_column('count') } $suggestion_tabs->as_list }; + my @allsuggestions; foreach my $criteriumvalue (@criteria_dv) { - my $search_params = {%$suggestion_ref}; + # By default, display suggestions from current working branch + my $definedvalue = defined $$suggestion_ref{$displayby} && $$suggestion_ref{$displayby} ne ""; next if $search_params->{STATUS} && $displayby eq 'STATUS' && $criteriumvalue ne $search_params->{STATUS}; - # By default, display suggestions from current working branch - my $definedvalue = defined $$suggestion_ref{$displayby} && $$suggestion_ref{$displayby} ne ""; - next if ( $definedvalue && $$suggestion_ref{$displayby} ne $criteriumvalue ) and ( $displayby ne 'branchcode' && $branchfilter ne '__ANY__' ); - $search_params->{$displayby} = $criteriumvalue; - - # filter on date fields - foreach my $field (qw( suggesteddate manageddate accepteddate )) { - my $from = delete $search_params->{"${field}_from"}; - my $to = delete $search_params->{"${field}_to"}; - - my $from_dt = $from && eval { dt_from_string($from) }; - my $to_dt = $to && eval { dt_from_string($to) }; - - if ( $from_dt || $to_dt ) { - my $dtf = Koha::Database->new->schema->storage->datetime_parser; - if ( $from_dt && $to_dt ) { - $search_params->{$field} = - { -between => [ $dtf->format_date($from_dt), $dtf->format_date($to_dt) ] }; - } elsif ($from_dt) { - $search_params->{$field} = { '>=' => $dtf->format_date($from_dt) }; - } elsif ($to_dt) { - $search_params->{$field} = { '<=' => $dtf->format_date($to_dt) }; - } - } - } - if ( $search_params->{budgetid} && $search_params->{budgetid} eq '__NONE__' ) { - $search_params->{budgetid} = [ undef, '' ]; - } - for my $f (qw (branchcode budgetid)) { - delete $search_params->{$f} - if $search_params->{$f} eq '__ANY__' - || $search_params->{$f} eq ''; - } - for my $bi (qw (title author isbn publishercode copyrightdate collectiontitle)) { - $search_params->{$bi} = { 'LIKE' => "%" . $search_params->{$bi} . "%" } if $search_params->{$bi}; - } - - $search_params->{archived} = 0 if !$filter_archived; - my @suggestions = Koha::Suggestions->search_limited($search_params)->as_list; + my $criterium_search_params = {%$search_params}; + $criterium_search_params->{"me.$displayby"} = $criteriumvalue; push @allsuggestions, { "suggestiontype" => $criteriumvalue || "suggest", "suggestiontypelabel" => GetCriteriumDesc( $criteriumvalue, $displayby ) || "", - 'suggestions' => \@suggestions, 'reasonsloop' => $reasonsloop, + 'search_params' => $criterium_search_params, + 'tab_count' => $tab_counts->{$criteriumvalue}, } - if scalar @suggestions > 0; + if $tab_counts->{$criteriumvalue} > 0; delete $$suggestion_ref{$displayby} unless $definedvalue; } -- 2.39.5