Attachment #120487 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 3625-3630 CREATE TABLE `message_queue` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (+1 lines)
3625	`from_address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,	3625	`from_address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
3626	`reply_address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,	3626	`reply_address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
3627	`content_type` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,	3627	`content_type` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
		3628	`delivery_note` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
3628	PRIMARY KEY (`message_id`),	3629	PRIMARY KEY (`message_id`),
3629	KEY `borrowernumber` (`borrowernumber`),	3630	KEY `borrowernumber` (`borrowernumber`),
3630	KEY `message_transport_type` (`message_transport_type`),	3631	KEY `message_transport_type` (`message_transport_type`),

Lines 51-56 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/members/notices.tt (-1 / +3 lines)
51	<th>Status</th>	51	<th>Status</th>
52	<th>Updated on</th>	52	<th>Updated on</th>
53	<th>Time created</th>	53	<th>Time created</th>
		54	<th>Delivery note</th>
54	</tr>	55	</tr>
55	</thead>	56	</thead>
56	<tbody>	57	<tbody>
Lines 90-96 Link Here
90	</td>	91	</td>
91	<td data-order="[% QUEUED_MESSAGE.updated_on \| html %]">[% QUEUED_MESSAGE.updated_on \| $KohaDates with_hours => 1 %]</td>	92	<td data-order="[% QUEUED_MESSAGE.updated_on \| html %]">[% QUEUED_MESSAGE.updated_on \| $KohaDates with_hours => 1 %]</td>
92	<td data-order="[% QUEUED_MESSAGE.time_queued \| html %]">[% QUEUED_MESSAGE.time_queued \| $KohaDates with_hours => 1 %]</td>	93	<td data-order="[% QUEUED_MESSAGE.time_queued \| html %]">[% QUEUED_MESSAGE.time_queued \| $KohaDates with_hours => 1 %]</td>
93	</tr>	94	<td>[% QUEUED_MESSAGE.delivery_note \| html %]</td>
		95	</tr>
94	[% END %]	96	[% END %]
95	</tbody>	97	</tbody>
96	</table>	98	</table>

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

Return to bug 14723

