View | Details | Raw Unified | Return to bug 19669
Collapse All | Expand All

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/issues_by_borrower_category.tt (-92 lines)
Lines 1-92 Link Here
1
[% USE Branches %]
2
[% INCLUDE 'doc-head-open.inc' %]
3
<title>Koha &rsaquo; Reports &rsaquo; Checkouts by patron category</title>
4
[% INCLUDE 'doc-head-close.inc' %]
5
</head>
6
<body id="rep_issues_by_borrower_category" class="rep">
7
[% INCLUDE 'header.inc' %]
8
[% INCLUDE 'cat-search.inc' %]
9
10
<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> &rsaquo; <a href="/cgi-bin/koha/reports/reports-home.pl">Reports</a> [% IF ( do_it ) %]&rsaquo; <a href="/cgi-bin/koha/reports/manager.pl?report_name=issues_by_borrower_category">Checkouts by patron category</a> &rsaquo; Results[% ELSE %]&rsaquo; Checkouts by patron category[% END %]</div>
11
12
<div id="doc3" class="yui-t2">
13
   
14
   <div id="bd">
15
	<div id="yui-main">
16
	<div class="yui-b">
17
18
[% IF ( do_it ) %]
19
[% FOREACH mainloo IN mainloop %]
20
	<h1>Checkouts by patron category [% IF ( mainloo.borrower_category ) %] for category = [% mainloo.borrower_category %][% END %]</h1>
21
	<table>
22
		<tr>
23
			<th>&nbsp;</th>
24
			[% FOREACH categorycodeloo IN mainloo.categorycodeloop %]
25
				<th>[% categorycodeloo.categorycode %]</th>
26
			[% END %]
27
			<th>TOTAL</th>
28
		</tr>
29
			[% FOREACH loopborrowertyp IN mainloo.loopborrowertype %]
30
				<tr>
31
					[% IF ( loopborrowertyp.hilighted ) %]<td class="hilighted">[% ELSE %]<td>[% END %]
32
					[% loopborrowertyp.itemtype %]</td>
33
					[% FOREACH loopitemtyp IN loopborrowertyp.loopitemtype %]
34
						[% IF ( loopitemtyp.hilighted ) %]<td>[% ELSE %]<td>[% END %]
35
							[% loopitemtyp.issues %]
36
						</td>
37
					[% END %]
38
					[% IF ( loopborrowertyp.hilighted ) %]<td>[% ELSE %]<td>[% END %]
39
						[% loopborrowertyp.totalitemtype %]
40
					</td>
41
				</tr>
42
			[% END %]
43
			<tr>
44
				<th>TOTAL</th>
45
				[% FOREACH loopborrowertota IN mainloo.loopborrowertotal %]
46
					<th>
47
						[% loopborrowertota.issues %]
48
					</th>
49
				[% END %]
50
				<th>[% mainloo.grantotal %]</th>
51
			</tr>
52
	</table>
53
[% END %]
54
[% ELSE %]
55
        <form method="post" action="/cgi-bin/koha/reports/manager.pl">
56
          <fieldset class="rows">
57
            <legend>Checkouts by patron category</legend>
58
            <ol>
59
              <li>
60
                <label for="borrowercategory">Select a borrower category</label>
61
                <select name="value" id="borrowercategory">
62
                  <option value="">Any Category code</option>
63
                [%- FOREACH patron_category IN patron_categories %]
64
                  <option value="[% patron_category.categorycode %]" >[% patron_category.description %]</option>
65
                [%- END %]
66
                </select>
67
              </li>
68
              <li>
69
                <label for="branch">Select a library:</label>
70
                <select name="value" size="1" id="branch">
71
                  <option value=""></option>
72
                [%- FOREACH l IN Branches.all() %]
73
                  <option value="[% l.branchcode %]">[% l.branchname %]</option>
74
                [%- END %]
75
               </select>
76
              </li>
77
            </ol>
78
          </fieldset>
79
          <fieldset class="action"><input type="submit" value="Submit" />
80
            <input type="hidden" name="report_name" value="[% report_name %]" />
81
            <input type="hidden" name="do_it" value="1" />
82
          </fieldset>
83
        </form>
