Bug 14637: Fix add patron category under MySQL 5.7

[koha.git] / reports / issues_by_borrower_category.plugin

#!/usr/bin/perl


# Copyright 2000-2002 Katipo Communications
#
# 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 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.
#
# You should have received a copy of the GNU General Public License
# along with Koha; if not, see <http://www.gnu.org/licenses>.

use strict;
use C4::Auth;
use CGI qw ( -utf8 );
use C4::Context;
use C4::Search;
use C4::Output;
use C4::Koha;
use C4::Members;

use Koha::Patron::Categories;

=head1 NAME

plugin that shows a table with issues for categories and borrower

=head1 DESCRIPTION

this result is quite complex to build...
the 2D array contains :
* item types on lines
* borrowers types on rows

If no issues are done, the array must be filled by 0 anyway.
So, the script works as this :
1- parse the itemtype table to get itemtype descriptions and set itemtype total to 0
2- for each borrower category :
** create an array with total = 0 for each itemtype defined in 1
** calculate the total for each itemtype (SQL request)
The big hash has the following structure :
$itemtypes{itemtype}
        ->{results}
                ->{borrowercategorycode} => the total of issues for each cell of the table.
        ->{total} => the total for the itemtype
        ->{description} => the itemtype description

the borrowertype hash contains description and total for each borrowercategory.

the hashes are then translated to hash / arrays to be returned to manager.pl & send to the template

=over2

=cut

sub set_parameters {
    my ($template) = @_;

    my $patron_categories = Koha::Patron::Categories->search_limited({}, {order_by => ['categorycode']});
    $template->param( patron_categories => $patron_categories );
    return $template;
}

sub calculate {
        my ($parameters) = @_;
        my @results =();
# extract parameters
        my $borrower_category = @$parameters[0];
        my $branch = @$parameters[1];
        my $dbh = C4::Context->dbh;
# build the SQL query & execute it

# 1st, loop every itemtypes.
        my $sth = $dbh->prepare("select itemtype,description from itemtypes");
        $sth->execute;
        my %itemtypes;
        while (my ($itemtype,$description) = $sth->fetchrow) {
                $itemtypes{$itemtype}->{description} = $description;
                $itemtypes{$itemtype}->{total} = 0;
        }
# now, parse each category. Before filling the result array, fill it with 0 to have every itemtype column.
        my $strsth="SELECT itemtype, count( * )
                                FROM issues, borrowers, biblioitems, items
                                WHERE issues.borrowernumber = borrowers.borrowernumber 
                                        AND items.itemnumber = issues.itemnumber 
                                        AND biblioitems.biblionumber = items.biblionumber 
                                        AND borrowers.categorycode = ?";
        $strsth.= " AND borrowers.branchcode = ".$dbh->quote($branch) if ($branch);
        $strsth .= " GROUP BY biblioitems.itemtype";
        my $sth = $dbh->prepare($strsth);
        my $sthcategories = $dbh->prepare("select categorycode,description from categories");
        $sthcategories->execute;
        my %borrowertype;
        my @categorycodeloop;
        my $categorycode;
        my $description;
        my $borrower_categorycode =0;
        my @mainloop;
        my @itemtypeloop;
        my @loopborrowertype;
        my @loopborrowertotal;
        my %globalline;
        my $hilighted=-1;
        my $grantotal =0;
        #If no Borrower-category selected....
        # Print all 
        if (!$borrower_category) {
                while ( ($categorycode,$description) = $sthcategories->fetchrow) {
                        $borrowertype{$categorycode}->{description} = $description;
                        $borrowertype{$categorycode}->{total} = 0;
                        my %categorycode;
                        $categorycode{categorycode} = $description;
                        push @categorycodeloop,\%categorycode;
                        foreach my $itemtype (keys %itemtypes) {
                                $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
                        }
                        $sth->execute($categorycode);
                        while (my ($itemtype, $total) = $sth->fetchrow) {
                                $itemtypes{$itemtype}->{results}->{$categorycode} = $total;
                                $borrowertype{$categorycode}->{total} += $total;
                                $itemtypes{$itemtype}->{total} += $total;
                                $grantotal += $total;
                        }
                }
                # build the result
                foreach my $itemtype (keys %itemtypes) {
                        my @loopitemtype;
                        $sthcategories->execute;
                        while (($categorycode,$description) =  $sthcategories->fetchrow ) {
                                my %cell;
                                $cell{issues} = $itemtypes{$itemtype}->{results}->{$categorycode};
                                #printf stderr "%s      ",$categorycode;
                                push @loopitemtype,\%cell;
                        }
                        #printf stderr "\n";
                        my %line;
                        $line{loopitemtype} = \@loopitemtype;
                        if ($itemtypes{$itemtype}->{description}) {
                                $line{itemtype} = $itemtypes{$itemtype}->{description};
                        } else {
                                $line{itemtype} = "$itemtype (no entry in itemtype table)";
                        }
                        $line{hilighted} = 1 if $hilighted eq 1;
                        $line{totalitemtype} = $itemtypes{$itemtype}->{total};
                        $hilighted = -$hilighted;
                        push @loopborrowertype, \%line;
                }
                $sthcategories->execute;
                while (($categorycode,$description) =  $sthcategories->fetchrow ) {
                        my %line;
                        $line{issues} = $borrowertype{$categorycode}->{total};
                        push @loopborrowertotal, \%line;
                }
        } else {
                # A Borrower_category has been selected
                # extracting corresponding data
                $borrowertype{$categorycode}->{description} = $borrower_category;
                $borrowertype{$categorycode}->{total} = 0;
                while (($categorycode,$description) = $sthcategories->fetchrow) {
                        if ($description =~ /$borrower_category/ ) {
                                $borrower_categorycode = $categorycode;
                                my %cc;
                                $cc{categorycode} = $description;
                                push @categorycodeloop,\%cc;
                                foreach my $itemtype (keys %itemtypes) {
                                        $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
                                }
                                $sth->execute($categorycode);
                                while (my ($itemtype, $total) = $sth->fetchrow) {
                                        $itemtypes{$itemtype}->{results}->{$categorycode} = $total;
                                        $borrowertype{$categorycode}->{total} += $total;
                                        $itemtypes{$itemtype}->{total} += $total;
                                        $grantotal +=$total;
                                }
                        }
                }
                # build the result
                foreach my $itemtype (keys %itemtypes) {
                        my @loopitemtype;
                        my %cell;
                        $cell{issues}=$itemtypes{$itemtype}->{results}->{$borrower_categorycode};
                        push @loopitemtype, \%cell;
                        my %line;
                        $line{loopitemtype} = \@loopitemtype;
                        if ($itemtypes{$itemtype}->{description}) {
                                $line{itemtype} = $itemtypes{$itemtype}->{description};
                        } else {
                                $line{itemtype} = "$itemtype (no entry in itemtype table)";
                        }
                        $line{hilighted} = 1 if $hilighted eq 1;
                        $line{totalitemtype} = $itemtypes{$itemtype}->{total};
                        $hilighted = -$hilighted;
                        push @loopborrowertype, \%line;
                }
                my %cell;
                $cell{issues} = $borrowertype{$borrower_categorycode}->{total};
                push @loopborrowertotal, \%cell;
        }
        # the header of the table
        $globalline{loopborrowertype} = \@loopborrowertype;
        # the core of the table
        $globalline{categorycodeloop} = \@categorycodeloop;
        # the foot (totals by borrower type)
        $globalline{loopborrowertotal} = \@loopborrowertotal;
        $globalline{grantotal}= $grantotal;
        push @mainloop,\%globalline;
        return \@mainloop;
}

1;