From 6a120f28ba2a2208afece66c4b30ef4ca772881f Mon Sep 17 00:00:00 2001
From: Garry Collum <gcollum@gmail.com>
Date: Wed, 1 Feb 2012 10:15:46 -0500
Subject: [PATCH] Bug 6984 - Holds statistics doesn't work.

This patch fixes several errors in reserves_stats.pl and reserves_stats.tt.

Testing -
To test this patch, data must be in either the reserves table or old_reserves or both.  The following SQL will give you the raw data that is used by the report.

SELECT priority, found, reservedate, notificationdate, reminderdate,
waitingdate, cancellationdate, borrowers.categorycode, items.itype,
reserves.branchcode, holdingbranch, items.homebranch, items.ccode,
items.location, items.itemcallnumber, borrowers.sort1, borrowers.sort2
FROM reserves
LEFT JOIN borrowers on (borrowers.borrowernumber = reserves.borrowernumber)
LEFT JOIN items on (items.itemnumber = reserves.itemnumber)
UNION SELECT priority, found, reservedate, notificationdate, reminderdate,
waitingdate, cancellationdate, borrowers.categorycode, items.itype,
old_reserves.branchcode, holdingbranch, items.homebranch, items.ccode,
items.location, items.itemcallnumber, borrowers.sort1, borrowers.sort2
FROM old_reserves
LEFT JOIN borrowers on (borrowers.borrowernumber = old_reserves.borrowernumber)
LEFT JOIN items on (items.itemnumber = old_reserves.itemnumber)

To test the notificationdate and reminderdate, I added data to the old_reserves table, since I have never run notices on my test machine.

Ex:
UPDATE old_reserves
SET notificationdate = "2012-01-29",
reminderdate = "2012-01-29"
WHERE timestamp = "2012-01-29 20:09:34";

Signed-off-by: Liz Rea <wizzyrea@gmail.com>
Confirm original bug -- Reports work as expected now!
prove t xt t/db_dependent no different from master.
(cherry picked from commit 67825637beb8ce3fabb7f34548f6962dec2993e5)

Signed-off-by: Chris Nighswonger <chris.nighswonger@gmail.com>
(cherry picked from commit 7ac0d0b9240c99c931234e440a06158aa1647d1b)

Signed-off-by: Chris Nighswonger <chris.nighswonger@gmail.com>
---
 .../prog/en/modules/reports/reserves_stats.tt | 28 +++---
 reports/reserves_stats.pl                     | 89 ++++++++++++-------
 2 files changed, 69 insertions(+), 48 deletions(-)

diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reserves_stats.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reserves_stats.tt
index 6f5865b9ec..f5d03f5832 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reserves_stats.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reserves_stats.tt
@@ -89,18 +89,18 @@
                 <td><input type="radio" name="Line" value="reservestatus" /></td>
                 <td><input type="radio" name="Column" value="reservestatus" checked="checked" /></td>
                 <td>
-				<input type="checkbox" name="filter_reservestatus_or_1" value="1"> Asked</input>
-				<input type="checkbox" name="filter_reservestatus_or_2" value="2"> Processing</input>
-				<input type="checkbox" name="filter_reservestatus_or_3" value="3"> Waiting</input>
-				<input type="checkbox" name="filter_reservestatus_or_4" value="4"> Satisfied</input>
-				<input type="checkbox" name="filter_reservestatus_or_5" value="5"> Cancelled</input>
+				<input type="checkbox" name="filter_reservestatus_or_1" value="1" /> Asked
+				<input type="checkbox" name="filter_reservestatus_or_2" value="2" /> Processing
+				<input type="checkbox" name="filter_reservestatus_or_3" value="3" /> Waiting
+				<input type="checkbox" name="filter_reservestatus_or_4" value="4" /> Satisfied
+				<input type="checkbox" name="filter_reservestatus_or_5" value="5" /> Cancelled
 				</td>
              </tr>
              <tr class="highlight">
                 <td>Hold Date</td>
                 <td><input type="radio" name="Line" value="reservedate" /></td>
                 <td><input type="radio" name="Column" value="reservedate" /></td>
-                <td><label for="reservedate_begin">From</label> <input type="text"  size="10" id="filter_reservedate_begin" name="filter_reservedate_begin" />
+                <td><label for="filter_reservedate_begin">From</label> <input type="text"  size="10" id="filter_reservedate_begin" name="filter_reservedate_begin" />
                     <img src="[% themelang %]/lib/calendar/cal.gif" alt="Show Calendar" border="0" id="openreservedateFrom" style="cursor: pointer;" />
                    <script type="text/javascript">
 				   //<![CDATA[ 
@@ -424,7 +424,7 @@
             <td><input type="radio" name="Column" value="items.itype" /></td>
            <td><select name="filter_items.itype" id="itype">
                <option value=""> </option>
