View | Details | Raw Unified | Return to bug 12529
Collapse All | Expand All

(-)a/installer/data/mysql/sysprefs.sql (+1 lines)
Lines 306-311 INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, ` Link Here
306
('OverDriveClientSecret','','Client key for OverDrive integration','30','YesNo'),
306
('OverDriveClientSecret','','Client key for OverDrive integration','30','YesNo'),
307
('OverDriveLibraryID','','Library ID for OverDrive integration','','Integer'),
307
('OverDriveLibraryID','','Library ID for OverDrive integration','','Integer'),
308
('OverdueNoticeBcc','','','Email address to bcc outgoing overdue notices sent by email','free'),
308
('OverdueNoticeBcc','','','Email address to bcc outgoing overdue notices sent by email','free'),
309
('OverdueNoticeCalendar',0,NULL,'Take the calendar into consideration when generating overdue notices','YesNo'),
309
('OverduesBlockCirc','noblock','noblock|confirmation|block','When checking out an item should overdues block checkout, generate a confirmation dialogue, or allow checkout','Choice'),
310
('OverduesBlockCirc','noblock','noblock|confirmation|block','When checking out an item should overdues block checkout, generate a confirmation dialogue, or allow checkout','Choice'),
310
('patronimages','0',NULL,'Enable patron images for the Staff Client','YesNo'),
311
('patronimages','0',NULL,'Enable patron images for the Staff Client','YesNo'),
311
('PatronSelfRegistration','0',NULL,'If enabled, patrons will be able to register themselves via the OPAC.','YesNo'),
312
('PatronSelfRegistration','0',NULL,'If enabled, patrons will be able to register themselves via the OPAC.','YesNo'),
(-)a/installer/data/mysql/updatedatabase.pl (+10 lines)
Lines 8570-8575 if ( CheckVersion($DBversion) ) { Link Here
8570
    SetVersion($DBversion);
8570
    SetVersion($DBversion);
8571
}
8571
}
8572
8572
8573
$DBversion = '3.17.00.XXX';
8574
if ( CheckVersion($DBversion) ) {
8575
    $dbh->do(
8576
"INSERT INTO systempreferences (variable,value,explanation,type) VALUES ('OverdueNoticeCalendar',0,'Take calendar into consideration when working out sending overdue notices','YesNo') "
8577
    );
8578
    print
8579
"Upgrade to $DBversion done (Bug XXX - Adding a syspref to allow the overdue notices to consider the calendar when generating notices)\n";
8580
    SetVersion($DBversion);
8581
}
8582
8573
=head1 FUNCTIONS
8583
=head1 FUNCTIONS
8574
8584
8575
=head2 TableExists($table)
8585
=head2 TableExists($table)
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref (-1 / +7 lines)
Lines 310-316 Circulation: Link Here
310
        -
310
        -
311
            - Send all notices as a BCC to this email address
311
            - Send all notices as a BCC to this email address
312
            - pref: OverdueNoticeBcc
312
            - pref: OverdueNoticeBcc
313
        - 
313
        -
314
            - pref: OverdueNoticeCalendar
315
              choices:
316
                  yes: "Use Calendar"
317
                  no: "Ignore Calendar"
318
            - when working out the period for overdue notices 
319
        -
314
            - Include up to
320
            - Include up to
315
            - pref: PrintNoticesMaxLines
321
            - pref: PrintNoticesMaxLines
316
              class: integer
322
              class: integer
(-)a/misc/cronjobs/overdue_notices.pl (-20 / +83 lines)
Lines 44-49 use C4::Budgets qw(GetCurrency); Link Here
44
44
45
use Koha::Borrower::Debarments qw(AddUniqueDebarment);
45
use Koha::Borrower::Debarments qw(AddUniqueDebarment);
46
use Koha::DateUtils;
46
use Koha::DateUtils;
47
use Koha::Calendar;
47
48
48
=head1 NAME
49
=head1 NAME
49
50
Lines 353-364 if (@branchcodes) { Link Here
353
        @branches = ('');
354
        @branches = ('');
354
    }
355
    }
355
}
356
}
356
357
my $date_to_run;
357
if ($date){
358
if ($date){
358
    $date=$dbh->quote($date);
359
    $date=$dbh->quote($date);
360
    $date_to_run = dt_from_string($date);
359
}
361
}
360
else {
362
else {
361
    $date="NOW()";
363
    $date="NOW()";
364
    $date_to_run = DateTime->now( time_zone => C4::Context->tz() );
362
}
365
}
363
366
364
# these are the fields that will be substituted into <<item.content>>
367
# these are the fields that will be substituted into <<item.content>>
Lines 417-425 elsif ( defined $text_filename ) { Link Here
417
}
420
}
418
421
419
foreach my $branchcode (@branches) {
422
foreach my $branchcode (@branches) {
423
    if ( C4::Context->preference('OverdueNoticeCalendar') ) {
424
        my $calendar = Koha::Calendar->new( branchcode => $branchcode );
425
        if ( $calendar->is_holiday($date_to_run) ) {
426
            next;
427
        }
428
    }
420
429
421
    my $branch_details = C4::Branch::GetBranchDetail($branchcode);
430
    my $branch_details      = C4::Branch::GetBranchDetail($branchcode);
422
    my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
431
    my $admin_email_address = $branch_details->{'branchemail'}
432
      || C4::Context->preference('KohaAdminEmailAddress');
423
    my @output_chunks;    # may be sent to mail or stdout or csv file.
433
    my @output_chunks;    # may be sent to mail or stdout or csv file.
424
434
425
    $verbose and warn sprintf "branchcode : '%s' using %s\n", $branchcode, $admin_email_address;
435
    $verbose and warn sprintf "branchcode : '%s' using %s\n", $branchcode, $admin_email_address;
Lines 431-437 SELECT biblio.*, items.*, issues.*, biblioitems.itemtype, TO_DAYS($date)-TO_DAYS Link Here
431
    AND biblio.biblionumber   = items.biblionumber
441
    AND biblio.biblionumber   = items.biblionumber
432
    AND biblio.biblionumber   = biblioitems.biblionumber
442
    AND biblio.biblionumber   = biblioitems.biblionumber
433
    AND issues.borrowernumber = ?
443
    AND issues.borrowernumber = ?
434
    AND TO_DAYS($date)-TO_DAYS(date_due) BETWEEN ? and ?
435
END_SQL
444
END_SQL
436
445
437
    my $query = "SELECT * FROM overduerules WHERE delay1 IS NOT NULL AND branchcode = ? ";
446
    my $query = "SELECT * FROM overduerules WHERE delay1 IS NOT NULL AND branchcode = ? ";
Lines 454-459 END_SQL Link Here
454
    # my $outfile = 'overdues_' . ( $mybranch || $branchcode || 'default' );
463
    # my $outfile = 'overdues_' . ( $mybranch || $branchcode || 'default' );
455
    while ( my $overdue_rules = $rqoverduerules->fetchrow_hashref ) {
464
    while ( my $overdue_rules = $rqoverduerules->fetchrow_hashref ) {
456
      PERIOD: foreach my $i ( 1 .. 3 ) {
465
      PERIOD: foreach my $i ( 1 .. 3 ) {
466
457
            $verbose and warn "branch '$branchcode', pass $i\n";
467
            $verbose and warn "branch '$branchcode', pass $i\n";
458
            my $mindays = $overdue_rules->{"delay$i"};    # the notice will be sent after mindays days (grace period)
468
            my $mindays = $overdue_rules->{"delay$i"};    # the notice will be sent after mindays days (grace period)
459
            my $maxdays = (
469
            my $maxdays = (
Lines 474-485 END_SQL Link Here
474
            # <date> <itemcount> <firstname> <lastname> <address1> <address2> <address3> <city> <postcode> <country>
484
            # <date> <itemcount> <firstname> <lastname> <address1> <address2> <address3> <city> <postcode> <country>
475
485
476
            my $borrower_sql = <<'END_SQL';
486
            my $borrower_sql = <<'END_SQL';
477
SELECT distinct(issues.borrowernumber), firstname, surname, address, address2, city, zipcode, country, email, emailpro, B_email, smsalertnumber
487
SELECT issues.borrowernumber, firstname, surname, address, address2, city, zipcode, country, email, emailpro, B_email, smsalertnumber, 
488
TO_DAYS(?)-TO_DAYS(date_due) as difference, date_due
478
FROM   issues,borrowers,categories
489
FROM   issues,borrowers,categories
479
WHERE  issues.borrowernumber=borrowers.borrowernumber
490
WHERE  issues.borrowernumber=borrowers.borrowernumber
480
AND    borrowers.categorycode=categories.categorycode
491
AND    borrowers.categorycode=categories.categorycode
481
END_SQL
492
END_SQL
482
            my @borrower_parameters;
493
            my @borrower_parameters;
494
            push @borrower_parameters, $date_to_run->datetime(); 
483
            if ($branchcode) {
495
            if ($branchcode) {
484
                $borrower_sql .= ' AND issues.branchcode=? ';
496
                $borrower_sql .= ' AND issues.branchcode=? ';
485
                push @borrower_parameters, $branchcode;
497
                push @borrower_parameters, $branchcode;
Lines 488-509 END_SQL Link Here
488
                $borrower_sql .= ' AND borrowers.categorycode=? ';
500
                $borrower_sql .= ' AND borrowers.categorycode=? ';
489
                push @borrower_parameters, $overdue_rules->{categorycode};
501
                push @borrower_parameters, $overdue_rules->{categorycode};
490
            }
502
            }
491
            $borrower_sql .= '  AND categories.overduenoticerequired=1 ';
503
            $borrower_sql .= '  AND categories.overduenoticerequired=1 ORDER BY issues.borrowernumber';
492
            if($triggered) {
504
            
493
                $borrower_sql .= " AND TO_DAYS($date)-TO_DAYS(date_due) = ?";
494
                push @borrower_parameters, $mindays;
495
            } else {
496
                $borrower_sql .= " AND TO_DAYS($date)-TO_DAYS(date_due) BETWEEN ? and ? " ;
497
                push @borrower_parameters, $mindays, $maxdays;
498
            }
499
500
            # $sth gets borrower info iff at least one overdue item has triggered the overdue action.
505
            # $sth gets borrower info iff at least one overdue item has triggered the overdue action.
501
	        my $sth = $dbh->prepare($borrower_sql);
506
	        my $sth = $dbh->prepare($borrower_sql);
502
            $sth->execute(@borrower_parameters);
507
            $sth->execute(@borrower_parameters);
503
            $verbose and warn $borrower_sql . "\n $branchcode | " . $overdue_rules->{'categorycode'} . "\n ($mindays, $maxdays)\nreturns " . $sth->rows . " rows";
508
            
504
509
            $verbose and warn $borrower_sql . "\n $branchcode | " . $overdue_rules->{'categorycode'} . "\n ($mindays, $maxdays, ".  $date_to_run->datetime() .")\nreturns " . $sth->rows . " rows";
510
            my $borrowernumber;
505
            while ( my $data = $sth->fetchrow_hashref ) {
511
            while ( my $data = $sth->fetchrow_hashref ) {
506
                my $borrowernumber = $data->{'borrowernumber'};
512
513
                # check the borrower has at least one item that matches
514
                my $days_between;
515
                if ( C4::Context->preference('OverdueNoticeCalendar') )
516
                {
517
                    my $calendar =
518
                      Koha::Calendar->new( branchcode => $branchcode );
519
                    $days_between =
520
                      $calendar->days_between(  dt_from_string($data->{date_due}),
521
                        $date_to_run );
522
                }
523
                else {
524
                    $days_between =
525
                      $date_to_run->delta_days(  dt_from_string($data->{date_due}) );
526
                }
527
                $days_between = $days_between->in_units('days');
528
                if ($triggered) {
529
                    if ( $mindays != $days_between ) {
530
                        next;
531
                    }
532
                }
533
                else {
534
                    unless (   $days_between >= $mindays
535
                        && $days_between <= $maxdays )
536
                    {
537
                        next;
538
                    }
539
                }
540
                if ($borrowernumber eq $data->{'borrowernumber'}){
541
# we have already dealt with this borrower
542
                    $verbose and warn "already dealt with this borrower $borrowernumber";
543
                    next;
544
                }
545
                $borrowernumber = $data->{'borrowernumber'};
507
                my $borr =
546
                my $borr =
508
                    $data->{'firstname'} . ', '
547
                    $data->{'firstname'} . ', '
509
                  . $data->{'surname'} . ' ('
548
                  . $data->{'surname'} . ' ('
Lines 548-555 END_SQL Link Here
548
                    );
587
                    );
549
                    $verbose and warn "debarring $borr\n";
588
                    $verbose and warn "debarring $borr\n";
550
                }
589
                }
551
                my @params = ($listall ? ( $borrowernumber , 1 , $MAX ) : ( $borrowernumber, $mindays, $maxdays ));
590
#                my @params = ($listall ? ( $borrowernumber , 1 , $MAX ) : ( $borrowernumber, $mindays, $maxdays ));
552
                $verbose and warn "STH2 PARAMS: borrowernumber = $borrowernumber, mindays = $mindays, maxdays = $maxdays";
591
                my @params = ($borrowernumber);
592
                $verbose and warn "STH2 PARAMS: borrowernumber = $borrowernumber";
553
                $sth2->execute(@params);
593
                $sth2->execute(@params);
554
                my $itemcount = 0;
594
                my $itemcount = 0;
555
                my $titles = "";
595
                my $titles = "";
Lines 558-563 END_SQL Link Here
558
                my $j = 0;
598
                my $j = 0;
559
                my $exceededPrintNoticesMaxLines = 0;
599
                my $exceededPrintNoticesMaxLines = 0;
560
                while ( my $item_info = $sth2->fetchrow_hashref() ) {
600
                while ( my $item_info = $sth2->fetchrow_hashref() ) {
601
                    if ( C4::Context->preference('OverdueNoticeCalendar') ) {
602
                        my $calendar =
603
                          Koha::Calendar->new( branchcode => $branchcode );
604
                        $days_between =
605
                          $calendar->days_between(
606
                            dt_from_string( $item_info->{date_due} ), $date_to_run );
607
                    }
608
                    else {
609
                        $days_between =
610
                          $date_to_run->delta_days(
611
                            dt_from_string( $item_info->{date_due} ) );
612
                    }
613
                    $days_between = $days_between->in_units('days');
614
                    if ($listall){
615
                        unless ($days_between >= 1 and $days_between <= $MAX){
616
                            next;
617
                        }
618
                    }
619
                    else {
620
                        unless ($days_between >=$mindays && $days_between <= $maxdays){
621
                            next;
622
                        }
623
                    }
624
561
                    if ( ( scalar(@emails_to_use) == 0 || $nomail ) && $PrintNoticesMaxLines && $j >= $PrintNoticesMaxLines ) {
625
                    if ( ( scalar(@emails_to_use) == 0 || $nomail ) && $PrintNoticesMaxLines && $j >= $PrintNoticesMaxLines ) {
562
                      $exceededPrintNoticesMaxLines = 1;
626
                      $exceededPrintNoticesMaxLines = 1;
563
                      last;
627
                      last;
564
- 

Return to bug 12529