C4/Members/Statistics.pm

   1 package C4::Members::Statistics;
   2
   3 # Copyright 2012 BibLibre
   4 # This file is part of Koha.
   5 #
   6 # Koha is free software; you can redistribute it and/or modify it
   7 # under the terms of the GNU General Public License as published by
   8 # the Free Software Foundation; either version 3 of the License, or
   9 # (at your option) any later version.
  10 #
  11 # Koha is distributed in the hope that it will be useful, but
  12 # WITHOUT ANY WARRANTY; without even the implied warranty of
  13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  14 # GNU General Public License for more details.
  15 #
  16 # You should have received a copy of the GNU General Public License
  17 # along with Koha; if not, see <http://www.gnu.org/licenses>.
  18
  19 =head1 NAME
  20
  21 C4::Members::Statistics - Get statistics for patron checkouts
  22
  23 =cut
  24
  25 use Modern::Perl;
  26
  27 use C4::Context;
  28
  29 our ( @ISA, @EXPORT, @EXPORT_OK, $debug );
  30
  31 BEGIN {
  32     $debug = $ENV{DEBUG} || 0;
  33     require Exporter;
  34     @ISA = qw(Exporter);
  35
  36     push @EXPORT, qw(
  37         &GetTotalIssuesTodayByBorrower
  38         &GetTotalIssuesReturnedTodayByBorrower
  39         &GetPrecedentStateByBorrower
  40     );
  41 }
  42
  43 =head2 get_fields
  44   Get fields form syspref 'StatisticsFields'
  45   Returns list of valid fields, defaults to 'location|itype|ccode'
  46   if syspref is empty or does not contain valid fields
  47
  48 =cut
  49
  50 sub get_fields {
  51
  52     my $syspref = C4::Context->preference('StatisticsFields');
  53     my $ret;
  54
  55     if ( $syspref ) {
  56         my @ret;
  57         my @spfields = split ('\|', $syspref);
  58         my $schema       = Koha::Database->new()->schema();
  59         my @columns = $schema->source('Item')->columns;
  60
  61         foreach my $fn ( @spfields ) {
  62             push ( @ret, $fn ) if ( grep { $_ eq $fn } @columns );
  63         }
  64         $ret = join( '|', @ret);
  65     }
  66     return $ret || 'location|itype|ccode';
  67 }
  68
  69 =head2 construct_query
  70   Build a sql query from a subquery
  71   Adds statistics fields to the select and the group by clause
  72
  73 =cut
  74
  75 sub construct_query {
  76     my $count    = shift;
  77     my $subquery = shift;
  78     my $fields = get_fields();
  79     my @select_fields = split '\|', $fields;
  80     my $query = "SELECT COUNT(*) as count_$count,";
  81     $query .= join ',', @select_fields;
  82
  83     $query .= " " . $subquery;
  84
  85     $fields =~ s/\|/,/g;
  86     $query .= " GROUP BY $fields;";
  87
  88     return $query;
  89
  90 }
  91
  92 =head2 GetTotalIssuesTodayByBorrower
  93   Return total issues for a borrower at this current day
  94
  95 =cut
  96
  97 sub GetTotalIssuesTodayByBorrower {
  98     my ($borrowernumber) = @_;
  99     my $dbh   = C4::Context->dbh;
 100
 101     my $query = construct_query "total_issues_today",
 102         "FROM (
 103             SELECT it.* FROM issues i, items it WHERE i.itemnumber = it.itemnumber AND i.borrowernumber = ? AND DATE(i.issuedate) = CAST(now() AS date)
 104             UNION
 105             SELECT it.* FROM old_issues oi, items it WHERE oi.itemnumber = it.itemnumber AND oi.borrowernumber = ? AND DATE(oi.issuedate) = CAST(now() AS date)
 106         ) tmp";     # alias is required by MySQL
 107
 108     my $sth = $dbh->prepare($query);
 109     $sth->execute($borrowernumber, $borrowernumber);
 110     return $sth->fetchall_arrayref( {} );
 111 }
 112
 113 =head2 GetTotalIssuesReturnedTodayByBorrower
 114   Return total issues returned by a borrower at this current day
 115
 116 =cut
 117
 118 sub GetTotalIssuesReturnedTodayByBorrower {
 119     my ($borrowernumber) = @_;
 120     my $dbh   = C4::Context->dbh;
 121
 122     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) ";
 123
 124     my $sth = $dbh->prepare($query);
 125     $sth->execute($borrowernumber);
 126     return $sth->fetchall_arrayref( {} );
 127 }
 128
 129 =head2 GetPrecedentStateByBorrower
 130   Return the precedent state (before today) for a borrower of his checkins and checkouts
 131
 132 =cut
 133
 134 sub GetPrecedentStateByBorrower {
 135     my ($borrowernumber) = @_;
 136     my $dbh   = C4::Context->dbh;
 137
 138     my $query = construct_query "precedent_state",
 139         "FROM (
 140             SELECT it.* FROM issues i, items it WHERE i.borrowernumber = ? AND i.itemnumber = it.itemnumber AND DATE(i.issuedate) < CAST(now() AS date)
 141             UNION
 142             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)
 143         ) tmp";     # alias is required by MySQL
 144
 145     my $sth = $dbh->prepare($query);
 146     $sth->execute($borrowernumber, $borrowernumber);
 147     return $sth->fetchall_arrayref( {});
 148 }
 149
 150 1;