Attachment #45768 for bug #15364

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

Lines 758-763 sub _parseletter_sth { Link Here

(-)a/C4/Letters.pm (+1 lines)
758	# broke things for the rest of us. prepare_cached is a better	758	# broke things for the rest of us. prepare_cached is a better
759	# way to cache statement handles anyway.	759	# way to cache statement handles anyway.
760	my $query =	760	my $query =
		761	($table eq 'accountlines' ) ? "SELECT * FROM $table WHERE borrowernumber = ? AND amountoutstanding > 0" :
761	($table eq 'biblio' ) ? "SELECT * FROM $table WHERE biblionumber = ?" :	762	($table eq 'biblio' ) ? "SELECT * FROM $table WHERE biblionumber = ?" :
762	($table eq 'biblioitems' ) ? "SELECT * FROM $table WHERE biblionumber = ?" :	763	($table eq 'biblioitems' ) ? "SELECT * FROM $table WHERE biblionumber = ?" :
763	($table eq 'items' ) ? "SELECT * FROM $table WHERE itemnumber = ?" :	764	($table eq 'items' ) ? "SELECT * FROM $table WHERE itemnumber = ?" :




            # if active currency isn't correct ISO code fallback to sprintf
            $item->{'fine'} = sprintf('%.2f', $fine) unless $item->{'fine'};

            $item->{'amountoutstanding'} = currency_format($currency_format, $item->{'amountoutstanding'}, FMT_SYMBOL);
            # if active currency isn't correct ISO code fallback to sprintf
            $item->{'amountoutstanding'} = sprintf('%.2f', $item->{'amountoutstanding'}) unless $item->{'amountoutstanding'};

            my $accountline = {
                'amountoutstanding' => $item->{'amountoutstanding'},
                'description' => $item->{'description'},
                };

            push @item_tables, {
                'biblio' => $item->{'biblionumber'},
                'biblioitems' => $item->{'biblionumber'},
                'items' => $item,
                'accountlines' => $accountline,
                'issues' => $item->{'itemnumber'},
            };
        }
    }

    my $amountoutstanding_total = 0;
    my @accountlines_tables;
    if ( my $i = $params->{'accountlines'} ) {
        foreach my $accountline (@$i) {
            $amountoutstanding_total += $accountline->{'amountoutstanding'};

            $accountline->{'amountoutstanding'} = currency_format($currency_format, $accountline->{'amountoutstanding'}, FMT_SYMBOL);
            # if active currency isn't correct ISO code fallback to sprintf
            $accountline->{'amountoutstanding'} = sprintf('%.2f', $accountline->{'amountoutstanding'}) unless $accountline->{'amountoutstanding'};
            $accountline->{'amount'} = currency_format($currency_format, $accountline->{'amount'}, FMT_SYMBOL);
            # if active currency isn't correct ISO code fallback to sprintf
            $accountline->{'amount'} = sprintf('%.2f', $accountline->{'amount'}) unless $accountline->{'amount'};

            push @accountlines_tables, {
                'accountlines' => $accountline
            };
        }
    }
    $amountoutstanding_total = currency_format($currency_format, $amountoutstanding_total, FMT_SYMBOL);
    # if active currency isn't correct ISO code fallback to sprintf
    $amountoutstanding_total = sprintf('%.2f', $amountoutstanding_total) unless $amountoutstanding_total;

    $substitute->{'accountlines.total'} = $amountoutstanding_total;

    return C4::Letters::GetPreparedLetter (
        module => 'circulation',
        letter_code => $params->{'letter_code'},
        branchcode => $params->{'branchcode'},
        tables => \%tables,
        substitute => $substitute,
        repeat => { item => \@item_tables, accountline => \@accountlines_tables},
        message_transport_type => $params->{message_transport_type},
    );
}




