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, $debug );
32 $debug = $ENV{DEBUG
} || 0;
37 &GetTotalIssuesTodayByBorrower
38 &GetTotalIssuesReturnedTodayByBorrower
39 &GetPrecedentStateByBorrower
44 our $fields = get_fields
();
48 Get fields form syspref 'StatisticsFields'
49 Returns list of valid fields, defaults to 'location|itype|ccode'
50 if syspref is empty or does not contain valid fields
56 my $syspref = C4
::Context
->preference('StatisticsFields');
61 my @spfields = split ('\|', $syspref);
62 my $schema = Koha
::Database
->new()->schema();
63 my @columns = $schema->source('Item')->columns;
65 foreach my $fn ( @spfields ) {
66 push ( @ret, $fn ) if ( grep(/^$fn$/, @columns) );
68 $ret = join( '|', @ret);
70 return $ret || 'location|itype|ccode';
73 =head2 construct_query
74 Build a sql query from a subquery
75 Adds statistics fields to the select and the group by clause
82 my @select_fields = split '\|', $fields;
83 my $query = "SELECT COUNT(*) as count_$count,";
84 $query .= join ',', @select_fields;
86 $query .= " " . $subquery;
89 $query .= " GROUP BY $fields;";
95 =head2 GetTotalIssuesTodayByBorrower
96 Return total issues for a borrower at this current day
100 sub GetTotalIssuesTodayByBorrower
{
101 my ($borrowernumber) = @_;
102 my $dbh = C4
::Context
->dbh;
104 my $query = construct_query
"total_issues_today",
106 SELECT it.* FROM issues i, items it WHERE i.itemnumber = it.itemnumber AND i.borrowernumber = ? AND DATE(i.issuedate) = CAST(now() AS date)
108 SELECT it.* FROM old_issues oi, items it WHERE oi.itemnumber = it.itemnumber AND oi.borrowernumber = ? AND DATE(oi.issuedate) = 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( {} );
116 =head2 GetTotalIssuesReturnedTodayByBorrower
117 Return total issues returned by a borrower at this current day
121 sub GetTotalIssuesReturnedTodayByBorrower
{
122 my ($borrowernumber) = @_;
123 my $dbh = C4
::Context
->dbh;
125 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) ";
127 my $sth = $dbh->prepare($query);
128 $sth->execute($borrowernumber);
129 return $sth->fetchall_arrayref( {} );
132 =head2 GetPrecedentStateByBorrower
133 Return the precedent state (before today) for a borrower of his checkins and checkouts
137 sub GetPrecedentStateByBorrower
{
138 my ($borrowernumber) = @_;
139 my $dbh = C4
::Context
->dbh;
141 my $query = construct_query
"precedent_state",
143 SELECT it.* FROM issues i, items it WHERE i.borrowernumber = ? AND i.itemnumber = it.itemnumber AND DATE(i.issuedate) < CAST(now() AS date)
145 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)
146 ) tmp"; # alias is required by MySQL
148 my $sth = $dbh->prepare($query);
149 $sth->execute($borrowernumber, $borrowernumber);
150 return $sth->fetchall_arrayref( {});