84
[% END %]
85
86
</div>
87
</div>
88
<div class="yui-b">
89
[% INCLUDE 'reports-menu.inc' %]
90
</div>
91
</div>
92
[% INCLUDE 'intranet-bottom.inc' %]
(-)a/reports/issues_by_borrower_category.plugin (-219 lines)
Lines 1-218 Link Here
1
#!/usr/bin/perl
2
3
4
# Copyright 2000-2002 Katipo Communications
5
#
6
# This file is part of Koha.
7
#
8
# Koha is free software; you can redistribute it and/or modify it
9
# under the terms of the GNU General Public License as published by
10
# the Free Software Foundation; either version 3 of the License, or
11
# (at your option) any later version.
12
#
13
# Koha is distributed in the hope that it will be useful, but
14
# WITHOUT ANY WARRANTY; without even the implied warranty of
15
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16
# GNU General Public License for more details.
17
#
18
# You should have received a copy of the GNU General Public License
19
# along with Koha; if not, see <http://www.gnu.org/licenses>.
20
21
use strict;
22
use C4::Auth;
23
use CGI qw ( -utf8 );
24
use C4::Context;
25
use C4::Search;
26
use C4::Output;
27
use C4::Koha;
28
use C4::Members;
29
30
use Koha::Patron::Categories;
31
32
=head1 NAME
33
34
plugin that shows a table with issues for categories and borrower
35
36
=head1 DESCRIPTION
37
38
this result is quite complex to build...
39
the 2D array contains :
40
* item types on lines
41
* borrowers types on rows
42
43
If no issues are done, the array must be filled by 0 anyway.
44
So, the script works as this :
45
1- parse the itemtype table to get itemtype descriptions and set itemtype total to 0
46
2- for each borrower category :
47
** create an array with total = 0 for each itemtype defined in 1
48
** calculate the total for each itemtype (SQL request)
49
The big hash has the following structure :
50
$itemtypes{itemtype}
51
	->{results}
52
		->{borrowercategorycode} => the total of issues for each cell of the table.
53
	->{total} => the total for the itemtype
54
	->{description} => the itemtype description
