admin/smart-rules.pl

   1 #!/usr/bin/perl
   2 # vim: et ts=4 sw=4
   3 # Copyright 2000-2002 Katipo Communications
   4 #
   5 # This file is part of Koha.
   6 #
   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
  10 # version.
  11 #
  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.
  15 #
  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.
  19
  20 use strict;
  21 #use warnings; FIXME - Bug 2505
  22 use CGI;
  23 use C4::Context;
  24 use C4::Output;
  25 use C4::Auth;
  26 use C4::Koha;
  27 use C4::Debug;
  28 use C4::Branch; # GetBranches
  29
  30 my $input = new CGI;
  31 my $dbh = C4::Context->dbh;
  32
  33 # my $flagsrequired;
  34 # $flagsrequired->{circulation}=1;
  35 my ($template, $loggedinuser, $cookie)
  36     = get_template_and_user({template_name => "admin/smart-rules.tmpl",
  37                             query => $input,
  38                             type => "intranet",
  39                             authnotrequired => 0,
  40                             flagsrequired => {parameters => 1},
  41                             debug => 1,
  42                             });
  43
  44 my $type=$input->param('type');
  45 my $branch = $input->param('branch') || ( C4::Branch::onlymine() ? ( C4::Branch::mybranch() || '*' ) : '*' );
  46 my $op = $input->param('op');
  47
  48 if ($op eq 'delete') {
  49     my $itemtype     = $input->param('itemtype');
  50     my $categorycode = $input->param('categorycode');
  51     $debug and warn "deleting $1 $2 $branch";
  52
  53     my $sth_Idelete = $dbh->prepare("delete from issuingrules where branchcode=? and categorycode=? and itemtype=?");
  54     $sth_Idelete->execute($branch, $categorycode, $itemtype);
  55 }
  56 elsif ($op eq 'delete-branch-cat') {
  57     my $categorycode  = $input->param('categorycode');
  58     if ($branch eq "*") {
  59         if ($categorycode eq "*") {
  60             my $sth_delete = $dbh->prepare("DELETE FROM default_circ_rules");
  61             $sth_delete->execute();
  62         } else {
  63             my $sth_delete = $dbh->prepare("DELETE FROM default_borrower_circ_rules
  64                                             WHERE categorycode = ?");
  65             $sth_delete->execute($categorycode);
  66         }
  67     } elsif ($categorycode eq "*") {
  68         my $sth_delete = $dbh->prepare("DELETE FROM default_branch_circ_rules
  69                                         WHERE branchcode = ?");
  70         $sth_delete->execute($branch);
  71     } else {
  72         my $sth_delete = $dbh->prepare("DELETE FROM branch_borrower_circ_rules
  73                                         WHERE branchcode = ?
  74                                         AND categorycode = ?");
  75         $sth_delete->execute($branch, $categorycode);
  76     }
  77 }
  78 elsif ($op eq 'delete-branch-item') {
  79     my $itemtype  = $input->param('itemtype');
  80     if ($branch eq "*") {
  81         if ($itemtype eq "*") {
  82             my $sth_delete = $dbh->prepare("DELETE FROM default_circ_rules");
  83             $sth_delete->execute();
  84         } else {
  85             my $sth_delete = $dbh->prepare("DELETE FROM default_branch_item_rules
  86                                             WHERE itemtype = ?");
  87             $sth_delete->execute($itemtype);
  88         }
  89     } elsif ($itemtype eq "*") {
  90         my $sth_delete = $dbh->prepare("DELETE FROM default_branch_circ_rules
  91                                         WHERE branchcode = ?");
  92         $sth_delete->execute($branch);
  93     } else {
  94         my $sth_delete = $dbh->prepare("DELETE FROM branch_item_rules
  95                                         WHERE branchcode = ?
  96                                         AND itemtype = ?");
  97         $sth_delete->execute($branch, $itemtype);
  98     }
  99 }
 100 # save the values entered
 101 elsif ($op eq 'add') {
 102     my $sth_search = $dbh->prepare("SELECT COUNT(*) AS total FROM issuingrules WHERE branchcode=? AND categorycode=? AND itemtype=?");
 103     my $sth_insert = $dbh->prepare("INSERT INTO issuingrules (branchcode, categorycode, itemtype, maxissueqty, renewalsallowed, reservesallowed, issuelength, fine, finedays, firstremind, chargeperiod,rentaldiscount) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");
 104     my $sth_update=$dbh->prepare("UPDATE issuingrules SET fine=?, finedays=?, firstremind=?, chargeperiod=?, maxissueqty=?, renewalsallowed=?, reservesallowed=?, issuelength=?, rentaldiscount=?  WHERE branchcode=? AND categorycode=? AND itemtype=?");
 105
 106     my $br = $branch; # branch
 107     my $bor  = $input->param('categorycode'); # borrower category
 108     my $cat  = $input->param('itemtype');     # item type
 109     my $fine = $input->param('fine');
 110     my $finedays     = $input->param('finedays');
 111     my $firstremind  = $input->param('firstremind');
 112     my $chargeperiod = $input->param('chargeperiod');
 113     my $maxissueqty  = $input->param('maxissueqty');
 114     my $renewalsallowed  = $input->param('renewalsallowed');
 115     my $reservesallowed  = $input->param('reservesallowed');
 116     $maxissueqty =~ s/\s//g;
 117     $maxissueqty = undef if $maxissueqty !~ /^\d+/;
 118     my $issuelength  = $input->param('issuelength');
 119     my $rentaldiscount = $input->param('rentaldiscount');
 120     $debug and warn "Adding $br, $bor, $cat, $fine, $maxissueqty";
 121
 122     $sth_search->execute($br,$bor,$cat);
 123     my $res = $sth_search->fetchrow_hashref();
 124     if ($res->{total}) {
 125         $sth_update->execute($fine, $finedays,$firstremind, $chargeperiod, $maxissueqty, $renewalsallowed,$reservesallowed, $issuelength,$rentaldiscount, $br,$bor,$cat);
 126     } else {
 127         $sth_insert->execute($br,$bor,$cat,$maxissueqty,$renewalsallowed,$reservesallowed,$issuelength,$fine,$finedays,$firstremind,$chargeperiod,$rentaldiscount);
 128     }
 129 }
 130 elsif ($op eq "set-branch-defaults") {
 131     my $categorycode  = $input->param('categorycode');
 132     my $maxissueqty   = $input->param('maxissueqty');
 133     my $holdallowed   = $input->param('holdallowed');
 134     $maxissueqty =~ s/\s//g;
 135     $maxissueqty = undef if $maxissueqty !~ /^\d+/;
 136     $holdallowed =~ s/\s//g;
 137     $holdallowed = undef if $holdallowed !~ /^\d+/;
 138
 139     if ($branch eq "*") {
 140         my $sth_search = $dbh->prepare("SELECT count(*) AS total
 141                                         FROM default_circ_rules");
 142         my $sth_insert = $dbh->prepare("INSERT INTO default_circ_rules
 143                                         (maxissueqty, holdallowed)
 144                                         VALUES (?, ?)");
 145         my $sth_update = $dbh->prepare("UPDATE default_circ_rules
 146                                         SET maxissueqty = ?, holdallowed = ?");
 147
 148         $sth_search->execute();
 149         my $res = $sth_search->fetchrow_hashref();
 150         if ($res->{total}) {
 151             $sth_update->execute($maxissueqty, $holdallowed);
 152         } else {
 153             $sth_insert->execute($maxissueqty, $holdallowed);
 154         }
 155     } else {
 156         my $sth_search = $dbh->prepare("SELECT count(*) AS total
 157                                         FROM default_branch_circ_rules
 158                                         WHERE branchcode = ?");
 159         my $sth_insert = $dbh->prepare("INSERT INTO default_branch_circ_rules
 160                                         (branchcode, maxissueqty, holdallowed)
 161                                         VALUES (?, ?, ?)");
 162         my $sth_update = $dbh->prepare("UPDATE default_branch_circ_rules
 163                                         SET maxissueqty = ?, holdallowed = ?
 164                                         WHERE branchcode = ?");
 165         $sth_search->execute($branch);
 166         my $res = $sth_search->fetchrow_hashref();
 167         if ($res->{total}) {
 168             $sth_update->execute($maxissueqty, $holdallowed, $branch);
 169         } else {
 170             $sth_insert->execute($branch, $maxissueqty, $holdallowed);
 171         }
 172     }
 173 }
 174 elsif ($op eq "add-branch-cat") {
 175     my $categorycode  = $input->param('categorycode');
 176     my $maxissueqty   = $input->param('maxissueqty');
 177     $maxissueqty =~ s/\s//g;
 178     $maxissueqty = undef if $maxissueqty !~ /^\d+/;
 179
 180     if ($branch eq "*") {
 181         if ($categorycode eq "*") {
 182             my $sth_search = $dbh->prepare("SELECT count(*) AS total
 183                                             FROM default_circ_rules");
 184             my $sth_insert = $dbh->prepare("INSERT INTO default_circ_rules
 185                                             (maxissueqty)
 186                                             VALUES (?)");
 187             my $sth_update = $dbh->prepare("UPDATE default_circ_rules
 188                                             SET maxissueqty = ?");
 189
 190             $sth_search->execute();
 191             my $res = $sth_search->fetchrow_hashref();
 192             if ($res->{total}) {
 193                 $sth_update->execute($maxissueqty);
 194             } else {
 195                 $sth_insert->execute($maxissueqty);
 196             }
 197         } else {
 198             my $sth_search = $dbh->prepare("SELECT count(*) AS total
 199                                             FROM default_borrower_circ_rules
 200                                             WHERE categorycode = ?");
 201             my $sth_insert = $dbh->prepare("INSERT INTO default_borrower_circ_rules
 202                                             (categorycode, maxissueqty)
 203                                             VALUES (?, ?)");
 204             my $sth_update = $dbh->prepare("UPDATE default_borrower_circ_rules
 205                                             SET maxissueqty = ?
 206                                             WHERE categorycode = ?");
 207             $sth_search->execute($branch);
 208             my $res = $sth_search->fetchrow_hashref();
 209             if ($res->{total}) {
 210                 $sth_update->execute($maxissueqty, $categorycode);
 211             } else {
 212                 $sth_insert->execute($categorycode, $maxissueqty);
 213             }
 214         }
 215     } elsif ($categorycode eq "*") {
 216         my $sth_search = $dbh->prepare("SELECT count(*) AS total
 217                                         FROM default_branch_circ_rules
 218                                         WHERE branchcode = ?");
 219         my $sth_insert = $dbh->prepare("INSERT INTO default_branch_circ_rules
 220                                         (branchcode, maxissueqty)
 221                                         VALUES (?, ?)");
 222         my $sth_update = $dbh->prepare("UPDATE default_branch_circ_rules
 223                                         SET maxissueqty = ?
 224                                         WHERE branchcode = ?");
 225         $sth_search->execute($branch);
 226         my $res = $sth_search->fetchrow_hashref();
 227         if ($res->{total}) {
 228             $sth_update->execute($maxissueqty, $branch);
 229         } else {
 230             $sth_insert->execute($branch, $maxissueqty);
 231         }
 232     } else {
 233         my $sth_search = $dbh->prepare("SELECT count(*) AS total
 234                                         FROM branch_borrower_circ_rules
 235                                         WHERE branchcode = ?
 236                                         AND   categorycode = ?");
 237         my $sth_insert = $dbh->prepare("INSERT INTO branch_borrower_circ_rules
 238                                         (branchcode, categorycode, maxissueqty)
 239                                         VALUES (?, ?, ?)");
 240         my $sth_update = $dbh->prepare("UPDATE branch_borrower_circ_rules
 241                                         SET maxissueqty = ?
 242                                         WHERE branchcode = ?
 243                                         AND categorycode = ?");
 244
 245         $sth_search->execute($branch, $categorycode);
 246         my $res = $sth_search->fetchrow_hashref();
 247         if ($res->{total}) {
 248             $sth_update->execute($maxissueqty, $branch, $categorycode);
 249         } else {
 250             $sth_insert->execute($branch, $categorycode, $maxissueqty);
 251         }
 252     }
 253 }
 254 elsif ($op eq "add-branch-item") {
 255     my $itemtype  = $input->param('itemtype');
 256     my $holdallowed   = $input->param('holdallowed');
 257     $holdallowed =~ s/\s//g;
 258     $holdallowed = undef if $holdallowed !~ /^\d+/;
 259
 260     if ($branch eq "*") {
 261         if ($itemtype eq "*") {
 262             my $sth_search = $dbh->prepare("SELECT count(*) AS total
 263                                             FROM default_circ_rules");
 264             my $sth_insert = $dbh->prepare("INSERT INTO default_circ_rules
 265                                             (holdallowed)
 266                                             VALUES (?)");
 267             my $sth_update = $dbh->prepare("UPDATE default_circ_rules
 268                                             SET holdallowed = ?");
 269
 270             $sth_search->execute();
 271             my $res = $sth_search->fetchrow_hashref();
 272             if ($res->{total}) {
 273                 $sth_update->execute($holdallowed);
 274             } else {
 275                 $sth_insert->execute($holdallowed);
 276             }
 277         } else {
 278             my $sth_search = $dbh->prepare("SELECT count(*) AS total
 279                                             FROM default_branch_item_rules
 280                                             WHERE itemtype = ?");
 281             my $sth_insert = $dbh->prepare("INSERT INTO default_branch_item_rules
 282                                             (itemtype, holdallowed)
 283                                             VALUES (?, ?)");
 284             my $sth_update = $dbh->prepare("UPDATE default_branch_item_rules
 285                                             SET holdallowed = ?
 286                                             WHERE itemtype = ?");
 287             $sth_search->execute($itemtype);
 288             my $res = $sth_search->fetchrow_hashref();
 289             if ($res->{total}) {
 290                 $sth_update->execute($holdallowed, $itemtype);
 291             } else {
 292                 $sth_insert->execute($itemtype, $holdallowed);
 293             }
 294         }
 295     } elsif ($itemtype eq "*") {
 296         my $sth_search = $dbh->prepare("SELECT count(*) AS total
 297                                         FROM default_branch_circ_rules
 298                                         WHERE branchcode = ?");
 299         my $sth_insert = $dbh->prepare("INSERT INTO default_branch_circ_rules
 300                                         (branchcode, holdallowed)
 301                                         VALUES (?, ?)");
 302         my $sth_update = $dbh->prepare("UPDATE default_branch_circ_rules
 303                                         SET holdallowed = ?
 304                                         WHERE branchcode = ?");
 305         $sth_search->execute($branch);
 306         my $res = $sth_search->fetchrow_hashref();
 307         if ($res->{total}) {
 308             $sth_update->execute($holdallowed, $branch);
 309         } else {
 310             $sth_insert->execute($branch, $holdallowed);
 311         }
 312     } else {
 313         my $sth_search = $dbh->prepare("SELECT count(*) AS total
 314                                         FROM branch_item_rules
 315                                         WHERE branchcode = ?
 316                                         AND   itemtype = ?");
 317         my $sth_insert = $dbh->prepare("INSERT INTO branch_item_rules
 318                                         (branchcode, itemtype, holdallowed)
 319                                         VALUES (?, ?, ?)");
 320         my $sth_update = $dbh->prepare("UPDATE branch_item_rules
 321                                         SET holdallowed = ?
 322                                         WHERE branchcode = ?
 323                                         AND itemtype = ?");
 324
 325         $sth_search->execute($branch, $itemtype);
 326         my $res = $sth_search->fetchrow_hashref();
 327         if ($res->{total}) {
 328             $sth_update->execute($holdallowed, $branch, $itemtype);
 329         } else {
 330             $sth_insert->execute($branch, $itemtype, $holdallowed);
 331         }
 332     }
 333 }
 334
 335 my $branches = GetBranches();
 336 my @branchloop;
 337 for my $thisbranch (sort { $branches->{$a}->{branchname} cmp $branches->{$b}->{branchname} } keys %$branches) {
 338     my $selected = 1 if $thisbranch eq $branch;
 339     my %row =(value => $thisbranch,
 340                 selected => $selected,
 341                 branchname => $branches->{$thisbranch}->{'branchname'},
 342             );
 343     push @branchloop, \%row;
 344 }
 345
 346 my $sth=$dbh->prepare("SELECT description,categorycode FROM categories ORDER BY description");
 347 $sth->execute;
 348 my @category_loop;
 349 while (my $data=$sth->fetchrow_hashref){
 350     push @category_loop,$data;
 351 }
 352
 353 $sth->finish;
 354 $sth=$dbh->prepare("SELECT description,itemtype FROM itemtypes ORDER BY description");
 355 $sth->execute;
 356 # $i=0;
 357 my @row_loop;
 358 my @itemtypes;
 359 while (my $row=$sth->fetchrow_hashref){
 360     push @itemtypes,$row;
 361 }
 362
 363 my $sth2 = $dbh->prepare("
 364     SELECT issuingrules.*, itemtypes.description AS humanitemtype, categories.description AS humancategorycode
 365     FROM issuingrules
 366     LEFT JOIN itemtypes
 367         ON (itemtypes.itemtype = issuingrules.itemtype)
 368     LEFT JOIN categories
 369         ON (categories.categorycode = issuingrules.categorycode)
 370     WHERE issuingrules.branchcode = ?
 371 ");
 372 $sth2->execute($branch);
 373
 374 while (my $row = $sth2->fetchrow_hashref) {
 375     $row->{'humanitemtype'} ||= $row->{'itemtype'};
 376     $row->{'default_humanitemtype'} = 1 if $row->{'humanitemtype'} eq '*';
 377     $row->{'humancategorycode'} ||= $row->{'categorycode'};
 378     $row->{'default_humancategorycode'} = 1 if $row->{'humancategorycode'} eq '*';
 379     $row->{'fine'} = sprintf('%.2f', $row->{'fine'});
 380     push @row_loop, $row;
 381 }
 382 $sth->finish;
 383
 384 my @sorted_row_loop = sort by_category_and_itemtype @row_loop;
 385
 386 my $sth_branch_cat;
 387 if ($branch eq "*") {
 388     $sth_branch_cat = $dbh->prepare("
 389         SELECT default_borrower_circ_rules.*, categories.description AS humancategorycode
 390         FROM default_borrower_circ_rules
 391         JOIN categories USING (categorycode)
 392
 393     ");
 394     $sth_branch_cat->execute();
 395 } else {
 396     $sth_branch_cat = $dbh->prepare("
 397         SELECT branch_borrower_circ_rules.*, categories.description AS humancategorycode
 398         FROM branch_borrower_circ_rules
 399         JOIN categories USING (categorycode)
 400         WHERE branch_borrower_circ_rules.branchcode = ?
 401     ");
 402     $sth_branch_cat->execute($branch);
 403 }
 404
 405 my @branch_cat_rules = ();
 406 while (my $row = $sth_branch_cat->fetchrow_hashref) {
 407     push @branch_cat_rules, $row;
 408 }
 409 my @sorted_branch_cat_rules = sort { $a->{'humancategorycode'} cmp $b->{'humancategorycode'} } @branch_cat_rules;
 410
 411 # note undef maxissueqty so that template can deal with them
 412 foreach my $entry (@sorted_branch_cat_rules, @sorted_row_loop) {
 413     $entry->{unlimited_maxissueqty} = 1 unless defined($entry->{maxissueqty});
 414 }
 415
 416 my @sorted_row_loop = sort by_category_and_itemtype @row_loop;
 417
 418 my $sth_branch_item;
 419 if ($branch eq "*") {
 420     $sth_branch_item = $dbh->prepare("
 421         SELECT default_branch_item_rules.*, itemtypes.description AS humanitemtype
 422         FROM default_branch_item_rules
 423         JOIN itemtypes USING (itemtype)
 424     ");
 425     $sth_branch_item->execute();
 426 } else {
 427     $sth_branch_item = $dbh->prepare("
 428         SELECT branch_item_rules.*, itemtypes.description AS humanitemtype
 429         FROM branch_item_rules
 430         JOIN itemtypes USING (itemtype)
 431         WHERE branch_item_rules.branchcode = ?
 432     ");
 433     $sth_branch_item->execute($branch);
 434 }
 435
 436 my @branch_item_rules = ();
 437 while (my $row = $sth_branch_item->fetchrow_hashref) {
 438     push @branch_item_rules, $row;
 439 }
 440 my @sorted_branch_item_rules = sort { $a->{'humanitemtype'} cmp $b->{'humanitemtype'} } @branch_item_rules;
 441
 442 # note undef holdallowed so that template can deal with them
 443 foreach my $entry (@sorted_branch_item_rules) {
 444     $entry->{holdallowed_any} = 1 if($entry->{holdallowed} == 2);
 445     $entry->{holdallowed_same} = 1 if($entry->{holdallowed} == 1);
 446 }
 447
 448 $template->param(show_branch_cat_rule_form => 1);
 449 $template->param(branch_item_rule_loop => \@sorted_branch_item_rules);
 450 $template->param(branch_cat_rule_loop => \@sorted_branch_cat_rules);
 451
 452 my $sth_defaults;
 453 if ($branch eq "*") {
 454     $sth_defaults = $dbh->prepare("
 455         SELECT *
 456         FROM default_circ_rules
 457     ");
 458     $sth_defaults->execute();
 459 } else {
 460     $sth_defaults = $dbh->prepare("
 461         SELECT *
 462         FROM default_branch_circ_rules
 463         WHERE branchcode = ?
 464     ");
 465     $sth_defaults->execute($branch);
 466 }
 467
 468 my $defaults = $sth_defaults->fetchrow_hashref;
 469
 470 if ($defaults) {
 471     $template->param(default_holdallowed_none => 1) if($defaults->{holdallowed} == 0);
 472     $template->param(default_holdallowed_same => 1) if($defaults->{holdallowed} == 1);
 473     $template->param(default_holdallowed_any => 1) if($defaults->{holdallowed} == 2);
 474     $template->param(default_maxissueqty => $defaults->{maxissueqty});
 475 }
 476
 477 $template->param(default_rules => ($defaults ? 1 : 0));
 478
 479 $template->param(categoryloop => \@category_loop,
 480                         itemtypeloop => \@itemtypes,
 481                         rules => \@sorted_row_loop,
 482                         branchloop => \@branchloop,
 483                         humanbranch => ($branch ne '*' ? $branches->{$branch}->{branchname} : ''),
 484                         current_branch => $branch,
 485                         definedbranch => scalar(@sorted_row_loop)>0
 486                         );
 487 output_html_with_http_headers $input, $cookie, $template->output;
 488
 489 exit 0;
 490
 491 # sort by patron category, then item type, putting
 492 # default entries at the bottom
 493 sub by_category_and_itemtype {
 494     unless (by_category($a, $b)) {
 495         return by_itemtype($a, $b);
 496     }
 497 }
 498
 499 sub by_category {
 500     my ($a, $b) = @_;
 501     if ($a->{'default_humancategorycode'}) {
 502         return ($b->{'default_humancategorycode'} ? 0 : 1);
 503     } elsif ($b->{'default_humancategorycode'}) {
 504         return -1;
 505     } else {
 506         return $a->{'humancategorycode'} cmp $b->{'humancategorycode'};
 507     }
 508 }
 509
 510 sub by_itemtype {
 511     my ($a, $b) = @_;
 512     if ($a->{'default_humanitemtype'}) {
 513         return ($b->{'default_humanitemtype'} ? 0 : 1);
 514     } elsif ($b->{'default_humanitemtype'}) {
 515         return -1;
 516     } else {
 517         return $a->{'humanitemtype'} cmp $b->{'humanitemtype'};
 518     }
 519 }