-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
# 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 $triggered = 0;
my $listall = 0;
my $itemscontent = join( ',', qw( issuedate title barcode author ) );
+my @myborcat;
+my @myborcatout;
GetOptions(
'help|?' => \$help,
'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.
my $sth2 = $dbh->prepare( <<'END_SQL' );
SELECT biblio.*, items.*, issues.*, TO_DAYS(NOW())-TO_DAYS(date_due) AS days_overdue
- FROM issues,items,biblio
- WHERE items.itemnumber=issues.itemnumber
- AND biblio.biblionumber = items.biblionumber
- AND issues.borrowernumber = ?
+ 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 $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 = (
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) {