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

(-)a/C4/Members/Statistics.pm (+118 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 ( $VERSION, @ISA, @EXPORT, @EXPORT_OK, $debug );
30
31
BEGIN {
32
    $VERSION = 3.02;
33
    $debug = $ENV{DEBUG} || 0;
34
    require Exporter;
35
    @ISA = qw(Exporter);
36
37
    push @EXPORT, qw(
38
        &GetTotalIssuesTodayByBorrower
39
        &GetTotalIssuesReturnedTodayByBorrower
40
        &GetPrecedentStateByBorrower
41
    );
42
}
43
44
=head2 construct_query
45
  Build a sql query from a subquery
46
  Adds statistics fields to the select and the group by clause
47
=cut
48
sub construct_query {
49
    my $count    = shift;
50
    my $subquery = shift;
51
    my $fields = C4::Context->preference('StatisticsFields') || 'location|itype|ccode';
52
    my @select_fields = split '\|', $fields;
53
    my $query = "SELECT COUNT(*) as count_$count";
54
    $query .= ", $_" for @select_fields;
55
56
    $query .= " " . $subquery;
57
58
    $fields =~ s/\|/,/g;
59
    $query .= " GROUP BY $fields;";
60
61
    return $query;
62
63
}
64
65
=head2 GetTotalIssuesTodayByBorrower
66
  Return total issues for a borrower at this current day
67
=cut
68
sub GetTotalIssuesTodayByBorrower {
69
    my ($borrowernumber) = @_;
70
    my $dbh   = C4::Context->dbh;
71
72
    my $query = construct_query "total_issues_today",
73
        "FROM (
74
            SELECT it.* FROM issues i, items it WHERE i.itemnumber = it.itemnumber AND i.borrowernumber = ? AND DATE(i.issuedate) = CAST(now() AS date)
75
            UNION
76
            SELECT it.* FROM old_issues oi, items it WHERE oi.itemnumber = it.itemnumber AND oi.borrowernumber = ? AND DATE(oi.issuedate) = CAST(now() AS date)
77
        ) tmp";     # alias is required by MySQL
78
79
    my $sth = $dbh->prepare($query);
80
    $sth->execute($borrowernumber, $borrowernumber);
81
    return $sth->fetchall_arrayref( {} );
82
}
83
84
=head2 GetTotalIssuesReturnedTodayByBorrower
85
  Return total issues returned by a borrower at this current day
86
=cut
87
sub GetTotalIssuesReturnedTodayByBorrower {
88
    my ($borrowernumber) = @_;
89
    my $dbh   = C4::Context->dbh;
90
91
    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
93
    my $sth = $dbh->prepare($query);
94
    $sth->execute($borrowernumber);
95
    return $sth->fetchall_arrayref( {} );
96
}
97
98
=head2 GetPrecedentStateByBorrower
99
  Return the precedent state (before today) for a borrower of his checkins and checkouts
100
=cut
101
sub GetPrecedentStateByBorrower {
102
    my ($borrowernumber) = @_;
103
    my $dbh   = C4::Context->dbh;
104
105
    my $query = construct_query "precedent_state",
106
        "FROM (
107
            SELECT it.* FROM issues i, items it WHERE i.borrowernumber = ? AND i.itemnumber = it.itemnumber AND DATE(i.issuedate) < CAST(now() AS date)
108
            UNION
109
            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)
110
        ) tmp";     # alias is required by MySQL
111
112
    my $sth = $dbh->prepare($query);
113
    $sth->execute($borrowernumber, $borrowernumber);
114
    return $sth->fetchall_arrayref( {});
115
}
116
117
1;
118
(-)a/installer/data/mysql/updatedatabase.pl (+15 lines)
Lines 5284-5289 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
5284
    SetVersion($DBversion);
5284
    SetVersion($DBversion);
5285
}
5285
}
5286
5286
5287
5288
5289
5290
5291
5292
$DBversion = "3.07.00.XXX";
5293
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
5294
    $dbh->do(qq{
5295
        INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES('StatisticsFields','location|itype|ccode','Define Fields used for statistics members (5 max !)','location|itype|ccode','free')
5296
    });
5297
    print "Upgrade to $DBversion done (Add System preference StatisticsFields)\n";
5298
    SetVersion($DBversion);
