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

(-)a/koha-tmpl/intranet-tmpl/prog/en/includes/reports-menu.inc (-1 lines)
Lines 22-28 Link Here
22
	
22
	
23
	<h5>Inactive</h5>
23
	<h5>Inactive</h5>
24
	<ul>
24
	<ul>
25
		<li><a href="/cgi-bin/koha/reports/borrowers_out.pl">Patrons who haven't checked out</a></li>
26
		<li><a href="/cgi-bin/koha/reports/catalogue_out.pl">Items with no checkouts</a></li>
25
		<li><a href="/cgi-bin/koha/reports/catalogue_out.pl">Items with no checkouts</a></li>
27
	</ul>
26
	</ul>
28
27
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/borrowers_out.tt (-137 lines)
Lines 1-137 Link Here
1
[% INCLUDE 'doc-head-open.inc' %]
2
<title>Koha &rsaquo; Reports &rsaquo; Patrons with no checkouts</title>
3
[% INCLUDE 'doc-head-close.inc' %]
4
[% INCLUDE 'calendar.inc' %]
5
<script type="text/javascript">
6
//<![CDATA[
7
$(document).ready(function(){
8
    $("#to").datepicker({ maxDate: "-1D" });
9
});
10
//]]>
11
</script>
12
</head>
13
<body id="rep_borrowers_out" class="rep">
14
[% INCLUDE 'header.inc' %]
15
[% INCLUDE 'cat-search.inc' %]
16
17
<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> &rsaquo; <a href="/cgi-bin/koha/reports/reports-home.pl">Reports</a> &rsaquo; Patrons with no checkouts</div>
18
19
<div id="doc3" class="yui-t2">
20
   
21
   <div id="bd">
22
	<div id="yui-main">
23
	<div class="yui-b">
24
25
[% IF ( do_it ) %]
26
    [% FOREACH mainloo IN mainloop %]
27
        <h1>Patrons with no checkouts</h1>
28
        [% IF ( mainloo.loopfilter ) %]
29
            <p>Filtered on:</p>
30
            [% FOREACH loopfilte IN mainloo.loopfilter %]
31
                    <p>[% IF ( loopfilte.err ) %]  [% END %] [% loopfilte.crit %] =[% loopfilte.filter %][% IF ( loopfilte.err ) %]  [% END %]</p>
32
            [% END %]
33
        [% END %]
34
        
35
        <table>
36
            <tr>
37
                <th>Num/Patrons</th>
38
                [% FOREACH loopco IN mainloo.loopcol %]
39
                    <th>[% loopco.coltitle %]</th>
40
                [% END %]
41
            </tr>
42
                [% FOREACH loopro IN mainloo.looprow %]
43
		[% UNLESS ( loop.odd ) %]
44
                    <tr class="highlight">
45
		[% ELSE %]
46
		    <tr>
47
		[% END %]
48
                        <td>[% loopro.rowtitle %]</td>
49
                        [% FOREACH loopcel IN loopro.loopcell %]
50
                        <td>[% IF ( loopcel.value ) %][% loopcel.value %][% END %]
51
                            </td>
52
                        [% END %]
53
                    </tr>
54
                [% END %]
55
        </table>
56
    [% END %]
57
[% ELSE %]
58
    <h1>Patrons with no checkouts</h1>
59
    <form method="post" action="/cgi-bin/koha/reports/borrowers_out.pl">
60
    <fieldset class="rows">
61
		<ol>
62
            <li><label for="patroncategory">Patron category: </label> <select name="Filter" id="patroncategory"><option value="" > Any category code</option>
63
    [% FOREACH borcatloo IN borcatloop %]
64
        <option value="[% borcatloo.value %]" >[% borcatloo.description %] </option>  
65
     [% END %] 
66
    </select>
67
</li>
68
    <li><label for="to">Not checked out since: </label> <input size="10" id="to" name="Filter" value="" type="text" />
69
</li>
70
		</ol>
71
	</fieldset>
72
	
73
    <fieldset class="rows">
74
	<legend>Limits</legend>
75
		<ol>
76
			<li><label for="numberlimit">Limit to: </label><select name="Limit" id="numberlimit">
77
                        <option value ="" selected="selected">None</option>
78
                        <option value ="5"> 5</option>
79
                        <option value ="10">10</option>
80
                        <option value ="15">15</option>
81
                        <option value ="20">20</option>
82
                        <option value ="25">25</option>
83
                        <option value ="40">40</option>
84
                        <option value ="50">50</option>
85
                        <option value ="100">100</option>
86
                    </select></li>
87
			<li><label for="criteria">By: </label><select name="Criteria" id="criteria">
