X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=misc%2Fcronjobs%2Foverdue_notices.pl;h=53f39f436fa2211888b80ff51da0581066133a9d;hb=76d1de381fb51c62e2b85ad25461c116a472651d;hp=1f870385aa157ea8068b8919de3beac9939ed47a;hpb=8ae66932cd7108dd8d502edc45f92d26b5fb6c5f;p=koha.git diff --git a/misc/cronjobs/overdue_notices.pl b/misc/cronjobs/overdue_notices.pl index 1f870385aa..53f39f436f 100755 --- a/misc/cronjobs/overdue_notices.pl +++ b/misc/cronjobs/overdue_notices.pl @@ -53,6 +53,8 @@ overdue_notices.pl [ -n ] [ -library ] [ -max ] [ -library only deal with overdues from this library -csv populate CSV file -itemscontent item information in templates + -borcat category code that must be included + -borcatout category code that must be excluded =head1 OPTIONS @@ -104,6 +106,31 @@ defaults to issuedate,title,barcode,author 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 @@ -206,6 +233,7 @@ alert them of items that have just become due. # 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; @@ -213,7 +241,11 @@ my $nomail = 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, @@ -224,6 +256,10 @@ GetOptions( '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; @@ -257,12 +293,13 @@ if ($mybranch) { # these are the fields that will be substituted into <> 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 { @@ -276,7 +313,6 @@ if ( defined $csvfilename ) { } 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. @@ -284,20 +320,34 @@ foreach my $branchcode (@branches) { $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 = ( @@ -314,13 +364,15 @@ END_SQL # $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). # 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) { @@ -331,42 +383,49 @@ END_SQL $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; + 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, @@ -377,16 +436,16 @@ END_SQL } } ); - + my @misses = grep { /./ } map { /^([^>]*)[>]+/; ( $1 || '' ); } split /\{'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, @@ -413,7 +472,7 @@ END_SQL } ); } 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( @@ -433,7 +492,6 @@ END_SQL ); } } - } $sth->finish; } @@ -556,7 +614,7 @@ sub prepare_letter_for_printing { 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";