From 4a64a0948744a07916eb2ac5c4fcfd0c66cfb88e Mon Sep 17 00:00:00 2001
From: Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi>
Date: Wed, 2 Jul 2014 19:54:33 +0300
Subject: [PATCH] Bug 7021 -  patron category in the statistics table

This patch populates the koha.statistics.usercode with borrowers.categorycode where it is easily available.
Currently for statistics.type 'issue' OR 'localuse' OR 'renew'.

Supplied a script to UPDATE the old statistics records.

Have fun!
---
 C4/Circulation.pm                             |  6 +-
 C4/Stats.pm                                   | 10 ++--
 test/add_statistics_borrowers_categorycode.pl | 82 +++++++++++++++++++++++++++
 3 files changed, 91 insertions(+), 7 deletions(-)
 create mode 100755 test/add_statistics_borrowers_categorycode.pl

diff --git a/C4/Circulation.pm b/C4/Circulation.pm
index f8b204a..524c055 100644
--- a/C4/Circulation.pm
+++ b/C4/Circulation.pm
@@ -739,7 +739,7 @@ sub CanBookBeIssued {
     #
     if ( $borrower->{'category_type'} eq 'X' && (  $item->{barcode}  )) { 
     	# stats only borrower -- add entry to statistics table, and return issuingimpossible{STATS} = 1  .
-        &UpdateStats(C4::Context->userenv->{'branch'},'localuse','','',$item->{'itemnumber'},$item->{'itemtype'},$borrower->{'borrowernumber'}, undef, $item->{'ccode'});
+        &UpdateStats(C4::Context->userenv->{'branch'},'localuse','','',$item->{'itemnumber'},$item->{'itemtype'},$borrower->{'borrowernumber'}, undef, $item->{'ccode'}, $borrower->{'categorycode'});
         ModDateLastSeen( $item->{'itemnumber'} );
         return( { STATS => 1 }, {});
     }
@@ -1305,7 +1305,7 @@ sub AddIssue {
             C4::Context->userenv->{'branch'},
             'issue', $charge,
             ($sipmode ? "SIP-$sipmode" : ''), $item->{'itemnumber'},
-            $item->{'itype'}, $borrower->{'borrowernumber'}, undef, $item->{'ccode'}
+            $item->{'itype'}, $borrower->{'borrowernumber'}, undef, $item->{'ccode'}, $borrower->{'categorycode'}
         );
 
         # Send a checkout slip.
@@ -2630,7 +2630,7 @@ sub AddRenewal {
     }
 
     # Log the renewal
-    UpdateStats( $branch, 'renew', $charge, '', $itemnumber, $item->{itype}, $borrowernumber, undef, $item->{'ccode'});
+    UpdateStats( $branch, 'renew', $charge, '', $itemnumber, $item->{itype}, $borrowernumber, undef, $item->{'ccode'}, $borrower->{'categorycode'});
 	return $datedue;
 }
 
diff --git a/C4/Stats.pm b/C4/Stats.pm
index e1cbd42..467e753 100644
--- a/C4/Stats.pm
+++ b/C4/Stats.pm
@@ -58,11 +58,13 @@ the Koha database, which acts as an activity log.
 =item UpdateStats
 
   &UpdateStats($branch, $type, $value, $other, $itemnumber,
-               $itemtype, $borrowernumber);
+               $itemtype, $borrowernumber, $usercode);
 
 Adds a line to the statistics table of the Koha database. In effect,
 it logs an event.
 
+C<$usercode> is borrowers.categorycode
+
 C<$branch>, C<$type>, C<$value>, C<$other>, C<$itemnumber>,
 C<$itemtype>, and C<$borrowernumber> correspond to the fields of the
 statistics table in the Koha database.
