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
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.
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.
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>.
21 C4::Members::Statistics - Get statistics for patron checkouts
29 our ( @ISA, @EXPORT, @EXPORT_OK );
36 &GetTotalIssuesTodayByBorrower
37 &GetTotalIssuesReturnedTodayByBorrower
38 &GetPrecedentStateByBorrower
43 Get fields form syspref 'StatisticsFields'
44 Returns list of valid fields, defaults to 'location|itype|ccode'
45 if syspref is empty or does not contain valid fields
51 my $syspref = C4::Context->preference('StatisticsFields');
56 my @spfields = split ('\|', $syspref);
57 my $schema = Koha::Database->new()->schema();
58 my @columns = $schema->source('Item')->columns;
60 foreach my $fn ( @spfields ) {
61 push ( @ret, $fn ) if ( grep { $_ eq $fn } @columns );
63 $ret = join( '|', @ret);
65 return $ret || 'location|itype|ccode';
68 =head2 construct_query
69 Build a sql query from a subquery
70 Adds statistics fields to the select and the group by clause
77 my $fields = get_fields();
78 my @select_fields = split '\|', $fields;
79 my $query = "SELECT COUNT(*) as count_$count,";
80 $query .= join ',', @select_fields;
82 $query .= " " . $subquery;
85 $query .= " GROUP BY $fields;";
91 =head2 GetTotalIssuesTodayByBorrower
92 Return total issues for a borrower at this current day
96 sub GetTotalIssuesTodayByBorrower {
97 my ($borrowernumber) = @_;
98 my $dbh = C4::Context->dbh;
100 my $query = construct_query "total_issues_today",
102 SELECT it.* FROM issues i, items it WHERE i.itemnumber = it.itemnumber AND i.borrowernumber = ? AND DATE(i.issuedate) = CAST(now() AS date)
104 SELECT it.* FROM old_issues oi, items it WHERE oi.itemnumber = it.itemnumber AND oi.borrowernumber = ? AND DATE(oi.issuedate) = CAST(now() AS date)
105 ) tmp"; # alias is required by MySQL
107 my $sth = $dbh->prepare($query);
108 $sth->execute($borrowernumber, $borrowernumber);
109 return $sth->fetchall_arrayref( {} );
112 =head2 GetTotalIssuesReturnedTodayByBorrower
113 Return total issues returned by a borrower at this current day
117 sub GetTotalIssuesReturnedTodayByBorrower {
118 my ($borrowernumber) = @_;
119 my $dbh = C4::Context->dbh;
121 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 my $sth = $dbh->prepare($query);
124 $sth->execute($borrowernumber);
125 return $sth->fetchall_arrayref( {} );
128 =head2 GetPrecedentStateByBorrower
129 Return the precedent state (before today) for a borrower of his checkins and checkouts
133 sub GetPrecedentStateByBorrower {
134 my ($borrowernumber) = @_;
135 my $dbh = C4::Context->dbh;
137 my $query = construct_query "precedent_state",
139 SELECT it.* FROM issues i, items it WHERE i.borrowernumber = ? AND i.itemnumber = it.itemnumber AND DATE(i.issuedate) < CAST(now() AS date)
141 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)
142 ) tmp"; # alias is required by MySQL
144 my $sth = $dbh->prepare($query);
145 $sth->execute($borrowernumber, $borrowernumber);
146 return $sth->fetchall_arrayref( {});