8127 – Most-circulated items report doesn't work when limited by library

Bug 8127 - Most-circulated items report doesn't work when limited by library

Summary: Most-circulated items report doesn't work when limited by library

Status:	Pushed to stable

Alias:	None

Product:	Koha
Classification:	Unclassified
Component:	Reports (show other bugs)
Version:	Main
Hardware:	All All

Importance:	P5 - low normal
Target Milestone:	---
Assignee:	Baptiste Wojtkowski (bwoj)
QA Contact:	Lucas Gass (lukeg)

URL:
Keywords:	no-sandbox

Depends on:
Blocks:

Reported:	2012-05-18 17:48 UTC by Nicole C. Engard
Modified:	2026-08-20 07:41 UTC (History)
CC List:	7 users (show)

See Also:	42736
GIT URL:
Initiative type:	---
Strategic theme:	---
Sponsorship status:	---
Comma delimited list of Sponsors:
Crowdfunding goal:	0
Crowdfunding committed:	0
Crowdfunding contact:
Patch complexity:	Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:	This fixes some of the filters for the "Most-circulated items" report so that using limits (the Limits section with "Limit to" and "By") now works. Previously some options for "By" (such as Library and Week) generated an error message or there were no results (when results were expected).
Version(s) released in:	26.11.00,26.05.03,25.05.14
Circulation function:

Attachments
Bug 8127: Remove useless commented warns (1.61 KB, patch) 2026-05-20 15:01 UTC, Baptiste Wojtkowski (bwoj)	Details \| Diff \| Splinter Review
Bug 8127: Fix filters use in 'Most-circulated items report' (4.72 KB, patch) 2026-05-20 15:02 UTC, Baptiste Wojtkowski (bwoj)	Details \| Diff \| Splinter Review
Bug 8127: Remove useless commented warns (1.65 KB, patch) 2026-05-21 19:12 UTC, David Nind	Details \| Diff \| Splinter Review
Bug 8127: Fix filters use in 'Most-circulated items report' (4.77 KB, patch) 2026-05-21 19:12 UTC, David Nind	Details \| Diff \| Splinter Review
Bug 8127: Remove useless commented warns (1.71 KB, patch) 2026-07-15 21:28 UTC, Lucas Gass (lukeg)	Details \| Diff \| Splinter Review
Bug 8127: Fix filters use in 'Most-circulated items report' (4.83 KB, patch) 2026-07-15 21:28 UTC, Lucas Gass (lukeg)	Details \| Diff \| Splinter Review
Show Obsolete (4) View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.

Description Nicole C. Engard 2012-05-18 17:48:37 UTC

I got this from a library:


I'm trying to get the "Most Circulated Items" report to give me something and I am not having success.  I chose a date range of 4 months, then I chose a library, then I chose a single item type (DVD1week) then I chose 20 for the limit, then I chose "library" for the "By" part of the limit and go an empty list.  So I went back and changed "library" to "Itemtype."  Now the system just hangs up spinning away without ever giving results.


I wrote this to help for now - but the canned report should work:

SELECT count(s.datetime) AS circs, b.title, b.author,
       i.ccode 
FROM statistics s
JOIN items i USING (itemnumber)
LEFT JOIN biblio b USING (biblionumber)
WHERE DATE(s.datetime) > DATE_SUB(CURRENT_DATE(),INTERVAL 4 MONTH) 
      AND DATE(s.datetime)<=CURRENT_DATE() AND 
      s.itemnumber IS NOT NULL AND i.itype=<<Item type|itemtypes>> AND
i.hombranch=<<Home branch|branches>>
GROUP BY b.biblionumber 
ORDER BY circs DESC 
LIMIT 20

Comment 1 Katrin Fischer 2023-01-25 22:56:03 UTC

I added a date range for the checkout dates and limit by item type: I get results.
Same date range, but with limit by library:

