Attachment #78313 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 = qq{
        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 ($patron) {
            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 = $patron->notice_email_address;

            # 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 ( Mail::Sendmail::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 ( $patron and $patron->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 2503-2508 CREATE TABLE `message_queue` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (+1 lines)
2503	`to_address` LONGTEXT,	2503	`to_address` LONGTEXT,
2504	`from_address` LONGTEXT,	2504	`from_address` LONGTEXT,
2505	`content_type` MEDIUMTEXT,	2505	`content_type` MEDIUMTEXT,
		2506	`delivery_note` MEDIUMTEXT,
2506	PRIMARY KEY `message_id` (`message_id`),	2507	PRIMARY KEY `message_id` (`message_id`),
2507	KEY `borrowernumber` (`borrowernumber`),	2508	KEY `borrowernumber` (`borrowernumber`),
2508	KEY `message_transport_type` (`message_transport_type`),	2509	KEY `message_transport_type` (`message_transport_type`),

Lines 36-41 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/members/notices.tt (-1 / +5 lines)
36	<th>Type</th>	36	<th>Type</th>
37	<th>Status</th>	37	<th>Status</th>
38	<th>Time</th>	38	<th>Time</th>
		39	<th>Delivery note</th>
39	</tr>	40	</tr>
40	</thead>	41	</thead>
41	<tbody>	42	<tbody>
Lines 74-79 Link Here
74	[% END %]	75	[% END %]
75	</td>	76	</td>
76	<td><span title="[% QUEUED_MESSAGE.time_queued \| html %]">[% QUEUED_MESSAGE.time_queued \| $KohaDates with_hours => 1 \| html %]</span></td>	77	<td><span title="[% QUEUED_MESSAGE.time_queued \| html %]">[% QUEUED_MESSAGE.time_queued \| $KohaDates with_hours => 1 \| html %]</span></td>
		78	<td>
		79	[% QUEUED_MESSAGE.delivery_note \| html %]
		80	</td>
77	</tr>	81	</tr>
78	[% END %]	82	[% END %]
79	</tbody>	83	</tbody>
Lines 97-103 Link Here
97	$(document).ready(function() {	101	$(document).ready(function() {
98	$("#noticestable").dataTable($.extend(true, {}, dataTablesDefaults, {	102	$("#noticestable").dataTable($.extend(true, {}, dataTablesDefaults, {
99	"aaSorting": [[ 3, "desc" ]],	103	"aaSorting": [[ 3, "desc" ]],
100	"aoColumns": [ null,null,null,{ "sType": "title-string" } ],	104	"aoColumns": [ null,null,null,{ "sType": "title-string" }, null ],
101	"sPaginationType": "four_button"	105	"sPaginationType": "four_button"
102	}));	106	}));
103		107

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 => 64;	21	use Test::More tests => 66;
22	use Test::MockModule;	22	use Test::MockModule;
23	use Test::Warn;	23	use Test::Warn;
24		24
Lines 132-138 is( $messages->[0]->{subject}, $my_message->{letter}->{title}, 'EnqueueLetter st Link Here
132	is( $messages->[0]->{content}, $my_message->{letter}->{content}, 'EnqueueLetter stores the content correctly' );	132	is( $messages->[0]->{content}, $my_message->{letter}->{content}, 'EnqueueLetter stores the content correctly' );
133	is( $messages->[0]->{message_transport_type}, $my_message->{message_transport_type}, 'EnqueueLetter stores the message type correctly' );	133	is( $messages->[0]->{message_transport_type}, $my_message->{message_transport_type}, 'EnqueueLetter stores the message type correctly' );
134	is( $messages->[0]->{status}, 'pending', 'EnqueueLetter stores the status pending correctly' );	134	is( $messages->[0]->{status}, 'pending', 'EnqueueLetter stores the status pending correctly' );
135		135	is( $messages->[0]->{delivery_note}, '', 'Delivery note for successful message correctly empty');
136		136
137	# SendQueuedMessages	137	# SendQueuedMessages
138	my $messages_processed = C4::Letters::SendQueuedMessages( { type => 'email' });	138	my $messages_processed = C4::Letters::SendQueuedMessages( { type => 'email' });
Lines 156-161 is( $resent, 0, 'The message should not have been resent again' ); Link Here
156	$resent = C4::Letters::ResendMessage();	156	$resent = C4::Letters::ResendMessage();
157	is( $resent, undef, 'ResendMessage should return undef if not message_id given' );	157	is( $resent, undef, 'ResendMessage should return undef if not message_id given' );
158		158
		159	# Delivery notes
		160	is($messages->[0]->{delivery_note}, 'Missing SMS number',
		161	'Delivery note for no smsalertnumber correctly set');
		162
		163
159	# GetLetters	164	# GetLetters
160	my $letters = C4::Letters::GetLetters();	165	my $letters = C4::Letters::GetLetters();
161	is( @$letters, 0, 'GetLetters returns the correct number of letters' );	166	is( @$letters, 0, 'GetLetters returns the correct number of letters' );
162	-

Return to bug 14723

Lines 906-914 sub EnqueueLetter { Link Here

(-)a/C4/Letters.pm (-13 / +23 lines)
906	my $dbh = C4::Context->dbh();	906	my $dbh = C4::Context->dbh();
907	my $statement = << 'ENDSQL';	907	my $statement = << 'ENDSQL';
908	INSERT INTO message_queue	908	INSERT INTO message_queue
909	( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type )	909	( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type, delivery_note )
910	VALUES	910	VALUES
911	( ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ? )	911	( ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?, ? )
912	ENDSQL	912	ENDSQL
913		913
914	my $sth = $dbh->prepare($statement);	914	my $sth = $dbh->prepare($statement);
Lines 923-928 ENDSQL Link Here
923	$params->{'to_address'}, # to_address	923	$params->{'to_address'}, # to_address
924	$params->{'from_address'}, # from_address	924	$params->{'from_address'}, # from_address
925	$params->{'letter'}->{'content-type'}, # content_type	925	$params->{'letter'}->{'content-type'}, # content_type
		926	$params->{'delivery_note'} \|\| '', # delivery_note
926	);	927	);
927	return $dbh->last_insert_id(undef,undef,'message_queue', undef);	928	return $dbh->last_insert_id(undef,undef,'message_queue', undef);
928	}	929	}
Lines 1056-1062 sub GetQueuedMessages { Link Here
1056		1057
1057	my $dbh = C4::Context->dbh();	1058	my $dbh = C4::Context->dbh();
1058	my $statement = << 'ENDSQL';	1059	my $statement = << 'ENDSQL';
1059	SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued	1060	SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, delivery_note
1060	FROM message_queue	1061	FROM message_queue
1061	ENDSQL	1062	ENDSQL
1062		1063
Lines 1110-1116 sub GetMessage { Link Here
1110	return unless $message_id;	1111	return unless $message_id;
1111	my $dbh = C4::Context->dbh;	1112	my $dbh = C4::Context->dbh;
1112	return $dbh->selectrow_hashref(q\|	1113	return $dbh->selectrow_hashref(q\|
1113	SELECT message_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type	1114	SELECT message_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type, delivery_note
1114	FROM message_queue	1115	FROM message_queue
1115	WHERE message_id = ?	1116	WHERE message_id = ?
1116	\|, {}, $message_id );	1117	\|, {}, $message_id );
Lines 1214-1220 sub _get_unsent_messages { Link Here
1214		1215
1215	my $dbh = C4::Context->dbh();	1216	my $dbh = C4::Context->dbh();
1216	my $statement = qq{	1217	my $statement = qq{
1217	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	1218	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
1218	FROM message_queue mq	1219	FROM message_queue mq
1219	LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber	1220	LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber
1220	WHERE status = ?	1221	WHERE status = ?
Lines 1261-1267 sub _send_message_by_email { Link Here
1261	unless ($patron) {	1262	unless ($patron) {
1262	warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";	1263	warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";
1263	_set_message_status( { message_id => $message->{'message_id'},	1264	_set_message_status( { message_id => $message->{'message_id'},
1264	status => 'failed' } );	1265	status => 'failed',
		1266	delivery_note => 'Invalid borrowernumber '.$message->{borrowernumber} } );
1265	return;	1267	return;
1266	}	1268	}
1267	$to_address = $patron->notice_email_address;	1269	$to_address = $patron->notice_email_address;
Lines 1269-1275 sub _send_message_by_email { Link Here
1269	# warn "FAIL: No 'to_address' and no email for " . ($member->{surname} \|\|'') . ", borrowernumber ($message->{borrowernumber})";	1271	# warn "FAIL: No 'to_address' and no email for " . ($member->{surname} \|\|'') . ", borrowernumber ($message->{borrowernumber})";
1270	# warning too verbose for this more common case?	1272	# warning too verbose for this more common case?
1271	_set_message_status( { message_id => $message->{'message_id'},	1273	_set_message_status( { message_id => $message->{'message_id'},
1272	status => 'failed' } );	1274	status => 'failed',
		1275	delivery_note => 'Unable to find an email address for this borrower' } );
1273	return;	1276	return;
1274	}	1277	}
1275	}	1278	}
Lines 1311-1321 sub _send_message_by_email { Link Here
1311		1314
1312	if ( Mail::Sendmail::sendmail( %sendmail_params ) ) {	1315	if ( Mail::Sendmail::sendmail( %sendmail_params ) ) {
1313	_set_message_status( { message_id => $message->{'message_id'},	1316	_set_message_status( { message_id => $message->{'message_id'},
1314	status => 'sent' } );	1317	status => 'sent',
		1318	delivery_note => '' } );
1315	return 1;	1319	return 1;
1316	} else {	1320	} else {
1317	_set_message_status( { message_id => $message->{'message_id'},	1321	_set_message_status( { message_id => $message->{'message_id'},
1318	status => 'failed' } );	1322	status => 'failed',
		1323	delivery_note => $Mail::Sendmail::error } );
1319	carp $Mail::Sendmail::error;	1324	carp $Mail::Sendmail::error;
1320	return;	1325	return;
1321	}	1326	}
Lines 1364-1376 sub _send_message_by_sms { Link Here
1364		1369
1365	unless ( $patron and $patron->smsalertnumber ) {	1370	unless ( $patron and $patron->smsalertnumber ) {
1366	_set_message_status( { message_id => $message->{'message_id'},	1371	_set_message_status( { message_id => $message->{'message_id'},
1367	status => 'failed' } );	1372	status => 'failed',
		1373	delivery_note => 'Missing SMS number' } );
1368	return;	1374	return;
1369	}	1375	}
1370		1376
1371	if ( _is_duplicate( $message ) ) {	1377	if ( _is_duplicate( $message ) ) {
1372	_set_message_status( { message_id => $message->{'message_id'},	1378	_set_message_status( { message_id => $message->{'message_id'},
1373	status => 'failed' } );	1379	status => 'failed',
		1380	delivery_note => 'Message is duplicate' } );
1374	return;	1381	return;
1375	}	1382	}
1376		1383
Lines 1378-1384 sub _send_message_by_sms { Link Here
1378	message => $message->{'content'},	1385	message => $message->{'content'},
1379	} );	1386	} );
1380	_set_message_status( { message_id => $message->{'message_id'},	1387	_set_message_status( { message_id => $message->{'message_id'},
1381	status => ($success ? 'sent' : 'failed') } );	1388	status => ($success ? 'sent' : 'failed'),
		1389	delivery_note => ($success ? '' : 'No notes from SMS driver') } );
		1390
1382	return $success;	1391	return $success;
1383	}	1392	}
1384		1393
Lines 1396-1404 sub _set_message_status { Link Here
1396	}	1405	}
1397		1406
1398	my $dbh = C4::Context->dbh();	1407	my $dbh = C4::Context->dbh();
1399	my $statement = 'UPDATE message_queue SET status= ? WHERE message_id = ?';	1408	my $statement = 'UPDATE message_queue SET status= ?, delivery_note= ? WHERE message_id = ?';
1400	my $sth = $dbh->prepare( $statement );	1409	my $sth = $dbh->prepare( $statement );
1401	my $result = $sth->execute( $params->{'status'},	1410	my $result = $sth->execute( $params->{'status'},
		1411	$params->{'delivery_note'} \|\| '',
1402	$params->{'message_id'} );	1412	$params->{'message_id'} );
1403	return $result;	1413	return $result;
1404	}	1414	}