X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=reports%2Fborrowers_out.pl;h=b5271d98a5804d2c3132b0401c1baeaf1e2d17c6;hb=a925b07f7b5c182f2c8050886d58e08ce065eff3;hp=0502dc6676a11546b9816420c4c79235a882cd12;hpb=b49a0a30a15ea416bbf4e01d23d3db1e4e21ea0f;p=koha-ffzg.git diff --git a/reports/borrowers_out.pl b/reports/borrowers_out.pl index 0502dc6676..b5271d98a5 100755 --- a/reports/borrowers_out.pl +++ b/reports/borrowers_out.pl @@ -4,63 +4,56 @@ # # This file is part of Koha. # -# Koha is free software; you can redistribute it and/or modify it under the -# terms of the GNU General Public License as published by the Free Software -# Foundation; either version 2 of the License, or (at your option) any later -# version. +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. # -# Koha is distributed in the hope that it will be useful, but WITHOUT ANY -# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR -# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. # -# You should have received a copy of the GNU General Public License along -# with Koha; if not, write to the Free Software Foundation, Inc., -# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . -use strict; -use warnings; +use Modern::Perl; -use CGI; -use C4::Auth; +use CGI qw ( -utf8 ); +use C4::Auth qw( get_template_and_user ); use C4::Context; -use C4::Koha; -use C4::Output; -use C4::Circulation; -use C4::Reports; -use C4::Members; -use C4::Dates qw/format_date_in_iso/; +use C4::Output qw( output_html_with_http_headers ); +use C4::Reports qw( GetDelimiterChoices ); + +use Koha::Patron::Categories; =head1 NAME -plugin that shows a stats on borrowers +reports/borrowers_out.pl =head1 DESCRIPTION -=over 2 +Plugin that shows a stats on borrowers =cut -my $input = new CGI; +my $input = CGI->new; my $do_it=$input->param('do_it'); -my $fullreportname = "reports/borrowers_out.tmpl"; +my $fullreportname = "reports/borrowers_out.tt"; my $limit = $input->param("Limit"); my $column = $input->param("Criteria"); -my @filters = $input->param("Filter"); -$filters[1] = format_date_in_iso($filters[1]) if $filters[1]; +my @filters = $input->multi_param("Filter"); + my $output = $input->param("output"); my $basename = $input->param("basename"); -our $sep = $input->param("sep") || ''; -$sep = "\t" if ($sep eq 'tabulation'); +our $sep = C4::Context->csv_delimiter(scalar $input->param("sep")); my ($template, $borrowernumber, $cookie) = get_template_and_user({template_name => $fullreportname, query => $input, type => "intranet", - authnotrequired => 0, flagsrequired => {reports => '*'}, - debug => 1, }); $template->param(do_it => $do_it, - DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), ); if ($do_it) { # Displaying results @@ -69,7 +62,7 @@ if ($do_it) { # Printing results to screen $template->param(mainloop => $results); output_html_with_http_headers $input, $cookie, $template->output; - exit(1); + exit; } else { # Printing to a csv file print $input->header(-type => 'application/vnd.sun.xml.calc', @@ -103,37 +96,20 @@ if ($do_it) { print $sep.$col->{totalcol}; } print $sep.@$results[0]->{total}; - exit(1); + exit; } # Displaying choices } else { my $dbh = C4::Context->dbh; - my @values; - my %labels; - my %select; - my $req; - - my $CGIextChoice=CGI::scrolling_list( - -name => 'MIME', - -id => 'MIME', - -values => ['CSV'], # FIXME translation - -size => 1, - -multiple => 0 ); - + + my $CGIextChoice = ( 'CSV' ); # FIXME translation my $CGIsepChoice = GetDelimiterChoices; - - my ($codes,$labels) = GetborCatFromCatType(undef,undef); - my @borcatloop; - foreach my $thisborcat (sort keys %$labels) { - my %row =(value => $thisborcat, - description => $labels->{$thisborcat}, - ); - push @borcatloop, \%row; - } + + my $patron_categories = Koha::Patron::Categories->search_with_library_limits({}, {order_by => ['categorycode']}); $template->param( CGIextChoice => $CGIextChoice, CGIsepChoice => $CGIsepChoice, - borcatloop =>\@borcatloop, + patron_categories => $patron_categories, ); output_html_with_http_headers $input, $cookie, $template->output; } @@ -144,7 +120,6 @@ sub calculate { my @mainloop; my @loopfooter; my @loopcol; - my @loopline; my @looprow; my %globalline; my $grantotal =0; @@ -181,17 +156,19 @@ sub calculate { $colorder .= $column; my $strsth2; - $strsth2 .= "select distinctrow $colfield FROM borrowers LEFT JOIN `old_issues` USING(borrowernumber)"; - if ($colfilter[0]) { + $strsth2 .= "select distinct " . $dbh->quote($colfield) . " FROM borrowers WHERE 1"; + my @query_args; + if ( $colfilter[0] ) { $colfilter[0] =~ s/\*/%/g; - $strsth2 .= " and $column LIKE '$colfilter[0]' " ; + $strsth2 .= " and " . $dbh->quote($column) . "LIKE ?" ; + push @query_args, $colfilter[0]; } - $strsth2 .=" group by $colfield"; - $strsth2 .=" order by $colorder"; + $strsth2 .=" group by " . $dbh->quote($colfield); + $strsth2 .=" order by " . $dbh->quote($colorder); # warn "". $strsth2; my $sth2 = $dbh->prepare( $strsth2 ); - $sth2->execute; + $sth2->execute( @query_args ); while (my ($celvalue) = $sth2->fetchrow) { my %cell; # my %ft; @@ -224,19 +201,35 @@ sub calculate { # Processing calculation $strcalc .= "SELECT CONCAT( borrowers.surname , \"\\t\",borrowers.firstname, \"\\t\", borrowers.cardnumber)"; - $strcalc .= " , $colfield " if ($colfield); + $strcalc .= " , " . $dbh->quote($colfield) if ($colfield); $strcalc .= " FROM borrowers "; $strcalc .= "WHERE 1 "; - @$filters[0]=~ s/\*/%/g if (@$filters[0]); - $strcalc .= " AND borrowers.categorycode like '" . @$filters[0] ."'" if ( @$filters[0] ); - my $strqueryfilter = "SELECT DISTINCT borrowernumber FROM old_issues WHERE borrowernumber IS NOT NULL "; - if (@$filters[1]){ - my $strqueryfilter .= "AND old_issues.timestamp> @$filters[1] "; + if(C4::Context->preference('IndependentBranches') && !C4::Context->IsSuperLibrarian()){ + $strcalc .= "AND branchcode = '".C4::Context->userenv->{branch}."' "; + } + + + my @query_args; + if ( @$filters[0] ) { + @$filters[0]=~ s/\*/%/g; + $strcalc .= " AND borrowers.categorycode like ?"; + push @query_args, @$filters[0]; + } + $strcalc .= " AND NOT EXISTS (SELECT * FROM issues WHERE issues.borrowernumber=borrowers.borrowernumber "; + if ( @$filters[1] ) { + $strcalc .= " AND issues.timestamp > ?"; + push @query_args, @$filters[1]; + } + $strcalc .= ") "; + $strcalc .= " AND NOT EXISTS (SELECT * FROM old_issues WHERE old_issues.borrowernumber=borrowers.borrowernumber "; + if ( @$filters[1] ) { + $strcalc .= " AND old_issues.timestamp > ?"; + push @query_args, @$filters[1]; } - $strcalc .= " AND borrowers.borrowernumber not in ($strqueryfilter)"; + $strcalc .= ") "; $strcalc .= " group by borrowers.borrowernumber"; - $strcalc .= ", $colfield" if ($column); - $strcalc .= " order by $colfield " if ($colfield); + $strcalc .= ", " . $dbh->quote($colfield) if ($column); + $strcalc .= " order by " . $dbh->quote($colfield) if ($colfield); my $max; if ($line) { if (@loopcol) { @@ -246,7 +239,7 @@ sub calculate { } my $dbcalc = $dbh->prepare($strcalc); - $dbcalc->execute; + $dbcalc->execute( @query_args ); # warn "filling table"; my $previous_col; $i=1;