Attachment #87525 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 2490-2495 CREATE TABLE `message_queue` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (+1 lines)
2490	`to_address` LONGTEXT,	2490	`to_address` LONGTEXT,
2491	`from_address` LONGTEXT,	2491	`from_address` LONGTEXT,
2492	`content_type` MEDIUMTEXT,	2492	`content_type` MEDIUMTEXT,
		2493	`delivery_note` MEDIUMTEXT,
2493	PRIMARY KEY `message_id` (`message_id`),	2494	PRIMARY KEY `message_id` (`message_id`),
2494	KEY `borrowernumber` (`borrowernumber`),	2495	KEY `borrowernumber` (`borrowernumber`),
2495	KEY `message_transport_type` (`message_transport_type`),	2496	KEY `message_transport_type` (`message_transport_type`),




		<th>Type</th>
		<th>Status</th>
		<th>Time</th>
		<th>Delivery note</th>
	    </tr>
	</thead>
	<tbody>

            [% END %]
        </td>
        <td><span title="[% QUEUED_MESSAGE.time_queued | html %]">[% QUEUED_MESSAGE.time_queued | $KohaDates  with_hours => 1 %]</span></td>
        <td>[% QUEUED_MESSAGE.delivery_note | html %]</td>
	    </tr>
	    [% END %]
	</tbody>

        $(document).ready(function() {
            $("#noticestable").dataTable($.extend(true, {}, dataTablesDefaults, {
                "aaSorting": [[ 3, "desc" ]],
                "aoColumns": [ null,null,null,{ "sType": "title-string" }, null ],
                "sPaginationType": "four_button"
            }));


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 => 67;	21	use Test::More tests => 69;
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 908-916 sub EnqueueLetter { Link Here

(-)a/C4/Letters.pm (-13 / +23 lines)
908	my $dbh = C4::Context->dbh();	908	my $dbh = C4::Context->dbh();
909	my $statement = << 'ENDSQL';	909	my $statement = << 'ENDSQL';
910	INSERT INTO message_queue	910	INSERT INTO message_queue
911	( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type )	911	( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type, delivery_note )
912	VALUES	912	VALUES
913	( ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ? )	913	( ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?, ? )
914	ENDSQL	914	ENDSQL
915		915
916	my $sth = $dbh->prepare($statement);	916	my $sth = $dbh->prepare($statement);
Lines 925-930 ENDSQL Link Here
925	$params->{'to_address'}, # to_address	925	$params->{'to_address'}, # to_address
926	$params->{'from_address'}, # from_address	926	$params->{'from_address'}, # from_address
927	$params->{'letter'}->{'content-type'}, # content_type	927	$params->{'letter'}->{'content-type'}, # content_type
		928	$params->{'delivery_note'} \|\| '', # delivery_note
928	);	929	);
929	return $dbh->last_insert_id(undef,undef,'message_queue', undef);	930	return $dbh->last_insert_id(undef,undef,'message_queue', undef);
930	}	931	}
Lines 1066-1072 sub GetQueuedMessages { Link Here
1066		1067
1067	my $dbh = C4::Context->dbh();	1068	my $dbh = C4::Context->dbh();
1068	my $statement = << 'ENDSQL';	1069	my $statement = << 'ENDSQL';
1069	SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued	1070	SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, delivery_note
1070	FROM message_queue	1071	FROM message_queue
1071	ENDSQL	1072	ENDSQL
1072		1073
Lines 1120-1126 sub GetMessage { Link Here
1120	return unless $message_id;	1121	return unless $message_id;
1121	my $dbh = C4::Context->dbh;	1122	my $dbh = C4::Context->dbh;
1122	return $dbh->selectrow_hashref(q\|	1123	return $dbh->selectrow_hashref(q\|
1123	SELECT message_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type	1124	SELECT message_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type, delivery_note
1124	FROM message_queue	1125	FROM message_queue
1125	WHERE message_id = ?	1126	WHERE message_id = ?
1126	\|, {}, $message_id );	1127	\|, {}, $message_id );
Lines 1224-1230 sub _get_unsent_messages { Link Here
1224		1225
1225	my $dbh = C4::Context->dbh();	1226	my $dbh = C4::Context->dbh();
1226	my $statement = qq{	1227	my $statement = qq{
1227	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	1228	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
1228	FROM message_queue mq	1229	FROM message_queue mq
1229	LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber	1230	LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber
1230	WHERE status = ?	1231	WHERE status = ?
Lines 1271-1277 sub _send_message_by_email { Link Here
1271	unless ($patron) {	1272	unless ($patron) {
1272	warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";	1273	warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";
1273	_set_message_status( { message_id => $message->{'message_id'},	1274	_set_message_status( { message_id => $message->{'message_id'},
1274	status => 'failed' } );	1275	status => 'failed',
		1276	delivery_note => 'Invalid borrowernumber '.$message->{borrowernumber} } );
1275	return;	1277	return;
1276	}	1278	}
1277	$to_address = $patron->notice_email_address;	1279	$to_address = $patron->notice_email_address;
Lines 1279-1285 sub _send_message_by_email { Link Here
1279	# warn "FAIL: No 'to_address' and no email for " . ($member->{surname} \|\|'') . ", borrowernumber ($message->{borrowernumber})";	1281	# warn "FAIL: No 'to_address' and no email for " . ($member->{surname} \|\|'') . ", borrowernumber ($message->{borrowernumber})";
1280	# warning too verbose for this more common case?	1282	# warning too verbose for this more common case?
1281	_set_message_status( { message_id => $message->{'message_id'},	1283	_set_message_status( { message_id => $message->{'message_id'},
1282	status => 'failed' } );	1284	status => 'failed',
		1285	delivery_note => 'Unable to find an email address for this borrower' } );
1283	return;	1286	return;
1284	}	1287	}
1285	}	1288	}
Lines 1322-1332 sub _send_message_by_email { Link Here
1322		1325
1323	if ( Mail::Sendmail::sendmail( %sendmail_params ) ) {	1326	if ( Mail::Sendmail::sendmail( %sendmail_params ) ) {
1324	_set_message_status( { message_id => $message->{'message_id'},	1327	_set_message_status( { message_id => $message->{'message_id'},
1325	status => 'sent' } );	1328	status => 'sent',
		1329	delivery_note => '' } );
1326	return 1;	1330	return 1;
1327	} else {	1331	} else {
1328	_set_message_status( { message_id => $message->{'message_id'},	1332	_set_message_status( { message_id => $message->{'message_id'},
1329	status => 'failed' } );	1333	status => 'failed',
		1334	delivery_note => $Mail::Sendmail::error } );
1330	carp $Mail::Sendmail::error;	1335	carp $Mail::Sendmail::error;
1331	return;	1336	return;
1332	}	1337	}
Lines 1375-1387 sub _send_message_by_sms { Link Here
1375		1380
1376	unless ( $patron and $patron->smsalertnumber ) {	1381	unless ( $patron and $patron->smsalertnumber ) {
1377	_set_message_status( { message_id => $message->{'message_id'},	1382	_set_message_status( { message_id => $message->{'message_id'},
1378	status => 'failed' } );	1383	status => 'failed',
		1384	delivery_note => 'Missing SMS number' } );
1379	return;	1385	return;
1380	}	1386	}
1381		1387
1382	if ( _is_duplicate( $message ) ) {	1388	if ( _is_duplicate( $message ) ) {
1383	_set_message_status( { message_id => $message->{'message_id'},	1389	_set_message_status( { message_id => $message->{'message_id'},
1384	status => 'failed' } );	1390	status => 'failed',
		1391	delivery_note => 'Message is duplicate' } );
1385	return;	1392	return;
1386	}	1393	}
1387		1394
Lines 1389-1395 sub _send_message_by_sms { Link Here
1389	message => $message->{'content'},	1396	message => $message->{'content'},
1390	} );	1397	} );
1391	_set_message_status( { message_id => $message->{'message_id'},	1398	_set_message_status( { message_id => $message->{'message_id'},
1392	status => ($success ? 'sent' : 'failed') } );	1399	status => ($success ? 'sent' : 'failed'),
		1400	delivery_note => ($success ? '' : 'No notes from SMS driver') } );
		1401
1393	return $success;	1402	return $success;
1394	}	1403	}
1395		1404
Lines 1413-1421 sub _set_message_status { Link Here
1413	}	1422	}
1414		1423
1415	my $dbh = C4::Context->dbh();	1424	my $dbh = C4::Context->dbh();
1416	my $statement = 'UPDATE message_queue SET status= ? WHERE message_id = ?';	1425	my $statement = 'UPDATE message_queue SET status= ?, delivery_note= ? WHERE message_id = ?';
1417	my $sth = $dbh->prepare( $statement );	1426	my $sth = $dbh->prepare( $statement );
1418	my $result = $sth->execute( $params->{'status'},	1427	my $result = $sth->execute( $params->{'status'},
		1428	$params->{'delivery_note'} \|\| '',
1419	$params->{'message_id'} );	1429	$params->{'message_id'} );
1420	return $result;	1430	return $result;
1421	}	1431	}

Lines 36-41 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/members/notices.tt (-1 / +3 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 %]</span></td>	77	<td><span title="[% QUEUED_MESSAGE.time_queued \| html %]">[% QUEUED_MESSAGE.time_queued \| $KohaDates with_hours => 1 %]</span></td>
		78	<td>[% QUEUED_MESSAGE.delivery_note \| html %]</td>
77	</tr>	79	</tr>
78	[% END %]	80	[% END %]
79	</tbody>	81	</tbody>
Lines 98-104 Link Here
98	$(document).ready(function() {	100	$(document).ready(function() {
99	$("#noticestable").dataTable($.extend(true, {}, dataTablesDefaults, {	101	$("#noticestable").dataTable($.extend(true, {}, dataTablesDefaults, {
100	"aaSorting": [[ 3, "desc" ]],	102	"aaSorting": [[ 3, "desc" ]],
101	"aoColumns": [ null,null,null,{ "sType": "title-string" } ],	103	"aoColumns": [ null,null,null,{ "sType": "title-string" }, null ],
102	"sPaginationType": "four_button"	104	"sPaginationType": "four_button"
103	}));	105	}));
104		106