From 3c58e8faadbbbb7ffc5c1ab9e2eb287ac319ec41 Mon Sep 17 00:00:00 2001 From: Paul POULAIN Date: Sun, 10 Feb 2008 01:41:47 -0600 Subject: [PATCH 1/1] overduenotices-csv from 2.2 + DL file from overdue.pl Signed-off-by: Chris Cormack Signed-off-by: Joshua Ferraro --- circ/overdue.pl | 55 ++++--- .../prog/en/modules/circ/overdue.tmpl | 34 ++-- misc/cronjobs/overduenotices-csv.pl | 174 +++++++++++++++++++++ 3 files changed, 227 insertions(+), 36 deletions(-) create mode 100755 misc/cronjobs/overduenotices-csv.pl diff --git a/circ/overdue.pl b/circ/overdue.pl index dd9dd203b8..178d5816f2 100755 --- a/circ/overdue.pl +++ b/circ/overdue.pl @@ -37,6 +37,7 @@ my $borcatfilter = $input->param('borcat'); my $itemtypefilter = $input->param('itemtype'); my $borflagsfilter = $input->param('borflags') || " "; my $branchfilter = $input->param('branch'); +my $op = $input->param('op'); my ( $template, $loggedinuser, $cookie ) = get_template_and_user( { @@ -50,6 +51,18 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user( ); my $dbh = C4::Context->dbh; + +# download the complete CSV +if ($op eq 'csv') { +warn "BRANCH : $branchfilter"; + my $csv = `../misc/cronjobs/overduenotices-csv.pl -c -n -b $branchfilter`; + print $input->header(-type => 'application/vnd.sun.xml.calc', + -encoding => 'utf-8', + -attachment=>"overdues.csv", + -filename=>"overdues.csv" ); + print $csv; + exit; +} my $req; $req = $dbh->prepare( "select categorycode, description from categories order by description"); $req->execute; @@ -95,13 +108,14 @@ foreach my $thisbranch ( sort keys %$branches ) { my %row = ( value => $thisbranch, branchname => $branches->{$thisbranch}->{'branchname'}, - selected => (C4::Context->userenv && $branches->{$thisbranch}->{'branchcode'} eq C4::Context->userenv->{'branch'}) + selected => ($branches->{$thisbranch}->{'branchcode'} eq $branchfilter) ); push @branchloop, \%row; } $branchfilter=C4::Context->userenv->{'branch'} if ($onlymine && !$branchfilter); -$template->param( branchloop => \@branchloop ); +$template->param( branchloop => \@branchloop, + branchfilter => $branchfilter); $template->param(borcatloop=> \@borcatloop, itemtypeloop => \@itemtypeloop, branchloop=> \@branchloop, @@ -131,36 +145,36 @@ my $todaysdate = $bornamefilter =~s/\*/\%/g; $bornamefilter =~s/\?/\_/g; -my $strsth="select date_due,concat(surname,' ', firstname) as borrower, - borrowers.phone, borrowers.email,issues.itemnumber, items.barcode, biblio.title, biblio.author,borrowers.borrowernumber,biblio.biblionumber - from issues +my $strsth="SELECT date_due,concat(surname,' ', firstname) as borrower, + borrowers.phone, borrowers.email,issues.itemnumber, items.barcode, biblio.title, biblio.author,borrowers.borrowernumber,biblio.biblionumber,borrowers.branchcode + FROM issues LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber ) LEFT JOIN items ON (issues.itemnumber=items.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber ) -where isnull(returndate) "; +WHERE isnull(returndate) "; $strsth.= " && date_due<'".$todaysdate."' " unless ($showall); $strsth.=" && (borrowers.firstname like '".$bornamefilter."%' or borrowers.surname like '".$bornamefilter."%' or borrowers.cardnumber like '".$bornamefilter."%')" if($bornamefilter) ; $strsth.=" && borrowers.categorycode = '".$borcatfilter."' " if($borcatfilter) ; $strsth.=" && biblioitems.itemtype = '".$itemtypefilter."' " if($itemtypefilter) ; $strsth.=" && borrowers.flags = '".$borflagsfilter."' " if ($borflagsfilter ne " ") ; -$strsth.=" && issues.branchcode = '".$branchfilter."' " if($branchfilter) ; +$strsth.=" && borrowers.branchcode = '".$branchfilter."' " if($branchfilter) ; if ($order eq "borrower"){ - $strsth.=" order by borrower,date_due " ; + $strsth.=" ORDER BY borrower,date_due " ; } elsif ($order eq "title"){ - $strsth.=" order by title,date_due,borrower "; + $strsth.=" ORDER BY title,date_due,borrower "; } elsif ($order eq "barcode"){ - $strsth.=" order by items.barcode,date_due,borrower "; -}elsif ($order eq "borrower desc"){ - $strsth.=" order by borrower desc,date_due " ; -} elsif ($order eq "title desc"){ - $strsth.=" order by title desc,date_due,borrower "; -} elsif ($order eq "barcode desc"){ - $strsth.=" order by items.barcode desc,date_due,borrower "; -} elsif ($order eq "date_due desc"){ - $strsth.=" order by date_due desc,borrower "; + $strsth.=" ORDER BY items.barcode,date_due,borrower "; +}elsif ($order eq "borrower DESC"){ + $strsth.=" ORDER BY borrower desc,date_due " ; +} elsif ($order eq "title DESC"){ + $strsth.=" ORDER BY title desc,date_due,borrower "; +} elsif ($order eq "barcode DESC"){ + $strsth.=" ORDER BY items.barcode desc,date_due,borrower "; +} elsif ($order eq "date_due DESC"){ + $strsth.=" ORDER BY date_due DESC,borrower "; } else { - $strsth.=" order by date_due,borrower "; + $strsth.=" ORDER BY date_due,borrower "; } my $sth=$dbh->prepare($strsth); #warn "overdue.pl : query string ".$strsth; @@ -188,7 +202,8 @@ while (my $data=$sth->fetchrow_hashref) { email => $email, biblionumber => $data->{'biblionumber'}, title => $title, - author => $author }); + author => $author, + branchcode => $data->{'branchcode'} }); } $template->param( diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/overdue.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/circ/overdue.tmpl index 9fa9764388..c5606a438b 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/overdue.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/circ/overdue.tmpl @@ -17,32 +17,32 @@

Items Overdue as of

-
Filter on :
- Name or cardnumber :"> - Patron category :">

+

- - - Patron flags :
- Item type : - - - Library : -
+ +

Show any items currently issued:checked="checked"> - - Sort By -
+
- +">Download file of all overdues (for branch all branches . Other filters are ignored)
+ @@ -71,9 +72,10 @@ - +
Due Date PatronBranch Title
"> ?subject=Overdue: ">[email] () ( + "> ( )

">

diff --git a/misc/cronjobs/overduenotices-csv.pl b/misc/cronjobs/overduenotices-csv.pl new file mode 100755 index 0000000000..aa6df0ba64 --- /dev/null +++ b/misc/cronjobs/overduenotices-csv.pl @@ -0,0 +1,174 @@ +#!/usr/bin/perl -w +#----------------------------------- +# Script Name: overduenotices.pl +# Script Version: 1.0 +# Date: 2003/9/7 +# Author: Stephen Hedges (shedges@skemotah.com) +# modified by Paul Poulain (paul@koha-fr.org) +# Description: +# This script runs send a mail with an attached file of all overdues +# that can be used for overdues claims, with your preffered word processor +# (OpenOffice.org hopefully ;-) ) + +# Revision History: +# 1.0 2003/9/7: original version +#----------------------------------- +# Copyright 2003 Skemotah Solutions +# 2007 Paul POULAIN +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 2 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along with +# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, +# Suite 330, Boston, MA 02111-1307 USA + +use strict; +use C4::Context; +use C4::Date; +use Date::Manip; +use Mail::Sendmail; # comment out if not doing e-mail notices +use Getopt::Long; +use MIME::QuotedPrint; +use MIME::Base64; + +my ($confirm, $nomail,$branch); +GetOptions( + 'c' => \$confirm, + 'n' => \$nomail, + 'b:s' => \$branch, +); +unless ($confirm) { + 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. + +Do you wish to continue? (y/n) +|; + chomp($_ = ); + exit unless (/^y/i); # comment these lines out once you've made the changes + +} +# +# BEGINNING OF PARAMETERS +# +my $mindays = 7; # the notice will be sent after mindays days (grace period) +my $maxdays = 30; # issues being more than maxdays late are managed somewhere else. (borrower probably suspended) +my $smtpserver = 'smtp.laposte.net'; # your smtp server (the server who sent mails) +my $from = 'fromadress@toto'; # all the mails sent to the borrowers will appear coming from here. +my $mailtitle = 'Relances'; # the title of the mails +my $librarymail = 'tonadress@email'; # all notices without mail are sent (in 1 mail) to this mail address. They must then be managed manua lly. +# 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 = ";;
;;;;;;\n"; +# +# END OF PARAMETERS +# +open OUTFILE, ">:utf8","overdues.csv" or die "impossible d'ouvrir le fichier de relances"; +print OUTFILE "Date;Name;Surname;Adress1;Adress2;zipcode;city;Mail;Nbitems;1title;1author;1barcode;1issuedate;1returndate;"; +print OUTFILE "2title;2author;2barcode;2issue_date;2return_date;3title;3author;3barcode;3issue_date;3return_date;4title;4author;4barcode;4issue_date;4return_date;5title;5author;5barcode;5issue_date;5return_date;6title;6author;6barcode;6issue_date;6return_date;7title;7author;7barcode;7issue_date;7return_date;8title;8author;8barcode;8issue_date;8return_date;9title;9author;9barcode;9issue_date;9return_date;10title;10author;10barcode;10issue_date;10return_date;\n"; +# 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 $dbh = C4::Context->dbh; +my $query = "SELECT COUNT(*), issues.borrowernumber,firstname,surname,address,address2,city,zipcode,email FROM issues,borrowers ,categories WHERE returndate IS NULL AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 0 and 500 AND issues.borrowernumber=borrowers.borrowernumber and borrowers.categorycode=categories.categorycode and (categories.overduenoticerequired = 1)"; +$query .= " AND borrowers.branchcode=".$dbh->quote($branch) if $branch; +$query .=" GROUP BY issues.borrowernumber"; +my $sth = $dbh->prepare ($query); + +warn "Q : $query"; +my $sth2 = $dbh->prepare("SELECT biblio.title,biblio.author,items.barcode, issues.timestamp, issues.date_due 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 0 and 500"); + +$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 ($itemcount,$borrnum,$firstname,$lastname,$address1,$address2,$city,$postcode,$email); + +while (($itemcount,$borrnum,$firstname,$lastname,$address1,$address2,$city,$postcode,$email) = $sth->fetchrow) { + my $notice = $mailtext; + $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/\/$email/g if $email; + $notice =~ s/\/$postcode/g if $postcode; + $notice =~ s/\/$city/g if $city; + $notice =~ s/\/$itemcount/g; + + $sth2->execute($borrnum); + my $titles=""; + my ($title, $author, $barcode,$timestamp,$date_due); + while (($title, $author, $barcode,$timestamp,$date_due) = $sth2->fetchrow){ + $titles .= ($title?$title:"").";".($author?$author:"").";".($barcode?$barcode:"").";" ; + $titles .= ($timestamp?format_date(substr($timestamp,0,10)):"").";".($date_due?format_date($date_due):"").";" ; + } + $notice =~ s/\/$titles/g; + $notice =~ s/(\<.*?\>)//g; + $sth2->finish; + print OUTFILE $notice; + $count++; + +} +$sth->finish; + close OUTFILE; +if ($nomail) { + open(OD,"overdues.csv"); + print ; +} else { + my %mail = ( To => 'mailto@mail.com', + From => 'mailfrom@mail.com', + Subject => 'Koha overdues', + ); + my $boundary = "====" . time() . "===="; + $mail{'content-type'} = "multipart/mixed; boundary=\"$boundary\""; + +# open FILE, "Relances.csv"; + my $message = encode_qp("The file"); + + my $file = "overdues.csv"; + + open (F, $file) or die "Cannot read $ $!"; + binmode F; + undef $/; + $mail{body} = encode_base64(); + close F; + + $boundary = '--'.$boundary; + $mail{body} = <