use Getopt::Long;
use Pod::Usage;
use Data::Dumper;
use Locale::Currency::Format 1.28;
BEGIN {
    # find Koha's Perl modules
    # test carefully before changing this

use C4::Members;
use C4::Members::Messaging;
use C4::Overdues;
use C4::Budgets qw(GetCurrency);
use Koha::DateUtils;
use C4::Log;



    my $from_address = $upcoming->{branchemail} || $admin_adress;

    my $sth2 = $dbh->prepare('
        SELECT *
        FROM accountlines
        WHERE borrowernumber = ? AND amountoutstanding > 0
        ');
    $sth2->execute($upcoming->{'borrowernumber'});
    my @accountlines = ();
    while ( my $accountline = $sth2->fetchrow_hashref() ) {
        push(@accountlines, $accountline);
    }
    $sth2->finish;

    my $borrower_preferences;
    if ( 0 == $upcoming->{'days_until_due'} ) {
        # This item is due today. Send an 'item due' message.

                                      itemnumber     => $upcoming->{'itemnumber'},
                                      substitute     => { 'items.content' => $titles },
                                      message_transport_type => $transport,
                                      accountlines   => \@accountlines,
                                    } )
                    or warn "no letter of type '$letter_type' found. Please see sample_notices.sql";
                push @letters, $letter if $letter;

                                      itemnumber     => $upcoming->{'itemnumber'},
                                      substitute     => { 'items.content' => $titles },
                                      message_transport_type => $transport,
                                      accountlines   => \@accountlines,
                                    } )
                    or warn "no letter of type '$letter_type' found. Please see sample_notices.sql";
                push @letters, $letter if $letter;

    my $count = $digest->{count};
    my $from_address = $digest->{email};

    my $sth2 = $dbh->prepare('
        SELECT *
        FROM accountlines
        WHERE borrowernumber = ? AND amountoutstanding > 0
        ');
    $sth2->execute($borrowernumber);
    my @accountlines = ();
    while ( my $accountline = $sth2->fetchrow_hashref() ) {
        push(@accountlines, $accountline);
    }
    $sth2->finish;

    my $borrower_preferences = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber,
                                                                                  message_name   => 'advance_notice' } );
    next PATRON unless $borrower_preferences; # how could this happen?

                },
                branchcode             => $branch_info{"branches.branchcode"},
                message_transport_type => $transport,
                accountlines   => \@accountlines,
            }
          )
          or warn "no letter of type '$letter_type' found. Please see sample_notices.sql";

    my $count = $digest->{count};
    my $from_address = $digest->{email};

    my $sth2 = $dbh->prepare('
        SELECT *
        FROM accountlines
        WHERE borrowernumber = ? AND amountoutstanding > 0
        ');
    $sth2->execute($borrowernumber);
    my @accountlines = ();
    while ( my $accountline = $sth2->fetchrow_hashref() ) {
        push(@accountlines, $accountline);
    }
    $sth2->finish;

    my $borrower_preferences = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber,
                                                                                  message_name   => 'item_due' } );
    next PATRON unless $borrower_preferences; # how could this happen?

                },
                branchcode             => $branch_info{"branches.branchcode"},
                message_transport_type => $transport,
                accountlines   => \@accountlines,
            }
          )
          or warn "no letter of type '$letter_type' found. Please see sample_notices.sql";

        $table_params{'biblioitems'} = $p;
    }

    my $currencies = GetCurrency();
    my $currency_format = $currencies->{currency} if defined($currencies);

    my $amountoutstanding_total = 0;
    my @accountlines_tables = ();
    foreach my $accountline (@{$params->{'accountlines'}}) {
        $amountoutstanding_total += $accountline->{'amountoutstanding'};

        $accountline->{'amountoutstanding'} = currency_format($currency_format, $accountline->{'amountoutstanding'}, FMT_SYMBOL);
        # if active currency isn't correct ISO code fallback to sprintf
        $accountline->{'amountoutstanding'} = sprintf('%.2f', $accountline->{'amountoutstanding'}) unless $accountline->{'amountoutstanding'};

        push @accountlines_tables, {
            'accountlines' => $accountline
        };
    }
    $amountoutstanding_total = currency_format($currency_format, $amountoutstanding_total, FMT_SYMBOL);
    # if active currency isn't correct ISO code fallback to sprintf
    $amountoutstanding_total = sprintf('%.2f', $amountoutstanding_total) unless $amountoutstanding_total;

    $params->{'substitute'}->{'accountlines.total'} = $amountoutstanding_total;

    return C4::Letters::GetPreparedLetter (
        module => 'circulation',
        letter_code => $params->{'letter_code'},
        branchcode => $table_params{'branches'},
        substitute => $params->{'substitute'},
        tables     => \%table_params,
        repeat     => {accountline => \@accountlines_tables},
        message_transport_type => $params->{message_transport_type},
    );
}




    $verbose and warn sprintf "branchcode : '%s' using %s\n", $branchcode, $admin_email_address;

    my $sth2 = $dbh->prepare( <<"END_SQL" );