88
                        <option value ="" selected="selected">None</option>
89
                        <option value ="categorycode">Patron category</option>
90
                        <option value ="branchcode">Library</option>
91
                    </select></li>
92
		</ol>
93
	</fieldset>
94
	
95
<fieldset class="rows">
96
	<legend>Output</legend>
97
    <ol>
98
        <li>
99
            <label for="outputscreen">To screen into the browser: </label><input type="radio" checked="checked" name="output" id="outputscreen" value="screen" />
100
        </li>
101
        <li>
102
            <label for="outputfile">To a file:</label> <input type="radio" name="output" value="file" id="outputfile" />
103
            <label class="inline" for="basename">Named: </label><input type="text" name="basename" id="basename" value="Export" />
104
            <label class="inline" for="MIME">Into an application </label>
105
            <select name="MIME" id="MIME" size="1">
106
            [% FOREACH value IN CGIextChoice %]
107
                <option value="[% value %]">[% value %]</option>
108
            [% END %]
109
            </select>
110
            <select name="sep" id="sep" size="1">
111
            [% FOREACH value IN CGIsepChoice.values.sort() %]
112
              [% IF ( value == CGIsepChoice.default ) %]
113
                <option value="[% value %]" selected="selected">[% value %]</option>
114
              [% ELSE %]
115
                <option value="[% value %]">[% value %]</option>
116
              [% END %]
117
            [% END %]
118
            </select>
119
        </li>
120
    </ol>
121
	</fieldset>
122
123
	<fieldset class="action">
124
	<input type="submit" value="Submit" />
125
	<input type="hidden" name="report_name" value="[% report_name %]" />
126
	<input type="hidden" name="do_it" value="1" />
127
	</fieldset>
128
	</form>
129
[% END %]
130
131
</div>
132
</div>
133
<div class="yui-b">
134
[% INCLUDE 'reports-menu.inc' %]
135
</div>
136
</div>
137
[% INCLUDE 'intranet-bottom.inc' %]
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt (-1 lines)
Lines 54-60 Link Here
54
	
54
	
55
	<h2>Inactive</h2>
55
	<h2>Inactive</h2>
56
	<ul>
56
	<ul>
57
		<li><a href="/cgi-bin/koha/reports/borrowers_out.pl">Patrons who haven't checked out</a></li>
58
		<li><a href="/cgi-bin/koha/reports/catalogue_out.pl">Items with no checkouts</a></li>
57
		<li><a href="/cgi-bin/koha/reports/catalogue_out.pl">Items with no checkouts</a></li>
59
	</ul>
58
	</ul>
60
	
59
	
(-)a/reports/borrowers_out.pl (-298 lines)
Lines 1-297 Link Here
1
#!/usr/bin/perl
2
3
# Copyright 2000-2002 Katipo Communications
4
#
5
# This file is part of Koha.
6
#
7
# Koha is free software; you can redistribute it and/or modify it
8
# under the terms of the GNU General Public License as published by
9
# the Free Software Foundation; either version 3 of the License, or
10
# (at your option) any later version.
11
#
12
# Koha is distributed in the hope that it will be useful, but
13
# WITHOUT ANY WARRANTY; without even the implied warranty of
14
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15
# GNU General Public License for more details.
16
#
17
# You should have received a copy of the GNU General Public License
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
19
20
use strict;
21
use warnings;
22
23
use CGI qw ( -utf8 );
24
use C4::Auth;
25
use C4::Context;
26
use C4::Koha;
27
use C4::Output;
28
use C4::Circulation;
29
use C4::Reports;
30
use C4::Members;
31
use C4::Dates qw/format_date_in_iso/;
32
33
=head1 NAME
34
35
reports/borrowers_out.pl
36
37
=head1 DESCRIPTION
38
39
Plugin that shows a stats on borrowers
40
41
=cut
42
43
my $input = new CGI;
44
my $do_it=$input->param('do_it');
45
my $fullreportname = "reports/borrowers_out.tt";
46
my $limit = $input->param("Limit");
47
my $column = $input->param("Criteria");
48
my @filters = $input->param("Filter");
49
$filters[1] = format_date_in_iso($filters[1]) if $filters[1];
50
my $output = $input->param("output");
51
my $basename = $input->param("basename");
52
our $sep     = $input->param("sep") || '';
53
$sep = "\t" if ($sep eq 'tabulation');
54
my ($template, $borrowernumber, $cookie)
55
    = get_template_and_user({template_name => $fullreportname,
56
                query => $input,
57
                type => "intranet",
58
                authnotrequired => 0,
59
                flagsrequired => {reports => '*'},
60
                debug => 1,
61
                });
