2 # Copyright 2000-2002 Katipo Communications
3 # copyright 2010 BibLibre
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
28 use C4
::Branch
; # GetBranches
29 use C4
::Dates qw
/format_date format_date_in_iso/;
32 my $dbh = C4
::Context
->dbh;
35 # $flagsrequired->{circulation}=1;
36 my ($template, $loggedinuser, $cookie)
37 = get_template_and_user
({template_name
=> "admin/smart-rules.tmpl",
41 flagsrequired
=> {parameters
=> 1},
45 my $type=$input->param('type');
46 my $branch = $input->param('branch') || ( C4
::Branch
::onlymine
() ?
( C4
::Branch
::mybranch
() || '*' ) : '*' );
47 my $op = $input->param('op');
49 if ($op eq 'delete') {
50 my $itemtype = $input->param('itemtype');
51 my $categorycode = $input->param('categorycode');
52 $debug and warn "deleting $1 $2 $branch";
54 my $sth_Idelete = $dbh->prepare("delete from issuingrules where branchcode=? and categorycode=? and itemtype=?");
55 $sth_Idelete->execute($branch, $categorycode, $itemtype);
57 elsif ($op eq 'delete-branch-cat') {
58 my $categorycode = $input->param('categorycode');
60 if ($categorycode eq "*") {
61 my $sth_delete = $dbh->prepare("DELETE FROM default_circ_rules");
62 $sth_delete->execute();
64 my $sth_delete = $dbh->prepare("DELETE FROM default_borrower_circ_rules
65 WHERE categorycode = ?");
66 $sth_delete->execute($categorycode);
68 } elsif ($categorycode eq "*") {
69 my $sth_delete = $dbh->prepare("DELETE FROM default_branch_circ_rules
70 WHERE branchcode = ?");
71 $sth_delete->execute($branch);
73 my $sth_delete = $dbh->prepare("DELETE FROM branch_borrower_circ_rules
75 AND categorycode = ?");
76 $sth_delete->execute($branch, $categorycode);
79 elsif ($op eq 'delete-branch-item') {
80 my $itemtype = $input->param('itemtype');
82 if ($itemtype eq "*") {
83 my $sth_delete = $dbh->prepare("DELETE FROM default_circ_rules");
84 $sth_delete->execute();
86 my $sth_delete = $dbh->prepare("DELETE FROM default_branch_item_rules
88 $sth_delete->execute($itemtype);
90 } elsif ($itemtype eq "*") {
91 my $sth_delete = $dbh->prepare("DELETE FROM default_branch_circ_rules
92 WHERE branchcode = ?");
93 $sth_delete->execute($branch);
95 my $sth_delete = $dbh->prepare("DELETE FROM branch_item_rules
98 $sth_delete->execute($branch, $itemtype);
101 # save the values entered
102 elsif ($op eq 'add') {
103 my $sth_search = $dbh->prepare('SELECT COUNT(*) AS total FROM issuingrules WHERE branchcode=? AND categorycode=? AND itemtype=?');
104 my $sth_insert = $dbh->prepare('INSERT INTO issuingrules (branchcode, categorycode, itemtype, maxissueqty, renewalsallowed, reservesallowed, issuelength, lengthunit, hardduedate, hardduedatecompare, fine, finedays, firstremind, chargeperiod,rentaldiscount) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
105 my $sth_update=$dbh->prepare("UPDATE issuingrules SET fine=?, finedays=?, firstremind=?, chargeperiod=?, maxissueqty=?, renewalsallowed=?, reservesallowed=?, issuelength=?, lengthunit = ?, hardduedate=?, hardduedatecompare=?, rentaldiscount=? WHERE branchcode=? AND categorycode=? AND itemtype=?");
107 my $br = $branch; # branch
108 my $bor = $input->param('categorycode'); # borrower category
109 my $cat = $input->param('itemtype'); # item type
110 my $fine = $input->param('fine');
111 my $finedays = $input->param('finedays');
112 my $firstremind = $input->param('firstremind');
113 my $chargeperiod = $input->param('chargeperiod');
114 my $maxissueqty = $input->param('maxissueqty');
115 my $renewalsallowed = $input->param('renewalsallowed');
116 my $reservesallowed = $input->param('reservesallowed');
117 $maxissueqty =~ s/\s//g;
118 $maxissueqty = undef if $maxissueqty !~ /^\d+/;
119 my $issuelength = $input->param('issuelength');
120 my $lengthunit = $input->param('lengthunit');
121 my $hardduedate = $input->param('hardduedate');
122 $hardduedate = format_date_in_iso
($hardduedate);
123 my $hardduedatecompare = $input->param('hardduedatecompare');
124 my $rentaldiscount = $input->param('rentaldiscount');
125 $debug and warn "Adding $br, $bor, $cat, $fine, $maxissueqty";
127 $sth_search->execute($br,$bor,$cat);
128 my $res = $sth_search->fetchrow_hashref();
130 $sth_update->execute($fine, $finedays,$firstremind, $chargeperiod, $maxissueqty, $renewalsallowed,$reservesallowed, $issuelength,$lengthunit, $hardduedate,$hardduedatecompare,$rentaldiscount, $br,$bor,$cat);
132 $sth_insert->execute($br,$bor,$cat,$maxissueqty,$renewalsallowed,$reservesallowed,$issuelength,$lengthunit,$hardduedate,$hardduedatecompare,$fine,$finedays,$firstremind,$chargeperiod,$rentaldiscount);
135 elsif ($op eq "set-branch-defaults") {
136 my $categorycode = $input->param('categorycode');
137 my $maxissueqty = $input->param('maxissueqty');
138 my $holdallowed = $input->param('holdallowed');
139 my $returnbranch = $input->param('returnbranch');
140 $maxissueqty =~ s/\s//g;
141 $maxissueqty = undef if $maxissueqty !~ /^\d+/;
142 $holdallowed =~ s/\s//g;
143 $holdallowed = undef if $holdallowed !~ /^\d+/;
145 if ($branch eq "*") {
146 my $sth_search = $dbh->prepare("SELECT count(*) AS total
147 FROM default_circ_rules");
148 my $sth_insert = $dbh->prepare("INSERT INTO default_circ_rules
149 (maxissueqty, holdallowed, returnbranch)
151 my $sth_update = $dbh->prepare("UPDATE default_circ_rules
152 SET maxissueqty = ?, holdallowed = ?, returnbranch = ?");
154 $sth_search->execute();
155 my $res = $sth_search->fetchrow_hashref();
157 $sth_update->execute($maxissueqty, $holdallowed, $returnbranch);
159 $sth_insert->execute($maxissueqty, $holdallowed, $returnbranch);
162 my $sth_search = $dbh->prepare("SELECT count(*) AS total
163 FROM default_branch_circ_rules
164 WHERE branchcode = ?");
165 my $sth_insert = $dbh->prepare("INSERT INTO default_branch_circ_rules
166 (branchcode, maxissueqty, holdallowed, returnbranch)
167 VALUES (?, ?, ?, ?)");
168 my $sth_update = $dbh->prepare("UPDATE default_branch_circ_rules
169 SET maxissueqty = ?, holdallowed = ?, returnbranch = ?
170 WHERE branchcode = ?");
171 $sth_search->execute($branch);
172 my $res = $sth_search->fetchrow_hashref();
174 $sth_update->execute($maxissueqty, $holdallowed, $returnbranch, $branch);
176 $sth_insert->execute($branch, $maxissueqty, $holdallowed, $returnbranch);
180 elsif ($op eq "add-branch-cat") {
181 my $categorycode = $input->param('categorycode');
182 my $maxissueqty = $input->param('maxissueqty');
183 $maxissueqty =~ s/\s//g;
184 $maxissueqty = undef if $maxissueqty !~ /^\d+/;
186 if ($branch eq "*") {
187 if ($categorycode eq "*") {
188 my $sth_search = $dbh->prepare("SELECT count(*) AS total
189 FROM default_circ_rules");
190 my $sth_insert = $dbh->prepare("INSERT INTO default_circ_rules
193 my $sth_update = $dbh->prepare("UPDATE default_circ_rules
194 SET maxissueqty = ?");
196 $sth_search->execute();
197 my $res = $sth_search->fetchrow_hashref();
199 $sth_update->execute($maxissueqty);
201 $sth_insert->execute($maxissueqty);
204 my $sth_search = $dbh->prepare("SELECT count(*) AS total
205 FROM default_borrower_circ_rules
206 WHERE categorycode = ?");
207 my $sth_insert = $dbh->prepare("INSERT INTO default_borrower_circ_rules
208 (categorycode, maxissueqty)
210 my $sth_update = $dbh->prepare("UPDATE default_borrower_circ_rules
212 WHERE categorycode = ?");
213 $sth_search->execute($branch);
214 my $res = $sth_search->fetchrow_hashref();
216 $sth_update->execute($maxissueqty, $categorycode);
218 $sth_insert->execute($categorycode, $maxissueqty);
221 } elsif ($categorycode eq "*") {
222 my $sth_search = $dbh->prepare("SELECT count(*) AS total
223 FROM default_branch_circ_rules
224 WHERE branchcode = ?");
225 my $sth_insert = $dbh->prepare("INSERT INTO default_branch_circ_rules
226 (branchcode, maxissueqty)
228 my $sth_update = $dbh->prepare("UPDATE default_branch_circ_rules
230 WHERE branchcode = ?");
231 $sth_search->execute($branch);
232 my $res = $sth_search->fetchrow_hashref();
234 $sth_update->execute($maxissueqty, $branch);
236 $sth_insert->execute($branch, $maxissueqty);
239 my $sth_search = $dbh->prepare("SELECT count(*) AS total
240 FROM branch_borrower_circ_rules
242 AND categorycode = ?");
243 my $sth_insert = $dbh->prepare("INSERT INTO branch_borrower_circ_rules
244 (branchcode, categorycode, maxissueqty)
246 my $sth_update = $dbh->prepare("UPDATE branch_borrower_circ_rules
249 AND categorycode = ?");
251 $sth_search->execute($branch, $categorycode);
252 my $res = $sth_search->fetchrow_hashref();
254 $sth_update->execute($maxissueqty, $branch, $categorycode);
256 $sth_insert->execute($branch, $categorycode, $maxissueqty);
260 elsif ($op eq "add-branch-item") {
261 my $itemtype = $input->param('itemtype');
262 my $holdallowed = $input->param('holdallowed');
263 my $returnbranch = $input->param('returnbranch');
264 $holdallowed =~ s/\s//g;
265 $holdallowed = undef if $holdallowed !~ /^\d+/;
267 if ($branch eq "*") {
268 if ($itemtype eq "*") {
269 my $sth_search = $dbh->prepare("SELECT count(*) AS total
270 FROM default_circ_rules");
271 my $sth_insert = $dbh->prepare("INSERT INTO default_circ_rules
272 (holdallowed, returnbranch)
274 my $sth_update = $dbh->prepare("UPDATE default_circ_rules
275 SET holdallowed = ?, returnbranch = ?");
277 $sth_search->execute();
278 my $res = $sth_search->fetchrow_hashref();
280 $sth_update->execute($holdallowed, $returnbranch);
282 $sth_insert->execute($holdallowed, $returnbranch);
285 my $sth_search = $dbh->prepare("SELECT count(*) AS total
286 FROM default_branch_item_rules
287 WHERE itemtype = ?");
288 my $sth_insert = $dbh->prepare("INSERT INTO default_branch_item_rules
289 (itemtype, holdallowed, returnbranch)
291 my $sth_update = $dbh->prepare("UPDATE default_branch_item_rules
292 SET holdallowed = ?, returnbranch = ?
293 WHERE itemtype = ?");
294 $sth_search->execute($itemtype);
295 my $res = $sth_search->fetchrow_hashref();
297 $sth_update->execute($holdallowed, $returnbranch, $itemtype);
299 $sth_insert->execute($itemtype, $holdallowed, $returnbranch);
302 } elsif ($itemtype eq "*") {
303 my $sth_search = $dbh->prepare("SELECT count(*) AS total
304 FROM default_branch_circ_rules
305 WHERE branchcode = ?");
306 my $sth_insert = $dbh->prepare("INSERT INTO default_branch_circ_rules
307 (branchcode, holdallowed, returnbranch)
309 my $sth_update = $dbh->prepare("UPDATE default_branch_circ_rules
310 SET holdallowed = ?, returnbranch = ?
311 WHERE branchcode = ?");
312 $sth_search->execute($branch);
313 my $res = $sth_search->fetchrow_hashref();
315 $sth_update->execute($holdallowed, $returnbranch, $branch);
317 $sth_insert->execute($branch, $holdallowed, $returnbranch);
320 my $sth_search = $dbh->prepare("SELECT count(*) AS total
321 FROM branch_item_rules
324 my $sth_insert = $dbh->prepare("INSERT INTO branch_item_rules
325 (branchcode, itemtype, holdallowed, returnbranch)
326 VALUES (?, ?, ?, ?)");
327 my $sth_update = $dbh->prepare("UPDATE branch_item_rules
328 SET holdallowed = ?, returnbranch = ?
332 $sth_search->execute($branch, $itemtype);
333 my $res = $sth_search->fetchrow_hashref();
335 $sth_update->execute($holdallowed, $returnbranch, $branch, $itemtype);
337 $sth_insert->execute($branch, $itemtype, $holdallowed, $returnbranch);
342 my $branches = GetBranches
();
344 for my $thisbranch (sort { $branches->{$a}->{branchname
} cmp $branches->{$b}->{branchname
} } keys %$branches) {
346 value
=> $thisbranch,
347 selected
=> $thisbranch eq $branch,
348 branchname
=> $branches->{$thisbranch}->{'branchname'},
352 my $sth=$dbh->prepare("SELECT description,categorycode FROM categories ORDER BY description");
355 while (my $data=$sth->fetchrow_hashref){
356 push @category_loop,$data;
360 $sth=$dbh->prepare("SELECT description,itemtype FROM itemtypes ORDER BY description");
365 while (my $row=$sth->fetchrow_hashref){
366 push @itemtypes,$row;
369 my $sth2 = $dbh->prepare("
370 SELECT issuingrules.*, itemtypes.description AS humanitemtype, categories.description AS humancategorycode
373 ON (itemtypes.itemtype = issuingrules.itemtype)
375 ON (categories.categorycode = issuingrules.categorycode)
376 WHERE issuingrules.branchcode = ?
378 $sth2->execute($branch);
380 while (my $row = $sth2->fetchrow_hashref) {
381 $row->{'current_branch'} ||= $row->{'branchcode'};
382 $row->{'humanitemtype'} ||= $row->{'itemtype'};
383 $row->{'default_humanitemtype'} = 1 if $row->{'humanitemtype'} eq '*';
384 $row->{'humancategorycode'} ||= $row->{'categorycode'};
385 $row->{'default_humancategorycode'} = 1 if $row->{'humancategorycode'} eq '*';
386 $row->{'fine'} = sprintf('%.2f', $row->{'fine'});
387 if ($row->{'hardduedate'} ne '0000-00-00') {
388 $row->{'hardduedate'} = format_date
( $row->{'hardduedate'});
389 $row->{'hardduedatebefore'} = 1 if ($row->{'hardduedatecompare'} == -1);
390 $row->{'hardduedateexact'} = 1 if ($row->{'hardduedatecompare'} == 0);
391 $row->{'hardduedateafter'} = 1 if ($row->{'hardduedatecompare'} == 1);
393 $row->{'hardduedate'} = 0;
395 push @row_loop, $row;
399 my @sorted_row_loop = sort by_category_and_itemtype
@row_loop;
402 if ($branch eq "*") {
403 $sth_branch_cat = $dbh->prepare("
404 SELECT default_borrower_circ_rules.*, categories.description AS humancategorycode
405 FROM default_borrower_circ_rules
406 JOIN categories USING (categorycode)
409 $sth_branch_cat->execute();
411 $sth_branch_cat = $dbh->prepare("
412 SELECT branch_borrower_circ_rules.*, categories.description AS humancategorycode
413 FROM branch_borrower_circ_rules
414 JOIN categories USING (categorycode)
415 WHERE branch_borrower_circ_rules.branchcode = ?
417 $sth_branch_cat->execute($branch);
420 my @branch_cat_rules = ();
421 while (my $row = $sth_branch_cat->fetchrow_hashref) {
422 push @branch_cat_rules, $row;
424 my @sorted_branch_cat_rules = sort { $a->{'humancategorycode'} cmp $b->{'humancategorycode'} } @branch_cat_rules;
426 # note undef maxissueqty so that template can deal with them
427 foreach my $entry (@sorted_branch_cat_rules, @sorted_row_loop) {
428 $entry->{unlimited_maxissueqty
} = 1 unless defined($entry->{maxissueqty
});
431 @sorted_row_loop = sort by_category_and_itemtype
@row_loop;
434 if ($branch eq "*") {
435 $sth_branch_item = $dbh->prepare("
436 SELECT default_branch_item_rules.*, itemtypes.description AS humanitemtype
437 FROM default_branch_item_rules
438 JOIN itemtypes USING (itemtype)
440 $sth_branch_item->execute();
442 $sth_branch_item = $dbh->prepare("
443 SELECT branch_item_rules.*, itemtypes.description AS humanitemtype
444 FROM branch_item_rules
445 JOIN itemtypes USING (itemtype)
446 WHERE branch_item_rules.branchcode = ?
448 $sth_branch_item->execute($branch);
451 my @branch_item_rules = ();
452 while (my $row = $sth_branch_item->fetchrow_hashref) {
453 push @branch_item_rules, $row;
455 my @sorted_branch_item_rules = sort { $a->{'humanitemtype'} cmp $b->{'humanitemtype'} } @branch_item_rules;
457 # note undef holdallowed so that template can deal with them
458 foreach my $entry (@sorted_branch_item_rules) {
459 $entry->{holdallowed_any
} = 1 if($entry->{holdallowed
} == 2);
460 $entry->{holdallowed_same
} = 1 if($entry->{holdallowed
} == 1);
463 $template->param(show_branch_cat_rule_form
=> 1);
464 $template->param(branch_item_rule_loop
=> \
@sorted_branch_item_rules);
465 $template->param(branch_cat_rule_loop
=> \
@sorted_branch_cat_rules);
468 if ($branch eq "*") {
469 $sth_defaults = $dbh->prepare("
471 FROM default_circ_rules
473 $sth_defaults->execute();
475 $sth_defaults = $dbh->prepare("
477 FROM default_branch_circ_rules
480 $sth_defaults->execute($branch);
483 my $defaults = $sth_defaults->fetchrow_hashref;
486 $template->param(default_holdallowed_none
=> 1) if($defaults->{holdallowed
} == 0);
487 $template->param(default_holdallowed_same
=> 1) if($defaults->{holdallowed
} == 1);
488 $template->param(default_holdallowed_any
=> 1) if($defaults->{holdallowed
} == 2);
489 $template->param(default_maxissueqty
=> $defaults->{maxissueqty
});
490 $template->param(default_returnbranch
=> $defaults->{returnbranch
});
493 $template->param(default_rules
=> ($defaults ?
1 : 0));
495 $template->param(categoryloop
=> \
@category_loop,
496 itemtypeloop
=> \
@itemtypes,
497 rules
=> \
@sorted_row_loop,
498 branchloop
=> \
@branchloop,
499 humanbranch
=> ($branch ne '*' ?
$branches->{$branch}->{branchname
} : ''),
500 current_branch
=> $branch,
501 definedbranch
=> scalar(@sorted_row_loop)>0
503 output_html_with_http_headers
$input, $cookie, $template->output;
507 # sort by patron category, then item type, putting
508 # default entries at the bottom
509 sub by_category_and_itemtype
{
510 unless (by_category
($a, $b)) {
511 return by_itemtype
($a, $b);
517 if ($a->{'default_humancategorycode'}) {
518 return ($b->{'default_humancategorycode'} ?
0 : 1);
519 } elsif ($b->{'default_humancategorycode'}) {
522 return $a->{'humancategorycode'} cmp $b->{'humancategorycode'};
528 if ($a->{'default_humanitemtype'}) {
529 return ($b->{'default_humanitemtype'} ?
0 : 1);
530 } elsif ($b->{'default_humanitemtype'}) {
533 return $a->{'humanitemtype'} cmp $b->{'humanitemtype'};