Attachment #62138 for bug #14723

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




    my $dbh       = C4::Context->dbh();
    my $statement = << 'ENDSQL';
INSERT INTO message_queue
( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type, delivery_note )
VALUES
( ?,              ?,       ?,       ?,        ?,           ?,                      ?,      NOW(),       ?,          ?,            ?,            ? )
ENDSQL

    my $sth    = $dbh->prepare($statement);

        $params->{'to_address'},                  # to_address
        $params->{'from_address'},                # from_address
        $params->{'letter'}->{'content-type'},    # content_type
        $params->{'delivery_note'}        || '',  # delivery_note
    );
    return $dbh->last_insert_id(undef,undef,'message_queue', undef);
}


    my $dbh = C4::Context->dbh();
    my $statement = << 'ENDSQL';
SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, delivery_note
FROM message_queue
ENDSQL


    return unless $message_id;
    my $dbh = C4::Context->dbh;
    return $dbh->selectrow_hashref(q|
        SELECT message_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type, delivery_note
        FROM message_queue
        WHERE message_id = ?
    |, {}, $message_id );


    my $dbh = C4::Context->dbh();
    my $statement = << 'ENDSQL';
SELECT mq.message_id, mq.borrowernumber, mq.subject, mq.content, mq.message_transport_type, mq.status, mq.time_queued, mq.from_address, mq.to_address, mq.content_type, b.branchcode, mq.letter_code, mq.delivery_note
  FROM message_queue mq
  LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber
 WHERE status = ?

        unless ($member) {
            warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";
            _set_message_status( { message_id => $message->{'message_id'},
                                   status     => 'failed',
                                   delivery_note => 'Invalid borrowernumber '.$message->{borrowernumber} } );
            return;
        }
        $to_address = C4::Members::GetNoticeEmailAddress( $message->{'borrowernumber'} );

            # warn "FAIL: No 'to_address' and no email for " . ($member->{surname} ||'') . ", borrowernumber ($message->{borrowernumber})";
            # warning too verbose for this more common case?
            _set_message_status( { message_id => $message->{'message_id'},
                                   status     => 'failed',
                                   delivery_note => 'Unable to find an email address for this borrower' } );
            return;
        }
    }


    if ( sendmail( %sendmail_params ) ) {
        _set_message_status( { message_id => $message->{'message_id'},
                status     => 'sent',
                delivery_note => '' } );
        return 1;
    } else {
        _set_message_status( { message_id => $message->{'message_id'},
                status     => 'failed',
                delivery_note => $Mail::Sendmail::error } );
        carp $Mail::Sendmail::error;
        return;
    }


    unless ( $member->{smsalertnumber} ) {
        _set_message_status( { message_id => $message->{'message_id'},
                               status     => 'failed',
                               delivery_note => 'Missing SMS number' } );
        return;
    }

    if ( _is_duplicate( $message ) ) {
        _set_message_status( { message_id => $message->{'message_id'},
                               status     => 'failed',
                               delivery_note => 'Message is duplicate' } );
        return;
    }


                                       message     => $message->{'content'},
                                     } );
    _set_message_status( { message_id => $message->{'message_id'},
                           status     => ($success ? 'sent' : 'failed'),
                           delivery_note => ($success ? '' : 'No notes from SMS driver') } );

    return $success;
}


    }

    my $dbh = C4::Context->dbh();
    my $statement = 'UPDATE message_queue SET status= ?, delivery_note= ? WHERE message_id = ?';
    my $sth = $dbh->prepare( $statement );
    my $result = $sth->execute( $params->{'status'},
                                $params->{'delivery_note'} || '',
                                $params->{'message_id'} );
    return $result;
}

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/Bug-14723_-_Additional_delivery_notes_to_messages.perl (+8 lines)
	1	$DBversion = 'XXX'; # will be replaced by the RM
	2	if( CheckVersion( $DBversion ) ) {
	3	$dbh->do("ALTER TABLE message_queue ADD delivery_note TEXT");
	4
	5	# Always end with this (adjust the bug info)
	6	SetVersion( $DBversion );
	7	print "Upgrade to $DBversion done (Bug 14723 - Additional delivery notes to messages)\n";
	8	}

Lines 2508-2513 CREATE TABLE `message_queue` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (+1 lines)
2508	`to_address` mediumtext,	2508	`to_address` mediumtext,
2509	`from_address` mediumtext,	2509	`from_address` mediumtext,
2510	`content_type` text,	2510	`content_type` text,
		2511	`delivery_note` text,
