From 5e2001a6f8be693f3c2d816d1b2558b632c00dd4 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 13 Apr 2012 16:21:32 +0200 Subject: [PATCH] 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 --- C4/Members/Statistics.pm | 116 ++++++++++ installer/data/mysql/updatedatabase.pl | 8 + .../prog/en/includes/circ-menu.inc | 1 + .../prog/en/includes/circ-menu.tt | 1 + .../prog/en/includes/members-menu.inc | 1 + .../en/modules/admin/preferences/patrons.pref | 4 + .../prog/en/modules/members/statistics.tt | 83 +++++++ members/statistics.pl | 214 ++++++++++++++++++ 8 files changed, 428 insertions(+) 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 0000000000..302259c1c3 --- /dev/null +++ b/C4/Members/Statistics.pm @@ -0,0 +1,116 @@ +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 9b02cb935d..1b6fe84374 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5369,6 +5369,14 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.09.00.015"; +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 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 a4b9384889..7014c39ee1 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 ) %]
  • [% ELSE %]
  • [% END %]Notices
    • + [% IF ( statisticsview ) %]
  • [% ELSE %]
  • [% END %]Statistics
    • [% 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 1a17c7f9a8..ced2858671 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 ) %]
  • [% ELSE %]
  • [% END %]Notices
    • + [% IF ( statisticsview ) %]
  • [% ELSE %]
  • [% END %]Statistics
    • [% 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 cf89f58aac..4b771d853b 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 ) %]
  • [% ELSE %]
  • [% END %]Notices
    • + [% IF ( statisticsview ) %]
  • [% ELSE %]
  • [% END %]Statistics
    • [% 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 58d67f02e0..030f376af9 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 @@ -127,3 +127,7 @@ Patrons: yes: Enable no: Disable - patron phone notifications using Talking Tech i-tiva (overdues, predues and holds notices currently supported). + - + - 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 0000000000..dd468bc123 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/members/statistics.tt @@ -0,0 +1,83 @@ +[% INCLUDE 'doc-head-open.inc' %] +Koha › Patrons › +[% IF ( unknowuser ) %] + Patron does not exist +[% ELSE %] + Patron details for [% INCLUDE 'patron-title.inc' %] +[% END %] + + +[% INCLUDE 'doc-head-close.inc' %] + +[% INCLUDE 'datatables-strings.inc' %] + +[% INCLUDE 'calendar.inc' %] + + + + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'patron-search.inc' %] + + + +
    + +
    +
    +
    +
    +

    Statistics

    + + + + [% FOREACH cn IN column_names %] + + [% END %] + + + + + + + + + [% FOREACH r IN datas %] + + [% FOREACH c IN r %] + + [% END %] + + [% END %] + + + + + + + + + + +
    [% cn %]Precedent StateIssuesIssues returnedActual State
    [% c %]
    TOTAL[% count_total_precedent_state %][% count_total_issues %][% count_total_issues_returned %][% count_total_actual_state %]
    +
    +
    +
    +
    +[% INCLUDE 'circ-menu.inc' %] +
    +
    +[% INCLUDE 'intranet-bottom.inc' %] diff --git a/members/statistics.pl b/members/statistics.pl new file mode 100755 index 0000000000..8551ec8f9b --- /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; +} -- 2.39.5