@@ -76,18 +78,18 @@ sub UpdateStats {
     my (
         $branch,         $type,
         $amount,   $other,          $itemnum,
-        $itemtype, $borrowernumber, $accountno, $ccode
+        $itemtype, $borrowernumber, $accountno, $ccode, $usercode
       )
       = @_;
     my $dbh = C4::Context->dbh;
     my $sth = $dbh->prepare(
         "INSERT INTO statistics
-        (datetime, branch, type, value,
+        (datetime, branch, type, value, usercode,
          other, itemnumber, itemtype, borrowernumber, proccode, ccode)
          VALUES (now(),?,?,?,?,?,?,?,?,?)"
     );
     $sth->execute(
-        $branch,    $type,    $amount,
+        $branch,    $type,    $amount, $usercode,
         $other,     $itemnum, $itemtype, $borrowernumber,
 		$accountno, $ccode
     );
diff --git a/test/add_statistics_borrowers_categorycode.pl b/test/add_statistics_borrowers_categorycode.pl
new file mode 100755
index 0000000..254dc94
--- /dev/null
+++ b/test/add_statistics_borrowers_categorycode.pl
@@ -0,0 +1,82 @@
+#! /usr/bin/perl
+
+## EXTRACTED USING THIS:
+# grep -Pnir "'notforloan' => '6'" 01_items0* | grep -Po "'id' => '-?(\d+)'" | grep -Po "\d+" > itemnumbers_notforloan_6.txt
+
+use Modern::Perl;
+
+use C4::Context;
+use utf8;
+
+use C4::Members;
+
+use open ':encoding(utf8)';
+binmode STDOUT, ':utf8';
+
+
+print "\nTHE FOLLOWING STATISTIC ENTRIES HAVE BEEN UPDATED\n------------------------------------------------\n";
+
+
+##Caches all the loaded Borrowers
+my $borrowers = {};
+my $deletedBorrowers = {};
+
+my $dbh = C4::Context->dbh;
+
+my $sthDelBor   = $dbh->prepare("SELECT * FROM deletedborrowers WHERE borrowernumber = ? ");
+my $sthUpdateStat   = $dbh->prepare("UPDATE statistics SET usercode = ? WHERE datetime = ? AND branch = ? AND type = ? AND itemnumber = ? ");
+
+my $query2 = "SELECT * FROM statistics WHERE type = 'issue' OR type = 'renew' OR type = 'localuse'";
+my $sth2   = $dbh->prepare($query2);
+$sth2->execute();
+my $stats =  $sth2->fetchall_arrayref({});
+
+foreach my $stat (@$stats) {
+  my $borrower = getCachedBorrower( $stat->{borrowernumber} );
+  
+  $borrower = getCachedDeletedBorrower( $stat->{borrowernumber} ) unless $borrower;
+  
+  if ($borrower) {
+    $stat->{usercode} = $borrower->{categorycode};
+    $sthUpdateStat->execute( $stat->{usercode},
+                             $stat->{datetime},
+                             $stat->{branch},
+                             $stat->{type},
+                             $stat->{itemnumber},
+                           );
+    print(  $stat->{usercode} . " " .
+            $stat->{datetime} . " " .
+            $stat->{branch} . " " .
+            $stat->{type} . " " .
+            $stat->{itemnumber} . " "
+          );
+    print "\n";
+  }
+}
+
+
+
+##Members are repeatedly loaded in various parts of this code. Better to cache them.
+sub getCachedBorrower {
+    my $borrowernumber = shift; #The hash to store all branches by branchcode
+
+    if (egit xists $borrowers->{$borrowernumber}) {
+        return $borrowers->{$borrowernumber};
+    }
+    my $borrower = C4::Members::GetMember( borrowernumber => $borrowernumber );
+    $borrowers->{$borrowernumber} = $borrower;
+    return $borrower;
+}
+##Deleted members are repeatedly loaded in various parts of this code. Better to cache them.
+sub getCachedDeletedBorrower {
+    my $borrowernumber = shift; #The hash to store all branches by branchcode
+
+    if (exists $deletedBorrowers->{$borrowernumber}) {
+        return $deletedBorrowers->{$borrowernumber};
+    }
+    $sthDelBor->execute( $borrowernumber );
+    
+    my $borrower = $sthDelBor->fetchrow_hashref();
+    $borrowers->{$borrowernumber} = $borrower;
+    return $borrower;
+}
-- 
1.8.1.2