CGI::Compile::ROOT::kohadevbox_koha_reports_cat_issues_top_2epl::calculate(): DBI Exception: DBD::mysql::st execute failed: called with 1 bind variables when 0 are needed at /kohadevbox/koha/reports/cat_issues_top.pl line 59
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77

Comment 2 Baptiste Wojtkowski (bwoj) 2026-05-20 14:36:31 UTC

On main, there are a few bugs with filtering, idk if I should open another bug.

Steps to reproduce: 
1. On an fresh ktd db, run the script provided here to fill the database with checkins/checkouts:    
    
Visit http://your_koha:8081/cgi-bin/koha/reports/cat_issues_top.pl    
2. Run a baseline test with following params:    
Checkout date from 01/01/2020    
Checkout date to: tomorrow       
Library: Any library    
Fill nothing else    
    
Notice it is works    

3. Test with some limits (Library, Week, and any other), notice you encounter errors on Library and Week, and that any other is empty, without error.

Comment 3 Baptiste Wojtkowski (bwoj) 2026-05-20 14:38:38 UTC Comment hidden (obsolete)

This is a script that might help running the upcoming test plan:



  use C4::Circulation qw( AddIssue AddReturn );
  use Koha::Items;
  use Koha::Patrons;
  use t::lib::Mocks;
  use Koha::DateUtils qw(dt_from_string);
  
S>my $koha_user = Koha::Patrons->find(51);
  t::lib::Mocks::mock_userenv({patron => $koha_user});
  
  my $henry = Koha::Patrons->find(19);
  my $edna = Koha::Patrons->find(5);
  my $book1 = Koha::Items->find({ itemnumber => 1 });
  my $book2 = Koha::Items->find({ itemnumber => 2 });
  my $book3 = Koha::Items->find({ itemnumber => 12 });
  my $book4 = Koha::Items->find({ itemnumber => 19 });
  my $book5 = Koha::Items->find({ itemnumber => 21 });
  my $musics = Koha::Items->search({ itype => 'MU' });
  my $music1 = $musics->next;
  
  my $nb_days;
  
  for( my $i = 10; $i<100; $i++){
      $nb_days = $i;
      my $item;
      my $patron;
      my $branch;
      if ($i == 15){
          $item = $book3;
      } elsif ($i == 16){
          $item = $book4;
      } elsif ($i == 17){
          $item = $book5;
      } elsif ($i < 30){
          $item = $book1;
      }
      elsif ($i < 70){
          $item = $book2;
      }
      else {
          $item = $music1;
      };
      if ($i % 2 == 1){
          $patron = $edna;
      }
      else {
          $patron = $henry;
      }
    if ($i % 3 == 0){
          $branchcode = 'CPL';
      }
      elsif ($i % 3 == 1){
          $branchcode = 'MPL';
      }
      else {
          $branchcode = 'FFL';
      };
      $branch = t::lib::Mocks::mock_userenv({branchcode => $branchcode});
  
      my $due = dt_from_string->subtract(days => $nb_days)->set_hour(12)->set_minute(00)->set_second(00);
      AddIssue($patron, $item->barcode, $due);
      AddReturn($item->barcode, $branchcode);
      my $returned_date = dt_from_string->subtract(days => $nb_days)->set_hour(12)->set_minute(00)->set_second(00);
      my $lastIssue = Koha::Old::Checkouts->filter_by_last_update({
              younger_than => 0,
              timestamp_column_name => 'returndate',
          })->single;
      $lastIssue->set({ returndate => $returned_date })->store();
  }

Comment 4 Baptiste Wojtkowski (bwoj) 2026-05-20 14:59:57 UTC

This is a script that might help running the upcoming test plan:



  use C4::Circulation qw( AddIssue AddReturn );
  use Koha::Items;
  use Koha::Patrons;
  use t::lib::Mocks;
  use Koha::DateUtils qw(dt_from_string);
  
