View | Details | Raw Unified | Return to bug 7955
Collapse All | Expand All

(-)a/C4/Members/Statistics.pm (+117 lines)
Line 0 Link Here
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 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
9
# version.
10
#
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.
14
#
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.
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 construct_query
44
  Build a sql query from a subquery
45
  Adds statistics fields to the select and the group by clause
46
=cut
47
sub construct_query {
48
    my $count    = shift;
49
    my $subquery = shift;
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;
54
55
    $query .= " " . $subquery;
56
57
    $fields =~ s/\|/,/g;
58
    $query .= " GROUP BY $fields;";
59
60
    return $query;
61
62
}
63
64
=head2 GetTotalIssuesTodayByBorrower
65
  Return total issues for a borrower at this current day
66
=cut
67
sub GetTotalIssuesTodayByBorrower {
68
    my ($borrowernumber) = @_;
69
    my $dbh   = C4::Context->dbh;
70
71
    my $query = construct_query "total_issues_today",
72
        "FROM (
73
            SELECT it.* FROM issues i, items it WHERE i.itemnumber = it.itemnumber AND i.borrowernumber = ? AND DATE(i.issuedate) = CAST(now() AS date)
74
            UNION
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
77
78
    my $sth = $dbh->prepare($query);
79
    $sth->execute($borrowernumber, $borrowernumber);
80
    return $sth->fetchall_arrayref( {} );
81
}
82
83
=head2 GetTotalIssuesReturnedTodayByBorrower
84
  Return total issues returned by a borrower at this current day
85
=cut
86
sub GetTotalIssuesReturnedTodayByBorrower {
87
    my ($borrowernumber) = @_;
88
    my $dbh   = C4::Context->dbh;
89
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) ";
91
92
    my $sth = $dbh->prepare($query);
93
    $sth->execute($borrowernumber);
94
    return $sth->fetchall_arrayref( {} );
95
}
96
97
=head2 GetPrecedentStateByBorrower
98
  Return the precedent state (before today) for a borrower of his checkins and checkouts
99
=cut
100
sub GetPrecedentStateByBorrower {
101
    my ($borrowernumber) = @_;
102
    my $dbh   = C4::Context->dbh;
103
104
    my $query = construct_query "precedent_state",
105
        "FROM (
106
            SELECT it.* FROM issues i, items it WHERE i.borrowernumber = ? AND i.itemnumber = it.itemnumber AND DATE(i.issuedate) < CAST(now() AS date)
107
            UNION
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
110
111
    my $sth = $dbh->prepare($query);
112
    $sth->execute($borrowernumber, $borrowernumber);
113
    return $sth->fetchall_arrayref( {});
114
}
115
116
1;
117
(-)a/installer/data/mysql/updatedatabase.pl (+9 lines)
Lines 5343-5348 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
5343
    SetVersion($DBversion);
5343
    SetVersion($DBversion);
5344
}
5344
}
5345
5345
5346
$DBversion = "3.09.00.XXX";
5347
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
5348
    $dbh->do(qq{
5349
        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')
5350
    });
5351
    print "Upgrade to $DBversion done (Add System preference StatisticsFields)\n";
5352
    SetVersion($DBversion);
5353
}
5354
5346
=head1 FUNCTIONS
5355
=head1 FUNCTIONS
5347
5356
5348
=head2 TableExists($table)
5357
=head2 TableExists($table)
(-)a/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc (+1 lines)
Lines 74-79 Link Here
74
    [% IF ( EnhancedMessagingPreferences ) %]
74
    [% IF ( EnhancedMessagingPreferences ) %]
75
    [% END %]	
75
    [% END %]	
