@@ -, +, @@
---
C4/Overdues.pm | 19 +-
.../prog/en/modules/tools/overduerules.tt | 357 ++++++++++++---------
tools/overduerules.pl | 347 +++++++-------------
3 files changed, 331 insertions(+), 392 deletions(-)
--- a/C4/Overdues.pm
+++ a/C4/Overdues.pm
@@ -782,9 +782,9 @@ returns a list of branch codes for branches with overdue rules defined.
sub GetBranchcodesWithOverdueRules {
my $dbh = C4::Context->dbh;
my $branchcodes = $dbh->selectcol_arrayref(q|
- SELECT DISTINCT(branchcode)
- FROM overduerules
- WHERE delay1 IS NOT NULL
+ SELECT DISTINCT b.branchcode
+ FROM branches b, overduerules r
+ WHERE b.branchcode = r.branchcode or r.branchcode = ''
ORDER BY branchcode
|);
if ( $branchcodes->[0] eq '' ) {
@@ -950,17 +950,14 @@ sub RemoveNotifyLine {
=cut
sub GetOverdueMessageTransportTypes {
- my ( $branchcode, $categorycode, $letternumber ) = @_;
- return unless $categorycode and $letternumber;
+ my ( $overduerules_id ) = @_;
+ return unless $overduerules_id;
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare("
- SELECT message_transport_type
- FROM overduerules odr LEFT JOIN overduerules_transport_types ott USING (overduerules_id)
- WHERE branchcode = ?
- AND categorycode = ?
- AND letternumber = ?
+ SELECT message_transport_type FROM overduerules_transport_types
+ WHERE overduerules_id = ?
");
- $sth->execute( $branchcode, $categorycode, $letternumber );
+ $sth->execute( $overduerules_id );
my @mtts;
while ( my $mtt = $sth->fetchrow ) {
push @mtts, $mtt;
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/overduerules.tt
+++ a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/overduerules.tt
@@ -3,24 +3,102 @@
[% INCLUDE 'doc-head-open.inc' %]
+
+
+
[% pagination_bar %]
+
+
+
+% INCLUDE 'tools-menu.inc' %]
[% INCLUDE 'intranet-bottom.inc' %]
--- a/tools/overduerules.pl
+++ a/tools/overduerules.pl
@@ -1,6 +1,6 @@
#!/usr/bin/perl
-# Copyright 2000-2002 Katipo Communications
+# Copyright 2014 Solutions inLibro
#
# This file is part of Koha.
#
@@ -20,6 +20,8 @@
use strict;
use warnings;
use CGI qw ( -utf8 );
+use List::Util qw/min/;
+use File::Spec;
use C4::Context;
use C4::Output;
use C4::Auth;
@@ -29,6 +31,7 @@ use C4::Members;
use C4::Overdues;
use Koha::Libraries;
+
use Koha::Patron::Categories;
our $input = new CGI;
@@ -38,24 +41,17 @@ my @patron_categories = Koha::Patron::Categories->search( { overduenoticerequire
my @category_codes = map { $_->categorycode } @patron_categories;
our @rule_params = qw(delay letter debarred);
-# blank_row($category_code) - return true if the entire row is blank.
-sub blank_row {
- my ($category_code) = @_;
- for my $rp (@rule_params) {
- for my $n (1 .. 3) {
- my $key = "${rp}${n}-$category_code";
-
- if (utf8::is_utf8($key)) {
- utf8::encode($key);
- }
- my $value = $input->param($key);
- if ($value) {
- return 0;
- }
- }
- }
- return 1;
+sub StringSearch {
+ # this needs to be rewritten to use DBIx::Class
+ my ($searchstring, $type) = @_;
+ my $dbh = C4::Context->dbh;
+ my @data = split( ' ', $searchstring );
+ my $sth = $dbh->prepare(
+ "SELECT * FROM overduerules WHERE (branchcode LIKE ?) or (categorycode LIKE ?) or (letter LIKE ?) or (itemtype LIKE ?) ORDER BY branchcode, delay"
+ );
+ $sth->execute("$data[0]%","$data[0]%","$data[0]%","$data[0]%");
+ return $sth->fetchall_arrayref({});
}
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
@@ -69,9 +65,20 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
}
);
-my $type = $input->param('type');
+my $searchfield = $input->param('description');
+my $script_name = "/cgi-bin/koha/tools/overduerules.pl";
+my $op = $input->param('op');
+my $overduerules_id = $input->param('overduerules_id');
+my $branch = $input->param('branchcode');
+my $cat = $input->param('categorycode');
+my $itemtype = $input->param('itemtype');
+my $onhold = $input->param('onhold');
+my $delay = $input->param('delay') || 1;
+my @mttparams;
+foreach ($input->param){
+ push (@mttparams, $1) if ($_ =~ /^mtt-(.*)/);
+}
-my $branch = $input->param('branch');
$branch =
defined $branch ? $branch
: C4::Context->preference('DefaultToLoggedInLibraryOverdueTriggers') ? C4::Context::mybranch()
@@ -80,237 +87,101 @@ $branch =
$branch ||= q{};
$branch = q{} if $branch eq 'NO_LIBRARY_SET';
-my $op = $input->param('op');
-$op ||= q{};
-my $err=0;
+$searchfield =~ s/\,//g;
-# save the values entered into tables
-my %temphash;
-my $input_saved = 0;
-if ($op eq 'save') {
- my @names=$input->multi_param();
- my $sth_search = $dbh->prepare("SELECT count(*) AS total FROM overduerules WHERE branchcode=? AND categorycode=?");
- my $sth_insert = $dbh->prepare("INSERT INTO overduerules (branchcode,categorycode, delay1,letter1,debarred1, delay2,letter2,debarred2, delay3,letter3,debarred3) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
- my $sth_update=$dbh->prepare("UPDATE overduerules SET delay1=?, letter1=?, debarred1=?, delay2=?, letter2=?, debarred2=?, delay3=?, letter3=?, debarred3=? WHERE branchcode=? AND categorycode=?");
- my $sth_delete=$dbh->prepare("DELETE FROM overduerules WHERE branchcode=? AND categorycode=?");
- my $sth_insert_mtt = $dbh->prepare("
- INSERT INTO overduerules_transport_types(
- overduerules_id, letternumber, message_transport_type
- ) VALUES (
- (SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?), ?, ?
- )
- ");
- my $sth_delete_mtt = $dbh->prepare("
- DELETE FROM overduerules_transport_types
- WHERE overduerules_id = (SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?)
- ");
+# fix all this
+my $hrItemtypes = $dbh->selectall_hashref("SELECT description,itemtype FROM itemtypes ", "itemtype");
+my $hrLetters = $dbh->selectall_hashref("SELECT name,code FROM letter WHERE module = 'circulation' order by name", "code");
+my $arMessage_transport_types = C4::Letters::GetMessageTransportTypes();
+my $hrUsercats = $dbh->selectall_hashref("SELECT description,categorycode FROM categories WHERE overduenoticerequired = 1", "categorycode");
- foreach my $key (@names){
- # ISSUES
- if ($key =~ /(delay|letter|debarred)([1-3])-(.*)/) {
- my $type = $1; # data type
- my $num = $2; # From 1 to 3
- my $bor = $3; # borrower category
- my $value = $input->param($key);
- if ($type eq 'delay') {
- $temphash{$bor}->{"$type$num"} = ($value =~ /^\d+$/ && int($value) > 0) ? int($value) : '';
- } else {
- # type is letter
- $temphash{$bor}->{"$type$num"} = $value if $value ne '';
- }
- }
- }
- # figure out which rows need to be deleted
- my @rows_to_delete = grep { blank_row($_) } @category_codes;
-
- foreach my $bor (keys %temphash){
- # get category name if we need it for an error message
- my $bor_category = Koha::Patron::Categories->find($bor);
- my $bor_category_name = $bor_category ? $bor_category->description : $bor;
-
- # Do some Checking here : delay1 < delay2
{delay1}=~/[^0-9]/ and $temphash{$bor}->{delay1} ne ""){
- $template->param("ERROR"=>1,"ERRORDELAY"=>"delay1","BORERR"=>$bor_category_name);
- $err=1;
- } elsif ($temphash{$bor}->{delay2}=~/[^0-9]/ and $temphash{$bor}->{delay2} ne ""){
- $template->param("ERROR"=>1,"ERRORDELAY"=>"delay2","BORERR"=>$bor_category_name);
- $err=1;
- } elsif ($temphash{$bor}->{delay3}=~/[^0-9]/ and $temphash{$bor}->{delay3} ne ""){
- $template->param("ERROR"=>1,"ERRORDELAY"=>"delay3","BORERR"=>$bor_category_name);
- $err=1;
- } elsif ($temphash{$bor}->{delay1} and not ($temphash{$bor}->{"letter1"} or $temphash{$bor}->{"debarred1"})) {
- $template->param("ERROR"=>1,"ERRORUSELESSDELAY"=>"delay1","BORERR"=>$bor_category_name);
- $err=1;
- } elsif ($temphash{$bor}->{delay2} and not ($temphash{$bor}->{"letter2"} or $temphash{$bor}->{"debarred2"})) {
- $template->param("ERROR"=>1,"ERRORUSELESSDELAY"=>"delay2","BORERR"=>$bor_category_name);
- $err=1;
- } elsif ($temphash{$bor}->{delay3} and not ($temphash{$bor}->{"letter3"} or $temphash{$bor}->{"debarred3"})) {
- $template->param("ERROR"=>1,"ERRORUSELESSDELAY"=>"delay3","BORERR"=>$bor_category_name);
- $err=1;
- }elsif ($temphash{$bor}->{delay3} and
- ($temphash{$bor}->{delay3}<=$temphash{$bor}->{delay2} or $temphash{$bor}->{delay3}<=$temphash{$bor}->{delay1})
- or $temphash{$bor}->{delay2} and ($temphash{$bor}->{delay2}<=$temphash{$bor}->{delay1})){
- $template->param("ERROR"=>1,"ERRORORDER"=>1,"BORERR"=>$bor_category_name);
- $err=1;
- }
- unless ($err){
- if (($temphash{$bor}->{delay1} and ($temphash{$bor}->{"letter1"} or $temphash{$bor}->{"debarred1"}))
- or ($temphash{$bor}->{delay2} and ($temphash{$bor}->{"letter2"} or $temphash{$bor}->{"debarred2"}))
- or ($temphash{$bor}->{delay3} and ($temphash{$bor}->{"letter3"} or $temphash{$bor}->{"debarred3"}))) {
- $sth_search->execute($branch,$bor);
- my $res = $sth_search->fetchrow_hashref();
- if ($res->{'total'}>0) {
- $sth_update->execute(
- ($temphash{$bor}->{"delay1"}?$temphash{$bor}->{"delay1"}:undef),
- ($temphash{$bor}->{"letter1"}?$temphash{$bor}->{"letter1"}:""),
- ($temphash{$bor}->{"debarred1"}?$temphash{$bor}->{"debarred1"}:0),
- ($temphash{$bor}->{"delay2"}?$temphash{$bor}->{"delay2"}:undef),
- ($temphash{$bor}->{"letter2"}?$temphash{$bor}->{"letter2"}:""),
- ($temphash{$bor}->{"debarred2"}?$temphash{$bor}->{"debarred2"}:0),
- ($temphash{$bor}->{"delay3"}?$temphash{$bor}->{"delay3"}:undef),
- ($temphash{$bor}->{"letter3"}?$temphash{$bor}->{"letter3"}:""),
- ($temphash{$bor}->{"debarred3"}?$temphash{$bor}->{"debarred3"}:0),
- $branch ,$bor
- );
- } else {
- $sth_insert->execute($branch,$bor,
- ($temphash{$bor}->{"delay1"}?$temphash{$bor}->{"delay1"}:0),
- ($temphash{$bor}->{"letter1"}?$temphash{$bor}->{"letter1"}:""),
- ($temphash{$bor}->{"debarred1"}?$temphash{$bor}->{"debarred1"}:0),
- ($temphash{$bor}->{"delay2"}?$temphash{$bor}->{"delay2"}:0),
- ($temphash{$bor}->{"letter2"}?$temphash{$bor}->{"letter2"}:""),
- ($temphash{$bor}->{"debarred2"}?$temphash{$bor}->{"debarred2"}:0),
- ($temphash{$bor}->{"delay3"}?$temphash{$bor}->{"delay3"}:0),
- ($temphash{$bor}->{"letter3"}?$temphash{$bor}->{"letter3"}:""),
- ($temphash{$bor}->{"debarred3"}?$temphash{$bor}->{"debarred3"}:0)
- );
- }
+# get everything on the page
+$template->param(
+ script_name => $script_name,
+ categories => \@patron_categories,
+ itemtypes => $hrItemtypes,
+ letters => $hrLetters,
+ message_transport_types => $arMessage_transport_types,
+);
- $sth_delete_mtt->execute( $branch, $bor );
- for my $letternumber ( 1..3 ) {
- my @mtt = $input->multi_param( "mtt${letternumber}-$bor" );
- next unless @mtt;
- for my $mtt ( @mtt ) {
- $sth_insert_mtt->execute( $branch, $bor, $letternumber, $mtt);
- }
- }
- }
- }
+# operations
+if ( $op =~ m/add/ ) {
+ $op =~ s/\D//g;
+ $op = ($op eq '') ? 0 : $op;
+ $cat = (!defined $cat ) ? 0 : $cat;
+ $itemtype = (!defined $itemtype ) ? '' :$itemtype;
+ $onhold = (!defined $onhold ) ? 0 : $onhold;
+ my $debarred = (!defined $input->param('debarred')) ? 0 : $input->param('debarred');
+ # dangerous, SQL injection fix
+ my $ruleId = $dbh->selectrow_array("SELECT overduerules_id from overduerules where branchcode = '$branch' and categorycode = '$cat' and itemtype = '$itemtype' and onhold = '$onhold' and delay = '$delay' ");
+ if ( $ruleId ) {# it's a modification. We update the overrules table and delete corresponding entries in overduerules_transport_types
+ my $query2 = " UPDATE overduerules
+ SET letter = ?, debarred = ?
+ WHERE branchcode = ? and categorycode = ? and itemtype = ? and onhold = ? and delay = ? ";
+ my $sth = $dbh->prepare($query2);
+ $sth->execute($input->param('letter') || '', $debarred , $branch, $cat, $itemtype, $onhold, $delay);
+ $dbh->do("DELETE FROM overduerules_transport_types where overduerules_id = $ruleId");
}
- unless ($err) {
- for my $category_code (@rows_to_delete) {
- $sth_delete->execute($branch, $category_code);
- }
- $template->param(datasaved => 1);
- $input_saved = 1;
+ elsif ($op != 0) { # case edit pkeys
+ my $query2 = "UPDATE overduerules
+ SET letter = ?, debarred = ?, branchcode = ?, categorycode = ?, itemtype = ?, onhold = ?, delay = ?
+ WHERE overduerules_id = ? ";
+ my $sth = $dbh->prepare($query2);
+ $sth->execute($input->param('letter') || '', $debarred , $branch, $cat, $itemtype, $onhold, $delay, $op);
+ $dbh->do("DELETE FROM overduerules_transport_types where overduerules_id = $op");
}
-}
-
-my $letters = C4::Letters::GetLettersAvailableForALibrary(
- {
- branchcode => $branch,
- module => "circulation",
+ else { # add a new entry in overrules
+ my $query = "
+ INSERT INTO overduerules (branchcode, categorycode, itemtype, onhold, delay, letter, debarred)
+ VALUES (?,?,?,?,?,?,?);";
+ my $sth = $dbh->prepare($query);
+ $sth->execute($branch, $cat, $itemtype, $onhold, $delay, $input->param('letter') || '', $debarred);
+ $ruleId = $sth->{mysql_insertid}; # mysqlism need to fix this
}
-);
-
-my @line_loop;
-
-my $message_transport_types = C4::Letters::GetMessageTransportTypes();
-my ( @first, @second, @third );
-for my $patron_category (@patron_categories) {
- if (%temphash and not $input_saved){
- # if we managed to save the form submission, don't
- # reuse %temphash, but take the values from the
- # database - this makes it easier to identify
- # bugs where the form submission was not correctly saved
- for my $i ( 1..3 ){
- my %row = (
- overduename => $patron_category->categorycode,
- line => $patron_category->description,
- );
- $row{delay}=$temphash{$patron_category->categorycode}->{"delay$i"};
- $row{debarred}=$temphash{$patron_category->categorycode}->{"debarred$i"};
- $row{selected_lettercode} = $temphash{ $patron_category->categorycode }->{"letter$i"};
- my @selected_mtts = @{ GetOverdueMessageTransportTypes( $branch, $patron_category->categorycode, $i) };
- my @mtts;
- for my $mtt ( @$message_transport_types ) {
- push @mtts, {
- value => $mtt,
- selected => ( grep {/$mtt/} @selected_mtts ) ? 1 : 0 ,
- }
- }
- $row{message_transport_types} = \@mtts;
- if ( $i == 1 ) {
- push @first, \%row;
- } elsif ( $i == 2 ) {
- push @second, \%row;
- } else {
- push @third, \%row;
- }
- }
- } else {
- #getting values from table
- my $sth2=$dbh->prepare("SELECT * from overduerules WHERE branchcode=? AND categorycode=?");
- $sth2->execute($branch,$patron_category->categorycode);
- my $dat=$sth2->fetchrow_hashref;
- for my $i ( 1..3 ){
- my %row = (
- overduename => $patron_category->categorycode,
- line => $patron_category->description,
- );
-
- $row{selected_lettercode} = $dat->{"letter$i"};
-
- if ($dat->{"delay$i"}){$row{delay}=$dat->{"delay$i"};}
- if ($dat->{"debarred$i"}){$row{debarred}=$dat->{"debarred$i"};}
- my @selected_mtts = @{ GetOverdueMessageTransportTypes( $branch, $patron_category->categorycode, $i) };
- my @mtts;
- for my $mtt ( @$message_transport_types ) {
- push @mtts, {
- value => $mtt,
- selected => ( grep {/$mtt/} @selected_mtts ) ? 1 : 0 ,
- }
- }
- $row{message_transport_types} = \@mtts;
- if ( $i == 1 ) {
- push @first, \%row;
- } elsif ( $i == 2 ) {
- push @second, \%row;
- } else {
- push @third, \%row;
+ # following an update or an insert, we have to insert into overduerules_transport_types
+ my $sth = $dbh->prepare("INSERT INTO overduerules_transport_types (message_transport_type, overduerules_id) values (?,?)");
+ foreach my $newMtt (@mttparams){
+ $sth->execute($newMtt, $ruleId);
+ }
+ print $input->redirect('overduerules.pl');
+ exit;
+}
+elsif ( $op eq 'delete' ) {
+ my $sth1 = $dbh->prepare("delete from overduerules_transport_types where overduerules_id = ?");
+ my $sth2 = $dbh->prepare("delete from overduerules where overduerules_id = ?");
+ $sth1->execute($overduerules_id);
+ $sth2->execute($overduerules_id);
+ print $input->redirect('overduerules.pl');
+ exit;
+}
+else { #default
+ my ($results) = StringSearch( $searchfield );
+ my @loop;
+ my $hrBranches = my $branches = Koha::Libraries->search({}, { order_by => ['branchname'] })->unblessed;
+ foreach my $rule ( @{$results} ) {
+ $rule->{branchname} = $hrBranches->{$rule->{branchcode}}->{branchname};
+ $rule->{lettername} = $hrLetters->{$rule->{letter}}->{name};
+ $rule->{categorydescription} = $hrUsercats->{$rule->{categorycode}}->{description};
+ $rule->{itemtypedescription} = $hrItemtypes->{$rule->{itemtype}}->{description};
+ my @selected_mtts = @{ GetOverdueMessageTransportTypes( $rule->{overduerules_id} ) };
+ my @mtts;
+ for my $mtt ( @$arMessage_transport_types ) {
+ push @mtts, {
+ value => $mtt,
+ selected => ( grep {/$mtt/} @selected_mtts ) ? 1 : 0 ,
}
}
+ $rule->{message_transport_types} = \@mtts;
+ push( @loop, $rule );
}
-}
-my @tabs = (
- {
- id => 'first',
- number => 1,
- values => \@first,
- },
- {
- id => 'second',
- number => 2,
- values => \@second,
- },
- {
- id => 'third',
- number => 3,
- values => \@third,
- },
-);
+ $template->param(
+ loop => \@loop,
+ );
+}
-$template->param(
- table => ( @first or @second or @third ? 1 : 0 ),
- branch => $branch,
- tabs => \@tabs,
- message_transport_types => $message_transport_types,
- letters => $letters,
-);
output_html_with_http_headers $input, $cookie, $template->output;
--