-               [% FOREACH itypeloo IN itypeloop %]
+               [% FOREACH itypeloo IN itemtypeloop %]
                  [% IF ( itypeloo.selected ) %]<option value="[% itypeloo.code %]" selected="selected">[% itypeloo.description %]</option>[% ELSE %]<option value="[% itypeloo.code %]">[% itypeloo.description %]</option>[% END %]
                [% END %]
                </select>
@@ -444,9 +444,9 @@
         </tr>
         <tr class="highlight">
             <td>Holding Library</td>
-            <td><input type="radio" name="Line" value="holdingbranch" /></td>
-            <td><input type="radio" name="Column" value="holdingbranch" /></td>
-            <td><select name="filter_holdingbranch" id="holdingbranch">
+            <td><input type="radio" name="Line" value="items.holdingbranch" /></td>
+            <td><input type="radio" name="Column" value="items.holdingbranch" /></td>
+            <td><select name="filter_items.holdingbranch" id="holdingbranch">
                <option value=""> </option>
                [% FOREACH branchloo IN branchloop %]
 [% IF ( branchloo.selected ) %]<option value="[% branchloo.value %]" selected="selected">[% branchloo.branchname %]</option>[% ELSE %]<option value="[% branchloo.value %]">[% branchloo.branchname %]</option>[% END %]
@@ -456,9 +456,9 @@
         </tr>
         <tr>
             <td>Home Library</td>
-            <td><input type="radio" name="Line" value="homebranch" /></td>
-            <td><input type="radio" name="Column" value="homebranch" /></td>
-            <td><select name="filter_homebranch" id="homebranch">
+            <td><input type="radio" name="Line" value="items.homebranch" /></td>
+            <td><input type="radio" name="Column" value="items.homebranch" /></td>
+            <td><select name="filter_items.homebranch" id="homebranch">
                <option value=""> </option>
                [% FOREACH branchloo IN branchloop %]
 [% IF ( branchloo.selected ) %]<option value="[% branchloo.value %]" selected="selected">[% branchloo.branchname %]</option>[% ELSE %]<option value="[% branchloo.value %]">[% branchloo.branchname %]</option>[% END %]
@@ -494,7 +494,7 @@
             <td>Item Call Number</td>
             <td></td>
             <td></td>
-            <td>From <input type="text" name="filter_itemcallnumber" size="10" /> (inclusive) to <input type="text" name="fiter_itemcallnumber" size="10" /> (exclusive) </td>
+            <td>From <input type="text" name="filter_items.itemcallnumber_begin" size="10" /> (inclusive) to <input type="text" name="filter_items.itemcallnumber_endex" size="10" /> (exclusive) </td>
         </tr>
         [% IF ( hassort1 ) %]
         <tr><td>Patron sort1</td>
diff --git a/reports/reserves_stats.pl b/reports/reserves_stats.pl
index 4254d5a207..a2b3737174 100755
--- a/reports/reserves_stats.pl
+++ b/reports/reserves_stats.pl
@@ -19,6 +19,7 @@
 
 
 use strict;
+use warnings;
 
 use CGI;
 
@@ -74,7 +75,7 @@ my ($template, $borrowernumber, $cookie) = get_template_and_user({
 	flagsrequired => {reports => '*'},
 	debug => 0,
 });
