From 9b8023044a979cc24c92493431e9e9d33c52c6a6 Mon Sep 17 00:00:00 2001
From: Katrin Fischer <Katrin.Fischer.83@web.de>
Date: Tue, 9 Jun 2015 01:04:07 +0200
Subject: [PATCH] Bug 13452: 'Average loan time' report to obey
 item-level_itypes preference

Fixes the 'Average loan time' report so that the system
preference setting item-level_itypes is taken into account.

Before the patch, the report was always using the biblio
level itemtype. With the patch, it will depend on the pref
setting.

To test:
- Run various reports, compare results with both
  possible pref settings
- Try limiting your search in various ways, especially
  on a specific itemtype
- Make sure you switch checkboxes in testing,
  using itemtype as the row or as the column

Notes:
- Filtering on a specific itemtype on master was not working
  if the itemtype was chosen as column. The patch should fix that.
- Fixes headings and breadcrumbs so that the name of the report
  is the same as on the reports start page

Signed-off-by: Indranil Das Gupta (L2C2 Technologies) <indradg@gmail.com>

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
(cherry picked from commit d0bf036c1828bbbd37a022f8449e217e8e269475)
Signed-off-by: Fridolin Somers <fridolin.somers@biblibre.com>
---
 .../en/modules/reports/issues_avg_stats.tt    |  4 +--
 reports/issues_avg_stats.pl                   | 33 +++++++++----------
 2 files changed, 17 insertions(+), 20 deletions(-)

diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/issues_avg_stats.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/issues_avg_stats.tt
index 728850812b..ae20291f94 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/issues_avg_stats.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/issues_avg_stats.tt
@@ -1,6 +1,6 @@
 [% USE Branches %]
 [% INCLUDE 'doc-head-open.inc' %]
-<title>Koha &rsaquo; Reports &rsaquo; Average checkout period</title>
+<title>Koha &rsaquo; Reports &rsaquo; Average loan time</title>
 [% INCLUDE 'doc-head-close.inc' %]
 [% INCLUDE 'calendar.inc' %]
 <script type="text/javascript">
@@ -41,7 +41,7 @@
 [% INCLUDE 'header.inc' %]
 [% INCLUDE 'cat-search.inc' %]
 
-<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> &rsaquo; <a href="/cgi-bin/koha/reports/reports-home.pl">Reports</a> [% IF ( do_it ) %]&rsaquo; <a href="/cgi-bin/koha/reports/issues_avg_stats.pl">Average checkout period</a> &rsaquo; Results [% ELSE %]&rsaquo; Average checkout period[% END %]</div>
+<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> &rsaquo; <a href="/cgi-bin/koha/reports/reports-home.pl">Reports</a> [% IF ( do_it ) %]&rsaquo; <a href="/cgi-bin/koha/reports/issues_avg_stats.pl">Average loan time</a> &rsaquo; Results [% ELSE %]&rsaquo; Average loan time[% END %]</div>
 
 <div id="doc3" class="yui-t2">
    
diff --git a/reports/issues_avg_stats.pl b/reports/issues_avg_stats.pl
index c353680736..c013f059c8 100755
--- a/reports/issues_avg_stats.pl
+++ b/reports/issues_avg_stats.pl
@@ -61,6 +61,8 @@ my $rodsp = $input->param("ReturnDisplay");
 my $calc = $input->param("Cellvalue");
 my $output = $input->param("output");
 my $basename = $input->param("basename");