SELECT biblio.*, items.*, issues.*, accountlines.*, biblioitems.itemtype, TO_DAYS($date)-TO_DAYS(date_due) AS days_overdue, branchname
  FROM issues,items,biblio, biblioitems, accountlines, branches b
  WHERE items.itemnumber=issues.itemnumber
    AND biblio.biblionumber   = items.biblionumber
    AND b.branchcode = items.homebranch
    AND biblio.biblionumber   = biblioitems.biblionumber
    AND accountlines.itemnumber = items.itemnumber
    AND issues.borrowernumber = ?
END_SQL


                @message_transport_types = @{ GetOverdueMessageTransportTypes( q{}, $overdue_rules->{categorycode}, $i) }
                    unless @message_transport_types;

                $sth2 = $dbh->prepare('
                    SELECT *
                    FROM accountlines
                    WHERE borrowernumber = ? AND amountoutstanding > 0
                    ');
                $sth2->execute($borrowernumber);
                my @accountlines = ();
                while ( my $accountline = $sth2->fetchrow_hashref() ) {
                    push(@accountlines, $accountline);
                }
                $sth2->finish;

                my $print_sent = 0; # A print notice is not yet sent for this patron
                for my $mtt ( @message_transport_types ) {

                            borrowernumber  => $borrowernumber,
                            branchcode      => $branchcode,
                            items           => \@items,
                            accountlines    => \@accountlines,
                            substitute      => {    # this appears to be a hack to overcome incomplete features in this code.
                                                bib             => $branch_details->{'branchname'}, # maybe 'bib' is a typo for 'lib<rary>'?
                                                'items.content' => $titles,

Lines 83-88 $dbh->do(q\| Link Here

(-)a/t/db_dependent/Letters.t (-15 / +47 lines)
83	$mtts = C4::Letters::GetMessageTransportTypes();	83	$mtts = C4::Letters::GetMessageTransportTypes();
84	is_deeply( $mtts, ['email', 'phone', 'print', 'sms'], 'GetMessageTransportTypes returns all values' );	84	is_deeply( $mtts, ['email', 'phone', 'print', 'sms'], 'GetMessageTransportTypes returns all values' );
85		85
		86	$dbh->do(q\|
		87	INSERT INTO accountlines( amountoutstanding, description, borrowernumber) VALUES (5, 'Rent', \|.$borrowernumber.q\|)
		88	\|);
		89	$dbh->do(q\|
		90	INSERT INTO accountlines( amountoutstanding, description, borrowernumber) VALUES (5, 'Fine', \|.$borrowernumber.q\|)
		91	\|);
		92
86		93
87	# EnqueueLetter	94	# EnqueueLetter
88	is( C4::Letters::EnqueueLetter(), undef, 'EnqueueLetter without argument returns undef' );	95	is( C4::Letters::EnqueueLetter(), undef, 'EnqueueLetter without argument returns undef' );
Lines 151-157 URL: <<OPACBaseURL>> Link Here
151		158
152	The following item(s) is/are currently <<status>>:	159	The following item(s) is/are currently <<status>>:
153		160
154	<item> <<count>>. <<items.itemcallnumber>>, Barcode: <<items.barcode>> </item>	161	<item><<items.itemcallnumber>>, Barcode: <<items.barcode>></item>
		162
		163	Accountlines:
		164	<accountline><<accountlines.amountoutstanding>> - <<accountlines.description>></accountline>
155		165
156	Thank-you for your prompt attention to this matter.	166	Thank-you for your prompt attention to this matter.
157	Don't forget your date of birth: <<borrowers.dateofbirth>>.	167	Don't forget your date of birth: <<borrowers.dateofbirth>>.
Lines 245-258 my $tables = { Link Here
245	my $substitute = {	255	my $substitute = {
246	status => 'overdue',	256	status => 'overdue',
247	};	257	};
248	my $repeat = [	258	my $repeat_items = [
249	{	259	{ items =>
250	itemcallnumber => 'my callnumber1',	260	{
251	barcode => '1234',	261	itemcallnumber => 'my callnumber1',
		262	barcode => '1234',
		263	},
252	},	264	},
253	{	265	{ items =>
254	itemcallnumber => 'my callnumber2',	266	{
255	barcode => '5678',	267	itemcallnumber => 'my callnumber2',
		268	barcode => '5678',
		269	},
		270	}
		271	];
		272	my $repeat_accountlines = [
		273	{ accountlines =>
		274	{
		275	amountoutstanding => 5,
		276	description => 'Rent',
		277	},
		278	},
		279	{ accountlines =>
		280	{
		281	amountoutstanding => 5,
		282	description => 'Fine',
		283	},
256	},	284	},
257	];	285	];
258	my $prepared_letter = GetPreparedLetter((	286	my $prepared_letter = GetPreparedLetter((
Lines 261-267 my $prepared_letter = GetPreparedLetter(( Link Here
261	letter_code => 'my code',	289	letter_code => 'my code',
262	tables => $tables,	290	tables => $tables,
263	substitute => $substitute,	291	substitute => $substitute,
264	repeat => $repeat,	292	repeat => {item => $repeat_items, accountline => $repeat_accountlines},
265	));	293	));
266	my $branch = GetBranchDetail($library->{branchcode});	294	my $branch = GetBranchDetail($library->{branchcode});
267	my $my_title_letter = qq\|$branch->{branchname} - $substitute->{status}\|;	295	my $my_title_letter = qq\|$branch->{branchname} - $substitute->{status}\|;
Lines 274-281 URL: http://thisisatest.com Link Here
274		302
275	The following item(s) is/are currently $substitute->{status}:	303	The following item(s) is/are currently $substitute->{status}:
276		304
277	<item> 1. $repeat->[0]->{itemcallnumber}, Barcode: $repeat->[0]->{barcode} </item>	305	$repeat_items->[0]->{items}->{itemcallnumber}, Barcode: $repeat_items->[0]->{items}->{barcode}
278	<item> 2. $repeat->[1]->{itemcallnumber}, Barcode: $repeat->[1]->{barcode} </item>	306	$repeat_items->[1]->{items}->{itemcallnumber}, Barcode: $repeat_items->[1]->{items}->{barcode}
		307
		308	Accountlines:
		309	$repeat_accountlines->[0]->{accountlines}->{amountoutstanding} - $repeat_accountlines->[0]->{accountlines}->{description}
		310	$repeat_accountlines->[1]->{accountlines}->{amountoutstanding} - $repeat_accountlines->[1]->{accountlines}->{description}
279		311
280	Thank-you for your prompt attention to this matter.	312	Thank-you for your prompt attention to this matter.
281	Don't forget your date of birth: \| . output_pref({ dt => $date, dateonly => 1 }) . q\|.	313	Don't forget your date of birth: \| . output_pref({ dt => $date, dateonly => 1 }) . q\|.
Lines 290-296 $prepared_letter = GetPreparedLetter(( Link Here
290	letter_code => 'my code',	322	letter_code => 'my code',
291	tables => $tables,	323	tables => $tables,
292	substitute => $substitute,	324	substitute => $substitute,
293	repeat => $repeat,	325	repeat => {item => $repeat_items, accountline => $repeat_accountlines},
294	message_transport_type => 'sms',	326	message_transport_type => 'sms',
295	));	327	));
296	$my_content_letter = qq\|This is a SMS for an $substitute->{status}\|;	328	$my_content_letter = qq\|This is a SMS for an $substitute->{status}\|;
Lines 303-309 $prepared_letter = GetPreparedLetter(( Link Here
303	letter_code => 'test_date',	335	letter_code => 'test_date',
304	tables => $tables,	336	tables => $tables,
305	substitute => $substitute,	337	substitute => $substitute,
306	repeat => $repeat,	338	repeat => {item => $repeat_items, accountline => $repeat_accountlines},
307	));	339	));
308	is( $prepared_letter->{content}, q\|This one only contains the date: \| . output_pref({ dt => $date, dateonly => 1 }) . q\|.\|, 'dateonly test 1' );	340	is( $prepared_letter->{content}, q\|This one only contains the date: \| . output_pref({ dt => $date, dateonly => 1 }) . q\|.\|, 'dateonly test 1' );
309		341
Lines 314-320 $prepared_letter = GetPreparedLetter(( Link Here
314	letter_code => 'test_date',	346	letter_code => 'test_date',
315	tables => $tables,	347	tables => $tables,
316	substitute => $substitute,	348	substitute => $substitute,
317	repeat => $repeat,	349	repeat => {item => $repeat_items, accountline => $repeat_accountlines},
318	));	350	));
319	is( $prepared_letter->{content}, q\|This one only contains the date: \| . output_pref({ dt => $date, dateonly => 1 }) . q\|.\|, 'dateonly test 2' );	351	is( $prepared_letter->{content}, q\|This one only contains the date: \| . output_pref({ dt => $date, dateonly => 1 }) . q\|.\|, 'dateonly test 2' );
320		352
Lines 325-331 $prepared_letter = GetPreparedLetter(( Link Here
325	letter_code => 'test_date',	357	letter_code => 'test_date',
326	tables => $tables,	358	tables => $tables,
327	substitute => $substitute,	359	substitute => $substitute,
328	repeat => $repeat,	360	repeat => {item => $repeat_items, accountline => $repeat_accountlines},
329	));	361	));
330	is( $prepared_letter->{content}, q\|This one only contains the date: \| . output_pref({ dt => $date, dateonly => 1 }) . q\|.\|, 'dateonly test 3' );	362	is( $prepared_letter->{content}, q\|This one only contains the date: \| . output_pref({ dt => $date, dateonly => 1 }) . q\|.\|, 'dateonly test 3' );
331		363