-our $sep     = $input->param("sep");
+our $sep     = $input->param("sep") || '';
 $sep = "\t" if ($sep eq 'tabulation');
 $template->param(do_it => $do_it,
 	DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
@@ -169,6 +170,7 @@ my $CGIsepChoice=GetDelimiterChoices;
 $template->param(
 	categoryloop => $categoryloop,
 	itemtypeloop => \@itemtypeloop,
+	locationloop => \@locations,
 	   ccodeloop => \@ccodes,
 	  branchloop => GetBranchesLoop(C4::Context->userenv->{'branch'}),
 	hassort1=> $hassort1,
@@ -194,18 +196,25 @@ sub calculate {
 # Filters
 # Checking filters
 #
-	my @loopfilter;
-	foreach my $filter (keys %$filters_hashref){
-		$$filters_hashref{$filter} =~s/\*/%/;
-		$$filters_hashref{$filter} = format_date_in_iso($$filters_hashref{$_}) if ($_=~/date/);
-	}
-	#display
-	@loopfilter= map{{ crit=>$_ ,filter=>($_=~/date/?
-											format_date($$filters_hashref{$_})
-											:$$filters_hashref{$_}
-										  )
-					  }
-					} sort keys %$filters_hashref;
+    my @loopfilter;
+    foreach my $filter ( keys %$filters_hashref ) {
+        $filters_hashref->{$filter} =~ s/\*/%/;
+        $filters_hashref->{$filter} =
+          format_date_in_iso( $filters_hashref->{$filter} )
+          if ( $filter =~ /date/ );
+    }
+
+    #display
+    @loopfilter = map {
+        {
+            crit   => $_,
+            filter => (
+                $_ =~ /date/
+                ? format_date( $filters_hashref->{$_} )
+                : $filters_hashref->{$_}
+            )
+        }
+    } sort keys %$filters_hashref;
 
 
 
@@ -228,7 +237,7 @@ sub calculate {
         if ($linefield =~ /^biblio\./ or $colfield =~ /^biblio\./ or any {$_=~/biblio/}keys %$filters_hashref);
 	$strcalc .= "LEFT JOIN items ON reserves.itemnumber=items.itemnumber "
         if ($linefield =~ /^items\./ or $colfield =~ /^items\./ or any {$_=~/items/}keys %$filters_hashref);
-        
+
 	my @sqlparams;
 	my @sqlorparams;
 	my @sqlor;
@@ -238,7 +247,6 @@ sub calculate {
 		my $string;
 		my $stringfield=$filter;
 		$stringfield=~s/\_[a-z_]+$//;
-		warn $stringfield;
 		if ($filter=~/ /){
 			$string=$stringfield;
 		}
@@ -246,6 +254,9 @@ sub calculate {
 			 push @sqlor, qq{( }.changeifreservestatus($filter)." = ? ) ";
 			 push @sqlorparams, $$filters_hashref{$filter};
 		}
+		elsif ($filter=~/_endex$/){
+			$string = " $stringfield < ? ";
+		}
 		elsif ($filter=~/_end$/){
 			$string = " $stringfield <= ? ";
 		}
@@ -274,7 +285,6 @@ sub calculate {
 	$dbcalc->execute(@sqlparams,@sqlparams);
 	my ($emptycol,$emptyrow); 
 	my $data = $dbcalc->fetchall_hashref([qw(line col)]);
-	my @loopline;
 	my %cols_hash;
 	foreach my $row (keys %$data){
 		push @loopline, $row;
@@ -304,14 +314,13 @@ sub calculate {
 	for my $col ( sort keys %cols_hash ) {
 		my $total = 0;
 		foreach my $row (@loopline) {
-			$total += $$data{$row}{$col}{calculation};
+			$total += $data->{$row}{$col}{calculation} if $data->{$row}{$col}{calculation};
 			$debug and warn "value added ".$$data{$row}{$col}{calculation}. "for line ".$row;
 		}
 		push @loopfooter, {'totalcol' => $total};
 		push @loopcol, {'coltitle' => $col,
 						coltitle_display=>display_value($colfield,$col)};
 	}
-
 	# the header of the table
 	$globalline{loopfilter}=\@loopfilter;
 	# the core of the table
@@ -331,22 +340,34 @@ sub null_to_zzempty ($) {
 	($string eq "NULL") and return 'zzEMPTY';
 	return $string;		# else return the valid value
 }
-sub display_value{
-	my ($crit,$value)=@_;
-	my $display_value =
-		($crit =~ /ccode/   ) ? $ccodes->{$value}    :
-		($crit =~ /location/) ? $locations->{$value} :
-		($crit =~ /itemtype/) ? $itemtypes->{$value}->{description} :
-		($crit =~ /branch/) ? GetBranchName($value):
-		($crit =~ /reservestatus/) ? reservestatushuman($value):
-		$value; # default fallback
-	if ($crit =~ /(sort1|sort2)/) {
-		$display_value=GetAuthorisedValues("B$_",$value);
-	} elsif ($crit =~ /category/) {
-		my $element=any{$value eq $_->{categorycode}} @$categoryloop;
-		$display_value=$$element{description};
-	}
-	return $display_value;
+sub display_value {
+    my ( $crit, $value ) = @_;
+    my $display_value =
+        ( $crit =~ /ccode/ )         ? $ccodes->{$value}
+      : ( $crit =~ /location/ )      ? $locations->{$value}
+      : ( $crit =~ /itemtype/ )      ? $itemtypes->{$value}->{description}
+      : ( $crit =~ /branch/ )        ? GetBranchName($value)
+      : ( $crit =~ /reservestatus/ ) ? reservestatushuman($value)
+      :                                $value;    # default fallback
+    if ($crit =~ /sort1/) {
+        foreach (@$Bsort1) {
+            ($value eq $_->{authorised_value}) or next;
+            $display_value = $_->{lib} and last;
+        }
+    }
+    elsif ($crit =~ /sort2/) {
+        foreach (@$Bsort2) {
+            ($value eq $_->{authorised_value}) or next;
+            $display_value = $_->{lib} and last;
+        }
+    }
+    elsif ( $crit =~ /category/ ) {
+        foreach (@$categoryloop) {
+            ( $value eq $_->{categorycode} ) or next;
+            $display_value = $_->{description} and last;
+        }
+    }
+    return $display_value;
 }
 sub reservestatushuman{
 	my ($val)=@_;
-- 
2.39.5