Lines 963-971 sub EnqueueLetter { Link Here

(-)a/C4/Letters.pm (-13 / +23 lines)
963	my $dbh = C4::Context->dbh();	963	my $dbh = C4::Context->dbh();
964	my $statement = << 'ENDSQL';	964	my $statement = << 'ENDSQL';
965	INSERT INTO message_queue	965	INSERT INTO message_queue
966	( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, reply_address, content_type )	966	( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, reply_address, content_type, delivery_note )
967	VALUES	967	VALUES
968	( ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?, ? )	968	( ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?, ?, ? )
969	ENDSQL	969	ENDSQL
970		970
971	my $sth = $dbh->prepare($statement);	971	my $sth = $dbh->prepare($statement);
Lines 981-986 ENDSQL Link Here
981	$params->{'from_address'}, # from_address	981	$params->{'from_address'}, # from_address
982	$params->{'reply_address'}, # reply_address	982	$params->{'reply_address'}, # reply_address
983	$params->{'letter'}->{'content-type'}, # content_type	983	$params->{'letter'}->{'content-type'}, # content_type
		984	$params->{'delivery_note'} \|\| '', # delivery_note
984	);	985	);
985	return $dbh->last_insert_id(undef,undef,'message_queue', undef);	986	return $dbh->last_insert_id(undef,undef,'message_queue', undef);
986	}	987	}
Lines 1123-1129 sub GetQueuedMessages { Link Here
1123		1124
1124	my $dbh = C4::Context->dbh();	1125	my $dbh = C4::Context->dbh();
1125	my $statement = << 'ENDSQL';	1126	my $statement = << 'ENDSQL';
1126	SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, updated_on	1127	SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, updated_on, delivery_note
1127	FROM message_queue	1128	FROM message_queue
1128	ENDSQL	1129	ENDSQL
1129		1130
Lines 1177-1183 sub GetMessage { Link Here
1177	return unless $message_id;	1178	return unless $message_id;
1178	my $dbh = C4::Context->dbh;	1179	my $dbh = C4::Context->dbh;
1179	return $dbh->selectrow_hashref(q\|	1180	return $dbh->selectrow_hashref(q\|
1180	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	1181	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
1181	FROM message_queue	1182	FROM message_queue
1182	WHERE message_id = ?	1183	WHERE message_id = ?
1183	\|, {}, $message_id );	1184	\|, {}, $message_id );
Lines 1282-1288 sub _get_unsent_messages { Link Here
1282		1283
1283	my $dbh = C4::Context->dbh();	1284	my $dbh = C4::Context->dbh();
1284	my $statement = qq{	1285	my $statement = qq{
1285	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	1286	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
1286	FROM message_queue mq	1287	FROM message_queue mq
1287	LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber	1288	LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber
1288	WHERE status = ?	1289	WHERE status = ?
Lines 1333-1339 sub _send_message_by_email { Link Here
1333	unless ($patron) {	1334	unless ($patron) {
1334	warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";	1335	warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";
1335	_set_message_status( { message_id => $message->{'message_id'},	1336	_set_message_status( { message_id => $message->{'message_id'},
1336	status => 'failed' } );	1337	status => 'failed',
		1338	delivery_note => 'Invalid borrowernumber '.$message->{borrowernumber} } );
1337	return;	1339	return;
1338	}	1340	}
1339	$to_address = $patron->notice_email_address;	1341	$to_address = $patron->notice_email_address;
Lines 1341-1347 sub _send_message_by_email { Link Here
1341	# warn "FAIL: No 'to_address' and no email for " . ($member->{surname} \|\|'') . ", borrowernumber ($message->{borrowernumber})";	1343	# warn "FAIL: No 'to_address' and no email for " . ($member->{surname} \|\|'') . ", borrowernumber ($message->{borrowernumber})";
1342	# warning too verbose for this more common case?	1344	# warning too verbose for this more common case?
1343	_set_message_status( { message_id => $message->{'message_id'},	1345	_set_message_status( { message_id => $message->{'message_id'},
1344	status => 'failed' } );	1346	status => 'failed',
		1347	delivery_note => 'Unable to find an email address for this borrower' } );
1345	return;	1348	return;
1346	}	1349	}
1347	}	1350	}
Lines 1417-1423 sub _send_message_by_email { Link Here
1417	_set_message_status(	1420	_set_message_status(
1418	{	1421	{
1419	message_id => $message->{'message_id'},	1422	message_id => $message->{'message_id'},
1420	status => 'sent'	1423	status => 'sent',
		1424	delivery_note => ''
1421	}	1425	}
1422	);	1426	);
1423	return 1;	1427	return 1;
Lines 1426-1432 sub _send_message_by_email { Link Here
1426	_set_message_status(	1430	_set_message_status(
1427	{	1431	{
1428	message_id => $message->{'message_id'},	1432	message_id => $message->{'message_id'},
1429	status => 'failed'	1433	status => 'failed',
		1434	delivery_note => $Mail::Sendmail::error
1430	}	1435	}
1431	);	1436	);
1432	carp "$_";	1437	carp "$_";
Lines 1477-1489 sub _send_message_by_sms { Link Here
1477		1482
1478	unless ( $patron and $patron->smsalertnumber ) {	1483	unless ( $patron and $patron->smsalertnumber ) {
1479	_set_message_status( { message_id => $message->{'message_id'},	1484	_set_message_status( { message_id => $message->{'message_id'},
1480	status => 'failed' } );	1485	status => 'failed',
		1486	delivery_note => 'Missing SMS number' } );
1481	return;	1487	return;
1482	}	1488	}
1483		1489
1484	if ( _is_duplicate( $message ) ) {	1490	if ( _is_duplicate( $message ) ) {
1485	_set_message_status( { message_id => $message->{'message_id'},	1491	_set_message_status( { message_id => $message->{'message_id'},
1486	status => 'failed' } );	1492	status => 'failed',
		1493	delivery_note => 'Message is duplicate' } );
1487	return;	1494	return;
1488	}	1495	}
1489		1496
Lines 1491-1497 sub _send_message_by_sms { Link Here
1491	message => $message->{'content'},	1498	message => $message->{'content'},
1492	} );	1499	} );
1493	_set_message_status( { message_id => $message->{'message_id'},	1500	_set_message_status( { message_id => $message->{'message_id'},
1494	status => ($success ? 'sent' : 'failed') } );	1501	status => ($success ? 'sent' : 'failed'),
		1502	delivery_note => ($success ? '' : 'No notes from SMS driver') } );
		1503
1495	return $success;	1504	return $success;
1496	}	1505	}
1497		1506
Lines 1515-1523 sub _set_message_status { Link Here
1515	}	1524	}
1516		1525
1517	my $dbh = C4::Context->dbh();	1526	my $dbh = C4::Context->dbh();
1518	my $statement = 'UPDATE message_queue SET status= ? WHERE message_id = ?';	1527	my $statement = 'UPDATE message_queue SET status= ?, delivery_note= ? WHERE message_id = ?';
1519	my $sth = $dbh->prepare( $statement );	1528	my $sth = $dbh->prepare( $statement );
1520	my $result = $sth->execute( $params->{'status'},	1529	my $result = $sth->execute( $params->{'status'},
		1530	$params->{'delivery_note'} \|\| '',
1521	$params->{'message_id'} );	1531	$params->{'message_id'} );
1522	return $result;	1532	return $result;
1523	}	1533	}