Lines 225-230 sub add_form { Link Here

(-)a/tools/letter.pl (-1 / +1 lines)
225	{value => 'items.content', text => 'items.content'},	225	{value => 'items.content', text => 'items.content'},
226	{value => 'items.fine', text => 'items.fine'},	226	{value => 'items.fine', text => 'items.fine'},
227	add_fields('borrowers');	227	add_fields('borrowers');
		228	push @{$field_selection}, add_fields('accountlines');
228	if ($module eq 'circulation') {	229	if ($module eq 'circulation') {
229	push @{$field_selection}, add_fields('opac_news');	230	push @{$field_selection}, add_fields('opac_news');
230		231
231	-

Return to bug 15364

Lines 1004-1025 sub parse_overdues_letter { Link Here

(-)a/C4/Overdues.pm (-1 / +35 lines)
1004	# if active currency isn't correct ISO code fallback to sprintf	1004	# if active currency isn't correct ISO code fallback to sprintf
1005	$item->{'fine'} = sprintf('%.2f', $fine) unless $item->{'fine'};	1005	$item->{'fine'} = sprintf('%.2f', $fine) unless $item->{'fine'};
1006		1006
		1007	$item->{'amountoutstanding'} = currency_format($currency_format, $item->{'amountoutstanding'}, FMT_SYMBOL);
		1008	# if active currency isn't correct ISO code fallback to sprintf
		1009	$item->{'amountoutstanding'} = sprintf('%.2f', $item->{'amountoutstanding'}) unless $item->{'amountoutstanding'};
		1010
		1011	my $accountline = {
		1012	'amountoutstanding' => $item->{'amountoutstanding'},
		1013	'description' => $item->{'description'},
		1014	};
		1015
1007	push @item_tables, {	1016	push @item_tables, {
1008	'biblio' => $item->{'biblionumber'},	1017	'biblio' => $item->{'biblionumber'},
1009	'biblioitems' => $item->{'biblionumber'},	1018	'biblioitems' => $item->{'biblionumber'},
1010	'items' => $item,	1019	'items' => $item,
		1020	'accountlines' => $accountline,
1011	'issues' => $item->{'itemnumber'},	1021	'issues' => $item->{'itemnumber'},
1012	};	1022	};
1013	}	1023	}
1014	}	1024	}
1015		1025
		1026	my $amountoutstanding_total = 0;
		1027	my @accountlines_tables;
		1028	if ( my $i = $params->{'accountlines'} ) {
		1029	foreach my $accountline (@$i) {
		1030	$amountoutstanding_total += $accountline->{'amountoutstanding'};
		1031
		1032	$accountline->{'amountoutstanding'} = currency_format($currency_format, $accountline->{'amountoutstanding'}, FMT_SYMBOL);
		1033	# if active currency isn't correct ISO code fallback to sprintf
		1034	$accountline->{'amountoutstanding'} = sprintf('%.2f', $accountline->{'amountoutstanding'}) unless $accountline->{'amountoutstanding'};
		1035	$accountline->{'amount'} = currency_format($currency_format, $accountline->{'amount'}, FMT_SYMBOL);
		1036	# if active currency isn't correct ISO code fallback to sprintf
		1037	$accountline->{'amount'} = sprintf('%.2f', $accountline->{'amount'}) unless $accountline->{'amount'};
		1038
		1039	push @accountlines_tables, {
		1040	'accountlines' => $accountline
		1041	};
		1042	}
		1043	}
		1044	$amountoutstanding_total = currency_format($currency_format, $amountoutstanding_total, FMT_SYMBOL);
		1045	# if active currency isn't correct ISO code fallback to sprintf
		1046	$amountoutstanding_total = sprintf('%.2f', $amountoutstanding_total) unless $amountoutstanding_total;
		1047
		1048	$substitute->{'accountlines.total'} = $amountoutstanding_total;
		1049
