X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=reports%2Fborrowers_stats.pl;h=9bdf37d0dc197da48e38182d4eacf07654bf8095;hb=680e6ce7d1aa53e716ea9fdab9c67e621fb828e2;hp=e2defa9cbc69f8972699f88c848825b8893722dd;hpb=56878f1453d15459d2f7586e3cc5d390d301692d;p=koha-ffzg.git diff --git a/reports/borrowers_stats.pl b/reports/borrowers_stats.pl index e2defa9cbc..9bdf37d0dc 100755 --- a/reports/borrowers_stats.pl +++ b/reports/borrowers_stats.pl @@ -4,33 +4,37 @@ # # 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 Modern::Perl; -use CGI; +use CGI qw ( -utf8 ); use List::MoreUtils qw/uniq/; use C4::Auth; use C4::Context; -use C4::Branch; # GetBranches use C4::Koha; -use C4::Dates; use C4::Acquisition; use C4::Output; use C4::Reports; use C4::Circulation; -use C4::Dates qw/format_date format_date_in_iso/; +use C4::Members::AttributeTypes; + +use Koha::AuthorisedValues; +use Koha::DateUtils; +use Koha::Libraries; +use Koha::Patron::Categories; + use Date::Calc qw( Today Add_Delta_YM @@ -42,29 +46,26 @@ plugin that shows a stats on borrowers =head1 DESCRIPTION -=over 2 - =cut my $input = new CGI; my $do_it=$input->param('do_it'); -my $fullreportname = "reports/borrowers_stats.tmpl"; +my $fullreportname = "reports/borrowers_stats.tt"; my $line = $input->param("Line"); my $column = $input->param("Column"); -my @filters = $input->param("Filter"); -$filters[3]=format_date_in_iso($filters[3]); -$filters[4]=format_date_in_iso($filters[4]); +my @filters = $input->multi_param("Filter"); +$filters[3] = eval { output_pref( { dt => dt_from_string( $filters[3]), dateonly => 1, dateformat => 'iso' } ); } + if ( $filters[3] ); +$filters[4] = eval { output_pref ({ dt => dt_from_string( $filters[4]), dateonly => 1, dateformat => 'iso' } ); } + if ( $filters[4] ); my $digits = $input->param("digits"); -my $period = $input->param("period"); +our $period = $input->param("period"); my $borstat = $input->param("status"); my $borstat1 = $input->param("activity"); my $output = $input->param("output"); my $basename = $input->param("basename"); our $sep = $input->param("sep"); $sep = "\t" if ($sep and $sep eq 'tabulation'); -my $selected_branch; # = $input->param("?"); - -our $branches = GetBranches; my ($template, $borrowernumber, $cookie) = get_template_and_user({template_name => $fullreportname, @@ -116,14 +117,8 @@ if ($do_it) { } else { my $dbh = C4::Context->dbh; my $req; - $template->param( CAT_LOOP => &catcode_aref); - my @branchloop; - foreach (sort {$branches->{$a}->{branchname} cmp $branches->{$b}->{branchname}} keys %$branches) { - my $line = {branchcode => $_, branchname => $branches->{$_}->{branchname} || 'UNKNOWN'}; - $line->{selected} = 'selected' if ($selected_branch and $selected_branch eq $_); - push @branchloop, $line; - } - $template->param(BRANCH_LOOP => \@branchloop); + my $patron_categories = Koha::Patron::Categories->search({}, {order_by => ['description']}); + $template->param( patron_categories => $patron_categories ); $req = $dbh->prepare("SELECT DISTINCTROW zipcode FROM borrowers WHERE zipcode IS NOT NULL AND zipcode <> '' ORDER BY zipcode"); $req->execute; $template->param( ZIP_LOOP => $req->fetchall_arrayref({})); @@ -135,12 +130,7 @@ if ($do_it) { $req->execute; $template->param( SORT2_LOOP => $req->fetchall_arrayref({})); - 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; $template->param( CGIextChoice => $CGIextChoice, @@ -153,20 +143,6 @@ if ($do_it) { } output_html_with_http_headers $input, $cookie, $template->output; -sub catcode_aref() { - my $req = C4::Context->dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description"); - $req->execute; - return $req->fetchall_arrayref({}); -} -sub catcodes_hash() { - my %cathash; - my $catcodes = &catcode_aref; - foreach (@$catcodes) { - $cathash{$_->{categorycode}} = ($_->{description} || 'NO_DESCRIPTION') . " ($_->{categorycode})"; - } - return %cathash; -} - sub calculate { my ($line, $column, $digits, $status, $activity, $filters, $attr_filters) = @_; @@ -180,51 +156,67 @@ sub calculate { # extract parameters my $dbh = C4::Context->dbh; + # check parameters + my @valid_names = qw(categorycode zipcode branchcode sex sort1 sort2); + my @attribute_types = C4::Members::AttributeTypes::GetAttributeTypes; + if ($line =~ /^patron_attr\.(.*)/) { + my $attribute_type = $1; + return unless (grep {$attribute_type eq $_->{code}} @attribute_types); + } else { + return unless (grep /^$line$/, @valid_names); + } + if ($column =~ /^patron_attr\.(.*)/) { + my $attribute_type = $1; + return unless (grep {$attribute_type eq $_->{code}} @attribute_types); + } else { + return unless (grep /^$column$/, @valid_names); + } + return if ($digits and $digits !~ /^\d+$/); + return if ($status and (grep /^$status$/, qw(debarred gonenoaddress lost)) == 0); + return if ($activity and (grep /^$activity$/, qw(active nonactive)) == 0); + # Filters my $linefilter; - given ($line) { - when (/categorycode/) { $linefilter = @$filters[0] } - when (/zipcode/) { $linefilter = @$filters[1] } - when (/branchcode/) { $linefilter = @$filters[2] } - when (/sex/) { $linefilter = @$filters[5] } - when (/sort1/) { $linefilter = @$filters[6] } - when (/sort2/) { $linefilter = @$filters[7] } - when (/^patron_attr\.(.*)$/) { $linefilter = $attr_filters->{$1} } - default { $linefilter = '' } - } + if ( $line =~ /categorycode/ ) { $linefilter = @$filters[0]; } + elsif ( $line =~ /zipcode/ ) { $linefilter = @$filters[1]; } + elsif ( $line =~ /branchcode/ ) { $linefilter = @$filters[2]; } + elsif ( $line =~ /sex/ ) { $linefilter = @$filters[5]; } + elsif ( $line =~ /sort1/ ) { $linefilter = @$filters[6]; } + elsif ( $line =~ /sort2/ ) { $linefilter = @$filters[7]; } + elsif ( $line =~ /^patron_attr\.(.*)$/ ) { $linefilter = $attr_filters->{$1}; } + else { $linefilter = ''; } my $colfilter; - given ($column) { - when (/categorycode/) { $colfilter = @$filters[0] } - when (/zipcode/) { $colfilter = @$filters[1] } - when (/branchcode/) { $colfilter = @$filters[2] } - when (/sex/) { $colfilter = @$filters[5] } - when (/sort1/) { $colfilter = @$filters[6] } - when (/sort2/) { $colfilter = @$filters[7] } - when (/^patron_attr\.(.*)$/) { $colfilter = $attr_filters->{$1} } - default { $colfilter = '' } - } + if ( $column =~ /categorycode/ ) { $colfilter = @$filters[0]; } + elsif ( $column =~ /zipcode/ ) { $colfilter = @$filters[1]; } + elsif ( $column =~ /branchcode/) { $colfilter = @$filters[2]; } + elsif ( $column =~ /sex/) { $colfilter = @$filters[5]; } + elsif ( $column =~ /sort1/) { $colfilter = @$filters[6]; } + elsif ( $column =~ /sort2/) { $colfilter = @$filters[7]; } + elsif ( $column =~ /^patron_attr\.(.*)$/) { $colfilter = $attr_filters->{$1}; } + else { $colfilter = ''; } my @loopfilter; - for (my $i = 0; $i <= scalar @$filters; $i++) { + foreach my $i (0 .. scalar @$filters) { my %cell; if ( @$filters[$i] ) { if ($i == 3 or $i == 4) { - $cell{filter} = format_date(@$filters[$i]); + $cell{filter} = eval { output_pref( { dt => dt_from_string( @$filters[$i] ), dateonly => 1 }); } + if ( @$filters[$i] ); } else { $cell{filter} = @$filters[$i]; } - given ($i) { - when (0) { $cell{crit} = "Cat code" } - when (1) { $cell{crit} = "Zip code" } - when (2) { $cell{crit} = "Branch code" } - when ([3,4]) { $cell{crit} = "Date of birth" } - when (5) { $cell{crit} = "Sex" } - when (6) { $cell{crit} = "Sort1" } - when (7) { $cell{crit} = "Sort2" } - default { $cell{crit} = "Unknown" } - } + if ( $i == 0) { $cell{crit} = "Cat code"; } + elsif ( $i == 1 ) { $cell{crit} = "ZIP/Postal code"; } + elsif ( $i == 2 ) { $cell{crit} = "Branch code"; } + elsif ( $i == 3 || + $i == 4 ) { $cell{crit} = "Date of birth"; } + elsif ( $i == 5 ) { $cell{crit} = "Sex"; } + elsif ( $i == 6 ) { $cell{crit} = "Sort1"; } + elsif ( $i == 7 ) { $cell{crit} = "Sort2"; } + else { $cell{crit} = "Unknown"; } + push @loopfilter, \%cell; } } @@ -237,9 +229,10 @@ sub calculate { } } + my @branchcodes = map { $_->branchcode } Koha::Libraries->search; ($status ) and push @loopfilter,{crit=>"Status", filter=>$status }; ($activity) and push @loopfilter,{crit=>"Activity",filter=>$activity}; - push @loopfilter,{debug=>1, crit=>"Branches",filter=>join(" ", sort keys %$branches)}; + push @loopfilter,{debug=>1, crit=>"Branches",filter=>join(" ", sort @branchcodes)}; push @loopfilter,{debug=>1, crit=>"(line, column)", filter=>"($line,$column)"}; # year of activity my ( $period_year, $period_month, $period_day )=Add_Delta_YM( Today(),-$period, 0); @@ -248,7 +241,7 @@ sub calculate { my $linefield; my $line_attribute_type; - if ($line =~/^patron_attr.(.*)$/) { + if ($line =~/^patron_attr\.(.*)$/) { $line_attribute_type = $1; $line = 'borrower_attributes.attribute'; } @@ -258,9 +251,13 @@ sub calculate { } else { $linefield = $line; } - - my %cathash = ($line eq 'categorycode' or $column eq 'categorycode') ? &catcodes_hash : (); - push @loopfilter, {debug=>1, crit=>"\%cathash", filter=>join(", ", map {$cathash{$_}} sort keys %cathash)}; + my $patron_categories = Koha::Patron::Categories->search({}, {order_by => ['categorycode']}); + push @loopfilter, + { + debug => 1, + crit => "Patron category", + filter => join( ", ", map { $_->categorycode . ' (' . ( $_->description || 'NO_DESCRIPTION' ) . ')' } $patron_categories->as_list ), + }; my $strsth; my @strparams; # bind parameters for the query @@ -288,8 +285,7 @@ sub calculate { my %cell; if ($celvalue) { $cell{rowtitle} = $celvalue; - # $cell{rowtitle_display} = ($linefield eq 'branchcode') ? $branches->{$celvalue}->{branchname} : $celvalue; - $cell{rowtitle_display} = ($cathash{$celvalue} || "$celvalue\*") if ($line eq 'categorycode'); + $cell{rowtitle_display} = ($patron_categories->find($celvalue)->description || "$celvalue\*") if ($line eq 'categorycode'); } $cell{totalrow} = 0; push @loopline, \%cell; @@ -304,11 +300,11 @@ sub calculate { $column = 'borrower_attributes.attribute'; } - if (($column =~/zipcode/) and ($digits)) { - $colfield = "left($column,$digits)"; - } else { - $colfield = $column; - } + if (($column =~/zipcode/) and ($digits)) { + $colfield = "left($column,$digits)"; + } else { + $colfield = $column; + } my $strsth2; my @strparams2; # bind parameters for the query @@ -334,10 +330,10 @@ sub calculate { $sth2->execute(@strparams2); while (my ($celvalue) = $sth2->fetchrow) { my %cell; - if ($celvalue) { + if (defined $celvalue) { $cell{coltitle} = $celvalue; # $cell{coltitle_display} = ($colfield eq 'branchcode') ? $branches->{$celvalue}->{branchname} : $celvalue; - $cell{coltitle_display} = $cathash{$celvalue} if ($column eq 'categorycode'); + $cell{coltitle_display} = $patron_categories->find($celvalue)->description if ($column eq 'categorycode'); } push @loopcol, \%cell; } @@ -352,6 +348,7 @@ sub calculate { my $coltitle = $row->{coltitle} // ''; $table{$rowtitle}->{$coltitle} = 0; } + $row->{rowtitle} ||= ''; $table{$row->{rowtitle}}->{totalrow}=0; $table{$row->{rowtitle}}->{rowtitle_display} = $row->{rowtitle_display}; } @@ -410,7 +407,7 @@ sub calculate { } } $strcalc .= " AND borrowernumber in (select distinct(borrowernumber) from old_issues where issuedate > '" . $newperioddate . "')" if ($activity eq 'active'); - $strcalc .= " AND borrowernumber not in (select distinct(borrowernumber) from old_issues where issuedate > '" . $newperioddate . "')" if ($activity eq 'nonactive'); + $strcalc .= " AND borrowernumber not in (select distinct(borrowernumber) from old_issues where issuedate > '" . $newperioddate . "' AND borrowernumber IS NOT NULL)" if ($activity eq 'nonactive'); $strcalc .= " AND $status='1' " if ($status); $strcalc .= " GROUP BY "; @@ -537,7 +534,8 @@ sub patron_attributes_form { my @attribute_loop; foreach my $class ( sort keys %items_by_class ) { - my $lib = GetAuthorisedValueByCode( 'PA_CLASS', $class ) || $class; + my $av = Koha::AuthorisedValues->search({ category => 'PA_CLASS', authorised_value => $class }); + my $lib = $av->count ? $av->next->lib : $class; push @attribute_loop, { class => $class, items => $items_by_class{$class},