S>my $koha_user = Koha::Patrons->find(51);
  t::lib::Mocks::mock_userenv({patron => $koha_user});
  
  my $henry = Koha::Patrons->find(19);
  my $edna = Koha::Patrons->find(5);
  my $book1 = Koha::Items->find({ itemnumber => 1 });
  my $book2 = Koha::Items->find({ itemnumber => 2 });
  my $book3 = Koha::Items->find({ itemnumber => 12 });
  my $book4 = Koha::Items->find({ itemnumber => 19 });
  my $book5 = Koha::Items->find({ itemnumber => 21 });
  my $musics = Koha::Items->search({ itype => 'MU' });
  my $music1 = $musics->next;
  
  my $nb_days;
  
  for( my $i = 10; $i<100; $i++){
      $nb_days = $i;
      my $item;
      my $patron;
      my $branch;
      if ($i == 15){
          $item = $book3;
      } elsif ($i == 16){
          $item = $book4;
      } elsif ($i == 17){
          $item = $book5;
      } elsif ($i < 30){
          $item = $book1;
      }
      elsif ($i < 70){
          $item = $book2;
      }
      else {
          $item = $music1;
      };
      if ($i % 2 == 1){
          $patron = $edna;
      }
      else {
          $patron = $henry;
      }
    if ($i % 3 == 0){
          $branchcode = 'CPL';
      }
      elsif ($i % 3 == 1){
          $branchcode = 'MPL';
      }
      else {
          $branchcode = 'FFL';
      };
      $branch = t::lib::Mocks::mock_userenv({branchcode => $branchcode});
  
      my $due = dt_from_string->subtract(days => $nb_days)->set_hour(12)->set_minute(00)->set_second(00);
      AddIssue($patron, $item->barcode, $due);
      AddReturn($item->barcode, $branchcode);
      my $returned_date = dt_from_string->subtract(days => $nb_days)->set_hour(12)->set_minute(00)->set_second(00);
      my $lastIssue = Koha::Old::Checkouts->filter_by_last_update({
              younger_than => 0,
              timestamp_column_name => 'returndate',
          })->single;
      $lastIssue->set({ returndate => $returned_date })->store();
      $lastIssue->set({ issuedate => $returned_date })->store();
  }

Comment 5 Baptiste Wojtkowski (bwoj) 2026-05-20 15:01:58 UTC

Created attachment 199341 [details] [review]
Bug 8127: Remove useless commented warns

Patch from commit f821cb6

Comment 6 Baptiste Wojtkowski (bwoj) 2026-05-20 15:02:00 UTC

Created attachment 199342 [details] [review]
Bug 8127: Fix filters use in 'Most-circulated items report'

Filters are broken for many reasons. Previous patch contains some
(little) tidying.
There are 3 parts in this patch:
1 - The column name is incorrectly named in the dropdown for "branch",
  it is added in this patch
2 - There is a definition for 'Day' and 'Month', but not for 'Week', it
is also added in this patch
3 - Dbh variables are passed in the string definition AND in the call. I
removed them from the string definition since it violates the coding
guidelines.

Test plan:

