Attachment #116642 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, reply_address, content_type, delivery_note )
VALUES
( ?,              ?,       ?,       ?,        ?,           ?,                      ?,      NOW(),       ?,          ?,            ?,           ?,              ? )
ENDSQL

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

        $params->{'from_address'},                # from_address
        $params->{'reply_address'},               # reply_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, updated_on, 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, updated_on, to_address, from_address, reply_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.reply_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;
        }
    }

        _set_message_status(
            {
                message_id => $message->{'message_id'},
                status     => 'sent',
                delivery_note => ''
            }
        );
        return 1;

        _set_message_status(
            {
                message_id => $message->{'message_id'},
                status     => 'failed',
                delivery_note => $Mail::Sendmail::error
            }
        );
        carp "$_";


    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 mediumtext");
	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 3617-3622 CREATE TABLE `message_queue` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (+1 lines)
3617	`from_address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,	3617	`from_address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
3618	`reply_address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,	3618	`reply_address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
3619	`content_type` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,	3619	`content_type` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
		3620	`delivery_note` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
3620	PRIMARY KEY (`message_id`),	3621	PRIMARY KEY (`message_id`),
3621	KEY `borrowernumber` (`borrowernumber`),	3622	KEY `borrowernumber` (`borrowernumber`),
3622	KEY `message_transport_type` (`message_transport_type`),	3623	KEY `message_transport_type` (`message_transport_type`),




                <th>Status</th>
                <th class="title-string">Updated on</th>
                <th class="title-string">Time created</th>
                <th>Delivery note</th>
            </tr>
        </thead>
	<tbody>

        </td>
        <td><span title="[% QUEUED_MESSAGE.updated_on | html %]">[% QUEUED_MESSAGE.updated_on | $KohaDates  with_hours => 1 %]</span></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" } ,{ "sType": "title-string" }, null ],
                "sPaginationType": "full"
            }));


Lines 18-24 Link Here