62
$template->param(do_it => $do_it,
63
        );
64
if ($do_it) {
65
# Displaying results
66
    my $results = calculate($limit, $column, \@filters);
67
    if ($output eq "screen"){
68
# Printing results to screen
69
        $template->param(mainloop => $results);
70
        output_html_with_http_headers $input, $cookie, $template->output;
71
        exit;
72
    } else {
73
# Printing to a csv file
74
        print $input->header(-type => 'application/vnd.sun.xml.calc',
75
                            -encoding    => 'utf-8',
76
                            -attachment=>"$basename.csv",
77
                            -filename=>"$basename.csv" );
78
        my $cols = @$results[0]->{loopcol};
79
        my $lines = @$results[0]->{looprow};
80
# header top-right
81
        print "num /". @$results[0]->{column} .$sep;
82
# Other header
83
        foreach my $col ( @$cols ) {
84
            print $col->{coltitle}.$sep;
85
        }
86
        print "Total\n";
87
# Table
88
        foreach my $line ( @$lines ) {
89
            my $x = $line->{loopcell};
90
            print $line->{rowtitle}.$sep;
91
            foreach my $cell (@$x) {
92
                my $cellvalue = defined $cell->{value} ? $cell->{value}.$sep : ''.$sep;
93
                print $cellvalue;
94
            }
95
#            print $line->{totalrow};
96
            print "\n";
97
        }
98
# footer
99
        print "TOTAL";
100
        $cols = @$results[0]->{loopfooter};
101
        foreach my $col ( @$cols ) {
102
            print $sep.$col->{totalcol};
103
        }
104
        print $sep.@$results[0]->{total};
105
        exit;
106
    }
107
# Displaying choices
108
} else {
109
    my $dbh = C4::Context->dbh;
110
    my @values;
111
    my %labels;
112
    my %select;
113
    my $req;
114
    
115
    my $CGIextChoice = ( 'CSV' ); # FIXME translation
116
	my $CGIsepChoice = GetDelimiterChoices;
117
    
118
    my ($codes,$labels) = GetborCatFromCatType(undef,undef);
119
    my @borcatloop;
120
    foreach my $thisborcat (sort keys %$labels) {
121
            my %row =(value => $thisborcat,
122
                                    description => $labels->{$thisborcat},
123
                            );
124
            push @borcatloop, \%row;
125
    }
126
    $template->param(
127
                    CGIextChoice => $CGIextChoice,
128
                    CGIsepChoice => $CGIsepChoice,
129
                    borcatloop =>\@borcatloop,
130
                    );
131
output_html_with_http_headers $input, $cookie, $template->output;
132
}
133
134
135
sub calculate {
136
    my ($line, $column, $filters) = @_;
137
    my @mainloop;
138
    my @loopfooter;
139
    my @loopcol;
140
    my @loopline;
141
    my @looprow;
142
    my %globalline;
143
    my $grantotal =0;
144
# extract parameters
145
    my $dbh = C4::Context->dbh;
146
147
# Filters
148
# Checking filters
149
#
150
    my @loopfilter;
151
    for (my $i=0;$i<=2;$i++) {
152
        my %cell;
153
        if ( @$filters[$i] ) {
154
            if (($i==1) and (@$filters[$i-1])) {
155
                $cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ;
156
            }
157
            $cell{filter} .= @$filters[$i];
158
            $cell{crit} .="Bor Cat" if ($i==0);
159
            $cell{crit} .="Without issues since" if ($i==1);
160
            push @loopfilter, \%cell;
161
        }
162
    }
163
    my $colfield;
164
    my $colorder;
165
    if ($column){
166
        $column = "borrowers.".$column if $column=~/categorycode/ || $column=~/branchcode/;
167
        my @colfilter ;
168
        $colfilter[0] = @$filters[0] if ($column =~ /category/ )  ;
169
    # 	$colfilter[0] = @$filters[11] if ($column =~ /sort2/ ) ;
170
    #warn "filtre col ".$colfilter[0]." ".$colfilter[1];
171
                                                
172
    # loop cols.
173
        $colfield .= $column;
174
        $colorder .= $column;
175
        
176
        my $strsth2;
177
        $strsth2 .= "select distinct $colfield FROM borrowers WHERE 1";
178
        if ($colfilter[0]) {
179
            $colfilter[0] =~ s/\*/%/g;
180
            $strsth2 .= " and $column LIKE '$colfilter[0]' " ;
181
        }
182
        $strsth2 .=" group by $colfield";
183
        $strsth2 .=" order by $colorder";
184
        # warn "". $strsth2;
185
        
186
        my $sth2 = $dbh->prepare( $strsth2 );
187
        $sth2->execute;
188
        while (my ($celvalue) = $sth2->fetchrow) {
189
            my %cell;
190
    #		my %ft;
191
    #		warn "coltitle :".$celvalue;
192
            $cell{coltitle} = $celvalue;
193
    #		$ft{totalcol} = 0;
194
            push @loopcol, \%cell;
195
        }
196
    #	warn "fin des titres colonnes";
197
    }
198
    
199
    my $i=0;
200
#	my @totalcol;
201
    
202
    #Initialization of cell values.....
203
    my @table;
204
    
205
#	warn "init table";
206
    if($line) {
207
        for (my $i=1;$i<=$line;$i++) {
208
            foreach my $col ( @loopcol ) {
209
                $table[$i]->{($col->{coltitle})?$col->{coltitle}:"Global"}=0;
210
            }
211
        }
212
    }
213
214
215
# preparing calculation
216
    my $strcalc ;
217
    
218
# Processing calculation
219
    $strcalc .= "SELECT CONCAT( borrowers.surname , \"\\t\",borrowers.firstname, \"\\t\", borrowers.cardnumber)";
220
    $strcalc .= " , $colfield " if ($colfield);
221
    $strcalc .= " FROM borrowers ";
222
    $strcalc .= "WHERE 1 ";
223
    @$filters[0]=~ s/\*/%/g if (@$filters[0]);
224
    $strcalc .= " AND borrowers.categorycode like '" . @$filters[0] ."'" if ( @$filters[0] );
225
226
    $strcalc .= " AND NOT EXISTS (SELECT * FROM issues WHERE issues.borrowernumber=borrowers.borrowernumber ";
227
    $strcalc .= " AND issues.timestamp> '" . @$filters[1] . "'" if (@$filters[1]);
228
    $strcalc .= ") ";
229
    $strcalc .= " AND NOT EXISTS (SELECT * FROM old_issues WHERE old_issues.borrowernumber=borrowers.borrowernumber ";
230
    $strcalc .= " AND old_issues.timestamp> '" . @$filters[1] . "'" if (@$filters[1]);
231
    $strcalc .= ") ";
232
    $strcalc .= " group by borrowers.borrowernumber";
233
    $strcalc .= ", $colfield" if ($column);
234
    $strcalc .= " order by $colfield " if ($colfield);
235
    my $max;
236
    if ($line) {
237
        if (@loopcol) {
238
            $max = $line*@loopcol;
239
        } else { $max=$line;}
240
        $strcalc .= " LIMIT 0,$max";
241
     } 
242
    
243
    my $dbcalc = $dbh->prepare($strcalc);
244
    $dbcalc->execute;
245
# 	warn "filling table";
246
    my $previous_col;
247
    $i=1;
248
    while (my  @data = $dbcalc->fetchrow) {
249
        my ($row, $col )=@data;
250
        $col = "zzEMPTY" if (!defined($col));
251
        $i=1 if (($previous_col) and not($col eq $previous_col));
252
        $table[$i]->{$col}=$row;
253
#		warn " $i $col $row";
254
        $i++;
255
        $previous_col=$col;
256
    }
257
    
258
    push @loopcol,{coltitle => "Global"} if not($column);
259
    
260
    $max =(($line)?$line:@table -1);
261
    for ($i=1; $i<=$max;$i++) {
262
        my @loopcell;
263
        #@loopcol ensures the order for columns is common with column titles
264
        # and the number matches the number of columns
265
        my $colcount=0;
266
        foreach my $col ( @loopcol ) {
267
            my $value;
268
            if (@loopcol){
269
                $value =$table[$i]->{(($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))?"zzEMPTY":$col->{coltitle}};
270
            } else {
271
                $value =$table[$i]->{"zzEMPTY"};
272
            }
273
            push @loopcell, {value => $value} ;
274
        }
275
        push @looprow,{ 'rowtitle' => $i ,
276
                        'loopcell' => \@loopcell,
277
                    };
278
    }
279
    
280
            
281
282
    # the header of the table
283
    $globalline{loopfilter}=\@loopfilter;
284
    # the core of the table
285
    $globalline{looprow} = \@looprow;
286
    $globalline{loopcol} = \@loopcol;
287
# 	# the foot (totals by borrower type)
288
    $globalline{loopfooter} = \@loopfooter;
289
    $globalline{total}= $grantotal;
290
    $globalline{line} = $line;
291
    $globalline{column} = $column;
292
    push @mainloop,\%globalline;
293
    return \@mainloop;
294
}
295
296
1;
297
__END__
298
- 

Return to bug 14426