Attachment 129120 Details for Bug 7021 – Bug 7021: patron category in the statistics table

[patch] Bug 7021: patron category in the statistics table

Bug-7021-patron-category-in-the-statistics-table.patch (text/plain), 8.27 KB, created by Chris Cormack on 2022-01-06 23:13:01 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Chris Cormack

Created: 2022-01-06 23:13:01 UTC

Size: 8.27 KB

patch

obsolete

>From 6578d402249600fe001496ec2b4f559c10a2d6e3 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!
>
>To test:
>1. Add loan for patron.
>2. Check statistics table
>=> 'usercode' column for this issue should now contain patrons categorycode
>
>To test add_statistics_borrowers_categorycode.pl:
>1. Run add_statistics_borrowers_categorycode.pl
>2. Check statistics table
>=> all statistics which are type 'issue' OR 'localuse' OR 'renew'
>should now contain patrons categorycode in 'usercode' column
>
>Also prove that tests in t/db_dependent/Circulation.t still pass.
>
>Rebased-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi>
>Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
>---
> C4/Circulation.pm                             |  5 +-
> C4/Stats.pm                                   |  5 +-
> installer/data/mysql/kohastructure.sql        |  1 +
> test/add_statistics_borrowers_categorycode.pl | 83 +++++++++++++++++++
> 4 files changed, 92 insertions(+), 2 deletions(-)
> create mode 100755 test/add_statistics_borrowers_categorycode.pl
>
>diff --git a/C4/Circulation.pm b/C4/Circulation.pm
>index c2edf81f949..6c32a787d93 100644
>--- a/C4/Circulation.pm
>+++ b/C4/Circulation.pm
>@@ -798,7 +798,8 @@ sub CanBookBeIssued {
>                      itemnumber => $item_object->itemnumber,
>                      itemtype => $effective_itemtype,
>                      borrowernumber => $patron->borrowernumber,
>-                     ccode => $item_object->ccode}
>+                     ccode => $item_object->ccode},
>+                     usercode => $patron->categorycode,
>                     );
>         ModDateLastSeen( $item_object->itemnumber ); # FIXME Move to Koha::Item
>         return( { STATS => 1 }, {});
>@@ -1644,6 +1645,7 @@ sub AddIssue {
>                     location       => $item_object->location,
>                     borrowernumber => $borrower->{'borrowernumber'},
>                     ccode          => $item_object->ccode,
>+                    usercode       => $borrower->{'categorycode'}
>                 }
>             );
> 
>@@ -3064,6 +3066,7 @@ sub AddRenewal {
>                 location       => $item_object->location,
>                 borrowernumber => $borrowernumber,
>                 ccode          => $item_object->ccode,
>+                usercode       => $patron->categorycode,
>             }
>         );
> 
>diff --git a/C4/Stats.pm b/C4/Stats.pm
>index 1b883d2de18..84b42d2f244 100644
>--- a/C4/Stats.pm
>+++ b/C4/Stats.pm
>@@ -65,6 +65,7 @@ C<$params> is an hashref whose expected keys are:
>     other              : sipmode
>     itemtype           : the type of the item
>     ccode              : the collection code of the item
>+    usercode           : the categorycode of the patron
> 
> type key is mandatory.
> For types used in C4::Circulation (renew,issue,localuse,return), the following other keys are mandatory:
>@@ -82,7 +83,7 @@ sub UpdateStats {
> # make some controls
>     return () if ! defined $params;
> # change these arrays if new types of transaction or new parameters are allowed
>-    my @allowed_keys = qw (type branch amount other itemnumber itemtype borrowernumber ccode location);
>+    my @allowed_keys = qw (type branch amount other itemnumber itemtype borrowernumber ccode location usercode);
>     my @allowed_circulation_types = qw (renew issue localuse return onsite_checkout);
>     my @allowed_accounts_types = qw (writeoff payment);
>     my @circulation_mandatory_keys = qw (type branch borrowernumber itemnumber ccode itemtype);
>@@ -123,6 +124,7 @@ sub UpdateStats {
>     my $itemtype          = exists $params->{itemtype}       ? $params->{itemtype}       : '';
>     my $location          = exists $params->{location}       ? $params->{location}       : undef;
>     my $ccode             = exists $params->{ccode}          ? $params->{ccode}          : '';
>+    my $usercode          = exists $params->{usercode}       ? $params->{usercode}       : undef;
> 
>     my $dtf = Koha::Database->new->schema->storage->datetime_parser;
>     my $statistic = Koha::Statistic->new(
>@@ -137,6 +139,7 @@ sub UpdateStats {
>             location       => $location,
>             borrowernumber => $borrowernumber,
>             ccode          => $ccode,
>+            usercode       => $usercode,
>         }
>     )->store;
> 
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index a309cea736f..8ec2fe104a6 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -4706,6 +4706,7 @@ CREATE TABLE `statistics` (
>   `location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the shelving location for this item (MARC21 952$c)',
>   `borrowernumber` int(11) DEFAULT NULL COMMENT 'foreign key from the borrowers table, links transaction to a specific borrower',
>   `ccode` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the items table, links transaction to a specific collection code',
>+  `usercode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
>   KEY `timeidx` (`datetime`),
>   KEY `branch_idx` (`branch`),
>   KEY `type_idx` (`type`),
>diff --git a/test/add_statistics_borrowers_categorycode.pl b/test/add_statistics_borrowers_categorycode.pl
>new file mode 100755
>index 00000000000..ef828d81f63
>--- /dev/null
>+++ b/test/add_statistics_borrowers_categorycode.pl
>@@ -0,0 +1,83 @@
>+#! /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 Koha::Patrons;
>+
>+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) {
>+    $borrower = $borrower->unblessed;
>+    $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 (exists $borrowers->{$borrowernumber}) {
>+        return $borrowers->{$borrowernumber};
>+    }
>+    my $borrower = Koha::Patrons->find({ 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;
>+}
>-- 
>2.17.1

Actions: View | Diff | Splinter Review

Attachments on bug 7021: 29456 | 29508 | 122881 | 122882 | 122883 | 129119 | 129120 | 129121 | 129122 | 135269 | 135270 | 135271 | 135272 | 135273 | 135274 | 140103 | 140104 | 140105 | 140106 | 140107 | 140108 | 140109 | 140110 | 140116 | 140144 | 140845