55
56
the borrowertype hash contains description and total for each borrowercategory.
57
58
the hashes are then translated to hash / arrays to be returned to manager.pl & send to the template
59
60
=over2
61
62
=cut
63
64
sub set_parameters {
65
    my ($template) = @_;
66
67
    my $patron_categories = Koha::Patron::Categories->search_limited({}, {order_by => ['categorycode']});
68
    $template->param( patron_categories => $patron_categories );
69
    return $template;
70
}
71
72
sub calculate {
73
	my ($parameters) = @_;
74
	my @results =();
75
# extract parameters
76
	my $borrower_category = @$parameters[0];
77
	my $branch = @$parameters[1];
78
	my $dbh = C4::Context->dbh;
79
# build the SQL query & execute it
80
81
# 1st, loop every itemtypes.
82
	my $sth = $dbh->prepare("select itemtype,description from itemtypes");
83
	$sth->execute;
84
	my %itemtypes;
85
	while (my ($itemtype,$description) = $sth->fetchrow) {
86
		$itemtypes{$itemtype}->{description} = $description;
87
		$itemtypes{$itemtype}->{total} = 0;
88
	}
89
# now, parse each category. Before filling the result array, fill it with 0 to have every itemtype column.
90
	my $strsth="SELECT itemtype, count( * )
91
				FROM issues, borrowers, biblioitems, items
92
				WHERE issues.borrowernumber = borrowers.borrowernumber 
93
					AND items.itemnumber = issues.itemnumber 
94
					AND biblioitems.biblionumber = items.biblionumber 
95
					AND borrowers.categorycode = ?";
96
	$strsth.= " AND borrowers.branchcode = ".$dbh->quote($branch) if ($branch);
97
	$strsth .= " GROUP BY biblioitems.itemtype";
98
	my $sth = $dbh->prepare($strsth);
99
	my $sthcategories = $dbh->prepare("select categorycode,description from categories");
100
	$sthcategories->execute;
101
	my %borrowertype;
102
	my @categorycodeloop;
103
	my $categorycode;
104
	my $description;
105
	my $borrower_categorycode =0;
106
	my @mainloop;
107
	my @itemtypeloop;
108
	my @loopborrowertype;
109
	my @loopborrowertotal;
110
	my %globalline;
111
	my $hilighted=-1;
112
	my $grantotal =0;
113
	#If no Borrower-category selected....
114
	# Print all 
115
	if (!$borrower_category) {
116
		while ( ($categorycode,$description) = $sthcategories->fetchrow) {
117
			$borrowertype{$categorycode}->{description} = $description;
118
			$borrowertype{$categorycode}->{total} = 0;
119
			my %categorycode;
120
			$categorycode{categorycode} = $description;
121
			push @categorycodeloop,\%categorycode;
122
			foreach my $itemtype (keys %itemtypes) {
123
				$itemtypes{$itemtype}->{results}->{$categorycode} = 0;
124
			}
125
			$sth->execute($categorycode);
126
			while (my ($itemtype, $total) = $sth->fetchrow) {
127
				$itemtypes{$itemtype}->{results}->{$categorycode} = $total;
128
				$borrowertype{$categorycode}->{total} += $total;
129
				$itemtypes{$itemtype}->{total} += $total;
130
				$grantotal += $total;
131
			}
132
		}
133
		# build the result
134
		foreach my $itemtype (keys %itemtypes) {
135
			my @loopitemtype;
136
			$sthcategories->execute;
137
			while (($categorycode,$description) =  $sthcategories->fetchrow ) {
138
				my %cell;
139
				$cell{issues} = $itemtypes{$itemtype}->{results}->{$categorycode};
140
				#printf stderr "%s	",$categorycode;
141
				push @loopitemtype,\%cell;
142
			}
143
			#printf stderr "\n";
144
			my %line;
145
			$line{loopitemtype} = \@loopitemtype;
146
			if ($itemtypes{$itemtype}->{description}) {
147
				$line{itemtype} = $itemtypes{$itemtype}->{description};
148
			} else {
149
				$line{itemtype} = "$itemtype (no entry in itemtype table)";
150
			}
151
			$line{hilighted} = 1 if $hilighted eq 1;
152
			$line{totalitemtype} = $itemtypes{$itemtype}->{total};
153
			$hilighted = -$hilighted;
154
			push @loopborrowertype, \%line;
155
		}
156
		$sthcategories->execute;
157
		while (($categorycode,$description) =  $sthcategories->fetchrow ) {
158
			my %line;
159
			$line{issues} = $borrowertype{$categorycode}->{total};
160
			push @loopborrowertotal, \%line;
161
		}
162
	} else {
163
		# A Borrower_category has been selected
164
		# extracting corresponding data
165
		$borrowertype{$categorycode}->{description} = $borrower_category;
166
		$borrowertype{$categorycode}->{total} = 0;
167
		while (($categorycode,$description) = $sthcategories->fetchrow) {
168
			if ($description =~ /$borrower_category/ ) {
169
				$borrower_categorycode = $categorycode;
170
				my %cc;
171
				$cc{categorycode} = $description;
172
				push @categorycodeloop,\%cc;
173
				foreach my $itemtype (keys %itemtypes) {
174
					$itemtypes{$itemtype}->{results}->{$categorycode} = 0;
175
				}
176
				$sth->execute($categorycode);
177
				while (my ($itemtype, $total) = $sth->fetchrow) {
178
					$itemtypes{$itemtype}->{results}->{$categorycode} = $total;
179
					$borrowertype{$categorycode}->{total} += $total;
180
					$itemtypes{$itemtype}->{total} += $total;
181
					$grantotal +=$total;
182
				}
183
			}
184
		}
185
		# build the result
186
		foreach my $itemtype (keys %itemtypes) {
187
			my @loopitemtype;
188
			my %cell;
189
			$cell{issues}=$itemtypes{$itemtype}->{results}->{$borrower_categorycode};
190
			push @loopitemtype, \%cell;
191
			my %line;
192
			$line{loopitemtype} = \@loopitemtype;
193
			if ($itemtypes{$itemtype}->{description}) {
194
				$line{itemtype} = $itemtypes{$itemtype}->{description};
195
			} else {
196
				$line{itemtype} = "$itemtype (no entry in itemtype table)";
197
			}
198
			$line{hilighted} = 1 if $hilighted eq 1;
199
			$line{totalitemtype} = $itemtypes{$itemtype}->{total};
200
			$hilighted = -$hilighted;
201
			push @loopborrowertype, \%line;
202
		}
203
		my %cell;
204
		$cell{issues} = $borrowertype{$borrower_categorycode}->{total};
205
		push @loopborrowertotal, \%cell;
206
	}
207
	# the header of the table
208
	$globalline{loopborrowertype} = \@loopborrowertype;
209
	# the core of the table
210
	$globalline{categorycodeloop} = \@categorycodeloop;
211
	# the foot (totals by borrower type)
212
	$globalline{loopborrowertotal} = \@loopborrowertotal;
213
	$globalline{grantotal}= $grantotal;
214
	push @mainloop,\%globalline;
215
	return \@mainloop;
216
}
217
218
1;
219
- 

Return to bug 19669