X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=reports%2Fbor_issues_top.pl;h=a41985b7523c6b3a76f4b97dcf0198f02d5a0c52;hb=638786e719fb297ae976061b09a71f3d788416f4;hp=8cc13bec8395c2810ce94dd0e8ba5849aa689a62;hpb=6e7a4d3a3da8db196dddb502c09817dd291f3590;p=koha-ffzg.git diff --git a/reports/bor_issues_top.pl b/reports/bor_issues_top.pl index 8cc13bec83..a41985b752 100755 --- a/reports/bor_issues_top.pl +++ b/reports/bor_issues_top.pl @@ -4,31 +4,34 @@ # # 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 CGI; +use Modern::Perl; + +use CGI qw ( -utf8 ); use C4::Auth; use C4::Output; use C4::Context; -use C4::Branch; # GetBranches use C4::Koha; use C4::Circulation; use C4::Members; use C4::Reports; use C4::Debug; -use C4::Dates qw(format_date format_date_in_iso); + +use Koha::DateUtils; +use Koha::ItemTypes; +use Koha::Patron::Categories; =head1 NAME @@ -36,37 +39,31 @@ plugin that shows a stats on borrowers =head1 DESCRIPTION -=over 2 - =cut -$debug = 1; -$debug and open DEBUG, ">/tmp/bor_issues_top.debug.log"; +$debug and open my $debugfh, '>', '/tmp/bor_issues_top.debug.log'; my $input = new CGI; -my $fullreportname = "reports/bor_issues_top.tmpl"; +my $fullreportname = "reports/bor_issues_top.tt"; my $do_it = $input->param('do_it'); my $limit = $input->param("Limit"); my $column = $input->param("Criteria"); -my @filters = $input->param("Filter"); +my @filters = $input->multi_param("Filter"); foreach ( @filters[0..3] ) { - $_ and $_ = format_date_in_iso($_); + $_ and $_ = eval { output_pref( { dt => dt_from_string ( $_ ), dateonly => 1, dateformat => 'iso' }); }; } my $output = $input->param("output"); my $basename = $input->param("basename"); -# my $mime = $input->param("MIME"); my ($template, $borrowernumber, $cookie) = get_template_and_user({template_name => $fullreportname, query => $input, type => "intranet", - authnotrequired => 0, flagsrequired => {reports => '*'}, debug => 1, }); -our $sep = $input->param("sep"); +our $sep = $input->param("sep") || C4::Context->preference('delimiter') || ','; $sep = "\t" if ($sep eq 'tabulation'); $template->param(do_it => $do_it, - DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), ); if ($do_it) { # Displaying results @@ -102,64 +99,37 @@ if ($do_it) { print join($sep, map {$_->{totalcol}} @$cols); print $sep.@$results[0]->{total}; } - exit(1); + exit; } my $dbh = C4::Context->dbh; -my @values; # here each element returned by map is a hashref, get it? -my @mime = ( map { {type =>$_} } (split /[;:]/,C4::Context->preference("MIME")) ); +my @mime = ( map { {type =>$_} } (split /[;:]/, 'CSV') ); # FIXME translation my $delims = GetDelimiterChoices; -my $branches = GetBranches; -my @branchloop; -foreach (sort keys %$branches) { -# my $selected = 1 if $thisbranch eq $branch; - my %row = ( value => $_, -# selected => $selected, - branchname => $branches->{$_}->{branchname}, - ); - push @branchloop, \%row; -} -my $itemtypes = GetItemTypes; -my @itemtypeloop; -foreach (sort {$itemtypes->{$a}->{description} cmp $itemtypes->{$b}->{description}} keys %$itemtypes) { - my %row = (value => $_, - description => $itemtypes->{$_}->{description}, - ); - push @itemtypeloop, \%row; -} - -my ($codes,$labels) = GetborCatFromCatType(undef,undef); -my @borcatloop; -foreach (sort keys %$labels) { - my %row =(value => $_, - description => $labels->{$_}, - ); - push @borcatloop, \%row; -} - +my $patron_categories = Koha::Patron::Categories->search_limited({}, {order_by => ['categorycode']}); +my $itemtypes = Koha::ItemTypes->search_with_localization; $template->param( mimeloop => \@mime, CGIseplist => $delims, - branchloop => \@branchloop, - itemtypeloop => \@itemtypeloop, - borcatloop => \@borcatloop, + itemtypes => $itemtypes, +patron_categories => $patron_categories, ); output_html_with_http_headers $input, $cookie, $template->output; sub calculate { - my ($line, $column, $filters) = @_; + my ($limit, $column, $filters) = @_; + my @loopcol; - my @loopline; my @looprow; my %globalline; my %columns; my $grantotal =0; my $dbh = C4::Context->dbh; + # Checking filters my @loopfilter; my @cellmap = ( @@ -181,7 +151,7 @@ sub calculate { $cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ; } # format the dates filters, otherwise just fill as is - $cell{filter} .= ($i>=4) ? @$filters[$i] : format_date(@$filters[$i]); + $cell{filter} .= @$filters[$i]; defined ($cellmap[$i]) and $cell{crit} .= $cellmap[$i]; push @loopfilter, \%cell; @@ -209,11 +179,6 @@ sub calculate { } elsif ($column =~ /sort2/ ) { # $colfilter[0] = @$filters[11]; } - # $colfilter[0] = @$filters[7] if ($column =~ /timestamp/ ) ; FIXME This can't be right. - # $colfilter[0] = @$filters[8] if ($column =~ /timestamp/ ) ; FIXME - # $colfilter[0] = @$filters[9] if ($column =~ /timestamp/ ) ; FIXME Only this line would have effect. - - #warn "filtre col ".$colfilter[0]." ".$colfilter[1]; # loop cols. if ($column eq "Day") { @@ -258,60 +223,48 @@ sub calculate { $strsth2 .=" GROUP BY $colfield"; $strsth2 .=" ORDER BY $colorder"; - $debug and print DEBUG "bor_issues_top (old_issues) SQL: $strsth2\n"; + $debug and print $debugfh "bor_issues_top (old_issues) SQL: $strsth2\n"; my $sth2 = $dbh->prepare($strsth2); $sth2->execute; - print DEBUG "rows: ", $sth2->rows, "\n"; + print $debugfh "rows: ", $sth2->rows, "\n"; while (my @row = $sth2->fetchrow) { $columns{($row[0] ||'NULL')}++; push @loopcol, { coltitle => $row[0] || 'NULL' }; } $strsth2 =~ s/old_issues/issues/g; - $debug and print DEBUG "bor_issues_top (issues) SQL: $strsth2\n"; + $debug and print $debugfh "bor_issues_top (issues) SQL: $strsth2\n"; $sth2 = $dbh->prepare($strsth2); $sth2->execute; - $debug and print DEBUG "rows: ", $sth2->rows, "\n"; + $debug and print $debugfh "rows: ", $sth2->rows, "\n"; while (my @row = $sth2->fetchrow) { $columns{($row[0] ||'NULL')}++; push @loopcol, { coltitle => $row[0] || 'NULL' }; } - $debug and print DEBUG "full array: ", Dumper(\%columns), "\n"; + $debug and print $debugfh "full array: ", Dumper(\%columns), "\n"; }else{ $columns{''} = 1; } - #Initialization of cell values..... - my @table; - for (my $i=1;$i<=$line;$i++) { - foreach (keys %columns) { -# warn " init table : $row->{rowtitle} / $_ "; - $table[$i]->{ $_ || "total" }->{'name'}=0; - } - } - my $strcalc ; # Processing average loanperiods - $strcalc .= "SELECT CONCAT(borrowers.surname , \",\\t\",borrowers.firstname), COUNT(*) AS RANK, borrowers.borrowernumber AS ID"; + $strcalc .= "SELECT CONCAT_WS('', borrowers.surname , \",\\t\", borrowers.firstname), COUNT(*) AS RANK, borrowers.borrowernumber AS ID"; $strcalc .= " , $colfield " if ($colfield); $strcalc .= " FROM `old_issues` - LEFT JOIN borrowers ON old_issues.borrowernumber=borrowers.borrowernumber - LEFT JOIN items ON items.itemnumber=old_issues.itemnumber - LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) - WHERE 1"; + LEFT JOIN borrowers USING(borrowernumber) + LEFT JOIN items USING(itemnumber) + LEFT JOIN biblioitems USING(biblioitemnumber) + WHERE old_issues.borrowernumber IS NOT NULL + "; my @filterterms = ( - 'old_issues.timestamp >', - 'old_issues.timestamp <', + 'old_issues.issuedate >', + 'old_issues.issuedate <', 'old_issues.returndate >', 'old_issues.returndate <', 'old_issues.branchcode like', 'biblioitems.itemtype like', 'borrowers.categorycode like', - 'dayname(old_issues.timestamp) like', - 'monthname(old_issues.timestamp) like', - 'monthname(old_issues.timestamp) like', - 'year(old_issues.timestamp) like', ); foreach ((@$filters)[0..9]) { my $term = shift @filterterms; # go through both arrays in step @@ -319,21 +272,16 @@ sub calculate { s/\*/%/g; $strcalc .= " AND $term '$_' "; } - $strcalc .= " GROUP BY borrowers.borrowernumber"; $strcalc .= ", $colfield" if ($column); $strcalc .= " ORDER BY RANK DESC"; $strcalc .= ",$colfield " if ($colfield); -# my $max; -# if (@loopcol) { -# $max = $line*@loopcol; -# } else { $max=$line;} -# $strcalc .= " LIMIT 0,$max"; + $strcalc .= " LIMIT $limit" if ($limit); - $debug and print DEBUG "(old_issues) SQL : $strcalc\n"; + $debug and print $debugfh "(old_issues) SQL : $strcalc\n"; my $dbcalc = $dbh->prepare($strcalc); $dbcalc->execute; - $debug and print DEBUG "rows: ", $dbcalc->rows, "\n"; + $debug and print $debugfh "rows: ", $dbcalc->rows, "\n"; my %patrons = (); # DATA STRUCTURE is going to look like this: # (2253=> {name=>"John Doe", @@ -352,10 +300,10 @@ sub calculate { use Data::Dumper; $strcalc =~ s/old_issues/issues/g; - $debug and print DEBUG "(issues) SQL : $strcalc\n"; + $debug and print $debugfh "(issues) SQL : $strcalc\n"; $dbcalc = $dbh->prepare($strcalc); $dbcalc->execute; - $debug and print DEBUG "rows: ", $dbcalc->rows, "\n"; + $debug and print $debugfh "rows: ", $dbcalc->rows, "\n"; while (my @data = $dbcalc->fetchrow) { my ($row, $rank, $id, $col) = @data; $col = "zzEMPTY" if (!defined($col)); @@ -374,7 +322,7 @@ sub calculate { $patrons{$id}->{total} += $count; } } - $debug and print DEBUG "\n\npatrons: ", Dumper(\%patrons); + $debug and print $debugfh "\n\npatrons: ", Dumper(\%patrons); my $i = 1; my @cols_in_order = sort keys %columns; # if you want to order the columns, do something here @@ -384,6 +332,7 @@ sub calculate { } keys %patrons; foreach my $id (@ranked_ids) { my @loopcell; + foreach my $key (@cols_in_order) { if($column){ push @loopcell, { @@ -403,8 +352,10 @@ sub calculate { 'loopcell' => \@loopcell, 'hilighted' => ($i%2), }; + # use a limit, if a limit is defined + last if $i > $limit and $limit } - + # the header of the table $globalline{loopfilter}=\@loopfilter; # the core of the table @@ -413,11 +364,10 @@ sub calculate { # the foot (totals by borrower type) $globalline{loopfooter} = []; $globalline{total}= $grantotal; # FIXME: useless - $globalline{line} = $line; $globalline{column} = $column; return [\%globalline]; # reference to a 1 element array: that element is a hashref } -$debug and close DEBUG; +$debug and close $debugfh; 1; __END__