From 1e3e69fe9954881d2a2aa62f7cebbafd8400bc88 Mon Sep 17 00:00:00 2001 From: Paul POULAIN Date: Mon, 22 Oct 2007 03:19:45 -0500 Subject: [PATCH] Overdues fixes (unfinished) - reindenting - upcasing SQL - the script at least compiles... ... but it does seem not work yet Signed-off-by: Chris Cormack Signed-off-by: Joshua Ferraro --- C4/Letters.pm | 2 +- misc/overduenotices-30.pl | 310 +++++++++++++++++++------------------- tools/overduerules.pl | 164 ++++++++++---------- 3 files changed, 236 insertions(+), 240 deletions(-) diff --git a/C4/Letters.pm b/C4/Letters.pm index 1d8856ddf7..97344cd43c 100644 --- a/C4/Letters.pm +++ b/C4/Letters.pm @@ -288,7 +288,7 @@ sub SendAlerts { ); sendmail(%mail); -# warn "sending to $mail{To} From $mail{From} subj $mail{Subject} Mess $mail{Message}"; +# warn "sending to $mail{To} From $mail{From} subj $mail{Subject} Mess $mail{Message}"; } } } diff --git a/misc/overduenotices-30.pl b/misc/overduenotices-30.pl index 39df18e7b4..40e9922429 100755 --- a/misc/overduenotices-30.pl +++ b/misc/overduenotices-30.pl @@ -38,176 +38,172 @@ use Getopt::Long; my ($confirm, $nomail, $mybranch, $myborcat,$myborcatout, $letter, $MAX, $choice); GetOptions( 'c' => \$confirm, - 'n' => \$nomail, - 'max=s' => \$MAX, - 'all' => \$choice, + 'n' => \$nomail, + 'max=s' => \$MAX, + 'all' => \$choice, ); unless ($confirm) { - print qq| + print qq| This script will send overdue notices by e-mail and prepare a file of\nnotices for printing if the borrower does not have e-mail. You MUST edit this script for your library BEFORE you run it for the first time! See the comments in the script for directions on changing the script. This script has 2 parameters : - -c to confirm and remove this help & warning - -n to avoid sending any mail. Instead, all mail messages are printed on screen. Usefull for testing purposes. - -branch to select overdues for ONE specific branch. - -borcat to select overdues for one borrower category, - -borcatout to exclude this borrower category from overdunotices, - -max MAXIMUM day count before stopping to send overdue notice, - -file to enter a specific filename to be read for message. - -all to include ALL the items that reader borrowed. + -c to confirm and remove this help & warning + -n to avoid sending any mail. Instead, all mail messages are printed on screen. Usefull for testing purposes. + -branch to select overdues for ONE specific branch. + -borcat to select overdues for one borrower category, + -borcatout to exclude this borrower category from overdunotices, + -max MAXIMUM day count before stopping to send overdue notice, + -file to enter a specific filename to be read for message. + -all to include ALL the items that reader borrowed. Do you wish to continue? (y/n) |; - chomp($_ = ); - exit unless (/^(y|Y|o|O)/i); # comment these lines out once you've made the changes - + chomp($_ = ); + exit unless (/^(y|Y|o|O)/i); # comment these lines out once you've made the changes + } -#warn 'site '.$mybranch.' text '.$letter; my $dbh = C4::Context->dbh; -my $rqoverduebranches=$dbh->prepare("SELECT DISTINCT branchcode from overduerules where delay1>0"); +my $rqoverduebranches=$dbh->prepare("SELECT DISTINCT branchcode FROM overduerules WHERE delay1>0"); $rqoverduebranches->execute; while (my ($branchcode)=$rqoverduebranches->fetchrow){ - warn "branchcode : $branchcode"; - my $branchname; - my $emailaddress; - if ($branchcode){ - my $rqbranch=$dbh->prepare("SELECT * from branches where branchcode = ?"); - $rqbranch->execute($branchcode); - my $data = $rqbranch->fetchrow_hashref; - $emailaddress = $data->{branchemail}; - $branchname = $data->{branchname}; - } - $emailaddress=C4::Context->preference('KohaAdminEmailAddress') unless ($emailaddress); + warn "branchcode : $branchcode"; + my $branchname; + my $emailaddress; + if ($branchcode){ + my $rqbranch=$dbh->prepare("SELECT * FROM branches WHERE branchcode = ?"); + $rqbranch->execute($branchcode); + my $data = $rqbranch->fetchrow_hashref; + $emailaddress = $data->{branchemail}; + $branchname = $data->{branchname}; + } + $emailaddress=C4::Context->preference('KohaAdminEmailAddress') unless ($emailaddress); - #print STDERR "$emailaddress\n"; - # - # BEGINNING OF PARAMETERS - # - my $rqoverduerules=$dbh->prepare("SELECT * from overduerules where delay1>0 and branchcode = ?"); - $rqoverduerules->execute($branchcode); - while (my $data=$rqoverduerules->fetchrow_hashref){ - for (my $i=1; $i<=3;$i++){ - #Two actions : - # A- Send a letter - # B- Debar - my $mindays = $data->{"delay$i"}; # the notice will be sent after mindays days (grace period) - my $rqdebarring=$dbh->prepare("UPDATE borrowers SET debarred=1 WHERE borrowernumber=?") if $data->{"debarred$i"}; - my $maxdays = ($data->{"delay".($i+1)}? - $data->{"delay".($i+1)} - :($MAX?$MAX:365)); # issues being more than maxdays late are managed somewhere else. (borrower probably suspended) - #LETTER parameters - my $smtpserver = 'smtp.wanadoo.fr'; # your smtp server (the server who sent mails) - my $from = $emailaddress; # all the mails sent to the borrowers will appear coming from here. - my $mailtitle = 'Overdue'; # the title of the mails - $mailtitle = 'Issue status' if ($choice); # the title of the mails - my $librarymail = $emailaddress; # all notices without mail are sent (in 1 mail) to this mail address. They must then be managed manually. - my $letter = $data->{"letter$i"} if $data->{"letter$i"}; - # this parameter (the last) is the text of the mail that is sent. - # this text contains fields that are replaced by their value. Those fields must be written between brackets - # The following fields are available : - # - my $mailtext=$letter; - # - # END OF PARAMETERS - # - open OUTFILE, ">overdues" or die "Cannot open file overdues: $!"; - - # set the e-mail server -- comment out if not doing e-mail notices - unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , $smtpserver; - # set your own mail server name here - - my $strsth = "SELECT COUNT(*), issues.borrowernumber,firstname,surname,streetaddress,physstreet,city,zipcode, emailaddress, MIN(date_due) as longest_issue FROM issues,borrowers,categories WHERE returndate IS NULL AND issues.borrowernumber=borrowers.borrowernumber and borrowers.categorycode=categories.categorycode "; - $strsth .= " and issues.branchcode='".$branchcode."' " if ($branchcode); - $strsth .= " and borrowers.categorycode='".$data->{categorycode}."' " if ($data->{categorycode}); - $strsth .= " and categories.overduenoticerequired=1 group by issues.borrowernumber HAVING TO_DAYS(NOW())-TO_DAYS(longest_issue) BETWEEN $mindays and $maxdays "; - my $sth = $dbh->prepare ($strsth); - warn "".$strsth; - my $sth2 = $dbh->prepare("SELECT biblio.title,biblio.author,items.barcode, issues.timestamp FROM issues,items,biblio WHERE items.itemnumber=issues.itemnumber and biblio.biblionumber=items.biblionumber AND issues.borrowernumber=? AND returndate IS NULL AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN $mindays and $maxdays"); + #print STDERR "$emailaddress\n"; + # + # BEGINNING OF PARAMETERS + # + my $rqoverduerules=$dbh->prepare("SELECT * FROM overduerules WHERE delay1>0 and branchcode = ?"); + $rqoverduerules->execute($branchcode); + while (my $data=$rqoverduerules->fetchrow_hashref){ + for (my $i=1; $i<=3;$i++){ + #Two actions : + # A- Send a letter + # B- Debar + my $mindays = $data->{"delay$i"}; # the notice will be sent after mindays days (grace period) + my $rqdebarring=$dbh->prepare("UPDATE borrowers SET debarred=1 WHERE borrowernumber=?") if $data->{"debarred$i"}; + my $maxdays = ($data->{"delay".($i+1)}? + $data->{"delay".($i+1)} + :($MAX?$MAX:365)); # issues being more than maxdays late are managed somewhere else. (borrower probably suspended) + #LETTER parameters + my $smtpserver = 'smtp.wanadoo.fr'; # your smtp server (the server who sent mails) + my $from = $emailaddress; # all the mails sent to the borrowers will appear coming from here. + my $mailtitle = 'Overdue'; # the title of the mails + $mailtitle = 'Issue status' if ($choice); # the title of the mails + my $librarymail = $emailaddress; # all notices without mail are sent (in 1 mail) to this mail address. They must then be managed manually. + my $letter = $data->{"letter$i"} if $data->{"letter$i"}; + # this parameter (the last) is the text of the mail that is sent. + # this text contains fields that are replaced by their value. Those fields must be written between brackets + # The following fields are available : + # + my $mailtext=$letter; + # + # END OF PARAMETERS + # + open OUTFILE, ">overdues" or die "Cannot open file overdues: $!"; + + # set the e-mail server -- comment out if not doing e-mail notices + unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , $smtpserver; + # set your own mail server name here + + my $strsth = "SELECT COUNT(*), issues.borrowernumber,firstname,surname,address,address2,city,zipcode, email, MIN(date_due) as longest_issue FROM issues,borrowers,categories WHERE returndate IS NULL AND issues.borrowernumber=borrowers.borrowernumber AND borrowers.categorycode=categories.categorycode "; + $strsth .= " AND issues.branchcode='".$branchcode."' " if ($branchcode); + $strsth .= " AND borrowers.categorycode='".$data->{categorycode}."' " if ($data->{categorycode}); + $strsth .= " AND categories.overduenoticerequired=1 GROUP BY issues.borrowernumber HAVING TO_DAYS(NOW())-TO_DAYS(longest_issue) BETWEEN $mindays and $maxdays "; + my $sth = $dbh->prepare ($strsth); +# warn "".$strsth; + my $sth2 = $dbh->prepare("SELECT biblio.title,biblio.author,items.barcode, issues.timestamp FROM issues,items,biblio WHERE items.itemnumber=issues.itemnumber and biblio.biblionumber=items.biblionumber AND issues.borrowernumber=? AND returndate IS NULL AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN $mindays and $maxdays"); - $sth->execute; - # - # my $itemcount = 0; - # my $row; - my $count = 0; # to keep track of how many notices are printed - my $e_count = 0; # and e-mailed - my $date=format_date(localtime); - my ($itemcount,$borrowernumber,$firstname,$lastname,$address1,$address2,$city,$postcode,$email); - - while (($itemcount,$borrowernumber,$firstname,$lastname,$address1,$address2,$city,$postcode,$email) = $sth->fetchrow) { - if ($data->{"debarred$i"}){ - #action taken is debarring - $rqdebarring->execute($borrowernumber); - warn "debarring $borrowernumber $firstname $lastname"; - } - # print STDERR "$itemcount,$borrowernumber,$firstname,$lastname,$address1,$address2,$city,$postcode,$email\n"; - if ($letter){ - my $notice .= $mailtext; - # print STDERR "$notice\n"; - $notice =~ s/\/$itemcount/g if ($itemcount); - $notice =~ s/\/$firstname/g if ($firstname); - $notice =~ s/\/$lastname/g if ($lastname); - $notice =~ s/\/$address1/g if ($address1); - $notice =~ s/\/$address2/g if ($address2); - $notice =~ s/\/$city/g if ($city); - $notice =~ s/\/$postcode/g if ($postcode); - $notice =~ s/\/$date/g if ($date); - $notice =~ s/\/$branchname/g if ($branchname); - - $sth2->execute($borrowernumber); - my $titles=""; - my ($title, $author, $barcode, $issuedate); - while (($title, $author, $barcode,$issuedate) = $sth2->fetchrow){ - $titles .= " ".format_date($issuedate)." ".($barcode?$barcode:"")." ".($title?$title:"")." ".($author?$author:"")."\n"; - } - # print STDERR "$titles"; - $notice =~ s/\/$titles/g; - $sth2->finish; - # if not using e-mail notices, comment out the following lines - if ($email) { # or you might check for borrowers.preferredcont - if ($nomail) { - print "TO => $email\n"; - print "FROM => $from\n"; - print "SUBJECT => $mailtitle\n"; - print "MESSAGE => $notice\n"; - } else { - my %mail = ( To => $email, - From => $from, - Subject => $mailtitle, - Message => $notice, - ); - sendmail(%mail); - } - $e_count++ - } else { - print OUTFILE $notice; - $count++; - } # and comment this one out, too, if not using e-mail - } - } - $sth->finish; - close OUTFILE; - # if some notices have to be printed & managed by the library, send them to library mail address. - if ($count) { - open ODUES, "overdues" or die "Cannot open file overdues: $!"; - my $notice = "$e_count overdue notices e-mailed\n"; - $notice .= "$count overdue notices in file for printing\n\n"; - - $notice .= ; - if ($nomail) { - print "TO => $email\n" if $email; - print "FROM => $from\n"; - print "SUBJECT => Koha overdue\n"; - print "MESSAGE => $notice\n"; - } else { - my %mail = ( To => $email, - From => $from, - Subject => 'Koha overdues', - Message => $notice, - ); - sendmail(%mail); - } - } - } - } + $sth->execute; + # + # my $itemcount = 0; + # my $row; + my $count = 0; # to keep track of how many notices are printed + my $e_count = 0; # and e-mailed + my $date=format_date(localtime); + my ($itemcount,$borrowernumber,$firstname,$lastname,$address1,$address2,$city,$postcode,$email); + + while (($itemcount, $borrowernumber, $firstname, $lastname, $address1, $address2, $city, $postcode, $email) = $sth->fetchrow) { + if ($data->{"debarred$i"}){ + #action taken is debarring + $rqdebarring->execute($borrowernumber); + warn "debarring $borrowernumber $firstname $lastname"; + } + if ($letter){ + my $notice .= $mailtext; + $notice =~ s/\/$itemcount/g if ($itemcount); + $notice =~ s/\/$firstname/g if ($firstname); + $notice =~ s/\/$lastname/g if ($lastname); + $notice =~ s/\/$address1/g if ($address1); + $notice =~ s/\/$address2/g if ($address2); + $notice =~ s/\/$city/g if ($city); + $notice =~ s/\/$postcode/g if ($postcode); + $notice =~ s/\/$date/g if ($date); + $notice =~ s/\/$branchname/g if ($branchname); + + $sth2->execute($borrowernumber); + my $titles=""; + my ($title, $author, $barcode, $issuedate); + while (($title, $author, $barcode,$issuedate) = $sth2->fetchrow){ + $titles .= " ".format_date($issuedate)." ".($barcode?$barcode:"")." ".($title?$title:"")." ".($author?$author:"")."\n"; + } + $notice =~ s/\/$titles/g; + $sth2->finish; + # if not using e-mail notices, comment out the following lines + if ($email) { # or you might check for borrowers.preferredcont + if ($nomail) { + print "TO => $email\n"; + print "FROM => $from\n"; + print "SUBJECT => $mailtitle\n"; + print "MESSAGE => $notice\n"; + } else { + my %mail = ( To => $email, + From => $from, + Subject => $mailtitle, + Message => $notice, + ); + sendmail(%mail); + } + $e_count++ + } else { + print OUTFILE $notice; + $count++; + } # and comment this one out, too, if not using e-mail + } + } + $sth->finish; + close OUTFILE; + # if some notices have to be printed & managed by the library, send them to library mail address. + if ($count) { + open ODUES, "overdues" or die "Cannot open file overdues: $!"; + my $notice = "$e_count overdue notices e-mailed\n"; + $notice .= "$count overdue notices in file for printing\n\n"; + + $notice .= ; + if ($nomail) { + print "TO => $email\n" if $email; + print "FROM => $from\n"; + print "SUBJECT => Koha overdue\n"; + print "MESSAGE => $notice\n"; + } else { + my %mail = (To => $email, + From => $from, + Subject => 'Koha overdues', + Message => $notice, + ); + sendmail(%mail); + } + } + } + } } diff --git a/tools/overduerules.pl b/tools/overduerules.pl index 6749a05b5f..f63f581016 100755 --- a/tools/overduerules.pl +++ b/tools/overduerules.pl @@ -38,103 +38,103 @@ my $op = $input->param('op'); # $flagsrequired->{circulation}=1; my ($template, $loggedinuser, $cookie) = get_template_and_user({template_name => "tools/overduerules.tmpl", - query => $input, - type => "intranet", - authnotrequired => 0, - flagsrequired => {parameters => 1, tools => 1}, - debug => 1, - }); + query => $input, + type => "intranet", + authnotrequired => 0, + flagsrequired => {parameters => 1, tools => 1}, + debug => 1, + }); my $err=0; # save the values entered into tables my %temphash; if ($op eq 'save') { - my @names=$input->param(); - my $sth_search = $dbh->prepare("select count(*) as total from overduerules where branchcode=? and categorycode=?"); + my @names=$input->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=?"); - foreach my $key (@names){ - # ISSUES - if ($key =~ /(.*)([1-3])-(.*)/) { - my $type = $1; # data type - my $num = $2; # From 1 to 3 - my $bor = $3; # borrower category - $temphash{$bor}->{"$type$num"}=$input->param("$key") if (($input->param("$key") ne "") or ($input->param("$key")>0)); - } - } - foreach my $bor (keys %temphash){ - # Do some Checking here : delay1 < delay2 {delay1}=~/[^0-9]/ and $temphash{$bor}->{delay1} ne ""){ - $template->param("ERROR"=>1,"ERRORDELAY"=>"delay1","BORERR"=>$bor); - $err=1; - } elsif ($temphash{$bor}->{delay2}=~/[^0-9]/ and $temphash{$bor}->{delay2} ne ""){ - $template->param("ERROR"=>1,"ERRORDELAY"=>"delay2","BORERR"=>$bor); - $err=1; - } elsif ($temphash{$bor}->{delay3}=~/[^0-9]/ and $temphash{$bor}->{delay3} ne ""){ - $template->param("ERROR"=>1,"ERRORDELAY"=>"delay3","BORERR"=>$bor); - $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); - $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"}: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), - $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) - ); - } - } - } - } - unless ($err) {$template->param(datasaved=>1);} + 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=?"); + foreach my $key (@names){ + # ISSUES + if ($key =~ /(.*)([1-3])-(.*)/) { + my $type = $1; # data type + my $num = $2; # From 1 to 3 + my $bor = $3; # borrower category + $temphash{$bor}->{"$type$num"}=$input->param("$key") if (($input->param("$key") ne "") or ($input->param("$key")>0)); + } + } + foreach my $bor (keys %temphash){ + # Do some Checking here : delay1 < delay2 {delay1}=~/[^0-9]/ and $temphash{$bor}->{delay1} ne ""){ + $template->param("ERROR"=>1,"ERRORDELAY"=>"delay1","BORERR"=>$bor); + $err=1; + } elsif ($temphash{$bor}->{delay2}=~/[^0-9]/ and $temphash{$bor}->{delay2} ne ""){ + $template->param("ERROR"=>1,"ERRORDELAY"=>"delay2","BORERR"=>$bor); + $err=1; + } elsif ($temphash{$bor}->{delay3}=~/[^0-9]/ and $temphash{$bor}->{delay3} ne ""){ + $template->param("ERROR"=>1,"ERRORDELAY"=>"delay3","BORERR"=>$bor); + $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); + $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"}: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), + $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) + ); + } + } + } + } + unless ($err) {$template->param(datasaved=>1);} } my $branches = GetBranches(); my @branchloop; foreach my $thisbranch (keys %$branches) { - my $selected = 1 if $thisbranch eq $branch; - my %row =(value => $thisbranch, - selected => $selected, - branchname => $branches->{$thisbranch}->{'branchname'}, - ); - push @branchloop, \%row; + my $selected = 1 if $thisbranch eq $branch; + my %row =(value => $thisbranch, + selected => $selected, + branchname => $branches->{$thisbranch}->{'branchname'}, + ); + push @branchloop, \%row; } my $letters = GetLetters("circulation"); my $countletters = scalar $letters; -my $sth=$dbh->prepare("Select description,categorycode from categories where overduenoticerequired>0 order by description"); +my $sth=$dbh->prepare("SELECT description,categorycode FROM categories WHERE overduenoticerequired>0 ORDER BY description"); $sth->execute; my @line_loop; my $toggle= 1; @@ -171,7 +171,7 @@ while (my $data=$sth->fetchrow_hashref){ } } else { #getting values from table - my $sth2=$dbh->prepare("SELECT * from overduerules WHERE branchcode=? and categorycode=?"); + my $sth2=$dbh->prepare("SELECT * from overduerules WHERE branchcode=? AND categorycode=?"); $sth2->execute($branch,$data->{'categorycode'}); my $dat=$sth2->fetchrow_hashref; for (my $i=1;$i<=3;$i++){ -- 2.39.5