5299
}
5300
5301
5287
=head1 FUNCTIONS
5302
=head1 FUNCTIONS
5288
5303
5289
=head2 TableExists($table)
5304
=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 (+5 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 used for statistics patrons 
128
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/members/statistics.tt (+79 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
[% INCLUDE 'doc-head-close.inc' %]
10
[% INCLUDE 'calendar.inc' %]
11
<script type="text/javascript" src="[% themelang %]/lib/jquery/plugins/jquery.tablesorter.min.js"></script>
12
<script type="text/javascript">
13
    $(document).ready(function() {
14
        $("#statistics").tablesorter({
15
            sortList: [[0,0]],
16
            headers: {}
17
        });
18
    });
19
</script>
20
</head>
21
22
<body>
23
[% INCLUDE 'header.inc' %]
24
[% INCLUDE 'patron-search.inc' %]
25
26
<div id="breadcrumbs">
27
         <a href="/cgi-bin/koha/mainpage.pl">Home</a>
28
&rsaquo; <a href="/cgi-bin/koha/members/members-home.pl">Patrons</a>
29
&rsaquo; [% IF ( unknowuser ) %]Patron does not exist[% ELSE %]Patron statistics for [% firstname %] [% surname %] ([% cardnumber %])[% END %]
30
</div>
31
32
<div id="doc3" class="yui-t1">
33
34
   <div id="bd">
35
    <div id="yui-main">
36
        <div class="yui-b">
37
            <div class="yui-g">
38
                <h2>Statistics</h2>
39
                <table id="statistics">
40
                <thead>
41
                    <tr>
42
                      [% FOREACH cn IN column_names %]
43
                          <th>[% cn %]</th>
44
                      [% END %]
45
                      <th>Precedent State</th>
46
                      <th>Issues</th>
47
                      <th>Issues returned</th>
48
                      <th>Actual State</th>
49
                    </tr>
50
                </thead>
51
52
                <tbody>
53
                    [% FOREACH r IN datas %]
54
                        <tr>
55
                            [% FOREACH c IN r %]
56
                                <td>[% c %]</td>
57
                            [% END %]
58
                        </tr>
59
                    [% END %]
60
                </tbody>
61
                <tfoot>
62
                    <tr>
63
                        <td colspan="[% length_keys %]">TOTAL</td>
64
                        <td>[% count_total_precedent_state %]</td>
65
                        <td>[% count_total_issues %]</td>
66
                        <td>[% count_total_issues_returned %]</td>
67
                        <td>[% count_total_actual_state %]</td>
68
                    </tr>
69
                </tfoot>
70
                </table>
71
            </div>
72
        </div>
73
    </div>
74
<div class="yui-b">
75
[% INCLUDE 'circ-menu.inc' %]
76
</div>
77
</div>
78
[% INCLUDE 'intranet-bottom.inc' %]
79
(-)a/members/statistics.pl (-1 / +155 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
foreach my $key ( keys %$borrower ) {
51
    $template->param( $key => $borrower->{$key} );
52
}
53
54
# Construct column names
55
my $fields = C4::Context->preference('StatisticsFields') || 'location|itype|ccode';
56
our @statistic_column_names = split '\|', $fields;
57
our @value_column_names = ( 'count_precedent_state', 'count_total_issues_today', 'count_total_issues_returned_today' );
58
our @column_names = ( @statistic_column_names, @value_column_names );
59
60
# Get statistics
61
my $precedent_state = GetPrecedentStateByBorrower $borrowernumber;
62
my $total_issues_today = GetTotalIssuesTodayByBorrower $borrowernumber;
63
my $total_issues_returned_today = GetTotalIssuesReturnedTodayByBorrower $borrowernumber;
64
my $r = merge (
65
    @$precedent_state, @$total_issues_today, @$total_issues_returned_today
66
);
67
add_actual_state( $r );
68
my ( $total, $datas ) = build_array( $r );
69
70
# Gettings sums
71
my $count_total_precedent_state = $total->{count_precedent_state};
72
my $count_total_issues = $total->{count_total_issues_today};
73
my $count_total_issues_returned = $total->{count_total_issues_returned_today};
74
my $count_total_actual_state = ($total->{count_precedent_state} - $total->{count_total_issues_returned_today} + $total->{count_total_issues_today});
75
76
$template->param(
77
    statisticsview => 1,
78
    datas          => $datas,
79
    column_names   => \@statistic_column_names,
80
    length_keys    => scalar( @statistic_column_names),
81
    count_total_issues => $count_total_issues,
82
    count_total_issues_returned => $count_total_issues_returned,
83
    count_total_precedent_state => $count_total_precedent_state,
84
    count_total_actual_state => $count_total_actual_state,
85
);
86
87
output_html_with_http_headers $input, $cookie, $template->output;
88
89
90
=head1 FUNCTIONS
91
92
=head2 add_actual_state
93
  Add a 'count_actual_state' key in all hashes
94
  count_actual_state = count_precedent_state - count_total_issues_returned_today + count_total_issues_today
95
=cut
96
sub add_actual_state {
97
    my ( $array ) = @_;
98
    for my $hash ( @$array ) {
99
        $hash->{count_actual_state} = ( $hash->{count_precedent_state} // 0 ) - ( $hash->{count_total_issues_returned_today} // 0 ) + ( $hash->{count_total_issues_today} // 0 );
100
    }
101
}
102
103
=head2 build_array
104
  Build a new array containing values of hashes
105
=cut
106
sub build_array {
107
    my ( $array ) = @_;
108
    my ( @r, $total );
109
    for my $hash ( @$array) {
110
        my @line;
111
        for my $cn ( ( @column_names, 'count_actual_state') ) {
112
            if ( grep /$cn/, ( @value_column_names, 'count_actual_state') ) {
113
                $hash->{$cn} //= 0;
114
                if ( exists $total->{$cn} ) {
115
                    $total->{$cn} += $hash->{$cn} if $hash->{$cn};
116
                } else {
117
                    $total->{$cn} = $hash->{$cn};
118
                }
119
            }
120
            push @line, $hash->{$cn};
121
        }
122
        push @r, \@line;
123
    }
124
    return ( $total, \@r );
125
}
126
127
=head2 merge
128
  Merge hashes with the same statistic column names into one
129
=cut
130
sub merge {
131
    my @array = @_;
132
    my @r;
133
    for my $h ( @array ) {
134
        my $exists = 0;
135
        for my $ch ( @r ) {
136
            $exists = 1;
137
            for my $cn ( @statistic_column_names ) {
138
                if ( not $ch->{$cn} ~~ $h->{$cn} ) {
139
                    $exists = 0;
140
                    last;
141
                }
142
            }
143
            if ($exists){
144
                for my $cn ( @value_column_names ) {
145
                    next if not exists $h->{$cn};
146
                    $ch->{$cn} = $h->{$cn} ? $h->{$cn} : 0;
147
                }
148
                last;
149
            }
150
        }
151
152
        if ( not $exists ) {push @r, $h;}
153
    }
154
    return \@r;
155
}

Return to bug 7955