-library <branchname> only deal with overdues from this library
-csv <filename> populate CSV file
-itemscontent <list of fields> item information in templates
+ -borcat <categorycode> category code that must be included
+ -borcatout <categorycode> category code that must be excluded
=head1 OPTIONS
Other possible values come from fields in the biblios, items, and
issues tables.
+=item B<-borcat>
+
+Repetable field, that permit to select only few of patrons categories.
+
+=item B<-borcatout>
+
+Repetable field, permis to exclude some patrons categories.
+
+=item B<-t> | B<--triggered>
+
+This option causes a notice to be generated if and only if
+an item is overdue by the number of days defined in a notice trigger.
+
+By default, a notice is sent each time the script runs, which is suitable for
+less frequent run cron script, but requires syncing notice triggers with
+the cron schedule to ensure proper behavior.
+Add the --triggered option for daily cron, at the risk of no notice
+being generated if the cron fails to run on time.
+
+=item B<-list-all>
+
+Default items.content lists only those items that fall in the
+range of the currently processing notice.
+Choose list-all to include all overdue items in the list (limited by B<-max> setting).
+
=back
=head1 DESCRIPTION
# These variables are set by command line options.
# They are initially set to default values.
+my $dbh = C4::Context->dbh();
my $help = 0;
my $man = 0;
my $verbose = 0;
my $MAX = 90;
my $mybranch;
my $csvfilename;
+my $triggered = 0;
+my $listall = 0;
my $itemscontent = join( ',', qw( issuedate title barcode author ) );
+my @myborcat;
+my @myborcatout;
GetOptions(
'help|?' => \$help,
'library=s' => \$mybranch,
'csv:s' => \$csvfilename, # this optional argument gets '' if not supplied.
'itemscontent=s' => \$itemscontent,
+ 'list-all' => \$listall,
+ 't|triggered' => \$triggered,
+ 'borcat=s' => \@myborcat,
+ 'borcatout=s' => \@myborcatout,
) or pod2usage(2);
pod2usage(1) if $help;
pod2usage( -verbose => 2 ) if $man;
# these are the fields that will be substituted into <<item.content>>
my @item_content_fields = split( /,/, $itemscontent );
-my $dbh = C4::Context->dbh();
+binmode( STDOUT, ":utf8" );
our $csv; # the Text::CSV_XS object
our $csv_fh; # the filehandle to the CSV file.
if ( defined $csvfilename ) {
- $csv = Text::CSV_XS->new( { binary => 1 } );
+ my $sep_char = C4::Context->preference('delimiter') || ',';
+ $csv = Text::CSV_XS->new( { binary => 1 , sep_char => $sep_char } );
if ( $csvfilename eq '' ) {
$csv_fh = *STDOUT;
} else {
}
foreach my $branchcode (@branches) {
-
my $branch_details = C4::Branch::GetBranchDetail($branchcode);
my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
my @output_chunks; # may be sent to mail or stdout or csv file.
$verbose and warn sprintf "branchcode : '%s' using %s\n", $branchcode, $admin_email_address;
my $sth2 = $dbh->prepare( <<'END_SQL' );
-SELECT biblio.*, items.*, issues.*
- FROM issues,items,biblio
- WHERE items.itemnumber=issues.itemnumber
- AND biblio.biblionumber = items.biblionumber
- AND issues.borrowernumber = ?
+SELECT biblio.*, items.*, issues.*, TO_DAYS(NOW())-TO_DAYS(date_due) AS days_overdue
+ FROM issues
+ LEFT JOIN items USING(itemnumber)
+ LEFT JOIN biblio USING(biblionumber)
+ WHERE issues.borrowernumber = ?
AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN ? and ?
END_SQL
- my $rqoverduerules = $dbh->prepare("SELECT * FROM overduerules WHERE delay1 IS NOT NULL AND branchcode = ? ");
- $rqoverduerules->execute($branchcode);
- my $outfile = 'overdues_' . ( $mybranch || $branchcode || 'default' );
+ my $query = "SELECT * FROM overduerules WHERE delay1 IS NOT NULL AND branchcode = ? ";
+ $query .= " AND categorycode IN (".join( ',' , ('?') x @myborcat ).") " if (@myborcat);
+ $query .= " AND categorycode NOT IN (".join( ',' , ('?') x @myborcatout ).") " if (@myborcatout);
+
+ my $rqoverduerules = $dbh->prepare($query);
+ $rqoverduerules->execute($branchcode, @myborcat, @myborcatout);
+
+ # We get default rules is there is no rule for this branch
+ if($rqoverduerules->rows == 0){
+ $query = "SELECT * FROM overduerules WHERE delay1 IS NOT NULL AND branchcode = '' ";
+ $query .= " AND categorycode IN (".join( ',' , ('?') x @myborcat ).") " if (@myborcat);
+ $query .= " AND categorycode NOT IN (".join( ',' , ('?') x @myborcatout ).") " if (@myborcatout);
+
+ $rqoverduerules = $dbh->prepare($query);
+ $rqoverduerules->execute(@myborcat, @myborcatout);
+ }
+
+ # my $outfile = 'overdues_' . ( $mybranch || $branchcode || 'default' );
while ( my $overdue_rules = $rqoverduerules->fetchrow_hashref ) {
PERIOD: foreach my $i ( 1 .. 3 ) {
-
$verbose and warn "branch '$branchcode', pass $i\n";
my $mindays = $overdue_rules->{"delay$i"}; # the notice will be sent after mindays days (grace period)
my $maxdays = (
# $letter->{'content'} 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 :
+ # itemcount is interpreted here as the number of items in the overdue range defined by the current notice or all overdues < max if(-list-all).
# <date> <itemcount> <firstname> <lastname> <address1> <address2> <address3> <city> <postcode>
my $borrower_sql = <<'END_SQL';
SELECT COUNT(*), issues.borrowernumber, firstname, surname, address, address2, city, zipcode, email, MIN(date_due) as longest_issue
-FROM issues,borrowers,categories
-WHERE issues.borrowernumber=borrowers.borrowernumber
-AND borrowers.categorycode=categories.categorycode
+FROM issues
+LEFT JOIN borrowers USING (borrowernumber)
+LEFT JOIN categories ON (borrowers.categorycode=categories.categorycode)
+WHERE 1
END_SQL
my @borrower_parameters;
if ($branchcode) {
$borrower_sql .= ' AND borrowers.categorycode=? ';
push @borrower_parameters, $overdue_rules->{categorycode};
}
- $borrower_sql .= <<'END_SQL';
-AND categories.overduenoticerequired=1
-GROUP BY issues.borrowernumber
-HAVING TO_DAYS(NOW())-TO_DAYS(longest_issue) BETWEEN ? and ?
-END_SQL
- push @borrower_parameters, $mindays, $maxdays;
- my $sth = $dbh->prepare($borrower_sql);
- $sth->execute(@borrower_parameters);
- $verbose and warn $borrower_sql . "\n\n ($mindays, $maxdays)\nreturns " . $sth->rows . " rows";
+ $borrower_sql .= ' AND categories.overduenoticerequired=1
+ GROUP BY issues.borrowernumber ';
+ if($triggered) {
+ $borrower_sql .= ' HAVING TO_DAYS(NOW())-TO_DAYS(longest_issue) = ?';
+ push @borrower_parameters, $mindays;
+ } else {
+ $borrower_sql .= ' HAVING TO_DAYS(NOW())-TO_DAYS(longest_issue) BETWEEN ? and ? ' ;
+ push @borrower_parameters, $mindays, $maxdays;
+ }
- while ( my ( $itemcount, $borrowernumber, $firstname, $lastname, $address1, $address2, $city, $postcode, $email ) = $sth->fetchrow ) {
- warn "borrower $firstname, $lastname ($borrowernumber) has $itemcount items overdue." if $verbose;
+ # $sth gets borrower info iff at least one overdue item has triggered the overdue action.
+ my $sth = $dbh->prepare($borrower_sql);
+ $sth->execute(@borrower_parameters);
+ $verbose and warn $borrower_sql . "\n $branchcode | " . $overdue_rules->{'categorycode'} . "\n ($mindays, $maxdays)\nreturns " . $sth->rows . " rows";
+ while( my ( $itemcount, $borrowernumber, $firstname, $lastname, $address1, $address2, $city, $postcode, $email ) = $sth->fetchrow ) {
+ $verbose and warn "borrower $firstname, $lastname ($borrowernumber) has $itemcount items triggering level $i.";
+
my $letter = C4::Letters::getletter( 'circulation', $overdue_rules->{"letter$i"} );
unless ($letter) {
$verbose and warn "Message '$overdue_rules->{letter$i}' content not found";
-
+
# might as well skip while PERIOD, no other borrowers are going to work.
+ # FIXME : Does this mean a letter must be defined in order to trigger a debar ?
next PERIOD;
}
-
+
if ( $overdue_rules->{"debarred$i"} ) {
-
+
#action taken is debarring
C4::Members::DebarMember($borrowernumber);
$verbose and warn "debarring $borrowernumber $firstname $lastname\n";
}
-
- $sth2->execute( $borrowernumber, $mindays, $maxdays );
+ $sth2->execute( ($listall) ? ( $borrowernumber , 1 , $MAX ) : ( $borrowernumber, $mindays, $maxdays ) );
+ my $itemcount = 0;
my $titles = "";
while ( my $item_info = $sth2->fetchrow_hashref() ) {
- my @item_info = map { $_ =~ /date$/ ? format_date( $item_info->{$_} ) : $item_info->{$_} || '' } @item_content_fields;
- $titles .= join "\t", @item_info . "\n";
+ my @item_info = map { $_ =~ /^date|date$/ ? format_date( $item_info->{$_} ) : $item_info->{$_} || '' } @item_content_fields;
+ $titles .= join("\t", @item_info) . "\n";
+ $itemcount++;
}
$sth2->finish;
-
+
$letter = parse_letter(
{ letter => $letter,
borrowernumber => $borrowernumber,
}
}
);
-
+
my @misses = grep { /./ } map { /^([^>]*)[>]+/; ( $1 || '' ); } split /\</, $letter->{'content'};
if (@misses) {
$verbose and warn "The following terms were not matched and replaced: \n\t" . join "\n\t", @misses;
}
$letter->{'content'} =~ s/\<[^<>]*?\>//g; # Now that we've warned about them, remove them.
$letter->{'content'} =~ s/\<[^<>]*?\>//g; # 2nd pass for the double nesting.
-
+
if ($nomail) {
-
+
push @output_chunks,
prepare_letter_for_printing(
{ letter => $letter,
}
);
} else {
-
+
# If we don't have an email address for this patron, send it to the admin to deal with.
push @output_chunks,
prepare_letter_for_printing(
);
}
}
-
}
$sth->finish;
}
if ( exists $params->{'outputformat'} && $params->{'outputformat'} eq 'csv' ) {
if ($csv->combine(
$params->{'firstname'}, $params->{'lastname'}, $params->{'address1'}, $params->{'address2'}, $params->{'postcode'},
- $params->{'city'}, $params->{'email'}, $params->{'itemcount'}, $params->{'items.content'}
+ $params->{'city'}, $params->{'email'}, $params->{'itemcount'}, $params->{'titles'}
)
) {
return $csv->string, "\n";
projects / koha.git / blobdiff