From 2302e7a9830c22164feb50bd917e419874c5fe1b Mon Sep 17 00:00:00 2001
From: Eivin Giske Skaaren <eivin@sysmystic.com>
Date: Wed, 28 Oct 2015 00:28:28 +0000
Subject: [PATCH] Bug 10154 Add Call number in the filter of most circulated
 items

This patch adds call number, collection code and shelving location to the filter.

To test:

It is of course a prerequisite to have items in the DB with the correct MARC fields
and that they have been checked out etc. to get a valid result when testing.

1. Apply patch to koha synced to master.
2. Go to /cgi-bin/koha/reports/cat_issues_top.pl
3. Select filters, the new ones are Call number, Collection code and Shelving location
4. Hit submit.

Expected result:
The filters chosen will be printed under "Filtered on".
A table with the result is shown.

For manual verification here is some SQL to run:

SELECT DISTINCT biblio.title, COUNT(biblio.biblionumber) AS RANK, biblio.biblionumber AS ID, itemcallnumber as CALLNUM, ccode as CCODE, location as LOC FROM `old_issues`
                  LEFT JOIN items USING(itemnumber)
                  LEFT JOIN biblio USING(biblionumber)
                  LEFT JOIN biblioitems USING(biblionumber)
                  LEFT JOIN borrowers USING(borrowernumber)
                  WHERE 1 AND biblioitems.itemtype like 'BK' AND itemcallnumber like '005.2/762' AND ccode like 'NFIC' AND location like 'CART' AND borrowers.categorycode like 'PT' group by biblio.biblionumber order by RANK DESC;

Put in your valid values in the WHERE clause for the values in the single quotes ''.

Sponsored-by: Halland county library
---
 .../prog/en/modules/reports/cat_issues_top.tt      |   17 +++++
 reports/cat_issues_top.pl                          |   72 ++++++++++++++++----
 2 files changed, 74 insertions(+), 15 deletions(-)

diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cat_issues_top.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cat_issues_top.tt
index c76fba7..1d007b4 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cat_issues_top.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cat_issues_top.tt
@@ -125,6 +125,23 @@
     </select>
       </li>
       <li> 
+        <label for="itemcallnumber">Call number: </label><input type="text" name="Filter" id="itemcallnumber" value="" />
+      </li>
+      <li> 
+        <label for="ccode">Collection code: </label><select name="Filter" id="ccode"><option value="" > Any collection code</option>
+    [% FOREACH ccodeloo IN ccodeloop %]
+        <option value="[% ccodeloo.value %]" >[% ccodeloo.description %] </option>  
+     [% END %] 
+    </select>
+      </li>
+      <li> 
+        <label for="shelvingloc">Shelving location: </label><select name="Filter" id="shelvingloc"><option value="" > Any shelving location</option>
+    [% FOREACH shelvinglocloo IN shelvinglocloop %]
+        <option value="[% shelvinglocloo.value %]" >[% shelvinglocloo.description %] </option>  
+     [% END %] 
+    </select>
+      </li>
+      <li> 
         <label for="patroncategory">Patron category: </label><select name="Filter" id="patroncategory"><option value="" > Any Category code</option>
     [% FOREACH borcatloo IN borcatloop %]
         <option value="[% borcatloo.value %]" >[% borcatloo.description %] </option>  
diff --git a/reports/cat_issues_top.pl b/reports/cat_issues_top.pl
index f88ff47..024bcfe 100755
--- a/reports/cat_issues_top.pl
+++ b/reports/cat_issues_top.pl
@@ -129,7 +129,31 @@ if ($do_it) {
                             );
             push @itemtypeloop, \%row;
     }
-    
+
+    #ccode
+    my $ccodes = GetAuthorisedValues('CCODE');
+    my @ccodeloop;
+    for my $thisccode (@$ccodes) {
+            my %row = (value => $thisccode->{authorised_value},
+                       description => $thisccode->{lib},
+                            );
+            push @ccodeloop, \%row;
+    }
+
+    @ccodeloop = sort {$a->{value} cmp $b->{value}} @ccodeloop;
+
+    #shelvingloc
+    my $shelvinglocs = GetAuthorisedValues('LOC');
+    my @shelvinglocloop;
+    for my $thisloc (@$shelvinglocs) {
+            my %row = (value => $thisloc->{authorised_value},
+                       description => $thisloc->{lib},
+                            );
+            push @shelvinglocloop, \%row;
+    }
+
+    @shelvinglocloop = sort {$a->{value} cmp $b->{value}} @shelvinglocloop;
+
     #borcat
     my ($codes,$labels) = GetborCatFromCatType(undef,undef);
     my @borcatloop;
@@ -147,6 +171,8 @@ if ($do_it) {
                     CGIsepChoice => $CGIsepChoice,
                     branchloop => GetBranchesLoop(C4::Context->userenv->{'branch'}),
                     itemtypeloop =>\@itemtypeloop,
+                    ccodeloop =>\@ccodeloop,
+                    shelvinglocloop =>\@shelvinglocloop,
                     borcatloop =>\@borcatloop,
                     );
 output_html_with_http_headers $input, $cookie, $template->output;
