1 package C4::Members::Statistics;
3 # Copyright 2012 BibLibre
4 # This file is part of Koha.
6 # Koha is free software; you can redistribute it and/or modify it under the
7 # terms of the GNU General Public License as published by the Free Software
8 # Foundation; either version 2 of the License, or (at your option) any later
11 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
12 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
13 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
15 # You should have received a copy of the GNU General Public License along
16 # with Koha; if not, write to the Free Software Foundation, Inc.,
17 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
21 C4::Members::Statistics - Get statistics for patron checkouts
29 our ( @ISA, @EXPORT, @EXPORT_OK, $debug );
32 $debug = $ENV{DEBUG} || 0;
37 &GetTotalIssuesTodayByBorrower
38 &GetTotalIssuesReturnedTodayByBorrower
39 &GetPrecedentStateByBorrower
43 =head2 construct_query
44 Build a sql query from a subquery
45 Adds statistics fields to the select and the group by clause
50 my $fields = C4::Context->preference('StatisticsFields') || 'location|itype|ccode';
51 my @select_fields = split '\|', $fields;
52 my $query = "SELECT COUNT(*) as count_$count";
53 $query .= ", " . C4::Context->dbh->quote( $_ ) for @select_fields;
55 $query .= " " . $subquery;
58 $query .= " GROUP BY $fields;";
64 =head2 GetTotalIssuesTodayByBorrower
65 Return total issues for a borrower at this current day
67 sub GetTotalIssuesTodayByBorrower {
68 my ($borrowernumber) = @_;
69 my $dbh = C4::Context->dbh;
71 my $query = construct_query "total_issues_today",
73 SELECT it.* FROM issues i, items it WHERE i.itemnumber = it.itemnumber AND i.borrowernumber = ? AND DATE(i.issuedate) = CAST(now() AS date)
75 SELECT it.* FROM old_issues oi, items it WHERE oi.itemnumber = it.itemnumber AND oi.borrowernumber = ? AND DATE(oi.issuedate) = CAST(now() AS date)
76 ) tmp"; # alias is required by MySQL
78 my $sth = $dbh->prepare($query);
79 $sth->execute($borrowernumber, $borrowernumber);
80 return $sth->fetchall_arrayref( {} );
83 =head2 GetTotalIssuesReturnedTodayByBorrower
84 Return total issues returned by a borrower at this current day
86 sub GetTotalIssuesReturnedTodayByBorrower {
87 my ($borrowernumber) = @_;
88 my $dbh = C4::Context->dbh;
90 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) ";
92 my $sth = $dbh->prepare($query);
93 $sth->execute($borrowernumber);
94 return $sth->fetchall_arrayref( {} );
97 =head2 GetPrecedentStateByBorrower
98 Return the precedent state (before today) for a borrower of his checkins and checkouts
100 sub GetPrecedentStateByBorrower {
101 my ($borrowernumber) = @_;
102 my $dbh = C4::Context->dbh;
104 my $query = construct_query "precedent_state",
106 SELECT it.* FROM issues i, items it WHERE i.borrowernumber = ? AND i.itemnumber = it.itemnumber AND DATE(i.issuedate) < CAST(now() AS date)
108 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)
109 ) tmp"; # alias is required by MySQL
111 my $sth = $dbh->prepare($query);
112 $sth->execute($borrowernumber, $borrowernumber);
113 return $sth->fetchall_arrayref( {});