+my $itype = C4::Context->preference('item-level_itypes') ? "items.itype" : "biblioitems.itemtype";
+
 #warn "calcul : ".$calc;
 my ($template, $borrowernumber, $cookie)
     = get_template_and_user({template_name => $fullreportname,
@@ -223,8 +225,7 @@ sub calculate {
 #	warn "filtres ".@filters[2];
 #	warn "filtres ".@filters[3];
     $line = "old_issues.".$line if ($line=~/branchcode/) or ($line=~/timestamp/);
-    $line = "biblioitems.".$line if $line=~/itemtype/;
-    
+    if ( $line=~/itemtype/ ) { $line = $itype; }
     $linefilter[0] = @$filters[0] if ($line =~ /timestamp/ )  ;
     $linefilter[1] = @$filters[1] if ($line =~ /timestamp/ )  ;
     $linefilter[2] = @$filters[2] if ($line =~ /timestamp/ )  ;
@@ -236,13 +237,11 @@ sub calculate {
     $linefilter[0] = @$filters[8] if ($line =~ /category/ )  ;
     $linefilter[0] = @$filters[9] if ($line =~ /itemtype/ )  ;
     $linefilter[0] = @$filters[10] if ($line =~ /branch/ )  ;
-# 	$linefilter[0] = @$filters[11] if ($line =~ /sort2/ ) ;
     $linefilter[0] = @$filters[11] if ($line =~ /sort1/ ) ;
     $linefilter[0] = @$filters[12] if ($line =~ /sort2/ ) ;
-#warn "filtre lignes".$linefilter[0]." ".$linefilter[1];
-# 
+
     $column = "old_issues.".$column if (($column=~/branchcode/) or ($column=~/timestamp/));
-    $column = "biblioitems.".$column if $column=~/itemtype/;
+    if ( $column=~/itemtype/ ) { $column = $itype; }
     my @colfilter ;
     $colfilter[0] = @$filters[0] if ($column =~ /timestamp/ )  ;
     $colfilter[1] = @$filters[1] if ($column =~ /timestamp/ )  ;
@@ -253,12 +252,10 @@ sub calculate {
     $colfilter[2] = @$filters[6] if ($column =~ /returndate/ )  ;
     $colfilter[3] = @$filters[7] if ($column =~ /returndate/ )  ;
     $colfilter[0] = @$filters[8] if ($column =~ /category/ )  ;
-    $colfilter[0] = @$filters[9] if ($column =~ /itemtype/ )  ;
+    $colfilter[0] = @$filters[9] if ($column =~ $itype )  ;
     $colfilter[0] = @$filters[10] if ($column =~ /branch/ )  ;
-# 	$colfilter[0] = @$filters[11] if ($column =~ /sort2/ ) ;
     $colfilter[0] = @$filters[11] if ($column =~ /sort1/ ) ;
     $colfilter[0] = @$filters[12] if ($column =~ /sort2/ ) ;
-#warn "filtre col ".$colfilter[0]." ".$colfilter[1];
                                             
 # 1st, loop rows.                             
     my $linefield;
@@ -291,7 +288,7 @@ sub calculate {
                 LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) 
                 LEFT JOIN issuingrules ON 
                     (issuingrules.branchcode=old_issues.branchcode
-                    AND  issuingrules.itemtype=biblioitems.itemtype 
+                    AND  issuingrules.itemtype=$itype
                     AND  issuingrules.categorycode=borrowers.categorycode) 
                 WHERE 1";
     
@@ -319,7 +316,6 @@ sub calculate {
     my $sth = $dbh->prepare( $strsth );
     $sth->execute;
 
-    
     while ( my ($celvalue) = $sth->fetchrow) {
         my %cell;
         if ($celvalue) {
@@ -353,7 +349,7 @@ sub calculate {
         $colfield .= $column;
         $colorder .= $column;
     }  
-    
+
     my $strsth2;
     $strsth2 .= "SELECT distinctrow $colfield 
                   FROM `old_issues`
@@ -362,7 +358,7 @@ sub calculate {
                   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) 
                   LEFT JOIN issuingrules ON 
                     (issuingrules.branchcode=old_issues.branchcode 
-                    AND  issuingrules.itemtype=biblioitems.itemtype 
+                    AND  issuingrules.itemtype=$itype
                     AND  issuingrules.categorycode=borrowers.categorycode) 
                   WHERE 1";
     
@@ -388,14 +384,14 @@ sub calculate {
     $strsth2 .=" ORDER BY $colorder";
     
     my $sth2 = $dbh->prepare( $strsth2 );
+
     if (( @colfilter ) and ($colfilter[1])){
         $sth2->execute("'".$colfilter[0]."'","'".$colfilter[1]."'");
     } elsif ($colfilter[0]) {
-        $sth2->execute($colfilter[0]);
+        $sth2->execute;
     } else {
         $sth2->execute;
     }
-    
 
     while (my ($celvalue) = $sth2->fetchrow) {
         my %cell;
@@ -430,7 +426,7 @@ sub calculate {
     
 # Processing average loanperiods
     $strcalc .= "SELECT $linefield, $colfield, ";
-    $strcalc .= " issuedate, returndate, old_issues.timestamp, COUNT(*), date_due, old_issues.renewals, issuelength FROM `old_issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=branchcode AND  issuingrules.itemtype=biblioitems.itemtype AND  issuingrules.categorycode=categorycode) WHERE old_issues.itemnumber=items.itemnumber AND old_issues.borrowernumber=borrowers.borrowernumber";
+    $strcalc .= " issuedate, returndate, old_issues.timestamp, COUNT(*), date_due, old_issues.renewals, issuelength FROM `old_issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=branchcode AND  issuingrules.itemtype=$itype AND  issuingrules.categorycode=categorycode) WHERE old_issues.itemnumber=items.itemnumber AND old_issues.borrowernumber=borrowers.borrowernumber";
 
     @$filters[0]=~ s/\*/%/g if (@$filters[0]);
     $strcalc .= " AND old_issues.timestamp > '" . @$filters[0] ."'" if ( @$filters[0] );
@@ -443,7 +439,7 @@ sub calculate {
     @$filters[8]=~ s/\*/%/g if (@$filters[8]);
     $strcalc .= " AND borrowers.categorycode like '" . @$filters[8] ."'" if ( @$filters[8] );
     @$filters[9]=~ s/\*/%/g if (@$filters[9]);
-    $strcalc .= " AND biblioitems.itemtype like '" . @$filters[9] ."'" if ( @$filters[9] );
+    $strcalc .= " AND $itype like '" . @$filters[9] ."'" if ( @$filters[9] );
     @$filters[10]=~ s/\*/%/g if (@$filters[10]);
     $strcalc .= " AND old_issues.branchcode like '" . @$filters[10] ."'" if ( @$filters[10] );
     @$filters[11]=~ s/\*/%/g if (@$filters[11]);
@@ -467,7 +463,7 @@ sub calculate {
     my $err;
     my $emptycol;
     my $weightrow;
-    
+
     while (my  @data = $dbcalc->fetchrow) {
         my ($row, $col, $issuedate, $returndate, $weight)=@data;
 #		warn "filling table $row / $col / $issuedate / $returndate /$weight";
@@ -560,6 +556,7 @@ sub calculate {
     $globalline{column} = $column;
     push @mainloop,\%globalline;
     return \@mainloop;
+
 }
 
 1;
-- 
2.39.5