@@ -171,7 +197,7 @@ sub calculate {
 # Checking filters
 #
     my @loopfilter;
-    for (my $i=0;$i<=6;$i++) {
+    for (my $i=0;$i<=12;$i++) {
         my %cell;
         if ( @$filters[$i] ) {
             if (($i==1) and (@$filters[$i-1])) {
@@ -188,10 +214,13 @@ sub calculate {
             $cell{crit} .="Return To" if ($i==3);
             $cell{crit} .="Branch" if ($i==4);
             $cell{crit} .="Doc Type" if ($i==5);
-            $cell{crit} .="Bor Cat" if ($i==6);
-            $cell{crit} .="Day" if ($i==7);
-            $cell{crit} .="Month" if ($i==8);
-            $cell{crit} .="Year" if ($i==9);
+            $cell{crit} .="Call number" if ($i==6);
+            $cell{crit} .="Collection code" if ($i==7);
+            $cell{crit} .="Shelving location" if ($i==8);
+            $cell{crit} .="Bor Cat" if ($i==9);
+            $cell{crit} .="Day" if ($i==10);
+            $cell{crit} .="Month" if ($i==11);
+            $cell{crit} .="Year" if ($i==12);
             push @loopfilter, \%cell;
         }
     }
@@ -210,11 +239,15 @@ sub calculate {
         $colfilter[1] = @$filters[3] if ($column =~ /returndate/ )  ;
         $colfilter[0] = @$filters[4] if ($column =~ /branch/ )  ;
         $colfilter[0] = @$filters[5] if ($column =~ /itemtype/ )  ;
-        $colfilter[0] = @$filters[6] if ($column =~ /category/ )  ;
-    # 	$colfilter[0] = @$filters[11] if ($column =~ /sort2/ ) ;
-        $colfilter[0] = @$filters[7] if ($column =~ /timestamp/ ) ;
-        $colfilter[0] = @$filters[8] if ($column =~ /timestamp/ ) ;
+      # These limits does not currently exist, maybe later?
+      # $colfilter[0] = @$filters[6] if ($column =~ /ccode/ )  ;
+      # $colfilter[0] = @$filters[7] if ($column =~ /location/ )  ;
+        $colfilter[0] = @$filters[8] if ($column =~ /category/ )  ;
+      # This commented out row (sort2) was not removed when adding new filters for ccode, shelving location and call number    
+      # $colfilter[0] = @$filters[11] if ($column =~ /sort2/ ) ;
         $colfilter[0] = @$filters[9] if ($column =~ /timestamp/ ) ;
+        $colfilter[0] = @$filters[10] if ($column =~ /timestamp/ ) ;
+        $colfilter[0] = @$filters[11] if ($column =~ /timestamp/ ) ;
     #warn "filtre col ".$colfilter[0]." ".$colfilter[1];
                                                 
     # loop cols.
@@ -299,6 +332,9 @@ sub calculate {
     
 # Processing average loanperiods
     $strcalc .= "SELECT DISTINCT biblio.title, COUNT(biblio.biblionumber) AS RANK, biblio.biblionumber AS ID";
+    $strcalc .= ", itemcallnumber as CALLNUM";
+    $strcalc .= ", ccode as CCODE";
+    $strcalc .= ", location as LOC";
     $strcalc .= " , $colfield " if ($colfield);
     $strcalc .= " FROM `old_issues` 
                   LEFT JOIN items USING(itemnumber) 
@@ -327,13 +363,19 @@ sub calculate {
         $strcalc .= "'" . @$filters[5] ."'" ;
     }
     @$filters[6]=~ s/\*/%/g if (@$filters[6]);
-    $strcalc .= " AND borrowers.categorycode like '" . @$filters[6] ."'" if ( @$filters[6] );
+    $strcalc .= " AND itemcallnumber like '" . @$filters[6] ."'" if ( @$filters[6] );
     @$filters[7]=~ s/\*/%/g if (@$filters[7]);
-    $strcalc .= " AND dayname(old_issues.timestamp) like '" . @$filters[7]."'" if (@$filters[7]);
+    $strcalc .= " AND ccode like '" . @$filters[7] ."'" if ( @$filters[7] );
     @$filters[8]=~ s/\*/%/g if (@$filters[8]);
-    $strcalc .= " AND monthname(old_issues.timestamp) like '" . @$filters[8]."'" if (@$filters[8]);
+    $strcalc .= " AND location like '" . @$filters[8] ."'" if ( @$filters[8] );
     @$filters[9]=~ s/\*/%/g if (@$filters[9]);
-    $strcalc .= " AND year(old_issues.timestamp) like '" . @$filters[9] ."'" if ( @$filters[9] );
+    $strcalc .= " AND borrowers.categorycode like '" . @$filters[9] ."'" if ( @$filters[9] );
+    @$filters[10]=~ s/\*/%/g if (@$filters[10]);
+    $strcalc .= " AND dayname(old_issues.timestamp) like '" . @$filters[10]."'" if (@$filters[10]);
+    @$filters[11]=~ s/\*/%/g if (@$filters[11]);
+    $strcalc .= " AND monthname(old_issues.timestamp) like '" . @$filters[11]."'" if (@$filters[11]);
+    @$filters[12]=~ s/\*/%/g if (@$filters[12]);
+    $strcalc .= " AND year(old_issues.timestamp) like '" . @$filters[12] ."'" if ( @$filters[12] );
     
     $strcalc .= " group by biblio.biblionumber";
     $strcalc .= ", $colfield" if ($column);
@@ -345,7 +387,7 @@ sub calculate {
     my $previous_col;
     my %indice;
     while (my  @data = $dbcalc->fetchrow) {
-        my ($row, $rank, $id, $col )=@data;
+        my ($row, $rank, $id, $callnum, $ccode, $loc, $col )=@data;
         $col = "zzEMPTY" if (!defined($col));
         $indice{$col}=1 if (not($indice{$col}));
         $table[$indice{$col}]->{$col}->{'name'}=$row;
-- 
1.7.10.4