1016	return C4::Letters::GetPreparedLetter (	1050	return C4::Letters::GetPreparedLetter (
1017	module => 'circulation',	1051	module => 'circulation',
1018	letter_code => $params->{'letter_code'},	1052	letter_code => $params->{'letter_code'},
1019	branchcode => $params->{'branchcode'},	1053	branchcode => $params->{'branchcode'},
1020	tables => \%tables,	1054	tables => \%tables,
1021	substitute => $substitute,	1055	substitute => $substitute,
1022	repeat => { item => \@item_tables },	1056	repeat => { item => \@item_tables, accountline => \@accountlines_tables},
1023	message_transport_type => $params->{message_transport_type},	1057	message_transport_type => $params->{message_transport_type},
1024	);	1058	);
1025	}	1059	}

Lines 447-458 foreach my $branchcode (@branches) { Link Here

(-)a/misc/cronjobs/overdue_notices.pl (-2 / +15 lines)
447	$verbose and warn sprintf "branchcode : '%s' using %s\n", $branchcode, $admin_email_address;	447	$verbose and warn sprintf "branchcode : '%s' using %s\n", $branchcode, $admin_email_address;
448		448
449	my $sth2 = $dbh->prepare( <<"END_SQL" );	449	my $sth2 = $dbh->prepare( <<"END_SQL" );
450	SELECT biblio., items., issues.*, biblioitems.itemtype, TO_DAYS($date)-TO_DAYS(date_due) AS days_overdue, branchname	450	SELECT biblio., items., issues., accountlines., biblioitems.itemtype, TO_DAYS($date)-TO_DAYS(date_due) AS days_overdue, branchname
451	FROM issues,items,biblio, biblioitems, branches b	451	FROM issues,items,biblio, biblioitems, accountlines, branches b
452	WHERE items.itemnumber=issues.itemnumber	452	WHERE items.itemnumber=issues.itemnumber
453	AND biblio.biblionumber = items.biblionumber	453	AND biblio.biblionumber = items.biblionumber
454	AND b.branchcode = items.homebranch	454	AND b.branchcode = items.homebranch
455	AND biblio.biblionumber = biblioitems.biblionumber	455	AND biblio.biblionumber = biblioitems.biblionumber
		456	AND accountlines.itemnumber = items.itemnumber
456	AND issues.borrowernumber = ?	457	AND issues.borrowernumber = ?
457	END_SQL	458	END_SQL
458		459
Lines 670-675 END_SQL Link Here
670	@message_transport_types = @{ GetOverdueMessageTransportTypes( q{}, $overdue_rules->{categorycode}, $i) }	671	@message_transport_types = @{ GetOverdueMessageTransportTypes( q{}, $overdue_rules->{categorycode}, $i) }
671	unless @message_transport_types;	672	unless @message_transport_types;
672		673
		674	$sth2 = $dbh->prepare('
		675	SELECT *
		676	FROM accountlines
		677	WHERE borrowernumber = ? AND amountoutstanding > 0
		678	');
		679	$sth2->execute($borrowernumber);
		680	my @accountlines = ();
		681	while ( my $accountline = $sth2->fetchrow_hashref() ) {
		682	push(@accountlines, $accountline);
		683	}
		684	$sth2->finish;
673		685
674	my $print_sent = 0; # A print notice is not yet sent for this patron	686	my $print_sent = 0; # A print notice is not yet sent for this patron
675	for my $mtt ( @message_transport_types ) {	687	for my $mtt ( @message_transport_types ) {
Lines 679-684 END_SQL Link Here
679	borrowernumber => $borrowernumber,	691	borrowernumber => $borrowernumber,
680	branchcode => $branchcode,	692	branchcode => $branchcode,
681	items => \@items,	693	items => \@items,
		694	accountlines => \@accountlines,
682	substitute => { # this appears to be a hack to overcome incomplete features in this code.	695	substitute => { # this appears to be a hack to overcome incomplete features in this code.
683	bib => $branch_details->{'branchname'}, # maybe 'bib' is a typo for 'lib<rary>'?	696	bib => $branch_details->{'branchname'}, # maybe 'bib' is a typo for 'lib<rary>'?
684	'items.content' => $titles,	697	'items.content' => $titles,

Lines 43-48 use warnings; Link Here

(-)a/misc/cronjobs/advance_notices.pl (+65 lines)
43	use Getopt::Long;	43	use Getopt::Long;
44	use Pod::Usage;	44	use Pod::Usage;
45	use Data::Dumper;	45	use Data::Dumper;
		46	use Locale::Currency::Format 1.28;
46	BEGIN {	47	BEGIN {
47	# find Koha's Perl modules	48	# find Koha's Perl modules
48	# test carefully before changing this	49	# test carefully before changing this
Lines 55-60 use C4::Letters; Link Here
55	use C4::Members;	56	use C4::Members;
56	use C4::Members::Messaging;	57	use C4::Members::Messaging;
57	use C4::Overdues;	58	use C4::Overdues;
		59	use C4::Budgets qw(GetCurrency);
58	use Koha::DateUtils;	60	use Koha::DateUtils;
59	use C4::Log;	61	use C4::Log;
60		62
Lines 235-240 UPCOMINGITEM: foreach my $upcoming ( @$upcoming_dues ) { Link Here
235		237
236	my $from_address = $upcoming->{branchemail} \|\| $admin_adress;	238	my $from_address = $upcoming->{branchemail} \|\| $admin_adress;
237		239
		240	my $sth2 = $dbh->prepare('
		241	SELECT *
		242	FROM accountlines
		243	WHERE borrowernumber = ? AND amountoutstanding > 0
		244	');
		245	$sth2->execute($upcoming->{'borrowernumber'});
		246	my @accountlines = ();
		247	while ( my $accountline = $sth2->fetchrow_hashref() ) {
		248	push(@accountlines, $accountline);
		249	}
		250	$sth2->finish;
		251
238	my $borrower_preferences;	252	my $borrower_preferences;
239	if ( 0 == $upcoming->{'days_until_due'} ) {	253	if ( 0 == $upcoming->{'days_until_due'} ) {
240	# This item is due today. Send an 'item due' message.	254	# This item is due today. Send an 'item due' message.
Lines 265-270 UPCOMINGITEM: foreach my $upcoming ( @$upcoming_dues ) { Link Here
265	itemnumber => $upcoming->{'itemnumber'},	279	itemnumber => $upcoming->{'itemnumber'},
266	substitute => { 'items.content' => $titles },	280	substitute => { 'items.content' => $titles },
267	message_transport_type => $transport,	281	message_transport_type => $transport,
		282	accountlines => \@accountlines,
268	} )	283	} )
269	or warn "no letter of type '$letter_type' found. Please see sample_notices.sql";	284	or warn "no letter of type '$letter_type' found. Please see sample_notices.sql";
270	push @letters, $letter if $letter;	285	push @letters, $letter if $letter;
Lines 299-304 UPCOMINGITEM: foreach my $upcoming ( @$upcoming_dues ) { Link Here
299	itemnumber => $upcoming->{'itemnumber'},	314	itemnumber => $upcoming->{'itemnumber'},
300	substitute => { 'items.content' => $titles },	315	substitute => { 'items.content' => $titles },
301	message_transport_type => $transport,	316	message_transport_type => $transport,
		317	accountlines => \@accountlines,
302	} )	318	} )
303	or warn "no letter of type '$letter_type' found. Please see sample_notices.sql";	319	or warn "no letter of type '$letter_type' found. Please see sample_notices.sql";
304	push @letters, $letter if $letter;	320	push @letters, $letter if $letter;
Lines 342-347 PATRON: while ( my ( $borrowernumber, $digest ) = each %$upcoming_digest ) { Link Here
342	my $count = $digest->{count};	358	my $count = $digest->{count};
343	my $from_address = $digest->{email};	359	my $from_address = $digest->{email};
344		360
		361	my $sth2 = $dbh->prepare('
		362	SELECT *
		363	FROM accountlines
		364	WHERE borrowernumber = ? AND amountoutstanding > 0
		365	');
		366	$sth2->execute($borrowernumber);
		367	my @accountlines = ();
		368	while ( my $accountline = $sth2->fetchrow_hashref() ) {
		369	push(@accountlines, $accountline);
		370	}
		371	$sth2->finish;
		372
345	my $borrower_preferences = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber,	373	my $borrower_preferences = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber,
346	message_name => 'advance_notice' } );	374	message_name => 'advance_notice' } );
347	next PATRON unless $borrower_preferences; # how could this happen?	375	next PATRON unless $borrower_preferences; # how could this happen?
Lines 371-376 PATRON: while ( my ( $borrowernumber, $digest ) = each %$upcoming_digest ) { Link Here
371	},	399	},
372	branchcode => $branch_info{"branches.branchcode"},	400	branchcode => $branch_info{"branches.branchcode"},
373	message_transport_type => $transport,	401	message_transport_type => $transport,
		402	accountlines => \@accountlines,
374	}	403	}
375	)	404	)
376	or warn "no letter of type '$letter_type' found. Please see sample_notices.sql";	405	or warn "no letter of type '$letter_type' found. Please see sample_notices.sql";
Lines 401-406 PATRON: while ( my ( $borrowernumber, $digest ) = each %$due_digest ) { Link Here
401	my $count = $digest->{count};	430	my $count = $digest->{count};
402	my $from_address = $digest->{email};	431	my $from_address = $digest->{email};
403		432
		433	my $sth2 = $dbh->prepare('
		434	SELECT *
		435	FROM accountlines
		436	WHERE borrowernumber = ? AND amountoutstanding > 0
		437	');
		438	$sth2->execute($borrowernumber);
		439	my @accountlines = ();
		440	while ( my $accountline = $sth2->fetchrow_hashref() ) {
		441	push(@accountlines, $accountline);
		442	}
		443	$sth2->finish;
		444
404	my $borrower_preferences = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber,	445	my $borrower_preferences = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber,
405	message_name => 'item_due' } );	446	message_name => 'item_due' } );
406	next PATRON unless $borrower_preferences; # how could this happen?	447	next PATRON unless $borrower_preferences; # how could this happen?
Lines 428-433 PATRON: while ( my ( $borrowernumber, $digest ) = each %$due_digest ) { Link Here
428	},	469	},
429	branchcode => $branch_info{"branches.branchcode"},	470	branchcode => $branch_info{"branches.branchcode"},
430	message_transport_type => $transport,	471	message_transport_type => $transport,
		472	accountlines => \@accountlines,
