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

(-)a/C4/Letters.pm (-13 / +23 lines)
Lines 959-967 sub EnqueueLetter { Link Here
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
}
(-)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 3617-3622 CREATE TABLE `message_queue` ( Link Here
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`),
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/members/notices.tt (-1 / +3 lines)
Lines 37-42 Link Here
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
(-)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 => 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