(-)a/t/db_dependent/Letters.t (-2 / +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 => 83;	21	use Test::More tests => 85;
22	use Test::MockModule;	22	use Test::MockModule;
23	use Test::Warn;	23	use Test::Warn;
24		24
Lines 145-150 is( $messages->[0]->{message_transport_type}, $my_message->{message_transport_ty Link Here
145	is( $messages->[0]->{status}, 'pending', 'EnqueueLetter stores the status pending correctly' );	145	is( $messages->[0]->{status}, 'pending', 'EnqueueLetter stores the status pending correctly' );
146	isnt( $messages->[0]->{time_queued}, undef, 'Time queued inserted by default in message_queue table' );	146	isnt( $messages->[0]->{time_queued}, undef, 'Time queued inserted by default in message_queue table' );
147	is( $messages->[0]->{updated_on}, $messages->[0]->{time_queued}, 'Time status changed equals time queued when created in message_queue table' );	147	is( $messages->[0]->{updated_on}, $messages->[0]->{time_queued}, 'Time status changed equals time queued when created in message_queue table' );
		148	is( $messages->[0]->{delivery_note}, '', 'Delivery note for successful message correctly empty');
148		149
149	# Setting time_queued to something else than now	150	# Setting time_queued to something else than now
150	my $yesterday = dt_from_string->subtract( days => 1 );	151	my $yesterday = dt_from_string->subtract( days => 1 );
Lines 174-179 is( $resent, 0, 'The message should not have been resent again' ); Link Here
174	$resent = C4::Letters::ResendMessage();	175	$resent = C4::Letters::ResendMessage();
175	is( $resent, undef, 'ResendMessage should return undef if not message_id given' );	176	is( $resent, undef, 'ResendMessage should return undef if not message_id given' );
176		177
		178	# Delivery notes
		179	is($messages->[0]->{delivery_note}, 'Missing SMS number',
		180	'Delivery note for no smsalertnumber correctly set');
		181
		182
177	# GetLetters	183	# GetLetters
178	my $letters = C4::Letters::GetLetters();	184	my $letters = C4::Letters::GetLetters();
179	is( @$letters, 0, 'GetLetters returns the correct number of letters' );	185	is( @$letters, 0, 'GetLetters returns the correct number of letters' );
180	-

Return to bug 14723

Lines 959-967 sub EnqueueLetter { Link Here

(-)a/C4/Letters.pm (-13 / +23 lines)
959	my $dbh = C4::Context->dbh();	959	my $dbh = C4::Context->dbh();
960	my $statement = << 'ENDSQL';	960	my $statement = << 'ENDSQL';
961	INSERT INTO message_queue	961	INSERT INTO message_queue
962	( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, reply_address, content_type )	962	( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, reply_address, content_type, delivery_note )
963	VALUES	963	VALUES
964	( ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?, ? )	964	( ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?, ?, ? )
965	ENDSQL	965	ENDSQL
966		966
967	my $sth = $dbh->prepare($statement);	967	my $sth = $dbh->prepare($statement);
Lines 977-982 ENDSQL Link Here
977	$params->{'from_address'}, # from_address	977	$params->{'from_address'}, # from_address
978	$params->{'reply_address'}, # reply_address	978	$params->{'reply_address'}, # reply_address
979	$params->{'letter'}->{'content-type'}, # content_type	979	$params->{'letter'}->{'content-type'}, # content_type
		980	$params->{'delivery_note'} \|\| '', # delivery_note
980	);	981	);
981	return $dbh->last_insert_id(undef,undef,'message_queue', undef);	982	return $dbh->last_insert_id(undef,undef,'message_queue', undef);
982	}	983	}
Lines 1118-1124 sub GetQueuedMessages { Link Here
1118		1119
1119	my $dbh = C4::Context->dbh();	1120	my $dbh = C4::Context->dbh();
1120	my $statement = << 'ENDSQL';	1121	my $statement = << 'ENDSQL';
1121	SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, updated_on	1122	SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, updated_on, delivery_note
1122	FROM message_queue	1123	FROM message_queue
1123	ENDSQL	1124	ENDSQL
1124		1125
Lines 1172-1178 sub GetMessage { Link Here
1172	return unless $message_id;	1173	return unless $message_id;
1173	my $dbh = C4::Context->dbh;	1174	my $dbh = C4::Context->dbh;
1174	return $dbh->selectrow_hashref(q\|	1175	return $dbh->selectrow_hashref(q\|
1175	SELECT message_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, updated_on, to_address, from_address, reply_address, content_type	1176	SELECT message_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, updated_on, to_address, from_address, reply_address, content_type, delivery_note
1176	FROM message_queue	1177	FROM message_queue
1177	WHERE message_id = ?	1178	WHERE message_id = ?
1178	\|, {}, $message_id );	1179	\|, {}, $message_id );
Lines 1276-1282 sub _get_unsent_messages { Link Here
1276		1277
1277	my $dbh = C4::Context->dbh();	1278	my $dbh = C4::Context->dbh();
1278	my $statement = qq{	1279	my $statement = qq{
1279	SELECT mq.message_id, mq.borrowernumber, mq.subject, mq.content, mq.message_transport_type, mq.status, mq.time_queued, mq.from_address, mq.reply_address, mq.to_address, mq.content_type, b.branchcode, mq.letter_code	1280	SELECT mq.message_id, mq.borrowernumber, mq.subject, mq.content, mq.message_transport_type, mq.status, mq.time_queued, mq.from_address, mq.reply_address, mq.to_address, mq.content_type, b.branchcode, mq.letter_code, mq.delivery_note
1280	FROM message_queue mq	1281	FROM message_queue mq
1281	LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber	1282	LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber
1282	WHERE status = ?	1283	WHERE status = ?
Lines 1323-1329 sub _send_message_by_email { Link Here
1323	unless ($patron) {	1324	unless ($patron) {
1324	warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";	1325	warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";
1325	_set_message_status( { message_id => $message->{'message_id'},	1326	_set_message_status( { message_id => $message->{'message_id'},
1326	status => 'failed' } );	1327	status => 'failed',
		1328	delivery_note => 'Invalid borrowernumber '.$message->{borrowernumber} } );
1327	return;	1329	return;
1328	}	1330	}
1329	$to_address = $patron->notice_email_address;	1331	$to_address = $patron->notice_email_address;
Lines 1331-1337 sub _send_message_by_email { Link Here
1331	# warn "FAIL: No 'to_address' and no email for " . ($member->{surname} \|\|'') . ", borrowernumber ($message->{borrowernumber})";	1333	# warn "FAIL: No 'to_address' and no email for " . ($member->{surname} \|\|'') . ", borrowernumber ($message->{borrowernumber})";
1332	# warning too verbose for this more common case?	1334	# warning too verbose for this more common case?
1333	_set_message_status( { message_id => $message->{'message_id'},	1335	_set_message_status( { message_id => $message->{'message_id'},
1334	status => 'failed' } );	1336	status => 'failed',
		1337	delivery_note => 'Unable to find an email address for this borrower' } );
1335	return;	1338	return;
1336	}	1339	}
1337	}	1340	}
Lines 1407-1413 sub _send_message_by_email { Link Here
1407	_set_message_status(	1410	_set_message_status(
1408	{	1411	{
1409	message_id => $message->{'message_id'},	1412	message_id => $message->{'message_id'},
1410	status => 'sent'	1413	status => 'sent',
		1414	delivery_note => ''
1411	}	1415	}
1412	);	1416	);
1413	return 1;	1417	return 1;
Lines 1416-1422 sub _send_message_by_email { Link Here
1416	_set_message_status(	1420	_set_message_status(
1417	{	1421	{
1418	message_id => $message->{'message_id'},	1422	message_id => $message->{'message_id'},
1419	status => 'failed'	1423	status => 'failed',
		1424	delivery_note => $Mail::Sendmail::error
1420	}	1425	}
1421	);	1426	);
1422	carp "$_";	1427	carp "$_";
Lines 1467-1479 sub _send_message_by_sms { Link Here
1467		1472
1468	unless ( $patron and $patron->smsalertnumber ) {	1473	unless ( $patron and $patron->smsalertnumber ) {
1469	_set_message_status( { message_id => $message->{'message_id'},	1474	_set_message_status( { message_id => $message->{'message_id'},
1470	status => 'failed' } );	1475	status => 'failed',
		1476	delivery_note => 'Missing SMS number' } );
1471	return;	1477	return;
1472	}	1478	}
1473		1479
1474	if ( _is_duplicate( $message ) ) {	1480	if ( _is_duplicate( $message ) ) {
1475	_set_message_status( { message_id => $message->{'message_id'},	1481	_set_message_status( { message_id => $message->{'message_id'},
1476	status => 'failed' } );	1482	status => 'failed',
		1483	delivery_note => 'Message is duplicate' } );
1477	return;	1484	return;
1478	}	1485	}
1479		1486
Lines 1481-1487 sub _send_message_by_sms { Link Here
1481	message => $message->{'content'},	1488	message => $message->{'content'},
1482	} );	1489	} );
1483	_set_message_status( { message_id => $message->{'message_id'},	1490	_set_message_status( { message_id => $message->{'message_id'},
1484	status => ($success ? 'sent' : 'failed') } );	1491	status => ($success ? 'sent' : 'failed'),
		1492	delivery_note => ($success ? '' : 'No notes from SMS driver') } );
		1493
1485	return $success;	1494	return $success;
1486	}	1495	}
1487		1496
Lines 1505-1513 sub _set_message_status { Link Here
1505	}	1514	}
1506		1515
1507	my $dbh = C4::Context->dbh();	1516	my $dbh = C4::Context->dbh();
1508	my $statement = 'UPDATE message_queue SET status= ? WHERE message_id = ?';	1517	my $statement = 'UPDATE message_queue SET status= ?, delivery_note= ? WHERE message_id = ?';
1509	my $sth = $dbh->prepare( $statement );	1518	my $sth = $dbh->prepare( $statement );
1510	my $result = $sth->execute( $params->{'status'},	1519	my $result = $sth->execute( $params->{'status'},
		1520	$params->{'delivery_note'} \|\| '',
1511	$params->{'message_id'} );	1521	$params->{'message_id'} );
1512	return $result;	1522	return $result;
1513	}	1523	}

Lines 37-42 Link Here

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