76
	[% IF ( sentnotices ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/notices.pl?borrowernumber=[% borrowernumber %]">Notices</a></li>
76
	[% IF ( sentnotices ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/notices.pl?borrowernumber=[% borrowernumber %]">Notices</a></li>
77
    [% IF (  statisticsview ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/statistics.pl?borrowernumber=[% borrowernumber %]">Statistics</a></li>
77
</ul></div>
78
</ul></div>
78
[% END %]
79
[% END %]
79
80
(-)a/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.tt (+1 lines)
Lines 77-82 in the global namespace %] Link Here
77
    [% IF ( EnhancedMessagingPreferences ) %]
77
    [% IF ( EnhancedMessagingPreferences ) %]
78
    [% END %]
78
    [% END %]
79
    [% IF ( sentnotices ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/notices.pl?borrowernumber=[% borrower.borrowernumber %]">Notices</a></li>
79
    [% IF ( sentnotices ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/notices.pl?borrowernumber=[% borrower.borrowernumber %]">Notices</a></li>
80
    [% IF (  statisticsview ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/statistics.pl?borrowernumber=[% borrowernumber %]">Statistics</a></li>
80
</ul></div>
81
</ul></div>
81
[% END %]
82
[% END %]
82
83
(-)a/koha-tmpl/intranet-tmpl/prog/en/includes/members-menu.inc (+1 lines)
Lines 10-14 Link Here
10
    [% IF ( EnhancedMessagingPreferences ) %]
10
    [% IF ( EnhancedMessagingPreferences ) %]
11
    [% END %]
11
    [% END %]
12
	[% IF ( sentnotices ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/notices.pl?borrowernumber=[% borrowernumber %]">Notices</a></li>
12
	[% IF ( sentnotices ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/notices.pl?borrowernumber=[% borrowernumber %]">Notices</a></li>
13
    [% IF (  statisticsview ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/statistics.pl?borrowernumber=[% borrowernumber %]">Statistics</a></li>
13
</ul></div>
14
</ul></div>
14
[% END %]
15
[% END %]
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref (+4 lines)
Lines 121-123 Patrons: Link Here
121
           choices:
121
           choices:
122
               now: current date.
122
               now: current date.
123
               dateexpiry: current membership expiry date.
123
               dateexpiry: current membership expiry date.
124
     -
125
        - pref: StatisticsFields
126
          class: multi
127
        - Define Fields (from the items table) used for statistics members (separate fields with |, for example:"location|itype|ccode").
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/members/statistics.tt (+84 lines)
Line 0 Link Here
1
[% INCLUDE 'doc-head-open.inc' %]
2
<title>Koha &rsaquo; Patrons &rsaquo;
3
[% IF ( unknowuser ) %]
4
    Patron does not exist
5
[% ELSE %]
6
    Patron details for [% INCLUDE 'patron-title.inc' %]
7
[% END %]
8
</title>
9
<link rel="stylesheet" type="text/css" href="[% themelang %]/css/datatables.css" />
10
[% INCLUDE 'doc-head-close.inc' %]
11
<script type="text/javascript" src="[% themelang %]/lib/jquery/plugins/jquery.dataTables.min.js"></script>
12
[% INCLUDE 'datatables-strings.inc' %]
13
<script type="text/javascript" src="[% themelang %]/js/datatables.js"></script>
14
[% INCLUDE 'calendar.inc' %]
15
<script type="text/javascript" src="[% themelang %]/lib/jquery/plugins/jquery.tablesorter.min.js"></script>
16
<script type="text/javascript">
17
    $(document).ready(function() {
18
        $("#statistics").dataTable($.extend(true, {}, dataTablesDefaults, {
19
            'bPaginate': false,
20
            'bFilter': false,
21
            'bInfo': false,
22
        } ));
23
    });
24
</script>
25
</head>
26
27
<body>
28
[% INCLUDE 'header.inc' %]
29
[% INCLUDE 'patron-search.inc' %]
30
31
<div id="breadcrumbs">
32
         <a href="/cgi-bin/koha/mainpage.pl">Home</a>
33
&rsaquo; <a href="/cgi-bin/koha/members/members-home.pl">Patrons</a>
34
&rsaquo; [% IF ( unknowuser ) %]Patron does not exist[% ELSE %]Patron statistics for [% firstname %] [% surname %] ([% cardnumber %])[% END %]
35
</div>
36
37
<div id="doc3" class="yui-t1">
38
39
   <div id="bd">
40
    <div id="yui-main">
41
        <div class="yui-b">
42
            <div class="yui-g">
43
                <h2>Statistics</h2>
44
                <table id="statistics">
45
                <thead>
46
                    <tr>
47
                      [% FOREACH cn IN column_names %]
48
                          <th>[% cn %]</th>
49
                      [% END %]
50
                      <th>Precedent State</th>
51
                      <th>Issues</th>
52
                      <th>Issues returned</th>
53
                      <th>Actual State</th>
54
                    </tr>
55
                </thead>
56
57
                <tbody>
58
                    [% FOREACH r IN datas %]
59
                        <tr>
60
                            [% FOREACH c IN r %]
61
                                <td>[% c %]</td>
62
                            [% END %]
63
                        </tr>
64
                    [% END %]
65
                </tbody>
66
                <tfoot>
67
                    <tr>
68
                        <td colspan="[% length_keys %]">TOTAL</td>
69
                        <td>[% count_total_precedent_state %]</td>
70
                        <td>[% count_total_issues %]</td>
71
                        <td>[% count_total_issues_returned %]</td>
72
                        <td>[% count_total_actual_state %]</td>
73
                    </tr>
74
                </tfoot>
75
                </table>
76
            </div>
77
        </div>
78
    </div>
79
<div class="yui-b">
80
[% INCLUDE 'circ-menu.inc' %]
81
</div>
82
</div>
83
[% INCLUDE 'intranet-bottom.inc' %]
84
(-)a/members/statistics.pl (-1 / +214 lines)
Line 0 Link Here
0
- 
1
#!/usr/bin/perl
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 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
9
# version.
10
#
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.
14
#
15
# You should have received a copy of the GNU General Public License along with
16
# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
17
# Suite 330, Boston, MA  02111-1307 USA
18
19
20
=head1 members/statistics.pl
21
  Generate statistic issues for a member
22
=cut
23
24
use Modern::Perl;
25
26
use CGI;
27
use C4::Auth;
28
use C4::Branch;
29
use C4::Context;
30
use C4::Members;
31
use C4::Members::Statistics;
32
use C4::Output;
33
34
my $input = new CGI;
35
36
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
37
    {   template_name   => "members/statistics.tmpl",
38
        query           => $input,
39
        type            => "intranet",
40
        authnotrequired => 0,
41
        flagsrequired   => { borrowers => 1 },
42
        debug           => 1,
43
    }
44
);
45
46
my $borrowernumber = $input->param('borrowernumber');
47
48
# Set informations for the patron
49
my $borrower = GetMemberDetails( $borrowernumber, 0 );
50
if ( not defined $borrower ) {
51
    $template->param (unknowuser => 1);
52
    output_html_with_http_headers $input, $cookie, $template->output;
53
    exit;
54
}
55
56
foreach my $key ( keys %$borrower ) {
57
    $template->param( $key => $borrower->{$key} );
58
}
59
60
# Construct column names
61
my $fields = C4::Context->preference('StatisticsFields') || 'location|itype|ccode';
62
our @statistic_column_names = split '\|', $fields;
63
our @value_column_names = ( 'count_precedent_state', 'count_total_issues_today', 'count_total_issues_returned_today' );
64
our @column_names = ( @statistic_column_names, @value_column_names );
65
66
# Get statistics
67
my $precedent_state = GetPrecedentStateByBorrower( $borrowernumber );
68
my $total_issues_today = GetTotalIssuesTodayByBorrower( $borrowernumber );
69
my $total_issues_returned_today = GetTotalIssuesReturnedTodayByBorrower( $borrowernumber );
70
my $r = merge (
71
    @$precedent_state, @$total_issues_today, @$total_issues_returned_today
72
);
73
add_actual_state( $r );
74
my ( $total, $datas ) = build_array( $r );
75
76
# Gettings sums
77
my $count_total_precedent_state = $total->{count_precedent_state} || 0;
78
my $count_total_issues = $total->{count_total_issues_today} || 0;
79
my $count_total_issues_returned = $total->{count_total_issues_returned_today} || 0;
80
my $count_total_actual_state = ($count_total_precedent_state - $count_total_issues_returned + $count_total_issues);
81
82
$template->param(
83
    statisticsview => 1,
84
    datas          => $datas,
85
    column_names   => \@statistic_column_names,
86
    length_keys    => scalar( @statistic_column_names),
87
    count_total_issues => $count_total_issues,
88
    count_total_issues_returned => $count_total_issues_returned,
89
    count_total_precedent_state => $count_total_precedent_state,
90
    count_total_actual_state => $count_total_actual_state,
91
);
92
93
output_html_with_http_headers $input, $cookie, $template->output;
94
95
96
=head1 FUNCTIONS
97
98
=head2 add_actual_state
99
  Add a 'count_actual_state' key in all hashes
100
  count_actual_state = count_precedent_state - count_total_issues_returned_today + count_total_issues_today
101
=cut
102
sub add_actual_state {
103
    my ( $array ) = @_;
104
    for my $hash ( @$array ) {
105
        $hash->{count_actual_state} = ( $hash->{count_precedent_state} // 0 ) - ( $hash->{count_total_issues_returned_today} // 0 ) + ( $hash->{count_total_issues_today} // 0 );
106
    }
107
}
108
109
=head2 build_array
110
  Build a new array containing values of hashes.
111
  It used by template whitch display silly values.
112
  ex:
113
    $array = [
114
      {
115
        'count_total_issues_returned_today' => 1,
116
        'ccode' => 'ccode',
117
        'count_actual_state' => 1,
118
        'count_precedent_state' => 1,
119
        'homebranch' => 'homebranch',
120
        'count_total_issues_today' => 1,
121
        'itype' => 'itype'
122
      }
123
    ];
124
  and returns:
125
    [
126
      [
127
        'homebranch',
128
        'itype',
129
        'ccode',
130
        1,
131
        1,
132
        1,
133
        1
134
      ]
135
    ];
136
137
=cut
138
sub build_array {
139
    my ( $array ) = @_;
140
    my ( @r, $total );
141
    for my $hash ( @$array) {
142
        my @line;
143
        for my $cn ( ( @column_names, 'count_actual_state') ) {
144
            if ( grep /$cn/, ( @value_column_names, 'count_actual_state') ) {
145
                $hash->{$cn} //= 0;
146
                if ( exists $total->{$cn} ) {
147
                    $total->{$cn} += $hash->{$cn} if $hash->{$cn};
148
                } else {
149
                    $total->{$cn} = $hash->{$cn};
150
                }
151
            }
152
            push @line, $hash->{$cn};
153
        }
154
        push @r, \@line;
155
    }
156
    return ( $total, \@r );
157
}
158
159
=head2 merge
160
  Merge hashes with the same statistic column names into one
161
  param: array, a arrayref of arrayrefs
162
  ex:
163
  @array = (
164
     {
165
       'ccode' => 'ccode',
166
       'count_precedent_state' => '1',
167
       'homebranch' => 'homebranch',
168
       'itype' => 'itype'
169
     },
170
     {
171
       'count_total_issues_returned_today' => '1',
172
       'ccode' => 'ccode',
173
       'homebranch' => 'homebranch',
174
       'itype' => 'itype'
175
     }
176
   );
177
   and returns:
178
   [
179
     {
180
       'count_total_issues_returned_today' => '1',
181
       'ccode' => 'ccode',
182
       'count_precedent_state' => '1',
183
       'homebranch' => 'homebranch',
184
       'itype' => 'itype'
185
     }
186
   ];
187
188
=cut
189
sub merge {
190
    my @array = @_;
191
    my @r;
192
    for my $h ( @array ) {
193
        my $exists = 0;
194
        for my $ch ( @r ) {
195
            $exists = 1;
196
            for my $cn ( @statistic_column_names ) {
197
                if ( not $ch->{$cn} eq $h->{$cn} ) {
198
                    $exists = 0;
199
                    last;
200
                }
201
            }
202
            if ($exists){
203
                for my $cn ( @value_column_names ) {
204
                    next if not exists $h->{$cn};
205
                    $ch->{$cn} = $h->{$cn} ? $h->{$cn} : 0;
206
                }
207
                last;
208
            }
209
        }
210
211
        if ( not $exists ) {push @r, $h;}
212
    }
213
    return \@r;
214
}

Return to bug 7955