1. On an fresh ktd db, run the script provided here (https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8127#c3)  to fill the database with checkins/checkouts:

Visit http://your_koha:8081/cgi-bin/koha/reports/cat_issues_top.pl
2. Run a baseline test with following params:
Checkout date from 01/01/2020
Checkout date to: tomorrow
Library: Any library
Fill nothing else

Notice it is works

3. Test with some limits (Library, Week, and any other), notice you encounter errors on Library and Week, and that any other is empty, without error.

4. Apply patch
5. Rerun 2-3 and notice you now have results. Check with each possible limit, limiting to 5. Test once with 10 as a limit.

Comment 7 Baptiste Wojtkowski (bwoj) 2026-05-20 15:03:12 UTC

Comment 4 contains a typo, remove the S> before executing

Comment 8 David Nind 2026-05-21 19:12:54 UTC

Created attachment 199396 [details] [review]
Bug 8127: Remove useless commented warns

Signed-off-by: David Nind <david@davidnind.com>

Comment 9 David Nind 2026-05-21 19:12:56 UTC

Created attachment 199397 [details] [review]
Bug 8127: Fix filters use in 'Most-circulated items report'

Filters are broken for many reasons. Previous patch contains some
(little) tidying.
There are 3 parts in this patch:
1 - The column name is incorrectly named in the dropdown for "branch",
  it is added in this patch
2 - There is a definition for 'Day' and 'Month', but not for 'Week', it
is also added in this patch
3 - Dbh variables are passed in the string definition AND in the call. I
removed them from the string definition since it violates the coding
guidelines.

Test plan:

1. On an fresh ktd db, run the script provided here (https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8127#c3)  to fill the database with checkins/checkouts:

Visit http://your_koha:8081/cgi-bin/koha/reports/cat_issues_top.pl
2. Run a baseline test with following params:
Checkout date from 01/01/2020
Checkout date to: tomorrow
Library: Any library
Fill nothing else

Notice it is works

3. Test with some limits (Library, Week, and any other), notice you encounter errors on Library and Week, and that any other is empty, without error.

4. Apply patch
5. Rerun 2-3 and notice you now have results. Check with each possible limit, limiting to 5. Test once with 10 as a limit.

Signed-off-by: David Nind <david@davidnind.com>

Comment 10 David Nind 2026-05-21 19:28:42 UTC

Testing notes (using KTD):

1. Note that both before and after the patch, that the checkout date from and to are cleared when you use the browser back button from the results page. Other values, such as library and limits are remembered.

2. For those that are not developers 8-), for step 1:
   - copy the script from comment 4 into a file and make the change in comment 7
     (for example: /kohadevbox/koha/script-add-checkouts.pl)
   - run it with: perl ./script-add-checkouts.pl

Comment 11 Lucas Gass (lukeg) 2026-07-15 21:28:12 UTC

Created attachment 201966 [details] [review]
Bug 8127: Remove useless commented warns

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>

Comment 12 Lucas Gass (lukeg) 2026-07-15 21:28:16 UTC

Created attachment 201967 [details] [review]
Bug 8127: Fix filters use in 'Most-circulated items report'

Filters are broken for many reasons. Previous patch contains some
(little) tidying.
There are 3 parts in this patch:
1 - The column name is incorrectly named in the dropdown for "branch",
  it is added in this patch
2 - There is a definition for 'Day' and 'Month', but not for 'Week', it
is also added in this patch
3 - Dbh variables are passed in the string definition AND in the call. I
removed them from the string definition since it violates the coding
guidelines.

Test plan:

1. On an fresh ktd db, run the script provided here (https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8127#c3)  to fill the database with checkins/checkouts:

Visit http://your_koha:8081/cgi-bin/koha/reports/cat_issues_top.pl
2. Run a baseline test with following params:
Checkout date from 01/01/2020
Checkout date to: tomorrow
Library: Any library
Fill nothing else

Notice it is works

3. Test with some limits (Library, Week, and any other), notice you encounter errors on Library and Week, and that any other is empty, without error.

4. Apply patch
5. Rerun 2-3 and notice you now have results. Check with each possible limit, limiting to 5. Test once with 10 as a limit.

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>

Comment 13 Pedro Amorim (ammopt) 2026-07-16 17:06:25 UTC

Thanks everyone! Pushed to main for 26.11!

Comment 14 Lucas Gass (lukeg) 2026-08-10 21:54:28 UTC

Nice work everyone!

Pushed to 26.05.x for the upcoming 26.05.03 release.

Comment 15 Wainui Witika-Park 2026-08-19 00:34:44 UTC

Applied to 25.05 for 25.05.14 (needed for 42736)

Comment 16 Fridolin Somers 2026-08-20 07:41:58 UTC

I've backported to 24.11.x but I dont change status because needs to be in 25.11.x