431	}	473	}
432	)	474	)
433	or warn "no letter of type '$letter_type' found. Please see sample_notices.sql";	475	or warn "no letter of type '$letter_type' found. Please see sample_notices.sql";
Lines 479-490 sub parse_letter { Link Here
479	$table_params{'biblioitems'} = $p;	521	$table_params{'biblioitems'} = $p;
480	}	522	}
481		523
		524	my $currencies = GetCurrency();
		525	my $currency_format = $currencies->{currency} if defined($currencies);
		526
		527	my $amountoutstanding_total = 0;
		528	my @accountlines_tables = ();
		529	foreach my $accountline (@{$params->{'accountlines'}}) {
		530	$amountoutstanding_total += $accountline->{'amountoutstanding'};
		531
		532	$accountline->{'amountoutstanding'} = currency_format($currency_format, $accountline->{'amountoutstanding'}, FMT_SYMBOL);
		533	# if active currency isn't correct ISO code fallback to sprintf
		534	$accountline->{'amountoutstanding'} = sprintf('%.2f', $accountline->{'amountoutstanding'}) unless $accountline->{'amountoutstanding'};
		535
		536	push @accountlines_tables, {
		537	'accountlines' => $accountline
		538	};
		539	}
		540	$amountoutstanding_total = currency_format($currency_format, $amountoutstanding_total, FMT_SYMBOL);
		541	# if active currency isn't correct ISO code fallback to sprintf
		542	$amountoutstanding_total = sprintf('%.2f', $amountoutstanding_total) unless $amountoutstanding_total;
		543
		544	$params->{'substitute'}->{'accountlines.total'} = $amountoutstanding_total;
		545
482	return C4::Letters::GetPreparedLetter (	546	return C4::Letters::GetPreparedLetter (
483	module => 'circulation',	547	module => 'circulation',
484	letter_code => $params->{'letter_code'},	548	letter_code => $params->{'letter_code'},
485	branchcode => $table_params{'branches'},	549	branchcode => $table_params{'branches'},
486	substitute => $params->{'substitute'},	550	substitute => $params->{'substitute'},
487	tables => \%table_params,	551	tables => \%table_params,
		552	repeat => {accountline => \@accountlines_tables},
488	message_transport_type => $params->{message_transport_type},	553	message_transport_type => $params->{message_transport_type},
489	);	554	);
490	}	555	}