2511	PRIMARY KEY `message_id` (`message_id`),	2512	PRIMARY KEY `message_id` (`message_id`),
2512	KEY `borrowernumber` (`borrowernumber`),	2513	KEY `borrowernumber` (`borrowernumber`),
2513	KEY `message_transport_type` (`message_transport_type`),	2514	KEY `message_transport_type` (`message_transport_type`),

Lines 12-18 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/members/notices.tt (-1 / +5 lines)
12	$(document).ready(function() {	12	$(document).ready(function() {
13	$("#noticestable").dataTable($.extend(true, {}, dataTablesDefaults, {	13	$("#noticestable").dataTable($.extend(true, {}, dataTablesDefaults, {
14	"aaSorting": [[ 3, "desc" ]],	14	"aaSorting": [[ 3, "desc" ]],
15	"aoColumns": [ null,null,null,{ "sType": "title-string" } ],	15	"aoColumns": [ null,null,null,{ "sType": "title-string" }, null ],
16	"sPaginationType": "four_button"	16	"sPaginationType": "four_button"
17	}));	17	}));
18		18
Lines 51-56 Link Here
51	<th>Type</th>	51	<th>Type</th>
52	<th>Status</th>	52	<th>Status</th>
53	<th>Time</th>	53	<th>Time</th>
		54	<th>Delivery note</th>
54	</tr>	55	</tr>
55	</thead>	56	</thead>
56	<tbody>	57	<tbody>
Lines 89-94 Link Here
89	[% END %]	90	[% END %]
90	</td>	91	</td>
91	<td><span title="[% QUEUED_MESSAGE.time_queued %]">[% QUEUED_MESSAGE.time_queued \| $KohaDates with_hours => 1 %]</span></td>	92	<td><span title="[% QUEUED_MESSAGE.time_queued %]">[% QUEUED_MESSAGE.time_queued \| $KohaDates with_hours => 1 %]</span></td>
		93	<td>
		94	[% QUEUED_MESSAGE.delivery_note %]
		95	</td>
92	</tr>	96	</tr>
93	[% END %]	97	[% END %]
94	</tbody>	98	</tbody>

Lines 18-24 Link Here

(-)a/t/db_dependent/Letters.t (-3 / +7 lines)
18	# along with Koha; if not, see <http://www.gnu.org/licenses>.	18	# along with Koha; if not, see <http://www.gnu.org/licenses>.
19		19
20	use Modern::Perl;	20	use Modern::Perl;
21	use Test::More tests => 80;	21	use Test::More tests => 82;
22	use Test::MockModule;	22	use Test::MockModule;
23	use Test::Warn;	23	use Test::Warn;
24		24
Lines 126-132 is( $messages->[0]->{subject}, $my_message->{letter}->{title}, 'EnqueueLetter st Link Here
126	is( $messages->[0]->{content}, $my_message->{letter}->{content}, 'EnqueueLetter stores the content correctly' );	126	is( $messages->[0]->{content}, $my_message->{letter}->{content}, 'EnqueueLetter stores the content correctly' );
127	is( $messages->[0]->{message_transport_type}, $my_message->{message_transport_type}, 'EnqueueLetter stores the message type correctly' );	127	is( $messages->[0]->{message_transport_type}, $my_message->{message_transport_type}, 'EnqueueLetter stores the message type correctly' );
128	is( $messages->[0]->{status}, 'pending', 'EnqueueLetter stores the status pending correctly' );	128	is( $messages->[0]->{status}, 'pending', 'EnqueueLetter stores the status pending correctly' );
129		129	is( $messages->[0]->{delivery_note}, '', 'Delivery note for successful message correctly empty');
130		130
131	# SendQueuedMessages	131	# SendQueuedMessages
132	my $messages_processed = C4::Letters::SendQueuedMessages();	132	my $messages_processed = C4::Letters::SendQueuedMessages();
Lines 149-154 is( $resent, 0, 'The message should not have been resent again' ); Link Here
149	$resent = C4::Letters::ResendMessage();	149	$resent = C4::Letters::ResendMessage();
150	is( $resent, undef, 'ResendMessage should return undef if not message_id given' );	150	is( $resent, undef, 'ResendMessage should return undef if not message_id given' );
151		151
		152	# Delivery notes
		153	is($messages->[0]->{delivery_note}, 'Missing SMS number',
		154	'Delivery note for no smsalertnumber correctly set');
		155
		156
152	# GetLetters	157	# GetLetters
153	my $letters = C4::Letters::GetLetters();	158	my $letters = C4::Letters::GetLetters();
154	is( @$letters, 0, 'GetLetters returns the correct number of letters' );	159	is( @$letters, 0, 'GetLetters returns the correct number of letters' );
155	-

Return to bug 14723

Lines 1000-1008 sub EnqueueLetter { Link Here

(-)a/C4/Letters.pm (-13 / +23 lines)
1000	my $dbh = C4::Context->dbh();	1000	my $dbh = C4::Context->dbh();
1001	my $statement = << 'ENDSQL';	1001	my $statement = << 'ENDSQL';
1002	INSERT INTO message_queue	1002	INSERT INTO message_queue
1003	( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type )	1003	( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type, delivery_note )
1004	VALUES	1004	VALUES
1005	( ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ? )	1005	( ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?, ? )
1006	ENDSQL	1006	ENDSQL
1007		1007
1008	my $sth = $dbh->prepare($statement);	1008	my $sth = $dbh->prepare($statement);
Lines 1017-1022 ENDSQL Link Here
1017	$params->{'to_address'}, # to_address	1017	$params->{'to_address'}, # to_address
1018	$params->{'from_address'}, # from_address	1018	$params->{'from_address'}, # from_address
1019	$params->{'letter'}->{'content-type'}, # content_type	1019	$params->{'letter'}->{'content-type'}, # content_type
		1020	$params->{'delivery_note'} \|\| '', # delivery_note
1020	);	1021	);
1021	return $dbh->last_insert_id(undef,undef,'message_queue', undef);	1022	return $dbh->last_insert_id(undef,undef,'message_queue', undef);
1022	}	1023	}
Lines 1113-1119 sub GetQueuedMessages { Link Here
1113		1114
1114	my $dbh = C4::Context->dbh();	1115	my $dbh = C4::Context->dbh();
1115	my $statement = << 'ENDSQL';	1116	my $statement = << 'ENDSQL';
1116	SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued	1117	SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, delivery_note
1117	FROM message_queue	1118	FROM message_queue
1118	ENDSQL	1119	ENDSQL
1119		1120
Lines 1167-1173 sub GetMessage { Link Here
1167	return unless $message_id;	1168	return unless $message_id;
1168	my $dbh = C4::Context->dbh;	1169	my $dbh = C4::Context->dbh;
1169	return $dbh->selectrow_hashref(q\|	1170	return $dbh->selectrow_hashref(q\|
1170	SELECT message_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type	1171	SELECT message_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type, delivery_note
1171	FROM message_queue	1172	FROM message_queue
1172	WHERE message_id = ?	1173	WHERE message_id = ?
1173	\|, {}, $message_id );	1174	\|, {}, $message_id );
Lines 1257-1263 sub _get_unsent_messages { Link Here
1257		1258
1258	my $dbh = C4::Context->dbh();	1259	my $dbh = C4::Context->dbh();
1259	my $statement = << 'ENDSQL';	1260	my $statement = << 'ENDSQL';
1260	SELECT mq.message_id, mq.borrowernumber, mq.subject, mq.content, mq.message_transport_type, mq.status, mq.time_queued, mq.from_address, mq.to_address, mq.content_type, b.branchcode, mq.letter_code	1261	SELECT mq.message_id, mq.borrowernumber, mq.subject, mq.content, mq.message_transport_type, mq.status, mq.time_queued, mq.from_address, mq.to_address, mq.content_type, b.branchcode, mq.letter_code, mq.delivery_note
1261	FROM message_queue mq	1262	FROM message_queue mq
1262	LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber	1263	LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber
1263	WHERE status = ?	1264	WHERE status = ?
Lines 1296-1302 sub _send_message_by_email { Link Here
1296	unless ($member) {	1297	unless ($member) {
1297	warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";	1298	warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";
1298	_set_message_status( { message_id => $message->{'message_id'},	1299	_set_message_status( { message_id => $message->{'message_id'},
1299	status => 'failed' } );	1300	status => 'failed',
		1301	delivery_note => 'Invalid borrowernumber '.$message->{borrowernumber} } );
1300	return;	1302	return;
1301	}	1303	}
1302	$to_address = C4::Members::GetNoticeEmailAddress( $message->{'borrowernumber'} );	1304	$to_address = C4::Members::GetNoticeEmailAddress( $message->{'borrowernumber'} );
Lines 1304-1310 sub _send_message_by_email { Link Here
1304	# warn "FAIL: No 'to_address' and no email for " . ($member->{surname} \|\|'') . ", borrowernumber ($message->{borrowernumber})";	1306	# warn "FAIL: No 'to_address' and no email for " . ($member->{surname} \|\|'') . ", borrowernumber ($message->{borrowernumber})";
1305	# warning too verbose for this more common case?	1307	# warning too verbose for this more common case?
1306	_set_message_status( { message_id => $message->{'message_id'},	1308	_set_message_status( { message_id => $message->{'message_id'},
1307	status => 'failed' } );	1309	status => 'failed',
		1310	delivery_note => 'Unable to find an email address for this borrower' } );
1308	return;	1311	return;
1309	}	1312	}
1310	}	1313	}
Lines 1346-1356 sub _send_message_by_email { Link Here
1346		1349
1347	if ( sendmail( %sendmail_params ) ) {	1350	if ( sendmail( %sendmail_params ) ) {
1348	_set_message_status( { message_id => $message->{'message_id'},	1351	_set_message_status( { message_id => $message->{'message_id'},
1349	status => 'sent' } );	1352	status => 'sent',
		1353	delivery_note => '' } );
1350	return 1;	1354	return 1;
1351	} else {	1355	} else {
1352	_set_message_status( { message_id => $message->{'message_id'},	1356	_set_message_status( { message_id => $message->{'message_id'},
1353	status => 'failed' } );	1357	status => 'failed',
		1358	delivery_note => $Mail::Sendmail::error } );
1354	carp $Mail::Sendmail::error;	1359	carp $Mail::Sendmail::error;
1355	return;	1360	return;
1356	}	1361	}
Lines 1399-1411 sub _send_message_by_sms { Link Here
1399		1404
1400	unless ( $member->{smsalertnumber} ) {	1405	unless ( $member->{smsalertnumber} ) {
1401	_set_message_status( { message_id => $message->{'message_id'},	1406	_set_message_status( { message_id => $message->{'message_id'},
1402	status => 'failed' } );	1407	status => 'failed',
		1408	delivery_note => 'Missing SMS number' } );
1403	return;	1409	return;
1404	}	1410	}
1405		1411
1406	if ( _is_duplicate( $message ) ) {	1412	if ( _is_duplicate( $message ) ) {
1407	_set_message_status( { message_id => $message->{'message_id'},	1413	_set_message_status( { message_id => $message->{'message_id'},
1408	status => 'failed' } );	1414	status => 'failed',
		1415	delivery_note => 'Message is duplicate' } );
1409	return;	1416	return;
1410	}	1417	}
1411		1418
Lines 1413-1419 sub _send_message_by_sms { Link Here
1413	message => $message->{'content'},	1420	message => $message->{'content'},
1414	} );	1421	} );
1415	_set_message_status( { message_id => $message->{'message_id'},	1422	_set_message_status( { message_id => $message->{'message_id'},
1416	status => ($success ? 'sent' : 'failed') } );	1423	status => ($success ? 'sent' : 'failed'),
		1424	delivery_note => ($success ? '' : 'No notes from SMS driver') } );
		1425
1417	return $success;	1426	return $success;
1418	}	1427	}
1419		1428
Lines 1431-1439 sub _set_message_status { Link Here
1431	}	1440	}
1432		1441
1433	my $dbh = C4::Context->dbh();	1442	my $dbh = C4::Context->dbh();
1434	my $statement = 'UPDATE message_queue SET status= ? WHERE message_id = ?';	1443	my $statement = 'UPDATE message_queue SET status= ?, delivery_note= ? WHERE message_id = ?';
1435	my $sth = $dbh->prepare( $statement );	1444	my $sth = $dbh->prepare( $statement );
1436	my $result = $sth->execute( $params->{'status'},	1445	my $result = $sth->execute( $params->{'status'},
		1446	$params->{'delivery_note'} \|\| '',
1437	$params->{'message_id'} );	1447	$params->{'message_id'} );
1438	return $result;	1448	return $result;
1439	}	1449	}