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

(-)a/C4/Letters.pm (-13 / +23 lines)
Lines 963-971 sub EnqueueLetter { Link Here
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
}
(-)a/installer/data/mysql/atomicupdate/Bug-14723_-_Additional_delivery_notes_to_messages.perl (+8 lines)
Line 0 Link Here
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
}
(-)a/installer/data/mysql/kohastructure.sql (+1 lines)
Lines 3625-3630 CREATE TABLE `message_queue` ( Link Here
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`),
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/members/notices.tt (-1 / +3 lines)
Lines 51-56 Link Here
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>
(-)a/t/db_dependent/Letters.t (-2 / +7 lines)
Lines 18-24 Link Here
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