Attachment 10223 Details for Bug 7955 – Bug 7955: Statistics tab for Patron checkouts

[patch] Bug 7955: Statistics tab for Patron checkouts

0001-Bug-7955-Statistics-tab-for-Patron-checkouts.patch (text/plain), 19.00 KB, created by Jonathan Druart on 2012-06-11 15:06:19 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jonathan Druart

Created: 2012-06-11 15:06:19 UTC

Size: 19.00 KB

patch

obsolete

>From 9bb5fb91b2bef1d7af7182f6831ea57f199d8601 Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@biblibre.com>
>Date: Fri, 13 Apr 2012 16:21:32 +0200
>Subject: [PATCH 1/1] Bug 7955: Statistics tab for Patron checkouts
>
>This patch adds a new 'statistics' tab in the Patron module.
>For a borrower, this tab contains a table with:
>- number of checkout for today
>- number of checkin for today
>- "precedent state", the number of checkouts the patron had yesterday on
>  its library card
>- "actual state", the number of checkouts on the borrower card at the current date
>
>A new syspref (StatisticsFields) contains a list of fields (separated by
>pipe (|) on which the table results is based. The default value is
>location|itype|ccode
>
>Signed-off-by: Mathilde Formery <mathilde.formery@ville-nimes.fr>
>---
> C4/Members/Statistics.pm                           |  117 +++++++++++
> installer/data/mysql/updatedatabase.pl             |    9 +
> .../intranet-tmpl/prog/en/includes/circ-menu.inc   |    1 +
> .../intranet-tmpl/prog/en/includes/circ-menu.tt    |    1 +
> .../prog/en/includes/members-menu.inc              |    1 +
> .../prog/en/modules/admin/preferences/patrons.pref |    4 +
> .../prog/en/modules/members/statistics.tt          |   84 ++++++++
> members/statistics.pl                              |  214 ++++++++++++++++++++
> 8 files changed, 431 insertions(+), 0 deletions(-)
> create mode 100644 C4/Members/Statistics.pm
> create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/members/statistics.tt
> create mode 100755 members/statistics.pl
>
>diff --git a/C4/Members/Statistics.pm b/C4/Members/Statistics.pm
>new file mode 100644
>index 0000000..87ce912
>--- /dev/null
>+++ b/C4/Members/Statistics.pm
>@@ -0,0 +1,117 @@
>+package C4::Members::Statistics;
>+
>+# Copyright 2012 BibLibre
>+# This file is part of Koha.
>+#
>+# Koha is free software; you can redistribute it and/or modify it under the
>+# terms of the GNU General Public License as published by the Free Software
>+# Foundation; either version 2 of the License, or (at your option) any later
>+# version.
>+#
>+# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
>+# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
>+# A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
>+#
>+# You should have received a copy of the GNU General Public License along
>+# with Koha; if not, write to the Free Software Foundation, Inc.,
>+# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
>+
>+=head1 NAME
>+
>+C4::Members::Statistics - Get statistics for patron checkouts
>+
>+=cut
>+
>+use Modern::Perl;
>+
>+use C4::Context;
>+
>+our ( @ISA, @EXPORT, @EXPORT_OK, $debug );
>+
>+BEGIN {
>+    $debug = $ENV{DEBUG} || 0;
>+    require Exporter;
>+    @ISA = qw(Exporter);
>+
>+    push @EXPORT, qw(
>+        &GetTotalIssuesTodayByBorrower
>+        &GetTotalIssuesReturnedTodayByBorrower
>+        &GetPrecedentStateByBorrower
>+    );
>+}
>+
>+=head2 construct_query
>+  Build a sql query from a subquery
>+  Adds statistics fields to the select and the group by clause
>+=cut
>+sub construct_query {
>+    my $count    = shift;
>+    my $subquery = shift;
>+    my $fields = C4::Context->preference('StatisticsFields') || 'location|itype|ccode';
>+    my @select_fields = split '\|', $fields;
>+    my $query = "SELECT COUNT(*) as count_$count";
>+    $query .= ", " . C4::Context->dbh->quote( $_ ) for @select_fields;
>+
>+    $query .= " " . $subquery;
>+
>+    $fields =~ s/\|/,/g;
>+    $query .= " GROUP BY $fields;";
>+
>+    return $query;
>+
>+}
>+
>+=head2 GetTotalIssuesTodayByBorrower
>+  Return total issues for a borrower at this current day
>+=cut
>+sub GetTotalIssuesTodayByBorrower {
>+    my ($borrowernumber) = @_;
>+    my $dbh   = C4::Context->dbh;
>+
>+    my $query = construct_query "total_issues_today",
>+        "FROM (
>+            SELECT it.* FROM issues i, items it WHERE i.itemnumber = it.itemnumber AND i.borrowernumber = ? AND DATE(i.issuedate) = CAST(now() AS date)
>+            UNION
>+            SELECT it.* FROM old_issues oi, items it WHERE oi.itemnumber = it.itemnumber AND oi.borrowernumber = ? AND DATE(oi.issuedate) = CAST(now() AS date)
>+        ) tmp";     # alias is required by MySQL
>+
>+    my $sth = $dbh->prepare($query);
>+    $sth->execute($borrowernumber, $borrowernumber);
>+    return $sth->fetchall_arrayref( {} );
>+}
>+
>+=head2 GetTotalIssuesReturnedTodayByBorrower
>+  Return total issues returned by a borrower at this current day
>+=cut
>+sub GetTotalIssuesReturnedTodayByBorrower {
>+    my ($borrowernumber) = @_;
>+    my $dbh   = C4::Context->dbh;
>+
>+    my $query = construct_query "total_issues_returned_today", "FROM old_issues i, items it WHERE i.itemnumber = it.itemnumber AND i.borrowernumber = ? AND DATE(i.returndate) = CAST(now() AS date) ";
>+
>+    my $sth = $dbh->prepare($query);
>+    $sth->execute($borrowernumber);
>+    return $sth->fetchall_arrayref( {} );
>+}
>+
>+=head2 GetPrecedentStateByBorrower
>+  Return the precedent state (before today) for a borrower of his checkins and checkouts
>+=cut
>+sub GetPrecedentStateByBorrower {
>+    my ($borrowernumber) = @_;
>+    my $dbh   = C4::Context->dbh;
>+
>+    my $query = construct_query "precedent_state",
>+        "FROM (
>+            SELECT it.* FROM issues i, items it WHERE i.borrowernumber = ? AND i.itemnumber = it.itemnumber AND DATE(i.issuedate) < CAST(now() AS date)
>+            UNION
>+            SELECT it.* FROM old_issues oi, items it WHERE oi.borrowernumber = ? AND oi.itemnumber = it.itemnumber AND DATE(oi.issuedate) < CAST(now() AS date) AND DATE(oi.returndate) = CAST(now() AS date)
>+        ) tmp";     # alias is required by MySQL
>+
>+    my $sth = $dbh->prepare($query);
>+    $sth->execute($borrowernumber, $borrowernumber);
>+    return $sth->fetchall_arrayref( {});
>+}
>+
>+1;
>+
>diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
>index 62b4372..f3e45a3 100755
>--- a/installer/data/mysql/updatedatabase.pl
>+++ b/installer/data/mysql/updatedatabase.pl
>@@ -5343,6 +5343,15 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
>     SetVersion($DBversion);
> }
> 
>+$DBversion = "3.09.00.XXX";
>+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
>+    $dbh->do(qq{
>+        INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES('StatisticsFields','location|itype|ccode','Define Fields (from the items table) used for statistics members','location|itype|ccode','free')
>+    });
>+    print "Upgrade to $DBversion done (Add System preference StatisticsFields)\n";
>+    SetVersion($DBversion);
>+}
>+
> =head1 FUNCTIONS
> 
> =head2 TableExists($table)
>diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc
>index a4b9384..7014c39 100644
>--- a/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc
>+++ b/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc
>@@ -74,6 +74,7 @@
>     [% IF ( EnhancedMessagingPreferences ) %]
>     [% END %]	
> 	[% IF ( sentnotices ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/notices.pl?borrowernumber=[% borrowernumber %]">Notices</a></li>
>+    [% IF (  statisticsview ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/statistics.pl?borrowernumber=[% borrowernumber %]">Statistics</a></li>
> </ul></div>
> [% END %]
> 
>diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.tt b/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.tt
>index 1a17c7f..ced2858 100644
>--- a/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.tt
>+++ b/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.tt
>@@ -77,6 +77,7 @@ in the global namespace %]
>     [% IF ( EnhancedMessagingPreferences ) %]
>     [% END %]
>     [% IF ( sentnotices ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/notices.pl?borrowernumber=[% borrower.borrowernumber %]">Notices</a></li>
>+    [% IF (  statisticsview ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/statistics.pl?borrowernumber=[% borrowernumber %]">Statistics</a></li>
> </ul></div>
> [% END %]
> 
>diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/members-menu.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/members-menu.inc
>index cf89f58..4b771d8 100644
>--- a/koha-tmpl/intranet-tmpl/prog/en/includes/members-menu.inc
>+++ b/koha-tmpl/intranet-tmpl/prog/en/includes/members-menu.inc
>@@ -10,5 +10,6 @@
>     [% IF ( EnhancedMessagingPreferences ) %]
>     [% END %]
> 	[% IF ( sentnotices ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/notices.pl?borrowernumber=[% borrowernumber %]">Notices</a></li>
>+    [% IF (  statisticsview ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/statistics.pl?borrowernumber=[% borrowernumber %]">Statistics</a></li>
> </ul></div>
> [% END %]
>diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref
>index 55b4457..040a805 100644
>--- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref
>+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref
>@@ -121,3 +121,7 @@ Patrons:
>            choices:
>                now: current date.
>                dateexpiry: current membership expiry date.
>+     -
>+        - pref: StatisticsFields
>+          class: multi
>+        - Define Fields (from the items table) used for statistics members (separate fields with |, for example:"location|itype|ccode").
>diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/members/statistics.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/members/statistics.tt
>new file mode 100644
>index 0000000..d14cb9d
>--- /dev/null
>+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/members/statistics.tt
>@@ -0,0 +1,84 @@
>+[% INCLUDE 'doc-head-open.inc' %]
>+<title>Koha &rsaquo; Patrons &rsaquo;
>+[% IF ( unknowuser ) %]
>+    Patron does not exist
>+[% ELSE %]
>+    Patron details for [% INCLUDE 'patron-title.inc' %]
>+[% END %]
>+</title>
>+<link rel="stylesheet" type="text/css" href="[% themelang %]/css/datatables.css" />
>+[% INCLUDE 'doc-head-close.inc' %]
>+<script type="text/javascript" src="[% themelang %]/lib/jquery/plugins/jquery.dataTables.min.js"></script>
>+[% INCLUDE 'datatables-strings.inc' %]
>+<script type="text/javascript" src="[% themelang %]/js/datatables.js"></script>
>+[% INCLUDE 'calendar.inc' %]
>+<script type="text/javascript" src="[% themelang %]/lib/jquery/plugins/jquery.tablesorter.min.js"></script>
>+<script type="text/javascript">
>+    $(document).ready(function() {
>+        $("#statistics").dataTable($.extend(true, {}, dataTablesDefaults, {
>+            'bPaginate': false,
>+            'bFilter': false,
>+            'bInfo': false,
>+        } ));
>+    });
>+</script>
>+</head>
>+
>+<body>
>+[% INCLUDE 'header.inc' %]
>+[% INCLUDE 'patron-search.inc' %]
>+
>+<div id="breadcrumbs">
>+         <a href="/cgi-bin/koha/mainpage.pl">Home</a>
>+&rsaquo; <a href="/cgi-bin/koha/members/members-home.pl">Patrons</a>
>+&rsaquo; [% IF ( unknowuser ) %]Patron does not exist[% ELSE %]Patron statistics for [% firstname %] [% surname %] ([% cardnumber %])[% END %]
>+</div>
>+
>+<div id="doc3" class="yui-t1">
>+
>+   <div id="bd">
>+    <div id="yui-main">
>+        <div class="yui-b">
>+            <div class="yui-g">
>+                <h2>Statistics</h2>
>+                <table id="statistics">
>+                <thead>
>+                    <tr>
>+                      [% FOREACH cn IN column_names %]
>+                          <th>[% cn %]</th>
>+                      [% END %]
>+                      <th>Precedent State</th>
>+                      <th>Issues</th>
>+                      <th>Issues returned</th>
>+                      <th>Actual State</th>
>+                    </tr>
>+                </thead>
>+
>+                <tbody>
>+                    [% FOREACH r IN datas %]
>+                        <tr>
>+                            [% FOREACH c IN r %]
>+                                <td>[% c %]</td>
>+                            [% END %]
>+                        </tr>
>+                    [% END %]
>+                </tbody>
>+                <tfoot>
>+                    <tr>
>+                        <td colspan="[% length_keys %]">TOTAL</td>
>+                        <td>[% count_total_precedent_state %]</td>
>+                        <td>[% count_total_issues %]</td>
>+                        <td>[% count_total_issues_returned %]</td>
>+                        <td>[% count_total_actual_state %]</td>
>+                    </tr>
>+                </tfoot>
>+                </table>
>+            </div>
>+        </div>
>+    </div>
>+<div class="yui-b">
>+[% INCLUDE 'circ-menu.inc' %]
>+</div>
>+</div>
>+[% INCLUDE 'intranet-bottom.inc' %]
>+
>diff --git a/members/statistics.pl b/members/statistics.pl
>new file mode 100755
>index 0000000..8551ec8
>--- /dev/null
>+++ b/members/statistics.pl
>@@ -0,0 +1,214 @@
>+#!/usr/bin/perl
>+
>+# Copyright 2012 BibLibre
>+# This file is part of Koha.
>+#
>+# Koha is free software; you can redistribute it and/or modify it under the
>+# terms of the GNU General Public License as published by the Free Software
>+# Foundation; either version 2 of the License, or (at your option) any later
>+# version.
>+#
>+# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
>+# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
>+# A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
>+#
>+# You should have received a copy of the GNU General Public License along with
>+# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
>+# Suite 330, Boston, MA  02111-1307 USA
>+
>+
>+=head1 members/statistics.pl
>+  Generate statistic issues for a member
>+=cut
>+
>+use Modern::Perl;
>+
>+use CGI;
>+use C4::Auth;
>+use C4::Branch;
>+use C4::Context;
>+use C4::Members;
>+use C4::Members::Statistics;
>+use C4::Output;
>+
>+my $input = new CGI;
>+
>+my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
>+    {   template_name   => "members/statistics.tmpl",
>+        query           => $input,
>+        type            => "intranet",
>+        authnotrequired => 0,
>+        flagsrequired   => { borrowers => 1 },
>+        debug           => 1,
>+    }
>+);
>+
>+my $borrowernumber = $input->param('borrowernumber');
>+
>+# Set informations for the patron
>+my $borrower = GetMemberDetails( $borrowernumber, 0 );
>+if ( not defined $borrower ) {
>+    $template->param (unknowuser => 1);
>+    output_html_with_http_headers $input, $cookie, $template->output;
>+    exit;
>+}
>+
>+foreach my $key ( keys %$borrower ) {
>+    $template->param( $key => $borrower->{$key} );
>+}
>+
>+# Construct column names
>+my $fields = C4::Context->preference('StatisticsFields') || 'location|itype|ccode';
>+our @statistic_column_names = split '\|', $fields;
>+our @value_column_names = ( 'count_precedent_state', 'count_total_issues_today', 'count_total_issues_returned_today' );
>+our @column_names = ( @statistic_column_names, @value_column_names );
>+
>+# Get statistics
>+my $precedent_state = GetPrecedentStateByBorrower( $borrowernumber );
>+my $total_issues_today = GetTotalIssuesTodayByBorrower( $borrowernumber );
>+my $total_issues_returned_today = GetTotalIssuesReturnedTodayByBorrower( $borrowernumber );
>+my $r = merge (
>+    @$precedent_state, @$total_issues_today, @$total_issues_returned_today
>+);
>+add_actual_state( $r );
>+my ( $total, $datas ) = build_array( $r );
>+
>+# Gettings sums
>+my $count_total_precedent_state = $total->{count_precedent_state} || 0;
>+my $count_total_issues = $total->{count_total_issues_today} || 0;
>+my $count_total_issues_returned = $total->{count_total_issues_returned_today} || 0;
>+my $count_total_actual_state = ($count_total_precedent_state - $count_total_issues_returned + $count_total_issues);
>+
>+$template->param(
>+    statisticsview => 1,
>+    datas          => $datas,
>+    column_names   => \@statistic_column_names,
>+    length_keys    => scalar( @statistic_column_names),
>+    count_total_issues => $count_total_issues,
>+    count_total_issues_returned => $count_total_issues_returned,
>+    count_total_precedent_state => $count_total_precedent_state,
>+    count_total_actual_state => $count_total_actual_state,
>+);
>+
>+output_html_with_http_headers $input, $cookie, $template->output;
>+
>+
>+=head1 FUNCTIONS
>+
>+=head2 add_actual_state
>+  Add a 'count_actual_state' key in all hashes
>+  count_actual_state = count_precedent_state - count_total_issues_returned_today + count_total_issues_today
>+=cut
>+sub add_actual_state {
>+    my ( $array ) = @_;
>+    for my $hash ( @$array ) {
>+        $hash->{count_actual_state} = ( $hash->{count_precedent_state} // 0 ) - ( $hash->{count_total_issues_returned_today} // 0 ) + ( $hash->{count_total_issues_today} // 0 );
>+    }
>+}
>+
>+=head2 build_array
>+  Build a new array containing values of hashes.
>+  It used by template whitch display silly values.
>+  ex:
>+    $array = [
>+      {
>+        'count_total_issues_returned_today' => 1,
>+        'ccode' => 'ccode',
>+        'count_actual_state' => 1,
>+        'count_precedent_state' => 1,
>+        'homebranch' => 'homebranch',
>+        'count_total_issues_today' => 1,
>+        'itype' => 'itype'
>+      }
>+    ];
>+  and returns:
>+    [
>+      [
>+        'homebranch',
>+        'itype',
>+        'ccode',
>+        1,
>+        1,
>+        1,
>+        1
>+      ]
>+    ];
>+
>+=cut
>+sub build_array {
>+    my ( $array ) = @_;
>+    my ( @r, $total );
>+    for my $hash ( @$array) {
>+        my @line;
>+        for my $cn ( ( @column_names, 'count_actual_state') ) {
>+            if ( grep /$cn/, ( @value_column_names, 'count_actual_state') ) {
>+                $hash->{$cn} //= 0;
>+                if ( exists $total->{$cn} ) {
>+                    $total->{$cn} += $hash->{$cn} if $hash->{$cn};
>+                } else {
>+                    $total->{$cn} = $hash->{$cn};
>+                }
>+            }
>+            push @line, $hash->{$cn};
>+        }
>+        push @r, \@line;
>+    }
>+    return ( $total, \@r );
>+}
>+
>+=head2 merge
>+  Merge hashes with the same statistic column names into one
>+  param: array, a arrayref of arrayrefs
>+  ex:
>+  @array = (
>+     {
>+       'ccode' => 'ccode',
>+       'count_precedent_state' => '1',
>+       'homebranch' => 'homebranch',
>+       'itype' => 'itype'
>+     },
>+     {
>+       'count_total_issues_returned_today' => '1',
>+       'ccode' => 'ccode',
>+       'homebranch' => 'homebranch',
>+       'itype' => 'itype'
>+     }
>+   );
>+   and returns:
>+   [
>+     {
>+       'count_total_issues_returned_today' => '1',
>+       'ccode' => 'ccode',
>+       'count_precedent_state' => '1',
>+       'homebranch' => 'homebranch',
>+       'itype' => 'itype'
>+     }
>+   ];
>+
>+=cut
>+sub merge {
>+    my @array = @_;
>+    my @r;
>+    for my $h ( @array ) {
>+        my $exists = 0;
>+        for my $ch ( @r ) {
>+            $exists = 1;
>+            for my $cn ( @statistic_column_names ) {
>+                if ( not $ch->{$cn} eq $h->{$cn} ) {
>+                    $exists = 0;
>+                    last;
>+                }
>+            }
>+            if ($exists){
>+                for my $cn ( @value_column_names ) {
>+                    next if not exists $h->{$cn};
>+                    $ch->{$cn} = $h->{$cn} ? $h->{$cn} : 0;
>+                }
>+                last;
>+            }
>+        }
>+
>+        if ( not $exists ) {push @r, $h;}
>+    }
>+    return \@r;
>+}
>-- 
>1.7.7.3
>

Actions: View | Diff | Splinter Review

Attachments on bug 7955: 9266 | 9763 | 9771 | 9776 | 10015 | 10223 | 10407 | 10409 | 10410 | 10518 | 10520 | 10746 | 10747 | 10826