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 2 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, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

use strict;
use C4::Auth;
use CGI;
use C4::Context;
use C4::Search;
use C4::Output;
use C4::Koha;
use C4::Members;

use C4::Branch; # GetBranches

=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) = @_;

    $template->param( branchloop => GetBranchesLoop() );
  
    my ($codes,$labels)=GetborCatFromCatType(undef,undef);
    my @borcatloop;
    foreach my $thisborcat (sort keys %$labels) {
        push @borcatloop, {
                          value       => $thisborcat,
                          description => $labels->{$thisborcat},
                          };
    }
    $template->param(loopcategories => \@borcatloop);
    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;